NETTOARBEITSTAGE() pro Bundesland

[JohnCamus]

Guten Tag, ich verfolge in einer Exceltabelle mehrere Mitarbeiter in diversen Bundesländer. Ich möchte die Nettoarbeitstage berechnen aber die Feiertage berücksichtigen. Das Problem ist nun leider, dass sich die Feiertage von Bundesland zu Bundesland unterscheiden. Ich habe bereits eine Mappe erstellt in der die Feiertage für jedes Bundesland berechnet werden. Jede Spalte hat ein Bundesland in denen jeder Feiertag von 2000 bis 2064 berechnet wird.

jetzt stehe ich allerdings auf dem Schlauch. Wie kann ich Excel bei der NETTOARBEITSTAGE() Formel sagen, dass der Eintrag unter [Feiertage] sich auf Spalte A B C etc. bezieht, je nachdem welcher Ort neben dem Mitarbeiter angegeben ist?

[echo]

Hallo,
markiere in Feiertage die Spalte D (ohne Überschrift) und trage doch mal links oben in das Namenfeld München ein,
bestätige mit RETURN

dito mit Spalte E und Oldenburg

dann erweiterst du deine Formel durch indirekt(a2)
=NETTOARBEITSTAGE(C2;D2;INDIREKT(A2))
Bedingung ist, das der Ort in Spalte A identisch mit den  soeben vergebenen Namen ist.

Alternative ohne Namen:
=NETTOARBEITSTAGE(C2;D2;BEREICH.VERSCHIEBEN(Tabelle2!$D$2:$D$1100;;VERGLEICH(A2;Tabelle2!$D$1:$E$1;0)-1))
Vergleich sucht in der Überschrift der Feiertage nach dem Namen aus Spalte A
BEREICH.VERSCHIEBEN() stellt die gefundene Spalte ein

Bedingung ist hier, das der Ort in Spalte A identisch mit der Überschrift ist.

Gruß Holger

[JohnCamus]

Ah! Vielen Dank, der Ansatz ist großartig! Komsicherweise funktioniert er bei mir nur, wenn ich mich auf eine benannte Spalte im Arbeitsblatt "Mitarbeiter" beziehe, aber nicht wenn ich mich auf eine Spalte im Arbeitsblatt "Tabelle2" beziehe. Woran kann das liegen?

[echo]

Hallo
schau mal Im Menü Formeln in den Namen-Manager (Ctrl.+F3)
du hasst die komplette Spalte und somit die Überschrift im Namen intrigiert, Netto-Arbeitstage kann mit dem Text der Überschrift aber nicht anfangen.
Ändere den Bezug im Namen-Manager ab Zeile 2 zB. =Tabelle2!$E$2:$E$2000

Gruß Holger

[JohnCamus]

Ah, klar, natürlich! Danke, jetzt läuft es. Und schon hat sich meine studentische Tipparbeit um 30% reduziert. Danke!

[Rabe]

Hi,

als zusätzlicher Tipp!

Ich habe bereits eine Mappe erstellt in der die Feiertage für jedes Bundesland berechnet werden. Jede Spalte hat ein Bundesland in denen jeder Feiertag von 2000 bis 2064 berechnet wird.

bitte beachten:
der 31.10. ist 2017 ein bundesweiter Feiertag! Nur ein einziges Mal, in den Folgejahren nicht mehr.

[Jockel]

Hallo, meinst du so..?

Arbeitsblatt mit dem Namen 'Mitarbeiter'
	A	B	C	D	E
1	Ort	Name	Angekommen	Weggegangen	Geblieben
2	Oldenburg	Hans Peter	01.03.2017	10.04.2017	29
3	München	Rudolf Rugowski	05.03.2017	05.05.2017	42

Name	Bezug
München	=Tabelle2!$D:$D
Oldenburg	=Tabelle2!$E:$E

Zelle	Formel
E2	=NETTOARBEITSTAGE(C2;D2;INDEX(Tabelle2!$D$2:$E$1041;;VERGLEICH(A2;Tabelle2!$D$1:$E$1;0)))
E3	=NETTOARBEITSTAGE(C3;D3;INDEX(Tabelle2!$D$2:$E$1041;;VERGLEICH(A3;Tabelle2!$D$1:$E$1;0)))

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

[JohnCamus]

Hallo, meinst du so..?

Arbeitsblatt mit dem Namen 'Mitarbeiter'
	A	B	C	D	E
1	Ort	Name	Angekommen	Weggegangen	Geblieben
2	Oldenburg	Hans Peter	01.03.2017	10.04.2017	29
3	München	Rudolf Rugowski	05.03.2017	05.05.2017	42

Name	Bezug
München	=Tabelle2!$D:$D
Oldenburg	=Tabelle2!$E:$E

Zelle	Formel
E2	=NETTOARBEITSTAGE(C2;D2;INDEX(Tabelle2!$D$2:$E$1041;;VERGLEICH(A2;Tabelle2!$D$1:$E$1;0)))
E3	=NETTOARBEITSTAGE(C3;D3;INDEX(Tabelle2!$D$2:$E$1041;;VERGLEICH(A3;Tabelle2!$D$1:$E$1;0)))

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

Ah! Klar. Ich arbeite so viel mit Index() match() zeilenweise, dass ich komplett vergessen habe, dass man natürlich auch spaltenweise damit arbeiten kann. Ich denke ich werde die Lösung übernehmen da sie für andere Mitarbeiter nachvollziehbarer sein dürfte. Vielen Dank euch!