いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)条件に合うものをすべて抽出する関数

 Excelで関数を使い、条件に該当する値と、同じ行にある値をすべて抽出する方法についてです。
 FILTER関数が使えれば簡単ですが、使えないバージョンではいくつかの関数を組み合わせることで実現できます。

FILTER関数を使う方法

 次の画像では、B3:D9の範囲のデータのうち、県名がF3セルに入力されている値(愛知県)と同じである3つの行を抽出しています。

 H3セル

=FILTER(B3:D9,B3:B9=F3)

 FILTER関数を使うことで簡単に抽出できます。
 ただし「B3:B9=F3」という条件の記し方が独特で、対象範囲(B3:B9)と値(F3)を比較する式で条件を指定する必要があります。この点がCOUNTIF関数やSUMIF関数などとは異なります。
 なお、この条件式により条件に該当する行をTRUEに、そうでない行をFALSEに変換した配列が得られ、TRUEに対応する行が抽出されます。

複数条件(AND,OR)の場合

 条件を複数指定する場合の例です。AND条件とOR条件の場合に分けて紹介します。


 まずはAND条件(かつ)の場合です。
 次の画像ではB3:D9の範囲のデータのうち、面積がF3セルの値(100)以上かつG3セルの値(1000)以下である2つの行を抽出しています。

 I3セル

=FILTER(B3:D9,(D3:D9>=F3)*(D3:D9<=G3))

 2つの条件をカッコで囲み「*」でつないでいます。これにより両方の条件に該当する行を「1」に、そうでない行を「0」に変換した配列が生成されます。これをFILTER第2引数にすることにより配列中の「1」に対応する行(正確には0でない値に対応する行)、つまり両方の条件に該当する行が抽出されます。
 ついAND関数を使いたくなりますが、AND関数の結果は1つの値にしかならない(各行が条件に該当するかどうかを判定した配列が得られない)ので、うまくいきません。



 続いてOR条件(または)の場合です。
 次の画像ではB3:D9の範囲のデータのうち、面積がF3セルの値(1000)以上またはG3セルの値(100)以下である5つの行を抽出しています。

 I3セル

=FILTER(B3:D9,(D3:D9>=F3)+(D3:D9<=G3))

 2つの条件を「+」でつないでいるのが大きな違いです。
 これにより両方の条件に該当する行を「2」に、1つの条件だけ該当する行を「1」に、両方該当しない行を「0」に変換した配列が生成されます。これをFILTER第2引数にすることにより配列中の「2」か「1」に対応する行(0でない値に対応する行)、つまり1つ以上の条件に該当する行が抽出されます。
 ここでもOR関数を使うとうまくいかないので注意してください。

INDEX関数等を使う方法

 FILTER関数が使えない古いバージョン向けの方法です。ただし新しいバージョンでも動作します。
 なお動作はExcel2010で確認していますが、画像はMicrosoft 365で実行したものです。


 ここではB3:D9の範囲のデータのうち、県名がF3セルに入力されている値(愛知県)と同じである行を抽出します。
 まずH3セルに次の式を入力し、H9セルまでフィルコピーします。

 H3セル(H9セルまでフィルコピー)

=COUNTIF(B$3:B3,F$3)

 この式をコピーすることで、例えばH9セルでは「=COUNTIF(B$3:B9,F$3)」となります。絶対参照($)のはたらきに注意してください。
 この式はつまり「その(式が入っている)セルと同じ行までに、F3セルと同じ県名がいくつカウントする」という意味になります。結果的に条件に該当する行では1,2…と加算され、それ以外の行では0または1つ上の行と同じ値になります。
 よって、あとは1,2…の各数字が初めて出現する行のデータを取り出せばよいことがわかります。



 そこで、I3セルに次の式を入力しI9セルまでフィルコピー、さらにK列までコピー(手順の一例としてはI3:I9を選択した状態でI9セル右下の緑の四角をK9セルまでドラッグしてください)すると完成となります。

 I3セル(I9セルへ→K9セルへフィルコピー)

=INDEX(B$3:B$9,MATCH(ROW(A1),$H$3:$H$9,0))

 ちょっと難しいですが、内容は次のようになっています。

  1. MATCH関数によりH3:H9の範囲から「1」(=ROW(A1))が何番目(nとします)にあるか取得する。
  2. INDEX関数でB3:B9の範囲うち、そのn番目にある値を抽出する。

 下方にコピーすることにより「2」(=ROW(A2))「3」(=ROW(A3))…の位置を取得できるので条件に該当する2番目,3番目…に該当する値が抽出でき、さらに右方向にコピーすることによりC列,D列の値を抽出できることがポイントです。
 絶対参照の使い方のほか、MATCH関数の「0」(完全一致)にも注意してください。



 なお、エラーを表示させたくない場合は次のようにします。
 以下の例でも同様ですが、以下ではこの処理は省略します。

 I3セル(I9セルへ→K9セルへフィルコピー)

=IFERROR(INDEX(B$3:B$9,MATCH(ROW(A1),$H$3:$H$9,0)),"")

複数条件(AND,OR)の場合

 条件を複数指定する場合の例です。AND条件とOR条件のそれぞれの場合について紹介します。
 必要となる「値のカウントの仕方」が異なるだけで基本的な考え方は1つ上の例(条件が1つ)と同様ですので、カウントすることの意味やINDEX関数を使った抽出の内容についてはそちらの説明を確認ください。


 はじめにAND条件(かつ)の例です。
 ここではB3:D9の範囲のデータのうち、面積がF3セルの値(100)以上かつG3セルの値(1000)以下である市町の行を抽出します。
 まずI3セルに次の式を入力してI9セルまでフィルコピーします。

 I3セル(I9セルまでフィルコピー)

=COUNTIFS(D$3:D3,">="&F$3,D$3:D3,"<="&G$3)

 基本的な考え方は1つ上の例(1つの条件)と同様ですが、AND条件でカウントするためにCOUNTIFS関数を使っています。



 あとはJ3セルに次の式を入力しJ9セルまでフィルコピー、さらにL列までコピーすると完成となります。

 J3セル(J9セルへ→L9セルへフィルコピー)

=INDEX(B$3:B$9,MATCH(ROW(A1),$I$3:$I$9,0))

 


 次にOR条件(または)の場合です。いきなり完成していますが、面積がF3セルの値(1000)以上またはG3セルの値(100)以下である市町の行を抽出しています。
 I3セルとJ3セルに次の式を入力し、それぞれフィルコピーして完成となっています。

 I3セル(I9セルまでフィルコピー)

=COUNTIF(D$3:D3,">="&F$3)+COUNTIF(D$3:D3,"<="&G$3)

 単純にCOUNTIF関数を2つ使い、それぞれの条件でカウントした結果を足しています。
 ただしOR条件の内容によってはこのように単純にカウントすると二重カウントしてしまい、適切でない結果になる可能性があります(例として「愛知県」or「面積100が以上」の場合、豊橋市を2回カウントしてしまいます)。その場合は一例として次の画像で紹介している式を使ってください。


 J3セル(J9セルへ→L9セルへフィルコピー)

=INDEX(B$3:B$9,MATCH(ROW(A1),$I$3:$I$9,0))

 


 なお、I列に使うカウント用の数式については次のようにする方法もあります。
 この数式ではOR条件における二重カウントの問題を回避できます。


 I3セル(I9セルまでフィルコピー)

=SUMPRODUCT(((D$3:D3>=F$3)+(D$3:D3<=G$3)>0)*1)

 ちょっと難しくなりますが、FILTER関数でのOR条件と似た感じの式です。
 それぞれの条件に該当したら1、しなかったら0に換算して足し算し、結果が0でなければ(2または1なら)1つにカウントするという内容です。両方の条件に該当する行も1としてカウントするので、二重カウントされません。
 条件が3つ以上の場合も同様に条件をカッコで囲んで「+」でつなげばOKです。


 なお、COUNTIF関数を使った時の二重カウントの問題と回避策については次の記事で詳しく紹介・説明していますので参考まで。

(Excel)COUNTIF(S)関数で複数条件(AND,OR)を指定する方法 - いきなり答える備忘録