Siiani oleme selles andmete valideerimise seerias õppinud looma tavalist ripploendit ja dünaamilist ripploendit, kasutades erinevaid meetodeid koos andmete valideerimisega Excelis.
Ja täna, selles peatükis, näitame teile, kuidas luua Microsoft Excelis sõltuv ripploend, kasutades erinevaid meetodeid.
Sõltuv rippmenüü on tuntud ka kui kaskaadne andmete valideerimine ja see piirab ripploendi valikuid, sõltuvalt väärtusest, mis on valitud teises lahtris, mis sisaldab andmete valideerimist. Teisisõnu, see sõltub esimeses ripploendis valitud väärtusest, mis määrab teises ripploendis kuvatavad väärtused.
See on väga levinud stsenaarium suurte andmete või mõne dünaamilise aruandega töötamisel, kui soovite, et teil oleks 2nd lahtris kuvatakse loend, mis sõltub esimeses rippmenüüs valitud loendiüksusest.
Nagu me teame, et Excelis on konkreetse ülesande täitmiseks palju võimalusi ja sarnaselt on Excelis palju võimalusi sõltuvate andmete valideerimiseks. Ja täna demonstreerime 5 erinevat tehnikat sõltuvate andmete valideerimise loendi loomiseks.
Toorandmed võivad olla suvalises järjekorras või vormingus ja iga kord, kui te ei saa andmeid või vormingut otsitava saamiseks muuta.
Seega oleme sõltuvate ripploendite saamiseks võtnud ühe andmekogumi, kuid kolmes erinevas vormingus. Ja nagu näete, on meie andmed vasakul küljel, mis on veerust A veergu E, ja meil on oodatav väljund paremal küljel, mis asub veerus J & K. Veerg J saab esmase valideerimise veerg K on sõltuv ja kuvab väärtused sõltuvalt veerus J valitud väärtusest.
1st Näide:-
2nd Näide:-
3rd Näide:-
1st Näide:-
Meil on iga tootekoodi jaoks tootenimekiri veerust A8 kuni E13. Ja me tahame valida tootekoodi J10 -s, seejärel olenevalt valitud tootekoodist lahtris K10 toote nime.
Esimene meetod:-
Esimene meetod on väga lihtne ja lühike ning sõltuvalt ripploendist on vaja ainult 3 sammu. Kuid see töötab edukalt ainult seni, kuni te oma vahemikus muudatusi ei tee. Kui olete oma andmeid muutnud, peate värskendatud kaskaadandmete kinnitamise saamiseks kõigepealt nimega vahemikku muutma.
Järgige allpool toodud samme:-
- Valige kogu tabel vahemikus A8 kuni E13
- Seejärel minge vahekaardile „Valemid” ja seejärel jaotises „Määratud nimed” klõpsake „Loo valikust”
- Võite kasutada ka kiirklahvi CTRL + SHIFT + F3
- Ilmub dialoogiboks Loo nimed valikute hulgast
- See palub kinnitada, milliseid ridu ja veerge teiste ridade ja veergude nimede loomiseks kasutada. Kinnitame, et kasutame nimede loomiseks ülemist rida ja tühjendame 2nd valik ja seejärel klõpsame nuppu OK
Märge: - tühikud ja muud erimärgid, välja arvatud alajoon ja punkt, ei ole nimedena lubatud. Vaikimisi teisendatakse see alajooneks. Niisiis, kasutage sõnade eraldamiseks tähemärki ja punkti. Samuti ei saa esimene täht olla number; see peab olema täht, alajoon või tagasilöök.
- Nüüd, et kinnitada, et igal vahemikul on nimi, läheme jaotisse „Nimehaldur” (vajutage CTRL + F3)
- Seal näeme kõiki 5 nimega vahemikku
- Samuti näeme, et igal vahemiku nimel on stringi keskel tühi asemel alajoon
Nüüd koostame ripploendi:-
- Valige lahter J10 ja vajutage dialoogiboksi Andmete valideerimine ALT ++ D+L
- Valige Loend> ja sisestage vahekaart Allikas vahekaart A8: E8
- Klõpsake nuppu OK
- Nüüd loome lahtrisse K10 sõltuva loendi
- Avage dialoogiboks Andmete valideerimine, vajutades klahvi ALT+D+L.
- Valige Loend, sisestage allikas see funktsioon:- = KAUDNE (ASENDUS ($ J $ 10, "", "_"))
Andmete valideerimisel oleme sõltuvate loendite loomiseks kasutanud funktsiooni INDIRECT, et tagastada esmaste andmete valideerimise loendil põhinev väärtus. Alajoone tühikuga asendamiseks kasutame funktsiooni INDIRECT sees asendust.
- Klõpsake nuppu OK
Kui valime lahtris J10 suvalise tootekoodi, ilmub lahtrisse K10 valitud tootekoodi tootenimekiri. Näiteks: - Oleme valinud ETV 501, nüüd näete lahtris K10 ilmuvat sõltuvate toodete loendit
Märge: - Kui lisate toote nime ja tootekoodi, mida loendis ei kuvata.
Näiteks: - Oleme lisanud toote 26 ETV 505 tootekoodi alla, kuid kui valime toote ETV 505, siis lisatud toodet ripploendisse ei kuvata.
Niisiis, saate luua lihtsa tehnika abil sõltuvat ripploendit vaid kolme lihtsa sammuga.
2nd Näide:-
Selles näites näeme, kuidas saada sõltuv ripploend, kui teil on oma andmed, nagu on näidatud selles vertikaalses tabelis.
Sõltuva ripploendi koostamiseks kasutame kahte erinevat meetodit. Mõlemad on peaaegu sarnased tehnikad. Kuid üks on ilma nimetatud vahemikuta ja teisel on nimega vahemik.
1st Meetod:-
Selleks kasutame koos funktsioone OFFSET, MATCH & COUNTIF.
Kuna me teame, et dünaamilise vahemiku loomiseks kasutatakse funktsiooni OFFSET, siis loendi „Dünaamiline andmete valideerimine” loomiseks kasutame dünaamilise vahemiku tagastamiseks funktsiooni OFFSET.
MATCH -i kasutatakse Exceli loendis üksuse suhtelise asukoha tagastamiseks. Ja siin aitab see meil sobitada lehel meie vahemikus esmase ripploendi valitud kategooria ja tagastab numbri.
Ja COUNTIF -i kasutatakse kriteeriumidele vastavate lahtrite arvu saamiseks. Ja siin kasutame seda funktsiooni COUNTIF abil kuvatavate ridade arvu loendamiseks.
Järgige allpool toodud samme:-
- Valige lahter J21, milles loome oma esmaste andmete valideerimise loendi
- Andmete valideerimise dialoogiboksi avamiseks vajutage klahvi ALT+D+L
- Valige loend lubamise kategooriast
- Klõpsake vahekaardil Allikas ja valige vahemik B20: B24
- Ja klõpsake nuppu OK
- Minge lahtrisse K21 ja avage uuesti andmete valideerimise dialoogiboks
- Seejärel valime loendi ja sisestame allikasse järgmise funktsiooni:
- = OFFSET ($ 19 $, MATCH ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))
- Klõpsake nuppu OK
- Lahtris K21 näeme kõiki valitud tootekoodi vastavaid väärtusi:-
Niisiis, nii saate sõltuvate loendit, võttes funktsiooni lahtrisse viiteid.
2nd Meetod:-
Järgmise meetodi puhul kasutame kaskaaditud andmete valideerimiseks samas funktsioonis nimega vahemikku. Kõigepealt peame tootekoodi jaoks looma dünaamilise loendi. Kui andmetele lisatakse mõni uus toode, tuleks rippmenüüd sama kuvamiseks värskendada.
Selleks toimige järgmiselt:-
- Valige B19, seejärel vajutage CTRL + F3, et avada aken „Name Manager”
- Nüüd klõpsame nuppu "Uus" ja ilmub dialoogiboks "Määra nimi"
- Näeme, et nimi kuvatakse juba nimede kastis -sellepärast oleme valinud B9 enne akna „Nimehaldur” avamist. Ja kuna B19 -l on tekst sees, saame soovi korral selle mõnele muule nimele muuta.
- Sisestage allpool mainitud valem:-
= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))
- Klõpsake nuppu OK
Kuna oleme koostanud ainulaadsete toodete jaoks dünaamilise loendi, loome nüüd D -veerus oleva tootekoodivahemiku jaoks dünaamilise vahemiku.
Järgige samu samme, mida oleme järginud ainulaadse toote puhul:-
- Valige lahter D19, avage dialoogiboks Määra nimi
- Leiad, et nimi on juba olemas
- Viidetes sisestage järgmine valem:-
= OFFSET ('Sõltuv rippmenüü'! $ D $ 20,0,0, COUNTA ('Sõltuv rippmenüü'! $ D $ 20: $ D $ 35))
- Klõpsake nuppu OK
- Nüüd on mõlemad dünaamilised vahemikud valmis. Niisiis, läheme J22 -le ja vajutage "ALT + D + L" ja valige "Loend"
- Allikas on meil nimega vahemik, mille me määratlesime selle jaoks "Unikaalse tootekoodi" jaoks, nii et vajutame F3, et näha kõiki saadaolevaid nimega vahemikke
- Näeme nimega vahemik „Unikaalne tootekood”, nii et klõpsame sellel, seejärel klõpsame nuppu OK ja vajutame sisestusklahvi
- Hetkel, kui vajutame sisestusklahvi, saame lahtris J22 rippmenüü noole, mis sisaldab unikaalsete tootekoodide loendit
- Valige lahter K22 ja avage dialoogiboks „Andmete valideerimine”
- Kasutame sama funktsiooni, mida oleme kasutanud viimases meetodis, kuid nimega vahemik
- Valige loend ja sisestage allikasse järgmine valem:-
= OFFSET ($ E $ 19, MATCH ($ J $ 22, Product_Code, 0), 0, COUNTIF (Product_Code, J22))
- Klõpsake nuppu OK
- Nüüd on meil esmane rippmenüü ja alamtoodete loend
- Valige J22-st toode „ETV-101” ja K22-s näeme ainult selle „ETV-101” toote alla kuuluvaid nimesid. Ja kui me muudame mõnda toodet (“ETV-103”) J22-s, kuvab K22 selle koodi vastavad väärtused
Nüüd näeme, mis juhtub, kui lisame loendisse uue toote koodi? Kas neid ripploendeid värskendatakse?
Lisame loendisse uue toote; Järgige alltoodud samme:-
- Lisage toote kood Unique_Prod_Code loendisse
- Lisage andmetesse ka toote_kood ja toote_nimi:-
- Nüüd kontrollige ripploendist lisatud tootekoodi ja nime
3rd Näide:-
Meil on dünaamilised pealkirjad otse tabelist ja lisame tootevalikusse uusi tooteid. Tabel on samas vormingus, mida kasutasime 1st meetod.
4th Meetod:-
Järgige allpool toodud samme:-
- Valige rubriik A40: E40
- Looge esmalt päiste jaoks dünaamiline vahemik, avage dialoogiboks „Määra nimi”
- Kirjutage nime kohale „Pealkiri” ja seejärel sisestage „viitab” allolevale valemile:-
- Sisestage järgmine funktsioon:-
- = OFFSET ('Sõltuv rippmenüü'! $ A $ 40 ,,,, COUNTA ('Sõltuv ripploend'! $ 40: $ 40))
- Klõpsake nuppu OK
- Dünaamiline „Suuna” vahemik on nüüd valmis
Ja nüüd loome igale pealkirjale nimega vahemiku, järgige alltoodud samme:-
- Valige tabel vahemikus A40 kuni E50
- CTRL + SHIFT + F3 kiirklahv
- Tühistame märke 2nd valik
- Ja enne kui klõpsame nuppu OK, veenduge, et 1st valik "Ülemine rida"
- Nüüd oleme mõlema vahemikuga valmis
Nüüd koostame vanemate ripploendi
- Valige lahter J42
- Avage dialoogiboks Andmete valideerimine
- Pärast loendi valimist vajutame allikas F3, et saada pealkirjade nimeline vahemik. Klõpsame nuppu "Pealkiri", seejärel klõpsake nuppu OK ja vajutage sisestusklahvi. Meil on nüüd J42 -s vanemate loend
- Üksuste üksikasjade loendi loomiseks valige lahter K42
- Avage dialoogiboks Andmete valideerimine, vajutades klahvi ALT+D+L.
- Valige loend ja seejärel sisestage vahekaardil Allikas funktsioon:-
- = OFFSET (KAUDNE (ASENDUS ($ J $ 42, "", "_")) ,,, COUNTA (KAUDNE (ASENDUS ($ J $ 42, "", "_"))))
- Klõpsake nuppu OK
Nüüd valige J42 -st mõni üksus, ütleme, et valime “Üksus 01” ja vaadake ripploendit K42. Ja nagu eelmised 3 meetodit, saime ka siin sõltuva loendi.
Niisiis, mis on uut? Esimeses näites ei saanud te ühtegi toodet loendisse lisada, kuid siin saate lisada mis tahes uue toote. Ütleme nii, et lisame sellele üksusele uue toote. Me läheme A45 juurde ja kirjutame “ETV-501 Prod 05” ning tuleme siis tagasi K42 juurde ja siin on kõik. Näete, uus toode on lisatud.
- Nüüd lisage uue toote alla mõned tooted
Kui valime elemendi 06, läheme K42 -le ja klõpsame ripploendil. Üllataval kombel ei juhtu rippmenüü noolel klõpsates midagi. Selle põhjuseks on asjaolu, et oleme loonud kõik dünaamiliseks ja unustanud tabelile dünaamilise vahemiku loomise, mistõttu tooteid ei kuvata alamloendis.
Selleks peame kasutama erinevaid tehnikaid. Selleks on kaks meetodit. Saate luua tabeli või kasutada lihtsalt funktsiooni OFFSET. Järgmise meetodi puhul kasutame funktsiooni OFFSET ja näeme trikki ka tabelivahemiku laiendamiseks.
- Niisiis, me läheme kõigepealt J43 -le ja vajutage "ALT + D + L"
- Valime "Loend" ja seejärel allikas, vajutame F3 ja valime "Pealkiri", klõpsame nuppu OK ja seejärel vajutame sisestusklahvi
- Nüüd läheme K43 -le ja pärast „Loendi” valimist läheme jaotisse „Allikas” ja sisestame allpool nimetatud funktsiooni
= OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0) -1, COUNTA (OFFSET ($ A $ 40,1, MATCH ($ J $ 43, $ 40: $ 40,0)) -1,1000 , 1)))
- Klõpsake nuppu OK
Nüüd läheme tagasi ja valime lahtris J43 „Üksus 06 ja naaseme K43 -le ning klõpsame rippmenüü noolel. Kuid see ajaloend näitab tooteid, mille oleme uue toote jaoks lisanud. Ja valime esimese toote “ETV-506 Prod 01”.
Nii saate luua sõltuva ripploendi, kasutades erinevaid meetodeid mis tahes tüüpi andmevormingu jaoks.
Video: Kuidas luua Excelis sõltuvat (kaskaadset) ripploendit, kasutades Microsoft Excelis 5 erinevat tehnikat
Klõpsake video linki, et saada kiiret teavet selle kasutamise kohta. Tellige meie uus kanal ja õppige koos meiega!
Kui teile meeldisid meie ajaveebid, jagage seda oma sõpradega Facebookis. Samuti saate meid jälgida Twitteris ja Facebookis.
Tahaksime sinust kuulda, andke meile teada, kuidas saaksime oma tööd täiustada, täiendada või uuendada ning muuta see teie jaoks paremaks. Kirjuta meile meilisaidile