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

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

 

”さらっ”とまとめると

・関数における「絶対参照」と「相対参照」の使い分けで、Excelの作業スピードが大きく変わる

 

スポンサーリンク

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

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

 

 

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

 

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

・セルをコピー&ペーストした時に、参照セルが移動するかどうか


   絶対参照:参照セルが移動しない

   相対参照:参照セルが移動する

 

というもので、

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

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

 

 

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

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

 

スクリーンショット 2017 05 03 12 15 03

 

VLOOKUP関数

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

 

スクリーンショット 2017 05 03 10 55 54

スクリーンショット 2017 05 03 10 55 40

 

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

 

・検索するのは「売掛金

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

 

このようになります。

 

MATCH関数

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

 

スクリーンショット 2017 05 03 11 42 56

 

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

 

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

 

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

 

スクリーンショット 2017 05 03 12 03 51

 

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

 

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

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

すると、

 

スクリーンショット 2017 05 03 12 22 40

 

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

 

スクリーンショット 2017 05 03 12 52 27

 

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

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

すると、

 

スクリーンショット 2017 05 03 12 54 36

 

スクリーンショット 2017 05 03 12 58 48

 

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

 

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

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

ところが、

 

スクリーンショット 2017 05 03 13 13 15

 

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

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

 

スクリーンショット 2017 05 03 13 35 09

 

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

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

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

 

MATCH関数の検索データの入力

・「」は、コピー&ペーストで移動

・「」は、コピー&ペーストしても1行目で固定

 

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

 

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

 

スクリーンショット 2017 05 03 13 50 36

 

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

スクリーンショット 2017 05 03 13 56 50

 

 

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

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

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

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

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

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

 

 

まとめ

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

おまけ

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

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