Как объединить одинаковые строки в excel и сложить их значения

Как объединить ячейки с одинаковым значением в Excel

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

Как выделить одинаковые ячейки группами

Допустим мы имеем список поставщиков:

Перед тем как найти повторяющиеся ячейки в Excel, отсортируем поставщиков по их идентификатору. Переходим в ячейку A2 и выбираем на закладке «ДАННЫЕ» в разделе «Сортировка и фильтр» инструмент «Сортировка от А до Я».

Чтобы автоматически отметить повторяющиеся ячейки и разделить их линиями на группы воспользуемся условным форматированием:

1. Выделите диапазон A2:A11 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:».
2. В поле для ввода формулы вводим следующе значение: =$A2<>$A3
3. Щелкните на кнопку «Формат», на вкладке «Граница» следует задать оформление для нижних границ строк. И ОК.

В результате получаем эффект как отображено на рисунке.

Нам удалось объединить и выделить повторяющиеся ячейки в Excel. Теперь в приходной накладной все отсортированные поставщики визуально разграничены линией друг от друга.

Так как мы сортировали поставщиков по их идентификатору в формуле мы зафиксировали смешанной ссылкой столбец листа $A. Если значения в соседних ячейках столбца $A равные между собой тогда формула возвращает значения ЛОЖЬ и форматирование границе не применяется. Но если верхнее значение неравно (оператор <>) нижнему значению тогда формула возвращает значение ИСТИНА и применяется форматирования нижней границы целой строки (так как в смешанной ссылке номер строки не есть абсолютным, а является относительным адресом).

Полезный совет! Если нужно разграничить данные не по первому столбцу таблицы, по любому другому, тогда соответственно отсортируйте и просто укажите адрес столбца. Например, разграничим по повторяющимся датам, а не по поставщикам. Для этого сначала сортируем данные по датам, а потом используем условное форматирование немного изменив формулу: =$C2<>$C3

Теперь список сгруппирован по одинаковым датам.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как объединить одинаковые строки в excel и сложить их значения

Вариант 1: сводная таблица. (Pivot) в примере
Вариант 2: MS query в примере
Вариант 3: Power Query
Вариант 4: Subtotal+фильтр .
Вариант 4: VBA Script

Прикрепленные файлы

• Copy of Книга2.xlsx (20.88 КБ)

Изменено: БМВ — 06.03.2017 16:47:06
По вопросам из тем форума, личку не читаю.
Пользователь
Сообщений: 3007 Регистрация: 18.07.2016
06.03.2017 16:51:42
Вариант с доп.столбцом
Прикрепленные файлы

• Книга2.xlsx (12.64 КБ)

Пользователь
Сообщений: 79 Регистрация: 21.02.2017
06.03.2017 16:56:56

БМВ,Спасибо огромное, со сводной таблицей вроде начал разбираться, а если допустим будет необходимо прицепить стоимость товара, список такой же, только с двумя видами цен?

Сообщений: 22249 Регистрация: 28.12.2016
Excel 2013, 2016
06.03.2017 17:19:57

Цитата
huchos написал: только с двумя видами цен

Цены тоже сложить? 🙂 вот в чем секрет ценообразования.

По существу это второй вопрос. Сводная может быть на основе MSQuery, там связать по Артикулу или наименованию две таблицы и в итоге получить сводную с искомым результатом , не проблема.

По вопросам из тем форума, личку не читаю.
Пользователь
Сообщений: 79 Регистрация: 21.02.2017
06.03.2017 18:35:16

БМВ,спасибо огромное, разобрался ! Цены необходимо было просто подставить (к примеру справа от движения товара), я наверное по колхозному поступил: ниже добавил список с наименованиями и добавил напротив две колонки, в сводной таблице всё вывел. Наверное есть проще способ. Ещё раз спасибо.

Пользователь
Сообщений: 79 Регистрация: 21.02.2017
06.03.2017 19:37:33
_Igor_61,Спасибо, тоже подходит разобрался.
Пользователь
Сообщений: 657 Регистрация: 09.06.2015
07.03.2017 00:17:14

доброго времени суток,вариант макроса,с выводом на Лист2,кнопка test

Sub test() Dim z, i&, j&, k&, m&, t$: z = Sheets("Лист1").Range("B1:E" & Sheets("Лист1").Range("B" & Rows.Count).End(xlUp).Row).Value With CreateObject("scripting.dictionary"): .CompareMode = 1 For i = 1 To UBound(z): t = z(i, 1) If .exists(t) = False Then m = m + 1: .Item(t) = m: For j = 1 To UBound(z, 2): z(m, j) = z(i, j): Next Else For k = 3 To UBound(z, 2): z(.Item(t), k) = z(.Item(t), k) + z(i, k): Next End If Next Sheets("Лист2").Range("A1").Resize(.Count, UBound(z, 2)).Value = z End With End Sub

Прикрепленные файлы

• example_06_03_2017_pl_sum.xls (47 КБ)

Как объединить одинаковые строки в Excel и сложить их значения?

У меня такая таблица, задача объединять похожие диски в одну строку и сложить их значения (Кол-во). Это возможно только макросом сделать ? Нашел один вариант в сети, но не рабочий. Можно ли обычно формулой сделать ?

• Вопрос задан более года назад
• 712 просмотров

7 комментариев

Средний 7 комментариев

покупка

Как объединить повторяющиеся строки и суммировать значения в Excel?

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

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

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

1. Щелкните ячейку, в которой вы хотите разместить результат на текущем листе.

2. Перейти к щелчку Данные > Консолидировать, см. снимок экрана:

3. В Консолидировать диалоговое окно:

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

4. После завершения настройки нажмите OK, а дубликаты объединяются и суммируются. Смотрите скриншот:

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

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

Kutools for Excel‘s Продвинутые ряды комбайна помогает объединить несколько повторяющихся строк в одну запись на основе ключевого столбца, а также может применять некоторые вычисления, такие как сумма, среднее значение, количество и т. д., для других столбцов. Нажмите, чтобы скачать Kutools for Excel!

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

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

1. Удерживайте ALT + F11 ключи, затем он открывает Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.

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

Sub CombineRows() 'Updateby Extendoffice Dim WorkRng As Range Dim Dic As Variant Dim arr As Variant On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Set Dic = CreateObject("Scripting.Dictionary") arr = WorkRng.Value For i = 1 To UBound(arr, 1) Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2) Next Application.ScreenUpdating = False WorkRng.ClearContents WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys) WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items) Application.ScreenUpdating = True End Sub 

3, Затем нажмите F5 ключ для запуска этого кода, и вам нужно выбрать диапазон, который вы хотите объединить, во всплывающем окне подсказки. Смотрите скриншот:

4, Затем нажмите OK, повторяющиеся строки были объединены, а значения суммированы.

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

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

Здесь я представляю удобный инструмент — Kutools for Excel для вас это Расширенные ряды комбинирования также может быстро решить эту проблему.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите нужный вам диапазон и нажмите Кутулс > Слияние и разделение > Расширенные ряды комбинирования. Смотрите скриншот:

2. В Расширенные ряды комбинирования диалог, проверьте У моих данных есть заголовки если в вашем диапазоне есть заголовки, выберите имя столбца, дубликаты которого вы хотите объединить, и щелкните Основной ключ, см. снимок экрана:

3. Затем выберите имя столбца, значения которого вы хотите суммировать, и нажмите Рассчитать > Сумма или другие расчеты по мере необходимости. Смотрите скриншот:

4. Нажмите Ok чтобы закрыть диалоговое окно, вы увидите, что дубликаты объединены, а соответствующие данные в другом столбце суммируются. Смотрите скриншоты:

Объедините соответствующие строки на основе повторяющихся значений в другом столбце с помощью Kutools for Excel

Иногда вы хотите объединить строки на основе повторяющихся значений в другом столбце, Расширенные ряды комбинирования of Kutools for Excel также может оказать вам услугу, пожалуйста, сделайте следующее:

1. Выберите диапазон данных, который вы хотите использовать, а затем щелкните Кутулс > Слияние и разделение > Расширенные ряды комбинирования для Расширенные ряды комбинирования диалоговое окно.

2. В Расширенные ряды комбинирования В диалоговом окне щелкните имя столбца, на основе которого вы хотите объединить другие данные, и щелкните Основной ключ, см. снимок экрана:

3. Затем щелкните имя другого столбца, данные которого вы хотите объединить, и щелкните Сочетать чтобы выбрать разделитель для разделения объединенных значений, см. снимок экрана:

4. Затем нажмите Ok, все значения в одной ячейке в столбце A были объединены вместе, см. скриншоты:

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

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Связанная статья: