Wenn Sie in Excel Daten in einer Tabelle mit einer Datumsspalte auswerten möchten, sind manchmal nur die letzten Datensätze von Interesse. In diesem Tipp erfahren Sie, wie Sie mit der Kombination neuer Verweisfunktionen sich bei der Auswertung nur auf die letzten x-Datensätze konzentrieren können

Ausgangsbeispiel

In der folgenden Tabelle befinden sich Bestelldaten, die nicht nach dem Bestelldatum in der ersten Spalte sortiert sind:

Excel-Tabelle mit Bestellungen

In diesem Beispiel sollen die letzten 5 Bestellungen näher analysiert werden.

Die letzten fünf Datensätze einer Tabelle abrufen

Mit der Funktion ÜBERNEHMEN können Sie u.a. die ersten oder letzten Datensätze in der Tabelle abrufen. Die Tabelle hat den Namen Bestellungen.

Mit der folgenden Formel werden die 5 letzten Bestellungen, die sich in den Zeilen 25 bis 29 befinden, abgerufen:

=ÜBERNEHMEN(Bestellungen;-5)

Die Formel kennt 3 Parameter:

• Mit dem ersten Parameter geben Sie den Zellbereich bzw. die Tabelle an, aus der die Zeilen und Spalten genommen werden. In unserem Beispiel ist es die Tabelle Bestellungen.
• Mit dem zweiten Parameter geben die Anzahl der Zeilen an, die aus der Tabelle übernehmen möchten. Wenn Sie eine negative Zahl eingeben werden, werden statt der ersten die letzten Datensätze verwendet. Mit der Zahl -5 werden die letzten 5 Bestellungen übernommen.
• Mit dem dritten Parameter können Sie die Anzahl der zu übernehmenden Spalten festlegen. Wen Sie den Parameter nicht angeben, werden alle Spalten übernommen.

Nach der Formatierung der Werte in der ersten Spalte als Datumswerte wird ein Überlaufbereich mit 5 Zeilen erzeugt:

Letzte Bestellungen unsortiert

Die fünf aktuellen Bestellungen abrufen

In dem oberen Beispiel wurden nicht die 5 aktuellen Bestellungen unter Berücksichtigung des Datums genommen. Um die 5 aktuellen Bestellungen abzurufen, müssen Sie entweder die Tabelle nach dem Bestelldatum sortieren oder in der Formel die vorherige Sortierung der Datensätze vornehmen.

Die 5 aktuellen Bestellungen können Sie unabhängig von der Sortierung in der Tabelle mit der folgenden Formel abrufen:

=ÜBERNEHMEN(SORTIERENNACH(Bestellungen;Bestellungen[Datum];-1);5)

Vor dem Abruf der Abruf mit der Funktion ÜBERNEHMEN wird die Tabelle Bestellungen nach der Spalte Datum absteigend sortiert. Die absteigende Sortierung erreichen Sie durch den zweiten Parameter der Funktion SORTIERENNACH. Er hat den Wert -1.

Aus dieser sortierten Tabelle werden mit der Funktion ÜBERNEHMEN die ersten 5 Datensätze genommen.

Im Tabellenblatt sieht das Ergebnis so aus:

Letzte Bestellungen absteigend sortiert

Ein ähnliches Ergebnis erzielen Sie, indem die Tabelle zunächst aufsteigend sortieren und dann die letzten 5 Bestellungen herausziehen. Die Formel lautet dafür:

=ÜBERNEHMEN(SORTIERENNACH(Bestellungen;Bestellungen[Datum];1);-5)

Das Ergebnis sieht so aus:

Letzte Bestellungen aufsteigend sortiert

Wenn Sie nur einzelne Spalten der Tabelle abrufen möchten, könne Sie zwei verschiedene Funktionen nutzen. Mit der Funktionen ÜBERNEHMEN können Sie auf eine bestimmte Anzahl von Spalten von links oder rechts gesehen beschränken:

Mit der folgenden Formel rufen Sie nur die Werte in den ersten beiden Spalten der Tabelle ab:

=ÜBERNEHMEN(SORTIERENNACH(Bestellungen;Bestellungen[Datum];-1);5;2)

Sie erreichen dies durch Eingabe des Wertes 2 für den dritten Parameter der Funktion ÜBERENEHMEN.

Letzte Bestellungen absteigend sortiert mit 2 linken Spalten

Falls Sie die Werte in den Spalten Datum und Umsatz abrufen möchten, können Sie die Funktion SPALTENWAHL einsetzen. Sie geben ab den zweiten Parameter die Spaltennummern an, deren Werte Sie übernehmen möchten. In unserem Beispiel lautet die Funktion:

=SPALTENWAHL(ÜBERNEHMEN(SORTIERENNACH(Bestellungen;Bestellungen[Datum];-1);5);1;4)

Die Parameter 1 steht für die erste Spalte Datum. Der zweite Parameter 4 stehen für die vierte Spalte Umsatz.

Das Ergebnis sieht so aus:

Letzte Bestellungen absteigend sortiert mit 2 Spalten

Kunden der letzten 5 Bestellungen

Als nächstes Beispiel soll ermittelt, welche Kunden zuletzt bestellt haben. Dabei soll jeder Kunde nur einmal in der Liste erscheinen. Die Formel lautet folgendermaßen:

=SORTIEREN(EINDEUTIG(SPALTENWAHL(ÜBERNEHMEN(SORTIERENNACH(Bestellungen;Bestellungen[Datum];-1);5);2)))

Von innen nach außen betrachtet werden folgenden Funktionen abgerufen:

• Ausgabe der letzten 5 Bestellungen, absteigend sortiert nach dem Datum mit den Funktion ÜBERNEHMEN Und SORTIERENNACH
• Auswahl der Spalte Kunde mit der Funktion SPALTENAUSAWHL und dem Parameter 2 für die zweite Spalte
• Herausfiltern aller doppelten Kundennamen mit der Funktion EINDEUTIG
• Aufsteigende Sortierung der Kunden nach ihrem Namen mit der Funktion SORTIEREN

Im Tabellenergebnis erscheint die folgende Auflistung:

Letzte Bestellungen absteigend sortiert nach Kundennamen

Durchschnittlicher Umsatz der letzten 5 Bestellungen

Im letzten Beispiel sollen nicht mehrere Werte, sondern mit dem durchschnittlichen Umsatz nur ein einziger Wert ermittelt werden. Die Formel lautet:

=MITTELWERT(SPALTENWAHL(ÜBERNEHMEN(SORTIERENNACH(Bestellungen;Bestellungen[Datum];-1);5);4))

Mit der Funktion SPALTENWAHL und dem Parameter 4 wird ein Bezug zu der vierten Spalte in der Tabelle hergestellt. Für die 5 Werte in dieser Spalte wird dann der Mittelwert gebildet.

Das Ergebnis lautet 293.

Da sich der Umsatz in der letzten Spalte der Tabelle sich befindet, können Sie auch die folgende Formel verwenden:

=MITTELWERT(ÜBERNEHMEN(SORTIERENNACH(Bestellungen;Bestellungen[Datum];-1);5;-1))

In der Formel wird mit dem Wert -1 für den dritten Parameter der Funktion ÜBERNEHMEN die rechte Spalte Umsatz adressiert.

Fazit

Mit einer Kombination mehrerer Verweisfunktionen wie beispielsweise SORTIERENNACH, ÜBERNEHMEN und SPALTENAUSWAHL können Sie flexibel Daten aus Tabellen extrahieren und auswerten.