Если ошибка впр ложь

Функция ЕСЛИОШИБКА – примеры формул

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

«Дайте мне точку опоры, и я переверну землю», — сказал однажды Архимед. «Дайте мне формулу, и я заставлю ее вернуть ошибку», — сказал бы пользователь Excel. Здесь мы не будем рассматривать, как получить ошибки в Excel. Мы узнаем, как предотвратить их, чтобы ваши таблицы были чистыми, а формулы — понятными и точными.

Итак, вот о чем мы поговорим:

Что означает функция Excel ЕСЛИОШИБКА

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

Синтаксис функции Excel ЕСЛИОШИБКА следующий:

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

Где:

• Значение (обязательно) — что проверять на наличие ошибок. Это может быть формула, выражение или ссылка на ячейку.
• Значение_если_ошибка (обязательно) — что возвращать при обнаружении ошибки. Это может быть пустая строка (получится пустая ячейка), текстовое сообщение, числовое значение, другая формула или вычисление.

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

Рассмотрим простой пример:

Чтобы этого не произошло, используйте формулу ЕСЛИОШИБКА, чтобы перехватывать и обрабатывать их нужным вам образом.

Если ошибка, то пусто

Укажите пустую строку (“”) в аргументе значение_если_ошибка, чтобы вернуть пустую ячейку, если обнаружена ошибка:

=ЕСЛИОШИБКА(A4/B4; «»)

Вернемся к нашему примеру и используем ЕСЛИОШИБКА:

Как видите по сравнению с первым скриншотом, вместо стандартных сообщений мы видим просто пустые ячейки.

Если ошибка, то показать сообщение

Вы также можете отобразить собственное сообщение вместо стандартного обозначения ошибок Excel:

=ЕСЛИОШИБКА(A4/B4; «Ошибка в вычислениях»)

Перед вами – третий вариант  нашей небольшой таблицы.

5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel

1. ЕСЛИОШИБКА в Excel обрабатывает все типы ошибок, включая #ДЕЛ/0!, #Н/Д, #ИМЯ?, #NULL!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.
2. В зависимости от содержимого аргумента значение_если_ошибка функция может заменить ошибки вашим текстовым сообщением, числом, датой или логическим значением, результатом другой формулы или пустой строкой (пустой ячейкой).
3. Если аргумент значение является пустой ячейкой, он обрабатывается как пустая строка (»’), но не как ошибка.
4. ЕСЛИОШИБКА появилась в Excel 2007 и доступна во всех последующих версиях Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021 и Excel 365.
5. Чтобы перехватывать ошибки в Excel 2003 и более ранних версиях, используйте функцию ЕОШИБКА в сочетании с функцией ЕСЛИ, например как показано ниже: 

=ЕСЛИ(ЕОШИБКА(A4/B4);»Ошибка в вычислениях»;A4/B4)

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

ЕСЛИОШИБКА с функцией ВПР

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

ЕСЛИОШИБКА(ВПР( … );»Не найдено»)

Если искомое значение отсутствует в таблице, которую вы просматриваете, обычная формула ВПР вернет ошибку #Н/Д:

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

=ЕСЛИОШИБКА(ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ); «Не найдено»)

На скриншоте ниже показан пример ЕСЛИОШИБКА вместе с ВПР в Excel:

Если вы хотите перехватывать только #Н/Д, но не все подряд ошибки, используйте функцию ЕНД вместо ЕСЛИОШИБКА. Она просто возвращает ИСТИНА или ЛОЖЬ в зависимости от появления ошибки #Н/Д. Поэтому нам здесь еще понадобится функция ЕСЛИ, чтобы обработать эти логические значения:

=ЕСЛИ(ЕНД(ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ)); «Не найдено»;ВПР(D3; $A$3:$B$5; 2;ЛОЖЬ))

Дополнительные примеры формул Excel ЕСЛИОШИБКА ВПР можно также найти в нашей статье Как убрать сообщение #Н/Д в ВПР?

Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР

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

Предположим, у вас есть несколько отчетов о продажах из региональных отделений вашей компании, и вы хотите получить сумму по определенному идентификатору заказа. С ячейкой В9 в качестве критерия поиска (номер заказа) и тремя небольшими таблицами поиска (таблица 1, 2 и 3), формула выглядит следующим образом:

=ЕСЛИОШИБКА(ВПР(B9;A3:B6;2;0);ЕСЛИОШИБКА(ВПР(B9;D3:E6;2;0);ЕСЛИОШИБКА(ВПР(B9;G3:H6;2;0);»Не найден»)))

Результат будет выглядеть примерно так, как на рисунке ниже:

То есть, если поиск завершился неудачей (то есть, ошибкой) первой таблице, начинаем искать во второй, и так далее. Если нигде ничего не нашли, получим сообщение «Не найден».

ЕСЛИОШИБКА в формулах массива

Как вы, наверное, знаете, формулы массива в 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 – они понимают формулы массива без дополнительных телодвижений).

ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?

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

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

Например, чтобы отловить ошибки ВПР, вы можете использовать любую из приведенных ниже формул.

В Excel 2007 — Excel 2016:

ЕСЛИОШИБКА(ВПР( … ); «Не найдено»)

Во всех версиях Excel:

ЕСЛИ(ЕОШИБКА(ВПР(…)); «Не найдено»; ВПР(…))

Обратите внимание, что в формуле ЕСЛИ ЕОШИБКА ВПР вам нужно дважды выполнить ВПР.  Чтобы лучше понять, расшифруем: если ВПР приводит к ошибке, вернуть «Не найдено», в противном случае вывести результат ВПР.

А вот простой пример формулы Excel ЕСЛИ ЕОШИБКА ВПР:

=ЕСЛИ(ЕОШИБКА(ВПР(D2; A2:B5;2;ЛОЖЬ)); «Не найдено»; ВПР(D2; A2:B5;2;ЛОЖЬ ))

Применение вы видите на рисунке ниже.

ЕСЛИОШИБКА против ЕСНД – что использовать?

Представленная в Excel 2013, ЕСНД (IFNA в английской версии) — это еще одна функция для проверки формулы на наличие ошибок. Его синтаксис похож на синтаксис ЕСЛИОШИБКА:

ЕСНД(значение; значение_если_НД)

Чем ЕСНД отличается от ЕСЛИОШИБКА? Функция ЕСНД перехватывает только ошибки #Н/Д, тогда как ЕСЛИОШИБКА обрабатывает все типы ошибок.

В каких ситуациях вы можете использовать ЕСНД? Когда нецелесообразно скрывать все ошибки. Например, при работе с важными данными вы можете захотеть получать предупреждения о возможных ошибках в вашем наборе данных (случайном делении на ноль и т.п.), а стандартные сообщения об ошибках Excel с символом «#» могут быть яркими визуальными индикаторами проблем.

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

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

=ЕСНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ); «Не найдено»)

Или подойдет комбинация ЕСЛИ ЕНД для старых версий Excel:

=ЕСЛИ(ЕНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ));»Не найдено»; ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ))

Как видите, формула ЕСНД с ВПР возвращает «Не найдено» только для товара, которого нет в таблице поиска (Сливы). Для персиков она показывает #ДЕЛ/0! что указывает на то, что наша таблица поиска содержит ошибку деления на ноль.

Рекомендации по использованию ЕСЛИОШИБКА в Excel

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

Эти простые рекомендации могут помочь вам сохранить приемлемый баланс.

1. Не ловите ошибки без весомой на то причины.
2. Оберните в ЕСЛИОШИБКА только ту часть формулы, где по вашему мнению могут возникнуть проблемы.
3. Чтобы обрабатывать только определенные ошибки, используйте другую функцию обработки ошибок с меньшей областью действия:
   • ЕСНД или ЕСЛИ ЕНД для обнаружения только ошибок #H/Д.
   • ЕОШ для обнаружения всех ошибок, кроме #Н/Д.

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

Также рекомендуем:

Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном…

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

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

4 способа, как сделать левый ВПР в Excel. — Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом…

Почему не работает ВПР в Excel? — Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте). Давайте постараемся вместе…

ВПР с несколькими условиями: 5 примеров. — Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными…

• Что делает ЕСЛИОШИБКА?
• Синтаксис
• Пример 1: ЕСЛИОШИБКА + ВПР
• Пример 2: ЕСЛИОШИБКА + деление на ноль
• Пример 3: ЕСЛИОШИБКА в формулах массива
• Другие логические функции

Раздел функций	Логические
Название на английском	IFERROR
Волатильность	Не волатильная
Похожие функции	ЕСЛИ, ЕОШ

Что делает ЕСЛИОШИБКА?

Часто при использовании формул, если результат возвращает ошибку, нужно обрабатывать ее, а если нет – возвращать результат вычисления.

Именно эту задачу и решает функция ЕСЛИОШИБКА.

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

Оцениваются следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!

Эквивалентным сочетанием была бы формула на основе комбинации функции ЕСЛИ и ЕОШ.

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

ЕСЛИ(ЕОШ(вычисление);"результат-если-ошибка";вычисление)

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

Также использование функции упрощает синтаксис формул.

Синтаксис

Синтаксис функции ЕСЛИОШИБКА предполагает всего два аргумента, оба – обязательные:

=ЕСЛИОШИБКА(Значение-Или-Вычисление;Значение-если-ошибка)

Пример 1: ЕСЛИОШИБКА + ВПР

Наиболее характерный пример использования – в паре с функцией ВПР при поиске данных в больших таблицах.

=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"ошибка")
=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"")

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

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

Пример 2: ЕСЛИОШИБКА + деление на ноль

Задача маркетолога – произвести оценку эффективности рекламных кампаний. Один из ключевых показателей – стоимость привлечения клиента. Рассчитывается он довольно просто – расходы по рекламным кампаниям делятся на количество приведенных ими клиентов.

Что делать, когда кампания не привела ни одного? Вычисление выдаст ошибку

#ДЕЛ/0!

Оставлять ее не стоит, ведь деньги на рекламу были потрачены, и нужно это учитывать.

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

Наиболее наглядна польза от такой формулы – если использовать условное форматирование с цветовой шкалой. Сразу бросаются в глаза эффективные и неэффективные кампании.

Пример 3: ЕСЛИОШИБКА в формулах массива

Проблема функций МИН и МАКС при работе с массивами в том, что, если в массиве присутствует хотя бы одна ошибка, они также возвращают ошибку.

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

Формула для поиска позиции первого символа латиницы:

{=МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1);""))}

Механика ее работы такова:

• Функция СИМВОЛ создает массив букв английского алфавита;
• Функция ПОИСК ищет позицию каждой буквы в строке;
• Если буква не найдена, функция вернет ошибку Н/Д;
• Функция ЕСЛИОШИБКА возвращает пустую строку в таких случаях, а числа оставляет как они есть;
• Функция МИН пропускает пустые строки и возвращает минимальное число;
• Если весь массив будет состоять из пустых строк, функция МИН вернет 0.

А такая формула использует массив констант и ищет позицию первой цифры:

{=МИН(ЕСЛИОШИБКА(ПОИСК({1:2:3:4:5:6:7:8:9:0};A1);""))}

Читайте подробнее в статье про формулы массива.

Другие логические функции

ЕСЛИ, И, ИЛИ, НЕ

Понравилась статья? Поддержите ее автора!
Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!

Функция «ЕСЛИОШИБКА» в Excel.

​Смотрите также​​ хотя бы одно​ ​ «логическое_выражение». Когда содержимое​ ​ нет.​​ 2007, при работе​​ беспокоясь о том,​ ​ регистра как одинаковые.​​не будет работать​ ошибке​(Конвертировать в число)​​. Более подробно о​​#N/A​​ Чтобы внедрить данное​​ нет, оператор сообщает​
​ИЛИ​ следует выделить такие:​функцию «ЕСЛИОШИБКА» в​Есть две похожие​ условие истинно, то​ графы больше 20,​Вручную можно сделать​ в более ранних​​ что придётся обновлять​​ Поэтому, если в​ и сообщит об​#VALUE!​ из контекстного меню.​ том, как искать​(#Н/Д),​
​ условие в нашу​ значение​может включать в​ИСТИНА;​Excel​функции в​ результат будет истинным.​ появляется истинная надпись​ так:​
​ версиях Вам придётся​ все связанные формулы​ таблице есть несколько​ ошибке​(#ЗНАЧ!), когда значение,​Если такая ситуация со​ точное и приближенное​#NAME?​ формулу, применим функцию​ИСТИНА​ себя от 1​ЛОЖЬ;​вставлять в другие​Excel «ЕСЛИОШИБКА» и «ЕОШИБКА»​ Суть такова: ЕСЛИ​ «больше 20». Нет​Выделяем диапазон замены​ использовать комбинацию​ поиска.​ элементов, которые различаются​#ЗНАЧ!​
​ использованное в формуле,​ многими числами, выделите​ совпадение с функцией​(#ИМЯ?) и​НЕ​или​ до 255 условий.​ЕСЛИ;​ формулы с другими​-​ а = 1​ – «меньше или​ (например А1:А10)​ЕСЛИ​Этот заголовок исчерпывающе объясняет​ только регистром символов,​(даже если рабочая​ не подходит по​​ их и щелкните​​ВПР​ ​#VALUE!​​:​ЛОЖЬ​В отличие от двух​
​ЕСЛИОШИБКА;​
​ функциями. Например.​
​это​ ИЛИ а =​ равно 20».​Заменяем «=» на​(IF) и​ суть проблемы, правда?​ функция ВПР возвратит​ книга с таблицей​ типу данных. Что​ по выделенной области​
​.​​(#ЗНАЧ!), появляющихся при​ ​=ЕСЛИ(ИЛИ(C4.​​. Синтаксис данной функции​ предыдущих операторов, функция​ИЛИ;​=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B11;НАИМЕНЬШИЙ(ЕСЛИ($F$2=A2:A11;СТРОКА(B2:B11)-1;»»);​логические функции в​
​ 2 ТОГДА значение​Внимание! Слова в формуле​​ «ё»​ЕОШИБКА​Решение:​
​ первый попавшийся элемент,​ поиска в данный​ касается​ правой кнопкой мыши.​Как Вы, вероятно, знаете,​ работе с функцией​Для того, чтобы скопировать​

excel-office.ru

Функции СРЗНАЧЕСЛИМН и ЕСЛИОШИБКА

​ следующий:​

​НЕ​И;​СТРОКА()-5));»»)​Excel​ в ИНАЧЕ значение​ необходимо брать в​Заменяем «ё» на​

Хотите узнать больше?

​(ISERROR) вот так:​

​Всегда используйте абсолютные​

​ не взирая на​

​ момент открыта).​

​ВПР​

​ В появившемся контекстном​

support.office.com

Логические функции в программе Microsoft Excel

​ одно из самых​ВПР​ данную функцию в​= ЕОШИБКА(значение)​имеет всего лишь​НЕ;​Эта формула взята​. Эти функции не​ с.​ кавычки. Чтобы Excel​ «ёеслиошибка(«​=IF(ISERROR(VLOOKUP формула),»Ваше сообщение при​ ссылки на ячейки​ регистр.​Для получения дополнительной информации​​, то обычно выделяют​​ меню выберите​ значительных ограничений​​, а также приёмы​​ ячейки столбца таблицы,​. В роли​ один аргумент. Она​ЕОШИБКА;​

​ из статьи «Выбрать​ дают в таблице​

Основные операторы

​Функции И и ИЛИ​ понял, что нужно​В соседнем столбце​

• ​ ошибке»,VLOOKUP формула)​
• ​ (с символом​
• ​Решение:​
• ​ о функции​
• ​ две причины ошибки​
• ​Format Cells​
• ​ВПР​
• ​ и способы борьбы​
• ​ где указана величина​

​ аргумента выступает исключительно​ меняет значение выражения​

​ЕПУСТО.​ сразу много данных​ написать слова​ могут проверить до​ выводить текстовые значения.​ пишем =A1&»;»»»»)»​=ЕСЛИ(ЕОШИБКА(ВПР формула);»Ваше сообщение при​$​Используйте другую функцию​

Функции ИСТИНА и ЛОЖЬ

​ВПР​​#ЗНАЧ!​​(Формат ячеек) >​это то, что​ с ними. Мы​ премии, становимся курсором​ ссылка на ячейку​ с​Существуют и менее распространенные​ из таблицы Excel».​

​#​​ 30 условий.​​Еще один пример. Чтобы​Протягиваем​ ошибке»;ВПР формула)​) при записи диапазона,​ Excel, которая может​, ссылающейся на другой​.​ вкладка​

Функции И и ИЛИ

​ она не может​​ начнём с наиболее​​ в нижний правый​ или на массив​ИСТИНА​ логические функции.​ Этой формулой выбираем​ССЫЛКА! или​​Пример использования оператора И:​​ получить допуск к​Копируем​Например, формула​​ например​​ выполнить вертикальный поиск​​ файл Excel, обратитесь​​Будьте внимательны: функция​Number​ смотреть влево, следовательно,​ частых случаев и​​ угол ячейки, в​​ ячеек.​на​У каждого из вышеуказанных​ из списка товаров,​

​#​​Пример использования функции ИЛИ:​​ экзамену, студенты группы​Вставляем на место​ЕСЛИ+ЕОШИБКА+ВПР​$A$2:$C$100​ (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС​ к уроку: Поиск​ВПР​(Число) > формат​​ столбец поиска в​​ наиболее очевидных причин,​ которой уже имеется​​Оператор​​ЛОЖЬ​ операторов, кроме первых​ те, которые выбрал​

Функция НЕ

​ДЕЛ/О!, если ошибка.​Пользователям часто приходится сравнить​​ должны успешно сдать​​ первоначальных формул как​, аналогична формуле​или​ и ПОИСКПОЗ) в​​ в другой рабочей​​не может искать​​Number​​ Вашей таблице должен​ почему​ формула. Появляется маркер​ЕПУСТО​​в пространстве указанного​​ двух, имеются аргументы.​

Функции ЕСЛИ и ЕСЛИОШИБКА

​ покупатель и переносим​Например, мы написали​​ две таблицы в​​ зачет. Результаты занесем​ значения​ЕСЛИОШИБКА+ВПР​​$A:$C​​ сочетании с​​ книге с помощью​​ значения, содержащие более​(Числовой) и нажмите​​ быть крайним левым.​​ВПР​ заполнения. Просто перетягиваем​делает проверку ячейки​ аргумента. Общий синтаксис​ Аргументами могут выступать,​ их в лист​ формулу. А ячейки,​ Excel на совпадения.​ в таблицу с​Заменяем «ё» на​, показанной выше:​

​. В строке формул​​СОВПАД​​ ВПР.​ 255 символов. Если​ОК​ На практике мы​не работает, поэтому​ его вниз до​ на то, пустая​ формулы выглядит следующим​ как конкретные числа​ заказа.​ на которые ссылается​ Примеры из «жизни»:​​ графами: список студентов,​​ «=»​

​=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),»»,VLOOKUP($F$2,$B$2:$C$10,2,FALSE))​​ Вы можете быстро​

Функции ЕОШИБКА и ЕПУСТО

​, которая различает регистр.​​Трудно представить ситуацию, когда​​ искомое значение превышает​.​ часто забываем об​ лучше изучать примеры​ конца таблицы.​ ли она или​

• ​ образом:​
• ​ или текст, так​
• ​Есть другие​
• ​ формула, пока пустые.​
• ​ сопоставить цены на​
• ​ зачет, экзамен.​
• ​Busine2012​

​=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));»»;ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))​ переключать тип ссылки,​ Более подробно Вы​ кто-то вводит значение​​ этот предел, то​​Это наименее очевидная причина​​ этом, что приводит​​ в том порядке,​Таким образом, мы получили​​ содержит значения. Если​​=НЕ(лог_значение)​ и ссылки, указывающие​логические функции в​ В таком случае,​ товар в разные​

​Обратите внимание: оператор ЕСЛИ​​: Если что-то нельзя​​На сегодня всё. Надеюсь,​ нажимая​ можете узнать из​ меньше​ Вы получите сообщение​ ошибки​​ к не работающей​​ в каком они​ таблицу с информацией​​ ячейка пустая, функция​​.​ адрес ячеек с​​Excel​​ для Excel это​ привозы, сравнить балансы​ должен проверить не​ сделать средствами Excel,​ этот короткий учебник​

Пример применения функций

​F4​ урока — 4​1​ об ошибке​

​#Н/Д​ формуле и появлению​ приведены в статье.​ о величине премии​ сообщает значение​Для более сложных конструкций​ данными.​. Читайте статью «Функция​ ошибка и в​ (бухгалтерские отчеты) за​ цифровой тип данных,​ можно попробовать сделать​ поможет Вам справиться​.​ способа сделать ВПР​, чтобы обозначить столбец,​#ЗНАЧ!​в работе функции​ ошибки​

​Исправляем ошибку #Н/Д​ для каждого работника​ИСТИНА​ используется функция​Оператор​ «ЕСЛИ» в Excel».Эта​ ячейке будет написано​ несколько месяцев, успеваемость​ а текстовый. Поэтому​ средствами VBA.​ со всеми возможными​Если Вы не хотите​ с учетом регистра​ из которого нужно​.​ВПР​#Н/Д​​Исправляем ошибку #ЗНАЧ! в​ предприятия в отдельности.​, если ячейка содержит​ЕСЛИ​ИСТИНА​ функция поможет посчитать​#​ учеников (студентов) разных​​ мы прописали в​​Примечание: VBA -​​ ошибками​

​ пугать пользователей сообщениями​ в Excel.​ извлечь значение. Хотя​Решение:​, поскольку зрительно трудно​.​ формулах с ВПР​Урок:​ данные –​. Данный оператор указывает,​принимает только определенное​ ячейки с определенными​

​ДЕЛ/О! Вот, чтобы вместо​ классов, в разные​ формуле В2= «зач.».​ это другими словами​ВПР​

​ об ошибках​​Как Вы уже знаете,​

​ это возможно, если​Используйте связку функций​ увидеть эти лишние​Решение:​Ошибка #ИМЯ? в ВПР​полезные функции Excel​ЛОЖЬ​ какое именно значение​ заданное значение. У​ данными.​ этой записи была​ четверти и т.д.​ В кавычки берем,​ макросы.​и заставит Ваши​#Н/Д​

​ВПР​

lumpics.ru

Функция ВПР не работает – способы устранения ошибок Н/Д, ИМЯ и ЗНАЧ

​ значение этого аргумента​ИНДЕКС+ПОИСКПОЗ​ пробелы, особенно при​​Если нет возможности​​ВПР не работает (проблемы,​Как видим, логические функции​. Синтаксис этого оператора​ является​ данной функции отсутствуют​Пример функции «ЕСЛИ» в​ пустая ячейка, и​Чтобы сравнить 2 таблицы​​ чтобы программа правильно​​Казанский​

​ формулы работать правильно.​,​возвращает из заданного​​ вычисляется другой функцией​​(INDEX+MATCH). Ниже представлена​ работе с большими​ изменить структуру данных​ ограничения и решения)​ являются очень удобным​ имеет такой вид:​ИСТИНА​ аргументы, и, как​​ Excel​​ применим эти функции.​ в Excel, можно​ распознала текст.​: Скопируйте эту команду​Урок подготовлен для Вас​#ЗНАЧ!​

​ столбца значение, соответствующее​ Excel, вложенной в​ формула, которая отлично​​ таблицами, когда большая​​ так, чтобы столбец​​ВПР – работа с​​ инструментом для проведения​​=ЕПУСТО(значение)​​, а какое​ правило, она практически​​смотрите в статье​​ У нас есть​ воспользоваться оператором СЧЕТЕСЛИ.​​ (макрос в одну​ командой сайта office-guru.ru​или​ первому найденному совпадению​​ВПР​​ справится с этой​ часть данных находится​ поиска был крайним​ функциями ЕСЛИОШИБКА и​ расчетов в программе​

• ​. Так же,​
• ​ЛОЖЬ​ всегда является составной​
• ​ «Как сделать тест​
• ​ такая таблица.​ Рассмотрим порядок применения​
• ​Часто на практике одного​ строку) БЕЗ перевода​Источник: https://www.ablebits.com/office-addins-blog/2014/08/27/excel-vlookup-not-working/​

Исправляем ошибку #Н/Д функции ВПР в Excel

​#ИМЯ?​​ с искомым. Однако,​​.​​ задачей:​​ за пределами экрана.​​ левым, Вы можете​​ ЕОШИБКА​ Microsoft Excel. Используя​ как и в​. Его общий шаблон​ частью более сложных​ в Excel» тут.​

1. Искомое значение написано с опечаткой

​В ячейке С7 мы​ функции.​ условия для логической​ строки: for each​Перевел: Антон Андронов​, можете показывать пустую​ Вы можете заставить​Итак, если случилось, что​=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))​Решение 1: Лишние пробелы​

2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР

​ использовать комбинацию функций​В формулах с​ сложные функции, можно​ предыдущем случае, аргументом​​ выглядит следующим образом:​​ выражений.​Как найти в​ написали такую формулу.​Для примера возьмем две​ функции мало. Когда​​ c in selection:c.formula=»=IFERROR(«​​Автор: Антон Андронов​

• ​ ячейку или собственное​ ее извлечь 2-е,​ аргумент​
• ​=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))​ в основной таблице​

3. Ошибка #Н/Д при поиске точного совпадения с ВПР

​ИНДЕКС​ВПР​​ задавать несколько условий​​ выступает ссылка на​=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если-ложь)​Оператор​ таблице данные по​ =A7/B7 Скопировали формулу​​ таблицы с техническими​​ нужно учесть несколько​ & mid(c.formula,2) &​СИНТАКСИС:​ сообщение. Вы можете​​ 3-е, 4-е или​​col_index_num​

4. Столбец поиска не является крайним левым

​Если Вы извлекаете данные​ (там, где функция​(INDEX) и​​сообщение об ошибке​​ одновременно и получать​ ячейку или массив.​. Таким образом,​ЛОЖЬ​ двум параметрам из​ по столбцу. В​ характеристиками разных кухонных​ вариантов принятия решений,​ «,»»»»)»:nextВ Excel выделите​=ЕСЛИОШИБКА(значение;значение_при_ошибке)​ сделать это, поместив​ любое другое повторение​​(номер_столбца) меньше​​ из другой рабочей​

​ ВПР)​​ПОИСКПОЗ​#N/A​ выводимый результат в​Теперь давайте рассмотрим применение​ если условие соблюдается,​, наоборот, принимает любое​​ двух столбцов, читайте​​ ячейках С9:С14 Excel​​ комбайнов. Мы задумали​​ выкладываем операторы ЕСЛИ​ диапазон, Alt+F11, Ctrl+G,​​АРГУМЕНТЫ:​​ВПР​

5. Числа форматированы как текст

​ значения, которое Вам​​1​​ книги, то должны​​Если лишние пробелы оказались​​(MATCH), как более​(#Н/Д) – означает​ зависимости от того,​ некоторых из вышеперечисленных​ то в ячейку,​

​ значение, которое не​ в статье «Соединить​ показывает ошибку., п.ч.​ выделение отличий цветом.​ друг в друга.​ Ctrl+V, Enter.​1. значение —​в функцию​

​ нужно. Если нужно​, функция​ указать полный путь​ в основной таблице,​

​ гибкую альтернативу для​not available​ выполнены эти условия​​ функций на конкретном​​ содержащую данную функцию,​ является истиной. Точно​ функции «ВПР» и​ ячейки столбцов А​ Эту задачу в​ Таким образом, у​caustic​ обязательный аргумент, проверяемый​ЕСЛИОШИБКА​

​ извлечь все повторяющиеся​​ВПР​ к этому файлу.​ Вы можете обеспечить​ВПР​​(нет данных) –​​ или нет. Применение​ примере.​

​ заполняют заранее указанные​ так же эта​ «СЦЕПИТЬ» в Excel».​ и В не​ Excel решает условное​ нас получиться несколько​:​​ на возникновение ошибок.​​(IFERROR) в Excel​ значения, Вам потребуется​​также сообщит об​​ Если говорить точнее,​​ правильную работу формул,​​.​​ появляется, когда Excel​​ подобных формул способно​

6. В начале или в конце стоит пробел

​Имеем список работников предприятия​ данные. Если условие​​ функция не имеет​​Обзор ​​ заполнены.​​ форматирование.​ функций ЕСЛИ в​обязательно попробую )​2. значение_при_ошибке —​ 2013, 2010 и​ комбинация из функций​ ошибке​

​ Вы должны указать​ заключив аргумент​Другой источник ошибки​ не может найти​

​ автоматизировать целый ряд​ с положенными им​ не соблюдается, то​ аргументов и входит​Функция СРЗНАЧЕСЛИ возвращает среднее​​Чтобы убрать эту​​Исходные данные (таблицы, с​​ Excel.​​ спасиб вам​

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

​ИНДЕКС​#ЗНАЧ!​ имя рабочей книги​

​lookup_value​#Н/Д​ искомое значение. Это​ действий, что способствует​​ заработными платами. Но,​​ ячейка заполняется другими​​ в более сложные​​ значение всех ячеек,​​ надпись ошибки, в​​ которыми будем работать):​Синтаксис будет выглядеть следующим​Логический оператор ЕСЛИ в​​ возвращаемое при ошибке​​ связку функций​​(INDEX),​​.​​ (включая расширение) в​​(искомое_значение) в функцию​

​в формулах с​
​ может произойти по​

​ экономии времени пользователя.​ кроме того, всем​ данными, указанными в​​ выражения.​​ которые удовлетворяют нескольким​​ ячейку С7 напишем​​Выделяем первую таблицу. Условное​ образом:​

Ошибка #ЗНАЧ! в формулах с ВПР

​ Excel применяется для​ при вычислении по​ЕСЛИ+ЕОШИБКА​​НАИМЕНЬШИЙ​​Если же аргумент​ квадратных скобках [​TRIM​ВПР​ нескольким причинам.​​Автор: Максим Тютюшев​​ работникам положена премия.​ третьем по счету​​Функция​​ условиям. Если результатом​

1. Искомое значение длиннее 255 символов

​ такую формулу. =ЕСЛИ(ЕОШИБКА(A7/B7);»»;A7/B7)​​ форматирование – создать​​=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))​ записи определенных условий.​ формуле. Возможны следующие​(IF+ISERROR) в более​(SMALL) и​col_index_num​ ], далее указать​​(СЖПРОБЕЛЫ):​​– это числа​

​Хорошая мысль проверить этот​​Этот урок объясняет, как​​ Обычная премия составляет​​ аргументе функции.​И​ является ошибка, функция​ Этой формулой мы​

​ правило – использовать​
​Здесь оператор проверяет два​

2. Не указан полный путь к рабочей книге для поиска

​ Сопоставляются числа и/или​ типы ошибок: #Н/Д,​ ранних версиях.​СТРОКА​(номер_столбца) больше количества​ имя листа, а​=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)​ в текстовом формате​ пункт в первую​ быстро справиться с​ 700 рублей. Но​Оператор​является связующим звеном​ ЕСЛИОШИБКА возвращает указанное​ говорим Excel –​ формулу для определения​ параметра. Если первое​ текст, функции, формулы​ #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,​

​Синтаксис функции​​(ROW).​​ столбцов в заданном​ затем – восклицательный​

​=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)​
​ в основной таблице​

​ очередь! Опечатки часто​ ситуацией, когда функция​

​ пенсионерам и женщинам​
​ЕСЛИОШИБКА​

​ между несколькими условиями.​ значение.​​ если при делении​​ форматируемых ячеек:​​ условие истинно, то​​ и т.д. Когда​​ #ЧИСЛО!, #ИМЯ? и​​ЕСЛИОШИБКА​​К сожалению, формулы с​​ массиве,​ знак. Всю эту​​Решение 2: Лишние пробелы​​ или в таблице​

​ возникают, когда Вы​ВПР​ положена повышенная премия​​, в случае если​​ Только при выполнении​Вложение функции в функцию​ ячейки А7 на​​В строку формул записываем:​​ формула возвращает первый​ значения отвечают заданным​ #ПУСТО!.​(IFERROR) прост и​

​ВПР​ВПР​​ конструкцию нужно заключить​​ в таблице поиска​ поиска.​ работаете с очень​(VLOOKUP) не хочет​ в размере 1000​ аргумент является истиной,​

3. Аргумент Номер_столбца меньше 1

​ всех условий, которые​Функция ЕСЛИ​ ячейку В7 будет​​ =СЧЕТЕСЛИ (сравниваемый диапазон;​​ аргумент – истину.​ параметрам, то появляется​КОММЕНТАРИИ:​ говорит сам за​перестают работать каждый​сообщит об ошибке​ в апострофы, на​​ (в столбце поиска)​​Это обычно случается, когда​

​ большими объёмами данных,​ работать в Excel​​ рублей. Исключение составляют​​ возвращает в ячейку​​ связывает данная функция,​​Функция СУММЕСЛИМН​​ ошибка (например, они​​ первая ячейка первой​ Ложно – оператор​​ одна запись. Не​​Если аргументы значение​

​ себя:​​ раз, когда в​​#REF!​ случай если имя​Если лишние пробелы оказались​​ Вы импортируете информацию​​ состоящих из тысяч​​ 2013, 2010, 2007​​ работники, по различным​

Ошибка #ИМЯ? в ВПР

​ его собственное значение.​​ она возвращает значение​​Функция СЧЁТЕСЛИМН​ пустые), то не​ таблицы)=0. Сравниваемый диапазон​ проверяет второе условие.​

​ отвечают – другая.​ или значение_при_ошибке является​

ВПР не работает (ограничения, оговорки и решения)

​IFERROR(value,value_if_error)​​ таблицу поиска добавляется​​(#ССЫЛ!).​ книги или листа​ в столбце поиска​ из внешних баз​ строк, или когда​ и 2003, а​​ причинам проработавшие в​​ Но, если аргумент​ИСТИНА​Функция СРЗНАЧЕСЛИМН​ пиши в ячейке​ – это вторая​​Примеры несколько условий функции​​Логические функции – это​

1. ВПР не чувствительна к регистру

​ пустой ячейкой, функция​​ЕСЛИОШИБКА(значение;значение_если_ошибка)​​ или удаляется новый​Простейший случай – ошибка​ содержит пробелы.​ – простыми путями​ данных или когда​ искомое значение вписано​ также, как выявить​ данном месяце менее​ ошибочный, тогда в​. Если хотя бы​Функция ЕСЛИОШИБКА​ С7 ошибку, а​

​ таблица.​​ ЕСЛИ в Excel:​ очень простой и​ рассматривает их как​То есть, для первого​ столбец. Это происходит,​#NAME?​​Вот полная структура функции​​ ошибку​ ввели апостроф перед​ в формулу.​ и исправить распространённые​ 18 дней. Им​ ячейку возвращается то​ один аргумент сообщает​

2. ВПР возвращает первое найденное значение

​Среди множества различных выражений,​​ поставь «пусто». А​​Чтобы вбить в формулу​Таблица для анализа успеваемости.​ эффективный инструмент, который​ пустые строковые значения​ аргумента Вы вставляете​ потому что синтаксис​(#ИМЯ?) – появится,​ВПР​#Н/Д​ числом, чтобы сохранить​Если Вы используете формулу​ ошибки и преодолеть​ в любом случае​​ значение, которое указывает​​ значение​​ которые применяются при​​ если ошибки нет​​ диапазон, просто выделяем​​ Ученик получил 5​

3. В таблицу был добавлен или удалён столбец

​ часто применяется в​​ («»).​​ значение, которое нужно​ВПР​ если Вы случайно​для поиска в​в формуле с​ стоящий в начале​​ с условием поиска​​ ограничения​ положена только обычная​ пользователь. Синтаксис данной​ЛОЖЬ​ работе с Microsoft​ (ячейки заполнены), то​ его первую ячейку​ баллов – «отлично».​ практике. Рассмотрим подробно​ОГРАНИЧЕНИЯ:​

​ проверить на предмет​​требует указывать полностью​ напишите с ошибкой​​ другой книге:​​ВПР​​ ноль.​​ приближённого совпадения, т.е.​​ВПР​​ премия в размере​ функции, содержащей всего​, то и оператор​ Excel, следует выделить​ раздели ячейку А7​ и последнюю. «=​ 4 – «хорошо».​ на примерах.​нет​ ошибки, а для​ весь диапазон поиска​

4. Ссылки на ячейки исказились при копировании формулы

​ имя функции.​=VLOOKUP(lookup_value,'[workbook name]sheet name’!table_array, col_index_num,FALSE)​

​не избежать. Вместо​​Наиболее очевидные признаки числа​ аргумент​.​​ 700 рублей.​​ два аргумента, выглядит​И​​ логические функции. Их​​ на ячейку В7​​ 0» означает команду​​ 3 – «удовлетворительно».​Синтаксис оператора в Excel​ПРИМЕРЫ ИСПОЛЬЗОВАНИЯ:​ второго аргумента указываете,​​ и конкретный номер​​Решение очевидно – проверьте​

ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

​=ВПР(искомое_значение;'[имя_книги]имя_листа’!таблица;номер_столбца;ЛОЖЬ)​ВПР​ в текстовом формате​​range_lookup​​В нескольких предыдущих статьях​​Попробуем составить формулу. Итак,​​ следующем образом:​​в целом возвращает​​ применяют для указания​ и напиши результат.​ поиска точных (а​ Оператор ЕСЛИ проверяет​​ – строение функции,​​ФОРМУЛЫ МАССИВА:​​ что нужно возвратить,​​ столбца для извлечения​ правописание!​Настоящая формула может выглядеть​Вы можете использовать​​ показаны на рисунке​​(интервальный_просмотр) равен TRUE​ мы изучили различные​

ВПР: работа с функцией ЕСЛИОШИБКА

​ у нас существует​​=ЕСЛИОШИБКА(значение;значение_если_ошибка)​​ это же значение.​ выполнения различных условий​ Скопировали формулу по​

​ не приблизительных) значений.​
​ 2 условия: равенство​

​ необходимые для ее​Функция работает в​ если ошибка найдётся.​ данных. Естественно, и​Помимо достаточно сложного синтаксиса,​ так:​ формулу массива с​ ниже:​

​ (ИСТИНА) или не​ грани функции​ два условия, при​.​

​ Общий вид данной​
​ в формулах. При​

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

​ВПР​=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)​
​ комбинацией функций​Кроме этого, числа могут​

ВПР: работа с функцией ЕОШИБКА

​ указан, Ваша формула​​ВПР​​ исполнении которых положена​Урок:​ функции:​ этом, если сами​Если вы работаете в​​ как изменятся ячейки​​ 5 и 4.​​=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)​​В КАКОЙ ВЕРСИИ​

​ возвращает пустую ячейку,​ номер столбца меняются,​
​имеет больше ограничений,​=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)​

​ИНДЕКС​​ быть сохранены в​​ может сообщить об​​в Excel. Если​​ премия в 1000​

​функция ЕСЛИ в Excel​
​=И(лог_значение1;лог_значение2;…)​

​ условия могут быть​ Excel 2007 и​ при соблюдении формулы.​В этом примере мы​Разберем синтаксис функции:​​ EXCEL РАБОТАЕТ:​​ если искомое значение​ когда Вы удаляете​

​ чем любая другая​Эта формула будет искать​
​(INDEX),​
​ формате​

​ ошибке​

office-guru.ru

ЕСЛИОШИБКА() IFERROR()

​ Вы читали их​
​ рублей – это​
​Функция​
​. Функция может​ довольно разнообразными, то​ новее, то функция​
​ Лучше сделать заливку​ добавили третье условие,​Логическое_выражение – ЧТО оператор​Начиная с версии​ не найдено:​ столбец или вставляете​ функция Excel. Из-за​ значение ячейки​ПОИСКПОЗ​
​General​
​#Н/Д​ внимательно, то сейчас​ достижение пенсионного возраста​ЕОШИБКА​ включать в себя​ результат логических функций​
​ будет называться «ЕСЛИОШИБКА».​
​ цветом.​

​ подразумевающее наличие в​


​ проверяет (текстовые либо​
​ Excel 2007.​=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),»»)​

​ новый.​ этих ограничений, простые​
​A2​(MATCH) и​
​(Общий). В таком​в двух случаях:​

excelworld.ru

Внедрение функции ЕСЛИОШИБКА в формулу

​ должны быть экспертом​​ или принадлежность работника​
​проверяет, не содержит​ от 1 до​ может принимать всего​ Эта функция отличается​Выделяем вторую таблицу. Условное​ табеле успеваемости еще​
​ числовые данные ячейки).​Посмотреть другие функции​
​=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);»»)​

​Решение:​​ на первый взгляд​в столбце​
​СЖПРОБЕЛЫ​ случае есть только​
​Искомое значение меньше наименьшего​ в этой области.​
​ к женскому полу.​ ли определенная ячейка​
​ 255 аргументов.​ два значения: условие​
​ от функции «ЕОШИБКА»​ форматирование – создать​
​ и «двоек». Принцип​
​Значение_если_истина – ЧТО появится​
​ этой категории.​Если Вы хотите показать​И снова на​
​ формулы с​B​

​(TRIM):​​ один заметный признак​ значения в просматриваемом​ Однако не без​ При этом, к​
​ или диапазон ячеек​Функция​ выполнено (​

​ тем, что выполняет​​ правило – использовать​ «срабатывания» оператора ЕСЛИ​ в ячейке, когда​caustic​ собственное сообщение вместо​ помощь спешат функции​ВПР​на листе​=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))​

​ – числа выровнены​​ массиве.​
​ причины многие специалисты​ пенсионерам отнесем всех​

CyberForum.ru

Функция ЕСЛИ в Excel с примерами нескольких условий

​ ошибочные значения. Под​ИЛИ​ИСТИНА​ сразу две функции​ формулу. Применяем тот​ тот же.​ текст или число​: Добрый день.​ стандартного сообщения об​ИНДЕКС​

​часто приводят к​Sheet1​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))​ по левому краю​Столбец поиска не упорядочен​ по Excel считают​

Синтаксис функции ЕСЛИ с одним условием

​ тех, кто родился​ ошибочными значениями понимаются​, наоборот, возвращает значение​) и условие не​

​ (не надо писать​

​ же оператор (СЧЕТЕСЛИ).​

​Когда нужно проверить несколько​ отвечают заданному условию​подскажите пожалуйста, есть​

​ ошибке функции​(INDEX) и​ неожиданным результатам. Ниже​в рабочей книге​Так как это формула​

​ ячейки, в то​ по возрастанию.​ВПР​ ранее 1957 года.​ следующие:​

​ ИСТИНА даже в​

​ выполнено (​ две функции –​Скачать все примеры функции​ истинных условий, используется​ (правдивы).​ ли не хитрый​ВПР​ПОИСКПОЗ​ Вы найдёте решения​

​New Prices​ массива, не забудьте​ время как стандартно​Если Вы ищете точное​одной из наиболее​

​ В нашем случае​#Н/Д;​ том случае, если​ЛОЖЬ​ «ЕСЛИ» и «ЕОШИБКА»).​ ЕСЛИ в Excel​ функция И. Суть​Значение,если_ложь – ЧТО появится​

​ способ внедрения для​, впишите его в​(MATCH). В формуле​ для нескольких распространённых​и извлекать соответствующее​ нажать​ они выравниваются по​ совпадение, т.е. аргумент​ сложных функций. Она​

​ для первой строчки​

Функция ЕСЛИ в Excel с несколькими условиями

​#ЗНАЧ;​ только один из​). Давайте подробнее разберемся,​ Формулу писать проще.​Здесь вместо первой и​ такова: ЕСЛИ а​ в графе, когда​ определенной (выделенной) области,​ кавычках, например, так:​ИНДЕКС+ПОИСКПОЗ​ сценариев, когда​

​ значение из столбца​Ctrl+Shift+Enter​

​ правому краю.​

​range_lookup​ имеет кучу ограничений​ таблицы формула примет​#ЧИСЛО!;​ аргументов отвечает условиям,​ что представляют собой​ В ячейку D7​

​ последней ячейки диапазона​ = 1 И​

​ текст или число​ состоящей исключительно из​=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),»Ничего не найдено. Попробуйте​Вы раздельно задаёте​ВПР​D​вместо привычного​Решение:​(интервальный_просмотр) равен FALSE​

​ и особенностей, которые​ такой вид:​#ДЕЛ/0!;​ а все остальные​ логические функции в​ пишем такую формулу.​ мы вставили имя​

Расширение функционала с помощью операторов «И» и «ИЛИ»

​ а = 2​ НЕ отвечают заданному​ формул, фунции ЕСЛИОШИБКА?​ еще раз!»)​ столбцы для поиска​ошибается.​.​Enter​

​Если это одиночное​ (ЛОЖЬ) и точное​ становятся источником многих​=ЕСЛИ(ИЛИ(C4. Но, не забываем,​#ССЫЛКА!;​ ложные. Её шаблон​ Экселе.​ =ЕСЛИОШИБКА(A7/B7;»») Формула намного​ столбца, которое присвоили​ ТОГДА значение в​ условию (лживы).​т.е. чтобы они​

​=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);»Ничего не найдено. Попробуйте​ и для извлечения​Функция​

​Если любая часть пути​

​, чтобы правильно ввести​

Как сравнить данные в двух таблицах

​ значение, просто кликните​ значение не найдено,​ проблем и ошибок.​ что обязательным условием​#ИМЯ?;​ имеет следующий вид:​Скачать последнюю версию​ короче. Копируем формулу​ ему заранее. Можно​ ИНАЧЕ значение с.​Пример:​ приняли вид:​

​ еще раз!»)​ данных, и в​ВПР​ к таблице пропущена,​ формулу.​

​ по иконке ошибки​ формула также сообщит​В этой статье Вы​ получения повышенной премии​#ПУСТО!​=И(лог_значение1;лог_значение2;…)​ Excel​ по столбцу D,​

​ заполнять формулу любым​Функция ИЛИ проверяет условие​

​Оператор проверяет ячейку А1​Код ЕСЛИОШИБКА(*формула*);»»)​Так как функция​ результате можете удалять​не различает регистр​

​ Ваша функция​В большинстве случаев, Microsoft​ и выберите​ об ошибке​ найдёте простые объяснения​ является отработка 18​

​В зависимости от того​. Как и​Существует несколько операторов логических​ получилось так.​ из способов. Но​ 1 или условие​ и сравнивает ее​

​Serge 007​ЕСЛИОШИБКА​ или вставлять сколько​ и принимает символы​ВПР​

​ Excel сообщает об​Convert to Number​#Н/Д​ ошибок​ дней и более.​

​ ошибочный аргумент или​ предыдущая функция, оператор​

​ функций. Среди основных​Можно​ с именем проще.​ 2. Как только​ с 20. Это​: Штатными средствами -​появилась в Excel​ угодно столбцов, не​

exceltable.com

​ нижнего и ВЕРХНЕГО​

Это вторая глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня. Раз вы читаете эту главу, видимо, ваша ВПР возвращает ошибку #Н/Д. Этот симптом может быть вызван одной из нескольких проблем. Чтобы найти лекарство, посмотрите, что из следующего применимо к вашему случаю:

• Большинство функций ВПР работают, но есть несколько разрозненных #Н/Д.
• #Н/Д нет в верхней части ваших данных, но #Н/Д встречаются всё чаще, по мере того, как вы двигаться вниз по диапазону. Скорее всего, вы забыли вставить знаки доллара в формулу, чтобы зафиксировать ссылку на таблицу просмотра.
• Все ВПР возвращают #Н/Д.
• ВПР работают для текстовых ячеек, но не для числовых.

Разрозненные #Н/Д. Проблема: отдельные ВПР выдают ошибки #Н/Д (рис. 2.1).

Рис. 2.1. BG33-9 – это новый товар, которого нет в таблице подстановки

Скачать заметку в формате Word или pdf, примеры в формате Excel

Такие ситуации встречаются, когда искомое значение не найдено в первом столбце Таблицы. В нашем примере товар BG33-9 не найден в таблице постановки (в диапазоне F2:H29). Добавьте элемент BG33-9 в таблицу подстановки (рис. 2.2). Исправьте формулу ВПР, расширив диапазон таблицы =ВПР(A2;$F$2:$H$30;3;ЛОЖЬ).

Рис. 2.2. Новый товар добавлен в таблицу подстановки

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

• Вставьте новые ячейки в любом месте в середине таблицы подстановки. Например, выделите ячейки F28:H28, кликните правой кнопкой мыши, выберите опцию Вставить… и в открывшемся окне укажите Ячейки со сдвигом вниз.
• В качестве таблицы подстановки выберите не диапазон ячеек ($F$2:$H$29), а столбцы целиком – F:H. Теперь, вы можете добавлять элементы внизу таблицы подстановки без переписывания формул. Excel достаточно сообразителен, чтобы использовать только непустые ячейки при расчетах.
• Выберите любую ячейку в таблице подстановки и нажмите сочетание клавиш Ctrl+Т (Т английское). Это превращает диапазон в таблицу. При вводе новых значений в пустые строки ниже таблицы, она автоматически расширится. Теперь ваша ВПР будет ссылаться не на диапазон, а на ТАБЛИЦУ (рис. 2.3).

Рис. 2.3. Превратите диапазон таблицы подстановки в инструмент Excel ТАБЛИЦА; это позволит автоматически расширять его при добавлении новых строк

Чтобы не пропустить ошибки #Н/Д, отсортируйте данные по столбцу Описание по убыванию (рис. 2.4). Все ошибки соберутся вверху. Для этого выделите столбец D, перейдите на вкладку ДАННЫЕ и в области Сортировка и фильтры нажмите кнопку сортировки. В открывшемся окне выберите Автоматически расширить выделенный диапазон и кликните Сортировать.

Рис. 2.4. Сортировка по убыванию разместит все записи с ошибкой #Н/Д вверху диапазона

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

Существует несколько способов решить эту проблему. Если вы используете Excel 2007 или более позднюю версию, воспользуйтесь функциями ЕСЛИОШИБКА() или ЕСНД(). Если у вас более ранняя версия Excel (или требуется совместимость с более ранней версией) подойдет связка функций ЕСЛИ() и ЕНД().

Если исходная формула =ВПР(A67;$F$2:$H$29;3;ЛОЖЬ), то замените ее на =ЕСЛИОШИБКА(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ);0), или на =ЕСНД(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ);0).

Функция ЕСЛИОШИБКА() появилась в Excel 2007, что стало значительным улучшение по сравнению с предыдущими методами. С ЕСЛИОШИБКА() Excel сначала вычисляет формулу в первом аргументе функции. В 99% случаев возвращается правильный ответ, и работа функции останавливается. В тех редких случаях, когда возвращается ошибка, Excel переходит к обработке второго аргумента функции ЕСЛИОШИБКА(). В нашем примере, просто возвращает ноль.

Если вы предпочитаете в случае ошибки получить пустую ячейку, используйте формулу =ЕСЛИОШИБКА(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ);""), но помните, что ячейка не будет по-настоящему пустой. Она будет содержать пустую текстовую строку, что, например, выдаст ошибку при попытке суммировать ее с другими числовыми значениями.

В более ранних версиях Excel 2007 используйте формулу: =ЕСЛИ(ЕНД(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ));0;ВПР(A67;$F$2:$H$29;3;ЛОЖЬ)).

Это ужасная формула, чтобы вычислить ее, нужно вдвое больше времени. Для каждой ячейки Excel сначала вычислит ВПР. Если результат – ошибка #Н/Д, то подставится ноль. Если результат – не ошибка, Excel повторно вычислит ВПР. С тысячами ячеек, содержащими ВПР задержка по времени может быть заметной.

Удалите начальные и конечные пробелы. Проблема: ни одна из моих формул ВПР работает. Я четко вижу, что есть совпадение в таблице подстановки, но Excel не находит эти значения.

Когда вы сталкиваетесь с такой проблемой, скорее всего, почти все ячейки содержат #Н/Д, а не некоторые, как в предыдущем разделе. Проблема может заключаться в том, что, либо элементы в столбце A, либо в столбце F имеют пробелы. Это может произойти, если вы импортировали данные в Excel из 1С, SAP и т.п. Чтобы диагностировать эту проблему, выделите ячейку А2 и нажмите клавишу F2, чтобы перевести ячейку в режим редактирования. Мигающий курсор вставки появится в конце ячейки (рис. 2.5). Если курсор вставки появляется сразу после последнего символа, всё в порядке. Если, как на рис. 2.5, значит присутствуют лишние пробелы. Аналогичную диагностику можно провести и для ячеек в столбце F.

Рис. 2.5. Ни одна из ВПР не работает, хотя совпадения наблюдаются

Воспользуйтесь функцией СЖПРОБЕЛЫ() для удаления начальных и конечных пробелов. Если есть пробелы между словами, функция изменит несколько пробелов подряд на один. Таким образом, формулу =ВПР(A3;$F$3:$G$30;2;ЛОЖЬ) замените на =ВПР(СЖПРОБЕЛЫ(A3);$F$3:$G$30;2;ЛОЖЬ).

Если лишние пробелы отображаются в таблице подстановки, вы можете воспользоваться формулой массива. Наберите с клавиатуры =ВПР(A3;СЖПРОБЕЛЫ($F$3:$G$30);2;ЛОЖЬ). Но не жмите Enter после редактирования. Вместо этого, нажмите одновременно Ctrl + Shift + Enter (подробнее см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel). Ваша формула будет выглядеть так {=ВПР(A2;СЖПРОБЕЛЫ($F$3:$G$30);2;ЛОЖЬ)}. Появятся фигурные скобки.

Подводный камень: формулы массива работают медленнее обычных формул. Это не проблема, если вы хотите произвести впечатление на коллег. Но в реальной жизни, лучше временно создать столбец, например, Н, в который ввести формулу =СЖПРОБЕЛЫ(F3). Затем скопировать его и вставить как значения в столбец F.

Числа и текст не распознаются как одинаковые. Проблема: часть значений ВПР работают корректно, а часть – возвращают ошибку (рис. 2.6).

Рис. 2.6. Часть значений ВПР возвращают ошибку

Вероятно, некоторые ячейки содержат числовые значения, а некоторые – числа, сохраненные как текст. На приведенном выше рисунке вы можете увидеть крошечный зеленый треугольник в левом верхнем углу ячеек В8, В11, В13. Если вы наведете курсор на ячейку, содержащую треугольник, слева появится символ с восклицательным знаком. Если раскрыть список, вы увидите, сообщение Число, сохраненное как текст (рис. 2.7).

Рис. 2.7. Сообщение: Число, сохраненное как текст

То, что числа хранятся в виде текста не обязательно плохо. Например, если это – модели продукта. Проблема в другом: формат данных в столбцах В и F – разный. Когда значение в B3 хранится в виде числа, а в F2 – в виде текста (см. рис. 2.6), ВПР не находит соответствия. Существуют функции, которые позволяют изменять формат данных. Однако, прежде чем их применить, нужно определить, в каком месте данные хранятся в виде чисел, а в каком – в виде текста. Для этого на свободном месте листа введите формулу =ЕЧИСЛО(В2) и =ЕЧИСЛО(F3). Значение ИСТИНА соответствует числу, ЛОЖЬ – тексту.

Если в столбце В хранится текст, а в столбце F – числа, используйте функцию ЗНАЧЕН, которая позволяет изменить текст на число. К сожалению, она выдаст ошибку, если наименование продукта буквенно-цифровое, например, 1120TS. Поэтому, нужно использовать конструкцию ЕСЛИОШИБКА вместе со ЗНАЧЕН (рис. 2.8).

Рис. 2.8. Преобразования текстовых значений в числовые

Если ситуация обратная, то можно использовать функцию ТЕСКТ. Буквенно-цифровые значения при этом не изменятся (рис. 2.9). Второй аргумент функции ТЕКСТ ("0") задает формат числа, на основании которого произойдет конвертация в текст (подробнее см. Пользовательский формат числа в Excel).

Рис. 2.9. Преобразования числовых значений в текстовые

Что делать, если проблема не укладывается ни в один из шаблонов? Т.е., когда, и столбец B, и столбец F, содержат комбинацию чисел и текста (рис. 2.10).

Рис. 2.10. Произвольное сочетание числовых и текстовых форматов в столбцах В и F

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

1. Выделите ячейку В2.
2. Нажмите Ctrl+Shift+Стрелка вниз. Выделится весь столбец В с данными.
3. Наберите Alt+Ы, не отпуская Alt, продолжайте набирать П, и наконец Г (или кликните на кнопке Готово). Это позволит преобразовать текстовые значения во всем столбце в числа.
4. Повторите пункты 1–3 для столбца F.

Примечание: сочетание клавиш Alt+Ы+П+Г – это быстрый способ выбрать все настройки по умолчанию в Мастере распределения текста по столбцам (вкладка ДАННЫЕ, область Работа с данными). Использование Мастера позволяет преобразовать все данные обратно в общий формат, что, как правило, решает проблему.

Альтернативные стратегии. Существуют и иные способы преобразования текста в числа. Самый простой способ собрать все текстовые ячейки рядом – отсортировать данные по столбцу B по убыванию. Ячейки с текстом попадут в начало списка. Если текстовых ячеек немного, их можно обработать вручную.

В Excel 2002 и более поздних версиях, вы можете преобразовать непрерывный диапазон ячеек из текста в числа. Чтобы сделать это, вы используйте знак ошибки (восклицательный знак).  Раскройте список и выберите Преобразовать в число (см. рис. 2.7). Этот метод работает только если верхняя ячейка вашего диапазона содержит число, сохраненное как текст.

Контроль дубликатов при использовании ВПР. Проблема: у меня есть первоначальная таблица, и данные о продажах за 19.06.14. Я использовал функцию ВПР, чтобы извлечь продажи из списка, и отразить их в первоначальной таблице. Затем я получил файл с продажами за 20.06.14. Я воспользовался функцией ПОИСКПОЗ и нашел одного нового клиента в списке продаж 20.06.14 – Sun Life Fincl.

Рис. 2.11. Действительно ли это новый клиент?

Этого клиента новым не назовешь. Кто-то в департаменте закупок создал его вместо существующего клиента по имени Sun Life Financial. Чтобы исправить этот дубль, надо заменить в таблице Sun Life Fincl.  на Sun Life Financial. Это устранит ошибку #Н/Д. Однако, после этого исправления у вас появилось две строки, которые соответствуют Sun Life Financial.

ВПР не способна обрабатывать ситуации, подобные описанной. При наличии двух строк в таблице подстановки ВПР вернет продажи из первой попавшейся. Вы получите $3541, но не $2815.

Рис. 2.12. ВПР возвращает первое найденное значение

Если у вас нет полной уверенности, что клиенты в таблице подстановки уникальны, вы не должны использовать ВПР. Вместо этого используйте функцию СУММЕСЛИ. На рис. 2.13 использована следующая формула: =СУММЕСЛИ($A$2:$A$10;E2;$C$2:$C$10). Эта формула предписывает выполнять сканирование по столбцу Клиент (диапазон А2:А10), чтобы найти клиента, соответствующего значению в ячейке Е2. Каждый раз, когда в Excel находит соответствие Выручка из диапазона С2:С10 добавляется к значению в ячейке F2.

Рис. 2.13. Используйте СУММЕСЛИ вместо ВПР для таблиц подстановки, содержащих дубли

Параметр Искомое_значение должен содержать не более 255 символов. Проблема: ни одна из моих ВПР не работает. Я получаю ошибку #ЗНАЧ!

Чтобы выяснить, сколько знаков в ячейке A2, перейдите в любую пустую ячейку и введите =ДЛСТР(А2). ВПР не будет работать, если символов >255. В определенных обстоятельствах вы могли бы усечь содержимое ячейки с помощью формулы =ЛЕВСИМВ(A2;255). Но, иногда это не удастся, если ключи не уникальны при усечении до 255 символов.

Таблица подстановки имеет неверную ориентацию. Проблема: таблица подстановки расположена по горизонтали (рис. 2.14). Можно ли использовать ВПР?

Рис. 2.14. Таблица подстановки расположена по горизонтали

Стратегия: буква “В” в ВПР обозначает вертикальный просмотр. В Excel также есть функция ГПР для горизонтальных таблиц подстановки. Если вы хотите почудачить, то могли бы использовать формулу =ГПР(B3,$F$2:$Q$3,2,ЛОЖЬ).

Альтернативная стратегия: вы, скорее всего, поступите также, как и большинство других пользователей. Скопируете диапазон F2:В3, выделите ячейку F5, выполните специальную вставку, задав параметр Транспонировать. Это предаст таблице подстановки вертикальное положение. Затем вы введете обычную формулу с ВПР. Заметьте, что, начиная с версии 2010 в Excel для транспонирования используется специальный значок:

Рис. 2.15. Значок Транспонировать

Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (VLOOKUP) не хочет работать в Excel 2013, 2010, 2007 и 2003, а также, как выявить и исправить распространённые ошибки и преодолеть ограничения ВПР.

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

В этой статье Вы найдёте простые объяснения ошибок #N/A (#Н/Д), #NAME? (#ИМЯ?) и #VALUE! (#ЗНАЧ!), появляющихся при работе с функцией ВПР, а также приёмы и способы борьбы с ними. Мы начнём с наиболее частых случаев и наиболее очевидных причин, почему ВПР не работает, поэтому лучше изучать примеры в том порядке, в каком они приведены в статье.

• Исправляем ошибку #Н/Д
• Исправляем ошибку #ЗНАЧ! в формулах с ВПР
• Ошибка #ИМЯ? в ВПР
• ВПР не работает (проблемы, ограничения и решения)
• ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

Исправляем ошибку #Н/Д функции ВПР в Excel

В формулах с ВПР сообщение об ошибке #N/A (#Н/Д) – означает not available (нет данных) – появляется, когда Excel не может найти искомое значение. Это может произойти по нескольким причинам.

1. Искомое значение написано с опечаткой

Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.

2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР

Если Вы используете формулу с условием поиска приближённого совпадения, т.е. аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, Ваша формула может сообщить об ошибке #Н/Д в двух случаях:

• Искомое значение меньше наименьшего значения в просматриваемом массиве.
• Столбец поиска не упорядочен по возрастанию.

3. Ошибка #Н/Д при поиске точного совпадения с ВПР

Если Вы ищете точное совпадение, т.е. аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ) и точное значение не найдено, формула также сообщит об ошибке #Н/Д. Более подробно о том, как искать точное и приближенное совпадение с функцией ВПР.

4. Столбец поиска не является крайним левым

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

Решение: Если нет возможности изменить структуру данных так, чтобы столбец поиска был крайним левым, Вы можете использовать комбинацию функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), как более гибкую альтернативу для ВПР.

5. Числа форматированы как текст

Другой источник ошибки #Н/Д в формулах с ВПР – это числа в текстовом формате в основной таблице или в таблице поиска.

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

Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:

Кроме этого, числа могут быть сохранены в формате General (Общий). В таком случае есть только один заметный признак – числа выровнены по левому краю ячейки, в то время как стандартно они выравниваются по правому краю.

Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.

Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.

6. В начале или в конце стоит пробел

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

Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)

Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)

Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)

Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (TRIM):

=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))

Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.

Ошибка #ЗНАЧ! в формулах с ВПР

В большинстве случаев, Microsoft Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Что касается ВПР, то обычно выделяют две причины ошибки #ЗНАЧ!.

1. Искомое значение длиннее 255 символов

Будьте внимательны: функция ВПР не может искать значения, содержащие более 255 символов. Если искомое значение превышает этот предел, то Вы получите сообщение об ошибке #ЗНАЧ!.

Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:

=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))

2. Не указан полный путь к рабочей книге для поиска

Если Вы извлекаете данные из другой рабочей книги, то должны указать полный путь к этому файлу. Если говорить точнее, Вы должны указать имя рабочей книги (включая расширение) в квадратных скобках [ ], далее указать имя листа, а затем – восклицательный знак. Всю эту конструкцию нужно заключить в апострофы, на случай если имя книги или листа содержит пробелы.

Вот полная структура функции ВПР для поиска в другой книге:

=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)

Настоящая формула может выглядеть так:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)

Эта формула будет искать значение ячейки A2 в столбце B на листе Sheet1 в рабочей книге New Prices и извлекать соответствующее значение из столбца D.

Если любая часть пути к таблице пропущена, Ваша функция ВПР не будет работать и сообщит об ошибке #ЗНАЧ! (даже если рабочая книга с таблицей поиска в данный момент открыта).

Для получения дополнительной информации о функции ВПР, ссылающейся на другой файл Excel, обратитесь к уроку: Поиск в другой рабочей книге с помощью ВПР.

3. Аргумент Номер_столбца меньше 1

Трудно представить ситуацию, когда кто-то вводит значение меньше 1, чтобы обозначить столбец, из которого нужно извлечь значение. Хотя это возможно, если значение этого аргумента вычисляется другой функцией Excel, вложенной в ВПР.

Итак, если случилось, что аргумент col_index_num (номер_столбца) меньше 1, функция ВПР также сообщит об ошибке #ЗНАЧ!.

Если же аргумент col_index_num (номер_столбца) больше количества столбцов в заданном массиве, ВПР сообщит об ошибке #REF! (#ССЫЛ!).

Ошибка #ИМЯ? в ВПР

Простейший случай – ошибка #NAME? (#ИМЯ?) – появится, если Вы случайно напишите с ошибкой имя функции.

Решение очевидно – проверьте правописание!

ВПР не работает (ограничения, оговорки и решения)

Помимо достаточно сложного синтаксиса, ВПР имеет больше ограничений, чем любая другая функция Excel. Из-за этих ограничений, простые на первый взгляд формулы с ВПР часто приводят к неожиданным результатам. Ниже Вы найдёте решения для нескольких распространённых сценариев, когда ВПР ошибается.

1. ВПР не чувствительна к регистру

Функция ВПР не различает регистр и принимает символы нижнего и ВЕРХНЕГО регистра как одинаковые. Поэтому, если в таблице есть несколько элементов, которые различаются только регистром символов, функция ВПР возвратит первый попавшийся элемент, не взирая на регистр.

Решение: Используйте другую функцию Excel, которая может выполнить вертикальный поиск (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС и ПОИСКПОЗ) в сочетании с СОВПАД, которая различает регистр. Более подробно Вы можете узнать из урока — 4 способа сделать ВПР с учетом регистра в Excel.

2. ВПР возвращает первое найденное значение

Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).

3. В таблицу был добавлен или удалён столбец

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

Решение: И снова на помощь спешат функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). В формуле ИНДЕКС+ПОИСКПОЗ Вы раздельно задаёте столбцы для поиска и для извлечения данных, и в результате можете удалять или вставлять сколько угодно столбцов, не беспокоясь о том, что придётся обновлять все связанные формулы поиска.

4. Ссылки на ячейки исказились при копировании формулы

Этот заголовок исчерпывающе объясняет суть проблемы, правда?

Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.

ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

Если Вы не хотите пугать пользователей сообщениями об ошибках #Н/Д, #ЗНАЧ! или #ИМЯ?, можете показывать пустую ячейку или собственное сообщение. Вы можете сделать это, поместив ВПР в функцию ЕСЛИОШИБКА (IFERROR) в Excel 2013, 2010 и 2007 или использовать связку функций ЕСЛИ+ЕОШИБКА (IF+ISERROR) в более ранних версиях.

ВПР: работа с функцией ЕСЛИОШИБКА

Синтаксис функции ЕСЛИОШИБКА (IFERROR) прост и говорит сам за себя:

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

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

Например, вот такая формула возвращает пустую ячейку, если искомое значение не найдено:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")

Если Вы хотите показать собственное сообщение вместо стандартного сообщения об ошибке функции ВПР, впишите его в кавычках, например, так:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")

ВПР: работа с функцией ЕОШИБКА

Так как функция ЕСЛИОШИБКА появилась в Excel 2007, при работе в более ранних версиях Вам придётся использовать комбинацию ЕСЛИ (IF) и ЕОШИБКА (ISERROR) вот так:

=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)

Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:

=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))

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