Перейти до основного матеріалу

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

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


Об’єднайте повторювані рядки та підсумуйте значення за допомогою функції Consolidate

Консолідація — це корисний інструмент для об’єднання кількох робочих аркушів або рядків у Excel. За допомогою цієї функції ми можемо об’єднати повторювані рядки та швидко й легко підсумувати їхні відповідні значення. Виконайте наведені нижче дії.

Крок 1: виберіть клітинку призначення

Виберіть місце для відображення консолідованих даних.

Крок 2: Доступ до функції консолідації та налаштування консолідації

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

Результат:

Excel об’єднає будь-які дублікати, знайдені в першому стовпці, і підсумує їхні відповідні значення в сусідніх стовпцях, як показано на знімку екрана:

Примітки:
  • Якщо діапазон не містить рядок заголовка, переконайтеся, що він є зніміть прапорець у верхньому рядку від Використовуйте ярлики в варіант.
  • За допомогою цієї функції обчислення можна консолідувати лише на основі першого стовпця (крайнього лівого) даних.

Об’єднайте повторювані рядки та підсумуйте значення за допомогою потужної функції – Kutools

Якщо ви встановили Kutools для Excel, його Розширені комбіновані ряди Функція дозволяє легко об’єднувати повторювані рядки, надаючи можливості підсумовувати, підраховувати, усереднювати або виконувати інші обчислення ваших даних. Крім того, ця функція не обмежується лише одним ключовим стовпцем, вона може працювати з кількома ключовими стовпцями, що значно полегшує складні завдання консолідації даних.

примітки: Якщо ви хочете використовувати це Розширені комбіновані ряди функція, будь ласка завантажте та встановіть Kutools для Excel перший.

після установки Kutools для Excel, виберіть діапазон даних і натисніть Кутулс > Злиття та розділення > Розширені комбіновані ряди.

У Розширені комбіновані ряди у діалоговому вікні встановіть такі операції:

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

Результат:

Тепер повторювані значення в стовпці ключа об’єднуються, а інші відповідні значення підсумовуються, як показано на знімку екрана:

Порада:
  • За допомогою цієї корисної функції ви також можете об’єднувати рядки на основі повторюваного значення клітинки, як показано нижче:
  • Ця функція підтримує Undo, якщо ви хочете відновити вихідні дані, просто натисніть Ctrl + Z.
  • Щоб застосувати цю функцію, будь ласка завантажте та встановіть Kutools для Excel перший.

Об’єднайте повторювані рядки та підсумуйте значення за допомогою зведеної таблиці

Зведена таблиця в Excel забезпечує динамічний спосіб перевпорядкування, групування та узагальнення даних. Ця функція стає неймовірно корисною, коли ви стикаєтеся з набором даних, заповненим повторюваними записами, і вам потрібно підсумувати відповідні значення.

Крок 1: Створення зведеної таблиці

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

Крок 2. Налаштування зведеної таблиці:

  1. У Поля зведеної таблиці перетягніть поле, що містить дублікати, до Рядок область. Це згрупує ваші дублікати.
  2. Далі перетягніть поля зі значеннями, які потрібно підсумувати, до Цінності область. За замовчуванням Excel підсумовує значення. Дивіться демонстрацію нижче:

Результат:

Зведена таблиця тепер відображає ваші дані з об’єднаними дублікатами та підсумованими значеннями, пропонуючи чітке та стисле уявлення для аналізу. Перегляньте скріншот:


Об'єднайте повторювані рядки та підсумуйте значення за допомогою коду VBA

Якщо вас цікавить код VBA, у цьому розділі ми надамо код VBA для консолідації повторюваних рядків і підсумовування відповідних значень в інших стовпцях. Виконайте наведені нижче дії.

Крок 1. Відкрийте редактор аркушів VBA і скопіюйте код

  1. Утримуйте клавішу ALT + F11 клавіші в Excel, щоб відкрити Microsoft Visual Basic для додатків вікна.
  2. Натисніть Insert > Модуліта вставте наступний код у Модулі Вікно
    Код 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 може бути простим і ефективним. Виберіть одну з простих функцій Consolidate, вдосконалених Kutools, аналітичних зведених таблиць або гнучкого кодування VBA, щоб знайти рішення, яке відповідає вашим навичкам і потребам. Якщо вам цікаво ознайомитися з іншими порадами та підказками щодо Excel, будь ласка, наш веб-сайт пропонує тисячі посібників натисніть тут, щоб отримати до них доступ. Дякуємо, що прочитали, і ми з нетерпінням чекаємо надати вам більше корисної інформації в майбутньому!


Статті по темі:

  • Об’єднайте кілька рядків в один на основі дублікатів
  • Можливо, у вас є діапазон даних, у стовпці A назви продукту є кілька повторюваних елементів, і тепер вам потрібно видалити повторювані записи в стовпці A, але об’єднати відповідні значення в стовпці B. Як можна виконати це завдання в Excel ?
  • Перегляд і повернення кількох значень без дублікатів
  • Іноді вам може знадобитися 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