logo

Блог Александра Томма

О том, как заставить Microsoft Office работать на Вас

Ошибки в Excel: как их найти, понять и исправить

Ошибки в Microsoft Excel Дата: 24 декабря 2015 Категория: Excel
Поделиться, добавить в закладки или распечатать статью

Ошибки в Экселе – непременный спутник всех, кто пишет формулы. Когда выражение в ячейке рассчитать невозможно, программа отображает в ячейке сообщение об ошибке. Оно начинается со знака «#», после которого записывается имя ошибки. Этого не нужно пугаться, если вы ориентируетесь в функциях Эксель и умеете следовать простейшей логике математических операций – легко найдёте и исправите ошибку.

Если ячейка полностью заполнена знаками решётки (#), это вовсе не ошибка. В ячейке недостаточно места для отображения результата. Увеличьте размеры ячейки или уменьшите шрифт, чтобы результат мог отобразиться.

Типы ошибок

Если ошибка всё же произошла, помочь в её исправлении поможет расшифровка:

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

Когда в ячейке формула с ошибкой, возле нее появляется маркер. Нажав на него, вы можете почитать справку по этой ошибке. А еще можно посмотреть этапы вычисления. Выберите этот пункт, и программа покажет окно, где место ошибки будет подчёркнуто. Это лучший способ определить место, где возникает ошибка.

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

Отслеживание ошибка в Excel
Отслеживание ошибки через этапы вычисления

Циклические ссылки в Excel

Еще один тип ошибки – это циклическая ссылка. Она возникает, когда вы ссылаетесь на ячейку, значение которой зависит от той, в которой вы пишете формулу. Например, в клетке А1 записана формула =А2+1, а в А2 написать =A1, возникнет циклическая ссылка, которая будет пересчитываться бесконечно. В этом случае, программа предупреждает о появлении циклической ссылки, останавливает расчет «зацикленных формул». В левой части ячеек появляется двунаправленная стрелка. Придётся исправить возникшую ошибку и повторить расчет.

Циклическая ссылка в Excel
Циклическая ссылка в Excel

Иногда случается сложное «зацикливание», когда циклическая ссылка образуется с несколькими промежуточными формулами.

Чтобы отследить такие ошибки, выполните Формулы – Зависимости формул – Проверка наличия ошибок – Циклические ссылки. В выпадающем списке программа выводит адреса ячеек, создающих бесконечный цикл. Остаётся только исправить формулы в этих клетках.

Отслеживание циклических ссылок в Excel
Отслеживание циклических ссылок

В Excel можно попытаться рассчитать результат зацикленных формул. Для этого установите галочку Файл – Параметры – Формулы – Включить итеративные вычисления. В этом же блоке можно задать максимальное количество итераций (просчётов) для нахождения баланса и допустимую погрешность. В большинстве случаев этого делать не нужно, поэтому я рекомендую эту галку не устанавливать. Тем не менее, когда Вы знаете, что зацикленные формулы верны и их расчет приведет к устойчивому результату — почему бы это не сделать?

Вот и всё про типы ошибок в Эксель. В этой короткой статье вы получили достаточно информации, чтобы справиться с самыми распространенными ошибками в Excel путем анализа возвращаемого значения. А вот расширенный перечень ошибок читайте в моей новой статье! Готов ответить на ваши вопросы – пишите в комментариях.

В следующей статье я расскажу о применении функций в расчетах. Стоит ли говорить, что функции Эксель — «наше всё»?
Думаю, нет. Потому, переходите и читайте, это будет первый шаг в мир сложных формул с правильными результатами!

Поделиться, добавить в закладки или распечатать статью

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

2 комментариев

  1. Что делать, если при копировании ячейки с одного листа на другой в одной книге идет ошибка ССЫЛКА ?
    Активных надстроек нет, Chrome отключен!

    Ответить
    1. Владимир, здравствуйте. Эта ошибка означает, что формула в ячейке ссылается на несуществующую ячейку, или адрес ячейки указан неверно. Например, исходная формула находится в ячейке B1 и ссылается на A1, т.е. на ячейку слева от текущей. Если в другой книге Вы вставляете эту формулу в А1, формула попытается сослаться на ячейку слева, но такая не существует. Тогда возникает ошибка «ССЫЛКА». Нужно внимательно проверить формулу. Если ссылки на ячейки должны сохранять свои координаты — запишите знаки доллара перед измерениями. Например, =$A$1. Подробнее о ссылках прочитайте здесь. Если не получится решить проблему — опишите подробнее и разберемся окончательно.

      Ответить