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?
Hilfee
Danke im Voraus!!
Gruß
Der Laie
Ich habe die Datei oben hinzugefügt. Es geht hier um das Blatt "Personalverfügbarkeit".
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.
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
Hallo Günter,
Zitat:Zusätzlich sind deine verbundenen Zellen kontraproduktiv
das auch noch! So weit hatte ich gar nicht gebuddelt.
Hallöchen,
ich glaube nicht, dass Deine verbundenen Zellen zu Problemen führen. Lediglich die Darstellung mit dem Tabellentool leidet etwas darunter
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' |
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW |
| 5 | Mo Di Mi Do Fr Soll |
| 6 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 7 | | 3 | | 2 | | 2 | | 2 | | 3 | | 2 | |
| 8 | | | | | | | |
| 9 | | | | | | | |
| 10 | | | | | | | |
| 11 | | | | | | | |
| 12 | | | | | | | |
| 13 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| Zelle | Formel |
| 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 |
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' |
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW |
| 5 | Mo Di Mi Do Fr Soll |
| 6 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 7 | | 1 | | 0 | | 20 | | 19 | | 18 | | 1 | |
| 8 | | | | | | | |
| 9 | | | | | | | |
| 10 | | | | | | | |
| 11 | | | | | | | |
| 12 | | | | | | | |
| 13 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| Zelle | Formel |
| 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 |
Hallöchen,
noch 'ne Korrektur. Ich habe die Soll-Zelle AP7 als KW genommen statt oben AV1.
| Arbeitsblatt mit dem Namen 'Personalverfügbarkeit' |
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW |
| 7 | | 1 | | 0 | | 20 | | 19 | | 18 | | 15 | |
| 8 | | | | | | | |
| 9 | | | | | | | |
| 10 | | | | | | | |
| 11 | | | | | | | |
| 12 | | | | | | | |
| Zelle | Formel |
| 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 |
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
Es geht nur um die oberen (hellblauen) Personen, die anderen sind für mich uninteressant, deswegen nur 15.
Vielen vielen Dank!