Green building technical scheme automatic generation method based on EXCEL-VBA

Abstract

The invention discloses an EXCEL-VBA-based green building technical scheme automatic generation method, which takes EXCEL-VBA as an operation carrier, and comprises the following steps: S1, obtaining green building scoring data: traversing the green building scoring data of a building engineering project, and obtaining green building star level and six sub-item scoring conditions; s2, checking the score condition and performing error correction: traversing data cells of the score table, comparing the input data with a preset rule and a standard, and indicating an error and a modification suggestion for the data which does not meet the requirement by the pop-up window until the pop-up window prompts that the score is correct; s3, generating a green building technical scheme: generating a technical scheme exclusive to a project according to specialty according to the score table data and preset technical provisions and detailed rules, and storing the technical scheme in an independent worksheet; and S4, exporting the technical scheme of the green building: selecting and combining the professional technical scheme needing to be exported according to a popup prompt, and automatically storing the professional technical scheme in a local user computer folder.

Description

Technical Field

[0001] The present invention relates to the technical field of document generation, and in particular to a method for rapidly generating a green building technology solution for an exclusive construction project. Background Art

[0002] Green building evaluation is an important part of building green buildings, and the core person is the green building consultant. They need to tailor technical solutions for construction projects in the early design stage based on my country's "Green Building Evaluation Standard" GB / T50378GB / T50378, and ensure that the evaluation clauses and technical details in the "Green Building Evaluation Standard" GB / T50378 can be effectively implemented in the drawings of different professions, so that the construction project can ultimately meet the star requirements (one star, two stars or three stars) in the standard.

[0003] The "Green Building Evaluation Standard" GB / T50378 contains 108 evaluation clauses, which are classified according to the six items of "safety and durability, health and comfort, convenience of life, resource conservation, livable environment, improvement and innovation". Each sub-item contains control items and scoring items, and these evaluation clauses involve technical requirements for different professions, covering 13 professions of "architecture, structure, water supply and drainage, electricity, green building, cost, air conditioning, BIM, intelligence, landscape, decoration, signs, and floodlighting". Since the green building star requirements and design characteristics of each construction project are different, the technical solutions for each profession will also change according to needs.

[0004] In the traditional workflow, green building consultants often need to: 1. Based on actual conditions, score construction projects in an EXCEL evaluation form that only covers the evaluation clauses of the "Green Building Evaluation Standard" GB / T50378, so that they can meet the star requirements; 2. Based on the final score of the EXCEL scoring form, separately prepare Word versions of technical solutions (including technical clauses and technical details) for different professions, and finally deliver them to each profession.

[0005] The EXCEL score sheet is compiled according to the logic of the Green Building Evaluation Standard GB / T50378, which can help green building consultants quickly grasp the overall score and star rating status. However, the Word version of the technical solution delivered to each professional needs to be reclassified and sorted, and technical details that are easy for designers to understand need to be added on the basis of technical clauses. Therefore, when dealing with large amounts of data and complex construction project requirements, traditional manual operations are often prone to omissions, affecting the accuracy and completeness of the technical solution. Traditional manual processing methods can no longer meet the growing business needs, and seriously restrict the improvement of work efficiency and quality.

[0006] Although the generation of green building technical solutions can also rely on the green building evaluation software systems developed on the market, these software systems are complex and have learning costs. Generally, each professional needs to work together online to fill in the form. This is undoubtedly an additional burden for engineers in the construction industry with a large volume of business. It is difficult to achieve the original design goals of the software at the actual implementation level. In addition, with the increasing requirements for carbon control and consumption reduction, as well as the requirements for the development of high-quality green buildings, the "Green Building Evaluation Standard" GB / T50378 is also being iterated and upgraded, and the software system may still have update costs.

[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 streamline the tedious process of manually checking clauses and technical details across software, freeing up more time for more valuable work. Summary of the invention

[0008] The purpose of the present invention is to solve the problems of low efficiency and insufficient accuracy caused by cumbersome manual operations in the prior art, and to provide a method for automatically generating green building technical solutions based on EXCEL-VBA. Using EXCEL-VBA as an operating carrier for formulating green building technical solutions can conveniently and quickly assist green building consultants to complete green building funding work in the design stage.

[0009] In order to achieve the above object, the technical solution adopted by the present invention is as follows: A method for automatically generating a green building technical solution based on EXCEL-VBA is implemented by VBA code using EXCEL-VBA as an operation carrier, and includes the following steps: S1. Obtain green building rating data: After completing the rating table of the construction project, traverse the green building rating data of the construction project to obtain the green building star rating and six sub-item ratings; S2. Check the score and make corrections: traverse the data cells of the score sheet, compare the input data with the preset rules and standards, and for data that does not meet the requirements, a pop-up window will indicate the error and modification suggestions until the pop-up window indicates that the score is correct; S3. Generate green building technical solutions: Generate technical solutions for construction projects based on the score sheet data and preset technical clauses and details, and store them in independent worksheets; S4. Export green building technical solutions: According to the pop-up prompts, flexibly select and combine the professional technical solutions that need to be exported, and automatically store them in the local user's computer folder.

[0010] Furthermore, step S1 includes: setting a visual control interface, setting a database worksheet, automatically calculating scores and presenting them visually, and dynamically updating the data worksheet.

[0011] Visual control interface, specifically: Based on the existing green building scoring table, a new project design phase self-assessment score table, a project design phase self-assessment score chart and three interactive macro buttons are added.

[0012] The project design phase self-assessment score table of the visual control interface is used to display the numerical information of the control items and the six sub-items of "safety and durability, health and comfort, convenience of life, resource conservation, livable environment, improvement and innovation", namely "pre-evaluation score, minimum pre-evaluation score, pre-evaluation sub-item score, proportion of pre-evaluation sub-item score, minimum requirement for sub-item score, and total score".

[0013] The project design phase self-assessment score chart of the visual control interface includes: 1 green building star rating diagram, which intuitively displays the corresponding star rating of the project; 1 hexagonal radar chart, which shows the proportion of the preliminary evaluation score of each sub-item and the comparison with the minimum score requirement line.

[0014] The three interactive macro buttons of the visual control interface include: "Check the score of this project" macro button: associated with step S2, check the score and correct it; "Generate project green building technical plan" macro button: associated with step S3, generate a green building technical plan; "Export project green building technical plan" macro button: associated with step S4, export the green building technical plan.

[0015] Furthermore, the database worksheet is set as follows: Two independent worksheets are added to store the basic data required for the green building technical plan. The content is designed according to the "Green Building Evaluation Standard" GB / T50378 and is specially processed. The two independent worksheets include the "Technical provisions and details of control items" worksheet and the "Technical provisions and details of scoring items" worksheet.

[0016] The contents of the "Technical Articles and Details of Control Items" worksheet include: storing all control item articles in the "Green Building Evaluation Standard" GB / T50378 in the order of article numbers, including explanations of the articles and requirements for supporting drawings; attaching a cell to each article to mark the professional affiliation involved in the article, such as "architecture", "structure", "electrical", etc.; for articles involving multiple professions, use separators (such as commas) to mark all relevant professions. According to the professional markings in the attached cells, the articles are assigned to the corresponding professional categories as the conditions for classifying and generating technical solutions.

[0017] The contents of the "Technical Articles and Details of Scoring Items" worksheet include: storing scoring item article information in order of article number, including specific technical requirements, evaluation rules and documentary requirements for scoring items; attaching a cell to each article to mark the professional affiliation involved in the article, such as "architecture", "structure", "electrical", etc.; for articles involving multiple professions, use separators (such as commas) to mark all relevant professions. According to the professional markings in the attached cells, the articles are assigned to the corresponding professional categories as the conditions for classifying and generating technical solutions.

[0018] Set up special processing in the database worksheet, specifically: for clauses with selective scoring, such as step-scoring clauses or clauses scored by building type, regardless of whether the clause is scored or not, all clauses and details will be fully included in the database worksheet to ensure the completeness of the screening criteria.

[0019] Furthermore, the scores are automatically calculated and visualized, 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 by traversing the preset SUM function. The six sub-item scores are filled in the "Pre-evaluation Sub-item Score" column of the "Project Design Phase Self-Assessment Score Table" in the visual control interface, and the total score is filled in the "Total Score" column; the proportion of each sub-item score is updated to the hexagonal radar chart of the "Project Design Phase Self-Assessment Score Chart", showing the comparison relationship between the sub-item score and the minimum requirement in the radar chart; according to the star threshold and total score of the "Green Building Evaluation Standard" GB / T50378, the green building grade is judged and presented in graphical form in the green building star diagram of the "Project Design Phase Self-Assessment Score Chart", to help users quickly identify the project rating.

[0020] Automatically calculate the score and visualize the star threshold of the Green Building Evaluation Standard GB / T50378: one star, total score ≥60; two stars, total score ≥70; three stars, total score ≥85.

[0021] Furthermore, the data worksheet is updated dynamically, specifically: When the user completes the scoring and clicks the "Save" button, the "Technical provisions and details of control items" and "Technical provisions and details of scoring items" worksheets are automatically updated through VBA code. The specific operations are as follows: According to the article number, a one-to-one mapping relationship is established between the scoring cells in the scoring table and the article cells in the two database worksheets through VBA code. The scoring cell status affects the font color of the database worksheet. When a scoring 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 a scoring cell is assigned a zero score, the font color of the corresponding article cell in the database worksheet is automatically changed to "white".

[0022] The purpose of this operation is to provide clear conditions for screening and generating green building technical solutions in step S3, that is, only the clauses and details with font color "black" are screened into the technical solution content.

[0023] Furthermore, step S2 includes: presetting rules, checking scoring data cell by cell, gradually checking score proportions, feedback and correction process, and dynamic verification and confirmation.

[0024] 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 scores are set: Condition 1: The control items must meet the requirements, that is, the control item pre-evaluation score must account for 100%.

[0025] Condition 2: The score limit for the scoring item, that is, the score for the scoring item must be strictly consistent with the score limit in the clause.

[0026] Condition three: Check the uniqueness of the ladder scoring, that is, identify the relevant cell groups of the ladder scoring clause, and only allow one of the cells to have a score value; if multiple cells are scored, the user will be prompted to make corrections, and the user needs to correct the data by himself.

[0027] Condition 4: Check the uniqueness of building type scores, that is, for the clauses that distinguish building types (such as public buildings and residential buildings), check whether only one type is scored; if multiple types are scored, prompt the user to make corrections, and the user needs to select one type of score and clear the other type of score values ​​by themselves.

[0028] Condition 5: The score proportion range of the five main sub-items, namely, the preliminary evaluation score proportion range of the five sub-items of "safety and durability, health and comfort, convenience of life, resource conservation, and livable environment" should be between 30% and 100%.

[0029] Condition six: The score limit for the "Improvement and Innovation" sub-item, that is, the total score of this sub-item cannot exceed 100 points, and the proportion of the pre-evaluation score should be between 0% and 100%.

[0030] Check the scoring data cell by cell. Specifically, use the six conditions in the preset rules and VBA code to check each cell in the scoring table one by one, completing the control item check and the scoring item check respectively.

[0031] Control item verification: Check whether 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.

[0032] Scoring item checks include: Score consistency check: Check whether the value assigned to the scoring item cell is consistent with the standard requirements; if the score does not match, prompt the user to correct it to the standard score.

[0033] Ladder scoring uniqueness check: Identify the cell group that belongs to the ladder scoring, and only allow one of the cells to have a score value; if multiple cells are scored, a pop-up window will prompt "Ladder scoring for clause number XX only allows one option to be scored, please correct it", and the user needs to select a score value and clear the values ​​of other cells.

[0034] Building type score uniqueness check: Identify the clauses that distinguish building types and check whether only one type is scored. If multiple types are scored, a pop-up window will pop up saying "The building type score for clause number XX only allows one type to be scored, please correct it." The user needs to select one building type score and clear other scores.

[0035] Further, the score proportion is checked step by step, specifically: after completing the score cell check, the table data in the visual control interface is further verified, including: Control item score ratio check: Check whether the pre-evaluation score ratio of the control item is equal to 100%; if not, prompt the user "The pre-evaluation score ratio of the control item must be 100%, please adjust the relevant evaluation."

[0036] Check the score proportion of the five main sub-items: Check whether the preliminary evaluation score proportion of the five sub-items "safety and durability, health and comfort, convenience of life, resource conservation, and livable environment" is within the range of 30% to 100%; if it is out of the range, a pop-up window will pop up to prompt "The preliminary evaluation score proportion of sub-item 'XX' exceeds the allowable range (30%-100%), please adjust the scoring data."

[0037] Check the score limit of the "Improvement and Innovation" sub-item: Check whether the total score of the sub-item exceeds 100 points, and whether the proportion of the pre-evaluation score is in the range of 0% to 100%; if not, it will prompt "The score limit of the 'Improvement and Innovation' sub-item is 0-100 points, and the proportion range is 0%-100%, please adjust the relevant score."

[0038] The feedback and correction process includes prompting error messages and user correction data one by one, specifically: Error messages are displayed one by one: Error messages are displayed one by one through pop-up windows, including the following: incorrect scoring cells (such as article numbers or sub-item names); error types (such as score exceeding the limit, multi-cell scoring, etc.); modification suggestions (such as "only keep one scoring item", "adjust the score to the standard score XX").

[0039] User modifies data: After the user modifies the scoring data according to the prompts, he can click the "Check the score of this item" macro button again to start the next round of checking.

[0040] Dynamic verification and validation, specifically: Dynamically update the data status of the rating table and visual control interface, including the total score, sub-item proportion and star result, to reflect the user's adjustments in real time; when all rules are met, a MessageBox pop-up window will prompt "All rating data has passed verification" and allow the user to proceed to the next step.

[0041] Furthermore, step S3 includes: triggering technical solution generation, screening qualified clause contents, generating technical solutions by category and profession, single generation and user control, and user confirmation.

[0042] Trigger the generation of technical solutions, specifically: After the user clicks the "Generate Project Green Building Technical Plan" macro button, the following operation process is initiated: read the current scoring results from the scoring table; filter the qualified clause content from the database worksheet; integrate the control item and scoring item clause information in the two database worksheets to generate a technical plan; arrange the generated technical plans in order of clause numbers and store them in a separate worksheet.

[0043] Screening the qualified clauses includes screening basis and screening logic, which are as follows: The screening criteria include two requirements: scoring results and font color status. Scoring results: the articles assigned non-zero scores in the scoring table are valid articles; font color status: the articles with black font color in the database worksheet are valid articles, and the articles with white font color are not generated.

[0044] Filtering logic: In the "Technical clauses and details of control items" worksheet, filter out all control item clauses; in the "Technical clauses and details of scoring items" worksheet, filter out all scoring item clauses; arrange them in the order of control items first and then scoring items, and sort them from small to large according to the clause number to ensure the logic and orderliness of the technical solution.

[0045] Generate technical solutions by category and profession, specifically: generate technical solutions for the screened articles according to their professional affiliation, including three steps: professional division, worksheet generation and article number sorting.

[0046] Professional division: Allocate the content of the article to the corresponding profession based on the additional professional attribution information of the article; optionally, if an article involves multiple professions (such as "construction, electrical"), copy its content to the technical solution of each relevant profession.

[0047] Generate worksheets: Generate a separate worksheet for each discipline, such as "Architectural Technical Plan" and "Electrical Technical Plan"; the worksheet content includes the article number, article content and technical details (including explanatory notes and certification drawing requirements) The article number sorting includes: all articles are first classified into control items and scoring items, and the corresponding headers "Technical Articles for Control Items" and "Technical Articles for Scoring Items" are automatically generated in each professional independent worksheet; each type of article number is arranged from small to large to ensure that the content of the plan is clear and orderly.

[0048] Single generation and user control, specifically: each time the macro button is clicked, a complete technical solution generation operation is performed; if the user modifies the 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 are performed, and the results are only stored after the generation is completed.

[0049] User confirmation, specifically: after all technical solutions are generated, a pop-up window will be used to notify the user that all current professional technical solutions have been generated; after the user confirms, the user can choose to enter S4: Export green building technical solutions.

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

[0051] Selective export interface, specifically: create a pop-up window interface through VBA, allowing users to flexibly select the professional technical solutions to be exported. The pop-up window interface includes the following: The list of majors uses numbers to associate majors. Users can enter numbers to select majors. When selecting multiple majors, separators (such as commas) can be used to distinguish them. The export confirmation button is used to perform the export operation.

[0052] The export file is generated as follows: according to the user's selection, the corresponding professional technical solution worksheet content is automatically converted into a Word document format, and the following operations are performed: Standardize file formats, set standardized titles, headers, footers, and paragraph styles for each document, such as landscape pages; The file naming rules are as follows: the file should be named in the format of "Professional 1+Professional 2+......Technical Solution.docx"; File storage path: store the generated Word document to the location where the EXCEL file is located to facilitate user search.

[0053] Automatic storage and user verification, specifically: after exporting the file, it will be automatically stored and the Word document will pop up directly, and the user can check whether the document content is complete.

[0054] The export completion prompt is as follows: after all selected professional technical solutions are successfully exported, a MessageBox pops up with an "Export Completed" prompt, and the entire method process ends.

[0055] Compared with the existing technology, the present invention uses the automation function of EXCEL-VBA to realize the rapid screening and scoring of green building evaluation clauses, automatically generate technical solutions for different professions, avoid omissions and duplication of labor in manual operation, and greatly improve efficiency and accuracy. Compared with traditional manual operation, it significantly improves efficiency and accuracy.

[0056] The present invention relies on EXCEL, a universal office tool, so users can quickly complete the generation of technical solutions without having to learn complex software operations, thereby reducing the technical implementation threshold and learning costs. Compared with complex software systems, it is more lightweight and easy to use.

[0057] According to the different star-level requirements of the Green Building Evaluation Standard GB / T50378 and the characteristics of the construction project, the present invention can tailor technical clauses and technical details for 13 professions, including "architecture, structure, water supply and drainage, electricity, green building, cost, air conditioning, BIM, intelligence, landscape, decoration, signage, and floodlighting", to ensure that the solution is highly consistent with the design requirements. Compared with a single technical solution, it is more flexible to adapt to the needs of multiple professions.

[0058] Through the flexible VBA programming structure, the present invention can quickly adjust the logic and rules of the evaluation clauses, 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 fixed process tools, it is more adaptable to the update and expansion of standards. BRIEF DESCRIPTION OF THE DRAWINGS

[0059] Figure 1 It is a flow chart of the method for automatically generating green building technical solutions based on EXCEL-VBA of the present invention.

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

[0061] Figure 3 The VBA code for visually controlling the macro button in the embodiment.

[0062] Figure 4 The VBA code for visually controlling the macro button in the embodiment.

[0063] Figure 5The VBA code for visually controlling the macro button in the embodiment.

[0064] Figure 6 This is the pop-up prompt for the macro button "Export Project Green Building Technical Plan" in the embodiment.

[0065] Figure 7 This is an example of a technical solution automatically generated in the embodiment. DETAILED DESCRIPTION

[0066] The following further describes the method for automatically generating a green building technical solution based on EXCEL-VBA in conjunction with the accompanying drawings and specific embodiments.

[0067] At present, the formulation of green building technical plans relies on green building consultants to manually operate scoring sheets and compile technical plans, which has problems such as heavy workload, easy errors, and low efficiency. Especially when dealing with large and complex projects such as residential buildings, manual operations can easily lead to a mismatch between technical provisions and project requirements, thus affecting the accuracy of the green building star rating and the implementation of the plan.

[0068] The present invention provides a method for automatically generating green building technical solutions based on EXCEL-VBA based on the automation function of EXCEL-VBA to solve the above problems. The present invention can quickly and accurately generate green building technical solutions that meet project requirements by embedding dynamic scoring calculation, clause database management and automatic technical solution generation functions in EXCEL, while supporting output by professional classification and customized export, which significantly improves work efficiency and the reliability of technical solutions.

[0069] See also Figure 1 The present invention discloses a method for automatically generating a green building technical solution based on EXCEL-VBA, which is implemented by VBA code using EXCEL-VBA as an operation carrier and includes the following steps: S1. Obtain green building rating data: After completing the rating table of the construction project, traverse the green building rating data of the construction project to obtain the green building star rating and six sub-item ratings; S2. Check the score and make corrections: traverse the data cells of the score sheet, compare the input data with the preset rules and standards, and for data that does not meet the requirements, a pop-up window will indicate the error and modification suggestions until the pop-up window indicates that the score is correct; S3. Generate green building technical solutions: Based on the score sheet data and the technical provisions and details preset in other worksheets, generate technical solutions specifically for construction projects in 13 disciplines and store them in independent worksheets; S4. Export green building technical solutions: According to the pop-up prompts, flexibly select and combine the professional technical solutions that need to be exported, and automatically store them in the local user's computer folder.

[0070] The following describes in detail the specific implementation of the method for automatically generating green building technical solutions based on EXCEL-VBA of the present invention in conjunction with an example of a residential building project.

[0071] This example takes a residential building project as an example. The goal is to achieve the two-star green building standard, that is, the total score is greater than 70 points. Using EXCEL-VBA as the core operation carrier, all visual interfaces, database worksheets and VBA codes of three macro buttons are preset. The visual control interface is as follows: Figure 2 As shown, the VBA codes for the three macro buttons of the visual control are as follows Figure 3 , Figure 4 and Figure 5 As shown, they 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 and scoring of the scoring table, and click the macro buttons in sequence to complete the operation according to the prompts.

[0072] Step S1: Traverse the green building rating data of the construction project to obtain the green building star rating and six sub-item ratings.

[0073] In step S1, the green building consultant first conducts self-assessment and scoring of the residential building project in accordance with the Green Building Evaluation Standard GB / T50378-2019 (2024 revised edition), using EXCEL-VBA as the operation carrier to achieve data acquisition and management through the following preset operations.

[0074] A preset visual control interface for dynamic presentation and interactive operation of data. The interface includes: a table showing control items and relevant scoring information of six sub-items (safety and durability, health and comfort, convenience of life, resource conservation, livable environment, improvement and innovation); a hexagonal radar chart to show the comparison between the proportion of each sub-item score and the minimum score requirement line; a green building star rating diagram to display the green building star rating results corresponding to the project; and three interactive macro buttons to check the score, generate technical solutions and export technical solutions.

[0075] Two database worksheets are preset: "Technical Articles and Details of Control Items" and "Technical Articles and Details of Scoring Items". The article information in the database is designed and classified according to the "Green Building Evaluation Standard" GB / T50378-2019 (2024 Revised Edition). Each article is attached with a professional attribution cell to mark the profession involved in the article, such as "Architecture", "Electrical", etc. When multiple professions are involved, they are separated by commas.

[0076] After the green building consultant completes the scoring in the scoring table according to the project characteristics, click the "Save" button to automatically complete the following operations through the VBA code: Dynamically calculate the score of each item and the total score; Update the visual interface, including presenting the sub-item scores and total scores in a table, updating the radar chart's six sub-item comparison results of "safety and durability, health and comfort, living convenience, resource conservation, livable environment, improvement and innovation", and displaying the project's green building star rating in the star rating diagram. For example, when the total score is 75 points, it will be displayed as a two-star green building logo; According to the scoring status in the scoring table, the font color of the article cells in the database worksheet is automatically updated: the font of articles with non-zero scores is set to "black", and the font of articles with zero scores is set to "white" for subsequent screening and classification.

[0077] Step S2: Check the score of this item.

[0078] After the green building consultant clicks the "Check the score of this project" macro button, the following operations are automatically performed through the VBA code to verify whether the scoring data complies with the rules.

[0079] According to the Green Building Evaluation Standard GB / T50378-2019 (2024 revised version), the scoring data is checked item by item. The specific rules include: The score of control items must account for 100%; the score of scoring items must strictly comply with the provisions of the articles; the step-by-step scoring articles only allow one cell to be scored; the scoring articles that distinguish between building types only allow one type to be scored; the score of the five sub-items such as safety and durability, health and comfort ranges from 30% to 100%; the total score of improvement and innovation sub-items shall not exceed 100 points, and the score ranges from 0% to 100%.

[0080] Check each scoring cell and item data one by one, and prompt the green building consultant to correct the error through a pop-up window. For example, if the score of a scoring item exceeds the limit, the prompt "The score of clause number 4.2.1 does not meet the standard, please correct it to the standard value of 10 points" will be prompted. If multiple options of a ladder scoring clause are scored at the same time, the prompt "The ladder scoring of clause number 5.2.2 only allows one option to be scored, please correct it" will be prompted.

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

[0082] Step S3: Generate a green building technical plan for the project. Based on the score sheet data and the technical provisions and details preset in other worksheets, generate a technical plan specific to the project in 13 disciplines and store it in an independent worksheet.

[0083] After the green building consultant clicks the "Generate Project Green Building Technical Plan" macro button, the VBA code generates a technical plan based on the current scoring data, including: Filter the qualified clauses from the database worksheet, based on the following criteria: clauses with non-zero scores in the scoring table and clauses with black font color in the database worksheet.

[0084] Classify by control items and scoring items, and sort by clause number from small to large. For example: Technical solution for control items: includes clauses such as "4.1.2 The building structure shall meet the requirements of bearing capacity and building use function. The building's exterior walls, roofs, doors and windows, curtain walls, external insulation and other enclosing structures shall meet the requirements of safety, durability and protection."

[0085] Technical solution for scoring items: includes clauses such as “5.2.2 The selected decorative and finishing materials shall meet the requirements for harmful substance limits in the current national green product evaluation standards”.

[0086] According to the professional attribution information attached to the clause, the content of the clause is assigned to the corresponding profession. For example, "4.1.2 The building structure shall meet the requirements of bearing capacity and building use function" is assigned to "Structural Technical Plan". Optionally, if a clause involves multiple professions, it is copied to multiple professional technical plans at the same time. For example, "5.2.2 The selected decorative and finishing materials shall meet the requirements for the limit of harmful substances in the current national green product evaluation standards" is assigned to "Building Technical Plan" and "Interior Decoration Technical Plan".

[0087] Each professional technical solution generates an independent EXCEL worksheet, the table header includes "Technical provisions for control items" and "Technical provisions for scoring items", and the content of the provisions is sorted by classification number. Green building consultants do not need to do any additional operations, and the generation work is automatically completed.

[0088] Step S4: Export the project green building technical plan. According to the pop-up window prompt, flexibly select and combine the professional technical plans to be exported, and automatically store them in the local user computer folder.

[0089] After the green building consultant clicks the "Export Project Green Building Technical Plan" macro button, a pop-up window displays the following list of professional technical solutions for selection, such as Figure 6 As shown, it includes: 1. Architectural technical solution; 2. Structural technical solution; 3. Air-conditioning technical solution; 4. Water supply and drainage technical solution; 5. Electrical technical solution; 6. Intelligent technical solution.

[0090] After the green building consultant enters the selection number (such as "1,2,3"), all the contents of the corresponding worksheets "Architectural Technical Plan", "Structural Technical Plan" and "Air Conditioning Technical Plan" are automatically converted to Word document format. For example, when "1,2,3" is entered, the generated file is named "Architectural + Structure + Air Conditioning Technical Plan.docx" and stored in the path where the EXCEL file is located. At the same time, the Word document is automatically opened for the green building consultant to check the content. The Word document is as follows: Figure 7 shown.

[0091] After the export is completed, a pop-up window will pop up saying "Export Completed". Green building consultants can directly view the generated documents and complete the preparation and delivery of green building technical solutions.

[0092] In summary, the present invention uses the automation function of EXCEL-VBA to achieve rapid screening and scoring of green building evaluation clauses, automatically generate technical solutions for different professions, avoid omissions and duplication of labor in manual operations, and greatly improve efficiency and accuracy. Compared with traditional manual operations, the efficiency and accuracy are significantly improved.

[0093] The present invention relies on EXCEL, a universal office tool, so users can quickly complete the generation of technical solutions without having to learn complex software operations, thereby reducing the technical implementation threshold and learning costs. Compared with complex software systems, it is more lightweight and easy to use.

[0094] According to the different star-level requirements of the Green Building Evaluation Standard GB / T50378 and the characteristics of the construction project, the present invention can tailor technical clauses and technical details for 13 professions, including "architecture, structure, water supply and drainage, electricity, green building, cost, air conditioning, BIM, intelligence, landscape, decoration, signage, and floodlighting", to ensure that the solution is highly consistent with the design requirements. Compared with a single technical solution, it is more flexible to adapt to the needs of multiple professions.

[0095] Through the flexible VBA programming structure, the present invention can quickly adjust the logic and rules of the evaluation clauses, 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 fixed process tools, it is more adaptable to the update and expansion of standards.

[0096] The above description is a detailed description of the preferred feasible embodiments of the present invention, but the embodiments are not intended to limit the scope of the patent application of the present invention. All equivalent changes or modified changes completed under the technical spirit disclosed by the present invention should fall within the patent scope covered by the present invention.

Claims

1. A method for automatically generating green building technology solutions based on EXCEL-VBA, characterized in that: Using EXCEL-VBA as the operation carrier and VBA code to implement it, the following steps are included: S1. Obtain green building rating data: After completing the rating table of the construction project, traverse the green building rating data of the construction project to obtain the green building star rating and six sub-item ratings; S2. Check the score and make corrections: traverse the data cells of the score sheet, compare the input data with the preset rules and standards, and for data that does not meet the requirements, a pop-up window will indicate the error and modification suggestions until the pop-up window indicates that the score is correct; S3. Generate green building technical solutions: Generate technical solutions for construction projects based on the score sheet data and preset technical clauses and details, and store them in independent worksheets; S4. Export green building technical solutions: According to the pop-up prompts, flexibly select and combine the professional technical solutions that need to be exported, and automatically store them in the local user's computer folder.

2. The method for automatically generating green building technical solutions based on EXCEL-VBA according to claim 1 is characterized in that: Step S1 includes: setting a visual control interface, setting a database worksheet, automatically calculating scores and visually presenting them, and dynamically updating a data worksheet; Set the visual control interface, specifically: Based on the existing green building score sheet, a new project design phase self-assessment score sheet, a project design phase self-assessment score chart and three interactive macro buttons are added; The self-assessment score table in the project design phase is used to display the numerical information of "pre-evaluation score, minimum pre-evaluation score, pre-evaluation sub-item score, proportion of pre-evaluation sub-item score, minimum requirement for sub-item score, and total score" for the control items and the six sub-items of "safety and durability, health and comfort, convenience of life, resource conservation, livable environment, improvement and innovation"; The self-assessment score chart for the project design phase includes: 1 green building star rating diagram, which intuitively displays the corresponding star rating of the project; 1 hexagonal radar chart, which shows the proportion of the pre-evaluation score of each sub-item and the comparison with the minimum score requirement line; The three interactive macro buttons include: a "Check the score of this project" macro button, associated with step S2, to check the score and make corrections; a "Generate project green building technical plan" macro button, associated with step S3, to generate a green building technical plan; and an "Export project green building technical plan" macro button, associated with step S4, to export the green building technical plan.

3. The method for automatically generating green building technical solutions based on EXCEL-VBA according to claim 2 is characterized in that: Set up the database worksheet, specifically: Two independent worksheets are added to store the basic data required for the green building technical plan. The content is designed according to the "Green Building Evaluation Standard" GB / T50378 and is specially processed; Two independent worksheets include the "Technical provisions and details of control items" worksheet and the "Technical provisions and details of scoring items" worksheet; The contents of the "Technical Articles and Details of Control Items" worksheet include: storing all control item articles in the "Green Building Evaluation Standard" GB / T50378 in the order of article numbers, including explanations of the articles and requirements for supporting drawings; attaching a cell to each article to mark the professional affiliation involved in the article; for articles involving multiple professions, using separators to mark all relevant professions; according to the professional markings in the attached cells, assigning the articles to the corresponding professional categories as the conditions for generating technical solutions by classification; The contents of the "Technical Articles and Details of Scoring Items" worksheet include: storing scoring item article information in order of article numbers, including specific technical requirements, evaluation rules and documentary requirements for scoring items; attaching a cell to each article to mark the professional affiliation involved in the article; for articles involving multiple professions, using separators to mark all relevant professions; according to the professional markings in the attached cells, assigning the articles to the corresponding professional categories as the conditions for generating technical solutions by classification; Special treatment, specifically: for selectively scored articles, regardless of whether the article is scored or not, its articles and details will be fully included in the database worksheet to ensure the completeness of the screening conditions.

4. The method for automatically generating green building technical solutions based on EXCEL-VBA according to claim 2 is characterized in that: Automatically calculate the score and visualize it, 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 by traversing the preset SUM function. The six sub-item scores are filled in 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 in 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 between the sub-item score and the minimum requirement in the radar chart; the green building grade is determined according to the star threshold and total score of the "Green Building Evaluation Standard" GB / T50378, and is presented in graphical form in the green building star diagram of the "Project Design Phase Self-evaluation Score Chart", helping users to quickly identify the project rating; "Green Building Evaluation Standard" GB / T50378 star threshold: one star, total score ≥ 60; Two stars, total score ≥70; Three stars, total score ≥85.

5. The method for automatically generating green building technical solutions based on EXCEL-VBA according to claim 2 is characterized in that: Dynamically update the data worksheet, specifically: When the user completes the scoring and clicks the "Save" button, the "Technical provisions and details of control items" and "Technical provisions and details of scoring items" worksheets are automatically updated through VBA code. The specific operations are as follows: According to the article number, a one-to-one mapping relationship is established between the scoring cells in the scoring table and the article cells in the two database worksheets through VBA code. The scoring cell status affects the font color of the database worksheet. When a scoring 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 a scoring cell is assigned a zero score, the font color of the corresponding article cell in the database worksheet is automatically changed to "white".

6. The method for automatically generating green building technical solutions based on EXCEL-VBA according to claim 1 is characterized in that: Step S2 includes: presetting rules, checking scoring data cell by cell, checking score proportion step by step, 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 scores are set: Condition 1: The control items must meet the requirements, that is, the control item pre-evaluation score must account for 100%; Condition 2: The score of the scoring item must be strictly consistent with the score specified in the article; Condition 3: Check the uniqueness of the ladder scoring, that is, identify the relevant cell groups of the ladder scoring clauses, and only allow one of the cells to have a score value; if multiple cells are scored, the user is prompted to make corrections, and the user needs to correct the data by himself; Condition 4: Check the uniqueness of building type scores, that is, for the clauses that distinguish building types, check whether only one type is scored; if multiple types are scored, prompt the user to make corrections, and the user needs to select one type of score and clear the other types of score values ​​by themselves; Condition 5: The proportion of the scores of the five main sub-items, namely, the proportion of the preliminary evaluation scores of the five sub-items of "safety and durability, health and comfort, convenience of life, resource conservation, and livable environment" should be between 30% and 100%; Condition 6: "Improvement and Innovation" sub-item score limit, that is, the total score of this sub-item cannot exceed 100 points, and the pre-evaluation score ratio should be between 0% and 100%; Check the scoring data cell by cell, specifically: check each cell in the scoring table one by one through the six conditions in the preset rules through VBA code, and complete the control item check and scoring item check respectively; Control item verification: Check whether the control item score meets condition 1; if not, prompt the user to adjust the control item score data; Scoring item checks include: Score consistency check: Check whether the value assigned to the scoring item cell is consistent with the standard; if the score does not match, prompt the user to correct it to the standard score; Tiered scoring uniqueness check: Identify the cell group that belongs to the tiered scoring, and only allow one of the cells to have a score value; if multiple cells are scored, a pop-up window will prompt "Tiered scoring for clause number XX only allows one option to be scored, please correct it", and the user needs to select a score value and clear the values ​​of other cells; Building type score uniqueness check: Identify the clauses that distinguish building types and check whether only one type is scored. If multiple types are scored, a pop-up window will pop up saying "Only one type of building type score is allowed for clause number XX, please correct it". The user needs to select one building type score and clear other scores.

7. The method for automatically generating green building technical solutions based on EXCEL-VBA according to claim 6 is characterized in that: Check the score proportion step by step, specifically: After completing the scoring cell check, further verify the table data in the visual control interface, including: Control item score ratio check: Check whether the pre-evaluation score ratio of the control item is equal to 100%; if not, prompt the user "The pre-evaluation score ratio of the control item must be 100%, please adjust the relevant evaluation"; Check the score proportion of the five main sub-items: Check whether the pre-evaluation score proportion of the five sub-items "safety and durability, health and comfort, convenience of life, resource conservation, and livable environment" is within the range of 30% to 100%; if it is out of the range, a pop-up window will pop up to prompt "The pre-evaluation score proportion of sub-item 'XX' exceeds the allowable range, please adjust the scoring data"; "Improvement and Innovation" sub-item score limit check: check whether the total score of the sub-item exceeds 100 points, and whether the proportion of the pre-evaluation score is within the range of 0% to 100%; if not, it will prompt "The score limit of the 'Improvement and Innovation' sub-item is 0-100 points, and the proportion range is 0%-100%, please adjust the relevant score"; The feedback and correction process includes prompting error messages and user correction data one by one, specifically: Prompt error messages one by one: Feedback error messages one by one through pop-up windows, including incorrect scoring cells, error types, and modification suggestions; User correction data: After the user corrects the scoring data according to the prompts, he can click the "Check the score of this project" macro button again to start the next round of checking; Dynamic verification and validation, specifically: Dynamically update the data status of the rating table and visual control interface, including the total score, sub-item proportion and star result, to reflect the user's adjustment in real time; when all rules are met, a MessageBox pop-up window will prompt "All rating data has passed verification" and allow the user to proceed to the next step.

8. The method for automatically generating green building technical solutions based on EXCEL-VBA according to claim 1 is characterized in that: Step S3 includes: triggering the generation of technical solutions, screening the content of clauses that meet the conditions, generating technical solutions by category and profession, single generation and user control, and user confirmation; Trigger the generation of technical solutions, specifically: After the user clicks the "Generate Project Green Building Technical Plan" macro button, the following operation process is initiated: read the current scoring results from the scoring table; filter the qualified clauses from the database worksheet; integrate the control item and scoring item clause information in the two database worksheets to generate a technical plan; arrange the generated technical plans in the order of clause numbers and store them in a separate worksheet; Screening the qualified clauses includes screening basis and screening logic, which are as follows: The screening criteria include two requirements: scoring result and font color status. Scoring result: the clauses with non-zero scores in the scoring table are valid clauses; font color status: the clauses with black font color in the database worksheet are valid clauses, and the clauses with white font color are not generated. Filtering logic: In the "Technical clauses and details of control items" worksheet, filter out all control item clauses; in the "Technical clauses and details of scoring items" worksheet, filter out all scoring item clauses; arrange them in the order of control items first and then scoring items, and sort them from small to large according to the clause number to ensure the logic and orderliness of the technical solution.

9. The method for automatically generating green building technical solutions based on EXCEL-VBA according to claim 8 is characterized in that: Generate technical solutions by category and specialty, specifically: Generate technical solutions for the selected articles according to their professional affiliation, including professional division, generation of worksheets and article number sorting; Professional division: According to the additional professional attribution information of the article, the content of the article is assigned to the corresponding profession; if the article involves multiple professions, its content is copied to the technical plan of each relevant profession; Generate worksheet: Generate an independent worksheet for each discipline, which includes the article number, article content and technical details; Article number sorting: All articles are first classified by control items and scoring items, and the corresponding table headers "Technical Articles for Control Items" and "Technical Articles for Scoring Items" are automatically generated in each professional independent worksheet; the article numbers of each category are arranged from small to large to ensure that the content of the plan is clear and orderly; Single generation and user control, specifically: Each time the macro button is clicked, a complete technical solution generation operation is performed; if the user modifies the 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 are performed, and the results are only stored after the generation is completed; User confirmation, specifically: after all technical solutions are generated, a pop-up window is used to notify the user that all current professional technical solutions have been generated; after the user confirms, the user can choose to enter step S4: export the green building technical solution.

10. The method for automatically generating green building technical solutions based on EXCEL-VBA 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; Selective export interface, specifically: A pop-up window interface is created through VBA, allowing users to flexibly select the professional technical solutions to be exported; the pop-up window interface includes: a professional list, which uses numbers to associate majors. Users can enter numbers to select majors. When selecting multiple majors, separators can be used to distinguish them; an export confirmation button is used to execute the export operation; The export file is generated as follows: According to the user's selection, the corresponding professional technical solution worksheet content is automatically converted into Word document format, and the following operations are performed: file format normalization, each document sets standardized title, header, footer and paragraph style; file naming rules, naming files in the format of "Professional 1+Professional 2+......Technical Solution.docx"; file storage path, the generated Word document is stored in the location of the EXCEL file, which is convenient for users to find; Automatic storage and user verification: After exporting the file, it will be automatically stored and the Word document will pop up directly. The user can check whether the document content is complete. The export completion prompt is as follows: after all selected professional technical solutions are successfully exported, a MessageBox pops up with the prompt "Export Completed", and the entire method process ends.

Images