Crearea unei liste derulante în Excel. Cum să faci o listă derulantă cu date adăugate

Pentru ca utilizatorii să lucreze mai ușor cu o foaie de lucru, adăugați liste derulante la celulele din care pot selecta element necesar.

Pe o nouă foaie de lucru, introduceți datele care doriți să apară în lista derulantă. Este de dorit ca elementele din listă să fie conținute într-un tabel Excel. Dacă nu este cazul, puteți converti rapid lista într-un tabel selectând orice celulă din interval și apăsând CTRL+T.

Note:

Selectați celula din foaia de lucru în care doriți să plasați lista verticală.

Pe panglică, deschideți fila Dateși apăsați butonul Verificarea datelor.

Notă: Dacă butonul Verificarea datelor indisponibilă, foaia poate fi protejată sau partajată. Deblocați anumite zone ale unui registru de lucru protejat sau deblocați acces general pe foaie, apoi repetați pasul 3.

Pe fila Opțiuniîn câmp Tip de date selectați elementul Listă.

Faceți clic pe câmp Sursăși evidențiați intervalul listei. În exemplu, datele se află pe foaia Orașe în intervalul A2:A9. Rețineți că rândul antetului nu se află în interval, deoarece nu este una dintre opțiunile disponibile pentru selecție.

Dacă puteți lăsa celula goală, bifați caseta de selectare Ignorați celulele goale.

Bifeaza casuta Listă valori acceptabile

Deschide fila Mesaj de introdus.
Deschide fila Mesaj de eroare.
Nu știu ce opțiune să selectez în câmp Vedere?

Lucrul cu o listă derulantă

După ce ați creat meniul drop-down, asigurați-vă că funcționează așa cum doriți. De exemplu, poate doriți să verificați dacă lățimile coloanelor și înălțimile rândurilor trebuie modificate, astfel încât să fie afișate toate înregistrările.

Descărcarea exemplelor

Vă sugerăm să descărcați un exemplu de carte cu mai multe exemple de verificare a datelor, similar cu exemplul din acest articol. Puteți să le utilizați sau să vă creați propriile scripturi de verificare a datelor. Descărcați exemple de validare a datelor Excel

Puteți introduce datele mai rapid și mai precis limitând valorile dintr-o celulă la opțiuni dintr-o listă derulantă.

Mai întâi, creați o listă de elemente valide pe foaia de lucru, apoi sortați sau aranjați-le În ordinea corectă. Aceste elemente pot servi ulterior ca sursă pentru o listă derulantă de date. Dacă lista este mică, o puteți face referire cu ușurință și puteți introduce articole direct în verificatorul de date.

Elementul listă ne este familiar din formularele de pe site-uri web. Este convenabil să selectați valori gata făcute. De exemplu, nimeni nu introduce luna manual, aceasta este luată dintr-o astfel de listă. Puteți completa o listă derulantă în Excel folosind diverse instrumente. În acest articol ne vom uita la fiecare dintre ele.

Cum să faci o listă derulantă în Excel

Cum se face o listă derulantă în Excel 2010 sau 2016 folosind o comandă din bara de instrumente? În fila „Date”, în secțiunea „Lucrul cu datele”, găsiți butonul „Validare a datelor”. Faceți clic pe el și selectați primul element.

Se va deschide o fereastră. În fila „Opțiuni”, în secțiunea drop-down „Tip de date”, selectați „Lista”.

O linie va apărea în partea de jos pentru a indica sursele.

Puteți furniza informații în diferite moduri.

Mai întâi să atribuim un nume. Pentru a face acest lucru, creați un astfel de tabel pe orice foaie.

Selectați-l și faceți clic dreapta. Faceți clic pe comanda „Atribuiți un nume”.

Introduceți numele dvs. în rândul de mai sus.

Apelați fereastra „Verificarea datelor” și în câmpul „Sursă”, specificați numele punând semnul „=” în fața acestuia.

În oricare dintre cele trei cazuri veți vedea elementul dorit. Selectarea unei valori din meniul drop-down Lista Excel se întâmplă cu mouse-ul. Faceți clic pe el și va apărea o listă cu datele specificate.

Ați învățat cum să creați o listă derulantă într-o celulă Excel. Dar se pot face mai multe.

Înlocuirea dinamică a datelor Excel

Dacă adăugați o anumită valoare intervalului de date care este substituit în listă, atunci nu vor avea loc modificări până când noile adrese nu sunt specificate manual. Pentru a asocia o gamă și element activ, trebuie să îl formatați pe primul ca tabel. Creați o matrice ca aceasta.

Selectați-l și în fila „Acasă”, selectați orice stil de tabel.

Asigurați-vă că bifați caseta de mai jos.

Veți primi acest design.

Creați un element activ așa cum este descris mai sus. Pentru sursă, introduceți formula

=INDIRECT(„Tabel1[Orașe]”)

Pentru a afla numele tabelului, accesați fila Design și priviți-l. Puteți schimba numele cu oricare altul.

Funcția INDIRECT creează o referință la o celulă sau un interval. Acum elementul dvs. din celulă este legat de matricea de date.

Să încercăm să creștem numărul de orașe.

Procedura inversă este de a înlocui datele dintr-o listă derulantă în Foaie de calcul Excel, funcționează foarte simplu. În celula în care doriți să inserați valoarea selectată din tabel, introduceți formula:

Adresă_celulă

De exemplu, dacă lista de date se află în celula D1, atunci în celula în care vor fi afișate rezultatele selectate, introduceți formula

Cum să eliminați (ștergeți) o listă derulantă în Excel

Deschideți fereastra de setări a listei derulante și selectați „Orice valoare” în secțiunea „Tip de date”.

Elementul inutil va dispărea.

Elemente dependente

Uneori, în Excel este nevoie de a crea mai multe liste atunci când una depinde de cealaltă. De exemplu, fiecare oraș are mai multe adrese. La selectarea primei, ar trebui să primim doar adresele localității selectate.

În acest caz, dați un nume fiecărei coloane. Selectați fără prima celulă (titlu) și faceți clic dreapta. Selectați „Nume”.

Acesta va fi numele orașului.

Când numiți Sankt Petersburg și Nijni Novgorod, veți primi o eroare, deoarece numele nu poate conține spații, liniuțe de subliniere, caractere speciale etc.

Prin urmare, vom redenumi aceste orașe cu un caracter de subliniere.

Creăm primul element din celula A9 în mod obișnuit.

Și în al doilea scriem formula:

INDIRECT(A9)

Mai întâi veți vedea un mesaj de eroare. De acord.

Problema este că nu există o valoare selectată. De îndată ce un oraș este selectat în prima listă, al doilea va funcționa.

Cum să configurați liste derulante dependente în Excel cu căutare

Poate fi folosit interval dinamic date pentru al doilea element. Acest lucru este mai convenabil dacă numărul de adrese crește.
Să creăm o listă derulantă de orașe. Gama numită este evidențiată în portocaliu.

Pentru a doua listă trebuie să introduceți formula:

OFFSET($A$1,MATCH($E$6,$A:$A,0)-1,1,COUNTIF($A:$A,$E$6),1)

MATCH returnează numărul celulei cu orașul selectat în prima listă (E6) în zona specificată SA:$A.
COUNTIF contorizează numărul de potriviri dintr-un interval cu valoarea din celula specificată (E6).

Avem liste derulante legate în Excel cu o condiție de potrivire și o căutare în interval pentru aceasta.

Selectare multipla

Adesea trebuie să obținem mai multe valori dintr-un set de date. Le puteți afișa în celule diferite, sau poate fi combinat într-unul singur. În orice caz, este nevoie de o macrocomandă.
Faceți clic dreapta pe eticheta foii din partea de jos și selectați Vizualizare cod.

Se va deschide fereastra dezvoltatorului. În el trebuie introdus următorul algoritm.

Private Sub Worksheet_Change(ByVal Target As Range) La eroare Reluare Next If Not Intersect(Target, Range("C2:F2")) Este Nimic și Target.Cells.Count = 1 Apoi Application.EnableEvents = False If Len(Target.Offset (1, 0)) = 0 Apoi Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Vă rugăm să rețineți că în rând

If Not Intersect(Target, Range("E7")) este Nimic și Target.Cells.Count = 1 Atunci

Ar trebui să introduceți adresa celulei cu lista. Pentru noi va fi E7.

Intoarce-te Foaie Excelși creați o listă în celula E7.

Când este selectat, valorile vor apărea sub acesta.

Următorul cod vă va permite să acumulați valori într-o celulă.

Private Sub Worksheet_Change(ByVal Target As Range) La eroare Reluare Next If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application. Undo oldval = Target If Len(oldval)<>0 Si oldval<>newVal Then Target = Target & "," & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

De îndată ce mutați indicatorul într-o altă celulă, veți vedea o listă cu orașele selectate. Pentru a citi acest articol.

V-am spus cum să adăugați și să modificați o listă drop-down în celula Excel. Sperăm că aceste informații vă vor ajuta.

O zi bună!

Dacă completați un tabel în Excel și datele din coloană pot fi uneori repetate, de exemplu, numele unui produs sau numele unui angajat, atunci nu introduceți parametrul necesar de fiecare dată, este mai simplu și mai ușor să creați o listă derulantă și să selectați o valoare din ea.

În acest articol ne vom uita la cum să facem liste derulante tipuri variateîntr-un tabel Excel.

Creați o listă derulantă simplă

Pentru a face acest lucru, în celulele A1:A7 introducem datele care vor fi afișate în listă. Acum să selectăm celula în care vom crea o listă derulantă - B2.

Accesați fila „Date” și faceți clic pe butonul „Verificarea datelor”.

În fila „Parametri”, în câmpul „Tip de date”, selectați „Lista”. Puteți introduce valori în câmpul Sursă în diferite moduri:

1 – introduceți manual valorile pentru listă, separate prin punct și virgulă;

2 – indicați intervalul de celule în care sunt introduse datele pentru lista derulantă;

3 – selectați celulele cu nume, faceți clic dreapta pe ele și selectați „Atribuiți un nume” din meniu.

Selectați celula B2 și puneți „=” în câmpul „Sursă”, apoi scrieți numele creat.

Așa că am creat o listă derulantă simplă în Excel.

Dacă aveți un antet pentru o coloană și trebuie să completați fiecare rând cu valori, atunci selectați nu o celulă, ci un interval de celule - B2:B9. Apoi puteți selecta valoarea dorită în fiecare celulă din lista derulantă.

Adăugarea de valori la o listă derulantă - listă dinamică

În acest caz, vom adăuga valori la intervalul necesar, iar acestea vor fi adăugate automat în lista derulantă.

Selectați intervalul de celule – D1:D8, apoi faceți clic pe fila „Acasă”. „Format ca tabel”și alege orice stil.

Confirmați locația datelor și bifați caseta „Tabel cu anteturi”.

În partea de sus scriem titlul tabelului - „Angajați” și îl completăm cu date.

Selectați celula în care va fi lista verticală și faceți clic pe butonul „Verificarea datelor”. În fereastra următoare, în câmpul „Sursă”, scrieți următoarele: =INDIRECT(„Tabel 1[Angajați]”). Am un tabel pe o foaie, așa că scriu „Tabel1”, dacă există un al doilea, scriu „Tabel2” și așa mai departe.

Acum să adăugăm un nou nume de angajat la lista noastră: Ira. A apărut în lista derulantă. Dacă ștergem orice nume din tabel, acesta va fi șters și din listă.

Listă derulantă cu valori dintr-o altă foaie

Dacă tabelul cu liste derulante este pe o foaie, iar datele pentru aceste liste sunt pe alta, atunci această funcție ne va ajuta foarte mult.

Pe foaia 2, selectați o celulă sau un interval de celule, apoi faceți clic pe butonul „Verificarea datelor”.

Mergeți la Foaia 1, plasați cursorul în câmpul „Sursă” și selectați intervalul dorit de celule.

Acum puteți adăuga nume pe foaia 1, acestea vor fi adăugate la listele derulante de pe foaia 2.

Crearea de liste derulante dependente

Să presupunem că avem trei intervale: prenume, nume de familie și patronimime ale angajaților. Pentru fiecare, trebuie să atribuiți un nume. Selectăm celulele acestui interval, le puteți goli și pe cele - în timp, puteți adăuga date la ele, care vor apărea în lista derulantă. Faceți clic dreapta pe ele și selectați „Atribuiți un nume” din listă.

Pe primul îl numim „Nume”, pe al doilea – „Nume”, pe al treilea – „Tată”.

Să facem un alt interval în care vor fi scrise numele atribuite. Să-i spunem „Angajați”.

Facem prima listă derulantă, care va consta din numele intervalelor. Selectați celula E1 și pe fila „Date” selectați „Verificarea datelor”.

În câmpul „Tip de date”, selectați „List”, în câmpul sursă, fie introduceți „=Angajați”, fie selectați o serie de celule cărora i-a fost atribuit un nume.

Prima listă derulantă a fost creată. Acum, în celula F2, vom crea o a doua listă, care ar trebui să depindă de prima. Dacă în primul selectam „Nume”, în al doilea va fi afișată o listă de nume, dacă selectăm „Nume” - o listă de nume de familie.

Selectați celula și faceți clic pe butonul „Verificarea datelor”. În câmpul „Tip de date”, selectați „List” în câmpul sursă, introduceți următoarele: =INDIRECT($E$1) . Aici E1 este celula cu prima listă derulantă.

Folosind acest principiu, puteți crea liste derulante dependente.

Dacă în viitor, va trebui să introduceți valorile într-un interval căruia i se dă un nume, de exemplu, „Nume”. Accesați fila „Formule” și faceți clic „Manager de nume”. Acum selectați „Nume” în numele intervalului, iar mai jos, în loc de ultima celulă C3, scrieți C10. Faceți clic pe bifa. După aceasta, intervalul va crește și puteți adăuga date la acesta, care vor apărea automat în lista derulantă.

Acum știi cum să faci o listă derulantă în Excel.

Evaluează acest articol:

(1 evaluări, medie: 5,00 din 5)

Webmaster. Educatie inalta cu o diplomă în Securitate Informațională

Foarte des, cei care introduc niște date sau pur și simplu completează un tabel trebuie să introducă date care se repetă. Pentru a evita repetarea acelorași lucruri, Excel are un instrument pentru aceste cazuri - o listă derulantă. Să vedem cum să o facem

Să o facem pas cu pas

Cum funcționează - creezi o listă în prealabil, iar apoi în anumite câmpuri selectezi valoarea dorită din listă cu mouse-ul. Economisește timp și nervi.

Acesta este ușor de implementat:

Mai întâi, creați lista de date în sine.

Selectați câmpurile pe care intenționați să le completați cu date.

Apoi accesați fila Date, comanda Data Validation – Data Type (List) și puneți un interval în câmpul Sursă.

Dacă intenționați să adăugați date noi în listă, lăsați intervalul să includă și un anumit număr de celule sub listă.

O opțiune alternativă este să marcați numele intervalului, mai degrabă decât intervalul. În acest caz, scapi de posibilitatea de a mări lista mai mult decât ai specificat în listă. Trebuie doar să vă asigurați că noile valori pe care le introduceți în interval sunt de fapt incluse în acesta.

Bifați caseta „Ignorați celulele goale”.

Acum, când selectați o celulă dintre cele cărora li s-a aplicat comanda, în dreapta va apărea un buton săgeată.

Un avertisment este că prin această metodă puteți introduce într-o celulă NUMAI valorile conținute în listă.

Nuanțe

O listă derulantă este o proprietate a celulei. Și, prin urmare, dacă copiați o altă celulă și lipiți în cea în care există o listă derulantă, proprietățile celulei vor fi înlocuite și meniul drop-down lista va dispărea. În consecință, dacă aveți nevoie de aceeași listă derulantă într-o altă celulă, puteți pur și simplu să copiați celula și să o lipiți la adresa corectă(sau prin insert special, trebuie să copiați „condițiile la valori”).

După completarea câmpurilor, dacă nu intenționați să faceți altceva, puteți elimina funcția listă derulantă (se întâmplă ca săgeata să interfereze vizual atunci când lucrați cu celule). Acest lucru se face astfel: selectați intervalul în care lista drop-down nu mai este necesară, utilizați aceeași comandă, dar apăsați butonul „Șterge tot”. Toate datele vor fi salvate.

A doua cale

Dacă nu completați date repetate, dar doriți să faceți ceva ca un chestionar, pentru că... tabel, unde câmpurile cu liste sunt aranjate aleatoriu, atunci este mai bine să utilizați o altă metodă de a crea o listă derulantă.

Pentru a face acest lucru, accesați fila Dezvoltator (Excel 2007-2010). În mod implicit, această filă nu este afișată. Pentru a-l deschide, trebuie să accesați Opțiuni (Opțiuni Office/Excel/General/Afișați fila „Dezvoltator” de pe panglică).

Pe fila Dezvoltator există un buton Inserare unde există elemente de formular. Avem nevoie de o „Cutie Combo”.

Desenați mouse-ul în jurul conturului butonului, apoi faceți clic dreapta pe câmpul rezultat și selectați „Format Object”.

În primul câmp, selectați un interval.
În al doilea câmp poți pune o celulă unde vei pune număr de serie element. Acest lucru poate fi util atunci când procesați datele ulterior.
De asemenea, puteți pune o listă de elemente care vor fi afișate. ÎN metoda anterioară Doar 8 elemente de selecție sunt date automat, deși setarea este mai complicată aici, dar există mai multe beneficii.

Apoi faceți clic pe „Ok” și utilizați-l.

„Ochilor le este frică, dar mâinile fac”

Cel mai simplu mod de a finaliza această sarcină este următorul. Prin apăsarea butonul corect după celulă de sub coloana de date apel meniul contextual. Domeniul de interes aici este Selectați din lista derulantă. Același lucru se poate face prin apăsarea combinației de taste Alt+Săgeată în jos.

Cu toate acestea, această metodă nu va funcționa dacă doriți să creați o listă într-o altă celulă care nu este în interval și în mai multe înainte sau după. Următoarea metodă va face acest lucru.

Metoda standard

Necesar selectați o serie de celule, din care va fi creat lista verticală, apoi Introduce – Nume – Atribui(Excel 2003). În mai mult versiune noua(2007, 2010, 2013, 2016) accesați fila Formule, unde în secțiune Denumiri specifice găsiți butonul Manager de nume.

apasa butonul Crea, introduceți un nume, puteți utiliza orice nume, după care Bine.

Selectați celule(sau mai multe) unde doriți să inserați o listă derulantă de câmpuri obligatorii. Din meniu, selectați Date – Tip de date – Listă. În câmp Sursă introduceți numele creat anterior sau puteți specifica pur și simplu intervalul, care va fi echivalent.

Acum celula rezultată poate fi copie oriunde pe foaie, va conține o listă elementele necesare Mese. De asemenea, îl puteți extinde pentru a obține o gamă cu liste derulante.

Un punct interesant este că atunci când datele din interval se schimbă, lista bazată pe acestea se va schimba și ea, adică dinamic.

Folosind controalele

Metoda se bazează pe introduce control numit " caseta combinată", care va reprezenta o serie de date.

Selectați o filă Dezvoltator(pentru Excel 2007/2010), în alte versiuni va trebui să activați această filă pe panglică în parametrii – Personalizați-vă feedul.

Să mergem la această filă - apasa butonul Introduce. În comenzi, selectați Caseta combinată(nu ActiveX) și faceți clic pe pictogramă. A desena dreptunghi.

Faceți clic dreapta pe el - Format obiect.

Prin conectarea la o celulă, selectați câmpul în care doriți să plasați numărul de serie al elementului în listă. Apoi apasa Bine.

Utilizarea controalelor ActiveX

Totul, ca și în precedentul, doar selectați Caseta combinată(ActiveX).

Diferențele sunt după cum urmează: element ActiveX poate fi în două variante - modul depanare, care vă permite să schimbați parametrii și modul - intrare, puteți eșantiona doar date din acesta. Schimbarea modurilor se face folosind butonul Modul de proiectareîn filă Dezvoltator.

Spre deosebire de alte metode, acest lucru permite ton fonturi, culori și efectuați o căutare rapidă.