表で使う複合参照

絶対参照は列と行の両方をロックします。しかし、場合によっては一方だけをロックしたいことがあります。それが複合参照です。

複合参照は、1つの数式から表全体を埋めるための鍵です。参照を一方向には移動させながら、もう一方向には固定できるためです。

複合参照には、次の2種類しかありません。

• $A1：列がロックされ、行は自由に移動します
• A$1：行がロックされ、列は自由に移動します

ルールを覚えておきましょう。ドル記号は、その直後の要素をロックします。

$A1は常にA列にとどまりますが、行は変わることができます。下方向にコピーすると$A2、$A3となります。右方向にコピーしても、列がロックされているため$A1のままです。

これは、表のすべての列で同じ入力列を参照する場合に使用します。たとえば、左側に並んだラベルや基準値を参照する場合です。

A$1は常に1行目にとどまりますが、列は変わることができます。右方向にコピーするとB$1、C$1となります。下方向にコピーしても、行がロックされているためA$1のままです。

これは、表のすべての行で、上部に横並びになった同じ見出し行を参照する場合に使用します。

表は、上部に横並びになった見出しの行と、側面に縦並びになったラベルの列という、2つの方向の情報を同時に使います。内部の各セルは、それぞれから1つずつ組み合わせます。

1つの数式で表全体を埋めるには、行の参照元では行をロックし、列の参照元では列をロックする必要があります。これを実現するのが複合参照です。

売上金額がA列（A2、A3、…）に下方向に並び、コミッション率が1行目（B1、C1、…）に横方向に並んでいるとします。表を埋めるには、B2に次の式を入力します。

=$A2*B$1

$A2は常に売上の列を参照し、B$1は常に率の行を参照します。

=$A2*B$1をB2からB3へ下方向にコピーします。

• $A2は$A3になります（行は自由に移動し、下へ進みます）
• B$1はB$1のままです（行がロックされています）

したがって、B3は=$A3*B$1となります。次の売上金額に、最初のレートを掛けています。これで正しい動きです。

次に、=$A2*B$1をB2からC2へ右方向にコピーします。

• $A2は$A2のままです（列がロックされています）
• B$1はC$1になります（列は自由に移動し、右へ進みます）

したがって、C2は=$A2*C$1となります。同じ売上金額に、次のレートを掛けています。1つの数式が両方向に適応します。

ドル記号をどこに付けるか決めるには、表を埋めるときに参照をどの方向に固定したいかを考えます。

• 1つの列にとどめたい場合は、列をロックします：$A2
• 1つの行にとどめたい場合は、行をロックします：B$1

移動させたくない部分をロックし、移動させたい部分はそのままにします。

複合参照は計算だけに使うものではありません。都市をA列に下方向に並べ、同じ都市を1行目に横方向に並べて、距離表を作成できます。内部の各セルでは、行の都市と列の都市の間の距離を検索します。

検索の数式では、行の都市に$A2、列の都市にB$1を使います。そのため、九九の表と同じように、1つの数式で表全体を埋められます。

F4を繰り返し押すと、4種類の形式が順番に切り替わるため、どちらの複合参照にも設定できます。

• 1回目：$A$1
• 2回目：A$1（行をロック）
• 3回目：$A1（列をロック）
• 4回目：A1

必要な部分がロックされる回数で止めてください。

複合参照を理解できているか確認しましょう。

複合参照は、一方の部分をロックし、もう一方を自由にします。

• $A1は列をロックし、行が移動します
• A$1は行をロックし、列が移動します

これにより、見出し行とラベル列から値を取得する表全体を、1つの数式で埋められます。次は、1つの数式から九九の表全体を作成して、この知識を実際に使ってみます。