本文介绍了VBA在列中找到相同值的范围并计算平均值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在 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在列中找到相同值的范围并计算平均值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!