Method for accurately and quickly counting immune data in large area

Abstract

The invention discloses a large-area, accurate and rapid immune data statistics method, and aims to solve the problems of large table number, large data volume, low efficiency and high error rate in the existing livestock and poultry immune data manual summarization, and the method realizes automatic summarization by constructing a structured summarization template. The method comprises the following steps: creating a structured template, and arranging lower-level unit spreadsheets in the same format according to a fixed sequence and line spacing; adding a same-structure summary table at the tail end of the template; establishing a cross-unit data reference formula in a target cell of the summary sheet, and covering all cells to be summarized through a batch copy formula; and after receiving the subordinate data and pasting the subordinate data to the corresponding position of the template, automatically updating an aggregation result by the summary table. For a multi-worksheet scene, an independent template is established for each worksheet to form a parallel summarizing channel. Manual repeated calculation is not needed, the accuracy and timeliness of data summarization are greatly improved, the efficiency is improved by dozens of times or more, and the method is suitable for the field of hierarchical data statistics of agriculture, animal husbandry and the like.

Description

Technical Field

[0001] This invention relates to the field of data statistics and processing technology, specifically a method for large-scale, accurate, and rapid statistical analysis of immunization data. It is particularly suitable for the statistical summary of livestock and poultry immunization data and can be widely applied in fields such as agriculture and animal husbandry that require hierarchical data collection and reporting. It can achieve rapid and accurate automated summarization of large amounts of spreadsheet data reported by lower-level units, providing efficient data support for industry decision-making and management. Background Technology

[0002] In livestock and poultry disease prevention and control, the statistical compilation of immunization data is crucial. In accordance with the unified prevention and control deployment of major livestock and poultry diseases by the Ministry of Agriculture and Rural Affairs, livestock and veterinary authorities at all levels are required to regularly collect, compile, and report immunization data level by level. For example... Figures 2-6 As shown, taking a certain county as an example, 20 townships need to submit 120 forms with more than 2,000 data points each month, totaling 1,440 forms per year; during the spring and autumn centralized immunization periods, 120 forms are submitted each week, totaling 2,400 forms per year.

[0003] County-level and above departments are required to compile these data forms and report them level by level. However, the actual compilation process is extremely difficult. Due to the sheer volume of forms and data, staff are prone to headaches and other mental strain from prolonged repetitive calculations, making it difficult to complete the compilation in a short period. This results in the inability to utilize important data in a timely and effective manner, affecting industry assessment and decision-making. Furthermore, the "large-scale, small-group" model of agricultural production leads to significant fluctuations in market quantities, necessitating rapid and accurate statistical data to guide the market; existing manual compilation methods are no longer sufficient to meet this demand. Summary of the Invention

[0004] The present invention aims to solve the above-mentioned technical problems by providing a method for large-area, accurate, and rapid statistical analysis of immune data.

[0005] To solve the above-mentioned technical problems, the technical solution provided by the present invention is: a method for large-area, accurate, and rapid statistical analysis of immune data, comprising the following steps: (a) Create a structured template: Arrange N lower-level spreadsheets of the same format vertically in the same workbook in a fixed order, with each unit's data area occupying a predetermined row range; (b) Construct a summary table: Add a summary table with the same structure as the tables of the subordinate units at the end of the spreadsheet; (c) Set up a dynamic summary formula: Create a reference formula in the target cell of the summary table: =D1+D2+…+D N D x This represents the cell containing the data to be summarized at a fixed row position for the x-th unit. (d) Batch copy formulas: Copy the formula of the first target cell horizontally to other target cells in the same row; (e) Automated data aggregation: Copy the data of the new receiving unit to the corresponding row range of the template, triggering the real-time update of the aggregated data in the summary table.

[0006] Furthermore, in step (a), the fixed order is sorted according to the administrative division code, the row spacing of each unit's data area is a fixed value K, and the starting row of the data of the i-th unit is R0+(i-1)×K.

[0007] Furthermore, the formula used in step (c) is specifically A[R0] + A[R0 + K] + A[R0 + 2K] + ... + A[R0 + (N-1)K], where A[R0] = A[R0] + A[R0 + (N-1)K] + ... + A[R0 + (N-1)K], and A[R0] = A[R0] + A[R0 + (N-1)K] + ... + A[R0 + (N X ] indicates that the x-th unit is in the target data row of column A.

[0008] Furthermore, when there are M worksheets, steps (a)-(d) of claim 1 are executed independently for each worksheet to form M parallel summary channels.

[0009] Furthermore, step (c) further includes: Identify the row and column coordinates of the data to be summarized in each unit's table; Calculate the absolute reference position of each unit data cell based on the template row spacing rules; Generate arithmetic expressions that span multiple data cells.

[0010] Furthermore, step (e) specifically includes: (e1) Receive encrypted spreadsheet attachments via email system; (e2) Match the target row range in the template according to the unit identifier; (e3) Automatically trigger formula recalculation after data is pasted.

[0011] Furthermore, it also includes periodic summary and optimization steps: In subsequent statistical periods, only the data range content of each unit is updated, and the summary table automatically inherits the historical formulas to output new aggregation results.

[0012] Furthermore, the method is applicable to high-level data statistics systems for processing at least one of the following types of data: Animal inventory and distribution data; Vaccine usage and immunization coverage; Dynamic data on the positive rate of disease testing.

[0013] Furthermore, the spreadsheet is an Excel workbook, and the reference formula in step (c) uses the SUM function in combination with the OFFSET function to achieve dynamic range referencing.

[0014] Furthermore, after step (e) is executed, a data verification report is automatically generated, marking abnormal summary values ​​that exceed the preset threshold range.

[0015] The advantages of this invention compared to existing technologies are as follows: By establishing fixed templates and automatic formulas, it solves the shortcomings of existing technologies, such as large data volumes, low efficiency, and susceptibility to errors in manual data aggregation. No repetitive manual calculations are required; data is simply copied and pasted, and the system automatically aggregates the data, significantly improving the accuracy and timeliness of data aggregation, resulting in high efficiency. Attached Figure Description

[0016] Figure 1 This is a flowchart illustrating the workflow of a method for large-area, accurate, and rapid statistical analysis of immune data according to the present invention.

[0017] Figure 2 This refers to the weekly report in the background technology section.

[0018] Figure 3 This refers to the weekly report 2 in the background technology.

[0019] Figure 4 This is the monthly report 1 in the background technology.

[0020] Figure 5 This is the monthly report 2 in the background technology.

[0021] Figure 6 This is the monthly report 3 in the background technology. Detailed Implementation

[0022] To make the technical problems, solutions, and beneficial effects of this invention clearer, the invention will be further described in detail below with reference to embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0023] Working principle of the invention: like Figure 1 As shown, this technical solution is based on a streamlined data processing logic and relies on spreadsheet software functions. Through three main stages—template creation, formula setting, and data aggregation—it achieves automated and efficient aggregation of immune data. 9. Template creation stage, Figure 1 "Template creation phase" 1. Receiving Raw Forms: First, receive the raw forms submitted by subordinate units as the data input source. These forms are the basic materials for subsequent summarization and contain immunization data that each unit needs to report, such as the number of livestock and poultry in stock and the number of immunizations administered.

[0024] 2. Multi-Worksheet Detection: The received tables undergo structural identification to determine if a multi-worksheet scenario exists (e.g., separate worksheets involving different categories of diseases such as poultry and ungulates). If "yes," an independent template is created for each worksheet to ensure partitioned management of different data categories; if "no," the process proceeds directly to single-template creation. This design addresses the issue of confusion caused by multi-dimensional data, ensuring a clear data structure.

[0025] 3. Table Sorting and Spacing Settings: Sort lower-level unit tables according to fixed rules (such as administrative division codes) to ensure data traceability; simultaneously set a fixed row spacing K, with the formula for the starting row of the i-th unit's data being R0 + (i-1) × K (R0 is the initial row, and K is the row spacing parameter). Through standardized arrangement, each lower-level unit's data occupies a fixed "coordinate" in the template, providing a positioning basis for accurate formula referencing.

[0026] 4. Add summary table: Add a summary table at the end of the template. Its structure is consistent with the tables of the lower-level units. It is used to aggregate data from all units to form the final summary result carrier that can be reported.

[0027] II. Formula Setting Stage Figure 1 "Formula Setting Stage" 1. Target Data Coordinate Identification: Using the cell positioning function of spreadsheet software, identify the row and column coordinates of the data to be summarized in the lower-level unit table (e.g., the 11th row of column A records the number of chickens to be exempted). This step clarifies the location of the "data source," which is a prerequisite for accurate formula referencing.

[0028] 2. Summary Position Calculation: Based on the row spacing rules established during template creation, the target position of the corresponding data in the summary table is calculated using the formula: Summary Row Position = R0 + i × K (where i is the unit quantity and R0 is the starting row of the summary table). This mathematical mapping associates the scattered lower-level data with the summary table position, ensuring that the data is correctly positioned.

[0029] 3. Dynamic Formula Reference: In the target cell of the summary table, create a cross-unit data reference formula (e.g., =A11+A33+A55+…, corresponding to the coordinates of the lower-level units arranged by row spacing). Essentially, the formula aggregates data cells of the same type from N lower-level units (e.g., the number of chickens exempted from each township) through arithmetic operations, achieving the logical transformation of "dispersed data → centralized summary".

[0030] 4. Horizontal Formula Copy: Copy the formula in the first target cell (e.g., the summary formula in column A) horizontally to other cells in the same row (e.g., columns B and C). Because columns of the same type of data have a consistent structure (e.g., both are quantity statistics columns), copying allows for quick reuse of formulas, covering all dimensions of data to be summarized (e.g., different livestock categories such as ducks and geese), significantly reducing the workload of formula setup.

[0031] III. Data aggregation stage Figure 1 "Data aggregation stage" 1. New Data Reception and Matching: Receive new data within the receiving period (such as monthly or quarterly immunization data). By parsing attachments and matching row ranges by fixed IDs (using preset unit identifiers in the template), the new data is accurately "delivered" to the corresponding position in the template (i.e., the lower-level unit data row range). This step ensures the new data is compatible with the template structure and triggers subsequent automatic calculations.

[0032] 2. Automatic Formula Triggering and Calculation: After data is pasted, the spreadsheet software automatically triggers formula recalculation. Based on the reference relationships established during the formula setup phase, new data is substituted into the formula (e.g., newly added township data will be dynamically included in the =A11+A33+… operation), and the summary table results are updated in real time.

[0033] 3. Outlier Validation and Report Output: Built-in outlier validation rules (such as setting data threshold ranges) automatically validate the summary results. If the data exceeds the preset range (e.g., the number of livestock and poultry immunizations is abnormally high / low), it is marked as an anomaly and a summary report is generated to assist manual review and solve the problem of anomalies being easily overlooked during manual summarization.

[0034] 4. Periodic Task Cycle: Determine if the task is periodic (e.g., monthly, spring / autumn centralized immunization reporting). If so, repeat the "data reception → paste → calculation → verification" process; otherwise, end the current round of aggregation. This cyclical mechanism adapts to the business needs of long-term, repetitive reporting of livestock and poultry immunization data.

[0035] Implementation method: Create a template: Open Excel and create a new workbook. Paste 20 reports with the same format in coded order. Set the row spacing K to 22 rows. The starting row R0 of the first report is row 11, the starting row of the second report is row 11 + (2-1) × 22 = 33, and so on. The starting row of the i-th report is row 11 + (i-1) × 22, so that each report occupies a fixed row range in the template.

[0036] Create multiple parallel templates: If the reported data is in different worksheets within the same workbook, repeat the steps for each worksheet. For example, if there are 6 worksheets including "Avian Immunization Table" and "Even-toed Agave Immunization Table", create a template for each worksheet according to the above rules to ensure that the data is not misplaced.

[0037] Create a summary formula: Paste a summary table at the end of each worksheet. Taking the "Poultry Immunization Table" as an example, create the formula =A11+A33+A55+A77+A99+A121+A143+A165+A187+A209+A231+A253+A275+A297+A319+A341+A363+A385+A407+A429 in cell A451 of the summary table (the cell containing the number of chickens to be immunized in 20 townships). This formula references the data from the cells containing the number of chickens to be immunized in 20 townships.

[0038] To copy the summary formula: Select cell A451, copy it, and then paste it into cells B451, C451, D451, etc., to complete the summary formula setting for the number of ducks, geese, and other poultry to be exempted. Perform this operation on other cells that need to be summarized.

[0039] Data summary: Receive reports via a designated email address, download them, copy the data from each table sequentially, and paste them into the corresponding fixed row range in the template. The summary table will automatically update the data after pasting.

[0040] Periodic summary: For subsequent statistical periods, simply receive the new report and paste it into the corresponding location. The summary table will automatically inherit the formula and output the new results. At the same time, it can automatically generate a data validation report and mark outliers.

[0041] The present invention and its embodiments have been described above. This description is not restrictive, and the accompanying drawings are only one embodiment of the present invention; the actual structure is not limited thereto. In conclusion, if those skilled in the art are inspired by this description and design similar structures and embodiments without departing from the spirit of the invention, such designs should fall within the protection scope of the present invention.

Claims

1. A method for large-area, accurate, and rapid statistical analysis of immune data, characterized in that: Includes the following steps: (a) Create a structured template: Arrange N lower-level spreadsheets of the same format vertically in the same workbook in a fixed order, with each unit's data area occupying a predetermined row range; (b) Construct a summary table: Add a summary table with the same structure as the tables of the subordinate units at the end of the spreadsheet; (c) Set up a dynamic summary formula: Create a reference formula in the target cell of the summary table: =D1+D2+…+D N D x This represents the cell containing the data to be summarized at a fixed row position for the x-th unit. (d) Batch copy formulas: Copy the formula of the first target cell horizontally to other target cells in the same row; (e) Automated data aggregation: Copy the data of the new receiving unit to the corresponding row range of the template, triggering the real-time update of the aggregated data in the summary table.

2. The method for large-area, accurate, and rapid statistical analysis of immune data according to claim 1, characterized in that: In step (a), the fixed order is sorted according to the administrative division code, the row spacing of each unit's data area is a fixed value K, and the starting row of the data of the i-th unit is R0+(i-1)×K.

3. The method for large-area, accurate, and rapid statistical analysis of immune data according to claim 2, characterized in that: The formula used in step (c) is specifically A[R0] + A[R0 + K] + A[R0 + 2K] + ... + A[R0 + (N-1)K], where A[R0] = A[R0] + A[R0 + (N-1)K] + ... + A[R0 + (N-1)K], and A[R0] = A[R0] + A[R0 + (N-1)K] + ... + A[R0 + (N-1 X ] indicates that the x-th unit is in the target data row of column A.

4. The method for large-area, accurate, and rapid statistical analysis of immune data according to claim 1, characterized in that: When there are M worksheets, steps (a)-(d) of claim 1 are executed independently for each worksheet to form M parallel summary channels.

5. The method for large-area, accurate, and rapid statistical analysis of immune data according to claim 1, characterized in that: Step (c) further includes: Identify the row and column coordinates of the data to be summarized in each unit's table; Calculate the absolute reference position of each unit data cell based on the template row spacing rules; Generate arithmetic expressions that span multiple data cells.

6. The method for large-area, accurate, and rapid statistical analysis of immune data according to claim 1, characterized in that: Step (e) specifically includes: (e1) Receive encrypted spreadsheet attachments via email system; (e2) Match the target row range in the template according to the unit identifier; (e3) Automatically trigger formula recalculation after data is pasted.

7. The method for large-area, accurate, and rapid statistical analysis of immune data according to claim 1, characterized in that: It also includes periodic summary and optimization steps: In subsequent statistical periods, only the data range content of each unit is updated, and the summary table automatically inherits the historical formulas to output new aggregation results.

8. The method for large-area, accurate, and rapid statistical analysis of immune data according to claim 1, characterized in that: The method is applicable to high-level data statistics systems and is used to process at least one of the following types of data: Animal inventory and distribution data; Vaccine usage and immunization coverage; Dynamic data on the positive rate of disease testing.

9. The method for large-area, accurate, and rapid statistical analysis of immune data according to claim 1, characterized in that: The spreadsheet is an Excel workbook, and the reference formula in step (c) uses the SUM function in combination with the OFFSET function to achieve dynamic range referencing.

10. The method for large-area, accurate, and rapid statistical analysis of immune data according to claim 1, characterized in that: After step (e) is executed, a data verification report is automatically generated, marking abnormal summary values ​​that exceed the preset threshold range.

Images