Excel建立采購成本的分析表

1、Excel建立采購成本的分析表在企業(yè)進行活動時，往往需要采購大量的材料，但是材料的成本除了一項價格因素外，還有一個非常重要的因素，就是采購的成本。采購成本包括購買價款、相關(guān)稅費、運輸費、裝卸費、保險費以及其他可歸屬于存貨采購成本的費用。在這些費用中，可以歸類為2項，其就是采購環(huán)節(jié)發(fā)生的 費用和材料儲存發(fā)生的費用。購買價款、相關(guān)稅費、運輸費、裝卸費、保險費就可以看成是采購環(huán)節(jié)產(chǎn)生的費用，而儲存采購貨物則是材料儲存發(fā)生的費用。這兩項費用是相互制約的，例如，在采購量較大的情況下，采購的次數(shù)就會減少，就可以減少采購成本，但是其儲存費用就會增加；反之則亦然。因此確定采購量和存儲量之間的關(guān)系也是一項很重要

2、的工作，通過“采購成本分析”可以幫助企業(yè)設(shè)置科學(xué)合理的采購量和采購次數(shù)，從而為降低企業(yè)采購成本提供可靠依據(jù)。首先，我們還是要來看一下本節(jié)要使用的公式：采購當(dāng)量=年采購量/年采購批次平均存量=采購數(shù)量/2存儲成本=平均存量×單位存儲成本采購成本=年采購批次×采購成本總成本=存儲成本+采購成本步驟01 新建工作表新建一個工作簿，并在工作簿中重命名其中一個工作表，刪除其他的。輸入表格的字段標(biāo)題、數(shù)據(jù)，并設(shè)置格式等。步驟02 輸入公式在B2到F2依次輸入下列公式：=$B$19/A2=B2/2=C2*$I$19=A2*$E$19=D2+E2步驟03 復(fù)制公式選中B2：F2，移動光標(biāo)到

3、F2右下角，雙擊即可復(fù)制上述公式至B3：F13區(qū)域。步驟04 最低采購成本公式在B16單元格輸入公式：=MIN（F2：F13）。步驟05 采購批次公式在E16單元格輸入公式：=INDEX（A2：A13，MATCH（B16，F(xiàn)2：F13，0）。步驟06 采購量公式在I16單元格輸入公式：=INDEX（B2：B13，MATCH（B16，F(xiàn)2：F13，0）。關(guān)于INDEX函數(shù)的使用我們在前面的相關(guān)章節(jié)已經(jīng)介紹過。知識點：MIN函數(shù)返回一組值中的最小值。函數(shù)語法MIN（number1，number2，）Number1，number2，是要從中查找最小值的1到255個數(shù)字。函數(shù)說明參數(shù)可以是數(shù)字或者是包

4、含數(shù)字的名稱、數(shù)組或引用。邏輯值和直接鍵入到參數(shù)列表中代表數(shù)字的文本被計算在內(nèi)。如果參數(shù)為數(shù)組或引用，則只使用 該數(shù)組或引用中的數(shù)字。數(shù)組或引用中的空白單元格、邏輯值或文本將被忽略。如果參數(shù)中不含數(shù)字，則函數(shù)MIN返回0.如果參數(shù)為錯誤值或為不能轉(zhuǎn)換為數(shù)字 的文本，將會導(dǎo)致錯誤。MATCH函數(shù)返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。如果需要找出匹配元素的位置而不是匹配元素本身，則應(yīng)該使用MATCH函數(shù)而不是LOOKUP函數(shù)。函數(shù)語法MATCH（lookup_value，lookup_array，match_type）Lookup_value為需要在 Lookup_array中查找

5、的數(shù)值。例如，如果要在電話簿中查找某人的電話號碼，則應(yīng)該將姓名作為查找值，但實際上需要的是電話號碼。 Lookup_value可以為數(shù)值（數(shù)字、文本或邏輯值）或?qū)?shù)字、文本或邏輯值的單元格引用。Lookup_array可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域。Lookup_array應(yīng)為數(shù)組或數(shù)組引用。Match_type為數(shù)字 -1、0或1.Match_type指明Excel如何在lookup_array中查找lookup_value.如果match_type為1，函數(shù) MATCH查找小于或等于lookup_value的最大數(shù)值，Lookup_array必須按升序排列：、-2、-1、0、1、2

6、、A-Z、 FALSE、TRUE；如果match_type為0，函數(shù)MATCH查找等于lookup_value的第一個數(shù)值，Lookup_array可以按任 何順序排列；如果match_type為-1，函數(shù)MATCH查找大于或等于lookup_value的最小數(shù)值，Lookup_array必須按降序排 列：TRUE、FALSE、Z-A、2、1、0、-1、-2、，等等。如果省略match_type，則假設(shè)為1.函數(shù)說明函數(shù)MATCH返回lookup_array中目標(biāo)值的位置，而不是數(shù)值本身。例如，MATCH（“b”，“a”，“b”，“c”，0） 返回2，即“b”在數(shù)組“a”，“b”，“c”中的相

7、應(yīng)位置。查找文本值時，函數(shù)MATCH不區(qū)分大小寫字母。如果函數(shù)MATCH查找不成功，則返回 錯誤值#N/A.如果match_type為0且lookup_value為文本，可以在lookup_value中使用通配符、問號（？）和星號 （*）。問號匹配任意單個字符；星號匹配任意一串字符。如果要查找實際的問號或星號，請在該字符前鍵入波形符（）。步驟07 添加年采購量滾動條我們前面已經(jīng)在自定義快速訪問工具欄上添加了滾動條按鈕，現(xiàn)在我們又要用到它。點擊這個按鈕，然后在A21單元格拖出一個滾動條。步驟08 設(shè)置年采購量滾動條在剛剛添加的滾動條上單擊鼠標(biāo)右鍵，在彈出的對話框中對滾動條進行如圖中所示的設(shè)置。與這個滾動條相關(guān)聯(lián)的單元格是B19，設(shè)置方法是單擊文本框后面的編輯按鈕，再用鼠標(biāo)選中B19單元格。單擊“確定”完成設(shè)置。步驟09 添加并設(shè)置采購成本滾動條使用與上兩步相機的方法在D21單元格添加滾動條