「ちゃんと数字が入っているのに、なぜか計算できない」——このシリーズも、今回で14回目になります。
私自身、Excelを使い始めたばかりの頃は、SUM関数を使っているのに合計が合わなかったり、CSVを開いた途端にExcelがおかしくなったように感じたりと、原因の分からないトラブルに何度も悩まされてきました。初心者のころは「関数の書き方が間違っているんだ」と思い込み、何度も式を打ち直していましたが、実際には、原因は関数ではなくCSVの取り込み方にあることが非常に多かったのです。
以前、会計ソフトから出力した売上データのCSVをExcelで開き、月次の合計を出そうとしたとき、SUM関数の結果がなぜか0のままだったことがあります。画面にはきれいに数字が並んでいるのに、まったく合計されない。原因を探ると、金額の列がすべて文字列として読み込まれていたのです。CSVを開いてそのまま集計しただけなのに、と、しばらく納得がいきませんでした。
これまで①〜⑬では、文字列・空白・改行・文字コードなど、さまざまな「見えない違い」を研究してきました。今回のテーマは「CSV取り込みで文字列になる」ケースです。社内システム、会計ソフト、販売管理ソフト、ネットショップ、勤怠システムなどから出力したCSVデータで、本当によく発生するトラブルです。そして厄介なのが、見た目では数字に見えるという点です。では、一緒に研究していきましょう。
よくある状況
まずはよくある例を見てみます。CSVファイルをExcelで開くと、次のようなデータが表示されたとします。
見た目としては、完全に数字です。そこで合計を出すために、次の関数を入力します。
=SUM(A2:A4)普通なら「600」になるはずです。ところが実際には、正しく計算されない、合計が0になる、といったことが起きることがあります。
「数字なのに、なぜ？」——ここでよく起きているのが、今回のテーマです。
原因：CSVでは文字列として読み込まれることがある
ここが、Excel初心者にとってかなり分かりにくいポイントです。
CSVは、「ただのデータの並び」を保存する、とてもシンプルな形式です。つまりCSV自体には、「これは数字」「これは文字列」「これは日付」という情報が含まれていません。そのため、Excelで開くときに、Excelが自動でデータの種類を判断する必要があります。そして、この自動判定が、うまくいかないことがあるのです。
たとえば「100」というデータがあっても、Excel側では「数値として読む」か「文字列として読む」かの2パターンがあります。もし文字列として読み込まれると、見た目は数字でも、Excelの内部では「文字列」として扱われます。すると、SUMで合計できない、AVERAGEで平均が出ない、COUNTIFで正しく数えられない、といった問題が発生します。
原因の根っこは、以前の記事で取り上げた「数字が文字列になっている」という問題とまったく同じです。数字が文字列になっているケースの見分け方は、CSV由来のトラブルでもそのまま役立ちますので、あわせて確認しておくと理解が深まります。
見分けるポイント
CSV由来の文字列データには、次のような特徴があります。
① 数字なのに左寄せになっている
Excelでは通常、数値は右寄せ、文字列は左寄せで表示されます。つまり、数字なのに左寄せになっていたら要注意です。CSVを開いた直後に、数値の列が左に寄っていないかを、まず確認してみてください。
② 緑の三角が出る
セルの左上に、小さな緑の三角が表示されることがあります。これは、Excelからの「数値が文字列として保存されています」という警告です。かなり重要なサインなので、見つけたら文字列化を疑いましょう。
③ SUM関数で合計できない
もっとも分かりやすい症状です。=SUM(A2:A10) を使っているのに、合計が0になる、一部しか集計されない、といった現象が起きます。
④ ISNUMBER関数で確認する
確実に判断したい場合は、別のセルに =ISNUMBER(A2) と入力してみてください。数値であればTRUE、文字列であればFALSEが返ってきます。範囲が広い場合は、隣の列にこの式を並べておくと、どのセルが文字列化しているかを一目で把握できます。
なぜ起きるのか
研究員として、現場でよく見る原因は主に次の3つです。
① CSV出力側の仕様
システムによっては、最初からデータを「文字列」として出力していることがあります。とくに、商品コード、郵便番号、顧客番号など、計算する必要のない番号は、文字列扱いで出力されやすいです。
② 先頭ゼロを保持したい
たとえば「00123」というコードは、Excelが数値として扱うと、先頭の0が消えて「123」になってしまいます。これを防ぐために、CSV側であえて文字列扱いにしているケースがあります。この場合、先頭ゼロを保つために、わざとアポストロフィが付けられていることもあります。アポストロフィが付いているケースについては、以前の記事でくわしく取り上げていますので、あわせて確認してみてください。
③ Excelの自動判定のズレ
Excelの自動判定は便利ですが、完璧ではありません。とくにCSVでは、数字・文字列・日付の判断を間違えることがあります。同じシステムから出力したデータでも、その時々の内容によって、判定が変わってしまうこともあります。
解決方法
方法①：「数値に変換する」を使う
一番簡単なのは、Excelの警告機能を使う方法です。文字列化しているセルを選択すると表示される、黄色い警告マーク（！マーク）をクリックし、「数値に変換する」を選びます。これだけで、文字列が数値に変換されます。初心者の方には、まずこの方法がおすすめです。
方法②：VALUE関数でまとめて直す
データ量が多い場合は、関数を使うほうが便利です。おすすめは、VALUE関数です。
=VALUE(A2)VALUE関数は、文字列を数値に変換する関数です。「”100″」という文字列を「100」という数値に変換してくれるので、これでSUM関数も正常に動くようになります。
方法③：区切り位置で一括変換する
範囲全体をまとめて変換したい場合は、区切り位置機能も便利です。対象の列を選択し、「データ」タブから「区切り位置」を開いて、何も設定を変えずにそのまま「完了」を押します。これだけで、文字列だった数字が、まとめて数値として認識し直されることがあります。関数を使わずに一気に直せるため、大量データのときに重宝します。
まとめて修正する手順
VALUE関数で大量のデータを直す場合は、次の流れがおすすめです。まず、新しい列にVALUE関数を入力し、下までコピーします。次に、その列全体をコピーし、「形式を選択して貼り付け」から「値」を選んで貼り付けます。最後に、元データの列を削除すれば完成です。関数のままだと、元の列を消したときに結果も消えてしまうため、「値として貼り付ける」という一手間が大切です。
CSVを開くときのひと工夫
そもそも文字列化を防ぎたい場合は、CSVの開き方を工夫する方法もあります。CSVファイルをダブルクリックで開くのではなく、Excelの「データ」タブから「テキストまたはCSVから」を使って読み込むと、各列のデータ形式を自分で指定しながら取り込むことができます。この方法なら、数値にしたい列は数値、文字列で保ちたい列は文字列、と明示的に指定できるため、Excelの自動判定に振り回されずに済みます。
とくに、先頭ゼロを保ちたいコード列と、計算に使いたい数値列が混在しているようなCSVでは、この読み込み方が効果的です。毎回同じCSVを扱う場合は、一度この手順を覚えておくと、その後の作業がぐっと楽になります。
研究員メモ
ここで、とても大切な考え方をお伝えします。それは、Excelでは**「見た目が数字」＝「数字」ではない**、ということです。人間には数字に見えても、Excel内部では文字列というケースは、本当に多いのです。
だからこそ、左寄せ・緑の三角・計算できない、この3つを見たら、「文字列化しているかも？」と疑う習慣が重要です。①の文字列、②の全角数字、③のスペース、④の単位、⑤のアポストロフィ、⑥の数字と文字の混在、⑦の日付、⑧の先頭の空白、⑨の末尾の空白、⑩の見えない文字、⑪の改行、⑫の形式の不統一、⑬の文字コード、そして今回⑭のCSV文字列化。ここまで14回にわたってお伝えしてきましたが、共通しているのは「見た目ではなく、データの中身に原因がある」という一点です。
CSVは実務で非常によく使われますが、Excel向けに完璧に整っているわけではありません。文字列化、日付崩れ、ゼロ消失など、多くの落とし穴があります。たとえば「2024/4/1」のような日付が、開いた瞬間に別の形式に変わってしまったり、「00123」という商品コードの先頭の0が消えて「123」になってしまったりする、というのは、CSVでよくあるトラブルです。だからこそ、CSVを開いた直後には、一度データの状態を確認する。これを習慣にするだけで、かなりの事故を防げます。数値の形式がバラバラで集計がズレるケースについては、以前の記事でもくわしく取り上げていますので、あわせて確認しておくとよいでしょう。数値の形式が統一されていないケースと、今回のCSV文字列化は、密接につながったトラブルです。
まとめ
今回のテーマは「SUM関数が計算されない理由⑭｜CSV取り込みで文字列になる」でした。ポイントをまとめます。
- チェックポイント：左寄せになっている／緑の三角が出る／SUMで計算できない
- 原因：CSVを開くときに、数字が文字列として読み込まれている
- 解決方法：
=VALUE(セル)または「数値に変換する」「区切り位置」で数値に変換する
いちばん大切なのは、CSVを開いたら、まずデータの状態を確認する、という習慣です。左寄せ・緑三角・計算結果、この3つを見るだけでも、かなりの事故を未然に防げます。
もし今、CSVから取り込んだデータが文字列化していても、慌てる必要はありません。VALUE関数や「数値に変換する」を使えば、見た目はそのままに、正しく計算できる状態へと整えることができます。
関数の使い方シリーズや実務トラブル解決シリーズでは、他の関数やトラブルについても実務目線で整理していますので、あわせてご覧ください。次回は、見た目が似ていても集計や比較でズレる「小数点の形式が違う」ケースを取り上げる予定です。
コメント