查看單個文章
舊 2006-09-21, 07:47 AM   #41 (permalink)
psac
榮譽會員
 
psac 的頭像
榮譽勳章
UID - 3662
在線等級: 級別:30 | 在線時長:1048小時 | 升級還需:37小時級別:30 | 在線時長:1048小時 | 升級還需:37小時級別:30 | 在線時長:1048小時 | 升級還需:37小時級別:30 | 在線時長:1048小時 | 升級還需:37小時級別:30 | 在線時長:1048小時 | 升級還需:37小時
註冊日期: 2002-12-07
住址: 木柵市立動物園
文章: 17381
現金: 5253 金幣
資產: 33853 金幣
預設

Excel讓表頭自動填寫當前月份和日期
假設我們要做一個標題為「某公司6月份電費統計表」的Excel表格,其中「6」為進行電費統計的當月月份,第二行有製表日期。

  電費每月公佈一次,那麼每月打開此表修改編輯時,都要對標題中的月份和製表日期進行修改,萬一只改動了表內資料,而忘記以上兩個時間的改動,會出現表頭時間與表內資料的矛盾。

  我們採取一個一勞永逸的措施來解決這個問題,任何時候打開此工作表,都讓表頭自動填寫當前月份和日期。這要借助Excel三個函數來實現,在標題單元格輸入「="實驗中學"&& MONTH(TODAY())&&"月份電費統計表"」,函數「MONTH(TODAY())」表示當前月份,在第二行製表時間單元格輸入「=TODAY()」。函數「TODAY()」表示當前日期,就出現圖中結果。

  完成上述設置,任何時候打開該表,我們大可放心地去做表內資料的處理工作了,不必擔心表頭時間會出現什麼差錯,無需對表頭做任何改動。

辦公技巧:巧用Office Excel自動工作日計算


工作量當然得以「工作日」的數量去安排,但每個月的天數不同,而且週六日也不盡相同。如果想準確地知道一個月有多少個工作日,可以請Excel[/b]來幫忙!
小知識
標準工作日是指法律規定的各企業、事業、機關、團體等單位在正常情況下普遍實行的工作日。通俗的說,就是指除了國家法定節假日(雙休日、元旦、五一、國慶、春節等)之外正常工作的日期。
任務分析
因為每月的總天數和雙休日的分佈是不同的,而且春節是農曆[/b]的假期,簡單地使用Excel[/b]函數去計算是滿足不了要求的。我們先使用Excel[/b]「分析工具庫」中的networkdays()函數計算出「准工作日」,再考慮其中的特殊農曆[/b]假日。這樣就能準確的算出「工作日」的天數。
有請 networkdays()函數
通常情況下,在Excel[/b]中是找不到這個函數的。別著急,跟我來。單擊「工具→載入巨集」,在彈出的「載入巨集」交談視窗中復選「分析工具庫」,再單擊「確定」按鍵。將Office的安裝光碟放入光碟,按提示即可安裝成功,如圖1。
http://article.pchome.net/00/09/67/64/Img242057077.jpg
外來的和尚念不好經
networkdays()函數的語法為:networkdays(Start_date,End_date,Holidays)。其中Start_date表示開始日期,End_date為停止日期,Holidays表示一個或者多個特定假日序列,可以採用單元格引用的方式。
如圖2,B8單元格公式「=networkdays(B2,B3,B4:B6)」結果為20。事實上,2004年的10月2日和3日是星期六和星期天,那麼按照我們的習慣就會通過調休的方式,最終得到10月份只有18天上班,而不是networkdays()函數計算出的20天。
特殊假日的處理
雖然說直接應用networkdays()函數並不能得到正確的工作日天數,但我們還是可以通過對此函數的變化來得到。圖3是我們的範例,演示了如何一步一步逼近我們的目標。
第一步:將A列定義為月份。選中A列,在右鍵表菜單中選擇「設定單元格格式」,在「數位」標籤中選擇「分類」為「自定義」,「類型」中輸入「yyyy-mm」格式,單擊「確定」按鍵退出。
第二步:設計B列公式。先直接用networkdays()函數計算只考慮本月除去雙休日後的天數。雖然可直接使用該函數,但需要知道每月的開始日期和停止日期。開始日期當然是每月的1日,用「date(year(A2),month(A2),1)」表示就OK了。結束日期應當是每月的最後一天,這就有些難度了,究竟是30日、31日還是28日、29日?需要一個複雜的判斷關係。這裡我們採用了一個變通的辦法,就是當月的最後一天其實就是下月的第一天再減去1天,所以我們可以用「date(year(A2),month(A2)+1,1)-1」來表示。於是B2的公式就有了:「=networkdays(date(year(A2),month(A2),1),date(year(A2),month(A2)+1,1)-1)」。
http://article.pchome.net/00/09/67/64/Img242057078.jpg
http://article.pchome.net/00/09/67/64/Img242057079.jpg
第三步:計算除了春節以外的雙休日。我們知道國家規定的公眾假日一共10天,除了春節的3天是每年變化的外,其餘元旦1天,勞動節3天,國慶節3天都是固定的。因此我們可以用IF函數對月份是否是1月、5月和10月做判斷,再來減去相應的公眾假日天數,就得到了不考慮春節以外的工作日。於是C2=if(month(A2)=5,B2-3,if(month(A2)=5,B2-3,if(month(A2)=1,B2-1,B2)))或者使用or()函數就是C2=if(or(month(A2)=5,month(A2)=10),B4-3,if(month(A2)=1,B4-1,B4))。
第四步:考慮春節的問題。根據常識,春節的三天只會在1月份或者2月份出現,也就是說最後的判斷只正對1、2月份。通過萬年曆查詢得到2000——2010這十年的春節分佈,巧的是正好沒有春節三天跨月份的年份,也就是說春節三天不是落在1月份就是落在2月份,如圖4所示,這給我們應用IF函數判斷帶來了方便。通過對年份和月份的判斷,減去相應的春節天數,就得到了真正意義上的工作日。因此在D2中應該輸入=IF(AND(OR(YEAR(A2)=2001,YEAR(A2)=2004,YEAR(A2)=2006,YEAR(A2)=2009),MONTH(A2)=1),C2-3,IF(AND(OR(YEAR(A2)=2000,YEAR(A2)=2002,YEAR(A2)=2003,YEAR(A2)=2005,YEAR(A2)=2007,YEAR(A2)=2008,YEAR(A2)=2010),MONTH(A2)=2),C2-3,C2))。
http://article.pchome.net/00/09/67/64/Img242057081.jpg
第五步:隱藏過渡列。選中「B:C」這兩列,在右鍵表菜單中選擇「隱藏」,將中間用於計算方便所使用的過渡列B和列C隱藏即可。
選中區域B22,按住填充柄向下拖動填充後,只要在A列任一單元格輸入月份,就可以在D列得到該月相應的天數,是不是很方便?
__________________
http://bbsimg.qianlong.com/upload/01/08/29/68/1082968_1136014649812.gif
psac 目前離線  
送花文章: 3, 收花文章: 1625 篇, 收花: 3196 次