Formel

[Gabriel08]

Hallo Liebe Leute

Ich habe eine Excel angefangen um Arbeitsstunden zu erfasen. Jetzt stehe ich vor eine kleines Problem. Ich möcht im in der Zelle I13 noch in der Formel hinzufügen wenn in der Zelle C15 ein Ferientag wie Weihnachte in der ZelleI13 plus eine Stunde hinzugefügt wird wenn C13 Anwesend oder Home Office steht ansonsten nichts berechnet wird oder angezeigt wird.

In der Zelle I13 habe ich folgende Formel schon eingetragen :

=WENN($C13="";"";WENN($C13="Neujahrstag";"";WENN($C13="Berchtoldstag";"";WENN($C13="Karfreitag";"";WENN($C13="Ostermontag";"";WENN($C13="Auffahrt";"";WENN($C13="Pfingstmontag";"";WENN($C13="Nationalfeier";"";WENN($C13="Weihnachten";"";WENN($C13="Stephanstag";"";WENN($C13="Anwesend";$H13-$G13+$E13-$D13;WENN($C13="Home Office";$H13-$G13+$E13-$D13;WENN($C13="Externe Weiterbildung";$H13-$G13+$E13-$D13;WENN($C13="Interne Weiterbildung";$H13-$G13+$E13-$D13;WENN($C13="Anwesend Feiertage";$H13-$G13+$E13-$D13;"")))))))))))))))

Gruss

 

[Nebuk]

Hallo Gabriel08

Vorweg ich verstehe nicht ganz was du genau wo haben möchtest, aber ich kann dir evtl. einen etwas einfacheren und übersichtlicheren Ansatz zeigen. Evtl. hilft dir dies oder kannst es bei dir sonst irgendwie verwenden. :)

Denn du kannst das ganze etwas vereinfachen bevor es da in eine "Wenn-Dann-Sonst" Orgie ausartet:
Such dir einen Bereich in der Tabelle in der nichts steht und du alle Feiertage auflisten möchtest die für deine Berechnung berücksichtigt werden sollen und schreib sie dort hinein. Ebenfalls mit den Bezeichnungen der "Anwesenheit" welche für die Sonderregel wichtig sind.

Ich hab beispielsweise die Feiertage in eine zweite Tabelle verschoben und ab Zeile A1 aufgelistet. Die Anwesenheiten habe ich ebenfalls in "Tabelle2" verfrachtet und sie ab B1 aufgeführt.



Jetzt kannst du einfach die Spalte A1-A... (bei mir A1-A10) auswählen und in das Menü "Einfügen" wechseln. Dort drückst du auf "Tabelle". Im neuen Dialogfenster drückst du einfach auf OK.



Die Bezeichnung "Spalte1" (Tabellenüberschrift) kannst du jetzt durch beispielsweise "Feiertage" ersetzen. Sofern eine Zelle des eben geschaffenen Bereichs aktiv ist, erhälst du unter "Tabellentools" das Menü "Entwurf", dort drückst du dann drauf und setzt unter "Tabellenname" einen treffenden Begriff (ich hab "Feiertage" verwendet).



Gleiches kannst du jetzt mit der Spalte B machen. Ich hab dafür die Begriffe "Anwesenheiten" anstatt "Feiertage" verwendet ansonsten bin ich gleich verfahren.



Die Feiertage und die Anwesenheiten kannst du jetzt dynamisch erweitern in dem du einfach unter dem letzten Element des Bereichs einen weiteren Eintrag ergänzt. Zum Testen habe ich einfach unter Anwesenheiten noch "Weiterbildung" ergänzt.



Nun darfst du wieder zum ursprünglichen Bereich bzw. dem ersten Tabellenblatt wechseln wo du deine Arbeitsstunden erfasst.



Für die Berechnung ob +1 berechtigt oder nicht kannst du folgenden Ansatz verwenden:

=WENN(UND(ISTTEXT(SVERWEIS(C15;Feiertage;1;FALSCH));ISTTEXT(SVERWEIS(C13;Anwesenheiten;1;FALSCH)));1;0)

Damit ist die Abfrage deutlich übersichtlicher bzw. lesbarer als in deinem Beispiel in dem du alleine schon 14 Wenn-Dann-Sonst-Abfragen verwendet hast.

Erklärung noch zur Formel:
Durch die Tabelle die du weiter oben erstellt hast, kannst du diesen Bereich einfach mit dem gesetzten Namen abfragen. Du brauchst keine Bereiche auszuwählen und zu suchen wo diese stehen. Es reicht, wenn du den Tabellenname anfängst zu tippen und Excel schlägt dir die Tabelle automatisch vor. Wichtig ist einfach, dass du den Tabellenname korrekt setzt und nach diesem suchst. Der Eintrag in A1 bzw. B1 ist irrelevant, da dieser einfach eine Spaltenüberschrift ist und nicht der Tabellenname.



Die Formel macht einen SVERWEIS auf die definierten Tabellen und verknüpft sie mit einem UND. Das heisst es müssen beide WAHR sein. Wenn ein Eintrag gefunden wurde wird mit ISTTEXT abgefragt ob ein Wert gefunden wurde und gibt ggf. WAHR zurück. Die Umschliessende Wenn-Dann-Sonst-Abfrage ist dafür da um zu ermitteln ob es ein +1 braucht oder eben nicht.

Gruss
Nebuk

 

[Gabriel08]

Dankeschön

Vielen dank für Deine Bemühungen. Ich habe mich ein bisschen falsch ausgedrück.

Bei I13 habe ich folgende Formel momentan :

=WENN($C13="";"";WENN($C13="Neujahrstag";"";WENN($C13="Berchtoldstag";"";WENN($C13="Karfreitag";"";WENN($C13="Ostermontag";"";WENN($C13="Auffahrt";"";WENN($C13="Pfingstmontag";"";WENN($C13="Nationalfeier";"";WENN($C13="Weihnachten";"";WENN($C13="Stephanstag";"";WENN($C13="Anwesend";$H13-$G13+$E13-$D13;WENN($C13="Home Office";$H13-$G13+$E13-$D13;WENN($C13="Externe Weiterbildung";$H13-$G13+$E13-$D13;WENN($C13="Interne Weiterbildung";$H13-$G13+$E13-$D13;WENN($C13="Anwesend Feiertage";$H13-$G13+$E13-$D13;"")))))))))))))))

Wenn ich die Formel erweitere auf :

=WENN($C13="";"";WENN($C13="Neujahrstag";"";WENN($C13="Berchtoldstag";"";WENN($C13="Karfreitag";"";WENN($C13="Ostermontag";"";WENN($C13="Auffahrt";"";WENN($C13="Pfingstmontag";"";WENN($C13="Nationalfeier";"";WENN($C13="Weihnachten";"";WENN($C13="Stephanstag";"";WENN($C13="Anwesend";$H13-$G13+$E13-$D13;WENN($C13="Home Office";$H13-$G13+$E13-$D13;WENN($C13="Externe Weiterbildung";$H13-$G13+$E13-$D13;WENN($C13="Interne Weiterbildung";$H13-$G13+$E13-$D13;WENN($C13="Anwesend Feiertage";$H13-$G13+$E13-$D13;"")))))))))))))))+WENN($C15="Neujahrstag";"1:00";WENN($C15="Berchtoldstag";"1:00";WENN($C15="Karfreitag";"1:00";WENN($C15="Ostermontag";"1:00";WENN($C15="Auffahrt";"1:00";WENN($C15="Pfingstmontag";"1:00";WENN($C15="Nationalfeier";"1:00";WENN($C15="Weihnachten";"1:00";WENN($C15="Stephanstag";"1:00";"")))))))))

Kommt im I13 ein Text"WERT" obwohl im C15 kein text vorhanden ist, dollte eigentlich das Resultat H13-G13+E13-D13 sein en 9 Stunden.

 

[Nebuk]

So möchtest du das haben?

=SUMME(WENN(UND(ISTTEXT(SVERWEIS(C15;Feiertage;1;FALSCH));ISTTEXT(SVERWEIS(C13;Anwesenheiten;1;FALSCH)));"01:00";0);(E13-D13);(H13-G13))

Funktioniert bei mir einwandfrei :)

 

[Gabriel08]

Danke

Das wäre die Lösung, die Formel geht nicht bei mir, aber Versuches anzupassen. Danke