Stel dat u meerdere projecten uitvoert met verschillende budgetten en dat u voor elk daarvan uw kosten wilt visualiseren. Dat wil zeggen, uit deze brontabel:
.. krijg zoiets als dit:
Met andere woorden, u moet het budget over de dagen van elk project spreiden en een vereenvoudigde versie van het project-Gantt-diagram krijgen. Dit met je handen doen is lang en saai, macro's zijn moeilijk, maar Power Query voor Excel toont in zo'n situatie zijn kracht in al zijn glorie.
Power Query is een add-on van Microsoft die gegevens uit bijna elke bron in Excel kan importeren en deze vervolgens op verschillende manieren kan transformeren. In Excel 2016 is deze invoegtoepassing al standaard ingebouwd en voor Excel 2010-2013 kan deze worden gedownload van de Microsoft-website en vervolgens op uw pc worden geïnstalleerd.
Laten we eerst onze originele tafel veranderen in een "slimme" tafel door het commando te kiezen Opmaken als een tabel tab Home (Home - Opmaken als tabel) of door op de sneltoets te drukken Ctrl+T :
Ga dan naar het tabblad Data (als je Excel 2016 hebt) of op het tabblad Power Query (als u Excel 2010-2013 hebt en Power Query als een afzonderlijke invoegtoepassing hebt geïnstalleerd) en klikt u op de knop Uit tabel / bereik. :
Onze slimme tabel wordt geladen in de Power Query-query-editor, waar de eerste stap is om de getalnotaties voor elke kolom in te stellen met behulp van de vervolgkeuzelijsten in de tabelkop:
Om het budget per dag te berekenen, moet u de duur van elk project berekenen. Selecteer hiervoor (toets ingedrukt houden) Ctrl) kolom eerst Finish, En vervolgens Start en kies een team Kolom toevoegen – Datum – Dagen aftrekken (Kolom toevoegen — Datum — Dagen aftrekken):
De resulterende aantallen zijn 1 minder dan nodig, omdat we verondersteld worden elk project op de eerste dag 's ochtends te starten en op de laatste dag 's avonds af te ronden. Selecteer daarom de resulterende kolom en voeg er een eenheid aan toe met het commando Transformeren – Standaard – Toevoegen (Transformeren — Standaard — Toevoegen):
Laten we nu een kolom toevoegen waarin we het budget per dag berekenen. Om dit te doen, op het tabblad Kolom toevoegen ik speel niet Aangepaste kolom (Aangepaste kolom) en voer in het venster dat verschijnt de naam van het nieuwe veld en de berekeningsformule in, met behulp van de namen van de kolommen uit de lijst:
Nu het meest subtiele moment - we maken nog een berekende kolom met een lijst met datums van begin tot eind met een stap van 1 dag. Om dit te doen, drukt u nogmaals op de knop Aangepaste kolom (Aangepaste kolom) en gebruik de ingebouwde Power Query-taal M, die wordt genoemd Lijst.Data:
Deze functie heeft drie argumenten:
- startdatum - in ons geval is het overgenomen uit de kolom Start
- het aantal te genereren datums - in ons geval is dit het aantal dagen voor elk project, dat we eerder in de kolom hebben geteld Aftrekking
- tijdstap - ingesteld door ontwerp #duur(1,0,0,0), wat betekent in de taal van M - een dag, nul uur, nul minuten, nul seconden.
Na het klikken op OK we krijgen een lijst (Lijst) met datums, die kan worden uitgebreid tot nieuwe regels met behulp van de knop in de tabelkop:
… en we krijgen:
Nu hoeft u alleen nog de tabel samen te vouwen en de gegenereerde datums te gebruiken als namen voor de nieuwe kolommen. Het team is hiervoor verantwoordelijk. Detailkolom (Spilkolom) tab Converteren (Transformeren):
Na het klikken op OK we krijgen een resultaat dat heel dicht bij het gewenste resultaat ligt:
Null is in dit geval een analoog van een lege cel in Excel.
Het blijft om onnodige kolommen te verwijderen en de resulterende tabel naast de originele gegevens te verwijderen met de opdracht Sluiten en laden – Sluiten en laden in… (Sluiten & laden — Sluiten & laden naar...) tab Home (Home):
We krijgen als resultaat:
Voor meer schoonheid kunt u het uiterlijk van de resulterende slimme tabellen op het tabblad aanpassen Constructeur (Ontwerp): een enkele kleurstijl instellen, filterknoppen uitschakelen, totalen inschakelen, enz. Bovendien kunt u een tabel met datums selecteren en nummermarkering ervoor inschakelen met behulp van voorwaardelijke opmaak op het tabblad Home — Voorwaardelijke opmaak — Kleurschalen (Home - Voorwaardelijke opmaak - Kleurschalen):
En het beste is dat u in de toekomst veilig oude kunt bewerken of nieuwe projecten aan de oorspronkelijke tabel kunt toevoegen, en vervolgens met de rechtermuisknop de juiste tabel kunt bijwerken met datums - en Power Query herhaalt alle acties die we automatisch hebben gedaan .
Voila!
- Gantt-diagram in Excel met voorwaardelijke opmaak
- Project mijlpaalkalender
- Dubbele rijen genereren met Power Query