Как использовать ЕСЛИОШИБКА в Excel и что она скрывает

1. Что такое ошибка, и семерку ты встретишь

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

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

Прежде чем читать дальше, выберите, какое из этих семи утверждений о вашем Данные и какие утверждения о вашем Формула. В этом расколе и состоит весь аргумент этой страницы.

Только #N/A на самом деле речь идет о данных: там говорится, что ключа, который вы искали, нет в списке, что может быть совершенно нормальным. Остальные шесть говорят, что формула требует чего-то невозможного. #REF! в частности, означает, что формула теперь нарушена таким образом, что никогда не исправится. Это разные новости, и инструмент, который обрабатывает их одинаково, нужно использовать осторожно.

2. Синтаксис и местонахождение формулы

Два аргумента. То, что вы хотите, и что показывать, если то, что вы хотите, возвращается с ошибкой.

=IFERROR( your_formula , what_to_show_instead )

Таким образом, поиск, который может пропустить, становится:

=IFERROR(INDEX(Costs[Cost], MATCH([@Product], Costs[Product], 0)), 0)

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

Стоит уточнить одну деталь: ЕСЛИОШИБКА ничего не исправляет. Поиск по-прежнему не удался. Деление на ноль все же произошло. Изменилось только то, что напечатано в ячейке, и все последующие теперь считывают вашу замену, как если бы это было настоящее измерение.

3. Что поставить во второй аргумент: 0, пробел или слово

Перед пояснением: двенадцать поисков стоимости завершились успешно, а четыре — неудачно. Заполнение четырех неудач 0 и заполнение их "" оба выглядят как уборка. Скажите, ожидаете ли вы, что среднее значение по столбцу в любом случае будет одинаковым.

Это не так. Оба были рассчитаны на одни и те же двенадцать значений, всего 1240.

=AVERAGE(  filled with 0  )        77.5
=AVERAGE( filled with "" )        103.3333

Заполнение 0 дает Excel шестнадцать чисел, поэтому он делит 1240 на 16 и получает 77,5. Наполнение "" дает Excel двенадцать чисел и четыре фрагмента текста, а СРЗНАЧ пропускает текст, поэтому он делит 1240 на 12 и получает 103,33. Также нет разницы в округлении. Это ответы на два разных вопроса, и введенная вами оболочка выбирает, какой вопрос будет задан.

Есть еще одна морщинка "" стоит знать, потому что он ловит людей, которые проверяют пустоту. Ячейка холдинга "" не пуст; он содержит фрагмент текста, в котором нет символов. Запустите в Excel такую ​​ячейку:

=ISBLANK(the cell)      FALSE
=COUNT(the column)      12
=COUNTA(the column)     16

COUNT считает числа, поэтому он говорит 12. COUNTA считает все, что не пусто, и поскольку "" не пусто, там написано 16. Если «количество записей» на вашем листе выглядит слишком большим ровно на количество неудачных поисков, вот почему.

Итак, три варианта и то, что на самом деле утверждает каждый из них.

Приведенное ниже правило короткое: никогда не заменяйте ошибку числом, если только это число не является показателем, который вы бы защищали. Ноль, который означает «мы не знаем», усредняется, отображается на графике и суммируется точно так же, как ноль, который означает «нет», и никто на последующих этапах не может их отличить.

4. Четыре исчезнувших ряда провязаны встык.

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

Список затрат поступает из финансовой системы. Там стоят столы и стулья. Лампы отсутствуют, потому что в прошлом квартале лампы были поставлены в другую категорию, и об этом никто не упомянул. Таким образом, поиск стоимости завершается успешно в двенадцати строках и возвращает результат. #N/A на четверых. Завернуто обычным способом:

=IFERROR(INDEX(CostList[Cost], MATCH([@Product], CostList[Product], 0)), 0)

Теперь лист выглядит совершенно чистым. Нигде ни одного сообщения об ошибке. Вот что он сообщает, рядом с правдой.

Проверьте пробел вручную: стоимость недостающих ламп равна 40 единицам по 22 штуки, что равно 880, а 6076 − 880 = 5196. Этот единственный неуказанный показатель 880 увеличивает заявленную маржу почти на девять процентных пунктов, и это сдвигает ее в благоприятном направлении, в котором никто не подвергает сомнению.

Обратите внимание, что каждое отдельное число на этом листе является арифметически правильным. Сумма на самом деле является суммой того, что есть. Чтобы отчет был неправильным, не должно быть никаких ошибок в расчетах; достаточно, чтобы чего-то не хватало и чтобы отсутствие было невидимым. Без ЕСЛИОШИБКИ общая сумма показала бы #N/A и кто-нибудь через минуту спросил бы, почему.

Теперь представьте себе свою самую популярную рабочую книгу. Если в этом месяце четыре его строки молча провалили поиск, какое число на вкладке сводки переместится, и сможет ли кто-нибудь это определить, глядя на него?

5. Подсчитайте ошибки, прежде чем скрывать их

Исправление состоит в том, чтобы не оставлять ошибки на странице навсегда. Это значит сделать их числом, которое вы намеренно производите и читаете.

=SUMPRODUCT(--ISNA(C2:C17))     4      unmatched lookups
=COUNT(C2:C17)                  12     rows that got a value
=SUM(C2:C17)                    #N/A   the whole total, poisoned by one

Эта последняя строка стоит того, чтобы с ней посидеть. Единственная ошибка в диапазоне приводит к тому, что SUM возвращает ошибку. Люди воспринимают это как помехи в работе Excel, и это самое полезное, что делает Excel: он отказывается складывать столбец, который не может видеть полностью, и отказывается громко. ЕСЛИОШИБКА не решает эту проблему, а отключает связанный с ней сигнал тревоги.

Итак, порядок работы такой: сначала посчитайте, поймите, что такое промахи, решите, что они означают, и только потом выбирайте, что отображать. Если четыре несовпадающие строки на самом деле ничего не представляют, то ноль является честным. Если это лампы стоимостью 880, ноль — это ложное утверждение о бизнесе. Формула не может обнаружить разницу; ты можешь.

Когда промахи реальны и вам все равно приходится отправлять товар, подсчет ведется в письменном виде рядом с результатом, а не только в вашей голове. Это привычка со своей страницей: Документирование ограничений данных рассказывает о том, как сказать «4 из 16 заказов не имеют стоимости, охватывающих 40 единиц», не превращая отчет в извинения.

6. IFNA: более узкая сеть

Перед объяснением: вы помещаете столбец поиска в ЕСЛИОШИБКА, чтобы скрыть несовпадающие ключи. Месяц спустя кто-то удалил столбец, и одна из этих формул перестала работать. #REF!. Скажите, что изображено на вашем листе.

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

=IFERROR(#REF! * 2, "hidden")     hidden
=IFNA(   #REF! * 2, "hidden")     #REF!

IFNA принимает те же два аргумента и ловит Только #N/A. Все остальное проходит насквозь и остается видимым. При поиске почти всегда это правильный инструмент, потому что #N/A это единственная ошибка, которую может произвести поиск. А #REF!, а #VALUE! или #NAME? выход из поиска означает, что что-то сломано, и вы хотите, чтобы вам об этом сказали.

Оба по-прежнему скрывают сам несовпадающий ключ, и это именно тот случай, который вы хотели обработать:

=IFERROR(MATCH("Sofa", Products[Product], 0), "hidden")     hidden
=IFNA(   MATCH("Sofa", Products[Product], 0), "hidden")     hidden

Так что выбор прост. Во всем, что является поиском, используйте IFNA. Сохраняйте ЕСЛИОШИБКА для случаев, когда ошибка действительно ожидается, а не возникает. #N/A, самым распространенным из которых является деление, знаменатель которого может законно равняться нулю.

Этот случай деления заслуживает отдельного приговора, потому что ноль и неизвестное здесь тоже различны. =IFERROR(A2/B2, 0) за месяц без заказов коэффициент конверсии составляет 0%, что означает «мы никого не конвертировали», а не «некого было конвертировать». Если это различие важно для вашего читателя, так и скажите: =IF(B2=0, "no orders", A2/B2) ввод стоит одинаково и не может быть неправильно прочитан.

7. Старый паттерн ЕСЛИОШИБКА и почему его заменили ЕСЛИОШИБКА

Эту форму вы встретите в унаследованных рабочих тетрадях, и ее стоит уметь читать.

=IF(ISERROR(VLOOKUP(A2,Costs,2,FALSE)), 0, VLOOKUP(A2,Costs,2,FALSE))

В целом он выполняет ту же самую работу. Поиск записывается дважды: один раз, чтобы проверить, не сработал ли он, и один раз, чтобы получить ответ, если это не так. Это хуже по трем пунктам. Читать в два раза больше. Excel выполняет поиск дважды, а не один раз, что незаметно для ста строк и заметно для пятидесяти тысяч. И любое будущее редактирование должно производиться в двух местах, поэтому две копии расходятся, и лист начинает проверять одну формулу и возвращать другую.

=IFERROR(VLOOKUP(A2,Costs,2,FALSE), 0)

То же поведение, одна копия поиска. Когда вы найдете старый образец, эта замена будет безопасной и механической. Родственники ISERR, ISNA и IFERROR все еще имеют свое применение, но ISNA зарабатывает себе на жизнь в формуле подсчета, как в пятом разделе, а не обертывается вокруг того, что он тестирует.

8. Куда идет обертка: по краю, а не посередине

Обертка — это решение о представлении, поэтому она находится там, где представлен лист, а не в середине цепочки вычислений.

Поместите ЕСЛИОШИБКА вокруг последнего шага, и промежуточные столбцы останутся честными. Вы по-прежнему можете видеть, какие строки не удалось выполнить, их по-прежнему можно подсчитать, и сглаживается только последний столбец отображения. Вместо этого поместите его на ранний шаг, и ошибка преобразуется в правдоподобное число на уровне строки, затем переносится на каждый последующий столбец, и к тому времени, когда кто-нибудь начнет искать, уже нечего будет найти.

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

Крайние случаи, которые незаметно нарушают обработку ошибок

Шесть, каждая из которых стоила кому-то дня.

ЕСЛИОШИБКА по всей формуле, когда только одна часть может дать сбой. упаковка A2*VLOOKUP(...) означает, что текстовое значение в A2 также будет скрыто. Оберните ту часть, которая может выйти из строя, а не предложение, в котором она находится.

Ошибки с заполнением нулями внутри среднего значения. Среднее значение в столбце, где отказы стали нулями, перетягивается к нулю именно из-за тех отказов, которые вы не могли видеть. Это та же проблема, которую вызывают NULL в базе данных, и его SQL-версия стоит прочитать, потому что механизм идентичен.

"" считается рекордом. COUNTA и многие сводные счетчики рассматривают пустую строку как присутствующую. Количество строк оказывается верным по счастливой случайности и неправильным на несколько раз всякий раз, когда меняется частота неудач.

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

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

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