Tietojen lajittelu Excel VBA: ssa
Excelillä on erinomainen tapa lajitella erilaisia taulukkotietoja Excelin käyttöliittymän nauhan avulla, ja jossain vaiheessa haluat todennäköisesti käyttää tätä toimintoa VBA -koodisi sisällä. Onneksi tämä on erittäin helppo tehdä.
Käyttöliittymävalintaikkuna löytyy napsauttamalla Lajittele-kuvaketta Excel-valintanauhan Tietojen välilehden Lajittele ja suodata -ryhmässä. Sinun on ensin valittava joukko taulukkotietoja.
Voit myös käyttää Alt-A-S-S näyttääksesi mukautetun lajittelun valintaikkunan.
Lajittelumenetelmää on parannettu huomattavasti Excelin myöhemmissä versioissa. Lajittelu rajoitettiin aiemmin kolmeen tasoon, mutta voit nyt syöttää niin monta tasoa kuin tarvitset, ja tämä koskee myös VBA: ta.
Voit sisällyttää kaikki Excel -lajittelu -valintaikkunassa olevat lajittelutoiminnot VBA -koodiin. Excelin lajittelutoiminto on nopea ja nopeampi kuin mikään muu, mitä voisit kirjoittaa itse VBA: han, joten hyödynnä toiminto.
Huomaa, että kun teet lajittelun VBA: ssa, lajitteluparametrit pysyvät ennallaan käyttöliittymän lajitteluruudussa. Ne tallennetaan myös, kun työkirja tallennetaan.
Jos käyttäjä valitsee saman taulukkodatan ja napsauttaa Lajittele -kuvaketta, hän näkee kaikki parametrit, jotka olet syöttänyt VBA -koodillasi. Jos he haluavat tehdä jonkinlaisen oman suunnittelunsa, heidän on ensin poistettava kaikki lajittelutasosi, mikä on heille erittäin ärsyttävää.
Jos et muuta koodin parametreja ja luotat oletusarvoihin, saatat huomata, että käyttäjä on tehnyt muutoksia, jotka heijastuvat VBA -lajitteluun ja voivat antaa odottamattomia tuloksia, joita voi olla erittäin vaikea korjata .
Onneksi VBA: ssa on selkeä menetelmä kaikkien lajitteluparametrien asettamiseksi uudelleen, jotta käyttäjä näkee puhtaan lajittelun valintaikkunan
| 1 | Laskentataulukot ("Sheet1"). Lajittele |
On hyvä käytäntö tyhjentää lajitteluparametrit VBA: ssa ennen lajittelua ja sen jälkeen.
Lajittelumenetelmän käytännön käyttö VBA: ssa
Kun taulukkotiedot tuodaan Exceliin, ne ovat usein hyvin satunnaisessa järjestyksessä. Se voidaan tuoda CSV (pilkuilla erotetut arvot) -tiedostosta tai se voi olla peräisin linkistä tietokantaan tai verkkosivulle. Et voi luottaa siihen, että se on asetetussa järjestyksessä tuonnista toiseen.
Jos esität nämä tiedot laskentataulukossasi olevalle käyttäjälle, käyttäjän voi olla vaikea katsoa ja ymmärtää valtavaa määrää dataa, joka on järjestyksessä kaikkialla. He saattavat haluta ryhmitellä tiedot tai leikata ja liittää tietyt niiden osat toiseen sovellukseen.
He voivat myös haluta nähdä esimerkiksi eniten palkatun työntekijän tai pisimmän palvelun.
Käyttämällä VBA: n lajittelumenetelmää voit tarjota vaihtoehtoja, joiden avulla käyttäjä voi lajitella helposti.
Esimerkkitietoja Excel -lajittelun osoittamiseksi VBA: n avulla
Tarvitsemme ensin näytetietoja syötettäväksi laskentataulukkoon, jotta koodi voi osoittaa kaikki VBA: n käytettävissä olevat palvelut.
Kopioi nämä tiedot laskentataulukkoon (nimeltään Sheet1) täsmälleen kuvan mukaisesti.
Huomaa, että on käytetty erilaisia solun taustavärejä ja fontin värejä, koska niitä voidaan käyttää myös lajitteluparametreina. Lajittelu solu- ja kirjasinväreillä osoitetaan myöhemmin artikkelissa. Huomaa myös, että solussa E3 osaston nimi on kaikki pieniä kirjaimia.
Et tarvitse solun sisä- ja kirjasinvärejä, jos et halua käyttää esimerkkejä solun ja fontin värin mukaan lajittelusta.
Makron tallentaminen VBA -lajittelua varten
Lajittelun VBA -koodi voi muuttua melko monimutkaiseksi, ja joskus voi olla hyvä idea lajitella Excelin käyttöliittymässä ja tallentaa makro, joka näyttää kuinka koodi toimii.
Valitettavasti tallennustoiminto voi tuottaa valtavan määrän koodia, koska se asettaa lähes kaikki käytettävissä olevat parametrit, vaikka monien parametrien oletusarvot ovat lajittelutoiminnon hyväksymiä.
Se antaa sinulle kuitenkin erittäin hyvän käsityksen siitä, mitä VBA -lajittelukoodin kirjoittamiseen liittyy, ja yksi etu on, että tallennettu koodi toimii aina sinulle. Oma koodisi saattaa tarvita testausta ja virheenkorjausta, jotta se toimisi oikein.
Muista, että VBA: ssa tehdyssä toiminnossa ei ole kumoamisominaisuutta, joten on hyvä kopioida taulukkotiedot toiselle laskentataulukolle ennen kuin aloitat lajittelukoodin kirjoittamisen.
Jos esimerkiksi suoritat yksinkertaisen lajittelun yllä olevista esimerkkitiedoista, lajittelemalla työntekijän mukaan, tallenne luo seuraavan koodin:
| 123456789101112131415161718 | Alamakro1 ()Alue ("A1: E6"). ValitseActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Alue ("A2: A6"), _SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = xlSortNormalActiveWorkbook.Worksheets ("Sheet1"). Lajittele.SetRange Range ("A1: E6")Otsikko = xlKyllä.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.KäytäLopetaEnd Sub |
Tämä on melko suuri koodinpala, ja suuri osa siitä on tarpeetonta oletusparametrien vuoksi. Jos olet kuitenkin aikapaineessa projektin loppuunsaattamisessa ja tarvitset koodin nopeasti, joka toimii, voit liittää sen helposti omaan VBA -koodiin.
Jos haluat kuitenkin tehdä koodistasi ymmärrettävän ja tyylikkäämmän, käytettävissä on myös muita vaihtoehtoja.
VBA -koodi yhden tason lajitteluun
Jos haluat lajitella esimerkkikoodin työntekijän perusteella vain kuten aiemmin makron tallennuksen aikana, koodi on hyvin yksinkertainen:
| 1234567 | Sub SingleLevelSort ()Laskentataulukot ("Sheet1"). LajitteleAlue ("A1: E6"). Lajittelunäppäin1: = Alue ("A1"), Otsikko: = xlKylläEnd Sub |
Tämä on paljon helpompi ymmärtää kuin tallennettu koodi, koska se hyväksyy oletusarvot, esim. Lajittele nouseva, joten parametreja ei tarvitse asettaa oletusarvoihin. Tämä olettaa, että olet käyttänyt "Tyhjennä" -lausetta etukäteen.
Tyhjennä -menetelmää käytetään aluksi varmistamaan, että kaikki laskentataulukon lajittelutiedot palautetaan oletusarvoihin. Käyttäjä on saattanut asettaa parametrit aiemmin eri arvoihin tai aikaisempi VBA: n lajittelu on saattanut muuttaa niitä. On tärkeää aloittaa oletusasennosta lajiteltaessa, muuten saatat helposti saada vääriä tuloksia.
Tyhjennä -menetelmä ei nollaa otsikkoparametria, ja on suositeltavaa sisällyttää tämä koodiin, muuten Excel voi yrittää arvata, onko otsikkorivi olemassa vai ei.
Suorita tämä koodi näytetietoja vastaan ja laskentataulukosi näyttää tältä:
VBA-koodi monitasoiseen lajitteluun
Voit lisätä koodiin niin monta lajittelutasoa kuin tarvitaan. Oletetaan, että halusit lajitella ensin osaston ja sitten aloituspäivän mukaan, mutta osaston nousevassa järjestyksessä ja laskevassa järjestyksessä aloituspäivänä:
| 12345678 | Sub MultiLevelSort ()Laskentataulukot ("Sheet1"). LajitteleAlue ("A1: E6"). Lajittelunäppäin1: = Alue ("E1"), Avain2: = Alue ("C1"), Otsikko: = xlKyllä, _Järjestys1: = xlAscendating, Order2: = xlDescendendingEnd Sub |
Huomaa, että lajittelulausekkeessa on nyt kaksi avainta (avain1 ja avain2). Avain1 (osasto -sarake E) lajitellaan ensin ja sitten avain2 (aloituspäivä sarake C) ensimmäisen lajittelun perusteella.
Tilausparametreja on myös kaksi. Order1 liittyy Key1: ään (osasto) ja Order2 liittyy Key2: een (aloituspäivä). On tärkeää varmistaa, että avaimet ja tilaukset pidetään toistensa tasalla.
Suorita tämä koodi näytetietoja vastaan ja laskentataulukosi näyttää tältä:
Osasto -sarake (E) on nousevassa järjestyksessä ja aloituspäiväsarake (C) laskevassa järjestyksessä.
Tämän tyyppinen vaikutus näkyy parhaiten, kun tarkastellaan Jane Halfacrea (rivi 3) ja John Sutherlandia (rivi 4). Molemmat ovat rahoituksessa, mutta Jane Halfacre aloitti ennen John Sutherlandia ja päivämäärät näytetään laskevassa järjestyksessä.
Jos taulukkotietojen alue voi olla minkä tahansa pituinen, voit käyttää lajittelualuetta UsedRange -objektin avulla. Tämä toimii vain, jos laskentataulukossa on vain taulukkotiedot, koska kaikki tietojen ulkopuoliset arvot antavat vääriä tuloksia rivien ja sarakkeiden lukumäärälle.
| 1234567 | Sub MultiLevelSort ()Laskentataulukot ("Sheet1"). LajitteleLaskentataulukot ("Sheet1"). UsedRange.Sort Key1: = Range ("E1"), Key2: = Range ("C1"), Header: = xlYes, _Järjestys1: = xlAscendating, Order2: = xlDescendendingEnd Sub |
Tämä estää ongelman, jos määrität lajittelualueen menetelmällä End (xlDown). Jos tietojen keskellä on tyhjä solu, mitään tyhjän solun jälkeen ei sisällytetä, kun taas UsedRange laskee laskentataulukon viimeiseen aktiiviseen soluun.
Lajittelu solun värin mukaan
Excel 2007: stä lähtien lajittelu solun taustavärin mukaan on nyt mahdollista, mikä tarjoaa valtavan joustavuuden lajittelukoodin suunnittelussa VBA: ssa.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Laskentataulukot ("Sheet1"). LajitteleActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Alue ("A2: A6"), _SortOn: = xlSortOnCellColor, Order: = xl Kasvava, DataOption: = xlSortNormalActiveWorkbook.Worksheets ("Sheet1"). Lajittele.SetRange Range ("A1: E6").KäytäLopetaEnd Sub |
Tämä koodi lajittelee näytetietoalueen (A2: A6) solun taustavärin perusteella. Huomaa, että nyt on olemassa lisäparametri nimeltä SortOn, jonka arvo on xlSortOnCellColor.
Huomaa, että SortOn -parametria voi käyttää vain laskentataulukko -objekti, ei alue -objekti.
Tästä syystä koodi on monimutkaisempi kuin soluarvoja käyttävä lajittelu.
Tämä koodi käyttää avainarvoa lajittelulle, joka kattaa koko tietoalueen, mutta voit määrittää yksittäiset sarakkeet taustavärin lajittelun avaimeksi ja käyttää useita tasoja, kuten aiemmin on esitetty.
Tämän koodin suorittamisen jälkeen laskentataulukosi näyttää nyt tältä:
Lajittelu fontin värin mukaan
Excel VBA: n lajittelutoiminto tarjoaa entistä enemmän joustavuutta siinä, että voit lajitella fontin väreillä:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Laskentataulukot ("Sheet1"). LajitteleActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Range ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)ActiveWorkbook.Worksheets ("Sheet1"). Lajittele.SetRange Range ("A1: E6")Otsikko = xlKyllä.Orientation = xlTopToBottom.KäytäLopetaEnd Sub |
Fonttivärin mukaan lajittelukoodi on paljon monimutkaisempi kuin solun taustaväri. SortOn -parametrilla on nyt arvo xlSortOnFontColor.
Huomaa, että sinun on määritettävä suunta "xlTopToBottom" ja määritettävä väri lajiteltavaksi. Tämä on määritetty RGB -termeillä (punainen, vihreä, musta) ja arvot vaihtelevat 0: sta 255: een.
Kun olet suorittanut tämän koodin näytetietoja vastaan, laskentataulukosi näyttää nyt tältä:
Värien lajittelu VBA: ssa on paljon monimutkaisempaa kuin monitasoinen lajittelu, mutta jos lajittelukoodisi ei toimi (mikä voi tapahtua, jos parametri puuttuu tai et ole syöttänyt koodia oikein), voit aina palata tallennukseen makro ja integroi tallennettu koodi VBA -laitteeseesi.
Muiden parametrien käyttäminen VBA -lajittelussa
VBA -koodissasi voit muokata lajittelua useilla valinnaisilla parametreilla.
Lajittele
SortOn valitsee, käyttääkö lajittelu soluarvoja, solun taustavärejä vai solun kirjasinvärejä. Oletusasetus on Solun arvot.
| 1 | SortOn = xlSortOnValues |
Tilaus
Järjestys valitsee, tehdäänkö lajittelu nousevassa vai laskevassa järjestyksessä. Oletusasetus on nouseva.
| 1 | Järjestys = xl |
DataOption
DataOption valitsee, miten teksti ja numerot lajitellaan. Parametri xlSortNormal lajittelee numeeriset ja tekstitiedot erikseen. Parametri xlSortTextAsNumbers käsittelee tekstiä lajittelun numeerisena datana. Oletus on xlSortNormal.
| 1 | DataOption = xlSortNormal |
Otsikko
Otsikko valitsee, onko taulukkotietoalueella otsikkorivi vai ei. Jos otsikkorivi on olemassa, et halua, että tämä sisällytetään lajitteluun.
Parametriarvot ovat xlYes, xlNo ja xlYesNoGuess. xlYesNoGuess jättää Excelin päätettäväksi, onko otsikkoriviä, mikä voi helposti johtaa epäjohdonmukaisiin tuloksiin. Tämän arvon käyttöä ei suositella.
Oletusarvo on XNo (ei otsikkoriviä tiedoissa). Tuodun datan yhteydessä on yleensä otsikkorivi, joten varmista, että asetat tämän parametrin arvoksi xlYes.
| 1 | Otsikko = xlKyllä |
MatchCase
Tämä parametri määrittää, erottaako lajittelu kirjainkoon vai ei. Vaihtoehtoarvot ovat True tai False. Jos arvo on epätosi, pieniä kirjaimia pidetään samoina kuin isoja kirjaimia. Jos arvo on tosi, lajittelu näyttää eron isojen ja pienten kirjainten välillä lajittelun sisällä. Oletusarvo on False.
| 1 | MatchCase = False |
Suuntautuminen
Tämä parametri määrittää, suoritetaanko lajittelu alaspäin rivien läpi vai kaikkien sarakkeiden välillä. Oletusarvo on xlTopToBottom (lajittele rivit). Voit käyttää xlLeftToRight -toimintoa, jos haluat lajitella horisontaalisesti. Arvot, kuten xlRows ja xlColumns, eivät toimi tälle parametrille.
| 1 | Suunta = xlTopToBottom |
Lajittelutapa
Tätä parametria käytetään vain kiinalaisten kielten lajitteluun. Siinä on kaksi arvoa, xlPinYin ja xlStroke. xlPinYin on oletusarvo.
xlPinYin lajittelee käyttämällä foneettista kiinalaista merkkijärjestystä. xlStroke lajittelee kunkin merkin iskujen määrän mukaan.
Jos tallennat lajittelumakron, tämä parametri sisältyy aina koodiin, ja olet ehkä miettinyt, mitä se tarkoittaa. Kuitenkin, ellet käsittele kiinalaista dataa, siitä on vähän hyötyä.
| 1 | SortMethod = xlPinYin |
Kaksoisnapsauta tapahtuman käyttämistä taulukkotietojen lajitteluun
Kaikissa toiminnoissa, jotka Microsoft sisällytti VBA: n lajittelumenetelmiin, se ei sisältänyt yksinkertaista tapaa kaksoisnapsauttaa sarakkeen otsikkoa ja lajitella koko taulukkotiedot kyseisen sarakkeen perusteella.
Tämä on todella hyödyllinen ominaisuus, ja siihen on helppo kirjoittaa koodi.
| 12345678910111213141516171819202122232425262728293031323334 | Yksityinen alityöarkki_BeforeDoubleClick (ByVal Target As Range, Cancel as Boolean)'Oletetaan, että tiedot alkavat solusta A1'Luo kolme muuttujaa kaapataksesi valittu kohdesarake ja suurin sarake ja rivi _'taulukkotiedotDim Col As Integer, RCol Long, RRow Long'Tarkista, että käyttäjä on kaksoisnapsauttanut otsikkoriviä - rivi 1 muutoin poistu aliosastaJos kohde. Rivi 1 ja lopeta sitten alaOta talteen taulukon tietoalueen enimmäisrivit käyttämällä UsedRange -objektiaRCol = ActiveSheet.UsedRange.Columns.Count'Kaappaa taulukon tietoalueen suurimmat sarakkeet käyttämällä UsedRange -objektiaRRow = ActiveSheet.UsedRange.Rows.Count'Tarkista, ettei käyttäjä ole kaksoisnapsauttanut taulukon tietoalueen ulkopuolella olevaa sarakettaJos Target.Column> RCol Sitten Exit SubOta talteen sarake, jota käyttäjä on kaksoisnapsauttanutCol = Kohde. Sarake'Tyhjennä aiemmat lajitteluparametritActiveSheet.Sort.SortFields.ClearLajittele taulukkoväli käytettyjen alueiden enimmäisrivien ja -sarakkeiden mukaan'Lajittele taulukkotiedot käyttämällä saraketta, jota käyttäjä on kaksoisnapsauttanut lajitteluavaimenaActiveSheet.Range (Solut (1, 1), Solut (RCol, RRow)). Lajittelunäppäin 1: = Solut (1, sarake), Otsikko: = xl Kyllä'Valitse solu A1 - tämä varmistaa, että käyttäjä ei jää muokkaustilaan sen jälkeen, kun lajittelu on _'valmisActiveSheet.Range ("A1"). ValitseEnd Sub |
Tämä koodi on sijoitettava taulukon tiedot sisältävän taulukon kaksoisnapsautustapahtumaan. Voit tehdä tämän napsauttamalla laskentataulukon nimeä Project Explorer -ikkunassa (VBE-näytön vasemmassa yläkulmassa) ja valitsemalla sitten "Työarkki" koodi-ikkunan ensimmäisestä avattavasta valikosta. Valitse toisesta pudotusvalikosta "BeforeDoubleClick" ja voit kirjoittaa koodisi.
Huomaa, että tähän koodiin ei ole kovakoodattu mitään nimiä, alueita tai soluviittauksia, paitsi jos kohdistin siirretään soluun A1 koodin lopussa. Koodi on suunniteltu saamaan kaikki tarvittavat tiedot solukoordinaateista, joita käyttäjä on kaksoisnapsauttanut, ja taulukkodatan koosta.
Ei ole väliä kuinka suuri taulukkotietoalue on. Koodi kerää edelleen kaikki tarvittavat tiedot, ja sitä voidaan käyttää missä tahansa työkirjan sisällä olevissa tiedoissa ilman, että arvoja täytyy kovakoodata.
Ainoa oletus on, että taulukkotiedoissa on otsikkorivi ja että tietoalue alkaa solusta A1, mutta tietoalueen aloituspaikkaa voidaan helposti muuttaa koodin sisällä.
Kaikki uudet käyttäjät ovat vaikuttuneita tästä uudesta lajittelutoiminnosta!
Lajittelutoiminnon laajentaminen VBA: n avulla
Microsoft on sallinut valtavan joustavuuden lajittelussa monenlaisten parametrien avulla. VBA: ssa voit kuitenkin viedä tätä pidemmälle.
Oletetaan, että halusit lajitella kaikki arvot lihavoidulla fontilla tietojen alkuun. Tätä ei voi tehdä Excelissä, mutta voit kirjoittaa VBA -koodin:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | AlalajitteluByBold ()'Luo muuttujia, jotka säilyttävät taulukkotietojen rivien ja sarakkeiden määränDim RR Kuinka kauan, RCol niin pitkä, N yhtä pitkä'Poista näytön päivitys käytöstä, jotta käyttäjä ei voi nähdä mitä tapahtuu - he voivat nähdä _'arvot muuttuvat ja ihmettele miksiApplication.ScreenUpdating = Väärä'Kaappaa taulukon tietoalueen sarakkeiden määräRCol = ActiveSheet.UsedRange.Columns.Count'Kaappaa taulukon tietoalueen rivien määräRRow = ActiveSheet.UsedRange.Rows.Count'Toista kaikki taulukon tietoalueen rivit huomiotta otsikkoriviäN = 2 RRow'Jos solussa on lihavoitu kirjasin, aseta solun arvoa edeltävä 0 -arvoJos ActiveSheet.Cells (N, 1) .Font.Bold = True thenActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .ArvoLoppu JosSeuraava N.'Tyhjennä aiemmat lajitteluparametritActiveSheet.Sort.SortFields.Clear'Lajittele taulukkotietoalue. Kaikki arvot, joiden alussa on 0 -arvo, siirtyvät alkuunActiveSheet.Range (Solut (1, 1), Solut (RCol, RRow)). Lajittelunäppäin1: = Solut (1, 1), Otsikko: = xl Kyllä'Toista kaikki taulukon tietoalueen rivit huomiotta otsikkoriviäN = 2 RRow'Jos solussa on lihavoitu kirjasin, poista alkuarvo 0 solun arvosta arvoon _'palauttaa alkuperäiset arvotJos ActiveSheet.Cells (N, 1) .Font.Bold = True thenActiveSheet.Cells (N, 1). Value = Mid (ActiveSheet.Cells (N, 1). Value, 2)Loppu JosSeuraava N.'Ota näytön päivitys uudelleen käyttöönApplication.ScreenUpdating = TottaEnd Sub |
Koodi selvittää taulukkotietoalueen koon "UsedRange" -objektin avulla ja toistaa sitten kaikki sen rivit. Kun lihavoitu kirjasin löytyy, solun arvon eteen asetetaan nolla.
Silloin tapahtuu lajittelu. Koska lajittelu on nousevassa järjestyksessä, kaikki, joiden edessä on nolla, menee listan yläreunaan.
Koodi sitten toistaa kaikki rivit ja poistaa etunollat ja palauttaa tiedot alkuperäisiin arvoihinsa.
Tämä koodi lajittelee käyttämällä lihavoituja fontteja kriteerinä, mutta voit helposti käyttää muita solun ominaisuuksia samalla tavalla, kuten kursivoitu fontti, tekstin pistekoko, alaviiva fontti, fontin nimi jne.
