Excel在工程預算中的應用

1、摘要：工程手工算量計算繁瑣，不易統(tǒng)計和修改，故而算量軟件陸續(xù)出現(xiàn)。然而算量軟件操作必須按軟件要求，對軟件公司的指導依賴性很強；另外算量軟件采用三維建模來實現(xiàn)計算的，較手工算量多出了建模工作，而建模本身工作量較大。隨著我國工程量清單規(guī)范的實施，編制自己的企業(yè)定額已日趨必要，企業(yè)定額的數(shù)據(jù)主要來源于歷史數(shù)據(jù)的記錄和統(tǒng)計。Excel具有強大的數(shù)據(jù)處理能力，本人通過實際經(jīng)驗總結，制作了工程算量模板。本模板在計算工程量及投標報價上具有以下優(yōu)點：數(shù)據(jù)清晰性強，數(shù)據(jù)追溯性強，調(diào)價直觀性強。當然模板在算量時智能化及精度無法與算量軟件相比，追求清晰性、實用性、高效性的最佳組合。關鍵詞：Excel應用，工程算量軟

2、件，預算軟件，工程量清單，企業(yè)定額，施工成本控制，多維數(shù)據(jù)的處理。一、概述工程預算是一項非常復雜的系統(tǒng)，工程量清單將其劃分為分部工程，分項工程并對其進行計價。這種方法有兩點不足：一是對于不同部位的相同分項不方便統(tǒng)一定價和調(diào)價，二是有些分項工程并不能直接與單價很好的對應，定價的經(jīng)驗成份較大。為了克服這兩點不足，我采取如下措施：1、將不同部位的相同分項匯總統(tǒng)一定價，以免重復計價。2、對于水表井類似的項目，繼續(xù)拆分成挖土方、墊層、磚砌體等子項，匯總價格后作為該水表井的綜合單價。另外為了增加企業(yè)對成本的透明度，我采取如下措施：1、計量時力求簡捷，計量過程盡量透明，適當降低計算精度（在計算鋼筋長度時，我

3、們只按規(guī)范中一般抗震要求計算，忽略搭接長度的細微差別）2、將單價劃分為支出費用和內(nèi)部成本。對于支出費用，采取細致詢價保證其準確性；而對于企業(yè)內(nèi)部成本，一方面可采用定額消耗量核算成本，另一方面可根據(jù)企業(yè)以往類似工程實際發(fā)生進行宏觀控制。3、調(diào)整報價要抓大放小，模板中企業(yè)定額庫中事先確定默認價格（其誤差根據(jù)性質(zhì)不同可以為50%-200%）。這樣在計量時可以直觀的得出大致價格，而在匯總后，我們可以對占總價比例較大的項目進行價格精細確認，以保證報價的精確性，以免發(fā)生報價低于成本或過高價影響中標。傳統(tǒng)手工算量、Excel手工算量及算量軟件的比較傳統(tǒng)手工算量的缺點Excel手工算量的處理方案煩匯總統(tǒng)計煩E

4、xcel數(shù)據(jù)處理，保持布局清晰數(shù)據(jù)提取煩相關設計參數(shù)查詢煩影響工程量的細部節(jié)點計算入公式模板，不影響投標報價的節(jié)點忽略難關聯(lián)構件扣減難，支座判斷難按構件拆分建筑物，對每個構件的所有項目一并計算異型構件列式計算難編輯公式模板，直接調(diào)用變更錯誤準確調(diào)整難Excel數(shù)據(jù)處理，保持布局清晰核量對量難說明由于工程變化因素較多，要想達到面面俱到是不現(xiàn)實的，Excel手工算量遵循如下原則：盡可能使整體清單的清晰性強，而將較煩鎖的工作留在參數(shù)輸入上（比如地面面積不在清單中計算，而使用CAD測量面積匯總后將結果作為參數(shù)；鋼結構工程量零件復雜，在鋼結構算量模板中計算，將其結果作為參數(shù)）算量軟件的優(yōu)缺點優(yōu)點缺點三維

5、建模形象直觀由于三維模型透明性差，二維圖紙作為施工依據(jù)是必要的，也是完全可行的（否則圖紙就不合格）。而軟件僅僅為了計算使用三維建模并不值得。人為錯誤易于檢查，不會出現(xiàn)大的計算錯誤軟件界面尚不成熟，操作者進對軟件的遷就性較大二、核心途徑1、有限多維數(shù)據(jù)的清晰處理1）多級目錄是多維數(shù)據(jù)的最基本方式，工程量清單就是采用得這種方式，將復雜的工程按清單項目目錄綜合成一體。2）表格的行與列可組成矩陣，屬二維數(shù)據(jù)，較目錄形式的一維數(shù)據(jù)更加清晰。3）數(shù)據(jù)的排序功能。假設有一個三維數(shù)據(jù)（A，B，C），我們將A與B合并，數(shù)據(jù)更改為二維數(shù)據(jù)（A&B,C），但A&B本身仍是一維數(shù)據(jù),現(xiàn)對A&B

6、進行排序，順序依次為（A1&B1,A1&B2,A2&B1,A2&B2），即目錄化的二維數(shù)據(jù)。2、工程量的公式合并根據(jù)工程量清單規(guī)范，工程量的計量單位有件數(shù)、長度、面積、體積、重量，我們需對不同的計算單位和項目特點分別計算工程量。我們知道，使用IF公式，可以對不同種情況的公式進行合并，但由于IF公式只能嵌套7層，類型較多時無法實現(xiàn)，且公式太過復雜不易檢查。這里我使用了宏表函數(shù)，操作如下：工具宏vb編輯器彈出vb編輯器窗口依次點擊菜單插入模塊1，在窗口編輯如下內(nèi)容：Function gcl(biaoji, L, B, H, n, d, one, two, three

7、, four, five, six)If biaoji = "構件" Then gcl = 0ElseIf biaoji = "件數(shù)" Then gcl = n ElseIf biaoji = "長度" Then gcl = L / 1000 * n。 Else gcl = "未知標記" End IfEnd Function這里自定義的公式為gcl()，輸入的參數(shù)包括L, B, H, n, d基本參數(shù)及one, two, three, four, five, six自定義參數(shù)，對于不同的biaoji，可定義不同的自

8、定義參數(shù)。中間省略的公式將在后面介紹。3、數(shù)據(jù)匯總三、過程介紹操作流程編輯構件列表輸入?yún)?shù)根據(jù)總價比例調(diào)整項目單價輔助表格計算構件數(shù)量結合CAD測量房間周長和面積打印匯總表填寫清單報價表1、構件列項建筑物由構件組成，構件包括基礎、柱、梁、板、門窗、墻等幾大構件，每一類構件要算的項目也不是很多，砼結構計算砼量、模板、鋼筋。裝飾計算每層做法。安裝計算管道長度，閥門數(shù)量。構件列表是根據(jù)手工算量的思路進行編輯的，易于上手，我們將常規(guī)構件的列項存在模板中方便調(diào)用。而對于特殊的零星項目自行列項并不耗費很多時間，不建議保留在模板中，以免影響模板的清晰度。下圖是基礎梁或無板梁的構件列表示例梁下墻的工程量在這里

9、計算，是因為梁下墻的高度與砼梁高度相關的，可以減少參數(shù)的重復輸入。同理我會在計算門窗時計算墻及內(nèi)外裝飾工程量，只不過數(shù)量為負數(shù)即扣減。2、計量公式的編輯計量公式的實現(xiàn)方法在前面已介紹，為了讓公式易于看懂，我采用了Excel的名稱功能，公式引用名稱進行計算，而不引用單元格名稱。下面是工程常用的公式：1）面積計算：ElseIf biaoji = "面積" Then gcl = L / 1000 * B / 1000 * n ElseIf biaoji = "梁下墻" Then 'one 層高 gcl = L / 1000 * (one - H) /

10、1000 ElseIf biaoji = "測量面積" Then gcl = one ElseIf biaoji = "屋面防水" Then 'one 上卷高度 gcl = (L + 2 * one) / 1000 * (B + 2 * one) / 1000 ElseIf biaoji = "柱模板" Then gcl = (L + B) * 2 / 1000 * H / 1000 * n ElseIf biaoji = "無板梁模" Then gcl = L / 1000 * (B + 2 * H) /

11、 1000 ElseIf biaoji = "有板梁模" Then 'one 板厚 gcl = L / 1000 * (B + 2 * (H - one) / 1000 ElseIf biaoji = "樓板模" Then 'one 板厚 gcl = (L + 2 * one) / 1000 * (B + 2 * one) / 10003）體積計算：長方體體積、梯形截面體積、異形截面體積、棱臺棱錐體積ElseIf biaoji = "體積" Then gcl = L / 1000 * B / 1000 * H / 10

12、00 * n ElseIf biaoji = "有梁板砼" Then 'one 板厚 gcl = L / 1000 * B / 1000 * (H - one) / 1000 ElseIf biaoji = "梯形" Then 'one 上寬 gcl = L / 1000 * (B + one) / 2000 * H / 1000 ElseIf biaoji = "放坡梯形" Then 'one 工作面 two 放坡系數(shù) gcl = L / 1000 * (B + one * 2 + two * H) / 10

13、00 * H / 1000 ElseIf biaoji = "錐形" Then 'one 上長 two 上寬 gcl = (L * B / 1000000 + one * two / 1000000 + Sqr(L * B * one * two) / 1000000) * H / 3000 ElseIf biaoji = "放坡錐形" Then 'one 工作面 two 放坡系數(shù) LL 上部長 BB 上部寬 LL = L + 2 * one + 2 * H * two BB = B + 2 * one + 2 * H * two gcl

14、 = (L + 2 * one) / 1000 * (B + 2 * one) / 1000 + LL / 1000 * BB / 1000 + Sqr(L + 2 * one) * (B + 2 * one) * LL * BB) / 1000000) * H / 30004）重量計算：長方體構件重量、各種部位鋼筋重量。 ElseIf biaoji = "柱鋼筋" Then 'one 下部伸長 two 上部伸長 g = Round(3.1416 * d * d * 7.85 / 4000, 3) gjl = H + one + two djl = 40 * d *

15、 Int(gjl / 9000) gcl = n * g / 1000 * (gjl + djl) / 1000 ElseIf biaoji = "柱箍筋" Then 'one 加密間距 two 間距 three 長箍筋數(shù)量 four短箍筋數(shù)量 five 加密區(qū)長度 g = Round(3.1416 * d * d * 7.85 / 4000, 3) gjn = (H - five) / two + five / one) * n gjl = (L * three + B * four) gcl = gjn * g / 1000 * gjl / 1000 ElseI

16、f biaoji = "上部筋" Then 'one 柱寬度 two 跨度 g = Round(3.1416 * d * d * 7.85 / 4000, 3) gjl = L * two + one * (two + 1) + 15 * d * 2 djl = 40 * d * Int(gjl / 9000) gcl = n * g / 1000 * (gjl + djl) / 1000 / two ElseIf biaoji = "加強筋" Then 'one 柱寬度 two 跨度 g = Round(3.1416 * d * d *

17、 7.85 / 4000, 3) gjl = L * 2 / 3 + (one * (two + 1) + 15 * d * 2) / two djl = 40 * d * Int(L / 3 + one + 15 * d) / 9000) * 2 gcl = n * g / 1000 * (gjl + djl) / 1000 ElseIf biaoji = "下部筋" Then 'one 柱寬度 two 跨度 g = Round(3.1416 * d * d * 7.85 / 4000, 3) gjl = L * two + one * (two + 1) djl

18、 = 40 * d * Int(gjl / 9000) gcl = n * g / 1000 * (gjl + djl) / 1000 / two ElseIf biaoji = "梁箍筋" Then 'one 加密間距 two 間距 three 寬度箍筋數(shù)量 four高度箍筋數(shù)量 five 加密區(qū)長度 g = Round(3.1416 * d * d * 7.85 / 4000, 3) gjn = (L - five) / two + five / one) * n gjl = B * three + H * four gcl = gjn * g / 1000 *

19、 gjl / 1000 ElseIf biaoji = "拉筋" Then 'one 間距 gcl = L / one * n * B / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 ElseIf biaoji = "長分布筋" Then 'one 間距 two 鋼筋加長 three 布置加寬 four 保護層 g = Round(3.1416 * d * d * 7.85 / 4000, 3) gjl = L + two - 2 * four djl = 40 * d * I

20、nt(gjl / 9000) gjn = (B + three - 2 * four) / one * n gcl = gjn * g / 1000 * (gjl + djl) / 1000 ElseIf biaoji = "寬分布筋" Then 'one 間距 two 布置加寬 three 鋼筋加長 four 保護層 g = Round(3.1416 * d * d * 7.85 / 4000, 3) gjl = B + three - 2 * four djl = 40 * d * Int(gjl / 9000) gjn = (L + two - 2 * fou

21、r) / one * n gcl = gjn * g / 1000 * (gjl + djl) / 1000 ElseIf biaoji = "雙層加固" Then 'one 長向間距 two 寬向間距 gcl = L / one * B / two * (H + 6.5 * d) / 1000 * Round(3.1416 * d * d * 7.85 / 4000, 3) / 1000 ElseIf biaoji = "零星鋼筋" Then gcl = n * L / 1000 * Round(3.1416 * d * d * 7.85 /

22、4000, 3) / 1000 ElseIf biaoji = "鋼板" Then gcl = L / 1000 * B / 1000 * H / 1000 * n * 7.85 ElseIf biaoji = "型材" Then 'one 型鋼規(guī)格 two 單位重量 gcl = n * L / 1000 * two / 10005）計量公式模板由于該公式內(nèi)容較多，逐個檢查太過復雜，我在公式編輯工作表中對所有項目進行了excel工式計算，通過與gcl公式的計算結果進行比較。我們只需要檢查excel公式的正確性就可以了，這項工作就是我們手工算量的列

23、式，嫌復雜就過分了！不要期望軟件為你包辦一切，包辦一切的軟件是不存在的，同時也是行不通的。這里，對于同一構件大部分項目都需要LBH三個參數(shù)，為了避免重復輸入，在項目行的這三列我們輸入公式進行計算，而當不需要構件參數(shù)時，我們可以直接輸入，為了區(qū)分是輸入的還是引用的構件參數(shù)，我們對其設置不同的格式。這里做簡要解釋構件參數(shù)的引用：首先判斷是否為構件行，如為構件行，則=0，并設置格式為黃色，見條件2；如為項目行，則要看參數(shù)說明中是否需要輸入該參數(shù)（這里采用findb函數(shù)），如需要輸入，則=INDEX(M$3:M27,MATCH($D27,$C$3:$C27,0)查找該構件行的參數(shù)，并設置單元格為蘭色（

24、條件1），而當重新輸入的數(shù)據(jù)后，單元格數(shù)據(jù)不等于其內(nèi)公式計算的值（條件3），則顯示黃色。單元格的底色為灰色。3、工程量計算輔助工作已完成，現(xiàn)在我們將建筑物拆分成構件并計數(shù)，按照構件列表項目復制到清單工作表中，工作表會依據(jù)標記的內(nèi)容不同以黃色或蘭色提示需要輸入哪些參數(shù)。參數(shù)輸入完成后會按自定義的公式計算工程量。四、布局及匯總過程介紹以上對各項工程量進行了計算，但由于項目數(shù)量繁多。為了使數(shù)據(jù)清晰，下面介紹如何對數(shù)據(jù)進行整理。1、項目的處理，如下圖：如某行輸入的項目在前面已出現(xiàn)，則返回該項目序號，如為新項目，則項目序號為前面項目序號的最大值+1，編輯的公式及效果如下圖：此處使用了indirect函數(shù)

25、，是為了避免插入復制行或刪除行時公式出錯，舉例如下：假設我們要實現(xiàn)如下公式A1=0A2=A1+1A3=A2+1如果我們直接輸入這個公式，當我們在刪除第二行后，第三行由于引用單元格被刪除而出錯，同理，插入行后，也不是我們需要的結果。因此修改公式如下：A1=0A2=indirect(“A”&row()-1)+1A2=indirect(“A”&row()-1)+1有了項目號后，我們就可以在“匯總”表中查找并引用，實現(xiàn)列出項目名稱的目的，這里我們采用矩陣格式匯總不同部位的相同項目，表格如下：表中基本的公式為index(項目名稱列,match(項目號，項目號列，0)。match(項目號，項目號列，0)用來查找依次項目序號在“計算”表中的位置，index(項目名稱列,)用來返回相應位置的項目名稱。這里的規(guī)格是按照計算表中出現(xiàn)先后順序排序的，為使數(shù)據(jù)清晰，我們只需對B、C兩列按C列進行排序，返回原順序在B列只需重新輸入1，2，3。，C列為自動計算。分類的名稱號顯示原理同項目序號。這張表的所有數(shù)據(jù)均為Excel自動計算。五、項目綜合單價的確定匯總表中工程量計算出后，我們就可以進行定價，由于同一項目工程量已匯總并在右側矩陣中顯示詳細分類，調(diào)整單價對總價的影響很直觀。項目單價確定如下表：支出費用主要包括材料費及大型機械臺班