Функции поиска данных: разыскивается информация!
Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!
Я выполняю подобные процедуры каждый день и без описанных ниже функций мне действительно пришлось бы туго. Берите на заметку и применяйте их в работе!
Поиск в таблице Эксель, функции ВПР и ГПР
Роль этих функций в жизни обычного пользователя сложно переоценить. Теперь можно легко найти в таблице данных подходящую запись и вернуть соответствующее ей значение.
Синтаксис функции ВПР такой: =ВПР( Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:
- Искомое значение – значение, которое будем искать. Это обязательный аргумент;
- Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
- Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
- Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения.
Поиск точного совпадения с помощью ВПР
Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .
Функция ВПР в Excel
Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .
Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.
И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.
Если искомое значение не будет найдено, функция вернёт ошибку #Н/Д.
Поиск неточного совпадения с помощью ВПР
Благодаря этой опции в работе ВПР, мы можем избежать сложных формул, чтобы найти нужный результат.
В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:
Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.
Нестрогий поиск ВПР в Excel
Для корректной работы функции нужно отсортировать первый столбец таблицы по возрастанию. Иначе, она может дать ошибочный результат.
Функция ГПР имеет такой же синтаксис, как и ВПР, но ищет результат не в столбцах, а в строках. То есть, просматривает таблицы не сверху вниз, а слева направо и выводит заданный номер строки, а не столбца.
Поиск данных с помощью функции ПРОСМОТР
Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения) :
- Искомое значение – данные или ссылка на данные, которые нужно искать;
- Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
- Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.
При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10) . Задача успешно решена!
Функция «ПРОСМОТР» в Microsoft Excel
Поиск по относительным координатам. Функции ПОИСКПОЗ и ИНДЕКС
Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.
Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления]). Аргументы функции:
- Искомое значение – обязательный аргумент
- Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
- Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.
Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца]) :
- Массив – аргумент указывает из какого массива ячеек нужно выбрать значение
- Номер строки – указываете порядковый номер строки (начиная с первой ячейки массива), которую нужно вывести. Здесь можно записать значение вручную, либо использовать результат вычисления другой функции. Например, ПОИСКПОЗ.
- Номер столбца – необязательный аргумент, указывается, если массив состоит из нескольких столбцов. Если аргумент упущен, формула использует первый столбец таблицы.
Теперь скомбинируем эти функции, чтобы получить результат:
Функции ПОИСКПОЗ и ИНДЕКС в Эксель
Вот такие способы поиска и выведения на экран данных существуют в Excel. Далее, Вы можете использовать их в расчетах, использовать в презентации, выполнять с ними операции, указывать в качестве аргументов других функций и др.