使用樞紐分析表在 Excel Services 中使用外部表格資料

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

如果您想要在 Excel Services 中處理以外部資料連線為基礎的表格資料,您無法使用外部資料範圍 (也稱為查詢表)。您必須建立一個樞紐分析表,將多維度、階層式的資料平面化為表格,就是兩個維度。以下為您說明做法。

比較多維度和二維資料

1. 將多維度樞紐分析表 …

2. …轉換為二維樞紐分析表…

3. … 在 Excel Services 中檢視平面化的表格資料。

您想要做什麼?

瞭解如何在 Excel Services 支援外部資料來源的連線

深入瞭解樞紐分析表連線至外部資料和外部資料範圍之間的差異

建立二維的表格式樞紐分析表報表

建立樞紐分析表,並將其連線至外部資料來源

新增、 配置及排列 [列標籤] 區域中的欄位

變更樞紐分析表的選項,欄位設定

配置為二維資料表的 [樞紐分析表

自訂樞紐分析表報表的設計

活頁簿發佈至 Excel Services

瞭解 Excel Services 如何支援連線至外部資料來源

樞紐分析表的設計目的是將許多數值資料聚合為加總和合計,並處理以階層的方式組織的多維度資料。而外部資料範圍是包含列和欄結構的二維表格,用來顯示來源資料的非聚合記錄。

當您使用的資料連線精靈或 Microsoft Query 連線到外部資料時,您通常會建立外部資料範圍。唯一的例外是當您建立樞紐分析表連線至外部資料。樞紐分析表並不會建立外部資料範圍。建立連線方式的差異了解當您將活頁簿發佈至 Excel Services,因為 Excel Services 僅支援根據樞紐分析表的外部資料連線,而不支援的外部資料範圍。

頁面頂端

瞭解連線至外部資料和外部資料範圍之樞紐分析表的不同

當您使用的連線至樞紐分析表報表 外部資料的樞紐分析表行為與外部資料範圍時,您必須知道,包括下列之間的差異︰

樞紐分析表以由左至右的順序群組及子群組資料

一般而言,群組的資料是根據一或多欄的排序。例如,如果您想要依部門檢視員工的群組,您可以先依部門執行資料的主要排序,再依員工進行次要排序。您也可以建立巢狀群組,例如產品線、類別、產品,或地理區、國家/地區、縣/市和城市。根據預設,樞紐分析表會以由左至右的順序自動排序群組及子群組,讓您輕鬆地檢視相關的資料、顯示標題和摘要資訊,以及展開或摺疊相關的項目。

產品線],並在每個產品線內 subgrouped 依類別

產品線

類別

產品

引擎

飛機

機翼擾流板

機尾擾流板

推進器

汽車

269 HP

454 HP

排氣管

客車

高溫

標準

休旅車

小型

在左邊的列標籤區域加上一個包含唯一值的欄,即可自動將資料平面化。

相同的產品線資料均化相當藉由新增識別碼數字

識別碼

產品線

類別

產品

WM-345

引擎

飛機

機翼擾流板

TM-231

引擎

飛機

機尾擾流板

BSTR-567

引擎

飛機

推進器

6C-734

引擎

汽車

269 HP

8C-121

引擎

汽車

454 HP

MF-202

排氣管

客車

高溫

MF-321

排氣管

客車

標準

MF-211

排氣管

休旅車

小型

如果您不想要顯示欄,可以在新增欄到樞紐分析表中後將它隱藏 (選取欄,在 [常用] 索引標籤的 [儲存格] 群組中按一下 [格式] 旁邊的箭頭,指向 [隱藏及取消隱藏],然後按一下 [取消隱藏欄])。

重要: 如果樞紐分析表從資料來源中偵測到資料有兩個或更多重複的列,樞紐分析表只會顯示一列。如果您想要在樞紐分析表中顯示所有的列,包含重複的列,您必須在想要從來源資料匯入的表格、查詢或檢視中,新增一個包含唯一值的欄。如果資料來源沒有包含唯一的欄,您可以在資料來源中新增一欄。例如,您可以在 Microsoft Office Access 資料表中新增一個包含 AutoNum 資料類型的欄;或是在 Microsoft SQL Server 表格中新增一個 Identity 資料類型的欄;或是在資料庫查詢或檢視中新增一個計算結果欄,為每一個記錄顯示一個唯一的編號。

編輯儲存格資料以及新增計算結果欄

當您將活頁簿發佈至 Excel Services 之前,您可以在外部資料範圍中編輯資料,但是樞紐分析表資料永遠是唯讀的。

如果您按兩下外部資料範圍中的一個儲存格,就會進入編輯模式,您可以變更值或輸入公式。不過請注意,您變更的不是資料來源中的原始值,而且當您下一次重新整理資料時,新的值可能會被覆寫,視外部資料範圍內容設定 [如果資料範圍中的列數在重新整理時改變] 而定。

如果您按兩下樞紐分析表中的 [列標籤] 儲存格,[列標籤] 中的項目便會展開或摺疊。當您將樞紐分析表平面化為二維表格時,會使儲存格左邊的群組中的所有列值消失或出現。

您可以在以外部資料範圍為基礎的 Microsoft Office Excel 表格中的任何地方新增計算結果欄,也可以在外部資料範圍中插入欄及使用自動填滿。您無法在樞紐分析表中插入計算結果欄 (不過您可以插入計算欄位),不過,您可以在樞紐分析表相鄰的左邊或右邊新增自動填滿資料或公式。在自動填滿中,您可以使用樞紐分析表中的儲存格參照。

建立圖表

如果您想要建立樞紐分析表中的資料的圖表,您可以建立樞紐分析圖,其中行為標準圖表上類似,但有部分 差異,包括下列︰

互動     使用標準圖表時,您可以為每一個想要的資料檢視建立一個圖表,但是檢視之間並不會互動。使用樞紐分析圖,您可以建立一個表格,然後以不同的方式互動地檢視資料,方式是變更版面配置或顯示的詳細資料。在 Excel Services 中,標準圖表和樞紐分析圖都可以根據使用者和活頁簿的互動進行更新,但是兩者都會顯示為靜態圖像。

圖表類型     標準圖表的預設圖表類型是群組直條圖,比較不同類別的值;樞紐分析圖的預設圖表類型是堆疊直條圖,比較不同類別間,每一個值對於整體的貢獻。您可以將樞紐分析圖變更為任何圖表類型,除了 xy (散佈圖)、股票圖或泡泡圖之外。

圖表項目    樞紐分析圖報表包含相同的項目為標準圖表,但也包含欄位和項目新增至、 旋轉或移除,以顯示您資料的不同檢視。類別與標準圖表中的資料數列,是類別欄位、 數列的欄位,以及樞紐分析圖報表中的值欄位。樞紐分析圖報表中也可以包含報表篩選。每個欄位包含的項目,在 [標準圖表圖例中的類別標籤] 或 [數列名稱會顯示。

格式設定    大部分的格式設定,包括項目、 版面配置和樣式,會保留時您重新整理樞紐分析圖。不過,不保留趨勢線、 資料標籤, 誤差線,以及其他變更資料數列。標準圖表不會遺失一次套用此格式設定。

移動項目或重新調整大小     在樞紐分析圖中,您無法移動繪圖區、圖例、圖表標題、座標軸標題,或重新調整大小。不過,您可以從圖例的預先設定位置中選擇,也可以變更標題的字型大小。在標準圖表中,您可以移動所有這些元素,並重新調整大小。

如果您想要建立樞紐分析圖之後,您可以將它轉換為標準圖表。如需詳細資訊,請參閱建立 anaylze 工作表資料的樞紐分析表] 或 [建立樞紐分析圖的文章。

比較外部資料範圍內容和樞紐分析表選項

您可以使用外部資料範圍和樞紐分析表的內容,控制資料格式設定和版面配置,以及資料的重新整理作業。

外部資料範圍和樞紐分析表都可以讓您調整欄寬、保留欄排序、篩選、儲存格格式設定,以及在外部資料重新整理時,調整資料的版面配置。

在外部資料範圍中,您也可以包含資料的第一欄的列數字的選項。在樞紐分析表報表中沒有任何類似的選項。請參閱] 區段中,樞紐分析表中從左到右順序的群組與子群組資料,如需新增唯一欄資料來源的討論。

當您重新整理外部資料範圍時,如果資料範圍中的列數改變,您可以設定下列其中一個選項,控制如何處理現有的資料:

  • [插入新資料並刪除未使用的儲存格] (預設值)

  • [插入新資料,但不刪除未使用的列]

  • [依需要覆寫現有儲存格內容]

當您重新整理樞紐分析表時,它只顯示中 新的資料。不過,您可能會出現提示,並要求決定是否要覆寫現有的儲存格下方目前的報告。

篩選資料

在外部資料範圍和樞紐分析表中篩選資料的作業非常類似,不過有下列不同之處:

  • 外部資料範圍使用 [文字篩選][數字篩選][資料篩選] 等命令;樞紐分析表則使用 [標籤篩選][值篩選][資料篩選] 等命令。

  • 外部資料範圍中的 [篩選] 功能表會自動將移除已不再有效的項目和篩選功能表永遠不會包含新項目以套用篩選之後重新整理作業,但樞紐分析表來控制這種行為的選項。如需詳細資訊,請參閱變更樞紐分析表選項和欄位設定] 區段。

  • 樞紐分析表提供報表篩選功能 (外部資料範圍不提供這項功能),可以讓使用者處理樞紐分析表中的資料子集。

排序資料

在外部資料範圍中,您可以執行多欄排序,最多可達 64 欄。您可以選擇任一欄做為主要篩選,然後選擇任何其他欄做為次要篩選。但是,在樞紐分析表中篩選資料的程序和篩選外部資料範圍是不同的。和外部資料範圍不同,您只能在樞紐分析表中執行最左邊一欄的主要排序,所有其他欄會根據第二欄執行次要排序。簡而言之,您只能以由左至右的順序執行主要和次要排序。

此外,當您加入一個包含唯一值的欄做為左邊第一欄時,您只能依該欄進行排序,因為實際上您正在為每一列建立一個主要群組。根據您的使用者的需求,您可以先在 Excel 中預先排序資料,將活頁簿發佈至 Excel Services 後,清除 Microsoft Office Excel Web Access 網頁組件工具窗格中的 [排序] 核取方塊,以關閉排序功能。

使用超連結

和外部資料範圍不同,樞紐分析表儲存格不支援使用中的超連結。超連結會被視為文字,但是您無法在 Excel 或 Excel Services 中使用超連結前往網頁或文件。

頁面頂端

建立二維的表格式樞紐分析表

您必須採取一些步驟,才能將多維度的資料平面化為二維、表格式的樞紐分析表。請依照順序執行下列程序,以獲得最佳的結果。

頁面頂端

建立樞紐分析表並連線到外部資料來源

  1. 選取一個空白儲存格。

  2. [插入] 索引標籤的 [表格] 群組中,按一下 [樞紐分析表],然後再按一下 [樞紐分析表]

    Excel 功能區圖像

    [建立樞紐分析表] 對話方塊隨即顯示。

  3. 按一下 [使用外部資料來源]

  4. 按一下 [選擇連線]

    [ 現有連線] 對話方塊隨即出現。

  5. 在對話方塊頂端的 [顯示] 清單中,選擇您想要使用的連線類型,或選取 [所有現有的連線] (這是預設值)。

  6. [選取連線] 清單中選取一個連線,然後按一下 [開啟]

  7. 輸入一個位置,並執行下列其中一項操作:

    • 如果要將樞紐分析表放在新工作表中,並且從儲存格 A1 開始,請按一下 [新工作表]

    • 如果要將樞紐分析表放在現有工作表中,請選取 [現有工作表],然後輸入您想要放置樞紐分析表之儲存格範圍的第一個儲存格。

      或者,按一下 [摺疊對話方塊 按鈕圖像 暫時隱藏對話方塊,選取工作表的開頭儲存格,然後按一下 [展開對話方塊] 按鈕圖像

  8. 按一下 [確定]

在您輸入的位置便會建立一個空的樞紐分析表,並顯示樞紐分析表欄位清單。

頁面頂端

在列標籤區域新增、配置及排列欄位

使用樞紐分析表欄位清單新增、配置及排列欄位,並確保將所有的欄位都新增至 [列標籤] 區域。

  1. 按一下樞紐分析表。

  2. 請執行下列任一或多項操作:

    • 若要新增欄位至報表,請執行下列任一或多項操作:

      • 在 欄位清單中,選取欄位區段中每個欄位旁邊的核取方塊。每一個欄位都放在版面配置區段的預設位置,但是您可以重新排列欄位。

        根據預設,非數值欄位會新增至 [列標籤] 區域,數值欄位會新增至 [值] 區域,而時間/日期欄位會新增至 [欄標籤] 區域。

      • 如果要將欄位移至 [列標籤] 區域,請用滑鼠右鍵按一下欄位名稱,然後選取快顯功能表上的 [新增至列標籤]

        提示: 您也可以按一下並按住欄位名稱,然後將欄位從欄位區段拖曳至版面配置區段中的區域。

    • 若要重新排列欄位,請按一下其中一個區域中的欄位名稱,然後選取下列其中一個命令:

上移    

將欄位在此區域中往上移動一個位置。

下移    

在區域中將欄位向下移動一個位置。

移到開頭    

將欄位移到此區域的開頭。

移動到結尾   

將欄位移到此區域的結尾。

移到列標籤    

將欄位移到 [列標籤] 區域。

  • 若要移除 欄位中,按一下欄位名稱中的 [版面配置] 區域中,然後按一下 [移除欄位,或清除您想要移除,請在 [欄位] 區段中的每個欄位名稱旁的核取方塊。

    提示: 您也可以按一下並按住版面配置區域中的欄位名稱,然後將欄位名稱拖曳至樞紐分析表欄位清單的外面。

    附註: 清除欄位區段中的核取方塊將會移除報表中所有的欄位實例。

頁面頂端

變更樞紐分析表選項及欄位設定

為了確保樞紐分析表的作用方式與外部範圍類似,請執行下列操作:

變更樞紐分析表選項   

  1. 按一下樞紐分析表。

  2. [選項] 索引標籤上,按一下 [樞紐分析表] 群組中的 [選項]

    便會出現 [樞紐分析表選項] 對話方塊。

  3. 如果要在重新整理作業之後自動調整欄寬,請按一下 [版面配置與格式] 索引標籤,然後在 [顯示] 區段下方,選取 [更新時自動調整欄寬] 核取方塊。

  4. 如果要在每次重新整理資料時保留格式設定,請按一下 [版面配置與格式] 索引標籤,然後在 [顯示] 區段下方,選取 [更新時自動套用格式] 核取方塊。

  5. 如果要確認在重新整理作業之後,[篩選] 功能表確實移除無效項目,請按一下 [資料] 索引標籤,然後在 [每個欄位要保留的項目數] 方塊中選取 [無]

變更欄位的設定   

  1. 按一下樞紐分析表。

  2. 如果要在樞紐分析表中包含套用篩選的新項目,請在每一個欄位中,用滑鼠右鍵按一下儲存格,按一下快顯功能表上的 [欄位設定],按一下 [小計與篩選] 索引標籤,然後在 [篩選] 區段下方,選取 [在手動篩選中包括新項目] 核取方塊。

頁面頂端

將樞紐分析表配置為二維表格

如果要將樞紐分析表配置為二維表格,請執行下列操作:

  1. 按一下樞紐分析表。

  2. [設計] 索引標籤的 [版面配置] 群組中,按一下 [小計],然後按一下 [不要顯示小計]

  3. [設計] 索引標籤的 [版面配置] 群組中,按一下 [總計],然後按一下 [關閉列與欄]

  4. [設計] 索引標籤的 [檢視] 群組中,按一下 [報表版面配置],然後按一下 [以列表方式顯示]

  5. [選項] 索引標籤上的 [顯示/隱藏] 群組中,按一下 [欄位標題]

頁面頂端

自訂樞紐分析表的設計

內建的樞紐分析表樣式和樣式選項被專為多維度資料,而非二維資料表。簡報表維到二維資料表,這些樣式 不會顯示儲存格框線,然後 [帶狀列] 和 [帶狀欄核取方塊,樞紐分析表樣式選項] 群組中的 [設計] 索引標籤不會影響的儲存格格式。不過,您可以自訂樞紐分析表樣式,好讓每個儲存格的儲存格框線和樞紐分析表使用設定格式化的條件來顯示帶狀的列或欄。

自訂樞紐分析表樣式   

  1. 按一下樞紐分析表。

  2. [設計] 索引標籤上的 [樞紐分析表樣式] 群組中,選取您想要的樣式,然後執行下列操作:

    1. 用滑鼠右鍵按一下一個看到的樣式,捲動瀏覽樣式庫,或按一下捲軸底部的 [其他]

    2. 按一下 [重複]

      便會出現 [修改樞紐分析表快速樣式] 對話方塊。

    3. 或者也可以在 [名稱] 方塊中輸入一個新的名稱。

    4. [表格項目] 方塊中,選取 [整個表格],然後按一下 [格式]

      便會出現 [儲存格格式] 對話方塊。

    5. 按一下 [框線] 索引標籤,然後建立一個儲存格框線。

    6. 或者也可以按一下 [填滿][字型] 索引標籤,然後進行其他變更。

    7. 按兩次 [確定]

    8. 按一下捲軸底部的 [其他] 按鈕,然後在 [自訂] 類別下方,按一下您建立的樞紐分析表樣式。

使用設定格式化的條件來顯示帶狀的列] 或 [帶狀的欄   

  1. 選取樞紐分析表中的所有欄。

  2. 在 [常用] 索引標籤的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,然後按一下 [管理規則]。

    Excel 功能區圖像

    便會出現 [設定格式化的條件規則管理員] 對話方塊。

  3. 如果要新增格式化的條件,請按一下 [新增規則]

    [新增格式化規則] 對話方塊隨即顯示。

  4. 按一下 [選取規則類型] 底下的 [使用公式來決定要格式化哪些存儲格]。

    1. [編輯規則說明] 下方的 [格式化在此公式為 True 的值] 方塊中輸入公式。

    2. 執行下列其中一項操作:

      • 如果要使列成為帶狀,請輸入下列公式:

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A))

  • 如果要使欄成為帶狀,請輸入下列公式:

=AND(MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

  • 如果要使列和欄成為帶狀,請輸入下列公式:

=AND(MOD(ROW(),2)=1,ROW()<=COUNTA(A:A),MOD(COLUMN(),2)=1,COLUMN()<=COUNTA(1:1))

  1. 按一下 [格式] 以顯示 [儲存格格式] 對話方塊。

  2. 選取當儲存格中的值符合條件時,您想要套用的數值、字型、框線或填滿格式,然後按一下 [確定]

    您可以選擇一個以上的格式。您選取的格式會顯示在 [預覽] 方塊中。

頁面頂端

將活頁簿發佈至 Excel Services

  1. 按一下 [ Microsoft Office 按鈕] Office 按鈕影像 ,按一下 [發佈] 旁的箭號,然後按一下Excel Services等距分佈] 給其他人的文件

  2. [檔案名稱] 方塊中輸入伺服器的路徑,並接受活頁簿的建議名稱。如有需要,也可輸入新名稱。

    在執行 Windows Vista 的電腦上   

    • [網址列] 中選取可存取所提及使用者的網路位置,然後按一下 [儲存]

      在執行 Microsoft Windows XP 的電腦上   

    • 在 [儲存位置] 方塊中,選取可讓預期使用者存取的網路位置,然後按一下 [儲存]。

      例如,輸入http://伺服器/網站/檔案名稱

      附註: Excel 只能以 Microsoft Office Excel 2007 XML 檔案格式 (.xlsx) 或 Office Excel 2007 二進位檔案格式 (.xlsb) 發佈活頁簿至伺服器。

  3. 按一下 [確定]

  4. 如果要確認活頁簿的可檢視範圍正確地顯示在瀏覽器中,請選取 [儲存後在瀏覽器中開啟此活頁簿] 核取方塊。

  5. 按一下 [儲存]

如需詳細資訊,請參閱發佈至 Excel Services 的活頁簿的文件。

頁面頂端

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

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×