合計が合わない原因は数値の文字列化かも。見分け方とAIへの伝え方

見分けサイン一覧

上から順に確認します。

• 緑の三角、見え方・確認方法：セルの左上隅に小さな緑の三角が出る、補足：公式が挙げる代表的なサインです
• 左揃え、見え方・確認方法：数値は通常右揃えなのに左に寄る、補足：公式が挙げるサインですが、配置を手で変えた表では当てになりません
• ISNUMBERがFALSE、見え方・確認方法：別の列に =ISNUMBER(A2) を入れて下へコピー、補足：見た目に頼らない判定です
• 個数の差、見え方・確認方法：=COUNT(A2:A101) と =COUNTA(A2:A101) を比べる、補足：COUNTは数値だけを数え、文字列は数えません。差が出たら文字列化の候補ですが、論理値やエラー値でも差は出ます
• 文字数が多い、見え方・確認方法：=LEN(A2) が見た目の桁数より大きい、補足：カンマも1文字と数えます。それを除いても多ければ、空白や見えない文字を疑います
• 末尾のマイナス、見え方・確認方法：「156-」のように右側にマイナスがある、補足：会計ソフト由来のデータの例として公式にあります

ISNUMBERは、IS関数が値を変換せずに判定する仕組みを使っています。公式の例では、文字列の「19」に対するISNUMBERはFALSEになります(IS関数)。

緑の三角が出ないからといって、数値とは限りません。公式ページには、「文字列形式の数値、またはアポストロフィで始まる数値」のエラーチェックをオプションでオフにできるとあります(公式の変換手順)。出ていない場合も、ISNUMBERで確かめます。

変換方法の選択表

• 緑の三角があり、範囲をまとめて直したい、方法：範囲を選び、三角のメニューから「数値に変換する」、元データ：元のセルを直接変更、注意点：実行前にシートを複製します
• 表示形式が文字列になっている、方法：範囲を選び、表示形式を数値などに変更、元データ：元のセルを直接変更、注意点：公式が挙げる方法です。直らなければ別の方法にします
• 元の列を残したまま変換したい、方法：別列に =VALUE(A2) を入れ、下へコピー、元データ：元の列は残る、注意点：結果を確認してから「値」として貼り付けます
• 全部に同じ処理を一括適用したい、方法：1を入れたセルをコピーし、対象範囲に「形式を選択して貼り付け」の「乗算」、元データ：元のセルを直接変更、注意点：元に戻しにくいので複製が前提です
• 空白や見えない文字が混じる、方法：=VALUE(TRIM(CLEAN(A2))) を別列に、元データ：元の列は残る、注意点：TRIMは単語間のスペース1つを残します。改行しないスペースは次の行の処理が必要です
• 改行しないスペース(コード160)が混じる、方法：=VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))、元データ：元の列は残る、注意点：Webからのコピーで起きやすいパターンです
• 末尾にマイナスがある、方法：=LEFT(A2,LEN(A2)-1)*-1、元データ：元の列は残る、注意点：公式が会計ソフト由来のデータ向けに示す式です

TRIM関数が対象とするのは通常のスペース(値32)だけです。改行しないスペース(値160)は、TRIM単独では削除されません(TRIM関数)。表の160用の式は、その対処としてSUBSTITUTEで通常のスペースに置き換える形にした説明用の式です。

VALUE関数が対象にするのは、Excelが数値・日付・時刻として認識できる文字列です。公式には、数式中の値は必要に応じて自動で変換されるため、通常は数式でVALUEを使う必要はないともあります(VALUE関数)。「+で足すと合うのにSUMだけ合わない」という現象は、この違いで説明できる可能性があります。ただし実際の表で確かめてください。

元データを壊さない進め方

1. シートを複製する(または別名で保存する)。
2. 合計が合わない列の右に、判定用の列を作る(=ISNUMBER(A2))。
3. FALSEの行をフィルターで絞り、どんな文字が混じっているか目で見る。
4. 状況に合う方法を、別列で試す。
5. 変換後の列で合計を出し、電卓や元の資料と照合する。
6. 一致したら、変換後の列を「値」として貼り付けて使う。

この手順なら、途中で違うと気づいても元の列が残っています。

変換前に、次の点も確認します。

• 先頭のゼロ: 郵便番号、電話番号、商品コードは、数値にすると先頭の0が消えます。計算しない列は変換しません。
• 日付に見える文字列: VALUEは日付も数値(シリアル値)にします。意図しない変換にならないか確認します。
• 小数点と桁区切り: 「1.234」が千二百三十四なのか1.234なのかは、データの出所の書式で変わります。

AIに任せる範囲と、伝える情報

AIに頼めるのは、原因の候補の洗い出しと、列の構成に合わせた式の下書きです。AIは手元のファイルを見られないので、情報が少ないと一般論になります。

渡す情報は次のとおりです。

• 使っているアプリとバージョン(Excelなど)
• 症状(例:合計が電卓より小さい)
• 数字の列と、サンプル行(5〜10行)
• 制約(元の列は変えたくない)
• 欲しい出力(式、手順、確認方法)

実データをそのまま貼る必要はありません。氏名、取引先、金額などは、構造が同じダミーの数字に置き換えます。

依頼文の例(説明用)

Excelの表で、E列の合計(SUM)が電卓の結果より小さくなります。
E列は金額で、システムからCSVで取り込みました。
下はダミーで、実際の表の構造を真似たものです。

E2: 12,000
E3: 8500
E4: " 3,200"(先頭に空白がありそう)
E5: 1,500-(右側にマイナス)

お願い:
1. 文字列になっている可能性が高い順に、原因の候補を挙げてください。
2. 元のE列は変更せず、F列に変換結果を出す式を原因ごとに示してください。
3. 変換が正しいか確かめる手順(件数と合計の照合)も教えてください。
4. 先頭ゼロや日付など、変換で壊れるおそれがあれば指摘してください。

この依頼文と入力例は説明用で、実際に実行した結果ではありません。

AIの回答で、人が見るところ

• 式が元の列を参照しているか(上書きする手順になっていないか)
• 説明に出る関数名やメニュー名が、自分のExcelの画面にあるか
• 変換後の件数と合計が、元の資料と一致するか
• 先頭ゼロなど、計算に使わない列まで変換していないか

まとめ: 迷ったときの順番

合計が合わなければ、まず =ISNUMBER() で数値かどうかを判定します。FALSEが混じっていたら、複製したシートの別列で変換し、合計を照合します。空白や見えない文字があるときは、TRIMやSUBSTITUTEを先に通します。式を組むのが難しいときだけ、ダミーデータを付けてAIに下書きを頼みます。