Excel函數(shù)及宏編程在提高施工結(jié)算效率中的應(yīng)用

錢 哨

（交通運輸部管理干部學(xué)院，北京 101601）

0 引言

Excel 作為歷史悠久的數(shù)據(jù)計算表工具在我國土建工程領(lǐng)域運用較為廣泛，既包括工程養(yǎng)護(hù)、工程量計算、工程造價、工程預(yù)算、工程復(fù)核等工程管理［1-10，16］，也包括工程橋梁、涵洞、邊坡、隧洞等工程建設(shè)計量［11-15，17-19］，主要使用Excel 中的函數(shù)公式和宏編程、數(shù)據(jù)透視表等功能實現(xiàn)各類工程計算。現(xiàn)有文獻(xiàn)雖然從土建工程專業(yè)角度實現(xiàn)了目標(biāo)指向性計算，但從信息科學(xué)角度分析尚存在無數(shù)據(jù)標(biāo)準(zhǔn)思維與軟件工程思維的突出問題。利用信息科學(xué)數(shù)據(jù)標(biāo)準(zhǔn)化導(dǎo)向，跨學(xué)科去思考土建工程的數(shù)據(jù)計算問題，是一種突破式的思維方式，對土建工程施工計量具有較好的借鑒作用［20］。

從本質(zhì)上講，信息化是解放管理者時間成本和機會成本［21］。因此，凡是不能夠創(chuàng)造新的財富（社會價值和經(jīng)濟(jì)價值）的信息化建設(shè)都是無效的信息化作為［22］。縱觀建筑施工企業(yè)，各類軟件產(chǎn)品汗牛充棟，但基層業(yè)務(wù)人員為錄入各類業(yè)務(wù)系統(tǒng)數(shù)據(jù)卻疲于奔命，信息化軟件系統(tǒng)不僅沒有解決基層項目部業(yè)務(wù)人員的工作負(fù)擔(dān)，反而成為繁忙工作中的累贅和無效的信息化行為［23］。基層項目部人員急切需要提高Excel 的數(shù)據(jù)處理技能，進(jìn)而提高單位時間生產(chǎn)率。

在我國建筑施工領(lǐng)域，工程項目部下設(shè)的計量、結(jié)算、工程等部門使用Excel 軟件記錄各類工程業(yè)務(wù)數(shù)據(jù)，形成電子數(shù)據(jù)文件并進(jìn)行工作流轉(zhuǎn)，是土建工程項目內(nèi)部管理的一項常態(tài)工作。但在實踐中項目部人員僅將Excel 視為數(shù)據(jù)錄入工具，并未引入數(shù)據(jù)標(biāo)準(zhǔn)化思維模式，通過數(shù)據(jù)標(biāo)準(zhǔn)化結(jié)合Excel 函數(shù)庫與宏編程等技巧解決面臨的問題，僅僅是為了錄入數(shù)據(jù)和計算工程結(jié)果疲于奔命，導(dǎo)致一線項目部管理工作效率不高，直接影響了工程結(jié)算和計量進(jìn)度，造成時間與人力資源浪費。

1 工程結(jié)算管理工作效率問題剖析

以某土建工程項目部機械租賃Excel 匯總結(jié)算清單為例分析工程結(jié)算管理效率低的主要原因，如圖1 所示。

Fig.1 Style of temporary machinery leasing account table圖1 臨時機械租賃臺帳表樣式

1.1 數(shù)據(jù)錄入分類

將業(yè)務(wù)人員需要錄入的數(shù)據(jù)簡單分為以下3 種類型：

（1）采集數(shù)據(jù)錄入類。采集數(shù)據(jù)錄入類一般是將現(xiàn)場發(fā)生的工程單據(jù)通過人工錄入到Excel 表格中，需要人工簡單的分析思考。圖1 的日期錄入和工作時間錄入就屬于采集數(shù)據(jù)錄入類。

（2）簡單重復(fù)錄入類。簡單重復(fù)錄入類一般指經(jīng)常發(fā)生、周期性重復(fù)出現(xiàn)，不需要人工分析或思考采用拖拉或者復(fù)制粘貼形式產(chǎn)生的數(shù)據(jù)。圖1 中的機械隊伍和申報人員即為簡單重復(fù)錄入類。

（3）計算統(tǒng)計類。計算統(tǒng)計類是Excel 所有操作中最為復(fù)雜的內(nèi)容，涉及知識面廣，不易掌握和操作。計算統(tǒng)計類包括科學(xué)計算、數(shù)量統(tǒng)計、條件分析、數(shù)據(jù)比較、數(shù)據(jù)篩選、趨勢分析等等。Excel 對實際工作具有較大驅(qū)動力，但掌握的人群較少。圖1 中的金額為計算統(tǒng)計類。

1.2 數(shù)據(jù)錄入時間成本估算

設(shè)定每單元格錄入基本時間為t 秒，表格總數(shù)為m 行n列。采集數(shù)據(jù)錄入類為tp、簡單重復(fù)錄入類為ts、計算統(tǒng)計類為tc、校驗統(tǒng)計時間為tj。增加權(quán)重參數(shù)后，可根據(jù)操作規(guī)律分析tp=t，ts=0.2t，tc=3t，tj=5t，則每行基準(zhǔn)時間合計為tp+ts+tc+tj=9.2t。

從數(shù)據(jù)列角度分析，設(shè)定采集數(shù)據(jù)錄入類有LP列、簡單重復(fù)錄入類有LS列、計算統(tǒng)計類有LC列，修訂后的行列基準(zhǔn)時間合計為：T′=tp*LP+ts*LS+tc*LC+tj=（LP+0.2LS+3LC+5）t，則最終T=（LP+0.2LS+3LC+5）t*m。代入本次場景參數(shù)，m=10 000，t=3s，LP=8，Ls=11，LC=4，則一張臨時機械租賃臺帳Excel 表格人工錄入計量時間為825 000s，合計229h，折合工作日（8h 計）需29 天，在工期緊張情況下人員需超負(fù)荷加班。

1.3 時間成本結(jié)果分析

此項計量沒有考慮多表之間的邏輯關(guān)系。大工程行列數(shù)據(jù)更為龐大，統(tǒng)計分析緯度及環(huán)境影響、溝通協(xié)調(diào)更為復(fù)雜。在工期緊張情況下，人員忙碌錄入數(shù)據(jù)，極易產(chǎn)生數(shù)據(jù)偏差，最終導(dǎo)致結(jié)算計量數(shù)據(jù)不準(zhǔn)確，需要反復(fù)進(jìn)行排查，導(dǎo)致現(xiàn)場管理矛盾重重，嚴(yán)重降低了項目部整體績效和管理效能。因此，亟需提高一線業(yè)務(wù)人員Excel 數(shù)據(jù)采錄的工作效率，降低人員工作負(fù)擔(dān)，解放生產(chǎn)效能，提高管理績效。

2 Excel 工程結(jié)算優(yōu)化解決方案

2.1 Excel 數(shù)據(jù)管理思維方式

信息化建設(shè)是把復(fù)雜事情簡單化、簡單事情流程化、流程事情定量化、定量事情信息化［20］，信息化最終結(jié)果一定是標(biāo)準(zhǔn)化和數(shù)據(jù)化［21］。Excel 表格設(shè)計就是標(biāo)準(zhǔn)化的業(yè)務(wù)邏輯再造過程，Excel 內(nèi)的錄入數(shù)據(jù)就是真實的業(yè)務(wù)數(shù)據(jù)，而Excel 的統(tǒng)計分析就是各類軟件所訴求的最終管理結(jié)果。所有軟件類企業(yè)建設(shè)軟件產(chǎn)品之前，需要大量調(diào)研企業(yè)實際發(fā)生的報表和臺帳，就是將已定型的業(yè)務(wù)流程、工作邏輯、實際數(shù)據(jù)、統(tǒng)計分析結(jié)果進(jìn)行再造，只是表現(xiàn)形式和實現(xiàn)技術(shù)手段更為專業(yè)。因此，重視Excel 的數(shù)據(jù)操作，提升數(shù)據(jù)標(biāo)準(zhǔn)化思考技巧，是重視信息化工作的基石。

2.2 數(shù)據(jù)標(biāo)準(zhǔn)化

數(shù)據(jù)標(biāo)準(zhǔn)化過程如下：

（1）剝離可標(biāo)準(zhǔn)化的數(shù)據(jù)內(nèi)容。標(biāo)準(zhǔn)化的信息指可以固定下來的不會隨著時間變化而變化的常量性數(shù)據(jù)，如人員、單位、計量標(biāo)準(zhǔn)、工程標(biāo)準(zhǔn)等。需要認(rèn)真分析Excel 報表中的數(shù)據(jù)類型，在臨時機械租賃臺帳中可被固定的標(biāo)準(zhǔn)化信息如圖2 所示。

Fig.2 Establishing standardized information圖2 建立標(biāo)準(zhǔn)化信息

（2）建表基本原則是禁止表中表，表格必須是二維表（唯一行列號可以確定唯一表中的數(shù)據(jù)）。禁止表中表原則是盡可能減少復(fù)雜表格中的邏輯關(guān)系，減少表格數(shù)據(jù)復(fù)雜因素，利于后期數(shù)據(jù)分析和統(tǒng)計工作。

（3）如果表之間存在關(guān)聯(lián)性則采用多頁關(guān)聯(lián)形式。Ex?cel 單個文件可由若干分頁（sheet）組成，工程中描述一個事務(wù)性的業(yè)務(wù)內(nèi)容由若干個分頁表共同組成，分頁與分頁之間存在一定的數(shù)據(jù)邏輯關(guān)系，如機械臺賬清單分頁與機械數(shù)據(jù)匯總分頁之間存在數(shù)據(jù)計算邏輯關(guān)系。

2.3 利用標(biāo)準(zhǔn)化信息創(chuàng)建Excel 下拉單，減輕常規(guī)數(shù)據(jù)錄入量

通過Excel 公式中的名稱管理器，將可以標(biāo)準(zhǔn)化的信息全部定義為數(shù)組形式，如圖3 所示。

Fig.3 Defines a standardized information array圖3 定義標(biāo)準(zhǔn)化信息數(shù)組

而后在數(shù)據(jù)選擇中定義單元格為序列下拉列表，并在數(shù)據(jù)來源中指定名稱管理器的具體數(shù)組，如圖4 所示。

Fig.4 Create drop-down list operation圖4 建立下拉列表操作

2.4 Excel 下拉單的數(shù)據(jù)聯(lián)動和復(fù)雜計算

Excel 文件列之間可能存在邏輯關(guān)聯(lián)，特別是下拉單之間可能存在集合邏輯關(guān)聯(lián)，如定義機械類別后需要完成型號單位的數(shù)據(jù)篩選，即確定某個大類信息，同時實現(xiàn)與該大類有關(guān)的邏輯數(shù)據(jù)，在數(shù)據(jù)來源中通過INDIRECT 函數(shù)實現(xiàn)下拉菜單聯(lián)動效果，如圖5 所示。

Fig.5 Realizes the correlation pointing of drop-down menu through INDIRECT function圖5 通過INDIRECT 函數(shù)實現(xiàn)下拉菜單關(guān)聯(lián)指向

例如，在確定機械類別后型號單位實現(xiàn)自動篩選，進(jìn)而通過標(biāo)準(zhǔn)化數(shù)據(jù)關(guān)聯(lián)直接生成某類別型號單位下的機械租賃單價。這一聯(lián)動邏輯處理不需要操作人員進(jìn)行任何鍵盤敲擊，實現(xiàn)數(shù)據(jù)錄入工作自動化，確保了數(shù)據(jù)自動計算，如圖6 所示。

Fig.6 Automatic calculation effect of multi-list linkage data圖6 多列表聯(lián)動數(shù)據(jù)自動計算效果

單價計算基本公式為：=INDEX（計價標(biāo)準(zhǔn)！$D$3：$D$1 000，MATCH（‘劉小峰（機械）’！L6，計價標(biāo)準(zhǔn)！$C$3：$C$1 000，0）），通過MATCH 函數(shù)的業(yè)務(wù)邏輯關(guān)系搜索計算和INDEX 函數(shù)的匹配后查詢數(shù)據(jù)計算得出。

2.5 通過宏編程實現(xiàn)單次機械租賃費用自動計價

單次機械租賃費用=機械租賃標(biāo)準(zhǔn)單價×租賃時間，通過下拉列表求得某機械類別中的型號單位并自動得出單價后，需要計算某機械臺班工作時間。機械臺班工作時間需要確定日期和工作起始終止時間，通過二者之差得出機械臺班工作時間。

傳統(tǒng)錄入方式通過手工方式錄入，耗時耗力且數(shù)據(jù)計算結(jié)果不精準(zhǔn)。有時為了省事，很多計量人員直接估算，造成隱形經(jīng)濟(jì)損失。改進(jìn)后的日期填寫為彈出日歷直接選擇，默認(rèn)為當(dāng)天，如圖7 所示。

Fig.7 Automatic calculation of time by date control圖7 日期控件實現(xiàn)時間自動計算

在開發(fā)工具中，通過插入時間控件的形式建立日期列某個單元格的日期時間控件，而后通過鼠標(biāo)雙擊進(jìn)入后臺的VBA 編程界面，鍵入如下VBA 代碼，就可以實現(xiàn)自動日期彈出，然后進(jìn)行選擇。

日期選擇實現(xiàn)后，分別建立起始時間和終止時間下拉列表，通過選擇形式確定開始和結(jié)束的小時和分鐘，然后根據(jù)選擇者的觸發(fā)自動計算出某天的時間量差，從而得到租賃時間值，具體公式如下（其中B6 代表選擇的日期，C6和D6 代表選擇的起始小時和分鐘，E6 和F6 代表選擇的終止小時和分鐘）：

通過計算分別獲得機械租賃標(biāo)準(zhǔn)單價和租賃時間后，單次機械租賃費用就可通過乘的形式直接計算得出，上述過程都是自動計算得到，無需人工參與。鑒于本案例計算過程的繁雜性和篇幅限制，關(guān)于工程隊臨時用工計費、臨時用工匯總、臨時機械匯總、臨時結(jié)算匯總臺賬、月度結(jié)算匯總內(nèi)容在此不一一列舉。

3 結(jié)語

實行改進(jìn)的Excel 自動計量計算方式后，業(yè)務(wù)人員操作評估計算分析tp=t，ts=0.1t，tc=0t，tj=0t，實際工作行列基準(zhǔn)時間合計為T′=tp×Lp+ts×Ls+tc×Lc+tj=（Lp+0.1Ls）t，則最終T=（Lp+0.1Ls）t×m。帶入本次場景參數(shù)m=10 000，t=3s，Lp=3，Ls=16，Lc=4 中，則一張臨時機械租賃臺帳Excel 表格人工錄入計量時間為138 000s，合計38h，折合工作日（8h 計）需4.7天，與原工作時間29 天量差為24.3 天，新方法可提高項目部工程結(jié)算計量6 倍。本文項值計算僅為單人工計差，如果擴(kuò)展到整個工程項目則工作效率提高非常驚人。

本文Excel 函數(shù)與宏編程僅涉及到一部分案例內(nèi)容，沒有一成之規(guī)和可以定制化的模版，僅僅提供了改進(jìn)思路和步驟，具體實操需要根據(jù)實際情況進(jìn)行改進(jìn)。如果按照本文思路持續(xù)改進(jìn)，則工程結(jié)算軟件化和產(chǎn)品化會邁出可喜的一大步。