Vba excel объединить ячейки
Обновлено: 05.07.2024
В данном примере напишем код макроса, который сможет автоматически найти и объединить все одинаковые ячейки в таблице 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»-«Выполнить». Результат действия макроса отображен на рисунке:
В начале кода мы декларируем переменную для хранения показателей счетчика цикла. В цикле проверяем значения соседних ячеек, начиная с низа выделенного диапазона. Если ячейка содержит такое же значение значит она будет объединена.
Обратите внимание! Если перед выполнением макроса выделить более одного столбца, то в результате будут объединены одинаковые значения только в первом столбце. Чтобы расширить поле действия макроса следует немного изменить его код.
Как объединить все одинаковые ячейки в любой таблице
Немного изменим структуру исходной таблицы:
На этот раз нам необходимо объединить все ячейки с одинаковыми значениями в столбце «Штат» (B) в столбце «№» (A).
Если мы хотим, чтобы действия макроса распространялось на несколько выделенных столбцов, то делаем следующее. Сначала добавим новую переменную:
Далее добавим строку с кодом начала нового цикла, который будет проходить по другим столбцам выделенного диапазона:
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
Чтобы увидеть результат действия новой версии кода, выделяем всю таблицу и запускаем макрос:
Как видно на рисунке теперь макрос автоматически объединяет одинаковые значения сразу в двух столбцах.
Объединить ячейки чтобы придать таблице читабельный вид очень часто существенно усложняется с увеличением объема данных. Так же весьма неблагодарным занятием является присвоение соответственных границ для ячеек. На помощь пользователям приходят макросы, благодаря которым все эти действия можно выполнить автоматически.
Автоматическое объединение большого количества ячеек по вертикали
Допустим мы имеем маркетинговый план внедрения нового информационного программного продукта:
Чтобы план было легче визуально анализировать лучше объединить ячейки этапов выполнения плана: A2:A4, B2:B4 и т.д. К сожалению, многократно объединять диапазоны с большим количеством строк вручную – это задание требует слишком много времени и сил. Кроме того, можно допустить много ошибок после очередного десятка выделения ячеек перед объединением. Рассмотрим каким способом можно существенно облегчить свой труд переложив большую часть работы на простую программу, написанную на языке VBA прямо в Excel. Для этого следует написать макрос, который безошибочно быстро и автоматически объединит ячейки диапазонов с разным количеством строк для каждого столбца.
- Сначала откройте редактор макросов: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» (или просто нажмите ALT+F11).
- Откройте стандартный модуль выбрав инструмент в редакторе: «Insert»-«Module» и введите в него следующий код макроса для объединения ячеек:
Sub ObedenitVertikal()
Dim i As Long
Dim j As Long
Dim intext As String
Application.DisplayAlerts = False
For i = 1 To Selection.Columns.Count
intext = Selection.Cells(1, i)
For j = 2 To Selection.Rows.Count
intext = intext & Chr(10) & Selection.Cells(j, i)
Next
Selection.Columns(i).Merge
Selection.Cells(1, i) = intext
Next
Application.DisplayAlerts = True
End Sub
Пока что это еще не полная версия макроса поэтому перед тем как его проверить нам все еще вручную необходимо выделить первый диапазон A2:D4. После чего можно выполнить нашу первую версию макроса.
Запуск макроса для объединения ячеек
Выбираем инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы».
В появившемся диалоговом окне выделяем значение «ObedenitVertikal» и нажимаем на кнопку «Выполнить».
Потом снова вручную выделите новый диапазон A5:D9 и повторно выполните тот же макрос. Ячейки будут выделены как показано ниже на рисунке:
В начале кода определены 3 переменные: две из них выполняют функцию счетчика, а третья служит для временного хранения текстового содержания ячеек. Счетчик первой переменной в цикле проходит по очереди все столбцы выделенного диапазона ячеек. В каждом таком столбце в текстовую переменную записывается текст, который содержится в первой ячейке каждого выделенного столбца. Второй счетчик идет по строкам каждого столбца и дописывает в текстовую переменную текстовые значения из остальных ячеек каждого текущего столбца выделенного диапазона – сверху вниз. Тексты, взятые из отдельных ячеек разделяться символом обрыва строки.
Символ обрыва строки вставлен с помощью функции Chr(10). Каждый вводимый символ из клавиатуры имеет свой код ASCII. Если введем код 10 в качестве аргумента для функции Chr(), тогда она будет возвращать символ обрыва строки. Такой же код ASCII на клавиатуре вызывается клавишей Enter для обрыва строки во всех текстовых редакторах.
Внимание! Если ячейки выделенного диапазона будут содержать формулы, то после выполнения макроса эти формулы будут заменены на текст. В результате после объединения ячеек макросом, формулы могут быть утеряны.
Модернизация и настройка кода макроса для объединения ячеек
Если нам нужно изменить текст разделяющий отдельные строки символов содержащийся в целых ячейках, то можно вписать другой код символа, текст или несколько текстов соединенных символом амперсантом (&). Допустим мы хотим вставить между двумя символами разрыва строки текст, состоящий из пяти тире «-----». Тогда данную строку следует модифицировать следующим образом:
intext = intext & Chr(10) & “-----” & Chr(10) & Selection.Cells(j, i)
Если в объединенной ячейке мы хотим всегда вставлять только текст из первой ячейки в выделенном столбце (без текстов, записанных в остальных ячейках), тогда удалим или закомментируем переменную второго счетчика и часть кода второго цикла:
Если нам нужно чтобы выполнять макрос после выделения нескольких диапазонов (с удержанием клавиши CTRL), тогда можно добавить еще одну переменную, которая будет дополнять функцию счетчика:
Перед первым циклом добавим новую строку с кодом:
For k = 1 To Selection.Areas.Count
А после последнего цикла добавим строку конца нового цикла:
Соответственно добавим новый отступ, чтобы код был более читабельным. Кроме того, после всех изменений для объекта Selection добавим ссылку на диапазон:
Полная новая версия макроса для объедения ячеек выделенных нескольких диапазонов, выглядит так:
Sub ObedenitVertikal()
Dim i As Long
Dim j As Long
Dim k As Long
Dim intext As String
Application.DisplayAlerts = False
For k = 1 To Selection.Areas.Count
For i = 1 To Selection.Areas(k).Columns.Count
intext = Selection.Areas(k).Cells(1, i)
For j = 2 To Selection.Areas(k).Rows.Count
intext = intext & Chr(10) & Selection.Areas(k).Cells(j, i)
Next
Selection.Areas(k).Columns(i).Merge
Selection.Areas(k).Cells(1, i) = intext
Next
Next
Application.DisplayAlerts = True
End Sub
Тепер выделяем 2 диапазона подряд A2:D4, A5:D8, A с нажатой клавишей CTRL на клавиатуре:
В результате получаем идентичный вид таблицы с объединенными ячейками:
Если выполнить первую версию макроса (без всех этих изменений), для многократного выделения диапазонов с нажатой клавишей CTRL, то объединение строк по столбцам будет выполнено только для первого диапазона.
Многократное объединение отдельных групп ячеек в столбцах вручную – требует много времени и сил. Поэтому рационально будет воспользоваться макросом, который автоматически выполнит всю эту, плюс еще и дополнительную работу.
Как объединить 2 и несколько столбцов в таблице Excel
Допустим, что мы имеем таблицу содержащую данные оп договорам и выставленных на их основе счетов. Данные по договорам находиться в столбцах D, E и F, но некоторые фактуры взяты с других подчиненных документов, а не из договора. Для таких случаев в столбце D указывается номер договора или описание подчиненного документа для каждой фактуры.
Нам необходимо объединить ячейки в этих трех столбцах (D, E и F) таким образом, чтобы для каждой строки где нет договоров была одна объединенная ячейка.
Откроем редактор Visual Basic (ALT+F11):
И вставим новый стандартный модуль используя инструмент в редакторе: «Insert»-«Module». А после чего запишем в модуль VBA код макроса для объединения ячеек столбцов по горизонтали:
Sub ObedinitGorizontal()
Dim i As Long
Dim j As Long
Dim savetext As String
Application.DisplayAlerts = False
For i = 1 To Selection.Rows.Count
savetext = Selection.Cells(i, 1)
For j = 2 To Selection.Columns.Count
savetext = savetext & Chr(32) & Selection.Cells(i, j)
Next
Selection.Rows(i).Merge
Selection.Cells(i, 1) = savetext
Selection.Cells(i, 1).HorizontalAlignment = xlHAlignCenter
Next
Application.DisplayAlerts = True
End Sub
В первом цикле данного кода выполняется прогон по отдельных строках выделенного диапазона, а во втором объединяться значения столбцов в Excel. Тексты между собой разделяться символом пробела, его код в таблице символом ASCII имеет номер 32.
В конце выполнения первого цикла VBA кода мы изменяем свойство HorizontalAlignment для объединенной ячейки, что позволяет выровнять текст по центру с помощью макроса.
Если мы хотим объединить ячейки в строках содержащих информацию о выставленных счетах без подчиненных договоров, выделяем диапазон ячеек D5:F7 и запускаем наш макрос: «РАЗРАБОТЧИК»-«Код»-«Макросы». В появившемся диалоговом окне выбираем имя нашего макроса «ObedinitGorizontal» и нажимаем на кнопку «Выполнить». А далее снова выделяем диапазон ячеек D9:F11 и вновь запускаем макрос «ObedinitGorizontal». В результате ячейки будут объединены как показано ниже на рисунке:
Внимание! Если объединяемые ячейки будут содержать формулы, то они могут быть утеряны так как в процессе объединения формулы преобразуются в интерпретированный текст.
Модификация и настройка кода макроса
Если мы хотим разделять тексты не пробелом, а вертикальной линией, тогда нужно просто изменить ASCII код в аргументе функции Chr() на 124:
savetext = savetext & Chr(124) & Selection.Cells(i, j)
Если в объединенных ячейках мы хотим вставлять только тексты из первого столбца (D), без текстов, записанных в остальных двух столбцах E и F тогда удалите или закомментируйте переменную j и код второго цикла:
Как объединить ячейки по столбцам в Excel
Если мы хотим выполнить макрос одновременно и сразу для нескольких выделенных диапазонов ячеек (с помощью удержания клавиши CTRL на клавиатуре), добавим еще одну переменную:
Перед первым циклом добавим строку для начала нового цикла прохода по всех выделенных диапазонах:
For k = 1 To Selection.Areas.Count
В конце первого цикла не забудьте добавить конце для нового цикла:
Кроме этого для всех элементов объекта Selection следует добавить ссылку на диапазон: Selection.Areas(k). Полная версия VBA кода модифицированного макроса выглядит следующим образом:
Sub ObedinitGorizontal()
Dim i As Long
Dim j As Long
Dim k As Long
Dim savetext As String
Application.DisplayAlerts = False
For k = 1 To Selection.Areas.Count
For i = 1 To Selection.Areas(k).Rows.Count
savetext = Selection.Areas(k).Cells(i, 1)
For j = 2 To Selection.Areas(k).Columns.Count
savetext = savetext & Chr(32) & Selection.Areas(k).Cells(i, j)
Next
Selection.Areas(k).Rows(i).Merge
Selection.Areas(k).Cells(i, 1) = savetext
Selection.Areas(k).Cells(i, 1).HorizontalAlignment = xlHAlignCenter
Next
Next
Application.DisplayAlerts = True
End Sub
Если хотите узнать как объединить строки в Excel, читайте статью: макрос для объединения строк в таблице.
Если бы мы не изменили код, то даже при многократном выделении диапазонов с нажатой клавишей CTRL все строки объединились бы в одну ячейку, что не есть хорошо.
| Объединить пустые поля выше | ||
 |  |  |
| Объединить оставшиеся пробелы | ||
 | |  |
Объединить пустые ячейки выше на основе столбца
Предположим, что есть диапазон данных в трех столбцах, и теперь вы хотите объединить данные выше на основе столбца C.
1. Нажмите Alt + F11 keys, чтобы включить Microsoft Visual Basic для приложений окно и щелкните Вставить > Модуль. Смотрите скриншот:
2. Затем вставьте приведенный ниже код в пустой скрипт. Смотрите скриншот:
VBA: объединить пустое поле выше на основе следующего столбца
3. Нажмите F5 нажмите клавишу для запуска кода, а затем появится диалоговое окно, напоминающее вам о выборе диапазона для работы. Смотрите скриншот:
4. Нажмите OK, то пустые ячейки были объединены выше. Смотрите скриншот:
Объединить пустые ячейки выше (работает только для одного столбца)
Вот код макроса, который может объединить пустые ячейки выше в указанном столбце.
1. Нажмите Alt + F11 ключи для включения Microsoft Visual Basic для приложений окно и щелкните Вставить > Модуль. Смотрите скриншот:
2. Вставьте приведенный ниже код в скрипт. Смотрите скриншот:
VBA: объединить пустые ячейки выше
3. Нажмите F5 нажмите клавишу, чтобы запустить код, и появится диалоговое окно, в котором вы можете выбрать диапазон столбцов. Смотрите скриншот:
4. Щелкните ОК. И пустые ячейки в выделенной области были объединены выше.
 | |  |
Объединить оставшиеся пустые ячейки
Если вы хотите объединить оставшиеся пустые ячейки, следующий код может оказать вам услугу.
1. Нажмите Alt + F11 ключи для включения Microsoft Visual Basic для окна приложений и щелкните Вставить > Модуль. Смотрите скриншот:
2. Затем вставьте приведенный ниже код в пустой скрипт. Смотрите скриншот:
VBA: объединить оставшиеся пробелы
3. Нажмите F5 нажмите клавишу для запуска кода, и появится диалоговое окно для выбора диапазона. Смотрите скриншот:
Читайте также: