Конкатенация строк Excel vba

Обновлено: 05.07.2024

В Microsoft Excel вы можете использовать макрос, чтобы объединить данные в двух соседних столбцах и отобразить результат в столбце справа от столбцов, содержащих ваши данные. Эта статья содержит пример макроса Microsoft Visual Basic для приложений (VBA) (подпроцедура) для выполнения этой задачи.

Дополнительная информация

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

Пример макроса Visual Basic для приложений (VBA)

ПРИМЕЧАНИЕ. Оператор ActiveCell.Offset(0, 1).FormulaR1C1 можно заменить оператором ActiveCell.Offset(0, 1).Formula. Их можно использовать с одинаковым успехом, если вы используете только текст и числа (не формулы). R1C1, используемый в конце первого оператора, относится к первой строке, первому столбцу и является формой, используемой в примерах в справке.

Как использовать макрос в Microsoft Office Excel 2007

Откройте книгу, содержащую данные.

Нажмите клавиши ALT+F11, чтобы запустить редактор Visual Basic.

В меню "Вставка" нажмите "Модуль", чтобы вставить модуль. Введите макрос в окне кода модуля.

В меню "Файл" выберите "Закрыть и вернуться в Microsoft Excel".

Выберите лист, содержащий данные, которые вы хотите объединить.

Нажмите верхнюю ячейку в правом столбце данных, которые вы хотите объединить. Например, если ячейки A1:A100 и B1:B100 содержат данные, щелкните ячейку B1.

Перейдите на вкладку "Разработчик". Если вкладка "Разработчик" не отображается, выполните следующие действия:

Нажмите кнопку Microsoft Office, а затем щелкните Параметры Excel.

Нажмите Популярные.

Нажмите, чтобы установить флажок Показать вкладку "Разработчик" на ленте.

Нажмите "ОК", чтобы закрыть диалоговое окно "Параметры Excel".

Нажмите "Макросы" в группе "Код".

Выберите макрос ConcatColumns и нажмите "Выполнить".

Как использовать макрос в Microsoft Office Excel 2003 и более ранних версиях Excel

Откройте книгу, содержащую данные.

Нажмите клавиши ALT+F11, чтобы запустить редактор Visual Basic.

В меню "Вставка" нажмите "Модуль", чтобы вставить модуль. Введите макрос в окне кода модуля.

В меню "Файл" выберите "Закрыть и вернуться в Microsoft Excel".

Выберите лист, содержащий данные, которые вы хотите объединить.

В меню "Инструменты" выберите "Макросы" и нажмите "Макрос". Выберите макрос ConcatColumns и нажмите кнопку "Выполнить".

Ссылки

Для получения дополнительных сведений о Visual Basic для приложений щелкните следующий номер статьи базы знаний Майкрософт:

226118 Список ресурсов, доступных для изучения Visual Basic для программирования приложений

В Excel конкатенация — это процесс объединения двух строк в одну строку. Проще говоря, если у нас есть таблица с именами в одном столбце и фамилиями в другом, мы можем использовать процедуру конкатенации, чтобы конкатенировать и объединить их в одной ячейке за доли секунды. В Excel у нас есть функция CONCATENATE(), которая позволяет нам выполнять эту конкатенацию. Однако в VBA этот тип функций не разрешен. Мы не можем использовать CONCATENATE() в коде VBA, так как это не сработает. Потому что в VBA нет встроенных функций, и мы не можем использовать функции электронных таблиц. Итак, этот урок продемонстрирует вам, как использовать конкатенацию VBA для объединения нескольких ячеек, столбцов и строк в Excel.

Скачать рабочую тетрадь

Загрузите это учебное пособие, чтобы тренироваться, пока читаете эту статью.

Введение в функцию конкатенации VBA

Как мы уже упоминали, в Excel нет встроенной функции для конкатенации VBA, но мы можем заставить ее работать как функцию, комбинируя разные строки с операторами. Здесь мы используем амперсанд (&) в качестве нашего оператора.

⟴ Синтаксис

String1 = «Первый текст»

String2 = «Второй текст»

⟴ Возвращаемое значение

Return_value = String1 & String2

4 различных варианта использования функции конкатенации VBA в Excel

Здесь мы будем использовать 4 разных подхода для выполнения процесса объединения. Для этого мы будем применять различные операторы в сочетании с кодом VBA.

1.Используйте оператор амперсанд (&) для объединения ячеек в конкатенации VBA

Как показано на снимке экрана ниже, у нас есть коллекция данных из двух столбцов с именами в одном столбце и фамилиями в другом. Объединив два столбца, мы теперь можем получить целые имена. Поскольку в VBA нет встроенных методов объединения, мы будем использовать оператор амперсанда (&), как описано в инструкциях ниже.

Шаг 1:

Шаг 2:

Чтобы объединить две ячейки в одну, скопируйте и вставьте следующий код VBA.

String1 = Cells(5, 2). Значение – это первая ячейка B5, строка 5 и столбец 2.
String2 = Cells(5, 3). Значение – это вторая ячейка C5, строка 5 и столбец 3.
Cells(5, 5).Value = String1 & String2 – это расположение ячейки результата E5, строка 5 и столбец 5.
String1 и String2 – две строки, соединенные амперсандом (&)

Шаг 3:

Сохраните и нажмите F5, чтобы запустить программу.

Поэтому вы получите результат в ячейке E5 вашего текущего рабочего листа.

Шаг 4:

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

Примечание. Чтобы запустить код VBA, каждый раз убедитесь, что файл Excel сохранен в формате рабочего листа Excel с поддержкой макросов (xlsm.).

2. Используйте оператор Plus (+) для объединения ячеек в конкатенации VBA

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

Шаг 1:

Чтобы открыть макрос в Excel, нажмите клавиши ALT + F11.
Нажмите "Вставить" и выберите
Открыв страницу программы, вставьте следующий код VBA

Cells(5, 5).Value = String1 + String2 — это строка, в которой мы используем знак плюс (+) вместо амперсанда (&)

Шаг 2:

После вставки сохраните и нажмите F5, чтобы запустить программу. Следовательно, вы увидите изменение в ячейке E5.

Чтобы получить окончательные результаты, заполните необходимые ячейки, выполнив предыдущие шаги еще раз.

3. Добавить несколько столбцов с помощью конкатенации VBA

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

Шаг 1:

With Worksheets("Sheet3") – это имя вашего текущего рабочего листа.
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row — имя первого столбца.
With .Range("E5:E" & LastRow) — это диапазон возвращаемых ячеек результата.
.Formula = «=B5&C5» — это формула для соединения первой ячейки диапазона.

Шаг 2:

Затем, наконец, сохраните и нажмите F5, чтобы запустить программу.

В результате вы получите результаты целиком в столбце.

4. Соединение нескольких строк с помощью конкатенации VBA

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

Шаг 1:

Set SourceRange = Range("B5:D5") – это диапазон исходных ячеек.
Range("B8").Value = Trim(i) – возвращаемый номер ячейки.

Шаг 2:

Наконец, сохраните программу и нажмите F5 для запуска.

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

Заключение

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

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

Команда Exceldemy будет постоянно отвечать на ваши вопросы.

В Excel есть два способа объединить содержимое нескольких ячеек:

Функция Excel CONCATENATE (или оператор амперсанда (&))
Функция Excel TEXTJOIN (новая функция в Excel, если у вас есть Office 365)

Если вы используете Excel с подпиской на Office 365, я предлагаю вам нажать здесь, чтобы перейти к той части, где рассматривается функция TEXTJOIN. Если вы не используете Office 365, продолжайте читать.

В своей базовой форме функция СЦЕПИТЬ может соединять 2 или более символов строк.

=CONCATENATE("Доброе","Утро") даст вам результат "Доброе утро".
=CONCATENATE("Доброе", ", "Утро") даст вам результат "Доброе утро".
=CONCATENATE(A1&A2) даст вам результат как "Доброе утро" (где A1 содержит текст "Хорошо", а A2 - текст "Утро".

Несмотря на то, что вы можете вводить ссылки по одной с помощью функции СЦЕПИТЬ, это не сработает, если вы введете ссылку сразу на несколько ячеек (как показано ниже):

Например, в приведенном выше примере используется формула =СЦЕПИТЬ(A1:A5), в результате отображается только «Сегодня» и не объединяются все ячейки.

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

Примечание. Если вы используете Excel 2016, вы можете использовать функцию ОБЪЕДИНЕНИЕ СОЕДИНЕНИЕМ, созданную для объединения нескольких ячеек с помощью разделителя.

ОБЪЕДИНИТЬ диапазон Excel (без разделителя)

Вот шаги для объединения диапазона Excel без разделителя (как показано на рисунке):

Выберите ячейку, в которой вам нужен результат.
Перейдите к строке формул и введите =ТРАНСП(A1:A5)
- Исходя из региональных настроек, вы также можете попробовать =A1:A5 (вместо =ТРАНСПОНИРОВАТЬ(A1:A5))
Выделите всю формулу и нажмите F9 (это преобразует формулу в значения).
Удалите фигурные скобки с обоих концов.
Добавьте =CONCATENATE( в начало текста и завершите его круглой скобкой).
Нажмите Enter.

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

ОБЪЕДИНИТЬ диапазоны Excel (с разделителем)

Вот шаги для объединения диапазона Excel с пробелом в качестве разделителя (как показано на рисунке):

Выберите ячейку, в которой вам нужен результат.
Перейдите к строке формул и введите =ТРАНСП(A1:A5)&" "
- Исходя из региональных настроек, вы также можете попробовать =A1:A5 (вместо =ТРАНСПОНИРОВАТЬ(A1:A5)).
Выделите всю формулу и нажмите F9 (это преобразует формулу в значения).
Удалите фигурные скобки с обоих концов.
Добавьте =CONCATENATE( в начало текста и завершите его круглой скобкой).
Нажмите клавишу ВВОД.

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

ОБЪЕДИНЕНИЕ диапазонов Excel (с помощью VBA)

Ниже приведен пример пользовательской функции, которую я создал с помощью VBA (я назвал ее CONCATENATEMULTIPLE), которая позволит вам объединить несколько ячеек, а также указать разделитель/разделитель.

Вот код VBA, который создаст эту пользовательскую функцию для объединения нескольких ячеек:

Вот шаги, чтобы скопировать этот код в Excel:

Теперь вы можете использовать эту функцию как любую обычную функцию рабочего листа в Excel.

СЦЕПИТЬ диапазоны Excel с помощью функции TEXTJOIN (доступно в Excel с подпиской на Office 365)

В Excel, входящем в состав Office 365, появилась новая функция — ТЕКСТОВОЕ СОЕДИНЕНИЕ.

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

Вот синтаксис функции:

TEXTJOIN(разделитель, ignore_empty, text1, [text2], …)

разделитель — здесь вы можете указать разделитель (разделитель текста). Вы можете ввести его вручную или использовать ссылку на ячейку с разделителем.
ignore_empty – если значение TRUE, пустые ячейки будут игнорироваться.
текст1 — это текст, который необходимо соединить. Это может быть текстовая строка или массив строк, например диапазон ячеек.
[text2] – это необязательный аргумент, в котором можно указать до 252 аргументов, которые могут быть текстовыми строками или диапазонами ячеек.

Вот пример работы функции TEXTJOIN:

В приведенном выше примере в качестве разделителя указан пробел, который объединяет текстовые строки в формате A1:A5.

Подробнее о функции TEXTJOIN можно прочитать здесь.

Сталкивались ли вы с ситуациями, когда это может быть полезно? Я хотел бы учиться у вас. Оставляйте свои следы в комментариях!

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

БЕСПЛАТНАЯ КНИГА EXCEL

Получите электронную книгу «51 совет по Excel», чтобы резко повысить свою продуктивность и быстрее выполнять работу

54 мысли о «СЦЕПИТЬ диапазон Excel (с разделителем и без него)»

Большое спасибо брат!! из Филиппин

текстовое соединение — это круто

Textjoin = Больше никаких мучений с конкатенацией для многих столбцовых объединений. Спасибо!

Вау, это очень полезно, сэкономило мне МНОГО времени! Спасибо, чувак.

Ваш макрос только что спас мне жизнь! Спасибо

Привет!
Может ли кто-нибудь помочь мне с этой формулой или похожей, пожалуйста?

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

имя1 Имя2 Имя3
строка 0 1 2

=1 Имя2, 2 Имя3

Возможно ли это?
ТНК

Если у вас есть значения
Name1 Name2 Name3
0 1 2
в диапазоне A1:C2, вы можете вставить формулу
=IF(A2=0, ””,TEXTJOIN(” “,,A2,A1))
в ячейку A3 и заполните ее на две ячейки справа.
Затем вы получите
(Пустая ячейка), 1 Имя2, 2 Имя3
в ячейках A3:C3.

Большое спасибо. Это сработало отлично.У меня есть тысячи строк с разделителями «|», и эта функция отлично справилась. Благослови вас Бог.

Спасибо, очень полезно! Одно из предложений – убрать количество символов в разделителе (подходит для разделителей с несколькими символами, например, запятая с пробелом, например ", ").

CONCATENATEMULTIPLE = Left(Результат, Len(Результат) – Len(Разделитель))

Это более универсальное решение проблемы с разделителем пустых строк ("") в Rowan. Молодец и спасибо, что поделились.

Обратите внимание, что по какой-то причине в Excel MVBA 7.1 я не смог применить этот код напрямую. Мне пришлось применить простую математику из второго параметра в Len(), чтобы редактор VB принял код. То есть:

resLen = Len(Результат)
sepLen = Len(Разделитель)
tmpLen = resLen – sepLen

CONCATENATEMULTIPLE = Left(Result, tmpLen)

Большое спасибо за код и объяснение. Отличная функция 🙂

Здравствуйте, ищу код VBA для объединения всей строки, например (A1:A25 в A26). как это сделать проще всего...!!

Большое спасибо за множественный вариант – очень признателен

Concatenatemultiple — это фантастика! Единственное ограничение, которое я заметил, заключается в том, что если у вас нет разделителя (используя «»), то он обрезает последнее значение текста. Так с небольшой функцией if было исправлено:
If Separator = "" Then
CONCATENATEMULTIPLE = Left(Result, Len(Result))
Else
CONCATENATEMULTIPLE = Left(Result, Лен(Результат) – 1)
Конец, если

Спасибо за исправление. Я столкнулся с этим.

Код CONCATENATEMULTIPLE работает хорошо. Как насчет того, когда количество ячеек для конкатенации является переменным? Как будет выглядеть этот код?

Кто-нибудь знает способ сделать следующее:

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

Название проекта Результат
Проект1 Майк Проект1, Майк, Нил, Питер
Проект1 Нил
Проект1 Питер
Проект2 Майк Проект2, Майк, Нил, Питер
Проект2 Нил
Проект2 Питер

Спасибо, хорошая маленькая функция. Также адаптировано для использования значения «Len(Separator)»:

Функция CONCATENATEMULTIPLE(ссылка как диапазон, разделитель как строка) как строка

Затемнить ячейку как диапазон
Затемнить результат как строку

Для каждой ячейки в ссылке
Результат = Результат и Cell.Value и Разделитель
Следующая ячейка

CONCATENATEMULTIPLE = Left(Результат, Len(Результат) – Len(Разделитель))

Возможно, вы могли бы реализовать функцию TEXTJOIN из таблицы Google. Вот моя реализация:

Функция TEXTJOIN(разделитель как строка, skipEmpty как логическое значение, ссылка как диапазон) как строка
Dim i как целое число
Dim tmp как строка
для каждой ячейки в ссылке
If (Cell.Value "") Then
tmp = tmp & Cell.Value & separator
End If
Следующая ячейка
TEXTJOIN = Left(tmp, Len(tmp) – Len( разделитель))
Конец функции

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

Кто-нибудь знает способ сделать следующее:

СТОЛБЦ A содержит:
A
B
C

СТОЛБЦ B содержит:
10,
20,
30,
40

Что я хочу вывести в другой КОЛОННЕ:
A10
A20
A30
A40
B10
B20
B30
B40
C10
C20
C30
C40

Кто-нибудь знает, как это сделать?

Я хочу сделать то же самое. Вы поняли, как это сделать?

Одно из решений без VBA представлено выше.

Другое решение без VBA:
Введите формулу =СЧЁТЕСЛИ(A:A”?*”) в ячейку C1 (подсчитывает количество ячеек с текстом в столбце A)
Введите формулу =СЧЁТЕСЛИ( B:B”,>0″) в ячейку C2 (подсчитывает количество ячеек с числами >=0 в столбце B)
Введите формулу =ДВССЫЛ(АДРЕС(ЧАСТНОЕ(СТРОКА()-1,$C$2)+ 1,1)) в ячейку D1 и заполнять до тех пор, пока она не начнет выдавать нули.
Введите формулу =D1&ДВССЫЛ(АДРЕС(СЧЁТЕСЛИ(D$1:D1,D1),2)) в ячейку E1 и заполняйте до тех пор, пока в столбце D не появятся значения. Ваше решение должно находиться в столбце E.
PS: Формула в ячейке C1 дана как раз для составления пар с текстовыми данными.

Третье решение без VBA (в одну строку) можно выполнить с помощью формулы =ДВССЫЛ(АДРЕС(ЧАСТНОЕ(СТРОКА()-1,СЧЁТЕСЛИ(B:B">0″))+1,1))&ДВССЫЛ( ЕСЛИ(ОСТАТ(СТРОКА(),СЧЁТЕСЛИ(B:B">0″))0,АДРЕС(ОСТАТ(СТРОКА(),СЧЁТЕСЛИ(B:B">0″)),2),АДРЕС(СЧЁТЕСЛИ( B:B”,>0″),2))) в ячейку C1 и заполнять ее до тех пор, пока она не начнет давать лишние решения. Если вы объединяете текст в столбце B, замените все СЧЁТЕСЛИ(B:B">0″) в формуле на СЧЁТЕСЛИ(B:B">0") .

* Исправление:
Если вы объединяете текст в столбце B, замените все СЧЁТЕСЛИ(B:B,">0″) в формуле на СЧЁТЕСЛИ(B:B,"?*") .< /p>

Скопируйте значения из столбца A в столбец C, начиная с C2:C4.Скопируйте значения из столбца B в столбец D, начиная с D1, но с помощью Paste_Special>>Transpose, чтобы все выглядело как пустая таблица с буквами для строк и цифрами для столбцов. Теперь выберите ячейку D2 и введите формулу =$C2&D$1 (на этих местах должны стоять знаки доллара). Теперь заполните формулу до конца строк и столбцов. Теперь выберите все 12 значений и скопируйте их, не перемещая выделение, затем вставьте >> вставить_значения. Теперь вам нужно поместить значения в один столбец. Откройте новый лист, скопируйте 12 значений, выберите B1 и Paste_Special>>Transpose. Теперь вставьте формулу = ЕСЛИ (СТРОКА () * 1/4 = ЦЕЛОЕ (СТРОКА () * 1/4), СТРОКА () * 1/4, ЦЕЛОЕ (СТРОКА () * 1/4) + 1) в ячейку A1. Наиболее важным является число 4, потому что оно связано с количеством строк, заполненных значениями. *** В случае 50 строк с данными формула будет =ЕСЛИ(СТРОКА()*1/50=ЦЕЛОЕ(СТРОКА()*1/50),СТРОКА()*1/50,ЦЕЛОЕ(СТРОКА()* 1/50)+1).*** Заполните формулу (четверками) 12 раз (потому что у вас 12 значений). Теперь вставьте формулу =ДВССЫЛ(АДРЕС(СТРОКА()-(A5-1)*4,СТОЛБЦ()+(A5-1),4)) в ячейку B5 и заполните до B12. Наиболее важным в этой формуле является то, что ПЕРВЫЙ номер 4 играет ту же роль, что и в предыдущем, а A5 присутствует, потому что мы помещаем формулу в B5. *** В случае 50 строк с формулой данных будет = ДВССЫЛ (АДРЕС (СТРОКА()) -(A51-1)*50,COLUMN()+(A51-1),4)) *** Число 4 в конце формулы является относительным адресом и не связано с вашим количеством строк.*** Столбец B — одно из решений без использования VBA.

Очевидно, что TEXTJOIN связан с Office 365, а НЕ с Excel 2016.

TEXTJOIN() НЕдоступна в моей настольной версии Excel 2016.

Привет, Корнан.. Ты прав! Я соответствующим образом отредактировал учебник.

Спасибо за это, идеальная замена формулы MCONCAT из ныне несуществующей (для всех пользователей 64-битной ОС) надстройки morefunc

Круто! Я искал для автообновления concatenate. Спасибо за код VBA.

Это здорово – спасибо!

Предлагаю внести одно изменение в код VBA: вместо использования
СЦЕПИТЬНЕСКОЛЬКО = Left(Результат, Длина(Результат) – 1) вы можете использовать
СЦЕПИТЬНЕСКОЛЬКО = Лево(Результат, Длина(Результат) – Лен(Разделитель)); это позволит использовать многосимвольные разделители.

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

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

Отлично! Это сделало именно то, что мне нужно, в сочетании с динамическими диапазонами.

В коде есть ошибка. ExcelConcatenate не равен CONCATENATEMULTIPLE, вы должны установить CONCATENATEMULTIPLE =

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

Очень-очень экономит время и интересно, брат, хорошие советы

Спасибо за комментарий.. Рад, что вам понравилось 🙂

Вы можете добавить образец документа?

Это действительно классное решение. Сэкономит много времени. Какой более продвинутый метод автоматически удаляет фигурные скобки?

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

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

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

Уровень квалификации: средний

Объединение: хорошее и плохое

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

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

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

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

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

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

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

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

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

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

Выберите ячейку, в которую вы хотите ввести формулу, и запустите макрос.
Появится поле ввода, в котором вам будет предложено выбрать ячейки, которые вы хотите объединить. Вы можете выделить диапазон ячеек с помощью мыши или клавиатуры.
Нажмите "ОК".
Макрос разбивает диапазон на ссылки на отдельные ячейки, поэтому эта ссылка на диапазон (A2:C2) превращается в (A2,B2,C2).
Затем в активной ячейке создается формула объединения или амперсанда.

Параметры макроса конкатенации

Тип формулы. Макрос объединения позволяет создать формулу объединения или амперсанда.
Символ-разделитель. Вы также можете добавить символ-разделитель между ячейками. Это удобно, если вы хотите добавить запятые, пробелы, тире или любой символ между объединенными ячейками.
Абсолютные ссылки. Макрос также позволяет сделать ссылки на ячейки абсолютными (привязанными). Это добавит знак $ перед буквой столбца или номером строки. Это удобно, если вы копируете формулу в определенном направлении и не хотите, чтобы относительные ссылки на ячейки изменялись.

Функция СЦЕПИТЬ или формулы амперсанда

Ячейки также можно объединять с помощью символа амперсанда (&). Это альтернатива использованию функции СЦЕПИТЬ.

Следующие две формулы дадут один и тот же результат.

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

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

Код VBA

Вы можете скачать книгу, содержащую приведенный ниже код.

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

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

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

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

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

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

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

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

Объединение нескольких ячеек с помощью транспонирования. У Chandoo есть интересный подход к решению этой проблемы с использованием функции транспонирования.

Заключение

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

Читайте также: