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

Ошибки синтаксиса

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

При обнаружении лишних символов операторов, лишних или недостающих скобок и т. п. выводится сообщение об ошибке (Рис. 14).

Рис. 14. Окно сообщения об ошибке

Ошибки в функциях и аргументах

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

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

Таблица 7 – Коды ошибок

Ошибка	Причина
#ЗНАЧ!	Использование недопустимого типа аргумента или операнда.
#ДЕЛ/0!	Деление числа на 0 (ноль).
#ИМЯ?	Excel не может распознать имя, используемое в формуле.
#Н/Д	Значение недоступно функции или формуле
#ССЫЛКА!	Ссылка на ячейку указана неверно
#ЧИСЛО!	Неправильные числовые значения в формуле или функции.
#ПУСТО!	Задано пересечение двух областей, которые в действительности не имеют общих ячеек

Трассировка связей между формулами и ячейками

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

Можно просмотреть все ячейки, которые непосредственно влияют на результат вычислений. Для этого необходимо выделить ячейку с формулой и нажать кнопку Влияющие ячейки в группе Зависимости формул вкладки Формулы.

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

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

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

Рис. 15. Отображение ячеек влияющих на ячейку D4

Использование встроенных функций

Суммирование

СУММ(А)

где A – список от 1 до 30 элементов, которые требуется суммировать. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются. Пример: =СУММ(В2:В7)

Фактически то же самое, что и =В2+В3+В4+В5+В6+В7. Однако есть и некоторые отличия. При использовании функции СУММ добавление ячеек в диапазон суммирования автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон суммирования. Аналогично формула будет изменяться и при уменьшении диапазона суммирования.

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

СТЕПЕНЬ(А;В)

где A – число, возводимое в степень; В – показатель степени, в которую возводится число. Пример: =СТЕПЕНЬ(2;128)

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

Извлечение квадратного корня

КОРЕНЬ(А)

где A – число, из которого извлекают квадратный корень. Пример: =КОРЕНЬ(C4)

Если же требуется извлечь, кубический корень из числа, следует воспользоваться математическим понятием, согласно которому извлечение кубического корня равнозначно возведению числа в степень 1/3. Поэтому вычисление кубического корня из 27 выполняется по формуле =27^(1/3). Этот принцип универсален: он позволяет находить корень любой степени через возведение числа в соответствующую дробную степень.

Нельзя извлекать корень из отрицательных чисел!

Тригонометрические вычисления

TAN(А)

где A – угол в радианах, для которого определяется тангенс. Пример: =SIN(20)

В Microsoft Excel можно выполнять тригонометрические вычисления SIN, COS, TAN , то есть, зная значение угла, находить значения тригонометрических функций. Синтаксис всех прямых тригонометрических функций одинаков.

Логические вычисления

ЕСЛИ(А;В;С)

где A – логическое выражение, правильность которого следует проверить; В – значение, если логическое выражение истинно; C – значение, если логическое выражение ложно. Пример: =ЕСЛИ(E3>0;"плюс";"минус")

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

Например, при расчете стоимости товара цена зависит от объема партии товара. При объеме партии более 30 цена понижается на 10%. Следовательно, при выполнении условия используется формула цена*количество*0,9, а при невыполнении условия – цена*количество

=ЕСЛИ(E3>30;" D2*E3*0,9 ";" D2* E3").