Как сгруппировать повторяющиеся значения в excel
Предположим, вы работаете с листом, который содержит несколько повторяющихся записей, и теперь вам нужно объединить / объединить строки на основе одного и того же значения и выполнить некоторые вычисления, такие как сумма, среднее значение, количество повторяющихся строк. С этим Расширенные ряды комбинирования of Kutools for Excel, вы можете быстро объединить одинаковые значения / данные или повторяющиеся строки в соответствующие ячейки.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Нажмите Кутулс > Слияние и разделение > Расширенные ряды комбинирования. Смотрите скриншоты:
Объединить строки с одинаковыми значениями / одинаковыми данными
Предположим, у вас есть диапазон, показанный на следующем снимке экрана, и вы хотите объединить столбец Количество данные на основе повторяющихся значений в столбце Продукт и колонка Имя и фамилия.
Оригинал | конец | |
1. Выберите данные диапазона, в которых вы хотите объединить дубликаты, затем примените это Расширенные ряды комбинирования утилита, нажав Кутулс > Слияние и разделение > Расширенные ряды комбинирования.
2. Чтобы выбрать ключевые столбцы для проверки дубликатов. Укажите столбец Продукт и колонка Имя и фамилия в качестве ключевого столбца для проверки повторяющихся значений. Выберите столбец, который нужно указать в качестве ключевого столбца, а затем нажмите первичная Основные кнопку, чтобы установить его как ключевой столбец для проверки повторяющихся значений. Смотрите скриншот:
3. Чтобы выбрать столбец для объединения данных. Пожалуйста, выберите столбец Количество И нажмите Сочетать кнопку, чтобы указать операцию, с которой вы хотите объединить данные в этом столбце. Например, мы хотим объединить данные в столбце Количество с разделителем Запятая. Смотрите скриншот:
4. Нажмите OK кнопку, чтобы применить операцию. Смотрите скриншоты:
Оригинал | конец | |
Объедините повторяющиеся строки и суммируйте / усредняйте значения
Предположим, у вас есть диапазон, показанный на следующем снимке экрана, и вы хотите суммировать столбец Количество данные на основе повторяющихся значений в столбце Продукт .
Оригинал | конец | |
1. Выберите диапазон данных и примените его. Расширенные ряды комбинирования утилита, нажав Кутулс > Слияние и разделение > Расширенные ряды комбинирования.
2. Для выбора ключевых столбцов для проверки дубликатов. Укажите столбец Продукт в качестве ключевого столбца для проверки повторяющихся значений. Выберите столбец, который нужно указать в качестве ключевого столбца, а затем нажмите Основной ключ кнопку, чтобы установить его как ключевой столбец для проверки повторяющихся значений. Смотрите скриншот:
3. Затем выберите столбец для расчета: Пожалуйста, выберите столбец Количествои нажмите Рассчитать, а затем выберите нужную функцию из раскрывающегося списка, здесь я выбираю Сумма. Смотрите скриншот:
4. Если вам не нужно настраивать операции для остальных столбцов, просто оставьте состояние по умолчанию, затем нажмите Ok, и повторяющиеся значения будут объединены, и их значения будут добавлены. Смотрите скриншоты:
ВниманиеВ Сочетать, вы можете суммировать, усреднять, подсчитывать и умножать значения, а также выбирать максимальное и минимальное значение из повторяющихся ячеек.
Объедините повторяющиеся строки и сохраните первые или последние данные столбца
Предположим, у вас есть диапазон, показанный на следующем снимке экрана, и вы хотите получить последние данные столбца Количество данные на основе повторяющихся значений в столбце Продукт.
Оригинал | конец | |
1. Выберите данные и нажмите Кутулс > Слияние и разделение > Предварительное объединение рядов.
2. Чтобы выбрать ключевой столбец для проверки дублирования: Укажите столбец Продукт как столбец первичного ключа. Выберите столбец, который нужно указать в качестве ключевого столбца, а затем нажмите Основной ключ кнопку, чтобы установить его как ключевой столбец для проверки повторяющихся значений.
3. Чтобы выбрать первые или последние данные: Выбрать столбец Количество, затем нажмите Оставить 1-е место or Оставаться последним чтобы сохранить первые или последние данные после объединения повторяющихся значений. см. снимок экрана:
4. Затем нажмите Ok, дубликаты будут объединены, и их первое или последнее значение также будет сохранено.
Оригинал | конец | |
Ноты:
1. Если вы проверите У моих данных есть заголовки При использовании этой утилиты ваши заголовки (первая строка) диапазона будут проигнорированы.
2. Использовать форматированные значения: он сохранит форматирование значений после объединения повторяющихся ячеек.
3. Эта утилита недоступна для фильтрации данных.
4. Эта утилита поддерживает расстегивать.
Демо: объедините несколько строк на основе одинаковых значений и выполните некоторые вычисления.
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Рекомендуемые инструменты для повышения производительности
Следующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?
Office Tab : Использование удобных вкладок в вашем офисе , как и в случае Chrome, Firefox и New Internet Explorer.
Kutools for Excel : Более 300 дополнительных функций для Excel 2021, 2019, 2016, 2013, 2010, 2007 и Office 365.
Kutools for Excel
Описанная выше функциональность - лишь одна из 300 мощных функций Kutools for Excel.
Предназначен для Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатно скачать и использовать в течение 60 дней.
В приходных накладных или прайсах очень часто повторяются некоторые значения ячеек в разных позициях. Если отсортировать эти значения не всегда удобно их визуально анализировать. Например, названия поставщиков могут быть очень похожими и какие данные к ним относятся легко спутать.
Как выделить одинаковые ячейки группами
Допустим мы имеем список поставщиков:
Перед тем как найти повторяющиеся ячейки в Excel, отсортируем поставщиков по их идентификатору. Переходим в ячейку A2 и выбираем на закладке «ДАННЫЕ» в разделе «Сортировка и фильтр» инструмент «Сортировка от А до Я».
Чтобы автоматически отметить повторяющиеся ячейки и разделить их линиями на группы воспользуемся условным форматированием:
- Выделите диапазон A2:A11 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:».
- В поле для ввода формулы вводим следующе значение: =$A2<>$A3
- Щелкните на кнопку «Формат», на вкладке «Граница» следует задать оформление для нижних границ строк. И ОК.
В результате получаем эффект как отображено на рисунке.
Нам удалось объединить и выделить повторяющиеся ячейки в Excel. Теперь в приходной накладной все отсортированные поставщики визуально разграничены линией друг от друга.
Так как мы сортировали поставщиков по их идентификатору в формуле мы зафиксировали смешанной ссылкой столбец листа $A. Если значения в соседних ячейках столбца $A равные между собой тогда формула возвращает значения ЛОЖЬ и форматирование границе не применяется. Но если верхнее значение неравно (оператор <>) нижнему значению тогда формула возвращает значение ИСТИНА и применяется форматирования нижней границы целой строки (так как в смешанной ссылке номер строки не есть абсолютным, а является относительным адресом).
Полезный совет! Если нужно разграничить данные не по первому столбцу таблицы, по любому другому, тогда соответственно отсортируйте и просто укажите адрес столбца. Например, разграничим по повторяющимся датам, а не по поставщикам. Для этого сначала сортируем данные по датам, а потом используем условное форматирование немного изменив формулу: =$C2<>$C3
В данном примере напишем код макроса, который сможет автоматически найти и объединить все одинаковые ячейки в таблице Excel любой сложности.
Как объединить одинаковые ячейки в столбце используя макрос
Имеется отчет продаж в сети магазинов по целой стране. Нам нужно сделать таблицу данного отчета более читабельной. Для этого следует красиво сформатировать первый столбец, в котором содержаться названия штатов:
Мы хотим объединить все ячейки с одинаковыми значениями в столбце «Штат» (A). Это можно реализовать с помощью ручного выделения отдельных групп одинаковых значений и объединения их ячеек, воспользовавшись инструментом: «ГЛАВНАЯ»-«Выравнивание»-«Объединить и поместить в центре». Но если таблица содержит тысячи таких групп, да еще с разным количеством повторяющихся ячеек, тогда рационально написать макрос. Он сам быстро и автоматически выполнит всю работу за Вас.
Откройте редактор Visual Basic (ALT+F11):
И создайте новый модуль с помощью инструмента: «Insert»-«Module». А потом запишите в него VBA-код макроса:
Sub JoinDoubles()
Dim i As Long
Application.DisplayAlerts = False
For i = Selection.Rows.Count To 2 Step -1
If Selection.Cells(i, 1) = Selection.Cells(i - 1, 1) Then
Range(Selection.Cells(i - 1, 1), Selection.Cells(i, 1)).Merge
End If
Next
Selection.VerticalAlignment = xlVAlignCenter
Application.DisplayAlerts = True
End Sub
Теперь если нам необходимо объединить ячейки с одинаковыми значениями, то выделите диапазон A1:A18 и запустите макрос выбрав инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«JoinDoubles»-«Выполнить». Результат действия макроса отображен на рисунке:
В начале кода мы декларируем переменную для хранения показателей счетчика цикла. В цикле проверяем значения соседних ячеек, начиная с низа выделенного диапазона. Если ячейка содержит такое же значение значит она будет объединена.
Обратите внимание! Если перед выполнением макроса выделить более одного столбца, то в результате будут объединены одинаковые значения только в первом столбце. Чтобы расширить поле действия макроса следует немного изменить его код.
Как объединить все одинаковые ячейки в любой таблице
Немного изменим структуру исходной таблицы:
На этот раз нам необходимо объединить все ячейки с одинаковыми значениями в столбце «Штат» (B) в столбце «№» (A).
Если мы хотим, чтобы действия макроса распространялось на несколько выделенных столбцов, то делаем следующее. Сначала добавим новую переменную:
Далее добавим строку с кодом начала нового цикла, который будет проходить по другим столбцам выделенного диапазона:
For j = 1 To Selection.Columns.Count
После конца, ранее созданного (внутреннего) цикла добавляем инструкцию Next для конца нового (внешнего) цикла . И соответственно сделаем код более читабельным с помощью отступов табуляции. Кроме того, для всех экземпляров объекта Cells во втором аргументе, вместо числа 1 введем переменную j (например, Selection.Cells(i, j)). Новая версия измененного кода макроса выглядит следующим образом:
Sub JoinDoubles()
Dim i As Long
Dim j As Long
Application.DisplayAlerts = False
For j = 1 To Selection.Columns.Count
For i = Selection.Rows.Count To 2 Step -1
If Selection.Cells(i - 1, j) = Selection.Cells(i, j) Then
Range(Selection.Cells(i - 1, j), Selection.Cells(i, j)).Merge
End If
Next
Next
Selection.VerticalAlignment = xlVAlignCenter
Application.DisplayAlerts = True
End Sub
Чтобы увидеть результат действия новой версии кода, выделяем всю таблицу и запускаем макрос:
Как видно на рисунке теперь макрос автоматически объединяет одинаковые значения сразу в двух столбцах.
В данном практическом примере мы напишем и пошагово разберем принцип действия VBA кода макроса для автоматического объединения повторяющихся ячеек в таблице.
Как объединить все повторяющиеся ячейки в строке
У нас иметься квартальный отчет, сформированный по отделам, как показано ниже на рисунке:
Сначала нам необходимо объединить все ячейки, которые содержат в своем значении одинаковый год. Решение данной задачи можно существенно упростить с помощью готового макроса.
Откройте редактор Visual Basic (ALT+F11):
Создайте новый модуль с помощью инструмента: «Insert»-«Module». А потом введите в него следующий VBA-код:
Sub MergeDoubles()
Dim i As Long
Application.DisplayAlerts = False
For i = Selection.Columns.Count To 2 Step -1
If Selection.Cells(1, i) = Selection.Cells(1, i - 1) Then
Range(Selection.Cells(1, i - 1), Selection.Cells(1, i)).Merge
End If
Next
Selection.HorizontalAlignment = xlHAlignCenter
Application.DisplayAlerts = True
End Sub
Теперь если нам нужно объединить одинаковые ячейки в строке по горизонтали, выделите диапазон ячеек B1:L1 и запустите макрос. А для этого выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«MergeDoubles»-«Выполнить». В результате должен получиться эффект такой же как на рисунке:
Так же мы еще воспользуемся объектом HorizontalAlignment, чтобы выровнять текст в объединенных ячейках по центру устанавливаем ему свойство xlHAlignCenter.
Обратите внимание на то, что наш макрос пока еще не умеет работать более чем с одной строкой. Сколько строк не выделяй он все равно объединяет повторяющиеся значения только в первой строке.
Как объединить все повторяющиеся ячейки одновременно
Если данный макрос должен уметь работать сразу с несколькими выделенными строками одновременно, тогда усовершенствуем его изменив и настроив код. В начале кода добавим декларацию для новой переменной j:
Далее нам необходимо создать новый цикл, который должен срабатывать еще до выполнения старого цикла. Так выглядит строка кода для нового цикла, которую следует добавить выше уже имеющегося цикла:
For j = 1 To Selection.Rows.Count
После инструкции конца первого цикла добавляем новую строку с инструкцией конца для второго:
Далее во всех экземплярах объекта Cells указываем в аргументе в место числа 1 переменную j. Например, Selection.Cells(i, j). Полная версия усовершенствованного кода макроса выглядит так:
Sub MergeDoubles()
Dim i As Long
Dim j As Long
Application.DisplayAlerts = False
For j = 1 To Selection.Rows.Count
For i = Selection.Columns.Count To 2 Step -1
If Selection.Cells(j, i) = Selection.Cells(j, i - 1) Then
Range(Selection.Cells(j, i - 1), Selection.Cells(j, i)).Merge
End If
Next
Next
Selection.HorizontalAlignment = xlHAlignCenter
Application.DisplayAlerts = True
End Sub
А теперь выделите диапазон B1:L12 и запустите макрос с уже новой версией кода:
Теперь, можно выделять любое количество строк и наш макрос будет во всех этих строках искать ячейки с одинаковым значением подряд, чтобы объединить их.
В 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: объедините повторяющиеся строки и просуммируйте значения
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 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Читайте также: