こんにちは！今日は、Excelで特定の条件に合致するデータを自動的に抽出する方法を紹介します。
データリストから「ある製品の価格を出したい」や「ある品種の基準値を出したい」といった悩み、ありませんか？このVBAコードを使えば、データリストにある情報を自動で読み取り指定した品種やカテゴリに該当する項目を出力する事ができます。これを使って作業を自動化し、手動操作によるミスも防げます。
本記事はVBAコードを丸ごとコピーして利用したい方や、ある程度読める方、他者のコードを参考にしたい方などに向けて記載しておりますので、一つ一つの細かな説明は割愛しております。
使用するデータ表
まず使用するデータ表について解説します。
データ表の構成
具体的には、「№」「分類」「品名」「メーカー」「価格」という構成になっており、１４行分のデータを作成しました。このデータ表にはすべての列が同じになる重複行は存在しません。
検索条件
次に検索条件の説明を行います。
今回の検索条件には「分類」「品名」「メーカー」を指定しており、下図のように「検索条件」というワークシート上の入力した値が条件として使用されます。ちなみに条件セルは入力規則にてリスト化して選択肢を出すようにしました。
ヒント
今回のサンプルでは入力規則にて選択リスト化していますが、項目数が多い場合には分類の選択により、品名やメーカー名の選択肢を変化させるマクロを作成する事で操作性を向上させることが可能です。この部分については今回説明を省略します。
結果の表示
抽出した価格は下図の赤枠のように「価格」を表示します。
コード解説★★★（コピーはここ）★★★
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
Sub Search() ' 検索条件を格納するための変数を宣言 Dim Cond1 As String, Cond2 As String, Cond3 As String ' 検索を開始する行と価格を格納するための変数を宣言 Dim Search_Y As Long, Price As Long ' 検索条件のセルから値を取得し、変数に格納 Cond1 = Range("検索条件!B4").Value ' 分類の条件 Cond2 = Range("検索条件!C4").Value ' 品名の条件 Cond3 = Range("検索条件!D4").Value ' メーカーの条件 ' 検索開始行を設定 Search_Y = 4 ' 検索対象のワークシートをアクティブにする Worksheets("データ").Activate ' データ表の行を1行ずつチェックし、指定した条件がすべて一致するまでループ Do While Cells(Search_Y, 3).Value <> "" ' 分類が空白になるまでループ ' 条件がすべて一致するか確認する。 If Cells(Search_Y, 3).Value = Cond1 And _ ' 分類が一致 Cells(Search_Y, 4).Value = Cond2 And _ ' 品名が一致 Cells(Search_Y, 5).Value = Cond3 Then ' メーカーが一致 ' 一致した行の価格を取得してPriceに格納 Price = Cells(Search_Y, 6).Value End If ' 次の行をチェックするために行を1つ進める Search_Y = Search_Y + 1 Loop ' 検索結果の価格を検索条件シートのB8セルに出力 Range("検索条件!B8").Value = Price End Sub |
解説
今回のコードは大きく分けて「変数の宣言」「検索条件の取得」「検索／価格取得」「検索結果の格納」の４つに分かれています。ではそれぞれ順番に説明していきます。
「変数の宣言」
|
1 2 |
Dim Cond1, Cond2, Cond3 As String '検索条件の宣言 Dim Search_Y, Price As Long |
この２行でコードで使用する変数を宣言しています。
１行目：入力した検索条件を格納するための変数
２行目：検索行と価格を格納するための変数
「検索条件の取得」
|
1 2 3 |
Cond1 = Range("検索条件!B4").Value Cond2 = Range("検索条件!C4").Value Cond3 = Range("検索条件!D4").Value |
ここでは宣言した変数へ検索条件を格納しています。
「検索／価格取得」
|
1 2 3 4 5 6 7 8 |
Do While Cells(Search_Y, 3).Value <> "" If Cells(Search_Y, 3).Value = Cond1 And _ Cells(Search_Y, 4).Value = Cond2 And _ Cells(Search_Y, 5).Value = Cond3 Then Price = Cells(Search_Y, 6).Value End If Search_Y = Search_Y + 1 Loop |
データ表から検索条件に合致する項目を繰返し処理にて検索しています。
１行目の「DO While」と最終行の「LOOP」が繰返しを意味し、「検索対象が空白になるまで」繰り返しています。
２行目の「IF･･･」から６行目の「End If」の部分では検索条件に合致するセルの価格を「Price」という変数へ格納しています。
７行目の処理にて次確認する行を１つ進めます。
ヒント
今回は省略していますが、重複行が存在しないことからPriceへ格納処理を行えば以降の行は処理不要ですので、終了処理を組み込むことで動作速度を改善する事が出来ます。
「検索結果の格納」
|
1 |
Range("検索条件!B8").Value = Price |
Priceへ格納した価格データを「検索条件」のワークシートのセルＢ８へ入力します。
使用している技術
基本：DO-LOOP、IF-THEN-ELSE
最後に
今回の使用例は検索して見つかれば格納するという単純なものです。組織での運用を考える際には「見つからなかった時の処理」も追加検討が必要となりますが、一時的な個人利用の範囲ではこれで十分かと思います。少しでも皆様のお悩み解決に繋がればうれしいです。
コメント