Ошибка в формуле Excel как убрать

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

 

 

Ошибка Н/Д в Excel

 

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

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

 

Ошибка в формуле Excel как убрать

 

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

 

 

Ошибка ЗНАЧ в Excel

 

Эта ошибка возникает в самых разнообразных случаях, и означает что что-то не так с данными или ячейками, из которых используются данные. Иногда достаточно сложно разобраться, почему возникает ошибка, однако основные причины возникновения ошибки #ЗНАЧ можно выделить.

Причина № 1 — Формула ссылается на другой файл

 

Бывают ситуации, когда в одном файле у нас представлена база данных (файл № 1), а в другой файл (файл № 2)  нужно подтянуть данные из этой базы. И есть ряд формул, которые будут выдавать ошибку, если файл № 1 будет закрыт в момент вычислений. Это особенности работы Excel, их нужно просто запомнить и учитывать.

Функции, которые будут выдавать ошибку ЗНАЧ при ссылке на другой файл, следующие:

      • СУММЕСЛИ()
      • СУММЕСЛИМН()
      • СЧЁТЕСЛИ()
      • СЧЁТЕСЛИМН()
      • СЧИТАТЬПУСТОТЫ()
      • СМЕЩ()
      • ДВССЫЛ()

 

Рассмотрим на примере функции СУММЕСЛИ. Ситуация: оба файла открыты.

 

Ошибка в формуле Excel как убрать

 

Ситуация: файл № 1 (с базой) закрыт.

 

Ошибка в формуле Excel как убрать

 

Что делать с ошибкой ЗНАЧ в таком случае:

      • вариант 1: исключить ссылки на внешние файлы, перенеся базу в тот же файл, где происходят вычисления.
      • вариант 2: Совместное использование формул СУММ() и ЕСЛИ() в массиве.
      • вариант 3: Всегда открывать файл с базой, на который ссылается файл итогов. Т.е. открывать оба файла одновременно, и тогда ошибка ЗНАЧ не будет возникать.
      • вариант 4: Использовать конструкцию ЕСЛИОШИБКА (о ней рассказано ниже в статье), которая будет предлагать открыть файл с базой.

 

Причина № 2 — Вычитание или сложение ячеек с датами

 

В этом случае ошибка ЗНАЧ означает, что одна из ячеек с датами имеет другой формат (отличный от формата даты). Например, имеются лишние пробелы.

 

Ошибка в формуле Excel как убрать

 

Что делать: проверить ячейки и убрать лишние пробелы. Это можно сделать, например, выделив ячейки, участвующие в формуле, и заменить (Ctrl + H) пробел на пустоту.

 

ошибка в формуле Excel как убрать

 

Причина № 3 — Лишние пробелы в числах

 

Такая ситуация часто возникает, когда используются выгрузки из различных учетных систем. В выгрузках, например, из 1C, часто присутствуют пробелы в числах, и в этом случае excel воспринимает число как текст. И если производить вычисления с такими ячейками, то будет возникать ошибка ЗНАЧ.

 

Ошибка в формуле Excel как убрать

 

Как исправить: заменить пробелы на пустоту, как в предыдущем варианте.

 

Ошибка ДЕЛ/0 в Excel

 

Ошибка ДЕЛ/0 буквально означает “ошибка деления на ноль”. Как известно, по правилам математики, делить на ноль нельзя. Следовательно, ошибка ДЕЛ/O в Excel возникает именно тогда, когда формула пытается произвести деление на ячейку:

      • значение которой равно нулю
      • на пустую ячейку

 

Ошибка в формуле Excel как убрать

 

Что делать, если возникла ошибка “деление на ноль”: во-первых, насколько правильно, что ячейка, на которую делите, пустая или равна нулю. Возможно, в ней должны быть данные. Если же ячейка действительно должна быть пустой или со значением 0, то обойти ошибка конструкцией ЕСЛИОШИБКА (см. ниже).

Ошибка ССЫЛКА в Excel

 

Ошибка ССЫЛКА возникает, когда ячейка, на которую ссылалась формула, была удалена. Или был удален лист, который использовался в вычислениях.

 

Что делать: как правило, в большинстве случаев решение только одно — переписать формулу заново, сославшись на существующие ячейки. 

 

 

Ошибка ИМЯ в Excel

 

Ошибка ИМЯ в Excel возникает, когда имя, которое мы используем в формуле, не было заранее определено

Причина 1: наименование функции написано с опечаткой

 

Ошибка в формуле Excel как убрать

 

Что делать: исправить написание функции.

 

Причина 2: используется имя или именованный диапазон, которые ранее не были определены

 

Ошибка в формуле Excel как убрать

 

Если ранее мы не определили имя “количество_покупателей”, то появится ошибка ИМЯ.

Что делать: определить имя для ячеек, которые участвуют в вычислении, создав *именованный диапазон*

 

Ошибка ЧИСЛО в Excel

 

Ошибка ЧИСЛО в excel возникает, когда используется некорректное для данного вычисления число.

Например, используется отрицательное число там, где оно использоваться не может.

 

Ошибка в формуле Excel как убрать

 

Что делать: проверить используемые в вычислениях значения и откорректировать их.

Также ошибка ЧИСЛО может появиться, когда значение слишком велико, чтобы Excel мог его отобразить. В примере мы пытаемся возвести число 1000 в 2000-ю степень.

 

Ошибка в формуле Excel как убрать

 

Excel поддерживает числовые значения от -1Е-307 до 1Е+307

Что делать: исключить использование чисел вне допустимого диапазона.

 

Ошибка ПУСТО в Excel

 

Эта ошибка появляется, когда в формуле указаны диапазоны, которые никак не связаны между собой

 

Ошибка в формуле Excel как убрать

 

Чаще всего ошибка ПУСТО возникает из-за опечатки в формуле, когда забыли указать оператор вычисления или поставить точку с запятой между аргументами.

 

Ошибка в формуле Excel как убрать

 

Как исправить ошибка ПУСТО: проверить корректность написания формул.

 

Ячейка заполнена решетками

 

Если ячейка заполнена знаками “решетка”, то это означает, что ширины ячейки недостаточно, чтобы отобразить ее содержимое.

 

Что делать: увеличить ширину ячейки или уменьшить шрифт в ячейке. Второй вариант не поможет, если значение в ячейке очень длинное. Также можно визуально уменьшить число в ячейке.

 

Функция ЕСЛИОШИБКА для обхода ошибок

 

Если ошибка в формуле Excel все же возникла, то нужно знать, как ее убрать. Убрать ошибку — не значит ее исправить. Это означает, что вместо ошибки будет отображаться какое-то другое значение (или даже вычисление).

Для этой цели существует несколько вариантов конструкций с формулами, но самая распространенная — формула ЕСЛИОШИБКА в Excel.

 

Синтаксис формулы ЕСЛИОШИБКА:

 

ЕСЛИОШИБКА(значение; значение если ошибка) 

 

первый аргумент функции значение — это как правило, некая формула.

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

Рассмотрим на примере ошибки #Н/Д. Есть две таблицы — одна со списком сотрудников и количеством отработанных дней, а во вторую нужно подтянуть значение из первой по фамилии. В примере фамилия из второй таблицы не встречается в первой, поэтому и возникла ошибка #Н/Д

 

 

Теперь посмотрим на три возможных варианта, как можно убрать ошибку в формуле excel.

 

Вариант 1: Число вместо ошибки

 

 

В данном случае, если возникла ошибка в формуле excel, то как ее убрать — подставить вместо нее ноль.

 

Вариант 2: Другая формула вместо ошибки

 

Предположим, у нас есть два источника данных — основной и резервный. И если мы не нашли значение в основном, то можем попробовать его найти в резервном.

В примере добавили еще одну таблицу с данными по отработанным часам. 

Формула ЕСЛИОШИБКА будет искать заданное значение во второй таблице в том случае, если не найдет в первой.

 

 

На картинке функция ВПР под номером 1 ищет значение в первой таблице, и, если не находит, ищет это же значение во второй таблице.

 

Вариант 3. Текст вместо ошибки

 

Вместо ошибки можно вывести также текст. Его нужно заключить в кавычки.

 

 

Примечательно, что проверку ЕСЛИОШИБКА можно использовать в одной формуле много раз. Ниже пример:

 

 

ЕСЛИОШИБКА № 1 — ВПР ищет искомое значение в таблице слева и, в случае отсутствия (ошибка #Н/Д), будет работать второй ВПР, который ищет это же значение в таблице справа.

ЕСЛИОШИБКА № 2 — если и во второй таблице искомое значение не нашлось, то выводится 0.

 

 

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

1 комментарий к “Ошибка в формуле Excel как убрать”

  1. Пингбэк: Функция ВПР в Excel с несколькими условиями | Excel Analytics

Оставьте комментарий

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