Как сцепить много ячеек в excel через запятую
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Объединение значений нескольких ячеек в одну текстовую строку в EXCEL
Предположим, что имеется диапазон имен, необходимо, чтобы все имена были выведены подряд в одной ячейке.
Когда значений немного, то объединить их можно с помощью функции СЦЕПИТЬ() записав формулу =СЦЕПИТЬ(A6;A7;A8;A9)
Или с помощью Амперсанда & (добавим еще для красоты символ пробела): =A6&» «&A7&» «&A8&» «&A9
Если значений много, то придется вводить вручную много амперсандов и аргументов функции СЦЕПИТЬ() . Это неудобно.
Избежать этого поможет дополнительный столбец с простейшей формулой:
Значение из последней строки будет содержать желаемую текстовую строку (см. файл примера ).
Возвращаясь к функции СЦЕПИТЬ() можно обнаружить, что она может вернуть несколько значений в одной строке и без утомительного ввода многочисленных аргументов. Для этого запишем формулу =СЦЕПИТЬ(A6:A9) , указав в качестве единственного аргумента весь диапазон сразу, а не отдельные ячейки. В результате получим лишь значение первой ячейки.
Теперь в Строке формул выделим формулу =СЦЕПИТЬ(A6:A9) и нажмем клавишу F9 . Получим массив =, его можно скопировать в буфер обмена (без знака = и фигурных скобок) и вставить в нужную ячейку.
От наборов символов «:» (двойные кавычки, двоеточие и опять двойные кавычки) можно избавиться с помощью функции ПОДСТАВИТЬ() (см. файл примера ). или с помощью инструмента Заменить (нажмите CTRL+H , чтобы его вызвать). Если строка Вася»:»Петя»:»Даша»:»Маша находится в ячейке G8 , то формула для замены этих символов, например, на символ ; будет выглядеть так:
В итоге, вставив результат формулы как значение, получим строку Вася;Петя;Даша;Маша.
Примечание : В формуле для замены двойных кавычек » использовано 2 двойные кавычки «».
Формула СЦЕПИТЬДИАПАЗОН
Функция =СЦЕПИТЬДИАПАЗОН(ДИАПАЗОН, [РАЗДЕЛИТЕЛЬ]) имеет два аргумента:
- — ДИАПАЗОН — диапазон ячеек, которые необходимо сцепить.
- — [РАЗДЕЛИТЕЛЬ] — символ-разделитель, который будет вставляться между значениями ячеек. Данный аргумент является не обязательным.
Аналогичная функция появилась только в Excel 2016. Если у Вас Excel ранней версии, то надстройка полностью исправляет это упущение разработчиков.
Далее привожу множество примеров использования данной функции.
Пример 1
Просто сцепить все ячейки диапазона, вставив между ними пробел.
Этот пример можно сделать также с помощью стандартной функции СЦЕПИТЬ, однако для этого придется перечислить все ячейки диапазона. Если их две-три, то это не проблема, а вот если их гораздо больше, то выбор в пользу функции СЦЕПИТЬДИАПАЗОН думаю очевиден.
Пример 2
Перечислить все фамилии через запятую.
Обратите внимание, что в качестве разделителя указана запятая с пробелом («, «). Если не указать пробел, то все ячейки «сольются».
Пример 3
Можно сцепить более одного столбца. При этом сцепление ячеек происходит построчно.
Пример 4
Используя промежуточные вычисления можно добиться нужного решения значительно проще и быстрее. Следующий пример демонстрирует как перечислить фамилии и инициалы, ставя запятую только после инициалов.
Код на VBA
Код короткий, но весьма полезный!
Public Function СЦЕПИТЬДИАПАЗОН(ByRef ДИАПАЗОН As Excel.Range, Optional ByVal РАЗДЕЛИТЕЛЬ As String = "") As String Dim rCell As Range Dim MergeText As String For Each rCell In ДИАПАЗОН If rCell.Text <> "" Then MergeText = MergeText & РАЗДЕЛИТЕЛЬ & rCell.Text End If Next MergeText = Mid(MergeText, Len(РАЗДЕЛИТЕЛЬ) + 1) СЦЕПИТЬДИАПАЗОН = MergeText End Function
Надстройка
VBA-Excel
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Объединение текста из двух или нескольких ячеек в одну
Данные из нескольких ячеек можно объединить в одну с помощью символа «амперсанд» (&) или функции СЦЕП.
Объединение данных с помощью символа «амперсанд» (&)
- Выделите ячейку, в которую вы хотите вставить объединенные данные.
- Введите = (знак равенства) и выберите первую ячейку, которую нужно объединить.
- Введите символ & и пробел, заключенный в кавычки.
- Выберите следующую ячейку, которую нужно объединить, и нажмите клавишу ВВОД. Пример формулы: =A2&» «&B2.
Объединение данных с помощью функции СЦЕП
- Выделите ячейку, в которую вы хотите вставить объединенные данные.
- Введите выражение = СЦЕП(.
- Выберите первую ячейку, которую нужно объединить. Для разделения объединяемых ячеек используйте запятые, а для добавления пробелов, запятых и другого текста — кавычки.
- Введите закрывающую скобку в конце формулы и нажмите клавишу ВВОД. Пример формулы: =CONCAT(A2, » Семья»).