Excel - Somma più se - Utilizzo delle tabelle e della notazione strutturata

Nel tutorial "Excel: La Tavola Pitagorica - Utilizzo delle tabelle e della notazione strutturata" abbiamo visto un esempio di notazione strutturata per l'oggetto tabella e la notazione strutturata.

Approfondiamo l'argomento con la funzione =SOMMA:PIÙ.SE()

Obiettivo

Dato un elenco di prodotti con mese e importi, vogliamo calcolare l'importo totale per ogni mese e per ogni prodotto

La tabella di partenza prodotti_tabella e allegata in fondo al tutorial.

Cosa impariamo

L'utilizzo della notazione strutturata delle tabelle

Impostazioni

Apriamo il file prodotti_tabella.

Nel file è presente l'oggetto tabella t_prodotti.

Inseriamo una nuova pagina Report

Nella cella A1 scriviamo l'intestazione Prodotto

Per riportare l'elenco univoco dei prodotti, possiamo inserire nella cella A2 la funzione

=UNICI(t_prodotti[Prodotto])

in questo modo possiamo riportare l'elenco univoco dei prodotti presenti nella tabella t_prodotti colonna Prodotto

Se vogliamo che l'elenco sia ordinato possiamo inserire prima di questa funzione anche DATI.ORDINA(). La funzione diventa

=DATI.ORDINA(UNICI(t_prodotti[Prodotto]))

Il risultato è il seguente

Per creare un oggetto tabella abbiamo bisogno di eliminare le formule. Selezioniamo la colonna A, la copiamo e facciamo Incolla Speciale ( CTRL+ALT+v)

Ora siamo pronti per creare la tabella.

Andiamo nella cella A1 e dal menù Inserisci facciamo click sul pulsante Tabella

Assicuriamoci che sia attivo "tabella con intestazioni"

Dal menù Struttura tabella diamo il nome a questo oggetto t_report

Inseriamo il numero 1 nella cella B1. Appena confermiamo con invio viene aggiunta una nuova colonna nella tabella t_report

Per ottenere i 12 mesi basta semplicemente trascinare il quadratino nero della cella B1 creando una serie

Il risultato è il seguente

Per calcolare la somma di tutti i totali per un particolare prodotto e per un particolare mese abbiamo bisogno della funzione SOMMA.PIÙ.SE()

Il primo argomento è la colonna che contiene gli importi quindi t_prodotti[Importo]

Il secondo argomento è la colonna che contiene il prodotto da cercare: t_prodotti[Prodotto]

Il terzo argomento è il prodotto specifico che vogliamo cercare quindi facciamo click sulla cella A2 e otteniamo [@Prodotto]

Il quarto argomento è la colonna che contiene i mesi, quindi t_prodotti[Mese]

Il quinto argomento è il mese da cercare quindi t_report[[#Intestazioni];[1]]

Se diamo OK sembra funzionare

Ma se selezioniamo le celle da B2 a B11 e le trasciniamo a destra otteniamo

Cerchiamo di capire cosa non funziona confrontando la formula in B2

=SOMMA.PIÙ.SE(t_prodotti[Importo];t_prodotti[Prodotto];[@Prodotto];t_prodotti[Mese];t_report[[#Intestazioni];[1]])

con quella ad esempio in E6

=SOMMA.PIÙ.SE(t_prodotti[Importo];t_prodotti[Prodotto];[@3];t_prodotti[Mese];t_report[[#Intestazioni];[4]])

Per sistemare la formula abbiamo bisogno di bloccare le colonne della tabella t_prodotti

• Importo
• Prodotto
• Mese

Per fare questo occorre replicare il nome della colonna. Ad esempio per Importo occorre scrivere

t_prodotti[[Importo]:[Importo]]

Mentre per la tabella t_report occorre bloccare la colonna Prodotto

In questo caso la sintassi è

t_report[@[Prodotto]:[Prodotto]]

La formula corretta da inserire nella cella B2 diventa

=SOMMA.PIÙ.SE(t_prodotti[[Importo]:[Importo]];t_prodotti[[Prodotto]:[Prodotto]];t_report[@[Prodotto]:[Prodotto]];t_prodotti[[Mese]:[Mese]];t_report[[#Intestazioni];[1]])

Trascinando la formula su tutta la tabella otteniamo:

Se ci spostiamo su un altre cella ad esempio K5, notiamo che la formula è corretta e l'unica cosa che cambia è t_report[[#Intestazioni];[10]] che consente di ricavare il valore 10

Per completare attiviamo la riga dei totali nel menù Struttura tabella e trasciniamo la formula che viene inserita nella colonna più a destra (M12) a sinistra.

in allegato l'esercizio risolto.

Approfondimenti

sul sito Microsoft nella pagina Uso di riferimenti strutturati con le tabelle di Excel

si possono trovare le informazioni sulla sintassi dei riferimenti strutturati

Cosa ho imparato

Ho imparato l'utilizzo delle tabelle e della notazione strutturata

Come bloccare una colonna e una riga con la notazione strutturata