サイト内検索

VBAで生データの複数項目列の削除と並べ替えの一括処理を行う

2020-01-03

生データから集計に関係のない複数列を削除すると同時に、列順を任意の順番に並べ替えて別シートに表示するサンプルコードをご紹介します。

Excel集計業務では、基幹システムから得られた生データを集計前に加工することがよくあります。加工内容はいろいろとありますが、本記事ではいつも決まった列を削除したり、列削除後に並べ替えを行う手作業の自動化に役立つVBAサンプルコードをご紹介します。

まずは本記事でご紹介するVBAサンプルコードで何ができるのかをご紹介している1分間動画（音声無し）を用意しましたので、宜しければこちらからご覧ください。

生データの複数列削除と並べ替えデモ用サンプルファイル

本サンプルファイルに含まれる3枚のExcelワークシートについて、順番にご紹介します。また、最後にダウンロードファイルがありますので、宜しければご利用ください。

生データ用シート

10000件のレコードがある「元データ」シートになります。項目名は、アルファベット順に「A」～「Q」です。項目は全部で17列あります。データの内容は、ランダムに生成された0～9までの数字です。

複数列削除デモ用シート

本サンプルでは、2つの出力シートを用意していますが、下図はそのうちの1つになります。

1つ目の出力シートでは、元データにある項目名のうち、「E」列、「I」列、「O」列の3列を削除しています（丸破線）。

複数列削除と項目列の任意並べ替えデモ用シート

もう1つの出力シートは、「E」列、「I」列、「O」列を削除すると同時に、列ごとの並び順を、任意に変更した結果を表示するものです。

ダウンロード用デモファイル

ご紹介した3つのシートを含むサンプルファイルになります。

VBA-Sample-File-for-Columns-Deletion-and-Sorting2 ダウンロード

生データの複数列削除と項目列並べ替えを同時に行うVBAサンプルコード

本記事でご紹介するVBAサンプルコードです。構成は、mainプロシージャから1つのサブプロシージャを呼び出しています。

Option Explicit
    Dim varOrgn As Variant          '元データ格納用配列
    Dim lngRNum As Long             '元データのレコード数（行数、1次要素数）
    Dim lngCNum As Long             '元データの列数（2次要素数)
    
    Dim varOut1() As Variant        '出力シート側データ格納用配列

Sub main()
    Dim rngTrms As Range            '項目名範囲
    Dim rngMtch As Range            '一致したセル範囲
    Dim r As Long
    Dim c As Long

    Call 配列準備                   '初期設定
    
    '出力シートの項目名範囲取得
    With Out1
        Set rngTrms = .Range("A1").CurrentRegion
    End With
    
    '元データのすべての項目名を繰り返し処理
    For c = 1 To lngCNum
        Set rngMtch = rngTrms.Find(varOrgn(1, c), lookat:=xlWhole)      '元データの項目名を、出力シート側の項目名範囲の中から探す
        
        If Not rngMtch Is Nothing Then                                  '元データの項目名が、出力シート側の項目名範囲の中に見つかった場合
            '項目名を含む元データのすべてのレコードを繰り返し処理
            For r = 1 To lngRNum
                varOut1(r, rngMtch.Column) = varOrgn(r, c)              '元データ配列から出力シート配列へデータコピー
            Next
        End If
    Next
    
    With Out1
        .Range(.Cells(1, 1), .Cells(lngRNum, lngCNum)).Value = varOut1  '出力データ格納配列から、出力シートへ転記
    End With
End Sub

'■配列初期化サブルーチン
Private Sub 配列準備()
    Dim rngOrgn As Range                                    '項目名含む元データ範囲用変数宣言
    
    Set rngOrgn = ORG.Range("A1").CurrentRegion             '項目名含む元データ範囲取得
    varOrgn = rngOrgn.Value                                 '元データ格納用配列変数に格納
    
    lngRNum = UBound(varOrgn, 1)                            '元データ範囲行数（項目名含む）
    lngCNum = UBound(varOrgn, 2)                            '元データ範囲列数
    
    ReDim varOut1(1 To lngRNum, 1 To lngCNum) As Variant    '出力データ格納用配列を、元データ格納用配列と同サイズで宣言
End Sub

Option Explicit

Dim varOrgn As Variant '元データ格納用配列

Dim lngRNum As Long '元データのレコード数（行数、1次要素数）

Dim lngCNum As Long '元データの列数（2次要素数)

Dim varOut1() As Variant '出力シート側データ格納用配列

Sub main()

Dim rngTrms As Range '項目名範囲

Dim rngMtch As Range '一致したセル範囲

Dim r As Long

Dim c As Long

Call 配列準備 '初期設定

'出力シートの項目名範囲取得

With Out1

Set rngTrms = .Range("A1").CurrentRegion

End With

'元データのすべての項目名を繰り返し処理

For c = 1 To lngCNum

Set rngMtch = rngTrms.Find(varOrgn(1, c), lookat:=xlWhole) '元データの項目名を、出力シート側の項目名範囲の中から探す

If Not rngMtch Is Nothing Then '元データの項目名が、出力シート側の項目名範囲の中に見つかった場合

'項目名を含む元データのすべてのレコードを繰り返し処理

For r = 1 To lngRNum

varOut1(r, rngMtch.Column) = varOrgn(r, c) '元データ配列から出力シート配列へデータコピー

End If

With Out1

.Range(.Cells(1, 1), .Cells(lngRNum, lngCNum)).Value = varOut1 '出力データ格納配列から、出力シートへ転記

End With

End Sub

'■配列初期化サブルーチン

Private Sub 配列準備()

Dim rngOrgn As Range '項目名含む元データ範囲用変数宣言

Set rngOrgn = ORG.Range("A1").CurrentRegion '項目名含む元データ範囲取得

varOrgn = rngOrgn.Value '元データ格納用配列変数に格納

lngRNum = UBound(varOrgn, 1) '元データ範囲行数（項目名含む）

lngCNum = UBound(varOrgn, 2) '元データ範囲列数

ReDim varOut1(1 To lngRNum, 1 To lngCNum) As Variant '出力データ格納用配列を、元データ格納用配列と同サイズで宣言

End Sub

VBAサンプルコード実行時の注意点

今回のVBAサンプルコードは、出力先の項目名に合わせてデータを出力させる仕組みになります。よって、下図のように出力先の項目名の並びが異なる2つのシートに対して、サンプルコード内の2か所（VBAコード17行目と33行目の赤枠内）のワークシート名を、表示させる出力シートに合わせて変更してください。

あるいは、VBAコードを変更せずに「出力シート（複数列削除）」の項目名の並びを任意に並べ替えても確認できます。ただし、全体に共通することですが、「元データ」シートの項目名にない項目名を、出力先に指定すると空白列になりますのでご注意ください。

複数列削除と並べ替えを同時に行うVBAサンプルコードのアルゴリズム

本サンプルコードを3つのパートに分けて解説していきます。

モジュール変数宣言

2行目：Dim varOrgn As Variant
元データシート上にあるすべてのデータを格納するための配列です。項目名を含む集計対象レコードをこの配列変数「varOrgn」に格納します。詳細は、実際にデータを代入するVBAコードの中で解説します。

3行目： Dim lngRNum As Long
4行目： Dim lngCNum As Long
元データシートの行数と列数を格納するための長整数型の変数宣言です。これらについても、実際に変数へ代入するVBAコードの中で解説します。

配列準備プロシージャ

順番が前後しますが、先にmainプロシージャ内で呼び出されている「配列準備」プロシージャから解説します。

40行目： Dim rngOrgn As Range
元データシート上のすべてのデータ範囲を格納するオブジェクト変数宣言です。

VBAコード42～43行目は、配列変数「varOrgn」へデータを格納するための処理をしています。まずVBAコード42行目で、Excelワークシート1行目の項目名を含むすべてのデータを、CurrentRegiionプロパティを使いオブジェクト変数「rngOrgn」に代入しています。

つづくVBAコード43行目で、rngOrgnに格納したデータを配列変数「varOrgn」へ格納しています。

VBAコード45～46行目は、元データ範囲（項目名含む）の行数と列数を配列変数「varOrgn」から取得し、各変数「lngRNum」と「lngCNum」に代入しています（下図）。

varOrgnは、項目名含む集計対象レコードのデータがすべて格納されています。よって、図のExcelシートの行列番号がそのまま配列変数「varOrgn」の要素番号に相当しますので、行列数を取得するのにUBound関数を使えます。

48行目： ReDim varOut1(1 To lngRNum, 1 To lngCNum) As Variant
集計結果を格納するための配列変数「varOut1」を宣言しています。下図のとおり、要素数に余裕を持たせるのと、元データと同じサイズにすることで管理し易くなることを理由に、配列変数「varOrgn」と同じ要素構成にしています。

mainプロシージャ

では説明の順番を戻しまして、mainプロシージャの説明になります。

9行目： Dim rngTrms As Range
出力シート1行目の項目名が記載されている範囲を格納するオブジェクト変数です。

10行目： Dim rngMtch As Range
出力先の項目名が、元データの項目名と一致した際に、その一致した出力先項目名のセル情報を格納するオブジェクト変数です。

14行目： Call 配列準備
「配列準備」プロシージャを呼び出しています。

VBAコード17～19行目は、下図のとおりCurrentRegionプロパティを使い、出力シートの項目名範囲（赤枠）を、オブジェクト変数「rngTrms」に格納しています。

元データから出力シートへ抽出するアルゴリズム

varOrgn(1, c)（元データシートの項目名データ）のすべての要素に対する繰り返し処理

VBAコード22～31行目では、varOrgn(1, c)に格納されているすべての要素について繰り返し処理をします。varOrgn(1, c)は、下図赤枠に示したイメージのとおり項目名が格納されています。

Findメソッドで元データと出力シートの項目名の照合を行う

VBAコード23行目は、VBAコード18行目で設定した変数「rngTrms」の中に、varOrgn(1,c)と一致するデータがあるかどうかを探すコードです。

Findメソッドは、いろいろな引数を指定することで、より柔軟な条件検索が可能ですが、経験上、図に示す使い方を覚えておけば、ほとんどの集計業務に対応できるでしょう。

また右辺の戻り値は、出力シート上のセル範囲「rngTrms」の中で条件にマッチした1つのセルになります。よって、左辺ではオブジェクト変数「rngMtch」を使い「Set rngMtch」のように記述します。

さらに、Findメソッドで検索結果が得られなかった場合は、戻り値は「Nothing」になります。

項目名がマッチしたか否かの判定式（NOT演算子）

VBAコード25～30行目は、VBAコード23行目で元データの項目名と出力シートの項目名が一致した場合に処理される内容です。If文で処理対象となる条件を絞っていますが、このときの条件式について下図を使い補足します。

If文の条件式は、上図右下のように「rngMtch Is Nothing」の前後に()をつけると理解しやすいと思います。理解の仕方は、まず()内だけで見ますと、「照合結果が得られなかった」と解釈できます。

そして、()全体をその直前にあるNOT演算子（青字）で否定することにより、結果的に反対の意味「照合結果が得られた場合」と解釈されます。

出力結果格納用配列「varOut1」への格納

VBAコード27～29行目では、項目名がマッチしたあと、該当する列のすべてのデータを配列「varOut1」にコピーしています。下図は項目名「D」でマッチした際の処理例を示しています。

VBAコード27～29行目のFor文で、元データシートの項目名「D」のすべての行のデータを繰り返し取得しながら、配列varOut1にデータを1つひとつコピーしています。図のイメージでいえば、赤枠で囲った個々のデータを、緑枠で囲った1つ1つの配列要素へデータコピーしています。

集計結果が格納された配列「varOut1」のデータを出力シートへ表示

集計結果が格納された配列変数「varOut1」は、そのままではExcelシート上に表示されませんので、最後にVBAコード34行目で集計結果を出力シートに表示します。

まとめ

基幹システムから提供された生データから、必要な列だけを抽出して並べ替えるのに使えるVBAサンプルコードをご紹介しました。もし定期的な集計業務の中で、いつもお決まりの不要列を削除したり、並べ替えを実施しているのであれば、本記事でご紹介したVBAサンプルコードの手法を応用することで業務効率化を実現できると考えます。

[スポンサーリンク]