La combinazione di potenza
Combinando le formule SUMIF e OFFSET, un elenco di convalida e una casella combinata per restituire un riepilogo dei dati da selezionare per mese: una vera combinazione di potenza!
Passaggio 1: selezione di elementi dagli elenchi di convalida
Selezione di un elemento da un elenco di convalida (colonna A nello screenshot)
consente alle formule inserite nelle celle nelle colonne C e D di identificare il testo e restituire i risultati di riepilogo da una colonna che contiene i criteri (l’elemento scelto) per il mese selezionato nella Casella combinata.
Per aggiungere un elenco di convalida a un intervallo di celle:
-
Seleziona l’intervallo di celle (nello screenshot della pagina precedente, le celle selezionate sono A12: A15)
-
Selezionare le celle A19: A23.
-
Dal menu Dati, selezionare Convalida.
-
Nella finestra di dialogo Convalida dati, selezionare la scheda Impostazioni e selezionare Elenco dalla casella Consenti.
-
Nella casella Origine, premere F3, selezionare il Nome definito per l’elenco (Livello3 in questo esempio, vedere lo screenshot nell’ultima pagina per questo suggerimento) e fare clic su OK.
Passaggio 2: immissione di formule che restituiscono i saldi di riepilogo per gli articoli scelti La formula nelle celle C12: C15 è:
SUMIF (DataLevel3, A12, OFFSET (DataLevel3,0, MonthSelectionNumber + 2))
La formula nelle celle D12: D15 è:
SUMIF (DataLevel3, A12, OFFSET (DataLevel3,0, MonthSelectionNumber + 2-12))
Spiegazione:
La formula SUMIF nella colonna C riassume gli importi del saldo dalla colonna del dicembre 2003; la formula SUMIF nella colonna D riepiloga gli importi del saldo dalla colonna del dicembre 2002.
La formula SOMMA.SE ha tre argomenti:
Primo argomento:
L’intervallo da valutare in base ai criteri immessi nel secondo argomento della formula SOMMA.SE. In questo esempio, l’intervallo è DataLevel3, che è il nome definito per la colonna C nel database Balance Sheet. Contiene elementi di livello 3 dello stato patrimoniale, come cassa, contabilità clienti, inventari e così via.
Secondo argomento:
Il criterio è l’elemento scelto dall’elenco di convalida Livello3.
Terzo argomento:
La colonna da cui verranno riepilogati i dati. Questo sarà scelto in base alla formula OFFSET per la colonna del mese, che è regolata dal numero selezionato da MonthList nella casella combinata. La formula OFFSET consente di deviare il mese selezionato dalla colonna di base (colonna C nella schermata sottostante).
Come funziona la formula OFFSET
La colonna 29 è il numero di colonna per dicembre 2003 e il numero di colonna per dicembre 2002 è 17, che è 12 colonne prima (vedi lo screenshot sotto).
Come modificare i titoli delle intestazioni in un foglio da caratteri a numeri: {vuoto} 1. Dal menu Strumenti, seleziona Opzioni.
-
Nella scheda Generale e controllare lo stile di riferimento R1C1.
Quando si seleziona Dicembre 2003 dall’elenco a discesa Casella combinata (Elenco mesi), il numero del mese in tale elenco è 24 (questo viene calcolato determinando il numero di mesi tra gennaio 2002 e dicembre 2003:
2 anni * 12 mesi = 24). La cella collegata alla casella combinata riceve il valore di 24.
Nella scheda tecnica, la colonna 3 è la colonna di base che la formula SOMMA.SE valuta per i criteri nel secondo argomento della formula SOMMA.SE.
In questo caso, dicembre 2003 è distanziato dalla colonna C di 24+ 2 = 26 (2 = Colonna D & Colonna E).
Nel terzo argomento, Sum_range dovrebbe essere di 26 colonne distanti dalla colonna di base. La formula OFFSET restituisce il risultato di 26 e fa sì che la formula SOMMA.SE riepiloghi le cifre della colonna di dicembre 2003.