Excel -suodatintoiminto - selitetty esimerkeillä + video

Katso video - Excel FILTER -toimintoesimerkkejä

Office 365 sisältää mahtavia toimintoja, kuten XLOOKUP, SORT ja FILTER.

Mitä tulee tietojen suodattamiseen Excelissä, Office 365: tä edeltävässä maailmassa olimme enimmäkseen riippuvaisia ​​Excelin sisäänrakennetusta suodattimesta tai korkeimmasta lisäsuodattimesta tai monimutkaisista SUMPRODUCT-kaavoista. Jos joudut suodattamaan osan tietojoukosta, se oli yleensä monimutkainen kiertotapa (mitä olen käsitellyt täällä).

Mutta uuden SUODATIN -toiminnon avulla on nyt todella helppoa suodattaa osa tietojoukosta nopeasti ehdon perusteella.

Ja tässä opetusohjelmassa näytän sinulle, kuinka mahtava on uusi SUODATIN -toiminto ja joitain hyödyllisiä asioita, joita voit tehdä tällä.

Mutta ennen kuin ryhdyn esimerkkeihin, opimme nopeasti SUODATIN -funktion syntaksista.

Jos haluat saada nämä uudet ominaisuudet Excelissä, voit päivitä Office 365: een (liittymällä sisäpiiriohjelmaan saat käyttöösi kaikki ominaisuudet/kaavat)

Excel -suodatintoiminto - syntaksi

Alla on FILTER -funktion syntaksi:

= FILTER (array, include, [if_empty])

• matriisi - tämä on solualue, jossa sinulla on tiedot ja haluat suodattaa niistä joitakin tietoja
• sisältää - tämä on ehto, joka kertoo funktiolle, mitä tietueita suodatetaan
• [if_empty] - Tämä on valinnainen argumentti, jossa voit määrittää, mitä palautetaan, jos FILTER -toiminto ei löydä tuloksia. Oletuksena (jos sitä ei ole määritetty), se palauttaa #CALC! virhe

Katsotaanpa nyt joitain hämmästyttäviä suodatintoimintoesimerkkejä ja asioita, joita se voi tehdä, mikä ennen oli melko monimutkaista sen puuttuessa.

Lataa esimerkkitiedosto napsauttamalla tätä ja jatka sitten

Esimerkki 1: Tietojen suodatus yhden kriteerin (alueen) perusteella

Oletetaan, että sinulla on alla näkyvä tietojoukko ja haluat suodattaa kaikki tietueet vain Yhdysvalloissa.

Alla on FILTER -kaava, joka tekee tämän:

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

Yllä oleva kaava käyttää tietojoukkoa taulukkona ja ehto on $ B $ 2: $ B $ 11 = ”US”

Tämä ehto saisi FILTER -funktion tarkistamaan kaikki sarakkeen B solut (alue, jolla on alue) ja vain ne kriteerit vastaavat tietueet suodatettaisiin.

Lisäksi tässä esimerkissä minulla on alkuperäiset tiedot ja suodatetut tiedot samalle arkille, mutta voit myös saada ne erillisille arkkeille tai jopa työkirjoille.

Suodatintoiminto palauttaa tuloksen, joka on dynaaminen taulukko (mikä tarkoittaa, että yhden arvon palauttamisen sijaan se palauttaa taulukon, joka roiskuu muihin soluihin).

Jotta tämä toimisi, sinulla on oltava alue, jolla tulos tulee tyhjäksi. Joissakin tämän alueen soluissa (tässä esimerkissä E2: G5) on jo jotain, toiminto antaa sinulle #SPILL -virheen.

Lisäksi koska tämä on dynaaminen taulukko, et voi muuttaa osaa tuloksesta. Voit joko poistaa koko alueen, jolla on tulos, tai solun E2 (johon kaava syötettiin). Molemmat poistavat koko tuloksena olevan taulukon. Mutta et voi muuttaa yksittäistä solua (tai poistaa sitä).

Yllä olevassa kaavassa olen koodannut aluearvon kovasti, mutta voit myös saada sen soluun ja viitata sitten soluun, jolla on alueen arvo.

Esimerkiksi alla olevassa esimerkissä minulla on alueen arvo solussa I2 ja tähän viitataan sitten kaavassa:

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

Tämä tekee kaavasta entistä hyödyllisemmän, ja nyt voit yksinkertaisesti muuttaa alueen arvoa solussa I2 ja suodatin muuttuu automaattisesti.

Voit myös valita avattavan valikon solussa I2, jossa voit tehdä valinnan ja päivittää suodatetut tiedot välittömästi.

Esimerkki 2: Tietojen suodatus yhden kriteerin perusteella (enemmän tai vähemmän kuin)

Voit myös käyttää vertailukelpoisia operaattoreita suodatintoiminnossa ja poimia kaikki tietueet, jotka ovat enemmän tai vähemmän kuin tietty arvo.

Oletetaan esimerkiksi, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat suodattaa kaikki tietueet, joiden myyntiarvo on yli 10000.

Alla oleva kaava voi tehdä tämän:

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

Taulukko -argumentti viittaa koko tietojoukkoon ja ehto on tässä tapauksessa ($ C $ 2: $ C $ 11> 10000).

Kaava tarkistaa jokaisen tietueen sarakkeen C arvon. Jos arvo on yli 10000, se suodatetaan, muuten se ohitetaan.

Jos haluat saada kaikki tietueet alle 10000, voit käyttää alla olevaa kaavaa:

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

Voit myös olla luovempi FILTER -kaavan avulla. Jos haluat esimerkiksi suodattaa kolmen parhaan tietueen myyntiarvon perusteella, voit käyttää alla olevaa kaavaa:

= SUODATIN ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = LARGE (C2: C11,3)))

Yllä oleva kaava käyttää LARGE -funktiota saadakseen tietojoukon kolmanneksi suurimman arvon. Tätä arvoa käytetään sitten suodatintoimintoehdoissa, jotta saadaan kaikki tietueet, joissa myyntiarvo on suurempi tai yhtä suuri kuin kolmanneksi suurin arvo.

Lataa esimerkkitiedosto napsauttamalla tätä ja jatka sitten

Esimerkki 3: Tietojen suodatus useilla kriteereillä (AND)

Oletetaan, että sinulla on alla oleva tietojoukko ja haluat suodattaa kaikki Yhdysvaltojen tietueet, joiden myyntiarvo on yli 10000.

Tämä on AND -ehto, jossa sinun on tarkistettava kaksi asiaa - alue tarvitsee Yhdysvaltojen ja myynnin on oltava yli 10000. Jos vain yksi ehto täyttyy, tuloksia ei pitäisi suodattaa.

Alla on SUODATIN -kaava, joka suodattaa tietueet, joiden alue ja USA ovat yli 10000:

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

Huomaa, että kriteeri (jota kutsutaan sisällytysargumentiksi) on ($ B $ 2: $ B $ 11 = ”US”)*($ C $ 2: $ C $ 11> 10000)

Koska käytän kahta ehtoa ja molempien on oltava totta, olen yhdistänyt nämä kaksi ehtoa kertooperaattorilla. Tämä palauttaa 0: n ja 1: n taulukon, jossa 1 palautetaan vasta, kun molemmat ehdot täyttyvät.

Jos ehtoja täyttäviä tietueita ei ole, funktio palauttaa #CALC! virhe.

Ja jos haluat palauttaa jotain merkitystä (virheen sijasta), voit käyttää alla olevaa kaavaa:

= SUODATIN ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "USA"))*($ C $ 2: $ C $ 11> 10000), "mitään ei löytynyt")

Tässä olen käyttänyt "Ei löydy" kolmanneksi argumenttina, jota käytetään, kun ehtoja vastaavia tietueita ei löydy.

Esimerkki 4: Tietojen suodatus useilla kriteereillä (OR)

Voit myös muokata FILTER -funktion sisällytä -argumenttia TAI -ehtojen tarkistamiseksi (jos jokin annetuista ehdoista voi olla totta).

Oletetaan esimerkiksi, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat suodattaa tietueet, joissa maa on joko Yhdysvallat tai Kanada.

Alla on kaava, jolla tämä tehdään:

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

Huomaa, että yllä olevassa kaavassa olen yksinkertaisesti lisännyt kaksi ehtoa käyttämällä lisäysoperaattoria. Koska jokainen näistä ehdoista palauttaa TOSI- ja EPÄTOSI -matriisin, voin lisätä yhdistetyn taulukon, jossa se on TOSI, jos jokin ehdoista täyttyy.

Toinen esimerkki voi olla, kun haluat suodattaa kaikki tietueet, joissa joko maa on Yhdysvallat tai myyntiarvo on yli 10000.

Alla oleva kaava tekee tämän:

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

Huomautus: Kun käytät AND -ehtoja FILTER -funktiossa, käytä kertooperaattoria (*) ja kun käytät OR -ehtoja, käytä lisäysoperaattoria (+).

Esimerkki 5: Tietojen suodattaminen keskimääräisten tietueiden ylä- tai alapuolelle

Voit suodattaa ja poimia tietueita, joissa arvo on keskiarvon ylä- tai alapuolella, suodattamalla ja poimimalla suodattimen funktion kaavoja.

Oletetaan esimerkiksi, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat suodattaa kaikki tietueet, joissa myyntiarvo on keskimääräistä korkeampi.

Voit tehdä sen käyttämällä seuraavaa kaavaa:

= SUODATIN ($ A $ 2: $ C $ 11, C2: C11> KESKIARVO (C2: C11))

Samoin keskiarvon alapuolella voit käyttää alla olevaa kaavaa:

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

Lataa esimerkkitiedosto napsauttamalla tätä ja jatka sitten

Esimerkki 6: Vain EVEN -numerotietueiden (tai ODD -tietueiden) suodatus

Jos haluat nopeasti suodattaa ja poimia kaikki tietueet parillisilta tai parittomilta riveiltä, ​​voit tehdä sen FILTER -toiminnolla.

Tätä varten sinun on tarkistettava FILTER -toiminnon rivinumero ja suodatettava vain rivinumerot, jotka täyttävät rivinumeron ehdot.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluan vain poimia parilliset tietueet tästä tietojoukosta.

Alla on kaava, jolla tämä tehdään:

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

Yllä oleva kaava tarkistaa MOD -toiminnon avulla jokaisen tietueen rivinumeron (jonka antaa ROW -funktio).

Kaava MOD (RIVI (A2: A11) -1,2) = 0 palauttaa TOSI, kun rivin numero on parillinen ja EPÄTOSI, jos se on pariton. Huomaa, että olen vähentänyt 1 RIVI -osasta (A2: A11), kun ensimmäinen tietue on toisella rivillä, ja tämä säätää rivinumeron siten, että toinen rivi katsotaan ensimmäiseksi tietueeksi.

Voit myös suodattaa kaikki parittomat tietueet alla olevan kaavan avulla:

= SUODATIN ($ A $ 2: $ C $ 11, MOD (RIVI (A2: A11) -1,2) = 1)

Esimerkki 7: Lajittele suodatetut tiedot kaavalla

FILTER -toiminnon käyttäminen muiden toimintojen kanssa antaa meille mahdollisuuden tehdä paljon enemmän.

Jos esimerkiksi suodatat tietojoukon FILTER -funktiolla, voit käyttää sen kanssa SORT -funktiota saadaksesi jo lajitelun tuloksen.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat suodattaa kaikki tietueet, joiden myyntiarvo on yli 10000. Voit käyttää toiminnon SORT -funktiota varmistaaksesi, että tuloksena olevat tiedot lajitellaan myyntiarvon perusteella.

Alla oleva kaava tekee tämän:

= LAJITTELE (SUODATIN ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000)), 3, -1)

Yllä oleva toiminto käyttää SUODATIN -funktiota saadakseen tiedot, joissa sarakkeen C myyntiarvo on yli 10000. Tätä SUODATIN -funktion palauttamaa matriisia käytetään sitten SORT -funktiossa tietojen lajitteluun myyntiarvon perusteella.

Lajittelutoiminnon toinen argumentti on 3, joka on lajiteltava kolmannen sarakkeen perusteella. Ja neljäs argumentti on -1, joka on lajitella nämä tiedot laskevaan järjestykseen.

Lataa esimerkkitiedosto napsauttamalla tätä

Joten tässä on 7 esimerkkiä FILTER -toiminnon käyttämisestä Excelissä.

Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä!

Saatat pitää myös seuraavista Excel -opetusohjelmista:

1. Solujen suodattaminen lihavoidulla fontin muotoilulla Excelissä
2. Dynaaminen Excel -suodattimen hakukenttä
3. Tietojen suodattaminen pivot -taulukossa Excelissä