El uso más corriente de estos gráficos es representar y comparar datos de dos poblaciones distintas.
Technorati Tags: MS Excel
sábado, abril 26, 2008
Crear gráficos tipo pirámide en Excel
En mi blog sobre gráficos y presentación de datos acabo de publicar una nota sobre cómo crear gráficos de tipo piramide en Excel.
El uso más corriente de estos gráficos es representar y comparar datos de dos poblaciones distintas.
El uso más corriente de estos gráficos es representar y comparar datos de dos poblaciones distintas.
domingo, abril 20, 2008
Consolidar datos de hojas Excel con MS Query
Supongamos que tenemos una hoja en un cuaderno de Excel con los siguientes datos
En otra hoja tenemos estos datos
Nuestra tarea consiste en consolidar los datos de ambas hojas en una sola hoja, como esta
No podemos hacerlo con Datos—Consolidar o con Tablas Dinámicas. Una posibilidad es agregar el campo Cliente en la segunda hoja usando la función BUSCARV basándonos en el número de factura.
Este método se vuelve muy ineficiente cuando tenemos que manejar una gran cantidad de datos. No sólo que BUSCARV puede ser muy lenta cuando hacemos una búsqueda exacta sino que también tendríamos que estar actualizando las fórmulas cada vez que agregamos datos.
En esta nota mostraremos un método mucho más eficiente usando un programa adjunto de Excel, y poco conocido, el MS Query, sobre el cual ya hemos escrito en el pasado.
Supongamos que las hojas que hemos mostrada más arriba se encuentran en el cuaderno Datos.xls.
Empezamos por abrir un cuaderno en blanco y en este cuaderno abrimos el menú Datos-Obtener Datos externos-Nueva consulta de base de datos
En el diálogo que se abre elegimos la opción Excel Files
Ubicamos la carpeta donde guardamos el archivo (en nuestro caso Datos.xls) y elegimos el archivo
En el diálogo Elegir Columna veremos las dos hojas que contiene nuestro cuaderno
Para ver las tablas, que de hecho son las hojas del cuaderno, debemos asegurarnos que la opción Tablas del sistema esté seleccionada. Para hacer esto apretamos el botón Opciones
Nuestro objetivo es agregar los campos Fecha y Cliente a la hoja Ventas. Apretamos el signo + de Facturas para ver los campos, y elegimos Fecha y Cliente
Ahora señalamos la tabla Ventas y pasamos todos los campos apretando la flecha correspondiente
Al hacer esto veremos este mensaje
MsQuery nos pide que creemos un vínculo entre ambas tablas (unión). Haremos esto más adelante. Por ahora apretamos Aceptar y seguimos
Al apretar Aceptar se abre la interfaz del MS Query. En la parte superior vemos las tablas de datos que hemos importado al MS Query. En la zona de datos, vemos todos los datos de ambas tablas. Como podrán comprobar, en esta tabla hay 90 registros. Esto se debe a que no hemos creado ningún vínculo específico entre las dos tablas y por lo tanto se crean registros redundantes.
Existen distintos tipo de vínculos entre tablas. Quienes tengan conocimientos de SQL, podrán resolver el caso fácilmente. Para quien no conozca SQL, MS Query ofrece un asistente, que es lo que emplearemos en este ejemplo.
En el menú de MS Query elegimos Tablas-Uniones
En la ventana de diálogo del asistente nos aseguramos que en Izquierda y Derecha figure el campo Factura, en una ventanilla el campo de la tabla Facturas y en la otra él de Ventas. Elegimos la opción 3 de las opciones de unión (Left Outer Join, en SQL)
Apretamos Agregar y luego Cerrar. A continuación apretamos el botón de Ejecutar consulta ahora
El resultado es la tabla que estamos buscando. Nótese la forma gráfica en la que MS Query indica el tipo de unión que hemos creado entre las tablas
Todo lo que nos queda por hacer es transferir los datos al cuaderno de Excel que hemos abierto para el caso. Esto lo hacemos con el menú Archivo-Devolver datos a Excel
Excel activa la hoja de la cual hemos partido y abre un diálogo donde podemos elegir desde que celda importar los datos. Existen varias opciones que son visibles al apretar los botones Propiedades y Modificar consulta, y que invito a que investiguen por vuestra cuenta.
Al apretar Aceptar Excel importa los datos a la hoja.
Como ven, Excel también pone la barra de comandos Datos Externos. Esta barra nos permite renovar los datos, con el icono "!" y también editar la consulta. Una de las grandes ventajas de este método es que Excel guarda un vínculo con las tablas de datos lo que nos permite actualizar con comodidad y eficiencia la hoja de Excel cada vez que cambiemos o agreguemos datos en las tablas de origen.
Quien quiera practicar el ejercicio, puede descargar el archivo datos.xls aquí.
En otra hoja tenemos estos datos
Nuestra tarea consiste en consolidar los datos de ambas hojas en una sola hoja, como esta
No podemos hacerlo con Datos—Consolidar o con Tablas Dinámicas. Una posibilidad es agregar el campo Cliente en la segunda hoja usando la función BUSCARV basándonos en el número de factura.
Este método se vuelve muy ineficiente cuando tenemos que manejar una gran cantidad de datos. No sólo que BUSCARV puede ser muy lenta cuando hacemos una búsqueda exacta sino que también tendríamos que estar actualizando las fórmulas cada vez que agregamos datos.
En esta nota mostraremos un método mucho más eficiente usando un programa adjunto de Excel, y poco conocido, el MS Query, sobre el cual ya hemos escrito en el pasado.
Supongamos que las hojas que hemos mostrada más arriba se encuentran en el cuaderno Datos.xls.
Empezamos por abrir un cuaderno en blanco y en este cuaderno abrimos el menú Datos-Obtener Datos externos-Nueva consulta de base de datos
En el diálogo que se abre elegimos la opción Excel Files
Ubicamos la carpeta donde guardamos el archivo (en nuestro caso Datos.xls) y elegimos el archivo
En el diálogo Elegir Columna veremos las dos hojas que contiene nuestro cuaderno
Para ver las tablas, que de hecho son las hojas del cuaderno, debemos asegurarnos que la opción Tablas del sistema esté seleccionada. Para hacer esto apretamos el botón Opciones
Nuestro objetivo es agregar los campos Fecha y Cliente a la hoja Ventas. Apretamos el signo + de Facturas para ver los campos, y elegimos Fecha y Cliente
Ahora señalamos la tabla Ventas y pasamos todos los campos apretando la flecha correspondiente
Al hacer esto veremos este mensaje
MsQuery nos pide que creemos un vínculo entre ambas tablas (unión). Haremos esto más adelante. Por ahora apretamos Aceptar y seguimos
Al apretar Aceptar se abre la interfaz del MS Query. En la parte superior vemos las tablas de datos que hemos importado al MS Query. En la zona de datos, vemos todos los datos de ambas tablas. Como podrán comprobar, en esta tabla hay 90 registros. Esto se debe a que no hemos creado ningún vínculo específico entre las dos tablas y por lo tanto se crean registros redundantes.
Existen distintos tipo de vínculos entre tablas. Quienes tengan conocimientos de SQL, podrán resolver el caso fácilmente. Para quien no conozca SQL, MS Query ofrece un asistente, que es lo que emplearemos en este ejemplo.
En el menú de MS Query elegimos Tablas-Uniones
En la ventana de diálogo del asistente nos aseguramos que en Izquierda y Derecha figure el campo Factura, en una ventanilla el campo de la tabla Facturas y en la otra él de Ventas. Elegimos la opción 3 de las opciones de unión (Left Outer Join, en SQL)
Apretamos Agregar y luego Cerrar. A continuación apretamos el botón de Ejecutar consulta ahora
El resultado es la tabla que estamos buscando. Nótese la forma gráfica en la que MS Query indica el tipo de unión que hemos creado entre las tablas
Todo lo que nos queda por hacer es transferir los datos al cuaderno de Excel que hemos abierto para el caso. Esto lo hacemos con el menú Archivo-Devolver datos a Excel
Excel activa la hoja de la cual hemos partido y abre un diálogo donde podemos elegir desde que celda importar los datos. Existen varias opciones que son visibles al apretar los botones Propiedades y Modificar consulta, y que invito a que investiguen por vuestra cuenta.
Al apretar Aceptar Excel importa los datos a la hoja.
Como ven, Excel también pone la barra de comandos Datos Externos. Esta barra nos permite renovar los datos, con el icono "!" y también editar la consulta. Una de las grandes ventajas de este método es que Excel guarda un vínculo con las tablas de datos lo que nos permite actualizar con comodidad y eficiencia la hoja de Excel cada vez que cambiemos o agreguemos datos en las tablas de origen.
Quien quiera practicar el ejercicio, puede descargar el archivo datos.xls aquí.
Technorati Tags: MS Excel
viernes, abril 18, 2008
Formato condicional por bloques – segunda nota
Uno de lo usos de formato condicional es facilitar la lectura de tablas, señalando con un fondo de color determinadas filas o columnas.
En la primer nota sobre cómo facilitar la lectura de tablas con formato condicional vimos algunas técnicas para señalar filas en forma alternada (por ejemplo, poner un fondo gris a todas las filas impares, o por intervalos determinados).
En la primer nota sobre formato condicional por bloques, vimos como señalar la primer fila de un bloque de datos
En realidad lo que estaba buscando cuando empecé a escribir la nota era como poner un fondo en forma alternada a todo el bloque, es decir, esto
Partiendo de la premisa que nuestra tabla está siempre ordenada por sucursal, queremos poner un fondo alternado que nos ayude a visualizar cada bloque de datos. Queremos que esta funcionalidad sea dinámica, es decir, si agregamos o quitamos líneas a la tabla, el fondo del bloque se adaptará automáticamente.
El problema que debemos resolver es encontrar un criterio que se pueda evaluar como VERDADERO para el primer bloque, FALSO para el segundo, nuevamente VERDADERO para el tercero y así sucesivamente.
Para facilitar la explicación, mostraremos varias columnas auxiliares, pero la solución definitiva utilizará una única columna auxiliar.
Empezamos por crear una columna auxiliar donde evaluamos si el contenido de una celda es distinto del de la celda inmediata superior
El doble signo "-" en la fórmula fuerza a Excel a convertir el valor VERDADERO en 1 y el resultado FALSO en 0. Lo mismo puede hacer multiplicando la expresión por 1.
En la segunda columna auxiliar ponemos esta fórmula en la celda E2
=CONTAR.SI($D$2:D2;1)
y la copiamos a lo largo del rango hasta E16
Como pueden ver, todas las celdas relacionadas a la Sucursal 1, dan como resultado 1, las de Sucursal 2 dan 2 y así sucesivamente.
Así tenemos un número que identifica cada bloque. Pero lo más importante es que tenemos una serie donde se alternan los números pares y los impares. De esta manera podemos usa la función ES.IMPAR (ISODD en inglés) para generar una serie de valores VERDADERO y FALSO que se alternan con cada bloque de sucursales
Podemos prescindir de las columnas auxiliares E, F y G usando esta fórmula
=ISODD(CONTAR.SI($D$2:$D2;1))
Para mayor comodidad encapsulamos esta fórmula en un nombre, "fcc"
Ahora, seleccionamos la tabla y aplicamos formato condicional usando nuestra fórmula, que hemos puesto en el nombre "fcc" como criterio
Como ven, sólo hemos dejado la columna auxiliar D, que usamos en nuestra fórmula.
El archivo con el ejemplo se puede descargar aquí.
En la primer nota sobre cómo facilitar la lectura de tablas con formato condicional vimos algunas técnicas para señalar filas en forma alternada (por ejemplo, poner un fondo gris a todas las filas impares, o por intervalos determinados).
En la primer nota sobre formato condicional por bloques, vimos como señalar la primer fila de un bloque de datos
En realidad lo que estaba buscando cuando empecé a escribir la nota era como poner un fondo en forma alternada a todo el bloque, es decir, esto
Partiendo de la premisa que nuestra tabla está siempre ordenada por sucursal, queremos poner un fondo alternado que nos ayude a visualizar cada bloque de datos. Queremos que esta funcionalidad sea dinámica, es decir, si agregamos o quitamos líneas a la tabla, el fondo del bloque se adaptará automáticamente.
El problema que debemos resolver es encontrar un criterio que se pueda evaluar como VERDADERO para el primer bloque, FALSO para el segundo, nuevamente VERDADERO para el tercero y así sucesivamente.
Para facilitar la explicación, mostraremos varias columnas auxiliares, pero la solución definitiva utilizará una única columna auxiliar.
Empezamos por crear una columna auxiliar donde evaluamos si el contenido de una celda es distinto del de la celda inmediata superior
El doble signo "-" en la fórmula fuerza a Excel a convertir el valor VERDADERO en 1 y el resultado FALSO en 0. Lo mismo puede hacer multiplicando la expresión por 1.
En la segunda columna auxiliar ponemos esta fórmula en la celda E2
=CONTAR.SI($D$2:D2;1)
y la copiamos a lo largo del rango hasta E16
Como pueden ver, todas las celdas relacionadas a la Sucursal 1, dan como resultado 1, las de Sucursal 2 dan 2 y así sucesivamente.
Así tenemos un número que identifica cada bloque. Pero lo más importante es que tenemos una serie donde se alternan los números pares y los impares. De esta manera podemos usa la función ES.IMPAR (ISODD en inglés) para generar una serie de valores VERDADERO y FALSO que se alternan con cada bloque de sucursales
Podemos prescindir de las columnas auxiliares E, F y G usando esta fórmula
=ISODD(CONTAR.SI($D$2:$D2;1))
Para mayor comodidad encapsulamos esta fórmula en un nombre, "fcc"
Ahora, seleccionamos la tabla y aplicamos formato condicional usando nuestra fórmula, que hemos puesto en el nombre "fcc" como criterio
Como ven, sólo hemos dejado la columna auxiliar D, que usamos en nuestra fórmula.
El archivo con el ejemplo se puede descargar aquí.
Technorati Tags: MS Excel
Suscribirse a:
Entradas (Atom)