Board logo

標題: 有關行事曆，如何計算當月出勤及假日天數？ [打印本頁]
作者: phoebegin    時間: 2015-7-13 15:26     標題: 有關行事曆，如何計算當月出勤及假日天數？

請教各位先進：
　　我想將行事曆的出勤天數及假日天數以公式方式計算出，請教要如何設定呢？
　　　　[attach]21377[/attach]

　之前看到如何計算儲存格顏色相同的數量?的教學有試用，
　但好像我的行事曆有另外設公式的關係，COUNTIF(A1:Q50,顏色號碼) 卻無法統計，可否請各先進幫忙看一下，是哪裡出了問題∼謝謝∼
作者: ML089    時間: 2015-7-18 10:01

1. 請上傳你的範例檔案，才知道EXCEL內的格式及數植
2. 顏色使用公式不方便統計
3. COUNTIF(A1:Q50,顏色號碼) 此公式不用來統計顏色的，當然會錯
作者: phoebegin    時間: 2015-7-20 11:44

回復 2# ML089

   原來顏色不能直接這麼設定~
   那麼請您幫我看看要如何修改~謝謝您
   目前只能用的方式為=NETWORKDAYS("2016/1/1","2016/1/31")
   [attach]21428[/attach]
作者: ML089    時間: 2015-7-20 12:34

回復 3# phoebegin

I10 公式 =NETWORKDAYS("2016/2/1","2016/2/29")-6，後面-6是特別假日，
建議使用 =NETWORKDAYS("2016/2/1","2016/2/29", holidays)
名稱 holidays 是特別假日日期範圍

使用 NETWORKDAYS 的holidays，用來增加其他特別假日。
1. 這樣你就可以直接用 NETWORKDAYS  計算出工作日，讓公式一致化。
2. 行事曆的黃色日期，可以利用格式化條件來處理。



NETWORKDAYS(start_date,end_date,holidays)

Start_date   係指起始日期。

End_date   係指結束日期。

Holidays   從工作日誌中排除一個或更多個選擇性的範圍日期。
作者: phoebegin    時間: 2015-7-20 13:58

回復 4# ML089


    謝謝您的教導，不過我發現用這樣的方法，不能直接抓B3到H20的假日ex.H3:H7,B4:B8,G3，
   
    holidays的日期要一天一天另外設定嗎？如圖[attach]21432[/attach]
作者: phoebegin    時間: 2015-7-20 14:05

回復 4# ML089


    不好意思∼我懂您的意思了，他可以讓我設定一整年的特別假日，在每個月的工作日設定NETWORKDAYS(start_date,end_date,L9:N13)即可對吧！
　 謝謝您~
作者: ML089    時間: 2015-7-20 21:08

回復 6# phoebegin

對的，
NETWORKDAYS(start_date,end_date,holidays)，中的holidays一般以一欄日期及一欄說明建立，holidays引用的是日期欄，爾且共用公式才能統一。
作者: phoebegin    時間: 2015-7-21 09:24

回復 4# ML089


    再請教您先前指點關於顏色填滿的部份，設定格式化條件，請教要如何設定呢？
作者: ML089    時間: 2015-7-21 13:30

回復 8# phoebegin


1. 行事曆內的日期要改為 日期 顯示 日，你目前是 1,2,3,....
2. 這樣在格式化上使用 NETWORK( 儲存格,儲存格,儲存格,holidays) = 0表示為非工作日就顯示顏色
作者: phoebegin    時間: 2015-7-21 15:21

回復 9# ML089

　原來是用這個角度切入，我完全懂了~謝謝您！
作者: ML089    時間: 2015-7-21 22:56

本帖最後由 ML089 於 2015-7-21 22:58 編輯

回復 10# phoebegin
[attach]21444[/attach]
A3 =DATE(B1,1,1)
A9 =EDATE(A3,1)
B3:H8 {=IF(MONTH(A3)=MONTH(A3-WEEKDAY(A3)+{1,2,3,4,5,6,7}+{0;1;2;3;4;5}*7),A3-WEEKDAY(A3)+{1,2,3,4,5,6,7}+{0;1;2;3;4;5}*7,"")}
I4 =NETWORKDAYS(A3,EOMONTH(A3,0),OFFSET(K$2,,,COUNT(K:K)))
I7 =DAY(EOMONTH(A3,0))-I4
{…} 表示為陣列公式，輸入公式需使用三鍵輸入(CTRL+SHIFT+ENTER三鍵齊按輸入)

EDATE函數
工具\加載宏(繁體版稱增益集)\打勾以下三個選項
1. internet VBA輔助程式
2.VBA分析工具箱
3.分析工具箱

[attach]21445[/attach]

補充
格式化條件 B3
=(B3>0)*(0=NETWORKDAYS(B3,B3,OFFSET($K$2,,,COUNT($K:$K))))
作者: phoebegin    時間: 2015-7-22 12:28

回復 11# ML089


　　謝謝您~您太貼心了，從昨天一直在思考如何修改，您這場及時雨又出現了，
　　　　　　不但秀檔案，知道我目前權限不足還幫我把公式秀出，問題幾乎都解決了∼
　　　　　　現在有狀況的剩最下方設定格式的問題，等權限足夠(快足囉)，再把您的檔案下載來好好研究~
               另外想請教您陣列公式
{=IF(MONTH(A5)=MONTH(A5-WEEKDAY(A5)+{1,2,3,4,5,6,7}+{0;1;2;3;4;5}*7),A5-WEEKDAY(A5)+{1,2,3,4,5,6,7}+{0;1;2;3;4;5}*7,"")}
　　　　　　裡頭{1,2,3,4,5,6,7}代表欄，{0;1;2;3;4;5}代表列嗎？
作者: ML089    時間: 2015-7-22 12:47

回復 12# phoebegin


    裡頭{1,2,3,4,5,6,7}代表欄，{0;1;2;3;4;5}代表列嗎？

YES

{1,2,3,4,5,6,7}代表水平陣列
{0;1;2;3;4;5}代表垂直陣列
作者: phoebegin    時間: 2015-7-28 10:30

回復 13# ML089


    老師~我真的發現自己很鈍，再教教我好嗎？[attach]21542[/attach]

　 另外設定格式條件要全部反白設定，還是一個一個設定呢？（我記得之前的檔是直接全部反白設定）

   再另外∼如果其中一天六或日必須補班（上班），那麼要再如何設定呢？
作者: ML089    時間: 2015-7-28 10:53

回復 14# phoebegin


    設定格式條件要全部反白設定，還是一個一個設定呢？（我記得之前的檔是直接全部反白設定）

全部反白設定

我目前使用2007版，有時全部反白設定有會有問題，我都是先清除選擇範圍內的原先設定再重設
我覺得2003我比較習慣。
作者: ML089    時間: 2015-7-28 10:56

回復 14# phoebegin

如果其中一天六或日必須補班（上班），那麼要再如何設定呢？

這有些麻煩，使用EXCEL的內定公式可能作不到，需要自行定義 補班日期 再加回來。
條件設定也是需要再參考  補班日期 。

幾年前有作過類似，晚上回家再找看看。
作者: ML089    時間: 2015-7-29 22:51

回復 14# phoebegin

加一欄 補班日期

    [attach]21565[/attach]
作者: phoebegin    時間: 2015-7-31 11:40

回復 15# ML089


    老師：
　　　　我全部反白連其他格式都變成，="(B3>0)*(0=NETWORKDAYS(B3,B3,OFFSET($X$3,,,COUNT($X:$X))))"[attach]21584[/attach]

　　　　而且在其他假日儲存格修改，雖然已改成功，但還是不會填滿顏色[attach]21590[/attach]！

　　　　還有會變成雙引號，現在才發現原來我和EXCEL這麼不熟！:'(
作者: ML089    時間: 2015-7-31 12:32

回復 18# phoebegin

複製公式時，或填入公式時前面有空白
須手工將雙引號去除
作者: phoebegin    時間: 2015-7-31 15:53

回復 19# ML089


    當我手動刪除雙引號時[attach]21591[/attach]，他說我參照到其他工作表
   
    當我連同=號和"號一起刪除時，它雙引號又跑出來見我了∼

    [attach]21592[/attach]
作者: ML089    時間: 2015-7-31 16:07

本帖最後由 ML089 於 2015-7-31 16:10 編輯

回復 20# phoebegin

先將格式設定全部清除後，重新設定。

格式公式錯誤，改為
=(B3>0)*(0=NETWORKDAYS(B3,B3,OFFSET($T$3,,,COUNT($T:$T))))

設定選取範圍 B3:H38

再複製B3:H38格式貼至K3:Q38
作者: phoebegin    時間: 2015-8-4 14:09

回復 21# ML089


    老師~
       我真的有那種覺得自己是智障的感覺~
       可否請老師用錄影的方式教我，讓我看看我到底是哪個環節出錯了？:L
作者: ML089    時間: 2015-8-4 17:39

回復 22# phoebegin

[attach]21648[/attach]
作者: phoebegin    時間: 2015-8-12 11:15

老師我回來了~最近太忙~一直無法回報~

您的步驟非常清楚，但我的是2003版~好像沒有那麼先進，所以目前還在奮戰當中~我再慢慢試~謝謝您~



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