Excel Power Pivot — загрузка данных

В этой главе мы научимся загружать данные в Power Pivot.

Вы можете загрузить данные в Power Pivot двумя способами:

• Загрузить данные в Excel и добавить их в модель данных

• Загружайте данные непосредственно в PowerPivot, заполняя модель данных, которая является базой данных PowerPivot.

Загрузить данные в Excel и добавить их в модель данных

Загружайте данные непосредственно в PowerPivot, заполняя модель данных, которая является базой данных PowerPivot.

Если вам нужны данные для Power Pivot, сделайте это вторым способом, даже если Excel даже не знает об этом. Это потому, что вы будете загружать данные только один раз, в сильно сжатом формате. Чтобы понять разницу, предположим, что вы загружаете данные в Excel, сначала добавляя их в модель данных, размер файла, скажем, 10 МБ.

Если вы загружаете данные в PowerPivot и, следовательно, в модель данных, пропуская дополнительный шаг Excel, размер вашего файла может составлять всего 1 МБ.

Источники данных, поддерживаемые Power Pivot

Вы можете либо импортировать данные в модель данных Power Pivot из различных источников данных, либо устанавливать соединения и / или использовать существующие соединения. Power Pivot поддерживает следующие источники данных —

• Реляционная база данных SQL Server

• База данных Microsoft Access

• Службы анализа SQL Server

• Службы отчетов SQL Server (SQL 2008 R2)

• Каналы данных ATOM

• Текстовые файлы

• Microsoft SQL Azure

• оракул

• Teradata

• Sybase

• Informix

• IBM DB2

• Связывание объектов и встраивание базы данных / Открытая связь с базой данных

• (OLEDB / ODBC) источники

• Файл Microsoft Excel

• Текстовый файл

Реляционная база данных SQL Server

База данных Microsoft Access

Службы анализа SQL Server

Службы отчетов SQL Server (SQL 2008 R2)

Каналы данных ATOM

Текстовые файлы

Microsoft SQL Azure

оракул

Teradata

Sybase

Informix

IBM DB2

Связывание объектов и встраивание базы данных / Открытая связь с базой данных

Файл Microsoft Excel

Текстовый файл

Загрузка данных непосредственно в PowerPivot

Чтобы загрузить данные непосредственно в Power Pivot, выполните следующее:

• Откройте новую рабочую книгу.

• Нажмите на вкладку POWERPIVOT на ленте.

• Нажмите «Управление» в группе «Модель данных».

Откройте новую рабочую книгу.

Нажмите на вкладку POWERPIVOT на ленте.

Нажмите «Управление» в группе «Модель данных».

Откроется окно PowerPivot. Теперь у вас есть два окна — окно книги Excel и окно PowerPivot для Excel, которое связано с вашей книгой.

• Перейдите на вкладку « Главная » в окне PowerPivot.

• Нажмите Из базы данных в группе Получить внешние данные.

• Выберите из доступа .

Перейдите на вкладку « Главная » в окне PowerPivot.

Нажмите Из базы данных в группе Получить внешние данные.

Выберите из доступа .

Появится мастер импорта таблиц.

• Перейдите к файлу базы данных Access.

• Укажите имя дружественного соединения.

• Если база данных защищена паролем, заполните эти данные также.

Перейдите к файлу базы данных Access.

Укажите имя дружественного соединения.

Если база данных защищена паролем, заполните эти данные также.

Нажмите кнопку Далее →. Мастер импорта таблиц отображает варианты выбора способа импорта данных.

Нажмите Выбрать из списка таблиц и представлений, чтобы выбрать данные для импорта.

Нажмите кнопку Далее →. Мастер импорта таблиц отображает таблицы и представления в выбранной вами базе данных Access.

Установите флажок Медали.

Как вы можете заметить, вы можете выбрать таблицы, установив флажки, просмотреть и отфильтровать таблицы перед добавлением в сводную таблицу и / или выбрать связанные таблицы.

Нажмите кнопку « Просмотр и фильтр» .

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

• Нажмите ОК.

• Нажмите кнопку « Выбрать связанные таблицы» .

• Power Pivot проверяет, какие другие таблицы связаны с выбранной таблицей медалей, если существует отношение.

Нажмите ОК.

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

Power Pivot проверяет, какие другие таблицы связаны с выбранной таблицей медалей, если существует отношение.

Вы можете видеть, что Power Pivot обнаружил, что таблица Disciplines связана с таблицей Медали, и выбрал ее. Нажмите Готово.

Отображается мастер импорта таблиц — Импорт и отображается состояние импорта. Это займет несколько минут, и вы можете остановить импорт, нажав кнопку « Остановить импорт» .

После импорта данных мастер импорта таблиц отображает — Успешно и показывает результаты импорта, как показано на снимке экрана ниже. Нажмите Закрыть.

Power Pivot отображает две импортированные таблицы в двух вкладках.

Вы можете прокручивать записи (строки таблицы), используя стрелки записи под вкладками.

Мастер импорта таблиц

В предыдущем разделе вы узнали, как импортировать данные из Access с помощью мастера импорта таблиц.

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

Нажмите Из других источников в окне Power Pivot.

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

Вы можете прокрутить список подключений в мастере импорта таблиц, чтобы узнать, какие подключения к данным совместимы с Power Pivot.

• Прокрутите вниз до текстовых файлов.

• Выберите файл Excel .

Прокрутите вниз до текстовых файлов.

Выберите файл Excel .

• Нажмите кнопку Далее →. Появится мастер импорта таблиц — подключиться к файлу Microsoft Excel.

• Перейдите к файлу Excel в поле «Путь к файлу Excel».

• Установите флажок — Использовать первую строку в качестве заголовков столбцов .

Нажмите кнопку Далее →. Появится мастер импорта таблиц — подключиться к файлу Microsoft Excel.

Перейдите к файлу Excel в поле «Путь к файлу Excel».

Установите флажок — Использовать первую строку в качестве заголовков столбцов .

• Нажмите кнопку Далее →. Отобразится мастер импорта таблиц: выберите таблицы и представления .

• Установите флажок Каталог продукции $ . Нажмите кнопку Готово .

Нажмите кнопку Далее →. Отобразится мастер импорта таблиц: выберите таблицы и представления .

Установите флажок Каталог продукции $ . Нажмите кнопку Готово .

Вы увидите следующее сообщение об успехе . Нажмите Закрыть.

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

Открытие существующих подключений

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

Нажмите «Существующие подключения» в окне PowerPivot.

Откроется диалоговое окно «Существующие подключения». Выберите Excel Sales Data из списка.

Нажмите кнопку Открыть. Мастер импорта таблиц отображает таблицы и представления.

Выберите таблицы, которые вы хотите импортировать, и нажмите Готово .

Выбранные пять таблиц будут импортированы. Нажмите Закрыть .

Вы можете видеть, что пять таблиц добавляются в Power Pivot, каждая на новой вкладке.

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

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

Вы можете связать таблицу Excel с Power Pivot в несколько шагов следующим образом:

• Создайте таблицу Excel с данными.

• Нажмите вкладку POWERPIVOT на ленте.

• Нажмите Добавить в модель данных в группе Таблицы.

Создайте таблицу Excel с данными.

Нажмите вкладку POWERPIVOT на ленте.

Нажмите Добавить в модель данных в группе Таблицы.

Таблица Excel связана с соответствующей таблицей данных в PowerPivot.

Вы можете видеть, что Table Tools с вкладкой — Связанная таблица добавляется в окно Power Pivot. Если вы нажмете Перейти к таблице Excel , вы переключитесь на лист Excel. Если вы нажмете « Управление» , вы вернетесь к связанной таблице в окне Power Pivot.

Вы можете обновить связанную таблицу автоматически или вручную.

Обратите внимание, что вы можете связать таблицу Excel, только если она присутствует в книге с Power Pivot. Если у вас есть таблицы Excel в отдельной книге, их нужно загрузить, как описано в следующем разделе.

Загрузка из файлов Excel

Если вы хотите загрузить данные из книг Excel, имейте в виду следующее:

• Power Pivot считает другую книгу Excel базой данных, и импортируются только рабочие листы.

• Power Pivot загружает каждый лист в виде таблицы.

• Power Pivot не может распознавать отдельные таблицы. Следовательно, Power Pivot не может распознать, если на листе есть несколько таблиц.

• Power Pivot не может распознать любую дополнительную информацию, кроме таблицы на листе.

Power Pivot считает другую книгу Excel базой данных, и импортируются только рабочие листы.

Power Pivot загружает каждый лист в виде таблицы.

Power Pivot не может распознавать отдельные таблицы. Следовательно, Power Pivot не может распознать, если на листе есть несколько таблиц.

Power Pivot не может распознать любую дополнительную информацию, кроме таблицы на листе.

Следовательно, держите каждую таблицу в отдельном рабочем листе.

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

• Нажмите Из других источников в группе Получить внешние данные в окне Power Pivot.

• Выполните действия, указанные в разделе «Мастер импорта таблиц».

Нажмите Из других источников в группе Получить внешние данные в окне Power Pivot.

Выполните действия, указанные в разделе «Мастер импорта таблиц».

Ниже приведены различия между связанными таблицами Excel и импортированными таблицами Excel.

• Связанные таблицы должны находиться в той же книге Excel, в которой хранится база данных Power Pivot. Если данные уже существуют в других книгах Excel, нет смысла использовать эту функцию.

• Функция импорта в Excel позволяет загружать данные из разных книг Excel.

• Загрузка данных из книги Excel не создает связь между двумя файлами. Power Pivot создает только копию данных при импорте.

• При обновлении исходного файла Excel данные в Power Pivot обновляться не будут. Вам нужно либо установить автоматический режим обновления, либо обновить данные вручную, на вкладке Связанная таблица окна Power Pivot.

Связанные таблицы должны находиться в той же книге Excel, в которой хранится база данных Power Pivot. Если данные уже существуют в других книгах Excel, нет смысла использовать эту функцию.

Функция импорта в Excel позволяет загружать данные из разных книг Excel.

Загрузка данных из книги Excel не создает связь между двумя файлами. Power Pivot создает только копию данных при импорте.

При обновлении исходного файла Excel данные в Power Pivot обновляться не будут. Вам нужно либо установить автоматический режим обновления, либо обновить данные вручную, на вкладке Связанная таблица окна Power Pivot.

Загрузка из текстовых файлов

Один из популярных стилей представления данных имеет формат, известный как значения, разделенные запятыми (csv). Каждая строка / запись данных представлена ​​текстовой строкой, в которой столбцы / поля разделены запятыми. Многие базы данных предоставляют возможность сохранения в файл формата csv.

Если вы хотите загрузить CSV-файл в Power Pivot, вы должны использовать опцию Текстовый файл. Предположим, у вас есть следующий текстовый файл в формате CSV —

• Откройте вкладку PowerPivot.

• Перейдите на вкладку «Главная» в окне PowerPivot.

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

• Прокрутите вниз до текстовых файлов.

Откройте вкладку PowerPivot.

Перейдите на вкладку «Главная» в окне PowerPivot.

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

Прокрутите вниз до текстовых файлов.

• Нажмите Текстовый файл.

• Нажмите кнопку Далее →. На экране появится Мастер импорта таблиц — Подключиться к плоскому файлу.

• Найдите текстовый файл в поле «Путь к файлу». Файлы csv обычно имеют первую строку, представляющую заголовки столбцов.

• Установите флажок Использовать первую строку в качестве заголовков столбцов, если в первой строке есть заголовки.

• В поле Разделитель столбцов по умолчанию используется запятая (,), но в случае, если в вашем текстовом файле есть какой-либо другой оператор, такой как Tab, точка с запятой, пробел, двоеточие или вертикальная черта, выберите этот оператор.

Нажмите Текстовый файл.

Нажмите кнопку Далее →. На экране появится Мастер импорта таблиц — Подключиться к плоскому файлу.

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

Установите флажок Использовать первую строку в качестве заголовков столбцов, если в первой строке есть заголовки.

В поле Разделитель столбцов по умолчанию используется запятая (,), но в случае, если в вашем текстовом файле есть какой-либо другой оператор, такой как Tab, точка с запятой, пробел, двоеточие или вертикальная черта, выберите этот оператор.

Как вы можете заметить, есть предварительный просмотр вашей таблицы данных. Нажмите Готово.

Power Pivot создает таблицу данных в модели данных.

Загрузка из буфера обмена

Предположим, у вас есть данные в приложении, которые не распознаются Power Pivot как источник данных. Чтобы загрузить эти данные в Power Pivot, у вас есть два варианта:

• Скопируйте данные в файл Excel и используйте файл Excel в качестве источника данных для Power Pivot.

• Скопируйте данные, чтобы они были в буфере обмена, и вставьте их в Power Pivot.

Скопируйте данные в файл Excel и используйте файл Excel в качестве источника данных для Power Pivot.

Скопируйте данные, чтобы они были в буфере обмена, и вставьте их в Power Pivot.

Вы уже изучили первый вариант в предыдущем разделе. И это предпочтительнее второго варианта, как вы найдете в конце этого раздела. Однако вы должны знать, как копировать данные из буфера обмена в Power Pivot.

Предположим, у вас есть данные в текстовом документе следующим образом:

Word не является источником данных для Power Pivot. Поэтому выполните следующее —

• Выберите таблицу в документе Word.

• Скопируйте и вставьте его в окно PowerPivot.

Выберите таблицу в документе Word.

Скопируйте и вставьте его в окно PowerPivot.

Откроется диалоговое окно Paste Preview .

• Дайте имя в виде таблицы Word-Employee .

• Установите флажок Использовать первую строку в качестве заголовков столбцов и нажмите ОК.

Дайте имя в виде таблицы Word-Employee .

Установите флажок Использовать первую строку в качестве заголовков столбцов и нажмите ОК.

Данные, скопированные в буфер обмена, будут вставлены в новую таблицу данных в Power Pivot с вкладкой — Таблица Word-Employee.

Предположим, вы хотите заменить эту таблицу новым содержанием.

• Скопируйте таблицу из Word.

• Нажмите Вставить и заменить.

Скопируйте таблицу из Word.

Нажмите Вставить и заменить.

Откроется диалоговое окно Paste Preview. Проверьте содержимое, которое вы используете для замены.

Нажмите ОК.

Как вы можете видеть, содержимое таблицы данных в Power Pivot заменяется содержимым в буфере обмена.

Предположим, вы хотите добавить две новые строки данных в таблицу данных. В таблице в документе Word у вас есть две строки новостей.

• Выберите две новые строки.

• Нажмите Копировать.

• Нажмите Вставить приложение в окне Power Pivot. Откроется диалоговое окно Paste Preview.

• Проверьте содержимое, которое вы используете для добавления.

Выберите две новые строки.

Нажмите Копировать.

Нажмите Вставить приложение в окне Power Pivot. Откроется диалоговое окно Paste Preview.

Проверьте содержимое, которое вы используете для добавления.

Нажмите OK, чтобы продолжить.

Как вы можете заметить, содержимое таблицы данных в Power Pivot добавляется к содержимому в буфере обмена.

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

Это из-за следующих причин —

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

• В файле Word имеется информация об отслеживании, например, когда данные заменяются и когда данные добавляются. Однако скопировать эту информацию в Power Pivot невозможно. Если вы сначала скопируете данные в файл Excel, вы можете сохранить эту информацию для последующего использования.

• При копировании из буфера обмена, если вы хотите добавить некоторые комментарии, вы не можете сделать это. Если вы сначала скопируете в файл Excel, вы можете вставить в таблицу Excel комментарии, которые будут связаны с Power Pivot.

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

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

В файле Word имеется информация об отслеживании, например, когда данные заменяются и когда данные добавляются. Однако скопировать эту информацию в Power Pivot невозможно. Если вы сначала скопируете данные в файл Excel, вы можете сохранить эту информацию для последующего использования.

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

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

Обновление данных в Power Pivot

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

Если вы хотите обновить только одну таблицу данных в Power Pivot, выполните следующие действия:

• Нажмите на вкладку таблицы данных.

• Нажмите Обновить.

• Выберите Обновить из выпадающего списка.

Нажмите на вкладку таблицы данных.

Нажмите Обновить.

Выберите Обновить из выпадающего списка.

Если вы хотите обновить все таблицы данных в Power Pivot, выполните следующие действия:

Нажмите кнопку Обновить.

Выберите Обновить все из выпадающего списка.