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

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

Купить

Пожизненная лицензия

Бесплатные обновления в течении 2 лет

Бесплатная пожизненная поддержка

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

Для объединения повторяющихся строк необходимо выбрать один или несколько столбцов, по значениям в которых строки будут сравниваться на идентичность. В данном примере таковым выбран первый столбец A. Второй столбец B выбран в качестве столбца, значения из которого будут объединяться. Если вторая и последующая строки имеют одинаковые значения в столбце A, по которым они оцениваются на одинаковость, они считаются повторяющимися и удаляются. Значения из столбца B удаляемых строк суммируются со значениями оставшейся уникальной строки.

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

Формат суммируемых ячеек может быть не только числовым, но также общим, текстовым или например, денежным. Приложение при необходимости преобразует значение ячейки к числовому значению для выполнения математической функции. Если преобразовать значение не удалось, значением такой ячейки считается число равное 0. Результат выполнения функции сохраняется в формате ячейки оригинальной строки.

Вам необходимо определить папку, в которой будут сохранены файлы электронных таблиц после выполнения операции объединения повторяющихся строк и суммирования значений. Можно изменить базовую папку (по умолчанию это папка "Документы" текущего пользователя). Для этого нажмите на кнопку (1) и выберите нужную папку в стандартном диалоговом окне выбора папок Windows. Выберите в раскрывающемся списке (2) "Создать подпапку. Имя задаётся вручную." и задайте имя папки в поле (3). Эта папка будет создана в базовой папке. Если оставить настройки по умолчанию и нажать на кнопку “Далее”, будет создана подпапка в папке "Документы", имя которой создаётся автоматически на основе текущей даты.

Выберите файлы Excel с повторяющимися строками. Установите переключатель “Выбрать один или несколько файлов Excel” (1) и нажмите на кнопку “Выбрать файлы…” Для выбора папки с файлами электронных таблиц установите переключатель (1) и нажмите на кнопку “Выбрать папку…”. После создания списка исходных файлов кнопка “Далее” станет доступна, позволяя перейти к следующему шагу.

Приложение позволяет управлять списком файлов Excel (1), в том числе исключать из операции объединения повторяющихся строк листы каждого выбранного файла в списке (2). Также возможно уточнить диапазон ячеек для каждого выбранного листа, указав начальные и конечные столбцы и строки диапазона обработки (3). Указанные параметры можно применить ко всем листам выбранного файла электронной таблицы или ко всем файлам сразу (4). При сохранении настроек по умолчанию, приложение выполнит операцию для всех листов файлов и всех строк каждого листа.

Диапазон ячеек, исключение листов и файлов из обработки

Объединять повторяющиеся строки будем на примере файла “сотрудники.xlsx”. Данный файл содержит в первом столбце A список фамилий, при этом значения строк 2 и 5 в этом столбце повторяются. Выберем этот столбец в качестве ключевого, по которому определяем повторяющиеся строки. Значения удаляемых строк-дубликатов из столбца B суммируем со значением оставшейся уникальной строки.

Диалоговое окно приложения содержит два списка столбцов. Первый список называется “Поиск одинаковых значений в столбцах”, а второй – “Слияние значений из столбцов”. Отметьте флажком первый столбец A в списке (1), а столбец B в списке (2) в соответствии с задачей объединения строк для файла, приведённого выше. Выберите из раскрывающегося списка (3) функцию суммирования – СУММ() для значений столбца B, выделив этот столбец с помощью указателя мыши в списке (2). Для каждого столбца из этого списка можно определить собственную математическую функцию или символ-разделитель для текстовых данных. Для начала объединения строк и суммирования значений нажмите на кнопку "Далее".

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

СУММ()	Суммирует аргументы.
СРЗНАЧ()	Возвращает среднее арифметическое своих аргументов (рассчитанное как сумма всех значений, делённое на количество этих значений).
МАКС()	Возвращает наибольшее значение из списка аргументов.
МИН()	Возвращает наименьшее значение из списка аргументов.

Кликните на кнопку ниже чтобы загрузить ZIP архив с исходным и итоговым (после слияния строк и суммирования значений) файлами Excel, которые использовались в данном руководстве.

Скачать ZIP архив

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

Суммирование значений повторяющихся строк в Excel

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

Запуск приложения и открытие мастера объединения строк

Выбор папки сохранения файлов Excel

Выбор файлов таблиц Excel

Исключение файлов и листов из обработки. Определение диапазона ячеек.

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

Файлы примера

Полезные ссылки: