Зачастую Excel выдает ошибки при вычислениях даже у опытных пользователей. Ошибки закодированы в различные наименования, по которым можно понять, в чем именно мы ошиблись. В этом статье рассмотрим виды ошибок в excel, а также что делать, если возникла ошибка в формуле excel и как ее убрать.
Ошибка Н/Д в Excel
Н/Д — ошибка “нет данных”. Н/Д означает, что искомое значение не было найдено в таблице.
Как правило, это ошибка возникает в excel при использовании таких функций как ВПР, ИНДЕКС/ПОИСКПОЗ, ПРОСМОТРХ (в версии office 365) и т.д. То есть, ошибка “нет данных” возникает, когда мы пытаемся подтянуть данные из одной таблицы в другую.
Что делать с ошибкой “нет данных”: нужно проверить, что искомое значение действительно присутствует в таблице для поиска. Иногда бывает, что визуально кажется, что значение есть, однако оно может быть написано немного иначе. Например, в конце строки есть невидимый пробел, или одна из букв написана в латинской раскладке.
Статья опубликована на моем сайте https://excel-analytics.ru/
Сообщество ВКонтакте
Ошибка ЗНАЧ в Excel
Эта ошибка возникает в самых разнообразных случаях, и означает что что-то не так с данными или ячейками, из которых используются данные. Иногда достаточно сложно разобраться, почему возникает ошибка, однако основные причины возникновения ошибки ЗНАЧ можно выделить.
Причина № 1 — Формула ссылается на другой файл
Бывают ситуации, когда в одном файле у нас представлена база данных (файл № 1), а в другой файл (файл № 2) нужно подтянуть данные из этой базы. И есть ряд формул, которые будут выдавать ошибку, если файл № 1 будет закрыт в момент вычислений. Это особенности работы Excel, их нужно просто запомнить и учитывать.
Функции, которые будут выдавать ошибку ЗНАЧ при ссылке на другой файл, следующие:
- СУММЕСЛИ()
СУММЕСЛИМН()
СЧЁТЕСЛИ()
СЧЁТЕСЛИМН()
СЧИТАТЬПУСТОТЫ()
СМЕЩ()
ДВССЫЛ()
Рассмотрим на примере функции СУММЕСЛИ. Ситуация: оба файла открыты.
Ситуация: файл № 1 (с базой) закрыт.
Что делать с ошибкой ЗНАЧ в таком случае:
- вариант 1: исключить ссылки на внешние файлы, перенеся базу в тот же файл, где происходят вычисления.
вариант 2: Совместное использование формул СУММ() и ЕСЛИ() в массиве.
вариант 3: Всегда открывать файл с базой, на который ссылается файл итогов. Т.е. открывать оба файла одновременно, и тогда ошибка ЗНАЧ не будет возникать.
вариант 4: Использовать конструкцию ЕСЛИОШИБКА (о ней рассказано ниже в статье), которая будет предлагать открыть файл с базой.
Причина № 2 — Вычитание или сложение ячеек с датами
В этом случае ошибка ЗНАЧ означает, что одна из ячеек с датами имеет другой формат (отличный от формата даты). Например, имеются лишние пробелы.
Что делать: проверить ячейки и убрать лишние пробелы. Это можно сделать, например, выделив ячейки, участвующие в формуле, и заменить (Ctrl + H) пробел на пустоту.
Причина № 3 — Лишние пробелы в числах
Такая ситуация часто возникает, когда используются выгрузки из различных учетных систем. В выгрузках, например, из 1C, часто присутствуют пробелы в числах, и в этом случае excel воспринимает число как текст. И если производить вычисления с такими ячейками, то будет возникать ошибка ЗНАЧ.
Как исправить: заменить пробелы на пустоту, как в предыдущем варианте.
Ошибка ДЕЛ/0 в Excel
Ошибка ДЕЛ/0 буквально означает “ошибка деления на ноль”. Как известно, по правилам математики, делить на ноль нельзя. Следовательно, ошибка ДЕЛ/O в Excel возникает именно тогда, когда формула пытается произвести деление на ячейку:
- значение которой равно нулю
на пустую ячейку
Что делать, если возникла ошибка “деление на ноль”: во-первых, насколько правильно, что ячейка, на которую делите, пустая или равна нулю. Возможно, в ней должны быть данные. Если же ячейка действительно должна быть пустой или со значением 0, то обойти ошибка конструкцией ЕСЛИОШИБКА (см. ниже).
Ошибка ССЫЛКА в Excel
Ошибка ССЫЛКА возникает, когда ячейка, на которую ссылалась формула, была удалена. Или был удален лист, который использовался в вычислениях.
Что делать: как правило, в большинстве случаев решение только одно — переписать формулу заново, сославшись на существующие ячейки.
Ошибка ИМЯ в Excel
Ошибка ИМЯ в Excel возникает, когда имя, которое мы используем в формуле, не было заранее определено.
Причина 1: наименование функции написано с опечаткой
Что делать: исправить написание функции.
Причина 2: используется имя или именованный диапазон, которые ранее не были определены
Если ранее мы не определили имя “количество_покупателей”, то появится ошибка ИМЯ.
Что делать: определить имя для ячеек, которые участвуют в вычислении, создав именованный диапазон.
Ошибка ЧИСЛО в Excel
Ошибка ЧИСЛО в excel возникает, когда используется некорректное для данного вычисления число.
Например, используется отрицательное число там, где оно использоваться не может.
Что делать: проверить используемые в вычислениях значения и откорректировать их.
Также ошибка ЧИСЛО может появиться, когда значение слишком велико, чтобы Excel мог его отобразить. В примере мы пытаемся возвести число 1000 в 2000-ю степень.
Excel поддерживает числовые значения от -1Е-307 до 1Е+307
Что делать: исключить использование чисел вне допустимого диапазона.
Ошибка ПУСТО в Excel
Эта ошибка появляется, когда в формуле указаны диапазоны, которые никак не связаны между собой.
Чаще всего ошибка ПУСТО возникает из-за опечатки в формуле, когда забыли указать оператор вычисления или поставить точку с запятой между аргументами.
Как исправить ошибка ПУСТО: проверить корректность написания формул.
Ячейка заполнена решетками
Если ячейка заполнена знаками “решетка”, то это означает, что ширины ячейки недостаточно, чтобы отобразить ее содержимое.
Что делать: увеличить ширину ячейки или уменьшить шрифт в ячейке. Второй вариант не поможет, если значение в ячейке очень длинное. Также можно визуально уменьшить число в ячейке.
Функция ЕСЛИОШИБКА для обхода ошибок
Если ошибка в формуле Excel все же возникла, то нужно знать, как ее убрать. Убрать ошибку — не значит ее исправить. Это означает, что вместо ошибки будет отображаться какое-то другое значение (или даже вычисление).
Для этой цели существует несколько вариантов конструкций с формулами, но самая распространенная — формула ЕСЛИОШИБКА в Excel.
Синтаксис формулы ЕСЛИОШИБКА:
ЕСЛИОШИБКА(значение; значение если ошибка)
первый аргумент функции значение — это как правило, некая формула.
значение если ошибка — это то, что будет выводиться, если первый аргумент значение выдаст любую из рассмотренных ошибок. Здесь может быть как число, так и другая формула, и даже текст в кавычках.
Рассмотрим на примере ошибки Н/Д. Есть две таблицы — одна со списком сотрудников и количеством отработанных дней, а во вторую нужно подтянуть значение из первой по фамилии. В примере фамилия из второй таблицы не встречается в первой, поэтому и возникла ошибка Н/Д
Теперь посмотрим на три возможных варианта, как можно убрать ошибку в формуле excel.
Вариант 1: Число вместо ошибки
В данном случае, если возникла ошибка в формуле excel, то как ее убрать — подставить вместо нее ноль.
Вариант 2: Другая формула вместо ошибки
Предположим, у нас есть два источника данных — основной и резервный. И если мы не нашли значение в основном, то можем попробовать его найти в резервном.
В примере добавили еще одну таблицу с данными по отработанным часам.
Формула ЕСЛИОШИБКА будет искать заданное значение во второй таблице в том случае, если не найдет в первой.
На картинке функция ВПР под номером 1 ищет значение в первой таблице, и, если не находит, ищет это же значение во второй таблице.
Вариант 3. Текст вместо ошибки
Вместо ошибки можно вывести также текст. Его нужно заключить в кавычки.
Примечательно, что проверку ЕСЛИОШИБКА можно использовать в одной формуле много раз. Ниже пример:
ЕСЛИОШИБКА № 1 — ВПР ищет искомое значение в таблице слева и, в случае отсутствия (ошибка Н/Д), будет работать второй ВПР, который ищет это же значение в таблице справа.
ЕСЛИОШИБКА № 2 — если и во второй таблице искомое значение не нашлось, то выводится 0.
В этой статье мы рассмотрели основные виды ошибок, а также что делать, если возникла та или иная ошибка в формуле Excel и как ее убрать или исправить.
