Wie soll ich denn anders mitteilen, dass C5:[C9]: bedeutet, dass in C5 die blaue Formel eingetragen wird und dann bis C9 ausgefüllt wird? Ich setze doch schon 2 Farben ein, die die Formel von etwas anderem trennen, und ich mag nicht jedesmal die lila Passage in Worten wiederholen müssen.
Dann ist alles gut. Das habe ich gemacht. Es läuft auch fast: nur eine Zeile ist in meinem Original Dokument falsch (F75). Dabei ist die wie alle anderen auch.
Ich habe jetzt nochmal ein Dokument in abgespeckter Form angehängt. Interessanterweise ist in diesem Dokument Gruppe A richtig (Zeilen 2-34), alle anderen aber falsch. Liegt dies an den unterschiedlichen Excel Versionen?
Nein, Du hast ZEILE(C1) nicht verstanden ... und willst es wohl auch weiter nicht verstehen.
Bei jedem neuen Block muss es wieder mit ZEILE(C1) losgehen.
Es ist auch nicht nett, dass Du das mit den Blöcken verschwiegen hast.
Alle Blöcke auf C1 umgestellt: Jetzt funktioniert es !
Ich bin davon ausgegangen ein kurzer Dummy würde reichen und dann kann ich es auf alle Blöcke kopieren. Beim nächsten Mal werde ich es gleich alles benennen.
Vielen, vielen Dank, dass Du bei meinen weiteren Fragen dabei geblieben bist und Deine weiteren Erklärungen.
Viel gelernt und mit dem Excel Virus infiziert :)
Leider ist die Formellösung LCohen's IMHO keine empfehlenswerte.
Füge mal vor Zeile 1 eine Zeile ein - nur ein Beispiel. Fürchterlich schwer zu findende Fehler sind Zeileneinfügungen unterhalb des ersten Blocks, wenn weiter unten Blöcke von einer Länge größer als die einzufügende Zeilennummer existieren.
Verwende seine Lösung nur, wenn Du dies nachvollziehen kannst und die Formel wirklich verstehst.
(Sorry LCohen, Du weisst, der konstruktive Streit mit Dir macht seit ~20 Jahren viel Spaß
)
Siehe meine Excel Don't #2:
https://berndplumhoff.gitbook.io/sulprob...xcel-donts
Anbei mein Lösungsvorschlag in Spalte G.
Du musst Dich nur entscheiden, ob Du den Rundungsfehler absolut oder relativ minimal halten möchtest (4. Parameter 1 oder 2).
Die Datei wird ohne jegliche Gewährleistung zur Verfügung gestellt, aber ich verwende einen aktuellen Virenscanner.
(12.12.2020, 11:45)Sulprobil schrieb: [ -> ]Füge mal vor Zeile 1 eine Zeile ein - nur ein Beispiel. Fürchterlich schwer zu findende Fehler sind Zeileneinfügungen unterhalb des ersten Blocks, wenn weiter unten Blöcke von einer Länge größer als die einzufügende Zeilennummer existieren.
"Fürchterlich schwer zu findende Fehler": Das ist seit VisiCalc in allen Tabellenkalkulationen so. Man kann sich z.B. mit Kontrollsummen behelfen ...
Interessant, hier eine Rundungsproblematik zu wittern, wo es doch auf REST bzw. KÜRZEN hinausläuft. Außerdem soll nicht in irgendeiner d'Hondtschen Weise "fair" verteilt werden, sondern die ersten werden immer bevorzugt.
(12.12.2020, 21:57)LCohen schrieb: [ -> ]"Fürchterlich schwer zu findende Fehler": Das ist seit VisiCalc in allen Tabellenkalkulationen so. Man kann sich z.B. mit Kontrollsummen behelfen ...
Kontrollsummen sind häufig nützlich, aber hier muss man (Du) lediglich sauber programmieren, z. B. in E2:
=MIN(3;KÜRZEN($D$36/ANZAHL2($D$2:$D$34))+(REST($D$36;ANZAHL2($D$2:$D$34))>=ZEILE($D2)-ZEILE($D$2)+1))+$D2
und dann hinunterkopieren bis zum Ende des ersten Blocks.
1. Die ZEILE Funktion nicht einfach "irgendwie" bei 1 starten lassen, sondern in direkter logischer Abhängigkeit von den Eingabedaten und nur von ihnen. Damit sichert man sich ab gegen Zeileneinfügungen und -löschungen. In diesem Fall hätte auch Jupe die Formel sicherlich richtig für die anderen Blöcke angepasst (wette ich).
2. Die ANZAHL Funktion auch direkt an die Eingabedaten binden, und nicht an ihre Beschreibung.
3. Nun kann man sehen, dass diese Formel nur von den Eingabedaten in D2:D34 abhängt, weil andere Referenzen nicht vorkommen.
Ich finde meine Funktion sbRoundToSum trotzdem schöner, aber ich bin natürlich voreingenommen, und wenn man als Werkzeug einen Hammer hat, dann sehen alle zu bearbeitenden Dinge wie Nägel aus
Have fun,
Bernd
(13.12.2020, 09:50)Sulprobil schrieb: [ -> ]...
3. Nun kann man sehen, dass diese Formel nur von den Eingabedaten in D2:D34 abhängt, weil andere Referenzen nicht vorkommen.
...
Ach, wie peinlich: natürlich auch von D36.
E2: =MIN(3;KÜRZEN(
(INDEX(D:D;VERGLEICH("";""&A2:A$9999;)+ZEILE()))/(VERGLEICH("";""&A2:A$9999;)+ZEILE()-2-VERWEIS(2;1/(A$1:A1="");ZEILE(A$1:A1))))+(REST(
(INDEX(D:D;VERGLEICH("";""&A2:A$9999;)+ZEILE()));(VERGLEICH("";""&A2:A$9999;)+ZEILE()-2-VERWEIS(2;1/(A$1:A1="");ZEILE(A$1:A1))))>=ZEILE()-VERWEIS(2;1/(A$1:A1="");ZEILE(A$1:A1))))+D2
Verteilsumme strikt nach genau einer Leerzeile unter einem Block (und möglicherweise ohne Leerzeile zum nächsten Block, auch wenn das scheußlich ist von Dir!). Notfalls entspr. Zeilen löschen (musste ich an einer Stelle).
Die Spalte A sauber halten (nur Blockdaten!).
Hier noch die ein wenig Rechenzeit sparende xl365-Schreibe, wer möchte:
E2: =LET(
NextEmpty;VERGLEICH("";""&A2:A$9999;)+ZEILE()-1;
PrevEmpty;VERWEIS(2;1/(A$1:A1="");ZEILE(A$1:A1));
BlockSum;INDEX(D:D;NextEmpty+1);
BlockCnt;NextEmpty-1-PrevEmpty;
MIN(3;KÜRZEN(BlockSum/BlockCnt+(REST(BlockSum;BlockCnt)>=ZEILE()-PrevEmpty)))+D2)
(13.12.2020, 13:21)LCohen schrieb: [ -> ]E2: =MIN(3;KÜRZEN(
(INDEX(D:D;VERGLEICH("";""&A2:A$9999;)+ZEILE()))/(VERGLEICH("";""&A2:A$9999;)+ZEILE()-2-VERWEIS(2;1/(A$1:A1="");ZEILE(A$1:A1))))+(REST(
(INDEX(D:D;VERGLEICH("";""&A2:A$9999;)+ZEILE()));(VERGLEICH("";""&A2:A$9999;)+ZEILE()-2-VERWEIS(2;1/(A$1:A1="");ZEILE(A$1:A1))))>=ZEILE()-VERWEIS(2;1/(A$1:A1="");ZEILE(A$1:A1))))+D2
Verteilsumme strikt nach genau einer Leerzeile unter einem Block (und möglicherweise ohne Leerzeile zum nächsten Block, auch wenn das scheußlich ist von Dir!). Notfalls entspr. Zeilen löschen (musste ich an einer Stelle).
Die Spalte A sauber halten (nur Blockdaten!).
Hier noch die ein wenig Rechenzeit sparende xl365-Schreibe, wer möchte:
E2: =LET(
NextEmpty;VERGLEICH("";""&A2:A$9999;)+ZEILE()-1;
PrevEmpty;VERWEIS(2;1/(A$1:A1="");ZEILE(A$1:A1));
BlockSum;INDEX(D:D;NextEmpty+1);
BlockCnt;NextEmpty-1-PrevEmpty;
MIN(3;KÜRZEN(BlockSum/BlockCnt+(REST(BlockSum;BlockCnt)>=ZEILE()-PrevEmpty)))+D2)
So programmiert man doch nicht.
Wenn Du etwas prüfen willst, dann verbesserst Du doch erst einmal die Tabellenstruktur. Zum Beispiel ordnest Du die Blöcke nebeneinander an, erst den Ursprungsblock (und natürlich die zu verteilende Anzahl darüber, damit der Block nach unten wachsen kann), dann rechts daneben den Block nach Verteilung, usw.
Dann kannst Du einfach SUMME(C:C) mit SUMME(D:D) vergleichen usw.
Du fährst doch Dein Auto auch nicht rückwärts von Bremen nach München, nur weil Du vorwärts in die Garage eingeparkt hast