Rangos dinámicos con Listas

Una tarea frecuente en Excel es crear rangos dinámicos. La técnica más difundida es crear un nombre (Insertar-Nombres-Definir) con una fórmula que combine DESREF y CONTARA.

Una técnica alternativa más sencilla es usar Listas (Excel 2003) o Tablas (Excel 2007). Esta funcionalidad es muy útil y permite simplificar nuestros modelos en Excel.

En una nota anterior mostramos como crear con facilidad un gráfico dinámico usando Listas. En esta nota mostraremos cómo crear un modelo dinámico.

Como ejemplo construiremos un modelo para manejar el inventario de un almacén/depósito. En un cuaderno Excel creamos dos hojas: “movimientos” y “saldos”. En la primera anotamos los movimientos de los productos en el almacén (entradas – salidas); en mostramos los saldos actualizados de los productos.





En la hoja “movimientos” tenemos ahora un cuadro de datos en el rango A1:D31. Para transformar este rango en Lista, usamos el menú Datos-Lista (o Ctrl+Q)






Al apretar Aceptar veremos que Excel selecciona todo el rango, activa Autofiltro y en la primer fila libre aparece un asterisco azul. A partir de este momento, cada vez que agreguemos datos a la lista, ésta se expandirá automáticamente.

En la celda A1 de la hoja “saldos” combinamos texto y funciones para crear un título dinámico


="Saldos a la fecha "&TEXTO(MAX(movimientos!C2:C31),"dd/mm/yyyy")

Como pueden ver usamos una referencia estática al rango de las fechas en la hoja “movimientos”.
Para calcular los saldos actualizados usamos la fórmula

=SUMAR.SI(movimientos!$A$2:$A$31,saldos!A4,movimientos!$D$2:$D$31)


También aquí usamos rangos “normales”.

Ahora agregamos los movimientos del día 08/01/2009



Cuando pasamos a la hoja “saldos” vemos que tanto el título como los saldos se han actualizados. Así de simple!



En Excel 2007, el mecanismo es similar, pero la funcionalidad Lista ha pasado a llamarse Tabla. Para convertir un rango en Tabla usamos el icono Tabla en la pestaña Insertar



Tanto en Excel 2003 como en Excel 2007, la forma más cómoda y eficiente de agregar datos en la lista/tabla, es usando Tab.



Technorati Tags: MS Excel

Transferir datos de Excel a Access – segunda nota.

En la nota anterior vimos como transferir datos de Excel a Access manualmente usando los mecanismos nativos de Access.
Otra alternativa es exportar los datos de Excel a Access programáticamente, es decir usando Vba.

Para nuestro ejemplo usaremos nuevamente el archivo de la nota anterior.

En la nota anterior usamos los mecanismos de Access para importar los datos de Excel.
Al realizar la tarea con Vba haremos el camino a la inversa, decir crearemos una conexión a Access desde Excel. Esto lo haremos usando objetos de la ADO library (ADO = ActiveX Data Objects).

Antes de sumergirnos en los vericuetos del código, una observación importante: para poder usar las funciones de ADO en Excel debemos primero crear una referencia con el menú Tools-Reference del editor de Vb





Como en el ejemplo anterior, empezamos por crear una base de datos que contiene una tabla a la cual queremos exportar los datos de Excel. Nuestra base de datos (bd1.mdb) contiene la tabla Ventas que por ahora está vacía



Sin entrar demasiado en detalles técnicos, podemos describir la tarea de la siguiente manera:

# - creamos una conexión a la base de datos

#- definimos en qué tabla de la base de datos queremos poner los datos

# - definimos un objeto que contenga los datos a transferir (recordset)

# - definimos donde se encuentran los datos a transferir (en nuestro ejemplo el cuaderno tiene dos hojas con datos)

# - transferimos los datos

He agregado la hoja Parámetros en la cual podemos definir donde se encuentra la base de datos, cuál es la tabla a la cual queremos copiar los datos y en qué cuaderno se encuentran. De esta manera, podemos controlar los parámetros del código con facilidad.


Como ven he agregado también la línea a partir de la cual hay que transferir los datos de la hoja. En general los datos empiezan en la fila 2 (la 1 está ocupada por los encabezamientos). También podemos elegir de qué hoja pasar los datos, con una lista desplegable en la celda B3.
El código de la macro es el siguiente

Sub ExcelaAccess_ADO()

Dim Conn As ADODB.Connection, RecSet As ADODB.Recordset
Dim fila As Long, primerFila As Integer, ultimaFila As Long, iX As Long
Dim dataSource As String, Tabla As String
Dim wsName As String


'definimos los parametros que seran usados por el codigo

dataSource = Sheets("parametros").[B1]
Tabla = Sheets("parametros").[B2]
wsName = Sheets("parametros").[B3]
primerFila = Sheets("parametros").[B4]


' establecemos la conexion a la base de datos

Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & _
dataSource & ";"

' definimos un recordset
Set RecSet = New ADODB.Recordset
RecSet.Open Tabla, Conn, adOpenKeyset, adLockOptimistic, adCmdTable

ultimaFila = WorksheetFunction.CountA(Sheets(wsName).Range("A:A"))

For iX = primerFila To ultimaFila 'mientras que la celda la columna A
'tenga un valor transferimos la fila
With RecSet
.AddNew
.Fields("Factura") = Sheets(wsName).Cells(iX, 1).Value
.Fields("Fecha") = Sheets(wsName).Cells(iX, 2).Value
.Fields("Producto") = Sheets(wsName).Cells(iX, 3).Value
.Fields("Descripcion") = Sheets(wsName).Cells(iX, 4).Value
.Fields("Cantidad") = Sheets(wsName).Cells(iX, 5).Value
.Fields("Precio") = Sheets(wsName).Cells(iX, 6).Value
.Fields("Total") = Sheets(wsName).Cells(iX, 7).Value
.Update
End With
Next iX


RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing
End Sub



Este código debe ponerse en un módulo común de Vba.

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