Autor: Oscar Cronquist Artículo actualizado en octubre 01, 2019

Este artículo muestra cómo llenar una lista desplegable con valores filtrados de una tabla definida por Excel.

La siguiente imagen animada muestra cómo los valores (Nombre) en una lista desplegable cambian según la forma en que se filtra la tabla (País).

Cómo construir esto

Primero necesitamos insertar una nueva hoja de trabajo, esta nueva hoja de trabajo contendrá una fórmula que extrae valores filtrados de la Tabla definida por Excel.

La lista desplegable contiene un rango con nombre que buscará los valores extraídos de la nueva hoja de trabajo.

Hoja de cálculo

  1. Haga clic derecho en cualquier pestaña de la hoja de trabajo en la parte inferior de la pantalla de Excel.
  2. Haga clic en "Insertar" para abrir un cuadro de diálogo.
  3. Seleccione el icono "Hoja de trabajo".
  4. Haga clic en el botón Aceptar.

Para cambiar el nombre de la hoja de trabajo, simplemente haga clic derecho en la pestaña de la hoja de trabajo y luego haga clic en "Cambiar nombre".

Escriba un nuevo nombre de hoja de cálculo y presione Entrar, lo llamé "Cálculos".

Fórmula

Ingresé un nombre de encabezado en la celda B2.

Fórmula en la celda B3:

=IFERROR(INDEX(Table2[First Name], SMALL(IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), MATCH(ROW(Table2[First Name]), ROW(Table2[First Name])), ""), ROWS($A$1:A1))), "")
  1. Haga doble clic en la celda B3.
  2. Pegue la fórmula de matriz por encima, acceso directo CTRL + v.
  3. Mantenga presionadas las teclas CTRL y MAYÚS simultáneamente
  4. Presione Enter una vez

La fórmula en la barra de fórmulas ahora se ve así: {= fórmula}
No entre estos paréntesis rizadas a sí mismo, que aparecen de forma automática si se hizo correctamente los pasos anteriores.

Explicando la fórmula en la celda B3.

Paso 1 - Crea una matriz

La Función de desplazamiento le permite identificar valores filtrados si lo combina con Función subtotal.

OFFSET (Table2 [Nombre], MATCH (ROW (Table2 [Nombre]), ROW (Table2 [Nombre])) - 1, 0, 1)

se convierte en

OFFSET (Table2 [Nombre], {0; 1; 2; 3; 4; 5; 6; 7; NUMX: Recolección principal; 8, 9)

y devoluciones

{"Kaya"; "Fraser"; "Jui"; "Cirilo"; "Spencer"; "Horacio"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; "Tiara"; "Damien"; "Linnie"; "Jui"; "Fraser"; "Siena"; "Lynna"; "Kelton"; "Kaya"}.

Este paso puede parecer extraño cuando solo puede usar una referencia de celda a los valores, sin embargo, eso no funcionará. Este paso es necesario para forzar a la función SUBTOTAL a evaluar cada valor en la matriz.

Nota la Función de desplazamiento es volátil, lo que significa que se recalcula cada vez que se recalcula el libro. El uso extensivo puede ralentizar considerablemente su libro de trabajo.

Paso 2 - Identificar valores filtrados

La Función subtotal devuelve 0 (cero) si el valor no es visible y 1 si el valor es visible.

SUBTOTAL (3, OFFSET (Table2 [First Name], MATCH (ROW (Table2 [First Name]), ROW (Table2 [First Name])) - 1, 0, 1))

se convierte en

SUBTOTAL (3, {"Kaya"; "Fraser"; "Jui"; "Cyril"; "Spencer"; "Horace"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; " Tiara ";" Damien ";" Linnie ";" Jui ";" Fraser ";" Siena ";" Lynna ";" Kelton ";" Kaya "})

y devoluciones

{0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1}

Paso 3: devuelve números de fila de valores filtrados

La Función IF devuelve el número de fila correspondiente de cada valor filtrado (visible) y un espacio en blanco para valores no visibles.

IF (SUBTOTAL (3, OFFSET (Table2 [First Name], MATCH (ROW (Table2 [First Name]), ROW (Table2 [First Name])) - 1, 0, 1)), MATCH (ROW (Table2 [First Nombre]), ROW (Table2 [Nombre])), "")

se convierte en

IF ({0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0; XN X Nombre]), ROW (Table0 [Nombre])), "")

se convierte en

IF ({0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0; ; 0; 1; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15}, "")

y devoluciones

{""; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; ""; ""; 15; ""; ""; ""; ""; 20}

Paso 4: extrae el k-ésimo número de fila más pequeño

La función PEQUEÑO extrae el k-ésimo número más pequeño en un rango de celdas o matriz.

PEQUEÑA(matriz, k)

SMALL (IF (SUBTOTAL (3, OFFSET (Table2 [First Name]), MATCH (ROW (Table2 [First Name]), ROW (Table2 [First Name])) - 1, 0, 1)), MATCH (ROW (Table2 [Nombre]), ROW (Table2 [Nombre])), ""), ROWS ($ A $ 1: A1))

se convierte en

PEQUEÑO ({""; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; ""; ""; 15; ""; " ";" ";" "; 20}, FILAS ($ A $ 1: A1))

se convierte en

PEQUEÑO ({""; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; ""; ""; 15; ""; " ";" ";" "; 20}, 1)

y devuelve 10.

Paso 5: valor de retorno basado en el número de fila

La función ÍNDICE devuelve un valor basado en un número de fila (y número de columna si es necesario).

ÍNDICE (Table2 [Nombre], PEQUEÑO (IF (SUBTOTAL (3, OFFSET (Table2 [Nombre)], MATCH (ROW (Table2 [Nombre)], ROW (Table2 [Nombre))) - 1, 0, 1 )), MATCH (ROW (Table2 [First Name]), ROW (Table2 [First Name])), ""), ROWS ($ A $ 1: A1)))

se convierte en

ÍNDICE (Table2 [Nombre], 10)

se convierte en

ÍNDICE ({"Kaya"; "Fraser"; "Jui"; "Cyril"; "Spencer"; "Horace"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; "Tiara" ; "Damien"; "Linnie"; "Jui"; "Fraser"; "Siena"; "Lynna"; "Kelton"; "Kaya"}, 10)

y devuelve "Fraser" en la celda B4, que es el valor 10th en la matriz.

La función IFERROR elimina los errores que se mostrarán cuando no haya más valores para mostrar.

Crear rango con nombre

  1. Ir a la pestaña "Fórmulas" en la cinta.
  2. Haga clic en el botón "Administrador de nombres".
  3. Haga clic en el botón "Nuevo ..." para abrir el cuadro de diálogo Administrador de nombres.
  4. Nombre: Drop_down_list
  5. Se refiere a: = INDIRECTO ("Cálculos! $ B $ 3: $ B $" & SUMPRODUCT ((¡Cálculos! $ B: $ B <> "") * 1) + 1)
  6. ¡Haga clic en Aceptar!
  7. Haga clic en Cerrar!

Explicando la fórmula de rango nombrado

La fórmula utilizada en el rango de nombres La lista desplegable es dinámico y cambia según el número de valores en la columna B en la hoja de trabajo "Cálculos.

Paso 1 - Identificar celdas no vacías en la columna B

Los signos menores y mayores que combinados verifican si las celdas están vacías o no.

Cálculos $ B: $ B <> ""

se convierte en

{""; "Valores de tabla filtrados"; "Fraser"; "Jui"; "Kaya"; "" ... ""} <> ""

y devoluciones

{"FALSO"; "VERDADERO"; "VERDADERO"; "VERDADERO"; "CIERTO"; "FALSO" ...; "FALSO"}

No es necesario verificar cada celda en la columna B, si este cálculo es lento para usted, use una referencia de celda más pequeña, por ejemplo: ¡Cálculos! $ B $ 1: $ B $ 1000 <> ""

Paso 2: cuente las celdas no vacías en la columna B

La función SUMPRODUCT devuelve un número que representa el número total de celdas no vacías.

SUMPRODUCT ((¡Cálculos! $ B: $ B <> "") * 1)

se convierte en

SUMPRODUCT ({"FALSE"; "TRUE"; "TRUE"; "TRUE"; "TRUE"; "FALSE" ...; "FALSE"} * 1)

La función SUMPRODUCT no puede sumar valores booleanos (VERDADERO o FALSO), por lo que debemos convertirlos a sus equivalentes numéricos, esto se hace multiplicando con 1.

SUMPRODUCTO ({0; 1; 1; 1; 1; 0 ...; 0})

y devuelve 4.

Paso 3 - Crear referencia de celda

El signo y le permite combinar texto con un cálculo.

"Cálculos! $ B $ 3: $ B $" & SUMPRODUCT ((¡Cálculos! $ B: $ B <> "") * 1) + 1

se convierte en

"Cálculos! $ B $ 3: $ B $" y 4 + 1

se convierte en

"Cálculos! $ B $ 3: $ B $" y 5

y devoluciones

"Cálculos! $ B $ 3: $ B $ 5"

Paso 4 - Convertir texto a referencia de celda

La Función INDIRECTA le permite usar el texto como referencia de celda.

INDIRECTO ("Cálculos! $ B $ 3: $ B $" y SUMPRODUCT ((¡Cálculos! $ B: $ B <> "") * 1) + 1)

se convierte en

INDIRECTO ("Cálculos! $ B $ 3: $ B $ 5")

y devuelve los valores en los cálculos del rango de celdas! $ B $ 3: $ B $ 5.

Crear lista desplegable

  1. Seleccionar celda D26
  2. Vaya a la pestaña "Datos" en el riboon.
  3. Haga clic en el botón "Validación de datos".
  4. Permitir: Lista
  5. Fuente: = Drop_down_list
  6. Haga clic en Aceptar