Clever-Excel-Forum › Office-Anwendungen › Excel v
Blattübergreifende Auswertung anhand Datum

Thema geschlossen 
Baumstrukturmodus
Blattübergreifende Auswertung anhand Datum
Der Laie Offline
pausiert
Registriert seit: 30.01.2019
Version(en): 365
#1
30.01.2019, 11:23 (Dieser Beitrag wurde zuletzt bearbeitet: 30.01.2019, 11:33 von Der Laie.)
Hallöchen zusammen,

ich hoffe, mir kann hier jemand weiterhelfen, nachdem ich 2 Tage an dem Problem gescheitert bin. :22:

Ich möchte in Excel 365 anhand einer einzugebenden Kalenderwoche ausgeben lassen, wer an den jeweiligen Tagen der KW und der Folgewoche anwesend ist. (Nur Anzahl der Personen.)

Ich habe Felder Mo bis Fr in den Feldern B7, J7, R7, Z7, AH7
und die Folgewoche von Mo bis Fr in den Feldern B19, J19, R19, Z19 und AH19.

Ich habe insgesamt 15 Personen.
in KW 2 sind 2 abwesend oder haben Urlaub (Dargestellt mit einem A oder U in einem anderen Arbeitsblatt) also soll in dem Feld eine 13 stehen.
Jeder Monat des Jahres hat ein eigenes Blatt in einer Excel Datei mit Namen Januar bis Dezember. (Ja, das ist Mist, aber leider Vorgabe -.-)

In diesen Blättern stehen die Personen untereinander und die Tage des Monats sind immer in den selben Spalten (der 1. immer in B8 beginnend bis zur 15. Person in B22, der 2. immer in C8 usw. bis zum 31. in AF8)

Reicht das an Beschreibung? Huh
Hilfee Confused

Danke im Voraus!!

Gruß
Der Laie


Angehängte Dateien
.xlsm   Abwesenheitskalender KFBB 2019.xlsm (Größe: 348,27 KB / Downloads: 11)
Suchen
Top
WillWissen Offline
CEF-Moderator
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
#2
30.01.2019, 11:25
Moin,

stell bitte eine Beispieldatei, die vom Aufbau her deinem Original gleichen muss, zur Verfügung. https://www.clever-excel-forum.de/thread-326.html
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Suchen
Top
Der Laie Offline
pausiert
ThreadStarter
Registriert seit: 30.01.2019
Version(en): 365
#3
30.01.2019, 11:35
Ich habe die Datei oben hinzugefügt. Es geht hier um das Blatt "Personalverfügbarkeit".
Suchen
Top
Klaus-Dieter Offline
Excel und VBA
Registriert seit: 11.04.2014
Version(en): Office 365
#4
30.01.2019, 11:40
Hallo,

da werde ich keine Arbeit hineinstecken. Häufig kommt es vor, das eine Kalenderwoche über zwei Monate verteilt ist. Das macht einen ebenso erheblichen wie unnötigen Aufwand. 

Zitat:(Ja, das ist Mist, aber leider Vorgabe -.-)

Das kannst du ja dem mitteilen, der die Vorgabe zu vertreten hat.  Blush
Viele Grüße
Klaus-Dieter
Der Erfolg hat viele Väter, 
der Misserfolg ist ein Waisenkind
Richard Cobden
Homepage Suchen
Top
WillWissen Offline
CEF-Moderator
Registriert seit: 10.04.2014
Version(en): Microsoft 365, mtl. Kanal
#5
30.01.2019, 11:53
Hi,

ich habe jetzt lange deine Datei angesehen - wie du ja selbst geschrieben hast, ist sie Mist. Eine Auswertung mit Formeln dürfte nur äußerst umständlich bis gar nicht durchführbar sein. Zusätzlich sind deine verbundenen Zellen kontraproduktiv - hier können sich u. U. gewaltige Fehler einschleichen. Lies dazu mal hier: https://www.clever-excel-forum.de/thread-142.html
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Suchen
Top
Klaus-Dieter Offline
Excel und VBA
Registriert seit: 11.04.2014
Version(en): Office 365
#6
30.01.2019, 11:58
Hallo Günter,

Zitat:Zusätzlich sind deine verbundenen Zellen kontraproduktiv

das auch noch! So weit hatte ich gar nicht gebuddelt.
Viele Grüße
Klaus-Dieter
Der Erfolg hat viele Väter, 
der Misserfolg ist ein Waisenkind
Richard Cobden
Homepage Suchen
Top
schauan Offline
Einer von hier
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#7
31.01.2019, 23:20
Hallöchen,

ich glaube nicht, dass Deine verbundenen Zellen zu Problemen führen. Lediglich die Darstellung mit dem Tabellentool leidet etwas darunter Sad

Ich frage mich jedoch, wozu Du ca. 100 Zeilen brauchst, um 15 Personen zu führen. Am 2. Januar stehen z.B. 20 x "U" in der Liste. Dazu kommen noch GU, NU und andere Abwesenheiten. Wie vereinbart sich das mit 15 Personen?


HIer mal eine Lösung:

Arbeitsblatt mit dem Namen 'Personalverfügbarkeit'
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
5Mo               Di               Mi               Do               Fr               Soll
6
7322232
8
9
10
11
12
13

ZelleFormel
B7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AP7)-5);"MMMM") & "!B6:AC105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)));"U")
J7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+1;"MMMM") & "!B6:AC105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+1));"U")
R7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+2;"MMMM") & "!B6:AC105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+2));"U")
Z7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+3;"MMMM") & "!B6:AC105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+3));"U")
AH7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+4;"MMMM") & "!B6:AC105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+4));"U")
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
[-] Folgende(r) 1 Nutzer sagt Danke an schauan für diesen Beitrag:
  • Der Laie
Homepage Suchen
Top
schauan Offline
Einer von hier
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#8
31.01.2019, 23:37 (Dieser Beitrag wurde zuletzt bearbeitet: 31.01.2019, 23:37 von schauan.)
Hallöchen,

noch eine kleine Korrektur. Der Bereich geht bis AF, also

statt
B6:AC105

dann
B6:AF105

Für Jahresanfang und -Ende musst Du noch eine Bedingung hinzufügen. Ansonsten holt Dir die Formel z.B. in KW01 am Montag den Urlaub vom 31.12.2019 - siehe hier der Montag, oder im Dezember 2019 dann Daten vom Januar 2019. Bekommst Du das hin?

Arbeitsblatt mit dem Namen 'Personalverfügbarkeit'
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
5Mo               Di               Mi               Do               Fr               Soll
6
7102019181
8
9
10
11
12
13

ZelleFormel
B7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AP7)-5);"MMMM") & "!B6:Af105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)));"U")
J7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AP7)-5+1);"MMMM") & "!B6:Af105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AP7)-5+1)));"U")
R7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+2;"MMMM") & "!B6:Af105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+2));"U")
Z7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+3;"MMMM") & "!B6:Af105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+3));"U")
AH7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+4;"MMMM") & "!B6:Af105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AP7)-5)+4));"U")
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
[-] Folgende(r) 1 Nutzer sagt Danke an schauan für diesen Beitrag:
  • Der Laie
Homepage Suchen
Top
schauan Offline
Einer von hier
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#9
01.02.2019, 08:30
Hallöchen,

noch 'ne Korrektur. Ich habe die Soll-Zelle AP7 als KW genommen statt oben AV1.

Arbeitsblatt mit dem Namen 'Personalverfügbarkeit'
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
71020191815
8
9
10
11
12

ZelleFormel
B7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AV1)-5);"MMMM") & "!B6:Af105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AV1)-5)));"U")
J7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AV1)-5+1);"MMMM") & "!B6:AF105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AV1)-5+1)));"U")
R7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AV1)-5+2);"MMMM") & "!B6:AF105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AV1)-5+2)));"U")
Z7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AV1)-5+3);"MMMM") & "!B6:AF105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AV1)-5+3)));"U")
AH7=ZÄHLENWENN(INDEX(INDIREKT(TEXT((7*KÜRZEN((2&-1&-A1)/7+AV1)-5+4);"MMMM") & "!B6:AF105");;TAG((7*KÜRZEN((2&-1&-A1)/7+AV1)-5+4)));"U")
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
[-] Folgende(r) 1 Nutzer sagt Danke an schauan für diesen Beitrag:
  • Der Laie
Homepage Suchen
Top
Der Laie Offline
pausiert
ThreadStarter
Registriert seit: 30.01.2019
Version(en): 365
#10
Wink  01.02.2019, 11:51
Wow ich werde das gleich mal ausprobieren. Ich hatte es jetzt mit separaten Vorberechnungen auf einem Extrablatt versucht aber Deine Lösung sieht schon eleganter aus Smile
Es geht nur um die oberen (hellblauen) Personen, die anderen sind für mich uninteressant, deswegen nur 15.

Vielen vielen Dank!
Suchen
Top
Thema geschlossen 


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste