¿Cómo puedo comprobar si una celda contiene alguno de varios valores en Excel?
En muchos escenarios empresariales, analíticos o de revisión de datos, es posible que tenga una lista de cadenas de texto en la columna A y desee comprobar si cada celda contiene alguno de los valores especificados en un rango como D2:D7. Por ejemplo, en datos de encuestas, registros o listas de productos, resulta fundamental identificar si las entradas incluyen palabras clave relevantes, códigos de producto o términos prohibidos. Si una celda incluye alguno de los elementos de su lista, Excel puede devolver Verdadero; de lo contrario, devolverá Falso, tal como se muestra en la captura de pantalla siguiente. Este artículo explica métodos prácticos para verificar si una celda contiene alguno de varios valores de otro rango, ofreciendo distintos enfoques adaptados a diversas versiones de Excel y necesidades del usuario.
Compruebe si una celda contiene uno de varios valores de una lista mediante fórmulas
Para saber si una celda contiene alguno de los valores de texto de otro rango, puede usar una fórmula matricial que funcione eficazmente incluso con grandes volúmenes de datos. Este método es ideal cuando necesita resultados en forma de valores lógicos ()Verdadero/Falso) para emplearlos en fórmulas adicionales, filtros o pruebas lógicas. Funciona en la mayoría de las versiones modernas de Excel.
Escriba la siguiente fórmula en una celda vacía (por ejemplo, en la celda B2, junto a sus datos originales) y arrastre el controlador de relleno hacia abajo para aplicarla a otras celdas según sea necesario. Devuelve Verdadero si la celda contiene alguno de los valores de texto de su rango limitado; de lo contrario, devuelve Falso. Vea la captura de pantalla:
Consejos y notas:
- Esta fórmula realiza una comprobación sin distinción entre mayúsculas y minúsculas. Si necesita distinguir entre mayúsculas y minúsculas, considere utilizar una columna auxiliar o combinar fórmulas de formas más avanzadas.
- Para obtener un resultado de «Sí» o «No» en lugar de Verdadero/Falso, utilice la siguiente fórmula ajustada:
- D2:D7 es el rango de valores (la «lista»); A2 es la celda que se va a comprobar.
- Tenga cuidado con las celdas vacías o los datos que no sean de texto, ya que la función HALLAR requiere una entrada de texto válida y los valores vacíos pueden provocar resultados inesperados («Verdadero»).
Muestre las coincidencias si la celda contiene uno de varios valores de una lista mediante fórmulas
A veces, resulta más útil mostrar los valores reales de la lista que aparecen en cada celda, en lugar de limitarse a un simple resultado Verdadero/Falso. Por ejemplo, al analizar descripciones de productos o comentarios en busca de palabras clave específicas, es posible que desee recuperar todos los valores encontrados para un análisis o informe posterior. Para ello, puede usar la siguiente fórmula y obtener todas las coincidencias separadas por comas, tal como se muestra a continuación:
Introduzca esta fórmula en una celda vacía (por ejemplo, B2) y mostrará todos los valores de D2:D7encontrados dentro de A2, separados por comas:
Nota: Aquí, D2:D7 es el rango de valores en el que se realizará la búsqueda y A2 es la celda que se va a buscar.
Después de escribir la fórmula, pulse Ctrl + Mayús + Entrar. A continuación, podrá arrastrar el controlador de relleno hacia abajo para aplicar la fórmula al resto de filas, tal como se muestra en la captura de pantalla del resultado:
- La función UNIRCADENAS solo está disponible en Excel 2019 y Office 365. En versiones anteriores de Excel, utilice la siguiente fórmula matricial: introdúzcala en una celda vacía y, a continuación, pulse Ctrl + Mayús + Entrar:
Arrastre la fórmula hacia la derecha para abarcar el mayor número posible de columnas y capturar todas las coincidencias disponibles; luego, arrástrela hacia abajo en cada fila. Las columnas adicionales permanecerán vacías cuando haya menos coincidencias. Este formato resulta especialmente útil cuando necesita mostrar las coincidencias en columnas separadas:
Si encuentra errores, revise nuevamente los rangos, asegúrese de que la región D2:D7 es correcta y confirme que está utilizando el delimitador adecuado para su configuración regional (coma o punto y coma).
Resalte las coincidencias si la celda contiene uno de varios valores de una lista con una función útil
Si necesita resaltar visualmente las palabras clave o frases que coincidan con cualquier valor de su lista dentro de cada celda, esto le ayudará a que los datos importantes destaquen para su revisión o acción. La función Marcado de palabras clave de Kutools para Excel está diseñada precisamente para este escenario, permitiéndole resaltar rápidamente todas las instancias de las palabras especificadas en el rango de datos, sin necesidad de escribir fórmulas ni código VBA. En hojas grandes o conjuntos de datos complejos, esta función resulta especialmente útil cuando la inspección manual no es viable.
Después de instalar Kutools para Excel, siga estos pasos:
1. Vaya a Kutools > Texto > Marcado de palabras clave para abrir el cuadro de diálogo, tal como se muestra:
2. En el cuadro de diálogo Marcado de palabras clave, realice lo siguiente:
- Seleccione su rango de datos objetivo en el cuadro Rango.
- Elija o introduzca manualmente las palabras clave en el cuadro Palabra clave (separadas por comas).
- Especifique un color de resaltado mediante la opción Color de fuente de palabra clave.
3. Haga clic en Aceptar. Todas las coincidencias con la palabra dentro del rango seleccionado se resaltarán con el color de fuente elegido:
- Esta función modifica directamente el formato de visualización de las palabras clave coincidentes, facilitando así la revisión o el intercambio de resultados con equipos que podrían no entender las salidas de fórmulas.
- Es especialmente eficaz cuando la lista de palabras clave o el rango de texto es muy amplio, o cuando necesita resaltar varios criterios simultáneamente.
- Revise siempre sus selecciones de rango y palabras clave antes de confirmar para evitar resaltados no deseados.
Compruebe si una celda contiene uno de varios valores mediante Usar formato condicional
La función integrada de Excel Usar formato condicional es otro enfoque eficaz para resaltar celdas que contengan cualquier valor de la lista especificada. Esta solución resulta especialmente útil para identificar visualmente filas relevantes de un vistazo, ya sea al revisar datos, comprobar errores o realizar tareas de cumplimiento normativo. A diferencia de la característica Marcado de palabras clave de Kutools, este enfoque no requiere complementos adicionales y está disponible en cualquier instalación estándar de Excel.
A continuación se explica cómo utilizar Usar formato condicional con una fórmula:
- Seleccione las celdas de su rango de datos (por ejemplo,)A2:A20) que desee supervisar.
- Vaya a la pestaña Inicio, haga clic en Formato condicional > Nueva regla.
- En el cuadro de diálogo Nueva regla de formato, elija Utilizar una fórmula que determine qué celdas se deben dar formato.
- Introduzca esta fórmula, suponiendo que D2:D7 contiene sus valores y que A2 es la primera celda de datos:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0- Haga clic en Formato, configure el formato deseado (por ejemplo, color de relleno) y pulse Aceptar.
Todas las celdas que contengan algún elemento de la lista D2:D7 se resaltarán automáticamente.
- Este método es dinámico: si actualiza el rango D2:D7, el formato se ajustará automáticamente.
- El formato condicional es únicamente visual: resalta las celdas de forma gráfica, pero no genera resultados en una columna independiente ni los hace disponibles para cálculos posteriores.
- El formato condicional basado en fórmulas es potente, pero con conjuntos de datos muy grandes puede ralentizar el rendimiento debido al cálculo repetido.
- Tenga en cuenta que HALLAR no distingue entre mayúsculas y minúsculas. Si necesita un enfoque que sí las distinga, puede requerir técnicas más avanzadas o el uso de columnas auxiliares.
Más artículos relacionados:
- Comparar dos o más cadenas de texto en Excel
- Si desea comparar dos o más cadenas de texto en una hoja de cálculo, ya sea distinguiendo entre mayúsculas y minúsculas o sin hacerlo, como se muestra en la siguiente captura de pantalla, en este artículo le explicaré algunas fórmulas útiles para realizar esta tarea en Excel.
- Si la celda contiene texto, mostrarlo en Excel
- Si tiene una lista de cadenas de texto en la columna A y una fila de palabras clave, ahora debe verificar si esas palabras clave aparecen en cada cadena. Cuando una palabra clave está presente en la celda, se muestra; de lo contrario, la celda permanece vacía, tal como se muestra en la siguiente captura de pantalla.
- Contar las palabras clave que contiene una celda según una lista
- Si desea contar cuántas palabras clave de una lista aparecen en una celda, la combinación de las funciones SUMAPRODUCTO, ESNÚMERO y HALLAR le ayudará a resolver este problema en Excel.
- Buscar y reemplazar varios valores en Excel
- Normalmente, la función Buscar y reemplazar le permite localizar un texto específico y sustituirlo por otro, pero en ocasiones puede necesitar buscar y reemplazar varios valores al mismo tiempo. Por ejemplo, cambiar todo el texto «Excel» por «Excel2019», «Outlook» por «Outlook2019», etc., tal como se muestra en la siguiente captura de pantalla. En este artículo, presentaré una fórmula para llevar a cabo esta tarea en Excel.
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos