Как посмотреть источник данных сводной таблицы в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Определение источников данных, используемых в модели данных книги
Если вы работаете с данными, добавленными в модель Excel данных, иногда вы можете не отслеживать, какие таблицы и источники данных были добавлены в модель данных.
Примечание: Убедитесь, что вы включили надстройку Power Pivot. Дополнительные сведения см. в том, как запустить надстройку Power Pivot для Excel.
Чтобы точно определить, какие данные есть в модели, выполните следующие простые действия:
- В Excel щелкните Power Pivot >Управление, чтобы открыть окно Power Pivot.
- Просмотрите вкладки в окне Power Pivot. Каждая вкладка содержит таблицу в вашей модели. Столбцы в каждой таблице отображаются в качестве полей в списке полей сводной таблицы. Любой серый столбец скрыт от клиентских приложений.
- в Excel щелкните Данные >Подключения;
- выберите подключение, используемое для заполнения таблицы в модели;
- щелкните Свойства >Определение, чтобы просмотреть строку подключения.
Примечание: Модели данных были введены в Excel 2013. Вы можете использовать их для создания сводных таблиц, сводных диаграмм и отчетов Power View, визуализирующих данные из нескольких таблиц. Дополнительные сведения о моделях данных можно узнать в Excel.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Использование внешнего источника данных для данных в Excel для Mac
Иногда требуется создать сводную таблицу для данных, хранящихся за пределами Excel, например в базе данных. В этом случае следует сначала подключиться к внешнему источнику данных, а затем использовать для их обобщения, анализа, изучения и визуализации сводную таблицу.
Создание сводной таблицы на основе внешнего источника данных
Вы можете использовать внешний источник данных, однако последовательность действий зависит от его типа.
Если источник — база данных SQL
- На вкладке Данные выберите команду Создать запрос базы данных.
- Выберите пункт SQL Server ODBC.
- Откроется диалоговое окно подключения к источнику данных SQL Server ODBC. Уточните данные сервера у своего администратора базы данных и введите их в этом окне. После этого нажмите кнопку Подключиться.
- Чтобы показать список баз данных, щелкните стрелку рядом с сервером в левой части окна.
- Щелкните стрелку рядом с нужной базой данных.
- Щелкните имя нужной таблицы.
- Чтобы предварительно просмотреть данные, выберите команду Запустить.
- Когда вы будете готовы импортировать данные в Excel, нажмите кнопку Вернуть данные.
- В диалоговом окне Импорт данных щелкните Сводная таблица и нажмите кнопку ОК. В Excel будет создана пустая сводная таблица и появится построитель сводных таблиц. Добавьте, удалите или переместите поля.
Если источник — не база данных SQL
Если вы хотите использовать внешний источник данных, не являющийся базой данных SQL, вам потребуется установить на компьютер Mac драйвер ODBC (Open Database Connectivity). Информация о драйверах есть на этой веб-странице. После установки драйвера для источника вы можете использовать внешние данные в с помощью указанных в ней действий.
- На вкладке Данные выберите команду Создать запрос базы данных.
- Выберите пункт Из базы данных.
- Добавьте источник данных для своей базы и нажмите кнопку OK.
- Чтобы показать список баз данных, щелкните стрелку рядом с сервером в левой части окна.
- Щелкните стрелку рядом с нужной базой данных.
- Щелкните имя нужной таблицы.
- Чтобы предварительно просмотреть данные, выберите команду Запустить.
- Когда вы будете готовы импортировать данные в Excel, нажмите кнопку Вернуть данные.
- В диалоговом окне Импорт данных щелкните Сводная таблица и нажмите кнопку ОК. В Excel будет создана пустая сводная таблица и появится построитель сводных таблиц. Добавьте, удалите или переместите поля.
Финансы в Excel
Главная 
Статьи Сводные таблицы Динамический источник данных сводной таблицы
Динамический источник данных сводной таблицы
| Содержание |
|---|
| Фиксированный диапазон |
| «Умная таблица» |
| Динамический именованный диапазон |
| pivotsources.xlsx | [Источники данных сводных таблиц] | 30 kB |
При работе со сводными таблицами несколько раз сталкивался с проблемой, когда новые данные не попадали в отчет. Сводная таблица была основана на фиксированном диапазоне рабочего листа, при этом новые строки просто выходили за границы диапазона. Найти ошибку при этом очень сложно, так как сводный отчет обновляется, но строится на неполных данных. Предлагаем более-менее универсальные решения, позволяющие избежать подобных проблем.
В приложенном к статье примере показано 3 варианта построения сводного отчета на основе данных рабочего листа.
- Фиксированный диапазон
- «Умная таблица» Excel 2007
- Динамический именованный диапазон
Фиксированный диапазон
Стандартный интерфейс создания новой сводной таблицы 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 выглядит следующим образом:
=OFFSET(Name!$A$1;0;0;COUNT(Name!$A:$A)+1;COUNTIF(Name!$1:$1;"*"))
Функции COUNT() и COUNTIF() используются для подсчета непустых значений в диапазоне, COUNT() предназначена для числовых ячеек, COUNTIF() – для текстовых. В формуле вычисляется количество непустых строк для поля типа дата (столбец A) и количество столбцов по заголовкам полей (строка 1). К количеству столбцов добавляется единица, так как в диапазон входит также строка заголовков. Результирующая функция OFFSET() выдает прямоугольный диапазон с относительным смещением от левого верхнего угла рабочего листа (Name!$A$1). Если количество полей исходных данных не меняется, то вместо последнего параметра функции лучше явно указать это число:
=OFFSET(Name!$A$1;0;0;COUNT(Name!$A:$A)+1;4)
Добавив несколько заполненных строк вниз таблицы исходных данных, можно убедиться, что сводная таблица успешно их обрабатывает (по нажатию кнопки «Обновить»).
Динамический именованный диапазон можно признать самым универсальным способом определения источника сводной таблицы, так как он не требует специальных объектов («умная таблица») и работает даже в старом xls-формате. В приведенном примере имеется требование на наличие непустого идентифицирующего поля в исходном диапазоне. На самом деле его также можно обойти, используя дополнительные вычисления (например, добавив функцию COUNTBLANK()), главное, чтобы в результате был найден конец заполненного диапазона.
Смотри также
» Обработка больших объемов данных. Часть 3. Сводные таблицы
Третья статья, посвященная обработке больших объемов данных с помощью Excel, описывает преимущества использования сводных таблиц. Вообще, эта статья должна была быть первой в цикле, если говорить о пользе того или иного метода работы. Действительно, интерфейс сводных таблиц специально создавался для анализа больших объемов данных, которые могут храниться не только в диапазонах электронных таблиц, но и во внешних источниках данных. Понимание принципов работы и практическое использование сводных таблиц позволяет существенно оптимизировать повседневную работу экономистов. Повышение уровня анализа данных, в свою очередь, ведет к улучшению управляемости компании и принятию верных управленческих решений менеджерами различных уровней.
» Сводная таблица Excelfin.ru
Надстройка предназначена для создания сводных таблиц на основе нескольких диапазонов данных файла Excel. Пользовательский интерфейс в виде «мастера» позволяет строить связанные запросы к данным без знания SQL.
» Сводные таблицы
Первый интерфейс сводных таблиц, называемых также сводными отчеты, был включен в состав Excel еще в 1993м году (версии Excel 5.0). Несмотря на множество полезных функциональных возможностей, он практически не применяется в работе большинством пользователей Excel. Даже опытные пользователи зачастую подразумевают под термином «сводный отчет» нечто построенное с помощью сложных формул. Попробуем популяризировать использование сводных таблиц в повседневной работе экономистов. В статье обсуждаются теоретические основы создания сводных отчетов, даются практические рекомендации по их использованию, а также приводится пример доступа к данным на основе нескольких таблиц.
» Сводный отчет на основе нескольких таблиц Excel
В стандартном режиме Excel позволяет строить сводные отчеты на основе диапазона ячеек, расположенного на одном рабочем листе. Собрать данные с нескольких листов при помощи формул нетрудно, только если структура полей на этих листах идентична. В противном случае задача создания источника данных сводной таблицы может оказаться достаточно сложной.Средства автоматизации позволяют обойти это ограничение и связать несколько диапазонов ячеек по определенным ключевым полям. На основе сформированного запроса строится сводная таблица Excel.
» Обновление списков сводной таблицы
При работе со сводными таблицами, сохраненными в качестве отчетов и использующих обновляемые исходные данные, выпадающие списки полей могут показывать устаревшие несуществующие в текущем контексте значения. Эта недоработка тянется с момента появления интерфейса сводных таблиц до версии Excel 2007. Исправить эту ситуацию можно, используя программный код VBA.