Lige siden rullemenuer med datavalidering blev tilføjet til Excel i 1997, har folk forsøgt at finde ud af, hvordan den anden rullemenu ændres baseret på valget i den første rullemenu.
Hvis du f.eks. Vælger Frugt i A2, vil rullelisten i A4 tilbyde Apple, banan, kirsebær. Men hvis du vælger Urter fra A2, vil listen i A4 tilbyde anis, basilikum, kanel. Der har været mange løsninger gennem årene. Jeg har dækket det mindst to gange i Podcast:
- Den klassiske metode brugte mange navngivne intervaller som vist i afsnit 383.
- En anden metode anvendte OFFSET-formler i afsnit 1606.
Med frigivelsen af de nye Dynamic Array-formler i offentlig forhåndsvisning vil den nye FILTER-funktion give os en anden måde at gøre afhængig validering på.
Sig, at dette er din database med produkter:
Brug en formel =SORT(UNIQUE(B4:B23))i D4 for at få en unik liste over klassifikationerne. Dette er en helt ny formel. En formel i D4 returnerer mange svar, der spildes i mange celler. For at henvise til Spiller Range, skal du bruge i =D4#stedet for =D4.
Vælg en celle for at indeholde datavalideringsmenuen. Vælg Alt + DL for at åbne datavalidering. Skift Tillad til "Liste". Angiv =D4#som kilde til listen. Bemærk, at Hashtag (#) er Spiller - det betyder, at du henviser til hele Spiller Range.
Planen er, at nogen vælger en klassifikation fra den første rullemenu. Derefter =FILTER(A4:A23,B4:B23=H3,"Choose Class First")returnerer en formel i E4 alle produkterne i den kategori. Bemærk, at brug af "Vælg klasse først" som det valgfri tredje argument. Dette forhindrer en #VÆRDI! fejl vises.
Der kan være et andet antal varer på listen afhængigt af den valgte kategori. Opsætning af datavalidering, der peger på =E4#, udvides eller trækker sig sammen med længden af listen.
Se video
Videoudskrift
Lær Excel fra, Podcast-afsnit 2248: Afhængig validering ved hjælp af arrays.
Nå, hej. Dette er blevet behandlet to gange før på podcasten, hvordan man gør afhængig validering, og hvad afhængig validering er, får man først til at vælge en kategori, og som svar på det ændres den anden rulleliste til bare den emner fra den kategori, og før dette var kompliceret, og med de nye dynamiske arrays, der blev annonceret i september 2018 … og disse rulles ud, så du skal have Office 365. Lige nu 10. oktober har jeg hørt at de er på omkring 50% af Office-insidere, så de ruller dem meget langsomt ud. Det vil sandsynligvis være igennem første halvdel af 2019, før du får disse, men det giver os mulighed for at foretage afhængig validering på en meget lettere måde.
Så jeg har to formler her. Den første formel er UNIK af alle klassifikationer, og jeg sendte den til SORT-kommandoen. Så det giver mig 1 formel, der returnerer 5 resultater, og der bor i D4. Så her, hvor jeg vil vælge datavalidering, vil jeg (DL - 1:09) … KILDEN bliver = D4 #. At # - vi har kaldt det spilleren - sørg for at det returnerer alle resultaterne fra D4. Så hvis jeg tilføjer en ny kategori herovre og denne vokser, vil D4 # afhente det ekstra beløb, okay? (= SORT (UNIK (B4: B23)))
Så den første validering er ret enkel, men nu hvor vi ved, at vi har valgt CITRUS - dette bliver vanskeligere - vil jeg filtrere listen i kolonne A, hvor elementet i kolonne B svarer til det valgte element , i orden? Så først skal vi lade dem vælge noget, og så når jeg ved, at det er CITRUS, så giv mig KALD, ORANGE og TANGERINE, de ville vælge noget andet. BÆR. Se lige det her. De videnskabelige tidsskrifter siger, at en banan er et bær. Jeg er ikke enig i det. Føler mig ikke som et bær, men bebrejd mig ikke. Jeg bruger bare internettet ved du. BANANA, ELDERBERRY og RASPBERRY.
Nu ved du, besværet med dette er, at nogen i første omgang vil komme her uden at have valgt noget, og i så fald har vi VÆLG KLASSE FØRST, hvilket er det tredje argument, der siger, hvis der ikke findes noget, okay? Så ved du på den måde, hvis vi starter i dette scenario, bliver valget VÆLG KLASSE FØRST. Ideen er, at de vælger KLASSE, VEGETABLE, denne opdatering, og så kommer disse varer fra denne liste. DATAVALIDERINGEN her, selvfølgelig, det er en anden spiller, = E4 # for at få det til at fungere, okay? Så det er sejt. (= FILTER (A4: A23, B4: B23 = H3, ”Vælg klasse først”))
Tjek min bog Excel Dynamic Arrays. Dette er … det vil være gratis i slutningen af 2018. Tjek linket dernede i YouTube-beskrivelsen, hvordan du kan downloade det, for netop dette eksempel plus 29 andre eksempler på, hvordan du bruger disse emner.
Nå, pakk ind i dag. Dynamiske arrays giver os en anden måde at gøre afhængig validering på. Hvis du ikke er på Office 365, og du ikke har disse endnu, er du velkommen til at gå tilbage til, antager jeg, video 1606, der viser den gamle måde at gøre dette på.
Jeg vil gerne takke dig, fordi du kom forbi. Vi ses næste gang til endnu en netcast fra.
Download Excel-fil
For at downloade excel-filen: afhængig-validering-med-arrays.xlsx
Hvis du vil lære mere om dynamiske arrays, skal du tjekke Excel Dynamic Arrays Straight To The Point.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke at tænke over:
"Slet aldrig en Excel-fil uden først at tage backup af den."
Mike Alexander
