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

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.



13 comentarios:

Anónimo dijo...

Me gusto mucho la macro gracias... solo tengo un detalle que al modificar un valor en la columna B de la hoja 1 no se actualiza en la hoja2, aparentemente se ejecuta la macro y todo pero no actualiza... la otra macro genial!!

El pilt®afilla - www.3piesalgato.com dijo...

Efectivamente eso ocurre, tal y como lo comento en el propio artículo. Se podría solucionar de una forma sencilla, que es revisando cada vez que se edita o introduce un dato, todas las columnas, para poder comprobar si es un dato nuevo, o una modificación de uno ya existente. Como me parecía una carga de trabajo innecesaria, pues debemos comprobar todo lo que hay introducido, por eso me he decantado por pasarle el macro al final (ese que podemos añadir al botón).

Saludos.

Anónimo dijo...

Hola Javier,

He intentado aplicar tu ejemplo 2 pero que me de el resultado en la misma hoja, y no consigue que se ejecute correctamente, unicamente me devuelve el dato b5!!

¿Me podrías ayudar por favor?

Gracias

Fran.

Anónimo dijo...

Hola Javier,
Tengo 5 archivos cada archivo tiene 10 hojas con el mismo formato, pero con diferentes datos(tiene formulas), quiero juntar en un solo archivo esas hojas o sea que este archivo en total tenga 50 hojas y que además se venga cada hoja con el mismo formato pero con los datos pegado como valores. Habrá alguna opción para eso. Agradecería mucho tu ayuda y tu respuesta, qué tengas un buen día.

Anónimo dijo...

Hola Javier:

Lo primero de todo agradecerte el blog, porque para los que no tenemos ni idea de Macros y demás, nos ayuda bastante, sobre todo porque tus explicaciones son muy claras.

Lo segundo, tengo una cuestión que no soy capaz de resolver con la excel y me gustaría comentartelo y es algo extenso, y quizá te sirviera para un nuevo tema en el Blog, a qué e-mail podría enviarte mi consulta? Mi e-mail es davidcharro@hotmail.com

Muchas gracias

Un saludo

Anónimo dijo...

Hola!!
no sería mucho más sencillo aplicar una Tabla dinámica sobre el origen de datos y llegar al mismo resultado???
Un saludo!!

El pilt®afilla - www.3piesalgato.com dijo...

Efectivamente, todo eso se puede hacer con una tabla dinámica o con filtros avanzados, como ya comento en el propio artículo. Como en el blog nos gusta hacer uso de las macros, hemos querido implementar esta tercera opción.

Saludos.

Anónimo dijo...

Hola,
este comentario no continua el hilo del articulo sino que al no encontrar otra manera para contactaros pues lo utilizo como formulario de contacto :)

El contenido del blog está rebien pero siento que tener que deciros que en cuanto a facilidad de uso deja mucho que desear... donde esta la barra de navegacion, la pagina de inicio ... y claro el formulario de contacto ;)

Seguro que algún otro os ha comentado algo

Saludos veraniegos

El pilt®afilla - www.3piesalgato.com dijo...

El hosting del blog es de blogger (blogspot), y no de wordpress, ni tampoco es un CMS propio, por tanto la flexibilidad a la hora de trabajar en esta platafomra es nula, pues debes limitarte a usar las plantillas que hay (o a diseñar una propia).

De todas formas, en cuanto a usabilidad, tienes un buscador arriba (propio de blogger), y todas las entradas del blog que he publicado aparecen en la barra de navegación de la izquierda.

Un saludo.

Yavir dijo...

Maravilloso el artículo, de gran utilidad para trabajar con datos similares. Por favor, Javier, regresa con mas articulos, estamos extrañando tus aportes valiosos.
Me sirvió para analizar una gran cantidad de datos.
A la espera de mas articulos....

El pilt®afilla - www.3piesalgato.com dijo...

Muchas gracias Yavir. Espero poder sacar algo de tiempo este próximo mes de agosto, para publicar algo sencillo que tengo en mente (probablemente sin necesidad de utilizar macros, o si llevara macros, serían bastante sencillos), porque llevo una temporada colapsadísimo con el trabajo, ese que me da de comer :-)

Gracias de nuevo por tu comentario, que ayuda y mucho.

Josue dijo...

Hola buenas tardes, gracias por toda la informacion entregada en esta pagina, ya una vez me ayudaste y queria pedir otra vez tu guia, la situacion es la siguiente:
necesito crear una macro que cada cierto tiempo cambie de la hoja 1 a la hoja 2, durante todo el dia y que cuando vuelva a abrir el archivo comience nuevamente este proceso

existe la forma de hacerlo

muchas gracias

un abrazo
saludos,
Josue Pineda

Unknown dijo...

gracias por los ejemplos me han ayudado de mucho, si todo mundo ayudara asi como lo haces vos sería un mundo diferente, tengo una complicación y me gustaria que me ayudaras, despues de extraer los archivos del fichero, como puedo ordenarlos automaticamente, podrias ayudarme por favor.

Gracias