Oglejte si video - Excelov napredni filter
Napredni filter Excel je ena izmed najbolj podcenjenih in premalo uporabljenih funkcij, na katere sem naletel.
Če delate z Excelom, sem prepričan, da ste ga uporabljali (ali vsaj slišali za običajni filter Excel). Hitro filtrira nabor podatkov na podlagi izbire, določenega besedila, številke ali drugih takih meril.
V tem priročniku vam bom pokazal nekaj kul stvari, ki jih lahko naredite z naprednim filtrom Excel.
Toda najprej … Kaj je napredni filter Excel?
Napredni filter Excel - kot že ime pove - je napredna različica običajnega filtra. To lahko uporabite, če morate za filtriranje nabora podatkov uporabiti bolj zapletena merila.
Tu je nekaj razlik med običajnim filtrom in naprednim filtrom:
- Medtem ko bo običajni podatkovni filter filtriral obstoječi nabor podatkov, lahko z naprednim filtrom Excel odstranite nabor podatkov tudi na drugo lokacijo.
- Napredni filter Excel vam omogoča uporabo zapletenih meril. Če imate na primer podatke o prodaji, lahko podatke filtrirate po merilu, pri katerem je prodajni predstavnik Bob, regija pa severna ali južna (v primerih bomo videli, kako to storiti). Pisarniška podpora ima za to dobro razlago.
- Napredni filter Excel lahko uporabite za ekstrahiranje edinstvenih zapisov iz vaših podatkov (več o tem v sekundi).
EXCEL ADVANCED FILTER (primeri)
Zdaj pa poglejmo nekaj primerov uporabe naprednega filtra v Excelu.
Primer 1 - Izvlečenje edinstvenega seznama
Napredni filter Excel lahko uporabite za hitro izvlečenje edinstvenih zapisov iz nabora podatkov (ali z drugimi besedami, odstranite podvojene).
V Excelu 2007 in novejših različicah obstaja možnost odstranjevanja dvojnikov iz nabora podatkov. Toda to spremeni vaš obstoječi nabor podatkov. Če želite ohraniti izvirne podatke nedotaknjene, morate ustvariti kopijo podatkov in nato uporabiti možnost Odstrani podvojene podatke. Napredni filter Excel vam omogoča izbiro lokacije, da dobite edinstven seznam.Poglejmo, kako z naprednimi filtri pridobiti edinstven seznam.
Recimo, da imate nabor podatkov, kot je prikazano spodaj:
Kot lahko vidite, so v tem naboru podatkov podvojeni zapisi (označeni oranžno). To je lahko posledica napake pri vnosu podatkov ali posledica sestavljanja podatkov.
V tem primeru lahko z orodjem Excel Advanced Filter hitro pridobite seznam vseh edinstvenih zapisov na drugem mestu (tako da vaši izvirni podatki ostanejo nedotaknjeni).
Tu so koraki za pridobitev vseh edinstvenih zapisov:
- Izberite celoten nabor podatkov (vključno z glavami).
- Pojdite na zavihek Podatki -> Razvrsti in filtriraj -> Napredno. (Uporabite lahko tudi bližnjico na tipkovnici - Alt + A + Q). S tem se odpre pogovorno okno Napredni filter.
- V pogovornem oknu Napredni filter uporabite naslednje podrobnosti:
- Ukrep: Izberite možnost »Kopiraj na drugo mesto«. To vam bo omogočilo, da določite lokacijo, kjer lahko dobite seznam edinstvenih zapisov.
- Obseg seznama: Poskrbite, da se nanaša na nabor podatkov, iz katerega želite najti edinstvene zapise. Prepričajte se tudi, da so vključene glave v naboru podatkov.
- Razpon meril: Pustite to prazno.
- Kopiraj v: Določite naslov celice, kamor želite dobiti seznam edinstvenih zapisov.
- Kopiraj samo edinstvene zapise: Preverite to možnost.
- Kliknite V redu.
To vam bo takoj dalo seznam vseh edinstvenih zapisov.
Pozor: Ko uporabljate Napredni filter za pridobitev edinstvenega seznama, se prepričajte, da ste izbrali tudi glavo. Če tega ne storite, bi prva celica štela za glavo.
Primer 2 - Uporaba meril v naprednem filtru Excel
Pridobivanje edinstvenih zapisov je ena od mnogih stvari, ki jih lahko naredite z naprednim filtrom Excel.
Njegova glavna korist je v sposobnosti, da omogoča uporabo zapletenih meril za filtriranje podatkov.
Tukaj mislim na kompleksna merila. Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite hitro pridobiti vse zapise, kjer je prodaja večja od 5000, regija pa ZDA.
Spodaj je opisano, kako lahko uporabite napredni filter Excel za filtriranje zapisov na podlagi navedenih meril:
- Prvi korak pri uporabi naprednega filtra Excel s kompleksnimi merili je določiti merila. Če želite to narediti, kopirajte glave in jih prilepite nekam na delovni list.
- Določite merila, po katerih želite filtrirati podatke. V tem primeru, ker želimo dobiti vse zapise za ZDA s prodajo več kot 5000, vnesite "ZDA" v celico pod regijo in> 5000 v celico pod prodajo. To bi zdaj uporabili kot vhod v naprednem filtru za pridobivanje filtriranih podatkov (kot je prikazano v naslednjih korakih).
- Izberite celoten nabor podatkov (vključno z glavami).
- Pojdite na zavihek Podatki -> Razvrsti in filtriraj -> Napredno. S tem se odpre pogovorno okno Napredni filter.
- V pogovornem oknu Napredni filter uporabite naslednje podrobnosti:
- Ukrep: Izberite možnost »Kopiraj na drugo mesto«. To vam bo omogočilo, da določite lokacijo, kjer lahko dobite seznam edinstvenih zapisov.
- Obseg seznama: Poskrbite, da se nanaša na nabor podatkov, iz katerega želite najti edinstvene zapise. Prepričajte se tudi, da so vključene glave v naboru podatkov.
- Razpon meril: Določite merila, ki smo jih zgradili v zgornjih korakih. V tem primeru bi bilo F1: I3.
- Kopiraj v: Določite naslov celice, kamor želite dobiti seznam edinstvenih zapisov.
- Kopiraj samo edinstvene zapise: Preverite to možnost.
- Kliknite V redu.
To bi vam takoj dalo vse zapise, kjer je regija ZDA in prodaja več kot 5000.
Zgornji primer je primer, ko je filtriranje izvedeno na podlagi dveh meril (ZDA in prodaja večja od 5000).
Napredni filter Excel vam omogoča ustvarjanje različnih kombinacij meril.
Tukaj je nekaj primerov, kako lahko sestavite te filtre.
Uporaba meril AND
Če želite uporabiti merila AND, ga morate podati pod glavo.
Na primer:
- Za filtriranje zapisov, ko je regija ZDA, prodajalec pa Joe.
- Za filtriranje zapisov, ko je regija ZDA IN je vrednost prodaje večja od 5000.
- Ko je regija ZDA IN se prodaja zabeleži po 31. 3. 2017.
Z uporabo meril OR
Če želite uporabiti merila ALI, morate v istem stolpcu podati merila.
Na primer:
- Za filtriranje zapisov, ko je regija ZDA ALI je regija Azija.
- Za filtriranje zapisov, ko je prodajalec Bob ALI Martha.
Primer 3 - Uporaba znakov WILDCARD v naprednem filtru v Excelu
Napredni filter Excel omogoča tudi uporabo nadomestnih znakov pri oblikovanju meril.
V Excelu so trije nadomestni znaki:
- * (zvezdica) - Predstavlja poljubno število znakov. Na primer, ex* lahko pomeni excel, excels, na primer strokovnjak itd.
- ? (vprašaj) - Predstavlja en sam znak. Tr? Mp lahko na primer pomeni Trump ali Tramp.
- ~ (tilda) - Uporablja se za identifikacijo nadomestnega znaka (~, *,?) V besedilu.
Zdaj pa poglejmo, kako lahko te nadomestne znake uporabimo za napredno filtriranje v Excelu.
- Za filtriranje zapisov, kjer se ime prodajnega predstavnika začne od J.
Upoštevajte, da * predstavlja poljubno število znakov. Tako bi bil vsak predstavnik z imenom, ki se začne z J, filtriran po teh merilih.
Podobno lahko uporabite tudi druga dva nadomestna znaka.
Opomba: Če uporabljate Office 365, preverite funkcijo FILTER. Z enostavno formulo lahko naredi veliko stvari, ki jih napredni filter lahko naredi.
OPOMBA:
- Ne pozabite, da morajo biti glave v merilih popolnoma enake kot v nizu podatkov.
- Naprednega filtriranja ni mogoče razveljaviti, če ga kopirate na druga mesta.
