Formula generica
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Sommario
Per contare valori di testo univoci in un intervallo con criteri, è possibile utilizzare una formula di matrice basata sulle funzioni FREQUENZA e CONFRONTA. Nell'esempio mostrato, la formula in G6 è:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
che restituisce 3, dal momento che tre persone diverse hanno lavorato al progetto Omega.
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
Spiegazione
Questa è una formula complessa che utilizza FREQUENZA per contare i valori numerici derivati con la funzione CONFRONTA. Lavorando dall'interno verso l'esterno, la funzione CONFRONTA viene utilizzata per ottenere la posizione di ogni valore che appare nei dati:
MATCH(B5:B11,B5:B11,0)
Il risultato di MATCH è un array come questo:
(1;1;3;1;1;6;7)
Poiché CONFRONTA restituisce sempre la posizione della prima corrispondenza, i valori che compaiono più di una volta nei dati restituiscono la stessa posizione. Ad esempio, poiché "Jim" compare 4 volte nell'elenco, viene visualizzato in questo array 4 volte come numero 1.
Al di fuori della funzione MATCH, la funzione IF viene utilizzata per applicare i criteri, che in questo caso comporta il test se il progetto è "omega" (dalla cella G5):
IF(C5:C11=G5 // filter on "omega"
La funzione IF agisce come un filtro, consentendo il passaggio dei valori di MATCH solo se associati a "omega". Il risultato è un array come questo:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
L'array filtrato viene consegnato direttamente alla funzione FREQUENCY come argomento data_array . Successivamente, la funzione RIGA viene utilizzata per creare un elenco sequenziale di numeri per ogni valore nei dati:
ROW(B3:B12)-ROW(B3)+1
Questo crea un array come questo:
(1;2;3;4;5;6;7;8;9;10)
che diventa l' argomento bins_array in FILTER. A questo punto abbiamo:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENZA restituisce una matrice di numeri che indicano un conteggio per ogni valore nella matrice di dati, organizzata per bin. Quando un numero è già stato contato, FREQUENZA tornerà a zero. Il risultato di FREQUENCY è un array come questo:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Nota: FREQUENZA restituisce sempre un array con un elemento in più rispetto a bins_array .
A questo punto, possiamo riscrivere la formula in questo modo:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Controlliamo valori maggiori di zero, che converte i numeri in VERO o FALSO:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Quindi usiamo un doppio negativo per forzare i valori logici a 1 e 0:
=SUM((1;0;0;0;0;1;1;0))
Infine, la funzione SOMMA restituisce 3 come risultato finale.
Nota: questa è una formula di matrice e deve essere inserita utilizzando Control + Maiusc + Invio.
Gestire le celle vuote nell'intervallo
Se alcune celle nell'intervallo sono vuote, sarà necessario regolare la formula per evitare che le celle vuote vengano passate nella funzione CONFRONTA, il che genererà un errore. Puoi farlo aggiungendo un'altra funzione SE nidificata per verificare la presenza di celle vuote:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Con due criteri
Se hai due criteri, puoi estendere la logica della formula aggiungendo un altro IF nidificato:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Dove c1 = criteri1 , c2 = criteri2 e vals = l'intervallo di valori.
Con logica booleana
Con la logica booleana, puoi ridurre IF annidati:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Ciò semplifica l'aggiunta e la gestione di criteri aggiuntivi.
