Soll-Arbeitszeit bis zum heutigen Tag auswerten

Question

Hallo Zusammen,

ich habe ein kleines Problem und komme einfach nicht auf die
Lösung.

Ich versuche gerade eine Tabelle zu erstellen, die mir anzeigt, wie
viel Zeit Mitarbeiter in einem Tool gebucht haben (erledigt).
Normalerweise sollten das natürlich 8 Stunden am Tag sein.
Ich habe für jeden Mitarbeiter auch den Soll-Wert im Monat
hinterlegt.
Allerdings benötige ich den Abgleich Ist zur Soll-Arbeitszeit bis zum
heutigen Tag. Ist ja alles kein Problem eigentlich, ABER wir haben
Mitarbeiter, die arbeiten mal 1 Tag pro Woche und manchmal 2 oder
3 Tage.
Diese Daten sind in einer Einsatzplanung hinterlegt, aber mit
"kryptischen" Kürzeln, die ich nicht abändern darf.
Ich bräuchte ein Formel, die folgendes ausrechnet:
Zähle, wie oft im Bereich C1 (Tag 1 im Monat), bis Spalte X (X =
Anzahl der bisher im Monat abgelaufenen Tage; also heute wäre es
Spalte C1:R1) die Kürzel x,y,z auftauchen und multipliziere diese
Zahl mit 8.
Es gibt auch Kürzel, die bedeuten, dass der Mitarbeiter an dem Tag
Urlaub oder sonstiges hat. Diese Kürzel dürfen nicht ausgewertet
werden.

Ich hoffe ich habe mein Anliegen halbwegs verständlich
ausgedrückt. =/
Und bedanke mich vorab für eure Hilfe!

mfg
VollBah

Answer 1

Hallo,

schreibe die folgende Formel in eine leere Zelle der Zeile, die du auswerten willst:

=(ZÄHLENWENN(C1:INDIREKT(ADRESSE(ZEILE();TAG(HEUTE()+2)));"x")+ZÄHLENWENN(C1:INDIREKT(ADRESSE(ZEILE();TAG(HEUTE()+2)));"y")+ZÄHLENWENN(C1:INDIREKT(ADRESSE(ZEILE();TAG(HEUTE()+2)));"z"))*8

Die Formel kannst du soweit wie benötigt nach unten ziehen.

Gruß

M.O.

Answer 2

Hallo M.O.,

leider achtet die Formel nicht darauf, den Bereich (gesamt von D1:AG1) nur bis zum x-ten Tag im Monat (heute) zu bewerten. Also heute dürfte die Formel nur die Werte aus dem Bereich D1:R1 bewerten.

Mein Testkollege hat zum Beipiel ab W1 "URL" (für Urlaub) eingetragen. Dies wird von deiner Formel aber heute auch schon berücksichtigt.

Ich habe in meiner Tabelle eine Abfrage, wie viel Tage der Monat bisher hatte. Diese Formel enthält jeden Tag den richtigen Wert.
Deine Formal müsste also diese Zahl abgreifen, um zu wissen, wie groß der Bereich ist, denn sie berücksichtigen muss.

Aber trotzdem schon mal vielen Dank für deine Hilfe! Die Formel funktioniert abgesehen von der Funktion bereits einwandfrei!

mfg
VollBah

Answer 3

Hallo,

probiere auch alternativ:

{=SUMME(WENN((C1:R1="x")+(C1:R1="y")+(C1:R1="z");1))*8}

runterkopieren bis?

[sub]Achtung Matrixformel:
die geschwungenen Klammern entfernen, anschließend mit Strg/Shift/Enter gleichzeitig abschließen, erst dann runterkopieren[/sub]

Gruß
Paul1

Answer 4

Hallo Paul1,

vielen Dank für deine Formel. Aber deine Formal, würde bspw. auch morgen nur bis R1 und nicht bis S1 prüfen.

mfg
VollBah

Answer 5

Hallo VollBah,

die Formel von @M.O. und mir soll ja nur ein Rüstzeug für Dich darstellen, anpassen solltest Du sie selber.

Gruß
Paul1

Answer 6

Hallo Paul1,

dafür bin ich euch auch sehr dankbar. Aber ich habe einfach genau das Problem, dass ich echt keine Ahnung habe, wie ich die Formel so aufbauen kann, dass sie den zu prüfende Bereich eben flexibel bzw.
abhängig von der Zahl der abgelaufenen Tage gestaltet.

Genau das ist mein großer Knackpunkt an dem ich gerade am Verzweifeln bin! :D

mfg
VollBah

Answer 7

Hallo,

meine gepostete Formel funktioniert unter den den von dir vorgegebenen Bedingungen einwandrei.
Der 1. Tag des Monats steht in C1 und die restlichen Tage stehen dann in D1 etc. Den Urlaub mit dem Kürzel "URL" kann die Formel gar nicht berücksichtigen, da nur "x", "y" oder "z" gezählt werden.
Die Formel muss z.B. in der 1. Zeile stehen, damit die Zellen ab C1 gezählt werden.

Also schau noch mal genauer auf deine Tabelle und überprüfe, ob dein PC das richtige Datum hat, damit wird nämlich berechnet, wie weit die Auswertung gehen soll.

Ansonsten lade mal eine Beispiel auf eine Hoster deiner Wahl hoch und poste den Link hier.

Gruß

M.O.

Answer 8

Hallo,

mein Vorschlag wäre:

=SUMMENPRODUKT((C1:INDIREKT(ADRESSE(ZEILE();TAG(HEUTE()+2)))={"x";"y";"z"})*1)

Gruß
Rainer

Answer 9

Hallo M.O.,

du hast recht. Es funktioniert doch. Keine Ahnung, warum ich zuerst einen falschen Wert erhalten habe.

Aber ein Problem habe ich jetzt trotzdem noch:
Ich habe die Formal etwas erweitert, da bei jedem Kollege teilweise andere Kürzel stehen. Sobald ich das 4. Zählenwenn mit angebe (hier ist ein Kürzel "WIE" angeben), welches beim ersten Kollege in der
Einsatzplanung nicht auftaucht, springt der Wert von dem korrekten Wert (88 Stunden) auf, für mich unerklärliche, 944.

Anbei die Formel:

=
(ZÄHLENWENN(D1:INDIREKT(ADRESSE(ZEILE();TAG(HEUTE()+2)));"HAM")+ZÄHLENWENN(D1:INDIREKT(ADRESSE(ZEILE();TAG(HEUTE()+2)));"SK")+ZÄHLENWENN(D1:INDIREKT(ADRESSE(ZEILE();TA
G(HEUTE()+2)));"KIE")")+ZÄHLENWENN(D1:INDIREKT(ADRESSE(ZEILE();TAG(HEUTE()+2)));"WIE"))*8

Wenn ich den Part +ZÄHLENWENN(D1:INDIREKT(ADRESSE(ZEILE();TAG(HEUTE()+2)));"WIE") wieder entferne, dann habe ich wieder meinen korrekten Wert.

Kann bzw. muss ich in der Formel mit angeben, dass er 0 verwendet, wenn er das angegebene Kürzel im Bereich nicht findet? Wenn ja, wie mache ich das?

mfg
VollBah

Answer 10

Hallo,

da du in D1 beginnst müsstest du anstatt +2 ---> +3 nehmen.

oder mein Vorschlag

=SUMMENPRODUKT((D1:INDIREKT(ADRESSE(ZEILE();TAG(HEUTE()+3)))={"HAM";"SK";"KIE";"WIE"})*1)*8

Gruß
Rainer