Anteriormente, aprendimos a contar valores únicos en un rango. También aprendimos cómo extraer valores únicos de un rango. En este artículo, aprenderemos cómo contar valores únicos en el rango con condición en Excel.
Fórmula genérica
{= SUM (- (FREQUENCY (IF (condition, MATCH (range, range, 0)), ROW (range) -ROW (firstCell in range) +1)> 0))}
Es una fórmula de matriz, use CTRL + MAYÚS + ENTRAR
Condición : Los criterios sobre los que desea obtener valores únicos.
Distancia : rango en el que desea obtener valores únicos.
firstCell en rango: Es la referencia de la primera celda en distancia. Si el rango es A2: A10, entonces es A2.
Ejemplo:
Aquí tengo estos datos de nombres. Las clases correspondientes se mencionan en la columna adyacente. Necesitamos contar nombres únicos en cada clase.
Usando la fórmula genérica anterior, escriba esta fórmula en E2
{= SUMA (- (FRECUENCIA (SI (B2: B19 = "Clase 1", COINCIDIR (A2: A19, A2: A19,0)), FILA (A2: A19) -ROW (A2) +1)> 0 ))}
La fórmula anterior devuelve el valor único en el rango de Excel A2: A19 con la condición de B2: B19 = "Clase 1".
Para obtener valores únicos en diferentes clases, cambie los criterios. Lo hemos codificado aquí, pero también puede dar una referencia de celda. Utilice rangos con nombre o referencias absolutas para rangos, si no desea que cambien demasiado.
¿Cómo funciona?
Analicémoslo desde adentro.
SI(B2: B19 = "Clase 1",FÓSFORO(A2: A19, A2: A19,0))
B2: B19 = "Clase 1": Esta parte devolverá una matriz de verdadero y falso. VERDADERO para cada partido.
{VERDADERO; FALSO; VERDADERO; FALSO; VERDADERO; VERDADERO; FALSO….}
FÓSFORO(A2: A19, A2: A19,0): esta parte devolverá la primera ubicación de cada valor en el rango A2: A19 según la propiedad de MATCH.
{1;2;1;4;5;4;1;8;9;1;2;1;4;5;4;1;8;9}.
Ahora, para cada valor VERDADERO obtendremos la posición y para falso obtendremos FALSO. Entonces, para toda la declaración IF obtendremos
{1; FALSO; 1; FALSO; 5; 4; FALSO; FALSO; FALSO; FALSO; 2; FALSO; FALSO; 5; FALSO; 1; 8; FALSO}.
A continuación, pasamos a la parte de frecuencia.
FRECUENCIA(SI(B2: B19 = "Clase 1",FÓSFORO(A2: A19, A2: A19,0)),HILERA(A2: A19) -HILERA(A2) +1)
FILA (A2: A19): Esto devuelve el número de fila de cada celda en el rango A2: A19.
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
FILA (A2: A19) -ROW (A2): Ahora restamos el número de la primera fila de cada número de fila. Esto devuelve una matriz de número de serie a partir de 0.
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
Como queremos tener un número de serie a partir de 1, le agregamos 1.
FILA (A2: A19) -ROW (A2) +1. Esto nos da una matriz de números de serie a partir de 1.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}
Esto nos ayudará a obtener una condición única.
Ahora tenemos: FRECUENCIA({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})
Esto devuelve la frecuencia de cada número en la matriz dada. {3; 1; 0; 1; 2; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Aquí cada número positivo indica la ocurrencia de un valor único cuando se cumplen los criterios. Necesitamos contar valores mayores que 0 en esta matriz. Para eso lo comprobamos por> 0. Esto devolverá VERDADERO y FALSO. Convertimos verdadero falso usando - (operador binario doble).
SUMA(--({3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0})>0) esto se traduce en SUM ({1; 1; 0; 1; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})
Y finalmente obtenemos el recuento único de nombres en el rango de criterios como 5.
Sé que es un poco complejo de entender, pero lo verifica en la opción de evaluación de fórmulas.
Para contar valores únicos con múltiples criterios, podemos usar lógica booleana:
Cuente el valor único con múltiples criterios con y lógica
{= SUM (- (FREQUENCY (IF (condition1 * Condition2, MATCH (range, range, 0)), ROW (range) -ROW (firstCell in range) +1)> 0))}
La fórmula genérica anterior puede contar valores únicos en múltiples condiciones y cuando todas son verdaderas.
Cuente el valor único con varios criterios con lógica o
{= SUM (- (FREQUENCY (IF (condition1 + Condition2, MATCH (range, range, 0)), ROW (range) -ROW (firstCell in range) +1)> 0))}
Esta fórmula genérica se puede utilizar para contar valores únicos con lógica Or. Significa que contará si alguna de las condiciones es verdadera.
Así que sí, chicos, así es como cuentan valores únicos en un rango en múltiples condiciones. Esto es un poco complejo pero rápido. Una vez que comience a usarlo, verá cómo funciona.
Si tiene alguna duda con respecto a este artículo de fórmula de Excel, hágamelo saber en la sección de comentarios a continuación.
Descargar archivo:
Cómo contar valores únicos en Excel con criteriosFórmula de Excel para extraer valores únicos de una lista
Contar valores únicos en Excel
Articulos populares:
La función BUSCARV en Excel
CONTAR.SI en Excel 2016
Cómo usar la función SUMIF en Excel
