Меню

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



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

Svodnie tablici Как получить данные со сводной таблицы Здравствуйте друзья!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Как отключить ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в Excel

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

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

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(“Сумма по полю Сумма товара”;$A$3;”Продавец”;”Лабытин”)

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

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

Читайте также:  СПРАВОЧНИК ПО ПРОЕКТИРОВАНИЮ И БУРЕНИЮ СКВАЖИН НА ВОДУ СССР 1983г резидент Белецкий А С

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

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

Для отключения функции выполните следующие действия:

  • Нажмите левой клавишей мыши на любой ячейке внутри сводной таблицы;
  • Перейдите на вкладку “Анализ” на панели инструментов;
  • Нажмите на иконку выпадающего списка рядом с кнопкой “Сводная таблица” => пункт “Параметры”;
  • В выпадающем списке уберите галочку напротив надписи “Создать GETPIVOTDATA”.

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

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

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

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

Источник

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

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

В этом примере =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.(«Продажи»; 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.

Источник

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

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

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

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

Читайте также:  Арифметика кондитера как рассчитать вес торта

Пример 1.

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

Создадим сводную таблицу.

Для получения искомых данных можно вручную ввести следующую функцию:

  • «Количество» – поле сводной таблицы, данные из которого требуется получить;
  • A4:C15 – диапазон ячеек, в которых находится сводная таблица;
  • «Наименование»;»Ноутбук»;»Фирма»;»Samsung» – характеристика получаемых данных, на основании которой производится поиск требуемых данных.

получение искомых данных.

Как видно, результат совпадает со значением, хранящимся в сводной таблице. Для получения значения поля «Сумма» воспользуемся более удобным способом получения данных, когда рассматриваемая функция генерируется автоматически. Для этого выделим ячейку G8, вставим символ «=» и выделим ячейку B7:

Данная формула была сгенерирована автоматически. Полученный результат:

получение значения поля Сумма.

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

Выборка данных из сводной таблицы с помощью формулы Excel

Пример 2. На основании данных из первого примера необходимо определить, насколько количество привезенных на склад телевизоров Samsung превышает количество телевизоров LG, а также разницу их общей стоимости.

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

Формула 1 для разницы количества:

Формула 1.

Формула 2 для разницы общей суммы:

Формула 2.

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

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

Пример 3. В таблицу Excel выгружены статистические данные сайта в виде таблицы с полями «День», «Логин», «Страна» и «Время активности». Определить среднее время активности на сайте за исследуемый период для пользователей из каждой страны, вычислить наибольшее среди полученных значений.

Пример 3.

Создадим сводную таблицу на новом листе и добавим поля для отображения среднего значения времени активности на сайте:

Создадим сводную таблицу на новом листе.

Произведем расчет для пользователя из GB (Великобритания):

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

расчет для пользователя из 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 сгенерирует функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ автоматически.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник