Для расчета данных таблиц в Excel используются формулы.

Формула – это последовательность значений, ссылок на ячейки, имен, функций и операторов, которые задаются в ячейке. Результат вычислений формулы отображается значением в ячейке, которая содержит формулу. Синтаксисом формул называют порядок, в котором располагаются элементы формулы и вычисляется ее значение. Формула всегда начинается со знака «=». Последовательность вычислений указывается расположением элементов в формуле. Кроме этого для указания приоритета вычисления используются круглые скобки, куда помещают операции, которые необходимо выполнить первыми. Для того чтобы ввести в ячейку формулу, нужно набрать ее в строке формул (рис. 13.35) и нажать клавишу «Enter».

Рис. 13.35 Вид строки для формул:

1 – функция; 2 – вложенная функция;

3 – операторы; 4 – ссылки; 5 – значения

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

Ссылкой является указание адреса ячейки или группы ячеек. С помощью ссылки можно использовать в формуле значения других ячеек. Ссылки бывают относительные и абсолютные. Относительные ссылки используются для обозначения ячеек в относительной системе координат (вида А1), абсолютные – в абсолютной системе координат (вид $А$1).

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

Чтобы указать ссылку на другой лист, на требуемый лист и выделить ячейку или диапазон ячеек, на которые нужно сослаться. Держа при этом клавишу «Shift», вы сможете указать ячейки и на других листах. Таким же образом можно сослаться на ячейки из другой книги. Это бывает нужно, когда вы производите расчеты с данными из других таблиц, так называемыми «сводными».

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

1. Арифметические операторы

«+» – сложение; «-» – вычитание или обозначение отрицательности числа; «/» – деление; «*» – умножение; «%» – процент; «^» (крышка) – возведение в степень;

Таблица 13.1 Формулы

Формулы в обычной записи	Формула в строке формул
	=7+5^3/(6*8)
	=A5/(C7-4)+(4+F4)/(8-D5)*2,4
	2+(SIN(D3)^2)

2. Операторы сравнения

«=» – равно; «>» – больше; «<» – меньше; «>=» – больше или равно; «<=» – меньше или равно; «<>» – не равно;

3. Текстовый оператор: «&» (амперсанд) – объединение последовательности символов в одну последовательность;

=”Петр”&”Кузнецов” — фраза «Петр Кузнецов»

4. Адресные операторы

«:» – ссылка на все ячейки между границами диапазона включительно;

=СУММ(A1:F1) – сумма диапазона ячеек A1 и F1.

«;» – ссылка только на конкретные ячейки

=СУММ(A1;F4) – сумма ячеек A1 и F4.

«,» – ссылка на объединение ячеек диапазонов;

пробел – ссылка на общие ячейки диапазонов.

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

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

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

Функцией называют стандартные формулы, упрощающие работу с различными вычислениями. Величины, которые используются для вычисления значений, называются аргументами (константы, текст, ссылки, другие формулы и т.д.). На рис. 13.36 аргументами функции СРЗНАЧ являются две функции СУММ, для которых аргументами являются ссылки 4 и константы 5. Величины, которые являются результатом функции, называются возвращенными значениями. Последовательность, в которой должны располагаться аргументы, и способ их оформления называется синтаксисом функции.

Для редактирования формул используется панель формул изображенная на рис. 13.36.

Рис. 13.36 Панель формул:

1 – список функций, 2 – изменить формулу,

3 – редактируемая формула, 4 – панель изменения функции

Команда «Функция…» из меню «Вставка» позволяет вставить в формулу функцию, при этом вызывается диалоговое окно «Мастер функций». В Excel 2007 рис. 12.37.

На первом этапе необходимо выбрать категорию, к которой относится необходимая вам функция (список «Категория») и саму функцию (список «Функция»). В нижней части этого окна описано, какое значение возвращает выделенная функция.

На втором этапе нужно ввести значение аргумента или аргументов, если их несколько, в зависимости от функции.

Виды ошибок при задании формул

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

Первым символом значения ошибки является символ диез (#), за которым следует текст. Текст значения ошибки может завершаться восклицательным знаком или знаком вопроса.

Ниже приводится список значений ошибок с пояснением наиболее распространенных причин их возникновения и указанием мер по их устранению.

Ошибка #####

Причины возникновения ошибки

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

2. Используется формула, результат выполнения которой не умещается в ячейке.

Меры по устранению ошибки – увеличьте ширину столбца путем перемещения границы, расположенной между заголовками столбцов. Кроме того, можно изменить формат числа ячейки, для чего следует выбрать команду Ячейки… в меню Формат, затем — вкладку Число и указать другой формат.

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

Рис. 13.37 Вставка функции из «Мастера функций»

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

Ошибка #ЗНАЧ!

Причины возникновения ошибки

1. Вместо числового или логического значения введен текст, и Excel не может преобразовать его к нужному типу данных.

Меры по устранению ошибки — проверьте в формуле правильность задания типов аргументов. Например, если в ячейке А1 содержится число 5, в ячейке В1 — текстовое значение «Привет», а в ячейке CI — формула =А1+В1, то в ячейке С будет выведена ошибка #ЗНАЧ!. Если все же необходимо сложить два таких значения, то следует использовать функцию СУММ (функция СУММ игнорирует текстовые значения). Для рассматриваемой ситуации функция = СУММ(А1:В1) рассчитывает значение 5.

2. После ввода или редактирования формулы массива нажимается клавиша Enter.

Меры по устранению ошибки – для редактирования формулы укажите ячейку или диапазон ячеек, содержащих формулу массива, нажмите клавишу F2, а затем – комбинацию клавиш Ctrl+Shift +Enter.

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

Меры по устранению ошибки – укажите правильную размерность матрицы данных.

Ошибка #ДЕЛ/0!

Причины возникновения ошибки

1. В качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение (если аргумент является пустой ячейкой, то ее содержимое интерпретируется как нуль). Такая ситуация чаще всего возникает случайно: например, если ячейка содержит формулу =А1/В1, а содержимое ячейки В1 по какой-либо причине было удалено.

Меры по устранению ошибки – измените ссылку или введите ненулевое значение в ячейку, используемую в качестве делителя.

Кроме того, в качестве делителя можно ввести значение #Н/Д. В этом случае ошибка #ДЕЛ/0! сменится на #Н/Д, указывающую, что значение делителя не определено. Рис.

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

Меры по устранению ошибки – исправьте формулу

Ошибка #ИМЯ?

Причины возникновения ошибки

1. Используемое в формуле имя было удалено или не было определено.

Меры по устранению ошибки – определите имя. Для этого выберите команду Имя в меню «Вставка», а затем – команду «Создать…». Кроме того, команда «Создать…» используется для добавления имени, отсутствующего в списке.

2. Имеется ошибка в написании имени.

Меры по устранению ошибки — исправьте написание имени.

Чтобы вставить правильное имя в формулу, выделите имя в строке формул, выберите команду Имя в меню «Вставка», а затем – команду «Вставить». На экране появится диалоговое окно «Вставка имени». Выделите нужное имя и щелкните по кнопке ОК.

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

Меры по устранению ошибки — исправьте написание имени

функции вручную или вставьте функцию с помощью мастера функций.

4. В формулу введен текст, не заключенный в двойные кавычки.

Excel пытается распознать такой текст как имя, хотя это не предполагается.

Меры по устранению ошибки — заключите текст формулы в двойные кавычки. Например, если в ячейке А1 содержится значение 200, а в ячейке В1 — формула =«Итого:»&А1, то в ячейке В1 будет выведен результат Итого:200.

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

Меры по устранению ошибки –  исправьте формулу так, чтобы во всех ссылках на диапазон ячеек использовался знак двоеточия (:), например =СУММ(А1:С10).

Ошибка #Н/Д

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

Формулы, ссылающиеся на эти ячейки, тоже будут иметь значение #Н/Д.

Причины возникновения ошибки

1. Для функций ГОР, ПРОСМОТР, ПОИСКПОЗ или ВПР (функции ссылки и автоподстановки) задан недопустимый аргумент искомое значение.

Меры по устранению ошибки – задайте правильный аргумент искомое значение, например значение или ссылку, но не диапазон ссылок.

2. Функции ВПР или ГПР используются для обработки неотсортированной таблицы.

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

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

Меры по устранению ошибки – если формула массива введена в несколько ячеек, проверьте диапазон ссылок формулы на соответствие числу строк и столбцов или введите формулу массива в недостающие ячейки. Например, если формула массива введена в первые 15 ячеек столбца С (С1:С15), а сама формула ссылается на первые 10 ячеек столбца А (А1:А10), то в ячейках CI 1:С15 будет отображаться ошибка #Н/Д. Чтобы исправить эту ошибку, уменьшите диапазон в формуле (например, С1:С10) или увеличьте диапазон, на который ссылается формула (например, А1:А15).

4. Не заданы один или несколько аргументов стандартной или пользовательской функции листа.

Меры по устранению ошибки – задайте все необходимые аргументы функции.

5. Используется пользовательская функция, обращение к которой приводит к ошибке.

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

Ошибка #ССЫЛКА!

Причина возникновения ошибки

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

Меры по устранению ошибки — измените формулы или сразу же после удаления или вставки скопированного восстановите прежнее содержимое ячеек с помощью кнопки Отменить

Ошибка #ЧИСЛО!

Причины возникновения ошибки

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

Меры по устранению ошибки – проверьте правильность использования в функции аргументов.

2. Задана функция (например, статистическая функция СТЬЮДРАСПОБР(вероятность;степени свободы), при вычислении которой используется итерационный процесс. При этом итерационный процесс не сходится и результат не может быть получен.

Меры по устранению ошибки – используйте другое начальное приближение для этой функции.

3. Введена формула, рассчитывающая числовое значение, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.

Меры по устранению ошибки – измените формулу так, чтобы в результате ее вычисления получалось число, попадающее в диапазон от – 1*10³⁰⁷ до 1*10³⁰⁷. Например, число 200 является слишком большим, чтобы быть использованным в качестве аргумента функции ФАКТР (функция вычисления факториала числа), поэтому формула =ФАКТР(200) помещает в ячейку значение ошибки #ЧИСЛО!

Ошибка #ПУСТО!

Причина возникновения ошибки

Использован оператор, задающий пересечение диапазонов, не имеющих общих ячеек.

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

В Excel оператором пересечения диапазонов является пробел (). Например, диапазоны А1:А5 и В1:В5 содержат массивы единиц. В этом случае формула =СУММ(А1:А5; В1:В5) будет выдавать значение ошибки #ПУСТО!, а формула =СУММ(А1:А5;А1:ВЗ) рассчитает значение 3. Для суммирования непересекающихся диапазонов А1:А5 и В1:В5 необходимо воспользоваться стандартной синтаксической конструкцией функции СУММ, т.е. =СУММ(А1А5;В1:В5), которая рассчитает значение 10.