Matrixkonstanten in Excel verwenden

Matrixkonstanten liegen vor, wenn die Inhalte von Matrizen als konstante Werte eingegeben werden.

Im Standard werden Matrizen in Excel als Zellbereiche eingegeben, z.B. A3:C15.

Wann macht es Sinn, so vorzugehen? Z.B. dann, wenn Berechnungen mit Werten durchgeführt werden sollen, die in einer Tabelle nicht sichtbar sein sollen.

In diesem Beitrag zeige ich, wie das geht.

Wie sind Matrixkonstanten zu schreiben?

Matrixkonstanten werden in geschweiften Klammern dargestellt. Das erste Beispiel stellt die Zahlenfolge von 1 bis 5 als Matrixkonstante dar.

{1;2;3;4;5}

Zwischen den Werten steht jeweils das Semikolon. D.h. wenn die Werte als Bereich dargestellt werden, stehen sie übereinander, z.B. in A1:A5.

Denkbar ist auch, die einzelnen Werte jeweils in Anführungszeichen zu setzen, sie werden dadurch zu Text, mit dem dann auch gerechnet werden kann.

{“1″;”2″;”3″;”4″;”5”}

Stehen die Werte in einem Bereich nebeneinander, z.B. in A1:E1, ist die Matrixkonstante so zu schreiben:

{1.2.3.4.5}

Getrennt wird mit einem Punkt.

Die Variante mit den Anführungszeichen ist ebenfalls möglich.

Soll ein Bereich mit mehreren Zeilen und mehreren Spalten, z.B. A1:C2 mit den Werten von 1 bis 6, als Matrixkonstante dargestellt werden, ist so zu schreiben

{1.2.3;4.5.6}

Die ersten 3 Werte (1 bis 3) stehen in A1:C1 und werden mit dem Punkt getrennt.

Es folgt ein Semikolon, das anzeigt, dass es in die nächste Zeile geht. In A2:C2 stehen die Werte 4 bis 6, wieder durch den Punkt getrennt.

Mit Matrixkonstanten rechnen

1. Die Werte der Matrixkonstante mit einer Konstanten multiplizieren und die Summe bilden

Es soll {1;2;3;4;5} mit D4 (Wert: 12) multipliziert werden. Ich rechne so:

=SUMMENPRODUKT({1;2;3;4;5}*D4)

Und erhalte das Ergebnis 180.

Schreibe ich =SUMMENPRODUKT({1.2.3.4.5}*D4)

Erhalte ich auch 180 als Ergebnis.

Und die dritte Variante:

=SUMMENPRODUKT({“1″;”2″;”3″;”4″;”5”}*D4)

Wieder errechnet sich das Ergebnis mit 180.

2. Die Werte der namentlich benannten Matrixkonstante mit einer Konstanten multiplizieren und die Summe bilden

Die Matrixkonstante {1;2;3;4;5} erhält den Namen „Matrix1“. Unter „Bezieht sich auf“ gebe ich ={1;2;3;4;5} ein.

Die Formel sieht dann so aus:

=SUMMENPRODUKT(Matrix1*D4)

Als Ergebnis erhalte ich auch hier den Wert 180.

3. Die Werte der namentlich benannten, mehrzeiligen und mehrspaltigen Matrixkonstante mit einer Konstanten multiplizieren und die Summe bilden

Die Matrixkonstante {1.2.3;4.5.6}, zwei Zeilen und drei Spalten, benenne ich mit Matrix2 und rechne:

=SUMMENPRODUKT(Matrix2*D4)

Das Ergebnis lautet 252.

Die Formel sieht genauso aus wie im Beispiel zuvor, aber bei der Namensvergabe ist auf die unterschiedlichen Trennzeichen (in der Zeile der Punkt, Zeilenwechsel mit Semikolon) zu achten.

4. Zugriff auf die Matrixkonstante mit weiteren Excel-Funktionen

Ich verwende weiter die Matrixkonstante Matrix2.

Mit =KGRÖSSTE(Matrix2;1) erhalte ich das Ergebnis 6, richtig.

Mit =INDEX(Matrix2;1;2) erhalte ich den Wert 2, auch richtig.

D.h. es können alle Funktionen, die eine Matrix als Argument benötigen, auch auf unsere Matrixkonstanten zugreifen, unabhängig davon, wie die Schreibweise ist.

Es kann daher auch so geschrieben werden:

=INDEX({1.2.3;4.5.6};1;2), Ergebnis: 2

5. Multiplikation von Matrixkonstanten

Zwei kleine Matrizen sollen miteinander multipliziert werden.

Matrix3                               {1.2.3}

Matrix4                               {2.4.6}

Was geschieht?

Die Matrizen sind zeilenweise angeordnet, denn das Trennzeichen ist ein Punkt.

Folglich wird jetzt 2 x 1, 4 x 2 und 6 x 3 gerechnet und die Summe gebildet. Das Ergebnis lautet 28.

Einfacher geht es wieder mit der Summenprodukt-Formel:

=SUMMENPRODUKT((Matrix3)*(Matrix4))

Das Ergebnis ist ebenfalls 28.

Wichtig ist, dass beide Matrizen die gleiche Anzahl an Elementen hat.

Was nun, wenn eine Matrize in einer Zeile und die andere in einer Spalte angeordnet ist?

Matrix5                               {1.2.3}

Matrix6                               {2;4;6}

Hier wird jeder Wert der einen mit jedem Wert der anderen Matrix multipliziert. Das Ergebnis ist 72.

Verwende ich die Formel

=SUMMENPRODUKT((Matrix5)*(Matrix6))

erhalte ich ebenfalls 72 als Ergebnis.

6. Verwendung von Matrixkonstanten in komplexen Formeln

In einem nicht mehr vorhandenen Artikel zur dynamischen Berechnung der Prüfziffer eines Barcodes beschrieb ich, wie mit der WENN-Funktion zumindest eine Teildynamisierung in Abhängigkeit von der Länge der Ziffernfolge erreichbar ist. Die darin enthaltenen Matrixkonstanten habe ich allerdings ausgeschrieben. Hier will ich mich nicht auf die lange Formel beziehen, sondern bleibe beim Ausgangspunkt.

Die Ziffer lautete 130719789.

Die ursprüngliche Formel war:

=REST(10-REST(SUMMENPRODUKT((TEIL($A$1;{1;2;3;4;5;6;7;8;9};1))*{3;1;3;1;3;1;3;1;3});10);10)

Benenne ich jetzt die Matrix {1;2;3;4;5;6;7;8;9} mit Zähler und die Matrix {3;1;3;1;3;1;3;1;3} mit Faktor, verkürzt sich die Formel schon ein wenig:

=REST(10-REST(SUMMENPRODUKT((TEIL($C$51;Zähler;1))*Faktor);10);10)

Bis hier habe ich nur die Matrixkonstante mit 9 Elementen namentlich benannt. Das kann ich auch mit den Matrixkonstanten mit 10, 11 oder 12 Elementen tun. Die Formel wird dadurch aber nicht volldynamisch.

Versuche, den Formelteil  „Faktor“ durch INDIREKT(„Faktor“&LÄNGE($C$51)) zu ersetzen und damit auf die richtige namentlich definierte Matrixkonstante zuzugreifen, blieben erfolglos, jedenfalls, wenn ich die Matrixkonstanten in der Schreibweise z.B. {3;1;3;1;3;1;3;1;3} in der Namensgebung unter „Bezieht sich auf“ so eingebe.

Ich habe mich daher entschieden, für den Faktor keine Matrixkonstanten zu verwenden, sondern diese Bereiche namentlich als Listen zu definieren:

In diesem Beitrag habe ich nur die Ziffernlängen von 9 bis 12 betrachtet, deshalb auch nur die Faktorenlisten für diese Ziffern. Beispielhaft steht in V162 diese 12-stellige Ziffer.

Dazu soll die Prüfziffer errechnet werden.

Ich verwende nun diese Formel:

=REST(10-REST(SUMMENPRODUKT(TEIL(V162;ZEILE(INDIREKT(“1:”&LÄNGE(V162)));1)*INDIREKT(“Faktor”&LÄNGE(V162)));10);10)

und erhalte die Prüfziffer 6.

Verwende ich diese 10-stellige Ziffer

erhalte ich mit der gleichen Formel die Prüfziffer 7.

Die Formel ist daher dynamisch und führt in jedem Fall zu einem richtigen Ergebnis, wenn die Faktorenmatrizen als Listen angelegt sind.

Den Formelteil „ZEILE(INDIREKT(“1:”&LÄNGE(Zahl)))“ habe ich übrigens bei den Herren von http://www.excelformeln.de gefunden, genial. [1]

Jetzt habe ich die Matrixkonstanten in diesem Abschnitt völlig verbannt, ohne es zu wollen.

Mein Fazit: Matrixkonstanten sind eben Konstanten und keine Variablen. Sie lassen sich nicht dynamisieren.

Quellen:

[1] Jens Fleckenstein,Walter Fricke,Boris Georgi: Excel – das Zauberbuch: raffinierte Zaubereien für Excel-Kenner, Markt+Technik Verlag 2011, S. 257, ISBN 978-3-8272-4695-0