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.

Nueva versión de Excel - Excel 2013

Supongo que para muchos de mis lectores éstas son noticias de ayer: Microsoft ha puesto a disposición de los usuarios la nueva versión del paquete Office, el Office 365.

Como es de esperar el paquete incluye la nueva versión de Excel – Excel 2013. Microsoft ofrece también la posibilidad de descargar el paquete en sus distintas configuraciones (tomar en cuenta que se requiere Windows 7 o superior).

Para descargar el paquete hay que abrir una cuenta en Office 365.

No he probado aun la nueva versión, lo que iré haciendo en los momentos libres en las próximas semanas.