Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Rechnen mit Zahlen verschiedener Tabellenblätter

Rechnen mit Zahlen verschiedener Tabellenblätter
Michael
Hallo Leute,
ich habe ein Excel-Sheet mit 120 Tabellenblättern (2 pro Monat über 5 Jahre hinweg):
Die Tabellenblätter heißen:
"Jan1 2011"
"Jan2 2011"
"Feb1 2011"
"Feb2 2011"
...
"Dez1 2015"
"Dez2 2015"
Nun zur Frage:
In Tabellenblätt "Apr1 2011" würde ich gerne einen Durchschnitt bilden aus den jeweiligen Zahlen in der Zelle A1 aus den Tabellenblättern "Jan1 2011", "Feb1 2011" und "Mrz1 2011". In Tabellenblätt Mai1 2011 würde ich gerne einen Durchschnitt bilden aus den jeweiligen Zahlen in der Zelle A1 aus den Tabellenblättern "Feb1 2011", "Mrz1 2011" und "Apr1 2011". In einem bestimmten Monat soll also stets der rollierender Durchschnitt aus einer Zahl aus den 3 vorangegangen Monaten erstellt werden.
Wer kann mir helfen wie ich das schnellstmöglich einrichte.
Grüße und danke für Eure Unterstützung schon vorab.
Stephan
AW: Rechnen mit Zahlen verschiedener Tabellenblätter
07.12.2010 11:48:56
Renee
Hi Stephan,
Mit einer UDF (Code in ein Modul der Mappe) könnte es so funktionieren:
Public Function getLast3Month(Zelle As Range) As Double
' summiert die Zellen mit der Adresse von Zelle
' der 3 Vormonate aufgrund des Blattnamens der Form 'MMMn YYYY'
Application.Volatile
Dim iYear As Integer
Dim iMonth As Integer, iM As Integer
Dim sTyp As String
Dim dDate As Double
iYear = CInt(Right(Application.Caller.Worksheet.Name, 4))
iMonth = Month(DateValue("1." & Left(Application.Caller.Worksheet.Name, 3) & "." & iYear))
sTyp = Mid(Application.Caller.Worksheet.Name, 4, 1)
For iM = 1 To 3
dDate = DateSerial(iYear, iMonth - iM, 1)
getLast3Month = getLast3Month + ThisWorkbook.Worksheets( _
Format(dDate, "MMM") & sTyp & _
Format(dDate, " YYYY")).Range(Zelle.Address)
Next iM
End Function

Die Formel =getLast3Month(A1) ergibt die Summe der Zellen A1 der 3 Vormonate (sofern vorhanden ;-)
GreetZ Renée
Anzeige
AW: Rechnen mit Zahlen verschiedener Tabellenblätter
07.12.2010 21:11:20
Michael
Hallo Rene,
vielen lieben Dank zunächst einmal für Deine Hilfe.
Ich habe mir Deinen Vorschlag eine Weile angesehen. Leider reichen meine Programmierkenntnisse nicht aus zu verstehen, was nun noch von mir angepasst/verändert werden muss. Könntest du mir das kurz noch erklären?
Nochmals vielen Dank und Grüße
Stephan
Wieso verändern? Anwenden bzw.
07.12.2010 22:30:06
Renee
ausprobieren, Michael
Also Code in Modul und die Formel in eine Zelle!
GreetZ Renée
AW: Wieso verändern? Anwenden bzw.
07.12.2010 23:56:00
Michael
Hallo Renee,
ich dachte ich muss noch etwas an Deinem Code anpassen, daher nochmals meine Rückfrage. Wenn ich den Code in ein neues Modul kopiere und die Formel in eine Zelle eintrage, erhalte ich leider in der betreffenden Zelle den Fehler "#Wert".
Weißt du woran das liegen könnte?
Anzeige
Fehler in der Anforderung ?
08.12.2010 09:20:10
Renee
Hallo Stephan,
Was für eine Formel hast du eingetragen ?
Der Fehler #WERT erscheint unter diesen Umständen:
1. Der Blattname auf dem die Formel eingetragen ist, entspricht nicht der Struktur 'MMMn YYYY'
2. Es gibt keine 3 Vormonat für das Blatt mit den Nammen 'MMM(-1..3)n YYYY'
3. In den angegebenen Zelleadressen der Formel findet die Funktion in den 3 Vormonatsblättern keine numerischen Werte, bzw. mind. 1 Wert ist nicht numerisch.
Fall du nicht weiterkommst, bitte beantworte folgende Fragen:
1. Ist die Struktur der Tabellennamen mit Sicherheit 'MMMn YYYY' ? wobei MMM die dt. Abkz. des Monatsnamens, n ein Zahl oder eine Buchstabe, der auch in den 'Vormonaten' wieder auftritt und YYYY einer Jahreszahl >1899 und 2. Befinden sich die 3 Zellen der Vormonate immer an der gleichen Stelle, oder sogar fix auf A1 ?
3. Gibt es noch andere Blattnamen in der Arbeitsmappe, ausser 'MMMn YYYY' Blättern?
4. Was soll die Prozedur für die ersten 3 Monaten (welche keine 3 Vormonate haben), zurückgeben ?
5. Kannst du eine anonymisierte Arbeitsmappe mit mind. 8 Blättern als Beispiel hochladen ?
GreetZ Renée
Anzeige
AW: Fehler in der Anforderung ?
08.12.2010 13:18:25
Michael
Hallo Renee,
danke für Deine ausführliche Hilfe.
ich konnte das Problem lösen. Meine Tabellenblätter waren nicht korrekt benannt. Ich kann in deinem Code gar nicht erkennen, dass die Prozedur Tabellenblätter der Benennung "MMM2 YYYY" ignoriert (also n=2). Beim Testen habe ich aber schon festgestellt, dass eine Arbeitsmappe namens "Mrz2 2010" in berücksichtigt wird. An welcher Stelle wird das bestimmt?
Ein letztes noch: Ich wollte eigentlich nicht die Summe, sondern den Mittelwert der 3 Werte. Soll hierzu etwas am Code geändert werden oder soll ich einfach so lösen: "=getLast3Month(A1)/3"
Grüße und vielen Dank für Deine Mühe
Michael
Anzeige
AW: Fehler in der Anforderung ?
08.12.2010 13:20:39
Michael
Sorry der 5. Satz muss natürlich heißen:
Beim Testen habe ich aber schon festgestellt, dass ein Tabellenblatt namens "Mrz2 2010" NICHT berücksichtigt wird (was ja auch so gewollt ist). Rein interessehalber: An welcher Stelle des Codes wird das bestimmt?
n-Index nach dem MMM
08.12.2010 13:34:31
Renee
Hallo Michael,
die Variable sTyp bestimmt, was für eine Zahl, oder Buchstabe nach dem 3stelligen Monatskürzel folgt und sucht dann Blattnamen mit dem gleichen 'Typ'.
Um den Mittelwert zu errechnen, kannst du das mit der Formel/3 machen oder du baust es in die Function ein:
...
Next iM
getLast3Month=getLast3Month/3
End Function
GreetZ Renée
Anzeige
AW: n-Index nach dem MMM
08.12.2010 14:51:40
Michael
Nochmals vielen Dank, Renee.
wie müsste der Code geändert werden, wenn die relevanten Tabellenblätter, auf die die Prozedur zugreifen soll anstatt "MMMn YYYY" folgendermaßen aufgebaut sind:
"Output MMM YY"
Bsp.:
- "Output Jan 10"
- "Output Feb 10"
Ich kriege den Transfer leider selbst nicht hin. Das wäre dann auch die letzte Rückfrage - Versprochen!
Grüße
Michael
anderer Blattname anderer Code
08.12.2010 16:42:56
Renee
Hi Michael,
Das wäre dann auch die letzte Rückfrage - Versprochen! Meinst du wirklich ;-))
Public Function getLast3Month(Zelle As Range) As Double
' summiert die Zellen mit der Adresse von Zelle
' der 3 Vormonate aufgrund des Blattnamens der Form 'Output MMM JJ'
Application.Volatile
Dim iYear As Integer
Dim iMonth As Integer, iM As Integer
Dim dDate As Double
iYear = CInt(Right(Application.Caller.Worksheet.Name, 2))
If iYear 

GreetZ Renée
Anzeige
AW: anderer Blattname anderer Code
08.12.2010 23:30:57
Michael

Meinst du wirklich ;-))
Fast... :-)
Benötige das gleiche noch 12monatig, rollierend. Wäre das so korrekt oder hab ich etwas übersehen:
Public Function getLast12Month(Zelle As Range) As Double
' summiert die Zellen mit der Adresse von Zelle
' der 12 Vormonate aufgrund des Blattnamens der Form 'Output MMM JJ'
Application.Volatile
Dim iYear As Integer
Dim iMonth As Integer, iM As Integer
Dim dDate As Double
iYear = CInt(Right(Application.Caller.Worksheet.Name, 2))
If iYear iYear = iYear + 2000
Else
iYear = iYear + 1900
End If
iMonth = Month(DateValue("1." & _
Mid(Application.Caller.Worksheet.Name, 8, 3) & _
"." & iYear))
For iM = 1 To 12
dDate = DateSerial(iYear, iMonth - iM, 1)
getLast12Month = getLast12Month + ThisWorkbook.Worksheets("Output " & _
Format(dDate, "MMM") & _
Format(dDate, " YY")).Range(Zelle.Address)
Next iM
getLast12Month = getLast12Month / 12
End Function
Scheint in ersten Tests so zu funktionieren...
Grüße
Michael
Anzeige
werd mal etwas generischer...
09.12.2010 11:45:28
Renee
Michael,
Es macht doch keinen Sinn, für jede Anforderung wieder eine neue Function zu schreiben.
Diese hier verpackt alles in eine (mit 2 zusätzlichen Parameter, Beispiel im Kommentar):
Public Function getLastFigure(Zelle As Range, _
Monate As Integer, _
Optional Typ As Integer = 1) As Double
' Für Blattnamen der Form 'Output MMM JJ'
' Parameter 1 = Zellebezug für die 'Vormonats-Blätter'
' Parameter 2 = Anzahl Monate vor dem des Blattnamens
' Parameter 3 = 0 z.B. Summe der 3 Vormonate =getLastFigure(A1;3;0)
'               1 z.B. Mittelwert der 12 Vormonate =getLastFigure(A1;12)
'               -1 z.B. Monat des Vorjahres =getLastFigure(A1;12;-1)
Application.Volatile
Dim iYear As Integer
Dim iMonth As Integer, iM As Integer
Dim dDate As Double
iYear = CInt(Right(Application.Caller.Worksheet.Name, 2))
If iYear 

GreetZ Renée
Anzeige
AW: werd mal etwas generischer...
09.12.2010 23:38:54
Michael
Oh toll, vielen Dank!
Werde es morgen direkt ausprobieren!
AW: werd mal etwas generischer...
12.12.2010 00:27:31
Michael
Phänomenale Arbeit, Renee!
Nochmals abschließend vielen Dank!
AW: anderer Blattname anderer Code
08.12.2010 23:42:26
Michael
Achja und falls du noch Lust hast, könntest du mir dabei helfen wie ich den Wert aus Zelle A1 (keine Summen, keine Durchschnitte, sondern nur den Wert) aus dem gleichen Monat das Vorjahrs dargestellt bekomme.
Sorry will nicht unverschämt sein. Nur wenn du Lust/Zeit hast. Ansonsten einfach ignorieren bitte.
Danke und Grüße
Michael
Scheint doch nicht sooo dringend ;-) (owT)
07.12.2010 15:21:10
Renee

AW: Scheint doch nicht sooo dringend ;-) (owT)
07.12.2010 21:14:25
Michael
Hey Renee,
mit schnellstmöglich war nicht gemeint, dass die Antwort schnellstmöglich erfolgen muss, sondern eher, dass ich mir am meisten Zeit sparen kann und so schnellstmöglich mit der Aufgabe fertig bin :-). Denn rein theoretisch könnte ich ja auch ganz stupide an das Problem herangehen und 120 Sheets entsprechend miteinander verknüpfen... :)
Anzeige

318 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige