Предотвращение ошибок формул в Excel с помощью IFERROR или IFNA. Они лучше старых ISERROR ISERR и ISNA. Узнайте больше здесь.
Ошибки в формулах могут быть обычным явлением. Если у вас есть набор данных с сотнями записей, то время от времени обязательно будут появляться символы деления на ноль или # Н / Д.
В прошлом для предотвращения ошибок требовались титанические усилия. Сознательно кивайте головой, если вы когда-нибудь нокаутировали =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Помимо очень длинного набора, это решение требует, чтобы Excel выполнял вдвое больше операций ВПР. Сначала вы выполняете VLOOKUP, чтобы увидеть, не приведет ли VLOOKUP к ошибке. Затем вы снова выполняете ту же функцию VLOOKUP, чтобы получить результат без ошибок.
В Excel 2010 значительно улучшена функция = ЕСЛИОШИБКА (формула, значение при ошибке). Я знаю, что ЕСЛИОШИБКА звучит как старые старые функции ISERROR, ISERR, ISNA, но это совершенно другое.
Это гениальная функция: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). Если у вас 1000 VLOOKUP и только 5 возвращают # N / A, то 995, которые работали, требуют только одного VLOOKUP. Только 5, вернувшие # N / A, должны перейти ко второму аргументу IFERROR.
Как ни странно, в Excel 2013 добавлена функция IFNA (). Это похоже на ЕСЛИОШИБКА, но ищет только ошибки # Н / Д. Можно представить себе странную ситуацию, когда значение в поисковой таблице найдено, но в результате получается деление на 0. Если по какой-то причине вы хотите сохранить ошибку деления на ноль, то это сделает IFNA ().
Конечно, человек, который построил таблицу поиска, должен был использовать ЕСЛИОШИБКА, чтобы предотвратить деление на ноль в первую очередь. На рисунке ниже «nm» - это код бывшего менеджера, означающий «не имеет смысла».
Спасибо Джастину Фишману, Стивену Гилмеру и Excel Джо.
Смотреть видео
- Раньше вы использовали
=IF(ISNA(Formula),0,Formula) - Начиная с Excel 2010,
=IFERROR(Formula,0) - Но ЕСЛИОШИБКА обрабатывает ошибки DIV / 0 так же, как и # Н / Д! ошибки
- Начиная с Excel 2013, используйте
=IFNA(Formula,0)для обнаружения только ошибок # N / A - Спасибо Джастину Фишману, Стивену Гилмеру и Excel Джо.
Стенограмма видео
Изучите Excel из подкаста, выпуск 2042 - ЕСЛИ ОШИБКА и IFNA!
Я буду подкастировать все свои советы из этой книги, нажмите «i» в правом верхнем углу, чтобы перейти ко всему плейлисту!
Хорошо, давайте вернемся к старым временам, Excel 2007 или более ранним версиям, если вам нужно было предотвратить # N / A! из формулы ВПР, подобной этой, мы делали эту безумную вещь. Перемешайте все символы VLOOKUP, за исключением =, Ctrl + C, чтобы поместить его в буфер обмена, = IF (ISNA (, нажмите клавишу End, чтобы добраться до конца, если это # N / A !, то мы скажите «Не найдено», запятую, иначе мы выполним ВПР! Хорошо, я вставляю его туда и смотрю, сколько времени эта формула, Ctrl + Enter, добавляю a) прямо здесь, Ctrl + Enter, хорошо, смотрите, это мило. Хорошо, но проблема в том, что хотя он решает проблему # N / A!, Каждая формула выполняет VOOKUP дважды. Он не хочет, чтобы мы говорили: «Эй, это ошибка? О, нет, это не так. ошибка. Давайте сделаем это снова! »Таким образом, выполнение всех этих операций ВПР занимает в два раза больше времени. В Excel 2010они дают нам удивительную формулу БЕЗОШИБКИ!
Теперь я знаю, что это похоже на то, что было раньше, ISERROR или ISERR, но это ЕСЛИ ОШИБКА. И как это работает, возьмите любую формулу, которая может возвращать ошибку, а затем вы говорите = ЕСЛИ ОШИБКА, там формула, запятая, что делать, если это ошибка, поэтому «Не найдено». Хорошо, вот что замечательно в этом: допустим, вам нужно сделать тысячу ВПР, и 980 из них работают. Для 980 он просто выполняет ВПР, это не ошибка, он возвращает ответ, он никогда не выполняет второй ВПР, в этом вся прелесть ЕСЛИОШИБКИ. ЕСЛИОШИБКА Excel появилась в Excel 2010, но, конечно же, одна действительно глупая проблема заключается в том, что сама таблица возвращает ошибку. Верно, у кого-то было 0 количество, доход / количество, и поэтому мы получаем # DIV / 0! ошибка там, и эта ошибка также будет обнаружена как ошибка ЕСЛИОШИБКА. Хорошо,и это будет выглядеть так, как будто он не найден, он найден, просто тот, кто построил эту таблицу, не сделал хорошей работы при создании этой таблицы.
Итак, выбор №1, Excel 2013 или новее, переключитесь на функцию, которую они добавили в 2013 году, которая не ищет все ошибки, t ищет только # N / A! Ctrl + Enter, и теперь мы получим Not Found for ExcelIsFun, но возвращает # DIV / 0! ошибка. На самом деле, то, что мы должны делать, находится здесь в этой формуле, мы должны обрабатывать эту формулу, чтобы предотвратить деление на ноль в первую очередь. Итак, = ЕСЛИОШИБКА, это работает для всех типов вещей, нажмите клавишу End, чтобы добраться до конца, запятую, а затем что делать? Я бы всегда ставил здесь ноль как среднюю цену.
Однажды у меня была менеджер, Сюзанна (?): «Это математически неверно, вы должны указать« нм », чтобы не иметь смысла». Точно так, просто безумие, как долго мой менеджер сводил меня с ума своим безумным запросом, так что давайте скопируем это, Вставить специальные формулы, alt = "" ES F. Теперь мы получаем здесь ошибку «не значимая», « Не найдено »обнаруживается ЕСЛИ ОШИБКА, а« не имеет смысла »фактически является результатом ВПР. Хорошо, есть пара разных способов, возможно, безопаснее всего здесь использовать IFNA, при условии, что у вас есть Excel 2013, и все, с кем вы делитесь своей книгой, имеют Excel 2013. Эта книга и множество других находятся в этой книге, изобилуют пикантными советами, 200 страниц, легко читаются, классная полноцветная, классная книга. Проверьте это, нажмите «I» в правом верхнем углу,Вы можете купить книгу.
Хорошо, резюмируем эпизод: в былые времена мы использовали длинный формат = ЕСЛИ (ISNA (формула, 0, в противном случае формула, формула вычислялась дважды, что ужасно для ВПР. Начиная с Excel 2010, = ЕСЛИОШИБКА) , просто введите формулу один раз, запятую, что делать, если это ошибка. ЕСЛИОШИБКА обрабатывает ошибки # DIV / 0! так же, как ошибки # N / A!, поэтому при запуске Excel 2013 функция IFNA работает так же, как ЕСЛИОШИБКА, но он обнаружит только ошибки # Н / Д!.
Этот совет, кстати, предложили читатели Джастин Фишман, Стивен Гилмер и Excel Джо. Спасибо им за это, и спасибо, что заглянули, увидимся в следующий раз на другом сетевом трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2042.xlsm
