Tähän mennessä tässä tietojen validointisarjassa olemme oppineet luomaan normaalin pudotusvalikon ja dynaamisen avattavan luettelon eri tekniikoilla ja tietojen validoinnilla Excelissä.
Ja tänään, tässä luvussa, näytämme sinulle, kuinka luoda riippuvainen pudotusvalikko Microsoft Excelissä eri menetelmillä.
Riippuva pudotusluettelo tunnetaan myös nimellä CSC -tietojen validointi, ja se rajoittaa avattavan luettelon valintoja riippuen toisesta tietojen validointia sisältävästä solusta. Toisin sanoen se riippuu ensimmäisessä avattavasta luettelosta valitusta arvosta, joka määrittää toisessa avattavassa luettelossa näytettävät arvot.
Tämä on hyvin yleinen skenaario, jossa käsitellään suuria tietoja tai joitain dynaamisia raportteja, jos haluat saada 2toinen solu näyttää luettelon, joka riippuu ensimmäisestä avattavasta valikosta valitusta luettelokohteesta.
Kuten tiedämme, että Excelissä on paljon tapoja suorittaa tietty tehtävä ja vastaavasti on monia tapoja luoda riippuvainen tietojen validointi Excelissä. Tänään esittelemme viisi eri tekniikkaa riippuvaisten tietojen validointiluettelon luomiseksi.
Raakatiedot voivat olla missä tahansa järjestyksessä tai muodossa, ja aina kun et voi muuttaa tietoja tai muotoa saadaksesi etsimäsi.
Joten olemme ottaneet yhden tietojoukon, mutta 3 eri muodossa, jotta saisimme riippuvan avattavan luettelon. Ja kuten näette, tietomme ovat vasemmalla puolella, joka on sarakkeesta A sarakkeeseen E, ja meillä on odotettu tulos oikealla puolella, joka on sarakkeessa J & K. Sarakkeessa J on ensisijainen vahvistus sarake K on riippuvainen ja näyttää arvot sarakkeesta J valitun arvon mukaan.
1st Esimerkki:-
2toinen Esimerkki:-
3rd Esimerkki:-
1st Esimerkki:-
Meillä on tuoteluettelo jokaiselle tuotekoodille sarakkeesta A8 - E13. Ja haluamme valita tuotekoodi J10: ssä ja sitten valitusta tuotekoodista riippuen tuotteen nimen solussa K10.
Ensimmäinen menetelmä:-
Ensimmäinen menetelmä on hyvin yksinkertainen ja lyhyt, ja se vaatii vain 3 vaihetta riippuvan avattavan luettelon saamiseksi. Se toimii kuitenkin vain onnistuneesti, kunnes et tee muutoksia alueeseesi. Kun olet muuttanut tietojasi, sinun on ensin muutettava nimettyä aluetta, jotta saat päivitetyn porrastetun datan vahvistuksen.
Noudata alla olevia ohjeita:-
- Valitse koko taulukko A8 - E13
- Siirry sitten "Kaavat" -välilehdelle ja sitten "Määritetyt nimet" -luokassa "Luo valinnasta"
- Voit käyttää myös pikanäppäintä CTRL + VAIHTO + F3
- Luo nimet valinnoista -valintaikkuna tulee näkyviin
- Se pyytää vahvistamaan, mitä rivejä ja sarakkeita käytetään muiden rivien ja sarakkeiden nimien luomiseen. Vahvistamme nimien luomisen käyttämällä "yläriviä" ja poista valinta kohdasta 2toinen vaihtoehto ja napsauta sitten OK
Huomautus: - Välilyöntejä ja muita erikoismerkkejä paitsi alaviivoja ja pisteitä ei sallita niminä. Oletuksena se muutetaan alaviivaksi. Käytä siis alaviivoja ja pisteitä sanojen erottamiseen. Myös ensimmäinen kirjain ei voi olla numero; sen on oltava kirjain, alaviiva tai viiva.
- Vahvistaaksemme nyt, että kullakin alueella on nimi, menemme "Nimienhallintaan" (paina CTRL + F3)
- Siellä näemme kaikki viisi nimettyä aluetta
- Ja myös voimme nähdä, että jokaisella alueen nimellä on alaviiva merkkijonon keskellä tyhjän sijaan
Luomme nyt avattavan luettelon:-
- Valitse solu J10 ja avaa Tietojen vahvistus -valintaikkuna painamalla ALT ++ D+L
- Valitse Lista> ja kirjoita alue A8: E8 Lähde -välilehdelle
- Napsauta OK
- Luomme nyt riippuvaisen luettelon soluun K10
- Avaa Tietojen vahvistus -valintaikkuna painamalla näppäintä ALT+D+L.
- Valitse Lista, kirjoita lähteeseen tämä toiminto:- = Epäsuora (SUBSTITUTE ($ J $ 10, "", "_"))
Tietojen validoinnissa riippuvaisen luettelon luomiseksi olemme käyttäneet INDIRECT -funktiota arvon palauttamiseen ensisijaisten tietojen validointiluettelon perusteella. Ja korottaaksemme alaviivan välilyönnillä, käytämme SUBSTITUTE -toimintoa INDIRECT -toiminnon sisällä.
- Napsauta OK
Kun valitsemme minkä tahansa tuotekoodin solussa J10, valitun tuotekoodin tuoteluettelo näkyy solussa K10. Esimerkki: - Olemme valinneet ETV 501: n, nyt näet, että riippuvainen tuoteluettelo näkyy solussa K10
Huomautus: - Aina kun lisäät tuotteen nimen ja tuotekoodi, jotka eivät näy luettelossa.
Esimerkiksi: - Olemme lisänneet tuotteen 26 ETV 505 -koodin alle, mutta kun valitsemme ETV 505 -tuotteen, lisätty tuote ei näy avattavassa luettelossa.
Joten, näin voit luoda riippuvaisen pudotusvalikon yksinkertaisella tekniikalla vain 3 yksinkertaisessa vaiheessa.
2toinen Esimerkki:-
Tässä esimerkissä näemme, miten saat riippuvaisen avattavan luettelon, kun sinulla on tietosi, kuten tässä pystysuorassa taulukossa.
Käytämme kahta eri menetelmää luodaksemme riippuvaisen avattavan luettelon. Molemmat ovat melkein samanlaisia tekniikoita. Toisella ei kuitenkaan ole nimettyä aluetta, ja toisella on nimetty alue.
1st Menetelmä:-
Tätä varten käytämme OFFSET-, MATCH & COUNTIF -toimintoja yhdessä.
Koska tiedämme, että OFFSET -toimintoa käytetään dynaamisen alueen luomiseen, luodaksemme ”Dynaaminen tietojen validointi” -luettelon, palaamme dynaamisen alueen OFFSET -toiminnolla.
MATCH -funktiota käytetään palauttamaan kohteen suhteellinen sijainti Excelin luettelossa. Ja tässä se auttaa meitä sovittamaan taulukon valikoimamme ensisijaisessa pudotusvalikossa valitun luokan ja palauttaa numeron.
Ja COUNTIF -arvoa käytetään ehtoja vastaavien solujen lukumäärän saamiseen. Ja tässä käytämme tätä laskeaksesi näytettävien rivien määrän käyttämällä COUNTIF -toimintoa.
Noudata alla olevia ohjeita:-
- Valitse solu J21, johon luomme ensisijaisen datan vahvistusluettelon
- Avaa tietojen validointi -valintaikkuna painamalla näppäintä ALT+D+L
- Valitse luettelo sallintaluokasta
- Napsauta Lähde -välilehteä ja valitse alue B20: B24
- Ja napsauta OK
- Siirry soluun K21 ja avaa tietojen validointi -valintaikkuna uudelleen
- Sitten valitsemme Lista ja syötetään lähteeseen seuraava toiminto:
- = OFFSET ($ E $ 19, MATCH ($ J $ 21, $ D $ 20: $ D $ 32,0), 0, COUNTIF ($ D $ 20: $ D $ 32, $ J $ 21))
- Napsauta OK
- Solussa K21 näemme kaikki valitun tuotekoodin vastaavat arvot:-
Joten, näin saat riippuvaisen luettelon ottamalla soluviittaukset funktiosta.
2toinen Menetelmä:-
Seuraavassa menetelmässä käytämme nimettyä aluetta samassa toiminnossa saadaksesi porrastetun datan vahvistuksen. Ensin meidän on luotava dynaaminen luettelo tuotekoodille. Jos tietoihin lisätään uutta tuotetta, avattava valikko on päivitettävä näyttämään sama.
Voit tehdä saman noudattamalla alla olevia ohjeita:-
- Valitse B19 ja avaa “Name Manager” -ikkuna painamalla CTRL + F3
- Napsauta nyt "Uusi" ja "Määritä nimi" -valintaikkuna tulee näkyviin
- Näemme, että nimi näkyy jo nimikentässä -tämä johtuu siitä, että olemme valinneet B9 ennen "Nimenhallinta" -ikkunan avaamista. Ja koska B19: ssä on tekstiä, voimme halutessamme muuttaa sen johonkin toiseen nimeen.
- Anna alla mainittu kaava:-
= OFFSET ('DependentDropDownList'! $ B $ 20,0,0, COUNTA ('DependentDropDownList'! $ B $ 20: $ B $ 32))
- Napsauta OK
Koska olemme luoneet dynaamisen luettelon ainutlaatuisille tuotteille, luomme nyt dynaamisen alueen sarakkeessa D oleville tuotekoodialueille.
Noudata samoja vaiheita, joita olemme noudattaneet ainutlaatuisen tuotteen kohdalla:-
- Valitse solu D19, avaa Määritä nimi -valintaikkuna
- Löydät nimen jo olemassa
- Kirjoita viitteisiin alla oleva kaava:-
= OFFSET ('Riippuva pudotusvalikko'! $ D $ 20,0,0, COUNTA ('Riippuvainen pudotusvalikko'! $ D $ 20: $ D $ 35))
- Napsauta OK
- Nyt molemmat dynaamiset alueet ovat valmiita. Siirrymme siis kohtaan J22 ja painamme "ALT + D + L" ja valitse "Lista"
- Lähteenä meillä on nimetty alue, jonka määritimme sille "Ainutlaatuinen tuotekoodi", joten näemme kaikki käytettävissä olevat nimetyt alueet painamalla F3
- Voimme nähdä ”Ainutlaatuinen tuotekoodi” -nimisen alueen, joten napsautamme sitä ja sitten OK ja painamme enter
- Heti kun painamme Enter -näppäintä, saamme avattavan nuolen soluun J22, joka sisältää luettelon ainutlaatuisista tuotekoodeista
- Valitse solu K22 ja avaa "Tietojen validointi" -valintaikkuna
- Käytämme samaa toimintoa, jota olemme käyttäneet viimeisessä menetelmässä, mutta nimetyllä alueella
- Valitse luettelo ja kirjoita sitten lähteeseen seuraava kaava:-
= OFFSET ($ E $ 19, MATCH ($ J $ 22, Product_Code, 0), 0, COUNTIF (Product_Code, J22))
- Napsauta OK
- Nyt meillä on ensisijainen pudotusvalikko ja lapsiluettelo
- Valitse ”ETV-101” tuote J22: sta, ja K22: ssa näemme vain tämän ETV-101-tuotteen alla olevat nimet. Ja kun muutamme mitä tahansa tuotetta (“ETV-103”) kohdassa J22, K22 näyttää kyseisen koodin vastaavat arvot
Katsotaan nyt, mitä tapahtuu, kun lisäämme luetteloon uusia tuotekoodeja? Päivitetäänkö nämä avattavat luettelot?
Lisätään uusi tuote luetteloon; Noudata alla olevia ohjeita:-
- Lisää tuotekoodi Unique_Prod_Code -luetteloon
- Lisää dataan myös Product_Code ja Product_Name:-
- Tarkista nyt pudotusvalikosta lisätty tuotekoodi ja nimi
3rd Esimerkki:-
Meillä on dynaamiset otsikot suoraan taulukosta, ja lisäämme uusia tuotteita valikoimaan. Taulukko on samassa muodossa kuin 1st menetelmä.
4th Menetelmä:-
Noudata alla olevia ohjeita:-
- Valitse otsikko A40: E40
- Luo otsikoille dynaaminen alue ensin, avaa Määrittele nimi -valintaikkuna
- Kirjoita nimen kohdalle "Otsikko" ja kirjoita sitten "viittaa" -kohtaan alla mainittu kaava:-
- Syötä alla oleva toiminto:-
- = OFFSET ('Riippuvainen pudotusvalikko'! $ A $ 40 ,,,, COUNTA ('Riippuvainen pudotusvalikko'! $ 40: $ 40))
- Napsauta OK
- Dynaaminen "Suunta" -alue on nyt valmis
Ja nyt luomme nimetyn alueen kullekin otsikolle, noudata alla olevia ohjeita:-
- Valitse taulukko A40 - E50
- CTRL + VAIHTO + F3 -pikanäppäin
- Poistamme valinnan 2toinen vaihtoehto
- Ja ennen kuin napsautamme OK, varmista 1st vaihtoehto "Ylin rivi" on valittu
- Nyt olemme valmiita molemmille alueille
Nyt valmistamme avattavan vanhemman luettelon
- Valitse solu J42
- Avaa Tietojen vahvistus -valintaikkuna
- Kun olet valinnut ”Lista”, painamme lähteestä F3 -näppäintä saadaksemme nimettyä aluetta otsikoille. Napsauta "Otsikko" ja sitten OK ja paina enter. Meillä on nyt vanhempien luettelo J42: ssa
- Jos haluat luoda luettelon kohteen tiedoista, valitse soluK42
- Avaa Tietojen vahvistus -valintaikkuna painamalla näppäintä ALT+D+L.
- Valitse Lista ja kirjoita sitten alla oleva toiminto Lähde-välilehdelle:-
- = SIIRTÖ (Epäsuora (KORVAUS (KORVAUS ($ J $ 42, "", "_"))) ,,, COUNTA (Epäsuora (SUBSTITUTE ($ J $ 42, "", "_"))))
- Napsauta OK
Valitse nyt jokin kohde J42: sta, sanomme, että valitsemme ”Tuote 01” ja katso avattavaa K42 -luetteloa. Ja kuten edelliset 3 menetelmää, meillä on myös riippuvainen luettelo täällä.
Mikä on uutta? Ensimmäisessä esimerkissä et voinut lisätä yhtään tuotetta luetteloon, mutta tässä voit lisätä minkä tahansa uuden tuotteen. Sanotaan siis, että lisäämme tähän tuotteeseen uuden tuotteen. Siirrymme kohtaan A45 ja kirjoitamme ”ETV-501 Prod 05” ja palaamme sitten takaisin kohtaan K42 ja näillä mennään. Näet, että uusi tuote on lisätty.
- Lisää nyt muutama tuote uuden tuotteen alle
Kun valitsemme ”Tuote 06”, siirrymme kohtaan K42 ja napsautamme avattavaa luetteloa. Yllättäen mitään ei tapahdu, kun napsautat avattavaa nuolta. Tämä johtuu siitä, että olemme luoneet kaiken dynaamisen ja unohtaneet luoda taulukolle dynaamisen alueen, minkä vuoksi tuotteet eivät näy aliluettelossa.
Tätä varten meidän on käytettävä erilaisia tekniikoita. On kaksi tapaa tehdä se. Voit joko luoda taulukon tai käyttää vain OFFSET -toimintoa. Ja seuraavassa menetelmässä käytämme OFFSET -toimintoa, ja näemme temppun myös laajentaa taulukkoaluetta.
- Joten menemme ensin kohtaan J43 ja painamme "ALT + D + L"
- Valitsemme "Lista" ja sitten lähteessä painamme F3 ja valitse "Otsikko" ja napsauta OK ja paina sitten Enter
- Siirrymme nyt kohtaan K43, ja kun olet valinnut ”Lista”, siirrymme ”Lähde” -osaan ja kirjoitamme alla mainitun toiminnon
= 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)))
- Napsauta OK
Nyt menemme takaisin ja valitse ”Tuote 06 J43 -solussa ja palaa K43: een ja napsauta avattavaa nuolta. Tällä kertaa tämä luettelo näyttää tuotteet, jotka olemme lisänneet uuteen tuotteeseen. Ja valitsemme ensimmäisen tuotteen “ETV-506 Prod 01”.
Näin voit luoda riippuvaisen avattavan luettelon eri menetelmillä kaikentyyppisille datamuodoille.
Video: Kuinka luoda riippuvainen (porrastettu) pudotusvalikko Excelissä käyttämällä 5 eri tekniikkaa Microsoft Excelissä
Napsauta videolinkkiä, niin pääset nopeasti käyttämään sitä. Tilaa uusi kanavamme ja jatka oppimista kanssamme!
Jos pidit blogistamme, jaa se ystävillesi Facebookissa. Voit myös seurata meitä Twitterissä ja Facebookissa.
Haluaisimme kuulla sinusta, kerro meille, kuinka voimme parantaa, täydentää tai innovoida työtämme ja parantaa sitä sinulle. Kirjoita meille sähköpostitse