Contar valores únicos en un rango con dos criterios.

Hace ya cinco años atrás publique una nota sobre cómo contar valores únicos en un rango. Siguiendo con el tema, un lector me consulta cómo contar los valores únicos en el rango pero con más de un criterio.
Por ejemplo, en nuestro ejemplo, contar vendedores por región. Digamos que tenemos una tabla de vendedores por región, pero por algún motivo hay líneas duplicadas

Podemos ver que el agente 3 y el agente 5 aparecen dos veces en la zona Norte. Tenemos seis líneas para la zona Norte pero sólo cuatro agentes.

Mi propuesta para este tipo de situaciones es usar tablas dinámicas (mi herramienta preferida, como ya habrán notado mis lectores habituales).

Empezamos por agregar una columna auxiliar a la tabla de datos para identificar el número de aparición del vendedor (suponemos que un vendedor no puede aparecer en dos zonas distintas)

En la columna “aux” usamos la fórmula =CONTAR.SI($B$2:B2,B2).

Luego creamos una tabla dinámica poniendo los campos Región y Agente en el área de filas, el campo “aux” como filtro del informe y el campo “Agente” también en el área de valores. Como éste no es un valor numérico, Excel usa CUENTA para totalizar los valores

Como puede verse, filtramos el informe poniendo el valor “1” en el campo “aux”.
Otra variación es usar la tabla dinámica como “motor de cálculo” y usar la función IMPORTARDATOSDINAMICOS para extraer el valor requerido.

Sencillamente ponemos en la celda C3 “=” y apuntamos a la celda correspondiente en la tabla dinámica; Excel crea la fórmula

=IMPORTARDATOSDINAMICOS("Agente",Hoja4!$A$3,"Region","Este")

Ahora remplazamos “Este” en la función por una referencia a la celda C2

Cada vez que remplazamos el valor de C2 por otra región, la fórmula se actualiza. El cuaderno que aparece abajo es interactivo.

Cuando CONTAR.SI da resultados erróneos

Si necesitamos contar condicionalmente, nuestra primera elección es la función CONTAR.SI. Pero en ciertas situaciones, CONTAR.SI puede dar resultados equivocados.

Veamos este ejemplo “basado en un hecho real” (uno de mis clientes). Supongamos una empresa que usa códigos alfanuméricos para identificar sus productos. Con este sistema, 12345 y 012345 son dos productos distintos. Ahora queremos contar cuántas veces se repite un código en una lista, por ejemplo en esta

Fácil, ¿no?
A simple viste vemos que 12345 se repite tres veces y 012345 se repite dos veces. Veamos que pasa con CONTAR.SI

Usamos =CONTAR.SI($A$2:$A$6,C2) en D2 y =CONTAR.SI($A$2:$A$6,C3) en D3 y en ambos casos el resultado es 5!

Esto se debe a que CONTAR.SI evalúa todo valor como número, de manera que 012345 es evaluado como número y por lo tanto es considerado idéntico a 12345.

La solución es usar SUMAPRODUCTO, de esta manera

=SUMAPRODUCTO(--($A$2:$A$6=C2))

SUMAPRODUCTO genera un vector de con los valores de la celda del rango

y los compara con el valor de la condición, generando un vector de valores VERDDERO t FALSO

Finalmente usamos el doble “--“ para convertir los VERDADERO en 1 y los FALSO en 0.

Quien no se sienta cómodo con el uso del doble “-“ puede multiplicar el vector por 1

=SUMAPRODUCTO(($A$2:$A$6=C2)*1)

Listas desplegables con contenido condicional.

Supongamos una lista de artículos que contiene dos columnas: el código del artículo y el estatus (activo o inactivo). ¿Cómo hacemos para crear una lista desplegable que muestre solamente los artículos activos?

Con anterioridad a la introducción de las “listas” en Excel 2003, luego rebautizadas “tablas” en Excel 2007/10, lo hubiera hecho con una macro. Posiblemente extrayendo los valores que cumplen con el criterio usando Filtro Avanzado y creando un nombre que se refiera a ese rango dinámicamente. Finalmente, usaríamos una macro para automatizar el proceso.

Pero podemos aprovechar las funcionalidades de las tablas para crear un modelo sin macros.

Supongamos que esta es nuestra lista

Vamos a usar una variante de la técnica que mostré en la nota que trató sobre cómo agregar valores únicos a una lista desplegable.

Empezamos por convertir la matriz en “tabla”

Ahora agregamos una columna auxiliar (“Aux1”) con esta fórmula

=SI(B2="Activo",A2,"")

El próximo paso es crear la columna “Aux2” que contiene la fórmula

=SI(CELDA("contenido",C2)="","",FILA())

Lo que hace esta fórmula es evaluar si el resultado de la fórmula es vacío (la celda no está vacía; contiene una fórmula); en caso afirmativo da un resultado vacío, en casi negativo muestra el número de código del artículo.

En esta columna obtenemos un número de orden para los artículos con estatus “activo”.

Finalmente creamos la columna auxiliar “Lista” donde aparecen los artículos activos ordenados por orden de aparición en la tabla. Esto lo hacemos con la fórmula

=INDICE($C$2:$C$15,COINCIDIR(K.ESIMO.MENOR($D$2:$D$15,FILA()-1),$D$2:$D$15))

Como estamos trabajando con una tabla, al agregar un nuevo artículo todas las fórmulas son copiadas automáticamente.

Lo último que nos queda por hacer es crear una nombre que se refiera dinámicamente al rango de la columna Lista en la tabla que no contiene valores #NUM!.

En el administrador de nombres definimos el nombre “ListaArticulos” que se refiere a la fórmula

=DESREF(articulos!$E$2,0,0,SUMAPRODUCTO(--NO(ESERROR(Tabla1[Lista]))),1)

Ahora podemos crear la lista desplegable usando Validación de Datos—Lista

Este video muestra como se adapta el contenido de la lista desplegable a los cambios en la tabla



El archivo con el ejemplo se puede descargar aquí.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.