как узнать с какого листа сводная таблица берет данные
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает видимые данные из сводной таблицы.
В этом примере =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.(«Продажи»; 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 на другую таблицу или диапазон ячеок либо другой внешний источник данных. Щелкните Отчет сводной таблицы. На вкладке «Анализ» в группе «Данные» нажмите кнопку «Изменить источник данных» и выберите «Изменить источник данных».
Как отключить функцию Получить данные сводной таблицы?
Как добавить новые данные в сводную таблицу?
Добавление вычисляемого поля
Как открыть список полей сводной таблицы?
Щелкните сводную таблицу. На ленте откроется вкладка Работа со сводными таблицами. Если нужно отобразить список полей сводной таблицы, на вкладке Анализ или Параметры в группе Показать нажмите кнопку Список полей.
Как настроить параметры сводной таблицы?
Для добавления сводной таблицы необходимо:
Как обновить данные в сводной таблице?
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами. На вкладке Анализ выберите команду Обновить или нажмите ALT+F5. Совет: Чтобы одновременно обновить все стебли в книге, нажмите кнопку Анализ > Обновить все.
Как изменить диапазон данных в диаграмме?
Щелкните диаграмму правой кнопкой мыши и выберите команду выбрать данные. В поле Элементы легенды (ряды) щелкните ряд, который нужно изменить. Нажмите кнопку Изменить, внесите изменения и нажмите кнопку ОК.
Как расширить диапазон для сводной таблицы?
Вы можете изменить диапазон исходных данных сводной таблицы. Например, вы можете расширить исходные данные, включив в них большее количество строк данных.
…
Изменение исходных данных сводной таблицы
Как разорвать связь сводной таблицы?
Как сделать из сводной таблицы обычную?
В сводной таблице выберите необходимый вам диапазон и нажмите скопируйте его (воспользуйтесь сочетанием клавиш Ctrl+C). На новом листе или другом свободном месте текущего листа выделите ячейку отобразите диалоговое окно Специальная вставка.
Что такое Getpivotdata?
ТАБЛИЦЫ (GETPIVOTDATA), которая умеет извлекать нужные нам данные из сводной, чтобы использовать их в других таблицах или расчетах. Давайте разберем ее подробно: Первый ее аргумент («Выручка») — это имя извлекаемого поля.
Как добавить дополнительный столбец в сводную таблицу?
За одно узнаем как в сводной таблице добавить столбец.
Как открыть редактор сводной таблицы Гугл таблицы?
Откройте файл в Google Таблицах на компьютере. Нажмите на сводную таблицу. Дважды нажмите на интересующую вас ячейку. Откроется новый лист с исходными данными.
Как изменить данные в таблице Excel?
Перейдите в режим правки
Определение источников данных, используемых в модели данных книги
Если вы работаете с данными, добавленными в модель Excel данных, иногда вы можете не отслеживать, какие таблицы и источники данных были добавлены в модель данных.
Примечание: Убедитесь, что вы включили надстройку Power Pivot. Дополнительные сведения см. в том, как запустить надстройку Power Pivot для Excel.
Чтобы точно определить, какие данные есть в модели, выполните следующие простые действия:
В Excel щелкните Power Pivot > Управление, чтобы открыть окно Power Pivot.
Просмотрите вкладки в окне Power Pivot.
Каждая вкладка содержит таблицу в вашей модели. Столбцы в каждой таблице отображаются в качестве полей в списке полей сводной таблицы. Любой серый столбец скрыт от клиентских приложений.
Чтобы просмотреть происхождение таблицы, щелкните Свойства таблицы.
Если Свойства таблицы затемнены и вкладка содержит значок ссылки, указывающей на связанную таблицу, данные происходят из листа в таблице, а не из внешнего источника данных.
Для всех остальных типов данных в диалоговом окне Изменить свойства таблицы отображаются имя подключения и запрос, используемые для извлечения данных. Запомните или запишите имя подключения, а затем используйте диспетчер подключений в приложении Excel, чтобы определить сетевой ресурс и базу данных, используемые в подключении:
в Excel щелкните Данные > Подключения;
выберите подключение, используемое для заполнения таблицы в модели;
щелкните Свойства > Определение, чтобы просмотреть строку подключения.
Примечание: Модели данных были введены в Excel 2013. Вы можете использовать их для создания сводных таблиц, сводных диаграмм и отчетов Power View, визуализирующих данные из нескольких таблиц. Дополнительные сведения о моделях данных можно узнать в Excel.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Предположим, что из вот такой базы данных по продажам:
. вы создали небольшую, но симпатичную сводную таблицу:
Но ваш руководитель хочет не совсем ее, а что-то похожее на:
То есть имеем несколько ощутимых трудностей:
Сделаем на отдельном от сводной листе заготовку отчета:
Выделите первую ячейку зеленого диапазона, введите знак «равно» и щелкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки а-ля «морской бой» Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:
Давайте разберем ее подробно:
А теперь самое интересное.
Теперь с данными в зеленых ячейках можно работать как с обычными формулами, а не как со сводной таблицей с ее жесткими ограничениями, т.е. дальше можно спокойно считать любые итоги, динамику, прогнозы, строить любую диаграмму и т.д.
Как получить данные со сводной таблицы
Здравствуйте друзья!
Эту статью я хочу посвятить вопросу как получить данные со сводной таблицы, так как часто делая презентацию, анализ, расчёты или еще какую-то статистическую или просто информацию, нам нужно вынуть определенную информацию из массива данных. Для более опытных пользователей, которых интересует этот вопрос и посвящается статья, а вот, более неискушенным, пользователям стоит сначала ознакомится с вопросом как создать сводную таблицу, который раскрывается в одном из моих уроков.
Если вы уже знаете, как создать сводную таблицу, значит следующим шагом мы и будем рассматривать вопрос о том, как получить данные со сводной таблицы, в таком виде который вас устроит, рассмотрим инструменты и функции для получения данных, а также для чего же это надо если сводная таблица и так формирует и сводит данные. Не всё так идеально, да и вообще стремится к совершенству это хорошо, но у чистых сводных таблиц, особенно в версиях ниже MS Office 2003, есть некоторые недостатки такие как:
Все это можно компенсировать другими возможностями и инструментами, которые мы и будем рассматривать в статье.
Получить данные со сводной таблицы возможно разными способами, это могут быть:
Основным инструментом, чтобы получить данные со сводной таблицы мы рассмотрим последнюю функцию. Эта функция позволит вам создать изящные решения по получению данных со сводных таблиц. Вы сможете с обыкновенной сводной таблицы, пусть и очень хорошей, получить все нужные данные и оформить ее в потрясающий по точности и удобству отчёт типа «Dashboard», который покажет вам новый уровень отчётности.
Что же собственно вы получите, данные обыкновенного вида:
Преобразуются в сводную таблицу приблизительно такого вида:
А теперь мы и приступим к тому, как же сделать наш отчёт изящным отчётом, который не стыдно будет показать руководителю любого ранга:
Итак, рассмотрим более детально вопрос, как получить данные со сводной таблицы в такую форму отчёта. У нас есть таблица данных, из которой мы формируем сводную таблицу. Следующим шагом для нас будет создание формы отчёта, в которую мы будем добавлять данные со сводной таблицы и анализировать их.
Ставим курсор на первую ячейку куда нам необходимо получить данные и вводим функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, которая и извлечёт со сводной нужные данные для использования в наших расчётах. И выбираем в сводной таблице нужные данные, щелкая сначала на ячейку, где лежит выручка за январь.
В результате мы получаем не привычного вида формулу, а автоматически вставленную функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, которую мы рассмотрим более подробно:
Теперь с полученными, таким образом, данными, возможно работать как с обыкновенными формулами и на ним не распространяются те жёсткие ограничения которые существуют для сводных таблиц, а значит можно легко строить графики, подводить итоги и проводить анализ.
Если ваши исходные данные будут исправлены, дополнены или заменены, вам нужно лишь обновить сводную таблицу (поставьте курсор мыши на сводную таблицу и вызвав контекстное меню, выбрать пункт «Обновить»). И даже если исходные данные кардинально изменятся, вы всё равно получите правильные данные, обновятся и итоги, и графики, и анализ.
Без экономической свободы никакой другой свободы быть не может.
Маргарет Тэтчер