Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать количество ячеек с ошибками / ячеек без ошибок в Excel?

Функция IFERROR (ЕСЛИОШИБКА) в Excel. Как использовать?

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

Видеоурок

Что возвращает функция

Значение, которое вы указываете в случае, если в ячейке произошла ошибка.

Синтаксис

=IFERROR(value, value_if_error) — английская версия

=ЕСЛИОШИБКА(значение;значение_если_ошибка) — русская версия

Аргументы функции

  • value (значение) — это аргумент, который проверяет, есть ли в ячейке ошибка. Обычно, ошибкой может быть результат какого либо вычисления;
  • value_if_error (значение_если_ошибка) — это аргумент, который заменяет ошибку в ячейке (в случае её наличия) на указанное вами значение. Ошибки могут выглядеть так: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, #NULL! (английская версия Excel) или #ЗНАЧ!, #ДЕЛ/0, #ИМЯ?, #Н/Д, #ССЫЛКА!, #ЧИСЛО!, #ПУСТО! (русская версия Excel).

Дополнительная информация

  • Если вы используете кавычки («») в качестве аргумента value_if_error (значение_если_ошибка), ячейка ничего не отображает в случае ошибки.
  • Если аргумент value (значение) или value_if_error (значение_если_ошибка) ссылается на пустую ячейку, она рассматривается как пустая.

Примеры использования функции IFERROR (ЕСЛИОШИБКА) в Excel

Пример 1. Заменяем ошибки в ячейке на пустые значения

Если вы используете функции, которые могут вернуть ошибку, вы можете заключить ее в функцию и указать пустое значение, которое будет возвращено при ошибке.

Ответом для ячейки D4 в приведенном ниже примере будет # DIV/0!

IFERROR в Excel

Если вы хотите удалить информацию об ошибке из ячейки, воспользуйтесь этой формулой:

=IFERROR(A1/A2,””) — английская версия

=ЕСЛИОШИБКА(A1/A2;»») — русская версия

IFERROR в Excel

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

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

Формула коррекции ошибок может быть использована для получения любого текста или значения, например, следующей формулы:

=IFERROR(A1/A2,”Error”) — английская версия

=ЕСЛИОШИБКА(A1/A2;»») — русская версия

IFERROR в Excel

В Excel 2003 и более поздних версиях вы не найдете функцию IFERROR. При желании вместо нее можно использовать IF или ISERROR.

Пример 2. Заменяем значения без данных при использовании функции VLOOKUP (ВПР) на “Не найдено”

При использовании функции VLOOKUP мы часто видим, что если ни для одного из них нет значений, формула выдает ошибку «#N/A. «.

Ниже приведен пример использования функции VLOOKUP для подстановки данных из результатов экзамена по выбранным студентам.

IFERROR в Excel

В нашем примере в списке студентов с результатами экзаменов нет данных об Иване. В результате, когда мы используем функцию VLOOKUP (VPR), формула не работает.

Именно здесь мы можем использовать функцию IFERROR, чтобы результат вычисления выглядел правильно и без ошибок. Для этого мы можем использовать формулу :

=IFERROR(VLOOKUP(D2,$A$2:$B$12,2,0),”Не найдено”) — английская версия

=ЕСЛИОШИБКА(ВПР(D2;$A$2:$B$12;2;0);»Не найдено») — русская версия

IFERROR в Excel

Пример 3. Возвращаем значение “0” вместо ошибок формулы

Если у вас нет конкретного значения, которое вы бы хотели использовать для замены ошибок — оставляйте аргумент функции value_if_error(значение_если_ошибка) пустым, как показано на примере ниже и в случае наличия ошибки, функция будет выдавать “0”:

IFERROR в Excel

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

Поиск и исправление ошибок в формулах MS Excel

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

Читайте так же:
Как посчитать количество выходных / будних дней между двумя датами в Excel?

К счастью, в наших руках несколько отличных инструментов для поиска “хитрых” ошибок в формулах MS Excel.

Влияющие и зависимые ячейки в MS Excel

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

Именно с этой точки зрения все ячейки в MS Excel делятся на влияющие и зависимые. Их легко различить и запомнить:

  • Влияющие ячейки, это ячейки на которые ссылается формула (т.е. если формула это А+Б, то данные в ячейках А и Б – это данные влияющие на результат вычисления формулы).
  • Зависимые – содержат формулу влияющую на содержимое ячейки (т.е. если формула В+Г берет данные по В из ячейки содержащей не число, а результат вычисления А+Б, то ячейка с формулой В+Г, будет по отношению к ней зависимой, т.к. от правильности работы А+Б зависит результат вычисления в В+Г).

Зная, какие клетки влияют на результат и от чего они зависят, легко найти причину проблемы.

Чтобы проиллюстрировать это, я подготовил простую таблицу данных. Он имеет два условных показателя и коэффициент, а окончательный расчет производится путем простого сложения двух показателей и умножения результата: (Показатель 1 + Показатель 2) x Коэффициент.

Дополнительно я создал ещё одну простую формулу: она умножает наш “Итог” на некую постоянную поправку, которую я задал прямо в формуле вручную: Итог х 0,6.

Давайте перейдем на вкладку “Формулы” и в группе “Зависимости формул” посмотрим на два крайне полезных в работе инструмента: “Влияющие ячейки” и “Зависимые ячейки”.

Читайте так же:
Как показать или скрыть полевые кнопки в сводной диаграмме в Excel?

Определяем влияющие ячейки в Excel.

Определение влияющих ячеек в Excel. Они, очевидно, влияют на вычисления, которые происходят в клетке

Выделяю результирующую ячейку “Итог” и нажимаю кнопку “Влияющие ячейки”. Оп, и на листе MS Excel появляются синие стрелки ведущие от трех используемых в вычислениях ячеек к итоговой формуле. Согласитесь, нагляднее представить себе понятие “влияющая ячейка” невозможно.

зависимые ячейки в excel

А теперь зависимые клетки. Весь лист теперь у вас на ладони.

Теперь нажимаю (не убирая курсор с ячейки “итоги”) кнопку “Зависимые ячейки” и на экране появляется ещё одна стрелка. Она ведет к ячейке “результат с поправкой”, то есть той, результат вычислений в которой зависит от текущей.

Теперь я намеренно “порчу” таблицу, внося в исходные данные ошибку – подставляя букву вместо цифры. Мгновение, и я уже точно знаю откуда эта ошибка взялась. Мне даже искать ничего не пришлось – все вполне наглядно и графически красиво.

Ошибка возникшая из-за замены цифры на букву. Excel подсветил "ошибочное" вычисление красной стрелкой

Ошибка возникшая из-за замены цифры на букву. Excel подсветил “ошибочное” вычисление красной стрелкой

Отключить графику можно в любой момент нажав на кнопку “Убрать стрелки”.

Чтобы убрать стрелки с листа MS Excel воспользуйтесь соответствующей кнопкой

Чтобы удалить стрелки с листа MS Excel, используйте соответствующую кнопку

Исправление ошибок возникающих в MS Excel

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

Ищем ошибку в формуле Excel

Поиск ошибки в формулах Excel

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

Читайте так же:
Как перечислить все имена файлов из папки и подпапок на листе?

исправление ошибок в Excel

А вот и ошибка – как видите, программа ясно дает понять, что проблема возникает ещё до умножения, то есть на этапе сложения показателей

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

Вот и всё. Пользуйтесь этими несложными методами, и без труда “расщелкаете” любую возникшую при вычисления в MS Excel ошибку.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector