Получение n-го совпадения с помощью ВПР
Предположим, у вас есть список значений и соответствующих им данных. Чтобы получить информацию, связанную с n-м совпадением определённого значения, воспользуйтесь формулой на основе функции ВПР.
Как с помощью ВПР получить информацию, соответствующую n-му совпадению искомого значения?
Чтобы с помощью функции ВПР получить информацию о первом, втором и третьем совпадениях значения «Карандаш» в приведённой выше таблице, сначала добавьте слева от таблицы вспомогательный столбец с формулой СЧЁТЕСЛИ, как показано ниже. Этот вспомогательный столбец объединяет наименования товаров с количеством их появлений, что позволит вам легко находить соответствующие значения для n-го совпадения «Карандаша».
Formula in cell A5: =B5&,":"&,COUNTIF($B$5:B5,B5).
Общий синтаксис
=VLOOKUP(lookup_value&,":"&,n,lookup_array,col_num,FALSE)
- lookup_value: Значение, по которому ищется его n-е совпадение. В данном случае — «Карандаш».
- n: Указывает номер искомого совпадения. Чтобы найти первое совпадение значения, задайте n как 1; чтобы найти второе — задайте n как 2.
- lookup_array: Диапазон, из которого извлекается соответствующая информация для n-го совпадения. Здесь имеется в виду весь диапазон данных, включая вспомогательный столбец.
- col_num: Номер столбца, указывающий, из какого столбца диапазона lookup_array следует вернуть информацию, соответствующую n-му совпадению.
Чтобы получить информацию о первом совпадении значения «Карандаш», скопируйте или введите приведённую ниже формулу в ячейку G7 и нажмите клавишу Enter, чтобы получить результат:
=ВПР()$F$4&":"&E7,$A$5:$C$11,3,ЛОЖЬ)
√ Примечание. Знаки доллара ($) выше обозначают абсолютные ссылки: параметры lookup_value и lookup_array в формуле не изменятся при перемещении или копировании формулы в другие ячейки. Однако перед ссылкой на ячейку с параметром n знаки доллара не ставятся, поскольку вы хотите, чтобы она оставалась динамической. После ввода формулы перетащите маркер заполнения вниз, чтобы применить её к нижестоящим ячейкам.
Пояснение формулы
В качестве примера рассмотрим следующую формулу:
=VLOOKUP($F$4&,":"&,E8,$A$5:$C$11,3,FALSE)
- Параметр range_lookup FALSE указывает функции выполнять точное совпадение.
- Параметр col_num 3 указывает, что найденное значение будет возвращено из 3-го столбца диапазона $A$5:$C$11.
- Фрагмент формулы $F$4&":"&E8 указывает на поиск 2-го (значение в ячейке)E8) совпадения значения Карандаш(значение в ячейке)$F$4). В текстовом виде этот фрагмент выглядит так: Карандаш:2.
- Теперь функция ВПР извлекает соответствующую информацию о количестве из 3-го столбца диапазона $A$5:$C$11, соответствующего значению Карандаш:2, то есть 478.
Связанные функции
Функция ВПР в Excel ищет значение, сопоставляя его с первым столбцом таблицы, и возвращает соответствующее значение из указанного столбца той же строки.
Связанные формулы
Получение n-го совпадения с помощью ИНДЕКС
Чтобы найти n-е совпадение значения в диапазоне и получить соответствующие данные, используйте формулу на основе функций ИНДЕКС, СТРОКА, НАИМЕНЬШИЙ и ЕСЛИ.
Точное совпадение с помощью ВПР
Чтобы быстро найти в Excel информацию о конкретном продукте, фильме, человеке или чём-то ещё, воспользуйтесь функцией ВПР.
Получение информации, связанной с n наименьшими значениями
Чтобы получить данные, соответствующие наименьшему, второму наименьшему или n-му наименьшему значению в списке, таблице или строке Excel, используйте комбинацию функций ИНДЕКС, НАИМЕНЬШИЙ и выделение формул.
Частичное совпадение с помощью ВПР
Иногда Excel нужно извлекать данные на основе частичной информации. Для этого отлично подходит формула ВПР в сочетании с подстановочными знаками — звёздочкой (*) и вопросительным знаком (?).
Лучшие инструменты для повышения продуктивности в Office
Kutools для Excel — Помогает Вам выделяться из толпы
Kutools для Excel предлагает более 300 функций,гарантируя, что всё необходимое всегда под рукой…
Office Tab — Включает вкладочное чтение и редактирование в Microsoft Office (включая Excel)
- Переключайтесь между десятками открытых документов всего за секунду!
- Сокращает сотни кликов мышью каждый день — забудьте о «мышечной руке» раз и навсегда!
- Повышает продуктивность на 50 % при одновременном просмотре и редактировании нескольких документов.
- Добавляет эффективность вкладок в Office (включая Excel) — как в Chrome, Edge и Firefox.