Excelove preglednice pogosto vključujejo spustne celice za poenostavitev in / ali standardizacijo vnosa podatkov. Ti spustni stolpci se ustvarijo s funkcijo za preverjanje podatkov, da določite seznam dovoljenih vnosov.
Za nastavitev preprostega spustnega seznama izberite celico, v katero želite vnesti podatke, nato kliknite Validacija podatkov (na zavihku Podatki ), izberite možnost Validacija podatkov, izberite Seznam (pod Dovoli :) in vnesite elemente seznama (ločene z vejicami) ) v polju Vir : (glej sliko 1).
V tej vrsti osnovnega spustnega seznama je seznam dovoljenih vnosov določen znotraj same validacije podatkov; zato mora uporabnik za spremembe seznama odpreti in urediti preverjanje veljavnosti podatkov. To pa je lahko težavno za neizkušene uporabnike ali v primerih, ko je seznam izbir dolg.
Druga možnost je, da postavite seznam v imenovano območje znotraj preglednice in nato podate, da ime razpona (označeno z znakom enakosti) v polju Vir : preverjanje veljavnosti podatkov (kot je prikazano na sliki 2).
Ta druga metoda olajša urejanje izbir na seznamu, vendar je dodajanje ali odstranjevanje elementov lahko problematično. Ker se imenovani razpon (FruitChoices, v našem primeru) nanaša na določeno območje celic ($ H $ 3: $ H $ 10, kot je prikazano), če se v celice H11 ali spodaj doda več možnosti, se ne bodo prikazale v spustnem meniju (ker te celice niso del razpona FruitChoices).
Podobno, če so na primer izbrisani vnosi Pears in Strawberries, se ne bodo več pojavljali v spustnem meniju, ampak bo spustni meni vključeval dve "prazni" izbiri, saj se spustna mapa še vedno sklicuje na celotno območje FruitChoices, vključno s praznimi celicami H9 in H10.
Zaradi teh razlogov, ko uporabite normalno imenovano območje kot vir seznama za spustni meni, morate imenovani razpon urediti tako, da bo vključeval več ali manj celic, če so vnosi dodani ali izbrisani s seznama.
Rešitev tega problema je uporaba imena dinamičnega razpona kot izvora za spustne izbire. Ime dinamičnega razpona je tisto, ki se samodejno razširi (ali pogodbeno) in se natančno ujema z velikostjo bloka podatkov, ko so vpisi dodani ali odstranjeni. To naredite tako, da uporabite formulo in ne določen obseg naslovov celic, da določite imenovani obseg.
Kako namestiti dinamični razpon v Excelu
Normalno (statično) ime obsega se nanaša na določen obseg celic ($ H $ 3: $ H $ 10 v našem primeru, glej spodaj):
Vendar pa je dinamični razpon določen s formulo (glej spodaj, vzeto iz ločene preglednice, ki uporablja imena dinamičnih razponov):
Preden začnemo, poskrbite, da boste prenesli našo datoteko v Excelu (razvrščanje makrov je bilo onemogočeno).
Poglejmo to formulo v podrobnosti. Izbira za sadje je v nizu celic neposredno pod naslovom ( FRUITS ). Temu naslovu je dodeljeno tudi ime: SadjeHeading :
Celotna formula, ki se uporablja za definiranje dinamičnega razpona za izbire Fruits, je:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH) (TRUE, INDEX (ISBLANK (OFFSET) (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
FruitsHeading se nanaša na naslov, ki je ena vrstica nad prvim vnosom na seznamu. Številka 20 (uporabljena dvakrat v formuli) je največja velikost (število vrstic) za seznam (to lahko prilagodite po želji).
Upoštevajte, da je v tem primeru na seznamu samo 8 vnosov, spodaj pa so prazne celice, kjer lahko dodate dodatne vnose. Številka 20 se nanaša na celoten blok, kjer se lahko izvedejo vnosi, ne na dejansko število vnosov.
Zdaj pa razčlenimo formulo na koščke (barvno kodiranje vsakega dela), da razumemo, kako deluje:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH) (TRUE, INDEX (ISBLANK ( OFFSET) (FruitsHeading, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1)
"Notranji" kos je OFFSET (sadjeHeading, 1, 0, 20, 1) . To se nanaša na blok 20 celic (pod celico FruitsHeading), kjer lahko vnesete izbire. Ta funkcija OFFSET v bistvu pravi: Začni v celici FruitsHeading, pojdi dol 1 vrstico in več kot 0 stolpcev, nato izberite območje, ki je dolgo 20 vrstic in 1 stolpec širok. To nam daje 20-vrstični blok, kjer so vnesene izbire sadja.
Naslednji del formule je funkcija ISBLANK :
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH) (TRUE, INDEX ( ISBLANK (zgoraj), 0, 0), 0) -1, 20), 1)
Tukaj je bila funkcija OFFSET (pojasnjeno zgoraj) nadomeščena z "zgoraj" (da bi stvari lažje prebrali). Vendar pa funkcija ISBLANK deluje na območju 20 vrstic celic, ki jih definira funkcija OFFSET.
ISBLANK nato ustvari 20 vrednosti TRUE in FALSE, kar kaže, ali je vsaka posamezna celica v obsegu 20 vrstic, na katero se sklicuje funkcija OFFSET, prazna ali prazna. V tem primeru bodo prvih 8 vrednosti v nizu FALSE, ker prvih 8 celic ni praznih in zadnjih 12 vrednosti bo TRUE.
Naslednji del formule je funkcija INDEX:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (zgoraj, 0, 0), 0) -1, 20), 1)
Še enkrat, "zgoraj" se nanaša na ISBLANK in OFFSET funkcije, opisane zgoraj. Funkcija INDEX vrne matriko, ki vsebuje 20 vrednosti TRUE / FALSE, ki jih ustvari funkcija ISBLANK.
INDEX se običajno uporablja za izbiro določene vrednosti (ali obsega vrednosti) iz bloka podatkov, tako da poda določeno vrstico in stolpec (znotraj tega bloka). Toda nastavitev vnosov vrstic in stolpcev na nič (kot je to storjeno tukaj) povzroči, da INDEX vrne polje, ki vsebuje celoten blok podatkov.
Naslednji del formule je funkcija MATCH:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (TRUE, zgoraj, 0) -1, 20), 1)
Funkcija MATCH vrne položaj prve vrednosti TRUE znotraj polja, ki ga vrne funkcija INDEX. Ker prvih 8 vnosov na seznamu ni praznih, bo prvih 8 vrednosti v matriki FALSE, deveta vrednost pa TRUE (ker je 9. vrstica v območju prazna).
Torej bo funkcija MATCH vrnila vrednost 9 . V tem primeru pa resnično želimo vedeti, koliko vnosov je na seznamu, zato formula odšteje 1 od vrednosti MATCH (ki podaja položaj zadnjega vnosa). Torej na koncu MATCH (TRUE, zgoraj, 0) -1 vrne vrednost 8 .
Naslednji del formule je funkcija IFERROR:
= OFFSET (SadjeHeading, 1, 0, IFERROR (zgoraj, 20), 1)
Funkcija IFERROR vrne nadomestno vrednost, če prva podana vrednost povzroči napako. Ta funkcija je vključena, ker če je celoten blok celic (vseh 20 vrstic) zapolnjen z vnosi, bo funkcija MATCH vrnila napako.
To je zato, ker povemo funkciji MATCH, da išče prvo vrednost TRUE (v nizu vrednosti iz ISBLANK funkcije), če pa NONE od celic ni prazna, bo celotno polje zapolnjeno z vrednostmi FALSE. Če MATCH ne more najti ciljne vrednosti (TRUE) v matriki, ki jo išče, vrne napako.
Torej, če je celoten seznam poln (in zato MATCH vrne napako), bo funkcija IFERROR namesto tega vrnila vrednost 20 (vedela, da mora biti na seznamu 20 vnosov).
Nazadnje, OFFSET (FruitsHeading, 1, 0, zgoraj, 1) vrne obseg, ki ga dejansko iščemo: začnite v celici FruitsHeading, pojdite navzdol po eni vrstici in nad 0 stolpci, nato izberite območje, ki ima veliko vrstic, v seznamu so vnosi (in 1 stolpec). Tako bo celotna formula skupaj vrnila obseg, ki vsebuje samo dejanske vnose (do prve prazne celice).
S to formulo določite obseg, ki je vir za spustni meni, kar pomeni, da lahko prosto uredite seznam (dodajanje ali odstranjevanje vnosov, dokler se preostali vnosi začnejo v zgornji celici in so sosednji) in spustni meni vedno odraža trenutno (glej sliko 6).
Datoteka zgleda (dinamični seznami), ki je bila uporabljena tukaj, je vključena in je mogoče prenesti s te spletne strani. Makroji pa ne delujejo, ker WordPress ne mara Excelovih knjig z makri v njih.
Kot alternativo določanju števila vrstic v bloku seznama se lahko seznamu dodeli lastno ime razpona, ki ga lahko nato uporabimo v spremenjeni formuli. V primerni datoteki ta seznam uporablja drugi seznam (Imena). Pri tem se celotnemu seznamskem bloku (pod naslovom »NAMES«, 40 vrstic v datoteki primera) dodeli ime območja NameBlock . Nadomestna formula za definiranje seznama NamesList je potem:
= OFFSET (NamesHeading, 1, 0, IFERROR (MATCH) (TRUE, INDEX (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1)
kjer NamesBlock nadomesti OFFSET (FruitsHeading, 1, 0, 20, 1) in ROWS (NamesBlock) nadomesti 20 (število vrstic) v prejšnji formuli.
Torej, za spustne sezname, ki jih je mogoče enostavno urejati (vključno z drugimi uporabniki, ki so morda neizkušeni), poskusite uporabiti imena dinamičnega razpona! Upoštevajte, da čeprav je bil ta članek osredotočen na spustne sezname, se lahko imena dinamičnega razpona uporabijo kjerkoli, kjer se morate sklicevati na obseg ali seznam, ki se lahko razlikujejo po velikosti. Uživajte!
