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

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

ただいまの
回答率

89.23%

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

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 1,087

lq_hm_165912

score 18

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

コード
Sub 集計()


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


    Call 縦軸項目設置
    Call 横軸項目設置


 '---変数を宣言---
    Dim cmax0, cmax1, cnt, x, j, k As Long
    Dim ws0, ws1 As Worksheet
    Dim tate, yoko, tate2, yoko2, way As String
    Dim goukei, ross As Long

    tate = Cells(4, 9)
    yoko = Cells(5, 9)
    way = Cells(6, 9)


    '---ワークシートを設定---
    Set ws0 = Worksheets("集計画面")
    Set ws1 = Worksheets("お試し集計")


    '---に格納---

'準備
   cmax0 = ws0.Cells(Rows.Count, "A").End(xlUp).Row
   cmax1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row

    cnt = ws1.Range("XFD10").End(xlToLeft).Column

    For k = 11 To cmax1

        tate2 = ws1.Range("A" & k).Value

        '---照合したい条件でデータベースを検索---
        For j = 0 To cnt - 4
            goukei = 0
            yoko2 = ws1.Range("C10").Offset(0, j + 1).Value

            For x = 2 To cmax0                           
                    If ws0.Range(tate & x).Value = tate2 Then
                        If ws0.Range(yoko & x).Value = yoko2 Then
                            goukei = goukei + ws0.Range(way & x).Value

                        End If
                    End If

            Next

            '---Resultシートの表に出力---
            ws1.Range("C" & k).Offset(0, j + 1).Value = goukei

        Next

    Next

    Application.ScreenUpdating = True
     Application.Calculation = xlCalculationAutomatic


End Sub

加重平均の式としては

コード

Sub ロス率計算()

 '===========================================  (仕損不良数量/消費数量合計)の加重平均

Dim rngN As String
Dim rngO As String
Dim Sh1 As Worksheet

rngN = Range("N1", Cells(Rows.Count, 1).End(xlUp)).Address
rngO = Range(rngN).Offset(, 1).Address
Set Sh1 = ThisWorkbook.Worksheets("集計画面")

With Sh1
Range("T1").Formula = "=SUMPRODUCT(" & rngN & "," & rngO & ")/SUM(" & rngO & ")"
End With


End Sub

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

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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • coco_bauer

    2019/02/18 13:17

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

    キャンセル

  • lq_hm_165912

    2019/02/18 13:20

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

    キャンセル

  • mattuwan

    2019/02/18 21:29

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

    キャンセル

  • coco_bauer

    2019/02/19 08:34

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

    キャンセル

回答 3

+1

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/26 09: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)

    としましたが上手く動かない現状です。

    キャンセル

  • 2019/02/26 09:44

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

    キャンセル

  • 2019/02/27 10:44

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

    キャンセル

check解決した方法

0

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

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

```VBA

Private Sub 仕損率集計()

Application.ScreenUpdating = False Application.Calculation = xlCalculationManual

 '---変数を宣言---     Dim cmax0, cmax1, cnt, x, j, k As Long     Dim ws0, ws1 As Worksheet     Dim tate, yoko, tate2, yoko2 As String     Dim goukei, suuryo As Long     Dim syuukei As Double

    tate = Cells(4, 9)     yoko = Cells(5, 9)

    '---ワークシートを設定---     Set ws0 = Worksheets("集計画面")     Set ws1 = Worksheets("お試し集計")

    '---格納---        cmax0 = ws0.Cells(Rows.Count, "A").End(xlUp).Row    cmax1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row

    cnt = ws1.Range("XFD10").End(xlToLeft).Column

    For k = 11 To cmax1

        tate2 = ws1.Range("A" & k).Value

        '---照合したい条件でデータベースを検索---         For j = 0 To cnt - 4             goukei = 0             suuryo = 0             syuukei = 0

            yoko2 = ws1.Range("C10").Offset(0, j + 1).Value

            For x = 2 To cmax0

                    If ws0.Range(tate & x).Value = tate2 Then                         If ws0.Range(yoko & x).Value = yoko2 Then

                           goukei = goukei + ws0.Range("N" & x).Value                            suuryo = suuryo + ws0.Range("V" & x).Value

                           If suuryo <> 0 Then

                           syuukei = Round(suuryo / goukei, 2)                         End If                     End If                 End If             Next

            '---Resultシートの表に出力---             ws1.Range("C" & k).Offset(0, j + 1).Value = syuukei

        Next

    Next

    Application.ScreenUpdating = True      Application.Calculation = xlCalculationAutomatic

End Sub```

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

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

  • ただいまの回答率 89.23%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る