エクセルの相対参照と絶対参照って使い分けが難しいですよね。
数式をコピーしてもどうしてもうまくいかない。。。
相対参照と絶対参照を理解するには簡単な題材が必要です。
今回は日本人なら誰でも理解ができる、「九九表」を作成していきながら「相対参照」と「絶対参照」について理解していきましょう。
下の「百マス計算」のような表を利用します。
日本人なら九九を理解していますよね？
フランス人は確か指で九九を計算するとか、昔古畑任三郎で知ったような記憶があります。
まずは数式を作成してそのままコピーする（相対参照）
それでは早速「九九表」を作成していきましょう。まずはB2セルにA2セルとB1セルを掛け算する数式、「=A2*B1」を入力します。
そしてB2セルの数式をB10セルまでオートフィルによるコピーをします。
すると、オートフィルによる数式のコピーがうまくいかないことが分かります。
これが「相対参照」です。エクセルは、コピーをするとそれに合わせてセルの行や列を自動で変更してくれる便利な機能を持っています。
ですから、普通に数式をコピーすると下の図のように一つずつ自動的に行をずらして計算します。
数式の参照先を固定したいときには「絶対参照」
今回の「九九表」では、掛ける数をB1セルに固定しないといけません。
その際に利用するのが「絶対参照」です。「絶対参照」では、エクセルは自動的に行をずらさずに数式をコピーします。
「絶対参照」の前には「$（ドルマーク、半角）」を入力して指定します。
それでは「絶対参照」の入力です。「$」をキーボードで入力する必要はありません。数式バーのセル参照、「B1」を選んでキーボードの「F4キー」を押すだけです。
「絶対参照」へ変更したB2セルをB10セルまでコピーすると下図のように「九九」がうまく計算されました。
各セルの数式を確認してください。すべてのセルがB1セルとの乗算（掛け算）になっていることが分かります。これが「絶対参照」です。
「絶対参照」がうまくいかないこともある
それでは続いて、B2～B10セルの数式をC列以降にコピーしてみます。
すると、下図のように「九九表」の計算はうまくいきません。
例としててF10セルの数式を確認してみます。
F10セルには本来、A10セルとF1セルの掛け算（9×5）の数式である「=A10*F1」が入力されて欲しいのですが、「=E10 *$B$1」と入力されています。
まず左側の掛ける数ですが、本来はA10セルに掛けたいのですが「相対参照」によって列がずれて「E列」となっています。左側の掛ける数の列は絶対参照によって「A列」に固定する必要があります。
また、右側の掛ける数は絶対参照が足かせになっています。こちらは列をずらして掛け算をしたいのですが、「絶対参照」によって列が固定されて「B列」のままになっています。
「絶対参照」がうまくいかない場合は「複合参照」を利用する必要がある
今回の「九九表」のように、列は固定で行はずらす、または列はずらして行を固定する必要があるときには「複合参照」を利用する必要があります。
「複合参照」とは
「複合参照」とは、「相対参照」と「絶対参照」を合わせたものです。
もう一度一から入力のやり直しをします。「1×1」の計算をするB2セルに「=$A2*B$1」と入力します。
ただし、「$」マークは入力する必要がありません。
例えば「A2」と入力した後に、キーボードの「F4」キーを押すごとに下記のように参照が変動します。
「A2（相対参照）」→「$A$2（絶対参照）」→「A$2（行のみ固定、複合参照）」→「$A2（列のみ固定、複合参照）」→「A2（相対参照、元に戻る）」
ですから左側のセル参照は「F4」キーを3回、右側のセル参照は「F4」キーを2回押すことになります。
それではB2セルの数式をB10セルまでコピーします。
下図のように、左側のセル参照は行番号だけが変更された数式がコピーされます。
続いて、A2～A10セルの数式をB列以降にコピーします。右側のセル参照は、列が相対参照（返答する）、行が絶対参照（固定される）となっています。
うまく「九九表」が稼働していることがお分かりいただけますでしょうか。
さらにJ列まで数式をコピー後が下図となります。
すべての「九九」がうまく計算ていることがお分かりいただけるでしょうか。
「相対参照」と「絶対参照」と「複合参照」は慣れるまで理解できる数式で
「相対参照」と「絶対参照」と「複合参照」は慣れるまで難しいかもしれません。
頭ですぐに合っている、間違っているかが理解できる題材でないと、直感として「相対参照」と「絶対参照」と「複合参照」を習得することはできません。
よく「相対参照」と「絶対参照」と「複合参照」の解説として売り上げレポートなどを題材にしているサイトもありますが、複雑すぎて初心者の方には理解・マスターすることは難しいです。
それに比べて「九九表」であれば、冒頭で述べたように日本人であれば直感的に理解できるのではないでしょうか。
もちろんフランス人の方にとっては指を折らなければならないので、今回の記事はおすすめできませんが（笑）。
今回の題材を利用して、「百マス計算」も自動で作成できます。
百マス計算を作成するには、重複しない乱数が必要になります。
追記：百マス計算作成記事、完成しました。
コメント