maiko

帖子: 96
主題: 29
精華: 0
積分: 129
點名: 0
作業系統: win
軟體版本: 7
閱讀權限: 20
註冊時間: 2011-3-8
最後登錄: 2024-8-14

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2017-1-13 14:21 | 只看該作者

[發問] 怎樣用函數把每八個星期為一個循環同時變換狀態的條件格式化？

本帖最後由 maiko 於 2017-1-13 14:23 編輯

請問在 J2和 K2選擇條件下，如何用函數完成以下條件格式化的要求？

在 B2:H65範圍內，每八個星期就一個循環，換一次主休日，而如果選擇了小息是：9.6 的話，那麼第1、3、5、7 星期就會在主休日後面加多一天（如圖黃色所示），反之選擇了小息是：8 的話，那麼第2、4、6、8 星期就會在主休日後面加多一天。

＊如果到主休日是星期日換星期一的話，那麼原本單休只有星期日換到星期一也是單休（不會是之前的那樣一個星期單休，一個星期雙休），也就是每 56 個星期以後，本來第 1、3、5、7 星期是雙休，現在要改成單休；本來第 2、4、6、8 星期是單休，那麼要改成雙休。

以此類推，每過 56 個星期以後也要把這規律倒序。

註：如果輪到主休日是星期天的話，那附加的一天會是星期一（如圖 B50:H57 的黃色所示）

RCondition_Fixa.rar (13.74 KB)

樓主熱帖

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

2^#

發表於 2017-1-14 07:29 | 只看該作者

本帖最後由 ML089 於 2017-1-15 05:00 編輯

格式化條件公式

主休(不可以選)
B2=MOD(INT((B2-$B$2)/56),7)=WEEKDAY(B2,3)

小息 (可以選)
B2 =N(MOD(INT((B2-$B$2)/56)+1,7) = WEEKDAY(B2,3)) *(MOD(INT((B2-$B$2)/7),2)=IF($K$2=8,1,0))
簡化少一個MOD

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

3^#

發表於 2017-1-15 05:13 | 只看該作者

格格式化公式 B2

主休 (可調選 J2)
B2 =N(MOD(INT((B2-$B$2)/56)+MATCH($J$2,$B$1:$H$1,)-1,7)=WEEKDAY(B2,3))
WEEKDAY(B2,3)是星期一由0開始
56是8周天數(7*8)

小息 (可調選 K2)
  9.6 時，在第 1、3、5、7 星期的主休日後面加多一天
  8 時，在第 2、4、6、8 星期的主休日後面加多一天
  注意，星期日時是該周星期一休
B2 =N(MOD(INT((B2-$B$2)/56)+MATCH($J$2,$B$1:$H$1,)-0,7)=WEEKDAY(B2,3))
*(MOD(INT((B2-$B$2)/7),2)=IF($K$2=8,1,0))

RCondition_Fix 主休小息每八個星期就一個循環.rar (67.18 KB)

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

maiko

中學生 Rank: 2

帖子: 96
主題: 29
精華: 0
積分: 129
點名: 0
作業系統: win
軟體版本: 7
閱讀權限: 20
註冊時間: 2011-3-8
最後登錄: 2024-8-14

4^#

發表於 2017-1-16 14:14 | 只看該作者

格格式化公式 B2

主休 (可調選 J2)
B2 =N(MOD(INT((B2-$B$2)/56)+MATCH($J$2,$B$1H$1,)-1,7)=WEEKDA ...
ML089 發表於 2017-1-15 05:13

謝謝提供這樣的思路，不過不好意思，到主休日是星期日的時候，那麼下個星期主休日應該是換星期一，而且是單休，不是雙休。(如圖紅圈所示)

這樣計算的話，每56個星期就要換主休日一次，如題要求的那樣。

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 2017-1-16 23:39 | 只看該作者

回復 4# maiko

星期日主休接下周星期一主休時，若小息是單周休時就會造成三天連假，若小息是雙周休時就不會造成三天連假，這與52周循環沒有關係。
這部分需求有些奇怪?

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

maiko

中學生 Rank: 2

帖子: 96
主題: 29
精華: 0
積分: 129
點名: 0
作業系統: win
軟體版本: 7
閱讀權限: 20
註冊時間: 2011-3-8
最後登錄: 2024-8-14

6^#

發表於 2017-1-17 08:30 | 只看該作者

本帖最後由 maiko 於 2017-1-17 08:33 編輯

回復 5# ML089

是這樣要求的，每次都在主休是星期日換星期一的時候，如果是雙周的話，那麼星期一也是雙周（這時才有機會連續三天），反之如果是單周的話，那麼星期一也是單周（這時只有連續兩天）。
確實是跟56個星期無關，只是每次都在星期日換星期一的時候，這樣算起來就隔了 7 * 8 = 56 個星期了。

謝謝! 請多費思量一下，這樣的話是怎麼達成的?

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

7^#

發表於 2017-1-17 09:09 | 只看該作者

回復 6# maiko

>>是這樣要求的，每次都在主休是星期日換星期一的時候，如果是雙周的話，那麼星期一也是雙周（這時才有機會連續三天），反之如果是單周的話，那麼星期一也是單周（這時只有連續兩天）。
>>確實是跟56個星期無關，只是每次都在星期日換星期一的時候，這樣算起來就隔了 7 * 8 = 56 個星期了。

>>謝謝! 請多費思量一下，這樣的話是怎麼達成的?

小息是雙周休的沒有此問題
小息是單周休的在主休為星期日改為星期一那八周全部改為雙周休嗎? 再過 56 個星期再改為單周休時還是有此問題(連休三天)?
問題是否改為主休為星期日改為星期一那八周小息全部為雙周休，其他不變這樣嗎?

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

maiko

中學生 Rank: 2

帖子: 96
主題: 29
精華: 0
積分: 129
點名: 0
作業系統: win
軟體版本: 7
閱讀權限: 20
註冊時間: 2011-3-8
最後登錄: 2024-8-14

8^#

發表於 2017-1-17 13:54 | 只看該作者

回復 7# ML089

>>小息是雙周休的沒有此問題
>>小息是單周休的在主休為星期日改為星期一那八周全部改為雙周休嗎? 再過 56 個星期再改為單周休時還是有此問題(連休三天)?
>>問題是否改為主休為星期日改為星期一那八周小息全部為雙周休，其他不變這樣嗎?

我這樣解釋一下看看：
本來就是一個星期雙周，一個星期單周，這樣８個星期就會改變主休日（如果是星期一的話，那就改成星期二，而雙周就順延星期二、三這樣）
每過８個星期就改變主休日，一直改到星期日，而這時如果主休是星期日的第８個星期的話，而又是單周，那麼下個星期主休就會改成星期一，這時候還是單周
因為只有在主休是星期日要改成星期一的時候，小息才會倒序，然後就一個星期單周、一個星期雙周這樣。
再到下一次主休是星期日要改成星期一的時候，那麼小息再次倒序，就是一個星期雙周、一個星期單周這樣。

如：
雙周
單周
雙周
單周
……
雙周
單周（星期日）
單周（星期一）
雙周
……
單周
雙周（星期日）
雙周（星期一）
單周
……

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

9^#

發表於 2017-1-17 16:37 | 只看該作者

回復 8# maiko

1. 小息全部排在雙周就可以決解問題

2. 一年一次連續三天，這會有甚麼影響? 還不是每8周休8+4天

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

maiko

中學生 Rank: 2

帖子: 96
主題: 29
精華: 0
積分: 129
點名: 0
作業系統: win
軟體版本: 7
閱讀權限: 20
註冊時間: 2011-3-8
最後登錄: 2024-8-14

10^#

發表於 2017-1-17 17:01 | 只看該作者

回復 9# ML089

>>1. 小息全部排在雙周就可以決解問題

>>2. 一年一次連續三天，這會有甚麼影響? 還不是每8周休8+4天

我想不是說自己要怎樣安排就怎樣安排，是不是？

程序是要這樣做的，你可以從一個星期一個星期的角度去看看，那麼所謂的連續三天就不存在了。

而且你認為這樣的連續三天其實並不是一年一次，而是大概兩年一次，因為只有在雙周改雙周的時候才出現，單周改單周不會出現連續三天的。

其實最後的重點就是，這樣的程序做法，應該要怎樣實現呢？還是請高手們賜教，謝謝！

TOP