Cum să creați o listă derulantă în Excel (singurul ghid de care aveți nevoie)

O listă derulantă este o modalitate excelentă de a oferi utilizatorului o opțiune de a selecta dintr-o listă predefinită.

Poate fi folosit pentru a obține un utilizator să completeze un formular sau pentru a crea tablouri de bord interactive Excel.

Listele derulante sunt destul de frecvente pe site-uri web / aplicații și sunt foarte intuitive pentru utilizator.

Vizionați video - Crearea unei liste drop-down în Excel

În acest tutorial, veți afla cum să creați o listă derulantă în Excel (durează doar câteva secunde pentru a face acest lucru), împreună cu toate lucrurile minunate pe care le puteți face cu el.

Cum se creează o listă derulantă în Excel

În această secțiune, veți afla pașii exacți pentru a crea o listă drop-down Excel:

1. Utilizarea datelor din celule.
2. Introducerea manuală a datelor.
3. Folosind formula OFFSET.

# 1 Utilizarea datelor din celule

Să presupunem că aveți o listă de articole așa cum se arată mai jos:

Iată pașii pentru a crea o listă drop-down Excel:

1. Selectați o celulă în care doriți să creați lista derulantă.
2. Accesați Date -> Instrumente de date -> Validare date.
3. În caseta de dialog Validare date, din fila Setări, selectați Listă ca criterii de validare.
   • De îndată ce selectați Listă, apare câmpul sursă.
4. În câmpul sursă, introduceți = $ A $ 2: $ A $ 6 sau pur și simplu faceți clic în câmpul Sursă și selectați celulele folosind mouse-ul și faceți clic pe OK. Aceasta va insera o listă derulantă în celula C2.
   • Asigurați-vă că este bifată opțiunea derulantă în celulă (care este bifată implicit). Dacă această opțiune este bifată, celula nu afișează o listă derulantă, cu toate acestea, puteți introduce manual valorile în listă.

Notă: Dacă doriți să creați liste derulante în mai multe celule dintr-o dată, selectați toate celulele în care doriți să le creați și apoi urmați pașii de mai sus. Asigurați-vă că referințele de celulă sunt absolute (cum ar fi $ A $ 2) și nu relative (cum ar fi A2 sau A $ 2 sau A2 $).

# 2 Prin introducerea manuală a datelor

În exemplul de mai sus, referințele de celule sunt utilizate în câmpul Sursă. De asemenea, puteți adăuga elemente direct introducându-l manual în câmpul sursă.

De exemplu, să presupunem că doriți să afișați două opțiuni, Da și Nu, în meniul derulant dintr-o celulă. Iată cum îl puteți introduce direct în câmpul sursă de validare a datelor:

• Selectați o celulă în care doriți să creați lista derulantă (celula C2 din acest exemplu).
• Accesați Date -> Instrumente de date -> Validare date.
• În caseta de dialog Validare date, din fila Setări, selectați Listă ca criterii de validare.
  • De îndată ce selectați Listă, apare câmpul sursă.
• În câmpul sursă, introduceți Da, Nu
  • Asigurați-vă că este bifată opțiunea derulantă în celulă.
• Faceți clic pe OK.

Aceasta va crea o listă derulantă în celula selectată. Toate elementele listate în câmpul sursă, separate printr-o virgulă, sunt listate în diferite linii din meniul derulant.

Toate elementele introduse în câmpul sursă, separate printr-o virgulă, sunt afișate în linii diferite în lista derulantă.

Notă: Dacă doriți să creați liste derulante în mai multe celule dintr-o dată, selectați toate celulele în care doriți să le creați și apoi urmați pașii de mai sus.

# 3 Utilizarea formulelor Excel

În afară de selectarea din celule și introducerea manuală a datelor, puteți utiliza și o formulă în câmpul sursă pentru a crea o listă verticală Excel.

Orice formulă care returnează o listă de valori poate fi utilizată pentru a crea o listă derulantă în Excel.

De exemplu, să presupunem că aveți setul de date așa cum se arată mai jos:

Iată pașii pentru a crea o listă verticală Excel utilizând funcția OFFSET:

• Selectați o celulă în care doriți să creați lista derulantă (celula C2 din acest exemplu).
• Accesați Date -> Instrumente de date -> Validare date.
• În caseta de dialog Validare date, din fila Setări, selectați Listă ca criterii de validare.
  • De îndată ce selectați Listă, apare câmpul sursă.
• În câmpul Sursă, introduceți următoarea formulă: = OFFSET ($ A $ 2,0,0,5,5)
  • Asigurați-vă că este bifată opțiunea derulantă în celulă.
• Faceți clic pe OK.

Aceasta va crea o listă derulantă care listează toate numele fructelor (așa cum se arată mai jos).

Notă: Dacă doriți să creați o listă derulantă în mai multe celule dintr-o dată, selectați toate celulele în care doriți să o creați și apoi urmați pașii de mai sus. Asigurați-vă că referințele de celulă sunt absolute (cum ar fi $ A $ 2) și nu relative (cum ar fi A2 sau A $ 2 sau A2 $).

Cum funcționează această formulă ??

În cazul de mai sus, am folosit o funcție OFFSET pentru a crea lista derulantă. Revine o listă de articole din ra

Returnează o listă de articole din intervalul A2: A6.

Iată sintaxa funcției OFFSET: = OFFSET (referință, rânduri, cols, [înălțime], [lățime])

Este nevoie de cinci argumente, unde am specificat referința ca A2 (punctul de plecare al listei). Rândurile / colurile sunt specificate ca 0, deoarece nu dorim să compensăm celula de referință. Înălțimea este specificată ca 5, deoarece există cinci elemente în listă.

Acum, când utilizați această formulă, returnează o matrice care are lista celor cinci fructe din A2: A6. Rețineți că, dacă introduceți formula într-o celulă, selectați-o și apăsați F9, veți vedea că returnează o serie de nume de fructe.

Crearea unei liste dinamice derulante în Excel (folosind OFFSET)

Tehnica de mai sus a utilizării unei formule pentru a crea o listă verticală poate fi extinsă pentru a crea și o listă verticală dinamică. Dacă utilizați funcția OFFSET, așa cum se arată mai sus, chiar dacă adăugați mai multe elemente la listă, meniul derulant nu se va actualiza automat. Va trebui să îl actualizați manual de fiecare dată când schimbați lista.

Iată o modalitate de a-l face dinamic (și nu este altceva decât o modificare minoră în formulă):

• Selectați o celulă în care doriți să creați lista derulantă (celula C2 din acest exemplu).
• Accesați Date -> Instrumente de date -> Validare date.
• În caseta de dialog Validare date, din fila Setări, selectați Listă ca criterii de validare. De îndată ce selectați Listă, apare câmpul sursă.
• În câmpul sursă, introduceți următoarea formulă: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Asigurați-vă că este bifată opțiunea derulantă în celulă.
• Faceți clic pe OK.

În această formulă, am înlocuit argumentul 5 cu COUNTIF ($ A $ 2: $ A $ 100, ””).

Funcția COUNTIF numără celulele care nu sunt goale în intervalul A2: A100. Prin urmare, funcția OFFSET se reglează pentru a include toate celulele care nu sunt goale.

Notă:

• Pentru ca acest lucru să funcționeze, NU trebuie să existe celule goale între celulele care sunt umplute.
• Dacă doriți să creați o listă derulantă în mai multe celule dintr-o dată, selectați toate celulele în care doriți să o creați și apoi urmați pașii de mai sus. Asigurați-vă că referințele de celulă sunt absolute (cum ar fi $ A $ 2) și nu relative (cum ar fi A2 sau A $ 2 sau A2 $).

Copiați lipirea listelor drop-down în Excel

Puteți copia lipiți celulele cu validarea datelor în alte celule și va copia și validarea datelor.

De exemplu, dacă aveți o listă derulantă în celula C2 și doriți să o aplicați și la C3: C6, copiați celula C2 și lipiți-o în C3: C6. Aceasta va copia lista derulantă și o va face disponibilă în C3: C6 (împreună cu meniul derulant, va copia și formatarea).

Dacă doriți doar să copiați meniul derulant și nu formatarea, iată pașii:

• Copiați celula care are meniul derulant.
• Selectați celulele în care doriți să copiați meniul derulant.
• Accesați Acasă -> Lipire -> Lipire specială.
• În caseta de dialog Lipire specială, selectați Validare în opțiuni Lipire.
• Faceți clic pe OK.

Aceasta va copia doar meniul derulant și nu formatarea celulei copiate.

Atenție când lucrați cu lista verticală Excel

Trebuie să fii atent atunci când lucrezi cu liste derulante în Excel.

Când copiați o celulă (care nu conține o listă derulantă) peste o celulă care conține o listă derulantă, lista derulantă se pierde.

Cea mai gravă parte a acestui lucru este că Excel nu va afișa nicio alertă sau prompt pentru a anunța utilizatorul că o listă verticală va fi suprascrisă.

Cum să selectați toate celulele care au o listă derulantă

Uneori, este greu să știți ce celule conțin lista derulantă.

Prin urmare, este logic să marcați aceste celule fie oferindu-i o margine distinctă, fie o culoare de fundal.

În loc să verificați manual toate celulele, există o modalitate rapidă de a selecta toate celulele care au liste derulante (sau orice regulă de validare a datelor).

• Accesați Acasă -> Găsiți și selectați -> Accesați Special.
• În caseta de dialog Accesați special, selectați Validarea datelor
  • Validarea datelor are două opțiuni: Toate și Același lucru. Toate ar selecta toate celulele care au aplicată o regulă de validare a datelor. Același lucru ar selecta doar acele celule care au aceeași regulă de validare a datelor ca cea a celulei active.
• Faceți clic pe OK.

Aceasta ar selecta instantaneu toate celulele care au aplicată o regulă de validare a datelor (aceasta include și listele derulante).

Acum puteți pur și simplu formata celulele (dați o margine sau o culoare de fundal) astfel încât să fie vizibil vizual și să nu copiați accidental o altă celulă pe ea.

Iată o altă tehnică de Jon Acampora pe care o puteți utiliza pentru a păstra întotdeauna vizibilă pictograma săgeată în jos. Puteți vedea, de asemenea, câteva modalități de a face acest lucru în acest videoclip de Mr. Excel.

Crearea unei liste derulante Excel dependente / condiționate

Iată un videoclip despre cum să creați o listă verticală dependentă în Excel.

Dacă preferați să citiți decât să vizionați un videoclip, continuați să citiți.

Uneori, este posibil să aveți mai multe liste drop-down și doriți ca elementele afișate în al doilea drop-down să depindă de ceea ce a selectat utilizatorul în primul drop-down.

Acestea se numesc liste derulante dependente sau condiționate.

Mai jos este un exemplu de listă derulantă condițională / dependentă:

În exemplul de mai sus, când elementele listate în „Drop Down 2” depind de selecția făcută în „Drop Down 1”.

Acum să vedem cum să creăm acest lucru.

Iată pașii pentru a crea o listă verticală dependentă / condiționată în Excel:

• Selectați celula în care doriți prima listă derulantă (principală).
• Accesați Date -> Validare date. Aceasta va deschide caseta de dialog pentru validarea datelor.
• În caseta de dialog pentru validarea datelor, în fila Setări, selectați Listă.
• În câmpul Sursă, specificați intervalul care conține elementele care urmează să fie afișate în prima listă derulantă.
• Faceți clic pe OK. Aceasta va crea Drop Down 1.
• Selectați întregul set de date (A1: B6 în acest exemplu).
• Accesați Formule -> Nume definite -> Creați din selecție (sau puteți utiliza comanda rapidă de la tastatură Control + Shift + F3).
• În caseta de dialog „Creați un nume din selecție”, bifați opțiunea Rând superior și debifați toate celelalte. Procedând astfel, se creează 2 intervale de nume („Fructe” și „Legume”). Gama cu fructe denumită se referă la toate fructele din listă și Gama cu legume denumită se referă la toate legumele din listă.
• Faceți clic pe OK.
• Selectați celula în care doriți lista drop-down Dependent / Conditional (E3 în acest exemplu).
• Accesați Date -> Validare date.
• În caseta de dialog Validare date, din fila de setări, asigurați-vă că Lista este selectată.
• În câmpul Sursă, introduceți formula = INDIRECT (D3). Aici, D3 este celula care conține meniul derulant principal.
• Faceți clic pe OK.

Acum, când faceți selecția în meniul drop down 1, opțiunile listate în lista drop down 2 se vor actualiza automat.

Descărcați fișierul de exemplu

Cum funcționează asta? - Lista derulantă condițională (în celula E3) se referă la = INDIRECT (D3). Aceasta înseamnă că, atunci când selectați „Fructe” în celula D3, lista derulantă din E3 se referă la intervalul numit „Fructe” (prin funcția INDIRECT) și, prin urmare, listează toate articolele din acea categorie.

Notă importantă în timp ce lucrați cu liste drop-down condiționate în Excel:

• Când ați făcut selecția și apoi modificați meniul derulant părinte, meniul derulant dependent nu s-ar schimba și, prin urmare, ar fi o intrare greșită. De exemplu, dacă selectați SUA ca țară și apoi selectați Florida ca stat și apoi vă întoarceți și schimbați țara în India, statul va rămâne ca Florida. Iată un tutorial excelent realizat de Debra despre ștergerea listelor drop-down dependente (condiționate) în Excel atunci când selecția este modificată.
• Dacă categoria principală este mai mult de un cuvânt (de exemplu, „Fructe sezoniere” în loc de „Fructe”), atunci trebuie să utilizați formula = INDIRECT (SUBSTITUT (D3, ”„, ”_”)), în loc de funcție simplă INDIRECT prezentată mai sus. Motivul pentru aceasta este că Excel nu permite spații în intervale denumite. Deci, atunci când creați un interval numit folosind mai mult de un cuvânt, Excel introduce automat o subliniere între cuvinte. Așadar, gama „Fructe sezoniere” numită „Fructe sezoniere”. Utilizarea funcției SUBSTITUTE în cadrul funcției INDIRECT asigură spațiile sunt convertite în subliniere.