¿Cómo contar el número de valores únicos en un rango según varios criterios en Excel?
En muchos escenarios prácticos, no basta con contar valores: es necesario determinar cuántos elementos únicos cumplen ciertas condiciones en sus datos. Por ejemplo, quizá quiera saber cuántos productos distintos vendió un vendedor específico o cuántos pedidos únicos se realizaron en un período determinado. Para abordar estas tareas de forma eficaz en Excel, deberá dominar fórmulas adecuadas, funciones avanzadas como las tablas dinámicas e incluso soluciones personalizadas mediante VBA. En este artículo, exploraremos varios métodos prácticos para contar el número de valores únicos en un rango según uno o varios criterios, con instrucciones paso a paso y consejos útiles.
Contar el número de valores únicos en un rango en función de un criterio
Contar el número de valores únicos en un rango en función de dos fechas dadas
Contar el número de valores únicos en un rango en función de dos criterios
Contar el número de valores únicos en un rango en función de tres criterios
Contar el número de valores únicos en un rango con código VBA (para casos complejos o automatizados)
Contar el número de valores únicos en un rango en función de un criterio
Consideremos un caso habitual: desea contar cuántos productos distintos vendió Tom. Este método es ideal cuando trabaja con un conjunto de datos sencillo y necesita evaluar la unicidad según una única condición, como los registros de ventas de una persona. Es directo, aunque requiere un uso cuidadoso de fórmulas matriciales.
Para este escenario, introduzca la siguiente fórmula en una celda vacía (por ejemplo, la celda G2):
=SUM(IF(«Tom»=$C$2:$C$20,1/(COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20)),0))
Después de escribir la fórmula, pulse Ctrl + Mayús + Entrar (no solo Entrar) para confirmarla como fórmula matricial. Aparecerán llaves alrededor de la fórmula en la Barra de fórmulas y verá inmediatamente el resultado, como se muestra a continuación:
Nota:
- «Tom» es la condición que desea usar para filtrar los resultados. Puede reemplazar «Tom» por una referencia a otra celda (por ejemplo, $F$2) si busca mayor flexibilidad.
- $C$2:$C$20 contiene los nombres de los vendedores que se evaluarán.
- $A$2:$A$20 es la columna de productos para la que desea obtener conteos únicos.
- Si su rango de datos cambia, recuerde ajustar las referencias en consecuencia.
Consejo: Si utiliza Excel 365 o Excel 2019 y versiones posteriores, puede probar a usar las funciones UNIQUE y FILTER para obtener fórmulas más sencillas.
Si aparecen errores #¡DIV/0!, revise detenidamente los criterios y asegúrese de que sus rangos tienen la misma longitud.
Contar el número de valores únicos en un rango en función de dos fechas dadas
Cuando necesite encontrar el número de elementos únicos dentro de un rango de fechas específico —por ejemplo, todos los productos únicos vendidos entre el 1/9/2016 y el 30/9/2016—, puede aplicar este enfoque. Resulta especialmente útil para analizar tendencias de datos en períodos determinados, como mensuales, trimestrales o rangos de fechas personalizados. No obstante, asegúrese de que el formato de las fechas coincida con los valores de fecha de su hoja de cálculo.
Coloque la siguiente fórmula en una celda vacía donde desee mostrar el resultado:
=SUM(IF($D$2:$D$20=DATE(2016,9,1)),1/COUNTIFS( $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1))),0)
Pulse Ctrl + Mayús + Entrar tras introducir la fórmula para ejecutarla como fórmula matricial. La siguiente captura de pantalla muestra el resultado:
Nota:
- 2016,9,1 y 2016,9,30 son las fechas de inicio y fin. Puede modificarlas según sus necesidades o incluso usar referencias de celda para crear filtros de fecha dinámicos.
- $D$2:$D$20 contiene las fechas que se van a verificar.
- $A$2:$A$20 es nuevamente la columna de artículos o productos cuyos valores únicos desea contar.
- Asegúrese de que sus fechas estén almacenadas como fechas válidas de Excel y no como cadenas de texto. Si el resultado no es el esperado, verifique el formato de las fechas y los rangos.
Consejo: Utilice FECHA(año, mes, día) para evitar problemas derivados del formato regional de las fechas. Si trabaja con rangos dinámicos, considere usar rangos con nombre para mayor claridad.
Contar el número de valores únicos en un rango en función de dos criterios
Supongamos que desea analizar únicamente los productos que Tom vendió en septiembre, combinando el nombre y un Rango de fechas en su recuento único. Este escenario es habitual en revisiones de rendimiento periódicas o análisis segmentados. A medida que aumentan sus criterios, la fórmula se vuelve más compleja y la atención a la precisión de los datos resulta aún más importante.
Introduzca la fórmula siguiente en cualquier celda vacía, como H2:
=SUM(IF((«Tom»=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))),1/COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1))),0)
Después de escribir la fórmula, confírmela con Ctrl + Mayús + Entrar. Verá inmediatamente el recuento único; consulte la siguiente ilustración:
Notas:
- «Tom» es el criterio de nombre, mientras que «2016,9,1» y «2016,9,30» definen los límites del rango de fechas. Ajústelos según sus necesidades o conviértalos en dinámicos mediante referencias de celda.
- $C$2:$C$20 es la columna del personal (u otro criterio principal); $D$2:$D$20 es la columna de fechas; y $A$2:$A$20 contiene los elementos únicos que se contarán.
- Todos los rangos deben tener la misma longitud para prevenir errores.
Si desea aplicar condiciones «O» —por ejemplo, contar los productos únicos vendidos por Tom o en la región Sur—, puede usar la siguiente fórmula. Esto le permite definir criterios de búsqueda más amplios, aunque los resultados podrían solaparse si los datos cumplen ambos criterios simultáneamente.
=SUMA(--(FRECUENCIA(SI((«Tom»=$C$2:$C$20)+(«South»=$B$2:$B$20), COUNTIF($A$2:$A$20, "0))
No olvide pulsar Ctrl + Mayús + Entrar. Verá los resultados como se muestra a continuación:
Consejo: Al aplicar criterios OR, ten en cuenta la posible doble contabilización si un mismo registro cumple ambas condiciones. En conjuntos de datos grandes, el rendimiento podría verse afectado.
Contar el número de valores únicos en un rango según tres criterios
A veces, su análisis puede requerir tres o más condiciones, como identificar los productos únicos vendidos por Tom en septiembre exclusivamente en la región Norte. Esta situación es habitual en el análisis de datos multidimensionales para generar informes u obtener información comercial específica. La gestión cuidadosa de las referencias es esencial al aplicar esta lógica compuesta.
Coloque esta fórmula matricial en una celda vacía (por ejemplo, I2):
=SUM(IF((«Tom»=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))*(«North»=$B$2:$B$20),1/COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1), $B$2:$B$20, "North")),0)
Pulse Ctrl + Mayús + Entrar para finalizar. Este es un resultado de ejemplo a modo de referencia:
Para condiciones avanzadas, verifique que todos los rangos sean coherentes y que los tipos de datos (por ejemplo, fecha y texto) sean correctos. Las desalineaciones pueden provocar errores o resultados engañosos.
Consejos:
- Si experimenta problemas de rendimiento con conjuntos de datos grandes, considere dividir la fórmula o utilizar la solución Tabla Dinámica de Excel.
- Utilizar rangos con nombre o hacer referencia a celdas para todos los criterios mejora la claridad y reduce los errores en las fórmulas.
- Para usos frecuentes, considere guardar estas fórmulas en referencias de celda con nombre o en funciones personalizadas.
Contar el número de valores únicos en un rango con Tabla Dinámica (Recuento distinto, Excel 2013+)
Para usuarios de Excel 2013 o versiones posteriores, las tablas dinámicas ofrecen una alternativa interactiva y sin fórmulas para contar el número de valores únicos en un rango con uno o varios criterios. La función *Recuento distinto* le permite resumir y filtrar grandes conjuntos de datos de forma eficiente, lo que hace que este método sea especialmente adecuado para entornos dinámicos basados en informes. No obstante, tenga en cuenta que las versiones anteriores de Excel no admiten la función *Recuento distinto* en tablas dinámicas.
Cómo usar este método:
- Seleccione su conjunto de datos y vaya a Insertar > Tabla dinámica.
- En el cuadro de diálogo Crear tabla dinámica, elija dónde colocar la tabla dinámica, marque la casilla "Agregar estos datos al modelo de datos" y, a continuación, haga clic en Aceptar.
- Arrastre el campo cuyos valores únicos desee contar (por ejemplo, Producto) al área Valores. De forma predeterminada, aparecerá como «Recuento de...».
- Haga clic en el campo del área Valores y seleccione Configuración de valor.
- En el cuadro de diálogo emergente, desplácese hacia abajo y seleccione Recuento distintos. (Esta opción solo está disponible en Excel 2013 o versiones posteriores y aparece cuando la tabla dinámica se crea con la opción «Agregar estos datos al modelo de datos» activada.)
- Añada sus campos de criterios (por ejemplo, Vendedor, Región, Fecha) a las áreas Filtros o Filas/Columnas para aplicar condiciones individuales o múltiples.
- Su tabla dinámica mostrará ahora el recuento único de valores, filtrado según los criterios seleccionados.
Ventajas: Muy visual, fácil de ajustar los filtros sin necesidad de editar fórmulas y perfecto para informes interactivos.
Limitaciones: No está disponible en Excel 2010 ni en versiones anteriores; para añadir nuevos datos, debes actualizar manualmente la tabla dinámica.
Consejo práctico: Asegúrese siempre de que los datos de origen no contengan duplicados dentro del mismo registro, a menos que estén previstos. Si no encuentra la opción «Recuento distinto», vuelva a crear la tabla dinámica y active la opción «Agregar estos datos al modelo de datos».
Contar el número de valores únicos en un rango con código VBA (para casos complejos o automatizados)
A veces puede necesitar contar el número de valores únicos en un rango según distintos criterios de forma automática, especialmente al trabajar con conjuntos de datos muy grandes o cuando repite el análisis con frecuencia. En estos casos, una macro de VBA es ideal, ya que puede procesar rápidamente diversas lógicas —incluido el filtrado con múltiples condiciones— sin intervención manual tras la configuración inicial. No obstante, VBA es más avanzado que las funciones habituales de Excel, por lo que resulta más adecuado para usuarios familiarizados con macros o con necesidades analíticas continuadas.
Pasos de operación:
- Pulse Alt + F11 para abrir el editor de VBA. En el editor, seleccione Insertar > Módulo para crear un nuevo módulo.
- Copie y pegue el siguiente código VBA en el módulo:
Sub CountUniqueWithCriteria()
Dim DataRange As Range
Dim CriteriaRange As Range
Dim CriteriaValue As Variant
Dim Dict As Object
Dim i As Long
Dim UniqueCount As Long
Dim ResultCell As Range
Set Dict = CreateObject("Scripting.Dictionary")
' Prompt for range settings
Set DataRange = Application.InputBox("Select data range (items to count):", "KutoolsforExcel", Type:=8)
Set CriteriaRange = Application.InputBox("Select criteria range (e.g. Salesperson):", "KutoolsforExcel", Type:=8)
CriteriaValue = Application.InputBox("Enter criteria value:", "KutoolsforExcel", "", Type:=2)
Set ResultCell = Application.InputBox("Select cell for result output:", "KutoolsforExcel", Type:=8)
On Error Resume Next
For i = 1 To DataRange.Rows.Count
If CriteriaRange.Cells(i, 1).Value = CriteriaValue Then
If Not Dict.Exists(DataRange.Cells(i, 1).Value) Then
Dict.Add DataRange.Cells(i, 1).Value, 1
End If
End If
Next i
UniqueCount = Dict.Count
ResultCell.Value = UniqueCount
MsgBox "Unique count for '" & CriteriaValue & "': " & UniqueCount, vbInformation, "KutoolsforExcel"
End Sub- Cierre el editor de VBA y regrese a su hoja de cálculo. Pulse Alt + F8, seleccione CountUniqueWithCriteria y ejecute la macro.
- Siga las indicaciones para definir los rangos y criterios según sus datos. El resultado se mostrará tanto en la celda que seleccione como en un cuadro de mensaje.
Explicación de parámetros y notas:
- Actualmente, esta macro está configurada para un único criterio. Para ampliarla a varios criterios, modifique la lógica
If ... Thendentro del bucle. - Guarde siempre su libro antes de ejecutar macros, ya que los cambios no se pueden deshacer.
- Active las macros en la configuración de Excel si encuentra errores al ejecutarlas.
- Este método es ideal para conjuntos de datos más grandes o que se actualizan con frecuencia, ya que las fórmulas manuales resultarían engorrosas.
Beneficios: Altamente personalizable y automatizable, gestiona con eficacia conjuntos de datos grandes y en constante cambio. Ideal para flujos de trabajo avanzados o repetitivos.
Inconvenientes: Requiere permisos para macros, y los principiantes pueden necesitar un tiempo para familiarizarse con las operaciones de VBA.
Al trabajar con recuentos de valores únicos basados en criterios, asegúrese siempre de verificar sus referencias de rango y de que todas las columnas de criterios tengan exactamente la misma extensión. Los rangos desalineados son una causa frecuente de errores o resultados inexactos. Si sus fórmulas arrojan resultados inesperados, revise la presencia de problemas ocultos de formato o celdas vacías. En escenarios donde el rendimiento es crítico, las tablas dinámicas y VBA ofrecen alternativas sólidas a las fórmulas matriciales. Elija la solución que mejor se ajuste a su nivel de comodidad y a la complejidad de su conjunto de datos. Recuerde que Kutools para Excel incluye utilidades y accesos directos adicionales que pueden agilizar muchas de estas tareas, mejorando significativamente la eficiencia en libros de trabajo complejos.
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