エクセル(Excel)の使い方/関数・数式

エクセル(Excel)で条件に合うデータを抽出する方法とは? 関数のテクニックを分かりやすく解説

関数を使用して、条件を満たす複数のデータを表から取り出すエクセル(Excel)のテクニックを詳しくご紹介します。VLOOKUP関数で物足りない場合はこちらをご利用ください。煩わしい手間も省け時短になるためしっかりマスターしましょう。

緑川 吉行

執筆者:緑川 吉行

エクセル(Excel)の使い方ガイド

エクセル(Excel)関数で条件に合うものをすべて抽出する方法!

関数で条件に合うものをすべて抽出する方法とは

今回は、関数を使用して、条件を満たす複数のデータを表から取り出すテクニックを紹介します。VLOOKUP関数を使用した場合、取り出せるデータを1行分だけ、ということもあり、読者の方々からの問合せが比較的多い内容でした。この機会に、ぜひマスターしてください。
<目次>

条件に合うものをすべて抽出するテクニックとは

VLOOKUP関数」を使用した場合、表から取り出せるデータはひとつだけでした。
 


今回紹介するテクニックを使用すれば、条件を満たす全てのデータを取り出すことができます。
 
 

実現するための3つのSTEP

条件を満たす全てのデータを取り出すには、下図のような作業用の列を準備して、次の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関数の組み合わせてデータを取り出す数式を入力

INDEX関数とMATCH関数の組み合わせてデータを取り出す数式を入力



そして、このままだと、取り出した行数より下の行でエラーが発生するので、IF関数を組み合わせます。ここでは、数式を入力した行の連番が、取り出し元で算出した連番の最大値より大きい場合に何も表示しないようにしています。
 
エラーが発生しないように数式を修正

エラーが発生しないように数式を修正

 

最後の仕上げ

最後に、作業用の列を非表示にします。ここでは、作業用の列だったD~E列を選択してマウスの右ボタンをクリックし、表示されたショートカットメニューで「表示しない」をクリックします。
 


これで完成です。
 


【関連記事】
【編集部おすすめの購入サイト】
楽天市場で Excel 関連の商品を見るAmazon で Excel 関連の商品を見る
※記事内容は執筆時点のものです。最新の内容をご確認ください。
※OSやアプリ、ソフトのバージョンによっては画面表示、操作方法が異なる可能性があります。

あわせて読みたい

あなたにオススメ

    表示について

    カテゴリー一覧

    All Aboutサービス・メディア

    All About公式SNS
    日々の生活や仕事を楽しむための情報を毎日お届けします。
    公式SNS一覧
    © All About, Inc. All rights reserved. 掲載の記事・写真・イラストなど、すべてのコンテンツの無断複写・転載・公衆送信等を禁じます