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

Lista derulantă este un instrument incredibil de util care poate ajuta să lucreze cu informații mai confortabil. Face posibilă includerea mai multor valori într-o celulă simultan, cu care puteți lucra, ca și cu oricare altele. Pentru a-l selecta pe cel de care aveți nevoie, faceți clic pe pictograma săgeată, după care se afișează o listă de valori uXNUMXbuXNUMXbis. După selectarea uneia anume, celula este automat completată cu aceasta, iar formulele sunt recalculate pe baza acesteia.

Excel oferă multe metode diferite pentru generarea unui meniu derulant și, în plus, vă permite să le personalizați în mod flexibil. Să analizăm aceste metode mai detaliat.

Procesul de creare a listei

Pentru a genera un meniu pop-up, faceți clic pe elementele de meniu de-a lungul căii „Date” – „Validare datelor”. Se va deschide o casetă de dialog în care trebuie să găsiți fila „Parametri” și să faceți clic pe ea dacă nu a fost deschisă înainte. Are o mulțime de setări, dar elementul „Tipul de date” este important pentru noi. Dintre toate semnificațiile, „Lista” este cea potrivită.

Numărul de metode prin care informațiile sunt introduse în lista pop-up este destul de mare.

1. Indicarea independentă a elementelor listei separate prin punct și virgulă în câmpul „Sursă” situat în aceeași filă a aceleiași casete de dialog.
   

   

2. Indicarea prealabilă a valorilor. Câmpul Sursă conține intervalul în care sunt disponibile informațiile necesare.
   

   

3. Specificarea unui interval numit. O metodă care o repetă pe cea anterioară, dar este necesară doar denumirea preliminară a gamei.
   

   

Oricare dintre aceste metode va produce rezultatul dorit. Să ne uităm la metodele de generare a listelor derulante în situații reale.

Pe baza datelor din listă

Să presupunem că avem un tabel care descrie tipurile de fructe diferite.

Pentru a crea o listă într-un meniu derulant pe baza acestui set de informații, trebuie să faceți următoarele:

1. Selectați celula rezervată pentru lista viitoare.
2. Găsiți fila Date de pe panglică. Acolo facem clic pe „Verificați datele”.
   

   

3. Găsiți elementul „Tip de date” și comutați valoarea la „List”.
   

   

4. În câmpul care indică opțiunea „Sursă”, introduceți intervalul dorit. Vă rugăm să rețineți că trebuie specificate referințe absolute, astfel încât la copierea listei, informațiile să nu se schimbe.
   

În plus, există o funcție pentru a genera liste simultan în mai multe celule. Pentru a realiza acest lucru, ar trebui să le selectați pe toate și să efectuați aceiași pași descriși mai devreme. Din nou, trebuie să vă asigurați că sunt scrise referințe absolute. Dacă adresa nu are un semn dolar lângă numele coloanelor și rândurilor, atunci trebuie să le adăugați apăsând tasta F4 până când semnul $ este lângă numele coloanelor și rândurilor.

Cu înregistrare manuală a datelor

În situația de mai sus, lista a fost scrisă prin evidențierea intervalului necesar. Aceasta este o metodă convenabilă, dar uneori este necesară înregistrarea manuală a datelor. Acest lucru va face posibilă evitarea dublării informațiilor din registrul de lucru.

Să presupunem că ne confruntăm cu sarcina de a crea o listă care să conțină două opțiuni posibile: da și nu. Pentru a îndeplini sarcina, este necesar:

1. Faceți clic pe celulă pentru listă.
2. Deschideți „Date” și găsiți acolo secțiunea „Verificarea datelor” care ne este familiară.
   

   

3. Din nou, selectați tipul „List”.
   

   

4. Aici trebuie să introduceți „Da; Nu” ca sursă. Vedem că informațiile sunt introduse manual folosind punct și virgulă pentru enumerare.

După ce facem clic pe OK, avem următorul rezultat.

Apoi, programul va crea automat un meniu derulant în celula corespunzătoare. Toate informațiile pe care utilizatorul le-a specificat ca elemente în lista pop-up. Regulile pentru crearea unei liste în mai multe celule sunt similare cu cele anterioare, cu singura excepție că trebuie să specificați manual informațiile folosind punct și virgulă.

Crearea unei liste derulante folosind funcția OFFSET

Pe lângă metoda clasică, este posibilă utilizarea funcției ELIMINAREApentru a genera meniuri derulante.

Să deschidem foaia.

Pentru a utiliza funcția pentru lista derulantă, trebuie să faceți următoarele:

1. Selectați celula de interes în care doriți să plasați lista viitoare.
2. Deschideți fila „Date” și fereastra „Validare date” în secvență.
   

   

3. Setați „Lista”. Acest lucru se face în același mod ca în exemplele anterioare. În final, se folosește următoarea formulă: =OFFSET(A$2$;0;0;5). O introducem acolo unde sunt specificate celulele care vor fi folosite ca argument.

Apoi programul va crea un meniu cu o listă de fructe.

Sintaxa pentru aceasta este:

=OFFSET(referință,line_offset,coloan_offset,[înălțime],[lățime])

Vedem că această funcție are 5 argumente. În primul rând, este dată prima adresă de celulă care trebuie compensată. Următoarele două argumente specifică câte rânduri și coloane trebuie compensate. Apropo de noi, argumentul Înălțime este 5 deoarece reprezintă înălțimea listei. 

Lista derulantă în Excel cu înlocuire de date (+ folosind funcția OFFSET)

În cazul dat ELIMINAREA permis să creeze un meniu pop-up situat într-un interval fix. Dezavantajul acestei metode este că, după adăugarea articolului, va trebui să editați singur formula.

Pentru a crea o listă dinamică cu suport pentru introducerea de noi informații, trebuie să:

1. Selectați celula de interes.
2. Extindeți fila „Date” și faceți clic pe „Validare datelor”.
3. În fereastra care se deschide, selectați din nou elementul „Lista” și specificați următoarea formulă ca sursă de date: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Faceți clic pe OK.

Acesta conține o funcție COUNTIF, pentru a determina imediat câte celule sunt umplute (deși are un număr mult mai mare de utilizări, doar îl scriem aici pentru un anumit scop).

Pentru ca formula să funcționeze normal, este necesar să urmăriți dacă există celule goale pe calea formulei. Nu ar trebui să fie.

Listă derulantă cu date dintr-o altă foaie sau fișier Excel

Metoda clasică nu funcționează dacă trebuie să obțineți informații dintr-un alt document sau chiar dintr-o fișă conținută în același fișier. Pentru aceasta se folosește funcția INDIRECT, care vă permite să introduceți în formatul corect un link către o celulă aflată într-o altă foaie sau în general – un fișier. Trebuie să faceți următoarele:

1. Activați celula în care plasăm lista.
2. Deschidem fereastra pe care o știm deja. În același loc în care am indicat anterior sursele pentru alte intervale, în format este indicată o formulă =INDIRECT(„[Lista1.xlsx]Foaie1!$A$1:$A$9”). Desigur, în loc de List1 și Sheet1, puteți introduce numele cărții și, respectiv, foii. 

Atenţie! Numele fișierului este dat între paranteze drepte. În acest caz, Excel nu va putea folosi fișierul care este în prezent închis ca sursă de informații.

De asemenea, trebuie menționat că numele fișierului în sine are sens numai dacă documentul solicitat se află în același folder cu cel în care va fi inserată lista. Dacă nu, atunci trebuie să specificați adresa acestui document în întregime.

Crearea dropdown-urilor dependente

O listă dependentă este una al cărei conținut este afectat de alegerea utilizatorului într-o altă listă. Să presupunem că avem un tabel deschis în fața noastră care conține trei intervale, fiecare dintre ele având un nume.

Trebuie să urmați acești pași pentru a genera liste al căror rezultat este afectat de opțiunea selectată într-o altă listă.

1. Creați prima listă cu nume de intervale.
   

   

2. La punctul de intrare sursă, indicatorii necesari sunt evidențiați unul câte unul.
   

   

3. Creați o a doua listă în funcție de tipul de plantă ales de persoană. Alternativ, dacă specificați arbori în prima listă, atunci informațiile din a doua listă vor fi „stejar, carpen, castan” și nu numai. Este necesar să scrieți formula în locul de intrare al sursei de date =INDIRECT(E3). E3 – celula care contine numele intervalului 1.=INDIRECT(E3). E3 – celula cu numele listei 1.

Acum totul este gata.

Cum să selectezi mai multe valori dintr-o listă derulantă?

Uneori nu este posibil să acordați preferință unei singure valori, așa că trebuie selectate mai multe. Apoi trebuie să adăugați o macrocomandă la codul paginii. Folosind combinația de taste Alt + F11 se deschide Editorul Visual Basic. Și codul este introdus acolo.

Private Sub Worksheet_Change (ByVal Target As Range)

    La data de eroare CV următoare

    Dacă nu se intersectează (Target, Range(«Е2:Е9»)) este Nimic și Target.Cells.Count = 1 Atunci

        Application.EnableEvents = Fals

        Dacă Len (Target.Offset (0, 1)) = 0 Atunci

            Target.Offset (0, 1) = țintă

        altfel

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

        Final, dacă

        Target.ClearContents

        Application.EnableEvents = Adevărat

    Final, dacă

End Sub 

Pentru ca conținutul celulelor să fie afișat mai jos, inserăm următorul cod în editor.

Private Sub Worksheet_Change (ByVal Target As Range)

    La data de eroare CV următoare

    Dacă nu se intersectează (Target, Range(«Н2:К2»)) este Nimic și Target.Cells.Count = 1 Atunci

        Application.EnableEvents = Fals

        Dacă Len (Target.Offset (1, 0)) = 0 Atunci

            Target.Offset (1, 0) = țintă

        altfel

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

        Final, dacă

        Target.ClearContents

        Application.EnableEvents = Adevărat

    Final, dacă

End Sub

Și, în sfârșit, acest cod este folosit pentru a scrie într-o singură celulă.

Private Sub Worksheet_Change (ByVal Target As Range)

    La data de eroare CV următoare

    If Not Intersect(Target, Range(«C2:C5»)) Este Nimic și Target.Cells.Count = 1 Atunci

        Application.EnableEvents = Fals

        newVal = țintă

        Aplicație.Anulați

        oldval = Țintă

        Daca Len (oldval) <> 0 Si oldval <> newVal Atunci

            Target = Target & «,» & newVal

        altfel

            Țintă = newVal

        Final, dacă

        Dacă Len (newVal) = 0, atunci Target.ClearContents

        Application.EnableEvents = Adevărat

    Final, dacă

End Sub

Intervalele sunt editabile.

Cum să faci o listă derulantă cu o căutare?

În acest caz, trebuie să utilizați inițial un alt tip de listă. Se deschide fila „Dezvoltator”, după care trebuie să faceți clic sau să atingeți (dacă ecranul este tactil) pe elementul „Inserare” – „ActiveX”. Are o casetă combinată. Vi se va solicita să desenați această listă, după care va fi adăugată în document.

În plus, este configurat prin proprietăți, unde este specificat un interval în opțiunea ListFillRange. Celula în care este afișată valoarea definită de utilizator este configurată folosind opțiunea LinkedCell. În continuare, trebuie doar să scrieți primele caractere, deoarece programul va sugera automat valori posibile.

Listă derulantă cu înlocuire automată a datelor

Există, de asemenea, o funcție prin care datele sunt înlocuite automat după ce sunt adăugate la interval. Este ușor să faci asta:

1. Creați un set de celule pentru lista viitoare. În cazul nostru, acesta este un set de culori. O selectăm.
   

   

2. Apoi, trebuie să fie formatat ca tabel. Trebuie să faceți clic pe butonul cu același nume și să selectați stilul tabelului.
   

   

   

Apoi, trebuie să confirmați acest interval apăsând butonul „OK”.

Selectăm tabelul rezultat și îi dăm un nume prin câmpul de introducere situat în partea de sus a coloanei A.

Asta este, există un tabel și poate fi folosit ca bază pentru o listă derulantă, pentru care aveți nevoie de:

1. Selectați celula în care se află lista.
2. Deschideți dialogul de validare a datelor.
   

   

3. Setăm tipul de date la „List”, iar ca valori dăm numele tabelului prin semnul =.
   

   

   

Totul, celula este gata, iar numele culorilor sunt afișate în ea, așa cum aveam nevoie inițial. Acum puteți adăuga noi poziții pur și simplu scriindu-le într-o celulă situată puțin mai jos imediat după ultima.

Acesta este avantajul tabelului, că intervalul crește automat atunci când sunt adăugate date noi. Prin urmare, acesta este cel mai convenabil mod de a adăuga o listă.

Cum se copiază lista derulantă?

Pentru a copia, este suficient să folosiți combinația de taste Ctrl + C și Ctrl + V. Deci lista drop-down va fi copiată împreună cu formatarea. Pentru a elimina formatarea, trebuie să utilizați o pastă specială (în meniul contextual, această opțiune apare după copierea listei), unde este setată opțiunea „condiții privind valorile”.

Selectați toate celulele care conțin o listă derulantă

Pentru a îndeplini această sarcină, trebuie să utilizați funcția „Selectați un grup de celule” din grupul „Găsiți și selectați”.

După aceea, se va deschide o casetă de dialog, unde ar trebui să selectați elementele „Toate” și „Aceștia la fel” din meniul „Validare datelor”. Primul element selectează toate listele, iar al doilea le selectează numai pe cele care sunt similare cu anumite liste.