可变长度 VBA 的平均行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11463127/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Average rows of variable length VBA
提问by Mary
I am trying to get a simple VBA code as part of a Macro that will put in column B the average of the values for each row. I have code that generates a time series and fills a column per simulation such that each column is a time series starting at column C. The number of simulations vary so I simply need something that averages the value for each point in time (ie for each row across all simulations) whilst adjusting for the number of simulations that are run (columns that are filled). I would then like it to generate a single graph of all the time series highlighting the average values that are calculated. Many thanks if you can help!
我试图获得一个简单的 VBA 代码作为宏的一部分，它将在 B 列中放入每行值的平均值。我有代码可以生成时间序列并在每个模拟中填充一列，这样每一列都是从 C 列开始的时间序列。模拟的数量各不相同，所以我只需要一些东西来平均每个时间点的值（即每个跨所有模拟的行），同时调整运行的模拟数量（填充的列）。然后我希望它生成所有时间序列的单个图形，突出显示计算出的平均值。如果您能提供帮助，非常感谢！
Here, for example, is the code that takes the values for the time steps from sheet1 and places it in columnA sheet2. I would like the macro to now place the average in the appropriate row down Column B:
例如，这里的代码从 sheet1 中获取时间步长的值并将其放置在 columnA sheet2 中。我希望宏现在将平均值放在 B 列下方的适当行中：
Sheets("Sheet1").Select
Range("E5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
采纳答案by Siddharth Rout
Try this
尝试这个
Sub Sample()
Dim ws As Worksheet
Dim lRow As Long
Set ws = Sheets("Sheet1")
With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("B2:B" & lRow).Formula = _
"=IF(ISERROR(AVERAGE(C2:E2)),"""",AVERAGE(C2:E2))"
End With
End Sub
FOLLOWUP
跟进
Try this
尝试这个
Sub Sample()
Dim ws As Worksheet
Dim lRow As Long, lCol As Long
Dim ColNm As String
Set ws = Sheets("Sheet1")
With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
lCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
ColNm = Split(.Cells(, lCol).Address, "$")(1)
.Range("B2:B" & lRow).Formula = "=IF(ISERROR(AVERAGE(C2:" _
& ColNm & _
"2)),"""",AVERAGE(C2:" & _
ColNm & "2))"
End With
End Sub
回答by whytheq
Try this although there will be lots of different variants on this code:
试试这个，尽管这段代码会有很多不同的变体：
Sub AddAvgFormula()
With Sheets("Sheet2")
.Range("B2").FormulaR1C1 = "=AVERAGE(RC[1]:RC[3])"
.Range("B2").Copy
.Range("B2:B" & .Cells(Excel.Rows.Count, 1).End(Excel.xlUp).Row).PasteSpecial Excel.xlPasteFormulas
End With
End Sub
At the end of your current code before the End Subadd this:
在当前代码的末尾End Sub添加以下内容之前：
Call AddAvgFormula
Here's a varient of the original code:
这是原始代码的变体：
Sub AddAvgFormula()
With Sheets("Sheet2")
.Range("B2") = "=AVERAGE(C2:E2)"
.Range("B2").Copy
.Range("B2:B" & .Cells(Excel.Rows.Count, 1).End(Excel.xlUp).Row).PasteSpecial Excel.xlPasteFormulas
End With
End Sub
Another variant which is shorter but maybe not so intuitive:
另一个更短但可能不那么直观的变体：
Sub AddAvgFormula()
With Sheets("Sheet2")
.Range("B2:B" & .Cells(Excel.Rows.Count, 1).End(Excel.xlUp).Row) = "=AVERAGE(C2:E2)"
End With
End Sub
