Engineering project detection data statistical analysis management system based on EXCEL

Abstract

The invention discloses an engineering project detection data statistical analysis management system based on EXCEL, which is operated based on EXCEL, and comprises a data input module used for acquiring basic information and a detection result of an engineering project detection target; the data analysis module is used for calculating engineering project detection indexes according to the basic information and the detection result; the engineering project detection indexes comprise detection quantity, detection frequency and detection results; the data pre-judgment module is used for judging whether follow-up analysis and evaluation are carried out or not according to the relation among the detection quantity, the detection frequency and the detection result; the data evaluation module is used for evaluating the quality of the engineering project detection target according to the detection indexes to generate an evaluation result; and the data output module is used for displaying detection indexes and evaluation results. Comprising. According to the technical scheme, the efficiency and accuracy of statistical analysis of the detection data and the utilization rate of the data can be improved, so that the purpose of quickly, accurately and comprehensively obtaining the statistical analysis result of the detection data is achieved.

Description

Technical Field

[0001] The present invention relates to the technical field of data analysis, and in particular to an EXCEL-based engineering project detection data statistical analysis management system. Background Art

[0002] Testing is an important means to ensure the quality of the project and reduce the risk of the project during the implementation of the project. With the development of social economy, it is becoming more and more important. It is very important to quickly obtain quality-related information from a large amount of test data. The analysis of test data requires the comprehensive test results of multiple units such as construction units, supervision units and test centers, and evaluation according to the specifications. However, the current analysis and processing of test data is relatively simple, especially when the test data involves different standards, there are many participating units, the test samples of each unit are different, and the implementation standards are different, which may lead to omissions in the test indicators and the test results cannot accurately evaluate the quality of the project. As for the shortcomings of the test, the existing test analysis is relatively simple, and it cannot evaluate the shortcomings of the existing test plans and test data, and cannot directly locate the test links that need to be improved. Therefore, it cannot further guide the test personnel to use the data, such as judging the stability of raw material quality and judging whether it is necessary to carry out mix ratio optimization.

[0003] Therefore, a technical solution is needed to build a systematic integrated management system for engineering project inspection data from multiple aspects such as inspection volume, inspection frequency and inspection results, to achieve comprehensive analysis and evaluation of inspection data at each link, and to suggest optimization solutions. Summary of the invention

[0004] To achieve the above purpose, the present application provides an engineering project inspection data statistical analysis management system based on EXCEL, which runs based on EXCEL and includes the following contents:

[0005] Data input module: used to obtain basic information and test results of engineering project test targets; the test sources of the engineering project test targets include: self-inspection by the construction unit, random inspection by the supervision center, test and random inspection by the geophysical exploration center; the sample categories of the test targets include: raw materials, intermediate products, on-site inspection and geophysical exploration inspection;

[0006] Data analysis module: used to calculate the engineering project detection indicators based on basic information and detection results; the engineering project detection indicators include: detection volume, detection frequency and detection results;

[0007] Data pre-judgment module: used to determine whether to conduct subsequent analysis and evaluation based on the relationship between the detection amount, detection frequency and detection result; if the detection amount value is less than the detection frequency value and the detection result value = 0, no subsequent data evaluation and analysis will be conducted;

[0008] Data evaluation module: used to evaluate the quality of the project inspection target according to the inspection indicators and generate evaluation results. The evaluation dimensions of the quality evaluation include: completeness, accuracy and effectiveness; the evaluation dimension indicators include: inspection frequency, completeness of inspection results, accuracy of inspection results, sampling method, and effectiveness of sampling location;

[0009] Data output module: used to display detection indicators and evaluation results.

[0010] Wherein, the data input module includes: a basic information unit and a test result unit;

[0011] The basic information unit is used to obtain basic information of engineering project detection and standardize the basic information;

[0012] The detection result unit obtains the detection result corresponding to the basic information, and performs data standardization on the detection result; the data standardization includes: the detection result corresponds to the basic information, and the detection result unit is unified.

[0013] The data analysis module includes: a detection quantity statistical analysis unit, a detection frequency statistical analysis unit and a detection result statistical analysis unit;

[0014] The detection quantity statistical analysis unit is used to establish the relationship between different parameters and the detection quantity;

[0015] The detection frequency statistical analysis unit is used to classify the basic information and detection results according to raw materials, intermediate products, on-site detection and geophysical detection, calculate the number of detection groups and the number of groups to be inspected, determine the detection frequency, and divide the focus area according to the detection frequency;

[0016] The test result statistical analysis unit is used to count the mathematical statistical parameter values ​​of the test results and extract the sample information corresponding to the unqualified indicators. The mathematical statistical parameter values ​​include: minimum value, maximum value and average value; the unqualified indicator sample information includes: the manufacturer and factory batch number information corresponding to the raw materials, and the detection part information corresponding to the intermediate products, on-site detection and geophysical detection.

[0017] The data output module includes: a detection quantity display unit, a detection frequency display unit, a detection result display unit and a data evaluation unit;

[0018] The detection quantity display unit is used to display the results and classification of the detection quantity data under different parameter selections through a slicer and a data perspective chart; wherein the slicer and the data perspective chart are linked to switch the corresponding analysis results in real time;

[0019] The detection frequency display unit is used to intuitively display and warn the detection frequency attributes of different detection units, and the detection frequency attributes include area and mark color;

[0020] The test result display unit uses a table format to display the test results and unqualified information;

[0021] The data evaluation unit is used to form the evaluation dimension indicator assignment, evaluation factor, and evaluation level of the engineering project detection target.

[0022] Furthermore, the types of areas of concern include Zone I, Zone II, and Zone III; Zone I indicates that the detection frequency is lower than the value specified in the regulations and specifications, Zone II indicates that the detection frequency is between the lower limit and the upper limit, and Zone III indicates that the detection frequency is higher than 2 times the specified value;

[0023] The types of areas of concern are distinguished by color markings, indicating different levels of concern, including: Zone I is marked in red, indicating that the detection frequency does not meet the requirements of the standard and a warning is given; Zone II is not marked, indicating that the detection frequency meets the requirements; Zone III is marked in orange, indicating that the detection frequency is too high and the detection frequency can be appropriately reduced.

[0024] Furthermore, integrity includes the integrity of the test frequency and test results, specifically, whether the test frequency is consistent with the specification requirements or whether there are deficiencies or surpluses, etc. The integrity of the test results includes: judging whether the actual test indicators are complete according to the specification requirements;

[0025] The accuracy mentioned refers to whether the test results are qualified;

[0026] The effectiveness includes the effectiveness of the sampling method and the sampling location, specifically, the sampling method meets the requirements of the specification, and the sampling location is random and includes all unit projects.

[0027] Specifically, the basic information of the project inspection target includes: sample sampling date, inspection date, sample category, inspection item, specification model, factory batch number, manufacturer, number of representatives, number of sampling groups, inspection unit, inspected unit, and inspection location;

[0028] Sample categories include: raw materials, intermediate products, on-site testing and geophysical testing; the testing units include the testing and geophysical center, the supervision center and the construction units; the inspected units are the construction units of different sections.

[0029] The number of groups to be inspected shall be determined based on the multiple of the number of representatives and the upper limit value specified in the standard;

[0030] When the number of representatives is ≤ the upper limit specified in the standard, the number of groups to be inspected is 1;

[0031] When the upper limit value specified in the standard is less than the representative quantity ≤ 2 times the upper limit value specified in the standard, the number of groups to be tested is 2;

[0032] When 2 times the upper limit value specified in the standard < the representative quantity ≤ 3 times the upper limit value specified in the standard, the number of groups to be inspected is 3; and so on.

[0033] Furthermore, the detection quantity value is the number of detection groups actually sampled;

[0034] The detection frequency is an indicator used to determine the relationship between the detection value and the number of groups to be tested. If the detection value is greater than the number of groups to be tested, the detection frequency meets the requirement; the detection frequency value is the number of groups to be tested that is automatically calculated;

[0035] The test result values ​​are used to count the minimum, maximum and average values ​​of various test indicators on the one hand; on the other hand, they are used to indicate whether there are unqualified items. If there are unqualified items, the total number of unqualified items is automatically counted.

[0036] Further, the data evaluation module includes a detection data evaluation unit, which establishes an evaluation factor K and an evaluation level to evaluate and analyze the effect of the data;

[0037] The evaluation factor is defined as the sum of the product of the completeness, accuracy and effectiveness index values ​​and weights, expressed as: K = m 1 ×a i ×b i +m 2 ×d i +m 3 ×f i ,

[0038] Where: m 1 is the integrity index weight, m 2 is the accuracy index weight, m 3 is the effectiveness indicator weight; a i Assign values ​​to the detection index, b i Assign a value to the detection frequency index, d i Assign a value to the accuracy index, f i Assign values ​​to effectiveness indicators.

[0039] According to the present invention, the detection quantities of different parameters can be statistically analyzed and displayed in charts, and color warnings can be given for items of different levels, so as to guide detection personnel to locate detection indicators that need attention from a large number of inspection items, and automatically analyze and compare whether the values ​​of various detection items meet the specified values ​​of the specification standards; at the same time, the detection results from different detection sources are standardized, and multi-party data are integrated to ensure the universality of the detection results of each link, ultimately improving the efficiency and accuracy of the statistical analysis of the detection data and the utilization rate of the data, thereby achieving the purpose of quickly, accurately and comprehensively obtaining the statistical analysis results of the detection data. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Figure 1It is a schematic diagram of the structure of a statistical analysis management system for engineering project inspection data provided in accordance with an embodiment of the present invention;

[0041] Figure 2 is a schematic diagram of a main interface provided according to an embodiment of the present invention;

[0042] Figure 3 is a schematic diagram of an input template provided according to an embodiment of the present invention;

[0043] Figure 4 is a schematic diagram of the arrangement of a detection quantity display unit according to an embodiment of the present invention;

[0044] Figure 5 is a schematic diagram of a detection quantity display unit provided according to an embodiment of the present invention;

[0045] Figure 6 is a schematic diagram of a detection frequency display unit provided in an embodiment of the present invention;

[0046] Figure 7 is a schematic diagram of a detection result display unit provided in an embodiment of the present invention;

[0047] Figure 8 2 is a schematic diagram of a data evaluation display unit provided according to an embodiment of the present invention. DETAILED DESCRIPTION

[0048] The present invention starts from three aspects, namely, detection quantity, detection frequency and detection result, combines detection data of different detection units, and creates a systematic engineering project detection data statistical analysis management system based on EXCEL. In the management system, the content of each cell in the EXCEL table is determined according to the detection data statistical analysis requirements, the relationship between different detection information and data, and the relationship between different detection information are determined by setting the cell association relationship, and the detection quantity, detection frequency and detection result are calculated by EXCEL operation function formula to verify whether the data is valid.

[0049] The specific implementation of the present invention is described in detail below with reference to the accompanying drawings.

[0050] Figure 1 A schematic diagram of the system structure of the present invention is provided. As shown in the figure, the engineering project detection data statistical analysis management system provided by the present invention includes the following parts:

[0051] P100 data input module: used to obtain basic information and test results of engineering project test targets;

[0052] In the actual application environment, the units involved in the inspection of engineering projects involve three parties: the construction unit, the supervision center, and the test and geophysical exploration center. In order to facilitate the comparative analysis of the inspection data of the three parties and further understand the quality of the project, this management system not only supports the use of each of the three inspection units, but also has the function of simultaneously analyzing and displaying the inspection data of the three parties. Therefore, in the present invention, the inspection sources of the engineering project inspection targets include: self-inspection by the construction unit, random inspection by the supervision center, and random inspection by the test and geophysical exploration center.

[0053] At the same time, samples of the detection targets are classified into categories including: raw materials, intermediate products, on-site detection and geophysical detection.

[0054] Based on the statistical analysis management system that supports the above data, its main interface is as follows Figure 2 shown.

[0055] Simply put, the data input module can realize the data entry of detection data and information, such as Figure 1 As shown in , the structure of input data includes: basic information unit and test result unit;

[0056] The basic information unit is used to obtain basic information of the engineering project inspection, and the acquisition method supports identification from the engineering project inspection file or direct entry by the management personnel; after identifying the basic information of the inspection target from the engineering project inspection file or directly entering the information, the basic information needs to be standardized;

[0057] The basic information of the project inspection target includes: sample sampling date, inspection date, sample category, inspection item, specification model, factory batch number, manufacturer, number of representatives, number of sampling groups, inspection unit, inspected unit, and inspection location;

[0058] Standardization of basic information means that the basic information unit uses functional functions to extract the entered sample sampling date according to "year", "month" and "day", and displays it in the corresponding position; and uses the IF nested function to form a program to calculate the representative quantity information in the basic information, generate the number of groups to be tested for the sample, and display it in the corresponding position.

[0059] The test result unit is used to obtain the test results corresponding to the basic information and perform data standardization on the test results. When obtaining the test results corresponding to the basic information, it can be identified from the engineering project test files provided by each unit, or it can be directly entered by the management personnel.

[0060] Since the test items for different samples are inconsistent, the units of the test result data are also inconsistent. Therefore, the test result unit also performs data standardization, including matching the test results with basic information and unifying the units of the test results. Specifically, when importing data, the test result data is entered in sequence according to the template rules, such as serial number identifiers ①~⑩.

[0061] The test results in the test documents of each engineering project comply with the agreed template rules. In the template rules, the data corresponds to specific test indicators.

[0062] Template rules are the information columns in the EXCEL template. In the data input module, users can design, add or delete relevant information columns in the EXCEL template according to their needs. Figure 3 As shown, part a is the basic information unit content, and part b is the detection result content.

[0063] The present invention provides specific examples to explain in detail the application method and specific functions of the engineering project detection data statistical analysis management system provided by the present invention.

[0064] First, implement the data entry function through the data input module: click Figure 2 Click the "Data Entry" button on the main interface of the management system shown to jump to the data input module; enter relevant information according to the basic information unit template. In the input information, distinguish the sample categories (raw materials, intermediate products, on-site testing and geophysical testing); the test items are sample names, and raw materials need to enter specifications, models, factory batch numbers, manufacturers, number of representatives, number of sampling groups, testing units and inspected units; intermediate products, on-site testing and geophysical testing need to enter specifications, models, number of representatives, number of sampling groups, testing units, inspected units and sampling locations;

[0065] It should be noted that the inspection units are divided into three categories: testing and geophysical exploration centers, supervision centers and construction units; the inspected units are construction units of different sections.

[0066] After the above information is entered, run the program embedded in the system to automatically extract the "year", "quarter" and "month" information in the sampling date and display them in the corresponding position; the month extraction rule is: the 21st of each month to the 20th of the next month is counted as the next month. This can be achieved through the time extraction function of the system EXCEL, and the specific code is:

[0067] Extract "year", =YEAR(D85); D85 is the sampling date column;

[0068] Extract "quarter", =INT(1+(C85-1) / 3);

[0069] Extract "Month", =IF(DAY(D85)<21,MONTH(D85),MONTH(D85)+1);

[0070] Based on the number of representatives entered, the system calls the program, automatically calculates the number of groups that should be tested for the sample and displays it in the corresponding position.

[0071] The program can realize the number of groups to be inspected through the IF nested function supported by EXCEL. The calculation rules are: when the number of representatives is ≤ the upper limit value specified in the standard, the number of groups to be inspected is 1; when the upper limit value specified in the standard is < the number of representatives ≤ 2 times the upper limit value specified in the standard, the number of groups to be inspected is 2; when 2 times the upper limit value specified in the standard is < the number of representatives ≤ 3 times the upper limit value specified in the standard, the number of groups to be inspected is 3; and so on, the number of groups to be inspected is determined according to the multiples of the number of representatives and the upper limit value specified in the standard. When the number of representatives is > 4 times the upper limit value specified in the standard, the number of groups to be inspected is displayed as > 4.

[0072] The specific code in EXCEL is:

[0073] =IF(L85 / M85<1,1,IF(L85 / M85<2,2,IF(L85 / M85<3,3,IF(L85 / M85<4,4,IF(L85 / M85>4,">4"))))); L85 is the representative quantity column; M85 is the upper limit value column of the sampling quantity specified by the standard.

[0074] Since the test items for different samples are inconsistent, the units of the test result data are also inconsistent. Therefore, when entering the test result units, the test result data is directly entered in sequence according to the labels in the template (such as ①~⑩).

[0075] In the embodiment, the cement test result data is filled in as "355, 213, 291, 28.8, 0.5, 4.9, 8.0, 27.5, 47.0", indicating that the cement test result consists of 9 indicators, and the identification numbers ① to ⑨ are specific surface area, initial setting time, final setting time, standard consistency water consumption, stability, 3d flexural strength, 28d flexural strength, 3d compressive strength, and 28d compressive strength, respectively.

[0076] In the embodiment, the mortar test result data is filled in with "38.5, 37.9", indicating that there are two groups of mortar compressive strength samples, and their test index values ​​are 38.5MPa and 37.9MPa respectively.

[0077] The meanings of the remaining test results are similar to those in the above two examples, and the specific test indicators are derived from the requirements of the corresponding specifications.

[0078] If the input data contains multiple testing units, in addition to statistical analysis of the data of a certain testing unit, comparative analysis of multiple testing units can also be performed.

[0079] P110 data analysis module: used to calculate the engineering project inspection indicators based on basic information and inspection results; engineering project inspection indicators include: inspection quantity value, inspection frequency value and inspection result value; the data analysis module specifically includes inspection quantity statistical analysis unit, inspection frequency statistical analysis unit and inspection result statistical analysis unit;

[0080] 1) The detection quantity statistical analysis unit is used to establish the relationship between different parameters and the detection quantity;

[0081] In the specific implementation, a pivot table is created for the data entered into the basic information unit, and the relationship between different parameters and detection quantities is established by the pivot table. Further, according to the needs of statistical analysis, a slicer is created, and the detection quantity data information corresponding to some or all parameters is selected through the slicer. Different slicers can be linked. At the same time, the slicer properties can be edited and its content and style can be set according to needs.

[0082] Define the detection value x in the detection quantity statistical analysis unit 1 , the detection value x 1 It is the number of test groups actually sampled based on the representative quantity, such as 3 groups.

[0083] The specific operation process of the embodiment provided by the present invention is as follows:

[0084] Create a PivotTable and PivotChart for the data entered in the basic information unit, click the EXCEL analysis option, insert a slicer, and click the inspected unit, year, quarter, month, sample category, and test item in the slicer in turn to generate a total of 6 slicers; if you need to compare and analyze the data of different testing units, when generating the slicer, you only need to replace the inspected unit with the testing unit.

[0085] Slicer property settings and position determination, the specific layout diagram is as follows Figure 4 As shown, slicer ① has a size of 60mm×72mm, slicer ② and slicer ③ have a size of 16mm×62mm, slicer ④ has a size of 28mm×62mm, slicer ⑤ has a size of 53mm×25mm, and slicer ⑥ has a size of 53mm×109mm.

[0086] The column chart, pie chart and bar chart settings and positions are determined. The specific layout diagram is as follows Figure 4 As shown, the horizontal axis of the column chart is the test items, the vertical axis is the number of test groups, and the graphic size is 65mm×188mm; the pie chart is the sample category, with a size of 48mm×80mm; the horizontal axis of the bar chart is the number of test groups, and the vertical axis is the inspected units, with a size of 48mm×128mm.

[0087] The size of the statistical value display area in the upper right corner is 48mm×20mm, and the statistical value area code is: =GETPIVOTDATA("Number of detection groups", $G$55); G55 is the detection item column.

[0088] When using, click the button of the three-party detection quantity comparison analysis (or statistical analysis of the test and geophysical exploration center, statistical analysis of the supervision center, and statistical analysis of the construction unit) on the main interface to jump to the detection quantity display interface. The detection quantity statistical analysis effect diagram is as follows: Figure 5 If you update the data in the template, you need to refresh the slicer to ensure the accuracy of the displayed results.

[0089] 2) The detection frequency statistical analysis unit is used to classify the basic information and detection results according to raw materials, intermediate products, on-site detection and geophysical detection, calculate the detection frequency according to the number of detection groups and the number of groups to be inspected, and divide the focus area according to the detection frequency; the detection frequency is a detection indicator used to determine the relationship between the detection value and the number of groups to be inspected. If the detection value is greater than the number of groups to be inspected, the detection frequency meets the requirements;

[0090] The detection frequency value is the number of groups to be tested that is automatically calculated. The detection frequency value x is calculated in the detection frequency statistical analysis unit. 2 , such as 3 groups.

[0091] In the present invention, the types of areas of interest include zone I, zone II and zone III; wherein zone I indicates that the detection frequency is lower than the value specified in the regulations and specifications, zone II indicates that the detection frequency is between the lower limit and the upper limit, and zone III indicates that the detection frequency is higher than 2 times the specified value;

[0092] In the present invention, the classification of basic information and detection results is realized by using a program written by using IF nested functions.

[0093] Furthermore, through the "conditional function" that comes with EXCEL, the types of areas of concern are marked with colors and differentiated by regions to indicate different levels of concern, including: Area I is marked in red, indicating that the detection frequency does not meet the requirements of the standard and a warning is given; Area II is not marked, indicating that the detection frequency meets the requirements; Area III is marked in orange, indicating that the detection frequency is too high and the detection frequency can be appropriately reduced.

[0094] The present invention provides an implementation case and introduces the functions of data classification and regional division in the detection frequency statistical analysis unit:

[0095] The data entered into the basic information unit are classified according to raw materials, intermediate products, on-site inspection and geophysical inspection. The program written using the IF nested function automatically analyzes and compares the number of inspection groups entered into the basic information unit and the automatically calculated number of inspection groups, and divides them into Zone I, Zone II and Zone III according to the results of the analysis and comparison. The division principle is as follows: Zone I indicates that the inspection frequency is lower than the lower limit value (the lower limit value is set to the value specified in the regulations and specifications), Zone II indicates that the inspection frequency is between the lower limit value and the upper limit value, including the upper and lower limits, and Zone III indicates that the inspection frequency is higher than the upper limit value (the upper limit value is set to 2.0 times the specified value).

[0096] The specific code is as follows:

[0097] =IF(G5<F5,"Region 1",IF(G5>1.2*F5,"Region 3","Region 2")); F5 is the sequence of the groups to be measured; G5 is the sequence of the actually measured groups; this code is for the regional division of the construction unit;

[0098] =IF(J5<I5,"Region 1",IF(J5>1.2*I5,"Region 3","Region 2")); I5 is the sequence of the groups to be measured; J5 is the sequence of the actually measured groups; this code is for the regional division of the supervision center;

[0099] =IF(M5<L5,"Region 1",IF(M5>1.2*L5,"Region 3","Region 2")); L5 is the sequence of the groups to be measured; M5 is the sequence of the actually measured groups; this code is for the regional division of the test and geophysical prospecting center.

[0100] On the other hand, in the detection frequency statistical analysis unit, different colors can be assigned according to the divided regions:

[0101] Using the "conditional formatting" function built in EXCEL, color-coding marks are made for the different assigned regions. Among them, Region I is marked red, indicating that the detection frequency does not meet the requirements of the specification standards and a warning is given; Region II is not marked, indicating that the detection frequency meets the requirements; Region III is marked orange, indicating that the detection frequency is too high and the detection frequency can be appropriately reduced.

[0102] The specific operation is as follows: Select all the data in the detection frequency area attribute table, click "conditional formatting", create a new rule, "the cell value is equal to Region 1, and the format setting value shows red", "the cell value is equal to Region 3, and the format setting value shows orange".

[0103] When in use, click the three-party detection frequency comparison and analysis button on the main interface, and it will jump to the detection frequency display interface. The statistical analysis effect diagram of the detection frequency is as Figure 6 shown. After updating the data in the input template and saving, the regional division of the detection frequency can be updated and displayed in real time.

[0104] 3) The detection result statistical analysis unit is used to statistically analyze the mathematical statistical parameter values of the detection results and extract the sample information corresponding to the unqualified indicators. The mathematical statistical parameter values include: the minimum value, the maximum value, and the average value; for the unqualified sample information, it includes: the production manufacturer and the ex-factory batch number information corresponding to the raw materials, and the detection location information corresponding to the intermediate products, on-site detections, and geophysical prospecting detections.

[0105] In the present invention, by using EXCEL's mathematical functions, such as the "Minifs", "Maxifs", and "Averageifs" functions, codes are written to perform multi-condition screening on the detection result data, calculate the minimum value, the maximum value, and the average value, determine the unqualified indicators, and then use the "Index + Match" function to extract the sample information corresponding to the unqualified indicators.

[0106] In the test result statistical analysis unit, the test result value is used to count the minimum, maximum and average values ​​of each test index on the one hand; on the other hand, it is used to indicate whether there are unqualified items. If there are unqualified items, the total number of unqualified items is automatically counted. The test result value x can be additionally defined. 3 , if there are unqualified items, then x 3 =0; in other cases x 3 ≠0.

[0107] In the implementation case provided by the present invention, the specific code of the detection result statistical analysis unit is as follows:

[0108] Detection group number statistics code = SUMIFS (three-party original data! $O: $O, three-party original data! $P: $P, three-party original data! $P$2, three-party original data! $G: $G, three-party original data! $G$2); O is the detection group number column; P is the detection unit column; G is the detection item column;

[0109] Unqualified group number statistics code = SUMIFS (three-party original data! $O: $O, three-party original data! $P: $P, three-party original data! $P$2, three-party original data! $G: $G, three-party original data! $G$2, three-party original data! $U: $U, "<300"); O is the test group number column; P is the test unit column; G is the test item column; U is the test result column; taking cement specific surface area as an example, the other indicator codes are similar;

[0110] Minimum value statistics code = MINIFS (three-party original data! $U: $U, three-party original data! $P: $P, three-party original data! $P$2, three-party original data! $G: $G, three-party original data! $G$2); P is the test unit column; G is the test item column; U is the test result column; taking cement specific surface area as an example, the other indicator codes are similar;

[0111] Maximum value statistics code = MAXIFS (three-party original data! $U: $U, three-party original data! $P: $P, three-party original data! $P$2, three-party original data! $G: $G, three-party original data! $G$2); P is the test unit column; G is the test item column; U is the test result column; taking cement specific surface area as an example, the other indicator codes are similar;

[0112] Average value statistics code = AVERAGEIFS (three-party original data! $U: $U, three-party original data! $P: $P, three-party original data! $P$2, three-party original data! $G: $G, three-party original data! $G$2); P is the test unit column; G is the test item column; U is the test result column; taking cement specific surface area as an example, the other indicator codes are similar;

[0113] Unqualified sample factory batch number extraction code = INDEX (J2: U10000, MATCH (J1, J1: U1, 0), MATCH (U2, U2: U10000, 0)); J2: U10000 is the unqualified information database; J1: U1 is to find the factory batch number column; U2: U10000 is to find the column where the value that does not meet the standard is located; Taking cement specific surface area as an example, the other indicator codes are similar;

[0114] The unqualified sample manufacturer extraction code = INDEX (K2: U10000, MATCH (K1, K1: U1, 0), MATCH (U2, U2: U10000, 0)); K2: U10000 is the unqualified information database; K1: U1 is the manufacturer search column; taking cement specific surface area as an example, the other indicator codes are similar.

[0115] When using, click the raw materials, intermediate products, on-site inspection or geophysical inspection button on the main interface to jump to the corresponding inspection results display interface. The inspection results statistical analysis effect diagram is as follows: Figure 7 As shown (taking cement as an example); after updating the data entered in the template and saving it, the test results and unqualified information can be updated and displayed in real time.

[0116] P120 data pre-judgment module: used to determine whether to conduct subsequent analysis and evaluation based on the relationship between the test volume, test frequency and test results;

[0117] The data pre-judgment module performs the data pre-judgment operation before the data evaluation and analysis. 1 、Detection frequency value x 2 And the test result value x 3 Analyze and judge the relationship:

[0118] When x 1 <x 2 And x 3 =0, no subsequent data evaluation and analysis will be performed, and this type of data evaluation will be directly judged as "V level", indicating that the data has no effect;

[0119] When x 1 ≥x 2 And x 3 =0 and x 3 When ≠0, the detection data evaluation unit of the P130 data evaluation module performs quality evaluation.

[0120] P130 data evaluation module: used to conduct quality evaluation of engineering project inspection targets according to inspection indicators and generate evaluation results. The evaluation dimensions of the quality evaluation include: completeness, accuracy and effectiveness; the evaluation dimension indicators include: inspection frequency, completeness of inspection results, accuracy of inspection results, sampling methods, and effectiveness of sampling locations.

[0121] 1) Integrity includes the integrity of the test frequency and test results, specifically, whether the test frequency is consistent with the specification requirements or whether there are deficiencies or surpluses. The integrity of the test results includes: whether the actual test indicators are complete according to the specification requirements;

[0122] 2) Accuracy refers to whether the test results are qualified;

[0123] 3) Effectiveness includes the effectiveness of the sampling method and the sampling location. Specifically, the sampling method meets the requirements of the specification, and the sampling location is random and includes all unit projects.

[0124] According to the importance of the three evaluation dimensions on data evaluation, in the data evaluation analysis, the integrity weight is given as m. 1 , accuracy weight m 2 , effectiveness weight m 3 , for example: completeness weight 30%, accuracy weight 50%, effectiveness weight 20%.

[0125] The data evaluation module includes a test data evaluation unit, which evaluates and analyzes the role of data by establishing an evaluation factor K and an evaluation level. The evaluation factor is defined as the sum of the product of the integrity, accuracy and effectiveness index assignments and weights, where integrity is represented by the product of the detection frequency and the detection index assignment. The specific calculation formula is as follows (the result is rounded to two decimal places):

[0126] K=m 1 ×a i ×b i +m 2 ×d i +m 3 ×f i (Formula 1)

[0127] Where: m 1 The weight of the integrity index is 0.30, m 2 is the accuracy index weight 0.50, m 3 The weight for the effectiveness index is 0.20;

[0128] a i Assign values ​​to the detection indicators: Indicates the integrity of the test indicators and the test indicators required by the specification, such as: i = 0, a 0=1.00 means that the detection indicators are complete; i = 3, n = 8, then a 3 =(1-3 / 8)=0.63, indicating that the detection index should detect 8 items, 5 items were actually detected, and 3 items were missed. The index is assigned a value of 0.63;

[0129] b i To assign the detection frequency index based on the results of the detection frequency statistical analysis unit, where zone I is assigned a value of b 0 =0.80, zone II is assigned value b 1 =1.00, zone III is assigned value b 2 =1.05, i=0, 1, 2;

[0130] d i Assign values ​​to the accuracy index: All detection indicators are assigned values ​​d 0 =1.00, otherwise the value is d 1 =0.00, and: i=0, 1;

[0131] f i Assign values ​​to the effectiveness index, i = 0, 1, 2, 3; the specific assignment definition includes the following:

[0132] ① The sampling method and sampling location meet the requirements, that is, f 0 =1.00;

[0133] ② The sampling method meets the requirements, but the sampling location is insufficient, i.e. f 1 =0.80;

[0134] ③ The sampling location meets the requirements, but the sampling method is insufficient, i.e. 2 =0.50;

[0135] ④The sampling method and sampling location do not meet the requirements, that is, f 3 =0.00.

[0136] According to the evaluation factors, the data are divided into five levels, namely "I, II, III, IV and V", among which the evaluation factor of level I K≥0.90, indicating that the data has a guiding role; the evaluation factor of level II 0.80≤K<0.90, indicating that the data has a reference role; the evaluation factor of level III 0.70<K<0.80, indicating that some data have a reference role; the evaluation factor of level IV K≤0.70 and the accuracy and detection index values ​​are not equal to 0.00, indicating that a small amount of data has a reference role; the evaluation factor of level V K≤0.70 and the accuracy value is equal to 0.00 or the detection index value is equal to 0.00, indicating that the data has no role, including two situations: unqualified detection and uninspected.

[0137] The present invention provides the following cases for the specific evaluation process of the data evaluation module:

[0138] Example 1: On April 2, 2022, a group of P·O42.5 cement produced by a cement production company was tested. The batch number was 2MR2240228. According to the test frequency and test results, it can be known that the batch of cement x 1 =x 2 And x 3 =0, the condition is not met, and the data evaluation and analysis unit is terminated. This type of data evaluation is directly judged as "V level", indicating that the data has no effect and re-sampling should be carried out for testing.

[0139] Example 2: On December 23, 2021, a group of concrete compressive strength specimens with strength grade C15 were tested. According to the test frequency and test results, the batch of concrete x 1 >x 2 And x 3 ≠0, the conditions are met and the test data evaluation and analysis unit is entered. The specific process is as follows:

[0140] This concrete belongs to zone III, then b 0 =1.05; the total inspection index of concrete compressive strength is n 应检 =2, actual detection index n 实检 =2, then a 0 =(1-0 / 2)=1.00; if the concrete compressive strength is qualified, the accuracy index d 0 =1.00; the concrete sampling method and sampling location meet the requirements, then f 0 =1.00; calculate the evaluation factor K,

[0141] K=m 1 ×a i ×b i +m 2 ×d i +m 3 ×f i =0.30×1.00×1.05+0.50×1.00+0.20×1.00=1.02

[0142] According to the evaluation grade standard, this batch of cement K=1.02≥0.90, which means that the evaluation grade of this batch of concrete belongs to Grade I. The data is of guiding significance and can be further analyzed to guide the testing personnel on whether it is necessary to carry out mix ratio optimization work.

[0143] Example 3: On January 11, 2022, a group of P·O42.5 bags of cement produced by a cement production company was tested. The factory batch number was D224002. According to the test frequency and test results, it can be known that the batch of concrete x 1 =x 2 And x3 ≠0, the conditions are met and the test data evaluation and analysis unit is entered. The specific process is as follows:

[0144] This batch of cement belongs to zone I, so b 0 =1.00; Total cement indicators to be tested n 应检 =7, actual detection index n 实检 =4, then a 3 =(1-3 / 7)=0.57; if the test results of this batch of cement are all qualified, then the accuracy index d 0 =1.00; if the sampling location of this batch of cement meets the requirements but the sampling method is insufficient, then f 2 =0.50; calculate the evaluation factor K, K = m 1 ×a i ×b i +m 2 ×d i +m 3 ×f i =0.30×0.57×1.00+0.50×1.00+0.20×0.50=0.77

[0145] According to the evaluation grade standard, this batch of cement K=0.77∈(0.70, 0.80), which means that this batch of cement belongs to Grade III. Some of its data are for reference only. The test indicators are incomplete but all the tested indicators are qualified. Only the content of qualified indicators can indicate that the relevant properties of the cement meet the requirements, such as qualified strength, qualified setting time, etc.

[0146] The calculation of evaluation factors, the division of evaluation levels and the display of evaluation results also use IF nested functions to write programs and automatically perform related operations.

[0147] The specific codes for evaluation level classification are as follows:

[0148] =IF(L5>=0.9,"I",IF(AND(L5<0.9,L5>=0.8),"II",IF(AND(L5<0.8,L5>0.7),"III",IF(AND(L5<=0.7,J5=0),"V","IV")))); L5 is the evaluation factor.

[0149] When using, click the data evaluation button on the main interface to jump to the corresponding data evaluation display interface. Combined with the detection process and the detection results, enter the corresponding evaluation index values ​​in the table. The system automatically calculates the evaluation factors, and automatically divides them into grades according to the evaluation factor values, and displays the evaluation results. The data evaluation analysis effect diagram is as follows: Figure 8 shown.

[0150] P140 data output module: used to display detection indicators and evaluation results.

[0151] The data output module includes: a detection quantity display unit, a detection frequency display unit, a detection result display unit and a data evaluation unit;

[0152] 1) The detection quantity display unit is used to display the results and classification of the detection quantity data under different parameter selections through a slicer and a data perspective chart; wherein the slicer and the data perspective chart are linked to switch the corresponding analysis results in real time;

[0153] The detection quantity display unit makes full use of the functions of EXCEL, and uses histograms, bar graphs and pie charts in the pivot diagram to display the results and classification of the detection quantity data under different parameter selections; the slicer and the pivot diagram are linked, and by clicking the options on the slicer, the corresponding analysis results can be displayed in real time; the structure layout can be set in the detection quantity display unit, for example: using a left-right structure layout, with the established slicer on the left, and histograms, bar graphs and pie charts on the right; the slicer style and properties can be edited to make its layout reasonable and beautiful; if the data entered in the template is updated, the slicer needs to be refreshed to ensure the accuracy of the displayed results. The display layout and functions provided by the detection quantity display unit are as follows: Figure 5 shown.

[0154] 2) The detection frequency display unit is used to intuitively display and warn the detection frequency attributes of different detection units, and the detection frequency attributes include area and mark color;

[0155] The table displayed in the test frequency display unit contains information such as sample category, test item, specification model / reference standard, manufacturer, etc. The test frequency attribute can be used to understand the test frequency of different test units at a glance.

[0156] For the update and storage of data in the input template, the detection frequency area division can be updated and displayed in real time. The data display diagram of the detection frequency display unit is as follows: Figure 6 shown, including color-coded areas of interest.

[0157] 3) The test result display unit adopts a table format to display the test results and unqualified information;

[0158] The test result display unit supports display structure layout, for example, it supports upper and lower structure layout, the upper part displays the statistical value of the test result mathematical statistics parameter, and the lower part displays the unqualified information. For the data update in the update input template, the test results and unqualified information can be updated and displayed in real time. The data display diagram of the test result display unit is as follows Figure 7 shown.

[0159] 4) The data evaluation unit is used to form the evaluation dimension index assignment, evaluation factor, and evaluation level of the engineering project detection target.

[0160] The data evaluation unit displays the evaluation results in a table format, which includes information such as sample type, test date, test items, specifications, batch number, manufacturer / sampling location, integrity index assignment (test frequency, test index), accuracy index assignment, effectiveness index assignment, etc. The evaluation factors, evaluation levels and remarks are automatically calculated and generated by the system. Figure 8 shown.

[0161] The system provided by the present invention is saved in the form of an EXCEL document. Open the corresponding EXCEL document, and the main interface will appear. According to the needs, click the corresponding button of the main interface to jump to the corresponding analysis display interface to obtain the relevant data statistical analysis and evaluation analysis results; click the EXCEL name box and select the set "interface" in the drop-down menu to jump to the main interface, which is convenient for continuing the next step.

[0162] Through the engineering project detection data statistical analysis management system based on EXCEL provided by the present invention, the statistical analysis results of indicators including detection amount, detection frequency and detection results can be obtained quickly, accurately, comprehensively and systematically from a variety of sources, so as to achieve the following functions: 1) the detection amount statistics and chart display corresponding to the selection of different parameters (time, detection unit, detection item) partially or completely; 2) color warning for projects that do not meet the requirements of the standard, and color reminder for projects whose detection frequency is higher than the upper limit, so as to guide the detection personnel to dynamically adjust the detection frequency; 3) automatic calculation of mathematical statistics parameters (maximum value, average value, standard deviation, etc.) of each detection item, automatic analysis and comparison of each detection item value to meet the standard value, if not, then automatically extract the relevant information of the unqualified product; 4) standardization of the detection results from different detection sources, integration of multi-party data, to ensure the universality of the detection results of each link. In addition, different parameters are defined, and through evaluation and analysis, the detection personnel are guided to analyze the stability of the quality of raw materials, whether the mix ratio of intermediate products such as concrete needs to be optimized, etc., to further improve the utilization of data.

[0163] The above disclosures are only several specific embodiments of the present invention; however, the present invention is not limited thereto, and any changes that can be conceived by those skilled in the art should fall within the protection scope of the present invention.

Claims

1. An engineering project inspection data statistical analysis management system based on EXCEL, characterized in that: The engineering project inspection data statistical analysis management system is run based on EXCEL and includes: Data input module: used to obtain basic information and test results of engineering project test targets; the test sources of the engineering project test targets include: self-inspection by the construction unit, random inspection by the supervision center, test and random inspection by the geophysical exploration center; the sample categories of the test targets include: raw materials, intermediate products, on-site inspection and geophysical exploration inspection; Data analysis module: used to calculate the engineering project detection indicators based on basic information and detection results; the engineering project detection indicators include: detection volume, detection frequency and detection results; Data pre-judgment module: used to determine whether to conduct subsequent analysis and evaluation based on the relationship between the detection amount, detection frequency and detection result; if the detection amount value is less than the detection frequency value and the detection result value = 0, no subsequent data evaluation and analysis will be conducted; Data evaluation module: used to evaluate the quality of the project inspection target according to the inspection indicators and generate evaluation results. The evaluation dimensions of the quality evaluation include: completeness, accuracy and effectiveness; the evaluation dimension indicators include: inspection frequency, completeness of inspection results, accuracy of inspection results, sampling method, and effectiveness of sampling location; Data output module: used to display detection indicators and evaluation results.

2. The engineering project detection data statistical analysis management system according to claim 1, characterized in that: The data input module includes: a basic information unit and a test result unit; The basic information unit is used to obtain basic information of engineering project detection and standardize the basic information; The detection result unit obtains the detection result corresponding to the basic information, and performs data standardization on the detection result; the data standardization includes: the detection result corresponds to the basic information, and the detection result unit is unified.

3. The engineering project detection data statistical analysis management system according to claim 1, characterized in that: The data analysis module includes: a detection quantity statistical analysis unit, a detection frequency statistical analysis unit and a detection result statistical analysis unit; The detected quantity statistical analysis unit is used to establish the relationship between different parameters and detected quantities; The detection frequency statistical analysis unit is used to classify the basic information and detection results according to raw materials, intermediate products, on-site detection and geophysical detection, calculate the number of detection groups and the number of groups to be inspected, determine the detection frequency, and divide the focus area according to the detection frequency; The test result statistical analysis unit is used to count the mathematical statistical parameter values ​​of the test results and extract sample information corresponding to the unqualified indicators. The mathematical statistical parameter values ​​include: minimum value, maximum value and average value; the unqualified indicator sample information includes: manufacturer and factory batch number information corresponding to the raw materials, and detection site information corresponding to intermediate products, on-site detection and geophysical detection.

4. The engineering project detection data statistical analysis management system according to claim 3 is characterized in that: The data output module includes: a detection quantity display unit, a detection frequency display unit, a detection result display unit and a data evaluation unit; The detection quantity display unit is used to display the results and classification of the detection quantity data under different parameter selections through a slicer and a data perspective chart; wherein the slicer and the data perspective chart are linked to switch the corresponding analysis results in real time; The detection frequency display unit is used to intuitively display and warn the detection frequency attributes of different detection units, and the detection frequency attributes include area and mark color; The test result display unit uses a table format to display the test results and unqualified information; The data evaluation unit is used to form the evaluation dimension indicator assignment, evaluation factor, and evaluation level of the engineering project detection target.

5. The engineering project detection data statistical analysis management system according to claim 3, characterized in that: The types of the areas of concern include Zone I, Zone II and Zone III; Zone I indicates that the detection frequency is lower than the value specified in the regulations and specifications, Zone II indicates that the detection frequency is between the lower limit and the upper limit, and Zone III indicates that the detection frequency is higher than 2 times the specified value; The types of areas of concern are distinguished by color markings to indicate different levels of concern, including: Area I is marked in red, indicating that the detection frequency does not meet the requirements of the standard and a warning is given; Area II is not marked, indicating that the detection frequency meets the requirements; Area III is marked in orange, indicating that the detection frequency is too high and the detection frequency can be appropriately reduced.

6. The engineering project detection data statistical analysis management system according to claim 4, characterized in that: The integrity includes the integrity of the test frequency and the test results, specifically whether the test frequency is consistent with the specification requirements or whether there are deficiencies or surpluses. The integrity of the test results includes: judging whether the actual test indicators are complete according to the specification requirements; The accuracy mentioned refers to whether the test results are qualified; The effectiveness includes the effectiveness of the sampling method and the sampling location, specifically, the sampling method meets the requirements of the specification, and the sampling location is random and includes all unit projects.

7. The engineering project detection data statistical analysis management system according to claim 1, characterized in that: The basic information of the engineering project inspection target includes: sample sampling date, inspection date, sample type, inspection item, specification model, factory batch number, manufacturer, number of representatives, number of sampling groups, inspection unit, inspected unit, and inspection location; Among them, the sample categories include: raw materials, intermediate products, on-site testing and geophysical testing; the testing units include the testing and geophysical center, the supervision center and the construction units; the inspected units are construction units of different sections.

8. The engineering project detection data statistical analysis management system according to claim 7, characterized in that: The number of groups to be tested is determined based on the multiple of the number of representatives and the upper limit value specified in the standard; When the number of representatives is ≤ the upper limit specified in the standard, the number of groups to be inspected is 1; When the upper limit value specified in the standard is less than the representative quantity ≤ 2 times the upper limit value specified in the standard, the number of groups to be tested is 2; When 2 times the upper limit value specified in the standard < the representative quantity ≤ 3 times the upper limit value specified in the standard, the number of groups to be tested is 3; And so on.

9. The engineering project detection data statistical analysis management system according to claim 8, characterized in that: The detection value is the number of detection groups actually sampled; The detection frequency is an indicator for judging the relationship between the detection value and the number of groups to be tested. If the detection value is greater than the number of groups to be tested, the detection frequency meets the requirement; the detection frequency value is the number of groups to be tested that is automatically calculated; The test result values ​​are used to count the minimum, maximum and average values ​​of various test indicators on the one hand; and to indicate whether there are unqualified items on the other hand. If there are unqualified items, the total number of unqualified items is automatically counted.

10. The engineering project detection data statistical analysis management system according to claim 1, characterized in that: The data evaluation module includes a detection data evaluation unit, which establishes an evaluation factor K and an evaluation level to evaluate and analyze the effect of the data; The evaluation factor is defined as the sum of the product of the completeness, accuracy and effectiveness index values ​​and weights, expressed as: K = m1 × a i ×b i +m2×d i +m3×f i , Among them: m1 is the completeness index weight, m2 is the accuracy index weight, and m3 is the effectiveness index weight; a i Assign values ​​to the detection index, b i Assign a value to the detection frequency index, d i Assign a value to the accuracy index, f i Assign values ​​to effectiveness indicators.

Images