Excel формула получить данные сводной таблицы

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

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

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

Отключение создания GetPivotData

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

Теперь, при ссылке на ячейку, находящуюся в сводной таблице, Excel будет генерировать адрес ячейки.

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

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

Использование ссылок на поле сводной таблицы

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

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

Однако, если мы поменяем первый аргумент поле_данных на ссылку на ячейку E3, Excel вернет нам ошибку #ССЫЛКА!

Проблему решит простое добавление пустой строки (“”) в начало или конец ссылки на ячейку.

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

Использование дат в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Если вы используете даты в функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, у вас могут возникнуть проблемы, даже если дата отображается в сводной таблице. К примеру, аргументом формулы ниже является дата “21/04/2013”, и сводная таблица содержит поле с датами продаж. Однако формула в ячейке E4 возвращает ошибку.

Для предотвращения ошибок, связанных с датами, вы можете воспользоваться одним из следующих способов:

Сравнять форматы дат в формуле и сводной таблице
Использовать функцию ДАТАЗНАЧ
Использовать функцию ДАТА
Сослаться на ячейку с корректной датой

Сравнять форматы дат в формуле и сводной таблице.

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

В ячейке E4, в формуле использована дата формата “ДД.ММ.ГГГГ”, и в результате возвращена правильтая информация.

Использование функции ДАТАЗНАЧ

Вместо ручного ввода даты в формуле, можно добавить функцию ДАТАЗНАЧ для возврата даты.

В ячейке E4, дата введена с помощью функции ДАТАЗНАЧ, и Excel возвращает необходимую информацию.

Использование функции ДАТА

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

Ссылка на ячейку с датой

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

Как получить данные со сводной таблицы

Здравствуйте друзья!

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

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

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

Все это можно компенсировать другими возможностями и инструментами, которые мы и будем рассматривать в статье.

Получить данные со сводной таблицы возможно разными способами, это могут быть:

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

Читать еще: Функция суммеслимн в excel примеры

Основным инструментом, чтобы получить данные со сводной таблицы мы рассмотрим последнюю функцию. Эта функция позволит вам создать изящные решения по получению данных со сводных таблиц. Вы сможете с обыкновенной сводной таблицы, пусть и очень хорошей, получить все нужные данные и оформить ее в потрясающий по точности и удобству отчёт типа «Dashboard», который покажет вам новый уровень отчётности.

Что же собственно вы получите, данные обыкновенного вида: Преобразуются в сводную таблицу приблизительно такого вида: А теперь мы и приступим к тому, как же сделать наш отчёт изящным отчётом, который не стыдно будет показать руководителю любого ранга:

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

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

Первым ее аргументом стало поле «Сумма», это является именем извлекаемого поля;
Вторым аргументом у нас идёт (Свод!$C$1), это координаты ячейки, откуда мы вытягиваем данные, нужно водить обязательно, поскольку может быть много таблиц и данных, а программа обязана знать откуда взять данные.
Третьим аргументом, как и следующими, являются названия полей и их значение. В нашем случае это стал вид товара и период времени.

Теперь с полученными, таким образом, данными, возможно работать как с обыкновенными формулами и на ним не распространяются те жёсткие ограничения которые существуют для сводных таблиц, а значит можно легко строить графики, подводить итоги и проводить анализ. Если ваши исходные данные будут исправлены, дополнены или заменены, вам нужно лишь обновить сводную таблицу (поставьте курсор мыши на сводную таблицу и вызвав контекстное меню, выбрать пункт «Обновить»). И даже если исходные данные кардинально изменятся, вы всё равно получите правильные данные, обновятся и итоги, и графики, и анализ.

Без экономической свободы никакой другой свободы быть не может.
Маргарет Тэтчер

Сводные таблицы Excel

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

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

Для решения этой задачи выполните следующие действия.

Рис. 3.42. В этой сводной таблице выполняются всевозможные вычисления

Рис. 3.43. Для создания формулы введите знак равенства и щелкните на нужной ячейке сводной таблицы

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

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, «Доход» — получение значения поля Доход.
CaseSCudyPivot$A$3 — идентификация сводной таблицы путем выбора любой ячейки. Обратите внимание: по умолчанию Excel выбирает левую верхнюю ячейку сводной таблицы.
«Год», 2011 — последняя пара аргументов представляет собой имя поля и возвращаемое значение. Второй аргумент из этой пары жестко закодирован. Ключ к успешному применению функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ — параметризация аргументов, которая позволит изменять их в дальнейшем.

Отредактируйте формулу ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Измените значение параметра 2011 на указатель ячейки С$3. Вместо значения параметра Промышленное оборудование используйте указатель на ячейку $В4. Выберите пользовательский формат для данных в ячейках в виде #, ##0, К. Как показано на рис. 3.44, результат не изменился, просто была создана формула, которая может быть скопирована.

Читать еще: Excel формула впр

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

Рис. 3.45. Этот отчет выводит данные из промежуточной сводной таблицы

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

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

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA)

Извлекает совокупное значение из сводной таблицы в соответствии с выбранной строкой и столбцом.

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

GETPIVOTDATA(«SUM of number of units», «Pivot table»!А1)

GETPIVOTDATA(«AVERAGE of price per unit», A1, «division», «east»)

GETPIVOTDATA(«price per unit», B2, «division», «east», «subdivision», 2)

GETPIVOTDATA(A1, «Pivot table»!A1, «division», A2)

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

название_значения – название поля в сводной таблице, из которого необходимо извлечь данные.
- название_значения должно быть заключено в кавычки или являться ссылкой на любую ячейку c соответствующим текстом.
- Если имеется более одного значения, необходимо использовать точное название из сводной таблицы (например, «SUM of Sales»).
любая_ячейка_сводной_таблицы – ссылка на ячейку в целевой сводной таблице. Мы рекомендуем указывать ячейку, расположенную в одном из верхних углов.
исходный_столбец (необязательно) – название столбца в исходном наборе данных (не в сводной таблице).
элемент_сводной_таблицы (необязательно) – название строки или столбца в сводной таблице, соответствующее исходному столбцу.

Примечания

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

Статьи по теме

Набор данных для примеров, приведенных ниже.

Сводная таблица данных, указанных выше.

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

Примеры работы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в 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]; …)

Описание аргументов (первые два являются обязательными для заполнения):

поле_данных – аргумент, характеризующий имя поля данных, в котором содержатся данные для извлечения. Должен быть представлен текстовой строкой, например «Покупки».
сводная_таблица – аргумент, принимающий ссылку на всю сводную таблицу, либо на некоторый диапазон ячеек, содержащийся в ней. Необходим для определения сводной таблицы, данные из которой требуется получить.
[поле1; элем1; поле2; элем2]; … — необязательные аргументы, характеризующие данные, которые необходимо получить. Функция принимает до 126 пар имен полей и элементов. Имена элементов необходимо заключать в кавычки (исключением являются данные числового типа и даты).

В качестве аргумента сводная_таблица может быть передан диапазон ячеек, который включает сразу несколько сводных таблиц. В этом случае функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ будет извлекать данные из созданной последней таблицы.
Если аргумент сводная_таблица принимает диапазон пустых ячеек, рассматриваемая функция вернет код ошибки #REF!.
Пара поле1; элем1, ссылающаяся на одну и ту же ячейку, вернут данные из данной ячейки, которые могут быть любого типа, включая код ошибки.
Код ошибки #ССЫЛКА! Будет возвращен в случае, если аргументы функции указывают на невидимое поле или содержат фильтр, не отображающий данные согласно установленным им условиям.
Для корректного отображения данных в формате Время и Дата необходимо выполнять прямое преобразование (использовать функции ДАТА, ДАТАЗНАЧ и ВРЕМЯ).

Сводные таблицы используются для создания удобочитаемого отчета на основе данных из имеющейся громоздкой таблицы с большим количеством полей данных.
В Excel реализован визуальный интерфейс создания сводных таблиц, который делает данный процесс простым и наглядным. Однако алгоритм форматирования таких таблиц не является достаточно гибким, поэтому зачастую не удается достичь ожидаемого результата.
Один из специалистов Microsoft предложил новый метод, согласно которому созданная сводная таблица является не окончательным действием, а лишь промежуточным этапом при создании отчетов. Требуется самостоятельно создать оболочку итогового отчета, которая затем будет заполнена данными из сводной таблицы с использованием рассматриваемой функции. При этом сводная таблица может иметь примитивный вид, не требует форматирования и может находиться на скрытом листе в качестве невидимой базы данных.
При необходимости можно отключить функционал автоматического генерирования рассматриваемой функции. Для этого в параметрах сводных таблиц необходимо снять флажок перед «Создать GetPivotData».
Синтаксис функции достаточно сложный, поэтому чтобы упростить работу с ней, можно выполнить следующие действия:

выделить пустую ячейку и ввести символ «=»;
выделить поле данных с требуемой информацией в сводной таблице;
функция с требуемыми аргументами сгенерируется автоматически.