Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 14:23:45
Gehe zu Tabellenblatt
23.04.2024 14:07:03
Alternative Summewenn für Verlinkung closed Workbook
23.04.2024 13:44:37
Datum in Excelformel anzeigen
23.04.2024 13:31:41
alle Tabellblätter als csv exportieren - Verzeichnis wählen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1784to1788
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1784to1788
1784to1788
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Kalenderansicht Urlaub aus Excelliste

Kalenderansicht Urlaub aus Excelliste
27.09.2020 12:42:08
Markus
Hallo Zusammen,
ich komme bei diesem Thema leider nicht weiter. Vielleicht kann mir jemand hier weiterhelfen?
Ich habe eine Auswertung aus dem HR-System mit dem Beginn und Endedatum von Urlaubstagen im Tabellenblatt "Urlaubsauswertung". Jede Personalnummer kommt mehrmals in der Auswertung vor.
Ich möchte für alle Mitarbeiter nun eine Kalenderübersicht im Tabellenblatt "Kalenderansicht" mit den Urlaubsabwesenheiten an den einzelnen Tagen erstellen - bei Urlaub an dem jeweiligen Tag soll ein x eingetragen werden, ohne Urlaub soll die Zelle leer bleiben.
Anbei die beiden Beispieltabellenblätter in einer Datei: https://www.herber.de/bbs/user/140466.xlsx
Ich freue mich über eine Rückmeldung.
Vielen Dank und viele Grüße
Markus

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: zum Beispiel mit SUMMENPRODUKT() und ...
27.09.2020 13:09:14
neopa
Hallo Markus,
... benutzerdefinierten Zahlenformat: "x";; in den Ergebniszellen.
In C2: =SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99=C$1))
und diese Formel nach rechts und unten kopieren.
Gruß Werner
.. , - ...
AW: zum Beispiel mit SUMMENPRODUKT() und ...
27.09.2020 13:23:50
Herbert_Grom
Hallo Werner,
was mache ich falsch, dass deine Formel bei mir nix anzeigt?
Userbild
Servus
AW:kleiner/größergleich ist bei Dir verwechset owT
27.09.2020 14:35:03
neopa
Gruß Werner
.. , - ...
AW: AW:kleiner/größergleich ist bei Dir verwechset owT
27.09.2020 15:03:50
Herbert_Grom
Hallo Werner
das ist deshalb „verwechselt“, da es anders herum ja auch nicht funktionierte!
Servus
Anzeige
funktioniert doch korrekt ...
27.09.2020 16:52:30
Matthias
Hallo
Funktioniert absolut korrekt!
Kalenderansicht

 ABCDEFGHIJKLMNOPQRST
1PersonalnummerName01.01.202002.01.202003.01.202004.01.202005.01.202006.01.202007.01.202008.01.202009.01.202010.01.202011.01.202012.01.202013.01.202014.01.202015.01.202016.01.202017.01.202018.01.2020
2123456789Mustermannxxxx  xx   xxxxxxx
3987654321Musterfrauxxx  x  x       xx

Formeln der Tabelle
ZelleFormel
C2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=C$1)*(Urlaubsauswertung!$D$2:$D$99>=C$1))
D2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=D$1)*(Urlaubsauswertung!$D$2:$D$99>=D$1))
E2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=E$1)*(Urlaubsauswertung!$D$2:$D$99>=E$1))
F2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=F$1)*(Urlaubsauswertung!$D$2:$D$99>=F$1))
G2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=G$1)*(Urlaubsauswertung!$D$2:$D$99>=G$1))
H2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=H$1)*(Urlaubsauswertung!$D$2:$D$99>=H$1))
I2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=I$1)*(Urlaubsauswertung!$D$2:$D$99>=I$1))
J2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=J$1)*(Urlaubsauswertung!$D$2:$D$99>=J$1))
K2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=K$1)*(Urlaubsauswertung!$D$2:$D$99>=K$1))
L2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=L$1)*(Urlaubsauswertung!$D$2:$D$99>=L$1))
M2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=M$1)*(Urlaubsauswertung!$D$2:$D$99>=M$1))
N2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=N$1)*(Urlaubsauswertung!$D$2:$D$99>=N$1))
O2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=O$1)*(Urlaubsauswertung!$D$2:$D$99>=O$1))
P2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=P$1)*(Urlaubsauswertung!$D$2:$D$99>=P$1))
Q2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=Q$1)*(Urlaubsauswertung!$D$2:$D$99>=Q$1))
R2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=R$1)*(Urlaubsauswertung!$D$2:$D$99>=R$1))
S2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=S$1)*(Urlaubsauswertung!$D$2:$D$99>=S$1))
T2=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A2)*(Urlaubsauswertung!$C$2:$C$99<=T$1)*(Urlaubsauswertung!$D$2:$D$99>=T$1))
C3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=C$1)*(Urlaubsauswertung!$D$2:$D$99>=C$1))
D3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=D$1)*(Urlaubsauswertung!$D$2:$D$99>=D$1))
E3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=E$1)*(Urlaubsauswertung!$D$2:$D$99>=E$1))
F3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=F$1)*(Urlaubsauswertung!$D$2:$D$99>=F$1))
G3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=G$1)*(Urlaubsauswertung!$D$2:$D$99>=G$1))
H3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=H$1)*(Urlaubsauswertung!$D$2:$D$99>=H$1))
I3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=I$1)*(Urlaubsauswertung!$D$2:$D$99>=I$1))
J3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=J$1)*(Urlaubsauswertung!$D$2:$D$99>=J$1))
K3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=K$1)*(Urlaubsauswertung!$D$2:$D$99>=K$1))
L3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=L$1)*(Urlaubsauswertung!$D$2:$D$99>=L$1))
M3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=M$1)*(Urlaubsauswertung!$D$2:$D$99>=M$1))
N3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=N$1)*(Urlaubsauswertung!$D$2:$D$99>=N$1))
O3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=O$1)*(Urlaubsauswertung!$D$2:$D$99>=O$1))
P3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=P$1)*(Urlaubsauswertung!$D$2:$D$99>=P$1))
Q3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=Q$1)*(Urlaubsauswertung!$D$2:$D$99>=Q$1))
R3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=R$1)*(Urlaubsauswertung!$D$2:$D$99>=R$1))
S3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=S$1)*(Urlaubsauswertung!$D$2:$D$99>=S$1))
T3=SUMMENPRODUKT((Urlaubsauswertung!$A$2:$A$99=$A3)*(Urlaubsauswertung!$C$2:$C$99<=T$1)*(Urlaubsauswertung!$D$2:$D$99>=T$1))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß ...
Anzeige
AW: AW:kleiner/größergleich ist bei Dir verwechset owT
27.09.2020 16:55:41
Herbert_Grom
Hallo Werner,
seltsam, aber jetzt funktioniert es! Vorher hat es nicht funktioniert. Vielleicht funktioniert es jetzt, da ich den PC zwischenzeitlich neu gebootet habe. Das passiert in letzter Zeit häufig! Aber egal! Asche auf mein Haupt, dass ich deine Formel angezweifelt habe! Kommt nicht wieder vor, glaub ich jedenfalls! :o)=)
Servus
AW: dann ist es jetzt ja gut owT
27.09.2020 19:47:09
neopa
Gruß Werner
.. , - ...
AW: zum Beispiel mit SUMMENPRODUKT() und ...
27.09.2020 18:21:56
Markus
Hallo Werner,
vielen lieben Dank. Ich konnte es so einbauen und es klappt wunderbar :-)
VG Markus
AW: bitteschön owT
27.09.2020 19:47:53
neopa
Gruß Werner
.. , - ...
AW: Kalenderansicht Urlaub aus Excelliste
27.09.2020 14:14:33
Richi
Versuchs mal mit dem Code
Option Explicit
Sub Abwesenheiten_übernehmen() Dim aktName As String Dim aktGruppe As String Dim anfDatum As Date Dim endDatum As Date Dim letzteSpalteZ As Long Dim letzteZeileQ As Long Dim letzteZeileZ As Long Dim maxDatum As Date Dim minDatum As Date Dim spalteZ As Long Dim wb As Workbook Dim wsQ As Worksheet ' Quelle Dim wsZ As Worksheet ' Ziel Dim zeileQ As Long Dim zeileZ As Long Dim ZellenStart As Long Dim ZellenEnde As Long Dim ZellenRange As Long Dim SpaA, SpaB, SpaC, SpaD, SpaE As String 'Spalten der _ Datenquellen Dim Val01, Val99 As String 'Values Zellen in _ Grafik Dim CI01, CI02, CI03, CI04, CI05, CI06, CI07, CI08, CI12 As Long 'Farbcodes Dim CI16, CI17, CI19, CI20, CI24, CI26, CI35 As Long 'Farbcodes Dim CI37, CI39, CI40, CI41, CI43, CI45, CI46, CI53 As Long 'Farbcodes '--------------Variablen Ferien-------------- Val99 = "'o" 'Alle nicht _ definierten Absenzen Val01 = "x" 'Ferien ' --------------Colorindex-------------- CI01 = 1 'Colorindex CI02 = 2 'Colorindex CI03 = 3 'Colorindex CI04 = 4 'Colorindex CI05 = 5 'Colorindex CI06 = 6 'Colorindex CI07 = 7 'Colorindex CI08 = 8 'Colorindex CI12 = 12 'Colorindex CI16 = 16 'Colorindex CI17 = 17 'Colorindex CI19 = 19 'Colorindex CI20 = 20 'Colorindex CI24 = 24 'Colorindex CI26 = 26 'Colorindex CI35 = 35 'Colorindex CI37 = 37 'Colorindex CI39 = 39 'Colorindex CI40 = 40 'Colorindex CI41 = 41 'Colorindex CI43 = 43 'Colorindex CI45 = 45 'Colorindex CI46 = 46 'Colorindex CI53 = 53 'Colorindex '--------------Spalte Quellen Dateneblatt-------------- SpaA = "A" SpaB = "B" SpaC = "C" SpaD = "D" SpaE = "E" Set wb = ThisWorkbook Set wsQ = wb.Worksheets("Urlaubsauswertung") Set wsZ = wb.Worksheets("Kalenderansicht") letzteZeileZ = wsZ.Cells(wsZ.Rows.Count, "A").End(xlUp).Row letzteSpalteZ = wsZ.Cells(1, wsZ.Columns.Count).End(xlToLeft).Column Application.CutCopyMode = False '--------------------------Startblock zur Geschwindigkeitserhöhung bei Schleifen--------------- _ With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With '--------------------------Bestehende Farben und x-Werte löschen in Quelldatei----------------- _ If letzteZeileZ > 2 And letzteSpalteZ > 2 Then With wsZ.Cells(2, "A").Resize(93, letzteSpalteZ - 0) ' Löschen ab Zellen .Interior.Pattern = xlNone .ClearContents .ClearComments End With End If '--------------------------Strart Zeichnen Grafik------------------------ minDatum = wsZ.Cells(1, 3) maxDatum = wsZ.Cells(1, letzteSpalteZ) letzteZeileQ = wsQ.Cells(wsQ.Rows.Count, SpaA).End(xlUp).Row zeileZ = 1 ' Start unterhalb _ Zelle schreiben) For zeileQ = 2 To letzteZeileQ If wsQ.Cells(zeileQ, SpaA) aktName Then ' Wechsel des _ Personalnummer zeileZ = zeileZ + 1 aktGruppe = wsQ.Cells(zeileQ, SpaA) ' Zelle Quelle _ Personalnummer) wsZ.Cells(zeileZ, SpaA) = aktGruppe ' Zelle Ziel _ Personalnummer) aktName = wsQ.Cells(zeileQ, SpaB) ' Zelle Quelle _ Name) wsZ.Cells(zeileZ, SpaB) = aktName ' Zelle Ziel Name) End If If Not IsEmpty(wsQ.Cells(zeileQ, SpaC)) And _ Not IsEmpty(wsQ.Cells(zeileQ, SpaD)) Then anfDatum = wsQ.Cells(zeileQ, SpaC) endDatum = wsQ.Cells(zeileQ, SpaD) ZellenStart = Application.Match(wsQ.Cells(zeileQ, SpaC), wsZ.Range(Cells(1, 1), Cells(1, _ letzteSpalteZ)), 1) ZellenEnde = Application.Match(wsQ.Cells(zeileQ, SpaD), wsZ.Range(Cells(1, 1), Cells(1, _ letzteSpalteZ)), 1) ZellenRange = ZellenEnde - ZellenStart For spalteZ = ZellenStart To ZellenStart With wsZ.Range(Cells(zeileZ, ZellenStart), Cells(zeileZ, ZellenEnde)) Select Case wsQ.Cells(zeileQ, "E") Case "" ' Ferien .Value = Val01 .Interior.ColorIndex = CI06 .Font.Size = 11 .Font.ColorIndex = CI01 Case Else ' undefinierter Code .Value = Val99 .Interior.ColorIndex = CI16 .Font.Size = 11 .Font.ColorIndex = CI01 End Select End With ' End If Next spalteZ End If Next zeileQ '------------------------------------Endblock zur Geschwindigkeitserhöhung bei Schleifen-------- _ With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With End Sub
Anzeige
AW: Kalenderansicht Urlaub aus Excelliste
27.09.2020 18:25:23
Markus
Hallo Richi,
danke für Deinen Tipp. Wenn ich Deinen Code verwende, kommt bei mir die Fehlermeldung "Typen unverträglich". Weißt Du, woran das liegt?
VG Markus
AW: Kalenderansicht Urlaub aus Excelliste
27.09.2020 15:28:03
Daniel
Hi
Wenn die Liste nicht allzu lang ist, reicht dir diese Formel in Zelle C2 
=ZÄHLENWENNS(Urlaubsauswertung!$A:$A;$A2;Urlaubsauswertung!$C:$C;"="&C$1)
Das ergibt 1 bei einem Urlaubstage und 0 bei einem anderen Tag
Wenn du lieber "x" und "" (leer) hast, kannst du das ja über ein einfaches wenn erreichen: 
=Wenn(Zählenwenns(..);"x";"")
Wenn die Liste länger ist und es Performanceprobleme gibt, könnte man zusätzlich in in zwei hilfspalten Anfangs- und Endzeile für jede Personalnummer ermitteln und dann im ZählenWenns anstelle der ersten Prüfung auf die Personalnummer die Zellbereiche über Index einschränken, so dass das ZählenWenns nicht über die komplette Spalte laufen muss sondern nur über den Bereich der jeweiligen Zeilennummer.
Gruß Daniel
Anzeige
AW: Kalenderansicht Urlaub aus Excelliste
27.09.2020 18:30:38
Markus
Hallo Daniel,
auch Dir vielen lieben Dank für Rückmeldung. Ich habe nun das Summenprodukt von Werner verwendet, da dies bei einer längeren Liste etwas schneller läuft als über Zählenwenns.
VG Markus
AW: Kalenderansicht Urlaub aus Excelliste
28.09.2020 12:01:13
Daniel
Hi
Wundert mich, normalerweise ist ZählenWenns schneller.
Vielleicht musst du hier auch mal auf den notwendigen Bereich einschränken.
Wenn du die Liste ohne spürbare Rechenzeiten haben willst, dann schau dir mal meine Anmerkung an.
Damit sollte sich das ganze erheblich beschleunigen lassen.
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige