The processing methods, devices, electronic equipment, and storage media for the formulas in the table.

Abstract

This application relates to a method, apparatus, electronic device, and storage medium for processing formulas in a table, comprising: acquiring a table to be diagnosed; calculating the formulas to be diagnosed in the table and acquiring the calculation characteristics of the formulas to be diagnosed; generating candidate optimized formulas for the formulas to be diagnosed based on a preset optimization strategy and determining the calculation characteristics of the candidate optimized formulas; marking the formulas to be diagnosed as optimizable formulas if the formulas to be diagnosed meet preset optimization conditions based on the calculation characteristics of the formulas to be diagnosed and the corresponding calculation characteristics of the candidate optimized formulas; and generating formula diagnosis results based on the optimizable formulas. Thus, while maintaining the reliability of the table calculation results, it achieves automatic identification, filtering, and output of time-consuming formulas, effectively improving the pertinence of formula performance diagnosis and the credibility of optimization suggestions, and improving the overall operational efficiency of the diagnosis and optimization process.

Description

Technical Field

[0001] This application relates to the field of table processing, and more particularly to a method, apparatus, electronic device, and storage medium for processing formulas in a table. Background Technology

[0002] In spreadsheet applications, users often need to handle workbooks containing numerous formulas. As the data volume increases and the formula complexity grows, the calculation time for spreadsheets rises significantly, leading to interface lag and response delays, severely impacting the user experience.

[0003] Currently, existing solutions typically require users to manually review or uniformly process all formulas in the entire table. This not only increases the user's workload but also makes the overall optimization process inefficient. Summary of the Invention

[0004] This application provides a method, apparatus, electronic device, and storage medium for processing formulas in a table, in order to solve the technical problem in the prior art that it is difficult to automatically identify and filter out optimizable formulas in a table, which not only increases the burden on users but also results in low overall efficiency of the optimization process.

[0005] Firstly, this application provides a method for processing formulas in a table, the method comprising: Obtain the diagnosis form; The formula to be diagnosed in the table to be diagnosed is calculated, and the calculation characteristics of the formula to be diagnosed are obtained; Based on a preset optimization strategy, candidate optimization formulas for the formula to be diagnosed are generated, and the computational characteristics of the candidate optimization formulas are determined. If, based on the computational characteristics of the formula to be diagnosed and the computational characteristics of the corresponding candidate optimization formula, it is determined that the formula to be diagnosed meets the preset optimization conditions, the formula to be diagnosed is marked as an optimizable formula. Based on the aforementioned optimizable formula, a formula diagnostic result is generated.

[0006] In one possible implementation, the computational features include: computation time and computation result; The preset optimization conditions include: the calculation result of the candidate optimization formula is consistent with the calculation result of the formula to be diagnosed, and the calculation time of the candidate optimization formula is lower than the calculation time of the formula to be diagnosed.

[0007] In one possible implementation, generating candidate optimization formulas for the formula to be diagnosed based on a preset optimization strategy includes at least one of the following methods: The formula to be diagnosed is matched with a preset set of optimization rules; if the match is successful, a candidate optimization formula is generated based on the matched optimization rules. Candidate optimized formulas are generated based on the trained formula optimization model for the formula to be diagnosed.

[0008] In one possible implementation, the method further includes: Structural clustering is performed on the formulas to be diagnosed in the table to be diagnosed, and multiple formulas to be diagnosed that meet the preset structural consistency conditions are grouped into the same formula class. The process of generating candidate optimization formulas for the formula to be diagnosed based on a preset optimization strategy includes: Based on the preset optimization strategy, candidate optimization formulas are generated for each formula class.

[0009] In one possible implementation, generating candidate optimization formulas corresponding to the formula class based on a preset optimization strategy includes: Based on the preset optimization strategy, candidate optimization formula templates corresponding to the formula class are generated; Based on the candidate optimization formula template, corresponding candidate optimization formulas are generated for each formula to be diagnosed contained in the formula class.

[0010] In one possible implementation, marking the formula to be diagnosed as an optimizable formula after determining that the formula to be diagnosed meets preset optimization conditions based on the computational characteristics of the formula to be diagnosed and the computational characteristics of the corresponding candidate optimization formula includes: If the calculation results of all formulas to be diagnosed in the formula class are consistent with the calculation results of the candidate optimization formulas, and the calculation time of the candidate optimization formulas of all formulas to be diagnosed in the formula class is lower than the calculation time of all formulas to be diagnosed in the formula class, then all formulas to be diagnosed in the formula class are marked as optimizable formulas.

[0011] In one possible implementation, the formula diagnostic results are displayed one by one by formula class, and each displayed item includes at least one of the following: the original formula type corresponding to the formula class, the cell range covered by the formula class, the comparison information of calculation time before and after optimization, and an identifier indicating that the result consistency verification has passed.

[0012] In one possible implementation, the method further includes: In response to a trigger operation on the displayed item, the cell range covered by the formula class corresponding to the displayed item is located in the table to be diagnosed.

[0013] In one possible implementation, the method further includes: In response to the user's confirmation and optimization operation for the displayed item, the corresponding candidate optimization formula is written in batches into all cell ranges contained in the formula class corresponding to the displayed item, and the table to be diagnosed is recalculated.

[0014] In one possible implementation, the method further includes: In response to the confirmation optimization operation of a single optimizable formula, the corresponding candidate optimization formula is written into the corresponding cell, and the table to be diagnosed is recalculated. And / or, in response to the batch confirmation optimization operation of multiple optimizable formulas, the corresponding multiple candidate optimization formulas are written into their respective cells, and the table to be diagnosed is recalculated.

[0015] In one possible implementation, the method further includes: Before writing the candidate optimization formula into the corresponding cell, record the original formula in the cell; In response to the restore command, the original formula is rewritten into the cell.

[0016] In one possible implementation, the method further includes: In response to a formula change in the table to be diagnosed, the formula diagnosis result is refreshed.

[0017] In one possible implementation, refreshing the formula diagnostic result includes: An incremental update approach is used to re-perform diagnostics on the formula regions where formula changes have occurred and the formulas on the affected calculation chains, and to update the corresponding formula diagnostic results.

[0018] In one possible implementation, obtaining the diagnostic form includes: In response to a diagnostic command, a table to be diagnosed is retrieved; the diagnostic command is triggered in at least one of the following ways: The user triggers the diagnostic command in the table; The diagnostic instruction is generated when it is detected that the calculation time of any formula in the table exceeds a preset threshold and the formula belongs to a preset optimizable type.

[0019] Secondly, this application provides a processing apparatus for formulas in a table, the apparatus comprising: The table retrieval module is used to retrieve the table to be diagnosed. The formula calculation module is used to calculate the formula to be diagnosed in the table to be diagnosed and to obtain the calculation characteristics of the formula to be diagnosed. The formula optimization module is used to generate candidate optimized formulas for the formula to be diagnosed based on a preset optimization strategy, and to determine the computational characteristics of the candidate optimized formulas. An optimizable formula determination module is used to mark the formula to be diagnosed as an optimizable formula when the formula to be diagnosed meets preset optimization conditions based on the calculation characteristics of the formula to be diagnosed and the calculation characteristics of the corresponding candidate optimization formulas. The formula diagnosis module is used to generate formula diagnosis results based on the optimizable formula.

[0020] In one possible implementation, the computational features include: computation time and computation result; The preset optimization conditions include: the calculation result of the candidate optimization formula is consistent with the calculation result of the formula to be diagnosed, and the calculation time of the candidate optimization formula is lower than the calculation time of the formula to be diagnosed.

[0021] In one possible implementation, the formula optimization module generates candidate optimized formulas for the formula to be diagnosed based on a preset optimization strategy, including at least one of the following methods: The formula to be diagnosed is matched with a preset set of optimization rules; if the match is successful, a candidate optimization formula is generated based on the matched optimization rules. Candidate optimized formulas are generated based on the trained formula optimization model for the formula to be diagnosed.

[0022] In one possible implementation, the device further includes: The clustering module is used to perform structural clustering on the formulas to be diagnosed in the table to be diagnosed, and to group multiple formulas to be diagnosed that meet the preset structural consistency conditions into the same formula class. The formula optimization module is specifically used for: Based on the preset optimization strategy, candidate optimization formulas are generated for each formula class.

[0023] In one possible implementation, the formula optimization module is specifically used for: Based on the preset optimization strategy, candidate optimization formula templates corresponding to the formula class are generated; Based on the candidate optimization formula template, corresponding candidate optimization formulas are generated for each formula to be diagnosed contained in the formula class.

[0024] In one possible implementation, the optimizable formula determination module is specifically used for: If the calculation results of all formulas to be diagnosed in the formula class are consistent with the calculation results of the candidate optimization formulas, and the calculation time of the candidate optimization formulas of all formulas to be diagnosed in the formula class is lower than the calculation time of all formulas to be diagnosed in the formula class, then all formulas to be diagnosed in the formula class are marked as optimizable formulas.

[0025] In one possible implementation, the formula diagnostic results are displayed one by one by formula class, and each displayed item includes at least one of the following: the original formula type corresponding to the formula class, the cell range covered by the formula class, the comparison information of calculation time before and after optimization, and an identifier indicating that the result consistency verification has passed.

[0026] In one possible implementation, the device further includes: The positioning module is used to locate the cell area covered by the formula class corresponding to the displayed item in the table to be diagnosed in response to the trigger operation of the displayed item.

[0027] In one possible implementation, the device further includes: The first batch optimization module is used to respond to the user's confirmation optimization operation for the displayed item, write the corresponding candidate optimization formulas in batches into all cell areas contained in the formula class corresponding to the displayed item, and trigger the recalculation of the table to be diagnosed.

[0028] In one possible implementation, the device further includes: A single formula optimization module is used to respond to the confirmation optimization operation of a single optimizable formula by writing the corresponding candidate optimization formula into the corresponding cell and triggering the recalculation of the table to be diagnosed. The second batch optimization module is used to respond to the batch confirmation optimization operation of multiple optimizable formulas, write the corresponding multiple candidate optimization formulas into their respective cells, and trigger the recalculation of the table to be diagnosed.

[0029] In one possible implementation, the device further includes: The recovery module is used to record the original formula in the cell before writing the candidate optimization formula into the corresponding cell; In response to the restore command, the original formula is rewritten into the cell.

[0030] In one possible implementation, the device further includes: The refresh module is used to refresh the formula diagnosis result in response to a change in the formula in the table to be diagnosed.

[0031] In one possible implementation, the refresh module is specifically used for: An incremental update approach is used to re-perform diagnostics on the formula regions where formula changes have occurred and the formulas on the affected calculation chains, and to update the corresponding formula diagnostic results.

[0032] In one possible implementation, the table acquisition module is specifically used for: In response to a diagnostic command, a table to be diagnosed is retrieved; the diagnostic command is triggered in at least one of the following ways: The user triggers the diagnostic command in the table; The diagnostic instruction is generated when it is detected that the calculation time of any formula in the table exceeds a preset threshold and the formula belongs to a preset optimizable type.

[0033] Thirdly, this application provides an electronic device, including: a processor and a memory, wherein the processor is configured to execute a processing program for formulas in a table stored in the memory, to implement the processing method for formulas in a table as described in any one of the first aspects.

[0034] Fourthly, this application provides a storage medium storing one or more programs that can be executed by one or more processors to implement the processing method for the formulas in the table as described in any one aspect of the first application.

[0035] Compared with the prior art, the technical solution provided in this application has the following advantages: By acquiring the table to be diagnosed and calculating the formulas to be diagnosed therein, the calculation characteristics are obtained. Based on this, candidate optimization formulas are generated according to a preset optimization strategy, and their calculation characteristics are also obtained. Then, the calculation characteristics of the original formula and the candidate formulas are compared according to preset optimization conditions. Only when the calculation characteristics of the candidate formula meet the preset optimization conditions is the original formula marked as an optimizable formula. This mechanism ensures that only those formulas that meet the safety and effectiveness requirements at the calculation characteristic level will be included in the subsequent diagnostic results, thereby filtering out invalid optimizations that may cause result deviations or no substantial performance improvement. Finally, diagnostic results are generated based on the marked optimizable formulas, enabling users or the system to accurately focus on formulas that truly have optimization value, without the need for manual screening of high-time-consuming formulas one by one, or the need for indiscriminate analysis and processing of all formulas in the entire table. It can be seen that this solution, while maintaining the reliability of the table calculation results, realizes the automatic identification, screening and output of high-time-consuming formulas, effectively improving the pertinence of formula performance diagnosis and the credibility of optimization suggestions, and improving the overall efficiency of the diagnosis and optimization process. Attached Figure Description

[0036] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0037] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0038] One or more embodiments are illustrated by way of example with reference numerals in the accompanying drawings. These illustrations do not constitute a limitation on the embodiments. Elements with the same reference numerals in the drawings are denoted as similar elements. Unless otherwise stated, the figures in the drawings are not to be limited by scale.

[0039] Figure 1 A flowchart illustrating an embodiment of a method for processing formulas in a table, provided in this application. Figure 2 A schematic diagram of a diagnostic form provided in an embodiment of this application; Figure 3 A schematic diagram of a formula diagnostic interface provided in an embodiment of this application; Figure 4 A schematic diagram of another formula diagnostic interface provided in an embodiment of this application; Figure 5 A block diagram illustrating an embodiment of a processing apparatus for formulas in a table, provided in this application. Figure 6 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0040] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0041] The following disclosure provides numerous different embodiments or examples for implementing various structures of this application. To simplify the disclosure, specific examples of components and arrangements are described below. These are merely examples and are not intended to limit the scope of this application. Furthermore, reference numerals and / or letters may be repeated in different examples. Such repetition is for simplification and clarity and does not in itself indicate a relationship between the various embodiments and / or arrangements discussed.

[0042] To address the technical problem in existing technologies that make it difficult to automatically identify and filter optimizable formulas in tables, which not only increases the burden on users but also results in low overall efficiency of the optimization process, this application provides a method, apparatus, electronic device, and storage medium for processing formulas in tables. This method enables the automatic identification, filtering, and output of time-consuming formulas while maintaining the reliability of the table calculation results. It effectively improves the pertinence of formula performance diagnosis and the credibility of optimization suggestions, and improves the overall efficiency of the diagnosis and optimization process.

[0043] Figure 1 This is a flowchart illustrating an embodiment of a method for processing formulas in a table, provided in this application. Figure 1 As shown, the method includes the following steps: Step 101: Obtain the form to be diagnosed.

[0044] The "table to be diagnosed" refers to the spreadsheet file that the user wants to analyze and optimize for performance. It can be an entire workbook, a specific worksheet within a workbook, or even a range of cells selected by the user. The table can contain various formulas, data, and dependencies between formulas. See also... Figure 1 , is an example of a form to be diagnosed.

[0045] In one embodiment, obtaining the table to be diagnosed includes: obtaining the table to be diagnosed in response to a diagnostic command.

[0046] In one exemplary application scenario, the diagnostic command is triggered in the following ways: the user triggers the diagnostic command in the table; that is, the diagnostic command can be actively triggered by the user. For example, see... Figure 1 For example, the status bar or function menu of the spreadsheet interface has a "Formula Diagnosis" button. When the user clicks this button, the system will immediately generate a diagnostic command and update the currently open spreadsheet (i.e., the formula diagnostic command). Figure 1 The table shown is used as the table to be diagnosed, and then the subsequent diagnostic process is initiated.

[0047] Furthermore, during the diagnostic process, if the user edits, scrolls, or switches worksheets, the system can temporarily suspend the diagnostic task to avoid interfering with the user's normal use. The diagnostic process automatically resumes after the user completes the operation (e.g., after a preset silent period of more than 500 milliseconds), continuing to output or refresh the diagnostic results. In a more specific implementation, when the system detects that the user is performing table operations (e.g., editing cells, scrolling, filtering, sorting), it not only pauses the updating of diagnostic results but also records the current diagnostic progress (e.g., which node in the calculation chain has been processed). After the user completes the operation, the system automatically resumes the diagnostic from the breakpoint, continues processing the remaining formulas, and finally merges and outputs the complete diagnostic results. This "breakpoint-to-resume" pause-and-resume mechanism avoids competing for system resources with user operations and eliminates the need to rescan after each user operation, further improving diagnostic efficiency.

[0048] In another exemplary application scenario, the diagnostic command is triggered as follows: if the calculation time of any formula in the table exceeds a preset threshold and the formula belongs to a preset optimizable type, a diagnostic command is generated. That is, the diagnostic command can be automatically generated by the system. Specifically, when a user opens a table, the table display inevitably involves formula calculations. During the calculation process, the system can continuously monitor the calculation time of each formula. Once it detects that the calculation time of any formula exceeds a preset threshold (e.g., 2 seconds), the system will further determine whether the formula belongs to a preset optimizable type.

[0049] The predefined optimizable types refer to formulas that are known to have significant performance improvement potential in engineering practice and can be safely rewritten using the optimization strategies supported by this solution. For example, formulas that use VLOOKUP with IF({1,0}) to construct a temporary array, formulas that reference an entire column (such as SUM(A:A)) but have a limited actual data range, or formulas that implement simple business logic through multiple auxiliary columns are all optimizable types. Conversely, simple formulas like =SUM(A1) that only reference a single cell, or already optimized high-efficiency formulas, even if their calculation time is occasionally slightly high (e.g., due to system load), are not optimizable types. The system will not automatically trigger diagnostics for them to avoid unnecessary disruptions.

[0050] When the system detects that the calculation time of a certain formula exceeds a preset threshold and the formula is of an optimizable type, it can automatically generate diagnostic instructions, designate the corresponding table as the table to be diagnosed, and simultaneously provide the user with appropriate prompts on the interface. For example, such as... Figure 1As shown, a pop-up notification can be added near the calculation progress bar in the table, with a message such as "Formula calculation detected to be taking a long time," along with a "Go to Diagnose" button. After the user clicks "Go to Diagnose," the system can proceed with the diagnostic process. Furthermore, if the user doesn't act immediately, the notification will automatically fade out after a few seconds, without forcibly interrupting the user's current operation.

[0051] In this way, the system can proactively remind users when performance bottlenecks occur and there is value in optimization, which avoids the cognitive burden of manual triggering by users and also avoids invalid prompts for formulas that cannot be optimized.

[0052] Step 102: Calculate the formula to be diagnosed in the diagnostic table and obtain the calculation characteristics of the formula to be diagnosed.

[0053] The formula to be diagnosed refers to the formula within the scope of the table to be diagnosed determined in step 101 that requires performance diagnosis. In practical application scenarios, the specific scope of the formula to be diagnosed may vary depending on the application scenario. For example, in a scenario where the user actively triggers the "Formula Diagnosis" button in the status bar or function menu of the table interface to trigger the diagnosis command, the scope of the formula to be diagnosed is usually all formulas in the current table. In a scenario where the system automatically generates a diagnosis command because it detects that the calculation time of a certain formula exceeds a preset threshold and the formula belongs to the optimizable type, the scope of the formula to be diagnosed includes at least the calculation chain coverage area corresponding to the formula that triggered the diagnosis command. That is, the system will take the formula as the starting point and trace forward and backward along the calculation chain to include related formulas that are affected by or depend on it in the diagnosis scope. Here, the calculation chain is a directed dependency link formed in the table based on cell reference relationships. For example, if the formula in cell C1 is "=A1+B1", then C1 depends on A1 and B1; if D1 references C1, then a dependency chain of A1, B1→C1→D1 is formed.

[0054] In addition, there are other methods, such as allowing the user to manually select a cell range or worksheet as the diagnostic target. In this case, the range of formulas to be diagnosed is all formulas within the user-selected range.

[0055] It's important to note that when the formulas to be diagnosed are not all formulas in the entire spreadsheet (e.g., automatically triggered by the system or selected by the user), it doesn't mean the system only calculates these formulas. Due to the computational chain dependencies in spreadsheets, even if some formulas are not within the scope of the diagnosis, as long as they are depended upon by the formulas to be diagnosed (i.e., they are preceding nodes in the computational chain), the system still needs to calculate them normally to ensure the correctness and accuracy of the calculation results for the formulas to be diagnosed. The difference is that subsequent optimization processes (such as generating candidate optimization formulas, result consistency verification, and marking formulas as optimizable) are only performed on the formulas to be diagnosed; formulas outside the diagnostic scope will not be included in the optimization suggestions.

[0056] Computational characteristics refer to quantitative indicators that reflect the performance and output of a formula during execution. In one embodiment, computational characteristics include at least two specific indicators: computation time and computation result. Computation time refers to the time taken from the start of the formula's calculation to obtaining the result (usually measured in milliseconds or seconds). Computation time directly measures the degree of impact of the formula on the overall performance of the table and is a key basis for identifying "time-consuming formulas." The computation result is the value obtained by the formula under the current data state (which may be a number, text, error value, or other value formats). The computation result is the only standard for judging whether the result before and after optimization is equivalent; only when the results are completely consistent is the optimization safe. These two characteristics, computation time and computation result, together constitute the basis for subsequent judgments on whether a formula is worthwhile and safe to optimize.

[0057] In one embodiment, based on the calculation chain of the table to be diagnosed, the formulas to be diagnosed in the table are calculated, and the calculation characteristics of the formulas to be diagnosed are obtained. That is, according to the directed dependency chain formed by the calculation chain of the table to be diagnosed, the formulas to be diagnosed in the table to be diagnosed are calculated sequentially, and the calculation characteristics of the formulas to be diagnosed are obtained. Specifically, the dependent cells are calculated first, and then the cells that depend on them are calculated. While each formula is being calculated, its time and result are recorded. For example, assuming the order of the calculation chain is formula A → formula B → formula C, the system will first wait for formula A to be calculated, record its time and result; then it will proceed to formula B, and record it in the same way; and then to formula C. The recorded information can be temporarily stored in memory for use in subsequent steps. This ensures that the calculation process of each formula to be diagnosed strictly follows the data dependency relationship, avoids result deviations caused by disordered calculation order, and thus accurately obtains the calculation time and calculation result characteristics that reflect the true performance of the formula. This provides reliable basic data support for subsequent judgments based on computational features on whether the formula is worth optimizing and whether it can be safely optimized, making the generation of optimization suggestions more targeted and credible. At the same time, it can effectively avoid invalid feature collection caused by incorrect calculation order, and improve the efficiency of the overall diagnosis and optimization process.

[0058] It's important to note that the computation time for each formula is calculated independently. This means it only refers to the CPU execution time consumed by that formula itself from the start of calculation to the output, excluding the computation time of its dependent formulas. For example, if formula A is slow to calculate, and formula B depends on the result of formula A, then formula B must wait for formula A to complete before it can begin execution. In this case, the "delay in formula B" perceived by the user is actually the waiting time for the dependent formula, not an increase in the computation time of formula B itself. Therefore, when diagnosing time-consuming formulas, this solution only reflects the execution efficiency of its own logic when calculating the computation time of each formula, without double-counting the time spent by dependent formulas. This helps to accurately pinpoint performance bottlenecks, such as when the real problem lies in the inefficiency of formula A itself, rather than formula B.

[0059] In one embodiment, the technical solution of this application reuses the original calculation engine of the spreadsheet software without building an additional independent scanning program. Thus, no additional computational overhead is introduced during the acquisition of formula calculation features, and there is no need to wait for the entire table scan to complete before analysis can begin, thereby achieving a seamless integration of the diagnostic process with the original calculation process of the spreadsheet.

[0060] Furthermore, embodiments of this application also support real-time output of diagnostic results. Specifically, see [link to relevant documentation]. Figure 3 For example, the system can calculate the formulas to be diagnosed sequentially according to the topological order of the calculation chain. After each formula completes its calculation and its calculation characteristics (time consumption and result) are obtained, the system immediately outputs the diagnostic information of that formula (such as whether it is time-consuming, whether it matches the optimization rules, etc.) to the diagnostic panel or cache, without waiting for all formulas to be calculated. For example, when the calculation chain is long, the user can see part of the diagnostic results as soon as the first few formulas are calculated, and the system dynamically updates the panel while calculating subsequent formulas. This "calculate and output" streaming processing method avoids the user having to wait a long time for the full table scan to finish before seeing any feedback, significantly improving the interaction response speed and user experience in large table scenarios.

[0061] Step 103: Based on the preset optimization strategy, generate candidate optimization formulas for the formula to be diagnosed, and determine the computational characteristics of the candidate optimization formulas.

[0062] The preset optimization strategy is a set of predefined rules or models used to rewrite inefficient formulas into efficient ones. The "rules" are usually written manually based on the computational characteristics and performance experience of the formulas; the "model" is based on machine learning technology and can automatically discover optimizable points from the overall structure of the table.

[0063] Specifically, the preset set of optimization rules includes the following categories of rules: (1) Function substitution rule, which is used to replace inefficient combinations of functions that achieve the same computational intent with efficient functions that are semantically equivalent but have better performance.

[0064] For example, in scenarios where you need to look up and return results from non-contiguous columns, a common inefficient approach is to use VLOOKUP in conjunction with IF({1,0}) or CHOOSE({1,2}) to construct a temporary two-dimensional array. This approach generates a large intermediate array, increasing memory and computational overhead. Function substitution rules can directly replace this with XLOOKUP.

[0065] For example, the original formula is: =VLOOKUP("lookup value",IF({1,0},Sheet1!A:A,Sheet1!T:T),2); Optimized formula: =XLOOKUP("Lookup value",Sheet1!A:A,Sheet1!T:T) (2) Calculation range reduction rule: This rule is used to identify cases where the formula references an entire column or row (e.g., SUM(A:A)), but the actual effective data range is much smaller than the entire column. The system analyzes the data distribution in the table to determine the actual data range (e.g., A2:A5000), and then reduces the entire column reference in the formula to the actual range. By reducing the participation of invalid cells in the calculation, the data scanning range is reduced, thereby improving the overall calculation efficiency.

[0066] For example, the original formula is: =SUM(A:A); Optimized formula: =SUM(A2:A5000) (3) Formula structure rewriting rules, which are used to identify those formulas that achieve conditional filtering and aggregation through array calculations and rewrite them as dedicated aggregation functions.

[0067] For example, the original formula: =SUM(FILTER(B:B,C:C="East China")) intends to sum the rows in column B that satisfy column C as 'East China'. The formula rewriting rules can rewrite it as: =SUMIFS(B:B,C:C,"East China") The rewritten formula avoids generating intermediate filtering arrays and performs aggregation calculations directly based on conditions, resulting in higher performance.

[0068] Those skilled in the art should understand that the above content is merely illustrative, and in practical applications, the preset set of optimization rules can be continuously expanded and optimized.

[0069] The aforementioned pre-defined set of optimization rules excels at handling inefficient patterns within a single formula, such as function substitution, range reduction, and structural rewriting. The model goes a step further, capable of comprehensively analyzing the structure of the entire table, formula dependencies, and computational chains to identify redundant computational patterns across formulas and regions, and propose a holistic reconstruction solution.

[0070] A typical application scenario is identifying "auxiliary column chains". In practice, many users are accustomed to breaking down calculations that could be completed in one step into multiple intermediate steps, such as: Column A: raw data (e.g., text containing a complete address); Column B: extracted fields, such as =LEFT(A2,6) or =MID(A2,3,5), used to extract key information from the raw data; Column C: conditional judgment, such as =IF(B2="a value","yes","no"), making logical judgments based on the extracted fields; Column D: summary calculation, such as =SUMPRODUCT((C2:C100="yes"), other columns) or =COUNTIFS(C2:C100,"yes").

[0071] The advantages of this approach are its clear logic, ease of understanding and debugging, but its disadvantages are: it introduces a large number of auxiliary columns, which consumes additional storage space; each step produces intermediate results, lengthening the calculation chain; and the final summary may involve repeated scanning of data areas, resulting in poor overall performance.

[0072] The model can analyze the dependencies in this computation chain (B depends on A, C depends on B, and D depends on C), understand the user's original computational intent, that is, to directly extract specific fields from column A and perform conditional filtering without introducing intermediate columns, then summarize the filtering results and generate a merged optimized formula.

[0073] The optimization formula can vary depending on the specific aggregation requirements. For example, if the goal is to count the number of rows that meet the given conditions, the optimization formula might be: =SUMPRODUCT((LEFT(A2:A100,6)="a certain value")), or =COUNTIFS(A2:A100,"a certain value*") (if the extraction operation is to extract the first few characters). If the goal is to calculate the sum of values ​​in a column that meet certain conditions, the optimized formula could be: =SUMPRODUCT((LEFT(A2:A100,6)="a certain value")*(E2:E100)) By eliminating auxiliary columns, merging calculation steps, and avoiding the expansion of intermediate arrays, the optimized formula significantly shortens the calculation chain length, reduces memory usage, and thus greatly improves overall calculation efficiency.

[0074] The model's advantage lies in its ability to handle complex scenarios that are difficult to cover with pre-defined rules, especially those involving multi-column dependencies, duplicate scans, and auxiliary column chains where intermediate results can be eliminated. Furthermore, as training data becomes richer and the model iterates, its optimization capabilities can be continuously improved, constantly covering new redundant patterns.

[0075] Based on the above description, in one embodiment, generating candidate optimization formulas for the formula to be diagnosed based on a preset optimization strategy includes at least one of the following methods: matching the formula to be diagnosed with a preset set of optimization rules; generating candidate optimization formulas for the formula to be diagnosed based on the matched optimization rules if the matching is successful; and generating candidate optimization formulas for the formula to be diagnosed based on a trained formula optimization model.

[0076] The aforementioned formula optimization model can employ a neural network architecture, such as a Transformer-based model. Its input is the expression text of the formula to be optimized (which may include cell references, function names, operators, etc.). Optionally, it can also input a small number of optimization examples (such as several sets of "original formula - optimized formula" comparison pairs) or a predefined optimization rule base as auxiliary information to guide the model in understanding the optimization intent. The model's output is the optimized formula expression, and it can selectively output the optimization basis (such as explaining what optimization strategy was used, like "replacing the VLOOKUP+IF structure with XLOOKUP" or "reducing the entire column reference to the actual data range"). During the training phase, a large number of manually labeled formula optimization samples can be used for supervised learning of the model, enabling it to learn to identify common inefficient patterns and generate equivalent efficient forms. In actual operation, for the formula to be diagnosed, the model directly generates candidate optimization formulas for subsequent dual verification. Candidates generated by the model, if they pass result consistency verification and performance improvement verification, are also marked as optimizable formulas.

[0077] In actual operation, matching preset rules should be attempted first because rule execution costs are low and results are highly deterministic. When a rule cannot be matched, or when the optimization candidate generated after matching fails to pass the result consistency check or performance improvement check, the system can further call the model for analysis and attempt to generate an overall optimization solution. The two complement each other, jointly covering different optimization needs from single formula fine-tuning to whole table structure reconstruction.

[0078] In step 103, after generating the candidate optimization formulas, the computation time and results of the candidate formulas are also calculated. It should be noted that this calculation is performed in the background environment and will not affect the user's current table interface or normal operations.

[0079] Step 104: Based on the computational characteristics of the formula to be diagnosed and the corresponding candidate optimization formula, if it is determined that the formula to be diagnosed meets the preset optimization conditions, the formula to be diagnosed is marked as an optimizable formula.

[0080] Corresponding to the aforementioned computational characteristics, the preset optimization conditions include: the calculation result of the candidate optimization formula is consistent with the calculation result of the formula to be diagnosed, and the computation time of the candidate optimization formula is lower than the computation time of the formula to be diagnosed. Specifically, in the technical solution provided in the embodiments of this application, for any formula to be diagnosed, only if both of the above conditions are met simultaneously is the formula to be diagnosed considered an optimizable formula. These two conditions are indispensable: if it is only fast but the result is incorrect, optimization is dangerous; if the result is correct but it does not become faster or even slower, optimization is worthless.

[0081] Furthermore, in practice, "lower than" is not an absolute inequality, but rather a variable range or tolerance threshold can be set. For example, if the computation time of a candidate optimization formula is only a few milliseconds faster than the original formula (within the statistical error range), the system can determine that there is "no significant improvement" and therefore not mark it as optimizable. This design is to avoid "pseudo-optimizations" that offer negligible benefits but may introduce complexity. Developers or users can configure this threshold according to the actual scenario, such as setting it to "reducing time by at least 5%" or "absolutely reducing time by at least 10 milliseconds" before adoption. For cases with only minor differences (such as time differences within the variable range), the "conservative principle" is followed, and the original formula is retained without optimization.

[0082] Step 105: Generate formula diagnostic results based on the optimizable formula.

[0083] In one embodiment, the formula diagnostic results include the results of optimizable formulas. This can be a user-oriented report that clearly and visually demonstrates which formulas have performance issues, which can be optimized, and by how much faster the optimization will be. The report can be presented as a sidebar panel (e.g., Figure 4(As shown), it can also be a standalone pop-up window or embedded in the task pane. For example, the diagnostic results include the following information: the number of formulas that can be optimized and the number of formulas that are time-consuming; diagnostic entries for each formula or formula class, including: formula type (e.g., SUMPRODUCT, VLOOKUP), performance problem description (e.g., "high latency calculation", "invalid references in the entire column", "redundant nesting"), the cell range (e.g., Sheet1!H2:H6), comparison of time consumption before and after optimization (e.g., "13s → -12.8s"), and an indicator indicating that the result consistency verification has passed (e.g., "the calculation results are consistent before and after optimization"); an operation entry for each entry, such as an "Optimize Formula" button, which allows users to view the specific optimized formula and choose whether to apply it; a summary panel at the bottom, displaying the total number of time-consuming formulas, the total number of formulas that can be optimized, the estimated total time consumption, and the estimated time reduction; and a "One-Click Optimize All" button for batch adoption of suggestions for all formulas that can be optimized.

[0084] For example, see Figure 4 The right-hand panel lists several diagnostic items, from "1. SUMPRODUCT: High Latency Calculation" to "6. SUMPRODUCT: High Latency Calculation (loading)". Items that have completed verification are clearly marked "Calculation results are consistent before and after optimization" and include an "Optimize Formula" button; items still in calculation show a "Calculating" status. The bottom summary displays "High-time formula 27, 16 formulas that can be optimized, estimated time 15.8s → reduced by 14.6s", and provides an option to "Optimize all with one click".

[0085] Based on this, in one embodiment, the technical solution provided by this application further includes: in response to the confirmation and optimization operation of a single optimizable formula, writing the corresponding candidate optimization formula into the corresponding cell, and triggering the recalculation of the table to be diagnosed.

[0086] The confirmation and optimization process here can be achieved by the user clicking the "Optimization Formula" button to the right of a diagnostic item and then clicking "Apply" in the pop-up confirmation dialog box; or by the user double-clicking the item to directly adopt the optimization. This mode is suitable for scenarios where users want to review and confirm each item individually.

[0087] In another embodiment, in response to the batch confirmation optimization operation of multiple optimizable formulas, the corresponding multiple candidate optimization formulas are written into their respective cells, and the table to be diagnosed is recalculated.

[0088] The batch confirmation optimization operation here can be further divided into two sub-modes: Manually Selective Batch Optimization: In the diagnostic results panel, users can select a subset of formulas from all optimizable options by checking checkboxes (e.g., selecting only SUMPRODUCT-type optimizations and skipping VLOOKUP-type optimizations). Then, clicking the "Batch Optimize" button on the panel will cause the system to perform replacements only on the selected formulas. This mode is suitable for users who wish to selectively apply optimizations, such as optimizing only formulas with clear problems and low risk, while leaving other formulas unprocessed.

[0089] One-click optimization: Users can directly click the "One-click All Optimization" button at the bottom of the diagnostic results. The system automatically replaces all formulas marked as "optimizable" with their corresponding candidate optimization formulas without requiring users to select them one by one. This mode is suitable for scenarios where users fully trust the diagnostic results and pursue maximum efficiency, especially when there are many formulas that can be optimized (such as dozens or even hundreds), which can significantly reduce repetitive operations.

[0090] Furthermore, in one embodiment, regardless of whether a single confirmation, manual batch optimization, or one-click optimization is used, the system can retain a backup of the original formula before performing the write operation (e.g., recorded in the original cell's comment or in a separate cache). If the user is not satisfied with the optimization result, they can trigger a restore command through the "Restore" function, thereby rewriting the original formula back into the cell and reverting to the state before optimization, balancing operational efficiency and data security.

[0091] In another embodiment, the "formula diagnostic results" generated in step 105 are not displayed to the user as a list for confirmation item by item, but rather directly output as intelligent optimization results. That is, the system automatically completes all the judgments from steps 102 to 104 in the background. For formulas marked as "optimizable," candidate optimization formulas are directly applied for replacement, and the table is recalculated. For example, an optimization completion report can also be presented to the user, such as: "A total of 27 time-consuming formulas were detected, of which 16 have been automatically optimized. The estimated total time has decreased from 15.8 seconds to 1.2 seconds, and all optimization results have passed consistency verification." This mode is suitable for scenarios with high performance requirements and where users trust the system's automatic decision-making (such as server-side batch processing or scheduled optimization tasks).

[0092] Regardless of which method is used (interactive panel or automatic optimization), the core of step 105 is to generate diagnostic results based on the optimizable formula marked in step 104.

[0093] The technical solution provided in this application obtains the table to be diagnosed and calculates the formulas to be diagnosed within it to acquire their computational characteristics. Based on this, candidate optimization formulas are generated according to a preset optimization strategy, and their computational characteristics are also acquired. Then, the computational characteristics of the original formula and the candidate formulas are compared according to preset optimization conditions. Only when the computational characteristics of a candidate formula meet the preset optimization conditions is the original formula marked as an optimizable formula. This mechanism ensures that only formulas that meet the safety and effectiveness requirements at the computational characteristic level are included in the subsequent diagnostic results, thereby filtering out invalid optimizations that may cause result deviations or no substantial performance improvement. Finally, diagnostic results are generated based on the marked optimizable formulas, allowing users or the system to accurately focus on formulas that truly have optimization value, without the need for manual screening of high-time-consuming formulas or indiscriminate analysis and processing of all formulas in the table. Therefore, this solution, while maintaining the reliability of the table's calculation results, achieves automatic identification, screening, and output of high-time-consuming formulas, effectively improving the targeting of formula performance diagnosis and the credibility of optimization suggestions, and overall improving the operational efficiency of the diagnosis and optimization process.

[0094] In practical spreadsheet applications, users often fill a contiguous area with formulas that have the same structure, with only the reference position changing depending on the row and column offset. For example, in the cell range A2:A100, the formulas are =SUM(B2:D2), =SUM(B3:D3), ..., =SUM(B100:D100). Although these formulas are syntactically independent, from a performance diagnostic and optimization perspective, they have exactly the same calculation logic, only differing in the cell positions they apply to. If each formula is matched with optimization rules, candidate formulas are generated, and results are validated one by one according to the aforementioned steps, not only will a large amount of repetitive calculations occur, but the diagnostic results panel will also display hundreds or thousands of almost identical optimization suggestions, requiring users to repeatedly confirm them, resulting in a poor user experience.

[0095] To address the aforementioned scenarios, this application introduces a formula structure clustering mechanism. Specifically, in one embodiment, the formulas to be diagnosed in the diagnostic table are clustered structurally, grouping multiple formulas that meet preset structural consistency conditions into the same formula class.

[0096] The pre-defined structural consistency conditions include at least one of the following three aspects: First, the formulas have completely identical syntax structures (for example, in the range A2:A100, the formulas are =VLOOKUP(A2,E:F,2,0), =VLOOKUP(A3,E:F,2,0)...=VLOOKUP(A100,E:F,2,0), and their function names, number of parameters, and parameter types are all consistent); Second, the reference relationships in the formulas only involve relative or absolute offsets, meaning that the changes in cell addresses follow a uniform pattern (e.g., the lookup value increases with the row number, while the lookup table and return column number remain fixed); Third, the cell range containing these formulas is contiguous (e.g., A2:A100). Formulas that meet the above conditions, although referencing different cell addresses, have the same calculation essence and can therefore be treated as the same type of formula.

[0097] Once clustering is complete, subsequent optimization strategies are no longer applied to individual formulas, but rather on a formula class basis. That is, candidate optimization formulas are generated for each formula class based on a pre-defined optimization strategy. This significantly reduces computational and storage overhead, improving the overall efficiency of the optimization process.

[0098] Specifically, as an optional implementation, candidate optimization formulas corresponding to the formula class are generated based on a preset optimization strategy, including: generating candidate optimization formula templates corresponding to the formula class based on the preset optimization strategy; and generating corresponding candidate optimization formulas for each formula to be diagnosed contained in the formula class based on the candidate optimization formula templates.

[0099] In this context, the candidate optimized formula template refers to a general expression that represents the optimized form of a class of formulas. It retains the core calculation logic of the formula and abstracts the reference parts that change with the cell as variable parameters. For example, for the formula class =VLOOKUP(A2,E:F,2,0) mentioned above, the preset optimization strategy (such as function replacement rules) can replace it entirely with the more efficient XLOOKUP function. The corresponding candidate optimized formula template is: =XLOOKUP(lookup value,E:E,F:F). Here, "lookup value" is a placeholder, indicating that it needs to be filled according to the specific position of each cell. Based on this template, the system can instantiate a specific candidate optimized formula for each cell in the class: for cell A2, it generates =XLOOKUP(A2,E:E,F:F); for cell A3, it generates =XLOOKUP(A3,E:E,F:F); and so on. In this way, the optimization rule matching is executed only once (for the entire formula class), and the candidate formula template is generated only once, thereby greatly reducing redundant analysis and significantly improving the overall efficiency of diagnosis and optimization, especially suitable for table scenarios with large amounts of data and many formulas.

[0100] Based on the above embodiments, this application also provides the following embodiments: In one embodiment, the above-mentioned marking of the formula to be diagnosed as an optimizable formula when the formula to be diagnosed meets the preset optimization conditions based on the calculation characteristics of the formula to be diagnosed and the corresponding candidate optimization formula includes: marking all formulas to be diagnosed in the formula class as optimizable formulas when the calculation results of all formulas to be diagnosed in the formula class are consistent with the calculation results of the candidate optimization formulas, and the calculation time of the candidate optimization formulas of all formulas to be diagnosed in the formula class is lower than the calculation time of all formulas to be diagnosed in the formula class.

[0101] Building upon the clustering mechanism described above, this embodiment further clarifies the "optimizability" criteria for formulas already grouped into the same category. Since the formulas within this category are structurally identical, exhibiting only a regular shift in their reference positions, their optimization behavior should be uniform: either all are adopted, or none are adopted. Based on this design, the system does not individually determine the optimizability of each formula within the category, but rather considers the entire formula category as a whole.

[0102] Specifically, for any formula class, the system first verifies whether the calculation result of the candidate optimized formula for each formula to be diagnosed in that class is consistent with the original calculation result of the original formula. If the result of any cell is inconsistent, the class is determined not to meet the optimization criteria, and the entire class will not be marked as optimizable. Only when all formulas in the class pass the result consistency check will the system proceed to the time consumption comparison stage.

[0103] Regarding time consumption comparison, the system can calculate the total computation time of all formulas in the category (i.e., the sum of the original computation times of all formulas in the category compared to the sum of the computation times of all candidate formulas), or it can use the average computation time as a comparison indicator (e.g., the difference between the original average computation time and the candidate average computation time). These two methods are essentially equivalent; total computation time more directly reflects the overall performance gain, while average computation time is suitable for scenarios with a large number of cells in the category and a relatively even distribution of computation time. Regardless of the method used, the criterion is the same: the total computation time (or average computation time) of the candidate optimized formula is lower than the total computation time (or average computation time) of the original formula. If this condition is met, the system marks all formulas in the entire formula category as optimizable formulas.

[0104] This "holistic judgment" mechanism not only ensures the consistency of calculation results before and after optimization (avoiding situations where some cells in a class are correct while others are incorrect), but also ensures that the optimization has substantial performance benefits through overall time comparison.

[0105] In one embodiment, the formula diagnostic results are displayed one by one by formula class. Each displayed item includes at least one of the following: the original formula type corresponding to the formula class, the cell range covered by the formula class, the comparison information of calculation time before and after optimization, and the identifier that the result consistency verification has passed.

[0106] After completing formula diagnosis and generating a list of optimizable formulas, how to clearly present this information to users directly impacts user experience and decision-making efficiency. If the number of optimizable formulas diagnosed is large (e.g., hundreds), and each formula is presented with a separate suggestion, users will be faced with a long string of almost identical information. This not only makes it difficult to quickly locate key issues but also easily leads to visual fatigue and decision-making confusion.

[0107] To address this, this application's embodiments design a display method based on formula classes. Through the aforementioned clustering mechanism, the system groups multiple formulas with identical structures, exhibiting only regular offsets in their reference positions, and forming continuous regions into the same formula class. In the diagnostic results panel, only one entry is displayed for each formula class, rather than listing every formula within that class. For example, for 99 structurally identical VLOOKUP formulas in the A2:A100 region, the diagnostic results will only show one optimization suggestion representing the entire region, instead of 99 duplicate entries.

[0108] The displayed items should include at least one of the following: the original formula type corresponding to the formula class (e.g., SUMPRODUCT, VLOOKUP, etc.), to help users quickly identify which function is causing the problem; the cell range covered by the formula class (e.g., Sheet1!H2:H6), to let users know which locations will be affected by the optimization; a comparison of the calculation time before and after optimization (e.g., "13s→12.8s"), to visually demonstrate the performance benefits brought by the optimization; and an indicator that the result consistency check has passed (e.g., "The calculation results are consistent before and after optimization"), to clearly inform users that "this optimization is safe".

[0109] Through this concise, aggregated display, lengthy lists that originally required scrolling through dozens of screens are compressed into a few to a dozen representative items. Users can easily grasp the performance bottlenecks and optimization opportunities of the entire table without having to painstakingly sift through massive amounts of repetitive information, thereby significantly improving the usability of diagnostic results and user experience.

[0110] In one embodiment, in response to a triggering operation on a displayed item, the cell area covered by the formula class corresponding to the displayed item is located in the table to be diagnosed.

[0111] Each formula class may correspond to a different location in the table. If a user needs to view the specific context of a formula in the original table (e.g., to check other data in the row containing the formula, or to confirm whether the formula's dependencies are as expected), simply seeing the text in the cell range (e.g., "Sheet1!H2:H6") is not intuitive enough. Therefore, this embodiment provides a location-based interactive function: when a user clicks or hovers over a displayed item (e.g., clicking a region link in the item or any location on the item itself), the system automatically scrolls the table view to the cell range covered by the formula class and highlights that area, for example, by highlighting it (e.g., using a colored border or background color). In this way, the user can immediately see the location of these formulas and the surrounding data in the table, facilitating further review or verification.

[0112] This feature directly links the displayed items in the diagnostic panel to their specific locations in the table, reducing the operational cost for users to manually search for areas. It is especially suitable for scenarios where formulas are scattered or worksheets are large.

[0113] In one embodiment, in response to the user's confirmation and optimization operation for the displayed item, the corresponding candidate optimization formulas are written in batches into all cell areas contained in the formula class corresponding to the displayed item, and the table to be diagnosed is recalculated.

[0114] When a user reviews a displayed item and decides to adopt its optimization suggestions, a simple way is needed to apply the candidate optimization formulas to the actual table. This embodiment provides a direct confirmation entry on the displayed item (e.g., an "Optimize Formula" button or a "One-Click Optimization" switch on the right side of the item). After the user clicks confirm, the system will batch write the corresponding candidate optimization formulas into all cell ranges contained in the formula class corresponding to the displayed item, instead of requiring the user to paste the formulas into each cell individually. Since the formula structure in this formula class is consistent, batch writing can be completely automated: the system instantiates the candidate optimization formula template into candidate optimization formulas according to the original reference offset rules of each cell in the class and then writes them into the corresponding cells. After writing is complete, the system automatically triggers a recalculation of the table, making the new formulas take effect immediately.

[0115] Throughout the process, the system can retain a backup of the original formula (e.g., recorded in memory or hidden comments) so that users can roll back when needed.

[0116] This batch confirmation and write mechanism simplifies events that might otherwise require dozens or hundreds of repetitive operations into a single click, significantly improving the efficiency of optimization and reducing the risk of errors caused by manually modifying each item one by one.

[0117] In practice, formula diagnostic results are not static. After viewing the diagnostic report, users may continue to edit the table, such as modifying a formula's parameter, adding an auxiliary formula column, or deleting rows containing formulas previously marked as time-consuming. These operations alter the formula structure or dependencies in the table, causing previously generated diagnostic results to become inaccurate. If the system continues to use old diagnostic results, it may recommend outdated optimization suggestions to users or miss newly emerging performance issues. Therefore, a mechanism is needed to maintain the timeliness of diagnostic results.

[0118] To address the above requirements, this application provides the following embodiments: in response to a formula change in the table to be diagnosed, the formula diagnosis results are refreshed.

[0119] Specifically, the system continuously monitors changes to all formula areas in the table to be diagnosed. "Formula changes" here include at least one of the following: the user adds a cell containing a formula, modifies the content of an existing formula, deletes a formula, or imports a new formula through copying and pasting. As an optional implementation, when such changes are detected, the system does not immediately re-execute a full table diagnosis (as this could lead to frequent calculations and disrupt the user's ongoing editing operations). Instead, it employs a prompting strategy: for example, displaying a message above the diagnosis results panel stating, "A change has been detected in the table formulas. The diagnosis results may be outdated. Click refresh for the latest suggestions." Users can click the refresh button at an appropriate time to trigger an update of the diagnosis results.

[0120] In another optional implementation, the system can automatically trigger incremental refresh: only the formula regions that have changed and the formulas on the affected calculation chains are re-diagnosed, and the corresponding diagnostic results are updated; the remaining unaffected formulas directly use the results from the previous diagnosis. This incremental refresh method ensures the accuracy of the diagnostic results while avoiding the performance loss caused by a full scan.

[0121] Regardless of the specific method used, the core of this embodiment lies in the fact that the diagnostic results do not remain permanently in the initial state, but are able to sense changes in the table and maintain consistency with the current table state through a refresh mechanism. This effectively avoids users making incorrect optimization decisions based on outdated diagnostic results, thus improving the reliability and practicality of the solution.

[0122] Finally, in enterprise-level or multi-user collaborative scenarios, spreadsheets typically have complex permission settings. To provide diagnostic and optimization functions while ensuring data security, this application embodiment introduces a permission control mechanism.

[0123] In one embodiment, the system first obtains the current user's overall editing permissions for the table to be diagnosed. If the current user is only a "viewer" or "read-only user" and does not have editing permissions, the system disables all optimization execution functions (e.g., the "one-click optimization" button is grayed out, and the "optimize formula" button for individual formulas is unavailable), but the functions for viewing diagnostic results (such as viewing a list of high-time-consuming formulas and locating cells) can be retained to help the user understand the performance issues.

[0124] If the current user has editing permissions but lacks region permissions for certain specific cell ranges (e.g., protected regions in the workbook that the user can only view but not modify), the system will employ the following strategy when handling these regions: For formulas without region permissions, they will still participate in the calculation chain (because they may be depended upon by other formulas with permissions), but the system will not display optimization suggestions for these formulas in the diagnostic results panel, or the corresponding optimization execution function (such as the "Optimize Formula" button) will be grayed out, and a message "No editing permissions for this region" will be displayed. This prevents users from attempting to modify cells they do not have editing privileges for.

[0125] In one embodiment, for formulas located in hidden rows or columns, the system handles them as follows: Because these formulas are hidden, users typically don't directly focus on them, but they may still be relied upon by formulas in other visible cells. Therefore, they must participate in the calculation chain to ensure the correctness of the results of formulas that depend on them. However, when generating diagnostic results, formulas in hidden rows / columns will not appear in the diagnostic panel, nor will optimization suggestions be provided. This ensures calculation accuracy while avoiding displaying invisible or irrelevant content to the user, reducing information interference.

[0126] The aforementioned access control and hidden row / column processing logic enables this technical solution to run securely and compliantly in enterprise-level document collaboration environments, protecting data security while preventing abnormal interruptions to the diagnostic process due to access control issues.

[0127] Figure 5 This is a block diagram illustrating an embodiment of a processing apparatus for formulas in a table, provided in this application. Figure 5 As shown, the device includes: Table acquisition module 51 is used to acquire the table to be diagnosed; Formula calculation module 52 is used to calculate the formula to be diagnosed in the table to be diagnosed and to obtain the calculation characteristics of the formula to be diagnosed. Formula optimization module 53 is used to generate candidate optimization formulas for the formula to be diagnosed based on a preset optimization strategy, and to determine the computational characteristics of the candidate optimization formulas. The optimizable formula determination module 54 is used to mark the formula to be diagnosed as an optimizable formula when it is determined that the formula to be diagnosed meets the preset optimization conditions based on the calculation characteristics of the formula to be diagnosed and the calculation characteristics of the corresponding candidate optimization formulas. Formula diagnosis module 55 is used to generate formula diagnosis results based on the optimizable formula.

[0128] In one possible implementation, the computational features include: computation time and computation result; The preset optimization conditions include: the calculation result of the candidate optimization formula is consistent with the calculation result of the formula to be diagnosed, and the calculation time of the candidate optimization formula is lower than the calculation time of the formula to be diagnosed.

[0129] In one possible implementation, the formula optimization module 53 generates candidate optimized formulas for the formula to be diagnosed based on a preset optimization strategy, including at least one of the following methods: The formula to be diagnosed is matched with a preset set of optimization rules; if the match is successful, a candidate optimization formula is generated based on the matched optimization rules. Candidate optimized formulas are generated based on the trained formula optimization model for the formula to be diagnosed.

[0130] In one possible implementation, the device further includes: The clustering module is used to perform structural clustering on the formulas to be diagnosed in the table to be diagnosed, and to group multiple formulas to be diagnosed that meet the preset structural consistency conditions into the same formula class. The formula optimization module 53 is specifically used for: Based on the preset optimization strategy, candidate optimization formulas are generated for each formula class.

[0131] In one possible implementation, the formula optimization module 53 is specifically used for: Based on the preset optimization strategy, candidate optimization formula templates corresponding to the formula class are generated; Based on the candidate optimization formula template, corresponding candidate optimization formulas are generated for each formula to be diagnosed contained in the formula class.

[0132] In one possible implementation, the optimizable formula determination module 54 is specifically used for: If the calculation results of all formulas to be diagnosed in the formula class are consistent with the calculation results of the candidate optimization formulas, and the calculation time of the candidate optimization formulas of all formulas to be diagnosed in the formula class is lower than the calculation time of all formulas to be diagnosed in the formula class, then all formulas to be diagnosed in the formula class are marked as optimizable formulas.

[0133] In one possible implementation, the formula diagnostic results are displayed one by one by formula class, and each displayed item includes at least one of the following: the original formula type corresponding to the formula class, the cell range covered by the formula class, the comparison information of calculation time before and after optimization, and an identifier indicating that the result consistency verification has passed.

[0134] In one possible implementation, the device further includes: The positioning module is used to locate the cell area covered by the formula class corresponding to the displayed item in the table to be diagnosed in response to the trigger operation of the displayed item.

[0135] In one possible implementation, the device further includes: The first batch optimization module is used to respond to the user's confirmation optimization operation for the displayed item, write the corresponding candidate optimization formulas in batches into all cell areas contained in the formula class corresponding to the displayed item, and trigger the recalculation of the table to be diagnosed.

[0136] In one possible implementation, the device further includes: A single formula optimization module is used to respond to the confirmation optimization operation of a single optimizable formula by writing the corresponding candidate optimization formula into the corresponding cell and triggering the recalculation of the table to be diagnosed. The second batch optimization module is used to respond to the batch confirmation optimization operation of multiple optimizable formulas, write the corresponding multiple candidate optimization formulas into their respective cells, and trigger the recalculation of the table to be diagnosed.

[0137] In one possible implementation, the device further includes: The recovery module is used to record the original formula in the cell before writing the candidate optimization formula into the corresponding cell; In response to the restore command, the original formula is rewritten into the cell.

[0138] In one possible implementation, the device further includes: The refresh module is used to refresh the formula diagnosis result in response to a change in the formula in the table to be diagnosed.

[0139] In one possible implementation, the refresh module is specifically used for: An incremental update approach is used to re-perform diagnostics on the formula regions where formula changes have occurred and the formulas on the affected calculation chains, and to update the corresponding formula diagnostic results.

[0140] In one possible implementation, the table acquisition module 51 is specifically used for: In response to a diagnostic command, a table to be diagnosed is retrieved; the diagnostic command is triggered in at least one of the following ways: The user triggers the diagnostic command in the table; The diagnostic instruction is generated when it is detected that the calculation time of any formula in the table exceeds a preset threshold and the formula belongs to a preset optimizable type.

[0141] like Figure 6 As shown in the figure, this application provides an electronic device, including a processor 111, a communication interface 112, a memory 113, and a communication bus 114, wherein the processor 111, the communication interface 112, and the memory 113 communicate with each other through the communication bus 114. Memory 113 is used to store computer programs; In one embodiment of this application, when the processor 111 executes the program stored in the memory 113, it implements the processing method for the formulas in the table provided in any of the foregoing method embodiments, including: Obtain the diagnosis form; The formula to be diagnosed in the table to be diagnosed is calculated, and the calculation characteristics of the formula to be diagnosed are obtained; Based on a preset optimization strategy, candidate optimization formulas for the formula to be diagnosed are generated, and the computational characteristics of the candidate optimization formulas are determined. If, based on the computational characteristics of the formula to be diagnosed and the computational characteristics of the corresponding candidate optimization formula, it is determined that the formula to be diagnosed meets the preset optimization conditions, the formula to be diagnosed is marked as an optimizable formula. Based on the aforementioned optimizable formula, a formula diagnostic result is generated.

[0142] This application also provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the steps of the processing method for the formulas in the table provided in any of the foregoing method embodiments.

[0143] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0144] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented using software plus a general-purpose hardware platform, or of course, using hardware. Based on this understanding, the above technical solutions, in essence or the parts that contribute to the related technology, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0145] It should be understood that the terminology used herein is for the purpose of describing particular exemplary embodiments only and is not intended to be limiting. Unless the context clearly indicates otherwise, the singular forms “a,” “an,” and “described” as used herein may also include the plural forms. The terms “comprising,” “including,” “containing,” and “having” are inclusive and therefore indicate the presence of the stated features, steps, operations, elements, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, elements, components, and / or combinations thereof. The method steps, processes, and operations described herein are not construed as requiring them to be performed in a particular order described or illustrated unless the order of performance is explicitly indicated. It should also be understood that additional or alternative steps may be used.

[0146] The above description is merely a specific embodiment of this application, enabling those skilled in the art to understand or implement this application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of this application. Therefore, this application is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features claimed herein.

Claims

1. A method for processing formulas in a table, characterized in that, The method includes: Obtain the diagnosis form; The formula to be diagnosed in the table to be diagnosed is calculated, and the calculation characteristics of the formula to be diagnosed are obtained; Based on a preset optimization strategy, candidate optimization formulas for the formula to be diagnosed are generated, and the computational characteristics of the candidate optimization formulas are determined. If, based on the computational characteristics of the formula to be diagnosed and the computational characteristics of the corresponding candidate optimization formula, it is determined that the formula to be diagnosed meets the preset optimization conditions, the formula to be diagnosed is marked as an optimizable formula. Based on the aforementioned optimizable formula, a formula diagnostic result is generated.

2. The method according to claim 1, characterized in that, The computational characteristics include: computation time and computation results; The preset optimization conditions include: the calculation result of the candidate optimization formula is consistent with the calculation result of the formula to be diagnosed, and the calculation time of the candidate optimization formula is lower than the calculation time of the formula to be diagnosed.

3. The method according to claim 1, characterized in that, The generation of candidate optimization formulas for the formula to be diagnosed based on a preset optimization strategy includes at least one of the following methods: The formula to be diagnosed is matched with a preset set of optimization rules; If a match is successful, a candidate optimization formula is generated based on the matching optimization rules for the formula to be diagnosed. Candidate optimized formulas are generated based on the trained formula optimization model for the formula to be diagnosed.

4. The method according to claim 1, characterized in that, The method further includes: Structural clustering is performed on the formulas to be diagnosed in the table to be diagnosed, and multiple formulas to be diagnosed that meet the preset structural consistency conditions are grouped into the same formula class. The process of generating candidate optimization formulas for the formula to be diagnosed based on a preset optimization strategy includes: Based on the preset optimization strategy, candidate optimization formulas are generated for each formula class.

5. The method according to claim 4, characterized in that, The generation of candidate optimization formulas corresponding to the formula class based on the preset optimization strategy includes: Based on the preset optimization strategy, candidate optimization formula templates corresponding to the formula class are generated; Based on the candidate optimization formula template, corresponding candidate optimization formulas are generated for each formula to be diagnosed contained in the formula class.

6. The method according to claim 4, characterized in that, The step of marking the formula to be diagnosed as an optimizable formula when it is determined, based on the computational characteristics of the formula to be diagnosed and the computational characteristics of the corresponding candidate optimization formulas, that the formula to be diagnosed meets the preset optimization conditions includes: If the calculation results of all formulas to be diagnosed in the formula class are consistent with the calculation results of the candidate optimization formulas, and the calculation time of the candidate optimization formulas of all formulas to be diagnosed in the formula class is lower than the calculation time of all formulas to be diagnosed in the formula class, then all formulas to be diagnosed in the formula class are marked as optimizable formulas.

7. The method according to claim 4, characterized in that, The formula diagnostic results are displayed one by one by formula class. Each displayed item includes at least one of the following: the original formula type corresponding to the formula class, the cell range covered by the formula class, the comparison information of calculation time before and after optimization, and the identifier that the result consistency verification has passed.

8. The method according to claim 7, characterized in that, The method further includes: In response to a trigger operation on the displayed item, the cell range covered by the formula class corresponding to the displayed item is located in the table to be diagnosed.

9. The method according to claim 7, characterized in that, The method further includes: In response to the user's confirmation and optimization operation for the displayed item, the corresponding candidate optimization formula is written in batches into all cell ranges contained in the formula class corresponding to the displayed item, and the table to be diagnosed is recalculated.

10. The method according to claim 1, characterized in that, The method further includes: In response to the confirmation optimization operation of a single optimizable formula, the corresponding candidate optimization formula is written into the corresponding cell, and the table to be diagnosed is recalculated. And / or, in response to the batch confirmation optimization operation of multiple optimizable formulas, the corresponding multiple candidate optimization formulas are written into their respective cells, and the table to be diagnosed is recalculated.

11. The method according to claim 10, characterized in that, The method further includes: Before writing the candidate optimization formula into the corresponding cell, record the original formula in the cell; In response to the restore command, the original formula is rewritten into the cell.

12. The method according to claim 1, characterized in that, The method further includes: In response to a formula change in the table to be diagnosed, the formula diagnosis result is refreshed.

13. The method according to claim 11, characterized in that, The process of refreshing the formula diagnostic results includes: An incremental update approach is used to re-perform diagnostics on the formula regions where formula changes have occurred and the formulas on the affected calculation chains, and to update the corresponding formula diagnostic results.

14. The method of claim 1, wherein, The process of obtaining the table to be diagnosed includes: In response to a diagnostic command, a table to be diagnosed is retrieved; the diagnostic command is triggered in at least one of the following ways: The user triggers the diagnostic command in the table; If the calculation time of any formula in the table exceeds a preset threshold and the formula belongs to a preset optimizable type, the diagnostic instruction is generated.

15. An apparatus for processing a formula in a table, characterized by The device includes: The table retrieval module is used to retrieve the table to be diagnosed. The formula calculation module is used to calculate the formula to be diagnosed in the table to be diagnosed and to obtain the calculation characteristics of the formula to be diagnosed. The formula optimization module is used to generate candidate optimized formulas for the formula to be diagnosed based on a preset optimization strategy, and to determine the computational characteristics of the candidate optimized formulas. An optimizable formula determination module is used to mark the formula to be diagnosed as an optimizable formula when the formula to be diagnosed meets preset optimization conditions based on the calculation characteristics of the formula to be diagnosed and the calculation characteristics of the corresponding candidate optimization formulas. The formula diagnosis module is used to generate formula diagnosis results based on the optimizable formula.

16. An electronic device, comprising: include: A processor and a memory, the processor being configured to execute a processing program for formulas in a table stored in the memory, to implement the method for processing formulas in a table as described in any one of claims 1-14.

17. A storage medium, characterized by The storage medium stores one or more programs, which can be executed by one or more processors to implement the processing method of the formulas in the table according to any one of claims 1-14.

Images