Excel建立采購(gòu)成本的分析表

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

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

3、2 : F2，移動(dòng)光標(biāo)到F2右下角，雙擊即可復(fù)制上述公式至B3 : F13區(qū)域。步驟 04 最低采購(gòu)成本公式在 B16 單元格輸入公式: =MIN （F2: F13）。步驟 05 采購(gòu)批次公式在 E16 單元格輸入公式： =INDEX （ A2 : A13，MATCH （ B16，F(xiàn)2 : F13，0）。步驟 06 采購(gòu)量公式在 I16 單元格輸入公式: =INDEX （B2: B13， MATCH （B16， F2: F13， 0）。關(guān)于 INDEX 函數(shù)的使用我們?cè)谇懊娴南嚓P(guān)章節(jié)已經(jīng)介紹過(guò)。知識(shí)點(diǎn):MIN 函數(shù)返回一組值中的最小值。函數(shù)語(yǔ)法 MIN (numberl , number2 ,

4、 )Numberl , number2 ,是要從中 查找最小值的 1 到 255 個(gè)數(shù)字。函數(shù)說(shuō)明參數(shù)可以是數(shù)字或者是包含數(shù)字的名稱、數(shù)組或引用。邏輯值和直接鍵入到 參數(shù)列表中代表數(shù)字的文本被計(jì)算在內(nèi)。如果參數(shù)為數(shù)組或引用，則只使用 該數(shù)組或引用 中的數(shù)字。數(shù)組或引用中的空白單元格、邏輯值或文本將被忽略。如果參數(shù)中不含數(shù)字， 則函數(shù) MIN 返回 0. 如果參數(shù)為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本，將會(huì)導(dǎo)致錯(cuò)誤。MATCH 函數(shù)返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。如果需要 找出匹配元素的位置而不是匹配元素本身，則應(yīng)該使用 MATCH 函數(shù)而不是 LOOKUP 函 數(shù)。函數(shù)語(yǔ)法 M

5、ATCH ( lookup_value ， lookup_array ， match_type ) Lookup_value 為需要在 Lookup_array 中查找的數(shù)值。例如，如果要在電話簿中查找某人的電話號(hào)碼， 則應(yīng)該將姓名作為查找值，但實(shí)際上需要的是電話號(hào)碼。 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_

6、array 中查找 lookup_value. 如果 match_type 為 1 ，函數(shù) MATCH 查找小于或等 于lookup_value 的最大數(shù)值，Lookup_array 必須按升序排列：、-2、-1、0、1、2、A-Z、 FALSE、TRUE;如果 match_type 為 0,函數(shù) MATCH 查找等于lookup_value 的第一個(gè)數(shù)值， Lookup_array 可以按任 何順序排列；如果 match_type 為 -1 ，函數(shù) MATCH 查找大于或等于 lookup_value 的最小數(shù)值， Lookup_array 必須按 降序排 列：TRUE、FALSE、Z-A、

7、2、1、0、-1、-2、，等等。如果省略match_type ，則假設(shè)為 1.函數(shù)說(shuō)明函數(shù) MATCH 返回 lookup_array 中目標(biāo)值的位置，而不是數(shù)值本身。例如，MATCH (“ b ”， “ a”，“ b ”，“ ©” 返回 2，即“ b ” 在數(shù)組 “ a”“ b”，“ c” 中的相應(yīng)位置。查找文本值時(shí)，函數(shù)MATCH不區(qū)分大小寫(xiě)字母。如果函數(shù)MATCH 查找不成功，則返回 錯(cuò)誤值 #N/A. 如果 match_type 為 0 且 lookup_value 為文 本，可以在 lookup_value 中使用通配符、問(wèn)號(hào)(？)和星號(hào)( *) 。問(wèn)號(hào)匹配任意單個(gè)字符；星號(hào)匹配任意一串字符。如果要查找實(shí)際的問(wèn)號(hào)或星號(hào)，請(qǐng)?jiān)谠撟址版I入波形符() 。步驟 07 添加年采購(gòu)量滾動(dòng)條我們前面已經(jīng)在自定義快速訪問(wèn)工具欄上添加了滾動(dòng)條按鈕，現(xiàn)在我們又要用到它。 點(diǎn)擊這個(gè)按鈕，然后在 A21 單元格拖出一個(gè)滾動(dòng)條。步驟 08 設(shè)置年采購(gòu)量滾動(dòng)條在剛剛添加的滾動(dòng)條上單擊鼠標(biāo)右鍵，在彈出的對(duì)話框中對(duì)滾動(dòng)條進(jìn)行如圖中所示的 設(shè)置。與這個(gè)滾動(dòng)條相關(guān)聯(lián)的單元格是 B19 ，設(shè)置方法是單擊文本框后面的編輯按鈕，再 用鼠標(biāo)選中 B19 單元格。單擊“確定”完成設(shè)置。步驟 09 添加并設(shè)置采購(gòu)成本滾動(dòng)條使用與上兩步相機(jī)的方法在 D21 單元格