Чем больше данных хранится в таблице Excel, тем сложнее быстро найти нужную информацию вручную. Поиск по десяткам или даже сотням строк отнимает время и повышает риск ошибок. Хорошая новость: в Excel есть встроенные функции, которые справляются с этой задачей автоматически. В статье рассмотрим четыре инструмента, которые чаще всего используют для поиска данных: ВПР, ГПР, ИНДЕКС и ПОИСКПОЗ.
Функция ВПР
ВПР, или вертикальный просмотр, — одна из самых востребованных функций Excel. Ее задача — найти заданное значение в первом столбце выбранного диапазона и вернуть информацию из другой ячейки той же строки.
Инструмент особенно удобен при работе со справочниками, списками сотрудников, каталогами товаров и другими большими таблицами, где нужные сведения приходится искать регулярно.
Как использовать ВПР:
- Подготовьте таблицу и убедитесь, что данные для поиска находятся в первом столбце диапазона.
- Выделите ячейку, в которую нужно вывести результат.
- Нажмите кнопку «Вставить функцию» рядом со строкой формул или перейдите на вкладку «Формулы».
- В списке функций выберите ВПР.
- Заполните параметры:
- Искомое значение — значение, которое необходимо найти;
- Таблица — диапазон данных;
- Номер столбца — столбец, из которого нужно вернуть результат;
- Интервальный просмотр — значение ЛОЖЬ для точного совпадения.
- Подтвердите ввод.
После этого Excel автоматически покажет найденные данные.
Предположим, в базе компании хранится информация о нескольких сотнях сотрудников. Чтобы не искать нужного человека вручную, достаточно ввести его табельный номер в отдельную ячейку. Функция ВПР моментально найдет соответствующую запись и отобразит фамилию, должность или другой необходимый параметр.
Функция ГПР
ГПР работает по тому же принципу, что и ВПР, но предназначена для горизонтально организованных таблиц. Если ВПР ищет информацию по столбцам, то ГПР выполняет поиск по строкам. Функция пригодится в ситуациях, когда заголовки или ключевые значения расположены в верхней строке, а необходимые данные находятся ниже.
Как использовать ГПР:
- Проверьте, что значения для поиска находятся в первой строке диапазона.
- Выберите ячейку для вывода результата.
- Откройте окно вставки функций.
- Найдите функцию ГПР.
- Укажите необходимые аргументы:
- искомое значение;
- диапазон таблицы;
- номер строки с возвращаемыми данными;
- параметр ЛОЖЬ для точного поиска.
- Нажмите ОК.
После этого Excel найдет нужное значение и выведет его в выбранную ячейку.
Например, если в первой строке таблицы указаны месяцы, а во второй — показатели продаж, функция поможет быстро определить выручку за конкретный месяц без ручного просмотра всей таблицы.
Функция ИНДЕКС
Функция ИНДЕКС возвращает содержимое ячейки по заданным координатам — номеру строки и номеру столбца. В отличие от ВПР и ГПР, она не выполняет поиск самостоятельно, а получает уже известное местоположение нужного значения.
Главное достоинство функции — универсальность. Она одинаково хорошо работает с таблицами любой структуры. К тому же не нужно, чтобы ключевые данные находились в определенном столбце. Благодаря этому ИНДЕКС часто используют в сложных отчетах и многоуровневых расчетах.
Как использовать ИНДЕКС:
- Выберите ячейку для вывода результата.
- Через мастер функций найдите функцию ИНДЕКС.
- Укажите вариант работы с массивом.
- Выберите диапазон данных.
- Введите номер строки.
- Укажите номер столбца.
- Подтвердите выполнение функции.
После этого Excel вернет значение, расположенное на пересечении указанных строки и столбца.
Представим прайс-лист с тысячами товарных позиций. Если известно, в какой строке находится нужный товар, функция ИНДЕКС поможет мгновенно получить его стоимость из соответствующего столбца.
Функция ПОИСКПОЗ
ПОИСКПОЗ не выводит найденное значение, а определяет его положение внутри диапазона. Результат — порядковый номер элемента в списке. Например, функция может определить, на каком месте находится конкретный сотрудник, товар или месяц в заданном диапазоне данных.
Как использовать ПОИСКПОЗ:
- Выберите ячейку для вывода результата.
- Откройте мастер функций и найдите ПОИСКПОЗ.
- Заполните аргументы:
- Искомое значение;
- Просматриваемый массив;
- Тип сопоставления — значение 0 для точного поиска.
- Подтвердите ввод.
После выполнения функции Excel покажет номер позиции, на которой находится искомый элемент.
Например, если в списке присутствуют товары «Ноутбук», «Монитор», «Клавиатура» и «Мышь», то поиск слова «Клавиатура» вернет число 3, поскольку этот товар расположен третьим по счету.
Как использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ
И ПОИСКПОЗ, и ИНДЕКС полезны сама по себе, однако вместе они образуют один из самых мощных инструментов поиска в Excel. Такая связка особенно эффективна в случаях, когда стандартных возможностей ВПР уже недостаточно.
Комбинация ИНДЕКС и ПОИСКПОЗ помогает находить данные независимо от расположения столбцов, корректно работает после изменения структуры таблицы и подходит для поиска сразу по нескольким направлениям.
Как использовать комбинацию функций:
- Подготовьте таблицу с данными.
- Выберите ячейку для результата.
- С помощью функции ПОИСКПОЗ определите номер строки, содержащей нужное значение.
- Используйте полученный результат в функции ИНДЕКС.
- Укажите диапазон данных и столбец, из которого необходимо вернуть информацию.
- Нажмите Enter.
В качестве примера рассмотрим поиск цены товара по артикулу А-105. Сначала ПОИСКПОЗ определит, на какой строке находится нужный артикул. Затем ИНДЕКС обратится к столбцу с ценами и вернет стоимость товара. Такой подход будет работать даже после перестановки столбцов внутри таблицы.
Как найти данные в Excel: коротко о главном
- ВПР предназначена для поиска данных в вертикально организованных таблицах. ГПР решает аналогичную задачу, но работает с горизонтальными диапазонами.
- ИНДЕКС возвращает значение по заданным координатам, а ПОИСКПОЗ определяет местоположение нужного элемента. Комбинация ИНДЕКС и ПОИСКПОЗ обеспечивает более гибкий поиск и не зависит от расположения столбцов в таблице.
- Использование функций поиска позволяет быстрее работать с большими массивами данных, автоматизировать рутинные операции и снизить вероятность ошибок.
