【Excel】「複合参照($A1やA$1)」をマスター!九九の表を数式1つで作る方法

九九の表では、左の数字と上の数字を掛け合わせます。数式を右へコピーしても左端の列を使い続け、下へコピーしても上端の行を使い続けたいときに使うのが「複合参照」です。列だけを固定する $A2 と、行だけを固定する B$1 を組み合わせます。

最初の式は =$A2*B$1 です。ここではWindows版Excelで小さな表を作り、横方向、縦方向の順にコピーして仕組みを確かめます。出来上がった数字だけでなく、コピー先で式がどう変わったかまで見ていきましょう。

1. 固定するのは「$の直後にある列か行」

A2という参照は、列Aと行2の組み合わせです。$A2 はAの前に$があるので列を固定し、行番号はコピーした方向に応じて変わります。A$2 は行2を固定し、列は変わります。$を付けた側がコピー時に動かない、と読むと整理しやすくなります。

九九の左端はA列です。右へコピーしたときにB列やC列を掛けてしまわないよう、Aの前に$を付けます。一方、上端は1行目なので、下へコピーしたときに2行目や3行目を参照しないよう、行番号1の前に$を付けます。

この固定は、数式をコピーする際の参照の変化を制御するものです。セルを編集禁止にしたり、行や列の挿入・削除から守ったりする機能ではありません。固定したセルの値を書き換えれば、それを参照する計算結果も変わります。「$があるから数値は変わらない」とは考えないでください。

2. 九九の表を横・縦の順にコピーして作る

1. A2:A10に1から9を縦に入力します。B1:J1にも1から9を横に入力します。A1は空欄で構いません。
2. B2に =$A2*B$1 を入力し、結果が1になることを確認します。
3. B2の右下にあるフィルハンドルを右へドラッグし、J2までコピーします。
4. B2:J2を選択し、範囲右下のフィルハンドルを下へドラッグして10行目まで広げます。
5. C3が4、D4が9、J10が81になることを確認します。

一つのセルから斜めにドラッグして表全体を埋めようとせず、まず1行を作り、その行を下へコピーします。先に上端と左端の見出しを用意しておくと、数式のコピーで見出しまで上書きする間違いを避けやすくなります。

D4では式が =$A4*D$1 になります。A4は左端の3、D1は上端の3なので結果は9です。A列と1行目は固定され、コピー先に応じて行4と列Dだけが変わっています。数式バーでこの形になっているか確認してください。

3. F4で切り替えるときは参照部分を選ぶ

Windows版では数式を編集し、A2などの参照部分に入力位置を置くか、その参照を選択してF4を押すと参照形式が切り替わります。相対参照A2から始めた場合は、$A$2、A$2、$A2、A2の順です。すでに$がある参照では開始位置が違うので、押す回数だけで決めないようにします。

B2の式を作るなら、A2を指定したあと列だけに$がある状態にし、掛け算の相手B1は行だけに$がある状態にします。$を手入力しても構いません。画面に出ている式を読んで、目的の側に付いていることを確かめる方が重要です。

F4が音量や明るさなどの操作になるノートPCでは、Fnキーとの組み合わせやキーボード設定を確認します。数式の編集状態でないF4は別の操作になるため、いったんF2で編集に入り、対象の参照を選び直してください。確認だけで編集を終える場合はEscを使います。

4. 4種類の参照を同じ移動量で比べる

次の表は、それぞれの参照を含む式を「右へ1列」「下へ1行」コピーした場合です。同じ移動量で比較すると、どこに$を付けるべきかが見えてきます。表中のA1は説明用の参照で、九九の開始セルB2とは別です。

九九で両方を絶対参照にし、=$A$2*$B$1 とすると、どこへコピーしても同じ1×1になります。反対に =A2*B1 だけでは、右や下へのコピーで見出しから参照が外れます。片方だけを固定する理由は、計算したい行・列には追従させたいからです。

新しい表で迷ったら、まず「右へ1列コピーしたら、式のどちらが変わるべきか」を紙に書いてみます。次に下へのコピーを考えます。縦横を同時に考えるより、一方向ずつ参照先を決める方が間違いを見つけやすくなります。

5. 単価と数量の見積もり表へ応用する

九九と同じ形で、A2:A4に単価120、250、480を置き、B1:D1に数量10、20、30を置きます。B2に =$A2*B$1 を入れて右と下へコピーすると、単価ごとの金額表になります。C3なら250×20で5000、D4なら480×30で14400です。

左端が商品名の文字になっている場合は、その列を掛けても計算できません。たとえばA列が商品名、B列が単価、C1:E1が数量なら、最初の計算セルC2の式は =$B2*C$1 です。式を丸暗記して貼り付けず、実際に数値がある列と見出し行に合わせます。

値上げ率を横に並べる表なら、単純な掛け算では値上げ分だけになるので、式を =$B2*(1+C$1) のように変えます。参照を固定する考え方は同じでも、業務で求める計算は別です。元単価1000、率10%なら新価格1100になるか、小さな例で先に確かめます。

6. 右下だけでなく途中の式と境界も確認する

九九の右下J10が81でも、途中のセルが手入力で上書きされている可能性は残ります。最初のB2、横方向の端J2、縦方向の端B10、途中のD4、最後のJ10を選び、数式バーで固定する側と動く側を確認します。右下1セルが正しければ全セルも正常、とは判断できません。

全部1になるなら両側を固定し過ぎていないか、途中で0やエラーになるなら空欄や文字の見出しを参照していないかを見ます。数字が合わない場合は、$の位置だけでなく、見出しの値自体や掛け算記号も確認します。* が計算用の記号です。

連続した表を作る例では、結合セルや非表示の行・列を含まない単純な範囲で練習すると位置を追いやすくなります。実務表へ広げるときは、入力用セルや小計を含む範囲まで一括で埋めないように、コピー先の範囲を先に確認してください。

九九なら、横も縦も見出しが1から9まで増えているかを確認できます。途中の見出しが重複していると、式が正しくても同じ計算結果が並びます。数式の誤りなのか、見出しの入力誤りなのかを分けて調べると修正箇所を絞れます。

7. 間違ったコピーを戻して最初の式から直す

コピー直後に見出しや既存データを上書きしたと気付いたら、Ctrl+Zで戻します。結果の数字を手で直すと、式が残っているセルと固定値のセルが混在し、その後の変更に追従しなくなります。最初の式を修正し、コピーする範囲を確認してやり直す方が管理しやすくなります。

作業の途中で保存した場合や、元に戻す履歴がない場合は、元のブックやシートのコピーから必要な範囲を戻します。九九の練習ならB2:J10を空にして作り直せますが、業務表で範囲を消す前には、そこに手入力データがないか確認してください。

最後に見出しの一つを変更し、その行または列の結果だけが追従するか試します。確認後は見出しを元の値に戻します。列の固定、行の固定、計算の内容を別々に検証できれば、数量別の見積もりや率別の試算にも同じ考え方を使えます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】