答案1
代码:
Function MakeCompact(times As Range, shedules As Range, letter As String) As String
Dim i As Integer, n As Integer
If times.Cells.Count <> shedules.Cells.Count + 1 Then
MakeCompact = "Error. Wrong source data."
Exit Function
End If
n = times.Cells.Count
MakeCompact = ""
For i = 1 To n
If letter = shedules.Cells(1, i).Value Then
If Right(MakeCompact, 1) <> "-" Then
MakeCompact = MakeCompact & "," & times.Cells(1, i).Value & "-"
End If
Else
If Right(MakeCompact, 1) = "-" Then
MakeCompact = MakeCompact & times.Cells(1, i).Value
End If
End If
Next
MakeCompact = Mid(MakeCompact, 2)
End Function
用法:
创建表:
A B C D E F G
1 name 1 2 3 4 5 6
2 bob m m b m m
3 fred b m e e
4
5 m b e
6 bob
7 fred
范围 A1:G7 是源数据(第 1 行是标题)。
范围 A5:D7 是我们要填充的表格。
在 B6 中插入公式:
=MakeCompact($B$1:$G$1;$B2:$F2;B$5)
水平和垂直拖动它以填充单元格。
我认为这个想法足以创建您的任务解决方案。
PS. 请注意 - 计划时间内会有一个附加栏。
PPS。调试后,使函数变为 Volatile。