Коротко. Самые частые ошибки в Excel: #ЗНАЧ! — вместо числа введён текст; #Н/Д — формула не нашла нужное значение; циклическая ссылка — формула ссылается сама на себя; ##### — столбец слишком узкий для числа. Ниже — как найти причину и исправить каждую из них.
Виды ошибок в Excel: таблица с расшифровкой
Идентификация ошибок осуществляется несколькими способами. Один из них реализуется через отображение кода ошибки в ячейке.
| Ошибка | Описание |
| #Н/Д | Н/Д – является сокращением термина Неопределённые данные. Помогает предотвратить использование ссылки на пустую ячейку |
| #ЧИСЛО! | Функция с числовым аргументом использует неприемлемый аргумент |
| #ИМЯ? | Ошибка в написании имени или используется несуществующее имя |
| #ССЫЛКА! | Используется ссылка на несуществующую ячейку |
| #ЗНАЧ! | Вместо числового или логического значения введён текст, и Excel не может преобразовать его к нужному типу данных |
| #ДЕЛ/0! | В качестве делителя используется ссылка на ячейку, в которой содержится нулевое или пустое значение (если ссылкой является пустая ячейка, то её содержимое интерпретируется как ноль) |
| #ПУСТО! | Используется ошибочная ссылка на ячейку |
| ############# | Данные не помещаются в ячейку по ширине, следует увеличить ширину столбца или применить более компактное форматирование |
Ошибка ЗНАЧ! в Excel: что означает и как исправить
Ошибка #ЗНАЧ! появляется, когда формула получает на вход текст там, где нужно число или логическое значение — например, при сложении числа и ячейки с текстом или пробелом. Проверьте аргументы формулы: откройте вкладку Формулы → Вычислить формулу, чтобы увидеть, на каком шаге вычисления появляется ошибка, и замените текстовое значение на число или используйте функцию ЕСЛИ с проверкой типа данных.
Ошибка #Н/Д в Excel: что означает
#Н/Д — сокращение от «нет данных». Ошибка появляется у функций поиска (ВПР, ПОИСКПОЗ, ИНДЕКС), когда искомое значение не найдено в диапазоне: из-за опечатки, лишнего пробела, несовпадения формата (число как текст) или потому что значения в таблице действительно нет. Оберните формулу в ЕСНД или ЕСЛИОШИБКА, чтобы вместо ошибки выводить понятный текст или 0.
Что означает ##### в ячейке Excel
Строка из решёток — не ошибка вычисления, а сообщение о том, что ширины столбца не хватает для отображения числа или даты. Достаточно расширить столбец вручную или дважды щёлкнуть по границе заголовка столбца для автоподбора ширины.
Второй способ обнаружения ошибок – Excel отображает в левом верхнем углу ячейки зелёный треугольник (индикатор ошибки). При выборе такой ячейки появляется смарт-тег проверки ошибок.
Проверка формул на ошибки: пошаговая инструкция
Для проверки ошибок необходимо выполнить следующие шаги:
1. Выберите лист, который требуется проверить на наличие ошибок.
2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Проверка наличия ошибок. Откроется окно диалога Контроль ошибок.
3. В окне диалога Контроль ошибок просмотрите информацию о текущей ошибке в левой части окна.
4. Для просмотра более детального описания ошибки и возможных вариантов её исправления нажмите кнопку Справка по этой ошибке.
5. Нажмите кнопку Показать этапы вычисления. MS Excel откроет окно диалога Вычисление формулы, где вы сможете просмотреть значения различных частей вложенной формулы, вычисляемые в порядке расчёта формулы:
a) нажмите кнопку Вычислить, чтобы проверить значение подчёркнутой ссылки. Результат вычислений показан курсивом;
b) если подчёркнутая часть формулы является ссылкой на другую формулу, нажмите кнопку Шаг с заходом, чтобы отобразить другую формулу в поле Вычисление. Нажмите кнопку Шаг с выходом, чтобы вернуться в предыдущую ячейку и формулу;
c) Выполняйте указанные действия, пока не будет вычислена каждая часть формулы;
d) Чтобы снова увидеть вычисления, нажмите кнопку Заново;
e) Чтобы завершить вычисления, нажмите кнопку Закрыть.
6. Для изменения формулы в строке формул нажмите кнопку Изменить в строке формул.
7. Если ошибка является несущественной, вы можете нажать кнопку Пропустить ошибку. Помеченная ошибка при последующих проверках будет пропускаться.
8. Для перехода к следующей ошибке нажмите кнопку Далее. Для возврата к предыдущей – кнопку Назад.
9. Доведите до конца проверку ошибок и закройте окно диалога Контроль ошибок.
Трассировка связей между формулами и ячейками
Для удобства поиска причин ошибок, а также и в некоторых других случаях можно графически отобразить связь между ячейками в формуле.
Для отображения ячеек, входящих в формулу в качестве аргументов, необходимо выделить ячейку с формулой и нажать кнопку Влияющие ячейки в группе Зависимости формул вкладки Формулы.
Один щелчок по кнопке Влияющие ячейки отображает связи с ячейками, непосредственно определяющими результат вычисления. Если в этих ячейках также находятся формулы, то следующий щелчок отображает связи с ячейками, являющимися аргументами этих формул. И так далее.
Для отображения ячеек, в формулы которых входит какая-либо ячейка, её следует выделить и нажать кнопку Зависимые ячейки в группе Зависимости формул вкладки Формулы.
Один щелчок по кнопке Зависимые ячейки отображает связи с ячейками, непосредственно зависящими от выделенной ячейки. Если эти ячейки также влияют на другие ячейки, то следующий щелчок отображает связи с зависимыми ячейками. И так далее.
Связи в пределах текущего листа отображаются синими стрелками. Связи с ячейками других листов и книг отображаются чёрными пунктирными линиями и значком листа. Красные стрелки показывают ячейки, вызывающие ошибки.
Для скрытия стрелок связей следует нажать кнопку Убрать все стрелки в группе Зависимости формул вкладки Формулы. Использование окна контрольных значений.
Если вы работаете с большой таблицей, содержащей формулы, которые ссылаются на различные ячейки текущей таблицы и/или ячейки другого листа, достаточно сложно отследить их взаимосвязи.
В этом случае вашим помощником может выступать панель инструментов Окно контрольного значения.
Окно контрольного значения значительно упрощает анализ, проверку зависимостей и подтверждение вычислений и результатов формул на больших листах. Благодаря окну контрольного значения не требуется постоянно прокручивать лист, когда вы работаете с большой таблицей, переходить к различным его частям.
Добавление ячеек в окно контрольных значений
Для добавления на панель контрольных значений выполните следующие действия:
1. Выделите ячейки, контрольные значения которых нужно поместить на панель.
Чтобы выделить все ячейки листа с формулами, на вкладке Главная в группе Правка нажмите кнопку Найти и выделить и выберите команду Формулы.
2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Окно контрольного значения.
3. В окне диалога Добавление контрольного значения проверьте правильность выбранного диапазона и нажмите кнопку Добавить.
Циклическая ссылка в Excel: как найти и убрать
Циклические ссылки возникают, когда формула в какой-либо ячейке посредством других ячеек ссылается сама на себя.
Например, ячейка С4 = Е7, Е7 = С11, С11 = С4. В итоге С4 ссылается на С4.
Появление циклических ссылок очень легко определить. При их возникновении или наличии в уже созданной книге Excel сразу же появляется предупреждение о циклической ссылке, которое и описывает суть явления.
При нажатии на кнопку OK сообщение будет закрыто, а в ячейке, содержащей циклическую ссылку, в большинстве случаев появится 0.
Предупреждение, как правило, появляется при первоначальном создании циклической ссылки или открытии книги, содержащей циклические ссылки. Если предупреждение принято, то при дальнейшем возникновении циклических ссылок оно может не появляться.
Циклические ссылки в Excel могут создаваться преднамеренно, для решения тех или иных задач финансового моделирования, а могут возникать случайно, в виде технических ошибок и ошибок в логике построения модели.
В первом случае мы знаем об их наличии, так как сами их предварительно создали, и знаем, зачем они нам нужны.
Во втором случае мы можем вообще не знать, где они находятся, например, при открытии чужого файла и появлении сообщения о наличии циклических ссылок.
Найти циклическую ссылку можно несколькими способами. Например, чисто визуально формулы и ячейки, участвующие в образовании циклических ссылок в Excel, отмечаются синими стрелками.
Если циклическая ссылка – одна на листе, то в строке состояния будет выведено сообщение о наличии циклических ссылок с адресом ячейки.
Если циклические ссылки есть ещё и на других листах кроме активного, то будет выведено сообщение без указания ячейки.
Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.
На вкладке Формулы в группе Зависимости формул выберите элемент Поиск ошибок и в раскрывающемся списке пункт Циклические ссылки.
Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После её корректировки или удаления – со второй и т. д.
Почему Excel не считает формулу: основные причины
Если формула не выдаёт ошибку, но и не считает — то есть просто показывает текст формулы или старое значение, — чаще всего дело в одной из этих причин:
- Ячейка отформатирована как текст — переключите формат на «Общий» и заново введите формулу.
- Формула введена с апострофом в начале ('=A1+A2) — Excel показывает её как текст, апостроф нужно убрать.
- Включён ручной пересчёт книги — на вкладке Формулы → Параметры вычислений выберите «Автоматически» или нажмите F9.
- В ячейке есть лишний пробел или скрытый непечатаемый символ перед числом — используйте функцию СЖПРОБЕЛЫ.
- В формуле опечатка в имени функции или лишняя/недостающая скобка — Excel подсветит проблемное место при редактировании формулы.
Итак, используя вышеперечисленные инструменты и приёмы, вы сможете облегчить поиск, исправление и предупреждение ошибок в рабочих книгах Excel.
Успехов!
Частые вопросы
Что означает ошибка ЗНАЧ! в Excel?
Excel не может преобразовать текстовое значение в число или логическое значение внутри формулы. Проверьте, не попал ли текст, пробел или пустая ячейка в аргумент, где ожидается число.
Как быстро найти циклическую ссылку в Excel?
Откройте вкладку Формулы → Поиск ошибок → Циклические ссылки. Excel покажет адрес первой ячейки с циклической ссылкой; после исправления заново откройте список, чтобы найти следующую.
Можно ли использовать циклические ссылки специально?
Да, для некоторых задач финансового моделирования это допустимо, но тогда нужно включить итеративные вычисления на вкладке Файл → Параметры → Формулы, иначе Excel будет выдавать предупреждение и обнулять результат.
Что делать, если формула в Excel не считает?
Проверьте формат ячейки (не текстовый ли), уберите апостроф перед формулой, если он есть, и убедитесь, что включён автоматический пересчёт книги на вкладке Формулы.
Чем ошибка #Н/Д отличается от #ЗНАЧ!?
#Н/Д означает, что искомое значение не найдено функцией поиска (например, ВПР). #ЗНАЧ! означает, что в формулу попал аргумент неподходящего типа — например, текст вместо числа.
Как убрать сообщение об ошибке, не убирая саму формулу?
Оберните формулу в функцию ЕСЛИОШИБКА или ЕСНД: вместо кода ошибки ячейка покажет заданный вами текст или число, а сама формула останется рабочей.
