Tipp zur Vereinfachung einer Formel

Hallo Thomas,

vielen Dank für die schnelle Antwort. Ich habe mich in der Benennung der Berechnung tatsächlich vertan. Ich nutze hier oft die Suchen Funktion. Anbei ein konkretes Beispiel aus den Zellen A3:A22 eines Rechenblatt1. In allen Zellen ist diese Funktion eingesetzt.

Wenn(IstNV( Wenn(Suchen(A$1;Rechenblatt2!$A$3:$A$54;Rechenblatt2!$E$3:$E$54)>Zeile-3;Zeile-2;) );
' ';
Wenn( Suchen(A$1;Spell Record Sheet!$A$3:$A$54;Rechenblatt2!$E$3:$E$54)>Zeile-3;Zeile-2; )
)

Obige Funktion generiert im Rechenblatt1!A3:22 eine laufende Nummer, welche allerdings nur bis zu einem Maximalwert angezeigt wird. Der zugehörige Maximalwert wird auf dem Rechenblatt2 über eine Suchen Funktion ermittelt. Die Suchen Funktion holt aus Rechenblatt1!A1 den zur Liste relevanten Kontext und ermittelt dann im Rechenblatt2!E3:E54 den zugehörigen Maximalwert. Der Maximalwert kann sich ändern und entsprechend wird die Laufende Nummer im Rechenblatt1 angepasst. Der Kontext kann sich auch ändern und auch dann wird die Laufende Nummer entsprechend angepasst.

Das funktioniert ganz gut, ist aber bei Änderungen etwas umständlich zu handhaben.

Gruß,
Farath

Hallo Farath

Deine Formel ist etwas kompliziert, um so auf die Schnelle verstanden zu werden. Aber ich halte mein in der ersten Antwort angegebenes Rezept für absolut praktikabel: Wenn ich dich richtig verstehe, dann steht die genau gleiche Formel in allen Zellen A3:A22 von Rechenblatt1. Die beiden "Suchen-Teile" der Formel liefern aber auf allen Zeilen immer je das gleiche Ergebnis. Weshalb also die Ausdrücke mehr als je ein Mal berechnen?

Schreib die beiden Such-Formeln in irgend eine Zelle deines Rechenblatts1 (oder auch eines anderen Rechenblatts). Ich verwende nachstehend einfach "Referenz1", bzw. "Referenz2"" als Referenz zu den beiden Zellen mit den Suchformeln. Dann werden deine Formeln plötzlich recht einfach:

Wenn(IstNV( Wenn(Referenz1>Zeile-3;Zeile-2;));' ';Wenn(Referenz2>Zeile-3;Zeile-2;))

Ich habe den Sinn deiner Formel allerdings nicht verstanden, was aber nicht heisst, dass sie falsch ist. Ich habe sie mal in einem Dokument eingesetzt, um zu sehen, was sie eigentlich macht.
Der Inhalt der Zelle A1 in Rechenblatt1 (RB1) ist allein entscheidend. Er wird mit Werten in A3:A54 von Rechenblatt2 (RB2) und "Spell Record Sheet" (SRS) verglichen. Es gibt 4 Fälle:
1. Der Wert in A1 kommt weder im RB2 noch im SRS vor - dann enthalten die Zellen A3:A22 in RB1 je ein Leerzeichen. Frage: Ist das überhaupt nötig - oder sollte die Zelle einfach nichts anzeigen, dann geht auch '' statt ' '.
2. Der Wert in A1 kommt nur in RB2 vor, nicht aber in SRS - alle Zellen RB1!A3:A22 zeigen "NV!"
3. Der Wert in A1 kommt nur in SRS vor, nicht aber in RB2 - gleiches Ergebnis wie im Fall 1
4. Der Wert in A1 kommt sowohl in SR2 wie SRS vor - in RB1!A3:A22 erscheinen die Werte (Zeile-2) bis zum Maximalwert, der durch die Position in SRS bestimmt ist, an welcher der Wert RB1!A1 gefunden wurde. Die übrigen Zellen bis A22 sind leer.

Mir kommt die ganze Sache reichlich "Spanisch" vor. In meinem Versuch mit genau der von dir angegebenen Formel haben die Werte in Spalte E von RB2 und SRS überhaupt keinen Einfluss. Was machen denn die Referenzen zu diesen Spalten überhaupt in der Formel? Ich bin mir allerdings bewusst, dass "Suchen" sich je nach der Art der Werte im Suchbereich, bzw. in RB1!A1 anders verhält und ich nicht alle möglichen Kombinationen durchgespielt habe.

Kannst du nicht mal verbal (in der Art, wie ich oben die 4 Fälle beschrieben habe) beschreiben, was deine Formel genau machen soll und insbesondere auch, was für Werte in den Spalten A und E der beiden Rechenblätter RB2 und SRS stehen können. Möglicherweise gibt es für dein Problem eine ganz andere Lösung.

Gruss
Thomas

Hallo Thomas,

vielen Dank für Deine Antwort. Zunächst muss ich mich entschuldigen, ich habe beim Vereinfachen der obigen Formel einen Fehler gemacht. Korrekt ist die Formel:

Wenn(IstNV(Wenn(Suchen(A$1;Spell Record Sheet!$A$3:$A$54;Spell Record Sheet!$E$3:$E$54)>Zeile-3;Zeile-2;));
' ';
Wenn(Suchen(A$1;Spell Record Sheet!$A$3:$A$54;Spell Record Sheet!$E$3:$E$54)>Zeile-3;Zeile-2;))

Am einfachsten lässt sich die Formel verstehen, wenn man sie mal ausprobiert. Ich kann Dir gerne meine Ragtime Datei mal zuschicken. Das ist wahrscheinlich am effizientesten. Es handelt sich hierbei um ein Charakterblatt-Formular für das Pen&Paper Rollenspiel "Rolemaster". Ich habe mich dort in Sachen Formeln ordentlich ausgetobt. Daher ist Dein Tipp, die Berechnung in eigene Zellen auszulagern nicht so recht attraktiv für mich. In Sachen Performance wäre es evtl. ein Gewinn, aber ich bräuchte dann eine zusätzliche Tabelle, welche all meine Berechnungen fasst. Und das sind einige! Ich fürchte, dass ich dann dort noch schneller den Überblick verliere, als es grenzwertig jetzt schon der Fall ist.

Inhaltlich macht die Funktion das Folgende: im Spell Record Sheet wird für den Spieler eingetragen, welche Spells er bis zu welcher Stufe gelernt hat. In der Tabelle "Spells_Open_1", in der obige Formel steht, werden die Spells dann mit einer steigenden Laufenden Nummer angezeit.

Zum Beispiel (" : " stellt im Beispiel eine Zellgrenze dar)

Rechenblatt "Spell Record Sheet" zeigt folgende Einträge:
Spell List : #Levels
Delving Ways : 5
Detecting Ways : 0
Elemental Shields : 0

Das Rechenblatt "Spells_Open_1" zeigt dann:
Delving Ways
Lvl : Spell
1 :
2 : Text Analysis I
3 : Stone Analysis
4 : Metal Analysis
5 : Gas Analysis

Geändertes Beispiel:

Rechenblatt "Spell Record Sheet" zeigt folgende Einträge:
Spell List : #Levels
Delving Ways : 3
Detecting Ways : 0
Elemental Shields : 0

Das Rechenblatt "Spells_Open_1" zeigt dann:
Delving Ways
Lvl : Spell
1 :
2 : Text Analysis I
3 : Stone Analysis

Das hier im Lvl 1 nichts eingetragen ist, ist kein Fehler, sondern entspricht den Regeln. Aber wie geschrieben, am einfachsten lässt sich die Formel verstehen, wenn man sie mal ausprobiert. Wenn Du Interesse hast, nenn mir einfach eine Mail-Adresse an die ich die Datei schicken soll. Ich kann sie Dir dann gerne übermitteln.

Gruß,
Farath

Ich hab Dir die Datei geschickt.

Gruß,
Farath

Hallo Farath

Nun habe ich mir mal die Formeln in dieser Spalte deines Dokumentes angesehen und habe eine Reihe von Bemerkungen dazu - vorerst mal diejenigen, die sich auf deine Formel beziehen, auch solche, die völlig unwichtige Kleinigkeiten betreffen:

Wenn(IstNV(Wenn(Suchen(A$1;Spell Record Sheet!$A$3:$A$54;Spell Record Sheet!$E$3:$E$54)
>Zeile-3;Zeile-2;));' ';
Wenn(Suchen(A$1;Spell Record Sheet!$A$3:$A$54;Spell Record Sheet!$E$3:$E$54)
>Zeile-3;Zeile-2;))

1. Wenn eine Wenn-Funktion nur bei Erfüllung der Bedingung einen Wert liefern soll, so kann nach diesem Wert die Klammer geschlossen werden. Das Semikolon kann entfallen.

2. Wenn du eine Zelle löschen willst, kannst du einen "Null-Text" einsetzen. Zwischen den beiden Apostrophs (in der DE-CH-Version von RT: zwischen den beiden Anführungszeichen) muss also kein Leerzeichen stehen.

3. Die wichtige Bemerkung: Du fragst nach einer Vereinfachung der Formel, damit der gleiche Ausdruck nicht innerhalb der Formel doppelt berechnet werden müsse. Ich verweise auf meine früheren Bemerkungen: Nicht nur in der Formel auf jeder Zeile wird der gleiche Wert doppelt berechnet, sondern dies zu allem Überfluss auch noch auf jeder Zeile - im Beispieldokument, das du mir geschickt hast, also von Zeile 3 bis Zeile 25, total 2*23=46 mal, wo ein einziges Mal genügen würde. Zudem ist die Suchen-Funktion sicher nicht gerade eine, die keine Rechenzeit benötigt. Kein Wunder also, wenn die Geschichte langsam wird. Als ersten Schritt habe ich also mal das Rechenblatt mittels "Komponente öffnen" im eigenen Fenster geöffnet und in Zelle Q1 folgende Formel eingesetzt:

Suchen(A$1;Spell Record Sheet!$A$3:$A$54;Spell Record Sheet!$E$3:$E$54)

Dann wird die Formel, die du in den Zellen A3:A25 stehen hast, schon mal wesentlich einfacher:

Wenn(IstNV(Wenn(Q$1>Zeile-3;Zeile-2));'';Wenn(Suchen(Q$1>Zeile-3;Zeile-2))

Die Bemerkungen 1 und 2 habe ich dabei auch gleich berücksichtigt. Nun kommt aber ein zweiter, viel wesentlicherer Schritt: In den Zellen A3:A25 braucht es gar keine Formeln!!! Genau gleich lautende Formeln in untereinander stehenden Zellen schreien geradezu nach Anwendung eines Vertikalen Suchlaufs. Lösch also schon mal diese Formeln. Dafür schreibst du in Zelle Q2 des gleichen Rechenblatts folgende Formel (die oben beschriebene Formel in Zelle Q1 braucht es dabei auch!):

VSuchlauf(Spell Record Sheet!$A$3:$A$54;1;0;
SetzeZelle(Wenn(Q1>LfdIndex-1;LfdIndex;'');A3:A25;LfdIndex);LfdIndex≥23)

Die Formel schaut den Bereich A3:A54 im "Spell Record Sheet" an, in welchem Änderungen zu einer Änderung des Ergebnisses führen können. Im ersten Schritt ist der LfdIndex=1. Da die Bedingung des Suchlaufs mit dem Wert 1 immer erfüllt ist, wird ein Wert in die erste Zelle des Bereichs A3:A25 geschrieben. Dieser Wert ergibt sich aus dem Vergleich des Wertes in Zelle Q1 und dem laufenden Index. Ist das Ergebnis der Formel in Zelle Q1 z.B. 5 (wie in deinem Musterdokument), dann wird in Zelle A3 in diesem Schritt der LfdIndex (also im ersten Schritt der Wert 1) geschrieben und in den folgenden Schritten bis und mit Zelle A7 die Werte 2:5. Die weiteren Zellen der Tabelle werden gelöscht. Erreicht der LfdIndex den Wert 23, dann wird der Suchlauf abgebrochen.

Wenn du das so machst, so wirst du staunen, wie rasch die Werte berechnet sind - im Vergleich mit deiner Lösung ist das etwa wie ein ICE zu einem Lokalzug.

Ich habe nur diese Formeln angesehen. Mir scheint, das ganze Dokument habe bei analoger Anwendung meiner Vorschläge noch erhebliches Verbesserungspotenzial.

Noch eine letzte Bemerkung: Ich werde dir das so abgeänderte Dokument zurücksenden. Du wirst feststellen, dass ich die Zellen Q1 und Q2 mit roter Füllung versehen habe. Das ist die in meinem Buch empfohlene Technik, damit ich später die Formeln wieder finde. Noch besser wäre es, wenn in Spalte R die Formeln möglichst praxisnah erläutert würden, damit bei einer Fehlersuche oder einer späteren Anpassung noch verstanden wird, was mit den Formeln erreicht werden sollte.

Mit freundlichem Gruss
Thomas

Hallo Thomas,

vielen Dank für die ausführliche Hilfe. Ich habe mir gestern auch mal Dein Buch gekauft, das erklärt es auch sehr gut. Ich muss sagen, dass die Suchlauf-Funktionen zwar recht komplex sind - da muss man sich erst mal reindenken - aber sehr mächtig und felxibel. Ist toll, was man damit alles anstellen kann.

Gruß,
Farath