Method and system for operating Excel table through natural language

Abstract

The invention relates to a method and a system for operating an Excel table through a natural language. The method comprises the following steps: acquiring the natural language in which a user needs to operate the Excel table; converting the natural language into a training set by using a python script; the method comprises the following steps: selecting a model Transform based on pre-training as a base, and integrating a learnable function layer to construct an NLToExcel model; training an NLToExcel model based on a natural language instruction of a Prompt technology and a large model fine tuning technology; according to the method, the NLToExcel model is trained to process the natural language instruction, the effectiveness of an Excel table processing function generated by the model is verified, and the Excel table operation is executed according to an operation function output by the NLToExcel model. According to the method, the NLToExcel model is trained to process the natural language instruction, new Excel operation types and requirements can be more easily adapted, and a large amount of manual adjustment is not needed.

Description

Technical Field

[0001] The invention relates to a method and system for operating an Excel table through natural language, belonging to the technical field of data processing. Background Art

[0002] With the advent of the big data era, Excel, as a widely used spreadsheet software, plays an important role in data processing and analysis. However, the traditional Excel operation method relies on the user's knowledge of Excel functions and programming ability, which limits the efficient use of Excel data by non-professionals. Excel spreadsheet operations involve a variety of instructions, such as data filtering, sorting, calculation, chart generation, etc. The diversity and complexity of these instructions increase the difficulty of the system to understand and execute user instructions.

[0003] The patent document with the patent number "CN101593244A" discloses an assessment method for Excel operation questions. The problem with this method is that it mainly relies on pre-set scoring points and scoring criteria. Although this method can ensure the accuracy of scoring, it lacks flexibility. If the type or requirements of the Excel operation question change, it may be necessary to reset a large number of scoring points and scoring criteria, which is a lot of work. When the test answer is different from the scoring conditions, external input of the scoring score is required. This means that manual intervention is required in the scoring process, which increases the labor cost and time cost of the scoring work. Summary of the invention

[0004] In order to solve the above problems existing in the prior art, the present invention proposes a method and system for operating an Excel spreadsheet through natural language.

[0005] The technical solution of the present invention is as follows:

[0006] In one aspect, the present invention provides a method for operating an Excel table through natural language, comprising the following steps:

[0007] Obtain the natural language that the user needs to operate the Excel table;

[0008] Use Python script to convert the natural language into a training set;

[0009] Select the pre-trained model Transformer as the base, and integrate the learnable function layer to build the NLToExcel model; fine-tune the NLToExcel model using the training set based on the Prompt technology, and the NLToExcel model is used to output the operation function of the Excel table;

[0010] Verify the operation functions generated by the NLToExcel model, and perform Excel table operations according to the operation functions output by the NLToExcel model.

[0011] As a preferred implementation, the learnable function layer f(x) is expressed as:

[0012]

[0013] Among them, L represents the network depth, N represents the width of each layer, Represents an external function.

[0014] As a preferred implementation, the method of fine-tuning training is:

[0015] Preset model prompt words;

[0016] Use Prompt technology to match the model prompt words corresponding to the training set;

[0017] The model prompt words and training set are input into the NLToExcel model for fine-tuning training, and the parameters of the learnable function are updated by finding the optimal edge activation function;

[0018] The activation function is expressed as:

[0019]

[0020] Among them, φ 1,j,i represents the activation value, b m represents the weight, represents a variable, and w represents a constant term.

[0021] As a preferred implementation, the verification method of the operation function includes syntax verification, logic verification and boundary condition verification;

[0022] The syntax verification includes checking whether the function name is correct, whether the parameters match, and whether the brackets are paired correctly;

[0023] The logic verification includes applying the function to sample data or actual data provided by the user to check whether the output result of the function conforms to the preset business logic;

[0024] The boundary condition verification includes considering whether there are errors or exceptions in the function behavior under the boundary conditions.

[0025] As a preferred implementation, it also includes a function optimization mechanism, which includes performance optimization and automation optimization.

[0026] In another aspect, the present invention further provides a system for operating an Excel spreadsheet through natural language, comprising:

[0027] Data collection module: obtain the natural language that users need to operate on Excel tables;

[0028] Data processing module: using Python script to convert the natural language into training set;

[0029] Model training module: select the pre-trained model Transformer as the base, and integrate the learnable function layer to build the NLToExcel model; fine-tune the NLToExcel model using the training set based on the Prompt technology, and the NLToExcel model is used to output the operation function of the Excel table;

[0030] Model verification module: Verify the operation functions generated by the NLToExcel model and perform Excel table operations according to the operation functions output by the NLToExcel model.

[0031] The present invention has the following beneficial effects:

[0032] The present invention accelerates model convergence and enhances model interpretability by introducing a learnable function layer. The variable activation function in the learnable function layer significantly improves the model's ability to deeply understand natural language descriptions and the accuracy of generating Excel functions compared to a single activation function. The present invention introduces a dynamic adaptive Prompt generation and guidance technology, which is a major improvement over the traditional static Prompt method. The technology can dynamically adjust the Prompt according to the user's real-time input and context environment, ensuring that the system can more accurately understand the user's natural language description, and generate more accurate and demand-oriented Excel functions accordingly. This dynamic adaptive capability not only improves the system's intelligence level and generation accuracy, but also reduces the user's usage threshold and learning cost, and enhances the user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] Figure 1 The present invention is a flowchart for implementing the method.

[0034] Figure 2 This is a structural diagram of the Prompt technology generation and guidance module of the present invention. DETAILED DESCRIPTION

[0035] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0036] It should be understood that the step numbers used in this document are only for convenience of description and are not intended to limit the order in which the steps are executed.

[0037] It should be understood that the terms used in the present specification are only for the purpose of describing specific embodiments and are not intended to limit the present invention. As used in the present specification and the appended claims, unless the context clearly indicates otherwise, the singular forms "a", "an" and "the" are intended to include plural forms.

[0038] The terms “include” and “comprising” indicate the presence of described features, integers, steps, operations, elements and / or components, but do not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or combinations thereof.

[0039] The term "and / or" means and includes any and all possible combinations of one or more of the associated listed items.

[0040] Embodiment 1:

[0041] See also Figure 1 On the one hand, the present invention provides a method for operating an Excel table through natural language, comprising the following steps:

[0042] Obtain the natural language that the user needs to operate the Excel table;

[0043] Use Python scripts to convert the natural language into training sets; use manual annotation and large model generation to collect a large amount of mapping data between the operations described by users in natural language and the corresponding Excel functions or operation instructions, and convert the collected raw data into the data format required for model training through Python scripts. The data should cover common Excel operations, such as data sorting, filtering, formula calculation, etc. Clean and annotate the data to ensure data quality and consistency.

[0044] Choose the pre-trained model Transformer as the base, and integrate the learnable function layer to build the NLToExcel model (NLToExcel model is a model trained from the self-named deep learning model architecture transformer model); fine-tune the NLToExcel model based on the Prompt technology using the training set, and the NLToExcel model is used to output the operation function of the Excel table; this step requires the integration of layers according to the input and output dimensions of the model and the number of internal nodes, so that the data flow between the integrated part and the other parts of the transformer model is normal. Configure the learning rate, batch size, and training rounds of the model. On the transformer infrastructure, embed the learnable function layer between the normalization layer and the feedforward layer. The purpose of this is to replace the single activation function of the multi-layer perceptron layer that cannot achieve model optimization and fast convergence. Each cycle of training is to find a better edge activation function.

[0045] Verify the operation functions generated by the NLToExcel model, and perform Excel table operations based on the operation functions output by the NLToExcel model. Verify the operation functions generated by the NLToExcel model, and perform Excel table operations based on the operation functions output by the NLToExcel model. Evaluate the performance of the fine-tuned model on the test set, including indicators such as accuracy and recall. Select the optimal model version for saving and deployment based on the evaluation results.

[0046] See also Figure 2 ,PROBLEM represents a clear expression of the problem to be solved, PROBLEM is the original problem, and PROBLEM1 is the problem input converted into a fine-tuned data format after integrating the table information;

[0047] TABLE represents the original Excel table;

[0048] FAISS: represents the Excel table knowledge vector retrieval model, which narrows the search scope of Excel functions;

[0049] AERA represents the role that the large model needs to play and the relevant domain knowledge of Excel after screening by the small model;

[0050] TASK stands for task description, task decomposition, workflow design, and the specific tasks that need to be performed to solve this problem;

[0051] FORMAT specifies the output format and restrictions.

[0052] As a preferred implementation, the learnable function layer f(x) is expressed as:

[0053]

[0054] Among them, L represents the network depth, N represents the width of each layer, Represents an external function.

[0055] As a preferred implementation, the method of fine-tuning training is:

[0056] Preset model prompt words;

[0057] Use Prompt technology to match the model prompt words corresponding to the training set;

[0058] The model prompt words and training set are input into the NLToExcel model for fine-tuning training, and the parameters of the learnable function are updated by finding the optimal edge activation function;

[0059] The activation function is expressed as:

[0060]

[0061] Among them, φ 1,j,i represents the activation value, b m represents the weight, represents a variable, and w represents a constant term.

[0062] As a preferred implementation, the verification method of the operation function includes syntax verification, logic verification and boundary condition verification;

[0063] The syntax verification includes checking whether the function name is correct, whether the parameters match, and whether the brackets are correctly paired; through the built-in syntax checker or regular expression tools, the system can quickly identify and point out syntax errors, ensuring that the function can be executed normally in Excel and avoiding operation failures caused by syntax errors.

[0064] The logic verification includes applying the function to sample data or actual data provided by the user to check whether the output result of the function conforms to the preset business logic; ensuring that the function can accurately reflect the actual needs of the user and solve practical problems.

[0065] The boundary condition verification includes considering whether there are errors or exceptions in the function behavior under boundary conditions (such as extreme values, null values, etc.). By setting test cases for these boundary conditions, the system can ensure that the function can run stably in various situations. Improve the robustness and reliability of the function and avoid errors or exceptions in specific situations.

[0066] As a preferred implementation, it also includes a function optimization mechanism, which includes performance optimization and automation optimization. Performance optimization means that after confirming that the function logic is correct, the system can optimize the function performance. This includes simplifying the calculation process, reducing unnecessary calculation steps, and using Excel's built-in functions and features. It improves the execution efficiency of the function, especially when processing large-scale data, reducing calculation time and resource consumption.

[0067] Automated Optimization Combining machine learning and artificial intelligence technology, the system can automatically analyze and optimize the generated Excel functions. By analyzing the usage data and feedback information of a large number of users, the system can find potential optimization points and automatically adjust the structure and parameters of the function. It realizes the intelligent and automated generation of functions and improves the quality and efficiency of generated functions.

[0068] Embodiment 2:

[0069] The present invention also provides a system for operating an Excel table through natural language, comprising:

[0070] Data collection module: obtain the natural language that users need to operate on Excel tables;

[0071] Data processing module: using Python script to convert the natural language into training set;

[0072] Model training module: select the pre-trained model Transformer as the base, and integrate the learnable function layer to build the NLToExcel model; fine-tune the NLToExcel model using the training set based on the Prompt technology, and the NLToExcel model is used to output the operation function of the Excel table;

[0073] Model verification module: Verify the operation functions generated by the NLToExcel model and perform Excel table operations according to the operation functions output by the NLToExcel model.

[0074] In the embodiments of the present application, "at least one" refers to one or more, and "more than one" refers to two or more. "And / or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and / or B can represent the existence of A alone, the existence of A and B at the same time, and the existence of B alone. Among them, A and B can be singular or plural. The character " / " generally indicates that the previous and next associated objects are in an "or" relationship. "At least one of the following" and similar expressions refer to any combination of these items, including any combination of single or plural items. For example, at least one of a, b and c can be represented by: a, b, c, a and b, a and c, b and c, or a and b and c, where a, b, c can be single or multiple.

[0075] Those of ordinary skill in the art will appreciate that the various units and algorithm steps described in the embodiments disclosed herein can be implemented in a combination of electronic hardware, computer software, and electronic hardware. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of this application.

[0076] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working processes of the systems, devices and units described above can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0077] In several embodiments provided in the present application, if any function is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application can be essentially or partly embodied in the form of a software product that contributes to the prior art. The computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (Read-Only Memory; hereinafter referred to as: ROM), random access memory (Random Access Memory; hereinafter referred to as: RAM), disk or optical disk, and other media that can store program codes.

[0078] The above descriptions are merely embodiments of the present invention and are not intended to limit the patent scope of the present invention. Any equivalent structure or equivalent process transformation made using the contents of the present invention specification and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present invention.

Claims

1. A method for operating an Excel table through natural language, characterized in that: The following steps are involved: Obtain the natural language that the user needs to operate the Excel table; Use Python script to convert the natural language into a training set; Select the pre-trained model Transformer as the base, and integrate the learnable function layer to build the NLToExcel model; fine-tune the NLToExcel model using the training set based on the Prompt technology, and the NLToExcel model is used to output the operation function of the Excel table; Verify the operation functions generated by the NLToExcel model, and perform Excel table operations according to the operation functions output by the NLToExcel model.

2. The method for operating an Excel table through natural language according to claim 1, characterized in that: The learnable function layer f(x) is expressed as: Among them, L represents the network depth, N represents the width of each layer, Represents an external function.

3. The method for operating an Excel table through natural language according to claim 1, characterized in that: The method of fine-tuning training is: Preset model prompt words; Use Prompt technology to match the model prompt words corresponding to the training set; The model prompt words and training set are input into the NLToExcel model for fine-tuning training, and the parameters of the learnable function are updated by finding the optimal edge activation function; The activation function is expressed as: Among them, φ l,j,i represents the activation value, b m represents the weight, represents a variable, and w represents a constant term.

4. The method for operating an Excel table through natural language according to claim 1, characterized in that: The verification method of the operation function includes syntax verification, logic verification and boundary condition verification; The syntax verification includes checking whether the function name is correct, whether the parameters match, and whether the brackets are paired correctly; The logic verification includes applying the function to sample data or actual data provided by the user to check whether the output result of the function conforms to the preset business logic; The boundary condition verification includes considering whether there are errors or exceptions in the function behavior under the boundary conditions.

5. The method for operating an Excel table through natural language according to claim 1, characterized in that: It also includes a function optimization mechanism, which includes performance optimization and automation optimization.

6. A system for operating Excel tables through natural language, characterized in that: include: Data collection module: obtain the natural language that users need to operate on Excel tables; Data processing module: using Python script to convert the natural language into training set; Model training module: select the pre-trained model Transformer as the base, and integrate the learnable function layer to build the NLToExcel model; fine-tune the NLToExcel model using the training set based on the Prompt technology, and the NLToExcel model is used to output the operation function of the Excel table; Model verification module: Verify the operation functions generated by the NLToExcel model and perform Excel table operations according to the operation functions output by the NLToExcel model.

7. The system for operating an Excel table through natural language according to claim 6, characterized in that: The model training module can be expressed as: Among them, L represents the network depth, N represents the width of each layer, Represents an external function.

8. The system for operating an Excel table through natural language according to claim 1, characterized in that: The model training module, the method of fine-tuning training is: Preset model prompt words; Use Prompt technology to match the model prompt words corresponding to the training set; The model prompt words and training set are input into the NLToExcel model for fine-tuning training, and the parameters of the learnable function are updated by finding the optimal edge activation function; The activation function is expressed as: Among them, φ l,j,i represents the activation value, b m represents the weight, represents a variable, and w represents a constant term.

9. The system for operating an Excel spreadsheet by natural language according to claim 6, characterized in that: The model verification module, the verification method of the operation function includes syntax verification, logic verification and boundary condition verification; The syntax verification includes checking whether the function name is correct, whether the parameters match, and whether the brackets are paired correctly; The logic verification includes applying the function to sample data or actual data provided by the user to check whether the output result of the function conforms to the preset business logic; The boundary condition verification includes considering whether there are errors or exceptions in the function behavior under the boundary conditions.

10. The system for operating an Excel table through natural language according to claim 6, characterized in that: It also includes a function optimization mechanism, which includes performance optimization and automation optimization.

Images