В этом руководстве по Excel вы узнаете, как работать с несколькими совпадениями (результатами) с помощью функции ВПР. Мы рассмотрим два разных метода. Первый фактически использует функцию ВПР (вместе с СЧЁТЕСЛИ). Второй использует ИНДЕКС / ПОИСКПОЗ для имитации ВПР.
ВПР с несколькими результатами
Для поиска нескольких совпадений с помощью функции ВПР вам необходимо создать вспомогательный столбец в таблице данных. Вспомогательный столбец использует функцию COUNTIF для создания уникального идентификатора для каждого экземпляра. Вспомогательный столбец должен быть крайним левым столбцом в наборе данных. Если корректировка набора данных нецелесообразна, вам следует изучить другой метод в следующем разделе.
Давайте посмотрим на этот метод на практике:
Шаг 1:
Обратите внимание на формулу в ячейке F6:= B5 & СЧЁТЕСЛИ (B5: B $ 9; B5)
В частности, указанный диапазон: B6: B $ 11. Обратите внимание на знак $. Знак $ «блокирует» ссылку на ячейку: B $ 11. Таким образом, когда вы копируете формулу, 11 B $ остаются заблокированными. B6, однако, не заблокирован, поэтому, когда вы копируете формулу вниз, B6 превращается в B7 и т. Д. Этот метод создает уникальный номер для каждого найденного экземпляра. Причина, по которой мы оставляем B6 разблокированным, заключается в том, что по мере учета экземпляра он удаляется из общего количества, создавая уникальный номер.
Также обратите внимание на &. & объединяет название продукта с номером экземпляра, чтобы создать одно поле, которое мы будем использовать при поиске.
Шаг 2:
Мы переместили новый вспомогательный столбец (project_adj) слева от набора данных в B14: C19. Теперь мы можем выполнить ВПР для нескольких результатов. Вместо поиска socks ищите socks1 и socks2. Теперь у вас может быть несколько строк результатов ВПР, представляющих несколько найденных совпадений.
Обратной стороной этого метода является то, что вы должны отредактировать исходный набор данных (или скопировать / вставить набор данных в другое место), чтобы выполнить ВПР с несколькими результатами. В качестве альтернативы вы можете использовать метод ИНДЕКС / ПОИСКПОЗ:
ИНДЕКС / МАТЧ для множественных поисков соответствий
Большинство пользователей Excel знают о возможностях функции ВПР, но многие не знают о мощи функции ИНДЕКС и функции сопоставления, используемых в сочетании. Комбинация ИНДЕКС / ПОИСКПОЗ может использоваться для имитации ВПР с преимуществом большей гибкости.
Примечание. На изображении ниже представлены формулы. Нижнее изображение содержит результаты формулы.
Что происходит в приведенных выше формулах?
ПОИСКПОЗ - поиск позиции значения в диапазоне. В этом примере MATCH ищет «носки» в списке продуктов.
КОСВЕННЫЙ - Создает ссылку из строки текста. Мы используем это для настройки массива поиска по списку товаров. Как только совпадение найдено, диапазон корректируется, чтобы исключить это совпадение из поиска, позволяя найти следующее совпадение. В ячейке G5 мы устанавливаем начальный диапазон равным B5: B10 (установив для начального start_num в ячейке F5 значение 5). В G5 мы нашли совпадение в строке 1 диапазона, поэтому start_num F6 равен 5 + 1 = 6.
ИНДЕКС - возвращает значение из массива на основе позиции номера столбца / строки в этом массиве.
!! Я думаю, включите формулу массива, чтобы у них была 1 формула для связи …
