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

重要:  本文係由機器翻譯而成,請參閱免責聲明。本文的英文版本請見這裡,以供參考。

當您想要重建,使其更容易閱讀並瞭解,請考慮使用交叉資料表查詢的摘要資料。

交叉資料表查詢計算總和、 平均值、 或其他彙總函數,,然後將結果分組的兩組值,其中一個下的資料工作表,上方另一側。

本文內容

概觀

建立交叉資料表查詢

使用範圍或間隔做為標題

提示參數以限制列名

以零取代 Null 值

交叉資料表查詢秘訣

概觀

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

交叉資料表查詢的結構可以使其更易於閱讀比簡單的選取查詢,顯示相同的資料,如下列圖例中所示。

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

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

2.交叉資料表查詢可以顯示相同的資料,但水平和垂直群組資料,讓資料工作表可以是更精簡,更容易閱讀。

建立交叉資料表查詢

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

您也可以使用運算式來產生列名、欄名或要摘要的值。

交叉資料表查詢圖

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

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

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

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

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

使用交叉資料表查詢精靈   交叉資料表查詢精靈通常是最快速最簡單的方式來建立交叉資料表查詢。它會大部分的工作,但精靈並未提供的幾個選項。

精靈有下列優點 ︰

  • 很容易使用。   若要使用它,您可以啟動精靈,然後回答一系列的逐步問題。

  • 它可以自動將日期劃分成時間間隔。   如果您使用的欄位會包含日期/時間資料的欄標題,精靈也可協助您將日期劃分成時間間隔,例如月或季。

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

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

不過,藉由使用精靈,您無法 ︰

  • 使用一個以上的資料表或查詢作為記錄來源。

  • 使用運算式建立欄位。

  • 加入參數提示。

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

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

在 [設計] 檢視中工作    [設計] 檢視可讓您進一步控制您的查詢設計。支援精靈中並沒有提供的功能。

請考慮建立交叉資料表查詢,如果您想要使用 [設計] 檢視 ︰

  • 有更多的控制程序。精靈會為您一些決定。

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

  • 查詢中加入參數提示。

  • 您可以使用運算式作為查詢中的欄位。

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

  • 練習使用設計格線。

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

提示: 請記住,您不一定要建立交叉資料表查詢的使用只有一個方法。您可以使用精靈來建立查詢,,然後再使用 [修改查詢設計的 [設計] 檢視。

頁面頂端

建立交叉資料表查詢

使用交叉資料表查詢精靈來建立交叉資料表查詢

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

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

使用交叉資料表查詢精靈來建立交叉資料表查詢

Using the Crosstab Query Wizard requires that you use a single table or query as the record source for your crosstab query. If a single table does not have all the data that you want to include in your crosstab query, start by creating a select query that returns the data that you want. For more information about creating a select query, refer to the See Also section.

  1. 按一下 [建立] 索引標籤的 [其他] 群組中的 [查詢精靈]。

  2. [新增查詢] 對話方塊中,按一下 [交叉資料表查詢精靈],再按一下 [確定]

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

  3. 在精靈的第一個頁面上,選擇您要用來建立交叉資料表查詢的資料表或查詢。

  4. 在下一個頁面上,選擇您要使用其值做為列名的欄位。

    您最多可以選取三個欄位做為列名來源,但使用的列名越少,交叉資料表查詢的資料工作表就越容易閱讀。

    如果選擇多個欄位做為列名來源,您選擇欄位的順序會決定用來排序結果的預設順序。

  5. 在下一個頁面上,選擇您要使用其值做為欄名的欄位。

    一般來說,應該選擇包含很少值的欄位,這樣結果才會容易閱讀。例如,寧可使用只包含幾種可能值 (例如性別) 的欄位,而不要使用可能包含很多不同值 (例如年齡) 的欄位。

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

  6. 如果您選擇 [日期/時間] 欄位做為欄名,精靈的下一頁會要求您指定要用來將日期分組的間隔。您可以指定 [年][季][月][日期][日期/時間]。如果您並未選擇 [日期/時間] 欄位做為欄名,則精靈會略過這個頁面。

  7. 在下一個頁面上,選擇一個欄位,用來計算合計值的函數。您選取的欄位的資料類型會決定可使用哪些函數。

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

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

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

  9. 在精靈的下一個頁面上,輸入查詢的名稱,然後指定您是要檢視結果或修改查詢設計。

頁面頂端

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

By using Design view to create your crosstab query, you can use as many record sources (tables and queries) as you want. However, you can keep the design simple by first creating a select query that returns all of the data that you want and then using that query as the only record source for your crosstab query. For more information about creating a select query, refer to the See Also section.

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

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

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

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

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

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

建立查詢

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

  2. [顯示資料表] 對話方塊中,按兩下您要用來做為記錄來源的每一個資料表或查詢。

    如果您是使用一個以上的記錄來源,請確定都有的欄位聯結的資料表或查詢。如需加入資料表和查詢的詳細資訊,請參閱 請參閱 一節。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

指定固定值做為欄名

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

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

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

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

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

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

頁面頂端

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

交叉資料表查詢的 SQL 語法

交叉資料表查詢被以 SQL 陳述式轉換為。轉換陳述式的語法如下 ︰

轉換aggfunction
selectstatement
PIVOT樞紐分析欄位[IN (value1[, value2[,...]])]

轉換陳述式包含下列部分 ︰

部分

描述

aggfunction

SQL 彙總函數在選取的資料上作業。

selectstatement

SELECT 陳述式。

樞紐分析欄位

運算式您要用來建立查詢的結果中的欄標題的欄位設定。

value1 value2

固定的值,用來建立欄標題。

SQL view does not limit the number of tables or queries that you can use as record sources for a crosstab query. However, you can help keep the design simple by creating a select query that returns all of the data that you want to use in your crosstab query, and then using that select query as the record source. For more information about creating a select query, refer to the See Also section.

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

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

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

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

    TRANSFORM 
    SELECT
    FROM
    GROUP BY
    PIVOT
    ;
  5. 在第一列,轉換,輸入要用來計算合計值; 運算式例如, Sum([Amount])

    如果您使用一個以上的資料表或查詢做為記錄來源,包括資料表或查詢名稱做為每個欄位名稱的一部分例如, Sum ([費用]。 [金額])

  6. 在第二行中,選取,請輸入清單的欄位] 或 [您想要使用的列名的欄位運算式。使用逗號; 分隔清單項目例如, [預算]。 [Dept_ID],[費用]。[類型]

  7. 在第三個行中,,輸入清單的資料表或查詢,您使用的記錄來源。例如,預算、 費用

  8. 在第四個行中,群組依據],輸入欄位所使用的相同清單在 SELECT 子句中,在步驟 6。

  9. 在第五個行中,樞紐分析表中,輸入的欄位名稱或您想要做為欄名; 的運算式例如,樞紐分析表 [預算]。 [Year].

新增至列標題欄位的排序順序

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

  1. 插入 GROUP BY 子句與樞紐分析表子句之間的線條。

  2. 在新的一行,輸入順序,後面再加一個空格。

  3. 輸入欄位名稱或您想要排序; 的運算式例如,順序,[費用]。 [Expense_Class]

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

  4. 如果您想要排序的其他欄位或運算式,輸入一個逗號,然後輸入其他的欄位名稱或運算式。排序會出現在 [欄位] 或 [運算式出現在 [ORDER BY 子句的順序。

限制為列或欄名的值

若要指定做為欄名中使用,並將準則新增至您的資料列標題欄位的值清單,您可以使用下列程序。下列程序假設您已在 SQL 檢視] 中開啟交叉資料表查詢。

指定固定的值做為欄名

  • 在樞紐分析表子句的結尾,輸入,使用做為欄名後面跟著逗點分隔值 (以括弧括住) 清單。例如,在 2007年、 2008年、 2009年 (2010年)產生四個欄名 ︰ 2007年、 2008年、 2009年、 2010年。

如果您指定固定的值從樞紐分析表欄位未對應的欄位值,固定的值會變成空白欄的欄標題。

新增查詢準則,以限制列名

  1. 後 FROM 子句中插入新行。

  2. 輸入位置後面的欄位的準則。

    如果您想要使用其他的準則,您可以使用 AND 和 OR 運算子來擴充 WHERE 子句。您也可以使用括號來群組準則成邏輯組。

頁面頂端

使用範圍或間隔做為標題

有時候,而不是使用每個欄位的值的列或欄標題,您想要將範圍群組欄位的值,然後使用 [列或欄標題的 [範圍。例如,假設您將 「 年齡 」 欄位做為欄名。而不是使用資料行的每個年齡,您可能會想要使用代表時間範圍的欄。

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

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

IIf 的運作方式

IIf函數的運作方式評估的運算式,如果運算式為 false,然後傳回一個值,如果運算式為 true 或其他值。您可以使用巢狀IIf陳述式以建立邏輯順序的比較。因此,您可以使用IIf至分成範圍中的數字欄位的值。

IIf 語法

IIf ( expr truepart falsepart )

IIf函數語法具有下列引數 ︰

引數

描述

expr

必要。您要評估的運算式。

truepart

必要。若 exprTrue,會傳回值或運算式。

falsepart

必要。若 exprFalse,會傳回值或運算式。

使用運算式建立範圍

  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運算子串連使用萬用字元,運算式。例如,而不是使用[哪些國家/地區或 region?]作為篩選條件,您可以使用像 [哪些國家/地區或 region?] & 」 * 「以符合大範圍的 [輸入參數。使用不會變更外觀的參數提示。

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

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

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

頁面頂端

以零取代 Null 值

如果交叉資料表查詢中用來計算摘要值的欄位含有 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 子句的一部分。

頁面頂端

附註: 機器翻譯免責聲明︰本文係以電腦系統翻譯而成,未經人為介入。Microsoft 提供此等機器翻譯旨在協助非英語系使用者輕鬆閱讀 Microsoft 產品、服務及技術相關內容。基於本文乃由機器翻譯而成,因此文中可能出現詞辭、語法、文法上之錯誤。

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×