Lad os sige, at du kører flere projekter med forskellige budgetter og ønsker at visualisere dine omkostninger for hvert af dem. Det vil sige fra denne kildetabel:
.. få noget som dette:
Du skal med andre ord sprede budgettet over dagene for hvert projekt og få en forenklet version af projektets Gantt-diagram. At gøre dette med dine hænder er langt og kedeligt, makroer er svært, men Power Query til Excel i en sådan situation viser sin kraft i al sin pragt.
Strømforespørgsel er en tilføjelse fra Microsoft, der kan importere data til Excel fra næsten enhver kilde og derefter transformere den på en masse forskellige måder. I Excel 2016 er dette tilføjelsesprogram allerede indbygget som standard, og til Excel 2010-2013 kan det downloades fra Microsofts hjemmeside og derefter installeres på din pc.
Lad os først forvandle vores originale tabel til en "smart" tabel ved at vælge kommandoen Formater som en tabel fanen Home (Hjem — Formater som tabel) eller ved at trykke på tastaturgenvejen Ctrl+T :
Gå derefter til fanen data (hvis du har Excel 2016) eller på fanen Strømforespørgsel (hvis du har Excel 2010-2013, og du har installeret Power Query som et separat tilføjelsesprogram), og klik på knappen Fra tabel/område. :
Vores smarte tabel indlæses i Power Query-forespørgselseditoren, hvor det første trin er at konfigurere talformaterne for hver kolonne ved hjælp af rullemenuerne i tabeloverskriften:
For at beregne budgettet pr. dag skal du beregne varigheden af hvert projekt. For at gøre dette skal du vælge (hold tasten nede Ctrl) kolonne først Finish, Og derefter Starten og vælg et hold Tilføj kolonne – Dato – Træk dage fra (Tilføj kolonne — Dato — Træk dage fra):
De resulterende tal er 1 mindre end nødvendigt, fordi vi formodes at starte hvert projekt den første dag om morgenen og afslutte den sidste dag om aftenen. Derfor skal du vælge den resulterende kolonne og tilføje en enhed til den ved hjælp af kommandoen Transform – Standard – Tilføj (Transform — Standard — Tilføj):
Lad os nu tilføje en kolonne, hvor vi beregner budgettet pr. dag. For at gøre dette, på fanen Tilføj kolonne Jeg spiller ikke Brugerdefineret kolonne (Tilpasset kolonne) og indtast navnet på det nye felt og beregningsformlen i det vindue, der vises, ved at bruge navnene på kolonnerne fra listen:
Nu er det mest subtile øjeblik - vi opretter endnu en beregnet kolonne med en liste over datoer fra start til slut med et trin på 1 dag. For at gøre dette skal du trykke på knappen igen Brugerdefineret kolonne (Tilpasset kolonne) og brug det indbyggede Power Query-sprog M, som kaldes Liste.Datoer:
Denne funktion har tre argumenter:
- startdato – i vores tilfælde er det taget fra kolonnen Starten
- antallet af datoer, der skal genereres – i vores tilfælde er dette antallet af dage for hvert projekt, som vi talte tidligere i kolonnen Subtraktion
- tidstrin – sat efter design #varighed(1,0,0,0), hvilket betyder på sproget M - en dag, nul timer, nul minutter, nul sekunder.
Efter at have klikket på OK vi får en liste (Liste) over datoer, som kan udvides til nye linjer ved at bruge knappen i tabeloverskriften:
… og vi får:
Nu er der kun tilbage at skjule tabellen ved at bruge de genererede datoer som navne på de nye kolonner. Teamet er ansvarlig for dette. Detalje kolonne (pivotkolonne) fanen Konverter (Transformer):
Efter at have klikket på OK vi får et resultat meget tæt på det ønskede:
Null er i dette tilfælde en analog af en tom celle i Excel.
Det er tilbage at fjerne unødvendige kolonner og fjerne den resulterende tabel ved siden af de originale data med kommandoen Luk og indlæs – Luk og læs ind... (Luk og indlæs — Luk og indlæs til...) fanen Home (Hjem):
Vi får som resultat:
For større skønhed kan du tilpasse udseendet af de resulterende smarte borde på fanen Constructor (Design): indstil en enkelt farvestil, deaktiver filterknapper, aktiver totaler osv. Derudover kan du vælge en tabel med datoer og aktivere nummerfremhævning for den ved hjælp af betinget formatering på fanen Hjem — Betinget formatering — Farveskalaer (Hjem — Betinget formatering — Farveskalaer):
Og det bedste er, at du i fremtiden sikkert kan redigere gamle eller tilføje nye projekter til den originale tabel, og derefter opdatere den rigtige tabel med datoer med højre museknap – og Power Query gentager automatisk alle de handlinger, vi har udført. .
Sådan!
- Gantt-diagram i Excel ved hjælp af betinget formatering
- Projekt milepælskalender
- Generering af duplikerede rækker med Power Query