Поиск значений в списке данных

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

В этой статье

Поиск значений в списке по вертикали по точному совпадению

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

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

Поиск значений в списке по горизонтали по точному совпадению

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

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

Поиск значений в списке по вертикали по точному совпадению

Для решения этой задачи можно использовать функцию ВПР или сочетание функций ИНДЕКС и ПОИСКПОЗ.

Примеры функции ВПР

Пример 1 функции ВПР

Пример 2 функции ВПР

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

Примеры функций ИНДЕКС и ПОИСКПОЗ

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

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

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

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

Дополнительные сведения см. в разделах, посвященных функциям ИНДЕКС и ПОИСКПОЗ.

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

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

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

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

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

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

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

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

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

Для выполнения этой задачи используются функции СМЕЩ и ПОИСКПОЗ.

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

Пример функций СМЕЩ и ПОИСКПОЗ

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

Формула ПОИСКПОЗ("Апельсины";C2:C7;0) ищет значение "Апельсины" в диапазоне C2:C7. Начальную ячейку не следует включать в этот диапазон.

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

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

Поиск значений в списке по горизонтали по точному совпадению

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

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

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

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

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

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

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

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

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

В приведенном выше примере функция ГПР ищет значение 11 000 в строке 3 в указанном диапазоне. Значение 11 000 отсутствует, поэтому она ищет следующее максимальное значение, не превышающее 11 000, и возвращает 10 543.

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

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

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

Примечание : Поддержка надстройки "Мастер подстановок" в Excel 2010 прекращена. Эта надстройка была заменена мастером функций и функциями для работы со ссылками и массивами.

В Excel 2007 мастер подстановок создает формулу подстановки, основанную на данных листа, содержащих названия строк и столбцов. С помощью мастера подстановок можно найти остальные значения в строке, если известно значение в одном столбце, и наоборот. В формулах, которые создает мастер подстановок, используются функции ИНДЕКС и ПОИСКПОЗ.

  1. Щелкните ячейку в диапазоне.

  2. На вкладке Формулы в группе Решения выберите команду Подстановка.

  3. Если команда Подстановка недоступна, необходимо загрузить надстройка мастера подстановок.

    Загрузка надстройки мастера подстановок

  4. Нажмите кнопку Microsoft Office Изображение кнопки Office , а затем — кнопку Параметры Excel и выберите категорию Надстройки.

  5. В поле Управление выберите значениеНадстройки Excel и нажмите кнопку Перейти.

  6. В области Доступные надстройки установите флажок рядом с пунктом Мастер подстановок и нажмите кнопку ОК.

  7. Следуйте инструкциям мастера.

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

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×