ch06 第六章生產規劃與銷售分析 ----------------------- Page 1----------------------- 第六章生產規劃與銷售分析  6- 1 前言  6-2 規劃求解的應用  6-3 生產規劃系統規劃  6-4 生產規劃系統檔案結構  6-5 生產規劃系統實作  6-6 銷售分析系統規劃  6-7 銷售分析系統檔案結構  6-8 銷售分析系統實作  6-9 條件式加總精靈  6- 10 結語 ----------------------- Page 2----------------------- 6- 1 前言 在本章共分兩個部分： ◦萬喜飲料股份有限公司，該公司生產部門主管正 面臨著有限生產資源的問題，即生產原料僅剩 1000克且機器設備時間僅剩800小時，而股東們 希望公司能製造出獲利最大的飲料產品，以獲取 股東利潤的極大化，生產部門如何完成使命？這 是本章所要探討的第一個問題。 ◦本章另一個要探討的主題是銷售分析的問題，萬 喜飲料股份有限公司為了瞭解公司各種產品銷售 的狀況，希望能依業務員編號、月份或銷售地區 來統計不同產品名稱的銷售總額，希望能藉由銷 售分析的報表來調整公司的銷售計劃，如何以樞 紐分析表來製作銷售分析統計報表是本章的第二 個重點。 ----------------------- Page 3----------------------- 6-2 規劃求解的應用  在使用規劃求解時，讀者應先釐清規劃求 解的最終目的是目標值的極大化，還是極 小化，我們需先依已知的條件設定限制式， 然後規劃求解的指令才能根據所設定的限 制式計算出一個符合條件的最佳目標值。  規劃求解是EXCEL的增益集，因此如果讀者在資料功 能區的右側沒看到規劃求解的標籤時，讀者應先按 Excel視窗左上角的Office按鈕，再按右下的Excel選項 鈕，在Excel選項對話方塊中，選取左側的增益集標 籤，就可看到使用中應用程式增益集的清單及未使用 中應用程式增益集的清單，請從未使用中應用程式增 益集的清單中選取規劃求解增益集並按執行鈕，在增 益集的對話方塊中選取規劃求解增益集再按確定鈕即 可完成安裝。 ----------------------- Page 4----------------------- 6-3 生產規劃系統規劃  由生產部門所提出的統計數字得知，我們 可以設定烏龍茶、綠茶及青草茶分別生產 X 、Y及Z打，總利潤設為P ，依本章前面 說明的各項限制條件，我們可以將這些限 制的條件以下列的數學不等式表達如下：  原料的限制不等式：3.5X+ 2.8Y+ 4Z<=1000  機器時間限制不等式： 1.3X+1.5Y+3.5Z<=800  生產數量不等式：X>=0 、Y>=0 、Z>=0且 皆為整數  規劃求解目標：Pmax=100X+90Y+130Z ----------------------- Page 5----------------------- 6-4 生產規劃系統檔案結構  請開啟生產規劃與銷售分析.xlsb並選取生產 規劃工作表，我們先依上面的已知條件將相 關的公式及資料輸入儲存格內，輸入後的工 作表如下： ----------------------- Page 6----------------------- 6-4 生產規劃系統檔案結構  毛利合計公式設定：F4 ＝D4*E4 、F5 ＝ D5*E5 、F6 ＝D6*E6  實際原料用量設定：B10儲存格的公式 設為＝B4*E4+B5*E5+B6*E6  實際生產時間設定：B11儲存格的公式 設為＝C4*E4+C5*E5+C6*E6  總收益設定：B12 ＝F4+F5+F6 ----------------------- Page 7----------------------- 6-5 生產規劃系統實作  請用滑鼠選取B12儲存格，即總收益的 儲存格，然後按資料功能區的規劃求解 標籤選取規劃求解指令，再依下列步驟 進行設定： ----------------------- Page 8----------------------- 6-5 生產規劃系統實作  烏龍茶生產數量限制式條件設定：請在 左上的圖示設定烏龍茶生產數量的條件， 設定完後按新增鈕以便設定烏龍茶生產 數量需為整數的條件如上圖示。請再按 新增鈕。 ----------------------- Page 9----------------------- 6-5 生產規劃系統實作  綠茶生產數量限制式條件設定：請在左 上的圖示設定綠茶生產數量的條件，設 定完後按新增鈕以便設定綠茶生產數量 需為整數的條件如上圖示。請再按新增 鈕。 ----------------------- Page 10----------------------- 6-5 生產規劃系統實作  青草茶生產數量限制式條件設定：請在 左上的圖示設定青草茶生產數量的條件， 設定完後按新增鈕以便設定青草茶生產 數量需為整數的條件如上圖示。請再按 新增鈕。 ----------------------- Page 11----------------------- 6-5 生產規劃系統實作  實際原料用量限制式條件設定：請依上 圖示設定後，再按新增鈕。 ----------------------- Page 12----------------------- 6-5 生產規劃系統實作  實際生產時間限制式條件設定：請依右上圖 示設定後，按確定鈕，則出現如下的畫面， 請按求解鈕即可計算出答案，詳細畫面如下。 ----------------------- Page 13----------------------- 6-5 生產規劃系統實作 ----------------------- Page 14----------------------- 6-5 生產規劃系統實作  報表選擇：由上圖得知最大總收益為 32420元，讀者可利用滑鼠選取欲輸出的 報表，因為生產數量有整數限制條件的關 係，因此僅能輸出分析結果報表。請選取 分析結果後按確定鈕則會產生一張運算 結果報表1的工作表如下： ----------------------- Page 15----------------------- 6-5 生產規劃系統實作 ----------------------- Page 16----------------------- 6-5 生產規劃系統實作  變更限制式條件：假設烏龍茶至少需生 產50打，則規劃求解的限制式需加以修 改，讀者可依下列步驟進行變更，請再 次執行資料功能區的規劃求解命令。 ----------------------- Page 17----------------------- 6-5 生產規劃系統實作 ----------------------- Page 18----------------------- 6-5 生產規劃系統實作  選取分析結果報表再按確定鈕後，可產生另一張運算結 果報表。 ----------------------- Page 19----------------------- 6-6 銷售分析系統規劃  在銷售分析系統中，我們將以資料功能區/資料 驗證標籤/資料驗證指令來建立銷售分析工作表 的銷售資料，首先我們需建立資料驗證所需的來 源清單，假設公司的業務員共有五位，銷售地區 共有五個地方且產品名稱有三種，詳細資料需輸 入在銷售紀錄工作表中，如下圖所示： ----------------------- Page 20----------------------- 6-6 銷售分析系統規劃  我們將以上述的資料利用資料驗證指令 來建立所有的銷售紀錄並利用插入功能 區/樞紐分析表標籤/樞紐分析表及樞紐 圖報表選項來產生樞紐分析表或樞紐分 析圖。 ----------------------- Page 21----------------------- 6-7 銷售分析系統檔案結構  請開啟生產規劃與銷售分析.xlsb檔案， 與銷售分析有關的工作表分別說明如後：  銷售紀錄工作表：在本工作表中，我們將以資料 驗證的下拉選項來建立業務員編號、銷售地區及 產品名稱欄位等資料，另外，我們將使用 VLOOKUP函數來查詢各產品名稱的單價，至於 其它欄位的內容，我們需依實際的銷售情形輸入。  樞紐分析表工作表：完成銷售紀錄工作表的建置 後，我們可依業務員編號、銷售地區與產品名稱 來產生樞紐分析表及統計各產品的銷售狀況。  樞紐分析圖工作表：樞紐分析圖是依樞紐分析表 所繪製的圖表。 ----------------------- Page 22----------------------- 6-8 銷售分析系統實作  在銷售分析系統中，各工作表的實作過 程分別說明如後： 1. 銷售紀錄工作表：本工作表將使用到資料 驗證及VLOOKUP函數，各欄位的設定分別 說明如下：  業務員編號欄：首先我們先選取A2 ：A100的儲 存格範圍，然後執行資料功能區/資料驗證標籤/ 資料驗證指令並依下列步驟設定： ----------------------- Page 23----------------------- 6-8 銷售分析系統實作 ----------------------- Page 24----------------------- 6-8 銷售分析系統實作  月份欄：依實際銷售月份輸入。  銷售地區欄：我們先選取C2:C100的儲存格範圍 後，執行資料驗證指令，依下列步驟設定資料驗 證的內容： ----------------------- Page 25----------------------- 6-8 銷售分析系統實作  產品名稱欄：請先選取D2:D100的儲存格範圍後， 執行資料驗證指令，再依下列步驟設定： ----------------------- Page 26----------------------- 6-8 銷售分析系統實作  單價欄：請選取E2儲存格後，輸入如下的公式：  =IF(D2="","",VLOOKUP(D2,$K$2:$L$4,2,0)) ，讀 者需特別注意此處是不能使用LOOKUP函數，因 為K欄的產品名稱並未遞增排列之故，請將公式 複製至E6:E100 。  訂購量欄：假設訂購量的數量必須為整數且數量 需介於1至500之間，則我們可以應用資料驗證的 功能來驗證所輸入的數量是否有效，請讀者選取 F2:F100的儲存格範圍並執行資料驗證指令後， 依下列步驟設定： ----------------------- Page 27----------------------- 6-8 銷售分析系統實作  小計欄：小計＝單價*訂購量，即G2 ＝ IF(D2="","",E2*F2) ，將公式複製至G3:G100 。 ----------------------- Page 28----------------------- 6-8 銷售分析系統實作 2. 樞紐分析工作表：請選取銷售紀錄中銷售 清單中的任一個儲存格後，執行插入功能 區/樞紐分析表標籤/樞紐分析表指令，再依 下列步驟設定。 ----------------------- Page 29----------------------- 6-8 銷售分析系統實作 ----------------------- Page 30----------------------- 6-8 銷售分析系統實作  建立樞紐分析表時，需將滑鼠設定在清單內的儲 存格，此時EXCEL就能自動選取該清單為樞紐分 析表的資料來源，因為此清單是一個封閉的儲存 格範圍，即清單與週遭的資料至少隔一列或一欄， 因此，EXCEL就能自動判斷其範圍，在EXCEL中， 瞭解清單或資料庫的觀念是非常重要的，在資料 功能區中的指令，譬如，排序、篩選等指令，清 單如果是封閉的區域，則在下達上述這些指令時 就無需事先再反白儲存格範圍了，但是有個限制 是清單的標題不能超過一列。 ----------------------- Page 31----------------------- 6-8 銷售分析系統實作 3. 樞紐分析圖工作表：在樞紐分析表中，請 選取插入功能區/直條圖標籤/立體群組直條 圖圖式，此時視窗就會顯示立體群組直條 圖，同時我們也可以選擇喜歡的圖表樣式， 如果想將圖表移動至新的工作表則可按移 動圖表標籤，然後將圖表工作表的名稱改 為樞紐分析圖即可，圖表內容詳如下圖。 ----------------------- Page 32----------------------- 6-8 銷售分析系統實作 ----------------------- Page 33----------------------- 6-9 條件式加總精靈  樞紐分析表確實讓我們瞭解公司整體的 銷售狀況，但如果讀者僅是要知道部份 的統計資料，這時使用條件式加總精靈 可能要比建立樞紐分析表來得簡單多了， 假設我們只要查詢某個萬喜青草茶的銷 售總金額，此時我們就可以利用條件式 加總精靈，首先，我們須先啟動條件式 加總精靈，條件式加總精靈是增益集的 一種，我們可依本章前面設定規劃求解 增益集的方式來設定條件式加總精靈。 ----------------------- Page 34----------------------- 6-9 條件式加總精靈 1. 請按公式功能區的條件式加總標籤，依 下列步驟設定。 ----------------------- Page 35----------------------- 6-9 條件式加總精靈 ----------------------- Page 36----------------------- 6-9 條件式加總精靈 ----------------------- Page 37----------------------- 6-9 條件式加總精靈 ----------------------- Page 38----------------------- 6- 10 結語  在本章裡，生產規劃系統讓我們瞭解如 何在有限的生產資源條件下，利用規劃 求解謀取股東利潤的最大化，同時，我 們更知道如何異動限制的條件，來求得 限制條件下的最佳解。在銷售分析系統 中，我們瞭解如何利用樞紐分析表來進 行統計分析，同時，我們更會利用條件 式加總精靈來解決簡易條件式加總的問 題。 ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ 生產規劃與銷售分析 ch6.xlsb --- 生產規劃 毛利合計 F4 =D4*E4 實際原料用量 B10 =B4*E4+B5*E5+B6*E6 實際生產時間 B11 =C4*E4+C5*E5+C6*E6 總收益 B12 =F4+F5+F6 --- 銷售紀錄 單價 如何在 Microsoft Excel 中使用條件式加總精靈 Excel 2010 中已經停用與已經修改的功能 http://office.microsoft.com/zh-tw/excel-help/HA101811053.aspx 改用 SUMIF, SUMIFS