Note: The other languages of the website are Google-translated. Back to English

¿Cómo extraer dinámicamente una lista de valores únicos de un rango de columnas en Excel?

Para un rango de columna cuyos valores cambian regularmente, y siempre necesita obtener todos los valores únicos del rango sin importar cómo cambió. ¿Cómo hacer una lista dinámica de valores únicos? Este artículo le mostrará cómo manejarlo.

Extraiga dinámicamente una lista de valores únicos de un rango de columna con fórmula
Extraiga dinámicamente una lista de valores únicos de un rango de columnas con código VBA


Extraiga dinámicamente una lista de valores únicos de un rango de columna con fórmula

Como se muestra a continuación, debe extraer dinámicamente una lista de valores únicos del rango B2: B9. Pruebe la siguiente fórmula de matriz.

1. Seleccione una celda en blanco como D2, ingrese la fórmula a continuación y presione el Ctrl + cambio + Enviar teclas simultáneamente. (B2: B9 son los datos de la columna de los que desea extraer los valores únicos, D1 es la celda anterior donde se encuentra su fórmula)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2. Siga seleccionando la celda D2, luego arrastre el controlador de relleno hacia abajo para obtener todos los valores únicos del rango especificado.

Ahora se extraen todos los valores únicos en el rango de columnas B2: B9. Cuando los valores en este rango cambian, la lista de valores únicos cambiará dinámicamente inmediatamente.

Seleccione y resalte fácilmente todos los valores únicos en un rango en Excel:

Programas de Seleccione celdas duplicadas y únicas utilidad de Kutools for Excel puede ayudarlo a seleccionar y resaltar fácilmente todos los valores únicos (incluidos los primeros duplicados) o los valores únicos que aparecen solo una vez, así como los valores duplicados que necesite, como se muestra a continuación.
¡Descarga Kutools para Excel ahora! (30-día de ruta libre)


Extraiga dinámicamente una lista de valores únicos de un rango de columnas con código VBA

También puede extraer una lista de valores únicos dinámicamente de un rango de columna con el siguiente código VBA.

1. Prensa otro + F11 teclas simultáneamente para abrir el Microsoft Visual Basic para aplicaciones ventana.

2. En el Microsoft Visual Basic para aplicaciones ventana, haga clic recuadro > Módulo. Luego copie y pegue el siguiente código VBA en el Módulo ventana.

Código de VBA: extraiga una lista de valores únicos de un rango

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

Nota:: En el código, D2 es la celda en la que ubicará la lista de valores únicos. Puede cambiarlo cuando lo necesite.

3. Regrese a la hoja de trabajo, haga clic en recuadro > formas > rectángulo. Ver captura de pantalla:

4. Dibuje un rectángulo en su hoja de trabajo, luego ingrese algunas palabras que necesita mostrar en él. Luego haga clic derecho y seleccione Asignar macro desde el menú contextual. En el Asignar macro cuadro de diálogo, seleccione el CreateUniqueList en el capítulo respecto a la Nombre de la macro cuadro, y luego haga clic en el OK botón. Ver captura de pantalla:

5. Ahora haga clic en el botón del rectángulo, un Kutools for Excel aparece el cuadro de diálogo, seleccione el rango que contiene valores únicos que necesita extraer, y luego haga clic en el OK

A partir de ahora, puede repetir el paso 5 anterior para actualizar la lista de valores únicos automáticamente.


Artículos relacionados:


Las mejores herramientas de productividad de oficina

Kutools para Excel resuelve la mayoría de sus problemas y aumenta su productividad en un 80%

  • Reutilizar: Inserte rápidamente fórmulas complejas, gráficos y cualquier cosa que hayas usado antes; Cifrar celdas con contraseña; Crear lista de distribución y enviar correos electrónicos ...
  • Barra de súper fórmula (edite fácilmente varias líneas de texto y fórmulas); Diseño de lectura (leer y editar fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas sin perder datos; Contenido de celdas divididas; Combinar filas / columnas duplicadas... Prevenir celdas duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Varias celdas sin cambiar la referencia de la fórmula; Crear referencias automáticamente a varias hojas; Insertar viñetas, Casillas de verificación y más ...
  • Extraer texto, Agregar texto, Eliminar por posición, Quitar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtros a otras hojas); Orden avanzado por mes / semana / día, frecuencia y más; Filtro especial en negrita, cursiva ...
  • Combinar libros y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes de xls, xlsx y PDF...
  • Más de 300 potentes funciones. Compatible con Office/Excel 2007-2021 y 365. Compatible con todos los idiomas. Fácil implementación en su empresa u organización. Funciones completas Prueba gratuita de 30 días. Garantía de devolución de dinero de 60 días.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilite 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 de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
officetab parte inferior
Comentarios (35)
Aún no hay calificaciones. ¡Sé el primero en calificar!
Este comentario fue minimizado por el moderador en el sitio
Gracias por el tutorial. Usando el método de la fórmula, ¿cómo modificaría la fórmula si quisiera agregar un calificador de categoría? Diga en la columna C que distingue si el artículo es una fruta o una verdura. ¿Cómo cambiaría el código para clasificar solo las frutas únicas y excluir las verduras? Intenté reemplazar COUNTIF con COUNTIFS, usando el segundo criterio de recuento de (RANGO DE LISTA, "CATEGORÍA") pero vuelve en blanco. ¿Necesitaría expandir mi matriz e incorporar BUSCARV?
Este comentario fue minimizado por el moderador en el sitio
Soy decente en Excel, pero realmente estoy tratando de entender cómo y por qué funciona la fórmula anterior (funciona para lo que la estoy usando, pero debo entender por qué). A veces me confundo un poco al usar matrices, por lo que cualquier explicación en términos idiotas sería extremadamente útil. Saludos
Este comentario fue minimizado por el moderador en el sitio
Esta fórmula está desactualizada y no funciona. Literalmente acabo de configurar esta hoja de Excel exacta para ver si puedo hacer que esta fórmula funcione y no lo hace.
Este comentario fue minimizado por el moderador en el sitio
Hey! Chico,
¿Qué versión de Office usas?
Este comentario fue minimizado por el moderador en el sitio
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - encontré que esto funciona desde otro sitio...

Use Ctrl+Shift+Enter para obtener la función de matriz (llaves). Arrastre copiar y pegar las fórmulas hasta que se muestre #NA. Mi conjunto de datos estaba en la Columna-Q, se comparó para ver si existía en la lista de únicos en la Columna-V, que se extiende continuamente a lo largo de esta misma columna.
Este comentario fue minimizado por el moderador en el sitio
Buen día.
Enumere todos los valores únicos de la columna Q con la fórmula anterior y luego use su fórmula = IF (D2 = V1, "Coincidencia", "Sin coincidencia") para comparar si los valores únicos en la columna Q se comparan con la columna V en la misma fila .
Este comentario fue minimizado por el moderador en el sitio
Hola, y gracias por tu ayuda.

Necesito exactamente esta funcionalidad, pero mi lista de "valores únicos" debe extenderse a lo largo de las columnas en lugar de las filas, por lo que la lista desplegable en las filas no funcionará para mí.

¿Cómo puedo modificar esta fórmula para hacer que la lista de "valores únicos" se expanda a medida que la arrastro por las columnas?

Compensar()?
Transponer()?
¿Indirecto () con una cadena de referencias absolutas concatenadas con una referencia a la columna en lugar de a la fila?


Gracias de nuevo!
Este comentario fue minimizado por el moderador en el sitio
Querido Ryan,
Esta fórmula =SI.ERROR(ÍNDICE($B$2:$B$9, COINCIDIR(0,CONTAR.SI($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter puede ayudarte a resolver el problema.
Vea la siguiente captura de pantalla:
Este comentario fue minimizado por el moderador en el sitio
Además, por alguna razón, la fórmula original proporcionó:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

devuelve una advertencia de "referencia circular" y no calculará.
Este comentario fue minimizado por el moderador en el sitio
Querido Ryan,
¿Qué versión de Office usas? La fórmula funciona bien en mi Office 2016 y 2013.
Este comentario fue minimizado por el moderador en el sitio
Ya me había pasado esto antes: mi solución fue que estaba ingresando la fórmula en la celda D1 (equivalente en la hoja de trabajo que estaba usando). Independientemente de la celda a la que corresponda $D:$1, debe ingresarla en la celda a continuación: D2. Disculpas si no es por eso que tienes el error
Este comentario fue minimizado por el moderador en el sitio
¿Algún consejo sobre cómo hacer que la opción VBA funcione con Excel 2016 para macOS? He seguido los pasos; sin embargo, cuando ejecuto la macro, no sucede nada en absoluto. ¡Gracias!
Este comentario fue minimizado por el moderador en el sitio
daer jones,
Pruebe el siguiente código de VBA y avíseme si funciona para usted. ¡Gracias!

Sub CreateUniqueList()
Dim xRng como rango
Dim xLastRow tan largo
Dim xLastRow2 tan largo
Dim I como entero
' En caso de error Reanudar siguiente
Establezca xRng = Application.InputBox ("Seleccione el rango:", "Kutools para Excel", Selection.Address, , , , , 8)
Si xRng no es nada, salga de Sub
On Error Resume Next
xRng. Copiar rango ("D2")
xÚltimaFila = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columnas:=1, Encabezado:=xlNo
xÚltimaFila2 = Celdas(Filas.Cuenta, "B").Fin(xlArriba).Fila
Para I = 1 Hasta xÚltimaFila2
Si ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Entonces
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
Si terminar
Siguiente
End Sub
Este comentario fue minimizado por el moderador en el sitio
Hola Crystal,
Estoy tratando de usar la versión VB de la lista de valores únicos y me encuentro con un problema.
El rango del que quiero crear una columna de valores únicos son todas las fórmulas que se refieren a diferentes pestañas.
¿Cómo se obtiene el valor para transferir en lugar de la fórmula?
Este comentario fue minimizado por el moderador en el sitio
Querido Mike,
Convierta sus referencias de fórmula a absolutas y luego aplique el script VB.
Este comentario fue minimizado por el moderador en el sitio
Tengo el mismo problema, excepto que mi fórmula se refiere a los nombres de las columnas y no se puede convertir en absoluta.
¿Cómo cambio el vba para pegar los valores y no la fórmula?
Este comentario fue minimizado por el moderador en el sitio
¿Cómo agregaría múltiples criterios, como si solo quisiera agregar a la lista dinámica si la fecha fuera solo el 9 de septiembre?

Estoy probando "&" en la fórmula MATCH, pero no funciona.

Por ejemplo, en base a su ejemplo:
=SI.ERROR(ÍNDICE($B$2:$B$9, COINCIDIR(0 & B4,CONTAR.SI($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
Esto arroja un error o crea duplicados.

Alternativamente, he leído que "+" podría funcionar, aunque no puedo hacerlo funcionar. O usando PEQUEÑO.

Ideas?
Este comentario fue minimizado por el moderador en el sitio
Estimado Zac,
Lo siento, no puedo ayudar con esto, puedes publicar tu pregunta en nuestro foro: https://www.extendoffice.com/forum.html para obtener más soporte de Excel de nuestro profesional.
Este comentario fue minimizado por el moderador en el sitio
¿Cómo agregarías una segunda variable? Por ejemplo, quiero todos los elementos únicos en una columna que también compartan un valor similar en otra columna. En su ejemplo, imagine una tercera columna titulada "Departamento" que tendría valores como producto, carne, etc. Me doy cuenta de que todos son Productos, pero espero que entienda mi punto. ¿Modificaría la fórmula CountIF a COUNTIFS o la modificaría de otra manera?
Este comentario fue minimizado por el moderador en el sitio
Hola Matt
Pruebe esta fórmula = IF (ISNA (BUSCARV (A2, $ C $ 2: $ C $ 13,1, FALSO)), "Sí", "").
Suponiendo que las dos listas comparadas son la Columna A y la Columna C, si los valores únicos permanecen solo en la Columna A pero no en la Columna C, se mostrará Sí en la columna B; mientras que si no devuelve nada en la Columna B, significa que el valor correspondiente permanece tanto en la Columna A como en la Columna C.
Este comentario fue minimizado por el moderador en el sitio
Gracias por la respuesta... pero estoy ansioso por extraer ese valor único si muestra SÍ... ¿podría aconsejarme la fórmula para extraer el valor único en una columna diferente?
Este comentario fue minimizado por el moderador en el sitio
Si hago esto para una hoja de Excel de mil filas en la última versión de Excel en una Mac, nunca regresa. La primera fila funciona, pero cuando duplico, Excel entra en un modo de cálculo que no ha devuelto valores durante más de dos horas.

¿Alguna idea sobre cómo hacer esto para listas grandes (hasta 2k filas) que devolverán 50 o 60 valores únicos?

Me burlé de esto en la aplicación "Números", y funciona perfectamente allí, tomando solo un par de minutos para calcular. Solo está tardando tanto en Excel que me pregunto si alguna vez se completará. Planeo dejarlo "funcionar" durante la noche para ver qué sucede.
Este comentario fue minimizado por el moderador en el sitio
Verifique sus opciones de Cálculo. Debe configurarse en automático. Archivo > Opciones > Fórmulas > Opciones de cálculo > Cálculo del libro de trabajo (selección automática)
Este comentario fue minimizado por el moderador en el sitio
Estoy tratando de arrastrar la fórmula más allá de mis datos reales para poder ingresar conjuntos de datos de diferentes tamaños y no tener que ajustar nada. Sin embargo, la última fila después de que finalicen mis datos reales siempre devuelve un "0". Estoy usando los valores únicos para otra cosa en una columna adyacente, y el 0 hace que se repita el último valor (cuando elimino el 0, el valor ya no se repite). ¿Algúna idea de cómo arreglar esto? También estoy usando Office 365 Business
Este comentario fue minimizado por el moderador en el sitio
Hola, gracias por tu ayuda.
Ahora, ¿cómo puedo tener mis valores también ordenados alfabéticamente? (No quiero usar el filtro en mi tabla maestra)
¿Debo usar COUNTIFS en lugar de COUNTIF?
Por favor ayuda
Este comentario fue minimizado por el moderador en el sitio
Hola alexis
Lo siento, no puedo ayudar a ordenar el valor extraído alfabéticamente al mismo tiempo que la fórmula. Gracias por tu comentario.
Este comentario fue minimizado por el moderador en el sitio
Estoy usando esa fórmula =SI.ERROR(ÍNDICE($B$2:$B$9, COINCIDIR(0,CONTAR.SI($D$1:D1, $B$2:$B$9), 0)),"") que es genial para una columna, pero mis datos se distribuyen en un rango de columnas y filas. ¿Puedo editar la fórmula para incluir toda el área? Mis datos viven desde AC4 hasta AR60...
Este comentario fue minimizado por el moderador en el sitio
Intento el Código VBA y la fórmula. El código VBA funciona muy bien, pero no puedo mantener un archivo con macro. Pero el problema es que no puedo hacer que la fórmula funcione. ¿Alguien tuvo una idea? Gracias
Este comentario fue minimizado por el moderador en el sitio
Hola charlotte
Gracias por tu comentario. Puede conservar el archivo con macro para uso futuro guardando el libro de trabajo como un libro de trabajo habilitado para macros de Excel.
Para el problema de la fórmula, ¿podría proporcionar una captura de pantalla de sus datos? Gracias por tu comentario.
Este comentario fue minimizado por el moderador en el sitio
muchas gracias
Este comentario fue minimizado por el moderador en el sitio
¿Cómo hacer que el código vba funcione para un rango donde se usó otra fórmula? En la columna BI, tenga una fórmula, que se refiera a las columnas D y E.
Si uso aplicar el código a la columna L (digamos), (obviamente, modificando correctamente las celdas en el código) la macro devuelve la fórmula aplicada a las columnas M y N... ¡Funciona, entonces, pero no como yo quiero! ¿Cómo mantener los valores en la columna B? gracias
Este comentario fue minimizado por el moderador en el sitio
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij grote conjuntos de datos. Een goed alternatief es het gebruik van een draaitabel. Kies dan alleen de rijlabels, je krijgt dan een lijstje met unieke waarden. Het kan zijn dat je wat extra's krijgt "(leeg)" bijvoorbeeld. Je kunt deze er vervolgens uitfilteren. Helaas kun je maar op 1 filtro de criterio. Ook daar zijn wel weer oplossingen voor, maar dat is wat complexer.
Este comentario fue minimizado por el moderador en el sitio
Me gustaría poder hacer exactamente lo mismo, excepto que usar dos rangos de columna separados (B2: B9) y (D2: D9) ¿es esto posible?
Este comentario fue minimizado por el moderador en el sitio
Hola Anthony,
Puede colocar los resultados en la misma columna que los datos originales. Como la columna B en este caso.
Pero debe hacer referencia a la celda superior de la celda de resultado en la fórmula de la siguiente manera.
=SI.ERROR(ÍNDICE($B$2:$B$9, COINCIDIR(0,CONTAR.SI($B$11:B11, $B$2:$B$9), 0)),"") + Ctrl + Mayús + Intro
Este comentario fue minimizado por el moderador en el sitio
con este procedimiento de filtro se hace de forma muy rapida

1.EN ESTE EJEMPLO los datos a remover los duplicados estan en la col A de la fila 59 a la 239
2. se define un criterio de filtrado en este caso en la fila d56 el mismo titulo de la lista a remover duplicados y la d57 la dejo en blanco
3. una vez ejecutada se muestran los datos en la fila destino, que en mi caso fue la d59

Range("A59:A239").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Rango _
("D56:D57"), CopyToRange:=Rango("D59"), Único:=Verdadero
No hay comentarios publicados aquí todavía
Deje sus comentarios
Publicar como invitado
×
Califica esta publicación:
0   Personajes
Ubicaciones sugeridas

Siganos

Copyright © 2009 - www.extendoffice.com. | Reservados todos los derechos. Energizado por ExtendOffice, | Mapa del Sitio
Microsoft y el logotipo de Office son marcas comerciales o marcas comerciales registradas de Microsoft Corporation en los Estados Unidos y / o en otros países.
Protegido por Sectigo SSL