並び替えVBA【 作業効率をアップさせる並び替えの方法を紹介 】

Left Caption

豆父ちゃん

とても便利な並び替えという処理をVBAで自動化したい。

こんな思いにお答えします。

結論からいうと、VBAでSortメソッドを使うと簡単に並び替えの処理を自動化することができます。

Sortメソッドの基本的な使い方とSortメソッドが使われているサンプルVBAを紹介します。

並び替え【Sortメソット】の基本的な使い方

Sortメソットの基本構造

Range( ).Sort(Key1 , Order1 , Key2 , Type, Order2 , Key3 , Header , OrderCustom , MatchCase , Orientation , SortMethod , DateOption1 , DateOption2 , DateOption3)

Key 省略可能 並び替えのキーになる列のセルを指定
Order 省略可能 キーの列を昇順か降順を指定
xlAscending⇒昇順
XlDescending⇒降順
Type 省略可能 ピボットテーブルを並び替えるときに指定
Header 省略可能 先頭行をタイトル行とするかを指定
xlGuess⇒タイトル行を自動指定
xlNo⇒タイトル行無
xlYes⇒タイトル行有
OrderCustom 省略可能 並び替えの順序を指定
MatchCase 省略可能 大文字と小文字を区別するかを指定
True⇒区別する
False⇒区別しない
Orientation 省略可能 並び替えの方向を指定
xlSortColumns⇒上から下へ
xlSortRows⇒左から右へ
SortMethod 省略可能 ふりがなを使うか指定
xlPinYin⇒ふりがなを使う
xlStroke⇒ふりがなを使わない
DateOption 省略可能 数値と文字列を別にして並び替えるかを指定
xlSortNormal⇒数値と文字列を別に並び替える
xlSortTextAsNumbers⇒数値と文字列を同様に並び替える

 

並び替えの範囲を指定

並び替えVBAでは範囲を指定することはとても重要なのでしっかりと覚えましょう。いくつかのサンプルを見比べるとわかりやすいのでサンプルを3つ紹介します。

サンプル1

1 Range( “A1:C5" ) . Sort ( Key1・・・

⇒ セルA1からC5までを範囲指定

 

サンプル2

1 Range( Cells ( 1 , 1 ) , Cells ( 5 , 3 )) .Sort( Key1・・・

セルA1からC5までを範囲指定

 

サンプル3

1 retu = Cells ( 1, Columns.Count ) . End( xlToLeft ) .Column
2 gyo = Cells ( Rows.Count , 1) .End( xlUp ) .Row
3 Range( Cells ( 1 , 1 ) , Cells ( gyo , retu )) .Sort( Key1・・・

⇒ セルA1から表の右下C5までを範囲指定

 

範囲を指定することはとても重要で、エクセル業務によってSheet全体が範囲対象になるとは限りません。こっちの表は日付で並び替え、あっちの表は価格で並び替えてくれ!といったことも

サンプル3のVBAを使えば表の行や列の増減を自動的に変更することも可能です。

 

並び替え【Sortメソット】を使ったサンプルVBA

A1セルからC20の表で、C列を昇順に並び替えを行う。一番上の行はタイトルとして使用する!というVBAを作ってみます。


サンプルVBA

1 Sub テスト()
2 Range(Cells(1, 1), Cells(20, 3)).Sort Key1:=Cells(1, 3), Order1:=xlAscending, Header:=xlYes
3 End Sub

実行結果

簡単なものだと、たったの1行で並び替えの自動化が可能です。

 

仕事でも使える並び替え(Sortメソット)VBA

電気用品に関する適当に作った一覧表を使って4種類の並び替えVBAを紹介します。

  1. 設備番号を昇順で並び替え
  2. 設備番号にある枝番(1-1や6-1)を認識して並び替え
  3. ユーザー定義設定を使って地域の都道府県を上から順番に並び替え(ユーザー定義の順番はプログラムに直接記載)
  4. ユーザー定義設定を使って地域の都道府県を下から順番に並び替え(ユーザー定義の順番は設定シートの順番を取得)

この4種類の並び替えVBAを扱えるようになると、いろんな業務に応用することが可能になります。私の職場ではよく4番目の並び替えVBA(ユーザー定義の順番を設定シートの一覧表から取得)が使われています。

 

並び替えVBAの動作

 

1.並び替えVBA(設備番号を昇順にする方法)

サンプルVBA

1 Sub テスト()
2 gyo = Cells(Rows.Count, 3).End(xlUp).Row
3 Range(Cells(1, 1), Cells(gyo, 3)).Sort Key1:=Cells(1, 3), Order1:=xlAscending, Header:=xlYes
4 End Sub

実行結果


このような単純な並び替えはたった4行のプログラムで完了します。

2.並び替えVBA(枝番を認識する方法)

仕事で使われている一覧が全て整数であるとは限りませんよね。番号に枝番がある表の並び替えはVBAを使わないとうまくいきません。枝番を判別して並び替えを行うVBAを紹介します。

枝番とは、番号「1」の次が「2」ではなく「1-1」や「1-2」となること。なぜこんなわかりくくするの?と思うかもしれませんが、仕事ではよくあることです。

番号「1」の商品の類似品が新発売した場合、番号「2」をつけたいところだが、番号「2」は別の商品で使っている。。こんなとき枝番「1-1」が新商品に付けられます。

枝番は並び替えにはとても厄介な表記だが、とても便利なものなのです。なので枝番の並び替えをマスターすれば仕事で間違いなく役立てることができます。


サンプルVBA

1 Sub テスト()
2 retu = Cells(1, Columns.Count).End(xlToLeft).Column
3 gyo = Cells(Rows.Count, 1).End(xlUp).Row
4 On Error Resume Next
5 For i = 2 To gyo
6 ds = InStr(Cells(i, 3), “-“)
7 df = Mid(Cells(i, 3), 1, ds – 1)
8 If InStr(Cells(i, 3), “-“) <> 0 Then
9 Cells(i, retu + 1) = df
10 Else
11 Cells(i, retu + 1) = Cells(i, 3)
12 End If
13 Next
14 Range(Cells(1, 1), Cells(gyo, retu + 1)).Sort Key1:=Cells(1, retu + 1), Order1:=xlAscending, Header:=xlYes
15 Cells(1, retu + 1).EntireColumn.ClearContents
16 End Sub

 

実行結果

枝番の識別のは関数InStr と 関数Midを使っています。

枝番の認識VBA

 

6行目で、枝番「-」の位置を「ds」に入れる

7行目で、テキストの始まりから枝番「-」の一つ前までのテキストを「df」にいれる

関数InStrと関数Midの組み合わせはテキストを操作するときによく使われます。

 

3.ユーザー定義を使った並び替えVBA(プログラムに直接並び替えたい項目の順番を記載)


サンプルVBA

1 Sub テスト()
2 With ActiveSheet.Sort
3 .SortFields.Clear
4 .SortFields.Add Key:=Cells(1, 1), Order:=xlAscending, CustomOrder:="北海道,青森,東京,大阪,鹿児島,沖縄"
5 .SetRange Range(“A1").CurrentRegion
6 .Header = xlYes
7 .Apply
8 End With
9 End Sub

 

実行結果

上記のVBAの紹介で使っているもので、地域を「北海道→青森→大阪→鹿児島→沖縄」の順番に並び替えています。

CustomOrder:="〇〇,〇〇"の〇〇を付け加えたり、変更したりするだけでユーザー定義の設定完了。

 

4.ユーザー定義を使った並び替えVBA(並び替えたい項目の一覧表から順番を取得する)


サンプルVBA

1 Sub テスト()
2 Dim Ash As Worksheet
3 Set Ash = Sheets(“一覧")
4 Dim Bsh As Worksheet
5 Set Bsh = Sheets(“設定")
6 Dim 配列() As String
7 Dim i As Integer
8 ReDim 配列(7)
9 For i = 0 To 7
10 配列(i) = Bsh.Cells(i + 2, 1)
11 Next i
12 With Ash.Sort
13 .SortFields.Clear
14 .SortFields.Add Key:=Cells(1, 1), Order:=xlAscending, CustomOrder:=Join(配列, “,")
15 .SetRange Range(“A1").CurrentRegion
16 .Header = xlYes
17 .Apply
18 End With
19 End Sub

実行結果

設定シートの一覧表からユーザー定義の項目を取得するのでシート名の取得VBA使っています。

シートの設定VBAについて下記の記事で詳しく紹介しています。

他にも Join関数(取得した文字列データは連結する)を使っていたり少し難しいVBAになっています。サンプルを使っていろいろ試しながら覚えていきましょう。

 

並び替えVBA(ユーザー設定の基本な使い方)

ユーザー設定の並び替えは順番を自分で設定できる!という特徴があります。

 

 

  • 都道府県を北から順番に並び替えたい
  • 市町村名を東から順番に並び替えたい
  • 商品名の順番を自由に並び替えられたい

 

このようなときにユーザー定義を設定すれば思い通りの並び替えが可能になります。

ユーザー定義を使った並び替えVBAの作り方

ユーザー定義による並べ替えVBAは基本構造の中のOrderCustomではなく、SortFieldsコレクションを使用します。OrderCustomでやろうとしてもどうしてもうまくいきませんでした。

 

SortFieldsの基本構造

SortFields.Add ( Key , order , Customorder , dataoption )

各プロパティは上記(Sortメソッド)で説明した内容と変わりありません。なぜSortメソットとSortFieldsを使い分けなければいけないのか理解できていません。

ユーザー定義を使った並び替えVBAを作るときはSortFieldsを使うもの!と自分の中で納得してVBAを開発しています。

下記のSortFieldsを使った基本サンプルを参考にしてみてください。

1 With ActiveSheet.Sort ←アクティブシートにSortFieldオブジェクトを入れていくのでWithを使って簡略化する
2 .SortFields.Clear 現在設定されているフィルターを解除
3 .SortFields.Add Key:=Cells(1, 1), Order:=xlAscending, CustomOrder:=" 〇〇,〇〇,〇〇 “ 基本構造の内容
4 .SetRange Range(“A1").CurrentRegion 並び替えの範囲を指定(CurrentRegionは全体を指定するもの)
5 .Header = xlYes タイトルの有無を指定
6 .Apply SortFieldsを実行させるもの(SortFieldを使う場合必ず必要)
7 End With Withの終了

 

Left Caption

豆父ちゃん

この基本サンプルはすぐ使えるようにメモっておくか、このページをブックマーク登録することをおすすめします。

並び替えVBAは作業の効率化に欠かせない機能

並び替えVBAはエクセル業務の効率をアップするのに欠かせない機能です。

並び替え機能はこんな時に便利

 

  • データを分析するとき
  • データを種類毎にまとめたいとき
  • データを編集しやすくするとき

私の職場ではほとんどのエクセルファイルに並び替えVBAが導入されています。

効果① 作業時間の短縮

 

データ編集のときスクロール移動を頻繁に行うのはとても非効率的な動作です。さらに目を酷使するので頭が凄い疲れる

並び替えを行いデータを種類毎にまとめると、画面移動が少なくて作業時間の短縮に繋がります

効果② ヒューマンエラーを防げる

 

時間をかけて膨大なデータを扱っていると、どうしてもヒューマンエラー(人間の操作にによる失敗)が発生してしまいます。人間の集中力には限界がある

並び替えを行いデータを扱いやすいものにしてしまえば、ヒューマンエラーを格段に減らせることができる

並び替えVBAを導入してエクセル業務の効率をアップさせましょう。