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

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

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

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

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

  1. Выделите всю таблицу с зажатой левой кнопкой мыши, а затем сделайте по ней клик правой.
  2. Выделение таблицы для ее транспонирования перед использованием функции ГПР в Excel

  3. В контекстном меню нажмите кнопку «Копировать».
  4. Копирование таблицы для ее транспонирования перед использованием функции ГПР в Excel

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

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

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

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

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

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

  3. Объявите ее в необходимом блоке, поставьте открывающую круглую скобку и переходите далее.
  4. Объявление функции ГПР в Excel для горизонтального просмотра строк

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

  7. После каждого объявленного аргумента не забывайте ставить знак «;», который закрывает его.
  8. Закрытие аргумента при использовании функции ГПР в Excel

  9. Следом выделите всю таблицу, в которой осуществляется поиск значения.
  10. Выделение таблицы для ее добавления в функцию ГПР в Excel

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

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

  15. Сравним полученные данные с оригиналом, чтобы убедиться в правильном составлении функции.
  16. Проверка функции ГПР в Excel после ее создания

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

  19. Выделите уже готовую ячейку и растяните ее на необходимое расстояние.
  20. Растягивание простой функции ГПР в Excel после ее создания

  21. Подстановка значений прошла успешно.
  22. Успешное растягивание функции ГПР в Excel после ее создание

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

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

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

  1. Мы добавили строки в нашу таблицу и создали новые значения для месяцев в нижней.
  2. Добавление строк для использования функции ГПР в Excel

  3. Используем все ту же формулу, созданную в первом примере.
  4. Объвление функции ГПР в Excel для ее растягивания на все строки

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

  7. Видно, что растягивание прошло успешно и значения подставились корректно.
  8. Успешное растягивание функции ГПР в Excel на столбец после ее создания

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

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

Закрыть Мы рады, что смогли помочь Вам в решении проблемы.
Закрыть Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

Поделиться статьей в социальных сетях:



Еще статьи по данной теме:


1 комментарий

При протягивании ГПР вниз номер строки никак не изменится. Как стояло 2, так 2 и останется. При этом искомое значение изменилось. И значит формула вернет /Н#Д.

Задайте вопрос или оставьте свое мнение

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