Home » Excel(エクセル)の関数・数式の使い方 » 検索・行列 » INDEX関数とMATCH関数で月別推移データから前年同月比較表を作成する

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

対象:Excel2007, Excel2010, Excel2013, Windows版Excel2016

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

予測値、着地点を過去を比較して、このままでいいのか、もっとがんばらなければいけないのかなどがわかります。
こういった使い方をするのが本来の数字の役割です。
と、経理というのは、ただ単に過去を記録して、税金を納めるためにだけにやっているわけではなく、未来のためのものでもあることを伝えている記事です。

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

[スポンサードリンク]

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

配布されているサンプルファイル「EX-ITサンプル VLOOKUP前期比較.xlsx」の、「前期比較」シートと同じ表を、INDEX関数とMATCH関数を使って作成する場合、以下のような手順です。


▼操作概要:月別の推移表データから前年同月比較を行う表を作成する
C2セルに、
 =INDEX(
   推移表!$C$2:$XFD$19,
   MATCH($B2, 推移表!$B$2:$B$19, 0),
   MATCH(C$1, 推移表!$C$1:$XFD$1, 0)
  )
という数式を入力
 ↓
C2セルをD2セルにコピー
 ↓
C2:D2セルをC19:D19セルまでコピー

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関数単体の動きを確認しましょう。

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

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

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

第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)
という数式を入力してから、

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

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

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

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

「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の前年同月比較部分を作成するのと同じですが、参照する表が異なっています。

[スポンサードリンク]

Home » Excel(エクセル)の関数・数式の使い方 » 検索・行列 » INDEX関数とMATCH関数で月別推移データから前年同月比較表を作成する

TrackBack:0

TrackBack URL

Home » Excel(エクセル)の関数・数式の使い方 » 検索・行列 » INDEX関数とMATCH関数で月別推移データから前年同月比較表を作成する

「検索・行列」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.