Zoradenie údajov v programe Excel VBA
Excel má vynikajúce prostriedky na triedenie radu tabuľkových údajov pomocou pásky na klientskom rozhraní programu Excel a v určitom okamihu budete pravdepodobne chcieť použiť túto funkciu v kóde VBA. Našťastie je to veľmi jednoduché.
Dialógové okno klientskeho rozhrania sa nachádza kliknutím na ikonu „Zoradiť“ v skupine „Zoradiť a filtrovať“ na karte „Údaje“ na páse s nástrojmi programu Excel. Najprv musíte vybrať rozsah tabuľkových údajov.
Môžete tiež použiť Alt-A-S-S na zobrazenie dialógového okna pre vlastné zoradenie.
Metóda zoradenia bola v neskorších verziách Excelu výrazne vylepšená. Tento druh bol predtým obmedzený na tri úrovne, ale teraz môžete zadať toľko úrovní, koľko potrebujete, a to platí aj vo VBA.
Do svojho kódu VBA môžete začleniť všetky ponúkané funkcie triedenia v dialógovom okne Zoradiť v programe Excel. Funkcia zoradenia v programe Excel je rýchla a rýchlejšia ako čokoľvek, čo by ste vo VBA mohli napísať sami, takže využite výhody tejto funkcie.
Všimnite si toho, že keď robíte triedenie vo VBA, parametre zoradenia zostanú v dialógovom okne front-end triedenia rovnaké. Uložia sa aj pri uložení zošita.
Ak používateľ vyberie rovnaký rozsah tabuľkových údajov a klikne na ikonu Zoradiť, zobrazia sa mu všetky vaše parametre, ktoré boli zadané kódom VBA. Ak chcú vytvoriť nejaký vlastný dizajn, budú musieť najskôr odstrániť všetky vaše úrovne triedenia, čo ich bude veľmi obťažovať.
Ak tiež nezmeníte parametre v kóde a budete sa spoliehať na predvolené hodnoty, môžete zistiť, že používateľ vykonal zmeny, ktoré sa prejavia vo vašom druhu VBA, a môžu poskytnúť neočakávané výsledky, ktorých ladenie môže byť veľmi ťažké. .
Našťastie vo VBA existuje metóda Clear, ktorá umožňuje znova nastaviť všetky parametre zoradenia tak, aby sa používateľovi zobrazilo dialógové okno čistého zoradenia.
| 1 | Pracovné listy („List1“). Zoradiť.SortFields.Clear |
Je dobrým zvykom vymazať parametre zoradenia vo VBA pred a po dokončení triedenia.
Praktické využitie metódy triedenia vo VBA
Keď sa tabuľkové údaje importujú do Excelu, často sú vo veľmi náhodnom poradí. Dalo by sa to importovať zo súboru CSV (hodnoty oddelené čiarkami) alebo to môže pochádzať z odkazu na databázu alebo webovú stránku. Nemôžete sa spoliehať na to, že bude od jedného importu k druhému v stanovenom poradí.
Ak tieto údaje prezentujete používateľovi vo svojom pracovnom hárku, pre používateľa môže byť ťažké prezerať a porozumieť obrovskému množstvu údajov, ktoré sú z hľadiska poradia všade naokolo. Môžu chcieť zoskupiť údaje alebo ich niektoré časti vystrihnúť a vložiť do inej aplikácie.
Môžu tiež chcieť vidieť napríklad najlepšie plateného zamestnanca alebo zamestnanca s najdlhšou službou.
Pomocou metódy Zoradiť vo VBA môžete ponúknuť možnosti, ktoré používateľovi umožnia ľahké triedenie.
Ukážkové údaje na ukážku triedenia v Exceli pomocou VBA
Najprv potrebujeme, aby sme do pracovného hárka vložili niekoľko vzorových údajov, aby kód mohol demonštrovať všetky možnosti dostupné v rámci VBA.
Skopírujte tieto údaje do pracovného hárka (nazývaného „List1“) presne podľa obrázku.
Všimnite si toho, že boli použité rôzne farby pozadia bunky a farby písma, pretože tieto môžu byť tiež použité ako parametre triedenia. Zoradenie pomocou farieb buniek a písma bude ukázané neskôr v článku. Upozorňujeme, že v bunke E3 sú názvy oddelení malými písmenami.
Ak nechcete používať príklady triedenia podľa farby bunky a písma, nepotrebujete farby buniek a interiéru.
Nahrávanie makra pre triedenie VBA
Kód VBA na triedenie môže byť dosť komplikovaný a niekedy môže byť vhodné vykonať triedenie na klientskom serveri Excel a zaznamenať makro, ktoré vám ukáže, ako kód funguje.
Bohužiaľ, funkcia záznamu môže generovať obrovské množstvo kódu, pretože nastavuje prakticky každý dostupný parameter, aj keď sú predvolené hodnoty pre mnoho parametrov prijateľné pre vašu operáciu triedenia.
Poskytne vám však veľmi dobrú predstavu o tom, čo je súčasťou písania triediaceho kódu VBA, a jednou výhodou je, že zaznamenaný kód vám bude vždy fungovať. Aby váš vlastný kód fungoval správne, môže byť potrebné testovanie a ladenie.
Pamätajte si, že pre operáciu vykonanú vo VBA neexistuje žiadna funkcia vrátenia späť, a preto je vhodné vytvoriť kópiu tabuľkových údajov do iného pracovného hárka skôr, ako začnete písať triediaci kód.
Ak by ste napríklad vykonali jednoduché zoradenie vyššie uvedených vzorových údajov a zoradenie podľa zamestnancov, záznam by vygeneroval nasledujúci kód:
| 123456789101112131415161718 | Pod makro1 ()Rozsah („A1: E6“). VyberteActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Kľúč: = rozsah ("A2: A6"), _SortOn: = xlSortOnValues, poradie: = xlAscending, dátová možnosť: = xlSortNormalS ActiveWorkbook.Worksheets ("Sheet1"). ZoradiťRozsah SetRange ("A1: E6").Header = xlÁno.MatchCase = False.Orientácia = xlTopToBottom.Trieda = xlPinYin.PoužiťUkončiť sKoniec pod |
Toto je dosť veľký kus kódu a veľa z toho je zbytočných kvôli použitým predvoleným parametrom. Ak ste však pod časovým tlakom na dokončenie projektu a potrebujete rýchlo fungujúci kód, môžete ho ľahko prilepiť do vlastného kódu VBA.
Ak však chcete, aby bol váš kód zrozumiteľný a elegantnejší, sú k dispozícii ďalšie možnosti.
Kód VBA na triedenie na jednej úrovni
Ak chcete pri nahrávaní makra zoradiť ukážkový kód na základe Zamestnanca iba ako predtým, je kód veľmi jednoduchý:
| 1234567 | Sub SingleLevelSort ()Pracovné listy („List1“). Zoradiť.SortFields.ClearRozsah ("A1: E6"). Triediaci kľúč 1: = rozsah ("A1"), hlavička: = xlÁnoKoniec pod |
Toto je oveľa jednoduchšie pochopiť ako zaznamenaný kód, pretože akceptuje predvolené hodnoty, napríklad zoradenie vzostupne, takže nie je potrebné nastavovať parametre na predvolené hodnoty. Predpokladá to, že ste predtým použili príkaz „Vymazať“.
Metóda „Vymazať“ sa pôvodne používa na zabezpečenie toho, aby sa všetky parametre zoradenia pre tento hárok vrátili späť na predvolené hodnoty. Užívateľ mohol v minulosti nastaviť parametre na iné hodnoty alebo ich mohlo zmeniť predchádzajúce zoradenie vo VBA. Pri triedení je dôležité začať z predvolenej polohy, inak by ste mohli ľahko skončiť s nesprávnymi výsledkami.
Metóda Clear neresetuje parameter Header a je vhodné ho zahrnúť do kódu, inak sa program Excel môže pokúsiť uhádnuť, či je prítomný riadok hlavičky alebo nie.
Spustite tento kód proti vzorovým údajom a váš pracovný hárok bude vyzerať takto:
Kód VBA na vykonanie viacúrovňového triedenia
Do svojho kódu môžete pridať ľubovoľný počet úrovní triedenia. Predpokladajme, že ste chceli zoradiť najskôr podľa odborov a potom podľa dátumu začiatku, ale vzostupne pre oddelenie a zostupne podľa dátumu začiatku:
| 12345678 | Podradené viacúrovňové triedenie ()Pracovné listy („List1“). Zoradiť.SortFields.ClearRozsah ("A1: E6"). Triediaci kľúč 1: = rozsah ("E1"), kľúč 2: = rozsah ("C1"), hlavička: = xlÁno, _Poradie 1: = xlVzostupne, Poradie2: = xl ZostupneKoniec pod |
Všimnite si toho, že vo výkaze triedenia sú teraz dva kľúče (Key1 a Key2). Kľúč 1 (stĺpec E oddelenia) sa najskôr zoradí a potom sa zoradí kľúč 2 (stĺpec C dátumu začiatku) podľa prvého zoradenia.
Existujú tiež dva parametre objednávky. Spolupracovníci objednávky 1 s kľúčom 1 (oddelenie) a spolupracovníci objednávky 2 s kľúčom 2 (dátum začiatku). Je dôležité zabezpečiť, aby boli kľúče a objednávky navzájom v jednom kroku.
Spustite tento kód proti vzorovým údajom a váš pracovný hárok bude vyzerať takto:
Stĺpec Oddelenie (E) je vzostupne a stĺpec Dátum začiatku (C) zostupne.
Efekt tohto druhu je najpozoruhodnejší pri pohľade na Jane Halfacre (riadok 3) a John Sutherland (riadok 4). Obaja sú vo financiách, ale Jane Halfacre začala pred Johnom Sutherlandom a dátumy sú zobrazené zostupne.
Ak môže mať rozsah tabuľkových údajov ľubovoľnú dĺžku, na definovanie rozsahu zoradenia môžete použiť objekt UsedRange. To bude fungovať iba vtedy, ak sú v pracovnom hárku iba tabuľkové údaje, pretože akékoľvek hodnoty mimo údajov poskytnú nesprávne výsledky pre počet riadkov a stĺpcov.
| 1234567 | Podradené viacúrovňové triedenie ()Pracovné listy („List1“). Zoradiť.SortFields.ClearPracovné listy ("List1"). UsedRange.Sort Key1: = Range ("E1"), Key2: = Range ("C1"), Header: = xlYes, _Poradie 1: = xlVzostupne, Poradie2: = xl ZostupneKoniec pod |
Toto zabráni problému, ak na definovanie rozsahu zoradenia použijete metódu „End (xlDown)“. Ak je v strede údajov prázdna bunka, nič za prázdnou bunkou nebude zahrnuté, zatiaľ čo UsedRange prejde nadol na poslednú aktívnu bunku v pracovnom hárku.
Zoradenie podľa farby bunky
Od Excelu 2007 je teraz možné triedenie podľa farby pozadia bunky, čo poskytuje obrovskú flexibilitu pri navrhovaní kódu triedenia vo VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Pracovné listy („List1“). Zoradiť.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Kľúč: = rozsah ("A2: A6"), _SortOn: = xlSortOnCellColor, poradie: = xlAscending, dátová možnosť: = xlSortNormalS ActiveWorkbook.Worksheets ("Sheet1"). ZoradiťRozsah SetRange ("A1: E6").PoužiťUkončiť sKoniec pod |
Tento kód zoradí rozsah vzorových údajov (A2: A6) na základe farby pozadia bunky. Všimnite si toho, že teraz existuje ďalší parameter s názvom „SortOn“, ktorý má hodnotu „xlSortOnCellColor“.
Parameter „SortOn“ môže používať iba objekt pracovného hárka, a nie objekt rozsahu.
Z tohto dôvodu je kód komplikovanejší ako pri triedení pomocou hodnôt buniek.
Tento kód používa kľúčovú hodnotu na zoradenie, ktoré pokrýva celý rozsah údajov, ale ako kľúč na triedenie farieb pozadia môžete zadať jednotlivé stĺpce a použiť viac úrovní, ako je uvedené vyššie.
Po spustení tohto kódu bude váš pracovný hárok teraz vyzerať takto:
Zoradenie podľa farby písma
Funkcia zoradenia v programe Excel VBA ponúka ešte väčšiu flexibilitu v tom, že môžete triediť podľa farieb písma:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Pracovné listy („List1“). Zoradiť.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Range ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)S ActiveWorkbook.Worksheets ("Sheet1"). ZoradiťRozsah SetRange ("A1: E6").Header = xlÁno.Orientácia = xlTopToBottom.PoužiťUkončiť sKoniec pod |
Kód na triedenie podľa farby písma je oveľa komplikovanejší ako pre farbu pozadia bunky. Parameter „SortOn“ teraz obsahuje hodnotu „xlSortOnFontColor“.
Upozorňujeme, že musíte zadať orientáciu ako „xlTopToBottom“ a musíte zadať farbu, podľa ktorej sa má triediť. Toto je špecifikované v podmienkach RGB (červená, zelená, čierna) s hodnotami od 0 do 255.
Po spustení tohto kódu proti vzorovým údajom bude váš pracovný hárok teraz vyzerať takto:
Zoradenie pomocou farieb vo VBA je oveľa komplikovanejšie ako viacúrovňové triedenie, ale ak váš triediaci kód nebude fungovať (čo sa môže stať, ak chýba parameter alebo ste kód nezadali správne), môžete sa kedykoľvek vrátiť k záznamu makro a integrácia zaznamenaného kódu do vášho VBA.
Použitie ďalších parametrov pri triedení VBA
Existuje niekoľko voliteľných parametrov, ktoré môžete vo svojom kóde VBA použiť na prispôsobenie svojho druhu.
Zoradiť
SortOn určuje, či sa pri triedení použijú hodnoty buniek, farby pozadia bunky alebo farby písma bunky. Predvolené nastavenie je Hodnoty buniek.
| 1 | SortOn = xlSortOnValues |
objednať
Poradie určuje, či sa triedenie bude vykonávať vzostupne alebo zostupne. Predvolená hodnota je Vzostupne.
| 1 | Poradie = xlAssending |
DataOption
DataOption určuje, ako sa budú triediť text a čísla. Parameter xlSortNormal triedi číselné a textové údaje oddelene. Parameter xlSortTextAsNumbers považuje text za číselné údaje pre triedenie. Predvolená hodnota je xlSortNormal.
| 1 | DataOption = xlSortNormal |
Hlavička
Hlavička určuje, či má rozsah tabuľkových údajov riadok hlavičky alebo nie. Ak existuje riadok hlavičky, nechcete, aby to bolo zahrnuté v triedení.
Hodnoty parametrov sú xlYes, xlNo a xlYesNoGuess. xlYesNoGuess ponecháva na programe Excel, aby zistil, či existuje riadok hlavičky, čo môže ľahko viesť k nekonzistentným výsledkom. Použitie tejto hodnoty sa neodporúča.
Predvolená hodnota je XNo (v údajoch nie je žiadny riadok hlavičky). Pri importovaných údajoch je spravidla riadok hlavičky, preto sa uistite, že ste tento parameter nastavili na xlYes.
| 1 | Hlavička = xlÁno |
MatchCase
Tento parameter určuje, či sa v triedení rozlišujú malé a veľké písmená alebo nie. Hodnoty možností sú True alebo False. Ak je hodnota nepravdivá, potom sa malé hodnoty považujú za rovnaké ako veľké písmená. Ak je hodnota True, zoradenie zobrazí rozdiel medzi veľkými a malými písmenami v rámci zoradenia. Predvolená hodnota je False.
| 1 | MatchCase = False |
Orientácia
Tento parameter určuje, či bude triedenie prebiehať nadol v riadkoch alebo vo všetkých stĺpcoch. Predvolená hodnota je xlTopToBottom (radenie podľa riadkov). Ak chcete triediť horizontálne, môžete použiť xlLeftToRight. Hodnoty ako xlRows a xlColumns pre tento parameter nefungujú.
| 1 | Orientácia = xlTopToBottom |
Metóda triedenia
Tento parameter sa používa iba na triedenie čínskych jazykov. Má dve hodnoty, xlPinYin a xlStroke. xlPinYin je predvolená hodnota.
xlPinYin triedi pomocou fonetického čínskeho poradia triedenia znakov. xlStroke triedi podľa počtu úderov v každom znaku.
Ak zaznamenáte triediace makro, tento parameter bude vždy zahrnutý v kóde a pravdepodobne vás zaujíma, čo to znamená. Pokiaľ však nemáte do činenia s údajmi v čínštine, je to málo platné.
| 1 | SortMethod = xlPinYin |
Použitie udalosti dvojitého kliknutia na zoradenie tabuľkových údajov
Vo všetkých funkciách, ktoré spoločnosť Microsoft zahrnula do spôsobov triedenia pre VBA, nezahŕňala jednoduchý spôsob, ako dvakrát kliknúť na hlavičku stĺpca a zoradiť všetky tabuľkové údaje na základe tohto konkrétneho stĺpca.
Je to skutočne užitočná funkcia a je ľahké napísať kód, ako to urobiť.
| 12345678910111213141516171819202122232425262728293031323334 | Súhrnný pracovný hárok_BeforeDoubleClick (cieľ podľa ByVal ako rozsah, zrušenie ako booleovský)„Je predpoklad, že údaje začínajú v bunke A1„Vytvorte tri premenné na zachytenie vybratého cieľového stĺpca a maximálneho stĺpca a riadka _“tabuľkové údajeDim Col ako celé číslo, RCol tak dlhé, RRako dlhé„Skontrolujte, či používateľ dvakrát klikol na riadok hlavičky - riadok 1, inak ukončí podsekciuAk je to Target.Row 1 Then Exit Sub„Zachyťte maximálny počet riadkov v rozsahu tabuľkových údajov pomocou objektu„ UsedRange “RCol = ActiveSheet.UsedRange.Columns.Count'Zachyťte maximálny počet stĺpcov v rozsahu tabuľkových údajov pomocou objektu' UsedRange 'RRow = ActiveSheet.UsedRange.Rows.Count„Skontrolujte, či používateľ dvakrát neklikal na stĺpec mimo rozsah tabuľkových údajovAk Target.Column> RCol Then Exit Sub„Zachyťte stĺpec, na ktorý používateľ dvakrát klikolCol = Target.Column„Vymažte predchádzajúce parametre zoradeniaActiveSheet.Sort.SortFields.Clear'Zoraďte rozsah tabuliek definovaný maximálnym počtom riadkov a stĺpcov z objektu' UsedRange ''Zoraďte tabuľkové údaje pomocou stĺpca, na ktorý používateľ dvakrát klikol, ako kľúča zoradeniaActiveSheet.Range (bunky (1, 1), bunky (RCol, RRow)). Zoradiť kľúč 1: = bunky (1, stĺpček), hlavička: = xlÁno„Vyberte bunku A1 - toto zaistí, aby používateľ po skončení zoradenia nezostal v režime úprav _'dokončenéActiveSheet.Range ("A1"). VyberteKoniec pod |
Tento kód je potrebné umiestniť na udalosť dvojitého kliknutia na hárku obsahujúcom tabuľkové údaje. Vykonáte to kliknutím na názov pracovného hárka v okne Project Explorer (ľavý horný roh obrazovky VBE) a potom v prvom rozbaľovacom zozname v okne kódu zvoľte „Pracovný list“. V druhom rozbaľovacom zozname vyberte možnosť BeforeDoubleClick a potom zadajte kód.
Upozorňujeme, že do tohto kódu nie sú napevno zakódované žiadne názvy, rozsahy ani odkazy na bunky, okrem presunu kurzora do bunky A1 na konci kódu. Kód je navrhnutý tak, aby získal všetky potrebné informácie zo súradníc buniek, na ktoré používateľ dvakrát klikol, a z veľkosti rozsahu tabuľkových údajov.
Nezáleží na tom, aký veľký je rozsah tabuľkových údajov. Kód bude stále zhromažďovať všetky požadované informácie a môže byť použitý na údaje uložené kdekoľvek vo vašom zošite bez toho, aby ste museli hodnoty napevno kódovať.
Vychádza sa iba z predpokladu, že v tabuľkových dátach je riadok hlavičky a že rozsah údajov začína v bunke A1, ale počiatočnú pozíciu pre rozsah údajov je možné v kóde ľahko zmeniť.
Každý používateľ bude touto novou funkciou triedenia primerane ohromený!
Rozšírenie funkcie triedenia pomocou VBA
Spoločnosť Microsoft umožnila obrovskú flexibilitu pri triedení pomocou širokej škály parametrov. V rámci VBA to však môžete posunúť ďalej.
Predpokladajme, že ste chceli zoradiť všetky hodnoty tučným písmom na začiatok údajov. V programe Excel to nemožno urobiť, ale môžete na to napísať kód VBA:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | PodtriedaByBold ()„Vytvorte premenné, ktoré budú uchovávať počet riadkov a stĺpcov pre tabuľkové údajeDim RRow As Long, RCol As Long, N As Long„Vypnite aktualizáciu obrazovky, aby používateľ nevidel, čo sa deje - môže vidieť _Hodnoty sa menia a pýtate sa prečoApplication.ScreenUpdating = False„Zachyťte počet stĺpcov v rozsahu tabuľkových údajovRCol = ActiveSheet.UsedRange.Columns.Count'Zachyťte počet riadkov v rámci rozsahu tabuľkových údajovRRow = ActiveSheet.UsedRange.Rows.Count„Opakujte všetky riadky v rozsahu tabuľkových údajov, pričom riadok hlavičky ignorujtePre N = 2 až RRow„Ak má bunka tučné písmo, umiestnite úvodnú hodnotu 0 k hodnote bunkyAk ActiveSheet.Cells (N, 1) .Font.Bold = True ThenActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .HodnotaKoniec AkĎalej N.'Vymažte všetky predchádzajúce parametre zoradeniaActiveSheet.Sort.SortFields.Clear'Zoradiť rozsah tabuľkových údajov. Všetky hodnoty s počiatočnou hodnotou 0 sa presunú na začiatokActiveSheet.Range (bunky (1, 1), bunky (RCol, RRow)). Triediaci kľúč 1: = bunky (1, 1), hlavička: = xlÁno„Opakujte všetky riadky v rozsahu tabuľkových údajov, pričom riadok hlavičky ignorujtePre N = 2 až RRow„Ak má bunka tučné písmo, odstráňte úvodnú hodnotu 0 z hodnoty bunky do _“obnovte pôvodné hodnotyAk ActiveSheet.Cells (N, 1) .Font.Bold = True ThenActiveSheet.Cells (N, 1) .Value = Mid (ActiveSheet.Cells (N, 1) .Value, 2)Koniec AkĎalej N.'Znova zapnite aktualizáciu obrazovkyApplication.ScreenUpdating = TrueKoniec pod |
Kód vypočíta veľkosť rozsahu tabuľkových údajov pomocou objektu „UsedRange“ a potom iteruje všetkými riadkami v ňom. Keď sa nájde tučné písmo, pred hodnotu bunky sa umiestni úvodná nula.
Potom dôjde k triedeniu. Keďže zoradenie je vzostupné, všetko, čo má vpredu nulu, sa dostane na začiatok zoznamu.
Kód potom prejde všetkými riadkami a odstráni úvodné nuly a obnoví údaje na pôvodné hodnoty.
Tento kód sa radí pomocou tučného písma ako kritéria, ale rovnakým spôsobom by ste mohli ľahko použiť aj iné vlastnosti bunky, napríklad kurzívu, veľkosť bodu v texte, písmo podčiarkovníka, názov písma atď.
