Как посмотреть источник данных сводной таблицы в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
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, например в базе данных Microsoft Access или Microsoft SQL Server или в файле куба OLAP. В этом случае вы подключитесь к внешнему источнику данных, а затем создадите сводную таблицу для суммирования, анализа, изучения и представления данных.
Вот как создать сводную таблицу с помощью существующего подключения к внешним данным:
- Щелкните любую ячейку на листе.
- Выберите Вставка >Сводная таблица.
- В диалоговом окне Создание сводной таблицы щелкните Из внешнего источника данных.
- Выберите вариант Выбрать подключение.
- На вкладке «Подключения» в окне «Показать» оставьте флажок «Все подключения» или выберите категорию подключения с источником данных, к которому вы хотите подключиться.
Чтобы повторно использовать существующее подключение или поделиться с этим подключением, используйте подключение из connections в этой книге.
- В списке подключений выберите нужное подключение и нажмите кнопку » Открыть».
- В разделе «Выбор места размещения отчета сводной таблицы» выберите расположение.
- Чтобы поместить сводную таблицу на новый лист, начиная с ячейки A1, выберите » Создать лист».
- Чтобы поместить сводную таблицу на активный лист, выберите «Существующий лист«, а затем в поле «Расположение» введите ячейку, в которой будет запущена сводная таблица.
- Нажмите кнопку ОК. Excel добавляет пустую сводную таблицу и отображает список полей, чтобы можно было отобразить нужные поля и изменить их порядок для создания собственного макета.
- В разделе списка полей установите флажок рядом с именем поля, чтобы поместить поле в область по умолчанию в разделе областей списка полей. Как правило, в область «Строки» добавляются нечисловые поля, в область «Значения» добавляются числовые поля, а в область «Столбцы» — поля даты и времени. При необходимости поля можно переместить в другую область.
Совет: Можно также щелкнуть правой кнопкой мыши имя поля, а затем выбрать «Добавить в фильтр отчета«, «Добавить в метки столбцов «, «Добавить в метки строк» или «Добавить в значения«, чтобы поместить поле в эту область раздела областей, или перетащить поле из раздела поля в область в разделе областей.
Подключение к новому внешнему источнику данных
Чтобы создать подключение внешних данных к SQL Server и импортировать данные в Excel в виде таблицы или сводной таблицы, выполните следующие действия.
- Щелкните «>из других источников».
- Щелкните нужное подключение.
- Щелкните «SQL Server«, чтобы создать подключение к SQL Server таблице.
- Щелкните «Из служб Analysis Services«, чтобы создать подключение к SQL Server analysis.
- В мастере подключения к данным выполните действия по установке подключения.
- На странице 1 введите сервер базы данных и укажите способ входа на сервер.
- На странице 2 введите базу данных, таблицу или запрос, содержащий нужные данные.
- На странице 3 введите файл подключения, который вы хотите создать.
Чтобы создать новое подключение к базе данных Access и импортировать данные в Excel в виде таблицы или сводной таблицы, сделайте следующее:
Если таблиц несколько, установите флажок «Включить выбор нескольких таблиц», чтобы установить флажки нужных таблиц, а затем нажмите кнопку «ОК».
В диалоговом окне «Импорт данных» выберите способ просмотра данных в книге и место их размещения, а затем нажмите кнопку «ОК «.
Таблицы автоматически добавляются в модель данных, а база данных Access добавляется в подключения к книге.
Дополнительные сведения о сводных таблицах
- Создание сводной таблицы для анализа данных на листе
- Создание сводной таблицы для анализа данных в нескольких таблицах
- Упорядочение полей сводной таблицы с помощью списка полей
- Изменение диапазона исходных данных для сводной таблицы
- Обновление данных в сводной таблице
- Удаление сводной таблицы
Использование внешнего источника данных для данных в Excel для Mac
Иногда требуется создать сводную таблицу для данных, хранящихся за пределами Excel, например в базе данных. В этом случае следует сначала подключиться к внешнему источнику данных, а затем использовать для их обобщения, анализа, изучения и визуализации сводную таблицу.
Создание сводной таблицы на основе внешнего источника данных
Вы можете использовать внешний источник данных, однако последовательность действий зависит от его типа.
Если источник — база данных SQL
- На вкладке Данные выберите команду Создать запрос базы данных.
- Выберите пункт SQL Server ODBC.
- Откроется диалоговое окно подключения к источнику данных SQL Server ODBC. Уточните данные сервера у своего администратора базы данных и введите их в этом окне. После этого нажмите кнопку Подключиться.
- Чтобы показать список баз данных, щелкните стрелку рядом с сервером в левой части окна.
- Щелкните стрелку рядом с нужной базой данных.
- Щелкните имя нужной таблицы.
- Чтобы предварительно просмотреть данные, выберите команду Запустить.
- Когда вы будете готовы импортировать данные в Excel, нажмите кнопку Вернуть данные.
- В диалоговом окне Импорт данных щелкните Сводная таблица и нажмите кнопку ОК. В Excel будет создана пустая сводная таблица и появится построитель сводных таблиц. Добавьте, удалите или переместите поля.
Если источник — не база данных SQL
Если вы хотите использовать внешний источник данных, не являющийся базой данных SQL, вам потребуется установить на компьютер Mac драйвер ODBC (Open Database Connectivity). Информация о драйверах есть на этой веб-странице. После установки драйвера для источника вы можете использовать внешние данные в с помощью указанных в ней действий.
- На вкладке Данные выберите команду Создать запрос базы данных.
- Выберите пункт Из базы данных.
- Добавьте источник данных для своей базы и нажмите кнопку OK.
- Чтобы показать список баз данных, щелкните стрелку рядом с сервером в левой части окна.
- Щелкните стрелку рядом с нужной базой данных.
- Щелкните имя нужной таблицы.
- Чтобы предварительно просмотреть данные, выберите команду Запустить.
- Когда вы будете готовы импортировать данные в Excel, нажмите кнопку Вернуть данные.
- В диалоговом окне Импорт данных щелкните Сводная таблица и нажмите кнопку ОК. В Excel будет создана пустая сводная таблица и появится построитель сводных таблиц. Добавьте, удалите или переместите поля.