Der heutige Artikel fällt etwas aus der Reihe, denn ich werde dir Dinge in Excel zeigen, die noch nicht sehr viele Menschen gesehen haben! Ich möchte mit dir einen Blick in die Excel-Zukunft werfen, wo vieles von dem, was wir bisher von Tabellenfunktionen gekannt haben, auf den Kopf gestellt wird.
Bist du neugierig geworden? Gut so! Dann lehn‘ dich entspannt zurück und freue dich auf die Lektüre.
Ach ja, noch ein kleiner Hinweis für die ganz Misstrauischen unter uns: Nein, auch wenn es das Datum vielleicht vermuten lässt, ist in diesem Artikel KEIN Aprilscherz versteckt.
Und los geht’s:
Array-Funktionen komplett neu gedacht
Microsoft wirft 7 neue Array-Funktionen in den Ring, von denen ich dir heute 3 vorstellen werden. Gleichzeitig wird sich die Art und Weise ändern, wie man mit Formeln und Funktionen auf Tabellenbereiche zugreift. Und damit fange ich gleich mal an:
Excel-Formeln, die mehrere Ergebnisse zurückliefern (sogenannte Arrays), werden zukünftig in benachbarte Zellen „überlaufen“ (englisch: spill). Wie kann man sich das vorstellen?
Wenn man bisher in einer Zelle Bezug genommen hat auf einen ganzen Zellbereich, gab es einen #WERT-Fehler:
Bisheriges Ergebnis bei Array-Bezügen
Ist ja auch irgendwie klar, denn es werden ja mehrere Werte zurückgeliefert, die nicht alle in eine Zelle passen.
Zukünftig wird das jedoch so aussehen:
Der zukünftige Umgang mit Array-Bezügen
Excel lässt die Werte einfach in die benachbarten Zellen „überlaufen“ und nimmt dabei automatisch soviel Platz in Anspruch, wie eben benötigt wird. Bisher musste man dazu den Zielbereich komplett markieren und die Formel umständlich mit Strg+Umschalt+Eingabe abschließen, um per Array-Formel das gleiche Ergebnis zu erzielen!
Trotzdem wird in jeder Zelle die gleiche Formel angezeigt. Solange sich die aktive Zelle innerhalb des Arrays befindet, wird um den ganzen Bereich herum ein blauer Rahmen angezeigt, so dass man auf einen Blick erkennt, das es sich tatsächlich um einen zusammengehörigen Bereich handelt.
In der Bearbeitungszeile sieht man noch einen kleinen Unterschied: Nur in der linken oberen Zelle wird die Formel schwarz angezeigt, in allen anderen Zellen ist sie in einem blasseren Hellgrau:
Nur die erste Zelle ist änderbar
Alle anderen Zellen sind unveränderlich
Wenn man versucht, einen einzelnen Wert innerhalb des Arrays zu entfernen oder zu ändern, wird Excel dies kommentarlos verweigern. Nur in der linken oberen Ecke ist das möglich. Ein Druck auf die Entfernen-Taste löscht das komplette Array, wie man oben in der Animation auch erkennen kann.
Die Funktion SORTIEREN
Das was ich eben beschrieben habe, ist nur die Basis für ein paar völlig neue Tabellenfunktionen. Eine davon heißt SORTIEREN und macht genau das, was der Name sagt. Damit lässt sich eine Tabelle oder ein Bereich daraus supereinfach und ohne irgendwelchen umständlichen Kniffe per Formel sortieren.
Die neue SORTIEREN-Funktion
Das ist schon extrem beeindruckend, oder? Und selbst wenn in der Quelltabelle neue Datensätze eingefügt werden oder sich die Werte verändern, nach denen sortiert wurde, passt sich das Ergebnis sofort automatisch an!
Die Funktion FILTER
In eine ähnliche Richtung geht die neue FILTERN-Funktion. Damit lassen sich nun auch dynamisch Daten filtern, ohne jedes mal über das Menü zu gehen oder komplexe Matrixformeln zu entwickeln:
Die neue FILTER-Funktion
Die Funktion EINDEUTIG
Diese neue Funktion liefert aus einer Liste entweder nur die eindeutigen Werte, also auch jeden mehrfach vorkommenden Wert nur genau einmal. Oder alternativ nur solche Werte, die nur ein einziges Mal vorkommen.
Die neue EINDEUTIG-Funktion
Wer das bisher mit Formeln lösen wollte, musste ein paar knackige Tricks anwenden.
Es gibt noch ein paar weitere dieser neuen dynamischen Array-Funktionen, aber für heute möchte ich es mit dem Ausblick in die (nahe) Excel-Zukunft belassen. In einem späteren Artikel werde ich auch die anderen Funktionen vorstellen.
Ja, vieles davon war jetzt auch schon möglich. Allerdings wesentlich umständlicher und nicht so dynamisch.
Ja, in den Genuss dieser Funktionen werden in absehbarer Zeit nur Office-365-Abonnenten kommen (es sei denn, du wartest auf die nächste reguläre Excel-Version 2022 oder 2025).
Und ja, damit wird die Kompatibilität und der Datenaustausch mit anderen Anwendern (noch) komplizierter werden.
Trotzdem bin ich sicher, dass diese extrem mächtigen Funktionen schnell ihren Platz und ihre Fans finden werden und sich deren Anwendungsgebiete im Augenblick nur ansatzweise erahnen lassen.
Wie denkst du darüber?
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.
Moin.
Ein spannender Einblick. Danke dafür!
PS:
Gibt es einen guten Grund für das Anführungszeichen in „lehn‘“, obwohl ein Apostroph zu sätzen wäre?
Liegt es am verwendeten Programm, dass von sich den Wert ‘ ausgewählt hat?
Gruß
Friedrich
Hallo Friedrich,
das sollte auch ein Apostroph sein, es liegt tatsächlich an WordPress. Hier gibt es leider immer wieder mal Schwierigkeiten mit der Darstellung von Apostrophen und Anführungszeichen.
Schöne Grüße,
Martin
Die neue Filterfunktion ist klasse! Sie ist eine konsequente Weiterführung der index/vergleich-Kombination, die man – natürlich – als separate Funktion entwickeln musste.
Hi Axel,
sehe ich genauso!
Schöne Grüße,
Martin
Hallo Martin.
Super Ausblick, habe mich schon immer gefragt, wann der lästige Abschluss der Array-Formeln endlich wegfällt. Hatte diese daher meist gemieden. Auch O365 stehe ich noch sehr skeptisch gegenüber, aber mit solchen Formeln in der Zukunft… da werde ich dann wohl auch zum Abonnenten werden (müssen).
Danke, es Grüßt Dein Namens-Vetter
Martin
Hallo Martin,
ja, O365 wird von manchen geliebt und von anderen gehasst. Aber es ist ganz offensichtlich, dass Microsoft langfristig alles daran setzen wird, die Anwender auf Office 365 zu bringen. Denn damit wird schließlich auch das Geld verdient…
Schöne Grüße,
Martin
Guten Morgen Martin,
auch für mich ist der Ausblick auf die Funktion FILTER mit viel Hoffnung versehen. Derzeit filtere ich eine Tabelle und füge diese per Inhalte einfügen -> Verknüpfung ein, aber per Filter ist dieses wesentlich interssanter (insbesondere wenn mehr als ein Filter verwendet werden kann. Ist dir zufällig bekannt, ob beim Office Insider Programm dieses für das gesamte Microsoft Konto oder für die lokale Installation altivierbar ist?
Unabhängig davon aber vielen Dank für den Blick in die Zukunft.
Viele Grüße
Andreas
Hallo Andreas,
soweit ich weiß, bezieht sich das Office Insider Programm nur auf die jeweilige lokale Installation, auf der sie aktiviert wurde. Also nicht auf das gesamte Microsoft Konto. Aber das ist ohne Gewähr, bitte nagle mich nicht darauf fest 🙂
Schöne Grüße,
Martin
Wow – die neuen Funktionen sind sehr hilfreich – die Frage die sich mir noch stellt ist wie können die neuen Funktionen auch so einfach mit VBA angewandt werden.
Hallo Gerhard,
ja, VBA wird in dem Zusammenhang sicherlich ein interessantes Thema sein.
Schöne Grüße,
Martin
Spannedes Thema, danke! Ich suche schon seit langem nach einer Lösung für folgendes Problem:
Ich eine Liste mit Namen von Studenten in einer Spalte, in einer anderen Spalte steht eine Ziffer, die für ein Referatsthema steht, für das sie sich entschieden haben, pro Student genau ein Thema. In einer weiteren Liste steht die Referatsnummer und das Thema. Dahinter sollen nun die Namen der Studenten (0..n) erscheinen, die das betreffende Thema ausgewählt haben, idealerweise aber nicht über mehrere Zellen überlaufend, sondern innerhalb einer Zelle. Beispiel:
Nr. Thema Referent(en)
1 (Beschreibung) Klaus, Liese, Erwin
2 (Beschreibung) Susi, Edith
3 (Beschreibung) Karl-Heinz
Sollte ich damit auf die dynamischen Funktionen warten, wenn es mit denen überhaupt geht, oder ist das doch eher ein Fall für VBA?
Über einen kurzen Ratschlag würde ich mich sehr freuen!
Hallo Dirk,
wenn du bereits Office 365 nutzt (oder Excel 2019), dann geht es auch mit der TEXTVERKETTEN-Funktion. Man muss sie allerdings als Array-Funktion eingeben. Das heißt, mit Strg+Umschalt+Eingabe abschließen.
Schöne Grüße,
Martin
Hallo Martin,
ich bin vor Erfurcht erstarrt und bin begeistert, dass es am Ende eine so knappe Formel ist!
Vielen Dank sagt ein fröhlicher Excel-Nutzer!
Interessante Ausblicke. Es stört mich allerdings etwas, dass ich den Eindruck gewann, dass bewährte Konzepte damit etwas durcheinander geraten, z.B. absolute und relative Verweise, etc. Von daher bin ich gespannt, inwieweit Excel damit konzeptionell „aus einem Guß“ bleibt.
Weiterhin die Fragen:
* wird eine Mappe, in der diese Funktionen verwendet werden, zur xlsm- oder xlsb-Datei, oder ist sie nach wie vor als .xlsx-Datei speicherbar?
* wie sind solche Dateien auf anderen Plattformen (Mac, Android,…) nutzbar?
* kann ich sowas dann auch in der Online-Version von Excel nutzen?
und, zuletzt etwas off topic: was passiert mit den XL4-Macrofunktionen (Zelle.zuordnen, etc.)? Die bieten immerhin nach wie vor Möglichkeiten, die mit den ‚Standard‘-Funktionen nicht oder nur umständlich erreichbar sind.
Gruß,
Rainer
Hallo Rainer,
ja, es muss sich noch zeigen, wohin sich Excel langfristig entwickeln wird. Was absolute und relative Bezüge angeht: Die werden sicher auf absehbare Zeit noch erhalten bleiben. Denn die neuen Array-Formeln sind ja nur ein kleiner, wenn auch sehr interessanter Aspekt in der gesamten Funktionalität. Und es gibt ja noch extrem viele andere Tabellenfunktionen, die mit diesen Bezügen arbeiten.
Im Mappenformat gibt es mit diesen Array-Funktionen keine Besonderheiten, Sie sind ganz normal als *.xlsx speicherbar. Und sie funktionieren auch mit der Online-Version in Office 365.
Wie lange die alten XL4-Makrofunktionen noch erhalten bleiben, weiß nur Microsoft. Im Augenblick funktionieren sie noch und darüber bin ich auch sehr froh 🙂
Schöne Grüße,
Martin
Ist es möglich die Filter Funktion innerhalb einer abelle zu nutzen ohne den Fehler #Überlauf zu erhalten?
Hallo Lars,
nein, die neuen dynamischen Array-Funktionen, wie hier die Filter-Funktion, können leider nicht innerhalb von formatierten Tabellen verwendet werden. Hingegen empfehlen sich formatierte Tabellen als Input-Quelle für diese neuen Funktionen, damit neue Daten auch automatisch berücksichtigt werden.
Hier findest du mehr dazu:
https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531?ui=en-us&rs=en-us&ad=us
Schöne Grüße,
Martin
Hallo Martin,
diese Array-Funktionen sind schon nützlich, aber leider kann man damit so manches wohl noch nicht realisieren.
Als Beispiel die Funktion Eindeutig, klar man bekommt eine Liste angezeigt und man kann auch diese auch fix woanders einfügen mit z.B. A1#, oder nur den ersten Eintrag eben mit A1. Aber wie kann man sich aus dieser Liste nun z.B. nur Nummer 2 oder 5 oder 10 in einer anderen Zelle anzeigen lassen? Gibt es da schon irgendeinen Trick für? Oder ist das von MS derzeit nicht gewollt?
Schönen Abend.
Hallo Oliver,
du kannst auf diese Arrays mit jeder anderen Excel-Funktion zugreifen, wie auf jede „normale“ Tabelle oder Liste auch.
Wenn also beispielsweis das Array in A1 beginnt (und sich in A2, A3 usw. fortsetzt), kannst du so auf den 2. Wert zugreifen:
=INDEX(A1#;2)
Schöne Grüße,
Martin