Excel již má několik způsobů, jak rychle třídit data.
Sadu dat můžete snadno řadit pomocí ikon řazení na pásu karet nebo v dialogovém okně řazení.
Proč tedy potřebujete vědět, jak to udělat pomocí VBA?
Vědět, jak třídit data pomocí VBA, může být užitečné, pokud je součástí kódu. Předpokládejme například, že každý den/týdně získáte sadu dat, kterou potřebujete formátovat a třídit v určitém pořadí.
Můžete vytvořit makro, které to všechno udělá za vás jediným kliknutím. To vám ušetří spoustu času a úsilí pokaždé, když to uděláte.
Pokud také vytvoříte řídicí panely Excelu, můžete posunout schopnost řazení v Excelu na novou úroveň tím, že uživateli umožníte třídit data pouhým dvojitým kliknutím na záhlaví (jak je uvedeno níže).
Jak to vytvořit, se budu zabývat později v tomto tutoriálu. Pojďme si nejprve rychle ujasnit základy.
Pochopení metody Range.Sort v aplikaci Excel VBA
Při řazení pomocí VBA musíte v kódu použít metodu Range.Sort.
„Rozsah“ by byla data, která se pokoušíte třídit. Pokud například třídíte data v A1: A10, pak „Rozsah“ bude Rozsah („A1: A10“).
Můžete také vytvořit pojmenovaný rozsah a použít jej místo odkazů na buňky. Pokud například vytvořím pojmenovaný rozsah ‘DataRange’ pro buňky A1: A10, pak mohu také použít Range („DataRange“)
U metody řazení je třeba zadat některé další informace prostřednictvím parametrů. Níže jsou klíčové parametry, které potřebujete znát:
- Klíč - zde musíte zadat sloupec, který chcete řadit. Pokud například chcete řadit sloupec A, musíte použít klíč: = Rozsah („A1“)
- Objednat - zde určíte, zda chcete řadit vzestupně nebo sestupně. Pokud například chcete řazení ve vzestupném pořadí, použijete Order: = xlAscending
- Záhlaví - zde určujete, zda má váš soubor dat záhlaví nebo ne. Pokud má záhlaví, třídění začíná od druhého řádku datové sady, jinak začíná od prvního řádku. Chcete -li určit, že vaše data mají záhlaví, použijete záhlaví: = xlYes
I když tyto tři postačí ve většině případů, můžete si přečíst více o parametrech v tomto článku.
Nyní se podívejme, jak pomocí metody Range.Sort ve VBA třídit data v aplikaci Excel.
Třídění jednoho sloupce bez záhlaví
Předpokládejme, že máte jeden sloupec bez záhlaví (jak je uvedeno níže).
Pomocí níže uvedeného kódu jej můžete řadit vzestupně.
Sub SortDataWithoutHeader () Range ("A1: A12"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Všimněte si toho, že jsem rozsah dat zadal ručně jako Rozsah („A1: A12“).
V případě, že může dojít ke změnám v datech a mohou být přidány/odstraněny hodnoty, můžete použít níže uvedený kód, který se automaticky upraví na základě vyplněných buněk v datové sadě.
Sub SortDataWithoutHeader () Range ("A1", Range ("A1"). End (xlDown)). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Všimněte si, že místo Range („A1: A12“) jsem použil Range („A1“, Range („A1“). End (xlDown)).
Tím zkontrolujete poslední po sobě vyplněnou buňku ve sloupci a zahrnete ji do řazení. V případě, že jsou mezery, bude brát v úvahu pouze data do první prázdné buňky.
Můžete také vytvořit pojmenovaný rozsah a použít tento pojmenovaný rozsah namísto odkazů na buňky. Pokud je například pojmenovaný rozsah DataSet, váš kód by nyní vypadal tak, jak je uvedeno níže.
Sub SortDataWithoutHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlNo End Sub
Nyní mi dovolte rychle vysvětlit parametry použité ve výše uvedených příkladech:
- Klíč 1: = Rozsah („A1“) - Určen A1, aby kód věděl, který sloupec má seřadit.
- Order1: = xlAscending - Zadal objednávku jako xlAscending. Pokud chcete, aby to bylo v sestupném pořadí, použijte xlDescending.
- Záhlaví: = xlNo - Určeno, že neexistují žádná záhlaví. Toto je také výchozí hodnota. Takže i když to vynecháte, vaše data budou seřazeny, protože nemají žádná záhlaví.
Zajímá vás, kam tento kód VBA vložit a jak spustit makro? Přečtěte si tento návod!
Třídění jednoho sloupce se záhlavím
V předchozím příkladu datová sada neměla záhlaví.
Když mají vaše data záhlaví, musíte to v kódu zadat, aby řazení mohlo začít od druhého řádku datové sady.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Níže je uveden kód, který bude data třídit sestupně na základě prodejů obchodů.
Dílčí rozsah SortDataWithHeader () ("DataRange"). Klíč řazení1: = Rozsah ("C1"), Order1: = xl Sestupně Konec Sub
Všimněte si, že jsem vytvořil pojmenovaný rozsah - „DataRange“ a tento pojmenovaný rozsah jsem použil v kódu.
Třídění více sloupců se záhlavími
V tomto kurzu jsme zatím viděli, jak třídit jeden sloupec (se záhlavími i bez nich).
Nyní, co když chcete řadit na základě více sloupců.
Například v níže uvedené sadě dat, co když chci nejprve řadit podle kódu státu a poté podle obchodu.
Zde je kód, který bude třídit více sloupců najednou.
Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Záhlaví = xlAno. Použít Konec S Koncovou Sub
Níže je uveden výsledek, který získáte.
Ve výše uvedeném příkladu jsou data nejprve tříděna podle kódu státu (sloupec A). Potom v datech stavového kódu je opět seřazen podle úložiště (sloupec B). Toto pořadí je určeno kódem, ve kterém ho uvádíte.
Třídění dat pomocí dvojitého kliknutí na záhlaví
Pokud vytváříte řídicí panel nebo chcete snadnější použití ve svých sestavách, můžete napsat kód VBA, který data roztřídí, když dvakrát kliknete na záhlaví.
Něco, jak je uvedeno níže:
Níže je uveden kód, který vám to umožní:
Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel as Boolean) Dim KeyRange As Range Dim ColumnCount As Integer 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"). Sort Key1: = KeyRange, Header: = xlYes End If End Sub
Všimněte si, že jsem vytvořil pojmenovaný rozsah („DataRange“) a použil jsem ho v kódu místo použití odkazů na buňky.
Jakmile poklepete na libovolné záhlaví, kód deaktivuje obvyklé funkce dvojitého kliknutí (což je přístup do režimu úprav) a při řazení dat použije tuto buňku jako klíč.
Všimněte si také, že od této chvíle bude tento kód třídit všechny sloupce pouze ve vzestupném pořadí.
Všimněte si, že dvojité kliknutí je spouštěč, který umožňuje Excelu spustit zadaný kód. Tyto spouště, jako je dvojité kliknutí, otevření sešitu, přidání nového listu, změna buňky atd., Se nazývají události a lze je použít ke spouštění maker v Excelu. Více o akcích Excel VBA si můžete přečíst zde.Kam tento kód vložit?
Tento kód musíte vložit do okna kódu listu, ve kterém chcete tuto funkci řazení dvojitým kliknutím.
Udělat toto:
- Klepněte pravým tlačítkem na kartu listu.
- Klikněte na Zobrazit kód.
- Vložte kód do okna kódu listu, ve kterém se nacházejí vaše data.
Co teď, když chcete seřadit první dva sloupce („Stav“ a „Uložit“) vzestupně, ale sloupec „Prodej“ sestupně.
Zde je kód, který to udělá:
Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel as Boolean) Dim KeyRange As Range Dim ColumnCount As Integer 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
Ve výše uvedeném kódu kontroluje, zda je buňka, na kterou se dvakrát klikne, záhlaví prodeje nebo ne. Pokud ano, přiřadí hodnotu xlDescending proměnné SortOrder, v opačném případě to udělá xlAscending.
Nyní to vezmeme o krok dále a při třídění v záhlaví ukážeme vizuální značku (šipka a barevná buňka).
Něco, jak je uvedeno níže:
Abych to získal, přidal jsem nový list a provedl v něm následující změny (můžete si stáhnout ukázkový soubor a postupovat podle něj):
- Název nového listu byl změněn na „BackEnd“.
- Do buňky B2 zadejte symbol šipky (za tímto účelem přejděte na Vložit a klikněte na možnost „Symbol“).
- Zkopírujte a vložte záhlaví ze sady dat do buňky A3: C3 na listu „Backend“.
- V buňce A4 použijte následující funkci: AC4:
= IF (A3 = $ C $ 1, A3 & "" & $ B $ 1, A3)
- Zbytek buněk se automaticky vyplní kódem VBA, když sloupec seřadíte dvojitým kliknutím na záhlaví.
Váš backendový list bude vypadat nějak takto:
Nyní můžete pomocí níže uvedeného kódu data seřadit poklepáním na záhlaví. Když dvakrát kliknete na záhlaví, automaticky se zobrazí šipka v textu záhlaví. Všimněte si, že jsem také použil podmíněné formátování pro zvýraznění buňky.
Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel as Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Zrušit = True Sheets ("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šimněte si, že tento kód funguje dobře pro konstrukci mých dat a sešitu. Pokud změníte strukturu dat, budete muset kód odpovídajícím způsobem upravit.
Stáhněte si ukázkový soubor
