Contar el número de celdas que no contienen muchos valores

Normalmente, es fácil contar celdas que no contienen un valor específico con la función CONTAR.SI. Este artículo te guiará a través de cómo contar el número de celdas que no contienen muchos valores en un rango especificado en Excel.

¿Cómo contar celdas que no contienen muchos valores?

Como se muestra en la siguiente captura de pantalla, para contar las celdas en B3:B11 que no contienen los valores listados en D3:D4, puedes hacer lo siguiente.

Fórmula genérica

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

Argumentos

Nota: Esta fórmula debe ingresarse como una fórmula de matriz. Después de aplicar la fórmula, si hay llaves alrededor de la fórmula, significa que se ha creado correctamente una fórmula de matriz.

¿Cómo usar esta fórmula?

1. Selecciona una celda en blanco para mostrar el resultado.

2. Ingresa la siguiente fórmula y luego presiona simultáneamente las teclas Ctrl + Shift + Enter para obtener el resultado.

=SUMA(1-(MMULT(--(ESNUMERO(BUSCAR(TRANSPONER(D3:D4);B3:B11)));FILA(D3:D4)^0)>0))

¿Cómo funcionan estas fórmulas?

=SUMA(1-(MMULT(--(ESNUMERO(BUSCAR(TRANSPONER(D3:D4);B3:B11)));FILA(D3:D4)^0)>0))

1) --(ESNUMERO(BUSCAR(TRANSPONER(D3:D4);B3:B11))):

• TRANSPONER(D3:D4): La función TRANSPONER rota la orientación de D3:D4 y devuelve {“count”,”blank”};
• BUSCAR({“count”,”blank”};B3:B11): La función BUSCAR aquí encuentra la posición de la subcadena “count” y “blank” desde el rango B3:B11, y devuelve una matriz como {#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;1;#¡VALOR!;#¡VALOR!;8;1;#¡VALOR!;#¡VALOR!;#¡VALOR!;1;
  #¡VALOR!;1;7}.
• En este caso, cada celda en B3:B11 será buscada dos veces ya que hay dos valores que deseas excluir al contar celdas, por lo que obtendrás 18 valores en la matriz. Cada número en la matriz indica la posición del primer carácter de “count” o “blank” en cada celda de B3:B11.
• ESNUMERO{#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;VERDADERO;FALSO;FALSO;VERDADERO;VERDADERO;
  FALSO;FALSO;FALSO;VERDADERO;FALSO;VERDADERO;VERDADERO}: La función ESNUMERO devuelve VERDADERO si encuentra números en la matriz, y devuelve FALSO si encuentra errores. Aquí devuelve el resultado como {FALSO;FALSO;VERDADERO;FALSO;FALSO;VERDADERO;FALSO;FALSO;VERDADERO;FALSO;FALSO;VERDADERO;
  VERDADERO;VERDADERO}.
• --({FALSO;FALSO;VERDADERO;FALSO;FALSO;VERDADERO;FALSO;FALSO;VERDADERO;FALSO;FALSO;VERDADERO;
  VERDADERO;VERDADERO}): Estos dos signos menos convierten “VERDADERO” en 1 y convierten “FALSO” en 0. Aquí obtendrás una nueva matriz como {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2) FILA(D3:D4)^0: La función FILA devuelve los números de fila de la referencia de celda: {3;4}, y luego el operador de exponenciación (^) devuelve el resultado de elevar los números 3 y 4 a la potencia 0, y finalmente devuelve el resultado como {1;1}.

3) MMULT({0;0;1;0;0;1;0;0;1;0;1;1};{1;1}): La función MMULT devuelve el producto matricial de estos dos arreglos: {0;0;1;1;1;0;0;1;2} para cumplir con los datos originales. Cualquier número distinto de cero en la matriz representa un valor donde al menos uno de los strings excluidos ha sido encontrado, y cero indica que no se encontraron strings excluidos.

4) SUMA(1-{0;0;1;1;1;0;0;1;2}>0):

• {0;0;1;1;1;0;0;1;2}>0: Aquí se verifica si cada número en la matriz es mayor que 0. Si un número es mayor que 0, devuelve VERDADERO, si no, devuelve FALSO. Luego obtendrás una nueva matriz como {FALSO;FALSO;VERDADERO;VERDADERO;VERDADERO;FALSO;FALSO;VERDADERO;VERDADERO}.
• 1-{FALSO;FALSO;VERDADERO;VERDADERO;VERDADERO;FALSO;FALSO;VERDADERO;VERDADERO}: Como necesitamos contar solo las celdas que no contienen los valores especificados, necesitamos invertir estos valores en la matriz restando de 1. Aquí el operador matemático automáticamente convierte los valores VERDADERO y FALSO a 1s y 0s, y finalmente devuelve el resultado como {1;1;0;0;0;1;1;0;0}.
• SUMA{1;1;0;0;0;1;1;0;0}: La función SUMA suma todos los números en la matriz y devuelve el resultado final como 4.

Funciones relacionadas

Fórmulas relacionadas