Коришћење напредног филтера у програму Екцел за решавање проблема Морта. Иако су редовни филтри постали моћнији, још увек има случајева да напредни филтер може да изведе неке трикове, а други не.
Погледајте видео
- Напредни филтер је „напреднији“ од уобичајеног филтера јер:
- 1) Може се копирати у нови опсег
- 2) Можете да направите сложеније критеријуме као што је Поље 1 = А или Поље 2 = А
- 3) Брзо је
- Морт покушава да обради 100.000 редова у ВБА прелиставањем записа или коришћењем низа
- Увек ће бити брже користити уграђене Екцел функције него писање сопственог кода.
- Потребан вам је опсег уноса, а затим опсег критеријума и / или опсег излаза
- За опсег уноса: један ред заглавља изнад података
- Додајте привремени ред за наслове
- За опсег излаза: ред наслова за колоне које желите да издвојите
- За опсег критеријума: наслови у реду 1, вредности које почињу у реду 2
- Компликација: Старије верзије програма Екцел не би дозволиле да се опсег излаза налази на другом листу
- Ако пишете макро који би се могао покренути у 2003, користите именовани опсег за опсег уноса да бисте га заобишли
Видео транскрипт
Научите Екцел из Подцаста, епизода 2060: Екцел напредни филтер
Хеј, добродошао назад на нетцаст, ја сам Билл Јелен. Данашње питање које је послао Морт. Морт, он има 100.000 редова података и занимају га колоне А, Б и Д где се колона Ц подудара са неком одређеном годином. Дакле, жели да особа уђе у годину дана, а затим добије колоне А, Б и Д. А Морт има неки ВБА где користи низове да би то урадио, а ја сам рекао, „Сачекајте мало, знате, напредни филтер би ово учинио много боље. " У реду, и сада само да прегледам, вратио сам се, погледао кроз своје видео записе. Већ дуго нисам покривао напредни филтер, па би требало да разговарамо о овоме.
Напредни филтер захтева опсег уноса, а затим најмање један од ових: опсег критеријума или опсег излаза. Иако ћемо данас користити и једно и друго. У реду, дакле опсег уноса су ваши подаци и изнад података морате да имате наслове. Дакле, Морт нема наслове и зато ћу привремено уметнути ред овде и понашати се као Поље 1. Морт зна који су његови подаци и могао би тамо да постави праве наслове. И не користимо ништа што се зове - ови подаци у колонама Е до О, па не морам тамо да додајем наслове, у реду? Дакле, сада од А1 до Д, 100000 постаје мој опсег уноса. А онда опсег излаза и опсег критеријума - Па, опсег излаза је само листа наслова која желите. Дакле, ставићу овде опсег излаза и не треба нам поље 3, паСамо ћу то скинути са стране. Дакле, овај опсег управо овде, од А1 до Ц1 постаје мој опсег излаза који Екцел-у говори која поља желим из улазног опсега. И, могли би да буду у другом редоследу ако желите да преуредите ствари, на пример ако прво желим поље 4, а затим поље 1 па поље 2. И опет, то би били стварни наслови попут броја фактуре. Само не знам како изгледају Мортови подаци.
А онда, опсег критеријума је наслов и вредност коју желите. Дакле, рецимо да сам покушавао да добијем било шта 2014. године. То постаје такав опсег критеријума. У реду, само реч опреза овде. Ја сам у програму Екцел 2016 и могуће је направити напредни филтер између два листа у програму Екцел 2016, али ако се вратите уназад, а не сећам се који је то пут, можда из 2003. године, нисам сигуран. У неком тренутку у прошлости, знало је да не можете да направите напредни филтер са једног листа на други, тако да ћете морати да дођете овде и именујете свој опсег уноса. Овде бисте морали да направите име. Моје име или нешто слично, у реду? И то би био начин на који бисте то могли извести, у реду. Не нужно у програму Екцел 2016, али опет,Нисам сигуран да ли ће Морт ово покретати у старијим верзијама података.
У реду, па назад овде у Дата, идемо на Напредни филтер, у реду. А ми ћемо копирати на другу локацију која тамо омогућава наш излазни опсег. У реду, па опсег листе, где су подаци? Будући да сам у програму Екцел 2016, прећи ћу на Дата, уместо да користим опсег имена - Дакле, то је мој опсег уноса. Опсег критеријума су оне ћелије управо тамо, а онда, тамо где ћемо ићи - излаз, биће само ове три ћелије тамо. А онда кликнемо ОК. У реду, и БАМ! То је тако брзо, брзо. А шта ако бисмо желели другу годину? Ако бисмо желели другу годину, избрисали бисмо резултате, ставили 2015. годину, а затим поново направили напредни филтер, копирали на другу локацију, кликнули на дугме У реду и ту су сви записи из 2015. године. Брзо муње.
У реду сада, иако сам фан напредног филтра у обичном Екцелу, био сам велики обожавалац напредног филтера у ВБА, у реду, јер ВБА чини унапред филтер заиста, заиста, заиста једноставним. У реду, па ћемо овде написати неки код за Морт, под претпоставком да Морт-ови подаци немају наслове и да ћемо морати привремено да додамо наслове, у реду? Дакле, прећи ћу на ВБА, Алт + Ф11 и покренућемо ово са радног листа који садржи податке. Дакле: Затамни ВС као радни лист, постави ВС = АцтивеСхеет. А затим уметните ред 1 и додајте само неке наслове: А, Б, Година и Д. Схватите колико редова података данас имамо, а затим почев од ћелије А1 која излази 4 колоне до крајњег реда, дајте јој име бити опсег уноса. У реду, и онда је ово заправо Мортов код овде, где је тражио ИнпутБок,добије годину коју желе и онда пита коју годину или како желе да именују нови лист, у реду. Дакле, уствари ће уметнути лист на Фли, а затим ћу димензионисати нови лист, ВСН, као АцтивеСхеет. Дакле, знам да је ВС оригинални лист, ВСН је нови лист који је управо додат. На нови лист ставите опсег критеријума тако да се у колони Е налази наслов који се овде подудара, а затим, који год одговор који су нам дали иде у Е2. Излазни опсег ће бити моја остала три наслова: А, Б и Д. И опет, ако ви или Морт промените ове у стварне наслове, што је вероватно боља ствар од А, Б, Д, а такође бисте промените их у стварне наслове, у реду? Дакле, све ово је само мало предрадње овде. Ова сјајна линија кода извршиће читав напредни филтер. Тако,из ИнпутРанге радимо АдванцедФилтер, копираћемо. То је наш избор филтера на месту или копије. Опсег критеријума је од Е1 до Е2, ЦопиТоРанге је од А до Ц. Јединствене вредности -Не, желимо све вредности. У реду, тај један ред кода тамо чини сву чаролију петље кроз све записе или замењује петљу кроз све записе или радећи низове. А онда смо готови, очистићемо опсег критеријума, а затим избрисати ред 1 на оригиналном радном листу.А онда смо готови, очистићемо опсег критеријума, а затим избрисати ред 1 на оригиналном радном листу.А онда смо готови, очистићемо опсег критеријума, а затим избрисати ред 1 на оригиналном радном листу.
Ок, вратимо се овде на наше податке. Олакшаћемо вам покретање овог, па: Уметните облик и позовите овај филтер, дом, центар, центар, већи, већи, већи, кликните десним тастером миша, додели макронаредбу и доделите га МацроФорМорт. У реду, идемо. Направићемо тест. Видите да смо на техничком листу, кликните на Филтер, коју годину желимо? Желимо 2015. Како да га назовем? Желим да то назовем 2015, у реду. И БАМ! Ето, готово је. Толико је брзо, толико је брзо ово.
Е сад, пошто Мортови оригинални подаци нису имали наслове, можда ови подаци не би требало да имају наслове. Дакле, идемо на Алт + Ф11, управо овде желимо да очистимо опсег критеријума. Такође ћемо редати (1) .Избриши. У реду, па следећи пут кад будемо били на овоме, ослободиће се тих наслова. И само да - Уместо да све покренемо брзо, погледајмо овде 2014. Дакле, изабраћу једну ћелију на Дата, Алт + Ф11, и желим да покренем само до тачке у којој радимо напредни филтер. Тако да можемо погледати и видети шта цео макро ради овде. Па ћемо кликнути на Рун, а ја желим да добијемо 2014. 2014, у реду. И тако, притисните Ф8, направићемо напредни филтер. Овде се можемо вратити на Екцел и видети шта се догодило.
Прва ствар која се догодила - Прва ствар која се десила је да смо додали нови привремени ред са заглављима. Уметнуо је овај радни лист, саградио опсег критеријума са насловом и коју годину уносе, одабрао поља која желимо да урадимо и онда назад у ВБА, покренућу следећи ред кодова, то је Ф8 који напредни филтер ради управо тамо . Невероватно је брз и видећете да нам је то заправо сада донело све рекорде. Одатле је само мало чишћења, избриши ово, избриши ово. Вратићу се на податке и избрисаћу ред 1 и бићемо спремни. Тако да ћу пустити да остатак тече, уклонити тачку прекида, у реду? Дакле, ту је ВБА. За мене је ово мислим најбржи пут, најбржи пут.
Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.
Па добро, ето ти. Желим да захвалим Морту што је послао то питање. Желим да вам захвалим што сте навратили. Видимо се следећи пут за још једно емитовање од.
Скини докуменат
Преузмите датотеку узорка овде: Подцаст2060.клсм
