Навчальна вправа: Імпорт даних у програмі Excel і створення моделі даних

Навчальна вправа: Імпорт даних у програмі Excel і створення моделі даних

Примітка.:  Ми хочемо надавати найновіший вміст довідки рідною мовою користувачів якомога швидше. Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була інформація корисною, унизу цієї сторінки. Для зручності цю статтю можна переглянути англійською мовою .

Короткий огляд.    Це перший посібник із серії, що знайомить вас із можливостями програми Excel і її вбудованими функціями поєднання даних і аналізу. Вивчаючи ці посібники, ви створите книгу Excel із нуля й уточните її, побудуєте модель даних, а потім створите дивовижні інтерактивні звіти, використовуючи надбудову Power View. Ці посібники створені спеціально, щоб продемонструвати функції й можливості бізнес-аналітики Microsoft у програмі 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. Виберіть посилання на завантаження файлів, ми використовуємо під час цього посібника ряду. Завантаження кожного з чотирьох файлів до розташування, яке легко зі спеціальними можливостями, наприклад завантаження або Мої документиабо створення нової папки:
    > База даних Access olympicmedals. accdb
    > Книга OlympicSports.xlsx Excel
    > Книга Excel Population. XLSX
    > Книга Excel discimage_table. XLSX

  2. Відкрийте пусту книгу в програмі Excel 2013.

  3. Натисніть кнопку дані > отримання зовнішніх даних > з Access. Стрічка змінить динамічно на основі ширини до книги, тому команди на стрічці може виглядати трохи нижче екранах. На першому екрані відображає стрічки, якщо книгу буде широкий, другий зображенні показано книги, яка зменшено зайнятися лише частину екрана.

    Імпорт даних із програми Access

    Імпорт даних із програми Access із невеликою стрічкою

  4. Виберіть файл olympicmedals. accdb, ви завантажили і натисніть кнопку Відкрити. Відкриється вікно нижче виберіть таблицю, відображення таблиці, що містяться в базі даних. Таблиці в базі даних, схожі на аркуші або таблиці Excel. Установіть прапорець дозволити Вибір кількох таблиць і виберіть пункт Усі таблиці. Натисніть кнопку OK.

    Вікно "Виділити таблицю"

  5. Відкриється вікно імпорт даних.

    Примітка.: Зверніть увагу, прапорець у нижній частині вікна, яке дає змогу Додати дані до моделі даних, показано на наступному екрані. Модель даних автоматично створено під час імпортування або одночасно працювати з кількох таблиць. Модель даних інтегрується таблиць, активувати широка аналізу за допомогою зведених таблиць, Power Pivotі Power View. Під час імпорту таблиць із бази даних, наявної бази даних зв'язки між цих таблиць використовується для створення моделі даних у програмі Excel. Модель даних прозорості у програмі Excel, але ви можете переглянути та змінити безпосередньо за допомогою надбудови Power Pivot . Модель даних обговорюється докладно далі в цьому посібнику.


    Виберіть параметр Звіт зведеної таблиці , який дає змогу імпортувати таблиці в програмі Excel і підготувати зведену таблицю, щоб аналізувати імпортовані таблиці і натисніть кнопку OK.

    Вікно "Імпорт даних"

  6. Коли дані імпортовано, зведена таблиця створюється з використанням імпортованих таблиць.

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

Коли дані імпортовано в програму Excel і модель даних автоматично створено, можна починати дослідження даних.

Дослідження даних за допомогою зведеної таблиці

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

Чотири області полів зведеної таблиці

Щоб визначити, до якої області слід перетягувати те чи інше поле, може знадобитися трохи поекспериментувати. Перетягувати можна довільну кількість полів із таблиць, аж доки зведена таблиця не набуде потрібного вигляду для належного представлення даних. Спробуйте перетягувати поля до різних областей зведеної таблиці. Це не впливає на базові дані.

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

  1. У списку Полів зведеної таблицірозгортання таблиці Medals , клацнувши стрілку поряд з ним. Знайдіть поле NOC_CountryRegion розгорнуто медалі таблиці і перетягніть його до області СТОВПЦІВ . НОК призначається національних Олімпійських комісій, тобто організаційний підрозділ для країни або регіону.

  2. Потім перетягніть поле Discipline із таблиці Disciplines до області РЯДКИ.

  3. Відфільтруймо спортивні дисципліни, щоб відображалися лише п’ять видів спорту: Archery, Diving, Fencing, Figure Skating і Speed Skating. Це можна зробити в області Поля зведеної таблиці або за допомогою фільтра Позначки рядків у самій зведеній таблиці.

    1. Клацніть будь-де у зведеній таблиці й переконайтеся, що вибрано зведену таблицю Excel. У списку Поля зведеної таблиці, у якому розгорнуто таблицю Disciplines, наведіть вказівник на поле Discipline. Поруч із цим полем відобразиться стрілка розкривного списку. Клацніть розкривний список, зніміть прапорець (виділити все), щоб скасувати все виділення, прокрутіть униз і встановіть прапорці Archery, Diving, Fencing, Figure Skating і Speed Skating. Натисніть кнопку OK.

    2. Або в розділі Позначки рядків зведеної таблиці клацніть стрілку розкривного списку поруч із написом Позначки рядків у зведеній таблиці, зніміть прапорець (виділити все), щоб скасувати все виділення, прокрутіть униз і встановіть прапорці Archery, Diving, Fencing, Figure Skating і Speed Skating. Натисніть кнопку OK.

  4. У списку Поля зведеної таблиці перетягніть поле Medal із таблиці Medals до області ЗНАЧЕННЯ. Оскільки значення мають бути числові, програма Excel автоматично змінює поле Medal на поле Підрахунок для Medal.

  5. У таблиці Medals знову виберіть поле Medal і перетягніть його до області ФІЛЬТРИ.

  6. Відфільтруймо зведену таблицю, щоб у ній відображалися лише ті країни та регіони, які отримали загалом більше 90 медалей. Ось як це зробити:

    1. У зведеній таблиці клацніть стрілку розкривного списку праворуч від напису Позначки стовпців.

    2. Виберіть команду Фільтри значень, а потім – пункт Більше….

    3. Введіть 90 на останньому полі (праворуч). Натисніть кнопку OK.
      Вікно фільтра значень

Зведена таблиця виглядатиме так:

Оновлена зведена таблиця

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

Але що робити, якщо дані походять із різних джерел або імпортуються не одночасно? Зазвичай можна створити зв’язки з новими даними за відповідними стовпцями. Далі ми імпортуємо додаткові таблиці та навчимося створювати нові зв’язки.

Імпорт даних з електронної таблиці

Тепер давайте імпортуємо дані з іншого джерела (цього разу це буде наявна книга), а потім укажемо зв’язки між наявними та новими даними. Зв’язки дають змогу аналізувати колекції даних у програмі Excel і створювати цікаві та глибокі графічні відображення на основі імпортованих даних.

Давайте спочатку створимо пустий аркуш, а потім імпортуємо дані з книги Excel.

  1. Вставте новий аркуш у програмі Excel і назвіть його Sports.

  2. Перейдіть до папки, що містить завантажені файли зі зразками даних, і відкрийте файл OlympicSports.xlsx.

  3. Виділіть і скопіюйте дані на аркуші Sheet1. Якщо виділено будь-яку клітинку з даними, наприклад A1, можна натиснути клавіші Ctrl+A, щоб виділити всі суміжні дані. Закрийте книгу OlympicSports.xlsx.

  4. На аркуші Sports установіть курсор у клітинку A1 і вставте дані.

  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 установіть курсор у клітинку A1 аркуша Hosts і вставте дані.

  2. Відформатуйте дані як таблицю. Як описано вище в цьому посібнику, щоб відформатувати дані як таблицю, натисніть клавіші Ctrl+T або клацніть елементи ОСНОВНЕ > Формат таблиці. Оскільки дані містять заголовки, установіть прапорець Таблиця із заголовками у вікні Створення таблиці, яке відобразиться.

  3. Назвіть таблицю. У розділі РОБОТА З ТАБЛИЦЯМИ > КОНСТРУКТОР > Властивості знайдіть поле Ім’я таблиці та введіть у ньому Hosts.

  4. Виділіть стовпець Edition, а потім на вкладці ОСНОВНЕ відформатуйте його як Числовий із нульовою (0) кількістю десяткових знаків.

  5. Збережіть книгу. Книга виглядатиме так:

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

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

Створення зв’язку між імпортованими даними

Поля з імпортованих таблиць можна використовувати у зведеній таблиці відразу. Якщо програмі Excel не вдалося визначити, як слід включати поле у зведену таблицю, потрібно установити зв’язок із наявною моделлю даних. Далі розповідається про те, як створити зв’язок між даними, імпортованими з різних джерел.

  1. На аркуші Аркуш1у верхній частиніПоля зведеної таблиці, натисніть кнопкувсе , щоб переглянути повний список доступних таблиць, як показано на наступному екрані.
    Клацання елемента "Усі" в області "Поля зведеної таблиці" для перегляду всіх доступних таблиць

  2. Прокрутіть список до щойно доданих таблиць.

  3. Розгорніть видів спорту і виберіть пункт видів спорту , щоб додати його до зведеної таблиці. Зверніть увагу, що програма Excel пропонує створити зв'язок, як показано на наступному екрані.
    Запит "СТВОРИТИ..." для створення зв’язку в області "Поля зведеної таблиці"

    Це сповіщення відбувається тому, що ви використовували поля з таблиці, яка не є частиною основної моделі даних. Один зі способів додати таблицю до моделі даних – це створення зв'язку таблиці, яка вже є в моделі даних. Щоб створити зв'язок, один із таблиці має бути стовпець унікальних, має повторюватися, значень. Зразки даних імпортовані з бази даних таблиці Disciplines містить поле з коди видів спорту, називається значення SportID. Ці самі коди видів спорту присутні як поля в ми імпорту даних Excel. Створення зв'язку.

  4. Натисніть кнопку Створити... у виділеному Поля зведеної таблиці , щоб відкрити діалогове вікно Створити зв'язок , як показано на наступному екрані.

    Вікно "Створити зв’язок"

  5. У полі Таблиця виберіть у розкривному списку значення Disciplines.

  6. У полі Стовпець (зовнішній) виберіть значення SportID.

  7. У полі Пов’язана таблиця виберіть значення Sports.

  8. У полі Пов’язаний стовпець (основний) виберіть значення SportID.

  9. Натисніть кнопку OK.

Зведена таблиця зміни, щоб вони відображали новий рівень. Але зведену таблицю виглядають неналежним зовсім ще, через впорядкування полів в області РЯДКІВ . Дисципліна є підкатегоріями заданого спорту, але після того, як ми впорядковані дисципліною над видів спорту в області РЯДКІВ , його не організовано належним чином. На наступному екрані показано цей неналежним упорядкуванням..
Зведена таблиця з неналежним упорядкуванням.

  1. В області РЯДКІВ переміщення видів спорту над дисципліною. Це набагато краще, і дані спосіб побачити, як показано на наступному екрані відображаються у зведеній таблиці.

    Зведена таблиця з виправленим упорядкуванням

Програма Excel автоматично створює модель даних, яку можна використовувати в будь-якій зведеній таблиці, Power Pivot або будь-якому звіті Power View в книзі. Зв’язки між таблицями – це основа моделі даних, і саме вони визначають переходи та шляхи обчислення.

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

Контрольна точка й вікторина

Стислий огляд вивченого матеріалу

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

Щоб забезпечити узгоджену роботу даних, ви створили між таблицями зв’язки, завдяки яким зіставляються рядки в програмі Excel. Ви також дізналися, що, щоб створювати зв’язки й шукати пов’язані рядки, стовпці однієї таблиці мають відповідати даним в іншій таблиці.

Тепер можна переходити до наступного посібника з цієї серії. Ось посилання на нього:

Розширення зв’язків моделі даних за допомогою Excel 2013, Power Pivot і DAX

ВІКТОРИНА

Хочете перевірити, наскільки добре запам’ятали пройдений матеріал? Спробуйте! Наведена нижче вікторина стосується функцій, можливостей і вимог, описаних у цьому посібнику. Відповіді наведено внизу сторінки. Бажаємо успіхів!

Запитання 1. Чому важливо перетворювати імпортовані дані на таблиці?

А. Їх не обов’язково перетворювати на таблиці, тому що всі імпортовані дані автоматично перетворюються на таблиці.

Б. Якщо перетворити імпортовані дані на таблиці, їх буде виключено з моделі даних. Лише якщо виключити їх із моделі даних, вони стають доступними у зведених таблицях, а також надбудовах Power Pivot і Power View.

В. Якщо перетворити імпортовані дані на таблиці, їх можна включити до моделі даних і вони стануть доступними у зведених таблицях, а також надбудовах Power Pivot і Power View.

Г. Імпортовані дані не можна перетворити на таблиці.

Запитання 2. Дані з яких із наведених нижче джерел даних можна імпортувати до програми Excel і включити в модель даних?

А. Бази даних Access, а також багато інших баз даних.

Б. Наявні файли Excel.

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

Г. Усі перелічені вище.

Запитання 3. Що станеться, якщо змінити порядок полів у чотирьох областях у вікні "Поля зведеної таблиці"?

А. Нічого. Додавши поля до областей у вікні "Поля зведеної таблиці", змінити їх порядок не можна.

Б. Формат зведеної таблиці зміниться відповідно до порядку полів, але це не вплине на базові дані.

В. Формат зведеної таблиці зміниться відповідно до порядку полів, а всі базові дані буде назавжди змінено.

Г. Базові дані буде змінено, у результаті чого буде створено нові набори даних.

Запитання 4. Що потрібно, щоб створити зв’язок між таблицями?

А. Жодна таблиця не може містити стовпці з унікальними значеннями які не повторюються.

Б. Одна з таблиць має не входити до книги Excel.

В. Стовпці не має бути перетворено на таблиці.

Г. Усі попередні твердження хибні.

Відповіді на вікторину

  1. Правильна відповідь: В

  2. Правильна відповідь: Г

  3. Правильна відповідь: Б

  4. Правильна відповідь: Г

Примітки.: Дані й зображення, використані в цій серії посібників:

  • інформація про Олімпійські ігри, надана компанією Guardian News & Media Ltd;

  • зображення прапорів зі сторінки Factbook веб-сайту ЦРУ (cia.gov);

  • дані про чисельність населення з веб-сайту Світового банку (worldbank.org);

  • піктограми олімпійських видів спорту, надані користувачами Thadius856 і Parutakupiu.

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×