Excel vba разъединить объединенные ячейки
Обновлено: 07.07.2024
Рискну перенести тему в "Мозговой штурм", может кто знает как это сделано?
Рискну перенести тему в "Мозговой штурм", может кто знает как это сделано? Serge_007
Рискну перенести тему в "Мозговой штурм", может кто знает как это сделано? Автор - Serge_007
Дата добавления - 07.07.2013 в 15:24
Skype: andre.tm.007
Donate: Q iwi: 9517375010
Кроме того, MergeCells тупо "убивает" значения всех ячеек, кроме левой верней Да, но макросов в файле нет.
Кроме того, MergeCells тупо "убивает" значения всех ячеек, кроме левой верней Serge_007
И что из того, что нет? Объединим ячейки макросом, затем его удалим. или объединим ячейки макросом из внешнего файла (надстройки, например).
А разъединять можем уже обычными средствами Excel.
И что из того, что нет? Объединим ячейки макросом, затем его удалим. или объединим ячейки макросом из внешнего файла (надстройки, например).
А разъединять можем уже обычными средствами Excel.
Skype: andre.tm.007
Donate: Q iwi: 9517375010
Дата добавления - 07.07.2013 в 16:16 Сентенцию не понял
Ты знаешь как это сделать? Автор - Serge_007
Дата добавления - 07.07.2013 в 16:19 Ну так ведь я же вроде отписал, как сделать.
Возьми пример, выделяй в нем нужные диапазоны - и запускай MergeSelection
Затем через Формат ячееек - Выравнивание сними "объединение ячеек". Данные будут на месте. Ну так ведь я же вроде отписал, как сделать.
Возьми пример, выделяй в нем нужные диапазоны - и запускай MergeSelection
Затем через Формат ячееек - Выравнивание сними "объединение ячеек". Данные будут на месте. AndreTM То есть, другими словами и без макроса если - копируем разъединенные ячейки куда-то в уголок, там объединяем, как нам нужно, объединенные копируем и вставляем форматом на старое место. Ну и уголок потом убиваем, конечно.Прикольно!
Так можно данные прятать. Ссылка на В3 дает 7916,. А визуально на листе, да и в строке формул, такого числа нет. То есть, другими словами и без макроса если - копируем разъединенные ячейки куда-то в уголок, там объединяем, как нам нужно, объединенные копируем и вставляем форматом на старое место. Ну и уголок потом убиваем, конечно.Прикольно!
Так можно данные прятать. Ссылка на В3 дает 7916,. А визуально на листе, да и в строке формул, такого числа нет. _Boroda_ Ага
Это и по тексту макроса хорошо видно, что данные действия можно просто проделать ручками. Ага
Это и по тексту макроса хорошо видно, что данные действия можно просто проделать ручками. AndreTM Я там и посмотрел. Сам не знал. Плюсик ставлю, конечно же. С этим можно ох как хорошо поиграться. И главное, очень-очень мало кто поймет, что ж такое творится-то, откуда данные берутся. А иногда это очень даже требуется. Я там и посмотрел. Сам не знал. Плюсик ставлю, конечно же. С этим можно ох как хорошо поиграться. И главное, очень-очень мало кто поймет, что ж такое творится-то, откуда данные берутся. А иногда это очень даже требуется. _Boroda_
AndreTM, _Boroda_, Alex_ST,
спасибо!
Действительно интересная вещь и иногда может очень даже понадобиться.
Добавлено:
не обязательно копировать ячейки. Главное формат. Объединяем пустые ячейки и переносим формат на нужные ячейки.
AndreTM, _Boroda_, Alex_ST,
спасибо!
Действительно интересная вещь и иногда может очень даже понадобиться.
Добавлено:
не обязательно копировать ячейки. Главное формат. Объединяем пустые ячейки и переносим формат на нужные ячейки. DV
Добавлено:
не обязательно копировать ячейки. Главное формат. Объединяем пустые ячейки и переносим формат на нужные ячейки. Автор - DV
Дата добавления - 07.07.2013 в 17:30
Оказывается.
Я грешил на сторонние программы. Где-то на краю сознания - был такой вопрос то ли на старой Планете, то ли еще где-то на просторах (может, галюники ).
Оказывается.
Я грешил на сторонние программы. vikttur Написал, а потом походил по гиперссылкам в теме..
The_Prist & Alex_ST - MergePlus Написал, а потом походил по гиперссылкам в теме..
The_Prist & Alex_ST - MergePlus SkyPro
знаю давно, но именно благодаря теме на планете.
насколько понял (хотя подробно не копал) - это вообще нигде и никак не документировано.
и, если на самом деле это так, то пользоваться можно, но осознавая, что в 2013-м или 2017-м всё может стать иначе
пс. не пользуюсь - реального применения не нашёл.
знаю давно, но именно благодаря теме на планете.
насколько понял (хотя подробно не копал) - это вообще нигде и никак не документировано.
и, если на самом деле это так, то пользоваться можно, но осознавая, что в 2013-м или 2017-м всё может стать иначе
пс. не пользуюсь - реального применения не нашёл. ikki
пс. не пользуюсь - реального применения не нашёл. Автор - ikki
Дата добавления - 09.07.2013 в 16:10
Многократное объединение отдельных групп ячеек в столбцах вручную – требует много времени и сил. Поэтому рационально будет воспользоваться макросом, который автоматически выполнит всю эту, плюс еще и дополнительную работу.
Как объединить 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 все строки объединились бы в одну ячейку, что не есть хорошо.
В программе Excel присутствует кнопка для разъединения объединенных ячеек таблицы на закладке: «ГЛАВНАЯ»-«Выравнивание»-«Отменить объединение ячеек». Но что, если эту операцию нужно выполнять многократно, да еще и после нее заполнять данными ново созданные ячейки. Реализовать данную задачу вручную – это весьма затратное занятие по времени и силам. Здесь рационально воспользоваться макросом.
Макрос для разъединения объединенных ячеек в Excel
Допустим у нас уже имеется вполне читабельная таблица списка заказов, в которой имеются объединенные ячейки в столбце «Год». Пример, такой таблицы изображен ниже на рисунке:
Но нам необходимо преобразовать данную таблицу в стандартный формат, например, для создания отчета на основе сводной таблицы. Для этого откроем редактор Visual Basic (ALT+F11):
И вставим новый стандартный модуль используя инструмент в редакторе: «Insert»-«Module». А после чего запишем в модуль VBA код макроса для разъединения объединенных ячеек:
Sub RazdelitVstavit()
Dim adres As String
adres = ActiveCell.MergeArea.Address
If adres <> ActiveCell.Address Then
ActiveCell.UnMerge
ActiveCell.Copy
ActiveSheet.Paste ActiveSheet.Range(adres)
Application.CutCopyMode = False
End If
End Sub
Если мы хотим отменить объединение ячеек в столбце «Год» и заполнить созданные ячейки соответствующими значениями (годами), тогда перейдите на одну большую объединенную ячейку B2 и запустите макрос: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«RazdelitVstavit»-«Выполнить».
В данном VBA коде макроса используется только одна переменная. Она хранит в себе адрес диапазона ячеек B2:B15 которые охватывает одна активная объединенная ячейка.
Адрес активной ячейки отображается в поле «Имя» (напротив строки формул Excel). Но там не отображается полный адрес объединенной ячейки.
Для пользователя в поле «Имя» будет адрес отображаться одинаково, но в макросе их можно различить с помощью методов объекта ActiveCell.MergeArea.Addres. В зависимости какой тип активных ячеек будет возвращен тип адреса – одна ячейка или диапазон. Если активная ячейка не является объединенной, тогда в переменной будет храниться только адрес одной активной ячейки, а не целого диапазона. Далее макрос проверяет является ли текущая активная ячейка – объединенной, с помощью сравнения двух способов получения адреса для одной и той же активной ячейки. Тот способ, который передал адрес в переменную из метода объекта ActiveCell.MergeArea.Addres и обычный – ActiveCell.Addres. Если адрес в переменной и адрес получен обычным способом не совпадает, значит она является объединенной и код выполняется дальше.
С помощью метода объекта ActiveCell.UnMerge выполняется разъединение объединенной активной ячейки. Далее копируется ее содержимое и заполняется диапазон на листе, адрес которого получен из переменной, его же ранее содержала в себе объединенная активная ячейка. После копирования значения для объекта CutCopyMode устанавливается свойство False, чтобы прекратить процесс копирования. В результате таблица листа заказов будет иметь такой же вид как показано ниже на рисунке:
Данный макрос позволяет разъединить объединенные ячейки, которые используют любое направление объединения: как по вертикали, так и полгоризонтали. Ее значение будет одинаково вставлено во все ячейки, созданные после разъединения.
Внимание! Объединенная ячейка может содержать в качестве значения формулы. В такие случаи после запуска макроса эта формула будет вставлена во все ячейки созданных в результате отмены объединения только из относительных ссылок в адресах, поскольку в переменной не будет символа $ необходимого для абсолютного или смешанного адреса.
Как разъединить объединенные ячейки сразу в нескольких диапазонах
Если мы хотим, чтобы данный макрос можно было применить одновременно для нескольких объединенных ячеек в выделенном диапазоне, тогда добавим еще одну переменную, которая дополнит код счетчиком цикла:
Создадим цикл, который будет перемещаться по всем выделенным объединенным ячейкам:
For i = 1 To Selection.Count
В конце кода не забудем добавить конец цикла:
Вместо ссылки на активную ячейку Active.Cell теперь будем использовать ссылку на очередную по счету ячейку в выделенном диапазоне: Selection.(i). Полная версия усовершенствованного макроса выглядит следующим образом:
Sub RazdelitVstavit()
Dim adres As String
Dim i As Long
For i = 1 To Selection.Count
adres = Selection(i).MergeArea.Address
If adres <> Selection(i).Address Then
Selection(i).UnMerge
Selection(i).Copy
ActiveSheet.Paste ActiveSheet.Range(adres)
Application.CutCopyMode = False
End If
Next
End Sub
Цикл, который перемещается по каждой объединенной ячейке выделенного диапазона, каждый раз вызывает VBA код макроса для разъединения их диапазона объединения с учетом всех выше описанных условий.
Так же стоит отметить, что выделенный диапазон может содержать необъединенные ячейки, которые будут просто игнорироваться макросом. Если бы мы не усовершенствовали наш макрос, то при выделении нескольких объединенных ячеек – разделилась бы только первая.
Всем доброго времени суток!
Нужна помощь знатоков VBA в написании макроса!
Задача:
1. Имеется файл Excel с объединенными ячейками (см. приложение)
2. Необходим макрос который находит объединенную ячейку, разъединяет её и во все получившиеся после разъединения ячейки записывает значение, которое было в объединенной ячейке.
3. В примере файла Excel: на листе1 исходные данные, на листе 2 как должно быть после применения макроса.
Всем доброго времени суток!
Нужна помощь знатоков VBA в написании макроса!
Задача:
1. Имеется файл Excel с объединенными ячейками (см. приложение)
2. Необходим макрос который находит объединенную ячейку, разъединяет её и во все получившиеся после разъединения ячейки записывает значение, которое было в объединенной ячейке.
3. В примере файла Excel: на листе1 исходные данные, на листе 2 как должно быть после применения макроса.
Нужна помощь знатоков VBA в написании макроса!
Задача:
1. Имеется файл Excel с объединенными ячейками (см. приложение)
2. Необходим макрос который находит объединенную ячейку, разъединяет её и во все получившиеся после разъединения ячейки записывает значение, которое было в объединенной ячейке.
3. В примере файла Excel: на листе1 исходные данные, на листе 2 как должно быть после применения макроса.
Спасибо! Автор - Aleksej
Дата добавления - 16.08.2016 в 07:22
Здравствуйте.
Последовательность действий такая:
- выделяем нужный диапазон (в примере А3:G29)
- снимаем объединение ячеек
- нажимаем F5 -- Выделить -- Пустые ячейки -- ОК
- нажимаем на клавиатуре = и стрелку вверх. В строке формул должно получиться =A3
- нажимаем на клавиатуре сочетание Ctrl+Enter
Здравствуйте.
Последовательность действий такая:
- выделяем нужный диапазон (в примере А3:G29)
- снимаем объединение ячеек
- нажимаем F5 -- Выделить -- Пустые ячейки -- ОК
- нажимаем на клавиатуре = и стрелку вверх. В строке формул должно получиться =A3
- нажимаем на клавиатуре сочетание Ctrl+Enter
Читайте также: