Formeln Folge 3: In vollem Lauf
Unter den Formel-Funktionen in RagTime gibt es vor allem zwei, mit deren Hilfe sich richtiggehende Skripts zusammenstellen lassen: Der Horizontal- und der Vertikalsuchlauf. Bei größeren Datenbeständen (wie Adressen usw.) ist ein effizientes Arbeiten ohne diese Funktionen kaum möglich. Allerdings braucht es einen Einblick in die Logik, mit der ein Suchlauf funktioniert. Setzen wir uns (und RagTime) in Bewegung!
Die Funktion «VSuchlauf» löst eigentlich nichts anderes aus, als dass in einem Rechenblatt etwas gesucht wird. Und zwar immer in einem definierten Bereich, Zeile für Zeile, um Zellinhalte zu finden, die eine bestimmte Bedingung erfüllen.
In der einfachsten Form der Funktion wird nur gezählt, wie oft die Bedingung erfüllt ist. Bei der Suche wird ein Zähler mitgeführt, der angibt, wie viele Zeilen bereits untersucht wurden. Beginnt der Bereich in Zeile 1 eines Rechenblattes, so gibt dieser Zähler also die Nummer der Zeile an, die gerade angeschaut wird.
Der aktuelle Zählerstand wird durch die Funktion «LfdIndex» wiedergegeben (eine Funktion, die nur innerhalb von Suchlauffunktionen einsetzbar ist). Nicht nur die Zeile, auch die gerade geprüfte Zelle in der ersten Spalte des Suchbereichs kann mit einer Funktion abgefragt werden: Mit «LfdZelle». Als Beispiel:
Mit dieser Formel wird gezählt, wie oft der Name «Müller» in Spalte A vorkommt. Der Suchlauf funktioniert wie folgt: Es werden alle Zellen in Spalte A angeschaut, beginnend in Zeile 1. Der «LfdIndex» ist also = 1, und er wird in der Folge mit jeder angeschauten Zeile erhöht. Der Wert der Funktion selbst ist auf 0 gesetzt.
In jeder Zeile der Spalte A, in welcher der gesuchte Name steht, wird das Ergebnis der Funktion um 1 erhöht. Das lässt sich aber nicht mitverfolgen, da die Funktion in einem Zug berechnet wird. Die Funktion liefert einfach am Schluss die gewünschte Zahl, die Anzahl «Müller» in Spalte A.
Die Bedingung in der Funktion kann aber auch komplizierter sein. Ist zum Beispiel gefragt, wie viele «Müller» in der Tabelle enthalten sind, die in Hamburg wohnen, so lautet die Formel, wenn der Wohnort in Spalte E der Tabelle steht:
Mit «Und» werden die nachfolgenden Bedingungen verknüpft: Alle müssen erfüllt sein, damit die Zeile als „Treffer“ gezählt wird. In der fünften Spalte muss also gleichzeitig der Begriff «Hamburg» vorkommen. Die Funktion «Spaltenwert(5)» fragt den Inhalt der gerade untersuchten Zeile in Spalte E ab. Von der Verwendung der Funktion «Spaltenwert» in dieser Form (mit expliziter Angabe der Spaltennummer) raten wir jedoch dringend ab! Fügen Sie nämlich später eine Spalte im Suchbereich ein, z. B. für ein Landeskürzel vor der Spalte mit der Postleitzahl, würde zwar der Suchbereich auf A:F angepasst, aber die 5 als Argument der Spaltenwert-Funktion würde nicht angepasst – und schon versagt die Formel.
Die Suche nach Fehlern in Formeln, die einmal einwandfrei funktioniert haben und nach einer scheinbar unbedeutenden Änderung des Dokuments plötzlich nicht mehr das erwartete Ergebnis liefern, kann einem schier zur Verzweiflung treiben. Schreiben Sie stattdessen:
Der Suchbereich kann in Formel F-3.3 auf A:A beschränkt bleiben, da die Index-Funktion, im Gegensatz zu «Spaltenwert», auch Zellen außerhalb des in der Funktion angegebenen Suchbereichs referieren kann. Und da nur eine einzige Spalte abgesucht wird, reicht die Funktion «Suchlauf» statt «VSuchlauf». Mit der Index-Funktion wird die Zelle in Spalte E adressiert, die auf der gleichen Zeile steht wie die gerade untersuchte Zelle in Spalte A. Dafür sorgt die Verwendung des aktuellen Suchzählers, welcher mit der Funktion «LfdIndex» für die Adressierung benützt wird. Wird nun in der Tabelle eine neue Spalte C eingefügt, so passt RagTime bei dieser Schreibweise der Formel die Spaltenreferenz in der Index-Funktion richtig an.
Enthält die Tabelle eine Kopfzeile, so ist das in diesem Fall bedeutungslos, da dort sicher nicht «Müller/Hamburg» steht. In anderen Fällen müsste der Und-Funktion eine weitere Bedingung beigefügt werden:
Selbstverständlich muss der Vergleichswert erhöht werden, wenn der automatische Tabellenkopf mehr als eine Kopfzeile enthält.
Bis hierher ist beschrieben, wie die Suchlauf-Funktion beim Suchen vorgeht. Wenden wir uns nun den komplexeren Formen dieser Funktion zu.
In vielen Fällen wollen wir ja nicht nur zählen, wir wollen, basierend auf den Werten in der „Trefferzeile“, ein bestimmtes Ergebnis. Ein konkretes Beispiel: Auf einem Lieferschein (in einem Rechenblatt) stehen verschiedene Artikel, die aber nicht alle an Lager sind (siehe Abb. F-3.1).
Bei allen Artikeln, die am Lager vorrätig sind, steht in Spalte F das heutige Datum als Lieferdatum. In Spalte H stehen die Preise. Für die heutige Rechnungsstellung möchten wir die Summe der Preise derjenigen Positionen ermitteln, die heute ausgeliefert werden. Hierzu brauchen wir die Suchlauffunktion, wie sie in der Funktionenliste in ihrer zweiten Form angegeben ist, also:
Gesucht werden in unserem Beispiel alle Positionen, in der das heutige Datum in Spalte F enthalten ist. Folglich sind die beiden ersten Argumente der Funktion klar: Der «Bereich» ist F:F und die «Bedingung» ist «LfdZelle=heute».
Die Suchlauffunktion ermittelt bei jedem Treffer den «Folgewert». Dieser wird von einer Formel bestimmt, die hier als viertes Argument angegeben werden kann. Wir möchten die Preise aller „Trefferzeilen“ summieren. Dazu brauchen wir ein Ergebnis aus Spalte H. Die laufende Summe (nach jedem Suchlaufschritt) wird durch die Funktion «LfdResultat» angegeben – auch das eine Funktion, die nur in Zusammenhang mit Suchlauffunktionen verfügbar ist. Vor Abarbeitung der ersten Zeile übernimmt «LfdResultat» den Wert, der durch das Argument «Startwert» vorgegeben wurde, und wird nach Behandlung jeder „Trefferzeile“ durch das Ergebnis der Formel für den «Folgewert» ersetzt. Wir setzen also den «Startwert» auf 0. Der «Folgewert» ergibt sich aus der Addition von «LfdResultat» und dem Wert in Spalte H. Formel F-3.6 ist somit der vollständige Suchlauf mit all diesen Argumenten.
Ist der Suchlauf abgeschlossen, so ist das Resultat der ganzen Formel gleich dem zuletzt ermittelten «Folgewert». Das Ergebnis steht somit dort, wo die Formel der Suchlauffunktion eingegeben wurde. In unserem Fall in Zelle K1 (siehe Abb. F-3.1).
Dieser Euro-Betrag kann natürlich auch mitten in einem fortlaufenden Text stehen, wenn die Formel dort eingegeben wurde. Wir können das Resultat aber gleichzeitig auch noch an einem zweiten Ort – zum Beispiel in einem anderen Rechenblatt – einsetzen lassen, indem wir die Formel F-3.6 zusätzlich mit der Funktion «SetzeZelle» kombinieren.
Die Funktion «SetzeZelle» hat in der Funktionsliste gleich mehrere Varianten mit unterschiedlichen Argumenten. Für uns reicht hier die erste Variante. Mit ihr wird ein Wert in eine Zelle mit fester Adresse geschrieben, gemäß Formel F-3.7 in die Zelle Z100.
Gleichzeitig erscheint der Wert aber auch dort, wo die Formel selbst eingegeben wurde. Sie setzt also den Text «Ergebnis der Suchlauffunktion» in die Zelle Z100 und hinterlässt den gleichen Text dort wo die Formel steht. Natürlich wollen wir nicht diesen Text, sondern effektiv das Ergebnis der Suchlauffunktion einsetzen. Nach «SetzeZelle» folgt also unsere bisherige Suchlauf-Formel, sodass alles – fertig kombiniert – die Formel F-3.8 ergibt:
Genauso wie Knöpfe oder immer wiederkehrende Layout- und Corporate-Design-Elemente müssen auch komplexe Formeln dokumentiert werden. Selbst routinierte RagTime-Anwender wissen schon nach kurzer Zeit, und erst recht nach mehreren Monaten, nicht mehr ganz genau, was eine von ihnen selbst geschriebene Formel konkret bewirkt. Dann wird jeder Versuch, den Sinn einer Formel und deren Funktionieren nachzuvollziehen, zu einem Ratespiel.
Leider lassen sich in der RagTime-Formelpalette die einzelnen Funktionen weder gliedern noch mit Kommentaren ergänzen. Die Formeleingaben müssen ja bekanntermaßen ohne Leerzeichen oder Zeilenschaltungen aneinander gereiht werden. Das macht sie schwer les- und analysierbar.
Je komplizierter und verschachtelter eine Formel aufgebaut ist, desto problematischer. Ein Überprüfen ist kaum noch möglich. Da kann nur eine Dokumentation helfen. Wir schlagen hier eine mögliche Methode vor, die wir dann auch in der Folge weiter verwenden. In einem Dokument mit Formeln legen wir ein zusätzliches Rechenblatt «DOK» an. Sie können aber auch, um ein Archiv von Formeln anzulegen, ein eigenes Dokument mit mehreren solchen DOK-Rechenblättern zusammenstellen.
Damit Sie jedes Mal mit dem Kopf nicken oder «Aha – ja klar» sagen können, strukturieren Sie am besten jede komplexere Formel nach Funktionen und Erläuterungen wie in Abb. F-3.2 gezeigt. In einer ersten Zeile steht die Adresse der Zelle, in der die Formel enthalten ist. Daneben die komplette Formel, die Sie aus der Formelpalette herauskopieren können. Das Feld mit der Formel kann auch als Mehrzeiler formatiert werden, sodass längere Formeln schon hier mit einer Gliederung etwas übersichtlicher dargestellt werden können. Wenn Sie allerdings ein Formelarchiv anlegen wollen, um die Formeln von hier aus bei Bedarf in die Formelpalette zu kopieren, müssen Sie dabei darauf achten, alle Zeilenschaltungen und Leerzeichen wieder zu entfernen.
Gleich unter der Formel folgt eine Beschreibung ihrer Funktion. Es empfiehlt sich, dies möglichst präzise und in Verbindung zum konkreten Fall der Anwendung zu tun.
Schließlich wird jede verwendete Funktion mit ihren Argumenten erläutert. Jede Verschachtelung wird dabei durch entsprechende Einrückung sichtbar gemacht. Für die äußerste Funktion («SetzeZelle») finden sich die beiden Argumente in der nächsten Einrückungsstufe: Zuerst die Funktion «Suchlauf», mit welcher der einzusetzende Wert ermittelt wird, danach die Adressierung, wo dieser Wert eingesetzt werden soll. Analog ist die Suchlauf-Funktion in der nächsten Ebene „seziert“.
Dabei werden hier zum besseren Verständnis auch noch die syntaktischen Definitionen der einzelnen Argumente aufgeführt. Diese Art der Dokumentation mag etwas übertrieben erscheinen. Doch wie sinnvoll das ist, werden Sie – bei wirklich komplizierten Formeln – bald erkennen.
Anhand eines weiteren Praxisfalles möchten wir die Qualitäten der Suchlauf-Funktion weiter verdeutlichen: Wir haben ein Dokument mit einem Rechenblatt «Personal» und einem Rechenblatt «Jubilare». Aus dem Rechenblatt «Personal» – mit Daten von Mitarbeiterinnen und Mitarbeitern – wollen wir diejenigen extrahieren, die im nächsten Jahr das Alter von 60 Jahren erreichen. Eine typische Konstellation also, die für Serienbriefe, Einladungen usw. in ähnlicher Form immer wieder vorkommt. Es geht um Extrahieren und Sortieren nach wechselnden bzw. aktuellen Kriterien. Auf der folgenden Seite gehen wir näher darauf ein. Wir haben uns hier bei den Personaldaten auf die für das Beispiel erforderliche Minimum beschränkt.
Bevor wir an die Lösung herangehen, sind noch zwei Konzepte für die Anwendung von komplexen Formeln in Rechenblättern hilfreich: Das Zerlegen von Formeln in Teilformeln, die voneinander abhängig gemacht werden einerseits, und die Verwendung von Kopfzeilen für Formeln, Hilfszellen und Knöpfe anderseits.
Die Zerlegung komplexer Formeln in Teilformeln hat einen wichtigen Vorteil: Durch Entkoppeln der Teilschritte – indem die Auslösung der nachfolgenden Teilformel unterdrückt wird – kann der ganze Berechnungsvorgang in Schritten getestet werden. Im Kapitel „Formeln Folge 2: Vielfalt der Knöpfe“ hatten wir unter anderem auch einen «Farbwahl»-Knopf erstellt (Und daraus wird ein Knopf…). Mit der Betätigung jedes Farbwahl-Knopfes wurde dort eine 1 in die Steuerzelle D2 geschrieben. Dadurch wurde die Ausführung einer Formel freigegeben, die mit der Funktion «SpezialWenn», abhängig von dieser Steuerzelle, verriegelt war. Bei der Ausführung der Formel wurde die Steuerzelle gleich wieder gelöscht, sodass die Formel nur ein einziges Mal berechnet wurde. Diesen Mechanismus können wir immer wieder zur Freigabe der Ausführung von Formeln oder Teilformeln benützen, auch hier.
Die erste Teilformel kann eine weitere Steuerzelle aktivieren, die ihrerseits die nächste Teilformel freigibt. Auf diese Weise lassen sich beliebig viele Teilformeln aneinander reihen. Mit einem solchen Vorgehen wird die Fehlersuche gewaltig erleichtert. Das Anfügen weiterer Teilschritte beeinflusst die bereits getesteten Formeln nicht. Auch die Dokumentation ist für jeden der Teilschritte von den anderen Teilschritten unabhängig. Vorraussetzung ist natürlich, dass jede Teilformel für sich allein eine vollständige Formel ist, damit die Formeleingabe überhaupt abgeschlossen werden kann.
Das zweite Konzept: Für alle Formeln und Steuerzellen benützen wir ausschließlich Zellen in der Kopfzeile der Tabelle. Diese Kopfzeile mit den Spaltentiteln wird wohl kaum je gelöscht. Würden Steuerzellen oder Formeln in Zeilen stehen, die auch von der Tabelle benützt werden, so bestünde immer die Gefahr, dass beim Löschen einer Tabellenzeile unbeabsichtigt auch eine Formel oder eine Steuerzelle gelöscht wird. Sie könnten damit ganze Formeln verlieren oder in bereits getesteten Formeln würden Referenzfehler entstehen. Und verlorene Referenzen sind nur schwer zu rekonstruieren!
Zunächst erstellen Sie einen Knopf, der irgendwo platziert werden kann. Im Knopf ist die Formel F-3.9 eingegeben – als Formel und nicht als Befehl! Dadurch wird der Knopf zum Auslöser für die Formel in der Zelle «Jubilare!F1» (siehe Abb. F-3.5).
In der Zelle «Jubilare!F1» beginnt die Suchlauf/SetzeZelle-Formel (siehe Formel F-3.10) mit einer vorangestellten SpezialWenn-Funktion. (NB: Bei der Durchsicht dieses Textes hat Jürgen Schell bemerkt, dass in diesem Fall auch «Wenn» statt «SpezialWenn» verwendet werden könnte. Aber da waren schon alle Bildschirmfotos gemacht.) Diese bewirkt, dass die übrigen Berechnungen nur ausgeführt werden, wenn in Zelle E1 eine 1 steht. Schauen wir uns die Formel F-3.10 noch genauer an. Sie ist bereits in sich aus mehreren Teilformeln gebildet, welche mit dem &-Operator aneinander gereiht sind. Zu ihrem Verständnis hilft uns die Dokumentation gemäß Abb. F-3.6.
Ist die Bedingung erfüllt (eine 1 in E1), wird durch die Funktion «SetzeZelle» diese 1 sofort wieder durch eine 0 ersetzt. Damit wird die Formel wirklich nur ein einziges Mal ausgewertet! Gleichzeitig wird aber auch der Rest der Formel abgearbeitet. Dass als Bedingung in der Formel nicht «E1=1» steht, sondern nur E1, ist kein Druckfehler. Der Wert 1 steht ebenfalls als logischer Wert «Wahr» für die Erfüllung einer Bedingung und kann deshalb mit einer Referenz zur Zelle ohne Vergleich direkt abgefragt werden. Mit dem anschließenden SetzeZelle-Befehl wird ein Vorgang im Rechenblatt «Personal» ausgelöst, das Übertragen der Personaldaten der Jubilare in das entsprechende Rechenblatt. Aber vorher, und das ist der Zweck des Rests der Formel, muss eine eventuell noch vorhandene alte Jubilarentabelle gelöscht werden.
Natürlich ist das mit einem LöscheBereich-Befehl relativ einfach zu bewerkstelligen. Nur – wie groß ist denn der Bereich? Mit der Funktion «Anzahl(C:C)» wird die Anzahl vorhandener Tabelleneinträge ermittelt. Wenn die Tabelle bereits leer ist, dann liefert diese Funktion den Wert 0 oder «Falsch» und der Rest der Formel wird dank der Wenn-Funktion gar nicht ausgeführt. Bemerken Sie, dass auch hier kein Vergleich für die Bedingung herangezogen wird. Nicht nur der Wert 1 wird durch RagTime als logisch «Wahr» interpretiert, sondern alle Werte außer 0. Einzig dank der „Power Function“ «Bereich» ist es möglich, die Bereichskoordinaten berechnen zu lassen. Aber leider lässt sich diese Funktion nicht verschachtelt innerhalb der Funktion «LöscheBereich» verwenden. Das gelingt nur mit einem Trick: Dieser ganze Formelteil muss als Text zusammengestellt und dann mit der MetaFormel-Funktion «RechneText» ausgewertet werden. Der berechnete Text ergibt, bei vier Tabelleneinträgen, die folgende Formel, die gewissermaßen Teil der Formel F-3.10 wird:
Nun ist also die Jubilarentabelle bereit, die neuen Einträge aufzunehmen. Diese müssen in der Personal-Tabelle gesucht und in die Jubilarentabelle übertragen werden. Die entsprechende Formel steht im Rechenblatt «Personal», Zelle F1 und ist mit der Steuerzelle in E1 verriegelt, die in Formel F-3.10 aktiviert wurde.
Hier (Formel F-3.12) zuerst einmal die ganze Formel, deren Dokumentation in Abb. F-3.7 zu finden ist. Wir haben dabei die Funktion «VSuchlauf» statt «Suchlauf» verwendet. Die Funktionen «Suchlauf» und «VSuchlauf», bzw. «HSuchlauf» sind austauschbar, wenn nur eine einzige Spalte/Zeile abgesucht wird. Intuitiv griffen wir hier zu «VSuchlauf», weil es eine Spalte abgesucht werden sollte.
Den Mechanismus der Rücksetzung dieser Aktivierung und des Entriegelns einer nächsten Teilformel durch Aktivierung der Zelle «Jubilare!G1» brauchen wir nicht erneut zu erläutern. Der Vollständigkeit halber der Unterschied, falls statt «SpezialWenn» die Funktion «Wenn» verwendet würde: Wenn die Steuerzelle die Ausführung der Formel blockiert, lässt «SpezialWenn» den Inhalt der Zelle mit der Formel unverändert, während er bei Verwendung von «Wenn» gelöscht wird. – Nach denn zwei SetzeZelle-Befehlen des „Verriegelungs-Mechanismus“ folgen zwei weitere, welche Ausgangswerte für einen nachfolgenden Verarbeitungsschritt in Hilfszellen der Jubilarentabelle eintragen:
Auf diese kommen wir erst bei der Erläuterung des übernächsten Schrittes zurück.
Die Suchlauffunktion ist hier im Prinzip gleich wie bei unserem Beispiel mit dem Lieferschein (Viel mehr als nur zählen). Nur ist hier das Auswahlkriterium nicht einfach ein Datum in einer anderen Spalte der gleichen Zeile. In unserem Falle müssen die Geburtsdaten in Spalte D des Rechenblattes «Personal» mit dem heutigen Datum verglichen werden. Am einfachsten ist es, aus den Geburtsdaten und dem heutigen Datum mit der Funktion «JahrVon» das Jahr zu extrahieren, wie in dieser Teilformel gezeigt:
Bei jedem so gefundenen Treffer handelt es sich um einen Jubilaren, dessen Personaldaten in die Jubilarentabelle zu übertragen sind. Dies ist die Aufgabe des letzten Teils der Formel F-3.12, gemäß Syntax der VSuchlauf-Funktion also «Folgewert» und «Abbruchbedingung». Im Gegensatz zu anderen Anwendungen der Funktion interessiert uns hier der «Folgewert» als solcher überhaupt nicht. Vielmehr geht es um die Formel, die für den «Folgewert» ausgeführt wird. Es handelt sich um zwei SetzeZelle-Befehle.
Vorläufig müssen die Personaldaten nicht vollständig übertragen werden, denn die Jubilare sollen ja noch in aufsteigender Reihenfolge ihrer Geburtstage sortiert werden. Dafür reichen Personalnummer als eindeutige Kennung und die Geburtstage. Damit uns beim Sortieren die Werte nicht in die Quere kommen, legen wir die Personalnummern vorläufig in Spalte A der Jubilarentabelle ab. Die Werte werden der Personalliste mit der Index-Funktion entnommen. Die Nummer der Zeile, in der sie in der Jubilarentabelle eingesetzt werden müssen, ergibt sich aus der Anzahl bereits gefundener Jubilare. Diese wird von der Funktion «LfdZähler» geliefert, einer weiteren Funktion, die nur innerhalb von Suchläufen benutzt werden kann.
Abbruchbedingung für den Suchlauf ist die erste leere Zeile in der Personaltabelle. Zwar wird so eine Zeile zu viel untersucht. Um das zu vermeiden könnten wir unter Benützung der Index-Funktion mit (IstLeer(Index(D:D;LfdIndex+1))) eine Zeile vorausschauen. Hier bringt das keinen Vorteil. In anderen Fällen kann es aber wichtig sein, dass keine Zeile zu viel vom Suchlauf erfasst wird, vor allem wenn der «Folgewert» interessiert.
Mit der angegebenen Formel F-3.12 wird der Wert der Suchlauffunktion ein Wert-Fehler sein, weil «JahrVon(Leere Zelle)» keinen gültigen Wert ergibt. Mit der alternativen Abbruchbedingung für den Suchlauf wäre es eine Aneinanderreihung der beim letzten Treffer mit den SetzeZelle-Befehlen übertragenen Werte. Beides ist an dieser Stelle nicht von Interesse.
Damit uns die Anzeige dieses Funktionswertes nicht verwirrt, wählen wir unter «Rechenblattinformation ➝ Zellinhalt ➝ Sichtbarkeit» für die „Formelzelle“ F1 die Option «Nirgends». Das machen wir in diesem Beispiel auch mit allen anderen „Formelzellen“. Zu beachten ist allerdings, dass die Zellen geschützt werden sollten, damit die völlig unsichtbaren Formeln nicht aus Versehen gelöscht wird. In unserem Beispiel haben wir, auch zur besseren Kennzeichnung in Abb. F-3.4 und Abb. F-3.5, diese Zellen noch leicht rot hinterlegt.
Lässt sich der Aufwand, den wir hier für die Lösung dieses Problems treiben werden, überhaupt rechtfertigen? – Natürlich hätten wir im letzten Suchlauf auch gleich alle Personaldaten in die Jubilarentabelle übertragen können. Eine so erzeugte Tabelle lässt sich dann problemlos mit dem Menübefehl nach Geburtsdatum sortieren. Die Übertragung mit dem SetzeZelle-Befehl hat ja den Vorteil, dass keine Formeln in der Tabelle stehen, die uns beim Sortieren einen Streich spielen können.
Aber hier wollen wir davon ausgehen, dass das Dokument auch für Computerlaien benutzbar sein muss. Es soll also alles automatisch ablaufen, auch das Sortieren! Der Aufwand, den wir für die Erstellung der Formeln betreiben müssen, ist wirklich nur in Ausnahmefällen vertretbar. Aber hier geht es ja darum, den Weg aufzuzeigen, der in diesem Fall gesucht werden muss. Deshalb wollen wir uns die Mühe machen.
Wie läuft ein solcher Sortiervorgang ab? Schauen Sie sich dazu das Flussdiagramm (Abb. F-3.8) an, das den Ablauf schematisch darstellt.
Die Formeln haben wir direkt in die Rechenblätter mit den Daten eingebaut. Natürlich könnten sie auch in einem separaten Rechenblatt stehen. So würden sie aber wegen der zusätzlich erforderlichen externen Rechenblattreferenzen bedeutend länger und damit schwerer lesbar. Es ist, vor allem beim Testen der Formeln, auch angenehm, auf Formeln und Werte im gleichen Fenster zugreifen zu können. Bei komplexen Dokumenten und Tabellen ist dieses Vorgehen allerdings weniger zweckmäßig.
Nun aber zur Formel F-3.15 in Zelle «Jubilare!H1», die in Abb. F-3.8 als Teilformel 3 bezeichnet ist. Die Ausführung der Formel ist mit dem Wert von Zelle G1 in bekannter Weise verriegelt, welche durch den zweiten SetzeZelle-Befehl in Teilformel 2 im Rechenblatt «Personal» (Formel F-3.12) aktiviert wird. Sie ist relativ einfach zu interpretieren. Trotzdem, der Vollständigkeit halber, auch dafür die Dokumentation gemäß Abb. F-3.9. In verschiedenen Ablaufschritten müssen Werte in Hilfszellen abgelegt werden. In der Teilformel 4 muss z. B. der letzte bereits einsortierte Geburtstag und dessen Position in der Jubilarentabelle bekannt sein, damit nach dem nächsten gesucht, und dieser an der richtigen Stelle in die Tabelle eingetragen werden kann.
Bereits beim ersten Durchgang müssen diese Werte für Teilformel 4 vorgegeben sein. Das ist die Aufgabe der in Formel F-3.13 gezeigten zwei SetzeZelle-Befehle. Diese sind als Teilformel in Formel F-3.12 enthalten. Der Zweck dieser Hilfszellen (K1 und L1) kann der Dokumentation in Abb. F-3.10 entnommen werden.
Die Hilfszelle K1 wird auch in Formel F-3.15 verwendet, um festzustellen, ob alle in der Tabelle enthaltenen Positionen (gemäß Spalte A) einsortiert wurden und der Sortiervorgang damit abgeschlossen ist. Abhängig davon wird eine der Teilformeln 4 oder 6 gemäß Abb. F-3.8 aktiviert.
Wir verfolgen zuerst den Ablauf des Sortiervorgangs, als Nächstes also die Teilformel 4 gemäß Formel F-3.16 und Dokumentation in Abb. F-3.11. Verriegelung und Auslösung der nächsten Teilformel sind mittlerweile selbstverständlich. Bevor der Suchlauf gestartet wird, muss noch die Hilfszelle M1 (siehe Abb. F-3.10) auf ein Datum initialisiert werden, das sicher nach dem jüngsten möglichen Geburtstag liegt – als Vergleichswert für den ersten untersuchten Geburtstag.
Als «Bedingung» des Suchlaufs müssen drei Kriterien erfüllt sein: Die behandelte Zeile darf nicht die Kopfzeile der Tabelle sein, der untersuchte Geburtstag muss jünger sein als der jüngste bisher gefundene (in Hilfszelle L1) und älter als das in Hilfszelle M1 enthaltene, bisher in diesem Suchlauf gefundene älteste Datum.
Ist die Bedingung erfüllt, wird mit dem ersten von drei SetzeZelle--Befehlen das Datum in Hilfszelle M1 gleich durch dieses Datum ersetzt. Mit dem zweiten wird die Personalnummer dieses Treffers in Hilfszelle N1 abgelegt und zudem auch gleich in die Jubilarentabelle, Spalte C, auf der Zeile eingetragen, auf die der nächste Jubilar einzutragen ist, obwohl noch gar nicht feststeht, dass der eben gefundene dieser Jubilar ist. Wird später im Suchlauf ein älterer, noch nicht einsortierter Jubilar gefunden, wird diese Personalnummer wieder überschrieben.
Als Abbruchbedingung für den Suchlauf wird anhand der Anzahl Personalnummern in Spalte A überprüft, ob alle Jubilare behandelt wurden. In diesem Fall ist es wichtig, dass keine Zeile zu viel bearbeitet wird.
Haben Sie realisiert, dass bei jeder Ausführung dieser Formel nur ein einziger Jubilar, bzw. dessen Personalnummer einsortiert wurde? Nach Abschluss des Suchlaufs werden noch zwei weitere SetzeZelle-Befehle ausgeführt. Mit dem ersten wird der Index zur Jubilarentabelle (Hilfszelle K1) um 1 erhöht, damit bei der nächsten Ausführung dieser Teilformel die Personalnummer des dort gefundenen Jubilars in die nächste Zeile geschrieben wird. Mit der zweiten wird das gefundene Geburtsdatum in die Hilfszelle L1 geschrieben. Wahrlich eine komplizierte Formel, um einen einzigen Jubilaren einzusortieren.
Nicht nur Zwillinge haben an ein und demselben Tag Geburtstag. Das kann durchaus auch bei Mitarbeitenden einer Firma vorkommen. Und nur weil wir einen Jubilaren mit Geburtstag X gefunden haben, dürfen wir die übrigen mit gleichem Geburtstag nicht vergessen. Das ist die Aufgabe von Teilformel 5 gemäß Abb. F-3.8, bzw. Formel F-3.17. Diese löst ihrerseits nicht etwa die noch weiter rechts in Zelle R1 stehende Formel aus, sondern wieder Teilformel 3, indem in Zelle G1 eine 1 geschrieben wird. Formel F-3.17 ist etwas einfacher als die vorangehende, aber in der Struktur sehr ähnlich. Siehe auch ihre Dokumentation in Abb. F-3.12.
Die Bedingung ist allerdings doch recht verschieden. Natürlich muss auch hier die Kopfzeile übersprungen werden. Der Geburtstag der untersuchten Zeile muss der gleiche sein wie der im letzten Sortierschritt gefundene. Und die Personalnummer darf nicht diejenige dieses letzten Eintrags sein, welche in Hilfszelle N1 abgelegt ist. Wir wollen ja nicht den gleichen Jubilaren ein zweites Mal eintragen.
Der Eintrag des gefundenen „Zwillings-Jubilaren“ erfolgt in gleicher Weise wie in Formel F-3.16: Personalnummer in Spalte C, Zeile gemäß Index in Hilfszelle K1, welche anschließend um 1 erhöht wird. Wichtig natürlich, dass diese Formel nicht nur einen einzigen Jubilaren mit gleichem Geburtstag findet, sondern so viele wie es eben gibt.
Die Abbruchbedingung für den Suchlauf ist die gleiche wie in der letzten Teilformel. Nach Ausführung dieses Schrittes wird im Ablauf zur Teilformel 3 zurückgekehrt, in dem geprüft wird, ob der Sortiervorgang abgeschlossen sei. Sonst kommen die Schritte gemäß Teilformeln 4 und 5 erneut an die Reihe, so lange, bis eben fertig sortiert ist.
Schauen wir jetzt das Resultat der Sortierung an, so müssen wir feststellen, dass wir bisher lediglich die Personalnummern in der Spalte C der Jubilarentabelle in der richtigen Reihenfolge eingereiht haben. Es gilt also, diese mit den restlichen Personaldaten zu ergänzen. Das ist die Aufgabe von Teilformel 6, bzw. Formel F-3.18, dokumentiert in Abb. F-3.12.
Da dies die letzte Teilformel des Sortierablaufs ist, muss in dieser Formel keine weitere Formel entriegelt werden. Als «Bedingung» muss der Suchlauf nur die Kopfzeile berücksichtigen. Es werden also alle anderen Zeilen der Jubilarentabelle behandelt. Für jede Zeile müssen, entsprechend der in Spalte C enthaltenen Personalnummer, die Personaldaten (Name, Vorname und Geburtstag) aus der Personaltabelle geholt werden. Drei Mal wird somit der gleiche Index benötigt, der aufgrund der Personalnummer ermittelt werden muss. Da drängt es sich auf, diesen Index nur einmal zu berechnen, und ihn in einer Hilfszelle – benützt wird Zelle N1 – abzulegen. Die Berechnung erfolgt mit der seit RagTime-Urzeiten bekannten Suchlauffunktion «Auswahl». Sie liefert den Index der Zeile in der Personaltabelle, welche die gesuchte Personalnummer enthält. Die Auswahl-Funktion wird innerhalb eines Suchlaufs verwendet. Für beide Funktionen wird hier die Funktion «LfdZelle» verwendet. Es muss deshalb bei der inneren der verschachtelten Funktionen angegeben werden, dass es sich im einen Fall um die «LfdZelle» der äußeren Schachtelungsebene handelt. Der Schachtelungsindex der inneren Funktion ist 0 und muss nicht angegeben werden, derjenige der äußeren Funktion ist 1. Theoretisch wären noch mehr Schachtelungsebenen möglich. Auf eine sinnvolle (und auch begreifbare) Anwendung von mehr als zwei Ebenen sind wir noch nicht gestoßen.
Ist der Index der zu übertragende Personaldaten mal in Hilfszelle N1 abgelegt, folgen drei praktisch identische SetzeZelle-Befehle, welche die Daten in den Spalten A, B und D von einer Tabelle in die andere übertragen. Die Abbruchbedingung ist analog wie in den vorangehenden Formeln, nur dass sie jetzt auf der Anzahl Personalnummern in Spalte C beruht.
Sicher sind Sie beim Nachvollziehen dieses Sortiervorgangs ebenso ins Schwitzen gekommen wie die Autoren beim Schreiben. Vielleicht fragen Sie sich, ob die Anwendung der Suchlauffunktion wirklich die optimale Lösung sei. Wir sind davon überzeugt!
Sicher gibt es für die gestellte Aufgabe der Jubilaren-Liste auch andere Lösungen. Die Verwendung von «VSuchlauf» – in einem anderen Fall kann es auch «HSuchlauf» oder «Suchlauf» sein – hat aber gewichtige Vorteile, vor allem im Vergleich mit einer Lösung, die auf in Tabellen nach unten kopierten Formeln beruht: Die Personalliste kann beliebig ergänzt werden, ohne dass irgendwelche Formeln extra kontrolliert werden müssen und die Tabelle kann kopiert, anderswo eingefügt und dort nachbearbeitet werden, da den Werten keine Formeln hinterlegt sind. Eignen Sie sich die Suchlauf-Meisterschaft an – es lohnt sich! Bald einmal werden Sie selbst mit Freude Suchläufe „bauen“!
Vergessen wir die einfachste Form des Suchlaufs nicht: Mit zwei Argumenten, dem Bereich und der Bedingung, zählen Suchläufe – egal welcher Art – nur, wie oft die Bedingung im Bereich erfüllt ist. Der Wert der Funktion entspricht am Schluss dieser Anzahl. Die beiden anderen Formen, mit oder ohne Abbruchbedingung, unterscheiden sich nicht wesentlich. Es ist aber empfehlenswert, im Zweifelsfall eine Abbruchbedingung zu verwenden. Leicht wird vergessen, dass in der Formel für den «Folgewert» eine Funktion eingesetzt wird, die z. B. mit leeren Zellen einen Fehler produziert. Es gab schon mehrere Forum-Beiträge, die vermeintliche Suchlauffehler bemängelten, welche nur auf fehlende oder falsche Abbruchbedingungen zurückzuführen waren.
Wichtig ist eine nicht aus der Syntax hervorgehende Unterscheidung: Geht es beim Suchlauf darum, einen «Folgewert» zu ermitteln, wie im Beispiel mit dem Total der am Stichtag ausgelieferten Lieferscheinpositionen (Abb. F-3.1), oder geht es um die Ausführung von Verarbeitungsschritten – meist unter Verwendung von «SetzeZelle» – bei denen der «Folgewert» als solcher, und damit der Wert der Funktion nach ihrer Ausführung gar keine Rolle spielt. Bei letzteren gilt es zu beachten – ein Problem, dem wir hier nicht begegnet sind – dass die Formel für den «Folgewert» so verarbeitet werden kann, dass keine Fehler entstehen.
Am ehesten könnten Typenkonflikte der verarbeiteten Daten zu solchen Fehlern führen. Wird bei einem Suchlaufschritt ein Fehler festgestellt, so wird der Suchlauf abgebrochen. Solchen Fehlern ist nicht einfach auf die Spur zu kommen!
Nach Studium des Beispiels mit dem Sortiervorgang sind sie sicher mit uns einig: Mit den Suchläufen sind skriptartige Konstruktionen möglich, mit denen die sowieso schon große Leistungsfähigkeit von RagTime noch potenziert wird. Machen Sie sich diese zunutze!
Die Serienbrieffunktion ist eigentlich auch eine Suchlauffunktion! Das wird vielfach übersehen. Die Funktion «LfdZelle» lässt sich deshalb auch in Argumenten verwenden. Möchten wir nur allen Jubilaren eine Einladung zum Jubilarenausflug schicken, die in diesem Jahr 60 werden, und interessieren uns gar nicht für eine separate, sortierte Tabelle, so könnte für Personal-Stammdaten, die gemäß Abb. F-3.4 angeordnet sind, auch mit «Serienbrief» gearbeitet werden. Dabei ist es wichtig, zu realisieren, dass bei Vorkommen mehrerer Serienbrieffunktionen im gleichen Dokument immer alle «Auswahlbedingungen» erfüllt sein müssen, damit der betreffende „Datensatz“ gedruckt wird. Das erlaubt uns, die Formeln aufzuteilen. Wenn Sie also in Ihrem Brief mit der Formel
den Personalstammdaten den Namen entnehmen und mit anderer Spaltenreferenz die übrigen Elemente, so können Sie in der weiteren Formel
die Auswahlbedingung für die Personen angeben, die den Brief erhalten sollen. Die erste der mit «Und» verknüpften Bedingungen in Formel F-3.20 schließt alle Zeilen aus, die in Spalte D kein Datum enthalten, also die Kopfzeile und die leeren Zeilen. Mit der zweiten werden alle Personen ausgelesen, die in diesem Jahr 60 werden.
Die Formel kann irgendwo im Text stehen, mit grafischem Text auf der Seite platziert werden oder in einem Rechenblatt, das gar nicht im Layout erscheint. Wenn die Formel im Text oder auf der Layoutseite angeordnet wird, dann sollten Sie dem sich ergebenden Text noch die «Nichtdruckende Füllung» zuordnen – sonst steht da plötzlich auf jedem Brief irgendwo das Geburtsdatum!
In obiger Formel F-3.20 ist die Funktion «LfdZelle» verwendet. Ebenfalls zulässig wäre «Spaltenwert», nicht aber die weiteren Spezialfunktionen, die nur innerhalb von Suchläufen benutzt werden können. Weiteres zu Serienbriefen finden Sie im Abschnitt 2.5.6 „Serienbrief mit Auswahl“.
Die Funktion «Auswahl» gab es schon in RagTime 3. Sie diente dort als „Suchlauf“. Aber sie ist tatsächlich auch eine Suchlauffunktion! In ihren Argumenten sind die gleichen Spezialfunktionen zulässig wie bei der Serienbrief-Funktion. Im Unterschied zu den Suchläufen kann mit «Auswahl» sehr direkt der n-te Treffer (in diesem Beispiel wird die n-te Zeile gesucht, die in Spalte A den Wert 1 enthält) gefunden werden:
Die Schreibweise ist einfacher als für den äquivalenten Suchlauf:
So lange es einen n-ten Treffer gibt liefern die beiden Formeln das gleiche Resultat, nämlich den Index der Zeile mit dem n-ten Treffer. Ein subtiler Unterschied ergibt sich, wenn in der abgesuchten Tabelle weniger als n Treffer vorkommen.
Die Formel F-3.21 mit der Auswahl-Funktion liefert dann sehr korrekt den Fehlerwert «NV!» (= Nicht verfügbar), die Formel F-3.22 mit dem Suchlauf aber den Index des letzten gefundenen Treffers. Das kann zu schwer lokalisierbaren Fehlern führen. Benutzen Sie also für die Suche nach dem n-ten Vorkommen eines Wertes in einer Tabelle immer die Funktion «Auswahl» und prüfen Sie auf den Fehler!
● Teilen Sie komplexe Vorgänge in Teilformeln auf, die mit Steuerzellen und «SpezialWenn» oder «Wenn» verriegelt sind. ● Platzieren Sie Formeln und Hilfszellen nicht auf Zeilen mit Tabellenwerten, sondern in der Kopfzeile Ihrer Tabelle – oder in einem anderen Rechenblatt. ● Vermeiden Sie die Funktion «Spaltenwert(n)» (bzw. «Zeilenwert») mit «n» als absoluter Zahl und vermeiden Sie dadurch Fehler beim Einfügen von Spalten und Zeilen – benutzen Sie stattdessen «Index», auch wenn das etwas aufwändiger ist. ● Wenn Sie die Funktion «Spaltenwert» trotzdem verwenden: Beachten Sie, dass die referierte Spalte innerhalb des in der Suchlauffunktion angegebenen Suchbereichs liegen muss! ● Beachten Sie die Reihenfolge der Bedingungen in Suchläufen! Wenn Sie z. B. ein Spalte nach einem Datum absuchen und dazu in der Bedingung eine Datumsfunktion verwenden, so ergibt sich in der Kopfzeile ein Fehler, wenn dort ein Text steht. Der Ausschluss der Kopfzeile mit der Bedingung «LfdIndex>1» muss deshalb vor der Bedingung mit der Datumsfunktion stehen, sodass diese auf die Kopfzeile gar nicht angewendet wird. ● Komplexe Folgewertberechnungen dürfen keine Typ-Inkompatibilitäten aufweisen. ● Dokumentieren Sie Ihre Formeln. Sie müssen dazu nicht die hier gezeigte Dokumentationsmethode verwenden. Aber Ihre Dokumentation soll Ihnen auch noch nach längerer Zeit das einfache Begreifen und Nachvollziehen Ihrer Formel ermöglichen und passen Sie die Dokumentation bei Änderungen an!