Vor einer halben Ewigkeit – also ca. 10 Jahren, um genau zu sein – hatte ich in diesem Blog ein Add-In publiziert, um große Zahlen z.B. mit über 30 Stellen, in Excel zu addieren. Nun ist ja kürzlich die LAMBDA-Funktion in Excel erschienen, die auch rekursiv angewendet werden kann. Ich hatte mich dann gefragt, ob es mit einer LAMBDA-Formel möglich ist, eine reine Formellösung zur Addition großer Zahlen zu entwickeln. Vorab, ja das geht.
Dieser Artikel stellt die Funktion LNUM.SUM vor, die Addition sehr großer Zahlen in Excel übernimmt. In diesem Artikel wird zudem auf einige von mir bereits erstellte benutzerdefinierte LAMBDA-Funktion zurückgegriffen. Diese Funktionen sind an entsprechender Stelle verlinkt.
Große Zahlen addieren
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. Eine Addition dieser beiden Zahlen über eine Excel-Formel, die die Texte in Zahlen umwandelt, führt zu fehlenden Stellen ab der 15. Stelle. Folgende Abbildung verdeutlicht das Problem. Zur besseren Lesbarkeit habe ich die Zahlen über ein Leerzeichen gruppiert, weshalb in der Formel WECHSELN zum Entfernen des Leerzeichens verwendet wurde.
Das Problem liesse sich beispielsweise lösen, indem eine benutzerdefinierte Funktion in VBA implementiert wird, die die beiden Texte in Zahlen umwandelt, addiert und das Ergebnis wieder als Text in einer Zelle ablegt. Eine solche VBA-Funktion könnte einen ähnlichen Code, wie nachfolgend sehr vereinfacht aufgeführt, beinhalten:
x = CDec(Replace(Range("B2").Value, " ", ""))
y = CDec(Replace(Range("B3").Value, " ", ""))
Range("B5").Value = "'" & (x + y)
Der von CDec adressierbare Zahlenbereich erreicht eine Genauigkeit von bis zu 27 Stellen für positive Ganzzahlen. Dies ist aber nicht genug, denn große Zahlen können ja auch mal 50 Stellen oder mehr beinhalten. Statt nun auf die Typkonvertierungsfunktionen in VBA zu setzen und den kompletten Text in eine Zahl umzuwandeln, ist es sinnvoller einen Algorithmus entwickeln, der die zwei Texte ziffernweise von rechts nach links durchläuft, die Addition durchführt und einen eventuell enstehenden Übertrag berücksichtigt. So sind die ersten beiden Ziffern in unserem Beispiel die Zahlen 6 und 4, was 10 ergibt und somit den Übertrag von 1.
Das Durchlaufen der einzelnen Ziffern führt bei wirklich sehr großen Zahlen jedoch zu vielen Schleifendurchläufen. Diese lassen sich wiederum reduzieren, indem statt einer einzelnen Ziffer ein Ziffernblock verwendet wird. So hat die ersten Zahl 20 Stellen. Das macht 20 Schleifendurchläufe. Gruppiere ich die Zahlen in 4-Blöcke, reduziert sich schon die Anzahl der Schleifendurchläufe auf 5. Auch hier ist ein eventuell enstehender Übertrag beim Addieren der Blöcke zu berücksichtigen.
Folgende Abbildung stellt schematisch dar, wie die Zahlen in 4-Blöcken addiert würden. Wie zu sehen, wandert der Übertrag von 1 in den nächsten Block, um dort zu dem Ergebnis aufaddiert zu werden.
LNUM.SUM
Was muss LNUM.SUM als reine Formellösung können? Zunächst muss die Funktion die beiden Zahlen in Textform in einzelne Blöcke aufteilen. Ist eine Zahl kürzer als die andere, müssen die fehlenden Stellen bei der kürzeren Zahl mit Nullen aufgefüllt werden. Anschließend müssen Teiladditionen stattfinden, die eventuell enstandenen Übertragswerte um eine Position verschoben und zu dem entsprechenden Teilergebnis aufaddiert werden. Schließlich muss LNUM.SUM die Teilergebnisse wieder zu einem Text zusammensetzen und zurückgeben.
In der Annahme, dass es möglich sein soll, optional die Blocklänge als Argument an LNUM.SUM zu übergeben, ist die Syntax der Funktion somit:
=LNUM.SUM(Value1;Value2;Blocksize)
In meinen vorherigen Artikeln hatte ich bereits einige benutzerdefinierte LAMBDA-Funktionen vorgestellt, die es ermöglichen aus einem Text ein Array zu erstellen sowie ein Array zu invertieren. Dies sind:
ARRAY.FROMTEXT.EX(Value;Blocksize;Direction;FillCharacter;AdditionalRows)
ARRAY.REVERSE(Array)
Die Funktion ARRAY.FROMTEXT.EX erstellt aus einem Text ein dynamisches Array, wo jedes Element des Arrays die im Argument Blocksize definierte Anzahl von Zeichen beinhaltet. Eventuell sich aus dem aufzuteilenden Text ergebende kürzere Teiltexte werden mit dem Füllzeichen FillCharacter aufgefüllt. Das Argument Direction legt die Richtung fest, von wo aus der Text aufgeteilt wird. Ist Direction gleich 2, wird der Text von rechts aus aufgeteilt. Das Argument AdditionalRows legt fest, ob zusätzliche Zeilen im Array erzeugt werden sollen, die ebenfalls mit dem Füllzeichen aufgefüllt werden.
Die Funktion ARRAY.REVERSE ist recht einfach und invertiert ein Array, d.h. das letzte Element im Array wird zum ersten Element und das erste Element zum letzten Element im Array.
Beginnen wir somit mit der Aufteilung unserer Beispielzahlen in Blöcke und rufen für die jeweiligen Zahlen ARRAY.FROMTEXT.EX auf und invertieren auch gleich das Ergebnis.
Die Formeln lauten dann in C2 und D2:
C2=ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&B2;B4;2;0;0))
D2=ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&B3;B4;2;0;0))
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, die dann respektive 21 und 17 Zeichen betragen.
Wie zu sehen, sind die beiden Arrays unterschiedlich groß. Um die beiden Arrays addieren zu können, sollten die Arrays jedoch die gleiche Größe haben. Somit müssen wir das zweite Array mit Nullen auffüllen. Dazu ändern wir die beiden Formeln und berücksichtigen die maximale Länge der beiden Zahlen. Und wir fangen schon mal an, die Formeln in LET zu kapseln. Es ergeben sich:
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)
Beide LET-Formeln berechnen in der Variable L die maximale Anzahl an entstehenden Blöcken – also letztlich die Anzahl der Zeilen im Ergebnisarray. Anschließend dient die Variable L dazu, die Anzahl an zusätzlich notwendigen Zeilen (AdditionalRows) in ARRAY.FROMTEXT.EX zu ermitteln. Das Ergebnis sieht nun wie folgt aus:
Der nächste Schritt besteht darin, die Werte der Arrays zeilenweise zu addieren. Da unser Ziel ja eine Gesamtformel ist, kombinieren wir beide vorangegangenen Formeln zu einer Formel. X und Y werden hierbei addiert und das Ergebnis als Text mit vorangestellten Nullen formatiert, was über die Funktion TEXT erreicht wird.
E2=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;TEXT(--X--Y;WIEDERHOLEN(0;Blocksize+1));Z)
Das Ergebnis in Spalte E sieht dann wie folgt aus:
Jetzt ist es an der Zeit, die Werte in der Spalte E in zwei Spalten bzw. zwei Arrays aufzuteilen: ein Array, das die Werte zum Übertrag enthält und ein Array, das die Werte der Teilsummen enthält. Dazu lassen sich die zwei folgenden Formeln verwenden:
F2=LET(Array;E2#;LINKS(Array;1))
G2=LET(Array;E2#;Blocksize;B4;RECHTS(Array;Blocksize))
Anschließend müssen die Elemente im Array zum Übertrag um eine Position nach unten verschoben werden, damit diese mit dem entsprechenden Block addiert werden können. Folgende Abbildung zeigt das Auftrennen der Werte aus der Spalte E in die Spalten F und G, das Verschieben des Übertrags um eine Position in Spalte H und die Addition des Übertrags in Spalte I.
Zum Verschieben der Elemente eines Arrays um eine Position nach unten hatte ich mir bereits die benutzerdefinierte Funktion ARRAY.PUSH geschrieben. Diese Funktion erwartet als erstes Argument ein Array und als zweites Argument einen Standardwert, der an die erste Position des Array geschrieben wird. Die Formeln in H2 und I2 lauten:
H2=ARRAY.PUSH(F2#;0)
I2=LET(Array1;G2#;Array2;H2#;Blocksize;B4;
TEXT(--Array1--Array2;WIEDERHOLEN(0;Blocksize+1)))
Wie in obiger Abbildung zu sehen, beinhaltet das Ergebnis in Spalte I wieder einen Übertrag, der dadurch entsteht, dass 9999 mit 1 addiert wird. Somit ist eine zweite Iteration erforderlich, die sich statt auf Spalte E nun auf Spalte I bezieht. In folgender Abbildung sind die Ergebnisse der zweiten Iteration zu sehen.
Die Formeln wären entsprechend für die Spalten J, K, L und M:
J2=LET(Array;I2#;LINKS(Array;1))
K2=LET(Array;I2#;Blocksize;B4;RECHTS(Array;Blocksize))
L2=ARRAY.PUSH(J2#;0)
M2=LET(Array1;K2#;Array2;L2#;Blocksize;B4;
TEXT(--Array1--Array2;WIEDERHOLEN(0;Blocksize+1)))
Dieses Beispiel zeigt, dass es mehr als nur eine Iteration geben kann, da das Addieren eines Übertrags zu einem Block wieder zu einem Übertrag führen kann. Somit muss LNUM.SUM eine solche Iteration durchführen können, bevor ein Gesamtergebnis aus den Teilergebnissen wieder zusammengesetzt wird.
LAMBDA-Funktionen sind in der Lage, sich selbst – also rekursiv – aufzurufen. Es muss allerdings ein Abbruchkriterium definiert sein. In unserem Fall lässt sich die Iteration über eine rekursive Funktion abbilden, wo die Rekursion dann abgebrochen werden soll, wenn kein Übertrag mehr vorhanden ist.
Um eine solche rekursive Funktion aufzubauen, fassen wir zunächst einmal die 4 Formeln der Iteration in eine Formel zusammen, und beziehen die erste Iteration auf die Spalte E über die Variable Data.
F2=LET(Data;E2#;Blocksize;$B$4;
C;LINKS(Data;1);
L;ARRAY.PUSH(C;0);
R;RECHTS(Data;Blocksize);
TEXT(--L--R;WIEDERHOLEN(0;Blocksize+1)))
Somit erscheint das Ergebnis der ersten Iteration in Spalte F, wie folgend abgebildet zu sehen. Hilfspalten werden nun nicht mehr benötigt. Kopieren wir die Formel nach rechts, passt sich diese automatisch an und verwendet das vorherige Ergebnis. In Spalte G werden dann die Werte aus Spalte F und in H die Werte aus Spalte G verwendet. Das Ergebnis in Spalte H verändert sich nicht mehr im Verhältnis zum Ergebnis in Spalte G. Spätestens hier haben wir unser Abbruchkriterium erreicht.
Um die Ergebnisse der drei Spalten in eine einzige Berechnung zusammenzufassen, definieren wir jetzt die Iterationsfunktion als LAMBDA-Formel:
=LAMBDA(Data;Blocksize;
LET(C;LINKS(Data;1);
L;ARRAY.PUSH(C;0);
R;RECHTS(Data;Blocksize);
WENN(SUMME(--C)<1;Data;
LNUM.SUM.ITERATOR(TEXT(--L--R;
WIEDERHOLEN(0;Blocksize+1));Blocksize))))
Die Formel erwartet die zwei Argumente Data und Blocksize, die vorher in der LET-Formel ebenfalls definiert wurden. Die LET-Formel wird allerdings um eine WENN-Abfrage ergänzt: ist die Summe der Übertragswerte kleiner 1, wird Data, also der Eingangsparameter, zurückgegeben. Sind noch Übertragswerte vorhanden, wird die Summe der verschobenen Übertragswerte und der Teilergebnisse wieder an LNUM.SUM.ITERATOR übergeben und somit eine Iteration durchgeführt.
Wir legen den Namen LNUM.SUM.ITERATOR im Namensmanager an und geben dort als Formel die vorherige Formel an. Die Funktion lässt sich anschließend verwenden und die Spalten G und H löschen. In Spalte F steht nun:
F2=LNUM.SUM.ITERATOR(E2#;B4)
Wir sind nun an einem Punkt angelangt, wo zwei relevante Spalten übrigbleiben: in E2 steht die Formel, die aus den beiden Zahlen zwei gleich große Arrays generiert und die Elemente der Arrays zeilenweise addiert. Die Spalte F enthält die Iteration mit den Werten aus Spalte E.
Der nächste Schritt besteht jetzt darin, die in Spalte F berechneten Werte zu dem Gesamtergebnis zusammenzusetzen. Wie oben zu sehen, sind die Teilergebnisse in Spalte E als 5-stellige Zahlen formatiert. Also eine Stelle mehr als die vordefinierte Blockgröße von 4. Wir müssen somit die erste Null entfernen. Das erreichen wir mit der Funktion RECHTS. Zudem muss das Array wieder invertiert werden. Und, wir setzen anschließend den Text anhand von TEXTKETTE zusammen. Bezogen auf die Spalte F ergibt sich somit folgende Formel:
=LET(Data;F2#;Blocksize;B4;
T;TEXTKETTE(ARRAY.REVERSE(RECHTS(Data;Blocksize)));T)
Als Ergebnis erhalten wir, hier im Text nur zur besseren Lesbarkeit mit einem Leerzeichen in 4-Blöcken formatiert: 0001 0000 2884 5444 0932 0930. Das Ergebnis beinhaltet jedoch noch einige Nullen am Anfang des Textes. Schön wäre es, wenn diese entfernt würden.
In einem der vorangegangen Artikel habe ich die Funktion TEXT.TRIM.LEFT implementiert, die genau das übernimmt: ein Zeichen von links aus gesehen solange zu entfernen, bis das Folgezeichen nicht mehr dem zu entfernenden Zeichen entspricht.
Deswegen kapseln wir das Ergebnis nochmals in TEXT.TRIM.LEFT. Und, falls dann das Ergebnis ein leerer Text sein sollte, weil die Summe Null war, fügen wir noch eine Überprüfung ein und setzen das Ergebnis auf Null. Die Formel lautet nun:
=LET(Data;F2#;Blocksize;B4;
T;TEXT.TRIM.LEFT(TEXTKETTE(ARRAY.REVERSE(RECHTS(Data;Blocksize)));0);
WENN(LÄNGE(T)>0;T;0))
Es ist fast geschafft. Es fehlt noch, dass wir alle Formeln zu einer Gesamtformel zusammenführen. Das ist relativ schnell erledigt und unsere LAMBDA-Funktion lautet nun:
=LAMBDA(Value1;Value2;Blocksize;
LET(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;LNUM.SUM.ITERATOR(TEXT(--X--Y;WIEDERHOLEN(0;B+1));B);
T;TEXT.TRIM.LEFT(TEXTKETTE(ARRAY.REVERSE(RECHTS(Z;B)));0);
WENN(LÄNGE(T)>0;T;0)))
Das das Argument zur Blockgröße optional sein soll, müssen wir den Fall abfangen, wo kein Wert für Blocksize übergeben wurde. Das geschieht in der WENN-Abfrage für die Variable B. Und, wenn wir schon mal dabei sind, begrenzen wir auch die möglichen Werte von Blocksize auf maximal 14.
Es verbleibt im Namensmanager den Namen LNUM.SUM anzulegen als Formel obige Formel anzugeben. Aufrufen lässt sich dann z.B. LNUM.SUM beispielsweise wie folgt:
=LNUM.SUM(B2;B3;B4)
=LNUM.SUM(B2;B3;12)
=LNUM.SUM(B2;B3;)
Eine Kleinigkeit kann noch optional erledigt werden: wenn in eine Excel-Zelle =LN eingetippt wird, erscheint auch die Iterationsfunktion LNUM.SUM.ITERATOR in der IntelliSense-Liste. Diese ist aber eher als Hilfsfunktion gedacht und sollte nicht direkt aufgerufen werden und somit nicht in der IntelliSense-Liste auftauchen. Von daher können wir die Funktion im Namensmanager umbenennen und ihr ein Zeichen voranstellen, was in Funktionsnamen in der Regel nicht verwendet wird und somit der User nicht geneigt ist, das Zeichen zu verwenden. Das ist beispielsweise ein Unterstrich. Die Funktion würde dann _LNUM.SUM.ITERATOR heißen.
Abschließend hatte ich noch die Performance getestet. Dazu habe ich mir eine Formel erstellt, die bis zu 64 Zeichen lange Zahlen per Zufall generiert und diese Formel in die zwei Zellen A1 und B1 abgelegt:
=""&LET(X;ZUFALLSBEREICH(1;64);
Y;SEQUENZ(1;X;1;0);
Z;ZUFALLSMATRIX(1;X;0;9;WAHR);
T;TEXT.TRIM.LEFT(TEXTKETTE(Y*Z);0);
WENN(LÄNGE(T)<1;0;T))
In Zelle C1 habe ich dann die Summe der beide Werte aus A1 und B1 anhand der Funktion LNUM.SUM gebildet. Und schließlich die Formeln bis zur Zeile 10.000 fortgeführt. Ich konnte bei den Berechnungen keine nennenswerten Verzögerungen feststellen. Die Berechnungsdauer liegt so zwischen 1 bis 2 Sekunden. Und das in einer virtuellen Maschine. Wow!
Fazit
Wirklich beeindruckend, was LAMBDA-Funktionen leisten können. Eine der nächsten Funktionen, die ich implementieren werde, ist eine Funktion zur Multplikation großer Zahlen. Das ist wesentlich komplexer. Als Algorithmus werde ich dann versuchen, den Karatsuba-Algorithmus zu verwenden. Ich freue mich, wie immer, über Verbesserungsvorschläge und Optimierungen zu der Funktion LNUM.SUM.
Happy Exceling :-)