Iki šiol šioje duomenų tikrinimo serijoje mes išmokome sukurti įprastą išskleidžiamąjį sąrašą ir dinaminį išskleidžiamąjį sąrašą, naudojant skirtingus metodus, naudojant duomenų patvirtinimą „Excel“.
Ir šiandien šiame skyriuje parodysime, kaip naudojant „Microsoft Excel“ naudojant skirtingus metodus sukurti priklausomą išskleidžiamąjį sąrašą.
Priklausomas išskleidžiamasis sąrašas taip pat žinomas kaip pakopinis duomenų patvirtinimas, ir jis riboja pasirinkimus išskleidžiamajame sąraše, priklausomai nuo vertės, pasirinktos kitame langelyje, kuriame yra duomenų patvirtinimas. Kitaip tariant, tai priklauso nuo pirmoje išskleidžiamajame sąraše pasirinktos vertės, kuri nustato reikšmes, kurios bus rodomos antrame išskleidžiamajame sąraše.
Tai labai dažnas darbo su dideliais duomenimis ar kai kuriomis dinamiškomis ataskaitomis scenarijus, kai norite turėti 2antra langelyje rodomas sąrašas, kuris priklauso nuo sąrašo elemento, pasirinkto pirmajame išskleidžiamajame meniu.
Kaip žinome, „Excel“ yra daug būdų, kaip atlikti tam tikrą užduotį, ir panašiai yra daug būdų, kaip sukurti priklausomą duomenų patvirtinimą „Excel“. Ir šiandien mes parodysime 5 skirtingus metodus, kaip sukurti priklausomų duomenų patvirtinimo sąrašą.
Neapdoroti duomenys gali būti bet kokia tvarka ar formatu ir kiekvieną kartą, kai negalite pakeisti duomenų ar formato, kad gautumėte tai, ko ieškote.
Taigi, norėdami gauti priklausomą išskleidžiamąjį sąrašą, paėmėme vieną duomenų rinkinį, bet 3 skirtingais formatais. Ir, kaip matote, mūsų duomenys yra kairėje pusėje, nuo A stulpelio iki E stulpelio, o laukiamą rezultatą turėsime dešinėje pusėje, kuri yra J & K stulpelyje. J stulpelis bus pirminis patvirtinimas sąrašas, o K stulpelis bus priklausomas ir rodys reikšmes, priklausomai nuo stulpelyje J pasirinktos vertės.
1st Pavyzdys:-
2antra Pavyzdys:-
3rd Pavyzdys:-
1st Pavyzdys:-
Turime kiekvieno produkto kodo produktų sąrašą nuo A8 iki E13 stulpelio. Ir mes norime pasirinkti produkto kodą J10, tada, priklausomai nuo pasirinkto produkto kodo, produkto pavadinimą langelyje K10.
Pirmasis metodas:-
Pirmasis metodas yra labai paprastas ir trumpas, o norint gauti atitinkamą išskleidžiamąjį sąrašą, reikia atlikti tik 3 veiksmus. Tačiau jis sėkmingai veikia tik tol, kol nepakeisite savo diapazono. Kai pakeisite savo duomenis, pirmiausia turėsite pakeisti pavadintą diapazoną, kad gautumėte atnaujintą pakopinių duomenų patvirtinimą.
Atlikite toliau nurodytus veiksmus:-
- Pasirinkite visą lentelę nuo A8 iki E13
- Tada eikite į skirtuką „Formulės“, tada kategorijoje „Apibrėžti vardai“ spustelėkite „Sukurti iš pasirinkimo“
- Taip pat galite naudoti sparčiuosius klavišus CTRL + SHIFT + F3
- Bus rodomas dialogo langas „Sukurti vardus iš pasirinkimų“
- Ji prašo patvirtinti, kurios eilutės ir stulpeliai bus naudojami kitų eilučių ir stulpelių pavadinimams kurti. Patvirtiname, kad pavadinimams kurti naudojame „viršutinę eilutę“ ir panaikiname 2 žymėjimąantra parinktį, tada spustelėkite Gerai
Pastaba: - Tarpai ir kiti specialieji simboliai, išskyrus pabraukimą ir tašką, neleidžiami kaip pavadinimai. Pagal numatytuosius nustatymus jis bus paverstas pabraukimu. Taigi, norėdami atskirti žodžius, naudokite pabraukimo simbolį ir tašką. Be to, pirmoji raidė negali būti skaičius; tai turi būti raidė, pabraukimas arba brūkšnys.
- Dabar, norėdami patvirtinti, kad kiekvienas diapazonas turi pavadinimą, einame į „Vardų tvarkytuvę“ (paspauskite CTRL + F3)
- Ten matome visus 5 pavadintus diapazonus
- Taip pat matome, kad kiekvieno diapazono pavadinimo eilutės viduryje yra pabraukimas, o ne tuščias
Dabar mes sukursime išskleidžiamąjį sąrašą:-
- Pasirinkite langelį J10 ir paspauskite ALT ++ D+L, kad atidarytumėte dialogo langą Duomenų patvirtinimas
- Pasirinkite Sąrašas> tada skirtuke Šaltinis įveskite diapazoną A8: E8
- Spustelėkite Gerai
- Dabar mes sukursime priklausomą sąrašą ląstelėje K10
- Atidarykite Duomenų patvirtinimo dialogo langą paspausdami klavišą ALT+D+L.
- Pasirinkite Sąrašas, šaltinyje įveskite šią funkciją:- = NETIESIOGINĖ (PAKEITIMAS ($ J $ 10, "", "_"))
Norėdami patvirtinti duomenis, norėdami sukurti priklausomą sąrašą, mes naudojome funkciją INDIRECT, kad grąžintume vertę pagal pirminį duomenų patvirtinimo sąrašą. Norėdami pakeisti pabraukimą į tarpą, mes naudosime funkciją SUBSTITUTE INDIRECT.
- Spustelėkite Gerai
Kai langelyje J10 pasirenkame bet kurį produkto kodą, ląstelė K10 pasirodys pasirinkto produkto kodo produktų sąrašas. Pavyzdžiui: - Pasirinkome ETV 501, dabar matote, kad priklausomas produktų sąrašas rodomas langelyje K10
Pastaba: - Kai tik pridėsite produkto pavadinimą ir produkto kodą, kurie nebus rodomi sąraše.
Pavyzdžiui: - Mes pridėjome 26 produktą prie ETV 505 produkto kodo, tačiau kai pasirenkame ETV 505 produktą, pridėtas produktas nerodomas išskleidžiamajame sąraše.
Taigi, taip galite sukurti priklausomą išskleidžiamąjį sąrašą naudodami paprastą techniką, atlikdami tik 3 paprastus veiksmus.
2antra Pavyzdys:-
Šiame pavyzdyje pamatysime, kaip gauti priklausomą išskleidžiamąjį sąrašą, kai turėsite duomenų, kaip parodyta šioje vertikalioje lentelėje.
Norėdami sukurti priklausomą išskleidžiamąjį sąrašą, naudosime du skirtingus metodus. Abi yra beveik panašios technikos. Tačiau vienas neturi pavadinto diapazono, o kitas - pavadintą diapazoną.
1st Metodas:-
Norėdami tai padaryti, kartu naudosime funkcijas OFFSET, MATCH & COUNTIF.
Kadangi mes žinome, kad funkcija OFFSET naudojama dinaminiam diapazonui sukurti, todėl, norėdami sukurti „Dinaminių duomenų patvirtinimo“ sąrašą, mes naudojame funkciją OFFSET, kad grąžintume dinaminį diapazoną.
„MATCH“ naudojama grąžinti santykinę elemento poziciją „Excel“ sąraše. Ir čia tai padės mums suderinti kategoriją, pasirinktą pirminiame išskleidžiamajame sąraše mūsų diapazone, ir grąžins skaičių.
COUNTIF naudojamas norint gauti ląstelių, atitinkančių kriterijus, skaičių. Ir čia mes tai naudosime skaičiuodami rodomų eilučių skaičių naudodami funkciją COUNTIF.
Atlikite toliau nurodytus veiksmus:-
- Pasirinkite langelį J21, kuriame sukursime pirminių duomenų patvirtinimo sąrašą
- Paspauskite klavišą ALT+D+L, kad atidarytumėte dialogo langą Duomenų patvirtinimas
- Pasirinkite sąrašą iš leistinų kategorijų
- Spustelėkite skirtuką Šaltinis ir pasirinkite diapazoną iš B20: B24
- Ir spustelėkite Gerai
- Eikite į langelį K21 ir dar kartą atidarykite duomenų patvirtinimo dialogo langą
- Tada mes pasirenkame sąrašą ir šaltinyje įveskite žemiau esančią funkciją:
- = PAKLAIDA ($ 19 USD, MATCH ($ 21 $, $ 20 $: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))
- Spustelėkite Gerai
- K21 langelyje matome visas atitinkamas pasirinkto produkto kodo reikšmes:-
Taigi, taip galite gauti priklausomą sąrašą, naudodami funkcijos langelių nuorodas.
2antra Metodas:-
Kitu metodu mes naudosime pavadintą diapazoną toje pačioje funkcijoje, kad gautume pakopinių duomenų patvirtinimą. Pirmiausia turime sukurti dinaminį produkto kodo sąrašą. Jei prie duomenų pridedamas naujas produktas, išskleidžiamasis meniu turėtų būti atnaujintas, kad būtų rodomas tas pats.
Norėdami tai padaryti, atlikite toliau nurodytus veiksmus:-
- Pasirinkite B19, tada paspauskite CTRL + F3, kad atidarytumėte langą „Name Manager“
- Dabar spustelėkite „Naujas“ ir pasirodys dialogo langas „Apibrėžti vardą“
- Matome, kad pavadinimas jau rodomas pavadinimo laukelyje -taip yra todėl, kad prieš atidarydami langą „Vardų tvarkyklė“ pasirinkome B9. Ir kadangi B19 yra tekstas, jei norime, galime jį pakeisti kitu pavadinimu.
- Įveskite žemiau nurodytą formulę:-
= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))
- Spustelėkite Gerai
Kadangi sukūrėme unikalių produktų dinaminį sąrašą, dabar sukursime dinaminį produktų kodo diapazono diapazoną, esantį D stulpelyje.
Atlikite tuos pačius veiksmus, kuriuos atlikome dėl unikalaus produkto:-
- Pasirinkite langelį D19, atidarykite dialogo langą Apibrėžti vardą
- Pamatysite, kad vardas jau yra
- Nuorodose įveskite šią formulę:-
= OFFSET („Priklausomas išskleidžiamasis sąrašas“! $ D $ 20,0,0, COUNTA („Priklausomas išskleidžiamasis sąrašas“! $ D $ 20: $ D $ 35))
- Spustelėkite Gerai
- Dabar abu dinaminiai diapazonai yra paruošti. Taigi, einame į J22 ir paspaudžiame „ALT + D + L“ ir pasirenkame „Sąrašas“
- Šaltinyje turėsime pavadintą diapazoną, kurį apibrėžėme kaip „Unikalus produkto kodas“, todėl paspaudę F3 pamatysite visus galimus pavadintus diapazonus
- Mes galime pamatyti pavadinimą „Unikalus produkto kodas“, todėl mes spustelime jį, tada spustelėkite Gerai ir paspaudžiame „Enter“
- Kai paspausime „Enter“, langelyje J22 gausime išskleidžiamąją rodyklę, kurioje yra unikalių produktų kodų sąrašas
- Pasirinkite langelį K22 ir atidarykite dialogo langą „Duomenų patvirtinimas“
- Mes naudosime tą pačią funkciją, kurią naudojome paskutiniame metode, bet su pavadinimu
- Pasirinkite sąrašą, tada šaltinyje įveskite šią formulę:-
= OFFSET ($ E $ 19, MATCH ($ J $ 22, Product_Code, 0), 0, COUNTIF (Product_Code, J22))
- Spustelėkite Gerai
- Dabar turime pagrindinį išskleidžiamąjį meniu ir vaikų produktų sąrašą
- Pasirinkite „ETV-101“ produktą iš J22, o K22 matome tik pavadinimus, kurie patenka į šį „ETV-101“ produktą. Ir kai mes pakeičiame bet kurį produktą („ETV-103“) J22, K22 rodo atitinkamas to kodo vertes
Dabar pamatysime, kas atsitiks, kai prie sąrašo pridėsime naują produkto kodą? Ar šie išskleidžiamieji sąrašai bus atnaujinti?
Į sąrašą įtraukime naują produktą; Atlikite toliau nurodytus veiksmus:-
- Įtraukite produkto kodą į „Unique_Prod_Code“ sąrašą
- Taip pat prie duomenų pridėkite „Product_Code“ ir „Product_Name“:-
- Dabar patikrinkite išskleidžiamąjį sąrašą -pridėtas produkto kodas ir pavadinimas
3rd Pavyzdys:-
Turime dinamiškas antraštes tiesiai iš lentelės, o asortimentą papildysime naujais produktais. Lentelė yra to paties formato, kurį naudojome 1st metodas.
4tūkst Metodas:-
Atlikite toliau nurodytus veiksmus:-
- Pasirinkite antraštę A40: E40
- Pirmiausia sukurkite dinaminį antraščių diapazoną, atidarykite dialogo langą „Apibrėžti vardą“
- Vardo vietoje parašykite „Antraštė“, o tada „nurodo“ įveskite žemiau nurodytą formulę:-
- Įveskite žemiau esančią funkciją:-
- = OFFSET ('Priklausomas išskleidžiamasis sąrašas'! $ A $ 40 ,,,, COUNTA ('Priklausomas išskleidžiamasis sąrašas'! $ 40: $ 40))
- Spustelėkite Gerai
- Dinaminis „Krypties“ diapazonas yra paruoštas
Dabar sukursime kiekvienos antraštės pavadintą diapazoną, atlikite toliau nurodytus veiksmus:-
- Pasirinkite lentelę nuo A40 iki E50
- CTRL + SHIFT + F3 spartusis klavišas
- Mes panaikiname žymėjimą 2antra variantas
- Ir prieš spustelėdami Gerai, įsitikinkite, kad 1st pasirinkta parinktis „Viršutinė eilutė“
- Dabar esame pasiruošę abiem diapazonams
Dabar paruošime išskleidžiamąjį tėvų sąrašą
- Pasirinkite langelį J42
- Atidarykite dialogo langą Duomenų tikrinimas
- Tada pasirinkę „Sąrašas“, paspaudžiame F3 šaltinyje, kad gautume pavadintą diapazoną antraštėms. Spustelėkite „Antraštė“, tada spustelėkite Gerai ir paspauskite „Enter“. Dabar turime tėvų sąrašą J42
- Norėdami sukurti elemento išsamios informacijos sąrašą, pasirinkite langelįK42
- Atidarykite Duomenų patvirtinimo dialogo langą paspausdami klavišą ALT+D+L.
- Pasirinkite Sąrašas, tada įveskite žemiau esančią funkciją skirtuke Šaltinis:-
- = OFFSET (NETIESIOGINIS (PAKEITIMAS ($ J $ 42, "", "_")) ,,, COUNTA (NETIESIOGINIS (PAKEITIS ($ J $ 42, "", "_"))))
- Spustelėkite Gerai
Dabar pasirinkite tam tikrą elementą J42, tarkime, kad mes pasirenkame „Prekė 01“ ir žiūrėkite išskleidžiamąjį sąrašą K42. Ir, kaip ir ankstesni 3 metodai, čia taip pat gavome priklausomų sąrašą.
Taigi kas naujo? Pirmame pavyzdyje negalėjote įtraukti jokio produkto į sąrašą, bet čia galite pridėti bet kurį naują produktą. Taigi, tarkime, prie šio elemento pridėjome naują produktą. Mes einame į A45 ir įvedame „ETV-501 Prod 05“, tada grįžtame prie K42 ir štai. Matote, naujas produktas buvo pridėtas.
- Dabar pridėkite keletą produktų prie naujo elemento
Kai pasirenkame „06 prekę“, einame į K42 ir spustelėkite išskleidžiamąjį sąrašą. Keista, bet nieko neįvyksta, kai spustelime išskleidžiamąją rodyklę. Taip yra todėl, kad sukūrėme viską dinamiškai ir pamiršome sukurti lentelės dinaminį diapazoną, todėl produktai nėra rodomi vaikų sąraše.
Norėdami tai padaryti, turime naudoti skirtingus metodus. Yra du būdai tai padaryti. Galite sukurti lentelę arba tiesiog naudoti tik OFFSET funkciją. Kitu metodu naudosime OFFSET funkciją ir pamatysime triuką, kaip išplėsti lentelių diapazoną.
- Taigi, pirmiausia einame į J43 ir paspaudžiame „ALT + D + L“
- Mes pasirenkame „Sąrašas“, tada šaltinyje paspaudžiame F3 ir pasirenkame „Antraštė“, spustelėkite Gerai, tada paspauskite „Enter“
- Dabar mes einame į K43, o pasirinkę „Sąrašas“ einame į „Šaltinis“ ir įvedame žemiau nurodytą funkciją
= OFFSET ($ 40,1 USD, MATCH ($ J $ 43, $ 40: $ 40,0) -1, COUNTA (OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0)) -1,1000 , 1)))
- Spustelėkite Gerai
Dabar grįžtame ir J43 langelyje pasirenkame „06 elementą, grįžtame į K43 ir spustelėkite išskleidžiamąją rodyklę. Tačiau šiame laiko sąraše rodomi produktai, kuriuos pridėjome naujam elementui. Ir mes pasirenkame pirmąjį produktą „ETV-506 Prod 01“.
Taip galite sukurti priklausomą išskleidžiamąjį sąrašą, naudodami skirtingus metodus bet kokio tipo duomenims.
Vaizdo įrašas: Kaip sukurti priklausomą (pakopinį) išskleidžiamąjį sąrašą „Excel“ naudojant 5 skirtingas „Microsoft Excel“ technikas
Spustelėkite vaizdo įrašo nuorodą, kad galėtumėte greitai sužinoti, kaip ją naudoti. Prenumeruokite mūsų naują kanalą ir mokykitės kartu su mumis!
Jei jums patiko mūsų tinklaraščiai, pasidalykite jais su draugais „Facebook“. Taip pat galite sekti mus „Twitter“ ir „Facebook“.
Mes norėtume išgirsti jūsų nuomonę, praneškite mums, kaip galime patobulinti, papildyti ar naujovinti savo darbą ir padaryti jį geresnį. Parašykite mums el