excel - 需要帮助在单个单元格中获取多个值，并在 excel 中满足条件

Question

我需要帮助才能在满足条件的单个单元格中获取值。
我想要在单个单元格中具有小于 95% 的值的违约者。
例如

Answer 1

如果有动态数组公式 FILTER 和 TEXTJOIN:

=TEXTJOIN(CHAR(10),TRUE,FILTER(A2:A7,E2:E7<.95))

确保输出单元格启用了自动换行。


如果有 TEXTJOIN 但没有 FILTER (Excel 2019)，则使用此数组公式:

=TEXTJOIN(CHAR(10),TRUE,IF(E2:E7<.95,A2:A7,""))

这需要在退出编辑模式时使用 Ctrl-Shift-Enter 而不是 Enter 来确认。

如果两者都没有，则将此代码放在标准模块中:

Function TEXTJOINIFS(rng As Range, delim As String, ParamArray arr() As Variant)
    Dim rngarr As Variant
    rngarr = Intersect(rng, rng.Parent.UsedRange).Value

    Dim condArr() As Boolean
    ReDim condArr(1 To Intersect(rng, rng.Parent.UsedRange).Rows.Count) As Boolean


    Dim i As Long
    For i = LBound(arr) To UBound(arr) Step 2
        Dim colArr() As Variant
        colArr = Intersect(arr(i), arr(i).Parent.UsedRange).Value
        Dim j As Long
        For j = LBound(colArr, 1) To UBound(colArr, 1)

            If Not condArr(j) Then
                Dim charind As Long
                charind = Application.Max(InStr(arr(i + 1), ">"), InStr(arr(i + 1), "<"), InStr(arr(i + 1), "="))
                Dim opprnd As String
                If charind = 0 Then
                    opprnd = "="
                Else
                    opprnd = Left(arr(i + 1), charind)
                End If
                Dim t As String
                t = """" & colArr(j, 1) & """" & opprnd & """" & Mid(arr(i + 1), charind + 1) & """"
                If Not Application.Evaluate(t) Then condArr(j) = True
            End If
        Next j
    Next i

    For i = LBound(rngarr, 1) To UBound(rngarr, 1)
        If Not condArr(i) Then
            TEXTJOINIFS = TEXTJOINIFS & rngarr(i, 1) & delim
        End If
    Next i

    TEXTJOINIFS = Left(TEXTJOINIFS, Len(TEXTJOINIFS) - Len(delim))

End Function

并这样称呼它:

=TEXTJOINIFS(A2:A7,CHAR(10),E2:E7,"<"&0.95)