Izradite povezane padajuće liste u Excelu - najlakši način! Kako stvoriti padajući popis u Excelu s automatskom zamjenom podataka. Kako napraviti padajući popis s dodanim podacima

PREDSTAVITE STANJE:Želimo napraviti malu tablicu u Excelu u kojoj možete odabrati državu i odgovarajući grad. Istodobno, pomoću padajućih popisa, potrebno je ograničiti dostupna korisnicima opcije zemalja i gradova iz kojih mogu birati. U prvoj ćeliji ćemo odabrati državu, au drugoj ćeliji biti dostupni samo gradovi koji pripadaju odabranoj zemlji. Mislim da je razumljivo?

Započnimo naš jednostavan primjer kako možete stvoriti povezani (ili ovisni) padajući popis u Excelu? U ćeliji B1 birat ćemo državu, i to u ćeliji B2- grad koji joj pripada, kao u primjeru:

Prvo morate stvoriti bazu podataka. Na drugom listu sam u prvom padajućem popisu upisao popis zemalja koje korisnicima želim dati na izbor, a u susjednom stupcu sam označio brojčani indeks koji odgovara jednom od popisa gradova. Popisi gradova nalaze se desno u stupcima D, F I H. Tako npr. pored Francuska indeks troškova 2 , što odgovara popisu gradova 2 . Kasnije ćete vidjeti kako će se ovaj indeks koristiti.

Ako radite u programu Excel 2010, možete izraditi izvorni list u zasebnom radna bilježnica. Ako imate verziju programa Excel 2003 i planirate koristiti imenovani raspon, tada vrijednosti moraju biti u istoj radnoj knjizi ili na drugom listu.

Koristit ćemo imenovane raspone i učiniti da ovi povezani padajući izbornik rade u svima Excel verzije. Sljedeći korak je stvaranje imenovanih raspona za naše popise. Na kartici Formule(Formule) imaju naredbu ime menadžer(menadžer imena). Klikom na njega otvorit će se dijaloški okvir. ime menadžer(menadžer imena).

Kliknite gumb Novi(Novo) za dodavanje novog imenovanog raspona. Otvorit će se dijaloški okvir novo ime(Izrada imena).

U polju Ime(Ime) unesite ime Zemlja za naš prvoimenovani raspon i na terenu Odnosi se na(Raspon) odaberite onu koja pohranjuje popis zemalja:

List3!$A$3:$A$5

Rasponi koji sadrže gradove mogu se imenovati na potpuno isti način.

Sada možemo kreirati padajuće liste u onim ćelijama gdje smo planirali odabrati podatke. Označite ćeliju B1(u njemu ćemo odabrati državu), otvorite karticu Podaci(Podaci), kliknite Provjera valjanosti podataka(Provjera valjanosti podataka), a zatim s padajućeg izbornika odaberite Provjera valjanosti podataka(Provjera valjanosti podataka).

Otvorit će se dijaloški okvir Provjera valjanosti podataka(Provjera ulaznih vrijednosti).

Želimo korisniku dati popis opcija za odabir, dakle u polju dopustiti(vrsta podataka) odaberite Popis(Popis). Ovo će aktivirati polje Izvor(Izvor), gdje trebate navesti naziv raspona sa zemljama. Unesite "=Country" u ovo polje i kliknite u redu. Sada moramo napraviti drugi padajući izbornik kako bi korisnici mogli odabrati grad. Stavit ćemo ovaj padajući izbornik u ćeliju B2. A sada pozornost - fokus! Moramo provjeriti sadržaj ćelije s nazivom države (ćelija B1) kako bismo dobili indeks koji odgovara bazi podataka gradova. Ako korisnik odabere Portugal, tada se moramo pozivati ​​na bazu s indeksom 3 , koji pohranjuje imena gradova u Portugalu. Koristit ćemo funkciju VLOOKUP(VLOOKUP) za traženje vrijednosti iz ćelije B1 u tablici s nazivima zemalja. Nakon što je indeks poznat, odabrat ćemo popis koji će biti izvor podataka za naš drugi padajući popis. Da bismo to učinili, pišemo sljedeću formulu:

ODABIR (VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),Engleska,Francuska,Portugal)
=SELECT(VLOOKUP(B1;Stab3!$A$3:$B$5;2;FALSE);Engleska;Francuska;Portugal)

Što čini ova formula? Ona traži vrijednost iz ćelije B1 na popisu zemalja i vraća odgovarajući indeks, koji zatim koristi funkcija ODABIRATI(SELECT) za odabir 1., 2. ili 3. imenovanog raspona.

Ovako će izgledati naš drugi padajući izbornik:

Microsoft Excel- izvrstan alat za rad s tabličnim podacima. Pomoću njega možete brzo stvoriti odgovarajuću tablicu i ispuniti je podacima. Istodobno, Excel pojednostavljuje ne samo rad s podacima u tablici, već i proces popunjavanja tablice.

U ovom materijalu pričat ćemo o popunjavanju stola. Ovdje možete naučiti kako napraviti padajući popis u Excelu, koji će vam omogućiti puno brže popunjavanje tablice podacima. Uputa će biti relevantna za Excel 2007, 2010, 2013 i 2016.

Najlakši način za izradu padajućeg popisa u Excelu je korištenje funkcije provjere valjanosti podataka. Da biste stvorili ovaj padajući popis, prvo morate učiniti redovita lista s podacima i smjestite ih u Excel dokument. Takav popis može se postaviti na isti list dokumenta gdje će se nalaziti padajući popis ili na bilo koji drugi list.

Dakle, prvo stvaramo popis podataka koji bi se trebali nalaziti na padajućem popisu i stavljamo ga na bilo koje prikladno mjesto u Excel dokumentu. Na primjer, možete postaviti takav popis iza područja za ispis ili na drugi list Excel dokumenta.

Nakon toga odaberite ćeliju u kojoj želite napraviti padajući popis. Odaberite ovu ćeliju mišem i kliknite na gumb "Provjera valjanosti podataka" na kartici "Podaci".

Ovo će otvoriti prozor Provjeri ulazne vrijednosti. U ovom prozoru prvo trebate otvoriti padajući popis "Vrsta podataka" i tamo odabrati opciju "Popis".

Zatim morate ispuniti redak "Izvor".

Da biste to učinili, postavite pokazivač na redak "Izvor", a zatim mišem odaberite popis podataka koji bi trebao biti na stvorenom padajućem popisu.

Nakon što odredite izvor, zatvorite prozor "Provjeri ulazne vrijednosti" klikom na gumb "U redu". Padajući popis u Excelu je napravljen i može se provjeriti.

Ali sada unutra datu ćeliju možete unijeti samo podatke koji se nalaze na padajućem popisu. A ako pokušate unijeti netočnu vrijednost, dobit ćete poruku o pogrešci.

Ako želite ostaviti mogućnost unosa podataka u ćeliju koja nije sadržana u padajućem popisu, tada trebate odabrati ćeliju s padajućim popisom i kliknuti na gumb "Provjera valjanosti podataka". Zatim, u prozoru koji se otvori, trebate otići na karticu "Poruka o pogrešci" i poništiti okvir pored funkcije "Prikaži poruku o pogrešci".

Nakon spremanja ovih postavki, u ćeliju možete unijeti ne samo podatke koji su dostupni na padajućem popisu, već i sve druge podatke koji su vam potrebni.

Kako napraviti padajući popis s dodanim podacima

Gore opisana opcija padajućeg popisa prilično je zgodna. Ali, ako mu želite redovito dodavati nove podatke, onda ova opcija neće raditi, jer ćete nakon svakog dodavanja podataka morati mijenjati raspon koji je naveden u polju "Izvor". Ovaj problem možete riješiti pomoću značajke Smart Tables, koja je predstavljena u programu Microsoft Excel 2007.

Da biste to učinili, morate napraviti popis s podacima, baš kao što je gore opisano. Jedina razlika je u tome što bi popis sada trebao imati naslov.

Nakon izrade popisa, trebate ga odabrati i upotrijebiti gumb "Formatiraj kao tablicu" na kartici "Početna" da biste na popis primijenili bilo koji stil.

Nakon odabira stila, pojavit će se prozor Format Table. Ovdje morate označiti okvir pored funkcije "Tablica sa zaglavljem" i kliknuti "U redu".

Kao rezultat, trebali biste dobiti tablicu s podacima, nešto kao na slici ispod.

Sada trebate odabrati ćeliju u kojoj želite napraviti padajući popis, te kliknuti na gumb "Provjera valjanosti podataka" na kartici "Podaci". U prozoru koji se otvori odaberite "Vrsta podataka - Popis", a zatim postavite pokazivač u redak "Izvor" i mišem odaberite popis s podacima koji se trebaju koristiti za padajući popis (ne trebate za odabir zaglavlja popisa).

Ova metoda kreiranja padajućih popisa s mogućnošću dodavanja novih redaka testirana je u Excelu 2010, ali bi također trebala funkcionirati u Excelu 2007. Kao u više moderne verzije Excel, kao što su Excel 2013 i Excel 2016.

Postoji nekoliko načina za stvaranje padajućeg popisa. Odabir jednog od njih ovisi o strukturi podataka kojima raspolažete.

Prvi način stvaranja popisa na dvije razine

Prva metoda temelji se na stvaranju "pametne" tablice, čiji naslov sadrži vrijednosti prvog padajućeg popisa (grupe), a redovi tablice odgovaraju vrijednostima druge padajući popis (podgrupa). Vrijednosti elemenata podgrupe moraju se staviti u odgovarajući stupac grupe, kao na slici ispod.

Sada krenimo stvarati prvi padajući popis grupe (u mom slučaju popis zemalja):

1. Odaberite ćeliju u koju ćete umetnuti padajući popis;
2. Idite na karticu vrpce Podaci;
3. Odaberite tim Provjera valjanosti podataka;
4. Odaberite vrijednost s padajućeg popisa Popis;
5. U polju Izvor navedite sljedeću formulu =INDIRECT("Tablica1[#Zaglavlja]").

Formula NEIZRAVNO vraća referencu na niz pametnih zaglavlja tablice. Prednost korištenja takve tablice je da kada se dodaju stupci, padajući popis će se automatski proširiti.

Ostaje stvoriti drugi ovisni padajući popis - popis podskupina.

Slobodno ponovite prve 4 gore opisane točke. izvor u prozoru Provjera valjanosti podataka za drugi padajući popis bit će formula =INDIRECT("Tablica1["&F2&"]"). stanica F2 u ovaj slučaj- vrijednost prve padajuće liste.

Također možete koristiti normalnu "ne-pametnu" tablicu, ali u ovom slučaju ćete morati ručno promijeniti raspon naslova i redaka. U ovom primjeru to se događa automatski.

Drugi način stvaranja popisa na dvije razine

Druga metoda je prikladna za korištenje kada su podaci padajućeg popisa napisani u dva stupca. Prvi sadrži naziv grupe, a drugi - podskupine.

VAŽNO! Prije stvaranja ovisni popis po podskupinama potrebno je izvornu tablicu sortirati po prvom stupcu (stupac s grupom), tada će biti jasno zašto se to radi.

Za izradu padajućih grupa potreban nam je dodatni stupac koji sadrži jedinstvene vrijednosti grupe iz izvorne tablice. Za izradu ovog popisa upotrijebite značajku Ukloni duplikate ili upotrijebite naredbu Unique iz dodatka VBA-Excel.

Sada napravimo padajući popis grupa. Da biste to učinili, slijedite prve 4 točke iz prve metode za izradu popisa na dvije razine. Kao izvor odrediti raspon jedinstvene vrijednosti grupe. Ovdje je sve standardno.

Preporuka: prikladno je navesti imenovani raspon kao izvor. Da biste ga stvorili, otvorite Upravitelj imena iz tab Formule i imenujte raspon jedinstvenim vrijednostima.

Sada je najteže precizirati izvor dinamička veza na raspon s vrijednostima drugog padajućeg popisa (popis podgrupa). Riješit ćemo ga pomoću funkcije OFFSET(referenca, row_offset, column_offset, [visina], [width]) A koji vraća referencu na raspon koji je zadani broj redaka i stupaca udaljen od ćelije ili raspona ćelija.

• Veza u našem slučaju - 1 $A$- gornji lijevi kut izvorne tablice;
• line_offset - UTAKMICA(F3,$A$1:$A$67,0)-1- broj retka s vrijednošću željene grupe (u mom slučaju, ćelija zemlje F3) minus jedan;
• Offset_by_columns - 1 - budući da nam je potreban stupac s podgrupama (gradovi);
• [Visina] - COUNTIF($A$1:$A$67,F3)- broj podskupina u željenoj skupini (broj gradova u zemlji F3);
• [Širina] - 1 - jer je ovo širina našeg stupca s podskupinama.

Padajući popis u Excelu je možda jedan od najvažnijih pogodne načine rad s podacima. Možete ih koristiti i za ispunjavanje obrazaca i za izradu nadzornih ploča i velikih tablica. Padajući popisi često se koriste u aplikacijama na pametnim telefonima, web stranicama. Intuitivni su za prosječnog korisnika.

Kliknite donji gumb za preuzimanje datoteke s primjerima padajućeg popisa u Excelu:

Video tutorial

Kako napraviti padajući popis u Excelu na temelju podataka s popisa

Zamislite da imamo popis voća:

Da bismo stvorili padajući popis, moramo to učiniti Sljedeći koraci:

• Idi na karticu " Podaci” => odjeljak “ Rad s podacima ” na alatnoj traci => odaberite stavku “ Provjera valjanosti podataka “.

• U skočnom prozoru Validacija ulaznih vrijednosti ” na kartici “ Parametri” u vrsti podataka odaberite “ Popis “:

• U polju" Izvor” unesite niz naziva voća =$A$2:$A$6 ili samo stavite pokazivač miša u polje za unos vrijednosti " Izvor”, a zatim mišem odaberite raspon podataka:

Ako želite stvoriti padajuće popise u više ćelija odjednom, odaberite sve ćelije u kojima ih želite stvoriti, a zatim slijedite gore navedene korake. Važno je osigurati da su reference ćelije apsolutne (na primjer, 2 A$), a ne relativno (npr. A2 ili 2 A$ ili $A2 ).

Kako napraviti padajući popis u Excelu pomoću ručnog unosa podataka

U gornjem primjeru upisali smo popis podataka za padajući popis odabirom raspona ćelija. Osim ove metode, možete unijeti podatke za ručno kreiranje padajućeg popisa (nije ih potrebno pohranjivati ​​ni u jednu ćeliju).

Na primjer, recimo da želimo prikazati dvije riječi "Da" i "Ne" u padajućem izborniku. Za ovo nam je potrebno:

• Odaberite ćeliju u kojoj želimo stvoriti padajući popis;
• Idi na karticu " Podaci” => odjeljak “ Rad s podacima ” na alatnoj traci => odaberite stavku “ Provjera valjanosti podataka “:

• U skočnom prozoru Validacija ulaznih vrijednosti ” na kartici “ Parametri” u vrsti podataka odaberite “ Popis “:

• U polju" Izvor” unesite vrijednost “Da; Ne".
• Pritisnite " u redu “

Nakon toga, sustav će stvoriti padajući popis u odabranoj ćeliji. Sve stavke navedene u polju “ Izvor“ odvojeno točkom-zarezom odrazit će se u različitim redcima padajućeg izbornika.

Ako želite stvoriti padajući popis u nekoliko ćelija u isto vrijeme, odaberite potrebne ćelije i slijedite gornje upute.

Kako stvoriti padajući popis u Excelu pomoću funkcije OFFSET

Uz gore opisane metode, možete koristiti i formulu za izradu padajućih popisa.

Na primjer, imamo popis s popisom voća:

Da biste napravili padajući popis pomoću formule, trebate učiniti sljedeće:

• Odaberite ćeliju u kojoj želimo stvoriti padajući popis;
• Idi na karticu " Podaci” => odjeljak “ Rad s podacima ” na alatnoj traci => odaberite stavku “ Provjera valjanosti podataka “:

• U skočnom prozoru Validacija ulaznih vrijednosti ” na kartici “ Parametri” u vrsti podataka odaberite “ Popis “:

• U polju" Izvor” unesite formulu: =OFFSET(A$2$;0;0;5)
• Pritisnite " u redu “

Sustav će stvoriti padajući popis s popisom voća.

Kako ova formula funkcionira?

U gornjem primjeru koristili smo formulu =OFFSET(referenca,line_offset,stupac_offset,[visina],[width]).

Ova funkcija sadrži pet argumenata. U argumentu " veza” (u primjeru $A$2) označava od koje ćelije započeti pomak. U argumentima “line_offset"I “offset_by_columns”(vrijednost “0” je naznačena u primjeru) – koliko redaka/stupaca treba pomaknuti za prikaz podataka. U argumentu " [visina]” je postavljeno na “5”, što označava visinu raspona ćelija. Argument" [širina]” ne navodimo, budući da se u našem primjeru raspon sastoji od jednog stupca.

Koristeći ovu formulu, sustav vam vraća kao podatke za padajući popis raspon ćelija počevši od ćelije $A$2, a sastoji se od 5 ćelija.

Kako napraviti padajući popis u Excelu sa zamjenom podataka (koristeći funkciju OFFSET)

Ako koristite formulu u gornjem primjeru za izradu popisa, tada ćete stvoriti popis podataka koji je fiksiran u određenom rasponu ćelija. Ako želite dodati bilo koju vrijednost kao element popisa, formulu ćete morati ručno prilagoditi. U nastavku ćete naučiti kako napraviti dinamički padajući popis koji će automatski učitavati nove podatke za prikaz.

Za izradu popisa trebat će vam:

• Odaberite ćeliju u kojoj želimo stvoriti padajući popis;
• Idi na karticu " Podaci” => odjeljak “ Rad s podacima ” na alatnoj traci => odaberite stavku “ Provjera valjanosti podataka “;
• U skočnom prozoru Validacija ulaznih vrijednosti ” na kartici “ Parametri” u vrsti podataka odaberite “ Popis “;
• U polju" Izvor” unesite formulu: =OFFSET(A$2$,0,0,COUNTIF($A$2:$A$100;”<>”))
• Pritisnite " u redu “

U ovoj formuli, u argumentu „[ visina]” navodimo kao argument koji označava visinu popisa s podacima - formulu koja izračunava u zadanom rasponu A2:A100 količina ne prazne ćelije.

Bilješka: za ispravan rad formule, važno je da na popisu podataka nema praznih redaka za prikaz u padajućem izborniku.

Kako stvoriti padajući popis u Excelu s automatskom zamjenom podataka

Kako bi se novi podaci automatski učitavali na padajući popis koji ste izradili, morate učiniti sljedeće:

• Izrađujemo popis podataka za prikaz na padajućem popisu. U našem slučaju, ovo je popis boja. Odaberite popis lijevom tipkom miša:

• Na alatnoj traci kliknite " Oblikujte kao tablicu “:

• S padajućeg izbornika odaberite stil tablice:

• Pritiskom na tipku " u redu” u skočnom prozoru potvrdite odabrani raspon ćelija:

• Zatim odaberite raspon podataka tablice za padajući popis i dajte mu naziv na lijevoj margini iznad stupca "A":

Tablica podataka je spremna, sada možemo stvoriti padajući popis. Za ovo vam je potrebno:

• Odaberite ćeliju u kojoj želimo stvoriti popis;
• Idi na karticu " Podaci” => odjeljak “ Rad s podacima ” na alatnoj traci => odaberite stavku “ Provjera valjanosti podataka “:

• U skočnom prozoru Validacija ulaznih vrijednosti ” na kartici “ Parametri” u vrsti podataka odaberite “ Popis “:

• U polje izvora unesite =”ime vašeg stola” . U našem slučaju, nazvali smo ga “ Popis “:

• Spreman! Padajući popis je kreiran, prikazuje sve podatke iz navedene tablice:

• Da biste dodali novu vrijednost na padajući popis, jednostavno dodajte informacije u sljedeću ćeliju nakon tablice podataka:

• Tablica će automatski proširiti svoj raspon podataka. Padajući popis bit će ažuriran u skladu s novom vrijednošću iz tablice:

Kako kopirati padajući popis u Excelu

U Excelu je moguće kopirati stvorene padajuće liste. Na primjer, u ćeliji A1 imamo padajući popis koji želimo kopirati u raspon ćelija A2:A6 .

Da biste kopirali padajući popis s trenutnim oblikovanjem:

• kliknite lijevu tipku miša na ćeliju s padajućim popisom koji želite kopirati;
• CTRL+C ;
• odaberite ćelije u rasponu A2:A6 gdje želite umetnuti padajući popis;
• pritisnite tipkovnički prečac CTRL+V .

Dakle, kopirat ćete padajući popis i spremiti izvornom formatu popis (boja, font, itd.). Ako želite kopirati/zalijepiti padajući popis bez spremanja formata, tada:

• kliknite lijevu tipku miša na ćeliju s padajućim popisom koji želite kopirati;
• pritisnite tipkovnički prečac CTRL+C ;
• odaberite ćeliju u koju želite umetnuti padajući popis;
• klik desni gumb miš => pokrenite padajući izbornik i kliknite " Poseban umetak “;

• U prozoru koji se pojavi, pod " Umetnuti” odaberite stavku “ uvjeti na vrijednosti “:

• kliknite " u redu “

Nakon toga, Excel će kopirati samo podatke s padajućeg popisa, bez očuvanja formatiranja izvorne ćelije.

Kako odabrati sve ćelije koje sadrže padajući popis u Excelu

Ponekad je teško razumjeti koliko stanica ima Excel datoteka sadrže padajuće liste. Postoji jednostavan način da ih prikažete. Za ovo:

• Kliknite na karticu " Dom” na alatnoj traci;
• kliknite " Pronađite i odaberite ” i odaberite “ Odaberite grupu ćelija “:

• U dijaloškom okviru odaberite " Provjera valjanosti podataka “. U ovom polju moguće je odabrati stavke “ svi"i" isto “. “svi” će odabrati sve padajuće liste na listu. Stavak " isto” će prikazati padajuće liste slične po sadržaju podacima u padajućem izborniku. U našem slučaju biramo " svi “:

Napravite padajući popis u Excelu jednostavno. Mogu li napraviti povezane padajuće liste u Excelu . Kada popis u drugom stupcu ovisi o vrijednosti u ćeliji prvog stupca. Kako to učiniti u padajućem izborniku koji ovise o Excelu, pročitajte članak "Povezani padajući popisi u Excelu".
Možete napraviti tako da će slova abecede prvo ispasti u padajućem popisu, klikom na odabrano slovo prikazat će se popis riječi, prezimena koja počinju ovim slovom. Pročitajte o tome u člankuPovezani padajući popis u Excelu po abecednom redu" .
Možete li napraviti padajuću listu u excelu dodatni popis s podacima. Pogledajte "Kako napraviti padajući popis u Excelu bez okvira s popisom".
Kada kreiramo složene tablice, upitnike, anketne listove, druge dokumente, možete postaviti padajući popis u ćelije. To je kada kliknemo na ćeliju i pojavi se popis s kojeg biramo željenu opciju riječi. Na primjer, da.
Ovdje su nazivi proizvoda navedeni na padajućem popisu.
Dakle, prvo kreiramo popis u stupcu, negdje ne u ovoj tablici. Potpisujemo stupac - to će biti budući naziv raspona. To je prikladno učiniti kada postoji nekoliko različitih popisa.Za više informacija o tome kako stvoriti raspon i dati mu ime, pogledajte članak "Raspon u Excelu» .
Popis se također može napisati u stupac iste tablice. Ali, onda, sakrijte stupac, popis će raditi. Kako sakriti stupac, pogledajte članak "Kako sakriti stupce u Excelu".
Sve. Imamo popis, dali smo ime ovom rasponu. Sada postavljamo ovaj popis na ćeliju.Stavljamo kursor u ćeliju u kojoj ćemo napraviti padajuću listu. Idemo na karticu "Podaci" u odjeljku "Rad s podacima", kliknite na gumb "Provjera valjanosti podataka", odaberite "Provjera valjanosti podataka". U dijaloškom okviru koji se pojavi, u retku "vrsta podataka" - odaberite "Popis".U retku "Izvor" - navedite naziv našeg asortimana,ali ispred imena stavljamo znak "jednako". Ispalo je ovako.

Na ovaj način je moguće napraviti nepovezane višerazinske padajuće liste u excelu. Na primjer, imamo takvu tablicu s podacima.

Raspon popisa može se učiniti dinamičkim. Pogledajte članak "Za automatsku promjenu veličine Excel tablice". nazovimo dinamički raspon popis ćelija J1:J6 "Jedinice". Dijaloški okvir "Kreiraj ime" popunjava se na sljedeći način.