標題: [發問] 開始時間與結束時間算法 [打印本頁]

---

作者: fantersy    時間: 2019-6-27 20:44     標題: 開始時間與結束時間算法

各位先進好
小弟有一問題想請教
Sum  offset  lookup 去做了一張表讓其工時能自動統計表單
但麻煩的是因為是24小時計算有些算不出來
超過00:00小弟有加1 但算出來的值還是不對
懇請解惑!!
感謝!!
公式如下
H2==IF(G2>F2,($G2-$F2)*60*24,(($G2+1)-($F2))*60*24)+SUM(OFFSET($C$2,,,LOOKUP(VALUE($F2),$B$2:$B$11,{1,2,3,4,5,6,7,8,9,10})))-IF(G2>F2,SUM(OFFSET($C$2,,,LOOKUP(VALUE($G2),$B$2:$B$11,{1,2,3,4,5,6,7,8,9,10}))),SUM(OFFSET($C$2,,,LOOKUP(VALUE($G2+1),$B$2:$B$11,{1,2,3,4,5,6,7,8,9,10}))))
[attach]30950[/attach]
[attach]30951[/attach]

---

作者: 准提部林    時間: 2019-6-29 10:23

上傳附檔與題不同???
是否重傳???

---

作者: fantersy    時間: 2019-7-1 14:05

回復 2# 准提部林

大大您好!!
[attach]30969[/attach]
重傳了附件
內容與原提問的圖片一樣，再麻煩確認一下
感謝您的幫忙!!

---

作者: 准提部林    時間: 2019-7-1 16:37

本帖最後由 准提部林 於 2019-7-1 17:19 編輯

回復 3# fantersy


休息時間調整一下:   
[attach]30971[/attach]

或這個方式:
[attach]30972[/attach]

---

作者: fantersy    時間: 2019-7-2 10:52

回復 4# 准提部林


    謝謝 准提部林 大大
這公式 比我之前還深澳
有點看不太懂  
需要點時間了解

---

作者: fantersy    時間: 2019-7-2 18:02

回復 4# 准提部林

准提部林大 您好
小弟嘗試了去用 您建議的公式去修改

並嘗試做了儲存格跟位置的修正，但都得不到一個答案
附件~請參考
[attach]30982[/attach]
想請問!!
我已經把範圍的ROW的範圍加大了為何還會錯誤!?
SMALL 已經抓到了相對的位置
但Lookup 卻抓不到
真的不太懂這公式的意思

麻煩您解惑!!謝謝!!

---

作者: 准提部林    時間: 2019-7-2 20:19

回復 6# fantersy

開始時間        結束時間
00:00        00:00
00:10        01:10
03:00        03:10
05:20        06:00
06:00        08:00
10:00        10:10
12:10        13:10
15:00        15:10
17:20        18:00
18:00        20:00
22:00        22:10

第一行必須是00:00, 否則 00:01 會LOOKUP為#N/A
共有11行*2個數字, 所以 SMALL必須是ROW(1:22),  
MOD則為 ROW(2:23), 也可用  MOD(ROW(1:22)+1,2), 只要形成{0;1;0;1~~~}陣列即可

=============================

---

作者: fantersy    時間: 2019-7-3 19:24

回復 7# 准提部林


   好的!!非常感謝 准提部林大的回覆 !!

---

作者: ML089    時間: 2019-7-4 15:59

回復 3# fantersy

依照3樓資料格式

H2公式
=MOD(G2-F2,1)*1440-SUM(
(MOD(F2*1440+ROW(INDIRECT("1:"& (G2-F2+(F2>G2))*1440)),1440)>TRANSPOSE(INT(A$2:A$11*1440)))*
(MOD(F2*1440+ROW(INDIRECT("1:"& (G2-F2+(F2>G2))*1440)),1440)<=TRANSPOSE(INT(B$2:B$11*1440)))*TRANSPOSE(C$2:C$11>0))
陣列輸入

---

作者: ML089    時間: 2019-7-4 16:06

回復 9# ML089

因 起始時間+1起跳，所以 8:00-8:00  3:00-3:00 這種為0的時間會不計入扣除，前公式可修簡

H2 陣列公式
=MOD(G2-F2,1)*1440-SUM(
(MOD(F2*1440+ROW(INDIRECT("1:"& (G2-F2+(F2>G2))*1440)),1440)>TRANSPOSE(INT(A$2:A$11*1440)))*
(MOD(F2*1440+ROW(INDIRECT("1:"& (G2-F2+(F2>G2))*1440)),1440)<=TRANSPOSE(INT(B$2:B$11*1440))))

下拉

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)