Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Microsoft Excel
Computers

Розширені зведені таблиці: об'єднання даних з декількох листів

by
Difficulty:AdvancedLength:LongLanguages:

Ukrainian (українська мова) translation by Nikita Dryabzhinskiy (you can also view the original English article)

Що ви будете робити, якщо потрібно створити зведену таблицю, а ваші дані на різних аркушах?  З встановленим Excel 2013 у вас є для цього простий спосіб.  Існує технологія, яка називається Data Model і вона використовує відносини даних так, як це робить база даних.

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

Відеоролик

Якщо ви хочете слідувати уроку, використовуючи власний файл Excel, можете так і зробити.   Або завантажте zip-файл до цього уроку, в якому міститься зразок книги Pivot Consolidate.xlsx.

Вивчення даних

У цій книзі є три робочих листа: інформація про клієнта, інформація про замовлення і інформація про оплату.

Натисніть на лист Customer Info і переконайтеся, що в ньому містяться номери замовлень, а також назву і стан клієнтів.

Customer Info sheet
Customer Info лист

Натисніть на лист Order Info і подивіться, що в ньому містяться номери замовлень, а також поля за місяць, замовлені товари і то, чи є ці продукти органічними.

Order Info sheet
Order Info лист

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

Payment Info sheet
Payment Info лист

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

Створення іменованих таблиць

Перед створенням зведеної таблиці давайте створимо таблицю з кожного листа.

Натисніть назад в Customer Table, потім клацніть в будь-якому місці всередині області даних.  Перейдіть на вкладку Insert на панелі стрічки і клацніть значок Table.

creating a table from existing data
Перетворіть дані на аркуші, вибравши Insert> Table

Діалогове вікно Create Table правильно визначає область таблиці.  Прапорець внизу повинен також ідентифікувати, що перший рядок таблиці призначен для заголовків.  (Якщо немає, виберіть цей варіант.)

Create Table dialog box
Діалог Create Table повинен правильно вгадувати область даних

Натисніть OK, і тепер у вас є таблиця з чергуються штрихами і кнопками фільтра.  Ви можете клацнути всередині, щоб зняти виділення, якщо хочете краще розглянути її (просто не натискайте поза таблиці).  На панелі стрічки також відображається вкладка Design для таблиці.  На лівій стороні стрічки в поле Table Name відображається тимчасове ім'я Table1.  Видаліть це і назвіть його Customer_Info (використовуйте символ підкреслення замість пробілу).  Натисніть Enter.

applying a name to a table
Застосувати ім'я до кожної таблиці

Повторіть ці дії з листами Order Info і Payment Info.  Назвіть таблиці Order_Info і Payment_Info.

Тепер ми готові вставити PivotTable.

Вставка PivotTable

Переконайтеся, що на аркуші Payment Info курсор знаходиться десь в таблиці.  Поверніться на вкладку Insert стрічки і клацніть значок PivotTable (це найперший значок).

inserting the PivotTable
За допомогою курсору всередині однієї з таблиць виберіть Insert> PivotTable

У діалоговому вікні необхідно правильно визначити таблицю і вибрати, щоб PivotTable перейшло на новий робочий лист.  Внизу встановіть прапорець Add this data to the Data Model.  Натисніть OK.

add data to the Data Model
Додавання даних в Data Model - це те, що дозволяє з'єднанням працювати

Тепер у вас буде PivotTable на новому аркуші, в правій частині екрана буде панель задач, а на стрічці з'явиться вкладка Analyze.

На панелі завдань відображається таблиця і поля тільки активного листа, тому натисніть ALL, щоб побачити всі створені вами таблиці.  Але перш ніж ми зможемо їх використати, ми повинні пов'язати їх один з одним, а це означає створення відносин.  Натисніть кнопку Relationships на панелі стрічки.

Налаштування відносин таблиць

Натискання цієї кнопки відображає діалогове вікно Manage Relationships.  Натисніть кнопку New і з'явиться діалогове вікно Create Relationship.  Ми створимо дві відносини, використовуючи поле Order # в якості з'єднувача.

У списках, що розкриваються виберіть Payment_Info для таблиці, а поруч з ним виберіть Order # в списку Column.  У другому рядку виберіть Customer_Info із списку Related Table, а поруч з нею виберіть Order # із списку Related Column.

creating a table relationship
Є три таблиці, тому створіть дві відносини

Це означає, що таблиці Payment_Info і Customer_Info пов'язані між собою за випадковим збігом номера замовлень.

Натисніть кнопку OK і ми побачимо ці відносини, перераховані в вікні Manage Relationships.

Повторіть цей процес, щоб створити зв'язок між Payment_Info і Order_Info, також використовуючи поле Order #.  Тепер Manage Relationships вікно виглядає так:

list of all relationships
У діалоговому вікні Manage Relationships відображаються відносини, які ви створюєте

Зверніть увагу, що немає необхідності створювати відносини між таблицями Order_Info і Customer_Info, так як вони автоматично з'єднуються через таблицю Payment_Info.

Натисніть кнопку Close в нижній частині вікна.  Тепер ми можемо, нарешті, перетягнути поля в PivotTable.

Вставка полів в PivotTable

У розділі ALL на панелі завдань клацніть маленькі стрілки, щоб, розгорнувши три таблиці, побачити їх поля.  Перетягніть поля в області PivotTable наступним чином:

  • State і Month в рядки
  • Product в колонки
  • $ Sale в значення
  • Status в фільтри
final PivotTable
Перетягніть поля кожної з трьох таблиць в PivotTable

Тепер ви можете використовувати і змінювати її, як і будь-яку іншу PivotTable.

Висновок

Використовуючи нову функцію Object Data Model в Excel 2013, ви можете вибрати поля з кількох аркушів для створення єдиної PivotTable.  Майте на увазі, що рядки кожної таблиці повинні бути якимось чином пов'язані один з одним.  У вас більше шансів на успіх, коли таблиці мають загальне поле з унікальними значеннями.

Якщо ви шукаєте хороші способи представлення своїх даних, Envato Market має хороший вибір Excel and PowerPoint templates, а також scripts and apps для перетворення даних Excel в веб-формати і навпаки.

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.