Iga finantsanalüütik on geenius Excel . Kui aga ladustamine muutub odavamaks, koguvad meie organisatsioonid rohkem andmeid. Ja kui neid andmeid on rohkem, on Exceliga töötamine keerulisem - kas tabame 1 048 576 rea limiiti või aeglustab dokument kõike töödeldes.
Sageli otsustatakse hoolikalt korraldatud detailide kaotamise või tüütult aeglase töövihiku vahel töötamise vahel. Mõnikord peame olema loovad, et ühendada kaks suurepärast andmekogumit. Me kasutasime keerukate valemite kasutamist ja ootasime igavesti arvutuste lahendamist.
Õnneks ei pea te enam neid otsuseid tegema. Funktsionaalsus Toide Pivot Excel pakub viisi suurte andmekogumite väljavõtmiseks, kombineerimiseks ja analüüsimiseks. Hoolimata asjaolust, et see ilmus Excelis 2010. aastal, ei tea enamik minu tuttavaid finantsanalüütikuid ikkagi Power Pivoti kasutamist ja paljud isegi ei tea, et see olemas on.
Selles artiklis näitan teile, kuidas kasutada Power Pivot'i mõnede levinumate Exceli probleemide ületamiseks ja heita pilk tarkvara peamistele täiendavatele eelistele, kasutades mõningaid näiteid. See Power Pivoti Exceli jaoks õpetus on mõeldud juhendiks, mida saate selle tööriistaga saavutada, ja lõpuks uurin mõningaid näiteid juhtumitest, kus Power Pivot võib olla hindamatu.
Power Pivot on Microsoft Exceli funktsioon, mis võeti kasutusele Exceli 2010 ja 2013 lisandmoodulina ning on nüüd Exceli 2016. aasta loomulik funktsioon. Seletama Microsoft, Power Pivot for Excel “võimaldab teil importida miljoneid ridu andmeid mitmest andmeallikast ühte Exceli töövihikusse, luua seoseid heterogeensete andmete vahel, luua arvutatud veerge ja mõõta valemite abil, luua PivotTable-liigendtabeleid ja PivotCharts-e ning seejärel andmeid analüüsida põhjalikult, et saaksite ilma IT-meeskonna abita õigeaegselt äriotsuseid teha. '
Microsofti poolt Power Pivotis kasutatav hostiekspressioon on DAX (Data Analysis Expressions), ehkki teisi saab kasutada ka konkreetsetes olukordades. Jällegi, nagu Microsoft selgitab, on DAX funktsioonide, operaatorite ja konstantide kogum, mida saab valemis või avaldises kasutada ühe või mitme väärtuse arvutamiseks ja tagastamiseks. Lihtsamalt öeldes aitab DAX teil luua mudelis juba sisalduvatest andmetest uut teavet. ' Õnneks näivad DAX-valemid teile juba Exceliga tuttavad olevat tuttavad, kuna paljudel valemitel on sarnane süntaks (nt SUM
, AVERAGE
, TRUNC
).
Selgituseks on Power Pivoti vs. Exceli põhitõed võib kokku võtta järgmiselt:
Järgmistes jaotistes vaatan läbi kõik nimetatud eelised ja näitan teile, kuidas Power Pivot for Excel võib teid aidata.
Nagu me juba arutasime, on üks Exceli suurimatest piirangutest seotud eriti suurte andmekogumitega töötamisega. Meie õnneks võib Excel ületada miljoni rea piirangu otse Power Pivotile.
Selle demonstreerimiseks koostasin valimi kahe aasta müügiväärtusega andmekogumist, mis on mõeldud sporditoodete müüjale, millel on üheksa erinevat tootekategooriat ja neli piirkonda. Saadud andmekogum on kaks miljonit rida.
Vahekaardi kasutamine Andmed bändis looge a Uus küsimus CSV-failist (1. näidis). Seda varem kasutatud funktsionaalsust nimetati PowerQuery Kuid alates Excel 2016-st on vahekaardile Exceli andmed integreeritud palju muud.
Näide 1: uue testi loomine
Tühjast Exceli töövihikust liikumine kõigi kahe miljoni rea Power Pivoti üleslaadimiseks võttis aega umbes minut! Pange tähele, et suutsin esimesest reast üles tõustes vormistada natuke kergeid andmeid, nii et neist said veerunimed. Viimastel aastatel on Toite päring on oluliselt paranenud, läks see Exceli lisandmoodulist tööriistariba vahekaardi Andmed integreeritud ossa. Toite päring See võib teie andmeid pöörata, purustada, puhastada ja kujundada mitmesuguste valikute ja omaenda M abil.
Üks Power Pivoti for Exceli peamisi eeliseid on võimalus hõlpsalt importida andmeid mitmest allikast. Varem lõid paljud meist oma mitmete andmeallikate jaoks mitu töölehte. Sageli hõlmas see protsess VBA-koodi kirjutamist ning nendest erinevatest allikatest kopeerimist ja kleepimist. Meie õnneks võimaldab Power Pivot teil importida andmeid erinevatest andmeallikatest otse Excelisse, ilma et peaksite ülalnimetatud probleemidega kokku puutuma.
Funktsiooni kasutamisel Päring Näites 1 saame väljavõtte järgmistest allikatest:
Lisaks saab funktsioonis kombineerida mitut andmeallikat Päring või Power Pivoti aknas andmete integreerimiseks. Näiteks saate Exceli töövihikust välja tuua tootmiskulude andmed ja SQL-serveri kaudu tegelikud müügitulemused Päring Power Pivoti suunas. Sealt saate ühendada need kaks andmekogumit, ühendades tootmispartii numbrid brutomarginaalidega ühiku kohta.
Teine Power Pivoti jaoks Exceli eelis on võime manipuleerida ja töötada koos suurte andmekogumitega, et jõuda asjakohaste järelduste ja analüüsini. Tutvun mõned levinumad näited, et anda teile hinnang tööriista võimsuse eest.
milline neist on portjee viie jõu mudeli kese, mida loengus arutatakse?
Exceli junkid nõustuvad sellega täielikult PivotTables need on ühed kõige kasulikumad ülesanded, kuid samas pettumust tekitavad. Eriti masendav, kui tegemist on suurte andmekogumitega töötamisega. Õnneks võimaldab Power Pivot Exceli jaoks meil kiiresti ja hõlpsalt luua PivotTables suuremate andmekogumitega töötamisel.
Alloleval pildil (2. näide) märkige, kuidas Power Pivoti aken on kaheks paaniks eraldatud. Ülemisel klaasil on andmed ja alumisel on mõõtmised. Mõõt on arvutus, mis viiakse läbi kogu andmekogumi kohta. Mõõdu sisestasin esiletõstetud lahtrisse sisenemisega.
Total Sales:=SUM('Accounting Data'[Amount])
See loob uue mõõdiku, mis liidetakse veeru Kogus kaudu. Samamoodi saan sisestada teise mõõtmise allolevasse lahtrisse.
Average Sales:=AVERAGE('Accounting Data'[Amount])
Näide 2: mõõtmiste loomine
Sealt vaadake, kui kiiresti on a PivotTable-liigendtabel perekond suures andmekogumis.
Näide 3: PivotTable-liigendtabeli loomine
Exceli kasutavate finantsanalüütikutena hakkame oskama keerulisi valemeid kasutada, et tehnoloogiat oma tahtmise järgi painutada. Me domineerime VLOOKUP
, SUMIF
ja isegi kardetud INDEX(MATCH())
. Kuid Power Pivoti abil saame sellest palju lahti.
Näide 4: lisage kasutaja loodud tabel Power Pivoti mudelile
Selle funktsionaalsuse demonstreerimiseks lõin väikese võrdlustabeli, milles määrasin iga kategooria tüübile. Valides 'Lisa andmemudelisse', laaditakse see tabel Power Pivoti (ülaltoodud 4. näidis).
Lõin ka kuupäevade tabeli, mida kasutada meie andmekogumiga (5. näidis). Power Pivoti jaoks Exceli jaoks on lihtne kuupõhiste, kvartalite ja nädalapäevade kaupa konsolideerimise kuupäevade tabelit kiiresti luua. Kasutaja saab luua ka rohkem kohandatud kuupäevade tabeli, mida saab analüüsida nädalate, eelarveaastate või organisatsiooni mis tahes konkreetse rühmituse järgi.
Näide 5: koostage kuupäevade tabel
Peale mõõtmiste on veel üks arvutuste tüüp: arvutusveerud. Exceli kasutajatel on mugav neid valemeid kirjutada, kuna need on väga sarnased kuupäevatabelite valemite tippimisega. Lõin allpool uue arvutusveeru (näidis 6), mis korraldab raamatupidamisandmete tabeli koguse järgi. Alla 50 dollari suurune müük on tähistatud 'Väike' ja ülejäänu 'Suur'. Kas valem ei tundu intuitiivne?
Näide 6: arvutusveeru loomine
Seejärel saame diagrammivaate abil luua seose raamatupidamisandmete tabeli kategooria välja ja kategooria tabeli kategooria välja vahel. Lisaks saame määratleda seose raamatupidamisandmete tabeli välja Müügikuupäev ja tabeli Kalender välja Kuupäev vahel.
Näitus 7: suhete määratlemine
Nüüd, ilma et oleks vaja ühtegi SUMIF
või VLOOKUP
, saame luua a PivotTable-liigendtabel mis arvutas kogumüügi aasta ja tüübi järgi, kasutades tehingu suuruse lõikurit.
Näide 8: PivotTable-liigendtabel suhete abil
Või saame uue kalendritabeli abil luua tavalise müügi graafiku igaks nädalapäevaks.
Näide 9: PivotChart suhteid kasutades
Kuigi see graafik näib lihtne, on muljetavaldav, et rohkem kui kahe miljoni rea andmete konsolideerimise loomiseks kulus vähem kui 10 sekundit, ilma et müügiandmetesse lisataks uut veergu.
Ehkki saame neid konsolideeritud aruandluse stsenaariume läbi viia, võime süveneda ka üksikutesse ridadesse. Me säilitame oma väga üksikasjalikud andmed.
Siiani on enamik näidatud analüüse suhteliselt sirgjoonelised arvutused. Nüüd tahan näidata selle platvormi mõningaid arenenumaid võimalusi.
Sageli soovime finantstulemusi uurides võrrelda neid eelmise aasta võrreldava ajaperioodiga. Power Pivotil on sisseehitatud ajaluure funktsioonid.
Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date])) YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())
Näiteks lisades Power Pivoti tabeli Raamatupidamisandmed kohale ainult kaks mõõdet, saan luua järgmise PivotTable-liigendtabel vaid paari klõpsuga.
Näitus 10: PivotTable-aja intelligentsus
Finantsanalüütikuna on üks probleem, mida sageli lahendama pean, ebaühtlane granuleeritus. Meie näites kuvatakse täpsed müügiandmed kategooriatasemel, kuid koostame eelarve, mis on ainult hooajalisel tasemel. Selle erinevuse suurendamiseks koostame kvartalieelarve, isegi kui müügiandmed on igapäevased.
Näide 11: Granularities - eelarvetabel
Power Pivot for Exceli abil on see vastuolu hõlpsasti lahendatav. Luues kaks täiendavat võrdlustabelit või dimensioonitabelit andmebaasi nomenklatuuris, saame luua sobivad seosed, et analüüsida meie tegelikku müüki eelarvestatud koguste alusel.
Näide 12: granulaarsused - ebaühtlased suhted
corp to corp vs w2 kalkulaator
Excelis järgmine PivotTable-liigendtabel see on kiiresti valmis.
Näide 13: Granularities - ebaühtlane eelarve vs. Tõelised tulemused
Lisaks saame määratleda uued mõõdikud, mis arvutavad tegeliku müügi ja eelarvestatud müügi erinevuse, nagu allpool näidatud:
Actual-to-Budget Variance:=if(ISBLANK([Total Budgeted Sales]),BLANK(),[Total Sales]/[Total Budgeted Sales]-1)
Selle mõõtme abil saame näidata lahknemist a-s PivotTable-liigendtabel .
Näide 14: täpsus on ebaühtlane - erinevad tulemused
Lõpuks uurime konkreetse kategooria müüki protsendina kogu müügist (nt kategooria panus üldmüüki) ja konkreetse kategooria müüki protsendina kogu sama tüüpi müügist (nt panus kategooriast hooajalisse) müük). Lõin kaks allpool näidatud mõõtmist:
Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data')) Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))
Neid meetmeid saab nüüd rakendada uues PivotTable-liigendtabel :
15. proov: kogu protsent
Pange tähele, kuidas arvutusi tehakse mõlemad tasemete tüübid, kategooria ja ajutine tüüp. Mulle meeldib, kuidas neid arvutusi tehakse, nii kiiresti ja vaevata, eriti nii suure andmekogumi puhul. Need on vaid mõned näited Power Pivoti elegantsest ja selgest arvutusjõust.
Teine eelis on see, et failide suurused minimeeritakse. Algne failisuurus oli 91 MB ja on nüüd alla 4 MB. See on 96% originaalfaili tihendamine.
Näide 16: faili suurused
Kuidas see juhtub? Power Pivot kasutab mootorit xVelocity andmete tihendamiseks. Lihtsamalt öeldes salvestatakse andmed ridade asemel veergudesse. See salvestusmeetod võimaldab arvutil dubleerida väärtusi. Meie näidisandmekogumis on ainult neli piirkonda, mis korduvad kahes miljonis reas. Power Pivot for Excel saab neid andmeid tõhusalt salvestada. Tulemuseks on see, et andmete puhul, millel on palju korduvaid väärtusi, maksab nende andmete salvestamine palju vähem.
Midagi olulist on märkida see, et kasutasin selles näidisandmekogumis dollari suuruseid summasid. Kui oleksin sentide kajastamiseks lisanud kaks kümnendkoha täpsust, langeks tihendusefekt muljetavaldava 80% -ni algsest failisuurusest.
Power Pivoti mudelid võivad olla laiendatavad ka kogu ettevõttes. Oletame, et koostate Power Pivoti mudeli, mis hakkab organisatsiooni kasutajaid võitma või andmed kasvavad 10 miljoni reani või mõlemani. Sel hetkel ei pruugi te soovida, et kolmkümmend erinevat kasutajat mudelit värskendaks või muudatusi teeks. Mudeli saab ilma suuremate ebamugavusteta muuta tabeliks SSAS-iks. Kõiki tabeleid ja seoseid säilitatakse, kuid nüüd saate juhtida värskendussagedust, määrata rollid (nt kirjutuskaitstud, lugeda ja töödelda) mitmele kasutajale ning rakendada väikese Exceli esiotsa, mis ühendub mudeliga. . Tulemuseks on see, et teie kasutajad saaksid kasutada juurutatud tabelimudelit koos väikese töövihikuga, kuid nad ei pääseks juurde valemitele ja meetmetele.
Minu klientide üks levinumaid taotlusi on see, et ma koostaksin aruandeid, mis vastavad rangelt määratletud kujundustele. Mul on kliente, kes soovivad konkreetseid veerulaiuseid, RGB värvikoode ning vaikimisi tähenimesid ja -suurusi. Mõelge järgmisele juhtpaneelile:
Näitus 17: Manustatud CUBE-valemid
Kuidas saab müüginumbrid genereerida ilma genereerimata PivotTables Kui kogu meie müük on salvestatud Exceli Power Pivotiga? Kasutades CUBE valemeid! Saame kirjutada CUBE-valemid igasse Exceli lahtrisse ja see teostab arvutuse juba loodud Power Pivoti mudeliga.
Näiteks lahtrisse nime 'Kogu müük 2016' alla on kirjutatud järgmine valem:
SumExpPctTotal
Kollasega esile tõstetud valemi esimene osa viitab Power Pivoti mudeli nimele. Üldiselt kasutatakse ThisWorkbookDataModeli Exceli Power Pivoti uuemate versioonide jaoks. Rohelises osas määratletakse, et me tahame kasutada kogu müügi mõõdikut. Sinise värviga osa juhendab Exceli filtreerima ainult ridu, mille müügikuupäev on võrdne aastaga 2016.
Lava taga on Power Pivot ehitanud a kuup Veebipõhine analüütiline töötlemine (OLAP) koos andmete, arvutatud veergude ja mõõtmistega. See disain võimaldab Exceli kasutajal andmetele juurde pääseda, tuues need lihtsalt funktsioonidega CUBE. Kasutades CUBE-i valemeid, olen suutnud koostada finantsaruanded, mis vastavad vaikeplaanidele. See oskus on üks eeliseid Power Pivot for Exceli kasutamiseks finantsanalüüsis.
Power Pivoti for Exceli teine eelis on see, et saate kiiresti üles ehitada kõik Power Pivoti töövihikud ja muuta need Power BI mudeliks. Importides Exceli töövihiku otse Power BI töölauarakendusse või Power BI võrku, saate oma andmeid analüüsida, visualiseerida ja jagada oma organisatsiooni kõigiga. Põhimõtteliselt on Power BI Power Pivot, PowerQuery ja SharePoint, mis on kõik ühendatud. Allpool lõin juhtpaneeli, importides Power Pivot for Exceli töövihiku Power BI töölauarakendusse. Pange tähele, kui liides on interaktiivne:
Näide 18: Power BI
Midagi suurepärast Power BI-s on loomuliku keele küsimused ja vastused. Demonstreerimiseks laadisin Power BI mudeli üles oma Power BI veebikontole. Veebilehelt saan esitada küsimusi ja Power BI loob teksti sisestamisel asjakohase analüüsi:
Näitus 19: küsimused ja vastused loomulikus keeles
digitaalne meedia vs graafiline disain
Seda tüüpi küsimisvõimalused võimaldavad kasutajal küsida andmemudeli kohta küsimusi ja suhelda andmetega lihtsamalt kui Excelis.
Power BI teine eelis on see, et Microsofti arendajad avaldavad pidevalt värskendusi. Uued funktsioonid, mida kasutajad on soovinud, antakse välja igakuiselt. Mis kõige parem - see on sujuv üleminek Power Pivotilt Excelisse. Nii et DAX-valemite õppimiseks kulutatud aja saab Power BI-s rakendada! Analüütiku jaoks, kes peab oma analüüsi jagama paljude seadmete paljude kasutajatega, tasuks Power BI-d uurida.
Kui olete alustanud, on paar head tava, mida peaksite järgima.
Esimene on teadlik otsustamine, mis kõigepealt oluline on. Kas kasutate kunagi müüja koduaadressi? Kas ma pean selle töövihiku kontekstis teadma oma kliendi e-posti aadressi? Kui eesmärk on andmete lisamine armatuurlauale, pole osa saadaolevatest andmetest nende arvutuste jaoks vajalik. Aega veetmine sissetulevate andmete puhastamisel hõlbustab probleemide koormust ja mälukasutust hiljem, kui teie andmekogum laieneb.
Teine hea tava on meeles pidada, et Power Pivot pole Excel. Excelis oleme harjunud arvutusi looma, laiendades oma tabeleid pidevalt paremale. Power Pivot for Excel töötleb andmeid tõhusalt, kui piirdume sellega sooviga. Selle asemel, et oma andmetest paremal pidevalt arvutusveerge luua, õppige mõõtmeid kirjutama alumisele paanile. See harjumus tagab väiksemad failid ja kiirema arvutamise.
Lõpuks soovitaksin kasutada mõõtmisteks ingliskeelseid üldnimetusi. Mul läks sellega kohanemiseks aega. Veetsin esimesed aastad selliste nimede loomisega nagu Ítem de Línea de Gastos como Porcentaje de Gastos Totales
, kuid kui teised inimesed hakkasid samu töövihikuid kasutama, oli mul palju selgitada. Nüüd, kui alustan uut töövihikut, kasutan selliseid nimesid nagu
|_+_|. Kuigi nimi on pikem, on seda kellelgi teisel palju lihtsam kasutada.
Selles artiklis tutvustasin vaid osa võimalustest, mille abil Power Pivot for Excel võimaldab teil teha mõne olulise sammu, mis ületab Exceli tavalist. Mõtlesin, et oleks kasulik välja tuua mõned reaalse elu juhtumid, kus Power Pivot for Excel on minu jaoks olnud äärmiselt kasulik.
Siin on mõned:
Finantsanalüütikutena palutakse meil teha keerukaid arvutusi andmekogumitele, mis kasvavad jätkuvalt. Kuna Excel on vaikeanalüüsivahend, on Power Pivoti õppimiskõver lihtne ja paljud funktsioonid matkivad Exceli natiivfunktsioone.
CUBE-funktsioonide abil saab Power Pivot for Exceli hõlpsasti integreerida teie olemasolevatesse Exceli töövihikutesse. Arvutustõhususe kasvu ei saa eitada. Eeldades 20% suuremat töötlemiskiirust, mis on konservatiivne, võib finantsanalüütik, kes veedab Excelis kuus tundi päevas, säästa 300 tundi aastas.
Lisaks saame nüüd analüüsida andmekogumeid, mis on pikemad võrreldes nendega, mida me varem oma traditsioonilise Exceli abil analüüsisime. Efektiivselt kujundatud mudelite korral võime saada rohkem kui kümme korda suurema hulga andmeid, kui meile traditsioonilise Exceli abil varem lubati, säilitades samas analüütilise väleduse. Võimalusega teisendada mudeleid Power Pivotist tabelist SSAS-i, on töödeldavate andmete hulk 100–1 000 korda suurem, kui suudame Excelis saavutada.
Power Pivoti jaoks Exceli võime teha arvutusi ülikiirelt, suurte andmemahtude korral ja säilitada siiski võimalus detailidesse sukelduda, võib finantsanalüüsi muuta kohmakatest arvutustabelitest tänapäevasteks töövihikuteks.
Kui olete huvitatud Exceli Power Pivoti proovimisest, leiate allpool mõned materjalid, mis aitavad teil alustada.
Collie, R., ja Singh, A. (2016). Power Pivot ja Power BI: Exceli kasutusjuhend DAX-i, Power Query, Power BI ja Power Pivoti kohta rakenduses Excel 2010-2016. Ameerika Ühendriigid: Püha Makro! Raamatud.
Ferrari, A., ja Russo, M. (2015). DAX-i lõplik juhend: äriteave koos Microsoft Exceli, SQL Serveri analüüsiteenuste ja Power BI-ga. Ameerika Ühendriigid: Microsoft Press, USA.