INDEX関数とMATCH関数で月別推移データから前年同月比較表を作成する

税理士の井ノ上陽一さんが、VLOOKUP関数を使って月別推移データから、前年との同月比較を行ったり、年別推移を確認したりするためのワークシートの作り方を記事にしてらっしゃいます。

予測値、着地点を過去を比較して、このままでいいのか、もっとがんばらなければいけないのかなどがわかります。

こういった使い方をするのが本来の数字の役割です。

読者が自分で数式を考えられることを目指して、VLOOKUP関数を使った数式を作る手順が解説されていますが、同じことをINDEX関数とMATCH関数を使って行うとどうなるのか気になり、試してみました。

前年同月比較を行う表を作成する

井ノ上さんが公開してらっしゃるサンプルファイルの「前期比較」シートは、上のほうに現預金の比較（C2:D6セル）と単月P/Lの比較（C8:D19セル）、下のほうに年ごとのP/Lの累計の比較（C21:D32セル）が作成されています。

上記の操作は、現預金と単月P/Lの前年同月比較部分（C2:D6セル・C8:D19セル）を、INDEX関数とMATCH関数を使って作成する手順です。

数式の概要

INDEX関数は、何らかの表から、指定した行・列のデータを取得する関数で、引数を3つ指定します。

第1引数にはデータが存在する元の表、
第2引数には表の何行目かを表す整数、
第3引数には表の何列目かを表す整数、
をそれぞれ指定します。

上記の数式
　=INDEX(
　　　推移表!$C$2:$XFD$19,
　　　MATCH($B2, 推移表!$B$2:$B$19, 0),
　　　MATCH(C$1, 推移表!$C$1:$XFD$1, 0)
　　)
では、
第1引数に、
　　推移表!$C$2:$XFD$19
第2引数に、
　　MATCH($B2, 推移表!$B$2:$B$19, 0)
第3引数に、
　　MATCH(C$1, 推移表!$C$1:$XFD$1, 0)
を、それぞれ指定しています。

INDEX関数とMATCH関数の組み合わせを使ったことのない方は、いきなり上記の数式を見ると意味がわからないと思いますので、分解して理解することをおすすめします。

INDEX関数の動作確認を行う

サンプルファイル「前期比較」シートの、F:M列が空白になっていますので、このあたりの空白部分を利用してINDEX関数の動きを確認するのがいいでしょう。

例えば、H2セルに、
　=INDEX(
　　　推移表!$C$2:$XFD$19,
　　　1,
　　　1
　　)
という数式を入力して、第2引数と第3引数の整数を変更して、INDEX関数単体の動きを確認しましょう。

第2・第3引数を「1, 1」と指定したときは
　　推移表シートの、C2:XFD19セルの
　　1行目1列目のデータである「51635」が、

第2・第3引数を「1, 2」と指定したときは
　　推移表シートの、C2:XFD19セルの
　　1行目2列目のデータである「37651」が、
第2・第3引数を「2, 1」と指定したときは、
　　推移表シートの、C2:XFD19セルの
　　2行目1列目のデータである「1155723」が、
それぞれ表示されます。

他の値も試してみて、INDEX関数の第2・第3引数が何を指示しているものなのかを、まず理解しましょう。

MATCH関数の動作確認を行う

INDEX関数の動きが理解できたら、INDEX関数の第2・3引数で使われている、MATCH関数の確認です。

H1セルに、
　=MATCH(C$1, 推移表!$C$1:$XFD$1, 0)
という数式を入力してから、

C1セルのデータを変更してみてください。

C1セルの値を「2012/1/1」に変更したときは、MATCH関数の第2引数に指定されている、推移表シートのC1:XFD1セルの中から、

「2012/1/1」が、何番目にマッチするかを探して「1」が表示されます。

C1セルの値を「2013/1/1」に変更したときは、推移表シートのC1:XFD1セルの中から「2013/1/1」が、何番目にマッチするかを探して「13」が表示されます。

つまりMATCH関数は、第1引数に指定されたデータが、第2引数に指定された複数のデータの中で、何番目にあるのかを教えてくれるのです。（第3引数に0を指定すると完全一致するデータを探してくれます。）

このMATCH関数を使った数式「MATCH(C$1, 推移表!$C$1:$XFD$1, 0)」が、INDEX関数の第3引数に指定されています。
　=INDEX(
　　　推移表!$C$2:$XFD$19,
　　　MATCH($B2, 推移表!$B$2:$B$19, 0),
　　　MATCH(C$1, 推移表!$C$1:$XFD$1, 0)
　　)

INDEX関数の第2引数に指定されているほうのMATCH関数の動きを確認するには、G2セルに
　=MATCH(B2, 推移表!$B$2:$B$19, 0)
という数式を入力して、下方向に数行オートフィルしてみてください。

P/L（累計）の比較を行う

P/L（累計）の比較を行う表（C21:D32セル）の部分は、
　=INDEX(
　　　推移表!$C$21:$XFD$32,
　　　MATCH($B21, 推移表!$B$21:$B$32, 0),
　　　MATCH(C$1, 推移表!$C$1:$XFD$1, 0)
　　)
という数式をC21セルに入力して、

コピーすればOKです。

考え方自体は、現預金や単月P/Lの前年同月比較部分を作成するのと同じですが、参照する表が異なっています。

• INDEX関数とMATCH関数の使い方
• 複合参照とはどんなときに使うのか