Не маєте Power Pivot? Не має значення. Більшість Power Pivot вбудовано в Excel 2013 і навіть більше в Excel 2016. Сьогодні наша порада від Ash об’єднує таблиці в зведену таблицю.
Щосереди протягом семи тижнів я пропоную одну з улюблених порад Еша Шарми. Еш - менеджер з продуктів у команді Excel. Його команда пропонує вам зведені таблиці та багато інших корисних речей. Сьогодні улюбленою функцією Еша є приєднання до декількох наборів даних за допомогою відносин та моделі даних.
Скажімо, ваш ІТ-відділ надає вам набір даних, показаний у стовпцях A: D. Є поля для споживача та ринку. Потрібно об’єднати певні ринки в регіони. Кожен клієнт належить до певного сектору. Регіон та сектор відсутні у вихідних даних, але у вас є таблиці пошуку, щоб надати цю інформацію.
Зазвичай ви згладжуєте дані, використовуючи VLOOKUP, щоб витягнути дані з оранжевої та жовтої таблиць до синьої таблиці. Але оскільки ключове поле знаходиться не ліворуч від кожної таблиці, вам доведеться або переключитися на INDEX і MATCH, або перевпорядкувати таблиці пошуку.
Починаючи з Excel 2013, ви можете залишати таблиці пошуку там, де вони є, та комбінувати їх у самому звіті зведеної таблиці.
Щоб цей прийом працював, усі три таблиці повинні бути відформатовані як таблиця. Виділіть по одній комірці в кожному наборі даних і виберіть Домашня сторінка, Формат як таблиця або натисніть Ctrl + T. Три таблиці спочатку називатимуться Таблиця1, Таблиця2 та Таблиця3. Я використовую вкладку Дизайн інструментів таблиці на стрічці та перейменовую кожну таблицю. Я також міняю колір кожного столу. У цьому прикладі синя таблиця називається Даними. Помаранчева таблиця - RegionTable. Жовта таблиця - SectorTable.
Примітка
Деякі скажуть вам, що ви повинні використовувати такі вигадливі імена, як Fact, TblSector та TblRegion. Якщо хтось клопоче вас так, просто вкрадіть його кишеньковий захисник і повідомте їм, що ви віддаєте перевагу іменам, що звучать по-англійськи.
Щоб перейменувати таблицю, введіть нове ім’я у полі зліва на вкладці Дизайн інструментів таблиці. Назви таблиць не повинні мати пробілів.
Після визначення трьох таблиць перейдіть на вкладку Дані та натисніть на Зв'язки.
У діалоговому вікні Керування відносинами натисніть Створити. У діалоговому вікні Створення взаємозв'язку вкажіть, що поле Клієнт таблиці даних пов'язане з полем Клієнта секторної таблиці. Клацніть OK.
Визначте ще один новий зв’язок між полем Market у полях Data та RegionTable. Після визначення обох відносин ви побачите їх у діалоговому вікні Керування відносинами.
Вітаємо! Ви щойно створили модель даних у своїй книзі. Настав час побудувати зведену таблицю.
Виберіть порожню комірку, де ви хочете, щоб відображалася ваша зведена таблиця. За замовчуванням у діалоговому вікні Створення зведеної таблиці буде вибрано Використовувати модель даних цієї книги. Розташування зведеної таблиці буде типовим для вибраної комірки. Клацніть OK.
У списку Полів зведеної таблиці будуть перераховані всі три таблиці. Використовуйте трикутник ліворуч від таблиці, щоб розгорнути назву таблиці, щоб показати вам поля.
Розгорніть таблицю даних. Виберіть поле Дохід. Він автоматично переміститься в область Значення. Розгорніть таблицю секторів. Виберіть поле Сектор. Він переміститься до області Рядки. Розгорніть таблицю регіонів. Перетягніть поле Регіон до області Стовпці. Тепер у вас буде зведена таблиця, що узагальнює дані з трьох таблиць.
Примітка
У кожній книзі, яку я писав до сьогодні, я використовую інший прийом для побудови цього звіту. Визначивши три таблиці, я вибираю комірку А1 та Вставка, зведену таблицю. Поставте прапорець Додати ці дані до моделі даних. У списку Поля зведеної таблиці виберіть Усі у верхній частині списку. Виберіть поля для звіту, а потім визначте взаємозв'язки по факту. Описана вище техніка здається більш плавною і насправді передбачає крихітне планування вперед. Людям, які використовують Option Explicit у своєму коді VBA, безумовно сподобався б цей метод.
Взаємозв'язки в моделі даних роблять Excel більш схожим на Access або SQL Server, але з усіма перевагами Excel.
Я люблю запитувати команду Excel про їхні улюблені функції. Кожної середи я буду ділитися однією з їх відповідей. Дякую Еш Шарма за те, що вона запропонувала цю ідею.
Думка дня в Excel
Я попросив своїх друзів Excel Master порадити їх щодо Excel. Сьогоднішня думка поміркувати:
"Не шукайте, якщо у вас стосунки"
Джон Міхалудіс
