Fórmula genérica
Explicación
Para contar valores de texto únicos en un rango con criterios, puede usar una fórmula de matriz basada en las funciones FRECUENCIA y COINCIDIR. En el ejemplo que se muestra, la fórmula en G6 es:
que devuelve 3, ya que tres personas diferentes trabajaron en el proyecto Omega.
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Cómo funciona esta fórmula
Esta es una fórmula compleja que usa FRECUENCIA para contar valores numéricos derivados de la función COINCIDIR. Trabajando de adentro hacia afuera, la función COINCIDIR se usa para obtener la posición de cada valor que aparece en los datos:
COINCIDIR(B5:B11,B5:B11,0)
El resultado de COINCIDIR es una matriz como esta:
{1;1;3;1;1;6;7}
Como COINCIDIR siempre devuelve la posición de la primera coincidencia, los valores que aparecen más de una vez en los datos devuelven la misma posición. Por ejemplo, debido a que "Jim" aparece 4 veces en la lista, aparece en esta matriz 4 veces como el número 1.
Fuera de la función COINCIDIR, la función SI se utiliza para aplicar criterios, que en este caso implica probar si el proyecto es "omega" (de la celda G5):
SI(C5:C11=G5 // filter on "omega"
La función SI actúa como un filtro, solo permite que los valores de COINCIDIR pasen si están asociados con "omega". El resultado es una matriz como esta:
{FALSO;FALSO;FALSO;1;1;6;7} // after filtering
La matriz filtrada se entrega directamente a la función FRECUENCIA como argumento data_array. A continuación, la función FILA se utiliza para crear una lista secuencial de números para cada valor en los datos:
Esto crea una matriz como esta:
{1;2;3;4;5;6;7;8;9;10}
que se convierte en el argumento bins_array en FILTER. En este punto, tenemos:
FRECUENCIA({FALSO;FALSO;FALSO;1;1;6;7},{1;2;3;4;5;6;7})
FRECUENCIA devuelve un conjunto de números que indican un recuento para cada valor en el conjunto de datos, organizado por bin. Cuando ya se ha contado un número, FRECUENCIA devolverá cero. El resultado de FRECUENCIA es una matriz como esta:
{2;0;0;0;0;1;1;0} // result from FREQUENCY
Nota: FRECUENCIA siempre devuelve una matriz con un elemento más que bins_array.
En este punto, podemos reescribir la fórmula de esta manera:
=SUMA(--({2;0;0;0;0;1;1;0}>0))
Verificamos valores mayores que cero, que convierte los números a VERDADERO o FALSO:
=SUMA(--({VERDADERO;FALSO;FALSO;FALSO;FALSO;VERDADERO;VERDADERO;FALSO}))
Luego usamos un doble negativo para forzar los valores lógicos a 1s y 0s:
=SUMA({1;0;0;0;0;1;1;0})
Finalmente, la función SUMA devuelve 3 como resultado final.
Nota: esta es una fórmula de matriz y debe ingresarse usando Control + Shift + Enter.
Manejo de celdas vacías en el rango
Si alguna celda en el rango está vacía, deberá ajustar la fórmula para evitar que las celdas vacías pasen a la función COINCIDIR, lo que arrojará un error. Puede hacer esto agregando otra función SI anidada para verificar las celdas en blanco:
Con dos criterios
Si tiene dos criterios, puede ampliar la lógica de la fórmula agregando otro SI anidado:
Donde c1 = criterio1, c2 = criterio2 y vals = el rango de valores.
Con lógica booleana
Con la lógica booleana, puede reducir los IF anidados:
Esto facilita agregar y administrar criterios adicionales.
0 comentarios:
Publicar un comentario