Кроме базовых формул поиска данных, Excel содержит несколько дополнительных формул, позволяющих осуществить специализированный поиск данных. К примеру, используя эти формулы, можно искать точные значения, значения в столбце рядом с первым столбцом таблицы, выполнять поиск с учетом регистра символа, возвращать значение из множества таблиц поиска, а также выполнять другие виды сложного поиска.
Поиск точного значения
Функции ВПР и ГПР не требуют точного соответствия между искомым значением и значением в таблице поиска. Иногда это допустимо, например, при поиске ставки налога. В других ситуациях может потребоваться полное соответствие значений. Например, при поиске табельного номера служащего необходимо полное соответствие номера.
Чтобы найти только точно соответствующее значение, используются те же функции ВПР или ГПР, но с необязательным четвертым аргументом со значением ЛОЖЬ.
На рисунке показан рабочий лист с таблицей, которая содержит табельные номера (столбец С), а также имена и фамилии служащих (столбец D). Таблица поиска носит название СписокСлужащих. Следующая формула, введенная в ячейку В2, ищет табельный номер служащего, заданный в ячейке В1, и возвращает имя служащего, имеющего данный табельный номер.
=ВПР(В1;СписокСлужащих;2;ЛОЖЬ)
Поскольку последний аргумент функции ВПР имеет значение ЛОЖЬ, функция возвращает значение только в том случае, если найдено точное соответствие. В противном случае, если значение не найдено, формула возвращает ошибку #Н/Д. Безусловно, поиск приближенного соответствия значения табельного номера служащего не имеет в данном случае никакого смысла. Обратите внимание также на то, что табельные номера служащих в столбце С расположены не в порядке возрастания, поскольку при использовании в качестве четвертого аргумента логического значения ЛОЖЬ упорядочения по возрастанию не требуется.
Если нужно, чтобы вместо ошибки #Н/Д на экране отображалась другая информация (в случае, если табельный номер служащего не найден), используйте функцию ЕСЛИОШИБКА. Ее синтаксис следующий:
=ЕСЛИОШИБКА(значение;значение_если_ошибка)
где:
• значение – это произвольное выражение; обычно это формула, потенциально способная вернуть ошибку;
• значение_если_ошибка – это любое выражение, содержащее числа, строки, ссылки на ячейки или формулы.
Следующая формула вместо ошибки #Н/Д отображает текст не найдено:
=ЕСЛИОШИБКА(ВПР(В1;СписокСлужащих;2;ЛОЖЬ));“Не найдено”)
Поиск значения слева
Функция ВПР всегда ищет значение в первом столбце заданного диапазона. Но как быть, если необходимо найти значение не в первом, а в каком-либо другом столбце? Следует отметить, что третьему аргументу функции ВПР нельзя назначать отрицательное значение.
Рассмотрим эту проблему на простом примере. Предположим, что необходимо найти средний темп (столбец В, диапазон Среднее) одного из игроков из столбца С (диапазон Игрок). Имя игрока, данные о котором необходимо найти, введено в ячейку Искомый_игрок. В таком случае функция ВПР работать не будет, поскольку данные размещены некорректно.
Конечно, данные легко переупорядочить, однако иногда и это не допускается.
Один из способов решения этой проблемы – использовать функцию ПРОСМОТР, которая принимает в качестве аргументов два диапазона. Следующая формула (в ячейке F3) возвращает средний темп игрока (из столбца В), имя которого введено в ячейку Искомый_игрок:
=ПРОСМОТР(Искомый_игрок;Игрок;Среднее)
Для того, чтобы использовать функцию ПРОСМОТР, диапазон поиска (в данном случае диапазон Игрок) необходимо представить в порядке возрастания. Кроме этого ограничения, формула имеет еще одну небольшую проблему: при вводе имени несуществующего игрока (другими словами, если ячейка Искомый_игрок содержит значение, которое отсутствует в диапазоне Игрок), формула возвращает значение ошибки.
Более эффективный способ – совместное использование функций ИНДЕКС и ПОИСКПОЗ. Следующая формула работает точно так же, как предыдущая, за исключением того, что она возвращает ошибку #Н/Д, если искомый игрок не найден. Еще одно преимущество этой формулы состоит в том, что имена игроков не требуют сортировки.
=ИНДЕКС(Среднее;ПОИСКПОЗ(Искомый_игрок;Игрок;0))
Поиск с учетом регистра
Рассмотрим задачу поиска текста в таблице данных. Базовые функции поиска данных в Excel (ПРОСМОТР, ВПР и ГПР) не учитывают регистр символов. Поэтому в результате использования этой функции для поиска в таблице текста бюджет формула вернет любую соответствующую этому тексту строку, например: БЮДЖЕТ, Бюджет или БюДжЕт.
Рабочий лист, показанный на рисунке, содержит два диапазона: первый включает ячейки D2:D7 (с именем Диапазон01), второй – ячейки Е2:Е7 (с именем Диапазон02). Текст, который необходимо найти, содержится в ячейке В1 (с именем Слово).
Следующая формула массива, введенная в ячейку В2, находит заданное значение в диапазоне Диапазон01, учитывая при этом регистр символов, и возвращает соответствующее значение из диапазона Диапазон02.
{=ИНДЕКС(Диапазон02;ПОИСКПОЗ(ИСТИНА;СОВПАД(Слово;Диапазон01);0))}
Данная формула находит слово СОБАКА (все символы верхнего регистра) и возвращает соответствующее значение 300. В свою очередь, стандартная формула поиска, использующая функцию ПРОСМОТР, возвращает значение 400.
=ПРОСМОТР(Слово;Диапазон01;Диапазон02)
Примечание
Не забывайте, что при вводе формулы массива необходимо использовать клавиши <Ctrl+Shift+Enter>.
Поиск значения во множестве таблиц
Рабочий лист может содержать любое количество таблиц поиска данных. В таких случаях в форму необходимо ввести информацию о том, в какой именно таблице будет осуществляться поиск. На рисунке приведен пример использования множества таблиц на одном рабочем листе.
Представленная в примере рабочая книга содержит два диапазона: диапазон G3:Н9 (с именем Таблица01) и диапазон J3:К8 (с именем Таблица02) и вычисляет комиссионные менеджеров фирмы. Ставка комиссионного вознаграждения каждого отдельного менеджера зависит от двух факторов: стажа работы менеджера в данной фирме (столбец В) и объема реализованного им товара (столбец С). Столбец D содержит формулы поиска ставки комиссионных в соответствующей таблице. Например, ячейка D2 содержит следующую формулу:
=ВПР(С2;ЕСЛИ(В2<3;Таблица01;Таблица02);2)
Второй аргумент функции ВПР содержит функцию ЕСЛИ, которая использует значение ячейки В2 для определения того, какую из таблиц следует просматривать.
Формула в столбце Е просто умножает объем продаж каждого менеджера в столбце С на ставку комиссионного вознаграждения из столбца D. Ячейка Е2 содержит следующую формулу:
=C2*D2
Вычисление оценки по количеству набранных баллов
Еще один пример использования таблиц поиска данных – это выставление экзаменационных оценок по результатам тестирования студентов. На рисунке показан рабочий лист, который содержит таблицу с количеством набранных баллов и соответствующих оценок (диапазон Е2:F6 с именем СписокОценок). Данная таблица используется для присвоения студенту, сдавшему тест, одной из оценок в виде буквы.
Столбец С содержит формулы, которые используют функцию ВПР и таблицу классификаторов, чтобы на основании полученного каждым студентом количества баллов (столбец В) присвоить ему соответствующую оценку. Например, ячейка С2 содержит следующую формулу:
=ВПР(В2;СписокОценок;2)
Если таблица поиска содержит небольшое количество значений, вместо нее можно использовать массив символов. Например, приведенная ниже формула возвращает оценку, не используя при этом таблицу поиска. Информация таблицы поиска введена непосредственно в формулу массива.
=ВПР(В5;{0;“F”:40;“D”:70;“С”:80;“В”:90;“А”};2)
В другой, более удобной, формуле назначения букв используется функция ПРОСМОТР с двумя аргументами, представляющими собой массивы.
=ПРОСМОТР(В2;{0;40;70;80;90};{“F”;“D”;“С”;“В”;“А”})
В заключение, если удается преобразовать входное значение (в данном случае количество баллов) в целое число от единицы до 29, можете воспользоваться функцией ВЫБОР. В данном примере можно разделить количество баллов на 10, отбросить дробную часть и добавить единицу. В результате получим целые числа от 1 до 11. Оставшиеся аргументы функции определяют возвращаемые значения для всех 11 результатов.
=ВЫБОР(ЦЕЛОЕ(В2/10)+1;“F”;“F”;“F”;“F”;“F”;“F”;“F”;“F”;“F”;“F”;“F”)
Вычисление средней оценки
Средняя оценка в данном примере вычисляется как среднее числовое значение оценок, полученных студентами. Для вычисления этого показателя каждая буква оценки сопоставляется с определенным числовым значением (А=4, В=3, С=2, D=1 и F=0). Средняя оценка рассчитывается как среднее числовых значений оценок, взвешенное по количеству часов, пройденных по данному курсу. Например, одночасовой курс меньше влияет на среднюю оценку, чем трехчасовой. Градация показателя средней оценки начинается с 0 (все F) и заканчивается 4,00 (все А).
На рисунке показан рабочий лист, содержащий информацию о студенте. Этот студент посетил пять занятий, общее количество прослушанных им часов – 13 (диапазон В2:В6 – ЧасыОбучения). Оценки, полученные за каждый курс, содержатся в столбце С (диапазон С2:С6 – Оценки). Столбец D отображает формулу поиска, которая определяет числовое значение оценки для каждого занятия. Например, следующая формула (в ячейке D2) использует таблицу поиска G2:Н6 (ТаблицаОценок):
=ВПР(С2;ТаблицаОценок;2;ЛОЖЬ)
Формулы, введенные в столбец Е, вычисляют взвешенные значения. Например, формула в ячейке Е2 выглядит следующим образом:
=D2*B2
И наконец, следующая формула в ячейке В8 вычисляет среднюю оценку:
=СУММ(Е2:Е6)/СУММ(В2:В6)
Несмотря на то, что предыдущие формулы работают достаточно хорошо, можно упростить вычисление среднего показателя. Для этого применяйте единственную формулу массива, не прибегая к использованию таблицы поиска и формул в столбцах D и Е. Эта формула массива выглядит следующим образом:
{=СУММ((ПОИСКПОЗ(Оценки;{“F”;“D”;“С”;“В”;“А”};0)-1)*
ЧасыОбучения)/СУММ(ЧасыОбучения)}
Поиск на пересечении строк и столбцов
На рисунке показан рабочий лист, который содержит данные о ежемесячных объемах продаж товаров. Для того чтобы найти информацию об объеме продаж определенного товара в течение одного заданного месяца, в ячейку В1 необходимо ввести искомый месяц, а в ячейку В2 – название товара.
Для упрощения работы с формулами на данном рабочем листе используются следующие диапазоны ячеек.
Следующая формула (ячейка В4) содержит функцию ПОИСКПОЗ и возвращает позицию заданного месяца в диапазоне ячеек СписокМесяцев. Например, если вы ищете месяц январь, то данная формула возвращает значение 2, поскольку январь – это второй элемент диапазона СписокМесяцев (первый элемент – незаполненная ячейка D1).
=ПОИСКПОЗ(Месяцы;СписокМесяцев;0)
Формула, введенная в ячейку В5, работает таким же образом, но применяется для диапазона СписокТоваров.
=ПОИСКПОЗ(Товар;СписокТоваров;0)
И наконец, формула в ячейке В6 возвращает соответствующий заданным условиям объем продаж. В формуле используется функция ИНДЕКС, которая в качестве аргументов принимает результаты, полученные в ячейках В4 и В5.
=ИНДЕКС(Таблица;В4;В5)
Все приведенные формулы можно объединить в одну.
=ИНДЕКС(Таблица;ПОИСКПОЗ(Месяц;СписокМесяцев;0);
ПОИСКПОЗ(Товар;СписокТоваров;0))
Существует еще один способ быстрого создания формулы для поиска значения на пересечении строк и столбцов. Он предусматривает присвоение имен каждой строке и каждому столбцу таблицы. Для этого выделите таблицу с данными и выберите команду Формулы→Определенные имена→Создать из выделенного. После создания имен приведенные формулы будут выглядеть гораздо проще.
=Зажигалки Июль
Данная формула использует пересечение двух диапазонов и возвращает данные о продажах зажигалок в июле. Для обращения к ячейкам, в которые введены месяц и товар, используйте следующую формулу:
=ДВССЫЛ(Месяцы) ДВССЫЛ(Товар)
Эта формула преобразует значения ячеек с именами Месяцы и Товар в ссылки на диапазоны и находит пересечение.
Поиск информации в двух столбцах
В отдельных случаях таблица поиска может содержать два столбца с искомыми данными. На рисунке приведен пример такой таблицы.
Таблица поиска содержит список автомобилей, для каждого из которых указаны производитель, модель и код. Диапазоны рабочего листа названы следующим образом.
F2:F12 – Код; В1 – Производитель; В2 – Модель; D2:D12 – Диапазон4; Е2:Е12 – Диапазон5.
Следующая формула массива выводит на экран номер, соответствующий автомобилю определенной марки и модели:
{=ИНДЕКС(Код;ПОИСКПОЗ(Производитель&Модель;Диапазон4&Диапазон5;0))}
Данная формула связывает текстовые строки, содержащиеся в ячейках Производитель и Модель, а затем ищет текст в соответствующем объединении двух диапазонов: Диапазон4 и Диапазон5.
Определение местонахождения значения в диапазоне
В большинстве случаев формулы просмотра данных таблицы используются для поиска определенных значений. Однако иногда требуется найти не само значение, а его местоположение. Например, на рисунке показан рабочий лист, который отображает диапазон данных, состоящий всего лишь из одного столбца (с именем Данные1). Ячейка В1 с именем Цель содержит значение, которое необходимо найти.
Следующая формула, введенная в ячейку В2, возвращает адрес той ячейки из диапазона Данные, которая содержит значение Цель:
=АДРЕС(СТРОКА(Данные1)+ПОИСКПОЗ(Цель;Данные1;0)-1;СТОЛБЕЦ(Данные1))
Если диапазон Данные содержит только одну строку, для поиска адреса значения воспользуйтесь следующей формулой:
=АДРЕС(СТРОКА(Данные1);СТОЛБЕЦ(Данные1)+ПОИСКПОЗ(Цель;Данные1;0)-1)
Если диапазон Данные содержит несколько экземпляров искомого значения, формулы вернут адрес первой найденной ячейки с этим значением. Если искомое значение не найдено, формулы вернут ошибку #Н/Д.
Поиск значения, максимально приближенного к заданному
Формулы ПРОСМОТР и ГПР наиболее часто используются в следующих ситуациях.
• Если необходимо найти значение, в точности соответствующее заданному. Для этого в качестве четвертого аргумента функций используется логическое значение ЛОЖЬ.
• Если необходимо найти значение, максимально приближенное к заданному. Для этого в качестве четвертого аргумента функций используется логическое значение ИСТИНА. Аргумент может быть также опущен, а поиск точного соответствия не выполняется вообще. Формула возвращает ближайшее к заданному значение, но в меньшую сторону.
Если необходимо найти значение, максимально близкое к заданному, независимо от направления отклонения, ни одна из предложенных выше базовых функций (ПРОСМОТР и ГПР) не сможет выполнить эту задачу.
На рисунке показан рабочий лист, содержащий имена студентов (столбец А) и некоторые значения (столбец В). Диапазон В2:В20 называется Данные2, ячейка Е2 (с именем Цель2) содержит значение, которое необходимо найти в диапазоне Данные2. Ячейка ЕЗ (с именем СмещениеСтолбца) содержит значение, которое определяет, на сколько столбцов и в какую сторону от диапазона Данные2 происходит смещение значения.
Приведенная ниже формула массива определяет ближайшее значение из диапазона Данные2, которое соответствует значению Цель2, и возвращает имя студента в столбце А (т.е. столбце, смещенном на -1 от столбца с диапазоном Данные2). Формула возвращает имя Давид, которому соответствует значение 8000 – ближайшее значение к целевому, равному 8025.
{=ДВССЫЛ(АДРЕС(СТРОКА(Данные)+ПОИСКПОЗ(МИН(ABS(Цель-Данные));
ABS(Цель-Данные);0)-1;СТОЛБЕЦ(Данные)+СмещениеСтолбца))}
Если в диапазоне Данные2 формула находит два значения, которые одинаково близки к значению Цель2, то она возвращает первое из найденных в списке значений.
Значение, содержащееся в ячейке СмещениеСтолбца, может быть отрицательным (если столбец расположен слева от диапазона Данные2), положительным (если столбец расположен справа от диапазона Данные2) или равным нулю (если ближайшее значение расположено в самом диапазоне Данные2).
Чтобы понять, как работает эта формула, вы должны уяснить себе принцип работы функции ДВССЫЛ. В качестве первого аргумента этой функции используется текстовая строка в виде ссылки на ячейку (или ссылки на ячейку, содержащую текстовую строку). В нашем примере текстовая строка создается с помощью функции АДРЕС, которая принимает в качестве аргументов номера строки и столбца, а возвращает адрес ячейки.
Поиск значения методом линейной интерполяции
Интерполяция представляет собой процесс создания недостающих значений на основе существующего ряда значений. Рассмотрим пример интерполяции данных. На рисунке показан рабочий лист, в котором столбец D содержит список значений х, а столбец Е – список значений у, соответствующих каждому значению х.
Кроме того, в рабочий лист включен график, который визуально иллюстрирует связь между диапазонами х и у. Расположение точек на диаграмме говорит о том, что зависимость между значениями х и у приблизительно линейная: рост значения х вызывает пропорциональный рост значения у. Обратите внимание, что значения в диапазоне х введены не в строгой последовательности. Например, отсутствуют значения 3, 6, 7, 14, 17, 18 и 19.
Вы уже умеете создавать формулы, которые просматривают значения диапазона х и возвращают значения диапазона у. Но как быть, если необходимо рассчитать значение у для отсутствующего значения х? Обычная формула таблицы поиска возвращает неправильный результат, поскольку оперирует только существующими значениями у (вместо расчетных). Например, следующая формула ищет значение 3, но возвращает значение 18,00 (как значение, соответствующее значению 2 в диапазоне х):
=ПРОСМОТР(3;х;у)
Именно в таких случаях требуется интерполяция значений. Другими словами, поскольку искомое значение 3 находится посредине между существующими значениями х (2 и 4), возвращаемое значение у должно быть равно 21,00, т.е. тоже располагаться посредине между значениями 18,00 и 24,00.
Формулы линейной интерполяции
На рисунке показан рабочий лист с формулами, введенными в столбец В. Искомое значение введено в ячейку В1, а итоговая формула в ячейке В16 возвращает результат подсчетов. Если значение, содержащееся в ячейке ВЗ, найдено в диапазоне х, формула возвращает соответствующее значение у. В противном случае (если значение в ячейке ВЗ не найдено) формула в ячейке В16 возвращает расчетное значение у, полученное путем линейной интерполяции.
Критично, чтобы диапазон х содержал значения в порядке возрастания. Если ячейка В1 содержит значение, меньшее, чем минимальное значение х, или большее, чем максимальное значение, формула возвращает значение ошибки. Формулы, используемые для получения значений методом интерполяции, приведены в таблице.
Комбинирование функций ПРОСМОТР и ТЕНДЕНЦИЯ
Существует еще один, более предпочтительный, способ поиска данных в таблице методом линейной интерполяции. Этот способ основан на использовании функций ПРОСМОТР и ТЕНДЕНЦИЯ и обладает одним большим преимуществом – в нем используется только одна формула.
Формула, приведенная ниже (в ячейке В2), для принятия решения использует функцию ЕСЛИ. Если в диапазоне х найдено значение, в точности соответствующее искомому, формула возвращает соответствующее значение из диапазона у (используется функция ПРОСМОТР). Если же точное соответствие не найдено, формула использует функцию ТЕНДЕНЦИЯ и возвращает значение у, рассчитанное методом поиска максимально близкой величины (не прибегая к линейной интерполяции).
=ЕСЛИ(В1=ПРОСМОТР(В1;х;х);ПРОСМОТР(ИНДЕСК(х;ПОИСКПОЗ(
ПРОСМОТР(В1;х;х);х;0));х;у);ТЕНДЕНЦИЯ(у;х;В1))