Ända sedan rullgardinsmenyerna för datavalidering tillkom i Excel 1997, har människor försökt ta fram ett sätt att ändra den andra rullgardinsmenyn baserat på valet i den första rullgardinsmenyn.
Om du till exempel väljer Frukt i A2 skulle rullgardinsmenyn i A4 erbjuda Apple, Banana, Cherry. Men om du väljer Örter från A2, skulle listan i A4 erbjuda anis, basilika, kanel. Det har funnits många lösningar genom åren. Jag har täckt det minst två gånger i Podcast:
- Den klassiska metoden använde många namngivna intervall som visas i avsnitt 383.
- En annan metod använde OFFSET-formler i avsnitt 1606.
Med lanseringen av de nya formlerna för dynamisk matris i offentlig förhandsvisning ger den nya FILTER-funktionen oss ytterligare ett sätt att göra beroende validering.
Säg att detta är din databas med produkter:
Använd en formel =SORT(UNIQUE(B4:B23))i D4 för att få en unik lista över klassificeringar. Detta är en helt ny typ av formel. En formel i D4 returnerar många svar som kommer att spillas in i många celler. För att hänvisa till Spiller Range, skulle du använda =D4#istället för =D4.
Välj en cell för att hålla menyn för datavalidering. Välj Alt + DL för att öppna datavalidering. Ändra Tillåt till "Lista". Ange =D4#som källa till listan. Observera att Hashtag (#) är Spelaren - det betyder att du hänvisar till hela Spiller Range.
Planen är att någon väljer en klassificering från den första rullgardinsmenyn. Sedan kommer en formel =FILTER(A4:A23,B4:B23=H3,"Choose Class First")i E4 att returnera alla produkter i den kategorin. Observera att användningen av "Välj klass först" som det valfria tredje argumentet. Detta förhindrar en #VÄRDE! fel visas.
Det kan finnas ett annat antal objekt i listan beroende på vald kategori. Att ställa in datavalidering som pekar på =E4#kommer att expandera eller krympa med listans längd.
Kolla på video
Videoutskrift
Lär dig Excel från, Podcast avsnitt 2248: Beroende validering med hjälp av arrays.
Men Hej. Detta har tagits upp två gånger tidigare på podcasten, hur man gör beroende validering, och vad beroende validering är är att du först väljer en kategori och sedan, som svar på det, ändras den andra rullgardinsmenyn till bara objekt från den kategorin, och tidigare var detta komplicerat och med de nya dynamiska matriserna som tillkännagavs i september 2018 … och dessa rullar ut, så du måste ha Office 365. Just nu 10 oktober har jag hört att de är på cirka 50% av Office-insiderna, så de rullar ut dem mycket långsamt. Det kommer förmodligen att gå igenom första halvan av 2019 innan du får dessa, men det gör att vi kan göra beroende validering på ett mycket enklare sätt.
Så jag har två formler här. Den första formeln är UNIK av alla klassificeringar och jag skickade den till SORT-kommandot. Så det ger mig 1 formel som ger 5 resultat och som lever i D4. Så här, där jag vill välja datavalidering, ska jag (DL - 1:09) … KÄLLAN kommer att vara = D4 #. Det # - vi har kallat det spelaren - se till att det returnerar alla resultat från D4. Så om jag skulle lägga till en ny kategori här och den växer kommer D4 # att hämta det extra beloppet, okej? (= SORTERA (UNIK (B4: B23)))
Så den första valideringen är ganska enkel, men nu när vi vet att vi har valt CITRUS - detta kommer att bli svårare - vill jag filtrera listan i kolumn A där artikeln i kolumn B motsvarar det valda objektet , OK? Så först måste vi låta dem välja något och sedan, när jag vet att det är CITRUS, sedan ge mig KALM, ORANGE och TANGERIN, skulle de välja något annat. BÄR. Kolla in det här. De vetenskapliga tidskrifterna säger att en banan är ett bär. Jag håller inte med det. Känns inte som ett bär för mig men klandra mig inte. Jag använder bara Internet, du vet. BANANA, ELDBERG och Raspberry.
Nu vet du, besväret med detta kommer att någon kommer att komma hit inledningsvis utan att ha valt någonting, och i så fall har vi VÄLJ KLASS FÖRST vilket är det tredje argumentet som säger om ingenting hittas, okej? Så du vet, på det sättet, om vi börjar i det här scenariot, kommer valet att VÄLJ KLASS FÖRST. Tanken är att de väljer CLASS, VEGETABLE, den här uppdateringen, och sedan kommer de artiklarna från listan. DATAVALIDERINGEN här, naturligtvis, det är en annan spelare, = E4 # för att få det att fungera, okej? Så det här är coolt. (= FILTER (A4: A23, B4: B23 = H3, ”Välj klass först”))
Kolla in min bok Excel Dynamic Arrays. Det här är … det kommer att bli gratis i slutet av 2018. Kontrollera länken där nere i YouTube-beskrivningen, hur du kan ladda ner den, för detta exempel plus 29 andra exempel på hur du använder dessa objekt.
Tja, avsluta för idag. Dynamiska matriser ger oss ett annat sätt att göra beroende validering. Om du inte använder Office 365 och du inte har dessa ännu, gå gärna tillbaka till, antar jag, video 1606 som visar det gamla sättet att göra detta.
Jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Ladda ner Excel-fil
För att ladda ner excel-filen: beroende-validering-med-arrays.xlsx
För att lära dig mer om dynamiska matriser, kolla in Dynamiska matriser i Excel direkt till punkten.
Excel-tanke på dagen
Jag har frågat mina Excel Master-vänner om deras råd om Excel. Dagens tanke att fundera på:
"Ta aldrig bort en Excel-fil utan att säkerhetskopiera den först."
Mike Alexander
