A data analysis system based on EXCEL table development

Abstract

The application discloses a data analysis system based on EXCEL table development and belongs to the technical field of industrial data analysis, and solves the technical problem of poor universality of existing professional data analysis software. The data analysis system is developed based on the EXCEL table combined with a VBA program and is operated in the original environment of EXCEL, and specifically comprises the following modules: a data import and automatic analysis module, a variable intelligent screening module, an interface and control automatic generation module, a chart automatic rendering module, a dynamic variable addition function module, a curve clarity quick adjustment module, a cursor instantaneous multi-parameter reading module, a sandwiched scrolling cursor module and a background parameter isolation operation module. The system can realize zero additional environment installation, full-automatic drawing, variable dynamic addition, instantaneous multi-parameter reading, scroll wheel scaling X axis, curve left-right translation in the magnified state, self-defined cursor resolution, quick curve adjustment and intelligent background parameter isolation, so that the use threshold of data analysis is reduced, and the operation convenience and analysis efficiency are improved.

Description

Technical Field

[0001] This invention relates to the field of industrial data analysis technology, and more specifically, to a data analysis system developed based on Excel spreadsheets. Background Technology

[0002] Data analysis and curve visualization are commonly used techniques in industrial production, equipment operation and maintenance, operating condition monitoring, and fault diagnosis. Currently, the mainstream time series data analysis and curve plotting tools in the industry are mainly divided into three categories: professional programming plotting tools, industrial configuration software, and general-purpose plotting software.

[0003] Professional programming and drawing tools and industrial configuration software are comprehensive, but they require separate software installation and environment configuration, making the deployment process complex and demanding high levels of professional knowledge from users. Traditional general-purpose drawing tools and basic Excel native drawing functions can only achieve simple curve display, and complete data visualization still relies on a lot of manual operation.

[0004] The existing conventional data analysis software and plotting tools operate as follows: First, the data source is manually imported, then the data source is manually configured, new curve sequences are added, coordinate axis parameters are set one by one, and curve colors are matched. If a new observation variable needs to be added, all data must be re-imported, the chart reconstructed, and all parameters reconfigured repeatedly. In fault analysis scenarios, traditional tools can only display the overall trend of the curves and cannot quickly read the real-time parameters corresponding to multiple curves at a certain moment. When adjusting the clarity of the curve display, it is necessary to click on each curve and modify the parameters in a pop-up window. To zoom in / out on the X-axis, the coordinate values ​​must be modified by bringing up the settings interface through the right-click menu, which is cumbersome and lacks real-time performance. At the same time, after zooming in, traditional tools cannot directly move the curve left or right. To view the continuous data range before and after zooming in, it is necessary to repeatedly zoom and adjust the coordinates, which is very inconvenient for viewing large-scale continuous data. In addition, the traditional cursor movement step is fixed, and the dragging process is choppy and lacks smooth operation.

[0005] Meanwhile, most professional data analysis software on the market are standalone applications that require separate download, installation, and configuration of a dedicated operating environment. In scenarios involving multiple devices and temporary on-site analysis, deployment is cumbersome, the learning curve is steep, and versatility is poor.

[0006] The shortcomings of existing technologies: 1) High barrier to entry and large amount of manual operation: Traditional plotting and data analysis tools require operators to master professional plotting skills. After importing data, curves need to be added manually, coordinate axes need to be configured, and color schemes need to be set. The whole process depends on manual operation, which is inefficient. 2) Poor scalability and inability to dynamically add variables: After importing data and generating curves, if it is necessary to add observation variables, the data must be re-imported and the entire chart must be redrawn, forcing the interruption of the data analysis process and resulting in many repetitive operations. 3) Low efficiency in fault tracing: It can only display the overall trend of the curve, and cannot simultaneously read the instantaneous parameters of multiple curves at a specified time node, making it difficult to quickly locate the cause of instantaneous faults and abnormal operating conditions. 4) Cumbersome coordinate axis operations: To zoom in or out on the X-axis, you must right-click to bring up the menu and modify the coordinate parameters layer by layer; after zooming in, the view does not support overall left and right translation of the curve, and to view a large continuous data range in the zoomed-in state, you need to repeatedly adjust the parameters, which is cumbersome. 5) Lack of dedicated analysis cursor and insufficient data reading capability: Traditional plotting methods do not have a dedicated analysis cursor. Only single-point coordinates can be viewed by clicking with the mouse. It is impossible to obtain the corresponding values ​​of all parameters at the same time, and it is impossible to achieve multi-parameter joint comparison. It is difficult to quickly determine the causes of faults and anomalies. 6) Cumbersome parameter adjustment: When modifying the curve range or adjusting the display clarity, it is necessary to select the curve and modify the parameters through a pop-up window, which involves multiple operation levels and slow response speed. 7) There is a problem of mixed parameters: Traditional plotting and simple data visualization tools cannot actively distinguish between functional backend parameters and business analysis parameters. Backend auxiliary parameters will be directly displayed in the analysis list, interfering with normal data observation and analysis. 8) Complex deployment and reliance on dedicated operating environment: Professional data analysis software are all independent programs that require additional software and supporting operating environment installation. They cannot be run directly using general office software, resulting in insufficient portability and versatility, and are extremely inconvenient for temporary on-site use and use on multiple devices. Summary of the Invention

[0007] The technical problem this invention aims to solve is to address the aforementioned shortcomings of existing technologies. The purpose of this invention is to provide a data analysis system based on Excel spreadsheets. This system is developed using secondary development based on Excel spreadsheets and relies on the general Excel operating environment to build fully automated data analysis software. It aims to solve problems in existing technologies such as excessive manual operation, inability to dynamically add variables, difficulty in fault tracing, cumbersome coordinate axis scaling and translation operations, lack of dedicated analysis cursors, cumbersome parameter adjustment, interference from background parameters, and deployment dependence on dedicated environments. It achieves zero additional environment installation, fully automatic plotting, dynamic variable addition, instantaneous multi-parameter reading, scroll wheel scaling of the X-axis, left and right translation of curves in zoomed-in state, customizable cursor resolution, rapid curve adjustment, and intelligent isolation of background parameters, lowering the barrier to entry for data analysis and comprehensively improving operational convenience and analysis efficiency.

[0008] The technical solution of this invention is: a data analysis system developed based on Excel spreadsheets. This data analysis system is a secondary development based on Excel spreadsheets combined with VBA programs, and runs within the native Excel environment. Specifically, it includes: The data import and automatic parsing module is used to acquire the time-series data in CSV format selected by the user, separate the time axis and each business parameter column, and automatically clean up old data in the table, delete the original controls and charts, and obtain the parsed data. The intelligent variable filtering module is used to automatically filter out redundant parameters of the background cursor line in the parsed data, display only the valid business analysis variables, and support users to visually select at least one variable. The interface and control automatic generation module is used to automatically generate an integrated analysis interface based on the variables selected by the user. The integrated analysis interface includes a variable selection list and an analysis list. The variable selection list has a variable checkbox, a variable name, a lower limit coordinate configuration, an upper limit coordinate configuration, and a current value. The variables selected by the user are displayed one by one in the variable name. The variable checkbox generates checkboxes corresponding to each variable. The lower limit coordinate configuration displays the lower limit coordinate value corresponding to each variable. The upper limit coordinate configuration displays the upper limit coordinate value corresponding to each variable. The fully automatic chart rendering module is used to automatically create XY scatter plots in the analysis list based on the variables selected by the user, bind time series data with corresponding parameter curves, and automatically adapt to the basic range and hide redundant grid lines. The dynamic variable addition module allows users to add variables at any time during data analysis and curve display, enabling them to select new analysis variables for secondary analysis. The curve clarity quick adjustment module allows users to directly modify values ​​in the lower limit and upper limit configuration fields of the coordinates. Modifications are linked to the chart in real time, updating the curve scaling ratio in milliseconds to quickly adjust the curve clarity. The cursor instantaneous multi-parameter reading module is used to simultaneously read the instantaneous parameters corresponding to all displayed curves at the current moment when the scroll cursor in the mezzanine is dragged to any time node, so as to realize multi-parameter joint comparison and quickly analyze the causes of faults and abnormal operating conditions. The mezzanine scroll cursor module is used to implement coordinate axis scaling, overall curve translation, and define the cursor movement resolution; The background parameter isolation operation module is equipped with a data isolation mechanism, which ensures that the cursor line parameter only supports the cursor function in the background and will not appear in the variable selection list or analysis list, thus ensuring the purity of the front-end analysis interface.

[0009] As a further improvement, after the data import and automatic parsing module obtains the parsed data, it automatically triggers the variable intelligent filtering module to generate a variable selection window. The variable selection window displays the parsed data and selection buttons, and the user selects at least one variable in the variable selection window.

[0010] Furthermore, the variable selection window is equipped with dual fault tolerance logic: when the user clicks Cancel, or clicks OK without selecting any variables, the data analysis system directly terminates the import process and does not generate any variable selection list or analysis list, thus avoiding the mis-import of all data.

[0011] Furthermore, a multi-color cycle algorithm is used to assign independent colors to the different variable curves in the variable name column, and the color of the curve corresponding to the variable is the same as the color of the variable.

[0012] Furthermore, after selecting the checkbox corresponding to the variable, the fully automatic chart rendering module automatically creates an XY scatter line chart corresponding to that variable.

[0013] Furthermore, the dynamic variable appending function module has a built-in variable comparison algorithm that automatically identifies and skips existing duplicate variables, appends new variables only to the end of the original variable selection list, and simultaneously generates checkboxes corresponding to the new variables, matches curve colors, and renders new curves.

[0014] Furthermore, the coordinate axis scaling specifically refers to: using the cursor as the center, directly scaling the X-axis time axis by clicking the "X-axis zoom in" button or the "X-axis zoom out" button.

[0015] Furthermore, the overall translation of the curve is specifically as follows: in the zoomed-in view, the "move left" or "move right" button can be clicked directly to complete the overall left and right movement of the curve.

[0016] Furthermore, the definition of cursor movement resolution specifically refers to adjusting the cursor movement step size as needed to make cursor dragging smoother and more precise.

[0017] Beneficial effects Compared with the prior art, the advantages of this invention are as follows: 1. No additional environment installation required, highly versatile: The system is developed entirely based on Excel spreadsheets and can run using the native Excel environment commonly found in office scenarios. Users do not need to download or install any additional software or runtime components. It is simple to deploy, easy to carry, and suitable for various scenarios such as offices and on-site maintenance, greatly improving convenience. 2. Zero manual drawing, low barrier to entry: After importing data, the system automatically completes parsing, interface generation, control arrangement, curve drawing, and automatic color matching. Operators do not need to master professional drawing skills, and even beginners can quickly get started. 3. Supports dynamic addition of variables, ensuring strong continuity of analysis: New analytical variables can be added at any time during the analysis process, and duplicates are automatically removed and corresponding curves are generated without the need to re-import data or redraw charts, ensuring the continuous operation of data analysis. 4. Instantaneous multi-parameter reading and efficient fault tracing: Through a dedicated analysis cursor, real-time data of multiple curves at any time can be read, and the status of multiple parameters can be compared synchronously to quickly locate the cause of faults and anomalies, thereby improving the efficiency of fault analysis. 5. Quickly zoom the X-axis and simplify operation: Equipped with a mezzanine scroll cursor, you can directly zoom in and out along the X-axis using buttons with the cursor as the center, eliminating the cumbersome operation of right-clicking to modify coordinates and quickly switching between global views and local details. 6. Supports curve panning in zoomed-in mode for a wider viewing range: After zooming in, the curve can be moved left and right as a whole without repeatedly modifying coordinate parameters. This allows for easy viewing of a large range of continuous data in zoomed-in mode, providing a more comprehensive view of data analysis. 7. Adjustable cursor resolution for smooth and precise control: Supports customizable cursor movement resolution and flexible adjustment of movement step size. The cursor drag is smooth and lag-free, allowing you to quickly browse overall data or accurately locate minute anomalies. 8. Simple parameter adjustment and fast response: The clarity of the curve can be adjusted by directly modifying the coordinate parameters in the table, eliminating the need for multiple pop-up windows, simplifying the steps and providing real-time response. 9. Intelligent isolation of redundant parameters, clean analysis environment: It can automatically identify and block background auxiliary parameters to avoid redundant parameters interfering with business data analysis. It is also equipped with anti-misdirection logic to prevent batch data from being loaded incorrectly. 10. High degree of automation and low overall cost of use: The entire process is automated, reducing manual operation and adapting to various data analysis scenarios such as industrial time-series data monitoring, equipment fault analysis, and operating condition statistics. Attached Figure Description

[0018] Figure 1 This is a diagram illustrating the import of CSV data. Figure 2 This is an illustration showing that no variables have been selected in the variable selection window; Figure 3 This is a diagram illustrating the selection of variables in the variable selection window. Figure 4 This is a diagram illustrating the variable selection list after selecting a variable. Figure 5 A schematic diagram for creating a variable curve; Figure 6 This is an illustration of the variable selection window that appears when the "Add Variable" button is clicked; Figure 7 This is a diagram illustrating the selection of variables in the variable selection window. Figure 8 A diagram showing the result after dynamically adding variables; Figure 9 This is a schematic diagram without modifying the upper limit of the coordinates; Figure 10 This is a diagram showing the changes after modifying the upper limit of the coordinates; Figure 11 This is a schematic diagram illustrating the instantaneous reading of multiple parameters of the cursor. Figure 12 This is a schematic diagram without enlargement. Figure 13 This is an enlarged schematic diagram; Figure 14 This is a schematic diagram showing the view shifted to the right. Figure 15 This is a diagram illustrating the definition of cursor movement resolution. Detailed Implementation

[0019] The present invention will be further described below with reference to specific embodiments shown in the accompanying drawings.

[0020] See Figures 1-15 A data analysis system developed based on Excel spreadsheets. The system is a secondary development based on Excel spreadsheets combined with VBA programs, and runs within the native Excel environment. Specifically, it includes: The data import and automatic parsing module is used to acquire the time-series data in CSV format selected by the user, separate the time axis from the columns of various business parameters, and automatically clean up old data in the table, delete the original controls and charts, and obtain the parsed data.

[0021] The Excel spreadsheet interface includes buttons for "Import CSV Data," "Add Variable," "Refresh Axis," and "Start Cursor." Clicking the "Import CSV Data" button opens the path to the CSV format time-series data and imports the data, such as... Figure 1 As shown.

[0022] The intelligent variable filtering module is used to automatically filter redundant parameters of the background cursor line in the parsed data, displaying only valid business analysis variables, and allowing users to visually select at least one variable.

[0023] After the data import and automatic parsing module obtains the parsed data, the intelligent variable filtering module is automatically triggered to generate a variable selection window. The variable selection window displays the parsed data and selection buttons. The user selects at least one variable in the variable selection window, such as... Figures 2-3 As shown.

[0024] The variable selection window is equipped with dual fault tolerance logic: when the user clicks Cancel, or clicks OK without selecting any variables, the data analysis system directly terminates the import process and does not generate any variable selection list or analysis list to avoid mis-importing of all data.

[0025] The interface and control auto-generation module is used to automatically generate an integrated analysis interface based on the variables selected by the user. The integrated analysis interface includes a variable selection list and an analysis list. The variable selection list has a variable checkbox, a variable name, a lower limit coordinate configuration, an upper limit coordinate configuration, and a current value. The variables selected by the user are displayed one by one in the variable name field. The variable checkbox generates checkboxes corresponding to each variable. The lower limit coordinate configuration displays the lower limit coordinate value corresponding to each variable. The upper limit coordinate configuration displays the upper limit coordinate value corresponding to each variable.

[0026] After selecting variables in the variable selection window, the user clicks "Add" and then "OK." The selected variables are automatically added to the variable name field. The lower and upper coordinate limit configuration fields display the lower and upper coordinate limits of the variables, respectively. Figure 4 As shown.

[0027] A multi-color cycle algorithm (i.e., a cycle coloring formula) is used to assign independent colors to different variable curves in the variable name column. At the same time, the color of the curve corresponding to the variable is the same as the color of the variable, eliminating the need for manual layout and color matching.

[0028] The fully automatic chart rendering module automatically creates XY scatter plots in the analysis list based on user-selected variables, binds time-series data with corresponding parameter curves, and automatically adapts to the basic range and hides redundant grid lines, eliminating the need for manual configuration of chart parameters throughout the process.

[0029] After selecting the checkbox corresponding to the variable, the fully automatic chart rendering module automatically creates an XY scatter line chart corresponding to that variable.

[0030] Specifically, after checking the corresponding checkboxes in front of the variables, users can click the "Refresh Axis" button to create XY scatter plots of each variable in the analysis list, such as... Figure 5 As shown.

[0031] The dynamic variable addition module allows users to add variables at any time during data analysis and curve display, enabling them to select new analysis variables for secondary analysis.

[0032] When a user clicks the "Add Variable" button, a variable selection window will pop up. After selecting the variable in the window, the user clicks "Add" and then "OK" to add the new variable to the variable name field. Figures 6-8 As shown.

[0033] The dynamic variable appending function module has a built-in variable comparison algorithm that automatically identifies and skips existing duplicate variables, appends new variables only to the end of the original variable selection list, and simultaneously generates checkboxes corresponding to the new variables, matches curve colors, and renders new curves, without the need to re-import data or redraw the entire chart.

[0034] The curve sharpness quick adjustment module allows users to directly modify values ​​in the lower and upper coordinate limit configuration fields. These modifications are reflected in the chart in real time, updating the curve scaling ratio in milliseconds for rapid adjustment of curve sharpness. The module is simple to operate and responds quickly. Figures 9-10 As shown.

[0035] The cursor instantaneous multi-parameter reading module is used to simultaneously read the instantaneous parameters corresponding to all displayed curves at any given time point when the cursor is dragged to any time point in the mezzanine. These instantaneous parameters are displayed in the current value column to enable multi-parameter joint comparison and rapid analysis of the causes of faults and abnormal operating conditions. Figure 11 As shown.

[0036] The mezzanine scroll cursor module is used to implement coordinate axis scaling, overall curve translation, and define the cursor's movement resolution.

[0037] The coordinate axis scaling function allows users to directly zoom in or out of the X-axis time axis by clicking the "X-axis Zoom In" or "X-axis Zoom Out" button, centered on the cursor. This eliminates the need to modify coordinate parameters via a right-click menu, enabling quick switching between global trends and local details. Figures 12-13 As shown.

[0038] The overall curve translation is as follows: In zoomed-in view, you can directly click the "Shift Left" or "Shift Right" button to move the entire curve left or right. This allows you to view a wider range of continuous data in zoomed-in mode, such as... Figure 14 As shown.

[0039] Define the cursor movement resolution (i.e., cursor sensitivity) as follows: Adjust the cursor movement step size as needed to make cursor dragging smoother and more precise, facilitating quick location of instantaneous anomalies and fault locations, such as... Figure 15 As shown.

[0040] The background parameter isolation module, where cursor line parameters are essential for chart rendering, employs a data isolation mechanism. This ensures that the cursor line parameters only run in the background to support cursor functionality, and they do not appear in the variable selection or analysis lists, thus maintaining a clean front-end analysis interface. Figure 11The vertical red positioning line in the analysis list (i.e., the graph list) is the cursor line. This cursor line itself is a curve, albeit a vertical one, and therefore it also requires support from the source data. However, the traditional approach is to artificially add a column of data to the data to be plotted. This column has a fixed Y-axis, while the X-axis changes with the data. This adds extra data to the plotting data and creates additional variables at various stages, such as in the parameter selection interface or in the variable list, impacting the interface and consuming computational resources. Therefore, this invention hides the cursor line parameter as a shadow curve, which is not included in the source data.

[0041] The above are merely preferred embodiments of the present invention. It should be noted that those skilled in the art can make several modifications and improvements without departing from the structure of the present invention, and these will not affect the effectiveness of the implementation of the present invention or the practicality of the patent.

Claims

1. A data analysis system developed based on Excel spreadsheets, characterized in that, The data analysis system is a secondary development based on Excel spreadsheets and VBA programs, and runs on the native Excel environment. Specifically, it includes: The data import and automatic parsing module is used to acquire the time-series data in CSV format selected by the user, separate the time axis and each business parameter column, and automatically clean up old data in the table, delete the original controls and charts, and obtain the parsed data. The intelligent variable filtering module is used to automatically filter out redundant parameters of the background cursor line in the parsed data, display only the valid business analysis variables, and support users to visually select at least one variable. The interface and control automatic generation module is used to automatically generate an integrated analysis interface based on the variables selected by the user. The integrated analysis interface includes a variable selection list and an analysis list. The variable selection list has a variable checkbox, a variable name, a lower limit coordinate configuration, an upper limit coordinate configuration, and a current value. The variables selected by the user are displayed one by one in the variable name. The variable checkbox generates checkboxes corresponding to each variable. The lower limit coordinate configuration displays the lower limit coordinate value corresponding to each variable. The upper limit coordinate configuration displays the upper limit coordinate value corresponding to each variable. The fully automatic chart rendering module is used to automatically create XY scatter plots in the analysis list based on the variables selected by the user, bind time series data with corresponding parameter curves, and automatically adapt to the basic range and hide redundant grid lines. The dynamic variable addition module allows users to add variables at any time during data analysis and curve display, enabling them to select new analysis variables for secondary analysis. The curve clarity quick adjustment module allows users to directly modify values ​​in the lower limit and upper limit configuration fields of the coordinates. Modifications are linked to the chart in real time, updating the curve scaling ratio in milliseconds to quickly adjust the curve clarity. The cursor instantaneous multi-parameter reading module is used to simultaneously read all instantaneous parameters corresponding to the displayed curves at the current moment when the scroll cursor in the mezzanine is dragged to any time node. The instantaneous parameters are displayed in the current value column to realize multi-parameter joint comparison and quickly analyze the causes of faults and abnormal operating conditions. The mezzanine scroll cursor module is used to implement coordinate axis scaling, overall curve translation, and define the cursor movement resolution; The background parameter isolation operation module is equipped with a data isolation mechanism, which ensures that the cursor line parameter only supports the cursor function in the background and will not appear in the variable selection list or analysis list, thus ensuring the purity of the front-end analysis interface.

2. The data analysis system based on Excel spreadsheets according to claim 1, characterized in that, After the data import and automatic parsing module obtains the parsed data, it automatically triggers the variable intelligent filtering module to generate a variable selection window. The variable selection window displays the parsed data and selection buttons, and the user selects at least one variable in the variable selection window.

3. The data analysis system based on Excel spreadsheets according to claim 2, characterized in that, The variable selection window is equipped with dual fault tolerance logic: when the user clicks Cancel, or clicks OK without selecting any variables, the data analysis system directly terminates the import process and does not generate any variable selection list or analysis list to avoid misleading the import of all data.

4. The data analysis system based on Excel spreadsheets according to claim 1, characterized in that, A multi-color cycle algorithm is used to assign independent colors to the different variable curves in the variable name column, and the color of the curve corresponding to the variable is the same as the color of the variable.

5. A data analysis system based on Excel spreadsheets according to claim 1, characterized in that, After selecting the checkbox corresponding to the variable, the fully automatic chart rendering module automatically creates an XY scatter line chart corresponding to that variable.

6. A data analysis system based on Excel spreadsheets according to claim 1, characterized in that, The dynamic variable appending function module has a built-in variable comparison algorithm that automatically identifies and skips existing duplicate variables, appends new variables only to the end of the original variable selection list, and simultaneously generates checkboxes, matches curve colors, and renders new curves for the new variables.

7. A data analysis system based on Excel spreadsheets according to claim 1, characterized in that, The coordinate axis scaling is specifically defined as follows: using the cursor as the center, the X-axis time axis can be directly scaled up or down by clicking the "X-axis zoom in" button or the "X-axis zoom out" button.

8. A data analysis system based on Excel spreadsheets according to claim 1, characterized in that, The overall translation of the curve is as follows: In the zoomed-in view, you can directly click the "Move Left" button or the "Move Right" button to move the curve left or right.

9. A data analysis system based on an Excel spreadsheet according to claim 1, characterized in that, The definition of cursor movement resolution specifically refers to adjusting the cursor movement step size as needed to make cursor dragging smoother and more precise.

Images