Diciamo che stai eseguendo diversi progetti con budget diversi e desideri visualizzare i tuoi costi per ciascuno di essi. Cioè, da questa tabella di origine:
.. ottieni qualcosa del genere:
In altre parole, è necessario distribuire il budget sui giorni di ogni progetto e ottenere una versione semplificata del diagramma di Gantt del progetto. Farlo con le mani è lungo e noioso, le macro sono difficili, ma Power Query per Excel in una situazione del genere mostra la sua potenza in tutto il suo splendore.
Power Query è un componente aggiuntivo di Microsoft che può importare dati in Excel da quasi tutte le fonti e quindi trasformarli in molti modi diversi. In Excel 2016, questo componente aggiuntivo è già integrato per impostazione predefinita e per Excel 2010-2013 può essere scaricato dal sito Web Microsoft e quindi installato sul PC.
Per prima cosa, trasformiamo la nostra tabella originale in una tabella "intelligente" scegliendo il comando Formatta come una tabella linguetta Casa (Home — Formatta come tabella) o premendo la scorciatoia da tastiera Ctrl+T :
Quindi vai alla scheda Dati (se hai Excel 2016) o nella scheda Power Query (se hai Excel 2010-2013 e hai installato Power Query come componente aggiuntivo separato) e fai clic sul pulsante Da tabella/intervallo. :
La nostra tabella intelligente viene caricata nell'editor di query di Power Query, dove il primo passaggio consiste nell'impostare i formati numerici per ciascuna colonna utilizzando i menu a discesa nell'intestazione della tabella:
Per calcolare il budget giornaliero, devi calcolare la durata di ogni progetto. Per fare ciò, selezionare (tenere premuto il tasto Ctrl) prima la colonna Fine, e poi Inizio e scegli una squadra Aggiungi colonna – Data – Sottrai giorni (Aggiungi colonna — Data — Sottrai giorni):
I numeri risultanti sono 1 in meno del necessario, perché dovremmo iniziare ogni progetto il primo giorno al mattino e finire l'ultimo giorno la sera. Pertanto, seleziona la colonna risultante e aggiungi un'unità ad essa utilizzando il comando Trasforma – Standard – Aggiungi (Trasforma — Standard — Aggiungi):
Ora aggiungiamo una colonna in cui calcoliamo il budget giornaliero. Per fare ciò, nella scheda Aggiungi colonna non gioco Colonna personalizzata (colonna personalizzata) e nella finestra che compare, inserisci il nome del nuovo campo e la formula di calcolo, utilizzando i nomi delle colonne della lista:
Ora il momento più sottile: creiamo un'altra colonna calcolata con un elenco di date dall'inizio alla fine con un passaggio di 1 giorno. Per fare ciò, premere nuovamente il pulsante Colonna personalizzata (colonna personalizzata) e usa il linguaggio di Power Query integrato M, che viene chiamato Elenco.Date:
Questa funzione ha tre argomenti:
- data di inizio – nel nostro caso, è presa dalla colonna Inizio
- il numero di date da generare – nel nostro caso, questo è il numero di giorni per ogni progetto, che abbiamo contato prima nella colonna Sottrazione
- passo temporale: impostato in base alla progettazione #durata(1,0,0,0), che significa nel linguaggio di M – un giorno, zero ore, zero minuti, zero secondi.
Dopo aver fatto clic su OK otteniamo un elenco (Elenco) di date, che può essere espanso in nuove righe utilizzando il pulsante nell'intestazione della tabella:
… e otteniamo:
Ora non resta che comprimere la tabella, utilizzando le date generate come nomi per le nuove colonne. La squadra è responsabile di questo. Colonna dei dettagli (colonna pivot) linguetta convertire (Trasformare):
Dopo aver fatto clic su OK otteniamo un risultato molto vicino a quello desiderato:
Null è, in questo caso, un analogo di una cella vuota in Excel.
Resta da rimuovere le colonne non necessarie e scaricare la tabella risultante accanto ai dati originali con il comando Chiudi e carica – Chiudi e carica in... (Chiudi e carica — Chiudi e carica in...) linguetta Casa (Casa):
Otteniamo come risultato:
Per una maggiore bellezza, puoi personalizzare l'aspetto delle tabelle intelligenti risultanti nella scheda Costruttore (Design): imposta uno stile di colore singolo, disabilita i pulsanti dei filtri, abilita i totali, ecc. Inoltre, puoi selezionare una tabella con le date e abilitare l'evidenziazione dei numeri per essa utilizzando la formattazione condizionale nella scheda Home — Formattazione condizionale — Scale di colore (Home — Formattazione condizionale — Scale di colore):
E la parte migliore è che in futuro puoi modificare in sicurezza quelli vecchi o aggiungere nuovi progetti alla tabella originale, quindi aggiornare la tabella giusta con le date con il pulsante destro del mouse e Power Query ripeterà tutte le azioni che abbiamo eseguito automaticamente .
Voilà!
- Diagramma di Gantt in Excel utilizzando la formattazione condizionale
- Calendario delle tappe fondamentali del progetto
- Generazione di righe duplicate con Power Query