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と関連する良く見られている質問

QExcel ワークシート関数で参照セルのアドレスを固定値ではなく動的に記述したい

皆様こんにちは。
Excel ワークシート関数で参照セルのアドレスを固定値ではなく、他のセルの値 (式の計算結果) を使用して動的に記述したいのですが、可能でしょうか。
一例ですが、

=VLOOKUP("山田 花子",Sheet2!A2:C100,3,FALSE)

という式の参照先アドレス範囲開始行「2」を固定値ではなく、他のセルの値を使用して動的に記述したいのです。
また、参照先の終端である、「100」についても固定値ではなく、有効データ領域の終端を動的に取得する方法 (VBA の Worksheet.UsedRange.Rows.Count または Worksheet.UsedRange.Rows.Address に相当) が知りたいと思います。

どちらも、計算式そのものをVBAで動的に出力するのは容易なのですが、出力されたブックを手作業で編集する際の利便性も考慮すると、ワークシート関数だけで解決する方法も知っておいたほうがよいと思っています。

ご存知の方、コメントを頂けると幸いです。

Aベストアンサー

A列の入力済みの最下行が文字列の場合
=MATCH("",A:A,-1)

最下行が数値の場合
=MATCH(MAX(A:A)+1,A:A,1)

で、最終行が取得できます。
取得された数値を、関数でセル範囲の指定に使うためにはINDIRECT関数を用います。
今夜は今から飲み会なのであとはINDIRECT関数を検索してみてください。
成功を祈ります。では。
(*^∇^)ノロ"ロヾ('∇'*) Cheers!!

Q例えば、AさんからGさんまでがA列に縦に並んでいてB列に数字が入っています。B列にある数字の合計をA

例えば、AさんからGさんまでがA列に縦に並んでいてB列に数字が入っています。B列にある数字の合計をA-Gさん別々に出したいんですが簡単なvbaの記述方法はないでしょうか?
お願いします。

Aベストアンサー

こんばんは!

A列のA~Gさんは複数存在しているのでしょうか?
そうであればSUMIF関数で対応できると思いますが、VBAをお望みだというコトですので
一例です。

元データはSheet1にあり、Sheet2に表示するとします。
尚、Sheet1の1行目は項目行でデータは2行目以降にあるという前提です。
標準モジュールにしてください。

Sub Sample1()
Dim lastRow As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")
wS.Cells.ClearContents
With Worksheets("Sheet1")
.Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=wS.Range("A1"), unique:=True
lastRow = wS.Cells(Rows.Count, "A").End(xlUp).Row
With Range(wS.Cells(2, "B"), wS.Cells(lastRow, "B"))
.Formula = "=SUMIF(Sheet1!A:A,A2,Sheet1!B:B)"
.Value = .Value
End With
End With
End Sub

こんな感じではどうでしょうか?m(_ _)m

こんばんは!

A列のA~Gさんは複数存在しているのでしょうか?
そうであればSUMIF関数で対応できると思いますが、VBAをお望みだというコトですので
一例です。

元データはSheet1にあり、Sheet2に表示するとします。
尚、Sheet1の1行目は項目行でデータは2行目以降にあるという前提です。
標準モジュールにしてください。

Sub Sample1()
Dim lastRow As Long, wS As Worksheet
Set wS = Worksheets("Sheet2")
wS.Cells.ClearContents
With Worksheets("Sheet1")
.Range("A:A...続きを読む

Qオープンオフィスのカルクで、関数内の特定のセルを固定したい

オープンオフィスのカルクで、Excelでいう「絶対参照」の「$」の意味と同じものを関数の中に設定したい(式をコピーしてもあるセルだけを固定できるようにしたい)

Aベストアンサー

もしかしてキーボード操作についてでしょうか?
でしたらExcelと異なりShift+F4です。

QA列とB列の重複を抽出したいのですがA列とB列の値は一部だけ同じ文字です。ご教示お願いします。

エクセル初心者です。重複を見つけるのが仕事です。いろいろやってみたのですがうまくできません。
お知恵をお貸しください。

A列には企業名が入力されています。
B列にも企業名が入力されていますが全く同じ文字ではないのです。

たとえばこういうことです。
A1 (有)雪見酒      B1  雪見
A2 株式会社豪雪地帯   B2 (株)豪雪地帯
A3 ゆきかき本舗     B3 (有)ゆきかき本舗

A列にある企業名とB列にある企業名が同じであればセルを塗りつぶすか○を表示させるように
したいのです。
重複を見つけるのが目的なので、ほかの方法でもかまいません。
すみません、A列のセルとB列のセルが全く同じ名前ならば重複が見つけられたのですが
ここから先がどうしてもわからないのです。。。
申し訳ありませんがどうか教えてください。。。

Aベストアンサー

No4です。以下のマクロを標準モジュールへ登録してください。
--------------------------------------------------
Option Explicit
Public Sub 重複チェック()
Dim maxrow1 As Long
Dim maxrow2 As Long
Dim row1 As Long
Dim row2 As Long
Dim nameT1() As String
Dim nameT2() As String
Dim t1, t2 As Variant
t1 = Time
maxrow1 = Cells(Rows.Count, "A").End(xlUp).row '最大行取得
maxrow2 = Cells(Rows.Count, "B").End(xlUp).row '最大行取得
ReDim nameT1(maxrow1)
ReDim nameT2(maxrow2)
Range("C1:" & "D" & maxrow2).Value = ""
Call makeTable(nameT1, "A", maxrow1)
Call makeTable(nameT2, "B", maxrow2)
For row1 = 1 To maxrow1
For row2 = 1 To maxrow2
If Cells(row2, "C") = "" Then
If Mymatch(nameT1(row1), nameT2(row2)) = True Then
Cells(row2, "C").Value = "○"
Cells(row2, "D").Value = row1
End If
End If
Next
Next
t2 = Time
MsgBox ("チェック完了 処理時間=" & Minute(t2 - t1) & "分" & Second(t2 - t1) & "秒")
End Sub
'余分な文字を削除した結果をテーブルに格納する
Private Sub makeTable(ByRef nameT() As String, ByVal col As String, ByVal maxrow As Long)
Dim row As Long
Dim ary As Variant
Dim name As String
Dim i As Long
ary = Array("㈱", "(株)", "株式", "(有)", "有限", "会社")
For row = 1 To maxrow
name = Cells(row, col).Value
For i = 0 To UBound(ary)
name = Replace(name, ary(i), "")
Next
nameT(row) = name
Next
End Sub
'企業名が一致かどうか判定する
Private Function Mymatch(ByVal name1 As String, ByVal name2 As String) As Boolean
Mymatch = False
Dim pos As Variant
pos = InStr(1, name1, name2, vbTextCompare)
If pos > 0 Then Mymatch = True
End Function
-----------------------------------------------------
一致の精度が悪ければその旨補足してください。
(一致すべきものが一致しない、一致してはいけないものが一致している)
100%解決できる保証はありませんが、多少のチューニングは行います。

No4です。以下のマクロを標準モジュールへ登録してください。
--------------------------------------------------
Option Explicit
Public Sub 重複チェック()
Dim maxrow1 As Long
Dim maxrow2 As Long
Dim row1 As Long
Dim row2 As Long
Dim nameT1() As String
Dim nameT2() As String
Dim t1, t2 As Variant
t1 = Time
maxrow1 = Cells(Rows.Count, "A").End(xlUp).row '最大行取得
maxrow2 = Cells(Rows.Count, "B").End(xlUp).row '最大行取得
ReDim ...続きを読む

Q1つのセルに、関数固定・データの入力規則(リスト)を設定できますか?

お世話になります。
1つのセルに以下のことを設定したいと考えております。

・毎月1日になったら、"未対応"(入力規則:リスト)と表示させる。
・"対応済"(入力規則:リスト)に変更しない限り、"未対応"表示にする。
・1日に"対応済"に変更できる状態にする。

1つのセルに、入力規則のリストと、関数を固定できたら
上記のことができると考えています。
セルにロックをかけるとリストを変更することができないので、
他に手段がないかと模索しております。
アドバイスありましたら、ぜひお願い致します。

Aベストアンサー

>・毎月1日になったら、"未対応"(入力規則:リスト)と表示させる。
>・"対応済"(入力規則:リスト)に変更しない限り、"未対応"表示にする。
>・1日に"対応済"に変更できる状態にする。

1つのセルで、数式とデータの共存はできません。
VBAで対応することになります。

Qエクセル セルの結合(A列,B列→A列に結合)の操作

現在、大量の人名を編集しています。

A列に名字、B列に名前が入力されている状態なのですが、A列に名字と名前を一緒に表示させるためには、どのようにすればよいか教えて下さい。

Aベストアンサー

C列に「名字+氏名」を表示させるなら、C1セルに
 =A1&B1
と入力して、下方向にコピーすれば良いです。

名字が入力されているA列の値を「名字+氏名」で置き換えるのは関数ではできません。
どうしても置き換えたいなら上記で説明した方法で表示された「名字+名前」をコピーして、A1に「編集」→「形式を選択して貼り付け」→「値」で貼り付けるという方法はありますが…

Qエクセルの関数で質問です。上のセルになんらかの文字が入っていた場合、左

エクセルの関数で質問です。上のセルになんらかの文字が入っていた場合、左のセルに入っている文字を返すにはどういう記述をしたらよいでしょうか。

返す文字は固定ではなく、左隣に入っている文字(数字)を返したいです。
この記述を結構大量のセルに入力するので、どのセルにも共通した記述を教えていただけるとなお良いです。

よろしくお願いします。

Aベストアンサー

エクセルの共通の操作ですが,あるB2セルに「上があったら左を返す」
=IF(B1="","",A2)
という式を入れておきます。

このB2セルをコピー,結構大量の「目的のセル」を選んでただ貼り付けると,共通でそのように動作する式に自動で書き換わって投入されます。
それぞれのセルにそれぞれのセル番地の式を,いちいち書き換えて記入していく必要は全くありません。

QExcel 列がA,B,C...ではなく1,2,3になっている。これを直すには

通常
A B C D E
1
2
3
4
5
のようになっているのですが、
1 2 3 4 5
1
2
3
4
5
となっています。処理等問題はないのですが、
セル指定が指定のセルからの位置指定になってしまい
RC[1]:RC[1]のようになったりしています。

通常のように戻すにはどうしたらいいのでしょうか

Aベストアンサー

[ツール]-[オプション]-[全般]で、
[R1C1参照形式を使用する]をOFF。

QVLOOKUP関数を使用して交差する位置から2行下のセルの参照式が分かりません

VLOOKUP関数を使用し検査値(検査値に商品名を指定)に該当するセルとF列とが交差するセル位置から下に2行移動したセルを参照する式が分からないのですが。例えばセルA1に入力されている"商品名A"(固定位置)を検査値として、セルA100にある"商品名A"(位置が上下に可変する)をVLOOKUP関数で検索し更に"商品名A"とF列とが交差するセルF102のセルを検索する式を教えてほしいのですが。下の例で説明しますとセルA1の"商品名A"をセルA100からA200の範囲から検索し、F列と交差するセルから2つ下のセルF102の"不良品"を参照する式をVLOOKUP関数を使用した式を教えてください。セルA100の"商品名A"とセルF102にある"不良品"との相対位置は不変です。具体的には100行の上に1行が追加されると"不良品"の位置は下に1行ずれるというものです。(要は1行追加しても不良品のセル位置は商品名Aから右に5、下に2移動した位置は変わらないということです)。VLOOKUP関数を使用するのは行が追加・削除されてもセルA100にある"商品名A"の位置が行の上下動に対応させるためです。もしVLOOKUP関数以外の関数式がございましたら併せて教えてほしいのですが、宜しくお願いします。


    A   B  C  D   E   F 
1  商品名A
2  商品名B


100商品名A
101
102               不良品

VLOOKUP関数を使用し検査値(検査値に商品名を指定)に該当するセルとF列とが交差するセル位置から下に2行移動したセルを参照する式が分からないのですが。例えばセルA1に入力されている"商品名A"(固定位置)を検査値として、セルA100にある"商品名A"(位置が上下に可変する)をVLOOKUP関数で検索し更に"商品名A"とF列とが交差するセルF102のセルを検索する式を教えてほしいのですが。下の例で説明しますとセルA1の"商品名A"をセルA100からA200の範囲から検索し、F列と交差するセルから2つ下のセルF102の"不良品"を参...続きを読む

Aベストアンサー

VLOOKUP関数ではできないのでINDEXとMATCH関数を組み合わせます
=INDEX($F$102:$F$202,MATCH(A1,$A$100:$A$200,0))
とか
=INDEX($F:$F,101+MATCH(A1,$A$100:$A$200,0))

Qエクセルの列番号(上のA,B,C,D…)が数字になってしまいました。

エクセルの列番号(上のA,B,C,D…)が数字になってしまいました。
気が付いたら、縦横ともに列番号、行番号ともに数字になっています。
どうしたら列番号が元のアルファベッドに戻るのでしょうか?
セル参照とかする時非常にわかりずらいのですが……
特にどこかの設定を触ったとかってないのですが、勝手に変わっちゃったってこともないと思うんですけど……
ウィンドウズVisutaのエクセル2007です。
どなたかわかるかたいらっっしゃいますか?

Aベストアンサー

http://www.geocities.jp/laut6/r1c1/r1c1.html
チェックを外し、現在開いているファイルを保存します。この設定は各ファイルに保存されるのですが、一番最初にこのR1C1参照形式のファイルを開いたりブックのウィンドウがない状態でR1C1形式のファイルを開いてしまうと、デフォルト設定が自動的にR1C1形式に変更され、それ以降開くファイルはすべてR1C1形式に変更されてしまいます。気づかずに上書き保存すると後が大変です。

R1C1/A1の参照形式がファイルを開くと変更される
http://support.microsoft.com/kb/405338/JA/

Excel で R1C1/A1の参照形式がファイルを開くと変更される
http://support.microsoft.com/kb/405338/ja


ちなみに、R1C1参照形式は
http://bbs.pc21.nikkeibp.co.jp/keyword-245
で解説されているように使用します。


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

人気Q&Aランキング

おすすめ情報