エクセルの数値が文字列で集計されない時の直し方

見た目は数字なのに SUM が 0 になるなら、原因はほぼ「文字列として保存された数値」の一つです。最短の直し方は、その列を選んで「データ」タブの「区切り位置」を開き、区切り文字のチェックを外したまま「完了」を押すこと。列まるごと数値に戻せます。ただし全角数字や、TRIM 関数では取り除けない文字コード 160 のスペースが混ざっていると区切り位置でも直らないので、まず切り分けてから手を選びます。

まず切り分ける（2〜3分）

直し方を選ぶ前に、本当に「文字列として保存された数値」なのかを確定させます。ここで挙げる操作はすべて読み取りだけなので、元のブックを壊しません。空いているセルを 2〜3 個使うだけです。

1. 合計したい範囲をドラッグし、画面いちばん下のステータスバーを見る。「データの個数」だけが出て「合計」が出ないなら、その範囲に数値が 1 つも入っていません。
2. 空きセルに =COUNT(範囲) と =COUNTA(範囲) を入れて比べる。COUNT は数値だけを数えるので、COUNTA との差がそのまま文字列になっているセルの件数です。
3. 疑わしいセル 1 つを =ISNUMBER(A2) で確かめる。TRUE なら数値、FALSE なら文字列です。ISNUMBER は引数を変換しないので、=ISNUMBER("19") は FALSE を返します。
4. =LEN(A2) で文字数を数え、画面に見えている桁数と一致するか確かめる。多ければ空白や改行が紛れ込んでいます。

見た目のサインでも当たりは付きます。セルの書式を「標準」にしたまま左寄せで並んでいる、左上に小さな緑の三角が出ている、のどちらかが出ていれば文字列です。ただし書式で中央揃えや右揃えを設定していると左寄せのサインは消えるため、最終判断は ISNUMBER で行ってください。

原因別の対処

直し方は 5 つあり、件数と中身の汚れ具合で使い分けます。数十件ならエラーチェック、列まるごとなら区切り位置、元データを残したいなら関数、が基本の選び方です。上から順に試す必要はありません。

方法1：エラーチェックの「数値に変換する」

緑の三角が出ているセルなら、これが一番速くて安全です。Microsoft が最初に案内している手順でもあります。

1. 緑の三角が付いたセル、または範囲をまとめて選択する。
2. 選択範囲の左上に出る「！」のボタンをクリックする（キーボードなら Alt + Shift + F10 でメニューが開きます）。
3. メニューから「数値に変換する」を選ぶ。

「！」のボタンが出ない場合は、エラーチェックがオフになっています。「ファイル」→「オプション」→「数式」を開き、「バックグラウンドでエラー チェックを行う」と、エラー チェック ルールの「文字列形式の数値、またはアポストロフィで始まる数値」の両方にチェックを入れてください。なお数千件以上を一度に変換すると待たされるので、その場合は方法2に切り替えたほうが早いです。

方法2：区切り位置ウィザードで列ごと一括変換

件数が多いときはこれが最短です。本来はテキストを複数の列に分ける機能ですが、分割せずに通すとセルの中身を読み直してくれるので、文字列が数値に戻ります。

1. 直したい列を 1 列だけ選ぶ（列見出しをクリック）。複数列を同時に選ぶと実行できません。
2. 「データ」タブ →「区切り位置」をクリックする。
3. 「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選び「次へ」。
4. 区切り文字のチェックをすべて外して「次へ」（この時点でプレビューが 1 列のままなのを確認します）。
5. 「列のデータ形式」で「G/標準」を選び、「完了」をクリックする。

表示先を変えなければ同じ列に上書きされます。注意点は 2 つ。伝票番号のように先頭の 0 を残したい列に実行すると 0 が落ちること、そして「001-02」のような表記が日付として読み替えられてしまうことがあることです。数値にしたくない列には実行せず、実行前にファイルをコピーしておくと安全です。日付に化けてしまったときの戻し方はエクセルで日付に勝手に変換される時の対処にまとめています。

方法3：形式を選択して貼り付けで「1」を掛ける

複数列をまとめて直したいときはこの方法が使えます。数値に 1 を掛けても値は変わらないので、計算させること自体が変換になります。

1. 空いているセルに 1 と入力して Enter を押す。
2. そのセルをコピーする（Ctrl + C）。
3. 直したい範囲を選択する（複数列でも構いません）。
4. 「ホーム」タブ →「貼り付け」→「形式を選択して貼り付け」を開く。
5. 「演算」の中の「乗算」を選んで「OK」。
6. 入力した「1」のセルを削除する。

ここでの落とし穴は書式です。元のセルの表示形式が「文字列」のままだと、値は数値になっても表示が変わらず直っていないように見えます。貼り付け後に範囲を選び直し、「ホーム」タブの表示形式を「標準」または「数値」に設定してください。貼り付け元の書式を持ち込みたくないときは、「貼り付け」の中の「値」を選んでから演算を指定します。

方法4：VALUE 関数や演算子で別列に作る

元データをそのまま残したいとき、または毎月同じ加工を繰り返すときは関数が向いています。VALUE 関数は、Excel が数値・日付・時刻として認識できる文字列を数値に変換します。

• =VALUE(A2) … もっとも素直な書き方。認識できない文字が混ざると #VALUE! が返ります。
• =A2*1 / =A2+0 … 計算させて変換する書き方。結果は VALUE と同じです。
• =--A2 … マイナスを 2 回かける書き方。短く書けるので配列的な集計式の中で使われます。
• =SUMPRODUCT(--A2:A20) … 列を直さずに合計だけ出したいときの応急処置。空白や文字が残っていると #VALUE! になります。

数式のままだと元の列を消せないので、作った列をコピーし、元の列の先頭セルで Ctrl + Shift + V（値として貼り付け）に置き換えてから作業列を削除します。なお VALUE は Excel の言語設定に従って小数点や桁区切りを解釈するため、海外システムから出た「1.234,56」のようなデータは NUMBERVALUE 関数で区切り記号を明示したほうが確実です。

方法5：全角数字・空白・改行・通貨記号が混ざっている

ここまでの方法を試しても FALSE のまま残るセルは、数字以外の文字が入っています。Web ページや PDF からコピーした値、基幹システムが出力した CSV でよく起きます。原因の文字ごとに関数を当てます。

• =ASC(A2) … 全角の数字や記号を半角に直します。全角（2 バイト）文字を半角（1 バイト）文字に変える関数です。
• =TRIM(A2) … 前後の余分な半角スペースを削除します。取り除けるのは文字コード 32 のスペースだけです。
• =CLEAN(A2) … セル内改行などを消します。削除するのは文字コード 0〜31 の 32 文字で、127・129・141・143・144・157 は残ります。
• =SUBSTITUTE(A2,CHAR(160),"") … Web からのコピーで紛れ込む文字コード 160 のスペース（ノーブレークスペース）を消します。TRIM でも CLEAN でも取れないのはこれです。
• =SUBSTITUTE(SUBSTITUTE(A2,"¥",""),",","") … 通貨記号と桁区切りのカンマを外します。

どれが原因か分からないときは、まとめて通す式を 1 本作って比べるのが早いです。=VALUE(TRIM(CLEAN(ASC(SUBSTITUTE(A2,CHAR(160),""))))) を隣の列に入れ、これで数値になるなら混入文字が原因、それでも #VALUE! なら数字として読めない文字（アルファベットや「〜」など）が入っています。犯人を特定したいときは =CODE(MID(A2,1,1)) の位置を 1 文字ずつずらして文字コードを確認してください。

VLOOKUP や XLOOKUP が一致しない場合

SUM が 0 になる現象と同じ原因で、検索値と参照先の型が食い違うと #N/A になります。見た目は同じ「1001」でも、片方が数値・片方が文字列なら別物として扱われるためです。

1. 検索値と、参照先の一列目の同じ値を =ISNUMBER() で両方調べ、どちらが文字列かを確定する。
2. 参照先が文字列なら、検索値を文字列に寄せる（=VLOOKUP(A2&"",…)）。
3. 参照先が数値なら、検索値を数値に寄せる（=VLOOKUP(VALUE(A2),…) または =VLOOKUP(--A2,…)）。

ただしこれは数式側での応急処置です。同じ表を他の人も使うなら、参照先の列そのものを方法2で直しておくほうが、後の事故が減ります。数式を直しても結果が変わらないときは、そもそも数式が再計算されていない可能性があるため、エクセルの数式が計算されない原因と対処も併せて確認してください。

再発させない運用

この症状は「一度直して終わり」になりません。毎月同じ CSV を取り込めば、毎月同じように文字列で入ってきます。入口を固定するのが唯一の対策です。

1. CSV をダブルクリックで開かない。Excel を先に起動し、「データ」タブ →「テキストまたは CSV から」で読み込む。
2. 取り込み画面（Power Query エディター）で列ごとにデータ型を指定し、金額・数量の列は「整数」または「10進数」にしておく。
3. 先頭の 0 を保持したい伝票番号・郵便番号・電話番号の列は、あえて「テキスト」のままにする。これらは数値に変換してはいけない列です。
4. 1 つの列に 1 つのデータ型しか入れないと決め、「未定」「―」「－」などの記号を数値列に混ぜない。空欄のままにする。

厄介なのは、アポストロフィ付きの '001 のように、意図して文字列にしている値と、事故で文字列になった値が同じ見た目で並ぶことです。列の役割が決まっていれば迷いませんが、1 枚のシートに手入力と CSV 取り込みが混ざると、どちらが正しいのか誰も判断できなくなります。

手入力とシステム出力が同じ台帳に同居している場合、型の崩れは何度でも起きます。入力の入口を分ける仕組みに寄せる選択肢もあるので、参考までに紹介させてください。

それでも直らない時

ここまでを試して ISNUMBER が TRUE になったのに、まだ合計が合わない場合は別の原因が重なっています。切り分けの順に挙げます。

• ISNUMBER は TRUE なのに SUM が 0 のまま … 計算方法が手動になっているか、SUM を入れたセル自体の表示形式が「文字列」になっています。数式の行き詰まりは別記事で扱っています。
• 合計は出るが端数が合わない … 表示形式で桁を丸めているだけで、元の値に小数が残っています。ROUND で値そのものを丸めているか確認します。
• 一部の行だけ変換できない … その行に数字として読めない文字（全角ハイフン、アルファベットの O と数字の 0 の取り違えなど）が入っています。方法5 の CODE で 1 文字ずつ確認します。
• 毎月・数万行で発生する … 手作業で直す範囲を超えています。出力元のシステム設定か、取り込みの自動化で解決すべき問題です。

判断の目安は「月 1 回以上起きるか」「複数人が同じ表を触るか」「元データがシステム出力か」の 3 つです。2 つ以上当てはまるなら、シート上で直すのをやめて、取り込みの段階で型を固定する側に手を入れたほうが、結果的に短い時間で済みます。

同じ変換作業が毎月続いている、あるいは直した先から崩れてしまうという場合は、表の作り自体を見直したほうが早いことがあります。状況を伺ったうえで、直すべき場所だけをお伝えします。