excel函數(shù)在總承包采購(gòu)管理工作中的運(yùn)用

1、萬(wàn)方數(shù)據(jù)Excel函鼓在總最包貢購(gòu)管理工作中的運(yùn)用口李太星文獻(xiàn)棟1只碼.AExcel函數(shù)的功能是非常強(qiáng)大的，在工作中靈活地運(yùn)用它，能夠極大地減少工作量，為工作帶來(lái)事半功倍的效果.下面就通過(guò)Ex∞l函數(shù)在總承包采購(gòu)管理工作中庫(kù)存管理、庫(kù)存分析、庫(kù)存物資的處理、銷售清單的確定四個(gè)案例中的運(yùn)用介紹Excel函數(shù)的運(yùn)用.一、Excel函敏在庫(kù)存管理中實(shí)現(xiàn)“日清月結(jié)“(一)事情的起因.在工程總承包初期，由于沒(méi)有系統(tǒng)的工程管理軟件，很多作業(yè)都是手工

2、作業(yè)。而手工作業(yè)是每月月底結(jié)帳，所以準(zhǔn)確的庫(kù)存都只是在月底知道，很難把握實(shí)時(shí)的庫(kù)存信息，這顯然不能滿足工程的需要。(二)解決方案的構(gòu)思與實(shí)施.構(gòu)思:庫(kù)存工作表中每種材料應(yīng)具有唯一的明細(xì)，這樣方便查詢庫(kù)存。庫(kù)存有進(jìn)有出，故迸有一塊、出有一塊.通過(guò)設(shè)置總?cè)霂?kù)量、總出庫(kù)量之后可以得出庫(kù)存.實(shí)施:進(jìn)按合同進(jìn)，這樣在行底可以合計(jì)，以檢查數(shù)據(jù)的正確性。出按施工單位子項(xiàng)出，以進(jìn)行成本核算。總?cè)霂?kù)量列對(duì)所有合同合計(jì)，出庫(kù)量列對(duì)所有施工單位子項(xiàng)合計(jì)，庫(kù)存

3、列可以得出庫(kù)存。工作中，倉(cāng)儲(chǔ)工作人員每進(jìn)行完一筆業(yè)務(wù)，在帳目中置記之后，實(shí)時(shí)庫(kù)存立刻顯現(xiàn)出來(lái)。對(duì)于多人操作問(wèn)題，可將文件設(shè)置為共享模式，可供多人同時(shí)操作存取數(shù)據(jù).以上“日消“工作完成以后.剩下的就是“月緒“工作.在手工作業(yè)中，每月月底需對(duì)各類型材料的月增加數(shù)量、月增加金額、月減少數(shù)量、月減少金額、結(jié)存數(shù)量、結(jié)存金額數(shù)據(jù)進(jìn)行月度總結(jié).而在總承包工程中，各類型材料的明細(xì)眾多，數(shù)據(jù)動(dòng)態(tài)巨大，手工統(tǒng)計(jì)的工作量是非常繁重的，需要將同種類型材料明細(xì)

4、當(dāng)月迸迸出出的數(shù)量及金額累加。如何在Excel中解決這一問(wèn)題.也成為減輕倉(cāng)儲(chǔ)工作人員工作量的一項(xiàng)重要事情.實(shí)現(xiàn)辦法:根據(jù)各類型材料月結(jié)這一概念，首先將各類型材料進(jìn)行“廣義“的編碼。在這里對(duì)“廣義“打上引號(hào)，是因?yàn)樵诳偝邪こ讨校牧系姆N類繁多，逐一編碼的工作量很大，一時(shí)無(wú)法完成。在這里只是將同種類型材料設(shè)作同一編碼，以便統(tǒng)計(jì)使用.第二步將每月月底與手工帳目核對(duì)無(wú)誤的庫(kù)存工作表進(jìn)行備份，并將備份工作表改名為當(dāng)前年、月、日。這樣既醒目，又可

5、避免因每天操作庫(kù)存工作表不可道免造成數(shù)據(jù)損失之后無(wú)糾正來(lái)源.第三步，設(shè)置月結(jié)工作表，在這里主要是用兩258I決策“銷aI20門(mén)王軍第10期總第82期個(gè)函數(shù)SUMIF、SUMPRODUCT.月增加數(shù)量=SUMIF(庫(kù)存工作表的類型列，當(dāng)前類型，庫(kù)存工作袤的總?cè)霂?kù)量)SUMIF(上一次備份庫(kù)存表的類型列，當(dāng)前類型，上一次備份庫(kù)存袤的類型列的總?cè)霂?kù)量).意義:當(dāng)前類型材料截止到目前總的入庫(kù)量截止到上月結(jié)帳時(shí)總的入庫(kù)量，即為當(dāng)前類型材料當(dāng)月入庫(kù)

6、量.月增加金額=SUMPRODUCT((庫(kù)存工作表類型列=當(dāng)前類型)(庫(kù)存工作表總?cè)霂?kù)量列庫(kù)存工作表單價(jià)列))SUMPRODUCT((上一次備份庫(kù)存表類型列=當(dāng)前類型)(上一次備份庫(kù)存表總?cè)霂?kù)量刑上一次備份庫(kù)存表單價(jià)列))。意義:當(dāng)前類型材料截止到目前總的入庫(kù)金額當(dāng)前類型材料截止到上月結(jié)帳時(shí)總的入庫(kù)金額，即當(dāng)前類型材料當(dāng)月入庫(kù)金額。月減少數(shù)量s參考月增加數(shù)量.月減少金額2參考月增加金額.結(jié)存數(shù)量:參考月地加數(shù)量.結(jié)存金額2參考月增加金額

7、.(三)解決方案的效果.通過(guò)以上解決方案以后，當(dāng)每天的出入情況在庫(kù)存工作表中輸入后，庫(kù)存情況立即顯現(xiàn)出來(lái)，月結(jié)表中也會(huì)有所顯現(xiàn)，月底核對(duì)完庫(kù)存工作表之后，月結(jié)情況也隨之出來(lái)了.從而極大地減輕了倉(cāng)儲(chǔ)工作人員的工作量.二、Excel函撾在庫(kù)存分析中的運(yùn)用(一)事情的起因.工程總承包在進(jìn)行到中后期以后，倉(cāng)庫(kù)己庫(kù)存較多材料，而在工程中卻緊缺另外許多種材料.基于對(duì)工程結(jié)束之后庫(kù)存材料偏多的擔(dān)憂，需對(duì)庫(kù)存材料進(jìn)行一次分析，以查出哪些材料是多余的，哪

8、些材料還需要再來(lái)購(gòu)，以便于后期采購(gòu)工作的進(jìn)行以及多余材料的處理。(二)解決方案的思路.各材料的總計(jì)劃量(已有施工單位各子項(xiàng)計(jì)劃表〉即為總需求量，總?cè)霂?kù)量(己有材料庫(kù)存表，內(nèi)含各材料總?cè)霂?kù)量)未入庫(kù)量(己到貨未辦理入庫(kù)手續(xù))在途物資(即己簽合同未到貨)可視為總采購(gòu)量，通過(guò)這兩個(gè)數(shù)據(jù)的量差〈采購(gòu)量差)可以判定材料是否有多買.通過(guò)采購(gòu)量差與庫(kù)存的比較，可以知道庫(kù)存的材料是否是有用的，或是其中有多少是無(wú)用的.(三)方案實(shí)施前的準(zhǔn)備.此方案主要通

9、過(guò)Sumproduct函數(shù)進(jìn)行多條件取值，即判定三個(gè)條件:材料名稱，材質(zhì)，規(guī)格，從各計(jì)劃分表中匯總各材料計(jì)劃量與各材料總?cè)霂?kù)量作比較，以查出該材料是否有多采購(gòu)的情況，所以要對(duì)各單位各子項(xiàng)計(jì)劃分表的各種材料名稱、材質(zhì)、規(guī)格的表述方式與庫(kù)存工作表的表述方式進(jìn)行統(tǒng)一。這個(gè)工作量是很大的，在沒(méi)有統(tǒng)一的材料編碼及綜合的系統(tǒng)軟件的前提下，這是不得己的選擇.在準(zhǔn)備過(guò)程中為簡(jiǎn)化方案的難度，還清理了所有的未入庫(kù)萬(wàn)方數(shù)據(jù)量，并視所有己簽合同己執(zhí)行完，此時(shí)總

10、采購(gòu)量=總?cè)霂?kù)量。(四)解決方案的實(shí)施.首先設(shè)置庫(kù)存分析工作表框架z從庫(kù)存工作表中拷貝出庫(kù)存明細(xì)(含材料名稱、材質(zhì)、規(guī)格等信息).在其后設(shè)置總?cè)霂?kù)量列、庫(kù)存列、采購(gòu)量差列、計(jì)劃合計(jì)列及所有施工單位各子項(xiàng)列。第2步用SUMPRODUCT函數(shù)在庫(kù)存分析工作表中對(duì)庫(kù)存工作表數(shù)據(jù)動(dòng)態(tài)取值，分別取出各材料明細(xì)總?cè)霂?kù)量數(shù)據(jù)及庫(kù)存數(shù)據(jù).公式:=SUMPRODUCT((庫(kù)存工作表!材料名稱區(qū)域=當(dāng)前材料名稱滬(庫(kù)存工作表!材質(zhì)區(qū)域=當(dāng)前材質(zhì)問(wèn)庫(kù)存工作表

11、!規(guī)格區(qū)域=當(dāng)前規(guī)格)氣庫(kù)存工作表!總?cè)霂?kù)量區(qū)域泊，意義z對(duì)庫(kù)存工作表中材料名稱、材質(zhì)、規(guī)格分別等于當(dāng)前材料名稱、材質(zhì)、規(guī)格的總?cè)霂?kù)量數(shù)據(jù)取值。庫(kù)存列可參考本公式.第3步在庫(kù)存分析工作表中用SUMPRODUCT函數(shù)對(duì)各單位各子項(xiàng)計(jì)劃表各材料總計(jì)劃數(shù)據(jù)取值。第一個(gè)公式可參考第2步公式。后面列改動(dòng)引用工作表名稱即可，其它行套用得所有施工單位所有子項(xiàng)所有材料總計(jì)劃。在計(jì)劃合計(jì)列匯總各材料總計(jì)劃量，在采購(gòu)量差列設(shè)置計(jì)劃合計(jì)與總?cè)霂?kù)量的量差，于是

12、一個(gè)動(dòng)態(tài)的庫(kù)存分析工作表便產(chǎn)生了。在此基礎(chǔ)之上，通過(guò)比較庫(kù)存與采購(gòu)量差的關(guān)系，可進(jìn)一步提煉庫(kù)存分析數(shù)據(jù)，以得出各類庫(kù)存無(wú)計(jì)劃物資的分布e公式=SUMPRODUCT((材料類型區(qū)域=某種類型)8采購(gòu)量差0)(采購(gòu)量差〈庫(kù)存)(采購(gòu)量差))SUMPRODUCT((材料類型區(qū)域=某種類型)(采購(gòu)量差0)(采購(gòu)量差異庫(kù)存)(庫(kù)存))意為2某種類型材料有多采購(gòu)的時(shí)候，庫(kù)存小于多采購(gòu)量時(shí)取庫(kù)存，庫(kù)存大子等于多采購(gòu)量時(shí)取多采購(gòu)量，其和便某種類型材料總

13、體多采購(gòu)量.(五)運(yùn)用的效果.通過(guò)設(shè)置動(dòng)態(tài)庫(kù)存分析工作衰，節(jié)省了統(tǒng)計(jì)全廠各種材料總計(jì)劃量的時(shí)間。最重要的是，通過(guò)它，能夠?qū)?kù)存材料的狀況有了一個(gè)詳細(xì)的了解，對(duì)后期材料的采購(gòu)以及多余材料的處理有了一個(gè)參考性的依據(jù)和指導(dǎo)性的方向。三、Excel函撒在庫(kù)存物資處理中的運(yùn)用(一)事情的起因.工程進(jìn)行到后期以后，倉(cāng)庫(kù)有一些庫(kù)存，需要查明這些庫(kù)存中哪些是有計(jì)劃的，又是哪些單位哪些子項(xiàng)未用的，以利于庫(kù)存物資的處理。(二)方案設(shè)計(jì).首先在各單位各子項(xiàng)計(jì)

14、劃表中設(shè)置己領(lǐng)用歹IJ、量差列、庫(kù)存列，。通過(guò)對(duì)庫(kù)存工作表中庫(kù)存列和當(dāng)前單位及子項(xiàng)出庫(kù)數(shù)據(jù)的取值可以取出庫(kù)存數(shù)據(jù)和己領(lǐng)用數(shù)據(jù)，量差設(shè)置可得。通過(guò)設(shè)置，可以看出各單位各子項(xiàng)未領(lǐng)用材料明細(xì)庫(kù)存情況。第2步設(shè)置各單位各子項(xiàng)未領(lǐng)用材料庫(kù)存總表，以便于總體掌握庫(kù)存物資的未領(lǐng)用情況.列出所有單位及其工程子項(xiàng)，并將鋼材類材料細(xì)分為線材，板材，型材，管材四類，以便于分別掌握它們總的未領(lǐng)用庫(kù)存情況.第3步匯總各單位各子項(xiàng)計(jì)劃表中數(shù)據(jù)。由于在總表中鋼材類材

15、料有細(xì)分，在這里也必須細(xì)分。通過(guò)在各計(jì)劃表中增加材料類別列，分別指定1，2.3.4對(duì)應(yīng)上述材料類別予以解決統(tǒng)計(jì)問(wèn)題。第4步:在總表中對(duì)各計(jì)劃分表計(jì)劃數(shù)據(jù)及己領(lǐng)用數(shù)據(jù)取值。取值用Sumif函數(shù)即可，由于其引用的工作表名稱可由總表中施工單位子項(xiàng)編號(hào)組成(與各計(jì)劃分表名稱表述方式一致).所以考慮嵌套使用lNDlRECT函數(shù)來(lái)動(dòng)態(tài)引用各個(gè)計(jì)劃分表以省去改動(dòng)參數(shù)。(二)i!.用的效采.通過(guò)此方案設(shè)計(jì)，對(duì)于詳細(xì)的材料未領(lǐng)用庫(kù)存情況及總體的未領(lǐng)用庫(kù)存

16、情況均有了一個(gè)充分的了解，對(duì)于庫(kù)存物資的處理也有了一個(gè)參考的依據(jù).四、Excel函數(shù)在設(shè)備銷售清單的確定中的運(yùn)用(一)事情的起因工程收尾階段，所采購(gòu)的設(shè)備在經(jīng)過(guò)多次銷售，并經(jīng)轉(zhuǎn)手以后已不知具體哪些設(shè)備未銷售了，只知己銷售金額與總合同金額是對(duì)不上的，需要清理出未銷售的設(shè)備清單.(二)方案分析.這一問(wèn)題初看起來(lái)時(shí)猶如是大海撈針的，因?yàn)榭偟脑O(shè)備合同有500多個(gè)，總的設(shè)備明細(xì)有6000多項(xiàng)，而且有些合同只銷售了部分，這就更加增加了問(wèn)題的難度。己

17、有的素材:1)各次己銷售設(shè)備清單〈含合同號(hào)、設(shè)備名稱、合同金額等等〉。2)合同資金支付臺(tái)帳〈含合同號(hào)、合同金額等等〉。3)每一合同具有Wd文本，內(nèi)含合同金額及明細(xì)。(三)解決方案的思路.用合同金額減去銷售清單中各合同己銷售金額，應(yīng)該可以看出各合同己銷售或未銷售的情況。(四)實(shí)施步驟.第一步匯總各次己銷售設(shè)備清單，并拷貝合并的清單中合同號(hào)列至新增工作表sheetl.通過(guò)數(shù)據(jù)刪除重復(fù)項(xiàng)~項(xiàng)CExce12007)刪除重復(fù)I頁(yè)，保留唯一合同號(hào)。

18、第二步在sheetl中用Sumif函數(shù)通過(guò)對(duì)己銷售設(shè)備清單匯總得出己銷售清單中各合同銷售額。第三步拷貝合同資金支付臺(tái)帳中合同編號(hào)、合同金額列至sheetl，將兩份消單分別排序，可大體對(duì)應(yīng)起來(lái)。在合同資金區(qū)域后用Sumif函數(shù)取出對(duì)應(yīng)合同的己銷售金額，可得出差額，此差額即為各合同己銷售或未銷售的情況。在其中會(huì)涉及將兩份清單中合同編號(hào)表述方式統(tǒng)一的問(wèn)題，統(tǒng)一之后即可.(五)運(yùn)用的效果.通過(guò)實(shí)施之后，有問(wèn)題的合同己詳細(xì)定位出來(lái).通過(guò)它可以對(duì)那

19、些部分未銷售或完全未銷售的合同擬定設(shè)備銷售清單，為設(shè)備的最終完全銷售鋪平了道路.五、結(jié)束語(yǔ)在工作中靈活運(yùn)用Excel函數(shù)，能夠極大地提升工作的效率。經(jīng)過(guò)Excel函數(shù)處理數(shù)據(jù)統(tǒng)計(jì)中的一些問(wèn)題，能夠?yàn)楣こ坦芾韼?lái)很大的幫助?？?作者單位:中銘國(guó)際遵義工程總承包項(xiàng)目部)參考文獻(xiàn)z川胡順起.豐曉君，周茂山等Excel函數(shù)在農(nóng)業(yè)氣象理~埔工作中的巧用.安徽農(nóng)業(yè)科學(xué).2010.38(2):837839[21吳豐革斗，蘇厚勤，朱~群.Excel對(duì)象