Question and answer method and device based on Excel multilevel header and medium

Abstract

The invention relates to the technical field of model questioning and answering, in particular to a questioning and answering method and device based on an Excel multilevel header and a medium, the method comprises the following steps: inputting pre-acquired abstract prompt information into a preset large language model to identify at least one target form corresponding to a preset question text from a to-be-processed form file, calling a form structure analysis tool to analyze the target form, obtaining header structure data corresponding to the target form, constructing code generation prompt information, inputting the information into a preset large language model, generating a data analysis code for answering a preset question text, and sending the data analysis code to a preset sandbox environment, obtaining and outputting a statistical result corresponding to the preset question text; the method can overcome the defect that the model cannot understand the complex table and the problem that the model cannot be input or output under the condition of super word number, and improves the question and answer quality of the complex table.

Description

Technical Field

[0001] This invention relates to the field of model question answering technology, and in particular to a question answering method, device and medium based on multi-level Excel headers. Background Technology

[0002] With the rapid development of large language model technology, it has demonstrated powerful capabilities in natural language processing and content generation, providing a new paradigm for automated data querying and statistical analysis. Currently, the industry generally attempts to directly enable large language models to understand and process natural language questions posed by users regarding tabular data and generate answers or analytical conclusions. However, when this model is applied to complex Excel spreadsheets commonly found in real-world business scenarios, especially those with multi-level headers, large language models cannot accurately understand the content of complex forms, resulting in low accuracy of output results. Furthermore, due to the character limit for input and output of large language models, and the fact that tables often contain massive amounts of data, converting the entire table data into text before inputting it into the model can easily trigger input limits, leading to task failure. Summary of the Invention

[0003] To address the aforementioned technical problems, this invention provides a question-and-answer method, device, and medium based on multi-level Excel headers. This overcomes the limitations of models in understanding complex tables and the problem of models being unable to input or output when the character limit is exceeded. It improves the question-and-answer quality for complex tables and facilitates accurate analysis and statistics of table data.

[0004] According to a first aspect of the present invention, a question-and-answer method based on an Excel multi-level header is provided, comprising the following steps: S1, input the pre-acquired summary prompt information into the preset large language model, and identify at least one target form corresponding to the preset question text from several original forms corresponding to the table file to be processed through the preset large language model; the summary prompt information includes the preset question text and the summary content extracted by the user based on the table file to be processed.

[0005] S2, invoke a pre-built form structure parsing tool to parse the target form and obtain the header structure data corresponding to the target form; the header structure data is data representing cell merging relationships and hierarchical relationships.

[0006] S3, construct code generation prompt information, input the code generation prompt information into the preset large language model, and generate data analysis code for answering the preset question text; the code generation prompt information includes the header structure data corresponding to the target form, the preset question text, the preset code generation specification, and the preset list of callable functions.

[0007] S4. Send the data analysis code to the preset sandbox environment so that the data analysis code can be executed in the preset sandbox environment and the data of the target form can be processed and analyzed to obtain and output the statistical results corresponding to the preset question text.

[0008] According to a second aspect of the present invention, a non-transitory computer-readable storage medium is provided, wherein at least one instruction or at least one program is stored therein, the at least one instruction or the at least one program being loaded and executed by a processor to implement the above-described question-and-answer method based on an Excel multi-level header.

[0009] According to a third aspect of the present invention, an electronic device is provided, including a processor and the aforementioned non-transitory computer-readable storage medium.

[0010] The present invention has at least the following beneficial effects: This invention provides a question-answering method based on multi-level headers in Excel. First, a pre-acquired summary hint information containing the summary content of the table file to be processed is input into a preset large language model. This identifies at least one target form corresponding to the preset question text from several original forms corresponding to the table file, narrowing the scope of subsequent data processing. Furthermore, inputting the summary hint information avoids exceeding the character limit when the table file is too large. Next, a form structure parsing tool is called to parse the target form, obtaining the corresponding header structure data and converting the target form from table format to text format. This overcomes the model's inability to understand complex tables with multi-level headers. Then, code is constructed to generate hint information and input into the preset large language model to generate data analysis code for answering the preset question text. Finally, the data analysis code is sent to a preset sandbox environment to output the statistical results corresponding to the preset question text. By combining the preset large language model with the sandbox, the method overcomes the model's inability to understand complex tables and the problem of the model being unable to input or output when the character limit is exceeded, improving the question-answering quality for complex tables and facilitating accurate data statistics for tables. Attached Figure Description

[0011] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0012] Figure 1 A flowchart illustrating a question-and-answer method based on an Excel multi-level header provided in an embodiment of the present invention. Detailed Implementation

[0013] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0014] This invention provides a question-and-answer method based on multi-level headers in Excel, such as... Figure 1 As shown, the method includes the following steps: S1: Input the pre-acquired summary prompt information into a preset large language model. The preset large language model identifies at least one target form corresponding to the preset question text from several original forms corresponding to the table file to be processed. This can be understood as the table file to be processed being a pre-defined Excel file. In specific implementations, it is not necessary to input the table file to be processed into the large language model. Instead, the selection of the target form corresponding to the preset question text is achieved by inputting the summary content extracted by the user from the table file in the summary prompt information. For example, the preset question text could be: "Calculate the human resources corresponding to certain projects from the table file to be processed and sort them from highest to lowest quantity."

[0015] Specifically, the summary prompt information includes a preset question text and a summary extracted by the user based on the table file to be processed. The summary content includes the name of each original form extracted from the table file, the header information of each original form, and the data content corresponding to the first preset number of records in each original form. This can be understood as the header information including the content of each field within the header area of ​​the original form. Those skilled in the art can set the preset number according to actual needs; for example, the information contained in the first 5-10 records is sufficient for filtering the target form. It should be noted that, except for the header area, each row in the table file represents one record.

[0016] Furthermore, the identification steps for the target form are as follows: S101, based on the summary content, calculate the relevance score between each original form and the preset question text using a preset large language model; wherein, the relevance score between any original form and the preset question text meets the following conditions: P = W1 × S1 + W2 × S2 + W3 × S3, where P represents the relevance score between any original form and the preset question text; S1, S1, and S3 are the semantic similarity, keyword matching, and metadata matching between the summary content corresponding to any original form and the preset question text, respectively; and W1, W1, and W3 are the preset weights corresponding to S1, S1, and S3, respectively. The preset weights can be learned by the model or pre-set.

[0017] Specifically, semantic similarity is obtained by calculating the cosine similarity between the vectors corresponding to the summary content and the preset question text. Those skilled in the art are familiar with how cosine similarity is calculated, so it will not be elaborated here.

[0018] Specifically, keyword matching degree refers to the percentage of key entities extracted from the preset question text that appear in the corresponding summary content of the table file to be processed. For example, key entities can be project names, years, human resources, etc.

[0019] Preferably, the metadata matching degree is obtained by comparing the keywords in the preset question text with the table header type. For example, when the preset question text contains statistics, summation, or averaging, forms with numerical columns will score higher, such as number of people, working hours, and budget; when the preset question text is a list of personnel, forms with text columns such as personnel name and position will score higher.

[0020] S102, identify the original form whose corresponding relevance score is greater than the preset score threshold as the target form.

[0021] As described above, when filtering target forms, it is not necessary to input the form file to be processed into the large language model. Only the summary prompt information needs to be input, which avoids the situation where the input exceeds the word limit when the form file is too large. At the same time, it overcomes the situation where the model cannot understand complex tables with multi-level headers, which significantly reduces the difficulty of model understanding. Furthermore, three dimensions, namely semantic similarity, keyword matching degree and metadata matching degree, are introduced when identifying target forms, realizing a deep integration of question text and table structure information, improving the reliability of target form filtering, and effectively narrowing the scope of data processing by filtering target forms.

[0022] In one embodiment, the following steps are included before step S1: S01, for each original form, identify the field names in the original form that do not conform to the preset naming convention as fields to be standardized.

[0023] Specifically, the situations that do not conform to the preset naming conventions include: field names being meaningless placeholder characters, having a large semantic difference from the data in the table, or having columns with the same field name.

[0024] S02, based on the data content within the original form, determine the business category corresponding to the original form using a preset classification model, and obtain the preset standard field name set corresponding to the business category. Those skilled in the art are familiar with the training method of the preset classification model, and can apply it directly here, so it will not be described in detail.

[0025] S03, for each column data corresponding to the field to be standardized, calculate the comprehensive matching degree between the column data and each candidate standard field name in the preset standard field name set, and assign the candidate standard field name to the corresponding column according to the comprehensive matching degree, so as to replace the field to be standardized and generate the original form with the header field standardized.

[0026] Specifically, the comprehensive matching degree is obtained by weighting the semantic relevance and data feature matching degree; it can be understood as: calculating the semantic relevance and data feature matching degree between the column data and each candidate standard field name in the preset standard field name set; wherein, the semantic relevance can be calculated by cosine distance; the data feature matching degree is calculated by comparing at least one of the column data's value range, data type, data format, and statistical characteristics with the expected data features of the candidate standard field name, for example, by using any of the following methods: rule matching, classifier, or similarity function.

[0027] Furthermore, the standardized original form can be sent to the user for confirmation, so that after user confirmation, the standardized original form can be used as the original form for subsequent processing.

[0028] As described above, by adding standardized preprocessing steps for table header fields, the problem of model comprehension bias caused by non-standard field names is effectively solved. This fundamentally improves the accuracy of the large language model's understanding of table structures and the reliability of generated code, and enhances the question-answering system's adaptability to diverse and low-quality data sources and its output reliability.

[0029] In one embodiment, the method further includes the following steps: S001, Obtain the complexity of each original form in the table file to be processed; wherein, the complexity meets the following conditions: Where C represents the complexity value, D is the number of rows in the original form, L is the number of header levels in the original form, M is the number of merged cells in the header of the original form, and D max L max M max These are the preset reference values ​​for the maximum number of rows, the maximum number of levels, and the maximum number of merged cells, respectively. α and β are preset weighting coefficients and α+β=1. S002, if the complexity of each original form is less than a preset complexity threshold, then each original form is input into the preset large language model via image recognition or text conversion to identify the target form corresponding to the preset question text; otherwise, step S1 is executed to identify the target form corresponding to the preset question text. Those skilled in the art can set the preset complexity threshold according to actual needs, which will not be elaborated here.

[0030] As described above, by introducing the complexity of data scale and table header structure, the system can intelligently assess the table complexity that matches business needs and adaptively select the processing path. This achieves intelligent selection of table processing paths. When the table complexity is low, lightweight image recognition or text conversion methods are used for processing, significantly reducing computing resource consumption and response latency. Only when the table complexity is high is a complete multi-step parsing process employed. This ensures that the system's processing efficiency and resource utilization are significantly improved while maintaining the capability to process complex tables.

[0031] S2, invoke a pre-built form structure parsing tool to parse the target form and obtain the header structure data corresponding to the target form; the header structure data is data representing cell merging relationships and hierarchical relationships.

[0032] Specifically, the form structure parsing tool is a program or function library used to parse table data with at least two levels of headers.

[0033] Furthermore, the parsing steps of the form structure parsing tool are as follows: S201: Obtain the maximum number of layers in the header area of ​​the target form, and split and fill the columns with fewer cells than the maximum number of layers to construct a two-dimensional header network. In the specific implementation, when splitting any target cell, the column with the maximum number of layers is used as the benchmark. Based on the number of cells in the column with the maximum number of layers corresponding to the area between the top and bottom of the target cell, the target cell is split into the same number of cells. The split cells are horizontally aligned with the cells in the column with the maximum number of layers. Then, for the split cells, the fields in the cells before splitting are used to fill the split cells.

[0034] S202: Using the bottom layer of the two-dimensional header network as the base layer, the system traces back up the number of fields covered by each cell in each layer of the base layer. When the percentage of the total number of fields covered by the layer to be judged exceeds a preset threshold, the layer to be judged is determined to be a valid layer. The technical personnel can set the preset threshold according to actual needs, for example, 50%-60%.

[0035] S203: Sort the determined effective layers from top to bottom into a first-level header to an n-level header. For each filled field, only the content of the corresponding last-level field is retained to obtain the final data information corresponding to each level header. Among them, the field content of the cell for which no field is retained is described as a null value in the final data information.

[0036] S204: Based on the final data information corresponding to each level of the header, fill in the fields of the preset header data filling template to generate the header structure data corresponding to the target form. Specifically, the preset header data filling template includes the filling in of header names at several levels, and the filling in of field names corresponding to each level of the header. For example, the filled template is as follows: the first-level header includes field 1, field 2, and null value; the second-level header includes field 3, field 4, and null value; the third-level header includes field 5, field 6, field 7, etc.

[0037] The above describes how a form structure parsing tool is used to parse the target form. During the parsing process, a series of operations, such as cell splitting, field filling, finding valid rows, and field sinking, are performed to obtain the final data information. This converts the target form from a table format into a text format, enabling the model to understand the multi-level headers of complex tables, which in turn facilitates accurate data statistics for complex tables.

[0038] S3, construct code generation prompt information, input the code generation prompt information into the preset large language model, and generate data analysis code for answering the preset question text; the code generation prompt information includes, but is not limited to, the header structure data corresponding to the target form, the preset question text, the preset code generation specification, and the preset list of callable functions.

[0039] Furthermore, the preset code generation specifications include stipulating the programming language used in the data analysis code, prohibited code libraries, code comment requirements, and data output format.

[0040] As described above, by obtaining the table header structure data and incorporating it into the code generation prompts, the prompts input to the model can be enhanced, making the data analysis code generated by the model more accurate. Furthermore, preset question texts have been added, enabling the data analysis code generated by the model to be specifically used to answer preset questions. In addition, preset code generation specifications have been added, improving the success rate of code generation and reducing the error rate.

[0041] S4 sends the data analysis code to the preset sandbox environment to execute the data analysis code and process and analyze the data of the target form in the preset sandbox environment, and obtains and outputs the statistical results corresponding to the preset question text; it can be understood as: inputting the target form into the sandbox for processing and analysis.

[0042] As described above, by generating data analysis code in the preset large language model and executing the code in the sandbox, the preset large language model does not need to statistically analyze the data results corresponding to the preset question text. Instead, the sandbox analyzes the target form and obtains the statistical results when executing the data analysis code. This solves the problem that the model cannot output all the statistical results when the number of characters exceeds the limit, and also overcomes the problem that the model cannot handle complex tables.

[0043] Furthermore, step S4 also includes the following steps: S401, if an error occurs when executing data analysis code in the preset sandbox environment, obtain the error information returned by the preset sandbox environment. In another scenario, if the preset large language model believes that the code generation prompt information is problematic and cannot generate data analysis code, it also returns an error message.

[0044] S402, the error message and the current context information are input into the preset large language model to regenerate the corrected data analysis code; the current context information includes the code generation prompt information and the previously generated data analysis code.

[0045] S403, the modified data analysis code is sent again to the preset sandbox environment to execute the modified data analysis code in the preset sandbox environment until the execution is successful and the statistical results corresponding to the preset question text are obtained and output, or the execution failure information is fed back to the user after the number of iterations reaches the preset execution threshold; it can be understood that when the modified data analysis code is executed in the preset sandbox environment, the data of the target form is processed and analyzed in the preset sandbox environment, and the statistical results are output by the preset sandbox environment.

[0046] The above approach takes into account the error situation when the data analysis code is executed in the preset sandbox environment. It applies the model's ability to adaptively correct itself, which significantly improves the success rate and reliability of complex task processing. Moreover, each generated data analysis code is generated based on the previous error. Through feedback from the code, multiple rounds of interaction with the model are formed, which realizes accurate learning of the current specific task and context, improving the robustness of the model and the optimization effect of the data analysis code.

[0047] Furthermore, the method also includes the following steps: S10, after the statistical results are output in the preset sandbox environment, the statistical results and the preset question text are input together into the preset verification model so that the preset verification model can judge the correlation between the statistical results and the preset question text, as well as the reasonableness of the statistical results relative to the data in the target form.

[0048] Specifically, the preset verification model employs a pre-trained large language model; this can be understood as leveraging the powerful semantic analysis and natural language understanding capabilities of the large language model to determine the relevance and numerical reasonableness. For example, relevance verification is achieved by judging the semantic matching degree between the statistical results and the preset question text, while numerical reasonableness verification is achieved by judging whether the numerical value of the statistical results is within the expected numerical range derived from the target form data.

[0049] S20. If the verification fails, the failure will be reported as an error message, and the process will return to steps S402-S403 until the output statistical result is verified or the number of iterations reaches the preset execution threshold. Then, the user will be notified of the verification failure.

[0050] As mentioned above, after obtaining the statistical results, in order to ensure the reliability of the statistical results, the correlation and numerical rationality of the statistical results are verified. If the verification fails, the data analysis code is regenerated. Through multiple iterations, the reliability of the results of complex task processing is improved.

[0051] Embodiments of the present invention also provide a non-transitory computer-readable storage medium, which can be disposed in an electronic device to store at least one instruction or at least one program related to implementing a method in the method embodiments, wherein the at least one instruction or the at least one program is loaded and executed by the processor to implement the question-and-answer method based on an Excel multi-level header provided in the above embodiments.

[0052] Embodiments of the present invention also provide an electronic device, including a processor and the aforementioned non-transitory computer-readable storage medium.

[0053] While specific embodiments of the invention have been described in detail by way of example, those skilled in the art should understand that the examples are for illustrative purposes only and not intended to limit the scope of the invention. It should also be understood that various modifications can be made to the embodiments without departing from the scope and spirit of the invention. The scope of the invention is defined by the appended claims.

Claims

1. A question-and-answer method based on multi-level headers in Excel, characterized in that, The method includes the following steps: S1, input the pre-acquired summary prompt information into the preset large language model, and identify at least one target form corresponding to the preset question text from several original forms corresponding to the table file to be processed through the preset large language model; the summary prompt information includes the preset question text and the summary content extracted by the user based on the table file to be processed; S2, calls a pre-built form structure parsing tool to parse the target form and obtain the header structure data corresponding to the target form; The header structure data represents the cell merging relationship and hierarchical relationship; S3, construct code generation prompts, input the code generation prompts into the preset large language model, and generate data analysis code to answer the preset question text; The code generation prompt information includes the header structure data corresponding to the target form, preset question text, preset code generation specifications, and preset list of callable functions; S4. Send the data analysis code to the preset sandbox environment so that the data analysis code can be executed in the preset sandbox environment and the data of the target form can be processed and analyzed to obtain and output the statistical results corresponding to the preset question text.

2. The question-and-answer method based on multi-level Excel headers according to claim 1, characterized in that, The following steps precede step S1: S01, For each original form, identify the field names in the original form that do not conform to the preset naming convention as fields to be standardized; S02, Based on the data content in the original form, determine the business category corresponding to the original form through a preset classification model, and obtain the preset standard field name set corresponding to the business category; S03, for each column data corresponding to the field to be standardized, calculate the comprehensive matching degree between the column data and each candidate standard field name in the preset standard field name set, and assign the candidate standard field name to the corresponding column according to the comprehensive matching degree, so as to replace the field to be standardized and generate the original form with the header field standardized; the comprehensive matching degree is obtained by weighted calculation of semantic relevance and data feature matching degree.

3. The question-and-answer method based on multi-level Excel headers according to claim 1, characterized in that, The method further includes the following steps: S001, Obtain the complexity of each original form in the table file to be processed; wherein, the complexity meets the following conditions: Where C represents the complexity value, D is the number of rows in the original form, L is the number of header levels in the original form, M is the number of merged cells in the header of the original form, and D max L max M max These are the preset reference values ​​for the maximum number of rows, the maximum number of levels, and the maximum number of merged cells, respectively. α and β are preset weighting coefficients and α+β=1. S002, if the complexity of each original form is less than the preset complexity threshold, then each original form is input into the preset large language model by image recognition or text conversion to identify the target form corresponding to the preset question text; otherwise, step S1 is executed to identify the target form corresponding to the preset question text.

4. The question-and-answer method based on multi-level Excel headers according to claim 1, characterized in that, The summary includes the name of each original form extracted from the table file to be processed, the header information of each original form, and the data content corresponding to the first preset number of records in each original form.

5. The question-and-answer method based on multi-level Excel headers according to claim 1, characterized in that, The steps for identifying the target form are as follows: S101, based on the summary content, calculate the relevance score between each original form and the preset question text using a preset large language model; wherein, the relevance score between any original form and the preset question text meets the following conditions: P = W1×S1 + W2×S2 + W3×S3, where P represents the relevance score between any original form and the preset question text, S1, S1 and S3 are the semantic similarity, keyword matching degree and metadata matching degree between the summary content corresponding to any original form and the preset question text, respectively, and W1, W1 and W3 are the preset weights corresponding to S1, S1 and S3, respectively. S102, identify the original form whose corresponding relevance score is greater than the preset score threshold as the target form.

6. The question-and-answer method based on multi-level Excel headers according to claim 1, characterized in that, In step S2, the parsing steps of the form structure parsing tool are as follows: S201, obtain the maximum number of layers in the header area of ​​the target form, and split and fill the cells in columns with fewer cells than the maximum number of layers to construct a two-dimensional header network; S202, taking the bottom layer of the two-dimensional header network as the base layer, backtracking upwards, each cell in each layer covers the number of fields of the base layer. When the proportion of the total number of fields covered by the layer to be judged exceeds the preset proportion threshold, the layer to be judged is determined to be a valid layer. S203: Sort the identified effective layers from top to bottom into a first-level header to an n-level header. For each filled field, only the content of the corresponding last-level field is retained to obtain the final data information corresponding to each level header. Among them, the field content of the cell for which no field is retained is described as a null value in the final data information. S204: Based on the final data information corresponding to each level of the table header, fill in the fields of the preset table header data filling template to generate the table header structure data corresponding to the target form.

7. The question-and-answer method based on multi-level Excel headers according to claim 1, characterized in that, Step S4 also includes the following steps: S401, if an error occurs when executing data analysis code in a preset sandbox environment, obtain the error information returned by the preset sandbox environment; S402, input the error message and the current context information into the preset large language model, and regenerate the corrected data analysis code; The current context information includes the code generation prompt information and the previously generated data analysis code; S403, the corrected data analysis code is sent again to the preset sandbox environment to execute the corrected data analysis code in the preset sandbox environment until the execution is successful and the statistical results corresponding to the preset problem text are obtained and output, or the execution failure information is fed back to the user after the number of loop executions reaches the preset execution threshold.

8. The question-and-answer method based on multi-level Excel headers according to claim 7, characterized in that, The method further includes the following steps: S10, after the statistical results are output in the preset sandbox environment, the statistical results and the preset question text are input together into the preset verification model so that the preset verification model can judge the correlation between the statistical results and the preset question text, as well as the reasonableness of the statistical results relative to the data in the target form. S20. If the verification fails, the failure will be reported as an error message, and the process will return to steps S402-S403 until the output statistical result is verified or the number of iterations reaches the preset execution threshold. Then, the user will be notified of the verification failure.

9. A non-transitory computer-readable storage medium, wherein the storage medium stores at least one instruction or at least one program segment, characterized in that, The at least one instruction or the at least one program segment is loaded and executed by the processor to implement the question-and-answer method based on Excel multi-level headers as described in any one of claims 1-8.

10. An electronic device, characterized in that, Includes a processor and the non-transitory computer-readable storage medium as described in claim 9.

Images