合計が 0 になる列（文字列になった数値）
問題
問題文
月次レポートで、5件の請求書の合計が 0 と表示されています（B2:B6）。金額はシステムから書き出された際に文字列として保存されたため、SUM は足すものが無いと判断しています。実際の合計を Correct total セル E2 に入れてください。
式の中で文字列を数値に変換してください。列を入力し直すことはできません。
問題のデータ
表でデータ全体を見る(選択・コピーできます)
| A | B | D | E | |
|---|---|---|---|---|
| 1 | Invoice | Amount | Report (wrong) | 0 |
| 2 | INV-901 | 1200 | Correct total | |
| 3 | INV-902 | 800 | ||
| 4 | INV-903 | 950 | ||
| 5 | INV-904 | 600 | ||
| 6 | INV-905 | 400 |
ポイント解説
SUM は文字列を無視します。取り込み時に数値が引用符で囲まれると、その列は SUM にとって存在しないのと同じです。SUMPRODUCT(--範囲) は -- （単項マイナス2つ）で各値を数値に変換し、SUM(VALUE(範囲)) は明示的に変換します。見分け方は配置で、文字列の数値は左寄せ、本物の数値は右寄せになります。
解き方のヒント
回答は E2 に入力します。送信時は 3 通りの入力で数式を再実行するので、数値を直接書くと必ず失敗します。数式はセルを参照してください。
- 対象セルは E2
- SUM が 0 なのは数値ではなく文字列だから
- 先に範囲を数値へ変換する
- 形：=SUMPRODUCT(--?) または =SUM(VALUE(?))
模範解答
模範解答を表示(2 通り・まずは自分で試してみましょう)
方法1：二重の単項マイナスで変換
=SUMPRODUCT(--B2:B6)
マイナス2つで各セルが数値になり、SUMPRODUCT が合計します：1200 + 800 + 950 + 600 + 400 = 3950。作業列も入力し直しも不要です。
方法2：SUM の中の VALUE
=SUM(VALUE(B2:B6))
VALUE が各セルを明示的に変換し、SUM が配列を合計します。結果は同じで、起きていることを素直に書いた形です。Excel 2019 以前では Ctrl+Shift+Enter が必要です。
よくある質問
数値が入っているのに SUM が 0 を返すのはなぜ？
文字列として保存されているためです。書き出しやWebページからの貼り付けでよく起きます。SUMPRODUCT(--範囲)、SUM(VALUE(範囲))、または 1 を掛ける方法で変換します。
1セルずつ見ずに文字列の数値を見分けるには？
配置を見ます。文字列の数値は左寄せ、本物の数値は既定で右寄せです。左に寄った数値は文字列の印です。