PDA

Vollständige Version anzeigen : formel fixieren? PROFIS GEFRAGT


Conger
11.12.2014, 08:18
Hallo Excel Experten,

wieder einmal ich und wieder eine Frage. Ich bin gerade dabei ein Projektplan zu erstellen. Möchte dazu die Start und Endzeiten des Projekts im Kalender farbig markieren. Kommen wir nun zur meiner Frage, ich habe in der Zelle e10 das Startdatum und in G10 das Enddatum.
Ich habe mit der Formel nur die Werktage abzüglich Feiertage berechnet:

=SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(F10&":"&G10));2)<6)*1)-SUMMENPRODUKT((feiern>=F10)*(feiern<=G10)*(WOCHENTAG(feiern;2)<6))

feiern = Feiertagetabelle

Die Projektzeit geht von 1-1-2014 - 31-1-2014 entsprechen 21 Werktage
Mein Problem ist, das die Feiertagsberechnung an das Jahr verbunden ist, so dass wenn ich das Jahr ändere das die Feiertage sich auch automatisch anpassen, aber in der Formel bräuchte ich nur die Feiertage im Jahr 2014, wie kann ich meine Spalte "Feiern" auf Jahr 2014 fixieren nur für die Formel, ohne funktionen verloren gehen müssen.

vielen lieben Dank wenn mir jemand behilflich sein kann.

Hajo_Zi
11.12.2014, 08:23
schreibe die Feiertage nur für 2014 nicht mit Formeln.

<a href="http://Hajo-Excel.de/index.htm" target="_blank" title="Hajo's Excelseiten">Gruß Hajo</a>

Conger
11.12.2014, 08:24
ne das möchte ich nicht, sonst muss ich für jedes jahr die Feiertage seperat schreiben. Möchte die Funktion beibehalten.

Klaus-Dieter
11.12.2014, 08:44
Hallo,

sieh mal ob dir das: Arbeitstage berechnen (http://excelwelt.de/UhrDatum/arbtgber.html) weiter hilft.

Conger
11.12.2014, 08:50
wie würde die formel aussehen wenn ich das mit verknüpfe; also wenn das Jahr 2014 dann formel anwenden; sonst nicht? hat jemand eine Idee?

Conger
11.12.2014, 09:18
Ich habe das jetzt so hinbekommen:

E2 = Jahr
F10 = Projektenddatum
G10 = Anfagsdatum
feiern = Feiertagstabelle


=WENN(IDENTISCH($E$2;JAHR(F10));SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(F10&":"&G10));2)<6)*1)-SUMMENPRODUKT((feiern>=F10)*(feiern<=G10)*(WOCHENTAG(feiern;2)<6));"")

Jetzt meine Frage ich möchte wenn ich das Jahr auf 2015 wechsel das das Ergebnis erhalten bleibt und nicht leer bleibt;also anstelle ;"") muss ich was schreiben?

Mc Santa
11.12.2014, 09:31
Hallo,

ich denke die praktikabelste Lösung ist es, wenn du in deine Spalte "feiern" die Feiertage von 2014-2024 einträgst (fest und ohne Formel) und dich danach nicht mehr darum kümmerst. Da du bereits Formeln hast, die dir die Feiertage in Abhängigkeit vom Jahr errechnen, sollte das schnell gemacht sein.

VG
Santa

Conger
11.12.2014, 09:34
ich möchte es aber nicht schnell sondern automatisiert haben. Brauche nur ein Sonst Wert es klappt wunderbar mit der Wenn Formel nur weiss ich nicht wie ich den sonst wert bennen soll.

Mc Santa
11.12.2014, 09:43
Hallo,

da du sagst, dass sich die Spalte "feiern" in Abhängigkeit vom Jahr ändert, weiß ich nicht, wie du 2015 noch die Feiertage von 2014 abrufen willst.

Für mich macht die Formel zur Berechnung der Feiertage daher nur bedingt Sinn: Es ist praktisch, wenn du immer die Feiertage des aktuellen Jahres berechnen willst. Aber wenn du dauerhaft wissen willst, wann Ostermontag in 2014 ist, dann lohnt sich eine Formel nicht. Denn der Feiertag für 2014 wird sich nie wieder ändern.

Deshalb würde ich eine Liste mit den notwendigen Feiertagen anlegen und auf eine Berechnung vollständig verzichten.

VG
Santa

Conger
11.12.2014, 10:13
das muss irgendwie gehen..:(

Mc Santa
11.12.2014, 10:18
Vielleicht findet man mit einer Beispieltabelle eine Lösung, die dir weiterhilft.

Conger
11.12.2014, 11:00
vllt kann mir jemand dir Formel in VBA schreiben, mit formeln gehts nicht.:(


=WENN(IDENTISCH($E$2;JAHR(F10));SUMMENPRODUKT((WOCHENTAG(ZEILE(INDIREKT(F10&":"&G10));2)<6)*1)-SUMMENPRODUKT((feiern>=F10)*(feiern<=G10)*(WOCHENTAG(feiern;2)<6));"")

Conger
11.12.2014, 11:05
ungefähr so soll es sein, kenne mich leider sehr gering mit VBA aus.:(


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 Then
If LCase(Target) = "x" Then
Application.EnableEvents = False
Target.Offset(-1, 0) = 9
Application.EnableEvents = True
End If
End If
End Sub

chris-kaiser
11.12.2014, 11:10
Hi,

Dein Code ergibt für mich zumindest keinen Sinn...

vllt kann mir jemand dir Formel in VBA schreiben, mit formeln gehts nicht

Wenn keine Daten (Feiertage) vorliegen geht auch kein VBA
außer im Code werden jetzt alle Feiertage berechnet, was aber nicht performant sein würde.

wie würde die formel aussehen wenn ich das mit verknüpfe; also wenn das Jahr 2014 dann formel anwenden; sonst nicht?

Das geht nicht....
Wo ist das Problem?
Lösung hast Du auch schon von Santa….

Wenn so eine Planung gemacht wird sollte abgeklärt sein:
Wie lange die einzelnen Projekte dauern können.
Wie lange dieselbe Datei dafür genutzt werden sollte.


Hinterlege dann ALLE Feiertage in dieser Zeitspanne! (dein Bereich feiern)
Würde nur ein bestimmtes Jahr über Formeln ermittelt werden, fehlen ja sonst alle Einträge im Vor/Folgejahr!
Wird eine Planung über das aktuelle Jahr hinaus gemacht, fehlen wiederum die Feiertage für dieses Jahr.....

Vorgang: lege für xx Jahre die Feiertage an. (geht ja schön per Formel)
Danach kannst du sogar die Formeln durch "Werte" ersetzen. (spart Rechenleistung).


Ich gehe auch nicht davon aus das ein Feedback von Dir kommt, so wie auch schon in den zwei Threads davor. Anscheinend findest Du das auch nicht für notwendig.
Ich werde Dich auch nicht weiter mit "lästigen" Tipps konfrontieren. :)

Conger
11.12.2014, 11:17
danke für deine lange aber nicht informative Antwort.Ist leicht zu sagen "geht nicht" aber ich bin mir sicher das ich eine Lösung finden werde.
Ich bastele gerade einen Projektool für das Unternehmen es wird jahrelang nach mir auch weiter genutzt, und versuche gerade alles zu automatisieren und idiotensicher zu machen. Deswegen bringt euer Vorschlag mich nicht weiter

Mc Santa
11.12.2014, 11:42
Hallo,

mein letzter Versuch:
Trage alle Feiertage für alle Jahre von 2000 bis 2200 ein. Das sollte doch ausreichen und einer Automatisierung gleich kommen.

Warum ich auf dieser Lösung beharre: Du schlägst vor, dass die Feiertage dynamisch für das aktuelle Jahr berechnet werden und für vergangene Jahre gespeichert bleiben. Du investierst sehr viel Zeit und Mühen dahin gehend, dass diese Berechnung unbedingt in der Zukunft erfolgt (zum Einsatzzeitpunkt deiner Tabelle).
Ich erkenne aber den Sinn darin nicht, den Zeitpunkt der Berechnung zu verschieben, wenn sich dadurch das Ergebnis nicht ändert. Du kannst problemlos die Feiertage für die nächsten 200 Jahre berechnen und als festen Wert speichern.

Viele Grüße
Santa

Klaus-Dieter
11.12.2014, 13:49
Hallo,

eventuell solltest du deine Anforderung etwas präzisieren.
Zwischen den beiden Aussagen deinerseits, sehe ich einen gewissen Widerspruch.
Ich bastele gerade einen Projektool für das Unternehmen es wird jahrelang nach mir auch weiter genutzt

aber in der Formel bräuchte ich nur die Feiertage im Jahr 2014

Nach meinem Verständnis brauchst du eine Liste aller Feiertage der Jahre, die dein Projektplan abdecken soll. Das wurde dir ja auch schon mehrfach geschrieben.