Ena izmed najbolj uporabnih lastnosti preverjanja podatkov je možnost ustvarjanja spustnega seznama, ki uporabnikom omogoča, da izberejo vrednost iz vnaprej določenega seznama. Spustni seznami uporabnikom olajšajo vnos samo podatkov, ki ustrezajo vašim zahtevam.
Spustne sezname je enostavno ustvariti in uporabljati. Ko pa boste v preglednicah začeli uporabljati spustne menije, boste neizogibno naleteli na izziv: kako lahko določite, da so vrednosti na enem spustnem seznamu odvisne od vrednosti na drugem? Z drugimi besedami, kako lahko spustni seznam naredite dinamičen?
kako dodati enačbo trendline v
Tu je nekaj primerov:
- seznam mest, ki je odvisen od izbrane države
- seznam okusov, ki so odvisni od vrste sladoleda
- seznam modelov, ki je odvisen od proizvajalca
- seznam živil, ki je odvisen od kategorije
Tovrstni seznami se imenujejo odvisni spustni seznami , ker je seznam odvisen od druge vrednosti. Ustvarjajo se s preverjanjem podatkov po formuli po meri, ki temelji na Neposredna funkcija in imenovanih obsegov . Morda se sliši zapleteno, vendar je v resnici zelo preprosto in odličen primer, kako je mogoče uporabiti INDIRECT.
V nadaljevanju preberite, kako ustvariti odvisne spustne sezname v Excelu.
Primer odvisnega spustnega menija
V spodnjem primeru stolpec B ponuja spustni meni za kategorijo hrane, stolpec C pa možnosti v izbrani kategoriji. Če uporabnik izbere 'Sadje', vidi seznam sadja, če izbere 'Oreh', vidi seznam oreščkov, če pa izbere 'Zelenjava', pa seznam zelenjave.
Validacija podatkov v stolpcu B uporablja to formulo po meri:
=category Validacija podatkov v stolpcu C uporablja to formulo:
= INDIRECT (B5)
Kjer delovni list vsebuje naslednje poimenovane obsege:
kategorija = E4: G4
zelenjava = F5: F10
matica = G5: G9
sadje = E5: E11
Kako to deluje
Ključ do te tehnike je imenovanih obsegov + funkcija INDIRECT. INDIRECT sprejema besedilne vrednosti in jih poskuša ovrednotiti kot sklice na celice. INDIRECT bo na primer vzel besedilo 'A1' in ga spremenil v dejansko referenco:
= INDIRECT ('A1') =A1
Podobno bo INDIRECT besedilo 'A1: A10' pretvoril v obseg A1: A10 znotraj druge funkcije:
= SUM ( INDIRECT ('A1:A10') = SUM (A1:A10)
Na prvi pogled se vam zdi ta konstrukcija nadležna ali celo nesmiselna. Zakaj zapletati lepo preprosto formulo z INDIRECT?
Bodite prepričani, do norosti obstaja način :)
Lepota INDIRECT je, da vam omogoča uporabo besedila točno kot referenca celice . To daje dve ključni prednosti:
- Referenco besedila lahko sestavite znotraj formule, kar je priročno določene vrste dinamičnih referenc .
- Besedilne vrednosti lahko poberete na delovnem listu in jih uporabite kot sklic na celico v formuli.
V primeru na tej strani združujemo slednjo idejo z imenovanimi obsegi za izdelavo odvisnih spustnih seznamov. INDIRECT preslika besedilo v imenovani obseg, ki se nato razreši na veljavno referenco. Tako v tem primeru poberemo besedilne vrednosti v stolpcu B in jih z INDIRECT pretvorimo v sklice na celice tako, da ujemamo obstoječa imenovana območja, na primer:
kako uporabljati matriko v
= INDIRECT (B5) = INDIRECT ('nut') =G5:G9
B5 se razreši na besedilo 'matica', ki se razreši na območje G5: G9.
Kako nastaviti odvisne spustne sezname
Ta razdelek opisuje, kako nastaviti odvisne spustne sezname, prikazane v primeru.
1. Ustvarite potrebne sezname. V tem primeru na delovnem listu ustvarite seznam sadja, oreščkov in zelenjave.
2. Ustvarite imenovana območja za vsak seznam: kategorija = E4: G4, zelenjava = F5: F10, oreh = G5: G9 in sadje = E5: E11.
Pomembno: vrednosti v E4, F4 in G4 se morajo ujemati z zadnjimi tremi zgoraj navedenimi območji (zelenjava, oreški in sadje). Z drugimi besedami, prepričati se morate, da so imenovani obsegi, ki ste jih ustvarili, poimenovani tako, da se ujemajo z vrednostmi, ki bodo prikazane na spustnem seznamu Kategorija.
Opomba: Če ste imenovani obsegi novi, poglej to stran .3. Ustvarite in preizkusite pravilo preverjanja veljavnosti podatkov, da zagotovite spustni seznam za kategorijo z uporabo naslednje formule po meri:
=category 
Opomba: Če želite biti jasni, se imenovani obseg 'kategorija' uporablja samo za berljivost in udobje - uporaba imenovanega obsega tukaj ni potrebna. Upoštevajte tudi, da se preverjanje podatkov s seznamom dobro obnese tako v vodoravnem kot v navpičnem obsegu - oba bosta predstavljena kot navpični spustni meni.
4. Ustvarite pravilo za preverjanje podatkov za odvisni spustni seznam s formulo po meri na podlagi funkcije INDIRECT:
= INDIRECT (B5)
V tej formuli INDIRECT preprosto ovrednoti vrednosti v stolpcu B kot sklice, ki jih povežejo z že določenimi obsegi.
formula za stopnjo dobička v excelu
5. Preizkusite spustne sezname in se prepričajte, da se dinamično odzivajo na vrednosti v stolpcu B.
Opomba: pristop, ki ga uporabljamo tukaj, ne razlikuje med velikimi in malimi črkami. Imenovani obseg se imenuje 'matica' in vrednost v B6 je 'Nut', vendar se funkcija INDIRECT pravilno razreši na imenovano območje, čeprav se razlikujejo velike in velike črke.
Ukvarjanje s prostori
Poimenovani obsegi ne dovoljujejo presledkov, zato je običajno, da namesto tega uporabite podčrtane znake. Na primer, če želite ustvariti poimenovano območje za sladoled , bi uporabili sladoled. To deluje v redu, vendar se bodo odvisni spustni seznami zlomili, če bodo poskušali preslikati 'sladoled' v 'ice_cream'. Če želite odpraviti to težavo, lahko uporabite bolj robustno formulo po meri za preverjanje podatkov:
= INDIRECT ( SUBSTITUTE (A1,' ','_'))
Ta formula še vedno uporablja INDIRECT za povezavo besedilne vrednosti v A1 z imenovanim obsegom, vendar preden se izvede INDIRECT, funkcija SUBSTITUTE nadomesti vse presledke s podčrtaji. Če besedilo ne vsebuje presledkov, SUBSTITUTE nima učinka.
Datoteka vadbe
Primer datoteke je priložen spodaj - preverite, kako deluje. Če se želite sami naučiti tehnike, vam priporočam, da sestavite svojo datoteko. Najboljši način za učenje je z delom.
Avtor Dave Bruns Priloge
odvisen spustni meni.xlsx
