В мир информатики # 92 (16–31 мая).
Microsoft Excel углубленно

Ошибки формул в Microsoft Excel

При проведении расчетов в электронной таблице Microsoft Excel иногда в ячейках вместо ожидаемых значений выводятся различные “непонятные” результаты. В настоящей статье мы попытаемся сделать их для вас понятными.

Ячейки заполнены знаками # # # # # # # # # #

Ячейка может заполняться набором символов “решетки” (#²) по одной из следующих причин:

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

2) ячейка содержит формулу, возвращающую некорректную дату или время. Excel, например, не поддерживает даты до 1900 года (см. [1]) или отрицательные значения времени. Увеличение ширины ячейки данную ошибку не исправляет.

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

О том, что делить на ноль нельзя, знают даже учащиеся начальных классов. Но тем не менее такая ситуация в Excel встречается довольно часто. Например, на листе, подготовленном для решения линейного уравнения вида ax + b = 0:

— формула в ячейке B4, имеющая вид =–B3/B2, вернет значение ошибки #ДЕЛ/0! в ситуации, когда значение коэффициента а в ячейке B2 еще не задано. Для исключения вывода ошибки можно воспользоваться функцией ЕСЛИ, которая проверяет наличие “неправильного” значения в ячейке B2:

=ЕСЛИ(B2=0; ““; –B3/B2)

Данная формула выводит “пустое” значение (““), если ячейка пустая или содержит 0; в противном случае выводится вычисленное значение корня уравнения.

Другое решение заключается в использовании функции ЕСЛИ для проверки существования любой ошибки. Следующая формула также отображает “пустое” значение в случае получения ошибки любого типа:

=ЕСЛИ(ЕОШИБКА(–B3/B2); ““; –B3/B2)

С особенностями функции ЕОШИБКА ознакомьтесь самостоятельно.

Ошибка Имя?

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

=ЕСЛИ(a=0; ““; –b/a)

— где a и b — имена ячеек, в которых задаются значения коэффициентов уравнения a и b соответственно, выглядит гораздо понятнее приведенного ранее варианта. О том, как присвоить имя ячейке или диапазону ячеек, см., например, [2].

Ошибка Имя? возникает в следующих случаях:

1) формула содержит неопределенное имя ячейки или диапазона. Здесь же следует отметить одну особенность Excel. Если удалить имя какой-то ячейки или какого-то диапазона, используемое в формуле, то в самой формуле оно останется (казалось бы, Excel мог бы преобразовать имена в ссылки на соответствующие ячейки, однако этого почему-то не происходит);

2) формула содержит текст, который Excel интерпретирует как неопределенное имя. Например, ошибка в написании имени функции приводит к отображению ошибки Имя?.

Ошибка #Знач!

Ошибка весьма распространена и встречается, как правило, в следующих ситуациях.

1. Формула пытается провести операцию, используя некорректные данные (например, пытается сложить число и текстовое значение). Если на листе в ячейке B7 (см. фрагмент листа ниже) была записана формула =B3+B4+B5+B6, которая затем была распространена (скопирована) на ячейки C7 и D7, то для приведенных исходных данных в ячейке будет выведен результат #Знач! (ясно, что складывать числа и текст нельзя).

Использование функции СУММ позволит найти сумму числовых значений в диапазоне, в котором имеются и текстовые значения: =СУММ(D3:D6). Это означает, что в ячейке B7 можно записать формулу =СУММ(B3:B6) и распространить (скопировать) ее на ячейки C7 и D7.

2. В качестве аргументов функции использованы данные несоответствующего типа (например, в функции ЕСЛИ вместо условия использовано число или текст).

3. В качестве аргумента функции используется диапазон, тогда как аргументом должна быть отдельная ячейка. Пример ошибочного оформления: =КОРЕНЬ (A3:A6).

Ошибка #Число!

Ошибка #Число! возникает в одном из двух случаев:

1) для функции, использующей числовой аргумент, задан аргумент другого типа. Пример ошибочного оформления: =КОРЕНЬ(И3);

2) формула содержит слишком большое или слишком маленькое значение. Excel поддерживает величины в пределах от 1Е-307 до 1Е307 (от 10-307 до 10307).

Ошибка #Ссылка!

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

1. Ячейка, на которую ссылалась формула, была удалена. Например, следующая формула отображает ошибку #Число!, если удалена строка 20, столбец A или столбец B:

=A20/B20

2. Формула скопирована в новое место, где относительные ссылки на ячейки становятся недействительными. Например, если формулу =A1–1 в ячейке A2 скопировать в ячейку A1, формула вернет ошибку #Число!, так как в ней будет присутствовать ссылка на несуществующую ячейку:

3. Содержимое ячейки с формулой было вырезано (командой Правка | Вырезать) и затем вставлено в ячейку, на которую ссылается формула.

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

Литература

1. Златопольский Д.М. Работа с данными типа “дата” в электронной таблице Microsoft Excel. / “Информатика” № 4/2001.

2. Медведев Л.Н. Модель колеса. Часть 2. / “В мир информатики” № 82 (“Информатика” № 23/2006).

² Название символа — диез