In einem früheren Artikel hatte ich die benutzerdefinierte Funktion LNUM.SUM vorgestellt, die das Addieren zwei sehr großer Zahlen als rein formelbasierte Lösung ermöglicht und dabei auf die kürzlich in Excel eingeführte LAMDBA-Funktion aufsetzt.
Dieser Artikel stellt nun die Funktion LNUM.SUBTRACT vor, die ebenfalls rein formelbasiert zwei sehr große Zahlen in Excel subtrahiert. Auch diese Funktion wird, wie LNUM.SUM, auf bereits von mir implementierte LAMBDA-Funktionen zurückgreifen, die im Artikel an entsprechender Stelle dann verlinkt sind.
Große Zahlen subtrahieren
Angenommen, es liegen die beiden Zahlen 99 995 492 198 785 672 356 und 7 392 345 623 648 574 als Text in den Zellen B2 und B3 eines Arbeitsblattes in Excel vor. Wie bei einer Addition, führt eine Subtraktion dieser beiden Zahlen über eine Excel-Formel, die die Texte in Zahlen umwandelt, zu fehlenden Stellen ab der 15. Stelle.
Das Problem liesse sich sicherlich z.B. durch ein VBA-Add-In lösen, welches dann eine benutzerdefinierte VBA-Funktion zur Verfügung stellen würde. Da wir jedoch eine reine Formellösung implementieren möchten, fällt die VBA-Lösung weg.
Der Algorithmus, der für die Formellösung verwendet wird, ist dem Algorithmus zum Addieren großer Zahlen recht ähnlich. Zunächst werden Blöcke aus den in Textform vorliegenden Zahlen gebildet und je in einem Array abgelegt, danach Teilberechnungen durchgeführt und hierbei Übertragswerte berücksichtigt und schließlich die Blöcke wieder zusammengesetzt.
Eine Subtraktion kann, rechentechnisch gesehen, auf eine Addition des Minuenden (im Beispiel die Zahl X) und dem Neunerkomplement des Subtrahenden (im Beispiel die Zahl Y) zurückgeführt werden. Das Neunerkomplement wird durch die Differenz jeder Ziffer zur Zahl 9 gebildet. Aufbauend auf dieser Logik kann die Subtraktion als „Nächste Zehnerpotenz + Minuend – Subtrahend – Übertrag = Ergebnis mit oder ohne Übertrag für Nachfolger“ formuliert werden.
Zu beachten ist allerdings hierbei, dass die beiden Zahlen positiv sein müssen und dass der Minuend größer oder gleich dem Subtrahenden sein muss. Folgende Abbildung stellt schematisch dar, wie die beiden Zahlen in 4-Blöcken subtrahiert würden. Wie zu sehen, wandert ein Übertrag von -1 in den nächsten Block sobald im aktuellen Block das Ergebnis kleiner 10.000 ist.
LNUM.SUBTRACT
In der Annahme, dass es wie bei LNUM.SUM möglich sein soll, optional die Blockgröße als Argument an LNUM.SUBTRACT zu übergeben, ist die Syntax der Funktion:
=LNUM.SUBTRACT(Value1;Value2;Blocksize)
Die Funktion setzt zwar voraus, dass beide Zahlen positiv sind, soll jedoch auch den Fall berücksichtigen, wo der Minuend X kleiner als der Subtrahend Y ist und dann ein Minuszeichen vor das Ergebnis setzen.
In einem ersten Schritt müssen die beiden in Textform vorliegenden Zahlen jeweils in ein Array geschrieben werden, dessen Elemente genau N Ziffern beinhalten und hierbei N der Blockgröße Blocksize entspricht. Gleichzeitig sollten die beiden Arrays invertiert werden, sodass wir rückwärts auf die Elemente zugreifen können. Zudem kann es ja vorkommen, wie in unserem Beispiel, dass die beiden Zahlen unterschiedlich lang sind. Das muss berücksichtigt werden, indem die kürzere Zahl mit Nullen aufgefüllt wird.
In meinen vorherigen Artikeln hatte ich bereits die benutzerdefinierten Array-Funktionen ARRAY.FROMTEXT.EX und ARRAY.REVERSE vorgestellt, die das Aufteilen eines Textes in ein Array und das Invertieren eines Arrays vornehmen. Die Syntax dieser beiden Funktionen ist:
ARRAY.FROMTEXT.EX(Value;Blocksize;Direction;FillCharacter;AdditionalRows)
ARRAY.REVERSE(Array)
Die Funktion ARRAY.FROMTEXT.EX erstellt aus dem übergebenen Text in Value ein dynamisches Array, wo jedes Array-Element die im Argument Blocksize definierte Anzahl von Zeichen beinhaltet. Sich aus dem aufzuteilenden Text ergebende kürzere Teiltexte werden mit dem Füllzeichen FillCharacter aufgefüllt und das Argument Direction legt die Richtung fest, von wo aus der Text aufgeteilt wird. Direction kann den Wert 1 für das Aufteilen von links aus gesehen und den Wert 2 für das Aufteilen von rechts aus gesehen annehmen. Das Argument AdditionalRows legt fest, ob zusätzliche Zeilen im Array erzeugt werden sollen, die ebenfalls mit dem Füllzeichen aufgefüllt werden.
Erzeugen wir also zunächst die beiden Arrays und berücksichtigen dabei die Länge der beiden Zahlen. Anschließend invertieren wir die beiden Arrays. Und, erstellen wir auch schon mal das Array mit den Zehnerpotenzen.
In den Zellen C2, D2 und E2 stehen folgende LET-Formeln:
C2=LET(Value1;B2;Value2;B3;Blocksize;B4;
L;AUFRUNDEN(MAX(LÄNGE(0&Value1);LÄNGE(0&Value2))/Blocksize;0);
X;ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value1;Blocksize;2;0;L-
AUFRUNDEN(LÄNGE(0&Value1)/Blocksize;0)));X)
D2=LET(Value1;B2;Value2;B3;Blocksize;B4;
L;AUFRUNDEN(MAX(LÄNGE(0&Value1);LÄNGE(0&Value2))/Blocksize;0);
Y;ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value2;Blocksize;2;0;L-
AUFRUNDEN(LÄNGE(0&Value2)/Blocksize;0)));Y)
E2=LET(Value1;B2;Value2;B3;Blocksize;B4;
L;AUFRUNDEN(MAX(LÄNGE(0&Value1);LÄNGE(0&Value2))/Blocksize;0);
Z;SEQUENZ(L;1;1;0)*10^Blocksize;Z)
In einem nächsten Schritt werden wir die drei Formeln zu einer Formel zusammenführen. Deshalb wurden in den drei Formeln gleichnamige Variablen gewählt, wie die Namen der Eingangsparameter und der Variable L. Die Variable L errechnet die Anzahl der in den Arrays benötigten Zeilen. Um auszuschließen, dass eine leere Zelle übergeben wird, wurde den Werten aus B2 und B3 jeweils eine Null vorangestellt, was die Textlänge der Werte in B2 und B3 jeweils um eins erhöht und auch in die Berechnung von L einfließt.
Die Zehnerpotenzen werden erzeugt, indem über die Funktion SEQUENZ eine Zahlenreihe von Einsen erzeugt wird, die wiederum in unserem Beispiel mit der nächst höheren Zehnerpotenz von vierstelligen Zahlen multipliziert wird. Das ergibt dann 10.000 in unserem Beispiel.
Der nächste Schritt besteht darin, zeilenweise die Berechnung Zehnerpotenz + X – Y durchzuführen. Gleichzeitig führen wir die Formeln zu einer Gesamtformel zusammen. Und, das Ergebnis soll als Text formatiert werden, sodass alle Ergebnisse fünfstellig sind. Die LET-Formel sieht wie folgt aus:
F2=LET(Value1;B2;Value2;B3;Blocksize;B4;
L;AUFRUNDEN(MAX(LÄNGE(0&Value1);LÄNGE(0&Value2))/Blocksize;0);
X;ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value1;Blocksize;2;0;L-
AUFRUNDEN(LÄNGE(0&Value1)/Blocksize;0)));
Y;ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value2;Blocksize;2;0;L-
AUFRUNDEN(LÄNGE(0&Value2)/Blocksize;0)));
Z;SEQUENZ(L;1;1;0)*10^Blocksize;
S;TEXT(Z--X---Y;WIEDERHOLEN(0;Blocksize+1));S))
Wie zu sehen, wurde die Berechnung Z--X---Y in der Ausgabevariable S durchgeführt und S als Text mit 5 Stellen formatiert. Nebenbei, das doppelte Negieren wandelt in Excel eine Zahl in Textform in eine echte Zahl um. In der Tabelle sieht es nun wie folgt aus:
Die erste Ziffer im Ergebnis entscheidet nun darüber, ob ein Übertrag gebildet wird oder nicht. Wir teilen somit das Array mit dem Ergebnis in zwei Teile auf: ein linker Anteil, bestehend aus der fünften Stelle und ein rechter Anteil bestehend aus vier Stellen. Die Bedingung, ob ein Übertrag berücksichtigt werden soll ist, dass die erste Ziffer eine Null ist. Wir ersetzen somit die Nullen durch eine Eins und die Einsen durch eine Null und verschieben die Positionen im Array um eine Position nach unten. Danach ziehen wir gegebenenfalls die Eins vom Teilergebnis ab. Folgende Abbildung zeigt das Ergebnis dieser einzelnen Schritte:
In den Zellen G2 bis J2 sind folgende Formeln abgelegt:
G2=LET(Array;F2#;--LINKS(Array;1))
H2=LET(Array;F2#;Blocksize;B4;RECHTS(Array;Blocksize))
I2=WENN(G2#>0;0;1)
J2=ARRAY.PUSH(I2#;0)
K2=LET(Array1;H2#;Array2;J2#;Blocksize;B4;
TEXT(--Array1-Array2;WIEDERHOLEN(0;Blocksize)))
Die Formel in G2 ermittelt den linken Anteil des Ergebnisses aus der Spalte F und wandelt die Textwerte auch gleich in Zahlen um. Ähnlich für die Formel in H2, wo der rechte Anteil ermittelt wird.
Die Formel in I2 invertiert anhand der WENN-Funktion die Elemente in der Spalte G. Die Formel in J2 nutzt die von mir bereits erstellte und in einem separaten Artikel beschriebenene LAMBDA-Funktion ARRAY.PUSH. Diese Funktion verschiebt die Elemente eines Arrays um eine Position nach unten und erwartet als erstes Argument das Array sowie als zweites Argument einen Standardwert, der an die erste Position des Array geschrieben wird.
Die Formel in K2 zieht schließlich den Wert aus der Spalte J von den Werten in Spalte H ab und formatiert das Ergebnis diesmal als vierstellige Zahl, also mit vorangestellten Nullen.
Fassen wir nun diese 5 Spalten in eine Spalte zusammen. Die LET-Formel dazu lautet:
G2=LET(S;F2#;Blocksize;B4;
U;ARRAY.PUSH(WENN(--LINKS(S;1)>0;0;1);0);
V;RECHTS(S;Blocksize);
T;TEXT(--V-U;WIEDERHOLEN(0;Blocksize));T)
In der Tabelle sind jetzt nur noch die zwei Spalten F und G relevant, um die Funktion LNUM.SUBTRACT aufzubauen.
Bevor wir die zwei Formeln in den Spalten F und G wiederum in eine Formel überführen, empfiehlt es sich, zuerst den Text wieder zusammenzubauen. Dazu invertieren wir wieder das Array aus Spalte G anhand der Funktion ARRAY.REVERSE und nutzen die in Excel integrierte Funktion TEXTKETTE. Dies ergibt dann folgende Formel in G2:
G2=LET(S;F2#;Blocksize;B4;
U;ARRAY.PUSH(WENN(--LINKS(S;1)>0;0;1);0);
V;RECHTS(S;Blocksize);
T;TEXTKETTE(ARRAY.REVERSE(
TEXT(--V-U;WIEDERHOLEN(0;Blocksize))));T)
Das Ergebnis der Subtraktion ist nun 0000 9998 8099 8531 6202 3782. Wie zu sehen, beinhaltet die Ausgabe noch Führungsnullen, die besser entfernt werden sollten. Meine benutzerdefinierte LAMBDA-Funktion TEXT.TRIM.LEFT ist genau für einen solchen Zweck vorgesehen: die Funktion durchläuft eine Zeichenkette von links nach rechts und entfernt ein anzugebendes Zeichen aus dem Text solange bis das Folgezeichen nicht mehr dem zu entfernenden Zeichen entspricht. Kapseln wir also nochmals T in diese Funktion, was nun ergibt:
G2=LET(S;F2#;Blocksize;B4;
U;ARRAY.PUSH(WENN(--LINKS(S;1)>0;0;1);0);
V;RECHTS(S;Blocksize);
T;TEXT.TRIM.LEFT(TEXTKETTE(ARRAY.REVERSE(
TEXT(--V-U;WIEDERHOLEN(0;Blocksize))));0);T)
Das Ergebnis ist nun 9998 8099 8531 6202 3782, was schon mal besser aussieht. Jetzt könnte aber der Fall eintreten, wo das Ergebnis der Subtraktion gleich Null ist. TEXT.TRIM.LEFT würde dann eine leere Zeichenkette zurückgeben. Von daher bauen wir noch eine letzte Überprüfung ein:
G2=LET(S;F2#;Blocksize;B4;
U;ARRAY.PUSH(WENN(--LINKS(S;1)>0;0;1);0);
V;RECHTS(S;Blocksize);
T;TEXT.TRIM.LEFT(TEXTKETTE(ARRAY.REVERSE(
TEXT(--V-U;WIEDERHOLEN(0;Blocksize))));0);
WENN(LÄNGE(T)>0;T;0))
Der nächste Schritt besteht darin, die Formeln aus F2 und G2 zusammenzuführen. Das ergibt nun folgende Formel:
G2=LET(Value1;B2;Value2;B3;Blocksize;B4;
L;AUFRUNDEN(MAX(LÄNGE(0&Value1);LÄNGE(0&Value2))/Blocksize;0);
X;ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value1;Blocksize;2;0;L-
AUFRUNDEN(LÄNGE(0&Value1)/Blocksize;0)));
Y;ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value2;Blocksize;2;0;L-
AUFRUNDEN(LÄNGE(0&Value2)/Blocksize;0)));
Z;SEQUENZ(L;1;1;0)*10^Blocksize;
S;TEXT(Z--X---Y;WIEDERHOLEN(0;Blocksize+1));
U;ARRAY.PUSH(WENN(--LINKS(S;1)>0;0;1);0);
V;RECHTS(S;Blocksize);
T;TEXT.TRIM.LEFT(TEXTKETTE(ARRAY.REVERSE(
TEXT(--V-U;WIEDERHOLEN(0;Blocksize))));0);
WENN(LÄNGE(T)>0;T;0))
Zu Anfang des Artikels erwähnte ich die Anforderung, dass der Wert zur Blockgröße optional sein sollte. Insofern muss auch eine Überprüfung stattfinden, ob der Eingangsparameter Blocksize gesetzt wurde oder nicht. Und, falls dieser gesetzt wurde, darf der Wert von Blocksize nicht größer als 14 sein. Wir erweitern die Formel noch um die Überprüfung über die Variable B:
G2=LET(Value1;B2;Value2;B3;Blocksize;B4;
B;WENN(ODER(ISTLEER(Blocksize);NICHT(ISTZAHL(Blocksize)));10;
WENN(Blocksize>14;14;Blocksize));
L;AUFRUNDEN(MAX(LÄNGE(0&Value1);LÄNGE(0&Value2))/B;0);
X;ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value1;B;2;0;L-
AUFRUNDEN(LÄNGE(0&Value1)/B;0)));
Y;ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value2;B;2;0;L-
AUFRUNDEN(LÄNGE(0&Value2)/B;0)));
Z;SEQUENZ(L;1;1;0)*10^B;
S;TEXT(Z--X---Y;WIEDERHOLEN(0;B+1));
U;ARRAY.PUSH(WENN(--LINKS(S;1)>0;0;1);0);
V;RECHTS(S;B);
T;TEXT.TRIM.LEFT(TEXTKETTE(ARRAY.REVERSE(
TEXT(--V-U;WIEDERHOLEN(0;B))));0);
WENN(LÄNGE(T)>0;T;0))
In dieser Form würde die Funktion erwarten, dass das erste Argument Value1 und somit die erste Zahl größer ist als der Wert in Value2. Damit die Funktion jedoch unabhängig von der Reihenfolge der Zahlengröße ist, muss noch eine Überprüfung eingebaut werden.
In einem vorangegangenen Artikel hatte ich die benutzerdefinierte Funktion LNUM.COMPARE vorgestellt, die zwei große Zahlen X und Y miteinander vergleicht und -1 zurückgibt, wenn X < Y und +1 zurückgibt, wenn X > Y ist. Bei Gleichheit gibt die Funktion 0 zurück. Bauen wir die Überprüfung ein, erhalten wir folgende, zugegeben, sehr lange Formel:
G2=LET(Value1;B10;Value2;B11;Blocksize;B4;
B;WENN(ODER(ISTLEER(Blocksize);NICHT(ISTZAHL(Blocksize)));10;
WENN(Blocksize>14;14;Blocksize));
C;LNUM.COMPARE(0&Value1;0&Value2);
L;AUFRUNDEN(MAX(LÄNGE(0&Value1);LÄNGE(0&Value2))/B;0);
X;ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&WENN(C<0;Value2;Value1);B;2;0;L-
AUFRUNDEN(LÄNGE(0&WENN(C<0;Value2;Value1))/B;0)));
Y;ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&WENN(C<0;Value1;Value2);B;2;0;L-
AUFRUNDEN(LÄNGE(0&WENN(C<0;Value1;Value2))/B;0)));
Z;SEQUENZ(L;1;1;0)*10^B;
S;TEXT(Z--X---Y;WIEDERHOLEN(0;B+1));
U;ARRAY.PUSH(WENN(--LINKS(S;1)>0;0;1);0);
V;RECHTS(S;B);
T;TEXT.TRIM.LEFT(TEXTKETTE(ARRAY.REVERSE(
TEXT(--V-U;WIEDERHOLEN(0;B))));0);
WENN(LÄNGE(T)>0;WENN(C<0;"-";"")&T;0))
Der Vergleich der beiden Zahlen findet in der Variable C statt. Ist C größer Null, ändert sich nichts an der vorherigen Version der Formel. Ist jedoch C kleiner Null, werden die Eingangswerte zur Erzeugung der Arrays vertauscht. Und in der Ausgabe ein Minuszeichen vorengestellt.
Das war’s. Es verbleibt im Namensmanager den Namen LNUM.SUBTRACT anzulegen und diesen Namen folgende LAMBDA-Funktion zuzuweisen:
=LAMBDA(Value1;Value2;Blocksize;
LET(B;WENN(ODER(ISTLEER(Blocksize);NICHT(ISTZAHL(Blocksize)));10;
WENN(Blocksize>14;14;Blocksize));
C;LNUM.COMPARE(0&Value1;0&Value2);
L;AUFRUNDEN(MAX(LÄNGE(0&Value1);LÄNGE(0&Value2))/B;0);
X;ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&WENN(C<0;Value2;Value1);B;2;0;L-
AUFRUNDEN(LÄNGE(0&WENN(C<0;Value2;Value1))/B;0)));
Y;ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&WENN(C<0;Value1;Value2);B;2;0;L-
AUFRUNDEN(LÄNGE(0&WENN(C<0;Value1;Value2))/B;0)));
Z;SEQUENZ(L;1;1;0)*10^B;
S;TEXT(Z--X---Y;WIEDERHOLEN(0;B+1));
U;ARRAY.PUSH(WENN(--LINKS(S;1)>0;0;1);0);
V;RECHTS(S;B);
T;TEXT.TRIM.LEFT(TEXTKETTE(ARRAY.REVERSE(
TEXT(--V-U;WIEDERHOLEN(0;B))));0);
WENN(LÄNGE(T)>0;WENN(C<0;"-";"")&T;0)))
Aufrufen lässt sich dann z.B. LNUM.SUBTRACT beispielsweise wie folgt:
=LNUM.SUBTRACT(B2;B3;B4)
=LNUM.SUBTRACT(B3;B2;12)
=LNUM.SUBTRACT(B2;B3;)
Fazit
Mit den beiden Funktionen LNUM.SUM und LNUM.SUBTRACT wird es nun möglich sein, eine auf Formeln basierende Multiplikation großer Zahlen in Excel zu implementieren.
Happy Exceling :-)
