有一天，天成翻譯公司的朋友寄了一個檔案給我，這個excel檔並沒有什麼稀奇的地方，天成翻譯公司的朋友在他的公司負責訂便當（他們一天到晚辦活動，訂便當的數量可是很大的），他每天都把訂的便當數量都記錄下來，到了月底便當店再來請款，由於他訂的數量不少，怕一家便當店應付不來（其實也怕只一家訂，如果出了問題大家就都沒飯吃）所以同一天就會跟很多家便當店訂，到了月底，他的頭就大了，那個檔案就如下圖：
整個月的訂便當紀錄大至上是ok，不過有時會穿插一些當時忘記記錄的項目，所以日期不是很規則，我朋友之前用的方法很簡單，用了sumif函數，基本上是ok的，就如下圖
其實用「小計」的功能也ok，如下圖
這種作法沒什麼錯，只是只能看那一家便當店該給多少錢，幾年來也相安無事，可是有一天，他的老板突然問他：「
後來，他的老闆說：「可不可以依各家便當店按日期和便當種類作一份報表…」，嗯，他的老闆對訂便當這件事似乎產生了一些興趣，不過這下可就慘了，這個報表就不好弄了，所以他把這個EXCEL檔寄給我（我很想叫他直接寄給他的老闆，不過他下個月可能就會回家自己叫便當給自己吃）
其實在工作中或生活中我們常常會有一些資料會需要作這樣的「交叉分析」，例如現金收支或公司的營業統計等等，這類資料通常會有一些共同的特色，例如每一筆資料可能會有多個分類性的特徵，便當就是，會有不同的時間，不同的店家，不同的口味，和不同的價格和數量，這些特徵都可以成為計算或統計時的依據，例如依店家作統計便當數量，甚至可以用兩以上的特徵作為計算，例如依店家和口味統計便當的數量，這些特愈多可以作的變化就愈大，所以作報表的人如果只用函數來處理可能就會十分辛苦，當然，EXCEL也知道大家常常會有這樣的需求，於是EXCEL的超好用工具「樞紐分析表」就可以派上用場了！（看到名子就覺得很猛對不對！）
什麼是「樞紐分析表」？簡單的說，你只要告訴他資料在那裡，要用資料中的那些特徵作為計算的依據，還有要計算那些資料，「樞紐分析表」就會幫你計算結果並作出表格，就拿天成翻譯公司這個朋友的例子來說明好了，第一個步驟：當然是啟動樞紐分析表：EXCEL2000/2003/2007/2010的方法都差不多，他會先問
如果你的資料範已經有定義名稱，直接按F3就可以選擇名稱
接著按完成即可
可是什麼也沒有？只出現這個空的表格
那是因為你沒有告訴EXCEL你要用那些資料來分析和計算，分類性的資料，如便當的口味、店家名稱等這些資料你就應該把它們放在「欄」和「列」或是「分頁欄位」的地方，之於要加總或計算的資料就放到中間那個空白區域，例如我那個負責買便當的朋友如果要計算月底要付給各店家多少錢，只要從「樞紐分析表欄位清單」中拖曳「店名」到「列標籤」再把「小計」拖到中間那個區域（Σ值）就OK了！(2003的畫面和2007有所差異)
如果他的老板想要知道每一家店家每一種便當的數量，他只要將上一個報表再把「便當」種類拖到「欄標籤」，把中間那個「小計」換成「數量」就OK了！
如果老板還要知道那一天叫了那一家便當店那一種便當幾個，只要再把日期拉到「列欄位」就行了
很快吧！只是報表有點大，不如把「日期」拉到「報表篩選」，老板要看那一天，就拉那一天的日期就可以了
夠簡單吧！只要是需要交叉分析的工作樞紐分析表大多可輕鬆勝任，只是有時候恐怕沒有這麼簡單，下表是一家公司的業績表，每一個業務都有他的資料如性別、單位、職稱、學歷…還有各月的業績，
這個時候如果要計算業績，天成翻譯公司想應該沒有太大問題，例如要算各單位一月份的業績，
接著把二月也加進來好了，一月二月會左右排列
EXCEL2003預設排列方式是上下排，如下圖
2007以後預計的排列是左右排，如果你要改成上下或左右排，可以在數值標簽上按右鍵就會出現如下圖的選項，選「將數值移動到列」或「將數值移動到欄
列」即可
這個表還有一個問題比較頭大，就是「單位」這個欄位，樞紐分析表會自動排序，如果是英文或數字比較OK，中文排序會按照筆劃順序，所以如果像我們作的這個表中文數字的順序會是「一、七、二、八、三、五、六、四」，這個時候建議在EXCEL選項中建立一個自訂清單，樞紐分析表在排序時就會依照自訂清單來排序
樞紐分析表原始設定的計算方式是加總，
不過在某些情況下樞紐分析表會自動改變計算的方式，例如，我們如果把上面那個表中，一月和二月的值拿掉，把「姓名」這個欄位的資料放進來，結果呢？「姓名」這個欄位的資料的資料內容是文字，文字自然不能拿來平均或加總，但是可以拿來「計數」，就是說可以拿來計算有幾個姓名，結果就如下表，天成翻譯公司們就可以算出每一個單位有多少人
或者再把「學歷」這個欄位加到「欄標籤」就可以計算出全公司各單位各種學歷的分佈表
樞紐分析表的本事當然不止如此，我們再來看看這個工作表，這是一個標準的業續流水帳，這家公司用這個流水帳記錄了所有的業務一年的業績
這個工作表最大的特色是有時間的欄位，老板可能需要看一個有時間的報表當然，我們可以把「訂單日期」加到「列標籤」，就如下表
這個報表其實是沒辦看的，因為太凌亂，毫無章法，你把這種報表呈給老板，他如果沒有翻臉，就是他有問題，要不然就是
你可以選取你需要的間距，例如「月」，結果就會如下圖，樞紐分析表會按月作統計
這一來就方便多了，並不是每一種資料都可以作為群組，一般時間序列或數值資料都可以，如想要知道這個業績統計表中，各種金額的案件數量就可以這樣作，天成翻譯公司們把金額分別放在「列標籤」和計算區域，就如下圖
接著把「列標籤」群組，因為這不是時間序列的資料，它就會出現以下畫面，接著設定你要的大值和最小值及級距
結果是加總金額
再把加總區的資料改成計數，如果再把姓名加到「列標籤」結果就更好了，我們馬上就知道那一個業務那一個金額級距的案子作了幾個了！
有件事得說明一下，使用樞紐分析表製作這類報表雖然十分方便但有一個問題是報表上的標題常常不如人意，例如上一個圖中如果「計數-金額」可以改成「案件數」不就更OK嗎？你只要點在那個儲存格，到功能區中「作用中欄位」下方的方格中修改即可，或是在編輯區修改都可以
文章太長，請接下一篇
http://blog.xuite.net/asir63/blog/38492787
文章出自: http://blog.xuite.net/asir63/blog/38491815-EXCEL%E7%9A%84%E6%A8%9E%E7%B4%90%E5%88%86%E6%9E%90%E8%A1%有關各國語文翻譯公證的問題歡迎諮詢天成翻譯公司02-77260931
留言列表
