Adatok rendezése Excel VBA -ban
Az Excel kiváló eszközzel rendelkezik a táblázatos adatok rendezéséhez az Excel kezelőfelületén található szalag segítségével, és valamikor valószínűleg ezt a funkciót szeretné használni a VBA -kódban. Szerencsére ezt nagyon könnyű megtenni.
A kezelőfelület párbeszédpaneljét az Excel szalag "Adatok" lapjának "Rendezés és szűrés" csoportjában található "Rendezés" ikonra kattintva találja meg. Először ki kell választania a táblázatos adatok körét.
Az Alt-A-S-S billentyűkombinációval megjelenítheti az egyéni rendezés párbeszédpaneljét is.
A rendezési módszer jelentősen javult az Excel későbbi verzióiban. A rendezés korábban három szintre korlátozódott, de most már annyi szintet adhat meg, amennyire szüksége van, és ez a VBA -n belül is érvényes.
Az Excel Rendezés párbeszédpanelen kínált összes rendezési funkciót beépítheti VBA -kódjába. Az Excel rendezési funkciója gyors és gyorsabb, mint bármi, amit saját maga írhat a VBA -ba, ezért használja ki a funkció előnyeit.
Vegye figyelembe, hogy amikor rendezést végez a VBA-ban, a rendezési paraméterek változatlanok maradnak a kezelőfelület rendezése párbeszédpanelen. A munkafüzet mentésekor is mentésre kerülnek.
Ha a felhasználó a táblázatos adatok ugyanazt a tartományát választja, és rákattint a Rendezés ikonra, látni fogja az összes olyan paramétert, amelyet a VBA -kód megadott. Ha egyfajta saját tervezést akarnak készíteni, akkor először törölniük kell az összes rendezési szintjét, ami nagyon bosszantó lesz számukra.
Továbbá, ha nem módosítja a kód paramétereit, és az alapértelmezett értékekre támaszkodik, előfordulhat, hogy a felhasználó olyan változtatásokat hajtott végre, amelyek tükröződnek a VBA -rendezésben, és váratlan eredményeket adhatnak, amelyeket nagyon nehéz lehet hibakeresni .
Szerencsére létezik egy egyértelmű módszer a VBA-ban az összes rendezési paraméter újbóli beállítására, hogy a felhasználó láthassa a tiszta rendezés párbeszédpanelt
| 1 | Munkalapok ("Sheet1"). Rendezés.SortFields.Clear |
Jó gyakorlat a rendezési paraméterek törlése a VBA -ban a rendezés befejezése előtt és után.
A rendezési módszer gyakorlati alkalmazása a VBA -ban
Amikor táblázatos adatokat importál az Excelbe, azok gyakran nagyon véletlenszerű sorrendben vannak. Importálható CSV (vesszővel elválasztott értékek) fájlból, vagy származhat egy adatbázisra vagy weboldalra mutató linkről. Nem támaszkodhat arra, hogy egy sorrendben van az egyik importálásról a másikra.
Ha ezeket az adatokat bemutatja egy felhasználónak a munkalapon belül, akkor előfordulhat, hogy a felhasználónak nehéz megnéznie és megértenie egy hatalmas mennyiségű adatot, amelyek sorrendben mindenhol megtalálhatók. Lehet, hogy csoportosítani akarják az adatokat, vagy kivágják és beillesztik bizonyos részeit egy másik alkalmazásba.
Lehet, hogy látni akarják például a legjobban fizetett munkavállalót vagy a leghosszabb szolgálatot teljesítő munkavállalót is.
A VBA rendezési módszerével olyan lehetőségeket kínálhat, amelyek lehetővé teszik a felhasználó egyszerű rendezését.
Mintaadatok az Excel rendezés demonstrálására VBA -val
Először néhány mintaadatot kell bevinni a munkalapba, hogy a kód bemutassa a VBA -n belül rendelkezésre álló összes lehetőséget.
Másolja ezeket az adatokat egy munkalapra (az úgynevezett „Sheet1”) pontosan az ábrán látható módon.
Vegye figyelembe, hogy különböző cellaháttér- és betűszíneket használtak, mivel ezek rendezési paraméterekként is használhatók. A cella- és betűszínek használatával történő rendezést a cikk későbbi részében mutatjuk be. Vegye figyelembe azt is, hogy az E3 cellában az osztály neve kisbetűs.
Nincs szüksége a cella belsejére és a betűszínekre, ha nem kívánja használni a cella- és betűszín szerinti rendezés példáit.
Makró rögzítése VBA rendezéshez
A rendezéshez használt VBA -kód meglehetősen bonyolult lehet, és néha jó ötlet lehet a rendezést az Excel kezelőfelületén, és rögzíteni egy makrót, amely megmutatja a kód működését.
Sajnos a rögzítési funkció hatalmas mennyiségű kódot generálhat, mivel gyakorlatilag minden elérhető paramétert beállít, még akkor is, ha sok paraméter alapértelmezett értékei elfogadhatók a rendezési művelethez.
Ez azonban nagyon jó képet ad arról, hogy mi tartozik a VBA rendezési kód írásához, és az egyik előnye, hogy a rögzített kód mindig működni fog. Saját kódjának tesztelésére és hibakeresésére lehet szükség ahhoz, hogy megfelelően működjön.
Ne feledje, hogy a VBA -ban végrehajtott műveleteknél nincs visszavonási funkció, ezért érdemes a táblázatos adatokról másolatot készíteni egy másik munkalapra, mielőtt elkezdené írni a rendezési kódot.
Például, ha egyszerű rendezést hajtott végre a fenti mintaadatokon, munkavállaló szerinti rendezéssel, a felvétel a következő kódot generálja:
| 123456789101112131415161718 | Makro1 ()Tartomány ("A1: E6"). Válassza kiActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Tartomány ("A2: A6"), _SortOn: = xlSortOnValues, Order: = xlNövekvő, DataOption: = xlSortNormalActiveWorkbook.Worksheets ("Sheet1"). Rendezés.SetRange tartomány ("A1: E6").Header = xlIgen.MatchCase = Hamis.Orientation = xlTopToBottom.SortMethod = xlPinYin.AlkalmazVége ezzelEnd Sub |
Ez meglehetősen nagy kódrészlet, és sok szükségtelen az alapértelmezett paraméterek miatt. Ha azonban időbeli nyomás nehezedik egy projekt befejezésére, és gyorsan szüksége van valamilyen kódra, amely működik, akkor ezt könnyen beillesztheti saját VBA -kódjába.
Ha azonban érthetőbbé és elegánsabbá szeretné tenni a kódot, akkor más lehetőségek is rendelkezésre állnak.
VBA kód egyszintű rendezéshez
Ha csak a makró rögzítésekor szeretné a mintakódot az Alkalmazott alapján rendezni, mint korábban, akkor a kód nagyon egyszerű:
| 1234567 | Sub SingleLevelSort ()Munkalapok ("Sheet1"). Rendezés.SortFields.ClearTartomány ("A1: E6"). Rendezési kulcs1: = Tartomány ("A1"), fejléc: = xlIgenEnd Sub |
Ezt sokkal könnyebb megérteni, mint a rögzített kódot, mert elfogadja az alapértelmezett értékeket, például növekvő rendezést, így nincs szükség a paraméterek alapértelmezett értékre állítására. Ez azt feltételezi, hogy korábban használta a „Törlés” utasítást.
A „Törlés” módszert használják annak biztosítására, hogy az adott munkalap minden rendezési paramétere vissza legyen állítva az alapértelmezett értékekre. Előfordulhat, hogy a felhasználó korábban különböző értékekre állította be a paramétereket, vagy a VBA egy korábbi típusa módosította azokat. Fontos, hogy rendezéskor alapértelmezett pozícióból induljon ki, különben könnyen hibás eredményeket kaphat.
A Törlés módszer nem állítja vissza a Fejléc paramétert, és célszerű ezt is belefoglalni a kódba, ellenkező esetben az Excel megpróbálhatja kitalálni, hogy van -e fejlécsor vagy sem.
Futtassa ezt a kódot a mintaadatok ellen, és a munkalapja így fog kinézni:
VBA kód többszintű rendezéshez
Annyi rendezési szintet adhat hozzá a kódhoz, amennyi szükséges. Tegyük fel, hogy először osztályok, majd kezdési dátumok szerint, de növekvő sorrendben és a kezdő dátum szerint csökkenő sorrendben kívánja rendezni:
| 12345678 | Sub MultiLevelSort ()Munkalapok ("Sheet1"). Rendezés.SortFields.ClearTartomány ("A1: E6"). Rendezési kulcs1: = Tartomány ("E1"), Kulcs2: = Tartomány ("C1"), Fejléc: = xlIgen, _Sorrend1: = xl növekvő, sorrend2: = xl csökkenőEnd Sub |
Vegye figyelembe, hogy a rendezési utasításban most két kulcs található (Key1 és Key2). A Kulcs1 (Osztály E oszlop) először rendezésre kerül, majd a Kulcs2 (Kezdő dátum C oszlop) az első rendezés alapján.
Két rendelési paraméter is létezik. A Order1 a Key1 -hez (osztály), a Order2 pedig a Key2 -hez (kezdő dátum) társul. Fontos gondoskodni arról, hogy a kulcsok és a megrendelések lépést tartsanak egymással.
Futtassa ezt a kódot a mintaadatok ellen, és a munkalapja így fog kinézni:
Az Osztály oszlop (E) növekvő sorrendben, a Kezdő dátum oszlop (C) pedig csökkenő sorrendben van.
Ez a fajta hatás leginkább akkor látható, ha Jane Halfacre -t (3. sor) és John Sutherlandet (4. sor) nézzük. Mindketten pénzügyek, de Jane Halfacre John Sutherland előtt kezdődött, és a dátumok csökkenő sorrendben jelennek meg.
Ha a táblázatos adatok tartománya bármilyen hosszúságú lehet, akkor a UsedRange objektummal határozhatja meg a rendezési tartományt. Ez csak akkor működik, ha csak a táblázatos adatok vannak a munkalapon, mivel az adatokon kívüli értékek helytelen eredményeket adnak a sorok és oszlopok számára.
| 1234567 | Sub MultiLevelSort ()Munkalapok ("Sheet1"). Rendezés.SortFields.ClearMunkalapok ("Sheet1"). UsedRange.Sort Key1: = Range ("E1"), Key2: = Range ("C1"), Header: = xlYes, _Sorrend1: = xl növekvő, sorrend2: = xl csökkenőEnd Sub |
Ez megakadályozza a problémát, ha az „End (xlDown)” metódust használja a rendezési tartomány meghatározásához. Ha üres cella van az adatok közepén, akkor az üres cella után semmi nem lesz benne, míg a UsedRange lemegy a munkalap utolsó aktív cellájába.
Rendezés cellaszín szerint
Az Excel 2007 óta már lehetséges a cella háttérszíne szerinti rendezés, ami óriási rugalmasságot biztosít a rendezési kód VBA -ban történő tervezésekor.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Munkalapok ("Sheet1"). Rendezés.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Tartomány ("A2: A6"), _SortOn: = xlSortOnCellColor, Order: = xlNövekvő, DataOption: = xlSortNormalActiveWorkbook.Worksheets ("Sheet1"). Rendezés.SetRange tartomány ("A1: E6").AlkalmazVége ezzelEnd Sub |
Ez a kód a minta adattartományát (A2: A6) rendezi a cella háttérszíne alapján. Vegye figyelembe, hogy most van egy további paraméter, a „SortOn”, amelynek értéke „xlSortOnCellColor”.
Ne feledje, hogy a „SortOn” paramétert csak egy munkalapobjektum használhatja, egy tartományobjektum nem.
Emiatt a kód bonyolultabb, mint a cellaértékeket használó rendezésnél.
Ez a kód egy kulcsértéket használ a rendezéshez, amely lefedi a teljes adattartományt, de megadhat egyedi oszlopokat a háttérszín rendezésének kulcsaiként, és több szintet is használhat, amint az korábban látható volt.
A kód futtatása után a munkalapja így fog kinézni:
Rendezés betűszín szerint
Az Excel VBA rendezési funkciója még nagyobb rugalmasságot kínál a betűszínek szerinti rendezéshez:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Munkalapok ("Sheet1"). Rendezés.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Tartomány ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)ActiveWorkbook.Worksheets ("Sheet1"). Rendezés.SetRange tartomány ("A1: E6").Header = xlIgen.Orientation = xlTopToBottom.AlkalmazVége ezzelEnd Sub |
A betűtípus színe szerinti rendezés kódja sokkal bonyolultabb, mint a cella háttérszíne. A „SortOn” paraméter most az „xlSortOnFontColor” értékét tartalmazza.
Ne feledje, hogy a tájolást „xlTopToBottom” -ként kell megadnia, és meg kell adnia a színt a rendezéshez. Ezt RGB -feltételek (piros, zöld, fekete) határozzák meg, 0 és 255 közötti értékekkel.
Miután futtatta ezt a kódot a mintaadatokkal szemben, a munkalapja így fog kinézni:
A színek rendezése a VBA-ban sokkal bonyolultabb, mint a többszintű rendezés, de ha a rendezési kód nem fog működni (ez megtörténhet, ha hiányzik egy paraméter, vagy nem adta meg helyesen a kódot), akkor bármikor visszatérhet a felvételhez makró és a rögzített kód integrálása a VBA -ba.
Más paraméterek használata a VBA rendezésben
Számos opcionális paramétert használhat a VBA -kódban a rendezés testreszabásához.
SortOn
A SortOn kiválasztja, hogy a rendezés cellaértékeket, cella háttérszíneket vagy cella betűszíneket használ -e. Az alapértelmezett beállítás a Cellaértékek.
| 1 | SortOn = xlSortOnValues |
Rendelés
A sorrend kiválasztja, hogy a rendezés növekvő vagy csökkenő sorrendben történik -e. Az alapértelmezett a növekvő.
| 1 | Sorrend = xlNövekvőben |
DataOption
A DataOption kiválasztja a szöveg és a számok rendezésének módját. Az xlSortNormal paraméter külön rendeli a numerikus és szöveges adatokat. Az xlSortTextAsNumbers paraméter a szöveget numerikus adatként kezeli a rendezéshez. Az alapértelmezett xlSortNormal.
| 1 | DataOption = xlSortNormal |
Fejléc
A fejléc választja ki, hogy a táblázatos adattartomány fejlécesorral rendelkezik -e vagy sem. Ha van fejlécsor, akkor nem szeretné, hogy ez szerepeljen a rendezésben.
A paraméterek értéke xlYes, xlNo és xlYesNoGuess. Az xlYesNoGuess az Excelre bízza annak meghatározását, hogy van -e fejléc, ami könnyen következetlen eredményekhez vezethet. Ennek az értéknek a használata nem ajánlott.
Az alapértelmezett érték az XNo (nincs fejléc sor az adatokon belül). Az importált adatoknál általában van fejlécsor, ezért győződjön meg róla, hogy ezt a paramétert xlYes értékre állítja.
| 1 | Fejléc = xlIgen |
Mérkőzés esetén
Ez a paraméter határozza meg, hogy a rendezés megkülönbözteti-e a kis- és nagybetűket. A beállítási értékek igazak vagy hamisak. Ha az érték hamis, akkor a kisbetűs értékek azonosak a nagybetűkkel. Ha az érték True, akkor a rendezés megmutatja a különbséget a nagy és kisbetűk között a rendezésen belül. Az alapértelmezett érték False.
| 1 | MatchCase = Hamis |
Orientáció
Ez a paraméter határozza meg, hogy a rendezés lefelé történik -e a sorokban, vagy az összes oszlopban. Az alapértelmezett érték az xlTopToBottom (sorok rendezése). Ha horizontálisan szeretné rendezni, használhatja az xlLeftToRight parancsot. Az olyan értékek, mint az xlRows és az xlColumns, nem működnek ennél a paraméternél.
| 1 | Tájolás = xlTopToBottom |
SortMethod
Ez a paraméter csak a kínai nyelvek rendezésére szolgál. Két értéke van, xlPinYin és xlStroke. Az xlPinYin az alapértelmezett érték.
Az xlPinYin a karakterek fonetikus kínai rendezési sorrendjét használja. Az xlStroke az egyes karakterek ütéseinek száma alapján rendez.
Ha rendezési makrót rögzít, ez a paraméter mindig szerepelni fog a kódban, és kíváncsi lehet, mit jelent. Azonban hacsak nem kínai nyelvű adatokkal van dolgunk, azoknak kevés haszna van.
| 1 | SortMethod = xlPinYin |
Dupla kattintású esemény használata a táblázatos adatok rendezéséhez
Az összes olyan funkcióban, amelyet a Microsoft a VBA rendezési módszereibe beépített, nem tartalmazott egyszerű módot arra, hogy duplán kattintson egy oszlopfejlécre, és a táblázatos adatokat az adott oszlop alapján rendezze.
Ez egy nagyon hasznos funkció, és könnyen megírhatja a kódot.
| 12345678910111213141516171819202122232425262728293031323334 | Privát almunkamenet_BeforeDoubleClick (ByVal Target as Range, Cancel as Boolean)„Feltételezzük, hogy az adatok az A1 cellában kezdődnekHozzon létre három változót a kiválasztott céloszlop, valamint a _ maximális oszlop és sor rögzítéséhez'a táblázatos adatokDim Col As As Integer, RCol As Long, RRow As Long„Ellenőrizze, hogy a felhasználó duplán kattintott -e a fejlécsorra - az 1. sor különben kilép az aloldalbólHa cél. 1. sor, majd kilépés a szub'Fogja be a táblázatos adattartomány maximális sorait a "UsedRange" objektum segítségévelRCol = ActiveSheet.UsedRange.Columns.Count'Rögzítse a maximális oszlopokat a táblázatos adattartományban a' UsedRange 'objektum használatávalRRow = ActiveSheet.UsedRange.Rows.Count„Ellenőrizze, hogy a felhasználó nem kattintott-e duplán a táblázatos adattartományon kívüli oszlopraHa Target.Oszlop> RCol, akkor lépjen ki a SubRögzítse az oszlopot, amelyre a felhasználó duplán kattintottCol = Cél. Oszlop'Törölje a korábbi rendezési paramétereketActiveSheet.Sort.SortFields.Clear'Rendezze a táblázatos tartományt a maximálisan használható sorok és oszlopok szerint a' UsedRange 'objektumból'Rendezze a táblázatos adatokat a felhasználó által duplán kattintott oszlop rendezési kulcsakéntActiveSheet.Range (cellák (1, 1), cellák (RCol, RRow)). Rendezési kulcs1: = cellák (1, oszlop), fejléc: = xl Igen'Válassza ki az A1 cellát - ez biztosítja, hogy a felhasználó ne maradjon szerkesztési módban a rendezés után _'befejeződöttActiveSheet.Range ("A1"). Válassza kiEnd Sub |
Ezt a kódot el kell helyezni a táblázatos adatokat tartalmazó lap dupla kattintásos eseményére. Ehhez kattintson a munkalap nevére a Project Explorer ablakban (a VBE képernyő bal felső sarkában), majd válassza a „Munkalap” lehetőséget a kódablak első legördülő menüjében. A második legördülő menüben válassza az „BeforeDoubleClick” lehetőséget, majd írja be a kódot.
Ne feledje, hogy egyetlen név, tartomány vagy cellahivatkozás nincs kódolva ebbe a kódba, kivéve, ha a kurzort a kód végén lévő A1 cellába helyezi át. A kód célja, hogy minden szükséges információt lekérjen a felhasználó által duplán kattintott cellakoordinátáktól és a táblázatos adattartomány méretétől.
Nem mindegy, hogy mekkora a táblázatos adattartomány. A kód továbbra is felveszi az összes szükséges információt, és felhasználható a munkafüzet bárhol tárolt adataira anélkül, hogy keményen kellene kódolni az értékeket.
Az egyetlen feltételezés az, hogy van egy fejlécesor a táblázatos adatokban, és hogy az adattartomány az A1 cellában kezdődik, de az adattartomány kiindulási pozíciója könnyen megváltoztatható a kódon belül.
Minden felhasználó lenyűgözni fogja ezt az új rendezési funkciót!
A rendezési funkció kiterjesztése a VBA használatával
A Microsoft óriási rugalmasságot tett lehetővé a paraméterek széles skáláját használó rendezésben. A VBA -n belül azonban ezt tovább is viheti.
Tegyük fel, hogy minden értéket vastag betűvel szeretne rendezni az adatok tetejére. Ezt Excelben nem lehet megtenni, de megírhatja a VBA kódot:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortByBold ()'Hozzon létre változókat a táblázatos adatok sorainak és oszlopainak számának megőrzéséreDim RRow Long, RCol Long, N As Long„Kapcsolja ki a képernyőfrissítést, hogy a felhasználó ne lássa, mi történik - láthatja, hogy _Az értékek megváltoznak, és vajon miértApplication.ScreenUpdating = Hamis'Rögzítse az oszlopok számát a táblázatos adattartománybanRCol = ActiveSheet.UsedRange.Columns.Count'Rögzítse a táblázatos adattartományon belüli sorok számátRRow = ActiveSheet.UsedRange.Rows.Count'Ismételje végig a táblázatos adattartomány összes sorát, figyelmen kívül hagyva a fejléc sortN = 2 esetén RRow'Ha egy cella félkövér betűtípussal rendelkezik, akkor helyezzen egy első 0 értéket a cella értékéhezHa ActiveSheet.Cells (N, 1) .Font.Bold = True AkkorActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .ValueVége HaKövetkező N.'Törölje a korábbi rendezési paramétereketActiveSheet.Sort.SortFields.Clear'Rendezze a táblázatos adattartományt. Az első 0 értékkel rendelkező összes érték a csúcsra kerülActiveSheet.Range (cellák (1, 1), cellák (RCol, RRow)). Rendezési kulcs1: = cellák (1, 1), fejléc: = xl Igen'Ismételje meg a táblázatos adattartomány összes sorát, figyelmen kívül hagyva a fejléc sortN = 2 esetén RRow'Ha egy cella félkövér betűtípussal rendelkezik, akkor távolítsa el a vezető 0 értéket a cella értékétől a _"állítsa vissza az eredeti értékeketHa ActiveSheet.Cells (N, 1) .Font.Bold = True AkkorActiveSheet.Cells (N, 1). Value = Mid (ActiveSheet.Cells (N, 1). Value, 2)Vége HaKövetkező N.'Kapcsolja be újra a képernyőfrissítéstApplication.ScreenUpdating = IgazEnd Sub |
A kód a „UsedRange” objektum segítségével határozza meg a táblázatos adattartomány méretét, majd iterál a benne lévő összes sorban. Ha félkövér betűtípust talál, a cella értéke elé egy nulla kerül.
Ekkor sor kerül a rendezésre. Mivel a rendezés növekvő sorrendben van, bármi, amely előtt nulla található, a lista elejére kerül.
A kód ezután ismétli az összes sort, és eltávolítja az első nullákat, visszaállítva az adatokat az eredeti értékekre.
Ez a kód félkövér betűtípusokat használ kritériumként, de könnyen használhat más cellatulajdonságokat is, például dőlt betűtípust, szövegméretet, aláhúzást, betűtípust stb.
