Примеры функции ГПР в Microsoft Excel

Функция ГПР предназначена для горизонтальных таблиц, о чем говорит ее название. Если вы работаете со стандартными вертикальными таблицами и не хотите преобразовывать их в горизонтальные, проще всего воспользоваться аналогичной функцией ВПР, выполняющей вертикальный просмотр. О ней уже написана статья на нашем сайте.

Подробнее: Функция ВПР в программе Microsoft Excel

Транспонировка вертикальной таблицы

Если у вас уже есть горизонтальная таблица, сразу же переходите к одному из двух разделов с примерами, а мы покажем, как транспонировать вертикальную таблицу для тех, у кого есть необходимость в ее преобразовании. Стандартная функциональность Excel позволяет реализовать подобное конвертирование в несколько кликов.

1. Выделите всю таблицу с зажатой левой кнопкой мыши, а затем сделайте по ней клик правой.



2. В контекстном меню нажмите кнопку «Копировать».



3. Выберите пустую ячейку для вставки таблицы или создайте отдельный лист для упрощения этой задачи. Снова правым кликом вызовите контекстное меню и нажмите «Специальная вставка».



4. Появится окно настройки, в котором среди всех параметров понадобится отметить только «транспонировать», после чего жмите «ОК».



5. Таблица сохранит свое форматирование и значения в ячейках, но теперь отобразится в горизонтальном представлении, а это значит, что можно переходить к использованию функции ГПР.

Пример 1: Одно значение

Ознакомимся с самым простым примером построения функции ГПР, чтобы у тех пользователей, кто ни разу не сталкивался с созданием подобных формул, не возникло трудностей при указании каждого аргумента.

1. Под основной горизонтальной таблицей у нас есть начало другой, где предстоит рассчитать, сколько единиц товара было продано в указанном месяце. Конечно, можно отыскать значение самому в основной таблице, однако это затратно по времени, если речь идет сразу о нескольких десятках или сотнях значений. Тогда создадим функцию ГПР, которая найдет значение автоматически.



2. Объявите ее в необходимом блоке, поставьте открывающую круглую скобку и переходите далее.



3. В качестве искомого значения указывается ячейка, по которой следует ориентироваться. В нашем случае это «Апрель».



4. После каждого объявленного аргумента не забывайте ставить знак «;», который закрывает его.



5. Следом выделите всю таблицу, в которой осуществляется поиск значения.



6. Введите номер строки для поиска. Если вы обратите внимание на следующий скриншот, то заметите, что он соответствует указанному ранее, а в качестве номера выступает просто цифра.



7. Добавьте в конце «0», чтобы обозначить точное совпадение, поставьте закрывающую круглую скобку и подтвердите создание функции нажатием клавиши Enter.



8. Сравним полученные данные с оригиналом, чтобы убедиться в правильном составлении функции.



9. Если далее у вас присутствуют другие ячейки, значения строк которых необходимо возвращать, не создавайте функцию с нуля или не копируйте ее с дальнейшим редактированием.



10. Выделите уже готовую ячейку и растяните ее на необходимое расстояние.



11. Подстановка значений прошла успешно.

Используйте приведенный выше пример в качестве основы, заменяя искомое значение и таблицу для поиска. Если нужно, открывайте окно «Аргументы функции», чтобы не запутаться при введении аргументов.

Пример 2: Несколько значений

Если для таблицы, где используется функция ГПР, необходимо рассчитать значения сразу в нескольких рядах, для этого не обязательно самостоятельно создавать функцию заново, указывать диапазон и остальные аргументы. Покажем, как значительно упростить эту задачу.

1. Мы добавили строки в нашу таблицу и создали новые значения для месяцев в нижней.



2. Используем все ту же формулу, созданную в первом примере.



3. Растяните ее вниз, чтобы посмотреть, как произойдет автоматическое замещение для новых строк.



4. Видно, что растягивание прошло успешно и значения подставились корректно.



5. Сразу выделите все ячейки для растягивания, чтобы не делать это с каждым столбцом.



6. Подсчет успешно завершен и все данные отображаются правильно. Проверить это можно, нажав по ячейке и посмотрев на указанные для нее аргументы в функции ГПР.