An excel-based data processing and visualization method and system

Abstract

The application discloses an Excel-based data processing and visualization method and system, which comprises the following steps: loading a visualization user interface in an Excel workbook, wherein the user interface comprises interactive controls for selecting independent variable fields, dependent variable fields and grouping fields; receiving non-continuous selection of multiple categories in the grouping field by the user through the interactive controls, and automatically classifying the unselected categories into other groups to form an extended grouping mapping; matching and reorganizing the original data based on the extended grouping mapping by using a hybrid indexing mechanism; automatically generating a dynamic chart according to the reorganized data, wherein the coordinate axis range, legend position, series color and auxiliary line style of the dynamic chart are automatically configured according to the number of groups and the dependent variable value range; performing statistical analysis and multivariate linear regression on the data of each group, and converting the regression results into an executable formula string containing cell references and outputting the executable formula string to a specified cell.

Description

Technical Field

[0001] This invention belongs to the field of data processing technology, and relates to a data processing and visualization method and system based on Excel. Background Technology

[0002] In the current field of data processing and visualization, traditional Excel software is widely used for data analysis and report generation. However, these methods rely heavily on manual operations to complete tasks such as data grouping, chart generation, and statistical analysis. Users first need to manually select data ranges and then perform subtotaling. For complex datasets, this often involves multiple repetitive operations and is prone to errors. Furthermore, when generating charts, users must individually set chart types, axis options, and style properties, making the entire process time-consuming and laborious. More importantly, advanced analyses such as regression analysis or process capability assessments typically require users to have in-depth statistical knowledge and manually calculate relevant indicators, posing a significant challenge for non-experts.

[0003] Another limitation of existing technologies lies in their lack of flexibility and automation. For example, in multivariate comparative analysis scenarios, traditional tools cannot provide a simple way to dynamically adjust X-axis and Y-axis variables or quickly switch between different grouping criteria. Similarly, when it comes to real-time updates and re-evaluation of data, users must repeat all previous steps to reflect the latest data changes. This not only reduces work efficiency but may also lead to inaccurate results due to human error.

[0004] To address the aforementioned issues, the market urgently needs a technological solution capable of automating the entire process from data selection to analysis report generation. Ideally, the system should allow users to quickly define data grouping logic through a simple interactive interface, achieving intelligent grouping and automatically generating high-quality visualizations. Simultaneously, the system should integrate advanced analytical functions such as descriptive statistics, process capability analysis, and regression analysis to deeply uncover the value of the data. The ultimate goal is to create an efficient, accurate, and easy-to-use Excel-based data analysis tool, significantly improving data processing efficiency and reducing the possibility of human error. Such technological innovation will greatly promote the popularization and professionalization of data analysis, driving the development of related industries. Summary of the Invention

[0005] To address the problems existing in the background technology, this invention proposes a data processing and visualization method and system based on Excel.

[0006] The first aspect of this application provides a data processing and visualization method based on Excel, including: Load a visual user interface into an Excel workbook, the user interface including interactive controls for selecting independent variable fields, dependent variable fields, and grouping fields; The system receives non-contiguous selections of multiple categories in the grouping field from the user through the interactive control, and automatically assigns the unselected categories to other groups to form an extended grouping mapping. Based on the extended grouping mapping, a hybrid indexing mechanism is used to match and reorganize the original data; Dynamic charts are automatically generated based on the reorganized data. The coordinate axis range, legend position, series colors, and auxiliary line styles of the dynamic charts are automatically configured according to the number of groups and the range of the dependent variable values. Perform statistical analysis and multivariate linear regression on each group of data, and convert the regression results into an executable formula string containing cell references and output it to the specified cell.

[0007] Optionally, the hybrid indexing mechanism includes: firstly calling the script dictionary object to establish a key-value index; and automatically switching to a collection object to perform data matching when the runtime environment does not support the script dictionary object.

[0008] Optionally, the process of constructing the extended group mapping includes: traversing all unique categories in the grouping field; if a category is selected by the user, it is mapped to itself; if it is not selected, it is mapped to other groups.

[0009] Optionally, the dynamic chart is a scatter plot or a line chart; the auxiliary lines include the mean line within the group and the upper and lower limit lines of the specifications input by the user; the upper and lower limit lines of the specifications are drawn in the form of dashed lines and their values ​​can be modified through the user interface.

[0010] Optionally, the statistical analysis includes calculating the mean for each group, including: ; in, This represents the arithmetic mean of the dependent variable data in the g-th group; This represents the number of valid samples in the g-th group; This represents the dependent variable value of the i-th observed sample in the g-th group; g represents the group number, which is a positive integer; and i represents the sample number in the g-th group.

[0011] Optionally, the statistical analysis further includes calculating a process capability index; the process capability index includes an actual process capability index and a potential process capability index, including: ; ; in, It represents the actual process capability index, which measures the process's actual ability to meet specifications when considering center offset. This represents the maximum allowed value of the dependent variable, set by the user based on product quality or process requirements. This represents the arithmetic mean of the dependent variable data in the g-th group; The minimum allowable value of the dependent variable set by the user based on product quality or process requirements; This represents the sample standard deviation of the g-th group; min(,) means taking the smaller of the two values ​​in parentheses; The process potential capability index measures the actual ability of a process to meet specifications, taking into account center offset. The statistical characteristic corresponding to the mean ± 3 standard deviation under a normal distribution covers approximately 99.73% of the data; 6 equals 2 × 3, representing the total process variation range corresponding to the specification width, i.e., the ± 3σ interval.

[0012] Optionally, the multivariate linear regression is solved using the least squares method and satisfies the following formula: ; in, Represents the regression coefficient vector; Let X represent an n×(k+1) dimensional matrix, where k is the number of independent variables, the first column of X is a vector of all 1s, and the remaining columns correspond to the observed values ​​of their respective variables; express The transpose of the matrix; It is an n×1 dimensional vector, where n is the total number of samples.

[0013] Optionally, each element in the regression coefficient vector can be combined sequentially with the column label of the corresponding independent variable and output to an Excel cell.

[0014] Optionally, the method further includes generating multiple sets of histograms and superimposing a normal distribution curve; the probability density function of the normal distribution curve is: ; in, The vertical axis represents the height of the normal distribution curve corresponding to the x-axis value of the probability density function. This represents the sample standard deviation of the g-th group; Represents pi; Indicates the independent variable; This represents the arithmetic mean of the dependent variable data in the g-th group; It is the sample variance of the g-th group, and multiplying it by 2 is the requirement for the standard form of the normal distribution; This represents the natural exponential function.

[0015] A second aspect of this application provides an Excel-based data processing and visualization system, comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the aforementioned Excel-based data processing and visualization method.

[0016] Compared with the prior art, the present invention has the following beneficial effects: This invention provides a data processing and visualization method and system based on Excel. By loading a visual user interface into the Excel workbook, users can quickly and interactively select independent variable fields, dependent variable fields, and grouping fields, significantly reducing operational complexity and avoiding omissions or misselections that are prone to occur when manually selecting data areas in the traditional way. Secondly, the system supports non-contiguous selection of multiple categories in the grouping field and automatically assigns unselected categories to other groups, forming an expanded grouping map. This allows the analysis to focus on key subsets without losing secondary data information, improving the completeness and interpretability of the results. Thirdly, a hybrid indexing mechanism is used to match and reorganize the original data. In environments supporting script dictionary objects, efficient constant-time lookups are achieved, and in constrained environments, matching can still be completed even when automatically downgrading to set objects, ensuring stable operation of the method in various enterprise IT environments while balancing performance and compatibility. Fourthly, the axis range, legend position, series colors, and auxiliary line styles of dynamic charts are automatically configured based on the number of groups and the range of the dependent variable values. This generates well-laid-out and visually clear scatter plots or line charts without manual adjustment, greatly improving visualization efficiency and professionalism. Fifth, after performing statistical analysis and multivariate linear regression on the data in each group, the system converts the regression results into an executable formula string containing cell references and outputs it to a specified cell. This allows the model to be directly used for new data prediction, completing the final link from modeling to application and avoiding errors caused by manually piecing together formulas. Overall, this invention achieves a one-click closed-loop process from data selection, intelligent grouping, automatic matching, dynamic plotting to advanced analysis, compressing a manual process that originally took hours to complete in minutes. It also ensures the accuracy and reproducibility of the analysis results, making it suitable for various practical scenarios such as manufacturing quality control, experimental data analysis, and business operation monitoring. Attached Figure Description

[0017] Figure 1 This is a flowchart of a data processing and visualization method based on Excel according to an embodiment of the present invention; Figure 2 This is a schematic diagram of an Excel-based data processing and visualization system according to an embodiment of the present invention. Detailed Implementation

[0018] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0019] In one embodiment, such as Figure 1 As shown, an Excel-based data processing and visualization method is provided, which is then applied to... Figure 1 Taking China as an example, the following specific steps will be used: S10: Load a visual user interface into an Excel workbook, the user interface including interactive controls for selecting independent variable fields, dependent variable fields, and grouping fields.

[0020] Specifically, the implementation of loading a visual user interface into an Excel workbook is as follows. This user interface is embedded within the Excel application as a user form, constructed using the UserForm object in a VBA project. The user form automatically pops up when Excel starts its data analysis function. Its layout includes several standard interactive controls to receive user selection instructions for data fields. The independent variable field is selected using a combo box control, whose data source dynamically reads the column headers of the first row of the currently active worksheet, excluding null values ​​and non-text items. The dependent variable field is selected using another combo box control, supporting single or multiple selection modes. When users need to analyze multiple dependent variables, they can achieve non-contiguous multiple selection by holding down the control keys and clicking the mouse; the system allows a maximum of five dependent variables to be selected simultaneously. The grouping field is selected using a list box control, which also uses the column headers of the first row of the current worksheet as the selection source and enables multiple selection. Users can select one or more category values ​​as independent analysis groups. All interactive controls are dynamically bound to the underlying data table structure. When the user switches worksheets or modifies the column headers of the first row, the control options are automatically refreshed to maintain synchronization. This user interface does not rely on external plugins or ActiveX extensions; it is entirely based on Excel's built-in VBA user form controls, ensuring stable operation in various enterprise IT environments. After selecting fields, users trigger the subsequent data processing flow by clicking the confirmation button on the form. The selected field information is passed to the grouping mapping module as input for constructing extended grouping maps. The entire user interface design follows the principle of minimal operation, with all controls clearly laid out and labeled to avoid user errors, thereby ensuring the accuracy and repeatability of the data analysis process.

[0021] S20: Receive the user's non-contiguous selection of multiple categories in the grouping field through the interactive control, and automatically assign the unselected categories to other groups to form an extended grouping map.

[0022] Specifically, the implementation process of receiving non-contiguous selections of multiple categories in the grouping field by the user through the interactive control, and automatically assigning unselected categories to other groups to form an extended grouping mapping is as follows: The system first reads the user's selection status of the grouping field category values ​​from the list box control in the user interface. This list box control supports multi-selection mode, allowing users to non-contiguously select any number of categories using mouse clicks and control keys, without needing to select them sequentially or consecutively. The system iterates through all unique values ​​in the column containing the grouping field, determining whether each value is selected. For categories selected by the user, the system retains them as independent group identifiers for subsequent data classification and chart coloring. For categories not selected by the user, regardless of their frequency or value, the system uniformly maps them to a predefined string "Other" as the new group identifier. This mapping relationship is stored in an index structure in memory as key-value pairs, where the original category value is the key and the mapped group identifier is the value. This constructed mapping relationship covers all unique values ​​in the grouping field, ensuring that each record in the original data can find its corresponding group affiliation based on its grouping field value. This process executes automatically after the user confirms their selection, requiring no additional configuration or manual intervention. The resulting expanded grouping map not only includes the categories of interest to the user but also fully preserves the information integrity of the remaining data, avoiding analytical bias caused by ignoring low-frequency categories. This mechanism breaks through the traditional Excel grouping method, which only matches raw field values ​​one-to-one, and realizes a flexible grouping logic guided by user intent, providing structured input for subsequent statistical analysis and visualization.

[0023] S30: Based on the extended grouping mapping, a hybrid indexing mechanism is used to match and reorganize the original data.

[0024] Specifically, the implementation process of matching and reorganizing the original data using a hybrid indexing mechanism based on the extended grouping mapping is as follows: The system first initializes a dynamic index structure to establish a fast lookup relationship between grouping field values ​​and target group identifiers. This index structure is created using a dual fault-tolerance strategy. The system prioritizes calling a script dictionary object. If the current Excel runtime environment supports ActiveX components and the security policy allows it, the script dictionary object is successfully created, and each item in the extended grouping mapping is stored in it as a key-value pair, where the key is the original category value and the value is the mapped group identifier. If the script dictionary object creation fails due to enterprise security policies or system configuration limitations, the system immediately captures the runtime error and automatically switches to a collection object as the alternative index structure. In collection object mode, the system encapsulates each key-value pair as a string format "original value|group identifier" and adds it to the collection sequentially. During subsequent searches, matching is completed by traversing the collection and parsing the string. After the index is built, the system scans each cell value in the column containing the grouping field in the original data table row by row. For each row, the system queries the corresponding group identifier in the index structure based on the cell value. If a script dictionary object is used, constant-time matching is achieved through direct key access. If a set object is used, linear-time matching is achieved through sequential traversal and comparison. The retrieved group identifier is written to a specified column in the results area, aligned with the original data rows. Simultaneously, the corresponding values ​​of the independent and dependent variable fields are also copied to the results area, forming a data structure reorganized according to the group identifier. Each row of this reorganized data table contains the original observation and its final group, including user-selected independent groups or other groups generated by the system.

[0025] In one specific embodiment, a user analyzes the product thickness data of a manufacturing company, grouping the data by production line, which includes five categories: A, B, C, D, and E. The user only selects A and B as the focus groups. The system automatically assigns C, D, and E to the other groups. Subsequently, the system successfully creates a script dictionary object, establishing mappings: A→A, B→B, C→Other, D→Other, E→Other. Next, the system iterates through the original 10,000 rows of data, performing a dictionary lookup on the production line value for each row, completing all matching and reorganization in an average of 0.3 seconds. If the same operation is performed in an environment where ActiveX is disabled, the system automatically switches to collection mode, completing the reorganization through traversal matching in 2.8 seconds, which is still significantly better than the more than 12 seconds required for traditional array row-by-row comparison. This hybrid indexing mechanism ensures the stable operation of the method in different IT environments, guaranteeing processing efficiency in high-performance scenarios while also considering functional availability in constrained environments, effectively solving the compatibility issues caused by existing Excel analysis tools relying on a single indexing method.

[0026] S40: Automatically generate dynamic charts based on the recombined data. The coordinate axis range, legend position, series colors, and auxiliary line styles of the dynamic charts are automatically configured according to the number of groups and the range of the dependent variable values.

[0027] Specifically, the system automatically generates dynamic charts based on the reorganized data. The specific implementation process for automatically configuring the coordinate axis range, legend position, series colors, and auxiliary line styles of the dynamic charts based on the number of groups and the range of dependent variable values ​​is as follows: The system first reads the set of dependent variable values ​​for all groups in the reorganized data. For each group, it calculates the maximum and minimum values ​​of its dependent variable and determines the global maximum and minimum values ​​across all groups. The display range of the vertical axis is set accordingly, with an upper limit of 1.05 times the global maximum value and a lower limit of 0.95 times the global minimum value, ensuring that all data points are displayed completely with appropriate margins. The chart type is set to a scatter plot by default, but can be switched to a line chart when the user selects only one dependent variable. The system identifies the total number of groups in the reorganized data. If the total number of groups is three or less, the legend position is automatically set in the upper right corner of the chart. If the total number of groups is more than three, the legend position is automatically adjusted to the bottom center of the chart to avoid obscuring the data series. Each group corresponds to a data series. The system assigns colors sequentially from a predefined color sequence containing six distinct colors: red, green, blue, orange, purple, and gray, ensuring visual differentiation. Data belonging to other groups are uniformly identified using gray. Auxiliary lines include the group mean line, upper specification limit line, and lower specification limit line. The group mean line is a horizontal solid line, with its y-axis value equal to the arithmetic mean of the dependent variable for that group; the line style and color are consistent with the corresponding data series. The upper and lower specification limit lines are pre-input by the user and are drawn as red dashed lines spanning the entire width of the chart. All chart elements are programmatically created and formatted using Excel's built-in ChartObjects and SeriesCollection objects, requiring no manual adjustment by the user.

[0028] In one specific embodiment, the user analyzes product strength data from three production lines. Production lines one and two are selected, while the rest are grouped into other groups, forming three groups in total. After calculating the range of the dependent variable for each group, the system sets the vertical axis range to 47.5 to 105.0. Because there are three groups, the legend is placed in the upper right corner. Production line one uses red, production line two uses green, and the other groups use gray. A mean horizontal line matching the color of each group is added. The user inputs an upper limit of 100 and a lower limit of 50 for specifications, and the system draws red dashed lines at 100 and 50 on the vertical axis. The entire chart is generated within 1.2 seconds, with a clear layout and complete information. This automatic configuration mechanism significantly improves chart generation efficiency, avoiding problems such as data truncation, legend overlap, or color confusion caused by axis compression in traditional methods. This makes the analysis results more intuitive and reliable, especially suitable for rapid visualization needs in complex grouping scenarios such as multiple batches and multiple production lines.

[0029] S50: Perform statistical analysis and multivariate linear regression on each group of data, and output the regression results as an executable formula string containing cell references to the specified cell.

[0030] Specifically, the process of performing statistical analysis and multivariate linear regression on the data of each group, and converting the regression results into an executable formula string containing cell references and outputting it to a specified cell is as follows: First, the system divides the reorganized data into several subsets according to the group identifier, with each subset corresponding to an independent group. For each subset, the system calculates the arithmetic mean, sample standard deviation, 10th percentile, 50th percentile, and 90th percentile of its dependent variable, and calculates the process capability indices Cp and Cpk based on the user-input upper and lower specification limits. Then, if the user selects to perform regression analysis, the system reads the valid observations of all independent and dependent variable fields in that group, constructing a design matrix and a dependent variable vector. Each row of the design matrix corresponds to a sample, with the first column containing the value 1 for estimating the intercept term, and subsequent columns filled with the values ​​of each selected independent variable in that sample. Each element of the dependent variable vector is the dependent variable value for the corresponding sample. The system calls Excel's built-in matrix functions MMult, MInverse, and Transpose to calculate the regression coefficient vector according to the least squares principle. This vector contains the intercept term and the slope coefficients of each independent variable. After completing the regression calculation, the system enters the formula string generation stage. This stage iterates through each element in the regression coefficient vector, retaining the intercept term as a constant, concatenating each slope coefficient with the column letter of its corresponding independent variable, and appending a fixed row number (2) to form cell references in formats like A2 and B2. All terms are combined according to algebraic expression rules, with positive coefficients preceded by an equals sign and a plus sign, and negative coefficients preceded by a minus sign, ultimately forming a complete string beginning with an equals sign. This string is written into user-specified worksheet cells and can be directly executed as an Excel formula for predicting or evaluating new data rows.

[0031] In one specific embodiment, the user analyzes data on the influence of two independent variables—temperature and pressure—on product thickness, selecting production line one as the group. The system extracts 300 valid records from this group, constructs a 300-row, 3-column design matrix, and calculates an intercept of 3.210, a temperature coefficient of 0.842, and a pressure coefficient of -1.057. Temperature is located in column A of the worksheet, and pressure is located in column B. The system generates the formula string: 3.210 + 0.842 × A2 - 1.057 × B2. This string is output to cell D1. When the user enters new observations in A2 and B2, cell D2 can immediately obtain the predicted thickness value by copying this formula. The entire process, from data reading to formula output, takes less than two seconds. This mechanism breaks down the barriers between statistical modeling and business applications, allowing regression models to be reused directly in the original Excel environment without programming. This significantly lowers the barrier to entry for advanced analytical techniques, avoids symbol errors or misplaced references caused by manually constructing formulas, and improves the operability and deployment efficiency of the analysis results.

[0032] The statistical analysis includes calculating the mean for each group, including: ; in, This represents the arithmetic mean of the dependent variable data in the g-th group; This represents the number of valid samples in the g-th group; This represents the dependent variable value of the i-th observed sample in the g-th group; g represents the group number, which is a positive integer; and i represents the sample number in the g-th group.

[0033] The statistical analysis also includes calculating a process capability index; the process capability index includes an actual process capability index and a potential process capability index, including: ; ; in, It represents the actual process capability index, which measures the process's actual ability to meet specifications when considering center offset. This represents the maximum allowed value of the dependent variable, set by the user based on product quality or process requirements. This represents the arithmetic mean of the dependent variable data in the g-th group; The minimum allowable value of the dependent variable set by the user based on product quality or process requirements; This represents the sample standard deviation of the g-th group; min(,) means taking the smaller of the two values ​​in parentheses; The process potential capability index measures the actual ability of a process to meet specifications, taking into account center offset. The statistical characteristic corresponding to the mean ± 3 standard deviation under a normal distribution covers approximately 99.73% of the data; 6 equals 2 × 3, representing the total process variation range corresponding to the specification width, i.e., the ± 3σ interval.

[0034] The multivariate linear regression is solved using the least squares method and satisfies the following formula: ; in, Represents the regression coefficient vector; Let X represent an n×(k+1) dimensional matrix, where k is the number of independent variables, the first column of X is a vector of all 1s, and the remaining columns correspond to the observed values ​​of their respective variables; express The transpose of the matrix; It is an n×1 dimensional vector, where n is the total number of samples.

[0035] In this invention, the Excel-based data processing and visualization method further includes generating multiple sets of histograms and overlaying them with a normal distribution curve; the probability density function of the normal distribution curve is: ; in, The vertical axis represents the height of the normal distribution curve corresponding to the x-axis value of the probability density function. This represents the sample standard deviation of the g-th group; Represents pi; Indicates the independent variable; This represents the arithmetic mean of the dependent variable data in the g-th group; It is the sample variance of the g-th group, multiplied by 2 to get the standard form of the normal distribution; This represents the natural exponential function.

[0036] In one embodiment, in step S30, the hybrid indexing mechanism includes: firstly calling the script dictionary object to establish a key-value index; and automatically switching to a collection object to perform data matching when it is detected that the runtime environment does not support the script dictionary object.

[0037] Specifically, the hybrid indexing mechanism includes prioritizing the creation of a key-value index using a script dictionary object, and automatically switching to a collection object for data matching when the runtime environment does not support the script dictionary object. The specific implementation process is as follows: During the initialization phase, the system performs a probing operation, attempting to instantiate a script dictionary object using the CreateObject function. This operation is placed in an error handling structure. If the current Excel runtime environment has enabled support for ActiveX components and is not blocked by security policies, the script dictionary object is successfully created, and the system uses it as the primary index structure. In this mode, each original category value in the extended grouping map is used as a key, and the corresponding group identifier is used as a value, stored in the script dictionary object as a key-value pair. During subsequent data matching, the system queries the group identifier corresponding to any original category value using direct key access, with a constant time complexity and high matching efficiency. If the creation of the script dictionary object fails due to enterprise IT security policies disabling WindowsScriptHost or restricting ActiveX controls, the system immediately captures the runtime error and transitions to a fallback processing flow. During the downgrade process, the system creates a collection object and converts each item in the extended grouping map into a fixed-format string, which is constructed by concatenating the original category value, a vertical bar separator, and a group identifier. All such strings are added to the collection object sequentially. In the data matching phase, for each original category value to be matched, the system iterates through all strings in the collection object, parsing and comparing the preceding content for consistency. Once a match is found, the following content is extracted as the group identifier. Although this method uses a linear search, it maintains acceptable efficiency in practice because the total number of group categories is typically limited. The entire switching process is transparent to the user, requiring no manual intervention or configuration changes.

[0038] In one specific embodiment, quality analysts at a manufacturing company ran the method of this invention on two computers with different configurations. The first computer allowed ActiveX components; the system successfully created a script dictionary object and completed matching of data containing 8000 records and 5 grouping categories in 0.4 seconds. The second computer, due to the group's security policy disabling the Scripting runtime library, failed to create the script dictionary object. The system automatically switched to collection object mode and used string parsing for matching, processing the same dataset in 2.6 seconds. In contrast, relying solely on traditional array traversal matching would take over 11 seconds. This hybrid indexing mechanism effectively solves the problem of existing Excel analysis tools failing to run in restricted environments, ensuring the method has functional integrity and acceptable performance in various enterprise deployment scenarios. The technical effect is reflected in balancing high efficiency and high compatibility, avoiding functional failures due to reliance on external components, and significantly improving the applicability and robustness of automated data analysis tools.

[0039] In one embodiment, in step S30, the process of constructing the extended grouping map includes: S31: Iterate through all unique categories in the grouping field; S32: If a category is selected by the user, then map it to itself; S33: If not selected, map it to another group.

[0040] Specifically, the construction process of the extended grouping mapping includes traversing all unique categories in the grouping field. If a category is selected by the user, it is mapped to itself; otherwise, it is mapped to other groups. The specific implementation is as follows: The system first reads all non-empty cell values ​​in the column containing the grouping field from the original data table, and extracts all unique category values ​​through hash deduplication or sorting comparison methods to form a unique category list. This list contains all different values ​​of the grouping field that actually appear in the current dataset, without relying on a preset enumeration. Subsequently, the system obtains the user's selection status for these categories in the list box control. This status is stored in the form of a boolean array, where each element corresponds to an entry in the unique category list. A value of true indicates selection, and a value of false indicates non-selection. The system traverses the unique category list item by item, performing judgment logic for each category. If the selection status corresponding to the category is true, it is used as the key and itself as the value in the mapping relationship, thus establishing a mapping from the category to itself. If the selection status corresponding to the category is false, it is used as the key and the predefined string "other groups" as the value in the mapping relationship, thus establishing a mapping from the category to other groups. All mapping entries are sequentially written into the in-memory index structure, forming a complete extended grouping map. This map covers every actual category that appears in the grouping field, ensuring that no undefined cases occur in subsequent data matching stages. The mapping process is executed immediately after the user confirms their selection, requiring no additional input or configuration.

[0041] In one specific embodiment, a user analyzes the test yield data of an electronic component factory. The grouping field is the test station number, and the raw data contains five unique categories: test stations A01, A02, B03, C10, and D22. The user selects only A01 and B03 as the key analysis objects. After extracting these five unique categories, the system constructs a mapping based on the user's selection: A01 maps to A01, B03 maps to B03, A02 maps to other groups, C10 maps to other groups, and D22 maps to other groups. During subsequent data reorganization, all records from A02, C10, and D22 are grouped into the same "other groups" for statistical analysis and plotting. This mechanism allows users to focus on key subsets while avoiding cluttered charts or insufficient statistical samples caused by grouping low-frequency categories separately. The technical effect is to improve the targeting and visualization clarity of the analysis, especially suitable for industrial scenarios with many secondary categories but only a few core categories requiring attention. Compared to traditional Excel grouping methods that require retaining all original categories, this method effectively simplifies the output structure, enhances the interpretability of the analysis results, and fully preserves information from non-interesting data, preventing bias caused by information loss.

[0042] In one embodiment, in step S40, the dynamic chart is a scatter plot or a line chart; the auxiliary lines include the mean line within the group and the upper and lower limit lines of the specifications input by the user; the upper and lower limit lines of the specifications are drawn in the form of dashed lines and their values ​​can be modified through the user interface.

[0043] Specifically, the dynamic chart is a scatter plot or a line chart. The auxiliary lines include the within-group mean line and the upper and lower specification limits input by the user. The upper and lower specification limits are drawn as dashed lines, and their values ​​can be modified through the user interface. The specific implementation process is as follows: When creating a chart object, the system defaults to a scatter plot to show the distribution relationship between the independent and dependent variables. When the user selects only one dependent variable and the data has time or order attributes, the system can also generate a line chart to reflect trend changes. After the chart is created, the system adds an independent data series for each group to ensure that different groups are visually distinguishable. Subsequently, the system calculates the arithmetic mean of the dependent variable for each group and adds a horizontal reference line to the chart. This line runs through the entire horizontal axis, and its vertical axis value is equal to the mean of the group. The line type is a solid line, and the color is consistent with the corresponding data series; this is called the within-group mean line. At the same time, the system reads the upper and lower specification limits input by the user through the user interface. If the user does not input them, no corresponding auxiliary lines are drawn. If input has already been made, the system adds two additional horizontal reference lines to the chart: one at the upper specification limit and the other at the lower specification limit. Both lines are red dashed lines with the same width as the default grid lines to distinguish them from the data series and mean lines. All auxiliary lines are added to the chart object as independent series, with their data source dynamically generated by the system, containing two endpoint coordinates to form a straight line across the chart. Users can return to the user interface at any time during analysis to modify the upper or lower specification limit values. The system automatically updates the position of the corresponding auxiliary lines upon detecting the value change, without needing to regenerate the entire chart.

[0044] In one specific embodiment, an automotive parts supplier analyzes product diameter data. The user selects the production line as the grouping field, resulting in three groups. The system generates a scatter plot, with each group labeled with a different color. The user inputs an upper limit of 50.2 mm and a lower limit of 49.8 mm in the interface. The system plots red dashed lines at the vertical axes of 50.2 and 49.8. The mean line within each group is displayed as a solid line of the corresponding color; for example, if the mean for production line one is 50.05, its mean line is located at 50.05. Subsequently, the user discovers an update to the specification standard, changing the upper limit to 50.3. The system immediately refreshes the chart, moving the dashed line from 50.2 to 50.3. The entire process does not require closing the chart or rerunning the analysis workflow. This mechanism provides real-time visualization of the comparison between specification limits and process performance, facilitating rapid identification of deviation risks. The technical effect is to improve the response speed and interactive flexibility of quality monitoring, avoiding the cumbersome operation and positioning errors caused by manually adding or adjusting reference lines in traditional methods, and ensuring that the analysis results are always consistent with the latest process requirements.

[0045] In one embodiment, such as Figure 2As shown, an Excel-based data processing and visualization system is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the Excel-based data processing and visualization system method.

[0046] For specific limitations regarding the Excel-based data processing and visualization system, please refer to the limitations of the Excel-based data processing and visualization methods mentioned above, which will not be repeated here. Each module in the aforementioned Excel-based data processing and visualization system can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in the computer device in hardware form, or stored in the memory of the computer device in software form, so that the processor can call and execute the corresponding operations of each module.

[0047] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, storage, databases, or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), dual data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), Rambus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc. Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is used as an example. In practical applications, the above functions can be assigned to different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0048] Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art can still modify the technical solutions described in the foregoing embodiments or make equivalent substitutions for some of the technical features. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A data processing and visualization method based on Excel, characterized in that, include: Load a visual user interface into an Excel workbook, the user interface including interactive controls for selecting independent variable fields, dependent variable fields, and grouping fields; The system receives non-contiguous selections of multiple categories in the grouping field from the user through the interactive control, and automatically assigns the unselected categories to other groups to form an extended grouping mapping. Based on the extended grouping mapping, a hybrid indexing mechanism is used to match and reorganize the original data; Dynamic charts are automatically generated based on the reorganized data. The coordinate axis range, legend position, series colors, and auxiliary line styles of the dynamic charts are automatically configured according to the number of groups and the range of the dependent variable values. Perform statistical analysis and multivariate linear regression on each group of data, and convert the regression results into an executable formula string containing cell references and output it to the specified cell.

2. The data processing and visualization method based on Excel according to claim 1, characterized in that, The hybrid indexing mechanism includes: First, the script dictionary object is called to establish the key-value index; When the runtime environment is detected to be unsupported by script dictionary objects, the system automatically switches to collection objects to perform data matching.

3. The data processing and visualization method based on Excel according to claim 1, characterized in that, The process of constructing the extended grouping map includes: Iterate through all unique categories in the grouping field; If a user selects a category, then that category is mapped to itself; If it is not selected, it will be mapped to another group.

4. The data processing and visualization method based on Excel according to claim 1, characterized in that, The dynamic chart is a scatter plot or a line chart; the auxiliary lines include the mean line within the group and the upper and lower limit lines of the specifications input by the user; the upper and lower limit lines of the specifications are drawn in the form of dashed lines and their values ​​can be modified through the user interface.

5. The data processing and visualization method based on Excel according to claim 1, characterized in that, The statistical analysis includes calculating the mean for each group, including: ； in, This represents the arithmetic mean of the dependent variable data in the g-th group; This represents the number of valid samples in the g-th group; This represents the dependent variable value of the i-th observed sample in the g-th group; g represents the group number, which is a positive integer; and i represents the sample number in the g-th group.

6. The data processing and visualization method based on Excel according to claim 5, characterized in that, The statistical analysis also includes calculating the process capability index; The process capability index includes the actual process capability index and the potential process capability index, including: ； ； in, It represents the actual process capability index, which measures the process's actual ability to meet specifications when considering center offset. This represents the maximum allowed value of the dependent variable, set by the user based on product quality or process requirements. This represents the arithmetic mean of the dependent variable data in the g-th group; The minimum allowable value of the dependent variable set by the user based on product quality or process requirements; This represents the sample standard deviation of the g-th group; min(,) means taking the smaller of the two values ​​in parentheses; The process potential capability index measures the actual ability of a process to meet specifications, taking into account center offset. The statistical characteristic corresponding to the mean ± 3 standard deviation under a normal distribution covers approximately 99.73% of the data; 6 equals 2 × 3, representing the total process variation range corresponding to the specification width, i.e., the ± 3σ interval.

7. The data processing and visualization method based on Excel according to claim 1, characterized in that, The multivariate linear regression is solved using the least squares method and satisfies the following formula: ； in, Represents the regression coefficient vector; Let X represent an n×(k+1) dimensional matrix, where k is the number of independent variables, the first column of X is a vector of all 1s, and the remaining columns correspond to the observed values ​​of their respective variables; express The transpose of the matrix; It is an n×1 dimensional vector, where n is the total number of samples.

8. The data processing and visualization method based on Excel according to claim 7, characterized in that, The method for generating the executable formula string includes: combining each element in the regression coefficient vector with the column label of the corresponding independent variable in sequence, and outputting it to an Excel cell.

9. The data processing and visualization method based on Excel according to claim 1, characterized in that, The method further includes generating multiple sets of histograms and superimposing them with a normal distribution curve; the probability density function of the normal distribution curve is: ； in, The vertical axis represents the height of the normal distribution curve corresponding to the x-axis value of the probability density function. This represents the sample standard deviation of the g-th group; Represents pi; Indicates the independent variable; This represents the arithmetic mean of the dependent variable data in the g-th group; It is the sample variance of the g-th group, multiplied by 2 to get the standard form of the normal distribution; This represents the natural exponential function.

10. An Excel-based data processing and visualization system, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the Excel-based data processing and visualization method according to any one of claims 1 to 9.

Images