Как объединить строки в Excel

Виды объединения

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

Способ 1: объединение через окно форматирования

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

1. Можно использовать два метода для выбора строк для слияния. Первый из них заключается в том, что вы, удерживая левую кнопку мыши, перетаскиваете сектора этих элементов на вертикальной линии координат, которые хотите объединить. Они будут выделены. Также все на той же вертикальной линии координат можно щелкнуть левой кнопкой мыши по номеру первой объединяемой строки. Затем нажмите на последнюю строку, но при этом зажмите клавишу Shift на клавиатуре. Таким образом, будет выделена вся область между этими двумя секторами.

   

2. После того, как нужная область выбрана, можно переходить непосредственно к процедуре слияния. Для этого щелкните правой кнопкой мыши в любом месте выделения. Откроется контекстное меню. Вводим его на элемент «Формат ячейки».
3. Окно форматирования активировано. Переходим во вкладку «Выравнивание». Затем в группе настроек «Отображение» установите флажок рядом с параметром «Объединить ячейки». После этого можно нажать кнопку «ОК» внизу окна.
4. После этого выбранные строки будут объединены. Кроме того, объединение ячеек будет происходить до конца листа.

Есть и альтернативные варианты перехода в окно форматирования. Например, после выбора строк, находясь на вкладке «Главная», вы можете щелкнуть значок «Формат», расположенный на ленте в панели инструментов «Ячейки». Из выпадающего списка действий выберите пункт «Форматировать ячейки…».

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

Вы также можете перейти в окно форматирования, нажав сочетание клавиш Ctrl+1 после выбора необходимых пунктов. Но в этом случае будет осуществлен переход в ту категорию в последнем посещенном окне «Формат ячеек.

При любом варианте перехода в окно форматирования все дальнейшие действия по объединению строк необходимо производить по описанному выше алгоритму.

Читайте также: День недели по дате Excel

Способ 2: использование инструментов на ленте

Вы также можете объединить строки, используя кнопку на ленте.

1. Прежде всего, мы выбираем нужные строки, используя один из вариантов, рассмотренных в способе 1. Затем мы переходим на вкладку «Главная» и нажимаем кнопку «Объединить и центрировать» на ленте. Он находится в панели инструментов «Выравнивание».
2. После этого выбранный диапазон строк будет объединен в конец листа. В этом случае все записи, сделанные на этой объединенной строке, будут центрированы.

Но не во всех случаях требуется, чтобы текст располагался в центре. Что делать, если его нужно оформить в стандартной форме?

1. Создаем выделение линий, которые необходимо соединить. Заходим во вкладку «Главная». Щелкаем лентой по треугольнику, который находится справа от кнопки «Объединить и центрировать». Откроется список различных действий. Выберите имя «Объединить ячейки».
2. После этого строки будут объединены в одну, а текстовые или числовые значения будут размещены так, как они заложены в стандартном числовом формате.

Способ 3: объединение строк внутри таблицы

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

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

   

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

Соединение в пределах границ таблицы также можно выполнить с помощью инструментов на ленте.

1. Выбираем нужные строки в таблице одним из двух описанных выше вариантов. Затем на вкладке «Главная» нажмите кнопку «Объединить и центрировать». Или щелкните треугольник слева от этой кнопки, а затем нажмите «Объединить ячейки» в раскрывающемся меню.

   

2. Слияние будет выполнено в соответствии с типом, выбранным пользователем.

Способ 4: объединение информации в строках без потери данных

Все перечисленные выше методы слияния подразумевают, что после завершения процедуры все данные в объединяемых элементах будут уничтожены, кроме тех, которые находятся в левой верхней ячейке диапазона. Но иногда необходимо без потерь объединить определенные значения, расположенные в разных строках таблицы. Это можно сделать с помощью функции СЦЕПИТЬ, специально предназначенной для таких целей.

Функция СЦЕПИТЬ относится к категории текстовых операторов. Его задача — объединить несколько строк текста в один элемент. Синтаксис этой функции следующий:

=СЦЕПИТЬ(текст1;текст2;…)

Аргументами для группы «Текст» может быть как отдельный текст, так и ссылки на элементы листа, в которые он помещен. Именно последнее свойство мы и будем использовать для выполнения задачи. Всего можно использовать до 255 таких аргументов.

Итак, у нас есть таблица, содержащая список компьютерного оборудования с ценой. Перед нами стоит задача объединить все данные в столбце «Единица» в одну строку без потерь.

1. Устанавливаем курсор на элемент листа, где будет отображаться результат обработки, и нажимаем кнопку «Вставить функцию».
2. Мастер функций запущен. Нам нужно перейти в блок оператора «Текст». Затем находим и выбираем название «CONNECT». Затем нажмите на кнопку «ОК».
3. Появится окно CONCATENATE Function Arguments. По количеству аргументов можно использовать до 255 полей с именем «Текст», но для реализации задачи нам нужно столько, сколько строк в таблице. В данном случае это 6. Установите курсор в поле «Текст1» и, удерживая левую кнопку мыши, щелкните по первому пункту, содержащему название оборудования, в столбце «Устройство». После этого адрес выбранного объекта отобразится в поле окна. Таким же образом вводим адреса следующих строчных букв в столбце «Единица измерения» соответственно в полях «Текст2», «Текст3», «Текст4», «Текст5» и «Текст6». Затем, когда в полях окна появятся адреса всех объектов, нажмите кнопку «ОК».
4. После этого функция будет отображать все данные в одну строку. Но, как видим, разрыва между названиями разных предметов нет, и это нас не устраивает. Чтобы решить эту проблему, выделите строку, содержащую формулу, и нажмите еще раз на кнопку «Вставить функцию».
5. Окно аргументов открывается снова, на этот раз без предварительного обращения к мастеру функций. В каждом поле открывшегося окна, кроме последнего, добавьте после адреса ячейки следующее выражение:&» »

   Это выражение является своего рода символом пробела для функции СЦЕПИТЬ. Только по этой причине нет необходимости добавлять его в последнее шестое поле. После завершения указанной процедуры нажмите на кнопку «ОК».

   

6. После этого, как мы видим, все данные не только помещаются в одну строку, но и разделяются пробелом.

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

1. Ставим знак «=» на строку, где должен отображаться результат. Нажмите на первый элемент в столбце. После отображения адреса в строке формул и в ячейке вывода результата набираем на клавиатуре следующее выражение:&» «&

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

   =A4&» «&A5&» «&A6&» «&A7&» «&A8&» «&A9

   

2. Чтобы отобразить результат на экране, нажмите кнопку Enter. Как видите, несмотря на то, что в данном случае использовалась другая формула, итоговое значение выводится точно так же, как и при использовании функции СЦЕПИТЬ.

Способ 5: группировка

Кроме того, вы можете группировать ряды без потери их структурной целостности. Посмотрим, как ты это сделаешь.

1. Прежде всего, выберите соседние встроенные элементы, которые необходимо сгруппировать. Вы можете выбирать отдельные ячейки в строках, а не обязательно строки целиком. После этого перейдите на вкладку «Данные». Нажимаем на кнопку «Группировать», которая находится в блоке инструментов «Структура». В открывшемся небольшом списке из двух пунктов выберите пункт «Группировать…».
2. После этого открывается маленькое окно, где вам предстоит выбрать, что именно мы хотим сгруппировать: строки или столбцы. Так как нам нужно сгруппировать линии, переставляем переключатель в правильное положение и нажимаем кнопку «ОК».
3. После последнего действия выбранные соседние строки будут объединены в группу. Чтобы его скрыть, достаточно нажать на иконку в виде минуса, расположенную слева от вертикальной линии координат.
4. Чтобы снова отобразить сгруппированные элементы, необходимо нажать на знак «+», образовавшийся в том же месте, где ранее был размещен символ «-».

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

Объединение строк в Excel с надстройкой «Merge cells»

Надстройка «Объединить ячейки» — это многофункциональный инструмент для объединения ячеек в Excel, который может объединять отдельные ячейки, а также целые строки или столбцы. И самое главное, этот инструмент сохраняет все данные, даже если выборка содержит несколько значений.

Чтобы объединить две или более строк в одну, выполните следующие действия:

1. Выберите диапазон ячеек, в котором вы хотите объединить строки.
2. Перейдите на вкладку «Данные Ablebits» и нажмите «Объединить ячейки», а затем нажмите «Объединить строки в одну).
3. Откроется диалоговое окно с предварительно выбранными настройками, которые хорошо работают в большинстве случаев. В этом примере мы просто меняем разделитель с диапазона по умолчанию на новую строку, как показано на снимке экрана ниже:
4. Нажмите кнопку «Объединить», и вы получите идеально объединенные строки данных, разделенные разрывами строк в ячейке:

Как объединить повторяющиеся строки в одну (сохраняя только уникальные значения)

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

На скриншоте ниже показано, чего мы пытаемся достичь. Получаем список заказов по каждому товару путем конкатенации значений из столбца «Идентификатор заказа».

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

Теперь мы собираемся поближе познакомиться с Merge Duplicates, надстройкой для слияния дубликатов, которая превращает эту трудоемкую и обременительную работу в быстрый процесс всего за четыре шага.

1. Выберите повторяющиеся строки, которые вы хотите объединить, и запустите мастер объединения дубликатов, нажав кнопку на ленте.
2. Проверьте правильность таблицы и нажмите «Далее». Рекомендуется установить флажок «Создать резервную копию», особенно если вы используете надстройку впервые.
3. Выберите ключевой столбец для проверки на наличие дубликатов. В этом примере мы выбираем столбец продукта, потому что мы хотим сгруппировать строки на основе названия продукта. Как видите, в этом столбце много повторяющихся элементов, относящихся к разным заказам.

То есть в этом случае повторяющимися строками будут строки с одинаковым названием продукта в столбце B.

Если вы хотите пропустить пустые ячейки, выберите этот параметр и нажмите «Далее.

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

Дополнительные параметры в верхней части окна позволяют:

1. Удалите повторяющиеся значения при объединении строк.
2. Пропускать пустые ячейки.
3. Сортировка объединенных значений.

Когда вы закончите, нажмите кнопку Готово».
Все данные из повторяющихся строк объединяются в одну ячейку:

Как видите, вместо 19 строк осталось всего 4 строки, каждая из которых содержит уникальное название продукта. А в следующей колонке показаны все заказы, связанные с этим товаром.

Как многократно объединять блоки строк в одну строку

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

Если вам нужно объединить только несколько записей, вы можете выбрать каждую третью строку и объединить каждый блок по отдельности с помощью инструмента «Объединить ячейки». Но если ваша электронная таблица содержит сотни или тысячи записей, вам нужен более быстрый способ.

1. Добавьте на лист вспомогательный столбец, в нашем примере — столбец C. Назовем его ID или как хотите.
2. Вставьте следующую формулу в C2 и скопируйте ее вниз по столбцу, перетащив маркер заполнения:

=ЦЕЛОЕ((СТРОКА(C2)-2)/3)

где:

1. C2 — это верхняя ячейка, в которую вы вводите формулу
2. 2 — это строка, с которой начинаются данные
3. 3 — количество строк, которые нужно объединить в каждом блоке

Эта формула добавляет уникальный номер к каждому блоку строк, как показано на рисунке ниже:

1. Вы сделали основную часть работы. Теперь вам просто нужно собрать строки на основе вычисленного идентификатора. Для этого воспользуемся мастером Merge Duplicates, который мы использовали для объединения повторяющихся строк:

На шаге 2 выберите ID в качестве ключевого столбца.

На шаге 3 выберите все столбцы, которые вы хотите объединить, и укажите новую строку в качестве разделителя.

Нажимаем Finish — и вы получите желаемый результат:

1. Удалите столбец ID, так как он вам больше не нужен, и все готово! И все это всего за 4 шага и без всяких формул!

Как объединить совпадающие строки из двух таблиц Excel без копирования / вставки.

Задача: у вас есть две таблицы с общим столбцом (или несколькими столбцами). Вы должны объединить совпадающие строки из этих двух таблиц. Таблицы могут находиться на одном листе, на двух разных листах или даже в двух разных книгах.

Например, у нас есть отчеты о продажах за январь и февраль на двух разных листах, и мы хотим объединить их в один. Тем не менее, каждая таблица может иметь разное количество строк и разный порядок продуктов, поэтому простое копирование/вставка не сработает.

В данном случае нам понадобится плагин Merge Two Tables:

1. Выберите любую ячейку в основной таблице и нажмите кнопку «Объединить две таблицы» на вкладке «Данные Ablebits»:

Это запустит надстройку с автоматически выбранной основной таблицей, поэтому на первом шаге мастера просто нажмите «Далее».

1. Выберите вторую таблицу, то есть таблицу поиска, содержащую совпадающие строки.
2. Выберите один или несколько столбцов, которые есть в обеих таблицах и чьи данные мы хотим объединить. В нашем примере мы хотим объединить информацию о продажах продуктов в одну таблицу. Следовательно, ключевым столбцом будет Product.
3. Если имена столбцов в обеих таблицах совпадают, выбранный столбец будет автоматически назначен соответствующему столбцу из другой таблицы. Однако вы можете сразу изменить этот выбор, если программа не угадала с выбором совпадения.

Плагин также определит, есть ли в таблицах строку заголовка, и отметит ее в соответствующих полях.

Если вам важен регистр записей, поставьте птичку в соответствующем чекбоксе.

1. При необходимости выберите столбцы для обновления в основной таблице. Данные из основной таблицы в этих столбцах будут заменены соответствующими значениями из таблицы поиска. В нашем случае его нет, поэтому просто нажмите «Далее.
2. Выберите столбцы для добавления в основную таблицу. В нашем случае — продажи за февраль. То есть мы добавляем столбец «Февраль» из таблицы поиска.

Программа сразу показывает нам, что в основной таблице появится новая колонка «Февраль» с данными, перенесенными из таблицы поиска.

1. На последнем шаге вы можете выбрать несколько вариантов в зависимости от того, как именно вы хотите объединить данные, и нажать кнопку «Готово». На скриншоте ниже показаны настройки по умолчанию, которые нас устраивают:

Подождите несколько секунд и посмотрите на результат:

Все надстройки, описанные в этом руководстве, а также более 60 других инструментов включены в Ultimate Suite for Excel. Надстройка работает со всеми версиями Excel 2019-2007.