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

Определение источников данных, используемых в модели данных книги

Если вы работаете с данными, добавленными в модель Excel данных, иногда вы можете не отслеживать, какие таблицы и источники данных были добавлены в модель данных.

Примечание: Убедитесь, что вы включили надстройку Power Pivot. Дополнительные сведения см. в том, как запустить надстройку Power Pivot для Excel.

Чтобы точно определить, какие данные есть в модели, выполните следующие простые действия:

1. В Excel щелкните Power Pivot >Управление, чтобы открыть окно Power Pivot.
2. Просмотрите вкладки в окне Power Pivot. Каждая вкладка содержит таблицу в вашей модели. Столбцы в каждой таблице отображаются в качестве полей в списке полей сводной таблицы. Любой серый столбец скрыт от клиентских приложений.
3. Чтобы просмотреть происхождение таблицы, щелкните Свойства таблицы. Если Свойства таблицы затемнены и вкладка содержит значок ссылки, указывающей на связанную таблицу, данные происходят из листа в таблице, а не из внешнего источника данных. Для всех остальных типов данных в диалоговом окне Изменить свойства таблицы отображаются имя подключения и запрос, используемые для извлечения данных. Запомните или запишите имя подключения, а затем используйте диспетчер подключений в приложении Excel, чтобы определить сетевой ресурс и базу данных, используемые в подключении:
   1. в Excel щелкните Данные >Подключения;
   2. выберите подключение, используемое для заполнения таблицы в модели;
   3. щелкните Свойства >Определение, чтобы просмотреть строку подключения.

Примечание: Модели данных были введены в Excel 2013. Вы можете использовать их для создания сводных таблиц, сводных диаграмм и отчетов Power View, визуализирующих данные из нескольких таблиц. Дополнительные сведения о моделях данных можно узнать в Excel.

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Использование внешнего источника данных для данных в Excel для Mac

Иногда требуется создать сводную таблицу для данных, хранящихся за пределами Excel, например в базе данных. В этом случае следует сначала подключиться к внешнему источнику данных, а затем использовать для их обобщения, анализа, изучения и визуализации сводную таблицу.

Создание сводной таблицы на основе внешнего источника данных

Вы можете использовать внешний источник данных, однако последовательность действий зависит от его типа.

Если источник — база данных SQL

1. На вкладке Данные выберите команду Создать запрос базы данных.
2. Выберите пункт SQL Server ODBC.
3. Откроется диалоговое окно подключения к источнику данных SQL Server ODBC. Уточните данные сервера у своего администратора базы данных и введите их в этом окне. После этого нажмите кнопку Подключиться.
4. Чтобы показать список баз данных, щелкните стрелку рядом с сервером в левой части окна.
5. Щелкните стрелку рядом с нужной базой данных.
6. Щелкните имя нужной таблицы.
7. Чтобы предварительно просмотреть данные, выберите команду Запустить.
8. Когда вы будете готовы импортировать данные в Excel, нажмите кнопку Вернуть данные.
9. В диалоговом окне Импорт данных щелкните Сводная таблица и нажмите кнопку ОК. В Excel будет создана пустая сводная таблица и появится построитель сводных таблиц. Добавьте, удалите или переместите поля.

Если источник — не база данных SQL

Если вы хотите использовать внешний источник данных, не являющийся базой данных SQL, вам потребуется установить на компьютер Mac драйвер ODBC (Open Database Connectivity). Информация о драйверах есть на этой веб-странице. После установки драйвера для источника вы можете использовать внешние данные в с помощью указанных в ней действий.

1. На вкладке Данные выберите команду Создать запрос базы данных.
2. Выберите пункт Из базы данных.
3. Добавьте источник данных для своей базы и нажмите кнопку OK.
4. Чтобы показать список баз данных, щелкните стрелку рядом с сервером в левой части окна.
5. Щелкните стрелку рядом с нужной базой данных.
6. Щелкните имя нужной таблицы.
7. Чтобы предварительно просмотреть данные, выберите команду Запустить.
8. Когда вы будете готовы импортировать данные в Excel, нажмите кнопку Вернуть данные.
9. В диалоговом окне Импорт данных щелкните Сводная таблица и нажмите кнопку ОК. В Excel будет создана пустая сводная таблица и появится построитель сводных таблиц. Добавьте, удалите или переместите поля.

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

В этом примере =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.(«Продажи»; A3) возвращает общий объем продаж из сводной таблицы:

Синтаксис

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

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

поле_данных

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

сводная_таблица

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

поле1, элемент1, поле2, элемент2.

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

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

• Можно быстро ввести простую формулу ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, введя = (знак равенства) в ячейке, в которой должно быть возвращено значение, и затем щелкнув ячейку в сводной таблице, содержащей необходимые данные.
• Вы можете отключить эту возможность. Для этого нужно выбрать любую ячейку в существующей сводной таблице, а затем перейти к вкладке Анализ сводной таблицы >Сводная таблица >Параметры > и снять флажок у параметра Генерировать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.
• Вычисляемые поля или элементы и дополнительные вычисления могут включаться в расчеты для функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ.
• Аргумент «сводная_таблица» задан как диапазон, включающий несколько сводных таблиц. Данные будут извлекаться из той сводной таблицы, которая была создана последней.
• Если аргументы «поле» и «элемент» описывают одну ячейку, возвращается значение, содержащееся в этой ячейке, независимо от его типа (строка, число, ошибка или пустая ячейка).
• Если аргумент «элемент» содержит дату, необходимо представить это значение как порядковый номер или воспользоваться функцией ДАТА, чтобы это значение не изменилось при открытии листа в системе с другими языковыми настройками. Например, элемент, ссылающийся на дату 5 марта 1999 г., можно ввести двумя способами: 36 224 или ДАТА(1999;3;5). Время можно задать в виде десятичных значений или с помощью функции ВРЕМЯ.
• Если аргумент «сводная_таблица» не является диапазоном, содержащим сводную таблицу, функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает значение ошибки #ССЫЛКА!.
• Если аргументы не описывают видимое поле или содержат фильтр отчета, в котором не отображаются отфильтрованные данные, функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает #ССЫЛКА! (значение ошибки).

Примеры

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

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Ошибка сводной таблицы Excel «Недопустимая ссылка на источник данных»

При открытии книги Excel, содержащей квадратные скобки, в имени (например, «foo [1]») при попытке создания сводных таблиц с использованием данных из книги пользователь получит следующее сообщение об ошибке:ссылка на источник данных недействительна

Причина

Причина из-за того, что квадратные скобки являются недопустимыми символами в имени файла книги Excel.

Решение

Это поведение ожидается, если выполняются эти условия. Для решения этой проблемы пользователю потребуется удалить все недопустимые символы Excel из имени файла.

Дополнительная информация

Эта проблема может возблюдаться при попытке открыть книгу Excel из Internet Explorer. Internet Explorer создает копию файла во временной папке, переименование файла с помощью квадратных скобок. Это можно избежать, если нажать кнопку «Сохранить», а не «Открыть», когда Internet Explorer выведет файл пользователю.