Excel約5分で読めます

ExcelのSUMIFが0になる|「1,200円」など文字列の金額を原本を残して確認する手順

ExcelのSUMIFが0になるを表したイメージ
記事の内容を表したイメージ(AI生成)

外部システムからコピーした金額一覧をSUMIFで集計したら合計が0になった――その場合、金額セルの型が文字列になっていることがよくある原因のひとつです。

この記事では、型の確認から数値化、SUMIFへの反映まで、原本列を残したまま進める手順を示します。扱う式は公式構文をもとに作成していますが、編集部でのExcel/Sheets実機検証は行っていません。お手元の環境で動作を確認してから業務に適用してください。


なぜSUMIFの合計が0になるのか

SUMIFは条件に合う範囲の数値を合計しますが、文字列や空白は数値として足しませんS1。「1,200円」のように桁区切りや単位が含まれるセルは、Excelから見ると数値ではなく文字列として格納されることがあります。見た目は数字でも、ISNUMBERで確認するとFALSEが返りますS6。

まず型を確認し、文字列であれば作業用の列で数値に変換してからSUMIFの範囲に指定するのが基本の対処です。


確認の流れ

原本列(B列) → 型確認(G列) → 作業数値(F列) → SUMIFで集計(I列)

原本列は変更しません。確認と変換は別の列で行い、問題が起きたときに元の値と照合できる状態を保ちます。


ステップ1:型をISNUMBERで確認する

配布素材 amount-types.csv には以下の5件が入っていますS7。これは架空の練習用データであり、実在の業務情報ではありません。CSVはセル書式や入力規則を持たない平文データです。表計算アプリで開く際の自動型変換に注意し、C列「元の型」の記載と照合して再現してください。

表は横にスクロールできます

ID元の値元の型期待数値確認区分
K11200数値1200変換不要
K21,200円文字列1200変換対象
K3300文字列300変換対象
K4(空欄)空欄—空欄
K5要確認文字列—保留

G列に型確認の式を入れます。G2に次の式を入力し、G6までコピーしてください。

=ISNUMBER(B2)

この式はセルの値が数値型ならTRUE、そうでなければFALSEを返しますS6。架空例の独立検算では、K1がTRUE、K2〜K5がFALSEという結果になります。FALSEのセルはSUMIFで合計されません。


ステップ2:変換対象の条件を先に確認する

この手順では、変換対象を半角数字・桁区切りカンマ・末尾の「円」だけで構成された金額に限定します。式がそれ以外を自動で拒否するわけではありません。VALUEは日付や時刻なども数値に変換する場合がありますS4。日付・時刻・外貨表記・マイナス値・全角数字・先頭ゼロが必要な番号などがあれば、式を入れる前に対象から外し、確認を保留してください。

F列のヘッダー(F1)に「作業数値」と入力し、F2に以下の式を入力してF6までコピーします。

=IF(B2="","",IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(B2,"円",""),",","")),"要確認"))

式の動作は次のとおりです。

  1. B列が空欄ならF列も空欄にするS3
  2. 「円」をすべて取り除く(この教材では末尾だけにあります)S2
  3. 桁区切りのカンマを取り除くS2
  4. 残った文字列を数値に変換するS4
  5. 変換できなかった場合は「要確認」と表示するS5

架空例の独立検算では、F2=1200、F3=1200、F4=300、F5=空欄、F6=「要確認」になります。K5の「要確認」とK4の空欄は0円として確定させず、内容を確認してから扱いを決めてください。


ステップ3:原本と作業数値のSUMIFを比較する

I列に2つのSUMIFを並べ、原本列と作業数値列の合計を比較します。

I1に「元の合計」と入力し、I2に以下の式を入れます。

=SUMIF($A$2:$A$6,"K*",$B$2:$B$6)

H3に「作業数値の合計」と入力し、I3に次の式を入れます。

=SUMIF($A$2:$A$6,"K*",$F$2:$F$6)

架空例の独立検算では、I2=1200(数値型のK1だけが合計される)、I3=2700(K1・K2・K3の数値化済み値の合計)になります。この差が、文字列として格納されていた金額の影響を示します。

実際のデータで試す場合は、IDの条件(ここでは "K*")と集計範囲(A2:A6)をご自身のデータに合わせて変更してください。


追加行・空欄・適用範囲の確認

行を追加したら、SUMIFの参照範囲が実際の最終行まで含むか確認し、必要に応じて修正してください。今回の式では2〜6行目を参照しています。

空欄セルはF列の式で空欄のまま残り、SUMIFの合計には加算されません。空欄が「金額なし」なのか「未入力」なのかは元データの仕様を確認してください。

「要確認」になったセルは、元の値の形式を確認して個別に対処します。変換式を修正するのか、手入力で数値に直すのかは内容によって判断してください。


この手順が使えない場合

次のいずれかに当てはまる場合は、この手順をそのまま適用できません。

  • 金額に全角数字・マイナス・外貨記号・小数点が含まれる
  • 先頭ゼロを保持する必要がある番号列が混在している
  • VALUEが日付や時刻として誤認識する形式の値が含まれている

これらは別の変換手順が必要です。今回の資料では対象外のため、手順は含まれていません。


今回使った手順をまとめます。ISNUMBERで型を確認し、文字列のセルがあればSUBSTITUTE+VALUEの組み合わせで変換列を作り、SUMIFの参照先をその列に変える、という流れです。原本列は変えずに進めるため、変換前後の差を随時確認できます。

手順を試す際は、まず練習用の小さなデータで式の動作を確認してから、実務データに適用することをおすすめします。

この記事の確認用ファイル

架空データの記入例や、手元で確認するための素材です。

参考資料

公式資料と架空データを使って編集しています。配布CSVは値と見出しのみで、数式・書式・保護設定を含む完成ブックではありません。Excel・Sheetsの実機操作は未検証です。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ