Excelの合計が合わない。8行中6行が足されていませんでした｜COUNTとCOUNTAの差が0でない列、あなたの表にありますか

あなたの表の、金額の列。いま合計を出している列の「件数」を、数えたことはありますか。

合計が合わないとき、私たちはまず足し算を疑います。でも、足し算は合っていることがあります。足されていない行が混じっているだけだからです。

今回は、Excelで実際に作って確かめました。8行の表で、SUMが出した合計は35,000。本来の合計は120,800でした。8行のうち6行が、足されていませんでした。

6行は「数字」ではなく「文字」でした

表の中身は、こうです。12,000、8,500、3,300、45,000、7,200、9,800の6行を、文字列として入れました。残りの15,000と20,000の2行は、普通の数値です。

見た目は、どちらも数字です。ところがExcelの中では別物です。

数値の2行：15,000 ＋ 20,000
文字列の6行：SUMは無視します
SUMは文字列の数字を足しません。だから合計は35,000のまま、エラーは出ません。数字が並んでいるので、目では気づけません。

不思議なのは、足し算そのものは通ることです。文字列のまま、「=A1+A2」と書くと、12,000＋8,500＝20,500とちゃんと出ました。足し算は合うのに、SUMだけ合わない。ここで原因を探す方向がずれます。

こういう列は、他のシステムからCSVやコピーで貼り付けた表で起きます。MicrosoftのサポートページにもExcelで数値が文字列として保存される場合として、他のソースからデータをインポートした場合が書かれています（https://support.microsoft.com/ja-JP/Excel/fix-text-formatted-numbers-by-applying-a-number-format
）。

毎月、別のシステムから出した表を貼って集計している方は、とくに当てはまります。

点検は、COUNTとCOUNTAの2行で終わります

見つけるのに、目は使いません。件数を2つ数えて、引き算するだけです。

この表では、COUNTが2、COUNTAが8でした。差は6。この差が、そのまま足されていない行の数です。

手順にすると、こうです。

1. 金額の列の下に、COUNTとCOUNTAを並べる（合計の隣でかまいません）

2. 差を出す（=COUNTA−COUNT）

3. 0でなければ、その列を直してから合計を見る

見出し行など文字が入る行は、範囲に含めないでください。含めると差が1ずれます。

「書式を標準に」では直りませんでした

直し方で、よくある勘違いがあります。セルの書式を「標準」に変える方法です。

やってみました。結果は、SUMは35,000のままでした。セルも文字列のままで、「数値が文字列として保存されています」の印も残りました。書式は、これから入れる数字に効くもので、すでに入っている文字は変えてくれません。

直せたのは、次の2つでした。

1. 入力し直す。 セルを選んで、F2を押してEnter。これで、そのセルは数値になりました。1〜2行ならこれで足ります。

1. 区切り位置を使う。 列を選び、「データ」→「区切り位置」→そのまま「完了」。列全体が一度に数値になり、SUMは120,800になりました。 件数が多いときは、こちらです。

数字に変わらない行は、中身を見る

区切り位置でも直らない行があります。VALUE関数で試すと、こう分かれました。

前後の半角空白が付いている：数値になる
全角の数字（１２０００）→ 数値になる
カンマ入り（12,000）→ 数値になる
円記号付き（¥3,300）→ 数値になる
後ろに全角空白が付いている：数値になる
「7200円」のように文字が混じっている：#VALUE!
ウェブ画面からコピーした改行なし空白（CHAR(160)）が混じっている：#VALUE!
最後の2つは、数式だけでは変換できません。「円」は消す、CHAR(160)はSUBSTITUTEで普通の空白に置き換えてからVALUEをかける、という順になります。

ここまでの手順は、Excel 16.0の日本語版で確かめました。Mac版とGoogleスプレッドシートでは確かめていません。

AIに頼むなら、数えるところまで自分でやる

直す数式を作ってもらうなら、状況を先に渡すと早いです。

頼む前にCOUNTとCOUNTAを数えておくと、AIに「何行が文字列か」を伝えられます。答えが返ってきたら、直したあとにもう一度COUNTとCOUNTAを並べて、差が0になったかを確かめてください。AIの答えが合っていたかどうかは、この差で分かります。

まとめ

合計が合わないとき、足し算より先に足されていない行を疑う
実際に作ると、8行の表でSUMは35,000、本来は120,800。文字列の数字6行が足されていなかった
文字列の数字をSUMは無視し、エラーは出ない。ただし、「=A1+A2」のような足し算は通る
点検は COUNTAからCOUNTを引くだけ。差が、足されていない行の数
「書式を標準に」では直らない。入力し直すか、区切り位置で列ごと変える
直らない行は、文字（「円」）や改行なし空白（CHAR(160)）が混じっている
いま開いている表の金額の列で、COUNTとCOUNTAは一致していますか。

自分の業務で、AIに渡せる工程を見つけたい方は、LINE公式「AIメモ室」で「AI用途の棚卸しシート」をお渡ししています。

自分の業務を工程に5〜8個へ割って、AIに渡せる1つを見つける、記入式の1枚です。所要5分、書くのは3か所だけです。

・工程に割る記入欄（記入例つき）

・その工程をAIに渡せるか判定する3つの条件

・「整える時間」を1週間だけ測る記録欄

同じ「見た目は同じなのに合わない」話を、突合で書いた回はこちらです。