Перейти к основному содержанию

В Excel часто встречается набор данных с повторяющимися записями. Часто вы можете столкнуться с диапазоном данных, где ключевой задачей является эффективное объединение этих повторяющихся строк с одновременным суммированием значений в соответствующем столбце, как показано на следующем снимке экрана. В этом контексте мы рассмотрим несколько практических методов, которые помогут вам консолидировать повторяющиеся данные и агрегировать связанные с ними значения, повышая наглядность и полезность ваших книг Excel.

Объедините повторяющиеся строки и просуммируйте значения


Объедините повторяющиеся строки и просуммируйте значения с помощью функции консолидации.

Консолидация — это полезный инструмент для консолидации нескольких листов или строк в Excel. С помощью этой функции мы можем объединять повторяющиеся строки и быстро и легко суммировать их соответствующие значения. Пожалуйста, выполните следующие действия:

Шаг 1. Выберите ячейку назначения

Выберите, где вы хотите, чтобы консолидированные данные отображались.

Шаг 2. Получите доступ к функции консолидации и настройте консолидацию.

  1. Нажмите Данные > Консолидировать, см. снимок экрана:
  2. В Консолидировать диалоговое окно:
    • (1.) Выберите Сумма от Функция раскрывающийся список;
    • (2.) Нажмите, чтобы выбрать диапазон, который вы хотите консолидировать в Справка коробка;
    • (3.) Проверить Верхний ряд и Левая колонка от Используйте ярлыки в вариант;
    • (4.) Наконец, нажмите OK .

Результат:

Excel объединит все дубликаты, найденные в первом столбце, и суммирует их соответствующие значения в соседних столбцах, как показано на следующем снимке экрана:

Ноты:
  • Если диапазон не включает строку заголовка, убедитесь, что снимите флажок Верхняя строка из Используйте ярлыки в опцию.
  • Благодаря этой функции расчеты можно консолидировать только на основе первого столбца (крайнего левого) данных.

Объедините повторяющиеся строки и суммируйте значения с помощью мощной функции – Kutools

Если вы установили Kutools for Excel, Его Расширенные ряды комбинирования Функция позволяет вам легко комбинировать повторяющиеся строки, предоставляя возможности суммировать, подсчитывать, усреднять или выполнять другие вычисления с вашими данными. Более того, эта функция не ограничивается одним ключевым столбцом, она может обрабатывать несколько ключевых столбцов, что значительно упрощает сложные задачи консолидации данных.

Внимание: Если вы хотите использовать это Расширенные ряды комбинирования особенность, пожалуйста скачайте и установите Kutools для Excel первый.

После установки Kutools for Excel, выберите диапазон данных и нажмите кнопку Кутулс > Слияние и разделение > Расширенные ряды комбинирования.

В Расширенные ряды комбинирования диалоговое окно, установите следующие операции:

  1. Щелкните имя столбца, на основе которого вы хотите объединить дубликаты. Здесь я нажму «Продукт», а затем выберите Основной ключ из раскрывающегося списка в Эксплуатация столбец;
  2. Затем выберите имя столбца, значения которого вы хотите суммировать, а затем выберите Сумма из раскрывающегося списка в Эксплуатация столбец;
  3. Что касается остальных столбцов, вы можете выбрать нужную вам операцию, например объединение значений с определенным разделителем или выполнение определенного расчета; (этот шаг можно проигнорировать, если у вас только два столбца)
  4. Наконец, вы можете просмотреть объединенный результат, а затем нажать OK .

Результат:

Теперь повторяющиеся значения в ключевом столбце объединяются, а другие соответствующие значения суммируются, как показано на следующем снимке экрана:

Советы:
  • С помощью этой полезной функции вы также можете объединять строки на основе повторяющихся значений ячеек, как показано в следующей демонстрации:
  • Эта особенность поддерживает отмену, если вы хотите восстановить исходные данные, просто нажмите Ctrl + Z.
  • Чтобы применить эту функцию, пожалуйста, скачайте и установите Kutools для Excel первый.

Объедините повторяющиеся строки и просуммируйте значения с помощью сводной таблицы.

Сводная таблица в Excel предоставляет динамический способ реорганизации, группировки и суммирования данных. Эта функция становится невероятно полезной, когда вы сталкиваетесь с набором данных, заполненным повторяющимися записями, и вам необходимо суммировать соответствующие значения.

Шаг 1. Создание сводной таблицы

  1. Выберите диапазон данных. А затем перейдите в Вставить вкладку и щелкните Сводная таблица, см. снимок экрана:
  2. В появившемся диалоговом окне выберите, где вы хотите разместить отчет сводной таблицы. Вы можете поместить его на новый или существующий лист по мере необходимости. Затем нажмите OK. Смотрите скриншот:
  3. Теперь сводная таблица вставляется в выбранную целевую ячейку. Смотрите скриншот:

Шаг 2. Настройка сводной таблицы:

  1. В Поля сводной таблицы перетащите поле, содержащее дубликаты, на панель Строка область. Это сгруппирует ваши дубликаты.
  2. Затем перетащите поля со значениями, которые вы хотите суммировать, в Наши ценности область. По умолчанию Excel суммирует значения. Посмотрите демо ниже:

Результат:

В сводной таблице теперь отображаются данные с объединенными дубликатами и суммированными их значениями, что обеспечивает четкое и краткое представление для анализа. Смотрите скриншот:


Объедините повторяющиеся строки и суммируйте значения с кодом VBA

Если вас интересует код VBA, в этом разделе мы предоставим код VBA для консолидации повторяющихся строк и суммирования соответствующих значений в других столбцах. Пожалуйста, выполните следующие действия:

Шаг 1. Откройте редактор модулей листов VBA и скопируйте код.

  1. Удерживая нажатой ALT + F11 ключи в Excel, чтобы открыть Microsoft Visual Basic для приложений окно.
  2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
    Код VBA: объедините повторяющиеся строки и просуммируйте значения
    Sub CombineDuplicateRowsAndSumForMultipleColumns()
    'Update by Extendoffice
        Dim SourceRange As Range, OutputRange As Range
        Dim Dict As Object
        Dim DataArray As Variant
        Dim i As Long, j As Long
        Dim Key As Variant
        Dim ColCount As Long
        Dim SumArray() As Variant
        Dim xArr As Variant
        Set SourceRange = Application.InputBox("Select the original range:", "Kutools for Excel", Type:=8)
        If SourceRange Is Nothing Then Exit Sub
        ColCount = SourceRange.Columns.Count
        Set OutputRange = Application.InputBox("Select a cell for output:", "Kutools for Excel", Type:=8)
        If OutputRange Is Nothing Then Exit Sub
        Set Dict = CreateObject("Scripting.Dictionary")
        DataArray = SourceRange.Value
        For i = 1 To UBound(DataArray, 1)
            Key = DataArray(i, 1)
            If Not Dict.Exists(Key) Then
                ReDim SumArray(1 To ColCount - 1)
                For j = 2 To ColCount
                    SumArray(j - 1) = DataArray(i, j)
                Next j
                Dict.Add Key, SumArray
            Else
                xArr = Dict(Key)
                For j = 2 To ColCount
                    xArr(j - 1) = xArr(j - 1) + DataArray(i, j)
                Next j
                Dict(Key) = xArr
            End If
        Next i
        OutputRange.Resize(Dict.Count, ColCount).ClearContents
        i = 1
        For Each Key In Dict.Keys
            OutputRange.Cells(i, 1).Value = Key
            For j = 1 To ColCount - 1
                OutputRange.Cells(i, j + 1).Value = Dict(Key)(j)
            Next j
            i = i + 1
        Next Key
        Set Dict = Nothing
        Set SourceRange = Nothing
        Set OutputRange = Nothing
    End Sub
    

Шаг 2. Выполните код

  1. После вставки этого кода нажмите F5 ключ для запуска этого кода. В поле подсказки выберите диапазон данных, который вы хотите объединить и суммировать. Затем нажмите OK.
  2. И в следующем окне подсказки выберите ячейку, в которую вы будете выводить результат, и нажмите OK.

Результат:

Теперь повторяющиеся строки объединены, и их соответствующие значения суммированы. Смотрите скриншот:


Объединение и суммирование повторяющихся строк в Excel может быть простым и эффективным. Выбирайте простую функцию консолидации, расширенные инструменты Kutools, аналитические сводные таблицы или гибкое кодирование VBA, чтобы найти решение, соответствующее вашим навыкам и потребностям. Если вы хотите узнать больше советов и рекомендаций по Excel, на нашем веб-сайте представлены тысячи руководств. нажмите здесь, чтобы получить к ним доступ. Спасибо за внимание, и мы с нетерпением ждем возможности предоставить вам еще больше полезной информации в будущем!


Статьи по теме:

  • Объедините несколько строк в одну на основе дубликатов
  • Возможно, у вас есть диапазон данных, в столбце «Название продукта» A есть несколько повторяющихся элементов, и теперь вам нужно удалить повторяющиеся записи в столбце A, но объединить соответствующие значения в столбце B. Как можно выполнить эту задачу в Excel? ?
  • Vlookup и возврат нескольких значений без дубликатов
  • Иногда вам может понадобиться vlookup и сразу вернуть несколько совпадающих значений в одну ячейку. Но если в возвращенные ячейки внесены повторяющиеся значения, как можно игнорировать дубликаты и сохранять только уникальные значения при возврате всех совпадающих значений, как показано на следующем снимке экрана в Excel?
Comments (30)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Em planilha sem cálculo número, onde eu tenho uma lista de prestadores de serviço para determinadas empresas, como faço para deixar classificado por empresas, por ordem de empresas? Se é possível.
Na planilha eu tenho, o nome da pessoa, razão social e empresa. Neste caso algumas empresas repetem, gostaria de classificar automático, sem precisar refazer um por um.
This comment was minimized by the moderator on the site
is there a way to save the specific merging and combining settings so that i can reuse them for future workbooks?
This comment was minimized by the moderator on the site
Thanks for your help.

This comment was minimized by the moderator on the site
SN SAD No Unit Item No Description Qty CIF_Value ID_EXD AID CSF ARF ECS RCF RDF IFT IDP AIT VAT
1 M200 UNT 1 Pen 194 500 50 0 0 0 0 0 0 0 0 0 65
2 M200 UNT 2 Pencil 241 250 25 0 0 0 0 0 0 0 0 0 32.5
3 M200 UNT 3 Cutter 204 400 40 0 0 0 0 0 0 0 0 0 52
4 M200 UNT 4 Copy 171 600 60 0 0 0 0 0 0 0 0 0 78
5 M300 KGM 1 Cup 220 250 25 0 500 0 0 0 0 0 0 0 32.5
6 M300 KGM 2 Plate 40 350 35 155 0 0 0 0 0 0 0 0 45.5
7 M300 UNT 3 Bottle 2 150 15 131 0 0 0 0 0 0 0 0 19.5
8 M300 UNT 4 Glass 2 90 9 34 0 0 0 0 0 0 0 0 11.7
9 M400 null 1 Shirt 20 800 80 0 0 0 0 0 0 0 0 0 104
10 M400 KGM 2 Pant 5 5000 500 0 0 0 0 0 0 0 0 0 650
11 M400 null 3 Shoe 12 7200 720 0 0 0 0 0 0 0 0 0 936
12 M400 MTR 4 Sandle 40 1600 160 0 0 0 0 0 0 0 0 0 208
13 M400 UNT 5 Belt 100 2000 200 0 0 0 0 0 0 0 0 0 260
how to sum cif value and remove duplicate No. (SAD No.) Please help me.
This comment was minimized by the moderator on the site
I am sooooo happy & glad with your tips. Allah bless you.
This comment was minimized by the moderator on the site
Sub MergeSameCells()
Application.DisplayAlerts = False

Dim rng As Range

MergeCells:

For Each rng In Selection
If rng.Value = rng.Offset(1, 0).Value And rng.Value <> "" Then
Range(rng, rng.Offset(1, 0)).Merge
GoTo MergeCells
End If
Next

End Sub
This comment was minimized by the moderator on the site
An absolute mess of an explantation. Thanks for the effort but it did nothing to help.
This comment was minimized by the moderator on the site
LOVE IT!!! YOUR SAVE MY LIFE!!
This comment was minimized by the moderator on the site
Ifsum=(columns include,start point row,sum column)
Example ifsum=(A:D,B:2,D:D)
WAY EASIER!
This comment was minimized by the moderator on the site
Hi Am chinnaraju

can u please assist for this. Any one?

=VLOOKUP(M5,E:F,2,)


Thanks in advance.
This comment was minimized by the moderator on the site
Needs to be:
=VLOOKUP(M5,E:F,2,FALSE)
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations