Учебник: импорт данных в Excel и создание модели данных

Аннотация: Это первый учебник в серии, предназначенной для знакомства с Excel и встроенными функциями объединения данных и анализа. С помощью этой серии учебников вы научитесь создавать с нуля и совершенствовать рабочие книги Excel, строить модели данных и создавать удивительные интерактивные отчеты с использованием надстройки Power View. Эти учебники предназначены для демонстрации функций и возможностей Microsoft Business Intelligence в Excel, сводных таблицах, Power Pivot и Power View. 

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

Учебники этой серии

1. Импорт данных в Excel 2016 и создание модели данных
2. Расширение связей между моделями данных с помощью Excel, Power Pivot и DAX
3. Создание отчетов Power View на основе карт
4. Объединение интернет-данных и настройка параметров отчета Power View по умолчанию
5. Справка по Power Pivot
6. Создание впечатляющих отчетов Power View, часть 2

В этом учебнике вы начнете работу с пустой книги Excel.

Разделы учебника

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

В конце учебника есть тест, с помощью которого можно проверить свои знания.

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

Импорт данных из базы данных

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

Сначала загрузим данные из Интернета. Эти данные об олимпийских медалях являются базой данных Microsoft Access.

1. Перейдите по следующим ссылкам, чтобы скачать файлы, которые мы используем в этой серии уроков. Загрузите каждый из четырех файлов в легкодоступное расположение, например " Загрузки" или "Мои документы", либо в новую папку, которую создаете:
   > База данных Access OlympicMedals.accdb
   > OlympicSports.xlsx книга Excel
   > Population.xlsx книга Excel
   > DiscImage_table.xlsx книга Excel

2. Откройте в Excel пустую книгу.

3. Выберите пункт "Получить данные > из базы данных > Microsoft Access".> Лента динамически настраивается в зависимости от ширины книги, поэтому команды на ленте могут немного отличаться от показанных ниже.

   

4. Выберите скачанный файл OlympicMedals.accdb и нажмите "Импорт". Откроется следующее окно навигатора, в котором отображаются таблицы, найденные в базе данных. Таблицы в базе данных похожи на листы или таблицы в Excel. Установите флажок Выбрать несколько таблиц и выделите все таблицы. Затем нажмите кнопку "Загрузить > по".

   

5. Появится окно "Импорт данных".

   Примечание

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

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

   

6. После завершения импорта данных будет создана сводная таблица на основе импортированных таблиц.

   

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

Просмотр данных в сводной таблице

Просматривать импортированные данные удобнее всего с помощью сводной таблицы. В сводной таблице можно перетаскивать поля (похожие на столбцы в Excel) из таблиц (например, таблиц, импортированных из базы данных Access) в разные области, настраивая представление данных. Сводная таблица содержит четыре области: ФИЛЬТРЫ, СТОЛБЦЫ, СТРОКИ и ЗНАЧЕНИЯ.

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

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

1. В разделе Поля сводной таблицы разверните таблицу Medals, щелкнув расположенную рядом с ней стрелку. В развернутой таблице Medals найдите поле NOC_CountryRegion и перетащите его в область СТОЛБЦЫ. Аббревиатура NOC обозначает Национальный олимпийский комитет — организационную единицу уровня страны или региона.

2. Затем перетащите виды спорта из таблицы Disciplines в область СТРОКИ.

3. Давайте отфильтруем дисциплины, чтобы отображались только пять видов спорта: стрельба из лука (Archery), прыжки в воду (Diving), фехтование (Fencing), фигурное катание (Figure Skating) и конькобежный спорт (Speed Skating). Это можно сделать в области Поля сводной таблицы или в фильтре Метки строк в самой сводной таблице.

   1. Щелкните в любом месте сводной таблицы, чтобы убедиться, что выбрана сводная таблица Excel. В списке полей сводной таблицы , где развернута таблица "Дисциплины ", наведите курсор на соответствующее поле "Дисциплина", и справа от поля появится стрелка раскрывающегося списка. Щелкните раскрывающийся список, нажмите **(Выбрать все)**, чтобы удалить все выбранные параметры, затем прокрутите вниз и выберите стрельбу из лука, прыжки в воду, фехтование, фигурное катание и конькобежный спорт. Нажмите кнопку ОК.
   2. Либо щелкните в разделе сводной таблицы Метки строк стрелку раскрывающегося списка рядом с полем Метки строк, нажмите кнопку (Выбрать все), чтобы снять отметку со всех выбранных параметров, а затем прокрутите вниз и выберите пункты Archery, Diving, Fencing, Figure Skating и Speed Skating. Нажмите кнопку ОК.

4. В разделе Поля сводной таблицы перетащите поле Medal из таблицы Medals в область ЗНАЧЕНИЯ. Поскольку значения должны быть числовыми, Excel автоматически изменит поле Medal на Count of Medal.

5. В таблице Medals снова выберите поле Medal и перетащите его в область ФИЛЬТРЫ.

6. Давайте отфильтруем сводную таблицу таким образом, чтобы отображались только страны или регионы, завоевавшие более 90 медалей. Вот как это сделать.

   1. В сводной таблице щелкните стрелку раскрывающегося списка рядом с полем Метки столбцов.
   2. Выберите Фильтры по значению, а затем — Больше...
   3. Введите 90 в последнем поле (справа). Нажмите кнопку ОК.
      

Сводная таблица будет иметь следующий вид:

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

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

Импорт данных из таблицы

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

Начнем с создания пустого листа, а затем импортируем данные из книги Excel.

1. Вставьте новый лист Excel и назовите его Sports.

2. Перейдите к папке, в которой содержатся загруженные файлы образцов данных, и откройте файл OlympicSports.xlsx.

3. Выберите и скопируйте данные на листе Sheet1. При выборе ячейки с данными, например, ячейки А1, можно нажать клавиши Ctrl + A, чтобы выбрать все смежные данные. Закройте книгу OlympicSports.xlsx.

4. На листе Sports поместите курсор в ячейку А1 и вставьте данные.

5. Нажмите клавиши Ctrl + T, чтобы отформатировать выделенные данные в виде таблицы. Вы также можете отформатировать данные как таблицу на ленте, выбрав HOME > Форматировать как таблицу. Поскольку у данных есть заголовки, установите флажок Таблица с заголовками в окне Создание таблицы, как показано ниже.

   

   Форматирование данных в виде таблицы дает много преимуществ. Таблице можно присвоить имя, чтобы ее было легче идентифицировать. Вы также можете устанавливать отношения между таблицами, позволяя проводить исследование и анализ в сводных таблицах, Power Pivot и PowerView.

6. назовите таблицу; В свойствах конструктора > таблиц найдите поле "Имя таблицы " и введите "Спорт". Книга выглядит следующим образом:
   

7. Сохраните книгу.

Импорт данных с помощью копирования и вставки

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

1. Вставьте новый лист Excel и назовите его Hosts.
2. Выделите и скопируйте приведенную ниже таблицу вместе с заголовками.

City	NOC_CountryRegion	Alpha-2 Code	Выпуск	Season
Melbourne / Stockholm	AUS	AS	1956	Summer
Sydney	AUS	AS	2000	Summer
Innsbruck	AUT	AT	1964	Winter
Innsbruck	AUT	AT	1976	Winter
Antwerp	BEL	BE	1920	Summer
Antwerp	BEL	BE	1920	Winter
Montreal	CAN	CA	1976	Summer
Lake Placid	CAN	CA	1980	Winter
Calgary	CAN	CA	1988	Winter
St. Moritz	SUI	SZ	1928	Winter
St. Moritz	SUI	SZ	1948	Winter
Beijing	CHN	CH	2008	Summer
Berlin	GER	GM	1936	Summer
Garmisch-Partenkirchen	GER	GM	1936	Winter
Barcelona	ESP	СП	1992	Summer
Helsinki	FIN	FI	1952	Summer
Paris	FRA	FR	1900	Summer
Paris	FRA	FR	1924	Summer
Chamonix	FRA	FR	1924	Winter
Grenoble	FRA	FR	1968	Winter
Albertville	FRA	FR	1992	Winter
London	GBR	UK	1908	Summer
London	GBR	UK	1908	Winter
London	GBR	UK	1948	Summer
Munich	GER	DE	1972	Summer
Athens	GRC	GR	2004	Summer
Cortina d'Ampezzo	ITA	IT	1956	Winter
Rome	ITA	IT	1960	Summer
Turin	ITA	IT	2006	Winter
Tokyo	JPN	JA	1964	Summer
Sapporo	JPN	JA	1972	Winter
Nagano	JPN	JA	1998	Winter
Seoul	KOR	KS	1988	Summer
Mexico	MEX	MX	1968	Summer
Amsterdam	NED	NL	1928	Summer
Oslo	NOR	NO	1952	Winter
Lillehammer	NOR	NO	1994	Winter
Stockholm	SWE	SW	1912	Summer
St Louis	USA	US	1904	Summer
Los Angeles	USA	US	1932	Summer
Lake Placid	USA	US	1932	Winter
Squaw Valley	USA	US	1960	Winter
Moscow	URS	RU	1980	Summer
Los Angeles	USA	US	1984	Summer
Atlanta	USA	US	1996	Summer
Salt Lake City	USA	US	2002	Winter
Sarajevo	YUG	YU	1984	Winter

1. В Excel поместите курсор в ячейку А1 на листе Hosts и вставьте данные.
2. Отформатируйте данные в виде таблицы. Как описано ранее в этом руководстве, нажмите клавиши CTRL+T, чтобы отформатировать данные как таблицу, или выберите "HOME > Форматировать как таблицу". Поскольку у данных есть заголовки, установите флажок Таблица с заголовками в окне Создание таблицы.
3. Присвойте таблице имя. В свойствах конструктора > таблиц найдите поле "Имя таблицы " и введите "Узлы".
4. Выберите столбец Edition и на вкладке ГЛАВНАЯ задайте для него числовой формат с 0 десятичных знаков.
5. Сохраните книгу. Книга будет иметь следующий вид:

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

Создание связи между импортированными данными

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

1. На листе 1 в верхней частиполей сводной таблицы нажмите кнопку "Все", чтобы просмотреть полный список доступных таблиц, как показано на следующем экране.
   

2. Прокрутите список, чтобы увидеть новые таблицы, которые вы только что добавили.

3. Разверните пункт Sports и выберите пункт Sport, чтобы добавить в сводную таблицу. Обратите внимание, что Excel выдаст запрос на создание связи, как показано ниже.
   
    
   Это уведомление появляется по той причине, что вы использовали поля таблицы, которая не является частью базовой модели данных. Один из способов добавить таблицу в модель данных заключается в создании связи с таблицей, которая уже есть в модели данных. Для создания связи в одной из таблиц должен содержаться столбец уникальных, не повторяющихся значений. В образце данных в таблице Disciplines, импортированной из базы данных, содержится поле с кодами видов спорта SportID. Эти же коды присутствуют в виде поля в импортированных нами данных Excel. Попробуем создать связь.

4. Нажмите кнопку СОЗДАТЬ... в выделенной области Поля сводной таблицы, чтобы открыть диалоговое окно Создание связи, как показано на приведенном ниже снимке экрана.

   

5. В разделе Таблица выберите из раскрывающегося списка пункт Модель данных Таблица: дисциплины.

6. В области Столбец (чужой) выберите пункт SportID.

7. В связанной таблице выберите модель данных Таблица: Спорт.

8. В области Связанный столбец (первичный ключ) выберите пункт SportID.

9. Нажмите кнопку ОК.

Сводная таблица изменится с учетом новой связи. Но пока она имеет не совсем правильный вид из-за порядка полей в области СТРОКИ. Дисциплина — это подкатегория вида спорта, но поскольку мы расположили дисциплины выше видов спорта в области СТРОКИ, она не организована должным образом. Этот нежелательный порядок показан на приведенном ниже снимке экрана.

1. В области ROWS переместите «Спорт» выше «Дисциплина». Это намного лучше, и сводная таблица отображает данные так, как вы хотите их видеть, как показано на следующем экране.

   

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

В следующем учебнике " Расширение связей между моделями данных с помощью Excel, Power Pivot** и DAX**" вы опираетесь на полученные здесь знания и поэтапно расширяете модель данных с помощью мощной и визуальной надстройки Excel под названием Power Pivot. Вы также узнаете, как вычислять столбцы в таблице и использовать этот вычисляемый столбец, чтобы добавить в свою модель данных несвязанную таблицу.

Контрольная точка и тест

Повторение изученного материала

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

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

Вы готовы перейти к следующему учебнику этого цикла. Вот ссылка:

Учебник. Расширение связей модели данных с использованием Excel, Power Pivot и DAX

ТЕСТ

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

Вопрос 1. Почему так важно преобразовывать импортируемые данные в таблицы?

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

Б. При преобразовании импортированных данных в таблицы они будут исключены из модели данных. Они становятся доступными в сводных таблицах, Power Pivot и Power View только в том случае, если они исключены из модели данных.

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

D. Импортированные данные нельзя преобразовать в таблицы.

Вопрос 2. Какие из указанных ниже источников данных можно импортировать в Excel и включить в модель данных?

A. Базы данных Access и многие другие базы данных.

B. Существующие файлы Excel.

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

D. Все вышеперечисленное.

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

A. Ничего. После размещения полей в области полей сводной таблицы их порядок изменить нельзя.

B. Формат сводной таблицы изменится в соответствии с макетом, но это не повлияет на базовые данные.

C. Формат сводной таблицы изменится в соответствии с макетом, но при этом базовые данные будут изменены без возможности восстановления.

D. Базовые данные изменятся, что приведет к созданию новых наборов данных.

Вопрос 4. Что необходимо для создания связи между таблицами?

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

B. Таблица не должна быть частью книги Excel.

C. Столбцы не должны быть преобразованы в таблицы.

D. Ни один из вышеперечисленных ответов не является правильным.

Ответы на вопросы теста

1. Правильный ответ: C
2. Правильный ответ: D
3. Правильный ответ: B
4. Правильный ответ: D