InformatikUrok

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

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

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

#СЛОВО!  - это и есть ошибка в формуле. Excel не все ошибки умеет определять, но те, которые он хорошо находит – может и исправить, а для этого нам самим нужно научиться распознавать ошибки и уметь просматривать источник ошибки в Excel 2010. Рассмотрим несколько типичных ошибок на примерах.

Пример №1.

Создать в Excel 2010 таблицу продаж автомобилей с названием столбцов: Продавец, Тип автомобиля, Число проданных единиц, Цена единицы, Итоги продаж. Задание:

• Заполнить таблицу данными.
• В столбце Цена единицы указать обозначение валюты, например, «грн».
• Посчитать Итоги продаж (в столбце E ввести формулу =C2*D2).

Рассмотрим типичные проблемы.

Проблема 1: почему в ячейке Е2 ошибка – #ИМЯ?

В ячейке Е2 вроде бы правильная формула стоит: c2*d2, но почему Excel 2010 выдает ошибку #ИМЯ? Потому что ссылка на ячейку с2 набрана русскими буквами, а в формуле можно писать только английскими!!

Для исправления этой ошибки, нужно выделить ячейку Е2, для редактирования нажать F2, удалить ссылку с2, и щелкнуть ЛКМ по ячейке С2, enter.  Excel сам напишет правильное название ячейки!

Проблема 2.

Вот, что может еще получиться: в ячейке Е2 появилась ошибка #ЗНАЧ! Что это за ошибка? Почему в ячейке Е3 стоит значение (число)?

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

Визуально данные в D2 и D3 для нас не отличаются (даже если поставить пробел в D2 между 2200 и грн, все равно в Е2 будет ошибка), а вот Excel 2010 эти данные «видит» по-разному: в D2 – текст (скорее всего «грн» вводилось после числа вручную, поэтому и ошибка!!!!), а в D3 – денежный формат числа (поэтому Excel вычислил формулу!!!) Как это исправить?

Но прежде, чем редактировать, давайте посмотрим, источник ошибки, для этого

• нажмем на Меню кнопки (зелененький треугольник в левом углу ячейки Е2);
• из предложенного списка выберем Показать этапы вычислений.

В результате на экране появится окно:

В этом окне чётко видно, что в ячейке перемножается число 5 на текст в кавычках «2200 грн». Не видно? Тогда нажимайте кнопку Вычислить, и в этом окне будет поэтапное вычисление формулы: на первом шаге 5*D2, на втором -  5*«2200 грн» и ответ -  #ЗНАЧ! Как это исправить?

Все просто, нужно:

• Отредактировать данные в ячейке D2, удалив текст грн.
• Изменить формат числа -  Денежный и выбрать Обозначение – «грн украинский».

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

Пример №2.

Для таблицы из предыдущего примера №1 пересчитать Итоги продаж в долларах. В ячейке H2 записать курс $ НБУ.

В ячейке F2 задана формула =E2/$H$2, и вместо результата вычислений появилась ошибка #ДЕЛ/0! Эта ошибка появляется, когда в формуле есть деление на ноль. Если проверить ячейку H2, то легко обнаружить, что в ней нет данных и поэтому нет результата в F2. Для исправления этой ошибки достаточно ввести в ячейку H2 число, например, 8,36.

Подведем итог. Сегодня на занятии мы узнали:

• Как выглядят ошибки в Excel.
• Как просмотреть источник ошибки в Excel, например, #ЗНАЧ!
• Как исправить ошибки #ИМЯ, #ЗНАЧ!, #ДЕЛ/0!