Manual de excel Financiero CestecDescripción completa
Manual de capacitacion de excel 2010 para todo los estudiantes y educadores.
Manual super completisimo de aplicado a la programación de visual basic en Excel.Descripción completa
Manual de Macros Excel
Manual Excel
manual excel- primii pasiFull description
Guía o manual de referencia de VBA para aplicaciones, colecciones y objetos en macros de ExcelDescripción completa
maualDescripción completa
Manual de Tips Excel 2010 Filtro Avanzado – valores únicos en otra hoja Ya hemos mencionado en el pasado la posibilidad de extraer valores únicos de una lista con Filtro Avanzado Avanzado.. En Excel 2007 usamos Datos-rdenar ! Filtrar-Avanzadas Filtrar-Avanzadas
El problema con este m"todo# tanto con Excel $l%sico como con Excel 2007# es &ue aparentemente podemos extraer la lista de valores únicos s'lo a la ho(a &ue contiene la lista
)ero si leemos con atenci'n el mensa(e de Excel# vemos &ue dice "Sólo se puede copiar los datos filtrados a la hoja activa" . Y remarco *ho(a activa*# !a &ue esta es la clave para
resolver el *problema*. $uando &ueremos copiar valores únicos de una lista a una u na ho(a distinta de la ho(a &ue contiene los datos# sencillamente activamos el Filtro Avanzado desde la ho(a &ue recibir% la lista de valores únicos. De esta manera la ho(a activa es la &ue recibir% los valores únicos. +eamos el caso en nuestro e(emplo. ,a lista de nombres# con varios valores repetidos# est% en la ho(a *lista* nuestro ob(etivo es copiar la lista de valores únicos en la ho(a *únicos*. *ú nicos*. Eleimos la celda de la ho(a *únicos* donde &ueremos poner los valores únicos /A en nuestro caso1 ! activamos Filtro Avanzado ! ponemos todas las deiniciones excepto *3ano de la lista*
Ahora se4alamos el rano de la lista
)ero si leemos con atenci'n el mensa(e de Excel# vemos &ue dice "Sólo se puede copiar los datos filtrados a la hoja activa" . Y remarco *ho(a activa*# !a &ue esta es la clave para
resolver el *problema*. $uando &ueremos copiar valores únicos de una lista a una u na ho(a distinta de la ho(a &ue contiene los datos# sencillamente activamos el Filtro Avanzado desde la ho(a &ue recibir% la lista de valores únicos. De esta manera la ho(a activa es la &ue recibir% los valores únicos. +eamos el caso en nuestro e(emplo. ,a lista de nombres# con varios valores repetidos# est% en la ho(a *lista* nuestro ob(etivo es copiar la lista de valores únicos en la ho(a *únicos*. *ú nicos*. Eleimos la celda de la ho(a *únicos* donde &ueremos poner los valores únicos /A en nuestro caso1 ! activamos Filtro Avanzado ! ponemos todas las deiniciones excepto *3ano de la lista*
Ahora se4alamos el rano de la lista
Al apretar *Aceptar* los valores únicos ser%n copiados al rano deseado. Antes de descubrir esta soluci'n /! supono &ue no so! el primero en descubrirla1 me hab5a embarcado a escribir una macro para hacer la tarea. 6orale(a dedicar siempre unos seundos para evaluar si entendemos lo &ue nos dicen.
Validación de datos en varias hojas – códio !ejorado 8racias al comentario del amio 9uan 6unevar en mi nota anterior# vimos &ue el c'dio para evitar duplicados a trav"s de varias ho(as tiene un inconveniente si la propiedad *mover despu"s de presionar Entrar* est% activada el c'dio puede borrar el contenido de la celda e&uivocada.
)ara &ue el c'dio de la anterior uncione# la propiedad *mover despu"s de presionar Entrar* debe estar desactivada
$omo no podemos saber de antemano cu%l es la deinici'n del cuaderno debemos escribir un c'dio para cubra todas las posibilidades. ,o &ue debemos tomar en cuenta es el orden
de los eventos cuando apretamos Entrar. El c'dio evalúa el valor de la celda &ue era la celda activa al disparar el evento /:aret1. Es decir# si la opci'n de mover la selecci'n despu"s de apretar Entrar no est% activada# borramos el contenido de la celda activa &ue es la misma &ue :aret si se mueve a la iz&uierda :aret se encuentra a la derecha de la celda activa si se mueve hacia aba(o# :aret se encuentra arriba si se mueve hacia arriba# :aret es la celda inmediata inerior. ;n caso particular es si la selecci'n se mueve a la iz&uierda. En este caso# si la celda evaluada est% en la columna A# :aret coincide con la celda activa !a &ue Excel no puede seleccionar una celda &ue no existe. En deinitiva# el c'dio es el siuiente Sub valid_accross_sheets(valValue)
If iValCalc > 1 Then MsgBox "El valor " & ActiveCell.Value & " ya existe" Select Case Application.MoveAfterReturn Case Is = False
ActiveCell.ClearContents Case Else Select Case Application.MoveAfterReturnDirection Case Is = xlDown ActiveCell.Offset(-1, 0).ClearContents Case Is = xlUp ActiveCell.Offset(1, 0).ClearContents Case Is = xlToRight
ActiveCell.Offset(0, -1).ClearContents Case Is = xlToLeft If ActiveCell.Column = 1 Then
ActiveCell.ClearContents Else ActiveCell.Offset(0, 1).ClearContents End If End Select End Select
End If
End Sub
Ejercicio
Validación de datos en !últiples hojas de un cuaderno Excel All% por el 2007 publi&u" dos notas sobre c'mo aplicar validaci'n de datos entre valores &ue se encuentran en varias ho(as de un mismo cuaderno. ,a idea era impedir la entrada de valores duplicados en el cuaderno# sin importar en &ue ho(a. Despu"s de una bús&ueda en la
En las notas mostr" dos m"todos con 'rmulas en contradicci'n con el comentario de ;mlas. El problema con los m"todos &ue expuse en mis notas es &ue no funcionan. As5 &ue como esto de contrici'n ! arrepentimiento /&ue viene a ser lo mismo1# a&u5 va mi nota sobre como validar datos a trav"s de las ho(as de un cuaderno utilizando +ba /macros1. >iuiendo con el e(emplo &ue expuse en esas notas# tenemos un cuaderno con tres ho(as en cada ho(a ha! una lista de nombres. ,a idea es &ue no podamos inresar en cual&uiera de las listas un nombre &ue !a existe en cual&uiera de las otras listas.
)ara cada lista hemos creado un nombre &ue se reiere al rano en orma din%mica /con la unci'n DE>3EF1
?DE>3EF/@o(aBAB200$C:A3A/@o(aBABA1-1 ?DE>3EF/@o(a2BAB200$C:A3A/@o(a2BABA1-1 ?DE>3EF/@o(aBAB200$C:A3A/@o(aBABA1-1 ,o &ue hacemos ahora es crear un evento de manera &ue cuando se produzca un cambio en aluna de las ho(as# el evento dispare una macro &ue controle si el nuevo valor introducido en aluna de las tres listas !a existe. El c'dio de la macro &ue hace esta tarea es Sub valid_accross_sheets(valValue)
ActiveCell.ClearContents MsgBox "El valor " & ActiveCell.Value & " ya existe" End If
End Sub
Esta macro usa la unci'n $C:A3.>< de Excel para comprobar cuantas veces aparece el valor de la celda activa /en la &ue hemos introducido el valor1. >i el valor aparece m%s de una vez# el contenido de la celda activa es borrado ! aparece un mensa(e diciendo &ue el valor !a existe. El valor de la celda activa es pasado a la macro por evento orbooG>heet$hane del ob(eto :hisorboo. ;samos este evento para evitar tener &ue proramar un evento para cada ho(a
El c'dio es mu! sencillo hace una llamada a la macro valid&accross&sheets(valalue% pasando el valor de :aret /la celda en la &ue hemos inresado el nuevo valor1. ;n detalle a tener en cuenta es &ue los ranos de los nombres deben ser continuos. >i de(amos una ila en blanco# el rano din%mico no incluir% el nuevo valor ! la validaci'n allar%.
El cuaderno con el e(emplo puede descararse a&u5 .
"oordinar horarios con Excel Esta nota viene a colaci'n de mi rencuentro con un entra4able amio de la (uventud. Cos unen maravillosos recuerdos de la (uventud pero nos separan H husos horarios. Despu"s de intercambiar nuestras direcciones de >!pe empezamos a coordinar el d5a ! la hora para comunicarnos. Fi(amos el pr'ximo in de semana# pero Ia &u" horaJ A&u5 es donde Excel puede darnos una buena a!uda. Yo resido en la $iudad ! mi amio en la $iudad 2. ,a dierencia horaria es de H horas ! el d5a eleido para conversar es el s%bado K de ebrero. ,as horas activas# es decir# cuando nuestro estado mental permite una conversaci'n m%s o menos coherente# son entre las L de la ma4ana ! las 0 de la noche /cada uno en su ciudad1. Este es el modelo &ue constru5 en Excel para ver las horas en &ue ambos coincidimos
En el cuadro de deiniciones /el rano E2FM1 ponemos la echa# la dierencia horaria# ! las horas de comienzo ! inal del horario de actividad ! en el rano @2<2H vemos las horas en
&ue coincidimos con ondo verde ! texto en nerita. I$'mo unciona el modeloJ ,as celdas de la tabla de horarios /el rano E2FM @2<2H1 contienen esta 'rmula ?BFB2N//F<,A/1-21O2P1 para $iudad ?BFB2N//F<,A/1-2NBFB1O2P1 para $iudad 2
donde BFB es la dierencia horaria entre las ciudades ,a expresi'n /F<,A/1-21O2P calcula la hora del d5a en saltos de hora# usando el valor de la unci'n F<,A ! dividiendo por 2P /ese es el m"todo &ue usa Excel para los c%lculos de horas1. Esta 'rmula nos permite pasar al d5a siuiente cuando se cumplen las primeras 2P horas. )ara mostrar el resultado con el d5a de la semana usamos ormato personalizado
)ara poner el ondo verde ! el texto en nerita usamos ormato condicional. ,a t"cnica &ue propono usa columnas auxiliares ocultas# como seuramente mis perspicaces lectores habr%n !a notado /en la primer imaen la primer columna visible es la columna D1.
$omo puede verse# la 'rmula usada es mu! sencilla ?B$2?. +eamos &u" ha! en $2
En A2 ponemos la 'rmula ?Y/@3A/@21Q?@3A/BFBH1@3A/@21R?@3A/BFBM11 El resultado es verdadero si la hora de la echa evaluada en la columna de la ciudad cae dentro del rano establecido entre FH ! FM lo mismo con la 'rmula en la celda =2 para la ciudad 2 ?Y/@3A/<21Q?@3A/BFBH1@3A/<21R?@3A/BFBM11 En la columna $ multiplicamos ambos resultados. $omo se trata de expresiones l'icas# dan si ambos resultados son +E3DADE3 ! 0 si aluno de los resultados es FA,>. >i bien podemos crear una 'rmula comple(a &ue combine las tres 'rmulas ! usarla directamente en el ormato condicional# esta t"cnica tiene en mi opini'n la venta(a de exponer en orma m%s clara la orma del c%lculo.
"onsolidar datos de varios cuadernos Excel con M# $uer% Ya hemos tratado anteriormente el tema de consolidaci'n de datos de varios cuadernos u ho(as Excel usando 6> Suer!. >in embaro el uso de esta herramienta presenta ciertos problemas ! la a!uda en l5nea del 6> Suer! es mu! pobre. En esta nota veremos c'mo lorar esta consolidaci'n para lo cual tendremos &ue editar ! modiicar la consulta >S, &ue enera el 6> Suer!. Aclaremos &ue esta tarea es sencilla ! no re&uiere conocimientos del lenua(e >S,. En nuestro e(emplo suponemos &ue tenemos los datos de ventas de tres sucursales de una empresa en tres cuadernos distintos ventasCorte.xls# ventas>ur.xls ! ventaseste.xls. ,as tablas de datos de cada cuaderno tienen la misma estructura# es decir# los mismos campos /columnas1 en las mismas posiciones. Empezamos por incluir cada tabla en un nombre. )or e(emplo# en ventasCorte.xls incluimos la tabla de datos en el nombre consCorte
:ambi"n podemos crear el nombre usando el cuadro de nombres
El pr'ximo paso es abrir un cuaderno en blanco donde consolidaremos los datos. En el nuevo cuaderno activamos la pesta4a Datos ! en btener Datos Externos usamos la opci'n Desde 6icrosot Suer!
;na tarea común en Excel es comparar tablas /o listas1 para encontrar dierencias o cambios. En varias notas de este blo hemos mostrado distintas t"cnicas para hacerlo. Existen varios complementos /Add
mostraremos
un
pe&ue4o
truco.
$uando &ueremos comparar &u" datos han sido areados# &uitados o cambiados# una posibilidad es usar unciones de bús&ueda como =;>$A3+ o $uponamos &ue recibimos un reporte de ventas
!
unos
d5as
m%s
tarde
una
seunda
versi'n.
$omo podemos apreciar# en el inorme altan las sucursales # P ! H tambi"n los datos de
las
sucursales
#
2
!
L
son
distintos.
El primer paso &ue damos es cambiar el encabezamiento de la columna = en ambas ho(as. En
luar
de
+entas
pondremos
Ventas1
!
Ventas2
respectivamente.
El seundo paso es arear una tercer ho(a# Ucomparaci'nV /podemos darle cual&uier nombre &ue &ueramos1. >eleccionamos la celda A de la nueva ho(a ! abrimos el menú Datos-$onsolidar
En el ormulario &ue se abre marcamos las opciones UFila >uperiorV ! U$olumna
Apretamos el bot'n Arear ! seleccionamos el rano relevante en la ho(a
+olvemos a apretar Arear ! lueo Aceptar. Excel crea en orma autom%tica esta tabla
)odemos ver con acilidad con acilidad todos los cambios entre ambas listas. $on unas 'rmulas sencillas tenemos en seundos un inorme detallado de las dierencias
;na complicaci'n puede surir cuando tenemos m%s de una columna descriptiva a la iz&uierda de los datos &ue &ueremos comparar. )or e(emplo# si en nuestras tablas adem%s de
las
sucursales
tambi"n
existe
un
campo
/columna1
con
la
zona
$omo Excel consolida en reerencia a la columna iz&uierda# el detalle de las sucursales no es tomado en cuenta. ,a soluci'n es crear una columna auxiliar concatenando los valores de
ambos
campos
Enseuida explicaremos por &u" separamos los valores con una coma . Ahora volvemos al
El
proceso
de
consolidar
resultado
partiendo
es
de
la
el
columna
auxiliar
siuiente
,a coma &ue hemos areado en la concatenaci'n nos a!uda a separar la columna auxiliar en las dos oriinales usando Datos-:exto en columnas. )rimero insertamos una columna en blanco entre las columnas A ! = ,ueo seleccionamos el rano con valores en la columna A ! usamos el menú Datos-:exto en columnas con la opci'n de separadores UcomaV
El resultado
:odo lo &ue nos &ueda por hacer es arear las 'rmulas ! los ormatos &ue &ueramos.
"o!paración r&pida de ta'las con (atos)"onsol (atos)"onsolidar idar ;na tarea común en Excel es comparar tablas /o listas1 para encontrar dierencias o cambios. En varias notas de este blo hemos mostrado distintas t"cnicas para hacerlo. Existen varios complementos /Add
$uando &ueremos comparar &u" datos han sido areados# &uitados o cambiados# una posibilidad es usar unciones de bús&ueda como =;>$A3+ o $uponamos &ue recibimos un reporte de ventas ! unos d5as m%s tarde una seunda versi'n.
En el ormulario &ue se abre marcamos las opciones UFila >uperiorV ! U$olumna
Apretamos el bot'n Arear ! seleccionamos el rano relevante en la ho(a
+olvemos a apretar Arear ! lueo Aceptar. Excel crea en orma autom%tica esta tabla
)odemos ver con acilidad con acilidad todos los cambios entre ambas listas. $on unas 'rmulas sencillas tenemos en seundos un inorme detallado de las dierencias
;na complicaci'n puede surir cuando tenemos m%s de una columna descriptiva a la
iz&uierda de los datos &ue &ueremos comparar. )or e(emplo# si en nuestras tablas adem%s de las sucursales tambi"n existe un campo /columna1 con la zona
$omo Excel consolida en reerencia a la columna iz&uierda# el detalle de las sucursales no es tomado en cuenta. ,a soluci'n es crear una columna auxiliar concatenando los valores de ambos campos
Enseuida explicaremos por &u" separamos los valores con una coma . Ahora volvemos al proceso de consolidar partiendo de la columna auxiliar
El resultado es el siuiente
,a coma &ue hemos areado en la concatenaci'n nos a!uda a separar la columna auxiliar en las dos oriinales usando Datos-:exto en columnas. )rimero insertamos una columna en blanco entre las columnas A ! = ,ueo seleccionamos el rano con valores en la columna A ! usamos el menú Datos-:exto en columnas con la opci'n de separadores UcomaV
Trans*erir datos de Excel a Access ) tercera nota En las notas anteriores vimos c'mo transerir datos de Excel a Access manualmente ! proram%ticamente. ,a necesidad de usar Access sure como una de las soluciones posibles cuando &ueremos# por e(emplo# consolidar datos de varias ho(as en una única base de datos para enerar a partir de ella una tabla din%mica. >i el total de ilas a consolidar supera el l5mite de una ho(a de Excel /hasta la versi'n 200 incluida# MHHM ilas1 no podemos almacenar los datos consolidados en una única ho(a. >i traba(amos con Excel 2007 podemos almacenar m%s de un mill'n de ilas en una ho(a# lo cual nos exime de tener &ue usar herramientas externas a Excel. )ero como m%s del H0W de los usuarios usa versiones anteriores a Excel 2007# almacenar datos en Access es una buena soluci'n. En las soluciones &ue muestro en las notas mencionadas suponemos &ue Access est% instalado en la m%&uina del usuario. )or supuesto# "ste no es siempre el caso ! en esta nota mostraremos como usar Access como recipiente para nuestros datos aún cuando no est" instalado en nuestra m%&uina. Empecemos por aclarar &ue los archivos con ormato .mdb no re&uieren la presencia de Access para poder ser utilizados. Estos archivos est%n asociados con el 6icrosot 9et
Database Enine# &ue est% incluido en el pa&uete de ice. >i bien este componente ha sido abandonado por 6icrosot# siue siendo viente por motivos de compatibilidad. Cuestra meta es crear dos rutinas# una para crear el archivo U.mdbV ! otra para almacenar los datos de la ho(a u ho(as Excel en la tabla. Cuestro primer paso es crear una reerencia a X 6icrosot Ext. AD 2.H or DD, >ecurit! /o posteriores1. X 6icrosot Active Data b(ect 2.H ,ibrar! /o posteriores1. en el editor de +=# en el menú :oolsZ3eerences
;n detalle a tomar en cuenta es &ue las macros &ue mostrar" m%s adelante est%n construidas para el e(emplo espec5ico de esta nota ! ha! &ue editarlas para adaptarlas a otros usos. En nuestro e(emplo tenemos un cuaderno Excel con tres ho(as. $ada ho(a contiene datos de tres distintos a4os /200H# 200M ! 20071 &ue &ueremos consolidar en una única ho(a para construir a partir de ella una tabla din%mica. ,a rutina para crear el archivo U.mdbV con una tabla para almacenar los datos es la siuiente
Option Explicit
Const dataSource As String = "provider=microsoft.jet.oledb.4.0;" _ & "data source=d:\BaseDeDatos.mdb" Const tableName As String = "datos_export"
Sub crearDB() Dim catalog As ADOX.catalog Dim new_table As ADOX.Table
'crear la base de datos
Set catalog = CreateObject("adox.catalog") catalog.Create dataSource
En la parte superior del m'dulo# antes de la rutina UcrearD=V hemos deinido dos constantes. ,o hacemos de esta manera !a &ue tambi"n en la seunda rutina haremos uso de estas constantes. Esta macro crea una base de datos Access /=aseDeDatos.mdb1 con una tabla /datosGexport1
Ahora tenemos &ue exportar los datos de la ho(a activa al la tabla UdatosGexportV# lo &ue hacemos con esta macro Sub AgregarDatos() ' exporta los datos de la hoja activa a una tabla de Access ' este procedimiento es especifico para el ejemplo. ' Para otros usos hay que adaptarlo
Dim cn As ADODB.Connection, rs As ADODB.Recordset, fila As Long
Set cn = New ADODB.Connection cn.Open dataSource
Set rs = New ADODB.Recordset rs.Open tableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table fila = 2 ' la primer fila en la hoja despues de los encabezamientos
Do While Len(Cells(fila, 1)) > 0 With rs .AddNew .Fields("mes") = Range("A" & fila).Value .Fields("pais") = Range("B" & fila).Value .Fields("suma") = Range("C" & fila).Value
.Update End With fila = fila + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub
Esta rutina es la misma &ue hemos usado en la seunda nota de la serie# adaptada a nuestro e(emplo. Ahora activamos la ho(a cu!os datos &ueremos exportar ! corremos la macro para cada una de las ho(as &ue &ueremos consolidar.
A esta altura de los acontecimientos tenemos un archivo Access con un tabla &ue contiene los datos de las ho(as /podemos ver &ue el tama4o del archivo se ha incrementado1
Ahora podemos crear la tabla din%mica usando la opci'n Fuente de datos Externa
En el seundo paso creamos una nueva uente de datos# &ue en nuestro e(emplo llamaremos Uconsolidar6dbV usando el driver de Access
Despu"s de apretar $onnect# apretamos [ con lo cual hemos creado la nueva conexi'n. De a&u5 en adelante seuimos los pasos del asistente tal como hemos mostrado en esta nota. El resultado
Trans*erir datos de Excel a Access – seunda nota+ En la nota anterior vimos como transerir datos de Excel a Access manualmente usando los mecanismos nativos de Access. tra alternativa es exportar los datos de Excel a Access proram%ticamente# es decir usando +ba. )ara nuestro e(emplo 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 +ba haremos el camino a la inversa# decir crearemos una conexi'n a Access desde Excel. Esto lo haremos usando ob(etos de la AD librar! /AD ? Active Data b(ects1. Antes de sumerirnos en los vericuetos del c'dio# una observaci'n importante para poder usar las unciones de AD en Excel debemos primero crear una reerencia con el menú :ools-3eerence del editor de +b
$omo en el e(emplo anterior# empezamos por crear una base de datos &ue contiene una tabla a la cual &ueremos exportar los datos de Excel. Cuestra base de datos /bd.mdb1 contiene la tabla +entas &ue por ahora est% vac5a
>in entrar demasiado en detalles t"cnicos# podemos describir la tarea de la siuiente manera T - creamos una conexi'n a la base de datos
T- deinimos en &u" tabla de la base de datos &ueremos poner los datos
T - deinimos un ob(eto &ue contena los datos a transerir /recordset1
T - deinimos donde se encuentran los datos a transerir /en nuestro e(emplo el cuaderno tiene dos ho(as con datos1
T - transerimos los datos
@e areado la ho(a )ar%metros en la cual podemos deinir donde se encuentra la base de datos# cu%l es la tabla a la cual &ueremos copiar los datos ! en &u" cuaderno se encuentran. De esta manera# podemos controlar los par%metros del c'dio con acilidad.
$omo ven he areado tambi"n la l5nea a partir de la cual ha! &ue transerir los datos de la ho(a. En eneral los datos empiezan en la ila 2 /la est% ocupada por los encabezamientos1. :ambi"n podemos eleir de &u" ho(a pasar los datos# con una lista despleable en la celda =. El c'dio de la macro es el siuiente
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
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'dio debe ponerse en un m'dulo común de +ba.
Trans*erir datos de Excel a Access – pri!era nota+ Despu"s de escribir la nota sobre c'mo consolidar datos de m%s de dos cuadernos con 6> Suer!# decid5 &ue hab5a lleado el momento de mostrar c'mo podemos transerir datos de una ho(a de Excel a una tabla de Access. ;sar Access es una soluci'n pr%ctica cuando nuestra tabla de datos supera el l5mite de Excel /para las versiones L7-2001 de MHHM ilas. En esta nota mostraremos &ue no ha! motivo para sentirse inhibido de usar Access# tambi"n si no estamos del todo amiliarizados con su uso. =%sicamente ha! dos caminos posibles para transerir los datos manualmente ! proram%ticamente /+ba1. Esta nota se ocupa de c'mo transerir datos de Excel a Access manualmente. )ara poder hacerlo necesitamos# obviamente# &ue Access est" instalado en nuestra m%&uina /o servidor1. )ara nuestro e(emplo consideraremos un cuaderno Excel con dos ho(a. ,a primera contiene ventas del mes de abril ! la seunda del mes de ma!o. )or motivos de claridad usamos pocos datos.
El primer paso es abrir Access ! crear una base de datos en blanco
En nuestro e(emplo aceptamos el nombre propuesto por Access /bd1# pero podemos darle cual&uier otro nombre# preerentemente m%s siniicativo.
;na vez creada la base de datos veremos esta pantalla
>eleccionamos con un clic :ablas en el %rea de b(etos ! abrimos el menú contextual /clic con el bot'n derecho del mouse1 a&u5 seleccionamos la opci'n
En el di%loo &ue se abre eleimos Excel en :ipo de Archivo ! seleccionamos el cuaderno &ue contiene la ho(a &ue &ueremos importar.
En nuestro e(emplo Excel detecta &ue ha! dos ho(as en el cuaderno. Eleimos UabrilV ! apretamos >iuiente
Ahora debemos deinir si la primer ila es contiene t5tulos. En caso airmativo los t5tulos son usados como nombres de los campos de la tabla.
En este paso debemos deinir donde uardar los datos. En nuestro caso n o hemos creado ninuna tabla as5 &ue la opci'n es Uen una nueva tablaV.
El pr'ximo paso nos permite deinir distintas opciones para los campos. En nuestro e(emplo# sencillamente seuimos adelante apretando U>iuienteV.
Ahora podemos deinir claves principales /5ndices1 para la tabla. )ara nuestro e(emplo este paso no es cr5tico# pero en eneral es recomendable de(ar la opci'n por deecto de Access.
En el último paso podemos deinir el nombre de la tabla. )or deecto# Access propone el nombre de la ho(a. )or lo eneral# como en nuestro e(emplo debemos cambiar el nombre a alo m%s siniicativo# como +entas. El último paso es apretar Finalizar. Al cabo de unos instantes Access nos inorma &ue el proceso ha terminado /o racasado si es &ue hemos cometido alún error o los datos no son los adecuados1. Access ha creado ahora la tabla +entas
&ue contiene los datos de la ho(a
Ahora repetimos el proceso pero eleimos la ho(a Uma!oV.
En el tercer paso eleimos la tabla +entas
Finalmente apretamos Finalizar. ,os nuevos datos han sido areados a los existentes en la tabla
)ara totalizar ! analizar los datos de base de datos podemos usar tablas din%micas con la opci'n Fuente de datos externa
,r&*ico -i Mac din&!ico en Excel ,a publicaci'n brit%nica :he Economist publica desde hace varios a4os el 5ndice =i 6ac. El =i 6ac
Este r%ico ue construido con la aplicaci'n :ableau. $omo !a habr%n intuido# la preunta es Ise puede hacer con ExcelJ +eamos &u" elementos inclu!e ^ 8r%ico de barras por pa5s ^ $ontroles &ue permiten visualizar los pa5ses por continente
^ ,os colores de las barras representan el valor /dierencias positivas en ro(o# dierencias neativas en verde1 ,a tabla de datos es la siuiente
Empezamos por ordenar la tabla en orden ascendente seún el campo )orcenta(e seleccionamos los campos U)a5sV ! U)orcenta(eV para construir este r%ico de barras
Este es el r%ico de barras est%ndar de Excel con unas pocas modiicaciones &uitamos las l5neas de cuadr5cula# i(amos las eti&uetas del e(e vertical en Uba(oV ! en ormato de series de datosZrelleno marcamos la opci'n Uvariar colores entre puntosV. Este r%ico es una primera aproximaci'n. )ara poder arear los elementos din%micos ! los calores del r%ico oriinal tendremos &ue hacer alunas transormaciones. ;na seunda aproximaci'n es usar una tabla din%mica para enerar un r%ico din%mico &ue muestre s'lo los pa5ses de los continentes eleidos
Excel ) Areando controles en hojas de c&lculo Excel permite la inclusi'n de controles como botones de opci'n ! listas despleables en las ho(as de c%lculo. ,os controles son ob(etos de interaz r%ica &ue permite al usuario controlar acciones. Cormalmente son utilizados en macros /+ba# +isual =asic or Applications1 pero tambi"n se los puede usar en una ho(a# sin &ue medie ninuna proramaci'n. ,as versiones m%s recientes de Excel tienen dos tipos de controles# los controles Active /la barra de la iz&uierda en la iura1 ! los controles de la barra de herramientas Formularios
En esta entrada daremos un e(emplo pr%ctico del uso de los controles de la barra de herramientas Formularios. Co nos detendremos a analizar las dierencias entre los dos tipos de controles ni el uso de los Active. $omo es costumbre de este blo# ilustraremos el uso de los controles con un e(emplo. >uponamos &ue hemos dise4ado un ormulario en una ho(a de c%lculo Excel# para calcular precios de productos. El precio depende de si el cliente es miembro del club de compras ! de las condiciones de pao. El ormulario del e(emplo se puede descarar a&u5 .
En este ormulario hemos puesto P controles una casilla de verificación /si el cliente es miembro del club marcamos la *+*1 ! tres botones de opción para se4alar la orma de pao. Estos controles uncionan de la siuiente manera _ Activamos la barra de ormularios ! pulsamos sobre el control re&uerido /en nuestro caso la casilla de veriicaci'n1 2 _ ubicamos el control en el luar re&uerido ! abrimos el menú de ormato del control
_ en la divisi'n *control* creamos un v5nculo con aluna celda de la ho(a.
En esta celda Excel anotar% un valor de acuerdo a al situaci'n del control. >i la casilla est% se4alada /*v*1# en la celda vinculada aparecer% el valor *+E3DADE3* de lo contrario Excel anotar% el valor *FA,>*. ,a celda vinculada &ue hemos eleido es A de manera &ue el control oculte el valor de la celda.
P _ )rocedemos 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 e(emplo1. El valor en la celda vinculada corresponde al orden de los controles. Es decir# si el control activo es el primero /$ontado1# el valor de la celda vinculada ser% **. >i el control activo es *@asta 0 d5as* el valor de la celda vinculada ser% *2* ! as5 sucesivamente. ,os valores de las celdas vinculadas uncionan como arumentos en las 'rmulas &ue calculan los descuentos - en la celda $2 tenemos la 'rmula ? SI(A3=VERDADER!D22"1#$!#% - en la celda $2P tenemos la 'rmula =D22"E&E'IR(A!1#$!)$!#% De esta manera# las opciones marcadas en los controles nos permiten *controlar* los resultados de las 'rmulas.
En el rano AMA2 usamos validaci'n de datos# con la opci'n *lista*. En la @o(a2 hemos puesto una lista de precios. En esta ho(a hemos deinidos dos nombres de ranos# uno para la lista de la validaci'n de datos ! el otro para la 'rmula =;>$A3+ &ue calcula los precios
de los productos en el rano $M$2. )ara evitar valor err'neos /TCOA# TD<+O0# etc1 # combinamos las unciones de bús&ueda con la unci'n de control E>=,AC$. ,a anatom5a del modelo es la siuiente
_ $ontroles insertamos 7 casillas de veriicaci'n /de la colecci'n de controles de ho(a# no Active1 ! los liamos a las celdas en el rano ==L. $uando se se4ala el control# la celda correspondiente muestra +E3DADE3 en caso contrario mostrar% FA,>. ;saremos estos valores en las macros &ue controlan los puntos mostrados en el r%ico. 2 _ $elda de control la celda = /el nombre DimensionGdelGarra! se reiere a esta celda1# cuenta cuantos controles han sido se4alados# es decir# cuantos continente &ueremos mostrar en el r%ico. $uando el valor es M# siniica &ue hemos eleido todos los continentes. :ambi"n esta celda la usaremos en nuestras macros. _ 6acros proramamos dos macros para ocultar o mostrar datos con Autoiltro en la tabla delos datos# una &ue responde a las elecciones de los controles excepto el control U:odosV# la seunda para el caso &ue el usuario se4ale la casilla U:odosV El c'dio de la primer macro es Sub select_series()
Dim strContinentes() As String Dim iR As Integer Dim iCounter As Integer
If Range("Dimension_del_array") = 6 Then Range("Todos") = True Else Range("Todos") = False End If
'redimensionar el array ReDim strContinentes(Range("Dimension_del_array"))
iCounter = 0
With Sheets("grafico dinamico") For iR = 4 To 9 If .Cells(iR, 2) Then strContinentes(iCounter) = .Cells(iR, 1) iCounter = iCounter + 1 End If Next iR
,a seunda macro# para el caso &ue se ha!a eleido la casilla U:odosV es
Sub all_Continents() Dim iR As Integer
'si se elige Todos With Sheets("grafico dinamico") If Range("Todos") Then For iR = 4 To 9 .Cells(iR, 2).Formula = True Next iR Else For iR = 4 To 9 .Cells(iR, 2).Formula = False Next iR End If End With
Call select_series
End Sub
En este modelo usamos una única serie de valores# por eso para ocultar alunos de los puntos de la serie con Autoiltro /los pa5ses de los continentes &ue no hemos eleido1 nos aprovechamos de la propiedad de los r%icos de no mostrar los valores de celdas ocultas. El control U:odosV est% asociado a la macro UallG$ontinentsV
A los dem%s controles les hemos asinado la macro UselectGseriesV. Este modelo puede adaptarse a muchos escenarios. El cuaderno puede descararse# sin caro# a&u5 /usar el enlace Descarar en la parte inerior de la p%ina1. Suien est" interesado en una explicaci'n detallada del modelo ! la contrase4a para acceder a los c'dios# puede descarar el manual /tiene un costo de H Euros1.
Auto*iltro de *echas con !acros ;na inesperada lluvia acaba de arruinarme el Ubautismo de ueoV de mi nueva bicicleta $# una >cott >par M0
&ue estaba proramado para este s%bado. As5 &ue dedicaremos esta ma4ana a un problema &ue se nos puede presentar a los usuarios de Excel en castellano /o cual&uier otra coniuraci'n reional uera del inl"s americano1. >uponamos una tabla con echas ! ventas. )odemos mostrar el detalle de ventas de un per5odo con acilidad usando Autoiltro. Esto no es ninuna novedad para ninuno de mis lectores /ni para el LLW de los usuarios de Excel1. >i &ueremos analizar los datos de la tabla con recuencia podemos crear una macro para ahorrarnos alunos de los pasos del uso de Autoiltro. ,a idea es &ue en una celda ponamos la echa de inicio del per5odo a analizar# en otra la echa de cierre del per5odo ! apretando haamos correr una macro &ue haa la tarea. >uponiendo &ue no somos expertos en +ba# empezamos por rabar las acciones &ue hacemos para aplicar el autoiltro
Al rabar una macro es recomendable darle un nombre siniicativo# arear un m"todo abreviado de teclado /preerentemente combinando la tecla 6a!úsculas1 ! poner una descripci'n de la tarea &ue cumple la macro. >uponamos &ue &ueremos iltrar la lista para &ue muestre las ventas del seundo trimestre del 2007 /n'tese &ue !a hemos areado las celdas para introducir las echas en la parte superior de la ho(a1
E, c'dio resultante es el siuiente Sub Filtrar_periodo() ' ' Filtrar_periodo Macro ' Filtrar la lista entre dos fechas ' ' Acceso directo: Ctrl+May?s+F '
El c'dio &ue resulta de rabar las acciones presenta dos problemas •
El rano de la tabla es i(o# 3ane/*BABBFB20K*1
•
,os criterios de iltrado tambi"n est%n preestablecidos en el c'dio
)ara &ue nuestra macro sea útil tenemos &ue modiicar el c'dio de manera &ue podamos cambiar las echas seún nuestras necesidades ! &ue el rano de la tabla se adapte cuando esta cambia. Empecemos por crear dos nombres &ue se reieran a las celdas =2 /celFechaDe1 ! = /celFecha@asta1. ,a orma m% pr%ctica de hacerlo es introduciendo los nombres en el cuadro de Combres
Ahora editamos nuestro c'dio donde deinimos dos variables tipo >trin /texto1 para los criterio de iltrado. El c'dio es el siuiente Sub filtrar_periodo_mejorado() Dim strCriterio1 As String Dim strCriterio2 As String
Al activar la macro obtenemos el siuiente resultado
Excel nos inorma raciosamente &ue no se han encontrado ilas &ue cumplan con los criterios El problema consiste en &ue en +ba Excel usa la notaci'n americana de echas /mesOd5aOa4o1 de manera &ue la echa 0O0PO2007 ha sido transormada en 0PO0O2007
,a seunda echa no ha surido transormaci'n dado &ue no existe el mes número 0. ,a orma de solucionar este problema es pasar las echas a una variable de tipo ,on ! con esta variable armar la cadena de texto del criterio. El c'dio es Sub filtrar_periodo_mejorado() Dim lFecha1 As Long, lFecha2 As Long
$omo puede verse solo deinimos dos variables de tipo ,on par alas echas ! la cadena de texto de los criterios la armamos con el operador U `V Ahora nuestro modelo unciona sin inconvenientes# sin importar cu%les sean las deiniciones reionales del sistema
Auto*iltro de ca!pos en ta'las din&!icas >uponamos esta tabla din%mica /basada en la tabla de acturas de la base de datos Corthind &ue viene con ice1
>i &ueremos mostrar s'lo los datos de un determinado intervalo /diamos entre el HO0KO200K ! el HO0LO200K1 podemos desplear la lista de echas ! eleir las echas una por una )ero como estamos usando Excel 2007# la tarea es mucho m%s %cil. >encillamente usamos la opci'n Filtros de echa-Entre $omo podemos ver# las posibilidades de iltrar por criterios de echas son múltiples
,o mismo es v%lido si los valores del campo de ilas son texto
En Excel L7-200 no existen# aparentemente# estas posibilidades.
Dio aparentemente por&ue con un pe&ue4o truco podemos replicar la uncionalidad de autoiltro en tablas din%micas &ue existe en Excel 2007. >eleccionamos la celda ad!acente al borde derecho de la tabla din%mica en la ila de los encabezamientos /en nuestro e(emplo $P1 ! aplicamos Autoiltro con el menú Datos-FiltroAutoiltro. El resultado es &ue el Autoiltro es aplicado a las columnas de la tabla din%mica
Ahora disponemos de la uncionalidad *)ersonalizar*# con la cual podemos aplicar el iltro con criterios personalizados
Mostrar los criterios de Auto*iltro en una celda ;na de las uncionalidades m%s populares de Excel es el Autoiltro. $on esta herramienta podemos iltrar una tabla de acuerdo criterios l'icos aplicados a los valores de una o m%s columnas de la tabla. ;n lector me consulta c'mo se puede hacer para &ue el criterio aplicado aparezca en una celda por encima del encabezamiento de la columna. )or e(emplo# partiendo de esta lista
ver en las celdas correspondientes de ila 2 los criterios aplicados para iltrar la lista
$omo puede verse# estamos usando una unci'n deinida por el usuario /;DF1. Esta unci'n ue desarrollada por el uru de Excel >tephen =ullen.
)ara poder usar esta unci'n tenemos &ue poner este c'dio en un m'dulo común del editor de +ba# preerentemente en el )ersonal.xlsb /)ersonal.xls en Excel L7-2001 Function FilterCriteria(Rng As Range) As String 'By Stephen Bullen Dim Filter As String
Application.Volatile True Filter = "" On Error GoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) Is Nothing Then GoTo Finish With .Filters(Rng.Column - .Range.Column + 1) If Not .On Then GoTo Finish Filter = .Criteria1 Select Case .Operator Case xlAnd Filter = Filter & " y " & .Criteria2 Case xlOr Filter = Filter & " o " & .Criteria2 End Select End With End With Finish: FilterCriteria = Filter End Function
Esta unci'n utiliza un único arumento# *3n*# &ue es cual&uier celda de la columna sobre se aplica el Autoiltro. @a! &ue tener en cuenta &ue esta unci'n ue desarrollada antes de la aparici'n de Excel
2007. )or e(emplo# si eleimos m%s de dos criterios en una misma columna# por e(emplo ver las zonas Corte# >ur ! este# la unci'n no podr% mostrar el criterio aplicado
:ip en Excel 2007# al apuntar con el mouse al icono del autoiltro podemos ver una ventanilla &ue nos muestra los criterios aplicados /como en la imaen de arriba1.
Fór!ulas con resultados !últiples @a! situaciones en las &ue una 'rmula puede dar m%s de un resultado. El caso cl%sico es con las unciones de bús&ueda como =;>$A3+ o uponamos &ue tenemos esta tabla /la misma &ue usamos en la nota sobre el uso de =;>$A3+ en listas con valores repetidos1
En otra ho(a &ueremos poner el nombre de un producto ! &ue se desplieuen todas las 'rdenes de compra del producto. >i usamos =;>$A3+ /o 'lo podemos obtener un resultado por celda. Co ha! ninuna orma de escribir resultados de una 'rmula en otra celda &ue no contena la 'rmula# al iual &ue no podemos cambiar la estructura de la ho(a con 'rmulas. >i &ueremos poner en una ho(a de Excel todos los resultados posibles de una 'rmula de bús&ueda tendremos &ue usar otras t"cnicas. En esta nota mostrar" dos t"cnicas para obtener todos los resultados. ;na estrateia posible es usar múltiples 'rmulas.
Establecemos por adelantado cu%l pueda ser el número m%ximo de resultados a obtener ! en un rano de celdas contiuas introducimos esta 'rmula matricial ?<6.6EC3/>3EF/odGcompra00F<,A>/odGcompra11? B=B2F<,A/DE>3EF/odGcompra00F<,A>/odGcompra111F<,A/DE>3EF/odGcompra0011NF<,A/DE>3EF/odGcompraF<,A>/odGcompra1011N1F<,A/1-121 donde el nombre *odGcompras* deine el rano A2=0 de nuestro e(emplo )or e(emplo# en una nueva ho(a /*ormulas*1 introducimos la 'rmula en la celda AP ! la copiamos en todo el rano hasta la celda AL /estamos suponiendo &ue el número m%ximo de 'rdenes posibles es seis1
Dado &ue s'lo ha! tres 'rdenes de compras para tornillos# el resultado en las celdas AKA20 es T3EF )ara ocultar estos resultados podemos usar ormato condicional
Esta t"cnica tiene varias desventa(as el uso de 'rmulas matriciales# el uso de DE>3EF &ue es vol%til# ! adem%s es complicada. ;na t"cnica alternativa es usar la c%mara c%mara (unto (unto con Autoiltro. Autoiltro. Activamos el Autoiltro en la tabla de datos /en la ho(a *datos*1. ,ueo seleccionamos todo el rano de la tabla sin incluir los encabezamientos ! activamos la c%mara creamos una nueva ho(a /*c%mara*1 ! peamos la imaen en el luar deseado
En la ho(a Datos iltramos la tabla de acuerdo al producto deseado
,a imaen en la ho(a *c%mara* rele(ar% el cambio instant%neamente
El inconveniente con este modelo es &ue debemos ir a la l a ho(a Datos para hacer el iltrado. >i &ueremos mane(ar los resultados desde la ho(a *c%mara*# tendremos &ue utilizar una macro para mane(ar el iltrado por *control remoto*. Empezamos por arear una lista despleables# con validaci'n de datos# en la celda =2 de la ho(a *c%mara* para poder controlar el iltrado
,ueo ponemos este c'dio en un m'dulo común del editor de +b Sub filtrado_ordenes() Sub filtrado_ordenes() Dim strCrit Dim strCrit As String Dim rngTablaDatos Dim rngTablaDatos As As Range Range
strCrit = Sheets("camara").Range("B2")
Set rngTablaDatos Set rngTablaDatos = Sheets("datos").Range("A1").CurrentRegion
If strCrit If strCrit <> "Todos" Then rngTablaDatos.AutoFilter Field:=1, Criteria1:=strCrit Else
rngTablaDatos.AutoFilter End If
End Sub
)ara activar el c'dio cuando eleimos el producto proramamos este evento en el m'dulo de la ho(a *c%mara* Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then Call filtrado_ordenes
End Sub
$on esto hemos completado nuestro modelo. )odemos ocultar la ho(a *datos* de manera &ue nuestro usuario s'lo vea la ho(a *c%mara* desde la cual puede mane(ar las &ue 'rdenes ser%n exhibidas en la ho(a.
Filtro Avanzado en hojas proteidas – una rareza de Excel Aplicamos )roteer a una ho(a de Excel para impedir &ue el usuario haa cambios o modiicaciones en la ho(a. Excel nos permite deinir excepciones. )or e(emplo# permitir &ue el usuario pueda usar Autoiltro /a condici'n &ue el autoiltro ha!a sido aplicado antes de proteer la ho(a1 u rdenar
>i aplicamos )roteer @o(a sin excepciones# la opci'n Filtro ! 6ostrar :odo del menú Datos aparecen deshabilitadas
>i nos i(amos bien veremos &ue la opci'n Filtro Avanzado siue activa. ,os proramadores de Excel se han olvidado de blo&uear esta opci'n. As5 podemos aplicar Filtro avanzado para iltrar todas las ilas donde el valor de la celda en la columna A sea ma!or de 2H00 a pesar de &ue la ho(a est% proteida
;no de los problemas &ue suren es &ue si bien la opci'n Filtro Avanzado siue activa# la opci'n 6ostrar :odo est% blo&ueada. Es decir# podemos iltrar# pero no podemos &uitar el iltro. )ara mostrar las ilas ocultas sin tener &ue cancelar la protecci'n basta con abril el di%loo del Filtro Avanzado ! borrar el rano de los criterios. 6icrosot conoce este problema desde la versi'n 2000# ! en la última nota publicado en la base de conocimientos de Excel # Advanced Filter 6a! Function on )rotected orsheet# mencionan &ue por el momento /noviembre del 2001 no ha! orma de impedir la aplicaci'n de Filtro Avanzado en una ho(a proteida.
;na soluci'n posible es proteer todas las celdas ! no permitir seleccionar todas las celdas donde pudieran deinirse criterios para iltro avanzado. De esta manera# si bien el di%loo del iltro avanzado se abrir%# no habr% posibilidad de deinir un rano de criterios# lo &ue hace &ue todas las ilas &ueden visibles.
Filtrado de *echas en Excel con Auto*iltro o Filtro Avanzado >uponamos &ue &ueremos iltrar una lista de echas de manera de de(ar visibles s'lo las echas &ue caen en mi"rcoles. )odemos hacer esto con Autoiltro o con Filtro Avanzado# pero cu%l ser5a el criterioJ >i usamos Autoiltro necesitamos crear una columna auxiliar &ue nos d" el d5a de la semana para cada una de las echas del rano. ,ueo iltramos de acuerdo a esta columna auxiliar.
