Что делает это если в составленной формуле содержится ошибка

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

#Н/Д — ошибка “нет данных”. #Н/Д означает, что искомое значение не было найдено в таблице. 

Как правило, это ошибка возникает в excel при использовании таких функций как ВПР, ИНДЕКС/ПОИСКПОЗ, ПРОСМОТРХ (в версии office 365) и т.д. То есть, ошибка “нет данных” возникает, когда мы пытаемся подтянуть данные из одной таблицы в другую. 

Что делать с ошибкой “нет данных”: нужно проверить, что искомое значение действительно присутствует в таблице для поиска. Иногда бывает, что визуально кажется, что значение есть, однако оно может быть написано немного иначе. Например, в конце строки есть невидимый пробел, или одна из букв написана в латинской раскладке.

Ошибка ЗНАЧ в 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 и как ее убрать или исправить.