使用交叉資料表查詢讓摘要資料更容易閱讀

若要在 Access 中讓摘要資料更容易閱讀及了解,請考慮使用交叉資料表查詢。交叉資料表查詢會計算總計、平均值或其他彙總函數,然後根據兩組值將結果分組:其中一組值位於資料工作表的側邊,另一組位於頂端。按一下功能區上的 [建立],然後按一下 [查詢] 群組中的 [查詢精靈]。在 [新增查詢] 對話方塊中按兩下 [交叉資料表查詢精靈]。

如果一組標題具有日期值,精靈會協助您依據如月或季等標準間隔將記錄分組。

本文內容

概觀

使用 [交叉​​資料表查詢精靈​​] 建立交叉資料表查詢

在 [設計檢視] 中建立交叉資料表查詢

在 [SQL 檢視] 中建立交叉資料表查詢

使用範圍或間隔做為標題

提示參數以限制列名

以零取代 Null 值

交叉資料表查詢祕訣

附註: 交叉資料表查詢不適用於 Access Web App,且只有部分支援 Web 資料庫,因此無法供任何 Web 物件使用。

概觀

交叉資料表查詢是一種選取查詢。當您執行交叉資料表查詢時,結果會顯示在結構與其他類型之資料工作表不同的資料工作表中。

相較於顯示相同資料的簡單選取查詢,交叉資料表查詢的結構較易於閱讀 (如下圖所示)。

顯示相同資料的選取查詢和交叉資料表查詢

1. 這個選取查詢是根據員工和類別,以垂直方式來分組摘要資料。

2. 交叉資料表查詢可以顯示相同資料,但能以水平和垂直方式來分組資料,讓資料工作表變得更精簡且易於閱讀。

建立交叉資料表查詢時,您要指定哪些欄位包含列名、哪個欄位包含欄名,以及哪個欄位包含要摘要的值。當您指定欄名和要摘要的值時,只能各使用一個欄位;指定列名時,最多可以使用三個欄位。

您也可以使用運算式來產生列名、欄名或要摘要的值。如需詳細資訊,請參閱 SQL 彙總函數

交叉資料表查詢圖

1. 這一邊的一欄、兩欄或三欄會包含列名。您用來做為列名的欄位名稱會出現在這幾欄的最上面一列。

2. 列名會出現在這裡。如果您使用多個列名欄位,交叉資料表查詢資料工作表中的列數可能會快速增加,因為列名的各個組合都會顯示出來。

3. 這一邊的資料行會包含欄名與摘要值。請注意,欄名欄位的名稱並不會顯示在資料工作表上。

4. 摘要值會出現在這裡。

交叉資料表查詢的建立方式

使用交叉資料表查詢精靈   :交叉資料表查詢精靈通常是建立交叉資料表查詢最輕鬆快速的方法。交叉資料表查詢精靈會為您完成大部分的工作,但精靈無法提供少數幾個選項。

精靈有以下優點:

  • 易於使用。您只要啟動精靈並回答一系列引導式問題,即可使用精靈。

  • 自動依時間間隔將日期分組。如果您使用欄名含有日期/時間資料的欄位,精靈也會協助您依時間間隔 (例如月或季) 將日期分組。

    提示: 如果您想使用 [日期/時間] 欄位中的值做為欄名,但想依精靈未提供的時間間隔 (例如會計年度或兩年) 將日期分組,請不要使用精靈建立查詢。請改為在 [設計檢視] 中建立交叉資料表查詢,並使用運算式來建立時間間隔。

  • 可做為起點。您可以使用精靈來建立您想要的基本交叉資料表查詢,然後使用 [設計檢視] 微調查詢的設計。

不過,使用精靈時,您無法:

  • 使用多個資料表或查詢做為記錄來源。

  • 使用運算式建立欄位。

  • 新增參數提示。

  • 指定固定值清單做為欄名。

    在精靈的最後一個步驟中,您可以選擇是否要在 [設計檢視] 中修改查詢。這可讓您新增精靈不支援的查詢設計元素,例如額外的記錄來源。

使用 [設計檢視]   :[設計檢視] 可讓您進一步控制查詢設計,並且支援精靈未提供的功能。

若想執行以下動作,請考慮使用 [設計檢視] 建立交叉資料表查詢:

  • 進一步控制程序。精靈會幫您做出某些決定。

  • 使用多個資料表或查詢做為記錄來源。

  • 在查詢中加入參數提示。

  • 在查詢中將運算式做為欄位。

  • 指定固定值清單做為欄名。

  • 練習使用設計格線。

在 [SQL 檢視] 中撰寫查詢   :您可以視需要在 [SQL 檢視] 中撰寫交叉資料表查詢。不過,您無法使用 [SQL 檢視] 指令參數資料類型。如果您想在交叉資料表查詢中使用參數,您必須在 [設計檢視] 中修改查詢來指定參數資料類型。

提示: 請記住,您並非只能使用一種方法來建立交叉資料表查詢。您可以使用精靈建立查詢,然後使用 [設計檢視] 修改查詢設計。

頁面頂端

使用 [交叉​​資料表查詢精靈​​] 建立交叉資料表查詢

使用交叉資料表查詢精靈時,您必須使用單一資料表或查詢做為交叉資料表查詢的記錄來源。如果單一資料表無法提供您要納入交叉資料表查詢的所有資料,請從建立選取查詢開始著手,以傳回您要的資料。如需建立選取查詢的詳細資訊,請參閱另請參閱部分。

在此範例中,我們將使用「北風」範例資料庫中的 [產品] 資料表建立交叉資料表查詢。我們想在所有供應商的每個類別中顯示產品數量計數。

  1. 在 [建立] 索引標籤上,按一下 [查詢] 群組中的 [查詢精靈]。請注意,如果您使用的是 Access 2007,請按一下 [建立] > [其他] > [查詢精靈]。

    Access 功能區中的 [查詢] 群組會顯示兩個選項:[查詢精靈] 和 [查詢設計]
  2. 在 [新增查詢] 對話方塊中,按一下 [交叉資料表查詢精靈],再按一下 [確定]。

    交叉資料表查詢精靈就會啟動。

  3. 在精靈的第一個頁面上,選擇要用來建立交叉資料表查詢的資料表或查詢。在此範例中,我們選取 [產品] 資料表,然後按一下 [下一步]。

    在交叉資料表查詢精靈中,選取一個資料表或查詢。
  4. 在下一個頁面上,選擇內含要用來做為列名之值的欄位。您最多可以選取三個欄位做為列名來源,但使用的列名越少,交叉資料表查詢的資料工作表就越容易閱讀。如果選擇多個欄位做為列名來源,您選擇欄位的順序會決定用來排序結果的預設順序。

    在本範例中,我們選取 [供應商識別碼.值],然後按一下標示為 > 符號的按鈕。請注意,Access 會在對話方塊底部的範例查詢預覽左側顯示欄位名稱。按一下 [下一步] 以繼續。

    在交叉資料表查詢精靈中選取要顯示為列標題的欄位。
  5. 在下一個頁面中,選擇內含要用來做為欄名之值的欄位。一般來說,應該選擇包含很少值的欄位,這樣結果才會容易閱讀。例如,寧可使用只包含幾種可能值 (例如性別) 的欄位,而不要使用可能包含很多不同值 (例如年齡) 的欄位。

    如果您選擇用來做為欄名的欄位含有「日期/時間」資料類型,精靈會增加一個步驟,讓您指定將日期劃分成時間間隔的方式,例如劃分成月或季。

    在此範例中,我們選取 [類別]。請注意,Access 會在對話方塊底部的範例查詢預覽頂端顯示類別範例名稱。按一下 [下一步] 以繼續。

    在交叉資料表查詢精靈中選取要顯示為列標題的欄位。
  6. 如果您選擇 [日期/時間] 欄位做為欄名,精靈的下一頁會要求您指定要用來將日期分組的間隔。您可以指定 [年]、[季]、[月]、[日期] 或 [日期/時間]。如果您並未選擇 [日期/時間] 欄位做為欄名,則精靈會略過這個頁面。

  7. 在下一個頁面上,選擇用來計算摘要值的欄位和函數。所選欄位的資料類型會決定有哪些函數可用。

    在同一個頁面上,選取或取消選取 [是,加上列合計] 核取方塊,以納入或排除列合計。

    如果納入列合計,交叉資料表查詢就會有一個額外的列名,該列名使用相同的欄位與函數做為欄位值。納入列合計會額外插入一欄,這一欄會彙總剩餘的欄。例如,假設交叉資料表查詢會根據位置和性別來計算平均年齡 (使用性別欄名),額外這一欄就會根據位置計算所有性別的平均年齡。

    在此範例中,我們在 [欄位] 方塊中選取 [識別碼],並在 [函數] 方塊中選取 [項目個數],以便讓 Access 計算每個供應商和類別交叉點的產品數量。將 [是,加上列合計] 核取方塊保留為選取狀態。Access 會建立可加總每個供應商之產品數量的資料欄。按一下 [下一步] 以繼續。

    在交叉資料表查詢精靈中選取要計算的欄位和函數。
  8. 在精靈的最後一個頁面上,輸入查詢的名稱,然後指定您是要檢視結果或修改查詢設計。

    您可以在 [設計檢視] 中編輯交叉資料表查詢,以變更用來產生列合計的函數。

  9. 如果您按照此範例使用「北風」資料庫中的 [產品] 資料表,交叉資料表查詢會以列的形式顯示供應商名稱清單、以欄的形式顯示產品類別名稱,並在每個交叉點顯示產品數量計數。

    顯示在資料工作表檢視中的交叉資料表查詢,當中包含供應商和產品類別。

頁面頂端

使用 [設計檢視] 建立交叉資料表查詢時,您可以視需要使用多個記錄來源 (資料表和查詢)。不過,您可以保持使用簡單的設計,方法是先建立會傳回您想要的所有資料的選取查詢,然後使用該查詢做為交叉資料表查詢的記錄來源。如需建立選取查詢的詳細資訊,請參閱另請參閱一節。

當您在 [設計檢視] 中建立交叉資料表查詢時,是使用設計格線中的 [合計] 和 [交叉資料表] 資料列來指定哪個欄位的值會變成欄名,哪些欄位的值會變成列名,以及會使用哪個欄位的值來計算總計、平均、計數或其他彙總值。

顯示在設計檢視中的部分交叉資料表查詢

1. 這些資料列中的設定會決定欄位是列名、欄名或摘要值。

2. 這項設定會將欄位的值顯示為列名。

3. 這項設定會將欄位的值顯示為欄名。

4. 這些設定會產生摘要值。

建立查詢

  1. 在 [建立] 索引標籤的 [查詢] 群組中,按一下 [查詢設計]。請注意,如果您使用 Access 2007,按一下 [建立] > [其他] > [查詢設計]。

    Access 功能區中的 [查詢] 群組會顯示兩個選項:[查詢精靈] 和 [查詢設計]
  2. 在 [顯示資料表] 對話方塊中,按兩下您要用來做為記錄來源的每一個資料表或查詢。

    如果您使用多個記錄來源,請確認這些資料表或查詢是以共通的欄位聯結在一起。如需聯結資料表和查詢的詳細資訊,請參閱另請參閱一節。

  3. 關閉 [顯示資料表] 對話方塊。

  4. 在 [設計] 索引標籤上,按一下 [查詢類型] 群組中的 [交叉資料表]。

  5. 在查詢設計視窗中,按兩下您要用來做為列名來源的每一個欄位。您最多可以選取三個欄位做為列名來源。

  6. 在查詢設計格線中,於每一個列名欄位的 [交叉資料表] 資料列中選取 [列名]。

    您可以在 [準則] 資料列輸入運算式,以限制該欄位的結果; 也可以使用 [排序] 資料列指定欄位的排序順序。

  7. 在查詢設計視窗中,按兩下您要做為欄名來源的欄位。您只能選取一個欄位做為欄名。

  8. 在查詢設計格線中,於欄名欄位的 [交叉資料表] 資料列中選取 [欄名]。

    您可以在 [準則] 資料列輸入運算式,以限制欄名欄位的結果;不過,搭配欄名欄位使用準則運算式,並不會限制交叉資料表查詢所傳回的欄數;而是限制哪幾欄會包含資料。例如,假設欄名欄位只可能含有三個值:紅色、綠色和藍色。如果您將準則 ='藍色' 套用至此欄名欄位,交叉資料表還是會顯示紅色一欄、綠色一欄,但只有藍色這一欄會有資料。

    如果您想要限制顯示為欄名的值,可以使用查詢的 [欄名] 屬性來指定一份固定值清單。如需詳細資訊,請參閱下一節。

  9. 在查詢設計視窗中,按兩下您要用來計算摘要值的欄位。您只能選取一個欄位用來做為摘要值。

  10. 在查詢設計格線中,於摘要值欄位的 [合計] 資料列中選取要用來計算值的總合函數。

  11. 在摘要值欄位的 [交叉資料表] 列中,選取 [值]。

    您無法指定摘要值欄位的準則或排序順序。

  12. 在 [設計] 索引標籤上的 [結果] 群組中,按一下 [執行]。

指定固定值做為欄名

如果您想要指定固定值做為欄名,可以設定查詢的 [欄名] 屬性。

  1. 在 [設計檢視] 中開啟交叉資料表查詢。

  2. 如果沒有看見屬性表,請按 F4 鍵以顯示屬性表。

  3. 在屬性表的 [一般] 索引標籤上方,確認 [選取類型] 是 [查詢屬性]。如果不是,請按一下查詢設計格線上方空間中的空白點。

  4. 在屬性表的 [一般] 索引標籤上,於 [欄名] 屬性中輸入以逗號分隔的值清單,這些值就是您要用來做為欄名的值。

    有些字元 (例如大部分的標點符號) 不能出現在欄名中。如果您在值清單中使用這些字元,Access 會以底線 (_) 來取代每一個這種字元。

交叉資料表查詢的 SQL 語法

交叉資料表查詢在 SQL 中是以 TARNSFORM 陳述式的形式表示。TRANSFORM 陳述式包含下列語法:

TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]

TRANSFORM 陳述式由下列各部分組成:

部分

描述

aggfunction

對所選資料執行的 SQL AGGREGATE 函數。

selectstatement

SELECT 陳述式。

pivotfield

您要用於在查詢的結果集中建立欄標題的欄位或運算式。

value1, value2

用於建立欄標題的固定值。

[SQL 檢視] 不會限制可用來做為交叉資料表查詢之記錄來源的資料表或查詢數量。不過,您可以保持使用簡單的設計,方法是建立會傳回您想要用於交叉資料表查詢的所有資料的選取查詢,然後使用該查詢做為記錄來源。如需建立選取查詢的詳細資訊,請參閱另請參閱一節。

  1. 在 [建立] 索引標籤上,按一下 [其他] 群組中的 [查詢設計]。

  2. 關閉 [顯示資料表] 對話方塊。

  3. 在 [設計] 索引標籤的 [檢視] 群組中,按一下 [檢視],再按一下 [SQL 檢視]。

  4. 在 [SQL 物件] 索引標籤中,輸入或貼上以下 SQL:

    TRANSFORM 
    SELECT
    FROM
    GROUP BY
    PIVOT
    ;
  5. 在第一行中的 TRANSFORM 後方,輸入要用來計算摘要值的運算式;例如 Sune([Amount])

    如果您使用多個資料表或查詢做為記錄來源,請在每個欄位名稱中加上資料表或查詢名稱、例如 Sum([Expense].[Amount])

  6. 在第二行中的 SELECT 後方,輸入您要做為列名使用的欄位或欄位運算式清單。請使用逗號分隔清單項目;例如 [Budget].[Dept_ID], [Expense].[Type]

  7. 在第三行中的 FROM 後方,輸入您做為記錄來源的資料表或查詢清單;例如 Budget, Expense

  8. 在第四行中的 GROUP BY 後方,輸入在步驟 6 中用於 SELECT 子句的相同欄位清單。

  9. 在第五列中的 PIVOT 後方,輸入您要做為欄名使用的欄位名稱或運算式;例如 PIVOT [Budget].[Year]

若要在 [SQL 檢視] 中新增排序順序至交叉資料表查詢,請使用 ORDER BY 子句。

  1. 在 GROUP BY 子句和 PIVOT 子句之間插入一行。

  2. 在新行中,輸入 ORDER BY,後面再加上一個空格。

  3. 輸入要當做排序依據的欄位名稱或運算式;例如 ORDER BY [Expense].[Expense_Class]

    根據預設,ORDER BY 子句會以遞增順序排序值。如果您想以遞減順序排序值,請在欄位名稱或運算式後方輸入 DESC

  4. 如果您想依據額外的欄位或運算式進行排序,請輸入逗號,然後輸入額外的欄位名稱或運算式。系統會依據欄位或運算式在 ORDER BY 子句中的順序進行排序。

  • 在交叉資料表查詢的 [SQL 檢視] 中的 PIVOT 子句尾端,輸入 IN,後面再加上以逗號分隔的值清單 (以括號括住) 當做欄名使用。例如 IN (2007, 2008, 2009, 2010) 會產生四個欄名:2007、2008、2009、2010。

如果您指定的固定值不會對應到樞紐欄位中的欄位值,該固定值會變成空白欄的欄名。

  1. 在交叉資料表查詢的 [SQL 檢視] 中的 FROM 子句後方插入新行。

  2. 輸入 WHERE,後面再加上欄位準則。

    如果您想使用額外的準則,可以使用 AND 和 OR 運算子來擴充 WHERE 子句。此外,您也可以使用括號將準則組成邏輯集合。

有時候,您可能想要將欄位的值組成數個範圍,然後使用這些範圍做為列名或欄名,而不是使用欄位的所有值做為列名或欄名。例如,假設您要使用「年齡」欄位做為欄名。您可能會偏好使用一欄來表示一個年齡範圍,而不是每一個年齡都使用一欄來表示。

您可以在運算式中使用 IIf 函數來建立範圍,以便用來做為列名或欄名。

提示: 如果您想要使用「日期/時間」欄位建立時間間隔,請考慮使用交叉資料表查詢精靈。此精靈可讓您將日期劃分成 [年]、[季]、[月]、[日期] 或 [日期/時間] 等間隔。如果這裡沒有您要的時間間隔,就必須在 [設計檢視] 中建立交叉資料表查詢,然後使用本節所述的技巧來建立您要的時間間隔。

  1. 在 [設計檢視] 中開啟交叉資料表查詢。

  2. 在查詢設計格線中,於 [欄位] 資料列中,以滑鼠右鍵按一下某空白資料行,然後按一下捷徑功能表上的 [顯示比例]。

  3. 在 [放大顯示] 方塊中,輸入欄位別名,後面再加上冒號 (:)。

  4. 輸入 IIf()

  5. IIf 後面的刮號內輸入比較運算式,以定義欄位值的第一個範圍。

    例如,假設您要建立「年齡」欄位的範圍,並且每一個範圍都是二十年,則第一個範圍的比較運算式就是 [年齡]<21

  6. 在比較運算式後面輸入逗號,然後輸入範圍的名稱,並用引號括住。您所提供的名稱是落入該範圍的值的交叉資料表標題。

    例如,在 [年齡]<21 後面輸入逗號,再輸入 "0-20 歲"

  7. 在範圍名稱後面輸入一個逗號 (位於引號外面),然後執行下列其中一項:

    • 若要建立另一個範圍,請輸入 IIf() 然後重複執行步驟 5、6 和 7。

    • 如果是最後一個範圍,只需輸入範圍名稱。

      例如,可將「年齡」欄位劃分成以二十年為範圍的完整巢狀 IIf 運算式,看起來可能像下面這樣 (分行是為了讓您容易閱讀):

      IIf([Age]<21,"0-20 years",
      IIf([Age]<41,"21-40 years",
      IIf([Age]<61,"41-60 years",
      IIf([Age]<81,"61-80 years", "80+ years"))))

      附註: 當 Access 評估運算式時,只要其中一個 IIf 陳述式評估為 True,就會停止評估。您不需要指定各個範圍的起點,因為任何值若落在某指定範圍的起點之下,早已經是評估為 True。

  8. 在查詢設計格線中,選取 [合計] 資料列中的 [群組]。

  9. 在 [交叉資料表] 資料列中,指定是要使用範圍做為列名或欄名。請記得,您只能指定一至三個列名以及一個欄名。

您可能會想要讓交叉資料表查詢在執行時顯示要求輸入的提示訊息。例如,假設您使用數個列名,其中一個是「國家/地區」。此時您可能會想要讓查詢提示輸入某個名稱,然後根據使用者的輸入來顯示資料,而不是一律顯示所有國家或地區的資料。

您可以在任何列名欄位中加入參數提示。

附註: 您也可以在欄名欄位中加入參數提示,但這樣並不會限制系統顯示的欄。

  1. 在 [設計檢視] 中開啟交叉資料表查詢。

  2. 在您要提示使用者輸入資訊的列名欄位的 [準則] 資料列中,輸入以方括號括住的問題文字。查詢執行時,問題文字就會顯示成提示訊息。

    例如,如果您在 [準則] 資料列中輸入 [哪一個國家或地區?],當查詢執行時,就會出現一個對話方塊,其中含有「哪一個國家或地區?」這個問題的輸入方塊,以及一個 [確定] 按鈕。

    提示: 如果想要讓參數有彈性,可以使用 Like 運算子以萬用字元來串連運算式。例如,也可以不使用 [哪一個國家或地區?] 做為準則,而使用 Like [哪一個國家或地區?]&"*" 讓參數符合更大的輸入範圍。使用 Like 並不會變更參數提示的外觀。

  3. 在 [設計] 索引標籤的 [顯示/隱藏] 群組中,按一下 [參數]。

  4. 在 [查詢參數] 對話方塊的 [參數] 資料欄中,輸入在 [準則] 資料列中所用的相同參數提示,包括方括號,但不包括任何串連的萬用字元或 Like 運算子。

  5. 在 [資料類型] 資料欄中,選取每個參數的資料類型。此資料類型應該符合列名欄位的資料類型。

如果交叉資料表查詢中用來計算摘要值的欄位含有 Null 值,您使用的任何總合函數都會忽略這些值。如果是使用某些總合函數,甚至可能會影響結果。例如,若要計算平均值,您要加總所有的值,然後將加總結果除以值的個數。但如果欄位含有任何 Null 值,這些 Null 值的數目並不會算入值的個數中。

在某些情況下,您可能會想要以零來取代任何 Null 值,這樣在進行總合計算時就會將這些值算進去。您可以使用 Nz 函數以零來取代 Null 值。

Nz 語法

Nz ( variant [, valueifnull ] )

Nz 函數語法具有下列引數:

引數

描述

variant

必要。Variant 資料類型的變數。

valueifnull

選用 (除非在查詢中使用)。若 variant 引數為 Null 時,提供要傳回值的 Variant。這個引數可讓您傳回零或零長度字串以外的值。

附註: 如果在查詢中使用運算式中的 Nz 函數,而不使用 valueifnull 引數,結果會是包含 Null 值之欄位中的零長度字串。

  1. 在 [設計檢視] 中開啟查詢,於查詢設計格線中,以滑鼠右鍵按一下 [值] 欄位。

  2. 按一下捷徑功能表上的 [顯示比例]

  3. 在 [放大顯示] 方塊中,以括號括住欄位名稱或運算式,然後在括號前面輸入 Nz

  4. 在右括號內直接輸入 , 0

    例如,如果使用 Nz 與稱為「損失時數」的欄位將 Null 值轉換為零,完成的運算式看起來會如下:

Nz([Hours Lost], 0)

  • 盡量簡化查詢   :當資料列組合增多時,交叉資料表查詢可能會變得很難閱讀。請勿使用所需以外的列名。

  • 考慮分步驟來建立交叉資料表   :不要限制自己只使用資料表。您通常可以先從建立合計查詢開始著手,然後再使用該查詢做為交叉資料表查詢的記錄來源。

  • 謹慎選擇欄名欄位   :欄名數目比較少時,交叉資料表比較容易閱讀。一旦找出要用來做為標題的欄位後,請考慮使用相異值最少的欄位來產生欄名。例如,如果查詢要根據年齡與性別來計算值,請考慮使用性別做為欄名,而不要使用年齡,因為性別的可能值一般比年齡少。

  • 在 WHERE 子句中使用子查詢   :您可以在交叉資料表查詢的 WHERE 子句中使用子查詢。

頁面頂端

請參閱

建立簡單的選取查詢

聯結資料表和查詢

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×