ブログ管理人のtetsuです。
簡単に自己紹介ですが、日商簿記検定１級合格の元経理マンで今はWEBエンジニアをしています。
今回は事務仕事で使えそうなGoogleスプレッドシートのINDEX関数とMATCH関数をご紹介します。
はじめに
スプレッドシートの別シートにある必要な値を上手に取得するテクニックです。
今回は具体例として会計データを使います。
例えば、シート１には会計ソフトから出力した試算表データを貼り付けて、その中から必要な値だけをシート２で取得して会計資料を作るような業務イメージです。
このようなときに便利なのがINDEX関数とMATCH関数です。
この２つを合わせて使うことで、より高度な値の取得が可能となり、業務負担や余計なストレスを減らすことが可能です。
それでは、やってみましょう。
INDEX関数とは
行や列を指定して値を取得できる関数です。
=INDEX(参照, 行, 列)- 参照 → 参照するセルの範囲を指定
- 行 → 指定した範囲内で何行目か指定
- 列 → 指定した範囲内で何列目か指定
具体例１
まず、同じシート内でINDEX関数を使用する具体例を説明します。
下の画像ではC15セルにINDEX関数を書いています。もし、現金の期首残高(B2)の値を取得する場合は下記のように指定して取得することができます。
- 参照 → B2:C13
- 行 → 1
- 列 → 1
参照(セルの範囲)をB2からC13と指定し、その中の1行目で1列目の値を取得すると指定しています。
具体例２
もう１つ取得してみましょう。買掛金の期末残高(C5)の値を取得する場合は下記のように指定して取得することができます。
- 参照 → B2:C13
- 行 → 4
- 列 → 2
参照(セルの範囲)をB2からC13と指定し、その中の4行目で2列目の値を取得すると指定しています。
具体例３
次に、別シートであるシート２にINDEX関数を書いて、シート１の買掛金期末残高(C5)の値を取得する場合です。
方法は簡単で「参照」の書き方を少し変えるだけでOKです。
- 参照 → ‘シート1’!B2:C13
- 行 → 4
- 列 → 2
参照に'シート1'!という感じで参照先のシート名を追加するだけです。参照(セルの範囲)をシート１のB2からC13と指定し、その中の4行目で2列目の値を取得すると指定しています。
MATCH関数とは
指定範囲内で指定した検索キーと一致する位置を返してくれる関数です。
MATCH(検索キー, 範囲, 検索の種類)- 検索キー → 検索する値（
7000、"現金"、C5など） - 範囲 → 参照するセルの範囲を指定。１列か１行となるように指定する必要あり。
- 検索の種類 → 0 は完全一致の意味。他の検索の種類についてはMATCH関数 公式ドキュメントへ。
具体例１
それでは、具体例を使って説明します。
下の画像ではC15セルにMATCH関数を書いています。
- 検索キー → “負債及び純資産の合計”
- 参照 → A2:A13
- 検索の種類 → 0
検索キーを"負債及び純資産の合計"と指定し、参照(セルの範囲)はA2からA13として列を指定することで、この範囲指定した列内で完全一致する位置が何行目かを返してくれます。
具体例２
次に、別シートであるシート２にMATCH関数を書いて、参照先はシート１にする場合です。
方法は簡単で「参照」の書き方を少し変えるだけでOKです。
- 検索キー → “買掛金”
- 参照 → ‘シート1’!A2:A13
- 検索の種類 → 0
参照に'シート1'!という感じで参照先のシート名を追加するだけです。検索キーを"買掛金"と指定し、参照(セルの範囲)はシート１のA2からA13として列を指定することで、この範囲指定した列内で完全一致する位置が何行目かを返してくれます。
INDEX関数とMATCH関数の合わせ技
どのように合わせて使うかというと、INDEX関数の中にMATCH関数を書きます。
おさらいですが、INDEX関数とは下記の条件で書くものでしたね。
=INDEX(参照, 行, 列)- 参照 → 参照するセルの範囲を指定
- 行 → 指定した範囲内で何行目か指定
- 列 → 指定した範囲内で何列目か指定
今回はINDEX関数の行の部分にMATCH関数を書きます。
具体例１
先ほどからの具体例の続きで、シート２にてシート１の買掛金期末残高の値を取得してみます。
INDEX関数の、行の部分に買掛金の位置を返してくれるMATCH関数を書いています。
- 参照 → ‘シート1’!B2:C13
- 行 → MATCH(“買掛金”, ‘シート1’!A2:A13, 0)
- 列 → 2
行の部分にMATCH関数があるので難しく見えますが、今回のMATCH関数だと４を返してくれるので結果的に=INDEX('シート1'!B2:C13, 4, 2)をしているのと同じです。
今までの説明だけでは、わざわざINDEX関数とMATCH関数の合わせ技を使うメリットがよく分かりませんよね？
それでは、もしシート１の内容に変更があった場合を考えみましょう。
左が変更前、右が変更後のシート１です。３行目に当座預金に関する新しい行が追加されています。
そして、買掛金期末残高のセルを見てみると、変更前と変更後では１行ズレてしまいました。
これだと先ほどのシート2の関数を修正する必要があると思う方もいるのではないでしょうか？
しかし、INDEX関数とMATCH関数の合わせ技を使ったことにより、シート１の内容に変更があっても自動で買掛金は何行目か探してくれるので、関数の修正をする必要なく買掛金期末残高の値を正しく取得できているのです。
INDEX関数の行の部分にMATCH関数があるので分かりにくいと思いますが、変更後のMATCH関数だと5を返してくれるので結果的に=INDEX('シート1'!B2:C13, 5, 2)をするように自動で変わったということです。
今回の会計データ(シート１)のように規則的な変更であれば、INDEX関数とMATCH関数の合わせ技で自動更新されるので便利ですよね！
会計データ以外でも使える場面は多いと思いますので、是非使ってみてください！
【もっと学びたい方へ】
GoogleAppsScript(GAS)というGoogleの様々なサービスを扱うためのプログラミング言語を学ぶことで、Googleスプレッドシートをもっと便利に活用することができます♪
オンラインスクールで学びたい人は、テックアカデミーという大手プログラミングスクールのGoogleAppsScriptコースがおすすめです。
まずは無料体験にチャレンジしてみることで、新しい世界が広がるかもしれません。
無料体験はこちら最後に
この記事を読む人は事務スキルが高い人だと思います。
簿記３級やそれ以上の資格やスキルに興味がある人向けに書いた記事もありますので、是非読んでみてください。
＞＞簿記３級とは？勉強方法は？独学できる？【おすすめ教材や始め方を知りたい方は見ないと損】
＞＞簿記３級受験後のおすすめ進路【相性が良い資格と将来性があるスキルを紹介】
今回は以上です。
コメント