SUM関数の注意点 、離れたセルの合計や日付・時刻の扱い

この連載では、関数や数式を使ってExcelを便利に活用する方法を紹介していく。初回となる第1回目は、関数を使える方なら誰でも知っている「関数SUM」の注意点について紹介していこう。安易に関数SUMを使用すると、状況によっては予想外の結果をもたらす危険性がある。Excelに慣れている方も、念のため確認しておくとよい。

「合計」を手軽に算出できる関数SUM

Excelでよく使用する関数として、真っ先に思いつくのは「合計」を算出する関数SUMではないだろうか？　その使い方は、カッコ内（引数）にセル範囲を指定するだけ。普段からよく使われている関数なので、「その仕様は十分に理解している（つもり）」という方が多いかもしれない。

• 

  関数SUMのノウハウと注意点を紹介

簡単な例を紹介しておこう。以下の図は「2020年の売上」の合計を関数SUMで算出した例だ。引数に「合計を求めるセル範囲」（C4:C7）を指定するだけで、その合計を求めることができる。

• 

  関数SUMの入力

• 

  関数SUMにより算出された「合計」

この例からもわかるように、関数SUMの使い方は特に難しいものではない。ただし、状況によっては、予想外の結果が表示されているケースもある。もちろん、そのことに気付かないまま作業を進めていくと、のちに重大なトラブルに発展してしまう恐れがある。

ということで、今回は「関数SUMの細かな仕様」について紹介していこう。Excel関数に不慣れな方だけでなく、Excelに慣れている方も一読しておくとよいだろう。

離れたセル範囲の合計を算出するには？

関数SUMの注意点を紹介する前に、覚えておくと便利な記述方法をひとつ紹介しておこう。すでに知っている方も多いと思われるが、こちらは初心者向けの補足説明と捉えていただければ幸いだ。

関数SUMは、1つのセル範囲だけでなく、離れた場所にある「複数のセル範囲」について合計を算出することも可能となっている。この場合は、それぞれのセル範囲を「,」（カンマ）で区切ってカッコ内に記述すればよい。

具体的な例を紹介しておこう。以下の例は、「国内」と「海外」について「各拠点の売上」を別々の表にまとめたものだ。これらの表から「2020年の売上」を合計する場合を考えてみよう。この場合は、「C4:C7」と「G4:G7」のセル範囲を「,」で区切って関数SUMの引数に指定すればよい。

• 

  引数に「複数のセル範囲」を指定する場合

「Enter」キーを押して関数SUMの入力を確定すると、2つのセル範囲内にある「数値」をすべて合計した値が計算結果として表示される。

• 

  関数SUMにより算出された「合計」

このように、関数SUMの引数に「複数のセル範囲」を指定することも可能である。そのほか、「=SUM(100,B2)」のように「数値」や「セル参照」をカンマ区切りで指定することも可能となっている。

関数SUMの引数には、「セル範囲」、「セル参照」、「数値」を最大255個まで組み合わせて指定できる仕様になっている。現実的に考えて、何十個、何百個もの引数を指定するケースは滅多にないと思われるので、「カンマで区切って、何個でもセル範囲などを指定できる」と覚えておいても支障はないだろう。

「数値」以外のデータが含まれる場合は？

続いては、合計するセル範囲に「数値」以外のデータが含まれる場合について紹介していこう。結論から述べると、合計するセル範囲に「文字」や「空白」のデータが含まれていても、関数SUMは正しく機能してくれる。ただし、例外となるデータもあるので注意が必要だ。

簡単な例を使って解説していこう。以下の図は、各列の3～7行目を関数SUMで合計した例となる。

• 

  「空白」や「文字」などを含む場合の検証例

一番左のB列は最も基本的な形だ。この列には「数値」データしかないため、当然ながら「正しい合計」が算出される。

C～E列は、合計するセル範囲に「文字」や「空白」のデータが含まれている例だ。この場合は「文字」や「空白」を無視した形で合計が算出される。なお、D列にある「30個」のように単位を付けて数値を入力すると、そのデータは「文字」として扱われる仕様になっている。このため、「30個」のデータを無視した形で合計が算出される。初心者の方が犯しやすいミスなので間違えないように注意しておこう。

一方、F～H列は合計の算出に問題が生じている例となる。順番に解説していこう。

F5セルには数式を入力してあるが、この計算結果が何らかの原因によりエラーになると、それを合計する関数SUMの計算結果もエラーになる。

G列とH列は、合計するセル範囲内に「日付」や「時刻」が含まれている例だ。この場合、エラーは発生しないが、予想外の計算結果が表示されてしまう。

このような結果になるのは、Excelが「日付」や「時刻」を数値の一種として扱っていることが原因だ。数値であれば、他の数値と足し算することも可能。つまり、「10＋20＋（5月10日）＋40＋50」などの計算が行われ、その結果として不可解な計算結果が表示されている訳だ。

こういった計算について詳しく理解するには、シリアル値について学んでおく必要がある。これについては以降の連載で、いつか詳しく解説する予定だ。

ここで覚えておくべきポイントは、合計するセル範囲に「日付」や「時刻」を入力すると、予想外の計算結果が表示されてしまうこと。たとえば、「まだ名古屋支社から売上の数値が届いておらず、10月5日に届く予定・・・」というケースを考えてみよう。このような場合に、そのメモとして「日付」のデータを入力してしまうと、不適切な合計が表示されてしまう。

• 

  セル範囲に「日付」を入力したミスの例

上図では「各年の合計」が4から始まっているため、ともすると正しい結果のように見えるかもしれない。しかし、よく見ると「2022年の合計」だけ1桁多い数値になっている。これは「10月5日」も数値データとして加算されていることが原因だ。

「日付」や「時刻」を関数SUMで合計できることが便利に機能する場合もあるが、それは少し上級者向けのお話。よくわからない方は、不用意に「日付」や「時刻」を入力してはいけない、と覚えておこう。

どうしても日時を入力したい場合は、「10月5日の予定」のように文字を含めた形でデータを入力するとよい。この場合、データは「文字」として扱われるため、関数SUMから無視される（合計の計算に影響を与えない）。お勧めの対処法ではないかもしれないが、「不適切な合計が表示されるよりはマシ」といえるだろう。

途中に行を挿入するときは注意が必要！

続いては、関数SUMを入力した後に「行を挿入」したときの挙動について紹介していこう。まず、最初の状態を紹介する。以下の図は、各商品の「小計」を（単価）×（数量）で算出し、その合計を関数SUMで求めた場合の例だ。

• 

  最初の状態（1）

今回の例では、合計9,800円という結果になった。ここまでの話は、特に問題のない処理手順といえる。

• 

  最初の状態（2）

ただし、合計を算出した後に「★★を加算するのを忘れていた・・・」となると、トラブルが生じるケースがある。こちらも順番に解説していこう。

まずは、合計するセル範囲の「途中」に行を挿入した例を紹介する。たとえば、以下の図のように行を挿入したケースを考えてみよう。

• 

  セル範囲の「途中」に行を挿入

この場合、関数SUMの引数（セル範囲）が自動補正されるため、正しい計算結果を得ることが可能だ。試しに「商品X」のデータを入力してみると、合計が39,800円に更新されているのを確認できる。

• 

  関数SUMにより算出された「合計」

念のため、関数SUMの記述も確認してみよう。このような場合は、関数を入力したセルをダブルクリックすると、関数の記述を手軽に確認できる。

• 

  自動補正された関数SUMの引数

今回の例では、もともと「E3:E7」だったセル範囲が「E3:E8」に自動補正されている。よって、その計算結果（合計）も正しい数値になる。

次は、合計するセル範囲の「下端」に行を挿入した例だ。たとえば、「送料」の加算を忘れていたため、以下の図のように行を挿入したケースを考えてみよう。

• 

  セル範囲の「下端」に行を挿入

続けて、「送料」と「\780」のデータを入力する。この場合、合計が9,800円のまま更新されない・・・という結果になる。運よく、そのことに気付けばよいが、これを見落としてしまうと、重大なミスにつながる恐れがある。注意しておこう。

• 

  関数SUMにより算出された「合計」

念のため、関数SUMの記述を見てみると、そのセル範囲は「E3:E7」のまま変化していないことを確認できる。よって、「送料」の金額が加算されないのは当然、という結果になる。

• 

  関数SUMの引数（セル範囲）を確認

このように「行を挿入する位置」に応じて自動補正の挙動は変化する。また、一部例外もあるため、その挙動を正確に把握するのは難しい。たとえば、先ほどの例のように「下端」に行を挿入したあと、「商品X」、「\10,000」、「3」とデータを入力していくと・・・、

• 

  「上の行」と同じ形式でデータを入力

「小計」を計算する数式が自動入力され、「合計」も正しい計算結果が表示される。

• 

  関数SUMにより算出された「合計」

念のため、関数SUMの記述を見てみると、合計するセル範囲が「E3:E8」に自動補正されているのを確認できる。

• 

  関数SUMの引数（セル範囲）を確認

このように「下端」に行を挿入した場合であっても、Excelが気を利かせて「セル範囲」を自動補正してくれるケースもある。

以上の話をまとめると、「セル範囲の自動補正」が機能してくれる場合と、そうでない場合があることになる。基本的には以下のような挙動になると考えられるが、例外もあるようで一概には言い切れない。

・「途中」に行を挿入
　合計するセル範囲は自動補正される

・「上端」や「下端」に行を挿入
　合計するセル範囲は自動補正されない
　※自動補正される場合もある

こういった挙動をふまえると、合計するセル範囲の「上端」または「下端」に行を挿入するときは、いちど関数SUMの記述を確認し、必要に応じてセル範囲を修正する、といった習慣を身に付けておく必要があるだろう。そうしないと、数値データの加算漏れに気付かず、重大なミスに発展してしまう恐れがある。Excelに慣れている方も注意しておこう。