Excel und Matrixformeln

Matrixformeln - Fingerübungen

Einfache Übung: Summe von 1 bis n

Zur Illustration der Leistungsfähigkeit wollen wir die Zahlen 1 bis n summieren. Dies könnten wir natürlich auch mit Hilfe der Formel n·(n+1)/2 machen, aber heute geht es nur darum zu zeigen, was die Verwendung von Matrixformen leistet.

Variante A

Zunächst verwenden wir die Formel '=Zeile()' in Spalte "A" für die Erzeugung der Folge der natürlichen Zahlen von 1 bis 40'000. In Zelle "C3" berechnen wir die Summe mit Hilfe des Verweises auf einen Bereich: '=SUMME(BEREICH.VERSCHIEBEN(A1;0;0;40000))'

Wir speichen die Arbeitsmappe ab und erhalten als Grösse in Office 2016 etwa 442 KB.

Variante B

Wenn wir die Formeln in Spalte "A" durch die Werte ersetzen und die Arbeitsmappe unter anderem Namen speichern, so vermindert sich deren Grösse auf 333 KB

Variante C

Zur Illustration erzeugen wir die Werte 1 bis 40000 mit Hilfe einer Matrizenformel zunächst in der Spalte "A". Dazu markieren wir den Bereich mit allen Werten (d.h. den Bereich A1:A40000) und geben im Eingabefeld ein: '=ZEILE(BEREICH.VERSCHIEBEN(A1;0;0;40000))'

Wir schliessen die Eingabe mit "Strg"+"Umschalt"+"Eingabe" ab, was die Formel als Matrizenformel kennzeichnet. Wenn irgendeine Zelle dieses Bereiches aktiv ist, wird die Formel in geschweiften Klammern angezeigt: '{=ZEILE(BEREICH.VERSCHIEBEN(A1;0;0;40000))}'. Abgespeichert hat die Mappe mit dieser Formel praktisch die selbe Grösse wie jene aus Variante "A".

Variante D

Nun sind ja die Zahlen 1 bis 40000 an sich gar nicht von Interesse, wir wollen ja nur deren Summe berechnen. Dazu klicken wir auf Zelle "C3" und ändern die bisherige Formel wie folgt: '=SUMME(ZEILE(BEREICH.VERSCHIEBEN(A1;0;0;40000)))'.

Wiederum schliessen wir die Eingabe mit "Strg"+"Umschalt"+"Eingabe" ab, um die Formel als Matrizenformel zu kennzeichnen. Das Ergebnis ändert sich natürlich nicht, aber wir können jetzt die Spalte A komplett löschen. Die Zahlenfolge wird jetzt direkt bei der Berechnung der Formel erzeugt und gleich summiert. Ein Abspeichern der Arbeitsmappe zeigt: Die Folge wird nicht zwischengespeichert, die Grösse der Arbeitsmappe schumpft auf 8 KB!

Erklärung: Die Funktion ZEILE(BEREICH.VERSCHIEBEN(A1;0;0;40000)), eingegeben als Matrixformel, liefert die Folge der Zeilennummern der Zeilen 1 bis 40000 in Spalte "A" als einspaltige Matrix. Die Summenfunktion summiert dann über alle Elemente der Matrix.

Anwendungen

Filterung periodischer Datenfolgen

Als Beispiel nehmen wir die monatlichen Temperaturmittel für Payerne von MeteoSchweiz, die wir in Spalten "A" bis "D" einfügen wie in der Bildschirmkopie dargestellt:

Wir möchten danach die Monatstemperaturen für alle Monate über alle Jahre mitteln. Als Übung reproduzieren wir zunächst die Folgen der Jahre und Monate in den Spalten "F" und "G", wobei wir das Anfangsjahr aus Zelle "A2" und den ersten Monat aus Zelle "C2" verwenden. Dazu sehen wir in den Spalten "F" und "G" einen Bereich für die Startwerte, die Grössenbestimmung und Konstanten vor:

Die Formel für die Monate verwendet die Folge der Zahlen 1 bis n aus der Funktinon 'ZEILE(BEREICH.VERSCHIEBEN(A1;0;0;G3))' über einen Bereich, dessen Höhe durch die Anzahl Datenpunkte in "G3" bestimmt ist. Weil der erste Monat nicht die Nummer 1 trägt, müssen wir den Monat der ersten Datenzeile berücksichtigen:

'{=$G$6+GANZZAHL((ZEILE(BEREICH.VERSCHIEBEN(A1;0;0;G3))+$G$7-2)/G$5)}'

Nach je 12 Zeilen nimmt die Jahreszahl um 1 zu. Der Dividend 12 wird aber nicht als fester Wert in die Formel eingetragen, sondern als Konstante ab Zelle "G5" abgelesen. Damit lässt sich die Formel auch für ähnliche Anwendungen mit anderen Perioden verwenden.

Die Formel wird auf ein paar Zellen der Spalte "H" als Matrixformel eingegeben und dient nur zur Illustration und Kontrolle, ob die Ergebnisse den Monatsnummern aus den Originaldaten entsprechen.

Analog wird in Spalte "I" zur Kontrolle die Monatsnummer berechnet:

'{=REST(G7+ZEILE(BEREICH.VERSCHIEBEN(A1;0;0;G3))-2;$G$5)+1}'

Die Berechnung der Monatsmittelwerte für alle Monate leiten wir in drei Schritten her aus:

1. Anzahl Werten, die dem Kriterium entsprechen
2. Summe der Werte, die dem Kriterium entsprechen
3. Quotient von 2 und 1

Für das Zählen von Werten, die einem Kriterium entsprechen, verwenden wir den Abfrageausdruck '1*(Wert=Kriterium)'. Dieser Ausdruck liefert 1, wenn die Abfrage 'Wert=Kriterium' gleich ist, sonst 0. Als Wert verwenden wir die Matrixformel für die Monatszahlen, und als Kriterium den betreffenden Monat. Wir bereiten einen Datenbereich mit 12 Zeilen vor, in dessen erste Spalte "L" die Zahlen 1 bis 12 stehen:

In Spalte "M" fügen wir beim ersten Monat (in Zelle "M3") die Formel ein, die die Monatszahlen aus der Matrizenformel mit der Monatszahl vergleicht und für jede Übereinstimmung 1 liefert. Somit brauchen wir diese Matrizenformel nur noch zu summieren, um die Anzahl zu erhalten. Die Matrizenformel für die Monatszahlen befindet sich in Spalte "I", von wo wir sie direkt übernehmen können:

{=SUMME(1*(REST(G$7+ZEILE(BEREICH.VERSCHIEBEN($A$1;0;0;G$3))-2;G$5)+1=L3))}

Mit den entsprechenden Fixierungen der Zeilen bei den Bezügen auf "G3" und "G7", können wir diese Formel jetzt für alle weiteren Monate anwenden, indem wir sie hinunterziehen. Die Berechnung der Summe der Monatsmittel aller Werte erfolgt jetzt ohne Bezug auf eine Hilfsspalte, die die Monatszahlen bei den Daten enthält.

Die Summe der Monatswerte der betreffenden Monate erstellen wir direkt aus der Formel für die Anzahl, indem wir die "1" ersetzen durch die Liste der Monatswerte:

{=SUMME(BEREICH.VERSCHIEBEN($C$2;0;0;G$3)*(REST(G$7+ZEILE(BEREICH.VERSCHIEBEN($A$1;0;0;G$3))-2;G$5)+1=L3))}

Die Mittelung können wir nun als Quotient der Wertesumme durch die Werteanzahl ausdrücken:

SUMME((REST(G$7+ZEILE(BEREICH.VERSCHIEBEN($A$1;0;0;G$3))-2;G$5)+1=L3)*BEREICH.VERSCHIEBEN($C$2;0;0;G$3))/SUMME(1*(REST(G$7+ZEILE(BEREICH.VERSCHIEBEN($A$1;0;0;G$3))-2;G$5)+1=L3))

Dies ist zwar ein ordentlich langer Ausdruck, er erlaubt uns aber, die Mittelwerte ohne die Monats- und Jahreszahlen zu berechnen. In diesem Beispiel können wir je 600 Werte einsparen, bei Summen von Viertelsundenwerten über ein Jahr sind es 35000, was bei drei bis vier Hilfsspalten langsam ins Gewicht fällt.

Mittelung über Kriterien

Den Ausdruck, den wir für die Berechnung der Jahreszahlen in Spalte "H" aufgestellt haben, verwenden wir, um das jährliche Mittel der Monatstemperaturen über die Jahre zu erhalten. Wiederum bereiten wir einen Datenbereich vor, der in Zelle "Q2" das erste Jahr enthält, und die folgenden nach unten.

Zur Kontrolle zählen wir zunächst die Werte, die jeder Jahreszahl entsprechen:

{=SUMME(1*(G$6+GANZZAHL((ZEILE(BEREICH.VERSCHIEBEN($A$1;0;0;G$3))+G$7-2)/G$5)=Q2))}

Als kleine Übung berechnen wir in der Spalte "S" die Summe der Monatstemperaturen für das entsprechende Jahr:

{=SUMME(BEREICH.VERSCHIEBEN(C$2;0;0;$G$3)*(G$6+GANZZAHL((ZEILE(BEREICH.VERSCHIEBEN($A$1;0;0;G$3))+G$7-2)/G$5)=Q2))}

Und zu guter Letzt in Spalte "T" die Mittel der Monatstemperaturen:

Wer Lust hat, kann sich den Verlauf über die Jahre als Grafik ausgeben lassen: