VBA-based Excel automatic data difference analysis and processing method

Abstract

The invention discloses a VBA-based Excel automatic data difference analysis and processing method, and relates to the technical field of rail transit software baseline restriction list data, and the method comprises the following steps: S1, clearing the format and content of an ACPreviusBaseline table; s2, the format and the content of an AClatestBaseline table are cleared away; s3, comparing the limit analysis table of the last round with the limit list of the latest software baseline in combination with the VBA macro, judging the change type of each limit through difference analysis, and carrying out difference processing on different change types; s4, carrying out difference analysis to judge no change and limited change types and carrying out difference processing on the change types and the limited change types; s5, reversely traversing the old base line, analyzing and judging the type of the base line before the base line is deleted, and processing the base line; and S6, an analysis and processing result is output by the AClatestBaseline table. According to the invention, through an automatic comparison process, the original time consumed by several hours for manual check one by one is shortened to a minute level, and the efficiency is improved by more than 90%.

Description

Technical Field

[0001] The present invention relates to the technical field of rail transit software baseline limit list data, and more specifically, to a method for analyzing and processing differences in limit list data in Excel format based on VBA automation. Background Art

[0002] After the signal system software in the rail transit field is officially released, it needs to be continuously updated and new software versions need to be released according to requirements. Each officially released software version after each update and upgrade is called a software baseline. With each software update, an Excel limit list with a similar data structure will be output within the software baseline, including limit tag numbers, limit titles, and limit details.

[0003] When application designers design based on different software baseline versions, they need to comply with these limit contents output at the system software level. Designers need to conduct a detailed analysis, allocation, implementation, and draw a processing conclusion for all the limits in the currently used software baseline according to their limit titles and limit contents to ensure design correctness and thus not affect the on-site use performance of the software system.

[0004] After designers first use and analyze the current software baseline, they obtain a limit analysis list applicable to the current software baseline. As the software version continues to iterate, if the software version is updated, designers need to manually compare the limit differences between the two software baselines according to the new software baseline limits and adopt different processing methods for different limit change types. The limit change types and processing methods are as follows:

[0005] 1. "No change" type: The limit tag number, limit title, and limit details remain unchanged. Designers do not need to re-analyze and only need to migrate and reuse the conclusions of the previous round of limit analysis;

[0006] 2. "Limit change" type: The limit tag number remains unchanged, but the limit title or limit content changes. Designers need to identify the specific changed content and conduct a detailed analysis of this part. The analysis conclusions of the previous software baseline cannot be reused;

[0007] 3. "New limit" type: The limit tag number does not exist in the limit analysis of the previous version of the software baseline and belongs to a completely new limit. Designers need to conduct a complete analysis of it;

[0008] 4. "New limit (restored)" type: The limit tag number appears in the limit analysis of the previous version of the software baseline but has a strikethrough, indicating that this limit was deleted during the previous round of analysis and is re-output in the current software baseline, belonging to a restored new limit. Designers need to re-analyze it.

[0009] 5. "Deleted (in this baseline)" type: The restricted label number existed in the restriction analysis of the previous software baseline, but in this software baseline, the restricted label number has a strikethrough, indicating that this restriction does not need to be complied with in this software baseline. Designers do not need to consider this restriction in the design, but the conclusions of the previous round can be reused.

[0010] 6. "Deleted (before this baseline)" type: The restricted label number existed in the restriction analysis of the previous software baseline, but this restricted label number does not exist in this software baseline, indicating that this restriction was deleted in a certain version of the software baseline between the previous software baseline and the software baseline used this time. Designers do not need to consider this restriction in the design, and only need to retain the restriction content in the new restriction analysis form.

[0011] Currently, the identification and processing of the above restriction change types completely rely on manual work. Manually search for the restricted label number in the new restriction release form one by one using the search function of Excel according to the previous restriction analysis form to determine whether the restriction is new. If it is not new, check whether it has a strikethrough, which means it is a deleted restriction. If it is not a deleted restriction, further manually identify whether the restriction title or content has changed. If the content has changed, further manually identify the specific changed content and analyze the changed content. This process not only consumes a lot of time, but also is extremely prone to omissions or errors in the identification of restriction changes due to human errors. Summary of the Invention

[0012] In order to overcome the defects existing in the above-mentioned prior art, the present invention discloses a VBA-based Excel automated data difference analysis and processing method. The purpose of the present invention is to solve the problems that the identification and processing of restriction change types in the prior art rely on manual work, consume a lot of time, and cause omissions or errors in the identification of restriction changes.

[0013] In order to achieve the above object, the technical solution adopted by the present invention is:

[0014] A VBA-based Excel automated data difference analysis and processing method, which uses Excel to set up the AC_latest_Baseline table to store the restriction list of the latest software baseline, and sets up the AC_Previous_Baseline table to store the previous round of restriction analysis table;

[0015] Preferably, the AC_latest_Baseline table and the AC_Previous_Baseline table are abbreviated as ws1 and ws2 respectively. Column A in both ws1 and ws2 is the column where the restricted label number is located, column B is the column where the restriction title is located, column C is the column where the restriction content is located, and columns D to AA are the columns in ws1 and ws2 other than columns A, B, and C;

[0016] In ws1, column D is the column where the content of the previous round of restrictions is located, column E is the column where the changed content is located, column F is the column where the comparison result is located, and columns G and later are the columns where the analysis conclusions of the new round are located. In ws1, there are Clear control and Compare control set.

[0017] In ws2, columns D and later are the columns where the analysis conclusions of the previous round are located. In ws2, there is Clear control set.

[0018] The data difference analysis and processing method includes the following steps:

[0019] I. Clearing and prompting of AC_Previous_Baseline table

[0020] S1. Clear the format and content of the AC_Previous_Baseline table, and prompt the user to fill in the previous round of restriction analysis table that needs to be compared.

[0021] Preferably, step S1 includes: the user clicks the Clear control of the AC_Previous_Baseline table, calls the format and content clearing module, clears the historical data, automatically adjusts the row height, and prompts the user to fill in the previous round of restriction analysis table that needs to be compared.

[0022] II. Clearing and prompting of AC_latest_Baseline table

[0023] S2. Clear the format and content of the AC_latest_Baseline table, and prompt the user to fill in the restriction list of the latest software baseline.

[0024] Preferably, step S2 includes: the user clicks the Clear control of the AC_latest_Baseline table, calls the format and content clearing module, clears the historical data, automatically adjusts the row height, and prompts the user to fill in the restriction list of the latest software baseline.

[0025] III. Comparison between the restriction analysis table and the restriction list

[0026] S3. Combine VBA macro to compare the previous round of restriction analysis table and the restriction list of the latest software baseline, determine the change type of each restriction through difference analysis and perform difference processing on different change types. The change types of the restrictions include: no change, restriction change, new restriction, new restriction recovery, this baseline has been deleted, before this baseline has been deleted. When making the determination, first determine the new restriction, this baseline has been deleted, and new restriction recovery types in sequence, then enter step S4 to determine the no change and restriction change types, and enter step S5 to determine the before this baseline has been deleted type;

[0027] Preferably, step S3 includes: the user clicks the Compare control of the AC_latest_Baseline table, calls the built-in data comparison module, and the data comparison module compares the previous round of limit analysis table with the latest limit list according to the built-in rules, determines the change type of each limit and performs corresponding color or symbol marking.

[0028] Preferably, step S3 includes the following steps:

[0029] S31. Initialize the format: Call the format clearing module to clear all the filling formats from the 3rd row to the last row in ws1, including the background color, automatically adjust the row height to 25, and add continuous border lines to columns D to AA;

[0030] S32. Obtain the data range: Obtain the row numbers of the last rows in column A of the limit label numbers in ws1 and ws2 respectively, denoted as the latest baseline lastRow1 and the previous round of baseline lastRow2;

[0031] S33. Traverse the latest baseline and match the label numbers: Start traversing from the 3rd row of ws1 to lastRow1, read the limit label numbers in column A row by row, and find the exactly matching label numbers in column A of ws2;

[0032] If no matching value is found: Determine it as "new limit added", mark "new limit added" in column F of ws1, and mark the background colors of columns A, B, and C of the current row in red;

[0033] If a matching value is found: Execute the strikethrough judgment logic of S34 - S35;

[0034] S34. Judge the strikethrough in the latest baseline: Check whether there is a strikethrough in columns A, B, and C of the current row in ws1:

[0035] If it exists: Determine it as "this baseline has been deleted", mark the result in column F, mark the background colors of columns A to AA of the current row in gray, and copy the data in columns D to AA of the corresponding row in ws2 to after column G of ws1;

[0036] If it does not exist: Continue to execute S35;

[0037] S35. Judge the strikethrough in the previous round of baseline: Check whether there is a strikethrough in columns A, B, and C of the matching row in ws2:

[0038] If it exists: Determine it as "new limit restored", mark the result in column F, and mark the background colors of columns A, B, and C of the current row in ws1 in red;

[0039] If it does not exist: Execute the content comparison logic and enter step S4.

[0040] IV. Determine no change and restricted change types

[0041] S4. Conduct difference analysis to determine no change and restricted change types and perform difference processing on them. When processing restricted change types, use regular expressions and a two-way comparison logic to generate a structured comparison result;

[0042] Preferably, step S4 includes: for the restrictions of the "restricted change" type, call the change comparison module, use regular expressions and a two-way comparison logic to compare the restriction title column and the restriction content column of the rows corresponding to the same restriction tag number in AC_latest_Baseline found in AC_Previous_Baseline, and identify and output the specific change content in the format of: "New: XXX", "Deleted: XXX".

[0043] Preferably, step S4 includes the following steps:

[0044] S41. Data preprocessing: Perform preprocessing on the strings in column B of the restriction title and column C of the restriction content to be compared in ws1 and ws2, convert variables to strings, and delete all spaces, tab characters, Windows / Linux / Mac-style line breaks, and carriage returns to obtain the standardized string Str2 of the latest baseline and the standardized string Str1 of the previous baseline;

[0045] S42. Content consistency judgment:

[0046] If Str1 = Str2, mark it as "no change", clear the background colors of columns B and C, and copy the data in columns D to AA of ws2 to the right of column G of ws1;

[0047] If Str1 ≠ Str2, perform the following operations:

[0048] Color the corresponding cells in column B or C of ws1 purple and mark "restricted change" in column F;

[0049] Copy the corresponding original content in ws2 to column D of ws1, retaining the line break format;

[0050] Call the string difference comparison function to enter steps S43 - S45;

[0051] S43. Create a regular expression object: Use regular expressions to split the strings Str1 and Str2 into arrays of words matches1 and matches2;

[0052] S44. Execute the two-way comparison logic:

[0053] Deleted items: Words that exist in matches1 but not in matches2 are marked as "Deleted: XXX".

[0054] Newly added items: Words that exist in matches2 but not in matches1 are marked as "Newly added: XXX".

[0055] S45. Construct the change comparison result string: Write the comparison results of the words marked as newly added and deleted into column E of ws1 in the format "Deleted: XXX; Newly added: XXX".

[0056] V. Determine the type before deleting this baseline

[0057] S5. Traverse the old baseline in reverse, analyze and determine the type before deleting this baseline, and process it;

[0058] Preferably, step S5 includes: Traversing the old baseline in reverse: Traverse from the 3rd row of ws2 to the last row of the previous baseline lastRow2, read the restricted label number in column A row by row, and find the matching value in column A of ws1;

[0059] If no matching value is found: Determine it as "Before deleting this baseline", and perform the following operations:

[0060] Copy the data in columns A, B, and C of the current row of ws2 to a newly added row at the end of ws1;

[0061] Mark "Before deleting this baseline" in column F of ws1, and copy the data from columns D to AA of ws2 to the columns after column G of ws1;

[0062] Add a strikethrough and a gray background to columns A to AA of the newly added row;

[0063] Automatically adjust the row height of ws1 to 25, and add continuous border lines to columns D to AA;

[0064] If a matching value is found, do not process.

[0065] VI. Output

[0066] S6. Output the analysis and processing results in the AC_latest_Baseline table.

[0067] Preferably, step S6 includes: Standardize the output format: Automatically adjust the row height of the entire AC_latest_Baseline table to 25, add borders, display the comparison completion information, and end.

[0068] The differences between the present invention and the prior art are as follows:

[0069] 1. VBA-based automated comparison framework:

[0070] By defining two worksheets, AC_latest_Baseline and AC_Previous_Baseline, and combining with VBA macros, the automatic comparison, marking, and result output of the restriction list are realized.

[0071] Protection points: The architecture design of the automated comparison module, including worksheet object initialization, data traversal, and matching logic.

[0072] 2. Association rules for strikethrough and color marking:

[0073] By judging the strikethrough status of cells and combining with the background color markings of red (new / restored), gray (deleted), and purple (content changed), the visual distinction of change types is realized.

[0074] Protection points: The linkage logic between strikethrough judgment and color marking, and the automatic classification rules for 6 change types.

[0075] 3. Bidirectional string comparison algorithm:

[0076] The regular expression (\S+) is used to split the string into an array of words, and the difference items (deleted / added) are matched by bidirectional traversal to generate a structured comparison result ("Deleted: XXX; Added: XXX").

[0077] Protection points: The string preprocessing method based on regular expressions and the bidirectional difference comparison algorithm.

[0078] 4. Historical deletion detection by reverse traversing the old baseline:

[0079] By traversing the restriction tag numbers in the old baseline, the missing entries in the new baseline are detected, automatically marked as "deleted before this baseline", and the historical data is synchronously copied.

[0080] Protection points: The reverse traversal logic and the historical deletion marking mechanism.

[0081] Advantages of the present invention:

[0082] Compared with the prior art (completely relying on manual comparison), the proposed application of the present application has the following significant advantages:

[0083] 1. Efficiency improvement:

[0084] Through the automated comparison process, the originally time-consuming manual item-by-item inspection is shortened to the minute level, and the efficiency is improved by more than 90%.

[0085] 2. Enhanced accuracy:

[0086] Based on the string comparison algorithm and strikethrough judgment rules of regular expressions, the content differences and change types can be accurately identified, avoiding manual omissions or misjudgments.

[0087] 3. Standardized output:

[0088] Automatically generate standardized comparison results (such as the details of differences in column E and the change types in column F), and supplement them with color markings and strikethroughs, greatly improving the readability and operability of the results.

[0089] 4. Reduce manual intervention:

[0090] Users only need to click the "Clear" and "Compare" controls to complete the full process operation, without the need to have advanced Excel skills, reducing the operation threshold.

[0091] 5. Support historical data backtracking:

[0092] By traversing the old baseline data in reverse, automatically mark historical deletion items to ensure the integrity of change records and avoid missing deletion operations in intermediate versions.

[0093] 6. Strong scalability:

[0094] The modular design (such as format clearing, string comparison) supports quickly adapting to other Excel data comparison scenarios with similar structures, such as requirement change management, version difference analysis, etc. Description of the drawings

[0095] Figure 1 This is the layout of the AC_latest_Baseline worksheet of the present invention;

[0096] Figure 2 This is the layout of the AC_Previous_Baseline worksheet of the present invention;

[0097] Figure 3 This is the flowchart of the restricted list data comparison module of the present invention;

[0098] Figure 4 This is Figure 3 the enlarged upper part of

[0099] Figure 5 This is Figure 3 the enlarged lower left part of

[0100] Figure 6 This is Figure 3 the enlarged lower right part of

[0101] Figure 7 This is the schematic diagram of the process of the restricted content change comparison module of the present invention;

[0102] Figure 8 This is Figure 7 the enlarged upper part of

[0103] Figure 9For Figure 7 An enlarged view of the lower part of

[0104] Figure 10 A schematic diagram of the six change type markings of the present invention;

[0105] Figure 11 A schematic diagram of the comparison result of the content change difference of the present invention. Detailed implementation manners

[0106] The following will clearly and completely describe the concept, specific structure and technical effects of the present invention in combination with embodiments and drawings, so as to fully understand the purpose, features and effects of the present invention.

[0107] A method for automatic data difference analysis and processing based on VBA in Excel defines a limit list for storing the latest software baseline in AC_latest_Baseline (as shown in the appendix Figure 1 ), and sets a Clear control and a Compare control. Define a form AC_Previous_Baseline for storing the previous round of limit analysis table (as shown in the appendix Figure 2 ), and set a Clear control. The detailed steps of the technical solution are as follows:

[0108] Step S1: The user clicks the Clear control of the AC_Previous_Baseline list, calls the format and content clearing module, clears the historical data, automatically adjusts the row height, and prompts the user to fill in the previous round of limit analysis table to be compared;

[0109] Step S2: The user clicks the Clear control of the AC_latest_Baseline list, calls the format and content clearing module, clears the historical data, automatically adjusts the row height, and prompts the user to fill in the limit list of the latest software baseline;

[0110] Step S3: The user clicks the Compare control of the AC_latest_Baseline list, calls the built-in data comparison module, and the module automatically compares the previous round of limit analysis table and the latest limit list according to the built-in rules. Automatically determine the change type of each limit and perform corresponding color or symbol markings. Define six change types, including: no change, limit change, new limit, new limit (recovery), deleted (this baseline), deleted (before this baseline), and the type of "deleted (before this baseline)" is judged in step S5;

[0111] As Figures 3 - 6 shown, the specific steps of step S3 are as follows:

[0112] S31. Set worksheet objects: Define the AC_Previous_Baseline worksheet as the previous round of limit analysis table (hereinafter referred to as ws2), and the AC_latest_Baseline worksheet as the limit list of the latest software baseline (hereinafter referred to as ws1);

[0113] S32. Initialize the format: Call the format clearing module to clear all fill formats (including background colors) from the 3rd row to the last row in ws1, automatically adjust the row height to 25, and add continuous border lines to columns D to AA;

[0114] S33. Obtain the data range: Obtain the last row numbers (ignoring blank rows) of the limit label number columns (column A) in ws1 and ws2 respectively, denoted as lastRow1 (latest baseline) and lastRow2 (previous round of baseline);

[0115] S34. Traverse the latest baseline and match the label numbers: Start traversing from the 3rd row of ws1 to lastRow1, read the limit label numbers (column A) row by row, and search for exactly matching label numbers (ignoring leading and trailing spaces) in column A of ws2.

[0116] If no matching value is found: Determine it as "new limit", mark "new limit" in column F of ws1, and color the background of columns A, B, and C of the current row red (RGB255, 0, 0).

[0117] If a matching value is found: Execute the strikethrough judgment logic in S35 - S36;

[0118] S35. Judge the strikethrough in the latest baseline: Check whether there is a strikethrough in columns A, B, and C of the current row in ws1:

[0119] If it exists: Determine it as "deleted (this baseline)", mark the result in column F, color the background of columns A to AA of the current row gray (RGB128, 128, 128), and copy the data in columns D to AA of the corresponding row in ws2 to after column G of ws1;

[0120] If it does not exist: Continue to execute S36;

[0121] S36. Judge the strikethrough in the previous round of baseline: Check whether there is a strikethrough in columns A, B, and C of the matching row in ws2:

[0122] If it exists: Determine it as "new limit (restored)", mark the result in column F, and color the background of columns A, B, and C of the current row in ws1 red.

[0123] If it does not exist: Execute the content comparison logic (step S4);

[0124] Step S4: For the restrictions determined to be of the "restriction change" type in step S3, further call the change comparison module, and use regular expressions and a two-way comparison logic to compare the restriction title column and the restriction content column of the rows corresponding to the same restriction tag number in AC_latest_Baseline found in AC_Previous_Baseline, and further identify and output the specific change content in the format of: "New: XXX", "Deleted: XXX".

[0125] As Figures 7 - 9 shown, the change comparison module in step S4 specifically executes the following steps:

[0126] S41. Data preprocessing: Perform preprocessing on the strings of the restriction titles and restriction contents (columns B and C) to be compared in ws1 and ws2, convert variables to strings, and delete all spaces, tab characters, Windows / Linux / Mac style line breaks, and carriage return characters to obtain the standardized strings Str2 (latest baseline) and Str1 (previous baseline);

[0127] S42. Content consistency judgment:

[0128] If Str1 = Str2, mark it as "no change", clear the background colors of columns B and C, and copy the data in columns D to AA in ws2 to the right of column G in ws1.

[0129] If Str1 ≠ Str2, perform the following operations:

[0130] 1. Mark the background color of the corresponding cells in column B or C of ws1 purple (RGB128, 0, 128), and mark "restriction change" in column F;

[0131] 2. Copy the corresponding original content in ws2 to column D in ws1 (retaining the line break format);

[0132] 3. Call the string difference comparison function (CompareAndMarkStrings), see S43 - S45 for details;

[0133] S43. Create a regular expression object: Use the regular expression (\S+) to split the Str1 and Str2 strings into word arrays matches1 and matches2;

[0134] S44. Two-way comparison logic:

[0135] Deleted items: Words that exist in matches1 but not in matches2 are marked as "Deleted: XXX".

[0136] New items: Words that exist in matches2 but not in matches1 are marked as "New: XXX".

[0137] S45. Construct the change comparison result string: Write the comparison results of the words marked as new and deleted into column E of ws1 in the format "Deleted: XXX; New: XXX".

[0138] Step S5. Traverse the old baseline in reverse: As Figure 3 and Figure 6 shown, traverse from row 3 of ws2 to lastRow2, read the restricted tag numbers (column A) row by row, and find the matching values in column A of ws1.

[0139] If no matching value is found: Determine it as "Deleted (before this baseline)", and perform the following operations:

[0140] 1. Copy the data in columns A, B, and C of the current row of ws2 to a new row added at the end of ws1.

[0141] 2. Mark "Deleted (before this baseline)" in column F of ws1, and copy the data from columns D to AA of ws2 after column G.

[0142] 3. Add strikethrough to columns A to AA of the new row and mark the background in gray (RGB128, 128, 128).

[0143] 4. Automatically adjust the row height to 25, and add continuous border lines to columns D to AA.

[0144] If a matching value is found, do nothing.

[0145] Step S6. Standardize the output format: Automatically adjust the row height of the entire AC_latest_Baseline table to 25, add borders, display the comparison completion information, and end, as Figure 10 and Figure 11 shown.

[0146] The above has specifically described the embodiments of the present invention, but the present invention is not limited to the described embodiments. Those skilled in the art can make various equivalent variations or substitutions without departing from the spirit of the present invention, and these equivalents or substitutions are all included within the scope defined by the claims of the present invention.

Claims

1. A method for automated Excel data difference analysis and processing based on VBA, characterized in that Use Excel to set up the AC_latest_Baseline table to store the limit list of the latest software baseline, and set up the AC_Previous_Baseline table to store the previous round of limit analysis table; the data difference analysis and processing method includes the following steps: S1. Clear the format and content of the AC_Previous_Baseline table, and prompt the user to fill in the previous round of limit analysis table to be compared; S2. Clear the format and content of the AC_latest_Baseline table, and prompt the user to fill in the limit list of the latest software baseline; S3. Combine the VBA macro to compare the previous round of limit analysis table and the limit list of the latest software baseline, perform difference analysis to determine the change type of each limit and perform difference processing on different change types. The change types of the limit include: no change, limit change, new limit, new limit recovery, this baseline deleted, before this baseline deleted. When judging, first judge the new limit, this baseline deleted, new limit recovery types in sequence, then enter step S4 to judge the no change and limit change types, and enter step S5 to judge the before this baseline deleted type; S4. Perform difference analysis to determine the no change and limit change types and perform difference processing on them. When processing the limit change type, use regular expressions and use two-way comparison logic to generate a structured comparison result; S5. Traverse the old baseline in reverse, analyze and determine the before this baseline deleted type and process it; S6. The AC_latest_Baseline table outputs the analysis and processing results.

2. The method for automated Excel data difference analysis and processing based on VBA according to claim 1, characterized in that, The AC_latest_Baseline table and the AC_Previous_Baseline table are abbreviated as ws1 and ws2 respectively. Column A in both ws1 and ws2 is the column where the limit label number is located, column B is the column where the limit title is located, column C is the column where the limit content is located, and columns D to AA are the columns in ws1 and ws2 other than columns A, B, and C; In ws1, column D is the column where the previous round of limit content is located, column E is the column where the changed content is located, column F is the column where the comparison result is located, and columns G and later are the columns where the new round of analysis conclusions are located. There are Clear control and Compare control set in ws1; In ws2, columns D and later are the columns where the previous round of analysis conclusions are located. There is Clear control set in ws2.

3. The method for automatically analyzing and processing data differences in Excel based on VBA according to claim 2, wherein, Step S1 includes: the user clicks the Clear control of the AC_Previous_Baseline table, calls the format and content clearing module, clears the historical data, automatically adjusts the row height, and prompts the user to fill in the previous round of limit analysis table to be compared.

4. The method for automatically analyzing and processing data differences in Excel based on VBA according to claim 2, characterized in that, Step S2 includes: the user clicks the Clear control of the AC_latest_Baseline table, calls the format and content clearing module, clears the historical data, automatically adjusts the row height, and prompts the user to fill in the limit list of the latest software baseline.

5. The method for automated data difference analysis and processing of Excel based on VBA according to claim 2, characterized in that, Step S3 includes: The user clicks the Compare control of the AC_latest_Baseline table to call the built-in data comparison module. The data comparison module compares the previous round of restriction analysis table and the latest restriction list according to the built-in rules, determines the change type of each restriction, and makes corresponding color or symbol markings.

6. The method for automated data difference analysis and processing of Excel based on VBA according to claim 2, characterized in that, Step S3 includes the following steps: S31. Initialize the format: Call the format clearing module to clear all filling formats from the 3rd row to the last row in ws1, including the background color, automatically adjust the row height to 25, and add continuous border lines to columns D to AA; S32. Obtain the data range: Obtain the row numbers of the last rows in column A of the restriction label numbers in ws1 and ws2 respectively, denoted as the latest baseline lastRow1 and the previous round of baseline lastRow2; S33. Traverse the latest baseline and match the label numbers: Start traversing from the 3rd row of ws1 to lastRow1, read the restriction label numbers in column A row by row, and find the exactly matching label numbers in column A of ws2; If no matching value is found: Determine it as "new restriction", mark "new restriction" in column F of ws1, and mark the background colors of columns A, B, and C of the current row in red; If a matching value is found: Execute the strikethrough judgment logic of S34 - S35; S34. Judge the strikethrough in the latest baseline: Check whether there is a strikethrough in columns A, B, and C of the current row in ws1: If it exists: Determine it as "this baseline has been deleted", mark the result in column F, mark the background colors of columns A to AA of the current row in gray, and copy the data in columns D to AA of the corresponding row in ws2 to after column G of ws1; If it does not exist: Continue to execute S35; S35. Judge the strikethrough in the previous round of baseline: Check whether there is a strikethrough in columns A, B, and C of the matching row in ws2: If it exists: Determine it as "recovery of new restriction", mark the result in column F, and mark the background colors of columns A, B, and C of the current row in ws1 in red; If it does not exist: Execute the content comparison logic and enter Step S4.

7. The method for automated data difference analysis and processing of Excel based on VBA according to claim 2, wherein Step S4 includes: For restrictions of the "restriction change" type, call the change comparison module, use regular expressions and a two-way comparison logic to compare the restriction title column and the restriction content column of the rows corresponding to the same restriction label numbers in AC_latest_Baseline found in AC_Previous_Baseline, identify and output the specific change content in the format of: "New: XXX", "Deleted: XXX".

8. The method for automated Excel data difference analysis and processing based on VBA according to claim 2, wherein Step S4 includes the following steps: S41. Data preprocessing: Perform preprocessing on the strings in columns B (restriction title) and C (restriction content) to be compared in ws1 and ws2, convert variables to strings, delete all spaces, tab characters, Windows / Linux / Mac style line breaks and carriage returns, and obtain the standardized string Str2 of the latest baseline and the standardized string Str1 of the previous round of baseline; S42. Content consistency judgment: If Str1 = Str2, mark it as "no change", clear the background colors of columns B and C, and copy the data from columns D to AA in ws2 to the columns after column G in ws1; If Str1 ≠ Str2, perform the following operations: Mark the background color of the corresponding cells in column B or C of ws1 purple, and mark "restricted change" in column F; Copy the corresponding original content in ws2 to column D of ws1, retaining the line break format; Call the string difference comparison function to enter steps S43 - S45; S43. Create a regular expression object: Use regular expressions to split the Str1 and Str2 strings into word arrays matches1 and matches2; S44. Execute the two-way comparison logic: Deleted items: Words that exist in matches1 but not in matches2 are marked as "Deleted: XXX"; Newly added items: Words that exist in matches2 but not in matches1 are marked as "Newly added: XXX"; S45. Construct the change comparison result string: Write the comparison results of the words marked as newly added and deleted in the format "Deleted: XXX; Newly added: XXX" to column E of ws1.

9. The method for automated data difference analysis and processing of Excel based on VBA according to claim 2, wherein, Step S5 includes: Traverse the old baseline in reverse: Traverse from the 3rd row of ws2 to the previous baseline lastRow2, read the restricted label number in column A row by row, and find the matching value in column A of ws1; If no matching value is found: Determine it as "before this baseline was deleted", and perform the following operations: Copy the data in columns A, B, and C of the current row in ws2 to a newly added row at the end of ws1; Mark "before this baseline was deleted" in column F of ws1, and copy the data from columns D to AA in ws2 to the columns after column G in ws1; Add a strikethrough and a gray background to columns A to AA of the newly added row; Automatically adjust the row height of ws1 to 25, and add continuous border lines to columns D to AA; If a matching value is found, do nothing.

10. A method for automatically analyzing and processing data differences in Excel based on VBA according to claim 2, characterized in that, Step S6 includes: Standardize the output format: Automatically adjust the row height of the entire AC_latest_Baseline table to 25, add borders, display the comparison completion information, and end.

Images