了解 Excel 中的樞紐分析表，並在其中建立日期表格

在 Excel 中建立日期資料表

您可以在 Excel 中建立日期資料表，然後在 [資料模型] 中將它複製到新資料表。 這是一個相當輕鬆的作業，而且有很大的彈性。

當您在 Excel 中建立日期資料表時，您需要從具有一個連續日期範圍的單一資料行開始。 接著，您可以使用 Excel 公式在 Excel 工作表中建立額外的資料行 (例如 [年份]、[季度]、[月份]、[會計年度]、[期間] 等)，或是在將資料表複製到 [資料模型] 後，您也可以將它們建立為計算結果欄。 若要了解如何在 Power Pivot 中建立額外的日期資料行，請參閱本文稍後的＜將日期資料行新增到日期資料表＞一節。

將新日期資料行新增到日期資料表

若要定義某個日期範圍內的所有日期，具有單一日期資料行的日期資料表 (每年的每一天都各有一個資料列) 是很重要的關鍵。 也需要建立事實資料表與日期資料表之間的關聯。 不過，在樞紐分析表或 Power View 報表中依據日期進行分析時，該單一日期資料行 (每年各有一個資料列) 並不實用。 您會希望讓日期資料表包含多個資料行，協助您彙總某個範圍或某個日期群組的資料。 例如，您可能會想要依據月份或季度加總銷售額，或者您可能會建立計算逐年成長率的量值。 在這些各個案例中，日期資料表需要年份、月份或季度資料行，以便彙總該期間的資料。

如果您已從關聯式資料來源匯入日期資料表，該資料表可能已包含您需要的不同類型的日期資料行。 在某些情況下，您可能會想要修改其中的某些資料行或建立額外的日期資料行。 如果您在 Excel 中建立自己的日期資料表並將它複製到 [資料模型] 中，則更是如此。 幸好，透過 DAX 中的日期和時間函數 (英文)，您可在 Power Pivot 中輕鬆建立新日期資料行。

DAX 日期和時間函數

如果您曾在 Excel 公式中使用日期和時間函數，您可能相當熟悉日期和時間函數 (英文)。 雖然這些函數與 Excel 中的對應項目類似，仍有一些重要差異：

• DAX 日期和時間函數使用 [日期時間] 資料類型。

• 它們可使用資料行中的值做為引數。

• 它們可用來傳回和/或操縱日期值。

在日期資料表中建立自訂日期資料行時，通常會使用這些函數，因此請務必了解這些函數。 我們將使用許多這類函數建立 [年份]、[季度]、[會計月份] 等等的資料行。

DAX 包含下列日期和時間函數：

• DATE

• DATEVALUE

• NEXTDAY

• EDATE

• EOMONTH

• 小時

• 分鐘

• 月

• 現在

• 第二

• 時間

• TIMEVALUE

• 今天

• WEEKDAY

• WEEKNUM

• 年

• YEARFRAC

也有許多其他可在公式中使用的 DAX 函數。 例如，此處描述的許多公式都使用數學和三 角函數 ，例如MOD和TRUNC、邏輯函數 ，例如 IF和 TEXT 函數，例如FORMAT，以進一步瞭解其他 DAX 函數，請參閱本文稍後的其他資源一節。

日曆年度的公式範例

下列範例說明在 [行事曆] 日期資料表中用來建立額外資料行的公式。 一個 [日期] 資料行已經存在，且包含從 1/1/2010 到 12/31/2016 的連續日期範圍。

月份名稱

=FORMAT([date],"mmmm")

在此公式中，為取得月份名稱，我們使用 FORMAT (英文) 函數將 [日期] 資料行中的數值轉換為文字。 我們將 [日期] 資料行指定為第一個引數，然後指定格式；我們想要讓月份名稱顯示所有字元，因此使用「mmmm」。 結果看起來像這樣：

若要傳回縮寫為三個字母的月份名稱，則在格式引數中使用「mmm」。

星期幾

=FORMAT([date],"ddd")

在此公式中，我們使用 FORMAT 函數取得星期幾名稱。 由於我們只想要縮寫的星期幾名稱，因此在格式引數中指定「ddd」。

樞紐分析表範例

有了 [年份]、[季度]、[月份] 等日期的欄位後，您就可以在樞紐分析表或報表中使用這些欄位。 例如，下列影像顯示 [銷售] 事實資料表中的 [銷售額] 欄位 (位於 [值] 中)，並顯示 [行事曆] 維度資料表中的 [年份] 和 [季度] (位於 [資料列] 中)。 將會彙總年份和季度內容的 [銷售額]。

會計年度的公式範例

假日或特殊日期

您可能會想要包含將某些日期顯示為假日或其他特殊日期的日期資料行。 例如，您可能會想要以交叉分析篩選或篩選的形式，將 [假日] 欄位新增到樞紐分析表，以加總新年的銷售總計。 而在某些情況下，您可能會想要在日期資料行或量值中排除這些日期。

包含假日或特殊日期非常簡單。 您可以在 Excel 中建立有您要包含的日期的資料表。 接著，您可以複製或使用 [加入至資料模型]，將它新增到 [資料模型]，以做為連結資料表。 在大多數情況下，您不需要建立資料表與 [行事曆] 資料表之間的關聯。 任何參照它的公式都可以使用 LOOKUPVALUE 函數來傳回值。

以下是在 Excel 中建立的資料表範例，其中包含要新增到日期資料表的假日：

在日期資料表中，我們建立 [假日] 資料行，並使用像這樣的公式：

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

讓我們更仔細看看這個公式。

我們使用 LOOKUPVALUE 函數取得 [假日] 資料表的 [假日] 資料行中的值。 在第一個引數中，我們指定結果值將位於哪個資料行。 我們指定 [假日] 資料表中的 [假日] 資料行，因為這是我們要傳回的值。

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

接著，我們指定第二個引數，也就是有我們要搜尋的日期的搜尋資料行。 我們指定 [假日] 資料表中的 [日期] 資料行，就像這樣：

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

最後，我們指定 [行事曆] 資料表中的資料行，該資料行具有我們要在 [假日] 資料表中搜尋的日期。 這當然就是 [行事曆] 資料表中的 [日期] 資料行。

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

[假日] 資料行將傳回具有符合 [假日] 資料表中日期之日期值的每一列的假日名稱。

自訂行事曆 - 十三個四週的期間

某些組織 (例如零售業或食品服務業) 通常會根據不同的期間製作報表，例如十三個四週的期間。 在十三個四週期間的行事曆中，每個期間為 28 天，因此每個期間都包含四個星期一、四個星期二、四個星期三，依此類推。 每個期間都包含相同的天數，而且假日通常會落在每年的同一期間內。 您可任意選擇從星期幾開始某個期間。 就像日曆年度或會計年度中的日期一樣，您可以使用 DAX 建立具有自訂日期的額外資料行。

在下列範例中，第一個完整期間從會計年度的第一個星期天開始。 在此情況下，會計年度從 7/1 開始。

期間

此自訂行事曆中的期間包含 28 天，且一律會從星期天開始。 此資料行會傳回從第一個會計年度的第一個星期天開始的期間數。

=INT(([Week]+3)/4)

讓我們更仔細看看這個公式。

首先，我們建立以整數形式傳回 [週] 資料行中的值的公式，就像這樣：

=INT([Week])

接著，將該值加上 3，就像這樣：

=INT([Week]+3)

接著，將結果除以 4，就像這樣：

=INT(([Week]+3)/4)

結果看起來像這樣：

期間會計年度

此值會傳回某個期間的會計年度。

=INT(([Period]+12)/13)+2008

讓我們更仔細看看這個公式。

首先，我們建立一個傳回 [期間] 中的值並加上 12 的公式：

= ([Period]+12)

將結果除以 13，因為會計年度中有十三個 28 天期間：

=(([Period]+12)/13)

加上 2010，因為這是資料表中的第一個年份：

=(([Period]+12)/13)+2010

最後，我們使用 INT 函數移除結果的任何分數，然後在除以 13 時傳回整數，就像這樣：

=INT(([Period]+12)/13)+2010

結果看起來像這樣：

會計年度中的期間

此值會傳回期間數 (1-13)，從每個會計年度中的第一個完整期間 (從星期天開始) 開始。

=IF(MOD([Period],13), MOD([Period],13),13)

此公式稍微比較複雜，因此我們會採用比較容易理解的方式說明。 此公式指定將 [期間] 中的值除以 13，以取得年份中的期間數 (1-13)。 如果該數字為 0，則會傳回 13。

首先，我們建立一個公式，傳回根據 13 的 [期間] 中的值的餘數。 我們可以使用 MOD (英文) (數學和三角函數)，就像這樣：

=MOD([Period],13)

這通常可以提供我們想要的結果，除了 [期間] 的值為 0 以外，因為這些日期不會落在第一個會計年度內，就像 [行事曆] 日期資料表範例的前五天。 我們可以使用 IF 函數處理這一點。 萬一結果為 0，則傳回 13，就像這樣：

=IF(MOD([Period],13),MOD([Period],13),13)

結果看起來像這樣：

多個關聯

在某些情況下，您可能需要建立多個關聯或建立多個日期資料表。 例如，如果 [銷售] 事實資料表中有多個日期欄位 (例如 [日期索引鍵]、[出貨日期] 和 [退貨日期])，這些欄位全部都能與 [行事曆] 日期資料表中的 [日期] 欄位建立關聯，但只有其中一個可以是作用中的關聯。 在此情況下，由於 [日期索引鍵] 代表交易日期 (因此是最重要的日期)，這最適合當做「作用中」的關聯。 其他則是非作用中的關聯。

下列樞紐分析表會根據 [會計年度] 和 [會計季度] 計算總銷售量。 使用 Total Sales:=SUM([SalesAmount]) 公式且名為 [總銷售額] 的量值會放置在 [值] 中，而 [行事曆] 日期資料表中的 [會計年度] 和 [會計季度] 欄位則會放置在 [資料列] 中。

這個簡單易懂的樞紐分析表會正確運作，因為我們想要根據 [日期索引鍵] 中的「交易日期」加總總銷售額。 我們的 [總銷售額] 量值使用 [日期索引鍵] 中的日期，並根據會計年度和會計季度加總，因為 [銷售] 資料表的 [日期索引鍵] 和 [行事曆] 資料表的 [日期] 資料行之間有關聯。

非作用中的關聯

但如果我們並不是要根據交易日期加總總銷售額，而是要根據「出貨日期」呢？ 我們需要在 [銷售] 資料表的 [出貨日期] 資料行與 [行事曆] 資料表的 [日期] 資料行之間建立關聯。 如果不建立該關聯，彙總一律會根據交易日期。 不過，我們可以有多個關聯 (即使只有一個可以是作用中的關聯)，而由於交易日期是最重要的，因此會取得與 [行事曆] 資料表之間的作用中關聯。

在此案例中，ShipDate 具有非作用中關係，因此建立以出貨日期為基礎匯總資料的任何量值公式都必須使用 USERELATIONSHIP 函數指定非作用中關係。

例如，因為 [銷售] 資料表的 [出貨日期] 資料行與 [行事曆] 資料表的 [日期] 資料行之間有非作用中的關聯，我們可以建立根據出貨日期加總總銷售額的量值。 我們使用像這樣的公式指定要使用的關聯：

Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

此公式只指定：計算 [總銷售額] 的加總，但使用 [銷售] 資料表的 [出貨日期] 資料行與 [行事曆] 資料表的 [日期] 資料行之間的關聯進行篩選。

現在，如果我們建立樞紐分析表，並根據 [出貨日期] 量值將 [總銷售額] 放在 [值] 中，且將 [會計年度] 和 [會計季度] 放在 [資料列] 上，則會看到相同的 [總計]，但會計年度和會計季度的所有其他加總金額都是不同的，因為它們是根據出貨日期，而不是根據交易日期。

使用非作用中的關聯可讓您只使用一個日期資料表，但它確實需要任何量值 (例如根據 [出貨日期] 的 [總銷售額]) 在其公式中參照非作用中的關聯。 還有另一個替代方法就是使用多個日期資料表。

多個日期資料表

在事實資料表中處理多個日期資料行的另一種方法就是建立多個日期資料表，然後在這些資料表之間建立個別的作用中關聯。 讓我們再次看看 [銷售] 資料表範例。 我們可能想要根據三個資料行的日期彙總資料：

• 日期索引鍵：每個交易的銷售日期。

• 出貨日期：將售出項目出貨給客戶的日期和時間。

• ReturnDate – 收到一或多個退貨專案的日期和時間。

請記住，具有交易日期的 [日期索引鍵] 欄位是最重要的。 我們會根據這些日期執行大多數彙總，因此我們必定會想要在它和 [行事曆] 資料表的 [日期] 資料行之間建立關聯。 如果我們不想在 [出貨日期] 和 [退貨日期] 與 [行事曆] 資料表的 [日期] 欄位之間建立非作用中關聯 (因而需要特殊量值公式)，我們可為出貨日期和退貨日期建立額外的日期資料表。 接著，我們可以在它們之間建立作用中的關聯。

在此範例中，我們已建立名為 [出貨行事曆] 的另一個日期資料表。 當然，這也表示建立了額外的日期資料行，而由於這些日期資料行位於另一個日期資料表中，因此在命名時，我們想要採用能讓它們與 [行事曆] 資料表的相同資料行有所區隔的方式。 例如，我們已建立 [出貨年份]、[出貨月份]、[出貨季度] 等資料行。

如果我們建立樞紐分析表，並將 [總銷售額] 量值放在 [值] 中，且將 [出貨會計年度] 和 [出貨會計季度] 放在 [資料列] 上，看到的結果則會與建立非作用中關聯和根據 [出貨日期] 導出欄位建立特殊 [總銷售額] 時所看到的結果相同。

這些方法都需要謹慎考量。 搭配單一日期資料表使用多個關聯時，您可能需要使用 USERELATIONSHIP 函數建立轉換非作用中關聯的特殊量值。 另一方面，在欄位清單中建立多個日期資料表可能會造成混淆，而由於您在 [資料模型] 中有更多資料表，因此更需要費力記住它們。 請進行試驗找出最適合您的方式。

如何：設定 [日期資料表] 屬性

1. 在 PowerPivot 視窗中，選取 [行事曆] 資料表。

2. 在 [設計] 索引標籤上，按一下 [標記為日期資料表]。

3. 在 [標記為日期資料表] 對話方塊中，選取具有唯一值和 [日期] 資料類型的資料行。

將文字資料類型日期轉換為日期資料類型

在某些情況下，含有交易資料的事實資料表包含文字資料類型的日期。 也就是說，顯示為 2012-12-04T11:47:09 的日期其實根本就不是日期，或至少不是 Power Pivot 能夠理解的日期類型。 它只是看起來像是日期的文字。 若要在事實資料表的日期資料行與日期資料表的日期資料行之間建立關聯，兩個資料行都必須是 [日期] 資料類型。

通常當您嘗試將屬於文字資料類型的日期資料行的資料類型變更為日期資料類型時，Power Pivot 可解讀日期並自動將它轉換為真正的日期資料類型。 如果 Power Pivot 無法執行資料類型轉換，則會出現類型不符錯誤。

不過，您仍可將日期轉換為真正的日期資料類型。 您可以建立新計算結果欄，並使用 DAX 公式剖析文字字串中的年份、月份、日、時間等項目，然後將它重新串連為 Power Pivot 能夠讀取為真正的日期的方式。

在此範例中，我們已將名為 [銷售] 的事實資料表匯入 Power Pivot。 它包含名為 [日期時間] 的資料行。 值看起來像這樣：

如果我們在 Power Pivot 的 [常用] 索引標籤中查看 [格式設定] 群組的 [資料類型]，我們會看到它是 [文字] 資料類型。

我們無法在日期資料表的 [日期時間] 資料行與 [日期] 資料行之間建立關聯，因為資料類型並不相符。 如果我們嘗試將資料類型變更為 [日期]，則會出現類型不符錯誤：

在此情況下，Power Pivot 無法將資料類型從文字轉換為日期。 我們仍可使用此資料行，但如果要讓它變成真正的日期資料類型，我們需要建立新資料行，剖析文字並重新建立為 Power Pivot 能夠視為 [日期] 資料類型的值。

請記住，從本文稍早的＜處理時間＞一節中，除非您的分析不需要採用一日中的時間的精確度，否則您應將事實資料表中的日期轉換為 [日] 精確度。 秉持這個原則，我們希望新資料行中的值採用 [日] 精確度 (排除時間)。 我們可以將 [日期時間] 資料行的值轉換為日期資料類型，並使用下列公式移除 [時間] 精確度：

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

此提供了新的資料行 (在此情況下，名為 [日期])。 Power Pivot 甚至會偵測到日期的值，並自動將資料類型設為 [日期]。

若要保留時間精確度，只需將公式延伸為包含小時、分和秒就可以了。

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +

TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

現在，我們有屬於 [日期] 資料類型的 [日期] 資料行，我們可以在日期中建立它與日期資料行之間的關聯。

其他資源

Power Pivot 中的日期

Power Pivot 中的計算

快速入門：30 分鐘學會 DAX 基本概念

資料分析運算式參照

DAX 資源中心