Базовые формулы поиска данных используются, в основном, для поиска определенных строк и столбцов таблицы с целью извлечения другого значения таблицы. Приложение Excel предоставляет в ваше распоряжение три базовых функции просмотра: ГПР, ВПР и ПРОСМОТР. Функции ПОИСКПОЗ и ИНДЕКС часто используются совместно с ними; они возвращают ячейку или ссылку на ячейку с искомым значением.
Функция ВПР
Функция ВПР выполняет поиск значения в первом столбце просматриваемой таблицы и возвращает значение из ячейки, расположенной на пересечении найденной строки и указанного столбца таблицы. Данная функция выполняет вертикальный поиск по таблице. Синтаксис функции ВПР следующий:
=ВПР(искомое_значение;массив_табл;номер_индекса_столбца;
интервальный_просмотр)
Функция ВПР использует следующие аргументы.
• искомое_значение – значение, которое необходимо найти в первом столбце таблицы (значение, ссылка или строка).
• массив_табл – диапазон, содержащий таблицу, в которой выполняется поиск данных.
• номер_индекса_столбца – номер столбца в таблице, из которого необходимо возвратить соответствующее значение.
• интервальный_просмотр – необязательный аргумент. Если данный аргумент содержит значение ИСТИНА или опущен, функция возвращает значение, найденное путем приближенного сопоставления искомого значения. Если не определено точное значение, функция возвращает ближайшее значение, меньшее, чем заданный аргумент искомое_значение. В случае, если аргумент содержит логическое значение ЛОЖЬ, функция ищет точное соответствие. Если точное соответствие не найдено, функция возвращает ошибку #Н/Д.
Примечание
Если аргумент интервальный_просмотр содержит значение ИСТИНА или опущен, таблица должна быть отсортирована в порядке возрастания. В случае, если аргумент искомое_значение меньше самого меньшего значения, содержащегося в первом столбце диапазона массив_табл, функция ВПР вернет ошибку #Н/Д. Если аргумент интервальный_просмотр содержит значение ЛОЖЬ, первый столбец таблицы поиска не требует сортировки по возрастанию. Если точное соответствие не найдено, функция возвращает ошибку #Н/Д.
Если аргумент искомое_значение содержит текст и аргумент интервальный_просмотр равен ЛОЖЬ, искомое значение может содержать групповые символы * и ?. Звездочка означает любое количество произвольных символов, а знак вопроса – один произвольный символ.
Классическим примером использования формулы поиска может служить таблица с процентными ставками налога. В эту таблицу внесены ставки налога, каждая из которых соответствует определенному уровню дохода служащего. Следующая формула, введенная в ячейку В3, возвращает ставку налога для заданного уровня дохода в ячейке В2:
=ВПР(В2;D2:F7;3)
Анализируемая таблица находится в диапазоне, состоящем из трех столбцов (D2:F7). Последний аргумент функции ВПР – 3, поэтому формула возвращает соответствующее значение, содержащееся в третьем столбце таблицы.
В данном случае точного соответствия не требуется. Если точное соответствие не найдено в первом столбце просматриваемой таблицы, функция ВПР будет использовать ближайшее наибольшее значение, которое меньше искомого в таблице значения. Другими словами, функция вернет строку, содержащую значение, которое меньше или равно искомому, а значение следующей за ней строки – больше искомого значения.
Функция ГПР
Функция ГПР работает аналогично ВПР, за исключением того, что таблица просматривается не по вертикали, а по горизонтали. Функция просматривает верхнюю строку таблицы и возвращает значение из указанной строки.
Функция ГПР имеет следующий синтаксис:
=ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)
В функции ГПР используются следующие аргументы.
• искомое_значение – значение, которое требуется найти в первой строке таблицы.
• таблица – таблица, в которой выполняется поиск данных.
• номер_строки – номер строки в таблице, из которой возвращается сопоставляемое значение.
• интервальный_просмотр – необязательный аргумент. Если значение этого аргумента ИСТИНА или опущено, функция возвращает значение, найденное путем приближенного сопоставления искомого значения (если точное соответствие не найдено, функция вернет наибольшее значение, меньшее чем значение аргумента искомое_значение). В случае, если аргумент содержит логическое значение ЛОЖЬ, функция ГПР будет искать точное соответствие. Если точное соответствие не найдено, функция вернет ошибку #Н/Д.
Если аргумент искомое_значение содержит текстовое значение и аргумент интервальный_просмотр равен ЛОЖЬ, в искомом значении можно использовать групповые символы * и ?. Звездочка соответствует любому количеству символов, а знак вопроса – одному символу.
На рисунке показана таблица со ставками подоходного налога, но уже в горизонтальной форме (E1:J3). Формула в ячейке ВЗ выглядит следующим образом:
=ГПР(В2;E1:J3;3)
Функция ПРОСМОТР
Функция ПРОСМОТР имеет следующий синтаксис:
=ПРОСМОТР(искомое_значение;вектор_просмотра;вектор_результата)
Данная функция использует следующие аргументы.
• искомое_значение – значение, которое функция ищет в векторе просмотра.
• вектор_просмотра – диапазон, состоящий только из одной строки или одного столбца и содержащий значения, среди которых необходимо найти нужное. Значения должны располагаться в порядке возрастания.
• вектор_результата – диапазон, состоящий только из одной строки или одного столбца и содержащий значения, которые будут возвращены. Размер этого диапазона должен быть равен размеру диапазона вектор_просмотра.
Функция ПРОСМОТР просматривает диапазон, состоящий из одной строки или одного столбца (вектор_просмотра), находит необходимое значение (искомое_значение) и возвращает значение, содержащееся в соответствующей ячейке второго диапазона, который также состоит из одной строки или одного столбца (вектор_результата).
Предупреждение
Имейте в виду, что значения диапазона вектор_просмотра должны располагаться в порядке возрастания. В том случае, когда искомое_значение меньше наименьшего значения, содержащегося в диапазоне вектор_просмотра, функция возвращает ошибку #Н/Д.
Примечание
Справочная система Excel указывает, что функция ПРОСМОТР имеет два варианта синтаксиса, один из которых включает аргумент массив. Это обеспечивает совместимость Excel с другими программами управления электронными таблицами. Однако на практике вместо второго варианта синтаксиса предпочтительнее использовать функции ВПР и ГПР.
На рисунке показана все та же таблица ставок налога, только на этот раз формула, которая введена в ячейку ВЗ, содержит функцию ПРОСМОТР, возвращающую соответствующую ставку. Обратите внимание на то, что возвращаемые значения содержатся в другой таблице.
=ПРОСМОТР(В2;D2:D7;F2:F7)
Предупреждение
Если значения в первом столбце не расположены в порядке возрастания, значение, которое возвращает функция ПРОСМОТР, может быть неправильным.
В отличие от функции ВПР, функция ПРОСМОТР может вернуть значение, содержащееся в строке, отличной от найденной. Если аргументы вектор_поиска и вектор_результата не принадлежат одной и той же таблице, функция ПРОСМОТР может оказаться очень полезной. Если же они принадлежат одной таблице и данные не отсортированы, лучше применить функцию ВПР.
Комбинирование функций ПОИСКПОЗ и ИНДЕКС
Достаточно часто при поиске данных функции ПОИСКПОЗ и ИНДЕКС используются вместе. Функция ПОИСКПОЗ возвращает относительную позицию ячейки в диапазоне, соответствующую искомому значению. Синтаксис функции следующий:
=ПОИСКПОЗ(искомое_значение;массив_поиска;тип_сопоставления)
Функция ПОИСКПОЗ принимает три аргумента.
• искомое_значение – значение, которое требуется найти в массиве массив_поиска. Если третий аргумент этой функции – тип_сопоставления – равен нулю, а аргумент искомое_значение представляет собой текст, то последний может содержать макросимволы * и ?.
• массив_поиска – диапазон, который содержит искомое значение.
• тип_сопоставления – целое число (-1, 0 или 1), которое задает метод сопоставления.
Примечание
В том случае, если аргумент тип_сопоставления равен единице, функция ПОИСКПОЗ ищет самое большое значение, которое меньше либо равно аргументу искомое_значение (значения диапазона массив_поиска должны располагаться в порядке возрастания, т.е. диапазон должен быть отсортирован). Если аргумент тип_сопоставления равен нулю, функция ищет первое значение, в точности соответствующее аргументу искомое_значение. Если же аргумент тип_сопоставления равен -1, функция ищет наименьшее значение, которое больше или равно аргументу искомое_значение (в этом случае значения диапазона массив_поиска должны располагаться в порядке убывания). Если аргумент тип_сопоставления опущен, то по умолчанию он принимается равным единице.
Функция ИНДЕКС возвращает ячейку диапазона. Синтаксис функции следующий:
=ИНДЕКС(массив;номер_строки;номер_столбца)
Функция ИНДЕКС принимает три аргумента.
• массив – диапазон, являющийся массивом.
• номер_строки – строка в массиве массив, из которого возвращается значение.
• номер_столбца – столбец в массиве массив, из которого возвращается значение.
Примечание
Если массив содержит всего одну строку или столбец, соответствующий аргумент номер_строки или номер_столбца использовать не обязательно.
На рисунке показан рабочий лист, в котором в столбцах D, Е и F содержатся значения дат, дней недели и сумм. При вводе даты в ячейку В1 формула, приведенная ниже (ячейка В2), просматривает значения в столбце D (так выполняется поиск необходимой даты), а затем возвращает из столбца F соответствующую этой дате сумму.
=ИНДЕКС(F2:F21;ПОИСКПОЗ(В1;D2:D21;0))
Чтобы понять, как работает эта формула, начните с функции ПОИСКПОЗ. Данная функция ищет в диапазоне D2:D21 дату, содержащуюся в ячейке В1, и относительный номер строки, в которой эта дата найдена. Полученное значение используется в качестве второго аргумента функции ИНДЕКС. В результате формула возвращает соответствующее значение, содержащееся в диапазоне F2:F21.
Когда пустая ячейка не считается нулем
Часто возникает ситуация, когда ячейки анализируемого диапазона не содержат никаких значений. Функции поиска Excel обрабатывают такие ячейки как нулевые значения. Например, рабочий лист на приведенном ниже рисунке содержит таблицу, состоящую из двух столбцов. Приведенная ниже формула просматривает первый столбец, отыскивая в нем имя, введенное в ячейку В1, и возвращает соответствующую этому имени сумму.
=ВПР(В1;D2:Е8;2;ЛОЖЬ)
Обратите внимание, что ячейка Сумма, соответствующая имени Чаки, пустая, однако формула возвращает нулевое значение.
Если нужно, чтобы нулевые значения отличались от пустых ячеек, измените формулу просмотра, добавив в нее функцию ЕСЛИ, – так вы определите длину возвращаемого значения. В том случае, если возвращаемая ячейка пустая, длина значения воспринимается равной нулю, во всех других случаях – как ненулевое значение. Следующая формула возвращает пустую строку всякий раз, когда длина возвращаемого значения нулевая; фактическое значение будет отображено в том случае, когда длина значения не равна нулю.
=ЕСЛИ(ДЛСТР(ВПР(В1;D2:Е8;2))=0;“”;(ВПР(В1;D2:Е8;2)))