絶対参照と相対参照を有効活用してみよう
参照先がズレないようにすることができるのが絶対参照。
式をコピペした時などに参照先も一緒に変わるのが相対参照。
というのは頭でわかっていても、どういう時に活かせるのがピンと来ない方のための、実用例の紹介です。
絶対参照と相対参照の基礎については下記の記事をご覧ください。
はじめに前提
当記事では、セル参照は相対参照（$マーク無し）が基本として考えます。
そのため、相対参照について活用するというより、相対参照だけではできないテクニックの紹介ということで進めていきます。
言い換えれば、わざわざ絶対参照にして良い事あるの？→あるんです！という記事になります。
行列とも絶対参照（$A）
まずは行列ともに固定する場合。
今回は、年齢計算をする表を例題にしましょう。
このまま、C5の式を、年齢計算したいセル全てにコピーするとどうなるかといいますと・・・
ボロボロですね。
生年月日から、指定日までの期間を計算して年齢を出したいので、常にB2セルを参照していないといけないのですが、コピペした際に一緒に参照先が動いてしまってます。
その結果、B6（1991/4/1）からB3（空欄）までの期間を計算しようとして、1991/4/1から1900/1/0（空欄の場合0として扱われるため）までの期間を計算しようとし、開始日と終了日が逆転してしまっているので、#NUM!エラーとなってます。
その下では、B7からB4の期間を計算しようとしてるのですが、1992/4/1～生年月日という文字までの期間を計算しようとして、どうやっても計算できねえよ！ということで#VALUE!エラーとなってます。
他の式も同じような理由でめちゃくちゃになってるので、エラーだらけですね。
そこで、終了日を$B$2というように絶対参照にして参照先を固定してあげることで、式が壊れなくなります。
このように、常に決められたセルを参照させたい場合、絶対参照が活きてきます。
行のみ、列のみを絶対参照（$A1、A）
絶対参照は、行または列の、どちらか片方にのみセットする事もできます。
列のみ絶対参照（$A1）
まずは列のみを絶対参照にする場合。
今回はVLOOKUPを使って、人材管理の表から、一部の人の生年月日と好きなものの情報のみを表示させる場合を例とします。
まずは単純にVLOOKUPの式を組んでみましょう。
このままでも、下方向にコピペする分にはまったく問題ありません。
ですが、問題は横方向。
B2セルをC2セルにコピーすると・・・
=VLOOKUP(B2,$E:$H,2,FALSE)
このように検索値も横にずれてしまいます。
この状況を整理すると、縦方向には変わってほしいけど、横方向には固定したいという事になります。
そんなワガママに応えてくれるのが、列だけ絶対参照なんですね。
実際にやってみましょう。
まずVLOOKUPの検索値をA2→$A2にします。
次にB2セルを、横のC2セルにコピーします。
検索値を$A2にしたので式は崩れていませんが、表示したいのは4列目の好きなものなので、列番号を2→4に手で修正します。
あとはB2～C2セルを選択、コピーしたら、B3～C5までを選択して貼り付けると完成。
列のみ絶対参照を使うことで、横方向へのVLOOKUP関数のコピーがやりやすくなりました。
ここで思い出してもらいたいのですが、途中、列番号を手で直しましたよね？
今回の例では2列分のみなので、手で列番号直しても気になりませんが、もしこれが10列分とかになってくると、面倒じゃないですか？
そこで行のみ絶対参照の出番です。
行のみ絶対参照（A）
列のみ絶対参照で使用した、人材管理の表から、指定した人の、一部項目をVLOOKUPで拾ってくるという例題を、ちょっと規模を大きくして試してみましょう。
人材管理の表に、もっと項目があって、独立したシートで管理されているとします。
そして、抽出シートで指定した人の分だけ、一部項目を表示するという表を作ります。
今回ポイントになってくるのは、列番号の箇所です。
最初に生年月日を表示させたいので、直接２と入力していますが、これをセル参照にしてしまいましょう。
まずは作業用に、先頭に1行挿入し、対応する列番号入力しておきます。
（先頭が嫌なら最終行でも、いっそ別シートで管理してもいいです）
そして列番号を、２→B$1に変えます。
あとはB3セルをコピーしたら、思い切ってB3～F6までを選択し、式を貼り付けましょう。
これで完成です。
一番右下の数式を見てみると、数式が壊れずに最後まで反映されている事がわかります。
この方法、列番号を式外に持つことになるので、どうしても作業用のスペースが必要になってしまうというデメリットはあります。
ですが、１つずつ式を直接直すよりは楽ですし、なにより、体重と身長の表示順を変えたくなった時など、手軽に変更が効くのでメンテナンスはしやすくなるなど、メリットも大きいので、ぜひお試しください。
INDIRECT、VLOOKUPの合わせ技と相性が良い
ここから少しややこしくなります。
まず、INDIRECTとVLOOKUPを組み合わせることで、検索範囲をセル値参照させることができるという手法があります。
実際どういう式になるのかというと・・・
例：=VLOOKUP(A2,INDIRECT(B1),2,FALSE)
このように、たとえば本来ならC:Zのように、検索範囲を指定する部分をINDIRECT(B1）とし、B1セルにC:Zと入力しておくという使い方になります。
そして、絶対参照となぜ相性がいいのかというと・・・言葉で伝えにくいので、例題をみてください。
まず、毎月の業績を、1カ月単位にシートを分けて記録されているとします。
そして、6月時点で千葉に所属している者の、過去半年の業績が表示されるような仕組みを作りたい場合、まず最初にこんな式を作りますよね。
あとはこれを右端までコピーして、参照先のシートを手で直して、最終行まで反映させて・・・
1回限りならいいけど毎月やれって言われたら、嫌になる作業です。
そこで、INDIRECTを使って、参照先をセル参照にし、しかも絶対参照を混ぜる事で、コピーするだけで最後まで反映される仕組みを作ってしまいましょう。
やり方は、検索先の範囲を置き換えるだけ。
'2020年1月'!$B:$C → INDIRECT("'" & C$1 & "'!$B:$C")
（この時、先頭行は書式が文字列になるようにしてください。）
これで、シート名はC1セルを参照するようになりました。
ちなみにINDIRECT使わずに、=VLOOKUP($B2,"'" & C$1 & "'!$B:$C",2,FALSE)という式にした場合、#VALUE!になってしまいます。
さらにC$1というように、行を絶対参照にすることで、あとはこれをコピーして、右下まで貼り付けるだけで作業完了となります。
慣れるまでは複雑な式でややこしいですが、使いこなせると、メンテナンスがとても簡単な仕組みが作れます。
たとえばこの例題も、来月になったら2月～7月の範囲で作り直してと言われても、先頭行の年月を変更するだけで完成します。
他にも対象者が増えても減ってもすぐ対応できるし、範囲を1年分まで広げてって言われてもすぐ対応できます。
さて、今回はもともとある式にINDIRECT関数を混ぜたので、INDIRECT("'" & C$1 & "'!$B:$C")という式にしましたが、INDIRECT関数内のダブルクオーテーションで囲まれた部分は、わざわざ絶対参照にしなくても固定になるので、INDIRECT("'" & C$1 & "'!B:C")でOKです。
これについては、また別のお話ということで。
式コピーのおともに
以上、絶対参照と相対参照は、うまく使えば１つ式を作ってあとはコピーするだけで作業を一気に片づけられる有能なお供なんだぞっていうお話でした。
みんなつけてるから合わせて$マークつけてみたとか、$マークついてても普通に機能してるから気にしてなかったとかで、存在をスルーしてしまうにはもったいない機能ですので、ぜひ体に染み込ませてください。
コメント