Dropdown liste med begrænsning

Jeg havde lidt problemer med at finde en overskrift til lige netop denne artikel, men endte altså med ovenstående. Det skulle forhåbentlig blive klart undervejs, hvad jeg mener. Jeg har valgt et placere løsningen under Excel, generelt og ikke under makroer, selv om den indeholder en smule kode.

Simpel dropdown liste med Datavalidering

I Excel kan man give brugerne mulighed for at udfylde en celle ved at vælge fra en liste. Dette kan gøres ved hjælp af funktionen Datavalidering. I figuren til venstre, har jeg valgt Datavalidering. Derefter har jeg indstillet til at tillade værdier fra liste. Som kilde til listen har jeg valgt et navngivet område, som jeg har kaldt "validliste". Jeg kunne også bare have indtastet det område i regnearket, som indeholder mine data, fx $I$3:$I$100. Fordelen ved at navngive området, er at listen så kan være i et andet ark, end det ark, hvor listen skal bruges. Bruger man cellereferencer, kunne man i ældre versioner af Excel kun referere til områder i samme ark, som listen var i. I dag virker det også, selv om man har en arkhenvisning med, fx =Ark2!A1:A10, men efter min mening er det nemmere, at bruge det navngivne område, da man så ikke glemmer at bruge absolutte referencer ved kopiering og lignende.

Det er vigtigt at huske lighedstegnet foran kilden, hvad enten denne er et navngivent område eller et område angivet ved cellereferencer. Glemmer man det, vil kun det/de ord, man har skrevet i kilden blive vist i listen.

Når man skal bruge listen, som i eksemplet er en oversigt over de 98 danske kommuner, vil de første af disse blive vist, og man kan så bladre ned, for at finde den, man har brug for, se figuren nedenfor.

Dette er en glimrende funktion til kortere lister, men allerede med de 98 kommuner i eksemplet, kan man risikere, at der skal bladres ret langt, før man finder den rigtige hvis man fx skal have fat i Aarhus.

Begynder man at skrive et ord som fx Bornholm, men færdiggør det ikke, inden man trykker Enter, vil man få en fejlmeddelelse. Først hvis ordet, man leder efter, skrives helt ud, eller vælges fra listen, vil indtastningen bliver accepteret.

Et ofte stillet spørgsmål på diverse fora er derfor, om man ikke kan lave en liste, hvor man kan skrive i boksen, og hvor udvalget så begrænses af det, men skriver. Altså skrive man et B vises ord, der begynder med B. Og det kan man godt, i hvert fald noget, der ligner. I den løsning, som jeg viser nedenfor, kan man skrive et bogstav, og så begrænses listen til de elementer, der indeholder dette bogstav, ikke nødvendigvis de, som begynder med dette bogstav. Efterhånden som man skriver flere bogstaver, vil listen blive kortere og kortere. Denne løsning kræver selvfølgelig en liste med indholdet til boksen (som ved datavalideringslisten), men også nogle hjælpekolonner (3 i alt), et ActiveX-objekt og en lille smule VBA kode. Men selv om det kan lyde besværligt og forklaringen er lang, er det faktisk ret let at gøre.

Dropdownliste med udvælgelse

Som det fremgår af illustrationen er listen nu reduceret til at vise elementer i listen (de kommuner), der indeholder bogstavkombinationen "br". Tilføjer jeg et "ø" forsvinder Holstebro og Ikast-Brande og kun Brøndby og Brønderslev vil være tilbage og så fremdeles. Vi skal dog helt hen "e" i Brønderslev før Brøndby forsvinder, men under alle omstændigheder vil den korte liste gøre valget fra listen en del nemmere.

Det første, man skal gøre, er at oprette selve dropdownlisten eller listeboksen, eller hvad den nu kaldes. Der er tale om et ActiveX objekt af typen ComboBox. For at indsætte dette objekt, skal man have fat i fanen Udvikler. Hvis denne fane ikke er vist i båndet, skal man gå ind i Excels indstillinger (findes forskellige steder afhængig af version). Under Populær kan man så sætte flueben i Vis fanen udvikler på båndet.

I fanen Udvikler skal man klikke på Indsæt i gruppen Kontrolelementer.  Så vises menuen til venstre. I den nederste gruppe, ActiveX-objekter vælger man ComboBox (Kombinationsboks på dansk). Det er knap nummer to fra venstre i den øverste række af ActiveX-objekter. Når knappen er valgt, klikkes et tilfældigt sted i regnearket, så indsættes boksen. Hvor den bliver sat ind er ikke væsentlig. Den kan flyttes til det ønskede sted på et senere tidspunkt.

Nu skal egenskaberne for comboboxen indstilles. Højreklik på denne og vælg Egenskaber (Properties på engelsk). Så vises en meget lang liste med egenskaber. Der er to faneblade i boksen, dels en alfabetisk sortering og dels en kategoriseret. I denne sammenhæng er det nok nemmest at blive i den alfabetiske oversigt. Selv om oversigten er lang, skal der kunne rettes i fire af disse egenskaber.

  • Først skal AutoWordSelect ændres fra True til False.
  • Så skal man udpege den celle, der skal kædes sammen med boksen under LinkedCell. Her har jeg i eksemplet valgt B3, men det kunne være en hvilken som helst celle, hvor man ønsker at det, der vælges i boksen, skal placeres.
  • Så skal der fyldes indhold i boksen. Det gøres under ListFillRange. Her angives navnet på et navngivent område (som vi endnu ikke har oprettet). I eksemplet har jeg valgt at kalde området for DropDownListe; er det ikke kreativt?
  • Endelig skal MatchEntry sættes til 2 - fmMatchEntryNone.

Hvis man vil afprøve om boksen virker (so far), kan man skrive noget i den. Det man skriver, skal så gerne blive vist i celle B3. Sker det ikke, må man lige kontrollere at LinkedCell er indstillet korrekt.

Nu skal vi så i gang med at få gjort klar til at fylde data i boksen. Det er her de tre hjælpekolonner kommer ind i billedet. I det eksempel, jeg har lagt op under Færdige eksempler, har jeg valgt kolonnerne C, D og E samt kolonnen F til listen over alle kommunerne. I praksis ville jeg nok flytte dem længere ud mod højre i regnearket, eller simpelthen skjule dem eller måske begge dele, men her er de altså for overskuelighedens skyld placeret i det område, der umiddelbart vises i arket. Så kan man også se, hvad der sker efterhånden som man skriver tekst i boksen. Listen med de 98 kommuner er placeret i F3:F100, og jeg har sorteret listen alfabetisk, men det er faktisk ikke nødvendigt for at funktionen kan virke. Det er igen gjort for overskuelighedens skyld. Listen over de 98 danske kommuner har jeg hentet fra nettet, men ved man ikke, hvad man skal få sin tid til at gå med, kan man jo taste dem alle selv (eller kopiere fra mit eksempel). Er det andre lister, der skal bruges, må man selvfølgelig selv i gang. Jeg har altså indsat mine kommuner fra F3 og nedefter.

Den første hjælpekolonne er C-kolonnen, startende i C3. Her skal man indsætte nedenstående formel:

=--ER.TAL(HVIS.FEJL(SØG($B$3;F3;1);""))

Denne formel kopieres nu nedad, lige så langt som der er elementer i F-kolonnen. I eksemplet til C100.

Denne formel giver som resultat et 1-tal, hvis det, der tastes i kombinationsboksen findes i den tilsvarende celle i listen over kommuner. Skriver jeg fx et 'p' vil der blev sat et 1-tal ud for de kommuner, hvis navn indeholder et 'P', og ud for alle andre kommuner, vil der stå '0'. Indtil der er skrevet noget i boksen, vil der stå '1' overalt.

Formlen forklaret:  SØG() leder efter den tekst, der står i B3 (overført fra kombinationsboksen), og den leder i den tekst, der står i F3. 1-tallet indikerer, at søgningen skal starte fra det første bogstav i F3. HVIS.FEJL() fortæller, at hvis det indtastede ikke findes, skal formlen returnere en blank og ikke en fejlkode. ER.TAL() undersøger om indholdet er et tal. Det er det imidlertid ikke umiddelbart, da formlen vil returnere enten SAND elle FALSK afhængig af om det søgte findes eller ej. De to "minustegn" foran ER.TAL() sikrer at SAND konverteres til 1 mens FALSK konverteres til 0. På den måde "opstår" listen med 1-taller og nuller.

I D3 indsættes nu denne formel

=HVIS(C3=1;TÆL.HVIS($C$3:C3;1);"")

Også denne formel kopieres nedad så den står ud for alle forekomster i F-kolonnen, her til og med D100. Formlen skriver et 1-tal ud for den første forekomst af det, der er tastet i kombinationsboksen (på grundlag af værdierne i C-kolonnen), et 2-tal ud for den næste forekomst og så fremdeles, mens alle andre celler er tomme. Vi får altså en en nummereret oversigt over de celler, der opfylder vores udfyldning. I eksemplet til venstre er der indtastet et 'c' i kombinationsboksen. Dette bogstav findes kun i et eneste kommunenavn, Frederica. Der står derfor 1 ud for Fredericia, mens cellerne ud for alle andre kommuner er tomme. Havde jeg i stedet skrevet 'brø', ville der have stået 1 ud for Brøndby og 2 ud for Brønderslev, mens alle andre ville have været tomme.

Formlen forklaret: HVIS undersøger om der står 1 i C3 (og i de tilsvarende celler, når formlen kopieres nedad. Står der ikke 1, sættes cellen til at være tom (blank). Hvis der står et 1-tal bruges TÆL.HVIS til at tælle, hvor mange 1-taller, der er i det talte område. Ved at fastholde C3 med absolutte referencer i den første celle i området, mens det ikke fastholdes i den sidste celle, sikres at området bliver dynamisk. I C3 tælles således området C3:C3, altså kun celle C3. I C4 tælles området C3:C4, altså cellerne C3 og C4 og så fremdeles. Når der tælles på denne måde, bliver antallet samtidigt et "nummer" på den pågældende forekomst. I eksemplet med 'brø" vil der stå 1-taller i C-kolonnen ud for Brøndby og Brønderslev og 0 ud for de øvrige kommuner. Ud for Brøndby tælles området C3:C9, og her er der et enkelt 1-tal, så Brøndby får "nummer 1". Ud for Brønderslev tælles området C3:C10, og i dette område er der to 1-taller, så Brønderslev får "nummer 2"). Disse numre skal vi så bruge i den sidste hjælpekolonne.

Den sidste formel, der skal bruges i en hjælpekolonne, er den mest komplekse. Jeg prøver at forklare den nedenfor, og håber at min forklaring bliver forståelig. Formlen, der skal indsættes i E3, er

=HVIS.FEJL(INDEKS($F$3:$F$100;SAMMENLIGN(RÆKKER($D$3:D3);$D$3:$D$100;0));"")

Formlen skal skrives på en enkelt linje, selv om den her evt. vistes på to afhængig af skærmopløsningen.

Også denne formel kopieres nedad til celle E100. Formlen "komprimerer" listen. Det vil sige, at den "fjerner" tomme rækker, så de celler i F-kolonnen, der opfylder de betingelser, vi har opstillet i hjælpekolonnerne C og D, skrives under hinanden uden tomme i mellem. De samles altså øverst i E-kolonnen i stedet for at ligge spredt i den orden, de oprindeligt stod i. Indtaster jeg fx et 't' i kombinationsboksen, kan jeg se, at der er 20 kommunenavne, der indeholder bogstavet 't', fra Albertslund til Vesthimmerland, og de står pænt spred i regnearket. Men nu samles de altså i E-kolonnen, som vist i figuren til venstre.

Af figuren fremgår at der er tastet 't' i kombinationsboksen og at dette 't' findes i B3 (det kan ikke ses af figuren, at det B3, da række- og kolonneangivelser ikke er vist, men det er det altså J). 1-tallet ud for Albetslund i C-kolonnen fortæller at 'Albertslund' indeholder et 't', og det tilsvarende 1-tal i D-kolonnen fortæller, at dette er den første forfekomst i 't'. Når der ikke er vist flere tal i D-kolonnen skyldes det, at den næste kommune, med bogstavet 't' i sit navn kommer længere nede på listen end Furesø. Faktisk er det kommunen lige efter Furesø, nemlig Faaborg-Midtfyn.

Formlen forklaret: Det er her, man kan miste overblikket. Jeg gør det faktisk lidt selv, når jeg skal forklare mig, hvilket jeg beklager. Jeg vil prøve at starte med INDEKS(). Denne funktion returnerer indholdet af en celle, der defineres ved et rækkenummer og et kolonnenummer. Her skal man være opmærksom på, at der ikke er tale om række- og kolonnenummer i regnearket, men kun i det område (matricen), der skal undersøges.

INDEKS(F3:F100;3;1) vil således returnere den værdi, der står i den tredje række og første kolonne i området F3:F100, i dette tilfælde Assens. I vores konkrete tilfælde udgøres matricen af cellerne F3:F100. Da vi skal kunne kopiere formlen nedad, gøres disse referencer absolutte. Desværre kender vi ikke på forhånd rækkenummeret, så det må vi først finde. Det gøres med SAMMENLIGN(). Denne funktion returnerer et række, eller kolonnenummer på en given forekomst af det man søger efter i en matrice. Her skal man ikke angive om der er tale om række-eller kolonnenummer, da SAMMENLIGN kun accepterer matricer, der består af enten en enkelt kolonne eller en enkelt række. Det, der skal søges efter, står først i funktionen, mens matricen, der søges i, står i det andet argument. Det sidste argument er "sammenligningstype" og her angiver et 0, at der kun skal findes noget, der er helt identisk med det, der søges efter. 1 eller -1 er andre typer. Dem kan du læse om i hjælpen til Excel. Her skal altså bruges 0. =SAMMENLIGN("Assens";F3:F100;0) vil returnere 3, fordi Assens står i tredje række i matricen.

Vi kan nu indsætte dette i INDEKS-formlen og få: =INDEKS(F3:F100;SAMMENLIGN("Assens";F3:F100;0);1). SAMMENLIGN fortæller at Assens står i tredje række og INDEKS returnerer så indholdet af denne række, nemlig Assens. Dette vil selvfølgelig være meningsløst, hvis vi kun skulle finde Assens hver gang. Det skal vi imidlertid ikke, og det er her RÆKKER kommer ind i billedet. RÆKKER fortæller, hvor mange rækker, der er i en matrice. =RÆKKER(F3:F100) vil således fortælle, at der er 98 rækker i matricen, svarende til en række pr. kommune. Ved at fastholde den første celle i området med absolutte referencer, men ikke den sidste vil området blive dynamisk, efterhånden som vi kopierer formlen nedad. I den første celle står således $D$3:D3, hvilket giver et 1-tal. I den næste står $D$3:D4 hvilket giver antallet af rækker til 2 og så fremdeles. Der kommer altså hele tiden flere rækker i området. Læg mærke til, at det er D-kolonnen. vi skal undersøge, ikke F, da det jo er i D-kolonnen, vi har markeret, hvilke kommuner, der skal med. Området, der skal sammenlignes med er hele tiden D3:D100, også når der kopieres, derfor de absolutte referencer her. Hvis der ikke er nogen, der opfylder vores kriterium, vil INDEKS returnere fejlkoden #I/T!. For at undgå det, pakker vi det hele ind i HVIS.FEJL, og angiver, at såfremt alt det, vi har undersøgt med RÆKKER, SAMMENLIGN og INDEKS giver en fejl og ikke en værdi, skal der returneres en blank. På den måde vil de celler, der står under den sidste med indhold, komme til at stå tomme, når formlen kopieres.

Jeg håber at forklaringen er til at forstå, hvis ikke, må man stole på mit ord. Det virker!

Men lad mig nu se at få gjort hele øvelsen færdig, efter disse lange formler og forklaringer. Vi skal først have oprettet det område, som vi tidligere kaldte DropDownListe. Gå til fanen Formler, og klik på Navnestyring i gruppen Definerede navne. Vælg Ny, så vises dialogboksen til venstre.

I ruden øverst skrives navnet på området, altså DropDownListe. Omfang skal der ikke ændres på, da det skal gælde hele projektmappen, men i andre tilfælde, kan det være aktuelt at ændre så navnet kun gælder i et bestemt ark. Kommentar skal man ikke udfylde, men her kunne man fx skrive, hvad området skal bruges til.

I ruden Referer til, skal vi have lavet et dynamisk område. Det skal kun indeholde de celler fra E-kolonnen, som faktisk har et indhold. Dette gøres ved at sætte Referer til til følgende formel:

=$E$3:INDEKS($E$3:$E$100;MAKS($D$3:$D$100);1)

Området, der refereres til  starter altid i E3, derfor de absolutte referencer. For at gøre det dynamisk i forhold til indholdet af D-kolonnen anvendes MAKS() til at finde det højeste tal, der står i D-kolonnen (den sidste forekomst af det søgte). Hvis det højest tal, der forekommer i D er 12, vil området, der navngives, derfor være E3:E15 (3+12). Da der kun er en kolonne i området, nemlig E, kan kolonnenummeret på forhånd defineres til 1. Når boksen er udfyldt klikkes på OK.

Måske har mine forsøg på at forklare, hvad der sker, forvirret mere end de oplyste, men det virker, og måske kan man trods alt forstå, hvad jeg mener. Ellers kan man studere eksemplet under Færdige eksempler.

Nu mangler bare VBA koden, der skal få boksen til at fungere. Den skal indsættes i arkets makroark, altså det ark, som indeholder kombinationsboksen. Koden kan kopieres direkte fra nedenstående:

Private Sub Combobox1_Change()
    ComboBox1.ListFillRange = "dropdownliste"
    Me.ComboBox1.DropDown
End Sub

Det eneste koden gør, er at den fylder indholdet fra kolonne E i dropdownlisten. Det er en hændelsesprocedure, som udløses hver gang der sker ændringer i ruden i boksen, og det gør der, hver gang, der tastes et bogstav. Da dette jo resulterer i, at kolonne E ændres, vil listen i dropdownboksen hele tiden blive opdateret til kun at vise navnene på de kommuner, som indeholder det indtastede. Når alt fungerer, kan man gå tilbage til Udvikler-fanen, klikke på Design knappen, og så kan man trække kombinationsboksen derhen, hvor man gerne vil have den, fx oven på den celle, den er kædet sammen med.

Husk til sidst, at da mappen indeholder kode, skal den gemmes som Excel Projektmappe med aktive makroer.