Sugerencias y ayuda para Excel en español. Ejemplos de funciones y fórmulas, gráficos, automatización con Vba, Power Query y PowerPivot
jueves, junio 11, 2009
Transferir datos de Excel a Access – segunda nota.
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.
lunes, diciembre 22, 2008
Insertar un calendario permanente en Excel 2007.
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})
=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
miércoles, octubre 01, 2008
Agregando valores a un cuadro combinado (ComboBox).
Existen ciertas diferencias entre los controles que agregamos directamente en una hoja de Excel y aquellos que agregamos a un UserForm en el editor de Visual Basic.
Cuando ponemos un control ActiveX directamente en la hoja vemos las propiedades del control activando el modo Diseño y abriendo el cuadro de propiedades (ambos iconos aparecen en la barra de Cuadro de Controles). Todo esto lo hemos mostrado en la nota mencionada.
Cuando creamos un cuadro combinado (ComboBox) o cuadro de lista (ListBox) sobre un Userform tenemos que activar el panel de propiedades del editor de Vb (VBE) para ver las propiedades
La propiedad que define los valores de cuadro o de la lista es RowSource. Para agregar valores al cuadro podemos usar una referencia a un rango en una hoja de Excel o podemos hacerlo usando código Vba.
Para ejemplificar el primer caso supongamos que en rango A1:A12 de la Hoja1 tenemos una lista de los meses del año, que podemos crear fácilmente con el menú Opciones-Listas Personalizadas.
En la propiedad RowSource del control ComboBox1 ponemos la referencia, con la sintaxis apropiada (ver el signo de exclamación entre el nombre de la hoja y la referencia al rango)
Para comprobar si nuestro control ha recibido los valores, seleccionamos el UserForm y apretamos F5. El UserForm aparecerá en la hoja activa (en realidad "sobre" la hoja) y podemos apretar la flecha para ver los valores disponibles
Otra posibilidad es agregar los valores usando programación. También aquí se nos abren dos posibilidades.
Si los valores se encuentran en un rango de una hoja, como en el caso que acabamos de mostrar, podemos usar un código como éste
Sub agregar_valores()
UserForm1.ComboBox1.RowSource = "Hoja1!A1:A12"
UserForm1.Show
End Sub
Al correr esta macro se abre el UserForm y podemos ver que los valores son los del rango.
Otra posibilidad es agregar los valores usando el método Add Item de Vba:
Sub add_val()
With UserForm1.ComboBox1
.RowSource = " "
.AddItem "Enero"
.AddItem "Febrero"
.AddItem "Marzo"
.AddItem "Abril"
.AddItem "Mayo"
.AddItem "Junio"
.AddItem "Julio"
.AddItem "Agosto"
.AddItem "Setiembre"
.AddItem "Octubre"
.AddItem "Noviembre"
.AddItem "Diciembre"
End With
UserForm1.Show
End Sub
En resumen, el manejo de las propiedades de los controles es distinto dependiendo si los hemos creado directamente en la hoja con la barra de Controles o han sido programados con Visual Basic.
Technorati Tags: MS Excel
viernes, septiembre 26, 2008
Autocompletar en Validación de Datos
En notas anteriores ya hemos mostrado como podemos mejorar aún más esta función creando listas desplegables ordenadas o listas desplegables dependientes.
Pero lo que falta en validación de datos es la funcionalidad autocompletar. Esto es importante cuando tenemos listas con varias decenas o centenas de miembros.
Supongamos que creamos una lista desplegable con todos los países del mundo (o casi todos, según Wikipedia) . Esta lista tendrá 247 miembros. Para facilitar la búsqueda de un país determinado podemos ordenar la lista alfabéticamente, pero de todas maneras si buscamos Zimbabue o Yemen tendremos que pulsar el ratón o el teclado decenas de veces.
La solución es imitar en la lista de validación de datos el comportamiento Autocompletar como existe las celdas de una hoja. La idea es que si pulsamos B aparezca Bahamas, si pulsamos Br aparezca Brasil, etc.
Para lograr esto podemos incorporar a la hoja de Excel un control ActiveX. En este caso usaremos un cuadro combinado (ComboBox).
En nuestro modelo hemos puesto el cuadro combinado sobre la celda B1 y enseguida mostraremos como dirigimos la elección de la lista a la celda B3
Para poder seleccionar el objeto activamos primero el modo de diseño
Luego seleccionamos el cuadro combinado con un clic y abrimos el menú de las propiedades del objeto
En LinkedCell ponemos B3, el ListFillRange ponemos Hoja2!A1:A247 (que es donde tenemos la lista de países) y en MatchEntry ponemos 1-fmMatchEntryComplete
También podemos cambiar otras propiedades como la fuente y el aspecto del objeto.
Cerramos el cuadro de propiedades y cancelamos el modo de diseño haciendo clic sobre el icono.
Ahora al abrir la ventanilla del cuadro combinado vemos los primeros 8 miembros de la lista (también esta propiedad puede ser cambiada en el cuadro de propiedades del objeto). Los miembros aparecen en el orden en que se encuentran en el rango de origen.
Pero lo más interesante es que podemos escribir directamente en la ventanilla y los países aparecerán de acuerdo a las letras que hayamos escrito
Al poner B aparece automáticamente Bahamas
Si agregamos ahora "r", el valor se convierte en Brasil. Nótese que el valor en la celda ligada, B3, se actualiza instantáneamente
Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.
Technorati Tags: MS Excel
sábado, julio 21, 2007
Agrupar controles botones de opción en hojas Excel.
Últimamente he recibido varias consultas sobre como crear grupos de botones de opción.
Cuando agregamos varios botones de opción en una hoja, Excel acepta una sola celda vinculada para todos los controles. Es decir, Excel agrupa todos los botones de opción en un solo grupo, no importa dónde o como los ubiquemos. Inclusive si definimos para cada botón una celda vinculada distinta, veremos que en todos los controles aparece como celda vinculada la última que hemos definido.
La técnica para superar este inconveniente depende de qué tipo de control estemos usando. Excel permite agregar dos tipos de controles directamente en la hoja: los controles de la barra de Formularios y los del cuadro de controles (controles ActiveX).
Supongamos que queremos crear un formulario para definir nuevos clientes. En este formulario definimos el nombre del cliente, el tipo (mayorista o minorista) y el tipo de crédito (15, 30 o 45 días). Nuestro formulario se verá así
Si usamos los controles de la barra de formularios, el truco consiste en poner cada uno de los grupos de botones de opción dentro de controles "cuadro de grupo" distintos. Para crear el formulario seguimos estos pasos:
1 – Ponemos un fondo gris al rango B2:F20
2 – Seleccionamos la celda C4 y ponemos "Nombre"
3 – Combinamos y centramos el rango D4:E4, y le quitamos el fondo gris
4 – Abrimos la barra de formularios y arrastramos dos cuadros de grupo
5 - Dentro de uno de los cuadro de grupo ponemos los botones de tipo de cliente. En el otro ponemos los botones de tipo de crédito. Cambiamos los encabezamientos directamente en el control.
6 – Los botones de tipo de cliente están ligados a la celda H3; los de tipo de crédito a la celda H4.
7 – Agregamos un control "botón" que nos servirá para activar una macro que agregue el nuevo cliente a nuestra base de datos (la hoja "Clientes")
En la celda I3 ponemos la fórmula =ELEGIR(H3;"Mayorista";"Minorista"); en la celda I4 la fórmula =ELEGIR(H4;"15 días";"30 días";"45 días")
Si usamos los controles ActiveX de la barra Cuadro de Controles, el truco consiste en cambiar la definición del Groupname en la propiedades del botón y darle a los botones del grupo el mismo Groupname.
En nuestro ejemplo los botones de tipo de cliente tienen como Groupname "Tipo" y los del tipo de crédito "Crédito".
Otro punto importante es que a cada control le definimos una celda vinculada distinta, a diferencia de los controles de la barra de formularios.
Sobre cómo definir los distintos controles, pueden consultar mi nota mencionada al principio.
En general es más fácil trabajar con los controles de la barra de formularios que con los controles ActiveX de la barra de cuadro de controles. La contrapartida es que los controles ActiveX tienen más posibilidades y son más flexibles a nuestras necesidades.
La hoja con los ejemplos y las macros se pueden descargar aquí.
sábado, julio 15, 2006
Insertar un calendario permanente en hojas de calculo Excel con Controles ActiveX
Al activarlo tendremos acceso a muchos más controles (no todos disponibles para las hojas de cálculos). Uno de ellos es el Calendar Control, que nos permite insertar un calendario permanente en la hoja de cálculos.
Para insertar el calendario en una hoja Excel, procedemos como con los otros controles ActiveX, como mostramos en la entrada anterior.
Una vez insertado en la hoja, abrimos el menú de propiedades, para adpatar el calendario a nuestras necesidades
En este ejemplo de calendario en Excel, tenemos una tabla con ventas diarias en la Hoja2. En la Hoja1 insertamos el calendario, y lo ligamos (LinkedCell) a la celda B7 (que no será visible al estar cubierta por el calendario).
En la celda B14 introducimos esta fórmula, que usa la celda ligada B7 como argumento
=FECHANUMERO(B7)
Esta formula convierte el valor textual del calendario a una fecha. En la celda C14 introducimos una función BUSCARV, para obtener el valor de las ventas correspondientes a la fecha elegida en el calendario
=SI(B14>MAX(Hoja2!A:A);"fecha sin datos";BUSCARV(B14;Hoja2!A:B;2;0))
la condición lógica con la función MAX, nos permite evitar valores erróneos cuando se elige una fecha que no exste en la tabla de ventas.
Ahora, podemos elegir la fecha en el calendario y ver la suma de ventas correspondientes en la celda C14.
Además del menú de propiedades en la barra de herramientas, también se puede cliquear con el boton izquierda el control y abrir este diálogo
Categorías: Varios_
Technorati Tags: Calendario en Excel, Controles ActiveX en Excel
Controles ActiveX en hojas de calculo Excel
En las entradas anteriores hablamos sólo sobre los controles Excel, que aparecen en la barra Formularios. Estos controles son menos flexibles y tienen menos posibilidades que los controles ActiveX, pero por lo general su funcionalidad es suficiente para cualquier usuario Excel promedio.
Sobre las diferencias entre los controles de la barra Formularios y los controles ActiveX en Excel, pueden leer el resumen de John Walkenbach (en inglés).
Los controles ActiveX son mucho más flexibles y ofrecen muchas más posibilidades, pero por lo general, requieren el uso de Vba.
Para usar los controles ActiveX en la hoja de cálculo tenemos que activar la barra Cuadro de Controles (clic en la zona de barras de herramientas y selección o Ver---Barra de Herramientas---Cuadro de Controles)
En la parte superior de la barra aparecen tres iconos: modo de diseño, propiedades y ver código.
Estos tres iconos nos permiten cambiar el aspecto y las propiedades del control y también agregarle funcionalidad con Vba (macros). Estas tareas se realizan estando en "modo de diseño". Una vez terminada la adaptación del control a nuestras necesidades, debemos pulsar el icono para salir del modo de diseño y poder utilizar el control.
Para insertar un control ActiveX procedemos como con los controles Excel, cliqueando con el mouse el control y ubicándolo en la hoja de cálculo.
Una de las diferencias con los controles Excel, es que los controles ActiveX tienen muchas más propiedades que pueden ser adaptadas. Para acceder al menú de las propiedades activamos el control y cliqueamos el icono de propiedades en la barra de Cuadro de Controles.
Por ejemplo, insertamos una casilla de verificación y abrimos el menú de propiedades
Como podemos ver, existen un gran número de propiedades, como el color, la fuente y otras. La propiedad "LinkedCell" nos permite ligar el "estado" del control a una celda en la hoja. Esta celda puede ser utilizada luego como argumento en una función. Por ejemplo, he cambiado el texto de la casilla a prueba de "prueba de casilla", el color del fondo y la he ligado a la celda C1. Al final del proceso apretamos el icono "modo de diseño" para activar el control. A partir de ahora, de acuerdo a la situación de la casilla, en la celda C1 aparecerá el valor VERDADERO (casilla señalada) o FALSO.
Como ya hemos dicho, podemos utilizar el valor de la celda C1 como argumento en una función. Por ejemplo, en la celda A4 podemos escribir esta fórmula
=SI(C1;"casilla señalada";"casilla sin señalar")
y de acuerdo a la situación del control, aparecerá uno de los dos textos.
En la esquina inferior derecha de la barra del Cuadro de Controles, está el icono "más controles". Al activarlo tendremos acceso a muchos más controles (no todos disponibles para las hojas de cálculos).
En la próxima entrada veremos como insertar un calendario permanente en una hoja de cálculo de Excel.
Categorías: Varios_
Technorati Tags: Controles en Excel
martes, marzo 28, 2006
Excel - Agregando controles en hojas de cálculo
Las versiones más recientes de Excel tienen dos tipos de controles, los controles ActiveX (la barra de la izquierda en la figura) y los controles de la barra de herramientas Formularios
En esta entrada daremos un ejemplo práctico del uso de los controles de la barra de herramientas Formularios. No nos detendremos a analizar las diferencias entre los dos tipos de controles ni el uso de los ActiveX.
Como es costumbre de este blog, ilustraremos el uso de los controles con un ejemplo.
Supongamos que hemos diseñado un formulario en una hoja de cálculo Excel, para calcular precios de productos. El precio depende de si el cliente es miembro del club de compras y de las condiciones de pago. El formulario del ejemplo se puede descargar aquí.
En este formulario hemos puesto 4 controles: una casilla de verificación (si el cliente es miembro del club marcamos la "V") y tres botones de opción para señalar la forma de pago.
Estos controles funcionan de la siguiente manera:
1 – Activamos la barra de formularios y pulsamos sobre el control requerido (en nuestro caso la casilla de verificación)
2 – ubicamos el control en el lugar requerido y abrimos el menú de formato del control
3 – en la división "control" creamos un vínculo con alguna celda de la hoja.
En esta celda Excel anotará un valor de acuerdo a al situación del control. Si la casilla está señalada ("v"), en la celda vinculada aparecerá el valor "VERDADERO"; de lo contrario Excel anotará el valor "FALSO". La celda vinculada que hemos elegido es A3 de manera que el control oculte el valor de la celda.
4 – Procedemos de la misma manera con los botones de opción. En este caso los tres controles están vinculados a la misma celda (A7 en nuestro ejemplo). El valor en la celda vinculada corresponde al orden de los controles. Es decir, si el control activo es el primero (Contado), el valor de la celda vinculada será "1". Si el control activo es "Hasta 30 días" el valor de la celda vinculada será "2" y así sucesivamente.
Los valores de las celdas vinculadas funcionan como argumentos en las fórmulas que calculan los descuentos
- en la celda C23 tenemos la fórmula =SI(A3=VERDADERO,D22*10%,0)
- en la celda C24 tenemos la fórmula =D22*ELEGIR(A7,10%,5%,0)
De esta manera, las opciones marcadas en los controles nos permiten "controlar" los resultados de las fórmulas.
En el rango A16:A21 usamos validación de datos, con la opción "lista". En la Hoja2 hemos puesto una lista de precios. En esta hoja hemos definidos dos nombres de rangos, uno para la lista de la validación de datos y el otro para la fórmula BUSCARV que calcula los precios de los productos en el rango C16:C21.
Para evitar valor erróneos (#N/A, #DIV/0!, etc), combinamos las funciones de búsqueda con la función de control ESBLANCO.
Categorías: Funciones&Formulas_, LOOKUPS_
Technorati Tags: Controles en hoja Excel, BUSCARV, VLOOKUP, Funcion ELEGIR