lange Formel an einer Stelle anlegen und automatisch in Tabelle übernehmen

[derjahn]

Hallo,

meine letzte Frage hat ja so bombastisch funktioniert... da habe ich doch gleich mal noch eine:

Ich habe in einer Spalte (in jeder Zeile fortlaufend) folgende Formel:

Code: Alles auswählen

=WENN(ISTZAHL(E27);WENN(ODER(D27="Müller";D27="Meier";D27="Schulze";E27<=15);E27;RUNDEN(E27*0,6;1));"")

Die Zeilenfolge ist jedoch unregelmäßig unterbrochen von Zellen, in denen diese Formel nicht steht, weshalb ich nicht mit "nach unten ziehen" die Formel in der gesamten Spalte auf einen Rutsch übertragen kann.

Außerdem entwickelt sich die Formel weiter, weil neue Namen hinzukommen. Das manuell zu pflegen, wird auf Dauer anstrengend und ist fehlerbehaftet.

Durch die Zeilenunterbrechungen kann ich bei Formeländerung in der aktuellen Zelle auch nicht durch "nach oben ziehen" die Formelerweiterung auf die vorigen Zeilen übertragen, sondern bestenfalls "häppchenweise" in den einzelnen Bereichen, die fortlaufend ohne die Unterbrechungszeilen sind.

Deshalb meine Idee:
Ich lege in einer separaten Zelle die Formel an und die entsprechenden Zellen übernehmen die Formel automatisch - angepasst an ihre jeweilige Zeilenposition, damit das Ergebnis in der jeweiligen Zeile korrekt ist.
----------------

Zweitgedanke/Alternative:
Ich könnte in einer separaten Spalte eine Markierung setzen und wenn diese vorhanden ist, soll die Formel greifen. Beispieldatei:

Kundenstatistik_Test_2.ods

(34.06 KiB) 192-mal heruntergeladen

In Spalte F ("Ziel") soll eine Formel angewendet werden -
zum Beispiel:

Code: Alles auswählen

RUNDEN(E3*0,6;1)

, WENN in der selben Zeile der Name in Spalte D in der Spalte H vorhanden UND direkt daneben in Spalte G ein "a" eingetragen ist.
WENN der Name in Spalte H nicht vorhanden ist ODER nicht durch ein "a" in Spalte G markiert ist
ODER
WENN die Zahl in Spalte E ("Start") gleich oder kleiner 15 ist, soll in der aktuellen Zelle die Zahl aus der selben Zeile aus Spalte E übernommen werden.

Anders formuliert:
Der Name in D3 wird verglichen mit den Namen in Spalte H und
wenn er dort vorhanden UND in Spalte G mit einem "a" markiert ist, soll die Formel

Code: Alles auswählen

RUNDEN(E3*0,6;1)

in F3 angewendet werden, WENN die Zahl in E3 nicht kleiner oder gleich 15 ist.

Ergänzung (diese kriege ich wahrscheinlich selbst reingebastelt... nur der Vollständigkeit halber):
WENN jedoch in B3 "Wo.-Summe" ODER "Zwischensumme kumulativ" steht, soll die Formel aus E3

Code: Alles auswählen

=SUMME(E3:E5)

kopiert und automatisch angepasst werden

Code: Alles auswählen

=SUMME(F3:F5)

- wobei ich nicht wirklich weiß, wie ich das mit der automatischen Anpassung hinbekomme...

Mit den WENN-Formeln komme ich eigentlich gut zurecht, aber mir ist nicht klar, wie ich das mit dem Namensvergleich und der Markierungsprüfung machen soll.

----------------------------------
Es führen also zwei Varianten zum Ziel:
1. An einer Stelle wird eine Formel abgelegt (und kann dort bearbeitet, angepasst, verändert und erweitert werden) und jede betreffende Zelle holt sich diese Formel, passt sie an ihre eigene Position an und zeigt das für ihre Zeilenposition gültige Ergenis (und übernimmt Veränderungen).

oder

2. Eine Formel wird (direkt in der betreffenden Zelle - hier F:F) so gebaut, dass ich auch über die "Unterbrechungszeilen" mit den Zwischensummen hinweg per "nach oben schieben" oder "nach unten ziehen" längere Bereiche aktualisieren kann, wenn an der Formel Änderungen erfolgen.

Variante 1 ist natürlich "eleganter" und spart noch mehr Eingabearbeit, aber mir wäre die "Halbatomtik" von Variante 2 auch völlig ausreichend - für wirklich lange Tabellen mit mehreren hundert Zeilen wäre natürlich Variante 1 deutlich besser... das habe ich aber eher selten.

Ich bitte um Korrektur- und Ergänzungsfragen, wenn ich Fehler oder Missverständlichkeiten eingebaut haben sollte - herzlichen Dank.

[derjahn]

gelöscht, weil überflüssig

[derjahn]

gelöscht, weil überflüssig

[derjahn]

gelöscht, weil überflüssig

[derjahn]

Kurz vor [Gelöst] - aber noch ein schwieriges Problem:

Code: Alles auswählen

=WENN(ODER(B6="Wo.-Summe";B6="Zwischensumme kumulativ");E6;WENN(ODER(E6<=15;UND(INDEX(H:H;VERGLEICH(D6;H:H;0); );VERSCHIEBUNG(INDEX(H:H;VERGLEICH(D6;H:H;0); );0;-1)="a"));E6;RUNDEN(E6*0,6;1)))

Das erste WENN:

Code: Alles auswählen

WENN(ODER(B6="Wo.-Summe";B6="Zwischensumme kumulativ");E6;

kopiert (wenn ODER zutreffend) den WERT von E6 und berechnet nicht die eigene Spaltensumme neu.

F6 soll (in der langen Formel) die Formel aus E6 übernehmen (wenn ODER wahr ist) und nun in der Spalte F die entsprechenden Berechnungen ausführen - und NICHT den WERT aus E6 kopieren.

Das kann ich nirgends finden.

Ich habe der Formel auch einen Namen gegeben und es damit versucht, aber dann werden unten im Verlauf der Spalte immer die selben Berechnungen angestellt (immer Summe aus drei darüberstehenden Zeilen), weil immer die obige Formel aus E6 benutzt wird.

Ich brauche aber die Anpassung an die aktuelle Position in der Spalte... Ich brauche die Übernahme aus E6 in F6, aus E7 in F7, aus E16 in F16, aus E17 in F17 usw., weil die Anzahl der zu summierenden Zeilen immer unterschiedlich ist.
*argh*

[redeagle56]

Hallo,
danke für die Einmannshow.
Hattest du Probleme?

MfG Günter

[derjahn]

nö.

Habe das meiste doch selber rausgefunden.
Aber an der letzten Hürde scheitere ich - es ist also noch Fleisch zum Abknabbern da.

Ist immer wieder dasselbe: Schreibt man zu wenig, ist es nicht richtig, schreibt man viel, wird auch gebrummelt.
btw: Dein Kommentar ist auch nur leere Füllung.

[mikele]

Hallo,
irgendwie ist es per Formel lösbar. Das Ganze wird entsprechend aufwändig und noch schwieriger anzupassen bzw. bei Änderungen zu pflegen.
Es wäre aus meiner Sicht sinnvoller, das Gesamtprojekt vom Design her zu überdenken, konkret die Trennung von Dateneingabe und Datenauswertung.
Wie sieht also dein Gesamtprojekt aus?

[derjahn]

naja... im Prinzip ist das gezeigte schon alles in dieser Tabelle.

Hier ist die aktualisierte Version:

Kundenstatistik_Test_2.ods

(36.04 KiB) 278-mal heruntergeladen

Wenn ich jetzt noch lerne, wie ich das mit der Formel hinbekomme, dass ich die "Unterbrechungszeilen" in einem Zug einbeziehen kann, wenn ich mit "nach unten ziehen" oder "nach oben schieben" die Spalte aktualisiere, wenn sich an der Formel was ändert... Das wäre toll.

Ich habe herausgefunden, wie ich in der Markierungsspalte das "a" mit den Namen mitführe, wenn in der Namensliste Änderungen passieren.

Ich habe die Namen, die vorne/links in der Eingabetabelle eingetragen werden inkl. Dublettenvermeidung in der rechten Auswertungstabelle erfasst.

In Spalte F werden die Werte nun korrekt berechnet. Behelfsweise überspringe ich mit copy+paste die "Unterbrechungszeilen" mit den Zwischensummen.

Im Prinzip liefert mir die Tabelle jetzt, was ich brauche - noch nicht ganz vollständig automatisiert, aber vernünftig brauchbar.

[Mondblatt24]

Wenn ich jetzt noch lerne, wie ich das mit der Formel hinbekomme, dass ich die "Unterbrechungszeilen" in einem Zug einbeziehen kann, wenn ich mit "nach unten ziehen" oder "nach oben schieben" die Spalte aktualisiere, wenn sich an der Formel was ändert... Das wäre toll.

Dafür kann ich keine Lösung anbieten.
Ich hätte da aber eine einfachere Formel als Alternative für

Code: Alles auswählen

=WENN(ISTTEXT(B3);"";WENN(ISTLEER(E3);"";WENN(ODER(E3<=15;UND(INDEX(H:H;VERGLEICH(D3;H:H;0); );VERSCHIEBUNG(INDEX(H:H;VERGLEICH(D3;H:H;0); );0;-1)="a"));E3;RUNDEN(E3*0,6;1))))

anzubieten.

Code: Alles auswählen

=WENN(ODER(ISTTEXT(B3);E3="");"";WENN(E3<=15;E3;WENN(SUMMENPRODUKT(H:H&G:G=D3);RUNDEN(E3*0,6;1);E3)))

Gruß
Peter