Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1648to1652
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Sverweis

Sverweis
10.10.2018 08:11:11
Hajo_Zi
Hallo,
es geht um Version 2016.
Ich habe zwei Tabellen.
Ersten Tabelle mit Datum (daten)
Zweite Tabelle mit Feiertage.
In erster Tabelle Bedingte Formatierung, die mit Sverweis auf die zweite zugreift.
Warum wir 01.01.2019 nicht als Feiertag erkannt?

Tagesprogramm-EP
 GHIJKLMNOPQRS
2123456 123456
323.1224.1225.1226.1227.1228.12 31.1201.0102.0103.0104.0107.01
die bedingte Formatierung Fülleffekte, Symbole, Datenbalken
werden in dieser Tabelle nicht dargestellt
Schriftart wird in dieser Tabelle nicht dargestellt

verwendete Formeln
Zelle Formel Bereich N/A
H2:L2,O2:S2=G2+1  
N3=WENN(WOCHENTAG(L3;2)=5;L3+3;WENN(WOCHENTAG(L3;2)=6;L3+2;WENN(WOCHENTAG(L3;2)=6;L3+1;L3+1)))  
H3:L3,O3:S3=WENN(WOCHENTAG(G3;2)=5;G3+3;WENN(WOCHENTAG(G3;2)=6;G3+2;WENN(WOCHENTAG(G3;2)=6;G3+1;G3+1)))  

definierte Namen  
Name Bezieht sich auf Tabelle Z1S1-Formel
Feiertage=Frei!$B$2:$C30 =Frei!R2C2:R[29]C3

Bedingte Formatierung Haupttabelle 1  
Wird angewendet auf   Nr. Bed.  Regeltyp  Operator   Formel1   Formel2   Format Schrift Füll-farbe   Unterstrichen   Schrift- farbe Muster   Muster-farbe Typ Bereich
$G$302.Bed.: Formel ist =WOCHENTAG(G3;2)>5  255         2$G$3
$G$3:$AG$301.Bed.: Formel ist =SVERWEIS(G$3;Feiertage;1;0)  255         2$G$3:$AG$3
Bedingte Formatierung Haupttabelle 2 oberere/unterer Bereich    
Wird angewendet auf   Nr. Bed.  Format Zelle Anhalten Auswahl   Anzeige   Anzahl   Durchschnitt   Typ   Bereich
$G$302.Bed.   Falsch          2$G$3
$G$3:$AG$301.Bed.   Falsch          2$G$3:$AG$3
Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.


Füllfarben und Muster  
Zelle Rot Grün Blau Color Muster Farbe
M2:M3 25500255   
Zellen mit Füllfarbe automatisch werden nicht dargestellt

Schriftformate  
Zelle Rot Grün Blau Color Stil Unterstreichung Effekte Durchgestrichen Schriftart
G2:S3     Fett        Calibri 
Zellen mit Schriftformatierung automatisch werden nicht dargestellt


Frei
 ABC
1Jahr2018 
2 Feiertag im
Bundesland
Text der geschrieben
wird
301.01.201801.01.2018Neujahr
406.01.201806.01.2018hl. 3 Könige
512.02.2018 Rosenmontag
630.03.201830.03.2018Karfreitag
731.03.2018 Ostersamstag
801.04.201801.04.2018Ostersonntag
902.04.201802.04.2018Ostermontag
1001.05.201801.05.2018Maifeiertag
1110.05.201810.05.2018Christi Himmelfahrt
1213.05.2018 Muttertag Formel von M.I.Nitraum
1319.05.2018 Pfingstsamstag
1420.05.201820.05.2018Pfingstsonntag
1521.05.201821.05.2018Pfingstmontag
1631.05.201831.05.2018Fronleichnam Bayern
1715.08.201815.08.2018Maria Himmelfahrt
1803.10.201803.10.2018Nationalfeiertag
1907.10.2018 Reformationstag laut Hinweis im Forum gibt es noch unterschiede in den Konfesion (evangelich / katolisch)
2031.10.2018 Reformationstag
2101.11.201801.11.2018Allerheiligen
2218.11.2018 Volkstrauertag
2321.11.2018 Buss- und Bettag
2425.11.2018 Totensonntag/Ewigkeitssontag
2502.12.2018 1. Advent
2609.12.2018 2. Advent
2716.12.2018 3. Advent
2823.12.2018 4. Advent
2924.12.201824.12.2018hl. Abend
3025.12.201825.12.20181. Weihnachtstag
3126.12.201826.12.20182. Weihnachtstag
3231.12.201831.12.2018Silvester
33   
34   
35Jahr2019 
36 Feiertag im
Bundesland
Text der geschrieben
wird
3701.01.201901.01.2019Neujahr
3806.01.201906.01.2019hl. 3 Könige
3904.03.2019 Rosenmontag
4019.04.201919.04.2019Karfreitag
4120.04.2019 Ostersamstag
4221.04.201921.04.2019Ostersonntag
4322.04.201922.04.2019Ostermontag
4401.05.201901.05.2019Maifeiertag
4530.05.201930.05.2019Christi Himmelfahrt
4612.05.2019 Muttertag Formel von M.I.Nitraum
4708.06.2019 Pfingstsamstag
4809.06.201909.06.2019Pfingstsonntag
4910.06.201910.06.2019Pfingstmontag
5020.06.201920.06.2019Fronleichnam Bayern
5115.08.201915.08.2019Maria Himmelfahrt
5203.10.201903.10.2019Nationalfeiertag
5306.10.2019 Reformationstag laut Hinweis im Forum gibt es noch unterschiede in den Konfesion (evangelich / katolisch)
5431.10.2019 Reformationstag
5501.11.201901.11.2019Allerheiligen
5617.11.2019 Volkstrauertag
5720.11.2019 Buss- und Bettag
5824.11.2019 Totensonntag/Ewigkeitssontag
5902.12.2018 1. Advent
6009.12.2018 2. Advent
6116.12.2018 3. Advent
6223.12.2018 4. Advent
6324.12.201924.12.2019hl. Abend
6425.12.201925.12.20191. Weihnachtstag
6526.12.201926.12.20192. Weihnachtstag
6631.12.201931.12.2019Silvester

verwendete Formeln
Zelle Formel Bereich N/A
B1=JAHR('E:\~Privat\Arbeitszeit\[arbeitszeit_2018.xlsm]1'!B1)  
B35=B1+1  
A3,A37=DATWERT("01.01."&B1)  
A4,A38=DATWERT("06.01."&B1)  
A5,A39=A8-48  
A6,A40=A8-2  
A7,A41=A8-1  
A8,A42=DM((TAG(MINUTE(B1/38)/2+55) & ".4." & B1)/7;)*7-WENN(JAHR(1)=1904;5;6)  
A9,A43=A8+1  
A10,A44=DATWERT("01.05."&B1)  
A11,A45=A8+39  
A12,A46=DATUM(B1;5;1)+15-WOCHENTAG(DATUM(B1;5;1))  
A13,A47=A8+48  
A14,A48=A8+49  
A15,A49=A8+50  
A16,A50=A8+60  
A17,A51=DATUM(B1;8;15)  
A18,A52=DATWERT("03.10."&B1)  
A19,A53=DATUM(B1;10;1)+7-WOCHENTAG(DATUM(B1;10;1);2)  
A20,A54=DATWERT("31.10."&B1)  
A21,A55=DATWERT("01.11."&B1)  
A22,A56=DATUM(B1;12;25)-WOCHENTAG(DATUM(B1;12;25);2)-35  
A23,A57=DATUM(B1;12;25)-WOCHENTAG(DATUM(B1;12;25);2)-32  
A24,A58=DATUM(B1;12;25)-WOCHENTAG(DATUM(B1;12;25);2)-28  
A25,A59=DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)-21  
A26,A60=DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)-14  
A27,A61=DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)-7  
A28,A62=DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)  
A29,A63=DATWERT("24.12."&B1)  
A30,A64=DATWERT("25.12."&B1)  
A31,A65=DATWERT("26.12."&B1)  
A32,A66=DATWERT("31.12."&B1)  
B3:B4,B6,B8:B11,B14:B18,B21,B29:B32,B37:B38,B40,B42:B45,B48:B52,B55,B63:B66=A3  
C3:C32,C37:C66=D3  
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 25.14 einschl. 64 Bit


Gruß Hajo

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
FEIERTAGE bis 66 statt 30
10.10.2018 08:14:21
lupo1
AW: FEIERTAGE bis 66 statt 30
10.10.2018 08:47:52
Hajo_Zi
Hallo Lupo,
ich habe mit mehren Dateien gearbeitet un habe es übersehen. Danke.
Gruß Hajo
AW: Sverweis
10.10.2018 08:19:27
ChrisL
Hi Hajo
Die Datei habe ich nicht nachgebaut, darum kann ich nur vermuten ;)
müsste hier nicht eine 2 stehen?
=SVERWEIS(G$3;Feiertage;1;0)
Zudem geht der Bereichsname nur bis Zeile 30
=Frei!$B$2:$C30
cu
Chris
1 ist schon korrekt so ...
10.10.2018 08:27:07
lupo1
... die Frage allerdings nicht, denn bis zum 25.12. oder 31.12.18 scheint es ja zu gehen.
SVERWEIS verwendet man auch bei 1-Spaltern.
AW: Sverweis
10.10.2018 08:48:53
Hajo_Zi
Hallo Chris,
ich habe mit mehren Dateien gearbeitet un habe es übersehen. Danke.
Gruß Hajo
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige