Cum să faci o listă derulantă complexă în excel? Excel: Selectați dintr-o listă. Lista derulantă într-o celulă de foaie Cum se inserează o selecție dintr-o listă în Excel

Una dintre cele mai utile caracteristici la introducerea datelor este capacitatea de a utiliza o listă derulantă. Vă permite să selectați o valoare dintr-o listă predefinită și vă permite să introduceți numai acele date care corespund cerințelor dumneavoastră. Vă vom oferi mai multe modalități simple de a crea liste derulante în Excel. De asemenea, vom analiza metode mai complexe bazate pe intervale dinamice și folosind date din alte tabele.

Cum ne poate fi util acest lucru?

Se întâmplă adesea că trebuie să introduceți valori identice care se repetă într-una dintre coloanele tabelului dvs. De exemplu, numele angajaților, numele produselor. Ce se poate întâmpla? Desigur, în primul rând vor apărea erori de tastare. Factorul uman nu a fost anulat. Cu ce ne amenință asta? De exemplu, când decidem să numărăm câte comenzi a finalizat fiecare manager, se dovedește că sunt mai multe nume decât angajați. În continuare, va trebui să căutați erori, să le corectați și să repetați calculul din nou.

Și, desigur, să tastezi aceleași cuvinte de mână tot timpul este doar o muncă inutilă și o pierdere de timp. Aici sunt utile listele derulante. Când faceți clic, apare o listă de valori predefinite, dintre care trebuie specificată doar una.

Important este că acum nu vei intra, ci le vei selecta folosind mouse-ul sau tastatura. Acest lucru accelerează semnificativ munca și, de asemenea, garantează protecție împotriva erorilor accidentale. Verificarea a ceea ce am introdus în tabel nu mai este necesară.

1 - Cea mai rapidă cale.

Care este cel mai simplu mod de a adăuga o listă drop-down? Doar un clic dreapta pe celula goală de sub coloana de date, apoi comanda meniului contextual „Alege din lista derulantă”. Sau puteți sta în locul potrivit și apăsați combinația de taste Alt+săgeată în jos. Apare o listă sortată de valori unice introduse anterior.
Metoda nu funcționează dacă celula și coloana noastră cu înregistrări sunt separate de cel puțin o linie goală sau doriți să introduceți ceva care nu a fost încă introdus mai sus. Acest lucru este clar vizibil în exemplul nostru.

2 - Utilizați meniul.

Să ne uităm la un mic exemplu în care trebuie să introducem în mod constant aceleași nume de produse în tabel. Notați într-o coloană datele pe care le vom folosi (de exemplu, numele produselor). În exemplul nostru - în intervalul G2:G7.

Selectați o celulă de tabel (puteți avea mai multe simultan) în care doriți să utilizați intrarea dintr-o listă predeterminată. Apoi, în meniul principal, selectați fila Date – Validare. Apoi, faceți clic pe Tip de date (Permite) și selectați opțiunea Listă. Plasați cursorul în câmpul Sursă și introduceți în el adresele cu valorile de referință ale elementelor - în cazul nostru G2:G7. De asemenea, este recomandat să folosiți linkuri absolute aici (apăsați F4 pentru a le seta).

Un bonus aici este posibilitatea de a seta un indiciu și un mesaj de eroare dacă doriți să modificați manual valoarea introdusă automat. Pentru a face acest lucru, există file Mesaj de intrare și Alertă de eroare.

De asemenea, puteți utiliza un interval numit ca sursă.

De exemplu, intervalul I2:I13, care conține numele lunilor, poate primi numele „luni”. Numele poate fi apoi introdus în câmpul Sursă.

În plus, atât sursa ca un interval obișnuit de celule, cât și intervalul numit pot fi pe alte foi din registrul de lucru.

Dar nu trebuie să utilizați intervale sau link-uri, ci pur și simplu definiți posibilele opțiuni direct în câmpul „Sursă”. De exemplu, scrie acolo -

Utilizați un punct și virgulă, virgulă sau alt caracter pe care l-ați setat ca separator de elemente pentru a separa valorile. (Vedea Panou de control - Ceas și regiune - Formate - Avansat - Numere.)

3 - Creați un element de control.

Să inserăm un nou obiect în foaie - un element de control „Combo Box” și apoi să-l legăm cu datele de pe foaia Excel. Noi facem:

Deschideți fila Dezvoltator. Dacă nu este vizibil, atunci în Excel 2007 trebuie să faceți clic pe butonul Office – Opțiuni – caseta de selectare Afișați fila Dezvoltator în Panglică (Butonul Office – Opțiuni – Afișați fila Dezvoltator în Panglică) sau în versiunile 2010–2013 faceți clic dreapta pe panglică, selectați comanda Customize Ribbon și activați afișarea filei Dezvoltator folosind caseta de selectare.
Găsiți pictograma dorită printre comenzi (vezi imaginea de mai jos).

După ce ați inserat controlul în foaia de lucru, faceți clic dreapta pe el și selectați „Format Object” din meniul care apare. În continuare, indicăm intervalul de celule în care sunt înregistrate valorile valide pentru intrare. În câmpul „Link to cell” indicăm exact unde să plasăm rezultatul. Este important să luăm în considerare faptul că acest rezultat nu va fi valoarea în sine din intervalul pe care l-am specificat, ci doar numărul său de serie.

Dar nu avem nevoie de acest număr, ci de cuvântul corespunzător. Folosim funcția INDEX (INDEX în engleză). Vă permite să găsiți unul dintre ele în lista de valori în funcție de numărul său de serie. Ca argumente INDEX, specificați intervalul de celule (F5:F11) și adresa cu numărul de secvență rezultat (F2).

Scriem formula în F3 așa cum se arată în figură:

INDEX(F5:F11,F2)

Ca și în metoda anterioară, aici sunt posibile link-uri către alte foi și intervale denumite.

Vă rugăm să rețineți că aici nu suntem legați de niciun loc anume din tabel. Această listă este convenabilă de utilizat, deoarece poate fi „trasă” liber cu mouse-ul în orice loc convenabil. Pentru a face acest lucru, în fila „Dezvoltator”, trebuie să activați modul de proiectare.

4 - element ActiveX

Procedăm în mod similar cu metoda anterioară, dar selectăm pictograma de mai jos - din secțiunea „Elemente ActiveX”.

Definim o listă de valori acceptabile (1). Vă rugăm să rețineți că aici puteți selecta mai multe coloane pentru afișare simultan. Apoi selectăm adresa la care se va insera poziția dorită din listă (2) Indicăm numărul de coloane care vor fi folosite ca date sursă (3), și numărul coloanei din care se va face selecția pentru introducerea în foaia (4). Dacă indicați coloana numărul 2, atunci nu numele de familie, ci poziția va fi inserată în A5. De asemenea, puteți specifica numărul de linii care vor fi afișate în listă. Valoarea implicită este 8. Restul poate fi derulat cu mouse-ul (5).

Această metodă este mai complicată decât cea anterioară, dar returnează imediat valoarea, nu numărul acesteia. Prin urmare, nu este nevoie de o celulă intermediară și procesarea acesteia folosind INDEX. Cred că această listă este mult mai convenabilă de utilizat.

5 - Lista cu completare automată

Sarcină: creați o listă la care se vor adăuga automat valori dintr-un interval dinamic specificat. Dacă se fac modificări în interval, setul de valori propuse se va schimba imediat. Nu trebuie ajustate formule sau setări aici.

Iată cum ar putea arăta completarea automată într-un exemplu simplu:

Metoda 1. Indicați o sursă mare cunoscută.

Cel mai simplu și mai necomplicat truc. În primul rând, urmăm algoritmul obișnuit de acțiuni: în meniu, selectați Date – Validare pe filă. Din lista Tip de date (Permite), selectați opțiunea Listă. Plasați cursorul în câmpul Sursă. Să rezervăm un set cu o marjă mare în listă: de exemplu, până la a 55-a linie, deși avem doar 7 ocupate. Nu uitați să bifați caseta de selectare „Ignorați gol...”. Atunci „rezerva” ta de valori goale nu te va deranja.

Într-adevăr, cel mai simplu mod, dar nu foarte convenabil. La urma urmei, locul rezervat se poate epuiza...

Desigur, puteți specifica și întreaga coloană ca sursă:

Dar procesarea unui număr atât de mare de celule poate încetini oarecum calculele.

Metoda 2: Utilizați un interval numit.

Un interval numit diferă de un interval obișnuit prin faptul că i se dă un nume specific. Este mult mai ușor de lucrat, deoarece nu trebuie să introduceți un link, ci pur și simplu indicați numele acestuia. Să ne uităm la un mic exemplu.

Coloana A conține numele angajaților pe care îi vom introduce. Lista poate fi atât de lungă cât doriți. Dorim ca fiecare intrare nouă să fie inclusă în lista derulantă fără nicio acțiune suplimentară din partea noastră.

Să evidențiem lista de nume pe care o avem la dispoziție A2:A10. Apoi dați-i un nume completând câmpul „Nume” situat în stânga barei de formule. Să creăm o listă de valori în C2. Ca sursă pentru el indicăm expresia

Dezavantajul lucrului cu o astfel de listă este că noile valori nu pot fi pur și simplu atașate la sfârșitul listei utilizate - ele vor rămâne în afara intervalului numit. Acestea vor trebui introduse în interiorul acestuia folosind o linie goală.

Lista poate fi, de asemenea, sortată pentru a fi mai ușor de utilizat.

Principalul inconvenient al folosirii unei astfel de liste este că gama pe care o folosim este static. Mărimea acestuia nu poate fi schimbată automat. De acord, aceasta nu este o metodă foarte convenabilă și avansată din punct de vedere tehnologic. Prea mulți pași manuali.

Metoda 3. O masă „inteligentă” ne va ajuta.

Din 2007, un tabel Excel nu mai este doar un set de rânduri și coloane. Dacă pur și simplu aranjați indicatorii în forma tabelară obișnuită, atunci nu îi va considera un tabel. Există o formatare specială, după care intervalul începe să se comporte ca un întreg, dobândind o serie de proprietăți interesante. În special, începe să-și urmărească propria dimensiune, schimbându-se dinamic pe măsură ce datele sunt ajustate.

Orice set de valori dintr-un tabel poate fi transformat în acest fel. De exemplu, A1:A8. Selectați-le cu mouse-ul. Apoi convertiți în tabel folosind meniul Acasă - Formatați ca tabel. Indicați că prima linie conține numele coloanei. Acesta va fi antetul tabelului dvs. Aspectul poate fi orice: nu este altceva decât un design exterior și nu afectează nimic altceva.

După cum am menționat mai sus, un tabel „inteligent” este bun pentru noi, deoarece își schimbă dinamic dimensiunea atunci când informațiile sunt adăugate la el. Dacă introduceți ceva în linia de sub ea, îl va atașa imediat la sine. Astfel, pot fi adăugate pur și simplu noi valori. De exemplu, introduceți cuvântul „nucă de cocos” în A9, iar tabelul se va extinde imediat la 9 rânduri.

În consecință, actualizarea automată a setului de informații utilizate din listă poate fi organizată dacă utilizați conținutul oricărei coloane a tabelului „inteligent”.

Tot ce rămâne este să-l desemnăm ca sursă. Problema este că programul, ca sursă în listă, nu înțelege o expresie a formei

Tabel1[Coloana1]

și nu o consideră o formulă. Deși în expresiile obișnuite de pe o foaie a registrului de lucru, acest lucru va funcționa destul de bine. Acest construct denotă o referire la prima coloană. Dar din anumite motive este ignorat în câmpul „Sursă”.

Pentru a folosi „masa inteligentă” ca sursă, va trebui să folosim un mic truc și să folosim funcția INDIRECT. Această funcție transformă o variabilă text într-o legătură obișnuită.

Formula va arăta acum astfel:

INDIRECT(„Tabel5[Produs]”)

Table5 este numele atribuit automat „mesei inteligente”. Poate fi diferit pentru tine. În fila meniului Design, puteți schimba numele standard cu al dvs. (dar fără spații!). Folosind-o, ne putem accesa apoi masa pe orice foaie a cărții.

„Produs” este numele primei și singurei noastre coloane, atribuită de titlul acesteia.

Asigurați-vă că includeți, de asemenea, întreaga expresie între ghilimele pentru a o indica ca variabilă text.

Acum, dacă adăugați un alt fruct (de exemplu, nucă de cocos) la A9, acesta va apărea automat în lista noastră. Va fi la fel dacă ștergem ceva. Problema creșterii automate a listei derulante de valori a fost rezolvată.

Sperăm că acum puteți utiliza liste pentru a introduce date repetate frecvent într-un tabel fără erori.

Iată câteva informații utile pentru tine:

- Una dintre cele mai utile caracteristici de validare a datelor este abilitatea de a crea o listă derulantă care vă permite să selectați o valoare dintr-o listă predefinită. Dar odată ce începi să aplici acest lucru în foile de calcul...

Pentru a face foaia de lucru mai ușor de utilizat de către utilizatori, adăugați liste derulante în celule. Listele drop-down permit utilizatorilor să selecteze articole dintr-o listă pe care o creați.

Video

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:

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 dorită. 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.

Vezi si

Utilizatorii care lucrează destul de des în Excel și își mențin bazele de date în acest program probabil trebuie adesea să selecteze o valoare a celulei dintr-o listă predeterminată.

De exemplu, avem o listă de nume de produse, iar sarcina noastră este să umplem fiecare celulă dintr-o anumită coloană a tabelului folosind această listă. Pentru a face acest lucru, trebuie să creați o listă cu toate elementele și apoi să implementați capacitatea de a le selecta în celulele necesare. Această soluție va elimina nevoia de a scrie (copia) același nume manual de multe ori și, de asemenea, vă va scuti de greșeli de scriere și alte posibile erori, mai ales când vine vorba de tabele mari.

Puteți implementa așa-numita listă derulantă folosind mai multe metode, pe care le vom lua în considerare mai jos.

Cea mai simplă și mai înțeleasă metodă este să creați mai întâi o listă în altă parte a documentului. Puteți să-l plasați lângă tabel sau să creați o foaie nouă și să faceți o listă acolo, pentru a nu „aglomera” documentul original cu elemente și date inutile.

În tabelul auxiliar scriem o listă cu toate numele - fiecare pe o linie nouă într-o celulă separată. Rezultatul ar trebui să fie o coloană cu date completate.
Apoi marchem toate aceste celule, facem clic dreapta oriunde în intervalul marcat și în lista care se deschide, facem clic pe funcția „Atribuiți un nume..”
Fereastra „Creați un nume” va apărea pe ecran. Numim lista cum vrem, dar cu condiția ca primul caracter să fie o literă, iar utilizarea anumitor caractere nu este permisă. De asemenea, vă permite să adăugați o notă la listă în câmpul de text corespunzător. Când este gata, faceți clic pe OK.
Treceți la fila „Date” din fereastra principală a programului. Marcăm grupul de celule pentru care dorim să setăm o selecție din lista noastră și facem clic pe pictograma „Validarea datelor” din subsecțiunea „Lucrul cu date”.
Pe ecran va apărea fereastra „Verificarea valorilor introduse”. În fila „Parametri”, în tipul de date, selectați opțiunea „Lista”. În câmpul de text „Sursă”, scrieți semnul egal (“=”) și numele listei nou create. În cazul nostru – „=Nume”. Faceți clic pe OK.
Totul este gata. O pictogramă mică cu o săgeată în jos va apărea în dreapta fiecărei celule din intervalul selectat, făcând clic pe care puteți deschide lista de articole pe care le-am compilat în prealabil. Făcând clic pe opțiunea dorită din listă, aceasta va fi imediat introdusă în celulă. În plus, valoarea dintr-o celulă poate corespunde acum doar cu numele din listă, ceea ce va elimina eventualele greșeli de scriere.

Puteți crea o listă derulantă într-un alt mod - prin instrumente pentru dezvoltatori care utilizează tehnologia ActiveX. Metoda este ceva mai complicată decât cea descrisă mai sus, dar oferă o gamă mai largă de instrumente pentru personalizarea listei: puteți seta numărul de elemente, dimensiunea și aspectul casetei de listă în sine, necesitatea de a potrivi o valoare într-o celulă. cu una dintre valorile listei și multe altele.

În primul rând, aceste instrumente trebuie găsite și activate, deoarece sunt dezactivate în mod implicit. Accesați meniul „Fișier”.
În lista din stânga, găsiți elementul „Opțiuni” din partea de jos și faceți clic pe el.
Accesați secțiunea „Personalizați panglica” și în zona „File principale”, bifați caseta de lângă „Dezvoltator”. Instrumentele pentru dezvoltatori vor fi adăugate la panglica programului. Faceți clic pe OK pentru a salva setările.
Programul are acum o filă nouă numită „Dezvoltator”. Vom lucra prin asta. În primul rând, creăm o coloană cu elemente care vor fi sursele de valori pentru lista noastră drop-down.
Comutați la fila „Dezvoltator”. În subsecțiunea „Controale”, faceți clic pe butonul „Inserare”. În lista care se deschide, în blocul funcțional „ActiveX Controls”, faceți clic pe pictograma „Combo Box”.
Apoi, faceți clic pe celula dorită, după care va apărea o casetă cu listă. Îi ajustăm dimensiunile de-a lungul limitelor celulei. Dacă lista este selectată cu mouse-ul, „Modul de proiectare” va fi activ pe bara de instrumente. Faceți clic pe butonul „Proprietăți” pentru a continua configurarea listei.
În parametrii care se deschid, găsiți linia „ListFillRange”. În coloana de lângă noi, despărțite de două puncte, scriem coordonatele intervalului de celule care alcătuiesc lista creată anterior. Închideți fereastra cu parametri făcând clic pe cruce.
Apoi faceți clic dreapta pe fereastra cu listă, apoi faceți clic pe elementul „Obiect ComboBox” și selectați „Editare”.
Ca rezultat, obținem o listă derulantă cu o listă predefinită.
Pentru a o insera în mai multe celule, mutați cursorul peste colțul din dreapta jos al celulei cu lista și, de îndată ce își schimbă aspectul într-o cruce, țineți apăsat butonul stâng al mouse-ului și trageți în jos până la linia de jos în care avem nevoie de o listă similară.

Utilizatorii au, de asemenea, capacitatea de a crea liste interdependente (legate) mai complexe. Aceasta înseamnă că lista dintr-o celulă va depinde de ce valoare am selectat în alta. De exemplu, putem seta kilograme sau litri în unități de produs. Dacă selectați chefir în prima celulă, în a doua vi se vor oferi două opțiuni din care să alegeți - litri sau mililitri. Iar dacă în prima celulă alegem mere, în a doua vom avea de ales între kilograme sau grame.

Pentru a face acest lucru, trebuie să pregătiți cel puțin trei coloane. Primul va conține numele mărfurilor, iar al doilea și al treilea vor conține posibilele unități de măsură ale acestora. Pot exista mai multe coloane cu posibile variații ale unităților de măsură.
În primul rând, creăm o listă generală pentru toate numele produselor selectând toate rândurile coloanei „Nume” prin meniul contextual al intervalului selectat.
Dați-i un nume, de exemplu, „Mâncare”.
Apoi, în același mod, creăm liste separate pentru fiecare produs cu unitățile de măsură corespunzătoare. Pentru o mai mare claritate, să luăm ca exemplu prima poziție – „Arcul”. Marcăm celulele care conțin toate unitățile de măsură pentru acest produs, iar prin meniul contextual atribuim un nume, care trebuie să se potrivească complet cu numele.
În același mod, creăm liste separate pentru toate celelalte produse din lista noastră.
După aceasta, inserăm o listă generală de produse în celula de sus a primei coloane a tabelului principal - ca în exemplul descris mai sus, prin butonul „Verificarea datelor” (fila „Date”).
Indicăm „=Nutriție” ca sursă (după numele nostru).
Apoi faceți clic pe celula de sus a coloanei cu unități de măsură, accesați și fereastra de verificare a datelor și indicați formula în sursă „ =INDIRECT(A2)„, unde A2 este numărul celulei cu produsul corespunzător.
Listele sunt gata. Rămâne doar să le întindeți toate rândurile tabelului, atât pentru coloana A, cât și pentru coloana B.