Introduction
Ver. all 2013 2016 2019 2021 365
Excel（エクセル）で表の縦と横のデータを一致させてデータを抜き出す方法です。
使用するのはMATCH関数とINDEX関数。２つの関数を組み合わせて使用します。また、複数条件の場合も説明します。
MATCH関数・・・範囲の中で指定した値と一致したデータを返す
=MATCH（検査値,検査範囲,照合の種類［省略可］）
- 検査値・・・検索する値（一致させたい場所のセル参照や値、文字列）を指定
- 検査範囲・・・検索する（一致させる）相手のデータ全体を指定
- 照合の種類・・・［検査値］を探す方法を指定
INDEX関数・・・指定した範囲からセルの値を取り出す。
=INDEX（配列,行番号,[列番号]）
- 配列・・・セル範囲、または配列定数を指定（範囲が１行もしくは１列の場合、それぞれ行番号、列番号を省略できる）
- 行番号・・・配列の中にあり、値を返す行を数値で指定
- 列番号・・・配列の中にあり、値を返す列を数値で指定［省略可能］
二つを組み合わせると、とても便利に使えるにゃ
MATCH関数を使用して、条件に一致した行列を探す
今回は、下の表を使用して説明していきます。
左側の「検索」表で、「商品名」と「個数」を入力すると、右側の「価格表」から、一致した値段を抜き出してくるものです。
関数の考え方としては、
- MATCH関数を使用して、商品名の「い・ろ・は」が右の表の何行目か探す
- MATCH関数を使用して、個数の「A・B・C・D」が右の表の何列目か探す
- MATCH関数で探した行と列を、INDEX関数で使用し、目的の場所を抜き出す
という感じ。
まず、最初にMATCH関数を使用します。
※関数の結果が正しいかすぐに分かるように、事前に「商品名」に「ろ」、「個数」に「C」と入力しておきます。
最終的には必要ないのですが、説明を分かりやすくするために、MATCH関数の結果を表示するための表を、下に作成しておきます。
では、用意した場所にMATCH関数を使用して、「商品名」と「個数」の場所が、右の表の何行目、何列目にあるか表示してみます。
関数を入れる「B8」を選択して、［関数の挿入］ボタンをクリック。
［関数の挿入］画面では、［すべて表示］か［検索/行列］を選んで「MATCH」関数を表示します。
MATCH関数・・・範囲の中で指定した値と一致したデータを返す
=MATCH（検査値,検査範囲,照合の種類［省略可］）
- 検査値・・・検索する値（一致させたい場所のセル参照や値、文字列）を指定
- 検査範囲・・・検索する（一致させる）相手のデータ全体を指定
- 照合の種類・・・［検査値］を探す方法を指定（省略すると「1」）
- 1・・・［検査値］以下の最大の値が検索される。［検査範囲］は昇順に並んでいる必要がある。
- 0・・・［検査値］に完全に一致した値が検索される。
- -1・・・［検査値］以上の最小の値が検索される。［検査範囲］は降順に並んでいる必要がある。
最後の［照合の種類］ですが、「0」を指定して完全一致させる場合が多いです。今回の例も「0」指定を例にします。
今回の引数は下のようになります。
- ［検査値］・・・「B4」・・・一致させたいデータがあるセルを指定
- ［検査範囲］・・・「D4：D6」・・・参照する範囲を指定
- ［照合の種類］・・・「0」・・・完全一致
=MATCH(B4,D4:D6,0)
結果は下のようになります。
「ろ」は確かに、［検査範囲］の2行目にありますね。
同じように、「列」の場所を表示してみます。
MATCH関数の引数は、今度は下のようになります。
- ［検査値］・・・「B5」・・・一致させたいデータがあるセルを指定
- ［検査範囲］・・・「F2：I2」・・・参照する範囲を指定
- ［照合の種類］・・・「0」・・・完全一致
=MATCH(B5,F2:I2,0)
結果はこちら。
「C」は確かに3列目ですね。
INDEX関数を使用して、結果を表示する
さて、行と列の場所が分かったところで、それを利用して、「価格」の場所を表示してみましょう。
「価格」を表示させたい「B6」をクリックして、［関数の挿入］ボタンをクリック。
［関数の挿入］画面では、［関数の分類］を［検索/行列］にするか、［すべて表示］に切り替えて「INDEX関数」を選びます。
INDEX関数の引数入力画面は２つから選ぶことができます。
今回は使用する機会の多い、［配列,行番号,列番号］を紹介します。
上の［配列,行番号,列番号］が選ばれている状態で［OK］をクリック。
INDEX関数・・・指定した範囲からセルの値を取り出す。
=INDEX（配列,行番号,[列番号]）
- 配列・・・セル範囲、または配列定数を指定（範囲が１行もしくは１列の場合、それぞれ行番号、列番号を省略できる）
- 行番号・・・配列の中にあり、値を返す行を数値で指定
- 列番号・・・配列の中にあり、値を返す列を数値で指定［省略可能］
入力する引数は下のようになります。
［配列］の場所には、検索場所のデータ全体を選択します。
［行番号］と［列番号］の場所には、先ほどMATCH関数で表示した、セル「B8」「B9」を選択。
=INDEX(F4:I6,B8,B9)
結果が下の画像です。
［配列］に指定した範囲「F4:I6」の2行目と3列目の交差した部分は「1000」ですね。合っています。
関数を１つにまとめる
2つの関数を、どう使うのか仕組みが分かったところで、それらを合体しましょう。
MATCH関数の結果を、わざわざ表示しなくても、そのままINDEX関数にネスト（入れ込む）してしまえばOK。
イメージは下の図です。INDEX関数の［行番号］と［列番号］の場所をMATCH関数に置き換えてしまいます。
下の「B5」に入力する関数は、
=INDEX(F4:I6,MATCH(B4,D4:D6,0),MATCH(B5,F2:I2,0))
これでOKということになります。
関数を直接手で入力しても良いのですが、分かりにくい場合は、ネスト（関数の入れ子）の入力手順を下のリンク先で説明しているのでご覧ください。
さて、これで完成と言いたいのですが、実際に使用する場合はもう一手間必要かもしれません。
下のように、「商品名」や「個数」の場所に何も入力されていないと、エラーが表示されてしまいます。
ちょっと格好悪いですね。
ということで、それらが空欄の場合は、「価格」も空欄になるようにしましょう。
IF関数やIFERROR関数を使用します。
IF関数を使用したのが下の例です。関数は長くなりますが、これで本当の完成です。
=IF(B4="","",IF(B5="","",INDEX(F4:I6,MATCH(B4,D4:D6,0),MATCH(B5,F2:I2,0))))
IF関数を２回使用するのがちょっと嫌かな。ということで、二か所のセルの判定を「OR関数」でまとめたのが下の式です。
=IF(OR(B4="",B5=""),"",INDEX(F4:I6,MATCH(B4,D4:D6,0),MATCH(B5,F2:I2,0)))
この式で、空欄時にエラーが表示されなくなります。しかし、参照する表に存在しない検索データを入力すると、やはりエラーが表示されます。
これを回避するにはIFERROR関数を使用します。
=IFERROR(INDEX(F4:I6,MATCH(B4,D4:D6,0),MATCH(B5,F2:I2,0)),"")
場合によっては、エラーが表示された方がミスが分かる場合もあるので、状況に応じて使い分けたいですね。
複数のデータを一致させる方法
最後に、複数のデータを一致させる例です。式が長くなり複雑になるので、急がずのんびり読んでください。
下の表をご覧ください。これまでとの違いは、「行」に関しては、「商品名」と「業者名」２つの名前が一致したものを取り出すというところ。
下の赤枠に関数を入力するわけですが、
このような式になります。
=INDEX(F3:I9,MATCH(B3&B4,INDEX(D3:D9&E3:E9,0),0),MATCH(B5,F2:I2,0))
式が長いのでわかりにくいですね。最初のINDEX関数の引数を、赤・紫・オレンジに色分けして、順番に考えてみましょう。
=INDEX(F3:I9,MATCH(B3&B4,INDEX(D3:D9&E3:E9,0),0),MATCH(B5,F2:I2,0))
赤文字・・・「F3:I9」・・・全体の検索範囲を指定
ここは問題ありませんね。
紫文字・・・「MATCH(B3&B4,INDEX(D3:D9&E3:E9,0),0)」・・・一致させたい「行」を指定
ここがポイントの場所です。拡大してみてみましょう。一致させたい「行」を指定するだけなのに、こんなに長くなっています。
複数のセル（文字列）を「＆」で結合して使用しています。
下の表を見ながら考えてみましょう。MATCH関数の最初の引数「B3&B4」で、「猫缶B業者B」という文字が検索する値になります。
その文字列が右の紫枠部分の何番目にあるか知りたいわけですが、そこでまたINDEX関数の登場です。
INDEX関数で「D3:D9&E3:E9」を結合した文字列を範囲にすることで、一致する「行」が何番目かを返すことができます。
最後に
オレンジ文字・・・「MATCH(B5,F2:I2,0)」・・・一致させたい「列」を指定
これは、特に問題ありませんね。普通に「列」が一致する場所を探す感じ。
どうでしょうか。関数が長くなると難しくなりますが、順を追って部分ごとに考えていくと理解しやすくなります。
さて、この方法ですが、データを結合して一致させているので、下のようにデータ入力を省略した表の場合は使用できません。
ちょっと、注意が必要です。
まとめ
INDEX関数とMATCH関数を使えるようになると、データ検索の幅が広がります。
様々な状況に対応できる関数なので、覚えておくと便利ですよ。
ちょっと話は変わりますが、今回のような検索する表を作成する場合は「入力規則」機能が便利です。
一覧からマウスで選ぶだけなので楽々です。
興味のある方は、下のリンク先をご覧ください。
【関連】
行番号と列番号に一致したセルを取り出す｜INDEX関数の使い方
Excel（エクセル）で項目の場所（位置）を調べる｜MATCH関数の使い方
なんか難しいけど覚えておいた方が良いみたいだにゃ