ПОИСК ДАННЫХ И ДИАПАЗОНЫ
VLOOKUP (ВПР)
Функция ВПР позволяет найти и вернуть данные из определенного столбца таблицы по заданному искомому значению (находящемуся в первом столбце этой таблицы).
Ее синтаксис:
VLOOKUP(искомое значение; диапазон данных; номер столбца; интервальный просмотр)
- Искомое значение — критерий, который мы будем искать в выбранном диапазоне данных.
- Диапазон данных — диапазон, в первом столбце которого будет производиться поиск критерия.
- Номер столбца — номер столбца (от начала диапазона), из которого нужно взять искомое значение.
- Интервальный_просмотр — аргумент для искомого значения (по умолчанию ИСТИНА, 1, рекомендуемое значение ЛОЖЬ, 0).
- ЛОЖЬ, 0 — возвращается только точное совпадение с критерием; важно отметить: если критерий будет встречаться несколько раз, то функция найдет его первое вхождение (поиск происходит сверху вниз).
- ИСТИНА, 1 — возвращается значение, максимально близкое к искомому (меньшее либо равное).
ВПР (ИНТЕРВАЛЬНЫЙ ПРОСМОТР = 1). ОТСОРТИРОВАННЫЙ И НЕОТСОРТИРОВАННЫЙ ДИАПАЗОН
На скриншоте ниже представлена таблица, в которой есть левая и правая части. Слева: сотрудники, их продажи и пустой столбец, в который нужно подставить премию. Справа: виды премий и минимальный порог продаж для их получения:
Будем использовать ВПР с интервальным просмотром = 1 (ИСТИНА). С таким аргументом формула будет искать в диапазоне поиска значение меньше либо равное искомому.
Чтобы все сработало, таблица для поиска (в нашем случае Е2:F4) должна быть отсортирована по возрастанию в первом столбце.
Посмотрите на правую часть таблицы — в ней указано минимальное значение продаж для получения премии, то есть:
- НИЧЕГО не получит тот, чьи продажи от 0 до 149;
- ВОЗДУШНЫЙ ШАРИК достанется сотруднику с продажами от 150 до 249;
- МОРОЖЕНОЕ получат все те, кто продал от 250 и до бесконечности.
Пишем в ячейке С2 формулу: вводим ВПР, в качестве искомого значения берем значение продаж из столбца B, закрепляем диапазон E2:F4 для поиска, указываем столбец для возврата = 2, делаем «интервальный просмотр» = 1 и копируем эту формулу на остальные ячейки столбца С:
Можно опустить аргумент «интервальный просмотр», потому что он по умолчанию равен 1:
Если поменять интервальный просмотр на 0, то ничего работать не будет и мы получим ошибку, так как формула начнет искать ТОЧНОЕ значение критерия из столбца В в диапазоне E2:F4, которого в таблице для поиска нет.
Что делать, если диапазон не отсортирован по столбцу поиска? Есть варианты: вы можете отсортировать его вручную или же применить на диапазон формулу SORT и вставить эту конструкцию в формулу ВПР.
Посмотрите на примере, на мой взгляд, выглядит довольно удобно:
Люди, которые разбираются в электронных таблицах куда лучше нас, утверждают, что ВПР на отсортированных данных и с интервальным просмотром, равным 1, работает быстрее, чем обычный ВПР. Разница заметна, когда дело касается использования формулы на десятках и сотнях тысяч строк. Впрочем, я очень надеюсь, дорогой читатель, что вам не придется решать задачи, способные напрочь подвесить все ресурсы системы.
КЕЙСЫ С ВПР (VLOOKUP)
Кейс «Ищем только точное значение аргумента с интервальным просмотром 0»
В примере нам нужно получить тематику книги (столбец В) по ее названию (столбец А) из таблицы.
Примечание. Если таблица, с которой вы работаете, может увеличиваться, то задавайте диапазоны с запасом, например $A$2:$В$100 или сразу $A:$C.
VLOOKUP с защитой от ошибок
Можно сделать VLOOKUP, который всегда берет значение из определенного столбца той же строки, в которой сам находится:
=VLOOKUP(INDIRECT(ADDRESS(ROW();1;4;true);true);таблица;номер_столбца;0)
=ВПР(ДВССЫЛ(АДРЕС(СТРОКА();1;4;ИСТИНА);ИСТИНА);таблица;номер_столбца;0)
Такую формулу не испортишь перемещением — в ней все параметры фиксированы, при этом каждый раз берется искомое значение из той же строки, в которой стоит формула.
Разберем формулу по частям, начиная изнутри — с вложенных функций:
- ADDRESS(ROW();1;4;true) — функция, возвращающая относительную (за относительность отвечает третий аргумент, равный 4) ссылку на ячейку этого же листа, первого столбца (второй аргумент) той же строки (первый аргумент), в которой находится функция. Последний аргумент true означает, что ссылка в стиле A1, а не R1C1.
- INDIRECT — функция, позволяющая ссылаться на ячейку, заданную в ее аргументе как текст (в нашем случае — с помощью функции ADDRESS, см. ).
- VLOOKUP — функция со стандартным применением, у которой первый аргумент задан на предыдущих двух шагах. Такой аргумент не «сбить» вырезанием и перемещением ячеек.
HLOOKUP (ГПР)
Все, что вам нужно знать о ГПР (HLOOKUP), — это перевернутая на 90° вправо формула ВПР. А еще поиск значения происходит не в столбце, как в ВПР, а в строке.
Напомню аргументы формулы:
=ГПР(значение для поиска;диапазон;номер строки;интервальный просмотр)
Перейдем к примерам:
Интервальный просмотр 0 (точное совпадение)
В ячейках G2:G5 находятся четыре формулы ГПР:
- диапазон данных для поиска везде один — A1:C6, это значит, что во всех случаях поиск будет начинаться с первой строки диапазона А1:C1;
- интервальный поиск равен нулю, то есть формулы будут искать точное совпадение.
С формулами в G2:G3 все понятно, мы нашли искомые значения в первой строке диапазона и вернули первую или вторую строку.
Давайте разберем ошибки. Формула в G4 возвращает ошибку, потому что запроса «Санкт-Петербург» нет в первой строке диапазона A1:C6.
G5 возвращает ошибку, потому что мы задали для возврата седьмую строку диапазона, но выбранный диапазон A1:C6 содержит всего шесть строк.
Интервальный просмотр 1 (ближайшее значение)
Как и ВПР, мы можем использовать формулу ГПР с аргументом «интервальный просмотр =1». И в этом случае формула будет искать в диапазоне ближайшее меньшее или точное значение.
Посмотрите на скриншот, в первой строке диапазона I1:M2 перечислены минимальные баллы для получения оценок, находящихся во второй строке. Например, двойку получат ученики, набравшие от 0 до 29,999 балла, с 30 баллов начинается тройка и т. д.
При таком формате таблицы можно воспользоваться ГПР с интервальным просмотром 1 — если мы введем любой балл от 0 до бесконечности, формула будет возвращать нам оценку.
1000 баллов, как и миллион, обозначают оценку 5, ведь это больше 85.
Для того чтобы формула работала, данные должны быть отсортированы, как на скриншоте — значения в строке для поиска должны увеличиваться слева направо.
