So verwendest du Matrixkonstanten in Excel

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

Möglich ist aber auch, die Inhalte der Matrizen durch konstante Werte einzugeben. Diese werden Matrixkonstanten genannt.

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.

Matrix1

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.

Matrix2

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.

Matrix3

Verwende ich die Formel

=SUMMENPRODUKT((Matrix5)*(Matrix6))

erhalte ich ebenfalls 72 als Ergebnis.

 

6. Verwendung von Matrixkonstanten in komplexen Formeln

In meinem Artikel „Wie du dynamisch die Prüfziffer eines Barcodes berechnest“ 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:

Matrix4

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.

Matrix5

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

Matrix6

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

 

Advertisements

Autor: Gerhard Pundt

Auf meiner Site https://clevercalcul.wordpress.com geht es um die Tabellenkalkulation mit Excel. Es wird über Funktionen, Diagramme, Basiswissen u.a.m. geschrieben.

5 Kommentare zu „So verwendest du Matrixkonstanten in Excel“

  1. Guten Morgen Gerhard,

    vielen Dank für deinen Überblick in Matrixformeln bzw. das Arbeiten mit Matrixkonstanten. Als kleine Ergänzung würde ich noch erwähnen, dass bei Formeln die nicht wie SUMMENPRODUKT mit Matrizen umgehen können eine Matrixformel zwingend mit STRG + SHIFT und ENTER abgeschlossen werden sollte.

    Ansonsten freue ich mich gerade deinen Artikel direkt weiter geben zu können um die Vorteile von Matrixformeln erläutern zu können.

    Viele Grüße
    Andreas

    Gefällt mir

    1. Hallo Andreas,
      danke für deinen Kommentar und die Ergänzung. Ich selbst habe noch gar keine Anwendungsfälle in meiner täglichen Praxis, werde sie also kaum nutzen. Interessant sind sie aber schon.
      Grüße
      Gerhard

      Gefällt mir

    2. Hallo Gerhard,

      bis auf die Prüfzifferberechnung ist mir auch selten eine Rechnung mit „echten“ Matrixfunktionen untergekommen. Allerdings möchte ich mich hier noch einmal intensiver mit den Thema durch das Schulungsvideo „Excel: Matrixformeln – Formeln für Fortgeschrittene“ von Frank Arendt-Theilen bei Video2Brain beschäftigen (siehe auch meinen Artikel unter http://www.andreas-unkelbach.de/blog/?go=show&id=687 ) da ich hier hoffe eine gute Anregung für das Summieren nach Farbwerten zu erhalten.

      Mein aktuell liebstes Beispiel für eine Matrixfunktion ist ohnehin die Verweisfunktion zur Überprüfung, in welchen Nummernintervall ein CO Objekt liegt siehe http://www.andreas-unkelbach.de/blog/?go=show&id=540 auch wenn dieses mittlerweile so auch aus unseren ERP System geliefert werden soll aber bis vor einigen Änderungen war dieses eine schöne Methode.

      Manchmal ist es daher ganz hilfreich sich einfach bewust zu sein, dass es solche Funktionen gibt und irgendwann kommen dann tatsächlich Anwendungsfälle…. Daher bin ich auch immer sehr froh, auf Grundlagenartikel (wie deinen) Zugriff zu haben um hier mich zeitnah ins Thema einlesen zu können.

      Viele Grüße
      Andreas

      Gefällt mir

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s