Почему не работает формула впр в excel © лишние пробелы

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

Значение, которое вам нужно найти, то есть искомое значение.

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

Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — «C» и т. д.

При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

= ВПР (искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением, приближенное соответствие (истина) или точное совпадение (ложь)).

Вот несколько примеров использования функции ВПР.

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

Здесь в столбцах A-F и H есть значения или формулы, которые используют только значения на листе, а остальные столбцы используют функцию ВПР и значения столбца A (клиентский код) и столбец B (юрист) для получения данных из других таблиц.

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

Чтобы открыть диалоговое окно Управление связями, нажмите кнопку данные > работа с данными > связей .

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

Поле, связывающее таблицы (в диалоговом окне отображаются в круглых скобках). Это lookup_value формулы функции ВПР.

Имя связанной таблицы подстановки. Это table_array формулы функции ВПР.

Поле (столбец) в связанной таблице подстановки с данными, которые вы хотите добавить в новый столбец. Эти сведения не отображаются в диалоговом окне Управление связями. чтобы узнать, какое поле вы хотите извлечь, вам потребуется ознакомиться со связанной таблицей подстановки. Вы хотите отметить номер столбца (A = 1) – это col_index_num в формуле.

Чтобы добавить поле в новую таблицу, введите формулу ВПР в первом пустом столбце с помощью сведений, собранных в действии 3.

В нашем примере столбец G использует юрист ( lookup_value) для получения данных о тарифных курсах из четвертого столбца (col_index_num = 4) из таблицы судебные тблатторнэйс ( table_array) с формулой = ВПР (, tbl_Attorneys, 4, ложь).

В формуле также могут использоваться ссылки на ячейки и ссылки на диапазоны. В нашем примере это было бы = ВПР (a2; «Юристы»! А:Д, 4, ложь).

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

Ошибка #ИМЯ?

Ошибка #ИМЯ появляется, когда имя, которое используется в формуле, было удалено или не было ранее определено.

Причины возникновения ошибки #ИМЯ?:

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

Ошибки в Excel – Использование имени в формуле

Устранение ошибки: определите имя. Как это сделать описано в этой статье.

Ошибка в написании имени функции:

Ошибки в Excel – Ошибка в написании функции ПОИСКПОЗ

Устранение ошибки: проверьте правильность написания функции.

В ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибки в Excel – Ошибка в написании диапазона ячеек

Устранение ошибки: исправьте формулу. В вышеприведенном примере это =СУММ(A1:A3).

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

Ошибки в Excel – Ошибка в объединении текста с числом

Устранение ошибки: заключите текст формулы в двойные кавычки.

Ошибки в Excel – Правильное объединение текста

Поиск в Excel по нескольким критериям

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

Пример 1: Поиск по 2-м разным критериям

Предположим, у нас есть список заказов и мы хотим найти Количество товара (Qty.), основываясь на двух критериях – Имя клиента (Customer) и Название продукта (Product). Дело усложняется тем, что каждый из покупателей заказывал несколько видов товаров, как это видно из таблицы ниже:

Обычная функция ВПР не будет работать по такому сценарию, поскольку она возвратит первое найденное значение, соответствующее заданному искомому значению. Например, если Вы хотите узнать количество товара Sweets, заказанное покупателем Jeremy Hill, запишите вот такую формулу:

– эта формула вернет результат 15, соответствующий товару Apples, так как это первое совпадающее значение.

Есть простой обходной путь – создать дополнительный столбец, в котором объединить все нужные критерии. В нашем примере это столбцы Имя клиента (Customer) и Название продукта (Product). Не забывайте, что объединенный столбец должен быть всегда крайним левым в диапазоне поиска, поскольку именно левый столбец функция ВПР просматривает при поиске значения.

Итак, Вы добавляете вспомогательный столбец в таблицу и копируете по всем его ячейкам формулу вида: =B2&C2. Если хочется, чтобы строка была более читаемой, можно разделить объединенные значения пробелом: =B2&» «&C2. После этого можно использовать следующую формулу:

или

Где ячейка B1 содержит объединенное значение аргумента lookup_value (искомое_значение), а 4 – аргумент col_index_num (номер_столбца), т.е. номер столбца, содержащего данные, которые необходимо извлечь.

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

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

Как и в предыдущем примере, Вам понадобится в таблице поиска (Lookup table) вспомогательный столбец с объединенными значениями. Этот столбец должен быть крайним левым в заданном для поиска диапазоне.

Итак, формула с ВПР может быть такой:

Здесь в столбцах B и C содержатся имена клиентов и названия продуктов соответственно, а ссылка Orders!$A&$2:$D$2 определяет таблицу для поиска на другом листе.

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

Чтобы формула работала, значения в крайнем левом столбце просматриваемой таблицы должны быть объединены точно так же, как и в критерии поиска. На рисунке выше мы объединили значения и поставили между ними пробел, точно так же необходимо сделать в первом аргументе функции (B2&» «&C2).

Запомните! Функция ВПР ограничена 255 символами, она не может искать значение, состоящее из более чем 255 символов. Имейте это ввиду и следите, чтобы длина искомого значения не превышала этот лимит.

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

Что такое функция ВПР в Эксель и как ей пользоваться

Сегодня мы рассмотрим:

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

Впр excel — расшифровка и пояснение

Объяснение для чайников начать нужно с расшифровки названия функции. ВПР — всего лишь сокращение от Вертикального Поиска. На английском выглядит название с аббревиатурой так: VLOOKUP – от Vertical LOOKUP. По названию уже можно понять, что функция считывает информацию в столбцах по вертикали, начиная слева направо, и является идентификатором этой информации. ВПР в эксель — это один из самых быстрых и простых способов найти в таблице интересующие данные. Например, артикул определенного товара в списке. Или его цену. Простыми словами ВПР — это самая обычная команда, которую можно задать с помощью составленной определенным образом формулы — и получить искомый ответ.

Формула состоит из нескольких элементов. Если вы знакомы с функциями эксель, вам известно, что любая команда начинается со значка «=». Далее будет следовать название самой функции — то есть, ВПР. После этого в круглых скобках задаются аргументы, х может быть от двух и до пяти-шести. Чем больше элементов поиска задается, тем более направленным он будет. Обычно в формуле указывается название элемента, который нужно найти в таблице, и его параметры — это минимум.

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

Что еще можно сделать при помощи ВПР в эксель:

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

ВПР можно использовать для работы в любой версии excel . Также эта формула подходит для некоторых версий гугл доков.

Инструкция с примером, как использовать ВПР excel

Вот самый простой пошаговый пример с картинками, как с использованием ВПР в excel найти нужную информацию:

Открыть таблицу эксель.
В любой свободной ячейке справа — на картинке-примере ячейка «Е», — указать формулу.
Формула **ВПР **начинается со знака «=».
Далее нужно задать функцию — это и есть ВПР (соответствует VLOOKUP).
Затем открываем круглые скобки, вводим аргументы и закрываем круглые скобки.

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

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

Важно! Функция ВПР в excel всегда ищет, начиная с левого столбца. То есть, сначала ВПР отсортирует элементы по названию. Потом в заданном диапазоне будет искать номер нужного столбца и данные в нем

Потом в заданном диапазоне будет искать номер нужного столбца и данные в нем.

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

Как сделать ВПР в Excel понятная инструкция

Чтобы понять, как пользоваться функцией ВПР в Excel, разберем ее синтаксис:

=ВПР(искомое значение; таблица; номер столбца; )

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

Таблица — ссылка на таблицу, в которой будет искать.

Номер столбца — порядковый номер столбца относительно самого левого столбца из выделенного диапазона поиска.Интервальный просмотр — указывается 0 (точный поиск) или 1 (приблизительный поиск)

Рассмотрим использование ВПР на примере:

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

В качестве критерия будет использоваться название месяца.

Добавим в первую таблицу столбец Температура и напишем следующую формулу:

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

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

Запомним, что в абсолютном большинстве случаем Интервальный просмотр ставим = 0.

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

Не забываем добавить абсолютные ссылки (значки доллара $) к диапазону, иначе диапазон “съедет” при копировании формулы.

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

Задача2. Поиск ближайшего числа

Предположим, что нужно найти товар, у которого цена равна или наиболее близка к искомой.

Чтобы использовать функцию ВПР() для решения этой задачи нужно выполнить несколько условий:

Ключевой столбец, по которому должен производиться поиск, должен быть самым левым в таблице;
Ключевой столбец должен быть обязательно отсортирован по возрастанию;
Значение параметра Интервальный_просмотр нужно задать ИСТИНА или вообще опустить.

Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА)

Для вывода найденной цены (она не обязательно будет совпадать с заданной) используйте формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)

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

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

Если нужно найти по настоящему ближайшее к искомому значению, то ВПР() тут не поможет. Такого рода задачи решены в разделе Ближайшее ЧИСЛО. Там же можно найти решение задачи о поиске ближайшего при несортированном ключевом столбце.

Примечание. Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием. Это можно сделать с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10).

Примечание: Если в ключевом столбце имеется значение совпадающее с искомым, то функция с параметром Интервальный_просмотр =ЛОЖЬ вернет первое найденное значение, равное искомому, а с параметром =ИСТИНА — последнее (см. картинку ниже).

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

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

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

Проблема: искомое значение не находится в первом столбце аргумента таблица

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

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

Ошибка #N/A, так как искомый аргумент «Ели» отображается во втором столбце (Продукты) аргумента table_array A2:C10. В этом случае Excel искать его в столбце A, а не в столбце B.

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

Как работает функция ВПР в Excel: несколько примеров для «чайников».

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

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

Использование точного и приблизительного поиска.

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

Обратите внимание, что четвертый параметр равен 1. Кое-что из результатов определено верно, но в большинстве случаев – ошибки

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

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

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

И не важно, что это цена абрикосов. Поиск цены на сливы происходил до тех пор, пока в D15 не встретилось слово, которое по алфавиту стоит ниже: яблоки

Остановились и взяли цену из предыдущей строки.

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

Вы спросите: «А зачем тогда этот неточный просмотр, если с ним столько проблем?»

Он отлично подходит для выбора значений из определенных интервалов.

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

Если у нас количество товара 11 единиц, то мы просматриваем столбец D до тех пор, пока не встретим число, большее 11. Это 20 и находится оно в 4-й строке. Останавливаемся здесь. Значит, наша скидка расположена в 3-й строке и равна 3%.

Использование нескольких условий.

Еще один простой пример для «чайников» – как использовать при выборе нужного значения несколько условий?

Предположим, у нас есть список имен и фамилий. Нам нужно найти нужного человека и вывести сумму его дохода.

В F2 используем следующую формулу:

Разберем пошагово, как в этом случае работает ВПР.

В начале мы формируем условие. Для этого при помощи оператора & «склеиваем» вместе имя и фамилию, а между ними вставляем пробел.

Не забываем при этом пробел заключить в кавычки, иначе Excel не воспримет его как текст.

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

Дальше все происходит по уже отработанной схеме.

Можно попробовать подстраховаться на тот случай, если между именем и фамилией введено несколько пробелов. Знак пробела в формуле заменяем на знак подстановки «*».

Приметно так – D2&”*”&E2

Функция ВПР и выпадающий список

Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.

Сначала сделаем раскрывающийся список:

Ставим курсор в ячейку Е8, где и будет этот список.
Заходим на вкладку «Данные». Меню «Проверка данных».
Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.
Когда нажмем ОК – сформируется выпадающий список.

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

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

Изменяем материал – меняется цена:

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

Вместо этого используйте индекс и сопоставление

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

Существует несколько преимуществ использования функции индекс и СОВПАДЕНИе вместо функции ВПР.

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

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

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

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

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

Синтаксис

Чтобы создать синтаксис для функции индекс и ПОИСКПОЗ, необходимо использовать аргумент array/Reference из функцией индекс и вложить в него синтаксис MATCH. Форма будет иметь следующий вид:

=ИНДЕКС(массив или ссылка; ПОИСКПОЗ(искомое_значение;массив;)

Используйте ИНДЕКСы и СОВПАДЕНИЯ для замены функции ВПР из приведенного выше примера. Синтаксис будет выглядеть следующим образом:

= Индекс (возвращающий значение из значения C2: C10, которое будет соответствовать (первое), которое находится где-то в массиве B2: B10, где возвращаемое значение является первым значением, соответствующим первое).

Формула ищет в C2:C10 первое значение, соответствующее значению Капуста (B7), и возвращает значение в ячейке C7 ( 100).

Причины и пути решения в Excel

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

Наиболее эффективный метод

В ситуации, когда не работает функция ВПР в Excel, проверьте наличие элемента на листе или задействуйте в формуле функцию обработки ошибок, к примеру, =ЕСЛИОШИБКА(ФОРМУЛА();0). В таком случае при появлении сбоев в расчете показывается ноль, а в ином случае — результат формулы. Можно дополнить запись “”, чтобы ничего не показывалось, или внести в скобки какую-либо запись.

Ошибка в типе параметров

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

Выберите ячейку (одну или несколько).
Жмите правой кнопкой мышки.
Выберите формат ячеек, а дальше Число.

Для принудительного внесения изменений нужно изменить формат для всего столбца. Для начала примените требуемое форматирование, а после выберите «Данные», «Текст по столбцам» и «Готово». После этого проверьте, появляется Н Д или нет.

Лишние пробелы

Распространенная причина, почему не работает формула ВПР в Excel, состоит в наличии пробелов. Для их удаления используйте функцию СЖПРОБЕЛЫ.

Ошибки метода поиска совпадения

Следующее объяснение, почему не срабатывает ВПР в Excel и возвращается Н Д — ошибки в применении метода совпадения. По умолчанию у опции ВПР имеется аргумент «интервальный просмотр», который дает команду на поиск точного совпадения даже при отсутствии сортировки данных в таблице.

Для поиска точного совпадения введите для аргумента «интервальный_просмотр» показатель ЛОЖЬ.

При этом учтите, что ИСТИНА, которое дает возможность поиска приблизительного параметра, может вернуть ошибку Н / Д. При использовании опции ПОИСКПОЗ попробуйте поменять параметр аргумента «тип_сопоставления» для указания порядке сортировки таблицы.

Не соответствие числа строк / столбцов заданному диапазону

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

В ячейке введена надпись Н / Д или Н Д

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

Вам может быть интересно:Не показывать 0 в Excel

Другие ошибки

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

В используемой формуле нет одного или более аргументов. Для исправления проблемы введите все необходимые документы и проверьте, работает опция или нет. Для контроля можно использовать Visual Basic.
Пользовательская опция недоступна. Для исправления проблемы убедитесь, что документ Excel с пользовательской функцией открыт, а опция работает корректно.
Макрос имеет функцию, которая возвращает Н Д. Если ВПР не работает по этой причине, для исправления ошибки убедитесь в правдивости аргументов и их нахождении в нужных местах.
Изменение защищенного файл с опцией ЯЧЕЙКА. Для исправления ситуации, когда ВПР в Excel не работает, жмите на комбинацию Ctrl+Alt+F9.
Столбец не является первым слева дли поискового диапазона. Для решения проблемы нужно ввести соответствующий параметр и проверить, появляется ли Н Д. Как вариант, можно использовать функции ИНДЕКС и ПОИСКПОК в качестве гибкой альтернативы для ВПР.
Неправильное форматирование числа. Бывают ситуации, когда цифры указаны в текстовом формате. Это часто происходит при импортировании сведений из внешней базы данных или при вводе апострофа перед числом для сохранения нуля в начале. Для решения проблемы жмите по ошибке и укажите Convert to Number. При появлении Н Д для многих чисел выделите их и жмите правой кнопкой мышки, а после выберите Format Cells и вкладку Число и Числовой.

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

Что такое ВПР и как ею пользоваться?

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

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

Допустим, нам необходимо заполнить “Таблицу заказов” данными из “Прайс листа”

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

Для начала приведите таблицу Excel в необходимый вам вид. Добавьте к заготовленной матрице данных два столбца с названиями «Цена» и «Стоимость». Выберите для ячеек, находящихся в диапазоне новообразовавшихся столбцов, денежный формат.
Теперь активируйте первую ячейку в блоке «Цена» и вызовите «Мастер функций». Сделать это можно, нажав на кнопку «fx», расположенную перед строкой формул, или зажав комбинацию клавиш «Shift+F3». В открывшемся диалоговом окне отыщите категорию «Ссылки и массивы». Здесь нас не интересует ничего кроме функции ВПР. Выберите её и нажмите «ОК». Кстати, следует сказать, что функция VLOOKUP может быть вызвана через вкладку «Формулы», в выпадающем списке которой также находится категория «Ссылки и массивы».
После активации ВПР перед вами откроется окно с перечнем аргументов выбранной вами функции. В поле «Искомое значение» вам потребуется внести диапазон данных, содержащийся в первом столбце таблицы с перечнем поступивших товаров и их количеством. То есть вам нужно сказать Excel, что именно ему следует найти во второй таблице и перенести в первую.
После того как первый аргумент обозначен, можно переходить ко второму. В нашем случае в роли второго аргумента выступает таблица с прайсом. Установите курсор мыши в поле аргумента и переместитесь в лист с перечнем цен. Вручную выделите диапазон с ячейками, находящимися в области столбцов с наименованиями товарной продукции и их ценой

Укажите Excel, какие именно значения необходимо сопоставить функции VLOOKUP.
Для того чтобы Excel не путался и ссылался на нужные вам данные, важно зафиксировать заданную ему ссылку. Чтобы сделать это, выделите в поле «Таблица» требуемые значения и нажмите клавишу F4. Если всё выполнено верно, на экране должен появиться знак $.
Теперь мы переходим к полю аргумента «Номер страницы» и задаём ему значения «2»

В этом блоке находятся все данные, которые требуется отправить в нашу рабочую таблицу, а потому важно присвоить «Интервальному просмотру» ложное значение (устанавливаем позицию «ЛОЖЬ»). Это необходимо для того, чтобы функция ВПР работала только с точными значениями и не округляла их.

Если всё выполнено верно, на экране должен появиться знак $.
Теперь мы переходим к полю аргумента «Номер страницы» и задаём ему значения «2»

Внесите формулу для первого столбца

Теперь, когда все необходимые действия выполнены, нам остаётся лишь подтвердить их нажатием кнопки «ОК». Как только в первой ячейке изменятся данные, нам нужно будет применить функцию ВПР ко всему Excel документу. Для этого достаточно размножить VLOOKUP по всему столбцу «Цена». Сделать это можно при помощи перетягивания правого нижнего уголка ячейки с изменённым значением до самого низа столбца. Если все получилось, и данные изменились так, как нам было необходимо, мы можем приступить к расчёту общей стоимости наших товаров. Для выполнения этого действия нам необходимо найти произведение двух столбцов — «Количества» и «Цены». Поскольку в Excel заложены все математические формулы, расчёт можно предоставить «Строке формул», воспользовавшись уже знакомым нам значком «fx».