☰

Filtern einer Tabelle anhand von Informationen in Zellen außerhalb der Tabelle

tag-icon • tag-icon microsoft-excel
Filtern einer Tabelle anhand von Informationen in Zellen außerhalb der Tabelle

Ich habe eine Tabelle, die Informationen aus mehreren verschiedenen Quellen in meiner Arbeitsmappe zusammenfasst. Es gibt 4 Kategorien, nach denen die Tabelle regelmäßig gefiltert wird und die von Benutzern verwendet werden, die nicht so gut mit Excel umgehen können.

Ich möchte einen Filterbereich mit Listen einrichten, die die folgende Tabelle filtern. Der Screenshot zeigt das vorgeschlagene Setup mit Listenwerten in den Zellen B4, C4, D4, E4und die folgende Tabelle wird basierend auf den eingegebenen Werten gefiltert.

Weiß jemand, wie man dies oder etwas Ähnliches erreicht?

Bildbeschreibung hier eingeben
(Klicken Sie auf das Bild, um es zu vergrößern)

Antwort1

Sie meinen, Sie möchten nach einem bestimmten Zellenwert filtern, anstatt mit der integrierten Tabellenfilterung zu filtern?

Nun, Sie können den Autofilter mit einem Makro aufrufen und den Wert der Zellen als Kriterium verwenden.

Sie können das Makro in das Blatt einfügen und es jedes Mal automatisch aufrufen lassen, wenn eine der relevanten Zellen mit einem Worksheet_ChangeUnterelement geändert wird.

Es gibt viele Möglichkeiten, dies zu schreiben, aber wir müssen angeben, welche Tabelle wir filtern möchten, und wir müssen auch angeben, welche Zeile gefiltert werden soll.

Dies ist der Code, den ich für mein Beispiel in das Blatt eingefügt habe (indem ich mit der rechten Maustaste auf die Blattregisterkarte geklickt und „Code anzeigen“ ausgewählt habe):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tabl As String, i As Long, C as Range
tabl = "Table1" 'Name of the Table
If Not Application.Intersect(Range(Target.Address), Range("B4:E4")) Is Nothing And Target.Count < 5 Then
    For Each C In Target
        tCol = Range(tabl & "[" & C.Offset(-1).Value2 & "]").Column
        If C.Value2 = "" Then
            ListObjects(tabl).Range.AutoFilter Field:=tCol
        Else
            ListObjects(tabl).Range.AutoFilter Field:=tCol, Criteria1:=C.Value2
        End If
    Next C
End If
End Sub

Ergebnis:

Bildbeschreibung hier eingeben

Bearbeiten
Schleife hinzugefügt, um alle Filter auf einmal löschen zu können.

Dieser Code erfordert, dass Ihre Überschriften in der Eingabezelle und in der Tabelle identisch sind, sonst wird ein Fehler ausgegeben.

-

Worksheet_Changewird aufgerufen, wenn Zellen im Arbeitsblatt geändert werden.
Dies wird häufig in Verbindung mit verwendet,
If Not Application.Intersect(Range(Target.Address), Range("")) Is Nothing Then
um die Ausführung des Subs zu unterbinden, sofern nicht bestimmte Bereiche als Ziel ausgewählt werden.

Eine Einschränkung Target.Countist auch eine gute Idee, um zu verhindern, dass der Code abstürzt. Oftmals möchten Sie ihn auf ein einzelnes Ziel beschränken, es sei denn, Sie möchten die Auswahl schleifen, wie ich es in diesem Fall getan habe.

Wenn Sie Dropdown-Listen mit Optionen haben möchten, gibt es hier einen VBA-Ansatz:Excel: Entfernen von Duplikaten mit Datenvalidierung

Antwort2

Ich möchte eine Array-Formel (CSE) vorschlagen, mit der Sie mehrere Zeilen basierend auf wenigen Kriterien extrahieren können.

Bildbeschreibung hier eingeben

Wie es funktioniert:

  • Ich gehe davon aus, dass die Quelldaten im Bereich liegen A2:E10.
  • Kriterienbereich ist A16:E16.
  • Geben Sie diese Formel in Zelle ein A20und beenden Sie mit Strg+Umschalt+Eingabe, füllen Sie rechts und dann nach unten aus.

{=IFERROR(INDEX($A$2:$E$10,SMALL(IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})=COUNTA($A$16:$E$16),MATCH(ROW($A$2:$A$10),ROW($A$2:$A$10)),""),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")}

Notiz:

  • Wenn die Kriterienzelle/n leer sind, gibt diese Formel alle Datensätze zurück.

Bildbeschreibung hier eingeben

  • Sie können ein oder mehrere Kriterien zum Filtern verwandter Datensätze festlegen.

Bildbeschreibung hier eingeben

Lassen Sie mich den Mechanismus des Befehls erklären.

  • MMULT functionkann nicht mit booleschen Werten arbeiten, daher muss die Formel das Array mit 1 multiplizieren, damit es funktioniert.

MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})

wird,

MMULT({0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0},{1;1;1;1;1;1;1})

und kehrt zurück,

{0;0;1;0;0;2;1;1;2;1}

Und,

MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16)

wird,

{0;0;1;0;0;2;1;1;2;1}=COUNTA($A$16:$E$16)

wird,

{0;0;1;0;0;2;1;1;2;1}=2

und kehrt zurück,

{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.

IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")

wird,

IF({FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")

  • KLEINDie Funktion ruft die k-te kleinste Zahl in einem Array ab.

  • INDEXDie Funktion gibt einen Wert aus einem Zellbereich oder Array zurück, basierend auf einer Zeilen- und Spaltennummer.

verwandte Informationen