Extraer valores únicos en Excel

Existen diferentes métodos para extraer valores únicos en Excel, ya sea utilizando comandos de la herramienta o a través de una fórmula. Pero si quieres automatizar por completo este proceso será necesario crear una macro.

A continuación revisaremos varias alternativas que tenemos en Excel para extraer los elementos únicos de un rango. Te recomiendo leer cada una de ellas y elegir la opción que mejor se adapte a tus necesidades.

Extraer valores únicos con comandos

Si en tus labores cotidianas la tarea de extraer valores únicos es poco frecuente, entonces será suficiente con utilizar el comando Quitar duplicados. Antes de utilizar este comando te recomiendo hacer una copia de la columna que contiene los datos originales ya que dicho comando modificará el valor de las celdas.

Una vez que hayas hecho la copia, deberás seleccionar una celda de dicho rango e ir a Datos > Herramientas de datos > Quitar duplicados, lo cual mostrará el cuadro de diálogo Quitar duplicados.

Extraer valores únicos en Excel

Si has colocado un encabezado a la copia de datos realizada deberás asegurarte de marcar la caja de selección Mis datos tienen encabezados. Al pulsar el botón Aceptar se mostrará un mensaje indicando la cantidad de valores duplicados encontrados y de valores únicos que permanecen después de realizar la operación. Como resultado obtendremos una lista de valores únicos:

Extraer valore únicos de una lista en Excel

Además del comando Quitar duplicados existe otro comando para extraer valores únicos en Excel y me refiero al Filtro Avanzado. A través de este comando podemos indicar a Excel que deseamos copiar los registros únicos de nuestra lista:

Extraer elementos únicos de un rango en Excel

Si quieres conocer más sobre el Filtro Avanzado te recomiendo consultar el artículo Crear una lista de valores únicos en Excel donde encontrarás un ejemplo detallado.

Extraer valores únicos con una fórmula

Los comandos mencionados anteriormente requieren que tú hagas algunas cosas, como seleccionar los rangos y pulsar ciertos botones. Si quieres utilizar un método que no requiera tanta intervención de tu parte, entonces puedes utilizar una fórmula de Excel para extraer los valores únicos. La fórmula que utilizaremos será la siguiente:

=SI.ERROR(INDICE($C$2:$C$31,COINCIDIR(0,INDICE(CONTAR.SI($F$1:F1,$C$2:$C$31),0,0),0)),"")

En esta fórmula existen solo dos rangos de celdas que explicaré a continuación. El primer rango es $C$2:$C$31 que se refiere a la ubicación donde se encuentran los datos originales. El segundo rango es $F$1:F1, que es el primer argumento de la función CONTAR.SI, se refiere a la primera celda de la columna donde se colocarán los valores únicos. Observa que la primera referencia de este rango es absoluta y la segunda es relativa y deberás mantenerla de esa manera.

Otra condición necesaria para que la fórmula funcione correctamente, es que la columna que contendrá los valores únicos deberá tener una celda de encabezado. Esto es necesario porque la fórmula será colocada en la celda F2 haciendo referencia al rango $F$1:F1 y si no tuviéramos la celda con el encabezado se crearía una referencia circular.

Una vez que hayas adecuado ambos rangos a tus datos y la columna de resultados tenga un encabezado, deberás colocar la fórmula en la segunda fila y copiarla hacia abajo tantas veces como sea necesario para contener todos los valores únicos que serán extraídos. En la siguiente imagen puedes observar el resultado de esta fórmula:

Lista de valores únicos con fórmulas en Excel

La función más importante de esta fórmula es CONTAR.SI ya que para cada fila se toma en cuenta el resultado de las filas superiores, de manera que podamos saber si un valor ya ha aparecido previamente. Por esta razón, el primer argumento de la función COINCIDIR es el valor cero, que nos asegura que cada valor analizado ha aparecido cero veces en la columna de resultados.

La función INDICE se encarga de copiar el valor de los datos originales y además utilizamos la función SI.ERROR para evitar desplegar el error #N/A que se mostrará en las celdas inferiores una vez que se ha terminado el listado de valores únicos.

Extraer valores únicos con una macro

Una manera más directa y automática de extraer valores únicos en Excel es utilizando una macro. El código VBA que nos ayudará a realizar dicha extracción es el siguiente:

Sub ValoresUnicos()
Dim listaOrigen As Range

On Error Resume Next
Set listaOrigen = Application.InputBox _
(Prompt:="Rango de datos origen:", Title:="Seleccionar rango", Type:=8)

listaOrigen.AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=ActiveCell, Unique:=True

Canceled:
End Sub

La línea 5 se encarga de mostrar un cuadro de diálogo para preguntarnos el rango que contiene la lista con los datos de origen. La línea 8 ejecuta el método AdvancedFilter que nos ayuda a obtener los valores únicos y colocarlos en la celda activa.

Para utilizar esta macro comienzo por seleccionar la celda donde se colocarán los valores únicos y posteriormente ir a Vista > Macros y elegir la macro a ejecutar. De inmediato se mostrará el cuadro de diálogo  que solicitará el rango de datos origen donde colocaré la referencia a la columna completa de la siguiente manera:

Cómo obtener una lista de valores únicos en Excel

Como resultado obtendremos la lista de valores únicos a partir de la celda activa:

Macro para extraer valores únicos en Excel

De esta manera hemos revisado diferentes alternativas para obtener una lista de valores únicos ya sea utilizando comandos, fórmulas o una macro. Descarga el libro de trabajo utilizado en este artículo y sigue experimentando con la extracción de valores únicos en Excel.

26 comentarios en “Extraer valores únicos en Excel

  1. LUIS ROMERO GONZALES

    Es un buen elemento para controlar la gestión del almacen.
    Gracias.

  2. William Puentes

    Como siempre…. tus contribuciones son de gran ayuda para el que quiere aprender….

  3. Cindy Lopez

    Excelentes tips

  4. Jesus Medrano

    Excelente explicación, en lo personal utilizo cada opción dependiendo de lo que deseo hacer con una base, ya que no siempre tenemos el tiempo suficiente y la frecuencia del uso de la misma base es de solo una vez.

    Saludos.

  5. Windel Cruz

    Muy bien. Esta información es de gran ayuda para los que utilizamos excel como herramienta de trabajo.

  6. José Hernández

    Dentro de las cosas que buscaba para crear mis trabajos semanales de Programación Actuarial, el contenido de esta página es increible.

  7. Jorge Aldana

    Mi buen tema mi estimado, gracias por compartir tus conocimientos.

  8. Ernesto

    Gracias, de mucha ayuda.

  9. Alejandro

    Hola,

    Muy buena tu página.
    Una consulta, probe la macro y asume que el primer termino del rango seleccionado corresponde al encabezado, ¿Cómo puedo evitar que coloque encabezado?
    Gracias
    Saludos

    1. Moisés Ortíz Autor

      Hola Alejandro, en el ejemplo mostrado para la macro indiqué el rango C:C que le indica a la macro considerar toda la columna incluyendo la celda C1 que es el encabezado. Por esa razón, dicho encabezado se muestra como parte de los valores únicos. Si no deseas considerar dicha celda, entonces debes indicar el rango comenzando por la celda C2, por ejemplo C2:C100.

  10. Felipe Anaya

    Muy útil en mi trabajo, en tu opinión cual método de los tres es el mas rápido si hablamos de listas muy largas ?

    Gracias.

    1. Moisés Ortíz Autor

      Hola Felipe, con grandes cantidades de datos, el método más lento es con fórmulas por la cantidad de cálculos que tendría que hacer Excel. Así que sugeriría utilizar los comandos de Excel que es funcionalidad “nativa” de la herramienta.

  11. ALFREDO SANCHEZ

    Agradezco este tema que me va a servir para mis labores con excel y aplaudir la explicacion tan didactica
    Gracias

  12. victor hugo

    Excelente tu explicacion felicidades

  13. Martín Flores

    Excelente Solución para mis registros…. había tenido bastantes problemas para filtrar más de 750 líneas.

    Gracias!

  14. Horacio Carmona

    Gracias Moy, estaba de incapacidad, pero ya estoy de regreso!!!

  15. strauss

    agraciendote tus atinadisimos consejos de excel. muchas gracias !!!!

  16. Sergio

    Es una herramienta excelente y muy didactica para los que queremos aprender a manejar excel
    FELICIDADES MOISES

  17. Diego

    Muy buen aporte, mostrar distintas opciones para una misma solución me parece excelente. la opción que más me agrada es el uso de formulas.
    Saludos! DJ

  18. Rossy

    Excelente, me funcionó. Gracias

  19. Pedro Pablo

    Hola Moises, ¿existe alguna manera mediante fórmulas en que no sólo muestre la lista con valores únicos, sino que además la entregue ordenada alfabéticamente?

    1. Moises Ortiz Autor

      Hola Pedro, en el siguiente artículo encontrarás una fórmula para ordenar datos en Excel:
      http://exceltotal.com/formula-para-ordenar-datos-en-excel/

      Aunque es importante leer todo el artículo, debes poner especial atención en la sección “Fórmula para ordenar texto en Excel”. Esa fórmula te ayudará a ordenar los datos alfabéticamente después de haberlos extraído.

  20. Nemesio

    Como puedo extraer solo valores numérico de un dato. Ejemplo: VAAN831230, si quisiera extraer solo los valores numérico como hago esa operación o como determinaría la fecha de la persona a partir del dato de arriba, considerando la fecha de hoy por ejemplo 25/06/14.
    Interesante esta pagina de Excel Total

  21. Danilo Cifuentes

    Una consulta, la lista original a la que hago referencia es producto de una fórmula que obtiene nombres ordenados alfabeticamente, al seleccionar el rango original para extraer los valores únicos la fórmula me desplega “La fórmula contienen texto no reconocido”, ¿que debo hacer para corregirlo? muchas gracias

    1. Moisés Ortíz Autor

      Hola Danilo, generalmente ese mensaje se muestra cuando Excel no reconoce el nombre de alguna función utilizada en la fórmula. La causa del error pueden ser varias cosas: Tal vez escribiste mal el nombre de alguna función, tal vez utilizas un nombre de rango que no existe, tal vez tienes Excel 2010 sin Service Pack y por lo tanto el nombre de las funciones es diferente.