Verschachtelter Suchlauf

Von Steffen. | 5 Mai, 2021 - 17:11

Liebes RagTime-Forum!

Bin mal wieder am Basteln ... :)

Also, ich habe eine Rechnungsdatenbank (Daten von Ausgangsrechnungen, Container "Daten") mit folgenden (relevanten) Spalten:
E: Rechnungsnummer (1 … 16000)
H: Rechnungsbetrag

und eine Buchhaltungsdatenbank (Daten von Zahlungseingängen, Container "RechnungenBez") mit folgenden (relevanten) Spalten:
E: Rechnungsnummer (1 … 16000)
H: Zahlbetrag

Da manche Zahlungen nicht auf einmal eingehen, sondern z.B. in Raten bezahlt werden, gibt es u.U. zu einer Rechnung in der Buchhaltungsdatenbank mehrere Zeilen.

Um nun die Vollständigkeit der jeweiligen Zahlungen zu überprüfen, habe ich bisher in Spalte I der Rechnungsdatenbank in jeder Zeile folgenden Suchlauf geformelt, der die Eingänge zur jeweiligen Rechnung aufsummiert:

Suchlauf(RechnungenBez!$E:$E;Und( NICHT IstFehl(LfdZelle);LfdZelle=Text(E1));0;LfdResultat+Index(RechnungenBez!$H:$H;LfdIndex))
...
Suchlauf(RechnungenBez!$E:$E;Und( NICHT IstFehl(LfdZelle);LfdZelle=Text(E16000));0;LfdResultat+Index(RechnungenBez!$H:$H;LfdIndex))

In einer weiteren Spalte (J) konnte dann die Übereinstimmung geprüft werden, was bisher ganz gut geklappt hat und auch relativ wenig Rechenzeit erforderte.

Nun stach mich heute der Hafer, und ich versuchte das Ganze in einen verschachtelten statt bisher 16.000 einzelne Suchläufe zu packen, und zwar mit folgender Formel in einem separaten Rechenblatt - und auch erst noch in einem Knopf verpackt, da ich mir schon dachte, dass das etwas rechenintensiv werden könnte:

Knopf('Bezahlung';VSuchlauf(Daten!$E:$E; NICHT IstFehl(LfdZelle);0;SetzeZelle(Suchlauf(RechnungenBez!$E:$E;Und( NICHT IstFehl(LfdZelle);LfdZelle=Text(Index(Daten!$E:$E;LfdIndex)));0;LfdResultat+Index(RechnungenBez!$H:$H;LfdIndex));Daten!$I:$I;LfdIndex)))

Mein armer Mac rechnet sich jetzt allerdings einen Wolf und ich bin mir nicht sicher, ob das an meiner möglicherweise falschen Formel liegt, oder einfch nur an der Komplexität ...

Hat mir jemand einen Tipp?

Mac OS 10.15.6, RagTime 6.6.2 Built 1876.

Viele Grüße
Steffen

Posted in RagTime-Forum | Anmelden oder registrieren um Kommentare einzutragen · Vorheriges Thema · Nächstes Thema »

Re: Verschachtelter Suchlauf

#13036 On 6 Mai, 2021 09:45 Thomas Kaegi said,

Hallo Steffen

In deinen beiden Suchläufen gibst du NICHT IstFehl(LfdZelle) als (Teil-)Bedingung an. Dort dürfte der Grund für die lange Rechenzeit liegen. Ich gehe davon aus, dass keine Zelle in der Spalte mit den Rechnungsnummern einen Fehler enthält, diese Bedingung also immer erfüllt ist. Damit wird der innere Suchlauf also für alle 16000 Zeilen durchlaufen. Und im inneren Suchlauf ist die erste Teilbedingung wieder die gleiche, so dass auch hier alle 16000 Zeilen auf die Erfüllung dieser Bedingung geprüft werden. 16000 * 16000 Tests - das kann schon eine Weile dauern. Diese (Teil-)Bedingung müsste nach meiner Ansicht NICHT IstLeer (LfdZelle) heissen.

Ich habe dir ja unterdessen per Mail einen Lösungsvorschlag geschickt, bei dem im Wesentlichen (mit obigem Unterschied) meine Formel deiner entspricht. Wenn du deren Funktionieren bestätigst werde ich die Lösung hier publizieren.

Freundlicher Gruss, Thomas

Anmelden oder registrieren um Kommentare einzutragen

Re: Verschachtelter Suchlauf

#13037 On 6 Mai, 2021 12:55 Thomas Kaegi said,

Hier also meine Formel, die laut Steffen funktioniert:

VSuchlauf(Rechnungen!$E:$E; NICHT IstLeer(LfdZelle);;SetzeZelle(VSuchlauf(RechnungenBez!$E:$E;Und( NICHT IstLeer(LfdZelle);LfdZelle=LfdZelle(1));Index(Rechnungen!$H:$H;LfdIndex(1));LfdResultat-Index(RechnungenBez!$H:$H;LfdIndex));Rechnungen!$J:$J;LfdIndex))

Noch ein paar Erläuterungen dazu:

Der äussere Suchlauf findet in der Tabelle "Rechnungen" alle Zellen, bei denen in Spalte E eine Rechnungsnummer steht (= die nicht leer sind). Für die so gefundenen Zeilen wird ein SetzeZelle-Befehl ausgeführt, der den Wert, der durch den inneren Suchlauf ermittelt wird, auf der gleichen Zeile in Spalte J einsetzt. Dieser Wert ist der noch offene Betrag für die betreffende Rechnung.

Der innere Suchlauf findet in der Tabelle "RechnungenBez" alle Zellen, bei denen in Spalte E die gleiche Rechnungsnummer steht wie die aktuell im äusseren Suchlauf adressierte (LfdZelle = LfdZelle(1)). Dabei werden nur die Zellen angeschaut, die nicht leer sind. Als "Startwert" für die Ermittlung des "Folgewerts" wird aus dem äusseren Suchlauf der Rechnungsbetrag aus Spalte H übernommen. Von diesem Wert werden als Folgewert des inneren Suchlaufs alle Beträge abgezogen, die für diese Rechnungsnummer überwiesen wurden. Bei vollständig bezahlten Rechnungen bleibt also der Wert 0 übrig, der gemäss Zellinhaltseinstellung nicht angezeigt wird. Bei allen nicht voll bezahlten Rechnungen bleibt dagegen als Wert der noch offene Betrag, also der für den SetzeZelle-Befehl gewünschte Wert.

Freundlicher Gruss, Thomas

Anmelden oder registrieren um Kommentare einzutragen

Re: Verschachtelter Suchlauf

#13038 On 6 Mai, 2021 19:05 Steffen said,

Wie immer TOP, lieber Thomas, klappt hervorragend!

"NICHT IstLeer(LfdZelle)" reicht natürlich völlig, da hast Du Recht.

Aber vor allem das mit der "LfdZelle(1)" bei verschachtelten Suchläufen hatte ich bisher nicht auf dem Schirm ...

Vielen vielen Dank!

Steffen

Anmelden oder registrieren um Kommentare einzutragen