【Excel】機能10選④：INDEX関数とMATCH関数の合体技（INDEX/MATCH）
「行方向・列方向の両方向から検索をかけられる関数」と紹介しましたが、この特性を使うと、
「項目ごとに数値の入ったデータテーブルから、特定の項目のみを抜き出す集計フォームを作る」ことができます。
下記のような数値テーブルから、
下記のようなフォームに対して、数値を参照して当てはめることができます。
フォームのデモGIFアニメーション(▼のクリックを押してご確認ください)
数値テーブルから、「5月」であり、かつ「本契約数」「仮登録」にあたる数値を取得する、ことを行っています。
このような書き方を用いることで、
- 数値の参照箇所が複数あっても、関数をコピペして使いまわしができる
- 検索条件が変更されても、自動的に検索結果が変わるフォームにすることができる（デモ内では5月から7月に変更しています）
使い方
INDEX関数
まず、基本となるINDEX関数についてです。
₌INDEX(範囲, 行番号, 列番号, 領域番号)
という書き方で、「範囲の中の、〇行目の〇列目にあたる数値を返す」ということができる関数です。
※領域番号はあまり使いどころがないので、無視で問題ありません。実際に書かなくても機能します。
例えば、下記の画像のように書くと、「A1～D5までの表のうち、3行目の2列目のあるもの」なのでB3、という結果になります。
MATCH関数
=MATCH(検査値, 検査範囲, 照合の型)
という書き方で、「検査値（＝探したい値）が、範囲の中の、何番目にあたるかを返す」ことができる関数です。
※照合の型はいくつか種類があるのですが、基本的には完全一致を指定する「0」を書き込む、と覚えてしまって問題ありません。
例えば、下記の画像のように書くと、「A9～A13までの1列のうち、A12は何番目か」なので4、という結果になります。（上から4番目）
MATCH関数は、範囲が行でも列でも同様に使えます。
例えば、下記の画像のように書くと、「A16～D16までの1行の範囲のうち、C16は何番目か」なので3、という結果になります。（左から3番目）
INDEX関数の「行番号」「列番号」の箇所にMATCH関数を入れ込む
ここが一番重要なポイントです。
「範囲の中の、〇行目の〇列目にあたる数値を返す」ということができるINDEX関数に対して、
「〇行目」「〇列目」を返すことのできる、MATCH関数を組み込む
というのが、INDEX-MATCHです。
INDEX関数の基本の書き方： ₌INDEX(範囲, 行番号, 列番号)
MATCH関数を組み込むと： ₌INDEX(範囲,MATCH(〇, △, 0),MATCH(◎, ◇, 0))
・効率化や自動化につながるExcelの機能をもっと知りたい
・組織においてExcelを使ったデータ活用をもっと推進したい
・新しいKPIを作りたいが、自信がない
アタラにはデータ活用やExcel、BIツールのエキスパートが多数在籍しております。このような課題をお持ちの方は、アタラ合同会社のBIツール導入コンサルティングサービスへお気軽にお問い合わせください。
この記事をシェアする
まずはお気軽にご相談ください
BIシステムの導入からデータ活用の自走化まで支援いたします
画像2022/11/13 14:562022/11/15 21:06【消さないで】記事テンプレート2022/11/13 15:562022/11/13 16:41