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.

La función SUBTOTALES con criterios.

Como ya sabemos, la función SUBTOTALES nos permite realizar cálculos con once operaciones distintas sin tomar en cuenta celdas ocultas. Esto es muy práctico cuando usamos Autofiltro

El resultado de la fórmula refleja sólo los valores de las filas visibles. Un lector me pregunta si se puede hacer el cálculo de tal manera que SUBTOTALES sólo tome en cuenta los valores positivos (o negativos, o distintos de cero, es decir, aplicando algún criterio al cálculo).

Una forma de hacerlo sería agregando una columna auxiliar y luego aplicar el filtro a la columna del criterio y a la columna auxiliar. Por ejemplo

También se puede hacer con fórmulas, que es lo que vamos a mostrar en esta nota. La ventaja de hacerlo con fórmulas es que podemos mostrar distintos resultados, por ejemplo el total de los montos positivos y el total de los negativos, en una misma vista de la hoja.

Supongamos que queremos ver el total de los montos positivos en la celda B1 y el total de los negativos en D1

La celda B1 contiene la fórmula

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1))*($B$4:$B$14>0))


La celda D1 contiene la fórmula

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1))*($B$4:$B$14<0))

Al filtrar por el criterio “a”, por ejemplo, veremos

subtcrit04

Analicemos la fórmula:

DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1) crea una matriz que contiene los valores del rango B4:B14

La expresión ($B$4:$B$14<0) crea una martiz de valores FALSO o VERDADERO

Al multiplicar ambas expresiones entre sí obtenemos un matriz donde los valores negativos han sido remplazados por ceros

Usamos estas expresiones como argumentos de la función SUBTOTALES con el operador 9 (suma).

La función SUMAPRODUCTO opera en forma matricial, por lo que nos permite realizar todas estas operaciones en una única celda.

Para calcular el subtotal de los valores negativos usamos ($B$4:$B$14<0); si quisiéramos hacer cálculos que no incluya ceros usaríamos ($B$4:$B$14<>0).