Kom ons sê dat jy verskeie projekte met verskillende begrotings bedryf en jou koste vir elk van hulle wil visualiseer. Dit wil sê uit hierdie brontabel:
.. kry so iets:
Met ander woorde, jy moet die begroting oor die dae van elke projek versprei en 'n vereenvoudigde weergawe van die projek Gantt-kaart kry. Om dit met jou hande te doen is lank en vervelig, makro's is moeilik, maar Power Query vir Excel in so 'n situasie wys sy krag in al sy glorie.
Kragnavraag is 'n byvoeging van Microsoft wat data vanaf byna enige bron in Excel kan invoer en dit dan op 'n klomp verskillende maniere kan transformeer. In Excel 2016 is hierdie byvoeging reeds by verstek ingebou, en vir Excel 2010-2013 kan dit van die Microsoft-webwerf afgelaai word en dan op jou rekenaar geïnstalleer word.
Kom ons verander eers ons oorspronklike tabel in 'n "slim" tabel deur die opdrag te kies Formateer as 'n tabel tab What is This (Tuis — Formateer as tabel) of deur die sleutelbordkortpad te druk Ctrl+T :
Gaan dan na die oortjie data (as jy Excel 2016 het) of op die blad Kragnavraag (as jy Excel 2010-2013 het en jy Power Query as 'n aparte byvoeging geïnstalleer het) en klik op die Van Tabel / Reeks-knoppie. :
Ons slim tabel word in die Power Query-navraagredigeerder gelaai, waar die eerste stap is om die getalformate vir elke kolom op te stel deur die aftreklys in die tabelopskrif te gebruik:
Om die begroting per dag te bereken, moet jy die duur van elke projek bereken. Om dit te doen, kies (hou die sleutel in Ctrl) kolom eerste Voltooi, En dan begin en kies 'n span Voeg kolom by – Datum – Trek dae af (Voeg kolom by – Datum – Trek dae af):
Die gevolglike getalle is 1 minder as wat nodig is, want ons is veronderstel om elke projek op die eerste dag in die oggend te begin en op die laaste dag in die aand klaar te maak. Kies dus die resulterende kolom en voeg 'n eenheid daarby met die opdrag Transformeer – Standaard – Voeg by (Transformeer — Standaard — Voeg by):
Kom ons voeg nou 'n kolom by waar ons die begroting per dag bereken. Om dit te doen, op die blad Voeg kolom by Ek speel nie Gepasmaakte kolom (Gepasmaakte kolom) en in die venster wat verskyn, voer die naam van die nuwe veld en die berekeningsformule in, deur die name van die kolomme uit die lys te gebruik:
Nou die mees subtiele oomblik - ons skep nog 'n berekende kolom met 'n lys van datums van begin tot einde met 'n stap van 1 dag. Om dit te doen, druk weer die knoppie Gepasmaakte kolom (Gepasmaakte kolom) en gebruik die ingeboude Power Query-taal M, wat genoem word Lys.Datums:
Hierdie funksie het drie argumente:
- begindatum – in ons geval is dit uit die kolom geneem begin
- die aantal datums wat gegenereer moet word – in ons geval is dit die aantal dae vir elke projek, wat ons vroeër in die kolom getel het Aftrek
- tydstap – bepaal deur ontwerp #duur(1,0,0,0), wat in die taal van M beteken – een dag, nul ure, nul minute, nul sekondes.
Na kliek op OK ons kry 'n lys (Lys) van datums, wat uitgebrei kan word in nuwe reëls met die knoppie in die tabelopskrif:
… en ons kry:
Al wat nou oorbly, is om die tabel in te vou, deur die gegenereerde datums as die name vir die nuwe kolomme te gebruik. Die span is hiervoor verantwoordelik. Detail kolom (Swaaikolom) tab Skakel (Transformeer):
Na kliek op OK ons kry 'n resultaat baie naby aan die gewenste een:
Null is in hierdie geval 'n analoog van 'n leë sel in Excel.
Dit bly om onnodige kolomme te verwyder en die resulterende tabel langs die oorspronklike data met die opdrag af te laai Maak toe en laai – Maak toe en laai in... (Maak toe en laai — Maak toe en laai na …) tab What is This (Tuis):
Ons kry as gevolg daarvan:
Vir groter skoonheid kan jy die voorkoms van die gevolglike slim tafels op die oortjie aanpas konstruktor (Ontwerp): stel 'n enkele kleurstyl, deaktiveer filterknoppies, aktiveer totale, ens. Daarbenewens kan jy 'n tabel met datums kies en nommeruitlig daarvoor aktiveer deur gebruik te maak van voorwaardelike formatering op die blad Tuis — Voorwaardelike formatering — Kleurskale (Tuis — Voorwaardelike formatering — Kleurskale):
En die beste deel is dat jy in die toekoms veilig oues kan redigeer of nuwe projekte by die oorspronklike tabel kan voeg, en dan die regte tabel met datums kan bywerk met die regter muisknoppie – en Power Query sal al die aksies wat ons gedoen het outomaties herhaal .
Siedaar!
- Gantt-grafiek in Excel met behulp van voorwaardelike formatering
- Projek mylpaal kalender
- Genereer duplikaatrye met Power Query