Формулы, операторы, ссылки, сообщения об ошибках, функции

Формулу можно создать с использование констант, ссылок, функций или имен, а также смешанного вида. Существуют также вложенные формулы.

Формула – это некое выражение, которое выполняет вычисление между операндами с помощью операторов. Операнд – это элемент вычисления (константы, функции и ссылки). Константа – постоянное (не вычисляемое) значение. Может быть числом или текстом.

Функция – заранее созданная формула, выполняющая сложные вычисления по введенным значениям (аргументам) в строго определенном порядке. Функции бывают математическими, финансовыми, статистическими и т. п.

Ссылка – адрес ячейки или диапазона ячеек на табличном поле, в которых содержатся значения.

Операторы – это знак (символ), определяющий тип вычисления в формуле над операндами. В «Excel» используются операторы: математические, текстовые, операторы сравнения и операторы ссылок.

Формула всегда начинается со знака равенства (=), а затем следуют операнды и операторы, например,

=24*3, или =А1+В2*(С10-D8)/СУММ(L12:H4), где: 24 и 3 – константы; СУММ() – функция; А1, В2, С10, D8, L12 и H4 – ссылки; +,-, / и * – операторы.

Формулу можно вводить непосредственно в ячейку или в окно ввода на строке формул. После вычисления в ячейке отображается полученный результат, а на строке формул – созданная формула.

Если выделить ячейку с формулой (щелкнуть по ячейке), то в самой ячейке увидим результат вычисления формулы, а в строке формул – саму формулу.

Типы операторов:

Математические операторы: – это операторы простых действий, а именно сложения, вычитания, умножения и т. д. Вводятся с помощью клавиатуры:

+ (плюс) – сложение; – (минус) – вычитание или отрицание; * (звездочка) – умножение;

/ (косая черта) – деление; % (знак процента) – процент; ^ (знак крышки) – возведение в степень.

Операторы сравнения: это операторы сравнения разных значений, результатом которого является логическое выражение «ИСТИНА» или «ЛОЖЬ»:

= (знак равенства) – равно;

> (знак «больше») – больше;

< (знак «меньше») – меньше;

>= (знак «больше или равно») – больше или равно;

<= (знак «меньше или равно») – меньше или равно;

<> (знак «не равно») – не равно.

Текстовый оператор: амперсанд (&) – оператор объединения нескольких отрывков текста в одну строку, или – объединение двух последовательностей знаков в одну последовательность.

Операторы ссылок: 1) оператор ссылки на диапазон ячеек: (двоеточие) – используется между адресами первой и последней ячейками диапазона. Такое сочетание представляет собой ссылку на группу ячеек, например, A10:C20; 2); (точка с запятой) – оператор объединения нескольких ссылок в одну, например, СУММ(С10:В15; Е45:Т30); 3) знак пробела – оператор пересечения множеств, который используется для ссылки на общие ячейки двух диапазонов. Например, В10:D20 C15:C25.

Ссылки в формулах.

При создании связей между ячейками (при написании формул, построении графиков и др.) используются три типа ссылок – относительные, абсолютные и смешанные.

Относительная ссылка – это ссылка в формуле, основанная на относительном расположении ячейки, в которой находится формула, и ячейки, на которую указывает ссылка. При этом при изменении позиции ячейки с формулой соответственно изменяется и ссылка на связанную ячейку. Так что, например, при копировании (размножении) формулы вдоль столбцов или строк ссылка автоматически корректируется с учетом перемещения ячейки с формулой. Данный тип ссылок используется по умолчанию.

Абсолютная ссылка – это неизменная ссылка в формуле на ячейку, расположенную в определенном месте. При перемещении ячейки с формулой адрес ячейки с абсолютной ссылкой не корректируется. Абсолютная ссылка указывается символами $ перед координатами. Например, абсолютная ссылка на ячейку $A$1 указывает на неизменность адреса ячейки А1 при копировании формулы вдоль столбца или строки.

Смешанная ссылка – это ссылка с использованием либо абсолютной ссылки на столбец и относительной – на строку ($A1), либо абсолютной ссылки на строку и относительной – на столбец (A$1).

При этом при изменении позиции ячейки с формулой относительная ссылка строки или столбца изменяется, а абсолютная часть ссылки остается прежней. Другими словами: при размножении формулы со смешанной ссылкой не изменяется та координата, перед которой находится знак $.

Изменение вида ссылки возможно вручную вставкой или удалением знака $, или путем нажатия клавиши F4 (поочередное нажатие циклически изменяет вид ссылки: В1 => $В$1 => В$1 => $B1 => В).

Сообщения об ошибках

Если формула содержит ошибку, то Excel отобразит специальное сообщение об ошибке. Каждый тип ошибки обозначается своим сообщением, имеет разные причины и требует разных способов разрешения.

##### Эти символы сообщают, что столбец, содержащий числа, недостаточно широк для них, или же дата и время, введенные в ячейки данного столбца, содержат отрицательные числа.

В первом случае достаточно просто увеличить ширину столбца или изменить числовой формат данных (например, уменьшить число знаков после запятой).

#ЗНАЧ! Эти сообщение об использовании текста вместо числа или логического значения (ИСТИНА или ЛОЖЬ). То есть Excel не может преобразовать данный текст в ячейке в правильный тип данных. Необходимо убедиться, что формула или функция ссылается на те ячейки, которые содержат правильные значения.

#ДЕЛ/0! Эти сообщение о том, что в ячейке происходит деление числа на 0 (ноль) или же используются ссылки на пустую ячейку.

#ИМЯ? Имя с ошибкой или неправильный оператор, или в формуле используется функция с ошибкой в написании, например, СУМ(A1:А10) вместо СУММ(A1:А10).

#Н/Д Эти символы сообщают, что нужное значение не доступно для функции или формулы.

#ССЫЛКА! Эти символы сообщают о неверной ссылке на ячейку.

#ЧИСЛО! Это сообщение об использовании неправильных числовых значений в формуле или функции. Если в функцию, использующую числовой аргумент, было вставлено неприемлемое значение, то необходимо проверить все аргументы функции и при необходимости исправить написание всех чисел и формат соответствующих ячеек.

Работа с функциями

Функции – это заранее созданные формулы, которые выполняют вычисления по введенным величинам и в указанном порядке.

Функция включает следующие элементы:

– знак равенства (=);

– собственное имя, например СУММ;

– открывающая скобка, после которой находится список аргументов, разделенных точкой с запятой;

– аргументы, которые непосредственно зависят от выбранной функции и могут состоять из чисел, текста, логических величин (например, «ИСТИНА» или «ЛОЖЬ»), массивов, ссылок, формул и т. д.

– закрывающая скобка.

Каждая функция имеет свой синтаксис и порядок действия, который нужно неукоснительно соблюдать, иначе вычисления будут ошибочными. Необходимо также учитывать соответствие типов аргументов.

Исполнение функции начинается с ввода имени, затем открывается круглая скобка, указываются аргумент или несколько аргументов, разделяющиеся точками с запятыми, а затем – закрывающая круглая скобка.

В Excel существуют математические, финансовые, статистические и другие функции. Имя функции можно вводить вручную с клавиатуры, а можно выбирать в окне «Мастер функций».

Функция может выступать в качестве аргумента для другой функции. В этом случае она будет называться вложенной функцией. При этом в формулах можно использовать до семи уровней (Excel 2007 и более ранние версии) или до 64 уровней (Excel 2010) вложения функций.

При работе с вложенными функциями появляется всплывающая подсказка аргумента, щелкнув на которую можно открыть окно справки, помогающей разобраться с синтаксисом и аргументами.

Функцию можно вставить как в начале вычисления, так и по ходу создания собственной формулы.

1 способ: 1. В окне открытого листа выделить ячейку, где будет располагаться функция, перейти к вкладке «Формулы» и в группе «Библиотека функций» щелкнуть по кнопке «f_x Вставить функцию».

2. В окне «Мастер функций – шаг 1 из 2» в графе «Категории» раскрыть список существующих типов функций и выбрать нужную категорию. Например, «Математические».

3. В группе «Выберите функцию» из списка категории выбрать нужную функцию.

4. Перейти к следующему шагу щелчком по кнопке «ОК».

5. В окне «Аргументы функции» ввести в поля аргументов адреса ячеек для вычисления.

Примечание. Для каждой функции окно «Аргументы функции» имеет собственный вид.

6. В ячейке, если аргументы введены были правильно, отобразится результат вычисления функции.

2 способ: 1. В окне открытого листа выделить ячейку, где будет располагаться функция.

2. Нажать сочетание клавиш Shift+F3.

3. Далее выполнять шаги, как в первом способе.

3 способ. Нужную функцию можно выбрать на вкладке «Формулы» и в группе «Библиотека функций» из списка соответствующих категорий (напр., Финансовые – рис.выше).