Excel Advanced Filter - Täydellinen opas esimerkkeineen

Katso video - Excel Advanced Filter

Excel Advanced Filter on yksi aliarvostetuimmista ja alikäytetyimmistä ominaisuuksista, joihin olen törmännyt.

Jos työskentelet Excelin kanssa, olen varma, että olet käyttänyt (tai ainakin kuullut tavallisesta Excel -suodattimesta). Se suodattaa nopeasti tietojoukon valinnan, määritetyn tekstin, numeron tai muiden vastaavien kriteerien perusteella.

Tässä oppaassa näytän sinulle hienoja juttuja, joita voit tehdä käyttämällä Excel -lisäsuodatinta.

Mutta ensin … Mikä on Excel Advanced Filter?

Excel Advanced Filter - kuten nimestä voi päätellä - on tavallisen suodattimen edistynyt versio. Voit käyttää tätä, kun sinun on käytettävä monimutkaisempia ehtoja tietojoukon suodattamiseen.

Tässä on joitain eroja tavallisen suodattimen ja lisäsuodattimen välillä:

• Vaikka tavallinen datasuodatin suodattaa olemassa olevan tietojoukon, voit käyttää Excelin lisäsuodatinta poimiaksesi tietojoukon myös johonkin muuhun paikkaan.
• Excel Advanced Filterin avulla voit käyttää monimutkaisia ​​ehtoja. Jos sinulla on esimerkiksi myyntitietoja, voit suodattaa tietoja kriteerin perusteella, jossa myyntiedustaja on Bob ja alue on joko pohjoinen tai etelä (näemme, miten tämä tehdään esimerkeissä). Office -tuella on tähän hyvä selitys.
• Voit käyttää Excelin lisäsuodatinta poimiaksesi ainutlaatuisia tietueita tiedoistasi (lisää tästä sekunnissa).

EXCEL LISÄSUODATIN (Esimerkkejä)

Katsotaanpa nyt esimerkkiä Excel -lisäsuodattimen käytöstä.

Esimerkki 1 - Ainutlaatuisen luettelon purkaminen

Voit käyttää Excelin lisäsuodatinta poimiaksesi ainutlaatuisia tietueita nopeasti tietojoukosta (tai toisin sanoen poistamaan kaksoiskappaleet).

Excel 2007: ssä ja uudemmissa versioissa on mahdollisuus poistaa kaksoiskappaleet tietojoukosta. Mutta se muuttaa olemassa olevaa tietojoukkoasi. Jos haluat säilyttää alkuperäiset tiedot ennallaan, sinun on luotava kopio tiedoista ja käytettävä sitten Poista kaksoiskappaleet -vaihtoehtoa. Excel Advanced -suodattimen avulla voit valita sijainnin saadaksesi ainutlaatuisen luettelon.

Katsotaanpa, kuinka edistyneiden suodattimien avulla saadaan ainutlaatuinen luettelo.

Oletetaan, että sinulla on alla näkyvä tietojoukko:

Kuten näette, tässä tietojoukossa on päällekkäisiä tietueita (korostettu oranssilla). Nämä voivat johtua virheestä tietojen syöttämisessä tai tietojen kokoamisesta.

Tällaisessa tapauksessa voit käyttää Excel Advanced Filter -työkalua saadaksesi nopeasti luettelon kaikista ainutlaatuisista tietueista eri paikassa (jotta alkuperäiset tiedot pysyvät ennallaan).

Tässä on vaiheet kaikkien ainutlaatuisten tietueiden saamiseksi:

• Valitse koko tietojoukko (mukaan lukien otsikot).
• Siirry Tiedot -välilehti -> Lajittele ja suodata -> Lisäasetukset. (Voit käyttää myös pikanäppäintä - Alt + A + Q). Tämä avaa Lisäsuodatin -valintaikkunan.
• Käytä Lisäsuodatin -valintaikkunassa seuraavia tietoja:
  • Toiminta: Valitse vaihtoehto Kopioi toiseen paikkaan. Tämän avulla voit määrittää sijainnin, josta voit saada luettelon ainutlaatuisista tietueista.
  • Lista -alue: Varmista, että se viittaa tietojoukkoon, josta haluat löytää ainutlaatuisia tietueita. Varmista myös, että tietojoukon otsikot ovat mukana.
  • Kriteerialue: Jätä tämä tyhjäksi.
  • Kopioi: Määritä soluosoite, josta haluat saada luettelon ainutlaatuisista tietueista.
  • Kopioi vain ainutlaatuiset tietueet: Valitse tämä vaihtoehto.
• Napsauta OK.

Tämä antaa sinulle heti luettelon kaikista ainutlaatuisista tietueista.

Varoitus: Kun käytät Advanced Filter -toimintoa saadaksesi ainutlaatuisen luettelon, varmista, että olet myös valinnut otsikon. Jos et, se pitää ensimmäistä solua otsikkona.

Esimerkki 2 - Ehtojen käyttäminen Excelin lisäsuodattimessa

Ainutlaatuisten tietueiden hankkiminen on yksi monista asioista, joita voit tehdä Excelin lisäsuodattimella.

Sen ensisijainen hyödyllisyys on sen kyky sallia monimutkaisten kriteerien käyttäminen tietojen suodattamiseen.

Tässä tarkoitan monimutkaisia ​​kriteerejä. Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat saada nopeasti kaikki tietueet, joiden myynti on yli 5000 ja alue on Yhdysvallat.

Näin voit suodattaa tietueet määritettyjen ehtojen perusteella Excel Advanced Filter -suodattimen avulla:

• Ensimmäinen vaihe, kun käytät Excel Advanced Filter -ohjelmaa monimutkaisilla ehdoilla, on ehtojen määrittäminen. Kopioi otsikot ja liitä se jonnekin laskentataulukkoon.
• Määritä kriteerit, joiden mukaan haluat suodattaa tiedot. Tässä esimerkissä, koska haluamme saada kaikki tietueet Yhdysvalloista, joiden myynti on yli 5000, kirjoita "US" alueen alla olevaan soluun ja> 5000 Myynnin alapuolella olevaan soluun. Tätä käytetään nyt tulona Advanced Filterissä suodatetun datan saamiseksi (kuten seuraavissa vaiheissa on esitetty).
• Valitse koko tietojoukko (otsikot mukaan lukien).
• Siirry Tiedot -välilehti -> Lajittele ja suodata -> Lisäasetukset. Tämä avaa Lisäsuodatin -valintaikkunan.
• Käytä Lisäsuodatin -valintaikkunassa seuraavia tietoja:
  • Toiminta: Valitse vaihtoehto Kopioi toiseen paikkaan. Tämän avulla voit määrittää sijainnin, josta voit saada luettelon ainutlaatuisista tietueista.
  • Lista -alue: Varmista, että se viittaa tietojoukkoon, josta haluat löytää ainutlaatuisia tietueita. Varmista myös, että tietojoukon otsikot ovat mukana.
  • Kriteerialue: Määritä kriteerit, jotka rakensimme yllä olevissa vaiheissa. Tässä esimerkissä se olisi F1: I3.
  • Kopioi: Määritä soluosoite, josta haluat saada luettelon ainutlaatuisista tietueista.
  • Kopioi vain ainutlaatuiset tietueet: Valitse tämä vaihtoehto.
• Napsauta OK.

Tämä antaisi sinulle välittömästi kaikki tietueet, joissa alue on Yhdysvallat ja myynti on yli 5000.

Yllä oleva esimerkki on tapaus, jossa suodatus tehdään kahden kriteerin perusteella (Yhdysvallat ja myynti yli 5000).

Excel Advanced -suodattimen avulla voit luoda monia erilaisia ​​kriteerien yhdistelmiä.

Seuraavassa on muutamia esimerkkejä siitä, miten voit rakentaa nämä suodattimet.

AND -kriteerien käyttäminen

Kun haluat käyttää AND -ehtoja, sinun on määritettävä se otsikon alle.

Esimerkiksi:

• Suodata tietueet, kun alue on Yhdysvallat JA myyntiedustaja on Joe.
• Tietueiden suodattaminen, kun alue on Yhdysvallat JA myyntiarvo on yli 5000.
• Kun alue on Yhdysvallat JA myynti kirjataan 31.3.2017 jälkeen.

TAI -kriteerien käyttäminen

Kun haluat käyttää TAI -ehtoja, sinun on määritettävä ehdot samassa sarakkeessa.

Esimerkiksi:

• Jos haluat suodattaa tietueet, kun alue on Yhdysvallat TAI alue on Aasia.
• Tietueiden suodattaminen, kun myyntiedustaja on Bob TAI Martha.

Tähän mennessä sinun on täytynyt ymmärtää, että kun meillä on kriteerit samalla rivillä, se on JA kriteerit, ja kun meillä on se eri riveillä, se on TAI kriteeri.

Esimerkki 3 - WILDCARD -merkkien käyttäminen Excelin lisäsuodattimessa

Excel Advanced Filter mahdollistaa myös yleismerkkien käytön ehtoja laadittaessa.

Excelissä on kolme jokerimerkkiä:

1. * (tähti) - Se edustaa mitä tahansa määrää merkkejä. Esimerkiksi ex* voi tarkoittaa Excel, Excel, esimerkki, asiantuntija jne.
2. ? (kysymysmerkki) - Se edustaa yhtä hahmoa. Esimerkiksi Tr? Mp voi tarkoittaa Trumpia tai Trampia.
3. ~ (tilde) - Sitä käytetään tunnistamaan tekstissä yleismerkki (~, *,?).

Katsotaanpa nyt, kuinka voimme käyttää näitä jokerimerkkejä Excelin edistyneeseen suodatukseen.

• Suodata tietueet, joissa myyntiedustajan nimi alkaa J.

Huomaa, että * edustaa mitä tahansa merkkiä. Joten kaikki edustajat, joiden nimi alkaa J: llä, suodatetaan näiden kriteerien mukaan.

Voit myös käyttää kahta muuta yleismerkkiä.

Huomautus: Jos käytät Office 365: tä, sinun kannattaa tarkistaa SUODATIN -toiminto. Se voi tehdä monia asioita, joita edistynyt suodatin voi tehdä yksinkertaisella kaavalla.

MERKINTÄ:

1. Muista, että ehtojen otsikoiden tulee olla täsmälleen samat kuin tietojoukossa.
2. Lisäsuodatusta ei voi kumota, kun se kopioidaan muihin paikkoihin.