как узнать источник сводной таблицы в excel
Изменение исходных данных сводной таблицы
После создания сводной таблицы можно изменить диапазон исходных данных. Например, расширить его и включить дополнительные строки данных. Однако если исходные данные существенно изменены, например содержат больше или меньше столбцов, рекомендуется создать новую сводную таблицу.
Вы можете изменить источник данных для таблицы Excel на другую таблицу или диапазон ячеок либо другой внешний источник данных.
Щелкните Отчет сводной таблицы.
На вкладке «Анализ» в группе «Данные» нажмите кнопку «Изменить источник данных» и выберите «Изменить источник данных».
Отобразилось диалоговое окно «Изменение источника данных в pivotTable».
Выполните одно из указанных ниже действий.
Чтобы изменить источник данных для таблицы Excel на другую таблицу или диапазон ячеок, щелкните «Выбрать таблицу или диапазон», а затем введите первую ячейку в текстовом поле «Таблица или диапазон» и нажмите кнопку «ОК».
Чтобы использовать другое подключение, сделайте следующее:
Выберите «Использовать внешний источник данных» инажмите кнопку «Выбрать подключение».
Отобразилось диалоговое окно «Существующие подключения».
В списке «Показать» в верхней части диалоговых окнах выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (значение по умолчанию).
Выберите подключение в списке «Выберите подключение» и нажмите кнопку «Открыть».Что делать, если подключения нет в списке?
Примечание: При выборе подключения из категории «Подключения» в этой категории будет повторное использование или совместное использование существующего подключения. Если выбрать подключение из файлов подключения в сети или файлов подключения в этой категории компьютеров, файл подключения будет скопирован в книгу как новое подключение к книге, а затем использован в качестве нового подключения для отчета pivottable.
Что делать, если подключения нет в списке?
Если подключения нет в диалоговом окне «Существующие подключения», нажмите кнопку «Обзор дополнительных данных» и найдите нужный источник данных в диалоговом окне «Выбор источника данных». Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Щелкните Отчет сводной таблицы.
На вкладке «Параметры» в группе «Данные» нажмите кнопку «Изменить источник данных» и выберите «Изменить источник данных».
Отобразилось диалоговое окно «Изменение источника данных в pivotTable».
Выполните одно из указанных ниже действий.
Чтобы использовать другую таблицу или диапазон ячеок Excel, щелкните «Выбрать таблицу или диапазон», а затем введите первую ячейку в текстовом поле «Таблица или диапазон».
Вы также можете нажать кнопку «Свернуть 
«, чтобы временно скрыть диалоговое окно, выбрать ячейку в начале, а затем нажать кнопку «Развернуть 
«.
Чтобы использовать другое подключение, выберите «Использовать внешний источник данных» и нажмите кнопку «Выбрать подключение».
Отобразилось диалоговое окно «Существующие подключения».
В списке «Показать» в верхней части диалоговых окнах выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (значение по умолчанию).
Выберите подключение в списке «Выберите подключение» и нажмите кнопку «Открыть».
Примечание: При выборе подключения из категории «Подключения» в этой категории будет повторное использование или совместное использование существующего подключения. Если выбрать подключение из файлов подключения в сети или файлов подключения в этой категории компьютеров, файл подключения будет скопирован в книгу как новое подключение к книге, а затем использован в качестве нового подключения для отчета pivottable.
Что делать, если подключения нет в списке?
Если подключения нет в диалоговом окне «Существующие подключения», нажмите кнопку «Обзор дополнительных данных» и найдите нужный источник данных в диалоговом окне «Выбор источника данных». Если необходимо, щелкните Создание источника и выполните инструкции мастера подключения к данным, а затем вернитесь в диалоговое окно Выбор источника данных.
Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
В Excel в Интернете изменить исходные данные для #x0. Для этого необходимо использовать настольная версия Excel.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает видимые данные из сводной таблицы.
В этом примере =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.(«Продажи»; 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.
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает видимые данные из сводной таблицы.
В этом примере =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.(«Продажи»; 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
Динамический источник данных сводной таблицы
При работе со сводными таблицами несколько раз сталкивался с проблемой, когда новые данные не попадали в отчет. Сводная таблица была основана на фиксированном диапазоне рабочего листа, при этом новые строки просто выходили за границы диапазона. Найти ошибку при этом очень сложно, так как сводный отчет обновляется, но строится на неполных данных. Предлагаем более-менее универсальные решения, позволяющие избежать подобных проблем.
В приложенном к статье примере показано 3 варианта построения сводного отчета на основе данных рабочего листа.
Фиксированный диапазон
Стандартный интерфейс создания новой сводной таблицы Excel выводит диалог с запросом источника данных. Удобнее всего выделить нужный диапазон на рабочем листе при помощи мыши, после чего программа показывает ссылку в A1-адресации. Выделенного диапазона достаточно для создания пустой сводной таблицы. Внешний вид и данные отчета настраиваются стандартными средствами интерфейса.
В зависимости от практической задачи может потребоваться добавить новые данные в источник сводного отчета. Обычно добавляются новые строки. Это может происходить как при ручном вводе или копировании, так и при автоматизированном получении данных из внешних систем. После простого добавления данных вниз исходного диапазона Excel не включит эту новую строку в источник сводной таблицы. Обновление отчета не приведет к изменениям. Действительно, сводная таблица основана на заранее определенном фиксированном диапазоне данных, не включающих новые строки. Пример такой ситуации показан в файле-примере на листах Data и ReportData – строки 11 и 12 в отчет не попали:
Одним из вариантов решения проблемы является добавление новых строк в середину диапазона – при этом Excel корректно исправит ссылку на источник данных сводной таблицы.
На практике же обычно удобнее и проще заранее определить максимально возможный диапазон строк источника сводной таблицы, добавив про запас достаточное количество строк. В зависимости от задачи можно добавить как 10, так и 10000 строк, как ни странно, это почти не сказывается на производительности сводной таблицы. В файле-примере поменяйте источник данных сводной таблицы ReportData следующим образом:
Этот способ вполне годится для использования, если вы уверены, что запас пустых строк всегда покроет возможное новое количество данных. Зачастую этого гарантировать нельзя, особенно, если файл рассчитан на длительное использование и с ним работает несколько пользователей.
Создавать запас пустых строк в источнике данных можно также для старых сводных таблиц xls-формата. Здесь, правда, имеется небольшая недоработка в интерфейсе: если столбец хранит даты, а в исходных данных присутствуют пустые значения, сводный отчет не позволит применить к этому полю стандартную группировку по годам, кварталам, месяцам и т.д. Новые сводные таблицы xlsx-формата лишены этого недостатка.
«Умная таблица»
Начиная с версии Excel 2007, в интерфейсе рабочего листа стала доступна работа с так называемой «умной таблицей» (лента Вставка \ Таблица). Такая таблица имеет расширенные возможности по дизайну, собственную нотацию формул и может быть опубликована на сервере SharePoint для совместного доступа нескольким пользователям. В нашем случае важно, что «умная таблица» Excel может быть указана в качестве источника данных сводного отчета – можно указать ее по имени или выделить мышью (листы Table, ReportTable):
Добавление строк в «умную таблицу» не влияет на источник данных сводного отчета. Ссылка определяется по имени, поэтому анализ всегда проводится на полном объеме информации. Т.е. это вариант решения можно признать удобным в случае, когда исходные данные могут быть преобразованы в «умную таблицу». При ручном заполнении источника данных сводной таблицы этот вариант работы можно даже назвать приоритетным. Недостатком является несовместимость с xls-форматом, а также сложность при автоматизации импорта из внешних систем.
Динамический именованный диапазон
«Продвинутые» пользователи Excel умеют использовать именованные диапазоны рабочего листа. При помощи таких имен можно заменять ссылки в формулах или указывать источники данных. Обычные именованные диапазоны – это просто замена длинной ссылки в координатах рабочего листа на короткое имя. Так в примере на листе Data можно выделить область A1:D12, присвоить ей имя, а затем указать его в качестве источника данных сводной таблицы ReportData.
Результат будет совершенно такой же, как и при использовании ссылки на фиксированный диапазон. Т.е. проблемы с добавлением новых строк простой именованный диапазон не решает.
Немногие пользователи Excel знают, что в качестве именованного диапазона можно использовать не только простые ссылки на область рабочего листа, но и формулы. Такие именованные диапазоны обычно называют вычисляемыми или динамическими. Создание такого диапазона возможно только в Диспетчере имен (лента Формулы \ Диспетчер имен). При помощи функций вычисляемой адресации можно динамически изменять размер диапазона в зависимости от количество строк с данными.
Пример такого использования имени с формулой на листах Name и ReportName.
Формула имени source выглядит следующим образом:
Функции COUNT() и COUNTIF() используются для подсчета непустых значений в диапазоне, COUNT() предназначена для числовых ячеек, COUNTIF() – для текстовых. В формуле вычисляется количество непустых строк для поля типа дата (столбец A) и количество столбцов по заголовкам полей (строка 1). К количеству столбцов добавляется единица, так как в диапазон входит также строка заголовков. Результирующая функция OFFSET() выдает прямоугольный диапазон с относительным смещением от левого верхнего угла рабочего листа (Name!$A$1). Если количество полей исходных данных не меняется, то вместо последнего параметра функции лучше явно указать это число:
Добавив несколько заполненных строк вниз таблицы исходных данных, можно убедиться, что сводная таблица успешно их обрабатывает (по нажатию кнопки «Обновить»).
Динамический именованный диапазон можно признать самым универсальным способом определения источника сводной таблицы, так как он не требует специальных объектов («умная таблица») и работает даже в старом xls-формате. В приведенном примере имеется требование на наличие непустого идентифицирующего поля в исходном диапазоне. На самом деле его также можно обойти, используя дополнительные вычисления (например, добавив функцию COUNTBLANK()), главное, чтобы в результате был найден конец заполненного диапазона.
Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Предположим, что из вот такой базы данных по продажам:
. вы создали небольшую, но симпатичную сводную таблицу:
Но ваш руководитель хочет не совсем ее, а что-то похожее на:
То есть имеем несколько ощутимых трудностей:
Сделаем на отдельном от сводной листе заготовку отчета:
Выделите первую ячейку зеленого диапазона, введите знак «равно» и щелкните по ячейке в сводной, которая содержит нужные данные, т.е. по B8, где лежит выручка Fiesta за январь. Вместо привычной ссылки а-ля «морской бой» Excel вставит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ:
Давайте разберем ее подробно:
А теперь самое интересное.
Теперь с данными в зеленых ячейках можно работать как с обычными формулами, а не как со сводной таблицей с ее жесткими ограничениями, т.е. дальше можно спокойно считать любые итоги, динамику, прогнозы, строить любую диаграмму и т.д.