An excel and vba-based power grid data analysis processing method and system

Abstract

The application discloses a kind of power grid data analysis processing method and system based on Excel and VBA, including data source preparation, mapping table establishment, function optimization processing, double check, data merging and index calculation etc.Step, first acquisition heterogeneous data table and clear field specification, then establish business department and application name mapping table, subsequently through CONCATENATE function splicing composite association key as VLOOKUP function search value to realize multiple data source multiple condition matching, then to the data after processing Double check, then based on the check result merges heterogeneous data table and through Excel function nesting calculates power grid data core and expansion index, finally output three kinds of data synthesis table and generate power grid weekly report.The application realizes the accurate matching of multiple conditions of multiple data sources of power grid, solves the technical problems that power grid heterogeneous data correlation is not accurate and error cannot be positioned, greatly improves the accuracy and overall processing efficiency of power grid data processing.

Description

Technical Field

[0001] This application relates to the field of power grid data analysis, specifically to a power grid data analysis and processing method and system based on Excel and VBA. Background Technology

[0002] As the digital transformation of the power grid industry deepens, the power grid resource business platform has become the core carrier for power grid data integration and operation management. It needs to integrate various heterogeneous data tables, such as interface statistics and interface call status, to carry out data processing. These data tables have many fields, complex relationships, and high requirements for data standardization. In addition, the business requirement of high-frequency power grid weekly report filling places high demands on the accuracy, efficiency, and adaptability of data processing.

[0003] Currently, the mainstream methods for power grid data processing are mainly divided into two categories: one is based on developing customized processing systems using professional programming languages ​​such as Java, which can realize complex business logic processing and automated operations. However, this solution has a long development cycle, high technical and maintenance costs, poor adaptability to changes in business requirements, and requires professional developers to operate. Power grid business personnel cannot adjust it independently, making it difficult to meet the high-frequency reporting requirements of weekly reports. The other is based on Excel basic functions combined with manual operation to complete data processing. This method has a low operating threshold and high industry popularity, but due to the limitations of basic functions, it suffers from problems such as poor accuracy of data association, error handling can only simply mask the problem without locating the cause, lack of verification in data merging leading to redundancy or incorrect merging, low efficiency and error-prone manual operation, and inability to adapt to complex merging requirements of multiple heterogeneous data tables, making it difficult to meet the actual requirements of power grid data processing.

[0004] It is evident that the existing technology contains the following technical contradictions:

[0005] The contradiction between functionality and cost: professional programming solutions are powerful but expensive, while basic Excel solutions are low-cost but have limited functionality.

[0006] The contradiction between automation and ease of use: professional programming solutions have a high degree of automation but are complex to operate, while basic Excel solutions are easy to use but lack automation.

[0007] The contradiction between accuracy and efficiency: manual operation is difficult to guarantee accuracy, while automated solutions are difficult to balance flexibility.

[0008] Therefore, developing a power grid data analysis and processing solution that combines professional data processing capabilities with low cost, ease of operation, and high efficiency has become an urgent technical problem to be solved in this field. Summary of the Invention

[0009] In view of the above problems, this application provides a power grid data analysis and processing method and system based on Excel and VBA to solve the technical problem that the above-mentioned power grid data analysis technology cannot simultaneously achieve low threshold operation, ease of operation and efficient processing of complex business.

[0010] To achieve the above objectives, this application provides a power grid data analysis and processing method based on Excel and VBA, comprising the following steps:

[0011] Data source preparation: Obtain four types of heterogeneous data tables from the unified power grid dispatching component and the power grid resource business platform: interface statistics table, interface call status table, application support status data details, and application interface call status table, and clarify the field specifications of each data table;

[0012] Mapping table establishment: Establish a business department mapping table and an application name mapping table. The business department mapping table configures the professional mapping relationship between the application of the unified power grid dispatch component and the competent business department and the power grid resource business middle platform operation and management platform. The application name mapping table configures the full mapping relationship between the application name of the unified power grid dispatch component and the application name of the power grid resource business middle platform operation and management platform.

[0013] Function optimization:

[0014] a) Composite Association Key Matching: The application name and interface identifier, and application name and interface address are concatenated using the Excel CONCATENATE function to obtain composite association keys. These composite association keys are then used as the lookup value for the VLOOKUP function. A VLOOKUP lookup operation is performed in conjunction with the business department mapping table and the application name mapping table to achieve multi-condition matching across multiple data sources; b) Refined Error Handling: Error message rules are set through multi-level nesting of the IFERROR function. Corresponding text messages are returned for #N / A errors, #VALUE errors, and multiple matching result errors returned by the VLOOKUP function.

[0015] Double verification: Perform uniqueness and timeliness verification on the data source after function optimization, provide prompts or filter data with abnormal verification, and perform subsequent processing on data with normal verification.

[0016] Data merging and indicator calculation: Based on the results of dual verification, four types of heterogeneous data tables are merged, and the core and extended indicators of power grid data are calculated by nested Excel functions.

[0017] Automated execution: Batch execution of Excel functions and formulas is driven by VBA scripts to automate data source reading, formula calculation, and filling in unmatched data;

[0018] Output results: Output interface synthesis table, application support data synthesis table, application call data synthesis table, and generate power grid weekly report.

[0019] To address the aforementioned technical problems, this application also provides another technical solution:

[0020] A power grid data analysis and processing system based on Excel and VBA is implemented based on the aforementioned power grid data analysis and processing method based on Excel and VBA. The system is deployed in Excel software and includes a data source layer, a mapping table layer, a function optimization layer, a validation and merging layer, an automated execution layer, and an output layer that sequentially perform unidirectional data transmission. The functional configuration of each layer is as follows:

[0021] Data source layer: Establish data connection with the unified power grid dispatching component and the power grid resource business platform, and acquire and store four types of heterogeneous data tables: interface statistics table, interface call status table, application support status data details, and application interface call status table;

[0022] Mapping table layer: Stores and maintains business department mapping tables and application name mapping tables, and provides unified field association rules to the function optimization layer;

[0023] Function optimization layer: Configures the composite association key adaptation submodule and the refined error handling submodule, which respectively perform the concatenation of composite association keys, VLOOKUP lookup operation and IFERROR function multi-level nested error prompt operation;

[0024] Verification and merging layer: Configure a dual verification submodule and a data merging submodule to perform uniqueness verification, timeliness verification, heterogeneous data table merging, and calculation of core and extended indicators of power grid data, respectively.

[0025] The automated execution layer is developed based on VBA scripts. It configures data source reading scripts, formula execution scripts, data filling scripts, and weekly report filling scripts, which respectively perform automatic data source reading, automatic function and formula execution, automatic filling of unmatched data, and automatic filling of power grid weekly reports.

[0026] Output layer: Receives the processing results from the automated execution layer, outputs the call interface composite table, application support data composite table, and application call data composite table, and generates a standardized power grid weekly report to provide data output results to the power grid business end.

[0027] Unlike existing technologies, the above technical solution uses Excel and VBA to perform power grid data analysis and processing. It sequentially executes the following steps: data source preparation, mapping table establishment, function optimization, double verification, data merging and indicator calculation, automated execution, and result output. First, it obtains four types of heterogeneous data tables from the unified power grid dispatching component and the power grid resource business platform, and clarifies field specifications. Then, it establishes a mapping table between business departments and application names. Next, it uses the CONCATENATE function to concatenate composite associative keys as lookup values ​​for the VLOOKUP function to achieve multi-data source, multi-condition matching. It also uses multi-level nested IFERROR functions to return corresponding text prompts for different types of errors. Then, it performs double verification on the processed data. Based on the verification results, it merges heterogeneous data tables and calculates core and extended indicators of power grid data using nested Excel functions. Finally, it uses VBA scripts to drive batch execution of Excel function formulas to complete automated processing. Finally, it outputs a composite table of three types of data and generates a power grid weekly report. This technical solution enables accurate matching of multiple data sources and conditions in the power grid, accurately identifies and prompts different abnormal errors in the data processing process, effectively merges multiple heterogeneous power grid data tables and calculates related indicators, and automates power grid data processing and rapidly generates weekly reports by relying on VBA scripts. It solves the technical problems of inaccurate correlation of heterogeneous power grid data and inability to locate errors, and significantly improves the accuracy and overall efficiency of power grid data processing.

[0028] The above description of the invention is merely an overview of the technical solution of this application. In order to enable those skilled in the art to better understand the technical solution of this application and to implement it based on the description and drawings, and to make the above-mentioned objectives and other objectives, features and advantages of this application easier to understand, the following description is provided in conjunction with the specific embodiments and drawings of this application. Attached Figure Description

[0029] The accompanying drawings are only used to illustrate the principles, implementation methods, applications, features, and effects of specific embodiments of the present invention and other related contents, and should not be considered as limitations on this application.

[0030] In the accompanying drawings of the instruction manual:

[0031] Figure 1 A flowchart illustrating the power grid data analysis and processing method based on Excel and VBA as described in the specific implementation method;

[0032] Figure 2 The technical architecture diagram of the power grid data analysis and processing method based on Excel and VBA described in the specific implementation method;

[0033] Figure 3 This is a schematic diagram of the three-layer error handling mechanism described in the specific implementation method;

[0034] Figure 4 This is a flowchart illustrating the automated execution process of the power grid data analysis and processing method based on Excel and VBA, as described in the specific implementation method. Detailed Implementation

[0035] To illustrate the possible application scenarios, technical principles, implementable specific solutions, and achievable objectives and effects of this application in detail, the following description, in conjunction with the listed specific embodiments and accompanying drawings, provides a detailed explanation. The embodiments described herein are merely illustrative of the technical solutions of this application and are therefore intended to limit the scope of protection of this application.

[0036] In this document, the term "embodiment" means that a specific feature, structure, or characteristic described in connection with an embodiment may be included in at least one embodiment of this application. The term "embodiment" appearing in various places throughout the specification does not necessarily refer to the same embodiment, nor does it specifically limit its independence or connection with other embodiments. In principle, in this application, as long as there are no technical contradictions or conflicts, the technical features mentioned in each embodiment can be combined in any way to form corresponding implementable technical solutions.

[0037] Unless otherwise defined, the technical terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application pertains; the use of related terms herein is merely for the purpose of describing particular embodiments and is not intended to limit this application.

[0038] In the description of this application, the term "and / or" is used to describe the logical relationship between objects, indicating that three relationships can exist. For example, A and / or B means: A exists, B exists, and A and B exist simultaneously. Additionally, the character " / " in this document generally indicates that the preceding and following objects have an "or" logical relationship.

[0039] In this application, terms such as “first” and “second” are used only to distinguish one entity or operation from another, and do not necessarily require or imply any actual quantity, hierarchy or order relationship between these entities or operations.

[0040] Without further limitations, the use of terms such as “comprising,” “including,” “having,” or other similar open-ended expressions in this application is intended to cover non-exclusive inclusion, which does not exclude the presence of additional elements in a process, method, or product that includes the stated elements, such that a process, method, or product that includes a list of elements may include not only those defined elements but also other elements not expressly listed, or elements inherent to such a process, method, or product.

[0041] As understood in the Examination Guidelines, in this application, expressions such as "greater than," "less than," and "exceeding" are understood to exclude the stated number; expressions such as "above," "below," and "within" are understood to include the stated number. Furthermore, in the description of the embodiments in this application, "multiple" means two or more (including two), and similar expressions related to "multiple" are also understood in this way, such as "multiple groups" and "multiple times," unless otherwise explicitly specified.

[0042] In the description of the embodiments of this application, the space-related expressions used, such as "center," "longitudinal," "lateral," "length," "width," "thickness," "upper," "lower," "front," "rear," "left," "right," "vertical," "horizontal," "vertical," "top," "bottom," "inner," "outer," "clockwise," "counterclockwise," "axial," "radial," and "circumferential," indicate the orientation or positional relationship based on the orientation or positional relationship shown in the specific embodiments or drawings. They are only for the purpose of describing the specific embodiments of this application or for the reader's understanding, and do not indicate or imply that the device or component referred to must have a specific position, a specific orientation, or be constructed or operated in a specific orientation. Therefore, they should not be construed as limitations on the embodiments of this application.

[0043] Unless otherwise expressly specified or limited, the terms "installation," "connection," "linking," "fixing," and "setting," as used in the description of the embodiments of this application, should be interpreted broadly. For example, "connection" can be a fixed connection, a detachable connection, or an integral setting; it can be a mechanical connection, an electrical connection, or a communication connection; it can be a direct connection or an indirect connection through an intermediate medium; it can be the internal connection of two components or the interaction between two components. For those skilled in the art to which this application pertains, the specific meaning of the above terms in the embodiments of this application can be understood according to the specific circumstances.

[0044] This embodiment provides a power grid data analysis and processing method based on Excel and VBA. This method is applicable to heterogeneous power grid data analysis, particularly for the analysis and processing of various heterogeneous power grid data in power grid resource business platforms and high-frequency reporting scenarios in power grid weekly reports. These scenarios require the integration of multiple heterogeneous data tables, such as interface statistics tables, interface call status tables, application support data details, and application call status tables. These data tables have numerous fields, complex relationships, and high data standardization requirements. In the prior art, those skilled in the art generally believe that such complex power grid data processing needs must be implemented using customized processing software developed through professional programming languages ​​such as Java. However, customized software suffers from long development cycles, high technical barriers, and high maintenance costs. Power grid business, operation, and maintenance personnel cannot operate it independently. Furthermore, the combination of basic Excel functions and manual operation is limited by functional limitations and cannot meet the complex data processing requirements. This invention breaks through the industry's conventional wisdom by innovatively employing a collaborative approach between native Excel functions and VBA scripts to process complex power grid data. This is a technical solution that is not easily conceived by those skilled in the art, effectively resolving the technical contradiction between the high barrier to entry for professional programming solutions and the insufficient functionality of basic Excel solutions in existing technologies. It balances low operational barriers with the ability to handle complex business processes. The invention will be further described in detail below with reference to two specific embodiments. The scope of protection of this invention is not limited to the following embodiments; conventional modifications based on the technical solutions of this invention by those skilled in the art all fall within the scope of protection of this invention.

[0045] Please see Figure 1 and Figure 2 The power grid data analysis and processing method based on Excel and VBA is implemented through the collaboration of native Excel functions and VBA scripts. It completes data processing and automatic generation of weekly power grid reports for various heterogeneous data tables in the power grid resource business platform, including the following steps:

[0046] S101. Data source preparation: Obtain various heterogeneous data tables to be analyzed from the unified power grid dispatching component and the power grid resource business platform, and clarify the field specifications of each data table;

[0047] S102. Mapping Table Establishment: Establish a business department mapping table and an application name mapping table. The business department mapping table configures the professional mapping relationship between the application of the unified power grid dispatch component and the competent business department and the power grid resource business middle platform operation and management platform. The application name mapping table configures the full mapping relationship between the application name of the unified power grid dispatch component and the application name of the power grid resource business middle platform operation and management platform.

[0048] S103, Function optimization processing, including:

[0049] Composite Association Key Matching: The application name and interface identifier, and the application name and interface address are concatenated using the Excel CONCATENATE function to obtain composite association keys. These composite association keys are then used as the lookup value for the VLOOKUP function. Combined with the business department mapping table and the application name mapping table, a VLOOKUP lookup operation is performed to achieve multi-condition matching across multiple data sources. Refined Error Handling: Error message rules are set through multi-level nesting of the IFERROR function, returning corresponding text messages for various errors returned by the VLOOKUP function.

[0050] S104, Dual Validation: Perform uniqueness and timeliness checks on the data source after function optimization, provide prompts or filter data with abnormal validation, and perform subsequent processing on data with normal validation.

[0051] S105. Data Merging and Indicator Calculation: Based on the results of dual verification, the heterogeneous data tables are merged, and the core and extended indicators of the power grid data are calculated through nested Excel functions.

[0052] S106. Automated Execution: Drives Excel function formulas to execute in batches via VBA scripts, automating data source reading, formula calculation, and filling in unmatched data.

[0053] S107. Output Results: Output the interface call synthesis table, application support data synthesis table, and application call data synthesis table, and generate the power grid weekly report.

[0054] In this embodiment, step S101 involves preparing four types of heterogeneous data tables from the unified power grid scheduling component and the power grid resource business platform: interface statistics table, interface call status table, application support status data details, and application interface call status table. The field specifications for each data table are clearly defined. Step S101 obtains these four types of heterogeneous data tables from two core sources and clarifies the field specifications for each data table to ensure the completeness and standardization of data input. The heterogeneous data tables include: interface statistics table, interface call status table, application support status data details, and application interface call status table. The interface statistics table (AT column) contains: application province, appID, application name, gateway instance IP, microservice province, application-managing business department, microservice English name, microservice Chinese name, microservice-managing business department, interface name, interface address, number of failed interface calls, number of timeouts, number of circuit breakers, number of rate limiting events, request method, number of requests, statistical time, most recent request time, and average response time (ms). Interface Call Status Table (AO column): Serial Number, Domain, Subdomain, Service, Interface Name, Interface Address, Number of Calls (times), Number of Successes (times), Success Rate, Number of Failures (times), Failure Rate, Number of Unauthorized Accesses (times), Average Duration (ms), Number of Timeouts (times), Timeout Rate. Application Support Status Data Details (AL column): Serial Number, Application ID, Application Name, Application Code, Domain, Subdomain, Service, Interface Name, Interface Address, Interface Code, Number of Calls (times), Number of Failures (times). Application Interface Call Status Table (AH column): Serial Number, Application Name, Application Code, Specialty, Number of Authentication Services (units), Number of Called Services (units), Service Call Rate, Total Number of Service Calls (times).

[0055] In step S102, the mapping tables are established as a business department mapping table and an application name mapping table. The business department mapping table configures the professional mapping relationship between the application of the unified power grid dispatch component and the competent business department and the power grid resource business middle platform operation and management platform, ensuring the unified association of the business department field. The application name mapping table configures the full mapping relationship between the application name of the unified power grid dispatch component and the application name of the power grid resource business middle platform operation and management platform, solving the problem of inconsistent application names.

[0056] In step S103, the function optimization process includes composite key matching and refined error handling. Composite key matching involves using Excel's CONCATENATE function to concatenate the application name and interface identifier, and the application name and interface address, to obtain a composite key. This composite key is then used as the lookup value for the VLOOKUP function. A VLOOKUP lookup operation is performed using the business department mapping table and the application name mapping table to achieve multi-condition matching across multiple data sources. Refined error handling involves setting error message rules through multi-level nesting of the IFERROR function. Corresponding textual prompts are returned for #N / A errors, #VALUE errors, and multiple matching result errors returned by the VLOOKUP function. For multiple matching result errors, a data redundancy verification prompt is returned; and all prompts include the specific verification object and processing direction.

[0057] In step S104, the dual verification involves performing uniqueness and timeliness checks on the data source after function optimization. The uniqueness check uses Excel's COUNTIFS function to count and verify the composite related keys. If the return value of the COUNTIFS function is greater than 1, the IFERROR function returns a data redundancy warning. The timeliness check uses Excel's DATEDIF function to calculate the difference between the statistical timestamps of the data in the four types of heterogeneous data tables. A preset time threshold is used. If the difference exceeds the time threshold, a timestamp mismatch warning is returned, and the corresponding data is filtered. Finally, data with no abnormal warnings in both uniqueness and timeliness checks proceeds directly to subsequent processing. Data with abnormal warnings is corrected before proceeding to subsequent processing.

[0058] In this embodiment, before processing power grid data, data source preparation is first completed. Interface statistics data table is obtained from the unified power grid scheduling component, and call interface status table, application support status data details, and application call interface status table are synchronously obtained from the power grid resource business platform. All four types of heterogeneous data tables follow the unified power grid resource coding rules. The interface statistics data table contains twenty fields, including the province of the application, appID, application name, interface name, interface address, statistical time, and average response time. The call interface status table contains fifteen fields, including the domain, interface name, number of calls, success rate, and timeout rate. The application support status data details contain twelve fields, including the application ID, application code, interface code, and number of calls. The application call interface status table contains eight fields, including the application code, profession, number of authentication services, and service call rate. The field definitions and data formats of each data table are clearly defined to ensure the integrity and standardization of the original data and provide a standard input source for subsequent data processing. After the data source preparation is completed, two types of mapping tables are established to solve the problems of inconsistent fields and mismatched names from different data sources. The business department mapping table establishes a full mapping relationship between the "application-supervising business department" of the power grid unified dispatch component and the "professional" of the power grid resource business middle platform operation and management platform. The application name mapping table establishes a full mapping relationship between the "application name" of the above two platforms, providing a unified field association rule for function matching and avoiding matching errors caused by differences in field names or formats.

[0059] In the function optimization process, precise association of multiple data sources is first achieved through composite association key matching. Considering the complex association characteristics of power grid data, such as "one interface corresponding to multiple applications" and "one application corresponding to multiple interfaces," the CONCATENATE string concatenation function is used to concatenate the application name and interface identifier, and the application name and interface address, respectively, into composite association keys. These composite association keys are then used as the lookup value for the VLOOKUP function. Simultaneously, the VLOOKUP lookup logic is optimized by combining the established business department mapping table and application name mapping table, achieving precise multi-condition matching across multiple data sources. This overcomes the limitations of single-condition lookups in basic Excel functions and ensures that the association logic is compatible with the unified resource coding rules of the power grid. Furthermore, refined error handling enables precise identification and location of data anomalies. Common error types in power grid data processing are combined with multi-layered nesting of the IFERROR function, setting targeted error message rules. For #N / A errors returned by the VLOOKUP function, a verification message indicating an abnormal associative key is returned; for #VALUE errors, a message indicating data format standardization processing is returned; and for multiple matching results errors caused by non-unique associative keys, a verification message indicating data redundancy is returned. All messages clearly specify the verification object and processing direction, achieving a triple function of error masking, anomaly indication, and cause location. This solves the problem that traditional IFERROR functions can only mask error values ​​but cannot locate the cause, allowing power grid personnel to directly troubleshoot data anomalies based on the prompts without requiring specialized programming knowledge.

[0060] After function optimization, a dual verification is performed on the processed data source, forming a closed-loop processing sequence of "association-verification-correction-merging". First, a uniqueness verification is performed, using the application name-interface address composite association key as the core verification object and the full data of four types of heterogeneous data tables as the verification scope. The COUNTIFS conditional counting function is used to count and verify the composite association key. If the count result is greater than 1, it indicates data redundancy. The IFERROR function returns the corresponding redundancy warning information, reminding staff to check the data source and correct it, thus avoiding data redundancy problems caused by one-to-many associations. Next, a timeliness check is performed. The DATEDIF function is used to calculate the difference in the statistical timestamps of the data in the four heterogeneous data tables, with the calculation unit being seconds. A time threshold corresponding to the power grid data statistical period is preset. If the timestamp difference exceeds the threshold, it indicates that the data are from different statistical periods. A timestamp mismatch prompt is returned, and invalid data of this type is automatically filtered out to ensure that the data participating in the subsequent merging is valid data from the same statistical period. After completing the uniqueness and timeliness checks, data with no abnormal prompts in both checks are directly transferred to the subsequent data merging and indicator calculation steps. For data with abnormal prompts in either check, staff first correct the data according to the prompts, and then re-execute the check. Only after the check passes can the subsequent steps be entered. Through dual checks, the uniqueness and timeliness of power grid data are guaranteed from the data source, improving the standardization of data merging and meeting the high accuracy requirements of power grid data.

[0061] After completing the dual verification, the following steps are executed in sequence: S105 Data Merging and Index Calculation, S106 Automated Execution, and S107 Result Output.

[0062] Data merging and metric calculation are based on any one of the following tables: Interface Statistics Table, Interface Call Status Table, Application Support Status Data Details Table, and Application Interface Call Status Table. A corresponding composite table is created, and the basic fields of the composite table are initialized using the field specifications of the base table. Nested SUMIF, INDEX-MATCH, and IFERROR functions are used to accumulate, weight, and perform conditional statistics on data from multiple data sources in the same dimension. This enables the calculation of core metrics such as call count, success rate, failure rate, average response time, and timeout rate. Conditional statistics are performed using COUNTIF and COUNTIFS functions to achieve extended metric statistics such as the number of service average response times exceeding limits and the number of failed interface calls. Finally, the field fusion and data merging of the four types of heterogeneous data tables are completed, adapting to the requirement of merging heterogeneous data tables with up to 20 fields per table. The calculation logic for the core metrics is as follows: Success rate is calculated based on the merged number of calls and the number of failures, with an IF function added to verify the non-zero call count and prevent division errors; average duration is the weighted average of the metrics from multiple data sources, with the COUNTIF function used to count the amount of data involved in the calculation for weighted operation; timeout rate is calculated based on the merged number of calls and the number of timeouts, with an IF function added to verify the non-zero call count and prevent division errors; and all core metric calculations nest the IFERROR function, returning a preset value of 0 in case of an exception. The specific steps of data merging and metric calculation include:

[0063] Synthetic Table Creation: Based on any one of the following tables—Interface Statistics Table, Interface Call Status Table, Application Support Status Data Details Table, and Application Interface Call Status Table—a corresponding synthetic table is created, and the basic fields of the synthetic table are initialized using the field specifications of the base table. Core Metric Calculation: Through nested SUMIF, INDEX-MATCH, and IFERROR functions, the system performs cumulative, weighted calculations, and conditional statistics on data from multiple data sources in the same dimension, calculating call count, success rate, failure rate, average duration, and timeout rate. Extended Metric Statistics: Through COUNTIF and COUNTIFS functions, the system performs conditional statistics on the number of service average response times exceeding the limit and the number of failed interface calls. Multi-Table Merging: The system merges fields and data from four types of heterogeneous data tables, adapting to the merging requirements of heterogeneous data tables with up to 20 fields per table.

[0064] In step S105, the concepts of "logic decomposition, conditional judgment, and loop verification" from program development are integrated into the Excel formula design. Complex nested formulas replace professional code, and combined with a double verification mechanism and VBA scripts, step S105 consists of three sub-steps: sub-steps 3.1 to 3.3, which complete data merging and indicator calculation.

[0065] Sub-step 3.1: Expanding and matching fields in the interface statistics table

[0066] Four new matching fields were added to the interface statistics table. Relationships with other data sources were established using nested IFERROR function formulas, achieving the transformation from "code logic" to "formula logic".

[0067] 1. Application Name Field in the Operations Management Platform: Formula "=IFERROR(INDEX(Application Name Mapping Table B:B,MATCH(Interface Statistics Data C2,Application Name Mapping Table A:A,0)),"Application name matching failed, please check the mapping table")". Logic: Borrowing from the "key-value pair matching" logic in the program, the INDEX-MATCH function (replacing the regular VLOOKUP to improve matching flexibility) combined with IFERROR is used to achieve automatic application name matching. When matching fails, a specific error message is returned, replacing the exception handling logic of try-catch in Java.

[0068] 2. Interface Address Matching Field: Formula "=IFERROR(COUNTIF(call interface status F:F,K2),0)". Logic: The COUNTIF function counts the number of interface address matches, simulating an "existence check" in the program. IFERROR ensures that a match fails and returns 0, preventing erroneous values ​​from affecting subsequent calculations.

[0069] 3. Application Name Matching Field: Formula "=IFERROR(COUNTIF(Application Support Status Data Details C:C,U2),0)". Logic: Counts the number of times the application name matches on the operation and management platform to further verify data consistency.

[0070] 4. Application Name and Interface Address Matching Field: Formula "=IFERROR(COUNTIFS(Application Support Status Data Details C:C,U2,Application Support Status Data Details I:I,K2),0)". Logic: The COUNTIFS function implements "multi-condition joint verification," simulating the "composite primary key matching" logic in the program, thus improving the accuracy of the association.

[0071] Sub-step 3.2: API call synthesis and core indicator calculation

[0072] Based on the API call status table, an API call synthesis table is established. Data is merged and metrics are calculated using function formulas. Combined with VBA scripts to populate unmatched data, a fully formulaic replacement of the entire process of "data merging → metric calculation → exception handling" is achieved.

[0073] 1. Initialize the basic fields of the composite table: Following the AO column field specifications of the API call status table, copy the basic field data to the composite table.

[0074] 2. Application of data merging formulas (nested core functions, replacing specialized code logic):

[0075] 1) Number of calls (times): "=IFERROR(call status G2+SUMIF(interface statistics K:K,F2,interface statistics Q:Q),call status G2)". Logic: Simulates the "data accumulation" logic in the program, accumulating the number of calls to the two types of data tables. IFERROR ensures that the original data is retained when there is no matching data, replacing the "not null check + accumulation calculation" code block in Java.

[0076] 2) Success Count (times): "=IFERROR(G3-J3,0)". Logic: Calculates the success count based on the merged call count and failure count. Returns 0 on failure, simulating the "arithmetic operation + exception fallback" logic in the program.

[0077] 3) Success Rate: "=IFERROR(IF(G2<>0,H2 / G2,0),0)". Logic: The IF statement prevents division errors when the number of calls is 0. IFERROR ensures that 0 is returned in case of an exception, replacing the "divisor non-zero check + division operation" logic in the program.

[0078] 4) Number of failures: "=IFERROR(Call status J2+SUMIF(Interface statistics K:K,F2,Interface statistics L:L),Call status J2)". Logic: Accumulates the number of failures for both types of data tables, simulating data fusion logic.

[0079] 5) Failure rate: "=IFERROR(IF(G2<>0,J2 / G2,0),0)". Logic: Calculates the failure rate to avoid division errors.

[0080] 6) Number of times an exception occurs: "=IFERROR(call status L2,0)". Logic: Directly references the original data; returns 0 in case of an exception.

[0081] 7) Average Duration (ms): "=IFERROR((Call Status M3+SUMIF(Interface Statistics K:K,F3,Interface Statistics T:T)) / (COUNTIF(Interface Statistics K:K,F3)+1),Call Status M3)". Logic: Calculates the weighted average duration, simulating the "weighted calculation" logic in the program to ensure data validity.

[0082] 8) Timeout Count (times): "=IFERROR(Call Status N2+SUMIF(Interface Statistics K:K,F2,Interface Statistics M:M),Call Status N2)". Logic: Accumulates the number of timeouts.

[0083] 9) Timeout Rate: "=IFERROR(IF(G4<>0,N4 / G4,0),0)". Logic: Calculates the timeout rate to avoid division errors.

[0084] 3. Automatic filling of unmatched data (VBA collaboration, compensating for the automation shortcomings of Excel formulas):

[0085] 1) Logic: For records with an interface address matching value of 0, the VBA script automatically extracts the unique interface name, fills it at the end of the composite table, and generates a serial number, simulating the "loop traversal + data insertion" logic in the program.

[0086] 2) Field matching formula: Field "=IFERROR(INDEX(Interface statistics H:H,MATCH(F335,Interface statistics K:K,0)),"Unmatched field information")", Interface name "=IFERROR(INDEX(Interface statistics J:J,MATCH(F335,Interface statistics K:K,0)),"Unmatched interface name")", and other indicators follow the above core formula.

[0087] 4. Calculation of extended indicators:

[0088] 1) Total number of service calls: "=IFERROR(SUM(call interface composition G:G),0)".

[0089] 2) The name and percentage of the service interface with the highest call volume: Name "=IFERROR(INDEX(call interface composition E:E,MATCH(MAX(call interface composition G:G),call interface composition G:G,0)),"No matching data")", Percentage "=IFERROR(LARGE(call interface composition G:G,1) / C2,0)".

[0090] 3) The name and percentage of the highest number of central calls: Name "=IFERROR(INDEX(Export Count_Domain A:A,MATCH(LARGE(Export Count_Domain B:B,1),Export Count_Domain B:B,0)),"No matching data")", Percentage "=IFERROR(LARGE(Export Count_Domain B:B,1) / C2,0)".

[0091] 4) Average response time: "=IFERROR(SUMIFS(call interface composition M:M,call interface composition G:G,">0") / COUNTIF(call interface composition G:G,">0"),0)".

[0092] 5) Other metrics (number of service average response times greater than 2 seconds / 5 seconds, number of failed API calls, etc.): These are calculated using the nested IFERROR COUNTIF and COUNTIFS functions, replacing the "conditional filtering + statistical counting" logic in the program.

[0093] Sub-step 3.3: Synthesis of application support data and application call data, and calculation of indicators.

[0094] Using the same logic as sub-step 3.2, the complex business logic in program development is transformed into nested Excel formulas, combining VLOOKUP nested IFERROR functions and VBA scripts:

[0095] Application support data synthesis: merge application support data details and mapping tables, and calculate core indicators (total number of applications supported by the middle platform service, number of applications in each professional field, and the top three services in terms of the number of supported applications, etc.).

[0096] Application call data synthesis: merge the application call interface information table with the business department mapping table, and calculate core indicators (number of applications in each professional field, number of applications in this field / across professional fields, and ranking of call counts, etc.).

[0097] S106 automated execution uses VBA scripts to drive the batch execution of Excel function formulas. The VBA script first automatically reads four types of heterogeneous data tables from the unified power grid dispatch component and the power grid resource business platform according to a preset file path. Then, it automatically triggers the batch execution of field expansion formulas for the interface statistical data table, data merging formulas for each composite table, and indicator calculation formulas. Next, it identifies unmatched data records with a matching value of 0, extracts the unique identifier information from the records, completes data filling at the end of the composite table, and generates consecutive serial numbers. Simultaneously, it triggers matching formulas to perform indicator calculations on the filled data. Finally, it locates the cell coordinates corresponding to the power grid data indicators in each composite table, reads the indicator values, and accurately fills the indicator values ​​into the corresponding cells of the weekly report according to the preset field specifications of the power grid weekly report template. Furthermore, when automatically filling in the weekly report, if empty or non-numerical abnormal indicator data is detected, a data error message is returned, and the error value is masked using the IFERROR function. The VBA script supports reading data tables in Excel's native .xlsx and .xls formats without requiring manual import or format conversion.

[0098] The output of S107 is an output call interface composite table, an application support data composite table, and an application call data composite table, and generates a power grid weekly report. This method is suitable for the high-frequency reporting scenario of the power grid resource business platform. When business requirements change, only the Excel function formulas or the association rules of the mapping table need to be adjusted, without modifying the core logic of the VBA script.

[0099] In this embodiment, data merging and indicator calculation are performed based on valid data after double verification. This step addresses the business requirements of the power grid resource business platform, enabling complex merging of multiple data sources and fields, and automated indicator calculation. First, three types of composite tables are established: a composite table based on the interface call status table, an application support data composite table based on the application support data details table, and an application call data composite table based on the application interface call status table. The basic fields of all composite tables are initialized using the field specifications of their respective base tables, ensuring that the field specifications of the composite tables are consistent with the power grid business requirements. Subsequently, core indicator calculations are performed. The SUMIF function is used to accumulate data from multiple data sources in the same dimension. For example, the number of calls, failures, and timeouts are all accumulated using the SUMIF function, with the IFERROR function nested to ensure data validity. The INDEX-MATCH function is used to achieve precise data matching and extraction, providing data support for indicator calculations, particularly for ratio-based indicators such as success rate, failure rate, and timeout rate. By adding a non-zero call count check using the IF function to avoid division errors, and nesting the IFERROR function to return a preset value when the indicator calculation is abnormal, this is particularly useful for weighted indicators such as average duration. The COUNTIF function is used to count the amount of data involved in the calculation, enabling the calculation of weighted averages for indicators from multiple data sources, accurately automating the calculation of core power grid indicators such as call count, success rate, failure rate, average duration, and timeout rate. Next, extended indicator statistics are performed. Different filtering rules are set using the COUNTIF and COUNTIFS functions to complete the statistics of extended indicators such as the number of services with average response time exceeding a preset threshold, the number of failed interface calls, service interface call volume ranking, and center call volume ranking, comprehensively covering the indicator requirements of power grid data processing. Finally, the field fusion and data merging of four types of heterogeneous data tables are completed. Related fields from different data tables are merged, while non-related fields are retained, achieving complex merging of four types of heterogeneous data tables with up to twenty fields per table, meeting the multi-data source merging requirements of the power grid resource business platform.

[0100] After configuring the relevant formulas for data merging and indicator calculation, the entire process is automated through VBA scripts, overcoming the limitations of static calculations using Excel functions and achieving an automated closed loop from data acquisition to weekly report submission. The VBA scripts are all developed based on the native Excel environment, requiring no additional deployment. First, they automatically read the data source, automatically reading four types of heterogeneous data tables in .xlsx and .xls formats according to preset file paths, eliminating the need for manual import or format conversion and improving data acquisition efficiency. Then, they automatically execute function formulas, triggering batch execution of field expansion formulas for the interface statistics table, data merging formulas for each composite table, and indicator calculation formulas, eliminating the need for manual input or formula triggering and avoiding errors caused by manual operation. Next, they automatically fill in unmatched data. The script automatically identifies unmatched data records with a matching value of 0, such as interface address and application name, extracts unique identifiers such as interface name and address from the records, fills in the data at the end of the corresponding composite table, and generates consecutive serial numbers. Simultaneously, it automatically triggers matching formulas to perform indicator calculations on the filled unmatched data, achieving complete data completion. Finally, the script automatically fills in the weekly report. It accurately locates the cell coordinates corresponding to the core and extended indicators of the power grid in each composite table, automatically reads the indicator values, and accurately fills the indicator values ​​into the corresponding cells of the weekly report based on the field specifications of the preset power grid weekly report template. At the same time, the script has built-in error protection logic. If it detects abnormal situations such as empty indicator data or non-numerical data, it immediately returns a data error message and uses the IFERROR function to block the error value, thus preventing the error value from appearing in the weekly report and ensuring the standardization of the weekly report.

[0101] After automated execution, the system outputs three standardized data composite tables: an interface call composite table, an application support data composite table, and an application call data composite table. Each table contains the complete merged data and automatically calculated indicators. Simultaneously, it generates standardized weekly power grid reports conforming to power grid business specifications, providing accurate and standardized data analysis results and reports for power grid business, operations, and maintenance personnel. If subsequent power grid business requirements change, only the calculation logic of Excel function formulas or the field association rules of mapping tables need to be adjusted accordingly; the core logic of the VBA script does not need to be modified. This significantly reduces the adaptation cost for requirement changes, improves the flexibility and adaptability of the solution, and allows power grid personnel to make adjustments independently without relying on professional developers.

[0102] In step S103 of this embodiment, the adaptation and optimization of composite association keys overcomes the limitations of conventional single-condition lookups in Excel. Composite association keys such as "application name-interface identifier" and "application name-interface address" are designed, and the CONCATENATE string concatenation function (e.g., "=CONCATENATE(C2,K2)") is embedded in the "lookup value" parameter of the VLOOKUP function. Combined with the IFERROR function, accurate matching of composite association keys is achieved. By combining the business department mapping table and the application name mapping table, the VLOOKUP lookup logic is optimized, addressing pain points such as "the same interface corresponds to multiple applications," "the same application corresponds to multiple interfaces," and "inconsistent application names." As shown in Table 1 below, the composite association keys ensure complete compatibility with the unified resource coding rules of the power grid, significantly improving the accuracy of association.

[0103]

[0104] Table 1: Formulaic Transformation Table of Program Logic

[0105] like Figure 3 As shown, in this embodiment, the logic of "anomaly classification and capture" in program development is integrated into the application of the IFERROR function, breaking through the limitations of conventional error masking; the nested logic of the IFERROR function is optimized, and multiple types of error prompts related to power grid data are preset to accurately distinguish error types and causes: when VLOOKUP returns a #N / A error (association key does not exist): it returns "association key error, please check the unified resource code and mapping table"; when it returns a #VALUE error (data format error): it returns "data format mismatch, please standardize the interface address, application name and other fields"; when it returns a multiple matching result error (association key is not unique): it returns "association key is not unique, there is data redundancy, please check the data source"; thus, it realizes the triple function of "error masking + anomaly prompt + cause location", which significantly improves the efficiency of anomaly investigation compared with conventional technology, replacing the "multi-type anomaly capture" code in the program.

[0106] Furthermore, this embodiment draws on the design concept of a "data validation layer" in program development, embedding dual validation logic for uniqueness and timeliness into Excel formulas to ensure data standardization. Specifically, it calls Excel's data deduplication function (combined with the COUNTIFS function) within nested function logic to verify the uniqueness of the composite related key. If redundancy is detected (COUNTIFS result > 1), an exception message is returned via IFERROR to avoid data redundancy, replacing the "primary key uniqueness validation" code in the program. It also embeds a DATEDIF time difference calculation formula (such as "=DATEDIF(interface statistics data R2, interface call statistics time column,"s")) to automatically calculate the timestamp difference between the two types of data, ensuring that the difference is ≤ 1 minute (within the same statistical period). If the difference exceeds the limit, an exception message is returned and invalid data is filtered out, replacing the "timestamp consistency validation" code in the program. This forms a closed loop of "association-validation-correction-merging," reducing the data redundancy rate to below 0.3% and the timestamp mismatch rate to 0.

[0107] This embodiment also enables automation through the collaboration of VBA scripts and functions. Combining the "automated workflow design" approach of program development, it uses VBA scripts to drive the execution of Excel formulas, achieving full-process automation. The deep collaboration between VBA scripts and optimized function formulas replaces the "automated task scheduling" function of professional programs, significantly shortening the processing time for a single weekly report, noticeably improving efficiency, and correspondingly reducing the error rate of manual operation. For example... Figure 4 As shown, it specifically includes:

[0108] 1. Automatically read data sources: VBA scripts automatically read four types of heterogeneous data tables via file paths, eliminating the need for manual import.

[0109] 2. Automatic function execution: The script automatically triggers the field expansion formula of the interface statistical data table, the data merging formula of the composite table, and the indicator calculation formula, without the need for manual input.

[0110] 3. Automatically fill in unmatched data: The script identifies unmatched records, automatically fills in the data, and executes the matching formula.

[0111] 4. Automatic weekly report filling: The script reads the composite table indicator data and automatically fills it into the weekly report template.

[0112] In another embodiment, a power grid data analysis and processing system based on Excel and VBA is provided. This system is deployed in Excel software and is implemented based on the power grid data analysis and processing method using Excel and VBA. It is suitable for multi-type heterogeneous power grid data analysis and processing in the power grid resource business platform and for high-frequency power grid weekly report filling scenarios. The system includes a data source layer, a mapping table layer, a function optimization layer, a verification and merging layer, an automated execution layer, and an output layer that sequentially perform unidirectional data transmission.

[0113] The data source layer establishes data connection with the unified power grid scheduling component and the power grid resource business platform, and acquires and stores four types of heterogeneous data tables: interface statistics table, interface call status table, application support status data details, and application interface call status table.

[0114] The mapping table layer stores and maintains the business department mapping table and the application name mapping table, and provides unified field association rules to the function optimization layer. The business department mapping table configures the professional mapping relationship between the application of the unified power grid dispatch component and the competent business department and the power grid resource business middle platform operation and management platform. The application name mapping table configures the full mapping relationship between the application name of the unified power grid dispatch component and the application name of the power grid resource business middle platform operation and management platform.

[0115] The function optimization layer is the core innovative module of the system, configured with a composite association key adaptation submodule and a refined error handling submodule. The composite association key adaptation submodule performs composite association key concatenation and VLOOKUP lookup operations. It uses Excel's CONCATENATE function to concatenate the application name and interface identifier, and the application name and interface address, respectively, to obtain the composite association key. The composite association key is used as the lookup value of the VLOOKUP function. Combined with the business department mapping table and the application name mapping table, the VLOOKUP lookup operation is performed to achieve multi-condition matching of multiple data sources. The refined error handling submodule performs multi-level nested error prompting operations using the IFERROR function. It returns corresponding text prompt information for #N / A errors, #VALUE errors, and multiple matching result errors returned by the VLOOKUP function.

[0116] The validation and merging layer is configured with a dual validation submodule and a data merging submodule. The dual validation submodule performs uniqueness and timeliness validation operations. It uses Excel's COUNTIFS function to count and validate composite related keys. If the return value of the COUNTIFS function is greater than 1, it returns a data redundancy warning message using the IFERROR function. It uses Excel's DATEDIF function to calculate the difference in the statistical timestamps of the data in the four types of heterogeneous data tables. A preset time threshold is used. If the difference exceeds the time threshold, a timestamp mismatch warning message is returned, and the corresponding data is filtered. Data with no abnormal warnings for uniqueness and timeliness validation is directly transmitted to the data merging submodule. Data with abnormal warnings is corrected before being transmitted to the data merging submodule.

[0117] After the system starts, the data source layer first handles data input. As the system's original data input source, this layer establishes stable data connections with the unified power grid dispatch component and the power grid resource business platform. It directly and synchronously obtains four types of heterogeneous data tables from these two platforms: interface statistics table, interface call status table, application support data details, and application interface call status table. All types of data tables are stored uniformly. Simultaneously, the field specifications for each data table are clearly defined, and raw data that does not meet the specifications is initially filtered to ensure that the stored raw data meets the standard requirements for subsequent system processing. This provides complete and standardized input data for all data processing stages of the system. After completing data processing, the data source layer transmits the standardized raw data tables upwards to the mapping table layer.

[0118] After receiving the raw data tables from the data source layer, the mapping table layer stores and maintains manually established business department mapping tables and application name mapping tables based on the unified resource coding rules of the power grid. The business department mapping table implements a full mapping between the "application-supervising business department" of the unified power grid dispatch component and the "professional" of the power grid resource business middleware operation and management platform. The application name mapping table implements a full mapping between the "application names" of the two platforms. This layer transforms the two types of mapping tables into unified field association rules that the system can recognize, providing a foundation for multi-data source matching in the function optimization layer. This solves the problem of inconsistent field names and formats between different data sources, avoiding matching errors caused by field differences. After completing the association rule configuration, the mapping table layer unidirectionally transmits the raw data tables and the unified field association rules to the function optimization layer.

[0119] As the core innovative module of the system, the function optimization layer receives the original data table and field association rules transmitted from the mapping table layer. The composite association key adaptation submodule and the refined error handling submodule then collaboratively complete the function optimization process. First, the composite association key adaptation submodule performs a composite association key matching operation. Considering the complex association characteristics of power grid data, it uses the CONCATENATE string concatenation function to concatenate the application name and interface identifier, and the application name and interface address, respectively, into a composite association key. This composite association key is then used as the lookup value for the VLOOKUP function. Simultaneously, it optimizes the VLOOKUP lookup logic based on the field association rules, performing VLOOKUP lookup operations across multiple data sources. This achieves accurate matching of multiple data sources under multiple conditions, ensuring that the data association logic is adapted to the needs of power grid business. Subsequently, the refined error handling submodule performs refined error handling operations. Through multi-layer nested IFERROR functions, it sets error prompting rules adapted to power grid data processing. For #N / A errors, #VALUE errors, and multiple matching result errors returned by the VLOOKUP function, it returns text prompts containing specific verification objects and processing directions, realizing the triple functions of error masking, anomaly prompting, and cause location. This makes it convenient for power grid staff to directly check data anomalies based on the prompts. After the function optimization layer completes the processing, the processed data source is unidirectionally transmitted to the verification and merging layer.

[0120] After the data source of the merge layer's receiving function is optimized, the dual verification submodule first performs a dual verification operation. This submodule first uses the COUNTIFS conditional counting function, with the application name-interface address composite association key as the verification object and the full data of the four types of heterogeneous data tables as the verification range, to perform a uniqueness check. If the count result is greater than 1, a data redundancy warning is returned. Then, the DATEDIF function calculates the difference between the data statistical timestamps in seconds, and combines this with a preset power grid data statistical cycle time threshold for timeliness verification. If the difference exceeds the threshold, a timestamp mismatch warning is returned, and invalid data is automatically filtered. After the dual verification is completed, data without any abnormal warnings is directly transmitted to the data merging submodule of this layer. For data with abnormal warnings, staff are first reminded to correct the data according to the prompts. After correction, the verification is re-executed. Only after passing the verification is the data transmitted to the data merging submodule. This dual verification forms a closed loop in data processing, ensuring the uniqueness and timeliness of power grid data from the data source and improving the standardization of subsequent data merging.

[0121] The data merging submodule of the verification and merging layer performs heterogeneous data table merging and power grid data indicator calculation operations. It establishes a corresponding composite table based on any one of the following data tables: Interface Statistics Table, Interface Call Status Table, Application Support Status Data Details Table, and Application Interface Call Status Table. It initializes the basic fields of the composite table using the field specifications of the base data table. It calculates core indicators such as call count, success rate, failure rate, average response time, and timeout rate using nested SUMIF, INDEX-MATCH, and IFERROR functions. It uses COUNTIF and COUNTIFS functions to statistically analyze extended indicators such as the number of service average response times exceeding the limit and the number of failed interface calls. Finally, it completes the field fusion and data merging of four types of heterogeneous data tables, adapting to the requirement of merging heterogeneous data tables with up to 20 fields per table.

[0122] The automated execution layer is developed based on VBA scripts, configuring data source reading scripts, formula execution scripts, data filling scripts, and weekly report filling scripts to perform automatic data source reading, automatic function formula execution, automatic filling of unmatched data, and automatic filling of power grid weekly reports, respectively. The data source reading script automatically reads four types of heterogeneous data tables in native Excel formats (.xlsx and .xls) based on preset file paths. The formula execution script automatically triggers batch execution of field expansion formulas for interface statistical data tables, data merging formulas for various composite tables, and indicator calculation formulas. The data filling script identifies unmatched data records with a matching value of 0, extracts unique identifier information, automatically fills it at the end of the composite table, generates consecutive serial numbers, and triggers matching formulas to complete the indicator calculation for the filled data. The weekly report filling script locates the cell coordinates corresponding to power grid data indicators in each composite table, accurately reads the indicator values, and fills them into the corresponding cells of the preset power grid weekly report template. It also has built-in error protection logic; when abnormal indicator data is detected, it returns a data error message and uses the IFERROR function to suppress error values.

[0123] The output layer receives the processing results from the automated execution layer, outputs the call interface synthesis table, the application support data synthesis table, and the application call data synthesis table, and generates a standardized power grid weekly report to provide data output results to the power grid business end.

[0124] After the dual-verification submodule of the verification and merging layer completes data verification, the data merging submodule performs data merging and indicator calculation operations. Based on the business requirements of the power grid resource business platform, this submodule establishes a call interface composite table, an application support data composite table, and an application call data composite table, using corresponding tables from four types of heterogeneous data tables as benchmarks. All composite tables use the field specifications of the benchmark data tables to initialize the basic fields, ensuring consistency between the composite table fields and the power grid business requirements. Subsequently, through deep nesting of functions such as SUMIF, INDEX-MATCH, and IFERROR, it achieves the accumulation, weighted calculation, and conditional statistics of data from multiple data sources in the same dimension, accurately completing the automated calculation of core indicators such as call count, success rate, failure rate, average duration, and timeout rate. By setting conditional filtering rules using the COUNTIF and COUNTIFS functions, the system completes the statistics of extended indicators such as the number of service average response times exceeding the standard and the number of failed API calls. Finally, it completes the field fusion and data merging of four types of heterogeneous data tables, realizing the complex merging of heterogeneous data tables with up to twenty fields in a single table. After the data merging submodule completes the processing, it transmits the verified merged composite table and indicator data unidirectionally to the automated execution layer.

[0125] The automated execution layer, as the core layer for automated processing, receives the composite tables and indicator data transmitted from the verification and merging layer. Through the collaborative work of four configured VBA scripts, it achieves fully automated execution. First, the data source reading script automatically reads four types of heterogeneous data tables in .xlsx and .xls formats according to preset file paths, eliminating the need for manual import or format conversion and achieving automatic data source acquisition. Then, the formula execution script automatically triggers the batch execution of field expansion formulas for the interface statistical data tables, data merging formulas for each composite table, and indicator calculation formulas, all without manual operation, ensuring high efficiency and accuracy in formula execution. Next, the data filling script automatically identifies unmatched data records with a matching value of 0, extracts the unique identifier information from the records, automatically fills in the data at the end of the corresponding composite table and generates a continuous sequence number, and triggers the matching formula to perform indicator calculations on the filled data, realizing the automatic completion of unmatched data and ensuring data integrity. Finally, the weekly report filling script accurately locates the cell coordinates corresponding to the core and extended indicators of the power grid in each composite table, automatically reads the indicator values, and accurately fills the indicator values ​​into the corresponding cells of the weekly report in accordance with the field specifications of the preset power grid weekly report template. The script has built-in error protection logic. If abnormal situations such as empty indicator data or non-numerical data are detected, it immediately returns a data abnormality prompt message and uses the IFERROR function to shield the error value, ensuring the standardization of the weekly report filling. After the automated execution layer completes the fully automated processing, the processed composite table and power grid weekly report data are transmitted unidirectionally to the output layer.

[0126] As the system's output layer, the output layer receives the processing results transmitted from the automated execution layer and directly outputs three types of standardized data composite tables: the call interface composite table, the application support data composite table, and the application call data composite table. At the same time, it generates standardized power grid weekly reports that conform to power grid business specifications, providing accurate and standardized data analysis results and report outputs to power grid business, operation, and maintenance personnel. All types of output results can be directly viewed, edited, and exported in Excel, which aligns with the daily office habits of power grid staff.

[0127] This system is deployed entirely within Excel software, requiring no additional server resources or professional developers for maintenance. Power grid personnel can operate it independently without specialized programming knowledge. When subsequent power grid business needs change, only Excel function formulas or mapping table association rules need adjustment; the core logic of the VBA script remains unchanged. This significantly reduces system maintenance and adaptation costs for changing requirements. Furthermore, the system has a short deployment cycle, requiring only template configuration and script debugging before deployment. It can be rapidly promoted and applied throughout the power grid industry, effectively resolving the technical contradictions of existing technologies such as high barriers to entry and high maintenance costs for professional programming systems, and limited functionality of basic Excel solutions. It achieves a balance between low-barrier operation and complex business processing, as well as high-efficiency automation and flexible adaptation to changing needs.

[0128] Both embodiments of this invention are based on the collaboration of native Excel functions and VBA scripts. They fully utilize the advantages of Excel's low operating threshold and high industry popularity, combined with the automation capabilities of VBA scripts, to achieve accurate and efficient processing of complex power grid data. At the same time, the method and system of this invention can be flexibly adjusted according to the actual needs of power grid business, with strong adaptability. They comprehensively cover the data processing and weekly report filling needs of the power grid resource business platform, providing a low-cost, highly adaptable, and easy-to-operate innovative solution for the field of power grid data processing.

[0129] The two embodiments described above replace the core business logic of professional programming languages ​​such as Java with complex nested formulas in Excel. This avoids the problems of long development cycles, high maintenance costs, and poor adaptability of professional programs, and also breaks through the functional limitations of basic Excel functions. It provides an innovative solution for power grid data processing that is "low-cost, highly adaptable, and easy to operate". This idea is a unique innovation of the inventor, combining program development and Excel application experience. There is no similar technology in the industry.

[0130] Furthermore, through the adaptation of composite association keys and the fusion of mapping tables, the accuracy of data association is significantly improved. In particular, the accuracy of data association is significantly improved compared to existing Excel solutions, far exceeding the industry average.

[0131] The above embodiments improve the precision and maintainability of error handling, achieving a triple function of "error masking + anomaly prompting + cause location", which improves the efficiency of anomaly troubleshooting. Compared with the anomaly handling of professional programs, it is more in line with the operating habits of power grid business personnel, operation personnel, and maintenance personnel, and can locate problems without looking at the code.

[0132] The above embodiments balance processing efficiency and ease of operation: full automation reduces the processing time for a single weekly report to less than 10 minutes, significantly improving efficiency and greatly reducing the error rate of manual operation; simultaneously, based on Excel and VBA, business personnel, operations personnel, and maintenance personnel can operate independently without professional programming knowledge, without relying on a development team, greatly improving business response flexibility. Furthermore, no additional server resources are required, and no professional developers are needed for maintenance. Compared to Java development solutions, development and maintenance costs are reduced by more than 90%, and the deployment cycle is short (template configuration and script debugging can be completed in 1-2 days), allowing for rapid promotion and application.

[0133] The above embodiments can adapt to the complex merging requirements of 4 types of core heterogeneous data tables (a maximum of 20 fields per table), support the automatic calculation of 10+ types of core indicators and extended indicators, fully cover the data processing and weekly report filling requirements of the power grid resource business platform, and only need to adjust the formula or mapping table when business requirements change, with extremely low adaptation costs.

[0134] Finally, it should be noted that although the above embodiments have been described in the text and drawings of this application, this should not limit the scope of patent protection of this application. Any technical solutions that are based on the essential concept of this application and utilize the content described in the text and drawings of this application, resulting in equivalent structural or procedural substitutions or modifications, as well as the direct or indirect application of the technical solutions of the above embodiments to other related technical fields, are all included within the scope of patent protection of this application.

Claims

1. A method for power grid data analysis and processing based on Excel and VBA, characterized in that, Includes the following steps: Data source preparation: Obtain various heterogeneous data tables to be analyzed from the unified power grid dispatching component and the power grid resource business platform, and clarify the field specifications of each data table; Mapping table establishment: Establish a business department mapping table and an application name mapping table. The business department mapping table configures the professional mapping relationship between the application of the unified power grid dispatch component and the competent business department and the power grid resource business middle platform operation and management platform. The application name mapping table configures the full mapping relationship between the application name of the unified power grid dispatch component and the application name of the power grid resource business middle platform operation and management platform. Function optimization processing includes: Composite key matching: The application name and interface identifier, and the application name and interface address are concatenated separately using the Excel CONCATENATE function to obtain a composite key, which is then used as the lookup value for the VLOOKUP function. This is combined with the business department mapping table and the application name mapping table to perform a VLOOKUP lookup operation, enabling multi-condition matching across multiple data sources; and refined error handling: Error message rules are set through multi-level nesting of the IFERROR function, returning corresponding text messages for various errors returned by the VLOOKUP function. Double verification: Perform uniqueness and timeliness verification on the data source after function optimization, provide prompts or filter data with abnormal verification, and perform subsequent processing on data with normal verification. Data merging and indicator calculation: Based on the results of dual verification, the heterogeneous data tables are merged, and the core and extended indicators of power grid data are calculated by nested Excel functions. Automated execution: Batch execution of Excel functions and formulas is driven by VBA scripts to automate data source reading, formula calculation, and filling in unmatched data.

2. The power grid data analysis and processing method based on Excel and VBA according to claim 1, characterized in that, The heterogeneous data tables include: interface statistics table, interface call status table, application support status data details, and application interface call status table; The dual verification specifically includes: Uniqueness verification: The composite associative key is counted and verified using Excel's COUNTIFS function. If the return value of the COUNTIFS function is greater than 1, the data redundancy warning message is returned using the IFERROR function. Timeliness verification: The difference between the timestamps of the data statistics in the four types of heterogeneous data tables is calculated using the DATEDIF function in Excel. A time threshold is preset. If the difference exceeds the time threshold, a timestamp mismatch prompt message is returned, and corresponding invalid data is automatically filtered out. Verification result processing: Data with no abnormality prompts in both uniqueness and timeliness verification will directly proceed to the data merging and indicator calculation steps; data with abnormality prompts will be corrected before proceeding to the data merging and indicator calculation steps.

3. The power grid data analysis and processing method based on Excel and VBA according to claim 1, characterized in that, The data merging and indicator calculation steps specifically include: Synthetic table creation: Based on any one of the following data tables: Interface statistics table, Interface call status table, Application support status data details table, and Application call status table, create a corresponding synthetic table and use the field specifications of the base data table to complete the initialization of the basic fields of the synthetic table. Core metric calculation: By nesting SUMIF, INDEX-MATCH, and IFERROR functions, the system achieves the accumulation, weighted calculation, and conditional statistics of data of the same dimension from multiple data sources, and completes the calculation of call count, success rate, failure rate, average duration, and timeout rate. Extended metrics statistics: Use the COUNTIF and COUNTIFS functions to perform conditional statistics, and complete the statistics on the number of service average response times exceeding the standard and the number of failed API calls; Multi-table merging: Completes field merging and data merging of four types of heterogeneous data tables, adapting to the merging requirements of heterogeneous data tables with up to 20 fields per table.

4. The power grid data analysis and processing method based on Excel and VBA according to claim 1, characterized in that, In the automated execution, the execution logic of the VBA script specifically includes: Automatically read data sources: The VBA script automatically reads four types of heterogeneous data tables from the unified power grid dispatching component and the power grid resource business platform according to the preset file paths; Automatic execution of function formulas: VBA scripts automatically trigger the batch execution of field expansion formulas in interface statistical data tables, data merging formulas in various composite tables, and indicator calculation formulas; Automatically fill in unmatched data: The VBA script identifies unmatched data records with a matching value of 0, extracts the unique identifier information from the records, fills in the data at the end of the composite table and generates consecutive serial numbers, and at the same time triggers the matching formula to perform index calculations on the filled data. Automatic weekly report filling: The VBA script locates the cell coordinates corresponding to the power grid data indicators in each composite table, reads the indicator values, and accurately fills the indicator values ​​into the corresponding cells of the weekly report according to the field specifications of the preset power grid weekly report template.

5. The power grid data analysis and processing method based on Excel and VBA according to claim 1, characterized in that, In the refined error handling, the error message rules for the multi-layered nested IFERROR function are as follows: for #N / A errors, a verification message indicating an abnormal associative key is returned; for #VALUE! errors, a message indicating data format standardization processing is returned; and for multiple matching result errors, a verification message indicating data redundancy is returned. Furthermore, each of these messages includes the specific object to be verified and the direction of processing.

6. The power grid data analysis and processing method based on Excel and VBA according to claim 2, characterized in that, The timeliness check has a preset time threshold of 1 minute, and the DATEDIF function calculates time in seconds; the uniqueness check uses the COUNTIFS function to check the application name-interface address composite association key, and the check scope is the full data of the four types of heterogeneous data tables.

7. The power grid data analysis and processing method based on Excel and VBA according to claim 3, characterized in that, The calculation logic for the core indicators is as follows: The success rate is calculated based on the merged number of calls and the number of failures. An IF function is used to add a non-zero check for the number of calls to avoid division errors. The average duration is a weighted average of the corresponding indicators from multiple data sources. The COUNTIF function is used to count the amount of data involved in the calculation to achieve the weighted operation. The timeout rate is calculated based on the merged number of calls and the number of timeouts. An IF function is used to add a non-zero check for the number of calls to avoid division errors. Furthermore, all core metrics calculations are nested within the IFERROR function, which returns a preset value of 0 in case of an exception.

8. The power grid data analysis and processing method based on Excel and VBA according to claim 4, characterized in that, The execution logic of the VBA script also includes error protection steps: when automatically filling in the weekly report, if the indicator data is detected to be empty or non-numerical, a data error message is returned, and the error value is masked by the IFERROR function; in the automatic reading of the data source, the VBA script supports reading data tables in Excel's native .xlsx and .xls formats, and no manual import or format conversion is required.

9. The power grid data analysis and processing method based on Excel and VBA according to any one of claims 1-8, characterized in that, It also includes result output: output call interface synthesis table, application support data synthesis table, application call data synthesis table, and generates power grid weekly report; The method is adapted to the high-frequency reporting scenario of weekly reports in the power grid resource business platform. It requires adjusting the association rules of Excel function formulas or mapping tables, but does not require modifying the core logic of VBA scripts.

10. A power grid data analysis and processing system based on Excel and VBA, characterized in that, The power grid data analysis and processing method based on Excel and VBA, as described in any one of claims 1-9, is implemented. The system is deployed in Excel software and includes a data source layer, a mapping table layer, a function optimization layer, a validation and merging layer, an automated execution layer, and an output layer that sequentially perform unidirectional data transmission. The functional configurations of each layer are as follows: Data source layer: Establish data connection with the unified power grid dispatching component and the power grid resource business platform, and acquire and store four types of heterogeneous data tables: interface statistics table, interface call status table, application support status data details, and application interface call status table; Mapping table layer: Stores and maintains business department mapping tables and application name mapping tables, and provides unified field association rules to the function optimization layer; Function optimization layer: Configures the composite association key adaptation submodule and the refined error handling submodule, which respectively perform the concatenation of composite association keys, VLOOKUP lookup operation and IFERROR function multi-level nested error prompt operation; Verification and merging layer: Configure a dual verification submodule and a data merging submodule to perform uniqueness verification, timeliness verification, heterogeneous data table merging, and calculation of core and extended indicators of power grid data, respectively. The automated execution layer is developed based on VBA scripts. It configures data source reading scripts, formula execution scripts, data filling scripts, and weekly report filling scripts, which respectively perform automatic data source reading, automatic function and formula execution, automatic filling of unmatched data, and automatic filling of power grid weekly reports. Output layer: Receives the processing results from the automated execution layer, outputs the call interface composite table, application support data composite table, and application call data composite table, and generates a standardized power grid weekly report to provide data output results to the power grid business end.

Images