从VBA的多张表收集平均值 [英] Collecting Average from multiple sheets in Vba
问题描述
我需要计算一些范围（C2：C11）从几个工作表到新的工作表的平均值。如果添加新工作表并将数据输入到指定的范围内，该功能仍然可以工作。
到目前为止，我有这个
....
Sheets.Add
Dim myavg As Collection
设置myavg =新集合
工作表中的每个工作表
myavg.Add wsheet.Range（B2：B11）
下一个
对于i = 1 To myavg.Count
avg1 = Application.WorksheetFunction.Average（Range（B2 ：B11））
下一个
curColumn = 5
curRow = 4
对于i = 1 To myavg.Count
ActiveSheet。单元格（curRow，curColumn）.Value = avg1
curRow = curRow + 1
下一个
...
它将一个数字返回到新工作表的所需范围，其不正确
<请帮我明白我做错了什么
谢谢你提前。
抛开集合是这个任务的好主意，我们来检查你的代码：
一般
你可能已经这样做了，重复
使用 Option Explicit 并对所有变量进行decalre
您的代码
Sheets.Add
将新的工作表添加到活动的工作簿中，并将其放在当前活动的工作表，然后激活新的工作表。
问题：
您依靠默认行为来获取新的工作表，您无法确定新表单所在的位置，您没有参考。
建议：
获取对工作簿对象的引用，并在整个代码中使用它
Dim wb As Workbook
设置wb = ActiveWorkbook
控制什么，在哪里和如何添加到工作簿
Dim wsSummary as Worksheet
设置wsSummary = wb.Worksheets.Add（之后：= wb.Worksheets（wb.Worksheets.Count））
wsSummary.Name =摘要
您的代码
对于工作表中的每个工作表
myavg.Add wsheet.Range（B2： B11）
下一个
这将创建一个集合 范围，从书中的每个工作表，包括我们刚刚创建的。我猜你不想包括那个。为了便于维护，您应该创建一个变量来保存范围，并在整个代码中使用它。
允许应用上述
Dim wsheet as WorkSheet
Dim RangeAddress As String
RangeAddress = B2：B11
对于每个表格在wb.Worksheets
如果wsheet.name<> wsSummary.Name然后
myavg.Add wsheet.Range（RangeAddress）
结束如果
下一个
您的代码
For i = 1 To myavg.Count
avg1 = Application.WorksheetFunction.Average（Range（B2：B11））
下一个
问题：
您正在迭代集合但不引用它在循环。每次通过您计算 avg1 ，然后在下一个循环中覆盖它。
每次循环，您可以计算活动中的范围的平均值表格（这将是刚刚添加的空白新表）
建议：
跳过来，看起来你想列出新的平均值单页，连续行一张。所以我们可以将阵列中的平均值存储在后面的汇总表中。请注意，如果任何范围为空，平均将导致错误。
Dim avg（）as Variant
Redim avg（1 to myavg.Count，1 To 1）
For i = 1 To myavg.Count
avg（i，1）= Application.WorksheetFunction.Average（myavg（i））
下一个
您的代码
curColumn = 5
curRow = 4
对于i = 1 To myavg.Count
ActiveSheet.Cells（curRow，curColumn）.Value = avg1
curRow = curRow + 1
下一个
问题：
这段代码简单地将最后计算的值 avg1 放在连续的行上
将结果放到新的表格：将平均值收集到数组中，将其放在表格上
Dim curColumn as Long，curRow As Long
curColumn = 5
curRow = 4
wsSummary.Cells（curRow，curColumn）.Resiz e（UBound（avg，1），1）= avg
目前为止没有改进的是错误处理。有很多方法代码可能出错，所以在您的代码中包含错误处理是明智的。例如，如果已经有一个名为摘要的工作表？如果平均返回错误怎么办？
是一个很好的方法，应该清楚的是，创建了一个数组来保存结果，该数组可以在通过工作簿的过程中填充。
/ p>
Sub Demo（）
Dim wb As Workbook
设置wb = ActiveWorkbook
Dim RangeAddress As String
RangeAddress =B2：B11
Dim wsSummary As Worksheet
Dim avg（）As Variant
ReDim avg（1对于wb.Worksheets.Count，1到1）
对于i = 1 To UBound（avg，1）
带有wb.Worksheets（i）.Range（RangeAddress）
如果WorksheetFunction.Count （.Value）> 0然后
avg（i，1）= WorksheetFunction.Average（.Value）
Else
avg（i，1）=无值在表
结束如果
结束
下一个
Dim curColumn As Long，curRow As Long
curColumn = 5
curRow = 4
关于错误恢复Next
wsSummary = wb.Worksheets（Summary）
如果Err.Number<> 0然后
'摘要表不存在，创建它
Err.Clear
错误GoTo 0
设置wsSummary = wb.Worksheets.Add（之后：= wb.Worksheets wb.Worksheets.Count））
wsSummary.Name =摘要
Else
错误GoTo 0
'摘要表已存在
'你有什么做？
End If
wsSummary.Cells（curRow，curColumn）.Resize（UBound（avg，1），1）= avg
End Sub
此代码可能仍然有问题，具体取决于您的具体要求（例如，如果汇总表已经存在，这将会处理它在平均循环中）
I need to calculate the average of a certain range ("C2:C11") from several worksheets into a new worksheet. The function should still work if a new sheet is added and data is entered into the specified range.
So far I have this
....
Sheets.Add
Dim myavg As Collection
Set myavg = New Collection
For Each wsheet In Worksheets
myavg.Add wsheet.Range("B2:B11")
Next
For i = 1 To myavg.Count
avg1 = Application.WorksheetFunction.Average(Range("B2:B11"))
Next
curColumn = 5
curRow = 4
For i = 1 To myavg.Count
ActiveSheet.Cells(curRow, curColumn).Value = avg1
curRow = curRow + 1
Next
...
It returns one number into the desired range of the new sheet and its not accurate
Please help me understand what I am doing wrong. Thank you in advance.
Putting aside whether or not a Collection is a good idea for this task, lets examine your code:
General
You may have already done this, but it warrents repeating
Use Option Explicit and decalre all your variables
Your Code
Sheets.Add
This adds a new Worksheet to the active Workbook, and puts it before the currently active sheet, then activates the new sheet.
Problems:
You are relying on the default behaviour to get a new Worksheet, you can't be sure where the new sheet is located, and you have no reference to it.
Suggestions:
Get a reference to a workbook object and use that throughout your code
Dim wb As Workbook
Set wb = ActiveWorkbook
Control what, where and how you add to the workbook
Dim wsSummary as Worksheet
Set wsSummary = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
wsSummary.Name = "Summary"
Your Code
For Each wsheet In Worksheets
myavg.Add wsheet.Range("B2:B11")
Next
This creates a Collection of Range's, from every Worksheet in the book, including the one we just created. I'm guessing you don't want to include that one. For ease of maintenance you should create a variable to hold the range and use that throughout the code.
Lets apply the above
Dim wsheet as WorkSheet
Dim RangeAddress As String
RangeAddress = "B2:B11"
For Each wsheet In wb.Worksheets
If wsheet.name <> wsSummary.Name Then
myavg.Add wsheet.Range(RangeAddress)
End If
Next
Your Code
For i = 1 To myavg.Count
avg1 = Application.WorksheetFunction.Average(Range("B2:B11"))
Next
Problems:
You are iterating over the Collection but not referencing it in the loop. Each time through you calculate avg1 and then overwrite it on the next loop.
Each time through the loop you calculate the average of a range on the Active Sheet (which will be the blank new sheet just added)
Suggestions:
Jumping ahead, it looks like you want to list the averages on the new sheet, one per sheet on successive rows. So lets store the averages in an array to be put on the summary sheet later. Note that if any of the Range's are empty, Average will cause an error.
Dim avg() as Variant
Redim avg(1 to myavg.Count, 1 To 1)
For i = 1 To myavg.Count
avg(i, 1) = Application.WorksheetFunction.Average(myavg(i))
Next
Your Code
curColumn = 5
curRow = 4
For i = 1 To myavg.Count
ActiveSheet.Cells(curRow, curColumn).Value = avg1
curRow = curRow + 1
Next
Problems:
This code simply puts the last calculated value of avg1 onto successive rows
Putting the result onto the new sheet: having collected the averages into an array, lets put that onto the sheet
Dim curColumn as Long, curRow As Long
curColumn = 5
curRow = 4
wsSummary.Cells(curRow, curColumn).Resize(UBound(avg, 1), 1) = avg
Whats not delt with so far is Error Handling. There a many ways code can go wrong, so it's wise to include Error Handling in your code. Eg what if there was already a sheet named "Summary"? What if Average returns an error?
Turning to whether this is a good method, it should be clear that having created an array to hold the results, that array could be populated in the pass through the workbook.
Something like this
Sub Demo()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim RangeAddress As String
RangeAddress = "B2:B11"
Dim wsSummary As Worksheet
Dim avg() As Variant
ReDim avg(1 To wb.Worksheets.Count, 1 To 1)
For i = 1 To UBound(avg, 1)
With wb.Worksheets(i).Range(RangeAddress)
If WorksheetFunction.Count(.Value) > 0 Then
avg(i, 1) = WorksheetFunction.Average(.Value)
Else
avg(i, 1) = "No Values On Sheet"
End If
End With
Next
Dim curColumn As Long, curRow As Long
curColumn = 5
curRow = 4
On Error Resume Next
wsSummary = wb.Worksheets("Summary")
If Err.Number <> 0 Then
' Summary sheet does not exist, create it
Err.Clear
On Error GoTo 0
Set wsSummary = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
wsSummary.Name = "Summary"
Else
On Error GoTo 0
' Summary sheet already exists
' what do you wnat to do?
End If
wsSummary.Cells(curRow, curColumn).Resize(UBound(avg, 1), 1) = avg
End Sub
This code may still have problems, depending on your exact requirements (eg if "Summary" sheets already exists, this will process it in the average loop)
这篇关于从VBA的多张表收集平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
