基于柱C值综上所述B列 [英] Sum up column B based on colum C values
问题描述
我有一个快速的问题:我尝试在4列列号2的表总结如果列号1和3场比赛的价值。我发现了一个样本code在这里堆栈溢出,但它计算目前基于列1.我是新来的VBA和不知道是什么改变或如何调整code在我的基础计算列1和3下面是示例code:
I have a quick question: I try to sum up in a table of 4 columns column number 2 if the value in column number 1 AND 3 matches. I found a sample code here on stack overflow, but it counts currently based on column 1. I'm new to VBA and don't know what to change or how to adjust the code to base my calculations on column 1 and 3. Here is the sample code:
Option Explicit
Sub testFunction()
Dim rng As Excel.Range
Dim arrProducts() As String
Dim i As Long
Set rng = Sheet1.Range("A2:A9")
arrProducts = getSumOfCountArray(rng)
Sheet2.Range("A1:B1").Value = Array("Product", "Sum of Count")
' go through array and output to Sheet2
For i = 0 To UBound(arrProducts, 2)
Sheet2.Cells(i + 2, "A").Value = arrProducts(0, i)
Sheet2.Cells(i + 2, "B").Value = arrProducts(1, i)
Next
End Sub
' Pass in the range of the products
Function getSumOfCountArray(ByRef rngProduct As Excel.Range) As String()
Dim arrProducts() As String
Dim i As Long, j As Long
Dim index As Long
ReDim arrProducts(1, 0)
For j = 1 To rngProduct.Rows.Count
index = getProductIndex(arrProducts, rngProduct.Cells(j, 1).Value)
If (index = -1) Then
' create value in array
ReDim Preserve arrProducts(1, i)
arrProducts(0, i) = rngProduct.Cells(j, 1).Value ' product name
arrProducts(1, i) = rngProduct.Cells(j, 2).Value ' count value
i = i + 1
Else
' value found, add to id
arrProducts(1, index) = arrProducts(1, index) + rngProduct.Cells(j, 2).Value
End If
Next
getSumOfCountArray = arrProducts
End Function
Function getProductIndex(ByRef arrProducts() As String, ByRef strSearch As String) As Long
' returns the index of the array if found
Dim i As Long
For i = 0 To UBound(arrProducts, 2)
If (arrProducts(0, i) = strSearch) Then
getProductIndex = i
Exit Function
End If
Next
' not found
getProductIndex = -1
End Function
<一个href=\"http://stackoverflow.com/questions/10112604/sum-column-b-based-on-column-a-using-excel-vba-macro/10113936#10113936?newreg=dc98a031ed9a4626a22e955954035ddb\">Sum基于列列B A使用Excel VBA宏
你能请告知我,我怎么能解决这个问题。下面你可以找到我的小桌子的样品图片。黄色部分的数量,例如,应概括和第二行应被删除。
Could you please advise me how I can solve this problem. Below you can find a sample picture of my small table. The quantity of the yellow part, for instance, shall be summed up and the second row shall be deleted.
推荐答案
你说的我试着在4列的表格总结列号2 的,而是来自你的样品表 - 图片我知道你要总结列号4
you said "I try to sum up in a table of 4 columns column number 2" but from your "Sample Table - Picture" I'd understand you want to sum up column number 4
编辑数据范围的OP变化之后
edited after OP variation of data range
假设你上面有什么可以尝试以下
Assuming what above you could try the following
Option Explicit
Sub main()
On Error GoTo 0
With ActiveSheet '<== set here the actual sheet reference needed
' With .Range("A:D").Resize(.cells(.Rows.Count, 1).End(xlUp).row) '<== here adjust "A:D" to whatever colums range you need
With .Range("A51:D" & .cells(.Rows.Count, "A").End(xlUp).row) '<== here adjust "A:D" to whatever colums range you need
With .Offset(1).Resize(.Rows.Count - 1)
.Offset(, .Columns.Count).Resize(, 1).FormulaR1C1 = "=SUMIFS(C2, C1,RC1,C3, RC3)" '1st "helper column is the 1st column at the right of data columns (since ".Offset(, .Columns.Count)")
.Columns(2).Value = .Offset(, .Columns.Count).Resize(, 1).Value 'reference to 1st "helper" column (since ".Offset(, .Columns.Count)")
.Offset(, .Columns.Count).Resize(, 1).FormulaR1C1 = "=concatenate(RC1,RC3)"
With .Offset(, .Columns.Count + 1).Resize(, 1) '2nd "helper" column is the 2nd column at the right of data columns (since ".Offset(, .Columns.Count + 1)"
.FormulaR1C1 = "=IF(countIF(R1C[-1]:RC[-1],RC[-1])=countif(C[-1],RC[-1]),1,"""")" 'reference to 1st "helper" column (with all those "C[-1]")
.Value = .Value
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Offset(, -1).Resize(, 2).ClearContents ' reference to both "helper" columns: ".Offset(, -1)" reference the 1st since it shifts one column to the left from the one referenced in the preceeding "With.." statement (which is the 2nd column at thre right of data columns) and ".Resize(, 2)" enlarges to encose the adjacent column to the right
End With
End With
End With
End With
End Sub
它使用两个帮手栏目中,我认为可能是两个相邻的最后一个数据列(即:如果数据列有A:D,那么助手列为E:F)
it makes use of two "helper" columns, which I assumed could be the two adjacent to the last data columns (i.e.: if data columns are "A:D" then helper columns are "E:F")
如果您需要使用不同的帮手栏目,然后看到他们是如何定位和评论的变化code相应
should you need to use different "helper" columns then see comments about how they are located and change code accordingly
这篇关于基于柱C值综上所述B列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!