Registrar entradas con fórmulas – una aclaración

En la nota sobre registro de entradas con fórmulas en Excel, sugerí el uso de Tablas para evitar tener que copiar las fórmulas con cada entrada.

Sin embargo, y como me señalan varios de mis lectores, el uso de tablas en este caso puede dar resultados inesperados. Veamos:

Al introducir el segundo nombre nos hemos movido con las flechas y el resultado es 0, que por el formato fecha-hora vemos como 00/01/1900 00:00:00.

Sin embargo, si nos movemos con la tecla TAB veremos que el resultado es el correcto:

En conclusión: podemos usar un rango común de Excel, copiando las fórmulas o convertir el rango en Tabla y movernos dentro de esta con la tecla TAB.

Registrar fecha y hora de una entrada en Excel con eventos

En la nota anterior vimos como registrar la fecha y hora de una entrada (timestamp) usando fórmulas.

Por supuesto puede hacerse también con macros, más precisamente, programando un evento.

El código del evento, que ponemos en el módulo de la hoja correspondiente, es el siguiente:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngColumnaNombre As Range
   
    Set rngColumnaNombre = Range("A:A")
   
    If Union(Target, rngColumnaNombre).Address = rngColumnaNombre.Address Then
        Select Case Len(Target)
            Case Is > 0
                Target.Offset(0, 1) = Now
            Case Else
                Target.Offset(0, 1).ClearContents
        End Select
    End If
      
End Sub

En nuestro ejemplo la columna A contiene los nombres y la columna B el registro de la fecha. Si borramos un nombre, también la fecha es eliminada.

El código evalúa la celda activa cuando se produce un cambio en la hoja; si la celda activa está en la columna A y no esta vacía, se registra la fecha y hora en la celda paralela de la columna B.

En este código definimos una variable de tipo rango

Set rngColumnaNombre = Range("TablaNombres").Columns(1)

La variable se refiere a la primera columna de la tabla “TablaNombres”

Esto nos permite crear una rango dinámico de manera que valores introducidos fuera de la tabla no disparan el evento.

Existen, por supuesto, otras formas de crear rangos dinámicos en el código.

1 – crear un nombre que se refiera al rango dinámico usando una fórmula con las funciones DESREF o INDICE; por ejemplo

=Hoja1!$A$2:INDICE(Hoja1!$A:$A,CONTARA(Hoja1!$B:$B)+1)

El código es

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Union(Target, Range("TablaNombres")).Address = Range("TablaNombres").Address Then
        Select Case Len(Target)
            Case Is > 0
                Target.Offset(0, 1) = Now
            Case Else
                Target.Offset(0, 1).ClearContents
        End Select
    End If
      
End Sub

2 – definir el rango en el código

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TablaNombres As Range
   
    Set TablaNombres = Range(Range("A2"), Cells(Range("A2").End(xlDown).Row + 1, 1))
    If Union(Target, TablaNombres).Address = TablaNombres.Address Then
        Select Case Len(Target)
            Case Is > 0
                Target.Offset(0, 1) = Now
            Case Else
                Target.Offset(0, 1).ClearContents
        End Select
    End If
      
End Sub

Registrar fecha y hora de una entrada en Excel con fórmulas

Poniéndome al día con los 196 mails acumulados durante mis vacaciones, me encuentro con esta consulta:

quiero capturar la hora y la fecha del momento que ingresar este información, la función HOY() y AHORA() no sirven por que estas se actualizan cada vez que ingreso datos en la hoja; es necesario para mi que estas queden estáticas para realizar posteriores cálculos y estadísticas con ellas…

Mi primera respuesta fue que la única forma de hacerlo es programando un evento (macro). Pero después de investigar un poco el tema descubrí (y muchos otros antes de mi) que puede hacerse con fórmulas.

Para hacerlo con fórmulas tenemos que crear una referencia circular, tema mencionado tangencialmente en la prehistoria de este blog en esta nota.

En pocas palabras, ¿qué es una referencia circular? Cuando una fórmula incluye una referencia a la misma celda que la contiene o una celda que se refiere a ésta, Excel genera una advertencia de "referencia circular".

En este ejemplo, queremos calcular la ganancia neta que incluye el pago de comisiones que a su vez son calculadas en base a la ganancia neta, creándose así una referencia circular (la celda B3 contiene la fórmula =B4*15% y la celda B4 la fórmula =B1-B2-B3 que se refiere a la celda B3)

Al apretar Aceptar veremos

Para que Excel pueda resolver el cálculo tenemos que habilitar el cálculo iterativo en Opciones de Excel-Fórmulas

Después de habilitar el cálculo iterativo Excel muestra el resultado

Una vez definido el cálculo iterativo podemos usar esta fórmula para crear una registro de fecha y hora ("timestamp" en inglés)

=SI(A2<>"",SI(B2="",AHORA(),B2),"")

Para evitar tener que copiar la fórmula a lo largo de la columna A, transformamos el rango en Tabla (en un rango definido como Tabla las fórmulas y formato de las columnas son copiadas automáticamente)

26/10/2012 - Aclaración importante sobre el uso de Tablas en este modelo.

En la próxima nota veremos como hacerlo programando un evento (macros).

El extraño caso del espacio inamovible en Excel (o ASCII 160)

Cuando importamos datos a una hoja de Excel, en particular de una base de datos o de la Web, las celdas pueden contener, además del valor visible, un espacio o algún carácter no imprimible.

La presencia de estos caracteres crea varios problemas: los números son interpretados por Excel como texto o los resultados de filtrar u ordenar resultan imprevisibles.
Excel nos provee con dos funciones para enfrentarnos con estos problemas: ESPACIOS() y LIMPIAR().

ESPACIOS() remueve todos los espacios excepto los espacios individuales entre palabras. Pero no siempre es así. Veamos este ejemplo

La celda A2 contiene tres caracteres visibles (abc) pero la función LARGO da un valor de 4. Este se debe a que la celda contiene un espacio en blanco después de "c".

En la celda A3 usamos ESPACIOS() para remover el espacio y vemos que ahora el largo es de 3.

Ahora veamos este caso, aparentemente idéntico

A pesar de que ambos casos parecen idénticos, en este segundo caso LIMPIAR no logra remover el espacio en blanco. Esto se debe a que la función LIMPIAR fue diseñada para remover el carácter de espacio de 7-bit ASCII (valor 32). Pero ciertos programas, y en especial datos provenientes del Web usan el carácter ASCII 160. Este carácter se utiliza comúnmente en las páginas Web como la entidad HTML. La función LIMPIAR no quita este carácter de espacio.

Una solución es usar la función SUSTITUIR()

=SUSTITUIR(A2;CARACTER(160);"")

Si se trata de un número (que ha sido convertido en texto por la presencia del espacio), agregamos un doble signo menos (--) al principio de la fórmula para forzar la conversión a número

El uso de la función SUSTITUIR puede ser menos conveniente cuando tenemos que ocuparnos de un gran número de registros. En esos caso es mejor usar una macro como ésta

Sub limpiar_todo()

    Selection.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
       
End Sub

Esta macro quita todos los espacion generados por el carácter 160 en el rango seleccionado. Si queremos quitar todos los espacios en la hoja usamos

Sub limpiar_todo()

    Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
       
End Sub