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

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

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

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

附註: 交叉資料表查詢無法在 Access 應用程式,以及只有部分支援 web 資料庫: 無法使用任何網頁的物件。若要在 web 資料庫中使用交叉資料表查詢,請使用 Access 開啟資料庫。如需有關 Access 應用程式的詳細資訊,請參閱建立 Access 應用程式。如需 web 資料庫的詳細資訊,請參閱編輯或發佈 web 資料庫在 SharePoint 2010文件。

本文內容

概觀

建立交叉資料表查詢

使用範圍或間隔做為標題

提示參數以限制列名

以零取代 null 值

交叉資料表查詢秘訣

概觀

交叉資料表查詢是一種選取查詢,可用於將摘要資料 (總和、平均、計數及類似的函數) 排列成兩個類別配置,其中一個配置通常為時段。例如,您可以使用交叉資料表依地區顯示每個月的總銷售額。和顯示相同資料的簡單選取查詢比較起來,交叉資料表查詢排列結果的方式可讓您更容易閱讀,如下圖所示。

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

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

2. 此交叉資料表查詢會顯示相同的資料,但同時以水平和垂直的方式來分組資料。

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

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

交叉資料表查詢圖

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

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

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

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

使用交叉資料表查詢精靈的優點

  • 您可以自動將日期劃分成時間間隔     如果欄名含有日期/時間資料,精靈會協助您將日期劃分成標準時間間隔,例如月或季。

    附註: 如果想要使用您沒有看到精靈 (例如,會計年度或 biennium),然後精靈無法儲存任何工作的時間間隔。若要使用自訂的時間間隔,建立交叉資料表查詢設計] 檢視中,以手動方式,以及使用運算式來建立自訂的時間間隔。

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

設計檢視中可用的交叉資料表功能

  • 讓查詢要求輸入 (加入參數提示)    當您新增參數提示時,查詢會顯示對話方塊,並使用輸入來決定要包含的資料。例如,假設您有產品類別-顯示每季銷售交叉資料表,但您只想要一次查看一區域的資料。您無法使用參數,讓查詢要求要使用哪一個區域。

  • 使用運算式作為查詢中的欄位    有時候,您需要執行次要計算或其他函數為欄位的資料。例如,如果您的資料有任何 null 值,請您交叉分析結果可能不正確或完成。若要修正問題,您可能會套用至欄位的 Nz 函數 – 以零取代 null 值。

  • 指定固定值做為欄名的清單    您可能會發現您想要的欄標題換成您自己的值。例如,如果您有為欄名的時間間隔,它們可能不排序您想要的方式。沒問題 – 只要將它們取代會排序正確的值。

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

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

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

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

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

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

頁面頂端

建立交叉資料表查詢

開始交叉資料表查詢精靈

交叉資料表查詢精靈可讓您選擇的單一資料表或查詢做為交叉資料表查詢記錄來源。如果單一資料表中沒有您想要包含在交叉資料表查詢的所有資料,請先建立選取查詢傳回您想要的資料。如需建立選取查詢的詳細資訊,請參閱查詢簡介

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

在設計檢視中手動建立交叉資料表查詢

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

指定固定值做為欄名

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

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

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

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

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

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

頁面頂端

使用範圍或間隔做為標題

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

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

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

IIf 的運作方式

IIf 的運作方式是經由評估運算式,如果運算式為 True 就傳回某個值;如果運算式為 False 就傳回另一個值。您可以建立 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 運算子以萬用字元來串連運算式。例如,也可以不使用 [哪一個國家或地區?] 做為準則,而使用 Like [哪一個國家或地區?]&"*" 讓參數符合更大的輸入範圍。使用 Like 並不會變更參數提示的外觀。

  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)

頁面頂端

交叉資料表查詢秘訣

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

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

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

頁面頂端

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

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×