作者pointchi (pointchi)
看板Office
標題[算表] 儲存格排除休假日後帶入資料
時間Thu Dec 3 10:24:12 2020
軟體:Excel
版本:2016
請教,我有一個檔案如下附圖,已經將整年份的日期標示以及星期六(綠色字體)、
星期日與國定假日(都是紅色字體)設定格式化條件。
https://i.imgur.com/Esxc67p.jpg
https://i.imgur.com/0Yroyvm.jpg
問題
1﹐我如何於起頭01/02輸入A後(表格旁邊的組別),接下來分別於上班日依序帶
入B.C.D...一直循環到月底,接著02/03延續上月末輸入F後,也是循環帶入
A.B.C.....
或者甚至是01/02輸入A後接續一直到12月可以一次全部帶入A.B.C...循環。
2﹐02/15為例是補班日,有辦法於設定格式化條件排除,然後上述的規則
帶入D
檔案連結如下:
https://reurl.cc/A8rNvp
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 118.168.96.105 (臺灣)
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1606962258.A.EEF.html
1F:→ soyoso: 補班日排除,加上and(*),以countif,補班日d欄,計數為0 12/03 10:31
2F:→ soyoso: 循環上以函數mod,先判斷日期是否為空字串後,再判斷日期 12/03 11:19
3F:→ soyoso: 是否為六日weekday、是否是國定休假日countif為0或match為 12/03 11:19
4F:→ soyoso: 錯誤、或是否為補班(類似國定休假日countif大於0、match為 12/03 11:19
5F:→ soyoso: 數字),mod的number就要看先前組別的個數,二個1.公式上方 12/03 11:19
6F:→ soyoso: 列b:af組別個數,2.公式所在組別A欄起,到公式所在左側儲 12/03 11:19
7F:→ soyoso: 存格的個數,這裡的個數為非<>空字串"",加上定位(組別是 12/03 11:19
8F:→ soyoso: 哪一個開始,儲存格c1)以match 12/03 11:19
回S大,小的研究您上面的說明一周了,奈何還是搞不定,
資質不夠,是否可以明示範例~感恩。
※ 編輯: pointchi (118.168.9.233 臺灣), 12/08/2020 10:51:53
9F:→ soyoso: 搞不定的話,提供檔案,看是哪個部分要調整的 12/08 11:17
回S大,檔案連結短址為:
https://reurl.cc/A8rNvp
感謝您。
※ 編輯: pointchi (118.168.9.233 臺灣), 12/08/2020 11:34:15
10F:→ soyoso: 沒有看到搞不定的部分在哪個儲存格?所以也就沒有辦法給原 12/08 11:39
11F:→ soyoso: po要調整的了 12/08 11:39
12F:→ soyoso: 要提供的是已寫公式,但無法達成的檔案,而不是提供同內文 12/08 11:40
13F:→ soyoso: 連結的檔案 12/08 11:40
14F:→ soyoso: 如果是內文檔案那要達成的邏輯我已經寫在12/03的回文內了 12/08 11:41
回S大,抱歉沒有說清楚。
目前我將B1儲存格設定格式化條件:
星期六(綠色字體)、星期日與國定假日(都是紅色字體),且補班日
(黑色字體,這個前幾天搞定,當時腦筋太死沒有轉過來,就跟國
定假日設定是一樣的)設定格式化條件,並將套用至其餘有標示日
期的欄位。
我於AG欄先建立好組別代號A.B.C.D.E.F,
然後於每月第一個工作日輸入組別(以C2儲存格為例 輸入A),
接下來於儲存格D2.E2.F2...我要如何設定公式,D2.E2.F2...
會排除週六、日與國定假日後依序帶入組別代號B.C.D...
這個是完全無頭緒。
※ 編輯: pointchi (118.168.15.23 臺灣), 12/09/2020 11:18:57
15F:→ soyoso: 1.判斷什麼條件下回傳空字串、什麼條件下循環組別,看日期 12/09 11:42
16F:→ soyoso: 1.1 是否空白儲存格,如2/29的右二格 12/09 11:42
17F:→ soyoso: 1.2 是否為六、日(函數weekday) 12/09 11:42
18F:→ soyoso: 1.3 是否在國定補班日內(函數countif或match) 12/09 11:42
19F:→ soyoso: 1.4 是否在國定休假日內(函數countif或match) 12/09 11:42
20F:→ soyoso: 1.5 上述match看是以數字(函數isnmuber)或是錯誤值 12/09 11:42
21F:→ soyoso: (函數isna)為真為假就自行調整 12/09 11:42
22F:→ soyoso: 2.循環組別(函數mod,mod的number為) 12/09 11:42
23F:→ soyoso: 2.1 公式上方b:af組別個數,就是排除日期(數字)和 12/09 11:42
24F:→ soyoso: 非空字串,二個條件,函數(sumproduct、 12/09 11:42
25F:→ soyoso: 排除日期可以istext、iseven或isodd看row列號) 12/09 11:42
26F:→ soyoso: 2.2 加上公式左側a欄起的組別個數,就是非空字串, 12/09 11:42
27F:→ soyoso: 一個條件,邏輯值要運算或函數value 12/09 11:42
28F:→ soyoso: 2.3 再加上是從誰開始的,就以儲存格c1去match組別ag:ag 12/09 11:42
29F:→ soyoso: 3.對應組別 12/09 11:42
30F:→ soyoso: 3.1 循環組別的用意是,看2.3從誰開始,如A(1起的話) 12/09 11:42
31F:→ soyoso: 1,2,3,4,5,6,1...,那C(3起的話)3,4,5,6,1,2,3.... 12/09 11:42
32F:→ soyoso: 3.2 再以這個數值看要配合函數index、offset或是indrect 12/09 11:42
33F:→ soyoso: 來回傳組別實際對應的值 12/09 11:42