簡単・絶対理解できる! エクセルの相対参照と絶対参照の違い 九九表を使って複合参照もマスター可能 365・2019・2016・2013・2010・2007対応

エクセルの相対参照と絶対参照って使い分けが難しいですよね。

数式をコピーしてもどうしてもうまくいかない。。。

相対参照と絶対参照を理解するには簡単な題材が必要です。

今回は日本人なら誰でも理解ができる、「九九表」を作成していきながら「相対参照」と「絶対参照」について理解していきましょう。

下の「百マス計算」のような表を利用します。

九九表をエクセルで作成しながら「相対参照」と「絶対参照」を理解する

日本人なら九九を理解していますよね?

フランス人は確か指で九九を計算するとか、昔古畑任三郎で知ったような記憶があります。

スポンサーリンク

まずは数式を作成してそのままコピーする(相対参照)

それでは早速「九九表」を作成していきましょう。まずはB2セルにA2セルとB1セルを掛け算する数式、「=A2*B1」を入力します。

「1×1」は「=A2*B1」

そしてB2セルの数式をB10セルまでオートフィルによるコピーをします。

すると、オートフィルによる数式のコピーがうまくいかないことが分かります。

オートフィルによる計算がうまくいかない

これが「相対参照」です。エクセルは、コピーをするとそれに合わせてセルの行や列を自動で変更してくれる便利な機能を持っています。

ですから、普通に数式をコピーすると下の図のように一つずつ自動的に行をずらして計算します。

エクセルの相対参照は一行ずつずらして計算してくれる
スポンサーリンク

数式の参照先を固定したいときには「絶対参照」

今回の「九九表」では、掛ける数をB1セルに固定しないといけません。

その際に利用するのが「絶対参照」です。「絶対参照」では、エクセルは自動的に行をずらさずに数式をコピーします。

「絶対参照」の前には「$(ドルマーク、半角)」を入力して指定します。

それでは「絶対参照」の入力です。「$」をキーボードで入力する必要はありません。数式バーのセル参照、「B1」を選んでキーボードの「F4キー」を押すだけです。

「相対参照」から「絶対参照」への変換はキーボードの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回押すことになります。

複合参照はキーボードのF4キーで切り替え

それではB2セルの数式をB10セルまでコピーします。

下図のように、左側のセル参照は行番号だけが変更された数式がコピーされます。

列は絶対参照、行は相対参照

続いて、A2~A10セルの数式をB列以降にコピーします。右側のセル参照は、列が相対参照(返答する)、行が絶対参照(固定される)となっています。

うまく「九九表」が稼働していることがお分かりいただけますでしょうか。

列が相対参照、行が相対参照

さらにJ列まで数式をコピー後が下図となります。

すべての「九九」がうまく計算ていることがお分かりいただけるでしょうか。

「九九表」の完成
スポンサーリンク

「相対参照」と「絶対参照」と「複合参照」は慣れるまで理解できる数式で

「相対参照」と「絶対参照」と「複合参照」は慣れるまで難しいかもしれません。

頭ですぐに合っている、間違っているかが理解できる題材でないと、直感として「相対参照」と「絶対参照」と「複合参照」を習得することはできません。

よく「相対参照」と「絶対参照」と「複合参照」の解説として売り上げレポートなどを題材にしているサイトもありますが、複雑すぎて初心者の方には理解・マスターすることは難しいです。

それに比べて「九九表」であれば、冒頭で述べたように日本人であれば直感的に理解できるのではないでしょうか。

もちろんフランス人の方にとっては指を折らなければならないので、今回の記事はおすすめできませんが(笑)。

今回の題材を利用して、「百マス計算」も自動で作成できます。

百マス計算を作成するには、重複しない乱数が必要になります。

エクセルでランダムに数字を発生させたい! 乱数を発生させるRAND関数とRANDBETWEEN関数 重複しない乱数もRANK関数で 365・2019・2016・2013・2010・2007対応
今回は、エクセルでランダムな数字を発生させる方法のご紹介です。ランダムな数字を発生させることで、リストをランダムに並び変えたり、抽選やくじ引きに利用す...

追記:百マス計算作成記事、完成しました。

エクセルで100マス計算 マクロを利用せず掛け算・足し算・引き算 桁数も変更可能 正解不正解の色表示も 365・2019・2016・2013・2010・2007対応
今回の記事ではマクロを利用せずに、エクセルで関数だけで百マス計算を自動で生成する方法のご紹介です。まずは今回ご紹介する百マス計算自動作成シートの仕様か...
スポンサーリンク
Office・Windows
スポンサーリンク
リーダーの独り言

コメント

タイトルとURLをコピーしました