Nochmal Feiertage einfärben
Anja Bergmann
mein erster Versuch hier hat leider nicht zu einer Lösung
geführt, daher möchte ich es noch einmal versuchen.
Ich habe eine Zeiterfassungstabelle ("Arbeitszeit") und
möchte die Feiertage einfärben. Im oberen Bereich befinden
sich zwei Auswahlfelder (Daten/Gültigkeit) zur Auswahl der
Monate (01 - 12) und der Bundesländer.
Auf einem zweiten Blatt ("Feiertage") habe ich eine
Tabelle mit den Feiertagsdatümern in der Vorspalte und den
Bundesländern im Kopf. Immer wenn zu dem Datum in dem
Bundesland ein Feiertag ist habe ich ein "x" an die stelle
gesetzt.
Mittels SVERWEIS habe ich es bisher leider nur geschafft
ein Bundesland zu berücksichtigen. Wie schaffe ich es, in
Abhängigkeit des ausgewählten Bundeslandes auf die
entsprechende Spalte in der Tabelle "Feiertage" zu
verweisen?
Vielen Dank
Anja
Andreas Stoye
> Anja Bergmann schrieb:
Ich versuche es mal.
Die Einfärbung geht mit Format - bedingte Formatierung - Formel ist
=INDEX($G$10:$H$12;VERGLEICH(DATWERT(TAG(A12)&"."&MONAT($B$10)&".2005");$F$1
0:$F$12;);VERGLEICH($C$10;$G$9:$H$9;))="x"
Die Bezüge musst du anpassen.
G10:H12 ist die Tabelle der Feiertage, davon der Bereich, wo die "x"
eingetragen sind.
A12 ist der Tag, für den die Abfrage gilt, der Tag ist eine Zahl (z.B. 1, 2,
3...)
B10 ist der Monat wie eingestellt, auch eine Zahl.
.2005 wird addiert wegen dem aktuellen Jahr, besser wäre aus einer Zelle die
Zahl 2005 zu übernehmen. Formel ändern: ...&"."&Bezug.
F10:F12 ist die Spalte der Datümer in der Tabelle Feiertage
C10 ist das eingestellte Bundesland.
G9:H9 ist die Zeile Bundesländer in der Tabelle Feiertage.
mfg Andreas
Melanie Breden
Anja Bergmann schrieb:
> Ich habe eine Zeiterfassungstabelle ("Arbeitszeit") und
> möchte die Feiertage einfärben. Im oberen Bereich befinden
> sich zwei Auswahlfelder (Daten/Gültigkeit) zur Auswahl der
> Monate (01 - 12) und der Bundesländer.
>
> Auf einem zweiten Blatt ("Feiertage") habe ich eine
> Tabelle mit den Feiertagsdatümern in der Vorspalte und den
> Bundesländern im Kopf. Immer wenn zu dem Datum in dem
> Bundesland ein Feiertag ist habe ich ein "x" an die stelle
> gesetzt.
>
> Mittels SVERWEIS habe ich es bisher leider nur geschafft
> ein Bundesland zu berücksichtigen. Wie schaffe ich es, in
> Abhängigkeit des ausgewählten Bundeslandes auf die
> entsprechende Spalte in der Tabelle "Feiertage" zu
> verweisen?
ok, wollen wir es mal versuchen :-)
Angenommen, deine Feiertagsliste steht im Bereich A1:Q20 in Blatt2.
Spalte A enthält die Datümer, die anderen Spalten die Bundesländer.
Markiere diesen Bereich 'A2:Q20' und vergebe im Namensfeld den Namen
'FTage'. Markiere den Bereich der Bundesländer-Überschriften, z.B.
B1:Q1 und vergebe den Namen 'BLänder'.
Angenommen, das Auswahlfeld für die Bundesländer befindet sich im
ersten Blatt in B1. Dann weise der Gültigkeitsliste dort den Bezug: '=BLänder' zu.
Um ein Datum in A10 zu überprüfen verwende nun folgende Formel:
=SVERWEIS(A10;FTage;VERGLEICH(B1;BLänder;0)+1;0)
Die Fehleranzeige #NV könnte man noch mit folgender Formel abfangen:
=WENN(ISTFEHLER(VERGLEICH(A10;Feiertage!A2:A20;0));"";
SVERWEIS(A10;FTage;VERGLEICH(B1;BLänder;0)+1;0))
--
Mit freundlichen Grüssen
Melanie Breden
- Microsoft MVP für Excel -
http://excel.codebooks.de (Das Excel-VBA Codebook)
#Excel-Auftragsprogrammierung#
Lars P. Wolschner
Ich weiß nicht, wie Dein erster Versuch aussah, aber dieser Ansatz
erscheint mir doch recht großräumig - die Konfiguration eines
Kalendariums erstreckt sich über zwei Arbeitsblätter.
Ich nutze eine Feiertagstabelle mit allen überhaupt in Betracht
kommenden Feiertagen nebst Freiraum für betriebsintern-besondere
Tage. In der ersten Spalte steht die Bezeichnung, in der folgenden
das Datum, entweder durch einen Ausdruck wie etwa =DATUM(Jahr;5;1)
für den 1. Mai oder vom Datum des Ostersonntag ausgehend durch
Addition oder Subtraktion berechnet. Rechts davon folgen dann die
Spalten mit den jeweils benötigten Eigenschaften der Tage. Darüber
befindet sich noch eine Tabelle mit einer Zeile für jeden Wochen-
tag, bei der wegen entfallender Datumsangabe die zweite Spalte leer
bleibt.
Da beide Tabellen übereinander stehen, werden die Eigenschaften
eines Tages durch einen einzigen Zeilenindex zugänglich.
Zur Auswertung wird das Datum des jeweiligen Arbeitstages in der
Datumsspalte der Feiertagstabelle gesucht (VERGLEICH()). Wenn es
dort nicht aufgeführt ist (VERGLEICH() liefert #NV), wird der dem
Wochentag des betroffenen Datums zugeordnete Wert aus der Wochen-
tagstabelle maßgeblich. Die Einträge der Feiertagstabelle haben so
Vorrang vor denjenigen der Wochentagstabelle.
Diese Reihenfolge kann für einzelne oder auch alle Eigenschaften
eines Tages durch Eintragung des Sterns * umgekehrt werden, so daß
der Wert dann doch noch der obersten wochentagsbezogenen Tabelle
entnommen wird. Das empfiehlt sich beispielsweise für solche Tage,
die lediglich hinsichtlich einzelner Eigenschaften vom Üblichen
abweichend behandelt werden sollen. Mit einem Stern bei jeder
Eigenschaft ist der Feiertag "ausgeschaltet" und wird in jeder
Hinsicht wie der entsprechende Wochentag behandelt.
Das ließe sich durchaus zu einer Bundesland-orientierten Tabelle
ausbauen. An der Berechnung des Zeilenindices änderte sich nichts,
die Bundesländer wären horizontal aufzutragen und ergäben einen
zusätzlichen Spaltenindex, den man zum Zugriff auf die Eigen-
schaften eines Tages braucht. Alle Eigenschaften eines Tages wären
für jedes Bundesland separat eingetragen.
Nun gibt es aber auch Regelungen, die nicht bundesland-, sondern
"landstrichweise" gelten, weil "überwiegend" katholische oder
evangelische Bevölkerung den Ausschlag gibt. Daher könnte selbst
eine schon recht große bundeslandweise aufgebaute Tabelle die
Realität noch nicht hinreichend abbilden.
Wenn Du es möchtest, sende ich Dir eine Anwendung mit diesem
Kalendarium zu; auf Wunsch auch auf dieses reduziert. Du müßtest
mir dann allerdings eine geeignete Mailadresse angeben.
CU
--
Lars P. Wolschner lars.wo...@nexgo.de
Bernardstraße 11b lars.wo...@gmx.de
D-63067 Offenbach am Main
Fon & Fax: +49 69 80068670 Mobil: +49 163 8122462 (eplus)