Excelove proračunske tablice često uključuju padajuće ćelije za pojednostavljenje i / ili standardiziranje unosa podataka. Ovi padajući izbornici kreiraju se pomoću značajke provjere valjanosti podataka kako bi se odredio popis dopuštenih unosa.
Da biste postavili jednostavni padajući popis, odaberite ćeliju u koju ćete unijeti podatke, a zatim kliknite Provjera podataka (na kartici Podaci ), odaberite Provjera podataka, odaberite Popis (pod Dopusti :), a zatim unesite stavke popisa (odvojene zarezima) ) u polju Izvor : (vidi sliku 1).
U ovoj vrsti osnovnog padajućeg popisa, popis dopuštenih unosa je naveden unutar same provjere valjanosti podataka; stoga, da biste napravili promjene na popisu, korisnik mora otvoriti i urediti provjeru podataka. Međutim, to može biti teško za neiskusne korisnike ili u slučajevima kada je popis izbora dugotrajan.
Druga je mogućnost smjestiti popis u imenovani raspon unutar proračunske tablice, a zatim odrediti to ime raspona (prethodno označeno znakom jednakosti) u polju Izvor : provjera valjanosti podataka (kao što je prikazano na slici 2).
Ova druga metoda olakšava uređivanje izbora na popisu, ali dodavanje ili uklanjanje stavki može biti problematično. Budući da se imenovani raspon (FruitChoices, u našem primjeru) odnosi na fiksni raspon ćelija ($ H $ 3: $ H $ 10 kako je prikazano), ako se u ćelije H11 ili niže doda više izbora, neće se pojaviti u padajućem izborniku (budući da te ćelije nisu dio raspona FruitChoices).
Isto tako, ako se, primjerice, brišu unosi iz krušaka i jagoda, oni se više neće pojavljivati u padajućem izborniku, već će se u padajućem izborniku uključiti dva "prazna" izbora budući da se padajući izbornik još uvijek odnosi na cijeli raspon FruitChoices, uključujući prazne ćelije H9 i H10.
Iz tih razloga, kada koristite normalni imenovani raspon kao izvorni popis za padajući popis, imenovani raspon mora se urediti tako da uključuje više ili manje ćelija ako se unosi dodaju ili brišu s popisa.
Rješenje ovog problema je korištenje imena dinamičkog raspona kao izvora padajućeg izbora. Naziv dinamičkog raspona je onaj koji se automatski proširuje (ili sklapa) kako bi točno odgovarao veličini bloka podataka jer su unosi dodani ili uklonjeni. Da biste to učinili, za definiranje imenovanog raspona koristite formulu umjesto fiksnog raspona adresa ćelija.
Kako postaviti dinamički raspon u Excelu
Normalni (statični) naziv raspona odnosi se na određeni raspon ćelija ($ H $ 3: $ H $ 10 u našem primjeru, pogledajte dolje):
No, dinamički raspon definira se pomoću formule (vidi dolje, preuzeto iz zasebne proračunske tablice koja koristi nazive dinamičkog raspona):
Prije nego počnemo, provjerite jeste li preuzeli našu primjerak Excel datoteke (makronaredbe za sortiranje su onemogućene).
Pogledajmo ovu formulu detaljno. Mogućnosti za voće nalaze se u bloku ćelija neposredno ispod naslova ( VOĆE ). Taj je naslov također dodijeljen nazivu: Voće :
Cijela formula koja se koristi za definiranje dinamičkog raspona za izbor voća je:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (offset (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
VoćeZastavljanje se odnosi na zaglavlje koje je jedan redak iznad prvog unosa na popisu. Broj 20 (korišten dva puta u formuli) je maksimalna veličina (broj redaka) za popis (može se prilagoditi po želji).
Napominjemo da u ovom primjeru na popisu ima samo 8 unosa, ali ispod njih su i prazne ćelije u koje se mogu dodati dodatni unosi. Broj 20 odnosi se na cijeli blok u kojem se mogu unositi, a ne na stvarni broj unosa.
Sada razradimo formulu na dijelove (kodiranje u boji svaki komad), kako bismo razumjeli kako radi:
= OFFSET (VoćeHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( OFFSET (voće), 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1)
"Najdublji" komad je OFFSET (Voće, 1, 0, 20, 1) . Ovo upućuje na blok od 20 stanica (ispod ćelije voća) gdje se mogu unijeti izbori. Ova OFFSET funkcija u osnovi kaže: Započnite u FruitsHeading ćeliji, idite dolje 1 redak i preko 0 stupaca, a zatim odaberite područje koje je 20 redaka dugačko i 1 stupac širok. To nam daje blok od 20 redova gdje se unose izbori voća.
Sljedeći dio formule je ISBLANK funkcija:
= OFFSET (VoćeHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK (gore navedeno), 0, 0), 0) -1, 20), 1)
Ovdje je OFFSET funkcija (objašnjena gore) zamijenjena s “gore” (kako bi se olakšalo čitanje). Međutim, funkcija ISBLANK-a radi na 20-rednom rasponu ćelija koje definira funkcija OFFSET.
ISBLANK zatim kreira skup od 20 TRUE i FALSE vrijednosti, ukazujući na to je li svaka od pojedinačnih ćelija u rasponu od 20 redova na koje upućuje OFFSET funkcija prazna (prazna) ili ne. U ovom primjeru, prvih 8 vrijednosti u skupu će biti FALSE budući da prvih 8 stanica nisu prazne, a posljednjih 12 vrijednosti će biti TRUE.
Sljedeći dio formule je funkcija INDEX:
= OFFSET (VoćeHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (gore, 0, 0), 0) -1, 20), 1)
Opet, "gore" odnosi se na ISBLANK i OFFSET funkcije opisane gore. Funkcija INDEX vraća niz koji sadrži 20 vrijednosti TRUE / FALSE koje stvara ISBLANK funkcija.
INDEX se obično koristi za odabir određene vrijednosti (ili raspona vrijednosti) iz bloka podataka, navođenjem određenog retka i stupca (unutar tog bloka). Ali postavljanje unosa retka i stupca na nulu (kao što je ovdje učinjeno) uzrokuje da INDEX vrati niz koji sadrži cijeli blok podataka.
Sljedeći dio formule je MATCH funkcija:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (TRUE, gore, 0) -1, 20), 1)
MATCH funkcija vraća poziciju prve TRUE vrijednosti, unutar polja koje vraća funkcija INDEX. Budući da prvih 8 unosa na popisu nisu prazni, prvih 8 vrijednosti u nizu bit će FALSE, a deveta vrijednost će biti TRUE (budući da je 9. red u rasponu prazan).
Tako će funkcija MATCH vratiti vrijednost 9 . U ovom slučaju, međutim, doista želimo znati koliko je unosa na popisu, pa formula oduzima 1 od MATCH vrijednosti (koja daje položaj posljednjeg unosa). Dakle, na kraju, MATCH (TRUE, gore, 0) -1 vraća vrijednost 8 .
Sljedeći dio formule je funkcija IFERROR:
= OFFSET (voće, 1, 0, IFERROR (gore, 20), 1)
Funkcija IFERROR vraća alternativnu vrijednost, ako prva navedena vrijednost rezultira pogreškom. Ova funkcija je uključena jer, ako je cijeli blok stanica (svih 20 redaka) ispunjen unosima, MATCH funkcija će vratiti pogrešku.
To je zato što poručujemo MATCH funkciji da traži prvu TRUE vrijednost (u nizu vrijednosti iz ISBLANK funkcije), ali ako NONE od stanica nije prazna, cijeli niz će biti ispunjen FALSE vrijednostima. Ako MATCH ne može pronaći ciljnu vrijednost (TRUE) u nizu koji traži, vraća pogrešku.
Dakle, ako je cijeli popis pun (i stoga, MATCH vraća pogrešku), funkcija IFERROR će umjesto toga vratiti vrijednost 20 (znajući da na popisu mora biti 20 unosa).
Konačno, OFFSET (FruitsHeading, 1, 0, gore, 1) vraća raspon koji zapravo tražimo: Počnite u FruitsHeading ćeliji, idite dolje 1 red i preko 0 stupaca, a zatim odaberite područje koje ima mnogo redaka na popisu se nalaze unosi (i široki 1 stupac). Tako će cijela formula zajedno vratiti raspon koji sadrži samo stvarne unose (sve do prve prazne ćelije).
Koristeći ovu formulu za definiranje raspona koji je izvor za padajući izbornik, možete slobodno urediti popis (dodavanje ili uklanjanje unosa, sve dok preostali unosi počinju u gornjoj ćeliji i susjedni su), a padajući izbornik uvijek će odražavati trenutnu (vidi sliku 6).
Datoteka primjera (Dinamički popisi) koja je ovdje korištena uključena je i može se preuzeti s ove web-lokacije. Međutim, makronaredbe ne rade jer WordPress ne voli Excelove knjige s makronaredbama u njima.
Kao alternativa specificiranju broja redaka u bloku popisa, bloku popisa može se dodijeliti vlastito ime raspona, koji se zatim može koristiti u modificiranoj formuli. U primjeru datoteke drugi popis (Imena) koristi ovu metodu. Ovdje je cijeli blok popisa (ispod naslova "IMENA", 40 redaka u datoteci primjera) dodijeljen naziv raspona NameBlock . Alternativna formula za definiranje NamesList je tada:
= OFFSET (NamesHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX) (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1)
gdje NamesBlock zamjenjuje OFFSET (FruitsHeading, 1, 0, 20, 1) i ROWS (NamesBlock) zamjenjuje 20 (broj redaka) u ranijoj formuli.
Dakle, za padajuće popise koji se mogu lako uređivati (uključujući i druge korisnike koji mogu biti neiskusni), pokušajte koristiti nazive dinamičkog raspona! I imajte na umu da, iako je ovaj članak fokusiran na padajuće popise, imena dinamičkog raspona mogu se koristiti bilo gdje da biste se pozvali na raspon ili popis koji se mogu razlikovati po veličini. Uživati!
