Sinds de vervolgkeuzemenu's voor gegevensvalidatie in 1997 aan Excel zijn toegevoegd, hebben mensen geprobeerd een manier te bedenken om de tweede vervolgkeuzelijst te laten veranderen op basis van de selectie in de eerste vervolgkeuzelijst.
Als u bijvoorbeeld Fruit kiest in A2, zou de vervolgkeuzelijst in A4 Apple, Banana, Cherry aanbieden. Maar als u Kruiden uit A2 kiest, zou de lijst in A4 Anijs, Basilicum, Kaneel bevatten. Er zijn in de loop der jaren veel oplossingen geweest. Ik heb het minstens twee keer behandeld in de Podcast:
- De klassieke methode gebruikte veel benoemde bereiken, zoals weergegeven in aflevering 383.
- Een andere methode gebruikte OFFSET-formules in aflevering 1606.
Met de release van de nieuwe Dynamic Array-formules in Public Preview, geeft de nieuwe FILTER-functie ons een andere manier om Dependent Validation uit te voeren.
Stel dat dit uw database met producten is:
Gebruik een formule van =SORT(UNIQUE(B4:B23))in D4 om een unieke lijst met classificaties te krijgen. Dit is een geheel nieuw type formule. Eén formule in D4 retourneert veel antwoorden die in veel cellen terechtkomen. Om naar de Spiller-reeks te verwijzen, zou u in =D4#plaats van gebruiken =D4.
Selecteer een cel om het menu Gegevensvalidatie vast te houden. Kies Alt + DL om gegevensvalidatie te openen. Wijzig Toestaan in "Lijst". Geef op =D4#als de bron van de lijst. Merk op dat de hashtag (#) de spiller is - dit betekent dat u verwijst naar de hele spillerreeks.
Het plan is dat iemand een classificatie kiest uit het eerste vervolgkeuzemenu. Vervolgens =FILTER(A4:A23,B4:B23=H3,"Choose Class First")retourneert een formule van in E4 alle producten in die categorie. Merk op dat het gebruik van "Kies eerst klasse" als het optionele derde argument. Dit voorkomt een #WAARDE! fout van het verschijnen.
Afhankelijk van de geselecteerde categorie kan er een ander aantal items in de lijst staan. Het instellen van gegevensvalidatie die naar verwijst, =E4#zal uitbreiden of inkrimpen met de lengte van de lijst.
Bekijk video
Videotranscriptie
Learn Excel From, Podcast Episode 2248: Dependent Validation using arrays.
Hallo daar. Dit is twee keer eerder behandeld in de podcast, hoe je afhankelijke validatie uitvoert en wat afhankelijke validatie is, je mag eerst een categorie kiezen en vervolgens, als reactie daarop, verandert de tweede vervolgkeuzelijst in alleen de items uit die categorie, en voorheen was dit gecompliceerd, en met de nieuwe dynamische arrays die in september 2018 werden aangekondigd … en deze worden uitgerold, dus je moet Office 365 hebben. Op 10 oktober heb ik gehoord dat ze op ongeveer 50% van de Office-insiders zitten, dus ze rollen ze heel langzaam uit. Het zal waarschijnlijk tot de eerste helft van 2019 duren voordat je deze krijgt, maar het zal ons in staat stellen om afhankelijke validatie op een veel eenvoudigere manier uit te voeren.
Dus ik heb hier twee formules. De eerste formule is de UNIEKE van alle classificaties en ik stuurde die naar het SORT-commando. Dus dat geeft me 1 formule die 5 resultaten oplevert en die leeft in D4. Dus hier, waar ik de gegevensvalidatie wil kiezen, zal ik (DL - 1:09) … de BRON is = D4 #. Dat # - we noemen het de spiller - zorg ervoor dat het alle resultaten van D4 retourneert. Dus als ik hier een nieuwe categorie zou toevoegen en deze groeit, zal D4 # dat extra bedrag ophalen, oké? (= SORTEREN (UNIEK (B4: B23)))
Dus die eerste validatie is vrij eenvoudig, maar nu we weten dat we CITRUS hebben gekozen - dit wordt moeilijker - wil ik de lijst in kolom A filteren waar het item in kolom B gelijk is aan het gekozen item , oké? Dus moeten we ze eerst iets laten kiezen en dan, als ik weet dat het CITRUS is, en me dan de LIMOEN, ORANJE en TANGERINE geven, dan kiezen ze voor iets anders. BES. Kijk hier eens naar. Volgens de wetenschappelijke tijdschriften is een banaan een bes. Ik ben het daar niet mee eens. Voelt me geen bes, maar neem het mij niet kwalijk. Ik gebruik gewoon internet. BANAAN, Vlierbes en FRAMBOOS.
Weet je, het gedoe hiermee is dat iemand in eerste instantie hier komt zonder iets te hebben gekozen, en in dat geval hebben we dus eerst KIES KLASSE, wat is dat derde argument dat zegt dat als er niets wordt gevonden, oké? Dus, weet je, op die manier, als we in dit scenario beginnen, is de keuze: KIES EERST KLASSE. Het idee is dat ze de KLASSE, GROENTE, deze updates kiezen en vervolgens komen die items uit die lijst. De GEGEVENSVALIDATIE hier, natuurlijk, dat is een andere spiller, = E4 # om dat te laten werken, oké? Dus dit is cool. (= FILTER (A4: A23, B4: B23 = H3, "Kies eerst klasse"))
Bekijk mijn boek Excel Dynamic Arrays. Dit is… het wordt gratis tot eind 2018. Bekijk de link daar beneden in de YouTube-beschrijving, hoe je het kunt downloaden, voor dit voorbeeld plus 29 andere voorbeelden van hoe je deze items kunt gebruiken.
Nou, sluit je af voor vandaag. Dynamische arrays bieden ons een andere manier om afhankelijke validatie uit te voeren. Als je Office 365 niet gebruikt en je hebt deze nog niet, ga dan gerust terug naar, veronderstel ik, video 1606 die de oude manier laat zien om dit te doen.
Ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download Excel-bestand
Om het Excel-bestand te downloaden: afhankelijke-validatie-met-arrays.xlsx
Raadpleeg Excel Dynamic Arrays Straight To The Point voor meer informatie over dynamische arrays.
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Verwijder nooit een Excel-bestand zonder er eerst een back-up van te maken."
Mike Alexander
