Excel講座 - 入力されているデータの抽出

株式会社サープレス

| HOME | お問い合わせ |

Excel講座

HOME > Excel講座 > 入力されているデータの抽出

NO.8 入力されているデータの抽出

入力されているデータを条件によって抽出しあれこれと加工して、はじめて意味のある情報となります。そこで今回は、一覧表形式になっているデータシートより条件抽出して、検索結果シートを作成するパターンを紹介します。

シートの作成

条件入力はそのときによって色々ありますので、条件入力シートを作成するのが一般的です。手順はNO.4 セルへの入力を簡単にするのデータ入力用シートの作成と同様です。

例題として受注明細を作成します。

受注明細シート

まずは商品IDのみで条件抽出する処理を行います。また、入力された条件をクリアするボタンも付けます。

条件入力シート

VBAの作成

まずは入力された条件をクリアする処理を作成します。これは説明するまでも無いでしょう。

作成するモジュール
Private Sub cmdClear_Click() Range("C6").Value = "" 'セルC6をクリアする End Sub

次にデータを抽出する処理を作成します。処理内容は、入力チェックと検索結果シートの初期化、そして抽出処理です。

作成するモジュール
Private Sub cmdGo_Click() Dim strId As String Dim iLast As Integer Dim iRow As Integer Dim iCnt As Integer Dim bCopy As Boolean If (Range("C6").Value = "") Then 'セルC6に入力されていない？ MsgBox "商品IDが入力されていません。", vbExclamation Exit Sub '処理を終了する End If Sheet3.Cells.Clear '全てのセルをクリアする Sheet3.Range("A1:J1").Value = Sheet2.Range("A1:J1").Value strId = Range("C6").Value iLast = WorksheetFunction.CountA(Sheet2.Columns(1)) iCnt = 1 'コピー先セル行の先頭行(実際は2行目からですので1とする) For iRow = 2 To iLast bCopy = False If (Sheet2.Cells(iRow, 3).Value = strId) Then '入力した文字列と一致？ bCopy = True End If If (bCopy = True) Then '一致？ iCnt = iCnt + 1 コピー先のセル行を1つ進める Sheet3.Range(Sheet3.Cells(iCnt, 1), Sheet3.Cells(iCnt, 10)).Value = _ Sheet2.Range(Sheet2.Cells(iRow, 1), Sheet2.Cells(iRow, 10)).Value End If Next iRow Sheet3.Select End Sub

作成するモジュール

Private Sub cmdGo_Click()
Dim strId As String
Dim iLast As Integer
Dim iRow As Integer
Dim iCnt As Integer
Dim bCopy As Boolean
    If (Range("C6").Value = "") Then 'セルC6に入力されていない？
        MsgBox "商品IDが入力されていません。", vbExclamation
        Exit Sub '処理を終了する
    End If
    Sheet3.Cells.Clear '全てのセルをクリアする
    Sheet3.Range("A1:J1").Value = Sheet2.Range("A1:J1").Value
    strId = Range("C6").Value
    iLast = WorksheetFunction.CountA(Sheet2.Columns(1))
    iCnt = 1 'コピー先セル行の先頭行(実際は2行目からですので1とする)
    For iRow = 2 To iLast
        bCopy = False
        If (Sheet2.Cells(iRow, 3).Value = strId) Then '入力した文字列と一致？
            bCopy = True
        End If
        If (bCopy = True) Then '一致？
            iCnt = iCnt + 1 コピー先のセル行を1つ進める
            Sheet3.Range(Sheet3.Cells(iCnt, 1), Sheet3.Cells(iCnt, 10)).Value = _
            Sheet2.Range(Sheet2.Cells(iRow, 1), Sheet2.Cells(iRow, 10)).Value
        End If
    Next iRow
    Sheet3.Select
End Sub

VBAの説明

入力された条件データのクリア処理は問題無いと思いますので、データ抽出処理のみの説明をします。

VBAの説明
If (Range("C6").Value = "") Then MsgBox "商品IDが入力されていません。", vbExclamation Exit Sub End If 入力条件の妥当性チェックを行います。
Sheet3.Cells.Clear 検索結果シートの全セルデータをクリアします。前回まではSheets("res")のような形式で指定していました。しかしシート名を変更してしまった場合には正しく動作しなくなります。そこで直接Sheet3と指定すればシート名を変更しても影響されなくなります。
Sheet3.Range("A1:J1").Value = Sheet2.Range("A1:J1").Value データシートのタイトル部分を検索結果シートへコピーします。 Rangeは、今まで一つのセルのみ指定していました。今回のような連続したセルの指定方法はRange("左上セル:右下セル")とします。
strId = Range("C6").Value iLast = WorksheetFunction.CountA(Sheet2.Columns(1)) iCnt = 1 入力された商品IDの検索条件をstrIdへコピーします。データシートの入力されているセル数(つまり最後のセル位置)をiLastへ設定します。対象データを検索結果シートへコピーする行位置のiCntを初期化します。
For iRow = 2 To iLast ・・・ Next iRow データシートの2行目からiLast(最終行)まで処理を繰り返しますが、その処理内容を別にして説明します。
Sheet3.Select Sheet3シートを選択して表示します。

チェックとコピー部分の繰り返し処理の説明をします。

VBAの説明
bCopy = False コピーチェックのための結果を初期化します。
If (Sheet2.Cells(iRow, 3).Value = strId) Then bCopy = True End If データシートの商品IDと入力された商品IDが一致した場合にbCopyをTrueにします。
If (bCopy = True) Then iCnt = iCnt + 1 Sheet3.Range(Sheet3.Cells(iCnt, 1), Sheet3.Cells(iCnt, 10)).Value = _ Sheet2.Range(Sheet2.Cells(iRow, 1), Sheet2.Cells(iRow, 10)).Value End If bCopyがTrueの場合に、データを検索結果シートへコピーします。ここでの連続したセルの指定方法は、毎回変化させないといけませんのでシート名.Range(左上セル, 右下セル)の各セル指定はシート名.Cells(行, 列)の形式で指定します。なお _ (アンダーバー)はVBAを複数行で記入するときに、次の行のVBAへ継続を意味します。

今回のまとめ

今回は条件に合致したデータを全てコピーしましたが、一般的には使う列データだけのコピーになると思います。なお条件チェックが複数ある場合には、初期値をTrueにしておいて条件に合致しないときにFalseにする方法もあります。

NO.8 入力されているデータの抽出

シートの作成

VBAの作成

VBAの説明

今回のまとめ

関連ページへのリンク