Excel -filterfunktion - förklaras med exempel + video

Titta på video - Excel FILTER Funktionsexempel

Office 365 har några fantastiska funktioner - som XLOOKUP, SORT och FILTER.

När det gäller filtrering av data i Excel, i pre-Office 365-världen, var vi mest beroende av inbyggt Excel-filter eller max Avancerat filter eller komplexa SUMPRODUCT-formler. Om du var tvungen att filtrera en del av en dataset var det vanligtvis en komplex lösning (något jag har täckt här).

Men med den nya FILTER -funktionen är det nu väldigt enkelt att snabbt filtrera en del av datamängden utifrån ett villkor.

Och i denna handledning kommer jag att visa dig hur fantastisk den nya FILTER -funktionen är och några användbara saker du kan göra med detta.

Men innan jag går in på exemplen, låt oss snabbt lära oss syntaxen för FILTER -funktionen.

Om du vill få dessa nya funktioner i Excel kan du uppgradera till Office 365 (gå med i insiderprogrammet för att få tillgång till alla funktioner/formler)

Excel -filterfunktion - Syntax

Nedan är syntaxen för FILTER -funktionen:

= FILTER (matris, inkludera, [if_empty])

• array - det här är cellintervallet där du har data och du vill filtrera data från det
• omfatta - detta är villkoret som berättar för funktionen vilka poster som ska filtreras
• [if_empty] - detta är ett valfritt argument där du kan ange vad du ska returnera om inga resultat hittas av FILTER -funktionen. Som standard (när det inte anges) returnerar det #CALC! fel

Låt oss nu titta på några fantastiska filterfunktionsexempel och saker som den kan göra som tidigare var ganska komplexa i sin frånvaro.

Klicka här för att ladda ner exempelfilen och följa med

Exempel 1: Filtrera data baserat på ett kriterium (region)

Anta att du har en dataset som visas nedan och du vill filtrera alla poster endast för USA.

Nedan är FILTER -formeln som gör detta:

= FILTER ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "US")

Ovanstående formel använder datamängden som array och villkoret är $ B $ 2: $ B $ 11 = ”US”

Detta villkor skulle göra att FILTER -funktionen kontrollerar varje cell i kolumn B (en som har regionen) och endast de poster som matchar detta kriterium filtreras.

I det här exemplet har jag också originaldata och filtrerade data på samma blad, men du kan också ha dessa i separata blad eller till och med arbetsböcker.

Filterfunktionen returnerar ett resultat som är en dynamisk matris (vilket innebär att istället för att returnera ett värde returnerar det en matris som spills till andra celler).

För att detta ska fungera måste du ha ett område där resultatet skulle bli tomt. I någon av cellerna i detta område (E2: G5 i det här exemplet) har redan något i det, ger funktionen dig #SPILL -felet.

Eftersom det här är en dynamisk array kan du inte ändra en del av resultatet. Du kan antingen radera hela intervallet som har resultatet eller cell E2 (där formeln angavs). Båda dessa skulle radera hela den resulterande matrisen. Men du kan inte ändra någon enskild cell (eller ta bort den).

I formeln ovan har jag hårdkodat regionvärdet, men du kan också ha det i en cell och sedan referera till den cellen som har regionvärdet.

Till exempel, i exemplet nedan har jag regionvärdet i cell I2 och detta refereras sedan till i formeln:

= FILTER ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)

Detta gör formeln ännu mer användbar och nu kan du helt enkelt ändra regionvärdet i cell I2 och filtret ändras automatiskt.

Du kan också ha en rullgardinsmeny i cell I2 där du helt enkelt kan välja och det skulle omedelbart uppdatera den filtrerade data.

Exempel 2: Filtrera data baserat på ett kriterium (mer än eller mindre än)

Du kan också använda jämförande operatörer inom filterfunktionen och extrahera alla poster som är mer eller mindre än ett specifikt värde.

Anta till exempel att du har datauppsättningen enligt nedan och du vill filtrera alla poster där försäljningsvärdet är mer än 10000.

Nedanstående formel kan göra detta:

= FILTER ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000))

Arrayargumentet hänvisar till hela datamängden och villkoret är i detta fall ($ C $ 2: $ C $ 11> 10000).

Formeln kontrollerar varje post för värdet i kolumn C. Om värdet är mer än 10000 filtreras det, annars ignoreras det.

Om du vill få alla poster mindre än 10000 kan du använda följande formel:

= FILTER ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11 <10000))

Du kan också bli mer kreativ med FILTER -formeln. Om du till exempel vill filtrera de tre bästa posterna baserat på försäljningsvärdet kan du använda följande formel:

= FILTER ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = STOR (C2: C11,3)))

Ovanstående formel använder funktionen STOR för att få det tredje största värdet i datamängden. Detta värde används sedan i FILTER-funktionskriterierna för att få alla poster där försäljningsvärdet är mer än eller lika med det tredje största värdet.

Klicka här för att ladda ner exempelfilen och följa med

Exempel 3: Filtrera data med flera kriterier (OCH)

Anta att du har nedanstående dataset och du vill filtrera alla poster för USA där försäljningsvärdet är mer än 10000.

Detta är ett OCH -villkor där du måste kontrollera två saker - regionen behöver till USA och försäljningen måste vara mer än 10000. Om bara ett villkor är uppfyllt bör resultaten inte filtreras.

Nedan visas FILTER -formeln som filtrerar poster med USA som region och försäljning av mer än 10000:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")*($ C $ 2: $ C $ 11> 10000))

Observera att kriteriet (kallat inkluderingsargumentet) är ($ B $ 2: $ B $ 11 = ”US”)*($ C $ 2: $ C $ 11> 10000)

Eftersom jag använder två villkor och jag behöver båda för att vara sanna, har jag använt multiplikationsoperatören för att kombinera dessa två kriterier. Detta returnerar en array med 0: or och 1: or, där en 1 returneras endast när båda villkoren är uppfyllda.

Om det inte finns några poster som uppfyller kriterierna skulle funktionen returnera #CALC! fel.

Och om du vill returnera något som betyder (istället för felet) kan du använda en formel enligt nedan:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "USA")*($ C $ 2: $ C $ 11> 10000), "Inget hittat")

Här har jag använt “Not Found” som det tredje argumentet, som används när inga poster hittas som matchar kriterierna.

Exempel 4: Filtrera data med flera kriterier (OR)

Du kan också ändra "inkludera" -argumentet i FILTER -funktionen för att leta efter ett ELLER -kriterium (där något av de givna villkoren kan vara sant).

Anta till exempel att du har datauppsättningen enligt nedan och du vill filtrera posterna där landet antingen är USA eller Kanada.

Nedan är formeln som gör detta:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+($ B $ 2: $ B $ 11 = "Kanada"))

Observera att i ovanstående formel har jag helt enkelt lagt till de två villkoren med hjälp av tilläggsoperatorn. Eftersom var och en av dessa villkor returnerar en matris med SANT och FALS kan jag lägga till för att få en kombinerad matris där det är SANT om något av villkoren är uppfyllda.

Ett annat exempel kan vara när du vill filtrera alla poster där antingen landet är USA eller försäljningsvärdet är mer än 10000.

Nedanstående formel kommer att göra detta:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+(C2: C11> 10000))

Obs! När du använder OCH -kriterier i en FILTER -funktion, använd multiplikationsoperatorn (*) och använd OR -kriterierna (+).

Exempel 5: Filtrera data för att komma över/under genomsnittliga poster

Du kan använda formler inom FILTER -funktionen för att filtrera och extrahera poster där värdet är över eller under genomsnittet.

Anta till exempel att du har datauppsättningen enligt nedan och du vill filtrera alla poster där försäljningsvärdet är över genomsnittet.

Du kan göra det med följande formel:

= FILTER ($ A $ 2: $ C $ 11, C2: C11> MEDEL (C2: C11))

På samma sätt kan du använda nedanstående formel för under genomsnittet:

= FILTER ($ A $ 2: $ C $ 11, C2: C11<>

Klicka här för att ladda ner Exempelfilen och följa med

Exempel 6: Endast filtrering av EVEN -nummerposter (eller ODD -nummerposter)

Om du snabbt behöver filtrera och extrahera alla poster från jämna talrader eller udda rader kan du göra det med FILTER -funktionen.

För att göra detta måste du kontrollera radnumret i FILTER -funktionen och endast filtrera radnummer som uppfyller radnumerskriterierna.

Anta att du har datauppsättningen enligt nedan och jag vill bara extrahera jämna nummer från den här datamängden.

Nedan är formeln som gör detta:

= FILTER ($ A $ 2: $ C $ 11, MOD (RAD (A2: A11) -1,2) = 0)

Ovanstående formel använder MOD -funktionen för att kontrollera radnumret för varje post (som ges av ROW -funktionen).

Formeln MOD (RAD (A2: A11) -1,2) = 0 returnerar SANT när radnumret är jämnt och FALSKT när det är udda. Observera att jag har subtraherat 1 från RAD (A2: A11) -delen eftersom den första posten är i den andra raden, och detta justerar radnumret för att betrakta den andra raden som den första posten.

På samma sätt kan du filtrera alla udda nummer med hjälp av formeln nedan:

= FILTER ($ A $ 2: $ C $ 11, MOD (RAD (A2: A11) -1,2) = 1)

Exempel 7: Sortera de filtrerade data med formel

Genom att använda FILTER -funktionen med andra funktioner kan vi få mycket mer gjort.

Om du till exempel filtrerar en dataset med hjälp av FILTER -funktionen kan du använda SORT -funktionen för att få resultatet som redan är sorterat.

Anta att du har en datauppsättning som visas nedan och du vill filtrera alla poster där försäljningsvärdet är mer än 10000. Du kan använda funktionen SORT med funktionen för att se till att de resulterande data sorteras baserat på försäljningsvärdet.

Nedanstående formel kommer att göra detta:

= SORT (FILTER ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000)), 3, -1)

Ovanstående funktion använder FILTER -funktionen för att få data där försäljningsvärdet i kolumn C är mer än 10000. Denna matris som returneras av FILTER -funktionen används sedan inom SORT -funktionen för att sortera dessa data baserat på försäljningsvärdet.

Det andra argumentet i SORT -funktionen är 3, vilket är att sortera baserat på den tredje kolumnen. Och det fjärde argumentet är -1 vilket är att sortera dessa data i fallande ordning.

Klicka här för att ladda ner exempelfilen

Så det här är 7 exempel för att använda FILTER -funktionen i Excel.

Hoppas du tyckte att denna handledning var användbar!

Du kanske också gillar följande Excel -självstudier:

1. Så här filtrerar du celler med fet teckensnittsformatering i Excel
2. Sökrutan för dynamiskt Excel -filter
3. Så här filtrerar du data i en pivottabell i Excel