¿Cómo extraer valores únicos de varias columnas en Excel?
Si suele trabajar con conjuntos de datos distribuidos en varias columnas de Excel, es probable que se enfrente a situaciones en las que ciertos valores aparezcan duplicados dentro de una misma columna o entre distintas columnas. En muchas tareas de elaboración de informes o análisis de datos, resulta esencial identificar y extraer todos los valores únicos —es decir, aquellos que aparecen solo una vez en toda la selección, independientemente de su ubicación—. Hacerlo manualmente puede resultar lento y propenso a errores, especialmente al manejar grandes volúmenes de datos o tablas complejas. Afortunadamente, Excel ofrece varios métodos eficaces para extraer estos valores únicos con facilidad.
Esta guía ofrece diversas soluciones adaptadas a su versión de Excel y preferencias: fórmulas compatibles con todas las versiones, fórmulas matriciales dinámicas para versiones recientes, el uso de KUTOOLS AI Aide para obtener resultados inmediatos, Tablas Dinámicas para una consolidación visual clara y código VBA para una extracción automatizada en escenarios complejos.
Extraer valores únicos de varias columnas con fórmulas
A veces, querrá realizar esta extracción utilizando funciones integradas de Excel. Esta sección explica cómo hacerlo mediante dos enfoques: una fórmula matricial compatible con todas las versiones de Excel y una fórmula matricial dinámica disponible en versiones recientes, como Excel 365 y Excel 2021. Estos métodos son ideales cuando busca una solución directa basada en fórmulas, necesita que los resultados se actualicen automáticamente al cambiar los datos o prefiere evitar complementos externos o código.
Extraer valores únicos de varias columnas con fórmula matricial para todas las versiones de Excel
Para garantizar compatibilidad con todas las versiones de Excel, el uso de una fórmula matricial te permite extraer valores únicos de varias columnas, incluso si tu versión de Excel no admite matrices dinámicas. Este enfoque combina las funciones INDIRECTO, TEXTO, MIN, SI, CONTAR.SI, FILA y COLUMNA, lo que lo hace flexible ante distintas estructuras de datos.
Suponga que sus datos se encuentran en el rango A2:C9. Para extraer los valores únicos a partir de la celda E2, siga este sencillo procedimiento:
1.Haga clic en la celda E2(o en la primera celda de su Área de ubicación de la lista) e introduzca la siguiente fórmula matricial:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>«»)*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&«»
- A2:C9 es el rango de datos del que desea extraer valores únicos.
- E1:E1 hace referencia a las celdas inmediatamente encima de su primera celda de salida y es necesario para hacer un seguimiento de las entradas ya generadas.
- $2:$9 son las referencias de fila de sus datos; $A:$C son las referencias de columna. Ajústelas según sea necesario para adaptarlas a la estructura de su hoja de cálculo.
2. Una vez introducida la fórmula, en lugar de pulsar solo Intro, pulse Ctrl + Mayús + Intro simultáneamente para confirmarla como fórmula matricial. Si lo hace correctamente, aparecerán llaves {} alrededor de su fórmula en la Barra de fórmulas. A continuación, arrastre el controlador de relleno desde E2 hacia abajo por la columna hasta que aparezcan celdas en blanco, lo que indica que ya no quedan más valores únicos por extraer. Este proceso garantiza que todos los valores únicos se muestren en la columna de destino.
- $A$2:$C$9Especifica todo el rango de celdas que se analizará para identificar valores únicos.
- IF(($A$2:$C$9<>«»)*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C),7^8):
- $A$2:$C$9<>«»Garantiza que se omitan las celdas en blanco.
- COUNTIF($E$1:E1,$A$2:$C$9)=0Asegura que solo se incluyan los valores nuevos (aún no extraídos).
- Si ambas condiciones se cumplen, la salida correspondiente es un cálculo basado en la fila y la columna de la celda que genera un número de índice único.
- Si alguna de las condiciones es falsa, la fórmula devuelve un número muy grande ()7^8) para evitar selecciones accidentales.
- MIN(...)Identifica el número de índice más bajo, localizando con precisión la posición del siguiente valor único disponible en los datos.
- TEXT(...,"R0C00")Convierte el índice en una referencia de celda válida utilizando el estilo R1C1.
- INDIRECT(...)Convierte la referencia de celda creada anteriormente en un valor de su rango de datos.
- &«»: Obliga a que el resultado de la fórmula se trate como texto, evitando sorpresas relacionadas con el formato.
Extraer valores únicos de varias columnas con fórmula para Excel 365, Excel 2021 y versiones posteriores
Si utiliza Excel 365, Excel 2021 o una versión posterior, dispone de funciones matriciales dinámicas que ofrecen una forma más sencilla e intuitiva de extraer valores únicos de varias columnas. Las funciones UNICO y VERTICAL facilitan y agilizan la combinación de datos entre columnas y la eliminación de duplicados en un solo paso, especialmente útil para quienes trabajan con conjuntos de datos grandes o en constante actualización.
Para utilizar este método, seleccione simplemente una celda vacía (por ejemplo,)E2, o donde desee que aparezcan los resultados), introduzca esta fórmula y pulse Intro:
=UNIQUE(TOCOL(A2:C9,1))Tras pulsar Intro, todos los valores únicos del rango A2:C9 se expandirán automáticamente en las celdas situadas bajo la fórmula. Esta característica es especialmente eficiente: la salida se actualiza dinámicamente cuando sus datos de origen cambian, ahorrándole pasos manuales de actualización.
- TOCOL(A2:C9,1): Transforma tu rango de varias columnas en una única columna, eliminando automáticamente las celdas vacías.
- UNIQUE(...): Extrae cada valor una sola vez, ofreciendo una lista limpia y sin duplicados.
Extraiga valores únicos de varias columnas con KUTOOLS AI Aide
Si prefiere un enfoque más ágil y quiere reducir el esfuerzo manual, KUTOOLS AI Aide en Kutools para Excel le permite extraer valores únicos de varias columnas con facilidad. Este método es especialmente útil si no está familiarizado con fórmulas o desea evitar errores al usarlas. KUTOOLS AI Aide interpreta sus instrucciones y procesa los datos automáticamente, convirtiéndose en la solución ideal tanto para principiantes como para usuarios que buscan resultados rápidos en tan solo unos clics.
Tras la instalación, haga clic en KUTOOLS AI>Asistente de IApara abrir el panel «KUTOOLS AI Aide»:
- Introduzca su solicitud en el cuadro de chat, por ejemplo:«Extrae los valores únicos del rango A2:C9, ignorando las celdas en blanco, y coloca los resultados a partir de E2:»
- Haga clic en "Enviar« o pulse Intro. Tras el análisis de la IA, simplemente haga clic en »Ejecutar" para ejecutarlo. ¡Los resultados aparecerán al instante en su hoja de cálculo, exactamente donde los haya indicado!
Consejo: Esta solución resulta muy útil si su flujo de trabajo de extracción de datos varía o si desea aprovechar funciones de procesamiento del lenguaje natural. Recuerde revisar cuidadosamente la lista Extraer en busca de celdas vacías si sus datos originales no son perfectamente consistentes, ya que las entradas en blanco podrían incluirse o filtrarse según los detalles de su solicitud de inteligencia artificial.
Extraiga valores únicos de varias columnas con Tabla Dinámica
Las tablas dinámicas constituyen otro método práctico para extraer valores únicos, especialmente si prefiere trabajar con herramientas visuales y desea resumir o analizar aún más esos elementos únicos, por ejemplo, contando sus apariciones. Este enfoque es sencillo y no requiere fórmulas; sin embargo, implica algunos pasos de configuración y una ligera reorganización de los datos, sobre todo si las columnas implicadas tienen encabezados distintos.
A continuación se presenta un proceso recomendado para extraer valores únicos mediante una Tabla Dinámica:
1. Inserte una nueva columna en blanco inmediatamente a la izquierda de sus datos. Por ejemplo, si sus datos comienzan en la columna B, inserte una nueva columna A. Este ajuste ayuda a garantizar una consolidación correcta del rango.
2. Seleccione cualquier celda dentro de su conjunto de datos, pulse Alt + D y, a continuación, pulse rápidamente P para abrir el «Asistente para tablas y gráficos dinámicos». En el primer paso del asistente, seleccione «Varios rangos de consolidación». Así podrá combinar valores procedentes de múltiples columnas en un único campo resumido.
3. Haga clic en Siguiente y, a continuación, elija «Crear un único campo de página para mí». Este paso agrupa todos sus datos en un solo conjunto para facilitar la extracción de valores únicos.
4. En el paso siguiente, seleccione todo el rango de datos (incluya la nueva columna en blanco), haga clic en el botón Agregar para añadir su selección a la lista «Todos los rangos» y, a continuación, haga clic en Siguiente.
5. En el último paso del asistente, indique dónde desea colocar la Tabla Dinámica (en una hoja de cálculo nueva o en una hoja existente) y, a continuación, haga clic en Finalizar para generar el informe de Tabla Dinámica.
6. En la nueva Tabla Dinámica, desmarca todas las casillas de la sección «Elija los campos que desea agregar al informe» para eliminar la vista predeterminada.
7. Por último, arrastre el campo «Valor» al área de Filas. La Tabla Dinámica mostrará todos los valores únicos procedentes del rango original de varias columnas, organizados claramente en una única columna.
Limitaciones:Los datos requieren una organización previa y, si su conjunto de datos de origen se actualiza, deberá actualizar la tabla dinámica para ver los nuevos valores únicos.
Extraer valores únicos de varias columnas con código VBA
En casos en los que necesite automatizar la extracción o manejar conjuntos de datos grandes e irregulares, el uso de código VBA (Visual Basic for Applications) puede ofrecer una solución rápida y reutilizable. Este enfoque resulta ideal para usuarios con conocimientos básicos del editor de VBA de Excel o para tareas recurrentes en las que se pretenda minimizar las operaciones manuales. Además, VBA puede gestionar grandes volúmenes de datos con mayor eficiencia que las fórmulas matriciales.
1. Abra el editor de VBA pulsando Alt + F11. En la ventana «Microsoft Visual Basic para Aplicaciones» que aparece, haga clic en Insertar > Módulo para añadir un nuevo módulo.
2.En el nuevo módulo, pegue el código siguiente:
VBA: Extraiga valores únicos de varias columnas
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3. Pulse F5 para ejecutar el código. Aparecerá un cuadro de diálogo que le pedirá que seleccione el rango de datos. Seleccione todas las columnas pertinentes (incluidas aquellas que contengan celdas vacías).
4.Tras hacer clic en Aceptar, aparecerá otro cuadro de diálogo que le preguntará dónde desea mostrar los valores únicos. Indique la celda superior donde quiera que aparezcan los resultados (por ejemplo,)E2).
5. Haga clic en Aceptar y la macro se ejecutará automáticamente. Todos los valores únicos aparecerán a partir de la ubicación que haya especificado.
- Si recibe errores como #¡VALOR! o #¡DESPLAZAMIENTO! al usar fórmulas, compruebe sus rangos y asegúrese de que el área de salida esté libre.
- Compruebe siempre si hay filas ocultas o combinadas en su rango de datos, ya que podrían afectar la precisión de la extracción de valores únicos.
- Las fórmulas matriciales y las fórmulas matriciales dinámicas se actualizan automáticamente ante cualquier cambio, mientras que Filtro Avanzado y las soluciones basadas en Tabla Dinámica pueden necesitar una actualización o ejecución manual.
- Para tareas recurrentes, automatice la extracción con VBA y asegure consistencia y rapidez.
- Haga una copia de seguridad de sus datos antes de aplicar cualquier rutina de extracción masiva o automatización, especialmente en libros de trabajo complejos.
Más artículos relacionados:
- Contar el número de valores únicos y distintos de una lista
- Suponga que tiene una larga lista de valores con algunos elementos duplicados y desea contar cuántos valores únicos (es decir, los que aparecen solo una vez) o el número total de valores distintos hay en una columna, tal como se muestra en la captura de pantalla de la izquierda. Este artículo explica métodos eficaces para contar entradas únicas y valores distintos en Excel.
- Extraer valores únicos según criterios en Excel
- Suponga que desea extraer únicamente los nombres únicos de la columna B según una condición específica en la columna A, obteniendo resultados como los que aparecen en la captura de pantalla. Este tutorial le muestra cómo aplicar criterios al extraer valores únicos.
- Permitir solo valores únicos en Excel
- Si desea permitir únicamente entradas únicas en una columna de la hoja de cálculo y prevenir valores duplicados, este artículo presenta técnicas prácticas para aplicar reglas de unicidad en Excel.
- Sumar valores únicos según criterios en Excel
- Por ejemplo, podría necesitar sumar solo los valores únicos en una columna de «Pedidos» en función de los nombres de una columna adyacente, tal como se muestra en la captura de pantalla. Este artículo analiza enfoques para combinar cálculos únicos y condicionales.
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos