Control de cambios personalizado en Excel – segunda nota

He recibido varios comentarios sobre la nota de ayer sobre el control de cambios personalizado. Entre ellos me piden dos modificaciones que encuentro muy apropiadas:

1 – que el último cambio aparezca en primer lugar en el cuadro de texto, de manera que siempre veamos los últimos cambios;

2 – que los cambios queden registrados en una hoja del cuaderno.

Ambas modificaciones son bastante sencillas y en esta nota mostraremos cómo hacerlas.

Para que el último cambio aparezca siempre en primer lugar en el cuadro de texto modificamos levemente el código de la macro "track_change"

Sub track_change()
    Dim strPrevious As String
    
    With ActiveCell
        If .Comment Is Nothing Then
            .AddComment Text:=Format(Now, "dd/mm/yy hh:mm") & " - " & .Value
        Else
            strPrevious = .Comment.Text
            .Comment.Text Text:=Format(Now, "dd/mm/yy hh:mm") & " - " & .Value _
                                & Chr(10) & strPrevious
        End If
    End With
    
    Call record_in_sheet
    
End Sub

Todo lo que hacemos es cambiar el orden de construcción del texto del comentario poniendo la variable "strPrevious" al final de la cadena de texto.

Para registrar los cambios en una hoja, empezamos, obviamente, por agregar la hoja al cuaderno

Como ven, hemos agregado la hoja "control" con cuatro campos (columnas): celda, fecha y hora, valor e introducido por.

El código para introducir los datos en esta hoja es el siguiente

Sub record_in_sheet()
    Dim lngFirstFreeRow As Long
  
    With Sheets("control")
        lngFirstFreeRow = WorksheetFunction.CountA(.Range("A:A")) + 1
        .Cells(lngFirstFreeRow, 1).Value = ActiveCell.Address
        .Cells(lngFirstFreeRow, 2).Value = Format(Now, "dd/mm/yy hh:mm")
        .Cells(lngFirstFreeRow, 3).Value = ActiveCell.Value
        .Cells(lngFirstFreeRow, 4).Value = Environ("username")
    End With
  
End Sub

Como ven, sencillo y sin muchas vueltas. Para activar el código hemos agregado la línea "Call record_in_sheet" en el código anterior. De esta manera, el evento de la hoja hace correr el código que crea el comentario con los cambios y este código a su vez, hace correr la macro que registra el cambio en la hoja "control".

Después de introducir y cambiar algunos valores en la lista de precios

La hoja de control los refleja de esta manera

Control de cambios personalizado en Excel

Hay situaciones en las cuales queremos guardar una historia de los cambios que realizamos en una celda o rango determinado. Por ejemplo, en una lista de precios queremos guardar la historia de los precios anteriores de cada artículo.

Una posibilidad es usar la funcionalidad Control de Cambios que nos ofrece Excel

EL problema con esta funcionalidad es que convierte el cuaderno en compartido, con la consiguiente pérdida de funcionalidades.

Un cliente me pidió que encontrara una solución más práctica. Mi solución fue utilizar los comentarios para guardar la historia de los cambios, asignándole a cada valor en el comentario la fecha en que fue introducido en la celda.

Mi solución utiliza una macro para crear y agregar información a los comentarios y un evento para disparar la macro cada vez que se cambia el valor de una celda en el rango relevante.

Al ingresar por primera vez un precio en la lista, se genera un comentario como este

Después de realizar un cambio en la celda, el comentario refleja los dos valores "históricos" de la celda

El código da la macro es


Sub track_change()
    Dim strPrevious As String
   
    With ActiveCell
        If .Comment Is Nothing Then
            .AddComment Text:=Format(Now, "dd/mm/yy hh:mm") & " - " & .Value
       
        Else
            strPrevious = .Comment.Text
            .Comment.Text Text:=strPrevious & Chr(10) & _
                        Format(Now, "dd/mm/yy hh:mm") & " - " & .Value
        End If
   
    End With
   
   
End Sub


Este código hay que ponerlo en un módulo corriente del editor de Vb.

El evento hay que ponerlo en el módulo de la hoja relevante


Private Sub Worksheet_Change(ByVal Target As Range)

    If Union(Target, Range("rngPrecio")).Address = Range("rngPrecio").Address _
            Then Call track_change
   
End Sub


Como pueden apreciar, estoy utilizando un rango dinámico dentro de un nombre (rngPrecio) para controlar el rango donde aplicamos el control de cambios

=DESREF(Hoja1!$C$2,1,,CONTARA(Hoja1!$B:$B),1)

En nuestro ejemplo, el rango se aplica dinámicamente a la columna C que contiene los precios. Un detalle importante en la definición del nombre es "anclar" el rango en la celda C2. Esto es necesario para el caso que el usuario elimine todas las líneas de la lista de precios (estamos suponiendo que no borrará los encabezamientos).

Y un cambio más antes de publicar la nota

Un inconveniente con esta solución es que el tamaño del cuadro de texto de los comentarios no se adapta dinámicamente a la cantidad de texto contenido. Para visualizar todos los cambios, en algún momento deberemos cambiar el tamaño del cuadro manualmente.

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.

En Excel 2007 usamos Datos-Ordenar y Filtrar-Avanzadas

El problema con este método, tanto con Excel Clásico como con Excel 2007, es que aparentemente podemos extraer la lista de valores únicos sólo a la hoja que contiene la lista

Pero si leemos con atención el mensaje de Excel, vemos que dice "Sólo se puede copiar los datos filtrados a la hoja activa". Y remarco "hoja activa", ya que esta es la clave para resolver el "problema".

Cuando queremos copiar valores únicos de una lista a una hoja distinta de la hoja que contiene los datos, sencillamente activamos el Filtro Avanzado desde la hoja que recibirá la lista de valores únicos. De esta manera la hoja activa es la que recibirá los valores únicos.

Veamos el caso en nuestro ejemplo. La lista de nombres, con varios valores repetidos, está en la hoja "lista"; nuestro objetivo es copiar la lista de valores únicos en la hoja "únicos".

Elegimos la celda de la hoja "únicos" donde queremos poner los valores únicos (A1 en nuestro caso) y activamos Filtro Avanzado y ponemos todas las definiciones excepto "Rango de la lista"

Ahora señalamos el rango de la lista

Al apretar "Aceptar" los valores únicos serán copiados al rango deseado.
Antes de descubrir esta solución (y supongo que no soy el primero en descubrirla) me había embarcado a escribir una macro para hacer la tarea. Moraleja: dedicar siempre unos segundos para evaluar si entendemos lo que nos dicen.

Transformar números en forma de texto a valores numéricos en Excel

Esta nota ha sido corregida a partir de las observaciones de mis lectores

Podría empezar esta nota con el conocido adagio "basado en hechos reales". Sucede que no existe una convención internacional sobre separadores de decimales y de miles. En Argentina y España por ejemplo, se usa punto para separar los miles y la coma para separar los decimales. Pero en otros países, como México, Puerto Rico y Estados Unidos, se usa la coma para separar los miles y el punto para los decimales.
Cuando introducimos un valor en una celda, Excel interpreta si es número o texto de acuerdo a las definiciones regionales

Como se puede apreciar, en Argentina se usa el punto para separar los miles y en México la coma.

Si un usuario en la Argentina recibe un cuaderno Excel hecho en México, Excel "traduce" el número de un sistema al otro. Los problemas empiezan cuando los datos son enviados de México a la Argentina en formato .txt o, peor aún, .csv.

Por ejemplo, sin un usuario mexicano envía un archivo .csv donde aparece el valor "123,456.78", que para él es un número, al abrir el archivo con las definiciones regionales de la Argentina, Excel lo transformará en texto.

En esta nota veremos cómo solucionar el problema con funciones Excel y, por supuesto, con UDF (funciones definidas por el usuario, macros).

En esta tabla,

los valores en la columna A son texto; en la columna B san sido transformados a números con esta fórmula

=VALOR(SUSTITUIR(SUSTITUIR(A2;",";".");".";",";LARGO(SUSTITUIR(A2;",";"."))-LARGO(SUSTITUIR(SUSTITUIR(A2;",";".");".";""))+ESERROR(HALLAR(".";A2))))

Vamos a explicar cómo funciona esta fórmula pasa por paso.

El primer paso consiste en reemplazar todas las comas que pueda haber en el texto por puntos. Esto lo hacemos en la columna B (Auxiliar 1) con la fórmula

=SUSTITUIR(A2;",";".")

Luego creamos una cadena sin separadores, en la columna C, con

=SUSTITUIR(B2;".";"")

Comparando el largo de las cadenas en B y C podemos saber cuántos separadores (puntos y comas) hay en la cadena con esta fórmula, lo que hacemos en la columna D

=LARGO(B2)-LARGO(C2)+ESERROR(HALLAR(".";A2))

Usamos la expresión =ESERROR(HALLAR(".";A2)) para agregar un 1 en caso que el "texto" no contenga una parte decimal

Este dato nos será útil en la columna E, para reemplazar el último punto en la cadena por el separador de decimales, la coma

=VALOR(SUSTITUIR(B2;".";",";D2))

Como se puede apreciar, también combinamos la función VALOR, para convertir la cadena de texto resultante de usar SUSTITUIR en un valor numérico.

En la columna F reemplazamos todas las fórmulas intermedias por referencias a la celda que contiene la cadena de texto (la columna A), resultando nuestra "mega-fórmula"

=VALOR(SUSTITUIR(SUSTITUIR(A2;",";".");".";",";LARGO(SUSTITUIR(A2;",";"."))-LARGO(SUSTITUIR(SUSTITUIR(A2;",";".");".";""))+ESERROR(HALLAR(".";A2))))

En definitiva, podemos usar esta fórmula sin todos los pasos intermedios.

También podemos incluir la fórmula en un nombre, lo que la hará más legible

Function TextNum_to_Num(strNumText As String, sepDec)
   
    Dim numInt As String 'la parte entera del numero
    Dim numDec As String  'la parte decimal del numero
    Dim arrNumTemp() As String
    Dim sepMiles
   
    If sepDec = "." Then
        sepMiles = ","
    Else
        sepMiles = "."
    End If
   
   
    arrNumTemp = Split(strNumText, sepDec, -1)
    numInt = WorksheetFunction.Substitute(arrNumTemp(0), sepMiles, "")
   
    Select Case WorksheetFunction.CountA(arrNumTemp)
        Case Is = 1
            TextNum_to_Num = Val(numInt)
        Case Else
            numDec = arrNumTemp(1)
            TextNum_to_Num = Val(numInt & sepDec & numDec)
    End Select
   
End Function


Los argumentos de esta función son

• strNumtoText: la celda que contiene que la cadena de texto que queremos convertir en número
• sepDec: el separador de decimales usado en la cadena de texto (distinto del usado en las definiciones regionales).

El archivo con los ejemplos y el código de la función se puede descargar aquí

Fórmulas con resultados múltiples

Hay situaciones en las que una fórmula puede dar más de un resultado. El caso clásico es con las funciones de búsqueda como BUSCARV o INDICE.


La alternativa más práctica en estos casos es usar Autofiltro (o Filtro Avanzado). Pero hay situaciones, por ejemplo cuando construimos un tablero de comandos (dashboard), donde queremos que los resultados aparezcan en un rango determinado de la hoja o en otra hoja.

Supongamos que tenemos esta tabla (la misma que usamos en la nota sobre el uso de BUSCARV en listas con valores repetidos):

En otra hoja queremos poner el nombre de un producto y que se desplieguen todas las órdenes de compra del producto.

Si usamos BUSCARV (o INDICE con COINCIDIR) para obtener todas las órdenes de compra de tornillos, sólo podemos obtener un resultado

Sólo podemos obtener un resultado por celda. No hay ninguna forma de escribir resultados de una fórmula en otra celda que no contenga la fórmula, al igual que no podemos cambiar la estructura de la hoja con fórmulas.
Si queremos poner en una hoja de Excel todos los resultados posibles de una fórmula de búsqueda tendremos que usar otras técnicas.

En esta nota mostraré dos técnicas para obtener todos los resultados. Una estrategia posible es usar múltiples fórmulas.


Establecemos por adelantado cuál pueda ser el número máximo de resultados a obtener y en un rango de celdas contiguas introducimos esta fórmula matricial:

=INDICE(od_compra;K.ESIMO.MENOR(SI(DESREF(od_compra;0;0;FILAS(od_compra);1)=$B$2;FILA(DESREF(od_compra;0;0;FILAS(od_compra);1))-FILA(DESREF(od_compra;0;0;1;1))+1;FILA(DESREF(od_compra;FILAS(od_compra)-1;0;1;1))+1);FILA()-3);2)

donde el nombre "od_compras" define el rango A2:B10 de nuestro ejemplo

Por ejemplo, en una nueva hoja ("formulas") introducimos la fórmula en la celda A4 y la copiamos en todo el rango hasta la celda A9 (estamos suponiendo que el número máximo de órdenes posibles es seis)

Dado que sólo hay tres órdenes de compras para tornillos, el resultado en las celdas A18:A20 es #¡REF!

Para ocultar estos resultados podemos usar formato condicional

Esta técnica tiene varias desventajas: el uso de fórmulas matriciales, el uso de DESREF que es volátil, y además es complicada.

Una técnica alternativa es usar la cámara junto con Autofiltro.

Activamos el Autofiltro en la tabla de datos (en la hoja "datos"). Luego seleccionamos todo el rango de la tabla sin incluir los encabezamientos y activamos la cámara; creamos una nueva hoja ("cámara") y pegamos la imagen en el lugar deseado

En la hoja Datos filtramos la tabla de acuerdo al producto deseado

La imagen en la hoja "cámara" reflejará el cambio instantáneamente

El inconveniente con este modelo es que debemos ir a la hoja Datos para hacer el filtrado. Si queremos manejar los resultados desde la hoja "cámara", tendremos que utilizar una macro para manejar el filtrado por "control remoto".

Empezamos por agregar una lista desplegables, con validación de datos, en la celda B2 de la hoja "cámara" para poder controlar el filtrado

Luego ponemos este código en un módulo común del editor de Vb

Sub filtrado_ordenes()
    Dim strCrit As String
    Dim rngTablaDatos As Range
  
    strCrit = Sheets("camara").Range("B2")
  
    Set rngTablaDatos = Sheets("datos").Range("A1").CurrentRegion
  
    If strCrit <> "Todos" Then
        rngTablaDatos.AutoFilter Field:=1, Criteria1:=strCrit
    Else
        rngTablaDatos.AutoFilter
    End If
  
End Sub

Para activar el código cuando elegimos el producto programamos este evento en el módulo de la hoja "cámara"

Private Sub Worksheet_Change(ByVal Target As Range)
  
    If Target.Address = "$B$2" Then Call filtrado_ordenes

End Sub

Con esto hemos completado nuestro modelo. Podemos ocultar la hoja "datos" de manera que nuestro usuario sólo vea la hoja "cámara" desde la cual puede manejar las que órdenes serán exhibidas en la hoja.

El archivo con el ejemplo se puede descargar gratuitamente aquí.