Līdz šim šajā datu validācijas sērijā mēs esam iemācījušies izveidot parastu nolaižamo sarakstu un dinamisku nolaižamo sarakstu, izmantojot dažādas metodes ar datu validāciju programmā Excel.
Un šodien šajā nodaļā mēs parādīsim, kā izveidot atkarīgo nolaižamo sarakstu programmā Microsoft Excel, izmantojot dažādas metodes.
Atkarīgais nolaižamais saraksts ir pazīstams arī kā kaskādes datu validācija, un tas ierobežo izvēli nolaižamajā sarakstā atkarībā no vērtības, kas atlasīta citā šūnā, kurā ir datu validācija. Citiem vārdiem sakot, tas ir atkarīgs no pirmajā nolaižamajā sarakstā atlasītās vērtības, kas nosaka otrajā nolaižamajā sarakstā redzamās vērtības.
Šis ir ļoti izplatīts scenārijs darbam ar lieliem datiem vai dažiem dinamiskiem pārskatiem, kur vēlaties iegūt 2nd šūnā tiek parādīts saraksts, kas ir atkarīgs no saraksta vienuma, kas izvēlēts pirmajā nolaižamajā izvēlnē.
Kā mēs zinām, ka programmā Excel ir daudz veidu, kā veikt noteiktu uzdevumu, un līdzīgi ir daudz veidu, kā programmā Excel izveidot atkarīgu datu validāciju. Un šodien mēs parādīsim 5 dažādas metodes, lai izveidotu atkarīgu datu validācijas sarakstu.
Neapstrādātie dati var būt jebkurā secībā vai formātā, un katru reizi, kad nevarat mainīt datus vai formātu, lai iegūtu meklēto.
Tātad, mēs esam paņēmuši vienu datu kopu, bet 3 dažādos formātos, lai iegūtu atkarīgo nolaižamo sarakstu. Un, kā redzat, mūsu dati atrodas kreisajā pusē, kas ir no A kolonnas līdz E slejai, un mums būs paredzamā izlaide labajā pusē, kas atrodas J & K slejā. J kolonnai tiks veikta primārā validācija saraksts, savukārt kolonna K būs atkarīga un parādīs vērtības atkarībā no kolonnā J atlasītās vērtības.
1st Piemērs:-
2nd Piemērs:-
3rd Piemērs:-
1st Piemērs:-
Mums ir produktu saraksts katram produkta kodam no kolonnas A8 līdz E13. Un mēs vēlamies atlasīt produkta kodu J10, pēc tam atkarībā no izvēlētā produkta koda produkta nosaukumu šūnā K10.
Pirmā metode:-
Pirmā metode ir ļoti vienkārša un īsa, un tai ir vajadzīgas tikai 3 darbības, lai iegūtu nolaižamo sarakstu. Tomēr tas darbojas tikai veiksmīgi, līdz jūs neveicat izmaiņas diapazonā. Kad esat mainījis savus datus, vispirms būs jāmaina nosauktais diapazons, lai iegūtu atjauninātu kaskādes datu validāciju.
Izpildiet tālāk norādītās darbības:-
- Atlasiet visu tabulu no A8 līdz E13
- Pēc tam dodieties uz cilni “Formulas”, pēc tam kategorijā “Definētie vārdi” noklikšķiniet uz “Izveidot no atlases”
- Varat arī izmantot īsinājumtaustiņu CTRL + SHIFT + F3
- Parādīsies dialoglodziņš Izveidot vārdus no atlasēm
- Tajā tiek lūgts apstiprināt, kuras rindas un kolonnas jāizmanto, lai izveidotu citu rindu un kolonnu nosaukumus. Mēs apstiprinām, ka nosaukumu izveidošanai tiek izmantota augšējā rinda un noņemam atzīmi no 2nd opciju un pēc tam noklikšķiniet uz Labi
Piezīme: - atstarpes un citas īpašās rakstzīmes, izņemot pasvītrojumu un punktu, nav atļautas kā nosaukumi. Pēc noklusējuma tas tiks pārveidots par pasvītrojumu. Tāpēc, lai atdalītu vārdus, izmantojiet pasvītrojumu un punktu. Arī pirmais burts nevar būt skaitlis; tam jābūt burtam, pasvītrojumam vai slīpsvītrai.
- Tagad, lai apstiprinātu, ka katram diapazonam ir nosaukums, dodamies uz nosaukumu pārvaldnieku (nospiediet taustiņu kombināciju CTRL + F3)
- Tur mēs varam redzēt visus pieejamos 5 nosauktos diapazonus
- Un arī mēs redzam, ka katram diapazona nosaukumam virknes vidū ir pasvītrojums, nevis tukšs
Tagad mēs izveidosim nolaižamo sarakstu:-
- Atlasiet šūnu J10 un nospiediet ALT ++ D+L, lai atvērtu dialoglodziņu Datu validācija
- Atlasiet Saraksts> un cilnē Avots ievadiet diapazonu A8: E8
- Noklikšķiniet uz Labi
- Tagad šūnā K10 izveidosim atkarīgo sarakstu
- Atveriet dialoglodziņu Datu validācija, nospiežot taustiņu ALT+D+L.
- Atlasiet sarakstu, avotā ievadiet šo funkciju:- = NETIEŠS (SUBSTITUTE ($ J $ 10, "", "_"))
Datu validācijā, lai izveidotu atkarīgo sarakstu, esam izmantojuši funkciju INDIRECT, lai atgrieztu vērtību, pamatojoties uz primāro datu validācijas sarakstu. Un, lai pasvītrojumu aizstātu ar atstarpi, INDIRECT funkcijā izmantosim funkciju SUBSTITUTE.
- Noklikšķiniet uz Labi
Kad šūnā J10 izvēlamies jebkuru produkta kodu, šūnā K10 parādīsies atlasītā produkta koda produktu saraksts. Piemēram: - Mēs esam izvēlējušies ETV 501, tagad jūs varat redzēt, ka šūnā K10 tiek parādīts atkarīgo produktu saraksts
Piezīme: - Ikreiz, kad pievienosit produkta nosaukumu un produkta kodu, kas sarakstā netiks parādīti.
Piemēram: - Mēs esam pievienojuši 26. produktu ar ETV 505 produkta kodu, bet, kad mēs izvēlamies produktu ETV 505, pievienotais produkts netiek parādīts nolaižamajā sarakstā.
Tātad, šādā veidā jūs varat izveidot atkarīgu nolaižamo sarakstu, izmantojot vienkāršu tehniku, veicot tikai 3 vienkāršas darbības.
2nd Piemērs:-
Šajā piemērā mēs redzēsim, kā iegūt atkarīgo nolaižamo sarakstu, kad jūsu dati ir tādi, kā parādīts šajā vertikālajā tabulā.
Mēs izmantosim divas dažādas metodes, lai izveidotu atkarīgo nolaižamo sarakstu. Abas ir gandrīz līdzīgas tehnikas. Tomēr vienam nav nosaukta diapazona, bet otram būs nosauktais diapazons.
1st Metode:-
Lai to izdarītu, mēs kopā izmantosim funkcijas OFFSET, MATCH & COUNTIF.
Tā kā mēs zinām, ka dinamiskā diapazona izveidei tiek izmantota funkcija OFFSET, tāpēc, lai izveidotu “Dynamic data validation” sarakstu, mēs izmantojam funkciju OFFSET, lai atgrieztu dinamisko diapazonu.
MATCH tiek izmantots, lai atgrieztu vienuma relatīvo pozīciju Excel sarakstā. Un šeit tas palīdzēs mums lapā atlasīt mūsu diapazonā primārajā nolaižamajā sarakstā atlasīto kategoriju, un tas atgriezīs skaitli.
Un COUNTIF tiek izmantots, lai iegūtu šūnu skaitu, kas atbilst kritērijiem. Un šeit mēs to izmantosim, lai saskaitītu parādāmo rindu skaitu, izmantojot funkciju COUNTIF.
Izpildiet tālāk norādītās darbības:-
- Atlasiet šūnu J21, kurā mēs izveidosim primāro datu validācijas sarakstu
- Nospiediet taustiņu ALT+D+L, lai atvērtu dialoglodziņu Datu validācija
- Atlasiet sarakstu no atļaušanas kategorijas
- Noklikšķiniet uz cilnes Avots un atlasiet diapazonu no B20: B24
- Un noklikšķiniet uz Labi
- Dodieties uz šūnu K21 un vēlreiz atveriet datu validācijas dialoglodziņu
- Pēc tam mēs izvēlamies sarakstu un avotā ievadām tālāk norādīto funkciju:
- = NOLĪGUMS ($ 19 $, MATCH ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))
- Noklikšķiniet uz Labi
- Šūnā K21 mēs varam redzēt visas atbilstošās izvēlētā produkta koda vērtības:-
Tātad, šādā veidā jūs varat iegūt atkarīgo sarakstu, izmantojot funkciju šūnu atsauces.
2nd Metode:-
Nākamajā metodē mēs izmantosim nosaukto diapazonu tajā pašā funkcijā, lai iegūtu kaskādes datu validāciju. Vispirms mums ir jāizveido produkta koda dinamiskais saraksts. Ja datiem tiek pievienots jauns produkts, nolaižamā izvēlne ir jāatjaunina, lai parādītu to pašu.
Lai to izdarītu, veiciet tālāk norādītās darbības:-
- Atlasiet B19, pēc tam nospiediet CTRL + F3, lai atvērtu logu “Name Manager”
- Tagad mēs noklikšķinām uz “Jauns” un parādās dialoglodziņš “Definēt vārdu”
- Mēs redzam, ka nosaukums jau parādās nosaukumu lodziņā -tas ir tāpēc, ka pirms loga “Vārdu pārvaldnieks” atvēršanas esam izvēlējušies B9. Un, tā kā B19 ir teksts, ja mēs to vēlamies, mēs varam to mainīt uz citu nosaukumu.
- Ievadiet zemāk minēto formulu:-
= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))
- Noklikšķiniet uz Labi
Tā kā esam izveidojuši dinamisku sarakstu unikāliem produktiem, tagad mēs izveidosim dinamisko diapazonu produktu kodu diapazonam, kas atrodas D slejā.
Izpildiet tās pašas darbības, kuras esam veikuši attiecībā uz unikālu produktu:-
- Atlasiet šūnu D19, atveriet dialoglodziņu Definēt nosaukumu
- Jūs atradīsit, ka vārds jau ir tur
- Atsaucēs ievadiet šādu formulu:-
= OFFSET ('Atkarīgais nolaižamais saraksts'! $ D $ 20,0,0, COUNTA ('Atkarīgais nolaižamais saraksts'! $ D $ 20: $ D $ 35))
- Noklikšķiniet uz Labi
- Tagad abi dinamiskie diapazoni ir gatavi. Tātad, mēs ejam uz J22 un nospiediet “ALT + D + L” un atlasiet “Saraksts”
- Avotā mums būs nosauktais diapazons, ko mēs definējām kā “Unikālo produkta kodu”, tāpēc mēs nospiežam F3, lai redzētu visus pieejamos nosauktos diapazonus
- Mēs varam redzēt “Unikālais produkta kods” nosaukto diapazonu, tāpēc mēs noklikšķiniet uz tā, pēc tam noklikšķiniet uz Labi un nospiediet ievadīšanas taustiņu
- Brīdī, kad mēs nospiežam taustiņu Enter, šūnā J22 tiek parādīta nolaižamā bultiņa, kurā ir unikālo produktu kodu saraksts
- Atlasiet šūnu K22 un atveriet dialoglodziņu “Datu validācija”
- Mēs izmantosim to pašu funkciju, ko esam izmantojuši pēdējā metodē, bet ar nosaukto diapazonu
- Atlasiet sarakstu un pēc tam avotā ievadiet zemāk esošo formulu:-
= OFFSET ($ E $ 19, MATCH ($ J $ 22, Product_Code, 0), 0, COUNTIF (Product_Code, J22))
- Noklikšķiniet uz Labi
- Tagad mums ir primārais nolaižamais saraksts un bērnu produktu saraksts
- Izvēlieties “ETV-101” produktu no J22, un K22 mēs varam redzēt tikai nosaukumus, kas ietilpst šajā “ETV-101” produktā. Un, mainot jebkuru produktu (“ETV-103”) J22, K22 parāda atbilstošās šī koda vērtības
Tagad redzēsim, kas notiks, kad sarakstam pievienosim jaunu produkta kodu? Vai šie nolaižamie saraksti tiks atjaunināti?
Pievienosim sarakstam jaunu produktu; Izpildiet tālāk norādītās darbības:-
- Pievienojiet produkta kodu Unique_Prod_Code sarakstam
- Datos pievienojiet arī produkta_kodu un produkta_nosaukumu:-
- Tagad pārbaudiet nolaižamo sarakstu -pievienots produkta kods un nosaukums
3rd Piemērs:-
Mums ir dinamiskie virsraksti tieši no tabulas, un mēs papildināsim klāstu ar jauniem produktiem. Tabula ir tādā pašā formātā, kādu izmantojām 1st metode.
4tūkst Metode:-
Izpildiet tālāk norādītās darbības:-
- Izvēlieties virsrakstu A40: E40
- Vispirms izveidojiet virsrakstu dinamisko diapazonu, atveriet dialoglodziņu “Definēt vārdu”
- Vārda vietā ierakstiet “Virsraksts” un pēc tam “atsaucas” ievadiet zemāk minēto formulu:-
- Ievadiet tālāk norādīto funkciju:-
- = OFFSET ('Atkarīgais nolaižamais saraksts'! $ A $ 40 ,,,, COUNTA ('Atkarīgais nolaižamais saraksts'! $ 40: $ 40))
- Noklikšķiniet uz Labi
- Dinamiskais “Virziena” diapazons ir gatavs
Un tagad mēs izveidosim nosaukto diapazonu katrai pozīcijai, izpildiet tālāk norādītās darbības.-
- Izvēlieties tabulu no A40 līdz E50
- CTRL + SHIFT + F3 īsinājumtaustiņi
- Mēs noņemam atzīmi no 2nd iespēja
- Un pirms mēs noklikšķinām uz Labi, pārliecinieties, ka 1st ir atlasīta opcija “Augšējā rinda”
- Tagad mēs esam gatavi abiem diapazoniem
Tagad mēs sagatavosim vecāku nolaižamo sarakstu
- Atlasiet šūnu J42
- Atveriet dialoglodziņu Datu validācija
- Pēc tam, kad esat atlasījis “Saraksts”, avotā nospiežam F3, lai iegūtu nosaukto diapazonu virsrakstiem. Mēs noklikšķiniet uz “Virsraksts”, pēc tam noklikšķiniet uz Labi un nospiediet taustiņu Enter. Mums tagad ir vecāku saraksts J42
- Lai izveidotu vienuma detaļu sarakstu, atlasiet šūnu K42
- Atveriet dialoglodziņu Datu validācija, nospiežot taustiņu ALT+D+L.
- Atlasiet sarakstu un cilnē Avots ievadiet tālāk norādīto funkciju:-
- = OFFSET (NETIEŠS (AIZSTĀJĪTĀJS ($ J $ 42, "", "_")) ,,, COUNTA (NETIEŠA (SUBSTITUTE ($ J $ 42, "", "_"))))
- Noklikšķiniet uz Labi
Tagad izvēlieties kādu vienumu J42, teiksim, ka mēs izvēlamies “01. prece” un apskatiet nolaižamo sarakstu K42. Un, tāpat kā iepriekšējās 3 metodes, arī šeit mēs saņēmām atkarīgo sarakstu.
Kas jauns? Pirmajā piemērā jūs nevarējāt sarakstam pievienot nevienu produktu, bet šeit varat pievienot jebkuru jaunu produktu. Tātad, pieņemsim, ka šim vienumam pievienojam jaunu produktu. Mēs ejam uz A45 un ierakstām “ETV-501 Prod 05”, pēc tam atgriežamies pie K42 un lūk. Jūs varat redzēt, ka jaunais produkts ir pievienots.
- Tagad pievienojiet dažus produktus zem jaunā vienuma
Kad mēs izvēlamies “06 vienumu”, mēs ejam uz K42 un noklikšķiniet uz nolaižamā saraksta. Pārsteidzoši, nekas nenotiek, noklikšķinot uz nolaižamās bultiņas. Tas ir tāpēc, ka mēs esam izveidojuši visu dinamisku un aizmirsām izveidot dinamisko diapazonu tabulai, tāpēc produkti netiek parādīti bērnu sarakstā.
Lai to izdarītu, mums jāizmanto dažādas metodes. Ir divas metodes, kā to izdarīt. Jūs varat izveidot tabulu vai vienkārši izmantot tikai funkciju OFFSET. Un nākamajā metodē mēs izmantosim funkciju OFFSET, un mēs redzēsim triku, kā arī paplašināt tabulas diapazonu.
- Tātad, vispirms dodamies uz J43 un nospiediet “ALT + D + L”
- Mēs izvēlamies “Saraksts” un pēc tam avotā nospiežam F3 un atlasām “Virsraksts”, noklikšķiniet uz Labi un pēc tam nospiediet taustiņu Enter
- Tagad mēs ejam uz K43 un pēc “Saraksta” atlasīšanas mēs dodamies uz “Avots” un ievadām zemāk minēto funkciju
= NOLĪGUMS ($ 40,1 USD, MATCH ($ J $ 43, $ 40: $ 40,0) -1, COUNTA (OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0)) -1,1000 , 1)))
- Noklikšķiniet uz Labi
Tagad mēs atgriežamies un J43 šūnā atlasām vienumu 06 un atgriežamies K43 un noklikšķiniet uz nolaižamās bultiņas. Bet šajā laika sarakstā ir redzami produkti, kurus esam pievienojuši jaunai precei. Un mēs izvēlamies pirmo produktu “ETV-506 Prod 01”.
Tādā veidā jūs varat izveidot atkarīgo nolaižamo sarakstu, izmantojot dažādas metodes jebkura veida datu formātam.
Video: Kā izveidot atkarīgu (kaskādes) nolaižamo sarakstu programmā Excel, izmantojot 5 dažādas metodes programmā Microsoft Excel
Noklikšķiniet uz video saites, lai ātri uzzinātu, kā to izmantot. Abonējiet mūsu jauno kanālu un turpiniet mācīties kopā ar mums!
Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook.
Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē