() translation by (you can also view the original English article)
Що ви будете робити, якщо потрібно створити зведену таблицю, а ваші дані на різних аркушах? З встановленим Excel 2013 у вас є для цього простий спосіб. Існує технологія, яка називається Data Model і вона використовує відносини даних так, як це робить база даних.
У цьому уроці я покажу вам все для створення зведеної таблиці в Excel 2013 з даних на декількох аркушах, використовуючи Data Model.
Відеоролик
Якщо ви хочете слідувати уроку, використовуючи власний файл Excel, можете так і зробити. Або завантажте zip-файл до цього уроку, в якому міститься зразок книги Pivot Consolidate.xlsx.
Вивчення даних
У цій книзі є три робочих листа: інформація про клієнта, інформація про замовлення і інформація про оплату.
Натисніть на лист Customer Info і переконайтеся, що в ньому містяться номери замовлень, а також назву і стан клієнтів.
Натисніть на лист Order Info і подивіться, що в ньому містяться номери замовлень, а також поля за місяць, замовлені товари і то, чи є ці продукти органічними.
Перейдіть на вкладку Payment Info і переконайтеся, що в ній містяться номери замовлень, сума в доларах кожного продажу, спосіб оплати і порядок розміщення замовлення новим або існуючим клієнтом.
Поєднавши всі ці листи в області завдань зведеної таблиці, ми можемо вибрати дані з кожного листа. Оскільки номера замовлень існують на всіх трьох аркушах, вони стануть точками підключення. Це те, що база даних викликає primary key. Зверніть увагу: не обов'язково мати primary key, але він зменшує ймовірність помилки.
Створення іменованих таблиць
Перед створенням зведеної таблиці давайте створимо таблицю з кожного листа.
Натисніть назад в Customer Table, потім клацніть в будь-якому місці всередині області даних. Перейдіть на вкладку Insert на панелі стрічки і клацніть значок Table.
Діалогове вікно Create Table правильно визначає область таблиці. Прапорець внизу повинен також ідентифікувати, що перший рядок таблиці призначен для заголовків. (Якщо немає, виберіть цей варіант.)
Натисніть OK, і тепер у вас є таблиця з чергуються штрихами і кнопками фільтра. Ви можете клацнути всередині, щоб зняти виділення, якщо хочете краще розглянути її (просто не натискайте поза таблиці). На панелі стрічки також відображається вкладка Design для таблиці. На лівій стороні стрічки в поле Table Name відображається тимчасове ім'я Table1. Видаліть це і назвіть його Customer_Info (використовуйте символ підкреслення замість пробілу). Натисніть Enter.
Повторіть ці дії з листами Order Info і Payment Info. Назвіть таблиці Order_Info і Payment_Info.
Тепер ми готові вставити PivotTable.
Вставка PivotTable
Переконайтеся, що на аркуші Payment Info курсор знаходиться десь в таблиці. Поверніться на вкладку Insert стрічки і клацніть значок PivotTable (це найперший значок).
У діалоговому вікні необхідно правильно визначити таблицю і вибрати, щоб PivotTable перейшло на новий робочий лист. Внизу встановіть прапорець Add this data to the Data Model. Натисніть OK.
Тепер у вас буде PivotTable на новому аркуші, в правій частині екрана буде панель задач, а на стрічці з'явиться вкладка Analyze.
На панелі завдань відображається таблиця і поля тільки активного листа, тому натисніть ALL, щоб побачити всі створені вами таблиці. Але перш ніж ми зможемо їх використати, ми повинні пов'язати їх один з одним, а це означає створення відносин. Натисніть кнопку Relationships на панелі стрічки.
Налаштування відносин таблиць
Натискання цієї кнопки відображає діалогове вікно Manage Relationships. Натисніть кнопку New і з'явиться діалогове вікно Create Relationship. Ми створимо дві відносини, використовуючи поле Order # в якості з'єднувача.
У списках, що розкриваються виберіть Payment_Info для таблиці, а поруч з ним виберіть Order # в списку Column. У другому рядку виберіть Customer_Info із списку Related Table, а поруч з нею виберіть Order # із списку Related Column.
Це означає, що таблиці Payment_Info і Customer_Info пов'язані між собою за випадковим збігом номера замовлень.
Натисніть кнопку OK і ми побачимо ці відносини, перераховані в вікні Manage Relationships.
Повторіть цей процес, щоб створити зв'язок між Payment_Info і Order_Info, також використовуючи поле Order #. Тепер Manage Relationships вікно виглядає так:
Зверніть увагу, що немає необхідності створювати відносини між таблицями Order_Info і Customer_Info, так як вони автоматично з'єднуються через таблицю Payment_Info.
Натисніть кнопку Close в нижній частині вікна. Тепер ми можемо, нарешті, перетягнути поля в PivotTable.
Вставка полів в PivotTable
У розділі ALL на панелі завдань клацніть маленькі стрілки, щоб, розгорнувши три таблиці, побачити їх поля. Перетягніть поля в області PivotTable наступним чином:
- State і Month в рядки
- Product в колонки
- $ Sale в значення
- Status в фільтри
Тепер ви можете використовувати і змінювати її, як і будь-яку іншу PivotTable.
Висновок
Використовуючи нову функцію Object Data Model в Excel 2013, ви можете вибрати поля з кількох аркушів для створення єдиної PivotTable. Майте на увазі, що рядки кожної таблиці повинні бути якимось чином пов'язані один з одним. У вас більше шансів на успіх, коли таблиці мають загальне поле з унікальними значеннями.
Якщо ви шукаєте хороші способи представлення своїх даних, Envato Market має хороший вибір Excel and PowerPoint templates, а також scripts and apps для перетворення даних Excel в веб-формати і навпаки.
