Embedded Files
Impareremo ad usare questa formula basandoci su un caso reale ed un esercizio complesso che divideremo in tutte le sue parti.
Mettiamo il caso di avere un foglio con riportate le ore che i vari operatori hanno svolto su un determinato progetto che per sfortuna nostra è anche suddiviso in azioni. L'amministrazioni ci gira mese per mese un file con delle righe che noi riportiamo in un foglio di Google Fogli e ogni mese aggiungiamo righe nuove che ci vengno inviate. Questo è il primo passo creo il mio database su cui fare i calcoli, nell'esempio qui sotto lo troviamo nel foglio ORE.
Ovviamente ogni colonna ha un dato che noi lasciamo così come è:
ID Formattato come Testo
operatore Formattato come Testo
data Formattato come data
dalle Formattato come orario
alle Formattato come orario
Assenze Formattato come Testo
Comune/ente Formattato come Testo
Area Formattato come Testo
tot_ore Formattato come numero
Note Formattato come Testo
km Formattato come numero
mese Formattato come numero Formula =SE(C2<>"";MESE(C2);"") verifica che ci sia una data nella cella C2 e se c'è riporta il numero del mese
SETTIMANA Formattato come numero =NUM.SETTIMANA(C2;2) Niente verifica, riporta il numero della settimana dell'anno o 0 se la cella non ha data
IN ROSSO LE COLONNE CHE USEREMO PER QUESTO LAVORO
Nel file sono attivi i filtri in modo da permetterci di andare a cercare informazioni specifiche e potremmo aggiungere nelle celle N1 la formula del subtotale in modo da vedere un totale ore corrispondente ai dati filtrati in quel momento =subtotale(9;I:I)
FOGLIO DATI
FOGLIO DATI
Per prima cosa impostiamo il foglio dati.
Qui inserisco le informazioni che devo verificare. Ovviamente lo faccio ad inizio progetto, ma posso sempre modificarle nel tempo.
Inserisco:
Anno
Mesi di durata del progetto
% questo valore mi servirà per calcolare dopo quale soglia segnalare un allert. Ossia se ho cento ore da fare se indico 80% farà in modo che il monte ore fatto si colori di giallo se supera l'80%
Creo poi la tabella che mi dice tutte le combinazione da verificare, ossia nel progetto 1 lavora l'operatore 1 e ha un Monte Ore Anno di ... e questo per ogni combinazione.
Le Colonne MonteOreMese e MonteOreSettimana sono calcolate.
MonteOreMese =MonteOreAnno / mesi di durata nell'anno =D5/D$2
MonteOreSettimana = MonteOreMese / 4,3 =E5/4,3
IN SOSTANZA COMPILO LE CELLE IN AZZURRO
Foglio Tabella pivot 1
Foglio Tabella pivot 1
Come si crea la tabella pivot lo sappiamo ma nel caso trovi qui tutte le indicazioni.
In questo caso abbiamo scelto i seguenti campi:
nelle righe i campi Comune/ente, Area, e operatore
nelle colonne nulla (ma potremmo metterci i mesi se ci servisse ad esempio tale suddivisione)
nei valori abbiamo messi il tot_ ore con la funzione somma (SUM) che viene scelta in automatico
Foglio Verifica_anno
Foglio Verifica_anno
Per avere una tabella che ci permetta di verificare, cominciamo a riportare qui i dati dal foglio delle impostazioni iniziali, ossia il foglio DATI.
Potremmo fare copia incolla, ma questo ci obbligherebbe a rifare copia incolla ogni volta che facciamo una modifica nel foglio dati quindi facciamo in modo che la nostra tabelle sia dinamica,
quindi:
siccome voglio che nella mia tabella di VERIFICA_ANNO nella cella A1 ci sia il contenuto della cella A4 del foglio dati scriverò la seguente formula =DATI!A4
e poi la trascinerò in orizzontale e verticale per sfruttare la compilazione automatica e la tabella è fatta
adesso rimane da compilare la colonna ore fatte e Saldo. Per la colonna ore fatte serve la funzione SOMMA.PIÙ.SE.
Quindi partiamo dal saldo :-)
=E2-D2 è la formula del saldo
quindi avremo:
un valore negativo se abbiamo ancora ore da fare rispetto al MonteOreAnno che è il massimo previsto
un valore positivo se abbiamo sforato rispetto al MonteOreAnno e quindi abbiamo fatto troppe ore
SOMMA.PIÙ.SE
SOMMA.PIÙ.SE
La funzione SOMMA.PIÙ.SE (SUMIFS in inglese) in un foglio di calcolo, come google fogli, serve per sommare i valori di una colonna che soddisfano più condizioni contemporaneamente. In altre parole, permette di effettuare una somma condizionata, ma con la possibilità di specificare più di un criterio per determinare quali celle includere nella somma.
Quindi, se vogliamo sapere quanter ore ha fatto l'operatore1, nell'azione 1, del progetto 1 questa è la funzione da usare.
Sintassi
Sintassi
SOMMA.PIÙ.SE(intervallo_somma; intervallo_criteri1; criterio1; [intervallo_criteri2; criterio2; ...])
intervallo_somma - L'intervallo da sommare.
intervallo_criteri1 - L'intervallo da verificare rispetto a criterio1.
criterio1 - Il pattern o la verifica da applicare a intervallo_criteri1.
intervallo_criteri2, criterio2, ... - [ FACOLTATIVO ] - Gli intervalli e criteri aggiuntivi da verificare.
nel nostro caso
ORE!I:I; identifica la colonna tot_ore del foglio ORE ossia le ore da sommare
ORE!G:G; ossia la colonna Comune/ente del foglio ORE, lo spazio dove cercare il primo criterio
A16; ossia il primo criterio da cercare (ovviamente nella colonna G:G) in questo caso Progetto 1
ORE!H:H; ossia la colonna Area del foglio ORE, lo spazio dove cercare il secondo criterio
B16; ossia il secondo criterio da cercare (ovviamente nella colonna G:G) in questo caso Azione 1
ORE!B:B; ossia la colonna Operatore del foglio ORE, lo spazio dove cercare il terzo criterio
C16 ossia il terzo criterio da cercare (ovviamente nella colonna G:G) in questo caso Operatore 1
Foglio Verifica_MESE
Foglio Verifica_MESE
La struttura è la stessa del foglio Verifica_Anno ma la colonna ore fatte e saldo vengono ripetute 12 volte una per mese
Nello specifico quindi la colonna 1 si rifà a gennaio, 2 a febbraio e così via, la colonna a fianco che non ha intestazione è la colonna saldo del mese (F:F per gennaio e così via)
La formula è stessa del foglio Verifica_Anno sono cambiate solo due cose:
E' stato aggiunto un quarto criterio: il mese. Si chiede quindi oltre ai precedenti criteri di cercare nella colonna L:L del foglio ore la corrispondenza con la cella E1 ossia in questo caso il numero 1 ossia il mese di gennaio
E' stato aggiunto il simbolo del dollaro ($) che trasforma i riferimenti relativi in assoluti. Se non sai di cosa parlo leggi qui.
Formattazione dei monte ore
Formattazione dei monte ore
Decidiamo di colorare di rosso i valori positivi perchè sono un problema,
di verde i colori negativi perchè abbiamo il via per lavorare senza vincoli particolari
di giallo quei monte ore che hanno superato la percentuale espressa in F2 del foglio dati perchè ci segnalerà che siamo vicini all'esaurire le ore.
Per farlo useremo la Formattazione Condizionale spiegata qui, poichè la formattazione condizionale non può pescare valori da fogli diversi da quello in cui opera è necessario richiamare la percentuale espressa nella cella F2 del foglio dati nel foglio in cui ci serve utilizzando la formula =DATI!F2 in modo che ogni modifica nel foglio dati generi un automatico cambiamento dei colori nei fogli dove ho impostato la formattazione condizionale.
Nel caso del nostro file le colonne con il monte ore fatto hanno la seguente formattazione: (i riferimenti sono quelli del foglio Verifica_anno e sono impostati alle celle E2:E24).
Il verde alla fine non l'ho messo mi sembrava pasticciasse troppo il foglio così sono più chiari gli Alert.
Page updated
Report abuse