Třídění dat v aplikaci Excel VBA
Excel má vynikající způsob třídění řady tabulkových dat pomocí pásu karet na frontendu Excelu a v určitém okamžiku budete pravděpodobně chtít tuto funkci použít v kódu VBA. Naštěstí je to velmi snadné.
Dialogové okno front-endu najdete kliknutím na ikonu „Seřadit“ ve skupině „Seřadit a filtrovat“ na kartě „Data“ na pásu karet aplikace Excel. Nejprve musíte vybrat rozsah tabulkových dat.
Můžete také použít Alt-A-S-S k zobrazení dialogového okna pro vlastní řazení.
V novějších verzích aplikace Excel byla metoda řazení výrazně vylepšena. Dříve bylo řazení omezeno na tři úrovně, ale nyní můžete zadat tolik úrovní, kolik potřebujete, a to platí i ve VBA.
Do svého kódu VBA můžete začlenit všechny nabízené funkce řazení v dialogu Třídění v Excelu. Funkce řazení v aplikaci Excel je rychlá a rychlejší než cokoli, co byste mohli sami napsat ve VBA, takže využijte této funkce.
Všimněte si toho, že když provádíte řazení ve VBA, parametry řazení zůstávají stejné v dialogovém okně řazení front-end. Uloží se také při uložení sešitu.
Pokud uživatel vybere stejný rozsah tabulkových dat a klikne na ikonu Seřadit, zobrazí se mu všechny vaše parametry, které byly zadány vaším kódem VBA. Pokud budou chtít vytvořit jakýsi vlastní design, budou muset nejprve odstranit všechny vaše úrovně řazení, což pro ně bude velmi nepříjemné.
Pokud nezměníte parametry v kódu a spoléháte na výchozí hodnoty, můžete zjistit, že uživatel provedl změny, které se promítnou do vašeho řazení VBA, a může poskytnout neočekávané výsledky, jejichž ladění může být velmi obtížné .
Naštěstí ve VBA existuje metoda Clear pro opětovné nastavení všech parametrů řazení, aby se uživateli zobrazilo dialogové okno čistého řazení
| 1 | Pracovní listy („List1“). Sort.SortFields.Clear |
Je dobrým zvykem vymazat parametry řazení ve VBA před a po dokončení řazení.
Praktické využití metody řazení ve VBA
Když jsou tabulková data importována do Excelu, jsou často ve velmi náhodném pořadí. Může být importován ze souboru CSV (hodnoty oddělené čárkami) nebo může pocházet z odkazu na databázi nebo webovou stránku. Nemůžete spoléhat na to, že bude v nastaveném pořadí od jednoho importu k druhému.
Pokud tyto údaje předkládáte uživateli v pracovním listu, může pro uživatele být obtížné podívat se na obrovské množství dat, která jsou z hlediska pořadí všude, a porozumět jim. Mohou chtít data seskupit nebo vyjmout a vložit určité jejich části do jiné aplikace.
Mohou také chtít vidět například nejlépe placeného zaměstnance nebo zaměstnance s nejdelší službou.
Pomocí metody Sort ve VBA můžete nabídnout možnosti, které uživateli umožní snadné třídění.
Ukázková data k demonstraci třídění v Excelu pomocí VBA
Nejprve potřebujeme zadat některá ukázková data do listu, aby kód mohl předvést všechna zařízení dostupná v rámci VBA.
Zkopírujte tato data do listu (nazývaného „List1“) přesně podle obrázku.
Všimněte si toho, že byly použity různé barvy pozadí buněk a barvy písma, protože tyto lze také použít jako parametry řazení. Řazení pomocí barev buněk a písem bude ukázáno dále v článku. Všimněte si také, že v buňce E3 jsou názvy oddělení malá.
Pokud si nepřejete použít příklady třídění podle barvy buňky a písma, nepotřebujete barvy buněk a písma.
Záznam makra pro třídění VBA
Kód VBA pro třídění se může dost zkomplikovat a někdy může být vhodné provést řazení na předním konci Excelu a zaznamenat makro, které vám ukáže, jak kód funguje.
Funkce záznamu bohužel může generovat obrovské množství kódu, protože nastavuje prakticky každý dostupný parametr, přestože jsou výchozí hodnoty pro mnoho parametrů přijatelné pro vaši třídící operaci.
Poskytne vám však velmi dobrou představu o tom, co je součástí psaní třídicího kódu VBA, a jednou výhodou je, že zaznamenaný kód vám bude vždy fungovat. Aby váš vlastní kód fungoval správně, může vyžadovat testování a ladění.
Pamatujte si, že u operace prováděné ve VBA neexistuje funkce vrácení, takže je dobré si vytvořit kopii tabulkových dat na jiný list, než začnete psát svůj třídicí kód.
Pokud byste například provedli jednoduché seřazení výše uvedených ukázkových dat, seřazení podle zaměstnance, záznam by vygeneroval následující kód:
| 123456789101112131415161718 | Dílčí makro 1 ()Rozsah („A1: E6“). VyberteActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Klíč: = Rozsah ("A2: A6"), _SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = xlSortNormalS ActiveWorkbook.Worksheets ("Sheet1"). TříditRozsah SetRange ("A1: E6").Header = xlAno.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.AplikovatKonec sEnd Sub |
To je docela velký kus kódu a hodně z toho je zbytečných, protože se používají výchozí parametry. Pokud jste však pod časovým tlakem na dokončení projektu a potřebujete rychle nějaký kód, který funguje, můžete jej snadno vložit do vlastního kódu VBA.
Pokud však chcete, aby byl váš kód srozumitelný a elegantnější, jsou k dispozici další možnosti.
Kód VBA pro třídění na jedné úrovni
Pokud chcete při záznamu makra třídit ukázkový kód na základě zaměstnance pouze jako dříve, je kód velmi jednoduchý:
| 1234567 | Sub SingleLevelSort ()Pracovní listy („List1“). Sort.SortFields.ClearRozsah ("A1: E6"). Klíč řazení1: = Rozsah ("A1"), záhlaví: = xlAnoEnd Sub |
To je mnohem snáze pochopitelné než zaznamenaný kód, protože přijímá výchozí hodnoty, např. Řadit vzestupně, takže není nutné nastavovat parametry na výchozí hodnoty. To předpokládá, že jste předem použili příkaz „Vymazat“.
Metoda „Vymazat“ se zpočátku používá k zajištění toho, aby každý parametr řazení pro tento list byl nastaven zpět na výchozí hodnoty. Uživatel mohl dříve nastavit parametry na jiné hodnoty, nebo je dřívější řazení ve VBA mohlo změnit. Při třídění je důležité začít z výchozí pozice, jinak byste mohli snadno skončit s nesprávnými výsledky.
Metoda Clear neresetuje parametr Header a je vhodné to zahrnout do kódu, jinak se Excel může pokusit odhadnout, zda je přítomen řádek záhlaví nebo ne.
Spusťte tento kód proti ukázkovým datům a váš list bude vypadat takto:
Kód VBA k provedení víceúrovňového třídění
Do svého kódu můžete přidat tolik úrovní řazení, kolik je požadováno. Předpokládejme, že byste chtěli třídit nejprve podle oddělení a poté podle data zahájení, ale ve vzestupném pořadí pro oddělení a sestupně podle data zahájení:
| 12345678 | Sub MultiLevelSort ()Pracovní listy („List1“). Sort.SortFields.ClearRozsah ("A1: E6"). Třídit klíč1: = rozsah ("E1"), klíč2: = rozsah ("C1"), záhlaví: = xlAno, _Pořadí1: = xlVzestupně, Pořadí2: = xlSestupněEnd Sub |
Všimněte si, že v příkazu sort jsou nyní dva klíče (Key1 a Key2). Klíč 1 (sloupec oddělení E) je nejprve seřazen a poté je klíč 2 (sloupec data zahájení C) seřazen na základě prvního řazení.
Existují také dva parametry objednávky. Spolupracovníci objednávky 1 s klíčem 1 (oddělení) a spolupracovníci objednávky 2 s klíčem 2 (datum zahájení). Je důležité zajistit, aby klíče a objednávky byly udržovány ve vzájemném souladu.
Spusťte tento kód proti ukázkovým datům a váš list bude vypadat takto:
Sloupec oddělení (E) je vzestupně a sloupec Datum zahájení (C) sestupně.
Efekt tohoto druhu je nejvíce patrný při pohledu na Jane Halfacre (řada 3) a John Sutherland (řada 4). Oba jsou ve financích, ale Jane Halfacre začala před Johnem Sutherlandem a data jsou zobrazena sestupně.
Pokud může mít rozsah tabulkových dat libovolnou délku, můžete použít UsedRange objekt k definování rozsahu řazení. To bude fungovat pouze v případě, že jsou na listu pouze tabulková data, protože jakékoli hodnoty mimo data poskytnou nesprávné výsledky pro počet řádků a sloupců.
| 1234567 | Sub MultiLevelSort ()Pracovní listy („List1“). Sort.SortFields.ClearPracovní listy („List1“). UsedRange.Sort Key1: = Range („E1“), Key2: = Range („C1“), Header: = xlYes, _Pořadí1: = xlVzestupně, Pořadí2: = xlSestupněEnd Sub |
To zabrání problému, pokud k definování rozsahu řazení použijete metodu „End (xlDown)“. Pokud je uprostřed dat prázdná buňka, pak cokoli za prázdnou buňkou nebude zahrnuto, zatímco UsedRange přejde dolů na poslední aktivní buňku v listu.
Řazení podle barvy buňky
Od aplikace Excel 2007 je nyní možné třídění podle barvy pozadí buňky, což poskytuje obrovskou flexibilitu při návrhu kódu řazení ve VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Pracovní listy („List1“). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Klíč: = Rozsah ("A2: A6"), _SortOn: = xlSortOnCellColor, Order: = xlAscending, DataOption: = xlSortNormalS ActiveWorkbook.Worksheets ("Sheet1"). TříditRozsah SetRange ("A1: E6").AplikovatKonec sEnd Sub |
Tento kód seřadí rozsah vzorových dat (A2: A6) podle barvy pozadí buňky. Všimněte si, že nyní existuje další parametr nazvaný „SortOn“, který má hodnotu „xlSortOnCellColor“.
Parametr „SortOn“ lze použít pouze pro objekt listu, nikoli pro objekt rozsahu.
Z tohoto důvodu je kód složitější než pro řazení pomocí hodnot buněk.
Tento kód používá klíčovou hodnotu pro řazení, které pokrývá celý rozsah dat, ale jako klíč pro řazení barev pozadí můžete zadat jednotlivé sloupce a použít více úrovní, jak je uvedeno výše.
Po spuštění tohoto kódu bude váš list nyní vypadat takto:
Řazení podle barvy písma
Funkce řazení v aplikaci Excel VBA nabízí ještě větší flexibilitu v tom, že můžete řadit podle barev písma:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Pracovní listy („List1“). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Rozsah ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)S ActiveWorkbook.Worksheets ("Sheet1"). TříditRozsah SetRange ("A1: E6").Header = xlAno.Orientation = xlTopToBottom.AplikovatKonec sEnd Sub |
Kód pro třídění podle barvy písma je mnohem komplikovanější než pro barvu pozadí buňky. Parametr „SortOn“ nyní obsahuje hodnotu „xlSortOnFontColor“.
Všimněte si, že musíte zadat orientaci jako „xlTopToBottom“ a musíte zadat barvu, na které se má řadit. Toto je specifikováno v podmínkách RGB (červená, zelená, černá) s hodnotami od 0 do 255.
Po spuštění tohoto kódu proti ukázkovým datům bude váš list nyní vypadat takto:
Třídění pomocí barev ve VBA je mnohem komplikovanější než víceúrovňové třídění, ale pokud váš třídicí kód nebude fungovat (což se může stát, pokud chybí parametr nebo jste kód nezadali správně), můžete se kdykoli vrátit k nahrávání makro a integrace zaznamenaného kódu do vašeho VBA.
Použití dalších parametrů při třídění VBA
Existuje řada volitelných parametrů, které můžete v kódu VBA použít k přizpůsobení svého řazení.
SortOn
SortOn určuje, zda budou při řazení použity hodnoty buněk, barvy pozadí buňky nebo barvy písma buňky. Výchozí nastavení je Hodnoty buněk.
| 1 | SortOn = xlSortOnValues |
Objednat
Pořadí určuje, zda bude řazení provedeno vzestupně nebo sestupně. Výchozí hodnota je Vzestupně.
| 1 | Order = xlAscending |
DataOption
DataOption určuje, jak se budou třídit text a čísla. Parametr xlSortNormal třídí číselná a textová data samostatně. Parametr xlSortTextAsNumbers považuje text za číselná data pro řazení. Výchozí hodnota je xlSortNormal.
| 1 | DataOption = xlSortNormal |
Záhlaví
Záhlaví určuje, zda má rozsah tabulkových dat řádek záhlaví nebo ne. Pokud existuje řádek záhlaví, nechcete, aby byl součástí řazení.
Hodnoty parametrů jsou xlYes, xlNo a xlYesNoGuess. xlYesNoGuess ponechává na Excelu, aby zjistil, zda existuje řádek záhlaví, což by mohlo snadno vést k nekonzistentním výsledkům. Použití této hodnoty se nedoporučuje.
Výchozí hodnota je XNo (žádný řádek záhlaví v datech). U importovaných dat obvykle existuje řádek záhlaví, takže se ujistěte, že jste tento parametr nastavili na xlYes.
| 1 | Záhlaví = xlAno |
Shodný případ
Tento parametr určuje, zda je při řazení rozlišována malá a velká písmena. Hodnoty možností jsou True nebo False. Pokud je hodnota False, pak jsou malá písmena považována za stejná jako velká písmena. Pokud je hodnota True, pak řazení zobrazí rozdíl mezi hodnotami velkých a malých písmen v rámci řazení. Výchozí hodnota je False.
| 1 | MatchCase = False |
Orientace
Tento parametr určuje, zda bude řazení probíhat směrem dolů v řádcích nebo ve všech sloupcích. Výchozí hodnota je xlTopToBottom (řazení v řádcích). Pokud chcete třídit horizontálně, můžete použít xlLeftToRight. Hodnoty jako xlRows a xlColumns pro tento parametr nefungují.
| 1 | Orientace = xlTopToBottom |
Metoda řazení
Tento parametr se používá pouze pro třídění čínských jazyků. Má dvě hodnoty, xlPinYin a xlStroke. xlPinYin je výchozí hodnota.
xlPinYin třídí pomocí fonetického čínského řazení znaků. xlStroke seřadí podle počtu tahů v každém znaku.
Pokud zaznamenáte třídicí makro, bude tento parametr vždy zahrnut v kódu a možná vás zajímá, co to znamená. Pokud se však nezabýváte daty v čínštině, je to k ničemu.
| 1 | SortMethod = xlPinYin |
Použití události dvojitého kliknutí k třídění tabulkových dat
Ve všech funkcích, které společnost Microsoft zahrnula do metod řazení pro VBA, nezahrnovala jednoduchý způsob, jak dvakrát kliknout na záhlaví sloupce a seřadit celá tabulková data na základě tohoto konkrétního sloupce.
Toto je opravdu užitečná funkce a je snadné napsat kód, jak to udělat.
| 12345678910111213141516171819202122232425262728293031323334 | Soukromý dílčí list_BeforeDoubleClick (cíl ByVal jako rozsah, zrušit jako booleovský)„Předpokládá se, že data začínají v buňce A1'Vytvořte tři proměnné pro zachycení vybraného cílového sloupce a maximálního sloupce a řádku _“tabulková dataDim Col jako celé číslo, RCol tak dlouhé, RRow jako dlouhé'Zkontrolujte, zda uživatel dvakrát klikl na řádek záhlaví - řádek 1, jinak ukončete dílčíIf Target.Row 1 Then Exit Sub„Zachyťte maximální řádky v rozsahu tabulkových dat pomocí objektu‘ UsedRange ’RCol = ActiveSheet.UsedRange.Columns.Count'Zachyťte maximální počet sloupců v rozsahu tabulkových dat pomocí objektu' UsedRange 'RRow = ActiveSheet.UsedRange.Rows.Count'Zkontrolujte, zda uživatel dvakrát neklikl na sloupec mimo rozsah tabulkových dat.'Pokud Target.Column> RCol Then Exit Sub'Zachyťte sloupec, na který uživatel dvakrát kliklCol = Target.Column"Vymažte předchozí parametry řazení."ActiveSheet.Sort.SortFields.Clear'Seřaďte rozsah tabulek podle maximálních řádků a sloupců z objektu' UsedRange ''Seřaďte tabulková data pomocí sloupce, na který uživatel dvakrát klikl jako na klíč řazeníActiveSheet.Range (buňky (1, 1), buňky (RCol, RRow)). Klíč řazení 1: = buňky (1, sloupec), záhlaví: = xlAno'Vyberte buňku A1 - tím zajistíte, že uživatel nebude po úpravě ponechán v režimu úprav _'dokončenoActiveSheet.Range ("A1"). VyberteEnd Sub |
Tento kód je třeba umístit na událost dvojitého kliknutí na list obsahující tabulková data. To provedete kliknutím na název listu v okně Průzkumníka projektu (levý horní roh obrazovky VBE) a poté v prvním rozevíracím seznamu v okně kódu vyberte „Pracovní list“. Ve druhém rozevíracím seznamu vyberte „BeforeDoubleClick“ a poté můžete zadat kód.
Všimněte si, že do tohoto kódu nejsou pevně zakódovány žádné názvy, rozsahy ani odkazy na buňky kromě přesunutí kurzoru do buňky A1 na konci kódu. Kód je navržen tak, aby získal všechny požadované informace ze souřadnic buněk, na které uživatel dvakrát klikl, a velikosti rozsahu tabulkových dat.
Nezáleží na tom, jak velký je rozsah tabulkových dat. Kód bude stále shromažďovat všechny požadované informace a lze jej použít na data uložená kdekoli ve vašem sešitu, aniž byste museli pevně kódovat hodnoty.
Jediným předpokladem je, že v tabulkových datech je řádek záhlaví a že rozsah dat začíná v buňce A1, ale počáteční pozici pro rozsah dat lze v kódu snadno změnit.
Každý uživatel bude touto novou funkcí třídění náležitě ohromen!
Rozšíření funkce třídění pomocí VBA
Společnost Microsoft umožnila obrovskou flexibilitu při řazení pomocí široké škály parametrů. V rámci VBA to však můžete posunout dále.
Předpokládejme, že byste chtěli seřadit všechny hodnoty tučným písmem na začátek dat. V aplikaci Excel to nelze provést, ale můžete k tomu napsat kód VBA:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | PodtřídaByBold ()'Vytvořte proměnné, které budou uchovávat počet řádků a sloupců pro tabulková data.'Dim RRow As Long, RCol As Long, N As Long'Vypněte aktualizaci obrazovky, aby uživatel neviděl, co se děje - může vidět _'hodnoty se mění a přemýšlím pročApplication.ScreenUpdating = False'Zachyťte počet sloupců v rozsahu tabulkových dat.'RCol = ActiveSheet.UsedRange.Columns.Count'Zachyťte počet řádků v rozsahu tabulkových dat.'RRow = ActiveSheet.UsedRange.Rows.Count"Opakujte všechny řádky v rozsahu tabulkových dat, přičemž ignorujte řádek záhlaví."Pro N = 2 do RRow'Pokud má buňka tučné písmo, umístěte počáteční hodnotu 0 proti hodnotě buňky.'Pokud ActiveSheet.Cells (N, 1) .Font.Bold = True ThenActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .ValueKonec IfDalší N."Vymažte všechny předchozí parametry řazení."ActiveSheet.Sort.SortFields.Clear„Seřaďte rozsah tabulkových dat. Všechny hodnoty s počáteční hodnotou 0 se přesunou nahoruActiveSheet.Range (buňky (1, 1), buňky (RCol, RRow)). Klíč řazení 1: = buňky (1, 1), záhlaví: = xlAno'Opakujte všechny řádky v rozsahu tabulkových dat, přičemž ignorujte řádek záhlaví.'Pro N = 2 do RRow'Pokud má buňka tučné písmo, odstraňte úvodní hodnotu 0 z hodnoty buňky na _'obnovte původní hodnotyPokud ActiveSheet.Cells (N, 1) .Font.Bold = True ThenActiveSheet.Cells (N, 1) .Value = Mid (ActiveSheet.Cells (N, 1) .Value, 2)Konec IfDalší N.'Znovu zapněte aktualizaci obrazovkyApplication.ScreenUpdating = TrueEnd Sub |
Kód vypočítá velikost rozsahu tabulkových dat pomocí objektu ‘UsedRange’ a poté iteruje všemi řádky v něm. Když je nalezeno tučné písmo, před hodnotu buňky se umístí úvodní nula.
Poté proběhne řazení. Protože je řazení ve vzestupném pořadí, cokoli s nulou vpředu přejde na začátek seznamu.
Kód poté iteruje všemi řádky a odstraní úvodní nuly a obnoví data na původní hodnoty.
Tento kód třídí pomocí tučných písem jako kritéria, ale stejným způsobem můžete snadno použít i jiné vlastnosti buňky, např. Kurzíva, velikost bodu textu, písmo podtržítka, název písma atd.
