Lista desplegable con ajuste automático en Excel

Esta es una variante de la técnica que mostré en la nota sobre cómo agregar valores únicos en una lista desplegable con validación de datos.

Un lector me presenta el siguiente caso: ha creado una lista desplegable que muestra una lista de facturas. ¿Cómo hacer para que las facturas abonadas vayan desapareciendo de la lista desplegable?

Supongamos que esta es la lista de las facturas



En el rango B6:B19 aparecen los números de las facturas y en el rango C6:C19 si han sido pagadas (señalado con la palabra “pagada).

En nuestro ejemplo, queremos crear una lista desplegable que sólo muestre aquellas facturas que no han sido pagadas



Por supuesto, queremos que esta lista sea dinámica, es decir que cuando pongamos “pagada” al lado de una factura, ésta desaparezca de la lista

.
La solución consiste en crear dos columnas auxiliares y una tercera que será la que contenga los valores válidos para la lista.

Empezamos por crear la primer columna auxiliar en el rango E6:E19.



En la celda E6 ponemos esta fórmula

=SI(LARGO(C6)=0,FILA(),"")

y la copiamos a todo el rango.

Esta columna nos da un “número de orden” que es de hecho un número entero único (el número de fila) que identifica a las facturas que aún no han sido pagadas.

Ahora agregamos una segunda columna auxiliar en el rango F6:F19 con esta fórmula

=INDICE($B$6:$B$19,COINCIDIR(K.ESIMO.MENOR($E$6:$E$19,FILA()-5),$E$6:$E$19))

Como puede verse, sólo las facturas pendientes aparecen en la lista.

Tenemos un pequeño problema y es que cuando una factura ha sido pagada el valor de la fórmula es #NUM!.

Si usáramos el rango F6:F19 para crear la lista de los valores, también el error #NUM! aparecería en ella lo cual es poco elegante.

Una de las soluciones es crear una tercera columna en el rango G6:G19 con la fórmula

=SI(ESERROR(F6),"",F6)



Este rango es el que usamos para definir la lista de valores válidos. Creamos el nombre "lista"

Ahora usamos validación de datos para crear la lista desplegable en la celda C2

Si quitamos la palabra “pagada” en alguna celda, la factura correspondiente aparecerá automáticamente en la lista; si agregamos “pagada”, la factura desaparecerá.

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.

Technorati Tags: MS Excel

Tamaño de cuadernos Excel con tablas dinámicas

Una de las mejores y más útiles funcionalidades de Excel son las tablas dinámicas. Si bien podemos generar tablas dinámicas basándonos en datos externos, por lo general los datos estarán en una de las hojas del cuaderno que contiene la tabla dinámica.

Si queremos distribuir nuestro archivo y queremos reducir su tamaño podemos sencillamente eliminar la hoja que contiene los datos. Esto sólo afectará la posibilidad de agregar o borrar datos de la base de datos, pero todas las otras funcionalidades seguirán vigentes.

Para demostrarlo construimos una base de datos de las ventas de una empresa imaginaria. Esta base de datos contiene las ventas de todos los días desde el 1ro. de enero de 1970 hasta el 30 de junio de 2009 (14423 días/líneas en la tabla). El tamaño del archivo, antes de construir la tabla dinámica es 1.2 MB.

Creamos una tabla dinámica que agrupe las ventas por año y trimestre



El tamaño de nuestro archivo ha crecido y es ahora 1.63 MB.

Ahora borramos la hoja que contiene los datos y guardamos el cuaderno. El tamaño de nuestro archivo se ha reducido dramáticamente a 455 KB

La funcionalidad de la tabla dinámica sigue intacta. Por ejemplo, podemos arrastrar el campo de los trimestres del área de columnas al área de filas






A pesar de haber borrado los datos en que se basa la tabla dinámica, éstos no se han perdido. Para recuperarlos todos lo que tenemos que hacer es un doble clic en la celda de la tabla dinámica que contiene el total general




Esta acción genera una nueva hoja con todos los datos tal como aparecían en la hoja que borramos previamente.

Esto es posible dado que cuando creamos una tabla dinámica, Excel “fotografía” todos los datos y los guarda en una memoria especial llamada Cache. Este método hace que las tablas dinámicas sean tan eficientes en la velocidad de cálculo.

Technorati Tags: MS Excel

Cálculos frecuencias en Excel usando tablas dinámicas

Un lector me consulta cómo crear un reporte que muestre cuántos de los artículos de un inventario tienen una antigüedad de hasta 3 meses, cuántos entre 3 y 6 meses, cuántos entre 6 y 9 y así sucesivamente.

Hay varias formas de hacer esto, pero cuando tenemos que enfrentarnos con listas de muchos artículos, digamos centenas o miles, la herramienta más eficiente son las tablas dinámicas.

De hecho vamos a mostrar cómo crear una tabla de frecuencias usando tablas dinámicas. EN nuestro caso lo aplicaremos al ejemplo del inventario.

Nuestro inventario se ve así

EL primer paso es agregar un campo (columna) donde calculamos la antigüedad de cada artículo en relación a la fecha corriente. Usamos la función SIFECHA de esta manera

=SIFECHA(C2,HOY(),"m")

Luego construimos una tabla dinámica, preferentemente en una hoja separada.

Ahora arrastramos el campo Antigüedad al área de campos de fila (si, a pesar de que Antigüedad es un campo de datos numéricos)

Luego arrastramos los campos Artículo y Descripción al área de filas.

El campo Artículo lo volvemos a arrastrar pero esta vez al área de datos. Inmediatamente después quitamos los subtotales de todos los campos.

Nuestra tabla dinámica debe verse así

Seleccionamos la celda A4 (Antigüedad) y apretamos el icono Ocultar detalle del asistente de Tablas Dinámicas



El resultado es



Ahora usamos Agrupar para crear los intervalos. En el formulario de agrupar ponemos 3 (o el tamaño de intervalo deseado) en la casilla Por



Volvemos a elegir la celda A4 (Antigüedad) y nuevamente apretamos Ocultar Detalle.

El resultado es una tabla dinámica agrupada por intervalos de 3 meses, que totaliza el total de artículos en cada intervalo (0-2 meses, 3-5 meses, etc).

Algunas observaciones:

# - Si queremos que el primer intervalo sea 1-3, deberemos modificar levemente la fórmula que calcula la antigüedad

=SIFECHA(C2,HOY(),"m")+1

Una vez modificada la fórmula, actualizamos la tabla dinámica

# - La función HOY() que usamos en la fórmula de antigüedad es volátil, lo cual puede influir negatívamente en el tiempo de recálculo en hojas con muchos datos. En lugar de HOY() podemos poner la fecha en relación a la cual queremos calcular la antigüedad en una celda fuera del rango de la lista o en un nombre.

Technorati Tags: MS Excel