標題:
[發問]
如何判斷7休1超時計算(含前月份)
[打印本頁]
作者:
jackson7015
時間:
2018-8-13 11:47
標題:
如何判斷7休1超時計算(含前月份)
如果要判斷七天內是否有出現數字"5"、"6"、"11"等三數字
請問如何判斷前月份的最後六天的格化語法；包含前月份有28天29天30天及31天的月份
第七天開始的很好計算，但包含到跨月部分的天數就卡住了，在請站上大大可以協助一下，感謝
[attach]29202[/attach]
作者:
a5007185
時間:
2018-8-13 14:02
回復
1#
jackson7015
建議是讓日期連續排列，
這樣就不用考慮跨月的問題，
而格式化公式也與你原先設定相同，
也無須調整，如圖所示。
[attach]29206[/attach]
作者:
jackson7015
時間:
2018-8-13 14:17
回復
2#
a5007185
感謝大大的回覆
因為有其他檔案和格式存在，所以前月份僅能放置後方
檔案中的儲存格也有部分隱藏，代表皆有其他功能使用
加上如果只是單純接續後月份，也有大小月閏年29天問題
所以想詢問是否有直接判斷前後部分的辦法
作者:
ML089
時間:
2018-8-13 16:59
前個月最後6天
BV48 =IF(COLUMN(A1)>6,"",$D$48-7+COLUMN(A1))
BV49 =TEXT(BV61,"[$-804] aaa;;;")
作者:
jackson7015
時間:
2018-8-13 17:57
回復
4#
ML089
感謝版主大大的回復
對不起，應該是我的表達錯誤了
需求是要[D50:I58]，這區間的儲存格格式化，判斷前七天的內容有沒有包含"5"、"6"、"11"等三數字，有的話就成立(紅字)
第七天因為可以判斷當月的部分，所以很容易
可是前6天因為包含到了[BV50:CE58]的範圍判斷，所以不曉得該如何著手
再請前輩看看，謝謝
作者:
ML089
時間:
2018-8-13 18:11
回復
5#
jackson7015
D50 格式化公式
=SUM(COUNTIF(OFFSET(D50,,,,-MIN(7,COLUMN()-3)),{5,6,11}))
作者:
jackson7015
時間:
2018-8-14 10:34
回復
6#
ML089
感謝版主ML089的幫忙
目前當月份的第七天開始都能用此語法或類似語法做判斷
(版大的語法因為聯集參數，所以不能用在格式化條件中)
有困難的地方是當月前六天要判斷[BV50:CE58]的範圍不知道要怎麼編寫
有查詢過站上相關資料，大部分都是以連續日期方式解決
但是因為格式關係，所以不能更動到前面的部分了
可以的部分就是再做出分頁放前月份來做連動判定
是否可再請大大們協助看看，感謝各位
作者:
准提部林
時間:
2018-8-14 15:13
D50.條件格式
=N(COUNT(0/COUNTIF(OFFSET(D50,,,,-MIN(7,DAY(D$48))),CHOOSE(ROW($1:$3),5,6,11)),0/(COUNTIF(OFFSET($CD50,,,,MIN(,DAY(D$48)-7)),CHOOSE(ROW($1:$3),5,6,11))))=0)
上月最後六天, 必須從CD欄往左遞減排列
作者:
jackson7015
時間:
2018-8-14 15:47
回復
8#
准提部林
感謝准提部林版大的回應
先感謝版主大大的協助，公式可正常運作
公式的編寫要慢慢消化了，好長一串要分解理解
雖然需要手動變動前月份的最後一天日期至CD位置，但已經解決大部分問題了
至於前月份最後六天的部分，是否僅能以手動方式排序 ?
因為目前是以巨集直接複製後面儲存格至[BV50:CE58]，然後再做當月份表格
所以沒有判斷以哪一日當做最後一天
再次感謝幫忙的前輩
作者:
ML089
時間:
2018-8-15 11:26
回復
7#
jackson7015
忘記是用在格式化公式不能用 {5,6,11}
參考准大公式改為 CHOOSE(ROW(1:3),5,6,11)，這是很棒的方法。
你的EXCEL資料
D48:AG48 為 2007/9/1 : 2007/9/30
BV48:CD48 為 2007/9/22 : 2007/9/30
是否應該改為
BV48:CE48 為 2007/8/22 : 2007/8/31
作者:
jackson7015
時間:
2018-8-15 13:17
回復
10#
ML089
感謝大大的回覆
日期因為臨時填入的，所以重複到了
正常應該是 2007/8/22 : 2007/8/31 沒錯
作者:
ML089
時間:
2018-8-15 14:40
本帖最後由 ML089 於 2018-8-15 14:44 編輯
回復
11#
jackson7015
D48:AG48 為 2007/9/1 : 2007/9/30
BV48:CE48 為
2007/8/22 : 2007/8/31 原檔案要修正
D50格式化公式，擇其一使用
=COUNT(0/(($D$48:$CE$48<=D$48)*($D$48:$CE$48>D$48-7)*($D50:$CE50=CHOOSE(ROW($1:$3),5,6,11))))
=COUNT(0/(($D$48:$CE$48<=D$48)*($D$48:$CE$48>D$48-7)*(($D50:$CE50=5)+($D50:$CE50=6)+($D50:$CE50=11))))
作者:
jackson7015
時間:
2018-8-16 09:26
回復
12#
ML089
感謝 ML089 版主大大的不吝回復
公式可以正常判斷"當月"的部分
測試前月份的更動班表後，沒有連動到當月份的判斷公式
而公式在格式化公式的判斷上也相反(這容易解決)
是否可以再麻煩大大看看，感激不盡
作者:
ML089
時間:
2018-8-16 09:36
回復
13#
jackson7015
測試前月份的更動班表後，沒有連動到當月份的判斷公式
這句不是很懂
作者:
jackson7015
時間:
2018-8-16 10:42
回復
14#
ML089
抱歉，剛剛在重新測試了幾次，發現公式正常無誤
但是因為使用在格式化中要再出現"5.6.11"時是FALSE的判定
所以格式化公示改成
D50=NOT(COUNT(0/(($D$48:$CE$48<=D$48)*($D$48:$CE$48>D$48-7)*($D50:$CE50=CHOOSE(ROW($1:$3),5,6,11)))))
然後又因為無上班時也出現動作，所以只好在格式化條件表中多加了
D50=IF(OR(LEN(D$48)<1,LEN($BU50)<1),TRUE,FALSE)
且在表列中的TURE成立就不再繼續判定下式
再次感謝ML089大大的幫忙
作者:
ML089
時間:
2018-8-16 13:12
回復
15#
jackson7015
$BU50：上月工作天數
D:I 需要上個月資料，J以後就不用了，
格式化條件表中多加了
D50=IF(OR(LEN(D$48)<1,LEN($BU50)<1),TRUE,FALSE)
會不會有反效果，當 LEN($BU50)<1 時，J以後的判斷是否被影響
作者:
jackson7015
時間:
2018-8-16 14:25
回復
16#
ML089
目前測試 J 以後的格式化還沒有出現影響的樣子
因為其他儲存格也有很多資料，還沒出現錯
如果有影響的話就把准提部林 大大的公式拿來用在七天後的
而LEN($BU50)<1 是因為那欄列會放人名，所以用來判斷有無人員及上班日
暫時還沒看出影響..
而剛剛公式上線後，也抓出了有超7休1的人員
感謝ML089版主的幫忙~
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)
