Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Список из заданного массива в одной ячейке, Формулой объеденить значения одного массива с заданным разделителем
 
#1
30.07.2014 22:01:45
Есть столбец из 10 ячеек.
Необходимо формулой (желательно формулой) из каждой ячейки этого диапазона взять значение, разделить каждое запятой или точкой с запятой и вставить в одну ячейку.
Пример прилагаю.
Кроме формулы СМЕЩ в голову ничего не приходит, но не могу придумать как её использовать.
 
 
 
#2
30.07.2014 22:55:37
А Вы пробовали посмотреть ПРИЕМЫ здесь, или тоже не не подходит?
 
 
 
#3
30.07.2014 23:13:56
Да, я видел этот прием, но он мне мне подходит.
В моем случае если диапазон будет A1:B10, то надо счеплять построчно, сначала А1, потом В1, А2, В2 итд...


Вот тут человек макросом создал формулу, которая позволяет формировать, но она формирует, к сожалению, только по столбцам, сначала столбец 1 из диапазона, потом построчно столбец 2 итд, а надо наоборот построчно обойти столбцы диапазона и счепить ячейки(

Нашел макрос и добавил еще 1 разделитель:

Код
Function CONCATENATErange(myRange As Range, mySeparator As String, mySeparator2 As String) As String
CONCATENATErange = ""
For Each rr In myRange
CONCATENATErange = CONCATENATErange & rr.Value & mySeparator2 & mySeparator
Next
End Function
Но беда в том, что он сцепляет хоть и последовательно, но не разделяет строки разделителями.
mySeparator2 служит для разделения данных ячеек в одной строка диапазона, а mySeparator должен вставить разделитель строк
Тут по идее еще один разделитель нужен чтобы в строке отделить значения первого столбца от второго например тире, но это уже как то сложно.
Как поправить макрос?
Изменено: andrey062006 - 30.07.2014 23:39:41
 
 
 
#4
30.07.2014 23:35:14
Цитата
andrey062006 пишет:
В моем случае если диапазон будет A1:B10...
А в примере только столбец А. Ну неужели нельзя следовать Правилам? "Приложите файл(ы) с примером ... в реальной структуре и форматах данных"
 
 
 
#5
30.07.2014 23:44:24
Юрий М, извините, мой косяк)
Файл приложил
 
 
 
#6
31.07.2014 04:59:42
Какое-то мутное объяснение... может покажете, что нужно получить, без формулы, вручную?
 
 
 
#7
31.07.2014 07:03:47
такое надо?
Код
Function CONCATENATErange(myRange As Range, mySeparator As String, mySeparator2 As String) As String
CONCATENATErange = ""
For Each rr In myRange.Rows
    For Each rr2 In rr.Columns
        CONCATENATErange = CONCATENATErange & rr2.Value & mySeparator
    Next
    CONCATENATErange = Left(CONCATENATErange, Len(CONCATENATErange) - 1) & mySeparator2
Next
CONCATENATErange = Left(CONCATENATErange, Len(CONCATENATErange) - 1)
End Function
Учимся сами и помогаем другим...
 
 
 
#8
31.07.2014 08:52:16
ber$erk, да!!!! Ураааа, спасибо!))))
Я кстати пробовал For Each rr In myRange.Rows, но у меня ничего не получалось, а тут оказывается надо ввести еще rr2, но все равно не очень понимаю строки 7 и 9 кода. А вот с .Columns я даже не эксперементировал, хотя мысль была, но я пробовал с For Each Cells In myRange.Row, но тут тоже беда настигала.
Макросам я только учусь потихоньку, поэтому всего не знаю.
Кстати, а что это за переменные rr и rr2 и где они объявлены я что то не понимаю?

P.S. А если имея 3 стобца в массиве мне каждое из этих 3 значений нужно разделить определенным разделителем?
Изменено: andrey062006 - 31.07.2014 09:01:49
 
 
 
#9
31.07.2014 09:46:10
Цитата
не очень понимаю строки 7 и 9 кода.
каждая строка в конце содержит символ разделителя. Его надо убрать, 7 и 9 это делают
Цитата
стати, а что это за переменные rr и rr2 и где они объявлены
их можно и не объявлять. Они будут типа Variant, но по правилам хорошего тона такие вещи лучше все таки объявлять типом Range
Цитата
А если имея 3 стобца в массиве мне каждое из этих 3 значений нужно разделить определенным разделителем
не совсем понял что надо, тем более, что в примере ничего не поменялось
Учимся сами и помогаем другим...
 
 
 
#10
31.07.2014 10:14:58
Цитата
ber$erk пишет:
не совсем понял что надо, тем более, что в примере ничего не поменялось
Я не знаю пока как поменять.
Я так понимаю что что можно объявить разделитель типа mySeparatorN, где N это порядковый номер разделителя, который можно указать будет в формуле
=CONCATENATErange(A1:C10;" ";[","];["!"].....)
Тогда, получается что не нужно объявлять mySeparator и mySeparator2. А в самой формуле на листе уже прописывать разделители в том порядке, в каком они должны быть представлены в строке.
Например, сейчас по функции выдается значение
1 f а,2 d в,3 s ы,4 a ф,  ,  ,  ,  ,  ,  
При текущих условиях (=CONCATENATErange(A1:C10;" ";",";" ,"  ;)  ) это правильно.
Но вот если сделать чтобы
1-f_ а,2-d_в,3-s_ы,4-a_ф,.......
Тогда формула должна быть примерно такой:
=CONCATENATErange(A1:C10;"-";"_";" ,"   )
Поэтому по моему надо ввести mySeparatorN, но вот как этот делать обход каждого N по функции не могу понять.
Ну или оставить mySeparator2 как основной разделитель одной строки от другой!
Изменено: andrey062006 - 31.07.2014 10:38:41
 
 
 
#11
31.07.2014 11:02:06
такое чувство, что у Вас узкая задача: для нее не надо придумывать универсальную функцию. просто введите четвертый параметр в функцию (третий вид разделителя) и стройте то, что нужно для решения.
Учимся сами и помогаем другим...
 
 
 
#12
31.07.2014 11:07:25
Цитата
ber$erk пишет:просто введите четвертый параметр в функцию (третий вид разделителя)
а куда его добавить? надо писать получается прописывать еще условие для r3, так?
 
 
 
#13
31.07.2014 11:13:50
Код
Function CONCATENATErange(myRange As Range, mySeparator As String, mySeparator2 As String, mySeparator3 As String) As String
CONCATENATErange = ""
m = True
For Each rr In myRange.Rows
    For Each rr2 In rr.Columns
        CONCATENATErange = CONCATENATErange & rr2.Value & IIf(m, mySeparator, mySeparator2)
        m = Not m
    Next
    CONCATENATErange = Left(CONCATENATErange, Len(CONCATENATErange) - 1) & mySeparator3
Next
CONCATENATErange = Left(CONCATENATErange, Len(CONCATENATErange) - 1)
End Function
Учимся сами и помогаем другим...
 
 
 
#14
31.07.2014 12:59:33
Всё ОК, только она теперь почему то чередует разделители :-(
1-f,а  !  2,d-в  !  3-s,ы  !  4,a-ф  !  -,  !  ,-  !  -,  !  ,-  !  -,  !  ,-  !
а должно быть
1-f,а  !  2-d,в  !  3-s,ы  !  4-a,ф ......................
Извините за тупой вопрос, а что за "m" введена?)
Изменено: andrey062006 - 31.07.2014 13:04:51
 
 
 
#15
31.07.2014 13:27:45
Вы скажите, задача для трех колонок или для любого количества?

Код
Function CONCATENATErange(myRange As Range, mySeparator As String, mySeparator2 As String, mySeparator3 As String) As String
CONCATENATErange = ""
For Each rr In myRange.Rows
    m = True
    For Each rr2 In rr.Columns
        CONCATENATErange = CONCATENATErange & rr2.Value & IIf(m, mySeparator, mySeparator2)
        m = Not m
    Next
    CONCATENATErange = Left(CONCATENATErange, Len(CONCATENATErange) - 1) & mySeparator3
Next
CONCATENATErange = Left(CONCATENATErange, Len(CONCATENATErange) - 1)
End Function
m здесь как переключатель, чтобы между первой и второй ячейкой вставлять один разделитель, а между второй и третьей - другой
Изменено: ber$erk - 31.07.2014 13:33:30
Учимся сами и помогаем другим...
 
 
 
#16
31.07.2014 13:30:28
Цитата
ber$erk пишет: задача для трех колонок или для любого количества?
Сейчас конкретно только для 3 колонок, больше не нужно.
Но количество колонок планируется расширять и, скорее всего будет 6-7 колонок.
За макрос ОГРОМНЕЙШЕЕ спасибо! Всё заработало как надо!!!!!!!!)))))))
 
 
 
Страницы: 1
Наверх