Applies ToExcel для Microsoft 365 Excel для Интернета Excel 2024 Excel 2021 Excel 2019 Excel 2016

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

В этой статье

Поиск значений по вертикали в списке с использованием точного соответствия

Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций INDEX и MATCH.

Примеры ВПР

=ВПР (B3,B2:E7,2,ЛОЖЬ)

ВПР ищет "Иванов" в первом столбце (столбец B) в таблице B2:E7 и возвращает "Григорий" из второго столбца (столбец C) таблицы.  Значение ЛОЖЬ возвращает точное совпадение.

=ВПР (102,A2:C7,2,ЛОЖЬ)

ВПР ищет точное совпадение (ЛОЖЬ) фамилии для 102 (искомое_значение) во втором столбце (столбец B) в диапазоне A2:C7 и возвращает "Григорий".

Дополнительные сведения см. в разделе Функция ВПР.

Примеры INDEX и MATCH

Функции ИНДЕКС и ПОИСКПОЗ можно использовать вместо функции ВПР

Что означает:

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение "Капуста" в массиве B2:B10))

Формула ищет первое значение в C2:C10, соответствующее Kale (в B7), и возвращает значение в C7 (100), которое является первым значением, которое соответствует Kale.

Дополнительные сведения см. в разделах Функция ИНДЕКС и ФУНКЦИЯ MATCH.

К началу страницы

Поиск значений по вертикали в списке с использованием приблизительного совпадения

Для этого используйте функцию ВПР.

Важно: Убедитесь, что значения в первой строке отсортированы по возрастанию.

Пример формулы ВПР для поиска приблизительного соответствия

В приведенном выше примере ВПР ищет имя учащегося, у которого есть 6 записок в диапазоне A2:B7. В таблице нет записи для 6 запаздывок, поэтому функция ВПР ищет следующее максимальное совпадение ниже 6 и находит значение 5, связанное с именем Дэйв, и таким образом возвращает Дэйв.

Дополнительные сведения см. в разделе Функция ВПР.

К началу страницы

Поиск значений по вертикали в списке неизвестного размера с использованием точного соответствия

Для выполнения этой задачи используйте функции OFFSET и MATCH.

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

Пример функций OFFSET и MATCH

C1 — это верхняя левая ячейка диапазона (также называемая начальной ячейкой).

MATCH("Oranges";C2:C7,0) ищет апельсины в диапазоне C2:C7. Не следует включать начальную ячейку в диапазон.

1 — это количество столбцов справа от начальной ячейки, из которой должно быть возвращено значение. В нашем примере возвращается значение из столбца D , Sales.

К началу страницы

Поиск значений по горизонтали в списке с использованием точного соответствия

Для выполнения этой задачи используйте функцию HLOOKUP. См. пример ниже:

Пример формулы HLOOKUP для поиска точного соответствия

HLOOKUP ищет столбец Продажи и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения см. в разделе Функция HLOOKUP.

К началу страницы

Поиск значений по горизонтали в списке с использованием приблизительного совпадения

Для выполнения этой задачи используйте функцию HLOOKUP.

Важно: Убедитесь, что значения в первой строке отсортированы по возрастанию.

Пример формулы HLOOKUP для поиска приблизительного соответствия

В приведенном выше примере HLOOKUP ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение меньше 1100 и возвращает 10543.

Дополнительные сведения см. в разделе Функция HLOOKUP.

К началу страницы

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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