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

La función DIA.LAB (WORKDAYS) mejorada

En la nota anterior mencionamos que la función DIA.LAB (WORKDAY) de Excel adolece del mismo problema como su hermana mayor DIAS.LAB (NETWORKDAYS). Si bien ambas aceptan un rango de fechas como feriados, los días sábado y domingo son considerados fin de semana y por lo tanto no laborales.
No hay forma de cambiar esta definición y si nos ha tocado vivir en alguna parte del mundo donde el fin de semana no es el sábado y el domingo (como en gran parte de los países musulmanes e Israel), estas funciones dan resultados inexactos
.
Ayer mostramos dos soluciones a este problema en lo que respecta a DIAS.LAB. Las fórmulas matriciales propuestas por Chip Pearson y la una UDF (función definida por el usuario) del autor de estas líneas.

Hoy veremos cómo resolver el problema opuesto al planteado ayer: dada la fecha inicial y la cantidad de días hábiles necesarios para completar una misión, cuál será la fecha final.

Si vivimos en cualquier país de Occidente y los días de labor corren de lunes a viernes, podemos usar la función DIA.LAB nativa de Excel. Por ejemplo, si queremos saber cuál será la fecha después de cinco días hábiles empezando el 06/04/2009, DIA.LAB nos da estas respuestas





La celda C4 da la respuesta sin tomar en cuenta los feriados; la celda C5 es la fecha calculada tomando en cuenta los feriados (en España).

Pero ¿qué pasa si en nuestro proyecto no se trabaja los martes y jueves en lugar de los sábados y domingos?

Para solucionar este problema usaremos una función definida por el usurario (UDF), FechaFinal

Function FechaFinal(fecha_inicial As Date, _
dias_habiles As Long, _
diasIncluidos As Range, _
Optional feriados As Range)

'JLD Excel blog en Castellano
'Desarrollada por Jorge Dunkelman
'Junio 2009

Dim daysCounter As Long, tmpDays As Date, dateCheck

tmpDays = fecha_inicial + 1
daysCounter = 1

On Error GoTo ErrorHandler


If feriados Is Nothing Then
Do
tmpDays = tmpDays + 1
dateCheck = " "
dateCheck = WorksheetFunction.Match(Weekday(tmpDays, vbMonday), diasIncluidos, 0)
If IsNumeric(dateCheck) Then
daysCounter = daysCounter + 1
End If

Loop Until daysCounter = dias_habiles

Else

Do
tmpDays = tmpDays + 1
dateCheck = " "
dateCheck = WorksheetFunction.Match(CLng(tmpDays), feriados, 0)
If Not IsNumeric(dateCheck) Then
dateCheck = " "
dateCheck = WorksheetFunction.Match(Weekday(tmpDays, vbMonday), diasIncluidos, 0)
If IsNumeric(dateCheck) Then
daysCounter = daysCounter + 1
End If
End If
Loop Until daysCounter = dias_habiles
End If

FechaFinal = tmpDays

Exit Function

ErrorHandler:
dateCheck = "A"
Resume Next


End Function

Los argumentos de la función FechaFinal son


Fecha_Inicial: la fecha de incio del proyecto escrita con formato de fecha o una referencia a una celda que contiene esta fecha

Dias_Habiles: un número entero que indica la cantidad de días hábiles a contar desde elprimer día del proyecto

Dias_Incluidos: los días de la semana a tomar en cuenta (1 = lunes, 2 = martes, etc.)

Feriados (opcional): un rango que contiene las fechas de los feriados relevantes.

Los resultados de nuestro ejemplo son:





Technorati Tags: MS Excel