Левый поиск с ВПР
В этом уроке мы обсудим, как использовать ВПР чтобы узнать информацию, указанную в левых столбцах Excel о данном элементе, который находится справа. Мы можем знать, что функция ВПР не может смотреть влево, поэтому для выполнения задачи нам придется воспользоваться помощью ВЫБЕРИТЕ функции.
Как выполнить поиск слева с помощью ВПР?
Чтобы заполнить информация о товаре 30001 который указан в крайнем правом столбце данных, как показано на скриншоте выше, вы можете использовать функцию ВЫБОР, чтобы изменить порядок таблицы - «переместите» крайний правый столбец со значением поиска в крайнее левое положение. Затем вы можете использовать обычную формулу ВПР для получения нужного значения.
Общий синтаксис
=VLOOKUP(lookup_value,CHOOSE({1,2,3,…,N},rangeN,range1,range2,range3,…),column_num,FALSE)
- искомое_значение: Значение VLOOKUP используется для определения положения соответствующей информации. Здесь относится к данному идентификатору продукта.
- Диапазон1, диапазон2, диапазон3, диапазонN: Столбцы ячеек, в которых искомое_значение и другая информация.
- номер_столбца: Число, указывающее, из какого столбца вы хотите получить данные. Обратите внимание, что, поскольку крайний правый столбец теперь перемещен в крайнее правое положение из-за функции ВЫБОР, чтобы представить 1-я колонна (цветной столбец) введите column_num как 2; представить 2-я колонна (столбец размера), вы должны ввести column_num как 3......
- range_lookup ЛОЖЬ: Заставляет ВПР находить только точное совпадение.
Чтобы заполнить информация о товаре 30001, скопируйте или введите приведенные ниже формулы в соответствующие ячейки и нажмите Enter чтобы получить результат:
Цвет (ячейка H6)
= ВПР (4 гонконгских доллара,ВЫБИРАТЬ(1,2,3,4 {},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),2,ЛОЖНЫЙ)
Размер (ячейка H7)
= ВПР (4 гонконгских доллара,ВЫБИРАТЬ(1,2,3,4 {},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),3,ЛОЖНЫЙ)
Цена (ячейка H8)
= ВПР (4 гонконгских доллара,ВЫБИРАТЬ(1,2,3,4 {},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),4,ЛОЖНЫЙ)
Примечание. Вместо ввода идентификатора продукта 30001 в формулах мы использовали ссылку на ячейку 4 гонконгских доллара (мы добавляем знаки доллара к ссылке, чтобы сделать ее абсолютной), поскольку мы хотим, чтобы формулы были динамическими. Таким образом, мы можем легко получить информацию о других продуктах, изменив только идентификатор продукта в ячейке. H4.
Пояснение формулы
Здесь мы используем формулу ниже в качестве примера:
=VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),2,FALSE)
- CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9): Функция CHOOSE переупорядочивает столбцы, перемещая столбец E в первую или, скажем так, крайнюю левую позицию в массиве следующим образом:
{30001,"White","Large",20;30002,"Black","Large",21;30003,"Blue","Medium",19;30004,"Red","Medium",18;30005,"Yellow","Small",20}.
В табличной форме массив будет таким:
- ВПР (4 гонконгских доллара,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),2, ЛОЖЬ) = ВПР (4 гонконгских доллара,{30001,"White","Large",20;30002,"Black","Large",21;30003,"Blue","Medium",19;30004,"Red","Medium",18;30005,"Yellow","Small",20},2,ЛОЖНЫЙ): Доступно 30001, значение поиска в ячейке H4, функция ВПР найдет строку с ее точным соответствием в новой таблице, созданной командой ВЫБОР, а затем вернет результат в 2ый столбец, который Белый.
Связанные функции
Функция ВПР в Excel выполняет поиск значения по первому столбцу таблицы и возвращает соответствующее значение из определенного столбца в той же строке.
Функция ВЫБОР возвращает значение из списка аргументов значений на основе заданного номера индекса. Например, ВЫБОР (3, «Яблоко», «Персик», «Оранжевый») возвращает оранжевый, номер индекса - 3, а оранжевый - третье значение после номера индекса в функции.
Связанные формулы
Поиск слева с помощью ИНДЕКС и ПОИСКПОЗ
Чтобы найти информацию, указанную в левых столбцах электронной таблицы Excel о данном элементе, который находится справа, вы можете использовать функции ИНДЕКС и ПОИСКПОЗ. Комбинация этих двух функций дает преимущество поиска значений в любом столбце по сравнению с другой мощной функцией поиска Excel, ВПР.
Искать значения из другого листа или книги
Если вы знаете, как использовать функцию ВПР для поиска значений на листе, значения vlookup из другого листа или книги не будут для вас проблемой.
Найти значение ближайшего соответствия по нескольким критериям
В некоторых случаях может потребоваться поиск ближайшего или приблизительного значения соответствия на основе нескольких критериев. С помощью комбинации функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ вы можете быстро сделать это в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
