20.7 C
Москва
31.08.2026

1

Как гпр в excel для чайников

Примеры формулы ГПР для выборки по строкам с условием в Excel

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

Формула ГПР и выборка по строкам в Excel для чайников

Ниже на рисунке представленная таблица с показателями населения по самым крупным городам России. После выбора пользователем города из выпадающего списка, ниже автоматически заполняется ячейка показателем населения соответствующее выбранному городу. Формула:

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

Формула ИНДЕКС и ПОИСКПОЗ для горизонтальной выборки по строкам

Функции ГПР и ВПР очень похожи между собой. Их обе можно заменить формулами из функций ИНДЕКС и ПОИСКПОЗ:

ИНДЕКС и ПОИСКПОЗ для горизонтальной выборки.

В данным примере номер строки в функции ИНДЕКС определен пользователем изначально и является неизменным – это число 2. Функция ПОИСКПОЗ автоматически определяет значение для третьего аргумента функции ИНДЕКС. ПОИСКПОЗ может искать только по одной строке или по одному столбцу ячеек. При нахождении содержимого ячейки соответственного искомому значению функция возвращает ее позицию в строке.

  • Excel Formula Examples
  • Создать таблицу
  • Форматирование
  • Функции Excel
  • Формулы и диапазоны
  • Фильтр и сортировка
  • Диаграммы и графики
  • Сводные таблицы
  • Печать документов
  • Базы данных и XML
  • Возможности Excel
  • Настройки параметры
  • Уроки Excel
  • Макросы VBA
  • Скачать примеры

Как гпр в excel для чайников

Вопросы по покупке sales@onlyoffice.com

Запросы на партнерство partners@onlyoffice.com

Запросы от прессы press@onlyoffice.com

Следите за нашими новостями:

© Ascensio System SIA 2023. Все права защищены

© Ascensio System SIA 2023. Все права защищены

Не пропустите обновление!

Получайте последние новости ONLYOFFICE на ваш email

Имя не указано.

Email не указан.

На ваш адрес электронной почты отправлено сообщение с подтверждением.

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

ГПР (функция ГПР)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Совет: Попробуйте воспользоваться новой функцией ПРОСМОТРX , улучшенной версией функции ГГ ПРОСМОТР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее использовать предшественницу.

В этой статье описаны синтаксис формулы и использование функции ГПР в Microsoft Excel.

Описание

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

Буква Г в аббревиатуре «ГПР» означает «горизонтальный».

Синтаксис

Аргументы функции ГПР описаны ниже.

  • Искомое_значение — обязательный аргумент. Значение, которое требуется найти в первой строке таблицы. «Искомое_значение» может быть значением, ссылкой или текстовой строкой.
  • Таблица — обязательный аргумент. Таблица, в которой производится поиск данных. Можно использовать ссылку на диапазон или имя диапазона.
    • Значения в первой строке аргумента «таблица» могут быть текстом, числами или логическими значениями.
    • Если аргумент «интервальный_просмотр» имеет значение ИСТИНА, то значения в первой строке аргумента «таблица» должны быть расположены в возрастающем порядке: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ГПР может выдать неправильный результат. Если аргумент «интервальный_просмотр» имеет значение ЛОЖЬ, таблица может быть не отсортирована.
    • В текстовых строках регистр букв не учитывается.
    • Значения сортируются слева направо по возрастанию. Дополнительные сведения см. в разделе Сортировка данных в диапазоне или таблице.

    Примечание

    • Если функция ГПР не может найти «искомое_значение» и аргумент «интервальный_просмотр» имеет значение ИСТИНА, используется наибольшее из значений, меньших, чем «искомое_значение».
    • Если значение аргумента «искомое_значение» меньше, чем наименьшее значение в первой строке аргумента «таблица», функция ГПР возвращает значение ошибки #Н/Д.
    • Если аргумент «интервальный_просмотр» имеет значение ЛОЖЬ и аргумент «искомое_значение» является текстом, в аргументе «искомое_значение» можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одному знаку; звездочка — любой последовательности знаков. Чтобы найти какой-либо из самих этих знаков, следует указать перед ним знак тильды (~).

    Пример

    Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

    Функции ВПР и ГПР в Excel с примерами их использования

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

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

    Синтаксис функций ВПР и ГПР

    Функции имеют 4 аргумента:

    1. ЧТО ищем – искомый параметр (цифры и/или текст) либо ссылка на ячейку с искомым значением;
    2. ГДЕ ищем – массив данных, где будет производиться поиск (для ВПР – поиск значения осуществляется в ПЕРВОМ столбце таблицы; для ГПР – в ПЕРВОЙ строке);
    3. НОМЕР столбца/строки – откуда именно возвращается соответствующее значение (1 – из первого столбца или первой строки, 2 – из второго и т.д.);
    4. ИНТЕРВАЛЬНЫЙ ПРОСМОТР – точное или приблизительное значение должна найти функция (ЛОЖЬ/0 – точное; ИСТИНА/1/не указано – приблизительное).

    ! Если значения в диапазоне отсортированы в возрастающем порядке (либо по алфавиту), мы указываем ИСТИНА/1. В противном случае – ЛОЖЬ/0.

    Как пользоваться функцией ВПР в Excel: примеры

    Для учебных целей возьмем таблицу с данными:

    Таблица с данными.

    Формула Описание Результат
    Функция ищет значение ячейки F5 в диапазоне А2:С10 и возвращает значение ячейки F5, найденное в 3 столбце, точное совпадение. Место для формулы.
    Нам нужно найти, продавались ли 04.08.15 бананы. Если продавались, в соответствующей ячейке появится слово «Найдено». Нет – «Не найдено». Место для функции.
    Если «бананы» сменить на «груши», результат будет «Найдено» Меняем бананы на груши.
    Когда функция ВПР не может найти значение, она выдает сообщение об ошибке #Н/Д. Чтобы этого избежать, используем функцию ЕСЛИОШИБКА. Значение вместо 0.
    Мы узнаем, были ли продажи 05.08.15
    Если необходимо осуществить поиск значения в другой книге Excel, то при заполнении аргумента «таблица» переходим в другую книгу и выделяем нужный диапазон с данными. Ссылка на список сотрудников.
    Мы захотели узнать, кто работал 8.06.15.
    Поиск приблизительного значения. Результат.
    1. Функция ВПР всегда ищет данные в крайнем левом столбце таблицы со значениями.
    2. Регистр не учитывается: маленькие и большие буквы для Excel одинаковы.
    3. Если искомое меньше, чем минимальное значение в массиве, программа выдаст ошибку #Н/Д.
    4. Если задать номер столбца 0, функция покажет #ЗНАЧ. Если третий аргумент больше числа столбцов в таблице – #ССЫЛКА.
    5. Чтобы при копировании сохранялся правильный массив, применяем абсолютные ссылки (клавиша F4).

    Как пользоваться функцией ГПР в Excel: примеры

    Для учебных целей возьмем такую табличку:

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

    Символы подстановки в функциях ВПР и ГПР

    Случается, пользователь не помнит точного названия. Задавая искомое значение, он может применить символы подстановки:

    • «?» — заменяет любой символ в текстовой или цифровой информации;
    • «*» — для замены любой последовательности символов.

    Таблица с именами.

    1. Найдем текст, который начинается или заканчивается определенным набором символов. Предположим, нам нужно отыскать название компании. Мы забыли его, но помним, что начинается с Kol. С задачей справится следующая формула: . Результат запроса Kol.
    2. Нам нужно отыскать название компании, которое заканчивается на — «uda». Поможет следующая формула: . Результат запроса uda.
    3. Найдем компанию, название которой начинается на «Ce» и заканчивается на –»sef». Формула ВПР будет выглядеть так: . Результат запроса sef.

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

    Как сравнить листы с помощью ВПР и ГПР

    У нас есть данные о продажах за январь и февраль. Эти таблицы необходимо сравнить с помощью формул ВПР и ГПР. Для наглядности мы пока поместим их на один лист. Но будем работать в условиях, когда диапазоны находятся на разных листах.

    Две таблицы для сравнения.

    Как сравнить листы с помощью ВПР в Excel?

    Решим проблему 1 : сравним наименования товаров в январе и феврале. Так как в феврале их больше, вводить формулу будем на листе «Февраль».

    Проверка на наличие значений.

    Решим проблему 2 : сравним продажи по позициям в январе и феврале. Используем следующую формулу:

    Сравнение продаж.

    Как сравнить листы с помощью ГПР в Excel?

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

    Сравнение таблиц по горизонтали.

    Задача – сравнить продажи по позициям за январь и февраль.

    Создаем новый лист «Сравнение». Это не обязательное условие. Сопоставлять данные и отображать разницу можно на любом листе («Январь» или «Февраль»).

    Результат горизонтального сравнения.

    Проанализируем части формулы:

    «Половина» до знака «-»:

    . Искомое значение – первая ячейка в таблице для сравнения. Анализируемый диапазон – таблица с продажами за февраль. Функция ГПР «берет» данные из 2 строки в «точном» воспроизведении.

    . Все то же самое. Кроме диапазона. Здесь берется таблица с продажами за январь.

    Когда мы вводим формулу, Excel подсказывает, какой сейчас аргумент нужно ввести.

    • Excel Formula Examples
    • Создать таблицу
    • Форматирование
    • Функции Excel
    • Формулы и диапазоны
    • Фильтр и сортировка
    • Диаграммы и графики
    • Сводные таблицы
    • Печать документов
    • Базы данных и XML
    • Возможности Excel
    • Настройки параметры
    • Уроки Excel
    • Макросы VBA
    • Скачать примеры
    Читать:
    Как узнать через сколько

Похожие записи

miui biometric как убрать значок

admin

Как включить 4к на телевизоре

admin

Как работать в 1с фреш

admin