[發問] 苦思不得其解之難題...

fr447446

在抓了幾百根頭髮之後還是要來發問...
請教各位出神入化的大師，
我想從一段時間內，抓出四個班別各佔了多少小時，
詳細內容請參閱附件，謝謝

hugh0620

回復 1# fr447446


    xxxx  思考中

Hsieh

G3=SUMPRODUCT((($A$2:$A$125+$B$2:$B$125)>=$E3)*(($A$2:$A$125+$B$2:$B$125)<=$F3)*($C$2:$C$125=G$2))
向下向右複製

fr447446

回復 3# Hsieh
感謝 Hsieh 大這麼快就回覆，
在驗證之後發現出來的數值好像不正確，
以 (F3-E3)*24 後這個區間的總時數有30.98小時，
但函數帶入後 ABCD班總時數只有6小時

gong

題目到現在都還沒有看懂

不過既然是一個月的班表
我想總工時應該落在168~360之間(8*22~12*30)

不知要算出時,還是天,還是連秒都要
還有開始時間有意義嗎?是要算出該區間工時

fr447446

回復 5# gong

我的需求是...每個開始與結束時間的區間內，每個班所佔用的時間分配比，

A~C欄位只是我用來區分每個班的輪班方式，
因為每天的早上7:30跟晚上的19:30就會換一次班，
也就是 早上的07:30~19:30 是一班，19:30~07:30會變成下一班，

又因為是四班二輪的方式所以每兩天就會從A/B班換成C/D班，
班別輪替的班表資料會從1月到12月，甚至會跨年度，

我的思考邏輯是...先從區間的日期去抓出在符合的資料範圍，
再從這個範圍內逐一將時間差作加總，
但...始終想不出方法來做出時間差的加總...

Hsieh

回復 6# fr447446


    以你舉的例子範圍是5:9列的資料要計算G3:J3的值
那麼其中A有3筆，B有2筆
那各班別各個起迄時間如何判定?

fr447446

回復 7# Hsieh

附上明確的手動算法，造成辨讀不易請多包含~

fr447446

暫時以粗糙的自定義函數 + 重新規劃班別的輪班規則後暫時達到需要的功能，
附上初版，有更好的方式的話煩請不吝指教，THANKS！

Andy2483

謝謝論壇,謝謝各位前輩
後學改變需求情境,藉此帖練習陣列與字典,學習方案如下,請各位前輩指教

執行前:

下載 (128.27 KB)

2023-5-12 11:43

執行結果:

下載 (181.72 KB)

2023-5-12 11:43

Option Explicit
Sub TEST()
Dim Brr, Crr, Y, N&, i&, j&
'↑宣告變數
Set Y = CreateObject("Scripting.Dictionary")
'↑令Y變數是 字典
Brr = Range([C1], [A65536].End(xlUp))
'↑令Brr變數是 二維陣列,以A~C欄資料帶入
For i = 2 To UBound(Brr)
'↑設順迴圈
   If Y(Brr(i, 3)) = "" Then N = N + 1: Y(Brr(i, 3)) = N
   '↑令以字典紀錄班別在結果陣列Crr的欄位
Next
ReDim Crr(1 To UBound(Brr), 1 To 2 + N)
'↑宣告Crr變數是 二維空陣列,縱向範圍同Brr陣列,橫向從1到(2+N)
For i = 1 To UBound(Brr)
'↑設順迴圈
   Crr(i, 1) = Brr(i, 1): Crr(i, 2) = Brr(i, 2)
   '↑令日期帶入Crr第1欄,'↑令時間帶入Crr第2欄
   If i = 1 Then
   '↑如果是Brr第1列!就把班別標題列寫入Crr陣列第1列
      For j = 1 To N: Crr(1, j + 2) = Y.KEYS()(j - 1): Next
      GoTo i01
      '↑標題列處理完,不必處理後續細節,所以跳到標示i01位置繼續執行
   End If
   Crr(i, Y(Brr(i, 3)) + 2) = "V"
   '↑令Crr陣列正確班別位置欄寫入 "V"
i01: Next
With [E1].Resize(UBound(Crr), 2 + N)
   .EntireColumn.Clear
   '↑令結果欄舊資料整欄清除
   .Value = Crr
   '↑令以Crr陣列值貼入這With的範圍
   Intersect(.Cells, [E:E]).NumberFormatLocal = "yyyy/m/d"
   Intersect(.Cells, [F:F]).NumberFormatLocal = "hh:mm:ss"
   .Borders.LineStyle = 1
   .EntireColumn.AutoFit
   '↑處理格式
End With
Set Y = Nothing: Erase Brr, Crr
'↑釋放變數
End Sub