Microsoft Excel

Herbers Excel/VBA-Archiv

Teilzahlen runden / bestimmte Summe


Betrifft: Teilzahlen runden / bestimmte Summe von: Daniel
Geschrieben am: 13.09.2019 16:19:55

Ich habe folgendes Problem:

In der Zelle A1 steht ein Fixwert.
In den Zellen B1-B8 sollen gerundetete Werte ausgegeben werden (ohne Nachkommastelle). Hierbei handelt es sich um prozentuale Anteile vom Fixwert A1.

z.B:
B1 = RUNDEN(15%*$A$1;0)
B2 = RUNDEN(20%*$A$1;0) etc.

Die Summe der gerundeten Zahlen B1-B8 müssen jedoch zwingend wieder die Fixzahl aus A1 ergeben.

Wenn ich nur wie oben beschrieben runde, kann es zu Abweichungen kommen. Gibt es hier eine Lösung?

  

Betrifft: AW: Teilzahlen runden / bestimmte Summe von: 1712974.html
Geschrieben am: 13.09.2019 16:26:30

Jein,

du könntest in B8 die Formel = A1-SUMME(B1:B7) schreiben.

Gruß zurück Gerd

  

Betrifft: AW: hierzu noch ... von: 1712982.html
Geschrieben am: 13.09.2019 17:42:50

Hallo Daniel,

... das kann wie angestrebt nur ermöglicht werden, wenn in A1 bereits eine Ganzzahl vorhanden ist, andernfalls muss ein Wert in B1:B8 den Dezimalanteil von A1 ausweisen.

Wenn A1 eine Ganzzahl ist, würde ich in Ergänzung zum Vorschlag von Gerd als Näherung vorschlagen, dass in B8 auch der Wert mit der größten prozentualen Vorgabe ermittelt wird.

Gruß Werner
.. , - ...


  

Betrifft: AW: hierzu noch ... von: 1713000.html
Geschrieben am: 13.09.2019 20:26:01

Hallo Werner,

dem muss ich leider widersprechen.
Das Problem tritt allgemein auf, wenn die gerundete Summe nicht identisch mit der Summe der gerundeten Summanden ist.
Man ist halt manchmal gezwungen, einen oder mehrere Beträge in die "falsche" Richtung zu runden. Dabei sucht man gewöhnlich den/die geringsten absoluten Fehler.

Viele Grüße,
Bernd P

  

Betrifft: AW: Deine Aussagen sind sicher allg. richtig ... von: 1713019.html
Geschrieben am: 14.09.2019 08:27:05

Hallo Bernd,

... und möglicherweise im konkreten Fall auch hier.
Doch ist momentan unbekannt, wie groß der Vorgabe-Fixwert in A1 ist. Je kleiner dieser ist, desto eher würde ich meinen Vorschlag für ausreichend halten, weil damit nur ein Wert evtl. vom gerundeten Ergebniswert mit dem Vorgabeprozentsatz abweicht.

Möglicherweise könnten aber noch weitere (uns für das Beispiel unbekannte) Faktoren einen Rolle spielen, ob es notwendig ist eine ausgewogenere Aufteilung vorzunehmen oder nicht.

Gruß Werner
.. , - ...

  

Betrifft: verteil mal 11 Eier auf 4 Personen von: 1713029.html
Geschrieben am: 14.09.2019 11:28:30

Hi,

Du meinst: 2 - 2 - 2 - 5

richtig ist: 2 - 3 - 3 - 3

WF

  

Betrifft: AW: hast proz. Zuordnung nicht berücksichtigt owT von: 1713087.html
Geschrieben am: 14.09.2019 18:12:59

Gruß Werner
.. , - ...

  

Betrifft: nö - ich zitiere Dich von: 1713096.html
Geschrieben am: 14.09.2019 18:37:22

"... weil damit nur ein Wert ... abweicht."
Nur ein abweichender Wert sind die 5 Eier.

WF

  

Betrifft: AW: gilt für 1 Teil-, nicht das Ges.-Ergebnis owT von: 1713100.html
Geschrieben am: 14.09.2019 18:45:37

Gruß Werner
.. , - ...

  

Betrifft: Ach, uneinsichtig - dann lass ich's. von: 1713102.html
Geschrieben am: 14.09.2019 18:57:26

.

  

Betrifft: AW: hast Du meinen Beitrag an Bernd gelesen? owT von: 1713105.html
Geschrieben am: 14.09.2019 19:07:44

Gruß Werner
.. , - ...

  

Betrifft: Ende von: 1713111.html
Geschrieben am: 14.09.2019 19:23:31

.

  

Betrifft: AW: mE verkennst Du (noch immer?) ... von: 1713165.html
Geschrieben am: 15.09.2019 10:37:41

Hallo WF,

... dass die Aufgabenstellung, die hier vom Thread-Eröffner angefragt wurde, anders als in der von Dir verlinkten Seite (http://www.excelformeln.de/formeln.html?welcher=347) gelöst werden kann(/muss).

Sollte der Lösungsansatz in meinem Beitrag an Bernd nicht richtig sein, dann weise dies nach bzw. zeige Deinen dafür konkret auf. Anderenfalls muss man davon ausgehen, dass Du nur Polemik betreiben wolltest.

Gruß Werner
.. , - ...

  

Betrifft: AW: Das gefiel mir! von: 1713207.html
Geschrieben am: 15.09.2019 15:50:04

Hallo WF,

Ein super Beispiel.

Have fun,
Bernd P

  

Betrifft: AW: Deine Aussagen sind sicher allg. richtig ... von: 1713031.html
Geschrieben am: 14.09.2019 11:35:09

Hallo Werner,

egal wie klein der Vorgabewert ist, musst Du ggf. mehrere Einzelwerte "falsch" runden, um die absolute Fehlersumme (bzgl. der Ursprungswerte) zu minimieren.

Dein Ansatz macht die Summen identisch, aber er kann je nach Lage und Größe der Eingabewerte einen recht hohen absoluten Fehler erzeugen. Weitere oder andere Faktoren mögen vielleicht die Entscheidung des OP bzgl. eines zu bevorzugenden Ansatzes beeinflussen, verbessern IMHO aber weder die Genauigkeit der Lösung noch die praktische Anwendbarkeit in Rechnungswesen und Controlling.

Google doch einfach mal nach TCRound.

Viele Grüße,
Bernd P

  

Betrifft: AW: seh ich hier noch immer etwas anders ... von: 1713086.html
Geschrieben am: 14.09.2019 18:10:52

Hallo Bernd,

... denn bei meinem Lösungsansatz gibt es bezogen auf die Vorgabe (prozentuale Aufteilung von A1, wobei deren Teilergebniswerte auf Ganzzahlen gerundet werden sollten) lediglich eine max Abweichung von absolut 1 und dies auch nur in einem Bruchteil der möglichen Ganzzahlen in A1 und dann auch nur für max einen der 8 Ergebniswerte B1:B8. Relativ betrachtet wird die max Abweichung von 1 für den einen Änderungswert natürlich um so kleiner, je größer der Wert in A1 ist.

Nachfolgend mein (jetzt noch etwas konkretisierter) Vorschlag für die Aufgabenstellung von Daniel (wobei ich mangels kompletter Vorgabe, die Prozentwerte in Spalte C willkürlich ergänzt habe; diese können natürlich so geändert werden, dass in C9 =100% eingehalten wird)

Die Spalte D dienst hier lediglich zur Veranschaulichung er ergebniskontrolle. Die Ermittlung der Hilfsergebniswerte der Spalte G könnte noch in die Formel B1 integriert werden. Der besseren Übersicht wegen hab ich hier jedoch darauf verzichtet.

Die mittels bedingter Formatierung gelb markierten Zellen, dienen hier ebenfalls lediglich zur Veranschaulichung, wo eine (automatische) Ergebniswert-Korrektur gegenüber der Hilfsspalte F vorgenommen wird.
(Der Formelteil ZELLE()%% in G1) kann im Normalfall noch eingespart werden)

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFG
113719679754878724,0%0,000% 54878720,010%
2 82318086,0%0,000% 82318080,020%
3 102897607,5%0,000% 102897600,030%
4 1371968010,0%0,000% 137196800,040%
5 1714960012,5%0,000% 171496000,050%
6 2057952015,0%0,000% 205795200,060%
7 2743935920,0%0,000% 274393590,070%
8 3429919825,0%0,000% 342991990,080%
9 137196797100%0,000% 1371967980,360%
10       

ZelleFormel
B1=WENN(F$9=A$1;F1;(ABS(G1)=MAX(INDEX(ABS(G$1:G$8);)))*(A$1-F$9)+F1)
D1=C1-B1/A$1
F1=RUNDEN(A$1*C1;)
G1=C1-F1/A$1+ZEILE()%%

Zellebedingte Formatierung...Format
B11: $B1<>$F1abc
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Gruß Werner
.. , - ...
  

Betrifft: AW: seh ich hier noch immer etwas anders ... von: 1713170.html
Geschrieben am: 15.09.2019 11:54:44

Hallo Werner,

Gib doch einmal in Deinem Beispiel in A1 die Zahl 11 ein und in C1:C8 die Zahlen 0.37, 0.39, 0.03, 0.04, 0.09, 0.03, 0.02 und 0.03.

Ich erhalte mit Deinen Formeln in B1:B8 die Zahlen 4, 4, 0, 2, 1, 0, 0, 0.

Korrekt wäre aber IMHO 4, 4, 1, 1, 1, 0, 0, 0.

Der absolute Fehler für B4 beträgt 2 - 0.44 = 1.56, was doch deutlich höher ist als 1.

Falls Du auf andere Zahlen kommst, lade bitte Deine Datei hoch.

Viele Grüße,
Bernd P

  

Betrifft: AW: genau hier lässt sich aber mE gut erkennen ... von: 1713182.html
Geschrieben am: 15.09.2019 13:06:09

Hallo Bernd,

... dass ein Lösungsansatz nur über die Minimierung des "absoluten Fehler im Endergebnis" zu suchen, wie Du es getan hast, aus meiner Sicht mindestens so unbefriedigend ist, wie Du in meinen Lösungsansatz siehst. Denn es geht hier mE nicht um eine "Gleichverteilung".

Denn anders betrachtet gibt es bei Deinem Ergebnis in Deinem Beispiel neben den zwei Abweichungen in B3 und B4 von den Teilergebniswerten sogar einen zusätzliche Differenz von unendlich zwischen B3 und B6 oder B8, die alle den gleichen Prozentsatz in C3, C6 und C8 haben.

U.a. aus diesem Grund, nehme ich den "Ausgleich" von absolut 1,0 nur über den "absoluten Fehler im Zwischenergebnis" nur in 1nem Wert gegenüber den mittels Faktoren aus Spalte C festgelegten Teilwerten vor.

Aus meiner Sicht gibt es nämlich keine 100% befriedigende Lösung insbesondere gerade für kleine Werten in A1.(Für kleine Werte wollte ich ja ursprünglich auch nur für den größten Wert in Spalte C einen evtl. notwendigen Ausgleich vornehmen, mit dem Ergebnis in Deinem Beiepiel 4 5 0 1 1 0 0 0).

Gruß Werner
.. , - ...

  

Betrifft: AW: Du darfst irren von: 1713201.html
Geschrieben am: 15.09.2019 15:02:49

Hallo Werner,

das Schöne ist:
- Du darfst irren
- Du musst Deine Irrtümer auch nicht einsehen
- Du musst nicht einmal das Problem verstehen

Deine Beiträge haben einen hohen Unterhaltungswert.

Es ist schon lustig, wenn Du erklärst dass Deine Formel maximal einen absoluten Fehler von 1 erzeugt, um nach der Widerlegung fröhlich auf andere Argumente umzuschwenken.

Genieß' den Sonntag,
Bernd P

  

Betrifft: Das gefällt mir. von: 1713204.html
Geschrieben am: 15.09.2019 15:37:30

.

  

Betrifft: AW: davon gehe ich aus und ... von: 1713219.html
Geschrieben am: 15.09.2019 18:41:54

Hallo Bernd,

... ich steh dazu, d.h. ich sehe meine Fehler und/oder Irrtümer schon auch ein, wenn sie mir sachlich nachgewiesen werden. Das hattest Du heute Mittag mit Deiner Beispieldatei getan. Ich hatte danach schon eingesehen, dass mein Lösungsvorschlag in bestimmten Konstellationen zu "unbefriedigten" oder gar falschen Lösungsergebnissen führt, was insbesondere bei Deinen vorgegebenen Beispieldaten deutlich wird.

Insofern glaubte ich schon, meine fehlerhafte Lösung eingestanden und somit auch das Problem verstanden zu haben. Wenn dies nicht so rüber gekommen war, dann denke ich, das ich es jetzt in ausreichender Form nachgeholt habe bzw. noch deutlicher nun schreibe: Mein Lösungsvorschlag nicht oder bestenfalls nur verwenden, wenn in A1 mindestens ein Wert größer 75 steht, die kleinste Prozentzahl in C1:C8 mind. 2% ist, alle Prozentzahlen verschieden groß sind und deren Differenzen untereinander mind. 2% betragen.

Trotzdem oder gerade deswegen sollte von Dir oder auch jemand Anderen aufgezeigt werden, wie denn ganz konkret auch für Deine Beispieldaten ein richtiger Formel-Lösungsvorschlag erzeugt werden kann, der auch die vorgegebene prozentuale Aufteilung korrekt berücksichtigt.

D.h. es darf als Ergebnis bei Vorhandensein von zwei- /oder auch mehrmals dem gleichen Prozentsatz in Spalte C im Endergebnis in Spalte B dafür auch stets nur der gleiche Ergebniswert zugewiesen sein und es dürfen auch keine Ergebniswerte entstehen, die zumindest nicht größer sind, als der Ergebniswerte für einen größeren Prozentwert in Spalte C (wie das z.B. bei mir falsch in B4 gegenüber B5 der Fall war). In dem von Dir als korrekt angegebenen Ergebniswerten kann ich das teilweise nicht erkennen.

Ein Formel-Lösungsansatz der dies wie angegeben umsetzt, würde zumindest mich schon interessieren.

Gruß Werner
.. , - ...

  

Betrifft: AW: davon gehe ich aus und ... von: 1713221.html
Geschrieben am: 15.09.2019 19:13:03

Hallo Werner,

es freut mich, wenn Dein Lösungsansatz bei Mondschein oder Temperaturen unter 0° Ergebnisse zu Deiner Zufriedenheit erzeugt.

Spaß beiseite:

Anbei meine Testdatei für meine VBA Lösung, die auch eine Formellösung zur Überprüfung enthält. Im Wesentlichen unterscheiden sich unsere Ansätze, wenn mehr als ein Wert "falsch" gerundet werden sollte, denke ich.

Die praktische Relevanz kannst Du in der Testdatei auch sehen. Versuch' doch bitte, Deinen Ansatz in die Korrektur der enthaltenen Gemeinkostenumlage einzubauen. Das würde ich ehrlich gern einmal sehen. Ich hoffe, Du ahnst wenigstens, warum allgemein der absolute Fehler möglichst klein gehalten wird.

Viele Grüße,
Bernd P

PS:
Die Testdatei wird ohne jede Gewährleistung zur Verfügung gestellt (aber ich verwende einen aktuellen Virenscanner).
https://www.herber.de/bbs/user/132046.xlsm

  

Betrifft: AW: die hierzu notwendige Anpassungen ... von: 1713223.html
Geschrieben am: 15.09.2019 19:51:29

Hallo Bernd,

... wollte ich ja z.B. von Dir sehen.

Ich hatte natürlich schon vor meinem vorherigen Beitrag versucht, eine geeignete Lösung zu finden, die der Vorgabe-Aufgabe des TE und meinen zuletzt dafür genannten Bedingungen und für Deine zuvor aufgezeigten Beispieldaten gerecht wird. Ich war aber auf keinen grünen Zweig gekommen. Hatte damit auch schon zu viel Zeit verbracht und warte jetzt erst einmal ab, ob und wie sich der thread weiterentwickelt. U.a. kann es ja auch sein, dass der TE noch andere Vorstellungen hat.

Gruß Werner
.. , - ...

  

Betrifft: AW: Meine Datei enthält eine gute Formellösung... von: 1713228.html
Geschrieben am: 15.09.2019 21:35:41

...aber VBA ist hier wesentlich besser.

  

Betrifft: gerundete Summe = gerundete Einzelwerte von: 1712992.html
Geschrieben am: 13.09.2019 18:46:40

siehe unsere:
http://www.excelformeln.de/formeln.html?welcher=347

WF

  

Betrifft: AW: Teilzahlen runden / bestimmte Summe von: 1712993.html
Geschrieben am: 13.09.2019 19:05:22

Hallo Daniel,

meine Lösung findest Du hier:
http://sulprobil.com/Get_it_done/IT/Excel_Fun/Excel_VBA/sbRoundToSum/sbroundtosum.html
Oder:
http://bplumhoff.de/html/sbroundtosum.html

Viele Grüße,
Bernd P

  

Betrifft: AW: Teilzahlen runden / bestimmte Summe von: 1713281.html
Geschrieben am: 16.09.2019 12:35:31

Vielen Dank für die vielen Antworten und die rege Beteiligung. Mir geht es im Endeffekt darum:

A1 (= Anzahl der zu leistenden Dienste = immer ganze Zahl)

Diese Anzahl an Diensten ist anteilmäßig (nach prozentualer Verteilung anhander verschiedener Parameter) durch die Personen 1-8 (B1-B8) zu leisten. Es können immer nur ganze Dienste geleistet werden, so dass die Zahlen gerundet werden müssen. Die Gesamtzahl der zu leistenden Dienste beträgt jedoch zwingend immer genauz A1. Es dürften so ca. 100 Dienste anfallen, zur Einordnung der Größenverhältnisse.

  

Betrifft: AW: neu nachgefragt ... von: 1713360.html
Geschrieben am: 16.09.2019 18:38:08

Hallo Daniel,

... ist die prozentuale Verteilung für die 8 Personen unterschiedlich oder gibt es welche, die den gleichen prozentualen Anteil erbringen müssen? Wie groß ist der kleinste prozentuale Anteil einer Person? Wenn es fixe prozentuale Anteile für die 8 Personen geben sollte, dann gib diese doch hier mal komplett an.

Gruß Werner
.. , - ...

  

Betrifft: AW: Teilzahlen runden / bestimmte Summe von: 1713300.html
Geschrieben am: 16.09.2019 13:56:38

bin leider mit VBA überfordert, weshalb ich mit der ein oder anderen Antwort hier nicht klar komme. Gibt es eine "unkomplizierte" Lösung?

  

Betrifft: AW: Teilzahlen runden / bestimmte Summe von: 1713375.html
Geschrieben am: 16.09.2019 22:14:34

Hallo Daniel,

Wie wäre es, wenn Du eine Beispieldatei hochlädst?
Es ist sicherlich ein Leichtes, die 4 vorgeschlagenen Ansätze da einzubauen.

Viele Grüße,
Bernd P

  

Betrifft: AW: nun neu, mit einer weiteren Hilfsspalte ... von: 1713458.html
Geschrieben am: 17.09.2019 12:48:10

Hallo Daniel,

... damit meine ich eine Formellösung gefunden zu haben, die zumindest nach meinen ersten Tests, Deinen und meinen Anforderungen weitestgehend gerecht werden sollte. Für kleinere Werte in A1 könnte man noch über die Einarbeitung einer zusätzlichen Bedingung nachdenken, dass dann (so vertretbar) keine überproportionale Differenz zwischen den Ergebnisswerte untereinander entsteht. Darauf hab ich hier verzichtet. Es werden aber auch für sehr kleine Werte (natürlich mit Ausnahme von 1) vertretbare Ergebniswerte erzielt. Teste ruhig mal mit A1=16 oder =11 oder gar =5.

Mindestens eine Hilfsspalte könnte zu Lasten komplizierter Formeln der verbleibenden zwar eingespart werden, woauf ich jedoch bewusst verzichtet habe. Die Spalte D dient lediglich zu Kontrollzwecken, ist aber nicht erfoderlich.

Arbeitsblatt mit dem Namen 'F_190917_1'
 ABCDEFGH
111122,0%-0,198% 21-0,155%
2 4237,0%0,838% 4110,635%
3 33,0%-0,297% 30 
4 33,0%-0,297% 30 
5 109,0%0,009% 1010,224%
6 4439,0%0,640% 4310,510%
7 44,0%-0,396% 41-0,260%
8 33,0%-0,297% 30 
9 111100,0%0,000% 109  
10        

ZelleFormel
B1=WENN(F$9=A$1;F1;F1-VORZEICHEN(F$9-A$1)*(KGRÖSSTE(H$1:H$8;ABS(A$1-F$9))<=H1)*(H1<>""))
D1=B1/A$1-C1
F1=RUNDEN(A$1*C1;)
G1=MIN(ABS(KÜRZEN((A$1-F$9)/ZÄHLENWENN(C$1:C$8;C1);));ABS(F1-WENNFEHLER(INDEX(F$1:F$8;VERGLEICH(AGGREGAT(15;6;C$1:C$8/(C$1:C$8>C1);1);C:C;0));0));1)
H1=WENN((G1=0)+(F1=0);"";F1/F$9+ZEILE()%%-C1)

Zellebedingte Formatierung...Format
B11: $B1<>$F1abc
F11: $B1<>$F1abc
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Gruß Werner
.. , - ...
  

Betrifft: AW: nun neu, mit einer weiteren Hilfsspalte ... von: 1713571.html
Geschrieben am: 18.09.2019 06:50:24

Vielen Dank für die vielen Antworten. Ich bin begeistert wieviel Zeit und Elan ihr hier hineinsteckt.

Werner, ich habe deine Lösung ausprobiert und komme im Endergebnis auch immer auf die richtige Summe. Allerdings deucht es mir, dass er bei dieser Lösung immer die Zahl aufrundet, die bereits die größte Abweichung hat. In einem Beispiel hat er 11,74 auf 13 "aufgerundet" und z.b. 14,36 auf 14 abgerundet.

Grüße

  

Betrifft: AW: es deucht Dir nicht nur so ... von: 1713593.html
Geschrieben am: 18.09.2019 09:28:04

Hallo Daniel,

... es war so. Hab das jetzt nachfolgend noch abgeändert. Formeln F1:H1 bleiben.

In B1 folgende Formel:

=WENN(F$9=A$1;F1;F1+WENN(F$9<A$1;KKLEINSTE(H$1:H$8;A$1-F$9)>=H1;-(KGRÖSSTE(H$1:H$8;F$9-A$1)<=H1))*(H1<>""))



Gruß Werner
.. , - ...



  

Betrifft: AW: es deucht Dir nicht nur so ... von: 1713621.html
Geschrieben am: 18.09.2019 11:08:47

Hallo Werner,

vielen Dank für das Engagement. Es funktioniert (alles was ich bisher testen konnte) für meine Bedürfnisse perfekt.

  

Betrifft: AW: bitteschön, freut mich! owT von: 1713627.html
Geschrieben am: 18.09.2019 11:35:09

Gruß Werner
.. , - ...

Beiträge aus dem Excel-Forum zum Thema "Teilzahlen runden / bestimmte Summe"