Autor: Oscar Cronquist Artículo actualizado en Febrero 27, 2019

En este artículo demostraré cómo usar un valor de un la lista desplegable y usarlo para hacer un búsqueda en un conjunto de datos o preferiblemente en una Tabla de Excel definida. En otras palabras, esto llenará automáticamente otras celdas al seleccionar valores en una lista desplegable.

También demostraré cómo crear, editar y agregar valores a una lista desplegable. Hay varios tipos de listas desplegables en Excel. Validación de datos y cuadros combinados. Tienen sus ventajas y desventajas y las discutiré en este artículo.

Lista desplegable de validación de datos

La lista desplegable de Validación de datos es fácil de configurar, pero tiene sus defectos, el propósito de la validación de datos es forzar al usuario a seleccionar uno de varios valores predeterminados.

Sin embargo, esto puede ser fácilmente ignorado por el usuario simplemente pegando un valor en la lista desplegable.

Otra desventaja es que la lista desplegable de validación de datos no le permitirá buscar un valor en la lista, esto puede hacer que su uso sea lento e ineficaz si contiene muchos valores.

¡Propina! Asegúrese de tener los valores ordenados de la A a la Z en una lista desplegable para facilitarle la vida al usuario.

Recomiendo usar un caja combo Si desea poder buscar en una lista desplegable. Un cuadro combinado es también una lista desplegable que puede crear y manipular fácilmente.

Crear una lista desplegable

La fuente de datos es en este caso una tabla definida por Excel que tiene su ventajas. Para poder usarlo en una lista desplegable se necesita una solución alternativa, la función INDIRECTA permite hacer referencia a la tabla como fuente.

  1. Seleccione la celda B3.
  2. Ir a la pestaña "Datos" en la cinta.
  3. Haga clic en el botón "Validación de datos"
  4. Seleccionar lista
  5. Tipo = INDIRECTO ("Table1 [Elemento]") en la ventana "Fuente:"
  6. haga clic en Aceptar

Fórmula que rellenará automáticamente una sola celda.

La primera fórmula que demostraré es una fórmula simple INDEX - MATCH que usará el valor seleccionado en la lista desplegable para buscar un conjunto de datos y devolver el valor adyacente si se encuentra una coincidencia.

Fórmula en la celda C3:

=INDEX(Table1[Color], MATCH(Sheet1!$B3, Table1[Item], 0))

El Función MATCH devuelve la posición relativa del valor de la lista desplegable en la columna Table1 [Item] si se encuentra.

COINCIDIR (Sheet1! $ B3, Table1 [Item], 0)

se convierte en

PARTIDO ("80X", {"200X"; "120X"; "80X"; "70X"; "150X"; "100X"; "130X"; "110X"; "140X"; "190X"; "90X"; "170X"; " ; "180X"; "160X"; "200X"; "120X"; "80X"; "150X"; "100X"; "130X"; "110X"; "90X"; "170X"; "180X"; " 0X "}, XNUMX)

y devuelve 3. "80X" se encuentra como el tercer valor en la matriz.

Tenga en cuenta que la función MATCH solo devuelve la posición relativa de la primera coincidencia.

El Función ÍNDICE devuelve el valor en la columna Table1 [Color] en función de la posición relativa devuelta por la función MATCH.

ÍNDICE (Table1 [Color], MATCH (Sheet1! $ B3, Table1 [Item], 0))

se convierte en

ÍNDICE (Tabla1 [Color], 3)

se convierte en

ÍNDICE ({"Navy"; "Lime"; "White"; "Yellow"; "Magenta / Fuchsia"; "Grey"; "Lime"; "Cyan / Aqua"; "Purple"; "Black"; "Teal" ; "Magenta / Fuchsia"; "Blanco"; "Teal"; "Teal"; "Blanco"; "Magenta / Fuchsia"; "Negro"; "Teal"; "Gris"; "Amarillo"; "Púrpura"; " Lima ";" Cian / Aqua "}, 3)

y devuelve "Blanco en la celda C3.

Fórmula que rellena automáticamente varias celdas.

La siguiente fórmula es más complicada, devuelve múltiples valores distribuidos horizontalmente a partir de la columna C.

Fórmula de matriz en la celda C3:

=IFERROR(INDEX(Table1[Color], SMALL(IF(Sheet2!$B3=Table1[Item], MATCH(ROW(Table1[Item]), ROW(Table1[Item])), ""), COLUMNS($A$1:A1))), "")

Para ingresar una fórmula matricial, escriba la fórmula en la celda C3 y luego presione y mantenga presionado CTRL + MAYÚS simultáneamente, ahora presione Entrar una vez. Suelte todas las teclas.

La barra de fórmulas ahora muestra la fórmula con un corchete de inicio y final que le indica que ingresó la fórmula con éxito. No entres en los rizos tú mismo.

Explicando la fórmula en la celda C3.

Paso 1 - Encuentra celdas que igualen condición

Sheet2! $ B3 = Table1 [Elemento] es una expresión lógica que devuelve valores booleanos, VERDADERO o FALSO.

Sheet2! $ B3 = Table1 [Elemento]

se convierte en

"80X" = {"200X"; "120X"; "80X"; "70X"; "150X"; "100X"; "130X"; "110X"; "140X"; "190X"; "90X"; "170X"; "180X"; "160X"; "200X"; "120X"; "80X"; "150X"; "100X"; "130X"; "110X"; "90X"; "170X"; "180X"}

y devuelve esta matriz:

{FALSO; FALSO; CIERTO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; CIERTO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}

Paso 2 - Calcular números de fila de celdas coincidentes

El Función IF tiene tres argumentos, el primero debe ser una expresión lógica. Si la expresión se evalúa como VERDADERA, sucede una cosa (argumento 2) y si FALSA sucede otra cosa (argumento 3).

La función reemplaza VERDADERO con el número de fila relativo correspondiente y FALSO con nada "".

IF (Sheet2! $ B3 = Table1 [Item], MATCH (ROW (Table1 [Item]), ROW (Table1 [Item])), "")

se convierte en

SI ({FALSO; FALSO; VERDADERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; VERDADERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO }, MATCH (ROW (Table1 [Item]), ROW (Table1 [Item])), "")

se convierte en

SI ({FALSO; FALSO; VERDADERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; VERDADERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO }, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; XUMX; 19; 20; }, "")

y devoluciones

{""; ""; 3; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 17; ""; ""; ""; ""; ""; ""; ""}.

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

Para poder devolver un nuevo valor en una celda cada uso el Pequeña función para filtrar los números de columna de menor a mayor.

El Función COLUMNAS realiza un seguimiento de los números basados ​​en una referencia de celda en expansión. Se expandirá a medida que la fórmula se copie a las celdas de abajo.

SMALL (IF (Sheet2! $ B3 = Table1 [Item], MATCH (ROW (Table1 [Item]), ROW (Table1 [Item])), ""), COLUMNS ($ A $ 1: A1))

se convierte en

PEQUEÑO ({""; ""; 3; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 17; ""; ""; ""; ""; ""; ""; ""}, COLUMNAS ($ A $ 1: A1))

se convierte en

PEQUEÑO ({""; ""; 3; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; 17; ""; ""; ""; ""; ""; ""; ""}, 1)

y devuelve 3.

Paso 4 - Valor de retorno de la tabla definida por Excel

El Función ÍNDICE devuelve un valor basado en una referencia de celda y números de columna / fila.

ÍNDICE (Table1 [Color], SMALL (IF (Sheet2! $ B3 = Table1 [Item], MATCH (ROW (Table1 [Item]), ROW (Table1 [Item])), ""), COLUMNS ($ A $ 1 : A1)))

se convierte en

ÍNDICE (Tabla1 [Color], 3)

y devuelve "Blanco" en la celda C3.

Paso 5 - Devolver en blanco si la fórmula devuelve un error

IFERROR (INDEX (Table1 [Color], SMALL (IF (Sheet2! $ B3 = Table1 [Item], MATCH (ROW (Table1 [Item]), ROW (Table1 [Item])), ""), COLUMNS ($ A $ 1: A1))), "")

El IFERROR maneja todos los errores en una fórmula, le permite especificar un valor para devolver si se encuentra un error.

Tenga en cuenta que la función IFERROR identifica todos los errores, esto puede dificultar la detección de errores. Úselo con precaución.