Data entry consistency verification method and device based on spreadsheet and storage medium

Abstract

The invention discloses a data entry consistency verification method and device based on a spreadsheet and a storage medium, and the method comprises the steps: (1) data importing: importing verification data in batches from a laboratory information system and an archive management system; (2) data verification: carrying out data comparison and summarizing problem data by utilizing an Excel formula, VBA programming or a comparison function; and (3) result display and report generation: displaying the data verification result in a visual mode, and generating a data verification report according to the summarized problem data. The method has the three advantages that the comparison efficiency is improved, manual operation is replaced by automatic comparison, the comparison time is greatly shortened, and the working efficiency is improved. And through preset comparison rules and automatic processing, human errors are reduced, and the data accuracy is improved. The data traceability is enhanced, the detailed comparison report and early warning function is beneficial for the user to track the problem source, and the data traceability is enhanced.

Description

Technical Field

[0001] The present invention relates to the technical field of data verification, and in particular to a method, device and storage medium for verifying consistency of data entry based on an electronic form. Background Art

[0002] Health checkup for key populations in basic public health services is an important part of the health services provided by the state. Every year, community health service centers need to provide free health checkup services for key populations within their jurisdiction and enter the test results into the corresponding resident health record system. Entering test data has become an important part of health checkup.

[0003] In the prior art, there are two methods for entering test data. One is to manually enter paper test results into the archive system one by one; the other is to use the resident ID card or physical examination number as a unique identifier for data matching, and import the test result data provided by the laboratory into the archive system in the format of an Excel table to achieve batch entry. However, whether it is manual entry or batch entry, it may be due to operational errors, such as wrong entry, missing entry, information matching errors, date matching errors, etc., resulting in the inconsistency between the test data entered into the archive system and the data provided by the actual laboratory. At the same time, since the resident health archive system is operated by multiple people for viewing, modification and use, it may be due to manual operation errors that the test data entered into the resident health archive system is inconsistent with the actual test data results. At present, there is no tool specifically developed for consistency verification of this problem. It mainly relies on manual visual verification to compare the paper test results with the system entry data one-to-one. Therefore, we provide Excel-based spreadsheet tools and verification methods to achieve consistency quality control verification of laboratory test data and test data entered into the archive system to improve data processing efficiency and accuracy. Summary of the invention

[0004] The present invention provides a data entry consistency verification method, device and storage medium based on electronic spreadsheets, which realizes consistency quality control verification of laboratory test data and test data entered into an archive system, so as to improve data processing efficiency and accuracy.

[0005] In a first aspect, the present invention provides a data entry consistency verification method based on an electronic form, comprising the steps of:

[0006] (1) Data import: batch import of test data from the laboratory information system and archive management system;

[0007] (2) Data verification: use Excel formulas, VBA programming, or comparison functions to compare data and summarize problematic data;

[0008] (3) Result presentation and report generation: The data verification results are presented in a visual way, and a data verification report is generated based on the summarized problem data.

[0009] Preferably, step (1) also includes data format analysis, identifying the imported data in different data formats and automatically setting the inspection data to a digital format.

[0010] Preferably, in step (1), the test data is extracted by a macro and the names of the test data in the matching column are generated according to the names of the customized laboratory test data and the test data in the physical examination form;

[0011] The specific procedures are:

[0012] Sub extracts test data and modifies the test data name()

[0013] DimiAsInteger,jAsInteger,kAsInteger,shtw1AsWorksheet,shtw2AsWorksheet,Edrow1AsLong,Edrow2AsLong

[0014] Edrow1 = Worksheets("Verification data name modification").Range("A1048576").End(xlUp).Row

[0015] Edrow2 = Worksheets("Verify data consistency").Range("A1048576").End(xlUp).Row

[0016] Worksheets("Inspection data consistency check").Rows("2:"&Edrow2).Delete

[0017] Worksheets("Laboratory Data").Columns("a:g").Copy Worksheets("Test Data Consistency Check").Columns("a:g")

[0018] Worksheets("Laboratory Data").Columns("k:k").Copy Worksheets("Test Data Consistency Check").Columns("h:h")

[0019] Worksheets("Laboratory Data").Columns("z:ab").Copy Worksheets("Test Data Consistency Check").Columns("i:k")

[0020] Worksheets("Laboratory Data").Columns("ay:ay").Copy Worksheets("Test Data Consistency Check").Columns("l:l")

[0021] For i = 2 To Edrow2' consistency

[0022] IfWorksheets("Verify data consistency").cells(i,13)=""Then

[0023] Forj = 2To Edrow1

[0024] If Worksheets("Verify data consistency").cells(i,12)=Worksheets("Change test data name").cells(j,2)Then

[0025] Worksheets("Verify consistency of inspection data").cells(i,13) = Worksheets("Change inspection data name").cells(j,3)

[0026] End If

[0027] Nextj

[0028] Else

[0029] End If

[0030] Next i

[0031] For i=2To Edrow2

[0032] IfWorksheets("Verify data consistency").cells(i,13)=""Then

[0033] Worksheets("Inspection data consistency check").cells(i,13) = Worksheets("Inspection data consistency check").cells(i,12)

[0034] End If

[0035] Next i

[0036] MsgBox "Verification data extraction completed"

[0037] End Sub.

[0038] Preferably, step (2) also includes customized comparison rules, allowing the user to customize the names of laboratory test data and test data in the basic health system according to actual needs, so as to facilitate the matching and comparison of test data; the comparison rules are implemented through Excel formulas, VBA programming or comparison functions to achieve verification and comparison.

[0039] Preferably, in step (2), the inspection and comparison are realized by a comparison function, and the comparison function is: =IF(XLOOKUP($G6, file system physical examination form data!$C:$C, file system physical examination form data!$C:$C,1,0)=1,"No physical examination form found",IF(AND($M6=O$1,($B6-XLOOKUP($G6, file system physical examination form data!$C:$C, file system physical examination form data!$A:$A,"",0))<3,($B6-XLOOKUP($G6, file system physical examination form data!$C:$C, file system physical examination form data!$A:$A,"",0))>-3,NOT(COUNTIF(XLOOKUP($G6, file system physical examination form data!$C:$C, file system physical examination form data!ER:ER,1,0),$I6))),$M6&"The results are inconsistent","")).

[0040] Preferably, in step (2), the summary problem function is: =IF(O6="No physical examination form found", O6, TEXTJOIN("", TRUE, O6:AF6)).

[0041] Preferably, in step (3), the visualization method includes: Excel charts, conditional formatting, and marking difference items and outliers with different colors.

[0042] In a second aspect, the present invention provides a system for implementing a data entry consistency verification method based on an electronic form, comprising:

[0043] Data import module, used to import the test data exported from the laboratory information system and the original test data exported from the archive management system into the tool table;

[0044] The verification and comparison module is used to automatically compare the laboratory test data with the data in the archive system according to the preset rules, and display the verification and comparison results, identifying the differences and abnormal values;

[0045] The early warning module is used to provide early warning prompts for serious differences or abnormal data, helping users to discover and handle problems in a timely manner;

[0046] The export module is used to export the output results of the verification and comparison module.

[0047] Preferably, it also includes:

[0048] Rule definition module, used to customize comparison rules;

[0049] An encryption module is used to encrypt data access;

[0050] The test verification module is used to test and verify the tool.

[0051] In a third aspect, the present invention provides a data entry consistency verification device based on a spreadsheet, comprising: a memory and a processor, wherein the memory stores a computer program, and when the computer program is executed by the processor, the steps of the above-mentioned data entry consistency verification method based on a spreadsheet are implemented.

[0052] In a fourth aspect, the present invention provides a computer storage medium storing computer instructions thereon, which, when executed on a terminal, enables the terminal to execute the electronic spreadsheet-based data entry consistency verification method as described above.

[0053] Compared with the prior art, the beneficial effects of the present invention are: the electronic spreadsheet-based data entry consistency verification method, device and storage medium have the following advantages:

[0054] The present invention designs an Excel-based electronic spreadsheet tool to realize an automated comparison tool between laboratory test data and physical examination form test data within the basic health system, which will greatly improve data processing efficiency and accuracy and provide strong support for laboratories and archives management departments.

[0055] The present invention brings three major advantages: improving comparison efficiency, replacing manual operation with automated comparison, greatly shortening comparison time and improving work efficiency. Improving data accuracy, reducing human errors and improving data accuracy through preset comparison rules and automated processing. Enhancing data traceability, detailed comparison reports and early warning functions help users track the source of problems and enhance data traceability. BRIEF DESCRIPTION OF THE DRAWINGS

[0056] Figure 1 The present invention provides a flow chart of a data entry consistency verification method based on an electronic form. DETAILED DESCRIPTION

[0057] The specific embodiments of the present invention are further described below in conjunction with the accompanying drawings. It should be noted that the description of these embodiments is used to help understand the present invention, but does not constitute a limitation of the present invention. In addition, the present disclosure describes multiple embodiments, but the description is exemplary rather than restrictive, and it is obvious to those of ordinary skill in the art that there may be more embodiments and implementations within the scope of the embodiments described in the present disclosure. Although many possible feature combinations are shown in the drawings and discussed in the embodiments, many other combinations of the disclosed features are also possible. Unless specifically limited, any feature or element of any embodiment may be used in combination with any other feature or element in any other embodiment, or may replace any other feature or element in any other embodiment.

[0058] The present disclosure includes and contemplates combinations of features and elements known to those of ordinary skill in the art. The embodiments, features, and elements disclosed in the present disclosure may also be combined with any conventional features or elements to form a unique invention scheme defined by the claims. Any features or elements of any embodiment may also be combined with features or elements from other invention schemes to form another unique invention scheme defined by the claims. Therefore, it should be understood that any feature shown and / or discussed in the present disclosure may be implemented individually or in any appropriate combination. Therefore, except for the limitations made according to the attached claims and their equivalents, the embodiments are not subject to other limitations. In addition, various modifications and changes may be made within the scope of protection of the attached claims.

[0059] In addition, when describing representative embodiments, the specification may have presented the method and / or process as a specific sequence of steps. However, to the extent that the method or process does not rely on the specific order of the steps described herein, the method or process should not be limited to the steps of the specific order described. As will be understood by those of ordinary skill in the art, other sequences of steps are also possible. Therefore, the specific sequence of the steps set forth in the specification should not be interpreted as a limitation to the claims. In addition, the claims for the method and / or process should not be limited to the steps of performing them in the order written, and those skilled in the art can easily understand that these sequences can be changed and still remain within the spirit and scope of the embodiments of the present application.

[0060] In a first aspect, an embodiment disclosed in the present invention provides a data entry consistency verification method based on an electronic form, comprising the steps of:

[0061] (1) Data import: batch import of test data from the laboratory information system and archive management system, usually implemented by using Excel's VBA programming import function; specifically: extract test data through macros and generate matching column test data names according to the names of customized laboratory test data and physical examination form test data; the specific procedure is:

[0062] Sub extracts test data and modifies the test data name()

[0063] DimiAsInteger,jAsInteger,kAsInteger,shtw1AsWorksheet,shtw2AsWorksheet,Edrow1AsLong,Edrow2AsLong

[0064] Edrow1 = Worksheets("Verification data name modification").Range("A1048576").End(xlUp).Row

[0065] Edrow2 = Worksheets("Verify data consistency").Range("A1048576").End(xlUp).Row

[0066] Worksheets("Inspection data consistency check").Rows("2:"&Edrow2).Delete

[0067] Worksheets("Laboratory Data").Columns("a:g").CopyWorksheets("Test Data Consistency Verification").Columns("a:g")

[0068] Worksheets("Laboratory Data").Columns("k:k").Copy Worksheets("Test Data Consistency Check").Columns("h:h")

[0069] Worksheets("Laboratory Data").Columns("z:ab").Copy Worksheets("Test Data Consistency Check").Columns("i:k")

[0070] Worksheets("Laboratory Data").Columns("ay:ay").Copy Worksheets("Test Data Consistency Check").Columns("l:l")

[0071] For i = 2 To Edrow2' consistency

[0072] IfWorksheets("Verify data consistency").cells(i,13)=""Then

[0073] Forj = 2To Edrow1

[0074] If Worksheets("Verify data consistency").cells(i,12)=Worksheets("Change test data name").cells(j,2)Then

[0075] Worksheets("Verify consistency of inspection data").cells(i,13) = Worksheets("Change inspection data name").cells(j,3)

[0076] End If

[0077] Nextj

[0078] Else

[0079] End If

[0080] Next i

[0081] For i=2To Edrow2

[0082] IfWorksheets("Verify data consistency").cells(i,13)=""Then

[0083] Worksheets("Inspection data consistency check").cells(i,13) = Worksheets("Inspection data consistency check").cells(i,12)

[0084] End If

[0085] Nexti

[0086] MsgBox "Verification data extraction completed"

[0087] EndSub;

[0088] It also includes data format parsing, which identifies the imported data in different data formats and automatically sets the test data to digital format. Since the data formats of different systems may be different, the tool needs to be able to identify and automatically set the test data to digital format to facilitate the next step of data consistency verification.

[0089] (2) Data verification: use Excel formulas, VBA programming, or comparison functions to compare data and summarize problematic data;

[0090] In one embodiment, the inspection and comparison is realized by a comparison function, and the comparison function is: =IF(XLOOKUP($G6, file system physical examination form data! $C:$C, file system physical examination form data! $C:$C, 1,0)=1,"No physical examination form found",IF(AND($M6=O$1,($B6-XLOOKUP($G6, file system physical examination form data! $C:$C, file system physical examination form data! $A:$A,"",0))<3,($B6-XLOOKUP($G6, file system physical examination form data! $C:$C, file system physical examination form data! $A:$A,"",0))>-3,NOT(COUNTIF(XLOOKUP($G6, file system physical examination form data! $C:$C, file system physical examination form data! ER:ER,1,0),$I6))),$M6&"The results are inconsistent",""));

[0091] The summary problem function is: =IF(O6="No physical examination form found",O6,TEXTJOIN("",TRUE,O6:AF6)).

[0092] In order to improve the accuracy of verification, it also includes custom comparison rules, allowing users to customize the names of laboratory test data and test data in the basic health system according to actual needs, to facilitate the matching and comparison of test data; the comparison rules are implemented through Excel formulas, VBA programming or comparison functions to achieve verification and comparison.

[0093] (3) Result presentation and report generation: The data verification results are presented in a visual way, and a data verification report is generated based on the summarized problem data. Visual presentation methods include: Excel charts, conditional formatting, and using different colors to mark difference items and outliers.

[0094] In a second aspect, an embodiment disclosed in the present invention provides a system for implementing a data entry consistency verification method based on an electronic form, comprising:

[0095] The data import module is used to import the test data exported from the laboratory information system and the original test data exported from the archive management system into the tool table to ensure the integrity and accuracy of the data;

[0096] The verification and comparison module is used to automatically compare the laboratory test data with the data in the archive system according to the preset rules, and display the verification and comparison results, identifying the differences and abnormal values; use VBA to write automation scripts to realize automatic data import, comparison and result output;

[0097] The early warning module is used to provide early warning prompts for serious differences or abnormal data, helping users to discover and handle problems in a timely manner;

[0098] The export module is used to export the output results of the verification and comparison module.

[0099] In one embodiment, it also includes:

[0100] The rule definition module is used to customize the comparison rules. Users can customize the corresponding names of laboratory test data and test data exported from the archive system according to actual needs to facilitate data comparison;

[0101] The encryption module is used to encrypt data access and use the document encryption function of the Excel spreadsheet to encrypt data access to ensure the security of data transmission and storage;

[0102] The test and verification module is used to test and verify the tool to ensure its accuracy and stability under different data sets and rules.

[0103] In a third aspect, an embodiment disclosed in the present invention provides a data entry consistency verification device based on a spreadsheet, comprising: a memory and a processor, wherein the memory stores a computer program, and when the computer program is executed by the processor, the steps of the above-mentioned data entry consistency verification method based on a spreadsheet are implemented.

[0104] In a fourth aspect, an embodiment disclosed in the present invention provides a computer storage medium on which computer instructions are stored. When the computer instructions are executed on a terminal, the terminal executes the data entry consistency verification method based on a spreadsheet as described above.

[0105] It will be appreciated by those skilled in the art that all or some of the steps, systems, and functional modules / units in the methods disclosed above may be implemented as software, firmware, hardware, and appropriate combinations thereof. In hardware implementations, the division between the functional modules / units mentioned in the above description does not necessarily correspond to the division of physical components; for example, a physical component may have multiple functions, or a function or step may be performed by several physical components in cooperation. Some or all components may be implemented as software executed by a processor, such as a digital signal processor or a microprocessor, or implemented as hardware, or implemented as an integrated circuit, such as an application-specific integrated circuit. Such software may be distributed on a computer-readable medium, which may include a computer storage medium (or non-transitory medium) and a communication medium (or temporary medium). As known to those skilled in the art, the term computer storage medium includes volatile and non-volatile, removable and non-removable media implemented in any method or technology for storing information (such as computer-readable instructions, data structures, program modules, or other data). Computer storage media include, but are not limited to, RAM, ROM, EEPROM, flash memory or other memory technology, CD-ROM, digital versatile disks (DVD) or other optical disk storage, magnetic cassettes, magnetic tapes, magnetic disk storage or other magnetic storage devices, or any other medium that can be used to store the desired information and can be accessed by a computer. In addition, it is well known to those of ordinary skill in the art that communication media typically contain computer-readable instructions, data structures, program modules, or other data in a modulated data signal such as a carrier wave or other transport mechanism, and may include any information delivery media.

[0106] It should be noted that the present disclosure may have many other embodiments. Without departing from the spirit and essence of the present disclosure, technicians familiar with the field may make various corresponding changes and modifications based on the present disclosure, but these corresponding changes and modifications should all fall within the scope of protection of the claims attached to the present disclosure.

Claims

1. A data entry consistency verification method based on electronic forms, characterized in that: Includes steps: (1) Data import: batch import of test data from the laboratory information system and archive management system; (2) Data verification: use Excel formulas, VBA programming, or comparison functions to compare data and summarize problematic data; (3) Result presentation and report generation: The data verification results are presented in a visual way, and a data verification report is generated based on the summarized problem data.

2. The electronic spreadsheet-based data entry consistency verification method according to claim 1, characterized in that: Step (1) also includes data format analysis, identifying the imported data in different data formats and automatically setting the inspection data to a digital format.

3. The electronic spreadsheet-based data entry consistency verification method according to claim 1, characterized in that: In step (1), the test data is extracted through a macro and the names of the test data in the matching column are generated according to the names of the customized laboratory test data and the physical examination form test data; the specific procedure is: Sub extracts test data and modifies the test data name() Dim i As Integer,j As Integer,k As Integer,shtw1 As Worksheet,shtw2 AsWorksheet,Edrow1 As Long,Edrow2As Long Edrow1 = Worksheets("Verification data name modification").Range("A1048576").End(xlUp).Row Edrow2 = Worksheets("Verify data consistency").Range("A1048576").End(xlUp).Row Worksheets("Verify data consistency").Rows("2:"&Edrow2).Delete Worksheets("Laboratory Data").Columns("a:g").Copy Worksheets("Test Data Consistency Check").Columns("a:g") Worksheets("Laboratory Data").Columns("k:k").Copy Worksheets("Test Data Consistency Check").Columns("h:h") Worksheets("Laboratory Data").Columns("z:ab").Copy Worksheets("Test Data Consistency Check").Columns("i:k") Worksheets("Laboratory Data").Columns("ay:ay").Copy Worksheets("Test Data Consistency Check").Columns("l:l") For i = 2 To Edrow2' consistency IfWorksheets("Verify data consistency").cells(i,13)=""Then Forj = 2To Edrow1 If Worksheets("Verify data consistency").cells(i,12)=Worksheets("Change test data name").cells(j,2)Then Worksheets("Verify consistency of inspection data").cells(i,13) = Worksheets("Change inspection data name").cells(j,3) End If Nextj Else End If Next i For i=2To Edrow2 IfWorksheets("Verify data consistency").cells(i,13)=""Then Worksheets("Inspection data consistency check").cells(i,13) = Worksheets("Inspection data consistency check").cells(i,12) End If Next i MsgBox "Verification data extraction completed" End Sub.

4. The electronic spreadsheet-based data entry consistency verification method according to claim 1, characterized in that: Step (2) also includes customized comparison rules, allowing users to customize the names of laboratory test data and test data in the basic health system according to actual needs, so as to facilitate the matching and comparison of test data; the comparison rules are implemented through Excel formulas, VBA programming or comparison functions to achieve verification and comparison.

5. The electronic spreadsheet-based data entry consistency verification method according to claim 1, characterized in that: In step (2), the comparison function is used to implement the inspection and comparison. The comparison function is: =IF(XLOOKUP($G6, file system physical examination form data! $C:$C, file system physical examination form data! $C:$C, 1,0)=1,"No physical examination form found",IF(AND($M6=O$1,($B6-XLOOKUP($G6, file system physical examination form data! $C:$C, file system physical examination form data! $A:$A,"",0))<3,($B6-XLOOKUP($G6, file system physical examination form data! $C:$C, file system physical examination form data! $A:$A,"",0))>-3,NOT(COUNTIF(XLOOKUP($G6, file system physical examination form data! $C:$C, file system physical examination form data! ER:ER, 1,0),$I6))),$M6&"The results are inconsistent","")).

6. The electronic spreadsheet-based data entry consistency verification method according to claim 5, characterized in that: In step (2), the summary problem function is: =IF(O6="No physical examination form found",O6,TEXTJOIN("",TRUE,O6:AF6)).

7. A system for implementing the electronic spreadsheet-based data entry consistency verification method according to any one of claims 1 to 5, characterized in that: include: Data import module, used to import the test data exported from the laboratory information system and the original test data exported from the archive management system into the tool table; The verification and comparison module is used to automatically compare the laboratory test data with the data in the archive system according to the preset rules, and display the verification and comparison results, identifying the differences and abnormal values; The early warning module is used to provide early warning prompts for serious differences or abnormal data, helping users to discover and handle problems in a timely manner; The export module is used to export the output results of the verification and comparison module.

8. The system for implementing the electronic spreadsheet-based data entry consistency verification method according to claim 7, characterized in that: Also includes: Rule definition module, used to customize comparison rules; An encryption module is used to encrypt data access; The test verification module is used to test and verify the tool.

9. A data entry consistency verification device based on electronic forms, characterized in that: include: A memory and a processor, wherein the memory stores a computer program, and when the computer program is executed by the processor, the steps of the data entry consistency verification method based on an electronic form according to any one of claims 1 to 5 are implemented.

10. A computer storage medium, characterized in that: Computer instructions are stored thereon, and when the computer instructions are executed on the terminal, the terminal executes the electronic form-based data entry consistency verification method as described in any one of claims 1-5.

Images