Transferir datos de Excel a Access – primera nota.

Después de escribir la nota sobre cómo consolidar datos de más de dos cuadernos con MS Query, decidí que había llegado el momento de mostrar cómo podemos transferir datos de una hoja de Excel a una tabla de Access. Usar Access es una solución práctica cuando nuestra tabla de datos supera el límite de Excel (para las versiones 97-2003) de 65536 filas.

En esta nota mostraremos que no hay motivo para sentirse inhibido de usar Access, también si no estamos del todo familiarizados con su uso.

Básicamente hay dos caminos posibles para transferir los datos: manualmente y programáticamente (Vba). Esta nota se ocupa de cómo transferir datos de Excel a Access manualmente. Para poder hacerlo necesitamos, obviamente, que Access esté instalado en nuestra máquina (o servidor).

Para nuestro ejemplo consideraremos un cuaderno Excel con dos hoja. La primera contiene ventas del mes de abril y la segunda del mes de mayo. Por motivos de claridad usamos pocos datos.

El primer paso es abrir Access y crear una base de datos en blanco



En nuestro ejemplo aceptamos el nombre propuesto por Access (bd1), pero podemos darle cualquier otro nombre, preferentemente más significativo.

Una vez creada la base de datos veremos esta pantalla

Seleccionamos con un clic Tablas en el área de Objetos y abrimos el menú contextual (clic con el botón derecho del mouse); aquí seleccionamos la opción Importar.



En el diálogo que se abre elegimos Excel en Tipo de Archivo y seleccionamos el cuaderno que contiene la hoja que queremos importar.



En nuestro ejemplo Excel detecta que hay dos hojas en el cuaderno. Elegimos “abril” y apretamos Siguiente



Ahora debemos definir si la primer fila es contiene títulos. En caso afirmativo los títulos son usados como nombres de los campos de la tabla.



En este paso debemos definir donde guardar los datos. En nuestro caso no hemos creado ninguna tabla así que la opción es “en una nueva tabla”.

El próximo paso nos permite definir distintas opciones para los campos. En nuestro ejemplo, sencillamente seguimos adelante apretando “Siguiente”.

Ahora podemos definir claves principales (índices) para la tabla. Para nuestro ejemplo este paso no es crítico, pero en general es recomendable dejar la opción por defecto de Access.



En el último paso podemos definir el nombre de la tabla. Por defecto, Access propone el nombre de la hoja. Por lo general, como en nuestro ejemplo debemos cambiar el nombre a algo más significativo, como Ventas.

El último paso es apretar Finalizar. Al cabo de unos instantes Access nos informa que el proceso ha terminado (o fracasado si es que hemos cometido algún error o los datos no son los adecuados). Access ha creado ahora la tabla Ventas



que contiene los datos de la hoja



Ahora repetimos el proceso pero elegimos la hoja “mayo”.



En el tercer paso elegimos la tabla Ventas



Finalmente apretamos Finalizar. Los nuevos datos han sido agregados a los existentes en la tabla



Para totalizar y analizar los datos de base de datos podemos usar tablas dinámicas con la opción Fuente de datos externa

Technorati Tags: MS Excel

Consolidar datos de más de dos cuadernos Excel con MS Query

En el pasado hemos mostrado cómo consolidar datos de distintas hojas de un mismo cuaderno empleando el MS Query.

Un lector me comenta que cuando quiere consolidar datos de tres hojas de tres distintos cuadernos, MS Query no permite agregar la tercera tabla (la hoja del tercer cuaderno). Como sucede frecuentemente con esta aplicación, la ayuda no viene a nuestro rescate en este problema.

Veamos un ejemplo (en este caso estaremos usando Excel 2007, pero el mecanismo es el mismo en Excel 97-2003).

Empezamos por establecer el nexo a la fuente de datos, en este caso dos cuadernos Excel

Seleccionamos uno de los cuadernos (MS Query no nos permite hacer selecciones múltiples)



Incluimos todos los campos de la hoja y seguimos adelante con el asistente hasta el último paso, donde seleccionamos la opción “Ver datos o modificar consulta con MS Query”. Al finalizar el proceso se abre la ventana de MS Query con los datos que hemos extraído de la hoja del primer cuaderno.



Ahora agregamos la hoja del segundo cuaderno con el menú Tabla-Agregar Tablas



Aquí nos topamos con el primer problema: no podemos ver el nombre del archivo a elegir dado que no podemos extender el ancho de la ventanilla. Guiándonos por la ubicación relativa de los archivos en la carpeta, elegimos el último



Ahora queremos agregar la tercer tabla, para lo cual volvemos sobre el proceso anterior



MS Query no nos permite agregar la tabla bajo la advertencia “Imposible el acceso..”.
Lo que hacemos en este caso es crear primero una unión entre las dos tablas presentes antes de intentar agregar la tercera. En nuestro caso la unión será entre los campos “factura” de ambas tablas.

Ahora intentamos agregar la tercer tabla, “Clientes”.




Del momento que existe por lo menos una unión entre las dos primeras tablas podemos agregar la tercera.

Si intentamos agregar una cuarta tabla, cuyo origen es una hoja de un cuarto cuaderno, nuevamente tendremos que crear una unión a la tercer tabla antes de poder agregar la cuarta.

Otro rodeo posible es mover las hojas de los distintos cuadernos a un único cuaderno y luego proceder como mostramos en la nota mencionada.

Technorati Tags: MS Excel

Cambios en el diseño, consultas de lectores y encuestas.

Este mayo ha sido un mes intenso. Mis ocupaciones cotidianas apenas me han dejado algo de tiempo para este blog. Como habrán notado la cantidad de notas publicadas este mes es sensiblemente menor a lo habitual. También he respondido a muy pocas de las consultas que me llegan por mail.

En el escaso tiempo disponible he hecho un primer intento de cambiar el diseño del blog. La intención es hacerlo más claro y aprovechar mejor el ancho de pantalla (ya que la mayoría de mis lectores usan resolución de pantalla 1024X768). Espero recibir vuestros comentarios.

A la encuesta sobre el tipo de usuarios de Excel respondieron 359 lectores. La mayoría, el 42%, se considera “intermedio”, un cuarto se consideran “avanzados, lo mismo los “principiantes” y el restante 7% “súper avanzados”.






Si bien había propuesto algunos criterios generales para la evaluación, supongo que cada uno se califica de acuerdo a su propia percepción del nivel de conocimientos y destreza con Excel.
Hace 10 años atrás mi respuesta a la pregunta cuál es mi nivel de Excel hubiera sido “avanzado”. Desde la perspectiva de mi nivel actual la respuesta correcta tendría que haber sido “básico-intermedio”.

Este tema es a la vez interesante y complejo. Hace unos días me consultaban del departamento de Recursos Humanos qué preguntar para establecer el grado de conocimientos de Excel de los postulantes a un determinado puesto. Mi respuesta fue que esto dependía de la definición de las tareas que el postulante tendría que realizar. En la medida que el puesto no incluya desarrollo de aplicaciones con Excel, un conocimiento superficial de Vba sería suficiente. Pero si estamos buscando alguien para el departamento de control de gestión, un buen dominio de tablas dinámicas es esencial. Un buen dominio del tema de gráficos no me parece crítico, pero sí lo es el conocimiento de las funciones de búsqueda (BUSCARV, INDICE, COINCIDIR, etc).

Technorati Tags: MS Excel

Análisis de sensibilidad dinámico con Excel

Hace dos años atrás comentaba el uso de la función Tabla para hacer análisis de sensibilidad. Esta herramienta se encuentra en el menú Datos en Excel 97-2003 o en la pestaña Datos—Análisis Y si en Excel 2007

Por ejemplo, este modelo analiza la sensibilidad del pago mensual por un préstamo en función de la tasa de interés anual y el número de cuotas



Podemos apreciar los valores de los parámetro de la tabla (la tasa de interés anual y el número de cuotas) se “distribuyen” hacia arriba y hacia abajo en relación al valor central. La tasa de interés crece o decrece en “saltos” de 0.25% y el número de cuotas en escalones de 5 años.

Estos valores son constantes y es evidente que lo ideal sería que nuestro modelo fuera dinámico. Si cambiamos la tasa de interés en la tabla superior, queremos que los parámetros de la tabla de sensibilidad se ajusten automáticamente. Lo mismo con el número de cuotas.

Parece bastante evidente que todo lo que tenemos que hacer determinas los valores de los parámetros con una fórmula y ligar el parámetro “central” a la celda correspondiente en la tabla superior. El problema es que TABLA no se comporta como hubiéramos supuesto. Veamos que pasa cuando determinamos los valores de los parámetros con fórmulas

Por ahora hemos dejado los valores de las celdas E7 y B10 constantes. En C7:D7 hemos puesto la fórmula “=E7-0.25%”, en F7:G7 “=E7+0.25%”. Lo mismo con el número de cuotas; en B11 ponemos “=B10+5”. Como podemos ver, los resultados se mantienen.

Ahora queremos ligar E7 a B2 y B10 a B3 para que cuando cambiemos alguno de estos valores, la tabla de sensibilidad se adapte automáticamente.

El resultado es desastroso!

La solución es programar un evento para cambiar los datos sin usar referencias a otras celdas en la hoja.

El primer paso es definir nombres para las celdas B2, B3, E7 y B10. Esto nos permitirá simplificar el código del evento y hacerlo más claro. La forma más práctica de definir los nombres es usando el cuadro de nombres

Los nombres que hemos definido son ("variable" es el nombre de la hoja que contiene la tabla) :

cuotas=variable!$B$3

paramCuotas=variable!$B$10

paramInteres=variable!$E$7

tasa_interes=variable!$B$2

Abrimos el editor de Vba usando la opción Ver Código de la hoja

En el módulo de la hoja ponemos este código


Private Sub Worksheet_Change(ByVal Target As Range)

If Union(Target, Range("tasa_interes")).Address = Range("tasa_interes").Address _
Or Union(Target, Range("cuotas")).Address = Range("cuotas").Address Then
Range("paramInteres") = Range("tasa_interes")
Range("paramCuotas") = Range("cuotas")
End If

End Sub



Como estamos usando el evento Worksheet_Change, cada vez que cambie el valor de la celda B2 o B3, la tabla de sensibilidad es vuelta a calcular. Usamos UNION para evitar que el cáclulo se efectúe sólo si el cambio se produjo en las celdas B2 o B3.

Technorati Tags: MS Excel

Lectores del feed

Por primera veo que el feed del blog ha superado los 1000 lectores



No se cuan importante pueda ser este hecho, pero no quería dejar de compartirlo con mis lectores.

Auditoría de fórmulas en Excel – señalar precedentes y dependientes

Ya hemos hablado en el pasado sobre la barra de auditoría de fórmulas. Es ésta una excelente herramienta para controlar y auditar las fórmulas en las hojas. Sin embargo, y de acuerdo a mi experiencia es poco usada y menos aún conocida por gran parte de los usuarios, incluidos aquello que se consideran usuarios avanzados.

En esta nota mostraremos sucintamente el rastreo de celdas precedentes y dependientes en un cuaderno de Excel.

Precedentes son celdas que afectan el valor de la celda inspeccionada. Dependientes son las celdas afectadas por la celda seleccionada. Veamos esto con un ejemplo

En Hoja1 tenemos una tabla de bonos que corresponden a intervalos de edades. En la hoja Nombres tenemos la lista de los nombres con sus respectivas edades.

En la celda E1 ponemos una lista desplegable con Validación de Datos, basada en la lista de nombres.



En la celda E2 ponemos la fórmula

=BUSCARV(E1,nombres!A2:B6,2,0)

que nos da la edad del nombre que aparece en la celda E1. Finalmente en la celda E3 ponemos

=BUSCARV(E2,A2:B7,2)

para determinar el bono de acuerdo a la edad.

Seleccionamos A7 (o cualquier celda en el rango A2:A7) y accionamos la opción Rastrear dependientes

Excel traza una flecha que indica que la celda E3 es afectada por el valor de la celda A7.



Ahora seleccionamos la celda E3 y activamos Rastrear Precedentes



Vemos que E2 es precedente de E3 (afecta su valor) y también todo el rango A2:B7 que además de la flecha aparece enmarcado con un borde de color azul.

¿Qué pasa cuando las celdas precedentes están en otra hoja? Ese es el caso de la fórmula en la celda E2 que es afectada por los valores de la tabla nombres en la hoja Nombres.
En ese caso Excel señala que se trata de una referencia remota poniendo una flecha y en su extremo el símbolo de una tabla.



Para ver las referencias a las celdas precedentes tenemos que apuntar con el mouse a la flecha (la figura del mouse cambia de una cruz a una flecha) y hacer un doble clic



Esto abrirá el menú de Ir A, donde podemos ver la lista de las celdas remotas que afectan a la fórmula



Podemos elegir una de las celdas de la lista y apretar aceptar para ir a la celda en cuestión.

Technorati Tags: MS Excel

Catálogo de Imágenes en Excel – otra versión

Crear catálogos de imágenes en Excel es uno de los temas más consultados en este blog. Hace un poco más de dos años y medio publiqué la primer nota sobre base de datos de imágenes que se convirtió en una de las más leídas del blog.

La técnica empleada para crear el catálogo de imágenes en Excel no es trivial y a principios de este año publique una nota aclaratoria que incluye un enlace para descargar una animación del proceso.

Además de los problemas potenciales que señalaba en la nota original, si guardamos las imágenes en el cuaderno, tal como muestra la técnica empleada en la nota original, el archivo tiende a volverse muy “pesado”, en especial si guardamos imágenes de alta resolución.

En esta nota mostraremos una técnica distinta, donde las imágenes están guardadas en alguna carpeta del sistema (computador personal o red) y sólo usamos Excel para mostrarlas de acuerdo a un número de código que se haya elegido en alguna celda.

Empezamos por guardar las imágenes en una única carpeta. Por comodidad las pondré en D:\catalogo





En el siguiente paso construimos el cuaderno Excel que tendrá dos hojas, Lista y Catalogo.
Como su nombre indica, en la primer hoja pondremos la lista de imágenes pero en lugar de guardar la imagen en la hoja pondremos la referencia a la dirección en el sistema





Podemos ver que los valores en la primer fila no son números de código y dirección en el disco duro. La función de estos valores será explicada más adelante.

Ahora creamos dos rangos dinámicos que encapsularemos en dos nombres

imagenes =DESREF(Lista!$A$1,0,0,CONTARA(Lista!$A:$A),1)

path_imagenes =DESREF(Lista!$A$1,0,0,CONTARA(Lista!$A:$A),2)

Estos nombres se adaptan automáticamente a la cantidad de filas en la lista usando la técnica que ya hemos mostrado en varias oportunidades. “Imagenes” se refiere al rango de la columna A que contiene valores; “path_imagenes“ se refiere al rango A:B con el número de filas correspondiente. Estos nombres serán usados en la hoja “Catalogo” donde mostramos las imágenes.

En la hoja Catalogo creamos una lista desplegable usando la opción Lista de Validación de Datos




Los valores de la lista desplegable están definidos por el rango dinámico “imagenes”



Como pueden ver, el primer valor de la lista sirve como indicación al usuario qué es lo que tiene que hacer. Los valores de la lista desplegable sirven como argumentos en para la función BUSCARV que ponemos en la celda C3
=BUSCARV(A3,path_imagenes,2,0)
Esta fórmula da como resultado la dirección de la imagen (que pusimos en la hoja Lista).

Nuestro próximo paso es poner en la hoja un medio de mostrar la imagen correspondiente al código que hayamos elegido. Activamos la barra de herramientas Cuadro de Controles y elegimos el control Imagen





Ponemos el control en una posición cercana a la lista desplegable y para mejorar el aspecto de la hoja quitamos las líneas de división. La posición del control no es casual y nos sirve para ocultar la fórmula que hemos puesto en C3.




Ahora necesitamos un método para cargar la imagen correspondiente, lo que haremos con una macro, y un evento que haga correr la macro cuando el usuario elige un número de código.

Sub show_pic() es la macro para cargar la imagen en el control

Sub show_pic()

Dim PicAddress

PicAddress = Sheets("Catalogo").Range("C3").Value

If IsError(PicAddress) Then
Sheets("Catalogo").Image1.Picture = Nothing
Else
Sheets("Catalogo").Image1.Picture = LoadPicture(PicAddress)
End If

End Sub

Esta macro va en un módulo común del editor de VBa.
Para que la macro corra cuando se elige un número de código programamos este evento en el módulo de la hoja Catalogo

Private Sub Worksheet_Change(ByVal Target As Range)
Dim celControl As Range

Set celControl = [A3]

If Union(Target, celControl).Address = celControl.Address Then show_pic

End Sub


Elegimos un número de código en la lista desplegable, lo que dispara la macro que carga la imagen correspondiente en el control




La ventaja de esta técnica reside en que al no guardar las imágenes en un cuaderno de Excel, éste es muy estable y nos permite usar imágenes de alta resolución.

Finalmente agregaremos dos mejoras a nuestro modelo. Tenemos que evitar que el usuario guarde el cuaderno con una imagen cargada en el control. Esto puede suceder de dos maneras:

1 – que el usuario intente cerrar el cuaderno y Excel le pida guardarlo antes de cerrarlo (y que el usuario decida guardarlo)

2 – que el usuario decida guardar el cuaderno por propia iniciativa.

La idea no es evitar que el usuario guarde el cuaderno, sino que al hacerlo no haya ninguna imagen cargada en el control.
Para lograr esto programamos dos eventos en el objeto ThisWorkbook





Workbook_BeforeClose

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Catalogo").Image1.Picture = Nothing
Sheets("Catalogo").Range("A3").ClearContents
End Sub


Workbook_BeforeSave

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Sheets("Catalogo").Image1.Picture = Nothing
Sheets("Catalogo").Range("A3").ClearContents
End Sub


El ejemplo se puede descargar aquí. Para que el modelo funcione hay que reemplazar los valores del rango A2:B6 por referencias a archivos que existan en el computador o red del usuario.



Technorati Tags: MS Excel

Efectos especiales con bordes en Excel

En el trámite de preparar una nota sobre el uso de estilos en Excel, tema importante pero ampliamente ignorado por la mayoría de los usuarios, encontré como lograr un efecto que he visto en varios cuadernos de Excel pero nunca supe cómo hacerlo.

Me refiero a las celdas que aparecen como alto o bajo relieves. Una imagen vale más que cien palabras:

Para lograr este efecto todo lo que tenemos que hacer es poner un fondo gris a un área de celdas, asegurándonos de tener una columna de más a cada lado de la celda y una fila de más por encima y por debajo de las celdas en cuestión.

El fondo del área A1:C5 es gris 25%

La línea de los bordes izquierdo y superior son de color blanco y el color de los bordes inferior y derecho es gris 50%



Para lograr el efecto de bajo relieve sencillamente volvemos blanco el borde inferior y gris 50% el superior.

Aplicando esta técnica podemos crear una tabla como esta:




Debido al fondo gris del rango, las líneas de división se han vuelto invisibles. Si necesitamos agregar líneas divisorias podemos usar el estilo de línea más “liviano” (el primero que aparece de la lista de estilo en el Formato de Celdas y darle el color gris 25%



Estos efectos pueden conmover al más implacable de los jefes!

Technorati Tags: MS Excel

Tipos de usuarios de Excel

El título de esta entrada estaba destinado a ser “¿Qué tipo de usuario de Excel sos?”. El “sos” expresa toda mi argentinidad, pero me parecía un tanto discriminatorio para la mayoría de mis lectores, que no son argentinos.

De acuerdo a las estadísticas que me provee Statcounter, la mayoría de mis lectores son españoles, mexicanos y venezolanos. Así que consideré usar “¿Qué tipo de usuario de Excel eres?”. Pero no podía liberarme de cierta sensación de hipocresía, así que concluí con un título “neutro” como el que encabeza esta nota.

Empecemos por decir que todo usuario de Excel es de hecho un programador (me niego rotundamente a usar “desarrollador” como traducción de “developer”).

De hecho Excel es mucho más que simplemente una hoja de cálculos. En esto, y muchas cosas más, coincido con Stephen Bullen, Rob Bovey y John Green que en su Excelente libro “Professional Excel Development” consideran a Excel una plataforma para el desarrollo de aplicaciones. En la introducción del libro, los autores clasifican los usuarios de Excel en cinco distintos tipos:

• El usuario básico (basic Excel user)

• El usuario avanzado (Excel power user)

• El programador de Vba (Vba developer)

• El programador de Excel (Excel developer)

• El programador profesional de Excel (professional Excel developer)

Supongo que la mayoría de mis lectores caen dentro de las dos primeras categorías.
Dentro de ellas es posible definir sub categorías. Pero antes de ocuparnos de ese tema es interesante ver cómo definen Bullen, Bovey y Green las diferencias entre las últimas tres categorías.

Para ellos el programador de Vba se caracteriza por el uso intensivo y aún exagerado de código Vba en sus cuadernos Excel. Este usuario tiene buenos conocimientos, aunque no avanzados, de Vba y cree que las macros son la mejor solución para todo problema. Por lo general no conocen suficientemente las posibilidades de Excel lo que los lleva a preferir soluciones de Vba.

El programador de Excel, por lo contrario, usa intensivamente las funcionalidades de Excel y las potencia usando Vba a discreción. Estos usuarios son capaces de desarrollar modelos complejos y a la vez eficientes basándose principalmente en Excel , pero llegan a su límite cuando se trata de usar otras aplicaciones o lenguajes junto con Excel.

El programador profesional desarrolla sus modelos basándose principalmente en Excel, pero sabe combinarlo con otras aplicaciones y lenguajes.

Volviendo a las dos primeras categorías es posible, como señalaba más arriba, definir sub categorías.

El primer nivel de usuario básico, el principiante, es aquel que conoce los rudimentos de Excel y las funciones más sencillas, como SUMA, PROMEDIO, CONTAR, etc. Por lo general tiende a crear hojas que replican formularios que llevaba en forma manual. El énfasis está puesto en el formato de la hoja más que en la funcionalidad.

El segundo nivel de usuario básico, el intermedio, conoce y comprende el uso de funciones de búsqueda como BUSCARV, INDICE y COINCIDIR. Conoce y usa Autofiltro y esto lo lleva a entender la necesidad de organizar los datos en forma tabular (listas en términos de Excel 2003, tablas en términos de Excel 2007).

El primer nivel de usuario avanzado usa Excel tanto para crear modelos de cálculo como para administrar datos. Usa funcionalidades avanzadas como nombres (en especial para rangos dinámicos), Filtro Avanzado, Validación de Datos y Formato Condicional. Usa funciones como DESREF y SUMAPRODUCTO.

EL segundo nivel de usuario avanzado, el súper usuario, conoce y usa para sus modelos funciones matriciales y tablas dinámicas y, lo más importante, comprende cuando deben ser usadas. Además tiene ya suficiente conocimientos de Vba para agregar macros y tal vez desarrollar funciones definidas por el usuario (UDF) sencillas.

En resumen, las líneas demarcadoras entre las categorías de usuarios de Excel son:

Básico ----funciones de búsqueda--->Intermedio--->DESREF, SUMAPRODUCTO--->Avanzado--->tablas dinámicas--->Súper Usuario.

¿Qué criterios agregarían o cambiarían? ¿Qué tipo de usuario se consideran ustedes?

Technorati Tags: MS Excel

Buenas y malas prácticas en Excel – colores como datos y número de hojas en un cuaderno.

Hay otros dos puntos en la discusión propuesta por The Universe Divided que merecen ser examinados: el uso de colores como datos (o mejor dicho, meta-datos) y la cantidad de hojas empleadas en un cuaderno.

Excel no viene provisto con fórmulas para realizar cálculos basados en el color de fondo de la celda (o el color de la fuente). Sin embargo a lo largo de los años que vengo ayudando a colegas a desarrollar modelos he visto muchos usuarios que usan en forma intensiva el color de fondo de la celda para señalar un meta-dato. Por ejemplo, esta lista con trabajadores de tres distintos niveles

A pesar que se ha agregado una referencia para indicar al usuario cuál es el nivel del trabajador de acuerdo al color del fondo, éste meta-dato es absolutamente inútil. Por ejemplo, no podemos usar CONTAR.SI para saber cuántos trabajadores de cada nivel hay. En este blog hemos mostrados dos soluciones para efectuar operaciones basándonos en colores, usando funciones XLM y usando funciones UDF (desarrolladas por el usuario – macros). Pero éstas no son soluciones ideales y si estamos diseñando un modelo desde el principio la mejor práctica es evitar el uso de colores como meta-datos.

La otra cuestión es la del número de hojas en un cuaderno. Una práctica común en especial en los usuarios poco experimentados es dividir los datos en hojas de acuerdo a algún criterio aparentemente lógico. Por ejemplo, creamos un modelo para registrar las ventas de una compañía que tiene varios departamentos o líneas de producto, parece lógico crear una hoja para cada departamento o para cada mes (o año).

Esta práctica no conlleva ninguna ventaja o beneficio y sólo hace que los sumarios y los cálculos sean más difíciles de realizar. Para demostrarlo supongamos que tenemos los datos de ventas por mes de una compañía con 20 departamentos. Queremos llevar todos los datos en un cuaderno con una hoja donde ponemos una tabla dinámica. La tabla dinámica nos permite analizar los datos con eficiencia y facilidad.

Una alternativa es crear una hoja para cada mes. Nuestro cuaderno tendrá 13 hojas, una para cada mes y una para contener la tabla dinámica. Como los datos no están en una única lista (o tabla) usamos la opción Rangos de consolidación múltiples. Esto nos obliga a agregar una por una cada hoja al construir la tabla y además los meses no son identificados como campos. Las hojas de nuestro cuaderno se ven así

La tabla dinámica resultante se ve así



Ahora tendremos que cambiar los nombres Elemento 1, Elemento 2, etc. , por los meses correspondientes (teniendo previamente que identificar que elemento corresponde a que mes).

Cada hoja de este cuaderno, sin la hoja Reporte, tiene 42 celdas, en total 504 celdas de datos y su peso es 78.5KB.

La alternativa recomendable es usar una única hoja para los datos. Esta hoja tiene tres campos: Departamento, Mes y Ventas

La tabla dinámica se crea simplemente y se ve así



La hoja datos tiene 723 celdas de datos y el cuaderno pesa 41.5KB, es decir, cerca de la mitad del Multihojas.xls.

La conclusión no es que bajo cualquier circunstancia hay que limitarse a una única hoja para contener los datos. Pero cuando se trate de analizar datos, dado que no hay ninguna herramienta que supere a las tablas dinámicas en eficiencia, la práctica recomendada es poner todos los datos en una única lista.

Otra ventaja, en especial para los usuarios de Excel 2007, es que una única lista permite el uso de tablas (listas en Excel 2003 y anteriores), como ya hemos mencionado en alguna nota anterior.

Technorati Tags: MS Excel

Buenas y malas prácticas en Excel – cuadros, tablas y listas.

El primer punto en la nota sobre malas prácticas en Excel de The Universe Divided, que mencioné en la nota de ayer, es usar cuadros (“using tables” en el original). Me he tomado la libertad de traducir “tables” como “cuadros” (y no tablas como pareciera que tiene que ser) por cierta confusión que existe entre los usuarios de Excel respecto a los términos tablas y listas. En la nota original el autor usar el término “table” como una matriz organizada de datos que se caracteriza por tener encabezamientos para las columnas y para las filas, como en este ejemplo:

A este tipo de matriz organizada de datos la llamaremos “cuadro”, para diferenciarla de listas y tablas.

El uso de cuadros es una mala práctica, por lo menos por dos motivos:

# - Esta forma de organizar datos hace que sea prácticamente imposible usar tablas dinámicas para analizar datos. Y si no podemos usar tablas dinámicas, estamos de hecho renunciando a una de las herramientas más poderosas de Excel. Para poder usar tablas dinámicas eficientemente tenemos que organizar los datos como en una tabla de una base de datos: campos (columnas) y registros (filas de datos). Por ejemplo, si queremos usar los datos del cuadro/matriz para construir una tabla dinámica, esto es lo que sucede

La columna A se convierte en el campo Ventas cuyos valores son los nombres de los meses del año (??!!).

# - No podemos convertir el rango de datos en una lista (Excel 2003) o tabla (Excel 2007). Nuevamente estaríamos renunciando al uso de una excelente herramienta de Excel. Por ejemplo, si aplicamos Datos—Listas, esto es lo que obtenemos Como en el caso de la tabla dinámica, podemos ver que el nombre del cuadro (Ventas) es interpretado como el encabezamiento de un campo, es decir, como parte de los datos.

Y volviendo sobre el tema de la confusión entre listas y tablas, digamos que en términos de Excel es lo mismo. Hasta la versión 2007, la funcionalidad se llama Listas (Datos—Lista). En Excel 2007 esta funcionalidad ha pasado a llamarse Tablas.