Andmete sortimine Excelis VBA abil (samm-sammuline juhend)

Excelis on juba paar võimalust andmete kiireks sortimiseks.

Andmekogumit saate hõlpsasti sortida, kasutades lindil olevaid sortimisikoone või sortimisdialoogiboksi.

Miks peate siis teadma, kuidas seda VBA abil teha?

Teadmine, kuidas andmeid VBA abil sortida, võib olla kasulik, kui see on teie koodi osana lisatud. Oletame näiteks, et saate iga päev/nädalas andmekogumi, mille peate vormindama ja sorteerima kindlas järjekorras.

Saate luua makro, et seda kõike teie jaoks teha ühe klõpsuga. See säästab palju aega ja vaeva iga kord, kui seda teete.

Samuti saate Exceli juhtpaneelide loomisel viia Exceli sorteerimisvõime uuele tasemele, lubades kasutajal sortida andmeid lihtsalt päise topeltklõpsuga (nagu allpool näidatud).

Selle õpetuse loomise kohta käsitlen hiljem selles õpetuses. Teeme kõigepealt põhitõed selgeks.

Vahemiku mõistmine. Sortimismeetod Excel VBA -s

VBA abil sorteerimisel peate oma koodis kasutama meetodit Range.Sort.

Vahemik oleks andmed, mida proovite sortida. Näiteks kui sorteerite andmeid vormingus A1: A10, oleks „Vahemik” vahemik („A1: A10”).

Samuti saate luua nimega vahemiku ja kasutada seda lahtriviidete asemel. Näiteks kui loon lahtritele A1: A10 nimega vahemiku „DataRange”, siis saan kasutada ka vahemikku („DataRange”)

Sortimismeetodi puhul peate parameetrite kaudu esitama lisateavet. Allpool on peamised parameetrid, mida peate teadma:

• Võti - siin peate määrama veeru, mida soovite sortida. Näiteks kui soovite veergu A sortida, peate kasutama võtit: = Vahemik ("A1")
• Telli - siin saate määrata, kas soovite sorteerida kasvavas või kahanevas järjekorras. Näiteks kui soovite sorteerida kasvavas järjekorras, kasutage järjestust: = xlAscending
• Päis - siin saate määrata, kas teie andmekogumil on päised või mitte. Kui sellel on päised, algab sortimine andmekogumi teisest reast, muidu algab esimesest reast. Et määrata, kas teie andmetel on päised, kasutage päist: = xlJah

Kuigi enamikul juhtudel neist kolmest piisab, saate parameetrite kohta lähemalt lugeda sellest artiklist.

Nüüd vaatame, kuidas kasutada Range.Sort meetodit VBA -s andmete sortimiseks Excelis.

Üksiku veeru sortimine ilma päiseta

Oletame, et teil on üks veerg ilma päiseta (nagu allpool näidatud).

Kasutades allolevat koodi, saate seda sortida kasvavas järjekorras.

Alam SortDataWithoutHeader () Vahemik ("A1: A12"). Sortimisvõti1: = Vahemik ("A1"), Järjekord1: = xlTõusev, Päis: = xl

Pange tähele, et olen määranud andmevahemiku käsitsi vahemikuks („A1: A12”).

Juhul kui andmetes võib muutuda ja väärtusi lisada/kustutada, võite kasutada allolevat koodi, mis kohandub automaatselt andmekogumi täidetud lahtrite alusel.

Alam SortDataWithoutHeader () Vahemik ("A1", Vahemik ("A1"). Lõpp (xlDown)). Sortimisvõti1: = Vahemik ("A1"), Järjekord1: = xlTõusuv, Päis: = xl

Pange tähele, et vahemiku (“A1: A12”) asemel olen kasutanud vahemikku (“A1”, vahemik (“A1”). Lõpp (xlDown)).

See kontrollib viimast järjestikku täidetud lahtrit veerus ja lisab selle sortimisse. Tühikute korral arvestab see andmeid ainult esimese tühja lahtrini.

Samuti saate luua nimega vahemiku ja kasutada seda nimega vahemikku lahtriviidete asemel. Näiteks kui nimetatud vahemik on DataSet, oleks teie kood nüüd selline, nagu allpool näidatud.

Alam SortDataWithoutHeader () Vahemik ("DataRange"). Sortimisvõti1: = Vahemik ("A1"), Järjestus1: = xlTõusev, Päis: = xl

Nüüd selgitan kiiresti ülaltoodud näidetes kasutatud parameetreid:

• Võti1: = Vahemik (“A1”) - määratud A1, et kood teaks, millist veergu sortida.
• Tellimus1: = xlAscending - määras tellimuse xlAscending. Kui soovite, et see oleks kahanevas järjekorras, kasutage nuppu xlDescending.
• Päis: = xlNo - määratud, et päiseid pole. See on ka vaikeväärtus. Nii et isegi kui te selle välja jätate, sorteeritakse teie andmed, arvestades, et neil pole päiseid.

Huvitav, kuhu see VBA -kood panna ja kuidas makro käivitada? Lugege seda õpetust!

Ühe veeru sortimine päisega

Eelmises näites ei olnud andmekogul päist.

Kui teie andmetel on päised, peate selle koodis täpsustama, et sortimist saaks alustada andmekogumi teisest reast.

Oletame, et teil on allpool näidatud andmekogum:

Allpool on kood, mis sorteerib andmed kaupluste müügi põhjal kahanevas järjekorras.

Alam SortDataWithHeader () vahemik ("DataRange"). Sortimisvõti1: = vahemik ("C1"), järjekord1: = xl

Pange tähele, et olen loonud nimega vahemiku - „DataRange” ja kasutasin seda nimega vahemikku koodis.

Mitme veeru sortimine päistega

Siiani oleme selles õpetuses näinud, kuidas sortida ühte veergu (päistega ja ilma).

Mis siis saab, kui soovite sortida mitme veeru alusel.

Näiteks allpool olevas andmekogus, mis siis, kui tahan esmalt sortida olekukoodi ja seejärel poe järgi.

Siin on kood, mis sorteerib korraga mitu veergu.

Sub SortMultipleColumns () ActiveSheet.Sort .SortFields.Add Key: = Vahemik ("A1"), Järjestus: = xlTõusuv .SortFields.Add Key: = Vahemik ("B1"), Järjestus: = xlTõusev .SetRange vahemik ("A1 : C13 "). Pealkiri = xlJah. Rakenda lõpp lõpuga Alam

Allpool on tulemus, mille saate.

Ülaltoodud näites sorteeritakse andmed kõigepealt olekukoodi järgi (veerg A). Seejärel sorteeritakse need olekukoodides uuesti poe järgi (veerg B). Selle järjekorra määrab kood, milles seda mainite.

Andmete sortimine topeltklõpsuga päisel

Kui loote juhtpaneeli või soovite oma aruannetes lihtsamat kasutamist, võite kirjutada VBA -koodi, mis sorteerib andmed, kui topeltklõpsate päistel.

Midagi, nagu allpool näidatud:

Allpool on kood, mis võimaldab teil seda teha:

Privaatne alamtööleht_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 Siis Cancel = True Set KeyRange = Range (Target.Address) Range ("DataRange"). Sortimisvõti 1: = KeyRange, Header: = xl Jah End If End Sub

Pange tähele, et olen loonud nimega vahemiku („DataRange”) ja kasutanud seda koodis, mitte lahtriviiteid.

Niipea, kui topeltklõpsate mõnel päisel, keelab kood tavapärase topeltklõpsamise funktsiooni (mis on redigeerimisrežiimi sisenemine) ja kasutab seda lahtrit andmete sortimisel võtmena.

Pange tähele, et praeguse seisuga sorteerib see kood kõik veerud ainult kasvavas järjekorras.

Pange tähele, et topeltklõps on päästik, mis võimaldab Excelil käivitada määratud koodi. Neid käivitajaid, nagu topeltklõps, töövihiku avamine, uue töölehe lisamine, lahtri muutmine jne, nimetatakse sündmusteks ja neid saab kasutada Excelis makrode käivitamiseks. Lisateavet Exceli VBA sündmuste kohta saate lugeda siit.

Kuhu see kood panna?

Peate selle koodi kleepima selle lehe koodiaknasse, kus soovite seda topeltklõpsuga sortimise funktsiooni kasutada.

Selleks tehke järgmist.

• Paremklõpsake lehe vahekaarti.
• Klõpsake nuppu Kuva kood.
• Kleepige kood selle lehe koodiaknasse, kus teie andmed asuvad.

Mis siis saab, kui soovite kahte esimest veergu (olek ja pood) sortida kasvavas järjekorras, kuid veergu „Müük” kahanevas järjekorras.

Siin on kood, mis seda teeb:

Privaatne alamtööleht_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 Siis Cancel = True Set KeyRange = Range (Target.Address) If Target.Value = "Sales" Siis SortOrder = xlDescending Else SortOrder = xlAscending End If Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlYes, Order1: = SortOrder End If End Sub

Ülaltoodud koodis kontrollib see, kas topeltklõpsatud lahter on päis Müük või mitte. Kui jah, siis määrab see muutujale SortOrder väärtuse xlDescending, muidu muudab selle xlAscending.

Nüüd liigume selle võrra edasi ja näitame päises visuaalset markerit (nool ja värviline lahter), kui see on sorteeritud.

Midagi, nagu allpool näidatud:

Selle saamiseks olen lisanud uue töölehe ja teinud selles järgmised muudatused (saate näidisfaili alla laadida ja seda järgida):

• Uue lehe nimi on „BackEnd”.
• Lahtris B2 sisestage noole sümbol (selleks minge sisesta ja klõpsake suvandit „Sümbol”).
• Kopeerige ja kleepige andmestiku päised lehe „Taustaprogramm” lahtrisse A3: C3.
• Kasutage lahtris A4 järgmist funktsiooni: AC4:

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

• Ülejäänud lahtrid täidetakse automaatselt VBA koodiga, kui topeltklõpsate päistel veeru sortimiseks.

Teie taustaleht näeks välja nagu allpool näidatud:

Nüüd saate andmete sortimiseks kasutada alltoodud koodi, topeltklõpsates päistel. Kui te topeltklõpsate päisel, saab see automaatselt päise teksti noole. Pange tähele, et olen ka lahtri esiletõstmiseks kasutanud tingimusvormingut.

Privaatne alamtööleht_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 Siis Cancel = True töölehed ("Backend"). Vahemik ("C1") = Target.Value Set KeyRange = Range (Target.Address) Range ("DataRange"). Sort Key1: = KeyRange, Header: = xlYes Worksheets ("BackEnd" "). Vahemik (" A1 ") = sihtmärgi veerg i = 1 kuni veergude arvu vahemik (" DataRange "). Lahtrid (1, i) .Väärtus = töölehed (" taustaprogramm "). Vahemik (" A4 "). Nihe (0, i - 1). Väärtus Järgmine i Lõpp Kui Lõpp Alam

Pange tähele, et see kood sobib hästi minu andmete ja töövihiku koostamiseks. Kui muudate andmete struktuuri, peate koodi vastavalt muutma.

Laadige alla näidisfail