申明: 本站飛宇網 https://feiyetopro.blogspot.com/。自網路收集整理之書籍、文章、影音僅供預覽交流學習研究，其[書籍、文章、影音]情節內容， 評論屬其個人行為， 與本網站無關。版權歸原作者和出版社所有，請在下載 24 小時內刪除，不得用作商業用途；如果您喜歡其作品，請支持訂閱購買[正版]。謝謝！
第5章 Excel中的日期與時間設置
輸入日期的基礎操作
Excel中的日期為西曆
“在員工名單中輸入了利用員工生日計算出年齡的函數，但是結果居然是0。”
如果沒有完全掌握在Excel中處理日期的基本方法，就會發生這樣的事情。在本章中，我將告訴大家在Excel中輸入日期的方法，以及時間資料的特性。
首先來看一下關於輸入日期的基本事項。有一項非常重要的原則是“必須按照西曆格式輸入日期”。例如，想要輸入2014年1月1日，在半形模式下，按以下格式將西曆年、月、日，並用“/”隔開。
2014/1/1
此時，如果省略西曆年份直接輸入“4/1”，則畫面顯示如下。
省略西曆年份，輸入4/1
儲存格內顯示的是“4月1日”，並非以西曆表示，但在算式欄中顯示為“2014/4/1”。也就是說，不輸入西曆僅以“月日”格式輸入的情況下，日期將自動變為輸入當時的西曆年即“今年”的日期。如果想要輸入不是今年的日期，卻不輸入具體的年份，會導致儲存格不顯示西曆年份，你也就很難注意到有錯誤。
Excel雖然有可以從出生日期計算年齡的函數，但實際輸入的過程中不小心漏掉西曆年份的話，所有的資料都會自動變成“今年”的。因此，無論你是否要輸今年的日期，一定要將年、月、日全部輸入到儲存格中，並用斜線（/）隔開。這樣雖然有點麻煩，但一定要記住這是最基本的操作。
專欄 如何快速輸入今天的日期與現在的時間
想要快速地輸入今天的日期，使用快速鍵Ctrl+ ; 最方便。按下快速鍵，在作用儲存格中會自動顯示今天的日期。
順便一提，按Ctrl+: 可以輸入現在的時間。也許有人會問“誰會使用這個啊？”工作中用Excel做會議記錄時，有時會需要記錄發言的時間。這時，就會用到這個技巧。
日期•時間實際為序列值
Excel中的日期資料幾乎都是以“2014/1/1”的形式顯示在儲存格中的。而日期資料的實質其實是“序列值”。
比如說，在儲存格A1中輸入2014/1/1，在設置單元格格式的選項中可以將A1的顯示形式變更為“數值”，就會出現41640。這就是序列值。
這種序列值，按照“以1900年1月1日為第1天”的演算法，算出儲存格中的日期為第幾天。那麼，2014年1月1日從1900年1月1日算起正好是第41640天，所以“2014/1/1”的序列值即為41640。
“儲存格輸入1，出現了‘1900/1/1’。這什麼意思啊？”
我經常聽到這樣的疑問。這是因為目標儲存格的表現形式變成了日期的緣故。這時候，如果將儲存格的格式改回“數值”或“常規”，就會正常地顯示數位“1”了。
實際處理日期資料時，一般不需要在意序列值。明明輸入的是日期卻出現“41702”這種數字，如果發生這種狀況，我們需要知道這是代表日期的“序列值”，其原因是儲存格的格式為“日期”而不是“數值”或“常規”，這樣我們就可以做相應的處理了。
在看Excel函數的相關解說時，若是看到“做成序列值”“將參數指定為序列值”這種說法，要意識到“序列值=日期”。Excel中在處理關於日期的資料時，比如計算天數、年齡，從日期數值中得出星期幾的函數，就是利用這種序列值處理的。
例如，用Excel計算從2014年3月28日到2014年4月3日為止一共有多少天。我們可以在儲存格A2輸入“2014/3/28”，B2儲存格輸入“2014/4/3”，為了得出這兩個日期之間的天數，在儲存格C2輸入下列公式。
=B2-A2
這樣，從B2的日期減去A2日期得到的結果“6”會顯示在儲存格C2中。
儲存格B2的日期資料“2014/4/3”，對應的序列值為41732。
儲存格A2的日期資料“2014/3/28”，對應的序列值為41726。
用B2的序列值減去A2的序列值，即“41732-41726”，就可以得出“6”這個答案。
經常能夠遇到的情況則是：像A2為“20140328”、B2為“20140403”這樣的形式，雖然在Excel中不會被當成資料來處理，如果直接將這兩個資料看作是日期並做減法，想要計算出這中間的天數，是無法得出正確結果的。
這2個數據說到底只是代表“20140328”這個數字，並不是指“2014年3月28日”這個日期。因此，在輸入有“=B2-A2”的儲存格，雖然是將上述2個八位數做減法，卻會得出“75”這個結果。這時，我們應該把代表日期的序列值改為日期形式再進行計算。（參考150頁）。
處理時間資料
時間資料的序列值為小數
接下來我們來看一下如何處理具體時間。一般輸入時間資料時，需要用“:”隔開時、分、秒，如下：
13:00:00
在記錄田徑競技成績時一般需要精確到秒，而在管理工作時間等事務時不必精確到秒，只用“：”區隔小時和分即可。
時間的資料也可以轉換成序列值。日期的序列值為整數，而時間的序列值則為0～1的小數。
日期的序列值，以1900年1月1日為起始（即1），每加上1就代表第二天的日期（Excel能夠處理的最後日期為9999年12月31日，其序列值為2958465）。另一方面，時間的序列值，以上午0時0分0秒為起始（即0），每多1秒就會加上“1/86400”。因為，一天是24（時）×60（分）×60（秒）=86400（秒），因此第二天上午0時0分0秒的序列值為1。
【例】
● 上午6:00的序列值：0.25
● 中午12:00的序列值：0.5
● 下午6:00的序列值：0.75
雖然，在實際操作中我們沒有必要記住這些序列值，但是與日期相同，如果儲存格的格式被設置為常規，儲存格中就會出現不明所以的小數。這時候，我們要知道這是“時間的序列值”，並且將儲存格的格式更正為“時間”。
容易出現誤差的地方
電腦在處理小數點以後的數值的計算時肯定會出錯，我們一定要牢記這一點。Excel在計算含有小數的數值時，無法得出正確答案。電腦的資料是以二進位表示的，如果公式中存在無法識別的小數數值，在計算時就會出現誤差。在用Excel計算序列值為小數數值的時間資料時，也同樣會發生這一問題。
例如，將B列的開始時間與C列的結束時間做減法，在D列中顯示經過的時間。A和B的經過時間在目標儲存格中皆顯示為1:01，但比較這兩個儲存格，卻判斷為不同值（D4儲存格）。
明明經過了相同的時間，卻被判定為不同值
之所以會發生這種情況，是由於各時間資料中實際上包含了以秒為單位的數值，如果不知道一些簡便的處理方法，在進行相關處理時就會變得非常麻煩。
如何輸入正確的時間
如果要詳細解說應該如何處理時間資料，反而會阻礙大家的理解……真要詳細地講，那麼這樣的解說將會變成讀起來都會覺得很厭煩的長篇大論。所以，在這裡我只給大家介紹解決對策。
首先，我們來瞭解一下TIME函數。它是處理時間資料的函數，能夠指定時、分、秒。比如要製作“9:30:00”這樣的時間資料，我們可以輸入下面的公式：
=TIME(9,30,0)
反過來，儲存格A1中含有時間資料（如“9:00”）時，想要從此儲存格中分析出小時、分、秒的數值的話，就要用到HOUR函數、MINUTE函數和SECOND函數。分別可通過以下公式匯出相應的數值。
● =HOUR(A1) : 匯出儲存格A1中時間資料的小時數
● =MINUTE(A1) : 匯出儲存格A1中時間資料的分鐘數
● =SECOND(A1) : 匯出儲存格A1中時間資料的秒數
在處理任何時間資料時都可以用下面的函數公式，這樣能夠匯出絕對沒有誤差的時間資料（假定儲存格A1中含有時間資料）。
=HOUR(A1)*60+MINUTE(A1)
這樣一來，如果儲存格A1中是“8:25”則會自動返回“505”這個數值。這個數字表示的是從“上午0:00”到“上午8:25”經過的分鐘數，正好是505分鐘。像這樣，將時間資料轉換為不含小數點的整數，就能在計算時避免出現誤差。
以上一個出現誤差的案例，可通過以下方式解決。
出現誤差的時候，中途增加處理步驟
在E列與F列中，輸入前文中提到的相應函數，將開始時間與結束時間轉換為分別距離上午0:00的分鐘數。
將E列到G列的儲存格的格式改為“數值”。將這些轉換後的數值相減，就會得到G列上的經過的分鐘數，由於結果是不含小數點的整數，也不會產生誤差。在儲存格G4中輸入的是這兩項經過的分鐘數是否為相同值的判定邏輯式（=G3=G2）。結果為TRUE，就是說判定為經過的時間相同。
快速設置日期與時間
避免資料變為日期形式
即便你不想輸入日期，但只要輸入“1-11”、“1/21”這類資料，Excel會自動認定該資料為日期資料，並將其轉換成“1月11日”的形式。如果不需要自動轉換，可選擇下面的方法解決。
● 將儲存格的格式設置中的表示形式改為“文本”
● 在開頭處輸入單引號（'）
順帶一提，想要顯示分數形式的話，可通過以下方法輸入。
● 將表示形式改為“分數”
● 像“0 1/2”這樣的格式，在開頭處輸入0和半形模式下的空格。
經常更新工作表的日期
“這份訂單的製作日期怎麼還是上一周啊！”
像訂單這樣的Excel表格，只是改變日期和內容來重複使用同一張工作表，經常會發生這種忘記更改相關專案的失誤。為避免這樣的情況發生，我們可以使用TODAY函數，自動將工作表的日期更新為當前日期。只要輸入這個函數，之後就沒有必要手動更新日期了。
=TODAY()
輸入=TODAY()後顯示的結果
TODAY函數在對截止交貨期的天數、年齡、入社時間等需要自動計算的任務可以發揮很大的作用。如果要用Excel處理日期資料，熟練使用TODAY函數是我們最先需要掌握的技巧。
但是，在使用TODAY函數修改訂單等工作表中的日期欄時必須注意一點，那就是TODAY函數會即時更新當天的日期。直接保存Excel製作的訂單後，工作表中的日期會自動調整為當前日期。因此，需要保留原始資料時，請把檔轉存成PDF形式。
想要將年、月、日分別輸入不同的儲存格時
我在前文中曾經提過“在輸入日期時，請務必用“/”將年、月、日隔開。”但如果實際上這樣操作非常麻煩。因此，需要“將年、月、日分別輸入到不同的儲存格，用作日期欄位”，這樣能夠提高操作效率。但是想要分別輸入不同的儲存格時，需要將所在儲存格的格式設置為非日期資料（序列值），否則Excel就無法自動將之認定為日期形式來處理。也就是說無法進行天數、時間段和年齡等計算，也不能將這日期自動轉換成星期。
這時，要用到可以把年、月、日3個數值變為日期資料，即序列值的函數，那就是DATE函數。在匯出顯示日期形式的儲存格中輸入以下公式。首先，輸入“=DATE(”，然後按住Ctrl鍵，同時按順序點擊儲存格A2、B2、C2，這樣能夠快速完成操作。
=DATE(A2,B2,C2)
在儲存格D2中輸入=DATE(A2,B2,C2)
DATE函數是按照順序在第一參數到第三參數中輸入年、月、日的數位，並以此製作日期資料（即序列值）的函數。想要計算不是正確日期格式的日期資料時，應該先使用DATE函數將其轉換為日期資料。
如果需要處理的日期資料為2014年1月1日，有時會用“20140101”的8位數值形式保存。如果想把它變為正確的日期資料，還是需要用到DATE函數來處理。這時，我們就用到後面接下來會介紹的LEFT函數、MID函數、RIGHT函數，分別抽出相應的年、月、日的資料，再逐個組入DATE函數。
【例】
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
關於這個技巧，我會在下一章的關於字串操作中詳細講解。
如何從日期資料中匯出年、月、日
那麼相反地，如果想從日期資料中提取出年、月、日的資料，需要用到YEAR函數、MONTH函數、DAY函數。例如，從儲存格A1中的日期資料分出年、月、日資料，可利用相應的函數按以下方式匯出。
● =YEAR(A1)→A1的西曆年
● =MONTH(A1)→A1的月份
● =DAY(A1)→A1的日期
熟練運用函數，快速設置日期和時間
隨時查看距截止日期還有幾天
在利用Excel管理客戶檔案時，最方便的莫過於能自動顯示距離每位元客戶的生日、合同的更新日期還有幾天這樣的資料。如果想要在含有更新日期資料表格中的“剩餘天數”一欄，即時計算出“距離更新日期還有幾天……”，可以用“更新日期減去當前的日期”。
例如，按以下方式輸入，就能匯出截止到儲存格B2中的日期的剩餘天數。
=B2-TODAY()
在儲存格C2中輸入=B2-TODAY()
B2中的日期資料所對應序列值，與TODAY函數匯出的當前日期的序列值，二者相減就會得出上述結果。“利用序列值來處理日期的相關計算”，希望諸位讀者朋友能夠從這個事例中掌握這個訣竅。
如何匯出除週末和節假日外的營業天數
想要計算除雙休日和節假日外距某個截止日期的營業天數，可以使用NETWORKDAYS函數。在一般的工作中，這種計算營業天數的案例十分常見。
由於Excel本身並不配備節假日的相關資料，因此我們在前期需要另外準備節假日一覽表。在此製作一個以“節假日表”命名的工作表，然後照著下表製作一張節假日一覽表。可以在網路上搜索節假日資料表。
節假日一覽表
在儲存格A2中輸入交貨日期，想要計算出除去週末和節假日外距離該交貨日期的工作日還剩幾天時，只要用“當前日期”減掉“除週末和節假日外的截止日期”即可，公式如下：
=NETWORKDAYS(TODAY(),A2,節假日表!A2:A195)
此函數的參數表示意義如下：
● 第一參數：日期計算的開始日
● 第二參數：日期計算的結束日
● 第三參數：需要從日期計算過程中去掉含有節假日的範圍
這種方式可以得出“距離今天為止還有多少工作日”的結果，所以開始日期中要填入TODAY函數。
第三參數用於指定節假日，在這一例子中實際指定為“節假日表”中含有節假日日期資料的儲存格範圍（即A2：A195）。如果要把公司自己規定的休息天數考慮進去的話，可根據需要自行調整第三參數。
自動計算年齡
Excel還有種函數，輸入出生日期後，可以自動計算出年齡，它叫作DATEDIF函數。每天花幾小時查看出生日期，如果發現當前日期是生日的話再手動將年齡資料加1……我見過不少會“永無止盡”重複如此操作的案例。在此提醒各位，只要掌握這個函數，就可以完全避免花費大量時間進行如此麻煩的操作。
DATEDIF函數的結構如下：
【格式】
=DATEDIF(起始日期,結束日期,單位)
通過指定起始日期與結束日期，得出間隔的資料。
第三參數則根據想要如何表示間隔資料的單位，進行指定。
● “Y”→年
● “M”→月
● “D”→日
計算年齡資料時需要選擇“年”來作為單位元，因此需要按照以下方式輸入（假定B2為出生日期）。順帶一提，這個函數無法使用輔助輸入功能，必須手動輸入“=DATEDIF(”。
=DATEDIF(B2,TODAY(),"Y")
在儲存格C2中輸入=DATEDIF(B2,TODAY(),"Y")
想要通過這種方法自動計算出年齡，將出生日期指定為起始日期後，通常會輸入能夠匯出當前日期的TODAY函數作為結束日期。接著，從出生日期到今天為止所間隔的時間以年作單位來表示的話，需要在第三參數單位中輸入“Y”。
想計算出準確的結果，需要準確無誤地輸入西曆年的出生日期。如果不瞭解日期資料的基礎，只輸入月份和日期的話，那麼年份則會變成當前年份，這樣就無法計算出正確的年齡。所以，請一定記住“所有日期都要從西曆年開始輸入”。
用“×年×個月×日”表示年齡和間隔期間的資料
在實際的工作中經常會遇到“用“×年×個月×日”表示年齡和間隔期間”這樣的事。想要完成此項操作，需要牢記如何匯出除去年份後的從起始日期到結束日期的月份數（即×個月的部分），或者除去年份和月份的數值後的起始日期到結束日期的天數（即×日的部分）。
若想計算出“×個月”部分，將第三參數的單位代碼指定為“YM”。
在儲存格D2中輸入=DATEDIF(B2,TODAY(),"YM")，得到月份的數值
若想算出“×日”部分，將第三參數的單位代碼指定為”MD”。
在儲存格E2中輸入=DATEDIF(B2,TODAY(),"MD")，得到天數
這樣，我們就能在不同的儲存格中分別得出對應的數值。
順帶一提，想要在一個儲存格裡得出“×年×個月”的結果，可用“&”連結字串等混合欄位，從而實現組合輸入數值與函數公式。
如何從日期設置中匯出星期
Excel還可以從日期資料中得到當前日期為星期幾。掌握這個方法後，在製作日曆和行程表時會非常有效率。
Excel中有個函數叫作WEEKDAY函數，其主要用途為返回某日期的星期數。但事實上，還存在一種比它更簡單的函數——TEXT函數。
例如，想要在儲存格B2中顯示儲存格A2中的日期為星期幾，我們可以在B2中輸入以下公式：
=TEXT(A2,"aaa")
在儲存格B2中輸入=TEXT(A2,"aaa")
這時候，按照第二參數的指定方法，星期幾的表示形式則變為：
● “aaa”→日
● “aaaa”→星期日
● “ddd”→Sun
● “dddd”→Sunday
0 留言:
發佈留言