A Speciális szűrő használata az Excel programban Mort problémájának megoldására. Bár a szokásos szűrők egyre erősebbek, még mindig vannak olyan esetek, amikor a Speciális szűrő olyan trükköket tud végrehajtani, amelyeket mások nem.
Videót nézni
- A Speciális szűrő „fejlettebb”, mint a szokásos szűrő, mert:
- 1) Új tartományba másolhatja
- 2) Összetettebb feltételeket hozhat létre, például 1. mező = A vagy 2. mező = A
- 3) Gyors
- A Mort 100 000 sort próbál feldolgozni a VBA-ban a rekordok közötti hurkolással vagy egy tömb használatával
- Mindig gyorsabb lesz a beépített Excel-szolgáltatások használata, mint a saját kód írása.
- Szüksége van egy bemeneti tartományra, majd egy kritériumtartományra és / vagy egy kimeneti tartományra
- A beviteli tartományhoz: egyetlen sor fejléc az adatok fölött
- Adjon meg egy ideiglenes sort a címsorokhoz
- A kimeneti tartományhoz: a kibontandó oszlopok címsorainak sora
- A kritériumtartományhoz: címsorok az 1. sorban, az értékek a 2. sortól kezdődnek
- Komplikáció: Az Excel régebbi verziói nem engedik meg, hogy a kimeneti tartomány egy másik lapon legyen
- Ha esetleg egy 2003-ban futtatható makrót ír, használjon megnevezett tartományt a bemeneti tartomány megkerülésére
Videó átirat
Ismerje meg az Excel programot a Podcast, Episode 2060: Excel Advanced Filter alkalmazásból
Hé, üdvözlöm újra a netcasten, Bill Jelen vagyok. A mai kérdést Mort küldte. Mort, 100 000 adatsora van, és érdekli az A, B és D oszlop, ahol a C oszlop egy adott évnek felel meg. Tehát azt szeretné, ha egy személy beírna egy évet, majd megkapná az A, B és D oszlopokat. És Mortnak van egy kis VBA-ja, ahol tömböket használ erre, és én azt mondtam: sokkal jobb. ” Rendben, és most csak áttekintésképpen visszatértem, visszanéztem a videóimon. Régóta nem foglalkozom a speciális szűrővel, ezért beszélnünk kellene erről.
A speciális szűrő megköveteli a bemeneti tartományt, majd ezek közül legalább egyet: kritériumtartományt vagy kimeneti tartományt. Bár ma mindkettőt használni fogjuk. Rendben, tehát a beviteli tartomány az Ön adata, és az adatok fölött fejlécekkel kell rendelkeznie. Tehát Mortnak nincsenek címsorai, ezért ideiglenesen beillesztek egy sort ide, és csak úgy csinálom, mint az 1. mezőt. Mort tudja, mi az adatai, és így valódi fejléceket tehet oda. És az E – O oszlopokban nem használunk semmit, ezeket nevezzük ezeknek az adatoknak, tehát nem kell oda címsorokat adnom, rendben? Tehát az A1-től D-ig 100000 lesz a bemeneti tartományom. Ezután a kimeneti tartomány és a kritériumtartomány - Nos, a kimeneti tartomány csak a kívánt címsorok listája. Tehát ide fogom tenni a kimeneti tartományt, és nincs szükségünk a 3. mezőre, ezértCsak leveszem oldalra. Tehát most ez a tartomány itt, az A1-től C1-ig lesz a kimeneti tartományom, amely megmondja az Excelnek, hogy mely mezőket szeretném a bemeneti tartományból. És más sorrendben is lehetnek, ha át akarja rendezni a dolgokat, például ha először a 4., majd az 1. mezőt, majd a 2. mezőt akarom. És ezek ismét valódi címsorok lennének, például a számlaszám. Csak nem tudom, hogy néznek ki Mort adatai.
És akkor a kritériumtartomány egy címsor, és milyen értéket szeretne. Tehát tegyük fel, hogy 2014-ben bármit megpróbáltam megszerezni. Ez válik a kritériumok közé. Rendben, itt csak egy figyelmeztetés. Az Excel 2016-ban vagyok, és az Excel 2016-ban két lap között lehet speciális szűrőt csinálni, de ha visszafelé megyünk, és nem emlékszem, mi a visszaút, talán 2003, akkor nem vagyok biztos benne. Valamikor a múltban szokott lenni, hogy nem lehet speciális szűrőt készíteni egyik lapról a másikra, így ide kell jönnie, és meg kell neveznie a beviteli tartományt. Itt kellene létrehoznia egy nevet. MyName vagy valami hasonló, rendben? És így lehetne ezt lehúzni, rendben. Nem feltétlenül az Excel 2016-ban, de ismétNem tudom, hogy Mort ezt futtatja-e az adatok régebbi verzióiban.
Rendben, szóval itt, a Data-nál, a Haladó szűrőhöz megyünk, rendben. És másolni fogunk egy másik helyre, amely lehetővé teszi a kimeneti tartományunkat ott. Rendben, tehát a listatartomány, hol vannak az adatok? Mivel az Excel 2016-ban vagyok, a névtartomány használata helyett az adatokra fogok mutatni - ez az én beviteli tartományom. A kritériumtartomány az, hogy azok a cellák ott vannak-e, és akkor, hová fogunk menni - kimenet, csak ez a három cella lesz ott. Ezután kattintson az OK gombra. Rendben, és BAM! Ilyen gyorsan, gyorsan. És mi lenne, ha más évet szeretnénk? Ha egy másik évet szeretnénk, akkor töröljük az eredményeket, 2015-be tesszük, majd újra elvégezünk egy speciális szűrőt, Másolunk egy másik helyre, kattintsunk az OK gombra, és ott vannak a 2015-ös rekordok. Villámgyorsan.
Rendben, miközben rajongok a fejlett szűrőkért a szokásos Excel-ben, nagy rajongója voltam a fejlett szűrőknek a VBA-ban, rendben, mert a VBA az előszűrőt nagyon-nagyon, nagyon egyszerűvé teszi. Rendben, ezért írunk ide néhány kódot a Mort számára, feltéve, hogy Mort adatainak nincsenek címsorai, és ideiglenesen hozzá kell adnunk a címsorokat, rendben? Tehát áttérek a VBA-ra, az Alt + F11-re, és ezt lefuttatjuk az adatokat tartalmazó munkalapról. Tehát: Dim WS mint munkalap, állítsa be a WS = ActiveSheet elemet. Ezután illessze be az 1. sort, és adjon hozzá néhány címsort: A, B, Év és D. Kiderítse ki, hogy hány adatsorunk van ma, majd az A1 cellából indulva, 4 oszlopból lefelé haladva az utolsó sorig, nevezze meg legyen bemeneti tartomány. Rendben, és akkor ez valójában Mort kódja, itt kérte az InputBoxot,megkapja a kívánt évet, majd megkérdezi, hogy melyik évet vagy mit akarnak elnevezni az új lapnak, rendben. Tehát ténylegesen beilleszt egy lapot a Fly-be, majd egy új lapot, a WSN-t méretezem ActiveSheet-ként. Tehát tudom, hogy a WS az eredeti lap, a WSN az új lap, amelyet most adtunk hozzá. Az új lapra tegye a kritériumtartományt, így az E oszlop alatt ott van a címsor, amely megfelel ennek a címsornak, majd bármelyik választ adta nekünk, az E2-be megy. A kimeneti tartomány a másik három címsorom lesz: A, B és D. És még egyszer, ha te vagy Mort ezeket valódi fejlécekre változtatod, ami valószínűleg jobb dolog, mint A, B, D, és akkor is ezeket valódi fejlécekre változtatja, rendben? Tehát mindez csak egy kis előmunka itt. Ez az egyetlen félelmetes kódsor elvégzi a teljes haladó szűrőt. Így,az InputRange-ből egy AdvancedFiltert készítünk, másolni fogjuk. Ez a választásunk szerinti szűrő a helyén vagy másolata. A CriteriaRange E1-től E2-ig, a CopyToRange A-tól C-ig. Egyedi értékek -Nem, az összes értéket szeretnénk. Rendben, ez az egyetlen kódsor ott rejti az összes rekord varázslatos varázslatát, vagy felváltja az összes rekordot vagy a tömböket. És akkor készen vagyunk, kitisztítjuk a kritériumtartományt, majd töröljük vissza az 1. sort az eredeti munkalapon.És akkor készen vagyunk, kitisztítjuk a kritériumtartományt, majd töröljük vissza az 1. sort az eredeti munkalapon.És akkor készen vagyunk, töröljük a kritériumtartományt, majd töröljük az 1. sort az eredeti munkalapról.
Oké, térjünk vissza ide az adatainkra. Megkönnyítjük ennek futtatását, így: Insert, a Shape és hívja ezt a Filter, Home, Center, Center, Larger, Larger, Larger, kattintson jobb gombbal, Assign Macro, és rendelje hozzá a MacroForMort programhoz. Rendben, szóval megyünk. Teszünk egy tesztet. Nézze meg, hogy az adatlapon van-e, kattintson a Szűrő gombra, melyik évet akarjuk? 2015-et szeretnénk. Hogy is hívjam? 2015-nek akarom nevezni, rendben. És BAM! Ott kész. Ilyen gyors, ennyire gyors ez.
Most, mivel Mort eredeti adatai nem voltak fejlécek, lehet, hogy ezeknek az adatoknak nem kellett volna címsorokkal rendelkezniük. Tehát menjünk az Alt + F11-re, itt szeretnénk törölni a kritériumtartományt. Mi is sorokat (1). Töröl. Rendben, így most, amikor legközelebb ezen voltunk, megszabadul ezektől a címsoroktól. Na és csak - ahelyett, hogy az egészet gyorsan lefuttatnánk, nézzük meg itt a 2014-et. Tehát kiválasztok egy cellát az adatokon, az Alt + F11-et, és csak addig a pontig szeretnék lefutni, ahol a speciális szűrő. Tehát megnézhetjük, hogy mit csinál itt az egész makró. Tehát a Futtatás gombra kattintunk, és szeretném megkapni a 2014-es évet. Tehát nyomja meg az F8 billentyűt, és mindjárt elvégezzük a speciális szűrőt. Itt visszagurulhatunk az Excelbe, és megnézhetjük, mi történt.
Az első dolog, ami történt - Most először az történt, hogy felvettünk egy új ideiglenes sort a címsorokkal. Beillesztette ezt a munkalapot, felépített egy kritériumtartományt egy címsorral és azzal, hogy melyik évet adják meg, kiválasztotta azokat a mezőket, amelyeket meg akarunk csinálni, majd visszatértem a VBA-ba. Futtatom a következő kódsort, ez az F8, amely ott hajtja végre a speciális szűrőt . Hihetetlenül gyors, és látni fogja, hogy ez tulajdonképpen most hozta nekünk az összes lemezt. Onnan már csak egy kis takarítás, törölje ezt, törölje ezt. Visszatérek az adatokhoz és törlöm az 1. sort, és jók leszünk. Szóval csak hagyom futni a maradékot, eltávolítom azt a töréspontot, rendben? Tehát ott van a VBA. Számomra ez szerintem a leggyorsabb, leggyorsabb út.
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.
Rendben, nos, itt van. Szeretném megköszönni Mortnak, hogy beküldte a kérdést. Szeretném megköszönni, hogy betértél. Legközelebb találkozunk egy újabb netcast-lal.
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2060.xlsm
