Почему при впр выдает 0

Исправление ошибки #ЗНАЧ! в функции ВПР

Если вы работаете с ВЛКП, то, возможно, вы уже не #VALUE! несколько раз. В этой теме перечислены наиболее распространенные проблемы, которые могут возникнуть при наступлении ВЛИО, и возможные решения.

Проблема: длина аргумента искомое_значение превышает 255 символов.

Решение: Сократите значение или используйте сочетание функций ИНДЕКС и ПОИСКПОЗ в качестве обходного пути.

Использование функций ИНДЕКС и ПОИСКПОЗ для поиска значений длиной более 255 символов

Это формула массива. Поэтому нажмите ввод (только при Microsoft 365 ) или CTRL+SHIFT+ВВОД.

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

Проблема: аргумент номер_столбца содержит текст или значение меньше 0.

Ошибка #ЗНАЧ! возникает, если значение col_index_argument меньше 1

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

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

Минимальное значение аргумента col_index_num — 1, где 1 — столбец поиска, 2 — первый столбец справа от столбца поиска и так далее. Таким образом, если поиск ведется в столбце A, то 1 — это ссылка, 2 — столбец B, 3 — столбец C и так далее.

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как пользоваться впр в экселе

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

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

Функция впр в excel для чайников

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

Если Вы знаете принцип работы функции, то можно пролистать до следующего блока.

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

У Вас уже есть шаблон ведомости, в котором заполнены табельные номера и ФИО.

Осталось внести суммы зарплат и собрать подписи.

Шаблон зарплатной ведомости

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

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

Заполнение таблицы вручную

Собственно, по такому же принципу и работает ВПР, делает просмотр сверху вниз (вертикально) до требуемого значения идентификатора и забирает значение напротив, как результат вычисления.

ВПР эксель как пользоваться

Функция ВПР находится в разделе функций ссылок и массивов.

Как найти ВПР

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

Функция впр в excel примеры: где писать функцию

Теперь нужно последовательно заполнить четыре аргумента функции ВПР.

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

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

Функция впр в excel примеры: Искомое значение.

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

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

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

Функция впр в excel примеры: Таблица

Номер столбца – номер столбца начиная с 1 для самого левого столбца Таблицы, содержащего возвращаемое значение.

В текущем примере у нас таблица начинается со столбца «Табельный номер» и заканчивается столбцом «Зарплата», поэтому номер столбца будет 2. Т.к. он второй в нашей таблице.

Функция впр в excel примеры: Номер столбца.

Интервальный просмотр – число 0 или 1.

0 (Ложь [False]) Точное совпадение–осуществляет поиск точного значения в первом столбце(если не находит –#Н/Д [#N/A]).

1 (Истина [True]) Приближенное соответствие–если нет совпадения, то выдает max_значение

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

А пока просто запомните, что здесь всегда ставим 0.

6 причин, почему функция ВПР не работает

Функция VLOOKUP (ВПР) – одна из самых популярных среди функций категории Ссылки и массивы в Excel. А также это одна из самых сложны функций Excel, где страшная ошибка #N/A (#Н/Д) может стать привычной картиной. В этой статье мы рассмотрим 6 наиболее частых причин, почему функция ВПР не работает.

Вам нужно точное совпадение
Зафиксируйте ссылки на таблицу
Вставлен столбец
Таблица стала больше
ВПР не может смотреть влево
Данные в таблице дублируются

Вам нужно точное совпадение

Последний аргумент функции ВПР, известный как range_lookup (интервальный_просмотр), спрашивает, какое совпадение Вы хотите получить – приблизительное или точное.

В большинстве случаев люди ищут конкретный продукт, заказ, сотрудника или клиента, и потому хотят точное совпадение. Если производится поиск уникального значения, то аргументом range_lookup (интервальный_просмотр) должно быть FALSE (ЛОЖЬ).

Этот аргумент не обязателен, но если его не указать, то будет использовано значение TRUE (ИСТИНА). В таком случае для правильной работы функции необходимо, чтобы данные были отсортированы в порядке возрастания.

На рисунке ниже показана функция ВПР с пропущенным аргументом range_lookup (интервальный_просмотр), которая возвращает ошибочный результат.

Функция ВПР не работает

Решение

Если Вы ищите уникальное значение, задайте последний аргумент равным FALSE (ЛОЖЬ). Функция ВПР в примере выше должна выглядеть так:

Зафиксируйте ссылки на таблицу

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

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

Функция ВПР не работает

Решение

Аргумент table_array (таблица) – это таблица, которую ВПР использует для поиска и извлечения информации. Чтобы корректно скопировать функцию ВПР, в аргументе table_array (таблица) должна быть абсолютная ссылка на диапазон ячеек.

Кликните по адресу ссылки внутри формулы и нажмите F4 на клавиатуре, чтобы превратить относительную ссылку в абсолютную. Формула должна быть записана так:

В этом примере ссылки в аргументах lookup_value (искомое_значение) и table_array (таблица) сделаны абсолютными. Иногда достаточно зафиксировать только аргумент table_array (таблица).

Вставлен столбец

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

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

Функция ВПР не работает

Столбец Quantity (Количество) был 3-м по счету, но после добавления нового столбца он стал 4-м. Однако функция ВПР автоматически не обновилась.

Решение 1

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

Решение 2

Другой вариант – вставить функцию MATCH (ПОИСКПОЗ) в аргумент col_index_num (номер_столбца) функции ВПР.

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

Формула, показанная ниже, может быть использована в этом примере, чтобы решить проблему, описанную выше.

Таблица стала больше

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

Функция ВПР не работает

Решение

Форматируйте диапазон ячеек как таблицу (Excel 2007+) или как именованный диапазон. Такие приёмы дадут гарантию, что ВПР всегда будет обрабатывать всю таблицу.

Чтобы форматировать диапазон как таблицу, выделите диапазон ячеек, который собираетесь использовать для аргумента table_array (таблица). На Ленте меню нажмите Home > Format as Table (Главная > Форматировать как таблицу) и выберите стиль из галереи. Откройте вкладку Table Tools > Design (Работа с таблицами > Конструктор) и в соответствующем поле измените имя таблицы.

В формуле на рисунке ниже использовано имя таблицы FruitList.

Функция ВПР не работает

ВПР не может смотреть влево

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

Решение

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

Пример, приведённый ниже, был использован для извлечения информации из колонки слева от той, по которой производится поиск:

Функция ВПР не работает

Данные в таблице дублируются

Функция ВПР может извлечь только одну запись. Она возвратит первую найденную запись, соответствующую введённому Вами условию поиска.

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

Решение 1

Нужны ли Вам повторяющиеся данные в списке? Если нет – удалите их. Это можно сделать быстро при помощи кнопки Removes Duplicates (Удалить дубликаты) на вкладке Data (Данные).

Решение 2

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

Таблица ниже – это список заказов. Допустим, Вы хотите найти все заказы определённого фрукта.

Функция ВПР не работает

Сводная таблица позволяет выбрать значение из столбца ID в фильтре, которое соответствует определенному фрукту, и получить список всех связанных заказов. В нашем примере выбрано значение ID равное 23 (Бананы).

Функция ВПР не работает

ВПР без забот

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