microsoftoff和iceexcel分析工具实用技巧.ppt

Microsoft Office Excel分析工具實用技巧 王仲麒 Microsoft MVP 目標搜尋 數學方程式的求解 請問X=? 房屋貸款提前繳清的反算 雙薪家庭貸款300萬購屋，年利率2.85，貸款期限20年，每月繳交本息16,413.56。 如果每月可繳交本息的能力為4萬，可以提早多久還清？ 目標搜尋 (續) 想要改變什麼（數據 - 變數儲存格）？ 使得什麼（公式 – 目標儲存格）？ 變成什麼（期望的運算結果 – 目標值）？ 目標搜尋 (續) 想要改變貸款期限（數據 - 變數儲存格） 使得每月繳交本息（公式 – 目標儲存格） 變成4萬（期望的運算結果 – 目標值） 目標搜尋 (續) 售價要訂定在多少？才可以有期望目標的利潤？ 特定利潤下的產品理想售價評估 原本每單位35的商品，若銷售10萬個，可以獲利350萬。考量平均折扣、考量產品成本，可計算所獲利潤。 如果成本不變、銷售目標不變。售價要提高到多少？才會有特定的利潤！ 規劃求解 Excel的增益集工具 ［目標搜尋］與［規劃求解］的差異 多變數的求解工具 產生規劃求解報表 運算結果報表 敏感度報表 極限值報表 規劃求解(續) 規劃求解(續) [目標搜尋]與[規劃求解]的差異 限制式 調整每種玩具製造量求：最大的總利潤 特定預算下，合理的業績要求 求得最低的總成本 調整三個倉庫的庫存，迎合六的地區的需求量，最低的總運費是多少？各倉庫的運量各是多少？ 分析藍本 分析藍本管理員 建立多樣本，進行假設分析 合併各分析藍本 摘要報告 分析工具 分析工具箱 建立複雜統計或工程分析時節省步驟 查表精靈 條件式加總精靈 查表精靈 條件式加總精靈 * * Agenda 目標搜尋 規劃求解 分析藍本 分析工具 問題: 調整售價，要調整到多少？可以至少有70,000元的毛利？ 想要改變售價（數據 - 變數儲存格） 使得毛利（公式 – 目標儲存格） 變成7萬（期望的運算結果 – 目標值） 問題: 調整男帽的銷售量，要調整到多少？可以至少有70,000元的毛利？ 條件假設 : 男帽銷售量不可超過 4,500 頂 女帽銷售量不可超過男女帽總銷售量的 65 % 或毛利至少 $75,000 問題: 同時調整男女帽的銷售量，要各調整到多少？可以至少有75000元的毛利？ 規劃求解 目標搜尋 設定多個判斷條件 必須設限(限制式) 多變數 僅有大於、等於、小於的比對 不需設限(沒有限制式) 單變數 問題: 同時調整三種麵包的生產數量，最多可賺多少錢？ 以低卡洛里的麵包為標準 低卡洛里的麵包至少生產 30,000 箱 高纖維麵包生產量的可接受度 : 至少必須為低卡洛里麵包的 1.5 倍 白麵包生產量的可接受度 : 至少必須為低卡洛里麵包的 2 倍 機器最多可運轉時數(生產所需總時數的極限) 機器總運轉時數不得超過 60,000 小時 調整每種媒體的廣告則數，在特定預算下，最多可以吸引的讀者數量。（每一種各刊登多少則廣告？） z-檢定 t 檢定 抽樣 迴歸 等級和百分比 亂數產生器 移動平均 長條圖 傅立葉分析 F-檢定：兩個常態母體變異數的檢定 指數平滑法 敘述統計 共變數 相關係數 Anova *