Абстрактні: Це перший навчальний посібник із серії, призначений для ознайомлення та зручного використання Програми Excel і його вбудованих функцій пюре та аналізу даних. Вивчаючи ці посібники, ви створите книгу Excel із нуля й уточните її, побудуєте модель даних, а потім створите дивовижні інтерактивні звіти, використовуючи надбудову Power View. Ці посібники розроблено для демонстрації функцій і можливостей бізнес-аналітики Microsoft у програмах Excel, зведених таблицях, Power Pivot і Power View.
У цих навчальних посібниках ви дізнаєтеся, як імпортувати й досліджувати дані в програмі Excel, створювати й уточнювати моделі даних за допомогою надбудови Power Pivot, а також створювати інтерактивні звіти за допомогою надбудови Power View, які можна публікувати, захищати та надавати до них спільний доступ.
До цієї серії входять такі посібники:
- Імпорт даних до Excel 2016 та створення моделі даних
- Розширення зв'язків моделі даних за допомогою excel, Power Pivot і DAX
- Створення звітів Power View на основі карт
- Включення даних з Інтернету й установлення стандартних параметрів для звітів Power View
- Довідка Power Pivot
- Створення вражаючих звітів Power View. Частина 2
У цьому посібнику все починається зі створення нової книги Excel.
Зміст посібника:
- Імпорт даних із бази даних
- Імпорт даних з електронної таблиці
- Копіювання та вставлення даних для імпорту
- Створення зв’язку між імпортованими даними
- Контрольна точка й вікторина
Наприкінці цього посібника пропонується вікторина, за допомогою якої можна перевірити свої знання.
У цій серії навчальних посібників використовуються дані з описом олімпійських медалей, країн, що приймають олімпійські ігри, а також різноманітних олімпійських спортивних змагань. Ми пропонуємо вам пройти через кожен підручник по порядку.
Імпорт даних із бази даних
У цьому посібнику все починається зі створення пустої книги. Ціль цього розділу – встановити зв’язок із зовнішнім джерелом даних та імпортувати ці дані в програму Excel для подальшого аналізу.
Спочатку завантажте дані з Інтернету. Ці дані містять опис олімпійських медалей і представлені у вигляді бази даних Microsoft Access.
Вибирайте наведені нижче посилання, щоб завантажити файли, які використовуються в цій серії посібників. Завантажте кожен із чотирьох файлів до розташування, яке легко доступні, наприклад "Завантаження " або "Мої документи", або до нової папки, яку ви створюєте:
> База даних Access OlympicMedals.accdb
> OlympicSports.xlsx книгу Excel
> Population.xlsx книгу Excel
> DiscImage_table.xlsx книгу ExcelВ Excel відкрийте пусту книгу.
Виберіть пункт Отримати дані >> з бази даних > із бази даних Microsoft Access. Стрічка динамічно змінюється залежно від ширини книги, тому команди на стрічці можуть дещо відрізнятися від наведеного нижче екрана.
Виберіть завантажений файл OlympicMedals.accdb та натисніть кнопку Імпорт. З'явиться вікно навігатора нижче, де відображаються таблиці, знайдені в базі даних. Таблиці бази даних схожі на аркуші або таблиці в програмі Excel. Установіть прапорець Вибрати кілька таблиць і виділіть усі таблиці. Потім натисніть кнопку Завантажити > до.
Відобразиться діалогове вікно "Імпорт даних".
Примітка.
Зверніть увагу на прапорець у нижній частині вікна, який дає змогу додати ці дані до моделі даних, як показано на наступному екрані. Модель даних створюється автоматично під час імпорту або роботи з кількома таблицями одночасно. Модель даних інтегрує таблиці, активуючи великий аналіз за допомогою зведених таблиць, Power Pivot і Power View. Під час імпорту таблиць із бази даних наявні зв'язки між ними використовуються для створення моделі даних у програмі Excel. Модель даних прозора в Excel, але її можна переглядати та змінювати безпосередньо за допомогою надбудови Power Pivot. Модель даних обговорюється докладніше пізніше в цьому посібнику.
Виберіть параметр Звіт зведеної таблиці, який дає змогу імпортувати таблиці в програму Excel і підготувати зведену таблицю, щоб аналізувати імпортовані таблиці, і натисніть кнопку OK.
Коли дані імпортовано, з імпортованих таблиць створюється зведена таблиця.
Коли дані імпортовано в програму Excel і модель даних автоматично створено, можна починати дослідження даних.
Дослідження даних за допомогою зведеної таблиці
Використовуючи зведену таблицю, можна легко дослідити імпортовані дані. У зведеній таблиці перетягніть поля (схожі на стовпці в Excel) з таблиць (наприклад, щойно імпортованих із бази даних Access) до різних областей зведеної таблиці, щоб настроїти спосіб представлення даних. Зведена таблиця складається з чотирьох областей: ФІЛЬТРИ, СТОВПЦІ, РЯДКИ та ЗНАЧЕННЯ.
Щоб визначити, до якої області слід перетягувати поле, може знадобитися кілька експериментів. З таблиць можна перетягнути скільки завгодно полів, доки зведена таблиця не відобразить дані, як їх потрібно переглянути. Не соромтеся досліджувати, перетягуючи поля до різних областей зведеної таблиці; це не вплине на базові дані під час упорядкування полів у зведеній таблиці.
Розгляньмо дані про олімпійські медалі у зведеній таблиці, починаючи з олімпійських медалістів, упорядкованих за спортивними дисциплінами, типами медалей і країнами або регіонами спортсменів.
У вікні Поля зведеної таблиці розгорніть таблицю Medals, клацнувши розташовану поруч із нею стрілку. Знайдіть поле NOC_CountryRegion у розгорнутій таблиці Medals і перетягніть його до області СТОВПЦІ. NOC – це Національний олімпійський комітет (National Olympic Committees), організаційний підрозділ країни або регіону.
Потім перетягніть поле Discipline із таблиці Disciplines до області РЯДКИ.
Відфільтруймо спортивні дисципліни, щоб відображалися лише п’ять видів спорту: Archery, Diving, Fencing, Figure Skating і Speed Skating. Це можна зробити в області Поля зведеної таблиці або за допомогою фільтра Позначки рядків у самій зведеній таблиці.
- Клацніть будь-де у зведеній таблиці, щоб переконатися, що зведену таблицю Excel вибрано. У списку Поля зведеної таблиці , де розгорнуто таблицю Disciplines , наведіть вказівник миші на поле Discipline, а праворуч від поля з'явиться стрілка розкривного списку. Клацніть розкривний список, натисніть кнопку **(Виділити все)**, щоб видалити всі виділені елементи, прокрутіть униз і виберіть archery, Diving, Fencing, Figure Skating і Speed Skating. Натисніть кнопку OK.
- Або в розділі Позначки рядків зведеної таблиці клацніть стрілку розкривного списку поруч із написом Позначки рядків у зведеній таблиці, зніміть прапорець (виділити все), щоб скасувати все виділення, прокрутіть униз і встановіть прапорці Archery, Diving, Fencing, Figure Skating і Speed Skating. Натисніть кнопку OK.
У списку Поля зведеної таблиці перетягніть поле Medal із таблиці Medals до області ЗНАЧЕННЯ. Оскільки значення мають бути числові, програма Excel автоматично змінює поле Medal на поле Підрахунок для Medal.
У таблиці Medals знову виберіть поле Medal і перетягніть його до області ФІЛЬТРИ.
Відфільтруймо зведену таблицю, щоб у ній відображалися лише ті країни та регіони, які отримали загалом більше 90 медалей. Ось як це зробити:
- У зведеній таблиці клацніть стрілку розкривного списку праворуч від напису Позначки стовпців.
- Виберіть команду Фільтри значень, а потім – пункт Більше….
- Введіть 90 в останньому полі (праворуч). Натисніть кнопку OK.
Зведена таблиця виглядатиме так:
Не докладаючи зайвих зусиль, ви створили просту зведену таблицю з полями з трьох різних таблиць. Це дуже просте завдання, тому що зв’язки між таблицями визначено заздалегідь. Оскільки вихідна база даних уже містила зв’язки, а всі таблиці імпортовано разом, програмі Excel вдалося відтворити ці зв’язки між таблицями в моделі даних.
Але що робити, якщо дані походять із різних джерел або імпортуються не одночасно? Зазвичай можна створити зв’язки з новими даними за відповідними стовпцями. Далі ми імпортуємо додаткові таблиці та навчимося створювати нові зв’язки.
Імпорт даних з електронної таблиці
Тепер давайте імпортуємо дані з іншого джерела (цього разу це буде наявна книга), а потім укажемо зв’язки між наявними та новими даними. Зв’язки дають змогу аналізувати колекції даних у програмі Excel і створювати цікаві та глибокі графічні відображення на основі імпортованих даних.
Давайте спочатку створимо пустий аркуш, а потім імпортуємо дані з книги Excel.
Вставте новий аркуш у програмі Excel і назвіть його Sports.
Перейдіть до папки, що містить завантажені файли зі зразками даних, і відкрийте файл OlympicSports.xlsx.
Виділіть і скопіюйте дані на аркуші Sheet1. Якщо виділено будь-яку клітинку з даними, наприклад A1, можна натиснути клавіші Ctrl+A, щоб виділити всі суміжні дані. Закрийте книгу OlympicSports.xlsx.
На аркуші Sports установіть курсор у клітинку A1 і вставте дані.
Виділивши дані, натисніть клавіші Ctrl+T, щоб відформатувати дані як таблицю. Ви також можете відформатувати дані як таблицю зі стрічки, вибравши елемент HOME Format as Table (Формат HOME > як таблиця). Оскільки дані містять заголовки, виберіть таблиця із заголовками у вікні Створення таблиці , що з'явиться, як показано тут.
Форматування даних як таблиці має багато переваг. Ви можете призначити таблицю ім'я, що спрощує визначення. Ви також можете встановити зв'язки між таблицями, увімкнувши пошук і аналіз у зведених таблицях, Power Pivot і Power View.
Назвіть таблицю. У властивості TABLE DESIGN >знайдіть поле Ім'я таблиці та введіть Спорт. Книга має такий вигляд:
Збережіть книгу.
Копіювання та вставлення даних для імпорту
Тепер, імпортувавши дані з книги Excel, давайте імпортуємо дані з таблиці, знайденої на веб-сторінці, або з будь-якого іншого джерела, дані з якого можна скопіювати та вставити в програму Excel. Нижче розповідається про те, як додати столиці Олімпійських ігор із таблиці.
- Вставте новий аркуш у програмі Excel і назвіть його Hosts.
- Виділіть і скопіюйте наведену нижче таблицю разом із заголовками.
- У програмі Excel установіть курсор у клітинку A1 аркуша Hosts і вставте дані.
- Відформатуйте дані як таблицю. Як описано вище в цьому посібнику, ви натискаєте клавіші Ctrl+T, щоб відформатувати дані як таблицю, або з home > Format as Table. Оскільки дані містять заголовки, установіть прапорець Таблиця із заголовками у вікні Створення таблиці, яке відобразиться.
- Назвіть таблицю. У властивості TABLE DESIGN > знайдіть поле Ім'я таблиці та введіть Hosts.
- Виділіть стовпець Edition, а потім на вкладці ОСНОВНЕ відформатуйте його як Числовий із нульовою (0) кількістю десяткових знаків.
- Збережіть книгу. Книга виглядатиме так:
Тепер, маючи книгу Excel із таблицями, можна створити між ними зв’язки. Створюючи зв’язки між таблицями, можна поєднувати дані з двох таблиць.
Створення зв’язку між імпортованими даними
Поля з імпортованих таблиць можна використовувати у зведеній таблиці відразу. Якщо програмі Excel не вдалося визначити, як слід включати поле у зведену таблицю, потрібно установити зв’язок із наявною моделлю даних. Далі розповідається про те, як створити зв’язок між даними, імпортованими з різних джерел.
На аркуші 1 у верхній частині полязведеної таблиці натисніть кнопкуУсі , щоб переглянути повний список доступних таблиць, як показано на знімку екрана нижче.
Прокрутіть список до щойно доданих таблиць.
Розгорніть розділ Спорт і виберіть елемент Вид спорту , щоб додати його до зведеної таблиці. Зверніть увагу, що програма Excel запропонує створити зв'язок, як видно на наступному екрані.
Це сповіщення відображається, тому що використано поля, які не входять до базової моделі даних. Один зі способів додати таблицю до моделі даних – створити зв’язок із таблицею, уже включеною до моделі даних. Щоб створити зв’язок, в одній із таблиць має бути стовпець унікальних значень, які не повторюються. У зразку даних таблиця Disciplines, імпортована з бази даних, містить поле SportID з кодами видів спорту. Ті самі коди видів спорту представлені як поле в даних, імпортованих із програми Excel. Створімо зв’язок.Натисніть кнопку СТВОРИТИ... у виділеній області вікна Поля зведеної таблиці, щоб відкрити діалогове вікно Створення зв’язку, як показано на екрані нижче.
У таблиці виберіть Таблиця моделі даних: Дисципліни з розкривного списку.
У полі Стовпець (зовнішній) виберіть значення SportID.
У пов'язаній таблиці виберіть таблиця моделі даних: Спорт.
У полі Пов’язаний стовпець (основний) виберіть значення SportID.
Натисніть кнопку OK.
Зведену таблицю буде змінено з урахуванням нового зв’язку. Але зведена таблиця ще не набула потрібного вигляду, оскільки поля в області РЯДКИ не впорядковано. Спортивна дисципліна – це підкатегорія певного виду спорту, але оскільки елемент Discipline опинився над елементом Sport в області РЯДКИ, зведену таблицю впорядковано неправильно (див. знімок екрана нижче).
В області РЯДКИ перемістіть поле Вид спорту над дисципліною. Це набагато краще, і у зведеній таблиці відображаються дані, як це потрібно бачити, як показано на наступному екрані.
Програма Excel створює модель даних, яку можна використовувати в будь-якій зведеній таблиці, зведеній діаграмі, надбудові Power Pivot або будь-якому звіті Power View. Зв’язки між таблицями – це основа моделі даних, і саме вони визначають переходи та шляхи обчислення.
У наступному посібнику розширте зв'язки моделі даних за допомогою Excel, Power Pivot**і DAX**, ви спираєтеся на те, що ви дізналися тут, і крок до розширення моделі даних за допомогою потужної та візуальної надбудови Excel під назвою Power Pivot. Ви також дізнаєтеся, як обчислити стовпці в таблиці, і використовувати цей обчислюваний стовпець, щоб до моделі даних можна було додати непов'язану таблицю.
Контрольна точка й вікторина
Стислий огляд вивченого матеріалу
Ви створили книгу Excel зі зведеною таблицею на основі даних із кількох таблиць, деякі з яких імпортовано окремо. Ви навчились імпортувати дані з бази даних та іншої книги Excel, а також копіювати дані та вставляти їх у програму Excel.
Щоб забезпечити узгоджену роботу даних, ви створили між таблицями зв’язки, завдяки яким зіставляються рядки в програмі Excel. Ви також дізналися, що, щоб створювати зв’язки й шукати пов’язані рядки, стовпці однієї таблиці мають відповідати даним в іншій таблиці.
Тепер можна переходити до наступного посібника з цієї серії. Ось посилання на нього:
Посібник. Розширення зв’язків моделі даних за допомогою Excel, Power Pivot і DAX
ВІКТОРИНА
Хочете перевірити, наскільки добре запам’ятали пройдений матеріал? Спробуйте! Наведена нижче вікторина стосується функцій, можливостей і вимог, описаних у цьому посібнику. Відповіді наведено внизу сторінки. Бажаємо успіхів!
Запитання 1. Чому важливо перетворювати імпортовані дані на таблиці?
А. Їх не обов’язково перетворювати на таблиці, тому що всі імпортовані дані автоматично перетворюються на таблиці.
Б. Якщо перетворити імпортовані дані на таблиці, їх буде виключено з моделі даних. Лише якщо їх виключено з моделі даних, вони доступні у зведених таблицях, Power Pivot і Power View.
В. Якщо перетворити імпортовані дані на таблиці, їх можна включити до моделі даних і зробити їх доступними для зведених таблиць, надбудов Power Pivot і Power View.
Г. Імпортовані дані не можна перетворити на таблиці.
Запитання 2. Дані з яких із наведених нижче джерел даних можна імпортувати до програми Excel і включити в модель даних?
А. Бази даних Access, а також багато інших баз даних.
Б. Наявні файли Excel.
В. Усе, що можна скопіювати та вставити в програму Excel і форматувати як таблицю, включно з таблицями даних на веб-сайтах, документами та будь-якими іншими даними, які можна вставити в програму Excel.
Г. Усі перелічені вище.
Запитання 3. Що станеться, якщо змінити порядок полів у чотирьох областях у вікні "Поля зведеної таблиці"?
А. Нічого. Додавши поля до областей у вікні "Поля зведеної таблиці", змінити їх порядок не можна.
Б. Формат зведеної таблиці зміниться відповідно до порядку полів, але це не вплине на базові дані.
В. Формат зведеної таблиці зміниться відповідно до порядку полів, а всі базові дані буде назавжди змінено.
Г. Базові дані буде змінено, у результаті чого буде створено нові набори даних.
Запитання 4. Що потрібно, щоб створити зв’язок між таблицями?
А. Жодна таблиця не може містити стовпці з унікальними значеннями які не повторюються.
Б. Одна з таблиць має не входити до книги Excel.
В. Стовпці не має бути перетворено на таблиці.
Г. Усі попередні твердження хибні.
Відповіді на вікторину
- Правильна відповідь: В
- Правильна відповідь: Г
- Правильна відповідь: Б
- Правильна відповідь: Г
Примітка.
Дані й зображення, використані в цій серії посібників:
- інформація про Олімпійські ігри, надана компанією Guardian News & Media Ltd;
- зображення прапорів зі сторінки Factbook веб-сайту ЦРУ (cia.gov);
- дані про чисельність населення з веб-сайту Світового банку (worldbank.org);
- піктограми олімпійських видів спорту, надані користувачами Thadius856 і Parutakupiu.