Excel(エクセル)で表の縦横(行列)を一致させて検索(抽出)する方法|複数条件での検索も

スポンサーリンク
スポンサーリンク
スポンサーリンク

Introduction

Ver. all 2013 2016 2019 2021 365

Excel(エクセル)で表の縦と横のデータを一致させてデータを抜き出す方法です。

使用するのはMATCH関数とINDEX関数。2つの関数を組み合わせて使用します。また、複数条件の場合も説明します。

 


MATCH関数・・・範囲の中で指定した値と一致したデータを返す

=MATCH(検査値,検査範囲,照合の種類[省略可])

  • 検査値・・・検索する値(一致させたい場所のセル参照や値、文字列)を指定
  • 検査範囲・・・検索する(一致させる)相手のデータ全体を指定
  • 照合の種類・・・[検査値]を探す方法を指定

 


INDEX関数・・・指定した範囲からセルの値を取り出す。

=INDEX(配列,行番号,[列番号])

  • 配列・・・セル範囲、または配列定数を指定(範囲が1行もしくは1列の場合、それぞれ行番号、列番号を省略できる)
  • 行番号・・・配列の中にあり、値を返す行を数値で指定
  • 列番号・・・配列の中にあり、値を返す列を数値で指定[省略可能]

 

二つを組み合わせると、とても便利に使えるにゃ

スポンサーリンク

MATCH関数を使用して、条件に一致した行列を探す

今回は、下の表を使用して説明していきます。

左側の「検索」表で、「商品名」と「個数」を入力すると、右側の「価格表」から、一致した値段を抜き出してくるものです。

関数の考え方としては、

  1. MATCH関数を使用して、商品名の「い・ろ・は」が右の表の何行目か探す
  2. MATCH関数を使用して、個数の「A・B・C・D」が右の表の何列目か探す
  3. 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関数の引数入力画面は2つから選ぶことができます

今回は使用する機会の多い、[配列,行番号,列番号]を紹介します。

上の[配列,行番号,列番号]が選ばれている状態で[OK]をクリック


INDEX関数・・・指定した範囲からセルの値を取り出す。

=INDEX(配列,行番号,[列番号])

  • 配列・・・セル範囲、または配列定数を指定(範囲が1行もしくは1列の場合、それぞれ行番号、列番号を省略できる)
  • 行番号・・・配列の中にあり、値を返す行を数値で指定
  • 列番号・・・配列の中にあり、値を返す列を数値で指定[省略可能]

 

入力する引数は下のようになります。

[配列]の場所には、検索場所のデータ全体を選択します。

[行番号]と[列番号]の場所には、先ほどMATCH関数で表示した、セル「B8」「B9」を選択

=INDEX(F4:I6,B8,B9)

結果が下の画像です。

[配列]に指定した範囲「F4:I6」の2行目と3列目の交差した部分は「1000」ですね。合っています。

関数を1つにまとめる

2つの関数を、どう使うのか仕組みが分かったところで、それらを合体しましょう。

MATCH関数の結果を、わざわざ表示しなくても、そのままINDEX関数にネスト(入れ込む)してしまえばOK。

イメージは下の図です。INDEX関数の[行番号]と[列番号]の場所をMATCH関数に置き換えてしまいます。

下の「B5」に入力する関数は、

=INDEX(F4:I6,MATCH(B4,D4:D6,0),MATCH(B5,F2:I2,0))

これでOKということになります。

関数を直接手で入力しても良いのですが、分かりにくい場合は、ネスト(関数の入れ子)の入力手順を下のリンク先で説明しているのでご覧ください。

Excel(エクセル)での関数の使い方 ⑤|複数の関数を入れ子にして使う方法(ネストの使い方)
Excel(エクセル)で関数の中に関数を使う(入れ子にする)ことをネストとよびます。このネストの使い方を説明します。IF関数と組み合わせて使用することが多いですね。

 

さて、これで完成と言いたいのですが、実際に使用する場合はもう一手間必要かもしれません。

下のように、「商品名」や「個数」の場所に何も入力されていないと、エラーが表示されてしまいます。

ちょっと格好悪いですね。

ということで、それらが空欄の場合は、「価格」も空欄になるようにしましょう。

IF関数やIFERROR関数を使用します。

IF関数を使用したのが下の例です。関数は長くなりますが、これで本当の完成です。

=IF(B4="","",IF(B5="","",INDEX(F4:I6,MATCH(B4,D4:D6,0),MATCH(B5,F2:I2,0))))

IF関数を2回使用するのがちょっと嫌かな。ということで、二か所のセルの判定を「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)),"")

場合によっては、エラーが表示された方がミスが分かる場合もあるので、状況に応じて使い分けたいですね。

複数のデータを一致させる方法

最後に、複数のデータを一致させる例です。式が長くなり複雑になるので、急がずのんびり読んでください。

下の表をご覧ください。これまでとの違いは、「行」に関しては、「商品名」と「業者名」2つの名前が一致したものを取り出すというところ。

下の赤枠に関数を入力するわけですが、

このような式になります。

=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))

INDEX(配列,行番号,[列番号])

赤文字・・・「F3:I9」・・・全体の検索範囲を指定

ここは問題ありませんね。

紫文字・・・「MATCH(B3&B4,INDEX(D3:D9&E3:E9,0),0)」・・・一致させたい「行」を指定

ここがポイントの場所です。拡大してみてみましょう。一致させたい「行」を指定するだけなのに、こんなに長くなっています。

複数のセル(文字列)を「&」で結合して使用しています。

MATCH(検査値,検査範囲,照合の種類[省略可])

下の表を見ながら考えてみましょう。MATCH関数の最初の引数「B3&B4」で、「猫缶B業者B」という文字が検索する値になります。

その文字列が右の紫枠部分の何番目にあるか知りたいわけですが、そこでまたINDEX関数の登場です。

INDEX関数で「D3:D9&E3:E9」を結合した文字列を範囲にすることで、一致する「行」が何番目かを返すことができます。

最後に

オレンジ文字・・・「MATCH(B5,F2:I2,0)」・・・一致させたい「列」を指定

これは、特に問題ありませんね。普通に「列」が一致する場所を探す感じ。

 

どうでしょうか。関数が長くなると難しくなりますが、順を追って部分ごとに考えていくと理解しやすくなります。

さて、この方法ですが、データを結合して一致させているので、下のようにデータ入力を省略した表の場合は使用できません。

ちょっと、注意が必要です。

まとめ

INDEX関数とMATCH関数を使えるようになると、データ検索の幅が広がります。

様々な状況に対応できる関数なので、覚えておくと便利ですよ。

ちょっと話は変わりますが、今回のような検索する表を作成する場合は「入力規則」機能が便利です。

一覧からマウスで選ぶだけなので楽々です。

興味のある方は、下のリンク先をご覧ください。

Excel(エクセル)でリストを作成して一覧からデータ入力する方法|プルダウン、ドロップダウンリスト
Excel(エクセル)でドロップダウン、プルダウンでデータ入力する方法です。リストを作成して選択肢の一覧から入力します。

【関連】

行番号と列番号に一致したセルを取り出す|INDEX関数の使い方

Excel(エクセル)で項目の場所(位置)を調べる|MATCH関数の使い方

 

なんか難しいけど覚えておいた方が良いみたいだにゃ