Formula generica
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Sommario
Per contare valori numerici univoci in un intervallo, è possibile utilizzare una formula basata sulle funzioni FREQUENZA, SOMMA e SE. Nell'esempio mostrato, i numeri dei dipendenti vengono visualizzati nell'intervallo B5: B14. La formula in G6 è:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
che restituisce 2, poiché ci sono 2 ID dipendenti univoci nell'edificio A.
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO, a meno che non si utilizzi Excel 365.
Spiegazione
Nota: prima di Excel 365, Excel non disponeva di una funzione dedicata per contare valori univoci. Questa formula mostra un modo per contare i valori univoci, purché siano numerici. Se hai valori di testo o una combinazione di testo e numeri, dovrai utilizzare una formula più complicata.
La funzione FREQUENZA di Excel restituisce una distribuzione di frequenza, che è una tabella riassuntiva che contiene la frequenza dei valori numerici, organizzata in "bin". Lo usiamo qui come un modo indiretto per contare valori numerici univoci. Per applicare i criteri, usiamo la funzione IF.
Lavorando dall'interno verso l'esterno, per prima cosa filtriamo i valori con la funzione IF:
IF(C5:C14="A",B5:B14) // filter on building A
Il risultato di questa operazione è un array come questo:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Notare che tutti gli ID nell'edificio B ora sono FALSE. Questo array viene fornito direttamente alla funzione FREQUENCY come data_array . Per bins_array , forniamo gli stessi ID:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Con questa configurazione, FREQUENZA restituisce la matrice seguente:
(4;0;0;0;2;0;0;0;0;0;0)
Il risultato è un po 'criptico, ma il significato è 905 appare quattro volte e 773 appare due volte. I valori FALSE vengono automaticamente ignorati.
FREQUENZA ha una caratteristica speciale che restituisce automaticamente zero per tutti i numeri che sono già apparsi nella matrice di dati, motivo per cui i valori sono zero una volta che un numero è stato rilevato. Questa è la caratteristica che consente a questo approccio di funzionare.
Successivamente, ciascuno di questi valori viene verificato per essere maggiore di zero:
(4;0;0;0;2;0;0;0;0;0;0)>0
Il risultato è un array come questo:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Ogni TRUE nell'elenco rappresenta un numero univoco nell'elenco e dobbiamo solo sommare i valori TRUE con SUM. Tuttavia, SUM non aggiungerà i valori logici in un array, quindi dobbiamo prima forzare i valori in 1 o zero. Questo viene fatto con il doppio negativo (-). Il risultato è un array di solo 1 o 0:
(1;0;0;0;1;0;0;0;0;0;0)
Infine, SUM somma questi valori e restituisce il totale, che in questo caso è 2.
Criteri multipli
Puoi estendere la formula per gestire più criteri come questo:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
