Autor: Oscar Cronquist Artículo actualizado en Agosto 11, 2019

Pregunta:

¿Cómo creo una lista desplegable con valores ordenados alfabéticamente distintos y únicos?

Tabla de contenido

Ordenar los valores utilizando una fórmula matricial

La columna auxiliar D filtra valores distintos únicos ordenados de A a Z de los valores de la columna B. La lista desplegable en la celda F3 se rellena con los valores de la columna D.

Fórmula de matriz en la celda D3

=INDEX($B$3:$B$11, MATCH(SMALL(IF(COUNTIF($D$2:D2,$B$3:$B$11)=0,COUNTIF($B$3:$B$11, "<"&$B$3:$B$11),""), 1), COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0))

Mira un video explicando la fórmula.

Vea un video que explica cómo rellenar la lista desplegable con un rango dinámico denominado

Artículo recomendado:

Ordenar una columna alfabéticamente

Tabla de contenido Ordenar una columna usando la fórmula de matriz Dos columnas ordenadas por la segunda columna Ordenar valores alfanuméricos I […]

Ordenar una columna alfabéticamente

Cree listas desplegables dependientes que contengan valores distintos únicos

Este artículo explica cómo construir listas desplegables dependientes. Aquí hay una lista de números de pedido y productos. Nosotros […]

Cree listas desplegables dependientes que contengan valores distintos únicos

Cómo crear una fórmula matricial

  1. Seleccionar celda D3
  2. Escriba encima de la fórmula matricial
  3. Mantener presionado Ctrl + Shift
  4. Presione Enter una vez
  5. Libera todas las llaves

Artículo recomendado:

Cómo ingresar una fórmula matricial

Las fórmulas de matriz le permiten hacer cálculos avanzados que no son posibles con fórmulas regulares.

Cómo ingresar una fórmula matricial

Cómo copiar fórmula matricial

  1. Seleccionar celda D3
  2. Copiar celda (Ctrl + c)
  3. Seleccionar rango de celdas D4: D7
  4. Pegar (Ctrl + v)

Explicando la fórmula matricial en la celda D3.

Paso 1 - Filtra valores distintos únicos

COUNTIF($D$2:D2,$B$3:$B$11)=0

La función COUNTIF cuenta los valores anteriores anteriores para asegurarse de que no se muestren duplicados.

Si no se encuentran valores en las celdas anteriores anteriores, la función COUNTIF devuelve un 0 (cero) para esa posición en la matriz.

COUNTIF($D$2:D2,$B$3:$B$11) returns {0;0;0;0;0;0;0;0;0}

COUNTIF ($ D $ 2: D2, $ B $ 3: $ B $ 11) = 0 devuelve {TRUE; TRUE; VERDAD VERDAD; VERDAD VERDAD; VERDAD VERDAD VERDAD}

La siguiente imagen muestra la matriz en el rango de celdas C3: C11. La matriz le dice a la fórmula que aún no se han mostrado valores en las celdas sobre la celda D3

Tenga en cuenta que el primer argumento en la función COUNTIF ($ D $ 2: D2) es una referencia de celda creciente que significa que cuando copia la fórmula a las celdas debajo de la referencia de celda se expande automáticamente.

Cómo usar la función COUNTIF

Cuenta el número de celdas que cumplen una condición específica.

Cómo usar la función COUNTIF

Paso 2 - Crea una matriz con números de clasificación

La función COUNTIF vuelve a rescatar, le permite clasificar cada valor en el rango de celdas B3: B11 en función de su posición en una matriz ordenada.

COUNTIF ($ B $ 3: $ B $ 11, "<" & $ B $ 3: $ B $ 11)

El valor "BB" es el primero en una lista ordenada indicada por el valor correspondiente 0 (cero) en la columna C. El segundo valor es "DD" con el número de rango 1.

COUNTIF($B$3:$B$11, "<"&$B$3:$B$11) returns {1;3;5;3;7;0;5;7;1}

Paso 3: crea una matriz con números de rango en función de los valores que aún no se han mostrado

IF(COUNTIF($D$2:D2,$B$3:$B$11)=0,COUNTIF($B$3:$B$11, "<"&$B$3:$B$11),"")

se convierte en

IF({TRUE;TRUE; TRUE;TRUE; TRUE;TRUE; TRUE;TRUE;TRUE},{1;3;5;3;7;0;5;7;1},"")

and returns {1;3;5;3;7;0;5;7;1}

No se han filtrado valores en la matriz, esto tiene sentido ya que la fórmula de la matriz está en la celda D3 y aún no se han mostrado valores.

Cómo utilizar la función IF

Comprueba si se cumple una expresión lógica. Devuelve un valor específico si es VERDADERO y otro valor específico si es FALSO.

Cómo utilizar la función IF

Paso 4 - Encuentra el valor más pequeño en la matriz

La función PEQUEÑA le permite filtrar el enésimo valor más pequeño de una matriz de valores. Tiene una gran ventaja para la función MIN, ya que también ignora el texto y los valores en blanco.

SMALL(IF(COUNTIF($D$2:D2,$B$3:$B$11)=0,COUNTIF($B$3:$B$11, "<"&$B$3:$B$11),""), 1)

se convierte en

SMALL({1;3;5;3;7;0;5;7;1}, 1)

y devuelve 0 (cero).

Cómo usar la función PEQUEÑA

La función PEQUEÑA le permite extraer un número en un rango de celdas en función de cuán pequeño es en comparación con los otros números del grupo.

Cómo usar la función PEQUEÑA

Paso 4 - Encuentra la posición de un valor en una matriz de valores

Para poder obtener el valor correcto para la celda D3, necesitamos saber su posición en la columna B. La función MATCH devuelve la posición.

MATCH(SMALL(IF(COUNTIF($D$2:D2,$B$3:$B$11)=0,COUNTIF($B$3:$B$11, "<"&$B$3:$B$11),""), 1), COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0)

se convierte en

PARTIDO (0, COUNTIF ($ B $ 3: $ B $ 11, "<" y $ B $ 3: $ B $ 11), 0)

se convierte en

MATCH(0, {1;3;5;3;7;0;5;7;1}, 0)

y devuelve 6. El valor 0 (cero) está en la posición 6 en esta matriz {1; 3; 5; 3; 7; 0; 5; 7; 1}

Cómo usar la función MATCH

Identificar la posición de un valor en una matriz.

Cómo usar la función MATCH

Paso 5 - Fetch valor

La función ÍNDICE recupera un valor basado en una coordenada, ya que es solo una columna, la función ÍNDICE le permite usar solo un número de fila como coordenada.

INDEX($B$3:$B$11, MATCH(SMALL(IF(COUNTIF($D$2:D2,$B$3:$B$11)=0,COUNTIF($B$3:$B$11, "<"&$B$3:$B$11),""), 1), COUNTIF($B$3:$B$11, "<"&$B$3:$B$11), 0))

se convierte en

ÍNDICE ($ B $ 3: $ B $ 11, 6)

se convierte en

ÍNDICE ({"DD"; "EE"; "FF"; "EE"; "GG"; "BB"; "FF"; "GG"; "DD"}, 6)

y devuelve el valor "BB" en la celda D3.

Cómo usar la función ÍNDICE

Obtiene un valor en un rango de celdas específico basado en un número de fila y columna.

Cómo usar la función ÍNDICE

Cómo crear una lista desplegable que se expande con nuevos valores automáticamente

  1. Haga clic en la pestaña de datos
  2. Haga clic en el botón de validación de datos
  3. Haga clic en "Validación de datos ..."
  4. Seleccione Lista en la ventana "Permitir:". Vea la imagen de abajo.
  5. Tipo = OFFSET ($ B $ 2, 0, 0, COUNT (IF ($ B $ 2: $ B $ 1000 = "", "", 1)), 1) en la ventana "Fuente:"
  6. ¡Haga clic en Aceptar!

crear-una-lista-desplegable-que contiene solo-único-3

Artículos recomendados:

Cree listas desplegables dependientes que contengan valores distintos únicos

Este artículo explica cómo construir listas desplegables dependientes. Aquí hay una lista de números de pedido y productos. Nosotros […]

Cree listas desplegables dependientes que contengan valores distintos únicos

Nota final

Convierta los datos en la columna B en una tabla definida de Excel para que sea dinámica. Entonces no necesita ajustar las referencias de celda en la fórmula de matriz en la columna D cada vez que agrega o elimina valores en la columna B.

Descargar el archivo * .xlsx

Cree una lista desplegable que contenga únicamente unique.xlsx

Artículos recomendados:

Cree listas desplegables dependientes que contengan valores distintos únicos en varias filas

Sharmila pregunta: ¿Cómo puedo usar esta lista para varias filas? Me gustaría usar estas listas para múltiples […]

Cree listas desplegables dependientes que contengan valores distintos únicos en varias filas

Plantilla de factura con listas desplegables dependientes

Este artículo muestra una plantilla de factura básica que creé. Le permite utilizar listas desplegables para seleccionar productos rápidamente en [...]

Plantilla de factura con listas desplegables dependientes

Listas desplegables dependientes - Habilitar / Deshabilitar filtro de selección

Josh pregunta: Tengo esto funcionando ahora con 6 menús desplegables / listas. Quería ver si posiblemente sabes [...]

Listas desplegables dependientes - Habilitar / Deshabilitar filtro de selección

Listas desplegables dependientes en varias filas

Este artículo muestra cómo configurar listas desplegables dependientes en varias celdas. Las listas desplegables se completan en función de [...]

Listas desplegables dependientes en varias filas

Funciones en este artículo:

SI(prueba_lógica, [value_if_true], [value_if_false])
Comprueba si se cumple una condición y devuelve un valor si es VERDADERO y otro valor si es FALSO

ÍNDICE(array, row_num, [column_num])
Devuelve un valor o referencia de la celda en la intersección de una fila y columna en particular, en un rango dado

PEQUEÑA(matriz, k) devuelve el k-ésimo número de fila más pequeño en este conjunto de datos.

FILA(referencia) devuelve el número de fila de una referencia

PARTIDO(lookup_value, lookup_array, [match_type])
Devuelve la posición relativa de un elemento en una matriz que coincide con un valor especificado

COUNTIFrango, criterios)
Cuenta el número de celdas dentro de un rango que cumple con la condición dada

CONTAR(value1, [value2])
Cuenta el número de celdas en un rango que contiene números
COMPENSAR(referencia, filas, columnas, [altura], [ancho])
Devuelve una referencia a un rango que es un número dado de filas y columnas de una referencia dada

Ordenar los valores usando vba

Fórmula de matriz en la celda B2: B8000:

=FilterUniqueSort($A$2:$A$8212)

Cómo crear una fórmula matricial

  1. Seleccionar rango de celdas B2: B8000
  2. Escriba la fórmula de matriz anterior
  3. Mantener presionado Ctrl + Shift
  4. Presione Enter una vez
  5. Libera todas las llaves

Código VBA
Puede encontrar la función selectionsort aquí: Usando una macro de Visual Basic para ordenar arrays en Microsoft Excel

Function FilterUniqueSort(rng As Range)

Dim ucoll As New Collection, Value As Variant, temp() As Variant
ReDim temp(0)

On Error Resume Next
For Each Value In rng
    If Len(Value) &amp;amp;amp;amp;amp;gt; 0 Then ucoll.Add Value, CStr(Value)
Next Value
On Error GoTo 0

For Each Value In ucoll
    temp(UBound(temp)) = Value
    ReDim Preserve temp(UBound(temp) + 1)
Next Value

ReDim Preserve temp(UBound(temp) - 1)
SelectionSort temp

FilterUniqueSort = Application.Transpose(temp)

End Function





Function SelectionSort(TempArray As Variant)
          Dim MaxVal As Variant
          Dim MaxIndex As Integer
          Dim i, j As Integer

          For i = UBound(TempArray) To 0 Step -1


              MaxVal = TempArray(i)
              MaxIndex = i


              For j = 0 To i
                  If TempArray(j) &amp;amp;amp;amp;amp;gt; MaxVal Then
                      MaxVal = TempArray(j)
                      MaxIndex = j
                  End If
              Next j

              If MaxIndex &amp;amp;amp;amp;amp;lt; i Then
                  TempArray(MaxIndex) = TempArray(i)
                  TempArray(i) = MaxVal
              End If
          Next i

      End Function


¿Dónde copiar código vba?

  1. Presione Alt + F11
  2. Inserte un módulo en su libro de trabajo
  3. Copie (Ctrl + c) el código anterior en la ventana de código

Descargar excel 97-2003 *, archivo xls

Crear-una-lista-desplegable-que contiene solo-único-vba.xls