Порядок выполнения действий в формулах

Синтаксис формулы

Синтаксисом формул называется порядок, в котором вычисляются значения. Синтаксисом формулы задается последовательность вычислений. Формула должна начинаться со знака равенства (=), за которым следует набор вычисляемых величин - вычисляемые элементы (операнды) и операторы. Операндами могут быть: константы (числа), ссылки на ячейки или диапазоны ссылок (переменные, заголовки, имена или функции). Функция – это стандартная формула, которая возвращает результат выполнения определенных действия над значениями, выступающими как аргументы. Функции используются для упрощения сложных и длительных вычислений. В следующем примере представлена формула, вычисляющая разность между числами 5 и 1. Результат выполнения отобразится в ячейке, в которой указана формула.

=5-1

По умолчанию, Microsoft Excel вычисляет формулу слева направо, начиная вычисление со знака равенства (=). Используя синтаксис написания формулы можно управлять процессом вычисления. Например, следующая формула возвращает число 11, так как умножение имеет больший приоритет над сложением и, поэтому, выполняется в первую очередь: сначала происходит умножение 2 на 3 (результат умножения равен 6), а затем полученное значение складывается с 5.

=5+2*3

Если для изменения синтаксиса воспользоваться скобками, то сначала произойдет сложение 5 и 2, а затем умножение полученного результата на 3. Следующая формула вернет число 21.

=(5+2)*3

Другой способ создания формул – это указание адресов ячеек. Например, дана таблица с числовыми значениями. В столбцах А, В, С заданы переменные, а столбце Д выполняется расчет по формуле

А	В	С	D
			=(A1+B1)*C1
			=(A2+B2)*C2
			=(A3+B3)*C3

В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, а также операторы сравнения и адресные операторы.

· ·Арифметические операторы используются для выполнения основных математических вычислений над числами (например сложение, вычитание или умножение, другие действия над числами и получение численных результатов). Результатом выполнения арифметической операции всегда является число.

· Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ.

· Текстовый оператор «& » используется для обозначения операции объединения последовательностей символов в единую последовательность.

· Адресные операторы объединяют диапазоны ячеек для осуществления вычислений.

Применение операторов

Арифметический оператор	Значение	Пример
+ (знак плюс)	Сложение	3+3
– (знак минус)	Вычитание	3–1
	Унарный минус	–1
* (звездочка)	Умножение	3*3
/ (косая черта)	Деление	3/3
% (знак процента)	Процент	20%
^ (крышка)	Возведение в степень	3^2 (аналогично 3*3)

Оператор сравнения	Значение	Пример
= (знак равенства)	Равно	A1=B1
> (знак больше)	Больше	A1> B1
< (знак меньше)	Меньше	A1< B1
> = (знак больше и знак равенства)	Больше или равно	A1> =B1
< = (знак меньше и знак равенства)	Меньше или равно	A1< =B1
< > (знак больше и знак меньше)	Не равно	A1< > B1

Адресный оператор	Значение	Пример
; (точка с запятой)	Оператор, который ссылается на отдельные ячейки.	А5; А25
: (двоеточие)	Оператор диапазона, который ссылается на все ячейки между границами диапазона включительно.	B5: B15
, (запятая)	Оператор объединения, который ссылается на объединение ячеек диапазонов.	СУММ(B5: B15, D5: D15)
(пробел)	Оператор пересечения, который ссылается на общие ячейки диапазонов.	СУММ(B5: B15 A7: D7) В этом примере, ячейка B7 является общей для двух диапазонов.

Текстовый оператор	Значение	Пример
& (амперсант)	Объединение последовательностей символов в одну последовательность.	Результатом выполнения выражения " Северный" & " ветер" будет: " Северный ветер"

Порядок выполнения действий в формулах

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

Оператор Описание

: (двоеточие) Операторы ссылок

, (запятая)

(пробел)

– Унарный минус (например –1)

% Процент

^ Возведение в степень

* и / Умножение и деление

+ и – Сложение и вычитание

& Объединение последовательностей символов в одну последовательность

= < > < = > = < > Операторы сравнения

Функции

В Microsoft Excel содержится большое количество стандартных формул, называемых функциями. Функции используются для простых или сложных вычислений. Наиболее распространенной является функция СУММ, суммирующая диапазоны ячеек. Несмотря на то, что пользователь может создать формулу, суммирующую значения несколько ячеек, функция СУММ обладает большими возможностями и может суммировать несколько диапазонов ячеек. Функции задаются с помощью формул, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке, называемом синтаксисом. Например, функция СУММ складывает значения или группы ячеек, а функция ППЛАТ вычисляет величину выплаты за один период годовой ренты на основе постоянных выплат и постоянной процентной ставки.

Список аргументов может состоять из чисел, текста, логических величин (например ИСТИНА или ЛОЖЬ), массивов, значений ошибок (например #Н/Д) или ссылок. Необходимо следить за соответствием типов аргументов. Кроме того, аргументы могут быть как константами, так и переменными.

Ввод формулы с клавиатуры

1 Укажите ячейку, в которую необходимо ввести формулу.

2 Введите = (знак равенства).

Если нажать кнопку Изменить формулу в строке формул или кнопку Вставка функции, автоматически вставляется знак равенства.

3 Введите формулу. 4 Нажмите клавишу ENTER.

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

Правка формулы

1 Укажите ячейку, содержащую изменяемую формулу. 2 Активизируйте строку формул или нажмите F2 - измените формулу 3 Нажмите клавишу ENTER.

Примеры формул

В следующей таблице приведены примеры используемых формул в Microsoft Excel.

Описание Формула

Необходимо вычислить баланс в кассовой книге. В ячейке D7 содержится приходная часть, в ячейке E7 содержится расходная часть, а в ячейке F6 содержится предыдущий баланс.	Чтобы рассчитать текущий баланс для первой сделки (в ячейке F7): =СУММ(F6, D7, –E7) Для расчета баланса следующей сделки скопируйте формулу в ячейку, содержащую баланс этой сделки.
Необходимо объединить фамилию и имя, записанных в разных ячейках. Ячейка D5 содержит фамилию, а ячейка E5 — имя.	Чтобы отобразить полное имя в формате " имя, фамилия": =E5& ", " & D5 Чтобы отобразить полное имя в формате " фамилия имя": =D5& " " & E5
Необходимо увеличить числовое значение на несколько процентов, например, на 5 процентов. Ячейка F5 содержит числовое значение.	=F5(1+5%) Если значение процента находится в ячейке (например в ячейке F2): =F5(1+$F$2) Ссылка на ячейку F2 является абсолютной ссылкой и, поэтому, формулу можно скопировать в другую ячейку без изменения ссылки на ячейку F2.
Необходимо объединить два текстовых фрагмента. Например, если в ячейке F5 содержится заявленная дата «05.01.1996», и необходимо отобразить текст " Дата отчета: 05.01.96" в ячейке G50.	=" Дата отчета: " & TEXT(F5, " dd.mm.yy" ) Примечание. Функция TEXT используется для преобразования числа, даты или значения времени в текстовый формат.
Необходимо вычислить сумму в диапазоне, основываясь на значениях другого диапазона. Например, для каждой ячейки диапазона B5: B25, содержащей значение «СеверныйВетер», необходимо вычислить сумму соответствующих ячеек из диапазона F5: F25.	=СУММЕСЛИ(B5: B25, " СеверныйВетер", F5: F25)
Необходимо вычислить сумму в диапазоне, основываясь на двух условиях. Например, необходимо вычислить сумму ячеек диапазона F5: F25, если ячейки диапазона B5: B25 содержат значение «СеверныйВетер», а ячейки диапазона C5: C25 содержат значение «Западный».	=СУММ(ЕСЛИ(B5: B25=" СеверныйВетер", ЕСЛИ(C5: C25=" Западный", F5: F25))) Примечание. Это формула массива и, поэтому, должна вводится клавишами CTRL+SHIFT+ENTER.
Необходимо вычислить число ячеек, содержащих определенное значение. Например, число ячеек в диапазоне B5: B25, содержащих значение «СеверныйВетер».	=СЧЁТЕСЛИ(B5: B25, " СеверныйВетер" )
Необходимо вычислить число совпадающих ячеек диапазона, основываясь на другом диапазоне. Например, число строк в диапазоне B5: B25, содержащих значение «СеверныйВетер» и значение «Западный» в диапазоне C5: C25.	=СУММ(ЕСЛИ(B5: B25=" СеверныйВетер", ЕСЛИ(C5: C25=" Западный", 1, 0))) Примечание. Это формула массива и, поэтому, должна вводится клавишами CTRL+SHIFT+ENTER.

" Автосуммирование" (стандартная панель инструментов)

Используется для автоматического суммирования чисел с помощью функции СУММ. При нажатии кнопки для суммирования предлагается возможный диапазон ячеек, расположенный в текущем столбце или строке. Если предложенный диапазон не подходит, выделите суммируемые ячейки и нажмите клавишу ENTER.

" Мастер функций"

(стандартная панель инструментов)

Используется для выбора из списка необходимой функции и задания её аргументов.

1 ШАГ – В окне диалога Мастера функций выбираем Категорию, затем Функцию, затем нажимаем кнопку Далее.

2 ШАГ - Вводим аргументы (диапазон ячеек) в поле fx

· ПРИМЕЧАНИЕ: для выбора диапазона ячеек можно переместить окно диалога Мастера функций, а затем мышью выделить необходимый диапазон.

3 ШАГ - Нажмите кнопку ГОТОВО.

· ПРИМЕЧАНИЕ: для сложных формул может быть предложено ввести аргументы в нескольких окнах диалога.

Использование ссылок

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

В ссылках на ячейки используются обозначения соответствующих строк и столбцов листа. Такие ссылки называются ссылками A1. По умолчанию в Microsoft Excel столбцы помечены латинскими буквами (A, B, C, D, ... Z; AA, AB, AC, AD, ... AZ; BA, BB, BC и т.д.), а строки пронумерованы.

Можно заменить буквы столбцов на номера. Такие ссылки называются ссылками R1C1. Ссылка на текущую ячейку выводится на экран в области ссылок, расположенной в левой части строки формул.

Ввод ссылки на ячейки другого листа (отображает адрес ячейки и имя листа с восклицательным знаком)

1 Укажите ячейку, в которую следует ввести ссылку. НАПРИМЕР ячейка С1 на Листе1

2 Наберите знак равенства (=).

3 Если необходимо, введите операнды и операторы, которые должны предшествовать ссылке. НАПРИМЕР: =А1*

4 Переключитесь на требуемый лист и выделите ячейки, на которые нужно сослаться.

НАПРИМЕР: Лист2! В10

5 Нажмите ENTER, если ввод формулы завершен

В итоге в ячейки С1 на Листе1 получится формула: =А1*Лист2! В10

· Примечание Чтобы ввести трехмерную ссылку, выделите область ячеек на первом листе, нажмите клавишу SHIFT и, не отпуская ее, укажите последний лист трехмерного диапазона.

Ввод внешней ссылки в формулу

1 Укажите ячейку, в которую следует поместить внешнюю ссылку.

2 Наберите знак равенства (=). Если необходимо, введите операторы, которые должны предшествовать внешней ссылке.

3 Перейдите в книгу, содержащую ячейки, на которые нужно сослаться.

4 Выберите рабочий лист, а затем адрес ячейки, на которую нужно сослаться.

5 Нажмите ENTER, если ввод формулы завершен

Например: =А1*[Книга3.xls]Лист1! А1

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

КОПИРОВАНИЕ ФОРМУЛ

АНАЛИЗ ОШИБОК в ФОРМУЛАХ

Ошибка в формуле

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

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

– Используйте двоеточие для указания диапазона. При указании ссылки на диапазон ячеек используйте двоеточие (: ) в качестве разделителя между первой и последней ячейками диапазона.

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

– Допускается вложение не более семи функций. Число уровней вложения функций в пределах одной функции не должно превышать семи.

– Имена других листов следует заключать в одинарные кавычки. Если формула содержит ссылки на значения ячеек других листов или книг, а имя другой книги или листа содержит небуквенные символы, это имя необходимо заключить в одинарные кавычки ( ' ).

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

– Вводите числа без форматирования. Не задавайте формат для чисел, вводимых в формулы. Например, даже если требуется ввести значение 1 000р., введите в формулу 1000.

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

Microsoft Excel отображает следующие ошибки

· Значение ошибки #####

Ошибка ##### появляется, когда используется формула, результат выполнения которой не умещается в ячейке.

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

· Значение ошибки #ДЕЛ/0!

Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на ноль.

Возможная причина и Предлагаемые действия:

· В качестве делителя используется ссылка на пустую ячейку или ячейку, содержащую ноль (если операнд должен быть числом, но ячейка пустая, ее содержимое интерпретируется как ноль).Измените ссылку или введите ненулевое значение в ячейку, используемую в качестве делителя. Значение ошибки

В формуле содержится явное деление на ноль (например, =5/0).Исправьте формулу.

· Значение ошибки #ЗНАЧ!

Ошибка #ЗНАЧ! появляется, когда используется недопустимый тип аргумента или операнда.

Возможная причина и Предлагаемые действия

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

· Ссылка на диапазон использована в качестве операнда выражения или аргумента функции, которые могут быть ссылками только на ячейку, причем ни одна ячейка этого диапазона не располагается в той же строке или столбце, что и данная формула. Замените ссылку на диапазон ссылкой на ячейку.

· Значение ошибки #ИМЯ?

Ошибка #ИМЯ? появляется, когда Microsoft Excel не может распознать имя (имя можно присвоить ячейке или диапазону ячеек и использовать в формулах), используемое в формуле.

Возможная причина и Предлагаемые действия:

· Используемое имя было удалено или не было определено, или имеется ошибка в написании имени.

· Имеется ошибка в написании имени функции.

· В формулу введен текст, не заключенный в двойные кавычки. Microsoft Excel пытается распознать такую запись как имя, даже если это не предполагалось.

· В ссылке на диапазон ячеек пропущен знак двоеточия (: ).

· Значение ошибки #Н/Д

Значение ошибки #Н/Д является сокращением термина “Неопределенные Данные”. Это значение помогает предотвратить использование ссылки на пустую ячейку. Введите в ячейки листа значение #Н/Д, если они должны содержать данные, но в настоящий момент эти данные отсутствуют. Формулы, ссылающиеся на эти ячейки, тоже будут возвращать значение #Н/Д вместо того, чтобы пытаться производить вычисления.

· Значение ошибки #ССЫЛКА!

Ошибка #ССЫЛКА! появляется, когда используется недопустимая ссылка на ячейку. Например нельзя указывать адрес текущей ячейки.

· Значение ошибки #ЧИСЛО!

Ошибка #ЧИСЛО! появляется, когда возникают проблемы при использовании чисел в формуле или функции. Например числа находятся вне в диапазоне от -1*10³⁰⁷до 1*10³⁰⁷.

· Значение ошибки #ПУСТО!

Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек.

ПОИСК ЯЧЕЕК, СТАВШИХ ПРИЧИНОЙ ОШИБОК В ФОРМУЛЕ

1. Выведите на экран панель инструментов Зависимости, выбрав в меню Сервис команду Зависимости, а затем — Панель зависимостей.

Перед тем как использовать панель инструментов Зависимости, установите флажки Отображать или Только очертания в группе Объекты на вкладке Вид диалогового окна Параметры (меню Сервис ).

2. Наведите указатель на ячейку, содержащую ошибку.

3. Нажмите кнопку Источник ошибки.

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

АНАЛИЗ ФОРМУЛ ( Поиск влияющих/зависимых ячеек )

Убедитесь, что на вкладке " Вид" окна диалога Параметры (меню Сервис) переключатель не установлен в положение " Скрывать все".

1 Укажите ячейку, содержащую формулу, ссылки которой необходимо проследить.

2 На панели инструментов " Зависимости", нажмите кнопку влияющие/зависимые, чтобы отобразить стрелку от первой влияющей/зависимой ячейки.

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

ПРИМЕРЫ ФУНКЦИЙ

СТАТИСТИЧЕСКИЕ ФУНКЦИИ.

СРЗНАЧ - Возвращает среднее (арифметическое) своих аргументов.

Синтаксис

СРЗНАЧ(число1; число2; ...)

Число1, число2, ... - это от 1 до 30 аргументов, для которых вычисляется среднее.

Замечания:

· Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.

· Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако, ячейки, которые содержат нулевые значения учитываются.

Совет: Вычисляя средние значения ячеек, следует учитывать различие между пустыми ячейками и ячейками, содержащими нулевые значения, особенно если не установлен флажок Нулевые Значения на панели Вид в диалоговом окне Параметры. Пустые ячейки не учитываются, но нулевые ячейки учитываются. Чтобы открыть диалоговое окно Параметры, выберите команду Параметры в меню Сервис.

Примеры

Если ячейки A1: A5 имеют имя Баллы и содержат числа 10, 7, 9, 27 и 2, то:

СРЗНАЧ(A1: A5) равняется 11

СРЗНАЧ(Баллы) равняется 11

МАКС(A1: A5) равняется 27

МАКС(A1: A5; 30) равняется 30

Если A1: A5 содержит числа 10, 7, 9, 27 и 2, то:

МИН(A1: A5) равняется 2

МИН(A1: A5; 0) равняется 0

МИН родственна функции МАКС. См. также примеры к функции МАКС.

МАТЕМАТИЧЕСКИЕ

ПРОИЗВЕД - Перемножает числа, заданные в качестве аргументов и возвращает их произведение.

Синтаксис

ПРОИЗВЕД(число1; число2; ...)

Число1, число2,... - это от 1 до 30 перемножаемых чисел.

Замечания

· Аргументы, которые являются числами, логическими значениями или текстовыми представлениями чисел учитываются; аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают ошибки.

· Если аргумент является массивом или ссылкой, то в массиве или ссылке учитываются только числа. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются.