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

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

Функция GetPivotData Возвращает видимые данные из сводной таблицы.

В этом примере = GETPIVOTDATA (“продажи”; a3) возвращает общую сумму продаж из сводной таблицы.

Синтаксис

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

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

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

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

field1, Item1, поле2, элем2.

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

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

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

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

Вычисляемые поля, элементы и пользовательские вычисления можно включить в расчеты в GETPIVOTDATA.

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

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

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

Если аргумент pivot_table не является диапазоном, в котором найдена Сводная таблица, функция GETPIVOTDATA возвращает #REF!.

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

Примеры

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник: support.office.com

Exceltip

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Расчет процентов по кредиту в excel таблица

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

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

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

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

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

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

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

Читайте также:  Как скопировать таблицу из эксель в эксель без изменений

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

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

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

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

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

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

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

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

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

Примеры работы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в 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 пар имен полей и элементов. Имена элементов необходимо заключать в кавычки (исключением являются данные числового типа и даты).
  1. В качестве аргумента сводная_таблица может быть передан диапазон ячеек, который включает сразу несколько сводных таблиц. В этом случае функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ будет извлекать данные из созданной последней таблицы.
  2. Если аргумент сводная_таблица принимает диапазон пустых ячеек, рассматриваемая функция вернет код ошибки #REF!.
  3. Пара поле1; элем1, ссылающаяся на одну и ту же ячейку, вернут данные из данной ячейки, которые могут быть любого типа, включая код ошибки.
  4. Код ошибки #ССЫЛКА! Будет возвращен в случае, если аргументы функции указывают на невидимое поле или содержат фильтр, не отображающий данные согласно установленным им условиям.
  5. Для корректного отображения данных в формате Время и Дата необходимо выполнять прямое преобразование (использовать функции ДАТА, ДАТАЗНАЧ и ВРЕМЯ).
  1. Сводные таблицы используются для создания удобочитаемого отчета на основе данных из имеющейся громоздкой таблицы с большим количеством полей данных.
  2. В Excel реализован визуальный интерфейс создания сводных таблиц, который делает данный процесс простым и наглядным. Однако алгоритм форматирования таких таблиц не является достаточно гибким, поэтому зачастую не удается достичь ожидаемого результата.
  3. Один из специалистов Microsoft предложил новый метод, согласно которому созданная сводная таблица является не окончательным действием, а лишь промежуточным этапом при создании отчетов. Требуется самостоятельно создать оболочку итогового отчета, которая затем будет заполнена данными из сводной таблицы с использованием рассматриваемой функции. При этом сводная таблица может иметь примитивный вид, не требует форматирования и может находиться на скрытом листе в качестве невидимой базы данных.
  4. При необходимости можно отключить функционал автоматического генерирования рассматриваемой функции. Для этого в параметрах сводных таблиц необходимо снять флажок перед «Создать GetPivotData».
  5. Синтаксис функции достаточно сложный, поэтому чтобы упростить работу с ней, можно выполнить следующие действия:
  • выделить пустую ячейку и ввести символ «=»;
  • выделить поле данных с требуемой информацией в сводной таблице;
  • функция с требуемыми аргументами сгенерируется автоматически.
Читайте также:  Excel много весит файл

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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