ゴリラストロングの雑記帳

月収10万円の広告収入を目指す自称専業ライターのブログです。




【VBA】WorksheetFunction.AverageIfsの使い方の例。複数の条件に合う数値を平均する。グラフの作成も。




どうも、ゴリラストロングです。

 

今回は、複数の条件に合う数値を平均する例として、WorksheetFunction.AverageIfsの使い方の例を作成してみました。

 

<元データ>

f:id:gorilla-strong:20200321150041p:plain

 

<マクロ実行後>

f:id:gorilla-strong:20200321150143p:plain

 

f:id:gorilla-strong:20200321150227p:plain

 

f:id:gorilla-strong:20200321150311p:plain



 スポンサーリンク

 

 

ソースコード

Sub averageifs()

Dim macro As Object
Set macro = ThisWorkbook.Worksheets(1)

Dim lastrow As Long
lastrow = macro.Cells(Rows.Count, 1).End(xlUp).Row


Dim rng1, rng2, rng3 As Range
Set rng1 = macro.Range("A3:A" & lastrow)
Set rng2 = macro.Range("B3:B" & lastrow)
Set rng3 = macro.Range("C3:C" & lastrow)


Dim dannjo(1 To 2)
dannjo(1) = "男"
dannjo(2) = "女"

Dim gen(1 To 5)
gen(1) = 10
gen(2) = 20
gen(3) = 30
gen(4) = 40
gen(5) = 50

macro.Range("F3") = WorksheetFunction.averageifs(rng3, rng1, dannjo(1), rng2, "<" & gen(1) + 10)
macro.Range("F4") = WorksheetFunction.averageifs(rng3, rng1, dannjo(1), rng2, ">=" & gen(2), rng2, "<" & gen(2) + 10)
macro.Range("F5") = WorksheetFunction.averageifs(rng3, rng1, dannjo(1), rng2, ">=" & gen(3), rng2, "<" & gen(3) + 10)
macro.Range("F6") = WorksheetFunction.averageifs(rng3, rng1, dannjo(1), rng2, ">=" & gen(4), rng2, "<" & gen(4) + 10)
macro.Range("F7") = WorksheetFunction.averageifs(rng3, rng1, dannjo(1), rng2, ">" & gen(5))


macro.Range("G3") = WorksheetFunction.averageifs(rng3, rng1, dannjo(2), rng2, "<" & gen(1) + 10)
macro.Range("G4") = WorksheetFunction.averageifs(rng3, rng1, dannjo(2), rng2, ">=" & gen(2), rng2, "<" & gen(2) + 10)
macro.Range("G5") = WorksheetFunction.averageifs(rng3, rng1, dannjo(2), rng2, ">=" & gen(3), rng2, "<" & gen(3) + 10)
macro.Range("G6") = WorksheetFunction.averageifs(rng3, rng1, dannjo(2), rng2, ">=" & gen(4), rng2, "<" & gen(4) + 10)
macro.Range("G7") = WorksheetFunction.averageifs(rng3, rng1, dannjo(2), rng2, ">" & gen(5))






n = 1

p = 1

q = 1

r = 1


For i = 3 To 7

Dim fixrng, moverng As Range
Set fixrng = macro.Range("E2:G2")
Set moverng = macro.Range(Cells(i, 5), Cells(i, 7))


With macro.Shapes.AddChart.Chart
    .ChartType = xlColumnClustered
    .SetSourceData Union(fixrng, moverng)
End With

If n <= 2 Then

With ActiveSheet.ChartObjects(n)
    .Top = Cells(p, 9).Top
    .Left = Cells(p, 9).Left
    .Height = 200
    .Width = 200
    .Chart.HasLegend = False
End With

p = p + 12


ElseIf n >= 2 And n <= 4 Then

With ActiveSheet.ChartObjects(n)
    .Top = Cells(q, 13).Top
    .Left = Cells(q, 13).Left
    .Height = 200
    .Width = 200
    .Chart.HasLegend = False
End With

q = q + 12

ElseIf n = 5 Then
With ActiveSheet.ChartObjects(n)
    .Top = Cells(r, 17).Top
    .Left = Cells(r, 17).Left
    .Height = 200
    .Width = 200
    .Chart.HasLegend = False
End With


End If

n = n + 1


Next



End Sub

 

 

<参照させていただいたサイト>

https://uxmilk.jp/61739

http://officetanaka.net/excel/vba/graph/03.htm

http://officetanaka.net/excel/vba/graph/12.htm

 

 

以上、ゴリラストロングでした。

 

 スポンサーリンク