Excel цикл объединения ячеек
Объединение ячеек – известная команда в Excel. Однако на выполнение этой операции часто уходит много времени. В данной статье рассмотрим, что подразумевают под объединением ячеек, и как быстро выполнить эту операцию. Выбрав оптимальный варианты, вы сэкономите много времени.
Формат ячеек
Команда Excel Правая кнопка мыши → Формат ячеек → Выравнивание → Отображение → объединение ячеек удаляет границы между ячейками в выделенном диапазоне. В результате получается одна большая ячейка. На картинке показано объединение ячеек одной строки и трех столбцов.
Таким же способом можно объединить любой прямоугольной диапазон. После объединения ячеек содержимое часто центрируют. На ленте во вкладке Главная даже есть специальная команда Объединить и поместить в центре.
Начинающие пользователи Excel часто применяют эту команду для размещения названия таблицы по центру.
Выглядит красиво, но крайне непрактично. Если выделить столбец комбинацией клавиш Ctrl + пробел, то диапазон расширится на все столбцы, которые захватывает объединенная ячейка. Возникнут и другие проблемы: при копировании, не работает в таблице Excel, нельзя автоматически подогнать ширину столбца и др. В общем, объединение ячеек сулит много неудобств в дальнейшей работе. Поэтому в большинстве случаев объединение ячеек лучше не применять.
Как найти объединенные ячейки в Excel
Бывает, что в файле уже есть объединенные ячейки и они мешают нормальной работе. Например, в отчете из 1С или при работе с чужим файлом Excel. Тогда их нужно как-то быстро найти и отменить объединение. Как это быстро сделать? Выполните следующие шаги.
- Вызовите команду поиска Главная (вкладка) → Редактирование (группа) → Найти и выделить → Найти. Или нажмите комбинацию горячих клавиш Ctrl + F.
- Убедитесь, что в поле Найти пусто.
- Нажмите кнопку Параметры.
- Перейдите в Формат, в окне формата выберите вкладку Выравнивание и поставьте флажок объединение ячеек.
- ОК.
- В окне Найти и заменить нажмите Найти все.
- Появятся адреса всех объединенных ячеек. Их можно выбрать по отдельности, или все сразу нажав Ctrl + A.
Как убрать объединение ячеек в Excel
Для отмены объединения сразу на всем листе Excel выполните следующие действия.
- Выделите все ячейки на листе. Можно щелкнуть на треугольнике, расположенном на пересечении заголовков строк и столбцов, либо горячей комбинацией Ctrl + A (один или два раза).
- Повторно примените команду Главная (вкладка) → Выравнивание (группа) → Объединить и поместить в центре.
Все объединенные ячейки снова станут нормальными. Если что-то пойдет не так, отмените последнее действие.
Горячие клавиши объединения ячеек в Excel
К сожалению, в Excel нет стандартных горячих клавиш для объединения ячеек. Тем не менее, у каждой команды есть свой клавиатурный эквивалент и его можно узнать. Для этого следует нажать клавишу Alt, на ленте под каждой вкладкой появится буква, нажав на которую, вы переходите внутрь этой вкладки.
Затем буквы появятся под каждой командой или выпадающим списком команд. Таким образом можно определить последовательность клавиш вызова нужной команды. Для объединения и центрирования ячеек в моей версии Excel необходимо последовательно нажать Alt – Я – Щ – Б. Это был первый способ.
Второй способ. Любым способом объединить ячейки и повторно вызвать команду клавишей F4 (повтор последнего действия).
Третий способ. Вначале поместите кнопку объединения ячеек на панель быстрого доступа. Для этого нажмите на треугольник справа от всех команд и там выберите Другие команды.
Вы попадаете в настройки панели быстрого доступа. Найдите команду Объединить и поместить в центре и добавьте ее в правое окно.
Кнопка появится на панели быстрого доступа.
После нажатия Alt под этой кнопкой появится цифра, зависящая от порядкового номера в ряду.
Теперь есть еще одна горячая клавиша объединить ячейки в Excel – Alt + 5.
Выравнивание по центру (без объединения ячеек)
Для расположения надписи в середине строки воспользуйтесь командой выравнивание по центру.
- Поместите надпись в левую ячейку строки, где должно произойти выравнивание.
- Выделите вправо нужное количество ячеек.
- Вызываете команду Правая кнопка мыши → Формат ячеек → Выравнивание → по горизонтали → по центру выделения.
Внешне результат такой же, как при объединении, только все ячейки останутся на своем месте. Есть пару нюансов.
- Не всегда понятно, в какой ячейке находится запись.
- Выравнивать по центру можно только по горизонтали, по вертикали нельзя.
В большинстве случаев вместо объединения ячеек лучше применять выравнивание по центру.
Формат ячеек влияет только на отображение данных. На деле часто приходится соединять содержимое из разных ячеек. Далее рассмотрим, как в Excel объединить данные из нескольких ячеек в одну.
Объединение ячеек с помощью & (амперсанд) и функции Excel СЦЕПИТЬ (CONCATENATE)
Объединение содержимого ячеек – очень распространенная задача. Выбор решения зависит от типа данных и их количества.
Если нужно сцепить несколько ячеек, то подойдет оператор & (амперсанд).
Обратите внимание, между ячейками добавлен разделитель в виде запятой с пробелом, то есть к объединению ячеек можно добавить произвольный текст. Полной аналогией & является применение функции СЦЕПИТЬ.
В рассмотренных примерах были только ячейки с текстом. Может потребоваться соединять числа, даты или результаты расчетов. Если ничего специально не делать, то результат может отличаться от ожидания. Например, требуется объединить текст и число, округленное до 1 знака после запятой. Используем пока функцию СЦЕПИТЬ.
Число присоединилось полностью, как хранится в памяти программы. Чтобы задать нужный формат числу или дате после объединения, необходимо добавить функцию ТЕКСТ.
Правильное соединение текста и числа.
Соединение текста и даты.
В общем, если вы искали, как объединить столбцы в Excel, то эти приемы работают отлично. Однако у & и функции СЦЕПИТЬ есть существенный недостаток. Все части текста нужно указывать отдельным аргументом. Поэтому соединение большого числа ячеек становится проблемой.
Функция Excel СЦЕП (CONCAT)
В Excel 2016 на смену функции СЦЕПИТЬ пришла функция СЦЕП. Она работает так же, только в качестве аргумента можно задать целый диапазон.
Все хорошо, но без пробелов. Если требуется соединить с разделителем, СЦЕП не сильно поможет. Поможет другая новая функция для объединения ячеек.
Функция Excel ОБЪЕДИНИТЬ (TEXTJOIN)
Функция ОБЪЕДИНИТЬ также появилась в Excel 2016 и решила сразу все проблемы склеивания ячеек: указание целого диапазона, вставка разделителя и даже пропуск пустых ячеек в диапазоне, чтобы не дублировать разделитель.
Синтаксис функции.
ОБЪЕДИНИТЬ(разделитель;пропускатьпустые;текст1;…)
разделитель – разделитель, который вставляется между ячейками
пропускать_пустые – если 0, то пустые ячейки включаются, если 1 – игнорируются. Обычно задают 1, чтобы не дублировать разделитель.
текст1;… – ссылка на диапазон или отдельные ячейки для сцепления.
Функция Excel ОБЪЕДИНИТЬ – лучшее решение для склеивания ячеек.
Заполнить – Выровнять
И еще один трюк. Объединить ячейки можно без формул. Исходные ячейки должны быть в одном столбце.
Выполните следующие действия.
- Выделите столбец с данными.
- Расширьте выделение вправо настолько, сколько предположительно должен занять объединенный текст или больше.
- Вызовите команду Главная (вкладка) -> Редактирование (группа) -> Заполнить -> Выровнять.
Текст будет помещен в одну ячейку через пробел.
Никаких формул.
Предупреждение.
- Если в ячейках есть числа, даты или формулы, фокус не получится. Работает только с текстом.
- Общая длина текста не должна превышать 255 символов. Лишнее будет перенесено на вторую строку автоматически (см. ролик ниже).
Подведем итоги, как объединить ячейки, строки и столбцы в таблице Эксель. С помощью форматирования можно просто удалить границы между ячейками. Это плохое решение. Лучше воспользоваться выравниванием по центру. Объединение ячеек в Excel без потери данных производится специальными функциями.
↓ Скачать файл с примером ↓
Поделиться в социальных сетях:
Источник
Объединение диапазона ячеек в одну или построчно с помощью кода VBA Excel. Метод Range.Merge и свойство MergeCells. Отмена объединения ячеек. Примеры.
Метод Range.Merge
Метод Merge объекта Range объединяет ячейки заданного диапазона в одну или построчно из кода VBA Excel.
Синтаксис метода:
Expression.Merge(Across)
- Expression – выражение, возвращающее объект Range.
- Across – логическое значение, определяющее характер объединения ячеек:
- True – ячейки объединяются построчно: каждая строка заданного диапазона преобразуется в одну ячейку.
- False – весь диапазон преобразуется в одну ячейку. False является значением по умолчанию.
Преимущество метода Range.Merge перед свойством MergeCells заключается в возможности построчного объединения ячеек заданного диапазона без использования цикла.
Свойство MergeCells
Свойство MergeCells объекта Range применяется как для объединения ячеек, так и для его отмены.
Синтаксис свойства с присвоением значения:
Expression.MergeCells = Boolean
- Expression – выражение, возвращающее объект Range.
- Boolean – логическое значение, определяющее необходимость объединения ячеек или его отмены:
- True – объединение ячеек заданного диапазона.
- False – отмена объединения ячеек.
С помощью свойства MergeCells можно из кода VBA Excel проверять диапазон (отдельную ячейку), входит ли он (она) в объединенную ячейку (True) или нет (False). Если проверяемый диапазон окажется комбинированным, то есть содержащим объединенные и необъединенные ячейки, компилятор сгенерирует ошибку.
Примеры объединения ячеек и его отмены
Пример 1
Наблюдаем, как происходит объединение ячеек при разных значениях параметра Across:
Sub Primer1() ‘Объединяем ячейки диапазона “A1:D4” построчно Range(“A1:D4”).Merge (True) ‘Объединяем диапазон “A5:D8” в одну ячейку ‘Across принимает значение False по умолчанию Range(“A5:D8”).Merge End Sub |
Не забываем, что логическое выражение True можно заменить единичкой, а False – нулем.
Пример 2
Отменяем объединение ячеек в диапазонах из первого примера:
Sub Primer2() Range(“A1:D8”).MergeCells = False End Sub |
Пример 3
Предполагается, что перед этим примером отменено объединение ячеек кодом из предыдущего примера.
Sub Primer3() ‘Объединяем ячейки диапазона “A1:D4” Range(“A1:D4”).MergeCells = 1 ‘Проверяем принадлежность диапазона ‘объединенной ячейке* MsgBox Range(“A1:C2”).MergeCells MsgBox Range(“A6:C7”).MergeCells ‘Ячейки диапазона “A5:D8” ‘можно объединить и так Cells(5, 1).Resize(4, 4).Merge End Sub |
*Если проверяемый диапазон окажется комбинированным, VBA Excel сгенерирует ошибку.
Предупреждение перед объединением
Если объединяемые ячейки не являются пустыми, пред их объединением появится предупреждающее диалоговое окно с сообщением: «В объединенной ячейке сохраняется только значение из верхней левой ячейки диапазона. Остальные значения будут потеряны.»
Пример 4
Наблюдаем появление предупреждающего окна:
Sub Primer4() ‘Отменяем объединение ячеек в диапазоне “A1:D4” Range(“A1:D4”).MergeCells = 0 ‘Заполняем ячейки диапазона текстом Range(“A1:D4”) = “Ячейка не пустая” ‘Объединяем ячейки диапазона “A1:D4” Range(“A1:D4”).MergeCells = 1 ‘Наблюдаем предупреждающее диалоговое окно End Sub |
Чтобы избежать появление предупреждающего окна, следует использовать свойство Application.DisplayAlerts, с помощью которого можно отказаться от показа диалоговых окон при работе кода VBA Excel.
Пример 5
Sub Primer5() ‘Отменяем объединение ячеек в диапазоне “A5:D8” Range(“A5:D8”).MergeCells = 0 ‘Заполняем ячейки диапазона “A5:D8” текстом Range(“A5:D8”) = “Ячейка не пустая” Application.DisplayAlerts = False Range(“A5:D8”).MergeCells = 1 Application.DisplayAlerts = True End Sub |
Теперь все прошло без появления диалогового окна. Главное, не забывать после объединения ячеек возвращать свойству Application.DisplayAlerts значение True.
Кстати, если во время работы VBA Excel предупреждающее окно не показывается, это не означает, что оно игнорируется. Просто программа самостоятельно принимает к действию ответное значение диалогового окна по умолчанию.
Источник
В данном примере напишем код макроса, который сможет автоматически найти и объединить все одинаковые ячейки в таблице Excel любой сложности.
Как объединить одинаковые ячейки в столбце используя макрос
Имеется отчет продаж в сети магазинов по целой стране. Нам нужно сделать таблицу данного отчета более читабельной. Для этого следует красиво сформатировать первый столбец, в котором содержаться названия штатов:
Мы хотим объединить все ячейки с одинаковыми значениями в столбце «Штат» (A). Это можно реализовать с помощью ручного выделения отдельных групп одинаковых значений и объединения их ячеек, воспользовавшись инструментом: «ГЛАВНАЯ»-«Выравнивание»-«Объединить и поместить в центре». Но если таблица содержит тысячи таких групп, да еще с разным количеством повторяющихся ячеек, тогда рационально написать макрос. Он сам быстро и автоматически выполнит всю работу за Вас.
Откройте редактор Visual Basic (ALT+F11):
И создайте новый модуль с помощью инструмента: «Insert»-«Module». А потом запишите в него VBA-код макроса:
Sub JoinDoubles()
Dim i As Long
Application.DisplayAlerts = False
For i = Selection.Rows.Count To 2 Step -1
If Selection.Cells(i, 1) = Selection.Cells(i – 1, 1) Then
Range(Selection.Cells(i – 1, 1), Selection.Cells(i, 1)).Merge
End If
Next
Selection.VerticalAlignment = xlVAlignCenter
Application.DisplayAlerts = True
End Sub
Теперь если нам необходимо объединить ячейки с одинаковыми значениями, то выделите диапазон A1:A18 и запустите макрос выбрав инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«JoinDoubles»-«Выполнить». Результат действия макроса отображен на рисунке:
В начале кода мы декларируем переменную для хранения показателей счетчика цикла. В цикле проверяем значения соседних ячеек, начиная с низа выделенного диапазона. Если ячейка содержит такое же значение значит она будет объединена.
Дополнительно в начале кода макроса устанавливаем свойство «False» для объекта «DisplayAlerts», чтобы предотвратить появления предупреждающего сообщение о попытке объединить непустые ячейки в программе Excel. В конце выполнения кода макроса обратно возвращаем свойство «True» для объекта «DisplayAlerts».
Обратите внимание! Если перед выполнением макроса выделить более одного столбца, то в результате будут объединены одинаковые значения только в первом столбце. Чтобы расширить поле действия макроса следует немного изменить его код.
Как объединить все одинаковые ячейки в любой таблице
Немного изменим структуру исходной таблицы:
На этот раз нам необходимо объединить все ячейки с одинаковыми значениями в столбце «Штат» (B) в столбце «№» (A).
Если мы хотим, чтобы действия макроса распространялось на несколько выделенных столбцов, то делаем следующее. Сначала добавим новую переменную:
Dim j As Long
Далее добавим строку с кодом начала нового цикла, который будет проходить по другим столбцам выделенного диапазона:
For j = 1 To Selection.Columns.Count
После конца, ранее созданного (внутреннего) цикла добавляем инструкцию Next для конца нового (внешнего) цикла . И соответственно сделаем код более читабельным с помощью отступов табуляции. Кроме того, для всех экземпляров объекта Cells во втором аргументе, вместо числа 1 введем переменную j (например, Selection.Cells(i, j)). Новая версия измененного кода макроса выглядит следующим образом:
Sub JoinDoubles()
Dim i As Long
Dim j As Long
Application.DisplayAlerts = False
For j = 1 To Selection.Columns.Count
For i = Selection.Rows.Count To 2 Step -1
If Selection.Cells(i – 1, j) = Selection.Cells(i, j) Then
Range(Selection.Cells(i – 1, j), Selection.Cells(i, j)).Merge
End If
Next
Next
Selection.VerticalAlignment = xlVAlignCenter
Application.DisplayAlerts = True
End Sub
Чтобы увидеть результат действия новой версии кода, выделяем всю таблицу и запускаем макрос:
Читайте также: как объединить одинаковые ячейки в строках таблицы.
Как видно на рисунке теперь макрос автоматически объединяет одинаковые значения сразу в двух столбцах.
Источник
В этой статье рассмотрим, как объединять ячейки в таблице Excel, а так же как объединить текст и другие типы данных в ячейках без потери данных.
Яндекс.Картинки
В Excel существует возможность объединения области из нескольких ячеек или столбцов в одну, а так же объединять данные в ячейках через формулы и Мгновенное заполнение, о котором вы можете узнать в отдельной статье.
Как объединить ячейки
Объединить несколько ячеек (строк/столбцов) в Excel очень просто. Способ применяется для объединения пустых ячеек или ячеек, среди которых только одна содержит данные, например шапка таблицы.
Для объединения выделите область ячеек, которые нужно объединить и нажмите на вкладке Главная (Выравнивание) подходящий вариант:
Объединение ячеек
Варианты объединения:
Варианты объединения ячеек Главная (Выравнивание)
Результат объединения выделенных ячеек:
Объединение ячеек
Использование объединения ячеек таким способом не самый лучший вариант, для дальнейшей работы с данными, например сортировка и фильтрация, создание сводных таблиц. Старайтесь использовать его в исключительных случаях, а лучше никогда.
Подобный эффект, но без последствий, даёт форматирование ячеек (Ctrl+1), вкладка Выравнивание ► по горизонтали ► по центру выделения:
Окно Формат ячеек
Если необходимо объединить несколько ячеек содержащих данные, то стандартными средствами сделать это не получится, о чем Excel вас любезно предупредит:
Объединение ячеек ограничение
Но вы настойчивы, и вот результат, мы потеряли часть данных:
Объединение ячеек
Нет не решаемых задач. На помощь придет макрос, который поможет объединить данные из разных ячеек (строк/столбцов) в одну общую без потери данных:
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
Результат работы макроса, объединенный текст в одной ячейке:
Результат объединения при помощи макроса
Как объединить ячейки при помощи формул
1. Самый простой способ объединить данные в ячейках – использование знака конкатенации & (амперсанд):
Объединение ячеек при помощи &
Для добавления & в формулу нажмите Shift+7, на ENG раскладке клавиатуры.
Ссылка на ячейки с добавлением оператора &, приведет к склейке данных без разделителя.Добавить разделитель можно используя в формуле необходимый разделитель заключённый в кавычки.
Например:
- пробел ” “;
- запятая и пробел “, ” ;
- точка с запятой “;” и т.д.
Объединение ячеек при помощи & с разделителем
2. Следующий вариант объединить строки – функция СЦЕПИТЬ (CONCATENATE), одна из текстовых функций, созданная для объединения двух или нескольких текстовых строк в одну.
Начиная с Excel 2016 является пока ещё рабочим атавизмом и используется в рамках совместимости версий.
Синтаксис функции:
СЦЕПИТЬ(текст1;[текст2];…), где текст1, ссылка на первую ячейку с данными или текстовую константу, соответственно [текст2] ссылка на следующую ячейку или текстовую константу и т.д. Объединять можно текстовое значение, число (дату) или ссылки на ячейки.
Формула СЦЕПИТЬ может содержать до 255 элементов и до 8 192 символов.
Объединение ячеек при помощи функции СЦЕПИТЬ
Функция склеивает текст без разделителей, устраняется проблема, добавлением между аргументами разделителя в кавычках.
При объединении текста проблем не возникнет, но в случае объединения чисел или дат результат может отличаться от ожиданий.
Например, требуется объединить текст и число в ячейке с формулой, округленное до целого числа или добавить дату.
Число присоединится так, как хранится в памяти программы, дата является числом, потому покажет себя без формата.
Чтобы задать нужный формат числу или дате после объединения, необходимо добавить функцию ТЕКСТ:
Объединение ячеек с числами и датами
Недостаток объединения данных при помощи оператора & и функции СЦЕПИТЬ, состоит в том, что все части текста нужно указывать отдельными аргументами, поэтому соединение большого числа ячеек является весьма утомительным делом.
Решение появляется начиная с Excel 2016, в арсенал которого вошли две новых функции СЦЕП (CONCAT) и ОБЪЕДИНИТЬ (TEXTJOIN), речь о которых пойдет ниже.
3. Функция СЦЕП, полностью выполняющая функционал предшественника, но работает и с диапазонами данных.
Синтаксис функции:
СЦЕП(текст1; [текст2]; …), где текст – элемент, который нужно присоединить: строка или массив строк, например диапазон ячеек.
Формула СЦЕП может содержать до 253 аргументов.
Если объединенная строка содержит свыше 32767 символов (ограничение на ввод данных для ячейки), возникнет ошибка ЗНАЧ!.
Объединение диапазона ячеек при помощи функции СЦЕП
Работает без проблем, но если требуется соединить диапазон с разделителем, СЦЕП не поможет.
4. Лучший вариант для объединения – функция ОБЪЕДИНИТЬ, объединяет текст из нескольких диапазонов и/или строк, вставляя между текстовыми значениями указанный пользователем разделитель.
Синтаксис функции:
ОБЪЕДИНИТЬ(разделитель; игнорировать_пустые; текст1; [текст2]; …), где
- Разделитель – символ, который будет вставлен между фрагментами, указывается в кавычках;
- игнорировать_пустые – если 0, то пустые ячейки включаются, если 1 – игнорируются;
- Текст… – диапазон ячеек, содержимое которых нужно склеить.
Формула ОБЪЕДЕНИТЬ может содержать до 252 аргументов.
Если объединенная строка содержит свыше 32767 символов (ограничение на ввод данных для ячейки), возникнет ошибка ЗНАЧ!.
Аргументы функции ОБЪЕДИНИТЬ
Результат выполнения функции ОБЪЕДИНИТЬ:
Объединение диапазона ячеек при помощи функции ОБЪЕДИНИТЬ
Спасибо, что дочитали до конца!
Было полезно? Ставьте лайк, пишите, что думаете в комментариях и подписывайтесь на канал, если еще не сделали этого.
Еще интересное по теме Excel:
Источник