Låt oss säga att du driver flera projekt med olika budgetar och vill visualisera dina kostnader för vart och ett av dem. Det vill säga från denna källtabell:
.. skaffa något sånt här:
Med andra ord måste du fördela budgeten över dagarna för varje projekt och få en förenklad version av projektets Gantt-diagram. Att göra detta med händerna är långt och tråkigt, makron är svårt, men Power Query för Excel i en sådan situation visar sin kraft i all ära.
Strömfråga är ett tillägg från Microsoft som kan importera data till Excel från nästan vilken källa som helst och sedan omvandla det på en massa olika sätt. I Excel 2016 är detta tillägg redan inbyggt som standard, och för Excel 2010-2013 kan det laddas ner från Microsofts webbplats och sedan installeras på din PC.
Låt oss först förvandla vår ursprungliga tabell till en "smart" tabell genom att välja kommandot Formatera som en tabell fliken Hem (Hem — Formatera som tabell) eller genom att trycka på kortkommandot ctrl+T :
Gå sedan till fliken Data (om du har Excel 2016) eller på fliken Strömfråga (om du har Excel 2010-2013 och du installerade Power Query som ett separat tillägg) och klicka på knappen Från tabell/intervall. :
Vår smarta tabell läses in i Power Query-frågeredigeraren, där det första steget är att ställa in talformaten för varje kolumn med hjälp av rullgardinsmenyn i tabellrubriken:
För att beräkna budgeten per dag måste du beräkna varaktigheten för varje projekt. För att göra detta, välj (håll ned knappen ctrl) kolumnen först Finish, Och sedan Start och välj ett lag Lägg till kolumn – Datum – Subtrahera dagar (Lägg till kolumn – Datum – Subtrahera dagar):
De resulterande siffrorna är 1 mindre än nödvändigt, eftersom vi ska starta varje projekt den första dagen på morgonen och avsluta den sista dagen på kvällen. Välj därför den resulterande kolumnen och lägg till en enhet till den med kommandot Transformera – Standard – Lägg till (Transformera — Standard — Lägg till):
Låt oss nu lägga till en kolumn där vi beräknar budgeten per dag. För att göra detta, på fliken Lägg till kolumn Jag spelar inte Anpassad kolumn (Anpassad kolumn) och i fönstret som visas anger du namnet på det nya fältet och beräkningsformeln, med hjälp av namnen på kolumnerna från listan:
Nu är det mest subtila ögonblicket – vi skapar ytterligare en beräknad kolumn med en lista över datum från början till slut med ett steg på 1 dag. För att göra detta, tryck på knappen igen Anpassad kolumn (Anpassad kolumn) och använd det inbyggda Power Query-språket M, som kallas Lista.Datum:
Denna funktion har tre argument:
- startdatum – i vårt fall är det hämtat från kolumnen Start
- antalet datum som ska genereras – i vårt fall är detta antalet dagar för varje projekt, som vi räknade tidigare i kolumnen Subtraktion
- tidssteg – designat #duration(1,0,0,0), vilket betyder på språket M – en dag, noll timmar, noll minuter, noll sekunder.
Efter att ha klickat på OK vi får en lista (lista) med datum, som kan utökas till nya rader med knappen i tabellhuvudet:
... och vi får:
Nu återstår bara att komprimera tabellen och använda de genererade datumen som namn på de nya kolumnerna. Teamet ansvarar för detta. Detalj kolumn (pivotkolumn) fliken Konvertera (Omvandla):
Efter att ha klickat på OK vi får ett resultat mycket nära det önskade:
Null är i det här fallet en analog till en tom cell i Excel.
Det återstår att ta bort onödiga kolumner och ta bort den resulterande tabellen bredvid originaldata med kommandot Stäng och ladda – Stäng och ladda in... (Stäng & ladda — Stäng & ladda till...) fliken Hem (Hem):
Vi får som resultat:
För större skönhet kan du anpassa utseendet på de resulterande smarta borden på fliken Konstruktör (Design): ställ in en enfärgsstil, inaktivera filterknappar, aktivera totalsummor, etc. Dessutom kan du välja en tabell med datum och aktivera nummermarkering för den med villkorlig formatering på fliken Hem — Villkorlig formatering — Färgskalor (Hem — Villkorlig formatering — Färgskalor):
Och det bästa är att du i framtiden säkert kan redigera gamla eller lägga till nya projekt i den ursprungliga tabellen och sedan uppdatera rätt tabell med datum med höger musknapp – och Power Query kommer att upprepa alla åtgärder vi har gjort automatiskt .
Voilà!
- Gantt-diagram i Excel med villkorlig formatering
- Projektets milstolpekalender
- Generera dubbletter av rader med Power Query