Excel: pokaż listę wartości na podstawie zapytania w zakresie

Mam taki stół:

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

Chciałbym stworzyć formułę, która pozwoli mi wybrać, które wiersze zawierają pomidory, a które zawierają gruszkę. więc wynikiem wzoru będzie „a, c” dla zapytania o pomidor i „b, d” dla zapytania o gruszkę pomidorową.

czy można to zrobić w programie Excel (a jeśli tak, to w jaki sposób?)

Dziękuję Ci!

Można to osiągnąć bez VBA przy użyciu TEXTJOIN() funkcja wprowadzona w programie Excel 2016. Jeśli nie masz tej wersji programu Excel, możesz zainstalować UDF z wypełnieniem. Dostarczyłem podstawowy na końcu tej odpowiedzi.

Array - wprowadź następującą formułę w 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))))))),""))}

Wstępnie sformułowana formuła jest następująca:

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

Uwagi:

• Poprawiona formuła działa, jeśli zostanie wprowadzona.

Moja wersja TEXTJOIN() poliwęglanowy 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

Uwagi:

• To jest nie poprawne wypełnienie:
  • Pierwsze dwa argumenty nie są opcjonalne;
  • Jeśli nie chcesz używać ogranicznika, ty musi przekazać pusty ciąg jako pierwszy parametr.
  • Dopuszczalny jest tylko jeden inny (również wymagany) argument.
• Możesz przekazać cokolwiek dla trzeciego argumentu, z wyjątkiem wielowymiarowa tablica / zakres. Spowoduje to a #VALUE! błąd.
• Powinien być bardzo szybki, szczególnie dla dużych wejść, ponieważ nie używa żadnych pętli. Jeśli nie ignorujesz pustych wartości, będzie to błyskawiczne. Ignorowanie ich będzie wolniejsze, ponieważ trzeba użyć kilku wyrażeń regularnych i dodatkowej manipulacji ciągami.

Pomocna może być funkcja zdefiniowana przez użytkownika.

Wyobraź sobie, że masz kolejne dane w arkuszu:

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

Otwórz edytor VBA (Alt-F11). Włóż nowy moduł (Insert - Module). Wstaw poniższy kod do niego:

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

Zamknij edytor VBA.

Przejdź do arkusza roboczego. Wstaw wartość „pomidor” do komórki D1.

Wybierz komórkę E1. Wstaw poniższą formułę:

=GetValues(A1:B4;D1)

Wartość komórki E1 to „a, c”. Zmień wartość D1 na „gruszka” - wartość E1 zmieni się. Zmień wartość D1 na „jabłko” - wartość E1 zmieni się na pusty ciąg.