Функции поиска данных в таблицах Excel 2007

Это можно сделать с помощью ВПР или ГПР. ВПР используется если искомое значение размещено в столбце. ГПР - если в строчке. А сам порядок и принцип работы функций одинаковый. Данные функции можно использовать, если данные уникальны (т.е. не повторяются).

Поскольку предполагается, что данных много, можно для упрощения процесса ввода искомых данных и во избежание ошибок, сделать поле со списком. Для этого, устанавливаем курсор в ячейку, которая будет полем со списком (можно это сделать на чистом листе, не содержащем никаких данных, который и будем в дальнейшем использовать для отображения результатов поиска). На вкладке Данные группы Работа с данными нажимаем на кнопку Проверка данных из выпадающего списка выбираем пункт Проверка данных…

В диалоговом окне Проверка на вкладке Параметры в поле Тип данных выбираем Список. В появившемся поле Источник указываем ссылку на диапазон ячеек, который содержит наименование товаров. Нажимаем кнопку ОК.

У вас должен получиться вот такой выпадающий список:

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

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

Искомое_значение - делаем ссылку на ячейку, в которой содержится созданный нами выпадающий список. Таблица - выделяем таблицу с данными так, что бы искомый аргумент был в первом столбце, и нажимаем кнопку F4 или же проставляем в ручную значки $, что бы ссылка стала абсолютной. Таблицу выделяем без шапки, только данные. Номер_столбца - указываем порядковый номер столбца, данные которого мы хотим видеть в результате поиска. Интервальный_просмотр - ЛОЖЬ - поскольку нам нужно точное совпадение данных с заданным в поиске. Нажимаем кнопку ОК.

Меняя Номер столбца в созданной функции ВПР, вы можете сделать выборку из исходной таблицы нескольких значений, которые будут отвечать вашим условиям поиска.

Можно ли осуществлять поиск аналогичный поиску с использованием функции ВПР, но без сортировки данных? ВПР выдает в таком случае ошибку 9.

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

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

Для упрощения поиска нужных значений можно сделать поле со списком, который будет состоять из данных, которые мы задаем для поиска (например, наименование товара). Для этого, установив курсор в свободную ячейку, переходим на вкладку Данные группу Работа с данными и нажимаем на кнопку Проверка данных, из выпадающего списка выбираем пункт Проверка данных… В диалоговом окне Проверка вводимых значений на вкладке Параметры в поле Тип данных выбираем Список и указываем диапазон, который содержит данные для поиска.

Затем, устанавливаем курсор в свободную ячейку, с помощью вкладки Формулы или кнопки Вставить функцию на Панели формул выбираем функцию ПОИСКПОЗ.

Искомое_значение - указываем ячейку, которая содержит поле со списком, созданный нами в самом начале.

Просматриваемый_массив - выделяем диапазон ячеек, которые содержат искомые ячейки. В нашем примере, столбец с марками телефонов, но выделять нужно без шапки.

Тип_сопоставления - указываем "0" (ноль) - это позволит функции находить первый аргумент, который соответствует искомому значению.

Нажимаем кнопку ОК.

Функция ПОИСКПОЗ указывает в какой строчке содержится искомое значение. Став курсором на ячейку, которая содержит формулу, в строке формул вы увидите синтаксис функции, выделяем его до знака "=" и копируем (нажав Ctrl+C) и нажимаем Enter, чтобы выйти из ячейки.

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

В диалоговом окне Аргументы функции указываем следующие значения:

Массив - выделяем весь массив данных (без шапки таблицы), которые содержит как искомые значения, так и данные, которые в последствии мы хотим что бы отображались. В нашем примере это столбцы, которые содержат марку телефона и цену.

Номер_сроки - здесь мы вставляем функцию ПОИСКПОЗ путем нажатия клавиш Ctrl+V.

Номер_стобца - здесь необходимо указать номер столбца в массиве, значение которого будет отображаться в результате поиска.

Нажимаем кнопку ОК.