La oss si at du kjører flere prosjekter med forskjellige budsjetter og ønsker å visualisere kostnadene dine for hvert av dem. Det vil si fra denne kildetabellen:
.. få noe sånt som dette:
Du må med andre ord spre budsjettet over dagene i hvert prosjekt og få en forenklet versjon av prosjektets Gantt-diagram. Å gjøre dette med hendene er langt og kjedelig, makroer er vanskelig, men Power Query for Excel i en slik situasjon viser sin kraft i all sin prakt.
Strømforespørsel er et tillegg fra Microsoft som kan importere data til Excel fra nesten alle kilder og deretter transformere det på en rekke forskjellige måter. I Excel 2016 er dette tillegget allerede innebygd som standard, og for Excel 2010-2013 kan det lastes ned fra Microsofts nettsted og deretter installeres på din PC.
Først, la oss gjøre vår originale tabell om til en "smart" tabell ved å velge kommandoen Formater som en tabell tab Hjemprodukt (Hjem — Formater som tabell) eller ved å trykke på hurtigtasten Ctrl+T :
Gå deretter til fanen Data (hvis du har Excel 2016) eller på fanen Strømforespørsel (hvis du har Excel 2010-2013 og du installerte Power Query som et separat tillegg) og klikk Fra tabell/område-knappen. :
Vår smarte tabell lastes inn i Power Query-spørringseditoren, hvor det første trinnet er å sette opp tallformatene for hver kolonne ved å bruke rullegardinmenyene i tabelloverskriften:
For å beregne budsjettet per dag, må du beregne varigheten av hvert prosjekt. For å gjøre dette, velg (hold nede tasten Ctrl) kolonne først Finish, Og deretter Start og velg et lag Legg til kolonne – Dato – Trekk fra dager (Legg til kolonne – Dato – Trekk fra dager):
De resulterende tallene er 1 mindre enn nødvendig, fordi vi skal starte hvert prosjekt på den første dagen om morgenen og avslutte den siste dagen om kvelden. Velg derfor den resulterende kolonnen og legg til en enhet til den ved å bruke kommandoen Transform – Standard – Legg til (Transform – Standard – Legg til):
La oss nå legge til en kolonne der vi beregner budsjettet per dag. For å gjøre dette, på fanen Legg til kolonne Jeg spiller ikke Egendefinert kolonne (Egendefinert kolonne) og i vinduet som vises, skriv inn navnet på det nye feltet og beregningsformelen ved å bruke navnene på kolonnene fra listen:
Nå er det mest subtile øyeblikket – vi lager en annen beregnet kolonne med en liste over datoer fra start til slutt med et trinn på 1 dag. For å gjøre dette, trykk på knappen igjen Egendefinert kolonne (Egendefinert kolonne) og bruk det innebygde Power Query-språket M, som kalles Liste.datoer:
Denne funksjonen har tre argumenter:
- startdato – i vårt tilfelle er den hentet fra kolonnen Start
- antall datoer som skal genereres – i vårt tilfelle er dette antall dager for hvert prosjekt, som vi telte tidligere i kolonnen Subtraksjon
- tidstrinn – satt etter design #varighet(1,0,0,0), som betyr på språket M - en dag, null timer, null minutter, null sekunder.
Etter å ha klikket på OK vi får en liste (Liste) over datoer, som kan utvides til nye linjer ved å bruke knappen i tabelloverskriften:
… og vi får:
Nå gjenstår det bare å skjule tabellen ved å bruke de genererte datoene som navn på de nye kolonnene. Det er laget som har ansvaret for dette. Detaljkolonne (pivotkolonne) tab Konverter (Forvandle):
Etter å ha klikket på OK vi får et resultat veldig nær ønsket:
Null er i dette tilfellet en analog av en tom celle i Excel.
Det gjenstår å fjerne unødvendige kolonner og laste ut den resulterende tabellen ved siden av de originale dataene med kommandoen Lukk og last – Lukk og last inn... (Lukk og last – Lukk og last til...) tab Hjemprodukt (Hjem):
Vi får som et resultat:
For større skjønnhet kan du tilpasse utseendet til de resulterende smartbordene på fanen Constructor (Design): angi en enkelt fargestil, deaktiver filterknapper, aktiver totaler osv. I tillegg kan du velge en tabell med datoer og aktivere tallutheving for den ved å bruke betinget formatering på fanen Hjem — Betinget formatering — Fargeskalaer (Hjem — Betinget formatering — Fargeskalaer):
Og det beste er at du i fremtiden trygt kan redigere gamle eller legge til nye prosjekter i den opprinnelige tabellen, og deretter oppdatere den riktige tabellen med datoer med høyre museknapp – og Power Query vil gjenta alle handlingene vi har gjort automatisk .
Det!
- Gantt-diagram i Excel ved bruk av betinget formatering
- Prosjektets milepælskalender
- Generer dupliserte rader med Power Query