Объединение ячеек. Практичные советы — Как объединить ячейки в Excel

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

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

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

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

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

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

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

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

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

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

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

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

Но далеко не всегда требуется объединять строчки до конца листа. Намного чаще соединение производится внутри определенного табличного массива. Давайте рассмотрим, как это сделать.

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

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

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

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

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

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

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

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

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

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

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

Бывают случаи, когда нужно объединить несколько ячеек, например, при создании шапок таблиц. Microsoft Excel 2013 позволяет это сделать. При этом должно соблюдаться условие: заполнена может быть только левая ячейка выделенного диапазона, а остальные ячейки должны быть пустыми. Для слияния нескольких ячеек в одну надо выделить их, а затем щелкнуть мышью на кнопке Объединить вкладки Главная . Выделенные ячейки объединятся в одну, а данные в объединенной ячейке разместятся с выравниванием по центру (рис. 1.14).

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

Щелкните мышью на объединенной ячейке, а затем - на стрелке в правой части кнопки Объединить . В появившемся меню выберите пункт Отменить объединение ячеек . Щелкните правой кнопкой мыши на объединенной ячейке и в появившемся контекстном меню выберите левой кнопкой мыши пункт Формат ячеек . В появившемся диалоговом окне перейдите на вкладку Выравнивание и сбросьте флажок Объединение ячеек (галочки в квадратике быть не должно). Щелкните мышью на кнопке OK .

Выравнивание содержимого ячеек

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

Иногда эксель удивляет своими требованиями делать что-то через э… макросы. Вот недавно, встала задача разбить несколько объединенных ячеек по одной, чтобы можно было нормально отсортировать весь диапазон. Думаю, никому не надо объяснять, как просто убрать объединение ячеек, разбив их по одной (я имею ввиду формат-выравнивание-убрать галку с «объединение ячеек»). Но, к сожалению, эксель обладает очень интересным свойством при отмене объединения ячеек не спрашивать, как именно мы хотели бы их вернуть в нормальное, разъединенное состояние, а тупо пишет в верхнюю левую ячейку значение, которое было в объединенной, а все остальные ячейки оставляет незаполненными. Ну, естественно, ни о какой нормальной сортировке по прежде объединенным ячейкам говорить не приходится. И хорошо бы их было всего штук 200-300. А если их шестьдесят тысяч, разбитых на группы по 10 ячеек? Вручную шесть тысяч значений потом копировать в оставшиеся пустыми ячейки?

Типичная картина — хотим отсортировать список пофамильно, чтобы Кузнецов шел все-таки после Иванова. Пример, разумеется, очень упрощенный.

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

А нам то надо, чтобы в каждой ячейке было соответствующее значение, иначе при сортировке получится черти что!

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

• разгруппировать ячейки выделенного диапазона с заполнением

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

Итак, создаем макрос с кодом:

ABAP

Sub UnMerge_And_Fill_By_Value() " разгруппировать все ячейки в Selection и ячейки каждой бывшей группы заполнить значениями из их первых ячеек Dim Address As String Dim Cell As Range If TypeName(Selection) <> "Range" Then Exit Sub End If If Selection.Cells.Count = 1 Then Exit Sub End If Application.ScreenUpdating = False For Each Cell In Intersect(Selection, ActiveSheet.UsedRange).Cells If Cell.MergeCells Then Address = Cell.MergeArea.Address Cell.UnMerge Range(Address).Value = Cell.Value End If Next End Sub

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

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

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

Тоже на всякий случай приведу код:

ABAP

Sub MergeCls() Dim ri As Integer, r2 As Integer, Col As Integer r1 = ActiveCell.Row r2 = ActiveCell.Row Col = ActiveCell.Column Do If Cells(r1, Col) <> Cells(r2 + 1, Col) Then If r1 <> r2 Then Range(Cells(r1 + 1, Col), Cells(r2, Col)).ClearContents With Range(Cells(r1, Col), Cells(r2, Col)) .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With End If r1 = r2 + 1 End If r2 = r2 + 1 Loop Until Cells(r2, Col) = "" End Sub

class="eliadunit">

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

Первый пример: использование объединение слов в одну ячейку в Excel (например, при написании объявлений в Яндекс Директ). Задача: добавить к тексту объявления слова «Звоните!» и «Звоните сейчас!». В столбце B находится текст объявления, в столбце C – слово «Звоните», в следующем столбце: «!» или «сейчас!»:

С целью масштабного объединения в столбце A вставляем формулы (знак амперсанд вставляется при английской раскладке Shift+7):

=СЦЕПИТЬ(B1;" ";C1;D1)

=B2&" "&C2&D2

=СЦЕПИТЬ(B3;" ";C3;D3)

=B4&" "&C4&D4

class="eliadunit">

В кавычках стоит знак пробела. Можно таким образов вставлять слова или даже предложения. В результате получаем такую картину в Excel:

Обычно подобное объединение используют к тексту и соответственно формула СЦЕПИТЬ относится к текстовым. Но её можно использовать и с числами. Вот пример:

Стоит обратить внимание на то, что с полученными результатами можно работать как с числами. Суммировать, умножать, делить и т.д. Но использовать в числовой формуле не получить. Например, если в ячейки суммы прописать формулу =СУММ (A2:A5), то подсчёт составит 0.

Далее разберем пример, когда нужно объединить текст и ячейку с числом, к которой применен какой-либо формат отображения (дата, финансы и др.). Если использовать только амперсанд или формулу СЦЕПИТЬ, требуемое значение будет отображаться некорректно (столбец A):

Для правильного отображения (столбец B), нужно использовать формулу ТЕКСТ. Её синтаксис следующий: ТЕКСТ(номер ячейки или значение;« формат числового значения»). С форматами можно познакомиться в меню Excel во вкладке «Главная», раздел «Число».

Имея, вышеперечисленные знания можно умело в Microsoft Excel объединять значения в одну ячейку.

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

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

Как просто объединить ячейки в Microsoft Excel

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

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

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

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

Как объединить ячейки в Microsoft Excel через контекстное меню

Также в Word есть отдельное контекстное меню, которое содержит в себе параметры объединения ячеек. Если стоит специфичная задача по объединению ячеек, которая не может быть решена указанными выше инструментами, рекомендуется пользоваться данным меню.

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

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

Как объединить ячейки в Microsoft Word без потери данных

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

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