Letzten Wert bei gefilterten Excel-Daten ermitteln
Schritt-für-Schritt-Anleitung
-
Hilfsspalte einfügen: Erstelle eine Hilfsspalte neben dem Datenbereich, den du filtern möchtest. Angenommen, deine Daten befinden sich im Bereich A2:D100, füge eine neue Spalte E hinzu.
-
Formel für die Hilfsspalte: In der Hilfsspalte (z.B. E2) gib folgende Formel ein:
=N(ZELLE.ZUORDNEN(17;INDIREKT("ZS(-1)";0*JETZT()))<>0)
Kopiere diese Formel bis zur letzten Zeile deiner Daten (E100).
-
FILTER-Funktion nutzen: Um den letzten Wert zu ermitteln, setze in einer neuen Zelle die folgende Formel ein:
=INDEX(A:A;SUMMENPRODUKT(MAX(E2:E100*ZEILE(E2:E100))))
-
Filter anwenden: Setze einen Autofilter auf deinen Datenbereich (A1:D100) und wähle die gewünschten Filterkriterien aus. Die Formel in Schritt 3 gibt dir nun den letzten Wert der gefilterten Daten zurück.
Häufige Fehler und Lösungen
-
Leere Zellen im gefilterten Bereich: Wenn die letzte Zelle, die du angezeigt bekommst, leer ist, wird möglicherweise der vorherige Wert angezeigt. Dies geschieht, wenn die Formel die Leerzelle nicht berücksichtigt. Achte darauf, dass du bei der Auswahl der Spalten in der Formel die richtige Spalte für das TEILERGEBNIS verwendest.
-
Formel gibt NULL zurück: Wenn du NULL erhältst, kann das daran liegen, dass die letzte Zeile tatsächlich leer ist und kein Wert vorhanden ist. Überprüfe, ob es echte Leerzellen oder nur leere Strings (="" ) gibt.
Alternative Methoden
Eine andere Möglichkeit, den letzten Wert bei gefilterten Daten zu ermitteln, ist die Verwendung von Array-Formeln ohne Hilfsspalte. Du kannst folgende Formel direkt in eine Zelle eingeben:
{=INDEX(B:B;MAX(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(1:100)))*ZEILE(1:100)))}
Diese Formel zeigt den letzten Wert aus Spalte B an, basierend auf den gefilterten Ergebnissen der Spalte A.
Praktische Beispiele
Stell dir vor, du hast eine Liste von Verkaufszahlen in Spalte A und das zugehörige Datum in Spalte B. Wenn du nur die Verkaufszahlen für einen bestimmten Monat filtern möchtest, kannst du die oben genannten Schritte verwenden, um den letzten Verkaufswert für diesen Monat zu erhalten.
Wenn du beispielsweise die Verkaufszahlen für Mai filterst, zeigt dir die Formel den letzten Verkaufswert aus Mai an, unabhängig davon, ob es eine echte Leerzeile gibt oder nicht.
Tipps für Profis
-
Filter Excel bearbeiten: Du kannst deine Filter jederzeit anpassen, um verschiedene Ansichten deiner Daten zu erhalten. Achte darauf, dass du die Hilfsspalte entsprechend aktualisierst, wenn du neue Daten hinzufügst.
-
Tausendertrennzeichen entfernen: Wenn du mit Zahlen arbeitest, kann es hilfreich sein, die Tausendertrennzeichen zu entfernen, um die Berechnungen zu vereinfachen. Nutze dazu die Funktion TEXT, um das Format zu ändern.
-
Teilergebnis auf Englisch umstellen: Wenn du Excel auf Englisch nutzt, kannst du die Funktion SUBTOTAL verwenden, um ähnliche Ergebnisse zu erzielen wie mit TEILERGEBNIS in der deutschen Version.
FAQ: Häufige Fragen
1. Wie kann ich den Filter in Excel bearbeiten?
Du kannst den Filter einfach anpassen, indem du auf den Dropdown-Pfeil in der Kopfzeile deiner Tabelle klickst und die gewünschten Kriterien auswählst.
2. Warum wird der letzte Wert nicht korrekt angezeigt?
Das kann daran liegen, dass die Formel nicht die richtige Spalte für das TEILERGEBNIS verwendet oder dass echte Leerzellen vorhanden sind, die nicht berücksichtigt werden. Überprüfe deine Formeln und die gefilterten Bereiche.