Как быстро объединить строки по значениям одного столбца и выполнить вычисления в Excel?
Допустим, у вас есть диапазон данных, в котором один из столбцов содержит дубликаты. Вы хотите объединить строки по столбцу A (содержащему дубликаты), а затем выполнить вычисления по другому столбцу на основе этих объединённых строк — как показано на снимке экрана:
В Excel нет быстрого способа объединить строки на основе значений одного столбца, но я предлагаю приёмы, которые помогут вам объединить дублирующиеся строки, а затем просуммировать или выполнить другие вычисления по другому столбцу.
| Исходные данные | Объединить и разделить |
 |  |
Объединение строк на основе одного столбца с последующим суммированием значений с помощью Advanced Combine
Объединение строк на основе одного столбца с выполнением различных операций над другими столбцами с помощью
Расширенное объединение строк
Объединение строк на основе одного столбца с последующим суммированием значений с помощью VBA
Существует код VBA, который поможет вам объединить дублирующиеся строки и просуммировать значения из другого столбца.
1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений, затем выберите Вставка > Модуль и скопируйте приведённый ниже код VBA в новый Модуль.
VBA: объединение строк на основе значений одного столбца
Sub MG30Nov12()
'Updateby20150519
Dim Rng As Range
Dim InputRng As Range
Dim nRng As Range
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set InputRng = InputRng.Parent.Range(InputRng.Columns(1).Address)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Rng In InputRng
If Not .Exists(Rng.Value) Then
.Add Rng.Value, Rng.Offset(, 1)
Else
.Item(Rng.Value).Value = .Item(Rng.Value).Value + Rng.Offset(, 1)
If nRng Is Nothing Then
Set nRng = Rng
Else
Set nRng = Union(nRng, Rng)
End If
End If
Next
If Not nRng Is Nothing Then
nRng.EntireRow.Delete
End If
End With
End Sub2. Нажмите F5 или кнопку Выполнить, чтобы запустить код VBA. После этого откроется диалоговое окно для выбора диапазона данных. См. снимок экрана:
3. После выбора рабочего диапазона нажмите ОК. Теперь данные объединены по первому столбцу, а значения во втором — просуммированы.
Примечание: этот код VBA корректно работает только при объединении данных по первому столбцу и суммировании значений во втором столбце.
Объединение строк на основе одного столбца с последующим суммированием значений с помощью Расширенное объединение строк
Если вы не знакомы с кодом VBA, воспользуйтесь функцией Расширенное объединение строк сторонней надстройки — Kutools для Excel, чтобы легко и быстро объединить дублирующиеся строки и просуммировать значения в другом столбце.
1. Выделите нужные данные и нажмите Kutools > Объединить и разделить > Расширенное объединение строк. См. снимок экрана:
2. В появившемся диалоговом окне выберите столбец, на основе которого будут объединяться остальные столбцы, и нажмите Первичный ключ. Затем выберите другой столбец и нажмите Вычислить, после чего укажите Сумма.
Совет: если ваш диапазон содержит заголовки, установите флажок Включить заголовки, а также отметьте Использовать отформатированные значения, чтобы сохранить форматирование после объединения.
3. Затем нажмите ОК. Теперь данные объединены на основе основного столбца, а значения в другом столбце — просуммированы. См. снимок экрана:
Объединение строк по одному столбцу с применением различных операций к другим столбцам с помощью функции «Расширенное объединение строк».
Расширенное объединение строк обладает расширенными возможностями: оно позволяет не только объединять дублирующиеся строки в одном столбце и суммировать значения другого столбца, но и объединять дублирующиеся строки в одном столбце с выполнением различных операций над другими столбцами.
Допустим, у вас есть диапазон данных, как на скриншоте ниже, и вы хотите одновременно объединить значения из столбца B и просуммировать значения из столбца C, сгруппировав их по столбцу A.
Kutools для Excel — включает более 300 незаменимых инструментов для Excel. Работайте в Excel быстрее, проще и эффективнее.Скачать сейчас!
1. Выделите диапазон и нажмите Kutools > Объединить и разделить > Расширенное объединение строк. См. снимок экрана:
2. В диалоговом окне Объединить строки на основе столбца выполните следующие действия:
(1) Щёлкните по столбцу, на основе которого нужно объединять, и нажмите Первичный ключ;
(2) Щёлкните по столбцу, данные в котором необходимо объединить, и нажмите Объединить, затем выберите нужный разделитель из списка;
(3) Щёлкните по столбцу, значения в котором нужно просуммировать, нажмите Вычислить, затем выберите Сумма.
3. Нажмите ОК. Теперь вы увидите результат, как показано ниже:
С помощью функции Расширенное объединение строк можно объединять строки на основе одного столбца, а также подсчитывать значения, находить среднее или сохранять первое/последнее значение в других столбцах.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек