SUM関数の答えが0になるときの対処法【スプレッドシート】


SUM関数の答えが0になることがあって、困っています。


今回は、こんなお悩みを解決します。


本記事の内容
・数字が正しく計算されないときの対処法(以下例題)
・¥など、通貨記号付きの値が計算されないときの対処法
・LEFT関数で取り出した数字が計算されないときの対処法
※ 本記事はエクセルにも対応しています。


例題
・次のSUM関数の答えが0になる

【本記事のサンプルファイルを見る】


例題への対処法
1. 数値として認識されないセル範囲を選択
2. メニューバー > 表示形式 > 数字 > 数値 をクリック


本例のように、表示形式が書式なしテキスト(=単なる文字)の場合、形式を数字>数値にすることで正しく計算されるようになります。

一方、表示形式を数値にしても答えが0になる場合、以下の異なる原因が考えられます。


 原因1:¥など、通貨記号を手打ちで使用している


表示形式を数字にしても、数字認識されない例

通貨記号を手打ちで直接入力すると、スプレッドシートは自動でそれを文字認識します。文字認識された通貨記号は、表示形式を数字>日本円に変更しても、数字として認識されません。
じゃあ、¥は足し算に使えないの?
いいえ、先に数字のみを入力して、表示形式から数字>日本円を選択すれば、¥マーク付きの計算は可能です。


通貨記号付きの値を数字認識させる手順


1. 数値のみをセルに入力
2. ¥表記にしたい数値を範囲選択し、表示形式から日本円に変更

上下の例は、見た目が同じでも¥マーク入力のプロセスが違う(手打ちでか、表示形式からか)せいで形式認識に差が出ることを示しています。例えば、どこかから¥マーク付きのデータを値でコピペした場合、文字と認識されてしまうから気をつけましょう。
数字認識されているか確認する方法はないの?
Isnumber(数字かどうかを教えてくれる関数)をつかえば、数字かどうかを判別できるわ。



Isnumber関数で、数字か文字かを判別する

Isnumber関数は数字の場合はTRUEを返して、そうでない場合はFALSEを返します。どちらも見た目は¥100で、表示形式も日本円に設定されているけど、上は手打ち入力だから数字認識されません(false)。

Isnumber関数とは


 原因2:Left(Right)関数を使用している


Left関数で100円から100を抜き出した後、表示形式を数字しても、falseと判別されます。Left(Right)は取り出しだした文字列の見た目が数字でも文字認識します(文字数で取得する範囲を指定するため)。次の方法で、これを防ぐことが可能です。

  1.  代わりにSplit関数をつかう
  2.  1を先頭にかける
  3.  value関数をつかう
  4.  置換機能をつかう
  5.  セルをコピーして、値のみで貼り付け後、表示形式を数字に変更


代わりにSplit関数をつかう

Split関数は、指定した文字を境に文字列を完全に分離するので、分離された100は数字として認識される。


= Split(分離させたいセル番, “分離する境となる文字”)


1を先頭にかける

1を先頭にかけることで、続く文字と認識される関数を自然数に戻すことが可能なので、数字として認識させることが可能です。”*”だけでなく、”+”や”-“といった四則演算を絡めると、書式に関係なく数字は数値として計算されるようになります。



value関数をつかう

Value関数は、関数の答えを数字にして返すので、left関数の答え100は数字として認識される。


= Value(数字として返す関数)



置換機能をつかう

置換対象が一つしか無い場合は、スプレッドシートの置換機能を使うことで数字に置き換えるほうが簡単です。完全に文字列を含まない数字に置き換えるので、表示形式を数字>数値に変更すれば、計算で使用できるようになります。


置換 = Ctrl(command) + Shift + h