大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで配列をソートする方法をご紹介します。配列のソートはバブルソート、クイックソート、Sortを使う方法があります。配列をソートしたい方に参考になるかと思います。

はじめに

訪問ありがとうございます。この記事では配列をソートする方法をご紹介します。

最初に断っておくと、残念なことにExcel 2016や2019には配列をソートする関数はありません。

なので、プログラミング的に配列をソートするかExcel VBAのSortを使ってセル自体をソートするかのどちらかになりますのでその方法についてご紹介します。

サブスクリプションのOffice 365ではワークシート関数でSortという関数が使えて配列のソートをすることができるみたいです。

この記事でわかること

  • バブルソートで配列のソート
  • クイックソートで配列のソート
  • Excel VBAのSort関数でソート

目次から見たい項目へ移動すると便利ですよ。

目次

Excel VBAで配列をソートしたい

やりたいことはExcel VBAで配列をソートすることです。

昇順で並べて別セルへ貼り付けたい

セルに入力されている大量のデータを昇順にして別セルへ貼り付けます。

配列をソートして貼り付け

セルに入力されている元のデータは変更しないようにします。

用意したデータは10万個で、降順に並べられています。

それぞれの方法で、データ取得、昇順に並べ替え、セルへの貼り付けまでの時間を計測して比べてみます。

バブルソートで配列のソート

バブルソートという方法で配列をソートします。

バブルソートとは

バブルソートはプログラムとしては簡単に書けますが、遅いです。

バブルソートのイメージはこちらになります。

バブルソートの手順

全パターンで一つ一つのデータを比較して昇順に入れ替えていく方法になります。

Excel VBAコード

バブルソートのExcel VBAコードはこちらになります。

'バブルソート
Sub Test1()

    'データを取得
    With ActiveSheet
        a = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
    
    'バブルソート
    For i = 1 To UBound(a, 1) - 1
        For j = i + 1 To UBound(a, 1)
            If a(i, 1) > a(j, 1) Then
                b = a(i, 1)
                a(i, 1) = a(j, 1)
                a(j, 1) = b
            End If
        Next
    Next
    
    'データを貼り付け
    With ActiveSheet
        .Range("C2").Resize(UBound(a, 1)) = a
    End With

End Sub

このようにVBAコードは比較的簡単にかけます。

結果

10万個のデータを昇順にするのにかかる時間はこちらとなりました。

1415.4 秒

やはり大量のデータを扱うにはバブルソートでは不十分なようです。

クイックソートで配列のソート

クイックソートという方法で配列をソートする方法について説明します。

クイックソートとは

クイックソートはプログラムは難しいですがかなり速いです。

理論上一番高速らしいです。

配列の中のソートする範囲を分割して入れ替えの手順を格段に減らしています。

クイックソートは簡単に説明するとこんな手順になります。

クリックソート手順

  • ①基準の値を決める
  • ②左側と右側から基準以上と以下の値を探して入れ替える
  • ③左側ループと右側ループが交差したら探索範囲を分割する
  • ④分割した範囲で①~③を繰り返す
  • ⑤分割する範囲がなくなったら昇順完了

①基準の値を決める

基準は配列の中央の値を採用します。

クイックソート手順:中央の値を決定する

②左側と右側から基準以上と以下の値を探して入れ替える

左側は基準以上の値を探します。右側は基準以下の値を探します。

クイックソート手順:基準値以上と基準値以下の値を入れ替える1回目

これを入れ替えます。

クイックソート手順:基準値以上と基準値以下の値を入れ替える2回目

『9』と『1』を入れ替えています。

これを繰り返していきます。

クイックソート手順:基準値以上と基準値以下の値を入れ替える3回目

『5』と『3』を入れ替えています。

クイックソート手順:基準値以上と基準値以下の値を入れ替える4回目

『8』と『4』を入れ替えています。

③左側ループと右側ループが交差したら探索範囲を分割する

左側のループと右側のループを繰り返していくと探索範囲が交差します。

クイックソート手順:探索範囲を分割する

交差したら左側と右側の探索範囲を分けます。ここが重要です。

探索範囲を分けていくことで入れ替える回数を減らすことができますのでその結果速度が速くなります。

④分割した範囲で①~③を繰り返す

左側と右側の範囲で①~③を繰り返していきます。

左側の範囲で①~③を繰り返していきます。

クイックソート手順:左側の範囲で並べ替えを繰り返す

右側の範囲でも同じように①~③を繰り返していきます。

クイックソート手順:右側の範囲で並べ替えを繰り返す

⑤分割する範囲がなくなったら昇順完了

範囲の分割を繰り替えしていくと最終的には分割する範囲がなくなっていきます。

分割する範囲がなくなると終了で配列のソートが完了します。

Excel VBAコード

クイックソートで配列をソートするExcel VBAはこちらになります。

'クイックソート
Sub Test2()
	
    'データを取得
    With ActiveSheet
        a = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
    
    'クイックソートを実行
    Call SortAscending(a, LBound(a), UBound(a))
    
    'データを貼り付け
    With ActiveSheet
        .Range("C2").Resize(UBound(a, 1)) = a
    End With

End Sub
'クイックソート
Sub SortAscending(a As Variant, iLeft As Variant, iRight As Variant)
    
    '中央値を取得
    Dim iMid As Variant '中央値
    iMid = a(Int((iLeft + iRight) / 2), 1)
    
    i = iLeft '左側の探索用変数
    j = iRight '右側の探索用変数
    
    '中央値から左側と右側の値を入れ替えていく
    Do
        '中央値から左側のループ
        Do While a(i, 1) < iMid
            '中央値以上の値まで右側に探索していく
            i = i + 1
        Loop
        
        '中央値から右側のループ
        Do While iMid < a(j, 1)
            '中央値以下の値まで左側に探索していく
            j = j - 1
        Loop
        
        '左側探索と右側探索の位置が交差したら終了
        If i >= j Then Exit Do
        
        'まだ交差していない場合、左側と右側の値を入れ替える
        b = a(i, 1)
        a(i, 1) = a(j, 1)
        a(j, 1) = b
        
        '左側は1つ右からスタート
        i = i + 1
        '右側は1つ左からスタート
        j = j - 1
    Loop
    
    '中央値から左側を入れ替えていく(再帰)
    If iLeft < i - 1 Then
        Call SortAscending(a, iLeft, i - 1)
    End If
    
    '中央値から右側を入れ替えていく(再帰)
    If j + 1 < iRight Then
        Call SortAscending(a, j + 1, iRight)
    End If
    
End Sub

コードを再帰的に使用して配列をソートしています。コードは結構難しいです。

結果

クイックソートで配列をソートするのにかかった時間はこちらです。

0.27 秒

バブルソートと比較すると圧倒的に速くなりました。実用的に使えるレベルです。

大量のデータを高速でソートしたい場合にはこちらを使うといいかと思います。

Excel VBAのSortでソート

このExcel VBAのSort関数を使ってソートする方法は正確には配列のソートではありません。

ですが、この方法はVBAコードが簡単な上にクイックソートと同じレベルで速いです。

簡単かつ高速に配列をソートしたい場合におすすめです。

Excel VBAのSortを使う方法

昇順に並べ替えるだけであれば入力されているセルのところでSortを使えばいいです。

ただし今回は元のデータは変更したくないという前提がありますので、一旦別のシートへデータを転記します。

データを別シートへ転記

転記したところでセルをSortで昇順に並べ替えます。

昇順に並べ替え再転記

並べ替えたデータを元のシートに貼り付ける。という手順です。

Excel VBAコード

Excel VBAのSortを使ってソートするVBAコードです。

'Sort関数を使う
Sub Test3()

    'データ取得
    With ActiveSheet
        a = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
    
    With Worksheets("別シート")
        '別シートにデータを一時的に貼付け
        .Range("A1").Resize(UBound(a, 1)) = a
        
        'Sort関数で昇順に並べ替え
        .Range("A1").CurrentRegion.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes
        
        '昇順にしたデータを取得
        b = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
    
    'データを貼り付け
    With ActiveSheet
        .Range("C2").Resize(UBound(b, 1)) = b
    End With

End Sub

Excel VBAコード自体はクイックソートよりはだいぶ簡単になります。

結果

Excel VBAのSortでソートした結果はこちらです。

0.39 秒

かなり高速です。

データを一度転記した分クイックソートよりは少し遅くなっていますが十分実用的に使えるレベルです。

配列のソートではありませんがデータをExcel VBAでデータをソートしたい場合はこちらはおすすめです。

おわりに

配列をソートする方法について3つをご紹介しました。

データが少ない場合で簡易的なプログラムではバブルソート大量データを高速にソートしたい場合はクイックソートが使えるかと思います。

厳密にいうと配列のソートではありませんが、簡単かつ高速に大量データをソートしたい場合はExcel VBAのSortがおすすめです。

ご参考になればと思います。最後までご覧くださいましてありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す