Excel н д

Ошибки #ЗНАЧ и #Н/Д в функции ВПР() Excel и как сними бороться.

В данной статье расскажу о двух ошибках которые может выдать функция ВПР() :

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

Когда возникает ошибка #Н/Д и как от нее избавиться при использовании ВПР().

Сообщение об ошибке Н/Д можно расшифровать как аббревиатуру (НД) – нет данных, то есть функции ВПР() нечего отобразить, и она как бы сообщает: «нет данных для отображения».

Почему возникает ошибка Н/Д (НД)?

Ошибка может возникать потому, что в Вашем списке (диапазоне) для сравнения нет искомого функцией ВПР() значения.
Ошибка может возникать потому, что в Вашем списке (диапазоне) для сравнения значения ячеек имеют ошибки. Иногда ошибки нельзя увидеть «не вооружённым глазом», например, если в ячейке добавлен лишний пробел или едва заметная точка. ВПР() воспринимает значение ячейки без пробела и с пробелом как совершенно разные данные и выдает ошибку «Н/Д».
Ошибка может возникать потому, что в искомой ячейке уже стоит значение «Н/Д», то есть ВПР() подтягивает эту ошибку из другой ячейки (искомой).

Как исправить ошибки Н/Д?

Первый способ – применить обработку ошибок – функцию ЕСЛИОШИБКА(ВПР(*;*;*;0);”Здесь была ошибка”). Эта функция заменяет сообщение об ошибке на любое значение, которое Вы укажете.
Способ №2 – удалить все пробелы и, по возможности, знаки препинания из ячеек. Для этого нужно нажатием клавиш ctrl+H вызвать окно замены значений, потом в поле «Найти» ввести пробел или знак препинания, а в поле «Заменить на:» не вводить ничего и нажить кнопку «Заменить все».
Способ №3 – поставить в функции ВПР() допуск ошибки. Как нам извесчтно 4 –й аргумент функции это число ошибок которые может допускать в сравниваемой строке функция ВПР(). То есть, если поставить число «1», то допускается 1 ошибка при сравнении [ВПР(*;*;*;1)]. В таком случае строка без пробела и с одним пробелом будут считаться идентичными. Но в таком способе есть подвох — очень высока вероятность неверных результатов, например, слово «полка» и «палка» имеют отличие всего в один знак и будут восприняты функцией, как одно и то же.

Когда возникает ошибка #ЗНАЧ и как от нее избавиться при использовании ВПР().

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

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

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

Исправить ошибку #ЗНАЧ в таком случае можно уменьшив длины сравниваемых строк.

Еще ошибка #ЗНАЧ может возникнуть если Вы пропустили(не указали) один из аргументов в функции.

Ошибки Excel 2007

Я ввожу формулу, но вместо результата я вижу текст #Н/Д или #####. Что это значит?

Это означает что в исходных данных, самой формуле, форматировании или в другом случае допущена ошибка. Сообщение об ошибке содержит указание на то, где именно она допущена. Ниже мы рассмотрим сообщения об ошибках и наиболее частые их причины:

Ошибка #####

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

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

Ошибка #Н/Д

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

В функции не указаны один или несколько обязательных аргументов.
В функциях ВПР, ГПР, ПРОСМОТР или ПОИСКПОЗ указано неправильное значение аргумента искомое_значение.
Функции ВПР, ГПР или ПОИСКПОЗ применяются для поиска в несортированной таблице.
В исходной таблице вместо пропущенных данных было введено значение #Н/Д или НД().

Ошибка #ДЕЛ/0!

Ошибка #ДЕЛ/0! Означает, что в процессе вычисления происходит деление на ноль. Причины могут быть следующие:

В формуле явно задано деление на ноль (например, =10/0).
В формуле или функции в качестве делителя стоит ссылка на пустую ячейку или на ячейку, которая содержит ноль.

Ошибка #ПУСТО!

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

Примечание: В некоторых задачах пользователю необходимо выполнить действия только с теми ячейками, которые одновременно присутствуют в двух диапазонах. Для этого диапазоны ячеек в функции разделяют не точкой с запятой, а пробелом (который называют оператором пересечения).

Если в функции не должны задаваться пересекающиеся диапазоны (например, при суммировании), вероятно, в качестве разделителя диапазонов был использован пробел, а не точка с запятой. Или пробел был использован при указании диапазона, вместо двоеточия.
Если в функции должны задаваться пересекающиеся диапазоны, вероятно диапазоны действительно не имеют общих ячеек. Нужно проверить заданные диапазоны в аргументах функции.

Ошибка #ЧИСЛО!

Данная ошибка возникает, если в формуле или функции указаны некорректные числовые значения. Например:

В функции, которая использует числовой аргумент, в его качестве указан текст или ссылка на ячейку с нечисловым значением.
Функция, которая выполняет итерации (вычисление, которое повторяется до тех пор, пока не будет выполнено заданное условие, например ВСД или СТАВКА) не может вычислить результат (например, заданное условие не наступило).
Результатом вычисления стало число, слишком большое или слишком малое для использования в Excel. Напоминаем, что максимальное число в Excel не может превышать 10307 и быть меньше минус 10307.

Ошибка #ССЫЛКА!

Подобное сообщение говорит нам о том, что ссылка на ячейку недействительна. Чаще всего это может свидетельствовать о том, что ячейки, на которые ссылается формула, были удалены, или в эти ячейки были скопированы другие значения.

Ошибка #ЗНАЧ!

Такая ошибка возникает при использовании неверного типа аргумента или операнда. Например, пользователь может ошибиться в следующем:

В формулу вместо числа или логического значения (ИСТИНА или ЛОЖЬ) введен текст.
После ввода или редактирования формулы массива нажимается клавиша ВВОД вместо комбинации CTRL+SHIFT+ВВОД.
Ссылка, формула или функция указана как массив.
В качестве условия функции, который требует единичное значение, указан диапазон.

Я понял, что означает ошибка. Но моя формула очень большая/работает с большим объемом данных. Как мне найти место, где допущена ошибка?

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

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

У меня ошибка #ИМЯ? и я не могу найти её причину.

Проверьте правильность написания имени (названия) функции. Для этого можно вызвать Мастер функций (группа Библиотека функций вкладки Формулы), и выбрать нужную функцию из предлагаемого списка.
В формулу был введен текст, не заключенный в двойные кавычки. Допустим, результатом формулы должен был быть текст с числом — например, Итого 200 р. Если при введении текста Итого в формулу этот текст не заключен в двойные кавычки, Excel интерпретирует такие данные как имя, что вызывает ошибку.

Примечание: Имя — это слово, словосочетание или другой набор знаков, заданные пользователем и обозначающие ячейку, диапазон ячеек, формулу или константу.

В формуле или функции используется имя, которое не было определено. Проверьте, что данное имя существует. На вкладке Формулы в группе Именованные ячейки выберите Диспетчер имен и выясните, присутствует ли данное имя в списке. Если данное имя отсутствует, его можно добавить его путем выбора команды Создание имени.
Была допущена ошибка в написании имени. Для проверки правильности указания имен в строке формул нужно нажать клавишу F3, щелкнуть требуемое имя, а затем нажать ОК.
В формуле или функции используется ссылка на другой лист, не заключенный в ординарные кавычки. Если имя другого листа или книги содержит небуквенные знаки или пробел, то это имя необходимо заключить в апострофы ( ‘ ).

Читать еще: Почему в эксель 20 значное число заменяет последние цифры на 0

У меня огромная база данных клиентов — покупателей. Каждый день в базе данных добавляется столбец, в котором указывается сумма выручки от каждого покупателя. Если покупатель ничего не купил, в ячейке ставится #Н/Д. Как мне просуммировать столбец, если там есть такие данные?

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

Но если основную таблицу изменить невозможно (по разным причинам), эту проблему можно попробовать решить с помощью функции БДСУММ.

Допустим, исходная таблица имеет следующий вид:

Применив функцию БДСУММ, мы сможем выбрать для суммирования те ячейки, которые не содержат значения #Н/Д. Функция БДСУММ имеет следующий синтаксис:

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

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

Далее мы вводим следующую формулу:

C2:C18 — наша таблица,
E2:E3 — наше условие.

Обратите внимание, что диапазоны базы данных, и условий обязательно должны содержать заголовки столбцов! При этом заголовок условия должен совпадать с заголовком столбца, над которым производятся вычисления!

Наш результат указан под таблицей:

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

Этот способ также может использоваться для функций СЧЁТ, БСЧЁТА, ДМАКС, ДМИН, БДПРОИЗВЕД и им подобных.

В начало страницы

Исправление ошибки #Н/Д

Ошибка #Н/Д обычно означает, что формула не находит запрашиваемое значение.

Лучшее решение

Чаще всего появление ошибки #Н/Д обусловлено тем, что формула не может найти значение, на которое ссылается функция ВПР, ГПР, ПРОСМОТР или ПОИСКПОЗ. Например, искомого значения нет в исходных данных.

В данном случае в таблице подстановки нет элемента «Банан», поэтому функция ВПР возвращает ошибку #Н/Д.

Решение: Убедитесь в том, что искомое значение существует в источнике данных, или используйте в формуле обработчик ошибок, например ЕСЛИОШИБКА. Например, = ЕСЛИОШИБКА (Формула (); 0), на которой написано следующее:

=ЕСЛИ(при вычислении формулы получается ошибка, то показать 0, в противном случае показать результат формулы)

Вы можете указать «», чтобы не отображалось ничего, или подставить собственный текст: =ЕСЛИОШИБКА(ФОРМУЛА(),»Сообщение об ошибке»)

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

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

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

Решение: Убедитесь в том, что типы данных одинаковы. Чтобы проверить формат ячейки, выделите ячейку или диапазон ячеек, щелкните правой кнопкой мыши и выберите пункт Формат ячеек > число (или нажмите клавиши CTRL + 1), а затем при необходимости измените формат чисел.

Совет: Если вам нужно принудительно изменить формат для целого столбца, сначала примените нужный формат, а затем выберите Данные > Текст по столбцам > Готово.

Начальные и конечные пробелы можно удалить с помощью функции СЖПРОБЕЛЫ. В приведенном ниже примере в функции ВПР используется вложенная функция СЖПРОБЕЛЫ для удаления начальных пробелов из имен в ячейках A2:A7 и возврата названия отдела.

= ВПР (D2; TRIM (A2: B7); 2; ЛОЖЬ)

Примечание: 24 сентября 2018 г. (формулы динамического массива): если у вас установлена текущая версия Office 365, и вы пользуетесь каналом выпуска программы предварительной оценки с ранним доступом, можно ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

По умолчанию функции, которые ищут данные в таблицах, должны использовать сортировку по возрастанию. Но у функций ВПР и ГПР есть аргумент интервальный_просмотр, который сообщает функции, что нужно искать точное совпадение, даже если таблица не отсортирована. Чтобы найти точное совпадение, укажите для аргумента интервальный_просмотр значение ЛОЖЬ. Помните, что значение ИСТИНА, сообщающее функции о том, что нужно искать приблизительное совпадение, может привести к возвращению не только ошибки #Н/Д, но и ошибочных результатов, как видно в следующем примере.

В этом примере возвращается не только ошибка #Н/Д для элемента «Банан», но и неправильная цена для элемента «Черешня». К такому результату приводит аргумент ИСТИНА, который сообщает функции ВПР, что нужно искать не точное, а приблизительное совпадение. Здесь нет близкого совпадения для элемента «Банан», а «Черешня» предшествует элементу «Персик». В этом случае при использовании функции ВПР с аргументом ЛОЖЬ будет отображаться правильная цена для элемента «Черешня», но для элемента «Банан» все равно будет указана ошибка #Н/Д, потому что в списке подстановок его нет.

Если вы используете функцию ПОИСКПОЗ, попробуйте изменить значение аргумента тип_сопоставления, чтобы указать порядок сортировки таблицы. Чтобы найти точное совпадение, задайте для аргумента тип_сопоставления значение (ноль).

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

В данном примере ячейка E2 содержит ссылку на несовпадающие диапазоны:

= СУММ (ЕСЛИ (A2: A11 = D2; B2: B5))

Чтобы формула вычислялась правильно, необходимо изменить ее так, чтобы оба диапазона включали строки 2–11.

= СУММ (ЕСЛИ (A2: A11 = D2; B2: B11))

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

Чтобы исправить ошибку, проверьте синтаксис используемой функции и введите все обязательные аргументы, которые возвращают ошибку. Вероятно, для проверки функции вам потребуется использовать редактор Visual Basic. Открыть этот редактор можно на вкладке «Разработчик» или с помощью клавиш ALT+F11.

Читать еще: Перевод excel в word

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

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

Чтобы исправить ошибку, нажмите клавиши CTRL+ALT+F9 для пересчета листа.

Если вы не знаете точно, какие аргументы использовать, вам поможет мастер функций. Выделите ячейку с формулой, а затем перейдите на вкладку Формула на ленте и нажмите кнопку Вставить.

Excel автоматически запустит мастер.

Щелкните любой аргумент, и Excel покажет вам сведения о нем.

Значение #Н/Д может принести пользу. Значения #Н/Д часто используются в диаграммах с такими данными, как в приведенном ниже примере, поскольку эти значения не отображаются на диаграмме. В примерах ниже показано, как выглядит диаграмма со значениями 0 и #Н/Д.

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

Дополнительные сведения об ошибке #Н/Д в конкретных функциях см. в следующих статьях:

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Функция ЕНД как скрыть ошибку #Н/Д для ВПР и ПРОСМОТР в Excel

Функция ЕНД в Excel используется для проверки ячеек или выражений, передаваемых в качестве аргумента, и возвращает логическое значение ИСТИНА. Например, если ячейка содержит код ошибки #Н/Д или результатом вычисления выражения, переданного в качестве аргумента, является код ошибки #Н/Д. В противном случае результатом выполнения данной функции является логическое ЛОЖЬ.

Суммирование количества ошибок в ячейках Excel

Примеры использования функции ЕНД в Excel. Данная функция принадлежит к категории «Проверка свойств и значений» – функций Excel (нелогические функции для проверки условий). Она удобна при проведении сложных расчетов с ветвлением логики. Например, при отсутствии ошибки будет выполнено действие_1, в противном случае – действие_2.

Пример 1. В таблице содержатся данные о товарах и их количестве. Данные были получены из СУБД, если количество единиц товаров равно нулю, в таблице Excel данная информация отобразилась в виде кода ошибки #Н/Д. Определить число наименований товаров, которых нет в наличии.

Вид таблицы данных:

Для расчета используем следующую запись (формула массива CTRL+SHIFT+Enter):

Функция ЕНД принимает сразу диапазон ячеек B3:B13 в качестве аргумента, поскольку используется формула массива. Двойное отрицание «—» необходимо для явного преобразования логических значений к числовым данным (ИСТИНА – 1, ЛОЖЬ – 0). Функция СУММ суммирует элементы полученного массива из нулей и единиц. В результате получаем:

В результате мы получили число равно количеству ошибок #Н/Д в столбце B.

Как получить первое значение ячейки вместо ошибки Н/Д в Excel

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

Вид таблицы данных:

Для поиска числа 1 используем следующую запись:

Функция ЕНД анализирует результат выполнения функции ПРОСМОТР. Если в качестве первого аргумента ПРОСМОТР передано числовое значение, которое меньше значения первого элемента просматриваемого диапазона, будет сгенерирован код ошибки #Н/Д и будет выполнено выражение, переданное в качестве аргумента значение_если_истина функции ЕСЛИ. В противном случае (искомое число находится в диапазоне массива или превышает значение его последнего элемента), выполнится выражение, переданное в качестве аргумента значение_если_ложь .

Как видно, ошибка #Н/Д не выводится, а вместо нее первое значение ячейки просматриваемого столбца.

Описание синтаксиса и параметров функции ЕНД в Excel

Функция ЕНД имеет следующую синтаксическую запись:

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

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

при использовании функций для поиска данных (ПОИСКОП, ВПР и других), если в качестве аргумента «искомое_значение» было введено несуществующее;
при использовании формул массивов, если длина массива результатов превышает длину исходных массивов;
если при использовании функции не были указаны один либо нескольких аргументов, обязательных для заполнения.

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

Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (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), как более гибкую альтернативу для ВПР.

Читать еще: Поиск в excel совпадений

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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