Kā kārtot datus programmā Excel, izmantojot VBA (Soli pa solim)

Programmā Excel jau ir daži veidi, kā ātri kārtot datus.

Jūs varat viegli kārtot datu kopu, izmantojot lentes kārtošanas ikonas vai kārtošanas dialoglodziņu.

Tad kāpēc jums jāzina, kā to izdarīt, izmantojot VBA?

Zināšana, kā kārtot datus, izmantojot VBA, var būt noderīga, ja tā ir iekļauta jūsu koda daļā. Piemēram, pieņemsim, ka katru dienu/nedēļu saņemat datu kopu, kas jāformatē un jāšķiro noteiktā secībā.

Jūs varat izveidot makro, lai to visu paveiktu jūsu vietā ar vienu klikšķi. Tas ietaupīs daudz laika un pūļu katru reizi, kad to darīsit.

Turklāt, ja veidojat Excel informācijas paneļus, varat pārvietot Excel šķirošanas iespējas jaunā līmenī, ļaujot lietotājam kārtot datus, tikai veicot dubultklikšķi uz galvenes (kā parādīts zemāk).

Vēlāk šajā apmācībā es apskatīšu, kā to izveidot. Vispirms ātri noskaidrosim pamatus.

Diapazona izpratne. Kārtošanas metode programmā Excel VBA

Kārtojot, izmantojot VBA, kodā jāizmanto Range.Sort metode.

“Diapazons” būtu dati, kurus jūs mēģināt kārtot. Piemēram, ja jūs kārtojat datus A1: A10, tad “Diapazons” būtu diapazons (“A1: A10”).

Varat arī izveidot nosauktu diapazonu un izmantot to šūnu atsauču vietā. Piemēram, ja šūnām A1: A10 izveidoju nosauktu diapazonu “DataRange”, tad varu izmantot arī diapazonu (“DataRange”)

Izmantojot kārtošanas metodi, jums ir jāsniedz papildu informācija, izmantojot parametrus. Tālāk ir norādīti galvenie parametri, kas jums jāzina:

• Atslēga - šeit jānorāda kolonna, kuru vēlaties kārtot. Piemēram, ja vēlaties kārtot kolonnu A, jums jāizmanto atslēga: = Diapazons (“A1”)
• Pasūtīt - šeit jūs norādāt, vai vēlaties kārtot augošā vai dilstošā secībā. Piemēram, ja vēlaties kārtot augošā secībā, izmantojiet secību: = xlAscending
• Galvene - šeit jūs norādāt, vai jūsu datu kopai ir galvenes vai nav. Ja tai ir galvenes, šķirošana sākas no datu kopas otrās rindas, citādi tā sākas no pirmās rindas. Lai norādītu, ka jūsu datiem ir galvenes, izmantojiet galveni: = xlJā

Lai gan vairumā gadījumu ar šiem trim pietiek, jūs varat lasīt vairāk par parametriem šajā rakstā.

Tagad redzēsim, kā izmantot Range.Sort metodi VBA, lai kārtotu datus programmā Excel.

Vienas kolonnas kārtošana bez galvenes

Pieņemsim, ka jums ir viena kolonna bez galvenes (kā parādīts zemāk).

Jūs varat izmantot zemāk esošo kodu, lai to sakārtotu augošā secībā.

Sub SortDataWithoutHeader () Diapazons ("A1: A12"). Kārtošanas atslēga1: = Diapazons ("A1"), Kārtība1: = xlPieaugoši, Galvene: = xl

Ņemiet vērā, ka esmu manuāli norādījis datu diapazonu kā diapazonu (“A1: A12”).

Gadījumā, ja var rasties izmaiņas datos un vērtības var tikt pievienotas/dzēstas, varat izmantot tālāk norādīto kodu, kas automātiski pielāgojas, pamatojoties uz aizpildītajām datu kopas šūnām.

Sub SortDataWithoutHeader () Diapazons ("A1", Diapazons ("A1"). Beigas (xlDown)). Kārtošanas atslēga1: = Diapazons ("A1"), Pasūtījums1: = xlApscending, Header: = xlNo End Sub

Ņemiet vērā, ka diapazona (“A1: A12”) vietā esmu izmantojis diapazonu (“A1”, diapazons (“A1”). Beigas (xlDown)).

Tādējādi tiks pārbaudīta pēdējā kolonnā pēc kārtas aizpildītā šūna un iekļauta to šķirošanā. Ja ir tukšas vietas, dati tiks ņemti vērā tikai līdz pirmajai tukšajai šūnai.

Varat arī izveidot nosauktu diapazonu un izmantot šo nosaukto diapazonu šūnu atsauču vietā. Piemēram, ja nosauktais diapazons ir DataSet, jūsu kods tagad būtu tāds, kā parādīts zemāk.

Sub SortDataWithoutHeader () Diapazons ("DataRange"). Kārtošanas atslēga1: = Diapazons ("A1"), Order1: = xlAscendējošs, Header: = xl

Tagad ļaujiet man ātri izskaidrot iepriekš minētajos piemēros izmantotos parametrus:

• Key1: = Diapazons (“A1”) - norādīts A1, lai kods zinātu, kuru kolonnu kārtot.
• Pasūtījums1: = xlAscending - norādījis pasūtījumu kā xlAscending. Ja vēlaties, lai tas būtu dilstošā secībā, izmantojiet xlDescending.
• Galvene: = xlNo - norādīts, ka nav galvenes. Šī ir arī noklusējuma vērtība. Tātad, pat ja to izlaidīsit, jūsu dati tiks sakārtoti, ņemot vērā, ka tiem nav galvenes.

Domājat, kur ievietot šo VBA kodu un kā palaist makro? Izlasiet šo pamācību!

Vienas kolonnas šķirošana ar galveni

Iepriekšējā piemērā datu kopai nebija galvenes.

Ja jūsu datiem ir galvenes, jums tas jānorāda kodā, lai šķirošanu varētu sākt no datu kopas otrās rindas.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:

Zemāk ir kods, kas sakārtos datus dilstošā secībā, pamatojoties uz veikalu pārdošanas apjomiem.

Sub SortDataWithHeader () Diapazons ("DataRange"). Kārtošanas atslēga1: = Diapazons ("C1"), Order1: = xlDescending End Sub

Ņemiet vērā, ka esmu izveidojis nosauktu diapazonu - “DataRange” un izmantoju šo nosaukto diapazonu kodā.

Vairāku kolonnu šķirošana ar galvenēm

Līdz šim šajā apmācībā mēs esam redzējuši, kā kārtot vienu kolonnu (ar galvenēm un bez tām).

Ko darīt, ja vēlaties kārtot, pamatojoties uz vairākām kolonnām.

Piemēram, tālāk norādītajā datu kopā, ko darīt, ja es vispirms vēlos kārtot pēc valsts koda un pēc tam pēc veikala.

Šeit ir kods, kas sakārtos vairākas kolonnas vienā piegājienā.

Sub SortMultipleColumns () Ar ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Virsraksts = xlJā .Pielietot beigas ar beigu apakšdaļu

Zemāk ir redzams rezultāts.

Iepriekš minētajā piemērā dati vispirms tiek sakārtoti pēc valsts koda (A sleja). Pēc tam valsts koda datos tas atkal tiek sakārtots pēc veikala (B sleja). Šo secību nosaka kods, kurā jūs to pieminējat.

Datu kārtošana, izmantojot dubultklikšķi uz galvenes

Ja veidojat informācijas paneli vai vēlaties vieglāk izmantot pārskatus, varat uzrakstīt VBA kodu, kas kārtos datus, veicot dubultklikšķi uz galvenēm.

Kaut kas, kā parādīts zemāk:

Zemāk ir kods, kas ļaus jums to izdarīt:

Privāta apakšdarblapa_BeforeDoubleClick (ByVal mērķis kā diapazons, atcelt kā Būla vērtība) Dim KeyRange kā diapazons Dim ColumnCount As Integer ColumnCount = Diapazons ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Atcelt = True Set KeyRange = Range (Target.Address) Range ("DataRange"). Kārtot Key1: = KeyRange, Header: = xlJa beigas, ja beigas Sub

Ņemiet vērā, ka esmu izveidojis nosauktu diapazonu (“DataRange”) un esmu to izmantojis kodā, nevis izmantojis šūnu atsauces.

Tiklīdz jūs veicat dubultklikšķi uz jebkuras galvenes, kods atspējo parasto dubultklikšķa funkcionalitāti (tas ir, lai iekļūtu rediģēšanas režīmā) un izmanto šo šūnu kā atslēgu, kārtojot datus.

Ņemiet vērā arī to, ka ar šo kodu visas kolonnas tiks kārtotas tikai augošā secībā.

Ņemiet vērā, ka dubultklikšķis ir aktivizētājs, kas ļauj programmai Excel palaist norādīto kodu. Šos aktivizētājus, piemēram, dubultklikšķi, darbgrāmatas atvēršanu, jaunas darblapas pievienošanu, šūnas maiņu utt., Sauc par notikumiem, un tos var izmantot, lai programmā Excel palaistu makro. Vairāk par Excel VBA notikumiem varat lasīt šeit.

Kur ievietot šo kodu?

Šis kods ir jāielīmē tās lapas koda logā, kurā vēlaties izmantot šo dubultklikšķa kārtošanas funkciju.

Lai to izdarītu:

• Ar peles labo pogu noklikšķiniet uz lapas cilnes.
• Noklikšķiniet uz Skatīt kodu.
• Ielīmējiet kodu tās lapas koda logā, kurā atrodas jūsu dati.

Ko darīt, ja pirmās divas slejas (“Valsts” un “Veikals”) vēlaties kārtot augošā secībā, bet sleju “Pārdošana” - dilstošā secībā.

Šeit ir kods, kas to darīs:

Privāta apakšdarblapa_BeforeDoubleClick (ByVal mērķis kā diapazons, atcelt kā Būla) Dim KeyRange kā diapazons Dim ColumnCount As Integer ColumnCount = Diapazons ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Atcelt = True Set KeyRange = Range (Target.Address) If Target.Value = "Sales" Tad SortOrder = xlDescending Else SortOrder = xlAscendējošs beigu diapazons ("DataRange"). Kārtot Key1: = KeyRange, Header: = xlYes, Order1: = SortOrder End If End Sub

Iepriekš minētajā kodā tā pārbauda, ​​vai šūna, uz kuras tiek dubultklikšķināts, ir galvene Pārdošana. Ja jā, tas piešķir xlDescending vērtību mainīgajam SortOrder, pretējā gadījumā tas padara to xlAscending.

Tagad turpināsim šo pakāpi un parādīsim vizuālu marķieri (bultiņa un krāsaina šūna) galvenē, kad tas ir sakārtots.

Kaut kas, kā parādīts zemāk:

Lai to iegūtu, esmu pievienojis jaunu darblapu un veicis tajā šādas izmaiņas (varat lejupielādēt parauga failu un sekot līdzi):

• Jaunās lapas nosaukums tika mainīts uz “BackEnd”.
• Šūnā B2 ievadiet bultiņas simbolu (lai to izdarītu, dodieties uz Ievietot un noklikšķiniet uz opcijas “Simbols”).
• Kopējiet un ielīmējiet galvenes no datu kopas lapas "Backend" šūnā A3: C3.
• Šūnā A4 izmantojiet šādu funkciju: AC4:

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

• Pārējās šūnas automātiski tiks aizpildītas ar VBA kodu, veicot dubultklikšķi uz galvenēm, lai kārtotu kolonnu.

Jūsu aizmugures lapa izskatīsies kā parādīts zemāk:

Tagad jūs varat izmantot zemāk esošo kodu, lai kārtotu datus, veicot dubultklikšķi uz galvenēm. Veicot dubultklikšķi uz galvenes, tā automātiski iegūs bultiņu galvenes tekstā. Ņemiet vērā, ka esmu izmantojis arī nosacījuma formatējumu, lai izceltu arī šūnu.

Privāta apakšdarblapa_BeforeDoubleClick (ByVal mērķis kā diapazons, atcelt kā Būla vērtība) Dim KeyRange kā diapazons Dim ColumnCount As Integer ColumnCount = Diapazons ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Atcelt = Patiesas darblapas ("Backend"). Diapazons ("C1") = Target.Value Set KeyRange = Range (Target.Address) Range ("DataRange"). Kārtot Key1: = KeyRange, Header: = xlYes Worksheets ("BackEnd" "). Diapazons (" A1 ") = mērķa kolonna. I = 1 līdz kolonnas skaitļu diapazonam (" datu diapazons "). (0, i - 1). Vērtība Tālāk i Beigt Ja beigas Sub

Ņemiet vērā, ka šis kods labi darbojas manu datu un darbgrāmatas veidošanas veidā. Ja maināt datu struktūru, kods būs attiecīgi jāmaina.

Lejupielādējiet parauga failu