excelで、１つのセルに入っている複数の数字を合計するには？

質問

Excel（2002以降）で、たとえば
　セルA1に　33,2,55,764
　セルA2に　654,654,8,165,46
のように、カンマ区切りで数字が入っていて、
その数字の合計をB列に表示させるということは可能でしょうか？
試算用にB列・C列などを使い、結果をD列、などという方法でも大丈夫ですが、数字がいくつに分けられるかはわからない（極端な話100個や200個の可能性もある）ので、その分のセルを確保するのは無理です。

数字はいくつ入っているかはセルごとに違い、何も入っていない場合や1つしかない場合もあります。

カンマを＋に置換して計算させるような関数があればできそうだと思ったのですが、ヘルプでは探しだせませんでした。

なにかいい方法があればお願いします。

回答No.4

AloneAgain · ベストアンサー

こんにちは。

次の方法を試してみてください。
***
B1に↓を入れ、以下オートフィルでコピー
------------------------
=SUBSTITUTE(A1,",","+")
------------------------

B1セルを選択した状態で、
メニュー＜挿入＞－＜名前＞－＜定義＞
　▽
いちばん上の欄に適当な名前を入力（例：keisan)
　▽
いちばん下の「参照範囲」欄に
↓のように入力してOKをクリック（Sheet1なら）
-------------------------
=EVALUATE(Sheet1!$B1)
-------------------------
絶対参照を間違えないようにしてください。
$B1 です。
　▽
C1に↓を入力して、以下オートフィルでコピー
----------------------------------------
=keisan
--------
C列に計算結果が表示されると思います。

空白セルの場合はエラーになりますので
それを表示させないようにするには、
↓のようにしてください。
-------------------------------
=IF(B1="","",keisan)
-------------------------------

お礼

まさに求めていた方法でした。ありがとうございます。
ところで、EVALUATEというのはなんなのでしょうか？
Excelのヘルプでは出てこないですが・・・。

回答No.7

AloneAgain

すみません、No.4です。

>極端な話100個や200個の可能性もある

No.4の方法では、
セルに入っている数値の個数に限界があるようです。

ちょっとテストしてみたところ
1桁の数字で130個あたり、
3桁の数字で 65個以上ぐらいでエラーになりました。

回避策はわかりません・・・すみません。
No.5、No.6の方のユーザー定義関数を試してみてください。

>EVALUATEというのはなんなのでしょうか？
VBAのメソッドにもありますが、
この場合は、VBAの前身（？）、 EXCEL4.0マクロの関数です。

回答No.6

BLUEPIXY

VBAのユーザ定義関数で
Public Function add(CVS As String) 'カンマ区切りの文字列の数値を足し算する
Dim a, x, sum
a = Split(CVS, ",")
sum = 0
For Each x In a
sum = sum + x
Next
add = sum
End Function
として、
B1
=add(A1)
として下にコピー

回答No.5

ja7awu

> 100個や200個の可能性もある）ので、その分のセルを確保するのは無理です。

そういうことであれば、VBAによるユーザー定義関数を使うよりないと思います。

宜しかったら、次の手順でテストしてみてください。

CommaSplitAdd関数の設定手順

１．Alt + F11 で VBE(Visual Basic Editor)を開きます。
２．VBE のメニューから[挿入] -->[標準モジュール] を指定します。
３．モジュールウィンドウに下記コードをコピーして貼り付けます。
４．Alt + Q （または、右上隅の×）でウィンドウを閉じ、シートに戻ります。
５．メニューから[ツール]-->[マクロ]-->[セキュリティ]で「セキュリティレベル」を
　「中」にして[OK]します。

これで、CommaSplitAdd関数が使用出来ます。

書式:
= CommaSplitAdd(カンマで区切られた文字列式)

仕様
カンマで区切られた数字を加算します。
文字列式に数字以外が含まれている場合は、その文字を無視します。

使用例:

セルA1に　33,2,55,764 が入力されているとき、

　= CommaSplitAdd(A1)

で、854 が表示されます。

一般の関数のように使用することが出来ます。

これで、如何でしょうか。

Function CommaSplitAdd(CelStr As String)
Dim N As Long
Dim WStr As String
Dim W As String
Dim ValSum As Currency
For N = 1 To Len(CelStr & ",")
　　W = Mid(CelStr & ",", N, 1)
　　If IsNumeric(W) Then
　　　　WStr = WStr & W
　　Else
　　　　If W = "," Then
　　　　　　ValSum = ValSum + Val(WStr)
　　　　　　WStr = ""
　　　　End If
　　End If
Next N
CommaSplitAdd = ValSum
End Function

回答No.3

noname#129708

FIND関数を絡めて、LEFT関数や、RIGHT関数は使えませんか。

お礼

※カンマ区切りでは支障が出るようなので、半角スペース区切りに変更しました。

A1に　　10 32 200 10
B1に　　=LEFT(A2,FIND(" ",A2)-1)
C1に　　=RIGHT(A2,LEN(A2)-FIND(" ",A2))
D1に　　=LEFT(C2,FIND(" ",C2)-1)
E1に　　=RIGHT(C2,LEN(C2)-FIND(" ",C2))
：
：

といったようにしていけば（もう少しきれいな書き方があると思いますが）、可能は可能ですが、A1に100個の数字が含まれる場合、作業領域にいったいどれだけ使う気かという状態になってしまいますし、どうしてもA1に書ける数字の個数に制限ができてしまいます・・。

最初に考えたのは
B1に　　="="&SUBSTITUTE(A2," ","+")
とし、この文字列の式を数式として計算してくれる関数があれば、と思ったのですが、存在しないのでしょうかね・・・。

回答No.2

y0923

A列を選択して「CTRL＋H」で置換機能を使用し「,」から「＋」に置換します。
B列に「="あ"&A1」と式を組み、置換した文字列の先頭に「あ」という文字をつけます。
B列をコピーしC列に〈形式を選択して貼り付け→値)と値のみを貼り付けます。
B列の選択し「あ」から「=」に置換します。

もっとスムーズなやり方があるかもしれませんが、これぐらいしか思いつきませんね。

お礼

それではA列が変更するたびにB列も計算させなおさないといけませんよね。
A列は形式を変更せず（カンマで区切った0個以上の数字）、その和を隣に自動計算させたいのです。
マクロを使えばy0923さんの方法でも自動計算させられますが、もうちょっとスマートな方法がないかと考えています。

回答No.1

papayuka

良く解りませんが、、、

A列を選んで、データ－区切り位置でカンマを指定してやれば各セルに分割出来ますよ。

お礼

そのデータ（A列）自体を変更したり、すべての数値をセルに分割する方法は無理なのです。