基于条件的EXCEL返回值范围 [英] EXCEL return range of values based on criteria

问题描述

我有一个VBA函数，仅当满足该表中的某些条件时，我才想传递该数据的自定义范围(而不是整个表的列范围).例如:

I have a VBA function that I would like to pass a custom range of data (As opposed to a full table column range) only when certain criteria within that table is met. For example:

          Table_1                            Table_2
    A        B         C                A          B
1 Policy    Data     Status     |   1 Policy    Function
  --------------------------    |     -------------------
2   AA      25      approved    |   2   AA      [25, 35]
3   AA      19      unapproved  |   3   BB      [16]
4   BB      16      approved    |
5   CC      27      approved    |
6   CC      30      unapproved  |
7   AA      35      approved

在表2的单元格B2中，我想返回表1中所有Data值的范围，其中Policy = AA和Status = approved.随后，在单元格B3中，其中Policy = BB和Status = approved等的值的范围.

In Table2, cell B2, I would like to return a range of all Data values from Table1 where Policy = AA and Status = approved. Subsequently, in cell B3 a range of values where Policy = BB and Status = approved etc..

使用公式是否可以?

推荐答案

如果您具有Office 365 Excel或更高版本，则可以将TEXTJOIN用作数组公式:

If you have Office 365 Excel or later you can use TEXTJOIN as an Array Formula:

="[" & TEXTJOIN(",",TRUE,IF(($A$2:$A$7=F2)*($C$2:$C$7="approved"),$B$2:$B$7,"")) & "]"

作为数组公式，退出编辑模式时需要使用Ctrl-Shift-Enter而不是Enter进行确认.

Being an Array Formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

如果您没有Office 365 Excel或更高版本，则这里是可以执行您想要的操作的UDF:

If you do not have Office 365 Excel or later, here is a UDF that will do what you want:

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

您将其称为类似于SUMIFS:

You would call it similar to SUMIFS:

=TEXTJOINIFS(B:B,",",A:A,F2,C:C,"approved")

它仅适用于列而不适用于行.

It only works with columns not rows.

这篇关于基于条件的EXCEL返回值范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！