エクセル関数の不思議

質問

エクセル関数でお聞きします。
別表のようにc5に　258と数値があります。
これを　6行目に　Left関数、Mid関数、Light関数で　それぞれ　２，５，８と分解します。
次にこの　２，５，８を　Ｓｕｍ関数で　=sum(c6:e6)で計算すると　０と表示されます。
次に　=c6+d6+e6 で個々に足し算すると　正解の15が表示されます。
これはなぜでしょうか。
また、今状態で　ＳＡＭ関数で正解を導くことはできますか？

※OKWAVEより補足：「富士通FMV」についての質問です。

回答No.2

notnot · ベストアンサー

leftやmid、right関数は文字列関数なので、結果は文字列です。
数値以外の値は、SUMの範囲にあっても0になります。
数値だけで計算するとすると、
C6・・・・C5を100で整数除算する・・・・ =QUOTIENT(C5,100)
D6・・・・C5を100で割った余りを10で整数除算・・・・・ =QUOTIENT(MOD(C5,100),10)
E6・・・・C5を10で割った余り・・・・ =MOD(C5,10)

あるいは、文字列に+0しても良いです。
=LEFT(C5,1)+0
お作法的には、文字列の数値変換関数VALUEを使います。何をしたいのかが明確に示せるので良いです。
=VALUE(LEFT(C5,1))

お礼

有難うございました

回答No.5

OKWavex

６行目は文字だから数値のみが対象のsum関数は０になります
計算式は自動的に数値に変換して計算するので15になります

回答No.4

bunjii

>次にこの　２，５，８を　Ｓｕｍ関数で　=sum(c6:e6)で計算すると　０と表示されます。
はい、その通りです。
SUM関数は文字列が含まれるときその値を無視することになっています。

>次に　=c6+d6+e6 で個々に足し算すると　正解の15が表示されます。
はい、その通りです。
四則演算では数値に見える文字列を数値として計算します。

>また、今状態で　ＳＡＭ関数で正解を導くことはできますか？
「SAM関数」→「SUM関数」
SUM関数の引数（要素）をVALUE関数で数値化すれば計算できます。
但し、数式内で配列演算するときは数式を確定するときにCtrl+Shift+Enterの打鍵が必要になります。
=SUM((VALUE(C6:E6)))
VALUE関数で配列値を返すときにINDEX関数内で実行すると数式の確定時にEnterキーのみの打鍵でも正しい結果が得られます。
=SUM(INDEX(VALUE(C6:E6),0))
　　　　　↓　等価
=SUM(INDEX((C6:E6)*1,0))

回答No.3

asciiz

＞これを　6行目に　Left関数、Mid関数、Light関数で　それぞれ　２，５，８と分解します。

おそらく「=LEFT(C5, 1)」のようにやっていると思いますが、ここで、C6セルに入っている価は「文字列値」となっています。
まあそれは、セルの中で左詰めに表示されていることからもわかりますね。(数値ならデフォルト右詰めになる)

そして、sum() 関数では、文字の入ったセルは飛ばして、計算します。
1, a, 2, b, 3 と並んでいるセルの sum() を計算すると、エラーではなく6になるのはそのためです。
ご質問の中で、C6セル、D6セル、E6セルは、文字列関数により、文字列値となってしまっているため、sum() で集計するものは何もなく、0という計算結果になったわけです。

では、C6+D6+E6で、15になるのはなぜか。
これは、Excelがそのような計算をする場合に、「文字列であっても数字しか含まないなら、数値として扱う」と言う自動変換が働くためです。
見た目の"8"は数値の8、見た目の"5"も数値の5…として扱うので、2+5+8は、15になります。

実はこういった自動変換、実は一番最初の時にも起こっています。
本来でしたら、=LEFT(C5, 1)とやるときの、C5セルは、文字列値でなければなりません。
でも、数値の「258」を、文字列値の"258"でもある、と見立てて、その1文字目を取り出したものが、C6セルの計算結果(文字列値)"2"となったのです。

ちなみに、文字列値を文字列値のまま結合するには、&演算子を使います。
=C6&D6&E6 とやると、"2" "5" "8" をそのまま結合した、"258"(文字列値、デフォルト左詰め)と言う結果になります。

ちなみにプログラミング言語では、文字列変数と数値変数は、区別して扱います。(まぜこぜにできる言語もありますが…)
数値変数に文字列値は入りません。=left(数値, 1)のようなことをやると、引数エラーとなるか、想定外の結果が返ってきてしまいます。

でもExcelはそこまで固いことを言わず、なるべく意図にそった計算をしてあげようと、文字列と数値をいろんな場面で自動変換してくれるわけです。
でもそれが余計なお世話で、かえって混乱する場合もありますね。

----

数値の3桁目・2桁目・1桁目をきちんと取り出すには、次のように、数値は数値として扱います。

C6=mod(int(C5/100), 10)
C7=mod(int(C5/10), 10)
C8=mod(int(C5/1), 10)
※C8セルではわざわざ1で割る必要はありません、単純に=mod(c5/10)でOK

こうすると、それぞれのセルに数値として2, 5, 8 が入ります。
sum計算しても、加算しても、15という結果が得られます。

回答No.1

f272

=sum(c6:e6)としても，c6，d6，e6は文字だから0にしかならない。
=c6+d6+e6とすれば，c6，d6，e6をそれぞれ計算に使用するので数値に自動的に変換し15になる。
そのままで合計を出したければ，例えば
=SUMPRODUCT(--(C6:E6))
とする。
そうでなければc6，d6，e6を求めるときに
=--LEFT(C5,1)
=--MID(C5,2,1)
=--RIGHT(C5,1)
などと，おまじないをかけてください。そうすれば=sum(c6:e6)でも大丈夫。