Getpivotdata в excel что это

Обновлено: 07.07.2024

Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает видимые данные из сводной таблицы.

В этом примере =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.("Продажи"; A3) возвращает общий объем продаж из сводной таблицы:

Синтаксис

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных; сводная_таблица; [поле1; элемент1; поле2; элемент2]; …)

Аргументы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ описаны ниже.

поле_данных

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

сводная_таблица

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

поле1, элемент1, поле2, элемент2.

От 1 до 126 пар имен полей и элементов, описывающих данные, которые необходимо извлечь. Они могут следовать друг за другом в произвольном порядке. Имена полей и элементов (кроме дат и чисел) должны быть заключены в кавычки.

В сводных таблицах OLAP элементы могут содержать исходное имя измерения, а также исходное имя элемента. Пара "поле-элемент" для сводной таблицы OLAP может выглядеть следующим образом:

Можно быстро ввести простую формулу ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, введя = (знак равенства) в ячейке, в которой должно быть возвращено значение, и затем щелкнув ячейку в сводной таблице, содержащей необходимые данные.

Вы можете отключить эту возможность. Для этого нужно выбрать любую ячейку в существующей сводной таблице, а затем перейти к вкладке Анализ сводной таблицы > Сводная таблица > Параметры > и снять флажок у параметра Генерировать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.

Вычисляемые поля или элементы и дополнительные вычисления могут включаться в расчеты для функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.

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

Если аргументы "поле" и "элемент" описывают одну ячейку, возвращается значение, содержащееся в этой ячейке, независимо от его типа (строка, число, ошибка или пустая ячейка).

Если аргумент "элемент" содержит дату, необходимо представить это значение как порядковый номер или воспользоваться функцией ДАТА, чтобы это значение не изменилось при открытии листа в системе с другими языковыми настройками. Например, элемент, ссылающийся на дату 5 марта 1999 г., можно ввести двумя способами: 36 224 или ДАТА(1999;3;5). Время можно задать в виде десятичных значений или с помощью функции ВРЕМЯ.

Примеры

Формулы в примере ниже представляют различные методы извлечения данных из сводной таблицы.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Предположим, что из вот такой базы данных по продажам:

. вы создали небольшую, но симпатичную сводную таблицу:

Но ваш руководитель хочет не совсем ее, а что-то похожее на:

То есть имеем несколько ощутимых трудностей:

Исходный внешний вид сводной таблицы не подходит - дизайн отчета должен соответствовать корпоративным стандартам (цвета, логотипы, спарклайны, стрелки и т.д.). "Дорабатывать напильником" дизайн сводной - долгий и мучительный процесс. И не факт, что красота не слетит после пересчета и обновления.
Из всей сводной для отчета вам нужны не все данные, а только конкретные модели Ford по Питеру - придется руками фильтровать.
Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зеленых ячейках, но среднее по месяцу в итогах - сводная так не умеет.
Полученные в сводной результаты - еще не конец, нам необходимо произвести с ними какие-то дополнительные вычисления: пересчитать выручку в тысячах, добавить прогноз на апрель, сравнить этот год с прошлым. Многое из перечисленного в сводных или невозможно в принципе (особенно для сводных на основе OLAP-кубов) или делается, но "через одно место" с помощью вычисляемых полей и объектов.
Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).

Сделаем на отдельном от сводной листе заготовку отчета:

Выделите первую ячейку зеленого диапазона, введите знак "равно" и щелкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки а-ля "морской бой" Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:

Давайте разберем ее подробно:

Первый ее аргумент ("Выручка") - это имя извлекаемого поля.
Второй (Лист1!$A$4) - это адрес первой ячейки сводной таблицы, откуда мы берем данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать из какой именно нужно вытащить число.
Все остальные аргументы начиная с третьего - это попарно название поля и его значение, т.е., в нашем случае, это имя модели (Наименование="Fiesta") и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто "январь".

А теперь самое интересное.

Аккуратно замените в формуле "Fiesta" на $С7, а единичку на D$5 и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зеленые ячейки.

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

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

Описание

Освободи Себя GETPIVOTDATA функция запрашивает сводную таблицу и возвращает данные на основе структуры сводной таблицы.

Синтаксис и аргументы

Синтаксис формулы

=GETPIVOTDATA ( data_field , pivot_table , [field1, item1] , . )

аргументы

Data_field: Необходимые. Имя поля заключено в двойные квоты и содержит данные, которые вы хотите вернуть.
Pivot_table: Необходимые. Ячейка или диапазон ячеек или именованный диапазон, используемый для определения, из какой сводной таблицы вы будете извлекать данные.
Field1, item1: Необязательный. От 1 до 126 пар имен полей и имен элементов, которые указывают на данные, которые вы хотите получить. Если имена полей и имена элементов не являются числовыми значениями, вам необходимо заключить их в кавычки.

Возвращаемое значение

Функция GETPIVOTDATA возвращает данные, хранящиеся в данной сводной таблице.

Замечания

1) Вычисляемые поля и пользовательские вычисления, такие как общая сумма и сумма каждого результата, также могут быть аргументами в GETPIVOTDATA функции.

Использование и примеры

Пример 1: базовое использование GETPIVOTDATA функция

1) Только первые два обязательных аргумента:

=GETPIVOTDATA("StoreNorth",$A$3)

Если есть только два аргумента в GETPIVOTDARA функция, она автоматически возвращает значения в поле Grand Total в зависимости от имени данного элемента. В моем примере он возвращает общее количество полей StoreNorth в сводной таблице, которое помещается в диапазон A3: E9 (начинается с ячейки A3).

2) С аргументом data_field, pivot_table, field1, item1

=GETPIVOTDATA("StoreNorth",$A$3,"Product","B")

Юг, север: data_field, поле, из которого вы хотите получить значение;

A3: pivot_table, первая ячейка сводной таблицы - это ячейка A3;

Продукт, B: filed_name, item_name, пара, которая описывает, какое значение вы хотите вернуть.

Пример 2: Как избежать значений ошибок, если аргументом является дата или время в GETPIVOTDATA функция

В этом случае вы можете

1) Используйте DATEVALUE функция

=GETPIVOTDATA("EachDate",A3,"Date Record",DATEVALUE("12/3/2018"))

2) Используйте DATE функция

=GETPIVOTDATA("EachDate",A3,"Date Record",DATE(2018,12,3))

3) Обратитесь к ячейке с датой

=GETPIVOTDATA("EachDate",A3,"Date Record",A12)

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

Как само название предлагает GETPIVOTDATA означает, Получить данные из сводной таблицы. Это своего рода функция поиска в сводной таблице. Он относится к категории «Поиск и ссылка». Эта функция помогает извлекать данные из указанных полей в сводной таблице. Сводная таблица - это инструмент анализа, который обобщает большой объем данных в удобочитаемом виде.

Getpivotdata может запрашивать сводную таблицу и извлекать конкретные данные на основе структуры таблицы вместо ссылок.

GETPIVOTDATA Формула в Excel

Формула для функции GETPIVOTDATA в Excel выглядит следующим образом:

Эта функция состоит из поля данных, сводной таблицы (Field1, Item1), (Field2, Item2), (Field3, Item3).

Прежде чем объяснить, синтаксис один за другим, давайте рассмотрим простой пример.

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

Поле данных: в приведенном выше примере поле данных означает, какую сумму заголовка вы ищете. В приведенном выше примере мы ищем итоговую сумму продаж. Это не необязательный аргумент, а обязательный аргумент.

Сводная таблица: это ссылка на ячейку вашего поля данных. В приведенном выше примере данные поданы, т. Е. Sales Amt находится в ячейке A3, поэтому принимает ссылку как A3.

(Field1, Item1): мы ищем общий итог продаж для региона (т. Е. (Field1)) на восток (т. Е. Item1).

Как использовать функцию GETPIVOTDATA в Excel?

Функция GETPIVOTDATA в Excel очень проста и удобна в использовании. Давайте рассмотрим работу функции GETPIVOTDATA в Excel на некоторых примерах.

Вы можете скачать этот шаблон Excel функции GETPIVOTDATA здесь - Шаблон Excel функции GETPIVOTDATA

GETPIVOTDATA в Excel, пример № 1

Если у вас есть Region в столбце 1, Project в столбце 2, Sales Person в столбце 3 и Sales Values в столбце4. Вам нужно получить общее количество мистера Санджу, используя Getpivotdata. Прежде чем мы применим функцию Getpivotdata, сначала нам нужно создать сводную таблицу для данных ниже. Идите вперед и примените функцию.

После применения сводной таблицы ваш стол должен выглядеть следующим образом.

Совет для профессионалов: есть два способа применения функции Getpivotdata в Excel.

Один из них - просто щелкнуть знак равенства (=) в любой ячейке (кроме ячейки сводной таблицы) и выбрать нужную ячейку в поле сводной таблицы.
Во-вторых, вручную введите формулу, как и другие формулы в Excel.

Тип 1: Нажмите на любую ячейку и выберите нужную ячейку результата в сводной таблице. Это даст вам значение 2, 16, 444.

Тип 2: введите знак равенства в любой ячейке и введите функцию Getpivotdata.

Теперь в разделе Data_Field введите « Sales Amt» . В разделе сводной таблицы введите « I1» (ссылочная ячейка, в которой находится ваша Sales Amt, в моем случае это I1). В разделе (Поле 1) введите « Продавец», а в разделе (Элемент 1) введите « Санджу ». Это даст вам значение 2, 16, 444.

GETPIVOTDATA в Excel, пример № 2 - получение промежуточных итогов сводной таблицы

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

Теперь необходимо получить значение г-на Раму для проекта 2.

= GETPIVOTDATA («Сумма продаж», $ 20, «Код проекта», «Проект2», «Сотрудник отдела продаж», «Раму»)

Data_Field: Sales Amt является полем данных.

Pivot_Table: ссылка на ячейку Sales Amt.

(Filed1) & (Item1): Код проекта заполнен, мы ищем, и в рамках кода проекта мы ищем проект 2.

(Filed2) & (Item2): Продавец подан, под этим мы ищем Раму.

Это означает, что мы ищем общий объем продаж г-на Раму для проекта 2.

Обратите внимание, что общая сумма продаж для г-на Раму составляет 3, 92, 051, но для проекта 2 - 3, 52, 519.

GETPIVOTDATA в Excel, пример № 3

Ниже приведены данные о месячных продажах компании XYZ. С помощью сводной таблицы найдите общую сумму продаж на дату 26-02-2018.

Поля и значения сводной таблицы

Примените сводную таблицу для вышеуказанных данных.
Данные строки должны быть Дата
Поле значения должно быть Sales Amt.

Сводная таблица будет выглядеть следующим образом.

Используя эту таблицу, узнайте о продаже 26 февраля 2018 года.

Чтобы получить правильный ответ при вводе даты ниже, используйте формулу Getpivotdata.

Продажа 26 февраля 2018 года - 643835.