Excel: Formel in Zelle schützen, manuellen Eintrag ermöglichen?
Hallo liebe Community,
ich habe schon wieder eine Excelfrage, hoffentlich vorerst meine Letzte:
Kann man für eine Zelle eine Formel erstellen, diese Formel schützen und dennoch einen manuellen Eintrag ermöglichen?
Es geht um folgendes: Ich habe einen Stundenzettel, in welchem sich die Stunden automatisch zusammenrechnen mit 0,75 Stunden Pause.
Ein Arbeitstag von 8:00 bis 16:00 Uhr hat also 7,25 Stunden. Arbeisstart werden in C und D eingetragen. Um die Pausen auszurechnen existiert folgende Formel:
=WENN(ISTLEER(D18);"";((D18-C18) 24)-0,75)
Nun soll die Zelle aber auch per Hand eingetragen werden können, wenn z.B. Urlaub genommen wurde oder die Pausenzeit von den 0,75 Stunden abweicht oder KRankheit oder ... Daher kann ich die Formel nicht schützen.
Wenn jetzt jemand per Hand 8 Stunden einträgt, ist die Formel weg. Daher würde ich sie gerne schützen.
Geht dies und wenn ja wie?
LG Karsten
4 Antworten
Wenn du in dieser Zelle, wo du deine Formel stehen hast etwas manuell einträgst, ist die Formel weg und wird überschrieben.
Du kannst es aber m,it einer erweiterung und einer zusätzlichen Spalte erreichen.
Beispiel:
Wenn in XY = Urlaub >> dann soll er etwas machen.
Das ist trotzdem der richtige Ansatz. Ich würde in einer eigenen Spalte mit Dropdown-Auswahl (Datenüberprüfung) etwa diese Werte für Abwesenheiten erlauben:
Mit Dieser Formel kannst du ganz elegant definieren, was in den einzelnen Fällen gerechnet werden soll. In B2 wäre der Abwesenheitsgrund:
=WAHL(LINKS("0"&B2;2)+1;"Standardformel";"Formel für Urlaub";"Formel für Krankheit";"Formel für ZA";"Formel für Kur")
Also ich weiß inzwischen schon, dass es geht aber nur per VBA.
Bin ich richtig, dass es mit diesem Makro funktioniert:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Target = "" Then Target.Formula = "=VLOOKUP(B1,D1:E5,2,0)"
End If
End Sub
Wenn ja, wie muss ich das genau für meine Formel abändern?
LG Karsten
Hallo,
mit einer Zusatzspalte arbeiten, die Du zwischen Spalte E und F einfügst (Deine "Bemerkungen" verschieben sich auf Spalte G).
Vorteilhaft ist immer mit einem Bezug für Pausenzeit zu arbeiten, denn sollten sich die regulären Pausenzeiten ändern, muss nur die Bezugszelle geändert werden.
Ich arbeite immer mit Heximal (also Uhrzeit-Format). Hier das Beispiel für Dein Anliegen.
Folgende Formel in E4 (dann nach unten kopieren) erstellen ($-Zeichen zum Bezug der Standard-Pause nicht vergessen).
=WENN(UND(ODER(D4="";C4="");ISTTEXT(F4));"";WENN(UND(D4<>"";ISTZAHL(F4));D4-C4-F4;WENN(D4<>"";D4-C4-$E$2;"")))
Falls die zusätzliche Erscheinung für "Urlaub" in Spalte E erwünscht ist (wäre aber dann doppelt), dann siehe hervorgehoben ändern.
=WENN(UND(ODER(D4="";C4="");ISTTEXT(F4));F4;WENN(UND(D4<>"";ISTZAHL(F4));D4-C4-F4;WENN(D4<>"";D4-C4-$E$2;"")))
Bemerkung: Für die Summe der Gesamtstundenzeiten das benutzerdefinierte Format [h]:mm wählen.
Gruß Tron
Die Eintragung, ob Formel oder konkreten Wert, welche Du machst, die steht.
Was Du machen könntest wäre z.B. die Krankstunden im Bemerkungsfeld oder auch in einer anderen Spalte eintragen und mit der "Wenn-Funktion" diesen Wert abholen.
z.B.
=WENN(F17>0;F17;WENN(ISTLEER(D17);"";((D17-C17)))-LINKS(E$15;4))
Ns.
Deine Rechnung mit dem Abzug von 0,75 Std. Pause, ergibt bei mir kein sichtbares Ergebnis
Das ist echt schön und eine Lösung, wenn ich keine andere Antwort bekomme. Dankeschön :)
Ich weiß aber, dass es mit Makros geht und nicht allzu schwer ist. Ich weiß nur nicht, was ich genau ins Makro reinschreiben muss :/
Wenn Du bei der Formal keine Fehlermeldung haben willst, weil z.B. keine Werte für den jeweiligen Tag vorhanden sind, dann
=WENN(ISTFEHLER(WENN(F20>0;F20;WENN(ISTLEER(D20);"";((D20-C20)))-LINKS(E$15;4)));"";WENN(F20>0;F20;WENN(ISTLEER(D20);"";((D20-C20)))-LINKS(E$15;4)))
Hi Deep,
danke für deine Antwort. Mit dem Urlaub funktioniert das noch gut, aber nicht mit Krankheit. Laut Gesetz wird Krankheit nicht mit der durchschnittlichen Stundenzahl (z.B. 6 Stunden bei 30 Wochenstunden), sondern mit der tatsächlich geplanten Stundenzahl angegeben. Da wir einen Bereich haben, an welchem die 30 Stunden in 4 Tagen erarbeitet werden, fallen die Stunden im Krankheitsfall z.B. höher aus als die 6 Stunden. Deshalb ist es mit der Formel oben nicht getan ;)