Especificar el conteo condicional de un valor suena como una tarea desalentadora, pero dos funciones de Excel hacen que este desafío se resuelva rápidamente.
Contar duplicados es una tarea común de Excel que se puede realizar fácilmente con una simple función COUNT() o un conjunto de datos filtrados. Sin embargo, si los recuentos son más importantes que los valores en sí mismos, es posible que la tarea le resulte un poco más difícil. Por ejemplo, es posible que necesite saber cuántas veces ocurre cualquier valor una vez, dos veces, tres veces, cuatro veces, y así sucesivamente. En otras palabras, necesita un conteo condicional de un conteo simple. Afortunadamente, el problema sólo suena complicado. En este artículo, le mostraré una solución sencilla que incluye una columna de ayuda y una matriz sencilla. Si necesitas una solución de expresión, te lo mostraré también.
Más información sobre Office
Estoy usando Excel 2019 en un sistema Windows 10 de 64 bits, pero esta solución funcionará en las versiones 2007 a 2019. La función COUNTIFS() no es compatible con Excel 2003 o anterior. Puede trabajar con cualquier conjunto de datos o descargar el archivo de demostración.xlsx.
Preparando las cosas
Tal vez se esté preguntando a qué me refiero con la función COUNTIFS() de Excel, que hace exactamente eso. Pero en este caso, estamos contando cuentas condicionalmente, no valores. La figura A muestra un conjunto de datos simple. El primer paso es contar el número de veces que un valor ocurre en el mismo conjunto de datos. Esto se puede lograr fácilmente con una simple función COUNT() en el formulario:
COUNT (columna, valor)
donde la columna hace referencia a la columna que contiene los valores que está contando y el valor es una celda en la misma columna. La función de conteo en la columna C:
=COUNTIF(B:B,B,B3)
utiliza una referencia de columna (B:B) para contar el número de veces que cada valor aparece en la columna B:
- Mike y Susan sólo tienen un registro en el conjunto de datos.
- John y George tienen dos registros en el conjunto de datos.
- Dawn tiene cinco registros en el conjunto de datos.
Use COUNT() para devolver el número de veces que ocurre el mismo valor.
La referencia de columna es una herramienta poderosa, pero nos limita un poco: No se pueden utilizar las celdas vacías de la columna B por debajo o por encima del conjunto de datos real para cualquier otra cosa. Ahora, supongamos que saber cuántos registros por persona no es su objetivo, usted quiere saber cuántas veces se repite un registro. En otras palabras, necesita saber cuántos registros ocurren una vez, dos veces, tres veces, y así sucesivamente.
Conteo condicional del conteo
En un simple conjunto de datos, puede ver los resultados de la función COUNT() y determinarlos rápidamente:
- Dos personas, Mike y Susan, tienen un disco cada una.
- Dos personas, John y George, tienen dos discos.
- Nadie tiene tres o cuatro discos.
- Una persona, Dawn, tiene cinco discos.
- Nadie tiene más de cinco discos.
Podría omitir fácilmente los nombres y los resultados serían igual de importantes: estamos tras el conteo condicional del conteo, no tras los valores reales que se están contando. Las menciono sólo para aclarar los resultados.
La figura B muestra nuestra matriz, que utiliza la función COUNTIFS() para contar condicionalmente los resultados de la función COUNT() en la columna C.
Una simple matriz cuenta los resultados de la función COUNT(.
Esta función toma la forma:
PAÍSES (país, criterios)
donde countrange es el rango con los valores que está contando y los criterios es que los valores de condición deben cumplir para ser incluidos en el conteo.
La función COUNTIFS() en la fila 3:
=CONDADOS($C$3:$C$13,F$2)/F$2
utiliza los valores de cabecera de la matriz simple como criterios. Ese resultado se divide entonces por criterios.
Sin embargo, la matriz no ofrece una imagen completa. Podemos ver claramente que al menos un valor tiene cinco registros pero la matriz no lo refleja.
Una solución de expresión
La matriz es tan buena como su fila de encabezado.
Podemos extender esa fila, ¿pero a qué distancia será suficiente? Una solución más dinámica es usar la misma expresión para referirse a los resultados de COUNT() en la columna C. La figura C muestra la expresión:
=CONDES($C$3:$C$13,$C3)/$C3
que utiliza como criterio el resultado de la función COUNT(.
La expresión de la matriz es más dinámica cuando se refiere a los valores de conteo.
Al referirse a los resultados reales, la expresión garantiza que se tengan en cuenta todas las posibilidades. La matriz también podría hacer esto si supieras de antemano hasta dónde se puede extender. Afortunadamente, el ESP no es un requisito para el conteo condicional.
En el lado negativo, la expresión en la columna D podría confundir a los usuarios que no entienden su propósito. La columna C muestra el conteo y la columna D muestra el número de veces que se repite el «conteo». La Tabla A ofrece una revisión rápida.
Cuadro A
Le sugiero que no incluya las columnas de conteo cuando comparta los datos con otros. Esta solución le ayuda a recopilar información que podría ser significativa si se presenta en otro formato.
Otras soluciones
Esta columna fue inspirada por un lector. Elegí esta ruta porque, al menos en mi mente, era rápida, y las características incorporadas que a menudo considero, como el filtrado y los subtotales, presentaban demasiados aros para saltar. ¿Tiene una solución diferente? Si es así, por favor compártalo en la sección de comentarios a continuación.
Envíenme sus preguntas sobre Office
Respondo a las preguntas de los lectores cuando puedo, pero no hay garantía. No envíe archivos a menos que se le solicite; las solicitudes iniciales de ayuda que lleguen con archivos adjuntos se eliminarán sin leer. Puede enviar capturas de pantalla de sus datos para ayudar a aclarar su pregunta. Cuando se ponga en contacto conmigo, sea lo más específico posible. Por ejemplo, «Please troubleshoot my workbook and fix what’s wrong» probablemente no obtendrá respuesta, pero «Can you tell me why this formula is’ t returning the expected results? Por favor, menciona la aplicación y la versión que estás usando. ConsejoTecnologico.com no me reembolsa por mi tiempo o experiencia en ayudar a los lectores, ni tampoco pido una cuota a los lectores a los que ayudo. Puede ponerse en contacto conmigo en susansalesharkins@gmail.com.