「関数は合っているのに、なぜか合計が合わない」——このシリーズも、今回で5回目になります。
私自身、Excelを使い始めたばかりの頃は、計算が合わないたびに「関数の書き方を間違えたのかな」と思い込み、何度も関数を書き直していました。ですが、Excelを使い続けているうちに気づいたのは、トラブルの多くは関数ではなく「データの状態」に原因があるということです。
社内システムから出力した売上データをExcelに取り込んで集計しようとしたとき、どうしても合計が0のままで、原因が分からず途方に暮れたことがあります。数字はきちんと表示されているのに、なぜか計算されない。何度もセルを見比べて、ようやく気づいたのが、数字の先頭に付いていた小さな記号でした。セルの表示上はまったく見えていなかったので、それが原因だと分かったときには、思わずため息が出てしまいました。
これまで、数字が文字列になっているケースや、スペースが入っているケースなど、SUM関数が計算されない原因を1つずつ研究してきました。
今回のテーマは「アポストロフィ（’）が付いている」ケースです。少し聞き慣れない言葉かもしれませんが、これもExcelではよくある原因の一つです。しかも厄介なのが、見た目ではほとんど分からないという点です。では、一緒に研究していきましょう。
よくある状況
まずはよくある例を見てみます。次のような売上表を作ったとします。
セルの中の表示では、普通の数字に見えるはずです。そして合計を出すために、次の関数を入力します。
=SUM(B2:B4)普通なら結果は600になります。しかし実際には、合計が0になる、正しく計算されない、SUM関数が反応しない、といった現象が起きることがあります。
関数も範囲も合っているのに、なぜか計算されない。そんなときに起きている原因が、今回のテーマです。
原因：アポストロフィは「文字列として扱う」という指示
Excelでは、100 は数値データです。しかし '100 と入力すると、Excelではこれを文字列データとして扱います。
この ' という記号を「アポストロフィ」と呼びます。Excelでは、この記号がセルの先頭に付くと、「このセルの中身は文字列として扱ってください」という指示になります。
Excelの基本ルールは、これまでの回でもお伝えしてきた通り、数値は計算できる、文字列は計算できない、というものです。そのため、アポストロフィが付いたセルが範囲に含まれていると、=SUM(B2:B4) と入力しても、そのセルは合計から外れてしまうのです。
原因の根っこは、これまで取り上げてきた「文字列になっている」「全角数字」「スペース」「単位付き」と、まったく同じです。数字のように見えても、Excelが数値として扱ってくれなければ計算されない。今回のアポストロフィは、その中でもとくに「文字列にしてください」という指示が、記号として明確に付いているケースだと言えます。
なぜアポストロフィが入るのか
「自分で入力した覚えはないのに、なぜか付いている」というケースが、実はとても多いです。Excelトラブルを研究していると、原因は主に次の3つに分かれます。
① Webやシステムからコピーした
Webサイトや社内システムからデータをコピーして貼り付けると、文字列データとして扱われることがあります。その際、Excelが自動的にアポストロフィを付けて、文字列であることを保持しようとする場合があります。コピペしたデータが文字列になる問題は、原因①でも取り上げましたが、アポストロフィはその「文字列化」がより具体的な形で現れたもの、と考えると分かりやすいと思います。
② CSVデータを読み込んだ
CSVファイルを読み込むと、データが文字列として扱われることがあります。その際、Excelが内部的に '100 のように処理することがあります。とくに、他システムから出力したCSVには、この形が紛れ込みやすい傾向があります。
③ Excelが自動で付けた
先頭に0がある数字（社員番号や郵便番号など）、桁数の非常に長い数字、特定の形式のデータなどを扱うとき、Excelが自動的に文字列扱いにすることがあります。これは、たとえば「0012」という番号の先頭の0が消えてしまわないように、Excelが気を利かせてくれている、という側面もあります。ただし、その結果として計算ができなくなることがあるため、意図しないところで付いていた場合には対処が必要です。
なお、アポストロフィは「悪者」というわけではありません。むしろ、社員番号や郵便番号、電話番号のように「計算する必要がなく、桁をそのまま保ちたい数字」を扱うときには、アポストロフィを付けて意図的に文字列にしておくことが正しい使い方になります。問題なのは、「計算したい数字」にアポストロフィが付いてしまっているケースです。つまり、アポストロフィそのものが悪いのではなく、そのセルを計算に使いたいのかどうか、という目的とのミスマッチが問題なのだと理解しておくと、対処すべきかどうかの判断がしやすくなります。
見分けるポイント
アポストロフィは、セルの中では表示されないことが多く、非常に気づきにくいのが特徴です。そんなときは、次のポイントを確認してみてください。
① 数式バーを見る
セルをクリックすると、画面上部に数式バーが表示されます。ここを見ると、セルの表示では見えなかった '100 のように、先頭に記号が付いていることが確認できます。セルの中の表示と、数式バーの表示が食い違っているときは、アポストロフィを疑う大きな手がかりになります。この「セルと数式バーを見比べる」という確認方法は、アポストロフィ以外のトラブルを見つけるときにも役立つので、ぜひ習慣にしておきたいところです。
② 数字なのに左寄せになっている
Excelには、数値は右寄せ、文字列は左寄せで表示される、というルールがあります。アポストロフィが付いていると文字列扱いになるため、数字なのに左寄せになります。これは、これまでの回でも繰り返し登場した、非常に分かりやすいサインです。表全体を見渡して、揃っていない数値がないかを確認してみてください。
③ ISNUMBER関数で確認する
確実に判断したい場合は、別のセルに次の関数を入力して確認する方法があります。
=ISNUMBER(B2)対象のセルが数値であればTRUE、アポストロフィなどで文字列扱いになっていればFALSEが返ってきます。範囲が広い場合は、隣の列にこの式を並べておくと、どのセルが文字列になっているかを一目で把握できます。
解決方法
いちばん簡単な方法：手動で削除する
対象が数件であれば、一番シンプルなのは手動で削除することです。
- セルをダブルクリックします。
- 先頭の
'を削除します。 - Enterキーを押します。
これだけで '100 が 100 になり、数値データとして扱われるようになります。すると =SUM(B2:B4) も正しく計算されるようになります。
データが多い場合：区切り位置機能を使う
データが大量にある場合、1つずつ手動で削除するのは大変です。その場合は、区切り位置機能を使う方法があります。
- 対象の列を選択します。
- 「データ」タブをクリックします。
- 「区切り位置」を選びます。
- 何も設定を変えずに、そのまま「完了」を押します。
これだけで、文字列データがまとめて数値に変換されることがあります。もともとは文字列を列に分割するための機能なのですが、この「そのまま完了」という操作をすると、Excelがデータを読み直して数値として認識し直してくれる、という便利な使い方です。業務でExcelを使う人は、覚えておくと重宝します。一度に大量のセルを変換できるため、手作業で1つずつ直すよりも、はるかに短い時間で作業を終えられます。
掛け算を使ってまとめて変換する
もう一つ、以前の回でもご紹介した方法があります。空いているセルに「1」を入力してコピーし、変換したい範囲を選択したうえで、「形式を選択して貼り付け」から「乗算」を選ぶ方法です。文字列の数字に1を掛けることで、Excelが自動的に数値として計算し直してくれます。区切り位置機能とあわせて、どちらか使いやすい方を覚えておくと安心です。
研究員メモ
Excelのトラブルを研究していると、よく感じることがあります。それは、見た目では判断できない、ということです。今回のアポストロフィも、その一つです。
人間の目には 100 も '100 も同じに見えます。しかしExcelの中では、まったく別のデータです。だからこそ、「関数は合っているのにおかしい」と思ったときは、関数ではなくデータの種類を疑う。この習慣を持つことが、Excelトラブル解決の一番の近道です。
①の文字列、②の全角数字、③のスペース、④の単位、そして今回⑤のアポストロフィ。ここまで5回にわたってお伝えしてきましたが、原因はどれも「数値であるべきデータが、文字列になっている」という一点に行き着きます。症状の名前を1つずつ暗記するのではなく、この共通のルールとして理解しておけば、初めて出会うトラブルにも応用が利くようになります。
まとめ
今回のテーマは「SUM関数が動かない理由⑤｜アポストロフィ（’）が付いている」でした。チェックポイントはこちらです。
- 数式バーを見て、先頭に
'が付いていないか - 数字なのに左寄せになっていないか
- コピペやCSVから持ってきたデータではないか
この3つを、まず確認してみてください。Excelでは、見た目ではなくデータの種類が重要です。この考え方を覚えておくと、トラブル解決がかなり早くなります。
アポストロフィは、見えないぶん最初は戸惑うかもしれませんが、「数式バーを見る」という習慣さえ身につけてしまえば、すぐに見抜けるようになります。焦らず、一つずつ確認していきましょう。次回は、数字と文字が同じセルに混ざってしまう「数字と文字列が混ざっている」ケースを取り上げる予定です。これもまた、SUM関数が動かなくなる原因の一つです。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数やトラブルについても実務目線で整理していますので、あわせてご覧ください。
コメント