CN112734403A - Highway engineering construction cost management system compiled based on Excel-VBA - Google Patents
Highway engineering construction cost management system compiled based on Excel-VBA Download PDFInfo
- Publication number
- CN112734403A CN112734403A CN202110312470.1A CN202110312470A CN112734403A CN 112734403 A CN112734403 A CN 112734403A CN 202110312470 A CN202110312470 A CN 202110312470A CN 112734403 A CN112734403 A CN 112734403A
- Authority
- CN
- China
- Prior art keywords
- data
- vba
- excel
- cost
- module
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000010276 construction Methods 0.000 title claims abstract description 42
- 238000007726 management method Methods 0.000 claims abstract description 37
- 238000007728 cost analysis Methods 0.000 claims abstract description 10
- 238000007405 data analysis Methods 0.000 claims description 15
- 238000004458 analytical method Methods 0.000 claims description 13
- 238000000605 extraction Methods 0.000 claims description 11
- 238000013075 data extraction Methods 0.000 claims description 9
- 238000007689 inspection Methods 0.000 claims description 8
- 238000000034 method Methods 0.000 claims description 5
- 238000013439 planning Methods 0.000 claims description 4
- 238000009430 construction management Methods 0.000 abstract description 4
- 210000001503 joint Anatomy 0.000 abstract 1
- 238000010586 diagram Methods 0.000 description 4
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 230000000694 effects Effects 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q10/00—Administration; Management
- G06Q10/10—Office automation; Time management
- G06Q10/103—Workflow collaboration or project management
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/248—Presentation of query results
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
- G06F40/177—Editing, e.g. inserting or deleting of tables; using ruled lines
- G06F40/18—Editing, e.g. inserting or deleting of tables; using ruled lines of spreadsheets
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q40/00—Finance; Insurance; Tax strategies; Processing of corporate or income taxes
- G06Q40/12—Accounting
- G06Q40/125—Finance or payroll
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q50/00—Systems or methods specially adapted for specific business sectors, e.g. utilities or tourism
- G06Q50/08—Construction
Landscapes
- Engineering & Computer Science (AREA)
- Business, Economics & Management (AREA)
- Theoretical Computer Science (AREA)
- Strategic Management (AREA)
- General Physics & Mathematics (AREA)
- Physics & Mathematics (AREA)
- Human Resources & Organizations (AREA)
- Finance (AREA)
- Accounting & Taxation (AREA)
- Economics (AREA)
- Marketing (AREA)
- General Business, Economics & Management (AREA)
- Health & Medical Sciences (AREA)
- General Engineering & Computer Science (AREA)
- General Health & Medical Sciences (AREA)
- Data Mining & Analysis (AREA)
- Tourism & Hospitality (AREA)
- Computational Linguistics (AREA)
- Entrepreneurship & Innovation (AREA)
- Audiology, Speech & Language Pathology (AREA)
- Quality & Reliability (AREA)
- Operations Research (AREA)
- Primary Health Care (AREA)
- Artificial Intelligence (AREA)
- Databases & Information Systems (AREA)
- Development Economics (AREA)
- Technology Law (AREA)
- Management, Administration, Business Operations System, And Electronic Commerce (AREA)
- Financial Or Insurance-Related Operations Such As Payment And Settlement (AREA)
Abstract
The invention discloses a highway engineering construction cost management system compiled based on Excel-VBA, belonging to the technical field of highway engineering construction management. The invention is simple and intuitive, can automatically generate various cost analysis results in the road and bridge engineering industry, can realize the accurate butt joint of the engineering management data and the financial management data, and effectively improves the construction cost management efficiency.
Description
Technical Field
The invention belongs to the technical field of highway engineering construction management, and particularly relates to a highway engineering construction cost management system compiled based on Excel-VBA.
Background
The cost management of the highway engineering construction project takes a highway construction project as an object, takes a value law as guidance, takes cost prediction, planning, control, accounting, analysis and assessment as contents, and applies a series of special means and methods to guide, coordinate, supervise and control the production and operation activities of the highway construction project, thereby being a most key management link for improving the economic benefits of highway construction enterprises.
At present, highway engineering construction enterprises are large in the number of supporting projects, large in construction site length, wide in construction range and more in construction positions, constructors are scattered in different places to work, the cost management data acquisition speed is low, and collection of bill information is generally adopted in the industry at present, input into a computer and assisted management by using excel tables or SQL databases; due to more data classification, the excel cannot realize data summarization analysis of a large number of workbooks, and the excel is limited in function and cannot generate complex analysis reports; although the SQL database has strong functions, the SQL database has high use difficulty and cannot be used by constructors in the general highway engineering industry.
Disclosure of Invention
The invention provides a highway engineering construction cost management system compiled based on Excel-VBA, which is mainly developed aiming at the work of planning, controlling, accounting and analyzing four links in the highway engineering construction project cost management, collects data of a construction site through a mobile phone, analyzes and processes the data by using a system, automatically generates a report and is added with a financial settlement function.
In order to solve the technical problems, the technical scheme adopted by the invention is as follows:
a highway engineering construction cost management system compiled based on Excel-VBA comprises a data acquisition module for extracting mobile phone data, a data inspection and query module for counting inspection data, a data extraction module for extracting data, a data analysis module for merging data files and a financial settlement report generation module for a financial department.
The technical scheme of the invention is further improved as follows: the system operation steps are as follows:
step 1) developing a VBA module;
step 2) setting a software interface and various functional areas in the Excel, and setting an operation window and a control;
step 3) calling the VBA data acquisition module to extract excel data acquired by a mobile phone;
step 4) calling the VBA data inspection module to perform statistical inspection data;
step 5) calling the VBA data extraction module to extract data;
step 6) calling the VBA data analysis module to analyze and merge a plurality of data files;
and 7) calling the VBA data financial module to generate merged payment data and interfacing with financial software data.
The technical scheme of the invention is further improved as follows: the mobile phone data extracted by the data acquisition module comprises: cost planning data, cost statistical data, and engineering volume data.
The technical scheme of the invention is further improved as follows: the cost plan data comprises comprehensive unit price cost plan data and common labor, material and machine name price data of each project;
the cost statistical data comprises labor cost data, mechanical cost data, material cost data, field expense data, purchase cost data, hospitality cost data and safety cost data;
the engineering quantity data comprises construction period node completion engineering quantity data.
The technical scheme of the invention is further improved as follows: the data statistically checked by the data checking and inquiring module comprises the following data: data checking table, manpower fee inquiry table, mechanical fee inquiry table, material fee inquiry table, field expense inquiry table, purchase fee inquiry table, hospitality fee inquiry table and safety fee inquiry table.
The technical scheme of the invention is further improved as follows: the data checking table comprises whether the item name in the acquired data is wrong or not, whether the money in the acquired data is wrong or not, whether the date in the acquired data is wrong or not, and whether the machine, material and field name in the acquired data are wrong or not.
The technical scheme of the invention is further improved as follows: the excel data extracted by the data extraction module comprises the following steps: single file extraction, folder extraction, root directory extraction.
The technical scheme of the invention is further improved as follows: the data analyzed by the data analysis module comprises: the method comprises the steps of project list cost analysis, mechanical material data analysis, comprehensive unit price analysis and project summary analysis.
The technical scheme of the invention is further improved as follows: the project inventory cost analysis comprises an automatic inventory and an extraction inventory;
the mechanical material data analysis comprises the use condition of an outsourced machine, the utilization analysis of the self-owned machine and the analysis of the use condition of the material.
The technical scheme of the invention is further improved as follows: the settlement report generated by the financial settlement report generating module comprises: manual summary reports, mechanical summary reports, material summary reports;
the manual summary report comprises a customer manual fee settlement report;
the mechanical summary report comprises a customer mechanical fee settlement report, a customer mechanical fee detail report and a customer mechanical fee item detail report;
the material summary report includes a customer material fee settlement report, a customer material fee detail report, and a customer material fee item detail report.
Due to the adoption of the technical scheme, the invention has the technical progress that:
the highway engineering construction cost management system compiled based on the Excel-VBA improves the working efficiency and saves the labor cost.
The highway engineering construction cost management system compiled based on Excel-VBA improves the statistical precision, realizes PDCA circulation of cost management, and improves the management level of enterprises.
The highway engineering construction cost management system compiled based on Excel-VBA improves the working efficiency of financial settlement.
Drawings
FIG. 1 is an operation step diagram of a road engineering construction cost management system compiled based on Excel-VBA according to the present invention;
FIG. 2 is a detailed diagram of a data acquisition module of a highway engineering construction cost management system compiled based on Excel-VBA according to the present invention;
FIG. 3 is a detailed view of a data checking and querying module of a road engineering construction cost management system compiled based on Excel-VBA according to the present invention;
FIG. 4 is a detailed diagram of a data extraction module of a highway engineering construction cost management system compiled based on Excel-VBA according to the present invention;
FIG. 5 is a detailed diagram of a data analysis module of a highway engineering construction cost management system compiled based on Excel-VBA according to the present invention;
FIG. 6 is a detailed view of a financial settlement report module of the highway engineering construction cost management system compiled based on Excel-VBA.
Detailed Description
The invention is described in further detail below with reference to the following figures and examples:
the invention relates to a highway engineering construction cost management system compiled based on Excel-VBA, which collects data of a construction site through a mobile phone, analyzes and processes the data by using the system, automatically generates a report and adds a financial settlement function.
As shown in fig. 1 to 6, the system includes a data acquisition module for extracting data of the mobile phone, a data checking and querying module for counting and checking data, a data extraction module for extracting data, a data analysis module for merging data files, and a financial settlement report generation module for interfacing with a financial department.
Further, the system operation steps are as follows:
step 1) developing a VBA module;
step 2) setting a software interface and various functional areas in the Excel, and setting an operation window and a control;
step 3) calling the VBA data acquisition module to extract excel data acquired by a mobile phone;
step 4) calling the VBA data inspection module to perform statistical inspection data;
step 5) calling the VBA data extraction module to extract data;
step 6) calling the VBA data analysis module to analyze and merge a plurality of data files;
and 7) calling the VBA data financial module to generate merged payment data and interfacing with financial software data.
In the embodiment provided by the invention, the excel data collected by the mobile phone comprises the following data: cost plan data, cost statistical data, engineering volume data; the extracted excel data comprises: single file extraction, folder extraction, root directory extraction.
Further, the statistical examination data includes: before the system is used, excel is mainly adopted for cost analysis, a plurality of technicians are required to be invested every year, the average time of analyzing the cost of each project is 3 days, and the requirement of guiding construction management in time cannot be met; after the cost analysis system is used, cost analysis can be completed by only two technicians in the same day, and construction management can be guided more timely.
Further, the data analysis includes: the project inventory cost analysis, the mechanical material data analysis, the comprehensive unit price analysis and the project summary analysis are carried out, the data format is unified after the cost analysis system is used, the data is accurate, the annual project data is utilized to carry out enterprise quota updating every year, and the more accurate enterprise quota data is the basis for compiling the cost plan next year.
Further, the generated settlement report interfaced with the financial software data includes: report forms are gathered manually, report forms are gathered mechanically, report forms are gathered to the material, the system has designed multiple financial settlement report forms, but the multi-angle is checked with the customer and financial accounting, and promote the settlement efficiency, has reduced financial working strength again.
In the embodiment provided by the invention, the highway engineering construction cost management system compiled based on Excel-VBA provides a way of acquiring data of a construction site through a mobile phone, analyzing and processing the data by using the system, automatically generating a report, increasing a financial settlement function, improving the working efficiency, saving the labor cost, improving the management level of an enterprise and improving the working efficiency of financial settlement.
It should be understood that the above examples are only for clarity of illustration and are not intended to limit the embodiments. Other variations and modifications will be apparent to persons skilled in the art in light of the above description. And are neither required nor exhaustive of all embodiments. And obvious variations or modifications therefrom are intended to be within the scope of the invention.
Claims (10)
1. A highway engineering construction cost management system based on Excel-VBA compiling is characterized in that: the system comprises a data acquisition module for extracting mobile phone data, a data checking and inquiring module for counting checking data, a data extraction module for extracting data, a data analysis module for combining data files, and a financial settlement report generation module for connecting a financial department.
2. The Excel-VBA writing-based highway engineering construction cost management system according to claim 1, wherein: the system operation steps are as follows:
step 1) developing a VBA module;
step 2) setting a software interface and various functional areas in the Excel, and setting an operation window and a control;
step 3) calling the VBA data acquisition module to extract excel data acquired by a mobile phone;
step 4) calling the VBA data inspection module to perform statistical inspection data;
step 5) calling the VBA data extraction module to extract data;
step 6) calling the VBA data analysis module to analyze and merge a plurality of data files;
and 7) calling the VBA data financial module to generate merged payment data and interfacing with financial software data.
3. The Excel-VBA writing-based highway engineering construction cost management system according to claim 1, wherein: the mobile phone data extracted by the data acquisition module comprises: cost planning data, cost statistical data, and engineering volume data.
4. The Excel-VBA written road engineering construction cost management system according to claim 3, wherein the system comprises: the cost plan data comprises comprehensive unit price cost plan data and common labor, material and machine name price data of each project;
the cost statistical data comprises labor cost data, mechanical cost data, material cost data, field expense data, purchase cost data, hospitality cost data and safety cost data;
the engineering quantity data comprises construction period node completion engineering quantity data.
5. The Excel-VBA writing-based highway engineering construction cost management system according to claim 1, wherein: the data statistically checked by the data checking and inquiring module comprises the following data: data checking table, manpower fee inquiry table, mechanical fee inquiry table, material fee inquiry table, field expense inquiry table, purchase fee inquiry table, hospitality fee inquiry table and safety fee inquiry table.
6. The Excel-VBA written road engineering construction cost management system according to claim 5, wherein the system comprises: the data checking table comprises whether the item name in the acquired data is wrong or not, whether the money in the acquired data is wrong or not, whether the date in the acquired data is wrong or not, and whether the machine, material and field name in the acquired data are wrong or not.
7. The Excel-VBA writing-based highway engineering construction cost management system according to claim 1, wherein: the excel data extracted by the data extraction module comprises the following steps: single file extraction, folder extraction, root directory extraction.
8. The Excel-VBA writing-based highway engineering construction cost management system according to claim 1, wherein: the data analyzed by the data analysis module comprises: the method comprises the steps of project list cost analysis, mechanical material data analysis, comprehensive unit price analysis and project summary analysis.
9. The Excel-VBA writing-based highway engineering construction cost management system according to claim 8, wherein: the project inventory cost analysis comprises an automatic inventory and an extraction inventory;
the mechanical material data analysis comprises the use condition of an outsourced machine, the utilization analysis of the self-owned machine and the analysis of the use condition of the material.
10. The Excel-VBA writing-based highway engineering construction cost management system according to claim 1, wherein: the settlement report generated by the financial settlement report generating module comprises: manual summary reports, mechanical summary reports, material summary reports;
the manual summary report comprises a customer manual fee settlement report;
the mechanical summary report comprises a customer mechanical fee settlement report, a customer mechanical fee detail report and a customer mechanical fee item detail report;
the material summary report includes a customer material fee settlement report, a customer material fee detail report, and a customer material fee item detail report.
Applications Claiming Priority (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2020113215149 | 2020-11-23 | ||
| CN202011321514 | 2020-11-23 |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112734403A true CN112734403A (en) | 2021-04-30 |
Family
ID=75595913
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110312470.1A Pending CN112734403A (en) | 2020-11-23 | 2021-03-24 | Highway engineering construction cost management system compiled based on Excel-VBA |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112734403A (en) |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20020038268A1 (en) * | 2000-09-25 | 2002-03-28 | Chiun-Kwei Yang | Financial cost prediction system and method |
| US20130013993A1 (en) * | 2011-07-07 | 2013-01-10 | Kong Ping Oh | Spreadsheet-based templates for supporting the systems engineering process |
| CN105320806A (en) * | 2015-08-21 | 2016-02-10 | 中国核电工程有限公司 | Method for summarizing nuclear power technology piping materials by combing PML with Excel-VBA to perform data processing |
| US20180232813A1 (en) * | 2015-08-24 | 2018-08-16 | Ju Ho Kim | Business management system and method through generation of accounting and financial information |
-
2021
- 2021-03-24 CN CN202110312470.1A patent/CN112734403A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20020038268A1 (en) * | 2000-09-25 | 2002-03-28 | Chiun-Kwei Yang | Financial cost prediction system and method |
| US20130013993A1 (en) * | 2011-07-07 | 2013-01-10 | Kong Ping Oh | Spreadsheet-based templates for supporting the systems engineering process |
| CN105320806A (en) * | 2015-08-21 | 2016-02-10 | 中国核电工程有限公司 | Method for summarizing nuclear power technology piping materials by combing PML with Excel-VBA to perform data processing |
| US20180232813A1 (en) * | 2015-08-24 | 2018-08-16 | Ju Ho Kim | Business management system and method through generation of accounting and financial information |
Non-Patent Citations (3)
| Title |
|---|
| 常传文 等: "利用VBA和数据透视表打造轻量级工作量统计分析软件", 《中国管理信息化》 * |
| 张自力 等: "境外炼化项目建设物料信息追踪系统的开发与应用", 《石油工程建设》 * |
| 胡蓉: "运用Excel软件实现工程成本测算", 《铁路工程造价管理》 * |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US20070021992A1 (en) | Method and system for generating a business intelligence system based on individual life cycles within a business process | |
| CN111767247A (en) | File specification and synchronous forming control platform for real-time supervision of engineering data | |
| US20070067756A1 (en) | System and method for enterprise software portfolio modernization | |
| CN202948475U (en) | Information management system of construction material | |
| CN104424613A (en) | Value added tax invoice monitoring method and system thereof | |
| CN110851667A (en) | Integrated analysis method and tool for multi-source large data | |
| CN113408890A (en) | Artificial intelligence-based method and system for generating evaluation report after industrial investment project | |
| CN106251215A (en) | A kind of financial budget analyzes system | |
| CN104899143A (en) | Software peer review system realizing device for providing DM (Data Mining) | |
| CN107729461A (en) | A kind of tax data acquisition method | |
| CN112100264A (en) | Power project contract monitoring system and method | |
| CN105069683A (en) | Tax risk assessment system | |
| CN104766240A (en) | Electronic banking data processing system and method | |
| CN115170090A (en) | Project management method and device, electronic equipment and readable storage medium | |
| CN107749028A (en) | A kind of tax digitizes checking method | |
| CN111476650A (en) | Financial automatic monthly payment system based on artificial intelligence technology | |
| CN104636341A (en) | Data cleaning storage method for added value tax one-number multi-name monitoring | |
| CN112651707A (en) | Integrated system for realizing standardized automatic accounting bookkeeping based on industry and finance integration | |
| CN111143431B (en) | Intelligent charge checking and anomaly identification system | |
| CN112214557A (en) | Data matching classification method and device | |
| CN105069546A (en) | Multi-service cost budget requirement data processing method for electric power enterprise | |
| CN112734403A (en) | Highway engineering construction cost management system compiled based on Excel-VBA | |
| CN111913962A (en) | Multi-dimensional annual detailed fund planning system and method | |
| CN101169846A (en) | Kanban data processing method and kanban data processing system | |
| CN112214536B (en) | Enterprise account data real-time import management system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20210430 |
|
| RJ01 | Rejection of invention patent application after publication |