Excel: mostra a lista de valores com base na consulta no intervalo

Question

Eu tenho uma tabela assim:

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

Eu gostaria de criar uma fórmula que me permita selecionar quais linhas contêm tomate e quais contêm pêra. Assim, o resultado da fórmula seria "a, c" para a consulta de tomate e "b, d" para a consulta de pêra de tomate.

isso pode ser feito no Excel (e se sim, como assim?)

Obrigado!

    

Answer 1

Isso pode ser feito sem qualquer VBA usando a função TEXTJOIN() introduzida no Excel 2016. Se você não tiver essa versão do Excel, poderá instalar uma UDF de preenchimento de polimento. Eu forneci um básico no final desta resposta.

Matriz:insiraaseguintefórmulaemE2:

{=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))))))),""))}

A fórmula prettificada é a seguinte:

{=
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)))
        )
      ))
    ),
    ""
  )
)}

Notas:

• A fórmula prettificada realmente funciona se inserida.

Minha versão do UDF de% de preenchimento de TEXTJOIN() :

'============================================================================================
' 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 = "\"
    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, "\"))
      TEXTJOIN = .Replace(Join(varText1, delimiter), "$1")
    End With
  Else
    TEXTJOIN = Join(varText1, delimiter)
  End If

End Function

Notas:

• Isso não é um preenchimento de polietileno adequado:
  • Os dois primeiros argumentos não são opcionais;
  • Se você não deseja usar um delimitador, você deve passar uma string vazia como o primeiro parâmetro.
  • Existe apenas um outro argumento (também obrigatório) permitido.
• Você pode passar qualquer coisa para o terceiro argumento, exceto um array / intervalo de várias dimensões. Isso resultará em um erro #VALUE! .
• Deve ser muito rápido, especialmente para grandes entradas, pois não usa loops. Se você não estiver ignorando valores vazios, será muito rápido. Ignorá-los será mais lento como um par de expressões regulares e uma manipulação extra de string também deve ser usada.

Answer 2

A função definida pelo usuário pode ajudar.

Imagine que você tenha os próximos dados na planilha:

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

Abra o editor de VBA (Alt-F11). Inserir novo módulo (Inserir - Módulo). Insira o código abaixo:

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

Feche o editor do VBA.

Ir para a planilha. Insira um valor "tomate" na célula D1.

Selecione a célula E1. Insira a fórmula abaixo:

=GetValues(A1:B4;D1)

O valor da célula E1 é 'a, c'. Altere o valor de D1 para 'pear' - o valor de E1 será alterado. Altere o valor de D1 para 'apple' - o valor de E1 será alterado para string vazia.