El tutorial muestra cómo contar el número de celdas con cierto texto en Excel. Encontrará ejemplos de fórmulas para coincidencias exactas, coincidencias parciales y celdas filtradas.
La semana pasada vimos cómo contar celdas con texto en Excel, es decir, todas las celdas con cualquier texto. Al analizar grandes cantidades de información, es posible que también desee saber cuántas celdas contienen texto específico. En este tutorial se explica cómo hacerlo de forma sencilla.
Cómo contar celdas con texto específico en Excel
Microsoft Excel tiene una función especial para contar celdas condicionalmente, la función CONTAR.SI. Todo lo que tiene que hacer es proporcionar la cadena de texto de destino en el argumento de criterios.
Aquí hay una fórmula genérica de Excel para contar el número de celdas que contienen texto específico:
CONTAR.SI(rango, «texto»)
El siguiente ejemplo lo muestra en acción. Supongamos que tiene una lista de ID de elementos en A2: A10 y desea contar la cantidad de celdas con una ID en particular, diga «AA-01». Escriba esta cadena en el segundo argumento y obtendrá esta fórmula simple:
=COUNTIF(A2:A10, "AA-01")
Para permitir que sus usuarios cuenten celdas con cualquier texto dado sin necesidad de modificar la fórmula, ingrese el texto en una celda predefinida, digamos D1, y proporcione la referencia de celda:
=COUNTIF(A2:A10, D1)
Nota. La función CONTAR.SI de Excel es no distingue entre mayúsculas y minúsculas, lo que significa que no diferencia entre mayúsculas y minúsculas. Para tratar los caracteres en mayúsculas y minúsculas de manera diferente, utilice esta fórmula que distingue entre mayúsculas y minúsculas.
Cómo contar celdas con cierto texto (coincidencia parcial)
La fórmula discutida en el ejemplo anterior coincide exactamente con los criterios. Si hay al menos un carácter diferente en una celda, por ejemplo, un espacio adicional al final, no será una coincidencia exacta y dicha celda no se contará.
Para encontrar la cantidad de celdas que contienen cierto texto como parte de su contenido, use caracteres comodín en sus criterios, es decir, un asterisco
que representa cualquier secuencia o caracteres. Dependiendo de su objetivo, una fórmula puede parecerse a una de las siguientes. Cuente las celdas que contienen texto específico en elmuy comienzo
:
CONTAR.SI(rango, «texto*») Cuente las celdas que contienen cierto texto enCualquier posición
:
CONTAR.SI(rango, «*texto*»)
=COUNTIF(A2:A10, "AA*")
Por ejemplo, para encontrar cuántas celdas en el rango A2:A10 comienzan con «AA», use esta fórmula:
=COUNTIF(A2:A10, "*AA*")
Para obtener el recuento de celdas que contienen «AA» en cualquier posición, use este:
Para hacer que las fórmulas sean más dinámicas, reemplace las cadenas codificadas con referencias de celda.
=COUNTIF(A2:A10, D1&"*")
Para contar celdas que comienzan con cierto texto:
=COUNTIF(A2:A10, "*"&D1&"*")
Para contar celdas con cierto texto en cualquier parte de ellas:
Fórmula para contar celdas que contienen una cadena de texto dada
Cuente las celdas que contienen texto específico (distingue entre mayúsculas y minúsculas)
En una situación en la que necesite diferenciar caracteres en mayúsculas y minúsculas, la función CONTAR.SI no funcionará. Dependiendo de si está buscando una coincidencia exacta o parcial, tendrá que crear una fórmula diferente.
Fórmula que distingue entre mayúsculas y minúsculas para contar celdas con texto específico (coincidencia exacta)
Para contar el número de celdas con cierto texto reconociendo mayúsculas y minúsculas, usaremos una combinación de las funciones SUMAPRODUCTO y EXACTO:
SUMAPRODUCTO(–EXACTO(«texto», rango))
- Cómo funciona esta fórmula:
- EXACTO compara cada celda en el rango con el texto de muestra y devuelve una matriz de valores VERDADERO y FALSO, VERDADERO representa coincidencias exactas y FALSO todas las demás celdas. Un guión doble (llamado unario doble) convierte VERDADERO y FALSO en 1 y 0.
SUMPRODUCT suma todos los elementos de la matriz. Esa suma es el número de 1, que es el número de coincidencias.
=SUMPRODUCT(--EXACT(D1, A2:A10))
Fórmula que distingue entre mayúsculas y minúsculas para obtener el número de celdas con un texto particular
Fórmula que distingue entre mayúsculas y minúsculas para contar celdas con texto específico (coincidencia parcial)
Para construir una fórmula que distinga entre mayúsculas y minúsculas que pueda encontrar una cadena de texto de interés en cualquier lugar de una celda, estamos usando 3 funciones diferentes:
SUMAPRODUCTO(–(ESNUMERO(ENCONTRAR(«texto», rango))))
- Cómo funciona esta fórmula: distingue entre mayúsculas y minúsculas Función ENCONTRAR
- busca el texto de destino en cada celda del rango. Si tiene éxito, la función devuelve la posición del primer carácter, de lo contrario, el #¡VALOR! error. En aras de la claridad, no necesitamos saber la posición exacta, cualquier número (a diferencia del error) significa que la celda contiene el texto de destino.
- La función ESNUMERO maneja la matriz de números y errores devueltos por ENCONTRAR y convierte los números en VERDADERO y cualquier otra cosa en FALSO. Un unario doble (–) convierte los valores lógicos en unos y ceros.
SUMPRODUCT suma la matriz de 1 y 0 y devuelve el recuento de celdas que contienen el texto especificado como parte de su contenido.
=SUMPRODUCT(--(ISNUMBER(FIND(D1, A2:A10))))
Para probar la fórmula en datos de la vida real, busquemos cuántas celdas en A2:A10 contienen la entrada de subcadena en D1:
Fórmula que distingue entre mayúsculas y minúsculas para contar celdas que contienen cierto texto en cualquier parte de ellas
Cómo contar celdas filtradas con texto específico Para contar elementos visibles
en una lista filtrada, deberá usar una combinación de 4 o más funciones dependiendo de si desea una coincidencia exacta o parcial. Para que los ejemplos sean más fáciles de seguir, echemos un vistazo rápido a los datos de origen primero.
Cómo contar celdas filtradas con cierto texto
Fórmula para contar celdas filtradas con texto específico (coincidencia exacta)
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(B2:B10=F1))
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), --(B2:B10=F1))
Para contar celdas filtradas cuyo contenido coincida exactamente con la cadena de texto de muestra, use una de las siguientes fórmulas:
Fórmula para contar celdas filtradas con texto particular
Cómo funcionan estas fórmulas:
- En el núcleo de ambas fórmulas, realiza 2 comprobaciones:
- Identifica filas visibles y ocultas. Para esto, use la función SUBTOTAL con el argumento núm_función establecido en 103. Para proporcionar todas las referencias de celdas individuales a SUBTOTAL, utilice INDIRECTO (en la primera fórmula) o una combinación de DESPLAZAMIENTO, FILA y MÍN (en la segunda fórmula) . Dado que nuestro objetivo es ubicar filas visibles y ocultas, en realidad no importa a qué columna hacer referencia (A en nuestro ejemplo). El resultado de esta operación es una matriz de 1 y 0 donde los unos representan filas visibles y los ceros, filas ocultas.
Encuentra celdas que contienen texto dado. Para ello, compare el texto de ejemplo (F1) con el rango de celdas (B2:B10). El resultado de esta operación es una matriz de valores VERDADERO y FALSO, que se convierten en 1 y 0 con la ayuda del operador unario doble.
Finalmente, la función SUMPRODUCT multiplica los elementos de las dos matrices en las mismas posiciones y luego suma la matriz resultante. Debido a que multiplicar por cero da cero, solo las celdas que tienen 1 en ambas matrices tienen 1 en la matriz final. La suma de 1 es el número de celdas filtradas que contienen el texto especificado.
Fórmula para contar celdas filtradas con texto específico (coincidencia parcial)
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(ISNUMBER(FIND(F1, B2:B10))))
=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), --(ISNUMBER(FIND(F1, B2:B10))))
Para contar las celdas filtradas que contienen cierto texto como parte del contenido de la celda, modifique las fórmulas anteriores de la siguiente manera. En lugar de comparar el texto de muestra con el rango de celdas, busque el texto de destino usando ESNUMERO y ENCONTRAR como se explica en uno de los ejemplos anteriores:
Fórmula para contar celdas filtradas con una determinada cadena de texto en cualquier posición Nota. La función SUBTOTAL con 103 en el argumento núm_función identifica todas las celdas ocultas, filtradas y ocultas manualmente. Como resultado, las fórmulas anteriores cuentan solo celdas visibles
independientemente de cómo se ocultaron las células invisibles. Para excluir solo las celdas filtradas pero incluir las ocultas manualmente, use 3 para function_num.
Así es como se cuenta el número de celdas con cierto texto en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
descargas disponibles