Com fer una llista desplegable a Excel

La llista desplegable és una eina increïblement útil que pot ajudar a treballar amb informació més còmode. Permet contenir diversos valors en una cel·la alhora, amb els quals podeu treballar, com amb qualsevol altra. Per seleccionar el que necessiteu, feu clic a la icona de fletxa, després de la qual es mostra una llista de valors uXNUMXbuXNUMXbis. Després de seleccionar-ne una de específica, la cel·la s'omple automàticament amb ella i les fórmules es tornen a calcular en funció d'ella.

Excel ofereix molts mètodes diferents per generar un menú desplegable i, a més, us permet personalitzar-los de manera flexible. Analitzem aquests mètodes amb més detall.

Procés de creació de llistes

Per generar un menú emergent, feu clic als elements del menú al llarg del camí "Dades" - "Validació de dades". S'obrirà un quadre de diàleg on haureu de trobar la pestanya "Paràmetres" i fer-hi clic si no s'ha obert abans. Té moltes opcions de configuració, però l'element "Tipus de dades" és important per a nosaltres. De tots els significats, "Llista" és el correcte.

El nombre de mètodes mitjançant els quals s'introdueix la informació a la llista emergent és bastant gran.

1. Indicació independent dels elements de la llista separats per un punt i coma al camp "Font" situat a la mateixa pestanya del mateix quadre de diàleg.
   

   

2. Indicació prèvia de valors. El camp Font conté l'interval on està disponible la informació requerida.
   

   

3. Especificació d'un interval amb nom. Un mètode que repeteix l'anterior, però només cal anomenar prèviament el rang.
   

   

Qualsevol d'aquests mètodes produirà el resultat desitjat. Vegem els mètodes per generar llistes desplegables en situacions de la vida real.

A partir de les dades de la llista

Suposem que tenim una taula que descriu els tipus de diferents fruites.

Per crear una llista en un menú desplegable basat en aquest conjunt d'informació, heu de fer el següent:

1. Seleccioneu la cel·la reservada per a la llista futura.
2. Cerqueu la pestanya Dades a la cinta. Allà fem clic a "Verificar dades".
   

   

3. Cerqueu l'element "Tipus de dades" i canvieu el valor a "Llista".
   

   

4. Al camp que indica l'opció "Font", introduïu l'interval desitjat. Tingueu en compte que s'han d'especificar referències absolutes perquè en copiar la llista, la informació no es desplaci.
   

A més, hi ha una funció per generar llistes alhora en més d'una cel·la. Per aconseguir-ho, hauríeu de seleccionar-los tots i realitzar els mateixos passos que s'han descrit anteriorment. De nou, heu d'assegurar-vos que s'escriuen referències absolutes. Si l'adreça no té un signe de dòlar al costat dels noms de les columnes i les files, haureu d'afegir-los prement la tecla F4 fins que el signe $ estigui al costat dels noms de les columnes i les files.

Amb gravació manual de dades

En la situació anterior, la llista es va escriure destacant l'interval requerit. Aquest és un mètode convenient, però de vegades és necessari registrar manualment les dades. Això permetrà evitar la duplicació d'informació al quadern de treball.

Suposem que estem davant de la tasca de crear una llista que contingui dues opcions possibles: sí i no. Per dur a terme la tasca, és necessari:

1. Feu clic a la cel·la per a la llista.
2. Obriu "Dades" i allà trobareu la secció "Comprovació de dades" que ens conegui.
   

   

3. De nou, seleccioneu el tipus "Llista".
   

   

4. Aquí heu d'introduir “Sí; No” com a font. Veiem que la informació s'introdueix manualment mitjançant un punt i coma per a l'enumeració.

Després de fer clic a D'acord, tenim el següent resultat.

A continuació, el programa crearà automàticament un menú desplegable a la cel·la adequada. Tota la informació que l'usuari ha especificat com a elements a la llista emergent. Les regles per crear una llista en diverses cel·les són semblants a les anteriors, amb l'única excepció que cal especificar la informació manualment mitjançant un punt i coma.

Creació d'una llista desplegable mitjançant la funció OFFSET

A més del mètode clàssic, és possible utilitzar la funció DISPOSICIÓper generar menús desplegables.

Obrim el full.

Per utilitzar la funció de la llista desplegable, heu de fer el següent:

1. Seleccioneu la cel·la d'interès on voleu col·locar la llista futura.
2. Obriu la pestanya "Dades" i la finestra "Validació de dades" en seqüència.
   

   

3. Estableix "Llista". Això es fa de la mateixa manera que els exemples anteriors. Finalment, s'utilitza la fórmula següent: =OFFSET (A$2$;0;0;5). L'introduïm on s'especifiquen les cel·les que s'utilitzaran com a argument.

A continuació, el programa crearà un menú amb una llista de fruites.

La sintaxi per a això és:

=OFFSET(referència,line_offset,column_offset,[alçada],[amplada])

Veiem que aquesta funció té 5 arguments. En primer lloc, es dóna la primera adreça de cel·la que s'ha de compensar. Els dos arguments següents especifiquen quantes files i columnes cal compensar. Parlant de nosaltres, l'argument Alçada és 5 perquè representa l'alçada de la llista. 

Llista desplegable a Excel amb substitució de dades (+ utilitzant la funció OFFSET)

En el cas donat DISPOSICIÓ permet crear un menú emergent situat en un rang fix. L'inconvenient d'aquest mètode és que després d'afegir l'element, haureu d'editar la fórmula vosaltres mateixos.

Per crear una llista dinàmica amb suport per introduir informació nova, heu de:

1. Seleccioneu la cel·la d'interès.
2. Amplieu la pestanya "Dades" i feu clic a "Validació de dades".
3. A la finestra que s'obre, torneu a seleccionar l'element "Llista" i especifiqueu la fórmula següent com a font de dades: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Feu clic a Acceptar.

Això conté una funció COUNTIF, per determinar immediatament quantes cel·les s'omplen (tot i que té un nombre molt més gran d'usos, només ho escrivim aquí amb un propòsit específic).

Perquè la fórmula funcioni amb normalitat, cal rastrejar si hi ha cel·les buides al camí de la fórmula. No ho haurien de ser.

Llista desplegable amb dades d'un altre full o fitxer Excel

El mètode clàssic no funciona si cal obtenir informació d'un altre document o fins i tot d'un full contingut en el mateix fitxer. Per a això s'utilitza la funció INDIRECTE, que permet introduir en el format correcte un enllaç a una cel·la situada en un altre full o, en general, un fitxer. Heu de fer el següent:

1. Activa la cel·la on col·loquem la llista.
2. Obrim la finestra que ja coneixem. Al mateix lloc on abans hem indicat fonts per a altres rangs, s'indica una fórmula en el format =INDIRECT(“[Llista1.xlsx]Full1!$A$1:$A$9”). Naturalment, en lloc de Llista1 i Full1, podeu inserir els noms del llibre i del full, respectivament. 

Atenció! El nom del fitxer s'indica entre claudàtors. En aquest cas, Excel no podrà utilitzar el fitxer que actualment està tancat com a font d'informació.

També cal tenir en compte que el propi nom del fitxer només té sentit si el document requerit es troba a la mateixa carpeta on s'inserirà la llista. En cas contrari, haureu d'especificar l'adreça d'aquest document íntegrament.

Creació de desplegables dependents

Una llista dependent és aquella el contingut de la qual es veu afectat per l'elecció de l'usuari en una altra llista. Suposem que tenim una taula oberta davant nostre que conté tres rangs, cadascun dels quals ha rebut un nom.

Heu de seguir aquests passos per generar llistes el resultat de les quals es vegi afectat per l'opció seleccionada en una altra llista.

1. Creeu la primera llista amb noms de rang.
   

   

2. Al punt d'entrada de la font, els indicadors necessaris es destaquen un a un.
   

   

3. Crea una 2a llista en funció del tipus de planta que hagi triat la persona. Alternativament, si especifiqueu arbres a la primera llista, la informació de la segona llista serà "alzina, carpe, castanyer" i més enllà. Cal anotar la fórmula al lloc d'entrada de la font de dades =INDIRECTE (E3). E3 – cel·la que conté el nom de l'interval 1.=INDIRECT(E3). E3 – cel·la amb el nom de la llista 1.

Ara tot està llest.

Com seleccionar diversos valors d'una llista desplegable?

De vegades no és possible donar preferència a un sol valor, de manera que cal seleccionar-ne més d'un. Aleshores, heu d'afegir una macro al codi de la pàgina. Amb la combinació de tecles Alt + F11 s'obre l'Editor de Visual Basic. I allà s'insereix el codi.

Full de treball secundari privat_Canvi (ByVal Target As Range)

    On Error Resume Next

    Si no es creua (Target, Range(«Е2:Е9»)) No és res i Target.Cells.Count = 1 Aleshores

        Application.EnableEvents = Fals

        Si Len (Target.Offset (0, 1)) = 0 Aleshores

            Target.Offset (0, 1) = Objectiu

        else

            Target.End (xlToRight) .Offset (0, 1) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub 

Per tal que el contingut de les cel·les es mostri a continuació, inserim el codi següent a l'editor.

Full de treball secundari privat_Canvi (ByVal Target As Range)

    On Error Resume Next

    Si no es creua (Target, Range(«Н2:К2»)) No és res i Target.Cells.Count = 1 Aleshores

        Application.EnableEvents = Fals

        Si Len (Target.Offset (1, 0)) = 0 Aleshores

            Target.Offset (1, 0) = Objectiu

        else

            Target.End (xlDown) .Offset (1, 0) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

I, finalment, aquest codi s'utilitza per escriure en una cel·la.

Full de treball secundari privat_Canvi (ByVal Target As Range)

    On Error Resume Next

    Si no es creua (Target, Range(«C2:C5»)) No és res i Target.Cells.Count = 1 Aleshores

        Application.EnableEvents = Fals

        newVal = Objectiu

        Aplicació.Desfer

        oldval = Objectiu

        Si Len (oldval) <> 0 I oldval <> newVal Aleshores

            Target = Target & «,» & newVal

        else

            Destí = newVal

        End If

        Si Len (newVal) = 0 Aleshores Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Els intervals són editables.

Com fer una llista desplegable amb una cerca?

En aquest cas, heu d'utilitzar inicialment un tipus de llista diferent. S'obre la pestanya "Desenvolupador", després de la qual cal fer clic o tocar (si la pantalla és tàctil) a l'element "Insereix" - "ActiveX". Té una caixa combinada. Se us demanarà que dibuixeu aquesta llista, després de la qual s'afegirà al document.

A més, es configura mitjançant propietats, on s'especifica un interval a l'opció ListFillRange. La cel·la on es mostra el valor definit per l'usuari es configura mitjançant l'opció LinkedCell. A continuació, només cal que anoteu els primers caràcters, ja que el programa suggerirà automàticament possibles valors.

Llista desplegable amb substitució automàtica de dades

També hi ha una funció que les dades es substitueixen automàticament després d'afegir-les a l'interval. És fàcil fer això:

1. Creeu un conjunt de cel·les per a la llista futura. En el nostre cas, es tracta d'un conjunt de colors. El seleccionem.
   

   

2. A continuació, s'ha de formatar com a taula. Heu de fer clic al botó del mateix nom i seleccionar l'estil de la taula.
   

   

   

A continuació, heu de confirmar aquest rang prement el botó "D'acord".

Seleccionem la taula resultant i li donem un nom a través del camp d'entrada situat a la part superior de la columna A.

Això és tot, hi ha una taula i es pot utilitzar com a base per a una llista desplegable, per a la qual necessiteu:

1. Seleccioneu la cel·la on es troba la llista.
2. Obriu el diàleg de validació de dades.
   

   

3. Definim el tipus de dades a "Llista", i com a valors donem el nom de la taula a través del signe =.
   

   

   

Tot, la cel·la està llesta i s'hi mostren els noms dels colors, tal com ens calia originalment. Ara podeu afegir noves posicions simplement escrivint-les en una cel·la situada una mica més avall immediatament després de l'última.

Aquest és l'avantatge de la taula, que el rang augmenta automàticament quan s'afegeixen dades noves. En conseqüència, aquesta és la manera més convenient d'afegir una llista.

Com copiar la llista desplegable?

Per copiar, n'hi ha prou amb utilitzar la combinació de tecles Ctrl + C i Ctrl + V. Així, la llista desplegable es copiarà juntament amb el format. Per eliminar el format, heu d'utilitzar una enganxa especial (al menú contextual, aquesta opció apareix després de copiar la llista), on s'estableix l'opció "condicions dels valors".

Seleccioneu totes les cel·les que contenen una llista desplegable

Per dur a terme aquesta tasca, heu d'utilitzar la funció "Selecciona un grup de cel·les" al grup "Cerca i selecciona".

Després d'això, s'obrirà un quadre de diàleg, on hauríeu de seleccionar els elements "Tots" i "Aquests mateixos" al menú "Validació de dades". El primer element selecciona totes les llistes i el segon només selecciona aquelles que són semblants a determinades.