Предположим, что вы хотите найти расширение телефона сотрудника с помощью номера значка или правильной ставки комиссии за сумму продаж. Вы ищете данные, чтобы быстро и эффективно найти определенные данные в списке и автоматически проверить, используются ли правильные данные. После поиска данных можно выполнить вычисления или отобразить результаты с возвращаемыми значениями. Существует несколько способов поиска значений в списке данных и отображения результатов.
В этой статье
-
Поиск значений по вертикали в списке с использованием точного соответствия
-
Поиск значений по вертикали в списке с использованием приблизительного совпадения
-
Поиск значений по вертикали в списке неизвестного размера с использованием точного соответствия
-
Поиск значений по горизонтали в списке с использованием точного соответствия
-
Поиск значений по горизонтали в списке с использованием приблизительного совпадения
-
Создание формулы подстановки с помощью мастера подстановки (только для Excel 2007)
Поиск значений по вертикали в списке с использованием точного соответствия
Для выполнения этой задачи можно использовать функцию ВПР или сочетание функций INDEX и MATCH.
Примеры ВПР
Дополнительные сведения см. в разделе Функция ВПР.
Примеры INDEX и MATCH
Что означает:
=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение "Капуста" в массиве B2:B10))
Формула ищет первое значение в C2:C10, соответствующее Kale (в B7), и возвращает значение в C7 (100), которое является первым значением, которое соответствует Kale.
Дополнительные сведения см. в разделах Функция ИНДЕКС и ФУНКЦИЯ MATCH.
Поиск значений по вертикали в списке с использованием приблизительного совпадения
Для этого используйте функцию ВПР.
Важно: Убедитесь, что значения в первой строке отсортированы по возрастанию.
В приведенном выше примере ВПР ищет имя учащегося, у которого есть 6 записок в диапазоне A2:B7. В таблице нет записи для 6 запаздывок, поэтому функция ВПР ищет следующее максимальное совпадение ниже 6 и находит значение 5, связанное с именем Дэйв, и таким образом возвращает Дэйв.
Дополнительные сведения см. в разделе Функция ВПР.
Поиск значений по вертикали в списке неизвестного размера с использованием точного соответствия
Для выполнения этой задачи используйте функции OFFSET и MATCH.
Примечание: Используйте этот подход, если данные отображаются во внешнем диапазоне данных, который обновляется каждый день. Вы знаете, что цена указана в столбце B, но не знаете, сколько строк данных вернет сервер, и первый столбец не отсортирован в алфавитном порядке.
C1 — это верхняя левая ячейка диапазона (также называемая начальной ячейкой).
MATCH("Oranges";C2:C7,0) ищет апельсины в диапазоне C2:C7. Не следует включать начальную ячейку в диапазон.
1 — это количество столбцов справа от начальной ячейки, из которой должно быть возвращено значение. В нашем примере возвращается значение из столбца D , Sales.
Поиск значений по горизонтали в списке с использованием точного соответствия
Для выполнения этой задачи используйте функцию HLOOKUP. См. пример ниже:
HLOOKUP ищет столбец Продажи и возвращает значение из строки 5 в указанном диапазоне.
Дополнительные сведения см. в разделе Функция HLOOKUP.
Поиск значений по горизонтали в списке с использованием приблизительного совпадения
Для выполнения этой задачи используйте функцию HLOOKUP.
Важно: Убедитесь, что значения в первой строке отсортированы по возрастанию.
В приведенном выше примере HLOOKUP ищет значение 11000 в строке 3 в указанном диапазоне. Он не находит 11000 и, следовательно, ищет следующее наибольшее значение меньше 1100 и возвращает 10543.
Дополнительные сведения см. в разделе Функция HLOOKUP.
Создание формулы подстановки с помощью мастера подстановки ( толькоExcel 2007 )
Примечание: Надстройка "Мастер подстановок" была прекращена в Excel 2010. Эта функция была заменена мастером функций и доступными функциями подстановки и ссылки (справочник).
В Excel 2007 мастер подстановки создает формулу подстановки на основе данных листа с метками строк и столбцов. Мастер подстановки помогает находить другие значения в строке, когда известно значение в одном столбце, и наоборот. В создаваемых формулах мастер подстановок использует индексы и MATCH.
-
Щелкните ячейку в диапазоне.
-
На вкладке Формулы в группе Решения щелкните Поиск.
-
Если команда Подстановка недоступна, необходимо загрузить мастер подстановки надстройка программу.
Загрузка надстройки мастера подстановок
-
Нажмите кнопку Microsoft Office
-
В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.
-
В диалоговом окне Доступные надстройки выберите поле проверка рядом с пунктом Мастер подстановки и нажмите кнопку ОК.
-
Следуйте инструкциям мастера.
