EXCEL: Cuente las celdas que no contienen muchas cadenas

Para contar celdas que no contienen muchas cadenas diferentes, puede usar una fórmula bastante compleja basada en la función MMULT. En el ejemplo que se muestra, la fórmula en F5 es:

{=SUMA(1-(MMULT(--(ESNUMERO(HALLAR(TRANSPONER(exclude),data))),FILA(exclude)^0)>0))}

donde "datos" es el rango nombrado B5: B14, y "excluir" es el rango nombrado D5: D7.

Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter

Prefacio

Esta fórmula es complicada por el requisito de "contiene". Si solo necesita una fórmula para contar celdas que no son * iguales * a muchas cosas, puede usar una fórmula más sencilla basada en la función COINCIDIR. Además, si tiene un número limitado de cadenas para excluir, puede usar la función CONTAR.SI.CONJUNTO de esta manera:

=CONTAR.SI.CONJUNTO(data,"<>*pink*",data,"<>*orange*",data,"<>*black*")

Sin embargo, con este enfoque, debe ingresar un nuevo par de argumentos de rango / criterio para que cada cadena se excluya. En contraste, la fórmula que se explica a continuación puede manejar una gran cantidad de cadenas para excluir ingresadas directamente en la hoja de trabajo.

Finalmente, esta fórmula es compleja. Avíseme si tiene una fórmula más simple para proponer :)

Cómo funciona esta fórmula

El núcleo de esta fórmula es ESNUMERO y HALLAR:

ESNUMERO(HALLAR(TRANSPONER(exclude),data))

Aquí, transponemos los elementos en el rango con nombre "excluir", luego enviamos el resultado a HALLAR como "buscar texto", con "datos" como "dentro del texto". La función HALLAR devuelve una matriz 2d de valores VERDADERO y FALSO, 10 filas por 3 columnas, de esta manera:

{3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12}

Para cada valor en "datos", tenemos 3 resultados (uno por cadena de búsqueda) que son #VALOR errores o números. Los números representan la posición de una cadena de texto encontrada, y los errores representan cadenas de texto no encontradas. Por cierto, la función TRANSPONER es necesaria para generar la matriz de 10 x 3 de resultados completos.

Esta matriz se alimenta a ESNUMERO para obtener valores VERDADERO FALSO, que convertimos a 1s y 0s con un operador doble negativo (-). El resultado es una matriz como esta:

{1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1}

que entra en la función MMULT como array1. Siguiendo las reglas de la multiplicación de matrices, el número de columnas en la matriz1 debe ser igual a la cantidad de filas en la matriz2. Para generar array2, usamos la función FILA de esta manera:

FILA(exclude)^0

Esto produce una matriz de 1s, 3 filas por 1 columna:

{1;1;1}

que entra en MMULT como array2. Después de la multiplicación de la matriz, tenemos una matriz dimensionada para que coincida con los datos originales:

{2;1;0;0;1;1;0;0;0;2}

En esta matriz, cualquier número distinto de cero representa un valor donde se ha encontrado al menos una de las cadenas excluidas. Los ceros indican que no se encontraron cadenas excluidas. Para forzar todos los valores distintos de cero a 1, utilizamos mayor que cero:

{2;1;0;0;1;1;0;0;0;2}>0

que crea otra matriz o valores VERDADERO y FALSO:

{VERDADERO;VERDADERO;FALSO;FALSO;VERDADERO;VERDADERO;FALSO;FALSO;FALSO;VERDADERO}

Nuestro objetivo final es contar solo los valores de texto donde no se encontraron cadenas excluidas, por lo que debemos revertir estos valores. Hacemos esto restando la matriz de 1. Este es un ejemplo de lógica booleana. La operación matemática obliga automáticamente a los valores VERDADERO y FALSO a 1s y 0s, y finalmente tenemos una matriz para volver a la función SUMA:

=SUMA({0;0;1;1;0;0;1;1;1;0})

La función SUMA devuelve un resultado final de 5.