Excel での複雑な勤務時間計算

Question

ここでは、いくつかの異なるシフトが運用されており、合計時間の計算式が必要です。

問題は、シフト表がシフトごとに異なる表記法で書かれていることです。

2時から出発するものもあれば、7時から5時まで出発するもの、早朝出発や深夜出発のものもあります。

各シフトの勤務時間はわかりましたが、Excel で計算する方法が欲しいです。可能なシフトとその合計勤務時間を表にして、Excel でシフト表を表に一致させて週の合計勤務時間を計算できると思います。

しかし、私はこれをどうやって行うのか分かりません。私がこれを試みても、あなたの式に問題があると返され、それ以上の説明はありません。

Answer 1

Excel 2010 を使用している場合は、これを簡単にする優れたツールがいくつかあります。私なら次のようにします。

1. シフト用のシンプルな表を作成します。2列だけの基本的なものでも構いません: Name&Length

   

2. スケジュール用の表を作成します。これには、、、、の 4 つの列がDate必要Employee Nameです。ShiftHours

   

   Date手動で入力できます。

   どちらもEmployee Name、ShiftExcel のデータ検証を使用して、特定の値のドロップダウン リストを作成できます。

   Hoursは、シフト テーブルから値を決定するために VLOOKUP を使用します。私の例では、数式は です。これは、最初のテーブルで 2 番目のテーブルの値=VLOOKUP([@Shift],Table1,2,FALSE)を検索し、2 番目の列を返し、 のみを返します(つまり、アルファベット順にする必要はありません)。ShiftTable1LengthExact Match (2)

   また、時間を簡単に確認できるように、テーブルに合計行を追加しました。

3. 最後に、2 番目のテーブルから を作成して、いくつかの追加機能を追加しますPivot Table。これにより、データをすばやく要約してレポートできます。データ ソースから複数のピボットを作成できるため、これら 2 つを簡単に作成でき、ソース データ (表 2) が更新されるたびに更新できます。

   

Answer 2

ドロップダウン リストを使用してテーブルを設定すると、すべてのシフトが同じ形式になります。次に、シフトとその横に時間を示すテーブルを作成します。たとえば、1 つのセルに午後 7 時から午後 9 時までを入力し、その横のセルに 2 時間を入力します。

次のようなsum vlookupを使用します

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

これにより合計時間がわかります。

この機能を確認するために Open Office を使用しました。Microsoft Excel を使用している場合は、数式の「;」を「,」に変更してください。スケジュールに時間が含まれていない場合は、if ステートメントを挿入するか、スケジュールに非稼働時間を 0 時間含めることができます。