実務で使える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

まとめ

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

おまけ

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

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする