【Excel】SUMIF関数で計算結果が大きくなる原因と対処法
ExcelでSUMIF関数やSUMIFS関数の計算結果が大きくなってしまう場合の原因と対策を説明します。
SUMIF関数で計算結果が大きくなってしまう
下図のように元データから「顧客コード」を検索条件としてSUMIF関数を使用する場合を考えます。
この場合、普通に考えればオレンジセルでは6,500が計算されるはずですが、実際に集計すると下のような結果になってしまいます。
原因は検索条件に含まれる「E」の文字
原因はExcelの仕様です。上記の例では、顧客コードが3E2だけでなく300のデータを合計してしまっているため計算結果が大きくなっています。
Excelでは指数表示という機能があり、桁数が多い数字などはゼロの数をE2のような形式で表示します。下表に具体例を挙げましたので参考にしてください。
| 指数表示の例 | 数値 |
| 5E9 | 5,000,000,000 |
| 2.5E-6 | 0.0000025 |
| 3E2 | 300 |
SUMIF関数において、検索条件が数字＋E＋数字のようになっている場合、Excel側が文字列を数字として認識してしまい、一致していないはずのデータまで引っ張ってきてしまうという訳です。
不具合を解消するための手順
この不具合を解消するには、検索条件を指数表示ではなく文字列として認識させる必要があります。
「検索範囲の表示形式を文字列にすれば解決するんじゃないか？」と思われるかもしれませんが、実はそれでは効果がありません。Excelが内部で勝手にEを指数として解釈してしまうようなので、それを防ぐためには前方や後方に文字を追加して指数として解釈され得ない文字列に変換する必要があります。
今回は顧客コードの前方に_（アンダーバー）を付け足して回避します。複数範囲を選択し、Ctrl＋Enterを押すと一括入力できるため便利です。
集計表も同様に、顧客コードの前方にアンダーバーを追加します。
ここまでできたら、検索条件を前方にアンダーバーがついた顧客名に変更することで正しい計算結果が得られるようになります。
まとめ
SUMIF関数では、検索条件が数字＋E＋数字のような文字列の場合、Excel側が指数表示と誤認識してしまうことにより計算結果に差異が生じるケースがあります。
業務においては可能な限り上記のような不具合が起こらないよう、検索条件を数字＋E＋数字（特に数字＋E2は要注意）という文字列にしないことがベストです。
ただどうしてもそのような集計が必要な場合には、検索条件の前方や後方に文字を追加してExcelによる指数表示の認識を回避するという手段が有効です。
