Filtriranje podatkov v Excelu – enostavno in celovito

Ukaz Excel Filter lahko pomaga filtrirati podatke v obsegu ali tabeli, da prikaže samo podatke, ki jih potrebujete, ostalo pa skrije. Vgrajene operatorje lahko uporabite za enostavno filtriranje številk, besedil ali datumov, na primer filtriranje vseh številk, ki je večje ali enako določenemu številu, filtriranje besedila se začne, konča ali vsebuje določen znak ali besedo ali pa se prikažejo samo vrstice, kjer rok je pred določenim datumom ali po njem itd. Ko podatke spremenite, lahko podatke filtrirate v obsegu ali tabeli, lahko znova uporabite filter, da dobite nove podatke, ali pa počistite filter za prikaz vseh podatkov.

V tej vadnici bomo prikazali, kako dodajati, uporabljati ali odstranjevati filtre v Excelu. Poleg tega vas bomo vodili, kako izboljšati funkcijo filtra za reševanje bolj zapletenih težav v Excelu.

---

1. Kako dodati filter v Excelu

Če želite filtrirati podatke v obsegu ali tabeli, morate najprej dodati filter svojim podatkom. Ta razdelek vsebuje tri načine za dodajanje filtra v Excelu.

1.1 Ukaz za filtriranje na zavihku Podatki

Izberite poljubne celice v obsegu ali tabeli, ki jih želite dodati, kliknite datum > Filter.

1.2 Ukaz za filtriranje na zavihku Domov

Izberite poljubne celice v obsegu ali tabeli, ki jih želite dodati, kliknite Domov > Razvrsti in filtriraj > Filter.

1.3 Dodajte filter z bližnjico

Izberite poljubne celice v obsegu ali tabeli, ki jih želite dodati, in pritisnite Ctrl + Shift + L ključi.

Po uporabi ene od zgornjih operacij lahko vidite, da se v glave stolpcev izbranih celic dodajo spustne puščice.

---

2. Kako uporabiti filter v Excelu (eno ali več meril)

Po dodajanju filtra ga morate uporabiti ročno. Ta razdelek vam bo pokazal, kako uporabiti filter v enem ali več stolpcih v Excelu.

2.1 Uporabi filter za en stolpec (en kriterij)

Če želite filter uporabiti samo za en stolpec, na primer podatke filtra v stolpcu C, kot je prikazano spodaj. Pojdite v ta stolpec in nato naredite naslednje.

1. 1) Kliknite puščico spustnega menija v glavi stolpca.
2. 2) Določite pogoj filtra, kot ga potrebujete.
3. 3) Kliknite OK za začetek filtriranja. Oglejte si posnetek zaslona:
   

Zdaj je filter uporabljen za stolpec C. Vsi podatki, ki ustrezajo kriterijem filtra, bodo prikazani, ostali pa bodo skriti.

Po uporabi filtra lahko vidite, da se puščica navzdol spremeni v ikono filtra .

Zelo previdno je, da ko miškin kazalec premaknete nad ikono filtra, bodo kriteriji filtra, ki ste jih določili, prikazani kot namig zaslona, ​​kot je prikazano spodaj. Če torej pozabite na merila, ki ste jih določili za filter, samo premaknite kazalec miške nad ikono filtra.

2.2 Uporabi filter z več merili za več stolpcev (več meril)

2.2.1 Uporabi filter z več merili za več stolpcev enega za drugim

Če želite filter uporabiti za več stolpcev z več merili, zgoraj ponovite zgornjo metodo za več stolpcev enega za drugim.

Po uporabi filtra za več stolpcev lahko vidite, da so puščice spustnega seznama v filtriranih stolpcih usmerjene v ikone filtra.

2.2.2 Hkrati uporabite filter z več merili za več stolpcev

Z zgornjo metodo morate filter uporabiti stolpce enega za drugim, najpomembnejše pa je, da ta metoda podpira samo IN merila. Tukaj predstavite metode, s katerimi ne boste le uporabili filtra za več stolpcev hkrati, temveč boste uporabili tudi oba IN in OR meril.

Recimo, da imate podatkovno tabelo, kot je prikazano na spodnji sliki zaslona, ​​in želite filtrirati podatke iz več stolpcev na podlagi več meril: Izdelek = AAA-1 in naročilo> 80, or Skupna cena> 10000. Poskusite z enim od naslednjih načinov.

2.2.2.1 Uporabi filter za več stolpcev s funkcijo Napredni filter

Funkcija naprednega filtra vam lahko pomaga rešiti to težavo, po korakih naredite naslednje.

1. Najprej ustvarite merila na delovnem listu, kot je prikazano spodaj.

Opomba: Za merila AND postavite vrednosti meril v različne celice iste vrstice. V drugo vrstico postavite vrednost merila OR.

2. klik datum > Napredno vklopite Napredni filter Funkcija.

3. V Ljubljani Napredni filter pogovorno okno, nastavite na naslednji način.

Zdaj se stolpci istočasno filtrirajo na podlagi danih meril, kot je prikazano na spodnji sliki zaslona.

2.2.2.2 Z izjemnim orodjem enostavno nanesite filter na več stolpcev

Kot IN in OR meril za filtriranje ni enostavno obvladati v zgornji metodi, zato toplo priporočam Super filter značilnost Kutools za Excel. S to funkcijo lahko v Excelu enostavno uporabite filter za več stolpcev z meriloma AND in OR.

1. Po namestitvi Kutools za Excel, Kliknite Kutools Plus > Super filter.

Potem Super filter podokno je prikazano na desni strani delovnega lista.

Privzeto sta dodani dve prazni skupini meril z razmerjem ALI med njima v Super filter podokno. In razmerje med merili v isti skupini je IN. Odnos med različnimi skupinami lahko spremenite glede na vaše potrebe.

2. V Ljubljani Super filter podokno, prosimo, nastavite kriterije filtra, kot sledi.

Zdaj so v izvirnem obsegu podatkov prikazani samo usklajeni podatki, ostali pa skriti. Oglejte si posnetek zaslona:

Nasvet: S to priročno funkcijo lahko dodate več meril v skupino, dodate več skupin, shranite trenutne nastavitve filtra kot scenarij za prihodnjo uporabo itd. Je nepogrešljivo orodje, ki lahko prihrani kopico delovnega časa in poveča delovno učinkovitost.

Kliknite, če želite izvedeti več o tej funkciji.

---

3. Kako uporabljati filter v Excelu

V tem razdelku boste izvedeli, kako lahko z ukazom za filtriranje filtrirate različne vrste podatkov, kot so besedilo, številke, datumi in formati.

3.1 Filtriranje besedilnih vrednosti

3.1.1 Filtriranje besedilnih celic z določenimi merili (začnite z, končajte z, vsebujte in tako naprej)

Pravzaprav vgrajeni operater filtra - Besedilni filtri vsebuje veliko uporabnih meril za enostavno filtriranje besedila. Recimo, da želite filtrirati celice, ki se začnejo z določenim znakom, kot je J, za to naredite naslednje.

1. V glavo stolpca izvirnega obsega podatkov dodajte filter. Kliknite, če želite izvedeti, kako.

2. Kliknite puščico navzdol v celici glave, da odprete meni filtra.

3. klik Besedilni filtri > Začne se z.

4. V Ljubljani Samodejni filter po meri v pogovorno okno vnesite določen znak (tukaj vtipkam J) v besedilno polje in kliknite OK.

Nasvet: Lahko dodate drugega in or Or razmerja, kot jih potrebujete.

Zdaj se vse celice, ki se začnejo z znakom J, prikažejo v stolpcu D, kot je prikazano spodaj.

3.1.2 Filter z razlikovanjem med velikimi in malimi črkami

Zdi se, da je z vgrajenim operaterjem filtrov enostavno filtrirati besedilne celice na podlagi določenih meril. Ker pa funkcija Filter ne podpira filtriranja besedila z občutljivimi črkami, kako lahko v Excelu naredimo filter, ki razlikuje med velikimi in malimi črkami? V tem razdelku so prikazane metode za dosego tega cilja.

3.1.2.1 Filtrirajte določeno besedilo z razliko med velikimi in malimi črkami po formuli in ukazu Filter

Recimo, da želite filtrirati vse velike črke določenega besedila, na primer »ORODJA ZA BESEDILA« v stolpcu B, storite naslednje.

1. Poleg prvotnega obsega podatkov ustvarite pomožni stolpec (tukaj za stolpec pomožnika izberem stolpec D). V drugo celico vnesite spodnjo formulo in pritisnite tipko Enter. Izberite celico z rezultatom in jo povlecite Ročaj za samodejno izpolnjevanje navzdol, da dobite druge rezultate.

= TOČNO (B2, ZGORNJE (B2))

Opomba: Ta formula pomaga prepoznati velike in male črke. Če celica vsebuje vse velike črke, bo rezultat PRAV, v nasprotnem primeru boste rezultat dobili kot LAŽNO.

2. Izberite stolpca B in D (izbran bo stolpec C, vseeno), kliknite Data> filter da jim dodate filtre.

3. Pojdite v stolpec B (stolpec vsebuje besedila, ki jih boste filtrirali) in nato nastavite na naslednji način.

Zdaj so v stolpcu B prikazana samo velika in mala „besedilna orodja“.

4. Kliknite puščico spustnega menija v stolpcu D, počistite polje Izberi vse potrdite, potrdite polje poleg TRUE in nato kliknite OK gumb.

Nato se vsa velika besedila "besedilnih orodij" v stolpcu B filtrirajo, kot je prikazano na spodnji sliki zaslona.

3.1.2.2 Z neverjetnim orodjem enostavno filtrirajte celice z občutljivimi na črke

Če želite v stolpcu filtrirati samo veliko ali malo besedilo, tukaj priporočite Poseben filter značilnost Kutools za Excel. S to funkcijo bo vse veliko ali malo besedilo enostavno filtrirano z le nekaj kliki.

1. Izberite obseg stolpcev, v katerega boste filtrirali besedila, in kliknite Kutools Plus > Poseben filter > Poseben filter.

2. V Ljubljani Poseben filter pogovorno okno, naredite spodnje nastavitve.

3. Nato a Kutools za Excel Pojavi se pogovorno okno, ki vam pove, koliko celic je bilo najdenih in bodo filtrirane, kliknite OK gumb.

Zdaj se vse velike ali male celice takoj filtrirajo, kot je prikazano spodaj.

Kliknite, če želite izvedeti več o tej funkciji.

3.1.3 Filtriraj po dolžini besedila

Če želite na primer filtrirati celice po dolžini besedila, da filtrirate celice z dolžino besedila 10 znakov, kaj morate storiti? Spodaj navedeni trije načini vam lahko naredijo uslugo.

3.1.3.1 Z ukazom Filtriraj celice po dolžini besedila

Pravzaprav ima ukaz Filter vgrajen operater za rešitev te težave, naredite naslednje.

1. Izberite obseg celic, ki jih je treba filtrirati (tukaj izberem B1: B27), in nato v ta obseg stolpcev dodajte filter s klikom datum > Filter.

2. Kliknite puščico spustnega menija v glavi stolpca in nato kliknite Besedilni filtri > Filter po meri. Oglejte si posnetek zaslona:

3. V Ljubljani Samodejni filter po meri v pogovorno okno izberite merila kot enaka, v besedilno polje vnesite 10 vprašanj (?) kot način vzorca in kliknite OK gumb.

nasveti: Teh 10 vprašajev pomeni, da se bo ujemala z besedilnim nizom, da je dolžina 10.

Zdaj se vse celice z dolžino besedilnega niza 10 (vključno s presledki) takoj filtrirajo.

3.1.3.2 Filtrirajte celice po dolžini besedila s formulo in ukazom Filter

Poleg tega lahko s funkcijo LEN izračunate dolžino besedilnega niza vsake celice in nato z ukazom Filter filtrirate potrebne celice dolžine besedila na podlagi izračunanega rezultata.

1. Ustvarite pomožni stolpec v bližini prvotnega obsega podatkov. Vanjo vnesite spodnjo formulo in pritisnite tipko Enter. Izberite celico z rezultatom in jo povlecite Ročaj za samodejno izpolnjevanje navzdol, da dobite druge rezultate.

= LEN (B2)

Zdaj dobite dolžino besedila vsake celice v določenem stolpcu.

2. Izberite stolpec za pomoč (vključite glavo), kliknite datum > filter da mu dodate filter.

3. Kliknite spustno puščico, počistite polje Izberi vse , če želite počistiti vse elemente, nato potrdite le polje poleg številke 10 in na koncu kliknite OK gumb.

Zdaj se vse celice z dolžino besedilnega niza 10 (vključno s presledki) takoj filtrirajo.

3.1.3.3 Z neverjetnim orodjem lahko preprosto filtrirate celice po dolžini besedila

Tukaj priporočite Poseben filter uporabnost Kutools za Excel za lažje filtriranje celic po dolžini besedila v Excelu.

1. Izberite obseg stolpcev, v katerem boste filtrirali celice, glede na določeno dolžino besedila, kliknite Kutools Plus > Poseben filter > Poseben filter. Oglejte si posnetek zaslona:

2. V Ljubljani Poseben filter pogovorno okno, nastavite na naslednji način.

3. Kutools za Excel Pojavi se pogovorno okno, ki vam pove, koliko celic je bilo najdenih in bodo filtrirane, kliknite OK nadaljevati.

Nato se vse celice z dolžino besedilnega niza 10 filtrirajo, kot je prikazano na spodnji sliki zaslona.

3.2 Številke filtrov

V Excelu je tudi zelo enostavno filtriranje številk z ukazom Številčni filtri.

Recimo, da želite v stolpcu filtrirati celice s številkami med 15000 in 20000 (na primer stolpec C, kot je prikazano spodaj), lahko to storite na naslednji način.

1. Izberite obseg stolpcev, ki vsebuje številke, ki jih boste filtrirali, kliknite datum > filter da dodate filter.

2. Po dodajanju filtra nastavite na naslednji način.

Nasvet: Ker želim filtrirati celice s številkami med 15000 in 20000, tukaj v polja z besedilom vnesem 15000 in 20000 ločeno.

Zdaj so celice s številkami med 15000 in 20000 filtrirane, kot je prikazano na spodnji sliki zaslona.

3.3 Filtriranje datumov

Privzeto vgrajena funkcija Date Filters ponuja veliko pogostih meril za filtriranje datumov. Kot lahko vidite, ni vgrajene možnosti filtriranja datumov po dnevih v tednu, vikendih ali delovnih dneh. Ta razdelek vas bo naučil, kako doseči te operacije.

3.3.1 Filtriranje datumov po dnevih v tednu ali vikendih

Če imate podatkovno tabelo, kot je prikazano na spodnji sliki zaslona, ​​če želite filtrirati datume po dnevih v tednu ali vikendih, uporabite enega od spodnjih načinov.

3.3.1.1 Filtrirajte datume po dnevih v tednu ali vikendih s formulo in ukazom Filter

V tem razdelku boste uporabili funkcijo WEEKDAY za izračun dneva v tednu vsakega datuma in nato uporabili filter za filtriranje določenega dne v tednu ali vikendih, kot ga potrebujete.

1. V prazno celico (v tem primeru D2) vanj vnesite spodnjo formulo in pritisnite tipko Vnesite tipko. Izberite celico z rezultati in nato povlecite Ročaj za samodejno izpolnjevanje nad spodnjimi celicami uporabiti to formulo.

= TEDEN (A2)

Nasvet:

Opomba: Kot lahko vidite, formula vrne številke iz 1 do 7, ki označuje dan v tednu od Nedelja do Sobota (1 je za nedeljo, 7 za soboto).

2. Izberite celotne rezultate formule (vključite celico glave), kliknite datum > Filter.

3. Kliknite puščico navzdol in nato počistite polje Izberi vse potrditveno polje.

Potem se filtrirajo vsi vikendi ali celice določenega dneva v tednu. Oglejte si posnetek zaslona:

3.3.1.2 Z izjemnim orodjem lahko preprosto filtrirate datume po dnevih v tednu ali vikendih

Če vam zgornja metoda ni primerna, tukaj priporočite Poseben filter uporabnost Kutools za Excel. S to funkcijo lahko preprosto filtrirate celice, ki vsebujejo kateri koli dan v tednu, le z več kliki.

1. Izberite celice z datumi, ki jih želite filtrirati glede na določen dan v tednu.

2. klik Kutools Plus > Poseben filter > Poseben filter.

3. V Ljubljani Poseben filter pogovorno okno, nastavite na naslednji način.

4. Nato a Kutools za Excel Pojavi se pogovorno okno s številom najdenih celic, ki bodo filtrirane, kliknite OK nadaljevati.

Zdaj so celice za vikende ali kateri koli dan v tednu filtrirane.

3.3.2 Celice filtrirajte po delovnih dneh z neverjetnim orodjem

Poleg filtrirnih celic po dnevih v tednu ali vikendih, Poseben filter uporabnost Kutools za Excel lahko pomaga tudi pri filtriranju celic po delovnih dneh.

1. Uporabite enake korake kot zgoraj da omogočite pripomoček Special Filter.

2. V Ljubljani Poseben filter pogovorno okno, naredite spodnje nastavitve.

3. Nato a Kutools za Excel odpre se pogovorno okno. Kliknite OK nadaljevati.

Zdaj so vse celice delovnega dne filtrirane.

3.4 Formati filtrov

Običajno Excel podpira filtriranje podatkov na podlagi vizualnih meril, kot so barva pisave, barva celic ali nabori ikon, kot je prikazano spodaj.

Če želite podatke filtrirati na podlagi drugih vizualnih meril, kot so slog pisave (krepko, ležeče), učinki pisave (prečrtano) ali posebna celica (ki vsebuje formule), Excel ne pomaga doseči. V tem razdelku so opisane metode za lažje reševanje teh težav.

3.4.1 Filtriranje po krepkem / ležečem besedilu

Če želite podatke filtrirati s krepkim ali ležečim oblikovanjem besedila, kot je prikazano na spodnji sliki zaslona, ​​vam lahko z naslednjimi metodami naredimo uslugo. Naredite naslednje.

3.4.1.1 Filtrirajte besedilo v obliki krepko / ležeče s formulo in ukazom Filter

Kombinacija formule Get.Cell in ukaza Filter lahko pomaga filtrirati krepko oblikovano besedilo v obsegu stolpcev.

1. klik Formule > Določite ime.

2. V Ljubljani Novo ime pogovorno okno, morate:

Sintaksa formule:

=GET.CELL(type_num, reference)

Argumenti formule

3. V isti vrstici B2 izberite prazno celico, vanjo vnesite spodnjo formulo in pritisnite tipko Vnesite tipko. Izberite celico z rezultatom in jo povlecite Ročaj za samodejno izpolnjevanje nad spodnjimi celicami uporabiti to formulo.

= Filter_Bold_Cells

4. Izberite celotne celice z rezultati (vključite glavo), kliknite datum > Filter.

5. Kliknite puščico navzdol, potrdite le polje zraven TRUE in nato kliknite OK.

Nato se filtrirajo vse krepke ali ležeče besedilne celice. Oglejte si posnetek zaslona:

3.4.1.2 Filtrirajte krepko ali ležeče oblikovano besedilo z ukazoma Najdi in zamenjaj ter Filtriraj

Za doseganje lahko uporabite tudi kombinacijo ukazov Najdi in Zamenjaj ter Filtriraj.

1. Izberite obseg stolpcev, ki vsebuje krepke ali ležeče besedilne celice, ki jih boste filtrirali, in pritisnite Ctrl + F ključi.

2. V Ljubljani Poišči in zamenjaj pogovorno okno, morate konfigurirati na naslednji način.

3. Zdaj so v izvirnem obsegu izbrane vse krepke ali ležeče besedilne celice, kliknite Domov > Barva polnila, in nato izberite barvo polnila za izbrane celice.

4. Ponovno izberite celoten obseg stolpcev, kliknite datum > filter da mu dodate filter.

5. Kliknite puščico spustnega menija in izberite Barvain nato kliknite barvo polnila, ki ste jo določili ravno pod Filtriraj po barvi celic. Oglejte si posnetek zaslona:

Nato se filtrirajo vse krepke ali ležeče besedilne celice.

3.4.1.3 Z neverjetnim orodjem enostavno filtrirajte krepko ali ležeče oblikovano besedilo

Kot smo že omenili, Poseben filter uporabnost Kutools za Excel lahko pomaga pri enostavnem filtriranju z razlikovanjem med velikimi in malimi črkami, filtriranjem po dolžini besedila in datumih filtriranja. Tukaj bomo naučili, kako uporabiti to funkcijo za filtriranje krepkih ali ležečih besedilnih celic v Excelu.

1. Izberite obseg stolpcev (vključi glavo), ki vsebuje krepko ali ležeče oblikovane celice, ki jih boste filtrirali.

2. klik Kutools Plus > Poseben filter > Filtriraj krepko / Filtriraj ležeče. Oglejte si posnetek zaslona:

3. klik OK v pojavnem oknu Kutools za Excel pogovorno okno za nadaljevanje (to pogovorno okno se prikaže, da vam pove, koliko celic ustreza merilom).

Zdaj so vse besedilne celice v formatu krepko ali ležeče oblikovane.

3.4.2 Filtriraj po prečrtanem oblikovanem besedilu

Če ste prejeli seznam z ljudmi, ki jih režejo tako, da jim dodate prečrtano črto, in da morate s filtriranjem ugotoviti vse prečrtane celice, vam lahko spodnje metode naredijo uslugo.

3.4.2.1 Prečrtano oblikovano besedilo filtrirajte z uporabniško določeno funkcijo in ukazom Filter

Uporabniško določeno funkcijo lahko uporabite za prepoznavanje prečrtanih oblikovanih besedilnih celic in nato z ukazom Filter filtrirate vse prečrtane celice na podlagi rezultatov.

1. Pritisnite druga + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Modul. In nato kopirajte spodnjo kodo VBA v Koda okno.

Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

3. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

4. Izberite prazno celico (ta celica naj bo v isti vrstici celice, ki jo želite izračunati), vnesite spodnjo formulo in pritisnite Vnesite tipko. Izberite celico z rezultatom in jo povlecite Ročaj za samodejno izpolnjevanje nad spodnjo celico, da uporabite to formulo.

= HasStrike (B2)

Opomba: Če ima ustrezna celica učinek prečrtane pisave, se vrne PRAV, v nasprotnem primeru se vrne LAŽNO.

5. Izberite celotne celice z rezultati (vključite celico z glavo), kliknite datum > Filter.

6. Nato kliknite puščico spustnega menija> potrdite le polje poleg možnosti TRUE> kliknite OK. Oglejte si prikazan posnetek zaslona.

Zdaj lahko vidite, da so vse prečrtane formatirane celice filtrirane.

3.4.2.2 Z izjemnim orodjem enostavno filtrirajte prečrtano oblikovano besedilo

Z Poseben filter uporabnost Kutools za Excel, vse prečrtane oblikovane celice lahko filtriramo neposredno samo z več kliki.

1. Izberite obseg stolpcev, v katerega boste filtrirali vse formatirane celice, kliknite Kutools Plus > Poseben filter > Filtriraj prečrtano.

2. Nato a Kutools za Excel Pojavi se pogovorno okno, ki vam pove, koliko celic je kvalificiranih, kliknite OK nadaljevati.

Nato se vse prečrtane formatirane celice filtrirajo, kot je prikazano spodaj.

3.4.3 Filtrirajte po pisavi ali barvi ozadja

Kot smo že na začetku omenili v tem razdelku o oblikah filtrov, Excel podpira vgrajeno funkcijo filtriranja podatkov na podlagi vizualnih meril, kot so barva pisave, barva celic ali nabor ikon. Ta razdelek prikazuje, kako uporabiti Barva funkcija za podrobno filtriranje celic po pisavi ali barvi ozadja. Medtem priporočamo priročno funkcijo tretje osebe, ki pomaga rešiti to težavo.

3.4.3.1 Z ukazom Filtriraj po eni pisavi ali barvi ozadja

Funkcijo Filtriranje po barvi ukaza Filter lahko neposredno uporabite za filtriranje celic po določeni pisavi ali barvi ozadja v Excelu.

1. Izberite obseg stolpcev, ki jih boste filtrirali, po pisavi ali barvi ozadja in kliknite datum > Filter.

2. Kliknite puščico spustnega menija> Barva. Nato lahko vidite vse barve celic in barve pisav trenutnega obsega stolpcev. Kliknite katero koli barvo celice ali barva pisave bo filtrirala vse celice, ki temeljijo na njej.

3.4.3.2 Filtriranje po več barvah ozadja z uporabniško določeno funkcijo in ukazom Filter

Če želite celice filtrirati po več barvah ozadja, uporabite spodnjo metodo.

Recimo, da želite v stolpcu B filtrirati vse celice z oranžnimi in modrimi barvami ozadja, kot je prikazano na spodnji sliki zaslona. Najprej morate izračunati barvni indeks teh celic.

1. Pritisnite druga + F11 tipke hkrati.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Modul. Nato kopirajte spodnjo kodo VBA v okno s kodo.

Koda VBA: pridobite indeks barv ozadja celice

Function GetColor(x As Range) As Integer
GetColor = x.Interior.ColorIndex
End Function

3. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

4. V nov stolpec vnesite glavo v prvo celico (ta celica mora biti v isti vrstici glave prvotnega obsega).

5. Izberite prazno celico poleg celice glave (tukaj izberem E2), vanj vnesite spodnjo formulo in pritisnite Vnesite tipko. Izberite celico z rezultati in jo povlecite Ročaj za samodejno izpolnjevanje nad spodnjimi celicami uporabiti to formulo.

= GetColor (B2)

Opomba: Če celica nima barve zapolnitve, vrne -4142.

6. Izberite celice pomožnega stolpca (vključite glavo), kliknite datum > filter če želite v stolpec dodati filter.

7. Kliknite spustno puščico, da odprete spustni seznam, in nato konfigurirajte na naslednji način.

Zdaj celice filtrirajo po določenih barvah ozadja, kot je prikazano na spodnji sliki zaslona.

3.4.3.3 Z neverjetnim orodjem enostavno filtrirajte po pisavi ali barvi ozadja

Nedvomno je vgrajeno funkcijo Filtriranje po barvah enostavno filtrirati celice po pisavi ali barvi ozadja. Pomanjkljivost pa je v tem, da spustno polje zajema vsebino izvirnih podatkov, tako da jih kadar koli ne moremo videti za pravilno izbiro barve pisave ali ozadja. Da bi se izognili tej težavi, tukaj priporočite Poseben filter uporabnost Kutools za Excel.

1. Izberite obseg stolpcev, ki jih boste filtrirali, po pisavi ali barvi ozadja in kliknite Kutools Plus > Poseben filter > Poseben filter.

2. V Ljubljani Poseben filter pogovorno okno, nastavite na naslednji način.

Nato se filtrirajo vse celice z določeno barvo pisave ali barvo ozadja v izbranem obsegu.

3.4.4 Filtriraj celice, ki vsebujejo formule

Če imate dolg seznam podatkov, ki vsebuje tako dejanske vrednosti kot formule in potrebujete samo filtriranje celic formule, kaj lahko storite? Ta razdelek ponuja dve metodi, kako to doseči.

3.4.4.1 Filtriranje celic formule z uporabniško določeno funkcijo in ukazom Filter

Najprej morate na uporabniku določiti vse celice formule na seznamu z uporabniško določeno funkcijo in nato uporabiti ukaz Filter za filtriranje celic formule na podlagi rezultatov.

1. Pritisnite druga + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Modul, nato kopirajte spodnjo kodo VBA v okno Code.

Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function

3. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

4. Izberite prazno celico, ta celica naj bo v isti vrstici celice, v kateri želite preveriti, ali gre za celico s formulo, vanjo vnesite spodnjo formulo in pritisnite Vnesite tipko. Izberite celico z rezultatom in jo povlecite Ročaj za samodejno izpolnjevanje nad spodnjimi celicami uporabiti to formulo.

= HasFormula (C2)

Kot je prikazano na zgornji sliki zaslona, ​​so rezultati FALSE in PRAV, ki kažejo, da če je ustrezna celica celica s formulo, vrne TRUE, sicer vrne FALSE.

5. Izberite celice z rezultati (vključite celico z glavo), kliknite datum > filter da mu dodate filter.

6. Kliknite puščico navzdol, potrdite le polje zraven TRUE in nato kliknite OK.

Potem lahko vidite, da so vse celice formule filtrirane.

3.4.4.2 Enostavno filtrirajte celice formule z neverjetnim orodjem

Tukaj je prikazan poseben pripomoček za filtriranje Kutools za Excel, ki vam pomaga enostavno filtrirati celice formule na seznamu samo z več kliki.

1. Izberite seznam, za katerega želite filtrirati vse celice formule, in kliknite Kutools Plus > Poseben filter > Formula filtra.

2. Kutools za Excel Pojavi se pogovorno okno, ki vam pove, koliko celic je kvalificiranih, kliknite OK nadaljevati.

Nato se vse celice formule filtrirajo, kot je prikazano na spodnji sliki zaslona.

Poleg tega lahko uporabite Poseben filter značilnost Kutools za Excel za enostavno filtriranje celic z drugim oblikovanjem, na primer:

Filtriraj vse celice s komentarji, kliknite, če želite izvedeti več ...

Filtrirajte vse združene celice glede na določeno vrednost, kliknite, če želite izvedeti več ...

3.5 Filtriraj z nadomestnim znakom

Včasih lahko pri filtriranju pozabite na natančna merila iskanja. V tem primeru vam priporočamo uporabo nadomestnih znakov.

V Excelu so samo 3 nadomestni znaki:

Znak nadomestnega znaka	Opis	Primer
* (zvezdica)	Predstavlja poljubno število znakov	Na primer, * jagodičje ugotovi “Blackberry""Jagoda""Borovnica" in tako naprej
? (vprašaj)	Predstavlja kateri koli posamezen znak	Na primer, l? ck ugotovi “lizati""zaklepanje""Pomanjkanje" in tako naprej
~ (plima) čemur sledi *, ?ali ~	Predstavljajo resnično *,? or ~ značaja	Na primer, Face ~ * Nakupujte najdbe “Face * Shop”

Poglejmo, kako uporabiti nadomestni znak pri filtriranju.

Recimo, da morate filtrirati vse celice, ki se v spodnjem posnetku zaslona končajo z Marketom v stolpcu B, storite naslednje.

1. Najprej ustvarite obseg meril. Vnesite glavo, enako kot prvotna glava stolpca, in nato v spodnjo celico vnesite kriterije filtra. Oglejte si posnetek zaslona:

2. klik datum > Napredno.

3. Na odprtju Napredni filter pogovorno okno, konfigurirajte na naslednji način.

Nato se vse celice, ki se končajo z Marketom, filtrirajo. Oglejte si posnetek zaslona:

Uporaba nadomestnih znakov * in ~ pri filtriranju je enaka kot pri zgornjih postopkih.

3.6 Filter z vgrajenim iskalnim poljem

Če uporabljate Excel 2010 ali novejšo različico, boste morda opazili, da je v Excelovem filtru vgrajeno iskalno polje. Ta razdelek prikazuje, kako lahko to iskalno polje uporabite za filtriranje podatkov v Excelu.

Kot je prikazano na spodnjem posnetku zaslona, ​​želite filtrirati vse celice, ki vsebujejo »Market«, iskalno polje vam lahko pomaga, da to enostavno izvedete.

1. Izberite obseg stolpcev, v katerega boste filtrirali podatke, kliknite datum > filter da mu dodate filter.

2. Kliknite spustno puščico in vnesite "Tržnica" v iskalno polje in nato kliknite OK.

Med vnosom podatkov v iskalno polje lahko vidite, da so vsa kvalificirana besedila navedena v realnem času.

Nato se vse celice, ki vsebujejo »Market«, filtrirajo, kot je prikazano na spodnji sliki zaslona.

---

4. Kopirajte samo vidne podatke (prezrite skrite ali filtrirane podatke)

Excel privzeto kopira vidne in skrite celice. Če želite kopirati samo vidne celice po filtriranju, lahko poskusite z enim od spodnjih načinov.

Kopirajte vidne podatke samo s bližnjicami

S tipkami za bližnjice lahko izberete samo vidne celice, nato pa jih ročno kopirate in prilepite na želeno mesto.

1. Izberite obseg, v katerega želite kopirati samo vidne celice. V tem koraku so izbrane tako vidne kot skrite celice.

2. Pritisnite druga in ; tipke hkrati. Zdaj so izbrane samo vidne celice.

3. Pritisnite Ctrl + C tipke za kopiranje izbranih celic in pritisnite Ctrl + V tipke, da jih prilepite.

Preprosto kopirajte vidne podatke samo z neverjetnim orodjem

Tukaj predstavite Prilepite v Vidno značilnost Kutools za Excel za enostavno kopiranje vidnih podatkov samo v Excelu. Poleg tega lahko s to funkcijo vrednosti kopirate in prilepite samo v vidne celice v obsegu, ki je bil filtriran.

1. Izberite filtrirani obseg, v katerega želite kopirati samo vidne celice, in kliknite Kutools > Območje > testenine to Vidno > vsi / Samo prilepi vrednosti.

Za celice formule izberite vsi kopira rezultat in formule, izberite Samo prilepi vrednosti kopira samo dejanske vrednosti.

2. V pojavnem oknu Prilepite v vidni obseg v pogovornem oknu izberite prazno celico za izpis kopiranih celic in kliknite OK.

Nato se samo vidne celice v izbranem filtriranem obsegu kopirajo in prilepijo na novo mesto.

Opomba: Če je bil ciljni obseg filtriran, bodo izbrane vrednosti prilepljene samo v vidne celice.

---

5. Po filtriranju izbrišite skrite ali vidne vrstice

Za filtrirani seznam boste morda morali izbrisati skrite vrstice, da boste obdržali samo vidne podatke. Tu v tem razdelku se boste naučili treh načinov za brisanje skritih ali vidnih vrstic na filtriranem seznamu v Excelu.

Iz trenutnega delovnega lista s kodo VBA izbrišite vse skrite vrstice

Spodnja koda VBA vam lahko pomaga izbrisati vse skrite vrstice s trenutnega delovnega lista v Excelu.

Opomba: Ta VBA odstrani ne samo skrito vrstico s filtriranega seznama, temveč tudi vrstice, ki ste jih ročno skrili.

1. V delovnem listu so skrite vrstice, ki jih želite odstraniti, pritisnite druga + F11 tipke hkrati, da odprete Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Modul, in nato kopirajte spodnjo kodo VBA v okno modula.

Koda VBA: izbrišite vse skrite vrstice s trenutnega delovnega lista

Sub RemoveHiddenRows()
    Dim xRow As Range
    Dim xRg As Range
    Dim xRows As Range
    On Error Resume Next
    Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
    If xRows Is Nothing Then Exit Sub
        For Each xRow In xRows.Columns(1).Cells
            If xRow.EntireRow.Hidden Then
                If xRg Is Nothing Then
                    Set xRg = xRow
                Else
                    Set xRg = Union(xRg, xRow)
                End If
            End If
        Next
        If Not xRg Is Nothing Then
            MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
            xRg.EntireRow.Delete
        Else
            MsgBox "No hidden rows found", , "Kutools for Excel"
        End If
End Sub

3. Pritisnite F5 tipko za zagon kode.

4. Nato a Kutools za Excel Pojavi se pogovorno okno, ki vam pove, koliko skritih vrstic je bilo odstranjenih, kliknite OK da ga zaprete.

Zdaj so odstranjene vse skrite vrstice (vključno s samodejno skritimi in ročno skritimi vrsticami).

Izbrišite vidne vrstice po filtriranju s funkcijo Pojdi na

Če želite odstraniti samo vidne vrstice v določenem obsegu, bo Pojdi na vam lahko pomaga.

1. Izberite filtrirani obseg, za katerega želite odstraniti vidne vrstice, pritisnite F5 ključ, da odprete Pojdi na pogovorno okno.

2. V Ljubljani Pojdi na pogovorno okno, kliknite na Poseben gumb.

3. V Ljubljani Pojdi na posebno v pogovornem oknu izberite Samo vidne celice in nato kliknite OK gumb.

4. Zdaj so izbrane vse vidne celice. Z desno miškino tipko kliknite izbrani obseg in kliknite Izbriši vrstico v kontekstnem meniju.

Nato se izbrišejo vse vidne celice.

Po filtriranju z neverjetnim orodjem enostavno izbrišite skrite ali vidne vrstice

Zgornje metode so dolgočasne in dolgotrajne. Tukaj priporočite Izbriši skrite (vidne) vrstice in stolpce značilnost Kutools za Excel. S to funkcijo lahko enostavno izbrišete skrite ali vidne vrstice ne samo v izbranem obsegu, temveč tudi v trenutnem delovnem listu, več izbranih delovnih listih ali celotnem delovnem zvezku. Naredite naslednje.

1. Izberite obseg, iz katerega želite odstraniti vse skrite ali vidne vrstice.

Opombe:

2. klik Kutools > Brisanje > Izbriši skrite (vidne) vrstice in stolpce.

3. V Ljubljani Izbriši skrite (vidne) vrstice in stolpce pogovorno okno, morate konfigurirati na naslednji način.

4. Nato se naenkrat izbrišejo vse vidne ali skrite vrstice. Medtem se odpre pogovorno okno, ki vam pove število izbrisanih vrstic, kliknite OK da končate celotno operacijo.

---

6. Filtrirajte na več listih

Običajno je enostavno filtrirati podatke na delovnem listu. V tem razdelku boste izvedeli, kako filtrirati podatke z enakimi merili na več delovnih listih, ki imajo skupno strukturo podatkov.

Recimo, da delovni zvezek vsebuje tri delovne liste, kot je prikazano na spodnji sliki zaslona, ​​zdaj želite hkrati filtrirati podatke na teh treh delovnih listih z enakimi merili “Izdelek = KTE”, Spodnja koda VBA vam lahko stori uslugo.

1. Pritisnite druga + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Modul, in nato kopirajte spodnjo kodo VBA v okno modula.

Koda VBA: Filtrirajte podatke na več delovnih listih hkrati

Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20210518
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
        xWs.Range("A1").AutoFilter 1, "=KTE"
    Next
End Sub

Opomba: Linija "Xws.Range (“A1” .AutoFilter 1, “= KTE”)"V kodi označuje, da boste podatke filtrirali v stolpec A z merili = KTE, številka 1 pa je številka stolpca stolpca A. Lahko jih spremenite po potrebi. Če želite na primer v stolpcu B filtrirati vse številke, ki so večje od 500, lahko to vrstico spremenite v »Xws.Range (“B1”. AutoFilter 2, “> 500”)".

3. Pritisnite F5 tipko za zagon kode.

Nato se navedeni stolpci istočasno filtrirajo na vseh delovnih listih trenutnega delovnega zvezka. Oglejte si spodnje rezultate.

---

7. Po spremembi podatkov ponovno uporabite filter

Včasih ste morda spremenili filtrirani obseg. Rezultat filtra pa ostaja enak, ne glede na to, kakšne spremembe ste naredili v obsegu (glejte posnetek zaslona spodaj). V tem razdelku vam bomo prikazali dva načina za ročno ali samodejno ponovno uporabo filtra na trenutnem obsegu, tako da bodo vključene spremembe, ki ste jih izvedli.

Ročno ponovno uporabite filter z ukazom Ponovna uporaba

Excel ima vgrajeno Ponovno se prijavi funkcija za ročno ponovno uporabo filtra. Prijavite se lahko na naslednji način.

klik datum > Ponovno se prijavi za ponovno uporabo filtra v trenutnem listu.

Nato lahko vidite, da se filtrirani obseg ponovno uporabi, da vključuje spremembe, ki ste jih naredili.

Samodejno znova uporabite filter s kodo VBA

Če je treba filtrirani seznam pogosto spreminjati, morate večkrat klikniti, da uporabite to funkcijo Ponovna uporaba. Tukaj navedite kodo VBA, ki pomaga samodejno ponovno uporabiti filter v realnem času, ko se podatki spremenijo.

1. Na delovnem listu je filter, ki ga želite samodejno ponovno uporabiti, z desno miškino tipko kliknite jeziček lista in izberite Ogled kode.

2. Na odprtju Microsoft Visual Basic za aplikacije okno, kopirajte spodnjo kodo VBA v okno Code.

Koda VBA: samodejno znova uporabi filter pri spreminjanju podatkov

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub

Opomba: V kodi, “List2” je ime trenutnega delovnega lista. Lahko ga spremenite glede na vaše potrebe.

3. Pritisnite druga + Q tipke za zapiranje Microsoft Visual Basic za aplikacije okno.

Od zdaj naprej se bo pri spreminjanju podatkov na filtriranem rezultatu filtrirani rezultat dinamično prilagajal. Oglejte si spodnjo gif sliko.

---

8. Očistite ali odstranite filter

Naučili smo se, kako dodati, uporabiti in uporabiti filter v zgornji vsebini. Tukaj se bomo naučili, kako počistiti ali odstraniti filter v Excelu.

8.1 Počistite filter iz stolpca

Po uporabi filtra v stolpcu, če ga želite počistiti, kliknite ikono za filtriranje in nato Počisti filter iz imena glave iz spustnega menija.

8.2 Počistite vse filtre na delovnem listu

Če ste filter uporabili za več stolpcev in želite hkrati počistiti vse, kliknite datum > Jasno.

Nato se vsi filtri izbrišejo, kot je prikazano na spodnji sliki zaslona.

8.3 Počistite filtre z vseh delovnih listov v trenutnem delovnem zvezku

Recimo, da ste uporabili filtre na več delovnih listih v delovnem zvezku in želite te filtre počistiti hkrati. Spodnja koda VBA vam lahko stori uslugo.

1. Odprite delovni zvezek in iz njega izbrišete vse filtre, nato pritisnite druga + F11 tipke hkrati.

2. Na odprtju Microsoft Visual Basic za aplikacije okno, kliknite Vstavi > Modul, in nato kopirajte spodnjo kodo VBA v okno modula.

Koda VBA: počistite filtre z vseh delovnih listov v trenutnem delovnem zvezku

Sub Auto_Open()
'Updated by Extendoffice 20201113
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

3. Pritisnite F5 tipko za zagon kode. Nato se vsi filtri izbrišejo iz vseh delovnih listov v trenutnem delovnem zvezku.

8.4 Odstranite vse filtre z delovnega lista

Zgornje metode pomagajo samo počistiti stanje filtriranja in filtri še vedno ostanejo na delovnem listu. Če želite z delovnega lista odstraniti vse filtre, preizkusite spodnje metode.

Odstranite vse filtre na delovnem listu tako, da izklopite filter

klik datum > filter za izklop funkcije (gumb Filter ni v stanju osvetlitve).

Odstranite vse filtre na delovnem listu s tipko za bližnjico

Poleg tega lahko s tipko za bližnjico odstranite vse filtre na delovnem listu.

V delovnem listu so filtri, ki jih želite odstraniti, pritisnite Ctrl + Shift + L tipke hkrati.

Nato se vsi filtri na trenutnem delovnem listu takoj odstranijo.