☰

在 Excel 中合并重复项，对某些列求和，然后对其他列求加权平均值

tag-icon • tag-icon microsoft-excel vba
在 Excel 中合并重复项，对某些列求和，然后对其他列求加权平均值

我有一个包含重复行的大型 Excel 电子表格，我需要将重复项压缩为一行，并对一些列求和，对其他列求加权平均值。这可能吗？这是 Google 搜索词的列表，有些词出现了十次，有些词只出现了一次，但这对于在合并时保持数据的准确性很重要。

A................B.......C..........D......E......F..........G.....H.....I.......J

期限............点击次数..展示次数...平均点击率...平均出价...费用....平均排名..转化率£转化率..CRate

降临节日历 5572 147355 0.04 0.12 658.01 5.21 328 2.01 0.06

降临节日历 5719 084078 0.07 0.15 883.38 4.05 234 3.78 0.04

降临节日历 3398 070341 0.05 0.16 540.52 5.58 226 2.39 0.07

降临节日历 3078 108016 0.03 0.09 274.54 4.69 194 1.42 0.06

降临节日历 4948 140714 0.04 0.13 619.11 4.02 184 3.36 0.04

降临节日历 2193 088628 0.02 0.14 312.10 4.69 142 2.20 0.06

降临节日历 0861 077904 0.01 0.11 097.07 5.89 036 2.70 0.04

降临节日历 0104 000635 0.16 0.12 012.63 2.03 004 3.16 0.04

降临节日历 0034 000927 0.04 0.11 003.82 4.53 002 1.91 0.06

降临节日历 0007 000082 0.09 0.11 000.76 5.09 001 0.76 0.14

我想将上面的内容转换为下面的内容，而无需手动操作？例如，删除重复项并求和 B、C、F 和 H，然后对 D、E、G、I 和 J 进行加权平均。

这是一个非常大的电子表格的一部分。

降临节日历 25914 718680 0.04 0.13 3401.94 4.68 1351 2.68 0.05

我可以只满足于 D、E、G、I 和 J 的平均值

我通过将该行数据中的点击次数除以该重复单词的点击次数总数，将该分数乘以单元格数据，然后对重复项列求和来计算加权平均值。总和如下所示：=($B2/$B15*E2)+($B3/$B15*E3)+($B4/$B15*E4)+($B5/$B15*E5)+($B6/$B15*E6)+($B7/$B15‌​*E7)+($B8/$B15*E8)+($B9/$B15*E9)+($B10/$B15*E10)+($B11/$B15*E11)

我已经做了我的请求有错误，对于 £/conv 列 I。它不想成为按点击次数加权的平均值，而是希望按转化次数加权，或者像我所做的那样，通过总成本除以总转化次数来计算（我在运行宏之前处理了它，删除了数字并放入零）。

答案1

每行代码都有注释。因此很容易调整代码以完成类似的任务

它能做什么

  • 此 VBA 宏将复制工作表中所有唯一行合并在一起。
    它查看 A 列的值来确定哪一列是重复的
  • 它将 B、C、F 和 H 列相加。
  • 它计算 D、E、G、I 和 J 的平均值。
    它不使用加权平均值，因为我仍然不知道你如何精确计算它们

如何使用

  • 打开数据工作簿并按ALT+F11
  • 将下面的代码复制并粘贴到某处或新模块中
  • 自定义AVcols()，SUMcols()如果你想计算其他列的总和或平均值
  • 关闭 VBA 编辑器并选择/查看要合并的工作表
  • 按ALT+F8并执行宏combineduplicates
Sub combineduplicates()                 '### starts our macro
Application.ScreenUpdating = False      '### Excel wont update its screen while executing this macro. This is a huge performace boost
Dim AVcols()                            '### declare an empty array for our average columns
Dim SUMcols()                           '### declare a second empty array for our sum columns
Dim AVtemp()                            '### declare a third empty array for our temporal values we need to calculate a weighted average

AVcols() = Array(4, 5, 7, 9, 10)        '### we use the first array to store our columns for calculating an average
SUMcols() = Array(2, 3, 6, 8)           '### the second array stores the columns which should be summed up
Mcol = 2                                '### whats the multiplier column for our weighted average?

ActiveSheet.Copy Before:=Sheets(1)      '### take a copy of our activesheet. this way we don't touch the original data
'### the next line sets our range for searching dublicates. Starting at cell A2 and ending at the last used cell in column A
Set searchrange = Range([A2], Columns(1).Find(what:="*", after:=[A1], searchdirection:=xlPrevious))
For Each cell In searchrange            '### now we start looping through each cell of our searchrange

    ReDim AVtemp(UBound(AVcols) + 1, 0) '### make our temp array 2-dimensional and reser it from the previous loop
    For i = 0 To UBound(AVcols)         '### save values from start row for average calculating into the temp array
        AVtemp(i, UBound(AVtemp, 2)) = CDbl(Cells(cell.Row, AVcols(i)))     '### still filling the temp array
    Next i                              '### go ahead to the next column
    AVtemp(UBound(AVcols) + 1, UBound(AVtemp, 2)) = CDbl(Cells(cell.Row, Mcol)) '### save the clicks too

    Set search = searchrange.Find(cell, after:=cell, lookat:=xlWhole)   '### searches for a dublicate. If no dub exists, it finds only itself
    Do While search.Address <> cell.Address     '### until we find our starting cell again, these rows are all dublicates

        For i = 0 To UBound(SUMcols)    '### loop through all columns for calculating the sum
            '### next line sums up the cell in our starting row and its counterpart in its dublicate row
            Cells(cell.Row, SUMcols(i)) = CDbl(Cells(cell.Row, SUMcols(i))) + CDbl(Cells(search.Row, SUMcols(i)))
        Next i                          '### go ahead to the next column

        ReDim Preserve AVtemp(UBound(AVcols) + 1, UBound(AVtemp, 2) + 1)    '### expand the temp array so we have enough space to fill with values
        For i = 0 To UBound(AVcols)     '### loop through all columns for calculating the weighted average
            '### the next line saves the value in our temp array, but now for the duplicate rows
            AVtemp(i, UBound(AVtemp, 2)) = CDbl(Cells(search.Row, AVcols(i)))
        Next i                          '### go ahead to the next column
        AVtemp(UBound(AVcols) + 1, UBound(AVtemp, 2)) = CDbl(Cells(search.Row, Mcol))   '### save the clicks too

        search.EntireRow.Delete         '### we are finished with this row. Delete the whole row
        Set search = searchrange.Find(cell, after:=cell)    '### and search the next dublicate after our starting row
    Loop

    If search.Row = cell.Row Then       '### ok, now we have to calculate the average. All needed values are temporarly stored in our temp array
        For i = 0 To UBound(AVcols)     '### start with looping through all average columns
            average = 0                 '### reset the variable from the last loop
            For j = 0 To UBound(AVtemp, 2)              '### start looping through the data from all dublicated rows
                clicks = AVtemp(UBound(AVcols) + 1, j)  '### take the clicks for that row from the array
                sumclicks = Cells(cell.Row, Mcol)       '### take the summed up  clicks for all dublicated rows
                addaverage = AVtemp(i, j)               '### take the value which should be multiplied
                average = average + (clicks / sumclicks * addaverage)   '### now calculate the weighted average and sum it up with the old one
            Next j                      '### goto next data of dublicate rows
            Cells(cell.Row, AVcols(i)) = average    '### when finished with calculating, write the result to the workbook
        Next i                          '### go ahead to the next average column
    End If                              '### only the end line of our condition

Next                                    '### from here we start over with the next cell of our searchrange
                                        '### Note: This is a NEW unique value since we already deleted all old dublicates
Application.ScreenUpdating = True       '### re-enable our screen updating
End Sub                                 '### ends our macro

看看我的测试工作簿如果您在启动宏时遇到问题。

答案2

如果您可以忍受将结果信息放在另一张纸上……

将 A 列复制到新工作表，然后选择新列并转到数据/删除重复项（或选择数据并按Alt+ A, M）。

对于需要

  • 总结一下，=SUMIF(OriginalData!A:A,NewData!A2,OriginalData!B:B)
  • 加权平均，SUMPRODUCT(OldData!B2:B1000,OriginalData!D2:D1000,--(OriginalData!A2:A1000=NewData!A2)/SUMIF(OriginalData!A:A,OriginalData!A2,OriginalData!B:B)
    • 请注意，你可以用新工作表中的单元格替换 SUMIF，只要它们恰好相同

相关内容