A data integration control method
Through VBA development program, the data in the hazard source identification table is combined with the organizational structure table and user table, which solves the problems of large labor consumption and high error rate in the data collection process, realizes efficient integration and accuracy of data, and shortens the system's online launch time.
Patent Information
- Application Number
- CN202110954594.X
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2021-08-19
- Publication Date
- 2024-11-26
- Estimated Expiration
- Not applicable · inactive patent
AI Technical Summary
In the existing technology, data collection and sorting work requires a lot of manpower, and due to the incomplete personnel quality and full of data loopholes, the data error rate is high, which affects the system's online time.
Using VBA development program, the data in the hazard source identification table is combined with the organizational structure table and user table, and the number of data rows is determined through specific character columns and formulas, advanced filtering and encoding are formed to form an orderly identification part, risk point and Control measures to reduce data duplication and form corresponding relationships.
It effectively reduces the time and error rate during the data collection process, greatly shortens the system's online time, and has extensive promotion and use value.
Smart Images

Figure CN114048933B_ABST
Abstract
Description
Technical Field
[0001] The invention relates to a data integration control method, belonging to the technical field of information methods. Background Art
[0002] Compared with the mature dual control system, the early data collection and collation work has become a task that requires a lot of manpower. Data collection includes the organizational table supporting the dual control system (organization name, organization code, organizational hierarchy), personnel table (personnel number, personnel login account, personnel name, personnel organization code, personnel team, personnel screening level, personnel position, etc.), identification location table (identification location name, identification location code, identification location hierarchy, etc.), risk point management table (operation area, identification location, position, risk status, operation activity, existence The data of a total of 6 tables need to be collected and organized in the format required by the system, including risk, risk level, accident type, etc.), risk identification and control measures (identification of locations, positions, activities, existing risks, risk levels, accident types, failure forms, control measures, measure categories, standard basis, possibility, frequency, severity of consequences, risk value), and user investigation list (risk point code, control measure code, responsible person code, responsible person name, responsible person team, responsible person department code, investigation cycle, etc.). This requires a lot of manpower. In view of the uneven quality of the personnel collecting the data, the collected data are also full of loopholes. Summary of the invention
[0003] The purpose of the present invention is to provide a data integration control method. By adopting VBA to develop a program, the data in the hazard source identification table is combined with the data tables of the other two related organization tables and user tables, and the data is batch-wise and orderly split into the identification location table, risk point management table, risk identification and control measures table and user investigation list table required by the system, which effectively reduces the time in the data collection process, reduces the error rate of data collection, greatly shortens the system's online time, has wide promotion and use value, and effectively solves the above-mentioned problems existing in the background technology.
[0004] The technical solution of the present invention is: a data integration control method, comprising the following steps:
[0005] (1) Extract the identification part fields in the existing hazard source identification table, form the hierarchical relationship of the identification parts by sorting specific characters, determining the total number of data rows with a formula, connecting the data, and selecting non-duplicate data through advanced screening, and encode them to form the data for identification part management;
[0006] (2) Take out the fields of work area, identification position, position and work activity and sort them, and take the fields of work area, identification position, position and work activity that are completely consistent as a risk point, and encode the risk point, take out other fields in the table to form the control measures corresponding to the risk point, and encode the control measures to form a corresponding relationship between the risk point code and the control measure code;
[0007] (3) Extract the control measure code, risk point code, up to 14 columns, a total of seven groups of responsible person names and investigation cycle data, filter the responsible person names and investigation cycle data that are not empty, connect them vertically into data columns, and encode the investigation personnel data row ID to form a corresponding relationship between the control measure code and the responsible person. Use the user table as the data source and use VLOOKUP to complete the responsible department, team, responsible person ID and investigation level data of the responsible person.
[0008] The step (1) comprises the following steps:
[0009] Step A-1: Extract the identification part field in the hazard source identification table, and separate the upper and lower levels of the identification part with "-";
[0010] Step A-2: sort the extracted identification parts into columns using "-" as separators, supporting up to five levels of sorting;
[0011] Step A-3: Use VBA to count the maximum number of rows in each column after sorting, and record it in a specific cell;
[0012] Step A-4: In order to prevent the middle-level identification parts from having the same name, the second-level identification parts are integrated, and the names of the first-level identification parts and the second-level identification parts are combined as the temporary names of the second-level identification parts. The third to fifth levels are analogous, and all the previous levels are combined and saved as the temporary names of the current level to form the identification part temporary name array data array [1];
[0013] Step A-5: Store the temporary names of the identification parts with data from the first to the fifth level into a column in order to form a joint identification part temporary name column [2];
[0014] Step A-6: Perform advanced filtering on the data of the temporary name column of the joint identification part, select non-duplicate rows, and store them in adjacent columns to form the identification part name data column [3];
[0015] Step A-7: Encode the identification part name data column according to the rule of "unit abbreviation-P-part code" to form the identification part code [4];
[0016] Step A-8: Find the hierarchical relationship between identification part codes: Use VLOOUP ([1], [3] [4], 0) to retrieve the corresponding identification part codes in the identification part temporary name column data array [1] according to the total number of rows in each column of data, and form the identification part code data array [5];
[0017] Step A-9: Since there is a parent node and child node relationship between two adjacent columns in the identification part code data array [5], the two adjacent columns are sequentially connected together to form a parent part code and identification part code column, and the split data are sequentially connected together to form an identification part name column;
[0018] Step A-10: Perform advanced screening on the joint area name composed of the three fields of upper-level part code, identification part code, and identification part, and take non-repeated data rows to form the identification part import data.
[0019] The step (2) comprises the following steps:
[0020] Step B-1: Take out the fields of work area, identification position, position and work activity and sort them in the order of the primary keyword work area, the secondary keyword identification position, the third keyword position and the fourth keyword work activity;
[0021] Step B-2: The same operating area, identification position, position and operating activity are regarded as a risk point and coded according to the coding rule: unit abbreviation + eight-digit date + "-FX-" + five-digit number. This will result in multiple rows of data being coded for the same risk point;
[0022] Step B-3: Take the risk point code, operation area code, risk level, possibility and risk value fields as the data of the control measures for encoding. The encoding rule is "GK_" + risk point code + "_" + the number of repetitions of the risk point code to form a corresponding relationship between the risk point code and the control measure code.
[0023] The step (3) comprises the following steps:
[0024] Step C-1: Take out the control measure codes and risk point codes, group the collected names of responsible persons and investigation periods, and process up to seven groups of data at the same time;
[0025] Step C-2: Filter the data in the first group according to the name of the responsible person and the investigation period is not empty, and place the filtered data in the AD column of sheet1 and store them according to the control measure code, risk point code, name of the responsible person and investigation period;
[0026] Step C-3: Clear the current filter, filter the data of the second group according to the name of the responsible person and the investigation period is not empty, put the filtered data into the EH column of sheet1 and store it according to the control measure code, risk point code, name of the responsible person and investigation period, and process and store the data of other groups in turn;
[0027] Step C-4: Count the number of rows of data in each of the seven groups of data, and connect the seven groups of data vertically in order according to the number of rows of data;
[0028] Step C-5: Use the user table as the data source and use VLOOKUP to complete the responsible department, team, responsible person ID and investigation level data of the responsible person's name.
[0029] The beneficial effect of the present invention is that by adopting VBA to develop a program, the data in the hazard source identification table is combined with the data tables of the other two related organization tables and user tables, and is batch- and orderly-split into the identification location table, risk point management table, risk identification and control measures table, and user investigation list table required by the system, which effectively reduces the time in the data collection process, reduces the error rate of data collection, greatly shortens the system's online time, and has wide promotion and use value. BRIEF DESCRIPTION OF THE DRAWINGS
[0030] Figure 1 is a process control diagram of the present invention;
[0031] Figure 2 It is a hazard identification table collected;
[0032] Figure 3 It is the data source format required for risk point management;
[0033] Figure 4 It is the data source format required for risk identification and control measures;
[0034] Figure 5 This is the data source format required for the user checklist. DETAILED DESCRIPTION
[0035] In order to make the purpose, technical solutions and advantages of the invention implementation cases clearer, the technical solutions in the implementation cases of the present invention will be clearly and completely described below in conjunction with the drawings in the implementation cases. Obviously, the implementation cases described are only a small part of the implementation cases of the present invention, rather than all the implementation cases. Based on the implementation cases in the present invention, all other implementation cases obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.
[0036] A data integration control method comprises the following steps:
[0037] (1) Extract the identification part fields in the existing hazard source identification table, form the hierarchical relationship of the identification parts by sorting specific characters, determining the total number of data rows with a formula, connecting the data, and selecting non-duplicate data through advanced screening, and encode them to form the data for identification part management;
[0038] (2) Take out the fields of work area, identification position, position and work activity and sort them, and take the fields of work area, identification position, position and work activity that are completely consistent as a risk point, and encode the risk point, take out other fields in the table to form the control measures corresponding to the risk point, and encode the control measures to form a corresponding relationship between the risk point code and the control measure code;
[0039] (3) Extract the control measure code, risk point code, up to 14 columns, a total of seven groups of responsible person names and investigation cycle data, filter the responsible person names and investigation cycle data that are not empty, connect them vertically into data columns, and encode the investigation personnel data row ID to form a corresponding relationship between the control measure code and the responsible person. Use the user table as the data source and use VLOOKUP to complete the responsible department, team, responsible person ID and investigation level data of the responsible person.
[0040] The step (1) comprises the following steps:
[0041] Step A-1: Extract the identification part field in the hazard source identification table, and separate the upper and lower levels of the identification part with "-";
[0042] Step A-2: sort the extracted identification parts into columns using "-" as separators, supporting up to five levels of sorting;
[0043] Step A-3: Use VBA to count the maximum number of rows in each column after sorting, and record it in a specific cell;
[0044] Step A-4: In order to prevent the middle-level identification parts from having the same name, the second-level identification parts are integrated, and the names of the first-level identification parts and the second-level identification parts are combined as the temporary names of the second-level identification parts. The third to fifth levels are analogous, and all the previous levels are combined and saved as the temporary names of the current level to form the identification part temporary name array data array [1];
[0045] Step A-5: Store the temporary names of the identification parts with data from the first to the fifth level into a column in order to form a joint identification part temporary name column [2];
[0046] Step A-6: Perform advanced filtering on the data of the temporary name column of the joint identification part, select non-duplicate rows, and store them in adjacent columns to form the identification part name data column [3];
[0047] Step A-7: Encode the identification part name data column according to the rule of "unit abbreviation-P-part code" to form the identification part code [4];
[0048] Step A-8: Find the hierarchical relationship between identification part codes: Use VLOOUP ([1], [3] [4], 0) to retrieve the corresponding identification part codes in the identification part temporary name column data array [1] according to the total number of rows in each column of data, and form the identification part code data array [5];
[0049] Step A-9: Since there is a parent node and child node relationship between two adjacent columns in the identification part code data array [5], the two adjacent columns are sequentially connected together to form a parent part code and identification part code column, and the split data are sequentially connected together to form an identification part name column;
[0050] Step A-10: Perform advanced screening on the joint area name composed of the three fields of upper-level part code, identification part code, and identification part, and take non-repeated data rows to form the identification part import data.
[0051] The step (2) comprises the following steps:
[0052] Step B-1: Take out the fields of work area, identification position, position and work activity and sort them in the order of the primary keyword work area, the secondary keyword identification position, the third keyword position and the fourth keyword work activity;
[0053] Step B-2: The same operating area, identification position, position and operating activity are regarded as a risk point and coded according to the coding rule: unit abbreviation + eight-digit date + "-FX-" + five-digit number. This will result in multiple rows of data being coded for the same risk point;
[0054] Step B-3: Take the risk point code, operation area code, risk level, possibility and risk value fields as the data of the control measures for encoding. The encoding rule is "GK_" + risk point code + "_" + the number of repetitions of the risk point code to form a corresponding relationship between the risk point code and the control measure code.
[0055] The step (3) comprises the following steps:
[0056] Step C-1: Take out the control measure codes and risk point codes, group the collected names of responsible persons and investigation periods, and process up to seven groups of data at the same time;
[0057] Step C-2: Filter the data in the first group according to the name of the responsible person and the investigation period is not empty, and place the filtered data in the AD column of sheet1 and store them according to the control measure code, risk point code, name of the responsible person and investigation period;
[0058] Step C-3: Clear the current filter, filter the data of the second group according to the name of the responsible person and the investigation period is not empty, put the filtered data into the EH column of sheet1 and store it according to the control measure code, risk point code, name of the responsible person and investigation period, and process and store the data of other groups in turn;
[0059] Step C-4: Count the number of rows of data in each of the seven groups of data, and connect the seven groups of data vertically in order according to the number of rows of data;
[0060] Step C-5: Use the user table as the data source and use VLOOKUP to complete the responsible department, team, responsible person ID and investigation level data of the responsible person's name.
[0061] Example:
[0062] Take the collected data table to identify the location data column and follow the steps below: Figure 1 Generates the data for identification part management
[0063] (1) Extract the identification part field in the hazard source identification table, and separate the upper and lower levels of the identification part with “-”;
[0064] Identification Parts Informatization Division-Office Supplies Informatization Division-Office Supplies Informatization Division-Electrical Informatization Division-Electrical Informatization Division-Electrical Informatization Division-Electrical Informatization Division-Computer Informatization Division-Computer
[0065] Information Technology Division - Computer
[0066] (2) The extracted identification parts are sorted into columns using "-" as a separator, supporting up to 5 levels of sorting;
[0067]
[0068] (3) Use VBA to count the maximum number of rows in each column after sorting, and record it in a specific cell, with two columns with 10 rows each and a title;
[0069] (4) To prevent the middle-level identification parts from having the same name, the second-level identification parts are integrated, and the names of the first-level identification parts and the second-level identification parts are combined as the temporary names of the second-level identification parts. The third to fifth levels are analogous, and all the previous levels are combined and saved as the temporary names of the current level, forming an identification part temporary name array data array [1];
[0070]
[0071]
[0072] (5) The temporary names of the identification parts with data from the first to the fifth levels are stored in a column in order to form a joint identification part temporary name column [2];
[0073] Paste all identified parts here
[0074] Information Technology Department
[0075] Information Technology Department
[0076] Information Technology Department
[0077] Information Technology Department
[0078] Information Technology Department
[0079] Information Technology Department
[0080] Information Technology Department
[0081] Information Technology Department
[0082] Information Technology Department
[0083] Information Technology Division Office Supplies
[0084] Information Technology Division Office Supplies
[0085] Information Technology Division Electrical
[0086] Information Technology Division Electrical
[0087] Information Technology Division Electrical
[0088] Information Technology Division Electrical
[0089] Computer of Information Division
[0090] Computer of Information Division
[0091] Computer of Information Division
[0092] (6) Perform advanced filtering on the data of the temporary name column of the joint identification part, select non-duplicate rows, and store them in adjacent columns to form the identification part name data column [3];
[0093] Filter non-repeated identification parts
[0094] Information Technology Department
[0095] Information Technology Division Office Supplies
[0096] Information Technology Division Electrical
[0097] Computer of Information Division
[0098] (7) Encode the identification part name data column according to the rule of "unit abbreviation-P-part code" to form the identification part code [4];
[0099]
[0100] (8) Finding the hierarchical relationship between identification part codes: Using VLOOUP([1], [3], [4], 0) in sequence according to the total number of rows in each column of data in the identification part temporary name column data array [1], retrieve the corresponding identification part code to form the identification part code data array [5];
[0101]
[0102] (9) Since there is a parent node and child node relationship between two adjacent columns in the identification part code data array [5], the two adjacent columns are sequentially connected together to form a parent part code and identification part code column, and the split data are sequentially connected together to form an identification part name column;
[0103]
[0104]
[0105] (10) Perform advanced screening on the joint area name composed of the three fields of superior part code, identification part code, and identification part, and select non-duplicate data rows to form the data source for importing the identification part
[0106]
[0107] 2. Take out the fields such as operation area, identification part, position, operation activity, etc. and sort them, and take the fields with the same operation area, identification part, position, and activity as a risk point, and encode the risk point. Take out other fields in the table to form the control measures corresponding to the risk point, and encode the control measures to form the corresponding relationship between the risk point code and the control measure code.
[0108] (1) Extract the fields such as work area, identification location, position, work activity, and risk, and sort them in the order of the primary keyword work area, the secondary keyword identification location, the third keyword position, and the fourth keyword work activity;
[0109] Serial number Work Area Identify the part post Activity There are risks 1 Information Technology Department Information Technology Division-Office Supplies Computer Operator Daily Office Tools and office supplies being transported 9 Information Technology Department Information Technology Division-Office Supplies Computer Operator Daily Office Open pocket knife 2 Information Technology Division Information Technology Division-Electrical Computer Operator Daily office 1 Fires caused by electrical equipment and electrical equipment1 3 Information Technology Department Information Technology Division-Electrical Computer Operator Daily office 1 Fires caused by electrical equipment and electrical equipment1 4 Information Technology Division Information Technology Division-Electrical Computer Operator Daily Office 3 Fires caused by electrical equipment and electrical equipment3 5 Information Technology Department Information Technology Division-Electrical Computer Operator Daily Office 3 Fires caused by electrical equipment and electrical equipment3 6 Information Technology Division Information Technology Division - Computer Computer Operator Daily Office 3 Electrical equipment 3 7 Information Technology Department Information Technology Division - Computer Computer Operator Daily Office 2 Electrical Equipment 2 8 Information Technology Department Information Technology Division - Computer Computer Operator Daily Office 2 Electrical Equipment 2
[0110] (2) The same operating area, identification position, position, and operating activity are considered as a risk point and coded according to the coding rule: unit abbreviation + eight-digit date + "-FX-" + five-digit number. This will result in multiple lines of data being coded for the same risk point;
[0111] Work Area Identify the part post Activity Risk point coding Information Technology Department Information Technology Division-Office Supplies Computer Operator Daily Office LG20210108-FX-00001 Information Technology Department Information Technology Division-Office Supplies Computer Operator Daily Office LG20210108-FX-00001 Information Technology Department Information Technology Division-Electrical Computer Operator Daily office 1 LG20210108-FX-00002 Information Technology Department Information Technology Division-Electrical Computer Operator Daily office 1 LG20210108-FX-00002 Information Technology Department Information Technology Division-Electrical Computer Operator Daily Office 3 LG20210108-FX-00003 Information Technology Department Information Technology Division-Electrical Computer Operator Daily Office 3 LG20210108-FX-00003 Information Technology Department Information Technology Division - Computer Computer Operator Daily Office 3 LG20210108-FX-00004 Information Technology Department Information Technology Division - Computer Computer Operator Daily Office 2 LG20210108-FX-00005 Information Technology Department Information Technology Division - Computer Computer Operator Daily Office 2 LG20210108-FX-00005
[0112] (3) Take the risk point code, operation area code, risk level, possibility, risk value and other fields as the data of the control measures for encoding. The encoding rule is "GK_" + risk point code + "_" + the number of repetitions of the risk point code to form a corresponding relationship between the risk point code and the control measure code;
[0113]
[0114]
[0115] 3. Take out the control measure codes, risk point codes, up to 14 columns totaling seven groups of responsible person names, and investigation cycle data, filter out the responsible person names and investigation cycle data that are not empty, connect them vertically into data columns, and encode the investigation personnel data row ID to form a corresponding relationship between the control measure codes and the responsible persons. Use the user table as the data source, and use VLOOKUP to complete the responsible department, team, responsible person ID, investigation level and other data of the responsible person.
[0116] (1) Extract the control measure code, risk point code, and group the collected names of responsible persons and investigation periods. Up to seven groups of data can be processed simultaneously;
[0117]
[0118] (2) Filter the data in the first group according to the name of the responsible person and the investigation period is not empty, and place the filtered data in the AD column of sheet 1 and store it according to the control measure code, risk point code, name of the responsible person, and investigation period;
[0119]
[0120] (3) Clear the current filter, filter the data of the second group according to the name of the responsible person and the investigation period is not empty, put the filtered data into the EH column of sheet 1 and store it according to the control measure code, risk point code, name of the responsible person, and investigation period, and process and store the data of other groups in turn;
[0121]
[0122] (4) Count the number of rows of data in each of the seven groups of data, and connect the seven groups of data vertically in order according to the number of rows of data;
[0123]
[0124]
[0125] (5) Using the user table as the data source, use VLOOKUP to complete the responsible department, team, responsible person ID, investigation level and other data of the responsible person's name.
[0126] ID Risk point coding Control measures code Responsible Department Responsible person ID Troubleshooting cycle team Troubleshooting level LG20210108-U-00001 LG20210108-FX-00001 GK_LG20210108-FX-00001_1 LG01 Yaojian year Chang Bai Team level LG20210108-U-00002 LG20210108-FX-00002 GK_LG20210108-FX-00002 LG01 Yaojian year Chang Bai Team level LG20210108-U-00003 LG20210108-FX-00002 GK_LG20210108-FX-00002_1 LG01 Yaojian year Chang Bai Team level LG20210108-U-00004 LG20210108-FX-00003 GK_LG20210108-FX-00003 LG01 Yaojian year Chang Bai Team level LG20210108-U-00005 LG20210108-FX-00003 GK_LG20210108-FX-00003_1 LG01 Yaojian year Chang Bai Team level LG20210108-U-00006 LG20210108-FX-00004 GK_LG20210108-FX-00004 LG01 Yaojian year Chang Bai Team level LG20210108-U-00007 LG20210108-FX-00005 GK_LG20210108-FX-00005 LG01 Yaojian year Chang Bai Team level LG20210108-U-00008 LG20210108-FX-00005 GK_LG20210108-FX-00005_1 LG01 Yaojian year Chang Bai Team level LG20210108-U-00009 LG20210108-FX-00001 GK_LG20210108-FX-00001 LG01 tianzhiwei quarter Chang Bai Factory level LG20210108-U-00010 LG20210108-FX-00001 GK_LG20210108-FX-00001_1 LG01 tianzhiwei quarter Chang Bai Factory level LG20210108-U-00011 LG20210108-FX-00002 GK_LG20210108-FX-00002 LG01 tianzhiwei quarter Chang Bai Factory level LG20210108-U-00012 LG20210108-FX-00003 GK_LG20210108-FX-00003 LG01 tianzhiwei quarter Chang Bai Factory level LG20210108-U-00013 LG20210108-FX-00003 GK_LG20210108-FX-00003_1 LG01 tianzhiwei quarter Chang Bai Factory level LG20210108-U-00014 LG20210108-FX-00004 GK_LG20210108-FX-00004 LG01 tianzhiwei quarter Chang Bai Factory level LG20210108-U-00015 LG20210108-FX-00005 GK_LG20210108-FX-00005 LG01 tianzhiwei quarter Chang Bai Factory level LG20210108-U-00016 LG20210108-FX-00005 GK_LG20210108-FX-00005_1 LG01 tianzhiwei quarter Chang Bai Factory level LG20210108-U-00017 LG20210108-FX-00001 GK_LG20210108-FX-00001 LG01 gaoshifeng month Chang Bai Factory level LG20210108-U-00018 LG20210108-FX-00001 GK_LG20210108-FX-00001_1 LG01 gaoshifeng month Chang Bai Factory level LG20210108-U-00019 LG20210108-FX-00002 GK_LG20210108-FX-00002 LG01 gaoshifeng month Chang Bai Factory level LG20210108-U-00020 LG20210108-FX-00002 GK_LG20210108-FX-00002_1 LG01 gaoshifeng month Chang Bai Factory level LG20210108-U-00021 LG20210108-FX-00003 GK_LG20210108-FX-00003 LG01 gaoshifeng month Chang Bai Factory level LG20210108-U-00022 LG20210108-FX-00005 GK_LG20210108-FX-00005 LG01 gaoshifeng month Chang Bai Factory level LG20210108-U-00023 LG20210108-FX-00005 GK_LG20210108-FX-00005_1 LG01 gaoshifeng month Chang Bai Factory level
Claims
1. A data integration control method, characterized in that The following steps are involved: (1) Extract the identification part fields in the existing hazard source identification table, form the hierarchical relationship of the identification parts by sorting specific characters, determining the total number of data rows with a formula, connecting the data, and selecting non-duplicate data through advanced screening, and encode them to form the data for identification part management; (2) Take out the fields of work area, identification location, position and work activity and sort them. The fields of work area, identification location, position and work activity that are completely consistent are regarded as a risk point and coded. Take out other fields in the table to form the control measures corresponding to the risk point and code the control measures to form a corresponding relationship between the risk point code and the control measure code. (3) Take out the control measure code, risk point code, up to 14 columns, a total of seven groups of responsible person names and investigation cycle data, select the responsible person names and investigation cycle data that are not empty, connect them vertically into data columns, and encode the investigation personnel data row ID to form a corresponding relationship between the control measure code and the responsible person. Use the user table as the data source and use VLOOKUP to complete the responsible department, team, responsible person ID and investigation level data of the responsible person; The step (1) comprises the following steps: Step A-1: Extract the identification part field in the hazard source identification table, and use - to separate the upper and lower levels of the identification part; Step A-2: sort the extracted identification parts into columns using - as a separator, supporting up to five levels of sorting; Step A-3: Use VBA to count the maximum number of rows in each column after sorting, and record it in a specific cell; Step A-4: In order to prevent the middle-level identification parts from having the same name, the second-level identification parts are integrated, and the names of the first-level identification parts and the second-level identification parts are combined as the temporary names of the second-level identification parts. The third to fifth levels are analogous, and all the previous levels are combined and saved as the temporary names of the current level to form the identification part temporary name array data array [1]; Step A-5: Store the temporary names of the identification parts with data from the first to the fifth level into a column in order to form a joint identification part temporary name column [2]; Step A-6: Perform advanced filtering on the data of the temporary name column of the joint identification part, select non-duplicate rows, and store them in adjacent columns to form the identification part name data column [3]; Step A-7: Encode the identification part name data column according to the rule of "unit abbreviation-P-part code" to form the identification part code [4]; Step A-8: Find the hierarchical relationship between identification part codes: Use VLOOUP ([1], [3] [4], 0) to retrieve the corresponding identification part codes in the identification part temporary name column data array [1] according to the total number of rows in each column of data, and form the identification part code data array [5]; Step A-9: Since there is a parent node and child node relationship between two adjacent columns in the identification part code data array [5], the two adjacent columns are sequentially connected together to form a parent part code and identification part code column, and the split data are sequentially connected together to form an identification part name column; Step A-10: Perform advanced screening on the joint area name composed of the three fields of upper part code, identification part code, and identification part, and select non-repeated data rows to form identification part import data; The step (2) includes the following steps: Step B-1: Take out the fields of work area, identification position, position and work activity and sort them in the order of the primary keyword work area, the secondary keyword identification position, the third keyword position and the fourth keyword work activity; Step B-2: The same operating area, identification position, position and operating activity are regarded as a risk point and coded according to the coding rule: unit abbreviation + eight-digit date + FX + five-digit number. This will result in multiple rows of data being coded for the same risk point; Step B-3: Take the risk point code, operation area code, risk level, possibility and risk value fields as the data of the control measures for encoding. The encoding rule is GK+risk point code+_+the number of repetitions of the risk point code to form a corresponding relationship between the risk point code and the control measure code; The step (3) includes the following steps: Step C-1: Take out the control measure codes and risk point codes, group the collected names of responsible persons and investigation periods, and process up to seven groups of data at the same time; Step C-2: Filter the data in the first group according to the name of the responsible person and the investigation period is not empty, and place the filtered data in the AD column of sheet1 and store them according to the control measure code, risk point code, name of the responsible person and investigation period; Step C-3: Clear the current filter, filter the data of the second group according to the name of the responsible person and the investigation period is not empty, put the filtered data into the EH column of sheet1 and store it according to the control measure code, risk point code, name of the responsible person and investigation period, and process and store the data of other groups in turn; Step C-4: Count the number of rows of data in each of the seven groups of data, and connect the seven groups of data vertically in order according to the number of rows of data; Step C-5: Use the user table as the data source and use VLOOKUP to complete the responsible department, team, responsible person ID and investigation level data of the responsible person's name.
