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

Este artículo muestra cómo configurar dependientes listas desplegables en multiples celdas. Las listas desplegables se completan en función del valor adyacente en la misma fila, en otras palabras, dependen unas de otras. Seleccione la Región "Este" mostrada anteriormente y solo puede seleccionar los Productos "BB" y "EE", la tabla en el rango de celdas B2: C11 determina cómo se relacionan.

Las listas desplegables regulares que se utilizan aquí son fáciles de insertar y personalizar, las fórmulas de este libro son fáciles de crear y también lo son las rangos nombrados también. Voy a explicar en detalle cómo funcionan.

El inconveniente de este enfoque es que solo puede usar dos listas desplegables conectadas. Por ejemplo, tres listas desplegables dependientes en realidad requieren una tabla tridimensional que es posible utilizando VBA, sin embargo, fuera del alcance de este artículo. Tenga en cuenta que, para ser claros, no hay código VBA en el ejemplo que se muestra aquí.

Otro inconveniente con las listas desplegables regulares es que si selecciona un valor de Región y luego un valor de Producto y luego regresa y cambia el valor de Región a otro valor, el valor del Producto permanece igual. Hubiera sido mejor si ese valor quedara en blanco en esas ocasiones.

También es posible pegar un valor y sobrescribir la lista desplegable, así es como están diseñados y no puedo hacer nada más que usar tal vez el código VBA o el código de evento para prevenir tales escenarios.

Echa un vistazo al sitio web de la Dependiente lista desplegable de complementos Si necesita más de dos listas desplegables dependientes.

Cómo construí este libro

Gama de celdas B2: C11 contiene una Tabla de Excel definida que se expande o se reduce automáticamente cuando se agregan o eliminan valores, respectivamente, no es necesario ajustar las referencias de las celdas cada vez que edite la Tabla definida por Excel, lo que supone un gran ahorro de tiempo.

También hay otro conjunto de datos, que se muestra a continuación, que se crea utilizando dos fórmulas de matriz. El conjunto de datos extrae elementos (Productos) para cada valor posible (Región), lo que facilita llenar las listas desplegables utilizando solo fórmulas y rangos con nombre.

Esta tabla es dinámica y cambia instantáneamente cada vez que se edita la tabla de origen. No se preocupe, explicaré la tabla anterior y las fórmulas utilizadas más adelante en este artículo.

Tabla de Excel definida

Aquí es cómo crear un Tabla de Excel definida:

  1. Seleccionar rango de celdas B2: C11.
  2. Ir a la pestaña "Insertar" en la cinta.
  3. Haga clic en el botón "Tabla".
  4. Haga clic en la casilla de verificación "Mi tabla tiene encabezados" si su conjunto de datos tiene encabezados.
  5. Haga clic en el botón Aceptar.

La razón principal por la que estoy usando una tabla definida por Excel aquí es que puede usar una "referencia estructurada" que es básicamente un nombre de tabla en lugar de una referencia de celda normal.

Relaciones entre categorias

La primera fórmula de matriz devuelve valores distintos únicos en la celda H2: M2 del rango con nombre Región. La segunda fórmula de matriz en el rango de celdas H3: M7 devuelve valores coherentes de cada región. Todos estos cálculos se pueden ocultar o colocar en una hoja de cálculo.

Fórmula de matriz en la celda H2:

=INDEX(Table1[Region], MATCH(0, COUNTIF($G$2:G2, Table1[Region]), 0))

Para ingresar una fórmula matricial, escriba la fórmula en una celda 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.

Tenga en cuenta que la fórmula anterior contiene una referencia de celda que cambia cuando copia la celda y la pega en el rango de celdas H2: M2.

Fórmula de matriz en la celda H3:

=INDEX(Table1[Product], SMALL(IF(H$2=Table1[Region], MATCH(ROW(Table1[Region]), ROW(Table1[Region]))), ROWS($A$1:A1)))

Copie la celda H3 y péguelo en el rango de celdas H3: M8, si tiene más categorías (Regiones) y Artículos (Productos) que este ejemplo, puede que tenga que extender las fórmulas a un rango de celdas más grande en consecuencia.

Rangos con nombre

Ahora es el momento de crear los dos últimos rangos con nombre que se utilizarán en las listas de validación de datos.

  1. Seleccione la celda F3, este paso es muy importante porque los rangos nombrados contienen fórmulas que contienen rangos de celdas relativos.
  2. Ir a la pestaña "Fórmula" en la cinta.
  3. Haga clic en el botón "Administrador de nombres" y aparecerá un cuadro de diálogo.
  4. Haga clic en el botón "Nuevo ...".
  5. Ingrese el nombre Región única se muestra a continuación.
  6. Introduzca la fórmula.
  7. Haga clic en el botón Aceptar.
  8. Repita el paso 3 a 6 con Producto único

Nombre: Región única

Fórmula:

=OFFSET('Dependent values'!$H$2, 0, 0, 1, MATCH("ZZZZZZZ", 'Dependent values'!$H$2:$T$2))

Nombre: Producto único

Fórmula:

=OFFSET('Dependent values'!$H$3, 0, MATCH('Dependent values'!$E3, 'Dependent values'!$H$2:$T$2, 0)-1, SUMPRODUCT(--('Dependent values'!$E3=Table1[Region])))

Puede encontrar una explicación de los rangos dinámicos con nombre que se muestran arriba en el artículo a continuación.

Crear un rango con nombre dinámico

Un rango con nombre dinámico crece automáticamente cuando se agregan nuevos valores y también se reduce si se eliminan los valores. Esto ahorra […]

Crear un rango con nombre dinámico

Listas de validación de datos

Los siguientes pasos le muestran cómo crear listas desplegables regulares en la columna E, vinculadas a un rango dinámico denominado Región única.

  1. Seleccionar celda E3
  2. Ir a la pestaña "Datos"
  3. Haga clic en el botón "Validación de datos"
  4. Seleccione "Lista"
  5. Type = UniqueRegion
  6. haga clic en Aceptar
  7. Copie la celda E3 y péguelas en las celdas de abajo según sea necesario.

Los siguientes pasos le muestran cómo crear listas desplegables regulares en la columna F, vinculadas a un rango dinámico denominado Producto único.

  1. Seleccionar celda F3
  2. Ir a la pestaña "Datos"
  3. Haga clic en el botón "Validación de datos"
  4. Seleccione "Lista"
  5. Tipo = Producto único
  6. haga clic en Aceptar
  7. Copie la celda F3 y péguelas en las celdas de abajo según sea necesario.