Tietojen lajitteleminen Excelissä VBA: n avulla (vaiheittainen opas)

Excelissä on jo pari tapaa lajitella tiedot nopeasti.

Voit lajitella tietojoukon helposti käyttämällä valintanauhan lajittelukuvakkeita tai lajitteluvalintaikkunaa.

Miksi sitten sinun täytyy tietää, miten tämä tehdään VBA: n avulla?

Tietojen lajittelu VBA: n avulla voi olla hyödyllistä, kun ne sisältyvät osaksi koodiasi. Oletetaan esimerkiksi, että saat tietojoukon päivittäin/viikoittain, jotka sinun on muotoiltava ja lajiteltava tietyssä järjestyksessä.

Voit luoda makron tekemään kaiken tämän puolestasi yhdellä napsautuksella. Se säästää paljon aikaa ja vaivaa joka kerta, kun teet sen.

Jos luot Excel-hallintapaneeleja, voit myös viedä Excelin lajittelukyvyn uudelle tasolle antamalla käyttäjän lajitella tiedot vain kaksoisnapsauttamalla otsikkoa (kuten alla on esitetty).

Aion käsitellä tämän luomista myöhemmin tässä opetusohjelmassa. Selvitetään ensin perusteet nopeasti.

Lajittelutapa Excel VBA: ssa

Kun lajittelet VBA: lla, sinun on käytettävä koodisi Range.Sort -menetelmää.

"Alue" olisi tiedot, joita yrität lajitella. Jos esimerkiksi lajittelet tiedot kohdassa A1: A10, "Range" on Range ("A1: A10").

Voit myös luoda nimetyn alueen ja käyttää sitä soluviittausten sijaan. Jos esimerkiksi luon nimetyn alueen DataRange soluille A1: A10, voin käyttää myös Range (”DataRange”)

Lajittelumenetelmän avulla sinun on annettava lisätietoja parametrien kautta. Alla on tärkeimmät parametrit, jotka sinun on tiedettävä:

• Avain - tässä sinun on määritettävä sarake, jonka haluat lajitella. Jos esimerkiksi haluat lajitella sarakkeen A, sinun on käytettävä näppäintä: = Alue ("A1")
• Tilaus - tässä määrität, haluatko lajittelun nousevassa vai laskevassa järjestyksessä. Jos esimerkiksi haluat lajitella nousevassa järjestyksessä, käytä järjestystä: = xlAscending
• Otsikko - tässä määrität, onko tietojoukossasi otsikoita vai ei. Jos sillä on otsikot, lajittelu alkaa tietojoukon toiselta riviltä, ​​muuten se alkaa ensimmäiseltä riviltä. Voit määrittää, että tiedoissasi on otsikoita, käyttämällä Header: = xlYes

Vaikka nämä kolme riittävät useimmissa tapauksissa, voit lukea lisää parametreista tässä artikkelissa.

Katsotaanpa nyt, kuinka käyttää Range.Sort -menetelmää VBA: ssa tietojen lajittelemiseksi Excelissä.

Yhden sarakkeen lajittelu ilman otsikkoa

Oletetaan, että sinulla on yksi sarake ilman otsikkoa (kuten alla).

Voit lajitella sen nousevassa järjestyksessä alla olevan koodin avulla.

AlilajitteluDataWithoutHeader () -alue ("A1: A12"). Lajitteluavain1: = Alue ("A1"), Järjestys1: = xlAscendating, Header: = xlEn End Sub

Huomaa, että olen määrittänyt tietoalueen manuaalisesti alueeksi (”A1: A12”).

Jos tietoihin saattaa tulla muutoksia ja arvoja voidaan lisätä/poistaa, voit käyttää alla olevaa koodia, joka mukautuu automaattisesti tietojoukon täytettyjen solujen perusteella.

AlilajittelutiedotWithoutHeader () -alue ("A1", alue ("A1"). End (xlDown)).

Huomaa, että Range (“A1: A12”) sijaan olen käyttänyt Range (“A1”, Range (“A1”). End (xlDown)).

Tämä tarkistaa sarakkeen viimeisen peräkkäin täytetyn solun ja sisällyttää sen lajitteluun. Jos tyhjiä on, se ottaa huomioon vain tiedot ensimmäiseen tyhjään soluun asti.

Voit myös luoda nimetyn alueen ja käyttää sitä nimettyä aluetta soluviittausten sijaan. Jos nimetty alue on esimerkiksi DataSet, koodisi on nyt alla.

AlilajitteluDataWithoutHeader () -alue ("DataRange"). Lajitteluavain1: = Alue ("A1"), Järjestys1: = xlAscendating, Header: = xlEn End Sub

Selitän nyt nopeasti yllä olevissa esimerkeissä käytetyt parametrit:

• Avain1: = Alue ("A1") - Määritetty A1, jotta koodi tietää, mikä sarake lajitellaan.
• Tilaus1: = xlAscending - Määritti tilauksen xlAscending. Jos haluat sen olevan laskevassa järjestyksessä, käytä xlDescending.
• Otsikko: = xlEi - Määritetty, ettei otsikoita ole. Tämä on myös oletusarvo. Joten vaikka jätät tämän pois, tietosi lajitellaan, koska niillä ei ole otsikoita.

Mietitkö, mihin tämä VBA -koodi laitetaan ja miten makro suoritetaan? Lue tämä opetusohjelma!

Yhden sarakkeen lajittelu otsikon avulla

Edellisessä esimerkissä tietojoukossa ei ollut otsikkoa.

Kun tiedoillasi on otsikot, sinun on määritettävä se koodissa, jotta lajittelu voidaan aloittaa tietojoukon toiselta riviltä.

Oletetaan, että sinulla on alla näkyvä tietojoukko:

Alla on koodi, joka lajittelee tiedot laskevassa järjestyksessä myymälöiden myynnin perusteella.

AlilajitteluDataWithHeader () -alue ("DataRange"). Lajittelunäppäin1: = Alue ("C1"), Järjestys1: = xlDescending End Sub

Huomaa, että olen luonut nimetyn alueen - "DataRange" ja käyttänyt tätä nimettyä aluetta koodissa.

Useiden sarakkeiden lajittelu otsikoilla

Toistaiseksi tässä opetusohjelmassa olemme nähneet kuinka lajitella yksi sarake (otsikoilla ja ilman).

Entä jos haluat lajitella useiden sarakkeiden perusteella?

Esimerkiksi alla olevassa tietojoukossa mitä jos haluan ensin lajitella tilakoodin ja sitten myymälän mukaan.

Tässä on koodi, joka lajittelee useita sarakkeita kerralla.

Sub SortMultipleColumns () ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Otsikko = xlKyllä. Käytä lopuksi lopussa

Alla on tulos, jonka saat.

Yllä olevassa esimerkissä tiedot lajitellaan ensin tilakoodin mukaan (sarake A). Sitten tilakooditiedoissa se lajitellaan jälleen myymälän mukaan (sarake B). Tämä järjestys määräytyy koodin mukaan, jossa mainitset sen.

Tietojen lajittelu kaksoisnapsauttamalla otsikkoa

Jos luot hallintapaneelia tai haluat helpompaa käyttöä raporteissasi, voit kirjoittaa VBA -koodin, joka lajittelee tiedot, kun kaksoisnapsautat otsikoita.

Jotain alla olevan kuvan mukaisesti:

Alla on koodi, jonka avulla voit tehdä tämän:

Yksityinen alityöarkki_BeforeDoubleClick (ByVal -tavoite alueena, peruuta totuusarvoksi) Dim KeyRange as Range Dim ColumnCount as Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 Ja Target.Column <= ColumnCount Sitten Cancel = True Set KeyRange = Range (Target.Address) Range ("DataRange").

Huomaa, että olen luonut nimetyn alueen (”DataRange”) ja käyttänyt sitä koodissa soluviittausten käyttämisen sijasta.

Heti kun kaksoisnapsautat mitä tahansa otsikkoa, koodi poistaa käytöstä tavalliset kaksoisnapsautustoiminnot (eli päästä muokkaustilaan) ja käyttää kyseistä solua avaimena tietoja lajiteltaessa.

Huomaa myös, että tällä hetkellä tämä koodi lajittelee kaikki sarakkeet vain nousevassa järjestyksessä.

Huomaa, että kaksoisnapsautus on liipaisin, jonka avulla Excel voi suorittaa määritetyn koodin. Näitä laukaisimia, kuten kaksoisnapsautus, työkirjan avaaminen, uuden laskentataulukon lisääminen, solun muuttaminen jne. Kutsutaan tapahtumiksi, ja niitä voidaan käyttää makrojen suorittamiseen Excelissä. Voit lukea lisää Excel VBA -tapahtumista täältä.

Minne tämä koodi laitetaan?

Sinun on liitettävä tämä koodi sen taulukon koodi -ikkunaan, jossa haluat tämän kaksoisnapsautuslajittelutoiminnon.

Tehdä tämä:

• Napsauta taulukon välilehteä hiiren kakkospainikkeella.
• Napsauta Näytä koodi.
• Liitä koodi sen taulukon koodi -ikkunaan, jossa tiedot ovat.

Entä jos haluat lajitella kaksi ensimmäistä saraketta ("State" ja "Store") nousevaan järjestykseen, mutta "Sales" -sarakkeen laskevaan järjestykseen.

Tässä on koodi, joka tekee sen:

Yksityinen alityöarkki_BeforeDoubleClick (ByVal -tavoite alueena, peruuta totuusarvoisena) Dim KeyRange as Range Dim ColumnCount as Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 Ja Target.Column <= ColumnCount Sitten Cancel = True Set KeyRange = Range (Target.Address) If Target.Value = "Sales" SortOrder = xlDescending Else SortOrder = xlAscendending End If Range ("DataRange"). = LajitteleTilaa loppu, jos loppu

Yllä olevassa koodissa se tarkistaa, onko kaksoisnapsautettu solu Myyntiotsikko vai ei. Jos kyllä, se määrittää xlDescending -arvon muuttujalle SortOrder, muuten se tekee siitä xlAscending.

Otetaan nyt tämä lovi pidemmälle ja näytetään otsikossa visuaalinen merkki (nuoli ja värillinen solu), kun se on lajiteltu.

Jotain alla olevan kuvan mukaisesti:

Saadaksesi tämän, olen lisännyt uuden laskentataulukon ja tehnyt siihen seuraavat muutokset (voit ladata esimerkkitiedoston ja seurata sitä):

• Muutettiin uuden taulukon nimeksi "BackEnd".
• Syötä soluun B2 nuolisymboli (voit tehdä tämän siirtymällä Lisää -kohtaan ja napsauttamalla vaihtoehtoa Symboli).
• Kopioi ja liitä otsikot tietojoukosta "Tausta" -arkin soluun A3: C3.
• Käytä seuraavaa toimintoa solussa A4: AC4:

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

• Loput solut täytetään automaattisesti VBA -koodilla, kun kaksoisnapsautat otsikoita lajitellaksesi sarakkeen.

Tausta -arkki näyttäisi seuraavanlaiselta:

Nyt voit käyttää alla olevaa koodia tietojen lajitteluun kaksoisnapsauttamalla otsikoita. Kun kaksoisnapsautat otsikkoa, se saa automaattisesti otsikon tekstin nuolen. Huomaa, että olen myös käyttänyt ehdollista muotoilua solun korostamiseen.

Yksityinen alityöarkki_BeforeDoubleClick (ByVal -tavoite alueena, peruuta totuusarvoksi) Dim KeyRange as Range Dim ColumnCount as Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 Ja Target.Column <= ColumnCount Sitten Peruuta = Todelliset laskentataulukot ("Backend"). Alue ("C1") = Target.Value Set KeyRange = Range (Target.Address) Range ("DataRange"). "). Alue (" A1 ") = Kohde.sarake Sarakkeelle i = 1 - sarakkeen laskualueelle (" DataRange "). Solut (1, i). Arvo = laskentataulukot (" tausta "). Alue (" A4 "). Siirtymä (0, i - 1). Arvo Seuraava i Lopeta Jos Lopeta Al

Huomaa, että tämä koodi toimii hyvin tietojen ja työkirjan rakentamisessa. Jos muutat tietojen rakennetta, sinun on muokattava koodia vastaavasti.

Lataa esimerkkitiedosto