関数で条件に合うものをすべて抽出する方法とは
今回は、関数を使用して、条件を満たす複数のデータを表から取り出すテクニックを紹介します。VLOOKUP関数を使用した場合、取り出せるデータを1行分だけ、ということもあり、読者の方々からの問合せが比較的多い内容でした。この機会に、ぜひマスターしてください。
条件に合うものをすべて抽出するテクニックとは
「
VLOOKUP関数」を使用した場合、表から取り出せるデータはひとつだけでした。
今回紹介するテクニックを使用すれば、条件を満たす全てのデータを取り出すことができます。
実現するための3つのステップ
条件を満たす全てのデータを取り出すには、下図のような作業用の列を準備して、次の3つのステップを踏んで実現します。
STEP1：IF関数を使用して、条件を満たす該当行を識別します。
STEP2：COUNTIF関数を利用して、該当行に連番を振ります。
STEP3：INDEX関数とMATCH関数を組み合わせて、該当行からデータを取り出します。
STEP1の数式作成
まず、仕入先の条件が入力されているセルと、取り出し元の仕入先のデータを比較して、条件と同じデータが入力されている行に「該当」という文字列を表示するIF関数を作成します。これで、条件を満たしている行を識別できました。
STEP2の数式作成
続いて、「該当」という文字が表示された行を対象に連番を振ります。そのために、
COUNTIF関数を利用して、「『該当』と入力されたセルの個数」を算出します。
ポイントは、COUNTIF関数の検索セル範囲が、1行目に入力したCOUNTIF関数は1行目だけ、2行目に入力したCOUNTIF関数は1～2行目、3行目に入力したCOUNTIF関数は1～3行目となるように工夫することです。つまり、「1行目からCOUNTIF関数が入力されている行までのセル範囲」を検索セル範囲とするわけです。
そのためには、検索セル範囲の「開始セル」を絶対参照にし、「終了セル」はそのままで数式を下の行へコピーします。これで、開始セルが固定されたまま、終了セルが行位置に対応して変動するので、1行目からCOUNTIF関数が入力されている行までのセル範囲を検索セル範囲とすることができます。
そして、IF関数を組み合わせて、「該当」と表示されている行だけにCOUNTIF関数で算出した個数を表示させます。これで、「『該当』と入力されたセルの個数」が「連番」の代わりになります。
取り出し先に連番を入力
STEP3の数式作成に入る前に、取り出し先に「連番」を入力しておきます。
連番の役割が大きなポイント
ここで、取り出し元のリストを「COUNTIF関数で算出した連番」も含めた形で見直してみましょう。すると、取り出し先の連番と見出しを使用して、取り出し元からデータを特定できることがわかります。これなら、ガイド記事「
早見表からデータを取り出そう」で紹介したINDEX関数とMATCH関数の組み合わせテクニックでデータを取り出すことができますね。
STEP3の数式作成
INDEX関数とMATCH関数の組み合わせた数式は、次のようになります。
複合参照を利用して、行を特定するMATCH関数の引数で列位置を固定し、列を特定するMATCH関数の引数で行位置を固定している部分がポイントです。
INDEX関数とMATCH関数の組み合わせてデータを取り出す数式を入力
|
そして、このままだと、取り出した行数より下の行でエラーが発生するので、IF関数を組み合わせます。ここでは、数式を入力した行の連番が、取り出し元で算出した連番の最大値より大きい場合に何も表示しないようにしています。
エラーが発生しないように数式を修正
|
最後の仕上げ
最後に、作業用の列を非表示にします。ここでは、作業用の列だったD～E列を選択してマウスの右ボタンをクリックし、表示されたショートカットメニューで「表示しない」をクリックします。
これで完成です。
【関連記事】
