月末の売上集計で、SUM関数で出した合計が、請求書の合計と1,800円だけ合わなかったことがあります。
たった1,800円です。桁が違うわけでも、マイナスになっているわけでもない。数式を見ても =SUM(D4:D38) と書いてあるだけで、どこにも間違いらしい間違いが見当たりません。結局、原因にたどり着くまでに30分近くかかりました。犯人は、前任者が「今期は集計対象外」と言って非表示にしていた、たった1行の返品データでした。
SUM関数は、Excelを触りはじめて最初に覚える関数だと思います。範囲を選んで合計するだけ。それ以上でもそれ以下でもない、と私もずっと思っていました。ただ実務で使い続けてみると、SUM関数の本当の難しさは「書き方」ではなく「範囲の中に何が入っているか」にあると気づかされます。しかもSUM関数は、おかしな状態になっていても赤いエラーを出してくれないことが多い。だから、誰も気づかないまま資料が回っていきます。
この記事では、SUM関数の基本の書き方をひととおり整理したうえで、後半で「合計が正しく出ないとき、どこを疑えばいいか」を症状別にまとめます。使い方だけ知りたい方は前半だけ、いま数字が合わなくて困っている方は後半から読んでいただいても大丈夫です。
そもそもSUMを使うべき場面なのか迷っている、という方は、どの関数をいつ使うかを目的別に整理した記事を先に読んでおくと、この記事の内容も入りやすくなると思います。
SUM関数は「足し算」ではなく「範囲の中の数値だけを拾う」関数
最初に、言葉の定義をはっきりさせておきます。ここを曖昧にしたままだと、後半の原因診断が全部ぼんやりしてしまうからです。
SUM関数は、足し算をする関数ではありません。正確には「指定された範囲を上から順に見ていって、数値として入っているものだけを拾い集めて合計する」関数です。「足し算」と「数値だけを拾う」は、日常の感覚ではほとんど同じに聞こえますが、実務では結果が大きく変わります。
SUM関数が持つ3つの性格
この3つは、欠陥ではなく仕様です。SUM関数は「範囲の中の数値を合計する」という仕事を、いつでも忠実にこなしているだけなんですね。
問題は、私たちが頭の中で思い描いている「この表の売上の合計」と、SUM関数が実際にやっている「D4からD38までの数値の合計」がズレたときに、Excelは何も教えてくれないという点です。冒頭の1,800円のズレも、まさにこの②の性格が原因でした。SUM関数を疑うのではなく、範囲の中身を疑う。この順番を体に入れておくと、トラブルの解決が一気に早くなります。
基本の書き方は3通り覚えておけば足りる
ここからは具体的な書き方です。SUM関数の入力方法はいくつもありますが、実務で使うのは次の3通りでほぼカバーできます。
① 連続した範囲をまとめて足す
いちばん基本の形です。A1からA10までを合計したいなら、こう書きます。
=SUM(A1:A10)A1:A10 の「:（コロン）」は「AからBまで」という意味です。手入力してもいいですし、=SUM( まで打ってからマウスで範囲をドラッグしても同じことができます。慣れないうちは、ドラッグで選んでから、数式バーに表示された範囲が自分の意図通りかを目で確かめる方法をおすすめします。
② 離れた場所をカンマでつなぐ
表の中に小計行が挟まっている場合など、連続していない範囲を足したいときは、カンマで区切って並べます。
=SUM(B4:B10, B14:B20, B24:B30)カンマは「これも足してね」という合図です。単独のセルや数値そのものを混ぜることもできます。
=SUM(B4:B10, B15, 5000)ただ、この書き方は便利な反面、後から見た人が「なぜこの3つなのか」を追えなくなりがちです。飛び飛びの範囲を3つ以上つなげたくなったときは、たいてい表の作り方のほうに無理があります。そういうときは、後述する「集計用の列を1つ足す」方法を検討したほうが、結果的に早く終わります。
③ Alt + = で一気に入力する
合計を出したいセルを選んで、キーボードの Alt キーと = キーを同時に押す。それだけでSUM関数が自動で入力されます。オートSUMと同じ動きをキーボードだけで呼び出せるので、数字を扱う仕事をしているなら、これは覚えておいて損がありません。
注意点が一つあります。このとき選ばれる範囲は、Excelが「たぶんここでしょう」と推測したものにすぎません。推測は、空白セルにぶつかったところで止まります。表の途中に空白行があると、その手前までしか選ばれず、下半分がまるごと合計から抜け落ちる、ということが起こります。
Alt + = を押したあと、Enterを押す前に、点線で囲まれた範囲を一度だけ見る。この1秒の習慣が、後で30分の犯人探しを防いでくれます。
一段上の使い方：シートをまたぐ・引き算する
基本形に慣れてきたら、次はこのあたりが実務で効いてきます。
同じ形のシートが並んでいるなら串刺し集計
「4月」「5月」「6月」と月ごとにシートを分けていて、どのシートも同じ位置に同じ形の表がある。よくある構成だと思います。この場合、シート名をカンマで並べる書き方もできますが、
=SUM(4月!B20, 5月!B20, 6月!B20)シートが12枚あると、この書き方は現実的ではありません。そこでコロンを使います。
=SUM(4月:6月!B20)これは「4月シートから6月シートまで、全部のB20を合計する」という意味です。串刺し集計、または3D参照と呼ばれる書き方で、シートの並び順が基準になります。
便利なのですが、落とし穴もあります。4月シートと6月シートの「間」に新しいシートを差し込むと、そのシートも自動的に集計対象に入ります。逆に、6月シートの右側に7月シートを作っても、それは対象に入りません。シートを移動させただけで合計額が変わってしまうので、串刺し集計を使うファイルでは、シートの並び替えを安易にしないのがルールです。集計対象の端に「ここから」「ここまで」という空のシートを置いておき、その内側にデータシートを挟む運用にしておくと、この事故はかなり減らせます。
SUM関数で引き算をする
「引き算専用の関数はないんですか」と聞かれることがありますが、ありません。SUM関数はプラスの値もマイナスの値も区別せずに合計するので、引きたい値をマイナスにして渡せば、それが引き算になります。
=SUM(C2:C10, -D1)売上の合計から値引き額を引く、といった場面ではこの形が使えます。ただ、個人的には次のように書くほうが好みです。
=SUM(C2:C10) - SUM(D2:D10)「売上の合計から、値引きの合計を引いている」という意図が、数式を見ただけで伝わるからです。数式は自分だけが読むものではありません。半年後の自分や、引き継いだ誰かが読んで、5秒で意味がわかる書き方を選ぶ。これも実務では立派な判断基準になります。
合計欄を先に作っておくという考え方
もう一つ、地味ですが効果の大きい使い方があります。データを入力する前に、合計欄の数式だけ先に入れてしまう方法です。
データが1件も入っていない段階で =SUM(D4:D100) と入れておく。合計は0と表示されますが、それで構いません。あとは4行目から順にデータを入れていくだけで、合計が自動で増えていきます。この順番なら、データが増えるたびに範囲を修正する必要がなく、範囲の取り直し忘れという事故そのものが発生しません。
「先に器を作ってから中身を入れる」。集計の仕事では、この順番のほうが圧倒的に安全です。
合計が正しく出ないときの原因診断
ここからが本題です。数式の書き方は合っているのに、合計がおかしい。そういうときは、症状から原因を絞り込んでいきます。当てはまるものを探してみてください。
症状1：合計が0になる、または明らかに少ない
もっとも多いのが、数値が数値として認識されていないケースです。SUM関数は文字列を無視するので、範囲内の数字が全部文字列扱いになっていると、合計は堂々と0を返します。
見分け方は簡単です。セルに何も書式設定をしていない状態で、数字が左に寄っていたら文字列、右に寄っていたら数値です。中央揃えなどを設定している場合は、対象範囲を選択して画面右下のステータスバーを見てください。そこに「合計」が表示されなければ、数値として扱われていません。
この状態になる典型的な原因は次の3つです。
- 基幹システムや会計ソフトからCSVで書き出したデータをそのまま貼り付けた
- 数字の先頭にアポストロフィ（’）が入っている
- セルの表示形式が「文字列」のまま数字を入力した
直し方は、対象範囲を選んで表示形式を「標準」に変えたあと、セルをダブルクリックしてEnterを押す、という手順が確実です。件数が多いときは、空のセルに「1」を入力してコピーし、対象範囲に「形式を選択して貼り付け」→「乗算」を実行すると、まとめて数値に変換できます。
症状2：合計が多すぎる、余計な数字が混ざっている
範囲の中に、集計対象ではない数字が紛れ込んでいるパターンです。小計行を含んだまま合計してしまい、金額がきっちり2倍になっている、というのは定番の事故です。
もう一つ厄介なのが、表の途中に書き込まれたメモです。「※要確認 3件」のような書き込みが集計列に入っていると、思わぬところで計算が狂います。
このあたりは表の作り方そのものの問題でもあるので、表の途中にメモを書くとどんな事故が起きるかをまとめた記事もあわせて読んでみてください。集計がズレる原因の半分くらいは、関数ではなく表の設計側にあります。
症状3：#VALUE! エラーが出る
SUM関数は文字列を無視するので、範囲指定をしているかぎり #VALUE! はあまり出ません。これが出ているときは、範囲ではなく個別のセルを直接足しているケースがほとんどです。
=SUM(A1, A2, A3)この書き方だと、A2に「未定」などの文字が入っていた瞬間にエラーになります。範囲指定の =SUM(A1:A3) なら無視されるのに、です。同じSUM関数でも、書き方によって挙動が変わる数少ない例なので、覚えておくと役に立ちます。
また、参照先のセルが #N/A や #DIV/0! になっている場合、SUM関数はそのエラーをそのまま持ち上げてきます。合計欄がエラーになったら、まず参照元をたどってください。
エラーをとりあえず非表示にしたくなる気持ちはよくわかりますが、その前に読んでおいてほしい話をエラー表示の扱い方と、隠す前に確認しておきたいことにまとめています。エラーを隠すのと直すのは、まったく別の作業です。
症状4：数値を直したのに合計が変わらない
セルの中身を修正したのに、合計欄の数字が古いまま。この場合は、計算方法が「手動」になっている可能性が高いです。
リボンの「数式」タブ →「計算方法の設定」を開いて、「自動」になっているか確認してください。重いファイルを扱う部署では、動作を軽くするために手動に切り替えたまま共有されていることがあります。とりあえず今すぐ再計算したいときは F9 キーで強制的に計算できますが、根本的には「自動」に戻しておくのが安全です。
症状5：画面に見えている数字と合計が食い違う
フィルターで絞り込んだ状態なのに、合計欄は全件の数字を出している。これは冒頭でお話しした、SUM関数の性格②そのものです。
表示されている行だけを合計したいなら、SUM関数ではなくSUBTOTAL関数を使います。
=SUBTOTAL(109, D4:D100)先頭の 109 は「合計、ただし非表示の行は除く」という意味の番号です。フィルターを使って作業する資料なら、合計欄は最初からSUBTOTALで作っておくと、見えている数字と合計が常に一致します。
非表示行の存在は本当に見つけにくいので、数字が合わないときは、行番号が飛んでいないか（3の次が7になっていないか）を確認するクセをつけておくとよいと思います。
事故を減らすSUMの書き方、4つの作法
原因診断とあわせて、そもそも事故を起こしにくくする書き方も押さえておきましょう。
作法1：合計欄は表の真下ではなく、1行空けて置く 表のすぐ下に合計行を置くと、データを追加したときに合計行を巻き込んだり、逆に新しい行が範囲外になったりします。1行空けておくだけで、この手のトラブルはかなり減ります。
作法2：範囲は少し広めに取る データが38行目までなら、=SUM(D4:D60) のように余白ごと指定しておきます。空白セルは無視されるので結果は変わりませんし、データが増えても範囲を直す必要がありません。
作法3：SUMの結果をさらにSUMしない 小計をSUMで出し、その小計をまたSUMで合計する。この構造は、どこかで二重計上が起きたときに原因を追えなくなります。合計は、常に元データから直接計算する。これを守るだけで、数字の説明責任が果たせるファイルになります。
作法4：条件付きの集計には最初からSUMIFSを使う 「A支店だけの合計」をSUMで出そうとして、該当するセルをカンマで並べていく。これはやめたほうがいいです。データが1件増えるたびに数式の修正が必要になり、必ずどこかで漏れます。
条件を指定して合計したい場面になったら、条件を指定して合計するSUMIFS関数の使い方を読んでみてください。SUMで頑張っていた作業が、数式1本で終わるようになります。
SUM関数のよくある質問
Q. SUM関数と「+」で足すのは、どちらがいいですか セルが2、3個なら =A1+A2 で十分です。それ以上になるならSUM関数を使ってください。理由は、範囲指定のほうが行の挿入・削除に強いからです。=A1+A2+A3 の状態でA2の下に行を挿入すると、その行は合計に入りませんが、=SUM(A1:A3) なら自動で範囲が広がります。
Q. 合計欄の数字が「1,234.0000001」のようになります Excel内部の小数計算の都合で起きる現象です。表示上だけ整えたいなら表示形式で桁数を指定し、計算結果そのものを丸めたいならROUND関数を使ってください。消費税や按分を扱う資料では、後者を選んでおくほうが安全です。
Q. 数値ではなく「入力されている件数」を数えたいのですが それはSUM関数ではなく、COUNT系の関数の仕事です。
条件に合う件数を数えるCOUNTIF関数の記事で、件数が合わなくなる原因もあわせて解説しています。
Q. 平均を出すときも同じ注意点がありますか 考え方は似ていますが、空白と0の扱いという別の論点が加わります。
AVERAGE関数の使い方と平均を求める時の注意点にまとめていますので、合計と平均をセットで使う資料を作る方は目を通しておくと安心です。
まとめ
SUM関数でつまずくとき、原因が数式の書き方そのものにあることは、実はほとんどありません。書き方は合っている。にもかかわらず数字が合わない。だから厄介なんですね。
最後に、この記事の要点を整理しておきます。
- SUM関数は「範囲の中の数値だけを拾って合計する」関数で、それ以外は黙って無視する
- 合計が少ないときは文字列化を、多いときは小計や余計な数字の混入を疑う
- 画面の数字と合わないときは、非表示行かフィルター。表示中だけを合計するならSUBTOTAL
Alt + =の範囲は「Excelの推測」でしかないので、Enterの前に一度見る- 条件付きの集計に入った時点で、SUMではなくSUMIFSの出番
冒頭の1,800円の話に戻ります。あのとき私が学んだのは、「数式を疑う前に、範囲の中身を見る」というただ一つのことでした。これを覚えてから、集計が合わないときの解決時間は目に見えて短くなりました。
SUM関数は、Excelでいちばん最初に覚える関数であると同時に、いちばん長く付き合う関数でもあります。焦らず、範囲の中身を一つずつ確認していけば大丈夫です。
なお、SUM関数単体ではなく、他の関数と組み合わせたときに起きる複合的なトラブルや、実務で使い回せる集計フォーマットについては、もう一歩踏み込みたい方向けにSUM関数の実務応用パターンをnoteでまとめました。基本を押さえたうえで、さらに複雑なケースに備えたい方はのぞいてみてください。
コメント