PartY  攻略 データ検索と抽出

関数あらかると


リスト範囲から条件に一致するデータのみを抽出する。
この件はよく質問を頂きます。


条件検索とその条件に合致するデータ抽出を下記 @、A の2つの方法を例に説明します。

@関数を組み合わせてのデータ抽出
Aフィルタオプションの設定機能を使ってのデータ抽出
 尚、フィルタオプションの設定によるデータ抽出は検索値が変更都度フィルタオプションの設定を行わなければならないので効率的、迅速的観点から検索条件を入力後にボタン押下一発で検索するようにVBAでソースを書いています。

■ リスト範囲とシートの構図


          

上図、シートはリスト範囲と2つの検索&データ抽出を兼ね備え、セルA列〜D列までをリスト範囲(検索データ対象範囲)、セルG列〜K列が1つ目の検索指定方法となる関数を組み合わせてのデータ抽出サンプル、セルM列〜P列までが2つ目のフィルタオプションの設定によるデータ抽出サンプルとなりす。

■ 抽出例その1 関数組み合わせ編

セルG列からK列に定義する内容の説明です。検索条件となるセルG3へ検索値「所在」を入力すると対象となるデータをセル列I、J、Kへ抽出します。

@検索のための自動ナンバリング
 リスト範囲を注目下さい。セルA列が空欄となっています。用途は仮想の検索番号をナンバリングするために用意したもので 抽出例その1のワークセルとなります。
セルA2の関数式: =IF($G$3=B2,MAX($A$1:A1)+1,"")
検索指定値セルG3とリストの値が同じかの判断を行い同じ場合に MAX($A$1:A1)+1 これは $A$1:A1 のセル範囲の最大値に+1した値を求めます。要するにここでの最初の最大値は図を見ればわかる通り、セルA1の値0となり、1つカウントアップするので求まられる値は 1 。 つまり、「条件に合致したデータ行に対して1から順にナンバリングを行う」といったことになります。

セルA3の数式  =IF($G$3=B3,MAX($A$1:A2)+1,"") 条件に合致している場合は絶対セルA1からA2を範囲に最大値を求めその値に+1した値を代入する。

AセルI列の数式  (セルI3)

  =IF(ISERROR(VLOOKUP(ROW()-2,$A$1:$D$10,COLUMN()-7,FALSE)),"",
            VLOOKUP(ROW()-2,$A$1:$D$10,COLUMN()-7,FALSE))

今回のチョイスした関数はVOOKUP関数です。ナンバリングした値をキーにVLOOKUP関数で検索を実行します。
IF(ISERROR(VLOOKUP(ROW()-2,$A$1:$D$10,COLUMN()-7,FALSE)) このIF文は、VLOOKUP関数がエラーを引き起こしていないか(ISERROR関数)を判断します。ここでのエラーとはデータ未検知(#N/A)となります。真の時は "" 空白を代入します。
偽の時は VLOOKUP(ROW()-2,$A$1:$D$10,COLUMN()-7,FALSE) の値を代入します。
数式を分解すると...

  • 検索値 ROW()-2 ROW関数は引数として定義したセルの行番号を返します。ここでは、セルI3の行数(3)が返値です。その値から-2を求めます。セルI3の場合は 3-2= 1 であり「1番目の検索条件に合致したデータを抽出」するための式です。
    次のセルI4の場合は 4-2= 2 となり2番目の検索条件に合致したデータの抽出となります。
  • 検索範囲 $A$1:$D$10
  • 列番号 COLUMN()-7 COLUMN関数は引数として定義したセルの列番号を返します。ここではセルI3の列数(9)が返値です。その値から-7する値(2)とは列で参照するとB列となります。
    VLOOKUP関数 「リスト範囲の中から検索値に合致するデータ行の2番目のセルのデータを抽出」 といった内容です。
    セルJ列も同じ数式ですがJ列の場合の列番号は10です。 10-7= 3 列で参照するとC列となります
  • 検索の型 FALSE  検索値と完全に一致する値だけを検索します。

ROW関数とCOLUMN関数を何故使わなければならないかについては考えてみて下さい。


セルJ列、K列の式は、I列と同様ですので省略します。


■ 抽出例その2 フィルタオプションの設定編

次はセルM列からP列に定義するフィルタオプションの設定です。検索条件となるセルM3へ検索値「所在」を入力すると対象となるデータをセル列N、O、Pへ抽出します。

フィルタオプションを設定する場合は、リスト範囲で定義する項目名と検索抽出側の項目名が同名であることが条件となります。
@データ抽出側の項目名は、検索値入力セルM2の項目名「所在」がセルB1、データ抽出セルN2の「所在」がセルB1、O2の「事業所」がセルC1、P2の「売上実績」がセルD1と同一名で入力します。

Aフィルタオプションの設定を選択


メニューバー「データ」 → 「フィルタ」 → 「フィルタオプションの設定」を選択します。

Bフィルタオプションの定義

図の通り指定します。指定時は範囲を確認しながら行って下さい。
本来は、条件を入力都度この設定を行う必要がありますがCのVBAで自動化を行います。


Cフィルタオプションの設定の自動化

VBAソースコード

Sub 検索()
'フィルタオプションの実行
  Range("B1:D10").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
  "M2:M3"), CopyToRange:=Range("N2:P2"), Unique:=False
End Sub


検索条件を入力し検索・抽出を実行する場合はこのボタンをクリックします。

■ お試しブックのダウンロード

その1とその2の条件で作成したブックをダウンロードすることができます。

ブック名: exck840.lzh  Download File: exck840.lzh