JLD en Castellano – balance del cuarto año

El tiempo vuela (o como diría mi profesora de Latín, tempus fugit). En unos días concluirá esta primera decena del siglo 21 y ha llegado el momento de presentar las estadísticas del blog a mis socios, sus lectores.

A pesar del título de la nota, sólo llevo estadísticas completas desde el año 2007 (gracias a los servicios de Statcounter), por lo que estaremos analizando estos últimos tres años.

En el 2009 470,000 lectores han visto casi 950,000 páginas. Esto significa un crecimiento de algo más del 11% en lectores y del 4% en las páginas vistas.

El blog ha seguido creciendo, conteniendo ahora 418 entradas que han merecido algo más de 3400 comentarios (incluyendo mis respuestas).

Estas estadísticas nos dan poca información así que decidí, siguiendo la corriente de mis últimas notas, organizar los datos en un dashboard

El gráfico en la sección izquierda del tablero nos permite comparar datos mensuales por año

La lista desplegable en el encabezado nos permite visualizar distintos tipos de datos y analizar sus tendencias.

Así vemos que el número de páginas vistas en el 2009 se ha mantenido estable, mientras que en el 2007 el crecimiento fue acelerado y la desaceleración empezó en el 2008.

Vemos que el número de visitantes se ha comportado de la misma manera

Otro efecto notable es que la cantidad de lectores y páginas vistas tiende a descender en los meses de julio y agosto (el verano en Europa), alcanza el máximo del año en Octubre para caer “en picada” en Noviembre y Diciembre.

Para agregar más información a partir de los mismos datos, he agregado cuatro gráficos, uno para cada tipo de datos, que muestran las tendencias de los tres años

He agregado líneas de tendencia para hacerlas más evidentes. En todo los casos vemos que la tendencia es ascendente pero también podemos observar que los últimos meses están por debajo de la línea de tendencia.
Podemos ver que el 2009 se diferencia en su comportamiento de los años anteriores y que ningún mes logro superar las cifras del 2008. Espero que esta tendencia se revierta en el año entrante.
Donde ha habido un gran crecimiento es en la cantidad de lectores del feed (de 30 en enero del 2007 a 1307 en diciembre del 2009).

Sólo me queda por desear a mis lectores lo mejor para el 2010 y agradecer a todos por el apoyo, por los halagos (a veces inmerecidos) y también por las (por lo general merecidas) observaciones.

Salud!

pd.: quien haya descargado alguno de los tableros anteriores (dashboard-1 o dashboard-2) y quiera recibir el archivo del tablero de esta nota sólo tiene enviarme un mail usando la misma dirección usada para la descarga.

Evitar borrar Validación de datos en Excel al pegar datos copiados

Empiezo con una aclaración: por primera vez he eliminado una entrada luego de haberla publicado. Se trata de la entrada donde mostraba un código para evitar el problema de la destrucción de la validación de datos al pegar un valor copiado de otra celda. Había demasiadas circunstancias que provocaban que el código no funcionara. Mea culpa!

Volviendo sobre el tema, una de las debilidades de Validación de Datos es que si un usuario pega un valor en una celda del rango validado en lugar de ingresarlo manualmente, las definiciones de la validación quedan eliminadas.

La única forma de evitar estas situaciones es usar macros, más precisamente eventos.



Buscando en la Internet encontré, entre otras, una solución propuesta por John Walkenbach. El problema con esta solución es que si el usuario en lugar de pegar el valor usa la opción Pegado Especial-Valores (o Fórmulas), el evento no responde y el valor es aceptado.

El código que propongo usa una parte del código de Walkenbach, para el caso que el usuario use la opción de pegado común, y parte de mi código para el caso que use pegar-valores, pegar-fórmulas, Insertar o arrastre el valor a la celda.

En el módulo de Vba de la hoja pegamos estos códigos

Private Sub Worksheet_Change(ByVal Target As Range)
'   rutina desarrollada por Jorge Dunkelman - JLD Excel Blog
'   parte del codigo tomado de la nota de John Walkenbach http://www.j-walk.com/ss/excel/tips/tip98.htm

    Dim rngValid As Range, cell As Range
    Dim Msg As String
    Dim codeValid As Variant

    Set rngValid = Range("rngValidado")

    On Error Resume Next

    If Not HasValidation(rngValid) Then

         Application.Undo
         MsgBox "Valor no válido", vbCritical
         Application.EnableEvents = False
         Target.ClearContents
         Application.EnableEvents = True
     End If

    For Each cell In Target
        If Union(cell, rngValid).Address = rngValid.Address Then
            codeValid = ActiveCell.Validation.Value
            If codeValid = True Then
                Exit Sub
            Else
                MsgBox "Valor no válido", vbCritical
                Application.EnableEvents = False
                cell.ClearContents
                cell.Activate
                Application.EnableEvents = True
            End If
        End If
    
    Next cell

    

End Sub

Private Function HasValidation(r) As Boolean
    Dim x

    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function


Nótese que estamos usando un evento Worksheet_Change y una función UDF.

Al rango B3:B12 de la hoja hemos aplicado validación de datos con la opción Lista. Los valores permitidos son a1, a2, a3 y a4.

Este video muestra cómo funciona la macro




El archivo con el ejemplo y el código puede descargarse aquí.

Construir un tablero de comandos (Dashboard) en Excel – cuarta nota

En esta cuarta nota mostraremos el uso de otra herramienta importante para la construcción de tableros de comandos: la función IMPORTARDATOSDINAMICOS.

Esta función nos permite extraer datos de una tabla dinámica. La importancia de esta herramienta reside en el hecho que las tablas dinámicas son el método más eficiente de Excel para totalizar y analizar datos de una base de datos.

Vamos a mostrar un ejemplo basándonos en los datos del ejemplo de las notas anteriores.

El cuaderno con el ejemplo se puede descargar aquí.

Después de totalizar los datos en esta tabla dinámica



donde hemos agrupado los datos por zonas, años y trimestres.

El tablero de comandos que construiremos nos permitirá extraer datos dinámicamente para realizar comparaciones por años, trimestres y zonas




En este video pueden apreciar el funcionamiento del dashboard



El cuaderno contiene cuatro hojas






Reporte: contiene el tablero de comandos

td: donde reside la tabla dinámica que totaliza nuestros datos; esta hoja es el “motor” del modelo

BD: la base de datos

definiciones: esta hoja contiene los valores de dos rangos nominados (nombres) que usamos en las listas desplegables del dashboard.

Nuestro tablero de comandos se divide en dos zonas: la zona de los parámetros



donde establecemos los parámetros de los datos a comparar, y la zona de los resultados



Las columnas A y B están ocultas. Estas columnas nos sirven para manejar rangos auxiliares, cuyo objetivo mostraremos más adelante.

El rango F4:H6 es donde ponemos los parámetros del análisis, eligiéndolos de listas desplegables cuyas definiciones pueden verse en la hoja “definiciones”.

Antes de poner la fórmula en la celda F10 nos aseguramos que el generador de la función IMPORTARDATOS DINAMICOS esté activo.

En Excel 2007 seleccionamos una celda de la tabla dinámica y en la cinta de Opciones de las herramientas de tablas dinámicas abrimos la pestaña Tabla Dinámica-Opciones



En Excel Clásico (97-2003) encontramos esta opción en la barra de herramientas de las tablas dinámicas



Una vez que esta opción está activa creamos una referencia a la celda correspondiente en la tabla dinámica



Excel crea automáticamente la función con los argumentos relevantes

=IMPORTARDATOSDINAMICOS("Clientes ";td!$A$3;"Fecha";1;"Zona";"Este";"Años";2007)

Analicemos esta fórmula:

el “1” que aparece después de “Fecha” es la referencia al Trimestre 1 (el primer elemento del campo Fecha). Este elemento debe ser dinámico, es decir, cambiar de acuerdo a la elección del usuario.

“Este” define que zona hemos elegido. También este elemento debe cambiar dinámicamente.

“2007” es el otro elemento que deberá cambiar de acuerdo a la elección del usuario.

Estos elementos serán reemplazados de esta manera:

=IMPORTARDATOSDINAMICOS("Ventas ";td!$A$3;"Fecha";$A$5;"Zona";F6;"Años";F4)

$A$5 reemplaza el índice del elemento del campo fecha.

La celda A5 contiene la fórmula

=COINCIDIR(F5;Trimestre;0)

cuyo valor sirve como argumento en las funciones IMPORTARDATOS DINAMICOS con las que extraemos los datos



Lo mismo con B5 que contiene la fórmula =COINCIDIR(H5;Trimestre;0)



Los argumentos “Año” y “Zona” podemos pasarlos directamente de las celdas F4 y F6 donde el usuario hace su elección.
Hacemos lo mismo con el resto de las celdas en las filas 10 y 11. Con esto nuestro modelo es absolutamente dinámico.

Las celdas F12, H12 y J12 contienen simples fórmulas para calculas el cambio porcentualmente.

Finalmente agregamos los gráficos con técnicas como las que he mostrado en la nota anterior.

El cuaderno se puede descargar aquí.




Technorati Tags: MS Excel

Construir un tablero de comandos (Dashboard) en Excel – tercera nota

Un tablero de comandos (dasboard) contiene básicamente tablas de datos y gráficos. Una de las cuestiones importantes del diseño es cómo incluir estos elementos en el espacio limitado del que disponemos.
Al hablar de espacio limitado nos referimos a la necesidad de colocar toda la información en una “unidad de presentación” que puede ser el espacio visible de una pantalla o una hoja impresa, por lo general del tamaño A4.

Por lo general trataremos de acomodar todos los elementos del reporte en una vista de pantalla cuando se trate de dashboards dinámicos que son aquellos donde el usuario puede cambiar datos y obtener distintas vistas del informe (como en el ejemplo de las dos primeras notas).

Por otro lado hay no pocos gerentes que prefieren recibir informes impresos, lo que nos lleva a diseñarlos de manera que toda la información esté contenida en una hoja, o en unidades lógicas de una hoja.

Sea cual fuere nuestra situación necesitamos crear dos tipos de elementos: mini-gráficos y tablas de datos cuyas dimensiones sean independientes de las dimensiones de las celdas de la hoja.

Veamos un ejemplo de mini-gráfico. Supongamos esta tabla de ventas y ganancias por meses

Queremos crear dos mini-gráficos que pondremos al lado de la tabla: ventas y ganancias. Empezamos por seleccionar el rango A1:B11 y crear el gráfico de las ventas



Queremos reducir el tamaño del gráfico para encuadrarlo con la tabla de datos. Quitamos algunos elementos innecesarios como la leyenda, reducimos el gráfico al tamaño deseado y lo movemos a su ubicación al lado de la tabla. Hacemos lo mismo con las ganancias. Al final del proceso obtenemos este resultado (para obetener dos gráficos idénticos en tamaño, creamos el primero y luego lo copiamos cambiando las referencias de las series de datos)



El resultado es horrendo pero ahora veremos cómo mejorarlo. Borramos el título del gráfico, lo cual no permite recuperar “terreno” valioso.



EL próximo paso es reducir el tamaño de la fuente. Seleccionamos uno de los ejes y cambiamos el tamaño de la fuente a 7 por ejemplo. Luego elegimos otro eje y apretamos F4. Repetimos la operación con los ejes restantes.



Para ganar más espacio cambiamos el formato de los números del eje de las Y a miles usando este formato personalizado

###.###. "M";[Rojo](###.###.)"M";0 “M”

También podemos convertir los valores del eje de las X a los nombres de los meses, o mejor aún, sus iniciales (“ene” para enero, “feb” para febrero, etc.). Esto lo hacemos con el formato personalizado “mmm”

El resultado hasta ahora es el siguiente

Ahora necesitamos poner un título a cada gráfico, para lo cual vamos a aprovechar el espacio disponible entre el borde derecho de los gráficos y el de la columna G.

Creamos un cuadro de texto que coincida con el alto del gráfico donde ponemos el rótulo Ventas orientado verticalmente

El resultado final es



El otro tema a tratar es cómo acomodar tablas cuyas dimensiones son distintas de las de la hoja a de otra tabla con la cual queremos que coincida.

Por ejemplo, supongamos que queremos agregar una tabla que muestra para cada mes cuál es la sucursal que más vendió. Esta tabla tiene sólo dos columnas, mes y sucursal, pero queremos que su ancho coincida con el de la tabla de ventas.

Hay varias soluciones, algunas no recomendadas como usar “combinar y centrar”, por ejemplo. En esta nota mostraremos como usar la cámara fotográfica de Excel para este cometido.

Empezamos por crear la tabla en otra hoja

Seleccionamos el rango de la tabla, A1:B11, y pulsamos el icono de la cámara (en la imagen arriba pueden ver que he agregado el icono a la barra de herramientas de acceso rápido)

Volvemos a la hoja de dashboard y hacemos clic en el lugar donde queremos poner la tabla. Como es una imagen, podemos moverla y cambiar su tamaño independientemente de las dimensiones de la hoja.

En la imagen arriba podemos ver que se trata de una imagen ligada al rango de la tabla de la hoja 2 (en la barra de las fórmulas vemos la referencia al rango de la tabla). Esto significa que todo cambio en la tabla original se reflejará inmediatamente en la imagen.

La misma funcionalidad de la cámara podemos lograrla usando Pegar-Pegar como imagen-Pegar vínculos de imagen.

Technorati Tags: MS Excel

Gráficos cascada (Waterfall charts) con Excel.

El gráfico de tipo cascada (waterfall) es un tipo especial de gráfico de columnas flotantes. Según Wikipedia:

El gráfico de tipo cascada es una forma de visualización de datos que ayuda a determinar el efecto acumulado de valores positivos o negativos en una secuencia. El gráfico de cascada es también conocido como “Flying Bricks” debido a la suspensión aparente de las columnas (de ladrillo) en el aire.

Un ejemplo de este tipo de gráficos sería el siguiente



¿En qué situaciones queremos usar este tipo de gráfico? Cuando queremos mostrar cómo se ve afectado un valor inicial por valores intermedios hasta llegar al resultado final.

El gráfico del ejemplo muestra la influencia de cada sucursal en el cambio del total de ventas ocurrido entre 2008 y 2009.


Excel no cuenta con una plantilla para este tipo de gráficos así que tendremos que deberemos maniobrar un tanto con los datos originales.

Los datos de origen son:



Organizamos los datos en la siguiente tabla



Las fórmulas utilizadas en la tabla auxiliar son



Nuestro próximo paso es crear un gráfico de columnas apiladas, para lo cual seleccionamos la tabla auxiliar (el rango G1:L7 en nuestro ejemplo) y usamos insertar –columna apilada (Excel 2007)



Este el primer resultado




Ahora seleccionamos la serie “Corriente”



y la volvemos invisible abriendo el menú de formato de serie de datos y estableciendo “sin relleno” y “sin línea” para el borde




Quitamos la leyenda y cambiamos el color de la serie “Negativo” a rojo y el de la serie “Positivo” a verde



Ahora podemos agregar algunos detalles. Por ejemplo, podemos eliminar el espacio entre las columnas, lo que nos permite enfatizar la influencia de los cambios




También podemos usar Autoformas para enfatizar el sentido del cambio

Technorati Tags: MS Excel

Construir un tablero de comandos (Dashboard) en Excel – segunda nota

En la nota anterior sobre cómo crear un tablero de comandos (dashboard) con Excel dijimos que el primer paso es definir cuál es el objetivo, que información debe ser presentada y de qué manera. A partir de ahí tenemos que solucionar tres cuestiones: diseño, datos y mecanismo, temas que serán abordados en una próxima nota.

En esta entrada me centraré en la explicación del modelo de nuestro ejemplo mostrando los elementos más importantes y las fórmulas en uso.

Desde ya, la mejor forma de entenderlo y estudiarlo es descargar y “disecar” el archivo.

Para descargar el archivo hay que seguir este enlace.

El modelo cuenta con siete hojas





La hoja Dashboard contiene el tablero de comandos y es en la que el usuario interactúa. Esta hoja tiene dos columnas ocultas: A y B



A y B son columnas auxiliares que controlan los datos visibles en la tabla Detalle por Sucursal



En el rango B23:B32 ponemos la fórmula

=DERECHA(D23;LARGO(D23)-HALLAR(" ";D23))

cuyo cometido es extraer el número de sucursal.

En el rango A23:A32 ponemos la expresión

D23=0

Al elegir una de las zonas de la lista desplegable, sólo aparecen las sucursales pertenecientes a la zona. Esto hace que las celdas relevantes del rango A23:A32 den el resultado VERDADERO. Ese resultado controla el formato condicional de la tabla que es el responsable de ocultar las celdas de las sucursales que no pertenecen a la zona elegida



La hoja tiene tres listas desplegables. Las dos superiores, que controlan la fecha de comienzo y fin del período analizado, están hechas usando los controles de la barra de Formularios.



La lista de valores está controlado por el nombre tdPeriodos que es un rango dinámico basado en la tabla dinámica de la hoja “tdZonas” que analizaremos más adelante. Todos los nombres usados en el modelo están descritos en la hoja “Documentacion”.

La lista desplegable para elegir las zonas en la tabla Detalle por sucursal está basada en un control de la barra Cuadro de Controles (ActiveX).




Los valores de la lista están controlados por el nombre “Zonas” y el valor elegido es guardado en la celda E18 quedando oculto por el control.
El valor de esta celda determina qué sucursales serán exhibidas en la tabla. Para este objetivo usamos la fórmula

=INDICE(INDIRECTO("col"&$E$18);FILA()-20)




donde INDIRECTO("col"&$E$18) componen el nombre del rango dinámico que contiene las sucursales de cada zona.
Así si elegimos la zona Norte, por ejemplo, esta expresión da como resultado “colNorte”, cuya definición puede verse en la hoja Documentacion.

Los datos residen en la hoja BD (obviamente, base de datos), donde son captados con alguno de los métodos mencionados en la nota anterior.
Estos datos son elaborados por tablas dinámicas en las hojas “tdZonas” y “tdSucursales”.

Estas hojas son los verdaderos motores del modelo. Cada una de ellas contiene una tabla dinámica (de allí “td”) que totalizan los datos de acuerdo al corte necesario (por zona o por sucursal).

Los rangos de estas tablas están definidos por el mismo rango dinámico (“tdRango”, documentado en la hoja Documentacion) de manera que no haga falta actualizar el rango cada vez que agregamos datos.

Tanto la tabla de Zonas como la de Sucursales utilizan una fórmula compuesta por las funciones SUMA, INDIRECTO y DIRECCION para extraer los datos correspondientes.

La fórmula en la celda E7 es

=SUMA(INDIRECTO(DIRECCION($E$3+5;3;;;"tdZonas")&":"&DIRECCION($E$4+5;3)))

En esta fórmula $E$3 es el número de orden de la fecha de incio (así funciona el control combobox de la barra de Formularios); le sumamos 5 para ubicarnos en la fila correspondiente de la hoja “tdZonas” (ver la explicación en el párrafo siguiente).
Lo mismo con $E$4. Usamos INDIRECTO para convertir el texto resultado de DIRECCION en un rango que la sunción SUMA pueda usar.

Las listas de zonas y sucursales están ubicadas en la hoja “Parametros”.

Los gráficos de la hoja Dashboard son imágenes capturadas con la herramienta Cámara fotográfica de Excel. Los verdaderos gráficos están en la hoja “graficos”.



Esta técnica nos permite no sólo controlar mejor el diseño de los gráficos sino también documentar el funcionamiento de éstos. Por ejemplo, los nombres en uso en el gráfico del detalle mensual



El último elemento importante del modelo es una pequeña macro que nos asegura que cada vez que terminamos de agregar datos en la hoja BD, las tablas dinámicas sean actualizadas.
Como sabemos, las tablas dinámicas no tienen un mecanismo de actualización automático. Esta macro es un evento de la hoja BD que es disparado cada vez que desactivamos la hoja

Technorati Tags: MS Excel

Construir un tablero de comandos (Dashboard) en Excel – primera nota

A partir de las dos notas publicadas sobre tableros de comandos (dashboards) sencillos he recibido pedidos de publicar algunas notas más organizadas. Con ésta empiezo una serie de entradas sobre construcción de dashboards en Excel, que espero será de provecho para todos.

A lo largo de estas notas veremos cómo construir este tablero de comandos (dashboard).

En este video podrán apreciar algunas de las funcionalidades del dashboard



Un dashboard es una herramienta, tal como un tablero de comando de un avión por ejemplo, que nos permite tener una idea precisa del curso de la empresa u organización en base a datos que hemos recopilado y organizado.

Excel es una excelente herramienta para construir este tipo de informes.



Una de las razones principales del poco uso de Excel para crear dashboards es que tal vez más del 80% de los usuarios desconocen el poder de Excel como plataforma de desarrollo.

Pocos usuarios conocen o están familiarizados con herramientas tales como

• tablas dinámicas y listas (tablas en Excel 2007),



• funciones tales como DESREF, IMPORTARDATOSDINAMICOS (GETPIVOTDATA) y SUMAPRODUCTO



• la gran mayoría sigue usando los formatos por defecto de Excel para crear gráficos,



• pocos usuarios intentan usar la grabadora de macros y menos aún intentan aprender técnicas sencillas de Vba (Visual Basic for Applications).

El primer paso para construir un dashboard en Excel es saber cuál es el objetivo del informe, que información y de qué manera será presentada.

Una vez que sabemos para quién, para qué y cómo presentaremos la información se nos presentan tres cuestiones que debemos resolver:

1. Diseño - cómo diseñar el dashboard para hacerlo comprensible en forma intuitiva



2. Datos - cómo llegarán los datos al cuaderno en el cual estará ubicado el dashboard



3. Mecanismo - cómo manejaremos los datos para obtener la información que mostraremos en el dashboard

En esta serie de notas usaremos como ejemplo la construcción de un tablero de comando para analizar los resultados de una empresa imaginaria.

Nuestra empresa cuenta con varias sucursales repartidas en cuatro zonas: Norte, Sur, Este y Oeste. El director general de la empresa ha encargado un dashboard que muestre estos datos cuantitativos:

• ventas,



• número de clientes,



• ganancia (para el caso de nuestro ejemplo no tiene importancia si se trata de la bruta o de la neta)

y los siguientes índices (KPI – key performance indicators)

• ventas por cliente


• porcentaje de ganancia

Todo esto debe ser dinámico y la resolución mínima de los datos debe ser mensual y comprender los últimos 3 años. Por dinámico entendemos que se podrá generar cortes de la información por períodos, zonas, sucursales y toda combinación posible de esos parámetros.

En las próximas notas estaré mostrando los pasos para construir este dashboard.

Y si, se podrá descargar el archivo... ya se puede descargar aquí

Technorati Tags: MS Excel