－ちょっと使えるネタを紹介－
Excel VBAで配列をソートする方法をご紹介します。配列のソートはバブルソート、クイックソート、Sortを使う方法があります。配列をソートしたい方に参考になるかと思います。
訪問ありがとうございます。この記事では配列をソートする方法をご紹介します。
最初に断っておくと、残念なことにExcel 2016や2019には配列をソートする関数はありません。
なので、プログラミング的に配列をソートするかExcel VBAのSortを使ってセル自体をソートするかのどちらかになりますのでその方法についてご紹介します。
サブスクリプションのOffice 365ではワークシート関数にSortという関数が使えて配列のソートをすることができるみたいです。
この記事を読むメリット
目次から見たい項目へ移動すると便利ですよ。
目次
やりたいことはExcel VBAで配列をソートすることです。
セルに入力されている大量のデータを昇順にして別セルへ貼り付けます。
セルに入力されている元のデータは変更しないようにします。
用意したデータは10万個で、降順に並べられています。
それぞれの方法で、データ取得、昇順に並べ替え、セルへの貼り付けまでの時間を計測して比べてみます。
バブルソートという方法で配列をソートします。
バブルソートはプログラムとしては簡単に書けますが、遅いです。
バブルソートのイメージはこちらになります。
全パターンで一つ一つのデータを比較して昇順に入れ替えていく方法になります。
バブルソートのExcel VBAコードはこちらになります。
Sub Test1()
t = Timer
Dim A, B
'データを取得
A = Range("A2:A100001")
'バブルソート
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
'データを貼り付け
Range("C2").Resize(UBound(A, 1)) = A
Debug.Print Timer - t & " 秒"
End Sub
このようにVBAコードは比較的簡単にかけます。
では、VBAコードを実行してみます。
配列は、あらかじめセルに入力した値から作成します。
10万行のデータを入力しています。
バブルソートで昇順にソートした結果を、セルに入力します。
バブルソートを使って、10万行の配列を昇順にソートできました。
10万個のデータを、昇順にするのにかかる時間は、こちらとなりました。
「1415.4 秒」です。
やはり大量のデータを扱うには、バブルソートではちょっと遅いですね。
クイックソートという方法で、配列をソートする方法について説明します。
クイックソートは、プログラム自体は難しいんですけど、かなり速いです。
理論上では、一番高速らしいです。
配列の中のソートする範囲を分割して、入れ替えの手順を格段に減らすことで、高速しています。
クイックソートは、簡単に説明するとこんな手順になります。
基準は配列の中央の値を採用します。
左側は基準以上の値を探します。右側は基準以下の値を探します。
これを入れ替えます。
『9』と『1』を入れ替えています。
これを繰り返していきます。
『5』と『3』を入れ替えています。
『8』と『4』を入れ替えています。
左側のループと右側のループを繰り返していくと探索範囲が交差します。
交差したら左側と右側の探索範囲を分けます。ここが重要です。
探索範囲を分けていくことで入れ替える回数を減らすことができますのでその結果速度が速くなります。
左側と右側の範囲で①～③を繰り返していきます。
左側の範囲で①～③を繰り返していきます。
右側の範囲でも同じように①～③を繰り返していきます。
範囲の分割を繰り替えしていくと最終的には分割する範囲がなくなっていきます。
分割する範囲がなくなると終了で配列のソートが完了します。
クイックソートで配列をソートするExcel VBAはこちらになります。
Sub Test2()
t = Timer
Dim A
'データを取得
A = Range("A2:A100001")
'クイックソートを実行
Call SortAscending(A, LBound(A), UBound(A))
'データを貼り付け
Range("C2").Resize(UBound(A, 1)) = A
Debug.Print Timer - t & " 秒"
End Sub
Sub SortAscending(A, iLeft, iRight)
'中央値を取得
Dim iMid '中央値
iMid = A(Int((iLeft + iRight) / 2), 1)
i = iLeft '左側の探索用変数
j = iRight '右側の探索用変数
Dim B
'中央値から左側と右側の値を入れ替えていく
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
コードを再帰的に使用して配列をソートしています。コードは結構難しいです。
では、VBAコードを実行してみます。
先ほどと同じように配列は、あらかじめセルに入力した値から作成します。
10万行のデータを入力しています。
クイックソートで昇順にソートした結果を、セルに入力します。
クイックソートを使って、10万行の配列を昇順にソートできました。
クイックソートで、配列をソートするのにかかった時間はこちらになります。
「0.27 秒」です。
バブルソートと比較すると圧倒的に速くなりました。実用的に使えるレベルです。
大量のデータを高速でソートしたい場合には、クリックソートを使うといいかと思います。
最後は、「Sort」を使って配列をソートするというものです。
一旦、ワークシートに配列を入力して、ワークシート上でソートした値を取得する、という方法になります。
このExcel VBAのSort関数を使ってソートする方法は、直接的に配列のソートをするものではありません。
ただ、この方法はVBAコードが簡単な上に、クイックソートと同じレベルで速いです。
簡単かつ高速に配列をソートしたい場合におすすめです。
やりたい内容は、配列をSortを使ってソートしたい、ということになります。
「Sort」を使って配列をソートするVBAコードの流れは、次のようになります。
という流れになります。
Excel VBAのSortを使ってソートするVBAコードです。
Sub Test3()
t = Timer
Dim A
'配列を作成
A = Range("A2:A100001")
'別シートにデータを一時的に貼付け
Range("C2").Resize(UBound(A, 1)) = A
'Sort関数で昇順に並べ替え
Range("C2").CurrentRegion.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlNo
'昇順にしたデータを取得
A = Range("C2").CurrentRegion
'仮のデータを削除
Range("C2").CurrentRegion.Clear
Debug.Print Timer - t & " 秒"
End Sub
Excel VBAコード自体は、クイックソートよりはだいぶ簡単になります。
では、VBAコードを実行してみます。
まず降順に並んだ10万行の値を入力した配列を作成します。
これで、まず10万行の配列を作成することができます。
配列の値を別セルに入力します。
「Sort」を使って、シート上の値を昇順にソートします。
昇順にソートした値を、配列に入力します。
これで、Sortを使って配列の値を昇順にソートできます。
Sortを使って、配列を昇順にソートできました。
不要なデータは削除しておきます。
配列をソートした結果は、こんな感じです。
こんな感じで、一旦、配列の値をワークシート上に入力して、Sortを使って昇順にソートすることができました。
Excel VBAのSortでソートした結果はこちらです。
「0.39 秒」となりました。
かなり高速です。
データを一度転記した分クイックソートよりは少し遅くなっていますが十分実用的に使えるレベルです。
配列のソートではありませんがデータをExcel VBAでデータをソートしたい場合はこちらはおすすめです。
配列をソートする方法について、3つの方法をご紹介しました。
データが少ない場合で簡易的なプログラムでは「バブルソート」、大量データを高速にデータをソートしたい場合は「クイックソート」が使えます。
ただ、クイックソートはコードが複雑なので、ワークシート上で並べ替えをする「Sort」を使って、配列をソートする方法が簡単です。
参考になればと思います。最後までご覧くださいましてありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する