Раздел 3: Синтаксис функции IFERROR
Функция IFERROR позволяет управлять ошибками в ячейках. Ее синтаксис очень прост:
- Значение: это значение, которое нужно проверить на наличие ошибки.
- Замена ошибки: это значение или формула, которые будут выведены, если в ячейке обнаружена ошибка.
Функция IFERROR имеет следующую структуру:
| Функция IFERROR | Значение | Замена ошибки |
|---|---|---|
| IFERROR() | Значение | Замена ошибки |
Если значение не содержит ошибки, то функция IFERROR возвращает это значение без изменений. Если значение содержит ошибку, то функция заменяет значение на указанную замену ошибки.
Пример использования функции IFERROR:
- Предположим, у нас есть формула =A1/B1, которая делит значение в ячейке A1 на значение в ячейке B1. Если значение в ячейке B1 равно 0, то в результате появляется ошибка, так как деление на ноль невозможно. Для обработки ошибки можно использовать функцию IFERROR следующим образом: IFERROR(A1/B1, «Ошибка деления»). Теперь, если значение в ячейке B1 равно 0, вместо ошибки будет выведено сообщение «Ошибка деления».
- Другой пример использования функции IFERROR — обработка ошибок формулы VLOOKUP. Эта функция возвращает значение из определенной ячейки в соответствии с заданным условием. Однако, если условие не выполнено, функция возвращает значение ошибки #N/A. Чтобы управлять такими ошибками, можно использовать функцию IFERROR следующим образом: IFERROR(VLOOKUP(A2, B:C, 2, FALSE), «Данные не найдены»). Если условие поиска VLOOKUP не выполняется, вместо ошибки #N/A будет выведено сообщение «Данные не найдены».
Функция IFERROR позволяет более эффективно управлять и обрабатывать ошибки в ячейках. Она значительно упрощает и улучшает видимость данных в таблицах Excel.
Раздел 4: Примеры использования функции Iferror
Функция Iferror в Excel позволяет обработать ошибки, которые могут возникнуть при выполнении других функций. Она предлагает заменить ошибочные значения на заданное значение или текст ошибки.
Вот несколько примеров использования функции Iferror:
-
Пример 1:
Предположим, у вас есть формула, которая делит одно число на другое. Однако, если делитель равен нулю, формула выдаст ошибку деления на ноль. В этом случае вы можете использовать функцию Iferror для замены ошибки на любое другое значение или текст.
Ячейка A1 Ячейка B1 Ячейка C1 (формула) Ячейка D1 (результат) 10 =A1/B1 =IFERROR(C1, «Деление на ноль») В данном примере, если значение в ячейке B1 равно нулю, то в ячейке D1 будет выведено сообщение «Деление на ноль». В противном случае будет выведен результат деления.
-
Пример 2:
Другим примером использования функции Iferror может быть обработка ошибок при нахождении значения в диапазоне ячеек. Например, вы можете использовать эту функцию, чтобы присвоить нулевое значение или другой текст, если значение не найдено.
Ячейка A1 Ячейка B1 Ячейка C1 (формула) Ячейка D1 (результат) Яблоко Апельсин =VLOOKUP(«Груша», A1:B2, 2, FALSE) =IFERROR(C1, «Значение не найдено») В данном примере, если значение «Груша» не найдено в диапазоне ячеек A1:B2, то в ячейке D1 будет выведено сообщение «Значение не найдено». В противном случае будет выведено найденное значение.
-
Пример 3:
Также функция Iferror может использоваться для обработки ошибок при работе с формулами, связанными с поиском данных в других рабочих книгах. Если внешняя рабочая книга не найдена или недоступна, функция Iferror может заменить ошибку на другое значение или текст.
Ячейка A1 Ячейка B1 (формула) Ячейка C1 (результат) Внешняя_рабочая_книга.xlsx =VLOOKUP(«Значение», ‘Лист1’!A1:B10, 2, FALSE) =IFERROR(B1, «Внешняя рабочая книга недоступна») В данном примере, если внешняя рабочая книга «Внешняя_рабочая_книга.xlsx» недоступна или не найдена, то в ячейке C1 будет выведено сообщение «Внешняя рабочая книга недоступна». В противном случае будет выведено найденное значение.
Как видно из примеров, функция Iferror представляет собой удобный инструмент для обработки ошибок в Excel и может быть использована в различных ситуациях, где требуется проверить наличие ошибок и произвести соответствующие действия.
Примеры
Скопируйте данные из таблицы ниже и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД.
|
Котировка |
Единиц продано |
|
|---|---|---|
|
210 |
35 |
|
|
55 |
||
|
23 |
||
|
Формула |
Описание |
Результат |
|
=ЕСЛИОШИБКА(A2/B2;»Ошибка при вычислении») |
Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление 210 на 35), не обнаруживает ошибок и возвращает результат вычисления по формуле |
6 |
|
=ЕСЛИОШИБКА(A3/B3;»Ошибка при вычислении») |
Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление 55 на 0), обнаруживает ошибку «деление на 0» и возвращает «значение_при_ошибке» |
Ошибка при вычислении |
|
=ЕСЛИОШИБКА(A4/B4;»Ошибка при вычислении») |
Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление «» на 23), не обнаруживает ошибок и возвращает результат вычисления по формуле. |
ЕСЛИОШИБКА в формулах массива
Как вы, наверное, знаете, формулы массива в Excel предназначены для выполнения нескольких вычислений внутри одной формулы. Если вы в аргументе значение функции ЕСЛИОШИБКА укажете формулу или выражение, которое возвращает массив, она также обработает и вернет массив значений для каждой ячейки в указанном диапазоне. Пример ниже поможет пояснить это.
Допустим, у вас есть Сумма в столбце B и Цена в столбце C, и вы хотите вычислить Количество. Это можно сделать с помощью следующей формулы массива, которая делит каждую ячейку в диапазоне B2:B4 на соответствующую ячейку в диапазоне C2:C4, а затем суммирует результаты:
=СУММ(($B$2:$B$4/$C$2:$C$4))
Формула работает нормально, пока в диапазоне делителей нет нулей или пустых ячеек. Если есть хотя бы одно значение 0 или пустая строка, то возвращается ошибка: #ДЕЛ/0! Из-за одной некорректной позиции мы не можем получить итоговый результат.
Чтобы исправить эту ситуацию, просто вложите деление внутрь формулы ЕСЛИОШИБКА:
=СУММ(ЕСЛИОШИБКА($B$2:$B$4/$C$2:$C$4;0))
Что делает эта формула? Делит значение в столбце B на значение в столбце C в каждой строке (3500/100, 2000/50 и 0/0) и возвращает массив результатов {35; 40; #ДЕЛ/0!}. Функция ЕСЛИОШИБКА перехватывает все ошибки #ДЕЛ/0! и заменяет их нулями. Затем функция СУММ суммирует значения в итоговом массиве {35; 40; 0} и выводит окончательный результат (35+40=75).
Примечание. Помните, что ввод формулы массива должен быть завершен нажатием комбинации Ctrl + Shift + Enter (если у вас не Office365 или Excel2021 – они понимают формулы массива без дополнительных телодвижений).
Преимущества использования функции IFERROR с IF: примеры кода
Функция IFERROR в Excel позволяет обрабатывать ошибки, которые могут возникать при выполнении других функций. Вместо того, чтобы показывать ошибку, функция IFERROR позволяет задать альтернативное значение или действие. В сочетании с функцией IF, IFERROR становится еще более мощным инструментом для обработки условий и ошибок. Преимущества использования функции IFERROR с IF включают:
| Пример кода | Описание |
|---|---|
| =IFERROR(A1/B1, «Ошибка деления») | Если деление A1 на B1 вызывает ошибку, то возвратить «Ошибка деления», в противном случае — результат деления |
| =IF(IFERROR(A1/B1, «»)>10, «Больше 10», «Меньше или равно 10») | Если деление A1 на B1 вызывает ошибку, то возвратить пустую строку, в противном случае — проверить, больше ли результат деления 10 и вывести соответствующее сообщение |
| =IF(IFERROR(VLOOKUP(A1, B1:C10, 2, FALSE), «»)«» , «Найдено», «Не найдено») | Если функция VLOOKUP не может найти соответствие в диапазоне B1:C10 для значения A1, то возвратить пустую строку, в противном случае — вывести сообщение «Найдено» |
Функции IF и IFERROR вместе обеспечивают гибкую возможность обработки условий и ошибок в Excel. Это позволяет создавать более надежные и полезные формулы, которые могут автоматически адаптироваться к различным ситуациям и возвращать соответствующие значения или сообщения. Использование функции IFERROR с IF является одним из ключевых методов обработки ошибок в Excel и позволяет сделать формулы более надежными и эффективными.
SEARCH – находим данные в строке
С помощью этой функции вы быстро найдете необходимые вам строки с большом массиве данных.
Синтаксис:
=SEARCH(«что искать»;где искать)
Функция используется в разных ситуациях:
- выделить ключевые фразы с необходимым интентом (например, брендированные или связанные с определенной тематикой, товаром или услугой);
- найти определенные символы в URL (например, UTM-параметры или знак вопроса);
- найти URL для целей линкбилдинга – например, содержащие слова «guest-post»).
=SEARCH(«porta»;A1)
Но в таком виде формула при отсутствии слова «porta» в ключе выведет нам #VALUE!.. Кроме того, при наличии этого слова в искомой ячейке функция будет проставлять номер символа, с которого начинается это слово. Выглядит результат так:
Для получения результата поиска в удобной для нас форме используем дополнительно функции IF и IFERROR:
=IFERROR(IF(SEARCH(«porta»;A1)>0;»бренд»;»0″))
Пример 3: ЕСЛИОШИБКА в формулах массива
Проблема функций МИН и МАКС при работе с массивами в том, что, если в массиве присутствует хотя бы одна ошибка, они также возвращают ошибку.
Поэтому, если есть вероятность такого события, функцию ЕСЛИОШИБКА нужно использовать как обработчик, чтобы избежать результирующей ошибки.
Формула для поиска позиции первого символа латиницы:
{=МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1);""))}
Механика ее работы такова:
- Функция СИМВОЛ создает массив букв английского алфавита;
- Функция ПОИСК ищет позицию каждой буквы в строке;
- Если буква не найдена, функция вернет ошибку Н/Д;
- Функция ЕСЛИОШИБКА возвращает пустую строку в таких случаях, а числа оставляет как они есть;
- Функция МИН пропускает пустые строки и возвращает минимальное число;
- Если весь массив будет состоять из пустых строк, функция МИН вернет 0.
А такая формула использует массив констант и ищет позицию первой цифры:
Читайте подробнее в статье про формулы массива.
ARRAYFORMULA – протягиваем формулу вниз в один клик
В работе с данными практически каждый раз приходится прописывать формулу для всех ячеек в столбце. «Тянуть» ее, зажав левую кнопку мыши, или копипастить – это прошлый век.
Достаточно заключить исходную функцию в функцию ARRAYFORMULA, и формула применится ко всем ячейкам ниже. Причем при удалении добавлении строк формула все равно будет работать – без пробелов в расчетах.
Синтаксис:
=ARRAYFORMULA(исходная формула)
Пример. Сделаем автоматическое применение формулы, описанной выше, для всех ячеек диапазона. Для этого заключаем исходную формулу в ARRAYFORMULA:
=ARRAYFORMULA(IFERROR(IF(B2:B*0,1>=300;»1″;»0″);»нет данных»))
Обратите внимание, что вместо ячейки B2 мы указали диапазон, для которого применяем формулу (B2:B – это весь столбец B, начиная со второй строки). Если указать одну ячейку, формула не сработает
Лайфхак. Нажмите сочетание клавиш CTRL+SHIFT+ENTER после ввода основной формулы, и функция ARRAYFORMULA применится автоматически.
ARRAYFORMULA работает не со всеми функциями. Например, она не совместима с GOOGLETRANSLATE и IMPORTXML, о которых расскажем ниже.
Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР
В ситуациях, когда вам нужно выполнить несколько операций ВПР в зависимости от того, была ли предыдущая ВПР успешной или неудачной, вы можете вложить две или более функции ЕСЛИОШИБКА одну в другую.
Предположим, у вас есть несколько отчетов о продажах из региональных отделений вашей компании, и вы хотите получить сумму по определенному идентификатору заказа. С ячейкой В9 в качестве критерия поиска (номер заказа) и тремя небольшими таблицами поиска (таблица 1, 2 и 3), формула выглядит следующим образом:
=ЕСЛИОШИБКА(ВПР(B9;A3:B6;2;0);ЕСЛИОШИБКА(ВПР(B9;D3:E6;2;0);ЕСЛИОШИБКА(ВПР(B9;G3:H6;2;0);»Не найден»)))
Результат будет выглядеть примерно так, как на рисунке ниже:
То есть, если поиск завершился неудачей (то есть, ошибкой) первой таблице, начинаем искать во второй, и так далее. Если нигде ничего не нашли, получим сообщение «Не найден».
Функция IFERROR (ЕСЛИОШИБКА) в Excel. Как использовать?
Функция IFERROR (ЕСЛИОШИБКА) в Excel лучше всего подходит для обработки случаев, когда формулы возвращают ошибку. Используя эту функцию, вы можете указать, какое значение функция должна возвращать вместо ошибки. Если функция в ячейке не возвращает ошибку, то возвращается её собственный результат.
Аргументы функции
- value (значение) — это аргумент, который проверяет, есть ли в ячейке ошибка. Обычно, ошибкой может быть результат какого либо вычисления;
- value_if_error (значение_если_ошибка) — это аргумент, который заменяет ошибку в ячейке (в случае её наличия) на указанное вами значение. Ошибки могут выглядеть так: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, #NULL! (английская версия Excel) или #ЗНАЧ!, #ДЕЛ/0, #ИМЯ?, #Н/Д, #ССЫЛКА!, #ЧИСЛО!, #ПУСТО! (русская версия Excel).
Дополнительная информация
- Если вы используете кавычки («») в качестве аргумента value_if_error (значение_если_ошибка), ячейка ничего не отображает в случае ошибки.
- Если аргумент value (значение) или value_if_error (значение_если_ошибка) ссылается на пустую ячейку, она рассматривается как пустая.
Пример 1. Заменяем ошибки в ячейке на пустые значения
Если вы используете функции, которые могут возвращать ошибку, вы можете заключить ее в функцию и указать пустое значение, возвращаемое в случае ошибки.
В примере, показанном ниже, результатом ячейки D4 является # DIV/0!.
Для того, чтобы убрать информацию об ошибке в ячейке используйте эту формулу:
В данном случае функция проверит, выдает ли формула в ячейке ошибку, и, при её наличии, выдаст пустой результат.
В качестве результата формулы, исправляющей ошибки, вы можете указать любой текст или значение, например, с помощью следующей формулы:
Если вы пользуетесь версией Excel 2003 или ниже, вы не найдете функцию IFERROR (ЕСЛИОШИБКА) . Вместо нее вы можете использовать обычную функцию IF или ISERROR.
Когда мы используем функцию VLOOKUP (ВПР) , часто сталкиваемся с тем, что при отсутствии данных по каким либо значениям, формула выдает ошибку “#N/A”.
На примере ниже, мы хотим с помощью функции VLOOKUP (ВПР) для выбранных студентов подставить данные из результатов экзамена.
На примере выше, в списке студентов с результатами экзамена нет данных по имени Иван, в результате, при использовании функции VLOOKUP (ВПР) , формула нам выдает ошибку.
Как раз в этом случае мы можем воспользоваться функцией IFERROR (ЕСЛИОШИБКА) , для того, чтобы результат вычислений выглядел корректно, без ошибок. Добиться этого мы можем с помощью формулы:
Пример 3. Возвращаем значение “0” вместо ошибок формулы
Если у вас нет конкретного значения, которое вы бы хотели использовать для замены ошибок — оставляйте аргумент функции value_if_error (значение_если_ошибка) пустым, как показано на примере ниже и в случае наличия ошибки, функция будет выдавать “0”:
REGEXEXTRACT – извлекаем нужный текст из ячеек
Эта функция позволяет извлечь из строки с данными текст, описанный с помощью регулярных выражений RE2, поддерживаемых Google. Синтаксис регулярных выражений достаточно сложный, больше примеров вы найдете в справке Google.
Синтаксис:
=REGEXEXTRACT(где искать;”регулярное выражение”)
Пример 1. У нас есть список URL. Нужно извлечь домены. Здесь нам поможет регулярное выражение:
^(?:https?:\/\/)?(?:+@)?(?:www\.)?([^:\/\n]+)
Пример 2. В списке ключевых фраз нужно найти брендированные ключи со словами «porta» и «порта». Для поиска фраз с вхождением любого из этих слов используем регулярное выражение:
(?i)(\W|^)(porta|порта)(\W|$)
Как видите, в таблицах можно кроить и резать данные так, как вам будет нужно, достаточно разобраться в формулах.
IFERROR Examples in VBA
VBA does not have a built-in IFERROR Fucntion, but you can also access the Excel IFERROR Function from within VBA:
Application.WorksheetFunction gives you access to many (not all) Excel functions in VBA.
Typically IFERROR is used when reading values from cells. If a cell contains an error, VBA may throw an error message when attempting to process the cell value. Try this out with the example code below (where cell B2 contains an error):
The code assigns cell B2 to a variable. The second variable assignment throws an error because the cell value is #N/A, but the first works fine because of the IFERROR function.
You can also use VBA to create a formula containing the IFERROR Function:
Error handling in VBA is much different than in Excel. Typically, to handle errors in VBA, you will use . VBA Error Handling looks like this:
Notice we use If Err.Number <> 0 Then to identify if an error has occurred. This is a typical way to catch errors in VBA. However, the IFERROR Function has some uses when interacting with Excel cells.
VLOOKUP – ищем значения в другом диапазоне данных
Функция выполняет поиск ключа в первом столбце диапазона и возвращает значение указанной ячейки в найденной строке.
Синтаксис:
=VLOOKUP(запрос;диапазон;номер_столбца;)
Пример 1. Есть два массива ключевых фраз, полученных из разных источников. Нужно найти ключи в первом массиве, которые не встречаются во втором массиве. Для этого используем формулу:
=VLOOKUP(A2:A;B2:B;1;false)
Что мы сделали:
- задали диапазон A2:A, из которого берем ключи для сравнения;
- задали диапазон B2:B, с которым сравниваем ключи из столбца А;
- задали номер столбца (1), из которого подтягиваем ключи при совпадениях;
- false – указали, что сортировка нам не нужна.
Функция VLOOKUP часто используется при поиске данных на разных листах или в разных документах.
Пример 2. Мы выгрузили данные из Яндекс.Вебмастера и Google Search Console об индексации страниц сайта. Наша задача – сопоставить данные и определить, какие страницы индексируются в одном поисковике, но не индексируются в другом.
Заносим результаты выгрузок в файл Google Sheets. На одном листе – URL из Google, на втором – из Яндекса.
В ячейке C2 прописываем функцию VLOOKUP. Сразу заключаем в функцию в ARRAYFORMULA для автоматического протягивания вниз:
=ARRAYFORMULA(VLOOKUP(A2:A;Yandex!A2:A;1;false))
Теперь мы сразу видим, какие страницы проиндексированы в Google, но не проиндексированы в Яндексе.
Что мы сделали:
- задали диапазон A2:A текущего листа, из которого берем значение для сравнения;
- задали диапазон Yandex!A2:A листа с выгрузкой из Яндекса, с которым будем сравнивать значения URL из Google;
- указали номер столбца листа с выгрузкой из Яндекса, значения из которого подтягиваем при совпадении значений из сравниваемых диапазонов;
- false – указали, что сортировка нам не нужна.
Если же вам нужно проверить одновременно индексацию конкретных страниц в Яндексе и Google, воспользуйтесь инструментом от PromoPult. Загрузите список URL и запустите проверку. Если страница проиндексирована в поисковике, в столбце будет цифра 1, если нет – 0.
Каким пользоваться этим инструментом и в каких ситуациях он полезен, читайте в этом гайде.
Использование функции Iferror для обработки ошибок
Функция Iferror в Excel предназначена для обработки и управления ошибками, которые могут возникнуть при выполнении различных вычислений или операций в таблице Excel. Она проверяет указанное выражение или значение и возвращает заданное значение, если исходное выражение вызывает ошибку, или само исходное выражение, если оно не вызывает ошибку.
Функция Iferror имеет следующий синтаксис: =IFERROR(value, value_if_error). Где value — исходное выражение, которое нужно проверить на наличие ошибки, и value_if_error — значение, которое нужно вернуть, если исходное выражение вызывает ошибку.
Функция Iferror может быть полезна во множестве ситуаций, когда исходные данные могут содержать потенциальные ошибки или некорректные значения. Например, она может быть использована для сокрытия ошибок, замены ошибочных значений или управления потенциальными ошибками при вводе данных.
Вот несколько примеров использования функции Iferror:
- Замена ошибок на заданное значение
Если вы вводите формулы, которые могут вызывать ошибки, например деление на ноль или ссылку на несуществующую ячейку, вы можете использовать функцию Iferror, чтобы заменить ошибки на пустую строку или другое заданное значение. Например, можно использовать следующее выражение:
=IFERROR(A1/B1, «»)
Если деление A1 на B1 вызывает ошибку, функция Iferror вернет пустую строку вместо ошибки, иначе она вернет результат деления.
Показ скрытых ошибок
Иногда ошибки могут быть полезны для анализа данных или выявления проблемных мест в таблице. В этом случае можно использовать Iferror для отображения ошибок. Например:
=IFERROR(AVERAGE(A1:B1), «Ошибка в данных»)
Если функция AVERAGE вызывает ошибку (например, если в диапазоне ячеек A1:B1 нет числовых значений), функция Iferror вернет сообщение «Ошибка в данных».
Обработка ошибочных значений
Если ваши исходные данные содержат значения, которые можно считать ошибочными или которые нужно исключить из рассмотрения, функция Iferror может быть использована для их обработки. Например:
=IFERROR(MAX(A1:A10), 0)
Если в диапазоне ячеек A1:A10 есть ошибки или пустые значения, функция Iferror вернет 0 вместо ошибочного значения или пустой ячейки.
Важно помнить, что функция Iferror проверяет только ошибки, связанные с вычислениями и операциями Excel. Она не обрабатывает другие типы ошибок, такие как ошибки ввода данных или ошибки форматирования
Благодаря функции Iferror вам легче обрабатывать и управлять ошибками в таблице Excel, делая вашу работу более эффективной и удобной.
LEN – считаем количество символов в ячейке
Эта функция особенно полезна при составлении объявлений контекстной рекламы – когда важно не заступать за отведенное количество символов для заголовков, описаний, отображаемых URL, быстрых ссылок и уточнений
В SEO функция LEN применяется, например, при составлении мета-тегов title и description. Символы функция считает с пробелами.
Синтаксис:
=LEN(ячейка с текстом)
Пример. Нам нужно составить тайтлы для всех страниц сайта. Мы знаем, что в результатах поиска отображается около 55 символов. Наша задача – составить тайтлы так, чтобы самая важная информация была в первых 55 символах. Прописываем формулу LEN для заполняемых ячеек. Теперь мы точно знаем, когда приближаемся к отображаемым 55 символам.
SUBSTITUTE – меняем/удаляем пробелы и спецсимволы
Универсальная функция замены/удаления символов в ячейках.
Синтаксис:
=SUBSTITUTE(где искать;»что искать»;»на что менять»;номер соответствия)
Номер соответствия – порядковый номер встреченного значения на замену, например, первое встреченное заменить, остальные оставить. Опциональный параметр.
Пример. У нас есть выгрузка ключевых фраз из Яндекс.Вордстат. Многие ключи содержат плюсики. Нам нужно их удалить.
Формула будет иметь вид:
=SUBSTITUTE(B12;»+»;»»;)
Что мы сделали:
- где искать – указали ячейку с данными;
- «что искать» – указали плюсик, который нужно удалить;
- «на что менять» – поскольку символ нужно удалить, мы указали кавычки без символов внутри; если бы нам нужна была замена, здесь бы мы прописали текст, на который нужно заменить плюсик;
- номер соответствия – здесь мы ничего не указали, и функция удалит все плюсы в фразе; если бы мы указали 1, то функция удаляла бы только первый плюсик, если 2 – второй и т. д.
How to use IFERROR function in Excel?
In this story I will explain how to handle error with IFERROR function in Excel, this is used to get an alternative result when the formula gets us error in some cases.
Resolving the #N/A error using IFERROR and setting a default value for such errors.
I believe knowledge should to be shared, this is also a learning for me. I will keep posting my learnings here, and try to explain them in the simplest way possible.
Microsoft Excel is a very powerful tool that gives you an edge when working with data. It improves your efficiency and provides better analysis. I will be sharing more Advanced Excel tips and tricks here.
IF – базовая логическая функция
Это одна из базовых функций, знакомых вам по Excel. Она помогает при решении разных SEO-задач. Формула IF выводит одно значение, если логическое выражение истинное, и другое – если оно ложное.
Синтаксис:
=IF(логическое_выражение;»значение_истина»;»значение_ложь»)
Пример. Есть список ключей с частотностями. Наша цель – занять ТОП-3. При этом мы хотим выбрать только такие ключи, каждый из которых приведет нам минимум 300 посетителей в месяц.
Определяем, какая доля трафика приходится на третью позицию в органике. Для этого заходим в сервис Advanced webranking и видим, что третья позиция приводит около 10% трафика из органики (конечно, эта цифра неточная, но это лучше, чем ничего).
Составляем выражение IF, которое будет возвращать значение 1 для ключей, который приведут минимум 300 посетителей, и 0 – для остальных ключей:
=IF(B2*0.1>=300;»1″;»0″)
Обратите внимание, в строке 7 формула выдала ошибку, поскольку значение частотности задано в неверном формате. Для подобных ситуаций есть продвинутая версия функции IF – IFERROR
Важно: использование в формуле запятой или точки для десятичных дробей определено в настройках ваших таблиц
More IFERROR Formula Examples
Add Excel automation to your workbook with just a few clicks.
Nested IFERROR – VLOOKUP Multiple Sheets
You can nest an IFERROR inside another IFERROR to perform 3 separate calculations. Here we will use two IFERRORs to perform VLOOKUPs on 3 separate worksheets:
Index / Match & XLOOKUP
Of course, IFERROR will also work with Index / Match and XLOOKUP formulas as well.
The XLOOKUP Function is an advanced version of the VLOOKUP function.
IFERROR INDEX / MATCH
INDEX and MATCH can be used to create more powerful VLOOKUPs (similar to how the new XLOOKUP function works) in Excel.
IFERROR in Arrays
Array formulas in Excel are used to perform several calculations through a single formula. Let’s suppose there are three columns of Year, Sales, and Avg Price. You can find out the total quantity with the following formula in the E column.
The formula performs well until it attempts to divde by zero, resulting in the #DIV/0! error.
You can use the IFERROR function like this to resolve the error:
Notice that the IFERROR function must be nested inside the SUM Function, otherwise the IFERROR will apply to the sum total and not each individual item in the array.
IFNA vs. IFERROR
The IFNA Function works exactly the same as the IFERROR Function except the IFNA function will only catch #N/A errors. This is extremely useful when working with lookup functions: regular formula errors will still be detected, but no error will appear if the lookup value is not found.
If ISERROR
If you are still using Microsoft Excel 2003 or an older version, then you can substitute IFERROR with a combination of IF and ISERROR. Here is a brief example:
IMPORTRANGE – импортируем данные из других таблиц
Функция позволяет вставить в текущий файл данные из других таблиц.
Синтаксис:
=IMPORTRANGE(«ссылка на документ»;»ссылка на диапазон данных»)
Пример:
=IMPORTRANGE(«https://docs.google.com/spreadsheets/d/ХХХХХХХХ/»,»имя листа!A2:A25″)
Пример. Вы продвигаете сайт клиента. Над проектом работает три специалиста: линкбилдер, SEO-специалист и копирайтер. Каждый ведет свой отчет. Клиент заинтересован отслеживать процесс в режиме онлайн. Вы формируете для него один отчет с вкладками: «Ссылки», «Позиции», «Тексты». На эти вкладки с помощью функции IMPORTRANGE подтягиваются данные по каждому направлению.
Преимущество функции в том, что вы открываете доступ только к конкретным листам. При этом внутренние части отчетов специалистов остаются недоступны для клиентов.
UNIQUE – выводим данные без дублирующихся ячеек
Функция анализирует выделенный диапазон на предмет полных дублей и выводит только уникальные строки – в том же порядке, что и в исходном диапазоне.
Синтаксис:
=UNIQUE(диапазон данных)
Пример. Мы собрали ключи из Яндекс.Вордстат, поисковых подсказок, парсили слова конкурентов. Естественно, в этом массиве ключей у нас будут дубли. Нам они не нужны. Убираем их с помощью UNIQUE.
Если вы хотите «одним махом» очистить ядро от мусора, используйте бесплатный нормализатор слов. Он убирает дублирующиеся фразы (с учетом перестановок), меняет регистры, удаляет пробелы и спецсимволы. По сути, он делает то же самое, что и функции TRIM, SUBSTITUTE, LOWER и UNIQUE вместе взятые – только в один клик.