326Excel應(yīng)用物料需求核定與采購計(jì)劃編制

1、 excel應(yīng)用(物料需求核定與采購計(jì)劃編制) 【試驗(yàn)?zāi)康摹?. 掌握工作區(qū)地建立與應(yīng)用2. 掌握篩選無重復(fù)數(shù)據(jù)3. 運(yùn)用sumproduct()行數(shù)分類匯總【實(shí)驗(yàn)內(nèi)容】1. 工作區(qū)的建立與使用2. 物料需求核定3. 篩選無重復(fù)數(shù)據(jù)(物料編碼)4. 分類匯總(分需求單位匯總)5. 分類匯總(分用途類別匯總)6. 采購計(jì)劃編制【實(shí)驗(yàn)步驟】1. 工作區(qū)的建立與使用1.1. 文件準(zhǔn)備1.1.1. 在優(yōu)盤（或當(dāng)?shù)赜脖P）建立“采購計(jì)劃”文件夾1.1.2. 將文件“物料平衡表.xls”移至“采購計(jì)劃”文件夾1.1.3. 打開“物料平衡表.xls”，選擇“物料編碼”工作表，右擊工作表標(biāo)簽，彈出菜單1.1.4

2、. 選擇“移動(dòng)和復(fù)制工作表”，彈出對(duì)話框，選擇“新建工作簿”1.1.5. 將新建工作簿“book1.xls”保存命名為“物料編碼,xls” （保存到“采購計(jì)劃”文件夾，下同）1.1.6. 新建工作簿，保存命名為“物料需求核算.xls” 1.1.7. 新建工作簿，保存命名為“采購計(jì)劃.xls”1.1.8. 關(guān)閉、保存所有excel文件1.2. 建立工作區(qū)1.2.1. 打開“物料編碼,xls”、“物料平衡表.xls”、“物料需求核算.xls”、“采購計(jì)劃.xls”1.2.2. 選擇菜單“文件/保存工作區(qū)”1.2.3. 保存命名為“采購計(jì)劃.xlw”1.3. 打開工作區(qū)1.3.1. 打開“采購計(jì)劃.

3、xlw”excel將同時(shí)打開相關(guān)文件1.3.2. 從“物料平衡表.xls”復(fù)制名稱定義code、name、unit的引用位置（觀察其與原來定義的區(qū)別），在物料需求核算.xls、采購計(jì)劃.xls定義名稱code、name、unit。2. 物料需求核定2.1. 進(jìn)入物料需求核算.xls2.2. 將下表拷貝至sheet1，工作表重命名為“生產(chǎn)物料需求”表（1.3a）2008年生產(chǎn)物料需求核算表需求單位產(chǎn)品計(jì)劃產(chǎn)量單位物料需求消耗定額定額單位物料單位需求量物料編碼名稱與規(guī)格一分廠甲2500件鋼錠500kg/件二分廠.乙1000件鋼錠1000kg/件三分廠丙1800件工字鋼500kg/件三分廠丙刀具15

4、00三分廠丙軸承/55mm2500u 引用物料編碼、物料單位（下同）u 計(jì)算鋼錠、鋼錠、工字鋼的需求量（消耗定額×計(jì)劃產(chǎn)量）2.3. 將下表拷貝至sheet2，工作表重命名為“基建物料需求”表（1.3b）2008年基建物料需求計(jì)劃表需求單位物料需求物料單位需求量編碼名稱與規(guī)格基建科木材/原木100水泥/4254502.4. 將下表拷貝至sheet3，工作表重命名為“維修物料需求”表（1.3）2008年維修物料需求計(jì)劃表需求單位物料需求物料單位需求量物料編碼名稱與規(guī)格一分廠薄鋼板/2.5mm200一分廠槽鋼/12200一分廠木材/原木80一分廠水泥/425350一分廠軸承/55mm80

5、0一分廠銅芯橡皮線/by502000一分廠工作服/棉布600二分廠銅芯橡皮線/by502000二分廠水泥/425150二分廠軸承/55mm400二分廠工作服/棉布110三分廠銅芯橡皮線/by501500三分廠薄鋼板/2.5mm110三分廠電石/一級(jí)80三分廠水泥/425120三分廠木材/原木170四分廠角鋼/10300四分廠槽鋼/12120四分廠木材/原木110四分廠水泥/425180四分廠電石/一級(jí)60四分廠工作服/棉布1402.5. 插入工作表，重命名為“技改物料需求”，移至最后，拷貝下表：表（1.3）2008年技改項(xiàng)目物料需求計(jì)劃表維修項(xiàng)目物料需求物料單位需求量編碼名稱與規(guī)格1、高爐大修

6、薄鋼板/2.5mm801、高爐大修槽鋼/12901、高爐大修水泥/425601、高爐大修電石/一級(jí)102、廠房大修木材/原木1202、廠房大修水泥/425402、廠房大修角鋼/10502、廠房大修電石/一級(jí)203、余熱工程木材/原木403、余熱工程水泥/4251504、福利設(shè)施木材/原木4004、福利設(shè)施水泥/4252004、福利設(shè)施角鋼/10302.6. 插入工作表，重命名為“物料需求匯總1”，移至最后，拷貝下表：表(1.3f)2008年物料需求匯總表(1)序號(hào)用途類別物料編碼物料名稱及規(guī)格計(jì)量單位分單位需求量備注合計(jì)一分廠二分廠三分廠四分廠2.7. 插入工作表，重命名為“物料需求匯總2”，

7、移至最后，拷貝下表：表(1.3gf)2008年物料需求匯總表(2)序號(hào)物料編碼物料名稱及規(guī)格計(jì)量單位按用途類別需求量備注合計(jì)生產(chǎn)物料基建物料維修物料技改物料3. 篩選無重復(fù)數(shù)據(jù)(物料編碼)3.1. 選擇“生產(chǎn)物料需求”工作表，選中物料編碼區(qū)域3.2. 選擇菜單“數(shù)據(jù)/篩選/高級(jí)篩選”3.3. 在“高級(jí)篩選”對(duì)話框中，選中“講篩選結(jié)果復(fù)制到其他位置”，在工作表中選定適當(dāng)?shù)膯卧瘢x中“選擇不重復(fù)的紀(jì)錄”，選擇“確定”，excel將自動(dòng)顯示選擇結(jié)果3.4. 對(duì)“基建物料需求”、“維修物料需求”、“技改物料需求”工作表重復(fù)3.2、3.3步驟。3.5. 將篩選結(jié)果復(fù)制到“物料需求匯總1”，并填充用料類

8、別；3.6. 引用“物料名稱及規(guī)格”，“計(jì)量單位”4. 分類匯總(分需求單位匯總)4.1. 分單位需求量（“生產(chǎn)物料/維修物料”）合計(jì)4.1.1. 選中“物料需求匯總1”工作表4.1.2. 在相應(yīng)區(qū)域的左上角，插入函數(shù)sumproduct()，確定參數(shù)。u 參數(shù)aeeay1：選中“生產(chǎn)物料需求/維修物料需求”工作表的物料編碼區(qū)域（絕對(duì)引用），輸入“=”，選中單元格所在行的物料編碼（列絕對(duì)引用）；u 參數(shù)aeeay2：選中“生產(chǎn)物料需求/維修物料需求”工作表的需求單位區(qū)域（絕對(duì)引用），輸入“=”，選中單元格所在列的需求單位（行絕對(duì)引用）；u 參數(shù)aeeay3：選中“生產(chǎn)物料需求/維修物料需求”工

9、作表的需求量區(qū)域（絕對(duì)引用）；u 選擇“確定”后，在輯欄內(nèi)分別在3個(gè)參數(shù)兩端加括號(hào)，將其間的“，”改成“*”號(hào)。u 按住“ctrl+shift”鍵，點(diǎn)擊“enter”鍵。4.1.3. 將所設(shè)置的公式填充至相應(yīng)的區(qū)域。4.1.4. 設(shè)置單元格格式，當(dāng)數(shù)據(jù)為0時(shí)，不顯示。4.1.5. “生產(chǎn)物料/維修物料”區(qū)域的“合計(jì)”，分別對(duì)對(duì)分單位需求量求和。4.2. 不分需求單位的“基建物料/技改物料”的“合計(jì)”：4.2.1. 分別在“基建物料/技改物料”的“合計(jì)”區(qū)域左上角，插入函數(shù)sumproduct()，確定參數(shù)。u 參數(shù)aeeay1：選中“基建物料需求/技改物料需求”工作表的物料編碼區(qū)域（絕對(duì)引用）

10、，輸入“=”，選中單元格所在行的物料編碼（列絕對(duì)引用）；u 參數(shù)aeeay3：選中“基建物料需求/技改物料需求”工作表的需求量區(qū)域（絕對(duì)引用）；u 選擇確定后，在輯欄內(nèi)分別在2個(gè)參數(shù)兩端加括號(hào)，將其間的“，”改成“*”u 按住“ctrl+shift”鍵，點(diǎn)擊“enter”鍵。4.2.2. 將所設(shè)置的公式填充至相應(yīng)的區(qū)域。5. 分類匯總(分用途類別匯總)5.1. 選中“物料需求匯總1”工作表5.2. 參照步驟3.1、3.2、3.3，篩選物料編碼(無重復(fù)數(shù)據(jù))5.3. 將篩選的物料編碼拷貝至“物料需求匯總2”工作表的相應(yīng)位置5.4. 選擇“物料需求匯總2”工作表5.5. 引用“物料名稱及規(guī)格”，“

11、計(jì)量單位”5.6. 在“按用途類別需求量”區(qū)域的左上角，插入函數(shù)sumproduct()，確定參數(shù)。u 參數(shù)aeeay1：選中“物料需求匯總1”工作表的物料編碼區(qū)域（絕對(duì)引用），輸入“=”，選中單元格所在行的物料編碼（列絕對(duì)引用）；u 參數(shù)aeeay2：選中“物料需求匯總1”工作表的用途類別區(qū)域（絕對(duì)引用），輸入“=”，選中單元格所在列的用途類別（行絕對(duì)引用）；u 參數(shù)aeeay3：選中“物料需求匯總1”工作表的需求量“合計(jì)”區(qū)域（絕對(duì)引用）；u 選擇確定后，在輯欄內(nèi)分別在3個(gè)參數(shù)兩端加括號(hào)，將其間的“，”改成“*”號(hào)。u 按住“ctrl+shift”鍵，點(diǎn)擊“enter”鍵。6. 采購計(jì)劃編

12、制6.1. 選擇“采購計(jì)劃.xls”工作簿6.2. “期末儲(chǔ)備”核定6.2.1. 將下表拷貝至“sheet1”，工作表重命名為“期末儲(chǔ)備”表(1.4a)2008年底物料儲(chǔ)備量估計(jì)物料編碼物料名稱及規(guī)格計(jì)量單位期末儲(chǔ)備量備注鋼錠薄鋼板/2.5mm200工字鋼500槽鋼/12150角鋼/10180木材/原木250水泥/425400電石/一級(jí)120工作服/棉布500刀具100銅芯橡皮線/by50600軸承/55mm600【備注】鋼錠總需要量2250t，全年按360天計(jì)算，供應(yīng)周期為60天，供應(yīng)期平均臨時(shí)需求量(保險(xiǎn)儲(chǔ)備量)為100t；按照下列公式計(jì)算期末儲(chǔ)備量，填入上表。日需求量 總需要量日歷天數(shù)周

13、轉(zhuǎn)儲(chǔ)備量 日需求量×供應(yīng)周期期末儲(chǔ)備量 周轉(zhuǎn)儲(chǔ)備量2 保險(xiǎn)儲(chǔ)備量 6.2.2. 引用“物料編碼”，“計(jì)量單位”6.3. 采購參數(shù)6.3.1. 將下表拷貝至“sheet1”，工作表重命名為“物料單價(jià)及訂購次數(shù)”表(1.4b)2008年度物料單價(jià)及訂購次數(shù)一覽表物料類別物料編碼物料名稱及規(guī)格計(jì)量單位單價(jià)(元 )訂購次數(shù)鋼材鋼錠250015鋼材薄鋼板/2.5mm400020鋼材工字鋼300010鋼材槽鋼/12320010鋼材角鋼/10380010建材木材/原木260020建材水泥/42560020防護(hù)工作服/棉布12012工具刀具4012電料銅芯橡皮線/by50816軸承軸承/55mm50

14、20化工電石/一級(jí)800126.3.2. 引用“物料編碼”，“計(jì)量單位”6.4. 采購計(jì)劃編制6.4.1. 將下表拷貝至“sheet3”，工作表重命名為“物料采購計(jì)劃表”表(1.5)2008年物料采購計(jì)劃表序號(hào)物料編碼物料名稱及規(guī)格計(jì)量單位期初庫存計(jì) 劃需求量期末儲(chǔ)備計(jì)劃采購量單價(jià)(元)訂購次數(shù)一 次訂購量訂購金額(元)×6.4.2. 將“物料需求核算.xls”中的“物料需求匯總2”工作表的物料編碼拷貝至“物料采購計(jì)劃表”的物料編碼6.4.3. 填充序號(hào)6.4.4. 引用“物料名稱及規(guī)格”，“計(jì)量單位”6.4.5. 引用數(shù)據(jù)“期初庫存”、“計(jì)劃需求量”、期末儲(chǔ)備、單價(jià)(元)、訂購次數(shù)u 在相應(yīng)區(qū)域中插入函數(shù)index(數(shù)據(jù)引用區(qū)域，match(物料編碼單元格,物料編碼引用區(qū)域，0)；其中“物料編碼單元格”指向物料編碼，相對(duì)引用；數(shù)據(jù)引用區(qū)域、物料編碼引用區(qū)域按下