Как сделать поиск по столбцу




  • Автор: Индык Игорь Викторович e-mail:

    У меня огромный массив данных и мне часто приходится искать, например, по наименованию товара, что бы посмотреть определенные характеристики. С помощью обычного поиска неудобно, потому что таблица имеет много столбцов и часто приходится перепроверять. Можно ли это как-то автоматизировать?


     

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


     
     

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


     
     

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


     
     
     

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


     
     

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


     

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


     
     

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


     

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


     

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


     

    Интервальный_просмотр - ЛОЖЬ - поскольку нам нужно точное совпадение данных с заданным в поиске.


     

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


     
     

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


     

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


     

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


     

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


     
     

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


     
     

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


     
     

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


     

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


     

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


     

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


     

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


     
     

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


     
     

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


     

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


     

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


     

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


     

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


     
     

    В начало страницы


    В начало страницы


    Поделись с друзьями



    Рекомендуем посмотреть ещё:


    Закрыть ... [X]

    Поиск значений с помощью функций ВПР, ИНДЕКС и - Чем смыть оттеночный бальзам в домашних условиях

    Как сделать поиск по столбцу Как сделать поиск по столбцу Как сделать поиск по столбцу Как сделать поиск по столбцу Как сделать поиск по столбцу Как сделать поиск по столбцу

    ШОКИРУЮЩИЕ НОВОСТИ