基于Excel的大型工程投標(biāo)報(bào)價(jià)

1、基于Excel的大型工程投標(biāo)報(bào)價(jià)工程造價(jià)管理旦笠第一期基于馥的大型工程投標(biāo)報(bào)價(jià)口王芝福中國(guó)水電顧問集團(tuán)華東勘測(cè)設(shè)計(jì)研究院摘要:本文結(jié)合工程實(shí)例,詳細(xì)論述了應(yīng)用Excel的名稱,篩選及條件格式的功能編制投標(biāo)價(jià)格表,解決了價(jià)格表編制過程中價(jià)格表修改的傳遞,格式的統(tǒng)一及快速輸入計(jì)算公式的問題,從而極大地提高了價(jià)格表的編制效率和正確率.關(guān)鍵詞:投標(biāo)報(bào)價(jià)表Exce1名稱篩選條件格一,問題的提出在工程投標(biāo)中,最核心的莫過于價(jià)格表編制.大型工程的價(jià)格表分項(xiàng)很多很細(xì),經(jīng)常有成百上千行,因此導(dǎo)致編制價(jià)格表極其繁瑣且容易出錯(cuò),更繁瑣的是大型工程,分工很細(xì),通常由多個(gè)專業(yè)的技術(shù)人員分別提供成本價(jià),再由商務(wù)人員匯總成

2、本價(jià)后進(jìn)行加價(jià)再對(duì)外報(bào)價(jià),經(jīng)過多人編制后.更容易出錯(cuò).主要問題有:1,價(jià)格表的修改由于投標(biāo)人對(duì)招標(biāo)文件很難完全響應(yīng).不可避免對(duì)價(jià)格表中的某些小項(xiàng)會(huì)有不同意見.如對(duì)小項(xiàng)貨物名稱的改變,數(shù)量的變化等.由于更改人員一般為專業(yè)技術(shù)人員,而匯總?cè)藶樯虅?wù)人員.此時(shí)需要尋找一種方法能快速自動(dòng)將更改人的更改記錄傳遞給匯總?cè)?而不是由更改人手動(dòng)標(biāo)注每一處更改.2,公式的快速輸入及格式的統(tǒng)一大型工程中價(jià)格表的格式一般已做好.并且比較復(fù)雜,大項(xiàng)中套小項(xiàng),分項(xiàng)之間有小計(jì),大項(xiàng)之間有多行空白等,如表1所示,其中成本價(jià)由多個(gè)專業(yè)的技術(shù)人員準(zhǔn)備.最終報(bào)價(jià)由商務(wù)人員考慮各種加價(jià)系數(shù)后對(duì)成本價(jià)進(jìn)行加價(jià),在單價(jià)轉(zhuǎn)換成總價(jià),成本價(jià)

3、轉(zhuǎn)換成最終價(jià)的過程中必須輸入公式,那么,怎樣才能快速輸入公式又不破壞原價(jià)格表的格式呢.表1招標(biāo)文件價(jià)格表矗轉(zhuǎn)0t價(jià)格裹謦-鼬警警A大項(xiàng)A飄觚一:簪廠l小H-r巾AIl矗_0I零A2.2飄瓣l小計(jì)項(xiàng)A日期:投標(biāo)人:28EngineeringCostManagement二,問題的分析1,價(jià)格表的修改由于專業(yè)技術(shù)人員對(duì)價(jià)格表意見主要有兩類.即小項(xiàng)貨物名稱的改變,數(shù)量的變化.常規(guī)做法是專業(yè)技術(shù)人員每修改一處地方,就用不同的顏色標(biāo)記出來,那么匯總時(shí)就能一目了然地看出來,但問題是專業(yè)技術(shù)人員總有忘記的時(shí)候,并且標(biāo)記起來也很繁瑣,所以最好的辦法就是讓Excel自動(dòng)判斷.經(jīng)分析專業(yè)技術(shù)人員的判斷過程,主要就是

4、看現(xiàn)有的貨物名稱和數(shù)量與原來的貨物名稱和數(shù)量是否一致.由此想到Excel的條件格式,將原招標(biāo)文件的貨物名稱和數(shù)量列復(fù)制,然后與專業(yè)技術(shù)人員修改后的貨物名稱和數(shù)量列進(jìn)行比較,即在條件格式中輸入相應(yīng)的比較公式就能實(shí)現(xiàn)其自動(dòng)判斷.2,格式的統(tǒng)一對(duì)于多人協(xié)作的投標(biāo),首先需要匯總?cè)烁鶕?jù)招標(biāo)文件的價(jià)格表統(tǒng)一填好相關(guān)公式.設(shè)置好相關(guān)格式,做成價(jià)格表格式文件發(fā)給專業(yè)技術(shù)人員.不需要其再輸入公式,從而減輕工作量,并且做到整個(gè)價(jià)格表格式的統(tǒng)一.在準(zhǔn)備價(jià)格表格式文件的過程中怎樣才能在不破壞原來價(jià)格表格式的情況下快速輸入公式到合適的位置呢,常規(guī)辦法是輸入公式后簡(jiǎn)單地下拉,但這樣做肯定會(huì)破壞原價(jià)格表的格式,并且還需要隨

5、時(shí)關(guān)注那些不需要輸人公式的單元格.為此我們需要解決兩個(gè)問題,第一是找出那些需要輸入公式的單元格,第二是設(shè)計(jì)出適合快速輸入的公式,而且其公式方便修改.對(duì)于第一個(gè)問題通過仔細(xì)觀察能夠發(fā)現(xiàn),那些數(shù)量欄非空的行都是需要填充公式的,Excel的篩選功能正好能夠快速將輸入公式的單元格篩選出來.對(duì)于第二個(gè)問題看似簡(jiǎn)單,即選中需要填充的單元格,然后輸入公式后,按Ctr|+Enter組合鍵.就一次性在選中單元格中輸入公式.但由于有些小項(xiàng)數(shù)量有可能"無"或者"已含",這就需要公式中設(shè)置一些條件格式,再加上其他的一些無法預(yù)知的改變,為了方便公式的修改,最好采用名稱公式替換普通

6、公式.Excel篩選功能能夠過濾那些不需要填充的行,這樣結(jié)合上面提到的快速輸入公式的方法,我們就能一次性快速輸入一列中幾百上千個(gè)公式,并且還不會(huì)破壞原有的格式.Excel的名稱功能很強(qiáng)大,能夠被定義成常量,變量(動(dòng)態(tài)單元格),并能加入Excel中的公式,從而將Excel中不同內(nèi)容的公式轉(zhuǎn)為同樣內(nèi)容的公式.三,詳細(xì)方法1,設(shè)置條件格式具體操作如下:(1)復(fù)制貨物名稱列和數(shù)量列,分別粘貼到K列和L列.(2)選中含數(shù)據(jù)項(xiàng)的所有單元格,具體為左鍵單擊選擇A4單元格下拉,按住shift鍵,單擊選擇L30單元格(3)設(shè)置具體的條件格式,具體為選中菜單欄"格式"一"條件格式&q

7、uot;,設(shè)置條件1,選擇"公式",輸入"=$E4:"無"".設(shè)置"格式"為"字體"一"特殊效果"一"刪除線","岡案"一"單元格底紋"一"黃色""添加'條件2",選擇"公式",輸入"=NOT($B4=38K4)",設(shè)置"格式"為"圖案"一"單元格底紋"一"青色

8、":"添加""條件3",選擇"公式",輸入"=NOTf$E4=$L4)",設(shè)置"格式"為"字體""特殊效果"一"刪除線","圖案"一"單元格底紋"一"粉紅色".(4)需要增加貨物項(xiàng)時(shí),先將鼠標(biāo)移到某一小項(xiàng)最左邊,出現(xiàn)"一"時(shí).點(diǎn)擊選中該行,"右鍵"一"復(fù)制",在需要插入的位置"右鍵"一&q

9、uot;插入復(fù)制單元格",然后更改貨物名稱單元格的內(nèi)容.解釋如下:第一步將原表貨物名稱及數(shù)量備份,從而實(shí)現(xiàn)與專業(yè)技術(shù)人員修改的貨物名稱及數(shù)量的對(duì)比.第二步選中條件格式的應(yīng)用范圍,該應(yīng)用范圍應(yīng)能包含所有的數(shù)據(jù)行.第三步設(shè)置相應(yīng)的條件格式,第一個(gè)條件格式為該項(xiàng)貨物沒有即當(dāng)數(shù)量欄輸入"無"的時(shí)候,該行數(shù)據(jù)字體會(huì)自動(dòng)加刪除線,底色變?yōu)辄S色;第二個(gè)條件格式是比較每行貨物名稱與備份在K列中的原表貨物名稱是否相同,當(dāng)專業(yè)技術(shù)人員對(duì)某行貨物名稱進(jìn)行改變后,該行底色會(huì)變?yōu)榍嗌?第個(gè)條件格式同理,是判斷數(shù)量的,數(shù)量不同時(shí)底色會(huì)變成粉紅色.第四步為判斷小項(xiàng)是否為添加的行,由于Excel

10、2003只有_二個(gè)條件格式,故添加項(xiàng)變化與貨物名稱變化一致.該行底色變?yōu)榍嗌?輸入的公式中"$"非常重要.Excel中在列號(hào)及行號(hào)前面加上"38"表示絕對(duì)引用.tI$E4表示E列為絕對(duì)引用,列號(hào)不可以變化,但行號(hào)是可以變化的,如對(duì)于A4單元格判斷的條件是E4單元格是否等于"無",B5單元格判斷的條件是E5單元格是否等于"無".條件格式順序比較重要,首先判斷數(shù)量是否為"無".其次判斷貨物名稱是否有變化.最后判斷數(shù)量是否有更改.如果將條件2與條件3更換的話.因?yàn)樨浳锩Q更改后.數(shù)量一般也會(huì)發(fā)生變化.

11、而此時(shí)條件格式顯示只有數(shù)量發(fā)生變化,容易出錯(cuò).2,輸入公式具體操作如下:(1)選中F和C列,"復(fù)制","粘貼"到H列中.(2)設(shè)置名稱公式.先選中H8單元格,然后在菜單欄中選擇"插人"一"名稱"一"定義",在名稱中輸入"單價(jià)",在"引用位置"中輸入"=IFf價(jià)格表1E8="無".',IF(價(jià)格表lF8="已含","已含",ROUND(價(jià)格表1F8x價(jià)格表1F$3,0)"

12、,點(diǎn)擊"確定"再選中G8單元格,在名稱中輸入"總價(jià)",在"引用位置"中輸入"=IFf價(jià)格表1E8="無",',IF(價(jià)格表1F8="已含","已含".價(jià)格表1$E8x價(jià)格表1VS)",點(diǎn)擊"確定".(3)篩選數(shù)據(jù).選中含數(shù)據(jù)項(xiàng)的所有單元格,具體為左鍵單擊選擇A4單元格下拉,按住shift鍵,單擊選擇G30單元格,點(diǎn)擊菜單欄"數(shù)據(jù)"一"篩選"一"自動(dòng)篩選",選擇篩選條件,

13、選中"數(shù)量"下拉欄中"(非空白)","總價(jià)"下拉欄中"(空白)".(4)快速輸入公式.左鍵單擊選擇G8單元格,按住shift鍵,單擊選擇G28單元格;在"編輯欄"中輸入"=總價(jià)",按Ctrl+Enter,一次性輸人公式:同樣Egeegc.Maageme29lA工程造價(jià)管理旦一笠第期工程造價(jià)管理一盎第期的方法在H列中輸入"=單價(jià)".(5)取消篩選,輸入小計(jì)項(xiàng)及總計(jì)項(xiàng)的求和公式.(6)選中G列."復(fù)制",在I列中"粘貼"

14、.解釋如下:復(fù)制"單價(jià)"和"總價(jià)"列,F列的單價(jià)由專業(yè)技術(shù)人員填入成本價(jià),H列的單價(jià)南匯總?cè)烁鶕?jù)成本價(jià)乘一定系數(shù)形成最終的對(duì)外報(bào)價(jià).名稱定義中可以輸入公式,包括單元格的絕對(duì)引用和相對(duì)引用.本公式中預(yù)先設(shè)定了兩個(gè)條件,即當(dāng)數(shù)量為"無"時(shí).總價(jià)中為空,當(dāng)單價(jià)"包含",總價(jià)也為"包含",并且采用了圓整公式,避免因小數(shù)四舍五人顯示造成的誤差.如某項(xiàng)單價(jià)乘系數(shù)后為3.14543,顯示2位小數(shù)3.15,數(shù)量為10個(gè),總價(jià)欄將為31.45,圓整后就不會(huì)出現(xiàn)此問題.另外單元格"價(jià)格表1$F$3&quo

15、t;為總系數(shù),后期需要根據(jù)最終報(bào)價(jià)和成本價(jià)進(jìn)行調(diào)整.由于相對(duì)引用容易出錯(cuò),所以最好先選中需要輸入公式的單元格,再在名稱定義中輸入相應(yīng)的公式.由于價(jià)格表不是一個(gè)整體的表格,故需要先選中含數(shù)據(jù)項(xiàng)的所有單元格,不然篩選的數(shù)據(jù)范圍只會(huì)含第一個(gè)表.篩選的過程中靈活應(yīng)用篩選條件,才能將需要輸入公式的單元格全部找出來.選中需要輸入公式的單元格后輸入公式,然后按住Ctrl+Enter,才能一次性輸入公式并且保證單元格的格式不被更改.輸入求和公式時(shí)一定要采用SUM公式,而不能直接相加.因?yàn)橛行﹩卧裰袃r(jià)格是"已含",直接相加會(huì)出錯(cuò).這樣可以減少輸入的次數(shù).3,設(shè)定對(duì)外格式及匯總(1)內(nèi)部發(fā)給

16、專業(yè)技術(shù)人員.內(nèi)部發(fā)給專業(yè)技術(shù)人員時(shí),將其不需要的信息進(jìn)行隱藏,即選擇H到L列,"右鍵"一"隱藏".然后作為格式文件發(fā)給專業(yè)技術(shù)人員,并跟專業(yè)技術(shù)人員約定格式如下:投標(biāo)價(jià)格表單位用"元".如果該小項(xiàng)數(shù)量發(fā)生變化,在數(shù)量"數(shù)量"欄中填入相應(yīng)的數(shù)量.單元格會(huì)自動(dòng)變?yōu)榉奂t底色.如果該小項(xiàng)沒有,不要?jiǎng)h除該行,只需要在"數(shù)量"欄中填"無",字體會(huì)自動(dòng)變紅加刪除線變黃底色.如果需要添加小項(xiàng),將鼠標(biāo)移到最左邊,出l3.Engneengc.sMaageme現(xiàn)時(shí),點(diǎn)擊,選中一行,右鍵一復(fù)制一右

17、鍵一插入復(fù)制單元格,然后更改單元格內(nèi)容,插入行底色會(huì)自動(dòng)變青底色,這樣專業(yè)技術(shù)人員遵循統(tǒng)一的格式,價(jià)格表的填寫和修改便一目了然,方便匯總.(2)匯總?cè)藚R總價(jià)格表.匯總?cè)藦膶I(yè)技術(shù)人員反饋回來的價(jià)格表格式統(tǒng)一,匯總已經(jīng)非常方便,只需要將反饋回來的價(jià)格表中的單價(jià)復(fù)制到總價(jià)表中,并隨時(shí)注意底色有變化的單元格即可.匯總完畢后就是價(jià)格表的成本價(jià)表,總價(jià)為X.最終,對(duì)外報(bào)價(jià)表只需要根據(jù)市場(chǎng)行情,確定對(duì)外總報(bào)價(jià)Y后,在總系數(shù)單元格F3中輸入"=Y/X".H列的單元格并自動(dòng)乘總系數(shù),H列和I列數(shù)據(jù)便是對(duì)外報(bào)價(jià).由于進(jìn)行了圓整,對(duì)外總報(bào)價(jià)與Y值會(huì)有點(diǎn)小誤差,只需要在H列某一單元格稍加調(diào)整就好了.匯總后的價(jià)格表如表2所示.表2Ae專羞F一K價(jià)格表2遂如