29 Ago Hallar si una celda contiene todos los valores de un rango en Excel
Funciones utilizadas: SUMAPRODUCTO, ESNUMERO, HALLAR, CONTARA
Suponiendo un rango con distintos textos en A2:A10 y un rango con valores específicos en D2:D5, planteamos el siguiente método a aplicarse en el rango B2:B10 (comenzando en B2) para hallar si cada celda del rango de la columna A contiene todos los valores específicos del rango de la columna D:
HALLAR SI UNA CELDA CONTIENE TODOS LOS VALORES DE UN RANGO EN EXCEL
|
Donde «A2» corresponde a la primera celda que contiene un texto a analizar, y «$D$2:$D$5» corresponde al rango de valores específicos a analizar dentro de dicha celda A2. Notar que dicho rango de columna D se encuentra fijo para poder arrastrar la fórmula desde B2 hasta B10.
Combinamos las funciones SUMAPRODUCTO, ESNUMERO, HALLAR y CONTARA para obtener el resultado deseado.
Primero aplicamos la función HALLAR para devolver la posición del primer carácter de cada valor buscado dentro de la celda objetivo en caso de encontrarlo, caso contrario devuelve error.
Luego lo englobamos en la función ESNUMERO para convertir dicho resultado a un valor lógico VERDADERO/FALSO y aplicarle el doble operador negativo para convertirlo finalmente a 1/0.
Finalmente englobamos dicho resultado con la función SUMAPRODUCTO para devolver la cantidad de valores buscados que contiene la celda objetivo, y lo igualamos a la cantidad total de valores buscados aplicado con la función CONTARA, de esta forma si el resultado es VERDADERO la celda objetivo contendrá todos los valores buscados, caso contrario devolverá FALSO.
Recordar de fijar las referencias del rango de valores buscados (en el ejemplo D2:D5).
De esta forma logramos hallar si una celda contiene todos los valores específicos de otro rango de forma rápida y fácil en Excel.
Puedes encontrar más fórmulas interesantes en el anuario de Desafiando Excel en el siguiente link o videos con más fórmulas ingeniosas de Excel aquí.
Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.