実務で使えるExcel入門セミナー 関数を使う時にこそ”絶対参照”と”相対参照”を意識しましょう

Excelを経理や財務の実務で使う際には、関数を使うことが多いです。関数の入力で気をつけたいのが、”絶対参照”と”相対参照”。最初は難しいかもしれませんが、使い分けられるようになると、関数の入力の効率が高まります。

スポンサーリンク

実務で関数をよく使うのは、コピー&ペーストで効率よくデータを処理できるから

Excelで関数を使えるようになると、その柔軟性と便利さに驚くと思いますが、実務の現場で関数を使うのには、もう一つ理由があります。

 
それは、1つのセルで入力した関数をコピー&ペーストすることで、大量のデータを処理できるから。

 
たとえば、VLOOKUP関数を使って、データベースから必要なデータを抽出することを考えます。

 

スクリーンショット 2015 09 29 11 55 21 のコピー 
(VLOOKUP関数で、右の「商品データベース」から、左の「売上明細」に”商品名”と”単価”を転記します)

 
売上明細にはD4セルからE12セルまでの18の空白セルがありますが、これに一つずつ関数を入力していくことはありません。

 
最初の1つのセルに関数を入力して、あとは空白セルにコピー&ペーストすればOK。

 
この、
 

スクリーンショット 2015 09 29 12 08 51

 
使い方ができると、大量のデータを効率よく処理することができるので、実務の現場でも関数がよく使われるのです。

 
ただ、コピー&ペーストを前提に関数を入力する際、必ずおさえておかなければいけないのが、”参照”の方法です。

 
と言うのも、最初に入力した関数で使われた「セル番地(「D4」「E12」のようなシート上のセルの場所を示す記号)」ですが、そのままコピー&ペーストしてしまうと、移動距離に応じて変化してしまいます。

 

 

※VLOOKUP関数による具体例

 
スクリーンショット 2015 09 29 12 24 53 
D4セルに、上のようなVLOOKUP関数の式を入力。最初のC4に注目して下さい)

 

スクリーンショット 2015 09 29 12 27 52 
(D4セルから隣のE4セルにコピー&ペーストした結果です。移動した距離(1つ隣に移動)に応じて、セル番号も変化してC4D4になっています。)

 

 
ですので、関数の入力に際しては、

 
・コピー&ペーストしても「動かしてはいけないセル

 
・コピー&ペーストと連動させて「動かすセル

 
を考えておかなければいけません。

 
この「動かしてはいけないセル」と「動かすセル」の区別をするのが”参照”です。

 
実務で関数を使う場合は、この”参照”を意識して使うようにしましょう。
次で、具体的な”参照”の使い方を見ていきます。

 

 

 

”行””列”に分けて、見ながら決める

ここから、具体的な参照の仕方を説明します。

 

 

”参照”の決め方

まず、最初に”参照”の方法をどのように決めるかを説明しておきます。 
難しい手順は必要なくて、

 
・””と””に分けて考える
 
・関数を見ながら決める

 
この2つに注意しながら決めていきます。

 
機械的に手順を覚えるよりも、実際の関数を見ながら決めていくのが確実で早いので、この方法をオススメしています。

 

 

”参照”の種類

次に”参照”の種類を確認します。

 
参照には2つの種類があります。

 
スクリーンショット 2015 09 29 20 24 45

 
「絶対参照」と「相対参照」です。 
こうやって用語ごとにまとめておいて言うのも何ですが、本当に押さえてもらいたいのは、赤字の部分だけ。 
つまり、

 

スクリーンショット 2015 09 29 20 35 28
 
   (強調したいので大きくしてみました)

 
これだけです。
言葉の説明だけでは分かりにくいので、実際に見ておきましょう。

 

 
A1セルに「=A3」と入力します。
 
スクリーンショット 2015 09 29 20 47 13

 
この場合、A3セルに入力されている「福山」が表示されます。 
次に、A1セルの「=A3」を、右隣のB1セルにコピー&ペーストしてみます。 
すると、

 

スクリーンショット 2015 09 29 20 50 47 

 
式の中のセルも「A3」から「B3」へ移動しました。 
これが、「コピー&ペーストしたらセルが動く」という意味です。

 
もう一つ見ておきましょう。 
今度は、A1セルの「=A3」を右斜め下の、B2セルにコピー&ペーストしてみます。

 

スクリーンショット 2015 09 29 20 56 07

 

 
すると、

 
スクリーンショット 2015 09 29 20 59 14

 
A1セルでは「=A3」だった式が、B2セルにコピー&ペーストされると、
」も「」も動いて、「=B4」になりました。

 

 
今度は、A1セルの式を「=$A3」としてB2セルにコピー&ペーストしてみます。

 
スクリーンショット 2015 09 29 21 17 53

 

すると、

 
スクリーンショット 2015 09 29 21 23 28

 
「=$A3」のAには””がついているので、コピー&ペーストしてもAのまま。
 
「=$A3」の3には””がついていないので、コピー&ペーストすると動いて4に。

 
その結果「=A4」となって、A4セルの「山本」が表示されました。

 

 
最後にもう一つだけクイズを。 
A1セルの式を「=A$3」として、B2セルにコピー&ペーストするとどうなるでしょうか。

 

 
スクリーンショット 2015 09 29 21 40 25

 
答えは、

 

 

スクリーンショット 2015 09 29 21 45 29

 
その通り。 
”$”のない「A」だけが動いて「3」はそのまま。 
B$3」になるので、「B3」セルの「吹石」が表示されます。

 

 
このように、参照は「絶対参照」「相対参照」といった言葉だけで理解するのではなく、

 
・”$”のあるなしを見る

 
・「行」と「列」に分けて見る

 
この2つを押さえることで、コピー&ペーストした後の関数の動きが分かるようになります。

 
最後に、VLOOKUP関数の入力で、参照の仕方を確認します。

 

 

 

VLOOKUP関数で参照の方法を確認

最初に出てきた「データベース」のデータを、「売上明細」の表に、VLOOKUP関数で転記することを考えます。

 

スクリーンショット 2015 09 29 11 55 21 のコピー 
(VLOOKUP関数で、右の「商品データベース」から、左の「売上明細」に”商品名”と”単価”を転記します)

 
なお、今回はVLOOKUP関数の入力がメインではないので、こちらの細かい解説は別の記事に譲ります。

 
(こちらの記事を見てみて下さい。「VLOOKUP関数の仕組み」が参考になると思います。)
 

 

 
この転記で使うVLOOKUP関数は、次のような式になります。

 

スクリーンショット 2015 09 29 22 06 18 のコピー

 
=VLOOKUP(C4,I3:O12,MATCH(D3,I3:K3,0),0)

 
こうなっています。 
表示された内容を見てみると、コード「b-003」に紐付けられている商品名「完全HORIKITA主義! ”スルー”から”愛”に変わるまで」になっていますので、

 
VLOOKUP関数の中身は正しいことがわかります。

 
ただ、このVLOOKUP関数には、全く”$がついていません。 
つまり、この関数を他の空白セルにコピー&ペーストしてしまうと、 
関数の中のセルが動いてしまうことになります。

 
これではコピー&ペーストしても、正しく転記されなくなるので、どこに”$”をつけるべきかを判断していきます。

 

 

見つけたいデータ「C4」の参照

まずは、

 
=VLOOKUP(C4,I3:O12,MATCH(D3,I3:K3,0),0)

 
最初に入力されている、「見つけたいデータ」「C4」について。

 
今回の「見つけたいデータ」は、「売上明細」の「コード」。

 
関数は、「日付」に応じて下にコピー&ペーストするのと、 
「単価」に応じて横にコピー&ペーストしますが、

 
スクリーンショット 2015 09 29 22 23 39

 

 
には、取引毎にコードを変わるので、関数のセルも動かす必要がありますが、

 
には、「コード」の列(=C列)動かしてしまうとまずいので、関数のセルは、動かしてはいけません

 
と言うことで、

 
・行:動かす

 
・列:動かさない

 
ように参照を決めるので、「$C4」になります。

 

 

調査する範囲「I3:O12」の参照

つぎに、

 
=VLOOKUP(C4,I3:O12,MATCH(D3,I3:K3,0),0)

 
「見つけたいデータ」があるかないかを、「調査する範囲」の参照です。

 
「見つけたいデータ」を探すのは、データベースですから、この範囲は、コピー&ペーストしたとしても動かすわけにはいきません。

 

スクリーンショット 2015 09 29 22 35 22

 
ですので、

 
・行:動かさない

 
・列:動かさない

 
ように参照を決めます。

 
行にも列にも”$”をつけますから、「$I$3 : $O$12」こうなります。

 

 

列番号「MATCH(D3,I3:K3,0)」の参照

最後に、列番号です。

 
こちらは、MATCH関数で入力しているのですが、これを解説すると説明が冗長になってしまいますので、答えだけ示しておきます。

 
「MATCH(D$3, $I $3 : $K$3 ,0)」

 
こうなります。

 

 

空白セルにコピー&ペースト

これまで見てきた、”参照”を考慮したVLOOKUP関数の全体を見ておくと、

 
=VLOOKUP( $C4, $I$3:$O$12, MATCH( D$3, $I$3:$K$3, 0), 0)

 
このようになりました。

 
スクリーンショット 2015 09 29 22 49 15

 
この関数を残りの空白セルにコピー&ペーストすると、

 

スクリーンショット 2015 09 29 22 55 38

 

 

 

まとめ

関数の入力時には”参照”を意識しましょう。 
関数をコピー&ペーストしたときに、「セル番地を動かすかどうか」を「行」「列」に分けて考えるのがポイントです。
 

おまけ

マイナンバーのセミナーを受けてきました。 
今まで自分で考えたことを、実務でどう生かすかについてのアイディアを学べたのが収穫。 
やはり、人から教わることも大事ですね。

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