Siitä lähtien, kun Data Validation -pudotusvalikot lisättiin Exceliin vuonna 1997, ihmiset ovat yrittäneet selvittää tapaa muuttaa toinen avattava valikko ensimmäisen avattavan valinnan perusteella.
Jos esimerkiksi valitset Hedelmät A2: ssa, A4: n avattavasta valikosta löytyy Apple, Banana, Cherry. Mutta jos valitset Yrtit A2: sta, A4-luettelossa olisi anista, basilikaa, kanelia. Vuosien varrella on ollut monia ratkaisuja. Olen katsonut sitä ainakin kahdesti Podcastissa:
- Klassisessa menetelmässä käytettiin paljon nimettyjä alueita, kuten jaksossa 383 esitetään.
- Toinen menetelmä käytti OFFSET-kaavoja jaksossa 1606.
Kun uudet Dynamic Array -kaavat julkaistaan julkisessa esikatselussa, uusi FILTER-toiminto antaa meille uuden tavan tehdä riippuvainen vahvistus.
Sano, että tämä on tietokanta tuotteistasi:
Käytä =SORT(UNIQUE(B4:B23))D4: n kaavaa saadaksesi ainutlaatuisen luettelon luokituksista. Tämä on aivan uudenlainen kaava. Yksi kaava D4: ssä palauttaa monia vastauksia, jotka vuotavat moniin soluihin. Viitata Spiller Range, voit käyttää =D4#sijaan =D4.
Valitse solu, jolle haluat pitää datan vahvistus-valikon. Avaa tietojen vahvistus valitsemalla Alt + DL. Vaihda Salli-asetukseksi Lista. Määritä =D4#luettelon lähteeksi. Huomaa, että Hashtag (#) on Spiller - se tarkoittaa, että tarkoitat koko Spiller-sarjaa.
Suunnitelman mukaan joku valitsee luokituksen ensimmäisestä avattavasta valikosta. Sitten =FILTER(A4:A23,B4:B23=H3,"Choose Class First")E4: n kaava palauttaa kaikki kyseisen luokan tuotteet. Huomaa, että "Valitse luokka ensin" -toiminnon käyttö valinnaisena kolmannena argumenttina. Tämä estää #ARVO! virhe ilmestymisestä.
Luettelossa voi olla erilainen määrä kohteita valitun luokan mukaan. Tietojen vahvistamisen määrittäminen osoittamaan =E4#laajentuu tai supistuu luettelon pituuden kanssa.
Katso video
Videon transkriptio
Opi Excelistä, Podcast Episode 2248: Riippuvainen vahvistus taulukoiden avulla.
No hei. Tätä on käsitelty kahdesti aikaisemmin podcastissa, kuinka tehdä riippuvainen vahvistus ja mikä riippuvainen vahvistus on, että valitset ensin luokan ja sitten vastauksena siihen toinen avattava valikko muuttuu vain tämän luokan kohteita, ja ennen, tämä oli monimutkaista, ja uusien dynaamisten taulukoiden kanssa, jotka ilmoitettiin syyskuussa 2018 … ja nämä ovat tulossa markkinoille, joten sinulla on oltava Office 365. Juuri nyt, 10. lokakuuta, olen kuullut että he ovat noin 50%: lla Office-sisäpiiriläisistä, joten he vievät heidät hyvin hitaasti. Se tapahtuu todennäköisesti vuoden 2019 alkupuoliskolla ennen kuin saat nämä, mutta se antaa meille mahdollisuuden tehdä riippuvainen validointi paljon helpommin.
Joten minulla on täällä kaksi kaavaa. Ensimmäinen kaava on kaikkien luokitusten UNIQUE, ja lähetin sen SORT-komentoon. Joten se antaa minulle yhden kaavan, joka palauttaa 5 tulosta ja joka asuu D4: ssä. Joten tässä, missä haluan valita tietojen validoinnin, minä (DL - 1:09) … LÄHDE tulee olemaan = D4 #. Se # - olemme kutsuneet sitä spilleriksi - varmista, että se palauttaa kaikki D4: n tulokset. Joten, jos haluaisin lisätä uuden luokan tähän ja tämä kasvaa, D4 # noutaa ylimääräisen määrän, okei? (= Lajittele (AINUTLAATUINEN (B4: B23)))
Joten ensimmäinen vahvistus on melko yksinkertainen, mutta nyt kun tiedämme, että olemme valinneet CITRUSin - tämä tulee olemaan vaikeampaa - haluan suodattaa luettelon sarakkeesta A, jossa sarakkeen B kohde on sama kuin valittu kohde , hyvä on? Joten meidän on ensin annettava heidän valita jotain ja sitten, kun tiedän, että se on CITRUS, anna sitten KALKI, ORANSSI ja TANGERIINI, he valitsevat jotain muuta. MARJA. Tsekkaa tämä. Tieteellisten aikakauslehtien mukaan banaani on marja. En ole samaa mieltä. Ei tunne minusta marjaa, mutta älä syytä minua. Käytän vain Internetiä. BANAANI, KELTA ja Vadelma.
Nyt, tiedätkö, että hässäkkä tämän kanssa on joku, joka tulee alun perin tänne valitsematta mitään, joten tässä tapauksessa meillä on VALITSE ENSIN LUOKKA, mikä on kolmas argumentti, joka sanoo, jos mitään ei löydy, okei? Joten tiedät, että jos aloitamme tässä skenaariossa, valinta on VALITSE LUOKKA ENSIMMÄISEKSI. Ajatuksena on, että he valitsevat LUOKAN, KASVILLE, tämän päivityksen, ja sitten nämä tuotteet tulevat kyseisestä luettelosta. Tietojen vahvistus täällä, tietysti, se on toinen spiller, = E4 # saadaksesi sen toimimaan, okei? Joten, tämä on hienoa. (= SUODATIN (A4: A23, B4: B23 = H3, "Valitse ensin luokka"))
Katso Excel Dynamic Arrays -kirjani. Tämä on… se tulee olemaan ilmainen vuoden 2018 loppuun mennessä. Tarkista alla oleva YouTube-kuvauksen linkki, kuinka voit ladata sen, tässä esimerkissä ja 29 muuta esimerkkiä näiden kohteiden käytöstä.
No, kääri tänään. Dynaamiset taulukot antavat meille toisen tavan tehdä riippuvainen vahvistus. Jos et ole Office 365: ssä ja sinulla ei vielä ole näitä, palaa takaisin, luulen, videoon 1606, joka näyttää vanhan tavan tehdä tämä.
Haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla toisesta netcastista.
Lataa Excel-tiedosto
Excel-tiedoston lataaminen: dependable-validation-using-arrays.xlsx
Saat lisätietoja dynaamisista taulukoista tutustumalla Excelin dynaamisiin taulukkoihin suoraan kohtaan.
Päivän Excel-ajatus
Olen pyytänyt Excel Master -kaveriltani neuvoja Excelistä. Tämän päivän ajatus miettiä:
"Älä koskaan poista Excel-tiedostoa varmuuskopioimatta sitä ensin."
Mike Alexander
