Как создать источник данных в excel

Обновлено: 04.07.2024

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

Прежде, чем начать давайте уточним пару моментов. Априори я полагаю, что в наших данных выполняются следующие условия:

Таблицы могут иметь любое количество строк с любыми данными, но обязательно - одинаковую шапку.
На листах с исходными таблицами не должно быть лишних данных. Один лист - одна таблица. Для контроля советую использовать сочетание клавиш Ctrl + End , которое перемещает вас на последнюю использованную ячейку листа. В идеале - это должна быть последняя ячейка таблицы с данными. Если при нажатии на Ctrl + End выделяется какая-либо пустая ячейка правее или ниже таблицы - удалите после таблицы эти пустые столбцы справа или строки снизу и сохраните файл.

Способ 1. Сборка таблиц для сводной с помощью Power Query

Начиная с 2010 версии для Excel существует бесплатная надстройка Power Query, которая умеет собирать и трансформировать любые данные и отдавать их потом как источник для построения сводной таблицы. Решить нашу задачу с помощью этой надстройки совсем несложно.

Сначала создадим новый пустой файл в Excel - в него будет происходить сборка и в нем же потом будет создаваться сводная таблица.

Затем на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) выберем команду Создать запрос - Из файла - Excel (Get Data - From file - Excel) и укажем исходный файл с таблицами, которые надо собрать:

В появившемся окне выберем любой лист (не принципиально какой именно) и внизу жмем кнопку Изменить (Edit) :

Поверх Excel должно открыться окно редактора запросов Power Query. В правой части окна на панели Параметры запроса удалим все автоматически созданные шаги кроме первого - Источник (Source) :

Теперь мы видим общий список всех листов. Если в файле кроме листов с данными есть еще какие-то побочные листы, то на этом шаге наша задача - отобрать только те листы, с которых нужно загрузить информацию, исключив все остальные с помощью фильтра в шапке таблицы:

Удалим все столбцы, кроме колонки Data, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns) :

Затем можно развернуть содержимое собранных таблиц, щелкнув по двойной стрелке в верхней части столбца (флажок Использовать исходное имя столбца как префикс можно при этом отключить):

Если вы всё сделали правильно, то на этом моменте должны увидеть содержимое всех таблиц, собранных друг под другом:

Осталось поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) и удалить попавшие в данные повторяющиеся шапки таблиц с помощью фильтра:

Сохраним всё проделанное с помощью команды Закрыть и загрузить - Закрыть и загрузить в. (Close & Load - Close & Load to. ) на вкладке Главная (Home) , а в открывшемся окне выберем опцию Только подключение (Connection Only) :

Всё. Осталось только построить сводную. Для этого идём на вкладку Вставка - Сводная таблица (Insert - Pivot Table) , выбирыем опцию Использовать внешний источник данных (Use external data source) , а затем, нажав кнопку Выбрать подключение, наш запрос. Дальнейшее создание и настройка сводной происходит совершенно стандартным образом путем перетаскивания нужных нам полей в области строк, столбцов и значений:

Если в будущем изменятся исходные данные или добавится еще несколько листов-магазинов, то достаточно будет обновить запрос и нашу сводную с помощью команды Обновить все на вкладке Данные (Data - Refresh All) .

Способ 2. Объединяем таблицы SQL-командой UNION в макросе

Еще одно решение нашей задачи представлено вот таким макросом, который создает набор данных (cache) для сводной таблицы, используя команду UNION языка запросов SQL. Эта команда объединяет таблицы со всех указанных в массиве SheetNames листов книги в единую таблицу данных. То есть вместо физического копирования-вставки диапазонов с разных листов на один мы делаем то же самое в оперативной памяти компьютера. Потом макрос добавляет новый лист с заданным именем (переменная ResultSheetName) и создает на нем полноценную(!) сводную на основе собранного кэша.

Чтобы воспользоваться макросом используйте кнопку Visual Basic на вкладке Разработчик (Developer) или сочетание клавиш Alt + F11 . Затем вставляем новый пустой модуль через меню Insert - Module и копируем туда следующий код:

Готовый макрос потом можно запустить сочетанием клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer - Macros) .

Минусы такого подхода:

Данные не обновляются, т.к. кэш не имеет связи с исходными таблицами. При изменении исходных данных надо запустить макрос еще раз и построить сводную заново.
При изменении количества листов необходимо правки в код макроса (массив SheetNames).

Зато в итоге получаем самую настоящую полноценную сводную таблицу, построенную по нескольким диапазонам с разных листов:

И скачайте и установите бесплатный движок обработки данных из Access с сайта Microsoft - Microsoft Access Database Engine 2010 Redistributable

Способ 3. Мастер консолидации сводных таблиц из старых версий Excel

Этот способ немного устарел, но тоже стоит упоминания. Формально говоря, во всех версиях до 2003 включительно в мастере сводных таблиц была опция "построить сводную по нескольким диапазонам консолидации". Однако, отчет, построенный таким образом, к сожалению, будет лишь жалким подобием настоящей полноценной сводной и не поддерживает многие "фишки" обычных сводных таблиц:

В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому начиная с 2007 года Microsoft эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц. Теперь эта возможность доступна только через настраиваемую кнопку Мастер сводных таблиц (Pivot Table Wizard) , которую при желании можно добавить на панель быстрого доступа через Файл - Параметры - Настройка панели быстрого доступа - Все команды (File - Options - Customize Quick Access Toolbar - All Commands) :

После нажатия на добавленную кнопку нужно выбрать на первом шаге мастера соответствующую опцию:

А затем в следующем окне выделить по очереди каждый диапазон и добавить его в общий список:

Но, повторюсь, это не полноценная сводная, так что не ждите от нее слишком многого. Подобный вариант могу рекомендовать только в очень простых случаях.

Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: 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 предложит сохранить изменения запроса – отвечаем положительно.

Коннектор « Excel » - объект, предназначенный для извлечения данных из файла формата Microsoft Excel (*.xls или *.xlsx). Для импорта данных из файлов Microsoft Excel в операционной системе разрядностью 64 бита «Форсайт. Аналитическая платформа» и Microsoft Office должны иметь такую же разрядность.

Примечание . Для импорта данных из файлов Excel с большим объёмом данных может потребоваться большое количество оперативной памяти. Для предотвращения такой ситуации перенесите данные из файла Excel в текстовый файл и воспользуйтесь коннектором «Текст».

После добавления коннектора на рабочее пространство задачи ETL задайте базовые свойства и настройки импорта. Также можно отредактировать выход коннектора и задать параметры фильтрации данных.

Базовые свойства

В базовых свойствах задается наименование объекта, идентификатор и примечание.

Настройка импорта

На странице « Настройка импорта » указывается файл с импортируемыми данными и параметры, которые используются для определения диапазона с данными.

В поле « Файл » укажите импортируемый файл Microsoft Excel. Для выбора файла нажмите кнопку « Обзор ».

В зависимости от используемого формата файла и его драйвера, будет доступен различный набор настроек:

В раскрывающемся списке « Лист данных » выберите лист, с которого будут импортированы данные.

Для настройки импорта укажите параметры:

Пропустить первые N строк . Укажите количество строк, которые необходимо пропустить при импорте. Если указан «0», то данные будут импортированы с начала листа;

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

В раскрывающемся списке выберите драйвер версии Microsoft Excel, в который был сохранен файл. По умолчанию используется драйвер ExcelEx . Если используется данный драйвер, то будут доступны настройки, аналогичные формату XLSX . Если используется другой драйвер, то будут доступны настройки:

Первая строка содержит заголовки . При установленном флажке значения первой строки будут идентифицированы как наименования столбцов. Если флажок снят, то первая строка таблицы будет расценена как строка данных;

Способ расположения импортируемых данных :

Лист данных . В раскрывающемся списке выберите лист, с которого будут импортированы данные;

Запрос . В поле ввода сформируйте запрос, на основе которого будет осуществлена выборка данных из файла.

Если установить флажок « Запрашивать файл при выполнении задачи », то при выполнении задачи ETL будет открыт диалог для выбора файла Microsoft Excel. По умолчанию флажок снят, при этом работа осуществляется с тем файлом, который указан в поле « Файл ».

Для задания дополнительных настроек, которые используются при идентификации данных, нажмите кнопку « Дополнительные настройки ». Будет открыто окно «Дополнительные настройки». Для предварительного просмотра данных, отобранных по запросу, нажмите кнопку « Просмотр данных ».

Примечание . После того, как определены настройки импорта, структура файла не должна изменяться. Если файл запрашивается при выполнении задачи и задача используется несколько раз для загрузки данных из разных файлов, то структура этих файлов должна быть одинаковая и соответствовать тому файлу, который использовался при первичной настройке коннектора в задаче ETL.

Кросс-таблица

Кросс-таблица - это сводная таблица, в которой заголовки могут располагаться как по строкам, так и по столбцам, позволяя таким образом отразить взаимосвязь двух и более переменных друг с другом.

При установке флажка « Кросс-таблица » будут доступны дополнительные страницы, на которых задаются настройки по идентификации данных в кросс-таблице:

Структура кросс-таблицы

Для настройки структуры кросс-таблицы для выбранного источника импорта:

В появившемся диалоговом окне установите флажок « Кросс-таблица ».

Перейдите на появившуюся в мастере создания и редактирования источника данных «Excel» страницу « Структура кросс-таблицы »:

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

Цвет выделенных ячеек отражает тип данных. Зависимость типа данных от цвета ячеек указана в легенде. Белым цветом выделяются ячейки, которые будут исключены при настройке кросс-таблицы и импортированы не будут. Ячейки, которые были исключены, будут использованы как заголовки столбцов.

Настройка кросс-таблицы

Для настройки кросс-таблицы:

Настройте её структуру.

Перейдите на страницу « Настройка кросс-таблицы »:

На данной странице существует возможность изменить диапазон импортируемых данных:

исключение строк/столбцов . Выполните команду « Исключить » в контекстном меню для крайних столбцов и/или строк. Данные этих столбцов импортированы не будут. Нельзя исключить единственную строку или столбец. Для добавления исключённых строк или столбцов в диапазон импортируемых данных выполните команду « Включить » в контекстном меню столбцов и/или строк;

фиксация ячеек, которые попали в исключение . Выполните команду « Добавить фиксированную ячейку » в контекстном меню ячейки. Исключёнными считаются ячейки, которые находятся за пределами кросс-таблицы, т.е. исключённые из заголовков строк и столбцов и не являющимися данными. Для удаления фиксированной ячейки выполните команду « Удалить фиксированную ячейку » в контекстном меню ячейки.

Редактирование выхода

На странице « Редактирование выхода » задаётся связь с объектом-приёмником, в который будут выгружаться данные при выполнении задачи ETL.

На странице доступны следующие настройки:

Укажите идентификатор выхода редактируемого объекта. Возможно использование символов латинского алфавита, цифр и специального символа «_».

Установите связь с объектом-приёмником, в который будут выгружаться данные. Для этого из раскрывающегося списка выберите объект задачи ETL.

Поля объекта-приёмника, в которые будут выгружаться данные, предназначены для определения формата вывода данных.

Примечание . Редактирование списка полей доступно только для коннекторов к источнику данных. Для коннекторов к приёмнику данных список полей заполняется автоматически.

Для добавления в список всех полей из связанного объекта-приёмника:

После выполнения действий в список полей будут добавлены все поля из связанного объекта-приёмника данных.

Для добавления в список всех полей из связанного объекта-источника:

В раскрывающемся меню кнопки выберите пункт « Из источника ».

После выполнения действий в список полей будут добавлены все поля из связанного объекта-источника данных.

Для добавления нового поля:

Задайте в открывшемся окне значения атрибутов поля:

Идентификатор . Укажите уникальный идентификатор поля. По умолчанию « FIELD< Порядковый номер поля > »;

Наименование . Укажите наименование поля. По умолчанию FIELD< Порядковый номер поля >;

Тип . Из раскрывающегося списка выберите тип данных поля:

По умолчанию установлен строковый тип данных;

Вычисляемое поле . Установите данный флажок для задания формулы, по которой будет вычисляться значение поля. После установки флажка введите выражение с помощью клавиатуры или редактора выражений, который открывается при нажатии на кнопку « Обзор ».
Для увеличения значения каждой новой записи на фиксированную величину используйте специальное выражение INCREMENT .

Примечание . Специальное выражение INCREMENT доступно только для вычисляемого поля целого типа.

Синтаксис данного выражения: INCREMENT[Value1, Value2], где Value1 - начальное значение, Value2 - шаг, на который значение Value1 должно увеличиваться при каждом вызове выражения. При каждой загрузке данных заполнение будет начинаться с начального значения.

После выполнения действий будет добавлено новое поле.

Для редактирования поля:

дважды щёлкните по полю основной кнопкой мыши;

выделите необходимое поле и нажмите кнопку « Редактировать ».

После выполнения действий будет открыто окно « Свойства поля », приведённое выше.

Для удаления выбранного поля нажмите кнопку « Удалить ». Поле будет удалено без подтверждения выполняемого действия.

Для изменения порядка выгрузки данных в коннектор выделите необходимое поле и переместите его в списке полей с помощью кнопок « Вверх » и « Вниз ».

Фильтрация

На странице « Фильтрация » задаётся условие, в соответствии с которым будут отбираться импортируемые данные.

Для формирования условия фильтрации нажмите кнопку « Обзор ». Будет открыто окно редактора выражения. Для составления выражения доступны все поля источника. Импортированы будут те данные, которые удовлетворяют заданному условию.

Программа Excel является прекрасным средством обработки и анализа данных. По сути, сводные таблицы сами по себе являются доказательством аналитической мощи Excel. Однако, говоря обо всех достоинствах программы, нельзя не упомянуть и об одном существенном упущении. Программа Excel построена на относительно простой платформе управления данными, обладающей тремя недостатками. [1]

Размер набора данных сильно влияет на скорость обработки данных в сводной таблице. Это накладывает серьезные ограничения на эффективность использования сводных таблиц как самодостаточных структур. Причина подобного поведения программы — в специфике управления оперативной памятью. Файл при открытии в Excel полностью перемещается в оперативную память для более быстрой обработки и доступа к данным. Но при этом в программе не реализован надежный механизм оптимального управления оперативной памятью при извлечении из нее даже небольшого фрагмента данных. Несмотря на то что в Excel 2013 предусматривается использование до 1 млн. строк и 16 тыс. столбцов, даже средние по размеру наборы данных приводят к значительным задержкам при обработке.
Отсутствие реляционной модели данных вынуждает нас использовать «плоские» таблицы, которые хранят избыточные данные и увеличивают вероятность появления ошибок.
Отсутствие индексации полей данных в Excel для оптимизации процесса извлечения больших объемов данных.

Именно поэтому в серьезных организациях для управления данными используется не Excel, а такие СУБД, как Microsoft Access и SQL Server. Эти СУБД применяются для хранения миллионов записей, которые можно быстро находить и извлекать. При подобном разделении производственных задач формируется уровень управления данными (сама база данных) и уровень приложения (Excel). Трудность заключается в том, чтобы найти наилучший способ извлечения информации из уровня управления данными и передачи ее на уровень приложения для эффективного использования в сводной таблице.

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

Создание сводных таблиц на основе данных Microsoft Access

Программа MS Access применяется для управления наборами таблиц, которые связаны согласно определенным правилам или полям. При управлении данными в Access в вашем распоряжении оказываются все преимущества реляционных баз данных: целостность информации, отсутствие избыточных данных и получение необходимых сведений с помощью запросов.

Для большинства пользователей Excel базы данных Access представляют ценность только как хранилища информации, данные из которых извлекаются с помощью запросов. После импорта данных в Excel их можно представить в виде сводных таблиц, а потому и быстро проанализировать самым тщательным образом. Проблема такого подхода заключается в том, что он предполагает хранение в одной рабочей книге сразу двух копий одного и того же набора данных: на рабочем листе и в кеше сводной таблицы. Это автоматически делает рабочую книгу громоздкой, а ее файл — в два раза большего размера, чем того требует ситуация. В результате возникают проблемы производительности.

В Excel 2013 поддерживается удивительно простой способ извлечения данных из файлов Access, не требующий создания двух их копий. Просто запустите Excel и откройте пустую рабочую книгу. Далее перейдите на вкладку ленты Данные и в группе Получение внешних данных щелкните на кнопке Из Access (рис. 1).

Рис. 1. Получение данных из Access

На экране появится диалоговое окно с просьбой указать базу данных, из которой будет извлекаться информация. Укажите исходную базу данных. (Ссылка на базу данных, используемую в данном примере, приведена в начале заметки; вам потребуется сохранить базу данных на локальном диске). После выбора базы данных на экране появится диалоговое окно, подобное показанному на рис. 2. В нем перечислены все таблицы и запросы, присутствующие в указанной базе данных. В нашем примере выбран запрос Sales By Employee. Щелкните на кнопке ОК.

Рис. 2. Выберите запрос или таблицу, данные которой нужно проанализировать

В диалоговом окне Выбор таблицы имеется столбец Тип. В Access существуют два типа объектов, которые могут импортироваться в Excel: представление (VIEW) и таблица (TABLE). Представления соответствуют запросам Access. В нашем примере Sales_By_Employee — это запрос Access. Таким образом, в сводную таблицу будет импортироваться результат выполнения запроса. Это очень важный аспект обработки данных. Выборку необходимых данных, соответствующих запросу, выполняет программа Access, a Excel всего лишь выполняет анализ выбранных данных.

Следующим на экране появляется диалоговое окно Импорт данных. В нем указывается формат, в котором будут импортироваться данные (рис. 3). Можно импортировать исходные данные как таблицу, сводную таблицу или сводную таблицу с соответствующей диаграммой. Кроме того, нужно указать Excel, куда именно следует поместить данные. Щелкните ОК. На листе появится панель Поля сводной таблицы. Можно начинать работать с только что созданной сводной таблицей (рис. 4).

Рис. 3. Установите переключатель Отчет сводной таблицы

Рис. 4. Сводная таблица готова к использованию

Преимущество описанного способа заключается в сохранении в рабочем файле только одной копии исходных данных, которая заносится в кеш сводной таблицы. Ни в каких рабочих листах в явном виде импортированные данные не сохраняются. Представляет ли это проблему, если вы захотите вывести импортированные данные? Конечно же, нет!

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

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

Создание сводных таблиц на основе данных SQL Server

В связи с популяризацией коллективных вычислений в Excel 2013 были серьезно усовершенствованы средства подключения к транзакционным базам данных, таким как SQL Server. С помощью новых, встроенных в Excel инструментов создание сводных таблиц на основе баз данных SQL Server становится предельно простым занятием. Начните с перехода на вкладку ленты Данные. Щелкните на кнопке Из других источников и в раскрывающемся списке выберите команду С сервера SQL Server (рис. 5).

Рис. 5. Выберите в раскрывающемся списке команду С сервера SQL Server

Тем самым вы запустите мастер подключения к данным (рис. 6). С его помощью в Excel настраивается ссылка на внешние данные, расположенные на сервере.

Рис. 6. Введите регистрационные данные и щелкните на кнопке Далее

В рассматриваемом случае файл примера отсутствует. Здесь проиллюстрирована процедура взаимодействия Excel и SQL Server. Действия, которые вы будете выполнять для подключения к собственной базе данных, полностью повторяют описанные ниже. На первом шаге мастера нужно предоставить Excel регистрационные данные. Как видно на рис. 6, от вас требуется ввести имя сервера, а также имя пользователя и пароль доступа к данным. При вводе регистрационных данных в Windows достаточно установить переключатель Использовать проверку подлинности Windows.

Далее укажите нужную базу данных, выбрав ее в раскрывающемся меню, в котором перечисляются все базы данных, хранящиеся на соответствующем сервере (рис. 7). В примере выбрана база MyServer66. Выбор этой базы данных приводит к отображению в нижней части окна всех ее таблиц и представлений. Вам осталось только указать анализируемую таблицу или представление, а затем щелкнуть на кнопке Далее.

Рис. 7. Укажите исходную базу данных, а затем выберите таблицу или представление

В следующем диалоговом окне мастера (рис. 8) можно ввести описательную информацию о создаваемом подключении. Все текстовые поля заполнять не обязательно. Даже если вы не введете ни один из параметров, подключение все равно будет работоспособным.

Рис. 8. Описательная информация о создаваемом подключении

Чаще всего в этом диалоговом окне используются такие параметры.

Имя файла. В этом поле можно изменить имя файла с расширением .ode (Office Data Connection), генерируемого с целью хранения параметров создаваемого подключения.
Сохранить пароль в файле. Этот флажок, расположенный под полем ввода имени файла, обеспечивает хранение пароля доступа к внешнему источнику в файле, содержащем описание параметров конфигурации подключения. Имейте в виду, что пароль не зашифрован, поэтому любой пользователь может узнать ваш пароль, просто просмотрев файл в текстовом редакторе.
Описание. В этом поле вводится краткое описание назначения устанавливаемого подключения.
Понятное имя. В качестве понятного (для пользователей) имени обычно используется собственное название внешнего источника данных. Это название должно быть более значимым для вас, чем то, которое дал ему его создатель.

Завершив ввод всей необходимой информации, щелкните на кнопке Готово. Вы увидите на экране последнее диалоговое окно — Импорт данных (такое же, как на рис. 3). Установите переключатель Отчет сводной таблицы и щелкните ОК, после чего переходите к непосредственному управлению отчетом сводной таблицы.

Практикум: создание модели данных, включающей несколько внешних таблиц

Предположим, что в вашем распоряжении имеется база данных Access из примера выше. Вы хотите проанализировать данные из этой базы в Excel. Выполните следующие действия.

1. Выделите вкладку Данные и перейдите в группу Получение внешних данных. Щелкните на кнопке Из Access (см. рис. 1).

2. Найдите базу Access и откройте ее. На экране появится диалоговое окно Выбор источника данных. Установите флажок Разрешить выбор нескольких таблиц. Отметьте флажками таблицы, которые будут включаться во внутреннюю модель данных. Щелкните ОК (рис. 9).

Рис. 9. Отметьте флажками таблицы, которые будут включены во внутреннюю модель данных

3. На экране появится диалоговое окно Импорт данных. Установите переключатель Отчет сводной таблицы. Щелкните на стрелке раскрывающегося списка, находящейся справа от кнопки Свойства, и снимите флажок Импорт связей между таблицами (рис. 10). Это исключит вероятность появления ошибки в случае некорректной интерпретации связей между таблицами. Это действие требуется в том случае, если вы собираетесь создавать связи между таблицами самостоятельно. Щелкните на кнопке ОК для создания базовой сводной таблицы

Рис. 10. Снимите флажок Импорт связей между таблицами

4. Выберите вкладку ленты Данные и в области Работа с данными щелкните на кнопке Отношения. На экране появится диалоговое окно Управление связями. Кликните Создать. Откроется окно Изменение связи (рис. 11). Используя раскрывающиеся списки создайте нужные связи через поля, представляющие собою первичные ключи. В нашем примере – это поле Employee_Number. Щелкните OK, чтобы закрыть окно Изменение связи.Щелкните Закрыть, чтобы закрыть окно Управление связями (рис. 12).

Рис. 11. Создайте требуемые связи для только что импортированных таблиц

Рис. 12. Окно Управление связями с только что созданной связью между таблицами Sales_by_Employeeи Employee_Master

5. В вашем распоряжении появилась сводная таблица, основанная на внешних данных, которые были импортированы в модель данных. Можно, например, проанализировать продажи по рынкам сбыта и ФИО торговых представителей (рис. 13).

Рис. 13. Сводная таблица на основе нескольких внешних таблиц

[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 7.

Читайте также: