VBA-based aircraft manufacturing configuration and design configuration difference rapid comparison method

Abstract

The invention belongs to the technical field of digitization, and relates to a VBA-based aircraft manufacturing configuration and design configuration difference rapid comparison method. The method comprises the following steps: 1, setting a user interface: designing a friendly user interface; 2, data input: acquiring manufacturing configuration and design configuration data in the file according to a set path; 3, data cleaning: performing format cleaning on manufacturing configuration and design configuration data through a VBA script; and 4, data matching: based on a preset rule, using a VBA script to match a corresponding relationship and uniqueness of manufacturing configuration and design configuration data. 5, difference identification: recording a difference type by identifying a difference point in a matching result; and 6, report generation: automatically listing all configuration information according to a data storage table template set in the Excel table, and automatically associating engineering change instruction content based on the identified difference type to generate difference information. Therefore, the comparison efficiency is improved, and the learning cost and the operation complexity are reduced.

Description

Technical Field

[0001] This invention belongs to the field of digital technology and relates to a method for rapid comparison of differences between aircraft manufacturing configuration and design configuration based on VBA. Background Technology

[0002] In aircraft manufacturing, comparing the manufactured configuration with the design configuration is a crucial step in ensuring product consistency, traceability, and quality control. It's worth noting that comparing discrepancies in a single configuration involves approximately 120,000 data items, a massive volume that presents significant challenges in practice. These data items typically originate from different design modules and subsystems, involving multi-level structural information. Each data item needs to be meticulously verified to ensure complete consistency between the manufactured and design configurations. While traditional database-based Product Data Management (PDM) systems can achieve a certain degree of configuration comparison, their efficiency is severely inadequate when handling such a large volume of data. Due to limitations in database query speed and complex data relationships, traditional systems often require lengthy completion times for comparison tasks. Furthermore, PDM systems typically require specialized technical personnel for operation and maintenance, further increasing labor and time costs. Currently, some automated tools exist for configuration comparison, but these tools usually rely on specialized software platforms, resulting in high costs and complex operation. Summary of the Invention

[0003] Purpose of the invention: Therefore, there is an urgent need for an automated comparison method based on general office software to improve comparison efficiency and reduce learning costs and operational complexity.

[0004] The technical solution of the present invention is as follows: This invention provides a VBA-based method for rapid comparison of differences between aircraft manufacturing configurations and design configurations. The method automates the comparison of manufacturing and design configuration data using VBA scripts, specifically including the following steps: 1. Set up the user interface: Design a user-friendly interface to facilitate users in entering paths, selecting data sources, and viewing comparison results; 2. Data Input: Retrieve manufacturing configuration and design configuration data from the files according to the set path; 3. Data cleaning: Use VBA scripts to clean the format of manufacturing configuration and design configuration data; 4. Data matching: Based on preset rules, VBA scripts are used to match the correspondence and uniqueness between manufacturing configuration and design configuration data.

[0005] 5. Difference Identification: By identifying differences in the matching results, record the difference type (no difference, new addition, version upgrade, number change); 6. Report Generation: Automatically lists all configuration information (including part number, drawing number, version, etc.) according to the data storage table template set in the Excel spreadsheet, and automatically generates difference information based on the identified difference types and the content of engineering change instructions.

[0006] Specifically, the configuration information in the path file in step 2 includes component information and airborne equipment hardware and software and ATP information (part number, parent part number, equipment model, equipment drawing number version, document number version, installation drawing number version, process document number, serial number, software part number version, electronic hardware part number version, ATP number version, etc.).

[0007] Specifically, the data cleaning in step 3 includes filtering out the information in the EBOM file (configuration specification component list) that is classified as six types of documents, standard parts, and electronic components by filtering out the information in the row containing these documents by leix_arr.

[0008] Specifically, in step 4, data matching includes storing the cleaned configuration specification component list data in a new array and storing the cleaned old difference data comparison table data in the old array.

[0009] Specifically, in step 4, data matching based on preset rules includes associating component numbers, parent numbers, and upper-level parent numbers as complete BOM path information. The data correspondence and uniqueness of the configuration description and the old difference base comparison data table are matched through the complete BOM path information.

[0010] Specifically, in step 6, when the difference type is no difference, version upgrade, or number change, the physical configuration information in the old difference data table is automatically assigned to the corresponding column in the new array.

[0011] Specifically, in step 6, the automatic association of engineering change instruction content with difference type to generate difference information includes recording the line number when the difference type is upgrade, opening the engineering change instruction content list according to the engineering change instruction report path, comparing the design version of the new difference data with the old design version according to the line number recorded in the upgrade array, and filling the difference description reference with the number of difference versions according to the engineering change instruction number matched with the engineering change instruction content.

[0012] Specifically, in step 6, the automatic association of engineering change instruction content with difference type to generate difference information includes recording the line number when the difference type is number change, opening the engineering change instruction content list according to the engineering change instruction report path, converting the file number and version in the design configuration into the engineering change instruction number according to the line number recorded in the number change array, and filling in the difference description reference by matching the engineering change instruction content.

[0013] Specifically, step 6 automatically lists all configuration information, including components and airborne equipment (software, electronic hardware, ATP).

[0014] Technical effects: This invention provides a VBA-based method for rapid comparison of differences between aircraft manufacturing configurations and design configurations. It automates the comparison of manufacturing and design configuration data, significantly reducing manual operation time. Through data cleaning and pre-defined rules, it improves the accuracy of comparison results and supports comparison of various data formats and versions. Applicable to configuration management and change control in the aircraft manufacturing field, it helps improve product quality and production efficiency. The complete path information function in this invention generates a globally unique "absolute path" identifier for each component within its manufacturing configuration tree, ensuring the uniqueness and accuracy of the comparison and fundamentally avoiding mismatches and missed matches, making the difference identification results extremely reliable. For complex product structure trees with deep layers and multiple branches (such as aircraft and automotive wiring harnesses), traditional layer-by-layer recursive traversal methods are computationally complex and prone to errors. This invention efficiently calculates the complete path of all nodes through a one-time ordered traversal and data table operations. The algorithm logic is clear, insensitive to the complexity and depth of the product structure, and has strong adaptability and robustness. Attached Figure Description

[0015] Figure 1 This is a schematic diagram of the VBA-based rapid comparison method for aircraft manufacturing configuration and design configuration as described in this invention. Figure 2 This is a schematic diagram of data cleaning and matching in an embodiment of the VBA-based rapid comparison method for aircraft manufacturing configuration and design configuration described in this invention. Figure 3 This is a schematic diagram illustrating the automatic matching of physical configuration information based on the difference type in an embodiment of the VBA-based rapid comparison method for aircraft manufacturing configuration and design configuration described in this invention. Figure 4 This is a schematic diagram of a method for comparing aircraft manufacturing configurations and design configurations based on traditional Excel functions. Detailed Implementation

[0016] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0017] The features and illustrative embodiments of various aspects of the present invention will now be described in detail. In the following detailed description, numerous specific details are set forth in order to provide a thorough understanding of the invention. However, it will be apparent to those skilled in the art that the invention may be practiced without requiring some of these specific details. The following description of embodiments is merely intended to provide a better understanding of the invention by illustrating examples of the invention. The invention is by no means limited to any specific setup and method set forth below, but covers any improvements, substitutions, and modifications to the structures, methods, and devices without departing from the spirit of the invention. In the following description, well-known structures and techniques are not shown to avoid unnecessarily obscuring the invention.

[0018] In the description of this invention, it should be noted that the directions or positional relationships indicated by terms such as "center," "upper," "lower," "left," "right," "vertical," "horizontal," "inner," and "outer" are based on the stated directions or positional relationships and are only for the convenience of describing and simplifying the invention, and should not be construed as limiting the invention. Furthermore, the use of ordinal numbers (e.g., "first and second," etc.) is for distinguishing objects and is not limited to this order, and should not be construed as indicating or implying relative importance.

[0019] In the description of this invention, it should be noted that, unless otherwise explicitly specified and limited, the terms "installation," "connection," and "linking" should be interpreted broadly, encompassing both direct connection and indirect connection via an intermediate medium. Those skilled in the art can understand the specific meaning of these terms in this invention based on the specific circumstances.

[0020] It should be noted that, unless otherwise specified, the embodiments of the present invention and the features thereof can be combined with each other, and the various embodiments can be referenced and cited in each other. The present invention will now be described in detail with reference to and in conjunction with the embodiments.

[0021] (1) Set up the user interface: Design a user-friendly interface to facilitate users to input paths, select data sources and view comparison results.

[0022] Sheet1 in the Excel spreadsheet serves as the calculation interface page, containing operation buttons and file path selection buttons. Sheet2 is a table showing the differences between the manufacturing and design configurations of components, storing the comparison results. Sheet3 is a table showing the differences between the manufacturing and design configurations of airborne equipment hardware and software and ATP, storing the comparison results. The file path selection buttons in Sheet1 allow you to select the paths for the EBOM list, the old difference comparison base data table, and the engineering change instruction report.

[0023] (2) Data cleaning: VBA scripts are used to clean the format of manufacturing configuration and design configuration data. First, verify that the data is empty according to the file input path in Sheet1. Open the EBOM file (Configuration Specification Component List) according to the EBOM path. Filter the configuration specification component list by leix_arr, removing rows containing documents of type six, standard parts, and electronic components. Copy the filtered results to Sheet2. Copy the airborne hardware and software list to Sheet3. Delete redundant columns in Sheet2 according to the design configuration header column requirements. Add a physical configuration information column header (installation drawing number version, process document number, serial number) to Sheet2 according to the physical information header column requirements.

[0024] (3) Data matching: Based on preset rules, VBA scripts are used to match the correspondence and uniqueness between manufacturing configuration and design configuration data. Add a column to Sheet2 to calculate the complete path information for each row of data by hierarchy (associated component number, parent number, and parent-parent number). Store the cleaned configuration specification component list data in a "new array". Open the old basic difference comparison data table by the path in Sheet1 and temporarily copy it to Sheet2. Similarly, add a column to Sheet2 to calculate the complete path information for each row of data by hierarchy. Store the cleaned old difference comparison data in the "old array". Iterate through each row of data in the new difference data table (row i) and each row of data in the old difference data table (row j) to compare whether the complete path information of the BOM is consistent.

[0025] (4) Difference identification: By identifying the differences in the matching results, the difference type (no difference, new addition, version upgrade, number change) is recorded.

[0026] By comparing the complete path information of the BOM line by line to see if they are consistent, the difference type of the corresponding line is identified as no difference, new addition, version upgrade, or number change.

[0027] (4) Report generation: Automatically list all configuration information (including part number, drawing number, version, etc.) according to the data storage table template set in the Excel sheet, and automatically generate difference information based on the identified difference type and the content of the engineering change instruction.

[0028] Based on the difference type of each row, when the difference type is no difference, version upgrade, or number change, the physical configuration information in the old difference data comparison table is automatically assigned to the corresponding column in the new array. When the difference type of the row is version upgrade, the row number is recorded. The engineering change instruction content list is opened according to the engineering change instruction report path. According to the row number recorded in the version upgrade array, the design version of the new difference data is compared with the old design version row by row. The number of versions of the difference is matched with the engineering change instruction content according to the engineering change instruction number and filled into the difference description reference. When the difference type of the row is number change, the row number is recorded. The engineering change instruction content list is opened according to the engineering change instruction report path. According to the row number recorded in the number change array, the file number and version in the design configuration are converted into engineering change instruction numbers and matched with the engineering change instruction content and filled into the difference description reference.

[0029] The following examples further illustrate the VBA-based rapid comparison method for aircraft manufacturing configuration and design configuration described in this invention.

[0030] (1) such as Figure 2 As shown, first verify whether the data is empty according to the file input path in Sheet1. Open the EBOM file (configuration specification component list) according to the EBOM path. Filter the configuration specification component list by leix_arr to remove the information in the rows where the document type is one of the six types of documents, standard parts, and electronic components. Copy the filtered results to Sheet2. Copy the airborne hardware and software list to Sheet3.

[0031] (2) such as Figure 2 As shown, according to the design configuration header column requirements, delete the redundant columns in Sheet2; according to the physical information header column requirements, add a physical configuration information column header (installation drawing number version, process document number, serial number) to Sheet2. Furthermore, add a column to Sheet2: "Complete Path Information" P_i for any component i. This path represents the absolute position of this component in the product manufacturing configuration tree, and the complete path information of the component is calculated as a key index for comparison. The specific calculation method is as follows: The entire configuration data table can be regarded as a tree structure T, where each component is a node in the tree. Each node i contains at least the following attributes: 1. ID_i: (unique identifier of the node); 2. Parent_ID_i: unique identifier of its parent node; 3. Level_i: the level depth of the node in the tree. Therefore, for any node i, its complete path P_i is an ordered sequence tracing back from itself to the root node. This sequence can be formed by concatenating a key attribute (such as name, code) of all its ancestor nodes. This calculation process can be formalized as a recursive function or iterative formula: P_i = F(i) = { F(Parent(i)) + Delimiter + K_i, if Parent(i) exists;K_i, otherwise} Where P_i is the full path string of node i, F() is the path calculation function, Parent(i) is the parent node of node i, K_i is the key attribute value of node i (such as name or code), and Delimiter is the path separator (such as " / " or "->").

[0032] Store the cleaned configuration specification component list data in a "new array". Open the old basic difference comparison data table by the path in Sheet1, temporarily copy it to Sheet2, and add a column as described above: "Complete Path Information" for any component i. The calculation method is still the same as the above formula. Store the cleaned old difference comparison data in the "old array". Iterate through each row of data in the new difference data table, row i, and iterate through each row of data in the old difference data table, comparing whether the complete path information of the BOM is consistent.

[0033] (3) By comparing the complete path information of the BOM line by line, the difference type of the corresponding line is identified as no difference, new addition, version upgrade, or number change.

[0034] (4) such as Figure 3 As shown, based on the difference type of each row, when the difference type is no difference, version upgrade, or number change, the physical configuration information in the old difference data comparison table is automatically assigned to the corresponding column in the new array. When the difference type of the row is version upgrade, the row number is recorded. The engineering change instruction content list is opened according to the engineering change instruction report path. According to the row number recorded in the version upgrade array, the design version of the new difference data is compared with the old design version row by row. The number of versions of the difference is matched with the engineering change instruction content according to the engineering change instruction number and filled into the difference description reference. When the difference type of the row is number change, the row number is recorded. The engineering change instruction content list is opened according to the engineering change instruction report path. According to the row number recorded in the number change array, the file number and version in the design configuration are converted into engineering change instruction numbers and matched with the engineering change instruction content and filled into the difference description reference.

[0035] Comparative Example (1) Set up the EXCEL table template: Sheet1 is used as a table to compare the manufacturing configuration and design configuration of components, storing the comparison results data; Sheet2 is used as a table to compare the manufacturing configuration and design configuration of airborne equipment hardware and software and ATP, storing the comparison results data. Open the configuration instructions and copy the design configuration information from the configuration instructions according to the table template.

[0036] (2) Use the CONCATENATE function to associate the component number and parent part number of the design configuration data in Sheet1 with path information line by line. Similarly, associate the component number and parent part number with path information in the old difference comparison data table. Use the VLOOKUP function to search for the physical configuration information corresponding to the same path information in the old difference comparison data table line by line. Then use the IF function to output the difference between the physical configuration and the design configuration information line by line. By filtering the difference rows, manually identify the difference type line by line and then use the VLOOKUP function to search for the corresponding engineering change instruction content.

[0037] (3) When using the VLOOKUP function to search for the physical configuration information corresponding to the same path information in the old difference comparison data table, it is easy to make a matching error. Therefore, it is necessary to filter out the duplicate path information and manually check and confirm it to ensure the accuracy of the output difference report data.

[0038] When the amount of data is too large, manually sorting out the differences between the aircraft manufacturing configuration and the design configuration is inefficient, and the excessive number of steps makes it prone to errors, resulting in low accuracy of the output results.

[0039] The above detailed embodiments are a description of the present invention. It should not be considered that the specific embodiments of the present invention are limited to these descriptions. For those skilled in the art, several simple deductions and substitutions can be made without departing from the concept of the present invention, and all of these should be considered to fall within the protection scope of the present invention.

Claims

1. A method for rapid comparison of differences between aircraft manufacturing configuration and design configuration based on VBA, characterized in that, The automated comparison of manufacturing configuration and design configuration data is achieved using VBA scripts, specifically including the following steps: Step 1: Set up the user interface: Design a user-friendly interface that allows users to easily input paths, select data sources, and view comparison results; Step 2, Data Input: Obtain the manufacturing configuration and design configuration data from the file according to the set path; Step 3, Data Cleaning: Use VBA scripts to clean the format of manufacturing and design configuration data; Step 4: Data Matching: Based on preset rules, use VBA scripts to match the correspondence and uniqueness between manufacturing configuration and design configuration data; Step 5, Difference Identification: Identify the differences in the matching results and record the difference types; Step 6, Report Generation: Automatically list all configuration information according to the data storage table template set in the Excel sheet, and automatically generate difference information based on the identified difference types and the content of engineering change instructions.

2. The method as described in claim 1, characterized in that, The configuration information in the path file in step 2 includes component information, airborne equipment hardware and software, and ATP information.

3. The method as described in claim 1, characterized in that, The data cleaning in step 3 includes filtering out the information in the EBOM file that contains documents of the six types, standard parts, and electronic components by filtering by leix_arr.

4. The method as described in claim 1, characterized in that, In step 4, data matching includes storing the cleaned configuration specification component list data in a new array and storing the cleaned old difference data comparison table data in the old array.

5. The method as described in claim 1, characterized in that, In step 4, data matching based on preset rules includes associating component numbers, parent numbers, and upper-level parent numbers as complete BOM path information. The data correspondence and uniqueness of the configuration description and the old difference base comparison data table are matched through the complete BOM path information.

6. The method as described in claim 1, characterized in that, In step 6, when the difference type is no difference, version upgrade, or number change, the physical configuration information in the old difference data table is automatically assigned to the corresponding column in the new array.

7. The method as described in claim 1, characterized in that, In step 6, the difference information generated by automatically associating the engineering change instruction content with the difference type includes recording the row number when the difference type is upgrade, opening the engineering change instruction content list according to the engineering change instruction report path, comparing the design version of the new difference data with the old design version according to the row number recorded in the upgrade array, and filling the difference description reference with the number of version differences and matching the engineering change instruction content according to the engineering change instruction number.

8. The method as described in claim 1, characterized in that, In step 6, the difference information generated by automatically associating the engineering change instruction content with the difference type includes recording the line number when the difference type is a number change, opening the engineering change instruction content list according to the engineering change instruction report path, converting the file number and version in the design configuration into the engineering change instruction number according to the line number recorded in the number change array, and filling in the difference description reference by matching the engineering change instruction content.

9. The method as described in claim 1, characterized in that, Step 6 automatically lists all configuration information, including components and airborne equipment.

Images