Excel 絶対参照・相対参照 SUMIF関数を題材にして語る

はじめに―絶対・相対参照とSUMIF関数―

Excelで最も必要な知識は何かと聞かれて、答えは色々とあるだろう。
しかしその中で「絶対参照と相対参照」をランキング上位に上げて答える人は中々いないと思う。

あなたが絶対参照と相対参照(以下、まとめて「絶対・相対参照」という)を (表面上)知っていたとして、それをどれだけ日頃から使いこなし重要性を認識できているだろうか。

スポーツでも勉強でも基礎の大切さはよく問われるが、絶対・相対参照はExcelでいう最も大切にすべき基礎であろう。
これを使いこなせなければ給料泥棒である。

とは言ってもこの絶対・相対参照は、取っ掛かりがだいぶ難しいのも事実で、習得の近道といえるものもない。

そこで今回の記事では、関数の例題も使って、具体例を多く出して、この必須の基礎知識を何としてもマスターしてExcel使いとして一皮むけられるようにしていきたい。

絶対・相対参照の重要性―計算式のコピペ―

Excelの計算式というものは、1個のセルに設定したらそれを、可能な限りどこまでも縦にも横にもコピペしていくものだ。

慣れない人が作った不慣れな計算式は、セルが違えば計算式も違って、あちこちでバラバラな計算式が入っているものだ。

しかし上手な人が作る計算式は違う。
どのように計算式を組み立てれば、1個の計算式だけを使い回してあちこちのセルにコピペしていけるかが考え抜かれている。

改めて言うが、Excelの計算式というのはコピペしまくってなんぼだ。
コピペしまくっているということは計算式が統一的になっていて、当然メンテナンスの手間も少なくなるし、シンプルで綺麗だ。

そして計算式のコピペを使いこなすということは絶対・相対参照を使いこなすということに他ならないのである。

絶対・相対参照を一言で言えば計算式コピペのためのテクニックであり、エレガントなExcelファイル作成に絶対不可欠のテクニックなのだ。

ただね・・・。絶対・相対参照を使いこなすというのは、それなりの計算式や関数を使うことが前提であり、まずその段階に来てない給料泥棒が多いと思う。

あなたの職場ではどうだろうか。絶対・相対参照を使えてるかっていう審査の壇上レベルに来てない人が、多くはないだろうか。

絶対・相対参照を自在に使えるようになれば、たとえ真の実力はさほどなくても、かなりExcelが上手く見えることは事実だ。
ハッタリとしてだけでもかなり有効だ。優先的にマスターしよう。

SUMIF関数を題材に用いる

そこで当記事では、ExcelでIF関数とかよりよほど重要だと私が考えるSUMIF関数を同時に取り上げて、絶対・相対参照を説明してみたい。

ちなみに、SUMIF関数の次に重要な関数はVLOOKUPで、その次はCOUNTIFというところだと思うけど、もちろんそれらでも絶対参照と相対参照は超重要だ。

そしてSUMIF関数は、絶対・相対参照の用途を最も効果的に説明できる題材作りに最も適していると思ったので、同時に説明することにした。

また、SUMIF関数の上位互換的な関数としてSUMIFS関数もあり、それについても別の記事で説明している。

例題

上図は、実務でも起こり得ると思うが、1行ごとに収入・支出が書かれていて、それらの合計を10行目・11行目に計算したいというものだ。

一応、サンプルExcelファイルも付けておきます。
こちらよりダウンロード

この問題で、給料泥棒レベルの初心者だと、売上合計のC10セルに
=C2+C4+C6+C8
などとマジで入れたりする。

いやまあ、その式でも、他の合計セルにそのまま貼り付けていける式ではあるが、合計する対象セルがもっと何百行にもわたったりしたら、そんな
=C2+C4+C6+・・・・・・・
なんて書いてられない。

効率化に関する考え方の一つとして、行数が10行から1000行に増えたとして作業時間も100倍に増えるようなやり方は論外と考えるべきだ。

まずSUMIF関数について

では、絶対・相対参照というものを何も考えず、まずSUMIF関数を使った回答を、C10セルだけに入れてみると、
=SUMIF(B2:B9,B10,C2:C9)
というようになる。

B2:B9の範囲内においてB10セル＜収入＞に合致する行(合計行)を抽出→C2:C9セルの中で合計行に該当するものだけを合計する
というのが、このSUMIF関数だ。

別解としては
=SUMIF(B2:B9,“収入”,C2:C9)
と、「“収入”」の部分を直接キーワード記入する方法もある。

場合によってはこれでも良いが、こういう露骨なキーワード記入はスマートではない。

特にこの例題では、11行目になると「“支出”」と個別にキーワードを書き換えないといけなくなり、不正解レベルだ。

なるべく「B10」のようにセル参照で指定しよう。これも、これから述べる「相対参照」と関わってくることだし、SUMIF関数に限らずExcel全般において心がけることだ。

注意事項

SUMIF関数においては、
=SUMIF(B2:K9,B10:B11,C2:K9)
のように、式中の条件指定を複数行・列を組み合わせて指定しないこと。

SUMIF関数の構成は
SUMIF(①範囲,②検索条件,③合計範囲)
となっているが、
①と③は「1行×n列」か「n行×1列」にして統一する。
①が1行×3列で③が1行×4列とかになってはいけない。

また②は単一のセルや値を参照するようにし、複数セル範囲を指定したりはしないこと。

SUMIF関数はこれらの注意事項を守らなくても、答えが出ることは多々あるし、仕様さえ知り尽くしていれば使いこなせないでもない。

しかしそんなのは一部の達人に任せて、 まずは上記の基本的な注意事項を遵守していこう。

完全な相対参照の式による答えのズレ

さて、今C10セルに入れた計算式
=SUMIF(B2:B9,B10,C2:C9)
を、隣のD10セルにコピペしてみよう。

すると、計算式は
=SUMIF(C2:C9,C10,D2:D9)
となり、答えは0になって明らかにおかしい。

絶対・相対参照を考慮せず入れたExcelの計算式(それを「相対参照」と言うのだが)というものは、コピペをすると縦横にセルの参照を自動でズラすようになっている。
それをまず理解し自分のものにしないといけない。

まず全て絶対参照にしてみる

計算式をコピペしたときにも、このズレの発生を防ぐのが絶対参照だ。

それでは、C10セルの計算式のカッコ内
B2:B9,B10,C2:C9
の部分を全部マウスでドラッグし、F4キーを押してみよう。
(普通はここまで、複数の範囲に同時にF4キーを押したりはしないと思う。ここでは練習のため。)

すると、式が
=SUMIF($B$2:$B$9,$B$10,
$C$2:$C$9)
と変化し、あちこちに「$」マークが付く。

知らない人が見ると、「このドルマークって、何なんですか？」と言ってくるやつだ。

この変化後の数式を、10行目～11行目に全て貼り付けてみると、答えは全て同じ値になるし、セルの中の計算式も全く同じになる。

絶対参照を相対参照に修正していく

これは、絶対参照により計算式を全て固定したためであり、コピペしても数式が一切変わらなくなった。もちろんこのままではいけない。

何がいけないか。式を再掲しよう。
=SUMIF($B$2:$B$9,$B$10,$C$2:$C$9)

まず最初に指定している
$B$2:$B$9
の部分は、キーワード「収入」「支出」が並んでいる箇所で、ここは変えなくて良い。

次に検索条件の$B$10だが、11行目においてもこのB10セルが指定されたままでは困る。
11行目に行ったらB11セルが指定されるようにしないといけない。

ではまず、このC10セルの式$B$10の文字にマウスカーソルを当ててF4キーを押してみよう。

恐らく1回では駄目で、何度かF4キーを押して、$B$10が$B10と、「B」文字の前だけに$マークが付くようにしよう。

その変化後の式を、10行目～11行目に全てコピペすると、C10セル・C11セルの答えはひとまず正しくなる。
しかし、横の他のセルにも全て同じ値が入ってしまうことになり、まだ駄目だ。

この理由は、最後の合計範囲$C$2:$C$9が完全固定されているため。

計算式が横にコピペされたら、この合計範囲も横にズレてくれないといけない。
2月なら2月の、4月なら4月の数値を縦に合計したいわけだ。

では次に、この$C$2:$C$9の部分をマウスで全部ドラッグし、またF4キーを何度か押して、C$2:C$9と、「C」文字の後ろにだけ$マークが付くようにしよう。

その上で数式をコピペすれば、今度は完全に正しく集計される。

完成した数式の説明

この完全に修正したC10セルの計算式を示すと、
=SUMIF($B$2:$B$9,$B10,C$2:C$9)
となる。

(1)最初の$B$2:$B$9は、$マークでガチガチに固めて完全固定だ。これが正確な意味の絶対参照だ。

(2)次の検索条件$B10は、コピペ後は縦方向にはズラしたいが横方向にはズレてほしくない。
だから、ズラしたくない「B」文字の前に$マークを付け、ズラしたい「10」文字の前からは$マークを外した。

(3)最後の合計範囲C$2:C$9は、コピペ後は横方向にはズラしたいが縦方向にはズレてほしくない。
だから、ズラしたくない「2」「9」文字の前に$マークを付け、ズラしたい「C」文字の前からは$マークを外した。

絶対・相対参照をマスターするために

クドい書き方になったがもう一度。
ズラしたくないところの前に$マークを付け、ズラしたいところの前からは$マークを外すのが絶対・相対参照のコントロールだ。

厳密な定義でいうと、

ということになるが、あまりこの定義に囚われなくてよいだろう。
私もけっこう、「相対参照」と「複合参照」なんかは混同して使っている。

上記の例ではF4キーを押して$マークを付けたり消したりする方法ばかり述べたが、もちろん理解さえしていれば、手作業でDeleteキーとか押して入力しても構わない。

というか私もそのやり方のほうが多いし、正しい結果が出せて効率が落ちなければ、方法は一つではない。

そんなことより、絶対・相対参照を使いこなして、計算式の縦横無尽なコピペ利用をできるようにしていくためには、「A1」のような完全な相対参照の計算式の使用を意識的に減らしていくのが1つの方法かと思う。

これは、計算式を変える度胸もだし、絶対・相対参照の正しい知識がなければできないことで、この訓練を重ねればきっと上手くなっていく。絶対・相対参照を完全にマスターしよう。