Dropdownlister med søgning, Excel 365
I artiklen Dropdownlister, med begrænsning, viste jeg, hvordan man kunne lave en dropdown liste, hvor det, der blev indtastet, begrænsede listen til elementer, der indeholdt den bogstavkombination, der blev indtastet. I eksemplet arbejdede jeg med en liste over landets 98 kommuner, og jeg brugte dels formler, dels en ActiveX komponent og endelig en lille smule VBA til at fylde listen.
Har
du imidlertid Excel 365, kan du løse opgaven uden brug af VBA se dog
bemærkningen sidst i artiklen. Jeg bruger igen en liste over danske kommuner,
men denne er den udvidet med befolkningstal og den region, som en given kommune
tilhører. Også denne gang, har jeg hentet listen på nettet, så jeg ikke skulle
bruge tid på at taste alle informationerne selv.
Igen viser jeg kun et uddrag af listen, men tro mig. Alle kommunerne er med. Jeg har i det færdige eksempel gemt lidsten i arket Liste, mens jeg vil lave min dropdownliste i arket Dropdown. Det færdige eksempel kan findes under Excel, Færdige eksempler. I dette ark vil jeg også vise den valgte kommunes indbyggertal og den region, som kommunen tilhører.
Som i den tidligere løsning, har jeg også denne gang brug for hjælp, men i dette tilfælde skal jeg kun bruge en enkelt hjælpekolonne, ikke tre. I eksemplet har jeg lavet hjælpekolonnen i arket Liste, lige ved siden af de data, der skal arbejdes med - også i dette eksempel for overskuelighedens skyld, men som i den foregående artikel om emnet, kan den naturligvis placeres uden for ”synsvidde”.
Grunden til at denne løsning kun virker i Excel 365 er, at den udnytter begrebet ”dynamisk matrix” ("dynamic array") og de funktioner, der hører til dette begreb, og de findes indtil videre kun i Excel 365, så vidt jeg ved. Jeg har pt. ikke adgang til Excel 2019, så jeg ved faktisk ikke, om funktionerne også findes her, men de blev først "indført" i Excel 365 i 2019, og Excel 2019 kom allerede på markedet i 2018.
I den første celle i hjælpekolonnen (her F og med formlen i F2), skal jeg have en formel, der kan virke lidt kompliceret, men som egentlig er ganske simpel. Jeg skal prøve at forklare, hvad der sker. I den første celle i hjælpekolonnen indsætter jeg denne formel: =SØG(Dropdown!A3;Liste!A2). Den søger i den første celle i mit dataområde (A2 i fanebladet Liste) efter den værdi, der tastes i A3 i Dropdown (den celle, hvor min dropdown skal være). Her skal man være opmærksom på, at SØG() returnerer den position, hvor det søgte starter. Hvis jeg derfor søger efter ”Al” (uden anførselstegn), vil der blive returneret 1, da Al starter på første position i Albertslund, som netop står i A2. Nu skal jeg imidlertid ikke kun lede i A2, men i hele det område, hvor kommunenavnene er anført, her A2:A99. I og med, der anvendes dynamisk matrix, behøver jeg ikke at kopiere formlen nedad; det gør Excel for mig, når jeg udvider formlen til hele området. Nu vil der fremkomme en liste, hvor de celler, der indeholder ”al” (der skelnes ikke mellem store og små bogstaver”), vil have et tal stående, nemlig for den position, hvor ”al” findes, mens de øvrige celler vil indeholde fejlkoden #VÆRDI!.
Jeg har imidlertid ikke brug for talværdier, men for SAND eller FALSK, afhængig af om den søgte kombination findes eller ikke. Det gør jeg ved at ”pakke” formlen ind i ER.TAL() funktionen: =ER.TAL(SØG(F1;Liste!A2:A99)). Igen skal jeg kun rette i formlen i F2, så tilretter Excel selv gennem den "overløbsfunktion”, som gælder for dynamiske matricer. Faktisk kan jeg kun rette i F2, da der i virkeligheden kun tilsyneladende står formler i cellerne under. Prøver jeg, sletter jeg bare det, der står og skriver jeg noget andet, vil det påvirke hele min dynamiske matrix, så lad være med det!
Nu ser mit område ud som vist i illustrationen herover. Nu vil jeg imidlertid kun se de værdier, der er SAND, og her kan jeg bruge en ny funktion, der netop hænger sammen med dynamiske matricer. Denne funktion kaldes FILTRER() og den har to argumenter; det område, der skal filtreres, her A2:A99, og det, der skal inkluderes i fileteret, og det var netop det, vi fandt med formlen, som den indtil nu har set ud. Når FILTRER() er tilføjet ser formlen derfor således ud:
=FILTRER(A2:A99;ER.TAL(SØG(F1;Liste!A2:A99));"Findes ikke!")
Det
sidste argument i FILTRER() fortæller bare, hvad vi gerne vil have vist, hvis
det vi leder efter, ikke findes i nogen forekomster i listen. Her kan man skrive,
hvad man vil, men vælg noget sigende. Søger vi nu efter ”Al”, vil listen kun
indeholde de celler, der faktisk indeholder ”Al”, som vist i illustrationen.
Som i den foregående artikel om emnet vil også denne løsning kun se på, om kombinationen findes et eller andet sted i teksten, ikke om de står i begyndelsen. Taster jeg noget, der ikke findes i noget kommunenavn fx ”Zar” får jeg svaret: Findes ikke!
I dette tilfælde hvor listen indeholder kommunenavne, er alle værdier unikke, men det er ikke nødvendigvis tilfældet med andre lister, hvor den samme værdi måske kan findes flere gange. I så fald vil vi gerne nøjes med at vise dem en gang i hjælpekolonnen. Også her kan vi udnytte en ny funktion knyttet til dynamiske matricer, nemlig funktionen ENTYDIGE(). Formlen i F2 ser nu således ud:
=ENTYDIGE(FILTRER(A2:A99;ER.TAL(SØG(Dropdown!A3;Liste!A2:A99));"Findes ikke!"))
Og retter jeg Allerød til Albertslund, så det altså kommer til at stå to gange, vil listen i F-kolonnen nu ser ud som vist her:
Albertslund står kun én gang, og Allerød er forvundet!
I mit tilfælde er listen over kommuner sorteret efter kommunenavn, men havde den været sorteret i anden rækkefølge, fx efter indbyggertal eller region, ville listen i hjælpekolonnen står i en ”rodet” rækkefølge. Det kan løses ved hjælp af endnu en ny funktion, SORTER(). Denne funktion har flere argumenter, men skal naturligvis kende matricen, der skal sorteres og andet behøver vi faktisk ikke i dette tilfælde, da sorteringsrækkefølgen ”by default” er faldende orden.
Nu ser formlen sådan ud:
=SORTER(ENTYDIGE(FILTRER(A2:A99;ER.TAL(SØG(Dropdown!A3;Liste!A2:A99));"Findes ikke!")))
Og dermed er hjælpekolonne færdig. Så skal vi bare have gjort vores dropdown færdig, og i modsætning til den forrige artikel, kan vi denne gang bruge Datavalidering. Vi skal nu tilbage til arket Dropdown. I celle A3 indsætter vi en datavalidering (fanebladet Data). Denne skal tillade Liste, og som kilde angiver vi den første celle i vores hjælpekolonne: =Liste!F2#. Hashtagget er vigtigt, da det fortæller, at vi vil bruge alle værdier i den dynamiske matrix. Når vi har gjort det, er det vigtigt at gå til det sidste faneblad i dialogboksen og fjernefluebenet fra Vis fejlmeddelelse, når der indtastes ugyldige data. Ellers kan vi ikke taste noget andet, uden at have valgt noget i første omgang, da ”al” jo ikke forekommer på listen, og dermed ikke er en gyldig værdi.
Så skal vi bare have hentet indbyggertal og region, og det kan vi gøre med LOPSLAG() eller INDEKS() og SAMMENLIGN(), men vi kan faktisk også gøre det med FILTRER(). Her kan vi bruge denne formel til at finde indbyggertallet: =FILTRER(Liste!B2:B99;Liste!$A$2:$A$99=Dropdown!$A$3;"") og denne kan så kopieres til cellen, hvor vi gerne vil have vist, hvilken region kommunen tilhører. Det sidste argument bruges til at fortælle, at hvis vi søger efter noget, der ikke findes på listen, bliver indbyggertal og kommune vist tomme.
End sidste detalje! Nu bliver listen over kommuner nok næppe udvidet foreløbigt, men det kunne ske ved andre lister, at der skulle tilføjes elementer. Det vil altså være fornuftigt, hvis listen i arket Liste, kan udvides uden at man skal ændre i formlerne. Det gøres nemmest ved at lave listen om til en Excel tabel. Stå et tilfældig sted i listen og tryk Ctril+t. Jeg har navngivet tabellen TabelListe, men det er der ikke noget krav om. Nu kan der tilføjes nye elementer til listen i bunden af denne, uden at referencerne i de forskellige formler, skal laves om.
NB! Vær opmærksom på, at denne løsning ikke giver mulighed for at skrive i dropdownlisten, når den er "foldet ud". Der skal altså tastes i denne, og derefter klikkes på pilen!
Skriver jeg ’brø’ og klikker på Dropdownpilen ser det således ud:
Og vælger jeg Brønderslev bliver resultatet.
God fornøjelse - hvis du altså bruger Excel 365!