Kako napraviti padajući popis u Excelu

Padajući popis nevjerojatno je koristan alat koji može pomoći da rad s informacijama bude udobniji. Omogućuje sadržavanje nekoliko vrijednosti u ćeliji odjednom, s kojima možete raditi, kao i s bilo kojim drugim. Da biste odabrali onu koja vam je potrebna, samo kliknite na ikonu strelice, nakon čega se prikazuje popis vrijednosti uXNUMXbuXNUMXbis. Nakon odabira određene, ćelija se automatski popunjava njome, a formule se ponovno izračunavaju na temelju nje.

Excel nudi mnogo različitih metoda za generiranje padajućeg izbornika, a osim toga, omogućuje vam da ih fleksibilno prilagodite. Analizirajmo ove metode detaljnije.

Proces izrade popisa

Da biste generirali skočni izbornik, kliknite na stavke izbornika duž putanje "Podaci" - "Validacija podataka". Otvorit će se dijaloški okvir u kojem trebate pronaći karticu “Parametri” i kliknuti na nju ako već nije otvorena. Ima puno postavki, ali važna nam je stavka "Vrsta podataka". Od svih značenja, "Lista" je pravo.

Broj metoda kojima se informacije unose u pop-up popis je prilično velik.

1. Neovisna indikacija elemenata popisa odvojenih točkom i zarezom u polju "Izvor" koje se nalazi na istoj kartici istog dijaloškog okvira.
   

   

2. Preliminarna indikacija vrijednosti. Polje Izvor sadrži raspon u kojem su potrebne informacije dostupne.
   

   

3. Određivanje imenovanog raspona. Metoda koja ponavlja prethodnu, ali je potrebno samo prethodno imenovati raspon.
   

   

Bilo koja od ovih metoda će dati željeni rezultat. Pogledajmo metode za generiranje padajućih popisa u situacijama iz stvarnog života.

Na temelju podataka iz popisa

Recimo da imamo tablicu koja opisuje vrste različitog voća.

Da biste izradili popis u padajućem izborniku na temelju ovog skupa informacija, morate učiniti sljedeće:

1. Odaberite ćeliju rezerviranu za budući popis.
2. Pronađite karticu Podaci na vrpci. Tamo kliknemo na “Provjeri podatke”.
   

   

3. Pronađite stavku "Vrsta podataka" i prebacite vrijednost na "Popis".
   

   

4. U polje koje označava opciju “Izvor” unesite željeni raspon. Imajte na umu da je potrebno navesti apsolutne reference kako se prilikom kopiranja popisa podaci ne bi pomicali.
   

Osim toga, postoji funkcija za generiranje popisa odjednom u više od jedne ćelije. Da biste to postigli, trebate ih sve odabrati i izvesti iste korake kao što je ranije opisano. Opet, morate biti sigurni da su napisane apsolutne reference. Ako adresa nema znak dolara uz nazive stupaca i redaka, tada ih trebate dodati pritiskom na tipku F4 dok znak $ ne bude pored naziva stupaca i redaka.

S ručnim snimanjem podataka

U gornjoj situaciji popis je napisan isticanjem potrebnog raspona. Ovo je praktična metoda, ali ponekad je potrebno ručno zabilježiti podatke. Time će se izbjeći dupliciranje podataka u radnoj knjižici.

Pretpostavimo da smo suočeni sa zadatkom kreiranja popisa koji sadrži dva moguća izbora: da i ne. Za realizaciju zadatka potrebno je:

1. Kliknite na ćeliju za popis.
2. Otvorite "Podaci" i tamo pronađite odjeljak "Provjera podataka" koji nam je poznat.
   

   

3. Opet odaberite vrstu "List".
   

   

4. Ovdje trebate unijeti „Da; Ne” kao izvor. Vidimo da se informacije unose ručno korištenjem točke i zareza za nabrajanje.

Nakon klika na OK, imamo sljedeći rezultat.

Zatim će program automatski stvoriti padajući izbornik u odgovarajućoj ćeliji. Sve informacije koje je korisnik naveo kao stavke na skočnom popisu. Pravila za izradu popisa u nekoliko ćelija slična su prethodnima, s jedinom iznimkom da podatke morate navesti ručno pomoću točke sa zarezom.

Stvaranje padajućeg popisa pomoću funkcije OFFSET

Osim klasične metode, moguće je koristiti funkciju RASPOLAGANJEza generiranje padajućih izbornika.

Otvorimo list.

Da biste koristili funkciju za padajući popis, trebate učiniti sljedeće:

1. Odaberite željenu ćeliju u koju želite smjestiti budući popis.
2. Redom otvorite karticu "Podaci" i prozor "Provjera valjanosti podataka".
   

   

3. Postavite "Popis". To se radi na isti način kao i prethodni primjeri. Na kraju se koristi sljedeća formula: =POMAK(A$2$;0;0;5). Upisujemo ga tamo gdje su navedene ćelije koje će se koristiti kao argument.

Zatim će program kreirati jelovnik s popisom voća.

Sintaksa za ovo je:

=OFFSET(referenca,pomak_linije,pomak_stupca,[visina],[širina])

Vidimo da ova funkcija ima 5 argumenata. Prvo se daje prva adresa ćelije koja se pomiče. Sljedeća dva argumenta određuju koliko redaka i stupaca treba pomaknuti. Kad smo već kod nas, argument Height je 5 jer predstavlja visinu liste. 

Padajući popis u Excelu sa zamjenom podataka (+ pomoću funkcije OFFSET)

U datom slučaju RASPOLAGANJE dopušteno stvaranje skočnog izbornika koji se nalazi u fiksnom rasponu. Nedostatak ove metode je što ćete nakon dodavanja stavke morati sami urediti formulu.

Za izradu dinamičkog popisa s podrškom za unos novih podataka morate:

1. Odaberite ćeliju od interesa.
2. Proširite karticu "Podaci" i kliknite na "Validacija podataka".
3. U prozoru koji se otvori ponovno odaberite stavku "Popis" i navedite sljedeću formulu kao izvor podataka: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Kliknite U redu.

Ovo sadrži funkciju COUNTIF, kako bismo odmah odredili koliko je ćelija popunjeno (iako ima puno veći broj namjena, ovdje ga samo pišemo za određenu svrhu).

Kako bi formula normalno funkcionirala, potrebno je pratiti ima li praznih ćelija na putu formule. Ne bi trebali biti.

Padajući popis s podacima s drugog lista ili Excel datoteke

Klasična metoda ne funkcionira ako trebate dobiti podatke iz drugog dokumenta ili čak lista koji se nalazi u istoj datoteci. Za to se koristi funkcija INDIREKTNO, koji vam omogućuje da u ispravnom formatu unesete vezu na ćeliju koja se nalazi na drugom listu ili općenito - datoteku. Morate učiniti sljedeće:

1. Aktivirajte ćeliju u koju postavljamo popis.
2. Otvaramo prozor koji već poznajemo. Na istom mjestu gdje smo prethodno naznačili izvore za druge raspone, u formatu je navedena formula =INDIRECT(“[List1.xlsx]Sheet1!$A$1:$A$9”). Naravno, umjesto List1 i Sheet1, možete umetnuti nazive svoje knjige odnosno listova. 

Pažnja! Naziv datoteke naveden je u uglatim zagradama. U tom slučaju Excel neće moći koristiti datoteku koja je trenutno zatvorena kao izvor informacija.

Također treba napomenuti da sam naziv datoteke ima smisla samo ako se traženi dokument nalazi u istoj mapi kao i ona u koju će se umetnuti popis. Ako ne, onda morate navesti adresu ovog dokumenta u cijelosti.

Stvaranje zavisnih padajućih izbornika

Zavisni popis je onaj na čiji sadržaj utječe korisnikov izbor na drugom popisu. Pretpostavimo da imamo otvorenu tablicu ispred sebe koja sadrži tri raspona od kojih je svaki dobio ime.

Morate slijediti ove korake za generiranje popisa na čiji rezultat utječe opcija odabrana na drugom popisu.

1. Napravite 1. popis s nazivima raspona.
   

   

2. Na ulaznoj točki izvora, potrebni indikatori su istaknuti jedan po jedan.
   

   

3. Napravite drugi popis ovisno o vrsti biljke koju je osoba odabrala. Alternativno, ako navedete drveće na prvom popisu, informacije na drugom popisu bit će "hrast, grab, kesten" i dalje. Formulu je potrebno zapisati na mjestu unosa izvora podataka =INDIRECT(E3). E3 – ćelija koja sadrži naziv raspona 1.=INDIRECT(E3). E3 – ćelija s nazivom liste 1.

Sada je sve spremno.

Kako odabrati više vrijednosti s padajućeg popisa?

Ponekad nije moguće dati prednost samo jednoj vrijednosti, pa ih je potrebno odabrati više. Zatim trebate dodati makro kodu stranice. Kombinacijom tipki Alt + F11 otvara se Visual Basic Editor. I kod je umetnut tamo.

Privatni podradni list_Promjena (ByVal Target As Range)

    Na Error Resume Next

    If Not Intersect(Target, Range(«E2:E9»)) Is Nothing And Target.Cells.Count = 1 Tada

        Application.EnableEvents = False

        Ako je Len (Target.Offset (0, 1)) = 0 Onda

            Target.Offset (0, 1) = Cilj

        drugo

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

        Kraj Ako je

        Target.ClearContents

        Application.EnableEvents = True

    Kraj Ako je

End Sub 

Da bi sadržaj ćelija bio prikazan ispod, u editor ubacujemo sljedeći kod.

Privatni podradni list_Promjena (ByVal Target As Range)

    Na Error Resume Next

    Ako Not Intersect(Target, Range(«N2:K2»)) Is Nothing And Target.Cells.Count = 1 Onda

        Application.EnableEvents = False

        Ako je Len (Target.Offset (1, 0)) = 0 Onda

            Target.Offset (1, 0) = Cilj

        drugo

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

        Kraj Ako je

        Target.ClearContents

        Application.EnableEvents = True

    Kraj Ako je

End Sub

I konačno, ovaj kod se koristi za pisanje u jednoj ćeliji.

Privatni podradni list_Promjena (ByVal Target As Range)

    Na Error Resume Next

    If Not Intersect(Target, Range(«C2:C5»)) Is Nothing And Target.Cells.Count = 1 Tada

        Application.EnableEvents = False

        newVal = Cilj

        Primjena.Poništi

        oldval = cilj

        Ako je Len (oldval) <> 0 i oldval <> newVal Onda

            Target = Target & «,» & newVal

        drugo

            Cilj = newVal

        Kraj Ako je

        If Len (newVal) = 0 Then Target.ClearContents

        Application.EnableEvents = True

    Kraj Ako je

End Sub

Rasponi se mogu uređivati.

Kako napraviti padajući popis s pretraživanjem?

U tom slučaju prvo morate koristiti drugu vrstu popisa. Otvara se kartica "Razvojni programer", nakon čega morate kliknuti ili dodirnuti (ako je zaslon osjetljiv na dodir) element "Umetni" - "ActiveX". Ima kombinirani okvir. Od vas će se tražiti da nacrtate ovaj popis, nakon čega će biti dodan u dokument.

Nadalje, konfigurira se kroz svojstva, gdje je raspon naveden u opciji ListFillRange. Ćelija u kojoj se prikazuje korisnički definirana vrijednost konfigurira se pomoću opcije LinkedCell. Zatim samo trebate zapisati prve znakove jer će program automatski predložiti moguće vrijednosti.

Padajući popis s automatskom zamjenom podataka

Također postoji funkcija da se podaci automatski zamjenjuju nakon što su dodani u raspon. Lako je to učiniti:

1. Napravite skup ćelija za budući popis. U našem slučaju, ovo je skup boja. Mi ga odabiremo.
   

   

2. Zatim ga treba formatirati kao tablicu. Potrebno je kliknuti istoimeni gumb i odabrati stil tablice.
   

   

   

Zatim morate potvrditi ovaj raspon pritiskom na gumb "OK".

Odaberemo dobivenu tablicu i dajemo joj naziv kroz polje za unos koje se nalazi na vrhu stupca A.

To je to, postoji tablica i može poslužiti kao osnova za padajući popis za koji vam je potrebno:

1. Odaberite ćeliju u kojoj se nalazi popis.
2. Otvorite dijaloški okvir Provjera valjanosti podataka.
   

   

3. Tip podataka postavljamo na "List", a kao vrijednosti dajemo naziv tablice kroz znak =.
   

   

   

Sve, ćelija je spremna, au njoj su prikazana imena boja, kao što smo izvorno trebali. Sada možete dodati nove pozicije jednostavnim upisivanjem u ćeliju koja se nalazi malo niže odmah iza zadnje.

To je prednost tablice, da se raspon automatski povećava kada se dodaju novi podaci. Sukladno tome, ovo je najprikladniji način dodavanja popisa.

Kako kopirati padajući popis?

Za kopiranje dovoljno je koristiti kombinaciju tipki Ctrl + C i Ctrl + V. Dakle, padajući popis će biti kopiran zajedno s oblikovanjem. Da biste uklonili oblikovanje, morate upotrijebiti posebno lijepljenje (u kontekstnom izborniku ova se opcija pojavljuje nakon kopiranja popisa), gdje je postavljena opcija "uvjeti na vrijednosti".

Odaberite sve ćelije koje sadrže padajući popis

Da biste izvršili ovaj zadatak, morate koristiti funkciju "Odaberi grupu ćelija" u grupi "Pronađi i odaberi".

Nakon toga otvorit će se dijaloški okvir u kojem trebate odabrati stavke "Sve" i "Iste" u izborniku "Provjera podataka". Prva stavka odabire sve popise, a druga odabire samo one koji su slični određenim.