Kako stvoriti padajući popis u Excelu (jedini vodič koji vam je potreban)

Padajući popis izvrstan je način da se korisniku pruži mogućnost odabira s unaprijed definiranog popisa.

Može se koristiti tijekom pridobivanja korisnika da ispuni obrazac ili prilikom stvaranja interaktivnih Excelovih nadzornih ploča.

Padajući popisi prilično su česti na web stranicama/aplikacijama i vrlo su intuitivni za korisnika.

Gledajte video - Stvaranje padajućeg popisa u Excelu

U ovom ćete vodiču naučiti kako stvoriti padajući popis u Excelu (potrebno je samo nekoliko sekundi za to) zajedno sa svim sjajnim stvarima koje možete učiniti s njim.

Kako stvoriti padajući popis u Excelu

U ovom odjeljku naučit ćete točne korake za izradu padajućeg popisa programa Excel:

1. Korištenje podataka iz ćelija.
2. Ručni unos podataka.
3. Koristeći formulu OFFSET.

#1 Korištenje podataka iz ćelija

Recimo da imate popis stavki kako je prikazano u nastavku:

Evo koraka za stvaranje padajućeg popisa programa Excel:

1. Odaberite ćeliju u kojoj želite stvoriti padajući popis.
2. Idite na Podaci -> Alati za podatke -> Provjera podataka.
3. U dijaloškom okviru Validacija podataka na kartici Postavke odaberite Popis kao kriterij provjere valjanosti.
   • Čim odaberete Popis, pojavit će se izvorno polje.
4. U polje izvora unesite = $ A $ 2: $ A $ 6 ili jednostavno kliknite u polje Izvor i mišem odaberite ćelije i kliknite U redu. Ovo će umetnuti padajući popis u ćeliju C2.
   • Provjerite je li označena padajuća opcija U ćeliji (koja se provjerava prema zadanim postavkama). Ako ova opcija nije označena, ćelija ne prikazuje padajući izbornik, međutim, možete ručno unijeti vrijednosti na popis.

Bilješka: Ako želite stvoriti padajuće popise u više ćelija odjednom, odaberite sve ćelije u kojima ga želite stvoriti, a zatim slijedite gore navedene korake. Uvjerite se da su reference ćelija apsolutne (na primjer, $ A $ 2), a ne relativne (kao što su A2 ili A $ 2 ili $ A2).

#2 Ručnim unosom podataka

U gornjem primjeru, reference ćelija koriste se u polju Izvor. Stavke možete dodati i izravno unosom ručno u polje izvora.

Na primjer, recimo da želite prikazati dvije opcije, Da i Ne, u padajućem izborniku u ćeliji. Evo kako ga možete izravno unijeti u polje izvora provjere valjanosti podataka:

• Odaberite ćeliju u kojoj želite stvoriti padajući popis (ćelija C2 u ovom primjeru).
• Idite na Podaci -> Alati za podatke -> Provjera podataka.
• U dijaloškom okviru Validacija podataka na kartici Postavke odaberite Popis kao kriterij provjere valjanosti.
  • Čim odaberete Popis, pojavit će se izvorno polje.
• U polje izvora unesite Da, Ne
  • Provjerite je li označena opcija padajućeg izbornika U ćeliji.
• Pritisnite U redu.

To će stvoriti padajući popis u odabranoj ćeliji. Sve stavke navedene u izvornom polju, odvojene zarezom, navedene su u različitim retcima u padajućem izborniku.

Sve stavke unesene u izvorno polje, odvojene zarezom, prikazane su u različitim redovima na padajućem popisu.

Bilješka: Ako želite stvoriti padajuće popise u više ćelija odjednom, odaberite sve ćelije u kojima ga želite stvoriti, a zatim slijedite gore navedene korake.

#3 Korištenje Excel formula

Osim odabira iz ćelija i ručnog unosa podataka, možete koristiti i formulu u izvornom polju za stvaranje padajućeg popisa programa Excel.

Bilo koja formula koja vraća popis vrijednosti može se koristiti za stvaranje padajućeg popisa u Excelu.

Na primjer, pretpostavimo da imate skup podataka kako je prikazano u nastavku:

Evo koraka za stvaranje padajućeg popisa programa Excel pomoću funkcije OFFSET:

• Odaberite ćeliju u kojoj želite stvoriti padajući popis (ćelija C2 u ovom primjeru).
• Idite na Podaci -> Alati za podatke -> Provjera podataka.
• U dijaloškom okviru Validacija podataka na kartici Postavke odaberite Popis kao kriterij provjere valjanosti.
  • Čim odaberete Popis, pojavit će se izvorno polje.
• U polje Izvor unesite sljedeću formulu: = OFFSET ($ A $ 2,0,0,5)
  • Provjerite je li označena opcija padajućeg izbornika U ćeliji.
• Pritisnite U redu.

Ovo će stvoriti padajući popis koji sadrži sve nazive voća (kao što je prikazano u nastavku).

Bilješka: Ako želite stvoriti padajući popis u više ćelija odjednom, odaberite sve ćelije u kojima ga želite stvoriti, a zatim slijedite gore navedene korake. Uvjerite se da su reference ćelija apsolutne (na primjer, $ A $ 2), a ne relativne (kao što su A2, ili A $ 2, ili $ A2).

Kako funkcionira ova formula ??

U gornjem slučaju koristili smo OFFSET funkciju za izradu padajućeg popisa. Vraća popis stavki iz ra

Vraća popis stavki iz raspona A2: A6.

Evo sintakse funkcije OFFSET: = OFFSET (referenca, redovi, stupci, [visina], [širina])

Potrebno je pet argumenata, gdje smo referencu naveli kao A2 (početna točka popisa). Redci/polja su navedeni kao 0 jer ne želimo pomaknuti referentnu ćeliju. Visina je navedena kao 5 jer na popisu ima pet elemenata.

Sada, kada koristite ovu formulu, ona vraća niz koji ima popis od pet plodova u A2: A6. Imajte na umu da ako formulu unesete u ćeliju, odaberete je i pritisnete F9, vidjet ćete da vraća niz naziva voća.

Stvaranje dinamičkog padajućeg popisa u Excelu (pomoću OFFSET -a)

Gornja tehnika korištenja formule za izradu padajućeg popisa može se proširiti i za stvaranje dinamičkog padajućeg popisa. Ako koristite funkciju OFFSET, kao što je gore prikazano, čak i ako dodate više stavki na popis, padajući izbornik se neće automatski ažurirati. Morat ćete ga ručno ažurirati svaki put kad promijenite popis.

Evo načina da ga učinite dinamičnim (i to nije ništa drugo nego manje podešavanje formule):

• Odaberite ćeliju u kojoj želite stvoriti padajući popis (ćelija C2 u ovom primjeru).
• Idite na Podaci -> Alati za podatke -> Provjera podataka.
• U dijaloškom okviru Validacija podataka na kartici Postavke odaberite Popis kao kriterij provjere valjanosti. Čim odaberete Popis, pojavit će se izvorno polje.
• U polje izvora unesite sljedeću formulu: = OFFSET ($ 2,0,0 $, COUNTIF ($ A $ 2: $ A $ 100, ""))
• Provjerite je li označena padajuća opcija U ćeliji.
• Pritisnite U redu.

U ovoj formuli zamijenio sam argument 5 sa COUNTIF ($ A $ 2: $ A $ 100, ””).

Funkcija COUNTIF broji ćelije koje nisu prazne u rasponu A2: A100. Stoga se funkcija OFFSET prilagođava tako da uključuje sve ćelije koje nisu prazne.

Bilješka:

• Da bi ovo funkcioniralo, NE smije biti praznih ćelija između ćelija koje su ispunjene.
• Ako želite stvoriti padajući popis u više ćelija odjednom, odaberite sve ćelije u kojima ga želite stvoriti, a zatim slijedite gore navedene korake. Uvjerite se da su reference ćelija apsolutne (kao što je $ A $ 2), a ne relativne (kao što su A2 ili A $ 2 ili $ A2).

Kopirajte zalijepljene padajuće liste u Excelu

Ćelije s provjerom valjanosti možete kopirati u druge ćelije, a također će se kopirati i provjera valjanosti podataka.

Na primjer, ako imate padajući popis u ćeliji C2, a želite ga primijeniti i na C3: C6, jednostavno kopirajte ćeliju C2 i zalijepite je u C3: C6. Ovo će kopirati padajući popis i učiniti ga dostupnim u C3: C6 (zajedno s padajućim izbornikom kopirat će se i oblikovanje).

Ako želite kopirati samo padajući izbornik, a ne oblikovanje, evo koraka:

• Kopirajte ćeliju koja ima padajući izbornik.
• Na padajućem izborniku odaberite ćelije u koje želite kopirati.
• Idite na Početna -> Zalijepi -> Posebno zalijepi.
• U dijaloškom okviru Zalijepi posebno odaberite Provjera u opcijama lijepljenja.
• Pritisnite U redu.

Ovo će kopirati samo padajući izbornik, a ne oblikovanje kopirane ćelije.

Oprez pri radu s padajućim popisom programa Excel

Morate biti oprezni pri radu s padajućim popisima u Excelu.

Kada kopirate ćeliju (koja ne sadrži padajući popis) preko ćelije koja sadrži padajući popis, padajući popis se gubi.

Najgore od svega je to što Excel neće prikazati nikakvo upozorenje ili upit koji bi korisniku dao do znanja da će padajući izbornik biti prebrisan.

Kako odabrati sve ćelije koje imaju padajući popis

Ponekad je teško znati koje ćelije sadrže padajući popis.

Stoga ima smisla označiti ove ćelije ili davanjem jasnih obruba ili boje pozadine.

Umjesto ručne provjere svih ćelija, postoji brz način odabira svih ćelija s padajućim popisima (ili bilo kojim pravilom provjere valjanosti podataka).

• Idite na Početna -> Pronađi i odaberi -> Idi na Posebno.
• U dijaloškom okviru Idi na posebno odaberite Validacija podataka
  • Validacija podataka ima dvije mogućnosti: sve i isto. Svi bi odabrali sve ćelije na koje je primijenjeno pravilo provjere valjanosti podataka. Isto bi odabralo samo one ćelije koje imaju isto pravilo provjere valjanosti podataka kao ono aktivne ćelije.
• Pritisnite U redu.

Time bi se odmah odabrale sve ćelije na koje je primijenjeno pravilo provjere valjanosti podataka (to uključuje i padajuće popise).

Sada možete jednostavno oblikovati ćelije (dati obrub ili boju pozadine) tako da budu vizualno vidljive i da slučajno ne kopirate drugu ćeliju na nju.

Evo još jedne tehnike Jona Acampore koju možete upotrijebiti kako bi ikona strelice padajućeg izbornika uvijek bila vidljiva. Također možete vidjeti neke načine kako to učiniti u ovom videu od gospodina Excela.

Stvaranje ovisnog / uvjetnog padajućeg popisa programa Excel

Evo videozapisa o tome kako stvoriti ovisni padajući popis u Excelu.

Ako više volite čitati nego gledati video, nastavite čitati.

Ponekad možete imati više od jednog padajućeg popisa i želite da stavke prikazane u drugom padajućem izborniku ovise o tome što je korisnik odabrao u prvom padajućem izborniku.

To se naziva ovisnim ili uvjetnim padajućim popisima.

Ispod je primjer uvjetnog/ovisnog padajućeg popisa:

U gornjem primjeru, kada stavke navedene u "padajućem izborniku 2" ovise o odabiru napravljenom u "padajućem izborniku 1".

Pogledajmo sada kako to stvoriti.

Evo koraka za stvaranje ovisnog / uvjetnog padajućeg popisa u Excelu:

• Odaberite ćeliju u kojoj želite prvi (glavni) padajući popis.
• Idite na Podaci -> Validacija podataka. Ovo će otvoriti dijaloški okvir za provjeru valjanosti podataka.
• U dijaloškom okviru za provjeru valjanosti podataka na kartici postavki odaberite Popis.
• U polju Izvor navedite raspon koji sadrži stavke koje će se prikazati na prvom padajućem popisu.
• Pritisnite U redu. Ovo će stvoriti padajući izbornik 1.
• Odaberite cijeli skup podataka (A1: B6 u ovom primjeru).
• Idite na Formule -> Definirani nazivi -> Stvori iz odabira (ili možete koristiti tipkovnički prečac Control + Shift + F3).
• U dijaloškom okviru "Kreiraj ime iz odabira" označite opciju Gornji red i poništite sve ostale. Time se stvaraju 2 raspona naziva ("Voće" i "Povrće"). Asortiman voća odnosi se na svo voće na popisu, a naziv Povrće na svo povrće na popisu.
• Pritisnite U redu.
• Odaberite ćeliju u kojoj želite ovisni/uvjetni padajući popis (E3 u ovom primjeru).
• Idite na Podaci -> Validacija podataka.
• U dijaloškom okviru Provjera valjanosti podataka na kartici postavki provjerite je li odabran Popis u.
• U polje Izvor unesite formulu = INDIREKTNO (D3). Ovdje je D3 ćelija koja sadrži glavni padajući izbornik.
• Pritisnite U redu.

Sada, kada odaberete u padajućem izborniku 1, opcije navedene u padajućem popisu 2 automatski će se ažurirati.

Preuzmite datoteku primjera

Kako ovo radi? - Uvjetni padajući popis (u ćeliji E3) odnosi se na = INDIREKTNO (D3). To znači da kada odaberete "Voće" u ćeliji D3, padajući popis u E3 odnosi se na imenovani raspon "Voće" (putem funkcije INDIREKT) i stoga navodi sve stavke u toj kategoriji.

Važna napomena pri radu s uvjetnim padajućim popisima u Excelu:

• Kada ste izvršili odabir, a zatim promijenili padajući izbornik nadređenog, ovisni padajući izbornik se ne bi promijenio i, prema tome, bio bi pogrešan unos. Na primjer, ako odaberete SAD kao državu, a zatim kao državu odaberete Floridu, a zatim se vratite i promijenite državu u Indiju, država će ostati kao Florida. Evo izvrsnog Debrinog vodiča o brisanju ovisnih (uvjetnih) padajućih popisa u Excelu kada se odabir promijeni.
• Ako je glavna kategorija više od jedne riječi (na primjer, "Sezonsko voće" umjesto "Voće"), tada morate koristiti formulu = INDIREKTNO (ZAMJENA (D3, "", "_")), umjesto jednostavna INDIRECT funkcija prikazana gore. Razlog tome je što Excel ne dopušta razmake u imenovanim rasponima. Dakle, kada kreirate imenovani raspon koristeći više od jedne riječi, Excel automatski umetne podcrtavanje između riječi. Tako bi naziv "Sezonsko voće" bio "Sezonsko voće". Korištenje funkcije SUBSTITUTE unutar funkcije INDIRECT osigurava razmake su pretvorene u podvlake.