Să presupunem că derulați mai multe proiecte cu bugete diferite și doriți să vă vizualizați costurile pentru fiecare dintre ele. Adică din acest tabel sursă:
.. obțineți ceva de genul acesta:
Cu alte cuvinte, trebuie să împărțiți bugetul pe parcursul zilelor fiecărui proiect și să obțineți o versiune simplificată a diagramei Gantt a proiectului. A face asta cu mâinile este lung și plictisitor, macrocomenzile sunt dificile, dar Power Query pentru Excel într-o astfel de situație își arată puterea în toată gloria.
Interogare de alimentare este un supliment de la Microsoft care poate importa date în Excel din aproape orice sursă și apoi le poate transforma într-o grămadă de moduri diferite. În Excel 2016, acest program de completare este deja încorporat în mod implicit, iar pentru Excel 2010-2013 poate fi descărcat de pe site-ul Microsoft și apoi instalat pe computer.
Mai întâi, să transformăm tabelul nostru original într-un tabel „inteligent”, alegând comanda Formatați ca tabel fila Acasă (Acasă — Format ca tabel) sau prin apăsarea comenzii rapide de la tastatură Ctrl+T :
Apoi accesați fila Date (dacă aveți Excel 2016) sau pe filă Interogare de alimentare (dacă aveți Excel 2010-2013 și ați instalat Power Query ca supliment separat) și faceți clic pe butonul Din tabel / interval. :
Tabelul nostru inteligent este încărcat în editorul de interogări Power Query, unde primul pas este să configurați formatele de numere pentru fiecare coloană folosind meniurile drop-down din antetul tabelului:
Pentru a calcula bugetul pe zi, trebuie să calculați durata fiecărui proiect. Pentru a face acest lucru, selectați (ține apăsată tasta Ctrl) prima coloană finalizarea, Și apoi acasă și alegeți o echipă Adăugați coloana – Data – Scădeți zile (Adăugați coloană — Data — Scădeți zile):
Numerele rezultate sunt cu 1 mai puțin decât este necesar, deoarece ar trebui să începem fiecare proiect în prima zi dimineața și să terminăm în ultima zi seara. Prin urmare, selectați coloana rezultată și adăugați o unitate la ea folosind comanda Transformare – Standard – Adăugare (Transformare — Standard — Adăugați):
Acum să adăugăm o coloană în care calculăm bugetul pe zi. Pentru a face acest lucru, pe fila Adăugați o coloană Eu nu joc Coloană personalizată (Coloană personalizată) iar în fereastra care apare, introduceți numele noului câmp și formula de calcul, folosind numele coloanelor din listă:
Acum cel mai subtil moment – creăm o altă coloană calculată cu o listă de date de la început până la sfârșit cu un pas de 1 zi. Pentru a face acest lucru, apăsați din nou butonul Coloană personalizată (Coloană personalizată) și utilizați limbajul încorporat Power Query M, care este numit Listă.Datele:
Această funcție are trei argumente:
- data de începere – în cazul nostru, este luată din coloană acasă
- numărul de date care urmează să fie generate – în cazul nostru, acesta este numărul de zile pentru fiecare proiect, pe care le-am numărat mai devreme în coloană Scădere
- pas de timp – stabilit prin proiect #duration(1,0,0,0), adică în limbajul lui M – o zi, zero ore, zero minute, zero secunde.
După ce faceți clic pe OK obținem o listă (Lista) de date, care poate fi extinsă în linii noi folosind butonul din antetul tabelului:
... și obținem:
Acum nu mai rămâne decât să restrângeți tabelul, folosind datele generate ca nume pentru noile coloane. Echipa este responsabilă pentru asta. Coloana de detaliu (Coloană pivot) fila Converti (Transforma):
După ce faceți clic pe OK obținem un rezultat foarte apropiat de cel dorit:
Null este, în acest caz, un analog al unei celule goale în Excel.
Rămâne să eliminați coloanele inutile și să descărcați tabelul rezultat lângă datele originale cu comanda Închideți și încărcați - Închideți și încărcați în... (Închidere și încărcare — Închidere și încărcare la...) fila Acasă (Acasă):
Obtinem ca rezultat:
Pentru o frumusețe mai mare, puteți personaliza aspectul tabelelor inteligente rezultate din filă Constructor (Proiecta): setați un singur stil de culoare, dezactivați butoanele de filtrare, activați totalurile etc. În plus, puteți selecta un tabel cu date și puteți activa evidențierea numerelor folosind formatarea condiționată din filă Acasă — Formatare condiționată — Scale de culoare (Acasă — Formatare condiționată — Scale de culoare):
Și cea mai bună parte este că în viitor le puteți edita în siguranță pe cele vechi sau puteți adăuga proiecte noi la tabelul original și apoi actualizați tabelul potrivit cu date cu butonul din dreapta al mouse-ului - iar Power Query va repeta automat toate acțiunile pe care le-am făcut .
Voilà!
- Diagrama Gantt în Excel folosind formatarea condiționată
- Calendarul de reper al proiectului
- Generarea de rânduri duplicate cu Power Query