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.
Использование функции ВПР (VLOOKUP) для подстановки значений
Кому лень или нет времени читать — смотрим видео. Подробности и нюансы — в тексте ниже.
Постановка задачи
Итак, имеем две таблицы — таблицу заказов и прайс-лист:
Задача — подставить цены из прайс-листа в таблицу заказов автоматически, ориентируясь на название товара с тем, чтобы потом можно было посчитать стоимость.
Решение
В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP) . Эта функция ищет заданное значение (в нашем примере это слово «Яблоки») в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:
Для простоты дальнейшего использования функции сразу сделайте одну вещь — дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме «шапки» (G3:H19), выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.
Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы — Вставка функции (Formulas — Insert Function) . В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:
Заполняем их по очереди:
- Искомое значение (Lookup Value) — то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае — слово «Яблоки» из ячейки B3.
- Таблица (Table Array) — таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя «Прайс» данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4 , чтобы закрепить ссылку знаками доллара , т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.
- Номер_столбца (Column index number) — порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
- Интервальный_просмотр (Range Lookup) — в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:
- Если введено значение 0 или ЛОЖЬ (FALSE) , то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, «Кокос»), то она выдаст ошибку #Н/Д (нет данных).
- Если введено значение 1 или ИСТИНА (TRUE) , то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с «кокосом» функция попытается найти товар с наименованием, которое максимально похоже на «кокос» и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле! Так что для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением является случай, когда мы ищем числа, а не текст — например, при расчете Ступенчатых скидок.
- Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
- Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
- Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
=ВПР(ТЕКСТ(B3);прайс;0)
Подробнее об этом можно почитать тут. - Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)
=VLOOKUP(TRIM(CLEAN(B3));прайс;0)
Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.
Ошибки #Н/Д и их подавление
Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:
Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR) . Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:
Если нужно извлечь не одно значение а сразу весь набор (если их встречается несколько разных), то придется шаманить с формулой массива. или использовать новую функцию ПРОСМОТРX (XLOOKUP) из Office 365.
Ссылки по теме
-
.
- Функции VLOOKUP2 и VLOOKUP3 из надстройки PLEX
Не за что — стараемся по мере сил и способностей Все мои видеоуроки можно найти (и подписаться на них) на канале planetaexcel на YouTube.
Добрый день! Подскажите, пожта, каким способом можно использовать функцию ВПР если происходит поиск значения, которое является частью текста в ячейке? Т.е. если ячейка состоит из одного значения — работает, а если значение «зашито» в ячейку — ВПР ее не распознает.
Спасибо Вам, Николай, как раз то что надо!
Сцепить два ваших критерия в отдельном столбце в один и делать ВПР по нему.
Либо написать свой вариант ВПР на Visual Basic
Подскажите, почему формула не работает? Составил Таблицу из 2-х столбцов, в первом Наименование, во втором параметр. Рядом 2 ячейки — выбор Наименования (через выпадающий список), во второй должно ставиться значение через формулу ВПР. Но появляются ошибки: При выборе Строчки «Ручка Опера» значение подставляется неверно, При выборе строчки «Ручка Бридж» значения не находит.
| Ручка Н | 54 |
| Ручка С | 36 |
| Ручка Сэко | 16 |
| Ручка Опера | 37 |
| Ручка Тауэр | 62 |
| Ручка Бридж | 2 |
P.S.: и как сюда пример готовый добавить?
Спасибо, экономил очень много времении )))))
Помогите, ВПР формулу прописал. Перепроверил. И мне в нужном месте вместо числового значения выдало ошибку #ССЫЛКА! Что делать и как ее обойти. И вообще почему она появилась? Для понимания проблемы. Всё сделал как в указанном примере. Но результат я уже написал.
УРРРЯЯЯЯЯЯ, сам разобрался. Проблема решилась когда я повторно пересмотрел урок и понял, что столбец указал не правильный для цены. Нужно указывать номер стоблца именно в ДИАПАЗОНЕ В КОТОРОМ БУДЕТ ВЫБИРАТЬСЯ ЦЕНА, а не столбец по порядку начиная с первого.
Функцией ВПР пользуюсь успешно, но вот проблема, если в ячейках есть знак * то функция подставляет только то что было до этого знака и находит то что было до этого знака. Как быть?
| Б 826Д*60*100 | 1 067,00 | Нет этого товара в Ч.Р. | 10 937 | Б 826Д*60*100 |
| Б 826Д*60*70 | 914,00 | 914,00 | 10 939 | Б 826Д*60*70 |
| Б 826Д*60*80 | 965,00 | 965,00 | 10 940 | Б 826Д*60*80 |
| Б 826Д*60*90 | 1 016,00 | 1 016,00 | 10 941 | Б 826Д*60*90 |
| Б 826Д*61*90,5 | 1 042,00 | 1 042,00 | 10 942 | Б 826Д*61*90,5 |
| Б 826Д*62*96 | 1 067,00 | 1 067,00 | 10 943 | Б 826Д*62*96 |
| Б 826Д*63*73 | 965,00 | 965,00 | 10 944 | Б 826Д*63*73 |
| 914,00 | Б 826Д** |
Николай, как быть в случае если цены в течении периода меняются. Предполагаем что в ТАБЛИЦЕ ЗАКАЗОВ есть столбец ДАТА. И в ПРАЙС-ЛИСТЕ есть столбцы ЦЕНА НА ЯНВАРЬ, ЦЕНА НА ФЕВРАЛ и т.д.
Очень полезная штука!
Николай, у меня вопрос.
Есть три таблицы:
1. Список товаров с артикулами и другими данными
2. Таблица соответствия артикула уникальному номеру (2 столбца).
3. Таблица соответствия уникального номера пути к фотографиям. В этой таблице два столбца, в одном уникальный номер, в другом путь к фото товара. Фотографий может быть как одна так и несколько. То есть айди может быть в пяти йчейках напротив которых ячейка с указанием пути.
Вопрос: Как в первую таблицу вытащить пути к нескольким фотографиям одного товара в одну ячейку из третей через вторую? При этом пути к фото разместить через символ | например.
Заранее огромное спасибо!
Николай, здравствуйте!
У меня вопрос. Может ли ВПР найти артикул с текстом?
Например, «525 393-090» ищем в «Кроссовки 525 393-090»
Заранее огромное спасибо!
ВПР не может, но можно по-другому:
Добрый день, Николай!
Пожалуйста, помогите понять работу предложенной вами здесь формулы с функциями ПРОСМОТР и ПОИСК.
Она создана как-то не в соответствии с описанием этих функций, но. как ни странно, работает!
Непонятки вот в чём.
По описанию функции ПРОСМОТР, аргумент №1 — строка с искомым значением, аргумент №2 — область-вектор (т.е. одномерная), в которой происходит поиск ячейки со значением, заданным аргументом №1, аргумент №3 — область-вектор, из которой выбирается значение-результат. То есть, если в области “аргумент №2” находится ячейка, значение которой равно “аргумент №1” — функция вычисляет порядковый номер этой ячейки в этой области и возвращает значение ячейки уже из области “аргумент №3” с тем же порядковым номером.
Вопрос 1. В этом свете, непонятно в вашем примере, какой смысл имеет на месте аргумента №1 значение 2^15 ?
Вопрос 2. В качестве аргумента №2 в вашей формуле стоит функция ПОИСК, результатом работы которой является вроде как «позиция первого вхождения знака или текстовой строки». А по описанию функции ПРОСМОТР, аргументом №2 должен быть дипазон ячеек (область). Как так?
Вопрос 3. По описанию фукции ПОИСК её аргумент №1 — строковый (текстовой), а в вашей формуле на этом месте — дипазон ячеек (область). Как так?
Вопрос 4. Ещё по функции ПОИСК. Когда я скопировал часть вашей формулы (аргумент №2 из ПРОСМОТР) в отдельную ячейку, чтобы посмотреть промежуточный результат, то есть, =ПОИСК(F$2:F$4;A2) — к моему недоумению результатом оказалось #ЗНАЧ! ! То есть, фукция отдельно — вроде неправильная, но внутри другой функции как-то работает?
Повторю, вся эта конструкция (формула) тем не менее, работает! Шаманство какое-то.
После долгих размышлений и экспериментов я догадался (в экселовской СПРАВКЕ об этом ни пол-слова! ), что конструкция ПОИСК(F$2:F$4;A2) возвращает виртуальный массив (!) из 4-х элементов (для удобства далее я его буду условно называть “ Просматриваемый_Вектор ”), с значениями, являющимися результатом поиска в значении ячейки A2 значений из соответствующих ячеек массива F$2:F$4 (далее буду условно называть “ Массив_Примет ”),
то есть,
в результирующий массив в элемент Просматриваемый_Вектор (1) помещается результат обычной функции ПОИСК(F$2;A2) (как бы, ПОИСК( Массив_Примет (1);A2)), в элемент Просматриваемый_Вектор (2) — результат ПОИСК(F$3;A2) (как бы, Массив_Примет (2);A2), и т. д.
То есть, в элементы Просматриваемый_Вектор (i) помещается: либо число (найденная в A2 позиция искомой подстроки Массив_Примет (i)), либо значение #ЗНАЧ! (если значение-подстрока Массив_Примет (i) в A2не найдена).
Что-ж, с моими вопросами 2, 3, 4 из предыдущего сообщения вроде разобрались. (За исключением того, что такое использование функции ПОИСК в экселовской Справке — отсутствует, т.е. — это недокументированная возможность?)
Остался вопрос 1 , по функции ПРОСМОТР:
Почему для выявления среди элементов массива Просматриваемый_Вектор элемента, содержащего число, их надо сравнивать именно c значением 2^15 ?
И ещё: В экселовской справке по функции ПРОСМОТР отмечено: «Значения в аргументе `просматриваемый_вектор` должны быть расположены в порядке возрастания. в противном случае функция ПРОСМОТР может вернуть неверный результат.».
В то же время, в вашем примере, в возвращаемом функцией ПОИСК виртуальном массиве Просматриваемый_Вектор положение элемента, содержащего число (т.е. соответствующего искомой подстроке) — относительно других элементов произвольное. Как это может сказаться на правильности работы ПРОСМОТР?
Я правильно понимаю, что Вы исключили в функции VLOOKUP 2 такую вещь как » приблизительный поиск (Интервальный просмотр=1) » ?
Т.е. она ищет только точно совпадение. Верно ?
У меня потребность именно в приблизительном поиске и лучше (крайне желательно) без сортировки таблицы.
Николай, добрый день, а что это может быть за ошибка если ВПР функция совсем не работает, то есть после введения всех 4 аргументов и нажатия ОК в нужной ячейке отражается
| =ВПР(A2;$J$2:$L$97864;3;0) |
а собственно значения (хотя бы #Н/Д ) не выдается. Много раз следовала Вашему уроку, но не получается..
Помогите пожалуйста какая то ошибка в формуле, интересует формула в клетке `M5`, там гдето ошибка, так как если правильно, то оно должно выдать результат аналогичный как в клетке `L5`, помогите найти ошибку пожалуйста..
Николай, добрый день.
Есть такая таблица:
| A | B | C |
| 1 | ИМЯ | |
| 2 | 500 |
при помощи функции ВПР (искомое значение «ИМЯ»;) мы можем присвоить значение из строки №1. Но, там пустые ячейки.
Как присвоить значение из строки, которая находится ниже? В данном примере «500»
Добрый день! А если в таблице позиции повторяются, а значения у них разные? ВПР цепляет тогда первое совпадение и получаются некорректные данные.
Например структура таблицы такая:
СКЛАД 1
яблоки 15 кг
груши 20 кг
апельсины 15 кг
СКЛАД 2
арбузы 30 кг
дыни 30 кг
яблоки 10 кг
В этом случае получается всегда «яблоки 15 кг»
Доброе время суток, Николай, а если ситуация вот такая:
Наименование
Сочное Яблоко красное 121212
Сочное Яблоко зеленое 333222
Сочное яблоко грени 4443344
Сочная Груша зелёная 33333
Сочная Груша желтая 55555
и т.д. до бесконечности, как это бывает в прайсах. Искать надо по слову Яблоко\Груша, как это можно реализовать?
Заранее, огромное Вам спасибо.
С уважением, Джон.
Добрый день. Как можно в одной таблице совместить возможности функций ВПР и VLOOKUP3
Т.е. есть исходные данные которые содержат в разных строках одинаковый артикул.
Если использовать ВПР, то она ищет только первое значение, если VLOOKUP3, то массив, но только для одного артикула. А мне нужно в одну таблицу «собрать» данные соответствующие и одиночным артикулам и если их несколько.
Т.е. по сути сводная таблица, но с простой, двумерной структурой.
Составил пример в экселе, не могу его прикрепить.
Файл1 Закупка
| № п/п | Наименование | Страна | Артикул | Дата покупки | Объем партии, кг |
Цена | Стоимость партии |
| 9 | Капуста | Россия | 0004/01 | 01.12.2015 | 5 | 12 | 60 |
| 8 | Ананас | Эквадор | 0001/02 | 02.12.2015 | 10 | 120 | 1200 |
| 14 | Киви | Тунис | 0005/01 | 03.12.2015 | 13 | 60 | 780 |
| 11 | Грейпфрут | Марокко | 0003/01 | 04.12.2015 | 14 | 45 | 630 |
| 17 | Нектарин | Тайланд | 0007/01 | 05.12.2015 | 14 | 40 | 560 |
| 5 | Киви | Тунис | 0005/01 | 01.01.2016 | 23 | 60 | 1380 |
| 10 | Манго | Тайланд | 0006/01 | 01.01.2016 | 10 | 80 | 800 |
| 13 | Киви | Тунис | 0005/01 | 01.01.2016 | 15 | 80 | 1200 |
| 16 | Абрикос | Армения | 0001/01 | 01.01.2016 | 26 | 40 | 1040 |
| 3 | Капуста | Россия | 0004/01 | 01.02.2016 | 35 | 12 | 420 |
| 6 | Капуста | Россия | 0004/01 | 02.02.2016 | 36 | 12 | 432 |
| 2 | Груши | Россия | 0003/02 | 03.02.2016 | 40 | 38 | 1520 |
| 15 | Персик | Армения | 0008/01 | 04.02.2016 | 42 | 45 | 1890 |
| 1 | Яблоки | Россия | 0009/01 | 01.03.2016 | 60 | 23 | 1380 |
| 4 | Мандарины | Марокко | 0006/02 | 01.03.2016 | 45 | 45 | 2025 |
| 7 | Киви | Тунис | 0005/01 | 01.03.2016 | 60 | 60 | 3600 |
| 12 | Банан | Алжир | 0002/01 | 01.03.2016 | 48 | 22 | 1056 |
Файл2 Продажи
| № п/п | Артикул | Дата продажи | Объем партии, кг |
Цена | Стоимость партии |
| 1 | 0009/01 | 10.03.2016 | 60 | 33 | 1980 |
| 2 | 0003/02 | 10.02.2016 | 40 | 48 | 1920 |
| 3 | 0004/01 | 03.02.2016 | 35 | 22 | 770 |
| 4 | 0006/02 | 10.03.2016 | 45 | 55 | 2475 |
| 5 | 0005/01 | 20.01.2016 | 23 | 70 | 1610 |
| 6 | 0004/01 | 03.02.2016 | 36 | 30 | 1080 |
| 7 | 0005/01 | 10.03.2016 | 40 | 70 | 2800 |
| 8 | 0001/02 | 10.01.2016 | 10 | 130 | 1300 |
| 9 | 0005/01 | 11.03.2016 | 15 | 80 | 1200 |
| 10 | 0004/01 | 15.01.2016 | 5 | 35 | 175 |
| 11 | 0005/01 | 12.03.2016 | 5 | 90 | 450 |
| 12 | 0006/01 | 05.01.2016 | 10 | 90 | 900 |
| 13 | 0003/01 | 16.01.2016 | 14 | 55 | 770 |
| 14 | 0002/01 | 10.03.2016 | 48 | 32 | 1536 |
| 15 | 0005/01 | 05.01.2016 | 15 | 90 | 1350 |
| 16 | 0005/01 | 15.01.2016 | 10 | 70 | 700 |
| 17 | 0005/01 | 15.01.2016 | 3 | 80 | 240 |
| 18 | 0008/01 | 15.03.2016 | 42 | 55 | 2310 |
| 19 | 0001/01 | 15.01.2016 | 20 | 50 | 1000 |
| 20 | 0007/01 | 17.01.2016 | 14 | 50 | 700 |
| 21 | 0001/01 | 15.01.2016 | 6 | 60 | 360 |
Что необходимо
| Сводная таблица по продажам | |||||
| Наименование | Страна | Артикул | Продано | Средняя цена | Сумма |
| Абрикос | Армения | 0001/01 | 20 | 50 | 1000 |
| Ананас | Эквадор | 0001/02 | 10 | 130 | 1300 |
| Банан | Алжир | 0002/01 | 48 | 32 | 1536 |
| Грейпфрут | Марокко | 0003/01 | 14 | 55 | 770 |
| Груши | Россия | 0003/02 | 40 | 48 | 1920 |
| Капуста | Россия | 0004/01 | 76 | 29 | 2204 |
| Киви | Тунис | 0005/01 | 111 | 78.57 | 8721.27 |
| Манго | Тайланд | 0006/01 | 10 | 90 | 900 |
| Мандарины | Марокко | 0006/02 | 45 | 55 | 2475 |
| Нектарин | Тайланд | 0007/01 | 14 | 50 | 700 |
| Персик | Армения | 0008/01 | 42 | 55 | 2310 |
| Яблоки | Россия | 0009/01 | 60 | 33 | 1980 |
Не могли бы помочь. При использовании ВПР вопросов не возникло. Но при синхронизации моей таблицы и таблицы поставщика, в некоторых товарах , определяется как #Н/Д.
Это понятно так как не было найдено арт. в таблице поставщика. Но как сделать, если в таблице поставщика нет точного арт. номера оставлять то что уже было в ячейки, а не ставить #Н/Д.
День добрый! Подскажите пожалуйста, как сделать ВПР в такой ситуации (я уже думала о других формулах, как поискпоз, просмотр, но что-то как-то не выходит). Во вкладке Исходник 10 столбцов : Фио, группа, город, ДАТА. 9-й столбец Показатель, 10-й — время. Данные идут вертикально по Датам. Необходимо перенести данные Во вкладку Показатель, где первые 3 столбца Фио, группа, город, а после идут 1,2,3 . , то есть даты, они что во вкладке Исходник и Показатель указаны как числа (общий формат), так как сам файл идет за определенный месяц. Как в формуле привязать эти числа, чтоб переносились данные во вкладку Показатель по определенному Фио и дате.
Вкладка Исходник
| ФИО | Группа | Город | Дата | Показатель | Время |
| Петров Иван | Смирнов | Саратов | 3 | 196 | 0:01:54 |
| Петров Иван | Смирнов | Саратов | 4 | 176 | 0:02:54 |
| Иванова Катя | Кузнецов | Омск | 5 | 171 | 0:03:10 |
Вкладка Показатель
| ФИО | Группа | Город | 1 | 2 | 3 | 4 | 5 | 6 |
| Петров Иван | Смирнов | Саратов | ||||||
| Смирнова Юлия | Попов | Самара | ||||||
| Мартынова Екатерина | Попов | Самара | ||||||
| Иванова Катя | Кузнецов | Омск |
Заранее спасибо.
Николай, добрый день!
Подскажите, пожалуйста, как именно можно при использовании функции ВПР подставить (рядом) для выбранного значения (названия, маркировки) соответствующую ему таблицу с данными?
Пример:
Имеется: «лист-1» с названиями моделей и их техническим описанием.
Необходимо: на «лист-2» указывать актуальные в данный момент модели (в определенных ячейках, позиции ячеек определены для возможности последующей печати), и чтобы рядом с ними автоматически выводились таблицы с их описаниями с «лист-1».
Далее лист с таблицами выбранных моделей отправляется на печать.
Подскажите как можно заставить ВПР работать со списком «Наименование»(из вашего примера), диапазон которого меняется, может быть короче или длиннее. Нужно составить универсальный шаблон, где будут прописаны все формулы, но расчёты будут основываться на выборке с помощью ВПР по списку «Наименование», но длинна списка может меняться бессистемно в широком диапазоне 10-10 000.
Функция ВПР() в EXCEL
Функция ВПР() является одной из наиболее используемых в EXCEL, поэтому рассмотрим ее подробно.
В этой статье выбран нестандартный подход: акцент сделан не на саму функцию, а на те задачи, которые можно решить с ее помощью.
Синтаксис функции
ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)
Искомое_значение — это значение, которое Вы пытаетесь найти в столбце с данными. Искомое_значение может быть числом или текстом, но чаще всего ищут именно число. Искомое значение должно находиться в первом (самом левом) столбце диапазона ячеек, указанного в таблице .
Таблица — ссылка на диапазон ячеек. В левом столбце таблицы ищется Искомое_значение , а из столбцов расположенных правее, выводится соответствующий результат (хотя, в принципе, можно вывести можно вывести значение из левого столбца (в этом случае это будет само искомое_значение )). Часто левый столбец называется ключевым . Если первый столбец не содержит искомое_значение , то функция возвращает значение ошибки #Н/Д.
Номер_столбца — номер столбца Таблицы , из которого нужно выводить результат. Самый левый столбец (ключевой) имеет номер 1 (по нему производится поиск).
Параметр интервальный_просмотр может принимать 2 значения: ИСТИНА (ищется значение ближайшее к критерию или совпадающее с ним) и ЛОЖЬ (ищется значение в точности совпадающее с критерием). Значение ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по возрастанию. Это способ используется в функции по умолчанию, если не указан другой.
Ниже в статье рассмотрены популярные задачи, которые можно решить с использованием функции ВПР() .
Задача1. Справочник товаров
Пусть дана исходная таблица (см. файл примера лист Справочник ).
Задача состоит в том, чтобы, выбрав нужный Артикул товара, вывести его Наименование и Цену .
Примечание . Это «классическая» задача для использования ВПР() (см. статью Справочник ).
Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или = ВПР($E9;$A$13:$C$19;2;ИСТИНА) или = ВПР($E9;$A$13:$C$19;2) (т.е. значение параметра Интервальный_просмотр можно задать ЛОЖЬ или ИСТИНА или вообще опустить). Значение параметра номер_столбца нужно задать =2, т.к. номер столбца Наименование равен 2 (Ключевой столбец всегда номер 1).
Для вывода Цены используйте аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра номер_столбца нужно задать =3).
Ключевой столбец в нашем случае содержит числа и должен гарантировано содержать искомое значение (условие задачи). Если первый столбец не содержит искомый артикул , то функция возвращает значение ошибки #Н/Д. Это может произойти, например, при опечатке при вводе артикула. Чтобы не ошибиться с вводом искомого артикула можно использовать Выпадающий список (см. ячейку Е9 ).
Понятно, что в нашей задаче ключевой столбец не должен содержать повторов (в этом смысл артикула, однозначно определяющего товар). В противном случае будет выведено самое верхнее значение.
При решении таких задач ключевой столбец лучше предварительно отсортировать (это также поможет сделать Выпадающий список нагляднее). Кроме того, в случае несортированного списка, ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) работать не будет.
В файле примера лист Справочник также рассмотрены альтернативные формулы (получим тот же результат) с использованием функций ИНДЕКС() , ПОИСКПОЗ() и ПРОСМОТР() . Если ключевой столбец (столбец с артикулами) не является самым левым в таблице, то функция ВПР() не применима. В этом случае нужно использовать альтернативные формулы. Связка функций ИНДЕКС() , ПОИСКПОЗ() образуют так называемый «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)
В файле примера лист Справочник показано, что формулы применимы и для ключевых столбцов содержащих текстовые значения, т.к. артикул часто бывает текстовым значением. Также задача решена для несортированного ключевого столбца.
Примечание . Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием . (см. статью Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке ).
Примечание . Никогда не используйте ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) если ключевой столбец не отсортирован по возрастанию, т.к. результат формулы непредсказуем (если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его).
Задача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) .
Примечание : Если в ключевом столбце имеется значение совпадающее с искомым, то функция с параметром Интервальный_просмотр =ЛОЖЬ вернет первое найденное значение, равное искомому, а с параметром =ИСТИНА — последнее (см. картинку ниже).
Если столбец, по которому производится поиск не самый левый, то ВПР() не поможет. В этом случае нужно использовать функции ПОИСКПОЗ() + ИНДЕКС() или ПРОСМОТР() .