質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

3回答

4705閲覧

VBAでの加重平均の求め方について

lq_hm_165912

総合スコア18

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2019/02/18 03:55

編集2019/02/19 00:23

他にも質問しているのですが、優先順位がこちらが先なのでまた質問させてください。
現在VBAで集計を行おうとしています。
合計に関しては下のコードで集計できていますが、合計ではなく加重平均を求めたいと思っています。

VBA

1コード 2Sub 集計() 3 4 5Application.ScreenUpdating = False 6Application.Calculation = xlCalculationManual 7 8 9 Call 縦軸項目設置 10 Call 横軸項目設置 11 12 13 '---変数を宣言--- 14 Dim cmax0, cmax1, cnt, x, j, k As Long 15 Dim ws0, ws1 As Worksheet 16 Dim tate, yoko, tate2, yoko2, way As String 17 Dim goukei, ross As Long 18 19 tate = Cells(4, 9) 20 yoko = Cells(5, 9) 21 way = Cells(6, 9) 22 23 24 '---ワークシートを設定--- 25 Set ws0 = Worksheets("集計画面") 26 Set ws1 = Worksheets("お試し集計") 27 28 29 '---に格納--- 30 31'準備 32 cmax0 = ws0.Cells(Rows.Count, "A").End(xlUp).Row 33 cmax1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row 34 35 cnt = ws1.Range("XFD10").End(xlToLeft).Column 36 37 For k = 11 To cmax1 38 39 tate2 = ws1.Range("A" & k).Value 40 41 '---照合したい条件でデータベースを検索--- 42 For j = 0 To cnt - 4 43 goukei = 0 44 yoko2 = ws1.Range("C10").Offset(0, j + 1).Value 45 46 For x = 2 To cmax0 47 If ws0.Range(tate & x).Value = tate2 Then 48 If ws0.Range(yoko & x).Value = yoko2 Then 49 goukei = goukei + ws0.Range(way & x).Value 50 51 End If 52 End If 53 54 Next 55 56 '---Resultシートの表に出力--- 57 ws1.Range("C" & k).Offset(0, j + 1).Value = goukei 58 59 Next 60 61 Next 62 63 Application.ScreenUpdating = True 64 Application.Calculation = xlCalculationAutomatic 65 66 67End Sub 68 69

加重平均の式としては

VBA

1コード 2 3Sub ロス率計算() 4 5 '===========================================  (仕損不良数量/消費数量合計)の加重平均 6 7Dim rngN As String 8Dim rngO As String 9Dim Sh1 As Worksheet 10 11rngN = Range("N1", Cells(Rows.Count, 1).End(xlUp)).Address 12rngO = Range(rngN).Offset(, 1).Address 13Set Sh1 = ThisWorkbook.Worksheets("集計画面") 14 15With Sh1 16Range("T1").Formula = "=SUMPRODUCT(" & rngN & "," & rngO & ")/SUM(" & rngO & ")" 17End With 18 19 20End Sub 21

この式を上手く使えないかと考えていますが上手くいっていません。
やりたいこととしましては、集計したい縦軸と横軸(都道府県×製品ごとなど)を条件にして
集計画面のN列とO列を使って加重平均を求めたいです。(N列*O列/O列)
way = 20 の場合のみ計算させたいので、For x = 2 To cmax0 の前にIf式を入れるイメージです。
よろしくお願いします。

イメージ説明
上が集計するシートで下がデータになります。
wayの値は右上の黄色で塗りつぶした部分で変動するようにしています。
データシートは省略していますが、上に書いている列にあります。赤字の部分は式が入っている部分です。
①の抽出準備を押すと、縦軸と横軸の設定が出来るので縦軸と横軸と合計値の表示が変わります。
②の計算を押すと選択した集計方法によって集計シートの数量が変わります。
データシートは都道府県順ではないです。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

coco_bauer

2019/02/18 04:17

SUMPRODUCTとSUMを使って加重平均を計算しようとしているのは正しい方向だと思います。 しかし『上手くいっていません』だけでは何が起きているのか判らないので回答できません。 何が問題なのですか? どこで困っているのですか????
lq_hm_165912

2019/02/18 04:20

提示した式は、すべての条件での加重平均しか求められません。 やりたいこととして書いてあります通り、指定した条件内での加重平均を求めたいと思っています。 現状は全データの加重平均しか求められないのが問題です。
mattuwan

2019/02/18 12:29

>指定した条件内での加重平均 う~ん。ちょっと条件がなんなのかイメージできません。 元データと結果のシート上のイメージを提示してみませんか? 画像でも文字でもいいですが。
coco_bauer

2019/02/18 23:34

「way = 20 の場合のみ計算させたい」と質問には書いてますが、wayの値は"way = Cells(6, 9)"で決まる。でも、wayの値が20かどうかを判断するIF文などがプログラムには含まれていません。 何がしたいのかを整理しては如何ですか? 「集計したい縦軸と横軸(都道府県×製品ごとなど)」の製品とか都道府県って何なんですか???  使っているワークシートがどうなっているのかを示していない事に気づいていますか?
guest

回答3

0

for文の中で値を取得して、それから加重平均を出したいと仰っていますが、最初から加重平均を求める関数は用意されているので、最初から範囲だけ決定させてから、その範囲の開始地点と終了地点だけ引数に渡し、値を取得する方法が、ExcelVBAでは主流のやり方です。

Sub calcLoss(ByVal startRow As String,ByVal EndRow As String,ByVal EndCol as String ) '===========================================  (仕損不良数量/消費数量合計)の加重平均 Dim result As Integer Set Sh1 = ThisWorkbook.Worksheets("集計画面") With Sh1 Range("T1").Formula = "=SUMPRODUCT(" & RANGE(startAddr,endAddr) & ")/SUM(" & endAddr & ")" End With End Sub

配列の左上(開始地点)、右上(最終行)、左下(最終地点)の引数を取得して、
こんな感じで関数を作ってはいかがでしょうか。

あと、上のサンプルですが、式が間違っていたので調整しています。
SUMPRODUCTはEXCELの関数なので、SUMPRODUCT(開始アドレス:最終アドレス)としないといけません。
もし(開始アドレス,最終アドレス)として出したい場合は上記のようにRANGE()をはさみます(VBAの関数なので、こっちは文字列にしてはいけません)

ややこしいのはエクセルの関数式とVBAの関数式は別物であり、VBAでEXCELの関数式を利用する場合は、文字列と同じ扱いをしないといけないということです。

投稿2019/02/18 10:04

編集2019/02/26 06:59
FKM

総合スコア3608

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

lq_hm_165912

2019/02/20 00:01

Sub calcLoss(Dim startAddr As String,Dim EndAddr As String ) 最初の部分でエラーになります。原因を探しましたがわかりませんでした。。
FKM

2019/02/20 00:09

ちょっと式を手直ししました。ちょっと怪しい部分はコメントを参考に、自分で調べていただけたらと思います。
lq_hm_165912

2019/02/20 08:01

どうもうまくいきません。そもそも連続したデータではないので、検索結果を別シートに抽出してSUMPRODUCTでの計算結果を戻したほうが良さそうですね。 もう少し頑張ってみます。
FKM

2019/02/20 09:32 編集

計算結果出せましたので、あとは任意のアドレスをいれてください。 そして、元の式も誤りがあることもわかりましたので、修正しています。あとはそのまま決め打ちで引数を指定するなり、最終行、列を式で取得するなりすれば大丈夫です
lq_hm_165912

2019/02/21 00:52

んー、提示いただいた式を上の式とどう連携すればいいか想像もつかないのでもう少し考えてみます。。。
FKM

2019/02/21 01:59 編集

確認ですけど、よくよく見たら計算式使わなくても合計値がグラフ上に出ていますよね? 加重平均の計算式を求めるSUMPRODUCT()は、行列の合計値を出すために用いるものなので 使う必要ないんじゃないかと思います。 N列の北海道の場合 番号が12、しかし今回は見えていない都道府県番号20の工場での値を出したい という解釈でOkですか?だとしたらINDEXかOFFSETを使えば簡単に出ます。 sum_products = INDEX(番号列の先頭アドレス:仕損不良列の最終アドレス,MATCH(20,番号の列,FALSE),1,FALSE) sum_loss = INDEX(番号列の先頭アドレス:仕損不良列の最終アドレス,MATCH(20,番号の列,FALSE),2,FALSE) result = sum_products / sum_loss
FKM

2019/02/21 02:09

追記で、品目を変えたい場合は、上記のように 「番号列の先頭アドレス:仕損不良列の最終アドレス」の部分に、さっきの数式を当てはめてみると いいと思います。そして、アドレスの取得方法を同じようにINDEXを使えば、対応した番号(今回なら20) の値を取得できますよ
lq_hm_165912

2019/02/21 02:14

説明不足ですみません。 O列/N列を計算したものがロス率になると思います。(一気に計算しようとしていましたが、試行錯誤した結果現在S列で計算しています。) SUMPRODUCTの式にすると、SUMPRODUCT(S列,N列)/SUM(N列)となると思います。 N列が北海道のときの場合は、その中の製品群Aの場合のロス率の加重平均がC9セル、製品群Bの場合はD9・・・と、選択した縦軸と横軸の2つの条件に一致する加重平均を求めたいです。 (北海道製品群Aが2000個で不良5個、3000個で100個の場合は、0.021で0.21%・・・といった感じです。
lq_hm_165912

2019/02/21 02:16

返信している間に補足ありがとうございます。 軸も合計値も変更できるようにしているのがそもそも無理があるのかもしれません。 教えていただいた内容で確認してみます。
FKM

2019/02/21 05:39

軸も合計値も不定でも大丈夫ですよ。OFFSET,MATCH,INDEX関数を使いわければいけます。
FKM

2019/02/21 05:41

コメントだと画像貼れなかったので、別アンサーにてサンプル画像貼ってます。 やりたいことはこういうことでしょうか?
lq_hm_165912

2019/02/21 07:20

画像までありがとうございます。 私が前貼った画像が少しおかしいですね。 (右上の製品群Aではなく製品群すべて、です。) 自分がやりたかったことよりとても良いのですが、製品が100種類ほどある場合は見づらくなってしまいそうですか?(100行が2つ縦に並ぶので) 各都道府県ごとの加重平均は番号の横に出すようにすれば問題ないでしょうか。。 質問ばかりですみません。。
FKM

2019/02/21 07:47 編集

その場合はシートをわければいいですよ。シート名!A1:A20 とかできますので。そして番号は インデックスとなりうるので、一番左側においておくと便利です (番号、対象製品群、消費数量、仕損不良数量、平均値) と並べたらいいでしょう。 また、OFFSET関数は非常に便利で、 OFFSET(始点、始点から移動したい縦の行数、始点から移動したい横の列数、移動した場所から取得したい縦の行数、移動した場所から取得したい横の列数)とできるので、 番号さえ一致させれば、好きな値を取得できます。
lq_hm_165912

2019/02/21 08:04

すみません、勉強不足で最終的にどのようなマクロ・関数を使えばいいかわかっていません。。。 ご教授いただけますか・・? シートは消費数量/不良数量/不良金額/不良ロス率と合計値は変動させずにシートにすべて出力した方が良いので、変動は縦と横のみにします。最初のマクロに組み込んでいく感じだと思うので試行錯誤してみます。
FKM

2019/02/21 08:34

北海道:番号12、青森:番号24…という風に場所ごとに製品群Aの加重平均、製品群Bの加重平均…の総平均値を出していくという解釈でよろしいでしょうか。
lq_hm_165912

2019/02/25 01:06

縦軸を場所、横軸を製品群にした場合はそのような見え方になると思います。 金曜日は出張でいなかったので今から頑張ってみます
lq_hm_165912

2019/02/26 00:40

最初に提示した式の goukei = goukei + ws0.Range(way & x).Value ここをSUMPRODUCTにすればシート増やさなくてもいいのでは、となり goukei = goukei + ws0.SumProduct(ws0.Range("N" & x).Value, ws0.Range("U" & x).Value) / ws0.Sum(ws0.Range("U" & x).Value) としましたが上手く動かない現状です。
FKM

2019/02/26 00:44

一番最初の私のコメントを読みましょう。EXCELの関数とVBAの関数をごっちゃにしてはいけません。
lq_hm_165912

2019/02/27 01:44

攻め方を変えることで解決しました! ありがとうございます
guest

0

自己解決

試行錯誤しましたが解決しました。
無駄な部分などありましたらご指摘ください。

(加重平均のためにV列を追加しました)

VBA

1 2Private Sub 仕損率集計() 3 4Application.ScreenUpdating = False 5Application.Calculation = xlCalculationManual 6 7 8 '---変数を宣言--- 9 Dim cmax0, cmax1, cnt, x, j, k As Long 10 Dim ws0, ws1 As Worksheet 11 Dim tate, yoko, tate2, yoko2 As String 12 Dim goukei, suuryo As Long 13 Dim syuukei As Double 14 15 tate = Cells(4, 9) 16 yoko = Cells(5, 9) 17 18 '---ワークシートを設定--- 19 Set ws0 = Worksheets("集計画面") 20 Set ws1 = Worksheets("お試し集計") 21 22 '---格納--- 23 cmax0 = ws0.Cells(Rows.Count, "A").End(xlUp).Row 24 cmax1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row 25 26 cnt = ws1.Range("XFD10").End(xlToLeft).Column 27 28 For k = 11 To cmax1 29 30 tate2 = ws1.Range("A" & k).Value 31 32 '---照合したい条件でデータベースを検索--- 33 For j = 0 To cnt - 4 34 goukei = 0 35 suuryo = 0 36 syuukei = 0 37 38 yoko2 = ws1.Range("C10").Offset(0, j + 1).Value 39 40 For x = 2 To cmax0 41 42 If ws0.Range(tate & x).Value = tate2 Then 43 If ws0.Range(yoko & x).Value = yoko2 Then 44 45 goukei = goukei + ws0.Range("N" & x).Value 46 suuryo = suuryo + ws0.Range("V" & x).Value 47 48 If suuryo <> 0 Then 49 50 syuukei = Round(suuryo / goukei, 2) 51 End If 52 End If 53 End If 54 Next 55 56 '---Resultシートの表に出力--- 57 ws1.Range("C" & k).Offset(0, j + 1).Value = syuukei 58 59 Next 60 61 Next 62 63 Application.ScreenUpdating = True 64 Application.Calculation = xlCalculationAutomatic 65 66 67End Sub```

投稿2019/02/27 01:46

lq_hm_165912

総合スコア18

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

こんな感じでサンプル作ってみました。やりたいことはこういうことだと思いますが

双方が固定されていない数値の場合

投稿2019/02/21 05:40

FKM

総合スコア3608

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問