El correcto uso de INDIRECTO (II): Listas desplegables dependientes

Como ya hemos comentado, la función “Indirecto” tiene varias características que no necesariamente son conocidas. En esta ocasión vamos a sacar provecho de ellas y mostraremos cómo hacer listas dependientes usando dicha función.

Para comenzar, en una hoja escribamos datos de este modo:

Luego, para asegurarnos de que si agregamos más datos estos sigan siendo parte de nuestras listas, vamos a convertir cada columna a “Tabla” (Enlace).

Ah, por favor, colocar que los datos tienen encabezados:

Luego, para asegurar de que funcionen nuestras listas, debemos cambiar el nombre de cada tabla y colocarle el mismo que el encabezado:

A esa hoja vamos a nombrarla “Datos”. Ahora vayamos a otra hoja y en la celda, por ejemplo, “B1” usando “Validación de datos” y la opción “lista” crearemos una lisa desplegable con los nombres de los cuatro continentes que hemos usado.

Como ya tenemos la primera lista crearemos, ahora, la que será dependiente. Vamos a la celda “B2” y nuevamente usaremos “Validación de datos” y la opción “lista”, pero en esta ocasión ingresaremos una fórmula con “Indirecto” del siguiente modo:

=INDIRECTO(B1)

El resultado que tendremos es el siguiente:

Cada vez que cambiemos el valor de “B1”, la lista de “B2” será con los países correspondiente. Ah, y si agregamos más países en la hoja “Datos”, al ser tablas serán automáticamente parte de cada conjunto de países y se visualizarán sin dilema en la hoja de las listas. Y eso amigos/as es otra buena idea para usar “Indirecto”. Hasta la próxima.

Abraham Valencia

Excel y MySQL (II)

Hace poco hablamos de cómo conectar MySQL con Excel (Enlace) aunque básicamente se hizo referencia a conectarse a un servidor y extraer sus datos. En esta ocasión lo que vamos a hacer es enviar datos de Excel hacia una base de datos MySQL.

Vamos a hacer algo corto y práctico porque la idea es que se entienda más que hacer algo complicado. Entonces, comenzaremos creando una tabla con cuatro campos en alguna base de datos MySQl que tengamos. Mi base de datos se llama “Pruebas” y llamaré a mi tabla “TablaPruebas”. Los campos serán los siguientes:

Nombre del campo	Tipo	Extras
Id	Int	Primary – Auto_Increment
Nombre	Text
País	Text
Fecha	Date

En nuestra hoja de Excel usaremos tres columnas A, B y C (La de “Id” no, ya que es de numeración automática) y pondremos encabezados en la fila 1. Llenaremos datos que queramos enviar a MySQL:

Como son varios datos usaremos un bucle para enviar los datos, aunque la clave de todo será usar SQL y en específico “Insert Into”. Dado que usaremos el bucle mencionado, debemos asegurar que al construir nuestra cadena SQL, se tomen los datos adecuados por lo que dicha cadena debe quedar de la siguiente manera:

sql = "Insert Into tablapruebas (Nombre, País, Fecha) Values ('" & Range("A" & x).Value & "','" _         
  & Range("B" & x).Value & "','" & Format(Range("C" & x).Value, "yyyy-mm-dd") & "')" 

Al ser los campos del tipo Text y Date, deben necesariamente ir entre comillas simples en la cadena. Para terminar, y basándonos en todo lo mencionado en el artículo anterior, nuestra macro debería quedar así, tomando en cuenta que usaré MySQL en el “LocalHost”:.

Sub DatosaMySQL()

Dim cnn As ADODB.Connection 
Dim rst As ADODB.Recordset 
Dim sql$, BD$, Servidor$, User$, Clave$ 
Dim UltimaFila As Long, x As Long 

Set cnn = New ADODB.Connection 
Let Servidor = "localhost": Let BD = "pruebas" 
Let User = "root": Let Clave = "" 
Let UltimaFila = Cells(Rows.Count, 1).End(xlUp).Row 

cnn.ConnectionString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};" _
      & "SERVER=" & Servidor & ";DATABASE=" & BD & ";" _
          & "UID=" & User & ";PWD=" & Clave & ";PORT=3306;OPTION=131072" 
cnn.Open 

For x = 2 To UltimaFila

  Set rst = New ADODB.Recordset
         Let sql = "Insert Into tablapruebas (Nombre, País, Fecha) Values ('" & Range("A" & x).Value & "','" _
           & Range("B" & x).Value & "','" & Format(Range("C" & x).Value, "yyyy-mm-dd") & "')"     

  With rst
      .CursorLocation = adUseClient         
   .CursorType = adOpenKeyset         
   .LockType = adLockOptimistic
         .Open sql, cnn, , , adCmdText
     End With 

  Set rst = Nothing 

Next x 

cnn.Close 
Set cnn = Nothing 
MsgBox "Todo listo" 

End Sub

Si todo salió bien, tendremos algo así en nuestra tabla:

Espero les sea útil. Hasta la próxima.

Abraham Valencia

El correcto uso de INDIRECTO (I)

En ocasiones tenemos fórmulas en nuestras hojas de Excel y estas casi siempre hacen referencia a otras celdas e incluso en ocasiones hacen referencia a otros libros de Excel. Pero ¿qué pasa cuando cambiamos de ubicación los datos de la celda de la referencia? Menudo problema que en ocasiones solucionamos cambiando o editando todas nuestras fórmulas lo que muchas veces nos toma más tiempo del que quisiéramos o que incluso a veces ocasiona que cometamos errores.

¿Hay alguna forma de facilitar todo esto? Por suerte para todos/as existe INDIRECTO. Dicha función, al ser aplicada en una fórmula, nos devuelve el contenido de la referencia especificada y nos muestra su contenido y/o no es útil al ser “entendida” por dicha fórmula. Sí, sí, quizá resulte un poco enredado entenderlo así que mejor vayamos con los ejemplos. Ah, pero antes, veamos que dice Microsoft sobre los argumentos que usa dicha función:

INDIRECTO (Ref, A1)

Ref (obligatorio): Es la referencia especificada como una cadena de texto.

A1 (opcional): Valor lógico que indica el tipo de referencia especificada: A1 (verdadero) o F1C1 (falso).

Ahora si vamos con los ejemplos, uno bastante simple para comenzar a entender INDIRECTO. Primero escribamos lo que sea en A10, yo colocaré “Hola a todos/as”:

Ahora, vamos a colocar la siguiente fórmula en A1:

=INDIRECTO("A10")

Como la referencia debe ser una cadena de texto, colocamos el A10 entre comillas ¿cuál es el resultado? Pues:

Aquí hemos hecho referencia directamente a una celda (A10) pero ¿qué pasa si lo que queremos es colocar la dirección de una celda que vaya variando? Para ese caso vamos ahora a escribir “B1” (sin las comillas) en la celda A10 y en B1 colocaremos “Hola a todos/as”. Por último, colocaremos lo siguiente en A1:

=INDIRECTO(A10)

Como lo que queremos obtener es el contenido de B1 y en la celda A10 hay texto, nuestra referencia es al contenido de dicha celda A10 no a A10 en específico, por lo que lo he colocado sin las comillas en la formula.

Sé que se leyó medio enredado, pero como para que se entienda, y aunque no es exacto, podríamos decir lo siguiente:

• Si queremos obtener el contenido de una celda con INDIRECTO, la referencia (ref) va entre comillas
• Si queremos obtener el contenido de una celda indicada en otra con INDIRECTO, la referencia (ref) va sin comillas

Un ejemplo más sobre ese punto para que se entienda mejor. Usaremos los mismos datos solo que en la celda A10 en lugar de “B1”, dejemos solo el número uno (1) y en A1 modifiquemos la fórmula así:

=INDIRECTO("B" & A10)

¿Mejor? Espero que sí. En un siguiente artículo seguiremos con el uso de esta función. Hasta la próxima.

Abraham Valencia

Cálculos con fechas: El uso de SIFECHA

Es muy cotidiano que nuestros datos en Excel contengan fechas y que tengamos que realizar algún tipo de operación con ellas; si bien Excel tiene varias funciones para ello hay una que no aparece en la lista de aquellas (no está “documentada”) y que incluso al ser usada en una celda no despliega ningún tipo de “ayuda” (“Intellisense”). Estamos hablando de “SIFECHA”. Al no ser una función "nativa" de Excel (es, por decirlo de un modo, una función "heredada" de Lotus) dicha función no está "documentada" como ya comenté (sí fue “documentada” en Excel 2000) pero a pesar de eso es compatible con todas las versiones de Excel.

Probablemente “SIFECHA” es la función que requiere menos trabajo, en su uso, para los cálculos con fechas. Dicha función nos puede dar el resultado de la diferencia de días, meses, años, o la combinación de ellos, que hay entre dos fechas. Para que se entienda, vamos a ver que argumentos usa dicha función para lo que usaremos la información que brinda Microsoft:

SIFECHA(fecha_inicial;fecha_final;unidad)

Fecha_inicial: Una fecha que representa la primera fecha del período o la fecha inicial. Las fechas pueden escribirse como cadenas de texto entre comillas (por ejemplo, "30/01/2001") como números de serie (por ejemplo, 36921, que representa el 30 de junio de 2001, si usa el sistema de fechas de 1900), o bien como resultado de otras fórmulas o funciones (por ejemplo FECHANUMERO("30/01/2001")).

Fecha_final: Una fecha que representa la última del período o la fecha de finalización.

Unidad: El tipo de información que desea obtener

Unidad	Devuelve
"Y"	El número de años completos en el período.
"M"	El número de meses completos en el período.
"D"	El número de días en el período.
"MD"	La diferencia entre los días en fecha_inicial y fecha_final. Los meses y años de las fechas se pasan por alto. Nota de Microsoft: No se recomienda usar el argumento "MD", ya que su uso presenta limitaciones.
"YM"	La diferencia entre los meses de fecha_inicial y fecha_final. Los días y años de las fechas se pasan por alto
"YD"	La diferencia entre los días de fecha_inicial y fecha_final. Los años de las fechas se pasan por alto.

Vamos a ver algunos ejemplos de su uso. Ingresaremos una fecha en A1 y usaremos la función HOY() en A2:

Luego usaremos las siguientes fórmulas en celdas de la columna B:

Estas fórmulas nos darán los siguientes resultados, aunque hemos agregado algunas indicaciones en celdas de la columna C para que se entienda de mejor modo:

Ahora vamos a hacer algo con una fórmula anidada. Coloquemos esta fórmula en alguna celda:

="Han transcurrido  "&SIFECHA(A1;A2;"Y")&" años  "&SIFECHA(A1;A2;"Ym")&" meses y  "&SIFECHA(A1;A2;"md")&" días desde que  naciste" 

Si todo salió bien, debes tener algo como esto en la celda:

Han transcurrido 41 años 2 meses y 9 días desde que naciste

Interesante ¿no creen? Ah, un detalle más, no olviden que en este caso estoy usando el “punto y coma” como separador de lista o argumentos, si tú usas la “coma”, solo reemplaza. Listo, eso es todo por hoy.

Abraham Valencia