¿Cómo puedo implementar la lista de validación de datos dependientes dinámicos en Excel?

Question

Las fuentes en la web muestran cómo implementar listas de validación de datos dependientes, pero todo lo que he encontrado es estático y no se actualiza automáticamente.

Creo que quien pueda responder esta pregunta ya sabe cómo funcionan las listas de validación de datos a nivel profesional, pero para estudiantes como yo daré una breve descripción (sugiero leer más en videos de Google y YouTube).

Las listas de validación de datos le permiten crear una estructura de datos definida en su libro de trabajo. Están más destinados a la interacción del usuario. Si desea que un usuario del libro de trabajo solo inserte un conjunto de datos determinados, por ejemplo, Donuts, Pasteles, Muffins y Crumpets, puede crear una lista usando un rango con nombre, diga "Snacks" o puede crear una tabla y nombrarla como "Snacks". Cuando el nombre de pila es una palabra (es decir, no existen espacios en el nombre de pila)

La desventaja de usar listas es que si tuviéramos que agregar otro bocadillo a la lista llamado Croissants, entonces tendríamos que redefinir las listas creadas originalmente. Las tablas evitan esta ineficiencia en el sentido de que puede agregar fácilmente una nueva fila a una tabla agregándola desde la última fila o escribiendo la fila siguiente después de la fila de la tabla actual y presionando Enter.

Ahora, para la validación de los datos, recomiendo visitar el siguiente enlace para obtener una exposición detallada, ya que una explicación hace que esta pregunta sea demasiado larga.http://analyticsdemystified.com/excel-tips/excel-dropdowns-done-right/

Por lo tanto, una lista de validación dependiente es una lista que se basa en el valor seleccionado por el usuario de una lista de validación anterior; por ejemplo, los tipos de donas incluyen donas con anillo helado, glaseadas con chocolate, mermelada y natillas. Los tipos de muffins incluyen plátano, arándano y chocolate. Los tipos de pasteles incluyen terciopelo rojo, zanahoria y coco. Finalmente, los tipos de Crumpets incluyen el inglés, el escocés y el Pikelets.

Por lo tanto, si una celda tiene un menú desplegable de la lista de categorías principal, ¿cómo se crea una lista de validación dependiente dinámica basada en las subcategorías?

Por lista de validación dependiente dinámica, me refiero a que todas las listas de columnas son tablas como se muestra a continuación.

Ahora la pregunta es ¿cómo implemento una celda adyacente, por ejemplo, si la celda C13 tiene una lista de validación para la tabla de refrigerios? ¿Cómo puedo hacer que la celda D13 sea una lista de validación basada en la entrada seleccionada por el usuario de C13 (es decir, si C13 está seleccionado como Donuts, entonces la posible selección de la lista de validación de D13 serán los datos de la columna de la tabla Donuts).

Por favor, es importante tener en cuenta que la lista de validación original en C13 se habría creado usando la función INDIRECTA ya que es una tabla y no un rango con nombre.

Answer 1

Manera fácil en 4 simples pasos

Paso 1 Crea tus tablas:

Paso 2 Asigne un nombre a sus tablas: seleccione las tablas y cámbieles el nombre para que coincida con el valor de la tabla principal.Tabla 1, p.ej. La tabla 2 con el encabezado "Tortas" debe tener el nombrePastel, ya que ese es el valor en la tabla principalTabla 1.
¿Pero cómo? Haga clic en la esquina de la tabla para seleccionarla, ingrese una nueva etiqueta donde diceTabla 2o similar y presione enter. No se preocupe si todavía dice "Tabla2", etc.
(¡Nota!Esto no es necesario para la tabla principal, por ejemplo. Tabla 1.)

Paso 3 Seleccione dónde desea su primera lista, vaya a la pestaña de datos y a la validación de datos. Seleccione "lista" e ingrese: =INDIRECT("Table1")en la ventana de origen:

Etapa 4 Seleccione una celda para la segunda lista, que dependerá de la primera lista. Regrese a la validación de datos, elija "lista" e ingrese =INDIRECT(K2)donde "K2" es la ubicación de la primera lista.

HECHO

Para una tercera lista, que depende de la segunda lista, repita el paso 4 y consulte la segunda lista. ¡Buena suerte!

Answer 2

Tendrás que confiar en mi palabra sobre el hecho de que esto funciona, pero he escrito una macro (muy desordenada, en código espagueti) para este propósito exacto. Desafortunadamente, el código no es nada elegante pero bueno, ¡funciona! Como dicen... "Hazlo funcionar, hazlo bien, hazlo rápido".

Paso 1: el código VBA

Necesitarás tres cosas:

1. Módulo de matrices de Chip Pearson--- el código VBA real está hacia el final.

2. Módulo de clasificación de Chip Pearson-- código VBA real también hacia el final.

3. Mi macro terriblemente organizada

Para pegarlos en módulos VBA en su libro de trabajo, presione Alt+ F11, busque su proyecto en el Explorador de proyectos, haga clic derecho en la carpeta Módulos y seleccione "Insertar módulo". Pegue el código de cada uno de los 3 enlaces anteriores en módulos separados.

Mientras esté en el Editor VBA, abra el cuadro de diálogo Referencias ( Alt+ Tluego Enter) y marque 'Microsoft Scripting Runtime'.

Paso 2: Estructurar tus datos

Una vez aclarado esto, lo que querrá ahora es una hoja con sus datos (llamada, por ejemplo, 'Datos') como si fuera una base de datos bien estructurada. Así es como lo haría:

Observe que he creado dos hojas más. Uno llamado Validación para donde se lleva a cabo la lógica de validación detallada y otro llamado Principal para el contenido real que le importa al usuario final.

Paso 3: hoja principal

Pasemos a la hoja de datos y configuremos la siguiente estructura:

Seleccione la celda C2y asígnele un nombre Snack.Selected. Para nombrar una celda, selecciónela y luego presione Alt M M D, luego escriba el nombre en el cuadro de diálogo que aparece, tal como se muestra en la imagen. Por ahora, ingresa un valor como "Pastel" para que el siguiente paso tenga más sentido.

Continúe y nombre también la celda C3"Tipo.Seleccionado", pero déjela vacía por ahora.

Paso 4: Obtener valores de nuestra tabla en la hoja de datos

Vaya a su hoja de Validación y configure la siguiente estructura:

Como puedes ver en la captura de pantalla, debes seleccionar celdas B3:B20y escribir la siguiente fórmula, que es donde mi Macro realmente entra en juego:

=MultiLookup("Data","Snack",TRUE,TRUE)

En lugar de presionar Enter, presione Ctrl+Shift+Enter porque este es unFórmula de matriz

Si funciona como se anuncia, debería ver una lista de sus refrigerios seguida de algunos errores #N/A. Los errores son esperados: solo significan que tienes menos bocadillos de los que esperabas según el tamaño de B3:B20.

Explicando los parámetros en la fórmula:

1. Hoja donde se ubican los datos (“Datos”)
2. Campo que deseas de esa hoja (“Snack”)
3. ¿Eliminar duplicados (es decir, agrupados)? (VERDADERO)
4. ¿Ordenados alfabéticamente? (VERDADERO)

Ok, parece mucho trabajo por poco valor, pero aquí viene el gran final. En las celdas C3:C20, colocarás la siguiente fórmula:

=MultiLookup("Data","Type",TRUE,TRUE,"Snack",Snack.Selected)

Esto debería mostrar los tipos de Pastel disponibles, cuál es el valor que ingresaste en la hoja Principal en el paso anterior, ¿recuerdas?

Esto funciona agregando dos argumentos a la fórmula:

5. Campo para filtrar ("Snack")
6. Seleccione solo valores iguales a (Snack.Selected)

Entonces, cuando cambia sus datos en Principal, ¡la columna Tipos se actualiza automáticamente!

Paso 5: Conclusión de la validación

Para concluir, terminemos nuestra validación de datos creando los nombres Snack.Choicesy Type.Choices. Por favor nombre las celdas B1y C1en Validación respectivamente. También puedes escribir en este pequeño cuadro para nombrar una celda:

En la celda B1, debes colocar la siguiente fórmula:

="Validation!"&CELL("address",B3)&":"&CELL("address",OFFSET(B$2,COUNTIF(B3:B50,"*"),0))

Lo que esto hace es crear una referencia de texto a ¡Validación!$B$3:$B$6, que es donde se encuentran sus opciones de refrigerios. Copia esa fórmula a la derecha y ¡listo!

Regrese a su hoja Principal y consultemos estas celdas en las reglas de validación para Merienda y Tipo.

Seleccione la celda C2y kbd>Alt A V Vpara crear Validación de datos. Elija 'Lista' y establezca la fuente en =INDIRECT(Snack.Choices). Observe que no hay comillasSnack.Choices

Haga lo mismo en el celular C3pero configure la fuente en =INDIRECT(Type.Choices).

Usamos INDIRECT()porque los valores de Snack.Choicesy Type.Choicesson referencias indirectas (es decir, texto) a rangos en nuestro libro de trabajo.

Ahora juegue con las opciones de validación para ver todo en acción.

¡Hazme saber si tienes alguna pregunta!