Andmete sortimine Excel VBA -s
Excelil on suurepärased vahendid tabeliandmete sortimiseks Exceli esiotsa lindi abil ja mingil hetkel soovite tõenäoliselt seda funktsiooni kasutada oma VBA -koodis. Õnneks on seda väga lihtne teha.
Esiotsa dialoogiboksi leiate, klõpsates Exceli lindi vahekaardi „Andmed” rühmas „Sortimine ja filtreerimine” ikooni „Sortimine”. Esmalt peate valima tabeliandmete vahemiku.
Kohandatud sortimise dialoogiboksi kuvamiseks võite kasutada ka klahve Alt-A-S-S.
Sortimismeetodit on Exceli hilisemates versioonides oluliselt täiustatud. Varem oli sorteerimine piiratud kolme tasemega, kuid nüüd saate sisestada nii palju tasemeid kui vaja ja see kehtib ka VBA -s.
Saate lisada kõik pakutavad sortimisfunktsioonid dialoogis Excel Sort oma VBA -koodi. Sortimisfunktsioon Excelis on kiire ja kiirem kui miski muu, mida võiksite ise VBA -sse kirjutada, nii et kasutage seda funktsiooni.
Pange tähele, et kui teete sorteerimist VBA-s, jäävad sortimisparameetrid kasutajaliidese sortimise dialoogiboksis samaks. Need salvestatakse ka töövihiku salvestamisel.
Kui kasutaja valib sama tabeliandmete vahemiku ja klõpsab sortimisikoonil, näevad nad kõiki teie parameetreid, mis on sisestatud teie VBA -koodiga. Kui nad tahavad teha omamoodi disaini, peavad nad kõigepealt kustutama kõik teie sortimistasemed, mis on nende jaoks väga tüütu.
Samuti, kui te ei muuda oma koodi parameetreid ja tuginete vaikeväärtustele, võite avastada, et kasutaja on teinud muudatusi, mis kajastuvad teie VBA -sortides ja võivad anda ootamatuid tulemusi, mida võib olla väga raske siluda .
Õnneks on VBA-s olemas selge meetod kõigi sortimisparameetrite uuesti seadistamiseks, nii et kasutaja näeks puhta sortimise dialoogiboksi
| 1 | Töölehed ("Sheet1"). Sort.SortFields.Clear |
Hea tava on kustutada sortimisparameetrid VBA -s enne ja pärast sorteerimist.
Sortimismeetodi praktiline kasutamine VBA -s
Tabelisse andmete importimisel Excelisse on need sageli väga juhuslikus järjekorras. Selle võib importida CSV -failist (komaga eraldatud väärtused) või see võib pärineda lingilt andmebaasile või veebilehele. Te ei saa loota, et see on määratud järjekorras ühelt impordilt teisele.
Kui esitate neid andmeid oma töölehel kasutajale, võib kasutajal olla raske vaadata ja mõista tohutut hulka andmeid, mis on järjekorras igal pool. Nad võivad soovida andmeid rühmitada või nende teatud osi teise rakendusse lõigata ja kleepida.
Samuti võivad nad soovida näha näiteks kõrgeima palgaga töötajat või pikima teenistusega töötajat.
Kasutades VBA -s sortimismeetodit, saate pakkuda valikuid, mis võimaldavad kasutajal lihtsat sortimist.
Näidisandmed, mis näitavad VBA abil Exceli sorteerimist
Esmalt peame töölehele sisestama mõned näidisandmed, et kood saaks näidata kõiki VBA -s saadaolevaid võimalusi.
Kopeerige need andmed töölehele (nimega „Sheet1”) täpselt nagu näidatud.
Pange tähele, et on kasutatud erinevaid lahtri taustavärve ja fondivärve, kuna neid saab kasutada ka sortimisparameetritena. Lahtrite ja fondivärvide abil sorteerimist demonstreeritakse hiljem artiklis. Pange tähele, et lahtris E3 on osakonna nimi väiketähtedega.
Kui te ei soovi lahtri- ja fondivärvi järgi sorteerimise näiteid kasutada, ei vaja te lahtri sisemust ja fondivärve.
Makro salvestamine VBA sortimiseks
Sortimiseks mõeldud VBA -kood võib muutuda üsna keeruliseks ja mõnikord võib olla hea mõte teha sortimine Exceli esiküljel ja salvestada makro, et näidata teile, kuidas kood töötab.
Kahjuks võib salvestusfunktsioon genereerida tohutul hulgal koodi, kuna see määrab praktiliselt kõik saadaolevad parameetrid, kuigi paljude parameetrite vaikeväärtused on teie sorteerimistoimingule vastuvõetavad.
Siiski annab see teile väga hea ettekujutuse VBA sortimiskoodi kirjutamisest ja üks eelis on see, et salvestatud kood töötab alati teie jaoks. Teie kood võib vajada testimist ja silumist, et see korralikult töötaks.
Pidage meeles, et VBA -s tehtud toimingu puhul pole tagasivõtmisfunktsiooni, seega on hea enne tabelite andmete koopia tegemist teisele töölehele enne sorteerimiskoodi kirjutamist alustada.
Näiteks kui tegite ülaltoodud näidisandmetel lihtsa sortimise, sorteerides töötaja järgi, genereeriks salvestus järgmise koodi:
| 123456789101112131415161718 | Alammakro1 ()Vahemik ("A1: E6"). ValigeActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Vahemik ("A2: A6"), _SortOn: = xlSortOnValues, järjestus: = xlTõusev, DataOption: = xlSortNormalActiveWorkbook.Worksheets ("Sheet1"). Sorteeri.SetRange vahemik ("A1: E6").Peal = xlJah.MatchCase = Vale.Orientation = xlTopToBottom.SortMethod = xlPinYin.KandidaLõpetaEnd Sub |
See on üsna suur kooditükk ja suur osa sellest on tarbetute parameetrite tõttu tarbetu. Kui aga teil on projekti lõpuleviimiseks ajaline surve ja vajate kiiret koodi, mis töötab, saate selle hõlpsalt oma VBA -koodi kleepida.
Kui aga soovite muuta oma koodi arusaadavaks ja elegantsemaks, on saadaval ka muid võimalusi.
VBA kood ühetasandilise sortimise tegemiseks
Kui soovite proovikoodi sortida vastavalt töötajale ainult makro salvestamisel nagu varem, on kood väga lihtne:
| 1234567 | Sub SingleLevelSort ()Töölehed ("Sheet1"). Sort.SortFields.ClearVahemik ("A1: E6"). Sortimisvõti1: = Vahemik ("A1"), päis: = xl JahEnd Sub |
Seda on palju lihtsam mõista kui salvestatud koodi, kuna see aktsepteerib vaikeseadeid, nt sorteerige kasvavalt, seega pole vaja parameetreid vaikeväärtustele seada. See eeldab, et olete eelnevalt kasutanud lauset „Selge”.
Algselt kasutatakse meetodit „Kustuta”, et tagada selle töölehe iga sortimisparameetri vaikeväärtused. Võimalik, et kasutaja on varem seadistanud parameetrid erinevatele väärtustele või VBA varasem sort on neid muutnud. Sorteerimisel on oluline alustada vaikeasendist, vastasel juhul võite kergesti saada valesid tulemusi.
Tühjendusmeetod ei lähtesta päise parameetrit ja see on soovitatav lisada oma koodi, vastasel juhul võib Excel proovida ära arvata, kas päise rida on olemas või mitte.
Käivitage see kood näidisandmetega ja teie tööleht näeb välja selline:
VBA kood mitmetasandilise sortimise tegemiseks
Saate oma koodile lisada nii palju sortimistasemeid kui vaja. Oletame, et soovite sortida kõigepealt osakonna ja seejärel alguskuupäeva järgi, kuid osakonna kasvavas järjekorras ja alguskuupäeva alanevas järjekorras:
| 12345678 | Sub MultiLevelSort ()Töölehed ("Sheet1"). Sort.SortFields.ClearVahemik ("A1: E6"). Sortimisvõti1: = vahemik ("E1"), võti2: = vahemik ("C1"), päis: = xlJah, _Järjekord1: = xlTõusev, Järjekord2: = xlAlanduvEnd Sub |
Pange tähele, et sortimislauses on nüüd kaks võtit (Key1 ja Key2). Võti 1 (osakonna veerg E) sorteeritakse kõigepealt ja seejärel võti 2 (alguskuupäeva veerg C) esimese sortimise alusel.
Samuti on kaks tellimuse parameetrit. Tellimus 1 seostub võtmega 1 (osakond) ja tellimus 2 seostub võtmega 2 (alguskuupäev). Oluline on tagada, et võtmed ja tellimused oleksid üksteisega sammu all.
Käivitage see kood näidisandmetega ja teie tööleht näeb välja selline:
Osakonna veerg (E) on kasvavas järjekorras ja veerg Alguskuupäev (C) kahanevas järjekorras.
Seda tüüpi mõju on kõige märgatavam, kui vaadata Jane Halfacre'i (rida 3) ja John Sutherlandi (rida 4). Mõlemad on rahanduses, kuid Jane Halfacre alustas enne John Sutherlandi ja kuupäevad on näidatud kahanevas järjekorras.
Kui tabeliandmete vahemik võib olla mis tahes pikkusega, saate sortimisvahemiku määratlemiseks kasutada objekti UsedRange. See toimib ainult siis, kui töölehel on ainult tabeli andmed, kuna kõik väärtused väljaspool andmeid annavad ridade ja veergude arvu kohta valesid tulemusi.
| 1234567 | Sub MultiLevelSort ()Töölehed ("Sheet1"). Sort.SortFields.ClearTöölehed ("Sheet1"). UsedRange.Sort Key1: = Vahemik ("E1"), Key2: = Vahemik ("C1"), Päis: = xlJah, _Järjekord1: = xlTõusev, Järjekord2: = xlAlanduvEnd Sub |
See hoiab ära probleemi, kui kasutate sortimisvahemiku määratlemiseks meetodit „End (xlDown)”. Kui andmete keskel on tühi lahter, siis pärast tühja lahtrit midagi ei kaasata, samas kui UsedRange langeb töölehe viimase aktiivse lahtrini.
Lahtrite värvi järgi sorteerimine
Alates Excel 2007 -st on lahtri taustavärvi järgi sortimine nüüd võimalik, mis annab tohutu paindlikkuse VBA -s sortimiskoodi kujundamisel.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Töölehed ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Vahemik ("A2: A6"), _SortOn: = xlSortOnCellColor, järjekord: = xlTõusev, andmete valik: = xlSortNormalActiveWorkbook.Worksheets ("Sheet1"). Sorteeri.SetRange vahemik ("A1: E6").KandidaLõpetaEnd Sub |
See kood sorteerib prooviandmete vahemiku (A2: A6) lahtri taustavärvi alusel. Pange tähele, et nüüd on olemas täiendav parameeter nimega „SortOn”, mille väärtus on „xlSortOnCellColor”.
Pange tähele, et parameetrit „SortOn” saab kasutada ainult töölehe objekt, mitte vahemiku objekt.
Seetõttu on kood keerulisem kui lahtrite väärtusi kasutava sortimise korral.
See kood kasutab sortimisel võtmeväärtust, mis hõlmab kogu andmemahtu, kuid taustavärvi sortimise võtmena saate määrata üksikud veerud ja kasutada mitut taset, nagu varem näidatud.
Pärast selle koodi käivitamist näeb teie tööleht nüüd välja selline:
Sorteerimine fondi värvi järgi
Excel VBA sortimisfunktsioon pakub veelgi suuremat paindlikkust, kuna saate sortida fondivärvide järgi:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Töölehed ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Vahemik ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)ActiveWorkbook.Worksheets ("Sheet1"). Sorteeri.SetRange vahemik ("A1: E6").Peal = xlJah.Orientation = xlTopToBottom.KandidaLõpetaEnd Sub |
Fondivärvi järgi sorteerimise kood on palju keerulisem kui lahtri taustavärvi puhul. Parameetril „SortOn” on nüüd väärtus „xlSortOnFontColor”.
Pange tähele, et peate määrama orientatsiooni „xlTopToBottom” ja sortimiseks värvi. See on määratud RGB -terminites (punane, roheline, must), väärtused jäävad vahemikku 0–255.
Pärast selle koodi käivitamist prooviandmetega näeb teie tööleht nüüd välja selline:
Värvide sortimine VBA-s on palju keerulisem kui mitmetasandiline sortimine, kuid kui teie sorteerimiskood ei tööta (mis võib juhtuda, kui parameeter puudub või pole koodi õigesti sisestanud), saate alati salvestamise juurde tagasi pöörduda makro ja salvestatud koodi integreerimine oma VBA -sse.
Teiste parameetrite kasutamine VBA sortimisel
VBA -koodis saate sorteerimise kohandamiseks kasutada mitmeid valikulisi parameetreid.
Sorteeri
SortOn valib, kas sortimisel kasutatakse lahtrite väärtusi, lahtrite taustavärve või lahtrite fondivärve. Vaikesäte on Lahtri väärtused.
| 1 | SortOn = xlSortOnValues |
Telli
Järjestus valib, kas sorteerimine toimub kasvavas või kahanevas järjekorras. Vaikimisi on kasvav.
| 1 | Järjestus = xlTõusev |
DataOption
DataOption valib teksti ja numbrite sortimise viisi. Parameeter xlSortNormal sorteerib arv- ja tekstiandmed eraldi. Parameeter xlSortTextAsNumbers käsitleb teksti sortimise arvandmetena. Vaikimisi on xlSortNormal.
| 1 | DataOption = xlSortNormal |
Päis
Päis valib, kas tabeliandmete vahemikus on päise rida või mitte. Kui on päise rida, ei soovi te seda sortimisse lisada.
Parameetrite väärtused on xlYes, xlNo ja xlYesNoGuess. xlYesNoGuess jätab Exceli otsustada, kas päise rida on olemas, mis võib kergesti põhjustada ebajärjekindlaid tulemusi. Selle väärtuse kasutamine pole soovitatav.
Vaikeväärtus on XNo (andmetes puudub päise rida). Imporditud andmete puhul on tavaliselt päise rida, seega veenduge, et määrate selle parameetri väärtuseks xlJah.
| 1 | Päis = xlJah |
Tikutoos
See parameeter määrab, kas sort on tõstutundlik või mitte. Valiku väärtused on tõene või vale. Kui väärtus on Väär, loetakse väiketähtede väärtused samadeks kui suurtähed. Kui väärtus on Tõene, siis näitab sort sortimisel suur- ja väiketähtede vahelist erinevust. Vaikeväärtus on False.
| 1 | MatchCase = vale |
Orienteerumine
See parameeter määrab, kas sortimine toimub ridade kaudu allapoole või kõigi veergude kaudu. Vaikeväärtus on xlTopToBottom (sorteeri ridade kaupa). Kui soovite horisontaalselt sortida, saate kasutada xlLeftToRight. Sellised väärtused nagu xlRows ja xlColumns selle parameetri puhul ei tööta.
| 1 | Orientatsioon = xlTopToBottom |
SortMethod
Seda parameetrit kasutatakse ainult hiina keelte sortimiseks. Sellel on kaks väärtust, xlPinYin ja xlStroke. xlPinYin on vaikeväärtus.
xlPinYin sorteerib tähemärkide foneetilise hiina sortimisjärjestuse abil. xlStroke sorteerib iga märgi löökide arvu järgi.
Kui salvestate sortimismakro, lisatakse see parameeter alati koodi ja olete ilmselt mõelnud, mida see tähendab. Kuid kui te ei tegele hiina andmetega, on sellest vähe kasu.
| 1 | SortMethod = xlPinYin |
Tabeliandmete sortimiseks topeltklõpsuga sündmuse kasutamine
Kõigis funktsioonides, mille Microsoft lisas VBA sortimismeetoditesse, ei sisaldanud see lihtsat viisi, kuidas topeltklõpsata veeru päisel ja sortida kogu tabeli andmed selle konkreetse veeru alusel.
See on tõesti kasulik funktsioon ja selle tegemiseks on lihtne koodi kirjutada.
| 12345678910111213141516171819202122232425262728293031323334 | Privaatne alamleht_BeforeDoubleClick (ByVal Target as Range, Cancel as Boolean)"Eeldatakse, et andmed algavad lahtrist A1„Looge kolm muutujat, et jäädvustada valitud sihtveerg ning maksimaalne veerg ja rida _"tabeli andmedDim Col As Integer, RCol As Long, RRow As Long„Kontrollige, kas kasutaja on päisereal topeltklõpsanud - rida 1, muidu väljuge alammenüüstKui sihite. 1. rida ja seejärel väljuge alammenüüst'Jäädvustage tabeli andmevahemiku maksimaalsed read, kasutades objekti "UsedRange"RCol = ActiveSheet.UsedRange.Columns.Count'Jäädvustage tabeli andmevahemiku maksimaalsed veerud, kasutades objekti' UsedRange 'RRow = ActiveSheet.UsedRange.Rows.Count„Kontrollige, kas kasutaja pole topeltklõpsanud tabeliandmete vahemikust väljaspool olevat veerguKui Siht. Veerg> RCol Seejärel väljuge alammenüüst'Jäädvustage veerg, millel kasutaja on topeltklõpsanudCol = Siht. Veerg'Kustutage eelmised sortimisparameetridActiveSheet.Sort.SortFields.Clear'Sorteerige tabelivahemik objekti' UsedRange 'maksimaalsete ridade ja veergude järgi'Sorteerige tabeliandmed, kasutades sortimisvõtmena kasutaja poolt topeltklõpsatud veerguActiveSheet.Range (lahtrid (1, 1), lahtrid (RCol, RRow)). Sortimisvõti1: = lahtrid (1, veerg), päis: = xl Jah'Valige lahter A1 - see tagab, et kasutaja ei jää pärast sortimist muutmisrežiimi _'lõpetatudActiveSheet.Range ("A1"). ValigeEnd Sub |
See kood tuleb paigutada tabeli andmeid sisaldava lehe topeltklõpsu sündmusele. Selleks klõpsake Project Exploreri aknas (VBE ekraani vasakus ülanurgas) töölehe nime ja seejärel valige akna esimeses rippmenüüs „Tööleht”. Valige teises rippmenüüs „BeforeDoubleClick” ja saate seejärel oma koodi sisestada.
Pange tähele, et sellesse koodi ei ole kõvasti kodeeritud ühtegi nime, vahemikku ega lahtriviidet, välja arvatud kursori viimine lahtrisse A1 koodi lõpus. Kood on loodud selleks, et saada kogu vajalik teave lahtri koordinaatidelt, millel kasutaja on topeltklõpsanud, ja tabeliandmete vahemiku suurusest.
Pole tähtis, kui suur on tabeli andmete vahemik. Kood kogub endiselt kogu vajaliku teabe ja seda saab kasutada kõikjal teie töövihikus hoitavate andmete jaoks, ilma et peaksite väärtusi kõvasti kodeerima.
Ainus eeldus on, et tabeliandmetes on päise rida ja andmevahemik algab lahtrist A1, kuid andmevahemiku lähtepositsiooni saab koodi sees hõlpsasti muuta.
See uus sortimisfunktsioon avaldab igale kasutajale piisavalt muljet!
Sortimisfunktsiooni laiendamine VBA abil
Microsoft on võimaldanud tohutut paindlikkust sortimisel, kasutades laia valikut parameetreid. VBA raames saate seda siiski edasi arendada.
Oletame, et soovisite sortida kõik väärtused paksus kirjas oma andmete ülaossa. Excelis ei saa seda kuidagi teha, kuid selleks saate kirjutada VBA -koodi:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortByBold ()„Looge muutujad, et hoida tabeli andmete ridade ja veergude arvuDim RRow Long, RCol Long, N As Long„Lülitage ekraani värskendamine välja, et kasutaja ei näeks toimuvat - nad näevad _väärtusi muudetakse ja mõtle miksApplication.ScreenUpdating = Vale'Jäädvustage tabelite andmevahemiku veergude arvRCol = ActiveSheet.UsedRange.Columns.Count'Jäädvustage tabelite andmevahemiku ridade arvRRow = ActiveSheet.UsedRange.Rows.Count„Korda kõiki tabeliandmete vahemiku ridu, jättes päiserea tähelepanutaN = 2 jaoks RRow"Kui lahtril on paks font, asetage lahtri väärtuse ette 0 -väärtusKui ActiveSheet.Cells (N, 1). Font.Bold = True thenActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .ValueLõpp KuiJärgmine N.'Kustutage kõik varasemad sortimisparameetridActiveSheet.Sort.SortFields.Clear'Sorteeri tabeliandmete vahemik. Kõik algväärtusega 0 väärtused liiguvad ülesActiveSheet.Range (lahtrid (1, 1), lahtrid (RCol, RRow)). Sortimisvõti1: = lahtrid (1, 1), päis: = xl Jah„Korda kõiki tabeliandmete vahemiku ridu, jättes päiserea tähelepanutaN = 2 jaoks RRow'Kui lahtril on paks kiri, eemaldage lahtri väärtusest 0 ees olev väärtus lahtrist _"taastada esialgsed väärtusedKui ActiveSheet.Cells (N, 1). Font.Bold = True thenActiveSheet.Cells (N, 1) .Value = Mid (ActiveSheet.Cells (N, 1). Value, 2)Lõpp KuiJärgmine N.'Lülitage ekraani värskendamine uuesti sisseApplication.ScreenUpdating = TõsiEnd Sub |
Kood määrab tabeliandmete vahemiku suuruse objekti „UsedRange” abil ja seejärel kordab kõik selle sisemised read. Kui rasvane font leitakse, pannakse lahtri väärtuse ette null.
Seejärel toimub sorteerimine. Kuna sorteerimine on kasvavas järjekorras, läheb loendi tippu kõik, mille ees on null.
Seejärel kordab kood kõiki ridu ja eemaldab eesmised nullid, taastades andmete algväärtused.
See kood sorteerib kriteeriumina paksude fontide abil, kuid saate hõlpsalt kasutada ka teisi lahtriomadusi, nt kaldkirja, teksti punkti suurust, allajoonivat fonti, fondi nime jne.
