Method and system for standardized correspondence of multiple elements based on field association

Abstract

The invention relates to the technical field of big data analysis, in particular to a multi-element standardization corresponding method and system based on field association. According to the method, a complex problem is simplified through row-by-row screening and column-by-column summarizing dimension reduction processing, and a three-dimensional model among main field elements, an auxiliary field containing multiple elements and elements contained in the auxiliary field is converted into a two-dimensional plane model between screening elements and elements contained in the fields; the application coverage is wide, the data reliability is high, and the operation convenience is good; the excel screening function is reduced and optimized, the minimum number of cells participate in calculation to achieve maximized function realization, the operation speed is high, occupied resources are few, and robustness is good. According to the method, the associated correspondence between multiple fields containing multiple elements and the elements is realized by combining python, an accurate, rapid and convenient method and means are provided for solving the problem of standardized correspondence of multiple elements based on field association, and the blank in the field is filled.

Description

Technical Field

[0001] The present invention relates to the technical field of big data analysis, and particularly to a method and system for standardizing the correspondence of multiple elements based on field association. Background Art

[0002] In the Internet era, big data is widely applied. Databases usually contain a vast number of fields, and each field often contains multiple elements. In practical applications, problems often arise in solving the standardized correspondence relationship of multiple elements based on field association. For example, in the well-known high-tech enterprise identification project, a strict correspondence is required among research and development projects (RD01, RD02, RD03), high-tech products (PS01, PS02, PS03), and intellectual properties (IP01, IP02, IP03). Here, RD, PS, and IP are called fields, and RD01, RD02, RD03, PS01, PS02, PS03, IP01, IP02, and IP03 are called elements. In practical applications, a research and development project often corresponds to multiple high-tech products and multiple intellectual properties at the same time, that is, the basic correspondence relationships of RD01→PS01, PS02→IP01, and IP02, IP03 are generated. At the same time, it is often necessary to solve the corresponding RD and PS for IP01, IP02, IP03 respectively and the corresponding RD and IP for PS01, PS02, PS03 respectively according to the above basic correspondence relationships. In the field of medical and health, there are also problems of associated correspondence of multiple fields and elements such as diseases, drugs, treatment taboos, and surgical instruments. Based on the simple correspondence relationships of a limited number of fields and elements, the corresponding results can be obtained through human participation. However, in the actual application process, there are often hundreds or thousands of fields, and each field contains several elements. Solving such complex correspondence relationships by the human brain incurs huge human and time costs and is very prone to errors, making it difficult to obtain correct results. Therefore, there is an urgent need for a solution for standardizing the association and correspondence of fields and elements to solve the above problem of standardizing the correspondence of multiple elements based on field association. Summary of the Invention

[0003] Technical Objective: To overcome the deficiencies in the prior art, the present invention provides a method and system for standardizing the correspondence of multiple elements based on field association, which realizes the standardization of the correspondence of multiple elements based on field association by combining Excel and Python. The technical solution of the present invention is as follows:

[0004] The present invention provides a method for standardizing the correspondence of multiple elements based on field association, which is applicable to the standardized correspondence between elements in a data table containing 1 main field and ZZZ secondary fields, where ZZZ≥2; the data of a single main field contains 1 main field element; the data of a single secondary field contains 1 or more secondary field elements, and unique punctuation marks are used to separate the elements; it is characterized by including:

[0005] [S1]Encode the fields and elements. The primary field "PrimaryField" contains 1 field "PF", including a total of X elements {PF01, PF02, ……, PFX}; the secondary field "SubField" contains {SFA, SFB, ……, SFZZZ}, where the SFA field contains a total of n1 elements {SFA01, SFA02, ……, SFAn1}, the SFB field contains a total of n2 elements {SFB01, SFB02, ……, SFBn2}, ……, the SFZZZ field contains a total of nZ elements {SFZZZ01, SFZZZ02, ……, SFZZZnZ}; as shown in Table 1:

[0006] Table 1: Data Table of Fields and Elements

[0007]

[0008] [S2]Build a filtering framework in excel, including:

[0009] [S2.1]Establish a set of all elements of the secondary field, denoted as

[0010]

[0011] [S2.2]Build a filtering framework for all elements of the secondary field described in step [S2.1] one by one. Use the number of data rows X of the primary field PF as the number of data rows of the filtering framework, and use {PF, SFA, SFB, ……, SFZZZ} as the data columns of the filtering framework. Perform a row-by-row filtering operation on the set of all elements of the secondary field described in step [S2.1] in sequence;

[0012] Take SFA01 as the filtering element: Fill all cells in the AI column where the SFA field is located with the data SFA01, and fill the cells in the remaining columns with a one-to-many filtering excel function with the corresponding cell data as the parameter. Perform a row-by-row filtering to obtain the values of the PF, SFB, ……, SFZZZ fields corresponding to the SFA01 element, as shown in Table 2:

[0013] Table 2: SFA01 Filtering Framework

[0014]

[0015] Take SFA02 as the filtering element: Fill all cells in the BI column where the SFA field is located with the data SFA02, and fill the cells in the remaining columns with a one-to-many filtering excel function with the corresponding cell data as the parameter. Perform a row-by-row filtering to obtain the values of the PF, SFB, ……, SFZZZ fields corresponding to the SFA02 element, as shown in Table 3:

[0016] Table 3: SFA02 Screening Framework

[0017]

[0018]

[0019] ……

[0020] SFZZZnz as the screening element: Fill all the cells in the WZZZ column where the SFZZZ field is located with the data SFZZZnZ, and fill the cells in the remaining columns with the one-to-many screening excel function with the corresponding cell data as parameters. Screen by row to obtain the PF, SFA, SFB,... field values corresponding to the SFZZZ element, as shown in Table 4:

[0021] Table 4: SFZZZnZ Screening Framework

[0022]

[0023] [S3] Aggregate the screening result data by column. Aggregate the result data of the row screening operation on the sub-field elements described in step [S2.2] by column, and separate the elements with the unique punctuation mark; record the column aggregation result in the X + 1 row;

[0024] SFA01 Aggregation: Aggregate the PF, SFB,... SFZZZ results corresponding to the SFA01 element obtained by row screening in step [S2.2] by column, as shown in Table 5:

[0025] Table 5: SFA01 Column Aggregation

[0026]

[0027]

[0028] SFA02 Aggregation: Aggregate the PF, SFB,... SFZZZ results corresponding to the SFA02 element obtained by row screening in step [S2.2] by column, as shown in Table 6:

[0029] Table 6: SFA02 Column Aggregation

[0030]

[0031] ……

[0032] SFZZZnZ Aggregation: Aggregate the PF, SFA, SFB,... results corresponding to the SFZZZ element obtained by row screening in step [S2.2] by column, as shown in Table 7:

[0033] Table 7: SFZZZnZ Column Aggregation

[0034]

[0035]

[0036] [S4] Split and deduplicate the results summarized by column. Use Python to split, deduplicate, sort, and merge the data obtained in step [S3] by column.

[0037] [S4.1] Use Python to read the data of any cell in the result summarized by column in step [S3] from an Excel file.

[0038] [S4.2] Use the split() string processing function and use the "unique punctuation mark" as the delimiter to split the cell data obtained in step [S4.1] into multiple substrings, and return a list of substrings; the data structure of the string list is a Python list.

[0039] [S4.3] Through the list(set()) function, first convert the list of substrings in step [S4.2] into a dictionary of substrings and remove duplicate data, and then convert the dictionary of substrings into a list of deduplicated substrings; the data structure of the dictionary of substrings is a Python dictionary, and the data structure of the list of deduplicated substrings is a Python list.

[0040] [S4.4] Use the list.sort() function to sort the list of deduplicated substrings in step [S4.3] from smallest to largest to obtain a sorted list of substrings; the data structure of the sorted list of substrings is a Python list.

[0041] [S4.5] Use a Python loop statement to convert the data in the sorted list of substrings in [S4.4] into a string of deduplicated and sorted results; each element in the string of deduplicated and sorted results is separated by the "unique punctuation mark".

[0042] [S4.6] Sequentially perform the traversal and loop operations described in steps [S4.1] to [S4.5] on the data summarized by column obtained in step [S3], that is, on all cell data in the (X + 1)-th row, so as to complete the splitting, deduplication, sorting, and merging of all elements, and finally achieve the standardized correspondence of multiple elements based on field association.

[0043] Further, the unique punctuation mark is "、".

[0044] Further, for the SFA01 screening in step [S2.2], the one-to-many screening function for cell AH2 is: =INDEX(H:H,SMALL(IF(ISNUMBER(FIND(AI2,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The filling method of the one-to-many screening function for cells AH3 to AHX in step [S2.2] is to drag the lower right corner of cell AH2 to the end of cell AHX in the excel file to complete the function filling for cells AH3 to AHX; among them: the one-to-many screening function for cell AH3 is: =INDEX(H:H,SMALL(IF(ISNUMBER(FIND(AI3,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); the one-to-many screening function for cell AHX is: =INDEX(H:H,SMALL(IF(ISNUMBER(FIND(AIX,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0))));

[0045] The one-to-many filtering function for cell AJ2 in step [S2.2] is: =INDEX(J:J,SMALL(IF(ISNUMBER(FIND(AI2,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The filling method of the one-to-many filtering function for cells AJ3 to AJX in step [S2.2] is to drag the lower right corner of cell AJ2 to the end of cell AJX in the excel file to complete the function filling for cells AJ3 to AJX; Among them: The one-to-many filtering function for cell AJ3 is: =INDEX(J:J,SMALL(IF(ISNUMBER(FIND(AI3,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The one-to-many filtering function for cell AJX is: =INDEX(J:J,SMALL(IF(ISNUMBER(FIND(AIX,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0))));

[0046] The one-to-many filtering function for cell AZZZ2 in step [S2.2] is: =INDEX(ZZZ:ZZZ,SMALL(IF(ISNUMBER(FIND(AI2,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The filling method of the one-to-many filtering function for cells AZZZ3 to AZZZX in step [S2.2] is to drag the lower right corner of cell AZZZ2 to the end of cell AZZZX in the excel file to complete the function filling for cells AZZZ3 to AZZZX; among them: The one-to-many filtering function for cell AZZZ3 is: =INDEX(ZZZ:ZZZ,SMALL(IF(ISNUMBER(FIND(AI3,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The one-to-many filtering function for cell AZZZX is: =INDEX(ZZZ:ZZZ,SMALL(IF(ISNUMBER(FIND(AIX,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0))));

[0047] For the SFA02 screening described in step [S2.2], the one-to-many screening function for cell BH2 is: =INDEX(H:H,SMALL(IF(ISNUMBER(FIND(BI2,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The filling method of the one-to-many screening function for cells BH3 to BHX in step [S2.2] is to drag the lower right corner of cell BH2 to the end of cell BHX in the excel file to complete the function filling for cells BH3 to BHX; among them: The one-to-many screening function for cell BH3 is: =INDEX(H:H,SMALL(IF(ISNUMBER(FIND(BI3,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The one-to-many screening function for cell BHX is: =INDEX(H:H,SMALL(IF(ISNUMBER(FIND(BIX,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0))));

[0048] The one-to-many filtering function for cell BJ2 in step [S2.2] is: =INDEX(J:J,SMALL(IF(ISNUMBER(FIND(BI2,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The filling method of the one-to-many filtering function for cells BJ3 to BJX in step [S2.2] is to drag the lower right corner of cell BJ2 to the end of cell BJX in the excel file to complete the function filling for cells BJ3 to BJX; Among them: The one-to-many filtering function for cell BJ3 is: =INDEX(J:J,SMALL(IF(ISNUMBER(FIND(BI3,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The one-to-many filtering function for cell BJX is: =INDEX(J:J,SMALL(IF(ISNUMBER(FIND(BIX,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0))));

[0049] The one-to-many filtering function for cell BZZZ2 in step [S2.2] is: =INDEX(ZZZ:ZZZ,SMALL(IF(ISNUMBER(FIND(BI2,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The filling method of the one-to-many filtering function for cells BZZZ3 to BZZZX in step [S2.2] is to drag the lower right corner of cell BZZZ2 to the end of cell BZZZX in the excel file to complete the function filling for cells BZZZ3 to BZZZX; Among them: The one-to-many filtering function for cell BZZZ3 is: =INDEX(ZZZ:ZZZ,SMALL(IF(ISNUMBER(FIND(BI3,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The one-to-many filtering function for cell BZZZX is: =INDEX(ZZZ:ZZZ,SMALL(IF(ISNUMBER(FIND(BIX,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0))));

[0050] The SFZZZnZ screening described in step [S2.2], the one-to-many screening function for cell WH2 is: =INDEX(H:H,SMALL(IF(ISNUMBER(FIND(WZZZ2,$ZZZ$2:$ZZZ$X)),ROW($2:$X),COUNTA(I:I)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The filling method of the one-to-many screening function for cells WH3 to WHX in step [S2.2] is to drag the lower right corner of cell WH2 to the end of cell WHX in the excel file to complete the function filling for cells WH3 to WHX; among them: the one-to-many screening function for cell WH3 is: =INDEX(H:H,SMALL(IF(ISNUMBER(FIND(WZZZ3,$ZZZ$2:$ZZZ$X)),ROW($2:$X),COUNTA(I:I)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); the one-to-many screening function for cell WHX is: =INDEX(H:H,SMALL(IF(ISNUMBER(FIND(WZZZX,$ZZZ$2:$ZZZ$X)),ROW($2:$X),COUNTA(I:I)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0))));

[0051] The one-to-many filtering function for cell WI2 in step [S2.2] is: =INDEX(I:I,SMALL(IF(ISNUMBER(FIND(WZZZ2,$ZZZ$2:$ZZZ$X)),ROW($2:$X),COUNTA(I:I)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The filling method of the one-to-many filtering function for cells WI3 to WIX in step [S2.2] is to drag the lower right corner of cell WI2 to the end of cell WIX in the excel file to complete the function filling of cells WI3 to WIX; Among them: The one-to-many filtering function for cell WI3 is: =INDEX(I:I,SMALL(IF(ISNUMBER(FIND(WZZZ3,$ZZZ$2:$ZZZ$X)),ROW($2:$X),COUNTA(I:I)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The one-to-many filtering function for cell WIX is: =INDEX(I:I,SMALL(IF(ISNUMBER(FIND(WZZZX,$ZZZ$2:$ZZZ$X)),ROW($2:$X),COUNTA(I:I)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0))));

[0052] The one-to-many filtering function for cell WJ2 in step [S2.2] is: =INDEX(J:J,SMALL(IF(ISNUMBER(FIND(WZZZ2,$ZZZ$2:$ZZZ$X)),ROW($2:$X),COUNTA(I:I)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The filling method of the one-to-many filtering function for cells WJ3 to WJX in step [S2.2] is to drag the lower right corner of cell WJ2 to the end of cell WJX in the excel file to complete the function filling of cells WJ3 to WJX; Among them: The one-to-many filtering function for cell WJ3 is: =INDEX(J:J,SMALL(IF(ISNUMBER(FIND(WZZZ3,$ZZZ$2:$ZZZ$X)),ROW($2:$X),COUNTA(I:I)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); The one-to-many filtering function for cell WJX is: =INDEX(J:J,SMALL(IF(ISNUMBER(FIND(WZZZX,$ZZZ$2:$ZZZ$X)),ROW($2:$X),COUNTA(I:I)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))).

[0053] Further, for the SFA01 screening in step [S2.2], the one-to-many screening function for cell AH2 is: =IFERROR(INDIRECT("H"&SMALL(IF(ISNUMBER(FIND(AI2,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); the filling method of the one-to-many screening function for cells AH3 to AHX in step [S2.2] is to drag the lower right corner of cell AH2 to the end of cell AHX in the excel file to complete the function filling of cells AH3 to AHX; among them: the one-to-many screening function for cell AH3 is: =IFERROR(INDIRECT("H"&SMALL(IF(ISNUMBER(FIND(AI3,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); the one-to-many screening function for cell AHX is: =IFERROR(INDIRECT("H"&SMALL(IF(ISNUMBER(FIND(AIX,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0);

[0054] The one-to-many filtering function for cell AJ2 in step [S2.2] is: =IFERROR(INDIRECT("J"&SMALL(IF(ISNUMBER(FIND(AI2,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The filling method of the one-to-many filtering function for cells AJ3 to AJX in step [S2.2] is to drag the lower right corner of cell AJ2 to the end of cell AJX in the excel file to complete the function filling for cells AJ3 to AJX; Among them: The one-to-many filtering function for cell AJ3 is: =IFERROR(INDIRECT("J"&SMALL(IF(ISNUMBER(FIND(AI3,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The one-to-many filtering function for cell AJX is: =IFERROR(INDIRECT("J"&SMALL(IF(ISNUMBER(FIND(AIX,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0);

[0055] The one-to-many filtering function for cell AZZZ2 in step [S2.2] is: =IFERROR(INDIRECT("ZZZ"&SMALL(IF(ISNUMBER(FIND(AI2,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The filling method of the one-to-many filtering function for cells AZZZ3 to AZZZX in step [S2.2] is to drag the lower right corner of cell AZZZ2 to the end of cell AZZZX in the excel file to complete the function filling for cells AZZZ3 to AZZZX; Among them: The one-to-many filtering function for cell AZZZ3 is: =IFERROR(INDIRECT("ZZZ"&SMALL(IF(ISNUMBER(FIND(AI3,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The one-to-many filtering function for cell AZZZX is: =IFERROR(INDIRECT("ZZZ"&SMALL(IF(ISNUMBER(FIND(AIX,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0);

[0056] For the SFA02 screening described in step [S2.2], the one-to-many screening function for cell BH2 is: =IFERROR(INDIRECT("H"&SMALL(IF(ISNUMBER(FIND(BI2,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The filling method of the one-to-many screening function for cells BH3 to BHX in step [S2.2] is to drag the lower right corner of cell BH2 to the end of cell BHX in the excel file to complete the function filling of cells BH3 to BHX; Among them: The one-to-many screening function for cell BH3 is: =IFERROR(INDIRECT("H"&SMALL(IF(ISNUMBER(FIND(BI3,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The one-to-many screening function for cell BHX is: =IFERROR(INDIRECT("H"&SMALL(IF(ISNUMBER(FIND(BIX,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0);

[0057] The one-to-many filtering function for cell BJ2 in step [S2.2] is: =IFERROR(INDIRECT("J"&SMALL(IF(ISNUMBER(FIND(BI2,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The filling method of the one-to-many filtering function for cells BJ3 to BJX in step [S2.2] is to drag the lower right corner of cell BJ2 to the end of cell BJX in the excel file to complete the function filling for cells BJ3 to BJX; among them: The one-to-many filtering function for cell BJ3 is: =IFERROR(INDIRECT("J"&SMALL(IF(ISNUMBER(FIND(BI3,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The one-to-many filtering function for cell BJX is: =IFERROR(INDIRECT("J"&SMALL(IF(ISNUMBER(FIND(BIX,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0);

[0058] The one-to-many filtering function for cell BZZZ2 in step [S2.2] is: =IFERROR(INDIRECT("ZZZ"&SMALL(IF(ISNUMBER(FIND(BI2,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The filling method of the one-to-many filtering function for cells BZZZ3 to BZZZX in step [S2.2] is to drag the lower right corner of cell BZZZ2 to the end of cell BZZZX in the excel file to complete the function filling of cells BZZZ3 to BZZZX; Among them: The one-to-many filtering function for cell BZZZ3 is: =IFERROR(INDIRECT("ZZZ"&SMALL(IF(ISNUMBER(FIND(BI3,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The one-to-many filtering function for cell BZZZX is: =IFERROR(INDIRECT("ZZZ"&SMALL(IF(ISNUMBER(FIND(BIX,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0);

[0059] The SFZZZnZ screening described in step [S2.2], the one-to-many screening function for cell WH2 is: =IFERROR(INDIRECT("H"&SMALL(IF(ISNUMBER(FIND(WZZZ2,$ZZZ$2:$ZZZ$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The filling method of the one-to-many screening function for cells WH3 to WHX in step [S2.2] is to drag the lower right corner of cell WH2 to the end of cell WHX in the excel file to complete the function filling of cells WH3 to WHX; among them: the one-to-many screening function for cell WH3 is: =IFERROR(INDIRECT("H"&SMALL(IF(ISNUMBER(FIND(WZZZ3,$ZZZ$2:$ZZZ$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); the one-to-many screening function for cell WHX is: =IFERROR(INDIRECT("H"&SMALL(IF(ISNUMBER(FIND(WZZZX,$ZZZ$2:$ZZZ$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0);

[0060] The one-to-many filtering function for cell WI2 in step [S2.2] is: =IFERROR(INDIRECT("I"&SMALL(IF(ISNUMBER(FIND(WZZZ2,$ZZZ$2:$ZZZ$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The filling method of the one-to-many filtering function for cells WI3 to WIX in step [S2.2] is to drag the lower right corner of cell WI2 to the end of cell WIX in the excel file to complete the function filling for cells WI3 to WIX; among them: The one-to-many filtering function for cell WI3 is: =IFERROR(INDIRECT("I"&SMALL(IF(ISNUMBER(FIND(WZZZ3,$ZZZ$2:$ZZZ$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The one-to-many filtering function for cell WIX is: =IFERROR(INDIRECT("I"&SMALL(IF(ISNUMBER(FIND(WZZZX,$ZZZ$2:$ZZZ$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0);

[0061] The one-to-many filtering function for cell WJ2 in step [S2.2] is: =IFERROR(INDIRECT("J"&SMALL(IF(ISNUMBER(FIND(WZZZ2,$ZZZ$2:$ZZZ$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The filling method of the one-to-many filtering function for cells WJ3 to WJX in step [S2.2] is to drag the lower right corner of cell WJ2 in the excel file to the end of cell WJX to complete the function filling for cells WJ3 to WJX; Among them: The one-to-many filtering function for cell WJ3 is: =IFERROR(INDIRECT("J"&SMALL(IF(ISNUMBER(FIND(WZZZ3,$ZZZ$2:$ZZZ$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); The one-to-many filtering function for cell WJX is: =IFERROR(INDIRECT("J"&SMALL(IF(ISNUMBER(FIND(WZZZX,$ZZZ$2:$ZZZ$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0).

[0062] Further, a multiple-element standardization correspondence system based on field association applies the multiple-element standardization correspondence method based on field association as described in any one of claims 1 to 4, and is characterized in that it includes:

[0063] [M1] Variable initialization module: Its function is to input the main field and all the elements it contains, the sub-field and all the elements it contains; According to the requirement that a single main field data contains 1 main field element, a single sub-field data contains 1 or more sub-field elements, and unique punctuation marks are used to separate each element as described in claim 1, check the format of all the input elements; Complete the field and element coding according to step [S1].

[0064] [M2] Excel-based data processing module: Use the data with field and element coding completed in [M1] as the input of the [M2] module, and use the one-to-many filtering function described in step [S2.2] to perform row-by-row filtering on all element sets of the sub-field in turn; Use the column-by-column summarization function described in step [S3] to summarize the row-by-row filtered result data column by column to complete the filtering and recombination of a single element corresponding to multiple fields.

[0065] [M3]Python-based data processing module: Use the data obtained in [M2] after screening and reorganizing multiple fields corresponding to a single element as the input of the [M3] module, and sequentially perform the traversal loop operations described in steps [S4.1] to [S4.5] to complete the splitting, deduplication, sorting, and merging of each element in all cells of the (X + 1)-th row, and realize the standardized correspondence of multiple elements based on field association;

[0066] [M4] Result feedback module: Feedback the result of the standardized correspondence of multiple elements based on field association processed by the [M3] module.

[0067] The beneficial effects of the present invention are as follows:

[0068] 1. The method and system for standardizing the correspondence of multiple elements based on field association provided by the present invention simplify complex problems through row-by-row screening and column-by-column summary and dimensionality reduction processing, convert the three-dimensional model among the main field elements, the secondary fields containing multiple elements, and the elements contained in the secondary fields into a two-dimensional plane model between the screened elements and the elements contained in the fields, with wide application coverage, high data reliability, and good operation convenience;

[0069] 2. The method and system for standardizing the correspondence of multiple elements based on field association provided by the present invention achieve the maximized function of the function with the least number of cells participating in the calculation by streamlining and optimizing the excel screening function, with fast running speed, less resource occupation, and good robustness;

[0070] 3. The method and system for standardizing the correspondence of multiple elements based on field association provided by the present invention realize the associated correspondence between multiple fields containing multiple elements and elements through the combination of excel and python, providing an accurate, fast, and convenient method for solving the problem of standardizing the correspondence of multiple elements based on field association, and filling the gap in this field. Description of the Drawings

[0071] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the following drawings are only some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0072] Figure 1 It is a schematic flowchart of the method for standardizing the correspondence of multiple elements based on field association;

[0073] Figure 2 It is a schematic diagram of the system modules for standardizing the correspondence of multiple elements based on field association. Detailed Embodiments

[0074] Exemplary embodiments of the present invention will be described in detail below with reference to the accompanying drawings. It should be noted that the following description is only explanatory and exemplary in nature and is in no way intended to limit the present invention and its application or use. Unless otherwise specifically stated, the relative positions, numerical expressions, and numerical values of the components and steps set forth in the embodiments do not limit the scope of the present invention. In addition, technologies, methods, and devices known to those skilled in the art may not be discussed in detail, but are intended to be part of the specification where appropriate.

[0075] Table 8: Basic Field Information Table of Embodiments of the Present Invention

[0076] R & D Project RD Intellectual Property IP High - tech Product PS RD01 IP01, IP02 PS01, PS02 RD02 IP02 PS01 RD03 IP03, IP04 PS03 RD04 IP04 PS01 RD05 IP01, IP05 PS02 RD06 IP03 PS02, PS03 RD07 IP02 PS01 RD08 IP03, IP04 PS03 RD09 IP02 PS01 RD10 IP01 PS01, PS03 RD11 IP04 PS03 RD12 IP01, IP05 PS01

[0077] Embodiment 1 is a method and system application for standardizing the correspondence of multiple elements based on field association disclosed in the present invention, and uses the screening function disclosed in claim 3:

[0078] As Figure 1 shown, through the [M1] variable initialization module entry interface, the field information included in Table 8 is entered into the system for standardizing the correspondence of multiple elements based on field association by means of copy and paste; as Figure 2 shown, the [M1] module completes a format check on the entered data according to the requirements for encoding fields and elements described in step [S1];

[0079] Table 9: Data Table of Fields and Elements in the Embodiment

[0080] H I J 1 R & D Intellectual Property High - tech Product 2 RD01 IP01, IP02 PS01, PS02 3 RD02 IP02 PS01 4 RD03 IP03, IP04 PS03 5 RD04 IP04 PS01 6 RD05 IP01, IP05 PS02 7 RD06 IP03 PS02, PS03 8 RD07 IP02 PS01 9 RD08 IP03, IP04 PS03 10 RD09 IP02 PS01 11 RD10 IP01 PS01, PS03 12 RD11 IP04 PS03 13 RD12 IP01, IP05 PS01

[0081] As shown in Table 9, the main field is RD, which contains a total of 12 elements {RD01, RD02,..., RD12}; the secondary fields include {IP, PS}, where the IP field contains a total of 5 elements {IP01, IP02, IP03, IP04, IP05}, and the PS field contains a total of 3 elements {PS01, PS02, PS03}; the data that has passed the format check is transmitted to the [M2] Excel-based data processing module.

[0082] The [M2] Excel-based data processing module loads the data passed from the [M1] module into the screening framework built in Excel described in step [S2] for processing, including:

[0083] [S2.1] Establish a set of all elements of the secondary field, denoted as {IP01, IP02, IP03, IP04, IP05, PS01, PS02, PS03};

[0084] [S2.2] Establish a screening framework for all elements of the secondary field described in step [S2.1] one by one. Use the number of rows of the main field RD data, which is 12, as the number of rows of the screening framework data, and use {IP, PS} as the data columns of the screening framework. Perform row-by-row screening operations on all element sets of the secondary field described in step [S2.1]. The screening frameworks and function operation results for each element of the secondary field are shown in Tables 10 - 16:

[0085] Table 10: IP01 Screening Framework

[0086]

[0087]

[0088] Table 11: Function Operation Results of IP01 Screening Framework

[0089] AH AI AJ 1 R & D Intellectual Property High - tech Product 2 RD01 IP02 PS01, PS02 3 RD05 IP02 PS02 4 RD10 IP02 PS01, PS03 5 RD12 IP02 PS01 6 0 IP02 0 7 0 IP02 0 8 0 IP02 0 9 0 IP02 0 10 0 IP02 0 11 0 IP02 0 12 0 IP02 0 13 0 IP02 0

[0090] The screening frameworks for IP02, IP03, IP04, and IP05 are similar to that of IP01, with only differences in the strings to be searched by the FIND function. The screening framework functions are not shown in detail here, and the function operation results are shown in Tables 12 and 13;

[0091] Table 12: Function Operation Results of IP02 and IP03 Screening Frameworks

[0092] BH BI BJ CH CI CJ 1 R & D Intellectual Property High - tech Product R & D Intellectual Property High - tech Product 2 RD01 IP02 PS01, PS02 RD03 IP03 PS03 3 RD02 IP02 PS01 RD06 IP03 PS02, PS03 4 RD07 IP02 PS01 RD08 IP03 PS03 5 RD09 IP02 PS01 0 IP03 0 6 0 IP02 0 0 IP03 0 7 0 IP02 0 0 IP03 0 8 0 IP02 0 0 IP03 0 9 0 IP02 0 0 IP03 0 10 0 IP02 0 0 IP03 0 11 0 IP02 0 0 IP03 0 12 0 IP02 0 0 IP03 0 13 0 IP02 0 0 IP03 0

[0093] Table 13: Function Operation Results of IP04 and IP05 Screening Frameworks

[0094]

[0095]

[0096] Table 14: PS01 Screening Framework

[0097]

[0098]

[0099] Table 15: Function Operation Results of PS01 Screening Framework

[0100] FH FI FJ 1 R & D Intellectual Property High - tech Product 2 RD01 IP01, IP02 PS01 3 RD02 IP02 PS01 4 RD04 IP04 PS01 5 RD07 IP02 PS01 6 RD09 IP02 PS01 7 RD10 IP01 PS01 8 RD12 IP01, IP05 PS01 9 0 0 PS01 10 0 0 PS01 11 0 0 PS01 12 0 0 PS01 13 0 0 PS01

[0101] The screening frameworks for PS02 and PS03 are similar to that of PS01, with only differences in the strings to be searched by the FIND function. The screening framework functions are not shown in detail here, and the function operation results are shown in Table 16;

[0102] Table 16: Operation Results of PS02 and PS03 Screening Framework Functions

[0103] GH GI GJ HH HI HJ 1 R & D Intellectual Property High - tech Product R & D Intellectual Property High - tech Product 2 RD01 IP01, IP02 PS02 RD03 IP03, IP04 PS03 3 RD05 IP01, IP05 PS02 RD06 IP03 PS03 4 RD06 IP03 PS02 RD08 IP03, IP04 PS03 5 0 0 PS02 RD10 IP01 PS03 6 0 0 PS02 RD11 IP04 PS03 7 0 0 PS02 0 0 PS03 8 0 0 PS02 0 0 PS03 9 0 0 PS02 0 0 PS03 10 0 0 PS02 0 0 PS03 11 0 0 PS02 0 0 PS03 12 0 0 PS02 0 0 PS03 13 0 0 PS02 0 0 PS03

[0104] [S3]Summarize the screened result data by column. Summarize the result data of the row screening operation on the secondary field elements described in step [S2.2] by column, and separate each element with "、".

[0105] Table 17: IP01 Column Summarization Function

[0106]

[0107]

[0108] Table 18: Operation Results of IP01 Column Summarization Function

[0109] AH AI AJ 1 RD IP PS 14 RD01, RD05, RD10, RD12 PS01, PS02, PS02, PS01, PS03, PS01

[0110] The column summarization functions of IP02, IP03, IP04, IP05, PS01, PS02, and PS03 are similar to that of IP01. The screening framework functions are not shown in detail here, and the function operation results are shown in Tables 19 - 25;

[0111] Table 19: Operation Results of IP02 Column Summarization Function

[0112] BH BI BJ 1 RD IP PS 14 RD01, RD02, RD07, RD09 PS01, PS02, PS01, PS01, PS01

[0113] Table 20: Operation Results of IP03 Column Summarization Function

[0114] CH CI CJ 1 RD IP PS 14 RD03, RD06, RD08 PS03, PS02, PS03, PS03

[0115] Table 21: Operation Results of IP04 Column Summarization Function

[0116] DH DI DJ 1 RD IP PS 14 RD03, RD04, RD08, RD11 PS03, PS01, PS03, PS03

[0117] Table 22: Operation Results of IP05 Column Summarization Function

[0118] EH EI EJ 1 RD IP PS 14 RD05, RD12 PS02, PS01

[0119] Table 23: Operation Results of PS01 Column Summarization Function

[0120]

[0121] Table 24: Operation Results of PS02 Column Summarization Function

[0122]

[0123]

[0124] Table 25: Running Results of PS03 Column-wise Summarization Function

[0125]

[0126] The column-wise summarization results shown in Tables 19 - 25 have duplicate elements and are unsorted. Therefore, [S4] is introduced to split and remove duplicates from the column-wise summarization results. Python is used to split, remove duplicates, sort, and merge the data obtained in step [S3] column-wise;

[0127] [S4.1] Use Python to read the data in cell FI14 of the column-wise summarization result described in step [S3] from the Excel file: "IP01, IP02, IP02, IP04, IP02, IP02, IP01, IP01, IP05";

[0128] [S4.2] Use the split() string processing function with ',' as the delimiter to split the data in cell AH14 into a list of substrings ['IP01', 'IP02', 'IP02', 'IP04', 'IP02', 'IP02', 'IP01', 'IP01', 'IP05'];

[0129] [S4.3] Use the list(set()) function to first convert the list of substrings described in step [S4.2] into a dictionary of substrings and remove duplicate data. The result is {'IP05', 'IP01', 'IP04', 'IP02'}; then convert the dictionary of substrings into a list of substrings after removing duplicates. The result is ['IP05', 'IP01', 'IP04', 'IP02'];

[0130] [S4.4] Use the list.sort() function to sort the list of substrings after removing duplicates described in step [S4.3] in ascending order to obtain a sorted list of substrings. The result is ['IP01', 'IP02', 'IP04', 'IP05'];

[0131] [S4.5] Use a Python loop statement to convert the data in the sorted list of substrings described in [S4.4] into a string of the result after removing duplicates and sorting: "IP01, IP02, IP04, IP05";

[0132] The Python programming statements for steps [S4.1] to [S4.5] are as follows:

[0133]

[0134]

[0135] [S4.6] Sequentially summarize the data obtained in step [S3] column by column, that is, perform the traversal loop operations described in steps [S4.1] to [S4.5] on all cell data in the 14th row in sequence, so as to complete the splitting, de-duplication, sorting, and merging of all elements, and finally realize the associated correspondence between multiple fields containing multiple elements and elements; the final field and element association correspondence table is shown in Tables 26 and 27:

[0136] Table 26: IP Element and Field Association Correspondence Results

[0137] Research and Development Project RD Intellectual Property IP High - tech Product PS RD01, RD05, RD10, RD12 IP01 PS01, PS02, PS03 RD01, RD02, RD07, RD09 IP02 PS01, PS02 RD03, RD06, RD08 IP03 PS02, PS03 RD03, RD04, RD08, RD11 IP04 PS01, PS03 RD05, RD12 IP05 PS01, PS02

[0138] Table 27: PS Element and Field Association Correspondence Results

[0139]

[0140] Embodiment 2 is a method and system application for standardizing the correspondence of multiple elements based on field association disclosed in the present invention, and uses the screening function disclosed in claim 4: The difference between Embodiment 2 and Embodiment 1 is that the IP01 screening framework in Table 10 is replaced with Table 28, and the remaining steps are the same as those in Embodiment 1.

[0141] Table 28: IP01 Screening Framework

[0142]

[0143]

[0144]

[0145] The screening frameworks of IP02, IP03, IP04, IP05, PS01, PS02, and PS03 are similar to that of IP01, and only differ in the strings to be searched and the strings to be searched in the FIND function, which will not be listed in detail here; the final field and element association correspondence results obtained in Embodiment 2 are shown in Tables 26 and 27.

[0146] The above are only the preferred embodiments of the present invention and are not intended to limit the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present invention shall be included in the protection scope of the present invention.

Claims

1. A method for standardized correspondence of multiple elements based on field association, which is applicable to standardized correspondence between elements in a data table containing 1 main field and ZZZ sub-fields, where ZZZ≥2; a single main field data contains 1 main field element; a single sub-field data contains 1 or more sub-field elements, and each element is separated by a unique punctuation mark; by combining Excel with Python, standardized correspondence of multiple elements based on field association is realized; the characteristics are: include: [S1] Encode the fields and elements. The primary field PrimaryField contains one field PF, including {PF01, PF02, ..., PFX}, totaling X elements; the subfield SubField contains {SFA, SFB, ..., SFZZZ, wherein the SFA field contains {SFA01, SFA02, ..., SFAn1}, totaling n1 elements; the SFB field contains {SFB01, SFB02, ..., SFBn2}, totaling n2 elements, ..., the SFZZZ field contains {SFZZZ01, SFZZZ02, ..., SFZZZnZ}, totaling nZ elements; as shown in Table 1 in Excel: Table 1: Field and element data table [S2] Build a filtering framework in Excel, including: [S2.1] Establish a set of all elements of the sub-field, expressed as [S2.2] Establish a screening framework for all elements of the sub-fields described in step [S2.1] one by one, take the number of data rows X of the main field PF as the number of data rows of the screening framework, take {PF, SFA, SFB, ..., SFZZZ} as the data columns of the screening framework, and perform row-by-row screening operations on all element sets of the sub-fields described in step [S2.1] in turn; SFA01 is used as the filter element: fill all cells in the AI ​​column where the SFA field is located with the data SFA01, and fill the remaining column cells with the one-to-many filter Excel function with the corresponding cell data as the parameter. Filter by row to obtain the PF, SFB, ..., SFZZZ field values ​​corresponding to the SFA01 element, as shown in Table 2 in Excel: Table 2: SFA01 screening framework SFA02 is used as the filter element: fill all cells in the BI column where the SFA field is located with the data SFA02, and fill the remaining column cell data with the one-to-many filter Excel function with the corresponding cell data as the parameter, and filter by row to obtain the PF, SFB, ..., SFZZZ field values ​​corresponding to the SFA02 element, as shown in Table 3 in Excel: Table 3: SFA02 screening framework …… SFZZZnZ is used as the filter element: fill all cells in the WZZZ column where the SFZZZ field is located with the data SFZZZnZ, and fill the remaining column cell data with the one-to-many filter Excel function with the corresponding cell data as the parameter, and filter by row to obtain the PF, SFA, SFB, ... field values ​​corresponding to the SFZZZ element, as shown in Table 4 in Excel: Table 4: SFZZZnZ screening framework [S3] Summarize the screening result data by column, and summarize the result data of the row-by-row screening operation on the sub-field elements in step [S2.2] by column, and separate each element with the unique punctuation mark; the column-by-column summary result is recorded in the X+1 row; SFA01 summary: The PF, SFB, ..., SFZZZ results corresponding to the SFA01 elements obtained by row screening in step [S2.2] are summarized by column, as shown in Table 5: Table 5: SFA01 Summary by Column SFA02 summary: The PF, SFB, ..., SFZZZ results corresponding to the SFA02 elements obtained by row screening in step [S2.2] are summarized by column, as shown in Table 6: Table 6: SFA02 Summary by Column …… SFZZZnZ summary: The PF, SFA, SFB, ... results corresponding to the SFZZZ elements obtained by row screening in step [S2.2] are summarized by column, as shown in Table 7: Table 7: SFZZZnZ summary by column [S4] Split and remove duplicates from the results of the column aggregation, and use Python to split, remove duplicates, sort, and merge the column aggregation results of the data obtained in step [S3]; [S4.1] Read any cell data of the column summary result described in step [S3] from Excel through Python; [S4.2] Use the split() string processing function and "unique punctuation mark" as a delimiter to split the cell data obtained in step [S4.1] into multiple substrings, and return a substring list; the string list data structure is a python list; [S4.3] Using the list(set()) function, first convert the substring list in step [S4.2] into a substring dictionary and remove duplicate data, and then convert the substring dictionary into a substring list after deduplication; the substring dictionary data structure is a python dictionary, and the substring list data structure after deduplication is a python list; [S4.4] Sort the substring list after deduplication described in step [S4.3] from small to large by using the list.sort() function to obtain a sorted substring list; the data structure of the sorted substring list is a Python list; [S4.5] Using a Python loop statement, convert the sorted substring list data described in [S4.4] into a de-duplicated sorted result string; each element in the de-duplicated sorted result string is separated by a "unique punctuation mark"; [S4.6] The data obtained in step [S3] is summarized by columns in turn, that is, all the cell data in row X+1 are sequentially subjected to the traversal loop operations described in steps [S4.1] to [S4.5], thereby completing the splitting, deduplication, sorting, and merging of all elements, and finally achieving standardized correspondence of multiple elements based on field association.

2. The method for standardizing multiple elements based on field association according to claim 1, characterized in that: The only punctuation mark is "、".

3. The method for standardizing and corresponding multiple elements based on field association according to claim 1, characterized in that: Step [S2.2] The SFA01 screening, AH2 cell one-to-many screening function is: =INDEX(H:H,SMALL(IF(ISNUMBER(FIND(AI2,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); Step [S2.2] The one-to-many screening function filling method for AH3~AHX cells is to drag the lower right corner of AH2 cell to the end of AHX cell in the Excel file; Step [S2.2] The one-to-many filter function of cell AJ2 is: =INDEX(J:J,SMALL(IF(ISNUMBER(FIND(AI2,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); Step [S2.2] The one-to-many filter function filling method of cells AJ3~AJX is to drag the lower right corner of cell AJ2 to the end of cell AJX in the Excel file; Step [S2.2] The one-to-many filter function of cell AZZZ2 is: =INDEX(ZZZ:ZZZ,SMALL(IF(ISNUMBER(FIND(AI2,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); Step [S2.2] The one-to-many filter function filling method of cells AZZZ3~AZZZX is to drag the lower right corner of cell AZZZ2 to the end of cell AZZZX in the Excel file; Step [S2.2] The SFA02 screening, BH2 cell one-to-many screening function is: =INDEX(H:H,SMALL(IF(ISNUMBER(FIND(BI2,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); Step [S2.2] The one-to-many screening function filling method for BH3~BHX cells is to drag the lower right corner of BH2 cell to the end of BHX cell in the Excel file; Step [S2.2] The one-to-many filter function of BJ2 cell is: =INDEX(J:J,SMALL(IF(ISNUMBER(FIND(BI2,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); Step [S2.2] The one-to-many filter function filling method of BJ3~BJX cells is to drag the lower right corner of BJ2 cell to the end of BJX cell in the Excel file; Step [S2.2] The one-to-many filter function for cell BZZZ2 is: =INDEX(ZZZ:ZZZ,SMALL(IF(ISNUMBER(FIND(BI2,$I$2:$I$X)),ROW($2:$X),COUNTA(H:H)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); Step [S2.2] The one-to-many filter function filling method for cells BZZZ3 to BZZZX is to drag the lower right corner of cell BZZZ2 to the end of cell BZZZX in the Excel file; In step [S2.2], the SFZZZnZ screening, the one-to-many screening function of WH2 cell is: =INDEX(H:H,SMALL(IF(ISNUMBER(FIND(WZZZ2,$ZZZ$2:$ZZZ$X)),ROW($2:$X),COUNTA(I:I)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); In step [S2.2], the one-to-many screening function filling method of WH3~WHX cells is to drag the lower right corner of WH2 cell to the end of WHX cell in the Excel file; Step [S2.2] The one-to-many filter function of WI2 cell is: =INDEX(I:I,SMALL(IF(ISNUMBER(FIND(WZZZ2,$ZZZ$2:$ZZZ$X)),ROW($2:$X),COUNTA(I:I)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); Step [S2.2] The one-to-many filter function filling method of WI3~WIX cells is to drag the lower right corner of WI2 cell to the end of WIX cell in the Excel file; Step [S2.2] The one-to-many filtering function of cell WJ2 is: =INDEX(J:J,SMALL(IF(ISNUMBER(FIND(WZZZ2,$ZZZ$2:$ZZZ$X)),ROW($2:$X),COUNTA(I:I)+1),ROW(INDIRECT(("R"&ROW()-1)&"C"&COLUMN(),0)))); Step [S2.2] The one-to-many filtering function of cells WJ3~WJX is filled by dragging the lower right corner of cell WJ2 to the end of cell WJX in the Excel file.

4. The method for standardizing and corresponding multiple elements based on field association according to claim 1, characterized in that: Step [S2.2] The SFA01 filter, the AH2 cell one-to-many filter function is: =IFERROR(INDIRECT("H"&SMALL(IF(ISNUMBER(FIND(AI2,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); Step [S2.2] The AH3~AHX cell one-to-many filter function filling method is to drag the lower right corner of the AH2 cell to the end of the AHX cell in the Excel file; Step [S2.2] The one-to-many filter function of cell AJ2 is: =IFERROR(INDIRECT("J"&SMALL(IF(ISNUMBER(FIND(AI2,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); Step [S2.2] The one-to-many filter function filling method of cells AJ3~AJX is to drag the lower right corner of cell AJ2 to the end of cell AJX in the Excel file; Step [S2.2] The one-to-many filter function of cell AZZZ2 is: =IFERROR(INDIRECT("ZZZ"&SMALL(IF(ISNUMBER(FIND(AI2,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); Step [S2.2] The one-to-many filter function filling method of cells AZZZ3~AZZZX is to drag the lower right corner of cell AZZZ2 to the end of cell AZZZX in the Excel file; Step [S2.2] The SFA02 screening, BH2 cell one-to-many screening function is: =IFERROR(INDIRECT("H"&SMALL(IF(ISNUMBER(FIND(BI2,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); Step [S2.2] The one-to-many screening function filling method for BH3~BHX cells is to drag the lower right corner of BH2 cell to the end of BHX cell in the Excel file; Step [S2.2] The one-to-many filter function for BJ2 is: IFERROR(INDIRECT("J"&SMALL(IF(ISNUMBER(FIND(BI2,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); Step [S2.2] The one-to-many filter function for BJ3~BJX is filled by dragging the lower right corner of BJ2 to the end of BJX in the Excel file; Step [S2.2] The one-to-many filter function for cell BZZZ2 is: =IFERROR(INDIRECT("ZZZ"&SMALL(IF(ISNUMBER(FIND(BI2,$I$2:$I$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); Step [S2.2] The one-to-many filter function filling method for cells BZZZ3 to BZZZX is to drag the lower right corner of cell BZZZ2 to the end of cell BZZZX in the Excel file; Step [S2.2] The SFZZZnZ screening, the one-to-many screening function of WH2 cell is: =IFERROR(INDIRECT("H"&SMALL(IF(ISNUMBER(FIND(WZZZ2,$ZZZ$2:$ZZZ$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); Step [S2.2] The one-to-many screening function filling method of WH3~WHX cells is to drag the lower right corner of WH2 cell to the end of WHX cell in the Excel file; Step WI2: One-to-many filter function is: =IFERROR(INDIRECT("I"&SMALL(IF(ISNUMBER(FIND(WZZZ2,$ZZZ$2:$ZZZ$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); Step [S2.2]: One-to-many filter function filling method for WI3~WIX cells is to drag the lower right corner of WI2 cell to the end of WIX cell in the excel file; Step WJ2 cell one-to-many filtering function is: =IFERROR(INDIRECT("J"&SMALL(IF(ISNUMBER(FIND(WZZZ2,$ZZZ$2:$ZZZ$X)),ROW($2:$X)),ROW(INDIRECT(("R"&ROW())&"C"&COLUMN(),0))-1)),0); Step [S2.2] The one-to-many filtering function filling method for cells WJ3~WJX is to drag the lower right corner of cell WJ2 to the end of cell WJX in the Excel file.

5. A system for standardizing and corresponding multiple elements based on field association, applying a method for standardizing and corresponding multiple elements based on field association as claimed in any one of claims 1 to 4, comprising: [M1] Variable initialization module: its function is to enter the main field and all the elements it contains, the sub-field and all the elements it contains; According to the rule of claim 1 that "a single main field data contains one main field element, a single sub-field data contains one or more sub-field elements, and each element is separated by a unique punctuation mark", all entered elements are format checked; and field and element coding is completed according to step [S1]; [M2] Excel-based data processing module: Use the data with completed field and element coding in [M1] as the input of module [M2], and use the one-to-many filtering function described in step [S2.2] to filter all element sets of the sub-fields in turn by row; Use the column aggregation function described in step [S3] to aggregate the row-filtered result data by column, and complete the filtering and reorganization of multiple fields corresponding to a single element; [M3] Python-based data processing module: The data that completes the screening and reorganization of a single element corresponding to multiple fields in [M2] is used as the input of the [M3] module, and the traversal loop operations described in steps [S4.1] to [S4.5] are performed in sequence to complete the splitting, deduplication, sorting, and merging of all elements in all cells of the X+1th row, and realize the standardized correspondence of multiple elements based on field association; [M4] Result feedback module: Feedback the standardized corresponding results of multiple elements based on field association after being processed by the [M3] module.

Images