Scarica la cartella di lavoro di esempio
Questo tutorial dimostrerà come calcolare il "subtotale se", contando solo le righe visibili con criteri.
SUBTOTALE Funzione
La funzione SUBTOTALE può eseguire vari calcoli su un intervallo di dati (conteggio, somma, media, ecc.). Ancora più importante, può essere utilizzato per calcolare solo sulle righe visibili (filtrate). In questo esempio, utilizzeremo la funzione per contare (COUNTA) le righe visibili impostando l'argomento SUBTOTAL_num_funzione su 3 (un elenco completo delle possibili funzioni è disponibile qui).
=SOTTOTALE(3,$D$2:$D$14)
Nota come cambiano i risultati quando filtriamo manualmente le righe.
SUBTOTALE SE
Per creare un "subtotale se", utilizzeremo una combinazione di SUMPRODUCT, SUBTOTAL, OFFSET, ROW e MIN in una formula di matrice. Usando questa combinazione, possiamo essenzialmente creare una funzione generica "SUBTOTALE SE". Passiamo attraverso un esempio.
Abbiamo un elenco dei membri e il loro stato di partecipazione per ogni evento:
Supponiamo che ci venga chiesto di contare il numero di membri che hanno partecipato a un evento in modo dinamico mentre filtriamo manualmente l'elenco in questo modo:
Per fare ciò, possiamo usare questa formula:
=SOMMA.PRODOTTO((=)*(SOTTOTOTALE(3,OFFSET(,RIGA()-MIN(RIGA()),0))))
=SOMMA.PRODOTTO((D2:D14="Assistito")*(SOTTOTOTALE(3,OFFSET(D2,RIGA(D2:D14)-MIN(RIGA(D2:D14)),0)))))
Quando si utilizza Excel 2022 e versioni precedenti, è necessario inserire la formula dell'array premendo CTRL + MAIUSC + INVIO per dire a Excel che stai inserendo una formula di matrice. Saprai che la formula è stata inserita correttamente come formula di matrice quando vengono visualizzate parentesi graffe attorno alla formula (vedi immagine sopra).
Come funziona la formula?
La formula funziona moltiplicando due array all'interno di SUMPRODUCT, dove il primo array si occupa dei nostri criteri e il secondo array filtra solo le righe visibili:
=SOMMAPRODOTTO(*)
La matrice dei criteri
L'array dei criteri valuta ogni riga nel nostro intervallo di valori (Stato "Atteso" in questo esempio) e genera un array come questo:
=(=)
=(D2:D14="Ha partecipato")
Produzione:
{VERO; FALSO; FALSO; VERO; FALSO; TURA; TURA; TURA; FALSO; FALSO; VERO; FALSO; VERO}
Nota che l'output nel primo array nella nostra formula ignora se la riga è visibile o meno, ed è qui che entra in gioco il nostro secondo array.
La matrice della visibilità
Utilizzando SUBTOTALE per escludere le righe non visibili nel nostro intervallo, possiamo generare il nostro array di visibilità. Tuttavia, SUBTOTALE da solo restituirà un singolo valore, mentre SUMPRODUCT si aspetta una matrice di valori. Per ovviare a questo, usiamo OFFSET per passare una riga alla volta. Questa tecnica richiede l'alimentazione di OFFSET un array che contiene un numero alla volta. Il secondo array ha questo aspetto:
=SOTTOTOTALE(3,OFFSET(,ROW()-MIN(ROW()),0))
=SOTTOTALE(3,OFFSET(D2,RIGA(D2:D14)-MIN(RIGA(D2:D14)),0))
Produzione:
{1;1;0;0;1;1}
Cucire i due insieme:
=SOMMA.PRODOTTO({VERO; VERO; FALSO; FALSO; VERO; VERO} * {1; 1; 0; 0; 1; 1})
= 4
SUBTOTALE SE con criteri multipli
Per aggiungere più criteri, semplicemente più criteri insieme all'interno del SUMPRODUCT in questo modo:
=SOMMAPRODOTTO((=)*(=)*(SOTTOTOTALE(3,OFFSET(,RIGA()-MIN(RIGA()),0))))
=SOMMA.PRODOTTO((E2:E14="Assistito")*(B2:B14=2019)*(SOTTOTALE(3,OFFSET(E2,RIGA(E2:E14)-MIN(RIGA(E2:E14)),0)) ))
SUBTOTALE SE in Fogli Google
La funzione SUBTOTALE SE funziona esattamente allo stesso modo in Fogli Google come in Excel:
