Ako zoradiť údaje v programe Excel pomocou VBA (podrobný sprievodca)

Excel už má niekoľko spôsobov, ako rýchlo triediť údaje.

Množinu údajov môžete jednoducho zoradiť pomocou ikon zoradenia na páse s nástrojmi alebo v dialógovom okne zoradenia.

Prečo potom potrebujete vedieť, ako to urobiť pomocou VBA?

Vedieť, ako triediť údaje pomocou jazyka VBA, môže byť užitočné, ak je súčasťou vášho kódu. Predpokladajme napríklad, že dostanete súbor údajov denne/týždenne, ktorý potrebujete naformátovať a zoradiť v konkrétnom poradí.

Môžete vytvoriť makro, ktoré to všetko urobí za vás jediným kliknutím. To vám ušetrí veľa času a námahy vždy, keď to urobíte.

Tiež, ak vytvoríte dashboardy Excelu, môžete posunúť schopnosť radenia Excelu na novú úroveň tým, že používateľovi umožníte triedenie údajov jednoduchým dvojitým kliknutím na hlavičku (ako je uvedené nižšie).

Ako to vytvoriť, sa budem venovať neskôr v tomto návode. Poďme si najskôr rýchlo ujasniť základy.

Pochopenie rozsahu. Metóda triedenia v programe Excel VBA

Pri triedení pomocou VBA musíte vo svojom kóde použiť metódu Range.Sort.

„Rozsah“ by boli údaje, ktoré sa pokúšate triediť. Ak napríklad triedite údaje v triede A1: A10, potom „rozsah“ bude rozsah („A1: A10“).

Môžete tiež vytvoriť pomenovaný rozsah a použiť ho namiesto odkazov na bunky. Ak napríklad vytvorím pomenovaný rozsah „DataRange“ pre bunky A1: A10, potom môžem použiť aj rozsah („DataRange“)

Pri metóde triedenia musíte prostredníctvom parametrov poskytnúť niekoľko ďalších informácií. Nasledujú kľúčové parametre, ktoré potrebujete vedieť:

• Kľúč - tu musíte zadať stĺpec, ktorý chcete zoradiť. Napríklad, ak chcete zoradiť stĺpec A, musíte použiť kľúč: = Rozsah („A1“)
• objednať - tu určíte, či chcete zoradiť vzostupne alebo zostupne. Ak napríklad chcete zoradiť vzostupne, použijete poradie: = xlAscending
• Hlavička - tu určíte, či má váš súbor údajov hlavičky alebo nie. Ak má hlavičky, triedenie začína od druhého riadka množiny údajov, inak začína od prvého riadka. Ak chcete určiť, že vaše údaje majú hlavičky, použijete záhlavie: = xlYes

Aj keď tieto tri postačujú vo väčšine prípadov, v tomto článku si môžete prečítať viac o parametroch.

Teraz sa pozrime, ako použiť metódu Range.Sort vo VBA na triedenie údajov v programe Excel.

Zoradenie jedného stĺpca bez hlavičky

Predpokladajme, že máte jeden stĺpec bez hlavičky (ako je uvedené nižšie).

Nasledujúci kód môžete použiť na zoradenie vzostupne.

PodroztriedenieDataWithoutHeader () Rozsah ("A1: A12"). Triediaci kľúč1: = rozsah ("A1"), poradie1: = xlAscending, hlavička: = xlŽiadny koncový podrad

Všimnite si toho, že som rozsah údajov zadal ručne ako Rozsah („A1: A12“).

V prípade, že by mohlo dôjsť k zmenám v údajoch a hodnoty by mohli byť pridané/odstránené, môžete použiť nižšie uvedený kód, ktorý sa automaticky upraví na základe vyplnených buniek v množine údajov.

Sub SortDataWithoutHeader () Rozsah ("A1", Rozsah ("A1"). Koniec (xlDown)). Triediaci kľúč1: = rozsah ("A1"), poradie1: = xlAscending, hlavička: = xlŽiadny koncový sub

Všimnite si toho, že namiesto Range („A1: A12“) som použil Range („A1“, Range („A1“). End (xlDown)).

Toto skontroluje poslednú po sebe vyplnenú bunku v stĺpci a zahrnie ju do triedenia. V prípade, že existujú medzery, bude brať do úvahy údaje iba do prvej prázdnej bunky.

Môžete tiež vytvoriť pomenovaný rozsah a použiť tento pomenovaný rozsah namiesto odkazov na bunky. Ak je napríklad pomenovaný rozsah DataSet, váš kód bude teraz taký, ako je uvedené nižšie.

Rozsah SortDataWithoutHeader () („DataRange“). Kľúč zoradenia1: = rozsah („A1“), poradie1: = xlAscending, hlavička: = xlŽiadny koncový podrad

Teraz mi rýchlo vysvetlite parametre použité vo vyššie uvedených príkladoch:

• Kľúč 1: = Rozsah („A1“) - Špecifikovaný A1, aby kód vedel, ktorý stĺpec sa má zoradiť.
• Order1: = xlAscending - Zadala objednávku ako xlAscending. Ak chcete, aby bol v zostupnom poradí, použite xlDescending.
• Header: = xlNo - Zadané, že neexistujú žiadne hlavičky. Toto je tiež predvolená hodnota. Takže aj keď to vynecháte, vaše údaje budú zoradené, pretože nemajú žiadne hlavičky.

Zaujíma vás, kam vložiť tento kód VBA a ako spustiť makro? Prečítajte si tento návod!

Zoradenie jedného stĺpca s hlavičkou

V predchádzajúcom príklade súbor údajov nemal hlavičku.

Keď majú vaše údaje hlavičky, musíte to zadať v kóde, aby sa triedenie mohlo začať od druhého riadka množiny údajov.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:

Nasleduje kód, ktorý zoradí údaje zostupne na základe tržieb z obchodov.

Podrozsah SortDataWithHeader () ("DataRange"). Triediaci kľúč1: = rozsah ("C1"), poradie1: = xl Zostupný koniec Sub

Všimnite si toho, že som vytvoril pomenovaný rozsah - „DataRange“ a tento pomenovaný rozsah som použil v kóde.

Zoradenie viacerých stĺpcov so hlavičkami

V tomto návode sme zatiaľ videli, ako zoradiť jeden stĺpec (s hlavičkami aj bez nich).

Teraz, čo keď chcete zoradiť podľa viacerých stĺpcov.

Napríklad v nižšie uvedenej množine údajov, čo keď chcem najskôr zoradiť podľa kódu štátu a potom podľa obchodu.

Tu je kód, ktorý bude triediť viac stĺpcov naraz.

Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Záhlavie = xlÁno. Použiť koniec s koncovým podč

Nasleduje výsledok, ktorý získate.

Vo vyššie uvedenom príklade sú údaje najskôr zoradené podľa kódu štátu (stĺpec A). Potom sa v rámci údajov o štátnom kóde opäť zoradí podľa Obchodu (stĺpec B). Toto poradie je určené kódom, v ktorom ho uvádzate.

Zoradenie údajov dvojitým kliknutím na hlavičku

Ak vytvárate informačný panel alebo chcete, aby bolo používanie vašich prehľadov jednoduchšie, môžete napísať kód VBA, ktorý údaje zoradí dvojitým kliknutím na hlavičky.

Niečo, ako je uvedené nižšie:

Nasleduje kód, ktorý vám to umožní:

Súkromný čiastkový pracovný hárok_BeforeDoubleClick (ByVal cieľ ako rozsah, Zrušiť ako booleovský) Dim KeyRange ako rozsah Dim ColumnCount ako celé číslo ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Set KeyRange = Range (Target.Address) Range ("DataRange"). Zoradiť Key1: = KeyRange, Header: = xlYes End If End Sub

Všimnite si toho, že som vytvoril pomenovaný rozsah („DataRange“) a použil som ho v kóde namiesto použitia odkazov na bunky.

Hneď ako dvakrát kliknete na niektorú z hlavičiek, kód deaktivuje obvyklú funkciu dvojitého kliknutia (ktorou sa dostanete do režimu úprav) a pri triedení údajov použije túto bunku ako kľúč.

Tiež si všimnite, že tento kód bude odteraz triediť všetky stĺpce iba vzostupne.

Všimnite si toho, že dvojité kliknutie je spúšťač, ktorý umožňuje Excelu spustiť zadaný kód. Tieto spúšťače, ako napríklad dvojité kliknutie, otvorenie zošita, pridanie nového pracovného hárka, zmena bunky atď., Sa nazývajú udalosti a dajú sa použiť na spustenie makier v programe Excel. Viac informácií o udalostiach programu Excel VBA si môžete prečítať tu.

Kam vložiť tento kód?

Tento kód musíte vložiť do okna s kódom listu, v ktorom chcete túto funkciu triedenia dvojitým kliknutím.

Urobiť toto:

• Kliknite pravým tlačidlom myši na kartu hárka.
• Kliknite na položku Zobraziť kód.
• Vložte kód do okna s kódom listu, v ktorom sú uložené vaše údaje.

Teraz čo keď chcete zoradiť prvé dva stĺpce („Štát“ a „Uložiť“) vzostupne, ale stĺpce „Predaj“ zostupne.

Tu je kód, ktorý to urobí:

Súkromný čiastkový pracovný hárok_BeforeDoubleClick (ByVal cieľ ako rozsah, Zrušiť ako booleovský) Dim KeyRange ako rozsah Dim ColumnCount ako celé číslo ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True Set KeyRange = Range (Target.Address) If Target.Value = "Sales" Then SortOrder = xlDescending Else SortOrder = xlAscending End If Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlYes, Order1: = SortOrder End If End Sub

Vo vyššie uvedenom kóde kontroluje, či je bunka, na ktorú sa dvakrát klikne, hlavičkou Predaj alebo nie. Ak áno, potom priradí hodnotu xlDescending premennej SortOrder, inak ju urobí xlAscending.

Teraz to poďme ďalej a po zoradení v záhlaví ukážte vizuálny znak (šípka a farebná bunka).

Niečo, ako je uvedené nižšie:

Aby som to dosiahol, pridal som nový pracovný hárok a vykonal v ňom nasledujúce zmeny (môžete si stiahnuť ukážkový súbor a postupovať podľa neho):

• Názov nového hárka bol zmenený na „BackEnd“.
• Do bunky B2 zadajte symbol šípky (Ak to chcete urobiť, prejdite na položku Vložiť a kliknite na možnosť „Symbol“).
• Skopírujte a prilepte hlavičky zo súboru údajov do bunky A3: C3 v hárku „Backend“.
• V bunke A4 použite nasledujúcu funkciu: AC4:

  = IF (A3 = $ C $ 1, A3 & "" & $ B $ 1, A3)

• Ostatné bunky sa automaticky vyplnia kódom VBA, keď stĺpček zoradíte dvojitým kliknutím na hlavičky.

Váš backendový list bude vyzerať nasledovne:

Teraz môžete pomocou nižšie uvedeného kódu údaje zoradiť dvojitým kliknutím na hlavičky. Keď dvakrát kliknete na hlavičku, automaticky sa zobrazí šípka v texte hlavičky. Všimnite si toho, že som tiež použil podmienené formátovanie na zvýraznenie bunky.

Súkromný čiastkový pracovný hárok_BeforeDoubleClick (ByVal cieľ ako rozsah, Zrušiť ako booleovský) Dim KeyRange ako rozsah Dim ColumnCount ako celé číslo ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Cancel = True worksheets ("Backend"). Range ("C1") = Target.Value Set KeyRange = Range (Target.Address) Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlYes worksheets ("BackEnd ") .Range (" A1 ") = Target.Column for i = 1 To ColumnCount Range (" DataRange "). Cells (1, i). Value = Worksheets (" Backend "). Range (" A4 "). Offset (0, i - 1). Hodnota Next i End If End Sub

Všimnite si toho, že tento kód funguje dobre na spôsob, akým sú skonštruované moje údaje a zošit. Ak zmeníte štruktúru údajov, budete musieť zodpovedajúcim spôsobom zmeniť kód.

Stiahnite si ukážkový súbor