Как легко объединить разные таблицы Excel в один файл

9092828.10.2012 Скачать пример

Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ

В категории Текстовые есть функция СЦЕПИТЬ (CONCATENATE), которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Например, вот так:

Нюанс: не забудьте о пробелах между словами — их надо прописывать как отдельные аргументы и заключать в скобки, ибо текст. 

Очевидно, что если нужно собрать много фрагментов, то использовать эту функцию уже не очень удобно, т.к. придется прописывать ссылки на каждую ячейку-фрагмент по отдельности. Поэтому, начиная с 2016 версии Excel, на замену функции СЦЕПИТЬ пришла ее более совершенная версия с похожим названием и тем же синтаксисом — функция СЦЕП (CONCAT). Ее принципиальное отличие в том, что теперь в качестве аргументов можно задавать не одиночные ячейки, а целые диапазоны — текст из всех ячеек всех диапазонов будет объединен в одно целое:

Для массового объединения также удобно использовать новую функцию ОБЪЕДИНИТЬ (TEXTJOIN), появившуюся начиная с Excel 2016. У нее следующий синтаксис:

=ОБЪЕДИНИТЬ(Разделитель; Пропускать_ли_пустые_ячейки; Диапазон1; Диапазон2 … )

где

• Разделитель — символ, который будет вставлен между фрагментами
• Второй аргумент отвечает за то, нужно ли игнорировать пустые ячейки (ИСТИНА или ЛОЖЬ)
• Диапазон 1, 2, 3 … — диапазоны ячеек, содержимое которых хотим склеить

Например:

Способ 2. Символ для склеивания текста (&)

Это универсальный и компактный способ сцепки, работающий абсолютно во всех версиях Excel. 

Для суммирования содержимого нескольких ячеек используют знак плюс «+«, а для склеивания содержимого ячеек используют знак «&» (расположен на большинстве клавиатур на цифре «7»). При его использовании необходимо помнить, что:

• Этот символ надо ставить в каждой точке соединения, т.е. на всех «стыках» текстовых строк также, как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8)
• Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки. В предыдущем примере с функцией СЦЕПИТЬ о кавычках заботится сам Excel — в этом же случае их надо ставить вручную.

Вот, например, как можно собрать ФИО в одну ячейку из трех с добавлением пробелов:

Если сочетать это с функцией извлечения из текста первых букв — ЛЕВСИМВ (LEFT), то можно получить фамилию с инициалами одной формулой:

Способ 3. Макрос для объединения ячеек без потери текста.

Имеем текст в нескольких ячейках и желание — объединить эти ячейки в одну, слив туда же их текст. Проблема в одном — кнопка Объединить и поместить в центре (Merge and Center) в Excel объединять-то ячейки умеет, а вот с текстом сложность — в живых остается только текст из верхней левой ячейки. 

Чтобы объединение ячеек происходило с объединением текста (как в таблицах Word) придется использовать макрос. Для этого откройте редактор Visual Basic  на вкладке Разработчик — Visual Basic (Developer — Visual Basic) или сочетанием клавиш Alt+F11, вставим в нашу книгу новый программный модуль (меню Insert — Module) и скопируем туда текст такого простого макроса:

Sub MergeToOneCell()      Const sDELIM As String = " "     'символ-разделитель      Dim rCell As Range      Dim sMergeStr As String      If TypeName(Selection) <> "Range" Then Exit Sub   'если выделены не ячейки - выходим      With Selection          For Each rCell In .Cells              sMergeStr = sMergeStr & sDELIM & rCell.Text  'собираем текст из ячеек          Next rCell          Application.DisplayAlerts = False   'отключаем стандартное предупреждение о потере текста          .Merge Across:=False                'объединяем ячейки          Application.DisplayAlerts = True          .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM))    'добавляем к объед.ячейке суммарный текст      End With  End Sub  

Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt+F8 или кнопкой Макросы на вкладке Разработчик (Developer — Macros), то Excel объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

Ссылки по теме

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

1. Объединение пустых ячеек.
2. Объединение ячеек, среди которых только одна содержит заполненные данные.

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

При таком способе выделенные ячейки будут объединены в одну единую, а содержимое будет выровнено по центру.

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

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

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

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

Ячейки также можно объединить посредством использования контекстного меню. Для выполнения этой задачи необходимо выделить курсором объединяемую область, щелкнуть правой кнопкой мыши, после чего выбрать из списка «Формат ячеек».

А появившемся окне выбираем пункт «Выравнивание» и ставим галочку напротив «Объединение ячеек». В этом меню также можно выбрать и другие параметры объединения: перенос текста по словам, автоподбор ширины, горизонтальная и вертикальная ориентация, направление, различные варианты выравнивания и прочее. После того как все параметры выставлены кликаем на «OK».

Итак, как мы и хотели, ячейки объединились в одну.

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

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

Первым шагом делаем следующее. Между объединяемыми ячейками необходимо добавить пустую ячейку. Чтобы это сделать нужно правой кнопкой мыши щелкнуть на номер столбца/строки, перед которыми мы хотим добавить новый столбец/строку и выбрать в раскрывшемся меню пункт “Вставка”.

В полученной новой ячейке прописываем формулу по следующему шаблону: “=СЦЕПИТЬ(X;Y)“. При этом X и Y — это значения координат объединяемых ячеек.

В нашем случае, требуется сцепить ячейки B2 и D2, значит, прописываем формулу “=СЦЕПИТЬ(B2;D2)” в ячейку C2.

Итогом будет склеивание данных в объединенной ячейке. Однако, как вы можете заметить, у нас получились целых три ячейки, вместо одной объединенной: две исходные и, соответственно, сама объединенная.

Для того чтобы убрать лишние ячейки, щелкаем мышью (правой кнопкой) по получившейся объединенной ячейке. В выпадающем списке необходимо нажать «Копировать».

Далее идем в ячейку, находящуюся справа от объединенной (в которой находятся изначальные данные),  щелкаем правой кнопкой мыши по ней, после чего выбираем из списка параметр “Специальная вставка».

В открывшемся окне среди всех вариантов выбираем “Значения” и щелкаем “ОК”.

В результате в этой ячейке окажется результат ячейки C2, в которой мы объединили первоначальные значения ячеек B2 и D2.

Теперь, после того, как мы вставили результат в ячейку D2, можно удалить лишние ячейки, которые теперь не нужны (B2 и С2). Для этого выбираем лишние ячейки/столбцы левой кнопкой мыши, далее по выделенному диапазону щелкаем правой кнопкой мыши и в открывшемся меню выбираем “Удалить”.

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

Таким образом, в обычном объединении ячеек ничего сложного нет. А вот чтобы объединить ячейки сохранив данные придётся слегка потрудиться. Но все же эта задача вполне выполнимая благодаря удобному функционалу программы Excel. Главное – запастись терпением и соблюдать правильную последовательность действий. Рекомендуем перед началом работы на всякий случай сделать копию документа, если вдруг что-то не получится и данные будут утеряны.

Примечание: Все вышеописанные операции можно применять в отношении как ячеек столбца (нескольких столбцов), так и в отношении ячеек строк (нескольких строк). Очередность действий и доступность функций при этом остается одинаковой.

Здравствуйте, друзья! Объединение ячеек это рутинная  операция при создании и редактировании таблиц, которая используется очень часто. Проблем при создании таблиц с нуля как правило не возникает.

Другое дело, если мы в Excel  экспортируем (или открываем)  уже созданные кем — то готовые таблицы. Часто необходимо привести их к виду, который нам необходим. И вот тут возникают проблемы — при  объединении ячеек часть информации в некоторых удаляется. На простом примере покажу, как создать кнопки в  документе и привязать к ним  нужный программный код для своих нужд.

Как объединить ячейки в excel в таблице ?

Для начала вспомним суть проблемы. Стандартный способ объединения ячеек делаем  через «формат ячеек» выделив нужные ячейки и вызвав правой кнопкой контекстное меню. Еще проще и быстрее найти в главном меню нужную кнопку:

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

Как в excel объединить две ячейки в одну  без потери данных и значений через пробел?

Можно например склеивать текст из ячеек в одну «фразу» с помощью функции «сцепить» или объединять содержимое через знак «&» (амперсанд), вводя формулы. Но, ввод формулы по  сути не является объединением ячеек таблицы, потому что мы в отдельную ячейку таким способом помещаем всего лишь информацию из  других разных ячеек, а не объединяем их.

Поэтому остается использовать лишь один способ —  немножко заняться программированием. Что такое макросы, и как  быстро настроить Excel для работы с ними я уже рассказывал. А сейчас открываем режим «разработчик» и в документ наш вставляем кнопку:

Местоположение  будущей кнопки выбираем в любом удобном месте документа. Курсор примет вид крестика; нажимаем левую кнопку мыши и рисуем границы:

Появится окошко макроса Пока нажимаем на кнопку «Создать». Откроется редактор VBA; удаляем из окна весь текст и копируем туда вместо него текст следующей процедуры:

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

В окне редактора код выглядит следующим образом:

Нажимаем значок «сохранить», если нигде нет красных строк во вставленном коде  — закрываем редактор. Теперь нужно привязать созданную процедуру  к нашей нарисованной кнопке. Для этого правой кнопкой мыши  вызываем нужный пункт:

Здесь видим название нашей процедуры — выбираем его. А так же обозначим местонахождение макроса — только в данном документе.

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

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

Проверяем работу кнопки — сначала выделяем нужные ячейки и затем жмем кнопку. Заработало? Ура!!

 Объединяем две текстовые ячейки в excel в одну через перенос строки

Следующий вариант кода поможет, если нужно объединить ячейки через символ переноса (delim = vbLf). Это значит что в ячейке строки будут располагаться друг  под другом. В принципе, можно отредактировать вторую строку предыдущего варианта, но у меня сегодня будет две кнопки. Этот код делает то же самое, но  имеет чуть другой синтаксис:

И соответственно, будет у нас две процедуры,  которые одна под другой.  В списке макросов они буду располагаться в алфавитном порядке; запомните имя процедуры (Sub)

Сохраняем изменения в редакторе. Создаем вторую кнопку и присваиваем ей снова макрос, только выбираем новую процедуру, переименовываем кнопку. Закрепляем области таблицы; начинаем работать с нужными ячейками:

Вот так работает сегодняшний пример. Возможности VBA языка обширны и с его помощью для Excel можно сильно расширить возможности работы, под себя. Удачи в изучении эксель! Пока!

Используемые источники:

• https://www.planetaexcel.ru/techniques/7/89/
• https://microexcel.ru/kak-obyedinit-yacheyki/
• https://fast-wolker.ru/kak-v-excel-obedinit-yachejki-bez-poteri-dannyx-i-znachenij.html