Inventurliste

Moin, Klaus und Thomas,

Klaus' Frage mußte Thomas Antwort herauskitzeln!

Und nun habe ich endlich, nach mehr als 10 Jahren RagTime-Benutzung,
den Suchlauf zum zweitenmal angewendet. Es klappt auf Anhieb. Die
Suchlaufformel aus Zelle H1 kann auch überall woanders stehen, wie ich
sehe und durch Ziehen ausprobiere. In der Zelle mit der
Suchlauffunktion wird das Ergebnis der untersten gefüllten Zelle der
Spalten A oder D angezeigt. Das ist etwas gewöhnungsbedürftig. Aber
man sieht so immerhin, wo die Suchlauffunktion formuliert ist. Ich
befürchtete, daß Suchlauf noch per Knopf angestoßen werden muß. Aber
bei jeder Änderung rechnet er selbst neu und zeigt immer aktuelle
Ergebnisse. Wirklich überzeugend.

Wenn Spaltenwerte in A oder D leer bleiben, ist das Ergebnis in E
nicht gleich. Bleibt A leer, steht in E nichts; bleibt D leer, steht
in E Null. Dasselbe, wenn A oder D mit 0 (Null) gefüllt wird (In
Rechenblattinformation > Format > Nullen nicht verborgen). Wie kommt
das?

RagTime ist eine Ideenwelt, der ich viel Verbreitung wünsche,
besonders für das bevorstehende neue Jahr 2011.

Allen RagTime-Foristen viel Gesundheit, Glück und großen Erfolg im
neuen Jahr!
Norbert Lindenthal

Zuerst mal auf Schweizerdeutsch - aber ihr werdet das schon verstehen können:
"Allne es guets Nöis!"

Hallo Norbert

Wenn Spaltenwerte in A oder D leer bleiben, ist das Ergebnis in E nicht gleich. Bleibt A leer, steht in E nichts; bleibt D leer, steht in E Null. Dasselbe, wenn A oder D mit 0 (Null) gefüllt wird (In Rechenblattinformation > Format > Nullen nicht verborgen). Wie kommt das?

Das ist ganz einfach zu erklären. Schau dir in der Suchlauffunktion
VSuchlauf(A:A;Und(LfdIndex>1;LfdZelle);0;SetzeZelle(LfdZelle*Index(D:D;LfdIndex);E:E;LfdIndex))
die Bedingung an
Und(LfdIndex>1;LfdZelle)
Das erste Argument bewirkt, dass nur Tabellenzeilen behandelt werden (schliesst die Kopfzeile aus), das zweite prüft, ob in der Zelle in Spalte A (LfdZelle) ein Wert ≠ 0 steht. Nur wenn beide Bedingungen erfüllt sind wird die Berechnung gemäss letztem Argument der Suchlauffunktion ausgeführt. Die Spalte D wird dabei also gar nicht betrachtet - was dort steht ist egal.

Nur wenn in Spalte A (ab Zeile 2) ein Wert ≠ 0 steht, wird also die Multiplikation A*D für die betreffende Zeile ausgeführt und das Resultat in Spalte E eingesetzt. 0 und "leer" in Spalte A verhindern also beide die Multiplikation, wogegen der Inhalt von Spalte D nur in der Multiplikation selbst eine Rolle spielt.

Die von mir angegebene Formel geht übrigens davon aus, dass in Spalte A ab Zeile 2 nur Zahlen oder leere Zellen stehen. Ein Text in einer Zelle in Spalte A führt dazu, dass die Suchlauffunktion nur bis zur betreffenden Zeile läuft und dann mit Fehler "WERT!" abgebrochen wird. Soll das verhindert werden, so muss die Bedingung ergänzt werden:
Und(LfdIndex>1;IstZahl(LfdZelle);LfdZelle)

Ich befürchtete, daß Suchlauf noch per Knopf angestoßen werden muß. Aber bei jeder Änderung rechnet er selbst neu und zeigt immer aktuelle Ergebnisse.

In vielen Fällen ist es zweckmässig, Suchlaufformeln mit einer Verriegelung zu ergänzen, die mit einem Knopf für eine einmalige Ausführung freigegeben wird (Beispiele siehe in meinem Buch), damit der Suchlauf nicht ewig läuft, was zu einer 100%-Porzessorauslastung führen würde. Das ist aber hier nicht nötig. Genau wie jede andere Formel nicht ewig immer wieder ausgeführt wird, sondern nur dann, wenn eines der Argumente geändert hat, ist das auch beim Suchlauf der Fall: Suchbereich und Bedingung beziehen sich nur auf Spalte A, die Multiplikation zudem auf Spalte D. Die Suchlaufformel wird also jedes Mal erneut ausgeführt, wenn sich in den Spalten A oder D etwas ändert, sonst aber nicht.

Gruss, Thomas

Hallo Norbert, hallo Thomas
Euch und allen anderen hier im Forum ein gutes neues Jahr!
Und auch RagTime wünsche ich ein erfolgreiches Jahr trotz aller vorgezogenen Nachrufe.

Die Diskussion um die Inventarliste von Norbert war mir sehr hilfreich bei einem laufenden Projekt. Thomas' Vorschlag, das "Herunterkopieren" von Formeln für Listen, deren Längen sich ständig vergrößern können, durch die Suchlauffunktion zu ersetzen, ist ja sehr schön.

Aber es gibt eine Schwäche, die ich noch nicht umgehen konnte. Thomas gibt die Funktion
VSuchlauf(A:A;Und(LfdIndex>1;LfdZelle);0;SetzeZelle(LfdZelle*Index(D:D;LfdIndex);E:E;LfdIndex))
an. Wenn jetzt z. B. in A1 der Wert 2 steht und in D1 der Wert 7, so wird in E1 richtig der Wert 14 ausgegeben. Gut. Aber wird jetzt A1 gelöscht oder auf Null gesetzt, so bleibt der alte Wert 14 in E1 stehen. Das ist zwar klar (VSuchlauf fasst die Zeile 1 wegen der Bedingung Und(LfdIndex>1;LfdZelle)gar nicht mehr an) aber nicht schön. Z. B. eine Summenbildung in Spalte E führt jetzt also zu einem Fehler.

Ich habe versucht, den Fehler zu umgehen, indem ich statt dessen
VSuchlauf(A:A;LfdIndex>1;0;Wenn(LfdZelle≠'';SetzeZelle(LfdZelle*Index(D:D;LfdIndex);E:E;LfdIndex);''))
genommen habe. Jetzt müssen zwar alle 15999 Zeilen bearbeitet werden, aber wohl nur beim ersten Ausführen (das dauerte einige Sekunden). Im Folgenden ging es immer ganz schnell.
Immerhin, jetzt wird E1 neu berechnet, auch wenn A1 zu Null geändert wird. Wenn aber A1 ganz gelöscht wird, bleibt der vorige Wert in E1 erhalten, er sollte aber wegen der WENN-Abfrage gelöscht werden, so hoffte ich.

Wieso wird die WENN-Klausel nicht berücksichtigt? Wo ist mein Fehler?

Gruß
Andreas

Hallo Andreas

Nur ganz kurz bevor ich weggehe: In Zeile 1 wird wegen der Bedingung "LfdIndex>1" gar nie etwas berechnet. Aus der ursprünglichen Frage ging klar hervor, dass die Tabelle eine Kopfzeile hatte. Und genau diese wurde mit dieser Bedingung bei der Berechnung ignoriert. In E1 wird also nie etwas ändern.

Ob in deiner Frage noch mehr steckt, werde ich mir erst heute Abend ansehen.

Gruss, Thomas

Hallo, Thomas,

Du hast recht. Natürlich wird die Zeile 1 nie angefasst. Mein Fehler: ich habe fälschlich A1 und E1 geschrieben, meinte aber immer A2 und E2 (es hätten genau so gut A16 und E16, also AN und EN mit N>1 sein können).

So, wie ich es geschrieben habe, war das alles nicht verständlich. Entschuldige bitte.

In der korrigierten Lesart wird meine Frage nach der nicht berücksichtigten WENN-Klausel hoffentlich deutlich.

Grüße
Andreas

Meine »Inventurliste« muß anders umgebaut werden:

Aus

A1 B1 C1 D1 E1 F1
A2 B2 C2 D2 E2 F2
… … … … … …

muß werden

leer leer A1 B1 C1 D1
E1 F1 A2 B2 C2 D2
E2 F2 … … … …
… … leer leer leer leer

Beschreibung: Die Werte der 6 Spalten werden zeilenweise um 2 Zellen
nach rechts verschoben, wobei Werte aus den 2 rechten Spalten in die 2
linken Spalten der nächsten Zeile verschoben werden. Die 2 mittleren
Spalten C und D haben Zahlenwerte (inkl. Null), die 4 äußeren
Spaltenfelder haben Zahlenwerte oder können leer sein.
Abbruchbedingung könnte sein, wenn C*D='' (leer) ist.

Geht das auch mit Suchlauf?
Ich bin Anfänger und verstehe nicht, wie die Werte verschoben
eingetragen werden in neue Spalten K bis P.

Norbert Lindenthal

Hallo Norbert

Natürlich lässt sich das mit Suchlauf machen - aber vielleicht auch noch viel einfacher! Allerdings verstehe ich deine Fragestellung nicht so ganz. In deinem "Beispiel" beziehst du dich auf die Spalten A:F und im letzten Satz sagst du etwas von den Spalten K:P. Ich sehe keinen Zusammenhang.

So wie ich dein Problem verstehe, handelt es sich um eine einmalige Operation - falls das nicht so ist, wäre ein Suchlauf tatsächlich eine elegante Lösung. Dann melde dich halt nochmals.

Für eine einmalige Operation:
- Spalten A:B im Spaltenkopf auswählen
- "Rechenblatt / Spalten einfügen…" (im Dialogfenster "Einfügen")
- Spalten G:H (das sind die früheren Spalten E:F) im Spaltenkopf auswählen und "Bearbeiten / Ausschneiden"
- Spalten A:B im Spaltenkopf auswählen
- "Bearbeiten / Einsetzen"
- Zellen A1:B1 auswählen
- "Rechenblatt / Zellen einfügen…" (im Dialogfenster: "nach unten" / "OK")

Gruss, Thomas

Korrektur!

Die zweitletzte Zeile der Anleitung muss natürlich heissen:
- Zellen A1:B1 auswählen

T

Hallo Norbert

Am Besten die Übertragung mit einem Knopf auslösen, so wie von mir auch für Andreas angegeben!

Ich setze die Formel in die Zelle H1 und benutze Zelle I1 als Hilfszelle für die Verriegelung. Natürlich können zwei beliebige andere Zellen verwendet werden. Die beiden Formeln müssen aber dabei entsprechend angepasst werden (nur je die Referenz zur Hilfszelle).

Die Formel des Knopfes:
SetzeZelle(1;Rechenblatt 1!$I$1)

Und die Formel in Zelle H1 (ohne Zeilenumbrüche!):

SpezialWenn(I1;SetzeZelle(0;I1)&
VSuchlauf(K:K;LfdIndex>1;0;HSuchlauf(K2:P2;1;0;SetzeZelle('';K:P;LfdIndex(1);LfdIndex)))&
VSuchlauf(A:F;Und(LfdIndex>1;Spaltenwert(3)≠'');0;
SetzeZelle(LfdZelle;M:M;LfdIndex)&SetzeZelle(Spaltenwert(2);N:N;LfdIndex)&
SetzeZelle(Spaltenwert(3);O:O;LfdIndex)&SetzeZelle(Spaltenwert(4);P:P;LfdIndex)&
SetzeZelle(Spaltenwert(5);K:K;LfdIndex+1)&SetzeZelle(Spaltenwert(6);L:L;LfdIndex+1)))

In obiger Formel...
- wird mit Zeile 1 die Formel verriegelt, so dass sie erst nach Knopfbetätigung erneut berechnet wird.
- wird mit Zeile 2 die Zieltabelle gelöscht (einfacher ginge es mit Martin's Power Function "LöscheBereich")
- werden in Zeile 3 die Parameter (Bereich und Bedingung) des Suchlaufs für die Datenübertragung definiert - vorausgesetzt wird, dass die Zelle in Spalte C nicht leer ist.
- werden mit Zeile 4 die Werte aus den Spalten A und B in die Spalten M und N übertragen.
- werden mit Zeile 5 die Werte aus den Spalten C und D in die Spalten O und P übertragen.
- werden mit Zeile 6 die Werte aus den Spalten E und F in die Spalten K und L der folgenden Zeile übertragen.

Ich hoffe, ich habe dein Problem richtig verstanden!

Mit freundlichem Gruss, Thomas

Hallo Andreas

Da hab ich heute Morgen etwas gar rasch geantwortet. Vermutlich wolltest du gar nie von den Zellen in Zeile 1 sprechen, sondern hast einfach fälschlicherweise die Adressen in der ersten Tabellenzeile mit 1 statt 2 indexiert.

Nun aber zu deinem Problem mit der ignorierten Wenn-Funktion:

VSuchlauf(A:A;LfdIndex>1;0;Wenn(LfdZelle≠'';SetzeZelle(LfdZelle*Index(D:D;LfdIndex);E:E;LfdIndex);''))
Immerhin, jetzt wird E1 neu berechnet, auch wenn A1 zu Null geändert wird. Wenn aber A1 ganz gelöscht wird, bleibt der vorige Wert in E1 erhalten, er sollte aber wegen der WENN-Abfrage gelöscht werden, so hoffte ich.
Wieso wird die WENN-Klausel nicht berücksichtigt? Wo ist mein Fehler?

Du hast doch in der Wenn-Funktion ganz klar formuliert, dass der SetzeZelle-Befehl nur ausgeführt werden solle, wenn die Zelle in Spalte A nicht leer sei. Weshalb erwartest du denn, dass er trotzdem ausgeführt wird, obwohl du die Zelle leerst?

Um all die möglichen Fälle von Änderungen und Löschungen zu berücksichtigen, empfehle ich, einen Knopf zu definieren, mit welchem die Berechnung ausgelöst wird.

Der Knopf hat die Formel: SetzeZelle(1;Rechenblatt 1!$I$1)

Und die Formel in Zelle H1 wird wie folgt ergänzt (ohne Zeilenumbruch):

SpezialWenn(I1;SetzeZelle(0;I1)&VSuchlauf(E:E;Und(LfdIndex>1;LfdZelle);0;SetzeZelle('';E:E;LfdIndex))&
VSuchlauf(A:A;Und(LfdIndex>1;LfdZelle);0;SetzeZelle(LfdZelle*Index(D:D;LfdIndex);E:E;LfdIndex)))

Mit der Formel wird zuerst die Verriegelung in Zelle I1 zurückgesetzt, dann die ganzen berechneten Werte in Spalte E gelöscht und schliesslich die ursprüngliche Berechnung durchgeführt.

Gruss, Thomas

Hallo Thomas,

ohje, da habe ich wohl tief und fest geschlafen. Klar, dass da nichts passiert, wenn die Zelle in Spalte A leer ist.
Mit

VSuchlauf(A:A;LfdIndex>1;0;Wenn(LfdZelle≠'';SetzeZelle(LfdZelle*Index(D:D;LfdIndex);E:E;LfdIndex);SetzeZelle('';E:E;LfdIndex)))

arbeitet die Funktion so, wie ich es haben will. Für meine Anforderungen ist dann der Knopf und die "SpezialWenn"-Ergänzung überflüssig.

Dank für Deine Mühe
Andreas