Fórmula de Excel: Comprobar si una celda contiene uno de varios valores pero excluye otros valores

Supongamos que hay dos listas de valores y quieres comprobar si la celda B3 contiene uno de los valores en el rango E3:E5, pero al mismo tiempo no contiene ninguno de los valores en el rango F3:F4, como se muestra en la siguiente captura de pantalla. Este tutorial proporcionará una fórmula para manejar rápidamente esta tarea en Excel y explicará los argumentos de la fórmula.

Fórmula genérica:

Argumentos

Valor devuelto:

La fórmula devuelve 1 o 0. Cuando la celda contiene uno de los valores que deben incluirse y no contiene ningún valor que deba excluirse, devuelve 1; de lo contrario, devuelve 0. En esta fórmula, 1 y 0 se manejan como valores lógicos VERDADERO y FALSO.

Cómo funciona esta fórmula

Supongamos que deseas comprobar si la celda B3 contiene uno de los valores en el rango E3:E5, pero al mismo tiempo excluir los valores en el rango F3:F4, utiliza la siguiente fórmula:

Pulsa Enter para obtener el resultado de la verificación.

Explicación

Parte 1: (SUMAPRODUCTO(--ESNUMERO(BUSCAR($E$3:$E$5,B3)))>0) comprueba si la celda contiene valores en E3:E5

BUSCAR función: la función BUSCAR devuelve la posición del primer carácter de la cadena de texto dentro de otra. Si la función BUSCAR encuentra el texto coincidente, devuelve la posición relativa; de lo contrario, devuelve un error #¡VALOR!. Por ejemplo, aquí la fórmula BUSCAR($E$3:$E$5,B3) buscará cada valor del rango E3:E5 en la celda B3 y devolverá la ubicación de cada cadena de texto en la celda B3. Devolverá un resultado de matriz como este: {1;7;12}.

Función ESNUMERO: la función ESNUMERO devuelve VERDADERO cuando una celda es un número. Entonces, ESNUMERO(BUSCAR($E$3:$E$5,B3)) devolverá un resultado de matriz como {verdadero,verdadero,verdadero}, ya que la función BUSCAR encuentra 3 números.

--ESNUMERO(BUSCAR($E$3:$E$5,B3)) convierte el valor VERDADERO a 1 y el valor FALSO a 0, por lo que esta fórmula cambia el resultado de la matriz a {1;1;1}.

Función SUMAPRODUCTO: se utiliza para multiplicar rangos o sumar matrices juntas y devuelve la suma de productos. La función SUMAPRODUCTO(--ESNUMERO(BUSCAR($E$3:$E$5,B3))) devuelve 1+1+1=3.

Finalmente, compara la fórmula de la izquierda SUMAPRODUCTO(--ESNUMERO(BUSCAR($E$3:$E$5,B3))) con 0; siempre que el resultado de la fórmula de la izquierda sea mayor que 0, el resultado será VERDADERO; de lo contrario, devolverá FALSO. Aquí devuelve VERDADERO.

Parte 2: (SUMAPRODUCTO(--ESNUMERO(BUSCAR($F$3:$F$4,B3)))=0) comprueba si la celda no contiene valores en F3:F4

La fórmula BUSCAR($F$3:$F$4,B3) buscará cada valor en el rango E3:E5 en la celda B3 y devolverá la ubicación de cada cadena de texto en la celda B3. Devolverá un resultado de matriz como este: {#¡VALOR!;#¡VALOR!}.

ESNUMERO(BUSCAR($F$3:$F$4,B3)) devolverá un resultado de matriz como {falso;falso}, ya que la función BUSCAR no encuentra ningún número.

--ESNUMERO(BUSCAR($F$3:$F$4,B3)) convierte el valor VERDADERO a 1 y el valor FALSO a 0, por lo que esta fórmula cambia el resultado de la matriz a {0;0}.

Función SUMAPRODUCTO: se utiliza para multiplicar rangos o sumar matrices juntas y devuelve la suma de productos. La función SUMAPRODUCTO(--ESNUMERO(BUSCAR($F$3:$F$4,B3))) devuelve 0+0=0.

Finalmente, compara la fórmula de la izquierda SUMAPRODUCTO(--ESNUMERO(BUSCAR($F$3:$F$4,B3))) con 0; siempre que el resultado de la fórmula de la izquierda sea igual a 0, el resultado devolverá VERDADERO; de lo contrario, devolverá FALSO. Aquí devuelve VERDADERO.

Parte 3: Multiplica las dos fórmulas

=(SUMAPRODUCTO(--ESNUMERO(BUSCAR($E$3:$E$5,B3)))>0)*(SUMAPRODUCTO(--ESNUMERO(BUSCAR($F$3:$F$4,B3)))=0)

=VERDADERO*VERDADERO

=1

En esta fórmula, 1 y 0 se manejan como valores lógicos VERDADERO y FALSO.

Archivo de muestra

Haz clic para descargar el archivo de muestra

Fórmulas relacionadas

• Comprobar si una celda contiene un texto específico
  Para comprobar si una celda contiene algunos textos en el rango A pero no contiene los textos en el rango B, puedes usar una fórmula matricial que combine las funciones CONTAR, BUSCAR y Y en Excel.
• Comprobar si una celda contiene una de muchas cosas
  Este tutorial proporciona una fórmula para comprobar si una celda contiene uno de varios valores en Excel y explica los argumentos en la fórmula y cómo funciona la fórmula.
• Comprobar si una celda contiene una de varias cosas
  Supongamos que en Excel hay una lista de valores en la columna E, y quieres comprobar si las celdas en la columna B contienen todos los valores en la columna E y devolver VERDADERO o FALSO.
• Comprobar si una celda contiene un número
  A veces, es posible que desees comprobar si una celda contiene caracteres numéricos. Este tutorial proporciona una fórmula que devolverá VERDADERO si la celda contiene un número y FALSO si la celda no contiene un número.