Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Blattübergreifende Auswertung anhand Datum
#1
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)
Antwortento top
#2
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
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) www.sprueche-zum-nachdenken.eu
Antwortento top
#3
Ich habe die Datei oben hinzugefügt. Es geht hier um das Blatt "Personalverfügbarkeit".
Antwortento top
#4
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
Antwortento top
#5
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
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) www.sprueche-zum-nachdenken.eu
Antwortento top
#6
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
Antwortento top
#7
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-2016)
[-] Folgende(r) 1 Benutzer sagt Danke an schauan für diesen Beitrag:
  • Der Laie
Antwortento top
#8
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-2016)
[-] Folgende(r) 1 Benutzer sagt Danke an schauan für diesen Beitrag:
  • Der Laie
Antwortento top
#9
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-2016)
[-] Folgende(r) 1 Benutzer sagt Danke an schauan für diesen Beitrag:
  • Der Laie
Antwortento top
#10
Wink 
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!
Antwortento top


Gehe zu:


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