Объедините дубликаты в Excel, суммируйте некоторые столбцы и средневзвешенные остальные

Question

У меня есть большая таблица Excel с дублированными строками, мне нужно сжать дубликаты в одну строку, при этом некоторые столбцы суммируются, а остальные взвешиваются. Это возможно? Это список поисковых терминов Google, и некоторые термины показаны в десять раз, другие только один раз, но это важно для обеспечения точности данных при объединении.

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

Term............Clicks..Impre...AvCTR...AvBid...Cost....AvPos..Conv.£Conv..CRate

advent calendar 5572    147355  0.04    0.12    658.01  5.21    328 2.01    0.06

advent calendar 5719    084078  0.07    0.15    883.38  4.05    234 3.78    0.04

advent calendar 3398    070341  0.05    0.16    540.52  5.58    226 2.39    0.07

advent calendar 3078    108016  0.03    0.09    274.54  4.69    194 1.42    0.06

advent calendar 4948    140714  0.04    0.13    619.11  4.02    184 3.36    0.04

advent calendar 2193    088628  0.02    0.14    312.10  4.69    142 2.20    0.06

advent calendar 0861    077904  0.01    0.11    097.07  5.89    036 2.70    0.04

advent calendar 0104    000635  0.16    0.12    012.63  2.03    004 3.16    0.04

advent calendar 0034    000927  0.04    0.11    003.82  4.53    002 1.91    0.06

advent calendar 0007    000082  0.09    0.11    000.76  5.09    001 0.76    0.14

Я хочу преобразовать вышеупомянутое в ниже, не делая это вручную? IE удаляют дубликаты и суммируют B, C, F и H и делают средневзвешенное значение для D, E, G, I и J.

Это часть очень большой таблицы.

advent calendar 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)+($ В6/$ В15 * Е6)+($ В7/$ В15 * Е7)+($ В8/$ В15 * Е8)+($ В9/$ В15 * Е9)+($ В10/$ В15 * Е10)+($ B11/$ B15 * E11)

Я допустил ошибку в своем запросе, для столбца I £ /conv. Он НЕ хочет быть средневзвешенным по кликам, он хочет быть взвешенным по конверсиям ИЛИ как я это сделал, вычисляя впоследствии из общей стоимости, деленной на общее количество конверсий (я имел дело с ним до запуска макроса, удаляя цифры и вставляя нули).

Answer 1

Если вы можете стоять, разместив полученную информацию на другом листе ...

Скопируйте столбец 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, если она окажется такой же

Answer 2

Каждая строка кода комментируется. Так что легко адаптировать код для подобных задач

Что оно делает

• Этот макрос 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

Посмотрите на мою тестовую книгу, если у вас есть проблемы с запуском макроса.