Excel: Liste der Werte basierend auf der Abfrage im Bereich anzeigen

419
Haaid

Ich habe einen Tisch wie diesen:

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

Ich möchte eine Formel erstellen, mit der ich auswählen kann, welche Reihen Tomaten und welche Birnen enthalten. Das Ergebnis der Formel wäre also "a, c" für die Abfrage nach Tomaten und "b, d" für die Abfrage nach Tomatenbirne.

Kann das in Excel gemacht werden (und wenn ja, wie?)

Vielen Dank!

1
Es gibt keine Arbeitsblattfunktion, die (bedingt) die Zeichenfolgenwerte der Bereichszelle in einem Wert zusammenfasst. Sie können die Aufgabe mithilfe zusätzlicher Spalten oder mithilfe einer benutzerdefinierten Funktion (VBA-Code - die einfachste Lösung) lösen. Akina vor 5 Jahren 0
Hallo Akina, danke für deine Antwort. Können Sie erklären, was Sie unter "Verwendung zusätzlicher Spalten" verstehen? Denn wenn ich gerade eine Rendite bekommen könnte, zum Beispiel ein | c in zwei verschiedenen Spalten, dann kälte ich diese Zellen anschließend, um das gewünschte Ergebnis zu erhalten. Haaid vor 5 Jahren 0
@Akina Sie benötigen keine Funktion, die bedingt verkettet ist. Sie können die entsprechenden Werte mit einer Array-Funktion erzeugen und dann entweder `CONCAT () 'oder` TEXTJOIN ()' verwenden. Es sind keine zusätzlichen Spalten oder VBA erforderlich. (Es sei denn, Sie haben natürlich kein Excel 2016.) robinCTS vor 5 Jahren 0
* Sofern Sie nicht über Excel 2016 verfügen, natürlich. * Leider nur 2010 :( ... Akina vor 5 Jahren 0
@Akina Benutze einfach meine mitgelieferte UDF-Füllfunktion ;-) (Oder du kannst online nach anderen Versionen suchen.) Übrigens, ich bin nur 2007 :-P robinCTS vor 5 Jahren 0

2 Antworten auf die Frage

1
robinCTS

Dies kann ohne VBA mit der TEXTJOIN()in Excel 2016 eingeführten Funktion erreicht werden. Wenn Sie nicht über diese Version von Excel verfügen, können Sie eine UDF mit Poly-Fill installieren. Ich habe am Ende dieser Antwort ein grundlegendes gegeben.

Arbeitsblatt-Screenshot

Geben Sie die folgende Formel im Feld ein 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))))))),""))}

Die verettete Formel lautet wie folgt:

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

Anmerkungen:

  • Die verettete Formel funktioniert tatsächlich, wenn sie eingegeben wird.


Meine Version der 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 = "^(?:#)+|(?:#)+$|(#)"  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

Anmerkungen:

  • Dies ist keine richtige Poly-Füllung:
    • Die ersten beiden Argumente sind nicht optional.
    • Wenn Sie kein Trennzeichen verwenden möchten, müssen Sie als ersten Parameter eine leere Zeichenfolge übergeben.
    • Es ist nur ein weiteres (auch erforderliches) Argument zulässig.
  • Sie können alles für das dritte Argument übergeben, außer einem Array / Bereich mit mehreren Dimensionen. Dies führt zu einem #VALUE!Fehler.
  • Es sollte sehr schnell sein, besonders für große Eingänge, da keine Schleifen verwendet werden. Wenn Sie leere Werte nicht ignorieren, wird es blitzschnell. Das Ignorieren wird langsamer, da ein paar reguläre Ausdrücke verwendet werden müssen und eine zusätzliche Manipulation der Saiten erforderlich ist.
0
Akina

Benutzerdefinierte Funktionen können helfen.

Stellen Sie sich vor, Sie haben die nächsten Daten auf dem Arbeitsblatt:

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

Öffnen Sie den VBA-Editor (Alt-F11). Neues Modul einsetzen (Insert - Module). Fügen Sie den Code unten ein:

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

Schließen Sie den VBA-Editor.

Springe zum Arbeitsblatt. Geben Sie einen Wert "Tomate" in die Zelle D1 ein.

E1-Zelle auswählen. Fügen Sie die folgende Formel ein:

=GetValues(A1:B4;D1)

Der E1-Zellenwert lautet 'a, c'. Ändern Sie den D1-Wert in 'Birne' - der E1-Wert ändert sich. Ändern Sie den D1-Wert in 'Apfel' - der E1-Wert ändert sich in eine leere Zeichenfolge.

Verwandte Probleme