Matrixformel¶
Matrixformeln werden auch Arrayformeln benannt. Diese Formelumsetzung bitte nicht mit den ebenfalls in Excel verfügbaren Matrixfunktionen verwechseln.
Abb. 63 Matrix- bzw. Arrayformeln - hier klassisch mit { .. }¶
Eine schnelle Recherche zu diesen speziellen Formelumsetzungen ergibt:
Hermann Baum - Matrixformeln bzw. Arrayformeln - Was ist das?
Excel 2019: Mit Matrixformeln kannst Du eine Menge Zeit sparen!
Außerdem bezeichnet man die hier dargestellten Matrixformel auch als CSE Formeln, da sie klassisch über die Tastenkombination Ctrl + Shift + enter abgeschlossen werden.
In aktuellen Microsoft 365 Varianten von Excel können diese Formeltechniken als Dynamische Matrixformeln mit einem einfachen Return bestätigt werden. die geschweiften Klammern sind dann nicht vorhanden!
Beispiel: Largest Remainder Method¶
In vielen meiner Excel Seminare starte ich mit einer einfachen Umsatztabellen inklusive Prozentualen Auswertungen von Umsätzen. Dabei werden durch die dargestellte Rundung von Nachkommastellen Abweichungen von 100% sichtbar - z.B. 25,4%, 25,5% und 49,0%. Das ergäbe ja nur 99,9% - oder in anderen Varianten auch mal 100,1% - aber liegt natürlich nur an der Darstellung der Nachkommastellen!
Ich übergebe daher solche Excel-Tabellen immer mit genügend Nachkommastellen, damit die Ansicht (bzw. der Ausdruck) immer 100% ergibt. Rechnerisch - also für Excel - stimmen die 100% ja immer!
Eine mögliche Umsetzung: Largest Remainer Method
Diese mathematische Technik wird auch bei Wahlauswertungen/Sitzanzahlberechnungen benutzt!
Beispielbeitrag auf Reddit: https://www.reddit.com/r/excel/comments/c57p42/excel_how_to_limit_percentages_to_100/?tl=de
Die folgenden Ausführungen sind von Reddit Autor Monimonika18 aus dem verlinkten Post.
Ich habe mir dieses Problem angesehen und eine Lösung verwendet, die als Largest Remainder Method (ich habe versucht, sie in 5 Schritten zu erklären) bezeichnet wird. Ich habe auch versucht, den Bedarf an Hilfszellen so weit wie möglich zu minimieren, konnte dies aber für einen Teil nicht tun. Sie müssen nur die Formeln in Schritt 3 und Schritt 5 eingeben und per Drag & Drop kopieren.
—Reddit Autor Monimonika18
Auch der Rest der Erläuterungen von Reddit Autor Monimonika18 (bis siehe Ende unten). Für meine Trainees habe ich eine entsprechend aufbereitete Übungsmappe. Aber natürlich trainiert es am Besten, wenn man sich die Schritte selber versucht zu erläutern. Folgen wir erst einmal weiter den Ausführungen von Reddit Autor Monimonika18:
Also, fangen wir an:
Ich werde davon ausgehen, dass die berechneten Werte (aus beiden Zeilen Ihrer Beispielzahlen) des Folgenden in A1 in der linken oberen Ecke beginnen und um sie in % Zahlen zu erhalten, muss ich mit 100 multiplizieren.
Tabelle (A1:E2):
=49/466 =77/466 =201/466 =84/466 =55/466
=13/407 =17/407 =42/407 =114/407 =221/407
Runden Sie % Zahlen auf % Ganzzahlen ab.
= INT(100*A1)
Subtrahieren Sie die Summe der % Ganzzahlen von 100, um die % Differenz zu erhalten.
= 100-SUM(INT(100*$A1:$E1))
Subtrahieren Sie die % Ganzzahlen von den % Zahlen, um die % Dezimalstellen zu erhalten.
= (100*A1)-INT(100*A1)
Sie müssen an dieser Stelle Hilfszellen hinzufügen, da ich nicht weiß, wie ich dieses Array in einer Formel zum Laufen bringen kann. Angenommen, die Formel direkt darüber, um die % Dezimalstellen zu erhalten, wird in Zelle A4 oben links eingefügt und dann per Drag & Drop kopiert, um den Rest der folgenden Tabelle (A4:E5) zu füllen:
0,5150214 0,5236051 0,1330472 0,0257510 0,8025751
0,1941031 0,1769041 0,3194103 0,0098280 0,2997542
Entschuldigen Sie die Formatierung. Ich habe gerade die resultierenden Werte (auf 7 Dezimalstellen gekürzt, um sie sichtbar zu machen) oben eingefügt, um zu zeigen, was Sie in den Hilfszellen sehen sollten.
Ordnen Sie die % Dezimalstellen von der höchsten zur niedrigsten (um doppelte Ränge zu vermeiden, addiert COUNTIF zu jedem doppelten Rang, falls vorhanden).
= RANK(A4,$A4:$E4)+COUNTIF($A4:A4,A4)-1
Nehmen Sie die % Differenz und verteilen Sie sie auf die % Ganzzahlen mit den am höchsten eingestuften % Dezimalstellen.
Wenn die % Differenz also 3 beträgt, wird zu den % Ganzzahlen der drei höchsten % Dezimalstellen jeweils 1 addiert.
= % Ganzzahl +IF( % Differenz >= % Dezimalstellenrang ,1,0)
= INT(100*A1)+IF(100-SUM(INT(100*$A1:$E1))>=RANK(A4,$A4:$E4)+COUNTIF($A4:A4,A4)-1,1,0)
Da es sich bei der obigen Formel um eine Array-Formel handelt, geben Sie sie bitte mit Strg-Umschalt-Eingabe (CSE) anstelle von nur Eingabe ein. Sie wissen, dass Sie erfolgreich waren, wenn Excel der Formel { } hinzufügt.
Die Ergebnisse der obigen Formel, die Sie erhalten, sind:
10 17 43 18 12
3 4 11 28 54
Wenn Sie über die Zeilen addieren, ergibt jede Zeile perfekt 100. Wenn Sie die Formel ohne CSE eingegeben hätten, wäre die Summe für jede Zeile größer als 100. Stellen Sie also sicher, dass Sie es richtig machen!
Ende der Erläuterungen von Reddit Autor Monimonika18!