範囲や期間の条件に一致するデータを関数で全部抽出する方法

ここでは、INDEX関を使って「範囲や期間の条件に一致するデータを全部抽出する方法」を解説します。

 

数式は長くなりますが、慣れるととても便利です。

1.まずはやりたいことを確認

始めに、やりたいことを確認します。

 

やりたいことは、「範囲条件に一致するデータを全部抽出する」です。

 

1つ例を見てみましょう。

上の表は、店舗ごとの売上額を入れた一覧表です。

 

この表から、「売上高の範囲を指定して該当する店舗のデータを全部抜き出したい」、というのがやりたいことです。

条件を「売上高が1,000以上1,200以下」とし、データを抜き出したのが右側の表です。

 

このように、「元の表から範囲指定した条件に合うデータのみを抜き出せる数式」について解説します。

2.数式の形と引数の入れ方

次に、数式の形と引数の入れ方を見てみます。

 

2-1.数式の形は

数式の形は、次の通りです。

=IFERROR(INDEX(抽出列,1/LARGE(INDEX((条件範囲>=下限値)*(条件範囲<=上限値)/ROW(条件範囲),0),ROW(A1))),"")

 

指定するのは、「抽出列」、「条件範囲」、「下限値」、「上限値」の4つです。

 

2-2.引数の入れ方

続いて、各引数の指定の仕方を見てみます。

 

ちなみに、ここは「こんなもんなんだ」レベルで読み流してください。次の「3.数式を作ってみる」を見ると、もっと分かると思います。

 

2-2-1.抽出列

「抽出列」には、「元データの一番左の列全体」を1列指定します。

 

例えば、元データの一番左がA列の場合には「A:A」、D列の場合には「D:D」となります。

 

2-2-2.条件範囲

「条件範囲」には、「条件に合うか判定したい範囲」を入れます。

 

なお、この部分は「$」を4つ付けて絶対参照にします。

 

例えば、条件範囲が「D3:D10」の場合には、「$D$3:$D$10」となります。

 

2-2-3.下限値・上限値

「下限値」と「上限値」を数値、またはセル番号で指定します。

 

なお、セル番号で指定する場合には「$」を2つ付けて絶対参照にします。

 

例えば、下限値がセルF3に入っている場合には「$F$3」となります。

3.数式を作ってみる

では、実際に数式を作ってみます。

 

以下、手順です。

 

(1)セルに以下の数式をコピーし、貼り付ける

【コピーする数式】

=IFERROR(INDEX(抽出列,1/LARGE(INDEX((条件範囲>=下限値)*(条件範囲<=上限値)/ROW(条件範囲),0),ROW(A1))),"")

 

(2)「抽出列」の文字を消し、指定する

 

(3)「条件範囲」の文字を消し、指定後にF4キーを1回押して絶対参照にすることを、3か所行う

 

(4)「下限値」、「上限値」の文字を消し、それぞれ指定後にF4キーを1回押して絶対参照にする

 

(5)Enterキーを押す

 

(6)セルの右下にカーソルを当て、「+」マークになったら左ドラッグで下方向と右方向に数式をコピーすれば完了

3.条件値を数式内に入力するには

続いて、条件値を数式内に入力する方法です。

 

数値と日付、時刻で入れ方が少し違います。

 

3-1.数値の場合

数値の場合には、「値をそのまま」入れます。

3-2.日付の場合

日付の場合には、「DATEVALUE関数を使いつつ、"(ダブルクォーテーション)」で囲んで入力します。

3-3.時刻の場合

時刻の場合には、「TIMEVALUE関数を使いつつ、"(ダブルクォーテーション)」で囲んで入力します。

4.下限値、上限値は含めない条件にするには

最後に、下限値や上限値を含めない、いわゆる「未満・~を超え」の指定の仕方です。

 

「未満・~を超え」の条件にするには、下限の方を「>=」ではなく「>」に、上限の方を「<=」ではなく「<」にします。

 

つまり、数式の形は次のようになります。

=IFERROR(INDEX(抽出列,1/LARGE(INDEX((条件範囲>下限値)*(条件範囲<上限値)/ROW(条件範囲),0),ROW(A1))),"")

 

これで、「未満・~を超え」の条件になって下限値・上限値が含まれません。

 

以上、参考になれば幸いです。

 


口コミ・評判のいいExcel本の紹介】


Top