[Excel] Autofilter Formel Aggregat

[schauan]

Zuweilen steht die Aufgabe, gefilterte Daten in einen anderen Tabellenbereich zu übernehmen.

Im Excel 365 gibt es neue Möglichkeiten mit der Funktion FILTER. Diese ist leider in älteren und auch in der aktuellen Desktop-Version 2019 nicht enthalten. Für den Austausch müsste man demzufolge auf eine andere Formellösung zurückgreifen.

Im Netz findet man zuweilen Formellösungen für den Autofilter. Diese basieren oft auf den eingeschränkten Möglichkeiten älterer Excelversionen vor 2010.

Seit 2010 bietet die AGGREGAT - Funktion in Verbindung mit der INDEX - Funktion alternative Möglichkeiten zum Filtern nach einem oder mehreren Kriterien. Hier habe ich ein Beispiel für eine einfache Filterung.

In den Spalten A und B habe ich eine Datenliste, aus der ich meine Daten extrahieren will. In D2 habe ich (m)ich dazu eingetragen. In Spalte E habe ich eine zum Filtern passende Funktion eingetragen - hier nur für E2 angezeigt. De Funktion kann nach Eingabe so weit erforderlich nach unten gezogen werden.

Zu den Parametern in der verwendeten AGGREGAT-FUNKTION:

15 entspricht der Funktion KKLEINSTE
6  steht für ignorieren von Fehlerwerten

ZEILE($A$2:$A$10)
ist das Array, in dem die Daten stehen
-1
ist ein Zeilenversatz da hier im Beispiel die Arrayangabe in Zeile 2 beginnt und die Auswertung aber mit dem ersten Element beginnen soll.

/($A$2:$A$10=$D$2)
ist die Angabe des Vergleichs- / Filterwertes und des Bereichs, in dem der Filterwert verglichen wird
Hier können z.B. auch mehrere Filter für verschiedene Spalten gesetzt werden, z.B. /(Bereich2=Filter2)/(Bereich3=Filter3)
ZEILE(A1)
ist der Index für KKLEINSTE

Hinweis zum Array ZEILE($A$2:$A$10) und dem Subtrahend (Zeilenversatz) 1
Das Array kennzeichnet genau genommen hier den Zeilenumfang / die Zeilenanzahl des Bereichs, in dem die Daten stehen. Ich könnte stattdessen auch ZEILE($X$66:$X$74)-65 schreiben - der Zeilenversatz ist dann also auch entsprechend größer - oder ZEILE($A$1:$A$9) und das dann ohne Zeilenversatz.
Die Angabe ZEILE($A$2:$A$10) - 1 erhöht aber m.E. die Nachvollziehbarkeit der Formel. Ich muss weder schauen, was in $X$66 steht noch grübeln, ob bei $A$1:$A$9 auch Zeile 10 berücksichtigt wird ... Dass hier im Beispiel die Überschrift nicht in die Formel einbezogen ist, soll zeigen, dass man mit dieser Formel sehr einfach auch Bereiche betrachten kann, die nicht in Zeile 1 beginnen.

Arbeitsblatt mit dem Namen 'Tabelle3'
	A	B	C	D	E
1	Wer	Wann		Wer	Wann
2	ich	01. Mrz		ich	01. Mrz
3	du	02. Mrz			04. Mrz
4	er	03. Mrz			07. Mrz
5	ich	04. Mrz
6	du	05. Mrz
7	er	06. Mrz
8	ich	07. Mrz
9	du	08. Mrz
10	er	09. Mrz

Zelle	Formel
E2	=WENNFEHLER(INDEX(B$2:B$10;AGGREGAT(15;6;ZEILE($A$2:$A$10)-1/($A$2:$A$10=$D$2);ZEILE(A1)));"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg