Självfiltrerande Dropdown lista i Excel
I följande exempel så skall vi jobba vidare med föregående uppgift då vi skapade en dynamisk dropdown-lista med endast unika värden. I följande exempel så skall vi lägga till en funktion som gör att man endast kan använda varje värde i dropdown listan en gång. När ett värde väl är använt så skall det värdet filtreras bort ifrån dropdown-listan.
Vi jobbar vidare med filen från föregående exempel som ser ut enligt bilden nedan och du kan ladda ner exempelfilen under inläggets rubrik.
I kolumn F har vi våra unika värden ifrån Namn i
kolumn A. Listan med Unika Personer i F används som källa för vår dropdownlista.
Vi skall nu göra en filtrering av tabellen Unika Personer med formeln FILTER. Med formlen FILTER så kan man referera till en lista och samtidigt
sätta villkor för vilka värden som skall inkluderas. Vårat villkor skall vara
att de värden som finns i kolumn D inte skall inkluderas.
Vi börjar med att skapa en ny lista i kolumn H där vi testar ifall ett värde som finns i kolumn D även finns i kolumn D. VI använder oss av formeln ANTAL.OM.
I kolumn Unika Personer i H har vi den formel som vi senare
skall ange i vår FILTER formel där vi skall ange vilka värden som skall
filtreras bort. Formeln är:
=ANTAL.OM(Tbl_Vecka[Ansvarig];F2)=0
Formeln ovan räknar antal gånger ett värde i kolumn F finns
i kolumn D. Genom att sätta ”=0” i slutet så görs det till en boolean, ett
SANT/FALSKT värde. Notera att det enda namnet som finns i kolumn D är Hannes
och det blir ett FALSKT värde för Hannes i kolumn H. Dvs det är ”FALSKT” att
den skall inkluderas i vår FILTER-formeln.
VI skall nu skapa vår slutliga formel i kolumn J som vi
kallar Unika Personer Filtrerad. Där skapar vi en filterfunktion som
skall filtrera listan Unika Personer, den skall endast inkludera de som
inte finns i listan Ansvarig. De personer som skall inkluderas finns
finner vi i kolumn H. Vår formel i cell J2 under rubriken Unika Personer
Filtrerad blir:
=FILTER(F2#;ANTAL.OM(Tbl_Vecka[Ansvarig];F2#)=0)
I bilden nedan har vi vårat resultat i kolumn J Unika Personer Filtrerad. Notera att Hannes som finns i Kolumn D inte finns med i kolumn J.
I formeln så använder vi referensen ”F2#”. Anledningen till
hastagen (#) är att det är så man refererar till ett dynamiskt område.
Vår funktion är nu färdig men vi kan snygga till den lite. Vi kan komprimera den till en cell genom att byta ut delen ”F2#” mot formeln som den refererar till: UNIK(Tbl_Namn[Namn]). Den referensen förekommer två gånger och vi får:
Gammal: =FILTER(F2#;ANTAL.OM(Tbl_Vecka[Ansvarig];F2#)=0)
Ny: =FILTER(UNIK(Tbl_Namn[Namn]);ANTAL.OM(Tbl_Vecka[Ansvarig];UNIK(Tbl_Namn[Namn]))=0)
Vi lägger till en sorteringsfunktion så att namnen hamnar i bokstavsordning
genom att använda funktionen SORTERA. Vi behöver inte lägga till någon sorteringsordning
då standard är från A till Ö.
=SORTERA(FILTER(UNIK(Tbl_Namn[Namn]);ANTAL.OM(Tbl_Vecka[Ansvarig];UNIK(Tbl_Namn[Namn]))=0))
När vi använt samtliga värden i dropdown-listan så blir den
tom. Då får vi felmeddelandet ” #KALK!”. Det innebär att beräkningen inte gått
att genomföra. Eftersom det inte ser så snyggt ut så vi vill ersätta texten ” #KALK!”
mot ett tomt värde, dvs ””. Vi använder oss då av formeln OMFEL. Vi slutliga
formel blir i cell L2 :
=OMFEL(SORTERA(FILTER(UNIK(Tbl_Namn[Namn]);ANTAL.OM(Tbl_Vecka[Ansvarig];UNIK(Tbl_Namn[Namn]))=0));"")
Markera slutligen listan i kolumn D infoga en dropdownlist och använd ”=$J$2#” som källa som i bilden nedan.