Použitie rozšíreného filtra v programe Excel na vyriešenie Mortovho problému. Aj keď sa bežné filtre stali výkonnejšími, stále existujú situácie, kedy pokročilý filter dokáže niektoré triky, ktoré iné nie.
Pozeraj video
- Pokročilý filter je „pokročilejší“ ako bežný filter, pretože:
- 1) Môže kopírovať do nového rozsahu
- 2) Môžete vytvoriť zložitejšie kritériá, ako napríklad Pole 1 = A alebo Pole 2 = A
- 3) Je to rýchle
- Mort sa pokúša spracovať 100 000 riadkov vo VBA opakovaním záznamov alebo použitím poľa
- Používanie vstavaných funkcií programu Excel bude vždy rýchlejšie ako písanie vlastného kódu.
- Potrebujete vstupný rozsah a potom rozsah kritérií a / alebo výstupný rozsah
- Pre vstupný rozsah: jeden riadok nadpisov nad údajmi
- Pridajte dočasný riadok pre nadpisy
- Pre výstupný rozsah: riadok nadpisov pre stĺpce, ktoré chcete extrahovať
- Pre rozsah kritérií: nadpisy v riadku 1, hodnoty začínajúce v riadku 2
- Komplikácia: Staršie verzie programu Excel by neumožňovali, aby sa výstupný rozsah nachádzal na inom hárku
- Ak píšete makro, ktoré by sa dalo spustiť v roku 2003, použite na obídenie pomenovaný rozsah pre vstupný rozsah
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2060: Pokročilý filter programu Excel
Ahoj, vitaj naspäť na netcast, som Bill Jelen. Dnešnú otázku poslal Mort. Mort, má 100 000 riadkov údajov a zaujímajú ho stĺpce A, B a D, kde stĺpec C zodpovedá konkrétnemu roku. Takže chce, aby človek vstúpil do jedného roka a potom dostal stĺpce A, B a D. A Mort má nejaké VBA, kde na to používa polia a ja som povedal: „Počkajte chvíľu, viete, pokročilý filter by to urobil oveľa lepšie. “ Dobre, a teraz, len na kontrolu, som sa vrátil, pozrel som sa späť cez svoje videá. Pokročilému filtru som sa dlho nevenoval, takže by sme sa mali o tom porozprávať.
Pokročilý filter vyžaduje vstupný rozsah a potom aspoň jedno z nich: rozsah kritérií alebo výstupný rozsah. Aj keď dnes použijeme obe tieto možnosti. Dobre, takže vstupným rozsahom sú vaše údaje a nad údajmi musíte mať nadpisy. Takže Mort nemá nadpisy, a tak dočasne vložím riadok sem a urobím to ako Pole 1. Mort vie, aké sú jeho údaje, a mohol by tam teda umiestniť skutočné nadpisy. A nepoužívame nič, čo sa nazýva - tieto údaje v stĺpcoch E až O, takže tam nemusím pridávať nadpisy, dobre? Takže teraz sa A1 až D stane mojim vstupným rozsahom 100 000. A potom rozsah výstupu a rozsah kritérií - Rozsah výstupu je iba zoznamom požadovaných nadpisov. Takže tu uvediem výstupný rozsah a pole 3 nepotrebujeme, takže som 'Len to zložím nabok. Takže teraz, tento rozsah tu, A1 až C1 sa stáva mojím výstupným rozsahom, ktorý hovorí programu Excel, ktoré polia chcem zo vstupného rozsahu. A mohli by byť v inom poradí, ak by ste chceli zmeniť poradie vecí, napríklad ak chcem najskôr Pole 4 a potom Pole 1 a potom Pole 2. A opäť by to boli skutočné nadpisy ako číslo faktúry. Len neviem, ako vyzerajú Mortove údaje.
Rozsah kritérií je potom nadpis a hodnota, ktorú chcete. Povedzme, že som sa snažil získať čokoľvek v roku 2014. Toto sa stáva takým rozsahom kritérií. Dobre, len tu opatrnosť. Som v programe Excel 2016 a je možné vykonať pokročilý filter medzi dvoma listami v programe Excel 2016, ale ak sa vrátite späť a nepamätám si, aká je cesta späť, možno v roku 2003, nie som si istý. Niekedy v minulosti to bývalo tak, že ste nemohli robiť pokročilý filter z jedného hárka na druhý, takže ste sem museli prísť a pomenovať svoj vstupný rozsah. Tu by ste si museli vytvoriť meno. Moje meno alebo niečo podobné, dobre? A to by bol spôsob, akým by ste to dokázali vyriešiť, dobre. Nie nevyhnutne v programe Excel 2016, ale znovaNie som si istý, či to Mort bude spúšťať v starších verziách údajov.
Dobre, takže tu v Data prejdeme k Advanced Filter, dobre. A my ideme kopírovať na iné miesto, ktoré tam umožňuje náš výstupný rozsah. Dobre, takže rozsah zoznamu, kde sú údaje? Pretože som v programe Excel 2016, namiesto použitia rozsahu mien prejdem na Data, takže to je môj vstupný rozsah. Rozsah kritérií sú bunky tam a potom, kam ideme - výstup do, budú to iba tieto tri bunky. A potom klikneme na OK. Dobre, a BAM! Takto je to rýchle, rýchle. A čo keby sme chceli iný rok? Ak by sme chceli iný rok, výsledky by sme vymazali, vložili do roku 2015 a potom znova vykonali pokročilý filter, skopírovali na iné miesto, klikli na OK a sú tu všetky záznamy z roku 2015. Blesk rýchlo.
V poriadku, zatiaľ čo som fanúšikom pokročilého filtra v bežnom programe Excel, bol som veľkým fanúšikom pokročilého filtra vo VBA, v poriadku, pretože vďaka VBA je pokročilý filter naozaj, naozaj, skutočne jednoduchý. Dobre, tak tu napíšeme nejaký kód pre Morta za predpokladu, že Mortove údaje nemajú nadpisy a budeme musieť dočasne nadpisy pridať, dobre? Prejdem teda na VBA, Alt + F11 a spustíme to z pracovného hárka, ktorý obsahuje údaje. Takže: Dim WS As Worksheet, Set WS = ActiveSheet. A potom vložte riadok 1 a pridajte nejaké nadpisy: A, B, rok a D. Zistite, koľko riadkov údajov dnes máme, a potom začnite od bunky A1 prechádzajúcej 4 stĺpce až po posledný riadok, pomenujte to byť vstupný rozsah. Dobre, a toto je vlastne Mortov kód priamo tu, kde požiadal o InputBox,dostane rok, ktorý chcú, a potom sa opýta, aký rok alebo ako chcú pomenovať nový list, v poriadku. Takže skutočne vložíte hárok na mušku a potom I- dimenzuje nový hárok WSN ako hárok ActiveSheet. Takže viem, že WS je pôvodný list, WSN je nový list, ktorý bol práve pridaný. Na nový hárok vložte rozsah kritérií, takže v stĺpci E je tu nadpis, ktorý sa zhoduje s týmto nadpisom, a potom, ktorákoľvek odpoveď, ktorú nám dajú, bude v E2. Výstupný rozsah budú moje ďalšie tri nadpisy: A, B a D. A znova, ak ich zmeníte vy alebo Mort na skutočné nadpisy, čo je pravdepodobne lepšia vec ako A, B, D, a tiež zmeniť ich na skutočné nadpisy, dobre? Toto všetko je tu teda len trochu práce. Tento úžasný riadok kódu urobí celý pokročilý filter. Takžez InputRange urobíme AdvancedFilter, ideme kopírovať. To je náš výber filtra na mieste alebo kopírovanie. CriteriaRange je E1 až E2, CopyToRange je A až C. Jedinečné hodnoty - Nie, chceme všetky hodnoty. Dobre, ten jeden riadok kódu tam robí kúzlo cyklovania cez všetky záznamy alebo nahradzuje cyklovanie cez všetky záznamy alebo robenie polí. Potom skončíme, vyčistíme rozsah kritérií a potom odstránime riadok 1 späť v pôvodnom pracovnom hárku.Potom skončíme, vyčistíme rozsah kritérií a potom odstránime riadok 1 späť v pôvodnom pracovnom hárku.Potom skončíme, vyčistíme rozsah kritérií a potom odstránime riadok 1 späť v pôvodnom pracovnom hárku.
Dobre, tak sa vráťme späť k našim údajom. Uľahčíme to spustením, takže: Vložte, Tvar a zavolajte tento Filter, Domov, Stred, Stred, Väčší, Väčší, Väčší, kliknite pravým tlačidlom myši, Priradiť makro a priraďte ho k MacroForMort. Dobre, tak ideme na to. Ideme na test. Uvidíte, že sme v údajovom liste, kliknite na filter, aký rok chceme? Chceme rok 2015. Ako to chcem nazvať? Chcem to nazvať rok 2015, dobre. A BAM! Tam je hotovo. Takto rýchlo, tak rýchlo to je.
Pretože Mortove pôvodné údaje nemali nadpisy, možno by tieto údaje nemali mať nadpisy. Poďme teda na Alt + F11, práve tu chceme vyčistiť rozsah kritérií. Budeme tiež riadky (1). Odstrániť. Dobre, takže keď sa nabudúce o tom budeme zaoberať, zbaví sa týchto položiek. A poďme - Namiesto toho, aby sme to celé rýchlo rozbehli, sa poďme pozrieť tu s rokom 2014. Takže vyberiem jednu bunku v Data, Alt + F11, a chcem bežať len do bodu, kde urobíme pokročilý filter. Môžeme sa teda pozrieť a vidieť, čo tu celé makro robí. Takže klikneme na Spustiť a chcem získať rok 2014. 2014, v poriadku. Stlačením klávesu F8 teda chystáme pokročilý filter. Tu sa môžeme vrátiť späť do Excelu a zistiť, čo sa stalo.
Prvá vec, ktorá sa stala - Prvá vec, ktorá sa stala, je, že sme pridali nový dočasný riadok s nadpismi. Vložil som tento pracovný hárok, vytvoril rozsah kritérií s nadpisom a rokom zadania, vybral polia, ktoré chceme urobiť, a potom späť vo VBA, spustím ďalší riadok kódov, to je F8, ktorý robí pokročilý filter priamo tam . Je to neuveriteľne rýchle a uvidíte, že to nám teraz prinieslo všetky rekordy. Odtiaľ je to už len trochu vyčistiť, vymazať toto, vymazať toto. Vrátim sa k údajom a odstránim riadok 1 a bude dobre. Takže zvyšok toho nechám bežať, odstránim ten bod prerušenia, dobre? Takže je tu VBA. Pre mňa je to myslím najrýchlejšia cesta, najrýchlejšia cesta vpred.
Dobre, rekapitulácia epizódy: Pokročilý filter je pokročilejší ako bežný filter, pretože dokáže kopírovať do nového rozsahu. A teraz som to v tomto videu neukazoval, ale môžete zostaviť zložité kritériá, kde Pole 1 = A alebo Pole 2 = A. Bežný automatický filter to nedokáže a je rýchly. Mort sa pokúša spracovať 100 000 riadkov vo VBA pomocou poľa alebo cyklovania, ale použitie funkcií budovy Excel bude vždy rýchlejšie ako písanie vlastného kódu. Musíte definovať vstupný rozsah, rozsah kritérií, výstupný rozsah. Aspoň v jednom z nich vždy potrebujete vstupný rozsah, aj keď dnes používam oba. Pre vstupný rozsah jeden riadok nadpisov nad údajmi. Takže pridáme dočasný riadok nadpisov. Pre výstupný rozsah, rovnaké nadpisy, ktoré chcete extrahovať. Takže, viete, keby to bolo A, B,Year and D, we just put A, B and D as the output range. Pre rozsah kritérií nadpisy v riadku 1. Takže toto je pole, na ktorom chcem zostaviť kritériá, a toto je hodnota, ktorú hľadám. Komplikácie: Staršie verzie programu Excel neumožnia, aby sa výstupný rozsah nachádzal na inom hárku, takže váš kód bude potenciálne bežať vtedy. Pre vstupný rozsah chcete použiť pomenovaný rozsah, pretože z tohto hárka viete, že pomenovaný rozsah, aj keď je na inom hárku, hárok verí vetvám mien na aktuálnom hárku. To by umožnilo pracovať pokročilému filtru.Staršie verzie programu Excel neumožnia, aby sa výstupný rozsah nachádzal na inom hárku, takže váš kód bude potenciálne bežať vtedy. Pre vstupný rozsah chcete použiť pomenovaný rozsah, pretože z tohto hárka viete, že pomenovaný rozsah, aj keď je na inom hárku, hárok verí vetvám mien na aktuálnom hárku. To by umožnilo pracovať pokročilému filtru.Staršie verzie programu Excel neumožnia, aby sa výstupný rozsah nachádzal na inom hárku, takže váš kód bude potenciálne bežať vtedy. Pre vstupný rozsah chcete použiť pomenovaný rozsah, pretože z tohto hárka viete, že pomenovaný rozsah, aj keď je na inom hárku, hárok verí vetvám mien na aktuálnom hárku. To by umožnilo pracovať pokročilému filtru.
Dobre, dobre, tu to máte. Chcem poďakovať Mortovi za zaslanie tejto otázky. Chcem vám poďakovať, že ste sa zastavili. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2060.xlsm
