Excel時間序列值:資料分析必備的基本日期函數

Excel資料分析往往需要日期維度,本文章先說明時間序列值的基本概念,再介紹如何利用DATE、TIME、TODAY、NOW等函數公式,快速得到特定與目前的時間日期的值。

一、Excel DATE函數

在Excel「B2」儲存格輸入函數公式:「=DATE(1900,1,1)」,在此使用DATE函數,三個參數分別是年月日,所以這裡的「(1900,1,1)」代表了1900年1月1日,公式計算結果為「1900/1/1」,這是依照Excel預設值所顯示的日期資料.。

注意到Excel為了計算方便,實際上是以數值儲存日期資料,也就是時間序列值的觀念,1900年1月1日的序列值為1。

Excel時間序列值:資料分析必備的基本日期函數 1

二、日期資料格式

在「B3」儲存格輸入和上個步驟同樣的DATE函數公式,快速鍵Ctrl+1進入「設定儲存格格式」的視窗,在「數值」頁籤這裡將「類別」切換到「通用格式」,可以看到計算結果為「1」。

「B3」對照上個步驟「B2」,應該可以更加瞭解Excel時間序列值的概念。

Excel時間序列值:資料分析必備的基本日期函數 3

三、日期序列值計算

在建立Excel時間(日期)序列值的基本概念之後,接下來修改DATE函數的參數值加以應用。例如「=DATE(2022,5,24)」是44705,代表從Excel日期計算的角度而言,1900/1/1到2022/5/24已經過了44705天了。

Excel時間序列值:資料分析必備的基本日期函數 5

四、Excel TIME函數

Excel的TIME函數有三個參數,依序分別是Hour(小時)、Minute(分鐘)以及Second(秒),因此三個參數都輸入0,會顯示計算結果為「12:00 AM」。

Excel時間序列值:資料分析必備的基本日期函數 7

五、Excel時間序列值

沿用先前第三步驟的方法,設計不同時間及更改儲存格格式,例如「=TIME(12,0,0)」,時間格式會顯示「12:00 PM」,數值格式顯示「0.5000」,如此應該就可以瞭解,所謂的一天,日期序列值是1,半天就是0.5,所以公式「=TIME(11,34,25)」的時間格式顯示「11:34 AM」,數值格式顯示「0.4822」,背後的計算意義為12:00 AM到11:34 AM差距為0.4822天,這就是Excel時間序列值的概念。

Excel時間序列值:資料分析必備的基本日期函數 9

六、TODAY日期函數

除了DATE函數以指定的年月日傳回特定日期,Excel還有一個TODAY日期函數,例如輸入公式:「=TODAY()」會傳回今天的日期,它算是Excel幾個不用帶有任何參數的函數之一。

Excel時間序列值:資料分析必備的基本日期函數 11

七、NOW時間函數

和TODAY日期函數相對應,有另外一個NOW函數同樣不帶有任何參數,它會傳回現在的時間。和第二步驟類似方式切換儲存格數值格式,可以知道所謂的現在,其實就隱含了今天的意思,因此也可以用完整的格式顯示日期加時間,例如「2022/5/24 11:37 AM」及「44,705.4846」。

Excel時間序列值:資料分析必備的基本日期函數 13

Excel及Power BI時間維度分析

日期及時間序列值主要分成兩個層面,一方面是因為很多時候要進行日期及時間差異的計算,因此在實質上必須以數值資料的方式加以儲存,另一方面在報表呈現時可能會依照需求而有不同格式的設置。這個不僅僅在Excel有很多相關的應用,在Power BI時間日期的DAX函數也是如此。本文主要透過Excel函數公式作為範例,希望讀者能具體瞭解其中概念,作為進階時間維度分析的基礎。

加強學習:贊贊小屋Excel函數文章

Excel時間序列值:資料分析必備的基本日期函數 15
加入Line社群,口袋裡的Excel小教室!