定時で上がろう! Excel関数の底力 第72回 相対参照と絶対参照の応用的な使い方

Excelに慣れている中級者以上の方なら「相対参照」と「絶対参照」の違いを理解できているだろう。また、応用的な使い方として「行」または「列」だけを固定したセル参照もある。今回は復習の意味も兼ねて、セル参照の記述について紹介していこう。これまで「$」の意味をよく理解していなかった方は、この機会にぜひ学習しておくとよい。
数式や関数をコピーしたときの挙動
数式や関数をオートフィルでコピーするには、「相対参照」と「絶対参照」を正しく使い分けておく必要がある。さらに「$」の記号を上手に活用することで、通常ではコピーできない数式・関数をコピー可にできるケースもある。ということで、今回はセル参照の記述方法について詳しく解説していこう。
相対参照と絶対参照は、Excelの新機能の「スピル」に関連する部分もあるので、この機会にしっかりと復習しておくとよい。
まずは、極めて簡単な例から紹介していこう。以下の図は「単価」×「数量」を計算する数式を入力した例だ。
この数式をオートフィルでコピーすると、以降の行についても「単価」×「数量」を正しく計算できるようになる。
上図は、初心者向けの解説書にも必ず記載されている一般的な操作例といえるだろう。念のため、コピーされた数式が「どのような記述になっているか？」を確認してみよう。適当なセルをダブルクリックして数式の記述内容を確認する。
上図の例では、「=C3D3」と入力した数式が「=C6D6」に変化していることを確認できる。つまり、数式内の“セル参照”をExcelが自動補正してくれたことになる。
ただし、Excelが数式の内容を分析して、常に正しく補正してくれる訳ではない。セル参照の自動補正は、特定のルールに従って“機械的”に補正されているに過ぎない。
C3やD3のように「列番号」と「行番号」をそのまま続けて記述すると、そのセル参照は「相対参照」として扱われる。この場合、数式や関数をコピーした方向に応じて、以下のようにセル参照が自動補正される仕組みになっている。
◆下方向へコピーした場合
「行番号」が1つずつ加算されていく
◆右方向へコピーした場合
「列番号」が1つずつ加算されていく
◆上方向へコピーした場合
「行番号」が1つずつ減算されていく
◆左方向へコピーした場合
「列番号」が1つずつ減算されていく
数式や関数が正しくコピーされるように、AI（人工知能）が自動補正してくれる訳ではない。将来、そのような機能がExcelに搭載される可能性も考えられるが、現時点では“機械的”にセル参照を自動補正しているだけだ。この点を勘違いしないように注意しておくこと。
絶対参照が必要になる例
続いては、セル参照の自動補正が思い通りに機能してくれない例を紹介していこう。以下の図は、アンケート調査において「とても満足」と回答した人の「割合」を数式で求めた例だ。各項目の割合は「回答数」÷「合計」を計算すると求められる。
この数式をオートフィルでコピーすると、他の計算結果は「#DIV/0!」のエラーになってしまう。
念のため、数式が「どのようにコピーされたか？」を確認しておこう。適当なセルをダブルクリックして数式の記述内容を確認する。
上図の場合、もともと「=C3/C8」であった数式が「=C4/C9」に自動補正されている。よって「=2,347/（空白セル）」を計算することになるが、空白セルは0（ゼロ）として処理されるため、“0での割り算は実行できない”ということを示す「#DIV/0!」のエラーが発生する。
先ほど説明した「自動補正のルール」を知っていれば、これは当然の結果と言えるだろう。今回の例では“下方向”へ数式をコピーしているため、C3 → C4、C8 → C9とセル参照が自動補正される。これでは正しい「割合」を算出できない。
「割合」を正しく計算するには、分母のセル参照を常にC8に固定しておく必要がある。このように、セル参照を“固定”として扱いたいときに「絶対参照」を使用する。具体的には、列番号と行番号の前にそれぞれ「$」の記号を付けてセル参照を記述すればよい。
すると、C8（$C$8）は自動補正の対象にならず、C3だけが自動補正されていく。この数式をオートフィルでコピーすると、それぞれの「割合」が正しく算出されるのを確認できるだろう。
適当なセルをダブルクリックして「コピーされた数式」の記述を見てみると、分子の部分はC4、C5、C6、……と変化していくのに対して、分母はC8（$C$8）のまま変化していないことを確認できる。
このように、常に“固定”しておきたいセルは「絶対参照」で指定するのが基本だ。そうではなく、コピーする方向に応じて自動補正したいセルは、普通に「相対参照」で記述すればよい。
これは関数にセル範囲などを指定する場合も同様だ。以下の図は、「氏名」を条件にして「合計金額」を算出する関数SUMIFの使用例だ。
この場合、C2:C27（検索する範囲）とD2:D27（合計するセル範囲）は常に固定しておきたいので「絶対参照」で指定している。一方、F2（検索条件）は、関数をコピーした際に自動補正してもらいたいので、普通に「相対参照」で指定している。
この関数SUMIFをオートフィルでコピーした後、「どのように関数がコピーされているか？」を確認してみよう。適当なセルをダブルクリックする。
C2:C27（検索する範囲）とD2:D27（合計するセル範囲）の部分は変化しておらず、F2（検索条件）の部分だけが自動補正されていることを確認できるだろう。
なお、数式や関数をコピーする必要がないときは、どちらの方法でセル参照を記述しても結果は同じになる。よって、記述が簡単な「相対参照」を使用すればよい。
また、Excelの新機能「スピル」を使用するときも「相対参照」のままで構わない。というのも、スピルを使用する場合は、数式や関数をコピーする作業そのものが不要になるからだ。これについては次回の連載で詳しく説明していこう。
絶対参照を応用した「累計」の計算
ここからは「絶対参照」を上手に活用した応用例を紹介していこう。最初に紹介するのは「累計」の計算だ。これまでに支払った金額の「累計」を計算する際に、以下の図のように数式を入力している方も多いだろう。
この数式の意味は、「直前の累計金額」に「今回の使用金額」を足し算する、となる。もちろん、この方法でも正しい「累計」を算出することが可能だ。
これと同じ計算結果を関数SUMで得ることもできる。具体的には、以下の図のように関数SUMを入力すればよい。
あとは、この関数SUMをオートフィルでコピーするだけ。これで各時点の「累計」を正しく算出できる。念のため、その理屈を説明しておこう。この場合、「コピーされた関数SUM」の記述は以下の図のようになる。
・始点セル ………… E3に固定（絶対参照）
・終点セル ………… E3を基準に自動補正（相対参照）
今回は下方向へ関数SUMをコピーしているため、「終点セル」だけがE4、E5、E6、E7、……と変化していく。つまり、合計するセル範囲がE3:E4、E3:E5、E3:E6、E3:E7、……と変化していくことになる。その結果、「これまでに使用した金額の合計」を正しく算出できる、という仕組みだ。
絶対参照を手軽に入力するには？
絶対参照を使用する際に、「何回も$を入力するのが面倒くさい」と感じている方もいるだろう。このような場合はショートカットキーを使ってセル参照を変換するとよい。
まずは、普通に「相対参照」でセル参照を入力する。続いて「F4」キーを押すと、そのセル参照が「絶対参照」に変換される。そのつど「$」の記号を入力しなくても、手軽に「絶対参照」を指定できる。
さらに「F4」キーを押していくと、セル参照の記述が「E3」→「$E$3」→「E$3」→「$E3」→「E3」→ ……という具合に循環していくのを確認できる。
このように「F4」キーでセル参照の記述をコントロールしていくことも可能だ。便利な機能なので、この機会に使い方を覚えておこう。
行または列だけを固定する絶対参照
先ほど示した「E$3」や「$E3」のように、「$」を1つだけ記述する使い方もある。具体的な例を使って解説していこう。
ここでは「Tシャツの作製計画」を練る場合を例に、セル参照の応用的な使い方を紹介する。Tシャツの色は白／黒／赤／青の4種類、サイズはS／M／Lの3種類を用意し、それぞれ以下の比率で合計12,000着のTシャツを作製したいとする。
◆色の比率
白：黒：赤：青 ＝ 35％：30％：20％：15％
◆サイズの比率
S：M：L ＝ 25％：50％：25％
この場合、各Tシャツをそれぞれ何着ずつ作製すればよいだろうか？　Excelで計算してみよう。それぞれの「比率」を記した表を作成し、「合計の作製数」を適当なセルに入力する。
続いて、各Tシャツの製作数を計算していく。まずは「白色、Sサイズ」のTシャツについて。この場合、
・白色は全体の35％
・Sサイズは全体の25％
という比率になるので、「合計の作製数」×「白の比率」×「Sの比率」を計算すると、「白色、Sサイズ」の製作数を求められる。これを数式で示すと「=D2D5C6」となる。「合計の作製数」が自動補正されないように、D2セルは絶対参照で指定した。
この数式を下方向へコピーすると、以下の図のような結果になる。「色の比率」がD5 → D6に自動補正されるため、このままでは正しい計算結果を得られない。正しく計算するには、「色の比率」をD5セルに“固定”しておく必要がある。その一方で、「サイズの比率」を示すC6 → C7の自動補正は正しく機能している。
今度は、先ほどの数式を右方向へコピーしてみよう。この場合、「色の比率」を示すD5 → E5の自動補正は正しく機能している。その一方で、「サイズの比率」がC6 → D6セルに自動補正されるため、このままでは正しい計算結果を得られない。正しく計算するには、「サイズの比率」をC6セルに“固定”しておく必要がある。
つまり、コピーする方向に応じて「固定すべきセル」と「自動補正すべきセル」が矛盾してしまう訳だ。
このような場合に「$」を1つだけ記述する使い方が活用できる。「$」を1つだけ記述すると、「行」または「列」だけを固定できるようになる。たとえば、セル参照を「D$5」のように記述すると、「5行目」だけが固定され、「列」は自動補正の対象になる。逆に、セル参照を「$C6」のように記述すると、「C列目」だけが固定され、「行」は自動補正の対象になる。
この仕組みを利用して、以下の図のように数式を入力する。
すると、
・色の比率 …………… 「5行目」に固定、列は自動補正
・サイズの比率 ……… 「C列目」に固定、行は自動補正
という動作を実現できるようなる。あとは、この数式を「下方向」と「右方向」へオートフィルでコピーするだけ。これで各Tシャツの製作数を正しく計算することが可能となる。
試しに「コピーされた数式」の記述を見てみると、「色の比率」と「サイズの比率」が正しく指定されていることを確認できるだろう。念のため、それぞれの合計を算出した表を掲載しておこう。
このように「行」または「列」だけを固定するセル参照を利用することで、通常ではコピーできない数式をコピー可にできるケースもある。
ちょっと頭が混乱しそうな問題なので、「少しくらい時間がかかっても、すべての数式を手入力した方が簡単！」と感じる方もいるだろう。計算結果が正しいのであれば、それも一つの手だ。理屈がよくわからないまま利用するよりは確実かもしれない。
単純な「絶対参照」に比べて「どのような挙動になるか？」を予測しにくいため、このテクニックを活用するか否かは、各自の好みで判断するとよいだろう。状況によっては便利なテクニックになるが、スムーズに使いこなすには“ある程度の慣れ”が必要かもしれない。