• Inicio
  • Programas
  • Excel
  • Listas desplegables dependientes múltiples con rangos dinámicos y sin muchos nombres en Excel (ejemplo)

Listas desplegables dependientes múltiples con rangos dinámicos y sin muchos nombres en Excel (ejemplo)

Listas desplegables dependientes múltiples con rangos dinámicos y sin muchos nombres

Bueno esto es un procedimiento que desarrolle teniendo como idea original la publicación de la técnica de enlazar listas desplegables dependientes múltiples del blog JLD Excel en Castellano. En la entrada “Listas desplegables dependientes múltiples”  publicada en dicho blog utilizan como ejemplo una lista de continentes con sus respectivos países y ciudades, la que tomare para mi ejemplo también.

La función INDIRECTO es muy usada y los nombres definidos para el libro no son dinámicos, esto quiere decir que si queremos agregar más continentes, más países o más ciudades debemos de modificar los nombres, me pongo a pensar que pasaría si en vez de países tengo personas u otra información con muchos datos, sería descomunal la definición de nombres, así que propondré otra solución más práctica. Cabe recalcar que no es el mismo ejercicio, tiene una variación, pero se supone que son listas dependientes de tres niveles.

El objetivo de este ejercicio es lograr generar listas desplegables múltiples utilizando 4 nombres definidos, sin usar la función INDIRECTO y que la lista de datos sea dinámica, quiero decir que se podrán agregar más países, ciudades y su respectiva población como respuesta, además utilizaremos solamente una hoja del documento.

Primero empezamos por colocar los datos en nuestra hoja de trabajo ordenadamente (esto sería el único problema presente pero se soluciona muy rápido, una recomendación bastante buena es utilizar el rellenado de celdas en blanco como lo explican en este post “Como seleccionar celdas en blanco solamente“):

Ahora tenemos que separar todos los países que se repiten en otra columna aparte la que será la lista inicial. Otra recomendación es seleccionar toda la columna de los países, ir a la pestaña datos, grupo ordenar y filtrar, botón avanzadas y lo copian a otro lugar teniendo en cuenta que copiaran solo registros únicos.

Luego tenemos que empezar asignando el nombre de nuestra primera lista (pestaña formulas, grupo nombres definidos, botón administrador de nombres y luego pulsan en nuevo), me refiero a la lista de países que debe ingresarse en una columna aparte.

El nombre nuevo se denominara “MENU” y la formula que contiene es la siguiente:

=DESREF(Hoja1!$D$2,0,0,CONTARA(Hoja1!$D:$D)-1,1)

Se supone que mi MENU de países se encuentra en la columna D y en la Hoja1. La fórmula representa un rango dinámico que se utiliza cuando quieran agregar más datos y considere todo esto en la lista sin cambiar el alcance la fórmula. Para más información pueden visitar esta entrada “Rangos dinámicos y funciones volátiles“.

Luego creo una lista (en la celda F3) con los datos del nombre MENU, para eso me dirijo a la pestaña datos, grupo Herramientas de datos, botón validación de datos.

Como verán en la imagen anterior selecciono la opción lista y presiono F3 para obtener el origen de mi lista (me refiero al nombre MENU)
Bueno lo que sigue es crear nuevos nombres el cual les detallo a continuación:

SOLOPAISES        >>        =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)
SOLOCIUDADES    >>        =DESREF(Hoja1!$B$2,Hoja1!$F$2-1,0,Hoja1!$F$1,1)
AREATRABAJO        >>        =DESREF(Hoja1!$A$2,Hoja1!$F$2-1,0,Hoja1!$F$1,3)

Estos nombres hacen referencia a la celda F1 y F2 en la que crearemos las siguientes formulas:

F1    >>        =CONTAR.SI(SOLOPAISES,$F$3)
F2    >>        =COINCIDIR($F$3,SOLOPAISES,0)

En forma detallada:

SOLOPAISES: Representa el rango dinámico de la columna PAISES
SOLOCIUDADES: Representa el rango dinámico de la columna CUIDADES teniendo en cuenta que sólo son ciudades dentro de un país elegido
AREATRABAJO: Es un rango doblemente dinámico ya que se desplaza ayudado de las fórmulas en las celdas F1 y F2

F1: Fórmula que cuenta cuantas ciudades existen de acuerdo con el país que elija
F2: Fórmula que muestra la posición inicial de un país elegido

Dadas las aclaraciones asignamos nuestra segunda lista (en la celda G3) utilizando el botón de validación de datos, la fórmula del origen será:
=SOLOCIUDADES

Por último en nuestra tercera celda va el resultado o los datos de la columna POBLACION, que se hace posible con la fórmula:
=INDICE(AREATRABAJO,COINCIDIR($G$3,SOLOCUIDADES,0),3)

Y listo, ahora tienen listas desplegables dependientes que arrojan un resultado. El ejercicio de adapta también a bases de datos generadas por otros programas y que podemos utilizar en Excel.

Una opción muy buena para una mejor presentación seria integrar las fórmulas de las celdas F1 y F2 a los nombres definidos de la siguiente manera:

SOLOPAISES
=DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)

SOLOCIUDADES
=DESREF(Hoja1!$B$2,COINCIDIR($F$3,SOLOPAISES,0)-1,0,CONTAR.SI(SOLOPAISES,$F$3),1)

AREATRABAJO
=DESREF(Hoja1!$A$2,COINCIDIR($F$3,SOLOPAISES,0)-1,0,CONTAR.SI(SOLOPAISES,$F$3),3)

Finalmente agradezco a Jorge L. Dunkelman por crear su fabuloso blog que me sirvió para perfeccionar mis conocimientos en Excel.

Etiquetas:,

"Trackback" Enlace desde tu web.

M. Vizcarra

Ing. Industrial, egresado de la Facultad de Ingeniería Industrial y de Sistemas de la Universidad Nacional Hermilio Valdizán de Huánuco, Perú.

Comentarios (13)

  • MARIANGELA SANCHEZ

    |

    MUY BUEN APORTE Y LOGRE ADAPTARLO A MIS LISTAS AHORA QUISIERA SABER SI PODRIAS AYUDARME PARA QUE ESTO NO SOLO DEPENDA DE LA CELDA F3 SI NO Q PUEDA COPIARLA EN n CELDAS POR EJEMPLO DE LA MISMA COLUMNA F DESDE F2 HASTA F101 Y QUE LA COLUMNA G SEA DEPENDIENTE DE LA F DE ESA MISMA FILA, ESPERO HABERME SABIDO EXPLICAR MUCHAS GRACIAS

    Reply

  • MARCO

    |

    BUENAS NOCHES, SE PUEDE AGREGAR UNA COLUMNA MAS PARA QUE NOS FILTRE LOS MUNICIPIOS, DE CADA ESTADO?
    COMO HACERLO?
    SALUDOS.

    Reply

  • MARCOS

    |

    veo que en los ejemplos solamente se pueden realizar 3 listas dependientes, utilizando INDIRECTO Y SUSTITUIR ¿ se puede realizar una nueva lista dependiente osea una cuarta dependencia utilizando este mismo proceso?

    Reply

  • NORBERTO

    |

    AGRADEZCO EL APORTE, PERO TENGO LA MISMA INQUIETUD QUE MARIANGELA RESPECTO A PODER APLICAR ESTE PROCEDIMIENTO PARA “N” CELDAS. ESTARE ATENTO A LA RESOLUCIÓN DE ESTA INQUIETUD.

    Reply

  • FERNANDO

    |

    Está excelente archivo, ya lo pude echar a andar y funciona sin problemas!

    Reply

  • GLORIA

    |

    hola este archivo me sirve en parte pero necesito saber somo aplicar estas listas a varias celdas para que se puedan esoger varios items.

    Reply

  • german

    |

    El archivo esta super para solucionar varios proyectos. Pero Tengo la misma duda de gloria, pero aplicada a una bd de datos en excel.

    que pasa si estoy constuyendo una base de datos en la que quiero que el usuario vaya llenando solo con combos y evitando de esta forma los errores de escritura . Gracias

    Reply

  • Emilio Ramirez

    |

    Hola Manuel.

    No sabes como he buscado algo que me pueda ayudar en los listados despegables, todos dicen los mismo pero tu has ido mas alla y con tu aporte he podido resolver mi problema. Mira te cuento..

    Tengo una matriz de medicinas estas tienen un codigo llamado CUM, ahora quiero asignar este codigo a otra matriz de medicinas de una clinica y para asignar el CUM es necesario una homologacion uno a uno, es decir manual, pero con tu ejemplo he podido buscar crear listas deplegables dinamicos, es decir que si en la celda A2 esta Panadol entonces en la celda B2 aparece una Lista Desplegable con todos los codigos CUM del Panadol. De esta manera en la siguiente celda B3 aparecera solo las opciones segun el campo A3 y asi en cada fila, he variado e integrado mejor la formula y me ha salido de maravilla, gracias a ti!

    Siguenos apoyando cuando puedas estimado.

    Un abrazo desde Lima-Peru y muchas felicidades por tu blog!

    Slds.

    Reply

  • Richard

    |

    Un saludos a todos, es excelente el foro y justo encontré una solución a algún problema que tenia con validaciones por aquí.
    Ahora tengo un nuevo problemita que espero puedan ayudarme, Lo pondré de manera simple:

    Tengo un lista de departamentos y nombre de usuarios:

    RRHH: Luis Borja, Ana Lescano, Gabriel Díaz
    IT: Andrés Rojas, Pedro Alonso, Marco Flores
    CONTABILIDAD: Alberto Sosa, Joaquin Vega, Adriana Bernuy, Paola Torres.

    En mi base de datos tengo estos nombres codificados:

    LBO:Luis Borja
    ALE: Ana Lescano
    GDI: Gabriel Díaz
    ARO: Andrés Rojas
    PAL: Pedro Alonso
    MFL: Marco Flores
    ASO: Alberto Sosa
    JVE: Joaquin Vega
    ABE: Adriana Bernuy
    PTO: Paola Torres.

    1. Deseo hacer en la celda A1: una lista desplegable con los departamentos RRHH, IT, CONTABILIDAD ….(Validación de datos LIST =AREAS)

    2. Hacer en la celda A2: una lista desplegable con los nombres de los usuarios a cada departamento (DESREF, COINCIDIR, COUNT.SI)…..Aquí viene la pregunta, ¿Es posible que luego de seleccionar el departamento y enseguida me aparezca los nombres de cada usuario me ponga el código de Usuario, pero al hacer el desplegable salga el nombre completo?

    A1: IT

    A2 desplegable……..
    USUARIOS:
    Andrés Rojas
    Pedro Alonso
    Marco Flores

    ….finalmente en A2deseo tener:

    A2: ARO ó A2: PAL ó A2: MFL

    Espero haberme dejado entender, y desde ya agradeceré me puedan dar una mano con esto.

    Saludos,

    Richard F.

    Reply

  • ANIBAL

    |

    IMPLEMENTE LA SOLUCION PARA LISTAS DEPENDIENTES Y FUNCIONA PERFECTO.
    AHORA, NECESITO PODER USAR LAS LISTAS DEPENDIENTES CREADAS EN DISTINTAS FILAS DE LA MISMA HOJA. (ESTOY CARGARDO REGISTROS). LA SOLUCION APORTADA HASTA AHORA SOLO SIRVE PARA UBICAR EL RESULTADO DE LA SELECCION EN UNA SOLA CELDA . YA QUE LAS FUNCIONES CONTAR Y COINCIDIR ESTAS REFERENCIADAS A LA CELDA F3.
    HAY FORMA DE PODER MODIFICAR ESTO? ESPERO RESPUESTA,GRACIAS

    Reply

  • Adriana Murillo

    |

    Con un buscarV que dependa de tu selección te puedes traer el código de la persona de la tabla de Datos

    Reply

  • lorena landazuri

    |

    Como puedo ampliar el numero de columnas en este ejercicio, tengo 5 columnas de datos de texto y un al final un dato numerico asi:
    Compañía, Segmento, Centro de Costo, Rubro Presupuestal, subrubro Presupuestal y Numero del Centreo de Costos.
    Deseo ir asociando las listas en el orden descrito y al final me arroje el numero del centro de costos. Como puedo Hacer esto!!!

    Reply

  • sadsad

    |

    Espero se siga leyendo este foro! muy buenas las indicaciones, lo que sí, no se si será un error por las versiones (tuve que cambiar todas las “,” por “;”). pero cuando mencionas el error que aparece cuando se crea la segunda lista desplegable, pongo aceptar y al seleccionar un país, luego no me deja usar la siguiente lista que hace referencia a las ciudades.

    ojala me puedan ayudar! muchas Gracias

    Reply

Deja un comentario