Hojas de cálculo en Excel - página principal

¿En qué columnas tenemos los datos que buscamos?

En esta ocasión vamos a explicar, a través de una función personalizada, es decir, a través de una función no implementada en el propio Excel, sino que nosotros mismos vamos a programar, cómo conseguir descubrir en qué columnas se encuentra un valor que buscamos dentro de un rango de celdas.

Quizás leyendo el párrafo anterior, no os quede muy claro que es lo que queremos obtener, pero como siempre, vamos a hacerlo lo más ameno posible, explicándolo de una forma sencilla, para que sea más inteligible.

Concretamente, vamos a hacer algo como esto: Imaginemos que tenemos una tabla de Excel, donde hay diferentes valores, ya sean numéricos, alfanuméricos, o simplemente caracteres de texto. Lo que pretendemos, es buscar un valor determinado en esa tabla, para saber en qué columnas se encuentra. Veámoslo con una imagen:


Como veis en esa imagen, hay una serie de vendedores, con sus ventas mensuales, un total anual, y una desviación con respecto a las ventas medias por vendedor. Vamos a complicarnos la vida, y lo que vamos a buscar no son los meses en los que encontremos determinado valor, sino la columna de Excel donde estos se encuentran.

Veámoslo con un ejemplo: Supongamos que queremos buscar en qué columnas tenemos el valor 6.000,00, para el vendedor llamado Pedro. Si os fijáis en la tabla anterior, podemos localizar ese valor en el mes de septiembre, que corresponde a la columna K. Esto último es lo que pretendemos localizar.


Para hacerlo más complicado, y como es probable que el valor buscado no coincida con ninguno de la tabla (por ejemplo, el vendedor Rafael no tiene ventas por importe de 6.000,00), vamos a buscar también aquellas columnas que tengan el valor buscado o uno superior (que se cumpla cualquiera de esas dos condiciones), y también aquellas columnas que tengan un valor inferior. Si queréis, podéis adaptar vosotros este ejercicio, para buscar las columnas cuyos valores que sean iguales o inferiores (que se cumpla cualquiera de esas dos condiciones), o bien, buscar las columnas cuyos valores sean superiores al valor buscado, pues es muy sencillo hacerlo, con los pasos que os indico a lo largo de este artículo.

Vamos al lío… Vamos a construir nuestra función personalizada, para lo cual, en un módulo VBA, copiaremos esto, que nos servirá para localizar las columnas que contengan el valor exacto al valor que busquemos:

Function Localizar(rango As Range, valor As Variant)
'fichamos la celda donde están los datos
'inicial y final (el rango)

mi_rango = rango.Address
'separamos los datos inicial y final
coordenadas = Split(mi_rango, ":")

inicio = coordenadas(0)
fin = coordenadas(1)
'pasamos a una variable la dirección de la celda inicial
celda = Range(inicio).Address
'para todo el rango de datos...
For i = 0 To Range(fin).Column - Range(inicio).Column
     'Comprobamos el valor introducido
     If Range(celda) = valor Then
         columna = columna & " " & Range(celda).Address
         'eliminamos el signo de $ de referencia absoluta
         columna = Replace(columna, "$", "")
     End If
     'pasamos a la siguiente columna
     celda = Range(celda).Offset(0, 1).Address
'seguimos con el bucle
Next
'Si no existe el dato en ninguna columna
If columna = 0 Then columna = " No existe"
'eliminamos los números de la fila
numeros = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
For j = 0 To UBound(numeros)
     columna = Replace(columna, numeros(j), "")
Next
'asignamos a la función, el dato del contador
'eliminando previamente el primer espacio vacío
Localizar = Mid(columna, 2)
End Function

Vale, esto está muy bien, pero ¿cómo se utiliza esta función?. Pues así:

=localizar(rango_de_celdas_donde_buscaremos;valor_a_buscar)

Veámoslo con otro ejemplo:


En la tabla anterior, podemos ver que estamos buscando el valor 6.000,00 en toda la tabla que va, del rango de celdas C7, hasta la celda N12, pero lo hemos separado por vendedores, por lo que en el caso por ejemplo del vendedor Rafael, lo que haremos será buscar el valor 6.000,00 en el rango que va de C7 a N7. Para ello, la fórmula que hemos puesto en la celda C17 es esta:

=localizar(C7:N7;$D$3)

En este caso, como vemos, no hay ventas por valor de 6.000,00, aunque sí las hay por valores superiores e inferiores. Si lo que queremos es obtener las columnas donde haya valores superiores o iguales a 6.000,00, entonces crearemos otra función personalizada a la que llamaremos "localizarMasOIgual", donde copiaremos el código que os puse anteriormente, cambiando estas líneas que os añado a continuación.

Cambiaremos esta línea del código (ojo, que tenemos que llamar a esta nueva función con otro nombre, y yo he elegido el de "localizarMasOIgual"):

If Range(celda) = valor Then

Por esta otra:

If Range(celda) >= valor Then

Y también cambiaremos esta línea:

localizar = Mid(columna, 2)

Por esta otra:

localizarMasOIgual = Mid(columna, 2)

De esta forma, ya tendremos nuestra nueva función creada, donde buscaremos las columnas donde tengamos un valor igual o superior al buscado. Este será el resultado, después de aplicar la nueva fórmula, al rango de datos donde buscaremos las ventas superiores o iguales a 6.000,00:


En el caso de buscar las columnas con valores inferiores al valor buscado (en nuestro ejemplo, ventas inferiores a 6.000,00), crearemos una nueva función a la que llamaremos "localizarMenos". En esta nueva función, cambiaremos el código inicial que os puse de dos líneas, concretamente cambiaremos esta línea:

If Range(celda) = valor Then

Por esta otra:

If Range(celda) <= valor Then

Y también cambiaremos esta línea:

localizar = Mid(columna, 2)

Por esta otra:

localizarMenos = Mid(columna, 2)

Y el resultado después de aplicar la nueva función, será este, si buscamos las columnas donde tenemos ventas de cada vendedor, inferiores a 6.000,00:


Como veis, es muy sencillo adaptar la función inicial, a vuestras necesidades. Si queréis, podéis probar a obtener las columnas donde se encuentran valores superiores al valor buscado. En nuestro ejemplo hemos aplicado la función "localizarMasOIgual", pero nos busca los valores superiores o iguales al valor buscado, y no solo los valores superiores, como os sugiero que hagáis. También podéis crear una función más, para localizar las columnas donde hay valores menores o iguales al valor buscado. En nuestro ejemplo hemos aplicado la función "localizarMenos", pero nos busca solo los valores inferiores al valor buscado.

Desde aquí, podéis descargar el libro de Excel, con el ejemplo que hemos visto en este artículo, para que lo analicéis, lo probéis, y veáis si esta utilidad tiene alguna funcionalidad en vuestra vida diaria, o simplemente os parece otro interesante ejemplo más de cómo crear funciones personalizadas en Excel.



Obtener un consolidado, extrayendo los datos de diferentes ficheros

Hace poco me preguntaba una amiga, si era posible obtener en un fichero único de Excel, una serie de registros procedentes de otros ficheros, de los cuales desconocemos, un montón de cosas, incluido el propio nombre de estos ficheros. Es decir, se trataba de obtener en ese fichero único de Excel, los registros de forma concatenada (o agregada, unos detrás de los otros), de una serie de archivos, de los que desconocemos tanto su número, como su nombre, e incluso, la subcarpeta en la que están estos propios ficheros que serán la fuente desde la que vamos a obtener la información.

Mi respuesta fue: “Ufffffff, no entiendo nada de lo que me planteas. Hazme un ejemplo de lo que necesitas, que soy muy torpe.”

Su explicación fue más o menos así: Imagina que tengo una carpeta donde tengo alojadas una serie de subcarpetas (desconozco su número), y dentro de estas subcarpetas hay una serie de ficheros (desconozco tanto el número de ficheros como el nombre que tienen los mismos), …pues bien, lo que quiero, es que mire en todas esas subcarpetas, y en todos los ficheros que haya dentro, y como todos tienen un esquema similar, que coja esos datos, y me los ponga en un único fichero de Excel, colocando cada bloque de registros de cada fichero, uno detrás de otro, dentro de este único fichero que recoge la información.

Y ante esa respuesta, yo pensé: “¿tú lo que quieres es trabajar poco, y que Excel lo haga todo, einghhhh?”. Pues sí, no estaba equivocado, quería que Excel le hiciese el trabajo tedioso, recopilar los datos, sin tener que abrir cada fichero de Excel, y hacer un “copypaste” en el fichero de destino, que ella ya se encargaría de analizar toda esa información.
Vamos, que la idea era pasar de esto:

A esto otro:

Pues nada, me puse manos a la obra, y tras un buen rato de de análisis, trabajo y depuración de código, conseguimos un macro que haría todo eso en un abrir y cerrar de ojos.
La idea parte de esto que podéis ver en esta imagen, que contiene una serie de carpetas, que a su vez están alojadas en una carpeta que será la que dediquemos a albergar los ficheros desde los que obtendremos la información:

Y dentro de cada carpeta, tenemos diferentes ficheros (en cada carpeta podemos tener un número diferente de ficheros), y con diferentes nombres (aunque puede que haya varios ficheros con el mismo nombre, pero en carpetas diferentes, claro). Este sería un ejemplo de lo que tenemos en la carpeta llamada "Carpeta 4":

Como podéis ver se trata de ficheros de Excel, que en mi caso he hecho con la versión 2003, para que los usuarios con una versión antigua de esta popular hoja de cálculo, vean que no van a tener ningún problema a la hora de utilizar el macro que recopilará la información de los diferentes ficheros. Evidentemente aquellos usuarios que utilicen versiones posteriores de Excel (2007, 2010, 2013, o las que estén por llegar), no van a tener el más mínimo problema, pues nos da igual el nombre que tenga el fichero, o incluso su extensión, pues solo necesitamos quesean ficheros de Excel.
Si abrimos por ejemplo el fichero llamado “Usuarios zona A.xls”, nos encontramos con esto (la estructura o registros de todos los ficheros es similar, aunque los datos contenidos evidentemente no, pues cada fichero tiene datos diferentes, los cuales queremos extraer, para pegarlos de forma concatenada, en un único fichero que nos servirá de agregado para poder analizar los datos, a partir de él):

Algo muy importante, a lo que tengo que hacer mención es que:

  • Da igual el nombre que tengan nuestras carpetas, y el nombre que tengan los ficheros que cuelguen de cada carpeta, y no tienen por qué tener un nombre correlativo. Incluso pueden tener nombre absurdos :-)
  • Da igual tanto el número de carpetas, y de ficheros que cuelguen de cada carpeta, aunque si tenemos más de la cuenta, puede que Excel no pueda procesar tanta información y se nos cuelgue la aplicación (no he probado el límite en el cual se cuelga, porque no soy masoquista).
  • Los datos los obtendremos de la primera hoja de cada uno de los libros.
  • Los datos de los libros deben tener todos la misma estructura (mismo número de campos), pues consolidaremos peras con peras, y no peras con manzanas. No obstante, el número de registros de cada fichero puede ser distinto (en el ejemplo de este artículo trabajamos con registros diferentes en cada fichero, para que veáis como funciona).
Vamos allá. Para hacer todo esto, lo primero que haremos, será crear un fichero que llamaremos “Consolidado.xls”, que contendrá dos hojas. La primera será donde consolidaremos los datos (hemos puesto los encabezados de fila, para ahorrarnos el trabajo en el macro). A esta primera hoja la hemos llamado “Consolidado”. La segunda hoja llamada “Ficheros”, contendrá nos nombres de las carpetas en una columna, y en la adyacente tendremos los archivos que contiene cada carpeta, pero claro, esto no lo haremos manualmente, porque sino no tendría sentido crear un macro. Esta segunda hoja también nos la “rellenará” nuestro macro, pues él solito obtendrá los nombres de las carpetas, y los ficheros que contiene cada una de ellas.

Es decir, en la hoja “Consolidado” de nuestro libro tendremos esta plantilla:

Y en la “Ficheros” tendremos esto (ojo, que esto que veis, es tras la ejecución del macro, pues ha recorrido cada carpeta y cada fichero “fichando” el nombre de todo lo que encuentra dentro, así que para evitar problemas, trabajad solo con ficheros Excel, y no los mezcléis con ficheros Word, o con imágenes, por ejemplo):

Bueno, pues para hacer todo esto, tan solo deberemos copiar y pegar este macro, en un módulo:

Sub Consolidado()
'--------------------------------------------------------
'Lo primero que haremos, será recorrer los directorios
'seleccionando los ficheros que haya en cada uno de ellos
'--------------------------------------------------------
'Si hay errores, que continúe

On Error Resume Next
'seleccionamos la hoja1
Hoja1.Select
'Seleccionamos el botón cuyo nombre queramos cambiar
ActiveSheet.Shapes("Botón 1").Select
'le cambiamos el nombre, y lo ponemos en rojo
Selection.Characters.Text = "Espera unos segundos..."
With Selection.Font
.ColorIndex = 3
End With
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'borramos todo lo que haya de datos anteriores en la Hoja1
Range("A6").Select
If ActiveCell <> "" Then
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
    Selection.ClearContents
    Selection.ClearFormats
End If
'Nos situamos en la hoja 2
Windows(ThisWorkbook.Name).Activate
Hoja2.Select
'borramos lo que haya de otras veces
Range("A2").Select
If ActiveCell <> "" Then
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
End If
'Creamos el objeto FileSystemObject
Set Fso = CreateObject("Scripting.FileSystemObject")
'Informamos de la ruta de donde vamos a obtener
'los ficheros, en este caso, el mismo directorio
'donde tengamos grabado este fichero con el macro
ruta = ActiveWorkbook.Path
'definimos dos variables que necesitaremos,
'para recuperar el nombre de la carpeta, y
'los subdirectorios y ficheros que haya dentro
Set directorio = Fso.GetFolder(ruta)
Set Subdirectorios = directorio.SubFolders
Set ficheros = directorio.Files
'escribimos un encabezado en la celda A2
Range("A2").Select
'escribimos los subdirectorios
For Each Subdirectorio In Subdirectorios
Hoja2.Select
'escribimos el nombre del subdirectorio
ActiveCell = Subdirectorio.Name
    'entramos en el subdirectorio
    Set directorio = Fso.GetFolder(Subdirectorio)
    Set Subdirectorios = directorio.SubFolders
    Set ficheros = directorio.Files
    ActiveCell.Offset(0, 1).Select
    For Each Archivo In ficheros
    'escribimos el nombre del fichero
    ActiveCell = Archivo.Name
    'bajamos una fila
    ActiveCell.Offset(1, 0).Select
    Next
'bajamos una fila y volvemos una columna atrás
ActiveCell.Offset(1, -1).Select
Next
'Limpiamos los objetos
Set Fso = Nothing
Set directorio = Nothing
Set Subdirectorios = Nothing
Set ficheros = Nothing
'---------------------------------------------------------------
'Abrimos los ficheros uno a uno, y vamos copiando y escribiendo
'los datos en el fichero del consolidado en la hoja1 (no importa
'el nombre que tenga esta hoja)
'---------------------------------------------------------------
'abrimos las carpetas desde la 1 a la 50, para no hacer esto interminable
carpeta = Range("A2").Address
For i = 1 To 50
'nos situamos en la celda A2, de la hoja 2, que era donde estábamos,
'creando unas variables que nos serán de ayuda
fichero = Range(carpeta).Offset(0, 1)
j = 1
    Do While Not IsEmpty(fichero)
        Workbooks.Open Filename:=ruta & "\" & Range(carpeta) & "\" & fichero
        'copiamos los datos que tenga desde A2 hasta el final
        Range("A2").Select
        Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
        Selection.Copy
        'los pegamos en nuestro libro
        Windows(ThisWorkbook.Name).Activate
        Hoja1.Select
        If Range("A6") = "" Then
            Range("A6").Select
        Else
            Range("A6").Select
            Selection.End(xlDown).Select
            ActiveCell.Offset(1, 0).Select
        End If
        ActiveSheet.Paste
        Application.CutCopyMode = False
        'nos situamos al final de la lista consolidada
        Selection.End(xlDown).Select
        'bajamos una celda
        ActiveCell.Offset(1, 0).Select
        'cerramos el libro
        Workbooks("" & fichero & "").Close savechanges:=False
        'volvemos a la hoja2
        Hoja2.Select
        'creamos una variable para ir bajando a los ficheros
        fichero = Range(carpeta).Offset(j, 1)
        j = j + 1
        Loop
Range(carpeta).Select
Selection.End(xlDown).Select
carpeta = ActiveCell.Address
Next
'volvemos a la Hoja2
Hoja2.Select
Range("A2").Select
'volvemos a la Hoja1
Hoja1.Select
'Seleccionamos el botón
ActiveSheet.Shapes("Botón 1").Select
'le cambiamos el nombre al botón, poniéndole el que tenía
'inicialmente y lo ponemos en negro
Selection.Characters.Text = "Consolidar"
With Selection.Font
.ColorIndex = xlAutomatic
End With
Range("A6").Select
'Mostramos el procedimiento
Application.ScreenUpdating = True
'mostramos un mensaje al finalizar
CreateObject("wscript.shell").Popup _
" Ya hemos finalizado. ¿Ves como tardaría poco?. " + _
Chr(13) + " Los datos han sido consolidados, y demasiado sin esfuerzo ;-) ", _
4, " Consolidación completada"
End Sub

Cuando ejecutéis el macro, lo primero que hace, es eliminar los datos que haya previamente (si es que los hay). Luego, simplemente consolida los datos de los diferentes ficheros. Os dejo que lo probéis, y veáis que he puesto cada bloque de datos de diferentes colores en los ficheros de origen, para que se vea claramente que en el consolidado se “pegan” de la misma forma, es decir, manteniendo su formato (en este caso el color de origen, para que se vean bien los datos “copypasetados”).

Descomprimid el fichero comprimido de este enlace, en una carpeta, y probadlo. Veréis como el contenido de cada uno de los ficheros, de todas y cada una de las carpetas, pasa a formar parte de un único fichero, que es este desde el que ejecutamos el macro (Consolidado.xls).



Obtener valores únicos en Excel

Vamos a explicar hoy, como podemos generar una lista con valores únicos en Excel, a través del uso de macros. Esta operación la podemos realizar de una forma muy sencilla con Excel 2007 o superior, tal y como nos explica Microsoft en este artículo, utilizando filtros avanzados. Como a nosotros nos gusta complicarnos la vida un poco, queremos que esta operación se realice sin intervención humana ;-) y de forma inmediata, en función de cuando vamos introduciendo valores en una hoja, con independencia de que a medida que los introduzcamos, estén repetidos una o mil veces, así que programaremos un sencillo macro que nos servirá para enseñárselo a nuestros compañeros de trabajo, o a nuestro jefe, y quedar como unos usuarios avanzados en Excel.

En definitiva, lo que vamos a hacer es obtener en una hoja del mismo libro, un listado de valores únicos (no repetidos), a partir de una serie de datos con valores que pueden estar o no repetidos (lo normal es que estén repetidos, para ver en funcionamiento esta utilidad) . Eso es lo mismo que decir, que vamos a eliminar todos aquellos valores duplicados que haya, para dejar un solo valor de cada dato introducido.

Para ilustrarlo de un modo gráfico, lo que pretendemos obtener es esto:


Como veis en la columna de la izquierda (tabla A), tenemos repetidos una serie de valores, en la columna de la derecha (tabla B), solo tenemos los valores únicos. Es decir, lo que haremos será eliminar los duplicados, o filtrar la tabla para que solo aparezcan los valores una sola vez.

Todo esto lo vamos a hacer introduciendo valores en la Hoja1, y en la Hoja2 es donde mostraremos los valores únicos o sin duplicados, de forma instantánea, a medida que vayamos introduciendo valores en la primera de las hojas.

Pensaréis que esto es algo complicado de obtener mediante macros, pero no, no lo es. De hecho es bastante sencillo. A pesar de que el código siempre lo comento -y esta vez no va a ser menos-, explicaré el proceso por el cual, a partir de una tabla donde vayamos introduciendo valores duplicados, obtendremos los valores únicos.

La técnica es muy sencilla. En el mismo instante en el que introduzcamos un valor en la Hoja1, nos desplazaremos a la Hoja2, a ver si ese valor está en la columna donde nos interesa que salgan los valores únicos, y si es así, volveremos a la Hoja1 sin hacer nada. Si por el contrario, el valor no está en esa columna de la Hoja2, lo que haremos será añadirlo, y volveremos a la Hoja1 para continuar introduciendo valores. Todo esto lo haremos sin darnos cuenta, pues visualmente no observaremos ningún refresco de pantalla por el cambio de la Hoja1 a la Hoja2, y de nuevo a Hoja1.

Para ello utilizaremos los siguientes códigos. Este primero, lo colocaremos en la Hoja1:


Private Sub Worksheet_Change(ByVal Target As Range)
'Al introducir un valor en la columna B,
'llamaremos al macro "valores_unicos_instantaneos"

If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then valores_unicos_instantaneos
End Sub

Al modificar un dato de la columna B, se ejecutará el macro llamado “valores_unicos_instantaneos”. Ahora, en un módulo escribiremos este código:

Sub valores_unicos_instantaneos()
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'si hay errores, que continúe
On Error Resume Next
'pasamos el dato que acabamos de introducir, a una variable
dato = ActiveCell
'buscamos ese dato en la Hoja2,
'y más concretamente en la columna B

With Hoja2.Range("B:B")
'buscamos el dato en esta Hoja2
Set existe = .Find(dato, LookAt:=xlWhole)
'si no existe, lo añadimos, pero antes nos
'situaremos en la primera celda donde queremos
'empezar a escribir los valores únicos

If existe Is Nothing Then
'nos situamos en la primera celda donde queremos
'empezar a escribir los valores únicos

Hoja2.Select
Range("B5").Select
'bajamos hasta encontrar una fila vacía
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
'y escribimos el dato
ActiveCell = dato
End If
End With
'subimos al principio de la tabla
Range("B5").Select
'volvemos a la Hoja1
Hoja1.Select
'mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Si hemos hecho todo correctamente, al introducir valores en la Hoja1, de una forma similar a esta, y en la que repetimos ciertos valores (hemos supuesto que introducimos el nombre los vendedores de la empresa):


Obtendremos de forma automática, y sin darnos cuenta, estos otros datos en la Hoja2:


Ahora pensaréis: “vale, muy chulo, pero ¿y si ya tengo la hoja con los datos duplicados?, ¿voy a tener que reescribirlos para que me tome sólo los valores únicos?”. La respuesta es no. No necesitarás hacer nada de eso porque hemos preparado un pequeño macro en Excel que hará el trabajo por ti. Con este sencillo ejemplo, obtendremos valores únicos, a partir de un listado donde tengamos datos duplicados. Es decir, no será necesario que introduzcamos datos para ver como se nos añaden en la Hoja2 los valores únicos, pues tan solo tendrás que ejecutar este macro:

Sub valores_unicos()
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'si hay errores, que continúe
On Error Resume Next
'fichamos la celda donde estamos, para volver a ella al final
celda = ActiveCell.Address
'si en la hoja2, la celda B5 contiene datos,
'eliminamos todas las filas con datos contiguas

If Hoja2.Range("B5") <> "" Then
'seleccionamos la hoja2
Hoja2.Select
Range("B5").Select
'seleccionamos el rango contínuo hasta abajo
Range(Selection, Selection.End(xlDown)).Select
'eliminamos las filas
Selection.EntireRow.Delete
'volvemos a B5
Range("B5").Select
End If
'seleccionamos la hoja1
Hoja1.Select
'y nos situamos en B5
Range("B5").Select
'hasta que no encuentre una fila vacía...
Do While Not IsEmpty(ActiveCell)
'pasamos el dato que acabamos de introducir, a una variable
dato = ActiveCell
'buscamos ese dato en la Hoja2,
'y más concretamente en la columna B

With Hoja2.Range("B:B")
'buscamos el dato en esta Hoja2
Set existe = .Find(dato, LookAt:=xlWhole)
'si no existe, lo añadimos, pero antes nos
'situaremos en la primera celda donde queremos
'empezar a escribir los valores únicos

If existe Is Nothing Then
'nos situamos en la primera celda donde queremos
'empezar a escribir los valores únicos

Hoja2.Select
Range("B5").Select
'bajamos hasta encontrar una fila vacía
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
'y escribimos el dato
ActiveCell = dato
End If
'seleccionamos la Hoja2
Hoja2.Select
'subimos al principio de la tabla
Range("B5").Select
End With
'volvemos a la Hoja1
Hoja1.Select
'bajamos una fila
ActiveCell.Offset(1, 0).Select
'continuamos con el bucle
Loop
'volvemos a la celda donde estábamos
Range(celda).Select
'mostramos el procedimiento
Application.ScreenUpdating = True
'mostramos un mensaje si no hay errores
If Err = 0 Then mensaje = MsgBox("¡Listo!. Acabamos de hacer el " + _
Chr(10) + "trabajo que nos encargaste :-) ", vbInformation, "Trabajo hecho")
End Sub

Ese macro de Excel también nos servirá para depurar la tabla de valores únicos de la Hoja2, en el caso de que eliminemos algún dato que tenga valores únicos de la Hoja1, pues no desaparecerá de forma automática ese dato eliminado en la Hoja2. Otro caso en el que nos servirá el macro, es cuando editamos un dato ya escrito. En ese caso, la primera edición permanecerá en la Hoja2, por lo que convendrá pasarle el macro, para arreglar las modificaciones realizadas en la Hoja1.

Si este nuevo macro de Excel que acabamos de hacer, lo asignamos a un botón como se muestra en la siguiente imagen, con tan solo leer el texto del propio botón, ya sabremos que es lo que va a ocurrir al presionarlo:


Con esto podríamos dar por finalizado este trabajo, porque hemos hecho lo que pretendíamos hacer al principio, que no es otra cosa que obtener valores únicos a partir de una lista con valores duplicados, pero como nos gusta complicarnos la vida, vamos a liarla un poco más.

Vamos a añadir una columna adicional en la Hoja1, con valores numéricos, de tal forma que imaginaremos que los nombres introducidos son los de nuestros vendedores, y los importes serán la cifra de ventas de cada uno de ellos:


Y claro, ahora nos interesará obtener en la Hoja2, no solo los valores únicos, sino además, la suma de la cifra de ventas de cada uno de los vendedores, y para acabar de redondearlo, el número de acciones de venta de cada uno de ellos, es decir, el número de ventas o transacciones, que no será otra cosa que el número de veces que sale en esta Hoja1, cada uno de los vendedores. Esto podríamos conseguirlo por ejemplo, mediante el uso de una tabla dinámica, pero vamos a prescindir de ella, para hacerlo "a pelo" con macros de Excel, como siempre solemos hacer.

Comenzaremos colocando este código en la Hoja1:

Private Sub Worksheet_Change(ByVal Target As Range)
'Al introducir un valor en la columna B o C,
'siempre que tengamos el dato de laotra columna
'llamaremos al macro "valores_unicos_instantaneos"

If (Not Application.Intersect(Target, Range("B:B")) Is Nothing And ActiveCell <> "" And _
ActiveCell.Offset(0, 1) <> "") Or (Not Application.Intersect(Target, Range("C:C")) Is Nothing _
And ActiveCell <> "" And ActiveCell.Offset(0, -1) <> "") Then
'siempre que no estemos en la columna 1
If ActiveCell.Column > 1 Then
'llamamos al macro
valores_unicos_instantaneos
End If
End If
End Sub

Ese código anterior, lo que hace es evaluar si cada vez que escribimos un dato en la columna B, tenemos asociado a su derecha en la columna C, otro dato. Si así fuera, ejecutaríamos el macro llamado “valores_unicos_instantaneos”. También lanzaremos este macro si escribimos un dato en la columna C, y tenemos en su columna inmediatamente anterior otro dato (en la columna B).

Ahora colocaremos este código en un módulo:

Sub valores_unicos_instantaneos()
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'si hay errores, que continúe
On Error Resume Next
'pasamos los datos a variable2
dato1 = Range("B" & ActiveCell.Row)
dato2 = Range("C" & ActiveCell.Row)
'buscamos ese dato1 en la Hoja2,
'y más concretamente en la columna B

With Hoja2.Range("B:B")
'buscamos el dato1 en esta Hoja2
Set existe = .Find(dato1, LookAt:=xlWhole)
'si no existe, lo añadimos, pero antes nos
'situaremos en la primera celda donde queremos
'empezar a escribir los valores únicos

If existe Is Nothing Then
'nos situamos en la primera celda donde queremos
'empezar a escribir los valores únicos

Hoja2.Select
Range("B5").Select
'bajamos hasta encontrar una fila vacía
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
'y escribimos los datos, comenzando por el vendedor
ActiveCell = dato1
'ahora el importe
ActiveCell.Offset(0, 1) = dato2
'ahora añadimos una venta
ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 2) + 1
'si ya existe, sumamos la cantidad, y añadimos una venta
Else
'seleccionamos la Hoja2
Hoja2.Select
'seleccionamos el valor encontrado
existe.Select
'añadimos la cantidad
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) + dato2
'añadimos una venta
ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 2) + 1
End If
End With
'subimos al principio de la tabla
Range("B5").Select
'volvemos a la Hoja1
Hoja1.Select
'mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Este macro que acabamos de implementar está muy bien, siempre que estemos introduciendo datos, pues obtendremos los valores únicos en la Hoja2. Pero como en el caso anterior, tiene un problema, y es que cuando nos equivocamos una vez introducidos el nombre del empleado y la cifra de ventas, si editamos el nombre poniendo un nuevo vendedor, nos genera una nueva línea en la Hoja2, pero sin eliminar el dato anterior. Si eliminamos una fila con un valor único en la Hoja1, tampoco nos lo eliminará en la Hoja2.

Todo esto lo podríamos haber resuelto creando un macro que evalúe toda la columna cada vez que introducimos o editamos un dato en la Hoja1, pero eso es cargar innecesariamente el programa con tareas innecesarias. La solución pasa como en el ejemplo anterior, por ejecutar el macro que os pongo a continuación -una vez finalizada la tabla de la Hoja1-, y que podemos asignar a un botón:

Sub valores_unicos()
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'si hay errores, que continúe
On Error Resume Next
'fichamos la celda donde estamos, para volver a ella al final
celda = ActiveCell.Address
'si en la hoja2, la celda B5 contiene datos,
'eliminamos todas las filas con datos contiguas

If Hoja2.Range("B5") <> "" Then
'seleccionamos la hoja2
Hoja2.Select
Range("B5").Select
'seleccionamos el rango contínuo hasta abajo
Range(Selection, Selection.End(xlDown)).Select
'eliminamos las filas
Selection.EntireRow.Delete
'volvemos a B5
Range("B5").Select
End If
'seleccionamos la hoja1
Hoja1.Select
'y nos situamos en B5
Range("B5").Select
'hasta que no encuentre una fila vacía...
Do While Not IsEmpty(ActiveCell)
'pasamos los datos a variables
dato1 = ActiveCell
dato2 = ActiveCell.Offset(0, 1)
'buscamos ese dato1 en la Hoja2,
'y más concretamente en la columna B

With Hoja2.Range("B:B")
'buscamos el dato1 en esta Hoja2
Set existe = .Find(dato1, LookAt:=xlWhole)
'si no existe, lo añadimos, pero antes nos
'situaremos en la primera celda donde queremos
'empezar a escribir los valores únicos

If existe Is Nothing Then
'nos situamos en la primera celda donde queremos
'empezar a escribir los valores únicos

Hoja2.Select
Range("B5").Select
'bajamos hasta encontrar una fila vacía
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
'y escribimos los datos, comenzando por el vendedor
ActiveCell = dato1
'ahora el importe
ActiveCell.Offset(0, 1) = dato2
'ahora añadimos una venta
ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 2) + 1
'si ya existe, sumamos la cantidad, y añadimos una venta
Else
'seleccionamos la Hoja2
Hoja2.Select
'seleccionamos el valor encontrado
existe.Select
'añadimos la cantidad
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) + dato2
'añadimos una venta
ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 2) + 1
End If
'seleccionamos la Hoja2
Hoja2.Select
'subimos al principio de la tabla
Range("B5").Select
End With
'volvemos a la Hoja1
Hoja1.Select
'bajamos una fila
ActiveCell.Offset(1, 0).Select
'continuamos con el bucle
Loop
'volvemos a la celda donde estábamos
Range(celda).Select
'mostramos el procedimiento
Application.ScreenUpdating = True
'mostramos un mensaje si no hay errores
If Err = 0 Then mensaje = MsgBox("¡Listo!. Acabamos de hacer el " + _
Chr(10) + "trabajo que nos encargaste :-) ", vbInformation, "Trabajo hecho")
End Sub

De tal forma que tras ejecutar ese macro en la Hoja1, sobre esta tabla con datos:


Obtendríamos esto en la Hoja2:


Como veis, con los macros podemos idear una alternativa fiable, para competir tanto a los filtros avanzados, como a las tablas dinámicas, a la hora de presentar valores únicos, y para construir tablas con un resumen de datos.

Desde aquí podéis descargar un fichero zip comprimido, con los ejemplos de Excel que hemos visto en este artículo. En el fichero comprimido se incluye tanto el libro de Excel “Valores únicos (ejemplo 1).xls”, como el libro “Valores únicos (ejemplo 2).xls”, que son los que hemos utilizado para ilustrar este artículo.