指定した文字列を含むデータを関数で全て抽出する方法

ここでは、INDEX関数を使って「指定した文字列を含むデータを全て抽出する方法」を解説します。

 

数式の作り方と、特定の文字列を含めない数式も紹介します。

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

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

 

やりたいことは、「指定した文字列を含むデータを全部抽出する」です。

 

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

上の表は、社員の所属や勤続年数、住所を記した一覧表です。

 

この表から、「所属に『営業』という文字列が入っている人のデータを全部抜き出したい」、というのがやりたいことです。

条件を「所属に『営業』という文字列が入っている人」とし、データを抜き出したのが右側の表です。

 

このように、「元の表から指定した文字列を含むデータを抜き出せる数式」について解説します。

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

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

 

2-1.数式の形は

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

=IFERROR(INDEX(抽出列,1/LARGE(INDEX((ISNUMBER(FIND(条件値,条件範囲)))/ROW(条件範囲),0),ROW(A1))),"")

 

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

 

2-2.引数の入れ方

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

 

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

 

2-2-1.抽出列

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

 

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

 

2-2-2.条件値

「条件値」は、そのままですが「条件値」を指定します。

 

文字列や日付、時刻の場合には、条件値を「"」で囲みます。セル番号や数値の場合には、そのまま入れます。

 

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

例)$A$1

 

2-2-3.条件範囲

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

 

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

 

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

3.数式を作ってみる

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

 

以下、手順です。

 

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

【コピーする数式】

=IFERROR(INDEX(元データの最左列,1/LARGE(INDEX((ISNUMBER(FIND(条件値,条件範囲)))/ROW(条件範囲),0),ROW(A1))),"")

 

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

 

(3)「条件値」の文字を消し、指定する

※ セル番号で指定する場合には、入力後にF4キーを1回押します

 

(4)「条件範囲」の文字を消し、指定した後でF4キーを1回押すことを2か所に行う

 

(5)Enterキーを押す

 

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

4.含めない条件にしたい時

最後に、「指定の文字列を含めない」条件にできる数式も紹介します。

 

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

=IFERROR(INDEX(抽出列,1/LARGE(INDEX(NOT((ISNUMBER(FIND(条件値,条件範囲))))/ROW(条件範囲),0),ROW(A1))),"")

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

 


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


Top