Excel在工程預(yù)算中的應(yīng)用

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

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

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

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

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

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

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

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

9、計(jì)算，是因?yàn)榱合聣Φ母叨扰c砼梁高度相關(guān)的，可以減少參數(shù)的重復(fù)輸入。同理我會在計(jì)算門窗時(shí)計(jì)算墻及內(nèi)外裝飾工程量，只不過數(shù)量為負(fù)數(shù)即扣減。2、計(jì)量公式的編輯計(jì)量公式的實(shí)現(xiàn)方法在前面已介紹，為了讓公式易于看懂，我采用了Excel的名稱功能，公式引用名稱進(jìn)行計(jì)算，而不引用單元格名稱。下面是工程常用的公式：1）面積計(jì)算：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）體積計(jì)算：長方體體積、梯形截面體積、異形截面體積、棱臺棱錐體積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）重量計(jì)算：長方體構(gòu)件重量、各種部位鋼筋重量。 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 = "加強(qiáng)筋" 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 保護(hù)層 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 保護(hù)層 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）計(jì)量公式模板由于該公式內(nèi)容較多，逐個(gè)檢查太過復(fù)雜，我在公式編輯工作表中對所有項(xiàng)目進(jìn)行了excel工式計(jì)算，通過與gcl公式的計(jì)算結(jié)果進(jìn)行比較。我們只需要檢查excel公式的正確性就可以了，這項(xiàng)工作就是我們手工算量的列

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

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

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