✅ Sõltuva ripploendi loomine Excelis (samm-sammult õpetus)

Lang L: none (table-of-contents)

Sõltuva ripploendi loomine Excelis

Vaadake videot - sõltuvate ripploendite loomine Excelis

Exceli ripploend on kasulik funktsioon andmete sisestamise vormide või Exceli juhtpaneelide loomisel.

See näitab lahtris olevate üksuste loendit ja kasutaja saab rippmenüüst valiku teha. See võib olla kasulik, kui teil on nimekiri nimedest, toodetest või piirkondadest, mida peate sageli lahtrite komplekti sisestama.

Allpool on näide Exceli ripploendist:

Ülaltoodud näites olen kasutanud üksuse A2: A6 rippmenüü loomiseks C3-s.

Loe: Siin on üksikasjalik juhend Exceli ripploendi loomise kohta.

Mõnikord võiksite aga Excelis kasutada rohkem kui ühte ripploendit, nii et teises ripploendis saadaolevad üksused sõltuvad esimeses ripploendis tehtud valikust.

Neid nimetatakse Excelis sõltuvateks ripploenditeks.

Allpool on näide sellest, mida ma Excelis sõltuva ripploendi all mõtlen:

Näete, et rippmenüü 2 valikud sõltuvad rippmenüüst 1 tehtud valikust. Kui valin rippmenüüst 1 valiku „Puuviljad”, kuvatakse mulle puuviljade nimed, kuid kui valin rippmenüüst 1 köögiviljad, siis Olen näidanud köögiviljade nimesid rippmenüüs 2.

Seda nimetatakse Excelis tingimuslikuks või sõltuvaks ripploendiks.

Sõltuva ripploendi loomine Excelis

Sõltuva ripploendi loomiseks Excelis toimige järgmiselt.

Valige lahter, kuhu soovite esimese (peamise) ripploendi.
Avage Andmed -> Andmete valideerimine. See avab andmete valideerimise dialoogiboksi.
Valige andmete valimise dialoogiboksis seadete vahekaardil Loend.
Väljal Allikas määrake vahemik, mis sisaldab esimeses ripploendis kuvatavaid üksusi.
Klõpsake nuppu OK. See loob rippmenüü 1.
Valige kogu andmekogum (selles näites A1: B6).
Avage Valemid -> Määratud nimed -> Loo valikust (või võite kasutada kiirklahvi Control + Shift + F3).
Märkige dialoogiboksis „Loo nimega valikust” ülemise rea valik ja tühjendage kõik muud. Seda tehes luuakse kaks nimevahemikku ("Puuviljad" ja "Köögiviljad"). Puuviljad nimega vahemik viitab kõigile loendis olevatele puuviljadele ja köögiviljad nimega vahemik viitab kõigile loendis olevatele köögiviljadele.
Klõpsake nuppu OK.
Valige lahter, kuhu soovite rippmenüüst Sõltuv/tingimus (selles näites E3).
Avage Andmed -> Andmete valideerimine.
Veenduge, et dialoogiboksi Andmete valideerimine seadete vahekaardil oleks valitud Loend sisse.
Sisestage väljale Allikas valem = KAUDNE (D3). Siin on D3 lahter, mis sisaldab peamist rippmenüüd.
Klõpsake nuppu OK.

Nüüd, kui valite rippmenüüst 1, värskendatakse automaatselt ripploendis 2 loetletud valikuid.

Laadige alla näidisfail

Kuidas see töötab? - Tingimuslik ripploend (lahtris E3) viitab = KAUDNE (D3). See tähendab, et kui valite lahtris D3 „Puuviljad”, viitab E3 ripploend nimega vahemikule „Puuviljad” (funktsiooni INDIRECT kaudu) ja loetleb seega kõik selle kategooria üksused.

Oluline märkus: Kui põhikategooria on rohkem kui üks sõna (näiteks „Puuviljad” asemel „Hooajalised puuviljad”), peate selle asemel kasutama valemit = KAUDNE (ASENDUS (D3, ”“, ”_”)). lihtne ülaltoodud kaudne funktsioon.

Selle põhjuseks on asjaolu, et Excel ei luba tühikuid nimivahemikes. Nii et kui loote nimevahemiku rohkem kui ühe sõna abil, lisab Excel sõnade vahele automaatselt alajoone. Näiteks kui loote nimega vahemiku suvandiga „Hooajalised puuviljad”, saab selle taustaprogrammis nimeks Season_Fruits. Funktsiooni SUBSTITUTE kasutamine INDIRECT funktsioonis tagab tühikute olemasolu on teisendatakse alakriipsudeks.

Sõltuva ripploendi sisu automaatne lähtestamine/kustutamine

Kui olete valiku teinud ja seejärel muudate rippmenüüd, ei muutu sõltuv ripploend ja seetõttu oleks see vale kirje.

Näiteks kui valite kategooriaks „Puuviljad” ja seejärel valite üksuseks Apple ning seejärel lähete tagasi ja muudate kategooriaks „Köögiviljad”, kuvatakse sõltuv rippmenüü kaubaartiklina.

VBA abil saate veenduda, et sõltuva ripploendi sisu lähtestatakse alati, kui peamist ripploendit muudetakse.

Siin on VBA -kood sõltuva ripploendi sisu kustutamiseks:

Privaatne alamlehe_muutmine (ByVal sihtmärk vahemikuna) vea korral Jätka järgmisel korral, kui sihtmärk. Veerg = 4 Siis, kui sihtmärk.Validation.Type = 3 Seejärel Application.EnableEvents = False Target.Offset (0, 1). ClearContents End If End Kui exit Application.EnableEvents = Tõesti väljumise alamotsa alam

Selle koodi autor on Debra õpetus sellest, kuidas Excelis olevaid ripploendeid kustutada, kui valikut muudetakse.

Selle koodi toimimiseks tehke järgmist.

Kopeerige VBA kood.
Minge Exceli töövihikus, kus teil on sõltuv ripploend, minge vahekaardile Arendaja ja klõpsake rühmas „Kood” nuppu Visual Basic (saate kasutada ka kiirklahvi - ALT + F11).
VB redaktori aknas, projektiuurija vasakul, näeksite kõiki töölehtede nimesid. Topeltklõpsake seda, millel on ripploend.
Kleepige kood paremal asuvasse koodiaknasse.
Sulgege VB redaktor.

Nüüd, kui muudate peamist ripploendit, vallandatakse VBA -kood ja see kustutab sõltuva ripploendi sisu (nagu allpool näidatud).

Kui te pole VBA fänn, võite kasutada ka lihtsat tingimusliku vormindamise nippi, mis tõstab lahtri esile mittevastavuse korral. See aitab teil mittevastavust visuaalselt näha ja parandada (nagu allpool näidatud).

Siin on sammud, mis toovad esile ebakõlad sõltuvates ripploendites:

Valige lahter, millel on sõltuv ripploend (id).
Avage Avaleht -> Tingimuslik vormindamine -> Uus reegel.
Valige dialoogiboksis Uus vormindusreegel „Kasutage valemit vormindatavate lahtrite määramiseks”.
Sisestage valemiväljale järgmine valem: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
Määrake vorming.
Klõpsake nuppu OK.

Valem kasutab funktsiooni VLOOKUP, et kontrollida, kas sõltuvate ripploendite üksus kuulub põhikategooriasse või mitte. Kui ei, siis tagastab valem vea. Funktsioon ISERROR kasutab seda tõe tagastamiseks, mis ütleb lahtri esiletõstmiseks tingimusvormingu.

Samuti võivad teile meeldida järgmised Exceli õpetused: