Žiūrėti vaizdo įrašą - „Excel“ išplėstinis filtras
„Excel“ išplėstinis filtras yra viena iš nepakankamai įvertintų ir nepakankamai naudojamų funkcijų, su kuriomis susidūriau.
Jei dirbate su „Excel“, esu tikras, kad naudojote (arba bent jau girdėjote apie įprastą „Excel“ filtrą). Jis greitai filtruoja duomenų rinkinį pagal pasirinkimą, nurodytą tekstą, skaičių ar kitus tokius kriterijus.
Šiame vadove parodysiu keletą įdomių dalykų, kuriuos galite padaryti naudodami išplėstinį „Excel“ filtrą.
Bet pirmiausia … Kas yra „Excel“ išplėstinis filtras?
Išplėstinis „Excel“ filtras - kaip rodo pavadinimas - yra išplėstinė įprasto filtro versija. Tai galite naudoti, kai duomenų rinkiniui filtruoti reikia naudoti sudėtingesnius kriterijus.
Štai keletas skirtumų tarp įprasto filtro ir išplėstinio filtro:
- Nors įprastas duomenų filtras filtruos esamą duomenų rinkinį, galite naudoti išplėstinį „Excel“ filtrą, kad duomenų rinkinį išgautumėte ir į kitą vietą.
- Išplėstinis „Excel“ filtras leidžia naudoti sudėtingus kriterijus. Pvz., Jei turite pardavimo duomenų, galite filtruoti duomenis pagal kriterijų, kai pardavimo atstovas yra Bobas, o regionas yra šiaurėje arba pietuose (kaip tai padaryti, pamatysime pavyzdžiuose). „Office“ palaikymas turi gerą paaiškinimą šiuo klausimu.
- Galite naudoti „Excel“ išplėstinį filtrą, kad iš savo duomenų išgautumėte unikalius įrašus (daugiau apie tai per sekundę).
Išplėstinis EXCEL filtras (pavyzdžiai)
Dabar pažvelkime į kai kuriuos išplėstinio filtro naudojimo „Excel“ pavyzdžius.
1 pavyzdys. Unikalaus sąrašo ištraukimas
Galite naudoti „Excel“ išplėstinį filtrą, norėdami greitai išgauti unikalius įrašus iš duomenų rinkinio (arba, kitaip tariant, pašalinti dublikatus).
„Excel 2007“ ir naujesnėse versijose yra galimybė pašalinti duomenų rinkinio dublikatus. Bet tai keičia esamą duomenų rinkinį. Norėdami išsaugoti originalius duomenis, turite sukurti duomenų kopiją ir naudoti parinktį Pašalinti dublikatus. „Excel“ išplėstinis filtras leis jums pasirinkti vietą, kad gautumėte unikalų sąrašą.Pažiūrėkime, kaip naudoti išplėstinius filtrus, kad gautumėte unikalų sąrašą.
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:
Kaip matote, šiame duomenų rinkinyje yra pasikartojančių įrašų (paryškinta oranžine spalva). Tai gali būti dėl klaidos įvedant duomenis arba dėl duomenų rinkimo.
Tokiu atveju galite naudoti „Excel“ išplėstinio filtravimo įrankį, kad greitai gautumėte visų unikalių įrašų, esančių kitoje vietoje, sąrašą (kad jūsų pradiniai duomenys liktų nepažeisti).
Norėdami gauti visus unikalius įrašus, atlikite šiuos veiksmus:
- Pasirinkite visą duomenų rinkinį (įskaitant antraštes).
- Eikite į skirtuką Duomenys -> Rūšiuoti ir filtruoti -> Išplėstiniai. (Taip pat galite naudoti sparčiuosius klavišus - Alt + A + Q). Bus atidarytas dialogo langas Išplėstinis filtras.
- Dialogo lange Išplėstinis filtras naudokite šią informaciją:
- Veiksmas: Pasirinkite parinktį „Kopijuoti į kitą vietą“. Tai leis jums nurodyti vietą, kur galite gauti unikalių įrašų sąrašą.
- Sąrašo diapazonas: Įsitikinkite, kad jis nurodo duomenų rinkinį, iš kurio norite rasti unikalių įrašų. Taip pat įsitikinkite, kad įtrauktos duomenų rinkinio antraštės.
- Kriterijų diapazonas: Palikite tai tuščią.
- Nukopijuoti: Nurodykite langelio adresą, kuriame norite gauti unikalių įrašų sąrašą.
- Kopijuoti tik unikalius įrašus: Patikrinkite šią parinktį.
- Spustelėkite Gerai.
Tai iš karto suteiks jums visų unikalių įrašų sąrašą.
Atsargiai: Kai naudojate išplėstinį filtrą, kad gautumėte unikalų sąrašą, įsitikinkite, kad taip pat pasirinkote antraštę. Jei to nepadarysite, pirmoji ląstelė bus laikoma antrašte.
2 pavyzdys. Kriterijų naudojimas „Excel“ išplėstiniame filtre
Unikalių įrašų gavimas yra vienas iš daugelio dalykų, kuriuos galite padaryti naudodami išplėstinį „Excel“ filtrą.
Pagrindinis jo naudingumas yra galimybė leisti naudoti sudėtingus duomenų filtravimo kriterijus.
Štai ką turiu omenyje pagal sudėtingus kriterijus. Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite greitai gauti visus įrašus, kurių pardavimas yra didesnis nei 5000, o regionas yra JAV.
Štai kaip galite naudoti „Excel“ išplėstinį filtrą, kad filtruotumėte įrašus pagal nurodytus kriterijus:
- Pirmasis žingsnis naudojant „Excel“ išplėstinį filtrą su sudėtingais kriterijais yra nurodyti kriterijus. Norėdami tai padaryti, nukopijuokite antraštes ir įklijuokite jas kažkur darbalapyje.
- Nurodykite kriterijus, pagal kuriuos norite filtruoti duomenis. Šiame pavyzdyje, kadangi norime gauti visus JAV, kurių pardavimai yra didesni nei 5000, įrašus, įveskite „US“ langelyje po regionu ir> 5000 langelyje po pardavimo. Dabar jis bus naudojamas kaip įvestis išplėstiniame filtre, kad būtų gauti filtruoti duomenys (kaip parodyta kituose veiksmuose).
- Pasirinkite visą duomenų rinkinį (įskaitant antraštes).
- Eikite į skirtuką Duomenys -> Rūšiuoti ir filtruoti -> Išplėstiniai. Bus atidarytas dialogo langas Išplėstinis filtras.
- Dialogo lange Išplėstinis filtras naudokite šią informaciją:
- Veiksmas: Pasirinkite parinktį „Kopijuoti į kitą vietą“. Tai leis jums nurodyti vietą, kur galite gauti unikalių įrašų sąrašą.
- Sąrašo diapazonas: Įsitikinkite, kad jis nurodo duomenų rinkinį, iš kurio norite rasti unikalių įrašų. Taip pat įsitikinkite, kad įtrauktos duomenų rinkinio antraštės.
- Kriterijų diapazonas: Nurodykite kriterijus, kuriuos sukūrėme atlikdami aukščiau nurodytus veiksmus. Šiame pavyzdyje tai būtų F1: I3.
- Nukopijuoti: Nurodykite langelio adresą, kuriame norite gauti unikalių įrašų sąrašą.
- Kopijuoti tik unikalius įrašus: Patikrinkite šią parinktį.
- Spustelėkite Gerai.
Tai akimirksniu suteiktų jums visus įrašus, kuriuose regionas yra JAV, o pardavimai viršija 5000.
Anksčiau pateiktas pavyzdys yra atvejis, kai filtravimas atliekamas pagal du kriterijus (JAV ir pardavimas didesnis nei 5000).
Išplėstinis „Excel“ filtras leidžia sukurti daugybę skirtingų kriterijų derinių.
Štai keletas pavyzdžių, kaip galite sukurti šiuos filtrus.
IR kriterijų naudojimas
Jei norite naudoti IR kriterijus, turite jį nurodyti po antrašte.
Pavyzdžiui:
- Norėdami filtruoti įrašus, kai regionas yra JAV, o pardavimo atstovas yra Džo.
- Norėdami filtruoti įrašus, kai regionas yra JAV IR pardavimo vertė yra didesnė nei 5000.
- Kai regionas yra JAV IR pardavimai registruojami po 2017-03-31.
Naudojant ARBA kriterijus
Jei norite naudoti ARBA kriterijus, tame pačiame stulpelyje turite nurodyti kriterijus.
Pavyzdžiui:
- Norėdami filtruoti įrašus, kai regionas yra JAV ARBA regionas yra Azija.
- Norėdami filtruoti įrašus, kai pardavimo atstovas yra Bobas arba Marta.
3 pavyzdys - WILDCARD simbolių naudojimas išplėstiniame „Excel“ filtre
„Excel“ išplėstinis filtras taip pat leidžia naudoti pakaitos simbolius kuriant kriterijus.
„Excel“ yra trys pakaitos simboliai:
- * (žvaigždutė) - Tai reiškia bet kokį simbolių skaičių. Pavyzdžiui, ex* gali reikšti „excel“, „excels“, „example“, „expert“ ir kt.
- ? (Klaustukas) - Tai vienas simbolis. Pavyzdžiui, Tr? Mp gali reikšti Trumpą ar Trampą.
- ~ (tildė) - Jis naudojamas tekste identifikuoti pakaitos simbolį (~, *,?).
Dabar pažiūrėkime, kaip galime naudoti šiuos pakaitos simbolius tam tikram išplėstiniam „Excel“ filtravimui.
- Norėdami filtruoti įrašus, kur pardavimo atstovo pavadinimas prasideda nuo J.
Atminkite, kad * reiškia bet kokį simbolių skaičių. Taigi bet koks atstovas, kurio pavadinimas prasideda J, būtų filtruojamas pagal šiuos kriterijus.
Panašiai galite naudoti ir kitus du pakaitos simbolius.
Pastaba: Jei naudojate „Office 365“, turėtumėte patikrinti FILTRO funkciją. Tai gali padaryti daug dalykų, kuriuos gali padaryti išplėstinis filtras, naudojant paprastą formulę.
PASTABA:
- Atminkite, kad kriterijų antraštės turi būti visiškai tokios pačios kaip ir duomenų rinkinyje.
- Išplėstinio filtravimo negalima anuliuoti, kai jis nukopijuojamas į kitas vietas.
