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

Объединение повторяющихся строк – одна из самых распространенных задач при работе с электронными таблицами Excel. Строки могут быть полностью идентичными (дубликаты), а также отличаться или совпадать значениями в одном или нескольких столбцах. При поиске повторяющихся строк приложение сравнивает значения в одном или нескольких ключевых столбцах, которые определяются пользователем. Если значения совпадают, повторяющиеся строки удаляются, а значения из выбранных столбцов объединяются в одной строке сохраняя уникальные значения без потери данных. Для текстовых объединяемых данных можно использовать символ-разделитель. Для числовых – функцию.

Купить

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

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

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

Скачать приложение

Для покупки на allsoft.by и allsoft.kz перейдите в раздел Купить

Возможности и ограничения объединения повторяющихся строк в Excel

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

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

Откройте таблицу Excel, содержащую данные, которые необходимо объединить.

Добавьте заголовки столбцов, если они еще отсутствуют.

Выделите столбцы A и B, включая заголовки столбцов.

Нажмите на кнопку "Сортировка и фильтрация" на вкладке "Данные" и выберите "Сортировка от А до Я". Это поможет группировать строки в столбце A с повторяющимися значениями.

Введите следующую формулу в первую ячейку столбца B:

=IF(A1=A2, B1 & ", " & B2, B1)

В этой формуле:

• IF - функция проверки, сравнивает текущее значение столбца A с предыдущим и определяет, объединять ли значения столбца B;

• A1 и A2 - относятся к ячейкам столбца A, для которых проверяются значения;

• "," - запятая объединяет два значения столбца B вместе;

• B1 и B2 - относятся к значениям в столбце B, которые будут объединены.

Скопируйте формулу из первой ячейки в любую другую ячейку столбца B, который требуется заполнить.

Проверьте, что все строки были правильно объединены, скопируйте значения столбца B и вставьте их в новый столбец, чтобы сохранить результат объединения.

Таким образом в Excel отсутствуют удобные встроенные методы для решения данной задачи и необходимо использовать формулы или VBA макросы. Если требуется выполнять эти действия сразу для большого количества листов или файлов лучшим решением является использование специализированных приложения, таких как XLSTools: Объединение Повторяющихся Строк.

Диалоговое окно "Сортировка от А до Я" в Excel для объединения повторяющихся строк

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

В данном примере повторяющиеся строки определяются по значениям в первом столбце. Этот столбец выбран в качестве ключевого. Значения из второго столбца комбинируются в случае объединения повторяющихся строк. Строки 1 и 3 являются повторяющимися (по результатам сравнения значений ключевого столбца 1). Значение строки 3 объединяется со значением из строки 1 (столбец 2). В качестве разделителя выбран символ ";".

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

Выбирайте для объединения все значимые столбцы, так как если значения столбца не выбраны для объединения, эти значения не комбинируются и соответственно теряются. В описанном выше примере значение столбца 3 строки 3 отсутствует в итоговой таблице.

В следующем примере происходит объединение строк, где столбец A является ключевым, а значения из столбцов B и C объединяются. При этом в качестве разделителя для столбца B выбран символ “,”, а для столбца C – формула суммирования значений. Обратите внимание, что регистр символов (заглавные или прописные) не учитывается при поиске дубликатов если значением ячейки является строка. Формат ячейки не учитывается если значением является числовое значение.

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

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

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

После запуска приложения нажмите на кнопку мастера объединения (1) или перейдите на соответствующую вкладку.

Определите папку сохранения конечных файлов

Приложение не замещает и не модифицирует исходные файлы электронных таблиц. Определите имя папки для сохранения итоговых файлов (4) или используйте настройки по умолчанию - в этом случае в базовой папке "Документы" (изменит её можно нажав на кнопу (1) ) будет создана папка, имя которой будет сгенерировано автоматически на основе текущей даты. Нажмите на кнопку "Далее" (5) для перехода следующему шагу.

Выбор папки сохранения итоговых файлов электронных таблиц

Выбор исходных файлов Excel

Выберите исходные файлы электронных таблиц Excel со строками, которые необходимо объединять. Оставьте переключатель "Выбрать один или несколько файлов Excel" из группы (1) в положении по умолчанию и нажмите на кнопку "Выбрать файлы" (2) для выбора файлов или установите переключатель "Выбрать папку" и нажмите на соответствующую кнопку. Если вам необходимо выбрать группу файлов, нажмите и удерживайте на клавиатуре клавишу Shift, а затем отметьте первый и последний файл в стандартном диалоговом окне выбора файлов Windows. Можно также использовать клавишу Ctrl для выбора отдельных файлов. При выборе папки, возможно задание настроек поиска в подпапках, а также определение расширений искомых файлов. Эти параметры устанавливаются в диалоговом окне "Настройки". Для перехода следующему шагу нажмите на кнопку "Далее".

Отбор файлов Excel, листов. Задание диапазона обработки

На данном шаге можно определить диапазон обработки для каждого листа выбранного файла (4), а также исключить листы (сняв флажок (6) в списке (4)) и файлы электронных таблиц (кнопка (2)) при выполнении операции объединения строк. Более подробная информация по использованию этих возможностей доступна в справочном файле.
Если вам в данный момент не требуются эти возможности, просто нажмите на кнопку "Далее" для перехода к следующему шагу.

Отбор файлов, листов, выбор диапазона обработки

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

На скриншоте ниже приведен пример файла "список товаров.xlsx" с повторяющимися строками. Поиск таких строк будем осуществлять по значениям в первом столбце A, а значения из столбцов B и C объединять без потери данных. При этом строковые значения из второго столбца B будем объединять, используя символ запятой в качестве разделителя, а значения из третьего столбца C суммировать. Наша задача - получить список товаров без дубликатов в первом столбце.

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

Результат объединения повторяющихся строк

Результат объединения повторяющихся строк в файле примера, описанного выше, приведен на скриншоте ниже. Строка 5 была удалена, а её значения из столбцов B и C объединены со строкой 2.

Смотрите пошаговое руководство по слиянию повторяющихся строк в одну в таблицах Excel в видео формате на YouTube:

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

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

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

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

НАВИГАЦИЯ

ПОДПИСЫВАЙТЕСЬ НА НАС

КОНТАКТЫ