Autor: Oscar Cronquist Artículo actualizado en Abril 25, 2019

Josh pregunta:

Ahora, si solo supiera cómo aplicar estas selecciones desplegables dependientes a un filtro, se establecería.

Respuesta

Tabla de contenido

Resumen
Crear rangos nombrados de expansión automática
Fórmula de matriz en la hoja de cálculo
Cree otros dos rangos con nombre de expansión automática en la hoja "Cálculo"
Crear cuadros combinados
Configurar cuadros combinados
Fórmula de matriz en la hoja de filtro
Descargar libro de ejemplo

Resumen

El filtro o una tabla de Excel pueden realizar esta tarea en milisegundos, pero varias personas han preguntado cómo hacerlo utilizando listas desplegables.

Así que en este post voy a utilizar dependiente cuadros combinados en lugar de listas desplegables. No vamos a utilizar ningún código vba pero necesitamos crear una hoja de "cálculo".

¿Por qué usar combo box? Si decide cambiar un valor en un cuadro combinado, otros valores de cuadro combinado dependientes cambian instantáneamente. Este no es el caso con las listas desplegables.

Vamos a crear dos cuadros combinados. El primer cuadro combinado contiene valores distintos únicos de la columna A. El segundo cuadro combinado contiene valores distintos únicos de la columna B, en función del valor seleccionado en el primer cuadro combinado. Vea las imágenes a continuación.

Los datos con los que estamos trabajando:

El resultado final:

Si quieres crear listas desplegables lee este artículo: Cree listas desplegables dependientes que contengan valores distintos únicos en Excel y luego sigue leyendo este artículo.

¿Qué es una lista distinta única? Elimine todos los duplicados en una lista y habrá creado una lista distinta única.

He creado tres hojas.

  • Filtra
  • Fecha de la cita
  • Cálculo

Crear rangos nombrados de expansión automática

Vamos a crear dos rangos con nombre, que se expandirán automáticamente cuando se agreguen nuevos valores. Los dos rangos nombrados existen en la hoja "Datos"

Rango con nombre "orden"

  1. Haga clic en la pestaña "Fórmulas"
  2. Haga clic en "Administrador de nombres"
  3. Haga clic en "Nuevo ..."
  4. Escribe un nombre. Lo llamé "orden". (Ver archivo adjunto al final de esta publicación)
  5. Escriba = OFFSET (Datos! $ A $ 3, 0, 0, COUNTA (Datos! $ A $ 3: $ A $ 1000)) en el campo "Se refiere a:".
  6. Haga clic en el botón "Cerrar"

Gama denominada "producto"

  1. Haga clic en la pestaña "Fórmulas"
  2. Haga clic en "Administrador de nombres"
  3. Haga clic en "Nuevo ..."
  4. Escribe un nombre. Lo llamé "producto". (Ver archivo adjunto al final de esta publicación)
  5. Escriba = OFFSET (Datos! $ B $ 3, 0, 0, COUNTA (Datos! $ B $ 3: $ B $ 1000)) en el campo "Se refiere a:".
  6. Haga clic en el botón "Cerrar"


Fórmula de matriz en la hoja "Cálculo"

Fórmula de matriz en la celda A2:

=IFERROR(INDEX(order, MATCH(0, COUNTIF($A$1:A1, order), 0)), "")

Presione CTRL + MAYÚS + ENTRAR. Copie la celda A2 y péguela hasta donde sea necesario.

Fórmula de matriz en la celda B2:

=IFERROR(INDEX(product, MATCH(0, COUNTIF($B$1:B1, product)+(order<>INDEX(Calculation!$A$2:$A$3, Filter!$B$1)), 0)), "")

Presione CTRL + MAYÚS + ENTRAR. Copie la celda B2 y péguela hasta donde sea necesario.

Los valores en la columna B cambian según el valor seleccionado en el cuadro combinado en la hoja "Filtro". No se preocupe, pronto crearemos los cuadros combinados.

Cree otros dos rangos con nombre de expansión automática en la hoja "Cálculo"

Los rangos con nombre se expanden automáticamente cuando las listas cambian en la columna A y B.

Rango con nombre "orden única"

  1. Haga clic en la pestaña "Fórmulas"
  2. Haga clic en "Administrador de nombres"
  3. Haga clic en "Nuevo ..."
  4. Escribe un nombre. Lo nombré "orden única". (Ver archivo adjunto al final de esta publicación)
  5. Tipo = OFFSET (Cálculo! $ A $ 2, 0, 0, COUNT (IF (Cálculo! $ A $ 2: $ A $ 1000 = "", "", 1)), 1) en el campo "Se refiere a:" .
  6. Haga clic en el botón "Cerrar"

Rango con nombre "uniqueproduct"

  1. Haga clic en la pestaña "Fórmulas"
  2. Haga clic en "Administrador de nombres"
  3. Haga clic en "Nuevo ..."
  4. Escribe un nombre. Lo nombré "producto único". (Ver archivo adjunto al final de esta publicación)
  5. Tipo = OFFSET (Cálculo! $ B $ 2, 0, 0, COUNT (IF (Cálculo! $ B $ 2: $ B $ 1000 = "", "", 1)), 1) en el campo "Se refiere a:" .
  6. Haga clic en el botón "Cerrar"

Crear cuadros combinados

  1. Haga clic en la pestaña "Desarrollador" en la cinta
  2. Haga clic en el botón "Insertar"
  3. Haga clic en el botón de cuadro combinado
  4. Coloque el cuadro combinado en la celda B2 en la hoja "Filtro

Crear un segundo cuadro combinado en la celda C2.

Cuadro combinado de configuración 1

  1. Seleccione hoja "Filtro"
  2. Haga clic derecho en el cuadro combinado en la celda B2
  3. Haga clic en "Control de Formato ..."
  4. Haga clic en la pestaña "Control"
  5. Tipo orden única en el rango de entrada:
  6. Seleccione la celda B1 en el enlace de la celda:
  7. haga clic en Aceptar

Cuadro combinado de configuración 2

  1. Seleccione hoja "Filtro"
  2. Haga clic derecho en el cuadro combinado en la celda C2
  3. Haga clic en "Control de Formato ..."
  4. Haga clic en la pestaña "Control"
  5. Tipo producto único en el rango de entrada:
  6. Seleccione la celda C1 en el enlace de la celda:
  7. haga clic en Aceptar

He ocultado los valores en las celdas B1 y C1.

  1. Seleccione B1: C1
  2. Presione Ctrl + 1
  3. Haga clic en "Personalizar" en la ventana Categoría
  4. Escriba ,,, en el campo "Tipo:"
  5. haga clic en Aceptar

Fórmula de matriz en la hoja "Filtro"


Fórmula de matriz en la celda B7:

=IFERROR(INDEX(Data!$A$3:$C$11, SMALL(IF((INDEX(uniqueorder, $B$1)=order)*(INDEX(uniqueproduct, $C$1)=product), ROW(order)-MIN(ROW(order))+1, ""), ROW(A1)), COLUMN(A1)), "")

Presione CTRL + MAYÚS + ENTRAR. Copie la celda B7 y péguela a la derecha de la celda B9. Copie el rango de celdas B7: B9 y péguelo hasta donde sea necesario.

Artículos relacionados:

Cree una lista desplegable que contenga únicamente valores de texto ordenados alfabéticamente distintos y únicos utilizando la fórmula matricial de excel
Cree listas desplegables dependientes que contengan valores distintos únicos en Excel