Excel 2003: индикация ошибок
Уважаемая редакция! При работе с Excel на рабочем листе иногда возникают сообщения об ошибках (#Н/Д, #ЧИСЛО и т. п.). Что они означают и как быть в этой ситуации?
А. Фурсов, бухгалтер, г. Харьков
Отвечает Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
В этой ситуации ошибку нужно или исправить, или обработать. Указанные вами сообщения появляются в случае, когда возникает конфликт при обработке формул. Варианты могут быть разные. Например, неправильно напечатано имя функции или вы пытаетесь просуммировать текст. Это может быть некорректная ссылка или попытка деления на ноль. Наиболее распространенные причины приведены в табл. 1.
Таблица 1
Сообщения об ошибках вычислений в MS Excel
| Индикация | Проблема |
| #ИМЯ? | Некорректно записано имя функции (например, «ЕСЛ» вместо «ЕСЛИ»). Возможно, адрес ячейки введен кириллицей, пропущено двоеточие в диапазоне или в имени функции есть латинская буква вместо русской |
| #ЗНАЧ! | Попытка выполнить арифметические действия над ячейками, содержащими не числа, а текст |
| #ДЕЛ/0! | Деление на ноль. Нельзя делить на ячейку, в которой записан ноль или пустое значение |
| #ЧИСЛО! | Недопустимое число. Например, вы пытаетесь извлечь квадратный корень из отрицательного числа |
| #ССЫЛКА! | Неверная ссылка на ячейку. Чаще всего возникает после удаления одной или группы ячеек, на которые ссылается формула |
| #ПУСТО | Чаще всего ошибка возникает, когда указано пересечение двух областей, которые в действительности не имеют общих ячеек. Оператором пересечения областей является пробел между ссылками. Возможно, что в формуле вместо разделителя «;» (точка с запятой, объединение диапазонов) или вместо «:» (двоеточие, указатель непрерывного диапазона) напечатали пробел |
| #Н/Д | Данная ошибка говорит, что значение недоступно в функции или формуле. Вариантов здесь много. Вот несколько примеров. 1. В функциях поиска («ГПР()», «ВПР()», «ПРОСМОТР()», «ПОИСКПОЗ()») неверно указано искомое значение, поэтому поиск неуспешен. 2. Этими функциями выполняется поиск в неотсортированной таблице, а параметр «интервальный просмотр» установлен по умолчанию. 3. Неверно или не полностью определены аргументы функции |
В большинстве случаев ошибку, конечно же, нужно
исправить, т. е. проверить формулу, откорректировать ссылки, данные, параметры. Но так бывает не всегда. Например, при организации поиска «#Н/Д» — это тоже результат! Часто такой результат нужно обработать и выполнить определенные действия. Чаще всего требуется подавить выдачу сообщений и заменить их пустой строкой. Ситуации бывают разные, поэтому в MS Excel предусмотрен богатый арсенал для обработки ошибок вычислений. Рассмотреть его в полной мере мы не сможем, так как объем заметки не позволяет. Но основные моменты зафиксируем.Итак, ошибки можно обработать двумя способами:
1. «Поймать» ее в момент возникновения и заменить результат.
2. Проанализировать данные (ссылки, параметры)
до момента появления ошибки, т. е. предупредить ее возникновение. Это сделать сложнее и не всегда возможно. Теперь по порядку.Для «перехвата» ошибок в Excel есть несколько функций. Основные из них: «
ЕОШ(Ссылка)», «ЕОШИБКА(Ссылка)» — сигнализируют о факте возникновения ошибок. Аргумент «Ссылка» — это адрес на рабочем листе. Если результат работы функций «Истина», значит, в ячейке по адресу «Ссылка» есть ошибка. Характер и причину ошибки эти функции не покажут. Поэтому, на мой взгляд, более универсальной функцией обработки ошибок является «ТИП.ОШИБКИ (ЗначениеОшибки)». Ей можно указать адрес на рабочем листе, а функция вернет код ошибки. Если ошибки нет, результат функции будет «#Н/Д». Расшифровка кодов ошибок функции «ТИП.ОШИБКИ()» приведена в табл. 2.При обработке ошибок приходится принимать решения. Для этого понадобится функция «
Если()». Вот пример. В ячейке «А1» записана формула. Я хочу обработать ситуацию деления на ноль и выдать об этом сообщение. Иначе никакого сообщения выдавать не нужно. Функция «=ЕОШИБКА(A1)» в данном случае вернет результат «ИСТИНА». Формула «=ТИП.ОШИБКИ(A1)» будет равна «2». Выражение «=ЕСЛИ(ТИП.ОШИБКИ(A1)=2;"Деление на ноль";"")» выдаст предупреждение о делении на ноль. При возникновении других ошибок сообщения появляться не будут. Но есть одно «но». Если результат в «A1» будет правильный, то наша формула вернет «#Н/Д» (см. табл. 2)! Поэтому предварительно придется выполнить еще одну проверку содержимого в «A1». Логика проверки должна быть двухэтапной:— вначале функцией «
ЕОШИБКА(A1)» проверить наличие ошибки как таковой;— если ошибка есть, то выполнить ее анализ функцией «
ТИП.ОШИБКИ(A1)» и сделать окончательную обработку.
Таблица 2
Коды ошибок функции «
ТИП.ОШИБКИ()»| Причина ошибки | Результат работы функции |
| #ПУСТО! | 1 |
| #ДЕЛ/0! | 2 |
| #ЗНАЧ! | 3 |
| #ССЫЛ! | 4 |
| #ИМЯ? | 5 |
| #ЧИСЛО! | 6 |
| #Н/Д | 7 |
| Любое другое | #Н/Д |
То есть нам понадобится организовать проверку
двумя вложенными функциями «ЕСЛИ()». Формула при этом будет выглядеть так: «=ЕСЛИ(ЕОШИБКА (A1);ЕСЛИ(ТИП.ОШИБКИ(A1)=2;"Деление на ноль";"");"")». Она выдаст предупреждение о делении на ноль. При возникновении других ошибок сообщения появляться не будут.Чтобы
предупредить возникновение ошибок в вычислениях, можно использовать функции для проверки содержимого ячеек. Основные из таких функций приведены в табл. 3.
Таблица 3
Функции для проверки типа данных в MS Excel
| Функция | Назначение |
| « ЕПУСТО(Значение)» | Проверяет, указывает ли ссылка « Значение» на пустую ячейку. Если «Да», то результат «ИСТИНА», в противном случае результат «ЛОЖЬ» |
| « ЕССЫЛКА(Значение)» | Проверяет, является ли « Значение» ссылкой. Если «Да», то результат «ИСТИНА», в противном случае результат «ЛОЖЬ» |
| « ЕТЕКСТ(Значение)» | Проверяет, является ли « Значение» текстом. Если «Да», то результат «ИСТИНА», в противном случае результат «ЛОЖЬ» |
| « ЕЧИСЛО(Значение)» | Проверяет, является ли « Значение» числом. Если «Да», то результат «ИСТИНА», в противном случае результат «ЛОЖЬ» |
| « ТИП(Значение)» | Анализирует тип параметра « Значение» и возвращает результат в виде числа. Если результат равен «1», «Значение» является числом, если «2» — строкой, «4» — логическим значением, «16» — ошибкой, «64» — массивом значений |
Используя «
ЕСЛИ()» в сочетании с функциями для проверки типов данных, можно вовремя обнаружить источник возможной проблемы и предупредить ее возникновение.Надеюсь, что этот материал поможет вам успешно бороться с ошибками вычислений в MS Excel. Удачной работы!
Жду ваших писем, замечаний, вопросов и предложений на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .
