Функции поиска данных в таблицах Excel 2007
| Автор: | Индык Игорь Викторович |
| e-mail: | exelentc@yandex.ru |
У меня огромный массив данных и мне часто приходится искать, например, по наименованию товара, что бы посмотреть определенные характеристики. С помощью обычного поиска неудобно, потому что таблица имеет много столбцов и часто приходится перепроверять. Можно ли это как-то автоматизировать?
Это можно сделать с помощью ВПР или ГПР. ВПР используется если искомое значение размещено в столбце. ГПР - если в строчке. А сам порядок и принцип работы функций одинаковый. Данные функции можно использовать, если данные уникальны (т.е. не повторяются).
Поскольку предполагается, что данных много, можно для упрощения процесса ввода искомых данных и во избежание ошибок, сделать поле со списком. Для этого, устанавливаем курсор в ячейку, которая будет полем со списком (можно это сделать на чистом листе, не содержащем никаких данных, который и будем в дальнейшем использовать для отображения результатов поиска). На вкладке Данные группы Работа с данными нажимаем на кнопку Проверка данных из выпадающего списка выбираем пункт Проверка данных…
В диалоговом окне Проверка на вкладке Параметры в поле Тип данных выбираем Список. В появившемся поле Источник указываем ссылку на диапазон ячеек, который содержит наименование товаров. Нажимаем кнопку ОК.
У вас должен получиться вот такой выпадающий список:
Теперь приступим непосредственно к созданию функции для поиска нужных значений. Таблицу, в которой будет происходить поиск, необходимо предварительно отсортировать по искомому значению по возрастанию. В нашем примере, по марке телефона.
Установите курсор мышки в строчке рядом с созданным выпадающим списком или в другом месте, где вы хотите, что бы отображались результаты поиска. С помощью вкладки Формулы или кнопки Вставить функцию на Панели формул выбираем функцию ВПР.
Искомое_значение - делаем ссылку на ячейку, в которой содержится созданный нами выпадающий список.
Таблица - выделяем таблицу с данными так, что бы искомый аргумент был в первом столбце, и нажимаем кнопку F4 или же проставляем в ручную значки $, что бы ссылка стала абсолютной. Таблицу выделяем без шапки, только данные.
Номер_столбца - указываем порядковый номер столбца, данные которого мы хотим видеть в результате поиска.
Интервальный_просмотр - ЛОЖЬ - поскольку нам нужно точное совпадение данных с заданным в поиске.
Нажимаем кнопку ОК.
Меняя Номер столбца в созданной функции ВПР, вы можете сделать выборку из исходной таблицы нескольких значений, которые будут отвечать вашим условиям поиска.
Можно ли осуществлять поиск аналогичный поиску с использованием функции ВПР, но без сортировки данных? ВПР выдает в таком случае ошибку 9.
Найти любое значение в строке, если известно значение, которое находится в одном столбце, можно с помощью двух функция ПОИСКПОЗ и ИНДЕКС.
В качестве примера возьмем таблицу, которая содержит наименование марок телефонов и их цену.
Для упрощения поиска нужных значений можно сделать поле со списком, который будет состоять из данных, которые мы задаем для поиска (например, наименование товара). Для этого, установив курсор в свободную ячейку, переходим на вкладку Данные группу Работа с данными и нажимаем на кнопку Проверка данных, из выпадающего списка выбираем пункт Проверка данных… В диалоговом окне Проверка вводимых значений на вкладке Параметры в поле Тип данных выбираем Список и указываем диапазон, который содержит данные для поиска.
Затем, устанавливаем курсор в свободную ячейку, с помощью вкладки Формулы или кнопки Вставить функцию на Панели формул выбираем функцию ПОИСКПОЗ.
Искомое_значение - указываем ячейку, которая содержит поле со списком, созданный нами в самом начале.
Просматриваемый_массив - выделяем диапазон ячеек, которые содержат искомые ячейки. В нашем примере, столбец с марками телефонов, но выделять нужно без шапки.
Тип_сопоставления - указываем "0" (ноль) - это позволит функции находить первый аргумент, который соответствует искомому значению.
Нажимаем кнопку ОК.
Функция ПОИСКПОЗ указывает в какой строчке содержится искомое значение. Став курсором на ячейку, которая содержит формулу, в строке формул вы увидите синтаксис функции, выделяем его до знака "=" и копируем (нажав Ctrl+C) и нажимаем Enter, чтобы выйти из ячейки.
Затем, устанавливаем курсор в свободную ячейку, в которой у нас будут отображаться результаты, и с помощью вкладки Формулы или кнопки Вставить функцию на Панели формул выбираем функцию ИНДЕКС. Данная функция содержит два вида аргумента, о чем вам сразу же и сообщат, нам нужен "массив; номер_строки; номер_столбца".
В диалоговом окне Аргументы функции указываем следующие значения:
Массив - выделяем весь массив данных (без шапки таблицы), которые содержит как искомые значения, так и данные, которые в последствии мы хотим что бы отображались. В нашем примере это столбцы, которые содержат марку телефона и цену.
Номер_сроки - здесь мы вставляем функцию ПОИСКПОЗ путем нажатия клавиш Ctrl+V.
Номер_стобца - здесь необходимо указать номер столбца в массиве, значение которого будет отображаться в результате поиска.
Нажимаем кнопку ОК.
