複合参照について

参照の3兄弟、最後は 複合参照 です。

相対参照:A1
絶対参照:$A$1
複合参照:$A1 または A$1

複合参照は、「列だけ固定」「行だけ固定」という、相対参照と絶対参照の ハーフ です。九九の表のような縦横の計算 を解決してくれる参照方式ですね。

レッスン035の相対参照、レッスン036の絶対参照、そして今回の複合参照。

この3つが使えるようになると、どの関数でも対応できるようになります! それではがんばっていきましょう!

複合参照の2パターン

$A1 ── 列だけ固定

「A」の前にだけドルがついています。

列(A)は固定 → 横にコピーしても、ずっと A 列を見る
行(1)は相対 → 下にコピーすると、A2、A3 とズレる

A$1 ── 行だけ固定

「1」の前にだけドルがついています。

列(A)は相対 → 横にコピーすると、B、C とズレる
行(1)は固定 → 下にコピーしても、ずっと 1 行目を見る

$ が付いているほうが固定される、というルールはレッスン036と同じ。複合参照は、その $ を 片方だけ に付ける、というだけの話です。

どこで使うのか

これは 九九の表 で考えると、わかりやすいです。

上端(B1〜J1)に「1, 2, 3, ..., 9」、左端(A2〜A10)に「1, 2, 3, ..., 9」が入っているとします。そして、表の中(B2〜J10)に、それぞれの掛け算の答えを入れたい。

要するに、

行の見出し(左端)と
列の見出し(上端)の
掛け算 を、表の中の各セルに入れたい

ということです。

具体的に考えてみる

行見出しが A2〜A10、列見出しが B1〜J1 だとします。

B2 セルに、「A列の数字 ✕ 1行目の数字」を入れたい。普通に「=A2＊B1」と書くと、B2 には「1×1=1」が入ります。

:::excel
A1=
B1=1
C1=2
D1=3
A2=1
B2==A2B1 -> 1
C2=
D2=
A3=2
B3=
:::

一見よさそうですが、これを下や横にコピーしていくと、そうはいきません。

たとえば B2 を C2 にコピーすると、相対参照のルールで「=B2＊C1」になってしまいます。これは違いますよね。

:::excel
A1=
B1=1
C1=2
D1=3
A2=1
B2==A2*B1 -> 1
C2==B2C1 -> 2
D2=
A3=2
B3=
:::

C2 で欲しいのは「A2(左端の1)✕ C1(上端の2)」＝ 2 のはずです。

たまたま結果は合っていますが、数式は「=B2＊C1」と、見るべき場所がずれています。これでは表全体は正しく埋まりません。

ここで考えます。

A列(行見出し)は、横にコピーしても列がズレてほしくない → 列だけ固定 → $A2
1行目(列見出し)は、縦にコピーしても行がズレてほしくない → 行だけ固定 → B$1

したがって、B2 に入れる正しい数式は、

=$A2*B$1

これを、B2〜J10 にコピーすれば、9×9 の九九の表が完成します。

:::excel
A1=
B1=1
C1=2
D1=3
A2=1
B2==$A2*B$1 -> 1
C2==$A2*C$1 -> 2
D2==$A2D$1 -> 3
A3=2
B3==$A3*B$1 -> 2
C3==$A3*C$1 -> 4
D3==$A3D$1 -> 6
A4=3
B4==$A4*B$1 -> 3
C4==$A4*C$1 -> 6
D4==$A4D$1 -> 9
:::

$A2 の列固定で左端の数字を、B$1 の行固定で上端の数字を、それぞれ正しく見続けています。

1つの数式をコピーしただけで、表全体が埋まりました。これが複合参照です。

ドル記号の場所を見直す

複合参照を組むコツは、「コピーしたとき、どっち方向にズレてほしいか」を考える。

横にコピーしてもズレてほしくない部分 → 列にドル($A)
縦にコピーしてもズレてほしくない部分 → 行にドル(A$)

両方ズレてほしくないなら、両方にドル($A$1 ＝絶対参照)。

両方ズレていいなら、ドルなし(A1 ＝相対参照)。

こう並べると、3兄弟が地続きだとわかります。「どっちが固定されるべきか」を1つ1つ考えると、参照は自由自在になります。

ちょっとした実務シーン

九九は分かりやすい例ですが、「配送料金表」のような縦横クロス表でトライしてみましょう。

たとえば、

左端(A列):配送エリア(北海道、関東、九州…)
上端(1行目):荷物の重さ(〜1kg、〜5kg、〜10kg…)
表の中:エリアの「基本料金」と、重さの「割増率」を掛けた配送料金

エリアごとの基本料金がB列、重さごとの割増率が2行目に並んでいるとき、表の中のセルには、

=$B2*C$1

エリア(B列)は列固定、重さの割増率(2行目)は行固定。これを表全体にコピーすれば、全エリア × 全重量の配送料金が、たった1つの数式から一気に計算 されます。

配送料金表、価格表、相性表など、「縦の条件 × 横の条件」で値が決まる表は、実務にたくさんあります。そのすべてで、この複合参照が効いてきます。

ドル記号の循環

レッスン036でも出てきた [F4] キー。押すと参照方式が循環します。

押す回数	表示	種類
1回目	`$A$1`	絶対参照
2回目	`A$1`	行のみ固定(複合)
3回目	`$A1`	列のみ固定(複合)
4回目	`A1`	相対参照

つまり、複合参照は [F4] を2回または3回押した状態 です。手で $ を打たず、[F4] で循環させて、欲しい形で止める。

これもレッスン036と同じ操作です。

複合参照を使う頻度

正直に言うと、実務で複合参照を頻繁に使う場面は、それほど多くない です。

絶対参照と相対参照を使い分ければ、ほとんどのシーンは対応できます。

ただ、先ほどの配送料金表のような 「縦横両方向に展開する表」 を作るときには、複合参照が必須です。一度マスターしておけば、いざというときに迷わずに済みます。

セル参照は「こういう形式もある」と知っているだけで、大きな差になるはずです。

まとめ

複合参照は、「列だけ固定」または「行だけ固定」の参照
$A1:列固定、行は相対
A$1:行固定、列は相対
縦横に展開する表(九九、配送料金表、価格表など)で活躍
[F4] キーで参照方式を切り替え(2回・3回で複合参照)
「どっちにズレてほしくないか」を考えるのがコツ

参照3兄弟の話は、ここで終わりです。

以降の記事では、相対参照・絶対参照・複合参照と、当たり前のように記載していきますので、ここは時間をかけてしっかりと定着させてくださいね。

ショートカット

参照方式の切り替え:[F4](数式編集中)