Примеры работы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel предназначена для получения доступа к полям данных сводных таблиц и возвращает данные в соответствии с запросом (формируется на основе переданных в данную функцию аргументов).
Пример как использовать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel
Пример 1. В таблице Excel содержатся данные о поступлениях бытовой техники различного типа и от разных производителей на склад интернет-магазина по номеру дня. Создать сводную таблицу на основе существующей, получить данные о количестве полученных ноутбуках фирмы Samsung и их общей стоимости с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.
Создадим сводную таблицу на новом листе, которая категорирует данные по типу техники и фирме-производителе, и содержит данные о количестве поступивших товаров и их общей сумме:
Для получения искомых данных можно вручную ввести следующую функцию:
- «Количество» – поле сводной таблицы, данные из которого требуется получить;
- A4:C15 – диапазон ячеек, в которых находится сводная таблица;
- «Наименование»;»Ноутбук»;»Фирма»;»Samsung» – характеристика получаемых данных, на основании которой производится поиск требуемых данных.
Как видно, результат совпадает со значением, хранящимся в сводной таблице. Для получения значения поля «Сумма» воспользуемся более удобным способом получения данных, когда рассматриваемая функция генерируется автоматически. Для этого выделим ячейку G8, вставим символ «=» и выделим ячейку B7:
Данная формула была сгенерирована автоматически. Полученный результат:
С помощью данной формулы мы имеем возможность получить доступ к любым значениям полей сводной таблицы Excel.
Выборка данных из сводной таблицы с помощью формулы Excel
Пример 2. На основании данных из первого примера необходимо определить, насколько количество привезенных на склад телевизоров Samsung превышает количество телевизоров LG, а также разницу их общей стоимости.
При работе с исходной таблицей нужно было бы отыскать данные для каждого поступления требуемой техники, что потребовало бы огромные временные затраты, если таблица содержала, например, данные за последний год. Благодаря использованию сводной таблицы и рассматриваемой функции расчет сводится к простым формулам:
Формула 1 для разницы количества:
Формула 2 для разницы общей суммы:
К числу достоинств данной функции относится не только возможность получения результатов в любом удобном представлении. Все полученные значения находятся в динамической зависимости с исходной таблицей, то есть будут обновляться при внесении изменений в исходную таблицу.
Формула для анализа данных полученных из сводной таблицы Excel
Пример 3. В таблицу Excel выгружены статистические данные сайта в виде таблицы с полями «День», «Логин», «Страна» и «Время активности». Определить среднее время активности на сайте за исследуемый период для пользователей из каждой страны, вычислить наибольшее среди полученных значений.
Создадим сводную таблицу на новом листе и добавим поля для отображения среднего значения времени активности на сайте:
Произведем расчет для пользователя из GB (Великобритания):
Формула выглядит очень громоздкой, однако ее аргументы генерируются автоматически при выборе соответствующих ячеек. Итоговые результаты расчета:
Определим наибольшее значение с помощью соответствующей формулы:
Как видно, на сайте в среднем больше времени проводили пользователи из UA (Украины).
Особенности использования функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel
Рассматриваемая функция имеет следующий синтаксис:
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( поле_данных; сводная_таблица ; [поле1; элем1; поле2; элем2]; …)
Getting Data From A Pivot Table – Using Getpivotdata()
If you have a large pivot and want to create a report based on that pivot, it is time to revert to a function dedicated for it – GETPIVOTDATA().
GETPIVOTDATA() is function that can bring back the data from the pivot table, that was created using a list. It works like a lookup function, can have many more criteria’s for looking up for a value than a conventional VLOOKUP().
As we have been doing, we will use an example to see how we can use this formula and how we can create custom reports by using this formula:
GETPIVOTDATA Syntax:
The following illustration gives the idea of the formula’s syntax and the corresponding element in the pivot table. It should be noted that data_field is the field that is being summed or count and the name of field should be in double quotes, similarly all the fields and items needed to be in double quotes. The pivot_table refers to the left and top most cell of the pivot. For this case it is A4.
Example:
Let’s assume we want to populate the following table based on a pivot table data.
We will use data validation to populate the lists in the table and will try to fetch “Units”, based on the selection. We need to setup a GETPIVOTTABLE() function like this:
=GETPIVOTDATA("Total",Sheet4!A4,"Item",C2,"Location",C3)
The Syntax has already been explained in the preceding lines. We are looking for the sum of Total (Total Sales) for a Criteria in C2 for Item name and in C3 for the location.
By selecting the criteria, the result is displayed automatically in the Units cell. The formula slices the table for the given criteria and returns the corresponding result. We can improve the number of criteria to get more insight. Look at the following table:
In the above table, we have added another criterion to the column on right side – we have added the name of the sales representative to further breakup the data and we can keep rearranging data to get the desired results.
Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Предположим, что из вот такой базы данных по продажам:
. вы создали небольшую, но симпатичную сводную таблицу:
Но ваш руководитель хочет не совсем ее, а что-то похожее на:
То есть имеем несколько ощутимых трудностей:
- Исходный внешний вид сводной таблицы не подходит — дизайн отчета должен соответствовать корпоративным стандартам (цвета, логотипы, спарклайны, стрелки и т.д.). «Дорабатывать напильником» дизайн сводной — долгий и мучительный процесс. И не факт, что красота не слетит после пересчета и обновления.
- Из всей сводной для отчета вам нужны не все данные, а только конкретные модели Ford по Питеру — придется руками фильтровать.
- Стандартные итоги в сводной нам не подходят, т.к. нужны суммы по выручке в зеленых ячейках, но среднее по месяцу в итогах — сводная так не умеет.
- Полученные в сводной результаты — еще не конец, нам необходимо произвести с ними какие-то дополнительные вычисления: пересчитать выручку в тысячах, добавить прогноз на апрель, сравнить этот год с прошлым. Многое из перечисленного в сводных или невозможно в принципе (особенно для сводных на основе OLAP-кубов) или делается, но «через одно место» с помощью вычисляемых полей и объектов.
- Нужно построить по результатам хитрую диаграмму (обычные сводные диаграммы имеют много ограничений).
Сделаем на отдельном от сводной листе заготовку отчета:
Выделите первую ячейку зеленого диапазона, введите знак «равно» и щелкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки а-ля «морской бой» Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:
Давайте разберем ее подробно:
- Первый ее аргумент («Выручка») — это имя извлекаемого поля.
- Второй (Лист1!$A$4) — это адрес первой ячейки сводной таблицы, откуда мы берем данные. Этот параметр нужен, т.к. на листе может быть несколько сводных и Excel должен понимать из какой именно нужно вытащить число.
- Все остальные аргументы начиная с третьего — это попарно название поля и его значение, т.е., в нашем случае, это имя модели (Наименование=»Fiesta») и временной период (Дата=1). Поскольку в сводной была применена группировка дат по месяцам, то в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ мы получили не имя месяца, а его номер. Если бы в исходной базе данных был столбец не с датой, а с названием месяца, то группировка была бы не нужна и вместо единички был бы просто «январь».
А теперь самое интересное.
Аккуратно замените в формуле «Fiesta» на $С7, а единичку на D$5 и допишите в конце формулы деление на 1000, т.к. нам нужно отобразить данные в тысячах. Затем нажмите на Enter и протяните формулу на оставшиеся зеленые ячейки.
Теперь с данными в зеленых ячейках можно работать как с обычными формулами, а не как со сводной таблицей с ее жесткими ограничениями, т.е. дальше можно спокойно считать любые итоги, динамику, прогнозы, строить любую диаграмму и т.д.
Если исходные данные поменяются, то нужно будет лишь обновить сводную (правой кнопкой мыши — Обновить). Даже если завтра изменится количество моделей или, скажем, Fiesta будет уже не третьей по счету машиной в сводной, наша функция все равно ее найдет и правильно извлечет соответствующий ей результат для нашего отчета.
Getpivotdata в excel что это
Описание
Наблюдения и советы этой статьи мы подготовили на основании опыта команды GETPIVOTDATA функция запрашивает сводную таблицу и возвращает данные на основе структуры сводной таблицы.
Синтаксис и аргументы
Синтаксис формулы
| =GETPIVOTDATA ( data_field , pivot_table , [field1, item1] , . ) |
аргументы
- Data_field: Необходимые. Имя поля заключено в двойные квоты и содержит данные, которые вы хотите вернуть.
- Pivot_table: Необходимые. Ячейка или диапазон ячеек или именованный диапазон, используемый для определения, из какой сводной таблицы вы будете извлекать данные.
- Field1, item1: Необязательный. От 1 до 126 пар имен полей и имен элементов, которые указывают на данные, которые вы хотите получить. Если имена полей и имена элементов не являются числовыми значениями, вам необходимо заключить их в кавычки.
Возвращаемое значение
Функция GETPIVOTDATA возвращает данные, хранящиеся в данной сводной таблице.
Замечания
1) Вычисляемые поля и пользовательские вычисления, такие как общая сумма и сумма каждого результата, также могут быть аргументами в GETPIVOTDATA функции.
2) Если элемент содержит дату или время, возвращаемое значение может быть потеряно, если книгу переместить в другое место и отобразить как значение ошибки #REF !. Чтобы избежать этого случая, вы можете выразить дату или время как порядковый номер, например show 12/3/2018 как 43437.
3) Если аргумент pivot_table не является ячейкой или диапазоном, в котором находится сводная таблица, GETPIVOTDATA возвращает #REF !.
4) Если аргументы не видны в данной сводной таблице, 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 функция
Если аргументы в 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 % времени.