Come utilizzare subtotale con if in excel?

QUESTA FORMULA È PURA MAGIA.

Se vuoi scrivere una formula SUBTOTALE in Excel con SE (condizione), devi utilizzare diverse funzioni per farlo. Ma prima di farlo, comprendiamo i dati che abbiamo per questo esempio.

Nell’esempio sopra, hai tre colonne:

• Nome
• Lastra d’età
• Genere

E quando filtri una lastra dalla colonna Età Lastra, viene visualizzato il numero di donne nella cella F1. Ciò significa quindi che abbiamo una formula che visualizza il numero di valori filtrati ma con una condizione.

La formula che abbiamo:

 =SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))))

Comprendere la formula SUBTOTALE SE

Questa formula utilizza cinque funzioni: SUMPRODUCT, SUBTOTALE, OFFSET, RIGA e MAX. Pertanto, per comprendere questa formula, dobbiamo dividerla in più parti.

1.LINEA(C2:C41)-2.0)

Questa parte della formula utilizza le funzioni MIN e ROW.

• Nella riga, abbiamo fatto riferimento alle colonne “Sesso” e restituisce una matrice di numeri di riga.
• Successivamente, MIN prende l’array o i numeri di riga e restituisce il numero di riga minimo. Ecco perché ne abbiamo 2 in questa parte della formula.

2.LINEA(C2:C41)

In questa parte abbiamo solo la funzione ROW, che restituisce un array di numeri di riga.

3. SPOSTAMENTO(C2,LINEA(C2:C41)-MIN(LINEA(C2:C41)),0)

Ora abbiamo la funzione OFFSET. Ti aiuta a creare un riferimento a un intervallo utilizzando un riferimento di cella come punto di partenza. Nell’argomento di riferimento abbiamo fatto riferimento alla cella C2, la prima cella da cui inizia il nostro intervallo di genere.

Nell’argomento righe abbiamo questa parte della formula discussa sopra nelle prime due parti. Successivamente, nell’argomento cols, abbiamo utilizzato 0. Con tutto ciò, OFFSET restituisce un array di tutti i valori nella colonna “Sesso”.

4. SUBTOTALE(3,SPOSTAMENTO(C2,LINEA(C2:C41)-MIN(LINEA(C2:C41)),0))

Abbiamo utilizzato l’array restituito da OFFSET nel SUBTOTALE. E in function_num abbiamo usato 3, che dice a SUBTOTALE di usare la funzione COUNTA per il calcolo.

Quando utilizzi un filtro sulla colonna “Age Slab”, questa parte SUBTOTALE della formula restituisce una matrice contenente 0 e 1.

In questa tabella abbiamo 1 per valori equivalenti al valore per il quale abbiamo applicato il filtro. Vedi l’esempio qui sotto:

5. (C2:C41=E1)

Questa parte della formula restituisce un array testando una condizione. In questa condizione, testiamo se il valore dell’intervallo è “Femmina” e restituisce TRUE e FALSE nell’array.

In questa tabella abbiamo TRUE per il valore “Femmina” e FALSE per gli altri.

7. Ultima parte

Alla fine, abbiamo due tabelle in SUMPRODUCT. E abbiamo anche un operatore asterisco tra questi array.

Quando moltiplichiamo le due tabelle tra loro, abbiamo un’unica tabella con 0 e 1. In questa tabella, uno (1) è per il valore “Female” in genere e 21-30 per “Age Slab”.

Alla fine, SUMPRODUCT restituisce la somma utilizzando questo array. E questa somma è il numero di celle con il valore “Femmina” nella colonna del sesso quando filtri per 21-30 nella colonna “Intervallo di età”.