Екцел модел података (Повер Пивот) омогућава вам повезивање великог броја детаљних података са стварним подацима са буџетом највишег нивоа помоћу столова за табелу.
Буџети се врше на највишем нивоу - приходи по линијама производа по регионима по месецима. Стварности се полако акумулирају - фактура по фактура, ставка по ставка. Поређење малог досијеа о буџету са обимним подацима из стварности је заувек мука. Обожавам овај трик Роба Колија, званог ПоверПивотПро.цом.
Да бисте поставили пример, имате табелу буџета од 54 реда: један ред месечно по региону по производу.
Датотека фактуре је на нивоу детаља: до сада је ове године 422 реда.
Не постоји ВЛООКУП на свету који ће вам икада омогућити да се подударате са ова два скупа података. Али захваљујући Повер Пивот-у (познат и као модел података у програму Екцел 2013+) ово постаје лако.
Треба да направите мале табеле које називам „столари“ како би повезали два већа скупа података. У мом случају су производ, регион и датум заједнички између две табеле. Табела производа је мали сто са четири ћелије. Исто као и за регион. Направите сваку од њих копирањем података из једне табеле и употребом Уклони дупликате.
Табела календара са десне стране заправо је била тежа за израду. Подаци о буџету имају један ред месечно, који увек падају на крај месеца. Подаци на фактури показују дневне датуме, обично радне дане. Дакле, морао сам да копирам поље Датум из оба скупа података у једну колону, а затим уклоним дупликате како бих био сигуран да су представљени сви датуми. Тада =TEXT(J4,"YYYY-MM")сам правио колону Монтх из дневних датума.
Ако немате пуни програмски додатак Повер Пивот, треба да направите пивот табелу из табеле Буџет и означите поље за потврду Додај ове податке у модел података.
Као што је речено у претходном савету, док додајете поља у пивот табелу, мораћете да дефинишете шест односа. Иако сте то могли да урадите са шест посета дијалогу Стварање везе, покренуо сам свој додатак Повер Пивот и користио приказ дијаграма да дефинишем шест односа.
Ево кључа да се све ово уради: Слободно можете користити нумеричка поља из буџета и из стварног. Али ако желите да прикажете регион, производ или месец у пивот табели, они морају да потичу из столова за спајање!
Ево пивот табеле са подацима који долазе из пет табела. Колона А долази од столара Регије. Ред 2 долази од столара за Календар. Резач производа је од столара производа. Бројеви буџета потичу из табеле Буџет, а стварни бројеви потичу из табеле Рачун.
То функционише јер столови за придруживање примењују филтере на табелу Буџет и Стварно. То је прелепа техника која показује да Повер Пивот није намењен само великим подацима.
Погледајте видео
- Имате мали скуп података о буџету од врха надоле
- Желите да упоредите са стварним низом стварних података
- Стварни подаци могу доћи из регистра рачуна
- Модел података омогућиће вам упоређивање ових скупова података различите величине
- Урадите оба скупа података у Цтрл + Т табели
- За свако текстуално поље из којег желите да пријавите направите столу стола
- Копирајте вредности и уклоните дупликате
- За датуме можете укључити датуме из обе табеле и претворити их у крај месеца
- Нека столари буду Цтрл + Т табеле
- Необавезно, али корисно за именовање свих пет табела
- Направите пивот табелу из прорачуна и одаберите модел података
- Направите пивот табелу користећи Буџет и Стварно из оригиналних табела
- Сва остала поља морају долазити из столова за столаре
- Додајте резаче по производу
- Створите три односа од буџета до столара
- Створите три односа од стварног до столарског
- Сутра: како је изградња односа лакша помоћу Повер Пивот и ДАКС формула
Видео транскрипт
Научите Екцел из подцаста, епизода 2016. - Буџет одозго према доле у односу на чињенице одоздо према горе!
Хеј, поткастим целу ову књигу, кликните оно „и“ у горњем десном углу и пратите листу песама.
Хеј, прекинућу ово, овде Бил Јелен од 15 минута. Сад схватам да је ово невероватно дугачак подцаст и у искушењу сте само да га кликнете, али дозволите ми да вам кажем само ово мало. Ако сте у програму Екцел 2013, а икада сте имали малу табелу буџета и масивну табелу стварних података и требате их мапирати заједно, ово је невероватна нова способност коју имамо у програму Екцел 2013, а коју није објаснио много људи , а вероватно не знате за то. Ако сте ово ви, у 2013. сте и треба да мапирате ова два скупа података, одвојите време, можда данас, можда сутра, можда додајте на листу праћења, вредело је, то је невероватна техника.
У реду, ево шта имамо, на левој страни имамо буџет, овај буџет, ради се на највишем нивоу, одозго надоле, тачно за сваку линију производа, за сваки регион, за сваки месец, постоји буџет . Овде нема много записа, броји 55, са десне стране покушавамо да ово упоредимо са стварним подацима. Стварни подаци долазе из регистра рачуна, тако да имамо регију, производ и приход, али то су појединачне фактуре, много више података овде, већ смо на пола године и већ имам 423 записа. Па, како онда мапирати ових 55 са ових 423? Могло би бити тешко урадити са ВЛООКУП-ом, прво бисте морали да резимирате, али на срећу у програму Екцел 2013 модел података ово чини заиста, заиста једноставним. Оно што треба да дозволимо овом великом масивном столу да комуницира са овим малим столом су посредници, ја их називам столарима.Мале табеле, Производ, Регија и Календар, придружићемо буџет овим трима табелама, придружићемо стварне те три табеле, и чудом ће Пивот табела радити. Па, ево како то радимо.
Прво морам да направим столаре, тако да ово поље Продуцт преузмем из колоне А и копирам га у колону Ф, а затим Дата, Ремове Дуплицатес, кликните ОК и остаје нам мала табела, 1 наслов 3 реда. Иста ствар за Регион, узмите регије, Цтрл + Ц, пређите на колону Г, налепи, уклони дупликате, кликни на ОК, 3 реда 1 заглавље, у реду. Сада за датуме датуми нису исти, ово су датуми завршетка месеца, они се заправо чувају као датуми завршетка месеца, а ово су радни дани. Узећу обе листе, Цтрл + Ц другу листу и залепити је овде, Цтрл + В, затим ћу узети краћу листу, копирати је и залепити доле, у реду. И заиста је досадно што се, иако су сачувани као датуми, појављују као месеци, а Ремове Дуплицатес их неће видети као исте.Дакле, пре него што користим Ремове Дуплицатес, морам да га променим на кратак датум. Изаберите податке, Подаци, Уклони дупликате, кликните на дугме У реду, а затим мало сортирајте овде да бисте то покренули.
У реду, сада не желим да извештавам по дневном датуму, па ћу овде додати колону, колону за претрагу која каже Месец, а ово ће бити једнако ЕОМОНТХ тог датума,, 0, што ће нас одвести до крај месеца. То ће форматирати као кратак датум и копирати га, у реду. Сада треба да направимо сваку од њих у Цтрл + Т табели, тако да одавде Цтрл + Т, Моја табела има заглавља, прелепо. Они мали, не схвата да су то заглавља горе, па морамо обавезно означити то и Цтрл + Т, у реду, и они називају ове табеле Табела1, Табела2, Табела3, заиста досадна имена, зар не? Тако да ћу их преименовати и назвати БудТабле, ПродТабле, РегТабле, ми ЦалТабле, а затим АцтТабле, у реду.
Почињемо од прве табеле, и иначе нећемо данас користити ПоверПивот, већ ћемо све ово урадити са моделом података. Дакле, Екцел 2013 или новији, имате овај Уметак, заокретну табелу, означићемо поље за потврду „Додај ове податке у модел података“, кликните на дугме У реду и добићемо нашу листу поља са чаробним дугметом Све, које омогућава ја бирам између свих пет табела у радној свесци, Стварно, Буџет, Календар, Производ, Регија. У реду, тако да ће бројеви долазити из табеле Буџет, ја ћу тамо убацити буџет, а из табеле Стварно убацити стварни, али ево ствари за остатак Пивот табеле. Било која друга текстуална поља која ћемо ставити у подручје реда или у колону или као резачи, они морају да потичу од столара, они морају да долазе из оних табела између табела.
У реду, па ћемо из табеле календара узети то поље Месец и ставити га преко врха, а сада ћемо занемарити друге везе. Ја ћу стварати везе, али желим да их створим одједном. А табела Регион, спусти регионе са стране. Могао бих да одложим производе са стране, али уствари ћу користити табелу Продуцт као резач, па Анализирајте, Уметните резач, опет морате да одете на Све ако још нисте користили табелу Продуцт. Дакле, идите на Све и видећете да је Производ доступан за стварање као резач од производа, попут тога. У реду, у овом тренутку нисмо створили везе, па су сви ови бројеви погрешни. А односи које морамо створити, морамо створити 3 табеле од ове мале буџетске табеле, једну према производима, једну према регионима, једну према календару,то су 3 везе. А онда морамо створити везе из стварне табеле са производном регијом у календару, дакле укупно 6 табела. И да, ово би дефинитивно било лакше да имамо ПоверПивот, али немамо или претпоставимо да немамо.
И тако ћу се послужити старомодним начином, дијалог Цреате овде, где имамо леву табелу буџета, а користићемо поље Регион и повезати то са табелом Регион, поље Регион . У реду, 1/6 су створени. Изабраћу Створи, поново из табеле Буџет идемо на Производ, а затим га повежем са табелом Производ, са Производом, кликните ОК. Из табеле Буџет из поља Датум идемо у табелу Календар, а у поље Фате кликните ОК, на пола смо пута, у реду. Из табеле „Актуелно“ идемо „Регион“, у „Регион“, кликните на „ОК“, из табеле „Ацтуал“ на „Продуцт“, а из „Ацтуал“ на „Цалендар“. Заправо ћу узети вредности и натерати га да се спусти са стране, у реду. Дизајн, изглед извештаја, приказ у табеларном облику да бисте добили поглед који више волим, Поновите све ознаке ставки, у реду,ово је апсолутно невероватно! Сада имамо ову мајушну малу табелу, 50-так записа у овој табели са стотинама записа, а створили смо једну стожерну табелу захваљујући моделу података. За свако место где можемо видети буџет можемо видети приход, рашчлањен је по регионима, рашчлањен по месецима и распоређен по производима.
Сад ми је овај концепт дошао од Роба Цоллиеја који води Повер Пивот Про, а Роб је створио пуно књига, а његова најновија је „Повер Пивот и Повер БИ“. Мислим да је овај заправо био у књизи „Повер Пивот Алцхеми“, управо сам њега видио и рекао сам „Па ово, иако немам милионе редова за извјештавање путем Повер Пивота, ово је онај који би направили су ВЕЛИКУ разлику у мом животу, имајући два скупа података који се не подударају и који су морали да извештавају из оба. “ Па, овај пример и многи други су у овој књизи, на крају ћу добити цео подцаст књиге, изгледа да ће требати два и по месеца. Али данас можете добити целу књигу, у исто време, отићи тамо, купити књигу, 10 долара за е-књигу, 25 долара за штампану књигу и можете све те савете добити одједном.
У реду, овде имамо заиста дугу епизоду: имамо мали буџет одозго надоле и дно горе Стварно, они су различитих величина, али користећи модел података у програму Екцел 2013 … И успут, ако сте у 2010. години, могли бисте , у теорији, урадите то тако што ћете добити Повер Пивот додатак и прођите кроз све ове кораке још 2010. Направите оба низа података у Цтрл + Т табелу, а затим придружите своје табеле за све о чему желите да извештавате, у ознака реда или ознака колоне или резачи, па копирајте те вредности и уклоните дупликате за датуме. Заправо сам узео вредности из обе табеле, јер су у свакој постојале неке јединствене вредности, а затим сам искористио ЕОМОНТХ да бих изашао тамо и учинио да те столарске табеле буду контролисане табеле. Није обавезно, али именовао сам свих 5 табела, јер је лакше када постављате те односе, уместо да вас зову Табела1,Табела2, Табела3.
И тако, почните од табеле Буџет, Уметни, Заокретна табела, означите поље за модел података, а затим направите заокретну табелу користећи Буџет и Стварно. Све остало долази из столова за столаре, па су Регион и месец у подручју редова и ступаца, резачи долазили из табеле производа. А онда смо морали да створимо 3 односа из буџета према столарима, 3 односа од стварног према столарима и имамо невероватну стожерну табелу. Сада ћемо сутра погледати употребу картице Повер Пивот и креирање неких додатних прорачуна. Дакле, све ово је могуће, када желимо да убацимо израчунато поље, тада морате да платите додатних 2 долара месечно да бисте добили Про Плус верзију Оффице 365.
Па, хеј, хвала Робу Цоллиеју из Повер Пивот Про-а на овом савету и хвала вам што сте навратили, видимо се следећи пут за још једно емитовање од!
Скини докуменат
Преузмите датотеку узорка овде: Подцаст2016.клск
