Avancerat filter
Funktionen Autofilter får betraktas som ett enkel verktyg men begränsas av att den inte tillåter utbyggnad av antalet villkor. Vi kan mha Autofilter lösa många uppgifter men den räcker inte alltid till.
Här presenteras den mer avancerade funktionen, Avancerat filter. Den erbjuder större handlingsutrymme men ställer samtidigt högre krav på förståelse för hur funktionen fungerar.
En begränsning med detta verktyg är att det maximalt kan visa 1.000 st poster vid urval.
I samband med användandet av funktionen är det också önskvärt att göra beräkningar för hela urvalet - se Tabellberäkningar och Tabellfunktioner (s k databasfunktioner).
Funktionen återfinns under Data | Filter | Avancerat filter.
Introduktion
En förutsättning är att vi bereder plats för ett villkorsområde i bladet. Det placeras företrädesvis ovanför listan eller under den.
Villkorsområdet ska ha samma rubriker som listan har. Enklast är att kopiera rubrikerna från listan. (XL skapar namnet "Villkor" vid den första urvalsprocessen.)
XL gör ingen skillnad om villkoren anges med versaler eller gemener.
Följande bild visar utgångspunkten för exemplet:
Villkoren som anges består av två kategorier:
- Jämförelsevillkor, här används jämförelseoperatorerna såsom >,
<, >=, <=, =, <>.
Exempel: >100, <>100, ="J?" ="*e" eller ="=Nordväst" - Beräknade villkor, om man t ex vill visa alla poster som är större än medelvärdet.
I första exemplet ska vi göra ett urval utifrån följande villkor:
- Månad Januari och
- Antal artiklar större än 50 st och
- I distrikten som finns inom "Nord" och
- Med ett bruttobelopp som är mindre eller lika med 100.000 kr.
De poster som visas efter denna selektion uppfyller alla villkoren.
Villkoren och resultatet av urvalet visas i följande bild:
Det som ej framgår i bilden ovan är textvillkoren: ="Jan"
och ="Nord*".
Noterbart är att villkorsområdet här endast omfattar cellområdet: A1:H2.
Ett tips är att alltid definiera om villkorsområdet vid varje ny urvalsprocess.
I nästa exempel föreligger följande villkor:
- Månad Januari och
- Ordernummer som börjar på "A" och
- Där bruttobeloppet är större eller lika med 50.000 kr eller
- Månad Februari och
- Ordernummer som börjar på "B" och
- Där bruttobeloppet är mindre eller lika 200.000 kr.
Poster som visas efter denna selektion uppfyller antingen de 3 första
villkoren eller de 3 sista villkoren.
Villkoren och resultatet av urvalet visar i följande bild:
Det som ej framgår av bilden ovan är textvillkoren: ="A*" och ="B*"
Noterbart är att villkorsområdet här endast omfattar cellområdet: A1:H3.
Ett tips är att alltid definiera om villkorsområdet vid varje ny urvalsprocess.
I detta exempel belyses hur vi använder oss av ett beräknat villkor.
Villkoren är följande:
- Månad är Februari och
- Antal artiklar är större än eller lika med 150 och
- Bruttobeloppet överstiger medelvärdet för Januari månad.
Villkoren och resultatet av urvalet visar i följande bild:
OBS! Listan är här sorterad efter Datum och i stigande ordning.
Beräknade villkor kan hanteras på olika sätt när det gäller referenser till den aktuella kolumnen. Här tas etiketten bort i villkorsområdet för att möjliggöra selektionen.
Det beräknade villkorets formel är: =F11>MEDEL($F$11:$F$17)
Formeln måste alltid utvärderas till SANT eller FALSKT och cellreferensen ska vara den första i aktuell kolumn. De poster som visas efter urvalsprocessen har följaktligen värdet SANT i själva urvalsprocessen. Medelvärdet beräknas utifrån de poster som återfinns i januari månad.
Noterbart är att villkorsområdet här endast omfattar cellområdet: A1:H2.
Ett tips är att alltid definiera om villkorsområdet vid varje ny urvalsprocess.
Här exemplifieras hur vi även kan använda oss av en mer avancerad
underliggande formel vid beräknat villkor:
- Orderdatumet är större eller lika med 2001-01-15 och
- Ordernummer är alla som inte börjar på "C" och
- Rabatten är större än medelvärdet för hela listan.
OBS! Listan är här sorterad efter Datum och i stigande ordning.
Villkoret för Ordernr är: ="<>C*"
Formeln för det beräknade villkoret är:
-
=G11>(SUMMA($G$11:$G$22)/ANTAL.OM($G$11:$G$22;">0"))
Följaktligen beräknar den medelvärdet för de poster som ej är tomma.
Noterbart är att villkorsområdet här endast omfattar cellområdet: A1:H2.
I detta exempel ska vi titta närmare på hur urval
sker när villkoret utgörs av ett intervall, t ex Bruttobelopp mellan två
värden.
- Bruttobeloppet ska vara större eller lika med 50.000 kr men inte större än eller lika med 100.000 kr
Bilden nedan visar villkoren och resultatet:
För att skapa intervallvillkoret krävs att vi infogar ytterligare en kolumn i villkorsområdet. I exemplet har kolumnen "Region" ersatts med en ytterligare kolumn "Bruttobelopp".
Noterbart är att villkorsområdet här endast omfattar cellområdet: A1:H2.
Ett tips är att alltid definiera om villkorsområdet vid varje ny urvalsprocess.
Viktig att komma ihåg om villkor
När vi använder oss av text i villkorsområdet måste vi vara medvetna om att XL alltid tolkar texten med ändelsen *, dvs till villkoret lägger XL till sökriteriet "*".
För att styra XL till att endast filtrera de poster som exakt uppfyller villkoret måste villkoret anges inom citationstecken ="=villkor".
Exempel:
- Artikelnummer: AR12 - Tolkar XL som att visa alla artikelnummer som börjar på AR12.
- Artikelnummer: ="=AR12" - Tolkar XL som att visa exakt detta artikelnummer då villkoret är =AR12
Beräknade villkor
Här demonstreras tekniker för att filtrera en lista utifrån s k beräknade villkor.
Följande krav måste uppfyllas:
- Formeln som vi anger måste ge det logiska värdet SANT eller FALSKT när listan filtreras.
- Rubrik för villkor måste skiljas sig från tabellrubrikerna.
- När vi refererar till en hel kolumn i listan måste absoluta cellreferenser användas.
- När vi refererar till cellen i den första raden i den berörda kolumnen måste en relativ cellreferens användas.
- Används referenser till celler utanför listan måste absoluta cellreferenser anges.
I första exemplet ska vi filtrera ut de poster vars värden är större än
medelvärdet för listan.
Villkoret i cell A2:
- =B5>MEDEL($B$5:$B$10)
Resultatet vid filtreringen blir följaktligen
I nästa exempel ska vi filtrera fram de poster som faller inom en 30-dagars period från dagens datum bakåt i tiden.
Villkoret i cell D2 är:
- =E5>IDAG()-30
Utfallet blir följaktligen:
Även villkor som är kopplade till celler utanför listan eller t o m till andra
listor kan användas, t ex =E5<=MEDEL($L$5:$L$100).