In precedenza abbiamo imparato a contare i valori univoci in un intervallo. Abbiamo anche imparato come estrarre valori univoci da un intervallo. In questo articolo impareremo come contare il valore univoco nell'intervallo con la condizione in Excel.
Formula generica
{=SUM(--(FREQUENCY(IF(condition,MATCH(range,range,0)),ROW(range)-ROW(firstCell in range)+1)>0))}
È una formula di matrice, usa CTRL+MAIUSC+INVIO
Condizione : i criteri in base ai quali si desidera ottenere valori univoci.
Gamma : intervallo in cui si vogliono ottenere valori univoci.
prima cella nel raggio d'azione: È il riferimento della prima cella in gamma. Se l'intervallo è A2:A10, allora è A2.
Esempio:
Qui ho questi dati dei nomi. Le classi corrispondenti sono menzionate nella colonna adiacente. Dobbiamo contare i nomi univoci in ogni classe.
Usando la formula generica sopra, scrivi questa formula in E2
{=SUM(--(FREQUENCY(IF(B2:B19="Class 1",MATCH(A2:A19,A2:A19,0)),ROW(A2:A19)-ROW(A2)+1)>0 ))}
La formula sopra restituisce il valore univoco nell'intervallo excel A2: A19 a condizione di B2: B19 = "Classe 1".
Per ottenere valori univoci in classi diverse, modificare i criteri. L'abbiamo codificato qui, ma puoi anche fornire un riferimento di cella. Usa intervalli denominati o riferimenti assoluti per gli intervalli, se non vuoi che anche loro cambino.
Come funziona?
Scomponiamolo dall'interno.
SE(B2: B19 = "Classe 1",INCONTRO(A2:A19,A2:A19,0))
B2: B19 = "Classe 1": Questa parte restituirà un array di true e false. VERO per ogni partita.
{VERO;FALSO;VERO;FALSO;VERO;VERO;FALSO… .}
INCONTRO(A2:A19,A2:A19,0): questa parte restituirà la prima posizione di ciascun valore nell'intervallo A2: A19 come da proprietà di MATCH.
{1;2;1;4;5;4;1;8;9;1;2;1;4;5;4;1;8;9}.
Ora per ogni valore VERO otterremo la posizione e per falso otterremo FALSO. Quindi per l'intera istruzione IF otterremo
{1;FALSO;1;FALSO;5;4;FALSO;FALSO;FALSO;FALSO;2;FALSO;FALSO;5;FALSO;1;8;FALSO}.
Successivamente passiamo alla parte di frequenza.
FREQUENZA(SE(B2: B19 = "Classe 1",INCONTRO(A2:A19,A2:A19,0)),RIGA(A2:A19)-RIGA(A2)+1)
RIGA(A2:A19): Questo restituisce il numero di riga di ogni cella nell'intervallo A2:A19.
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
ROW(A2:A19)-ROW(A2): Ora sottraiamo il numero della prima riga da ogni numero di riga. Questo restituisce un array di numeri di serie a partire da 0.
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
Poiché vogliamo avere un numero di serie a partire da 1, aggiungiamo 1 ad esso.
RIGA(A2:A19)-RIGA(A2)+1. Questo ci dà una serie di numeri di serie a partire da 1.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}
Questo ci aiuterà a ottenere un conteggio unico a condizione.
Ora abbiamo: FREQUENZA({1;FALSO;1;FALSO;5;4;FALSO;FALSO;FALSO;FALSO;2;FALSO;FALSO;5;FALSO;1;8;FALSO},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})
Questo restituisce la frequenza di ogni numero in un dato array.{3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0}
Qui ogni numero positivo indica l'occorrenza di un valore univoco quando i criteri sono soddisfatti. Dobbiamo contare i valori maggiori di 0 in questo array. Per questo lo controlliamo per >0. Questo restituirà VERO e FALSO. Convertiamo vero falso usando -- (operatore binario doppio).
SOMMA(--({3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0})>0) questo si traduce in SOMMA({1;1;0;1;1;0;0;1;0;0;0;0;0;0;0;0;0;0;0})
E infine otteniamo il conteggio univoco dei nomi nell'intervallo sui criteri come 5.
So che è un po' complesso da capire, ma lo controlli dall'opzione di valutazione della formula.
Per contare valori univoci con più criteri possiamo usare la logica booleana:
Conta il valore univoco con più criteri con e logica
{=SUM(--(FREQUENCY(IF(condition1 * Condition2,MATCH(range,range,0)),ROW(range)-ROW(firstCell in range)+1)>0))}
La formula generica sopra può contare valori univoci su più condizioni e quando tutte sono vere.
Conta il valore univoco con più criteri con o logica
{=SUM(--(FREQUENCY(IF(condition1 + Condition2,MATCH(range,range,0)),ROW(range)-ROW(firstCell in range)+1)>0))}
Questa formula generica può essere utilizzata per contare valori univoci con la logica Or. Significa che conterà se una qualsiasi delle condizioni è vera.
Quindi sì ragazzi, questo è il modo in cui contate i valori univoci in un intervallo su più condizioni. Questo è un po' complesso ma è veloce. Una volta che inizi a usarlo, capirai come funziona.
Se hai dei dubbi su questo articolo sulla formula Excel, fammelo sapere nella sezione commenti qui sotto.
Download file:
Come contare i valori univoci in Excel con criteriFormula di Excel per estrarre valori univoci da un elenco
Conta valori univoci in Excel
Articoli popolari:
La funzione CERCA.VERT in Excel
CONTA.SE in Excel 2016
Come utilizzare la funzione SOMMA.SE in Excel
