Модель даних Excel (Power Pivot) дозволяє підключити великий детальний набір фактичних даних до бюджету верхнього рівня за допомогою столярних таблиць.
Бюджети складаються на найвищому рівні - дохід за товарними лініями за регіонами за місяцями. Факти з часом накопичуються повільно - рахунок-фактура, рядок-позиція. Порівняння невеликого бюджетного файлу з об’ємними фактичними даними було болем назавжди. Мені подобається цей фокус від Роб Коллі, він же PowerPivotPro.com.
Щоб налаштувати приклад, у вас є 54-рядкова таблиця бюджету: один рядок на місяць для регіону на товар.
Файл рахунку-фактури знаходиться на рівні деталізації: наразі цього року 422 рядки.
У світі немає жодного VLOOKUP, який коли-небудь дозволив би вам зіставити ці два набори даних. Але завдяки Power Pivot (вона ж модель даних в Excel 2013+) це стає легко.
Вам потрібно створити крихітні маленькі таблиці, які я називаю «столярами», щоб зв’язати два більші набори даних. У моєму випадку продукт, регіон і дата є спільними між цими двома таблицями. Таблиця Product - це крихітна таблиця з чотирьох комірок. Так само для регіону. Створіть кожен із них, скопіювавши дані з однієї таблиці та використовуючи Видалити дублікати.
Календарну таблицю праворуч насправді було складніше створювати. Дані бюджету мають один рядок на місяць, завжди припадаючи на кінець місяця. Дані рахунків-фактур відображають щоденні дати, як правило, будні. Отже, мені довелося скопіювати поле Дата з обох наборів даних в один стовпець, а потім видалити дублікати, щоб переконатися, що представлені всі дати. Потім я =TEXT(J4,"YYYY-MM")створював стовпець "Місяць" із щоденних дат.
Якщо у вас немає повної надбудови Power Pivot, вам потрібно створити зведену таблицю з таблиці бюджету та встановити прапорець Додати ці дані до моделі даних.
Як обговорювалося в попередній підказці, під час додавання полів у зведену таблицю вам доведеться визначити шість взаємозв’язків. Хоча ви могли зробити це за шість відвідувань діалогового вікна «Створення зв’язків», я запустив надбудову Power Pivot і використав режим діаграми для визначення шести зв’язків.
Ось ключ до виконання всієї цієї роботи: Ви можете вільно використовувати числові поля з Бюджету та Фактичного. Але якщо ви хочете показати регіон, товар або місяць у зведеній таблиці, вони повинні надходити із столярних таблиць!
Ось зведена таблиця з даними, що надходять із п’яти таблиць. Стовпець A надходить від столяра регіону. Рядок 2 надходить від столяра Календаря. Зріз продукту - від столяра продукту. Номери бюджету походять із таблиці Бюджет, а фактичні - з таблиці Рахунки.
Це працює, оскільки столярні таблиці застосовують фільтри до таблиці Бюджет та Фактична. Це прекрасна техніка, яка показує, що Power Pivot призначений не лише для великих даних.
Переглянути відео
- У вас невеликий набір бюджетних даних зверху вниз
- Ви хочете порівняти з фактичним набором даних, що знижується
- Фактичні факти можуть надходити з реєстру рахунків-фактур
- Модель даних дозволить вам порівняти ці набори даних різного розміру
- Зробіть обидва набори даних у таблиці Ctrl + T
- Для кожного текстового поля, за яким ви хочете звітувати, створіть таблицю столяра
- Скопіюйте значення та видаліть дублікати
- Для дат можна включити дати з обох таблиць і перетворити їх на кінець місяця
- Зробіть столярами таблиці Ctrl + T
- Необов’язково, але корисно назвати всі п’ять таблиць
- Створіть зведену таблицю з бюджету та оберіть модель даних
- Створіть зведену таблицю, використовуючи Бюджет та Фактичну з вихідних таблиць
- Всі інші поля повинні надходити з таблиць столярів
- Додайте нарізки за продуктом
- Створіть три відносини від бюджету до столярів
- Створіть три стосунки від фактичного до столярного
- Завтра: як побудувати відносини простіше за допомогою Power Pivot та DAX Formulas
Стенограма відео
Дізнайтеся Excel з підкасту, серія 2016 - Бюджет зверху вниз проти фактичного знизу вгору!
Гей, я підкастую всю цю книгу, клацніть на “i” у верхньому правому куті та дотримуйтесь списку відтворення.
Гей, я збираюся перервати це, це Білл Джелен через 15 хвилин. Зараз я усвідомлюю, що це неймовірно довгий подкаст, і вас спокушає просто натиснути на ньому, але дозвольте мені просто дати вам це коротше. Якщо ви працюєте в Excel 2013, і у вас коли-небудь була невелика таблиця бюджету та масивна таблиця фактичних даних, і вам потрібно зіставити їх разом, це дивовижна нова здатність, яку ми маємо в Excel 2013, і не багато людей пояснювали це , і ви, мабуть, не знаєте про це. Якщо це ви, ви знаходитесь у 2013 році, і вам потрібно зіставити ці два набори даних, не поспішайте, можливо сьогодні, можливо завтра, можливо, додайте його до списку перегляду, воно того варте, це дивовижна техніка.
Добре, ось що у нас є, зліва у нас є бюджет, цей бюджет, це робиться на верхньому рівні, зверху вниз, праворуч для кожної товарної лінійки, для кожного регіону, на кожен місяць, є бюджет . Тут не так багато записів, нараховуємо 55, праворуч ми намагаємося порівняти це з реальними. Фактичні дані надходять із реєстру рахунків-фактур, тому у нас є регіон, товар і дохід, але це окремі рахунки-фактури, тут набагато більше даних, ми вже на півдорозі року, і я вже маю 423 записи. Гаразд, так як ви зіставляєте ці 55 з цими 423? Можливо, це важко зробити з VLOOKUP, спочатку вам доведеться підвести підсумки, але, на щастя, в Excel 2013 модель даних робить це дійсно, дуже просто. Те, що нам потрібно, щоб цей великий масивний стіл спілкувався з цим крихітним столиком, є посередниками, я називаю їх столярами.Крихітні маленькі таблиці, Товар, Регіон та Календар, ми приєднаємо бюджет до цих трьох таблиць, ми приєднаємо фактичну до цих трьох таблиць, і дивом зведена таблиця буде працювати. Гаразд, ось як ми це робимо.
Спочатку мені потрібно створити столярів, тому я беру це поле Product зі стовпця A і копіюю його у стовпець F, а потім Дані, Видалення дублікатів, клацання ОК, і у нас залишається крихітна таблиця, 1 заголовок 3 ряди. Те саме, що стосується регіону, візьміть регіони, Ctrl + C, перейдіть до стовпця G, Вставте, Видаліть дублікати, натисніть OK, 3 рядки 1 заголовок, добре. Тепер для дат дати не однакові, це дати закінчення місяця, насправді вони зберігаються як дати закінчення місяця, і це будні. Я збираюся взяти обидва списки, Ctrl + C другий список і вставити його сюди, Ctrl + V, потім я збираюся взяти коротший список, скопіювати його і вставити його нижче, добре. І насправді дратує те, що, хоча вони зберігаються як дати, вони відображаються як місяці, а Видалити копії не бачитимуть їх однаковими.Тому перед тим, як я застосую Видалити дублікати, я повинен змінити його на короткий термін. Виберіть ці дані, Дані, Видалити дублікати, натисніть кнопку ОК, а потім трохи сортуйте тут, щоб змусити їх працювати.
Добре, тепер я не хочу звітувати за щоденною датою, тому я збираюся додати сюди стовпець, підстановний стовпець із написом Місяць, і це буде дорівнює EOMONTH тій даті,, 0, що призведе до кінець місяця. Він відформатує це як коротку дату і скопіює це, добре. Тепер нам потрібно зробити кожну з них таблицею Ctrl + T, тому звідси Ctrl + T, у моєї таблиці є заголовки, красиво. Маленькі, він не усвідомлює, що це заголовки там, тому нам обов’язково потрібно поставити галочку біля цього і Ctrl + T, добре, і вони називають ці таблиці Table1, Table2, Table3, справді нудні імена, так? Тож я збираюся перейменувати їх і назвати це BudTable, ProdTable, RegTable, my CalTable, а потім ActTable, добре.
Ми починаємо з першої таблиці, і, до речі, ми не збираємося використовувати PowerPivot сьогодні, ми будемо робити все це з моделлю даних. Отже, Excel 2013 або новішої версії, у вас є ця вставка, зведена таблиця, ми встановимо прапорець біля пункту «Додати ці дані до моделі даних», натисніть кнопку «ОК», і ми отримаємо список полів з чарівною кнопкою «Все», що дозволяє я вибираю з усіх п’яти таблиць у книзі, Фактичне, Бюджет, Календар, Продукт, Регіон. Гаразд, отже, цифри надходитимуть із таблиці Бюджет, я вкладу туди бюджет, а з таблиці Фактичних я вкладу туди фактичну, але ось ось що стосується решти зведеної таблиці. Будь-які інші текстові поля, які ми збираємося розмістити в області рядків або області стовпців або як нарізки, вони повинні надходити від столярів, вони повинні надходити з тих таблиць між таблицями.
Гаразд, отже, з таблиці Календаря ми візьмемо це поле Місяць і розмістимо його зверху, зараз ми будемо ігнорувати інші відносини. Я буду створювати стосунки, але хочу створити їх усі відразу. А таблиця регіонів, відкладіть регіони збоку. Я міг би відкласти продукти збоку, але насправді я буду використовувати таблицю Product як нарізку, тому Проаналізуйте, Вставте Slicer, знову вам доведеться перейти до All, якщо ви ще не використовували таблицю Product. Тож перейдіть до пункту Усі, і ви побачите, що Продукт можна створити у вигляді нарізки з продуктів, наприклад. Добре зараз, на даний момент ми не створили відносин, тому всі ці цифри помилкові. І відносини, які ми повинні створити, ми повинні створити 3 таблиці з цієї маленької таблиці бюджету, одну до продуктів, одну до регіонів, одну до календаря,це 3 відносини. А потім ми повинні створити зв’язки з таблиці Фактична до області Продукту в Календарі, тож загалом 6 таблиць. Так, це, безумовно, було б простіше, якби у нас був PowerPivot, але ми цього не маємо або припустимо, що цього не робимо.
І ось я збираюся використати старомодний спосіб, діалог Створення тут, де ми маємо таблицю Бюджет зліва, і ми будемо використовувати поле Регіон і зв’яжемо це з таблицею Регіон, поле Регіон . Гаразд, 1/6 створено. Я оберу Створити, знову з таблиці Бюджет ми переходимо до Продукту, а потім прив'язую до таблиці Продукт, до Продукту, натискаю OK. З таблиці Бюджет поля Дата ми переходимо до таблиці Календар, а в полі Доля натисніть кнопку ОК, ми на півдорозі, добре. З таблиці Актуальності ми переходимо до Регіон, до таблиці Регіон, натискаємо ОК, з таблиці Актуальності до Продукту, а з таблиці Актуальності до Календаря. Я насправді збираюся взяти Цінності і змусити це спуститися збоку, добре. Дизайн, макет звіту, показ у табличній формі, щоб отримати вигляд, який я віддаю перевагу, повторити всі мітки елементів, добре,це абсолютно дивно! Тепер у нас є ця крихітна маленька таблиця, 50-ти записів у цій таблиці з сотнями записів, і ми створили єдину зведену таблицю завдяки моделі даних. Для кожного, де ми можемо бачити бюджет, ми можемо бачити дохід, він розподіляється за регіонами, розбивається за місяцями та розбивається за продуктами.
Зараз ця концепція прийшла до мене від Роба Коллі, який керує Power Pivot Pro, і Роб створив там багато книг, остання з них - “Power Pivot і Power BI”. Я думаю, що цей насправді був у книзі “Алхімія Power Pivot”, це той, який я бачив і сказав: “Ну це, хоча у мене немає мільйонів рядків для звітування через Power Pivot, це той, який би зробив ВАГОМИННУ різницю в моєму житті, маючи два набори даних, що не відповідають розмірам, і я маю потребу звітувати з обох ». Ну цей приклад та багато інших є у цій книзі, я врешті отримаю весь книжковий подкаст, здається, це займе два з половиною місяці. Але ви можете отримати цілу книгу сьогодні, тоді ж, поїдьте туди, купите книгу, 10 доларів за електронну книгу, 25 доларів за друковану книгу, і ви можете отримати всі ці поради відразу.
Гаразд, дуже довгий епізод тут: у нас є невеликий бюджет зверху вниз і знижується Фактично, вони різного розміру, але, використовуючи модель даних у Excel 2013 … І до речі, якщо ви перебуваєте в 2010 році, , теоретично, зробіть це, отримавши надбудову Power Pivot, і пройдіть усі ці кроки ще в 2010 році. Зробіть обидва набори даних у таблиці Ctrl + T, а потім приєднайте свої таблиці до всього, про що ви хочете звітувати, у мітка рядка, мітка стовпця або нарізки, тому скопіюйте ці значення та Видаліть дублікати для дат. Я насправді взяв значення з обох таблиць, оскільки в кожній з них було кілька унікальних значень, а потім я використав EOMONTH, щоб вийти туди, зробити ці столярні таблиці керованими таблицями. Це необов’язково, але я назвав усі 5 таблиць, оскільки легше, коли ви встановлюєте ці зв’язки, а не називаєтесь Table1,Таблиця2, Таблиця3.
Отже, почніть із таблиці Бюджет, Вставка, Зведена таблиця, поставте прапорець біля Модель даних, а потім створіть Зведену таблицю, використовуючи Бюджет та Фактичний. Все інше походить із столярних таблиць, тому регіон та місяць у області рядків та стовпців, зрізи надходять із таблиці Product. І тоді нам довелося створити 3 взаємозв'язки з бюджету до столярів, 3 взаємозв'язки з фактичного до столярів, і ми маємо дивовижну зведену таблицю. Тепер завтра ми подивимося на використання вкладки Power Pivot і створимо деякі додаткові розрахунки. Отже, все це можливо, саме тоді, коли ми хочемо вставити обчислюване поле, саме тоді вам доведеться платити додаткові 2 долари на місяць, щоб отримати версію Pro Plus Office 365.
Ну, привіт, дякую Роб Коллі з Power Pivot Pro за цю підказку та спасибі Вам, що завітали, ми побачимо Вас наступного разу для чергової трансляції від!
Завантажити файл
Завантажте зразок файлу тут: Podcast2016.xlsx
