Method for automatically generating green building technical scheme based on EXCEL-VBA

Abstract

The application discloses a kind of green building technical scheme automatic generation method based on EXCEL-VBA, with EXCEL-VBA as operating carrier, comprising the following steps: S1.green building score data is obtained: traversing building engineering project green building score data, obtains green building star and six sub-item score conditions;S2.check score situation and make corrections: traverse the data cell of score table, compare input data with preset rule and standard, for the data that does not meet the requirements, pop-up window indicates the place of error and modification suggestion, until pop-up window prompts that scoring is correct;S3.generate green building technical scheme: according to score table data and preset technical provisions and rules, generate the technical scheme that belongs to project by profession, store in independent worksheet;S4.export green building technical scheme: according to pop-up window prompt, select combined professional technical scheme that needs to be exported, and automatically stored in local user computer folder.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of file generation, in particular, especially relates to a method for quickly generating a special building engineering project green building technical scheme. BACKGROUND

[0002] Green building evaluation is an important work to build green buildings, and the core person is the green building consultant. They need to tailor technical solutions for building engineering projects in the early stage of design according to the "Green Building Evaluation Standard" GB / T50378GB / T50378, and ensure that the evaluation provisions and technical details in the "Green Building Evaluation Standard" GB / T50378 can be effectively positioned in different professional drawings, so that the building engineering project can finally meet the star level requirements (one star, two stars or three stars) in the standard.

[0003] The "Green Building Evaluation Standard" GB / T50378 covers 108 evaluation provisions, which are classified according to "safety and durability, health and comfort, life convenience, resource conservation, environment livable, improvement and innovation". Each sub-item contains control items and scoring items, and these evaluation provisions are related to technical requirements of different professions, covering 13 professions such as "architecture, structure, water supply and drainage, electrical, green building, cost, air conditioning, BIM, intelligent, landscape, decoration, identification, floodlight". Because the green building star level requirements and design characteristics of each building engineering project are different, the technical solutions for each profession will change accordingly.

[0004] In the traditional workflow, the green building consultant often needs to: 1. According to the actual situation, in the EXCEL evaluation table covering only the evaluation provisions of the "Green Building Evaluation Standard" GB / T50378, score the building engineering project so that it can meet the star level requirements; 2. According to the final score of the EXCEL score table, separately produce Word version technical solutions (including technical provisions and technical details) for different professions, and finally deliver to each profession.

[0005] The EXCEL score table is compiled according to the logic of the "Green Building Evaluation Standard" GB / T50378, which can help the green building consultant quickly master the overall score and star level compliance. But the Word version technical solution delivered to each profession needs to be reclassified and sorted out, and the technical details that designers can easily understand need to be added based on the technical provisions. Therefore, when dealing with a large amount of data and complex building engineering project requirements, traditional manual operation is often prone to omissions, affecting the accuracy and integrity of the technical solution. The traditional manual processing method has been unable to meet the growing business needs, seriously restricting the improvement of work efficiency and quality.

[0006] Although the generation of green building technical solutions can still rely on the green building evaluation software systems developed on the market, these software systems are complex and have a learning cost, and generally require each professional to fill in online in conjunction, which is undoubtedly an additional burden for engineers in the construction industry with a large amount of business, and it is difficult to achieve the original design goal of the software in actual execution. In addition, with the increasing requirements of carbon control and consumption reduction, and the requirement of developing high-quality green buildings, the Green Building Evaluation Standard GB / T50378 is also being iteratively upgraded, and the software system may also have an update cost.

[0007] Therefore, in the field of green building consulting services, there is an urgent need for a lightweight and automated technical solution generation method to effectively simplify the tedious process of manually checking clauses and technical rules across software, and to release more time for more valuable work. SUMMARY

[0008] The purpose of the present application is to solve the problem of low efficiency and insufficient accuracy caused by tedious manual operation in the prior art, and to provide a green building technical solution automatic generation method based on EXCEL-VBA, which uses EXCEL-VBA as the operation carrier of green building technical solution formulation, and can conveniently and quickly assist green building consultants to complete the green building investment work in the design phase.

[0009] In order to achieve the above purpose, the technical scheme adopted by the present application is as follows:

[0010] A green building technical solution automatic generation method based on EXCEL-VBA, using EXCEL-VBA as the operation carrier, realized by VBA code, comprising the following steps:

[0011] S1. Obtain green building score data: after completing the score table of the building engineering project, traverse the green building score data of the building engineering project to obtain the green building star rating and six sub-item score conditions;

[0012] S2. Check the score conditions and correct errors: traverse the data cells of the score table, compare the input data with the preset rules and standards, and for data that does not meet the requirements, pop up a window to indicate the error and modification suggestions until the score is correct as prompted by the pop-up window;

[0013] S3. Generate green building technical solutions: according to the score table data and the preset technical clauses and rules, generate technical solutions specific to the building engineering project by professional, and store in an independent worksheet;

[0014] S4. Export green building technical solutions: according to the pop-up window prompt, flexibly select and combine the professional technical solutions that need to be exported, and automatically store them in the local user computer folder.

[0015] Further, the step S1 comprises: setting a visualization control interface, setting a database worksheet, automatically calculating scores and visualizing presentation, dynamically updating data worksheet.

[0016] The visualization control interface specifically comprises:

[0017] On the basis of the existing green building scoring table, one project design stage self-assessment score table, one project design stage self-assessment score chart and three interactive macro buttons are added.

[0018] The project design stage self-assessment score table of the visualization control interface is used to display the numerical information of the control items and the "pre-evaluation score, pre-evaluation minimum score, pre-evaluation sub-item score, pre-evaluation sub-item score ratio, sub-item score minimum requirement, total score" of the six sub-items of "safety durability, health comfort, life convenience, resource conservation, environment livability and improvement and innovation".

[0019] The project design stage self-assessment score chart of the visualization control interface includes: one green building star level schematic diagram, which directly displays the corresponding star level of the project; one hexagonal radar chart, which displays the pre-evaluation score ratio of each sub-item and the comparison with the minimum score requirement line.

[0020] The three interactive macro buttons of the visualization control interface include: the "check the score of the current project" macro button: associated with step S2, check the score for error correction; the "generate project green building technical scheme" macro button: associated with step S3, generate the green building technical scheme; the "export project green building technical scheme" macro button: associated with step S4, export the green building technical scheme.

[0021] Further, the database worksheet is set, specifically:

[0022] Two independent worksheets are added to store the basic data required for the green building technical scheme, the content is designed according to the "Green Building Evaluation Standard" GB / T50378, and special processing is performed. The two independent worksheets include the "control item technical provisions and details" worksheet and the "scoring item technical provisions and details" worksheet.

[0023] The content of the "control item technical provisions and details" worksheet includes: storing all control item provisions in the "Green Building Evaluation Standard" GB / T50378 in order of provision number, and including the explanation and description of the provisions and the proof drawing requirements; an additional cell is added for each provision to mark the professional attribution involved in the provision, such as "building", "structure", "electrical", etc.; for provisions involving multiple specialties, use separators (such as commas) to mark all related specialties. According to the professional marking in the additional cell, the provisions are assigned to the corresponding professional category as the condition for generating the technical scheme.

[0024] The content of the "Scoring Item Technical Provisions and Rules" worksheet includes: storing scoring item provision information in order of provision number, including specific technical requirements, evaluation rules, and proof document requirements of the scoring item; adding a cell for each provision to mark the professional attribution involved in the provision, such as "architecture", "structure", "electrical", etc.; for provisions involving multiple specialties, use a separator (such as a comma) to mark all related specialties. According to the professional attribution marked in the additional cell, the provisions are assigned to the corresponding professional category as a condition for generating technical solutions.

[0025] Special processing is set in the database worksheet, specifically: for selective scoring provisions such as step scoring provisions or building type scoring provisions, whether the provision is scored or not, the provision and rules are included in the database worksheet in their entirety to ensure the completeness of the screening conditions.

[0026] Further, the score is automatically calculated and visually presented, specifically:

[0027] After the green building consultant completes the scoring and clicks the save button, the six item scores and the total score are automatically calculated by traversing the pre-set SUM function, where the six item scores are filled into the "pre-evaluation item score" column of the "project design phase self-evaluation score table" in the visual control interface, and the total score is filled into the "total score" column. The proportion of each item score is updated in the hexagonal radar chart of the "project design phase self-evaluation score chart", showing the comparison relationship of the item score with the minimum requirement in the radar chart. According to the star threshold of the "Green Building Evaluation Standard" GB / T50378 and the total score, the green building level is determined, and the green building star diagram is presented in graphical form in the "project design phase self-evaluation score chart" to help users quickly identify the project rating.

[0028] The star threshold of the "Green Building Evaluation Standard" GB / T50378 in the automatic calculation of the score and visual presentation: one star, total score ≥ 60; two stars, total score ≥ 70; three stars, total score ≥ 85.

[0029] Further, the data worksheet is dynamically updated, specifically:

[0030] When the user completes the scoring and clicks the "Save" button, the "Control Item Technical Provisions and Rules" and "Scoring Item Technical Provisions and Rules" worksheets are automatically updated by VBA code, with the following specific operations:

[0031] According to the provision number, a one-to-one mapping relationship is established between the scoring cells of the scoring table and the provision cells of the two database worksheets through VBA code, and the state of the scoring cells affects the font color of the database worksheet cells;

[0032] When the score cell is assigned a non-zero score, the font color of the corresponding article cell in the database table is automatically set to "black"; when the score cell is assigned a score of zero, the font color of the corresponding article cell in the database table is automatically changed to "white".

[0033] The purpose of this operation is to provide clear conditions for screening and generating green building technical solutions in step S3, i.e. only screening articles and rules with font color "black" into the technical solution content.

[0034] Further, step S2 includes: presetting rules, checking score data cell by cell, checking score proportion step by step, feedback and correction process, dynamic verification and confirmation.

[0035] The preset rules are as follows: according to "Green Building Evaluation Standard" GB / T50378, the following six VBA code rules for checking the correctness of the score are set:

[0036] Condition one: the control item must meet the requirements, i.e. the pre-evaluation score proportion of the control item must be 100%.

[0037] Condition two: score item score limit, i.e. the score of the score item must be strictly consistent with the limited score of the article.

[0038] Condition three: ladder score uniqueness check, i.e. identify the relevant cell group of the ladder score article, and only allow one cell to have a score value; if multiple cells are assigned scores, prompt the user to correct, and the user needs to correct the data themselves.

[0039] Condition four: building type score uniqueness check, i.e. for articles that distinguish between building types (such as public buildings and residential buildings), check whether scores are assigned only to one type; if multiple types are found to have scores, prompt the user to correct, and the user needs to select one type of score and remove the score values of other types themselves.

[0040] Condition five: score proportion range of the five main items, i.e. the pre-evaluation score proportion of the five items "safety and durability, health and comfort, convenient life, resource conservation, and environment-friendly" should be between 30% and 100%.

[0041] Condition six: "improvement and innovation" item score limit, i.e. the total score of this item cannot exceed 100 points, and the pre-evaluation score proportion should be between 0% and 100%.

[0042] Checking score data cell by cell, specifically: through the six conditions in the preset rules, each cell in the score table is checked one by one through VBA code, and control item verification and score item verification are completed respectively.

[0043] Control item check: Check if the control item score meets condition one (the pre-evaluation score must be 100%). If not, prompt the user to adjust the control item score data.

[0044] Score item check includes:

[0045] Score consistency check: Check if the score of the score item cell is consistent with the standard. If the score is not consistent, prompt the user to correct the standard score.

[0046] Ladder score uniqueness check: Identify the cell group belonging to the ladder score, and only allow one cell to have a score. If multiple cells have scores, a pop-up window prompts "Ladder score of article number XX only allows one option to score, please correct", the user needs to select a score and clear the other cell assignments.

[0047] Building type score uniqueness check: Identify the article that distinguishes building types and check if only one type is scored. If multiple types are scored, a pop-up window prompts "Building type score of article number XX only allows one type to score, please correct", the user needs to select a building type score and clear the other scores.

[0048] Further, the score percentage is checked step by step, specifically: After completing the score cell check, the table data in the visualization control interface is further verified, including:

[0049] Control item score percentage check: Check if the pre-evaluation score percentage of the control item is equal to 100%. If not, prompt the user "The pre-evaluation score percentage of the control item must be 100%, please adjust the relevant evaluation".

[0050] Five main score percentage checks: Check if the pre-evaluation score percentage of the five sub-items "safety and durability, health and comfort, life convenience, resource conservation, and environment livability" is within the range of 30% to 100%. If it exceeds the range, a pop-up window prompts "The pre-evaluation score percentage of sub-item 'XX' exceeds the allowed range (30%-100%), please adjust the score data".

[0051] "Improvement and innovation" sub-item score limit check: Check if the total score of this sub-item exceeds 100 points, and if the pre-evaluation score percentage is within the range of 0% to 100%. If not, prompt "The score limit of the 'Improvement and Innovation' sub-item is 0-100 points, with a percentage range of 0%-100%, please adjust the relevant score".

[0052] Feedback and correction process includes prompting error information and user correction data, specifically:

[0053] Item-by-item error information: feedback error information item by item through pop-up window, including the following content: error scoring cells (such as article number or sub-item name); error type (such as score out of limits, multiple cell scoring, etc.); modification suggestions (such as "only one scoring item is retained", "adjust the score to the standard score XX").

[0054] User correction data: after the user corrects the scoring data according to the prompt, the user can click the "check the scoring situation of this item" macro button again to start the next round of checking.

[0055] Dynamic verification and confirmation, specifically:

[0056] Dynamic updating of the data state of the scoring table and the visual control interface, including total score, sub-item proportion and star level result, real-time reflection of user adjustment; when all rules are met, a MessageBox pop-up window prompts "all scoring data has passed verification", and allows the user to proceed to the next step.

[0057] Further, step S3 includes: triggering the generation of technical solutions, filtering the contents of the articles that meet the conditions, generating technical solutions by category and profession, single generation and user control, and user confirmation.

[0058] Triggering the generation of technical solutions, specifically:

[0059] After the user clicks the "generate project green building technical solution" macro button, the following operation process is started: read the current scoring results from the scoring table; filter the article contents that meet the conditions from the database worksheet; integrate the control item and scoring item article information in the two database worksheets to generate technical solutions; arrange the generated technical solutions in order of article number and store them in a separate worksheet.

[0060] Filtering the contents of the articles that meet the conditions includes filtering criteria and filtering logic, specifically:

[0061] The filtering criteria include two aspects of requirements: scoring results and font color state. Scoring results: articles with non-zero scores in the scoring table are valid articles; font color state: articles with black font color in the database worksheet are valid articles, and articles with white font color are not involved in generation.

[0062] Filtering logic: in the "control item technical articles and rules" worksheet, all control item articles are filtered out; in the "scoring item technical articles and rules" worksheet, all scoring item articles are filtered out; arrange them in order of article number from small to large according to the order of control item first and then scoring item, to ensure the logicality and orderliness of the technical solutions.

[0063] Generating technical solutions by category and specialty, specifically: generating technical solutions by classifying the selected provisions by specialty, including three steps: specialty classification, generating work sheets, and provision number sorting.

[0064] Specialty classification: distributing provision content to corresponding specialties according to the additional specialty classification information of the provisions; optionally, if a provision involves multiple specialties (e.g., "building, electrical"), its content is copied to the technical solutions of each related specialty.

[0065] Generating work sheets: generating an independent work sheet for each specialty, such as "building technical solutions" and "electrical technical solutions"; the work sheet content includes provision number, provision content, and technical details (including explanatory notes and proof drawing requirements)

[0066] Provision number sorting includes: first classifying all provisions by control items and scoring items, and automatically generating corresponding table headers "control item technical provisions" and "scoring item technical provisions" in each specialty independent work sheet; each type of provision number is arranged from small to large to ensure that the solution content is clear and orderly.

[0067] Single generation and user control: perform a complete technical solution generation operation once each time the macro button is clicked; if the user modifies data in the scoring table, the macro button must be clicked again to trigger the update of the technical solution; no dynamic storage and automatic verification, only store the results after generation is complete.

[0068] User confirmation: after completing all technical solution generation, notify the user that the technical solutions for all specialties have been generated in the form of a pop-up window; after user confirmation, optionally proceed to S4: export green building technical solutions.

[0069] Further, step S4 includes: selective export interface, export file generation, automatic storage and user verification, export completion prompt.

[0070] Selective export interface, specifically: create a pop-up interface through VBA to allow users to flexibly select the specialty technical solutions they want to export. The pop-up interface includes the following content:

[0071] Specialty list, associate specialties with numbers, users can input numbers to select specialties; when selecting multiple specialties, use separators (such as commas) to distinguish; export confirmation button, used to perform export operations.

[0072] Export file generation, specifically: according to user selection, automatically convert the content of the corresponding specialty technical solution work sheet into a Word document format, and perform the following operations:

[0073] File format standardization, set standardized titles, headers, footers, and paragraph styles for each document, such as horizontal pages;

[0074] File naming rules, name the file in the format of "Professional 1 + Professional 2 +... Technical Solution.docx";

[0075] File storage path, store the generated Word document to the location of the EXCEL file, for the convenience of user to find.

[0076] Automatic storage and user verification, specifically: after exporting the file, automatically store and directly pop up the Word document, the user can check whether the document content is complete.

[0077] Export completion prompt, specifically: after all selected professional technical solutions are successfully exported, a MessageBox pops up "Export Complete" prompt, and the entire method flow ends.

[0078] Compared with the prior art, the present application utilizes the automation function of EXCEL-VBA to realize the rapid screening and scoring of green building evaluation provisions, automatically generates technical solutions for different professionals, avoids omissions and repetitive labor in manual operation, and greatly improves efficiency and accuracy. Compared with traditional manual operation, the efficiency and accuracy are significantly improved.

[0079] The present application relies on the general office tool EXCEL, and the user can quickly complete the generation of technical solutions without learning complex software operations, thereby reducing the technical implementation threshold and learning cost. Compared with complex software systems, it is more lightweight and easy to use.

[0080] The present application can customize technical provisions and technical rules for 13 professionals of "building, structure, water supply and drainage, electrical, green building, cost, air conditioning, BIM, intelligentization, landscape, decoration, identification, floodlighting" according to the different star requirements of "Green Building Evaluation Standard" GB / T50378 and the characteristics of building projects, to ensure that the scheme highly fits the design requirements. Compared with single technical solution, it is more flexible and adaptive to multi-professional requirements.

[0081] The present application can quickly adjust the logic and rules of evaluation provisions through flexible VBA programming structure, adapt to the iterative upgrade requirements of "Green Building Evaluation Standard" GB / T50378, meet the long-term requirements of carbon control and consumption reduction and high-quality development of green building. Compared with fixed process tools, it is more suitable for updating and expanding standards. BRIEF DESCRIPTION OF DRAWINGS

[0082] Figure 1 It is a flowchart of the green building technical solution automatic generation method of the present application based on EXCEL-VBA.

[0083] Figure 2 It is a visual control interface in the embodiment.

[0084] Figure 3 VBA code for visual control of macro buttons in the example.

[0085] Figure 4 VBA code for visual control of macro buttons in the example.

[0086] Figure 5 VBA code for visual control of macro buttons in the example.

[0087] Figure 6 Pop-up prompt for the "Export Project Green Building Technical Solution" macro button in the example.

[0088] Figure 7 Technical solution example automatically generated in the example. DETAILED DESCRIPTION

[0089] The present application based on EXCEL-VBA green building technical solution automatic generation method is further described below in combination with the drawings and specific embodiments.

[0090] Currently, the formulation of green building technical solutions relies on manual operation of scoring tables and preparation of technical solutions by green building consultants, which has the problems of heavy workload, easy errors, low efficiency, etc. Especially when dealing with large and complex projects such as residential buildings, manual operation is prone to cause technical provisions and project requirements to be mismatched, thereby affecting the accuracy of green building star rating and the implementation effect of the solution.

[0091] The present application based on EXCEL-VBA green building technical solution automatic generation method is further described below in combination with the drawings and specific embodiments.

[0092] Please refer to Figure 1 The present application discloses a green building technical solution automatic generation method based on EXCEL-VBA, which uses EXCEL-VBA as the operation carrier and is realized through VBA code, including the following steps:

[0093] S1. Obtain green building scoring data: After completing the scoring table of the building engineering project, traverse the green building scoring data of the building engineering project to obtain the green building star rating and the six sub-item scoring conditions;

[0094] S2. Check the score to correct errors: traverse the data cells of the scoring table, compare the input data with the preset rules and standards, and for data that does not meet the requirements, a pop-up window indicates the error and modification suggestions until the pop-up window prompts that the scoring is correct;

[0095] S3. Generate green building technical solutions: based on the scoring table data and the preset technical provisions and rules in other worksheets, generate technical solutions for building engineering projects in 13 specialties, and store them in separate worksheets;

[0096] S4. Export green building technical solutions: according to the pop-up window prompts, flexibly select and combine the professional technical solutions that need to be exported, and automatically store them in the local user computer folder.

[0097] The following will be combined with an example of a residential building project to explain the specific implementation of the green building technical solution automatic generation method based on EXCEL-VBA.

[0098] This example takes a residential building project as an example, and the goal is to achieve a green building two-star standard, i.e. the total score is greater than 70 points. EXCEL-VBA is used as the core operation carrier, and all visual interfaces, database worksheets, and VBA codes of the three macro buttons are preset. The visual control interface is as shown in Figure 2 , the VBA codes of the three macro buttons of the visual control are as shown in Figure 3 , Figure 4 and Figure 5 , which are stored in Module 1, Module 2 and Module 3 respectively. Green building consultants do not need to make complex settings, they only need to complete the self-assessment scoring of the scoring table and click the macro buttons in turn according to the prompts to complete the operation.

[0099] Step S1: Traverse the green building scoring data of the building engineering project to obtain the green building star level and the six sub-item scoring conditions.

[0100] In step S1, the green building consultant first performs self-assessment scoring on the residential building project according to the "Green Building Evaluation Standard" GB / T50378-2019 (2024 revised edition), and uses EXCEL-VBA as the operation carrier to realize data acquisition and management through the following preset operations.

[0101] A preset visual control interface for dynamic presentation and interaction of data. The interface includes: a table displaying control items and six sub-items (safety and durability, health and comfort, life convenience, resource conservation, environment livability, and improvement and innovation) related score information; a hexagonal radar chart for showing the proportion of each sub-item score and the minimum score requirement line; a green building star level diagram for displaying the corresponding green building star level result of the project; three interactive macro buttons for checking the score, generating a technical solution, and exporting the technical solution.

[0102] Two database tables are preset: the "Control Item Technical Provisions and Rules" table and the "Scoring Item Technical Provisions and Rules" table. The provisions information in the database is designed and classified according to the "Green Building Evaluation Standard" GB / T50378-2019 (2024 revised edition), and each provision is attached with a professional attribution cell for marking the professional involved, such as "building", "electrical", etc. When involving multiple professionals, use commas to separate the marks.

[0103] After the green building consultant completes the scoring in the scoring table according to the project characteristics, clicks the "Save" button, and automatically performs the following operations through VBA code:

[0104] Dynamically calculates the score of each sub-item and the total score;

[0105] Updates the visual interface, including presenting the sub-item scores and total score in the table, updating the radar chart comparison results of the six sub-items "safety and durability, health and comfort, life convenience, resource conservation, environment livability, and improvement and innovation", and displaying the green building star level of the project in the star level diagram, for example, if the total score is 75, it displays the green building two-star level logo;

[0106] According to the scoring state in the scoring table, automatically updates the font color of the provision cells in the database table: the font color of the non-zero score provisions is set to "black", and the font color of the zero score provisions is set to "white" for subsequent screening and classification use.

[0107] Step S2: Check the score of the current project.

[0108] After the green building consultant clicks the "Check the score of the current project" macro button, the VBA code automatically performs the following operations to verify whether the scoring data meets the rules.

[0109] According to the "Green Building Evaluation Standard" GB / T50378-2019 (2024 revised edition), the scoring data is checked item by item, and the specific rules include:

[0110] The control item score percentage must be 100%; the score item score must strictly comply with the provisions; the ladder score provisions only allow one cell to assign a score; the score provisions that distinguish between building types only allow one type to assign a score; the five sub-items of safety durability, health and comfort have a score percentage range of 30% to 100%; the total score of the improvement and innovation sub-items should not exceed 100 points, with a score percentage range of 0% to 100%.

[0111] Each scoring cell and sub-item data is checked one by one, and a pop-up window prompts the green building consultant to correct errors. For example, if the score of a certain scoring item exceeds the limit, the prompt "The score of article number 4.2.1 does not meet the standard, please correct it to the standard value of 10 points." If multiple options of a ladder scoring article are assigned scores at the same time, the prompt "The ladder scoring of article number 5.2.2 only allows one option to assign a score, please correct it."

[0112] After the green building consultant corrects the scoring data according to the prompts, he or she can click the macro button again to recheck until the pop-up window prompts "All scoring data has passed the verification" to complete step S2.

[0113] Step S3: Generate project green building technical solutions. According to the scoring table data and the pre-set technical provisions and details in other worksheets, generate technical solutions specific to the project in 13 specialties and store them in separate worksheets.

[0114] After the green building consultant clicks the "Generate Project Green Building Technical Solutions" macro button, the VBA code generates technical solutions based on the current scoring data, which includes:

[0115] Filter the article content that meets the conditions from the database worksheet, and the filtering basis is: the articles that are assigned non-zero scores in the scoring table and the articles whose font color is black in the database worksheet.

[0116] Classify by control items and scoring items, and sort them in ascending order according to the article number. For example:

[0117] Control item technical solutions: include articles such as "4.1.2 Building structure should meet the requirements of bearing capacity and building function. Building exterior walls, roofs, doors, windows, curtain walls and external insulation should meet the requirements of safety, durability and protection."

[0118] Scoring item technical solutions: include articles such as "5.2.2 The selected decorative materials should meet the requirements of the current national green product evaluation standard for harmful substance limits."

[0119] According to the professional attribution information added to the clauses, the content of the clauses is distributed to the corresponding specialty. For example, "4.1.2 Building structure shall meet the bearing capacity and building function requirements" is distributed to "structural technical scheme". Alternatively, if a clause relates to multiple specialties, it is simultaneously copied to multiple specialty technical schemes. For example, "5.2.2 The selected decoration materials shall meet the requirements of the current national green product evaluation standard for harmful substance limit" is distributed to "building technical scheme" and "indoor decoration technical scheme".

[0120] Each specialty technical scheme generates an independent EXCEL worksheet, and the table header includes "control item technical clauses" and "scoring item technical clauses", and the clause content is sorted according to the classification number. The green building consultant does not need to perform additional operations, and the generation is automatically completed.

[0121] Step S4: Exporting the project green building technical scheme. According to the pop-up prompt, flexibly select and combine the specialty technical schemes that need to be exported, and automatically store them in the local user computer folder.

[0122] After the green building consultant clicks the "export project green building technical scheme" macro button, the pop-up window displays the following specialty technical scheme list for selection, as shown in Figure 6 The list includes: 1, building technical scheme; 2, structural technical scheme; 3, air conditioning technical scheme; 4, water supply and drainage technical scheme; 5, electrical technical scheme; 6, intelligent technical scheme.

[0123] After the green building consultant inputs the selection number (such as "1, 2, 3"), all the contents of the corresponding worksheets "building technical scheme", "structural technical scheme" and "air conditioning technical scheme" are automatically converted into a Word document format. For example, when "1, 2, 3" is input, the generated file is named "building + structure + air conditioning technical scheme.docx", and is stored in the path of the EXCEL file, and the Word document is automatically opened for the green building consultant to check the content, as shown in Figure 7 .

[0124] After the export is completed, the pop-up window prompts "export completed". The green building consultant can directly view the generated document to complete the production and delivery of the green building technical scheme.

[0125] In summary, the present application utilizes the automation function of EXCEL-VBA to realize the rapid screening and scoring of green building evaluation clauses, automatically generates technical schemes for different specialties, avoids omissions and repetitive labor in manual operation, and greatly improves the efficiency and accuracy. Compared with traditional manual operation, the efficiency and accuracy are significantly improved.

[0126] The application relies on the general office tool of EXCEL, and a user can quickly start and complete the generation of a technical scheme without learning complex software operation, thereby reducing the technical implementation threshold and learning cost. Compared with a complex software system, the application is more lightweight and easy to use.

[0127] The application can customize technical provisions and technical details for 13 professions of "building, structure, water supply and drainage, electricity, green building, cost, air conditioning, BIM, intelligence, landscape, decoration, identification and floodlighting" according to different star requirements of the Green Building Evaluation Standard GB / T50378 and the characteristics of a building project, and ensure that the scheme highly fits the design requirements. Compared with a single technical scheme, the application is more flexible and adaptive to the requirements of multiple professions.

[0128] The application can quickly adjust the logic and rules of the evaluation provisions through a flexible VBA programming structure, adapt to the iterative upgrade requirements of the Green Building Evaluation Standard GB / T50378, and meet the long-term requirements of carbon control and consumption reduction and high-quality development of green buildings. Compared with a fixed process tool, the application is more adaptive to the update and expansion of the standard.

[0129] The above description is a detailed description of the preferred and feasible embodiments of the application, but the embodiments are not used to limit the patent application range of the application, and any equivalent changes or modified changes completed under the technical spirit disclosed by the application should belong to the patent range covered by the application.

Claims

1. An EXCEL-VBA-based green building technical scheme automatic generation method, characterized in that, With EXCEL-VBA as the operation carrier, the following steps are realized through VBA code: S1. Obtain green building scoring data: after completing the scoring table of the building engineering project, traverse the green building scoring data of the building engineering project to obtain the green building star rating and the six sub-item scoring conditions; S2. Check the scoring conditions for error correction: traverse the data cells of the scoring table, compare the input data with the preset rules and standards, and for data that does not meet the requirements, pop up a window to indicate the error and modification suggestions until the scoring is correct as prompted by the pop-up window; S3. Generate green building technical solutions: based on the scoring table data and the preset technical provisions and details, generate technical solutions specific to the building engineering project by specialty, and store them in separate worksheets; S4. Export green building technical solutions: according to the pop-up window prompts, flexibly select and combine the professional technical solutions that need to be exported, and automatically store them in the local user computer folder; Step S1 includes: setting a visual control interface, setting a database worksheet, automatically calculating scores and visualizing them, and dynamically updating the data worksheet; The visual control interface is set as follows: On the basis of the existing green building scoring table, add 1 project design phase self-assessment scoring table, 1 project design phase self-assessment scoring chart, and 3 interactive macro buttons; The project design phase self-assessment scoring table is used to display the control item and the "pre-evaluation score, pre-evaluation minimum score, pre-evaluation sub-item score, pre-evaluation sub-item score ratio, sub-item score minimum requirement, total score" numerical information of the six sub-items "safety durability, health and comfort, life convenience, resource conservation, environment livability, and improvement and innovation"; The project design phase self-assessment scoring chart includes: 1 green building star rating diagram, which visually displays the corresponding star rating of the project; 1 six-sided radar chart, which displays the pre-evaluation score ratio of each sub-item and the comparison with the minimum score requirement line; The 3 interactive macro buttons include: "Check the scoring conditions of this project" macro button, associated with step S2, to check the scoring conditions for error correction; "Generate project green building technical solutions" macro button, associated with step S3, to generate green building technical solutions; "Export project green building technical solutions" macro button, associated with step S4, to export green building technical solutions; Step S2 includes: preset rules, cell-by-cell checking of scoring data, step-by-step checking of score ratio, feedback and correction process, dynamic verification and confirmation; The preset rules are as follows: according to the "Green Building Evaluation Standard" GB / T50378, the following six VBA coding rules for checking the correctness of the score are set: Condition one: the control item must meet the requirements, i.e. the control item pre-evaluation score ratio must be 100%; Condition two: score item score limit, i.e. the score item score must strictly match the provision score; Condition three: ladder score uniqueness check, i.e. identify the related cell group of ladder score provisions, only allow one cell to have a score value; if multiple cells have scores, prompt the user to correct the data, and the user needs to correct the data themselves; Condition four: building type score uniqueness check, i.e. for the provisions distinguishing building types, check whether only one type is scored; if multiple types are scored, prompt the user to correct, and the user needs to select one type score and clear the scores of other types by himself / herself; Condition five: score proportion range of the five main sub-items, i.e. the pre-evaluation score proportion of the five sub-items "safety and durability, health and comfort, life convenience, resource conservation, and environment livability” should be between 30% and 100%; Condition six: score limit of the sub-item "improvement and innovation”, i.e. the total score of this sub-item cannot exceed 100, and the pre-evaluation score proportion should be between 0% and 100%; Cell-by-cell score data checking, specifically: through the six conditions in the preset rules, each cell in the score table is checked one by one through VBA code, and control item checking and score item checking are completed respectively; Control item checking: check whether the control item score meets condition one; if not, prompt the user to adjust the control item score data; Score item checking includes: Score consistency check: check whether the score value of the score item cell is consistent with the standard; if not, prompt the user to correct to the standard score value; Ladder score uniqueness check: identify the cell group belonging to ladder score, and only allow one cell to have a score value; if multiple cells have scores, a pop-up window prompts "ladder score of provision number XX only allows one option to score, please correct”, and the user needs to select one score value and clear the scores of other cells; Building type score uniqueness check: identify the provisions distinguishing building types, and check whether only one type is scored; if multiple types are scored, a pop-up window prompts "building type score of provision number XX only allows one type to score, please correct”, and the user needs to select one building type score and clear the scores of other types; Step S3 includes: triggering technical solution generation, filtering content of provisions meeting the conditions, generating technical solutions by category and specialty, single generation and user control, and user confirmation; Triggering technical solution generation, specifically: After the user clicks the "generate project green building technical solution” macro button, the following operation process is started: read the current score results from the score table; filter the content of provisions meeting the conditions from the database table; integrate the control item and score item provision information in the two database tables to generate technical solutions; arrange the generated technical solutions in order of provision number and store them in a separate table; Filtering content of provisions meeting the conditions includes filtering basis and filtering logic, specifically: Filtering basis includes two aspects of requirements: score results and font color state. Score results: provisions with non-zero scores in the score table are valid provisions; font color state: provisions with black font color in the database table are valid provisions, and provisions with white font color do not participate in generation; Filtering logic: in the "control item technical provisions and details” table, all control item provisions are filtered out; in the "score item technical provisions and details” table, all score item provisions are filtered out; arrange them in order of provision number from small to large in the order of control item first and then score item, to ensure the logicality and orderliness of the technical solutions.

2. The EXCEL-VBA based green building technical scheme automatic generation method according to claim 1, characterized in that, The database worksheet is set, specifically: Two independent worksheets are added to store the basic data required for green building technical solutions. The content is designed according to the "Green Building Evaluation Standard" GB / T50378 and is specially processed. The two independent worksheets include the "Control Item Technical Provisions and Details" worksheet and the "Scoring Item Technical Provisions and Details" worksheet. The content of the "Control Item Technical Provisions and Details" worksheet includes: storing all control item provisions in the "Green Building Evaluation Standard" GB / T50378 in order of provision number, including explanations and illustrations of the provisions and proof drawing requirements; adding a cell for each provision to mark the professional attribution involved in the provision; for provisions involving multiple specialties, use a separator to mark all related specialties; according to the professional attribution in the additional cell, assign the provision to the corresponding professional category as a condition for generating technical solutions by classification; The content of the "Scoring Item Technical Provisions and Details" worksheet includes: storing scoring item provision information in order of provision number, including specific technical requirements, evaluation rules, and proof document requirements; adding a cell for each provision to mark the professional attribution involved in the provision; for provisions involving multiple specialties, use a separator to mark all related specialties; according to the professional attribution in the additional cell, assign the provision to the corresponding professional category as a condition for generating technical solutions by classification; Special processing, specifically: for selective scoring provisions, whether the provision is scored or not, the provision and details are included in the database worksheet in their entirety to ensure the completeness of the screening conditions.

3. The EXCEL-VBA based green building technical scheme automatic generation method according to claim 1, characterized in that, Automatic calculation of scores and visual presentation, specifically: After the green building consultant completes the scoring and clicks the save button, the six sub-item scores and the total score are automatically calculated through the pre-set SUM function, where the six sub-item scores are filled into the "pre-evaluation sub-item score" column of the "project design phase self-evaluation score table" in the visual control interface, and the total score is filled into the "total score" column; the proportion of each sub-item score is updated to the hexagonal radar chart of the "project design phase self-evaluation score chart", showing the comparison relationship of the sub-item scores in the radar chart with the minimum requirement; according to the star level threshold of the "Green Building Evaluation Standard" GB / T50378 and the total score, the green building level is determined, and the green building star level is presented in graphical form in the "project design phase self-evaluation score chart" to help users quickly identify the project rating; The star level threshold of the "Green Building Evaluation Standard" GB / T50378: one star, total score ≥ 60; Two stars, total score ≥ 70; Three stars, total score ≥ 85.

4. The EXCEL-VBA based green building technical scheme automatic generation method according to claim 1, characterized in that, Dynamic updating of data worksheet, specifically: When the user completes the scoring and clicks the "Save" button, the "Control Item Technical Provisions and Details" and "Scoring Item Technical Provisions and Details" worksheets are automatically updated through VBA code, with specific operations as follows: According to the provision number, a one-to-one mapping relationship between the scoring cells of the scoring table and the provision cells of the two database worksheets is established through VBA code, and the state of the scoring cells affects the font color of the database worksheet; When the score cell is assigned a non-zero score, the font color of the corresponding article cell in the database worksheet is automatically set to "black"; when the score cell is assigned a score of zero, the font color of the corresponding article cell in the database worksheet is automatically changed to "white".

5. The EXCEL-VBA based green building technical scheme automatic generation method according to claim 1, characterized in that, Step-by-step check the score proportion, specifically: After completing the score cell check, further verify the table data in the visualization control interface, including: Control item score proportion check: Check if the pre-evaluation score proportion of the control item is equal to 100%; if not, prompt the user that "the pre-evaluation score proportion of the control item must be 100%, please adjust the relevant evaluation"; Five main sub-item score proportion check: Check if the pre-evaluation score proportion of the five sub-items "safety durability, health and comfort, life convenience, resource conservation, and environment livability" is within the range of 30% to 100%; if it exceeds the range, a pop-up window prompts "the pre-evaluation score proportion of the sub-item 'XX' exceeds the allowed range, please adjust the score data"; "Improvement and innovation" sub-item score limit check: Check if the total score of this sub-item exceeds 100 points and the pre-evaluation score proportion is within the range of 0% to 100%; if not, prompt "the score limit of the 'improvement and innovation' sub-item is 0-100 points, with a proportion range of 0%-100%, please adjust the relevant score"; The feedback and correction process includes step-by-step error information and user data correction, specifically: Step-by-step error information: Feedback error information through pop-up windows, including error score cells, error types, and modification suggestions; user data correction: After the user corrects the score data according to the prompt, click the "check the score situation of this project" macro button again to start the next round of checking; Dynamic verification and confirmation, specifically: Dynamic update of score table and visualization control interface data status, including total score, sub-item proportion, and star level results, reflecting user adjustments in real time; when all rules are met, a MessageBox pop-up window prompts "all score data has passed verification" and allows the user to proceed to the next step.

6. The EXCEL-VBA based green building technology scheme automatic generation method according to claim 1, characterized in that, Generating technical solutions by category and specialty, specifically: Generate technical solutions by sorting the selected articles by specialty, including specialty division, generating a worksheet, and sorting article numbers; Specialty division: Assign article content to corresponding specialties based on additional specialty attribution information; if an article involves multiple specialties, copy its content to each related specialty's technical solution; Generate a worksheet: Generate an independent worksheet for each specialty, including article number, article content, and technical details; Article number sorting: All articles are first classified by control items and scoring items, and corresponding table headers "control item technical articles" and "scoring item technical articles" are automatically generated in each specialty's independent worksheet; each type of article is arranged in ascending order of article number to ensure that the solution content is clear and orderly; Single generation and user control, specifically: The complete technical scheme generation operation is performed once every time the macro button is clicked; if the user modifies the data in the score table, the macro button needs to be clicked again to trigger the update of the technical scheme; no dynamic storage and automatic verification are performed, and only the results are stored after the generation is completed; User confirmation, specifically: after all technical schemes are generated, a pop-up window is used to inform the user that the technical schemes of all specialties have been generated; after the user confirms, the user can optionally proceed to step S4: export green building technical schemes.

7. The EXCEL-VBA based green building technology scheme automatic generation method according to claim 1, characterized in that, Step S4 includes: selective export interface, export file generation, automatic storage and user verification, and export completion prompt. The selective export interface is specifically: A pop-up interface is created through VBA to allow the user to flexibly select the professional technical schemes to be exported; the pop-up interface includes: a specialty list, which is associated with numbers, and the user can input numbers to select specialties; when multiple specialties are selected, a separator can be used to distinguish them; an export confirmation button is used to perform the export operation. The export file generation is specifically: According to the user's selection, the corresponding professional technical scheme worksheet content is automatically converted into a Word document format, and the following operations are performed: file format standardization, setting standardized titles, headers, footers, and paragraph styles for each document; file naming rules, naming the file in the format "Specialty 1 + Specialty 2 +... Technical Scheme.docx"; file storage path, storing the generated Word document in the location of the EXCEL file for easy user access; Automatic storage and user verification, specifically: after the export file is generated, it is automatically stored and the Word document is directly popped up, and the user can check the completeness of the document content; The export completion prompt is specifically: after all selected professional technical schemes are successfully exported, a MessageBox is used to pop up a "Export Complete" prompt, and the entire method flow is completed.

Images