EXCEL - 在列表中查找一个值并返回多个相应的值

Question

我正在尝试在 Excel 中为我的时间表创建树遍历。我现在有 2 个列表，每个列表有 1006 个单元格长。第一个是前人，第二个是后继者。我正在尝试使用一组函数来显示多个结果。例如，如果我输入 3，我希望列出任务 3 的所有后续任务。到目前为止，我想出的代码是：

=IF(ISERROR(INDEX($A$1:$B$1006,SMALL(IF($A$1:$A$1006=$E$3,ROW($A$1:$A$1006)),ROW(1:1)),2)),"NO",INDEX($A$1:$B$1006,SMALL(IF($A$1:$A$1006=$E$3,ROW($A$1:$A$1006)),ROW(1:1)),2))

但是，当我输入前任时，它不会显示正确的后继。

预先感谢任何可以帮助我的人

Answer 1

您无法使用公式连接值（或者至少，我看不到一种简单的方法来做到这一点）。

您可以调用一个过程（更快但更具侵入性）：

Option Explicit

Sub Proc_ListPre()
Dim rData As Range, lLastrow As Long, i As Integer
Dim aValues() As Variant
Dim sFilter As String, sRes As String

'Ask for the value to filter to the user
sFilter = InputBox("Which predecessor do you want to analyse?", "Please type the predecessor you want")
If Len(sFilter) = 0 Then Exit Sub

'Define the range
'either use UsedRange (if only columns A and B are used)
'Set rData = ActiveSheet.UsedRange
'or use End(xlUp) if not
lLastrow = ActiveSheet.Range("a65536").End(xlUp).Row
Set rData = ActiveSheet.Range("A1:B" & lLastrow)
'Filter the predecessor with the criteria given in arg
rData.AutoFilter Field:=1, Criteria1:=sFilter

'Find the last row of the filtered data
lLastrow = ActiveSheet.Range("a65536").End(xlUp).Row
aValues = ActiveSheet.Range("A2:B" & lLastrow).Value
'Join the 2nd column of the array
'Join(WorksheetFunction.Index(aValues, 0, 2), ";") 'note that this doesn't work because index returns a 2D array
'Workaround to join the 2nd column
For i = 1 To UBound(aValues, 1)
    If Len(CStr(aValues(i, 2))) > 0 Then
        sRes = sRes & aValues(i, 2) & ";"
    End If
Next
sRes = Left(sRes, Len(sRes) - 1)
MsgBox sRes

ActiveSheet.AutoFilterMode = False
End Sub

或者使用您将在工作表中调用的公式，如 =ListPre(mypredecessor)

Function ListPre(ByVal sFilter As String)
Dim rData As Range, lLastrow As Long, i As Integer
Dim aValues() As Variant
Dim sRes As String

'Define the range
'either use UsedRange (if only columns A and B are used)
'Set rData = ActiveSheet.UsedRange
'or use End(xlUp) if not
lLastrow = ActiveSheet.Range("a65536").End(xlUp).Row
Set rData = ActiveSheet.Range("A1:B" & lLastrow)
aValues = ActiveSheet.Range("A2:B" & lLastrow).Value

'Join the 2nd column of the array
'Join(WorksheetFunction.Index(aValues, 0, 2), ";") 'note that this doesn't work because it returns a 2D array
'Workaround to join the 2nd column
For i = 1 To UBound(aValues, 1)
    If Len(CStr(aValues(i, 2))) > 0 And CStr(aValues(i, 1)) = sFilter Then
        sRes = sRes & aValues(i, 2) & ";"
    End If
Next
sRes = Left(sRes, Len(sRes) - 1)
ListPre = sRes
End Function