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:

1. Markieren Sie den Zellbereich C2:C7.
2. Geben Sie die folgende Formel ein: =B2:B7*1,19
3. 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:

1. Aktivieren Sie die Zelle C2.
2. Geben Sie die folgende Formel ein: =B2:B7*1,19
3. 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.