Как гпр в excel для чайников
Обновлено: 06.07.2024
10-ый день марафона 30 функций Excel за 30 дней мы посвятим изучению функции HLOOKUP (ГПР). Эта функция очень похожа на VLOOKUP (ВПР), только она работает с элементами горизонтального списка.
Несчастная функция HLOOKUP (ГПР) не так популярна, как её сестра, поскольку в большинстве случаев данные в таблицах расположены вертикально. Вспомните, когда в последний раз Вы хотели выполнить поиск по строке? А вернуть значение из того же столбца, но расположенное в одной из строк ниже?
Как бы там ни было, давайте подарим функции HLOOKUP (ГПР) заслуженный момент славы и посмотрим внимательно на информацию об этой функции, а также примеры её использования. Помните, если у Вас есть интересные идеи или примеры, пожалуйста, делитесь ими в комментариях.
Функция 10: HLOOKUP (ГПР)
Функция HLOOKUP (ГПР) ищет значение в первой строке таблицы и возвращает другое значение из того же столбца таблицы.
Как можно использовать функцию HLOOKUP (ГПР)?
Поскольку функция HLOOKUP (ГПР) может найти точное или приближенное значение в строке, то она сможет:
- Найти итоги продаж по выбранному региону.
- Найти показатель, актуальный для выбранной даты.
Синтаксис HLOOKUP (ГПР)
Функция HLOOKUP (ГПР) имеет следующий синтаксис:
Ловушки HLOOKUP (ГПР)
Как и VLOOKUP (ВПР), функция HLOOKUP (ГПР) может работать медленно, особенно когда выполняет поиск точного совпадения текстовой строки в несортированной таблице. По мере возможности, используйте приблизительный поиск в таблице, отсортированной по первой строке по возрастанию. Вы можете сначала применить функцию MATCH (ПОИСКПОЗ) или COUNTIF (СЧЁТЕСЛИ), чтобы убедиться, что искомое значение вообще существует в первой строке.
Другие функции, такие как INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ), могут быть также использованы для извлечения значений из таблицы, и они более эффективны. Мы рассмотрим их позже в рамках нашего марафона и увидим, насколько мощны и гибки они могут быть.
Пример 1: Найти значения продаж для выбранного региона
Еще раз напомню, что функция HLOOKUP (ГПР) ищет значение только в верхней строке таблицы. В этом примере мы найдём итоги продаж для выбранного региона. Нам важно получить правильное значение, поэтому используем такие настройки:
- Имя региона введено в ячейке B7.
- Таблица поиска по региону имеет две строки и занимает диапазон C2:F3.
- Итоги продаж находятся в строке 2 нашей таблицы.
- Последний аргумент имеет значение FALSE (ЛОЖЬ), чтобы найти точное совпадение при поиске.
Формула в ячейке C7 такая:
Пример 2: Найти показатель для выбранной даты
Обычно при использовании функции HLOOKUP (ГПР) требуется точное совпадение, но иногда приблизительное совпадение подходит больше. Например, если показатели меняются в начале каждого квартала, а в качестве заголовков столбцов используются первые дни этих кварталов (см. рисунок ниже). В таком случае, с помощью функции HLOOKUP (ГПР) и приблизительного соответствия, Вы найдёте показатель, который актуален для заданной даты. В этом примере:
- Дата записана в ячейке C5.
- Таблица поиска показателя имеет две строки и расположена в диапазоне C2:F3.
- Таблица поиска отсортирована по строке с датами по возрастанию.
- Показатели записаны в строке 2 нашей таблицы.
- Последний аргумент функции имеет значение TRUE (ИСТИНА), чтобы искать приближенное совпадение.
Формула в ячейке D5 такая:
Совет: Попробуйте воспользоваться новой функцией ПРОСМОТРX , улучшенной версией функции ГГ ПРОСМОТР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее использовать предшественницу.
В этой статье описаны синтаксис формулы и использование функции ГПР в Microsoft Excel.
Описание
Выполняет поиск значения в первой строке таблицы или массив значений и возвращает значение, находящееся в том же столбце в заданной строке таблицы или массива. Функция ГПР используется, когда сравниваемые значения расположены в первой строке таблицы данных, а возвращаемые — на несколько строк ниже. Если сравниваемые значения находятся в столбце слева от искомых данных, используйте функцию ВПР.
Буква Г в аббревиатуре "ГПР" означает "горизонтальный".
Синтаксис
Аргументы функции ГПР описаны ниже.
Искомое_значение — обязательный аргумент. Значение, которое требуется найти в первой строке таблицы. "Искомое_значение" может быть значением, ссылкой или текстовой строкой.
Таблица — обязательный аргумент. Таблица, в которой производится поиск данных. Можно использовать ссылку на диапазон или имя диапазона.
Значения в первой строке аргумента "таблица" могут быть текстом, числами или логическими значениями.
Если аргумент "интервальный_просмотр" имеет значение ИСТИНА, то значения в первой строке аргумента "таблица" должны быть расположены в возрастающем порядке: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ГПР может выдать неправильный результат. Если аргумент "интервальный_просмотр" имеет значение ЛОЖЬ, таблица может быть не отсортирована.
В текстовых строках регистр букв не учитывается.
Значения сортируются слева направо по возрастанию. Дополнительные сведения см. в разделе Сортировка данных в диапазоне или таблице.
Примечание
Если функция ГПР не может найти "искомое_значение" и аргумент "интервальный_просмотр" имеет значение ИСТИНА, используется наибольшее из значений, меньших, чем "искомое_значение".
Если аргумент "интервальный_просмотр" имеет значение ЛОЖЬ и аргумент "искомое_значение" является текстом, в аргументе "искомое_значение" можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одному знаку; звездочка — любой последовательности знаков. Чтобы найти какой-либо из самих этих знаков, следует указать перед ним знак тильды (
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Совет: Попробуйте использовать новую функцию ПРОСМОТРX , улучшенную версию функции ВЛОП, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее в использовании, чем предшественницу.
Если вам нужно найти что-то в таблице или диапазоне по строкам, используйте В ПРОСМОТР. Например, можно найти цену автомобильной части по номеру части или имя сотрудника на основе его ИД.
Самая простая функция ВПР означает следующее:
=В.ПРОСМОТР(то, что вы хотите найти, где ее искать; номер столбца в диапазоне, содержащего возвращаемую величину, возвращает приблизительное или точное совпадение, обозначенные как 1/ИСТИНА или 0/ЛОЖЬ).
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
Используйте функцию ВПР для поиска значения в таблице.
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
=ВLOOKUP(A2;'Сведения о клиенте'! A:F;3;ЛОЖЬ)
Имя аргумента
искомое_значение (обязательный)
Значение для поиска. Иного значения должно быть в первом столбце диапазона ячеек, который указан в table_array.
Например, если массив таблицы охватывает ячейки B2:D7, lookup_value должны быть в столбце B.
Искомое_значение может являться значением или ссылкой на ячейку.
таблица (обязательный)
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Можно использовать именуемый диапазон или таблицу, а не ссылки на ячейки, а имена в аргументе.
Первый столбец в диапазоне ячеев должен содержать lookup_value . Диапазон ячеев также должен включать возвращаемую величину, которая требуется найти.
номер_столбца (обязательный)
Номер столбца (начиная с 1 в левом большинстве столбцов table_array),содержащий возвращаемую величину.
интервальный_просмотр (необязательный)
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
Приблизительное совпадение: 1/ИСТИНА предполагает, что первый столбец таблицы отсортжен в алфавитном или числовом порядке, а затем будет выполнять поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Например, =ВКП(90;A1:B100;2;ИСТИНА).
Точное совпадение: 0/ЛОЖЬ ищет точное значение в первом столбце. Например, =ВКП("Кузнецов";A1:B100;2;ЛОЖЬ).
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
Значение, которое вам нужно найти, то есть искомое значение.
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона указать B2:D11, следует посчитать B первым столбцом, C — вторым и так далее.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВLOOKUP(искомые значения, диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащий возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Объединение данных из нескольких таблиц на один таблицу с помощью ВЛКПФункцию ВЛОП можно использовать для объединения нескольких таблиц в одну, если одна из таблиц имеет поля, общие для всех остальных. Это особенно полезно, если вам нужно поделиться книгой с людьми, у которых есть более старые версии Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных, путем объединения источников в одну таблицу и изменения источника данных функции данных в новую таблицу, функцию данных можно использовать в более старых версиях Excel (при условии, что функция данных поддерживается более старой версией).
Здесь столбцы A–F и H имеют значения или формулы, которые используют только значения на этом сайте, а остальные столбцы используют В ПРОСМОТР и значения столбцов A (код клиента) и B (Доверенность) для получения данных из других таблиц.
Скопируйте таблицу с общими полями на новый и придать ей имя.
Чтобы открыть диалоговое окно Управление отношениями, > в > управления отношениями нажмите кнопку Data > Data Tools (Управление отношениями).
Для каждой из указанных связей обратите внимание на следующее:
Поле, которое связывает таблицы (в скобки в диалоговом окне). Это первый lookup_value для формулы ВЛКП.
Имя связанной таблицы подсмотра. Это первый table_array в формуле ВЛИО.
Поле (столбец) в связанной таблице подытовки с данными, которые должны быть в новом столбце. Эта информация не отображается в диалоговом оке Управление связями. Чтобы узнать, какое поле нужно извлечь, необходимо посмотреть в связанной таблице подыска. Обратите внимание на номер столбца (A=1) — это col_index_num формуле.
Чтобы добавить поле в новую таблицу, введите формулу в первом пустом столбце СРОТ, используя сведения, собранные на шаге 3.
В нашем примере в столбце G для получения данных "Ставка выставления счета" из четвертого столбца(col_index_num = 4) из таблицы "Доверенности" используется столбец "Доверенность" lookup_value(table_array) с формулой =ВЛП([@Attorney];tbl_Attorneys;4;ЛОЖЬ).
В формуле также можно использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =ВЛВП(A2;'Поверенные'! A:D,4;ЛОЖЬ).
Продолжайте добавлять поля, пока не будут добавлены все необходимые поля. Если вы пытаетесь подготовить книгу, содержащую функции данных, которые используют несколько таблиц, измените источник данных функции на новую таблицу.
Возможная причина
Неправильное возвращаемое значение
Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия.
Используйте абсолютные ссылки в аргументе интервальный_просмотр
Использование абсолютных ссылок позволяет заполнить формулу так, чтобы она всегда отображала один и тот же диапазон точных подстановок.
Не сохраняйте числовые значения или значения дат как текст.
При поиске числовых значений или значений дат убедитесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. Иначе функция ВПР может вернуть неправильное или непредвиденное значение.
Сортируйте первый столбец
Если для аргумента интервальный_просмотр указано значение ИСТИНА, прежде чем использовать функцию ВПР, отсортируйте первый столбец таблицы.
Используйте подстановочные знаки
Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (
) перед искомым символом.
Например, =ВЛП("Шрифт?";B2:E7;2;ЛОЖЬ) будет искать все экземпляры Шрифтаны с последней буквой, которая может отличаться.
Убедитесь, что данные не содержат ошибочных символов.
При поиске текстовых значений в первом столбце убедитесь, что данные в нем не содержат начальных или конечных пробелов, недопустимых прямых (' или ") и изогнутых (‘ или “) кавычек либо непечатаемых символов. В этих случаях функция ВПР может возвращать непредвиденное значение.
Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Функции упорядочены по категориям в зависимости от функциональной области. Щелкните категорию, чтобы просмотреть относящиеся к ней функции. Вы также можете найти функцию, нажав CTRL+F и введя первые несколько букв ее названия или слово из описания. Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.
Ниже перечислены десять функций, которыми больше всего интересуются пользователи.
Эта функция используется для суммирования значений в ячейках.
Эта функция возвращает разные значения в зависимости от того, соблюдается ли условие. Вот видео об использовании функции ЕСЛИ.
Используйте эту функцию, когда нужно взять определенную строку или столбец и найти значение, находящееся в той же позиции во второй строке или столбце.
Эта функция используется для поиска данных в таблице или диапазоне по строкам. Например, можно найти фамилию сотрудника по его номеру или его номер телефона по фамилии (как в телефонной книге). Посмотрите это видео об использовании функции ВПР.
Данная функция применяется для поиска элемента в диапазоне ячеек с последующим выводом относительной позиции этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 7 и 38, то формула =MATCH(7,A1:A3,0) возвращает значение 2, поскольку элемент 7 является вторым в диапазоне.
Эта функция позволяет выбрать одно значение из списка, в котором может быть до 254 значений. Например, если первые семь значений — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента "номер_индекса".
Эта функция возвращает порядковый номер определенной даты. Эта функция особенно полезна в ситуациях, когда значения года, месяца и дня возвращаются формулами или ссылками на ячейки. Предположим, у вас есть лист с датами в формате, который Excel не распознает, например ГГГГММДД.
Функция РАЗНДАТ вычисляет количество дней, месяцев или лет между двумя датами.
Эта функция возвращает число дней между двумя датами.
Функции НАЙТИ и НАЙТИБ находят вхождение одной текстовой строки в другую. Они возвращают начальную позицию первой текстовой строки относительно первого знака второй.
Эта функция возвращает значение или ссылку на него из таблицы или диапазона.
Эти функции в Excel 2010 и более поздних версиях были заменены новыми функциями с повышенной точностью и именами, которые лучше отражают их назначение. Их по-прежнему можно использовать для совместимости с более ранними версиями Excel, однако если обратная совместимость не является необходимым условием, рекомендуется перейти на новые разновидности этих функций. Дополнительные сведения о новых функциях см. в статьях Статистические функции (справочник) и Математические и тригонометрические функции (справочник).
Если вы используете Excel 2007, эти функции можно найти в категориях Статистические и Математические на вкладке Формулы.
Возвращает интегральную функцию бета-распределения.
Возвращает обратную интегральную функцию указанного бета-распределения.
Возвращает отдельное значение вероятности биномиального распределения.
Возвращает одностороннюю вероятность распределения хи-квадрат.
Возвращает обратное значение односторонней вероятности распределения хи-квадрат.
Возвращает тест на независимость.
Соединяет несколько текстовых строк в одну строку.
Возвращает доверительный интервал для среднего значения по генеральной совокупности.
Возвращает ковариацию, среднее произведений парных отклонений.
Возвращает наименьшее значение, для которого интегральное биномиальное распределение меньше заданного значения или равно ему.
Возвращает экспоненциальное распределение.
Возвращает F-распределение вероятности.
Возвращает обратное значение для F-распределения вероятности.
Округляет число до ближайшего меньшего по модулю значения.
Вычисляет, или прогнозирует, будущее значение по существующим значениям.
Возвращает результат F-теста.
Возвращает обратное значение интегрального гамма-распределения.
Возвращает гипергеометрическое распределение.
Возвращает обратное значение интегрального логарифмического нормального распределения.
Возвращает интегральное логарифмическое нормальное распределение.
Возвращает значение моды набора данных.
Возвращает отрицательное биномиальное распределение.
Возвращает нормальное интегральное распределение.
Возвращает обратное значение нормального интегрального распределения.
Возвращает стандартное нормальное интегральное распределение.
Возвращает обратное значение стандартного нормального интегрального распределения.
Возвращает k-ю процентиль для значений диапазона.
Возвращает процентную норму значения в наборе данных.
Возвращает распределение Пуассона.
Возвращает квартиль набора данных.
Возвращает ранг числа в списке чисел.
Оценивает стандартное отклонение по выборке.
Вычисляет стандартное отклонение по генеральной совокупности.
Возвращает t-распределение Стьюдента.
Возвращает обратное t-распределение Стьюдента.
Возвращает вероятность, соответствующую проверке по критерию Стьюдента.
Оценивает дисперсию по выборке.
Вычисляет дисперсию по генеральной совокупности.
Возвращает распределение Вейбулла.
Возвращает одностороннее P-значение z-теста.
Возвращает свойство ключевого показателя эффективности (КПЭ) и отображает его имя в ячейке. КПЭ представляет собой количественную величину, такую как ежемесячная валовая прибыль или ежеквартальная текучесть кадров, используемой для контроля эффективности работы организации.
Возвращает элемент или кортеж из куба. Используется для проверки существования элемента или кортежа в кубе.
Возвращает значение свойства элемента из куба. Используется для подтверждения того, что имя элемента внутри куба существует, и для возвращения определенного свойства для этого элемента.
Возвращает n-й, или ранжированный, элемент в множестве. Используется для возвращения одного или нескольких элементов в множестве, например лучшего продавца или 10 лучших студентов.
Определяет вычисленное множество элементов или кортежей путем пересылки установленного выражения в куб на сервере, который формирует множество, а затем возвращает его в Microsoft Office Excel.
Читайте также: