VBA在列中找到相同值的范围并计算平均值 [英] VBA find a range of same values in a column and calculate average

问题描述

我想在 A列中找到一个相同值的范围，然后将其计算为平均值，有人可以帮助我吗?代码下方:

解决方案2

另一种更简单的方法是使用公式.在 Cell E2

中输入以下公式

  = AVERAGEIF($ A $ 2:$ A $ 11，D2，$ B $ 2:$ B $ 11) 

根据需要拖动/复制下来.根据您的数据更改范围.

有关 AVERAGEIF 的详细信息，请参见此.

---

1

  Sub test()昏暗作为工作表输入LR为长暗，输出LR为长暗Dimcel作为范围，aRng作为范围，bRng作为范围Dim dict作为对象，c作为变量，i只要设置字典= CreateObject("Scripting.Dictionary")Set sht = ThisWorkbook.Worksheets("Sheet1")'您的数据表随便inputLR = .Cells(.Rows.Count，"A").End(xlUp).Row'A列中的最后一行在A列中设置aRng = .Range(.Cells(2，1)，.Cells(inputLR，1))'数据范围在B列中设置bRng = .Range(.Cells(2，2)，.Cells(inputLR，2))'数据范围c = aRng对于i = 1到UBound(c，1)dict(c(i，1))= 1接下来我.Range("D2").Resize(dict.Count)= Application.Transpose(dict.keys)'显示列A中的唯一身份outputLR = .Cells(.Rows.Count，"D").End(xlUp).Row'D列中的最后一行对于.Range(.Cells(2，4)，.Cells(outputLR，4))中的每个cel'在D列中的每个单元格中循环cel.Offset(0，1)= Application.WorksheetFunction.AverageIf(aRng，cel，bRng)'计算平均值下一个结束于结束子 

---

2 以获得 Min 而不是

 对于.Range(.Cells(2，4)，.Cells(outputLR，4))中的每个cel'在D列中的每个单元格中循环cel.Offset(0，1)= Application.WorksheetFunction.AverageIf(aRng，cel，bRng)'计算平均值下一个 

使用

 对于.Range(.Cells(2，4)，.Cells(outputLR，4))中的每个cel'在D列中的每个单元格中循环cel.Offset(0，1).FormulaArray ="= MIN(IF("& aRng.Address&"="& cel.Value&，"& bRng.Address&))"下一个.Range(.Cells(2，4)，.Cells(outputLR，4)).Offset(0，1).Value = .Range(.Cells(2，4)，.Cells(outputLR，4)).Offset(0，1).值 

I want to find a range of same values in column A , and then calculate it average , can anyone help me ? below the code :

https://i.stack.imgur.com/bU1hW.png

Sub test()
    Dim sht As Worksheet
    Dim LastRow As Long
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow
Columns("A:A").Select
    Set cell = sELECTION.Find(What:="i", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If cell Is Nothing Then
    'do it something

Else
    'do it another thing
End If

End Sub

Thanks !

解决方案

Solution 1

Try this

Sub test()
    Dim sht As Worksheet
    Dim inputLR As Long, outputLR As Long
    Dim cel As Range, aRng As Range, bRng As Range

    Set sht = ThisWorkbook.Worksheets("Sheet1") 'your data sheet

    With sht
        inputLR = .Cells(.Rows.Count, "A").End(xlUp).Row    'last row in column A
        outputLR = .Cells(.Rows.Count, "D").End(xlUp).Row   'last row in column D
        Set aRng = .Range(.Cells(2, 1), .Cells(inputLR, 1)) 'data range in column A
        Set bRng = .Range(.Cells(2, 2), .Cells(inputLR, 2)) 'data range in column B

        For Each cel In .Range(.Cells(2, 4), .Cells(outputLR, 4))   'loop through each cell in Column D
            cel.Offset(0, 1) = Application.WorksheetFunction.AverageIf(aRng, cel, bRng) 'calculate average
        Next cel
    End With
End Sub

See image for reference.

Solution 2

Another easier approach will be to use formula. Enter the following formula in Cell E2

=AVERAGEIF($A$2:$A$11,D2,$B$2:$B$11)

Drag/Copy down as required. Change range as per your data.

For details on AVERAGEIF see this.

---

EDIT : 1

Sub test()
    Dim sht As Worksheet
    Dim inputLR As Long, outputLR As Long
    Dim cel As Range, aRng As Range, bRng As Range
    Dim dict As Object, c As Variant, i As Long

    Set dict = CreateObject("Scripting.Dictionary")
    Set sht = ThisWorkbook.Worksheets("Sheet1") 'your data sheet

    With sht
        inputLR = .Cells(.Rows.Count, "A").End(xlUp).Row    'last row in column A
        Set aRng = .Range(.Cells(2, 1), .Cells(inputLR, 1)) 'data range in column A
        Set bRng = .Range(.Cells(2, 2), .Cells(inputLR, 2)) 'data range in column B

        c = aRng
        For i = 1 To UBound(c, 1)
            dict(c(i, 1)) = 1
        Next i
        .Range("D2").Resize(dict.Count) = Application.Transpose(dict.keys)  'display uniques from column A
        outputLR = .Cells(.Rows.Count, "D").End(xlUp).Row   'last row in column D

        For Each cel In .Range(.Cells(2, 4), .Cells(outputLR, 4))   'loop through each cell in Column D
            cel.Offset(0, 1) = Application.WorksheetFunction.AverageIf(aRng, cel, bRng) 'calculate average
        Next cel
    End With
End Sub

---

EDIT : 2 To get Min, instead of

For Each cel In .Range(.Cells(2, 4), .Cells(outputLR, 4))   'loop through each cell in Column D
    cel.Offset(0, 1) = Application.WorksheetFunction.AverageIf(aRng, cel, bRng) 'calculate average
Next cel

use

For Each cel In .Range(.Cells(2, 4), .Cells(outputLR, 4))   'loop through each cell in Column D
    cel.Offset(0, 1).FormulaArray = "=MIN(IF(" & aRng.Address & "=" & cel.Value & "," & bRng.Address & "))"
Next cel
.Range(.Cells(2, 4), .Cells(outputLR, 4)).Offset(0, 1).Value = .Range(.Cells(2, 4), .Cells(outputLR, 4)).Offset(0, 1).Value

这篇关于VBA在列中找到相同值的范围并计算平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！