今回ご紹介するのは「複合参照」という、Excelの機能なのですが、私個人の考えでは、Excel初心者さんがこの機能を覚えるのは、まだ早いかなぁ〜と思うので、Excel初心者さんにはおすすめしません。
「数式がズレるってどういうこと？」
「数式がズレないように固定するにはどうすればいいの？」
「数式に付いているドルマークって何？」
という方は、まずはそれについてを完璧にマスターすることを強く、いやかなり強くおすすめします。
今回は、下記のページをご覧いただいていることを前提として進めていきますので、まだの方は最初に下記のページをご覧ください。
そして更に、この下記のページの内容を「完璧だぜ〜♪」と確実にマスターしていただいてから、このページをご覧ください。
数式のコピーがうまくいかない
図のような表のC4番地には、「手数料」を計算するための数式が入力されています。
それがどんな数式かを確認するために、C4番地を選択した状態で数式バーを見ると、
＝ 購入額 × 手数料率
を意味する数式、
＝ B4 ＊ C3
が入力されていることが分かります。
残りのセルにも手数料を求める数式を入力したいのですが、これらの数式も
＝ 購入額 × 手数料率
の数式を入れたいことは同じなので、手っ取り早く、このC4番地の数式をコピーしたいと思います。
コピー元であるC4番地を選択すると、そのセルが太い枠で囲まれ、その右下にポッチが付いています。
このポッチにマウスポインタを合わせると、マウスポインタが図のような黒い十字の形になるので、下に向かってドラッグします。
オートフィルでの数式のコピーの操作です。
まずは下方向に、数式をコピーすることができました。
この時点で、表示されている計算結果が明らかにおかしいのですが、これが本命、後で詳しくご説明しますので、このまま強行突破で先に進みます。
オートフィルの直後なので、C4からC8番地が選択された状態になっていますが、その一番右下にポッチが付いているので、再びこのポッチにマウスポインタを合わせ、マウスポインタが図のような黒い十字の形で、今度は右に向かってドラッグします。
残りのセルにも数式をコピーすることができました。
もう、先程から気付いてはいたけれども、どうやって見ても、計算結果がおかしいです！
なぜこんな風になってしまったのか、「相対参照」と「絶対参照」をマスターしている今なら、もうお分かりですね？
次の章では、あなたの予想が合っているか、答え合わせをしながら、更に解決策へと進んでいきます！
スポンサーリンク
これが原因
数式のコピーでおかしな現象が起きてしまった時、コピーで入力された数式が、実際にどんな風になっているのかを確認するのが、解決への第一歩なのですが、コピーしたセルがこれだけあると、全てのセルを確認するのは大変です。
そこで、今回のように、数式を縦方向にも横方向にもコピーしておかしくなってしまった場合には、コピーした範囲の最初の一列分と、
最初の一行分を確認するだけで、充分原因を把握できます。
それでは、まずは最初の一列分から。
先頭のC4番地はコピー元のセルです。
このセルに入力されている数式の内容は、このページの最初で確認していますので、この次のセルから確認していきましょう。
C5番地を選択した状態で数式バーを見ると、このセルには、
＝ B5 ＊ C4
という数式が入っていることが分かります。
更にその下のセルも見てみましょう。
C6番地を選択した状態で数式バーを見ると、このセルには、
＝ B6 ＊ C5
という数式が入っていることが分かります。
こんな感じで、更にその下にあるセルでも、数式の内容を確認してみてください。
今度は、数式が入っている範囲の、最初の一行分を確認してみることにしましょう。
先頭のC4番地はコピー元で、既に数式の内容も確認していたわけですから、その次のセルから確認していきましょう。
D4番地を選択した状態で数式バーを見ると、このセルには、
＝ C4 ＊ D3
という数式が入っていることが分かります。
更にその右のセルも見てみましょう。
E4番地を選択した状態で数式バーを見ると、このセルには、
＝ D4 ＊ E3
という数式が入っていることが分かります。
今回入力したい数式は、
＝ 購入額 × 手数料率
なわけですが、その数式の「購入額」の部分に注目したのが上の図。
数式は、「相対参照」の仕組みがが働くので、下方向にコピーした時には、数式内で指定しているセル番地の、行番号の部分（数字）が1つずつズレていきます。
「4、5、6、7、8」とズレたので、元々指定したかった各「購入額」のセルを、数式内でちゃんと指定できています。
今度は、コピー元から右方向を見ていきましょう。
数式を右方向にコピーした時には、数式内で指定しているセル番地の、列番号の部分（アルファベット）が1つずつズレていきます。
「B、C、D」とズレるのは、「相対参照」の正常な動きではあるものの、「購入額」を表すセルはB列にしか無いため、「B、C、D」とズレずに、「B」のまま固定したいです。
そうすると、数式内の「購入額」の部分は、「行番号の部分（数字）はズレてほしいけど、列番号の部分（アルファベット）はズレてほしくない」ということになります。
続いて今度は、
＝ 購入額 × 手数料率
の、「手数料率」の部分に注目してみます。
数式を下方向にコピーした時には、数式内で指定しているセル番地の、行番号の部分（数字）が1つずつズレていきます。
それゆえ、「3、4、5、6、7」とズレたものの、実際の「手数料率」のパーセンテージが入力されているセルは3行目にしか無いため、「3、4、5、6、7」とズレずに、「3」のまま固定したいところ。
数式を右方向にコピーした時には、数式内で指定しているのセル番地の、列番号の部分（アルファベット）が1つずつズレていきます。
「C、D、E」とズレてくれたことにより、数式内で指定したかった各「手数料率」のセルを、ちゃんと指定できています。
というわけで、数式内の「手数料率」の部分は、「行番号の部分（数字）はズレてほしくないけど、列番号の部分（アルファベット）はズレてほしい」ということになります。
今回のように、数式をコピーした時に、行番号・列番号のどちらか片方だけしかズレてほしくない時は、そのどちらかだけを固定する「複合参照」という技を使って、数式を入力していきます。
スポンサーリンク
複合参照の数式の入力
まずは、コピー元となったC4番地の数式自体、現時点ではそれをコピーすると不具合が起きてしまう数式です。
なので、このコピー元のセルと、明らかにおかしいコピー先の数式を、すべて消していくことにしましょう。
C4からE8番地を範囲選択した状態で、［Delete］キーを押すと、
それでは気分を改めて。
コピー元となる数式を入力するところから始めていきましょう！
数式を入力したいC4番地を選択し、
数式を入れる合図の「＝」（イコール）を入力します。
入れたい数式は、
＝ 購入額 × 手数料率
なので、その「購入額」にあたるB4番地をクリックで選択します。
そしてこの、「購入額」にあたるB4番地は、
図のように「行番号の部分（数字）はズレてほしいけど、列番号の部分（アルファベット）はズレてほしくない」ので、
［F4］キーをポンッと押すと、列番号（アルファベット）の前と、行番号（数字）の前の、両方にドルマークが付きます。
ドルマークが付いた方がズレない、という意味なので、この状態では列番号と行番号の両方ともに、ズレないように指定していることになります。
［F4］キーを押せば押すほど、ドルマークが付く位置が変わっていくので、
ズレてほしくない列番号（アルファベット）の前にだけドルマークが付くよう、［F4］キーを押していきます。
これで、まずは
＝ 購入額 × 手数料率
の数式の、「購入額」の部分の指定が終わりました。
続いて、
＝ 購入額 × 手数料率
の「×（かける）」にあたる、「＊」を入力します。
更に続いて、
＝ 購入額 × 手数料率
の「手数料率」にあたる、C3番地をクリックで選択します。
この、「手数料率」にあたるC3番地は、
図のように「行番号の部分（数字）はズレてほしくないけど、列番号の部分（アルファベット）はズレてほしい」ので、
［F4］キーを押すたびに変わるドルマークの位置が、
ズレてほしくない行番号（数字）の前にだけ付くよう、［F4］キーを押していきます。
数式はこれでいいので、最後に［Enter］キーを押すか、数式バーの［入力］ボタンをクリックして、数式を確定します。
数式を入力することができました！
数式を入れたばかりのC4番地を選択した状態で数式バーを見ると、
＝ 購入額 × 手数料率
の「購入額」にあたるB4、「手数料率」にあたるC3のそれぞれで、コピーした時に列番号もしくは行番号の片方だけがズレないよう、ドルマークがズレてほしくない方にだけ付いていることが改めて確認できます。
早速、この数式をオートフィルでコピーしてみましょう！
数式を入力したC4番地を選択すると、そのセルが太い枠で囲まれ、その太い枠の右下にポッチが表示されます。
このポッチにマウスポインタを合わせると、マウスポインタが図のような黒い十字の形に変わるので、まずは下に向かってドラッグしてみます。
下方向に数式をコピーすることができました！
続けて、右方向にも数式をコピーしていきましょう。
現在は、下方向に行った数式のコピーの直後なので、C4からC8番地が選択された状態になっています。
その一番右下にポッチが付いているので、再びこのポッチにマウスポインタを合わせ、マウスポインタが図のような黒い十字の形で、右に向かってドラッグします。
右方向にも数式をコピーすることができました！
今度こそ、入力したかった正しい数式になっているか確認してみましょう！
コピー先であるC5番地を選択すると、ちゃんと
＝ 購入額 × 手数料率
である、
＝ $B5 ＊ C$3
になっていることが分かります。
数式内の「購入額」にあたるセルB5は列番号（アルファベット）だけがズレないよう、「手数料率」にあたるセルC3は行番号（数字）だけがズレないよう、列番号・行番号のそれぞれズレてほしくない方にだけドルマークが付いています。
更にその下のコピー先であるC6番地を選択してみても、
＝ $B6 ＊ C$3
となり、うまく
＝ 購入額 × 手数料率
の数式になっていることが分かります。
今度はコピー先の先頭行の数式も見てみることにしましょう。
コピー先であるD4番地を選択すると、
＝ $B4 ＊ D$3
となり、右方向にコピーした数式も、ちゃ〜んと
＝ 購入額 × 手数料率
の数式になっています！
更にその右の数式も、うまくいっていますね。
今回のように、1つの数式を縦方向にも横方向にもコピーしたい時には、数式内で指定するセル番地で、列番号・行番号のどちらか片方にだけドルマークを付けることが発生するため、かなり慣れるまではどっちにドルマークを付けるべきなのか、頭の中だけで考えるのは相当難しいと思います。
そこで、数式はどんな風にズレてしまうのか、そして列番頭・行番号のどちらを固定させたいのかを、紙に手書きしながら考えるのがおすすめです。
でも、「パソコンを使っているんだから、紙に書くようなアナログなことはカッコ悪い」
と、手書きで考えながら操作することを恥ずかしいと思ってしまう方がいらっしゃるようなのですが、それは違います！
複雑になればなるほど、紙に書いて頭の中を整理することは必須だと思います！
Officeの使いこなしテクニックをメールでお届け！