Excelを経理や財務の実務で使う際には、関数を使うことが多いです。関数の入力で気をつけたいのが、”絶対参照”と”相対参照”。最初は難しいかもしれませんが、使い分けられるようになると、関数の入力の効率が高まります。
実務で関数をよく使うのは、コピー＆ペーストで効率よくデータを処理できるから
Excelで関数を使えるようになると、その柔軟性と便利さに驚くと思いますが、実務の現場で関数を使うのには、もう一つ理由があります。
それは、１つのセルで入力した関数をコピー＆ペーストすることで、大量のデータを処理できるから。
たとえば、VLOOKUP関数を使って、データベースから必要なデータを抽出することを考えます。
（VLOOKUP関数で、右の「商品データベース」から、左の「売上明細」に”商品名”と”単価”を転記します）
売上明細にはD4セルからE12セルまでの18の空白セルがありますが、これに一つずつ関数を入力していくことはありません。
最初の１つのセルに関数を入力して、あとは空白セルにコピー＆ペーストすればOK。
この、
使い方ができると、大量のデータを効率よく処理することができるので、実務の現場でも関数がよく使われるのです。
ただ、コピー＆ペーストを前提に関数を入力する際、必ずおさえておかなければいけないのが、”参照”の方法です。
と言うのも、最初に入力した関数で使われた「セル番地（「D4」「E12」のようなシート上のセルの場所を示す記号）」ですが、そのままコピー＆ペーストしてしまうと、移動距離に応じて変化してしまいます。
※VLOOKUP関数による具体例
（D4セルに、上のようなVLOOKUP関数の式を入力。最初のC4に注目して下さい）
（D4セルから隣のE4セルにコピー＆ペーストした結果です。移動した距離（１つ隣に移動）に応じて、セル番号も変化してC4がD4になっています。）
ですので、関数の入力に際しては、
・コピー＆ペーストしても「動かしてはいけないセル」
・コピー＆ペーストと連動させて「動かすセル」
を考えておかなければいけません。
この「動かしてはいけないセル」と「動かすセル」の区別をするのが”参照”です。
実務で関数を使う場合は、この”参照”を意識して使うようにしましょう。
次で、具体的な”参照”の使い方を見ていきます。
”行””列”に分けて、見ながら決める
ここから、具体的な参照の仕方を説明します。
”参照”の決め方
まず、最初に”参照”の方法をどのように決めるかを説明しておきます。
難しい手順は必要なくて、
・”行”と”列”に分けて考える
・関数を見ながら決める
この２つに注意しながら決めていきます。
機械的に手順を覚えるよりも、実際の関数を見ながら決めていくのが確実で早いので、この方法をオススメしています。
”参照”の種類
次に”参照”の種類を確認します。
参照には２つの種類があります。
「絶対参照」と「相対参照」です。
こうやって用語ごとにまとめておいて言うのも何ですが、本当に押さえてもらいたいのは、赤字の部分だけ。
つまり、
（強調したいので大きくしてみました）
これだけです。
言葉の説明だけでは分かりにくいので、実際に見ておきましょう。
A1セルに「=A3」と入力します。
この場合、A3セルに入力されている「福山」が表示されます。
次に、A1セルの「=A3」を、右隣のB1セルにコピー＆ペーストしてみます。
すると、
式の中のセルも「A3」から「B3」へ移動しました。
これが、「コピー＆ペーストしたらセルが動く」という意味です。
もう一つ見ておきましょう。
今度は、A1セルの「=A3」を右斜め下の、B2セルにコピー＆ペーストしてみます。
すると、
A1セルでは「＝A3」だった式が、B2セルにコピー＆ペーストされると、
「行」も「列」も動いて、「＝B4」になりました。
今度は、A1セルの式を「＝$A3」としてB2セルにコピー＆ペーストしてみます。
すると、
「＝$A3」のAには”＄”がついているので、コピー＆ペーストしてもAのまま。
「＝$A3」の3には”＄”がついていないので、コピー＆ペーストすると動いて4に。
その結果「＝A4」となって、A4セルの「山本」が表示されました。
最後にもう一つだけクイズを。
A1セルの式を「＝A$3」として、B2セルにコピー＆ペーストするとどうなるでしょうか。
答えは、
その通り。
”$”のない「A」だけが動いて「3」はそのまま。
「B$3」になるので、「B3」セルの「吹石」が表示されます。
このように、参照は「絶対参照」「相対参照」といった言葉だけで理解するのではなく、
・”$”のあるなしを見る
・「行」と「列」に分けて見る
この２つを押さえることで、コピー＆ペーストした後の関数の動きが分かるようになります。
最後に、VLOOKUP関数の入力で、参照の仕方を確認します。
VLOOKUP関数で参照の方法を確認
最初に出てきた「データベース」のデータを、「売上明細」の表に、VLOOKUP関数で転記することを考えます。
（VLOOKUP関数で、右の「商品データベース」から、左の「売上明細」に”商品名”と”単価”を転記します）
なお、今回はVLOOKUP関数の入力がメインではないので、こちらの細かい解説は別の記事に譲ります。
（こちらの記事を見てみて下さい。「VLOOKUP関数の仕組み」が参考になると思います。）
http://hatenablog.com/embed?url=https://yskzt.com/we-should-use-MS-Excel-with-multi-functions20150115
この転記で使うVLOOKUP関数は、次のような式になります。
=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」について。
今回の「見つけたいデータ」は、「売上明細」の「コード」。
関数は、「日付」に応じて下にコピー＆ペーストするのと、
「単価」に応じて横にコピー＆ペーストしますが、
縦には、取引毎にコードを変わるので、関数のセルも動かす必要がありますが、
横には、「コード」の列（＝C列）を動かしてしまうとまずいので、関数のセルは、動かしてはいけません。
と言うことで、
・行：動かす
・列：動かさない
ように参照を決めるので、「$C4」になります。
調査する範囲「I3:O12」の参照
つぎに、
=VLOOKUP(C4,I3:O12,MATCH(D3,I3:K3,0),0)
「見つけたいデータ」があるかないかを、「調査する範囲」の参照です。
「見つけたいデータ」を探すのは、データベースですから、この範囲は、コピー＆ペーストしたとしても動かすわけにはいきません。
ですので、
・行：動かさない
・列：動かさない
ように参照を決めます。
行にも列にも”＄”をつけますから、「$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)
このようになりました。
この関数を残りの空白セルにコピー＆ペーストすると、
まとめ
関数の入力時には”参照”を意識しましょう。
関数をコピー＆ペーストしたときに、「セル番地を動かすかどうか」を「行」「列」に分けて考えるのがポイントです。
おまけ
マイナンバーのセミナーを受けてきました。
今まで自分で考えたことを、実務でどう生かすかについてのアイディアを学べたのが収穫。
やはり、人から教わることも大事ですね。