соединить строки с условием в Excel

Question

Скажем, у меня есть такой индекс:

sheet1

1
2
3
4
5
6
...

И данные соединения представляют отношение между этими точками, например, первые 2 строки означают, что point_1 связана с point_75 и point_78 :

sheet2

1   75
1   78
2   44
3   45
3   65
4   39
4   63
5   49
5   50
6   59
...

И я хотел бы использовать некоторое выражение для представления этого в sheet1 как:

1   75,78
2   44
3   45,65
...

Есть ли способ сделать это?

Answer 1

Сначала введите следующую пользовательскую функцию в стандартный модуль:

Public Function Konnect(r1 As Range, r2 As Range) As String
    Dim ary, i As Long
    Dim v As Variant

    ary = r2
    v = r1.Value
    Konnect = ""

    For i = LBound(ary, 1) To UBound(ary, 1)
        If ary(i, 1) = v Then Konnect = Konnect & "," & ary(i, 2)
    Next i

    If Len(Konnect) <> 0 Then Konnect = Mid(Konnect, 2)
End Function

Затем в ячейку Листа B1 введите:

=Konnect(A1,Sheet2!A$1:B$10)

и скопируйте вниз:

Естественно, вы изменили бы 10, чтобы соответствовать фактическому размеру таблицы на Sheet2.

EDIT # 1:

Пользовательские функции (UDF) очень просты в установке и использовании:

1. ALT-F11 открывает окно VBE
2. ALT-I ALT-M открывает новый модуль
3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить UDF:

1. откройте окно VBE, как указано выше
2. очистить код
3. закройте окно VBE

Чтобы использовать UDF из Excel:

=Konnect(A1,Sheet2!A$1:B$10)

Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

а подробности о UDF смотрите в:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Макросы должны быть включены, чтобы это работало!