Využití pokročilého filtru v aplikaci Excel k vyřešení Mortova problému. I když se běžné filtry staly výkonnějšími, stále existují chvíle, kdy pokročilý filtr dokáže některé triky, které ostatní ne.
Sledovat video
- Rozšířený filtr je „pokročilejší“ než běžný filtr, protože:
- 1) Může kopírovat do nového rozsahu
- 2) Můžete vytvořit složitější kritéria, jako je pole 1 = A nebo pole 2 = A
- 3) Je to rychlé
- Mort se pokouší zpracovat 100 tis. Řádků ve VBA opakováním záznamů nebo pomocí pole
- Vždy bude rychlejší používat vestavěné funkce Excelu než psaní vlastního kódu.
- Potřebujete vstupní rozsah a poté rozsah kritérií a / nebo výstupní rozsah
- Pro vstupní rozsah: jeden řádek nadpisů nad daty
- Přidejte dočasný řádek pro nadpisy
- Pro výstupní rozsah: řádek záhlaví sloupců, které chcete extrahovat
- Pro rozsah kritérií: nadpisy v řádku 1, hodnoty začínající v řádku 2
- Komplikace: Starší verze aplikace Excel neumožňují, aby byl výstupní rozsah na jiném listu
- Pokud píšete makro, které by mohlo být spuštěno v roce 2003, použijte k obcházení pojmenovaný rozsah pro vstupní rozsah
Přepis videa
Naučte se Excel z Podcastu, epizoda 2060: Pokročilý filtr aplikace Excel
Hej, vítej zpět na netcastu, jsem Bill Jelen. Dnešní otázku poslal Mort. Mort, má 100 000 řádků dat a zajímá se o sloupce A, B a D, kde sloupec C odpovídá určitému roku. Takže chce, aby člověk vstoupil do roku a poté dostal sloupce A, B a D. A Mort má nějaké VBA, kde k tomu používá pole a já jsem řekl: „Počkejte chvíli, víte, pokročilý filtr by to udělal mnohem lépe. “ Dobře, a teď, abych to jen zkontroloval, jsem se vrátil, podíval jsem se zpět na svá videa. Pokročilý filtr jsem dlouho nepokrýval, takže bychom si o tom měli promluvit.
Pokročilý filtr vyžaduje vstupní rozsah a poté alespoň jeden z nich: rozsah kritérií nebo výstupní rozsah. I když dnes použijeme obojí. Dobře, takže vstupní rozsah jsou vaše data a musíte mít nad daty nadpisy. Mort tedy nemá nadpisy, a proto sem dočasně vložím řádek a udělám to stejně jako Pole 1. Mort ví, jaké jsou jeho údaje, a tak by tam mohl dát skutečné nadpisy. A nepoužíváme nic, čemu se říká tato data ve sloupcích E až O, takže tam nemusím přidávat nadpisy, dobře? Takže nyní se A1 až D stane 100 000 mým vstupním rozsahem. A pak rozsah výstupu a rozsah kritérií - Rozsah výstupu je jen seznam nadpisů, které chcete. Takže sem uvedu výstupní rozsah a nepotřebujeme Field 3, takže já 'Prostě to sundám na stranu. Takže teď, tento rozsah tady, A1 až C1 se stane mým výstupním rozsahem, který řekne aplikaci Excel, která pole chci ze vstupního rozsahu. Mohli by být v jiném pořadí, pokud byste chtěli změnit pořadí věcí, jako kdybych chtěl nejprve pole 4 a pak pole 1 a potom pole 2. A opět by to byly skutečné nadpisy jako číslo faktury. Jen nevím, jak vypadají Mortova data.
A pak je rozsah kritérií nadpisem a jakou hodnotu chcete. Řekněme tedy, že jsem se snažil získat cokoli v roce 2014. Toto se stalo takovým rozsahem kritérií. Dobře, zde jen opatrnost. Jsem v aplikaci Excel 2016 a je možné provést pokročilý filtr mezi dvěma listy v aplikaci Excel 2016, ale pokud se vrátíte zpět a nepamatuji si, jaká je zpět, možná 2003, nejsem si jistý. V určitém okamžiku v minulosti to bývalo tak, že jste nemohli udělat pokročilý filtr z jednoho listu na jiný, takže jste museli přijít sem a pojmenovat svůj vstupní rozsah. Zde byste si museli vytvořit jméno. Moje jméno nebo něco takového, dobře? A to by byl způsob, kterým bys to dokázal vytáhnout, dobře. Ne nutně v aplikaci Excel 2016, ale znovuNejsem si jistý, jestli to Mort spustí ve starších verzích dat.
Dobře, takže tady v Data přejdeme na Advanced Filter, dobře. A my se chystáme zkopírovat do jiného umístění, které tam umožňuje náš výstupní rozsah. Dobře, takže rozsah seznamu, kde jsou data? Protože jsem v aplikaci Excel 2016, místo použití rozsahu jmen přejdu na Data, takže to je můj vstupní rozsah. Rozsah kritérií jsou ty buňky právě tam a potom, kam se chystáme - výstup, budou to jen tyto tři buňky. A pak klikneme na OK. Dobře, a BAM! Tak to je rychlé, rychlé. A co kdybychom chtěli jiný rok? Pokud bychom chtěli jiný rok, odstranili bychom výsledky, vložili je do roku 2015 a poté znovu provedli pokročilý filtr, zkopírovat do jiného umístění, kliknout na OK a jsou zde všechny záznamy z roku 2015. Blesk rychle.
Dobře, teď, když jsem fanouškem pokročilého filtru v běžném Excelu, byl jsem velkým fanouškem pokročilého filtru ve VBA, dobře, protože VBA dělá předem filtr opravdu, opravdu, opravdu jednoduchý. Dobře, tak zde napíšeme nějaký kód pro Morta, za předpokladu, že Mortova data nemají žádné nadpisy a budeme muset dočasně přidat nadpisy, ano? Takže přepnu na VBA, Alt + F11 a spustíme to z listu, který obsahuje data. Takže: Dim WS As Worksheet, Set WS = ActiveSheet. A pak vložte řádek 1 a přidejte jen několik záhlaví: A, B, Year a D. Zjistěte, kolik řádků dat dnes máme, a pak počínaje buňkou A1 procházením 4 sloupce dolů do posledního řádku, pojmenujte být vstupní rozsah. Dobře, a tohle je vlastně Mortův kód právě tady, kde žádal o InputBox,dostane rok, který chtějí, a pak se zeptá, jaký rok nebo co chtějí pojmenovat nový list, dobře. Ve skutečnosti tedy vložíte list na Fly a pak I- Dimension nový list, WSN, jako list ActiveSheet. Takže vím, že WS je původní list, WSN je nový list, který byl právě přidán. Na nový list vložte rozsah kritérií, takže pod sloupcem E je zde nadpis, který odpovídá tomuto nadpisu, a pak, kterákoli odpověď, kterou nám dali, jde do E2. Výstupní rozsah bude moje další tři nadpisy: A, B a D. A znovu, pokud vy nebo Mort změníte tyto na skutečné nadpisy, což je pravděpodobně lepší věc než A, B, D, a vy byste také změnit je na skutečné nadpisy, dobře? To vše je zde tedy jen trochu práce. Tento úžasný řádek kódu provede celý pokročilý filtr. Tak,z InputRange uděláme AdvancedFilter, budeme kopírovat. To je náš výběr filtr na místě nebo kopírovat. CriteriaRange je E1 až E2, CopyToRange je A až C. Jedinečné hodnoty - Ne, chceme všechny hodnoty. Dobře, ten jeden řádek kódu dělá všechno kouzlo smyčkování skrz všechny záznamy nebo nahrazuje smyčkování skrz všechny záznamy nebo dělení polí. A pak jsme hotovi, vymažeme rozsah kritérií a potom odstraníme řádek 1 zpět na původní list.A pak jsme hotovi, vymažeme rozsah kritérií a potom odstraníme řádek 1 zpět na původní list.A pak jsme hotovi, vymažeme rozsah kritérií a potom odstraníme řádek 1 zpět na původní list.
Dobře, tak pojďme přepnout zpět na naše data. Umožníme to snadno spustit, takže: Vložit, Tvar a zavolat tento Filtr, Domů, Střed, Střed, Větší, Větší, Větší, klepněte pravým tlačítkem, Přiřadit makro a přiřadit jej k MacroForMort. Dobře, tak jdeme na to. Budeme dělat test. Podívejte se, že jsme v datovém listu, klikněte na Filtr, jaký rok chceme? Chceme rok 2015. Jak to mám nazvat? Chci to nazvat rok 2015, dobře. A BAM! Tam je hotovo. Tak rychle, tak rychle to je.
Nyní, protože Mortova původní data neměla nadpisy, možná by tato data neměla mít nadpisy. Pojďme tedy Alt + F11, právě tady chceme vymazat rozsah kritérií. Budeme také řádky (1). Odstranit. Dobře, takže teď, až se na to podíváme příště, zbaví se těchto nadpisů. A pojďme - Místo toho, abychom to celé rychle spustili, pojďme se zde podívat na rok 2014. Takže vyberu jednu buňku na Data, Alt + F11, a chci běžet jen do bodu, kde uděláme pokročilý filtr. Můžeme se tedy podívat a zjistit, co tady celé makro dělá. Takže klikneme na Spustit a chci získat rok 2014. 2014, dobře. Stisknutím klávesy F8 se chystáme udělat pokročilý filtr. Zde se můžeme vrátit zpět do Excelu a zjistit, co se stalo.
První věc, která se stala - Teď, první věc, která se stala, jsme přidali nový dočasný řádek s nadpisy. Vložil tento list, vytvořil rozsah kritérií s nadpisem a rokem, který zadali, vybral pole, která chceme udělat, a pak zpět ve VBA, spustím další řádek kódů, to je F8, který dělá pokročilý filtr právě tam . Je to neuvěřitelně rychlé a uvidíte, že to nám nyní přineslo všechny rekordy. Odtamtud je to jen trochu vyčištění, smazat toto, smazat toto. Vrátím se k datům a odstraním řádek 1 a budeme rádi. Takže zbytek toho nechám běžet, odstraním ten zlom, dobře? Takže existuje VBA. Pro mě je to myslím nejrychlejší cesta, nejrychlejší cesta.
Dobře, rekapitulace epizody: Pokročilý filtr je pokročilejší než běžný filtr, protože může kopírovat do nového rozsahu. A teď jsem to v tomto videu neukazoval, ale můžete vytvořit složitá kritéria, kde Pole 1 = A nebo Pole 2 = A. Pravidelný automatický filtr to nedokáže a je rychlý. Mort se pokouší zpracovat 100 000 řádků ve VBA pomocí pole nebo smyčkování, ale vždy bude rychlejší používat funkce budovy Excel než psaní vlastního kódu. Musíte definovat vstupní rozsah, rozsah kritérií, výstupní rozsah. Minimálně v jednom z nich vždy potřebujete vstupní rozsah, i když dnes používám oba. Pro vstupní rozsah jeden řádek nadpisů nad daty. Přidáme tedy dočasnou řadu nadpisů. Pro výstupní rozsah, stejné záhlaví, které chcete extrahovat, dobře. Takže, víte, kdyby to bylo A, B,Year and D, we just put A, B and D as the output range. Pro rozsah kritérií nadpisy v řádku 1. Takže toto je pole, na kterém chci stavět kritéria, a toto je hodnota, kterou hledám. Komplikace: Starší verze aplikace Excel neumožní, aby byl výstupní rozsah na jiném listu, takže potenciálně bude váš kód spuštěn tehdy. Chcete použít pojmenovaný rozsah pro vstupní rozsah, protože z tohoto listu, víte, pojmenovaný rozsah, i když je na jiném listu, list věří, že větve názvu na aktuálním listu. To by umožnilo pracovat pokročilému filtru.Starší verze aplikace Excel neumožní, aby se výstupní rozsah nacházel na jiném listu, takže potenciálně bude váš kód spuštěn tehdy. Chcete použít pojmenovaný rozsah pro vstupní rozsah, protože z tohoto listu, víte, pojmenovaný rozsah, i když je na jiném listu, list věří, že větve názvu na aktuálním listu. To by umožnilo pracovat pokročilému filtru.Starší verze aplikace Excel neumožní, aby se výstupní rozsah nacházel na jiném listu, takže potenciálně bude váš kód spuštěn tehdy. Chcete použít pojmenovaný rozsah pro vstupní rozsah, protože z tohoto listu, víte, pojmenovaný rozsah, i když je na jiném listu, list věří, že větve názvu na aktuálním listu. To by umožnilo pracovat pokročilému filtru.
Dobře, dobře, tady to máte. Chci poděkovat Mortovi za zaslání této otázky. Chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2060.xlsm
