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

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

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

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

A 回答 (7件)

こんにちは。



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

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

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

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

お礼日時:2005/04/17 15:43

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



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

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

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

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

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

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)
として下にコピー
    • good
    • 0

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



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

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


CommaSplitAdd関数の設定手順

1.Alt + F11 で VBE(Visual Basic Editor)を開きます。
2.VBE のメニューから[挿入] -->[標準モジュール] を指定します。
3.モジュールウィンドウに下記コードをコピーして貼り付けます。
4.Alt + Q (または、右上隅の×)でウィンドウを閉じ、シートに戻ります。
5.メニューから[ツール]-->[マクロ]-->[セキュリティ]で「セキュリティレベル」を
 「中」にして[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
    • good
    • 0

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

    • good
    • 0
この回答へのお礼

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

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," ","+")
とし、この文字列の式を数式として計算してくれる関数があれば、と思ったのですが、存在しないのでしょうかね・・・。

お礼日時:2005/04/17 14:22

A列を選択して「CTRL+H」で置換機能を使用し「,」から「+」に置換します。


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

もっとスムーズなやり方があるかもしれませんが、これぐらいしか思いつきませんね。
    • good
    • 0
この回答へのお礼

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

お礼日時:2005/04/17 13:51

良く解りませんが、、、



A列を選んで、データ-区切り位置でカンマを指定してやれば各セルに分割出来ますよ。
    • good
    • 0
この回答へのお礼

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

お礼日時:2005/04/17 13:46

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルの一つのセル内に2つの数値を配置したい

 はじめまして。質問させてください。
質問
 ひとつのセル内に2つの数値を表示したいと思っています。
折り返しなどで2段に表示するのではなく,1段に2つの数値を配置したいです。
 2つの数値を配置したいセルに,「=A1&" "&B2」←このような計算式を組んだ結果,ひとつのセルに2つの数値を配置する事には成功しました。
 しかし,A1,B2に入れてある数値は全て小数第1位まで表示させてあるのですが(0も0.0としている),上記の式で,2つの数値を配置してあるセルでは,小数第1位が0の時は,小数第1位が省かれて表示されません。(0.0は0に,6.0は6になります)

 なんとか小数第1位まで表示させる方法はありますでしょうか?

また,他に「&」を使わなくても,ひとつのセルに2つの数値をいれる方法がありましたらお教え下さい。
(ちなみにこのデータの数値は,ひとつは去年の樹高(気の高さ),もうひとつは今年の樹高を表す数値で,去年の樹高と比べるために,ひとつのセルに2数値表示させる必要があります。)

どうぞよろしくお願いします。

 はじめまして。質問させてください。
質問
 ひとつのセル内に2つの数値を表示したいと思っています。
折り返しなどで2段に表示するのではなく,1段に2つの数値を配置したいです。
 2つの数値を配置したいセルに,「=A1&" "&B2」←このような計算式を組んだ結果,ひとつのセルに2つの数値を配置する事には成功しました。
 しかし,A1,B2に入れてある数値は全て小数第1位まで表示させてあるのですが(0も0.0としている),上記の式で,2つの数値を配置してあるセルでは,小数第1位が0の時は,小数第1位が...続きを読む

Aベストアンサー

=Text(A1,"#.0") & " " & Text(B2,"#.0")
のように、TEXT関数を使うと書式設定ができます。

Q(エクセル)複数のセルの数値を、一つのセル羅列したい

いつもお世話になります。
エクセル初心者(マクロも関数も;;)です。

表題の通り、複数のセルにある数値を一つのセルにまとめる方法を探しています。
例えば、
--------------------------
    A   B   C   D

1   123  45   67  1234567

2   00   11   22  001122
--------------------------
のようにDの縦列にA・B・Cの数値を
(合計ではなく)羅列させたいのです。
すごく初歩的な質問のようなことだと思いますが
これがわかりません。。。

どうか簡単な方法を教えて下さい。

あと数値じゃなくて文字列でも同じような事ができるでしょうか?

環境:Excel2003

Aベストアンサー

見た目だけの結合であれば、D1列に
=A1&B1&C1
で、いかがですか。

Qエクセルでひとつのセルに複数の数字があり合計を出す

エクセルですが、教えて下さい
ひとつの長いセルに
  600  100  300 と数字 スペース 数字 スペースのパターンになっています、その合計1000を別のセルに出す方法はございますか?

同僚が手計算で出してくるのですが、必ず合計が違ってきて困っています。


どうぞ宜しくお願い致します

Aベストアンサー

VBA使っていいのなら、関数を定義すれば可能は可能です。

手順
1)ブックを開いて[Alt]+[F11]
2)[挿入]→[標準モジュール]
3)右側のエディタエリアに下記コードを貼り付け

Function spSum(sStr As String) As Double
Dim nSp As Variant
Dim n As Integer
sStr = StrConv(sStr, vbNarrow)
nSp = Split(sStr, " ")
spSum = 0
For n = 0 To UBound(nSp)
spSum = spSum + Val(nSp(n))
Next
End Function

これで、spSum関数が使えるようになります。
A1セルに 10 30 40  が入っていて B1セルに =spSum(A1) と入れると80と
表示されるはずです。

Qエクセルで一つのセルに入っている複数の数字を、それぞれ各セルに入れるにはどうしたらいいでしょうか

よろしくお願いします。

今エクセルにおいて、一つのセルに複数の数字が空白を置きながら入っています。
例えば以下のようなものが一つのセルに入っています。
1 0.935 4.571 0.000 4.918

これを各数字ごとに各セルに分けたいのですが、やり方がよくわかりません。

分かりづらい質問で申し訳ありません。
宜しくお願いします。

Aベストアンサー

関数を使用しない方法ですが如何でしょうか。
セルを選択→データ→区切り位置→「スペースによって・・・」にチェックがついていたら→完了

セルを選択→データ→区切り位置→「カンマやタブ・・・」にチェックが」ついていたら次へ→スペースにチェックを付け→完了

QエクセルEXCELでセル内にある複数の文字列の個数を数えたい

エクセルのA列に
1,3,4
1,2
1
4
2,3
という数字があるとします。これから結果として
それぞれの数字の個数を数えたいのです。すなわち、
B列に
1 3個
2 2個
3 2個
4 2個
という結果を得たいのですが、どうすればいいのでしょうか?

基本的な質問ですみません。countifを試したのですが、
条件に1と設定すると、(複数の数字が入力されたセルが無視されて)1個という結果になってしまいます。どなたかよろしくお願いします。

Aベストアンサー

すみません、完全に勘違いしておりました。
No.4の変形、少々強引ですが、以下でいかがでしょう?

=COUNTIF($A$1:$A$5,B1)+COUNTIF($A$1:$A$5,B1&",*")+COUNTIF($A$1:$A$5,"*,"&B1)+COUNTIF($A$1:$A$5,"*,"&B1&",*")

Qexcelで1つのセルに複数の値を返す方法はありますか?

おせわになっております。
excelで質問です。
excel関数でもVBAでもいいのですが、1つのセルに複数の値を返す方法はありますでしょうか??
たとえばVLOOKUP関数だと1つのセルに1つしか返せませんよね??
a=りんご b=バナナ c=すいか d=いちご e=キウイ
と設定したしたとして、
a,c,dと入力すると→りんご,すいか,いちご
とすべて1つのセルに出るようにしたいのです。りんご,すいか,いちごという値にしたいので、それぞれを別々のセルに入れることはしたくありません。可能でしょうか?? よろしくお願いいたします。

Aベストアンサー

#3です。ご期待にそえたようでうれしく思います。

>jlookupのjに何か意味はあるのでしょうか??

深い意味はありません。
てきとーに考えてJoin(結合)のJをつけただけです。

自由に変更して構いませんが、その場合、
下から2行目の
 jlookup = Join(myAry, ",")
の左辺もあわせて変更する必要があります。
----------------------------------------
>コンマの代わりににスペースで区切る場合はどのようになりますでしょうか??

#3のコードについて、

myAry = Split(myStr, ",")

myAry = Split(myStr, " ")
とすれば、検索値が半角スペースで区切られているものとして動作します。

jlookup = Join(myAry, ",")

jlookup = Join(myAry, " ")
とすれば、戻り値を半角スペースで区切って返します。
----------------------------------------
なお、引数を増やして区切文字をその都度自由に選べるようにすることもできます。

●拡張VLOOKUP(検索値,範囲,列番号,検索の型,区切文字(検索値),区切文字(戻値))
区切文字の指定を省略した場合は、","を区切文字として動作する仕様です。

'-------------↓ ココカラ ↓-------------
Function 拡張VLOOKUP( _
 ByVal myStr As String, _
 ByVal myRng As Variant, _
 ByVal myCol As Long, _
 ByVal myOpt As Boolean, _
 Optional ByVal kyDiv As String = ",", _
 Optional ByVal rtDiv As String = "," _
 ) As Variant
 Dim myAry As Variant
 Dim i   As Long
 myAry = Split(myStr, kyDiv)
 For i = 0 To UBound(myAry)
  myAry(i) = WorksheetFunction. _
   VLookup(myAry(i), myRng, myCol, myOpt)
 Next i
 拡張VLOOKUP = Join(myAry, rtDiv)
End Function
'-------------↑ ココマデ ↑-------------

ご参考まで。

#3です。ご期待にそえたようでうれしく思います。

>jlookupのjに何か意味はあるのでしょうか??

深い意味はありません。
てきとーに考えてJoin(結合)のJをつけただけです。

自由に変更して構いませんが、その場合、
下から2行目の
 jlookup = Join(myAry, ",")
の左辺もあわせて変更する必要があります。
----------------------------------------
>コンマの代わりににスペースで区切る場合はどのようになりますでしょうか??

#3のコードについて、

myAry = Split(myStr, ",")

myAry =...続きを読む

Q同一セル内に数字・文字がある場合の計算式

同一セル内に数字・文字がある場合の計算式

エクセルにおける表計算で困っています。
表計算をする場合、通常はセル内に数字のみを入力し合計値を求めてました。

例:100+100 合計200

しかし、同一セル内に文字が入ってきた場合は、合計値を求めても計算ができません。

例:100円+100円 合計0
セル数が少なければ電卓でも出来ますが、多い場合や平均値を求める場合に効率が悪くなります。
セル内の文字を読み取ることなく、数字データだけ読み取る方法はありますか?

Aベストアンサー

他の皆様が仰る通りデータ入力の効率も悪くこんな事考えない方がいいでしょう

・単に 「円」の表示が必要なら 表示書式を #,##0円として セルには数値だけにする。

・今回だけならシート名を右クリックからコピーして新しいワークシート上で文字を一括除去してから計算する
※ 文字列の一括除去は編集メニューから置換えをクリック 以下図参照して下さい。

・あえて式がご希望なら =LEFT(A1,LEN(A1)-1)+LEFT(B1,LEN(B1)-1)

Qエクセルで、セル内の数字を各々足すには

Excel 2002を使っています。
セル内の数字を各々足し、その結果を別のセルに出力する方法はあるのでしょうか。
例えば、
     A      B      C
---------------------------------------------
1| 12345678 |      |      |

となっている時に、
1+2+3+4+5+6+7+8を計算してB1に出力させるといった具合にです。
よろしくお願いします。

Aベストアンサー

B1 = IF(COUNT(A1),SUMPRODUCT(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)*1),"")

とします。B2から下や任意のセルにコピーできます。



ほとんど同じ意味ですが。配列数式を使って、

B1 = IF(COUNT(A1),SUM(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)*1),"")

  配列数式を登録するには、入力時に Ctrl+Shift+Enter とします。

Qエクセルの区切り数字を個別に集計する方法を教えてください。

エクセルでアンケート集計しているのですが、
カンマ区切りの数字から特定の数字の個数を
カウントする方法をご存じでしたら教えてください。

現在、複数回答の項目を下記の様に入力。
A4のセル 1,2,3,5,10,15,21
A5のセル 1,2,12,22

例えばA6のセルで、「1」と答えた数をカウントしたい。

複数回答の項目が一桁だと、
A6=COUNTIF("A4:A5","*1*")
で集計できたのですが、
今回のように二桁になると
A6=COUNTIF("A4:A5","*1*")=5
となってしまいました。

よろしくお願いします。

Aベストアンサー

=COUNTIF(A4:A5,"1")+COUNTIF(A4:A5,"1,*")+COUNTIF(A4:A5,"*,1,*")+COUNTIF(A4:A5,"*,1")

でどうでしょうか。
「1だけのセル」「1で始まるセル」「中に1があるセル」「1で終わるセル」の足し算です。

Qエクセルで、いつのセルに入っている7桁の数字をそれぞれ1つづつ、7つのセルに分ける関数はありますか??

こんばんは。大変こちらでお世話になっています。

エクセルのデーターをもとに、宛名印刷をしたくていろいろ試みているのですが、会社規定の封筒の郵便番号枠に、美しく数字を印字することがどうしてもできません。
こうなったら、郵便番号をばらして、1字づつ印刷しようかと思っています。

そこで教えて頂きたいのですが、今1つのセルに入っている郵便番号7桁をそれぞれ1桁づつ、7つのセルに分けたいのです。これは何か関数を使ってできるのでしょうか?
どうぞ教えてください。よろしくお願いします。

Aベストアンサー

MIDという関数が良いかと思います。
A1に対象の値が入っているとして
=MID(A1,xxx,1)
xxxの部分は何桁目から取り出すか、最後の数字が何文字取り出すかという指定です。

例)A1に「abcdefg」とあり
=MID(A1,3,1)
とすると「c」が表示されます。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報