Catalogo de imágenes en Excel – Nota aclaratoria

Desde la publicación de la nota sobre el manejo de una base de datos con imágenes en Excel, hace más de dos años, he recibido muchos pedidos de aclaración sobre la técnica empleada. Sucede que varios de mis lectores no han logrado usar los métodos allí descritos en sus propios modelos.
En un intento de facilitar la explicación he creado una animación que pueden descargar en este enlace.
Una vez pulsado el enlace se abrirá la página de descarga. El enlace no es visible a primera vista



El archivo está comprimido, por lo que deberá ser descomprimido



Pueden apretar el botón Open o el botón Save para guardar el archivo y abrirlo más tarde.
El archivo comprimido contiene tres archivos.



Para ver la animación deben activar el archivo HTML (la animación se abrirá en una página del navegador).
Están invitados a dejar comentarios en especial sobre la técnica de animación como herramienta didáctica.

Technorati Tags: MS Excel

Agrupar datos de tablas dinámicas de acuerdo a valores numéricos

Hasta ahora hemos visto como agrupar (y desagrupar) datos en una tabla dinámica en base a campos de fechas o texto. Existe también la posibilidad de agrupar de acuerdo a valores numéricos.
Volviendo a al ejemplo de las tiendas, supongamos que queremos agruparlas por el monto de ventas en intervalos de 150000, es decir, un grupo con tiendas con ventas de 0 a 149999, otro grupo de 150000 a 299999 y así sucesivamente.
Antes de agrupar nuestra tabla dinámica se ve así



Como sólo podemos agrupar y desagrupar por campos de filas o de columnas, agregamos el campo Ventas (que ya aparece en el área de datos) al área de filas



Seleccionamos el campo Ventas del área de filas (en nuestro ejemplo la celda B2) y abrimos el menú Agrupar y mostrar detalle-Agrupar



Como Excel ha detectado que el campo contiene números (y éstos no son fechas) abre automáticamente el formulario Agrupar-Automático.
Excel detecta automáticamente los valores mínimo (Comenzar en) y máximo (Terminar en) del rango, los cuales pueden ser cambiados de ser necesario. En nuestro caso ponemos 0 como mínimo y 500000 como máximo; en la ventanilla Por del formulario ponemos 150000. Apretamos Aceptar



Ahora tenemos que arrastrar el campo Ventas del área de filas y ponerlo a la izquierda del campo Tienda. Al hacerlo obtenemos este resultado



Podemos obtener el mismo resultado creando un campo adicional en la base de datos, pero este método es más eficiente.

Technorati Tags: MS Excel

Agrupar y desagrupar datos manualmente en tablas dinámicas (segunda nota)

Ayer vimos como agrupar datos en tablas dinámicas de Excel basándonos en campos de texto.
Ahora veremos como agregar o quitar líneas a los campos agrupados. Excel no tiene un menú para editar campos agrupados. Podemos agrupar (como mostramos ayer) o desagrupar usando la opción Desagrupar del asistente de tablas dinámicas.
Hay situaciones en las cuales queremos agregar o quitar un miembro a un grupo ya existente. En esta nota mostraré las técnicas para hacerlo.
Volvamos sobre nuestro ejemplo de ayer donde hemos creado el grupo Norte



Si queremos agregar la Tienda 12 a este grupo empezamos por seleccionar todos los miembros del grupo y también Tienda 12 (apretando Ctrl mientras hacemos clic con el mouse)



Abrimos el menú Agrupar y mostrar detalle del asistente de tablas dinámicas y apretamos agrupar



Tienda 12 aparece ahora incluida en el grupo Norte



Nótese que el nombre del grupo ha cambiado al nombre por defecto, Grupo1.

Para quitar alguno de los miembros empezamos por seleccionar todos los miembros del grupo, menos aquel que queremos quitar (en nuestro ejemplo Tienda 19)



Ahora volvemos a usar Agrupar y mostrar detalle-Agrupar, lo que hará que Tienda 10 quede fuera del grupo



Podemos combinar ambas técnicas en una sola. Por ejemplo, si queremos quitar del grupo las tiendas 13 y 16 y agregar la tienda 2, seleccionamos todas las tiendas del grupo excepto 13 y 16 y seleccionamos también la tienda 2. Luego usamos Agrupar y mostrar detalle-Agrupar, obteniendo el resultado deseado en una sola operación.

Technorati Tags: MS Excel

Más sobre agrupar datos en tablas dinámicas de Excel

En la nota Tablas Dinámicas en Excel – Agrupar datos vimos como agrupar datos en una tabla dinámica de Excel basándonos en un campo de fechas.
Pero esta funcionalidad no se limita sólo a fechas. Podemos agrupar datos basándonos también en campos de texto.
Supongamos, una vez más, esta tabla que muestra las ventas de cada tienda de una cadena



Empezamos creando la tabla dinámica



Nuestro objetivo es agrupar las tiendas por zonas: Norte, Sur, Este y Oeste. El problema es que la tabla de datos no incluye un campo que nos permita agrupar las tiendas por zonas. Una solución posible es agregar el campo, pero esto no es necesario.
Empezamos por seleccionar todas las tiendas que pertenecen a la zona Norte. No es necesario que el rango sea continuo de manera que después de seleccionar la primer tienda apretamos Ctrl al seleccionar las restantes



En nuestro caso hemos seleccionado las tiendas 1, 13, 16, 19 y 3 que corresponden a la zona Norte. En el asistente de Tablas Dinámicas abrimos el menú Agrupar y mostrar detalle--Agrupar.



Excel crea un nuevo campo de filas (Tienda2) y agrupa las tiendas que hemos seleccionado en Groupo1.



Cambiamos el nombre del grupo a Norte



Repetimos el proceso para el resto de las zonas



Ahora podemos cambiar el nombre del campo de Tienda2 a Zonas y arrastrar el campo Tienda fuera de la tabla



Un detalle a tomar en cuenta es que Excel crea el nuevo campo sin subtotales



Es recomendable cambiar la definición a Automático, de manera que cuando abrimos el detalle o agregamos el campo Tienda, podamos ver el total por zona

Technorati Tags: MS Excel

Versiones de Excel – resultado de la encuesta

A la encuesta ¿Qué versión de Excel usas? han respondido 650 lectores y estos son los resultados



Vemos que 59% de mis lectores usan el Excel clásico (versiones 93 hasta 2003) y 41% usan el nuevo Excel (Excel2007).



Al lanzar la encuesta presuponía que la vasta mayoría de mis lectores no había adoptado la versión 2007 y seguían usando alguna de las versiones clásicas de Excel.
El sitio Exceluser de Charley Kyd hay una encuesta similar a la cual han respondido 1500 lectores. En esta encuesta se contemplaba también la posibilidad de usar ambas versiones de Excel (clásica y nueva). Los resultados son



Resultados ciertamente distintos, donde sólo el 22% de los lectores usan Excel 2007. Si eliminamos a los que usan ambas versiones, vemos que el 71% de los usuarios siguen con las versiones clásicas de Excel y un poco más de un cuarto, el 29%, se ha pasado a la nueva versión. También podemos considerar las respuestas “ambas versiones” como dos respuestas, y en ese caso vemos que el 68% usa las versiones clásicas y un 32% la nueva versión



Ambas encuestas coinciden en que las versiones clásicas de Excel siguen siendo las predominantes. Pero en lo que se refiere a Excel 2007, las respuestas de mis lectores tienden a mostrar que la nueva versión de Excel está ganando terreno y acercándose a la mitad del mercado mientras que las conclusiones de Exceluser son radicalmente distintas.
En una conclusión coincidimos: al instalar la nueva versión de Excel es recomendable dejar instalada la versión anterior.

Technorati Tags: MS Excel

JLD en castellano – Balance del tercer año

Ha llegado el momento de desearles a mis lectores un feliz año nuevo y de presentarles las estadísticas del año que acaba de terminar.
Dice el conocido adagio que una imagen vale más que cien palabras, así que aquí pueden ver la cantidad de visitantes por mes, comparada con la del año anterior



y su respectiva tabla



Este blog ha crecido en relación al 2007 en un 82%, notándose que en los últimos meses la tasa de crecimiento es menor.
Es interesante ver estas estadísticas en forma continua a lo largo de los dos años:



Podemos ver claramente tres etapas:
# - de enero a mayo del 2007 el promedio de visitantes por mes es 9310
# - de junio del 2007 a marzo del 2008 el promedio de visitantes mensuales es 24110, un crecimiento cercano al 160%
# - de abril a diciembre de este año, con un promedio de 34657 visitantes por mes, lo que significa un crecimiento del 44% en relación a la etapa anterior
En cuanto al número de páginas vistas, los 387284 visitantes del 2008 vieron 814677 páginas, un promedio de 2.1 páginas vistas por visitantes. En el 2007 los 212275 visitantes consultaron 483518 páginas, 2.28 por visitante



Podemos ver que en las dos primeras etapas señaladas más arriba el promedio de páginas vistas por visita es 2.3, mientras que en la tercer etapa (abril-diciembre 2008), si bien el número de visitantes ha seguido creciendo, el promedio de páginas por visita es 2.04.
También el número de lectores que siguen el blog a través del feed ha ido creciendo. En el 2007 había llegado a 257 suscriptores. En el 2008 este blog cuenta con 803 suscriptores.




El balance del 2008 es positivo y el reconocimiento que recibo, casi diariamente, de mis lectores no sólo me reconforta sino que me impulsa a seguir adelante.
Durante el 2008 he seguido con mi tradición de no llevar a cabo todos los proyectos que me había propuesto. Lo que espero poder realizar en el 2009:
# - crear mi propio sitio que contendrá entre otras cosas los ejemplos de las notas para descargar y guías sobre temas de Excel
# - agregar animaciones a los ejemplos
# - seguir disfrutando de la tarea de publicar las notas en este blog y de las cálidas respuestas de mis lectores.

A todos, muchas gracias y mis mejores deseos para el año que comienza

Technorati Tags: MS Excel

Fechas anteriores al año 1900 en Excel

Hagamos la siguiente prueba: en la celda A7 ponemos la fecha 01/01/1900 (el primero de enero del 1900); luego arrastramos el valor con el mouse hasta la celda A1. Supuestamente tendríamos que ver la siguiente sucesión:

A6 = 31/12/1899
A5 = 30/12/1899
A4 = 29/12/1899
A3 = 28/12/1899
A2 = 27/12/1899
A1 = 26/12/1899

Veamos que pasa en la práctica



Extraño, no? Hagamos otro experimento. En una celda ponemos esta fórmula

=FECHA(1899,12,31)

¿Cuál será el resultado? Sería de esperar 31/12/1899, sin embargo el resultado será 31/12/3799



Es decir Excel ha calculado el año en la función agregando 1900 al número 1899. Esto se debe a que Excel no sabe, o mejor dicho, no ha sido programado para calcular fechas anteriores al primero de enero del 1900. Como ya hemos explicado en más de una nota Excel usa una serie de números para las fechas donde el número 1 representa el 01/01/1900, el 2 la fecha 02/01/1900 y así sucesivamente. Es decir que si queremos calcular la edad de alguien nacido antes del 1900 o si queremos manejar una base de datos genealógicos con Excel tendremos que encontrar algún rodeo.

En esta nota veremos algunos rodeos conocidos para superar este problema.

La solución oficial de Microsoft es una UDF (user defined function – función definido por el usuario). Para usar la función (en realidad son dos funciones) deben copiar el código en la hoja del enlace y pegarlo a un módulo normal de Vba (preferentemente en el cuaderno Personal.xls).

La función tiene dos variables: fecha de comienzo (startdarte) y fecha final (enddate). Fechas anteriores a 01/01/1900 deben ser texto. Por ejemplo, si nuestro abuelo nació el 18/02/1877 y falleció el 25/04/1963, la función da como resultado 86.
El problema con esta macro es que funciona con la notación americana, mes/día/año. Si tratamos de usarlo con la notación corriente en la mayoría de los países, d+ia/mes/año, el resultado será “invalid date”.


Otra solución es descargar e instalar el complemento XDATE (eXtended Date) desarrollado por John Walkenbach. Una vez descargado e instalado, una serie de funciones son incorporadas al grupo de funciones de fechas del asistente de funciones



Ahora podemos volver al ejemplo de mi abuelito y utilizar XDATEDIF para calcular cuántos días vivió: 31476



Para calcular cuantos años usamos XDATEYEARDIF.



Este complemento agrega otras funciones como por ejemplo XDATEDOW que calcula el día de la fecha (1 = domingo).

Un problema similar existe con fechas posteriores al 31/12/9999. En otras palabras, para Excel el mundo fue creado el 1 de enero del 1900 (en la versión para McIntosh, en 1904) y tendrá su fin el 31 de diciembre del 10000.

Technorati Tags: MS Excel

Actualización automática de gráficos con listas.

Allá por el verano del 2006 (o el invierno, dependiendo de donde resida el lector) expliqué como crear un gráfico que se actualice automáticamente a medida que le agregamos datos.

La técnica consistía en usar rangos dinámicos en la función SERIES del gráfico. La ventaja de esta técnica es que es consistente con todas las versiones de Excel. Pero si usamos la versión 2003 o 2007 de Excel podemos usar una técnica mucho más sencilla.

Supongamos que tenemos esta tabla de ventas por mes a partir de la cual creamos un sencillo gráfico de columnas

Queremos que al agregar los próximos meses el gráfico se actualice automáticamente. Para hacerlo sacaremos provecho de la funcionalidad Datos--Listas.

Empezamos por seleccionar alguna de las celdas de la tabla, por ejemplo A1 y abrimos el menú Datos-Lista-Crear Lista



Excel selecciona automáticamente todo el área de la tabla



Después de controlar que la selección es la deseada, apretamos Aceptar. Excel ha creado ahora una lista y expandirá el área de la tabla automáticamente cada vez que agreguemos (o quitemos) una fila. Excel también abre la barra de herramientas de listas donde tenemos todo tipo de herramientas para administrarla



Para agregar una fila a la lista seleccionamos alguna celda del área. Veremos que aparece un marco azul alrededor del área de la lista y una estrella azul en la primer celda libre, donde debemos poner los datos



Todo los que nos queda por hacer es agregar los datos y el gráfico se actualizará automáticamente



En Excel 2007 esta técnica funciona de la misma manera pero con, como no podía ser de otra manera, algunas diferencias funcionales:

# - La funcionalidad no se llama Lista sino Tablas y no se encuentra en la pestaña Datos como en Excel 2003 sino en la pestaña Insertar



# - Al seleccionar alguna celda de la lista/tabla no aparece una nueva línea en blanco en la tabla. De todas maneras si agregamos un mes inmediatamente debajo del último registro de la tabla, Excel expande la lista en forma automática. Otra técnica en Excel 2007 es usar la tecla Tab. Por ejemplo, si después de introducir el dato de setiembre en la celda B10 apretamos TAB, Excel selecciona automáticamente la celda A11 y expande la tabla.

Microsoft ha agregado muchas otras herramientas y funcionalidades a las tablas en Excel 2007, pero esto será tema de una futura nota.

Technorati Tags: MS Excel

Insertar un calendario permanente en Excel 2007.

Hace ya más de dos años publiqué la nota sobre como insertar un calendario permanente en hojas de calculo Excel con Controles ActiveX. Eran los días previos a la aparición de Excel 2007 y los controles estaban ligados a dos barras de herramientas, Formularios y Cuadros de Controles (controles ActiveX).
En Excel 2007 no hay barras de herramientas y los controles están ubicados en la pestaña Programador de la cinta de opciones

Nótese que todas las herramientas necesarias están a nuestra vista en la misma sección de la pestaña (Modo diseño, Propiedades, etc.).

Como pueden ver, al apretar Insertar vemos de hecho las mismas barras de herramientas a las que estábamos acostumbrados en las versiones anteriores de Excel.

También el modelo de validación de fechas programando un evento que muestre el calendario si se cumplen ciertas condiciones podemos usarlo en Excel 2007. Si bien la interfaz del editor de Visual Basic no ha cambiado algunos lectores me comentan que no encuentran el botón de controles adicionales. Al igual que en las versiones anteriores podemos usar el menú Herramientas-Controles Adicionales o hacer un clic con el botón derecho del mouse en cuadro de herramientas del UserForm

También podemos crear un calendario permanente en una hoja de Excel sin usar controles. Esto puede hacerse con fórmulas matriciales, como en este modelo desarrollado por John Walkenbach



Este modelo usa fórmulas matriciales de rango, es decir una fórmula que da el resultado en varias celdas simultáneamente. Para crear este modelo empezamos por introducir en la celda B2 el mes y el año del calendario. Podemos hacer esto poniendo en B2 la fórmula =HOY() y dando un formato “mmmm,aaaa” a la celda. Luego seleccionamos el rango B4:H9 asegurándonos que la celda activa sea B4, introducimos esta fórmula en la celda activa

=SI(MES(FECHA(AÑO(B2);MES(B2);1))<>MES(FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7}-1);"";FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7}-1)

y apretamos simultáneamente Ctrl+Mayúsculas+Enter

Podemos usar una fórmula más sencilla,

=FECHA(AÑO(B13);MES(B13);1)-(DIASEM(FECHA(AÑO(B13);MES(B13);1))-1)+{0\7\14\21\28\35}+{0;1;2;3;4;5;6}

pero que mostrará las fechas en todas las casillas del rectángulo

Como ven, estos calendarios son “gringos” con la semana comenzando en domingo. Si queremos que la primer columna de la semana sea el lunes, modificamos levemente la fórmula

=SI(MES(FECHA(AÑO(B2);MES(B2);1))<>MES(FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7});"";FECHA(AÑO(B2);MES(B2);1)-(DIASEM(FECHA(AÑO(B2);MES(B2);1))-1)+{0\1\2\3\4\5}*7+{1;2;3;4;5;6;7})

O esta fórmula

=FECHA(AÑO(B13);MES(B13);1)-(DIASEM(FECHA(AÑO(B13);MES(B13);1))-2)+{0\7\14\21\28\35}+{0;1;2;3;4;5;6}

para el modelo sencillo

Technorati Tags: MS Excel