データ整理に毎回手間をかけてない！？並び替えVBAで一覧表を自動ソートする方法とは

 

エクセルの一覧表を毎回手作業で並び替えていませんか？

 

VBAを使えば、複雑な並び替えもボタン1クリックで完了させることが可能です。

この記事では、実務でよく使う並び替えパターンを4種類、実際のVBAコードを交えて紹介します。

 

手作業の並び替えが招く3つの問題点

エクセルで大量のデータを管理していると、並び替えは避けて通れない作業です。

 

「昇順・降順に並び替えるだけ」ならエクセルの標準機能でも対応できます。

しかし実務では、もっと複雑な要件が出てくることがほとんどです。

 

たとえば、「枝番が付いた品番を正しい順番に並べたい」「北海道、秋田、宮城のように自分で決めた地域の順番に並べたい」「別シートの設定一覧と照合しながら並び替えたい」といった場面は、日常的に発生します。

 

こういった並び替えを毎回手作業でやろうとすると、次の3つの問題が起きます。

 

問題①：時間がかかる

データ量が多くなるほど、フィルターをかけて並び替えて、また戻して、という繰り返しが発生します。

慣れた人でも1回の並び替えに10分以上かかることは珍しくありません。

 

私が勤めていた職場では、数千個もの設備データから「不良の設備」「要注意の設備」を抜き出すという作業を月１回行っていました。

（ フィルターをかけて、並び替えして、別シートにコピペして、印刷して、、、)

 

単純な作業だけど地味に時間がかかっていました。

 

問題②：編集ミスが起きやすい

目視で並び替えると「この枝番が飛んでいる」「この地域の順番がずれた」といったミスが発生します。

特にデータ件数が100件、200件を超えてくると、目視確認だけでは限界があります。

 

1-1、1-1-2、1-1-3、1-2、のように枝番が増えると手作業での並び替えは難しくなるのでVBAでの自動化が必須です。

 

問題③：毎回同じ手順を繰り返す非効率さ

一度やり方を覚えても、月次・週次で同じ作業を繰り返すのは大きなロスです。

 

データ量が多く数回に分けて並び替えが必要な場合、わざわざ手順書を作成している上司がいました。

（ 誰でも並び替え作業ができるように手順書を作ってくれるのはありがたいけど、資料作成の時間が無駄では？）

 

VBAを使えば、どんなに複雑な並び替えでも設定した内容で処理可能す。

しかも毎回同じ方法で並び替えてくれるので、編集ミスが発生しません。

 

並び替えVBAの基本｜まずはシンプルなソートから理解する

並び替えVBAは、Sortオブジェクトを使用します。

まずはシンプルな昇順ソートのサンプルコードを見ていきましょう。

 

下記の一覧表の並び替えを想定します。

 

昇順・降順の基本ソートVBA

C列「設備番号」の情報を元に並び替えを行います。

Sub 基本ソート()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("一覧")

'並び替え前にソート条件をクリア
ws.Sort.SortFields.Clear

'C列（設備番号）を昇順で並び替え
ws.Sort.SortFields.Add _
Key:=ws.Range("C2:C1000"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending

'ヘッダーありの設定でソート実行
With ws.Sort
.SetRange ws.Range("A1:E1000")
.Header = xlYes
.Apply
End With

MsgBox "並び替えが完了しました。", vbInformation

End Sub

 

コードの流れを解説します。

 

最初の Set ws = ThisWorkbook.Worksheets(“一覧") で、処理対象のシートを「一覧」シートに指定しています。

 

ws.Sort.SortFields.Clear は、前回の並び替え設定が残っていると誤作動する原因になるため、毎回クリアするのが鉄則です。

 

SortFields.Add で並び替えのキーと順序を指定します。

Key に並び替えの対象列、Order:=xlAscending で昇順（A→Z、小→大）を指定。

降順にしたい場合は xlAscending を xlDescending に変えるだけです。

 

Header = xlYes は「1行目はヘッダーなので並び替えの対象外にする」という設定。

データの先頭行に見出しがある場合は必ずこの設定を入れてください。

 

実行結果がこちら

 

枝番を含む品番を正しい順番に並び替えるVBA

実務で特に厄介なのが、枝番付きの品番の並び替えです。

たとえば、A001、A001-1、A001-2、A002、A002-1 のような枝番が付いたデータを昇順に並べると、文字列としてソートされるため A001、A001-1、A001-2、A002 という正しい順番にならないことがあります。

 

これを正確に処理するには、品番と枝番を分離して2列でソートするのが確実です。

 

次のサンプルは、C列の設備番号に含まれる枝番を認識して並び替えを行うという内容です。

 

サンプルコードがこちら

Sub 枝番認識並び替え()
Dim i As Long
Dim lastRow As Long
Dim cellValue As String
Dim mainNum As String
Dim subNum As String
Dim hyphenPos As Long

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

'D列に枝番前の数値だけを取り出す
For i = 2 To lastRow
cellValue = Cells(i, 1).Value
hyphenPos = InStr(cellValue, "-")

If hyphenPos > 0 Then
'「-」が含まれている場合は枝番あり
mainNum = Mid(cellValue, 1, hyphenPos - 1)
subNum = Mid(cellValue, hyphenPos + 1)
Else
'枝番なしの場合はそのまま
mainNum = cellValue
subNum = "0"
End If

'D列に主番号、E列に枝番を入れる
Cells(i, 4).Value = CLng(mainNum)
Cells(i, 5).Value = CLng(subNum)
Next i

'主番号→枝番の順で並び替え
Range("A1:E" & lastRow).Sort _
Key1:=Range("D1"), Order1:=xlAscending, _
Key2:=Range("E1"), Order2:=xlAscending, _
Header:=xlYes

'作業列（D列・E列）を削除
Columns("D:E").Delete

MsgBox "枝番を考慮した並び替えが完了しました。"
End Sub

 

コードの流れを解説します。

InStr関数とMid関数を使って、文字列の中に「-」があるかどうか検索します。

 

「-」があった場合、主番号をE列に表示、枝番をF列に表示。

この２つの列をキーとして並び替えを実行する。

並び替え完了後、不要となったE列とF列を削除します。

 

実行結果がこちら

 

設定シートの順番に従ってカスタム並び替えをするVBA

エクセル標準の昇順・降順では対応できない並び替えの代表例が、「自分で決めた順番に並べたい」というケースです。

 

たとえば都道府県を「北海道→秋田→宮城→東京→大阪」という任意の順番に並べたい場合、昇順・降順では絶対に実現できません。

 

こういったケースで使えるのが、「設定シート」を使ったカスタム並び替えです。

「設定」シートのA列に並べたい順番を記載しておき、それを参照して一覧表を並び替える仕組みです。

 

Sub 別シートのリストで並び替え()

Dim Ash As Worksheet
Set Ash = Sheets("一覧")
Dim Bsh As Worksheet
Set Bsh = Sheets("設定")
Dim 配列() As String
Dim i As Integer
ReDim 配列(7)

For i = 0 To 7
配列(i) = Bsh.Cells(i + 2, 1)
Next i
MsgBox Join(配列, ",")
With Ash.Sort
.SortFields.Clear
.SortFields.Add Key:=Cells(1, 1), Order:=xlAscending, CustomOrder:=Join(配列, ",")
.SetRange Range("A1").CurrentRegion
.Header = xlYes
.Apply

End With

End Sub

 

コードの流れを解説します。

設定シートには、以下のように並べたい順番のデータを入力しておきます。

 

「設定」シートにある一覧表情報を取得し、変数「配列」に格納します。

配列は、セルを1つずつ読み書きするよりも処理スピードを大幅にアップさせる効果があるのでおすすめです。

 

For i ループを使って、設定シートの2行目から最終行までの情報を取得します。

CustomOrdeで配列を指定して完成です。

 

この仕組みを使えば、設定シートの内容を変えるだけで並び替え順を自由にカスタマイズできます。

「北海道→秋田→宮城」の順番を変更したいときは、設定シートのA列を書き直すだけでOKです。

 

実行結果がこちら

 

複数条件で並び替えて結果を別シートに出力するVBA

実務では「並び替えた結果を別シートに出力したい」という要望も多くあります。

 

元の一覧表を崩したくない場合や、並び替え結果を印刷用シートに転記したい場合に使えるパターンです。

Sub 並び替え結果を別シートに出力()

Dim ws_src As Worksheet
Dim ws_out As Worksheet
Dim lastRow As Long

Set ws_src = ThisWorkbook.Worksheets("一覧")
Set ws_out = ThisWorkbook.Worksheets("出力")

'出力シートの2行目以降をクリア
ws_out.Rows("2:" & ws_out.Rows.Count).ClearContents

'元データをコピー
lastRow = ws_src.Cells(Rows.Count, 1).End(xlUp).Row
ws_src.Range("A1:E" & lastRow).Copy ws_out.Range("A1")

'出力シート上で並び替え
ws_out.Sort.SortFields.Clear

'第1キー：A列（地域）昇順
ws_out.Sort.SortFields.Add _
Key:=ws_out.Range("A2:A" & lastRow), _
Order:=xlAscending

'第2キー：B列（担当者名）昇順
ws_out.Sort.SortFields.Add _
Key:=ws_out.Range("B2:B" & lastRow), _
Order:=xlAscending

With ws_out.Sort
.SetRange ws_out.Range("A1:E" & lastRow)
.Header = xlYes
.Apply
End With

MsgBox "出力シートへの並び替えが完了しました。", vbInformation

End Sub

 

コードの流れを解説します。

最初に ws_out.Rows(“2:" & ws_out.Rows.Count).ClearContents で出力シートをクリアします。

前回の出力データが残っていると重複するため、毎回クリアしてからデータを貼り付けるのが基本です。

 

ws_src.Range(“A1:E" & lastRow).Copy ws_out.Range(“A1") で元の一覧表を出力シートにコピーします。

コピー先でソートするため、元の一覧表には一切手を加えません。

 

その後は、出力シート上でソートを実行します。

lastRow を変数で取得しているため、データが増減しても自動的に正しい範囲をソート対象にしてくれます。

 

実行結果がこちら

 

関連記事「別シートにテキストを自動転記」では、シートを指定するVBA、転記VBAが設定されたエクセルファイルをダウンロードすることができます。

[blogcard url="https://note.com/mamemametochan/n/na1b21cd70673″]

 

並び替えVBAをボタンに登録する方法

ここまで紹介したVBAコードは、エクセルシート上にボタンを配置してワンクリックで実行できるようにすることが可能です。

 

「開発」タブ → 「挿入」 → 「ボタン（フォームコントロール）」を選択して、シート上にドラッグすると「マクロの登録」ダイアログが開きます。

登録したいマクロ名（例：カスタム並び替え）を選んで「OK」をクリックすれば設定完了です。

 

ボタン上で右クリックして「テキストの編集」を選べば、「▶ 並び替え実行」などの説明文をボタンに表示することもできます。

一度ボタンを設置してしまえば、次回からは操作手順を覚える必要はありません。

ボタンを押すだけで、いつでも同じ正確な並び替えが実現できます。

 

並び替えVBAを使う際の注意点

並び替えVBAを実装するにはいくつかの注意点があります。

これから紹介する注意点を把握しておかなければ、「今まで使えていたのに突然並び替えVBAが使えなくなった」「想定している並び替え結果にならない」という問題が発生するかもしれません。

 

データの最終行を正確に取得する

VBAコードの中で Cells(Rows.Count, 1).End(xlUp).Row という記述を使っていました。

これはA列の最終行を自動的に取得する命令で、データが増えても範囲指定を変更する必要がなくなる便利なコードです。

並び替えVBAには、このコードを使うようにしましょう。

 

固定値（例：A2:A1000）で範囲を指定すると、データが少ないときは問題ありませんが、1000行を超えたときにソートされないデータが出てくるので注意が必要です。

 

動的に最終行を取得する書きにしておくと、後々の修正が不要になります。

 

ソート前にSortFields.Clearを忘れずに

ws.Sort.SortFields.Clear を忘れると、前回のソート条件が残ったまま新たな条件が追加されてしまい、意図しない並び順になることがあります。

並び替えVBAを書くときは必ず冒頭でClearを実行するクセをつけておきましょう。

 

カスタム並び替えは設定シートを必ず整備する

設定シートに入力された値と一覧表の値が完全一致していないと、並び替えが正しく動作しません。

全角と半角、スペースの有無、漢字の使い方など、些細な違いで一致しなくなるため、データ入力時のルールを統一しておくことが重要です。

 

まとめ ～ 並び替え作業にはVBAを使った自動化が必須 ～

エクセルの並び替えは、標準機能だけでは対応できないケースが実務では頻繁に発生します。

VBAを使えば、昇順・降順の基本ソートはもちろん、枝番付き品番のソート、設定シートを使ったカスタム並び替え、別シートへの出力まで、あらゆる並び替えパターンを自動化することが可能です。

 

時間の短縮、編集ミス発生防止、業務内容に合わせたカスタイマイズができる、などメリットしかありません。

 

今回紹介したコードをベースに、自分の業務に合わせてカスタマイズしてみてください。

 

並び替えに毎回10分以上かけている方は、ぜひ関連記事「並び替えに毎回10分ロス！？VBAで一瞬で完了させる4つのソート術」も参考にしてみてください。

[blogcard url="https://note.com/mamemametochan/n/n38c751b4e427″]

 

もし「自分の業務に合わせた複雑なチェック処理を実装したい」「既存のマクロを改良したい」といったご要望がありましたら、マメBlogのエクセルマクロ開発依頼にご相談ください。

[blogcard url="https://mamemametochan.com/macro-orders/"]

業務内容に合わせたオーダーメイドマクロを設定させていただきます。

 

最後まで読んでいただきありがとうございました。エクセルVBAを使って面倒なルーティーン作業を自動化しちゃいましょう。