четверг, 15 сентября 2016 г.
СЦЕПИТЬ МНОГО ЯЧЕЕК С УКАЗАННЫМ РАЗДЕЛИТЕЛЕМ
Часто бывает ситуация, когда необходимо из трех разных столбцов сцепить данные в одну строку с разделителем. Допустим в А1 Фамилия, в В1 - Имя, в С1 - Отчество, а надо получить все вместе Фамилия Имя Отчество. Как обычно в Excel объединяют значения нескольких ячеек в одну? Правильно, при помощи функции СЦЕПИТЬ или при помощи амперсанда:
=СЦЕПИТЬ(A1;" ";B1;" ";C1;" ")
=A1&" "&B1&" "&C1&" "
Это достаточно эффективно, если необходимо сцепить значения из трех-пяти ячеек. А если ячеек 50? Или того больше? Не очень удобно объединять их все описанными выше способами. А других встроенных функций в Excel для подобных операций не существует.
Поэтому я написал функцию пользователя, которая сцепляет данные из указанных ячеек в одну строку. Чем отличается от стандартной функции СЦЕПИТЬ()? Тем, что в качестве ячеек для сцепки указывается не каждая из ячеек по очереди, а сразу весь диапазон с возможностью указания разделителя между значениями каждой ячейки.
=A1&" "&B1&" "&C1&" "
Это достаточно эффективно, если необходимо сцепить значения из трех-пяти ячеек. А если ячеек 50? Или того больше? Не очень удобно объединять их все описанными выше способами. А других встроенных функций в Excel для подобных операций не существует.
Поэтому я написал функцию пользователя, которая сцепляет данные из указанных ячеек в одну строку. Чем отличается от стандартной функции СЦЕПИТЬ()? Тем, что в качестве ячеек для сцепки указывается не каждая из ячеек по очереди, а сразу весь диапазон с возможностью указания разделителя между значениями каждой ячейки.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : СцепитьМного
' http://www.excel-vba.ru
' Purpose : Функция сцепляет все указанные ячейки в одну с указанным разделителем.
' Аргументы функции:
' Диапазон — диапазон ячеек, значения которых необходимо объединить в строку.
' Разделитель — необязательный аргумент.
' Один или несколько символов, которые будут вставлены между каждым словом.
' По умолчанию пробел.
' БезПовторов — необязательный аргумент.
' Если указан как ИСТИНА или 1 — в результирующей строке будут значения без дубликатов.
' Для английской локализации данный параметр указывается как TRUE и FALSE соответственно.
'---------------------------------------------------------------------------------------
Function СцепитьМного(Диапазон As Range, Optional Разделитель As String = " ", Optional БезПовторов As Boolean = False)
Dim avData, lr As Long, lc As Long, sRes As String
avData = Диапазон.Value
If Not IsArray(avData) Then
СцепитьМного = avData
Exit Function
End If
For lc = 1 To UBound(avData, 2)
For lr = 1 To UBound(avData, 1)
If Len(avData(lr, lc)) Then
sRes = sRes & Разделитель & avData(lr, lc)
End If
Next lr
Next lc
If Len(sRes) Then
sRes = Mid(sRes, Len(Разделитель) + 1)
End If
If БезПовторов Then
Dim oDict As Object, sTmpStr
Set oDict = CreateObject("Scripting.Dictionary")
sTmpStr = Split(sRes, Разделитель)
On Error Resume Next
For lr = LBound(sTmpStr) To UBound(sTmpStr)
oDict.Add sTmpStr(lr), sTmpStr(lr)
Next lr
sRes = ""
sTmpStr = oDict.keys
For lr = LBound(sTmpStr) To UBound(sTmpStr)
sRes = sRes & IIf(sRes <> "", Разделитель, "") & sTmpStr(lr)
Next lr
End If
СцепитьМного = sRes
End Function
|
Чтобы применить код необходимо ознакомиться со статьей: Что такое функция пользователя(UDF)?
Синтаксис функции:
=СцепитьМного(A2:A100;", ";ИСТИНА)
Диапазон - диапазон ячеек, значения которых необходимо объединить в строку.
Разделитель - необязательный аргумент. Один или несколько символов, которые будут вставлены между каждым словом. По умолчанию пробел.
БезПовторов - необязательный аргумент. Если указан как ИСТИНА или 1 - в результирующей строке будут значения без дубликатов. Например, из значений Сидоров, Петров, Сидоров, Иванов в результат попадут только Сидоров, Петров, Иванов. Если ЛОЖЬ или 0 - будут выведены все значения. Для английской локализации данный параметр указывается как TRUE и FALSE соответственно.
Разделитель - необязательный аргумент. Один или несколько символов, которые будут вставлены между каждым словом. По умолчанию пробел.
БезПовторов - необязательный аргумент. Если указан как ИСТИНА или 1 - в результирующей строке будут значения без дубликатов. Например, из значений Сидоров, Петров, Сидоров, Иванов в результат попадут только Сидоров, Петров, Иванов. Если ЛОЖЬ или 0 - будут выведены все значения. Для английской локализации данный параметр указывается как TRUE и FALSE соответственно.
Скачать пример
Tips_Macro_CoupleCells.xls (54,0 KiB, 3 162 скачиваний)
0 коммент.:
Отправить комментарий