社内パソコン教室(仮)Excelの絶対参照と相対参照
今日もまた、事務所のどこかで問題発生。
性懲りもなく出しゃばってみる。
「どうかしたんですか?」
「下にも同じ式入れようとしてコピーしたんだけど、なんかおかしいんだよ。」
いまいち状況が呑み込めない…
事情を聞いてみる
並んだ数値(A3)の右隣り(B3)に、数値*掛け率(=A3*B2) の式を入れて、
それを下のセル(B4,B5...)にもコピーしたいらしい。
図解
何がどうなっているかよくわからないけど、これをなんとかしたいらしい。
Excelの絶対参照と相対参照
どうしてこうなったのか
数式で参照するセルを指定する方法に、「相対参照」「絶対参照」「複合参照」というものがあります。
単純に一つのセルに数式を入力する場合には、あまり違いはありませんが、他のセルに同じ数式をコピーする場合には重要になります。
大まかに説明すると…
相対参照 | 現在のセルから見て同じ位置関係にあるセルを参照する |
---|---|
絶対参照 | どこのセルからでも指定したセル番地を参照する |
複合参照 | 行・列のどちらか一方が絶対参照、もう一方が相対参照という組み合わせ |
という感じ。
初めに入力したセルの数式を見てみると
B3=A3*B2
この数式は、相対参照なので、B3から見て、左のセルの値と上のセルの値を掛けるという意味になっています。
これをこのままB4にコピーすると、
というように、B4から見て、左のセル(A4)の値と上のセル(B3)の値を掛ける数式になってしまいます。
意図したものとは違う数式になり、結果もとんでもないことに…
左のセルの値*掛け率(B2)という式にしたいので、今回はB2を絶対参照します。
絶対参照する方法
- 数式を入力する
- 絶対参照したいセルを入力したら、"F4キー"を1回押す
- 行・列番号両方の前に"$"が挿入されたことを確認して"Enter"
- "$"はキーボードから直接入力してもOK
これでB2は絶対参照されるようになりました。
下のセルにコピーしてみると、
相対参照したセルは、コピー先に合わせて変化し、絶対参照したセルはそのままになっています。
これで意図した通りの、左のセルの値 * 掛け率 の計算が出来るようになりました。
複合参照する方法
- 行が絶対、列が相対の複合参照
- 数式を入力する
- 複合参照したいセルを入力したら、"F4キー"を2回押す
- 行番号の前に"$"が挿入されたことを確認して"Enter"
- 列が絶対、行が相対の複合参照
- 数式を入力する
- 複合参照したいセルを入力したら、"F4キー"を3回押す
- 列番号の前に"$"が挿入されたことを確認して"Enter"
"F4キー"を押すたびに、
絶対参照 → 複合参照(行) → 複合参照(列) → 相対参照 → 絶対参照 …
と変化します。
複合参照をつかって出来ること
複合参照を使うと、"A列の中の自セルと同じ行のセル"とか"1行目の中の自セルと同じ列のセル"というような指定が出来ます。
わかりやすそうな例として、九九の表をつくってみます。
- 表を用意し、B2のセルに次の数式を入れます。
B2=$A2*B$1
"A列の中の自セルと同じ行のセル"と1行目の中の自セルと同じ列のセル"を掛けるという意味になります。
- これを表全体にコピーすると…
複合参照を利用することで、数式入力の手間が81分の1になりました!
実際にExcelで九九の表を作成する場面は殆どないと思いますが(笑)
使いこなして効率化!
相対参照、絶対参照、複合参照、これらを使いこなすと、表をつくるときの数式入力の手間を大幅に減らすことが出来ます。
表計算をしていると、使える場面は結構あるので、思い立ったら"F4キー"で"$マーク"!