2012年10月31日 星期三

搞定Excel 日期時間相關計算--【七】日期相關函數(五)WEEKDAY、WEEKNUM

WEEKDAY(日期,類型代碼)

依「類型代碼」傳回「日期」是星期幾的數字值。

類型代碼:

1或省略星期日=1.....星期六=7
2星期一=1.....星期日=7
3星期一=0.....星期日=6

WEEKNUM(日期,類型代碼)

依「類型代碼」傳回「日期」是當年度第幾週週數。
WEEKNUM 函數將包含一月 1 日的那一週視為一年當中的第一週。然而,歐洲標準是將大部分天數 (四天以上) 落在新一年中的那一週定義為一年中的第一週。因此,根據歐洲標準,對於只有三天以下是處於一月份第一週的年份來說,WEEKNUM 函數傳回的週編號會是錯誤的。

類型代碼:

Excel 2007

Excel 2010


2012年10月30日 星期二

搞定Excel 日期時間相關計算--【六】日期相關函數(四)NETWORKDAYS、NETWORKDAYS.INTL

之前在「「隱藏版」函數Datedif()」一文中介紹過計算兩個日期之間相差多少天、月、年....的函數。但是!如果只要算相差多少個「工作日」呢?....頭大了吧!

NETWORKDAYS(日期1,日期2,假日列表)

最後一個函數可以先忽略(下段詳述),將會傳回第1個引述(日期)與第2個引數(日期)之間扣除週六、日之後的日數(也就是工作日啦!)。

但是....如果有些假日不是在週六、日呢?

這時候第三個引數就派上用場啦!使用者可以將假日的日期列成一張獨立清單(Excel 2007之後建議格式化成表格,方便日後增補時可以自動計算)

實作範例:

在這裡順便呼籲一下:那個「人事行政局」啊!別再搞這種「行事曆」啦!
至少....這種表格....也別用Excel 啦!Excel會哭哭的....

NETWORKDAYS.INTL(日期1,日期2,自訂週休參數(數值或文字),假日列表)

Excel 2010新增函數。功能跟NETWORKDAYS一樣,多了一個參數可以自訂固定周休日。如果不是休週六、週日的使用者可以更有彈性。
這個自訂週休參數有數值及文字兩種設定法:

自訂週休參數(數值):

代碼
固定休日
1 或省略
星期六,星期日
2
星期日,星期一
3
星期一,星期二
4
星期二,星期三
5
星期三,星期四
6
星期四,星期五
7
星期五,星期六
11
僅星期日
12
僅星期一
13
僅星期二
14
僅星期三
15
僅星期四
16
僅星期五
17
僅星期六

自訂週休參數(文字):

為 7 個字元長度的字串,而字串中的每個字元代表一週中的某一天,時間則是從星期一開始。1 代表非工作日,而 0 代表工作日。字串中只能使用字元 1 和 0。使用 1111111 將永遠傳回 0。例如,0000011 會產生週末,也就是星期六和星期日。
直接放在函數中,記得加「""」喔!

2012年10月29日 星期一

搞定Excel 日期時間相關計算--【五】日期相關函數(三)EDATE、EOMONTH


上一篇的應用範例可以算出「從某日期算起幾年、幾個月、幾天前後是那個日期」。但是,運算式有點給他複雜了一點點...比較簡單的函數也有....

EDATE(起算日期,月數)

傳回自起算日期算起幾個月後(前)的日期值。
當然啦!要算幾年幾個月....請自行計算出總月數。
(注意圖右的結果,並不會因為2月只有28天而跳到3月去喔!)

EOMONTH(起算日期,月數)

傳回起算日期算起幾個月後(前)的那個月的最後一天的日期值。

計算月初日期容易,計算月底可就難了一點點。以前黑輪都是算多一個月的月初,再減一天。看到這個函數真是....相見恨晚啊!

2012年10月28日 星期日

搞定Excel 日期時間相關計算--【四】日期相關函數(二)


DATE(年,月,日):傳回特定日期的序列值

三個引數分別輸入:年、月、日,傳回日期值,這函數相信大家都清楚。但是你也許會太為Excel 想太多,以為輸入的引數都得符合固定的格式。(例如:以為第二個引數要在1~12之間)其實你過慮了!DATE()函數會幫你自動進位的:
DATE(2012,13,25)→傳回的是:2013/1/25。真的不用想太多....

應用範例:

E1輸入訂單日期,E2輸入預計交貨期限,請在E3計算出預計交貨日期?


TIME(時,分,秒):傳回特定時間的序列值

三個引數分別輸入:時、分、秒,傳回時間值,一樣會自動進位喔!
TIME(12,65,30)→傳回的是:13:05:30


2012年10月27日 星期六

搞定Excel 日期時間相關計算--【三 】日期相關函數(一)

在此先列出所有日期相關函數:

函數
引數
傳回資料類型
描述
(數值),(數值),(數值)
日期
傳回特定日期的序列值
DATEVALUE
能被視為日期的字串
日期
將文字形式的日期轉換為序列值
DAY
日期
數值
將序列值轉換為月份中的日
DAYS360
日期1,日期2,是否用歐式算法(邏輯值)(省略為否)
數值
按每年 360 天計算兩個日期之間的天數
開始日期,月數(數值)
數值
傳回日期的序列值,這是在開始日期之前或之後所指出的月份數
開始日期,月數(數值)
日期
傳回所指定月份數之前或之後的月份最後一天的序列值
HOUR
時間
數值
將序列值轉換為小時
MINUTE
時間
數值
將序列值轉換為分鐘
MONTH
日期
數值
將序列值轉換為月份
日期1,日期2,假日陣列
數值
傳回兩個日期之間所有工作日的數目
日期1,日期3,自訂休參數(數值或文字),假日陣列
數值
使用參數指出哪幾天和多少天是週末,以計算兩個日期之間的所有工作日
NOW
無引數
日期時間
傳回目前日期及時間的序列值。
SECOND
時間
數值
將序列值轉換為秒
(數值),(數值),(數值)
時間
傳回特定時間的序列值
TIMEVALUE
能被視為時間的字串
時間
將文字形式的時間轉換為序列值
TODAY
無引數
日期
傳回今天日期的序列值
日期,傳回值類型(12)
數值
將序列值轉換為星期
日期,傳回值類型(13)
數值
將序列值轉換為表示某一是一年當中第幾的數字
WORKDAY 
開始日期,工作天數,假日陣列
日期
傳回指定的工作日數目之前或之後,日期的數列值
WORKDAY.INTL 函數
開始日期,工作天數,自訂休參數(數值或文字),假日陣列
日期
使用參數指出哪幾天和多少天是週末,以傳回在指定的工作日數之前或之後的日期序列值
YEAR 函數
日期
數值
將序列值轉換為年
YEARFRAC 函數
日期1,日期2,計算類型(0,1,2,3,4)
數值
傳回代表在 start_date end_date 之間所有日期數字的年份分數
後續再詳細一一介紹幾個較複雜函數的詳細做法......

2012年10月26日 星期五

搞定Excel 日期時間相關計算--【二】輸入日期時間


  • 輸入日期:年、月、日之間可以用「/」或「-」隔開。
    例如:「2012/10/26」、「2012-10-26」
  • 可省略年份,會自動識別為系統時間的年份。
  • 輸入時間:時、分、秒之間用「:」隔開。
    例如:「10:25:30」
    • 12時制則在時間後面空一個空白再輸入「AM」「PM」區分上午及下午。
      例如:「10:25:30 PM」
  • 可省略「秒」
  • [ Ctrl ]+[ ; ] 快速輸入今天日期
  • [ Ctrl ]+[ Shift ]+[ ; ]  ([Ctrl]+[ : ]) 快速輸入現在時間
  • 年份只輸入二位數字,會被解譯為1930~2029年之間的年份。
    (這是作業系統的設定,可以在「控制台→地區及語言選項→中修改。)
  • 如果要以民國年輸入,可以在年份前加「r」。會自動解譯為西元年份。(如果這儲存格的數值格式為「通用格式」則會自動套用民國年格式)

2012年10月25日 星期四

搞定Excel 日期時間相關計算--【一】日期時間的真相

Excel中的日期相關計算一直讓人很頭痛。到處尋求「密技」....
其實。要搞定日期,要先認識他的本質:「日期時間」是數值資料以日期格式顯示而已!
日期怎麼會是數值呢?其實只要在儲存格中輸入一個日期(例如:2012/10/25),再把儲存格的數值格式改為「通用格式」,將會發現日期變成一個數值(41207)。這就是日期資料的本質。

再試試在原本放置日期的儲存格中:

  • 改放「 1 」,你將會發現儲存格顯示的是「1900/1/1」(儲存格修改內容,並不會修改儲存格的格式)。也就是說:「 1 = 1900/1/1 」。
  • 改放「 2 」,你將會發現儲存格顯示的是「1900/1/2」。
  • 改放「 2.5 」,並將儲存格數值格式改成「時間」中的日期帶24時制時間(格式代碼:yyyy/m/d h:mm)你將會發現儲存格顯示的是「1900/1/2 12:00」。

所以日期的真相是:

  • 日期是數值資料。
  •  1 = 1900/1/1
  •  1 = 1天
  •  1 = 24小時 
所以說,當我們要計算兩個日期相差多少天。最簡單的方法就是將兩個日期相減即可。
P.S.要算出相差多少 年、月、日....請參考「「隱藏版」函數Datedif()

2012年10月24日 星期三

學會樣式,就可以做目錄啦!

就如同先前「如果你不會「樣式」,抱歉!你不算會用Word。」一文當中說的:「如果你不會用「樣式」,你一定不會做「目錄」。」
那現在會做樣式了,就該來談談「怎麼做目錄了」...
「目錄」在Word的設計中,是利用功能變數來產生目錄。也就是說用程式自動跑出來,決不是自己手工打造(那....多累啊!....)
然而,程式要怎麼知道那些「段落」要拿來當作「目錄內容」呢?....
當然是標題呀!
那又怎麼知道那些段落是「標題」?.....
在「幾個與樣式相關的格式--【一】大綱階層」一文中有提到用「大綱階層」的設定來標定那些段落是「標題」。
而一般來說,我們通常把「大綱階層」的格式設定在樣式中。再以樣式來區分各階層的標題。所以我才說:「如果你不會用「樣式」,你一定不會做「目錄」。」

如何建立目錄:

  1. 先準備好各階層標題所需的樣式。
    (當然也可以使用「空白文件範本」預設的「標題1~9」的樣式)
  2. 將文字游標放在樣放目錄的地方。
    (目錄雖然通常在文件最前面,但卻不一定在固定位置。所以要先放文字游標,來決定要放置的位置。)
  3. 「參考資料」(功能區頁標籤)→目錄→建立目錄



  4. 在目錄對話框中....
P.S.按【選項】鈕,進入目錄選項。可以手動設定目錄的階層,將為設定大綱階層的樣式也納入目錄階層中。

完成結果:


2012年10月23日 星期二

一個小秘訣搞定惱人的「多層次清單」(大綱編號)

「多層次清單」,舊版本(Word 2003以前)叫做「大綱編號」。
就是那個像是我們「小時候」數學課本看到的:
第1章 xxxxxxx
1-1 xxxxxxx
1-1-1 xxxxxxxx
1-1-2 xxxxxxxx
1-2 xxxxxxx
1-2-1 xxxxxxxx
1-2-2 xxxxxxxx
第2章 xxxxxxx
2-1 xxxxxxx
2-1-1 xxxxxxxx
2-1-2 xxxxxxxx
2-2 xxxxxxx
2-2-1 xxxxxxxx
2-2-2 xxxxxxxx


這種有「層級」的編號方式。
通常學Word的人都曾經想試一下.....最常見的結果都是「放棄!我自己打好了....」。因為....她總是不聽話!
其實只要掌握一個小秘訣,保證她乖乖聽話!

2012年10月22日 星期一

幾個與樣式相關的格式--【三】與前段距離、與後段距離

相信大多數人平常都是用一個空的段落來分隔兩個段落,讓段落與段落之間保持一點「距離」。但是段落中的格式中明明就有「與前段距離」「與後段距離」可以設定,為何棄之不用呢?
答案應該都是:按Enter比較快啊!
學會用樣式後,這個答案應該不一樣了吧!
這一篇寫到這裡.....應該就可以了吧!

2012年10月21日 星期日

幾個與樣式相關的格式--【二】分行與分頁設定

段落格式對話框的「分行與分頁設定」這一頁,我想大家應該孰悉卻又陌生吧!
這一頁的段落格式簡單的說法:設定當段落遇到分頁位置時所需要遵守的「規則」,而Word為了符合這些規則所採用的處理方式只有一種:就是「向後推移」。
以下簡單介紹「分頁」的四種規則:

  • 段落遺留字串控制:設定此格式的段落,雖然可以在段落中被分頁,但是其結果絕對不能只有一行在某一頁,而其他部分在另一頁。也就是必須有兩行以上在同一頁。
  • 與下段同頁:設定此格式的段落,必須與下一段在同一頁。也就是設定此格式的段落不能是該頁的最後一個段落。(通常是當作標題的段落或設定此格式,以避免標題下的說明內容在另一頁的尷尬情況。)
  • 段落中不分頁:設定此格式的段落,不能在對落中被分頁。已就是此段落的每一行都必須在同一頁。
  • 段落前分頁:設定此格式的段落,在段落前面會自動分頁(無須分頁符號)。也就是說次段落必是某頁的第一個段落。
像這類的「規則」格式,當然不能等到遇到狀況才設定。所以通常是將此種格是設定在各種段落樣式之中。使得套用該樣式的段落在遇到分頁位置時,將自動調整來符合這些規則。

2012年10月20日 星期六

幾個與樣式相關的格式--【一】大綱階層

介紹了樣式的建立、套用與修改之後。接著來聊聊幾個通常在樣式中才會使用(因為如果在段落中設定,並不容易表現他的方便性。)
這些格式配合樣式來使用,將會讓你深深感受到Word是一個「功能強大且貼心」的「偉大軟體」

大綱階層

定義段落在文件中是屬於哪一層級的「標題」或只是一般敘述的「本文」。用來讓軟體知道文的架構組成。
一般長一點的文件,通常會分「章」,章之下再分「節」....。這便是文章的架構!
通常我們會在每一章、節之前加上當作「標題」的段落。在Word中是利用「大綱階層」這個屬於段落的格式來表現。設定段落的「大綱階層」的值為「階層1」~「階層9」來設定段落是屬於哪個層級的「標題」;「本文」則是非標題的段落(所以預設是本文。)

範例(下列引文中括弧部分為大綱階層的層級)

這是第一章的標題(階層1)
這是第一章的內容,第一章的內容,第一章的內容,第一章的內容,第一章的內容,第一章的內容,第一章的內容。(本文)
這是第一章第一節(階層2)
內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容 (本文)
這是第一章第二節(階層2)
內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容 (本文)
這是第二章的標題(階層1)
這是第二章的內容,第二章的內容,第二章的內容,第二章的內容,第二章的內容,第二章的內容,第二章的內容。(本文) 
這是第二章第一節(階層2)
內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容 (本文)
這是第二章第二節(階層2)
內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容內容(本文)
大綱階層如果是設定在段落上,實在不方便。以上例來說,如果能夠建立一個樣式:「章標題」供每一章的標題段落套用; 建立另一個樣式:「節標題」供每一節的標題段落套用。
那我們只需在「章標題」中定義段落的「大綱階層為階層1」; 「節標題」中定義段落的「大綱階層為階層2」

空白文件範本中的樣式已預先設定好大綱階層

一般Word的使用者都是沒有選擇「範本」來開啟新文件,其實都是從「空白文件範本」(Normal.dotc)開啟新的文件。
而這個空白文件範本預設建立好的樣式中:標題1~標題9,早已在樣式中分別定義好「階層1~階層9」的大綱階層。建議大家優先使用,因為某些特殊用途必須是套用這幾個預先建立的樣式才能正常運作....(以後有空再介紹囉!)

文件中有大綱階層才可以.....

Word 2003以前版本:在檢視功能表中有個文件引導模式的選項 (檢視→文件引導模式)。也許能曾經開啟過,但是看到左邊一塊灰色空白的窗格.....於是乎默默地關掉他....從此不再提起...
Word 2007:在  檢視  索引標籤中  也有個「文件引導模式」的核取方塊。

Word 2010:「文件引導模式」改成「功能窗格」(因為他功能增加了,不只是文件引導而已...)

文件中有「標題」(大綱階層設定為「階層1~9」的段落),「文件引導模式」中才會顯示出標題內容。作為「閱讀引導」使用。