☰

Excel：根据范围内的查询显示值列表

tag-icon • tag-icon microsoft-excel worksheet-function query
Excel：根据范围内的查询显示值列表

我有一张这样的桌子：

a    |    tomato
b    |    pear
c    |    tomato
d    |    pear

我想创建一个公式，让我选择哪些行包含西红柿，哪些行包含梨。因此，对于西红柿的查询，公式的结果将是“a，c”，对于西红柿梨的查询，公式的结果将是“b，d”。

这可以在 Excel 中完成吗 (如果可以，怎么做？)

谢谢你！

答案1

通过使用 Excel 2016 中引入的函数，无需任何 VBA 即可完成此操作。TEXTJOIN()如果您没有该版本的 Excel，则可以安装 poly-fill UDF。我在本答案的末尾提供了一个基本的。

工作表截图

数组——在中输入以下公式E2：

{=TEXTJOIN(", ",TRUE,IFERROR(INDEX(A1:A5,N(IF(1,SMALL(IFERROR(1/(1/((B1:B5=D2)*ROW(B1:B5))),FALSE),ROW(INDEX(E:E,1):INDEX(E:E,ROWS(B1:B5))))))),""))}

美化后的公式如下：

{=
TEXTJOIN(
  ", ",
  TRUE,
  IFERROR(
    INDEX(
      A1:A5,
      N(IF(1,
        SMALL(
          IFERROR(1/(1/((B1:B5=D2)*ROW(B1:B5))),FALSE),
          ROW(INDEX(E:E,1):INDEX(E:E,ROWS(B1:B5)))
        )
      ))
    ),
    ""
  )
)}

笔记：

  • 美化的公式确实可以起作用。


TEXTJOIN()我的poly-fill UDF 版本：

'============================================================================================
' Module     : <any standard module>
' Version    : 0.1.1
' Part       : 1 of 1
' References : Optional - Microsoft VBScript Regular Expressions 5.5   [VBScript_RegExp_55]
' Source     : https://superuser.com/a/1331555/763880
'============================================================================================
Public Function TEXTJOIN( _
                                     ByRef delimiter As String, _
                                     ByRef ignore_empty As Boolean, _
                                     ByRef text1 As Variant _
                        ) _
       As String
       Dim ƒ As Excel.WorksheetFunction: Set ƒ = Excel.WorksheetFunction

  Const DELIMITER_ As String = "#"
  Const PATTERN_ As String = "^(?:#)+|(?:#)+$|(#){2,}"

  Static rexDelimiterEscaper As Object ' VBScript_RegExp_55.RegExp ' ## Object
  Static rexEmptyIgnorer As Object ' VBScript_RegExp_55.RegExp ' ## Object
  If rexEmptyIgnorer Is Nothing _
  Then
    Set rexEmptyIgnorer = CreateObject("VBScript.RegExp") ' New VBScript_RegExp_55.RegExp ' ## CreateObject("VBScript.RegExp")
    With rexEmptyIgnorer
      .Global = True
      .Pattern = PATTERN_ ' Replacement = "$1"
    End With
    Set rexDelimiterEscaper = CreateObject("VBScript.RegExp") ' New VBScript_RegExp_55.RegExp ' ## CreateObject("VBScript.RegExp")
    With rexDelimiterEscaper
      .Global = True
      .Pattern = "(.)" ' Replacement = "\$1"
    End With
  End If

  Dim varText1 As Variant
  Select Case TypeName(text1)
    Case "Range":
      varText1 = ƒ.Transpose(text1.Value2)
      If text1.Rows.Count = 1 Then
        varText1 = ƒ.Transpose(varText1)
        If text1.Columns.Count = 1 Then varText1 = Array(varText1)
      End If
    Case "Variant()":
      On Error Resume Next
        If LBound(text1, 2) <> LBound(text1, 2) Then
          varText1 = text1
        Else
          varText1 = ƒ.Transpose(text1)
        End If
      On Error GoTo 0
    Case Else:
      varText1 = Array(text1)
  End Select
  If ignore_empty _
  Then
    With rexEmptyIgnorer
      .Pattern = Replace(PATTERN_, DELIMITER_, rexDelimiterEscaper.Replace(delimiter, "\$1"))
      TEXTJOIN = .Replace(Join(varText1, delimiter), "$1")
    End With
  Else
    TEXTJOIN = Join(varText1, delimiter)
  End If

End Function

笔记：

  • 这是不是适当的填充：
    • 前两个参数不是可选的；
    • 如果你不想使用分隔符，你必须传递一个空字符串作为第一个参数。
    • 仅允许一个其他（也是必需的）参数。
  • 你可以将任何东西传递给第三个参数，除了多维数组/范围。这样做会导致#VALUE!错误。
  • 它应该非常快，特别是对于大量输入，因为它不使用任何循环。如果你不忽略空值，它会非常快。忽略它们会更慢，因为还必须使用几个正则表达式和额外的字符串操作。

答案2

用户定义函数可以提供帮助。

假设工作表上有下列数据：

  |  A  |  B
--+-----+---------
1 |  a  |  tomato
2 |  b  |  pear
3 |  c  |  tomato
4 |  d  |  pear

打开 VBA 编辑器 (Alt-F11)。插入新模块 (插入 - 模块)。将以下代码插入其中：

Public Function GetValues(rngSource As Range, strValue As String) As String
Dim i As Integer
If rngSource.Columns.Count <> 2 Then
    GetValues = "#ERROR - Source range must have 2 columns!"
    Exit Function
End If
For i = 1 To rngSource.Rows.Count
    If rngSource.Cells(i, 2) = strValue Then
        GetValues = GetValues & "," & rngSource.Cells(i, 1)
    End If
Next
GetValues = Mid(GetValues, 2)
End Function

关闭 VBA 编辑器。

跳转到工作表。在 D1 单元格中插入值“番茄”。

选择 E1 单元格。在其中插入以下公式：

=GetValues(A1:B4;D1)

E1 单元格值为 'a,c'。将 D1 值更改为 'pear' - E1 值将发生变化。将 D1 值更改为 'apple' - E1 值将更改为空字符串。

相关内容