Mit einem Update Ende April 2020 haben sich tiefgreifende Änderungen in Excel 365 ergeben. So gibt es neuerdings Dynamische Arrays. Aufgefallen ist mir das in einem Seminar Ende April 2020, als in einer Datei plötzlich das Zeichen „@“ vor einer kombinierten INDEX/VERGLEICH-Funktion „erschien“. Außerdem tauchte eine Fehlermeldung #Überlauf! bei Verwendung der Tastenkombination <Strg> + <Enter> in einigen Zellen auf. Sehr merkwürdig!
Lesen Sie hier weiter, was wir über Dynamische Arrays herausgefunden haben!
Was sind Arrays?
Ein Array (engl. für Bereich, Matrix) ist eigentlich eine Matrix. Excel meint damit einen (konstanten) Tabellenbereich, der aus einer bestimmten Anzahl von Zeilen und Spalten besteht. Dafür geben sogenannte Array-Formeln mehrere Ergebnisse oder ein einzelnes Ergebnis zurück. Arrayformeln erfordern das Markieren des gesamten Ausgabebereichs und das Abschließen der Formel mit der Tastenkombination <Ctrl> + <Shift> + <Enter> (hier ein Anwendungsbeispiel mit MTRANS). Sie werden daher auch als CSE -Formeln (CONTROL+SHIFT+ENTER) oder als Array-Konstante bezeichnet.
Was sind Dynamische Arrays?
Dynamische Arrays entstehen dann, wenn Sie in eine Zelle eine Formel/Funktion eingeben, diese Eingabe mit <Enter> bestätigen und diese Funktion automatisch in einen Zellbereich übertragen wird (Überlauf). Dabei orientiert sich das Array an den vorhandenen Spalten und Zeilenbeschriftungen in Ihrem Zielbereich. So werden Ergebnisse automatisch entweder nach unten oder über die benachbarten Zellen „übertragen“. Voraussetzung dafür ist nach meiner Beobachtung die Verwendung von „Intelligenten Tabellen“ oder Namen für Zellen/Zellbereiche in Ihrem Excel-Modell. Klassische Zellbezüge, relativ oder absolut helfen hier nicht weiter.
Das ist Mustererkennung und ein Teil der seit 2018 umgesetzten Strategie, Künstliche Intelligenz in Excel zu integrieren
Beispiel mit der Funktion SUMMEWENNS
In der Beispieldatei sollen in einem Zielbereich ähnlich einer Pivot-Tabelle passend zur Zeilen- und Spaltenbeschriftung der Umsatz aus einer Liste addiert werden. Das geschieht mit der Funktion SUMMEWENNS, da diese mit bis zu 127 Kriterien bedingt Addieren kann. Alle Spalten der Liste sind benannt, ebenso die Überschrift und die Zeilen in der Zieltabelle.
Unterschiede in den Excel-Versionen
Wenn Sie, wie in der Abbildung dargestellt, die Funktion SUMMEWENNS eintragen und mit der Taste <Enter> abschließen, dann wird die Funktion bei Excel 365 in den Zellbereich C3 : J7 übertragen. Um die Name in den Funktionsargumenten einzutragen, verwenden Sie die Funktionstaste <F3>.
In den älteren Funktionen würde in diesem Fall die Funktion nur in die Zelle C3 eingetragen. Markieren Sie stattdessen zuerst den Zellbereich C3 : J7, tragen die Funktion ein und schließen die Eingabe mit <Ctrl> + <Enter> ab, wird die Funktion relativ angepasst, in den markierten Zellbereich übertragen.
Schließen Sie dagegen die Eingabe mit <Ctrl> + <Shift> + <Enter> ab wird die Funktion als Matrix in den markierten Zellbereich übertragen. Dies ist an den geschweiften Klammern erkennbar. Die Matrix ist nun eine Einheit. Es können nur alle Zellen gemeinsam gelöscht oder bearbeitet werden, nicht mehr eine einzelne.
Probieren Sie es aus, hier geht es zur Beispieldatei.
Geben Sie die Funktion in Excel 365 mit <Ctrl> + <Enter> ein, erhalten Sie die Fehlermeldung #Überlauf!, die hier bei Microsoft beschrieben wird. Leider habe ich noch nicht hundertprozentig verstanden, wie man diese Fehlermeldung umgeht. Das Zeichen „@“ ist in diesem Zusammenhang wichtig und muss vor dem Namen der Funktion eingegeben werden, u.U. an mehreren Stellen.
