Blog

Rainer Pollmann

Rainer Pollmann

Zeit sparen beim Kopieren von Formeln

Klicken Sie auf den unteren Button, um den Inhalt von videopress.com zu laden.

Inhalt laden

So funktioniert ein dynamisches Array

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.

SUMMEWENNS
In der Bearbeitungsleiste kann man die benannten Zellbereiche erkennen

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.

SUMMEWENNS2

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. 


Wenn Ihnen dieser Beitrag gefallen hat, dann teilen  Sie ihn gerne. Falls Sie Anmerkungen haben, schreiben Sie bitte einen Kommentar, oder senden Sie mir eine Mail an info@prt.de.

Beitrag teilen:

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp
Share on xing
Share on email
Share on print

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Up to date bleiben

Melden Sie sich für unseren Newsletter an!