Моделът на данни на Excel (Power Pivot) ви позволява да свържете голям детайлен набор от фактически данни към бюджет от най-високо ниво, като използвате дърводелски таблици.
Бюджетите се правят на най-високо ниво - приходите по продуктова линия по региони по месеци. Действителностите се натрупват бавно с течение на времето - фактура по фактура, ред по ред. Сравняването на малкия бюджетен файл с обемните фактически данни е било болка завинаги. Обичам този трик от Роб Коли, известен още като PowerPivotPro.com.
За да настроите примера, имате 54-редова бюджетна таблица: един ред на месец за регион за продукт.
Файлът на фактурата е на ниво детайли: 422 реда до момента тази година.
Няма VLOOKUP в света, който някога ще ви позволи да съвпадате с тези два набора от данни. Но благодарение на Power Pivot (известен още като Модел на данни в Excel 2013+) това става лесно.
Трябва да създадете малки малки таблици, които аз наричам „дърводелци“, за да свържат двата по-големи набора от данни. В моя случай Product, Region и Date са общи между двете таблици. Таблицата Product е малка таблица с четири клетки. Също така за Регион. Създайте всеки от тях, като копирате данни от една таблица и използвате Премахване на дубликати.
Календарната таблица вдясно беше по-трудна за създаване. Данните за бюджета имат един ред на месец, винаги попадащ в края на месеца. Данните на фактурите показват ежедневни дати, обикновено делнични дни. Така че, трябваше да копирам полето Date от двата набора от данни в една колона и след това да премахна дубликати, за да се уверя, че всички дати са представени. След това използвах =TEXT(J4,"YYYY-MM")да създавам колона Month от дневните дати.
Ако нямате пълната добавка Power Pivot, трябва да създадете обобщена таблица от таблицата Бюджет и да поставите отметка в квадратчето за Добавяне на тези данни към модела на данни.
Както беше обсъдено в предишния съвет, докато добавяте полета към обобщената таблица, ще трябва да дефинирате шест връзки. Въпреки че можете да направите това с шест посещения в диалоговия прозорец Създаване на връзка, аз активирах добавката си Power Pivot и използвах изгледа на диаграмата, за да дефинирам шестте връзки.
Тук е ключът към всичко това: Можете свободно да използвате цифровите полета от Бюджет и от Действително. Но ако искате да покажете Регион, Продукт или Месец в обобщената таблица, те трябва да идват от дърводелските таблици!
Ето една обобщена таблица с данни, идващи от пет таблици. Колона А идва от Регионалния дърводелец. Ред 2 идва от дърводелеца на Календар. Продуктовият слайсър е от Product joiner. Бюджетните номера идват от таблицата „Бюджет“, а действителните - от таблицата „Фактура“.
Това работи, защото дърводелските таблици прилагат филтри към таблицата Бюджет и Действително. Това е красива техника и показва, че Power Pivot не е само за големи данни.
Гледам видео
- Имате малък набор от бюджетни данни отгоре надолу
- Искате да сравните с актуален набор от данни за актуални данни
- Действителните факти могат да идват от регистър на фактурите
- Моделът на данните ще ви позволи да сравните тези набори от данни с различен размер
- Направете и двата набора от данни в таблица Ctrl + T
- За всяко текстово поле, за което искате да отчитате, създайте таблица на дърводелци
- Копирайте стойностите и премахнете дубликати
- За дати можете да включите дати от двете таблици и да конвертирате в края на месеца
- Направете дърводелците да бъдат Ctrl + T таблици
- Незадължително, но полезно за назоваване на всички пет таблици
- Създайте обобщена таблица от Бюджет и изберете Модел на данни
- Изградете обобщена таблица, използвайки Бюджет и Действително от оригиналните таблици
- Всички останали полета трябва да идват от дърводелските таблици
- Добавете резачки по продукт
- Създайте три взаимоотношения от Бюджет към Дърводелци
- Създайте три връзки от Actual към Joiners
- Утре: как изграждането на взаимоотношения е по-лесно с Power Pivot и DAX Formulas
Видео транскрипция
Научете Excel от подкаст, епизод 2016 - Бюджет отгоре надолу срещу факти отдолу нагоре!
Хей, подкастирам цялата тази книга, щракнете върху „i“ в горния десен ъгъл и следвайте плейлиста.
Хей, ще го прекъсна, това е Бил Джелен след 15 минути. Сега осъзнавам, че това е невероятно дълъг подкаст и вие се изкушавате просто да кликнете върху него, но нека просто ви дам този кратък резултат. Ако сте в Excel 2013 и някога сте имали малка бюджетна таблица и масивна актуална таблица и трябва да ги съпоставите заедно, това е невероятна нова способност, която имаме в Excel 2013, която не много хора са обяснили и вероятно не знаете за това. Ако това сте вие, вие сте през 2013 г. и трябва да картографирате тези два набора от данни, отделете време, може би днес, може би утре, може би да го добавите към списъка за гледане, струва си, това е невероятна техника.
Добре, ето какво имаме, отляво имаме бюджет, този бюджет, прави се на най-високо ниво, отгоре надолу, вдясно за всяка продуктова линия, за всеки регион, за всеки месец, има бюджет . Няма много записи тук, броят на 55, в дясната страна се опитваме да сравним това с действителните. Действителните данни идват от регистър на фактурите, така че имаме регион, продукт и приходи, но те са индивидуални фактури, много повече данни тук, вече сме на половината година и вече имам 423 записа. Добре, така как да картографирате тези 55 към тези 423? Може да е трудно да се направи с VLOOKUP, първо трябва да обобщите, но за щастие в Excel 2013 моделът на данните прави това наистина много лесно. Това, което трябва да позволим на тази голяма масивна маса да комуникира с тази малка маса, са посредници, аз ги наричам дърводелци.Малки малки таблици, Продукт, Регион и Календар, ще присъединим бюджета към тези три таблици, ще присъединим действителните към тези три таблици и по чудо Pivot таблицата ще работи. Добре, ето как правим това.
Първо трябва да създам дърводелците, затова взема това поле Product от колона A и го копирам в колона F, а след това Data, Remove Duplicates, щракнете върху OK и ни остава малка малка таблица, 1 заглавие 3 реда. Същото нещо за Регион, вземете регионите, Ctrl + C, преминете към колона G, Поставяне, Премахване на дубликати, щракнете върху OK, 3 реда 1 заглавка, добре. Сега за датите датите не са еднакви, това са дати, завършващи на месеца, те всъщност се съхраняват като дати, приключващи на месеца, и това са делничните дни. Ще взема двата списъка, Ctrl + C втория списък и ще го поставя тук, Ctrl + V, след това ще взема по-краткия списък, копирам го и го поставям долу, добре. И наистина е досадно, че въпреки че те се съхраняват като дати, те се появяват като месеци и Remove Duplicates няма да ги вижда като еднакви.Така че, преди да използвам Remove Duplicates, трябва да го променя на кратка дата. Изберете тези данни, Данни, Премахване на дубликати, щракнете върху OK и след това малко сортирайте тук, за да накарате да работи.
Добре, сега не искам да докладвам по дневна дата, затова ще добавя колона тук, справочна колона, която казва месец, и това ще бъде равно на EOMONTH тази дата,, 0, което ще ни отведе до края на месеца. Той ще форматира това като кратка дата и ще го копира, добре. Сега трябва да направим всеки от тях в таблица Ctrl + T, така че оттук Ctrl + T, Моята таблица има заглавки, красиви. Малките, той не осъзнава, че това са хедъри там, затова трябва да сме сигурни, че ще маркираме това и Ctrl + T, добре и те наричат тези таблици Таблица1, Таблица2, Таблица3, наистина скучни имена, нали? Така че ще ги преименувам и ще ги нарека BudTable, ProdTable, RegTable, my CalTable и след това ActTable, добре.
Започваме от първата таблица и между другото няма да използваме PowerPivot днес, ще направим всичко това с модела на данни. И така, Excel 2013 или по-нова версия, имате тази Insert, PivotTable, ще поставим отметка в квадратчето за „Добавяне на тези данни към модела на данни“, щракнете върху OK и ще получим списъка с полетата с магическия бутон All, който позволява аз да избера от всичките пет таблици в работната книга, действително, бюджет, календар, продукт, регион. Добре, така че цифрите ще идват от таблицата Бюджет, ще вложа бюджета там, а от таблицата Действително ще вложа действителната там, но ето какво е останалата част от обобщената таблица. Всички други текстови полета, които ще поставим в областта на редовете или в областта на колоните или като срезове, те трябва да идват от дърводелците, те трябва да идват от тези таблици между таблиците.
Добре, така че от таблицата на календара ще вземем това поле за месец и ще го поставим в горната част, ще игнорираме други връзки в момента. Ще създавам връзките, но искам да ги създам наведнъж. И таблицата Регион, оставете регионите отстрани. Бих могъл да сложа продукти отстрани, но всъщност ще използвам таблицата Product като резачка, така че Analyze, Insert Slicer, отново трябва да отидете на All, ако все още не сте използвали таблицата Product. Така че отидете на Всички и ще видите, че Продуктът е достъпен за създаване като нарязване от продукти, като това. Добре сега, към този момент не сме създали взаимоотношения, така че всички тези числа са грешни. И връзките, които трябва да създадем, трябва да създадем 3 таблици от тази малка бюджетна таблица, една към продуктите, една към регионите, една към календара,това са 3 взаимоотношения. И тогава трябва да създадем взаимоотношения от Действителната таблица към Продуктовата област в Календар, така че общо 6 таблици. И да, това определено би било по-лесно, ако имахме PowerPivot, но нямаме или нека приемем, че нямаме.
И така, ще използвам старомодния начин, диалога Създаване тук, където имаме таблица Бюджет вляво и ще използваме полето Регион и ще го свържем с таблицата Регион, полето Регион . Добре, 1/6 са създадени. Ще избера Създаване, отново от таблицата Бюджет отиваме към Продукта и след това го свързвам към таблицата Продукт, към Продукта, щракнете върху OK. От таблицата „Бюджет“ полето „Дата“ отиваме към таблицата „Календар“ и полето „Съдбата“ щракнете върху „ОК“, ние сме на половината път, добре. От таблицата Actuals отиваме Region, към таблицата Region, щракнете върху OK, от таблицата Actuals към продукта и от таблицата Actuals към календара. Всъщност ще взема Ценностите и ще го накарам да слезе отстрани, добре. Дизайн, оформление на отчета, показване в таблична форма, за да получите предпочитан от мен изглед, Повторете всички етикети на елементи, добре,това е абсолютно невероятно! Сега имаме тази малка малка таблица, 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
