Работа с таблицами excel поиск значений

В документах Microsoft Excel, которые состоят из большого количества полей, часто требуется найти определенные данные, наименование строки, и т.д. Очень неудобно, когда приходится просматривать огромное количество строк, чтобы найти нужное слово или выражение. Сэкономить время и нервы поможет встроенный поиск Microsoft Excel. Давайте разберемся, как он работает, и как им пользоваться.

Поисковая функция в Excel

Поисковая функция в программе Microsoft Excel предлагает возможность найти нужные текстовые или числовые значения через окно «Найти и заменить». Кроме того, в приложении имеется возможность расширенного поиска данных.

Способ 1: простой поиск

Простой поиск данных в программе Excel позволяет найти все ячейки, в которых содержится введенный в поисковое окно набор символов (буквы, цифры, слова, и т.д.) без учета регистра.

1. Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.



2. После того, как вы перешли по соответствующим пунктам на ленте, или нажали комбинацию «горячих клавиш», откроется окно «Найти и заменить» во вкладке «Найти». Она нам и нужна. В поле «Найти» вводим слово, символы, или выражения, по которым собираемся производить поиск. Жмем на кнопку «Найти далее», или на кнопку «Найти всё».



3. При нажатии на кнопку «Найти далее» мы перемещаемся к первой же ячейке, где содержатся введенные группы символов. Сама ячейка становится активной.

   Поиск и выдача результатов производится построчно. Сначала обрабатываются все ячейки первой строки. Если данные отвечающие условию найдены не были, программа начинает искать во второй строке, и так далее, пока не отыщет удовлетворительный результат.

   Поисковые символы не обязательно должны быть самостоятельными элементами. Так, если в качестве запроса будет задано выражение «прав», то в выдаче будут представлены все ячейки, которые содержат данный последовательный набор символов даже внутри слова. Например, релевантным запросу в этом случае будет считаться слово «Направо». Если вы зададите в поисковике цифру «1», то в ответ попадут ячейки, которые содержат, например, число «516».

   Для того, чтобы перейти к следующему результату, опять нажмите кнопку «Найти далее».

   

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

4. В случае, если при запуске поисковой процедуры вы нажмете на кнопку «Найти все», все результаты выдачи будут представлены в виде списка в нижней части поискового окна. В этом списке находятся информация о содержимом ячеек с данными, удовлетворяющими запросу поиска, указан их адрес расположения, а также лист и книга, к которым они относятся. Для того, чтобы перейти к любому из результатов выдачи, достаточно просто кликнуть по нему левой кнопкой мыши. После этого курсор перейдет на ту ячейку Excel, по записи которой пользователь сделал щелчок.

Способ 2: поиск по указанному интервалу ячеек

Если у вас довольно масштабная таблица, то в таком случае не всегда удобно производить поиск по всему листу, ведь в поисковой выдаче может оказаться огромное количество результатов, которые в конкретном случае не нужны. Существует способ ограничить поисковое пространство только определенным диапазоном ячеек.

1. Выделяем область ячеек, в которой хотим произвести поиск.



2. Набираем на клавиатуре комбинацию клавиш Ctrl+F, после чего запуститься знакомое нам уже окно «Найти и заменить». Дальнейшие действия точно такие же, что и при предыдущем способе. Единственное отличие будет состоять в том, что поиск выполняется только в указанном интервале ячеек.

Способ 3: Расширенный поиск

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

К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.

Но, как отсечь такие, и другие заведомо неприемлемые результаты выдачи поиска? Именно для этих целей существует расширенный поиск Excel.

1. После открытия окна «Найти и заменить» любым вышеописанным способом, жмем на кнопку «Параметры».



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

   По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, если мы поставим галочки около соответствующих пунктов, то в таком случае, при формировании результата будет учитываться введенный регистр, и точное совпадение. Если вы введете слово с маленькой буквы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буквы, как это было бы по умолчанию, уже не попадут. Кроме того, если включена функция «Ячейки целиком», то в выдачу будут добавляться только элементы, содержащие точное наименование. Например, если вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.

   

   По умолчанию, поиск производится только на активном листе Excel. Но, если параметр «Искать» вы переведете в позицию «В книге», то поиск будет производиться по всем листам открытого файла.

   

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

   

   В графе «Область поиска» определяется, среди каких конкретно элементов производится поиск. По умолчанию, это формулы, то есть те данные, которые при клике по ячейке отображаются в строке формул. Это может быть слово, число или ссылка на ячейку. При этом, программа, выполняя поиск, видит только ссылку, а не результат. Об этом эффекте велась речь выше. Для того, чтобы производить поиск именно по результатам, по тем данным, которые отображаются в ячейке, а не в строке формул, нужно переставить переключатель из позиции «Формулы» в позицию «Значения». Кроме того, существует возможность поиска по примечаниям. В этом случае, переключатель переставляем в позицию «Примечания».

   

   Ещё более точно поиск можно задать, нажав на кнопку «Формат».

   

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

   

   Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».

   

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

   

   После того, как формат поиска настроен, жмем на кнопку «OK».

   

   Бывают случаи, когда нужно произвести поиск не по конкретному словосочетанию, а найти ячейки, в которых находятся поисковые слова в любом порядке, даже, если их разделяют другие слова и символы. Тогда данные слова нужно выделить с обеих сторон знаком «*». Теперь в поисковой выдаче будут отображены все ячейки, в которых находятся данные слова в любом порядке.



3. Как только настройки поиска установлены, следует нажать на кнопку «Найти всё» или «Найти далее», чтобы перейти к поисковой выдаче.

Как видим, программа Excel представляет собой довольно простой, но вместе с тем очень функциональный набор инструментов поиска. Для того, чтобы произвести простейший писк, достаточно вызвать поисковое окно, ввести в него запрос, и нажать на кнопку. Но, в то же время, существует возможность настройки индивидуального поиска с большим количеством различных параметров и дополнительных настроек.

Поиск нужных данных в диапазоне

Как использовать функцию ВПР (VLOOKUP) для поиска и выборки нужных значений из списка мы недавно разбирали. Если вы еще с ней не знакомы — загляните сюда, не пожалейте пяти минут, чтобы сэкономить себе потом несколько часов.

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

Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.

Задача решается при помощи двух функций:

=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)

Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16. Последний аргумент функции 0 — означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.

Функция ИНДЕКС выбирает из диапазона A1:G13 значение, находящееся на пересечении заданной строки (номер строки с артикулом выдает функция ПОИСКПОЗ) и столбца (нам нужен регион, т.е. второй столбец). 

Ссылки по теме

• Использование функции ВПР (VLOOKUP) для поиска и подстановки значений.
• Улучшенная версия функции ВПР (VLOOKUP)
• Многоразовый ВПР

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

ВПР (искомое значение; диапазон поиска; номер столбца с входным значением; 0 (ЛОЖЬ) или 1 (ИСТИНА)).

ЛОЖЬ – точное значение, ИСТИНА – приблизительное значение.

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

В ячейке С12 начинаем писать функцию:

1. B12 – поскольку нам нужен Хепилор, выбираем ячейку с предварительно написанным названием искомого лекарства.
2. Далее выбираем диапазон данных B3:D10, где функция будет совершать поиск нужного нам значения. Крайний левый столбец диапазона должен содержать в себе искомый критерий, по которому производится поиск значения.
3. Следующий шаг – указать номер столбца в массиве B3:D10, из которого будет считана информация на одной строке с Хепилором. Столбцы нумеруются слева направо в самом диапазоне, в нашем примере первый столбец – В, но не А, поскольку А лежит вне области диапазона.

Поиск по столбце «Производитель» будет работать точно так же, нужно просто указать последовательность столбца, где находится нужная нам информация – заменяем цифру «3» в формуле (ячейка С27) на цифру «2»:

Есть определённая особенность, связанная со столбцами. Иногда в Excel-файле в таблицах некоторые ячейки объединяют. На картинке ниже в формуле на месте порядкового номера столбца у нас написана цифра «3», но результат – название производителя, а не цена, как в первом примере:

Произошел сдвиг нумерации столбцов как раз из-за наличия объединения ячеек в столбце «Лекарственное средство»: мы объединяли столбцы «H» и «I», зрительно столбец «Лекарственное средство» — это первый столбец, а «Производитель» — второй, НО формула нумерует их следующим образом:

• H – первый;
• I – второй;
• J – третий;
• K – четвертый.

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



Использование функции ВПР для работы с несколькими таблицами и другими функциями

В следующем примере рассмотрим, как ещё мы можем использовать функцию для поиска и получения информации по критериям и комбинирование функции с функцией ЕСЛИОШИБКА. Например, мы имеем два отчета – отчет о количестве товара и отчет о цене за единицу товара, которые нам необходимы для подсчета стоимости. Опять же, с небольшим количеством данных это вполне можно сделать вручную, но, когда мы имеем большой объем, справиться с этим скорее и эффективнее нам поможет функция ВПР. В ячейке D3 начинаем писать функцию:

1. B3 – критерий, по котором проводим поиск данных.
2. F3:G14 – диапазон, по котором наша функция будет осуществлять поиск совпадения критерия и данных по строке.
3. Цифра «2» — номер столбца с нужной нам информацией по критерию.
4. Цифра «0» (или можно использовать слово «ЛОЖЬ») — для точности результатов.

Таким образом, когда мы задаем формуле искомый критерий, она начинает поиск совпадений с верхней ячейки первого столбца (шаг 1 на картинке). Затем функция «читает» все критерии сверху вниз, пока не найдет точное совпадение (шаг 2). Когда ВПР дойдет до Хепилора, она отсчитает нужное количество столбцов вправо (шаг 3) и выдаст нам искомое значение для критерия – цену 86,90 (шаг 4):

Но сейчас у нас есть данные только по первому критерию. Для того чтобы заполнить третий столбец D первой таблицы до конца, нужно просто скопировать функцию до последнего критерия. Однако, на этом этапе для корректной работы диапазон, где совершается поиск, нужно закрепить, иначе массив данных «съедет» вниз и у нас ничего не получится. Для этого используем абсолютные ссылки для диапазона в ячейке D3 – выделяем курсором диапазон F3:G14 и нажимаем клавишу F4, после чего совершаем копирование формулы до конца таблицы:

В итоге мы получаем необходимый нам результат:

Однако, наш пример базировался на полном соответствии критериев с обеих таблиц – одинаковое количество товаров, одинаковые наименования. Но что, если, например, убрать последние четыре товара с отчета по ценам за упаковку? Тогда у нас будет ошибка #Н/Д в первой таблице в тех позициях, которые находятся на одной строке с искомым критерием:

Если вас не устраивает такое содержание ячеек, можно заменить значение ошибки. Для этого комбинируем функцию ВПР с функцией ЕСЛИОШИБКА. Синтаксис функции ЕСЛИОШИБКА(значение, значение_если_ошибка), таким образом значением у нас будет наша использованная функция ВПР, а значением если ошибка – то, что мы хотим видеть вместо #Н/Д, например, прочерк, но обязательно взятый в кавычки:

В результате мы получим красиво оформленную таблицу с надлежащим видом:

Использование приблизительного значения

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

Как видим, размер премии зависит от того диапазона по системе премирования, куда попал показатель выполнения продаж конкретного сотрудника. Мы видим, что если план выполнен менее, чем на 100% — премия не присваивается, а если на 107% (выше 100%, но меньше 110%), тогда сотрудник получает премию размером 10%. Описанные показатели премии нам нужно вписать с помощью функции ВПР в столбец «Премия» первой таблицы, только на этот раз критерий будет находиться в определённом диапазоне.

Для корректной работы нужно убедиться, что границы диапазонов во второй таблице крайнего левого столбца размещены по возрастанию сверху вниз (шаг 1). Формула берёт выбранный нами критерий и осуществляет поиск в первом столбце второй таблицы (шаг 2), просматривая все значения сверху вниз (шаг 3). Как только функция находит первое значение, которое превышает критерий с первой таблицы, делает «шаг назад» (шаг 4) и считывает значение, которое соответствует найденому критерию (шаг 5). Иными словами, при неточном поиске функция ВПР ищет меньшее значение для искомого критерия:

Таким образом, наша функция будет выглядеть так:

И результат использования функции ВПР с приблизительным поиском имеет вот такой результат:

Скачать все пошаговые примеры функции ВПР в Excel

Например, сотрудник Ольга имеет премию размером 0%, поскольку она выполнила 76% продаж, тоесть перевыполнила план на 0%. А сотрудник Наталья совершила продажи на 21% выше нормы и была премирована на 20%, что мы и видим, если сравнить самостоятельно данные с двух таблиц.

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