Как объединить строки в Excel без потери данных? Примеры и советы.
Однако, рассмотрим все имеющиеся способы с учётом их достоинств и недостатков.
Объединение строк стандартными средствами Excel.
Как и при объединении столбцов, самый простой вариант — использование возможностей форматирования при помощи инструмента объединения.
В качестве примера рассмотрим небольшую таблицу, в которой по строкам записаны данные о каждом человеке – его имя, фамилия и адрес. Нам нужно объединить данные, расположенные в каждом столбце со второй по шестую строку. Выделим мышкой клетки с A2 до A6, потом по правой кнопке мыши задействуем пункт выпадающего меню «Формат».
Если мы отметим чекбокс как на скриншоте выше, нажмем кнопку OK, то получим сообщение о том, что все данные нам сохранить не удастся.
Аналогично мы можем попытаться объединить информацию в строках при помощи кнопки «Объединить и центрировать» на ленте.
Нажатие OK объединит выделенные позиции, но сохранит только значение первой из них, все остальные данные исчезнут.
То же самое произойдет, если мы выделим, к примеру, диапазон A2:C6, либо полностью строки со 2 по 6 целиком. Уцелеет только содержимое из A2. Все остальные данные, к сожалению, будут утеряны.
Примечание. В тех столбцах, строки которых вы объединяли, у вас возникнут проблемы с сортировкой данных. При попытке упорядочить данные по возрастанию или убыванию вы увидите следующее сообщение:
Итак, очевидно, что нам нужно более оптимальное решение. В этой статье далее описывается несколько методов, которые позволят вам объединить несколько строк в Эксель без потери данных.
Объединение строк при помощи формул.
Вернемся к нашей небольшой табличке. Мы хотим объединить все строки с данными, относящимися к определенному человеку, как показано ниже:
Чтобы объединить значения из нескольких строк в одну, вы можете использовать функции ОБЪЕДИНИТЬ, СЦЕПИТЬ, СЦЕП или оператор &.
В любом случае вы делаете ссылки на отдельные позиции и вводите желаемые разделители между ними.
Объединяем строки и разделяем значения запятой и пробелом:
Не очень удобная, но достаточно гибкая формула, так как каждый разделитель вы указываете отдельно. Можно использовать самые разные разделители после каждого элемента, комбинировать их различными способами.
Аналогично указываем каждый адрес и нужные разделители.
Весьма компактно, поскольку можно указывать сразу диапазон данных. Но возможен только один вид разделителя между элементами.
Просто объединяет содержимое без всяких разделителей.
Как видите, выбор есть в зависимости от ваших предпочтений и характера решаемой задачи.
Чтобы преобразовать их в значения, используйте инструмент «Специальная вставка».
Вернёмся к нашему примеру.
Если вы объединяли построчно значения в нескольких столбцах, то, естественно, можно выделить сразу несколько позиций и использовать этот инструмент специальной вставки, чтобы заменить формулы на их значения.
Теперь ваши данные, объединённые из нескольких строк, застрахованы от случайного изменения.
Такими приёмами можно объединить данные из нескольких строк в одну. О дополнениях к Excel, которые позволяют выполнить эту операцию быстро и без потери данных, мы расскажем ниже.
Объединение строк в Excel с надстройкой «Merge cells»
Чтобы объединить две или более строк в одну, вам нужно сделать следующее:
Как объединить повторяющиеся строки в одну (сохраняя только уникальные значения)
На скриншоте ниже показано, чего мы пытаемся достичь. Получаем список заказов по каждому товару, объединяя значения из столбца «Код заказа».
Варианта поиска и объединения повторяющихся строк вручную определенно следует избегать. В больших таблицах это просто нереально сложно и чревато ошибками.
Сейчас мы познакомимся поближе с надстройкой для объединения дубликатов Merge Duplicates, которая превращает эту трудоемкую и обременительную работу в быстрый процесс и всего лишь за четыре шага.
То есть в данном случае повторяющимися строками будут те, в которых имеется одинаковое наименование товара в столбце B.
Дополнительные опции в верхней части окна позволяют:
Когда закончите, нажмите кнопку «Готово».
Все данные из повторяющихся строк объединяются в одной ячейке:
Как видите, вместо 19 строк осталось только 4, каждая из которых содержит уникальное название товара. А в соседнем столбце перечислены все заказы, относящиеся к этому товару.
Как многократно объединять блоки строк в одну строку
Задача: у вас есть файл Excel с информацией о последних заказах, и каждый заказ занимает 3 строки: название товара, имя клиента и дата покупки. Вы хотите объединить каждые три строки в одну, т.е. многократно объединить блоки из трех строк.
Если нужно объединить только несколько записей, вы можете выбрать каждые 3 строки и объединить каждый блок по отдельности с помощью инструмента объединения ячеек Merge Cells. Но если ваш рабочий лист содержит сотни или тысячи записей, вам понадобится более быстрый способ.
Эта формула добавляет уникальный номер к каждому блоку строк, как показано на рисунке ниже:
На шаге 2 выберите ID в качестве ключевого столбца.
На шаге 3 выберите все столбцы, которые вы хотите объединить, и укажите перевод строки в качестве разделителя.
Нажмите Finish – и у вас будет желаемый результат:
Как объединить совпадающие строки из двух таблиц Excel без копирования / вставки.
Задача: у вас есть две таблицы с общим столбцом (или несколькими столбцами). Вам нужно объединить совпадающие строки из этих двух таблиц. Таблицы могут находиться на одном листе, в двух разных электронных таблицах или даже в двух разных рабочих книгах.
Например, у нас есть отчеты о продажах за январь и февраль на двух разных листах, и мы хотим объединить их в один. При этом в каждой таблице может быть разное количество строк и разный порядок товаров, поэтому простое копирование / вставка не сработает.
В этом случае нам пригодится надстройка Merge Two Tables:
Это запустит надстройку с автоматически выбранной основной таблицей, поэтому на первом шаге мастера вы просто нажимаете «Далее».
Если названия колонок в обеих таблицах совпадают, то выбранному столбцу будет автоматически назначен соответствующий ему из второй таблицы. Впрочем, этот выбор вы можете сразу изменить, если программа не угадала с подбором соответствия.
Надстройка также определит, имеют ли таблицы строку заголовка и отметит это в соответствующих полях.
Если для вас важен регистр записей, то поставьте птичку в соответствующем чекбоксе.
Программа сразу показывает нам, что в основной таблице появится новый столбец Февраль с данными, перенесёнными из таблицы поиска.
Подождите несколько секунд и просмотрите результат:
Все надстройки, обсуждаемые в этом руководстве, а также более 60 других инструментов включены в наш Ultimate Suite for Excel. Надстройки работают со всеми версиями Excel 2019-2007.
Надеюсь, теперь вы можете объединять строки в таблицах Excel именно так, как вам нужно. Если вы не нашли решения для своей конкретной задачи, просто оставьте комментарий, и мы вместе постараемся найти выход. Спасибо за чтение!
Как сцепить ячейки в Excel при помощи различных функций и операторов
В этом руководстве вы узнаете о различных способах соединения текстовых строк, ячеек, диапазонов, столбцов и строк в Excel с использованием функции СЦЕПИТЬ, СЦЕП, ОБЪЕДИНИТЬ и оператора «&».
В этом руководстве мы собираемся изучить различные методы «склеивания» данных, чтобы вы смогли выбрать наиболее подходящий для себя.
Что значит и как «сцепить» в Excel?
По сути, существует два способа объединения данных в таблицах:
Когда вы объединяете ячейки, вы «физически» объединяете две или более клеточки в одну. В результате у вас есть одно большое поле, которое отображается в нескольких строчках и / или столбиках на вашем рабочем листе.
Команда СЦЕПИТЬ предназначена для объединения различных фрагментов текста или объединения данных из нескольких ячеек в одну.
Синтаксис её выглядит следующим образом:
Замечание. В Excel 2016, 2019, Excel Online и Excel Mobile СЦЕПИТЬ заменяется функцией СЦЕП (CONCAT в английской версии), которая имеет точно такой же синтаксис. Хотя функция СЦЕПИТЬ сохраняется для обратной совместимости, рекомендуется использовать вместо нее СЦЕП, поскольку Microsoft не дает никаких обещаний, что старая функция будет доступна в будущих версиях программы.
Как сцепить несколько ячеек.
Простейшее выражение для конкатенации содержимого A1 и B1 выглядит следующим образом:
Обратите внимание, что они будут соединены друг с другом без разделителя, как в строке 3 на рисунке ниже. Думаю, такой вариант нас не устроит.
Чтобы разделить их пробелом, введите “ “ во втором аргументе, как в строке 4.
Чтобы разделить объединенные значения с другими разделителями, такими как запятая, пробел или косая черта, смотрите здесь.
Объединение текстовой строки и ссылки.
Нет необходимости ограничиваться только объединением значений ячеек. Вы также можете добавить к ним какой-либо текст, чтобы сделать результат более значимым и понятным. Например:
Приведенный выше пример информирует пользователя о завершении определенного задания. Обратите внимание, что мы добавляем пробел перед словом «выполнено», чтобы отделить соединенные текстовые элементы.
Естественно, вы можете добавить текст в начале или в середине формулы СЦЕПИТЬ:
Между объединенными элементами добавляется пробел (» «), поэтому результат отображается как «задание 2», а не «задание2».
Объединение текстовой строки и вычисляемой формулы
Чтобы сделать результат, возвращаемый каким-то расчётом, более понятным для ваших пользователей, вы можете связать его с текстовым пояснением. Оно объяснит, как следует оценивать получившееся.
Например, вы можете использовать следующую формулу для возврата текущей даты:
Чтобы вычисления с этой функцией всегда давали правильные результаты, запомните следующие простые правила:
Оператор «&» для объединения текста
Этот метод очень удобен во многих случаях, потому что ввод знака амперсанда (&) гораздо быстрее, чем ввод целого выражения 🙂
Подобно функции СЦЕПИТЬ, вы можете использовать «&» для объединения различных текстовых строк, значений ячеек и результатов, возвращаемых другими вычислениями.
Чтобы увидеть оператор конкатенации в действии, давайте перепишем примеры, уже рассмотренные выше:
Слить вместе A1 и B1:
Соедините A1 и B1 через пробел:
Соединить A1, B1 и текст:
Собрать воедино текст и результат вычисления текущей даты:
Как показано на скриншоте ниже, функция СЦЕПИТЬ и оператор «&» возвращают идентичные результаты:
Оператор «&» против функции СЦЕПИТЬ
Многие пользователи задаются вопросом, какой же более эффективный способ объединения строк – использовать СЦЕПИТЬ или оператор «&».
Кроме этого, нет никакой разницы между этими двумя методами конкатенации, и нет никакой разницы в скорости работы между СЦЕПИТЬ и «&».
А поскольку число 255 действительно большое, и в реальных задачах кому-то вряд ли когда-нибудь понадобится объединить столько элементов, разница сводится к удобству и простоте использования. Некоторым пользователям формулы легче читать, я лично предпочитаю использовать метод «&». Так что, просто придерживайтесь метода конкатенации, который вам удобнее.
Объединить с пробелом, запятой и другими символами
В ваших рабочих листах часто может потребоваться собрать воедино элементы таким образом, чтобы они содержали запятые, пробелы, различные знаки пунктуации или другие символы, такие как дефис или косая черта.
Для этого просто включите нужный символ в формулу объединения. Не забудьте заключить этот символ в кавычки, как показано в следующих примерах.
Так можно сцепить с пробелом:
Объединение через запятую:
Соединяем с дефисом:
На рисунке показано, как могут выглядеть результаты:
Объединение текста с переносами строк.
Чаще всего вы бы разделяли соединенные текстовые элементы знаками препинания и пробелами, как показано в предыдущем примере. Однако в некоторых случаях может потребоваться разделить их с помощью переноса строки или возврата каретки. Типичным примером является объединение почтовых адресов из информации в отдельных столбцах.
Проблема заключается в том, что вы не можете просто ввести разрыв строки, как обычный символ, и поэтому необходима специальная функция CHAR для предоставления соответствующего кода ASCII в формулу конкатенации:
В этом примере у нас есть части адреса в столбцах от A до F, и мы соединяем их в столбце G с помощью оператора конкатенации «&». Объединенные элементы разделяются запятой («,»), пробелом (» «) и переводом строки СИМВОЛ(10):
Таким же образом вы можете разделить связанные части и другими символами, такими как:
Хотя проще включить печатные символы в формулу конкатенации, просто набрав их в двойных кавычках, как мы это делали в предыдущем примере.
В любом случае, все четыре из приведенных ниже выражений дают одинаковые результаты:
=СЦЕПИТЬ(A1; СИМВОЛ(47); B1)
Как объединить столбцы
Чтобы собрать воедино два или более столбца, вы просто вводите обычную формулу объединения в первой клетке, а затем копируете ее в другие, перетаскивая маркер заполнения (маленький квадрат, который появляется в нижнем правом углу выделенной клетки).
Например, чтобы интегрировать два столбца (столбцы A и B), разделяя их содержимое пробелом, введите следующую формулу в C2, а затем скопируйте ее ниже. При перетаскивании маркера заливки для копирования указатель мыши изменится на крест, как показано на снимке экрана ниже:
Обратите внимание, что программа сама определяет, как далеко копировать после двойного щелчка дескриптор заливки, основываясь на ячейках, указанных вами в качестве аргументов. Если в вашей таблице окажутся пустые места, скажем, A6 и B6 в этом примере были пустыми, будет предложено скопировать только до строки 5. В этом случае вам потребуется вручную перетащить маркер заполнения ещё дальше, чтобы объединить все столбцы ниже.
Как объединить диапазон.
Объединение значений из нескольких ячеек может потребовать некоторых усилий, поскольку функция СЦЕПИТЬ в Excel не воспринимает массивы и требует использования одной ссылки в каждом аргументе.
Чтобы интегрировать несколько ячеек, скажем, от А1 до А4, вам понадобится что-то подобное:
При присоединении к довольно небольшому диапазону нет ничего сложного, чтобы ввести все ссылки в строке формул. Но было бы весьма утомительно добавлять большой диапазон, указывая каждую ссылку вручную. Ниже вы найдете 3 метода быстрой конкатенации.
Метод 1. Используем клавишу CTRL.
Чтобы быстро выбрать несколько ячеек, вы можете нажать клавишу CTRL и затем щелкнуть по каждой из тех, которые вы хотите использовать в качестве аргумента функции. Вот подробные шаги:
Замечание. При использовании этого метода вы должны нажать на каждую отдельную ячейку. Выбор диапазона с помощью мыши добавит в формулу массив, который функция СЦЕПИТЬ не понимает.
Способ 2. Используйте ТРАНСП
Когда вам нужно консолидировать огромный диапазон, состоящий из десятков или сотен ячеек, предыдущий метод недостаточно удобен и быстр. Ведь он требует нажатия на каждую из них.
В этом случае лучше использовать функцию ТРАНСП (TRANSPOSE в английской версии) для возврата массива, а затем заменить его ссылками одним махом.
Замечание. Какой бы метод вы ни использовали, объединенное значение в С1 является текстом (обратите внимание на выравнивание по левому краю), даже если каждое из исходных значений является числом. Это связано с тем, что СЦЕПИТЬ всегда возвращает текстовую фразу независимо от типа данных источника.
Объединение чисел и дат в различных форматах
Когда вы объединяете какие-то слова с числом или датой, вы можете форматировать результат по-разному в зависимости от набора данных. Чтобы сделать это, вставьте функцию ТЕКСТ в свою формулу.
ТЕКСТ(значение; формат) имеет два аргумента:
Ниже приведены еще несколько примеров, объединяющих текст, дату и число:
Используем новые функции СЦЕП и ОБЪЕДИНИТЬ.
Долгое время СЦЕПИТЬ была первой функцией, о которой мы думали, когда нам нужно было соединить текст в Excel. Но несколько новых игроков недавно вошли в игру. Я говорю о ОБЪЕДИНИТЬ (или TEXTJOIN) и СЦЕП ( или CONCAT), разработанных, чтобы помочь вам интегрировать несколько значений. Используйте эти простые функции всякий раз, когда вам нужно сложить вместе части имен, адресов или фраз, соединить цифры и слова. В настоящее время они доступны в Excel 2016, Excel Online и Mobile, Excel для планшетов и телефонов Android.
Вот как она выглядит:
ОБЪЕДИНИТЬ(разделитель, игнорировать_пустые, ТЕКСТ1, [ТЕКСТ2],…)
Он имеет 3 обязательных и 1 необязательный аргументы:
Для текстовых элементов может быть не более 252 аргументов. Если результат превышает предел 32767 символов, она возвращает ошибку #ЗНАЧ!.
Представленный как часть обновления в феврале 2016 года, СЦЕП заменяет функцию СЦЕПИТЬ в Excel. Работает почти так же, но существенное отличие состоит в том, что теперь можно работать с диапазонами данных, а не перечислять каждую позицию по очереди. Согласитесь, это здорово упрощает работу.
Кроме того, СЦЕП является стандартной функцией, используемой в Google Sheets.
СЦЕПИТЬ продолжает поддерживаться для обеспечения совместимости с предыдущими версиями Excel.
Выглядит это следующим образом:
Есть только два аргумента, один из которых является необязательным:
Для текстовых данных может быть не более 253 аргументов.
Ранее, если вам нужно было объединить текст из нескольких ячеек, необходимо было указать каждую из них по отдельности. С новыми функциями ОБЪЕДИНИТЬ и СЦЕП вы можете просто ссылаться на диапазон и комбинировать текстовые фразы из диапазонов с разделителем или без него.
Скажем, вы хотите сгруппировать вместе части телефонных номеров. Применяя СЦЕПИТЬ, вам нужно будет вводить адреса один за другим, как в этом примере.
В этом случае СЦЕП выглядит гораздо компактнее. Все, что вам нужно сделать, это просто выбрать диапазон для объединения.
В то же время, СЦЕП и СЦЕПИТЬ выглядят одинаково, когда вам нужно соединить части текста в одну фразу с помощью разделителей.
Как легко увидеть, они не обрабатывают пустые ячейки, что приводит к появлению лишних пробелов (например, в G3 и G8).
Поэтому функции ОБЪЕДИНИТЬ суждено стать действительно популярным выбором для пользователей, работающих с большими объемами текстовых данных.
Вы просто указываете разделитель (если нужно), выбираете «игнорировать пустые» и определяете диапазон. Это гораздо более разумное и быстрое решение.
Если у вас есть Excel 2016, изучите применение ОБЪЕДИНИТЬ и СЦЕП, чтобы увидеть, насколько они полезны, и попрощайтесь с традиционными способами при объединении.
Объединение строк в Excel
Во время работы с таблицами Эксель иногда возникает необходимость в изменении их структуры. Одним из наиболее эффективных и полезных способов преобразования конструкции таблиц является процедура объединения строк.
Стоит сразу отметить, что если в объединении участвуют 2 и более ячеек с заполненными данными, то после проведения данной операции в новой объединенном элементе останутся только данные самой верхней левой ячейки, а остальные значения сотрутся. Если нужно сохранить все данные, следует использовать другие функции, о которых также пойдет речь ниже.
Итак, давайте подробно рассмотрим, каким образом можно объединить строки в Microsoft Excel.
Как объединить строки через формат ячеек
Первый способ заключается в объединении строк через формат ячеек. Однако, до того, как начать непосредственно, саму процедуру объединения, необходимо позаботиться о том, чтобы требуемые для слияния строчки были выделены.
Другие варианты открытия формата ячеек
Независимо от выбранного способа, дальнейший алгоритм действий для того, чтобы объединить строки, аналогичен описанному ранее.
Объединение строк с использованием кнопок на ленте
Процедуру слияния элементов можно произвести с помощью кнопки расположенной прямо на ленте инструментов.
Однако иногда такое центрирование не то, что не помогает, а наоборот является ненужным. Поэтому необходимо разобраться и с тем, как размещать данные таким образом, чтобы они соответствовали стандартному виду или нашим иным пожеланиями.
Процедура объединения в границах таблицы
Варианты, разобранные выше позволяют выполнить слияние всех ячеек выделенных строк до самого конца книги. Однако, в большинстве случаев, нужно объединить только определенную часть таблицы.
Поэтому давайте рассмотрим способы, которые помогут справиться и с такой задачей.
Есть возможность произвести объединение в рамках таблицы с использованием кнопок на ленте инструментов.
Как выполнить объединение без потери данных
Описанные ранее варианты слияния табличных элементов предусматривают удаление всех данных, находящихся в ячейках, за исключением одной. Единственное, что остаётся после этой процедуры – это значение располагающиеся в левой верхней ячейке. Однако, в некоторых случаях необходимо, чтобы после обработки сохранилась вся информация из участвующих в процессе объединения ячеек. Разработчики Эксель предусмотрели это и предоставили пользователю специальную функцию сцепления, с помощью которой ячейки могут объединиться без потери данных.
Основная задача функции состоит в объединении диапазона строк, содержащих данные, в единый элемент. Вот как выглядит формула инструмента, позволяющего это выполнить: =СЦЕПИТЬ(текст_1;текст_2;…)
Аргументом функции может быть либо определенное значение, либо координаты ячейки в таблице. Мы будем пользоваться последним вариантом, поскольку он поможет нам корректно решить поставленную задачу. Максимальное число аргументов функции – 255, что более, чем достаточно.
Для примера возьмем ту же самую таблицу с наименованием товаров. Теперь необходимо произвести объединение всех данных, расположенных в первом столбце, таким образом, чтобы ничего не потерять.
Существует еще один метод объединения элементов с сохранением их значений. Для того, чтобы реализовать его на практике, нет необходимости применять функцию сцепления, достаточно будет использовать обычую формулу.
Группировка строк
Помимо перечисленных действий, можно еще воспользоваться группированием строк, сохраняя структурную целостность таблицы. Давайте разберемся, как это можно осуществить.
Заключение
Таким образом, в зависимости от структуры таблицы Эксель и поставленных задач, можно пользоваться разными вариантами объединения строк. Программа предоставляет возможность производить слияние элементов, как в широком диапазоне, так и с определёнными ограничениями, с потерей данных и без, с применением функций и формул, а также с использование такого инструмента, как группировка. Также, в определенных случаях можно комбинировать эти способы для достижения наилучшего результата.