Excel函數及宏編程在提高施工結算效率中的應用

錢 哨

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

0 引言

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

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

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

1 工程結算管理工作效率問題剖析

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

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

1.1 數據錄入分類

將業務人員需要錄入的數據簡單分為以下3 種類型：

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

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

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

1.2 數據錄入時間成本估算

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

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

1.3 時間成本結果分析

此項計量沒有考慮多表之間的邏輯關系。大工程行列數據更為龐大，統計分析緯度及環境影響、溝通協調更為復雜。在工期緊張情況下，人員忙碌錄入數據，極易產生數據偏差，最終導致結算計量數據不準確，需要反復進行排查，導致現場管理矛盾重重，嚴重降低了項目部整體績效和管理效能。因此，亟需提高一線業務人員Excel 數據采錄的工作效率，降低人員工作負擔，解放生產效能，提高管理績效。

2 Excel 工程結算優化解決方案

2.1 Excel 數據管理思維方式

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

2.2 數據標準化

數據標準化過程如下：

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

Fig.2 Establishing standardized information圖2 建立標準化信息

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

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

2.3 利用標準化信息創建Excel 下拉單，減輕常規數據錄入量

通過Excel 公式中的名稱管理器，將可以標準化的信息全部定義為數組形式，如圖3 所示。

Fig.3 Defines a standardized information array圖3 定義標準化信息數組

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

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

2.4 Excel 下拉單的數據聯動和復雜計算

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

Fig.5 Realizes the correlation pointing of drop-down menu through INDIRECT function圖5 通過INDIRECT 函數實現下拉菜單關聯指向

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

Fig.6 Automatic calculation effect of multi-list linkage data圖6 多列表聯動數據自動計算效果

單價計算基本公式為：=INDEX（計價標準！$D$3：$D$1 000，MATCH（‘劉小峰（機械）’！L6，計價標準！$C$3：$C$1 000，0）），通過MATCH 函數的業務邏輯關系搜索計算和INDEX 函數的匹配后查詢數據計算得出。

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

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

傳統錄入方式通過手工方式錄入，耗時耗力且數據計算結果不精準。有時為了省事，很多計量人員直接估算，造成隱形經濟損失。改進后的日期填寫為彈出日歷直接選擇，默認為當天，如圖7 所示。

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

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

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

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

3 結語

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

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