第160回日商簿記検定試験日まで

あと時間

INDEX関数とMATCH関数で別シートの値を参照【スプレッドシート】

スプレッドシート
ヒカテツ
ヒカテツ

こんにちは、ヒカテツです。

 

日商簿記検定1級合格の元経理マンで今はWEBエンジニアをしています。

 

今回は事務仕事で使えそうなGoogleスプレッドシートのINDEX関数とMATCH関数をご紹介します。

はじめに

スプレッドシートで会計データの必要な値を上手に取得する具体例を説明します。

例えば、シート1には会計ソフトから出力した試算表データを貼り付けて、その中から必要な値だけをシート2で取得して分析資料を作るような業務イメージです。

このようなときに便利なのがINDEX関数とMATCH関数です。

この2つを合わせて使うことで、より高度な値の取得が可能となり、業務負担や余計なストレスを減らすことも可能です。

INDEX関数とは

行や列を指定して値を取得できる関数です。

=INDEX(参照, 行, 列)
  • 参照 → 参照するセルの範囲を指定
  • 行 → 指定した範囲内で何行目か指定
  • 列 → 指定した範囲内で何列目か指定

具体例1

まず、同じシート内でINDEX関数を使用する具体例を説明します。

下の画像ではC15セルにINDEX関数を書いています。もし、現金の期首残高(B2)の値を取得する場合は下記のように指定して取得することができます。

  • 参照 → B2:C13
  • 行 → 1
  • 列 → 1

参照(セルの範囲)をB2からC13と指定し、その中の1行目で1列目の値を取得すると指定しています。

具体例2

もう1つ取得してみましょう。買掛金の期末残高(C5)の値を取得する場合は下記のように指定して取得することができます。

  • 参照 → B2:C13
  • 行 → 4
  • 列 → 2

参照(セルの範囲)をB2からC13と指定し、その中の4行目で2列目の値を取得すると指定しています。

具体例3

次に、別シートであるシート2にINDEX関数を書いて、シート1の買掛金期末残高(C5)の値を取得する場合です。

方法は簡単で「参照」の書き方を少し変えるだけでOKです。

  • 参照 → ‘シート1’!B2:C13
  • 行 → 4
  • 列 → 2

参照に'シート1'!という感じで参照先のシート名を追加するだけです。参照(セルの範囲)をシート1のB2からC13と指定し、その中の4行目で2列目の値を取得すると指定しています。

INDEX関数 公式ドキュメント

MATCH関数とは

指定範囲内で指定した検索キーと一致する位置を返してくれる関数です。

MATCH(検索キー, 範囲, 検索の種類)
  • 検索キー → 検索する値(7000"現金"C5 など)
  • 範囲 → 参照するセルの範囲を指定。1列か1行となるように指定する必要あり。
  • 検索の種類 → 0 は完全一致の意味。他の検索の種類についてはMATCH関数 公式ドキュメントへ。

具体例1

それでは、具体例を使って説明します。

下の画像ではC15セルにMATCH関数を書いています。

  • 検索キー → “負債及び純資産の合計”
  • 参照 → A2:A13
  • 検索の種類 → 0

検索キーを"負債及び純資産の合計"と指定し、参照(セルの範囲)はA2からA13として列を指定することで、この範囲指定した列内で完全一致する位置が何行目かを返してくれます。

具体例2

次に、別シートであるシート2にMATCH関数を書いて、参照先はシート1にする場合です。

方法は簡単で「参照」の書き方を少し変えるだけでOKです。

  • 検索キー → “買掛金”
  • 参照 → ‘シート1’!A2:A13
  • 検索の種類 → 0

参照に'シート1'!という感じで参照先のシート名を追加するだけです。検索キーを"買掛金"と指定し、参照(セルの範囲)はシート1のA2からA13として列を指定することで、この範囲指定した列内で完全一致する位置が何行目かを返してくれます。

INDEX関数とMATCH関数の合わせ技

どのように合わせて使うかというと、INDEX関数の中にMATCH関数を書きます。

おさらいですが、INDEX関数とは下記の条件で書くものでしたね。

=INDEX(参照, 行, 列)
  • 参照 → 参照するセルの範囲を指定
  • 行 → 指定した範囲内で何行目か指定
  • 列 → 指定した範囲内で何列目か指定

今回はINDEX関数の行の部分にMATCH関数を書きます。

具体例1

先ほどからの具体例の続きで、シート2にてシート1の買掛金期末残高の値を取得してみます。

INDEX関数の、行の部分に買掛金の位置を返してくれるMATCH関数を書いています。

  • 参照 → ‘シート1’!B2:C13
  • 行 → MATCH(“買掛金”, ‘シート1’!A2:A13, 0)
  • 列 → 2

行の部分にMATCH関数があるので難しく見えますが、今回のMATCH関数だとを返してくれているので結果的には=INDEX('シート1'!B2:C13, 4, 2)をしているのと同じです。

今までの説明だけでは、わざわざINDEX関数とMATCH関数の合わせ技を使うメリットがよく分かりませんよね?

それでは、もしシート1の内容に変更があった場合を考えみましょう。

左が変更前、右が変更後のシート1です。3行目に当座預金に関する新しい行が追加されています。

そして、買掛金期末残高のセルを見てみると、変更前と変更後では1行ズレてしまいました。

これだと先ほどの関数を修正する必要があると思う方もいるのではないでしょうか?

しかし、INDEX関数とMATCH関数の合わせ技を使ったことにより、シート1の内容に変更があっても自動で買掛金は何行目か探してくれるので、関数の修正をする必要なく買掛金期末残高の値を正しく取得できるのです。

このようにシート1の会計データの変更が規則的なものであれば、自動更新されるのは便利ですね!

経理関係の資料作成以外でも使える場面は多いと思いますので、是非使ってみてください!

 

Google Apps Script(GAS)というGoogleのさまざまなサービスを扱うためのプログラミング言語を学ぶことで、Googleスプレッドシートをもっと便利にすることができます。ExcelでのVBAのようにプログラムを組んでみたい方や業務効の率化を考えている方は学んでみる価値があるかもしれません♪

 

今回は以上です。

コメント

タイトルとURLをコピーしました