対照表を使ってIDから合致したデータを抽出するINDEX関数MATCH関数MID関数の組み合わせ-GSS/Excel/関数

特定の条件に合致した値を抽出するINDEX関数とMATCH関数とMID関数の組み合わせについて説明していきます。

INDEX関数とMATCH関数やMID関数の使い方は下記でも説明をしていきました。

特定条件に合致した値を返すINDEX関数MATCH関数の組み合わせ-GSS/Excel/関数 住所から特定文字列を抽出するMID/FIND/RIGHT/LEFT/len/SUBSTITUTE/IFERROR関数の組み合わせ-GSS/Excel/関数 文字列からと特定の値を抽出したいMID関数RIGHT関数LEFT関数-GSS/Excel/関数

今回は、それにMID関数を掛け合わせて、IDからデータを逆引きするというのを説明していきます。

商品のIDや顧客IDの中に、属性を含めるということはよくありますので、そうした場合にも焦らず逆引きをしていきましょう。

使い方

=INDEX（抽出したい範囲,MATCH(検索値,検索範囲,照合の種類)）

=MID(対象セル,抽出開始位置,抽出したい文字数)

次の演習問題を使って説明していきます。

設定としては、アパレル販売店の納品リスト。

納品された商品にはIDが振られており、IDでどんな商品化がわかるようになっています。

下はIDの対照表です。

それぞれのID構成番号が何を指しているのかを表しています。

正解例

[D3]=INDEX('出題2_ID参照'!B2:B6,MATCH(MID(A3,1,2),'出題2_ID参照'!A2:A6,0))

複数の関数を組み合わせるときは、一つずつ完成させて組み合わせていくと楽に作りやすいです。

今回はまず、

[D3]=MID(A3,1,2)

を作ります。

UIDの左から2桁までが色味を表すIDになっているので、その数字を抽出する必要があります。

抽出するとこのように、「10」が出ました。

次にINDEX関数とMATCH関数を使います。

MATCH関数の検索値にMID関数でだした「10」を使います。

あとはINDEX関数、MATCH関数の使い方と同じ。

「赤」が出ました。

同じようにE列～G列が出せます。

ほかの列も出した結果がこちらです。

[D3]=INDEX('出題2_ID参照'!B$2:B$6,MATCH(MID($A3,1,2),'出題2_ID参照'!A$2:A$6,0))

[E3]=INDEX('出題2_ID参照'!E$2:E$6,MATCH(MID($A3,3,2),'出題2_ID参照'!D$2:D$6,0))

[F3]=INDEX('出題2_ID参照'!H$2:H$6,MATCH(MID($A3,5,2),'出題2_ID参照'!G$2:G$6,0))

[G3]=MID(A3,7,4)

[D3][E3][F3]はほとんど同じ数式です。

[D3]を作ったら、あとは対照表上で参照する部分と、検索値を変えるだけですべての数式が作れます。

こうしてみると意外と簡単だってわかりますよね。

一点気を付けていただきたい部分は「$」の使いどころです。

絶対を使うべきところ、使わないところをしっかり押さえることで0から数式を作る頻度を減らします。

これができないと、本当は全部コピペで出せる数式をすべて0から書く必要が出てしまいます。

なるべく一度作った数式を別の列・行にも使えるように固定をつかいこなしていってください。

VLOOKUP関数とINDEX関数＋MATCH関数の組み合わせの違い

条件に合致した値をデータリストの中から抽出する方法にVLOOKUP関数があります。VLOOKUP関数は認知度も高く使っている人も多いと思います。

ただ私はVLOOKUP関数はほとんど使いません。デメリットがあるからです。

VLOOKUP関数のデメリットとは、数式の中で、「列番号」を指定するために起こることです。

例えばスプレッドシートを使っていくうちに、データリストで列を追加したくなることってありませんか？

日付と売り上げが並んでいるデータリストがあり、日付の隣に曜日がでるように列を追加したとします。別のシートなどで、特定の日付の売り上げを参照できるようにVLOOKUP関数でデータを作っていたとします。

すると、検索値から2番目のデータを持ってくるようにVLOOKUP関数で指定していた場合、日付列の隣が曜日の列に変わってしまうので、売り上げの値が参照できなくなってしまいます。

実際にやってみましょう。

実演

結果がでる[F]列にはそれぞれ、VLOOKUP関数とINDEX関数とMATCH関数の組み合わせの2種類で関数が入っています。

[F2]=INDEX(B2:B5,MATCH(E2,A2:A5,0))

[F3]=VLOOKUP(E3,A2:B5,2)

どちらも「100000」と出ていて間違っていないことがわかります。

この状態で、曜日の列を追加してみます。

B列に曜日を追加しました。

するとどうでしょうか。結果を示すセルで、INDEX関数＋MATCH関数の組み合わせは「100000」のままですが、VLOOKUP関数で記述したものは「火曜日」になってしまっています。

これはVLOOKUP関数の数式が、検索値がある列から数えて2番目の値が「100000」から「火曜日」になってしまったため、抽出結果が変わってしまったということです。

スプレッドシートやエクセルファイルは、情報を追加削除しながら使いやすくなるように改変していくことが多いので、もしVLOOKUP関数ですべての抽出を行ってしまうと、列の追加削除のたびに指定する列番号を変える必要が出てしまいます。

最初からINDEX関数MATCH関数でつくっておけば、列が追加削除されても、検索対象と参照範囲さえ消えなければ抽出され続けるので、とても楽です。

まとめ

• INDEX関数とMATCH関数の組み合わせの使い所
  • データベース、データリストの中から特定条件に一致するデータを抽出したいとき
  • データの列を追加削除することが多くVLOOKUP関数を使いたくないとき
• MID関数・RIGHT関数・LEFT関数の使い所
  • 文字列から一意の文字列を抽出したいとき
• 構文
  • =INDEX(抽出したい範囲,MATCH(検索値,検索範囲,照合の種類))
  • =MID(対象セル,抽出開始位置,抽出したい文字数)
• 例文
  • =INDEX(B2:B5,MATCH(E2,A2:A5,0))
  • =MID(A1,4,1)