[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『sumif/sumifで作った平均を求める式をマクロで組みたい』（みかん）
いつも大変お世話になります。 今回は関数を組み合わせた式を短くしたいことと、マクロで関数を作る方法を覚えたく 教えを請いに参りました。 [[20070416165357]] こちらを参考に条件に見合う関数を組み合わせることができたのですが、データ項目が 多くブックが肥大化してしまう、また数式が見難いのでなんとか改善しようと考えています。
シート1 日報 日々追加されていきます。 D列の0と1はTimeが入力されていたら1を返すようになっています。 目的は作業列です。 __A______B_____ C_______D Code Time kg Check 1001 120 100 1 1001 60 0 1002 30 50 1 1002 300 0 ~~~~~~~~~~~~~~~~~~~~~~~~~ 1001 300 200 1 ←1000行
シート2 codeが順番に300項目あります。 300番の間には欠番もあります。 ___A_______B____ code TimeAverage 1001 1002 1003 1005 ~~~~~~~~~~~~~~~~ 2035
B2=IF(ISERRIR(SUMIF(シート1!$A$1:$A$10001$,A2,シート1!$B$1:$B$10001$)/SUMIF(シート1!$A$1:$A$10001$,A2,シート1!$D$1:$D$10001$)),"",SUMIF(シート1!$A$1:$A$10001$,A2,シート1!$B$1:$B$10001$)/SUMIF(シート1!$A$1:$A$10001$,A2,シート1!$D$1:$D$10001$))
現在はこのように使っています。 実際は範囲に名前の定義をつけてもう少しだけ短くなっています。 Countifではtimeが入力されていなくてもカウントしてしまうので作業列をわざわざ設けています。 使用しているオフィスが2007なのでiferror関数を使えるのですが互換性を持たせたいのであえてif(iserror(),,)としています。
この長い数式を
B2=SUMIFAVERAGE(シート1!$A$1:$A$10001$,A2,シート1!$B$1:$B$10001$,シート1!$D$1:$D$10001$)
このようにしたいのです。
他のスレッドを参考に下記のように仮説を立てたのですが、変な動きされたら怖いので
先に質問させていただきました。
Function SUMIFAVERAGE(A as Range,B as String,C as Range,D as Range)
SUMIFAGERAGE = SUMIF(A,B,C)/SUMIF(A,B,D)
End Function
また、作業列が必要ない方法もありましたら教えてください。
よろしく御願いいたします。
こんにちは。かみちゃん です。
ちょっと確認させてください。
> マクロで関数を作る
あくまでユーザー定義関数にしたいということですか？ マクロということは、数式を使わず、マクロで処理してしまうという考えはないということでしょうか？
(かみちゃん) 2009-05-03 8:28
このような計算をしたいということでしょうか? (Hatch)
Function SUMIFAVERAGE(A As Range, B As String, C As Range)
Dim x, y, z
x = Application.SumIf(A, B, C)
For Each y In A
If y.Value = B And y.Offset(0, 1).Value <> "" Then
z = z + 1
End If
Next
SUMIFAVERAGE = x / z
End Function
早速のお返事ありがとうございます。 こんなに早くお返事がいただけると思っていなかったので、ゆっくりしてしまいました^^;
かみちゃん様 マクロで処理してしまうのもよいのですが、今回はユーザー定義関数を自分で作ってみたかったので どういう形を組み立てればよいのか知りたかったのです。 ですのであくまで関数として使用したかったということです。
Hatch様 ありがとうございます。 見る限り私が考えていた通りの動作（今から仕事なので申し訳ないのですが、結果は夕刻ほどに なってしまいそうです。）をしていただけそうです。 いくつか教えていただきたいことがあるのですが > For Each y In A > If y.Value = B And y.Offset(0, 1).Value <> "" Then > z = z + 1 > End If > Next Eachの使い方がいまだにわかっておらず、上記の場合yの値がどのように変化していくのか教えて いただけますか？ また、ifの部分なのですが、 もしyの値がB且つyセルから↓に0セル→に1セルが空白ならばz+1 という解釈であっていますでしょうか？ こちらなのですが、どんな表にも適用できるようoffsetを使わずに処理したい場合は他にどのような 方法があるのか教えていただけますでしょうか？
わがままで申し訳ございませんがよろしくお願い申しあげます。
（みかん）
横から失礼します。
For Each は指定したオブジェクト要素（今回はセル）を一つずつ見ていく というものです。 ですから、A で シート1!$A$1:$A$10001 が指定されれば、A1からA10001までを 順番に y として処理していきます。
> If y.Value = B And y.Offset(0, 1).Value <> "" Then >もしyの値がB且つyセルから↓に0セル→に1セルが空白ならばz+1 >という解釈であっていますでしょうか？ は yの値（シート１のA列のn番目のセル)の値が B（シート２のA2）で yの右隣のセル（シート１のB列のn番目のセル）が空でなければ、z を１増やす という処理です。
蛇足ながら、
SUMIFAVERAGE = x / z
は
If z>0 Then
SUMIFAVERAGE = x / z
End If
としておいた方が良いでしょうか。
引数Cは使っていないのですが、計算対象が固定であれば不要ですし、
「C列分だけ横を参照する」とするなら、関数を
Function SUMIFAVERAGE(A As Range, B As String, C As Long)
Dim y As Range
Dim x As Double, z As Double
For Each y In A
If y.Value = B And y.Offset(0, C).Value <> "" Then
z = z + 1
x = x + y.Offset(0, C).Value
End If
Next
If z>0 Then
SUMIFAVERAGE = x / z
End If
End Function
とすれば、Time 列が変更になった時も応用が利きますね。
(Mook)
引数を４つ指定して B2=SUMIFAVERAGE(シート1!$A$1:$A$10001,A2,シート1!$B$1:$B$10001,シート1!$C$1:$C$10001) の様に使用します。
'------
Function SUMIFAVERAGE(A As Range, B As String, C As Range, D As Range)
Dim x
If B = "" Then B = """"""
x = Evaluate( _
"SUMPRODUCT((" & A.Parent.Name & "!" & A.Address & "=" & B & ")*" _
& "(" & C.Parent.Name & "!" & C.Address & "<>"""")," _
& D.Parent.Name & "!" & D.Address & ")")
If x > 0 Then
SUMIFAVERAGE = Application.SumIf(A, B, C) / x
Else
SUMIFAVERAGE = ""
End If
End Function
'------
(HANA)
> どんな表にも適用できるようoffsetを使わずに処理したい場合は他にどのような方法があるのか
Offsetは安易すぎでしたね・・・、ワークシート関数を利用せずに計算の理屈だけで
コード化すればこんな感じでは? スピードなどはどうなるかわかりませんが(^^ゞ
(Hatch)
Function SUMIFAVERAGE(A As Range, B As String, C As Range)
Dim i As Long
Dim x1 As Variant, x2 As Variant
Dim y As Double, z As Double
x1 = A
x2 = C
For i = LBound(x1) To UBound(x1)
If x1(i, 1) = B And x2(i, 1) <> "" Then
y = y + x2(i, 1)
z = z + 1
End If
Next
If z > 0 Then
SUMIFAVERAGE = y / z
Else
SUMIFAVERAGE = "エラー"
End If
End Function
あ・・・よく見ると、C列のkgは関係無いのですね。。。。
済みません。 引数は３つで B2=SUMIFAVERAGE(シート1!$A$1:$A$10001,A2,シート1!$B$1:$B$10001) の様に使用します。
'------
Function SUMIFAVERAGE(A As Range, B As String, C As Range)
Dim x
If B = "" Then B = """"""
x = Evaluate( _
"SUMPRODUCT((" & A.Parent.Name & "!" & A.Address & "=" & B & ")*" _
& "(" & C.Parent.Name & "!" & C.Address & "<>""""))")
If x > 0 Then
SUMIFAVERAGE = Application.SumIf(A, B, C) / x
Else
SUMIFAVERAGE = ""
End If
End Function
'------
(HANA)
夕刻には返信できるとしながら遅れて申し訳ございません。 身内に不幸がありまして、しばらく確かめる時間と気持ちがついていけなさそうです。 質問しておいてこんな体たらくで申し訳ございませんが、皆様に教えていただいたコードや 考え方は絶対に無駄に致しません。
もうしばらくお時間ください。
(みかん)
おはようございます。 結局、あまり眠れず気を紛らわす道具にしてしまいました。 動機が不純で大変申し訳なく思います。 一通り自分なりの解答を得ましたのでご報告いたします。
'---- Function SUMIFAVERAGE(A As Range, B As String, C As Long, Optional D As Variant = "") '検索値の合計の平均を求めるユーザー関数 'SUMIFAVERAGE=(検索範囲,検索値,相対合計列位置,無視数[省略可能]) '検索範囲 = 検索したい文字列がある1列のみ範囲選択 '検索値 = 検索したい値を直接入力、またはセルを参照 '相対合計位置 = 検索範囲列を1として、からn列分移動した列の合計 '無視数[省略可能] = 入力した数字以下を合計と平均の対象から外す ' 省略した場合、全ての数字を対象にする
Dim y As Range
Dim x As Double, z As Double
Application.Volatile '自動再計算を行う
For Each y In A
If Not IsNumeric(y.Offset(0, C - 1).Value) = True Then '数字じゃない場合
SUMIFAVERAGE = "Error：" & y.Offset(0, C - 1).Address & "数値ではありません。"
Exit Function
End If
If y.Value = B And y.Offset(0, C - 1).Value <> "" Then '検索値がBである且つ参照が空白ではない場合
If Not y.Offset(0, C - 1).Value <= D Or D = "" Then '参照がD以下ではない場合または空白の場合
z = z + 1
x = x + y.Offset(0, C - 1).Value
End If
End If
Next
If z > 0 Then
SUMIFAVERAGE = x / z
Else
SUMIFAVERAGE = 0
End If
End Function
'----
元にしたのはMook様の助言とコードでした。 いつも思っていたのですが、SUMIFにはなんで配列が二つ必要なんだろう？という疑問でした。 ですので ＞引数Cは使っていないのですが、計算対象が固定であれば不要ですし、 ＞「C列分だけ横を参照する」とするなら、関数を と言われたとき、固定観念に囚われていたんだなと目から鱗でした。 また、解釈の説明もとても分かりやすかったです。 Mook様ありがとうございます。
HANA様 サンプルデータとして、 A 100 A A 100 =SUMIFAGERAGE(A1:A5,C1,B1:B5) A 100 B 100 B 100 としましたが、#VALUE!エラーが出てしまいました。 コードの内容はEvaluateでsumproduct関数を計算させ、分母を出しsumifで最後に計算させている、 所までは理解できました。 たぶんエラーが出てしまう原因は「シートを跨がないため」だろうと推測しています。 VBA上でワークシート関数を使うときに今後参考にさせていただこうと思います。 ありがとうございました。
Hatch様 拙い質問に対し、100%答えていただきありがとうございました。 結果として別の方法で解決してしまい大変申し訳ないです。 最初に答えていただけたのがとても心強く、またコードは読みやすくいつも勉強にさせていただいております。 ありがとうございました。
自分で書いていていくつか気になったところも最後にあわせて質問させてください。
１）SUMIFはなぜ配列が二つ必要なのか ２）上記コード内におけるVariant型の以外の解決方法はないでしょうか？ ３）エラー処理するとしたらあとは何が必要でしょうか？
以上よろしく御願いいたします。 （みかん）
> １）SUMIFはなぜ配列が二つ必要なのか 条件が一致するか否かを探しているセル範囲が一つの配列 合計するセル範囲が二つ目の配列 として、計算するためでは？ Vlookupのような位置関係でも良さそうな気もしますけど、そのような設計にしただけだったりして・・・ それこそ、作った人に聞いて・・・といった感じでは?
> ２）上記コード内におけるVariant型の以外の解決方法はないでしょうか？ 質問の意味が分かりません。
> ３）エラー処理するとしたらあとは何が必要でしょうか？ どのようなことが考えられるのかは、そのシートがどのような運用をされるのかが分からないことには 何とも言えないと思います。 私は人に使ってもらってから付け加えることが多いです。あまり深く考えて作っていないからですけど(^^ゞ 以上、私見でした。 (Hatch)
横からおはようございます。
１）SUMIFはなぜ配列が二つ必要なのか 必要というわけではないでしょうが、別々に指定できると以下のような計算もできるということで、ご参考まで。
R/C A B 1 コード 1 2 金額 1000 3 コード 2 4 金額 2000 5 コード 1 6 金額 3000 7 8 4000
B8 =SUMIF(B1:B5,1,B2:B6)
検索範囲と合計範囲を１行ずらすことで、うまく該当する金額を合計できますね。 (コタ)
そうですね。 ＞エラーが出てしまう原因は「シートを跨がないため」だろうと推測しています。 エラーが出てしまう原因は、最初に載せられたCodeは数値に成っていますが 再度試されたデータは文字列に成っている為です。 数値でしかテストしなかったので。。。 （シート間参照は関係有りません。）
SUMPRODUCT関数を作成する部分を変更すれば 良いかもしれません。
x = Evaluate( _
"SUMPRODUCT((" & A.Parent.Name & "!" & A.Address & _
IIf(IsNumeric(B), "=" & B & ")*", "=""" & B & """)*") _
& "(" & C.Parent.Name & "!" & C.Address & "<>""""))")
一応お伺いしてみますが、計算させる範囲はどの位有るのでしょう？ 実際も最初に書いて居られる「$1:$10001」の範囲で計算させ なおかつこの式を多用するのなら 試しておかれるのが良いと思います。 計算に時間が掛かるくらいなんて事無いのなら、気にする事も無いと思いますが もしかするとセルに直接 =IF(SUMPRODUCT((A1:A10000=C1)*(B1:B10000<>""))>0, SUMIF(A1:A10000,C:C,B1:B10000)/SUMPRODUCT((A1:A10000=C1)*(B1:B10000<>"")),"") の数式を入れて計算させるのが一番早いかもしれません。 各参照範囲を正しく指定する（させる？）のは、大変かもしれませんが。
(HANA)
単純に
=SUMIFAVERAGE(A2,シート1!$A$1:$A$10001,シート1!$B$1:$B$10001,シート1!$D$1:$D$10001)
Function SUMIFAVERAGE(ByVal myVal, ParamArray a()) As Double
Dim b, r As Range, i As Long, mySum As Double, myCount As Long
For Each r In a
b = r.Value
For i = 1 To UBound(a, 1)
If b(i, 1) = myVal Then
mySum = mySum + a(i, 2)
myCount = myCount + 1
End If
Next
Next
SUMIFAVERAGE = mySum / myCount
End Function
とか？
(seiya)
統合で処理するものもどうでしょう？ (PENSIONER)
Sub Macro1()
Sheets("Sheet2").Range("A2:B300").ClearContents
Sheets("Sheet2").Range("A2").Consolidate Sources:="Sheet1!R2C1:R1000C2", _
Function:=xlAverage, TopRow:=False, LeftColumn:=True, CreateLinks:=False
End Sub
返信が遅くなり申し訳ございません。
Hatch様 言葉が足りず申し訳ないです。 ＞Varianto型 についてですが、 ＞Optional D As Variant = "" このDを省略した場合、現在は数値ではなく空白にして下記のように条件分岐しております。 ＞If Not y.Offset(0, C - 1).Value <= D Or D = "" Then 数値以外には考えられませんので型はIntegerかLongにしておきたいのです。 （不要なエラーを避けるため） なので２）のような質問になりました。 １）については確かに仕様を作った人に聞けって話ですよね； ３）運用の仕方も千差万別ですし、全てにおいてこうであるべきというのは無いでしょうが、マクロを 知らない人が使う予定もありますのでデバッグやマクロのエラーにしたくなかったのです。 こちらも言葉足らずで申し訳ありませんでした。
コタ様 確かにそのような表でしたら一番シンプルでいいですね！ 似たような表で作業列を設けて計算していたものがありましたので、早速試してみたいと思います。 ありがとうございました。
HANA様 申し訳ございません。 仕様を説明するときにCodeを文字列とすることをお伝えし忘れていました。 ご質問の計算させる表ですが、新しく作成した作業日報に使う予定です。 今現在は4月末からのデータ（およそ100行）しかございませんが、1日多くて10行ずつ増えていく予定です。 10000行ですと1000日、約3年強分のデータとなりますので、現実的ではありません。 およそ2000行を運用の基準と考えております。 余談なのですが、2007の互換モードで作成した.xlsファイルが1.4MBととても重いのですが、 バイナリモードで保存すると131KBとものすごく軽くなります。 使う人に2007の使用を強要させて軽いファイルを読ませるか、それとも互換性重視で保持するほうが 良いのか迷ってしまいました。 直接計算時の処理の重さとは関係ないのですがどうにか互換モードでも軽くならないのでしょうか…
seiya様 ちゃんと動いた。。。のですがコードを読んでもどこがどの値になっているのか分からなくて悩んでいますorz 今日初めてVBAを触る人向けの本を購入致しましたので、どのような処理をしているのか勉強してみたいと思います。 ありがとうございました。
PENSIONER様 お返事ありがとうございます。 それからごめんなさい… 実はVBAの基本すら知らずにコードを切り貼りしているので統合で処理と言われても、コードのどこを 読んでもさっぱり分からなくて試せないでいます こちらも同じく一つ一つどんな処理しているのか勉強してみます。
みなさん本日はとても勉強になりました。 本当にありがとうございました。 (みかん)
>このDを省略した場合、現在は数値ではなく空白にして下記のように条件分岐しております。 >＞If Not y.Offset(0, C - 1).Value <= D Or D = "" Then >数値以外には考えられませんので型はIntegerかLongにしておきたいのです。 D は小さい数値を計算から除外するためのオプションでしょうか。
D を整数型にするのであれば、 Function SUMIFAVERAGE(A As Range, B As String, C As Long, Optional D As Long = -1 ) としておけば、条件判定自体を省略できると思います。
また Not y.Offset(0, C - 1).Value <= D は y.Offset(0, C - 1).Value > D の方が分かりやすいかと思います（ >= と > のどちらを使うべきかは仕様しだいですが）。
面倒ですが、長く大量のデータで運用するのであれば、今のうちにいろいろな ケースでテストしておいた方が良いですね（性能面も含めて）。 結果的には、その方が後々苦労しなくてすみます。 (Mook)
Macro1()は、標準モジュール貼り付け、実行するものです。 以下の手順をマクロの記録で作成し、手を加えました。
Sheet2のA2を選択し、データ、統合として、 集計の方法に平均、統合元範囲にSheet1!$A$2:$B$1000を追加して登録し、 統合の基準の左端列にチェックして、ＯＫをクリックしますと、 Sheet2のA2:B2以下にコード別に平均が表示されます。 (PENSIONER)
> seiya様 >ちゃんと動いた。。。のですがコードを読んでもどこがどの値になっているのか分からなくて悩んでいますorz > 今日初めてVBAを触る人向けの本を購入致しましたので、どのような処理をしているのか勉強してみたいと思います。 > ありがとうございました。
私のコードは第二引数に、ParamArray (パラメーターアレイ) という配列を使用しています。 引数の数を 限定しない/できない ような場合にとても重宝します。
=SUMIFAVERAGE(A2,$A$1:$A$10001,$B$1:$B$10001,$D$1:$D$10001,$C$1:$D$500,$C$200:$D$2000) というように、第一引数 A2 の後はすべて第二引数になります、増減することができます。
第二引数はカンマで区切られた部分を要素にした a() という配列に渡されますので、 For Each r In a という具合に、それぞれの要素（この場合、範囲）を処理速度を上げるため、 b という配列 に格納し、処理をしています。 (seiya)
Mook様 お返事ありがとうございます。 -1にすることは考えたのですが、マイナスの平均があるかも知れないと考えたときに数値以外かなと 思い込んでしまいました。 実際どれほどマイナスを使う需要があるのか、自分が使っていく上で考えてしまいましたが特になさそう でしたので必要なときに随時書き換えという形で使っていこうと思います。 条件にNotを使ったのは日本語にしたとき自分が分かりやすいからで特に意識したわけではないのです^^; いかんせん頭の出来が悪いので。。。。 アドバイスありがとうございました。
PENSIONER様 これは便利ですね！ ただ、今回の件についてはまた別のアプローチですので他の作業で重宝しそうです。 現在使用しているフォーマットと過去使用しているフォーマットが違い、集計が大変だったのですが これを少し手を加えてあげればずっと作業時間が短く出来ると思います。 ありがとうございました。
seiya様 詳しいご説明ありがとうございます。 昨日は動いたと思ったのですが、今日動かしたら ＞Function SUMIFAVERAGE3(NyVal myVal, ParamArray a()) As Double で「コンパイルエラー 配列をFor Eachで使用する場合はバリアント型でなければいけません」 というようなエラーが出てしまいました； 何か変な事してしまったのでしょうか
もう一点分からないことがあるのですが ＞b(i, 1) は配列Bの中の(i,1)ってことでしょうか？ そうすると ＞b = r.Value a.Value(i,1)ってことになるのでしょうか？ となると、Valueの中身はどのように変化するのですか？
分からないことばかりで申し訳ございません。 （みかん）
みかんさん
大変失礼しました。a(i, 2) は b(i, 2) です。
Function SUMIFAVERAGE(ByVal myVal, ParamArray a()) As Double
Dim b, r, i As Long, mySum As Double, myCount As Long '変数rをVariant型にしました
For Each r In a '<- 引数で渡された配列にループ
b = r.Value '<- 要素を配列bに格納
For i = 1 To UBound(a, 1) '<- 配列bにループ
If b(i, 1) = myVal Then '<- 配列の一列目の値が myVal の時
mySum = mySum + b(i, 2) '<- ここが a(i, 2) は X
' ↑ 同じ行の2列目をmySumに加算
myCount = myCount + 1 '<- カウンターを増加
End If
Next
Next
SUMIFAVERAGE = mySum / myCount
End Function
です。 (seiya) Typo修正 10:26
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.