実務で使えるExcel入門セミナー　【「参照」の使い分け】関数の入力で注意したい「絶対参照」「相対参照」の使い分け

「絶対参照」と「相対参照」は、Excelを使いこなす上で差が出やすいところです。実務の現場では、両方をしっかり使い分けられるようにしておく必要があります。

「絶対参照」と「相対参照」の違い

「絶対参照」と「相対参照」については、こちらの記事で詳しく説明しました。

簡単に振り返っておくと、

というもので、

「絶対参照」と「相対参照」は、「列」「行」単位で使い分けることができ、実務ではそこまで意識して使い分ける必要があると説明しました。

ここでは、実際に関数の中でどのように「絶対参照」と「相対参照」を使い分けるかを、具体的に見ていくことにします。

「絶対参照」「相対参照」を関数の中で使い分ける

「仕訳データ」の中から、「売掛金」を含む取引を転記します。
使うのは、「VLOOKUP関数」と「MATCH関数」です。

VLOOKUP関数

VLOOKUP関数は、指定したデータを検索して、該当するデータがあれば、そのセルから指定した数だけ右に移動したセルの内容を表示する関数です。

ここでは、仕訳データから「売掛金」を含むものを転記するので、

・検索するのは「売掛金」

・「売掛金」があったときにいくつ右に進んだセルを表示させるかは、
　売掛金データの”見出し”と一致するセルの数をMATCH関数で指定

このようになります。

MATCH関数

MATCH関数は、指定したデータが指定範囲の左から数えて何番目かを表示してくれる関数です。MATCH関数を使うと、「売上債権データの”見出し”」が仕訳データの中で左から数えて何番目かを表示させることができます。

このMATCH関数の機能を使って、売上債権データの見出しを検索するデータに使い、仕訳データの左から数えて何番目かを表示させることで、VLOOKUP関数の「右にいくつ進んだセルを表示させるか」を入力します。

実際に入力すると、このようになります。

＝VLOOKUP (“売掛金”, $K:$O, MATCH (C1,K1:O1,0), 0)

正しく転記することができました。

関数をコピー＆ペーストしたときのセルの移動1

売上債権のデータは、「金額（借方）」も転記する必要があるのでVLOOKUP関数を隣のセル（D2）にコピー＆ペーストします。

すると、

「金額（借方）」のデータではなく、全く関係ないデータが転記されてしまいました。
なぜ、このような誤りが起きたかというと、

コピー＆ペーストによって、MATCH関数の指定範囲が右に１つずれてしまいました。これが上手く転記できなかった原因です。これを防ぐために必要なのが、絶対参照。

MATCH関数の指定範囲については、どこにコピー＆ペーストしても移動させたくないので、「列」も「行」も絶対参照にします。

すると、

指定範囲が固定されて、正しく転記されました。

関数をコピー＆ペーストしたときのセルの移動２

仕訳データには、「売掛金」が他にもあるので、VLOOKUP関数をコピー＆ペーストして他のデータも転記します。

ところが、

元のVLOOKUP関数をコピー＆ペーストすると、MATCH関数の検索データがC1からD2に移動してしまいます。

MATCH関数の検索データは、列の見出しになるように設定しなければいけません。

言い換えると、C列にコピー＆ペーストするなら「C1」、D列にコピー＆ペーストするなら「D1」になるように入力します。

さらに、複数のデータを転記する場合は、下の行にもコピー＆ペーストすることになるので、その場合もMATCH関数の検索データが先頭行にある各列の見出しになるように入力します。

MATCH関数の検索データの入力についてまとめると、次のようになります。

これを、VLOOKUP関数の式にあてはめると、

＝ VLOOKUP (“売掛金”, $J:$N, MATCH (C$1, $J$1:$N$1, 0), 0)

MATCH関数の検索データを、「行」だけ絶対参照にすれば、他のセルにコピー＆ペーストしても正しく転記することができます。

「絶対参照」「相対参照」はExcel効率化の要

「絶対参照」「相対参照」が関数の入力においてどのように使われるかを見てきました。

Excelの作業を効率よく進めるには、入力作業を極力する少なくして、コピー＆ペーストを積極的に使うことが重要になります。具体的に言うと、関数を使う場合に、入力は1箇所だけにして、他のセルはそれをコピー＆ペーストで完成させてしまうことです。

関数をコピー＆ペーストで正しく入力するには、「絶対参照」「相対参照」の使い分けが欠かせません。最初に関数を入力する段階で、コピー＆ペーストすることを前提に「絶対参照」「相対参照」を使い分けられるかが、実務レベルでExcelが使えるかどうかの分かれ目です。

最初は入力だけで精一杯かもしれませんが、それだけでは不十分です。
早い段階で「絶対参照」「相対参照」まで意識して入力するようにしましょう。

普段から意識できていれば、意外に早くその感覚を身につけることができます。

まとめ

実務では関数の入力段階で「絶対参照」と「相対参照」の使い分けができるかどうかが、重要なポイントになります。最初は難しいかもしれませんが、普段から「絶対参照」と「相対参照」の違いを意識して入力するようにしましょう。Excelの効率化に必ずつながります。
 

おまけ

週2でサッカー観戦。何かトクした気分です。