Komplexe Schichtstundenberechnung in Excel

Question

OK, hier sind also mehrere unterschiedliche Schichten im Einsatz und ich möchte eine Formel für die Gesamtstundenzahl.

Das Problem ist, dass der Dienstplan mit einer Reihe unterschiedlicher Notationen für verschiedene Schichten geschrieben wird.

Einige sind 2-FIN, einige sind 7-5PM, einige sind FRÜH oder SPÄT

jetzt weiß ich, wie viele Stunden jede Schicht hat, aber ich möchte eine Möglichkeit, sie in Excel zu berechnen. Ich schätze, ich könnte eine Tabelle mit einer Menge möglicher Schichten und deren Gesamtstunden erstellen und dann Excel den Dienstplan mit der Tabelle abgleichen und die Gesamtstunden für die Woche zusammenrechnen lassen.

aber ich habe keine Ahnung, wie das geht. Alle meine Versuche diesbezüglich enden mit einem Problem mit Ihrer Formel und ohne weitere Erklärung.

Answer 1

Da Sie Excel 2010 verwenden, stehen Ihnen einige großartige Tools zur Verfügung, die Ihnen dies erleichtern. Ich würde Folgendes tun:

1. Erstellen Sie eine einfache Tabelle für Ihre Schichten. Sie kann aus zwei Spalten bestehen: Name&Length

   

2. Erstellen Sie eine Tabelle für Ihren Zeitplan. Diese sollte vier Spalten haben: Date, Employee Name, Shift, Hours.

   

   Datekann manuell eingegeben werden.

   Beide Employee Namekönnen Shiftdie Datenüberprüfung von Excel verwenden, um eine Dropdown-Liste mit bestimmten Werten zu erstellen.

   Hoursverwendet SVERWEIS, um den Wert aus Ihrer Shift-Tabelle zu ermitteln. In meinem Beispiel lautet die Formel =VLOOKUP([@Shift],Table1,2,FALSE). Dadurch wird der ShiftWert in Ihrer zweiten Tabelle in der ersten Tabelle gesucht, Table1, und die zweite Spalte zurückgegeben. LengthEs wird nur ein zurückgegeben Exact Match (2)(was bedeutet, dass die Dinge nicht in alphabetischer Reihenfolge sein müssen).

   Für eine schnelle Überprüfung der Stunden habe ich der Tabelle auch eine Gesamtzeile hinzugefügt.

3. Fügen Sie abschließend einige zusätzliche Funktionen hinzu, indem Sie Pivot Tableaus Ihrer zweiten Tabelle eine erstellen. Auf diese Weise können Sie Ihre Daten schnell zusammenfassen und berichten. Sie können aus einer Datenquelle mehrere Pivots erstellen, sodass Sie diese beiden problemlos erstellen und aktualisieren können, wenn Ihre Quelldaten (Tabelle 2) aktualisiert werden.

   

Answer 2

Sie könnten eine Tabelle mit Dropdown-Listen einrichten, sodass jede Schicht im gleichen Format vorliegt. Dann haben Sie eine Tabelle mit den Schichten und den Stunden daneben. Zum Beispiel 19-21 Uhr in einer Zelle und dann 2 Stunden in der Zelle daneben.

Sie verwenden eine Summen-Verweisfunktion wie

=SUM(VLOOKUP(B6;Sheet2.A3:B4;2;0);VLOOKUP(B7;Sheet2.A3:B4;2;0))

Dadurch erhalten Sie die Gesamtstundenzahl.

Bitte beachten Sie, dass ich Open Office verwendet habe, um zu prüfen, ob dies funktioniert. Wenn Sie Microsoft Excel verwenden, ändern Sie in der Formel ";" in ",". Wenn die Zeitpläne keine Stunden enthalten, können Sie entweder eine if-Anweisung einfügen oder 0 Stunden ohne Arbeit in den Zeitplan aufnehmen.