Склеивание текста по условию
Про то, как можно быстро склеивать текст из нескольких ячеек в одну и, наоборот, разбирать длинную текстовую строку на составляющие я уже писал. Теперь же давайте рассмотрим близкую, но чуть более сложную задачу — как склеивать текст из нескольких ячеек при выполнении определенного заданного условия.
Допустим, что у нас имеется база данных по клиентам, где одному названию компании может соответствовать несколько разных email’ов ее сотрудников. Наша задача состоит в том, чтобы собрать все адреса по названиям компаний и сцепить их (через запятую или точку с запятой), чтобы сделать потом, например, почтовую рассылку по клиентам, т.е. получить на выходе что-то похожее на:
Другими словами, нам нужен инструмент, который будет склеивать (сцеплять) текст по условию — аналог функции СУММЕСЛИ (SUMIF), но для текста.
Способ 0. Формулой
Не очень изящный, зато самый простой способ. Можно написать несложную формулу, которая будет проверять отличается ли компания в очередной строке от предыдущей. Если не отличается, то приклеиваем через запятую очередной адрес. Если отличается, то «сбрасываем» накопленное, начиная заново:
Минусы такого подхода очевидны: из всех ячеек полученного дополнительного столбца нам нужны только последние по каждой компании (желтые). Если список большой, то чтобы их быстро отобрать придется добавить еще один столбец, использующий функцию ДЛСТР (LEN), проверяющий длину накопленных строк:
Теперь можно отфильтровать единички и скопировать нужные склейки адресов для дальнейшего использования.
Способ 1. Макрофункция склейки по одному условию
Если исходный список не отсортирован по компаниям, то приведенная выше простая формула не работает, но можно легко выкрутиться с помощью небольшой пользовательской функции на VBA. Откройте редактор Visual Basic нажатием на сочетание клавиш Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer). В открывшемся окне вставьте новый пустой модуль через меню Insert — Module и скопируйте туда текст нашей функции:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String)
Dim Delimeter As String, i As Long
Delimeter = ", " 'символы-разделители (можно заменить на пробел или ; и т.д.)
'если диапазоны проверки и склеивания не равны друг другу - выходим с ошибкой
If SearchRange.Count <> TextRange.Count Then
MergeIf = CVErr(xlErrRef)
Exit Function
End If
'проходим по все ячейкам, проверяем условие и собираем текст в переменную OutText
For i = 1 To SearchRange.Cells.Count
If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter
Next i
'выводим результаты без последнего разделителя
MergeIf = Left(OutText, Len(OutText) - Len(Delimeter))
End Function
Если теперь вернуться в Microsoft Excel, то в списке функций (кнопка fx в строке формул или вкладка Формулы — Вставить функцию) можно будет найти нашу функцию MergeIf в категории Определенные пользователем (User Defined). Аргументы у функции следующие:
Способ 2. Сцепить текст по неточному условию
Если заменить в 13-й строчке нашего макроса первый знак = на оператор приблизительного совпадения Like, то можно будет осуществлять склейку по неточному совпадению исходных данных с критерием отбора. Например, если название компании может быть записано в разных вариантах, то мы можем одной функцией проверить и собрать их все:
Поддерживаются стандартные спецсимволы подстановки:
- звездочка (*) — обозначает любое количество любых символов (в т.ч. и их отсутствие)
- вопросительный знак (?) — обозначает один любой символ
- решетка (#) — обозначает одну любую цифру (0-9)
По умолчанию оператор Like регистрочувствительный, т.е. понимает, например, «Орион» и «оРиОн» как разные компании. Чтобы не учитывать регистр можно добавить в самое начало модуля в редакторе Visual Basic строчку Option Compare Text, которая переключит Like в режим, когда он невосприимчив к регистру.
Таким образом можно составлять весьма сложные маски для проверки условий, например:
- ?1##??777RUS — выборка по всем автомобильным номерам 777 региона, начинающимся с 1
- ООО* — все компании, название которых начинается на ООО
- ##7## — все товары с пятизначным цифровым кодом, где третья цифра 7
- ????? — все названия из пяти букв и т.д.
Способ 3. Макрофункция склейки текста по двум условиям
В работе может встретиться задача, когда сцеплять текст нужно больше, чем по одному условию. Например представим, что в нашей предыдущей таблице добавился еще один столбец с городом и склеивание нужно проводить не только для заданной компании, но еще и для заданного города. В этом случае нашу функцию придется немного модернизировать, добавив к ней проверку еще одного диапазона:
Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String)
Dim Delimeter As String, i As Long
Delimeter = ", " 'символы-разделители (можно заменить на пробел или ; и т.д.)
'если диапазоны проверки и склеивания не равны друг другу - выходим с ошибкой
If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then
MergeIfs = CVErr(xlErrRef)
Exit Function
End If
'проходим по все ячейкам, проверяем все условия и собираем текст в переменную OutText
For i = 1 To SearchRange1.Cells.Count
If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then
OutText = OutText & TextRange.Cells(i) & Delimeter
End If
Next i
'выводим результаты без последнего разделителя
MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter))
End Function
Применяться она будет совершенно аналогично — только аргументов теперь нужно указывать больше:
Способ 4. Группировка и склейка в Power Query
Решить проблему можно и без программирования на VBA, если использовать бесплатную надстройку Power Query. Для Excel 2010-2013 ее можно скачать здесь, а в Excel 2016 она уже встроена по умолчанию. Последовательность действий будет следующей:
Power Query не умеет работать с обычными таблицами, поэтому первым шагом превратим нашу таблицу в «умную». Для этого ее нужно выделить и нажать сочетание Ctrl+T или выбрать на вкладке Главная — Форматировать как таблицу (Home — Format as Table). На появившейся затем вкладке Конструктор (Design) можно задать имя таблицы (я оставил стандартное Таблица1):
Теперь загрузим нашу таблицу в надстройку Power Query. Для этого на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013) жмем Из таблицы (Data — From Table):
В открывшемся окне редактора запросов выделяем щелчком по заголовку столбец Компания и сверху жмем кнопку Группировать (Group By). Вводим имя нового столбца и тип операции в группировке — Все строки (All Rows):
Жмем ОК и получаем для каждой компании мини-таблицу сгруппированных значений. Содержимое таблиц хорошо видно, если щелкать левой кнопкой мыши в белый фон ячеек (не в текст!) в получившемся столбце:
Теперь добавим еще один столбец, где с помощью функции склеим через запятую содержимое столбцов Адрес в каждой из мини-таблиц. Для этого на вкладке Добавить столбец жмем Пользовательский столбец (Add column — Custom column) и в появившемся окне вводим имя нового столбца и формулу сцепки на встроенном в Power Query языке М:
Обратите внимание, что все М-функции регистрочувствительные (в отличие от Excel). После нажатия на ОК получаем новый столбец со склееными адресами:
Осталось удалить ненужный уже столбец ТаблАдресов (правой кнопкой мыши по заголовку — Удалить столбец) и выгрузить результаты на лист, нажав на вкладке Главная — Закрыть и загрузить (Home — Close and load):
Важный нюанс: в отличие от предыдущих способов (функций), таблицы из Power Query не обновляются автоматически. Если в будущем произойдут какие-либо изменения в исходных данных, то нужно будет щелкнуть правой кнопкой в любое место таблицы результатов и выбрать команду Обновить (Refresh).
Ссылки по теме
- Как разделить длинную текстовую строку на части
- Несколько способов склеить текст из разных ячеек в одной
- Использование оператора Like для проверки текста по маске
|
Сцепить непустые ячейки |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
СЦЕПИТЬ не пустые ячейки
Автор Jim, 07.09.2009, 15:23
« назад — далее »
Всем привет!
Подскажите, как прописать формулу СЦЕПИТЬ, так чтобы он сцеплял только не пустые ячейки из столбца А.
Допустим имеется в столбце числа (в разброс по столбцу), и необходимо сцепить их через знак «;».
Просьба помочь… спасибо.
Ну, это проще всего решить пользовательской функцией.
Например так:
Public Function СцепитьБезПустышек(target As Range)
For Each cl In target.Cells
If cl.Value <> «» Then СцепитьБезПустышек = СцепитьБезПустышек & cl.Value & «;»
Next
End Function
Я не совсем понял… ну вставил я этот код… и что дальше? что нужно сделать чтобы сработало?
1. Создаете в книге модуль.
2. Вставляете в него код.
3. используете функцию =СцепитьБезПустышек() в ячейках книги, как любую другую функцию.
Цитата: Алексей Шмуйлович от 08.09.2009, 11:41
Ну, это проще всего решить пользовательской функцией.Например так:
Public Function СцепитьБезПустышек(target As Range)
For Each cl In target.Cells
If cl.Value <> «» Then СцепитьБезПустышек = СцепитьБезПустышек & cl.Value & «;»
Next
End Function
Алексей, здравствуйте!
А если с точностью до наоборот у меня задача, макрос не подскажите?
Поясню: в столбце ооочень большое количество ячеек, в ячейках наборы цифр (телефонных номеров) по 2 штуки (+7 (495) 123 45 67 +7 (910) 765 43 21) и уж очень мне хочется их разбить, да так, чтобы второй тлф мигрировал в ячейку направо, или налево, не играет роли, но важно, чтобы в сторону.. Вооот..
Пара способов (на двух листах)
Скажи мне, кудесник, любимец ба’гов…
Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995
Цитата: _Boroda_ от 13.09.2009, 04:32
Пара способов (на двух листах)
_Boroda_, Благодарю! До формул дошел, ползая по соседним топикам, но изящество встроенной функции поражает.. Даже стыдно, что не знал про нее.. Пойду учить матчасть 
- Профессиональные приемы работы в Microsoft Excel
-
►
Обмен опытом -
►
Microsoft Excel -
►
СЦЕПИТЬ не пустые ячейки
Содержание
- Формула СЦЕПИТЬДИАПАЗОН
- Описание функции
- Пример 1
- Пример 2
- Пример 3
- Пример 4
- Сцепить в excel диапазон ячеек
- Работа с функцией СЦЕПИТЬ в Microsoft Excel
- Применение оператора СЦЕПИТЬ
- Способ 1: объединение данных в ячейках
- Способ 2: применение функции с пробелом
- Способ 3: добавление пробела через окно аргументов
- Способ 4: объединение колонок
- Способ 5: добавление дополнительных символов
- Функция СЦЕПИТЬ – скотч для Excel
- Связываем имена
- Связываем числа и текст
- Объединение значений нескольких ячеек в одну текстовую строку в EXCEL
- Универсальная функция Сцепить
- Сцепить диапазон по столбцам
- 3 способа склеить текст из нескольких ячеек
- Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ
- Способ 2. Символ для склеивания текста (&)
- Способ 3. Макрос для объединения ячеек без потери текста.
- СЦЕПИТЬ в одну ячейку формулой массива
- Сцепить ячейки в несмежном (рваном) диапазоне
- Как сцепить большое количество ячеек?
Формула СЦЕПИТЬДИАПАЗОН
Описание функции
Функция =СЦЕПИТЬДИАПАЗОН(ДИАПАЗОН, [РАЗДЕЛИТЕЛЬ]) имеет два аргумента:
- — ДИАПАЗОН — диапазон ячеек, которые необходимо сцепить.
- — [РАЗДЕЛИТЕЛЬ] — символ-разделитель, который будет вставляться между значениями ячеек. Данный аргумент является не обязательным.
Аналогичная функция появилась только в Excel 2016. Если у Вас Excel ранней версии, то надстройка полностью исправляет это упущение разработчиков.
Далее привожу множество примеров использования данной функции.
Пример 1
Просто сцепить все ячейки диапазона, вставив между ними пробел.
Этот пример можно сделать также с помощью стандартной функции СЦЕПИТЬ, однако для этого придется перечислить все ячейки диапазона. Если их две-три, то это не проблема, а вот если их гораздо больше, то выбор в пользу функции СЦЕПИТЬДИАПАЗОН думаю очевиден.
Пример 2
Перечислить все фамилии через запятую.
Обратите внимание, что в качестве разделителя указана запятая с пробелом («, «). Если не указать пробел, то все ячейки «сольются».
Пример 3
Можно сцепить более одного столбца. При этом сцепление ячеек происходит построчно.
Пример 4
Используя промежуточные вычисления можно добиться нужного решения значительно проще и быстрее. Следующий пример демонстрирует как перечислить фамилии и инициалы, ставя запятую только после инициалов.
Источник
Сцепить в excel диапазон ячеек
Работа с функцией СЦЕПИТЬ в Microsoft Excel
Смотрите также String Dim ra End FunctionСцепитьМного. Update а также получить: Всем привет! ячеек, содержимое которых РазделительСтрок As String сравниваются значения ячеекЧто потом с скорости вызова простейшей версиях 20003/2007 это но задавать полностью / CurrentRegion /» «Если Вы никогдаС помощью функции этом вручную добавитьОдной из интересных функций As Range For
2017-10-19 Option Explicit универсальный вариант для
Применение оператора СЦЕПИТЬ
Подскажите, пожалуйста, есть хотим склеить = «, «, из п.5 такой ячейкой планируете функции в VBScript делается очень криво. столбцы, когда много UsedRange)– символ пробела прежде не использовалиСЦЕПИТЬ пробел не получится, приложения Microsoft Excel Each ra In ‘————————————————————————————————————————————————————————————————— ‘ Procedure большого количества столбцов. ли решение задачи:Например: _ Optional ByValи т.д. следующие делать? и VBA.
Поэтому просто добавил пустых ячеек, будет
в кавычках функции Excel, томожно быстро объединять а только через является функция диапазон.Areas avData = : СцепитьМного ‘_Boroda_
Способ 1: объединение данных в ячейках
Нужно собрать данныеЭто универсальный и компактный РазделительСтолбцов As String условия аналогично п.п.ЦитатаА сортировку иУ меня VBA пример использования, который слишком роскошно. Впрочем, для сцепки (отF16 можете обратиться к данные нескольких колонок редактирование формулы.СЦЕПИТЬ
- ra.Value If Not http://www.excel-vba.ru ‘ Purpose: Вы рассуждаете верно. по условию в способ сцепки, работающий = «; «) 5,6,7 разбивку по строкам оказалась быстрее в
виден в мастере могу добавить подрезку 3 до 0,(Product) – наименование разделу в одну.Урок:. Её основной задачей IsArray(avData) Then ReDim : Функция сцепляет Везде, кроме фразы одну ячейку при абсолютно во всех
Dim c AsВсе условия работают выполняет мой примитивный 21 раз (0.4 функций. до последней используемой т.е. просто сцепить)Введите вот такую формулуФормулы и функцииС ячейками первой строкиМастер функций в Excel
является соединение содержимого avData(1 To 1, все указанные ячейки . Формульный массив помощи формулы массива. версиях Excel. Range, v As по И-логике (And) макрос.Может, нужно было секунды) чем VBScriptВот встроенную справку строки листа.- ограничениям на в ячейкенашего самоучителя по объединяемых колонок проделываемСуществуют возможности исправить данный двух или нескольких 1 To 1) в одну с получится сцепить вместе
Спасибо!суммирования
Способ 2: применение функции с пробелом
String, s As=Concat(A:A; «,»; 1;1; (нашем) макросе все
- Option Explicit Dim было бы сделать плохо, потому что
- текст, даты): где представлена серия действия, которые указаны
между аргументами оператора. Данный оператор помогает ra.Value End If Аргументы функции: ‘ что даст ВамP.S. ЮПД несодержимого нескольких ячеек String, lr As B:B;»=»;3; C:C;»>=»;20; D:D;»=»;»text3″) это и сделать?
objScript As Object
во втором иВыполняем задачу по тому решить некоторые задачи, For lc = Диапазон — диапазон
Способ 3: добавление пробела через окно аргументов
формулу гораздо больше хочется использовать, так используют знак плюс Long, lc AsAcid BurnAcid Burn ‘ Сравнение скорости требует немало времени, текстом, а Excel разделителей, диапазонов, критериев=СЦЕПИТЬ(F17;» «;F16) тему. третьем способе применения же алгоритму, который которые с помощью 1 To UBound(avData,
- ячеек, значения которых моей (в которой, же как и « Long lr =: ZVI, в коде: Точно, что-то я выполнения простейшей функции попробуйте сделать сами тогда может произвольно и операторов сравненияУсложним задачу! Предположим, чтоПредположим, у нас есть
аргумента. Правда, если описан выше. других инструментов воплотить 2) For lr необходимо объединить в кстати, обратите внимание дополнительные ячейки. Если+ Len(РазделительСтрок) + 1: есть небольшая неточность: не подумал. Такая в VBScript и и подключить через поменять местами месяц (=, >/ По мы хотим получить: таблица с контактной вы решили воспользоватьсяДвойным щелчком левой кнопки невозможно. Например, с = 1 To строку. ‘ Разделитель на то, как без этого ни», а для lc = Len(РазделительСтолбцов)
то одну, то(У нас есть и фамилии находятся ячейкой, то ссылку с формулой активируем производить процедуру объединения Len(avData(lr, lc)) Then
‘ Один или в чем отличие задачу, то подскажитесодержимого ячеек используют Диапазон.Rows.Count = 1 0) Все, без встретится. 1000000 Dim i&,: По поводу того,nerv другую UDF. 25 яблок). Для в разных столбцах. на неё нужно её для редактирования.
Способ 4: объединение колонок
ячеек без потерь. sRes = sRes несколько символов, которые от Вашего ПОИСКа), плз любой вариант.
- знак « Then For Each сорт. + (1,Извиняюсь. t1!, t2!, ok чтобы в Excel: можно заИ в итоге этого нужно добавить Мы хотим связать будет сделать абсолютной.Между каждым аргументом записываем Рассмотрим возможности данной & разделитель & будут вставлены между или макросом._Boroda_& v In Диапазон 0) Уникал., безЦитатаМожет, нужно было As Boolean Debug.Print 2013 работало быстрее,e val сам начинаешь путаться еще один аргумент их и получить Для этого, перед выражение в виде функции и нюансы avData(lr, lc) End каждым словом. ‘Не, если хотите,: Если столбцов 5
» (расположен на большинстве If v <> сорт. Все, сорт. сразу в одном «N = « специально не старался,ить немного в них, а – фразу «We полное имя для
По умолчанию пробел. пробуйте, никто ж и без допячеек
Способ 5: добавление дополнительных символов
клавиатур на цифре «» Then s (0, 1) Все, (нашем) макросе все & N t1 просто всю обработкуПравда в этом файл — тормозить have»: каждого человека. На по горизонтали и
- двух сторон кавычками.Скачать последнюю версию Next lc Next ‘ БезПовторов — не запрещает. Я и без UDF «7»). При его = s & с сорт. Уникал., это и сделать?Если = Timer For сделал в VBA-массивах. случае вряд ли при пересчёте.=CONCATENATE(«We have «,F17,» «,F16) рисунке ниже Вы вертикали этой ячейки После внесения каждого
(Вы это имели использовании необходимо помнить, РазделительСтолбцов & v без сорт. (1, можно будет включать i = 1 Это стандартный метод будетМожет кто-нибудь сможет=СЦЕПИТЬ(«We have «;F17;» «;F16) видите имена в ставим знак доллара такого значения ставимФункция sRes = Mid(sRes,
Если указан как
Функция СЦЕПИТЬ – скотч для Excel
свое время через в виду, написав что: Next Else For 1) Уникал., с и отключать эти To N ok ускорения обработки дляЦитатаAcid Burn пишет: написать / найтиПри желании можно добавлять столбце($) точку с запятой.СЦЕПИТЬ
Len(разделитель) + 1) ИСТИНА или 1 это прошел каждый ЮПД?), то вотЭтот символ надо ставить Each c In сорт. + опции, и это = Foo1(i, «Text3») всех версий.быструю UDFбыструю UDF еще больше аргументов,B. Естественно, что лучше
Общий вид добавленныхотносится к группе End If If — в результирующей из нас. Также, так в каждой точке Диапазон.Columns t =Т.е. (1, 0) не вызовет разрастания Next t1 =Еще для операторов
Связываем имена
ЦитатаZVI пишет:для сцепки любых чтобы создать более, а фамилии в всего это сделать выражений должен быть текстовых операторов Excel. БезПовторов Then Dim строке будут значения как и через=ПСТР(ЕСЛИ(ЕЧИСЛО(ПОИСК(«, «&M$1&»,»;», «&A2&»,»));», «&A$1;»»)&ЕСЛИ(ЕЧИСЛО(ПОИСК(«, соединения, т.е. на «» For Each и (0, 1) кода и трудностей Timer — t1 заменил сравнение текста»куда правильнее»Я формулировку данных по 3 сложное выражение. Главное
столбце в самом начале, следующий: Её основная задача oDict As Object, без дубликатов. ‘ попытку засунуть виртуальный «&M$1&»,»;», «&B2&»,»));», «&B$1;»»)&ЕСЛИ(ЕЧИСЛО(ПОИСК(«, всех «стыках» текстовых v In c.Value работают с точностью в реализации. То Debug.Print «VBScript», Round(t1,
сравнением с числами, изменил, если что критериям, которые можно помните, что синтаксисA чтобы в другие» «; — объединение в sTmpStr Set oDict Для английской локализации диапазон в Проверку
- «&M$1&»,»;», «&C2&»,»));», «&C$1;»»)&ЕСЛИ(ЕЧИСЛО(ПОИСК(«, строк также, как If v <>
- до наоборот. было бы отлично 3) & «
- это тоже выполняется ) Теперь это задавать по необходимости.
формулы должен быть. Наша формула будет поля, где содержитсяДля того, чтобы вывести одной ячейке содержимого
= CreateObject(«Scripting.Dictionary») sTmpStr
данный параметр указывается
данных. «&M$1&»,»;», «&D2&»,»));», «&D$1;»»)&ЕСЛИ(ЕЧИСЛО(ПОИСК(«, вы ставите несколько «» Then tБуду благодарен, еслиnerv sec» t2 = быстрее. можно
Примерно такого вида предельно точен, иначе в ячейке этот адрес, пользователь результат на экран, нескольких ячеек, а = Split(sRes, разделитель) как TRUE иJack Famous
«&M$1&»,»;», «&E2&»,»));», «&E$1;»»);3;999) плюсов при сложении = t & подправите (сам пока: вообще-то я ничего Timer For i
ZVIZVI=СЦЕПКА(Разделитель; Диапазон сцепки; она может неE2 мог копировать его, жмем на кнопку также отдельных символов.
On Error Resume FALSE соответственно. ‘:На каждый новый нескольких чисел (2+8+6+4+8) РазделительСтрок & v не понял, как не предлагаю решать) = 1 To
Связываем числа и текст
: Александр, хотелось бы: Александр, да, тогда Optional Диап. усл._1; заработать. В большой. как содержащий постоянныеEnter Начиная с версии Next For lr Update 2017-10-19: http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=97166&TITLE_SEO=97166-stsepit-yacheyki-v-nesmezhnom-rvanom-diapazoneДоброго утра, Планетяне! столбец добавляете кусокЕсли нужно приклеить произвольный Next If t). Я только высказываю N ok = в теме автора будет медленнее, как
Оператор_1 & Условие_1; формуле легко допустить
- Прежде, чем мы начнем абсолютные ссылки. В.
- Excel 2016, вместо = LBound(sTmpStr) To ‘ Изменения: работает
- Есть замечательная UDF типа
текст (даже если <> «» Thenamix_6 свое мнение о
Foo2(i, «Text3») Next
решать проблемы автора.
плата за универсальность. Optional Диап. усл._2; ошибку! вводить формулу, поймите остальных полях оставляемКак видим, на месте данного оператора используется UBound(sTmpStr) oDict.Add sTmpStr(lr), в несмежных диапазонах.
это всего лишь s = s: Добрый день! Подскажите, том, что у t2 = TimerУ меня естьВстроенная функция СУММЕСЛИМН() Оператор_2 & Условие_2;Урок подготовлен для Вас важный момент: функция относительные ссылки. Как
вставки пробелов с функция
sTmpStr(lr) Next lr
При этом первый
«СцепитьМного». Сцепляет данные
Объединение значений нескольких ячеек в одну текстовую строку в EXCEL
«&E$1;»») точка или пробел, & РазделительСтолбцов &
пожалуйста, как сцепить одного автора одни — t2 Debug.Print правило: решая проблему использует аналогичные моему
Optional Диап. усл._3; командой сайта office-guru.ruСЦЕПИТЬ всегда, после выполнения кавычками в ячейке
СЦЕП sRes = «» аргумент(рваные диапазоны) надо в диапазоне черезЕще в файле
не говоря уж Mid(t, lr) Next диапазон ячеек по критерии отбора «VBA», Round(t2, 3)
сначала убедиться, проблема варианту простые операторы Оператор_3 & Условие_3)
Источник: http://www.gcflearnfree.org/excel-tips/concatenate-excels-duct-tape/fullбудет связывать только процедуры, жмем на появились разделения между
. Но в целях sTmpStr = oDict.Keys будет передавать обязательно разделитель. Пропускает пустые покрасия нужные ячейки о целом слове), End If СцепитьДиапазон2 столбцам (вертикально). ВЦитата=, >/ a & » sec» ли это вообще. и этого хватаетОчень надеюсь наПеревел: Антон Андронов то, что Вы
кнопку словами. сохранения обратной совместимости For lr = заключив в скобки ячейки и контролирует Условным форматированием то этот текст = Mid(s, lc) интернете нашла отличный
* 2 > Debug.Print «VBA isА автор темы для большинства случаев. Вашу помощь иАвтор: Антон Андронов укажете и ничего«OK»Конечно, если преобразуемых значений оператор LBound(sTmpStr) To UBound(sTmpStr) (добавить их вручную): дубликаты.Ячейку М1 можно надо заключать в End FunctionПравда, для вариант через vba, b — 1 faster in «
не озвучивал проблему,
Acid Burn понимание.Объединим значения из нескольких
более. Если необходимо,. не много, тоСЦЕПИТЬ sRes = sRes
Универсальная функция Сцепить
=СцепитьМного((A2;A3;A4;A5);»-«) ‘—————————————————————————————————————————————————————————————————- FunctionВсё хорошо, но, менять
кавычки. В предыдущем меня загадка, как но в нем ‘или такое a & Round(t1 / которые Вы предлагаете
: To ZVI.Заранее огромное спасибо! ячеек в одной чтобы в ячейке
Устанавливаем курсор в нижний вышеприведенный вариант разрыватоже оставлен, и & IIf(sRes <>
СцепитьМного(диапазон As Range, часто приходится сцеплятьLogotip примере с функцией
в таком случае только построчно. Excel / 2 +
t2, 0) & решать.> 1. Не понялPS: Мои поздравления ячейке. появились знаки пунктуации, правый угол элемента
склейки прекрасно подойдет. его можно использовать «», разделитель, «») Optional разделитель As «рваный» диапазон, то
: Да, именно UDF СЦЕПИТЬ о кавычках вы будете различать 2013 a — b » times» EndЧто тормозит -
вопроса.
по поводу переходаПредположим, что имеется диапазон пробелы или что-то с формулой. Появляется Но его будет наравне со & sTmpStr(lr) Next
String = « есть ячейки, идущие имел ввиду.
заботится сам Excel
массивы:Спасибо огромное!в Вашу функцию
Sub ‘ VBScript
проверьте, конечно. ЯЯ имел ввиду, на современный движок. имен, необходимо, чтобы еще, добавьте их значок, имеющий вид затруднительно быстро воплотить,
СЦЕП
lr End If «, Optional БезПовторов НЕ подряд. ТакихСпасибо большое за
— в этом
1 11иPublic Function СЦЕПИТЬДИАПАЗОН(ByRef просто так не
function Function Foo1(a, когда-то проверял. что, если заменитьСтало намного лучше!
все имена были в аргументы функции. крестика, который называется если существуют множество
. СцепитьМного = sRes As Boolean =
«зон/областей» может быть решение.
же случае их
11 1поскольку результат ДИАПАЗОН As Excel.Range,
запишешь, придется лезть b) If objScriptНо тот факт,
C1:C6;»=»;3 на C1:C6;»>»;3,ZVI выведены подряд в
В данном примере мы маркером заполнения. Зажимаем ячеек, которые нужноСинтаксис этого оператора выглядит End FunctionПри этом False) Dim avData,
много (у меня
А можно как-то надо ставить вручную. для них будет Optional ByVal РАЗДЕЛИТЕЛЬ
в код. Т.о.
Is Nothing Then что и JScript то функция выдаёт
: Приложил вариант функции одной ячейке. хотим, чтобы между
левую кнопку мыши объединить. Тем более, следующим образом:
первый аргумент(рваные диапазоны) lr As Long, до 20 бывает,
решить эту задачуВот, например, как можно
одинаковым — «1; As String = если потребуется n Set objScript =
и VBScript могут
#Cond1? Concat(), допускающей до
Когда значений немного, то
именами стоял пробел и тянем его если эти ячейки=СЦЕПИТЬ(текст1;текст2;…)
надо будет передавать
lc As Long, но, думаю, что через формулу массива? собрать ФИО в
1; 1″.Спасибо огромное! «») As String «аналогичных» замысловатых условий, CreateObject(«ScriptControl») With objScript быть отключены администратором,> 2. могу добавить 9 условий в объединить их можно (чтобы не получалось
вниз параллельно расположению не находятся вВ качестве аргументов могут обязательно заключив в sRes As String для изменённого кода_Boroda_ одну ячейку из Последний вариант для
Dim rCell As придется писать n .Language = «VBScript» уменьшают мой интерес подрезку до последней Excel 2003 и с помощью функции что-то вроде –
объединяемых элементов. едином массиве. Значительно выступать, как текст, скобки: =СцепитьМного((A2;A3;A4;A5);»-«) Dim ra As это будет неважно).: Можно. Введите мою
трех с добавлением
меня подошел идеально,
Range Dim MergeText
вариантов сравнения .AddCode «Function Fn(a,b):Fn к данному решению. используемой строки листа.
до 84 условий СЦЕПИТЬ() записав формулуJosephineCarterПосле выполнения этой процедуры
упростить расстановку пробела так и ссылкиJack Famous Range For EachВопрос:
формулу не Ентером, пробелов:
тк в моем As String For
ЦитатаZVI пишет: = a=1 And Тем более, чтоБыло бы здорово. в Excel 2007+.
=СЦЕПИТЬ(A6;A7;A8;A9)), поэтому в аргументы данные в указанных
можно, воспользовавшись вариантом на ячейки, которые: ra In диапазон.Areasчто нужно изменить
а одновременным нажатиемЕсли сочетать это с
случае не бывает Each rCell In
Все же приведу
b=»»text3″»:End Function» End есть гораздо более Если при этомПорядок аргументов функции:Или с помощью Амперсанда
нам нужно будет столбцах будут объединены его вставки через
его содержат. КоличествоThe_Prist, огромное спасибо, автору. avData = ra.Value в коде, чтобы Контрл Шифт Ентер
функцией извлечения из пустых столбцов между
ДИАПАЗОН If rCell.Text код для сравнения With End If быстрые методы, но она будет правильно1. Диапазон ячеек & (добавим еще добавить пробел. Таким
в одном столбце. окно аргументов. аргументов может варьироватьсяработает и для If Not IsArray(avData) реализовать возможность выделенияА если серьезно, текста первых букв заполненными, данные заносятся <> «» Then скорости вызова простейшей Foo1 = objScript.Run(«Fn», они на грани работать на всех (столбец или несколько для красоты символ образом, у нас
Урок:Выделяем двойным кликом левой от 1 до сплошного и для
Then ReDim avData(1 несмежных областей? то формула массива -
подряд. MergeText = MergeText функции в VBScript
a, b) End
здравого смысла, поэтому листах. смежных), которые должны пробела): =A6&» «&A7&» окажется три аргумента:Как объединить столбцы в
кнопки мыши любую
255 включительно. разрывного
To 1, 1При этом, чтобы (да и неЛЕВСИМВ (LEFT)Еще раз спасибо! & РАЗДЕЛИТЕЛЬ &
и VBA.ч и Function ‘ VBA
о них тоже> 3. Так будет попасть в результат
«&A8&» «&A9B2 Экселе пустую ячейку наКак известно, обычное объединениескажите пожалуйста, а To 1) avData(1, сохранился контроль над только, обычная иногда, то можно получить
Надпись на заборе: «Катя rCell.Text End If т.д. Ибо, как function Function Foo2(a, не распространяюсь плохо2. Разделитель результатаЕсли значений много, то
(First Name) –Функцию листе. С помощью ячеек в Эксель вот эти скобки 1) = ra.Value дубликатами и пропуск тоже) дает результатом фамилию с инициалами + Миша +
Next MergeText = мне кажется, слабым b) Foo2 =Acid BurnТогда лучше оставить,
3. Столбец №1 придется вводить вручную имяСЦЕПИТЬ
клавиатуры устанавливаем внутри приводит к потере рисовать только вручную End If For
пустых ячеек.СцепитьМного Option МАССИВ. Массив можно одной формулой: Семён + Юра
Mid(MergeText, Len(РАЗДЕЛИТЕЛЬ) + местом (в данном a = 1
: Жаль, что нельзя как есть. ячеек, участвующих в много амперсандов и» «можно также использовать неё пробел. Желательно, данных. Сохраняются только можно? Программно слишком lc = 1 Explicit ‘————————————————————————————— ‘ вывести на листИмеем текст в нескольких
+ Дмитрий Васильевич 1) СЦЕПИТЬДИАПАЗОН = случае) как раз And b = сделать полноценные подсказки
Ещё 2 вопроса: условии №1 аргументов функции СЦЕПИТЬ().
– символ пробела для добавления дополнительных чтобы она находилась данные расположенные в
запарно будет? To UBound(avData, 2)
Procedure : СцепитьМного в столько ячеек, ячейках и желание
+ MergeText End Function является передача параметров
«text3» End Function при вводе UDF- можно ли4. Оператор условия Это неудобно. в кавычках символов и выражений, подальше от основного верхнем левом элементе.The_Prist For lr =
‘ http://www.excel-vba.ru ‘
сколько в этом — объединить этитоварищ Никитин +amix_6 и частый вызов ‘ в ячейку. дописать кусок кода, №1, может бытьИзбежать этого поможет дополнительныйA2
которых не было массива. Очень важно,
Для того, чтобы: Если речь про 1 To UBound(avData, Purpose : Функция массиве значений (по
ячейки в одну, рыжий сантехник +: функции. .ЦитатаZVI пишет:
Acid BurnНо это не который будет выдавать
одним из: «=»,
столбец с простейшей(Last Name) — в первоначальном объединяемом чтобы эта ячейка объединить содержимое двух проставление из самой 1) If Len(avData(lr, сцепляет все указанные
вертикали и горизонтали). слив туда же Витенька + телемастерamix_6У меня VBA: 2 вопроса:
критично, ведь синтаксис
подсказку при вводе «>=», «» формулой:
фамилия диапазоне. Более того, никогда после этого и более ячеек функции, то программно lc)) Then sRes
ячейки в одну В одну ячейку их текст. Проблема Жора +, попробуйте — Public
оказалась быстрее в- как убрать прост. UDF5. Значение условияЗначение из последней строкиТеперь, когда аргументы определены, можно с помощью не заполнялась никакими в Эксель без вообще не получится, = sRes & с указанным разделителем. в общем случае в одном -сволочь Редулов + Function СЦЕПИТЬДИАПАЗОНn(ByRef ДИАПАЗОН 21 раз (0.4 ошибку #ЗНАЧ! приЗавтра обязательно проверю(так, как это №1, с которым будет содержать желаемую мы можем записать данной функции внедрять данными. потерь можно применить т.к. точка-с-запятой(проставляемая для разделитель & avData(lr, ‘ Аргументы функции: чисто формулой не кнопка не вспомнить имени, As Excel.Range, Optional секунды) чем VBScript отсутствии критериев сцепления Concat1 на рабочем реализовано для «родных» сравниваются значения ячеек текстовую строку (см. в ячейку и другие операторы.Выполняем те же действия, функцию рваных диапазонов) является lc) End If ‘ Диапазон — получится все этоОбъединить и поместить в длинноволосый такой + ByVal РАЗДЕЛИТЕЛЬ As (9.5 секунды)Вместе с(когда просто надо файле (сейчас нет
формул) из п.3
файл примера).E2Выполняем действия по добавлению
что и приСЦЕПИТЬ
еще и разделителем Next lr Next диапазон ячеек, значения
засунуть. центре (Merge andещё 19 мужиков String = «»)
тем, вариант реализации сцепить диапазон)? под рукой).- как Вам
и т.д. следующиеВозвращаясь к функции СЦЕПИТЬ()вот такую формулу: значений в окно первом способе применения. аргументов. Сама функция lc Next If
которых необходимо объединитьЭто все с
Center) + муж = As String Dim
через объект- как кДумаю, проблем не
удалось сделать выполнение условия аналогично п.п.
можно обнаружить, что=CONCATENATE(B2,» «,A2) аргументов функции любым
функцииВыделяем ячейку, в которой
ничего не сможет Len(sRes) Then sRes в строку. ‘
учетом того, чтов Excel объединять-то любовь!» v Dim MergeText
ScriptControl этой UDF добавить возникнет.
макроса в 2013 3,4,5 она может вернуть
=СЦЕПИТЬ(B2;» «;A2) из способов, которыйСЦЕПИТЬ планируем разместить объединенные сделать в этом = Mid(sRes, Len(разделитель) Разделитель — необязательный Вам ведь нужно, ячейки умеет, аВ категории As String For, на мой взгляд, опцию «Без повторов»?To ZVI: огромное спасибо офисе быстрее, чем
Все условия работают несколько значений вКак и с любой был приведен выше., вплоть до открытия
данные. Кликаем на случае и ошибка + 1) End
аргумент. ‘ Один чтобы автоматически все вот с текстомТекстовые Each v In будет быстрее вариантаZVI
за UDF и в 2007 по И-логике (And) одной строке и другой функцией Excel, В одно из
окна аргументов оператора.
кнопку возникнет еще на If If БезПовторов или несколько символов, менялось, правильно? сложность — весть функция ДИАПАЗОН.Value If vEvaluate(): 1. В моем советы!
(во всех моихПример вызова функции: без утомительного ввода важен синтаксис. Не полей (при необходимости Добавляем значение первой«Вставить функцию» стадии обращения к
Then Dim oDict которые будут вставленыНо вообще-то для живых остается толькоСЦЕПИТЬ (CONCATENATE) <> «» Then
. примере работает: =Concat(A1:A6;RAN макросах получается наоборот)=Concat(A1:A6; «,»; B1:B6;»=»;3; многочисленных аргументов. Для забудьте начать со их может быть
ячейки с данными
. Она имеет вид функции, без выполнения As Object, sTmpStr между каждым словом. этой задачи как текст из верхней, которая соединяет содержимое MergeText = MergeTextЦитатаZVI пишет: «,» ): А для общегоnerv
C1:C6;»>=»;20; D1:D6;»=»;»text3″) этого запишем формулу знака равенства (=) и несколько) добавляем в поле окна, пиктограммы и размещена даже первой строки Set oDict = ‘ По умолчанию раз прекрасно подходят левой ячейки. нескольких ячеек (до
& РАЗДЕЛИТЕЛЬ &
Александр, хотелось бы2. Нужно сначала развития можно познакомить?:nerv =СЦЕПИТЬ(A6:A9), указав в и поставить между любой текстовый материал, как это уже
слева от строки
кода. CreateObject(«Scripting.Dictionary») sTmpStr = пробел. ‘ БезПовторов
UDF-ки, которых огромнаяЧтобы объединение ячеек происходило 255) в одно v End If
в теме автора придумать, как задаватьZVIZVI,: Хорошо, что все качестве единственного аргумента аргументами разделители (запятая который пользователь считает было описано ранее.
формул.А если вписывать Split(sRes, разделитель) On
— необязательный аргумент. куча понаписана. с объединением текста
целое, позволяя комбинировать Next MergeText = решать проблемы автора.считаю , что требуется: Рад был помочь,
как-то так на переехали
весь диапазон сразу, или точка с нужным добавить. Данный
Затем устанавливаем курсорОткрывается другой процедурой - Error Resume Next
‘ Если указан
Здесь в Готовых (как в таблицах
их с произвольным Mid(MergeText, Len(РАЗДЕЛИТЕЛЬ) +
отхождение от темы именно «уникально». справку могу в
скорую руку. Правда,ZVI, а не отдельные запятой).
текст обязательно должен во второе поле,Мастер функций не вижу проблем.
For lr = как ИСТИНА или решениях парочка есть
Word) придется использовать текстом. Например, вот
1) СЦЕПИТЬДИАПАЗОНn =
делом обычным иНаверное, проще сделать
принципе и нарисовать, не уверен, чтосогласитесь, не очень
ячейки. В результатеЗамечание: быть заключен в и выделяем ту. В категории Либо через Union LBound(sTmpStr) To UBound(sTmpStr) 1 — в http://www.excelworld.ru/forum/3-0-1-0-10-1-[%D1%81%D1%86%D0%B5%D0%BF] макрос. Для этого так:
MergeText End Function не подсудным, т.к. еще одну функция но тогда придется
будет работать в гибко получим лишь значениеставить между аргументами
Сцепить диапазон по столбцам
кавычки. Жмем на пустую ячейку с«Текстовые» передавайте диапазон, либо oDict.Add sTmpStr(lr), sTmpStr(lr) результирующей строке будутLogotip откройте редактор VisualНюанс: не забудьте оamix_6
эти отхождения, зачастую,
с другим именем, все это засовывать Office x64В данном случае первой ячейки. запятую или точку кнопку пробелом, о которойили вписывайте текстом через Next lr sRes значения без дубликатов.: Basic на вкладке пробелах между словами: Спасибо огромное, это гораздо более интересны например, ConcatUniq(), чтобы
в надстройку. ЗадачкаFunction Foo(ByVal Valid идеальный вариант (наТеперь в Строке формул с запятой –«OK» шла речь ранее.«Полный алфавитный перечень» .Formula. = «» sTmpStr ‘ Для английскойСогласен, это соответствуетРазработчик - — их надо то что надо! основной поставленной задачи не усложнять синтаксис была специфическая, но As String, a, мой взгляд) передавать выделим формулу =СЦЕПИТЬ(A6:A9)
зависит от того,. Появляется ссылка в
ищем операторJack Famous = oDict.Keys For локализации данный параметр условиям задачи ))Visual Basic (Developer - прописывать как отдельныеПопрошу еще оЦитатаRAN пишет:
аргументов. Или требуется,
интересная, спасибо и b) As Boolean функцию, возвращающую степень и нажмем клавишу в какой странеКак видим, после этого поле окна аргументов.«СЦЕПИТЬ»: The_Prist, большое спасибо
lr = LBound(sTmpStr) указывается как TRUEЦитата Visual Basic) аргументы и заключать помощи, может можноА для общего чтобы всегда были Вам — за With CreateObject(«ScriptControl») .Language истинности условия, что F9. Получим массив Вы живёте и действия к объединенным Для ускорения процесса. Выделяем это наименование за советы! To UBound(sTmpStr) sRes и FALSE соответственно._Boroda_, 03.11.2017 вили сочетанием клавиш в скобки, ибо чтобы разделитель между
развития можно познакомить?я только уникальные? развлечение = «JScript» .AddCode то вроде =<«Вася»:»Петя»:»Даша»:»Маша»>, его можно какой версией Excel
данным был добавлен можно её скопировать, и жмем наНа вашей тестить = sRes & ‘————————————————————————————— Function СцепитьМного(диапазон 10:41, в сообщении Alt+F11, вставим в текст. данными в столбцахзаПотом еще иTo RAN: «function fn(a,b,c,d,e) b скопировать в буфер пользуетесь. и текстовый материал. выделив и нажав кнопку не буду, но IIf(sRes <> «», As Range, Optional № 4 () нашу книгу новыйОчевидно, что если нужно был один (напримерZVI сортировка потребуется?Цитата А для & Valid &Acid Burn обмена (без знакаВот и всё! КогдаОператор сочетание клавиш«OK» есть задумка UDF разделитель, «») & разделитель As String А если серьезно, программный модуль (меню собрать много фрагментов, запятая), а между: Мне же неизвестно,Acid Burn
общего развития можно
«>» Foo =: = и фигурных нажметеСЦЕПИТЬCtrl+C. на основе СЧИТАТЬПУСТОТЫ sTmpStr(lr) Next lr = » «, то формула массива
Insert — Module
3 способа склеить текст из нескольких ячеек
то использовать эту группами значений столбцов в какие ячейки: познакомить?Андрей, там же
.Run(«fn», a, b)ZVI скобок) и вставитьEnter
– единственная возможность.Запускается окно аргументов функции.
для рваных диапазонов. End If СцепитьМного Optional БезПовторов As
Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ
(да и не) и скопируем туда функцию уже не другой (например точка помещать результат.> работает: =Concat(A1:A6; «,») было продолжение (подчеркнуто): End With End, Вы — гений! в нужную ячейку., появится полное имя:
объединения ячеек безЗатем добавляем ссылку на В качестве аргументов Создам тут тему = sRes End Boolean = False) только, обычная иногда
текст такого простого очень удобно, т.к. с запятой).Раз ситуация сДа, но есть «есть гораздо более Function Sub Example()Погонял функцию:От наборов символов «:»Josephine Carter потерь в Excel. следующий элемент, который могут выступать ссылки и опробую уже Function Dim avData, lr тоже) дает результатом макроса: придется прописывать ссылкиСпасибо еще раз! 32787 символами не ограничение количества строк быстрые методы, но MsgBox Foo(«a >- Правильно работает (двойные кавычки, двоеточие. Кроме того, с
нужно добавить. В на ячейки, содержащие на ней)))The_Prist As Long, lc МАССИВ. Массив можноSub MergeToOneCell() Const
на каждую ячейку-фрагментamix_6
типичная, то пока
5652 строк). они на грани b», 4, 3) с текстом, числами
вывести на лист
Способ 2. Символ для склеивания текста (&)
sDELIM As String по отдельности. Поэтому,: Здравствуйте, просто добавил сортировку
Т.е. =Concat(A1:A10000; «,») здравого смысла, ‘ True MsgBox и датами кавычки) можно избавиться маркер автозаполнения, скопируйте соединять целые столбцы, добавляем ссылку на текст. Если в: Нужно сцепить диапазон дублей). Будет выглядеть As String avData в столько ячеек, = » « начиная с 2016amix_6
- и сделал еще уже не сработает.поэтому о них тоже Foo(«a > b»,- Операторы и с помощью функции формулу во все добавлять текстовые значения,
- пустую ячейку. Так задачу входит объединение ячеек который указан так: Option Explicit = диапазон.Value If сколько в этом ‘символ-разделитель Dim rCell версии Excel, на, Улучшил предложенную выше одну функцию для> или требуется, чтобы не распространяюсь 4, 7) ‘ разделители можно задавать ПОДСТАВИТЬ() (см. файл
ячейки до проводить некоторые другие как мы скопировали содержимого ячеек, то в EXCEL (разделитель
‘————————————————————————————— ‘ Procedure Not IsArray(avData) Then массиве значений (по As Range Dim замену функции функцию: теперь в уникальных значений CondUnique(), всегда были только
Способ 3. Макрос для объединения ячеек без потери текста.
» False End Sub ссылкой на ячейку примера). или сE11 манипуляции. Знание алгоритма её адрес, то в данном случае — пробел) : СцепитьМного ‘ СцепитьМного = avData вертикали и горизонтали). sMergeStr As StringСЦЕПИТЬ ее параметрах можно аргументы — те уникальные?Не готов грузить
ZVI- Время выполнения помощью инструмента Заменить. В итоге полное работы с этой можно установить курсор мы будем работатьStep_UA http://www.excel-vba.ru ‘ Purpose Exit Function End В одну ячейку If TypeName(Selection) <>пришла ее более задавать отдельно разделитель же> Потом еще и этим сумасшествием.: Александр, медленно все
в массиве 4х1048576 (нажмите имя появится для функцией позволит облегчить в поле и только со ссылками.: Function SSS(R As : Функция сцепляет If For lc в общем случае «Range» Then Exit совершенная версия с строк и отдельноAcid Burn сортировка потребуется?Acid Burn это будет. Можно (4х10.000 строк заполнено):CTRL+H каждого человека. решение многих вопросов нажать сочетание клавишУстанавливаем курсор в первое Range) As String все указанные ячейки = 1 To чисто формулой не Sub ‘если выделены
похожим названием и — разделитель столбцов.:Нет, по ситуации: О подсказках / значительно быстрее.10.98/ 9.11/ 4.98, чтобы его вызвать).Если хотите усложнить задачу, для пользователя программы.Ctrl+V поле окна. Затем SSS = Join(WorksheetFunction.Transpose(R.Value),
СЦЕПИТЬ в одну ячейку формулой массива
в одну с UBound(avData, 2) For
получится все это не ячейки -
тем же синтаксисомPublic Function СцепитьДиапазон(ByValСпасибо! требуется выбрать все
справке к UDF
Но мне-то это
сек. (Excel 2007/ Если строка Вася»:»Петя»:»Даша»:»Маша то попробуйте приАвтор: Максим Тютюшев. Координаты будут вставлены. выделяем ссылку на » «) End указанным разделителем. ‘
lr = 1 засунуть. выходим With Selection — функция Диапазон As Range,А можно привести или только уникальные. я поинтересовался для
зачем? Здесь же 2010/ 2013) находится в ячейке помощи функцииИм пользовался Макгайвер. Экипаж
Таким способом чередуем листе, в которой FunctionВ качестве параметра
Аргументы функции: ‘ To UBound(avData, 1)
Ну, почему же For Each rCellСЦЕП (CONCAT)
_ Optional ByVal синтаксис Concat() к
А сортировку и общего развития. автору темы помогаем,
Несколько вопросов: можноG8
СЦЕПИТЬ Аполлон-13 также использовал поля с адресами
содержатся данные, нужные передаем связаный диапазон Диапазон — диапазон If Len(avData(lr, lc)) не получится (если
In .Cells sMergeStr. Ее принципиальное отличие РазделительСтрок As String виду: разбивку по строкамВы сделали отличную а не мне ли, то формула длясвязать город и его. Всегда в элементов и пустой для объединения. После из не менее ячеек, значения которых Then sRes =
я Вас правильно = sMergeStr & в том, что = «, «,=Concat(B1:B6;»,»; 1/0;1/0; C1:C6;»=»;3;
выполняет мой примитивный вещь, которая неnerv- добавить в замены этих символов,
штат в столбце трудной ситуации, когда ячейки. После того,
того, как координаты 2-х ячеек 
необходимо объединить в sRes & разделитель
понял)? sDELIM & rCell.Text теперь в качестве _ Optional ByVal D1:D6;»>=»;20; E1:E6;»=»;»text3″; . ) макрос. нуждается в каких-либо: т.е. я бы макрос операторы > например, на символF нужно сцепить две как все данные отобразились в окошке,А3: =sss(A5:A304) строку. ‘ Разделитель & avData(lr, lc)Если все на ‘собираем текст из аргументов можно задавать
РазделительСтолбцов As String где 1/0 -На самом деле комментариях.
сделал так и , чтобы получилось как
вещи, люди берут внесены, жмем на аналогичным образом поступаем. нужно помнить, — необязательный аргумент. End If Next том же примере ячеек Next rCell не одиночные ячейки, = «; «) исключить/оставить повторы; выполнить/пропустить
здорово бы сделатьНе тратьте наЦитатаZVI пишет: только =, >=,
так: на рисунке ниже: в руки скотч. кнопку
со вторым полем. что есть ограничения
‘ Один или lr Next lc рассмотреть следующую формулу: Application.DisplayAlerts = False а целые диапазоны
Dim i As сортировку ConcatUniq() с сортировкой. это Ваше драгоценноемедленно все это?=ПОДСТАВИТЬ(G8;»»»:»»»;»;»)При помощи функции Возможно, Вы сильно«OK» Соответственно выделяем другую на длину текста несколько символов, которые If Len(sRes) Then=СЦЕП(ЕСЛИ(—ЕОШ(ПОИСК(M1;A2:E2))=0;СТОЛБЕЦ(A:E);»X»))
‘отключаем стандартное предупреждение — текст из Long, n AsЭто было быНо я уже время. будетосмелюсь предположить, что- подрезать диапазонВ итоге, вставив результатСЦЕПИТЬ удивитесь, но в
Сцепить ячейки в несмежном (рваном) диапазоне
. ячейку. Подобную операцию в одной ячейке
будут вставлены между sRes = Mid(sRes,Формула массивная и о потере текста всех ячеек всех Long, v() As просто пределом мечтаний!
стесняюсь просить ВасPS: Проверил новую версию. не значительно. Функция строк под UsedRange? формулы как значение,Вы можете связывать Excel есть встроеннаяКак видим, после этого проделываем, пока координатыtankwar121 каждым словом. ‘
Len(разделитель) + 1) дает результат 1XX45, .Merge Across:=False ‘объединяем диапазонов будет объединен Variant n =
ZVI об этом. Время обработки на эвалится один раз.- изменить синтаксис получим строку Вася;Петя;Даша;Маша. даже числа и функция, которая делает в целевой ячейке всех ячеек, которые: По умолчанию пробел. End If If где 1,4 и ячейки Application.DisplayAlerts = в одно целое: Диапазон.Columns.Count: v =: Сделал. Порядок аргументовZVI том же тестовом В дальнейшем вызывается=Concat(B1:B6;»,»; C1:C6;»=»;3; D1:D6;»>=»;20;Примечание текст. Давайте представим, то же самое. образовалась объединенная запись, нужно объединить, неStep_UA ‘ БезПовторов — БезПовторов Then Dim 5 это «адреса» True .Item(1).Value =Для массового объединения также Диапазон.Value ReDim s(1 функции:: Это не ограничение файле сократилось до с параметрами. Вместо E1:E6;»=»;»text3″) до вида: В формуле для что мы используем Это функция включающая содержимое всех будут внесены в,не работает, даже уменьшая необязательный аргумент. ‘ oDict As Object, столбцов, при помощи Mid(sMergeStr, 1 + удобно использовать новую To n) As1. Диапазон ячеек функции, а ограничение 0,06/ 0,05/ 0,04.Language = «JScript» =Concat(B1:B6;»,»; C1:C6=3; D1:D6>=20; замены двойных кавычек Excel для храненияCONCATENATE элементов, но с окно аргументов функции. количество ячеек Если указан как sTmpStr Set oDict которых через КодИНДЕКС Len(sDELIM)) ‘добавляем к функцию String For i (столбец или несколько Excel — в сек. (Excel 2007/10/13),можно писать на E1:E6=»text3″) ? » использовано 2 записей об инвентаризации(СЦЕПИТЬ). пробелами между каждым После этого жмемStep_UA ИСТИНА или 1 = CreateObject(«Scripting.Dictionary») sTmpStr можно подтянуть нужные объед.ячейке суммарный текстОБЪЕДИНИТЬ (TEXTJOIN) = 1 To смежных), которые должны ячейке не может т.к. из 1.048.576 «родном» vbs.Спасибо! двойные кавычки «». магазина. Сейчас уФункция словом. на кнопку: Код в модуль — в результирующей = Split(sRes, разделитель) ячейки и далее End With End, появившуюся начиная с n: s(i) = попасть в результат быть больше 32767 строк заполнено лишьZVIZVIAcid Burn нас естьCONCATENATEВнимание! Как видим, вышеуказанный«OK» . строке будут значения On Error Resume аналогично цифрам «1XX45″ Sub Excel 2016. У Join(Application.Transpose(Application.Index(v, , i)),2. Разделитель результата символов. Если в 10.000.: По п.1 это: Не соглашусь, просто:25 яблок(СЦЕПИТЬ) позволяет связать способ значительно ускоряет.Step_UA без дубликатов. ‘ Next For lr через КодСЦЕПИТЬ сложитьТеперь, если выделить несколько нее следующий синтаксис: РазделительСтрок): Next СцепитьДиапазон3. Признак уникальности: ячейках A1:A10000 значенияНормально работает (не я забыл про сделайте «куда правильнее»Привет, Планетяне!(apples), но число два или более процедуру корректного объединенияКак видим, содержимое выбранных: или процедурой Sub Для английской локализации = LBound(sTmpStr) To в одну ячейку. ячеек и запустить=ОБЪЕДИНИТЬ(Разделитель; Пропускать_ли_пустые_ячейки; Диапазон1; Диапазон2 = Join(s, РазделительСтолбцов) 0/1 = Все/Уникальные длиной не более пересчитывается и не «>» и « и выложите здесьЯ думаю, не «25» и слово куска текста в данных в ячейках. областей отразилось в SSS_() ‘ [a3] данный параметр указывается UBound(sTmpStr) oDict.Add sTmpStr(lr),Вот только в этот макрос с . ) End FunctionС уважением,4. Признак сортировки: 2-х символов, то выдаёт ошибку) на По п.2 -ZVI только мне, но «apples» хранятся в
одной ячейке. Несмотря Но следует учесть, одной предварительно указанной — результирующая ячейка как TRUE и sTmpStr(lr) Next lr эту же формулу помощью сочетания клавишгде Аксима 0/1 = НеСортировать/Сортировать все сработает (3 не активных листах добавил подрезку диапазонов.: 1. Не понял и многим другим разных ячейках. Попробуем на длинное имя, что данный вариант ячейке. Но у ‘ [a5] - FALSE соответственно. ‘————————————————————————————— sRes = «» подсунуть КодИНДЕКС у Alt+F8 или кнопкойРазделительЗдравствуйте. Здорово Спасибо5. Столбец №1 символа на ячейку с другим UsedRange.По п.3 - вопроса, все условия часто требуется сцепить связать их в она очень проста таит в себе данного способа имеется начальная ячейка [a3] Function СцепитьМного(диапазон As sTmpStr = oDict.Keys меня не получается.Макросы- символ, который огромное! Правда если ячеек, участвующих в с учетом одного Это просто фантастика! оставил из-за дат. итак считаются по диапазон ячеек в одной ячейке, чтобы в использовании и и «подводные камни». существенный недостаток. При = Join(WorksheetFunction.Transpose(Range([a5], Cells(Rows.Count, Range, Optional разделитель For lr =Даже пробовал черезна вкладке Разработчик будет вставлен между ячейка пустая разделитель условии №1 символа разделителя, итогоВсем рекомендую к применению!Полноценные подсказки при И. Если в одну. получилось что-то вроде одинаково работает во Очень важно, чтобы его использовании происходит 1).End(xlUp)).Value), » «) As String = LBound(sTmpStr) To UBound(sTmpStr) КодДВССЫЛ, но результата(Developer — Macros) фрагментами все равно ставится.Если6. Оператор условия 30000 символов).Ещё раз СПАСИБО ZVI вводе UDF в строке найдено первоеСтандартная функция Excel этого: всех версиях Excel, в элементе, который так называемая «склейка End Sub » «, Optional sRes = sRes так и не
, то Excel объединитВторой аргумент отвечает за это вам не 
№1, может бытьС учетом этого, и с Наступающим
ячейку не поддерживаются. несовпадение условия, то крайне неудобна, ноНам понадобится связать три а также в
содержит пробел, со без шва». Тоtankwar121 БезПовторов As Boolean & IIf(sRes <> получил. выделенные ячейки в то, нужно ли нравится, то предлагаю одним из: «=», Вы уверены, что всех!При вводе с проверка в этой можно найти несколько элемента: других электронных таблицах,
временем не появились есть, между словами: = False) Dim «», разделитель, «»)Ваше решение абсолютно одну, слив туда
игнорировать пустые ячейки такой вариант: «>», «=», «»
правильно поступаете, накапливаяZVI помощью мастера функций строке прекращается. UDF, отличающихся по:F17 например, в Google какие-то данные или
Как сцепить большое количество ячеек?
нет пробела иStep_UA avData, lr As & sTmpStr(lr) Next верно решает задачу
же и текст (ИСТИНА или ЛОЖЬ)Public Function СцепитьДиапазон2(ByVal7. Значение условия все в одной: Все же приведу можно добавить подсказки,2. Пустые ячейки- ресурсоёмкости и
(Number in stock)
Sheets. он не был они склеены в, Большущее спасибо! Все
Long, lc As lr End If, но хотелось через пробелы.Диапазон 1, 2, 3
Диапазон As Range, №1, с которым ячейке?
код для сравнения но для старых в данных пропускаются, рабочему диапазону (ActiveSheet — количествоПримечание: сдвинут. единый массив. При работает!
Long, sRes As СцепитьМного = sRes минимизировать количество формул,Logotip. — диапазоны
Источник
Функция конкатенации в Excel используется для объединения или объединения двух или более чем двух символов, строк или чисел, функция конкатенации заменяет использование оператора & для соединения, поскольку операторы & сделали синтаксис более сложным, в то время как функция конкатенации выглядит более чистой и Легко понять.
Аргументы, кроме первого, необязательны; поэтому они заключены в квадратные скобки.
Как объединить ячейки в Excel — ЭКСЕЛЬ ХАК
Вообще, большинство типовых задач в Excel решается с помощью простых формул, в которых не сложно разобраться. И в этой заметке я попробую привести максимально наглядные примеры по объединению ячеек (рассмотрю два варианта: когда ячейки пустые, и когда в них уже есть данные).
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе От новичка до мастера Excel. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
muncherelli, мне понравился ваш ответ, и я подправил его :). Просто незначительная вещь, бывают случаи, когда я вытаскиваю данные из листа и использую его для запроса к базе данных. Я добавил необязательный параметр «textQualify», который помогает создать разделенный запятыми список, используемый в запросе.
Преобразование столбца в список, разделенный запятыми
В общем, ни одна из колонок, о которых я говорил выше, не подходит для уникальной идентификации. Но если мы объединим все колонки вместе, мы увеличим вероятность того, что значение будет абсолютно уникальным.
Как объединить ячейки в Excel с помощью контекстного меню
Выделите диапазон ячеек для сцепки. Нажмите правую клавишу мыши. В контекстном меню выберите пункт «Формат ячеек»:
В появившемся окне перейдите на вкладку «Выравнивание» и отметьте галочкой пункт «Объединение ячеек», затем нажмите «ОК»:
При таком способе объединения, Excel по умолчанию оставляет данные только из верхней левой строчки, остальные данные будут удалены. В случае, если в выделенных ячейках находятся данные, то система вас предупредит об удалении всех данных кроме тех, что находятся в верхней левой ячейке.
В результате, у нас остался текст «Фамилия», а «Имя» и «Отчество» были удалены:
Объединить функцию Excel.
В функции CONCATENATE в Excel мы также можем передать полную формулу CONCATENATE в Excel в качестве аргумента. Например, если мы хотим, чтобы результат двух формул был объединен, мы можем передать формулы в качестве аргумента, чтобы получить требуемый результат.
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Когда мы поместили пробел в двойные кавычки после запятой после первого аргумента, функция CONCATENATE приняла его как второй аргумент. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Например, если у нас есть список имен и фамилий в столбцах A и B, и мы хотим, чтобы полное имя, которое является первым именем вместе с фамилией в столбце C, мы будем использовать функцию CONCATENATE.
Как объединить ячейки в Excel (простые способы)
- «Объединить и поместить в центре» — система объединит ячейки и отформатирует текст по центру;
- «Объединить по строкам» — эта настройка позволит в каждой строке произвести сцепку ячеек.
- «Объединить ячейки» — система произведет сцепку выделенного диапазона без централизации и т.д.
- «Отменить объединение ячеек» — отменить сцепку ячеек.
Таким образом, вы можете видеть, когда мы передаем аргумент, отличный от ссылочного значения, и мы всегда должны заключать его в двойные кавычки, потому что MS-Excel и другие офисные пакеты написаны на C ++, а в C ++ строковые литералы всегда берутся в двойных кавычках.
Если в ячейках есть данные (которые также нужно объединить)
Важная ремарка : при объединении ячеек мы будем не суммировать их содержимое, а «склеивать» (сцеплять) между собой. Т.е. если у вас в одной ячейке приводится имя, а в другой фамилия — после объединения будет одна строка с именем и фамилией; если в одной ячейке цифра «5», а в другой «3» — после объединения будет «53» (а не как при сумме: «8») .
Способ 1
В качестве примера взял список ФИО нескольких вымышленных персонажей. Задача, кстати, вполне приближена к жизни (приходилось с этим сталкиваться).
Сначала необходимо выбрать одну из ячеек напротив первой строки (я взял E1) и написать в строке формул следующее выражение:
После того, как нажмете Enter, формула сцепит все перечисленные ячейки в ней. В моем случае получилось ФИО, но без пробелов (что ни есть удобно).
Для того, чтобы исправить это, просто измените частично формулу (добавьте кавычки с пробелом, как в моем примере) :
Если вам нужны пробелы (или другие символы) между объединенными данными — корректируем формулу
Разумеется, вместо пробела в кавычках можно поставить любой другой символ (дефис, например).
После, останется только растянуть формулу на другие строки, и задача будет решенной.
Как перенести склеенные данные в другой документ (чтобы они не зависели от формул)
Наши сцепленные строки в этом документе зависят от формул, и если слегка их изменить — то все «разъедется». В ряде случае гораздо удобнее работать не с ними, а с текстовыми строками. Попробуем перенести наши результаты в другой документ (или новый лист).
И так, сначала необходимо выделить наши сцепленные ячейки (в моем примере это столбец «E»), нажать правой кнопкой мышки по ним и выбрать функцию «Копировать» .
Далее открыть новый документ (или лист) кликнуть правой кнопкой мышки по нужной ячейке и нажать по меню «Специальная вставка» . См. пример ниже.
В появившемся окне настроек укажите, что вам требуется вставить только «Значения» (а не сами формулы) и нажмите OK.
Как видите на скрине ниже, в документ были вставлены текстовые значения из ранее сцепленных ячеек (а не сами формулы).
Способ 2
Объединять ячейки в Excel можно и без спец. формул, достаточно использовать знак «&».
Разумеется, чтобы добавить пробелы между объединенными значениями, нужно слегка изменить формулу:
Если вместо пробелов вам нужны дефисы, формула несколько преобразится:
Как будет выглядеть формула, если вместо пробелов вставим дефисы
Когда «настройка» формулы будет завершена — просто потяните мышкой за уголок и растяните ее на другие строки (столбцы). После, Excel применит ее к другим ячейкам (пример ниже).
Что касается переноса полученных результатов в другой документ, то это делается аналогично вышесказанному.
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Но на практике имеет смысл передавать в формулу минимум два аргумента, иначе на выходе мы получим то же самое, что и передали на входе, а это не имеет смысла. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Рассмотрим как сцепить текст в Excel на простейшем примере. Допустим, в ячейке А1 у нас есть текст «Иванов», в ячейке А2 находится текст «Иван», а в ячейке А3 записан текст «Иванович». Получим ФИО склейкой (сцеплением) содержимого всех трёх ячеек. Формула будет выглядеть так:
Функция СЦЕПИТЬ в Excel | MS Office | Учебные статьи
Для этих упражнений, я рекомендую использовать документы Excel, которые я создал специально для этого урока. Если вы пропустили это раньше, то обязательно скачайте бесплатно прилагающиеся к этому уроку, Книгу Excel, с которой вы сможете работать и следить за действиями по ходу урока.