Viele Anwender kennen das Problem. Man hat lange Tabellen mit vielen Rohdaten und möchte daraus nur die wichtigsten Datensätze in einer separaten Übersicht ausgeben: Die 10 Artikel mit den höchsten Umsätzen, die 20 langjährigsten Kunden oder die 5 Mitarbeiter mit der höchsten Telefonauslastung, um nur ein paar Beispiele zu nennen.
Natürlich gibt es verschiedene Möglichkeiten, solche Datensätze aus einer Gesamttabelle herauszufischen. Im heutigen Artikel stelle ich dir eine supereinfache Formellösung vor. Voraussetzung ist allerdings, dass Excel aus Microsoft 365 zum Einsatz kommt.
Und so geht’s:
Beispieldatei herunterladen
Die Aufgabenstellung
Für mein heutiges Beispiel greife ich auf Klimadaten vom Deutscher Wetterdienst zurück, genau genommen auf die Wetterdaten der Messstation am Flughafen München. Meine Tabelle enthält Temperaturen, Sonnenscheindauer und Niederschlagsmengen für 500 Tage. Aus dieser Tabelle sollen nun die Tage mit den höchsten Niederschlagsmengen in einer separaten Liste ausgegeben werden.
Beispieltabelle mit Wetterdaten
Natürlich könnten wir jetzt die Tabelle einfach nach der Niederschlagsmenge absteigend sortieren und dann „zu Fuß“ die gewünschten Datensätze herauskopieren. Oder eine kleine Abfrage mit Power Query erstellen. Wenn du jedoch Excel aus Microsoft 365 im Einsatz hast, dann zeige ich dir gleich eine schöne Formel, die genau das für uns automatisch erledigt.
Formellösung Teil 1
Ja, ich weiß, nicht jeder Anwender nutzt Microsoft 365. Aber die dort verfügbaren dynamischen Arrayfunktionen sind so genial und erleichtern die Arbeit auf so vielfältige Weise, dass man über einen Umstieg vielleicht doch mal nachdenken sollte. Und es gibt ja auch noch die Möglichkeit, das kostenlose Excel für das Web zu nutzen. Auch dort sind die vorgestellten Funktionen verfügbar. Wie man da drankommt, erfährst du in diesem Artikel: Excel völlig kostenlos? Wo gibt’s denn so was!
Die wichtigste Funktion, die in meinem Anwendungsbeispiel dabei zum Einsatz kommt, ist die noch relativ unbekannte ÜBERNEHMEN-Funktion.
Was macht diese Funktion? Sie gibt eine bestimmte Anzahl von Zeilen und/oder Spalten vom Anfang oder Ende einer Matrix aus:
=ÜBERNEHMEN(Matrix;Zeilen;[Spalten])
Das Argument „Matrix“ gibt den Bezug der Quelltabelle an. Dies kann ein normaler Zellbezug sein (z.B. A4:F15), ein benannter Bereich oder eben auch eine formatierte Tabelle.
Das Argumente „Zeilen“ bestimmt die Anzahl der Zeilen, die man ausgeben möchte. Ein positiver Wert gibt die entsprechende Anzahl vom Anfang der Quelltabelle aus, ein negativer Wert vom Ende her.
Das optionale Argument „Spalten“ macht das gleiche mit den Spalten.
Bei meinem Beispiel handelt es sich um eine formatierte („intelligente“) Tabelle mit dem Namen tblWetter.
Verborgene Talente in Excel: Als Tabelle formatieren
Ordnung ins Chaos bringen: Strukturierte Verweise
Um beispielsweise die ersten 5 Zeilen auszugeben, genügt folgende Formel:
=ÜBERNEHMEN(tblWetter;5)
Ausgabe der ersten 5 Zeilen
Da es sich um hier um eine dynamische Arrayfunktion handelt, muss die Formel nur in einer Zelle eingegeben werden und läuft dynamisch in so viele Zeilen und Spalten über, wie eben benötigt werden. Lediglich die Formatierungen werden nicht automatisch übernommen, daher werden in der ersten Spalte statt des Datums nur die numerischen Datumswerte angezeigt.
Um stattdessen die ersten 2 Spalten der letzten 5 Zeilen auszugeben, gibt man diese Formel ein:
=ÜBERNEHMEN(tblWetter;-5;2)
Damit man das Ergebnis besser erkennt, habe ich die komplette Spalte H als Datum formatiert.
Ausgabe der ersten 2 Spalten von den letzten 5 Zeilen
Das ist schon mal ziemlich praktisch, oder?
In unserem Fall reicht das aber noch nicht, denn wir wollen ja nicht einfach nur die ersten oder letzten Zeilen aus der Liste, sondern die mit den höchsten Niederschlagsmengen. Das heißt, wir müssen die Tabelle erst noch sortieren.
Formellösung Teil 2
Für diese Aufgabe bietet Excel 365 die gleichnamige SORTIEREN-Funktion:
=SORTIEREN(Matrix;[Sortierindex];[Sortierreihenfolge];[Nach_Spalte])
Über den Sortierindex gibt man die Spaltennummer an, nach der sortiert werden soll. In unserem Fall wäre das für den Niederschlag, also 6.
Bei der Sortierreihenfolge besagt 1 eine aufsteigende und -1 eine absteigende Sortierung. Lässt man die Angabe weg, wird aufsteigend sortiert.
Um also eine nach Niederschlagsmengen absteigend sortierte Wettertabelle zu erhalten, reicht diese Formel:
=SORTIEREN(tblWetter;6;-1)
Die nach den Niederschlägen absteigend sortierte Tabelle
Formellösung Teil 3
Jetzt müssen wir die beiden Funktionen nur noch kombinieren, um das gewünschte Ergebnis zu erhalten:
=ÜBERNEHMEN(SORTIEREN(tblWetter;6;-1);5)
Damit werden die ersten 5 Datensätze aus der nach Niederschlag absteigend sortierten Wettertabelle ausgegeben – und damit die 5 Tage mit den höchsten Niederschlagsmengen!
SORTIEREN und ÜBERNEHMEN kombiniert
Wie man sieht, ermöglichen 2 einfach zu durchschauende Funktionen eine sehr komfortable Lösung unserer Aufgabe. Da es sich bei meinen Beispieldaten um eine formatierte Tabelle handelt, können diese Funktionen einen weiteren Vorteil ausspielen: Sie sind dynamisch. Das bedeutet: Kommen in der Ausgangstabelle weitere Datensätze dazu, bezieht unsere Formel die neuen Daten ohne weitere Änderung ein und liefert automatisch aktualisierte Ergebnisse. Ohne Microsoft 365 wäre das mit einer Formellösung nicht zu bewerkstelligen. Hier würde sich Power Query als komfortable Alternative anbieten.
Hast du auch schon Erfahrungen mit der ÜBERNEHMEN-Funktion gemacht? Dann lass es uns in den Kommentaren wissen!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin.
Die Übernehmen-Funktion kannte ich bisher noch nicht, aber es ist immer schön neue Funktionen kennen zu lernen.
An dieser Stelle hätte ich vermutlich auf die Indexformel zurückgegriffen, also in etwa so:
INDEX(SORTIEREN(tblWetter;6;-1);{1;2;3;4;5};{1.2.3.4.5.6})
Das Ergebnis mit der Funktion Übernehmen ist im Endeffekt das Gleiche, jedoch etwas kürzer.
Beim Index habe ich jedoch den Vorteil, dass ich die Spalten des Arrays die ausgegeben werden sollen wählen kann. Also, wenn ich z.B. nur das Datum und die Niederschlagsmenge ausgeben möchte.
Das ist mir bei der Übernehmen-Funktion nicht gelungen, da vermutlich – wie der Name des Arguments bereits sagt – eine Menge an Spalten abgefragt wird und die welche Spalte/n genau.
Danke+Gruß
Sebastian
Hallo Sebastian,
ja, mit INDEX ist das natürlich möglich, es geht aber auch in Kombination mit der Funktion SPALTENWAHL. Bezogen auf das Beispiel aus dem Artikel:
=ÜBERNEHMEN(SPALTENWAHL(SORTIEREN(tblWetter3;6;-1);1;6);5)
Hier würden nur die Spalten 1 und 6 ausgegeben werden, also Datum und Niederschlagsmenge.
Schöne Grüße,
Martin
Geht aber auch mit FILTER und somit dann auch in Excel 2021, nicht nur in 365
Das wäre jetzt unsortiert, könnte man noch sortieren. So gibt es mir aber zusätzlich auch die Freiheit sie so herauszufiltern, wie die Daten in der Originaltabelle sind und nicht nur sortiert:
=FILTER(tblWetter;tblWetter[Niederschlag in mm]>=KGRÖSSTE(tblWetter[Niederschlag in mm];5))
FILTER hat auch noch den Unterschied, dass wenn der 5. Wert mehrfach vorkommt, ich auch ggf. 6 oder mehr Daten bekomme, also alle Daten, die zu den „5 höchsten“ Werten gehören.
Hallo Ralf,
danke für den guten Hinweis. Da FILTER auch schon in Excel 2021 verfügbar ist, ist das natürlich auch eine schöne Alternative.
Schöne Grüße,
Martin
Guten Morgen,
erst Mal möchte ich dir zu dieser Super-Seite gratulieren. Sehr hilfreich und klasse gestaltet.
Mein Problem:
Habe auf eine Tabelle ‚Deckblatt‘ und hole mir hier Daten von verschiedenen anderen Tabellen in der selben Datei (=’02 2024′!R149).
Dies funktioniert auch reibungslos. In machen Zellen wird aber nicht das Ergebnis aus den anderen Tabellen angezeigt, sondern nur die Formel. Hast du eine Idee, wie das behoben werden kann?
Hallo Raik,
das liegt ziemlich sicher am Zahlenformat der Zelle, in der die Formel steht. Hier wurde vermutlich „Text“ als Format eingestellt. Ändere das Format für diese Zellen in Zahl oder Standard, danach musst du die betreffenden Zellen nochmal mit F2 bearbeiten und sofort mit der Eingabetaste wieder beenden. Erst dann wird das neue Zahlenformat auch tatsächlich angewendet.
Schöne Grüße,
Martin