Използване на разширен филтър в Excel за решаване на проблема на Морт. Въпреки че обикновените филтри са станали по-мощни, все още има моменти, при които Разширеният филтър може да направи някои трикове, които други не могат.
Гледам видео
- Разширеният филтър е по-"усъвършенстван" от обикновения филтър, защото:
- 1) Може да копира в нов диапазон
- 2) Можете да изградите по-сложни критерии като поле 1 = A или поле 2 = A
- 3) Бързо е
- Морт се опитва да обработи 100K редове във VBA, като прелиства записи или използва масив
- Винаги ще бъде по-бързо да използвате вградените функции на Excel, отколкото да пишете свой собствен код.
- Нуждаете се от обхват на въвеждане и след това обхват на критериите и / или обхват на изхода
- За входния диапазон: единичен ред заглавия над данните
- Добавете временен ред за заглавия
- За изходния диапазон: ред заглавия за колоните, които искате да извлечете
- За обхвата на критериите: заглавия в ред 1, стойности, започващи в ред 2
- Усложнение: По-старите версии на Excel не позволяват изходният диапазон да бъде на друг лист
- Ако пишете макрос, който може да се изпълни през 2003 г., използвайте имена за диапазон на въвеждане, за да заобиколите
Видео транскрипция
Научете Excel от Podcast, Епизод 2060: Разширен филтър на Excel
Хей, добре дошли отново в netcast, аз съм Бил Джелен. Днешният въпрос, изпратен от Морт. Морт, той има 100 000 реда данни и се интересува от колони A, B и D, където колона C съответства на определена година. Затова той иска човек да влезе в една година и след това да получи колони A, B и D. И Морт има някои VBA, където използва масиви, за да направи това, и аз казах: „Чакай малко, знаеш ли, разширеният филтър ще направи това a много по-добре. " Добре, а сега само за преглед, върнах се назад, погледнах назад през видеоклиповете си. Отдавна не съм обхващал разширен филтър, така че трябва да поговорим за това.
Разширеният филтър изисква обхват на въвеждане и след това поне един от тях: обхват на критериите или изходен диапазон. Въпреки че днес ще използваме и двете. Добре, така че обхватът на въвеждане е вашите данни и трябва да имате заглавия над данните. Така че, Морт няма заглавия и затова ще вмъкна временно ред тук и просто ще направя като Поле 1. Морт знае какви са неговите данни и така може да постави истински заглавия там. И ние не използваме нищо, което се нарича - тези данни в колони E до O, така че не трябва да добавям заглавия там, нали? И така, от A1 до D, 100000 се превръща в моя входен диапазон. И след това обхватът на изхода и обхватът на критериите - Е, изходният обхват е просто списък на заглавията, които искате. Така че ще поставя изходния диапазон тук и нямаме нужда от поле 3, така чепросто ще сваля това отстрани. Така че сега, този диапазон тук, от A1 до C1 се превръща в моя изходен диапазон, който казва на Excel кои полета искам от входния диапазон. И те биха могли да бъдат в различен ред, ако искате да пренаредите нещата, например ако първо искам поле 4, а след това поле 1, след това поле 2. И отново това биха били реални заглавия като номер на фактура. Просто не знам как изглеждат данните на Морт.
И тогава, обхватът на критериите е заглавие и каква стойност искате. Така че, да кажем, че се опитвах да получа нещо през 2014 г. Това се превръща в диапазон на критериите така. Добре, само една дума за предпазливост тук. Аз съм в Excel 2016 и е възможно да направя усъвършенстван филтър между два листа в Excel 2016, но ако се върнете назад и не помня какъв е обратният път, може би 2003, не съм сигурен. В някакъв момент в миналото се е случвало, че не можете да направите разширен филтър от един лист на друг, така че ще трябва да дойдете тук и да назовете своя диапазон на въвеждане. Тук ще трябва да създадете име. MyName или нещо подобно, нали? И това би бил начинът, по който ще можете да го направите, добре. Не е задължително в Excel 2016, но отново, аз "Не съм сигурен дали Морт ще използва това в по-старите версии на данните.
Добре, така че тук, в Data, отиваме към Advanced Filter, добре. И ние ще копираме на друго място, което позволява нашия изходен диапазон там. Добре, така че обхватът на списъка, къде са данните? Тъй като съм в Excel 2016, ще отида да посоча данните, вместо да използвам диапазона от имена - Така че това е моят диапазон на въвеждане. Обхватът на критериите е тези клетки точно там и след това, където ще отидем - изход към, това просто ще бъдат тези три клетки там. И тогава щракваме OK. Добре, и БАМ! Това е колко бързо, бързо е. И какво, ако искахме различна година? Ако искахме различна година, щяхме да изтрием резултатите, поставихме през 2015 г. и след това отново направихме разширен филтър, копирахме на друго място, щракнахме OK и там са всички записи от 2015 г. Светкавично бързо.
Добре сега, макар че съм фен на усъвършенствания филтър в обикновения Excel, бях масивен фен на усъвършенствания филтър във VBA, добре, защото VBA прави предварително филтъра наистина, наистина, наистина опростен. Добре, така че ще напишем някакъв код тук за Морт, ако приемем, че данните на Морт нямат заглавия и ще трябва временно да добавим заглавията, нали? Така че, ще премина към VBA, Alt + F11 и ще стартираме това от работния лист, който съдържа данните. И така: Затъмнете WS като работен лист, задайте WS = ActiveSheet. И след това вмъкнете ред 1 и просто добавете някои заглавия: A, B, Year и D. Разберете колко реда данни имаме днес и след това започвайки от клетка A1, излизайки 4 колони надолу до последния ред, наречете това на да бъде обхват на въвеждане. Добре, и това всъщност е кодът на Морт тук, където той поиска InputBox,получава годината, която искат и след това той пита коя година или как искат да назоват новия лист, добре. Така че всъщност ще вмъкне лист в Fly и след това ще оразмеря нов лист, WSN, като ActiveSheet. Така че знам, че WS е оригиналният лист, WSN е новият лист, който току-що е добавен. На новия лист поставете диапазона на критериите, така че под колона E има заглавие, което съответства на това заглавие тук, и след това, който и отговор да ни даде, отива в E2. Изходният диапазон ще бъде другите ми три заглавия: A, B и D. И отново, ако вие или Mort промените тези на реални заглавия, което вероятно е по-добре да направите от A, B, D, и вие също да ги промените в реални заглавия, нали? Така че всичко това е само малко предварителна работа тук. Този страхотен ред код ще направи целия разширен филтър. Така,от InputRange правим AdvancedFilter, ще копираме. Това е нашият филтър за избор на място или копиране. CriteriaRange е от E1 до E2, CopyToRange е от A до C. Уникални стойности -Не, искаме всички стойности. Добре, че един ред код там прави цялата магия на цикъла през всички записи или замества цикъла през всички записи или прави масивите. И тогава приключихме, ще изчистим обхвата на критериите и след това ще изтрием ред 1 обратно в оригиналния работен лист.И тогава приключихме, ще изчистим обхвата на критериите и след това ще изтрием ред 1 обратно в оригиналния работен лист.И тогава приключихме, ще изчистим обхвата на критериите и след това ще изтрием ред 1 обратно в оригиналния работен лист.
Добре, така че нека се върнем тук към нашите данни. Ще улесним стартирането на това, така че: Вмъкнете, Форма и извикайте този Филтър, Начало, Център, Център, По-голям, По-голям, По-голям, щракнете с десния бутон, Присвояване на макрос и го присвойте на MacroForMort. Добре, така че започваме. Ще направим тест. Вижте, че сме на листа с данни, щракнете върху филтъра, коя година искаме? Искаме 2015 г. Как да го нарека? Искам да го нарека 2015, добре. И БАМ! Ето го направено. Ето колко бързо, толкова бързо е това.
Сега, тъй като първоначалните данни на Морт нямаха заглавия, може би тези данни не трябва да имат заглавия. Така че нека отидем Alt + F11, точно тук искаме да изчистим обхвата на критериите. Ние също ще редове (1). Изтриване. Добре, така че сега следващия път, когато бяхме на това, ще се отърве от тези заглавия. И нека просто - Вместо да стартираме цялото нещо бързо, нека погледнем тук с 2014 г. Така че ще избера една клетка в Data, Alt + F11 и искам да стартирам до точката, в която правим усъвършенстван филтър. Така че можем да погледнем и видим какво прави целият макрос тук. Така че ще щракнем „Изпълни“ и искам да получа 2014 г., добре. И така, натиснете F8, ние ще направим разширения филтър. Тук можем да се върнем към Excel и да видим какво се е случило.
Първото нещо, което се случи - Първото, което се случи, е, че добавихме нов временен ред със заглавията. Вмъкна този работен лист, изгради диапазон от критерии със заглавие и коя година въведоха, избра полетата, които искаме да направим и след това обратно във VBA, ще пусна следващия ред кодове, това е F8, който прави разширения филтър точно там . Това е невероятно бързо и ще видите, че това всъщност ни донесе всички записи. Оттам нататък е само малко почистване, изтрийте това, изтрийте това. Ще се върна към данните и ще изтрия ред 1 и ще бъде добре да тръгнем. Така че просто ще оставя останалата част от тази работа да премахне тази точка на прекъсване, нали? Така че има VBA. За мен това е, мисля, най-бързият път, най-бързият път.
Добре, резюме на епизода: Разширеният филтър е по-усъвършенстван от обикновения филтър, тъй като може да копира в нов диапазон. И сега, не го показах в това видео, но можете да изградите сложни критерии, където поле 1 = A или поле 2 = A. Редовният автоматичен филтър не може да направи това и е бърз. Морт се опитва да обработи 100 000 реда в VBA с помощта на масив или чрез цикъл, но винаги ще бъде по-бързо да използвате функции за изграждане на Excel, отколкото да пишете свой собствен код. Трябва да дефинирате обхват на въвеждане, обхват на критериите, изходен обхват. Винаги се нуждаете от входен диапазон в поне един от тях, въпреки че днес използвам и двете. За входния диапазон, един ред заглавия над данните. Така че ще добавим временен ред от заглавия. За изходния диапазон, същите заглавия, които искате да извлечете, добре. Така че, знаете ли, ако беше A, B,Година и D, просто ще поставим A, B и D като обхват на изхода. За диапазона от критерии, заглавия в ред 1. И така, това е полето, върху което искам да изградя критерии и това е стойността, която търся. Усложнения: По-старите версии на Excel няма да позволят изходният диапазон да бъде на друг лист, така че потенциално вашият код ще се изпълни тогава. Искате да използвате наименуван диапазон за входния диапазон, защото от този лист, знаете, наименуваният диапазон, въпреки че е на друг лист, листът вярва, че имената се разклонява на текущия лист. Така че това ще позволи на усъвършенствания филтър да работи.По-старите версии на Excel няма да позволят изходният диапазон да бъде на друг лист, така че потенциално вашият код ще се изпълни тогава. Искате да използвате именен диапазон за входния диапазон, защото от този лист, знаете, наименуваният диапазон, въпреки че е на друг лист, листът вярва, че имената се разклонява на текущия лист. Така че това ще позволи на усъвършенствания филтър да работи.По-старите версии на Excel няма да позволят изходният диапазон да бъде на друг лист, така че потенциално вашият код ще се изпълни тогава. Искате да използвате наименуван диапазон за входния диапазон, защото от този лист, знаете, наименуваният диапазон, въпреки че е на друг лист, листът вярва, че имената се разклонява на текущия лист. Така че това ще позволи на усъвършенствания филтър да работи.
Добре, добре, ето го. Искам да благодаря на Морт за изпращането на този въпрос. Искам да ви благодаря, че се отбихте. Ще се видим следващия път за поредното излъчване от.
Свали файл
Изтеглете примерния файл тук: Podcast2060.xlsm
