¿Te has encontrado alguna vez frente a una hoja de cálculo de Excel llena de datos repetidos y te has preguntado cómo extraer solo la información que realmente importa? ¡No te preocupes más! En este artículo, te mostraremos cómo obtener una lista de valores únicos en Excel y cómo extraer filas únicas de una manera sencilla y eficiente. Con unos pocos clics y fórmulas, podrás organizar tu información de manera efectiva y mejorar tu productividad. Prepárate para convertirte en un maestro de Excel y descubrir el potencial oculto en tus datos. ¡Comencemos!
Excel es una herramienta poderosa cuando se trata de trabajar con datos, pero a veces puede ser complicado lidiar con información repetida. Si alguna vez te has encontrado en la necesidad de obtener una lista de valores únicos en Excel y extraer filas únicas, ¡has venido al lugar correcto! En este artículo, te mostraremos paso a paso cómo lograrlo de manera rápida y sencilla. Ya sea que estés trabajando con una enorme base de datos o simplemente quieras ahorrar tiempo eliminando duplicados, nuestra guía te ayudará a dominar esta tarea con facilidad. Así que prepara tu hoja de cálculo y vamos a empezar a trabajar en esos valores únicos en Excel.
Esta es la parte final de la serie Valores únicos de Excel que muestra cómo obtener una lista de valores distintos/únicos en una columna usando una fórmula y cómo modificar esa fórmula para diferentes conjuntos de datos. También aprenderá cómo obtener rápidamente una lista distinta utilizando el filtro avanzado de Excel y cómo extraer filas únicas con Duplicate Remover.
En un par de artículos recientes, analizamos diferentes métodos para contar y encontrar valores únicos en Excel. Si tuvo la oportunidad de leer esos tutoriales, ya sabe cómo obtener una lista única o distinta identificando, filtrando y copiando. Pero esa es una forma un poco larga, y ni mucho menos la única, de extraer valores únicos en Excel. Puedes hacerlo mucho más rápido usando una fórmula especial, y en un momento te mostraré esta y un par de técnicas más.
Consejo. Para obtener rápidamente valores únicos en la última versión de Excel 365 que admite matrices dinámicas, use la función ÚNICA.
Cómo obtener valores únicos en Excel
Para evitar confusiones, primero que nada, pongámonos de acuerdo sobre lo que llamamos valores únicos en Excel. Valores únicos son los valores que existen en una lista solo una vez. Por ejemplo:
>Para extraer una lista de valores únicos en Excel, utilice una de las siguientes fórmulas.
Formación fórmula de valores únicos (completada presionando Ctrl + Mayús + Intro):
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1,$A$2:$A$10) + (COUNTIF($A$2:$A$10, $A$2:$A$10)<>1), 0)), «»)
Regular Fórmula de valores únicos (completada presionando Enter):
=IFERROR(INDEX($A$2:$A$10, MATCH(0,INDEX(COUNTIF($B$1:B1, $A$2:$A$10)+(COUNTIF($A$2:$A$10, $A$2:$A$10)<>1),0,0), 0)), «»)
En las fórmulas anteriores, se utilizan las siguientes referencias:
- A2:A10 – la lista de fuentes.
- B1: la celda superior de la lista única menos 1. En este ejemplo, comenzamos la lista única en B2 y, por lo tanto, proporcionamos B1 a la fórmula (B2-1=B1). Si su lista única comienza, digamos, en la celda C3, cambie $B$1:B1 a $C$2:C2.
Nota. Debido a que la fórmula hace referencia a la celda encima de la primera celda de la lista única, que suele ser el encabezado de la columna (B1 en este ejemplo), asegúrese de que su encabezado tenga un nombre único que no aparezca en ningún otro lugar de la columna.
En este ejemplo, extraemos nombres únicos de la columna A (más precisamente del rango A2:A20) y la siguiente captura de pantalla muestra la fórmula matricial en acción:
>La explicación detallada de la lógica de la fórmula se proporciona en un sección separaday aquí se explica cómo usar la fórmula para extraer valores únicos en sus hojas de cálculo de Excel:
- Modifique una de las fórmulas según su conjunto de datos.
- Ingrese la fórmula en la primera celda de la lista única (B2 en este ejemplo).
- Si está utilizando la fórmula matricial, presione Ctrl + Mayús + Intro. Si ha optado por la fórmula normal, presione la tecla Intro como de costumbre.
- Copie la fórmula tanto como sea necesario arrastrando el controlador de relleno. Dado que ambas fórmulas de valores únicos están encapsuladas en la función SIERROR, puede copiar la fórmula hasta el final de su tabla y no saturará sus datos con errores sin importar cuántos valores únicos se hayan extraído.
Cómo obtener valores distintos en Excel (único + 1calle ocurrencias duplicadas)
Como ya habrás adivinado por el título de esta sección, valores distintos en Excel hay todos valores diferentes en una lista, es decir, valores únicos y primeras instancias de valores duplicados. Por ejemplo:
>Para obtener una lista distinta en Excel, utilice las siguientes fórmulas.
Formación fórmula distinta (requiere presionar Ctrl + Mayús + Intro):
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), «»)
Regular fórmula distinta:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), «»)
Dónde:
- A2:A10 es la lista de fuentes.
- B1 es la celda encima de la primera celda de la lista distinta. En este ejemplo, la lista distinta comienza en la celda B2 (es la primera celda donde ingresa la fórmula), por lo que hace referencia a B1.
>Extraiga valores distintos en una columna ignorando las celdas en blanco
Si su lista de origen contiene celdas en blanco, la fórmula distinta que acabamos de analizar devolvería un cero por cada fila vacía, lo que podría ser un problema. Para solucionar este problema, mejore un poco más la fórmula:
Fórmula matricial para extraer valores distintos excluyendo espacios en blanco:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&»») + IF($A$2:$A$10=»»,1,0), 0)), «»)
Obtenga una lista de valores de texto distintos ignorando números y espacios en blanco
De manera similar, puede obtener una lista de valores distintos. excluyendo celdas vacías y celdas con números:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&»») + IF(ISTEXT($A$2:$A$10)=FALSE,1,0), 0)), «»)
Como recordatorio rápido, en las fórmulas anteriores, A2:A10 es la lista de origen y B1 es la celda justo encima de la primera celda de la lista distinta.
La siguiente captura de pantalla muestra el resultado de ambas fórmulas:
>Cómo extraer valores distintos que distinguen entre mayúsculas y minúsculas en Excel
Al trabajar con datos que distinguen entre mayúsculas y minúsculas, como contraseñas, nombres de usuario o nombres de archivos, es posible que necesite obtener una lista de valores distintos que distinguen entre mayúsculas y minúsculas. Para esto, use la siguiente fórmula matricial, donde A2:A10 es la lista de origen y B1 es la celda encima de la primera celda de la lista distinta:
Fórmula matricial para obtener valores distintos que distinguen entre mayúsculas y minúsculas (requiere presionar Ctrl + Mayús + Intro)
=IFERROR(INDEX($A$2:$A$10, MATCH(0, FREQUENCY(IF(EXACT($A$2:$A$10,TRANSPOSE($B$1:B1)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), «»), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0)), «»)
>Cómo funciona la fórmula única/distinta
Esta sección está escrita especialmente para aquellos usuarios de Excel curiosos y reflexivos que no sólo quieren conocer la fórmula sino comprender completamente sus aspectos prácticos.
No hace falta decir que las fórmulas para extraer valores únicos y distintos en Excel no son triviales ni sencillas. Pero si miras más de cerca, podrás notar que todas las fórmulas se basan en el mismo enfoque: usar ÍNDICE/COINCIDIR en combinación con las funciones CONTAR.SI o CONTAR.SI + SI.
Para nuestro análisis en profundidad, usemos la fórmula matricial que extrae un lista de valores distintos porque todas las demás fórmulas analizadas en este tutorial son mejoras o variaciones de esta básica:
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), «»)
>Para empezar, descartemos la función IFERROR obvia, que se usa con el único propósito de eliminar errores #N/A cuando la cantidad de celdas donde copió la fórmula excede la cantidad de valores distintos en la lista de origen.
Y ahora, analicemos la parte central de nuestra fórmula distintiva:
En este ejemplo, CONTAR.SI($B$1:B1, $A$2:$A$10) devuelve una matriz de unos y ceros en función de si alguno de los valores de la lista fuente ($A$2:$A$10) aparece en algún lugar de la lista distinta ($B$1:B1). Si se encuentra el valor, la fórmula devuelve 1; de lo contrario, 0.
En particular, en la celda B2, CONTAR.SI($B$1:B1, $A$2:$A$10) se convierte en:
COUNTIF(«Distinct», {«Ronnie»; «David»; «Sally»; «Jeremy»; «Robert»; «David»; «Robert»; «Tom»; «Sally»})
y devuelve:
{0;0;0;0;0;0;0;0;0}
porque ninguno de los elementos de la lista fuente (criterios) aparece en el rango donde la función busca una coincidencia. En este caso, rango ($B$1:B1) consta de un solo elemento: «Distinto».
En este ejemplo, el valor_buscado es 0 y, en consecuencia:
MATCH(0,COUNTIF($B$1:B1, $A$2:$A$10), 0)
se convierte en:
MATCH(0, {0;0;0;0;0;0;0;0;0},0)
y devuelve 1
porque nuestra función COINCIDIR obtiene el primer valor que es exactamente igual al valor de búsqueda (como recordará, el valor de búsqueda es 0).
En este ejemplo, ÍNDICE($A$2:$A$10, 1)
se convierte en:
INDEX({«Ronnie»; «David»; «Sally»; «Jeremy»; «Robert»; «David»; «Robert»; «Tom»; «Sally»}, 1)
y devuelve «Ronnie».
Cuando la fórmula se copia en la columna, la lista distinta ($B$1:B1) se expande porque la segunda referencia de celda (B1) es una referencia relativa que cambia según la posición relativa de la celda donde se mueve la fórmula.
Entonces, cuando se copia en la celda B3, CONTAR.SI($B$1:B1$A$2:$A$10) cambia a CONTAR.SI($B$1:B2$A$2:$A$10), y se convierte en:
COUNTIF({«Distinct»;»Ronnie»}, {«Ronnie»; «David»; «Sally»; «Jeremy»; «Robert»; «David»; «Robert»; «Tom»; «Sally»}), 0)), «»)
y devuelve:
{1;0;0;0;0;0;0;0;0}
porque un «Ronnie» se encuentra en el rango $B$1:B2.
Y luego, COINCIDIR(0,{1;0;0;0;0;0;0;0;0},0) devuelve 2, porque 2 es la posición relativa del primer 0 en la matriz.
Y finalmente, INDEX($A$2:$A$10, 2) devuelve el valor del 2Dakota del Norte fila, que es «David».
Consejo. Para comprender mejor la lógica de la fórmula, puede seleccionar diferentes partes de la fórmula en la barra de fórmulas y presionar F9 para ver cuál es la evaluación de una parte seleccionada:
>
Si aún tiene dificultades para descifrar la fórmula, puede consultar el siguiente tutorial para obtener una explicación detallada de cómo funciona el enlace ÍNDICE/COINCIDIR: ÍNDICE Y COINCIDIR como una mejor alternativa a BUSCARV de Excel.
Como ya se mencionó, las otras fórmulas analizadas en este tutorial se basan en la misma lógica, con sólo algunas modificaciones:
Fórmula de valores únicos – contiene una función más CONTAR.SI que excluye de la lista única todos los elementos que aparecen en la lista fuente más de una vez: COUNTIF($A$2:$A$10, $A$2:$A$10)<>1.
Fórmula de valores distintos que ignora los espacios en blanco – aquí agrega una función SI que evita que se agreguen celdas en blanco a la lista distinta: IF($A$2:$A$13=»»,1,0).
Fórmula de valores de texto distintos ignorando los números – utiliza la función ISTEXT para comprobar si un valor es texto y la función IF para descartar todos los demás tipos de valores, incluidas las celdas en blanco: IF(ISTEXT($A$2:$A$13)=FALSE,1,0).
Extraiga valores distintos de una columna con el filtro avanzado de Excel
Si no quieres perder el tiempo descubriendo los giros arcanos del fórmulas de valores distintos, puede obtener rápidamente una lista de valores distintos utilizando el filtro avanzado. Los pasos detallados siguen a continuación.
>
- Controlar Copiar a otra ubicación boton de radio.
- En el rango de lista verifique que el rango de origen se muestre correctamente.
- En el Copiar al cuadro, ingrese la celda superior del rango de destino. Tenga en cuenta que puede copiar los datos filtrados sólo al hoja activa.
- Selecciona el Sólo registros únicos
>
>
Preste atención a que, aunque la opción del Filtro avanzado se llame «Sólo registros únicos«, extrae valores distintoses decir, valores únicos y 1calle apariciones de valores duplicados.
Extraiga filas únicas y distintas con Duplicate Remover
En la parte final de este tutorial, permítame mostrarle nuestra propia solución para encontrar y extraer valores distintos y únicos en hojas de Excel. Esta solución combina la versatilidad de las fórmulas de Excel y la simplicidad del filtro avanzado. Además, proporciona un par de características únicas como:
- Buscar y extraer filas únicas/distintas basado en valores en una o más columnas.
- Encontrar, destacary Copiar valores únicos a cualquier otra ubicación, en el mismo libro o en uno diferente.
Y ahora, veamos la herramienta Eliminador de duplicados en acción.
Supongamos que tiene una tabla de resumen creada mediante la consolidación de datos de varias otras tablas. Obviamente, esa tabla de resumen contiene muchas filas duplicadas y su tarea es extraer filas únicas que aparecen en la tabla solo una vez, o filas distintas, incluidas las únicas y 1.calle ocurrencias duplicadas. De cualquier manera, con el complemento Duplicate Remover el trabajo se realiza en 5 rápidos pasos.
> Se ejecutará el asistente de eliminación de duplicados y seleccionará toda la tabla. Entonces, simplemente haga clic Próximo para proceder al siguiente paso.
>- Único
- Único +1calle ocurrencias (distintas)
En este ejemplo, nuestro objetivo es extraer filas únicas que aparecen en la tabla fuente sólo una vez, por lo que seleccionamos el Único opción:
>
Consejo. Como puede ver en la captura de pantalla anterior, también hay 2 opciones para valores duplicadosténgalo en cuenta si necesita deduplicar alguna otra hoja de trabajo.
En este ejemplo, queremos encontrar filas únicas basadas en valores en las 3 columnas (Número de orden, Nombre de pila y Apellido), por lo tanto seleccionamos todos.
>
- Resalte valores únicos
- Seleccionar valores únicos
- Identificar en una columna de estado
- Copiar a otra ubicación
Debido a que estamos extrayendo filas únicas, seleccione Copiar a otra ubicacióny luego especifique dónde desea copiarlos exactamente: hoja activa (seleccione la Ubicación personalizada y especifique la celda superior del rango de destino), nueva hoja de trabajo o nuevo libro de trabajo.
En este ejemplo, optemos por la nueva hoja:
>
>
¿Le gustó esta forma rápida y sencilla de obtener una lista de valores o filas únicos en Excel? Si es así, te animo a que descargues una versión de evaluación a continuación y la pruebes. Duplicate Remover, así como todas las demás herramientas que tenemos para ahorrar tiempo, se incluyen en Ultimate Suite for Excel.
descargas disponibles
Encuentre valores únicos en Excel: libro de trabajo de muestra (archivo .xlsx)
Ultimate Suite – versión de evaluación (archivo .exe)
¡Genial artículo! Siempre he tenido problemas para encontrar esos valores únicos en mis hojas de Excel, pero después de leer esto probé el truco y me salvó la vida en un proyecto. ¡Gracias por compartirlo! – pwyllU.
Zaballa: ¡Me encanta este artículo! La verdad es que antes siempre tenía que hacer mil maniobras para filtrar datos, y era un verdadero dolor de cabeza. La última vez que usé una de estas fórmulas, no solo me ahorré tiempo, sino que también logré presentar los resultados mucho más claros. ¡Es un cambio total! Gracias por compartir este tipo de info.
Jiahao: ¡Me encantó el artículo! Siempre batallaba para sacar esos valores únicos en Excel, pero desde que vi estos tips, todo ha sido pan comido. La última vez que lo usé, pude organizar un montón de datos en un santiamén, ¡una locura! Gracias por compartir estos trucos tan útiles.
Bryan fabricio: ¡Totalmente de acuerdo, pwyllU! Este tipo de trucos son oro puro. La última vez que necesité sacar valores únicos, me volví loco tratando de hacerlo manualmente. Gracias a este artículo, aprendí una fórmula que me facilitó muchísimo el trabajo. ¡Impresionante!
Gst5ramdy: ¡Qué gran artículo, de verdad! Yo solía hacer malabares con mis hojas de cálculo para encontrar valores únicos y terminaba frustrado. Pero desde que aprendí estos trucos, todo ha cambiado. La última vez que lo usé, me ayudó a simplificar un informe súper complicado y me hizo ganar tiempo. ¡Mil gracias por compartir!