Manejo de pequeñas bases de datos en Excel – Acceso a base de datos externos con MS Query

Esta es otra nota de la serie sobre manejo de pequeñas bases de datos en Excel. Las entradas anteriores fueron

1 - Manejo de pequeñas bases de datos en Excel con tablas dinámicas (pivot tables), donde nos ocupamos de cómo construir una lista o tabla que sirva de base para generar una tabla dinámica (pivot table).

2 - Manejo de pequeñas bases de datos en Excel con tablas dinámicas (pivot tables) - Actualización de Datos, donde vimos como actualizar la tabla dinámica cada vez que los datos de la base de datos cambian.

3- Manejo de pequeñas bases de datos en Excel – Referencias dinámicas con nombres (NAMES), donde expliqué como generar una referencia al rango de la base de datos en la tabla dinámica, de manera que no haga falta actualizarlo manualmente con cada cambio.

En todas estas entradas la base de datos de la tabla dinámica era una lista que se encontraba en una hoja de Excel. A esta tabla le agregamos datos (o borramos datos de ella) manualmente.
En esta cuarta entrada nos ocuparemos de cómo acceder bases de datos externas con Excel. Para esta tarea Excel cuenta con un programa auxiliar, el MS Query.
Para los ejemplos de esta entrada he utilizado la base de datos Northwind, que forma parte del paquete Office. Estos archivos se encuentran (Office XP) en la carpeta C:\Program Files\Microsoft Office\Office10\Samples .

Para importar los datos externos a una hoja Excel usamos los comandos Datos--->Obtener datos externos--->Nueva consulta de base de datos.

Si se fijan en la barra inferior de la pantalla, verán que Excel a abierto un nuevo programa, el MS Query




En el diálogo que se abre, debemos señalar cual es nuestra fuente de datos



En nuestro ejemplo basta con señalar el tipo de base datos, MS Access, que ya figura en la lista de MS Query. A veces el tipo de base de datos no aparece en al lista y el acceso debe ser creado. En este ejemplo nos limitaremos al primer caso.
Luego de elegir la fuente, elegimos la tabla y/o los campos de la tabla que estamos interesados en importar



Luego de elegir la fuente, veremos la lista tablas disponibles. Para no complicar nuestro ejemplo elegiremos sólo una tabla. Al lado del nombre de cada tabla hay un símbolo "+". Al pulsarlo veremos la lista de campos de la tabla- Esto nos permite elegir cuales estamos interesados en importar. Cada campo ocupará una columna en la hoja de Excel.

En nuestro ejemplo elegimos la tabla "Alphabetical List of Products", y de ella los campos que aparecen en la imagen (eso se hace señalando los campos en la ventanilla izquierda y pulsando la flecha en dirección a la ventanilla derecha)



Luego podemos establecer criterios para la importación, por ejemplo productos cuyos precios sean mayores de 15



En el próximo diálogo podemos ordenar los datos de acuerdo a los campos



Finalmente podemos elegir si importar los datos directamente a la hoja de Excel, si ver los datos en el MS Query (luego se pueden importar desde allí) o crear un cubo OLAP (tema sobre el cual escribiré en el futuro)



Si elegimos importar los datos a una hoja Excel, debemos indicar a partir de qué celda comenzar (por lo general será A1)



Como pueden ver, Excel nos propone en este mismo diálogo crear una tabla dinámica.

Apretamos "Aceptar" y Excel importará los datos a la hoja



A partir de este momento podemos trabajar como con toda lista de datos en Excel, ordenar los datos, generar subtotales, usar filtros y generar tablas dinámicas.

Si en lugar de importar los datos a una hoja, elegimos la opción "Crear un informe de tabla dinámica", Excel abrira una plantilla de tabla dinámica vacía. En lugar de importar los datos a una hoja, Excel establece un vínculo con los datos en la tbla de la base de datos.

En la próxima entrada daré una breve explicación de cómo proceder cuando la fuente de datos no aparece en la lista de MS Query.

Categorías: Manejo de Datos_

Technorati Tags: MS Query, Importar Datos, Bases de Datos

Si te gustó esta entrada anotala en del.icio.us

Funciones SI (IF) en Excel – Como construir fórmulas anidadas compactas

Una tarea bastante corriente en Excel es construir fórmulas que combinan varias funciones. Una de las combinaciones "clásicas" es con la función SI (IF en la versión inglesa).
La función SI analiza una propuesta (Jorge mide más de 1.80 metros) y de acuerdo al resultado (verdadero o falso) da una respuesta (Jorge es alto). Si la propuesta resulta "falsa" el resultado será distinto (Jorge es bajo).
Por supuesto se pueden combinar (anidar) varias funciones SI, de manera que cada una sirva de argumento para la otra. Excel acepta hasta 7 funciones SI en una misma fórmula. El problema es que también con 3 o 4 funciones SI combinadas, la fórmula resulta larga y complicada. Sobre esto he escrito en la entrada sobre el uso de la función ELEGIR (Choose, en inglés).
En este ejemplo (les recomiendo descargar el archivo del ejercicio) veremos cómo construir fórmulas que anidan varias funciones SI, en forma compacta.
Para esto es necesario conocer el concepto de "nombres" (NAMES) en Excel. Si no están familiarizados con este concepto, pueden leer mis entradas sobre el tema:
Uso de Nombres (NAMES) en MS Excel
Usar Nombres en Excel para Formato Condicional
Uso de NOMBRES (NAMES) en Excel – Validación de Datos

En nuestro ejemplo suponemos la existencia de una tienda que tiene un club de clientes. Cada cliente puede pertenecer a una de las categorías: Platinum, Gold o Regular. De acuerdo a la categoría el miembro puede recibir determinados descuentos e acuerdo a esta tabla:


Además la tienda vende con el sistema "1+1", al comprar dos productos se recibe un descuento del 50% sobre el más barato.
Este ejercicio fue parte de un examen en un curso sobre Excel. Los alumnos tenían que proponer una fórmula que calculara el descuento adicional a los clientes.


Las fórmulas propuestas por los alumnos variaron entre 4 y 5 funciones SI combinadas. Si bajaron el archivo, pueden ver en la columna G la fórmula con 5 funciones SI

=SI(C2="Platinum",SI(Y(CONTAR(D2:E2)=2,F2>10000),F2*0.2,SI(Y(CONTAR(D2:E2)=2,F2<=10000),F2*0.1,SI(Y(CONTAR(D2:E2)=1,F2>10000),F2*0.1))),SI(Y(C2="Gold",CONTAR(D2:E2)=2,F2>15000),F2*0.1," "))

Si bien esta fórmula resuelve el problema, se puede escribir de otra manera usando solamente 2 funciones SI. La regla a seguir dice que la cantidad de funciones SI a combinar equivale a la cantidad de casos menos 1. Si bien hay cinco casos de descuento, como podemos ver en la tabla arriba, que de hecho hay tres casos: los que reciben 20% de descuento adicional; los que reciben 10% y los que no reciben descuento adicional.

Para construir la fórmula, primero defineremos los nombres que contengan las fórmulas para determinar el tipo de descuento.
Para esto escribimos las fórmulas en celdas laterales, lo que nos permitirá verificar su funcionamiento.

Fíjense en las fórmulas en el rango J2:M7. Los nombres y sus fórmulas son:
P_2_10 --- =Y(C2="Platinum",CONTARA(D2:E2)=2,F2>10000)
P_2 -------- =Y(C2="Platinum",CONTARA(D2:E2)=2)
P_10 ------- =Y(C2="Platinum",F2>10000)
G_2_15 --- =Y(C3="Gold",CONTARA(D3:E3)=2,F3>15000)

La función CONTARA nos permite contar la cantidad de productos comprados en cada compra. Usamos esta función y no CONTAR para evitara resultados erróneos cuando una de las celdas del rango está en blanco.
Los nombres los definimos usando el menú Insertar---Nombre---Definir

La forma más práctica de construir los nombres, es copiando las fórmulas de la barra de fórmulas y pegándolas en la ventanilla "Se refiere a" del diálogo Definir Nombre.
MUY IMPORTANTE: al construir los nombres la celda activa debe ser la primer celda en el rango relevante, en nuestro caso H2. Esto se debe a que en nuestras fórmulas usamos direcciones relativas (sin los símbolos $).

Dado que las condiciones P_2, P_10 y G_2_15 dan como resultado el mismo descuento (10%), las combinaremos en una sola condición usando la función O (OR en su versión inglesa).
Finalmente podemos escribir nuestra fórmula de la siguiente manera:

=SI(P_2_10,F2*0.2,(SI(O(P_2,P_10,G_2_15),F2*0.1,"")))

Como pueden ver (en la columna H), no sólo la fórmula es mucho más compacta, si no que el uso de nombres la convierte en explícita.


Categorías: Funciones&Formulas_, Manejo de Datos_


Technorati Tags: Nombres, Funcion SI en Excel, IF

Manejo de pequeñas bases de datos en Excel – Referencias dinámicas con nombres (NAMES).

En la entrada de ayer sobre tablas dinámicas vimos que cada vez que agregamos datos a la base de datos, debemos actualizar la referencia al rango. De no hacer esto, las nuevas líneas de datos no aparecerán en la tabla dinámica.
En el ejemplo de ayer hicimos esto manualmente. Excel nos permite construir referencias dinámicas, que se actualizan con los cambios en el tamaño de la base de datos. La técnica para hacer esto es utilizar "nombres" (NAMES).
Si no estás familiarizado con este tema, puedes ver mi nota sobre uso de nombres en Excel.
Como ya explicamos, los nombres pueden referirse no sólo a rangos sino también a fórmulas. Para crear el rango dinámico en nuestro caso definiremos un nombre que contendrá la formula DESREF (Offset en la versión inglesa). El archivo de este ejemplo se puede descargar aquí.
La sintaxis de esta fórmula es la siguiente; DESREF(referencia ;filas;columnas;alto;ancho) donde:

referencia: la celda en el ángulo superior derecho de la lista (en nuestro caso será A1);
filas: para nuestro uso será siempre 0
columnas: para nuestro uso será siempre 0
alto: la cantidad de filas en nuestra lista
ancho: la cantidad de columnas en nuestra lista.

Para convertir esta fórmula en dinámica, usaremos la función CONTARA para determinar los valores de los parámetros alto y ancho. Esta es nuestra fórmula:

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


como se puede ver comenzamos en la celda A1, contamos cuantos valores hay en la columna A (que representan la cantidad de líneas de la tabla) y también contamos la cantidad de valores en la línea 1 (que representan la cantidad de columnas en la lista).

Ahora sólo nos queda reemplazar la referencia al rango de datos en la lista por el nombre que acabamos de definir. Los pasos a seguir son:
1 – activamos la Hoja2 y ubicamos el mouse en alguna de las celdas de la tabla dinámica
2 – abrimos el menú Tabla Dinámica--->Asistente



3 – pulsamos el botón "atrás"; en el diálogo que se abre reemplzamos el rango



con el nombre (la forma más práctica es pulsar F3)





A partir de este momento, la referencia de la tabla dinámica se adaptará automáticamente de acuerdo a la cantidad de líneas que agreguemos, o que borremos, a la base de datos.

Categorías: Funciones&Formulas_, Manejo de Datos_


Technorati Tags: Tablas Dinamicas, Pivot Tables