Excel vba объединение строк

Обновлено: 07.07.2024

Часто бывает ситуация, когда необходимо из трех разных столбцов сцепить данные в одну строку с разделителем. Допустим в А1 Фамилия, в В1 - Имя, в С1 - Отчество, а надо получить все вместе Фамилия Имя Отчество. Как обычно в Excel объединяют значения нескольких ячеек в одну? Правильно, при помощи функции СЦЕПИТЬ или при помощи амперсанда:
=СЦЕПИТЬ( A1 ;" "; B1 ;" "; C1 ;" ")
= A1 &" "& B1 &" "& C1 &" "
Это достаточно эффективно, если необходимо сцепить значения из трех-пяти ячеек. А если ячеек 50? Или того больше? Не очень удобно объединять их все описанными выше способами. А других встроенных функций в Excel для подобных операций не существует. С момента написания статьи Microsoft порадовал нас новыми функциями и теперь в составе функций есть функция ОБЪЕДИНИТЬ (TEXTJOIN) , которая способна решить задачу без лишних телодвижений.
=ОБЪЕДИНИТЬ(", ";ИСТИНА; A2:A100 )
=TEXTJOIN(", ",TRUE,A2:A100)

Правда и здесь не все так радужно: эта функция доступна только пользователям версий 2019 и выше, а так же офиса 365 .

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

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(Insert -Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Shift + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .

Синтаксис функции:
=СцепитьМного(A2:A100;", ";ИСТИНА)

Диапазон - диапазон ячеек, значения которых необходимо объединить в строку.
Разделитель - необязательный аргумент. Один или несколько символов, которые будут вставлены между каждым словом. По умолчанию пробел.
БезПовторов - необязательный аргумент. Если указан как ИСТИНА или 1 - в результирующей строке будут значения без дубликатов. Например, из значений Сидоров, Петров, Сидоров, Иванов в результат попадут только Сидоров, Петров, Иванов. Если ЛОЖЬ или 0 - будут выведены все значения. Для английской локализации данный параметр указывается как TRUE и FALSE соответственно.

СцепитьМного.xls (52,5 KiB, 10 808 скачиваний)

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

И еще одна реализация - в ней допускается указывать не только отдельные диапазоны, но и вообще все что угодно(ячейки, отдельный текст, числа и т.п.). Единственная проблема - в этой функции иначе организован порядок аргументов: сначала указывается разделитель, а уже потом значения для сцепления. Более подробно эта функция рассмотрена в статье Что такое функция пользователя(UDF)?. Так же эта функция не убирает дубли, что впрочем, не так сложно добавить, ориентируясь на функции выше.

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

Сначала мы увидим, как мы будем работать с функцией листа, затем мы увидим то же самое в VBA. Рассмотрим две строки в Excel, как показано на скриншоте ниже.

Теперь используйте функцию объединения, чтобы объединить обе строки.

Соблюдайте формулу, D4 и E4 - это адрес ячеек, которые мы хотим объединить. Как и выше, мы можем объединить несколько строк из разных ячеек.

Как использовать функцию конкатенации Excel VBA?

Мы научимся использовать VBA Concatenate с несколькими примерами в Excel.

Вы можете скачать этот шаблон Excel для конкатенации VBA здесь - Шаблон Excel для конкатенации VBA

Конкатенация VBA - Пример № 1

Поскольку у нас нет никаких встроенных функций в VBA, объединение в VBA может быть достигнуто с помощью оператора амперсанда (&).

Мы возьмем тот же пример, который мы уже использовали для функции рабочего листа. У нас есть «Я люблю» в ячейке D4 и «Индия» в ячейке E4. Теперь мы объединим эти две строки в VBA. Перейдите на вкладку редактора VBA.

Шаг 1: Перейдите на вкладку «Разработчик» и затем выберите вкладку «Visual Basic» с левой стороны. Затем он перейдет на экран ниже.

Шаг 2: Во-первых, нам нужно создать подпроцесс с любым именем, например, конкатенацией . Для запуска подпроцесса используйте ключевое слово Sub и имя процесса «concatenation».

Код:

Шаг 3: В подпроцессе нам нужно определить строки, такие как string1, string2 и full_string, используя ключевое слово dim.

Код:

Шаг 4: Теперь нам нужно присвоить строку «I love» для string1 и «India» для string2, используя оператор присваивания «=», как показано ниже.

Код:

Шаг 8: В приведенном выше примере мы взяли две строки непосредственно в программе, и между первой строкой и второй строкой нет пробела. Как добавить пространство тогда? Очень просто при объединении, объединить пространство также.

Код:

Шаг 10: Результат будет таким, как показано ниже.

Конкатенация VBA - Пример №2

Шаг 1: Теперь мы возьмем данные из таблицы Excel и объединим, а затем отобразим результаты. Для этого присвойте данные в ячейках string1 и string2, как показано ниже.

Код:

Шаг 2: Посмотрите на изображение выше, мы присвоили значение в ячейках (4, 4) stirng1 и ячейках (4, 5) в string2. Как обычно, объедините обе строки с помощью оператора амперсанда.

Код:

Шаг 3: Из-за этого все данные в ячейках (4, 4) и ячейках (4, 5) будут объединены и сохранены в full_string. Теперь выполните процесс, нажав на символ воспроизведения.

Шаг 4: Данные взяты из Excel, показанного ниже.

Шаг 5: Если мы изменим данные в Excel и перезапустим, результаты программы изменятся согласно данным Excel. Вместо Индии я перешел на сладости, теперь мы побежим и проверим.

Код:

Шаг 8: Поскольку мы добавляем две строки, мы можем использовать символ «+» также, как показано ниже.

Код:

Шаг 9: Результат будет таким, как показано ниже. Там не будет никакой разницы.

Шаг 10: Но если мы будем использовать числа вместо строк для конкатенации с использованием символа «+», он будет выполнять конкатенацию только без сложения, поскольку мы взяли тип данных как строку, а не целое число.

Шаг 11: Результат будет таким, как показано ниже.

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

Конкатенация VBA - Пример № 3

Шаг 1: Если мы хотим объединить строку и число, мы также можем применить ту же технику. Для этого сохраните числовое значение в одной ячейке и строковое значение в другой ячейке и нажмите на опцию запуска.

Шаг 2: Результат не окажет влияния, это будет тот же строковый результат, что и ранее, с единственным изменением - число вместо строки.

То, что нужно запомнить

Различия между двумя операторами объединения Differences Between the Two Concatenation Operators

Оператор + имеет основную цель сложения двух чисел. The + Operator has the primary purpose of adding two numbers. Однако он также может объединять числовые операнды со строковыми. However, it can also concatenate numeric operands with string operands. Оператор + имеет сложный набор правил, определяющий, следует ли выполнять добавление, объединение, сигнализировать об ошибке компилятора или выдавать исключение времени выполнения InvalidCastException. The + operator has a complex set of rules that determine whether to add, concatenate, signal a compiler error, or throw a run-time InvalidCastException exception.

Операторы сравнения

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

Результатом любой операции сравнения является значение типа Boolean: True, False.

Если оба операнда в выражении сравнения имеют один и тот же тип данных, VBA выполняет простое сравнение для этого типа.

Если один или оба операнда в выражении сравнения являются переменными типа Variant, VBA пытается преобразовать тип Variant в какой-либо совместимый тип.

Сравнение строк

При сравнении строк операторами отношения, VBA сравнивает каждую строку слева направо посимвольно.

Следует быть внимательным при сравнении строк переменной длины.

Двоичное и текстовое сравнение строк

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

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

Для выбора метода сравнения строк используется директива Option Compare

Option Compare [Text | Binary]

Данная директива должна находиться в области объявления модуля.

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

Присоединение одной строки к другой называется конкатенацией строк.

Если операнд в выражении конкатенации строк имеет значение Empty или Null, VBA интерпретирует этот операнд как строку нулевой длины (строка не содержащая символов).

Оператор сложения в конкатенации строк

Для конкатенации строк можно также использовать оператор (+).

Приоритеты выполнения операций

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

Для данных типа String существует только одна операция - конкатенация (объединение). Например, результатом операции конкатенации трех строковых значений «Петр» & " " & «Иванович» будет строка «Петр Иванович». Возможно также использование другого оператора для операции конкатенации, например: «десяти» + «тысячник». Разница между этими выражениями состоит в том, что в первом случае операндами могут быть значения любого типа (они просто будут преобразовываться в строковые), а во втором - оба операнда должны иметь тип String.

Для работы со строками существует большое количество функций (таблица. Функции работы со строками).

Таблица «Функции работы со строками»

Функция	Описание	Пример
Len(str)	Определяет длину строки	Из а=lеn("Персонажи") следует а=9
Left (<строка>, <длина>)	Выделяет из аргумента <строка> указанное количество символов слева	Left(" 1234string", 4) ="1234"
Right(<строка>, <длина>)	Выделяет из аргумента <строка> указанное количество символов справа	Right(" 1234string", 6) ="string"
Mid(<строка>, <старт> [, <длина>])	Выделяет из аргумента <строка> подстроку с указанным числом символов, начиная с позиции <старт>	Mid ("12345678", 4, 3) ="456"
Mid(<строка>, <старт>)	Выделяется подстрока от позиции <старт> до конца строки	Mid ("12345678", 4) ="45678"
LTrim (<строка>)	Удаляет пробелы в начале строки	LTrim(" печать") ="печать"
RTrim (<строка>)	Удаляет пробелы в конце строки	RTrim("печать ") ="печать"
Trim (<строка>)	Удаляет пробелы в начале и в конце строки	Trim(" печать ") ="печать"
InStr([<старт>, ] < строка 1>, <строка 2> [, <сравнение>])	Производит поиск подстроки в строке. Возвращает позицию первого вхождения строки <строка 2> в строку <строка 1>, <старт> - позиция, с которой начинается поиск. Если этот аргумент пропущен, поиск начинается с начала строки	Instr("C:Temp test.mdb", "Test")=9 Если искомая строка не находится в указанной строке, функция возвращает 0
InStrRev ([<старт>, ] <строка 1>, <строка 2> [, <сравнение>])	Ищет подстроку в строке, но начинает поиск с конца строки и возвращает позицию последнего вхождения подстроки. Необязательный аргумент <сравнение> определяет тип сравнения двух строк
Replace (<строка>, <строка Поиск>, <строка Замена>)	Позволяет заменить в строке одну подстроку другой. Эта функция ищет все вхождения аргумента <строка Поиск> в аргументе <строка> и заменяет их на <строка Замена>

Для сравнения строковых значений можно использовать обычные операторы сравнения числовых значений, так как при сравнении символов сравниваются их двоичные коды.

Следующие три функции позволяют работать с массивом строк

Split (<строка> [, <разделитель>]) - преобразует строку в массив подстрок. По умолчанию в качестве разделителя используется пробел. Данную функцию удобно использовать для разбиения предложения на слова. Однако можно указать в этой функции любой другой разделитель. Например, Split(3, «Это тестовое предложение») возвращает массив из трех строковых значений: «Это», «тестовое», «предложение».
Join (<массив Строк> [, <разделитель>]) - преобразует массив строк в одну строку с указанным разделителем.
Filter (<массив Строк>, <строка Поиск>[, <включение>] [, <сравнение>]) - просматривает массив строковых значений и ищет в нем все подстроки, совпадающие с заданной строкой.

Эта функция имеет четыре аргумента:

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

Еще три функции обеспечивают преобразование строк:

LCase (<строка>) - преобразует все символы строки к нижнему регистру, например функция LCase(«ПОЧTA») возвращает строку «почта»;
UCase (<строка>) - преобразует все символы строки к верхнему регистру;
StrConv (<строка>, <преобразование>) - выполняет несколько типов преобразований строки в зависимости от второго параметра. Этот параметр описывается встроенными константами, например функция StrConv(«poccия», VbProperCase) возвращает значение «Россия».

И последние две функции генерируют строки символов

Space (<число>) - создает строку, состоящую из указанного числа пробелов;
String (<число>, <символ>) - создает строку, состоящую из указанного в первом аргументе числа символов. Сам символ указывается во втором аргументе.

Пример

1 метка: сообщается длина строки, введенной в первое текстовое поле (1 строка);

2 метка: преобразует все символы третьего текстового поля (3 строка) в заглавные буквы;

3 метка: выводит вместе содержание первого и второго текстовых полей (1 и 2 строки).

Хороший фреймворк (framework) лучшее решение для безпроблемной работы со строками.

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

Excel vba объединение строк

Как использовать функцию конкатенации Excel VBA?

Конкатенация VBA - Пример № 1

Конкатенация VBA - Пример №2

Конкатенация VBA - Пример № 3

То, что нужно запомнить

Рекомендуемые статьи

Различия между двумя операторами объединения Differences Between the Two Concatenation Operators

Операторы сравнения

Сравнение строк

Двоичное и текстовое сравнение строк

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

Оператор сложения в конкатенации строк

Приоритеты выполнения операций

Таблица «Функции работы со строками»

Следующие три функции позволяют работать с массивом строк

И последние две функции генерируют строки символов

Пример