Excel Beträge spitz rechnen

[snb]

Wir wissen genau was volatile ist.
Volatile ist nicht verboten, wie du vielleicht meinst.

[Sulprobil]

Wir wissen genau was volatile ist.
Volatile ist nicht verboten, wie du vielleicht meinst.

Aber wenig ratsam, wenn eine nicht-volatile Lösung existiert, auch wenn Du vielleicht nicht zustimmst.

Der Formelzirkus hier ist recht unterhaltsam, es hält etwas in Übung. Nichts für ungut.

Viele Grüße,
Bernd P

[LCohen]

Volatile Formeln werden dann überdramatisiert, wenn man deren Ergebnisse nach ihrer Ermittlung in Werte umwandeln kann. Das gilt für fast alle kaufmännischen Bereiche.

Und gar keine Rolle spielen sie, wenn man ein Modell von vornherein per VBA errechnen lässt. Egal, ob mit oder ohne Verwendung von Tabellenformeln.

[snb]

@Sul

Bitte nicht quoten, ist redundant (noch 'schlimmer' als volatile).

[Sulprobil]

Bitte bleib beim Thema.

[LCohen]

Sub ZeitvergleichTagesaufteilung() 'benötigt ~20 Sekunden
Workbooks.Add xlWorksheet
Dim f(4)
n = 50000
a = "EOMONTH(RC2,0)"
b = "DATE(YEAR(RC2),MONTH(RC2)+1,0)"
c = "INDIRECT(""1:""&RC2-RC1)"
d = "R1C1:INDEX(C1:C1,RC2-RC1)"
f(1) = "=(DATEDIF(RC1-DAY(RC1)," & a & "+1,""M"")-((DAY(" & a & ")+1-" & _
       "DAY(RC2))/DAY(" & a & ")+(DAY(RC1)-1)/DAY(EOMONTH(RC1,0))))*RC3"
f(2) = Replace(f(1), a, b)
f(3) = "=SUMPRODUCT(RC3/DAY(EOMONTH((RC1+ROW(" & c & ")-1),0)))"
f(4) = Replace(f(3), c, d)
Range("A1:A" & n) = "1/1/18"
Range("B1:B" & n) = "6/3/18" '(= 3. Juni)
Range("C1:C" & n) = 100
For i = 1 To 4
 t = Timer
 With Range(Cells(1, i + 3), Cells(n, i + 3))
  .FormulaR1C1 = f(i)
  e = Timer - t
  .Value = .Value
 End With
 Msg = Msg & Chr(10) & Chr(10) & Format(e, "0000.0") & ": " & f(i)
Next
MsgBox Msg
End Sub

Die Zeitnahme erbringt folgende Ergebnisse: 

• (Bosko): MONATSENDE(B1;0) und DATUM(JAHR($B1);MONAT($B1)+1;0) sind annähernd gleich schnell. Somit ist die Antik-Excel-Lösung nicht schlechter.
  
• (snb): Die Schleifenlösungen sind bei nur ca. 3 Tagen Dauer (!) noch ähnlich schnell, wie die mathematischen. Ab dann benötigen sie jedoch ein Vielfaches davon, da wie bei den Erbsen jeder einzelne Tag abgezählt werden muss. Das Vielfache steigt dabei nicht linear um den Faktor 1 an, sondern schwächer.
  
• (snb): Schleifen-INDEX benötigt bei größeren Zeitdifferenzen (100 Tage) etwa die Hälfte der Zeit von -INDIREKT, zusätzlich zum Vorteil der Nonvolatilität. Ein Nachteil der INDEX-Schleifenlösung ist jedoch: Man darf nicht einfach mal eben so Zeilen einfügen.
  

Ich konnte Bernds Lösung (die der von Bosko ähnlich ist) leider nicht ergebnisfehlerfrei abschreiben, da ich mich vermutlich bei den Klammerungen vertan habe.

[snb]

Und was mit n=1 ?


Und UDF:

Code:

Function F_snb(sn)
   For j = sn(1, 1) To sn(1, 2)
      y = y + sn(1, 3) / Day(DateSerial(Year(j), Month(j) + 1, 0))
   Next
   F_snb = y
End Function

In D1:

PHP-Code:

=F_snb(A1:C1) 


[LCohen]

Hallo snb,

Deine Lösung reicht in den meisten Fällen aus, da man ja niemals 50000 Formeln dieser Art NEU rechnen muss. Die ersten 49983 kann man da schon längst platt gemacht haben. Ich wollte nur klarstellen, dass auch der PC zum Erbsenzählen länger braucht, als für mathematische Berechnungen.

Function: Kannst Du selbst einbauen; interessieren mich nur in Sonderfällen. Ich stehe auf Sub's - und dies ist rein auf VBA-Ebene gemeint ;).

EDIT: Hab's getestet (mit Schleifenende -1): Ca. 3 Minuten (also gut 18 Mal langsamer als die Tabellenschleifenformeln). Noch ein Nachteil: Die Function kann man nicht so einfach per Konstruktionsmakro bauen (siehe aber Nepumuk im alten office-Loesung ohne /p).

[snb]

Ganz einfach:  http://www.snb-vba.eu/VBA_Excel_VBprojec...#L_3.2.2.3

NB. Es gibt keine 'nicht-mathematische' Berechnungen (eine interne Widerspruch) ;)

[LCohen]

Sehr schön, Dein Link! Da spare ich ja direkt noch ein paar Zeilen ein, wenn ich meine Konstruktionsmakrodatei vorstelle ...

Mit "nicht-mathematisch" hast Du recht. Ich meine wohl "nicht-algorithmisch". Oder irgend so etwas. Vielleicht weißt Du ja, was ich meine ;)

Denn Du rechnest ja auch im Kopf direkt =23-9 statt =ANZAHL(9;10;...;22).