In meinem vorangegangen Artikel hatte ich meine benutzerdefinierte Funktion LNUM.SUM vorgestellt, die anhand der LAMBDA-Funktion eine reine Formellösung zur Addition sehr großer Zahlen in Excel ermöglicht. Als nächste Funktion würde ich gerne die Funktion LNUM.SUBTRACT implementieren, die dann sehr große Zahlen subtrahiert.
Der Algorithmus, den ich für die Subtraktion großer Zahlen vorgesehen habe, beruht jedoch auf der Verwendung des Neunerkomplements und darauf, dass die größere Zahl von der kleineren Zahl abgezogen wird. Deswegen benötige ich zunächst eine Funktion, die mir aus zwei großen Zahlen die größere und kleinere Zahl ermittelt.
Dieser Artikel stellt die Funktion LNUM.COMPARE vor, die den Vergleich zwei großer Zahlen durchführt und einen entsprechenden Wert zurückgibt. Als Nebeneffekt lassen sich dann LNUM.MAX und LNUM.MIN implementieren, die respektive die größere oder kleinere Zahl zurückgeben.
LNUM.COMPARE
Die Funktion LNUM.COMPARE soll zwei sehr große Zahlen X und X miteinander vergleichen und den Wert -1 zurückgeben, wenn X kleiner Y ist und den Wert 1 wenn X größer Y ist. Sind X und Y gleich, soll die Funktion 0 zurückgeben. Die Syntax lautet:
=LNUM.COMPARE(Value1;Value2)
Es gibt verschiedene Möglichkeiten zwei große Zahlen in Textform zu vergleichen. Eine Möglichkeit wäre, zunächst beide Zahlen durch das Voranstellen von Nullen auf die gleiche Länge zu bringen und rekursiv die Ziffern beider Zahlen von links nach rechts zu vergleichen. Eine weitere Möglichkeit wäre, die beiden Zahlen ebenfalls zuerst auf die gleiche Länge zu bringen, dann jedoch jeweils ein Array aus den Zahlen zu generieren und die erste Position zu suchen, an der sich die Ziffern in den Arrays unterscheiden. Das erspart die Rekursion und ist die Methode, die nachfolgend verwendet wird.
Da diese Methode auch mit kürzeren Zahlen funktioniert, die als Text in Zellen abgelegt wurden, habe ich im nachfolgenden Beispiel die Zahlen 99 837 656 und 99 531 083 verwendet.
Zur Umwandlung eines Textwertes in ein Array hatte ich bereits die benutzerdefinierte Array-Funktion ARRAY.FROMTEXT implementiert, die wie folgt lautet:
ARRAY.FROMTEXT=LAMBDA(Value;TEIL(Value;SEQUENZ(LÄNGE(Value));1))
Wenden wir diese Funktion auf die beiden Zahlen in B2 und B3 an. Um leere Zellen abzufangen, stellen wir den Zellwerten grundsätzlich eine Null voran. Gleichzeitig überprüfen wir auch die Länge der Zahlen und füllen eine der beiden Zahlen mit Nullen auf. Die LET-Formeln in C2 und D2 lauten dann:
C2=LET(Value1;B2;Value2;B3;
L;MAX(LÄNGE(0&Value1);LÄNGE(0&Value2));
X;ARRAY.FROMTEXT(WIEDERHOLEN(0;L-LÄNGE(0&Value1))&0&Value1);X)
D2=LET(Value1;B2;Value2;B3;
L;MAX(LÄNGE(0&Value1);LÄNGE(0&Value2));
Y;ARRAY.FROMTEXT(WIEDERHOLEN(0;L-LÄNGE(0&Value2))&0&Value2);Y)
Wie zu sehen, sind beide LET-Formeln vom Aufbau her gleich. Wir können diese Formel somit in eine Formel zusammenfassen und auch schon gleich den Vergleich mit einbauen.
E2=LET(Value1;B2;Value2;B3;
L;MAX(LÄNGE(0&Value1);LÄNGE(0&Value2));
X;ARRAY.FROMTEXT(WIEDERHOLEN(0;L-LÄNGE(0&Value1))&0&Value1);
Y;ARRAY.FROMTEXT(WIEDERHOLEN(0;L-LÄNGE(0&Value2))&0&Value2);
P;X<>Y;P)
Das Ergebnis sieht dann wie folgt aus.
Die erste Position, wo sich die Ziffern im Array unterscheiden, ist somit die Position 4. Um diesen Wert zu finden, können wir die Funktion VERGLEICH verwenden. Die LET-Formel lautet nun:
E2=LET(Value1;B2;Value2;B3;
L;MAX(LÄNGE(0&Value1);LÄNGE(0&Value2));
X;ARRAY.FROMTEXT(WIEDERHOLEN(0;L-LÄNGE(0&Value1))&0&Value1);
Y;ARRAY.FROMTEXT(WIEDERHOLEN(0;L-LÄNGE(0&Value2))&0&Value2);
P;WENNFEHLER(VERGLEICH(WAHR;X<>Y;0);1);P)
Es verbleibt somit nur noch die beiden Ziffern miteinander zu vergleichen und daraus den Rückgabewert der Funktion zu bestimmen.
E2=LET(Value1;B2;Value2;B3;
L;MAX(LÄNGE(0&Value1);LÄNGE(0&Value2));
X;ARRAY.FROMTEXT(WIEDERHOLEN(0;L-LÄNGE(0&Value1))&0&Value1);
Y;ARRAY.FROMTEXT(WIEDERHOLEN(0;L-LÄNGE(0&Value2))&0&Value2);
P;WENNFEHLER(VERGLEICH(WAHR;X<>Y;0);1);
WENN(--INDEX(X;P;1)>--INDEX(Y;P;1);1;
WENN(--INDEX(X;P;1)<--INDEX(Y;P;1);-1;0)))
Die Zeichenfolge -- in der Formel zum Vergleich sorgt dafür, dass die Ziffer auf jeden Fall in eine Zahl umgewandelt wird. Nun lässt sich aus der Formel die LAMBDA-Funktion erstellen, die wie folgt lautet:
=LAMBDA(Value1;Value2;
LET(L;MAX(LÄNGE(0&Value1);LÄNGE(0&Value2));
X;ARRAY.FROMTEXT(WIEDERHOLEN(0;L-LÄNGE(0&Value1))&0&Value1);
Y;ARRAY.FROMTEXT(WIEDERHOLEN(0;L-LÄNGE(0&Value2))&0&Value2);
P;WENNFEHLER(VERGLEICH(WAHR;X<>Y;0);1);
WENN(--INDEX(X;P;1)>--INDEX(Y;P;1);1;
WENN(--INDEX(X;P;1)<--INDEX(Y;P;1);-1;0))))
Legen wir im Namensmanager den Namen LNUM.COMPARE an, steht uns dann die benutzerdefinierte Funktion zur Verfügung.
LNUM.MAX und LNUM.MIN
Die Funktion LNUM.COMPARE lässt sich prima dazu verwenden, um aus den beiden Zahlen den kleineren und den größeren Wert zu ermitteln. Die Formeln für LNUM.MAX und LNUM.MIN lauten dann als LAMBDA-Funktionen:
LNUM.MAX=LAMBDA(Value1;Value2;
WENN(LNUM.COMPARE(Value1;Value2)>0;Value1;Value2))
LNUM.MIN=LAMBDA(Value1;Value2;
WENN(LNUM.COMPARE(Value1;Value2)<0;Value1;Value2))
Fazit
Nun steht der Implementierung der Funktion, die große Zahlen subtrahiert nichts mehr im Wege.
Happy Exceling :-)
