Edistyneen suodattimen käyttö Excelissä Mortin ongelman ratkaisemiseksi. Vaikka tavalliset suodattimet ovatkin tulleet tehokkaammiksi, on edelleenkin tilanteita, joissa Advanced Filter voi tehdä joitain temppuja, joita muut eivät.
Katso video
- Lisäsuodatin on "edistyneempi" kuin tavallinen suodatin, koska:
- 1) Se voi kopioida uudelle alueelle
- 2) Voit luoda monimutkaisempia ehtoja, kuten Kenttä 1 = A tai Kenttä 2 = A
- 3) Se on nopea
- Mort yrittää käsitellä 100 000 riviä VBA: ssa silmukoiden läpi tietueita tai käyttämällä taulukkoa
- Sisäänrakennettujen Excel-ominaisuuksien käyttö on aina nopeampaa kuin oman koodin kirjoittaminen.
- Tarvitset syöttöalueen ja sitten kriteerialueen ja / tai lähtöalueen
- Syöttöalue: yksi otsikkorivi tietojen yläpuolella
- Lisää väliaikainen rivi otsikoille
- Lähtöalue: otsikkorivi sarakkeille, jotka haluat purkaa
- Kriteerialue: otsikot rivillä 1, arvot alkavat riviltä 2
- Komplikaatio: Excelin vanhemmat versiot eivät salli tulostusalueen olevan toisella arkilla
- Jos kirjoitat makroa, joka saatetaan ajaa vuonna 2003, käytä kiertämiseen syötettyä aluetta nimeltä
Videon transkriptio
Opi Excel Podcastista, Episode 2060: Excel Advanced Filter
Hei, tervetuloa takaisin netcastiin, olen Bill Jelen. Tämän päivän kysymyksen lähetti Mort. Mortilla on 100 000 tietoriviä ja hän on kiinnostunut sarakkeista A, B ja D, joissa sarake C vastaa tiettyä vuotta. Joten hän haluaa henkilön syöttävän vuoden ja saamaan sitten sarakkeet A, B ja D. Ja Mortilla on jonkin verran VBA: ta, jossa hän käyttää matriiseja tähän ja sanoin: "Odota hetki, tiedät, edistynyt suodatin tekisi tämän a paljon parempi. " Selvä, ja nyt vain tarkistaakseni, palasin takaisin, katsoin videoita läpi. En ole käsitellyt tarkennettua suodatinta pitkään aikaan, joten meidän pitäisi puhua tästä.
Edistynyt suodatin vaatii syöttöalueen ja sitten vähintään yhden näistä: ehtoalueen tai lähtöalueen. Vaikka tänään aiomme käyttää molempia. Selvä, joten syötealue on tietosi ja sinulla on oltava otsikot tietojen yläpuolella. Joten, Mortilla ei ole otsikoita, joten aion lisätä väliaikaisesti rivin tähän ja tehdä vain kuten kenttä 1. Mort tietää, mitä hänen tietonsa ovat, ja jotta hän voisi laittaa todelliset otsikot ylös. Ja emme käytä mitään kutsuttua-näitä tietoja sarakkeissa E - O, joten minun ei tarvitse lisätä otsikoita sinne, okei? Joten nyt A1: stä D: hen, 100000: sta tulee tuloalueeni. Ja sitten lähtöalue ja kriteerialue - No, lähtöalue on vain luettelo haluamistasi otsikoista. Joten aion laittaa lähtöalueen tähän ja emme tarvitse kenttää 3, jotenOtan sen vain sivulle. Joten nyt tästä alueesta A1: stä C1: ksi tulee lähtöalue, joka kertoo Excelille, mitkä kentät haluan syöttöalueelta. Ja ne voivat olla eri järjestyksessä, jos haluat järjestää asiat uudelleen, kuten jos haluan ensin kentän 4 ja sitten kentän 1 ja sitten kentän 2. Ja taas, nämä olisivat todellisia otsikoita, kuten laskun numero. En vain tiedä miltä Mortin tiedot näyttävät.
Ja sitten ehtojen alue on otsikko ja mitä arvoa haluat. Joten sanotaan, että yritin saada mitään vuonna 2014. Tästä tulee sellainen kriteerialue. Selvä, vain varoituksen sana täällä. Olen Excel 2016: ssa ja on mahdollista tehdä edistynyt suodatin kahden arkin välillä Excel 2016: ssä, mutta jos palaat takaisin, enkä muista, mikä tie on, ehkä 2003, en ole varma. Jossain vaiheessa menneisyydessä oli tapana, että et voinut tehdä edistynyttä suodatinta yhdeltä arkilta toiselle, joten sinun on tultava tänne ja nimettävä syöttöalueesi. Sinun on luotava nimi tähän. OmaNimi tai jotain sellaista, okei? Ja se olisi tapa, jolla pystyt vetämään tämän pois, okei. Ei välttämättä Excel 2016: ssä, mutta taasEn ole varma, aikooko Mort käyttää tätä tietojen vanhemmissa versioissa.
Selvä, joten palataan tänne Dataen, siirrymme Edistyneeseen suodattimeen. Ja aiomme kopioida toiseen paikkaan, joka mahdollistaa tuotantoalueemme siellä. Selvä, joten luetteloalue, missä tiedot ovat? Koska olen Excel 2016: ssä, aion siirtyä kohtaan Data sen sijaan, että käyttäisin nimialuetta - Joten se on minun syöttöalueeni. Kriteerialue on ne solut, jotka ovat siellä ja sitten, mihin olemme menossa - lähtö, ne ovat vain nämä kolme solua siellä. Ja sitten napsautamme OK. Selvä, ja BAM! Se on kuinka nopeaa, nopeaa se on. Entä jos haluaisimme toisen vuoden? Jos haluaisimme toisen vuoden, poistamme tulokset, laitimme vuoden 2015 ja teemme sitten tarkennetun suodattimen uudelleen, Kopioi toiseen sijaintiin, napsauta OK ja kaikki vuoden 2015 tietueet ovat. Salamannopea.
Okei nyt, vaikka olen edistyneen suodattimen fani tavallisessa Excelissä, olin massiivinen edistyneen suodattimen fani VBA: ssa, ok, koska VBA tekee etusuodattimesta todella, todella, todella yksinkertaisen. Selvä, joten kirjoitamme koodin tähän Mortia varten olettaen, että Mortin tiedoilla ei ole otsikoita ja joudumme lisäämään otsikot väliaikaisesti, okei? Joten vaihdan VBA, Alt + F11 ja suoritamme tämän laskentataulukosta, jossa on tiedot. Joten: Himmennä WS laskentataulukoksi, aseta WS = ActiveSheet. Lisää sitten rivi 1 ja lisää vain otsikot: A, B, vuosi ja D. Selvitä, kuinka monta tietoriviä meillä on tänään ja aloita sitten solusta A1, joka kulkee 4 saraketta alas viimeiseen riviin, nimeä se olla syöttöalue. Selvä, ja sitten tämä on oikeastaan Mortin koodi täällä, missä hän pyysi InputBoxia,saa haluamansa vuoden ja sitten hän kysyy, minkä vuoden tai mitä he haluavat nimetä uudelle arkille. Joten se todella lisää levyn Flyiin ja sitten - Mitoitan uuden arkin, WSN: n ActiveSheetiksi. Joten tiedän, että WS on alkuperäinen arkki, WSN on uusi arkki, joka juuri lisättiin. Laita uudelle taulukolle kriteerialue niin, että sarakkeen E alla on otsikko, joka vastaa tätä otsikkoa, ja sitten kumpi vastauksen he meille antoivat, menee kohtaan E2. Lähtöalue tulee olemaan kolme muuta otsikkoni: A, B ja D.Ja vielä kerran, jos sinä tai Mort vaihdatte nämä todellisiksi otsikoiksi, mikä on luultavasti parempi asia kuin A, B, D, ja haluaisitte myös Vaihda nämä todellisiin otsikoihin, okei? Joten kaikki tämä on vain vähän ennakkotyötä täällä. Tämä yksi mahtava koodirivi tekee koko edistyneen suodattimen. Niin,InputRangeista teemme AdvancedFilter, kopioimme. Se on valinta suodatin paikallaan tai kopio. CriteriaRange on E1 - E2, CopyToRange on A - C. Ainutlaatuiset arvot -Ei, haluamme kaikki arvot. Selvä, tuo yksi koodirivi tekee kaiken taian silmukoiden läpi kaikki tietueet tai korvaa silmukan kaikkien tietueiden läpi tai tekemällä taulukot. Ja sitten olemme valmiit, tyhjennämme kriteerialueen ja poistamme sitten rivin 1 takaisin alkuperäiseen laskentataulukkoon.Ja sitten olemme valmiit, tyhjennämme kriteerialueen ja poistamme sitten rivin 1 takaisin alkuperäiseen laskentataulukkoon.Ja sitten olemme valmiit, tyhjennämme kriteerialueen ja poistamme sitten rivin 1 takaisin alkuperäiseen laskentataulukkoon.
Okei, vaihdetaan siis takaisin tietoihimme. Teemme tämän suorittamisen helpoksi, joten: Lisää, muoto ja kutsu tätä Suodatin, Koti, Keski, Keski, Suurempi, Suurempi, Suurempi, napsauta hiiren kakkospainikkeella, Määritä makro ja määritä se MacroForMortille. Selvä, joten tässä mennään. Teemme testin. Katso, että olemme tietosivulla, napsauta Suodatin, mitä vuotta haluamme? Haluamme vuoden 2015. Miksi haluan kutsua sitä? Haluan kutsua sitä vuodeksi 2015, okei. Ja BAM! Siinä se on tehty. Se on kuinka nopeaa, niin nopeaa tämä on.
Koska Mortin alkuperäisillä tiedoilla ei ollut otsikoita, ehkä näillä tiedoilla ei pitäisi olla otsikoita. Joten mennään Alt + F11, täällä haluamme tyhjentää kriteerialueen. Tulemme myös riveihin (1). Selvä, joten nyt kun seuraavan kerran käytimme tätä, se päästä eroon näistä otsikoista. Ja katsotaanpa - Sen sijaan, että suoritamme koko asian nopeasti, katsotaanpa täällä 2014: n kanssa. Joten valitsen yhden solun Datasta, Alt + F11, ja haluan juosta vain siihen pisteeseen, jossa teemme edistynyt suodatin. Joten voimme katsoa ja nähdä, mitä koko makro tekee täällä. Joten napsautamme Suorita, ja haluan saada vuoden 2014, ok. Ja niin, paina F8, olemme tekemässä edistyneen suodattimen. Voimme palata Exceliin täällä ja nähdä, mitä tapahtui.
Ensimmäinen asia, mitä on tapahtunut - Nyt ensimmäinen asia, joka tapahtui, on lisännyt uuden väliaikaisen rivin otsikoihin. Lisättiin tämä laskentataulukko, rakennettiin kriteerialue, jossa oli otsikko ja minkä vuoden he syöttivät, valitsi kentät, jotka haluamme tehdä, ja sitten takaisin VBA: seen, suoritan seuraavan koodirivin, se on F8, joka suorittaa edistyneen suodattimen siellä . Se on uskomattoman nopea ja huomaat, että se on tosiasiallisesti tuonut meille kaikki levyt. Sieltä se on vain vähän puhdistusta, poista tämä, poista tämä. Palaan tietoihin ja poistan rivin 1, ja meillä on hyvä mennä. Joten annan vain jäljellä olevan juoksun, poistan tauon, okei? Joten siellä on VBA. Minulle tämä on mielestäni nopein tapa, nopein tapa edetä.
Selvä, jakson yhteenveto: Edistynyt suodatin on edistyneempi kuin tavallinen suodatin, koska se voi kopioida uudelle alueelle. Ja nyt, en näyttänyt sitä tässä videossa, mutta voit rakentaa monimutkaisia ehtoja, joissa Kenttä 1 = A tai Kenttä 2 = A. Tavallinen automaattinen suodatin ei voi tehdä sitä ja se on nopea. Mort yrittää käsitellä 100000 riviä VBA: ssa käyttämällä taulukkoa tai silmukoiden avulla, mutta Excel-rakennusominaisuuksien käyttö on aina nopeampi kuin oman koodin kirjoittaminen. Sinun on määritettävä syöttöalue, kriteerialue ja lähtöalue. Tarvitset aina tuloalueen ainakin yhdessä näistä, vaikka tänään käytän molempia. Syöttöalueen osalta yksi otsikkorivi tietojen yläpuolella. Joten lisäämme väliaikaisen otsikkorivin. Lähtöalueella samat otsikot, jotka haluat purkaa, kunnossa. Joten tiedät, jos se oli A, B,Vuosi ja D, asetamme vain A, B ja D lähtöalueeksi. Kriteerialueelle otsikot riville 1. Joten tämä on kenttä, johon haluan rakentaa kriteerit, ja tämä on arvo, jota etsin. Komplikaatiot: Excelin vanhemmat versiot eivät salli tulostusalueen olevan toisella arkilla, joten mahdollisesti koodisi juoksee sitten. Haluat käyttää nimettyä aluetta syötealueella, koska tiedät, että tällä taulukolla nimetty alue, vaikka se on toisella taulukolla, uskoo, että nimi haarautuu nykyiselle taulukolle. Joten se antaisi edistyneen suodattimen toimivan.Vanhemmat Excel-versiot eivät salli tulostusalueen olevan toisella taulukolla, joten koodi saattaa toimia silloin. Haluat käyttää nimettyä aluetta syötealueella, koska tiedät, että tällä taulukolla nimetty alue, vaikka se on toisella taulukolla, uskoo, että nimi haarautuu nykyiselle taulukolle. Joten se antaisi edistyneen suodattimen toimivan.Vanhemmat Excel-versiot eivät salli tulostusalueen olevan toisella taulukolla, joten koodi saattaa toimia silloin. Haluat käyttää nimettyä aluetta syötealueella, koska tiedät, että tällä taulukolla nimetty alue, vaikka se on toisella taulukolla, uskoo, että nimi haarautuu nykyiselle taulukolle. Joten se antaisi edistyneen suodattimen toimivan.
Selvä, no, sinulla on se. Haluan kiittää Mortia tämän kysymyksen lähettämisestä. Haluan kiittää sinua siitä, että kävit ohi. Nähdään ensi kerralla toisesta netcastista.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2060.xlsm
