Selectați din listă dacă. Cum să faci o listă derulantă complexă în excel

Când completați un tabel în Excel, datele sunt adesea repetate. De exemplu, scrieți numele produsului sau numele complet al angajatului. Astăzi vom vorbi despre cum să faceți o listă derulantă în Excel, astfel încât să nu introduceți în mod constant același lucru, ci pur și simplu să selectați valoarea dorită.

Există cel mai simplu mod de a face față sarcinii. Faceți clic dreapta pe celula de sub coloana de date pentru a deschide meniul contextual. Aici căutăm elementul „Selectați din lista derulantă”. Aceeași acțiune poate fi efectuată cu ușurință folosind combinația Alt + săgeată în jos.

Dar această metodă nu va funcționa dacă trebuie să faci un astfel de obiect în Excel într-o altă celulă și în 2-3 etc. înainte și după. Dacă este necesar, utilizați următoarea opțiune.

Mod tradițional

Selectați zona celulelor din care veți crea o listă derulantă, apoi continuați:

Inserați/Nume/Atribuiți (Excel 2003)

În cele mai recente versiuni (2007, 2010, 2013, 2016) accesați:

Formule/Nume definite/Manager de nume/Nou

Introdu orice nume și atinge OK.

Apoi selectați celulele (sau mai multe) în care intenționați să inserați lista câmpurilor specificate. Accesați meniul:

Date/Tip de date/Lista

În secțiunea „Sursă”, introduceți numele scris anterior, pur și simplu marcați intervalul. Puteți copia celula rezultată în orice loc, aceasta va conține deja un meniu cu elementele tabelului specificate.

În plus, îl puteți întinde pentru a crea o gamă. Apropo, dacă informațiile din ea se schimbă, informațiile din listă se vor schimba și ele;

Cum se face o listă derulantă în Excel: folosind metode de gestionare

Când utilizați această opțiune, inserați un control care reprezintă intervalul de date în sine. Pentru a face acest lucru:

• in primul rand cauta sectiunea “Developer” (Excel 2007/2010), in alte editii o activezi singur prin parametrii “Customize Ribbon”;

• în al doilea rând, accesați secțiunea, faceți clic pe „Inserați”;
• în al treilea rând, selectați „Câmp...” și faceți clic pe pictogramă;
• desenați un dreptunghi;

• faceți clic dreapta pe el și faceți clic pe Format Object;

• căutați „Formular...”, selectați limitele necesare;
• marcați zona în care doriți să setați numărul de serie al elementului din listă;
• Faceți clic pe OK.

Cum se creează un meniu derulant în Excel: folosind elemente ActiveX

Pașii sunt similari cu cei descriși mai sus, dar căutăm „Câmp cu ActiveX”.

Principalele diferențe aici sunt că elementul special ActiveX poate fi în 2 variante - modul de depanare, care vă permite să schimbați parametrii și modul de intrare, care vă permite doar să selectați informații din acesta. Puteți schimba modul folosind tasta Modul de proiectare in sectiune Dezvoltator. Folosind această metodă, puteți personaliza culoarea, fontul și puteți efectua căutări rapide.

Caracteristici suplimentare

Excel vă permite să creați o listă derulantă legată. Adică, atunci când selectați o valoare, puteți selecta parametrii de care are nevoie într-o altă coloană. De exemplu, alegeți un produs și trebuie să marcați unitatea de măsură, de exemplu, gram, kilogram.

În primul rând, ar trebui să faceți un semn cu listele în sine și apoi ferestre separate cu numele produselor.

Pentru a doua, activăm fereastra de verificare a informațiilor, dar în elementul „Sursă” scriem „=INDIRECT” și adresa primei celule. Totul a mers.

După aceasta, pentru ca ferestrele de mai jos să fie completate cu aceleași proprietăți, selectați secțiunea de sus și trageți totul în jos cu mouse-ul apăsat. Gata.

Cum se face o listă derulantă în Excel? După cum puteți vedea, acest lucru este ușor de realizat folosind oricare dintre metodele de mai sus. Doar alege-l pe cel mai optim pentru tine. Și alegerea va depinde de scopul creării, scopul, zona de utilizare, cantitatea de informații și alte lucruri.

Listele derulante din Microsoft Excel sunt indispensabile atunci când se creează tabele mari și se lucrează cu baze de date. Care este mai exact confortul acestui instrument?
Dacă, la completarea unui tabel, unele date sunt repetate periodic, nu este nevoie să introduceți manual o valoare constantă de fiecare dată - de exemplu, numele produsului, luna, numele complet al angajatului. Este suficient să fixați o dată un parametru care se repetă în listă.
Celulele din listă sunt protejate împotriva introducerii de valori străine, ceea ce reduce probabilitatea de a face o eroare în funcționare.
O masă astfel concepută arată îngrijită.
În articol vă spun cum să faci o listă derulantă într-o celulă în Excel e și cum să lucrezi cu el.

Formarea unei liste derulante

Unul dintre exemplele comune de utilizare a listelor derulante sunt magazinele online în care toate produsele sunt împărțite în categorii - această structură facilitează căutarea utilizatorilor pe site.
Să aruncăm o privire mai atentă:

Toate produsele enumerate în tabel trebuie să fie clasificate în categoria „Îmbrăcăminte”. Pentru a crea o listă derulantă pentru această listă, va trebui să faceți următoarele:
Selectați orice celulă în care va fi creată lista.
Accesați fila „Date”, în secțiunea „Verificarea datelor”.
În fereastra care se deschide, selectați fila „Parametri”, iar în lista „Tip de date”, selectați opțiunea „Lista”.
În rândul care apare, trebuie să indicați toate numele listelor disponibile. Acest lucru se poate face în două moduri: selectați un interval de date din tabel cu mouse-ul (în exemplu, celulele A1-A7) sau introduceți numele manual, separate prin punct și virgulă.
Selectați toate celulele cu valorile dorite și faceți clic dreapta și selectați „Atribuiți un nume” din meniul contextual.
În linia „Nume”, indicați numele listei – în acest caz, „Îmbrăcăminte”.
Selectați celula în care a fost creată lista și introduceți numele creat în linia „Sursă” cu semnul „=" la început.
Rezultatul final arată așa. Aceasta este cea mai simplă opțiune pentru o listă derulantă. În funcție de versiunea de Excel, pot fi mai mulți sau mai puțini pași, dar, în general, instrucțiunile sunt universale pentru orice program.

Cum să adăugați valori la o listă

Uneori devine necesară completarea unei liste existente. În lista derulantă, toate elementele noi sunt afișate automat când sunt adăugate. Cu toate acestea, pentru a asocia o serie de celule cu un element nou adăugat, lista trebuie formatată ca tabel. Pentru a face acest lucru, trebuie să selectați o gamă de valori, să găsiți elementul „Format ca tabel” din fila „Acasă” și să selectați orice stil care vă place, de exemplu.

Lucrul în Excel cu tabele și datele din ele este structurat în așa fel încât utilizatorul să le poată procesa și analiza confortabil. În acest scop, în program sunt integrate diverse instrumente. Utilizarea lor necesită ca utilizatorul să aibă anumite cunoștințe, dar odată cu ele Excel se transformă într-un instrument puternic de analiză. Dezvoltatorul Office încearcă să simplifice majoritatea programelor sale, astfel încât oricine să le poată utiliza pe deplin.

O foaie de calcul poate fi transformată într-un instrument de analiză a datelor

Unul dintre cele mai comune motive pentru crearea unei liste pop-up este utilizarea datelor dintr-o celulă într-o formulă Excel. Este mai ușor să oferiți un număr finit de opțiuni, așa că ar fi indicat să alegeți mai multe valori, astfel încât utilizatorul să poată alege dintr-un set gata făcut. În plus, poate exista un alt motiv: un stil de document predeterminat. De exemplu, pentru rapoarte sau alte documente oficiale. Același nume de departament poate fi scris în moduri diferite. Dacă acest document va fi ulterior procesat de o mașină, ar fi mai corect să folosiți un stil de umplere uniform, decât să-i atribuiți recunoașterea, de exemplu, prin cuvinte cheie. Acest lucru poate introduce un element de inexactitate în munca ei.

Partea tehnică a problemei

Înainte de a crea o listă derulantă în Excel, creați opțiunile necesare pe foaie într-o serie de celule. Asigurați-vă că nu există linii goale în această listă, altfel Excel nu va putea crea obiectul dorit pe foaie. Valorile introduse în rânduri pot fi sortate alfabetic. Pentru a face acest lucru, găsiți fila de date în Panglica Setări și faceți clic pe „Sortare”. Când ați terminat de lucrat cu datele, selectați intervalul dorit. Nu trebuie să conțină linii goale, acest lucru este important! Programul nu va putea crea o listă cu un element gol în interior, deoarece șirul gol nu va fi acceptat ca date de selecție. În acest caz, puteți crea o listă de date pe o altă foaie, nu numai pe cea în care va fi amplasat câmpul de introducere. Să presupunem că nu doriți ca acestea să fie editabile de alți utilizatori. Apoi are sens să le așezi pe o foaie ascunsă.

După ce ați generat o listă de date, selectați celula în care ar trebui să fie creată lista derulantă. În Panglica Setări Excel, în fila „Date”, găsiți butonul „Validare”. Când faceți clic pe el, se va deschide o casetă de dialog. În el, trebuie să selectați elementul „Permite” și să setați valoarea acestuia la „List”. Deci, în această celulă, metoda de introducere va fi modificată pentru a selecta dintre opțiunile disponibile. Dar aceste opțiuni nu au fost încă stabilite. Pentru a le adăuga la obiectul creat, introduceți intervalul de date în câmpul „Sursă”. Pentru a nu le introduce manual, faceți clic pe pictograma enter din partea dreaptă a câmpului, apoi fereastra se va restrânge și puteți selecta celulele dorite folosind selecția obișnuită a mouse-ului. De îndată ce eliberați butonul stâng al mouse-ului, fereastra se va deschide din nou. Tot ce trebuie să faceți este să faceți clic pe OK și un triunghi, o pictogramă de listă derulantă, va apărea în celula selectată. Făcând clic pe el, veți primi o listă cu opțiunile pe care le-ați introdus mai devreme. După aceasta, dacă opțiunile sunt situate pe o foaie separată, o puteți ascunde făcând clic dreapta pe numele său în partea de jos a ferestrei de lucru și selectând elementul cu același nume în meniul contextual.

Când selectați această celulă, mai multe butoane vor apărea lângă ea. Pentru a ușura sarcina de introducere pentru utilizator, puteți utiliza acest buton pentru a specifica numele celulei. Puteți face același lucru mai sus, lângă fereastra de introducere a formulei, există un element corespunzător. În acest fel lista va fi mai clară, deoarece utilizatorul nu va trebui să ghicească din valorile sale ce anume trebuie selectat aici. În plus, în caseta de dialog puteți introduce un mesaj indiciu care va fi afișat atunci când treceți cursorul peste o celulă. Dacă nu doriți ca celula să rămână goală, debifați „Ignorați valorile goale”. Caseta de selectare „Lista de valori acceptate” trebuie bifată în orice caz.

Ștergeți o listă

Când nu mai este necesară o listă derulantă, aceasta poate fi eliminată din document. Pentru a face acest lucru, selectați celula din foaia Excel care o conține și mergeți la Panglica Setări la fila „Date” - „Validare datelor”. Acolo, în fila Opțiuni, faceți clic pe butonul „Ștergeți totul”. Obiectul va fi șters, dar intervalul de date va rămâne neschimbat, adică valorile nu vor fi șterse.

Listele drop-down fac introducerea datelor în foile de calcul ușor și convenabil. Doar faceți clic pe săgeată și selectați o opțiune. Puteți adăuga liste derulante la celulele Excel care conțin opțiuni precum Da și Nu, Bărbați și Femei sau orice altă listă personalizată de opțiuni.

Adăugați o listă derulantă la o celulă în Excel destul de simplu, dar procesul nu este intuitiv. Listele derulante sunt create folosind funcția de validare a datelor. Vom crea o listă derulantă cu un set de intervale de vârstă pentru a vă arăta cum se face.

Pentru a începe, introduceți o listă de intervale de vârstă în celule secvențiale după coloană sau rând. Am introdus intervalele noastre de vârstă în celulele A9 la A13 pe aceeași foaie de lucru, așa cum se arată mai jos. De asemenea, puteți adăuga lista de opțiuni într-o altă foaie de lucru din același registru de lucru.

Acum vom denumi gama noastră de celule pentru a le ușura adăugarea în lista drop-down. Pentru a face acest lucru, selectați toate celulele care conțin elemente din lista derulantă, apoi introduceți un nume pentru intervalul de celule în caseta Nume de deasupra grilei. Ne-am numit gama Vârstă.

Acum selectați celula în care doriți să adăugați lista verticală și accesați fila Date.

Sub fila Instrumente de date Date Faceți clic pe butonul Validare date.

Se deschide caseta de dialog Validare date. În fila Opțiuni, selectați Listă din lista drop-down Tip de date.

Vom folosi acum Numele pe care l-am atribuit intervalului de celule care conține opțiunile din lista derulantă. Intră =Vârstaîn câmpul „Sursă” (dacă ați denumit gama de celule altceva, înlocuiți „Vârsta” cu acel nume). Asigurați-vă că caseta de selectare Ignorați celulele goale marcat.

Puteți adăuga un mesaj pop-up care se afișează atunci când este selectată o celulă dintr-o listă derulantă. Pentru a face acest lucru, faceți clic pe fila Mesaj de introdusîn caseta de dialog Validare date. Asigurați-vă că caseta de selectare Afișați sfatul explicativ dacă celula este curentă instalat. Introduceți un mesaj în câmpurile Titlu și Mesaj, apoi faceți clic pe OK.

Când este selectată celula care conține lista derulantă, veți vedea un buton săgeată în jos în partea dreaptă a celulei. Dacă ați adăugat un mesaj de intrare, acesta va apărea sub celulă. Butonul săgeată în jos apare numai atunci când este selectată o celulă.

Faceți clic pe butonul săgeată în jos pentru a afișa o listă de opțiuni și selectați una.

Dacă decideți că doriți să eliminați lista derulantă dintr-o celulă, deschideți caseta de dialog Validare date așa cum este descris mai sus și faceți clic pe butonul Șterge tot, care este disponibil indiferent de fila selectată în caseta de dialog.

Opțiunile din caseta de dialog Validare date sunt resetate la valorile implicite. Faceți clic pe OK pentru a elimina meniul derulant și a restabili celula la valoarea implicită.

Dacă a fost selectată o opțiune, atunci când lista derulantă este ștearsă, celula va fi completată cu valoarea acelei opțiuni.

Urmați acest proces pentru a adăuga liste derulante la alte celule după cum este necesar. Puteți utiliza aceeași listă pentru mai multe liste derulante.

Dacă aveți multe liste derulante pe care trebuie să le adăugați la o foaie de lucru, vă recomandăm să plasați listele de opțiuni într-o foaie de lucru Excel separată și să ascundeți foaia de lucru pentru a preveni modificările opțiunilor.

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 dintr-o listă derulantă Excel se face folosind 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 înlocuit în listă, atunci nu vor avea loc modificări în aceasta până când noile adrese sunt specificate manual. Pentru a conecta un interval și un 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

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ă - înlocuirea datelor dintr-o listă derulantă într-un tabel 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

Puteți utiliza un interval de date dinamice 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. Intervalul 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 multiplă

Adesea trebuie să obținem mai multe valori dintr-un set de date. Le puteți afișa în celule diferite sau le puteți combina într-una 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. Trebuie să introduceți următorul algoritm în el.

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.

Reveniți la foaia de lucru 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 arătat cum să adăugați și să modificați o listă derulantă într-o celulă Excel. Sperăm că aceste informații vă vor ajuta.

O zi bună!