SUMIFで合計したのに結果が0のまま。VLOOKUPで探した値が、表のなかにちゃんとあるのに#N/Aが返る。COUNTIFで数えたのに、見るからに該当データがあるのに0件と表示される。式の書き方は何度見直しても合っているのに、結果だけが合わない。締め切り間際の集計作業でこれをやられると、本当に泣きたくなりますよね。
実はこの「式は正しいのに結果が合わない」現象、原因のほとんどは数式ではなくデータの側にあります。セルに入っている値が、見た目は同じでも内部的には別物になっているのです。数値に見えて実は文字列だったり、目に見えない空白が紛れ込んでいたり、全角と半角が混ざっていたり。SUMIFもVLOOKUPもCOUNTIFも、この「データの品質」が崩れていると、まったく同じ理由でそろって結果が狂います。
この記事では、集計・検索が合わない原因を5パターンに整理し、冒頭の早見表で「いまの症状からどの原因か」を一気に絞り込めるようにしました。さらに各原因には「見分け方」「その場で直す処置」「再発防止」の3段構えで対処法を用意しています。最後に、どのセルを疑えばいいか3ステップでたどり着ける診断フローも置きました。同僚にこっそり教えてもらう感覚で、上から順に読み進めてください。
対応バージョン: Microsoft 365 / Excel 2021 / 2019 / 2016 / Excel for the Web（Web版）で確認しています。原因と関数の挙動はどのバージョンでも共通ですが、緑の三角（エラーインジケーター）や「区切り位置」機能はデスクトップ版のみで、Web版では使えません。その都度本文で補足します。
【早見表】症状×関数×原因5パターン一覧
まずは、いま起きている症状を下の表で探してください。横軸に「症状」、縦軸に「使っている関数」を取り、交わるマスに疑うべき原因コード（A〜E）を入れてあります。原因コードからそのまま該当セクションへジャンプできます。
| 関数＼症状 | 合計・件数が0になる | #N/A（見つからない） | 部分的にしかカウント・集計されない |
|---|---|---|---|
| SUMIF | A・B・C | ― | B・C・D |
| COUNTIF | A・B・C | ― | B・C・D |
| VLOOKUP / XLOOKUP | ― | A・B・C・D | B・C・D |
| すべての関数共通 | E（結果が古いまま） | E | E |
原因コードの意味は次のとおりです。
| コード | 原因 | ひと言で言うと | 該当セクション |
|---|---|---|---|
| A | 数値が文字列として保存されている | 数字に見えて実は文字 | 原因① |
| B | 前後に見えない空白が混入している | 余計なスペースで別物扱い | 原因② |
| C | 全角・半角が混在している | 「A」と「Ａ」は別の文字 | 原因③ |
| D | 似ている文字が混入している | 「○」と「〇」、「ー」と「―」 | 原因④ |
| E | Excelが手動計算モードになっている | 計算結果が更新されていない | 原因⑤ |
表を見ると分かるとおり、SUMIF・COUNTIF・VLOOKUPは別々の関数なのに、つまずく原因（A〜D）はほぼ共通です。これらはすべて「セルの値どうしを照合する」という同じ仕組みで動いているため、照合のもとになるデータが汚れていると、関数の種類に関係なくそろって失敗します。だからこそ、関数を疑う前にデータを疑うのが解決への近道です。
特に多いのが原因①（文字列数値）と原因②（空白）の2つです。心当たりがなければ、まずこの2つから確認してみてください。
どの原因か3ステップで絞り込む（診断フロー）
早見表を見ても原因を絞れないときは、次の3ステップを上から順にたどってください。1つのセルを対象に確認していくだけで、原因①〜④のどれかにたどり着けます（原因⑤の手動計算は、先にF9を押して結果が変われば即判定できるので、最初に除外しておくとスムーズです）。
ステップ1：セルの配置（左揃え）でデータ型を疑う
まず、合わないデータが入っているセルの配置を見ます。書式を変えていないのに数字が左揃えなら、文字列数値（原因①）の可能性が高いです。確証を得るにはISNUMBERを使います。
=ISNUMBER(A2)FALSEが返れば文字列数値です。原因①へ進んでください。数値が右揃えでTRUEなら、データ型は正常なので次のステップへ進みます。
ステップ2：LENで文字数の異常を確認
次に、文字数を確認します。想定より多ければ、空白（原因②）や余計な文字が混入しています。
=LEN(A2)-LEN(TRIM(A2))この値が0より大きければ、除去できる半角スペースが入っています。0なのに合わない場合は、全角スペースやノーブレークスペースの可能性があるため、=LEN(A2)の素の文字数を「正しいはずの文字数」と見比べてください。差があれば原因②（空白）へ。文字数が想定どおりなら、最後のステップへ進みます。
ステップ3：UNICODEで文字コードを確認
文字数は合っているのに照合できない。ここまで来たら、原因は全角・半角（原因③）か似た文字（原因④）に絞られます。怪しい文字を1文字切り出して、文字コードを確認します。
=UNICODE(MID(A2,1,1))=EXACT(A2,B2)正しいはずのセルとUNICODEの値を比べて違えば、全角・半角か似た文字の不一致です。EXACTで2セルを厳密比較し、FALSEなら原因③または④へ進んでASC/JISまたはSUBSTITUTEで直してください。迷ったら、左揃え→LEN→UNICODEの順、とだけ覚えておけば十分です。
それでは原因を1つずつ見ていきましょう。
原因①：数値が文字列として保存されている
いちばん多いのがこれです。セルに「1000」と入っているのに、Excelはそれを「数値の1000」ではなく「文字としての”1000″」として持っている状態です。会計システムやWebからコピーした数字、CSVを取り込んだデータでよく起こります。
SUMIFやSUM、COUNTIFの数値条件は「数値」を前提に動くため、中身が文字列だと合計の対象から外れて0になります。VLOOKUPでも、検索する側が数値・探される側が文字列（またはその逆）だと、見た目が同じ「1000」でも一致せず#N/Aになります。Microsoftの公式でも、検索値を扱うときは「最初の列のデータがテキストとして保存されていないこと」を確認するよう明記されています。
見分け方：セルの左揃え・緑の三角・ISNUMBER確認
最初の手がかりはセル内での配置です。Excelでは数値は自動で右揃え、文字列は左揃えで表示されます。書式を何も変えていないのに数字が左に寄っていたら、文字列数値を疑ってください。
デスクトップ版なら、該当セルの左上に緑の三角（エラーインジケーター）が出ることもあります。ただしこの緑の三角はデスクトップ版だけの機能で、Excel Web版では表示されません。
確実に判定したいときはISNUMBER関数を使います。
=ISNUMBER(A2)A2が本物の数値ならTRUE、文字列数値ならFALSEが返ります。範囲をまとめて見たいときは、空いた列に下までコピーすれば、どのセルが文字列なのか一目で分かります。
=ISNUMBER(A2)
=ISNUMBER(A3)
=ISNUMBER(A4)FALSEが並んでいたら、それが0や#N/Aの正体です。
直し方：VALUE関数・貼り付け形式・エラーインジケーター
文字列数値を本物の数値に変えるには、VALUE関数を使うのが最も確実で、デスクトップ版・Web版どちらでも使えます。
=VALUE(A2)これで"1000"という文字列が、計算に使える数値1000に変換されます。変換した列をコピーして、元の列に「値貼り付け」で戻せば、以降の集計が正しく動きます。
デスクトップ版限定の手早い方法もあります。緑の三角が出ているセル範囲を選択し、表示されるエラーインジケーターのボタンから「数値に変換」をクリックするだけです。複数セルを一括で直せます。また「データ」→「区切り位置」→そのまま「完了」でも数値化できますが、こちらもデスクトップ版のみで、Web版では使えません。Web版で作業しているならVALUE関数が基本の手段になります。
変換方法をもっと詳しく知りたい場合は、Excelで文字列を数値に変換する4つの方法で、貼り付け形式や区切り位置の手順まで個別に解説しています。
再発防止：入力書式の統一とコピペルール
再発を防ぐには、データの入口をそろえるのが効果的です。
- 数値を入れる列は、あらかじめ表示形式を「標準」または「数値」に設定しておく
- Webやシステムからコピーするときは、一度メモ帳などのプレーンテキストを経由してから貼ると、余計な書式が落ちる
- CSV取り込み時は、取り込み後に必ず
ISNUMBERで1セルだけ抜き取りチェックする習慣をつける
なお、逆に「数値を文字列の見た目（桁区切りやゼロ埋め）で表示したい」場合は、データ自体を文字列に変えるのではなくTEXT関数で表示だけ整えるのが安全です。詳しくはTEXT関数の使い方を参照してください。集計に使う元データはあくまで数値のまま保つのがコツです。
原因②：前後に見えない空白が混入している
「東京」と「東京 」（末尾に空白）は、人間の目には同じでもExcelには別の文字列です。VLOOKUPはこの2つを一致と見なさず#N/Aを返し、SUMIFやCOUNTIFも条件に一致しないものとして集計対象から外します。空白はコピペや手入力で簡単に紛れ込むうえ、画面上はまったく見えないため、最も気づきにくい原因です。
Microsoftの公式でも、#N/Aエラーの主要因として「セル内の余分なスペース」が挙げられ、COUNTIFの注意書きにも「先頭・末尾のスペースで予期しない結果になる」と記載されています。
見分け方：LENで文字数を確認する
空白の有無はLEN関数（文字数を数える関数）で炙り出せます。次の式を使ってください。
=LEN(A2)-LEN(TRIM(A2))TRIMは半角スペースを取り除く関数なので、「元の文字数」と「空白を取った文字数」の差を見れば、除去できる半角スペースが何個入っているか分かります。結果が0より大きければ、そのセルには余計な半角空白があります。
=LEN(A2)単純にLEN(A2)だけでも、想定より文字数が多ければ何かが混入していると判断できます。LEN関数そのものの使い方はLEN関数の使い方で詳しく解説しています。
直し方：TRIM・SUBSTITUTE+CHAR(160)で除去
定番はTRIM関数です。
=TRIM(A2)ただし、ここに重要な落とし穴があります。TRIMが除去するのは半角スペース（文字コード32）だけです。Microsoft公式も「7ビットASCIIスペース文字（値32）のみを対象」と明記しており、全角スペースはTRIMでは消えません。これを知らずに「TRIMしたのに直らない」とハマる人が非常に多いところです。
全角スペースが原因の場合は、先にSUBSTITUTEで全角スペースを半角に置き換えてからTRIMを掛けます。
=TRIM(SUBSTITUTE(A2," "," "))SUBSTITUTE(A2," "," ")の部分で全角スペース（” ”）を半角スペース（” “）に変換し、それをTRIMで除去する流れです。
もう一つ厄介なのが、Webサイトからコピーしたときに混入するCHAR(160)（ノーブレークスペース、改行されない特殊な空白）です。これもTRIMでは除去できません。次のように個別に消します。
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))印刷できない制御文字（改行コードなど、文字コード0〜31）が原因ならCLEAN関数が有効です。
=CLEAN(A2)ただしCLEANも万能ではなく、対象は文字コード0〜31の制御文字だけです。CHAR(160)のノーブレークスペースは文字コードが160なのでCLEANでは消えません。「TRIMでもCLEANでも直らない空白」に遭遇したら、CHAR(160)をSUBSTITUTEで個別除去する、と覚えておくと迷いません。空白の見分け方や5パターンの対処はExcelで空白セルがカウントされない5パターンと対処法にまとめてあります。
再発防止：Webコピペ禁止・入力規則の活用
- Webやメールからの直接コピペは、空白混入の最大の発生源です。一度メモ帳などのプレーンテキストに貼ってから持ってくると、ノーブレークスペースや制御文字が落ちます
- マスタとして使う表（VLOOKUPの参照先など）は、取り込み時に全列へまとめて
TRIM+SUBSTITUTEを掛けてから値貼り付けしておく - キーになる列にはデータの入力規則を設定し、想定外の文字数・形式が入らないようにする
原因③・④：全角半角や似た文字が混在している（見た目が同じでも別物）
商品コードや型番が「同じはずなのに一致しない」とき、犯人は文字そのものの違いです。全角・半角の混在（原因③）と、見た目がそっくりな別文字の混入（原因④）は、どちらも照合を静かに壊します。原因は近いので、続けて見分け方と直し方を押さえましょう。
原因③：全角・半角の混在
「Ａ１２３」（全角）と「A123」（半角）は、人間には同じに見えてもExcelにとっては完全に別の文字列です。商品コードや型番、電話番号などで全角と半角が混ぎると、VLOOKUPは一致せず#N/A、SUMIF・COUNTIFも条件に合わずカウント漏れを起こします。特に複数人で入力する台帳や、IMEの設定がバラバラな環境で起こりがちです。
見分け方：EXACT関数で厳密比較
「同じはずなのに一致しない」2つのセルを突き合わせるにはEXACT関数が便利です。
=EXACT(A2,B2)EXACTは大文字小文字も全角半角も区別して厳密に比較し、完全一致ならTRUE、少しでも違えばFALSEを返します。見た目が同じなのにFALSEが出たら、全角・半角の違い（あるいは後述の似た文字や空白）が潜んでいます。
直し方：ASC/JIS関数で統一
全角・半角の不一致は、どちらかに統一すれば一発で解決します。半角にそろえるならASC関数、全角にそろえるならJIS関数を使います。
=ASC(A2)ASCは全角の英数字・カタカナを半角に変換します。「Ａ１２３」→「A123」のようにそろうので、コードや型番は半角に統一するケースが多いです。
=JIS(A2)JISは逆に半角の英数字・カタカナを全角に変換します。検索する側（数式）と探される側（マスタ）の両方に同じ関数を掛けて、土俵をそろえるのがポイントです。
注意点として、ASCは全角スペース・漢字・ひらがなは変換しません。またJISを使うと半角スペースが全角スペースに変わるので、空白問題（原因②）と組み合わさっているときは処理の順番に気をつけてください。なおJIS関数はExcel専用で、Google Sheetsには存在しません（この記事はExcelの話です）。
再発防止：入力規則でIMEモードを固定
- 商品コードや型番の列は、データの入力規則で日本語入力（IME）を「半角英数」に固定しておくと、全角混入をそもそも防げます
- 既存の台帳には、取り込み時に
ASCまたはJISを全体へ掛けて一度そろえてしまう - 複数人で更新する表は、入力ルールを「英数字は半角」と明文化して共有する
原因④：似ている文字の混入（○と〇、ーと―）
全角・半角よりさらに厄介なのが、見た目はほぼ同じなのに文字コードが違う文字です。代表例が次の3組です。
- 丸記号の「○」（白丸）と「〇」（漢数字のゼロ）
- 長音の「ー」（音引き）と「―」（ダッシュ）と「‐」（ハイフン）
- マイナス記号の「-」（半角ハイフン）と「−」（全角マイナス）と「‐」（ハイフン）
これらは画面上では区別がほとんどつきませんが、Excelには別の文字です。VLOOKUPで「データーセンター」を探しているのに台帳が「データ―センター」（長音がダッシュ）になっていると、当然#N/Aになります。
見分け方：CODEで文字コードを確認
文字コードを直接確認すれば、似た文字を見破れます。CODE関数は最初の1文字の文字コードを返します。
=CODE(A2)ただしCODEはANSI（環境依存の文字セット）のコードを返すため、似た記号の判別にはより広範囲をカバーするUNICODE関数のほうが確実です。
=UNICODE(A2)一致しないはずの2セルでそれぞれUNICODEを取り、値が違えば「見た目は同じでも別の文字」と確定できます。文字列の途中の文字を調べたいときは、MIDで1文字を切り出してからUNICODEに渡します。
=UNICODE(MID(A2,3,1))これで3文字目の文字コードが分かります。「ここの長音だけ怪しい」というときに、ピンポイントで犯人を特定できます。
直し方：SUBSTITUTE で正規文字に置換
似た文字はSUBSTITUTEで正しい文字に置き換えます。たとえばダッシュ「―」を長音「ー」に直すなら次のとおりです。
=SUBSTITUTE(A2,"―","ー")複数の似た文字をまとめて直したいときは、SUBSTITUTEを入れ子にします。
=SUBSTITUTE(SUBSTITUTE(A2,"〇","○"),"―","ー")内側から順に処理され、まず「〇」を「○」に、次に「―」を「ー」に置換します。直したい文字の種類が増えても、入れ子を重ねれば対応できます。置換後の列を値貼り付けでマスタに反映すれば、以降の照合が安定します。
再発防止：リスト入力で自由記述を禁止
- 似た文字の混入は「手入力で人によって変換候補が違う」ことが原因です。選択肢が決まっている列は、データの入力規則のリストで選ばせ、自由記述をやめると根本から防げます
- 既存データは、よく混入する似た文字（○／〇、ー／―）を一覧化し、取り込み時に
SUBSTITUTEで一括正規化するテンプレートを用意しておく
原因⑤：Excelが手動計算モードになっている
ここまでの①〜④はデータの中身が原因でしたが、原因⑤だけは設定の問題です。Excelの計算方法が「手動」になっていると、データを直しても数式を入れ直しても、画面の結果が古いままで更新されません。「直したはずなのに合わない」と感じる場合、実は値は正しく、表示だけが追いついていないケースがあります。
マクロ付きファイルを開いたときや、重い表で動作を軽くするために誰かが切り替えたまま、という形で手動モードになっていることがよくあります。
見分け方：数式タブ→計算方法の確認
確認は数式タブから行います。
- リボンの「数式」タブを開く
- 右端の「計算方法の設定」をクリック
- 「手動」にチェックが入っていれば、それが原因です
簡単な切り分けとして、適当なセルでF9キーを押してみて、それまで変わらなかった結果が一斉に更新されたら、手動計算モードで間違いありません。
直し方：自動計算に切り替え・F9で即時再計算
直し方は2つです。
- 設定を「自動」に戻す：「数式」タブ→「計算方法の設定」→「自動」を選びます。これ以降は値を変えるたびに自動で再計算されます
- その場で再計算する：いますぐ結果を更新したいだけなら
F9キーを押します。Shift+F9は現在のシートだけ、Ctrl+Alt+F9はすべてのシートを強制的に再計算します
ふだんは「自動」で運用するのが安全です。設定を戻したうえで、念のためF9で一度全体を更新しておくと安心です。
なお、計算されない症状は手動計算モード以外にも、文字列書式・先頭のアポストロフィ・数式表示モードなど複数の原因があります。当てはまらないときはExcelの数式が計算されない7つの原因と直し方で原因の全体像を確認してください。
よくある質問（FAQ）
SUMIF・COUNTIF・VLOOKUPで原因が同じなのはなぜ？
これらの関数はすべて「セルの値どうしを照合する」という共通の仕組みで動いているからです。SUMIFは条件と一致する値を、COUNTIFは数える対象を、VLOOKUPは検索値を、それぞれ「比較」して合致したものを処理します。照合のもとになる値が文字列数値・空白・全角半角・似た文字で汚れていると、どの関数でも「一致しない」と判定され、そろって0や#N/Aになります。関数を疑う前にデータを疑うべき理由がここにあります。
XLOOKUPでも同じ問題は起きる？
起きます。XLOOKUPはVLOOKUPより新しく高機能な関数ですが、「値を照合する」仕組み自体は同じです。検索する側が数値・探される側が文字列だったり、空白や全角半角が混在していれば、XLOOKUPでも一致せず、結果（既定では#N/A、または指定した「見つからない場合」の値）が返ります。XLOOKUPに乗り換えてもデータ品質の問題は解決しないので、この記事の対処はそのまま有効です。なおXLOOKUPはMicrosoft 365 / Excel 2021以降とWeb版で使え、古い版では#NAME?になります。
一度直したのにまた合わなくなった理由は？
多くは「元データが更新されたとき、新しく入ったデータに同じ汚れが再混入した」ためです。VALUEやTRIMで直すのは、あくまでその時点のデータです。マスタや集計元の表に毎月データを継ぎ足す運用だと、追加分にまた文字列数値や空白が紛れ込みます。だからこそ各原因の「再発防止」が重要で、入力規則やコピペルールで入口をふさいでおくと、直し直しの無限ループから抜け出せます。
TRIMで空白を消したのに、まだ集計が合いません
TRIMが除去するのは半角スペース（文字コード32）だけだからです。全角スペースやノーブレークスペース（CHAR(160)）はTRIMでは消えません。=TRIM(SUBSTITUTE(A2," "," "))で全角スペースを、=TRIM(SUBSTITUTE(A2,CHAR(160)," "))でノーブレークスペースを処理してください。それでも残る制御文字はCLEANで除去できますが、CLEANも対象は文字コード0〜31だけで、CHAR(160)は消えない点に注意が必要です。
Web版のExcelを使っています。緑の三角や区切り位置が見当たりません
エラーインジケーター（緑の三角）と「区切り位置」機能は、デスクトップ版のExcel専用です。Excel for the Web（Web版）には用意されていません。Web版で文字列数値を直すときはVALUE関数を使ってください。ISNUMBER・LEN・TRIM・SUBSTITUTE・ASC・JIS・UNICODEはWeb版でも使えるので、この記事の確認・修正のほとんどはWeb版だけで完結できます。
どの原因か当たりがつきません。最短の確認順は？
まずF9を押して結果が変わるか見てください（変われば原因⑤）。変わらなければ、合わないセルで「左揃えか→ISNUMBER」（原因①）、「LENで文字数の異常」（原因②）、「UNICODEまたはEXACTで文字の違い」（原因③・④）の順に確認します。この記事の診断フローがそのままチェックリストになっているので、上から順にたどれば必ずどれかに行き着きます。
まとめ
Excelで集計や検索が合わないとき、犯人は数式ではなくデータであることがほとんどです。SUMIF・COUNTIF・VLOOKUPがそろって0や#N/Aを返すのは、これらが同じ「値の照合」で動いているからで、照合のもとになるデータが汚れていれば関数の種類を問わず失敗します。
原因は5つに整理できます。①数値が文字列として保存されている、②前後に見えない空白が混入している、③全角・半角が混在している、④似ている文字が混入している、⑤手動計算モードになっている。冒頭の早見表で症状から原因を絞り、各セクションの「見分け方→直し方→再発防止」で対処してください。
迷ったら診断フローの3ステップ（左揃え確認→LEN→UNICODE）を上から順にたどれば、原因にたどり着けます。そして直したあとは「再発防止」で入口をふさぐこと。これで「式は合っているのに結果が合わない」という消耗戦から、すっきり卒業できます。
