Svinkovod.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

Автоматическое объединение большого количества ячеек по вертикали

Допустим мы имеем маркетинговый план внедрения нового информационного программного продукта:

маркетинговый план.

Чтобы план было легче визуально анализировать лучше объединить ячейки этапов выполнения плана: A2:A4, B2:B4 и т.д. К сожалению, многократно объединять диапазоны с большим количеством строк вручную – это задание требует слишком много времени и сил. Кроме того, можно допустить много ошибок после очередного десятка выделения ячеек перед объединением. Рассмотрим каким способом можно существенно облегчить свой труд переложив большую часть работы на простую программу, написанную на языке VBA прямо в Excel. Для этого следует написать макрос, который безошибочно быстро и автоматически объединит ячейки диапазонов с разным количеством строк для каждого столбца.

  1. Сначала откройте редактор макросов: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» (или просто нажмите ALT+F11). Visual Basic.
  2. Откройте стандартный модуль выбрав инструмент в редакторе: «Insert»-«Module» и введите в него следующий код макроса для объединения ячеек:

Module.

Пока что это еще не полная версия макроса поэтому перед тем как его проверить нам все еще вручную необходимо выделить первый диапазон A2:D4. После чего можно выполнить нашу первую версию макроса.

Запуск макроса для объединения ячеек

Выбираем инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы».

РАЗРАБОТЧИК.

В появившемся диалоговом окне выделяем значение «ObedenitVertikal» и нажимаем на кнопку «Выполнить».

Выполнить.

Потом снова вручную выделите новый диапазон A5:D9 и повторно выполните тот же макрос. Ячейки будут выделены как показано ниже на рисунке:

Пример.

В начале кода определены 3 переменные: две из них выполняют функцию счетчика, а третья служит для временного хранения текстового содержания ячеек. Счетчик первой переменной в цикле проходит по очереди все столбцы выделенного диапазона ячеек. В каждом таком столбце в текстовую переменную записывается текст, который содержится в первой ячейке каждого выделенного столбца. Второй счетчик идет по строкам каждого столбца и дописывает в текстовую переменную текстовые значения из остальных ячеек каждого текущего столбца выделенного диапазона – сверху вниз. Тексты, взятые из отдельных ячеек разделяться символом обрыва строки.

Символ обрыва строки вставлен с помощью функции Chr(10). Каждый вводимый символ из клавиатуры имеет свой код ASCII. Если введем код 10 в качестве аргумента для функции Chr(), тогда она будет возвращать символ обрыва строки. Такой же код ASCII на клавиатуре вызывается клавишей Enter для обрыва строки во всех текстовых редакторах.

Потом остальной код макроса объединяет все ячейки столбцов в выделенном диапазоне, а потом заполняет их текстом из текстовой переменной. Чтобы не появлялось предупреждающее сообщение об объединении ячеек, в начале кода программы макроса отключаем отображение сообщений в Excel средствами программирования. Для этой цели используем свойство: Application.DisplayAlerts = Fale. После выполнения кода макроса изменяем значение свойства на True, чтобы в дальнейшем процессе работы с программой Excel все сообщения предупреждений (Alerts) имели возможность отображаться.

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

Читайте так же:
Интерполяция данных в excel

Модернизация и настройка кода макроса для объединения ячеек

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

intext = intext & Chr(10) & “——” & Chr(10) & Selection.Cells(j, i)

разбиение на строки.

Если в объединенной ячейке мы хотим всегда вставлять только текст из первой ячейки в выделенном столбце (без текстов, записанных в остальных ячейках), тогда удалим или закомментируем переменную второго счетчика и часть кода второго цикла:

закомментируем.

Если нам нужно чтобы выполнять макрос после выделения нескольких диапазонов (с удержанием клавиши CTRL), тогда можно добавить еще одну переменную, которая будет дополнять функцию счетчика:

Перед первым циклом добавим новую строку с кодом:

For k = 1 To Selection.Areas.Count

А после последнего цикла добавим строку конца нового цикла:

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

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

Тепер выделяем 2 диапазона подряд A2:D4, A5:D8, A с нажатой клавишей CTRL на клавиатуре:

2 диапазона подряд.

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

Пример2.

Если выполнить первую версию макроса (без всех этих изменений), для многократного выделения диапазонов с нажатой клавишей CTRL, то объединение строк по столбцам будет выполнено только для первого диапазона.

Как создать ряд ячеек в Excel

Итог: Изучите два разных способа быстрого объединения ряда ячеек. Это включает в себя метод Ctrl + щелчок левой кнопкой мыши и бесплатный макрос VBA, который позволяет быстро и легко создавать формулы объединения или Ampersand.

Уровень мастерства: Средний

Create Concatenate Formula with Ctrl+Left-click Excel Create Concatenate Formula for Range of Cells with Concatenate Macro in Excel

Сцепление: хорошее и плохое

Функция CONCATENATE может быть очень полезна для объединения значений нескольких ячеек в одну ячейку или формулу. Одно из популярных применений — создание формул VLOOKUP на основе нескольких критериев.

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

Concatenate Individual vs Range of Cells Formula Error Comparison

Вариант № 1: Ctrl + щелчок левой кнопкой мыши, чтобы выбрать несколько ячеек

Вы можете удерживать клавишу Ctrl при выборе ячеек для добавления в формулу CONCATENATE. Это экономит время при вводе запятой после каждого выбора ячейки.

На следующем снимке экрана показано, как использовать сочетание клавиш Ctrl + щелчок левой кнопкой мыши. Вам не нужен макрос для этого, он встроен в Excel.

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

Вариант № 2: CONCATENATE макроса диапазона

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

Макрос Concatenate использует InputBox, который позволяет вам выбирать диапазон ячеек. Затем он создает формулу Concatenate или Ampersand, создавая аргумент для каждой ячейки в выбранном диапазоне.

Вы можете назначить макрос кнопке на ленте или комбинации клавиш. Макрос позволяет очень быстро создавать формулы.

Читайте так же:
Как безопасно обновить биос

Как работает макрос?

Макрос в основном разделяет ссылку на диапазон, заданную с помощью InputBox, а затем создает формулу в активной ячейке. Вот резюме высокого уровня:

  1. Выберите ячейку, в которую нужно ввести формулу, и запустите макрос.
  2. Появляется InputBox и предлагает вам выбрать ячейки, которые вы хотите объединить. Вы можете выбрать диапазон ячеек с помощью мыши или клавиатуры.
  3. Нажмите ОК
  4. Макрос разделяет диапазон на ссылки на одну ячейку, поэтому эта ссылка на диапазон (A2: C2) превращается в (A2, B2, C2).
  5. Формула Concatenate или Ampersand создается в активной ячейке.

Опции макроса Concatenate

  1. Тип формулы. Макрос «Concatenate» позволяет создать формулу «Concatenate» или «Ampersand».
  2. Символ разделителя — Вы также можете добавить символ разделителя между каждой ячейкой. Это удобно, если вы хотите добавить запятые, пробелы, тире или любой символ между соединенными ячейками.
  3. Абсолютные ссылки — макрос также дает вам возможность сделать ссылки на ячейки абсолютными (привязанными). Это добавит знак $ перед буквой столбца или номером строки. Это удобно, если вы копируете формулу в определенном направлении и не хотите, чтобы относительные ссылки на ячейки менялись.

Функция Concatenate или формулы Ampersand

Клетки также могут быть объединены с помощью символа
Ampersand (&). Это альтернатива использованию функции CONCATENATE. Следующие две формулы приведут к одному и тому же результату.

Concatenate vs Ampersand Formulas in Excel

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

Опять же, макрос позволяет вам создать либо Concatenate , либо формулу Ampersand.

Код VBA

Вот код для макросов Concatenate и Ampersand .

Как использовать код

Добавьте кнопки макроса на ленту

В приведенных выше примерах я добавил этот код в свою книгу личных макросов. Затем я добавил кнопки макросов на ленту для каждого из 4 макросов. Я создал новую группу на вкладке Формулы и добавил к ней кнопки.

Add Concatenate Macro Buttons to the Ribbon

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

Назначить сочетание клавиш для макросов

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

Я запускаю макрос, помещая кнопку макроса на панель быстрого доступа, а затем нажимаю сочетание клавиш Alt + Button Position для QAT. Ознакомьтесь с этой статьей о том, как использовать сочетания клавиш QAT для получения более подробной информации.

Дополнительные ресурсы

Concatenate с разрывами строк — Дейв Брунс из ExcelJet имеет отличную статью и видео о том, как добавить разрывы строк в формулу конкатенации. Отличный совет для присоединения почтовых адресов.

Комбинируйте ячейки без сцепления — Дебра Далглиш в Contextures объясняет, как создавать формулы Ampersand.

Concatenate нескольких ячеек с использованием Transpose — Chandoo имеет интересный подход к этой проблеме с помощью функции TRANSPOSE.

Заключение

Этот инструмент должен значительно ускорить и упростить создание формул Concatenate или Ampersand. Это может быть не то, что вы используете каждый день, но это здорово иметь в вашем наборе инструментов.

Читайте так же:
Как восстановить предыдущую сессию в гугл хром

Объединение ячеек в Microsoft Excel

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

Самый простой способ

1 . Выделяем то, что нужно объединить.

2 . Нажимаем на кнопку во вкладке «Главная» в верхней панели.

По умолчанию эта кнопка соединяет всё выделенное и помещает содержимое в центре. Но есть и другие варианты – для их просмотра нажмите на маленькую стрелку справа от кнопки.

Еще один простой способ

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

2 . В окошке переходим на вкладку «Выравнивание» и ставим галочку напротив пункта «Объединение ячеек», после чего нажимаем OK.

Объединение ячеек с данными

Если вы собираетесь соединить несколько ячеек, в которых уже есть информация, то в результате будут сохранены не все данные. Часть информации удалится.

Чтобы этого не произошло, склеивать их нужно по-другому. Есть несколько способов: через сцепление данных, при помощи программы Блокнот и через макрос.

Первый способ (сцепление) я бы рекомендовал начинающим пользователям, второй (Блокнот) – любителям необычных решений, а третий (макрос) – тем, кто знает, что такое Visual Basic.

Способ первый (через сцепление)

Шаг 1: склеиваем данные

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

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

1 . Щелкаем по пустой ячейке.

2 . Печатаем знак = и большими буквами слово СЦЕПИТЬ

3 . Открываем скобку и щелкаем по первой ячейке с данными, затем печатаем точку с запятой и щелкаем по второй, затем опять точку с запятой – и по третьей. После это закрываем скобку.

4 . Нажимаем клавишу Enter на клавиатуре.

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

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

Склеивание через знак & . Находится этот символ там же, где цифра 7 на клавиатуре. Набирать его нужно на английской раскладке при зажатой клавише Shift.

Принцип почти такой же, как и при склеивании через функцию СЦЕПИТЬ:

1 . Щелкаем в пустой ячейке и печатаем в ней знак =

2 . Нажимаем на первую ячейку с данными, печатаем символ & и нажимаем на вторую ячейку, затем опять вводим & и нажимаем на третью и так далее.

Как и в предыдущем случае, любой дополнительный символ (разделитель) должен быть указан в кавычках.

Шаг 2: убираем дубли

Не важно, каким способом, но данные мы склеили.

Если нужно получить такой же результат и для остальных ячеек, эту «формулу» можно растянуть:

  • Щелкаем по ячейке с результатом
  • Наводим на маленький черный квадратик внизу справа
  • Когда курсор примет вид тонкого черного плюса, нажимаем левую кнопку мышки и, не отпуская ее, растягиваем по другим ячейкам
Читайте так же:
Как вернуть старый интерфейс гугл хром

У меня получилось вот что:

Подробнее о «растягивании» можно узнать из вот этого урока.

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

Иногда это подходит, но что делать, если нужны только склеенные данные? Выход есть!

1 . Выделяем новые (склеенные) ячейки.

2 . Щелкаем по любой из них правой кнопкой мышки и из меню выбираем «Копировать».

3 . Выделяем какие-нибудь другие пустые ячейки.

4 . Нажимаем по любой из них правой кнопкой мышки и выбираем «Специальная вставка».

5 . В окошке выбираем пункт «Значения» (вверху) и нажимаем ОК.

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

Собственно, теперь все предыдущие данные можно удалить, а эти (новые) скопировать и вставить на их место.

Способ второй (через Блокнот)

Честно говоря, мне этот способ даже больше нравится – он быстрее.

1 . Выделяем в Экселе ячейки с нужными данными (можно выделить целые столбы/строки).

2 . Копируем их (правая кнопка мыши – Копировать).

3 . Открываем программу Блокнот: Пуск – Все программы – Стандартные — Блокнот. Или открываем Пуск и печатаем слово блокнот в поле для поиска (внизу).

4 . Вставляем в окно программы скопированные данные (правой кнопкой мышки по пустому месту – Вставить).

5 . Копируем символ табуляции.

Для этого в пустой строчке Блокнота (перейти на нее можно, нажав на Enter) нажимаем один раз клавишу Tab на клавиатуре.

Указатель сразу перемещается чуть правее. Зажимаем левую кнопку мышки и выделяем этот отрезок, который визуально похож на десяток пробелов. Затем копируем (правой кнопкой по выделенному – Копировать).

6 . Вверху программы Блокнот нажимаем на пункт «Правка» и выбираем «Заменить…».

7 . В первое поле («Что:») вставляем скопированный символ табуляции, а во второе поле («Чем:») вставляем необходимый нам разделитель, например, жмем клавишу пробел.

8 . Нажимаем на кнопку «Заменить все» и закрываем это маленькое окошко.

После этого данные в Блокноте немного изменятся – текст как будто бы чуть-чуть сожмется.

9 . Выделяем все это в Блокноте и копируем.

10 . Переходим в программу Excel и вставляем скопированные данные (удалив предыдущие).

Способ третий (макрос)

Еще один вариант объединения ячеек в Экселе без потери данных. Он чуть более сложный — для тех, кто знает, что такое Visual Basic.

Данный способ я позаимствовал из вот этой статьи. Кстати, очень клёвый сайт, советую всем, кто работает с программой Excel.

1 . Открываем вкладку «Вид» и нажимаем на кнопку «Макросы».

2 . Печатаем название для макроса, нажимаем «Создать».

3 . Откроется редактор Visual Basic. Вставляем туда следующий код:

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

Читайте так же:
Как в ворде исправить орфографические ошибки

4 . Закрываем редактор Visual Basic.

Теперь, чтобы объединить ячейки с данными, нужно их выделить, после чего на вкладке «Вид» нажать кнопку «Макросы» и в новом окошке на «Выполнить».

Правда, такой файл нужно будет сохранить в специальном формате с поддержкой макросов: Файл – Сохранить как – в поле «Тип файла» выбрать «Книга Excel с поддержкой макросов».

О том, как соединить текст из разных ячеек на основании условий.

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

Например, существует перечень поставщиков сока для крупной компании. У каждого поставщика имеется несколько наименований продукции.

ПоставщикНаименование сока
ООО «Крошка-Морошка»«Сок Ванили»
ООО «Икар-Макар»«Ореховый настой»
ООО «Крошка-Морошка»«Апельсинка»
ООО «Крошка-Морошка»Морс «Старый Ельник»
ООО «Крошка-Морошка»«Тыквовый»
ООО «Крошка-Морошка»«Огуречный с сиропом»
ООО «Икар-Макар»«Яблочный с мякотью»
ООО «Икар-Макар»«Груша-дичка»
ПАО «Старгарден»«Морс из клюквы»
ООО «Крошка-Морошка»Сок «Натуральный ананас»
ПАО «Старгарден»Напиток газированный «Лаванда»
ООО «Крошка-Морошка»Настой морошки
ПАО «Старгарден»Напиток газированный «Абрикос»

соединение текста в эксель

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

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

Чтобы добавить макрос, следует выполнить следующие действия:

  • Войти в панель разработчика;
  • Нажать «Просмотр кода»;Макро VBA Excel
  • В окне project кликнуть правой кнопкой мыши и вставить модуль;Вставить модуль
  • Открыть просмотр кода нового модуля;
  • Скопировать приведенный ниже код макроса VBA и вставить его в модуль;
  • Сохранить файл.

Код макроса VBA :

Function Scepka(DiapazonScepki As Range, DiapazonPoiska As Range, Uslovie As String)

Dim Delitel As String, i As Long , OutText As String

‘назначаем переменные для работы макроса DiapazonScepki — это диапазон с текстом, который следует сцепить; DiapazonPoiska – это диапазон условий для сравнения; Uslovie – это то условие, которое мы ищем; Delitel – символ делителя.

Delitel = «, » ‘указываем разделитель (символ, который будет разделять сцепленный текст, можно поставить пробел или пустоту «» – тогда текст сольется в одно слово)

If DiapazonPoiska.Count <> DiapazonScepki.Count Then

Scepka = CVErr(xlErrRef)

Exit Function

End If ‘если диапазоны с данными для проверки и для сцепки отличаются по длине – функция выдает ошибку и закрывается

For i = 1 To DiapazonPoiska.Cells.Count

If DiapazonPoiska.Cells(i) Like Uslovie And Len(DiapazonScepki.Cells(i)) > 0 Then OutText = OutText & DiapazonScepki.Cells(i) & Delitel

Next i ‘сверяем ячейки между собой и присваиваем переменной OutText подходящие по условиям текстовые значения. Для точного совпадения оператор Like следует заменить на знак «=».

Scepka = Left(OutText, Len(OutText) — Len(Delitel))

End Function

Теперь когда функция условной сцепки текста добавлена в книгу Excel при помощи макроса , ее можно использовать для обработки текста следующим образом:

  • Поставить курсор в ячейку, предназначенную для внесения соединенного (сцепленного) текста.
  • Активировать мастер функций.
  • Среди функций «Определенных пользователем» выбрать Scepka.Мастер функций
  • В поле DiapazonScepki внести адреса ячеек, которые будут соединяться
  • В поле DiapazonPoiska внести адреса ячеек, по которым будет произведен поиск (сравнение).
  • В поле Uslovie внести условие поиска.Использование созданной функции
  • Нажать «Ок»
голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector