6)枢纽分析表.docVIP

6) 樞紐分析表 ( Pivot Table ) 樞紐分析表能按清單內的大量資料，依使用者所選擇的格式有組織地快速摘要出來。 要建立樞紐分析表，可選功能表「資料」的「樞紐分析表...」，即呼叫「樞紐分析表精靈」： 第一步 選擇資料來源 第二步 選擇清單範圍 清單範圍 來源清單內的字段 第三步 版面配置 分析表內的欄位 、列位及資料排列 要配置版面，只需將右方字段 按紐用滑鼠拖曳到左方區域去 例如將 Year 放在 「列」 Type 放在 「欄」 Amount 放在 「資料」 另外「分頁」被空置起來。 第四步 選擇樞紐分析表的擺放位置 擺放位置，如不訂定位置，分析表將出現 於同一活頁簿的新一頁工作表上。 完成 結果.... 單一資料 單一欄位 單一列位 其他配置格式及結果： 以年份分頁 分頁按鈕：按這裡可選擇所有年度的總和或只顯示1995或1996的總和 ( All ) 1995及1996 1995年 目標搜尋（Goal Seek） 在使用公式時，我們都能在有充足參數值下知道公式計算的結果。例如 =SUM(B4:B12) 只要知道在B4至B12內各數值，自然可求得它們的總和。 相反，在某些情況下，我們希望知道在公式中代入甚麼數值才可得出某一目標結果，這種情況稱為「目標搜尋」。 例如 PMT 這函數是可用作計算樓宇按揭利率的問題： 本金 年利率 所供期數 每月按款額 =PMT(B4/12,B5,-1*B3,0,1) 這公式內含B3, B4及B5三個參數 上面例值中可計算出在年利率九厘半，供廿年而貸款二百五十萬這情況下，每月便應供款二萬三千餘元。這是直接利用PMT這函數代入各參數值而計算出結果。 但假設我們想知道 １）在每月只供 $20000的清況下，需要供多少個月？（假設其他參數不變） ２）在每月只供 $22000的清況下，可借貸多少本金？（假設其他參數不變） 利用Excel的「目標搜尋」功能，便可解決上面兩個疑問。 第一個問題： 選取「工具」的「目標搜尋」，出現下面視窗： 設定目標所在的儲存格 設定目標值 設定可改變參數的所在 設定完成按「確定」 結果計出所需年期為 508個月。 第二個問題： 同樣選取「工具」的「目標搜尋」，出現下面視窗： 結果是可借得 2,378,867.58元 運算列表（Table） 在上面計算樓宇貸款的例子中，假如我們要製一總表，列出在不同參數值下所得的月供款值，可使用Excel的「運算列表」（Table）。首先建立如下的列表： 利用PMT函數及上面三數值所計出的供款額 不同年利率參數值 選取適當範圍，選「資料」的「運算列表」。 設定列表中橫向改動參數位置 設定列表中直向改動參數位置 結果： 規劃求解（Solver） Excel的強大「規劃求解」（Solver）功能是一個很好的最佳化（Optimization）及資源配置（Resource allocation）工具。在工商業中解決計算「最高利潤」或「最低成本」的問題。例如某工廠在一定的人手、材料、運輸設備、時間資源下，管理階層應怎樣安排各生產線以獲得最高利潤或盡量減少製造成本？ 在中五數學課程中，不等式一課中的「線性規劃」（Linear programming）就是解決這些「最優化」問題。現在可以用Excel在一秒之間求出結果來。 上面的試算表是一間公司的利潤變化表，其中利潤（Prod. Profit）與廣告開支（Advertising）有密切關係。現在試利用Solver來求出各季的最佳化廣告開支。 首先試第一季： 選「工具」的「規劃求解」，得出下面的視窗。 改變哪一數值 設定目標儲存格 最佳化B14為最大值 完成設定後按「求解」，得出 結果：最佳廣告開支 17093元，而得出的利潤為 15093元。 選擇保留最佳化數值 或 還原原本數值 另外，如果求出在全年總廣告開支不超於40000元情況下，怎樣分配各季廣告費用使得全 年的利潤為最高？ 這次的「目標」是令儲存格 F15（即全年總利潤）等於最大值： 設定改變值的範圍（即四季廣告開支） 這次也加上限制（Constraint）： 儲存格F11（即四季廣告總開支） = 40000 最後得出最佳化數值： 最佳化數值是： 第一季廣告開支 $ 7273 第二季廣告開支 $ 12346 第三季廣告開支 $ 5117 第四季廣告開支 $ 15263 而總利潤得出最大值 $ 71447 另外，在一些情況下我們往往需要保特一些變動數值為整數；這時在「限