王 明 鏡

(福建華航建設(shè)集團有限公司廈門分公司，福建 廈門 361004)

·計算機技術(shù)及應(yīng)用·

Excel軟件在工程進度款計算及造價結(jié)算中的應(yīng)用

王 明 鏡

(福建華航建設(shè)集團有限公司廈門分公司，福建 廈門 361004)

介紹了進度計算和工程造價結(jié)算的一般方法，提出了把建設(shè)工程合同報價書轉(zhuǎn)化成Excel電子表格，通過改造該電子表格，使得工程進度款計算文件或工程造價結(jié)算文件的制作不必依賴電腦計價軟件，并能編排成直觀易懂的全費用清單格式的文件，以簡化造價文件編制工作。

Excel應(yīng)用，工程量清單，進度款，結(jié)算，全費用清單

1 概述

作者在工程建設(shè)項目的管理實踐中，通過解析了規(guī)范格式的工程造價的構(gòu)成，使用Excel電子表格軟件，用一種相對于大多數(shù)房屋建筑工程項目的通常做法而言全新的方法，對按照國家規(guī)范GB 50500—2008建設(shè)工程工程量清單計價規(guī)范制作的合同報價書進行改造,并制作成簡單易用的全費用清單格式的Excel電子表格，用于工程進度款計算和工程造價結(jié)算，使進度款和造價結(jié)算的申報和審核工作得到簡化。

2 進度款計算和工程造價結(jié)算的一般方法

工程進度款計算和工程造價結(jié)算以合同報價書為依據(jù)和藍本。合同報價書是建筑工程合同文件的構(gòu)成部分之一；它通常是由電腦計價軟件生成、打印的，它的各項內(nèi)容的編排(如項目的編號、名稱及排列順序，單價及合價組成，各種匯總，版面分頁等等)有固定的格式，其表格形式與GB 50500—2008建設(shè)工程工程量清單計價規(guī)范所列表格相同[1]。每份報價書包含20多種表格，總頁數(shù)都在200頁以上。

因為計算項目繁多，所以工程進度款計算文件或工程造價結(jié)算文件(以下簡稱“造價文件”)的編排格式要與合同報價書一致，才便于各方人員的校對、審核。也就是說，每份造價文件實際上就是一份完整的“報價書”。這種格式的造價文件篇幅巨大，各條目之間的算術(shù)關(guān)系不直觀，查閱、審核時十分費時費力。

為了快速地制作、審核其編排格式與合同報價書相同的造價文件，通常都是使用與制作合同報價書所用相同的計價軟件來操作。這就要求施工、監(jiān)理、建設(shè)各單位相關(guān)人員都必須配備相同的計價軟件，并且要通過學(xué)習(xí)、培訓(xùn)掌握該軟件的操作；為此，各單位往往還需要為每個工程建設(shè)項目配置專門的工程造價人員，從而增加成本支出。

如果可以只使用Excel軟件而不必使用計價軟件來編制造價文件，那么，一般工程人員通過簡單的電腦操作就可以完成造價文件編制的主要工作，而不必勞煩專門的工程造價人員。

3 使用Excel軟件編制“工程量清單”格式的造價文件

如果沒有安裝相應(yīng)的計價軟件，則由計價軟件生成的合同報價書電子版無法直接在電腦中打開。可以利用計價軟件的導(dǎo)出Excel電子表格即“xls(或xlsx)文件”的功能，將合同報價書轉(zhuǎn)為Excel文件。這樣，電腦只要安裝了極為普通常用的Excel或WPS軟件，就可以打開和操作合同報價書電子版。

但是，合同報價書如果只是簡單地轉(zhuǎn)為Excel文件，是無法用來制作造價文件的。這是因為：1)轉(zhuǎn)換后的Excel表格內(nèi)的“數(shù)字”其實是文本格式的阿拉伯字符，不能直接用于Excel計算。2)轉(zhuǎn)換后的Excel表格內(nèi)數(shù)字的算術(shù)關(guān)系丟失了，不能實現(xiàn)自動計算。

所以，將合同報價書導(dǎo)出為Excel電子表格格式的文件后，還必須對其進行改造，才能用于后續(xù)施工過程中造價文件的制作。改造的具體步驟和方法如下(以Excel 2010的操作[2]為例)。

3.1 把各工作表中文本格式的阿拉伯字符轉(zhuǎn)換為數(shù)字

1)先使用“另存為”命令后再繼續(xù)后面的操作，因為合同報價書原始文件很可能需要用于查對，要避免被更改。

2)在合同報價書(.xls或.xlsx文件)工作簿的某個工作表中，選中需要轉(zhuǎn)換的單元格區(qū)域；這時選中區(qū)域的左上角的左側(cè)會出現(xiàn)一個“！”標記；將鼠標移近“！”標記，在標記的右邊會出現(xiàn)一個向下的小黑三角；點擊小黑三角，彈出下拉菜單；點擊下拉菜單上的“轉(zhuǎn)換為數(shù)字”。

3)在所有工作表中重復(fù)上一步操作，把合同報價書中所有文本格式的阿拉伯字符轉(zhuǎn)換為阿拉伯數(shù)字。

a.選中工作表中需要去除單元格中換行符的區(qū)域。b.同時按住Ctrl鍵和F鍵，彈出“查找和替換”對話框。c.點擊“替換”選項卡，在“查找內(nèi)容”后面的文本框里點擊鼠標，清空該文本框里的內(nèi)容，將光標停留在此處。d.按住Alt鍵，同時用數(shù)字小鍵盤輸入“10”(這時“查找內(nèi)容”后面的文本框里不會顯示任何內(nèi)容)，松開Alt鍵。e.清空“替換為”后面的文本框里的內(nèi)容。f.點擊“全部替換”按鈕。

在所有工作表中重復(fù)上述操作，使需要恢復(fù)算術(shù)關(guān)系的所有單元格里的阿拉伯數(shù)字都可以用于算術(shù)運算。上述操作也可以去除一些沒必要手動分行排列的文本單元格(如“項目名稱”和“項目特征描述”單元格)中的換行符，以方便調(diào)整行高和列寬。

3.2 恢復(fù)每個工作表中各級匯總數(shù)字的算術(shù)關(guān)系

3.2.1 首先明確工程造價的構(gòu)成

工程造價=各單項工程造價之和。單項工程造價=各單位工程造價之和。單位工程造價=①分部分項工程費+②措施項目費+③其他項目費(如有發(fā)生)+④規(guī)費+⑤稅金。其中：

①分部分項工程費=∑人工費+∑材料費+∑機械費+∑企管費+∑利潤+∑風險費(分部分項工程費也就是合同報價書中“分部分項工程量清單與計價表”(未列出綜合單價的構(gòu)成)或“分部分項工程量清單綜合單價分析計算表”(列出了綜合單價的構(gòu)成)中各項目的“合價”之累計)。

②措施項目費=措施項目費(一)(也即“通用措施項目費”)+措施項目費(二)(也即“專業(yè)措施項目費”)。

措施項目費(一)=文明施工費+安全施工費+臨時設(shè)施費+夜間施工費+已完工程及設(shè)備保護費+風雨季施工增加費+生產(chǎn)工具用具使用費+工程點交、場地清理費(措施項目費(一)的各子項均以分部分項工程費總計為計算基礎(chǔ)，按一定費率計取。其中文明施工費、安全施工費、臨時設(shè)施費按現(xiàn)行規(guī)定屬不可競爭項目)。

措施項目費(二)=環(huán)境保護費+混凝土、鋼筋混凝土模板及支架費+腳手架費+垂直運輸機械費+大型機械設(shè)備進出場及安拆費+大型機械設(shè)備基礎(chǔ)費+大型機械設(shè)備檢測費+施工排水降水費+其他措施費(措施項目費(二)的各子項按實際工程量計算造價。其中環(huán)境保護費按現(xiàn)行規(guī)定屬不可競爭項目)。

③其他項目費(如有發(fā)生)=暫列金額+專業(yè)工程暫估價+計日工+總承包服務(wù)費。

④規(guī)費=工程排污費+勞保費+危險作業(yè)意外傷害保險費。

⑤稅金=(①+②+③+④)×稅率。

單位工程造價各費用構(gòu)成之間的算術(shù)關(guān)系示例見表1。

3.2.2 設(shè)置Excel工作表中單元格之間的算術(shù)關(guān)系

明確了工程造價的構(gòu)成后，很容易就可以知道并設(shè)置各單元格之間的算術(shù)關(guān)系，即設(shè)置Excel單元格公式。例如：

在表1中，設(shè)置E5單元格的公式為E5=SUM(E6∶E15)。完成此設(shè)置可以通過以下不同的操作來完成：1)雙擊E5單元格，在E5單元格中或表格頂部的編輯欄公式框中輸入字符“=SUM(E6∶E15)”。2)點擊E5單元格，再點擊“∑自動求和”命令按鈕，選取E6～E15單元格區(qū)域。類似地，設(shè)置C42=E5+E16+E34+E37，E43=E5+E16+E34+E37+E41，等等。

又如：在表2中，設(shè)置M13=E13*L13，M12=SUM(M13∶M18)。

在設(shè)置單元格公式時應(yīng)注意一個問題：進度款計算或工程造價結(jié)算時不會變化的因素不要設(shè)置公式。因為在進度款計算或工程造價結(jié)算時，各分部分項工程項目(以下亦稱“合并項”，通常都包含若干子項)單價是不變的，變化的只是合并項所包含的子項的工程量，合并項(如表2中第12行)的數(shù)量是根據(jù)其“合價”和既定的“綜合單價”反算出來的。所以，不管某個合并項只包含一個子項或者包含多個子項，在設(shè)置其數(shù)量、綜合單價、合價的算術(shù)關(guān)系時，要假定其數(shù)量是未知的，對數(shù)量設(shè)置公式，即設(shè)置“數(shù)量=合價÷綜合單價”“合價=∑子項合價”。例如，在表2中，要設(shè)置E12=M12/L12，M12=SUM(M13∶M18)；而不要設(shè)置L12=M12/E12。

在工作表中設(shè)置Excel單元格公式時，可以利用Excel的復(fù)制粘貼和填充柄功能，讓具有相同規(guī)律公式的單元格快速自動地填充公式，還可以充分利用Excel的函數(shù)、篩選、排序功能，以加快完成設(shè)置公式的工作。

跨工作表設(shè)置單元格公式時，進行以下操作即可：1)點擊需要設(shè)置公式的單元格，輸入“=”號。2)點擊要進行算術(shù)運算的第一個單元格(在同一個工作表或另一個工作表中)。3)輸入運算符號，點擊下一個要進行算術(shù)運算的單元格(在同一個工作表或另一個工作表中)。4)重復(fù)上一步驟，直至點擊完所有要進行算術(shù)運算的單元格。5)在編輯欄公式框中修正運算符號、完善公式，按回車鍵。

在合同報價書的每個工作表中進行類似操作，完成各單元格算術(shù)關(guān)系的設(shè)置。

3.3 進一步改造工作表

在編制造價文件的實際應(yīng)用中，還必須對合同報價書中的工作表進一步改造。比如，表2中的“數(shù)量”一列，在實際應(yīng)用時應(yīng)為“本期數(shù)量”。如果用于編制進度款造價文件，可以在“數(shù)量(即清單工程量)”列后插入“截止上期累計完成量”“本期乙方申報量”“本期乙方審批金額”“本期監(jiān)理審核量”“本期監(jiān)理審批金額”“本期甲方審批量”“本期甲方審批金額”各列；在“合價”列后插入“截止本期累計完成量”“截止本期累計完成百分比”“截止本期累計完成金額”各列；按各列的算術(shù)關(guān)系設(shè)置公式。這樣便于清晰直觀地核驗填報數(shù)字，以及從財務(wù)方面掌握工程進展情況。插入的各列可視需要予以隱藏或顯示。示例如表3所示。

表3中，“截止上期累計完成量”“本期乙方申報量”“本期監(jiān)理審核量”“本期甲方審批量”必須手動填寫，其中“截止上期累計完成量”可以從上一期(比如2014年10月份)的“截止本期累計完成量”列復(fù)制、粘貼而得，但要注意，必須在撤銷工作表保護、篩選項全選、沒有隱藏行的情況下進行，才能避免錯誤。

如果用于編制結(jié)算文件，在“數(shù)量(即“清單工程量”)”列后面插入用于比較的“結(jié)算工程量”列就可以了。

3.4 保護工作表

完成設(shè)置公式和改造工作表后，應(yīng)進行“保護工作表”的操作，以防所設(shè)置的公式和表格格式在造價文件申報填寫、審核的過程中因各種原因而被破壞。具體操作如下：

點擊工作表左上角的方框全選工作表，點擊右鍵，點擊“設(shè)置單元格格式”；點“保護”選項卡，清空“鎖定”“隱藏”復(fù)選框，按“確定”。

選取整個表格所在的單元格區(qū)域，點擊右鍵，點擊“設(shè)置單元格格式”；點“保護”選項卡，選中“鎖定”復(fù)選框，按“確定”。

選擇表格中不能鎖定的單元格區(qū)域，如表3中“本期乙方申報量”“本期監(jiān)理審核量”“本期甲方審批量”各列的子項。對于行列數(shù)量較多的大表格，如表3所示，此操作可以利用“篩選”功能來簡化：選取表格所在單元格區(qū)域，點擊“數(shù)據(jù)”功能選項卡，再點擊“篩選”圖標按鈕；點擊表頭中“序號”右下角的黑三角，點擊“文本篩選”“包含”，在文本框中輸入“(”符號，點擊“確定”，這樣，所有子項(其序號都包含“(”符號)都被篩選出來了；選取這些子項單元格區(qū)域，點擊右鍵，點擊“設(shè)置單元格格式”，再點“保護”選項卡，清空“鎖定”“隱藏”復(fù)選框，按“確定”。

要使單元格“鎖定”“隱藏”的特性生效，還要進行以下操作：點擊“審閱”功能選項卡，再點擊“保護工作表”圖標按鈕，輸入所要設(shè)置的密碼，按確定，再輸入一遍密碼，按“確定”。

保護工作表的密碼通常由最終審定者如建設(shè)單位造價負責人員保管。

3.5 完成“工程量清單”格式的造價文件的編制

經(jīng)過以上步驟的操作，以報價書作為藍本來進行編制改造而得的造價文件就完成了；其中需要進行算術(shù)運算的單元格可以實現(xiàn)自動計算；編制某期造價文件時只需要以前一期的造價文件為模板，修改工程量(各單價和費率保持不變)就可以自動得出造價。

實際操作時，可按以下步驟進行(以進度款計算為例)：1)打開上一期進度款造價文件，另存，以上一期進度款造價文件為藍本制作本期進度款造價文件；2)撤銷工作表保護、取消篩選、取消隱藏行；選取“截止本期累計完成量”列下面的數(shù)據(jù)，用右鍵拖動到“截止上期累計完成量”，點“僅復(fù)制數(shù)值”，即把上一期的累計數(shù)正確、快速地填好；這個步驟一定要先完成再繼續(xù)后面的步驟；3)在表3中按“序號”列篩選出各個子項(即序號中包含文本“(”的項目)；4)選取篩選后的“本期乙方申報量”“本期監(jiān)理審核量”“本期甲方審批量”區(qū)域，清除內(nèi)容，即清除上一期的申報數(shù)據(jù)；5)取消篩選、取消隱藏行、保護工作表；依次交由申報人(乙方)、審核人(監(jiān)理)、審批人(甲方)填寫。

但“工程量清單”格式的造價文件和合同報價文件相似，各條目之間的算術(shù)關(guān)系(造價構(gòu)成)不直觀，篇幅巨大，有失工作的簡便和高效。

4 使用Excel軟件編制“全費用清單”格式的造價文件

“全費用清單”計價不是我國的現(xiàn)行規(guī)范，“全費用清單”目前沒有統(tǒng)一的標準格式，它是指針對分部分項工程每一個條目及其子目列出所有造價構(gòu)成的計價清單；每個條目或子目對應(yīng)的單價是已經(jīng)囊括了所有成本費用(分部分項工程費、措施項目費、其他項目費、規(guī)費、稅金)的“全費用單價”；累計所有子目對應(yīng)的“合價”，就得出總造價。而“工程量清單”按GB50500—2008建設(shè)工程工程量清單計價規(guī)范的定義是“建設(shè)工程的分部分項工程項目、措施項目、其他項目、規(guī)費項目和稅金項目的名稱和相應(yīng)數(shù)量等的明細清單”?！叭M用清單”和“工程量清單”的根本區(qū)別是：前者先分再合(先計算每個項目的造價，再合并各個項目的造價得出總造價)，后者是合而不分(其計算程序見表1)；前者篇幅小、算術(shù)關(guān)系清晰、直觀、簡單，后者篇幅大、算術(shù)關(guān)系模糊、抽象、復(fù)雜。

GB50500—2008建設(shè)工程工程量清單計價規(guī)范只在第1.0.3條規(guī)定“全部使用國有資金投資或國有資金投資為主的工程建設(shè)項目，必須采用工程量清單計價”，并未限定非國有資金投資的工程建設(shè)項目也必須采用工程量清單計價。所以，為了提高工作效率，對非國有資金投資項目而言，在實際工作中完全可以把前述“工程量清單”格式的造價文件改造成“全費用清單”格式的造價文件，使所編制的Excel表格更有實用價值。編制“全費用清單”的步驟如下。

4.1 插入體現(xiàn)全費用單價各費用構(gòu)成的列

以前面的表3為基礎(chǔ)，在“綜合單價”列后面插入體現(xiàn)項目全費用單價各費用構(gòu)成的列(見表4)。

4.2 插入體現(xiàn)全費用合價各費用構(gòu)成的列

以前面的表3為基礎(chǔ)，在“截止本期累計完成金額”(即“全費用合價”)列后面插入體現(xiàn)項目全費用合價各費用構(gòu)成的列(見表5)，這些列里的數(shù)據(jù)須用于匯總統(tǒng)計。

4.3 設(shè)置公式

4.3.1 按造價構(gòu)成的算術(shù)關(guān)系設(shè)置單元格公式

按照前述造價構(gòu)成的算術(shù)關(guān)系(如表1所示)設(shè)置單元格公式。例如，在表4中：

文明施工費單價=綜合單價×1.13%。工程點交、場地清理費單價=綜合單價×0.07%。

環(huán)境保護費單價=綜合單價×環(huán)境保護費率。

工程排污費單價=(綜合單價+措施費單價小計)×工程排污費率。

全費用單價=綜合單價+措施費單價小計+規(guī)費單價小計+稅金單價，等等。

其中：

環(huán)境保護費率=∑單位工程環(huán)境保護費÷∑單位工程分部分項工程費；

工程排污費率=∑單位工程排污費÷(∑單位工程分部分項工程費+∑單位工程措施項目費)。

通常情況下工程排污費在竣工結(jié)算時憑繳費憑據(jù)按實結(jié)算。

在表5中，按照“費=甲方審批量(見表3)×單價”的簡單算術(shù)關(guān)系設(shè)置單元格公式。

在實際應(yīng)用中，為了在形式上與合同報價書相同，改造后的表格中包括分部分項工程項目、措施項目在內(nèi)的所有項目都與合同報價書相同，見表4。所以，應(yīng)該注意的是：既然專業(yè)措施項目(如模板、腳手架等)已像合同報價書一樣單列出來，則各分部分項工程項目的“專業(yè)措施費單價”應(yīng)為零；而各專業(yè)措施項目(如模板、腳手架等)的“綜合單價”應(yīng)為零，其綜合單價填在“專業(yè)措施費單價”列里，這樣，表4中AR列即“全費用單價”單元格的公式(如AR296=U296+AH296+AN296+AO296+AP296+AQ296)才會統(tǒng)一，便于使用“復(fù)制、粘貼”的功能快速完成公式輸入。

4.3.2 設(shè)置公式技巧

如前3.2.2所述，要對合并項的數(shù)量設(shè)置公式；制作全費用清單造價文件時，同樣要對合并項的其他字段(如表6中AU～BF列)設(shè)置公式。如果合并項所包含的子項條目數(shù)量不同，則其相同字段的公式也不同，所以使用“復(fù)制、粘貼”功能設(shè)置公式時，只能針對具有相同子項條目數(shù)量的合并項(以下稱“同類合并項”)進行操作。但因為表格龐大，同類合并項在表格里不好找到，如果逐行查找同類合并項、粘貼公式，十分費時費力。

可以使用“篩選”和“排序”的功能在同類合并項之間快速地“復(fù)制、粘貼”公式。以表6(已隱藏了一些行、列)為例，操作步驟如下：

在“序號”插入“子項數(shù)目”列，填寫各合并項所包含的子項數(shù)目。

選取整個表格區(qū)域，點擊“數(shù)據(jù)”功能選項卡，再點擊“篩選”圖標按鈕，標題行各單元格右下角出現(xiàn)了向下的小三角即篩選標志；點擊“子項數(shù)目”的篩選標志，去掉“(空白)”前面的“√”號，點確定，就把所有合并項篩選出來了。

選取整個表格區(qū)域，點擊“數(shù)據(jù)”功能選項卡，再點擊“排序”圖標按鈕，在“主要關(guān)鍵字”的下拉列表中點選“子項數(shù)目”，點擊“確定”，就把同類合并項排列在一起了，見表7(已隱藏了一些行、列)。這樣，對于每類同類合并項，只要設(shè)置一次公式，再將公式“復(fù)制、粘貼”就行了。如：

設(shè)置好AU21∶BF21各單元格公式后，復(fù)制AU21∶BF21區(qū)域，粘貼到AU25∶BF33區(qū)域即可。

注意：完成以上操作后，要讓表格各行恢復(fù)到原始的排列順序，即以“序號1”(如表7所示)為主要關(guān)鍵字對整個表格排序，這樣才能保證公式所引用的單元格準確無誤。

4.4 表格排版

完整的“全費用清單”計算表格包括表4～表6中的各列。但在實際應(yīng)用中，可以隱藏一些列(沒有必要顯示的列)，以便縮小版面，減少頁數(shù)。以進度款造價文件為例，排版后的表格(已隱藏了一些行)如表8所示。

4.5 制作匯總表

制作造價文件時，通常還要有匯總表來體現(xiàn)工程款的構(gòu)成及其支付情況；特別是，按建設(shè)主管部門的要求，必須體現(xiàn)安全文明措施費的支付情況。該匯總表內(nèi)的各項明細金額可以通過設(shè)置簡單的四則運算公式，直接從明細表格(如表6所示)引用，參前3.2節(jié)。以進度款造價文件為例，匯總表如表9所示。

表9中C30單元格(大寫金額)的公式為：=IF((INT(C28*10)-INT(C28)*10)=0,TEXT(INT(C28),″[DBNum2]″)&″圓″&IF((INT(C28*100)-INT((C28)*10)*10)=0,″整″,″零″&TEXT(INT(C28*100)-INT(C28*10)*10,″[DBNum2]″)&″分″),TEXT(INT(C28),″[DBNum2]″)&″圓″&IF((INT(C28*100)-INT((C28)*10)*10)=0,TEXT((INT(C28*10)-INT(C28)*10),″[DBNum2]″)&″角整″,TEXT((INT(C28*10)-INT(C28)*10),″[DBNum2]″)&″角″&TEXT(INT(C28*100)-INT(C28*10)*10,″[DBNum2]″)&″分″))。

4.6 完成“全費用清單”格式的造價文件的編制

編制某期“全費用清單”格式的造價文件時，只要以前一期的造價文件為模板，修改工程量(各單價和費率不變)就能自動得出造價、自動生成匯總表。其操作步驟和前述“3.5完成‘工程量清單’格式的造價文件的編制”相同。

5 結(jié)語

Excel軟件的功能十分強大；只要掌握了它的基本操作以及工程造價的構(gòu)成和計算程序，不必使用專門的造價軟件和造價人員便可編制準確、方便、實用的造價文件電子表格，從而降低從事造價文件編制工作的門檻，提高工作效率。

[1]GB50500—2008，建設(shè)工程工程量清單計價規(guī)范[S].

[2]ExcelHome.Excel2010應(yīng)用大全[M].北京：人民郵電出版社，2011.

The application of Excel software in engineering progress payment and cost settlement calculation

Wang Mingjing

(XiamenBranch,FujianHuahangConstructionGroupLimitedCompany,Xiamen361004,China)

This paper introduced the general method of progress calculation and engineering settlement, put forward conversion of construction engineering progress payment into Excel spreadsheet, through the spreadsheet transformation, made the progress payment calculation file or engineering cost settlement file production not need to rely on computer software and valuation, arranged into full expenses list format file to understand, to simplify the cost file documentation work.

Excel application, bill of quantity, progress payment, settlement, full expenses list

2015-01-17

王明鏡(1974- )，男，工程師

1009-6825(2015)09-0253-06

TP391.13

A