Ребята, всем привет! 👋 Сегодня разберем функции: ВПР и ГПР. Их использование зависит от расположения исходных данных в таблицах, из которых осуществляется подстановка.
При работе с большими таблицами для быстрого получения отдельных записей из этих списков можно использовать функции подстановок. Эти функции нужны для поиска связанных записей в таблицах.
При использовании таких функций задача формулируется следующим образом – есть значение, для которого нужно найти совпадение в другой таблице и получить в ответ значение, которое хранится в ячейке, соответствующей строки или столбца этой другой таблицы.
Основное применение этих функций – это подставлять данные, осуществлять сравнение двух таблиц.
📚 Немного теории...
✅ В случае если данные хранятся в столбцах: например, если известна Модель товара (критерий) и необходимо из таблицы получить данные столбца «Цена», которые (что важно!) находятся правее критерия, то можно воспользоваться функцией ВПР (применяется для вертикальных таблиц).
ВПР(Искомое_значение;Таблица;Номер_столбца;Интервальный_просмотр) – ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы.
VLOOKUP(Lookup_value;Table_array;Col_index_num;Range_lookup)
👉 Искомое_значение [Lookup_value] – значение, по которому ищем совпадение в первом столбце другой таблицы.
👉 Таблица [Table_array] – таблица, в которой в первом столбце осуществляется поиск искомого значения. Необходимо выделить таблицу так, чтобы в первом левом столбце было совпадение по искомому значению и правее, включая столбец ответа по задаче. Как правило, таблица при копировании формулы должна оставаться неизменной, поэтому она должна быть в абсолютной адресации.
👉 Номер_столбца [Col_index_lookup] – номер столбца-ответа по задаче (целое число), считается в выделенной таблице.
👉 Просматриваемый массив [Range_lookup] – число 0 или 1.
- 0 (Ложь [False]) – ищет первое точное совпадение при просмотре сверху вниз (если не находит – # н/Д [# n/A]).
- 1 (Истина [True]) – если нет совпадения, то выдает max_значение < искомого, при этом выделенная таблица должна быть отсортирована по первому столбцу по возрастанию.
✅ В случае если данные хранятся в строках: например, если известна Модель товара (критерий) и необходимо из таблицы получить данные строки «Цена», которые (что важно!) находятся ниже критерия, то удобно воспользоваться функцией ГПР (применяется для горизонтальных таблиц).
ГПР(Искомое_значение;Таблица;Номер_строки;Интервальный_просмотр) – ищет значение в крайней верхней строке таблицы и возвращает значение в том же столбце из указанной строки таблицы.
HLOOKUP(Lookup_value;Table_array;Row_index_num; Range_lookup)
👉 Искомое_значение [Lookup_value] – значение, которое должно быть найдено в первой строке другой таблицы.
👉 Таблица [Table_array] – таблица, в которой в первой стоке осуществляется поиск искомого значения. Необходимо выделить таблицу таким образом, чтобы в первой стоке было искомое значение и ниже строки, включая строку ответа по задаче.
👉 Номер_стоки [Row_index_lookup] – номер строки-ответа по задаче (целое число), считается в выделенной таблице.
👉 Просматриваемый массив [Range_lookup] – число 0 или 1.
- 0 (Ложь [False]) – ищет первое точное совпадение ( и если не находит - # н/Д [# n/A]
- 1 (Истина [True]) – если нет совпадения, то выдает max_значение < искомого, при этом выделенная таблица должна быть отсортирована по первой строке по возрастанию.
⚠ Если по расположению исходных данных не подходит функция ВПР или ГПР, то задачи можно решать с использованием функций ПОИСКПОЗ и ИНДЕКС (эти функции мы рассмотрим позже в наших уроках).
📝 Как обстоят дела на практике?
ФУНКЦИЯ ВПР
✅ Пример 1, определить стоимость товара из прайса зная его модель:
Прим. В ячейке F2 (посредством выпадающего списка) мы выбрали нужный товар. Далее, посредством зависимого выпадающего списка указали интересующую модель (ячейка G2).
Подробнее о выпадающих списках я расскажу позже в наших уроках
=ВПР(G2;C2:D7;2) – ищет значение модель (ячейка G2) в ячейках 1-го столбца (C) указанной таблицы (C2:D7).
Результат формулы (ячейка Н2)– значение ячейки 2-го столбца (D - -Стоимость, руб) выделенной таблицы строки с искомым значением модели.
⚠ Наиболее часто функцию ВПР используют для определения точного совпадения искомого значения в другой таблице, но возможно использование функции ВПР для приблизительного поиска.
Таблица, в которой ведется поиск должна быть отсортирована по возрастанию по первому столбцу (первый столбец считается, как и при точном поиске, в выделенной таблице).
✅ Пример 2, рассчитать % премии менеджеров в зависимости от количества продаж:
=ВПР(B3;$E$3:$F$6;2;ИСТИНА) – ищет значение суммы продаж (ячейка B2) в ячейках 1-го столбца (E) указанной таблицы($E$3:$F$6).
Результат формулы – значение ячейки 2-го столбца (F) выделенной таблицы ($E$3:$F$6) строки со значением ≤ искомого значения.
ФУНКЦИЯ ГПР
В случае, если искомая таблица по каким либо причинам имеет горизонтальный вид - используем функцию ГПР
✅ Пример 3, определить сумму продаж в месяц по указанному менеджеру:
=ГПР(G2;B1:E3;2;ЛОЖЬ) – ищет ФИО менеджера Петров А.А (ячейка G2) в ячейках 1-й строки (Менеджер) указанной таблицы B1:E3.
Результат формулы – значение ячейки 2-й строки таблицы (Сумма продаж в месяц, руб) столбца со значением ФИО менеджера Петров А.А.
На этом сегодня все. Продолжение следует...
В следующих уроках более подробно рассмотрим:
☑ логические функции
☑ текстовые функции
☑ функции для работы с датами
А так же:
☑ Создание условия с использованием формулы
☑ Защита ячеек, листов и рабочих книг Excel
☑ Установка ограничений на ввод данных
☑ Поиск неверных данных и др.
#excel #встроенные функции excel #финансовые функции excel
#математические функции excel #ГПР excel #впр excel