この記事では、Index関数とMatch関数を使って表から検索対象のデータを抽出する方法についてご説明します。
ただし、条件に合うデータが複数ある場合はいくつか手間が必要なので、その点についても詳しくご説明します。
【動画】Index関数とMatch関数を使って表から検索対象のデータを抽出する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
顧客名に該当するデータを左側の表からIndex関数とMatch関数を使って抽出し、該当したデータを右側の表に出力しています。
今回のデータは、該当する顧客のデータが複数あるため、Index関数とMatch関数を使って複数あるデータに該当する項番(A列)を抜き出してユニークで並べて(J列)、その項番を元にさらにIndex関数とMatch関数を使って右の表に出力しています。
ちなみに、ユニークで項番をJ列に並べるのにUnique関数を使用していません。(Unique関数はOffice365から使用可能。使用しているのはINDEX関数とMATCH関数、COUNTIF関数です)
Excelファイルの例
今回は次のExcelファイルを用意しました。
以上のExcelファイルは、左の表(A列からH列)にいくつかの店舗データが存在しており、セルA3に入力された顧客名に該当する店舗データを抽出して右側の表(K列からR列)に出力するExcelファイルです。
セルA3にD列の顧客名のどれかを入力すると、右側の表にその顧客名に該当する店舗のデータだけが出力されます。
前提
今回の記事は、以下のポイントを前提として説明していきます。
- 一意の値が入っている列が表に存在していること
- 条件に合うデータが表の中に複数ある場合
- 検索条件は一つだけ
【前提①】一意の値が入っている列が表に存在していること
検索値に該当するデータが表の中のどの行に存在するのかを特定するために、一意の値が入っている列が表に存在していることが前提になります。
今回用意したExcelファイルだと、A列が一意の値が入っている列になります。
【前提②】条件に合うデータが表の中に複数ある場合
今回の記事は、条件に合うデータが表の中に複数あることが前提の内容となっています。
【前提③】検索条件は一つだけ
今回の記事は、検索条件は一つだけであることが前提の内容となっています。
以上、3つの前提を踏まえてご説明していきます。
Excelファイルの作成の流れ
今回例で挙げたExcelファイルで言うと、左の表の、再右列の隣の列の1行目はセル「I6」になります。
今回例で挙げたExcelファイルで言うと、セル「I6」をセル「I25」までコピーします。
今回例で挙げたExcelファイルで言うと、セル「I6」のセルの右隣りのセルは「J6」になります。
今回例で挙げたExcelファイルで言うと、セル「J7」をセル「J25」までコピーします。
今回例で挙げたExcelファイルで言うと、STEP.3のセルの右のセルは「K6」になります。
今回例で挙げたExcelファイルで言うと、セル「K6」を「K25」までコピーします。
今回用意したExcelファイルの作成の流れ
①Index関数とMatch関数でキーとなるセルの値を抽出
【STEP.1】左の表の、再右列の右隣の列の1行目に、検索値に該当する項番を抽出するIndex関数とMatch関数を埋め込む
セルI6に以下の計算式を入力します。
- =IFERROR(INDEX(A6:$H$25,MATCH(A$3,D6:D$25,0),1),””)
【STEP.2】STEP.1の計算式が埋め込まれたセルを、表の最後の行位置までコピーする
I6のセルを、最後の行位置(セルI25)までコピーします。
②合致したデータが何行目にあるかをユニークでリストアップ
【STEP.3】STEP.1のセル「I6」の値を、その右隣りのセルから参照する
J6のセルに、I6のセルを参照する計算式を入力します。
- =I6
【STEP.4】I列の値をユニークで取得する計算式をセル「J7」に埋め込む
セルJ7に以下の計算式を入力します。
- =IFERROR(INDEX(I$6:I$25,MATCH(0,INDEX(COUNTIF(J$6:J6,I$6:I$25),),0)),””)
【STEP.5】STEP.4の計算式が埋め込まれたセルを、表の最後の行位置までコピーする
J7のセルを、最後の行位置(セルJ25)までコピーします。
③顧客名に該当する店舗のデータを抽出して表に出力する
【STEP.6】STEP.3のセルの値を条件に、合致する店舗データを抽出するIndex関数とMatch関数をSTEP.3のセルの右のセルに埋め込む
セルK6に以下の計算式を入力します。
- =IFERROR(INDEX(A$6:H$25,MATCH(J6,A$6:A$25,0),0),””)
【STEP.7】STEP.6の計算式が埋め込まれたセルを、表の最後の行位置までコピーする
K6のセルを、最後の行位置(セルK25)までコピーします。
以上で手順は完了です。
Excelファイル作成の解説
ここからは先ほどお話したExcelファイルの作成の流れについて詳しく説明していきます。
①Index関数とMatch関数でキーとなるセルの値を抽出
【STEP.1】左の表の、再右列の右隣の列の1行目に、検索値に該当する項番を抽出するIndex関数とMatch関数を埋め込む
左の表の、再右列の右隣の列の1行目に、検索値に該当する項番を抽出するIndex関数とMatch関数を埋め込みます。
埋め込んだ計算式は次の通りです。
- =IFERROR(INDEX(A6:$H$25,MATCH(A$3,D6:D$25,0),1),””)
- セルA3の値がD列の顧客名の中に存在するかを確認し、存在していればA列の項番をセルI6に出力する。ただし、もし存在していなければ空白を出力する。
- 検索文字列(セルA3の値)が含まれる行のA列の値
①Match関数
Match関数は、検索文字列を、検索したい範囲のセルの中から検索し、何行目にあるのかを検索する関数です。
検索して見つかった場合は、検索したセル範囲の上から数えて何行目かを返します。
Match関数の1つ目の引数Match関数の1つ目の引数には検索文字列を指定します。
今回はセルA3に入力された文字列のデータを検索したいので、1つ目の引数にこのセルA3の値を指定しています。
なお、「A3」ではなく「A$3」と記述しているのは、行3の位置を絶対参照するためです。
絶対参照とは、セルの参照位置がずれないように参照する、という意味です。
今回はこのMatch関数使った計算式をセルI25までコピーして使いたいのですが、絶対参照に設定しておかないと、3の値が変わってしまいます(ずれてしまいます)
下の画像は、試しにセルI6の計算式を一つ下のセルにコピーした時の画像です。
絶対参照に設定しておかないと、セルA4を参照してしまいます。(参照位置が一つずれる)
絶対参照に設定しておくと、セルの参照位置はずれません(セルA3を参照したまま)
一つ下の位置にコピーしてもA3のセルを参照したい場合は「A$3」と記述する必要があります。
検索文字列のセルを参照する場合は絶対参照するように記述します。
Match関数の2つ目の引数Match関数の2つ目の引数には検索文字列を検索するセルの範囲を指定します。
今回は検索するセルの範囲が左の表の中なので、セルA6からH25を検索範囲として2つ目の引数に「D6:D$25」を指定しています。
なお、セルの指定を「D25」ではなく「D$25」と記述しているのは、セルのD25の行位置を絶対参照するためです。
絶対参照については先ほどの説明した通り、セルの参照位置がずれずに参照する指定方法ですが、「D25」の行のセル位置はずらしたくないので絶対参照で指定します。
絶対参照で指定しないと、セルをコピーした時にセルの行の位置がずれてしまいます。
Match関数の3つ目の引数には照合する種類を指定します。
この照合する種類には3つあります。
今回は完全に一致する値を検索したいので0を指定しています。
②Index関数
Index関数は、行と列を指定したセルの値を取得します。
今回は、行はMatch関数で取得した行位置、列はA列の値(項番)を取得したいので1を指定しています。
Index関数の1つ目の引数Index関数の1つ目の引数には左の表のセル範囲を指定します。
Index関数の2つ目の引数Index関数の2つ目の引数にはMatch関数で取得した行位置を指定します。
Match関数により、検索した文字列が何行目にあるのかが特定できるので、その何行目かを2つ目の引数に指定します。
Index関数の3つ目の引数Index関数の3つ目の引数には左の表のセル範囲を指定します。
列はA列の値(項番)を取得するために1を指定しています。
③IFERROR関数
IFERROR関数は、計算式がエラーを返す場合はそのセルに何かしらの値をセルに表示させる関数です。
今回は計算式がエラーを返す場合は空白を表示させるよう「””」をIFERROR関数の2つ目の引数に指定しています。
エラーではない場合は1つ目の引数の値をセルに出力します。
IFERROR関数の1つ目の引数IFERROR関数の1つ目の引数にはエラーが出るか判定させる値を指定します。
今回はIndex関数のエラー判定を行うため1つ目の引数に指定しています。
IFERROR関数の2つ目の引数IFERROR関数の2つ目の引数には、1つ目のセルの値がエラーの場合にそのままエラーは表示させずに、その代わりに表示させる値を指定します。
今回は、1つ目のセルの値がエラーの場合は空白「””」を表示させるよう、2つ目の引数に空白「””」を指定しています。
【STEP.2】STEP.1の計算式が埋め込まれたセルを、表の最後の行位置までコピーする
STEP.1の計算式が埋め込まれたセルを、表の最後の行位置までコピーします。
コピーが完了すると、コピーしたセルに値が出力されます。
コピー元とそのコピーしたセルには、6、7、8、9、10の値が出力されています。
この値は、D6からD25の中に存在する検索文字列「スーパーショップ」のセルの行の項番を指します。
これで検索文字列「スーパーショップ」が表に存在する項番が特定できました。
ただし、項番6が複数あるので、6、7、8、9、10の値をユニークで取り出します。
②合致したデータが何行目にあるかをユニークでリストアップ
【STEP.3】STEP.1のセル「I6」の値を、その右隣りのセルから参照する
STEP.1のセル「I6」の値を、その右隣りのセルから参照します。
今回例で挙げたExcelファイルで言うと、セル「I6」の右隣りのセルは「J6」になります。
セル「J6」に、左隣のセル「I6」の値が出力されました。
この値をベースに、次のSTEP.4ではJ列のセルにI列のセルの値をユニークで取得していきます。
【STEP.4】I列の値をユニークで取得する計算式をセル「J7」に埋め込む
セルJ7の一つ下のセルに、検索値に該当する項番を抽出するIndex関数とMatch関数を埋め込みます。
埋め込んだ計算式は次の通りです。
- =IFERROR(INDEX(I$6:I$25,MATCH(0,INDEX(COUNTIF(J$6:J6,I$6:I$25),),0)),””)
- セルI6からセルI25の中からセルJ6の値を検索します。
もし存在していればセルJ6と同じ値以外のセルをI6からI25のセルを上から下まで探していきます。
見つかったらそのセル(複数存在している場合、位置は複数の中の一番下にあるセル)の値を出力します。
ただし、もし存在していなければ空白を出力します。
- 検索文字列(セルJ6の値)以外の値のセルを、セルI6からセルI25の範囲を対象に上から下に探して見つかったセルの値
※複数存在している場合、複数の中の一番下にあるセルの値
①COUNTIF関数
COUNTIF関数は、検索する値が検索先にいくつ存在するのかを数えて、その数を返す関数です。
今回はセルJ6の値が、セルI6からセルI25の中にいくつあるのかを数えてその数を返します。
COUNTIF関数の1つ目の引数1つ目の引数には、検索する値のセルを指定します。
セルJ6の値がいくつあるのかを数えるために、引数にセルJ6を指定します。
COUNTIF関数の2つ目の引数2つ目の引数には、検索範囲のセルを指定します。
今回は、セルJ6の値をセルI6からセルI25の範囲で検索するので、I$6:I$25と指定しています。(指定は絶対参照で)
②MATCH関数
MATCH関数は、検索文字列を、検索したい範囲のセルの中から検索し、何行目にあるのかを検索する関数です。
今回は、COUNTIF関数が対象とするセルの範囲を対象に、COUNTIF関数が0を返すセルが何行目にあるのかを探して、特定後にその行数を返します。
検索文字列に対してCOUNTIFが0を返す行位置が特定できれば、検索文字列でない文字列が入ったセルの行位置、つまり検索文字列以外の値が入ったセルが特定できるので、ユニークな値を取得することができます。
MACTH関数の1つ目の引数MACTH関数の1つ目の引数には検索文字列を指定します。
今回は1つ目の引数に0を指定しています。
なぜ0を指定しているのかというと、セルI6からI25のセルの中からJ6以外の値を探すためです。
値が0の場合は、J6の値以外の値であることを指します。
セルI6からI25のセルの中からJ6以外の値が見つかったらその値をJ列のセルに出力します。
MACTH関数の2つ目の引数MACTH関数の2つ目の引数には、COUNTIF関数の戻り値を指定します。
COUNTIF関数の戻り値はCOUNTIF関数が検索した値がいくつあるのかを数えた数ですが、その戻り値が0である場合は第1引数で指定した0と値が一致するので、もしCOUNTIF関数の戻り値が0の場合はその0を返すセルの位置を返します。
例えば(I6からI11までのセルの値全てがJ6と一致していて)セルI12の値がセルJ6の値と一致していなければ、7を返します。(セルI6から数えて下に7行目の意)
MACTH関数の3つ目の引数には照合する種類を指定します。
この照合する種類には3つあります。
今回は完全に一致する値を検索したいので0を指定しています。
③INDEX関数
Index関数は、行と列を指定したセルの値を取得します。
また、Match関数で取得した行位置が空白の場合は、そのまま空白を返します。
今回は、行はMatch関数で取得した行位置を指定しています。
ただし、今回はIndex関数に列を指定していません。
なぜなら今回は(複数の列ではなく)I列の1列だけを対象にしているからです。
1列だけを対象にする場合は列番号の指定は省略することができます。(第3引数は無し)
INDEX関数の1つ目の引数INDEX関数の1つ目の引数には、セルI6からセルI25を指定します。(範囲の指定)
INDEX関数の2つ目の引数INDEX関数の2つ目の引数には、Match関数で取得した行位置を指定します。
④IFERROR関数
IFERROR関数は、計算式がエラーを返す場合はそのセルに何かしらの値をセルに表示させる関数です。
今回は計算式がエラーを返す場合は空白を表示させるよう「””」をIFERROR関数の2つ目の引数に指定しています。
エラーではない場合は1つ目の引数の値をセルに出力します。
IFERROR関数の1つ目の引数IFERROR関数の1つ目の引数にはエラーが出るか判定させる値を指定します。
今回はIndex関数のエラー判定を行うため1つ目の引数に指定しています。
IFERROR関数の2つ目の引数IFERROR関数の2つ目の引数には、1つ目のセルの値がエラーの場合にそのままエラーは表示させずに、その代わりに表示させる値を指定します。
今回は、1つ目のセルの値がエラーの場合は空白「””」を表示させるよう、2つ目の引数に空白「””」を指定しています。
【STEP.5】STEP.4の計算式が埋め込まれたセルを、表の最後の行位置までコピーする
STEP.4の計算式が埋め込まれたセルを、表の最後の行位置までコピーします。
STEP.4の計算式が埋め込まれたセルを、表の最後の行位置までコピーすることで、セルI6からセルI25までの値から重複しないユニークな値を抽出することができました。
以上の画像では「スーパーショップ」が検索値として、左の表に「スーパーショップ」がどの行に存在するのかを特定した結果、項番が6、7、8、9、10であることが分かりました。
次に、この「スーパーショップ」の店舗のデータを右の表に出力させるために、次のSTEPに進みます。
③顧客名に該当する店舗のデータを抽出して表に出力する
【STEP.6】STEP.3のセルの値を条件に、合致する店舗データを抽出するIndex関数とMatch関数をSTEP.3のセルの右のセルに埋め込む
STEP.3のセルの値を条件に、一致する店舗データを抽出するIndex関数とMatch関数をSTEP.3のセルの右のセルに埋め込みます。
埋め込んだ計算式は次の通りです。
- =IFERROR(INDEX(A$6:H$25,MATCH(J6,A$6:A$25,0),0),””)
- セルJ6の値が、左の表の項番(A列)と一致する店舗データを取得します。ただし、もし存在していなければ空白を出力します。
- セルJ6の値と左の表の項番(A列)とが一致する店舗データ
①Match関数
Match関数は、検索文字列を、検索したい範囲のセルの中から検索し、何行目にあるのかを検索する関数です。
検索して見つかった場合は、検索したセル範囲の上から数えて何行目かを返します。
Match関数の1つ目の引数Match関数の1つ目の引数には検索文字列を指定します。
今回はセルJ6のデータを検索したいので、1つ目の引数にこのセルJ6を指定しています。
検索して見つかった場合は、検索したセル範囲の上から数えて何行目かを返します。
Match関数の2つ目の引数Match関数の2つ目の引数には検索文字列を検索するセルの範囲を指定します。
今回はJ6の項番と一致する項番をセルA6からA25までの間で探したいので、2つ目の引数に「A$6:A$25」を指定しています。
なお、セルA6からA25の指定を「A6:A25」ではなく「A$6:A$25」と記述しているのは、行6と行25の位置を絶対参照するためです。
絶対参照とは、セルの参照位置がずれないように参照する、という意味です。
今回はこのMatch関数使った計算式をセルK25までコピーして使いたいのですが、絶対参照に設定しておかないと、行6と行25の位置が変わってしまいます(ずれてしまいます)
絶対参照に設定しておくと、セルの参照位置はずれません(セルA6からA25を参照したまま)
Match関数の3つ目の引数には照合する種類を指定します。
この照合する種類には3つあります。
今回は完全に一致する値を検索したいので0を指定しています。
②Index関数
Index関数は、行と列を指定したセルの値を取得します。
今回は、行はMatch関数で取得した行位置、列は左の表の列全てを対象に取得したいので0を指定しています。
Index関数の1つ目の引数Index関数の1つ目の引数には左の表のセル範囲を指定します。
Index関数の2つ目の引数Index関数の2つ目の引数にはMatch関数で取得した行位置を指定します。
Match関数により、検索した文字列が何行目にあるのかが特定できるので、その何行目かを2つ目の引数に指定します。
Index関数の3つ目の引数Index関数の3つ目の引数には左の表のセル範囲を指定します。
今回は引数に0を指定しています。
引数が0の場合、第1引数のセルの列範囲のデータを対象にします。
第1引数のセルがAからHの列を対象としているので、AからHの列を対象にデータを取得します。
③IFERROR関数
IFERROR関数は、計算式がエラーを返す場合はそのセルに何かしらの値をセルに表示させる関数です。
今回は計算式がエラーを返す場合は空白を表示させるよう「””」をIFERROR関数の2つ目の引数に指定しています。
エラーではない場合は1つ目の引数の値をセルに出力します。
IFERROR関数の1つ目の引数IFERROR関数の1つ目の引数にはエラーが出るか判定させる値を指定します。
今回はIndex関数のエラー判定を行うため1つ目の引数に指定しています。
IFERROR関数の2つ目の引数IFERROR関数の2つ目の引数には、1つ目のセルの値がエラーの場合にそのままエラーは表示させずに、その代わりに表示させる値を指定します。
今回は、1つ目のセルの値がエラーの場合は空白「””」を表示させるよう、2つ目の引数に空白「””」を指定しています。
【STEP.7】STEP.6の計算式が埋め込まれたセルを、表の最後の行位置までコピーする
STEP.6の計算式が埋め込まれたセルを、表の最後の行位置までコピーします。
今回例で挙げたExcelファイルで言うと、セル「K6」のセルを「K25」までコピーします。
コピーすると、J列の値と一致するA列の値の店舗データが右の表に出力されました。
(⇒項番が6、7、8、9、10(顧客名がスーパーショップ)の店舗データ)
検証
セルA3に顧客名を入力すると、左側の表にある該当する顧客の店舗データが右側に出力されるのかを検証してみます。
検証した結果は、次の通りです。
テスト①：スーパーショップを選んだ場合
スーパーショップを選んだ場合、顧客名がスーパーショップの店舗データを右側の表に出力しています。
テスト②：オーガニックフードを選んだ場合
オーガニックフードを選んだ場合、顧客名がオーガニックフードの店舗データを右側の表に出力しています。
テスト③：自然食品スーパーを選んだ場合
自然食品スーパーを選んだ場合、顧客名が自然食品スーパーの店舗データを右側の表に出力しています。
最後に
本記事では、Index関数とMatch関数を使って表から検索対象のデータを抽出する方法についてご説明しました。
Index関数とMatch関数を組み合わせて使うことで、表から検索の対象データを抽出することができます。
ただし、抽出先に検索の対象データが複数存在する場合はいくつか手順を踏まないといけません。
例えば、表から「自然食品スーパー」という顧客名の店舗データを抽出したい場合に、表には「自然食品スーパー」という顧客名の店舗データが複数存在する場合そのすべてを抽出する、といったケースです。
以上のデータ抽出を行いたい場合は、詳しくは本記事の内容を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
