Источник данных в эксель где находится
Главная Надстройки Статьи Сводные таблицы Динамический источник данных сводной таблицы
Динамический источник данных сводной таблицы
| 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 выглядит следующим образом:
Функции COUNT() и COUNTIF() используются для подсчета непустых значений в диапазоне, COUNT() предназначена для числовых ячеек, COUNTIF() – для текстовых. В формуле вычисляется количество непустых строк для поля типа дата (столбец A) и количество столбцов по заголовкам полей (строка 1). К количеству столбцов добавляется единица, так как в диапазон входит также строка заголовков. Результирующая функция OFFSET() выдает прямоугольный диапазон с относительным смещением от левого верхнего угла рабочего листа (Name!$A$1). Если количество полей исходных данных не меняется, то вместо последнего параметра функции лучше явно указать это число:
Добавив несколько заполненных строк вниз таблицы исходных данных, можно убедиться, что сводная таблица успешно их обрабатывает (по нажатию кнопки «Обновить»).
Динамический именованный диапазон можно признать самым универсальным способом определения источника сводной таблицы, так как он не требует специальных объектов («умная таблица») и работает даже в старом xls-формате. В приведенном примере имеется требование на наличие непустого идентифицирующего поля в исходном диапазоне. На самом деле его также можно обойти, используя дополнительные вычисления (например, добавив функцию COUNTBLANK()), главное, чтобы в результате был найден конец заполненного диапазона.
Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.
В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.
Теперь немного о действиях, совершенных мной с целью подключения к базе:
В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».
Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.
Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).
После всех проделанных манипуляций, Мастер подключения предложит сохранить файл подключения. Потребуется задать «(1) Имя файла». Желательно также указать «(2) Описание» и «(3) Понятное имя файла», чтобы спустя время было понятно какой файл подключения к какой базе или таблице обращается.
Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».
Открыв только что созданное подключение, в случае если вы соединялись с базой в целом, MS Excel опять предложит выбрать одну или несколько конкретных таблиц:
Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».
В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.
Вы узнали, как суммировать данные с помощью сводной таблицы. Данные, на которых основана сводная таблица, могут обновляться либо периодически, либо при возникновении события. Кроме того, вам также может потребоваться изменить макет сводной таблицы для разных отчетов.
В этой главе вы узнаете о различных способах обновления макета и / или обновления данных в сводной таблице.
Обновление макета сводной таблицы
Вы можете решить, будет ли обновляться сводная таблица всякий раз, когда вы вносите изменения в макет, или она должна обновляться с помощью отдельного триггера.
Как вы узнали ранее, в области задач Поля сводных таблиц в нижней части вы найдете флажок для отложенного обновления макета. По умолчанию этот флажок снят, что означает, что макет сводной таблицы обновляется, как только вы вносите изменения в области сводной таблицы.
Установите флажок « Отложить обновление макета» .
Кнопка UPDATE рядом с ней будет включена. Если вы внесете какие-либо изменения в области сводной таблицы, эти изменения будут отражены только после нажатия кнопки ОБНОВЛЕНИЕ.
Обновление данных сводной таблицы
Когда данные сводной таблицы изменяются в ее источнике, то же самое можно отразить в сводной таблице, обновив ее.
- Нажмите на сводную таблицу.
- Нажмите вкладку ANALYZE на ленте.
- Нажмите Обновить в группе данных.
Существуют различные варианты обновления данных в раскрывающемся списке.
Изменение исходных данных сводной таблицы
Вы можете изменить диапазон исходных данных сводной таблицы. Например, вы можете расширить исходные данные, включив в них большее количество строк данных.
Однако, если исходные данные были существенно изменены, например, имеется больше или меньше столбцов, рассмотрите возможность создания новой сводной таблицы.
Нажмите на сводную таблицу. НА ЛЕНТЕ появляются СВОБОДНЫЕ ИНСТРУМЕНТЫ .
Нажмите на вкладку АНАЛИЗ.
Нажмите Изменить источник данных в группе данных.
Нажмите на сводную таблицу. НА ЛЕНТЕ появляются СВОБОДНЫЕ ИНСТРУМЕНТЫ .
Возможно, вы заметили, что сводная диаграмма не поддерживает изменение источника данных в Excel. Однако в некоторых случаях вам необходимо изменить источник данных сводной диаграммы. Эта статья покажет вам, как изменить источник данных сводной диаграммы, а также изменить ось и условные обозначения сводной диаграммы в Excel.
Изменение или редактирование оси / легенд сводной диаграммы в Excel
На самом деле, очень легко изменить или отредактировать ось и легенды сводной диаграммы в списке файлов в Excel. А сделать можно так:
Шаг 1. Выберите сводную диаграмму, ось и условные обозначения которой вы хотите изменить, а затем отобразите панель «Составленный список», щелкнув значок Поданный список Кнопка на Анализировать меню.
Внимание: По умолчанию панель списка полей открывается при нажатии на сводную диаграмму.
Шаг 2: перейдите к Ось (Категории) раздел или Легенда (Серия) в области «Список файлов» перетащите все поля из области «Список полей».
Затем вы увидите, что все оси или легенды удалены из сводной диаграммы.
Шаг 3: Теперь перейдите к Выберите поля для добавления в отчет на панели Список полей перетащите поле в Ось (Категории) sдействие или Легенда (Серия) .
Внимание: Вы также можете щелкнуть поле правой кнопкой мыши и выбрать Добавить в поля оси (категории) or Добавить в поля легенды (серии) из контекстного меню.
Изменение / редактирование источника данных сводной диаграммы в Excel
Если вы хотите изменить источник данных сводной диаграммы в Excel, вам необходимо разорвать связь между этой сводной диаграммой и ее исходными данными сводной таблицы, а затем добавить для нее источник данных. А сделать можно так:
Шаг 1: Выберите сводную диаграмму, в которой вы измените источник данных, и вырежьте ее, нажав кнопку Ctrl + X ключи одновременно.
Шаг 2. Создайте новую книгу, нажав Ctrl + N одновременно, а затем вставьте вырезанную сводную диаграмму в эту новую книгу, нажав Ctrl + V одновременно.
Шаг 3: Теперь вырежьте сводную диаграмму из новой книги, а затем вставьте ее в исходную книгу.
Шаг 4. Щелкните правой кнопкой мыши вставленную сводную диаграмму в исходной книге и выберите Выберите данные из контекстного меню.
Шаг 5: В появившемся диалоговом окне Select Data Source поместите курсор в Диапазон данных диаграммы поле, а затем выберите новые исходные данные в своей книге и щелкните значок OK кнопку.
Читайте также: