QQ登錄
微博登錄
微信登錄
材料的成本是生產成本的重要組成部分，而材料的成本除了價格因素外，還有一項很重要的因素，就是采購成本。采購成本通常由兩項組成，一是采購環(huán)節(jié)發(fā)生的費用；二是材料存儲時的發(fā)生的費用。這是兩個互相制約的因素，每批采購量大，采購次數少，可以減少年采購成本，但是存儲費用的增加；反之亦然。
因此確定采購量和存儲量之間的關系也是一項很重要的工作，通過“采購成本分析”可以幫助企業(yè)設置科學合理的采購量和采購次數，從而為降低企業(yè)采購成本提供可靠依據。
我們先創(chuàng)建采購成本和存儲成本在不同批次下的數據表，再利用公式計算最小成本、采購批次和采購量。然后添加年采購量、年采購成本和單位存儲成本滾動條，最后制作存儲成本和采購成本的散點圖。首先，我們還是要來看一下本節(jié)要使用的公式：
采購當量＝年采購量/年采購批次平均存量＝采購數量/2存儲成本＝平均存量×單位存儲成本采購成本＝年采購批次×采購成本總成本＝存儲成本＋采購成本
步驟01 新建工作表
新建一個工作簿，并在工作簿中重命名其中一個工作表，刪除其他的。輸入表格的字段標題、數據，并設置格式等。
步驟02 輸入公式
在B2到F2依次輸入下列公式：
=$B$19/A2
=B2/2
=C2*$I$19
=A2*$E$19
=D2+E2
步驟03 復制公式
選中B2:F2，移動光標到F2右下角，雙擊即可復制上述公式至B3:F13區(qū)域。
步驟04 最低采購成本公式
在B16單元格輸入公式：=MIN(F2:F13)。
步驟05 采購批次公式
在E16單元格輸入公式：=INDEX(A2:A13,MATCH(B16,F2:F13,0))。
步驟06 采購量公式
在I16單元格輸入公式：=INDEX(B2:B13,MATCH(B16,F2:F13,0))。關于INDEX函數的使用我們在前面的相關章節(jié)已經介紹過。
知識點：
MIN函數返回一組值中的最小值。
函數語法MIN(number1,number2,...)Number1,number2,...是要從中查找最小值的1到255個數字。
函數說明參數可以是數字或者是包含數字的名稱、數組或引用。邏輯值和直接鍵入到參數列表中代表數字的文本被計算在內。如果參數為數組或引用，則只使用該數組或引用中的數字。數組或引用中的空白單元格、邏輯值或文本將被忽略。如果參數中不含數字，則函數MIN返回0。如果參數為錯誤值或為不能轉換為數字的文本，將會導致錯誤。
MATCH函數返回在指定方式下與指定數值匹配的數組中元素的相應位置。如果需要找出匹配元素的位置而不是匹配元素本身，則應該使用MATCH函數而不是LOOKUP函數。
函數語法MATCH(lookup_value,lookup_array,match_type)Lookup_value為需要在Lookup_array中查找的數值。例如，如果要在電話簿中查找某人的電話號碼，則應該將姓名作為查找值，但實際上需要的是電話號碼。Lookup_value可以為數值（數字、文本或邏輯值）或對數字、文本或邏輯值的單元格引用。
Lookup_array可能包含所要查找的數值的連續(xù)單元格區(qū)域。Lookup_array應為數組或數組引用?！ atch_type為數字-1、0或1。Match_type指明Excel如何在lookup_array中查找lookup_value。如果match_type為1，函數MATCH查找小于或等于lookup_value的*5數值，Lookup_array必須按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；如果match_type為0，函數MATCH查找等于lookup_value的*9個數值，Lookup_array可以按任何順序排列；如果match_type為-1，函數MATCH查找大于或等于lookup_value的最小數值，Lookup_array必須按降序排列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...，等等。如果省略match_type，則假設為1。
函數說明函數MATCH返回lookup_array中目標值的位置，而不是數值本身。例如，MATCH("b",{"a","b","c"},0)返回2，即“b”在數組{"a","b","c"}中的相應位置。查找文本值時，函數MATCH不區(qū)分大小寫字母。如果函數MATCH查找不成功，則返回錯誤值#N/A。如果match_type為0且lookup_value為文本，可以在lookup_value中使用通配符、問號(?)和星號(*)。問號匹配任意單個字符；星號匹配任意一串字符。如果要查找實際的問號或星號，請在該字符前鍵入波形符(~)。
步驟07 添加年采購量滾動條
我們前面已經在自定義快速訪問工具欄上添加了滾動條按鈕，現(xiàn)在我們又要用到它。點擊這個按鈕，然后在A21單元格拖出一個滾動條。
步驟08 設置年采購量滾動條
在剛剛添加的滾動條上單擊鼠標右鍵，在彈出的對話框中對滾動條進行如圖中所示的設置。與這個滾動條相關聯(lián)的單元格是B19，設置方法是單擊文本框后面的編輯按鈕，再用鼠標選中B19單元格。單擊“確定”完成設置。
步驟09 添加并設置采購成本滾動條
使用與上兩步相機的方法在D21單元格添加滾動條，并對其進行設置，設置內容如圖所示。
步驟10 添加并設置單位存儲成本滾動條
同理，在H21單元格添加滾動條，并對其進行設置，設置內容如圖所示。
步驟11 完成設置
通過以上幾個步驟的設置，我們已經得到一個具有數據調節(jié)功能的采購成本分析表。
掃一掃微信，學習實務技巧
