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

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

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

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

Примечание: В этой статье описаны моделей данных в Excel 2013. Однако же моделированию данных и возможности Power Pivot, представленные в Excel 2013 также применять для Excel 2016.

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

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

  1. Импорт данных в Excel 2013 и создание модели данных

  2. Расширение связей модели данных с помощью Excel, Power Pivot и DAX

  3. Создание отчетов Power View на основе карт

  4. Объединение интернет-данных и настройка параметров отчета Power View по умолчанию

  5. Создание впечатляющих отчетов Power View, часть 1

  6. Создание впечатляющих отчетов Power View, часть 2

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Импорт данных из Access с маленькой лентой

  4. Выберите загруженный файл OlympicMedals.accdb и нажмите кнопку Открыть. Откроется окно Выбор таблицы ниже таблиц в базе данных. Таблицы в базе данных похожи на листы или таблицы в Excel. Установите флажок Разрешить выбор нескольких таблиц и выберите все таблицы. Нажмите кнопку ОК.

    Окно "Выбор таблицы"

  5. Откроется окно импорта данных.

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


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

    Окно "Импорт данных"

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

    Пустая сводная таблица

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

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

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

Четыре области полей сводной таблицы

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

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

  1. В Полях сводной таблицы разверните таблице , щелкнув стрелку рядом с ним. Найдите поле NOC_CountryRegion в развернутом таблице и перетащите его в область СТОЛБЦОВ. Центра управления СЕТЬЮ означает национальный Олимпийских комитетов, являющееся подразделение для страны или региона.

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

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

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

    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, чтобы отформатировать данные как таблицу. Также можно отформатировать данные как таблицу на ленте, выбрав ГЛАВНАЯ > форматировать как таблицу. Так как данные с заголовками, выберите Таблица с заголовками в окне Создание таблицы, которая появляется, как показано ниже.

    Окно "Создание таблицы"

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

  6. Назовите таблицу. В РАБОТА с ТАБЛИЦАМИ > КОНСТРУКТОР > Свойства, найдите поле Имя таблицы и введите Sports. Книга будет иметь вид ниже.
    Присвоение имени таблице в Excel

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

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

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

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

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

City

NOC_CountryRegion

Alpha-2 Code

Edition

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

SP

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 или выберите пункт меню ГЛАВНАЯ > Форматировать как таблицу. Поскольку у данных есть заголовки, установите флажок Таблица с заголовками в окне Создание таблицы.

  3. Присвойте таблице имя. На вкладках РАБОТА С ТАБЛИЦАМИ > КОНСТРУКТОР > Свойства найдите поле Имя таблицы и введите слово Hosts.

  4. Выберите столбец Edition и на вкладке ГЛАВНАЯ задайте для него числовой формат с 0 десятичных знаков.

  5. Сохраните книгу. Книга будет иметь следующий вид:

Основная таблица

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

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

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

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

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

  3. Разверните виды спорта и выберите видов спорта , чтобы добавить его в сводной таблице. Обратите внимание на то, что Excel предложит создать связи, как показано на приведенном ниже снимке экрана.
    Запрос на СОЗДАНИЕ... связи в полях сводной таблицы

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

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

    Окно "Создание связи"

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

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

  7. В области Связанная таблица выберите пункт Sports.

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

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

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

  1. Переход в область СТРОК видов спорта выше Discipline. Это гораздо лучше и отображение в сводной таблице как вы хотите просмотреть, как показано на приведенном ниже снимке экрана.

    Сводная таблица с правильным порядком

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

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

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

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

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

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

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

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

ТЕСТ

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

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

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

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

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

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. Правильный ответ: Б

  4. Правильный ответ: D

Примечания: Ниже перечислены источники данных и изображений в этом цикле учебников.

  • Набор данных об Олимпийских играх © Guardian News & Media Ltd.

  • Изображения флагов из справочника CIA Factbook (cia.gov).

  • Данные о населении из документов Всемирного банка (worldbank.org).

  • Авторы эмблем олимпийских видов спорта Thadius856 и Parutakupiu.

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×