Гпр коп показатели бизнеса

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

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

Например, в таблице с полями «Имя» и «Дата рождения» необходимо получить значение даты рождения для сотрудника, запись о котором является третьей сверху. В этом случае удобно использовать следующую функцию: =ГПР(«Дата рождения»;A1:B10;4), где «Дата рождения» – наименование столбца таблицы, в котором будет выполнен поиск, A1:B10 – диапазон ячеек, в котором расположена таблица, 4 – номер строки, в которой содержится возвращаемое значение (поскольку таблица содержит шапку, номер строки равен номеру искомой записи +1.

КЛЮЧЕВЫЕ ФИНАНСОВЫЕ ПОКАЗАТЕЛИ БИЗНЕСА | Панель приборов предпринимателя

Пошаговые примеры работы функции ГПР в Excel

Пример 1. В таблице содержатся данные о клиента и их контактных номерах телефонов. Определить номер телефона клиента, id записи которого имеет значение 5.

Вид таблицы данных:

Для расчета используем формулу:

• F1 – ячейка, содержащая название поля таблицы;
• A1:C11 – диапазон ячеек, в которых содержится исходная таблица;
• E2+1 – номер строки с возвращаемым значением (для – шестая строка, поскольку первая строка используется под шапку таблицы).

В ячейке F2 автоматически выводится значение соответствующие номеру id в исходной таблице.

ГПР для выборки по нескольких условиях в Excel

Пример 2. На основе таблицы из первого примера создать компактное представление таблицы, в которой по номеру id можно получить записи, хранящиеся в столбцах «Клиент» и «Телефон».

Создадим заготовку таблицы:

Для удобного использования в ячейке E2 создадим выпадающий список. Для этого выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

В появившемся окне «Проверка вводимых значений» выберите из секции «Тип данных:» в выпадающем меню опцию «Список». А в поле «Источник:» укажите адрес ссылки на диапазон исходных ячеек первого столбца таблицы =$A$2:$A$11, как показано выше на рисунке.

Для выбора клиента используем следующую формулу в ячейке F2:

Какими показателями можно достичь роста в бизнесе. Опережающие и запаздывающие показатели.

Для выбора номера телефона используем следующую формулу (с учетом возможного отсутствия записи) в ячейке G2:

Функция ЕСЛИ выполняет проверку возвращаемого значения. Если искомая ячейка не содержит данных, будет возвращена строка «Не указан».

Интерактивный отчет для анализа прибыли и убытков в Excel

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

Вид таблиц данных с выпадающим списком в ячейке E2 (как сделать выпадающий список смотрите в примере выше):

В ячейку F2 запишем следующую формулу:

Функция ABS возвращает абсолютное число, равное разнице возвращаемых результатов функций ГПР.

В ячейке G2 запишем формулу:

Функция ЕСЛИ сравнивает возвращаемые функциями ГПР значения и возвращает один из вариантов текстовых строк.

Особенности использования функции ГПР в Excel

Функция имеет следующую синтаксическую запись:

=ГПР( искомое_значение;таблица;номер_строки; [интервальный_просмотр])

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

1. В качестве аргумента номер_строки передано число
2. Передано число, превышающее значение номера последней записи (строки) в таблице или массиве. Возвращаемое значение – код ошибки #ССЫЛ!.

• [интервальный_просмотр] – необязательный для заполнения аргумент, принимающий логические значения:

1. ИСТИНА – поиск ближайшего меньшего значения в случае, если точное совпадение с искомым значением отсутствует. Используется по умолчанию, если аргумент явно не указан.
2. ЛОЖЬ – поиск точного совпадения с искомым значением. Возврат кода ошибки #Н/Д, если точного соответствия нет.

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

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

Источник: exceltable.com

ГПР или Горизонтальный поиск в Эксель

Как использовать функцию Горизонтального поиска / ГПР (HLOOKUP) в Excel

Функция ГПР (HLOOKUP) в Excel — это мощный инструмент для быстрого поиска данных из таблицы или набора данных. Он расшифровывается как “Горизонтальный поиск” и выполняет поиск по горизонтали по строкам, чтобы найти определенный элемент данных. Если вы имеете дело с большим или сложным набором данных, функция ГПР (HLOOKUP) может сэкономить вам время за счет быстрого поиска данных в огромном массиве строк и столбцов. Благодаря возможности поиска, индексации и извлечения соответствующих элементов данных это, как правило, проще, быстрее и точнее, чем поиск вручную.

Синтаксис для функции ГПР (HLOOKUP) следующий: ГПР(значение, диапазон, номер строки, совпадение)
• Значение: это значение, которое вы хотите найти из набора данных.
• Диапазон: Это диапазон ячеек, в которых вы хотите выполнить поиск по горизонтали.
• Номер строки: это номер строки в диапазоне, который содержит возвращаемое значение.
• Совпадение: Это необязательный аргумент, который указывает, хотите ли вы получить точное совпадение (ИСТИНА) или приблизительное совпадение (ЛОЖЬ). Значение по умолчанию равно ИСТИНА.

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

В приведенном ниже примере Значение — это имя клиента, которое является “Джон Смит”. Диапазон — это диапазон ячеек, содержащих имена клиентов и данные о продажах (A2:C7). Номер строки равен 2, что указывает на то, что мы хотим вернуть значение из второй строки Диапазона, которая является строкой, содержащей данные о продажах. Поскольку мы хотим получить точное совпадение, аргументу Совпадение присваивается значение ИСТИНА.
Когда вы введете эту формулу в ячейку, она вернет показатель продаж для Джон Смит, который составляет 25 000 долларов.
Формула:
=ГПР(«Джон Смит»,A2:C7,2,TRUE)
Результат: 25000

Одна из замечательных особенностей функции ГПР (HLOOKUP) заключается в том, что вы можете использовать подстановочные знаки для поиска нескольких элементов в рамках одного поиска. Чтобы сделать это, вы бы использовали знак вопроса (?), за которым следует звездочка (*) после вашего Значения.

Например, если бы вы хотели вернуть данные о продажах всех клиентов, фамилия которых “Смит”, формула выглядела бы следующим образом:
Формула:
=ГПР(«*Смит»,A2:C7,2,ИСТИНА)
Это вернет данные о продажах всех клиентов, чья фамилия Смит, включая Джона Смита, Джейн Смит и так далее.

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

Источник: www.bigroi.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: lumpics.ru