Lajittelu eli järjestäminen

Vastaa

Tämän artikkelin esimerkit tiedostossa sort.xlsm

Range.Sort-menetelmä

Range.Sort on suoraviivaisin menetelmä laskentataulukon tietojen lajitteluun.  Sort-menetelmä lajittelee Range-objektin määrittelemän solualueen.

Seuraava ohjelma lajittelee aktiivisen solun ympäriltä löytyvän aineiston käyttäen lajitteluperusteena B-sarakkeen tietoja.

Sub MySort1()
Dim MyRange As Range
    Set MyRange = ActiveCell.CurrentRegion
    MyRange.sort Key1:=Range("B1"), Order1:=xlAscending, _
    Header:=xlYes, MatchCase:=True, Orientation:=xlSortColumns
End Sub

Range.Sort-menetelmän argumentteja

  • Key1 määrittää sarakkeen (tai rivin), jonka perusteella lajittelu suoritetaan
  • Order1 määrittää onko kyseessä nouseva xlAscending vai laskeva xlDescending järjestys
  • Header määrittää onko lajiteltavalla aineistolla otsikot xlYes vai ei xlNo. Jos aineistolla on otsikot, niin on tärkeää käyttää xlYes-arvoa, jotta otsikot pysyvät ensimmäisellä rivillä. Voin myös käyttää xlGuess, jolloin annan Excelin arvata, onko aineistolla otsikot.
  • MatchCase määrittää huomioidaanko isojen ja pienten kirjainte ero True vai ei False.
  • Orientation määrittää lajitellaanko sarakkeita xlSortColumns vai rivejä xlSortRows.

Muita harvemmin tarvittavia argumentteja löydät Range.Sort-menetelmän ohjetiedoista.

Tarvittaessa voin käyttää jopa kolmea lajitteluperustetta. Seuraavassa on kaksi lajitteluperustetta. Ensisijainen lajittelu suoritetaan B-sarakkeen perusteella nousevaan järjestykseen ja toissijainen lajittelu C-sarakkeen perusteella laskevaan järjestykseen.

Sub MySort2()
Dim MyRange As Range
    Set MyRange = ActiveCell.CurrentRegion
    MyRange.sort Key1:=Range("B1"), Order1:=xlAscending, _
    Key2:=Range("C1"), Order2:=xlDescending, Header:=xlYes, _
    MatchCase:=True, Orientation:=xlSortColumns
End Sub

Sort-objekti

Jos nauhoitat lajittelun, niin nauhoitetussa koodissa käytetään SortObjects-kokoelmaa, SortObject-objektia ja Sort-objektia. Näitä käytettäessä tarvitaan seuraavat vaiheet:

  • Laskentataulukon SortFields-kokoelman tyhjentäminen lauseella ActiveSheet.Sort.SortFields.Clear
  • Uusien SortField-objektien lisääminen SortFields-kokoelmaan lauseella ActiveSheet.Sort.SortFields.Add
  • Sort-objektin Apply-menetelmän käyttö

Seuraava ohjelma lajittelee aktiivisen solun ympäriltä löytyvän aineiston käyttäen ensisijaisena lajitteluperusteena B-sarakkeen tietoja ja toissijaisena lajitteluperusteena E-sarakkeen tietoja.

Sub MySort3()
Dim MyRange As Range
    Set MyRange = ActiveCell.CurrentRegion
    ActiveSheet.sort.SortFields.Clear
    ActiveSheet.sort.SortFields.Add Key:=Range("B1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending
    ActiveSheet.sort.SortFields.Add Key:=Range("E1"), _
    SortOn:=xlSortOnValues, Order:=xlDescending
    With ActiveSheet.sort
        .SetRange MyRange
        .Header = xlYes
        .MatchCase = True
        .Orientation = xlSortColumns
        .Apply
    End With
End Sub

SortField-objektille on tärkeää määrittää Key, SortOn ja Order -ominaisuudet. SortField-objektin avulla voit lajitella jopa värin perusteella, koska SortOn-ominaisuutena voi olla SortOnCellColor tai SortOnFontColor.

Sort-objektille on tärkeää määrittää lajiteltava solualue SetRange-menetelmällä.

Muita SortField-objektin ja Sort-objektin ominaisuuksia löydät ohjetoiminnosta.

Taulukkomuuttujan lajittelu

Taulukkomuuttujan (Array) arvot voit lajitella kirjoittamalla taulukkomuuttujan arvot laskentataulukkoon, jonka jälkeen voit käyttää edellä kuvattuja menetelmiä.

Joissain tilanteissa voi tuntua tarkoituksenmukaisemmalta lajitella taulukkomuuttuja kirjoittamatta sen arvoja laskentataulukkoon. VBA:ssa ei valitettavasti ole valmista menetelmää tätä varten. Tarvitset erillisen funktion, joka suorittaa lajittelun. Pienillä aineistoilla voit käyttää lajitteluun yksinkertaista BubbleSort-lajittelualgoritmia. Excel-tiedostosta sort.xlsm löydät BubbleSort-algoritmia soveltavan funktion, jonka olen kopioinut osoitteesta http://oco-carbon.com/2012/08/13/sorting-functions-for-vba/

Funktiota kutsutaan seuraavan kaltaisella lauseella

MyArray2 = BubbleSort(MyArray1, 2, True)

  • MyArray2 on taulukkomuuttuja, johon lajiteltu taulukkomuuttuja sijoitetaan
  • MyArray1 on lajiteltava taulukkomuuttuja
  • Kolmas argumentti kertoo kuinka monennen taulukkomuuttujan sarakkeen mukaan lajitellaan (esimerkissä 2. sarakkeen mukaan)
  • Viimeinen argumentti on True laskevalle järjestykselle ja False nousevalle järjestykselle.

Funktion käytössä täytyy huomioida, että taulukkomuuttujan indeksien oletetaan alkavan arvosta 0. Jos käytät taulukkomuuttujia, joiden indeksit alkavat arvosta 1, niin löydät sovitetun version BubbleSort1 tiedostosta sort.xlsm.

Jos lajiteltavana on tuhansia rivejä, niin BubbleSort on auttamatta liian hidas algoritmi. Tietoa ja keskustelua nopeammista lajittelualgoritmeista löydät netistä esimerkiksi hakusanalla vba sorting algorithms.

MyRandomOrder-ohjelma arpoo nimet satunnaiseen järjestykseen seuraavien vaiheiden kautta:

  • Lukee aktiivisen solun ympärillä olevat nimet taulukkomuuttujaan MyArray1
  • Arpoo taulukkomuuttujaan satunnaisluvut kunkin nimen viereen
  • Lajittelee nimet MyArray2-taulukkomuuttujaan satunnaislukujen mukaiseen järjestykseen BubbleSort1-funktiota käyttäen
  • Kirjoittaa taulukkomuuttujan MyArray2 sisällön laskentataulukkoon alkuperäisten nimien viereen.
Sub MyRandomOrder()
Dim MyRange As Range
Dim MyNamesCount As Long
Dim MyArray1() As Variant
Dim MyArray2() As Variant
Dim i As Integer
    Set MyRange = ActiveCell.CurrentRegion
    MyNamesCount = MyRange.Rows.Count
    ReDim MyArray1(1 To MyNamesCount, 1 To 2)
    ReDim MyArray2(1 To MyNamesCount, 1 To 2)
    MyArray1 = MyRange.Resize(, 2)
    For i = 1 To MyNamesCount
        MyArray1(i, 2) = Rnd
    Next i
    MyArray2 = BubbleSort1(MyArray1, 2, True)
    MyRange.Offset(, 2).Resize(, 2) = MyArray1
End Sub

Tämän artikkelin esimerkit tiedostossa sort.xlsm

Jätä kommentti