將樞紐分析表儲存格轉換成工作表公式

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

樞紐分析表有幾種版面配置,可為報表提供預先定義的結構,但是您無法自訂這些版面配置。如果在設計樞紐分析表的版面配置時需要更大的彈性,可以將儲存格轉換成工作表公式,然後充分利用工作表中的所有功能來變更這些儲存格的版面配置。您可以將儲存格轉換成使用 Cube 函數或使用 GETPIVOTDATA 函數的公式。將儲存格轉換成公式可大幅簡化建立、更新及維護這些自訂樞紐分析表的程序。

將儲存格轉換成公式時,這些公式會存取與樞紐分析表相同的資料,而且重新整理即可看見最新的結果。不過,報表篩選可能是例外,您將無法再存取樞紐分析表的互動功能,例如篩選、排序或展開及摺疊層級。

附註: 當您轉換線上處理分析 (OLAP) 樞紐分析表時,可以繼續重新整理資料以取得最新的測量值,但是無法更新顯示在報表中的實際成員。

您想要做什麼?

了解將樞紐分析表轉換成工作表公式的常見案例

將儲存格轉換成使用 Cube 函數的公式

使用 GETPIVOTDATA 函數轉換儲存格

了解將樞紐分析表轉換成工作表公式的常見案例

下列是一般範例,說明在將樞紐分析表儲存格轉換成工作表公式之後,有哪些方式可用來自訂轉換之儲存格的版面配置。

重新排列及刪除儲存格   

假設您需要每個月為職員建立一份定期報表。您只需要一部分報表資訊,而且想要依照自訂方式來安排資料的版面配置。那麼您只要在設計版面配置中移動及排列需要的儲存格、刪除職員月報表不需要的儲存格,然後設定儲存格及工作表的格式以配合您的個人偏好即可。

插入列及欄   

假設您想要顯示依區域及產品群組細分的前兩年銷售資訊,而且想要在其他列中插入詳細註解,那麼只要插入列並輸入文字即可。此外,您想要新增一欄,列出不在原始樞紐分析表中之區域及產品群組的銷售量,那麼只要插入欄、新增公式以取得您要的結果,然後向下填滿該欄以取得各列的結果即可。

使用多個資料來源   

假設您想要比較生產資料庫與測試資料庫之間的結果,以確定測試資料庫是否可產生預期的結果。只要輕鬆複製儲存格公式,然後變更連線引數以指向測試資料庫,即可比較這兩者的結果。

使用儲存格參照改變使用者輸入   

假設您想要依據使用者輸入來變更整個報表,可以將 Cube 公式的引數變更為工作表上的儲存格參照,然後在這些儲存格中輸入不同的值,即可得到不同的結果。

建立不一致的列或欄版面配置 (又稱為不對稱報表)   

假設您需要建立包含 2008 一欄 (稱為實際銷售量) 及 2009 一欄 (稱為預估銷售量) 的報表,但是不要其他任何欄,您可以建立只包含這些欄的報表,而且不必像樞紐分析表需要對稱。

建立自己的 Cube 公式及 MDX 運算式   

假設您想要建立報表,列出七月份某三個特定銷售人員的特定產品銷售量。如果您知道如何使用 MDX 運算式及 OLAP 查詢,可以自行輸入 Cube 公式。不過這些公式可能會變得相當複雜,您可以使用 [公式自動完成] 來簡化建立程序並改善這些公式的正確性。如需詳細資訊,請參閱使用公式自動完成

頁面頂端​

將儲存格轉換成使用 Cube 函數的公式

附註: 您只能使用這個程序來轉換「線上分析處理」(OLAP) 樞紐分析表。

  1. 若要將樞紐分析表以供日後使用,我們建議您進行的活頁簿複本,才能使用 [另存新檔] 命令,在Microsoft Office 按鈕] Office 按鈕影像 轉換樞紐分析表。如需詳細資訊,請參閱儲存檔案

  2. 執行下列動作來準備樞紐分析表,以便能盡量減少在轉換之後重新排列儲存格的工作:

    • 變更為與您所要之版面配置最類似的版面配置。

    • 在報表上執行操作 (例如篩選、排序及重新設計報表),以取得您要的結果。

  3. 按一下樞紐分析表。

  4. 在 [選項] 索引標籤上,按一下 [工具] 群組中的 [OLAP 工具],然後按一下 [轉換成公式]。

    如果沒有報表篩選,轉換作業隨即完成。如果有一或多個報表篩選,則會顯示 [轉換成公式] 對話方塊。

  5. 決定您要轉換樞紐分析表的方式:

    轉換整個樞紐分析表   

    • 選取 [轉換報表篩選] 核取方塊。

      這會將所有儲存格都轉換成工作表公式,並刪除整個樞紐分析表。

      只轉換樞紐分析表的列標籤、欄標籤及值區域,但保留報表篩選   

    • 確定已取消選取 [轉換報表篩選] 核取方塊 (這是預設值)。

      這會將所有的列標籤、欄標籤及值區域儲存格都轉換成工作表公式,並保留原始的樞紐分析表,只留下報表篩選,讓您能繼續使用報表篩選來進行篩選。

      附註: 如果樞紐分析表的格式為 2000-2003 (含) 以前版本,則您只能轉換整個樞紐分析表。

  6. 按一下 [轉換]。

    轉換作業會先重新整理樞紐分析表,以確定使用的是最新資料。

    在進行轉換作業時,狀態列中會顯示訊息。如果作業時間很長,您想要以後再轉換,請按 ESC 鍵取消作業。

    附註: 

    • 如果儲存格含有套用至隱藏層級的篩選,則無法轉換。

    • 如果儲存格中的欄位含有透過 [值欄位設定] 對話方塊的 [值的顯示方式] 索引標籤所建立的自訂計算,則無法轉換 (在 [選項] 索引標籤上,按一下 [作用中欄位] 群組中的 [作用中欄位],然後按一下 [值欄位設定])。

    • 轉換的儲存格會保留儲存格格式設定,但是會移除樞紐分析表樣式,因為這些樣式只能套用於樞紐分析表。

頁面頂端​

使用 GETPIVOTDATA 函數轉換儲存格

當您想要使用非 OLAP 資料來源時,如果不想立即升級至新版的 2007 樞紐分析表格式,或是想要避免使用 Cube 函數時的複雜性,可以使用公式中的 GETPIVOTDATA 函數將樞紐分析表儲存格轉換成工作表公式。

  1. 確定在 [選項] 索引標籤上已開啟 [樞紐分析表] 群組中的 [產生 GETPIVOTDATA] 命令。

    附註: [產生 GETPIVOTDATA] 命令會設定或取消選取位於 [Excel 選項] 對話方塊的 [運用公式] 中,[公式] 類別裡的 [為樞紐分析表參照使用 GETPIVOTTABLE 函數]

  2. 在樞紐分析表中,確定已顯示要在各公式中使用的儲存格。

  3. 在樞紐分析表以外的工作表儲存格中,在您想要包含報表資料的位置輸入公式。

  4. 在樞紐分析表中按一下您要用於公式的樞紐分析表儲存格。GETPIVOTDATA 工作表函數便會新增至公式中,用來擷取樞紐分析表中的資料。如果報表的版面配置變更,或是您重新整理資料,這個函數會繼續擷取正確的資料。

  5. 完成公式的輸入,並按 ENTER 鍵。

附註: 如果將 GETPIVOTDATA 公式中參照的任何儲存格從報表中移除,該公式便會傳回 #REF!。

頁面頂端​

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

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×