Suurin osa Excelin käyttäjistä, kun sana "tietojen suodatus" tulee mieleen, vain tavallinen klassinen suodatin välilehdestä Tiedot – Suodatin (Data – suodatin):
Tällainen suodatin on epäilemättä tuttu asia, ja useimmissa tapauksissa se tekeekin. On kuitenkin tilanteita, joissa joudut suodattamaan useiden monimutkaisten ehtojen mukaan useissa sarakkeissa kerralla. Tavallinen suodatin täällä ei ole kovin kätevä ja haluan jotain tehokkaampaa. Tällainen työkalu voisi olla edistynyt suodatin, varsinkin pienellä "viimeistelyllä" (perinteen mukaan).
Perusta
Aloita lisäämällä muutama tyhjä rivi tietotaulukon yläpuolelle ja kopioimalla taulukon otsikko sinne – tämä on alue ehdoineen (korostettu keltaisella selvyyden vuoksi):
Keltaisten solujen ja alkuperäisen taulukon välissä on oltava vähintään yksi tyhjä rivi.
Keltaisiin soluihin on syötettävä kriteerit (ehdot), joiden mukaan suodatus sitten suoritetaan. Esimerkiksi, jos sinun on valittava banaanit Moskovan "Auchanissa" III vuosineljänneksellä, olosuhteet näyttävät tältä:
Suodata valitsemalla mikä tahansa solu alueella, jossa on lähdetiedot, ja avaa välilehti Päiväys Ja napsauta Lisäksi (Data – Lisäasetukset). Avautuvassa ikkunassa pitäisi jo automaattisesti syöttää data-alue ja meidän on määritettävä vain ehtoalue, eli A1:I2:
Huomaa, että ehtoaluetta ei voi allokoida "marginaalilla", eli et voi valita ylimääräisiä tyhjiä keltaisia rivejä, koska ehtoalueen tyhjän solun Excel havaitsee kriteerin puuttumisena ja kokonaisena tyhjänä. rivillä pyyntönä näyttää kaikki tiedot umpimähkäisesti.
Vaihtaa Kopioi tulos toiseen paikkaan sallii sinun suodattaa luettelon ei tässä taulukossa (kuten tavallisella suodattimella), vaan purkaa valitut rivit toiselle alueelle, joka on sitten määritettävä kenttään Aseta tulos vaihteluvälille. Tässä tapauksessa emme käytä tätä toimintoa, jätämme Suodatinlista paikallaan ja valitse OK. Valitut rivit näkyvät taulukossa:
Makron lisääminen
"No, missä tässä on mukavuus?" kysyt ja olet oikeassa. Sinun ei tarvitse vain syöttää ehtoja keltaisiin soluihin käsilläsi, vaan myös avata valintaikkuna, syöttää alueelle alueet, paina OK. Surullista, samaa mieltä! Mutta "kaikki muuttuu, kun ne tulevat ©" - makrot!
Työskentelyä edistyneen suodattimen kanssa voidaan nopeuttaa ja yksinkertaistaa huomattavasti käyttämällä yksinkertaista makroa, joka suorittaa edistyneen suodattimen automaattisesti, kun olosuhteet asetetaan, eli keltaisia soluja muutetaan. Napsauta hiiren kakkospainikkeella nykyisen arkin välilehteä ja valitse komento Lähdeteksti (Lähdekoodi). Kopioi ja liitä seuraava koodi avautuvassa ikkunassa:
Private Sub Worksheet_Change(ByVal Target as Range) Jos ei leikkaa(kohde, alue("A2:I5")) ei ole mitään, sitten on virhe Jatka seuraavaksi ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=Rlace,CriteInria :=Alue("A1").CurrentRegion End If End Sub Tämä toimenpide suoritetaan automaattisesti, kun mitä tahansa nykyisen laskentataulukon solua muutetaan. Jos muuttuneen solun osoite putoaa keltaiselle alueelle (A2:I5), tämä makro poistaa kaikki suodattimet (jos sellaisia on) ja lisää laajennetun suodattimen uudelleen lähdetietotaulukkoon alkaen A7, eli kaikki suodatetaan välittömästi, välittömästi. seuraavan ehdon syöttämisen jälkeen:
Joten kaikki on paljon paremmin, eikö? 🙂
Monimutkaisten kyselyjen toteuttaminen
Nyt kun kaikkea suodatetaan lennossa, voimme mennä hieman syvemmälle vivahteisiin ja purkaa monimutkaisempien kyselyiden mekanismit edistyneessä suodattimessa. Tarkkojen vastaavuuksien syöttämisen lisäksi voit käyttää erilaisia yleismerkkejä (* ja ?) ja matemaattisia epäyhtälömerkkejä useissa olosuhteissa toteuttaaksesi likimääräisen haun. Hahmon tapauksella ei ole väliä. Selvyyden vuoksi olen koonnut kaikki mahdolliset vaihtoehdot taulukkoon:
| Kriteeri | Tulos |
| gr* tai gr | kaikki solut alkaen GrIe Grkorva, Grapfruitti, Granat ja niin edelleen |
| = sipuli | kaikki solut täsmälleen ja vain sanalla Jousi, eli tarkka vastaavuus |
| *liv* tai *liv | sisältävät solut Liv miten alleviivaus, esim ОLivettä, Livep, MukaanLiv ja niin edelleen |
| =p*v | sanat alkavat П ja päättyen В ie Пensimmäinenв, Пeetteriв ja niin edelleen |
| kuten | sanat alkavat А ja lisäksi sisältävät СIe Аpelсin, Аnanaс, Asai ja niin edelleen |
| =*s | sanat päättyvät С |
| =???? | kaikki solut, joissa on 4 merkin tekstiä (kirjaimet tai numerot, mukaan lukien välilyönnit) |
| =m??????n | kaikki solut, joissa teksti on 8 merkkiä alkaen М ja päättyen НIe Мandariн, Мlevottomuusн ja niin edelleen |
| =*n??a | kaikki sanat, jotka päättyvät А, missä on neljäs kirjain lopusta НIe Palkkiнikа, Mukaanнozа ja niin edelleen |
| >=e | kaikki sanat alkavat Э, Ю or Я |
| <>*o* | kaikki sanat, jotka eivät sisällä kirjainta О |
| <>*vich | kaikki sanat paitsi ne, jotka päättyvät HIV (esimerkiksi suodata naiset toisen nimen mukaan) |
| = | kaikki tyhjät solut |
| <> | kaikki ei-tyhjät solut |
| > = 5000 | kaikki solut, joiden arvo on suurempi tai yhtä suuri kuin 5000 |
| 5 tai =5 | kaikki solut, joiden arvo on 5 |
| >=3 | kaikki solut, joiden päivämäärä on 18. maaliskuuta 2013 jälkeen (mukaan lukien) |
Hienovaraisia pointteja:
- *-merkki tarkoittaa mitä tahansa määrää mitä tahansa merkkejä, ja ? - yksi hahmo.
- Tekstin ja numeeristen kyselyjen käsittelyn logiikka on hieman erilainen. Joten esimerkiksi ehtosolu numerolla 5 ei tarkoita kaikkien viidellä alkavien lukujen etsimistä, vaan ehtosolu, jossa on kirjain B, on yhtä suuri kuin B*, eli etsii mitä tahansa B-kirjaimella alkavaa tekstiä.
- Jos tekstikysely ei ala =-merkillä, voit laittaa *:n loppuun.
- Päivämäärät on syötettävä yhdysvaltalaisessa muodossa kuukausi-päivä-vuosi ja murto-osan kautta (vaikka sinulla olisi Excel- ja alueasetukset).
Loogiset liitännät JA-TAI
Eri soluihin, mutta samalle riville kirjoitetut ehdot katsotaan loogisen operaattorin yhdistäviksi И (JA):
Nuo. suodatin banaaneja minulle kolmannella neljänneksellä, juuri Moskovassa ja samaan aikaan Auchanista.
Jos haluat linkittää ehdot loogiseen operaattoriin OR (OR), sitten ne on syötettävä eri riveille. Jos meidän on esimerkiksi löydettävä kaikki johtaja Volinan Moskovan persikoiden tilaukset ja kaikki sipulitilaukset kolmannella vuosineljänneksellä Samarassa, tämä voidaan määrittää useilla ehdoilla seuraavasti:
Jos sinun on asetettava kaksi tai useampia ehtoja yhdelle sarakkeelle, voit yksinkertaisesti kopioida sarakkeen otsikon kriteerialueella ja kirjoittaa sen alle toisen, kolmannen jne. ehdot. Joten voit esimerkiksi valita kaikki tapahtumat maaliskuusta toukokuuhun:
Yleisesti ottaen "viimeistelyn jälkeen" kehittynyt suodatin osoittautuu varsin kunnolliseksi työkaluksi, paikoin ei huonommaksi kuin klassinen automaattisuodatin.
- Supersuodatin makroissa
- Mitä makrot ovat, missä ja miten makrokoodi lisätään Visual Basiciin
- Älykkäät taulukot Microsoft Excelissä