月末の締め日、経理から回ってきた請求データをExcelに貼り付けて、下の行にSUM関数を置いた瞬間、合計欄に「0」が出ました。数字は全部見えています。範囲も間違っていません。それでも0。
このとき私が最初にやったのは、SUM関数を消してもう一度打ち直すことでした。結果は同じ。次にやったのは範囲の指定し直し。これも同じ。三度目にようやく、セルをクリックして画面の上のほうに目をやりました。数式バーには '128000 と表示されていました。セルの中には出ていない、小さな記号が先頭に一つ。
犯人はこれでした。所要時間、十数分。あとから振り返ると、最初の10秒でセルを1つクリックしていれば終わっていた話です。
この記事では、その「先頭のアポストロフィ」について、まず見つける、次に数える、それから件数に応じて直すという順番で整理していきます。ついでに、「直したはずなのに直らない」ときに何が起きているのか、そして「そもそも付けないようにする」にはどうすればいいのかまで踏み込みます。
最初にやること：30秒で白黒をつける
原因の説明よりも先に、判定方法から書きます。合計が0になっている、あるいは一部しか足されていないとき、次の2つを順に確認してください。
1つ目。合計されていないセルを1つクリックして、数式バーを見る。
Excelの画面上部、リボンのすぐ下にある横長の入力欄が数式バーです。セルの中には 128000 と表示されているのに、数式バーには '128000 と表示されていたら、それが答えです。セルの表示と数式バーの表示が食い違っている——これがアポストロフィの決定的なサインです。
2つ目。数字の列を上から下まで眺めて、左に寄っているセルがないか探す。
Excelは何も指定しなければ、数値を右寄せ、文字を左寄せで表示します。これは設定ではなく、Excelがそのセルの中身をどう解釈したかを表す「顔つき」のようなものです。数字の列の中に1つだけ左に張り付いているセルがあれば、そのセルは数値として扱われていません。
この2つで9割方は判別できます。ここで「該当あり」となった時点で、SUM関数を書き直す作業は完全に無駄なので、いったん手を止めてください。関数は悪くありません。
なお、この「セルの表示と数式バーを見比べる」という動作は、アポストロフィ以外の原因を探すときにも同じように効きます。合計が合わない原因はいくつも種類がありますが、入口の確認動作は共通です。もし数式バーを見ても何も付いていなかった場合は、別の原因を疑うことになります。
数式バーに記号が見当たらないのに合計が合わないときは、そもそも数字全体が文字列として保存されている可能性があります。その見分け方は SUM関数が計算されない一番多い原因は『数字の文字列化』 にまとめてありますので、こちらから当たってみてください。
そもそもアポストロフィとは何をしている記号なのか
' はアポストロフィと読みます。日本語のキーボードでは、Shiftキーを押しながら「7」のキーで入力できる記号です。
Excelにおいて、この記号をセルの先頭に置くことには、はっきりした意味があります。「このあとに続く内容は、文字として保存してください」という命令です。
たとえば、セルに 100 と入力すればExcelは数値の100として保存します。ところが '100 と入力すると、Excelは「文字の1、文字の0、文字の0」という3文字の並びとして保存します。表示されるのは 100 ですが、中身は数字ではなく文字です。
ここで少し不思議に思うかもしれません。「入力したはずのアポストロフィが、なぜセルに表示されないのか」。
これは、アポストロフィがデータそのものではなく、Excelへの指示だからです。指示は保存されますが、印刷したり表示したりする対象ではないので、セルの中には出てきません。実際に、'100 と入力したセルの文字数を =LEN(B2) で数えると、返ってくるのは4ではなく3です。アポストロフィは文字数にも入っていません。
つまりアポストロフィは、セルの中に隠れているのではなく、セルの属性として記録されていると考えるほうが実態に近い。だからこそ、目で探しても見つからないのです。
そして、Excelの計算ルールはシンプルです。数値は足せる、文字は足せない。 SUM関数は範囲の中から数値だけを拾って合計します。文字は無視します。エラーも出しません。黙って飛ばします。合計が0になったり、実際より小さい数字になったりするのは、この「黙って飛ばす」性質のためです。
なぜ、入力した覚えがないのに付いているのか
自分で ' を打った記憶がないのに付いている、というのがこのトラブルのややこしいところです。混入経路は、実務では次のパターンにほぼ集約されます。
社内システムやWebページからのコピー貼り付け
基幹システムの画面や勤怠管理システム、Webの管理画面から数字をコピーしてExcelに貼ると、貼り付け先が文字列として受け取ることがあります。このとき、Excelが「これは文字として扱う」という印を付けた結果が、先頭のアポストロフィです。コピー元の側で、桁区切りや通貨記号を含んだ形式で出力されている場合ほど起こりやすくなります。
CSVファイルを開いたとき
CSVは中身がただのテキストなので、Excelが開くときに「この列は数値」「この列は文字」と推測します。この推測が文字寄りに働いたとき、結果的に文字列として読み込まれます。同じCSVでも、ダブルクリックで開いた場合と、Excelの「データの取得」から読み込んだ場合で挙動が変わることがあるのは、この推測の仕方が違うためです。
Excelが気を利かせて付けた
先頭に0が付く番号（0012 のような社員番号）、16桁を超える長い番号、住所の一部に見える数字などを入力すると、Excelが自動的に文字列として保存することがあります。これは意地悪ではなく、0012 の先頭の0を消さないための配慮です。ただ、その配慮が計算したい列に働いてしまうと、合計が合わなくなります。
前任者が意図的に付けていた
引き継いだファイルで、番号列や品番列にアポストロフィが付いているケース。これは前任者が正しい判断でそうしている可能性が高いので、消す前に一度立ち止まる必要があります。この判断基準については後半で詳しく扱います。
直す前に「何件あるか」を数える
ここが、この記事でいちばん伝えたい部分です。
トラブルを見つけると、多くの人はすぐ直しにかかります。ただ、アポストロフィに関しては、直す前に件数を把握したほうが、結果的に速いです。3件なら手で消すのが最速ですし、300件なら手作業は現実的ではありません。どちらの方法を選ぶかは件数で決まるので、先に数えます。
COUNT関数との差で数える
空いているセルに、次の2つを並べて入力します。
=COUNT(B2:B200)
=COUNTA(B2:B200)COUNT関数は「数値として扱われているセル」だけを数えます。COUNTA関数は「空白でないセル」をすべて数えます。この2つの数が一致していれば全部数値、差があれば、その差の分だけ文字列が混ざっています。
たとえばCOUNTが183、COUNTAが199なら、16件が計算から外れているとわかります。合計が0ではなく「なんとなく少ない」ときは、この差を見ると状況がはっきりします。
どのセルが該当するかを特定する
件数がわかったら、次は場所です。データの隣の空き列に、次の式を入れて下までコピーします。
=ISNUMBER(B2)数値ならTRUE、文字列ならFALSEが返ります。FALSEの行がそのまま容疑者リストです。オートフィルターでFALSEだけを絞り込めば、対象行だけが並びます。
もう少し踏み込んで、「そのセルの先頭に何の文字が入っているのか」まで見たい場合は、CODE関数が使えます。
=CODE(LEFT(B2,1))返ってきた数字が、先頭1文字の文字コードです。半角の数字であれば48〜57の範囲に収まります。もしこれが32なら半角スペース、160なら特殊な空白文字（ノーブレークスペース）です。アポストロフィそのものは前述のとおりLEFT関数では拾えないため、この式でスペース系が検出されなければ、消去法でアポストロフィの線が濃くなる、という使い方になります。
なお、この確認をしていると「アポストロフィだと思っていたら、実はスペースだった」というケースにもよく出会います。症状はほぼ同じですが、直し方が変わります。
スペースが原因だった場合の対処は、数字の前後に『見えないスペース』が入るとSUM関数は黙って計算をやめる で手順を分けて解説しています。CODE関数で32や160が出た方はそちらをご覧ください。
件数別・直し方の選び分け
数え終わったら、いよいよ修正です。件数と、今後も同じ作業が発生するかどうかで、選ぶ手段が変わります。
数件〜10件程度：直接消す
対象が少なければ、手で消すのがいちばん確実です。
- 対象のセルをダブルクリックして編集状態にする
- カーソルを先頭に移動し、
'を削除する - Enterキーを押す
これで数値に戻ります。Enterを押した瞬間、表示が右寄せに変わるはずです。右寄せになれば成功、左寄せのままなら失敗、と見た目で判定できます。
数十件〜数百件：区切り位置で一括変換
列全体をまとめて変換する定番の方法です。
- 対象の列（列見出しをクリックして列全体、またはデータ範囲）を選択する
- 「データ」タブを開く
- 「区切り位置」をクリックする
- ウィザードが開いたら、設定は何も変えずに「完了」を押す
これだけです。本来この機能は「山田 太郎」のような1つのセルの内容を2列に分割するためのものですが、分割せずに完了させると、Excelがその列のデータを最初から読み直します。この読み直しの過程で、文字列だった数字が数値として認識されます。
注意点が1つあります。区切り位置は1列ずつしか処理できません。3列分あるなら3回繰り返すことになります。
数式で解決したい：VALUE関数
元データを書き換えたくない場合、あるいは元データが毎回上書きされる場合は、別列で変換する方法があります。
=VALUE(B2)VALUE関数は、文字列として書かれた数字を数値に変換します。B列はそのまま残し、C列にこの式を並べて、SUM関数はC列に対して使う、という構成です。
元データが差し替わっても式が生きているので、毎月同じファイルを更新する運用ではこちらが向きます。ただし、変換できない文字（不明 や - など）が混ざっていると #VALUE! エラーが出ます。それを避けたい場合は、
=IFERROR(VALUE(B2),0)のように包んでおくと、変換できないセルは0として扱われ、表全体が崩れずに済みます。何を0とみなすかは集計の意味に関わるので、金額の集計に使うときは「0にしていい数字なのか」を一度考えてから使ってください。
掛け算で一気に変換する
もう1つ、覚えておくと便利な方法があります。
- どこか空いているセルに
1と入力してコピーする - 変換したい範囲を選択する
- 右クリックして「形式を選択して貼り付け」を開く
- 演算の欄で「乗算」を選んでOKを押す
文字列の数字に1を掛けると、Excelは計算のために内部で数値に変換します。その結果がセルに書き戻されるので、まとめて数値化できます。区切り位置と違って複数列を一度に処理できるのが利点です。作業後は、コピーに使った 1 のセルを消しておくのを忘れないようにしてください。
毎月繰り返すなら：取り込み方法を変える
同じシステムから毎月同じ形式のデータを出しているなら、直す作業を毎月やるより、取り込み方を変えたほうが結果的に楽です。
CSVをダブルクリックで開くのではなく、Excelの「データ」タブから「テキストまたはCSVから」を選んで読み込むと、読み込み時に各列のデータ型を指定できます。金額の列を最初から「10進数」や「整数」に指定しておけば、文字列として入ってくること自体がなくなります。
最初に設定するのは少し手間ですが、以降の毎月の作業からこのトラブルが消えます。同じ作業を12回繰り返す予定があるなら、1回目に設定してしまうほうが得です。
「直したのに直らない」ときに起きていること
区切り位置をやった、乗算もやった、それでも左寄せのまま——という状況は珍しくありません。この場合、原因は別のところにあります。
セルの表示形式が「文字列」になっている
これが最も多いパターンです。アポストロフィを消しても、セルそのものの表示形式が「文字列」に設定されていると、入力した数字はまた文字として保存されます。バケツの底に穴が空いている状態です。
対処は、対象範囲を選択し、「ホーム」タブの数値グループにある表示形式のドロップダウンを「文字列」から「標準」に変更すること。ただし、表示形式を変えただけでは既存のデータは変換されません。標準に変えたあとで、あらためて区切り位置か乗算を実行する必要があります。順番は「表示形式を標準にする」→「区切り位置または乗算」です。逆にやると効きません。
計算方法が「手動」になっている
数式の再計算が手動設定になっていると、データが数値に戻っても合計欄の表示が古いままです。「数式」タブの「計算方法の設定」を確認し、「自動」になっているかを見てください。手動になっていた場合は、F9キーを押すか、自動に戻せば更新されます。
引き継いだファイルや、行数の非常に多いファイルでは、動作を軽くするために手動に設定されていることがあります。
スペースや通貨記号が一緒に入っている
' 1,280 円 のように、アポストロフィ以外の余計な文字も含まれているケースです。アポストロフィだけ消してもカンマや単位が残っていれば、やはり文字列のままです。
単位が一緒に入力されている場合の対処は 『100円』と入力した表は合計できない にまとめています。置換で一括処理する手順と、見た目だけ単位を付ける表示形式の使い方を紹介しています。
目に見えない制御文字が混ざっている
Webページからのコピーでは、改行コードや制御文字が数字と一緒に入り込むことがあります。これらはアポストロフィよりさらに見つけにくく、LEN関数で文字数を数えると「見えている文字数より多い」という形で気づくことになります。
このパターンに心当たりがある方は、コピー時に文字が混入するときの見分け方と直し方 を先に確認してみてください。CLEAN関数とTRIM関数を組み合わせた処理の手順を解説しています。
消していいアポストロフィ、残すべきアポストロフィ
ここまで「消す方法」を書いてきましたが、すべてのアポストロフィが敵というわけではありません。むしろ、残しておかないと壊れるデータがあります。
判断基準はひとつだけです。その列を、足し算や引き算に使うかどうか。
計算に使う列（金額、数量、単価、点数など）にアポストロフィが付いていれば、それは邪魔です。消してください。
一方で、計算に使わない列は事情が違います。
| データの種類 | 計算する？ | アポストロフィの扱い |
|---|---|---|
| 売上金額・数量・単価 | する | 消す |
| 社員番号（0011など） | しない | 残す。消すと先頭の0が消える |
| 郵便番号（0100001など） | しない | 残す |
| 電話番号（03-1234-5678） | しない | 残す |
| 品番・型番（0045-A） | しない | 残す |
| 年度（2026） | 場合による | 計算しないなら残してよい |
社員番号 0011 を数値にすると、Excelは 11 として保存します。表示上も11です。これはデータの破損です。名簿の照合やVLOOKUPの検索値として使っている場合、この瞬間に全部の突き合わせが失敗します。
だからこそ、区切り位置や乗算を「シート全体」に対してかけるのは危険です。必ず、計算に使う列だけを選択してから実行してください。 私は一度、範囲を広く取りすぎて社員番号の列まで数値化してしまい、元ファイルから取り直す羽目になったことがあります。作業前にファイルを別名で保存しておくと、こういうときに助かります。
なお、同じ列の中に数値と文字列が混ざっている、あるいは列によってデータの形式がばらばらになっている、という状態は、アポストロフィ以外の集計トラブルの温床にもなります。
表全体のデータ形式を整理したい方は、列ごとにデータ形式が違うと集計できない原因と直し方 が参考になると思います。SUMだけでなく、VLOOKUPや並び替えへの影響もあわせて整理しています。
予防：そもそも入れないための3つの習慣
直し方を覚えるのも大事ですが、入ってこなくするほうが根本的です。実務で効果があったものを3つ挙げます。
1. 貼り付けるときは「値のみ」ではなく、貼り付け先の書式を先に整える。
外部からコピーしたデータを貼る前に、貼り付け先の列の表示形式を「標準」にしておきます。文字列に設定された列に貼ると、そこで文字列化が固定されてしまうためです。
2. 貼り付け直後に、必ず1回COUNT関数で数える。
データを貼ったら、その場で =COUNT(範囲) と =COUNTA(範囲) を打って一致を確認する。10秒で終わります。集計が終わってから「合わない」と気づくより、この10秒のほうがはるかに安上がりです。
3. 集計前のファイルは、加工前に一度別名保存する。
区切り位置や乗算は、元に戻すのが面倒な操作です。加工前の状態が残っていれば、やり直しが効きます。
この3つは、アポストロフィに限らず、外部データを扱うときのトラブル全般に効きます。
セルフ診断チェックリスト
作業中のファイルで、次の項目を上から順に確認してみてください。
- 合計されていないセルをクリックし、数式バーに
'が表示されていないか確認した - 数字の列に、左寄せになっているセルがないか目視で確認した
=COUNT()と=COUNTA()の結果を比較し、差の件数を把握した=ISNUMBER()で、どの行が文字列扱いになっているか特定した- 修正対象の列が「計算に使う列」であることを確認した（社員番号などを巻き込んでいない）
- 加工前のファイルを別名で保存した
- 修正後、対象セルが右寄せに変わったことを確認した
- 修正後、SUM関数の結果が期待どおりの値になったことを確認した
- 直らない場合、セルの表示形式が「標準」になっているか確認した
- 直らない場合、計算方法が「自動」になっているか確認した
上から順に潰していけば、原因の切り分けはひととおり終わります。
もう一歩踏み込みたい方へ
ここまでは、1つのファイル・1つの列で起きるアポストロフィのトラブルを扱いました。
実務では、これが単独で起きるとは限りません。アポストロフィとスペースと単位が同じ表に同居していたり、複数シートから集めたデータで形式がばらばらだったり、月次で同じ処理を繰り返す必要があったり——といった、少し込み入った場面が出てきます。
そうした複数の原因が重なったケースへの対処や、月次処理を仕組み化するための応用パターンについては、note会員限定コンテンツのご案内 のほうでまとめています。この記事の内容で解決した方はここで十分ですが、「毎月同じところで詰まっている」という方は覗いてみてください。
まとめ
アポストロフィのトラブルは、押さえる順番さえ決めておけば、そこまで手ごわい相手ではありません。
見つける ——セルをクリックして数式バーを見る。表示が食い違っていれば確定。左寄せも有力なサイン。
数える ——COUNTとCOUNTAの差で件数を把握し、ISNUMBERで場所を特定する。ここを飛ばすと手段の選択を間違えます。
直す ——数件なら手で消す。まとまった量なら区切り位置か乗算。式で処理するならVALUE関数。毎月繰り返すなら取り込み設定を変える。
確認する ——右寄せになったか、合計が期待値になったか。直らないときは表示形式と計算方法を疑う。
守る ——社員番号や郵便番号の列は巻き込まない。範囲選択は慎重に。
この記事の冒頭で書いた「十数分かけて関数を打ち直した話」も、この順番を知っていれば1分で終わっていました。Excelのトラブルは、知識の量より確認する順番で決まる部分が大きいと感じています。
数字が合わないとき、まず疑うのは関数ではなくデータの状態。この習慣が身につくと、原因不明のまま時間だけが過ぎる場面が確実に減ります。
同じ「SUMが動かない」系のトラブルで、1つのセルの中に数字と文字が同居しているパターンについては、数字と文字列が混ざっているときの見分け方と直し方 で扱っています。合計が0ではなく「一部だけ足されている」状態の方は、そちらもあわせてご覧ください。
コメント