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