Monatsberechnung und Kummulation

Bild

Betrifft: Monatsberechnung und Kummulation
von: BorussenFritz
Geschrieben am: 19.02.2005 06:34:14
Hallo Herber-Forum,
ich bastele schon seit längerer Zeit an einem Problem, dass ich zwar
in der Einzelberechnung lösen, aber nicht automatisieren kann. Vielleicht
hat jemand von Euch Lust, mir dabei zu helfen:
Folgende Aufgabe stellt sich mir:
********PlanSoll*******Ist******NeuesSoll
Januar*****50***********10********** 0
Februar*****50**********************53
März********50**********************53
April*******50**********************53
Mai*********50**********************53
Juni********50**********************54
Juli********50**********************54
August******50**********************54
September***50**********************54
Oktober*****50**********************54
November****50**********************54
Dezember****50**********************54
Summen******600****10***************590
Ist + Neues Soll = 600
Anmerkung: Summe Ist + Neues Soll = PlanSoll
Sobald ich im jeweiligen Monat einen IST-Wert eingebe,
soll Excel automatisch für die kommenden Monate das
"NeuesSoll" berechnen. IST-Werte und Werte "Neues Soll"
ergeben zusammen das "PlanSoll".
Wichtig wäre mir, dass bis auf die Spalte "Ist" die Tabelle
schreibgeschützt sein soll, der Anwender also lediglich die
"erarbeiteten" Ist-Werte eintragen soll. Daher wäre m.E. wohl eine
VBA-Lösung oder eine benutzerdefinierte Funktion erforderlich.
Wäre echt Klasse, wenn Ihr mir helfen könntet.
Bis dahin und ein schönes Wochenende wünscht
BorussenFritz :-)

Bild

Betrifft: AW: Monatsberechnung und Kummulation
von: Reinhard
Geschrieben am: 19.02.2005 07:19:53
Hi Fritz,
ich verstehe nicht genau deine Erläuterungen, aufgrund der Erläuterung müsste die tavelle m.E. so aussehen:
Tabellenblattname: Tabelle1
         A          B        C        D       
 1      Monat   PlanSoll   Ist   NeuesSoll   
 2     Januar         50    10          40   
 3    Februar         50                50   
 4       März         50    34          16   
 5      April         50                50   
 6        Mai         50                50   
 7       Juni         50                50   
 8       Juli         50     4          46   
 9     August         50                50   
10  September         50                50   
11    Oktober         50                50   
12   November         50                50   
13   Dezember         50                50   
14      Summe        600    48         552   
Benutzte Formeln:
B14:  =SUMME(B2:B13)
C14:  =SUMME(C2:C13)
D2:  =B2-C2
D3:  =B3-C3
D4:  =B4-C4
D5:  =B5-C5
D6:  =B6-C6
D7:  =B7-C7
D8:  =B8-C8
D9:  =B9-C9
D10:  =B10-C10
D11:  =B11-C11
D12:  =B12-C12
D13:  =B13-C13
D14:  =SUMME(D2:D13)
Gruß
Reinhard
Tabelle wurde eingefügt mit Code von https://www.herber.de/forum/technik/messages/1226.html

Bild

Betrifft: AW: Monatsberechnung und Kummulation
von: BorussenFritz
Geschrieben am: 19.02.2005 11:39:31
Hallo Reinhard,
zunächst vielen Dank für Deine Hilfe. Der in Deinem Beispiel
aufgeführte Weg ist aber nicht richtig, da ich mich wohl nicht
richtig ausgedrückt habe:
Also, ich habe ein PlanSoll von 600. Im Januar werden davon 10
erwirtschaftet, also muss ich im Februar bis Dezember linear
590 erwirtschaften, um mein Plansoll am Jahresende zu halten,
also 590 / 11 Monate = 53,64 für die Monate Februar bis Dezember.
Im Februar werden 70 erwirtschaftet. Januar und Februar = 80.
PlanSoll 600 abzgl. IST von 80 = 520. Beginnend ab März sind
nun 520 linear zu erwirtschaften, also 520 / 10 Monate = 52 im
Monat usw.
Tja, das ist mein Problem. Hoffentlich kannst Du (oder Ihr) mir
helfen. Vielen Dank!
BorussenFritz :-)
Bild

Betrifft: AW: Monatsberechnung und Kummulation
von: Reinhard
Geschrieben am: 19.02.2005 12:10:08
Hi Fritz,
dann poste doch mal ein komplettes Jahr. Wohin soll am Jahresendedie Differenz, wenn nicht exat 600 erreicht wurden?
Ins neue Jahr zu Januar mitdazu? Dann lass mal deine Tabelle bis März des Folgejhres laufen...
Gruß
Reinhard
Bild

Betrifft: AW: Monatsberechnung und Kummulation
von: BorussenFritz
Geschrieben am: 19.02.2005 12:32:42
Hallo Reinhard,
die Differenz braucht nicht fortgeschrieben werden. Bereits jetzt vielen
Dank!
BorussenFritz :-)
https://www.herber.de/bbs/user/18330.xls
Bild

Betrifft: AW: Monatsberechnung und Kummulation
von: Reinhard
Geschrieben am: 19.02.2005 12:49:57
Hi Fritz,
mit "poste doch mal ein komplettes Jahr" meinte ich garantiert nicht leere Zellen in C2:D13 :-(
Bild

Betrifft: Plan Ist Soll
von: Reinhard
Geschrieben am: 19.02.2005 12:57:14
Hi Fritz,
Tabellenblattname: Tabelle1
       A        B        C       D      
 1  Monat   PlanSoll   Ist   SollNeu   
 2    Jan         50    10         0   
 3    Feb         50    70         0   
 4   März         50              52   
 5  April         50              52   
 6    Mai         50              52   
 7   Juni         50              52   
 8   Juli         50              52   
 9    Aug         50              52   
10    Sep         50              52   
11    Okt         50              52   
12    Nov         50              52   
13    Dez         50              52   
14  Summe        600    80       520   
Benutzte Formeln:
B14:  =SUMME(B2:B13)
C14:  =SUMME(C2:C13)
D2:  =WENN(C2="";$B$14/(12-letzte);0)
D3:  =WENN(C3="";($B$14-SUMME($C$2:$C$13))/(12-letzte);0)
D4:  =WENN(C4="";($B$14-SUMME($C$2:$C$13))/(12-letzte);0)
D5:  =WENN(C5="";($B$14-SUMME($C$2:$C$13))/(12-letzte);0)
D6:  =WENN(C6="";($B$14-SUMME($C$2:$C$13))/(12-letzte);0)
D7:  =WENN(C7="";($B$14-SUMME($C$2:$C$13))/(12-letzte);0)
D8:  =WENN(C8="";($B$14-SUMME($C$2:$C$13))/(12-letzte);0)
D9:  =WENN(C9="";($B$14-SUMME($C$2:$C$13))/(12-letzte);0)
D10:  =WENN(C10="";($B$14-SUMME($C$2:$C$13))/(12-letzte);0)
D11:  =WENN(C11="";($B$14-SUMME($C$2:$C$13))/(12-letzte);0)
D12:  =WENN(C12="";($B$14-SUMME($C$2:$C$13))/(12-letzte);0)
D13:  =WENN(C13="";($B$14-SUMME($C$2:$C$13))/(12-letzte);0)
D14:  =B14-C14
Namen in der Tabelle:
letzte:  =MAX(WENN(ISTZAHL(VERGLEICH({"".-1E+307};Tabelle1!$C$2:$C$13;-1));VERGLEICH({"".-1E+307};Tabelle1!$C$2:$C$13;-1)))
Gruß
Reinhard
Tabelle wurde eingefügt mit Code von https://www.herber.de/forum/technik/messages/1226.html

Bild

Betrifft: OT @Reinhard
von: Hübi
Geschrieben am: 19.02.2005 13:05:53
Hi Reinhard,
ich habe deinen Code für "arme Leute" mal ausprobiert. Bei mir verschieben sich die Spaltenköpfe immer nach ganz links. Hast du den Code nachgebessert - bei dir ist´s ja OK?
Gruß Hübi
Bild

Betrifft: AW: OT @Reinhard
von: Reinhard
Geschrieben am: 19.02.2005 13:36:38
Hallo Hübi,
jain, habe schon nachgebessert, bzw bin laufend dabei wenn ich was entdecke, aber an den Spaltenköpfenpositionen habe ich nichts bewusst geändert.
Geändert habe ich die Zeile:
anz = ThisWorkbook.Names.Count 'Anzahl der im Workbook benutzten Namen ermitteln
in
anz = ActiveWorkbook.Names.Count 'Anzahl der im Workbook benutzten Namen ermitteln
da das Makro sonst in Personl.xls die namen zählt :-)
Weiterhin habe ich die Funktion
Function SName(ByVal sp As Integer) As String
entfernt.
Anstatt dem Funktionsaufruf im Makro:
A1Name = SName(.Cells(1, s).Column)
steht dort jetzt
A1Name = Replace(.Cells(1, s).Address(0, 0), "1", "")
Anschliessend der aktuelle Code, wegen Html-Tags müssen dort dann nur noch die html-Tags korrekt geschrieben werden, also in
Mastersatz = "(pre)" & Left(Mastersatz, Len(Mastersatz) - 1) & Formeln & vbLf
die runden Klammern von "(pre)" ersetzen durch die eckigen Klammern, dito für die Codezeile vor End With.
Meine Personl.xls mit dem Code und einem Klassenmodul dass den Verweis für fm20.dll (MS Forms 2.0 Object Lib) setzt, ist hier: https://www.herber.de/bbs/user/18334.xls
Gruß
Reinhard

Sub herber()
'Programm zum formatierten Einfügen von kleinen Beispieltabellen in wer-weiss-was
'Es werden auch benutzte Formeln und Namen aufgelistet
'Februar2005 Reinhard
'Im VBA-EDitor muss über Extras---Verweise der Verweis auf MS Forms2.0 Object Library
'gesetzt sein, sonst Fehlermeldung bei Dim kurz as DataObject
'Anwendung der Sub ist einfach, in Tabelle gewünschten Bereich markieren,
'Makro ausführen, dann in wer-weiss-was mit Strg+V einfügen
'In den Remarks ist mit positionieren oder/und formatieren das Einfügen von Leerzeichen gemeint
Dim anzS As Integer, s As Integer, anzZ As Long, z As Long
Dim ZeilenSatz() As String, Breite() As Integer, Mastersatz As String, A1Name As String
Dim vor As Integer, hinter As Integer
Dim Formeln As String, Bezeichnungen As String
Dim anz As Integer, n As Integer, Länge As Integer
Dim kurz As DataObject
With Selection
    anzS = .Columns.Count 'Anzahl Spalten im markierten Tabellenbereich
    anzZ = .Rows.Count 'Anzahl der Zeilen
    ReDim Breite(anzS) 'jede Spalte hat eine Breite
    ReDim ZeilenSatz(anzZ) 'aus der Zeile plus Füll-Leerzeichen wird ein Zeilensatz
    For s = 1 To anzS 'Schleife um pro Spalte die jeweilig höchste Breite zu ermitteln
        Breite(s) = 0
        For z = 1 To anzZ
            If Len(.Cells(z, s).Value) > Breite(s) Then
                Breite(s) = Len(.Cells(z, s).Value)
            End If
        Next z
    Next s
    For z = 1 To anzZ 'die zeilennummer in jedem Zeilensatz wird generiert und formatiert
        ZeilenSatz(z) = Right("     " & .Cells(z, 1).Row, Len(.Cells(anzZ, 1).Row)) & "  "
    Next z
    'MasterSatz wird mit Blattnamen gefüllt
    Mastersatz = "Tabellenblattname: " & ActiveSheet.Name & vbLf & vbLf
    'Mastersatz wird positioniert um A B C usw aufzunehmen
    Mastersatz = Mastersatz & "   " & String(Len(.Cells(anzZ, 1).Row), " ")
    For s = 1 To anzS 'In MasterSatz werden die Spaltenbezeichnungen aufgrund ihrer Spaltenbreite eingefügt
        A1Name = Replace(.Cells(1, s).Address(0, 0), "1", "")
        If Breite(s) < Len(A1Name) Then Breite(s) = Len(A1Name)
        vor = Int((Breite(s) - Len(A1Name)) / 2)
        hinter = Breite(s) - vor - Len(A1Name)
        Mastersatz = Mastersatz & String(vor, " ") & A1Name & String(hinter, " ") & "   "
    Next s
    Mastersatz = Mastersatz & vbLf
    'Die Zellen der jeweiligen Zeile werden eingelesen und gemäß Spaltenbreite formatiert, dann in Mastersatz aufgenommen
    For z = 1 To anzZ
        For s = 1 To anzS
            ZeilenSatz(z) = ZeilenSatz(z) & Right(String(Breite(s), " ") & .Cells(z, s).Value, Breite(s)) & "   "
        Next s
        Mastersatz = Mastersatz & ZeilenSatz(z) & vbLf
    Next z
    Formeln = ""
    For s = 1 To anzS ' alle Zellen werden geprüft ob sie Formeln enthalten
        For z = 1 To anzZ
            If .Cells(z, s).HasFormula Then
                Formeln = Formeln & .Cells(z, s).Address(0, 0) & ":  " & .Cells(z, s).FormulaLocal & vbLf
            End If
        Next z
    Next s
    If Formeln <> "" Then 'wenn es Formeln gibt
        Formeln = vbLf & vbLf & "Benutzte Formeln:" & vbLf & Left(Formeln, Len(Formeln) - 1)
    End If
    'Formeln werden in Masteratz gelesen
    Mastersatz = "(pre)" & Left(Mastersatz, Len(Mastersatz) - 1) & Formeln & vbLf
    anz = ActiveWorkbook.Names.Count 'Anzahl der im Workbook benutzten Namen ermitteln
    
    If anz >= 1 Then 'Wenn es Namen gibt
        Bezeichnungen = vbLf & vbLf & "Namen in der Tabelle:" & vbLf
        Länge = Len(ActiveWorkbook.Names.Item(1).Name)
        For n = 1 To anz
            If Länge < Len(ActiveWorkbook.Names.Item(n).Name) Then Länge = Len(ActiveWorkbook.Names.Item(n).Name)
        Next n
        'Länge = Länge + 3
        For n = 1 To anz
            Bezeichnungen = Bezeichnungen & Left(ActiveWorkbook.Names.Item(n).Name & String(Länge, " "), Länge) & ":  "
            Bezeichnungen = Bezeichnungen & ActiveWorkbook.Names.Item(n).RefersToLocal & vbLf
        Next n
    End If
    'evtl. Bezeichnungen(Namen) werden in Mastersatz eingelesen
    Mastersatz = Left(Mastersatz, Len(Mastersatz) - 1) & Bezeichnungen
    Mastersatz = Mastersatz & vbLf & "Gruß" & vbLf & "Reinhard"
Mastersatz = Mastersatz & vbLf & vbLf & "Tabelle wurde eingefügt mit Code von <a href="https://www.herber.de/forum/technik/messages/1226.html"">https://www.herber.de/forum/technik/messages/1226.html"</a> & "(/pre)"
End With
'Mastersatz wird in Zwischenablage geschrieben
Set kurz = New DataObject
kurz.SetText Mastersatz
kurz.PutInClipboard
Set kurz = Nothing
End Sub

Bild

Betrifft: AW: OT @Reinhard
von: Hübi
Geschrieben am: 19.02.2005 17:27:28
Hi Reinhard,
danke, mit dem neuen Code klappt´s
Gruß Hüb
Bild

Betrifft: AW: Plan Ist Soll
von: BorussenFritz
Geschrieben am: 19.02.2005 18:51:48
Hallo Reinhard,
zunächst vielen, vielen Dank für DIE Lösung. Einfach Spitze. Bei den fehlenden
Einträgen in C2:D13 hatte ich mir schon gedacht, dass ich das evtl. missverstanden
habe, aber ich dachte mir halt, dass die Einträge in Spalte D keinen Sinn machen
würden, da sie ja abhängig vom individuellen Eintrag in Spalte C sind. Bitte nicht
Übel nehmen.
Nochmals vielen Dank für Deine tolle Hilfe und noch ein schönes Wochenende.
BorussenFritz :-)
Bild

Betrifft: AW: Plan Ist Soll
von: BorussenFritz
Geschrieben am: 19.02.2005 18:52:02
Hallo Reinhard,
zunächst vielen, vielen Dank für DIE Lösung. Einfach Spitze. Bei den fehlenden
Einträgen in C2:D13 hatte ich mir schon gedacht, dass ich das evtl. missverstanden
habe, aber ich dachte mir halt, dass die Einträge in Spalte D keinen Sinn machen
würden, da sie ja abhängig vom individuellen Eintrag in Spalte C sind. Bitte nicht
Übel nehmen.
Nochmals vielen Dank für Deine tolle Hilfe und noch ein schönes Wochenende.
BorussenFritz :-)
 Bild

Beiträge aus den Excel-Beispielen zum Thema "Monatsberechnung und Kummulation"