快速產生分析報表- Excel 2000樞紐分析表

恆逸資訊教育訓練中心首頁

作者: 恆逸資訊 羅慧真

Excel 的樞紐分析表可快速幫您取得各類分析表。

一季又過了轉眼已經到今年的第三季,聽說今年績效通過高標者,明年公司招待夏威夷七天四夜之旅。明天有個績效會議,聽前輩說就是這個年度會議定生死,為了夏威夷之旅,一定要好好表現。嗯… 行銷人員嘛! 績效要好就是消息要靈通,能夠提出各類分析報表並從中洞悉市場需求及流行走向。後者要靠平時資料的蒐集以及充足的分析報表。前者則可以依賴工具快速的產生。好,今天咱們就來談談如何快速的產生分析報表。
談到試算表,各位一定馬上想到Excel,但大部份的人都不曉得Excel除了一般報表的計算之外,還有一個很強大的功能,那就是樞紐分析表。如果您是個秘書或程式設計人員或許都經歷過這樣的事:手拿著辛苦計算出來的依地區別銷售分析表給老闆,老闆看完後點頭說很好,但接著他想看依月份或季別或產品…分析,就這麼往返n 次時間就浪費掉了,而老闆的一句話可能常令你要坐一個下午或犧牲約會時間才完成。為了美麗的週末以及夏威夷之旅,Excel的樞紐分析表您一定要會。

什麼是樞紐分析表


簡單來講,就是您那個可愛(加薪時)又可恨(下命令時)的老闆說的那句話「我要看今年每個月銷售人員的銷售報表,然後最好可以再來個依產品類別查詢…」然後您依據這些需求產生出來的報表;這看起來像是對資料下達查詢指令,然後再印報表。沒錯! 但寫過程式的人都知道,實際上要處理的可複雜多了,可是在Excel的樞紐分析表及圖報表精靈可以快速的幫您產生,我們先來看看樞紐分析表長的是什麼樣子。(如右圖)

產生樞紐分析表之前,您必須先了解分析數字的依據,這些數字的來源可以是一個資料庫的資料表,也可以是Excel工作表。如果您的資料來自於外部的資料表,那麼在之前您必須先定義好要查詢的資料。如果是Excel的工作表,就必須在精靈中指定要分析資料的範圍。不管資料是來自那兒,咱們先來了解一下樞紐分析表的結構。

樞紐分析表的結構


樞紐分析表是由四個區塊組合而成:列(Row)、欄(Column)、資料(Data)、以及頁(Page)。
列(Row)及欄(Column)

列(Row)及欄(Column)通常是您要查詢資料的主要依據。例如:如果您想要取得的是「每個銷售員在每月的銷售成績?」。那麼在樞紐分析表的列(Row)區塊為銷售人員,而在欄(Column)區塊則為月份。如果查詢改為「產品類別及每個銷售員在每月的銷售成績」,您只要在樞紐分析表上將產品類別拖放到列(Row)的區塊即可。(如右圖)
資料(Data)區塊

資料區塊為樞紐分析表中顯示資料的區塊,為列與欄交叉格子的資料內容。如圖三,「水果類、許俊龍」在「一月份」的銷售資料為「1284」。這個數字會隨著列與欄的變化而有所不同;也是你老闆較在意的一個區塊。
頁(Page)區塊

在樞紐分析表中,列、欄、以及資料區塊為必要的資訊,而頁區塊則不一定需要,不過有了它確可幫助您省了許多事。例如圖一,您的老闆可能只想看到某單一產品每個銷售員的每銷售狀況,並且可以自由的選擇想查看的產品類別,這時候您就需要「頁」區塊了。


從頁區塊的下拉方塊,您的老闆隨時可以點選您想看的類別統計數字,接著只要點選「確定」資料區塊就會顯示統計結果,是不是很方便呢!! 所以我們趕快來看看如何建立這個樞紐分析表吧!!

建立樞紐分析表

前面曾提過,樞紐分析表的建立很簡單,只要透過精靈就可建立完成,但是如果想透過一些VBA的程式碼來做到自動化的功能,則需要了解樞紐分析表的的物件架構。別擔心,記得巨集錄製功能嗎? 沒錯,咱們就是先來錄製個樞紐分析表吧!
現在咱們就來看看如何錄製樞紐分析表,這個功能非常簡單,只要幾個動作即可完成。
  1. 錄製巨集,在選單中選取「工具」、「巨集」、「錄製新巨集」。

  2. 開始製作樞紐分析,點選資料來源的起始位置A1的格子。

  3. 啟動樞紐分析表精靈,在選單中的「資料」點選「樞紐分析表及圖報表」。接著會出現樞紐分析表精靈。
    a. 樞紐分析表及樞紐分析圖精靈的步驟1:資料來源選擇,「Excel清單或資料庫」;建立何種型式的報表選擇,「樞紐分析表」。點選下一步。
    資料來源分為四種:
    --- Excel清單或資料庫:以Excel工作表當做分析資料的來源。
    --- 外部資料庫:可以為另一個Excel檔案、資料庫中的一個資料表、OLAP資料庫、以及Cube檔案。
    --- 多重彙總資料範圍。
    --- 別的樞紐分析表:樞紐分析表相同資料來源。

    樞紐分析表的型式:
    --- 樞紐分析表:分析的結果以工作表的方式表現。
    --- 樞紐分析圖:分析的結果以圖表的方式表現。



    b. 步驟2:建立或選擇資料範圍,選擇全部資料。點選下一步。

    c. 步驟3:產生的樞紐分析表要放在那裡,選擇「已經存在的工作表」,並選擇要擺放的位置。

    --- 新工作表:產生一個新的工作表,並將樞紐分析表的結果儲存在當中。
    --- 已經存在的工作:現存工作表的指定位置,必須指定空白的格子。



    d. 點選「版面配置」,在版面配置的對話方塊上,將右邊欄位名稱拖放欄位到適當位置。















    欄位名稱
    欄位位置
    銷售員
    列(R)
    日期
    欄(C)
    產品類別
    總計

    設定完畢後點選「確定」。









    e. 回到樞紐分析表及樞紐分析圖表精靈,點選「完成」。這時候工作表上會出現已經完成的樞紐分析表。












  4. 設定月份群組,點選日期格子,在選單中點選「資料」、「群組及大綱」、「群組」。「間距值」清單點選「月」,點選「確定」。

  5. 此時您可以看到完整的分析結果,試著在「產品類別」的下拉方塊點選「糖果類」,您會看到如圖一的結果。

  6. 停止錄製巨集。

PivotTable的物件架構


PivotTable物件為工作表下的一個物件,它的物件架構如右:

經過精靈的產生,在您的工作表上已經有一個樞紐分析表,整個樞紐分析表在Excel裡被視為一個PivotTables物件(如圖一)。如前述,樞紐分析表的內容包含:列、欄、頁、以及資料等區塊,而這些區塊(如圖九),即為PivotTable物件之下的PivotFields物件。記得我們前面曾說過的,您可以依據產品類別(如圖四),決定分析資料的因子,在每個區塊上都有個下拉方塊,下拉方塊所出現的清單即為PivotItems物件。

看看程式碼

如同精靈產生的步驟:
  1. 步驟1,選擇資料來源種類。
    PivotCaches.Add利用快取記憶體中快速運算建立一個樞紐分析表。須傳遞二個參數,如下:
    SourceType:xlDatabase,Excel清單或資料庫。
    xlExternal,外部資料庫。xlConsolidation,多種彙總資料範圍。 xlPivotTable,別的樞紐分析表。
    SourceData:資料來源。
  2. 步驟2,指定資料來源為目前的工作表。
    CreatePivotTable,參數如下:
    TableDestination :必須參數,指定樞紐分析表的列印位置。
    TableName :選擇性,樞紐分析表名稱。
  3. 步驟3,指定樞紐分析表列印位置,在CreatePivotTable的TableDestination 指定。
  4. 版面配置。指定每個區塊顯示的欄位。
    PivotTables("Pivot1").AddFields,參數如下:
    RowFields:指定列(R)區塊的欄位。
    ColumnFields:指定欄(C)區塊對映的欄位。
    PageFields:指定頁(P)區塊的欄位。
Sub Macro1()
' Macro1 巨集表
'
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'依產品類別查詢銷售人員月銷售量'!R1C1:R356C6").CreatePivotTable TableDestination _
:=Range("H1"), TableName:="樞紐分析表1"
ActiveSheet.PivotTables("樞紐分析表1").SmallGrid = False
ActiveSheet.PivotTables("樞紐分析表1").AddFields RowFields:="銷售員", _
ColumnFields:="日期", PageFields:="產品類別"
ActiveSheet.PivotTables("樞紐分析表1").PivotFields("總計").Orientation = _
xlDataField
ActiveWindow.ScrollColumn = 7
Range("I13").Select
Range("I3").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
False, True, False, False)
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 12
ActiveWindow.SmallScroll ToRight:=-7
Range("I1").Select
ActiveSheet.PivotTables("樞紐分析表1").PivotFields("產品類別").CurrentPage = _
"糖果類"
End Sub

結語

Excel提供相當多的試算功能,尤其在製作各類分析資料時,更是方便。各位只要依照以上的步驟即可產生多種類型的分析報表,而且可接受各類型的資料來源,像是OLAP資料庫或是Cube檔案。