„Excel“ jau turi keletą būdų greitai surūšiuoti duomenis.
Galite lengvai rūšiuoti duomenų rinkinį naudodami rūšiavimo piktogramas juostelėje arba rūšiavimo dialogo lange.
Tada kodėl jums reikia žinoti, kaip tai padaryti naudojant VBA?
Žinojimas, kaip rūšiuoti duomenis naudojant VBA, gali būti naudingas, kai yra įtrauktas į jūsų kodo dalį. Pvz., Tarkime, kad kasdien/kas savaitę gaunate duomenų rinkinį, kurį turite formatuoti ir rūšiuoti tam tikra tvarka.
Galite sukurti makrokomandą, kad visa tai padarytumėte vienu paspaudimu. Tai sutaupys daug laiko ir pastangų kiekvieną kartą, kai tai padarysite.
Be to, jei kuriate „Excel“ prietaisų skydelius, galite perkelti „Excel“ rūšiavimo galimybes į naują lygį, leisdami vartotojui rūšiuoti duomenis tiesiog dukart spustelėdami antraštę (kaip parodyta žemiau).
Kaip tai sukurti, aptarsiu vėliau šioje pamokoje. Pirmiausia greitai išsiaiškinkime pagrindus.
Diapazono supratimas. Rūšiavimo metodas „Excel VBA“
Rūšiuodami naudodami VBA, savo kode turite naudoti Range.Sort metodą.
„Diapazonas“ būtų duomenys, kuriuos bandote rūšiuoti. Pvz., Jei rūšiuojate duomenis A1: A10, tada „Diapazonas“ būtų diapazonas („A1: A10“).
Taip pat galite sukurti pavadintą diapazoną ir jį naudoti vietoj ląstelių nuorodų. Pvz., Jei langeliams A1: A10 sukuriu pavadintą diapazoną „DataRange“, taip pat galiu naudoti diapazoną („DataRange“)
Naudodami rūšiavimo metodą, turite pateikti papildomos informacijos per parametrus. Žemiau yra pagrindiniai parametrai, kuriuos reikia žinoti:
- Raktas - čia reikia nurodyti stulpelį, kurį norite rūšiuoti. Pavyzdžiui, jei norite rūšiuoti A stulpelį, turite naudoti raktą: = Diapazonas („A1“)
- Įsakymas - čia nurodote, ar norite rūšiuoti didėjančia, ar mažėjančia tvarka. Pavyzdžiui, jei norite rūšiuoti didėjančia tvarka, naudosite tvarką: = xlAuncending
- Antraštė - čia nurodote, ar jūsų duomenų rinkinyje yra antraščių, ar ne. Jei jis turi antraštes, rūšiavimas prasideda nuo antros duomenų rinkinio eilutės, kitaip jis prasideda nuo pirmosios eilutės. Norėdami nurodyti, kad jūsų duomenys turi antraštes, naudokite antraštę: = xlYes
Nors daugeliu atvejų šių trijų pakanka, daugiau apie parametrus galite perskaityti šiame straipsnyje.
Dabar pažiūrėkime, kaip naudoti „Range.Sort“ metodą VBA, kad būtų galima rūšiuoti duomenis „Excel“.
Vieno stulpelio rūšiavimas be antraštės
Tarkime, kad turite vieną stulpelį be antraštės (kaip parodyta žemiau).
Žemiau esantį kodą galite rūšiuoti didėjančia tvarka.
Sub SortDataWithoutHeader () Diapazonas ("A1: A12"). Rūšiavimo raktas1: = Diapazonas ("A1"), Tvarka1: = xlAuncending, Header: = xl
Atminkite, kad duomenų diapazoną rankiniu būdu nurodžiau kaip diapazoną („A1: A12“).
Jei duomenys gali pasikeisti, o vertės gali būti pridėtos/ištrintos, galite naudoti žemiau esantį kodą, kuris automatiškai koreguojamas pagal užpildytas duomenų rinkinio ląsteles.
Sub SortDataWithoutHeader () Diapazonas ("A1", Diapazonas ("A1"). Pabaiga (xlDown)). Rūšiavimo raktas1: = Diapazonas ("A1"), Tvarka1: = xlAuncending, Header: = xl
Atminkite, kad vietoj diapazono („A1: A12“) naudojau diapazoną („A1“, diapazoną („A1“). Pabaiga (xlDown)).
Taip bus patikrintas paskutinis iš eilės užpildytas stulpelio langelis ir įtrauktas į rūšiavimą. Jei yra tuščių, ji atsižvelgs tik į duomenis iki pirmo tuščio langelio.
Taip pat galite sukurti pavadintą diapazoną ir vietoj ląstelių nuorodų naudoti tą pavadintą diapazoną. Pavyzdžiui, jei pavadintas diapazonas yra „DataSet“, jūsų kodas dabar bus toks, kaip parodyta žemiau.
Sub SortDataWithoutHeader () Diapazonas („DataRange“). Rūšiavimo raktas1: = Diapazonas („A1“), Tvarka1: = xlAuncending, Header: = xl
Dabar leiskite man greitai paaiškinti aukščiau pateiktuose pavyzdžiuose naudojamus parametrus:
- 1 raktas: = Diapazonas („A1“) - nurodytas A1, kad kodas žinotų, kurį stulpelį rūšiuoti.
- Užsakymas1: = xlAscending - nurodė užsakymą kaip xlAscending. Jei norite, kad jis būtų mažėjančia tvarka, naudokite xlDescending.
- Antraštė: = xlNo - nurodyta, kad nėra antraščių. Tai taip pat yra numatytoji vertė. Taigi, net jei to praleisite, jūsų duomenys bus rūšiuojami atsižvelgiant į tai, kad jie neturi antraščių.
Įdomu, kur įdėti šį VBA kodą ir kaip paleisti makrokomandą? Perskaitykite šią pamoką!
Vieno stulpelio rūšiavimas su antrašte
Ankstesniame pavyzdyje duomenų rinkinys neturėjo antraštės.
Kai jūsų duomenys turi antraštes, turite tai nurodyti kode, kad rūšiavimą būtų galima pradėti nuo antrosios duomenų rinkinio eilutės.
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:
Žemiau yra kodas, kuris surūšiuos duomenis mažėjančia tvarka, atsižvelgiant į parduotuvių pardavimus.
Sub SortDataWithHeader () diapazonas („DataRange“). Rūšiavimo raktas1: = Diapazonas („C1“), tvarka1: = xlDescending End Sub
Atminkite, kad sukūriau pavadintą diapazoną - „DataRange“ ir naudoju šį pavadintą diapazoną kode.
Kelių stulpelių rūšiavimas su antraštėmis
Iki šiol šioje pamokoje matėme, kaip rūšiuoti vieną stulpelį (su antraštėmis ir be jų).
Ką daryti, jei norite rūšiuoti pagal kelis stulpelius.
Pvz., Toliau pateiktame duomenų rinkinyje, ką daryti, jei pirmiausia noriu surūšiuoti pagal būsenos kodą, o tada pagal parduotuvę.
Čia yra kodas, kuris vienu metu surūšiuos kelis stulpelius.
Sub SortMultipleColumns () su ActiveSheet.Sort .SortFields.Add raktas: = Diapazonas ("A1"), Tvarka: = xlAugantis. : C13 "). Antraštė = xlTaip. Taikyti pabaigą su pabaiga
Žemiau yra rezultatas, kurį gausite.
Pirmiau pateiktame pavyzdyje duomenys pirmiausia surūšiuoti pagal būsenos kodą (A stulpelis). Tada būsenos kodo duomenyse jie vėl rūšiuojami pagal parduotuvę (B stulpelis). Šią tvarką lemia kodas, kuriame jį paminėjote.
Duomenų rūšiavimas naudojant antraštę dukart
Jei kuriate informacijos suvestinę arba norite, kad ataskaitose būtų lengviau naudotis, galite parašyti VBA kodą, kuris surūšiuos duomenis dukart spustelėjus antraštes.
Kažkas, kaip parodyta žemiau:
Žemiau yra kodas, leidžiantis tai padaryti:
Privatus antrinis darbalapis_BeforeDoubleClick („ByVal Target As Range“, atšaukti kaip „Boolean“) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Diapazonas („DataRange“). Columns.Count Cancel = False If Target.Row = 1 Ir Target.Column <= ColumnCount Tada Cancel = True Set KeyRange = Range (Target.Address) Range ("DataRange"). Rūšiuoti raktas 1: = KeyRange, Header: = xl Taip pabaiga Jei pabaiga
Atminkite, kad sukūriau pavadintą diapazoną („DataRange“) ir panaudojau jį kode, o ne naudoju langelio nuorodas.
Kai tik dukart spustelėsite bet kurią antraštę, kodas išjungs įprastą dvigubo paspaudimo funkciją (kuri turi patekti į redagavimo režimą) ir naudoja tą langelį kaip raktą rūšiuodama duomenis.
Taip pat atminkite, kad nuo šiol šis kodas surūšiuos visus stulpelius tik didėjimo tvarka.
Atminkite, kad dvigubas spustelėjimas yra aktyviklis, leidžiantis „Excel“ paleisti nurodytą kodą. Šie aktyvikliai, tokie kaip dukart spustelėti, atidaryti darbaknygę, pridėti naują darbalapį, pakeisti langelį ir pan., Vadinami įvykiais ir gali būti naudojami makrokomandoms paleisti „Excel“. Daugiau apie „Excel VBA“ renginius galite perskaityti čia.Kur dėti šį kodą?
Turite įklijuoti šį kodą į lapo, kuriame norite atlikti šio dvigubo paspaudimo rūšiavimo funkciją, kodo langą.
Padaryti tai:
- Dešiniuoju pelės mygtuku spustelėkite lapo skirtuką.
- Spustelėkite Peržiūrėti kodą.
- Įklijuokite kodą lapo, kuriame yra jūsų duomenys, kodo lange.
Ką daryti, jei norite surūšiuoti pirmuosius du stulpelius („Valstybė“ ir „Parduotuvė“) didėjančia tvarka, bet stulpelį „Pardavimas“ mažėjančia tvarka.
Štai kodas, kuris tai padarys:
Privatus antrinis darbalapis_BeforeDoubleClick („ByVal Target As Range“, atšaukti kaip „Boolean“) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Diapazonas („DataRange“). Columns.Count Cancel = False If Target.Row = 1 Ir Target.Column <= ColumnCount Tada Cancel = True Set KeyRange = Range (Target.Address) If Target.Value = "Sales" Tada SortOrder = xlDescending Else SortOrder = xlAscending end If Range ("DataRange"). Rūšiuoti 1 raktas: = KeyRange, Header: = xlYes, Order1: = SortOrder End If End Sub
Pirmiau pateiktame kode patikrinama, ar langelis, kurį du kartus spustelėja, yra pardavimo antraštė, ar ne. Jei taip, tada jis kintamajam „SortOrder“ priskiria xlDescending reikšmę, kitaip jis tampa xlAscending.
Dabar pažvelkime toliau ir parodyk vaizdinį žymeklį (rodyklę ir spalvotą langelį) antraštėje, kai jis bus surūšiuotas.
Kažkas, kaip parodyta žemiau:
Norėdami tai padaryti, pridėjau naują darbalapį ir padariau šiuos pakeitimus (galite atsisiųsti pavyzdinį failą ir sekti toliau):
- Pakeitė naujo lapo pavadinimą į „BackEnd“.
- B2 langelyje įveskite rodyklės simbolį (norėdami tai padaryti, eikite į Įterpti ir spustelėkite parinktį „Simbolis“).
- Nukopijuokite ir įklijuokite antraštes iš duomenų rinkinio į A3: C3 langelį „Backend“ lape.
- A4 langelyje naudokite šią funkciją: AC4:
= IF (A3 = $ 1 $, A3 ir "" & $ B $ 1, A3)
- Likusieji langeliai bus automatiškai užpildyti VBA kodu, kai dukart spustelėsite antraštes, kad rūšiuotumėte stulpelį.
Jūsų galinis lapas atrodys taip, kaip parodyta žemiau:
Dabar galite naudoti žemiau esantį kodą duomenims rūšiuoti dukart spustelėdami antraštes. Kai dukart spustelėsite antraštę, antraštės tekste bus automatiškai rodoma rodyklė. Atminkite, kad taip pat naudoju sąlyginį formatavimą, norėdamas paryškinti langelį.
Privatus antrinis darbalapis_BeforeDoubleClick („ByVal Target As Range“, atšaukti kaip „Boolean“) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Diapazonas („DataRange“). Columns.Count Cancel = False If Target.Row = 1 Ir Target.Column <= ColumnCount Tada Cancel = True Worksheets ("Backend"). Diapazonas ("C1") = Target.Value Set KeyRange = Range (Target.Address) Range ("DataRange"). Rūšiuoti Key1: = KeyRange, Header: = xlYes Worksheets ("BackEnd" "). Range (" A1 ") = Target.Column For i = 1 to ColumnCount Range (" DataRange "). Langeliai (1, i) .Verta = darbalapiai (" Backend "). Diapazonas (" A4 "). Poslinkis (0, i - 1). Vertė Kitas i Pabaiga Jei pabaiga Sub
Atminkite, kad šis kodas puikiai tinka mano duomenų ir darbaknygės sudarymui. Jei pakeisite duomenų struktūrą, turėsite atitinkamai pakeisti kodą.
Atsisiųskite pavyzdinį failą
