In den nächsten Wochen werden Anwender von Excel 365 die
Vorteile von dynamischen Arrays nutzen können. Dynamische Arrays erlauben die
Eingabe einer Formel in einer Zelle, die nicht wie bisher nur einen einzigen
Wert zurückgibt, sondern mehrere Ergebnisse in einem Zellbereich benachbarter
Zellen, dem sogenannten Array.
In dem folgenden Artikel geben wir Ihnen einen grundlegenden
Überblick über den Einsatz dynamischer Arrays.
In Zukunft sollen die dynamischen Arrays die sogenannten
Matrix- oder Arrayformeln zum großen Teil ablösen. Matrixformeln sind dadurch
gekennzeichnet, dass sie mit der Tastenkombination <STRG> + <SHIFT>
+ <RETURN> abgeschlossen werden und nicht wie einfache Formeln mit
<RETURN>. In der Bearbeitungsleiste erscheint die Matrixformel in
geschweiften Klammern {}.
Im folgenden Beispiel zeigen wir Ihnen, wie Sie dynamische Arrays als Ersatz für Matrixformeln nutzen können.
Matrixformel
Für mehrere Artikel sollen auf der Basis des Nettopreises der Bruttopreis unter Berücksichtigung eines Mehrwertsteuersatzes von 19% errechnet werden. Die Tabelle sieht folgendermaßen aus:
Um alle Bruttopreise in einem Arbeitsschritt zu errechnen,
soll zunächst eine Matrixformel verwendet werden:
Markieren
Sie den Zellbereich C2:C7.
Geben
Sie die folgende Formel ein: =B2:B7*1,19
Schließen
Sie die Formeleingabe mit der Tastenkombination <STRG>+ <SHIFT> + <RETURN>
ab.
Nach Eingabe der Matrixformel hat die Tabelle folgendes Aussehen:
Dynamischer Array
Wenn Sie mit Ihrer Excel-Version dynamische Arrays verwenden
können, können Sie die Berechnung der Bruttopreise folgendermaßen vornehmen:
Aktivieren
Sie die Zelle C2.
Geben
Sie die folgende Formel ein: =B2:B7*1,19
Bestätigen
Sie die Eingabe.
Das Tabellenblatt sieht folgendermaßen aus:
Da die Formel mehrere Werte zurückgibt, hat Excel sie in die benachbarten leeren Zellen überlaufen lassen.
Wodurch unterscheiden sich dynamische Arrays von Matrixformeln?
Dynamische Formeln sind einfacher und schneller einzugeben.
Wenn Sie die Formel =B2:B7*1,19 in einer Excel-Version eingeben, die dynamischen Arrays nicht unterstützt, wird der Bruttopreis nur in der ersten Zelle C2 ausgegeben. Obwohl Sie eine Formel mit einem Zellbereich eingegeben haben, die eigentlich mehrere Werte zurückgeben sollte, wird kein Fehler ausgewiesen, sondern nur ein einziger Bruttopreis. Es ist der Bruttopreis, der sich in der gleichen Zeile befindet wie der zugrunde liegende Nettopreis. Excel verwendet bei der Formelberechnung die sogenannte implizite Schnittmenge und kann somit einen einzelnen Wert aus einem Zellbereich von Werten in einer einzelnen Zelle zurückgeben. Die implizite Schnittmenge zwischen dem Zellbereich B2:B7 und der Formelzelle C2 ist in unserem Beispiel die die Zelle B2, weil sie sich in der gleichen Zeile befindet wie die Formel in Zelle C2. Die Bildung einer impliziten Schnittmenge hindert Excel daran, eine Zellbereich mit mehreren Werten zu füllen. Die dynamischen Arrays verzichten auf die Anwendung der impliziten Schnittmenge und können daher mehrere Werte mit einer einzelnen Formel zurückgeben.
Wenn Sie die Matrixformel anwenden, können Sie zwischen den Zeilen 2 und 7 keine Zeilen einfügen oder löschen. Im Zellbereich C2:C7 können Sie keinen einzelnen Zellenwert ändern. Es erscheint ein Dialogbild mit der folgenden Meldung:
Teile einer Matrix können nicht geändert werden.
Bei Anwendung von dynamischen Arrays können Sie die Formel nur in der ersten, linken oberen Zelle ändern. In allen anderen Zellen steht dieselbe Formel. Sie sieht in der Bearbeitungsleiste so aus:
Wenn Sie eine Zelle im Zellbereich C2:C7 aktivieren, wird der sogenannte Überlaufbereich farblich markiert. Der Überlaufbereich erkennen Sie an dem durchgezogenen blauen Rahmen. Die Größe des Zellbereichs einer Matrixformel wird dagegen nicht direkt im Tabellenblatt angezeigt. Der Zellbereich einer Matrixformel kann über die Tastenkombination <STRG>+ <SHIFT> + <7> markiert werden.
Im Gegensatz zur Matrixformel können Sie zwar einen einzelnen Zellwert in dem dynamischen Array ändern. In dem unteren Beispiel wurde in der Zelle C4 die Zeichenfolge Text eingegeben. Als Folge erscheint in der linken oberen Zelle des Überlaufbereichs der Fehlerhinweis: #ÜBERLAUF!. Die Werte in allen anderen Zellen werden gelöscht. Der vorgesehene Überlaufbereich ist jetzt mit einem gestrichelten blauen Rahmen umgeben.
Ein Klick auf den entsprechenden Smarttag mit dem Fehlerhinweis sieht so aus:
Durch eine Klick auf den Menüpunkt Blockierende Zellen auswählen können Sie alle Zellen, die den Überlauf blockieren, markieren. Nachdem Sie alle die Werte in den blockierenden Zellen gelöscht haben; wird der Überlaufbereich wieder mit den Ergebnissen gefüllt.
Der Überlauffehler wird Ihnen auch angezeigt, wenn Sie nach der Erstellung der Formel für den dynamischen Array nicht alle Zellen im benötigten Überlaufbereich leer sind.
Formeln mit Bezug auf dynamischen Array
Den höchsten Bruttopreis im dynamischen Array können Sie mit
der folgenden Formel in einer beliebigen Zelle ermitteln:
=MAX(C2#)
Mit dem Hashtag oder Doppelkreuz # wird ein dynamischer
Array gekennzeichnet mit C2 als linke obere Zelle. Falls sich der
Überlaufbereich ändern sollte, brauchen Sie die obige Formel selbst nicht mehr
anpassen.
Falls Sie eine Arbeitsmappe mit dieser Formel in einer Excel-Version
öffnen, die keine dynamischen Arrays kennt, erscheint in der Bearbeitungszeile
für die Formelzelle folgender Eintrag:
=MAX(_xlfn.ANCHORARRAY(C2))
Der Formelwert wird in der Zelle so lange angezeigt, bis eine Neuberechnung des Wertes erfolgen soll. Dann erscheint in der Zelle der Fehlerwert #NAME?.
Implizite Schnittmenge in Tabellen
Wenn Sie die obige Artikelliste in eine Excel-Tabelle
umwandeln, ist die Ermittlung der Bruttopreise so einfach wie die dynamischen
Arrays. Sie brauchen nur die folgende Formel in der Zelle C2 eingeben:
=[Nettopreis]*1,19
Standardmäßig werden alle Werte in der Spalte Bruttopreis ermittelt.
Bei der Berechnung des Bruttopreises wird hier der Wert aus der Spalte Nettopreis genommen, der sich in der gleichen Zeile befindet wie die Formelzelle. Es handelt sich wieder um eine implizite Schnittmenge.
Dynamischer Array mit Bezug auf Tabellenspalte
Wenn Sie die Bruttopreise außerhalb der Tabelle ermitteln wollen, können Sie wieder eine dynamische Arrayformel in einer beliebigen Zelle im Tabellenblatt dafür verwenden, wie z. B. in dem unteren Beispiel in der Zelle E2:
Wenn Sie in der Tabelle einen neuen Artikel hinzufügen oder
löschen, wird der Überlaufbereich automatisch erweitert bzw. verkleinert.
Wichtige Hinweise
Werte von Formeln, die dynamische Arrays mit Bezügen
auf Zellen in einer anderen Arbeitsmappe verwenden, werden nur angezeigt, wenn die
andere Arbeitsmappe geöffnet ist. Ansonsten erscheint der Fehlerwert #BEZUG!.
Dynamische Arrays können Sie nicht in
Excel-Tabellen einsetzen.
Beim Erstellen einer dynamischen Arrayformel für
die Berechnung des Bruttopreises wird das Währungsformat nicht für alle Zellen
im Überlaufbereich übernommen, sondern nur für die obere linke Zelle.
In der ersten Zelle des dynamischen Arrays wird
in der linken, oberen Zelle ein Überlauffehler angezeigt, wenn sich im Überlaufbereich
mindestens eine verbundene Zelle befindet.
Um den Überlaufbereich an eine andere
Zellposition zu verschieben, brauchen Sie vorher nicht den gesamten
Überlaufbereich markieren. Sie gehen mit der Maus an Rand der linken, obere
Zelle, bis der Mauszeiger die Form eines schwarzen Kreuzes annimmt. Bei
gedrückter Maustaste können Sie den Überlaubereich verschieben.
Setzen Sie die dynamischen Arrayformeln nur ein,
wenn alle Anwender, die eine entsprechende Arbeitsmappe öffnen, eine
Excel-Version verwenden, die diese neuen Funktionen unterstützt.
Neue Funktionen
Im Zusammenhang mit der Einführung von dynamischen Arrays
stehen Ihnen in Excel die folgenden neuen Funktionen zur Verfügung, die viele komplizierte
Berechnungen deutlich vereinfachen:
EINDEUTIG
FILTER
SEQUENZ
SORTIEREN
SORTIERENNACH
ZUFALLSMATRIX
In unserem nächsten Tipp zeigen wir Ihnen einige interessante Beispiele.
Fazit
Dynamische Arrays machen in vielen Situationen komplizierte Formeln überflüssig. Sie können jetzt ohne komplizierte Formeln komplexe Berechnungen durchführen.
Arbeiten Sie mit Excel noch effizienter mit professionell erstellten Arbeitsmappen und individuell programmierten Add-Ins.
Excel-Projekte
Kennen Sie die nützlichen Funktionen von Excel für das professionelle Arbeiten aus dem Homeoffice.
Wenn Ihnen dieser Beitrag weitergeholfen hat, teilen Sie ihn in den sozialen Medien. Möchten Sie uns etwas mitteilen, nutzen Sie die Kommentarfunktion. Antworten abbrechen