Selecting a table file for plotting Shewhart control charts, histograms, and scatter plots.

The software “Shewhart control charts PRO-Analyst +AI (for Windows, Mac, Linux)” allows you to import structured data from table files: XLSX; XLSB; ODS, for automatic generation of Shewhart control chart with histogram and scatter plot, Pareto charts , multivariate analysis And machine learning .

This function is included in the list of parameters saved in the chart properties when saved in the list for automatic chart updates with a selected timeout or to quickly open them with updated data.

XLSX files; XLSB; ODS can be located either on your computer, on the corporate network, or in cloud storage.

You can download example table files for plotting a Shewhart control chart with histogram and scatter plot: XLSX ; XLSB ; O.D.S.

Do not change the order of the data columns or change the column names, because The form of recording source data is already a standard for many enterprises, and not only those using our software. Tabs (sheets) with [XmRDatabase] and [Tolerance] data can be renamed. To import data into the software, you will need to fill out the tables on these tabs. The order of placing data on the [XmRDatabase] tab is from the oldest in the top row of the table to the newest in the bottom row, in chronological order of product release, namely release, and not the order of sample measurement .

Common mistake! Products received for inspection are transferred to a common pile, and inspectors select them according to the principle that it is convenient to take them first and make notes in the same order; the chronological order of product output is lost along with opportunities for improvement.

We recommend that when preparing data, you avoid using formulas in table file columns (not values). If you cannot do without formulas, the easiest way is to first import the table from the sheet with formulas into a new sheet using the Excel [Get Data] function in the [Data] menu and then import the data from the new sheet into our software. Before importing updated data, do not forget to [Update All] in the [Data] menu in the [Queries and Connections] button group.

If you use pre-organized data into subgroups to import data into the software, you will need to fill in the tables of another template file XLSX on the [XbarRDatabase] and [Tolerance] tabs.

The following office software products can be used to prepare data in tabular files: "R7-Office" ; "My office" ; "Yandex Documents" ; Google Sheets ; "Microsoft Office Excel" , "Microsoft 365" ; "LibreOffice" ; "OpenOffice" ; "WPS Office" - China, a very worthy free alternative to the Microsoft office suite; "FreeOffice" ; "Apple iWork"

You can import data even from open table files that are in editing mode. All saved data will be imported. This is done for the convenience of users, since table editors office software products more familiar to users.

Important! If you need to group data to build an XbarR-chart of subgroup means and ranges, you must have at least one factor column in the source data table. If you have no factor entries at all, then in the single factor column insert any string value, for example [Not Specified].

To automatically prepare data for import into our software, use the function converting your CSV, XLSX, JSON files .

Import data

Figure 1. Tabular file selection button for plotting control charts, histograms, and scatter plots. A tooltip appears when you hover your mouse over the [Import data from tabular data] button
Legend: UNPL(x) - Upper Natural Process Limit / Upper natural process limit; CL(x) - Center Line / Central line (process average); LNPL(x) - Lower Natural Process Limit / Lower natural process limit; LSL(x) - Lower Specification Limit; NSL(x) - Nominal Specification Line; USL(x) - Upper Specification Limit; UCL(mR) - Upper Control Limit(mR) / Upper control limit of moving ranges; CL(mR) - Center Line(mR) / Central line of moving ranges (average mR); Me(mR) - Median Line(mR) / Line of the median of moving ranges

Figure 2. File selection panel for plotting the Shewhart control chart, histogram and scatter plot. A tooltip appears when you hover the mouse over the [Select file] button. There is no checkmark in the [Select data from example] checkbox.

Figure 3. Window for selecting a table file (XLSX, XLSB, ODS) for constructing a Shewhart control chart, histogram and scatter plot.

Figure 4. A tooltip appears when you hover the mouse over the [Update graph] button to rebuild the graph based on the selected data. The result of constructing a Shewhart control chart, histogram and scatter plot using data from the selected table file.

Figure 5. When you check the [Select data from example] checkbox, the path fields to the data file and file sheet are filled in automatically. To update the graph using the data from the example, click the [Update graph] button. A tooltip is displayed when you hover the mouse over the Checkbox field [Select data from the SAMPLE DATA example]. In the field with graphs in the upper left corner there is the inscription SAMPLE DATA

You can always refer to the example data file and use it as a template for your data when constructing a control chart or for other types of data analysis. When you hover the mouse cursor over the field with the selected file, a tooltip opens with the path to the selected data file, and if the [Select data from example] checkbox is checked, this field will be filled in with the path to the example file SAMPLE DATA . After clicking the [Update graphs] button, the graph area will be updated with the data from the example.

Figure 6. A tooltip appears when you hover the mouse over the field with the path to the selected data file in the [Selected file:] block.

Selection of part (object) and controlled size (indicator)

We recommend maintaining in one data source file (*.xlsx) data on the Dimensions (characteristics) of homogeneous Parts (objects). For example, all Dimensions of one Part. This will allow you to quickly, without selecting a new source file, rebuild the control chart with a histogram and dot plot by selecting the Size (characteristic) of the required Part (object) in the corresponding control panel, see the pictures below.

The software provides for saving the settings of user-selected labels for the control chart points and displaying vertical dotted lines of actions of the selected type of factors, which will be automatically applied to all Parts (objects) and Dimensions (characteristics) when they change.

Figure 7. Button to go to the control panel for selecting Parts (object) and Size (characteristics) from the main program window.

Figure 8. Control panel for selecting Part (object) and Size (characteristics).

You can change the selected value in the Part (object) and Dimension (characteristics) fields, or use the mouse scroll wheel while hovering the mouse cursor over the corresponding field to select values ​​sequentially.

Figure 9. Control panel for selecting Part (object) and Size (characteristics). The drop-down list of Parts (objects) in the imported data file is open.

Figure 10. Control panel for selecting Part (object) and Size (characteristics). The drop-down list of Dimensions (characteristics) in the imported data file is open.

Figure 11. Control panel for selecting Parts (object) and Size (characteristics). After changing the values ​​in the Parts (object) and/or Size (characteristics) fields, the XmR control chart is automatically rebuilt based on these data.

If you maintain data for several [Parts (objects)] with the same names [Dimensions (characteristics)] in one file, then when you change [Parts (objects)] in the top drop-down list, the control chart will be rebuilt to the same name [Size (characteristics)] , selected for the previous [Part (object)].

Sales example:
You maintain data on [Details (objects)]: [Product-1, Product-2, Product-3] each product has the same list of [Sizes (characteristics)]: [Go to the product card, Added to cart, Placed an order, Paid order].

By selecting the [Added to cart] value for Product-1 in the [Size (characteristic)] drop-down list and changing the Products in the [Part (object)] drop-down list, the control chart will be automatically rebuilt for the [Added to cart] characteristic of the selected Product. This is convenient for quickly viewing control charts for the same characteristics of multiple Products.

Pre-grouped data

Analysis of all data imported to construct Shewhart control charts begins with the construction of an XmR-chart of individual values ​​and moving ranges. If the source data contains pre-grouped data for constructing an XbarR-chart of averages and ranges of subgroups, they are automatically ungrouped for the initial construction of an XmR-chart with the creation of new factor columns “No. Subgroup” and “No. (name) in the Subgroup”, see the figure below . After this, you can build an XbarR-chart of the averages and ranges of subgroups using the function rational grouping of data . Firstly, this decision corresponds to a strong recommendation Donald Wheeler start data analysis by constructing an XmR-chart of individual values ​​(process progress map), and secondly, this allows you to regroup the data according to any type of factors, records of which are present in the source data.

Figure 12. The original data is pre-grouped into subgroups of size (n-4) before the data is automatically ungrouped.

Figure 13. Automatically ungrouped data when imported into the software “Shewhart control charts PRO-Analyst +AI (for Windows, Mac, Linux).”

Video 1. Selecting a table file to build a control chart, histogram and scatter plot.