我有一个从G开始的大约60列的工作表。我想根据特定单元格中的给定值在每列中找到单元格/行(从G3开始)中的值的总和。 (即,在单元格G1中给出值10,我想要从单元格G3到G13的值的总和,并将其显示在G2中)。对于H,I,J等列也需要做同样的工作。 我尝试了什么: 我试过以下代码。它只给了我所有数字的总和。 Sub dural() Dim N As Long N = Cells(Rows.Count, G)。结束(xlUp)。行 单元格(N + 1,G)。公式== SUM(B5:G& N&) End Sub
I have a worksheet with around 60 columns starting from G. I want to find the sum of values in the cells/rows (starting from G3) in each column based on a given value in a particular cell. (i.e, a value say 10 is given in cell G1, I want the total of values from cell G3 to G13 and display it in G2). The same needs to be done for columns H,I,J etc. What I have tried: I have tried the below code. It just gives me sum of all numbers. Sub dural() Dim N As Long N = Cells(Rows.Count, "G").End(xlUp).Row Cells(N + 1, "G").Formula = "=SUM(B5:G" & N & ")" End Sub
推荐答案SUM函数采用start形式表示的值范围:'end'(含)。 如果您将多列范围传递给它,它会将多列相加! 将B5位替换为G3以提供单列范围。 The SUM function takes a range of values expressed in the form 'start':'end' inclusive. If you pass it a multicolumn range, it sums multiple columns! Replace the "B5" bit with "G3" to give you a single column range.
查看Richard的评论和OriginalGriff的解决方案。 以下解决方案明确了解您的其余需求 - 即从G开始检查所有填充的列,从中获取总和的行数每列的单元格3对每列进行求和 See the comment from Richard and the solution from OriginalGriff. The following solution explicitly picks up on the rest of your requirement - i.e. checks for all of the populated columns from G onwards, picks up the number of rows to sum from Cell 3 of each column, does the summation for each column Sub dural() Dim lastCol As Integer lastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Dim col As Integer For col = 7 To lastCol 'From G until finished Dim numRows As Long numRows = ActiveSheet.Cells(1, col).Value2 Dim rng As String If numRows > 0 Then rng = ConvertToLetter(col) & "3:" & ConvertToLetter(col) & CStr(numRows + 3) 'Get the appropriate total ActiveSheet.Cells(2, col).Formula = "=SUM(" & rng & ")" End If Next End Sub
我使用了Microsoft论坛上发布的 ConvertToLetter 函数:
Function ConvertToLetter(iCol As Integer) As String 'support.microsoft/en-gb/help/833402/how-to-convert-excel-column-numbers-into-alphabetical-characters Dim iAlpha As Integer Dim iRemainder As Integer iAlpha = Int(iCol / 27) iRemainder = iCol - (iAlpha * 26) If iAlpha > 0 Then ConvertToLetter = Chr(iAlpha + 64) End If If iRemainder > 0 Then ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64) End If End Function那没有用,但这个相当优雅的解决方案似乎很好
That didn't work, but this rather elegant solution seems to be fine
Function ConvertToLetter(icol As Integer) As String ConvertToLetter = Split(Cells(1, icol).Address, ")( 1 ) 结束 功能
信用:Chris Newman Thespreadsheetguru [ ^ ]
Credit: Chris Newman at Thespreadsheetguru[^]
更多推荐
我想要一个VBA代码来查找列中的数字总和。
发布评论