Dynamiske nedtrekkslister, datavalidering med UNIK() og FILTRER()

Her har vi laget et eksempel på hvordan du kan lage nedtrekkslister som relaterer seg til hverandre – altså hvordan alternativene i en liste endrer seg når du gjør et valg i en annen nedtrekksliste. Vi bruker datavalidering og funksjonene UNIK() og FILTRER().

Du kan laste ned eksempelfilen i bunnen av denne artikkelen.

Hvis jeg f.eks velger KIA som bilmerke i den første nedtrekksmenyen, vises bare KIA-modeller i den neste nedtrekksmenyen.

Datagrunnlaget

I kolonne A-B har vi datagrunnlaget

Trekke ut unike bilmerker

Vi kan bruke UNIK() for å trekke ut de forskjellige bilmerkene:

=UNIK(A2:A1000)

Dette gir oss listen

Null får vi fordi vi har dratt med oss 1000 rader, og de fleste av disse er tomme, og har verdien 0. Disse kvitter vi oss med ved å bruke funksjonen FILTRER():

=UNIK(FILTRER(A2:A1000;A2:A1000<>0))

Altså alle verdiene i A-kolonnen som ikke er 0. Da får vi disse valgene i nedtrekksmenyen:

Nedtrekksmeny for bilmerker

I celle H2 setter vi inn nedtrekkmenyen som skal vise bilmerker. Da klikker vi i celle H2 og velger Datavalidering:

Under Tillat velger vi Liste, og så setter vi inn D2# i Kilde. Celle D2 er starten på UNIK-formelen vår, så i stedet for å sette f.eks D2:D100, kan vi sette D2#, som betyr at vi skal bruke alle radene som UNIK returnerer. Dette gjelder for alle funksjoner i Excel som returnerer matriser.

Nå har vi fått pilen til høyre for H2, og kan velge bilmerke.

Trekke ut tilhørende modeller

Nå er vi klare for å lage nedtrekkmenyen for de modellene som finnes for det valgte bilmerket. Denne formelen setter vi inn i celle F2:

=FILTRER(B2:B1000;A2:A1000=H2)

Her filtrerer vi ut alle verdiene i området B2:B1000 hvor verdien i H2 står på samme rad i A-kolonnen.

Har vi valgt KIA, blir listen i F2 slik:

Velge modell

Nå mangler vi bare datavalideringen i J2:

Her bruker vi F2# som kilde, som er listen over modeller som hører til merket som vi har valgt i H2.

I Eksemplet har jeg lagt datagrunnlaget i samme fane som nedtrekksmenyene – da blir det enklere å følge med både for meg og deg. Det er lurt å legge disse i separate faner, slik at du kan lime inn et oppdatert datagrunnlag uten å tenke på om du skriver over formlene dine.

Last ned

Last ned Excelboka her: