エクセルのユーザー定義でｋｇｓとｐｃｓを設定した時

例）A1: 100pcs
A2: 200pcs
A3: 10kgs
A4: 20kgs
------------
A5: 300pcs
A6: 30kgs

という結果を得たいのです。
A5=SUM(A1:A2)
A6=SUM(A3:A4)
というような式ではなく
”A1からA4の範囲で値にPCSがついているもの
を合計する”
というような式をA5に入れて計算させたいのです。
どんな関数を使ったら、またどういう
工夫をすれば、上記の様な計算結果を
得られるのかがどうしても解りません。
お忙しいところ恐縮ですが、どなたか
ご存知の方がいっらっしゃいましたら
教えてくださいM(__)M

No.1 回答者： maruru01 回答日時： 2003/11/12 15:18

こんにちは。

maruru01です。

A5に、

=SUMPRODUCT((RIGHT(A1:A4,3)="pcs")*LEFT(A1:A4,LEN(A1:A4)-3))

A6に、

=SUMPRODUCT((RIGHT(A1:A4,3)="kgs")*LEFT(A1:A4,LEN(A1:A4)-3))

です。

この回答へのお礼

ご回答参考になりました
ありがとうございます＾＾

お礼日時：2003/11/13 13:19

No.2 回答者： FlossenEngel 回答日時： 2003/11/12 16:23

マクロ(VBA)で

Function mysum(unit As String, cells As Range) As Variant
'シートのセルには
'=mysum("kgs",A1:A4)
'=mysum("pcs",A1:A4)
'のように入力
Dim row, col, tmp

tmp = 0
unit = """" & unit & """"
For Each row In cells.Rows
For Each col In row.Columns
If (Right(col.NumberFormatLocal, Len(unit)) = unit) Then
tmp = tmp + col.Value
End If
Next
Next
mysum = tmp
End Function

この回答へのお礼

入力して試してみました＾＾
見事できました。
とてもプロフェッショナルな回答ありがとう
ございました。
大変勉強になりました

お礼日時：2003/11/13 13:41

No.3 ベストアンサー 回答者： ja7awu 回答日時： 2003/11/12 16:47

「ユーザー定義でｋｇｓとｐｃｓを設定した時」とありますが、

この「ユーザー定義」とは、書式のことを指しているようにも取れますが、

一応、数値を書式で"pcs"等を付加している場合も考慮し、空白や３文字
以外が含まれていてもエラーにならないような「ユーザー定義関数」を
作ってみました。　詳しい操作手順を書きますので、宜しかったらテスト
してみてください。

関数の使い方は、=SumX(範囲,"文字列")　です。
例えば、=SumX(A1:A4,"pcs")　で、A1:A4範囲の指定した文字列が含まれていて
それより左側が数字と認識できるものを加算します。
結局、=SumX(A1:A10,"") は、一般的な =SUM(A1:A10) と同じになります。

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

これで、SumX 関数が使用できます。　例の場合　セルA5に =SumX(A1:A4,"pcs") 、
A6に　=SumX(A1:A4,"kgs")　と入力してみてください。

（もし、数値を書式で"pcs"や"kgs"の文字列を付加していないときは、
コードの３ヶ所ある Rng.Text を Rng.Value に変更してもよいでしょう。）

Function SumX(R As Range, S As String) As Double
Dim Rng As Range
Dim ValSum As Double
Dim V As Variant
For Each Rng In R
　　If InStr(Rng.Text, S) > 0 Then
　　　　V = Left(Rng.Text, Len(Rng.Text) - Len(S))
　　　　If IsNumeric(V) Then
　　　　　　ValSum = ValSum + V
　　　　End If
　　End If
Next Rng
SumX = ValSum
End Function

この回答へのお礼

お忙しい所詳しい解説ありがとうございます。
VBA駆け出しの私には難しいコードですが
解読して使えるように頑張ってみますね＾＾

お礼日時：2003/11/13 13:24

No.4 回答者： imogasi 回答日時： 2003/11/12 20:37

書式でpcs,gsを表示しているとして、通常のエクセル関数では書式を捉えることが難しいと思います。

したがってＶＢＡに頼らざるを得ません。
下記でどうでしょう。書式に「pcs」を含むものを加えています。
Sub test01()
d = Range("A1").CurrentRegion.Rows.Count
MsgBox d 'データ最下行を確認
s = 0
For i = 1 To d
f = Cells(i, "A").NumberFormatLocal '書式を取る
p = InStr(f, "pcs") '書式にｐｃｓの文字があるか
If p = 0 Then
Else
s = s + Cells(i, "A") 'ｐｃｓを含めば加える
End If
Next i
MsgBox s '合計を表示
End Sub

この回答へのお礼

お忙しいとこ詳しいご回答ありがとうございました
入力してできるか試してみますね
ありがとうございます

お礼日時：2003/11/13 13:45