Home

Premium SQC for Excel

1. 1 4 Premium SQC for Excel User Manual f Adding Online SQC for Excel parameters Start and Stop Buttons Internal Trigger Reports and Saving The Premium SQC for Excel updates all worksheets selected in the Analysis dialog To ensure that the Premium SQC for Excel program does all calculations on time please follow these steps l From the SQC menu choose the Analysis option 2 Change the parameters to get the following setup SQC for Excel TM 97 Step 3 of 4 Analysis Eg Analysis J Histogram Distribution Properties T Auto Correlation Eo T Process Capabilities pca m Control Charts None Properties se C Attributes e gf Multi variable Nested Chart Advanced IV Runs and Trends F cusum Properties T Trend chart T Ewes 3 Click on the NEXT button 4 Select the worksheet TAB labeled Online MAE B cot cea oe _ a Start Bi Stop O External trigger 0 01 00 MLocal timer Time Stamp O Refresh external data Label Date Product Batch Tag 1 Tag 2 Chart name Out of control points Output3 Output4 Output5 Outputs 10 000 MPrint report every gt i Online Calculation Z Charts Auto correlation Capabilities Regi 5 Set the following Parameters Premium SQC Using External Database 15 Variab
2. 6 Time Stamp 103645 TRUE TT LastoflD ILastofl abel LastofDate LastofProduct LastofBatch Las 16 1 0 00 10 00 new 1 X Bar Range Chart C Save workbook every 10 000 20 4141P Pil Online Calculation Charts Auto correlation lt Capabilities R Step 2 Click the Start button Note that the SQC menu on the menu bar is now dimmed You need to click the Stop button to stop the Online SQC for Excel program After a minute you will see the program writes the time stamp in cell B5 and copies the data to all appropriate spreadsheets For example the CALCULATION worksheet after three minutes is shown 16 10109 10 00 new 16 10 100 10 00 new 16 100 10 00 new K W Date Ore J Calotstion Charts tuto corrmdation ff Capab e 24 Premium SQC for Excel User Manual Nested Charts Introduction This chapter describes how to construct nested SQC charts This allows the user to examine multiple sources of variations Suppose a process generates 15 batches of a product For each batch 2 samples are taken and the thickness of each sample is measured 2 times Using the standard SQC charting techniques described in the previous sections we can analyze the process using X Bar and S Charts All First SubGroup Last SubGrou 30 000 30 000 1 000 Limits are calculated using zone AB Std Average LCL UCL X bar Average LCL UCL 3 013 0 000 9 843 99 522 91 5
3. Tag 2 Number Tag 3 Number Tag 4 Number Tag5 Number Status Text Comment Text J uct Number E LCL Number x Field Properties General Lookup A field Field Size Long Integer name can New Values Increment be up to Format 64 Caption characters long Indexed Yes No Duplicates including on field names 20 Premium SQC for Excel User Manual How to set the Online Worksheet Once the database is setup and connected to the data gathering program i e collecting data at every minute you need to setup the ONLINE worksheet Please follow these steps Step 1 Select the ONLINE worksheet Step 2 From Data menu choose Get External Data gt Create New Query option Step 3 Choose MS Access 97 Database and click OK Step 4 Click on your data Sheet1 and click on the gt arrow to copy all fields across Query Wizard Choose Columns 2 x What columns of data do you want to include in your query Available tables and columns Columns in your query E lt ELE Preview of data in selected column Preview lt Back Cancel Step 5 Click on NEXT button until the following dialog Query Wizard Finish BEI gt What would you like to do next Retum Data to Microsoft Excel Save Query Step 7 Select the View Data and click on the Finish button Premium SQC Using External Database 2 1 a Microsoft Quer
4. 41 42 43 46 46 47 48 49 52 Introduction Before You Begin Complete and mail the registration card Make a back up of your original disk Do not use your original disk as your working copy If you happen to overwrite it you will have lost your Premium SQC for Excel program Keep your original disk locked Either copy it to your hard disk or make a working copy on another floppy The spreadsheets generated by the Premium SQC for Excel or SQC for Excel programs can only be used when a license for the Premium SQC for Excel or SQC for Excel program are purchased Please do not distribute the stand alone SQC worksheets generated by the SQC for Excel or Premium SQC for Excel without written permission of BaRaN Systems Ltd Microsoft Excel Version The SQC for Excel and Premium SQC for Excel are developed for Microsoft Excel 97 or version 8 or Microsoft Excel 2000 or version 9 Premium SQC Using External Database 5 Online SQC for Excel Introduction The Online SQC for Excel allows the users to connect their Excel SQC charts to the auxiliary online measurement The Access to your DCS Digital Computer Systems or any other computer should be functional before installing this program SQC for Excel Data Online Gathering Program Data Gathering Computer SQC Computer How the Program Works The Online program utilizes the following steps 1 Input Data Processing The
5. I 1 2 for all J 1 2 15 Measurement z Average O Measurement i J gt J 1 2 15 Sample Variance If Variance of the samples equals the average of the variances of the sample averages then Vs sample 2 O Measurement K where k is the number of measurements per sample O Measurement is the estimated variance of measurement 2 5 O sample is the estimated variance of sample Batch Variance The Batch to Batch variance is determined from the batch averages The following procedure is used to calculate batch to batch variance gan Vp Batch a Sample 2 n Measurement k n Vp is the variance of the batch averages k is the number of measurements per sample and n is the number of samples taken per batch 2 S Premium SQC for Excel User Manual Nested Control Chart Example Based on the statistical definition provided before we can construct control charts for batch process variability Melak Description _ Number of Measurements per sample Sub group size P Number of Samples per Batch _ Ne Total Number of Batches Calculation buffer size The SQC for Excel automatically generates the following calculations Step 1 Develop S charts based on all measurements Subgroup size Ns 2 k Number of subgroups I J 2 15 30 All Product Sampl B 5 E i 1 000 Limits are calculated using zone AB Average 3 013 3 013 LCL 0 00
6. You Begin 5 Microsoft Excel Version 5 Online SQC for Excel 6 Introduction 6 How the Program Works 6 1 Input Data Processing 6 2 History Buffer 7 3 Calculation Buffer 8 How to Setup Online Parameters 9 Quick Tour 10 Purpose of the Quick Tour 10 Files Required 10 Flow of Calculations 11 a Opening the initial data file 11 b Installing the SQC for Excel 12 c Loading the data to a working SQC spreadsheet 13 e Analyzing the data 14 f Adding Online SQC for Excel parameters 15 f Setting the external data generator 16 g Starting the Online SQC for Excel 17 h Checking the results 18 Online SQC Using External Database 19 Introduction 19 How to set the Access Database 20 How to set the Online Worksheet 21 How to Start the Online Program 24 Nested Charts 25 Introduction 25 Theory 26 Calculation 28 Nested Control Chart Example 29 How to Calculate 32 How to Analyze Nested Control Charts 34 How to Setup Nested Charts 35 Premium SQC for Excel 111 1 Prepare Original Data PCA Partial Component Analysis 35 39 Introduction Theory Data processing and scaling Example Data processing and scaling Similarity matrix calculation Calculation of Eigen vector and Eigen matrix Sorting of Eigen vector Loading Factors Score Factors M SQC How to Use PCA Program References 1V Premium SQC for Excel User Manual 39 40 40 40
7. data from B1 to F13 See Quick Tour chapter for more detail Make sure you click on Header row if you have selected the header in your data i e Row 1 SQC for Excel TM 97 Step 2 of 4 Load Data xi The Load Data dialog helps you to copy your data to the SQC worksheet m Direction of data Row Column C No header row m Paste data as Value C Link to value Header information Address type Absolute Reference Data type Initial Incremental Cancel lt Back next gt Finish Nested SQC Charts 49 Select Make sure you ONLY select PCA to speed up the operation PCA SQC for Excel TM 97 Step 3 of 4 Analysis xi Analysis J Histogram Distribution Properties I Auto Correlation T Regression I Process Capabilities M pca m Control Charts None Properties C variables C Attributes Multi variable Nested Chart gt Gustin Properties ft Je Enns Cancel lt Back r gt Einish PCA You can select 3 parameters for PCA Parameters 1 Number of Score Factors i e for the above example 2 Accumulation 3 SQC Charts Analysis Setup x Histogram Process Capabilities Auto Correlation Regression PCA Number of score columns Percent of confidence 5 0 Premium SQC for Excel User Manual Check the PCA chart Bi case_01 xls Score 1 pipca Setup Z Temp da
8. either online or from historical databases represent a process usually having different means and standard deviations This will yield some inconsistency in statistical evaluation of the data Assume a multivariable data set consists of m data points i e 5 classes of measurement and n measurements i e 12 in the above example e PCAT est xls iof x Pop s School a Emp Prof Ser House a MLA P pL PCATest Sheet2 Sheet3 f Sheet4 4 SheetS However if one calculates the Data Mean Standard Deviation as the normalized measurement then each class of data has the same effect on the overall analysis of the data Nested SQC Charts 4 1 Similarity matrix calculation The degree of relationship existing between three or more variables is called multiple correlation The fundamental principles involved in the problems of multiple correlation are analogous to those of single input single output or simple correlation In case of a simple correlation the relationship between two variables x and y is investigated The variables x and y as shown below seem to lie near a line hence the correlation is called linear case_01 xls Iof x Chart Title y 0 3509x 143 28 2000 4000 6000 8000 10000 12000 f Online Calculation The linear relationship between variables x and y can be described as Y SLOPE X INTERC
9. the previous section the data was organized as e 2 Measurements per Sample e 2 Samples per Batch e 15 Batches However it is possible to have access to data with more samples or sub groups per Batch The following example provides a example of how to use MS Excel to collect the data Assume that you have collected e 10 measurements per Sample e 10 samples per Batch e 5 Batches However you are interested in analyzing only 5 Sample per batch instead of 10 The following steps will provide you with a detailed procedure to do this Step 1 Open the spreadsheet with data S Trem DO om wo woo oana oworowono omet NNPOoOOAnoaon O o of wn oom uD A w N e waup OONO NNO NANOOD N 7 1 1 1 1 1 1 1 1 1 1 2 2 2 5 5 JPNS OD nHopnvwmonoaowdan an E Nested SQC Charts 3 5 Step 2 From the Data menu choose Filter and then AutoFilter Filter AutoFilter STIG Fl Step 3 In the cell labeled Sample Number click on the down arrow and then select Custom option Step 4 Click on the selection arrow and choose the Less than and Equal to option Custom AutoFilter Custom AutoFilter s less than or equal to 3 6 Premium SQC for Excel User Manual Step 5 all data with the Sample Number of less than or equal 5 are selected Bi data xls eee ean 1 2 3 4 5 1 2 3 4 5 1 2
10. 0 96 215 79 960 96 690 92 185 114 210 97 285 83 120 94 210 93 560 104 620 99 030 94 200 100 690 103 935 117 050 61 595 90 200 106 345 100 015 101 645 99 960 3 0 Premium SQC for Excel User Manual Note that the S 1 and S 2 for the first record are the averages of the first and second records from the previous table The table size is Ysize of the original table Nested Charts eae Bo jan J a es ee es First SubGroup 1 000 1 000 1 000 Limits are calculated using zone AB 14 890 14890 14 690 13 56 432 56 432 58 432 53 Var s Vs Var m K 37 563 37 563 37 563 34 Var b Vp Var s K Var s il K 110 885 110 885 110 885 Vartt Var m Var s Var b 65 676 65 6768 65 876 70 501 70 501 70 501 Vp Var b Var si k Var s k SORT s 8117 8117 8117 Sigma S SQRT Vs LCL g 000 0 000 0 000 Sigma S 0 000 B3 UCL 26 517 26 517 26 517 Sigma S 3 267 B4 8 397 8397 8397 Sigma SORT Vp 99 522 99 522 77 195 77 195 77 195 X_Bar_Ave Sigma X 2 659 A3 121 848 121 848 121 848 X Bar Ave Sigma x Nested SQC Charts 3 1 How to Calculate T 2 Calculate the Var m Average of all Variances in the Table 1 Calculate the Average and Variance for table Label S 1 __S 2 Var Average 9 10 11 12 94 850 216 196 105 295 122 535 39 739 118 078 98 080 i 4500 99 580 99 700 39 561 95 253 102 520 19 8577 99 368 79 960 139
11. 0 0 000 Std_Ave 0 000 B3 UCL 9 843 9 843 Std_Ave 3 267 B4 FEW WNN Average 99 522 99 522 99 522 LCL 91 511 91 511 91 511 X_BaStd_Ave Std_Ave 2 659 A3 UCL 107 533 107 533 107 533 X_BaStd Ave Std Ave 2 659 A3 Average 99 522 99 522 99 522 Sigma 3 776 3 776 3 776 Std_Ave 0 798 C4 LCL 91 970 91 970 91 970 X_Ave 2 Sigma UCL 107 073 _107 073 _107 073 X_Ave 2 Sigma Step 2 Construct nested S charts based on the averages of each batch This is similar to having the X Bar and S charts constructed for each batch average Nested SQC Charts 29 X_Bar Chart 110 4 d R UCL X Bar z S o n r o KR r 9 Q A fF 2 fF F N amp A N A Sample Number LCL 91 511 Ave 99 522 UCL 107 533 Std Chart Sample Number LCL 0 000 Ave 3 013 UCL 9 843 Step 3 Calculate the Sample Average and Variance for each sample 2 measurements per sample X bar Var 92 320 97 380 94 850 12 802 120 120 111 360 115 740 38 369 123 210 121 860 122 535 0 911 111 580 115 660 113 620 8 323 99 430 96 730 98 080 3 645 102 260 99 900 101 080 2 785 100 730 98 670 99 700 2 122 89 900 91 710 90 805 1 638 101 430 103 610 102 520 2 376 94 470 97 960 96 215 6 090 Step 4 Construct a new table based on Samples per Batch E Sea as Label S 1 _S 2 94 850 115 740 122 535 113 620 98 080 101 080 99 700 90 605 102 52
12. 11 107 533 3 013 0 000 9 843 99 522 91 511 107 533 99 522 91 511 107 533 Std_Ave 0 000 B3 Std_Ave 3 267 B4 X_BaStd_Ave Std_Ave 2 659 A3 X_BaStd_Ave Std_Ave 2 659 A3 Average Sigma LCL UCL 99 522 3 776 91 970 107 073 99 522 3 776 91 970 107 073 99 522 3 776 91 970 107 073 Std_Ave 0 798 C4 X_Ave 2 Sigma x Ave 2 Sigma Analyzing the above data yields the following X Bar and S charts X_Bar Chart X Bar gt o fl UCL Ave LCL o o irs D LCL 91 511 Ave 99 522 UCL 107 533 70 E S S E S S SA SS S S n 0 D Sample Number e ire N D A A A A 12 Std Chart Std on AOD n 4 j t T pe Sample Number LCL 0 000 Ave 3 013 UCL 9 843 Nested SQC Charts 25 Theory Considering the above charts the X Bar chart is severely out of control In interpreting these charts one should realize that the control limits in the X Bar chart were calculated on the basis of the standard deviations within the subgroup on the basis of the within sample variability The subsequent subgroups however are not only subject to within sample variability but also to between sample to sample and batch to batch variability What are the sources of variation in a product response There are three major sources of variation in a process 1 Measurement variability 2 Samp
13. 12 CALCULATION Worksheet 01 10 32 12 12 14 12 02 10 33 13 12 11 11 03 10 34 12 11 1415 14 10 45 13 12 11 12 15 10 46 14 14 10 12 CALCULATION Worksheet How to Setup Online Parameters The Online Parameters are Variable a B Start Bi Stop Description Value These buttons start and stop the Premium SQC for Excel program The content of the cell B3 is changed externally the cell B4 selected The data to be used by Premium SQC for Excel The data to be sent to the External source This option allows the user to print a report and save the data to a disk External trigger C Local timer Time Stamp 0 01 00 C Refresh external data Label Date Product Batch Tag 1 Tag 2 1 Chart name Out of control points Output2 Output3 Output4 Output5 Outputs C Print report every 10 000 Cl Save workbook every 10 000 4 gt Ml Online Calculation Charts Auto correlation Capabilities Regi 4 Niuarisee of decimal place 4 Mokry ae Fork Nane trial See EJE Premium SQC Using External Database 9 Quick Tour Purpose of the Quick Tour The purpose of the quick tour is to provide the user with a quick overview of the functionality of SQC for Excel by stepping through an example problem By so doing the user will become familiar with the basic steps involved in doing an a
14. 3 1 IoOPanomanonononne i n omolon A aA A OO A O gt THaAnannpoaowanNnNnnNonn Como mianaomomonmonnonw Step 6 Highlight the data and choose New from SQC Menu Ss Themis OF x SG ae Pe e Se ee a eae ae x Y i ile Af SS 525 5 88 8 3 5 e s 5 9 o o a a v x J I4 bit Step 7 Check the results Make sure you select NESTED CHART Nested SQC Charts 37 Sample Number LCL 6948 008 Ave 7005 720 UCL 7063 432 Sample Number LCL 16 810 Ave 59 192 UCL 101 573 D E E X_Bar Chart Nested Nested x_Bar Batch Number LCL 6977 514 Ave 7003 583 UCL 7029 652 Std Nested Batch Number LCL 17 793 Ave 62 653 UCL 107 513 33323 8 213 ils 5 S S FS S S 8 8 9 8 B 2 88 S 8 B 57 lo m ztemz Ltd KNIK MLS T Calculation _ Charts Auto correlation 4 3 8 Premium SQC for Excel User Manual PCA Partial Component Analysis Introduction Statistical process control SPC is being implemented across the industry without consideration to the multivariable nature of the measurements The objective of this chapter is to provide basic statistical theory and application of the Principal Component Analysis PCA technique to evaluate and understand process unit data The theory is presented from the user s point of view and more detail of math
15. 365 13 464 13 052 Peed ep a 4 Check that the values in Data row in the ONLINE XLS file is changing g Starting the Online SQC for Excel Once the external data is being forwarded to the Online XLS you can start the Premium SQC for Excel program with the following steps 1 From SQC menu choose Report and set it as shown below sac for Excel TM 97 Step 4 of 4 Report BER u u u u ugi AATA 749 4 AAAA 090 4 J 100 EJ 100 f 100 J 00 fo J 00 EJ f 100 Premium SQC Using External Database 1 7 2 In the Online worksheet click on the Start button Note You need to click the STOP button to stop the online program In case of emergency press the ESCAPE key and then click on the END button The ESCAPE key will make the program terminate incorrectly and you will need to start over again h Checking the results The SQC for Excel program automatically updates all relevant calculations and charts in the Online spreadsheet Hi online xls olx N m wo Sample Number LCL 12 177 Ave 12 990 UCL 13 803 Sample Number LCL 12 240 Ave 13 037 UCL 13 834 Sample Number LCL 12 311 Ave 13 090 UCL 13 868 gt pif Calculation Charts Auto correlation Capabilities 1 8 Premium SQC for Excel User Manual Online SQC Using Exte
16. 946 88 325 92 185 242 550 103 198 97 205 100 524 90 203 94 210 0 211 93 685 104 620 15 624 101 625 94 200 21 060 97 445 103 935 66 002 110 493 61 395 36 764 85 796 106 345 20 034 103 180 101 845 1 77 100 903 Calculate the Vs or the Average of all Variances in the Table 2 Calculate the Vp or the Variance of all Averages in the Table 2 Calculate the Var s Vs Var m K where k is 2 Calculate the Var b Vp Var s K Var s J K Where K and J are 2 and 2 respectively Calculate Var t Var m Var s Var b and the percentage of each Var out of total variance Var t Calculate the Sigma for control charts as Sigma SQRT Vs Calculate the S Chart Lower Control Limit LCL Sigma 0 000 B3 Calculate the S Chart Upper Control Limit LCL Sigma 3 267 B4 Calculate the overall average as Average of all Averages in the Table 2 Calculate the Nested Chart Upper Control Limit 3 2 Premium SQC for Excel User Manual UCL X_Bar_Ave Sigma X 2 659 A3 13 Calculate the Nested Chart Lower Control Limit LCL X_Bar_Ave Sigma X 2 659 A3 14 Plot the charts X_Bar Chart Nested 130 120 T 110 9 Z 100 3 90 x 80 70 N m wo o N o N sp vt wo Batch Number LCL 77 195 Ave 99 522 UCL 121 848 Std Chart Nested 30 62 eee re Std UCL g 20 1 D ToL A ee ee eee Aen nen eee eee ne Ee ETE S
17. EPT after estimation of SLOPE and INTERCEPT using least square regression the goodness of the regression is defined as R SQRT explained variation Total variation R SQRT pee E Yave A Ly Y ave f 4 Premium SQC for Excel User Manual The quantity R called the coefficient of correlation varies between 1 and 1 In a multivariable case the coefficients of correlation for the m variables needs to be calculated This is called the similarity matrix or correlation matrix R Defining Xi SLOPE X INTERCEPT R SQRT explained variation Total variation R SQRT LX jest Xiave gt LX Xiave 7 and Rui Rip gt R13 pee Rim R R21 R22 gt R23 eee Rom R31 5 R32 gt R33 yaar Rm Giaa a aaa gc Rai E Rar Rez y Rarene Ran and in the case of the multivariable system m 5 R can be represented as School Prof Ser House Pop School Emp Prof Ser House Calculation of Eigen vector and Eigen matrix Nested SQC Charts 43 The correlation matrix represents the correlation of the variables with respect to each other The PCA technique is based on using the correlation matrix to calculate a new set of linear coefficients called Loading Factors These coefficients will be used in condensing the original data set of m variables by n observations to a manageable k variables by n observations Original New Data Data se di Where k is usually much less than m Hence PCA ca
18. User s Guide Premium SQC for Excel Version 3 02f 1991 2001 BaRaN Systems Ltd Information in this document is subject to change without notice No part of this document may be reproduced or transmitted in any form or by any means electronic or mechanical for any purpose without the express written permission of BaRaN Systems Ltd 1991 2001 BaRaN Systems Ltd Disclaimer Microsoft and Excel are registered trademarks of Microsoft Cooperation SQC for Excel and Premium SQC for Excel are provided as is without warranty To the best of our knowledge the statistical calculations provided in the package are accurate However this validity is not guaranteed The user is responsible for decisions made based on the results generated by the package The spreadsheets generated by the SQC for Excel or Premium SQC for Excel program can only be used when a license for the SQC for Excel or Premium SQC for Excel programs are purchased Please do not distribute the stand alone SQC worksheets generated by the SQC for Excel or Premium SQC for Excel without written permission of BaRaN Systems Ltd BaRaN Systems BSL 458 Plantation Dr PMB 100 Lake Jackson TX 77566 Phone 403 449 6554 Web www baran systems com Document No Sfx_Premium_101 doc 11 Premium SQC for Excel User Manual Contents Introduction 5 Before
19. ematical derivations can be found in the references The technology is mainly being introduced to the chemical industry by John MacGregor of McMaster University The statistical process control SPC and statistical quality control SQC foundations are based upon the detection of a statistically significant change in a given process When a process can be described by a single measurement such as making an object with a desired length implementation of these techniques becomes an easy task However in the majority of the industries a given process such as a reactor can not be described completely by a single measurement Implementation of SQC and SPC technology will yield hundreds of control charts which will identify some causes but not the status of the process The multivariable nature of processes makes it difficult to provide the unit operator with the information with respect to the health of the process based on SQC and SPC technology due to the number of charts generated The PCA technique presented in this chapter will reduce a large multivariable system i e 10 tags with 200 measurements to a manageable system i e 2 tags with 200 measurement while preserving most of the information in the original system Nested SQC Charts 3 9 Theory Principal Component Analysis PCA is a mathematical technique which examines a multivariable data set and determines the underlying associative patterns based on variance Process compute
20. f significance Assume a data based with 5 measurements and the following Eigen vector Nested SQC Charts 45 1 2 3 4 5 Sorting of Eigen vector The order of the Eigen Vector is 5 1 2 3 and 4 therefore the 5th element is the most significant one By sorting the Eigen vector in a descending order 5 1 2 3 4 Pf 30 0 58 4 804 4 94 0 100 0 Hence by using only 5 1 and 2 one can utilize 80 44 of information within the original data set and the system size has been reduced form 5 to 3 Loading Factors The program calculates the following Loading Factor Matrix Factor 1 Factor 2 Factor 3 Pop 0 581 0 806 0 028 Se eS Se 46 Premium SQC for Excel User Manual Score Factors The program calculates the following Score Factors Score 1 Score 2 Score 3 SH case_01_xls olx This chart represents the Score2 and Score3 plotted against Scorel Please observe that the data is normalized with the mean centering and standard deviation scaling Hence any point outside 3 sigma is out of control Nested SQC Charts 47 M SQC Based on the Scored data the Norm of the data i e the dissonance from the center is plotted as individual X chart lt i case_01_xls x SQRT S fori 1 2 and 3 48 Premium SQC for Excel User Manual How to Use PCA Program Do the following steps to develop the PCA charts Step Select Data Choose PCA worksheet and select
21. flD Last Sheet Label as LastofLabel Sheet Date as LastofDate Cancel i Sheet Product as LastofProduct Last Sheet Batch as LastofBatch Sheet Tag 1 as LastofTag1 Sheet Tag 2 as LastofTag2 Last Sheet Tag 3 as LastofTag3 Last Sheetl Tag 4 as LastofTag4 Last Sheet Tag 5 as LastofTag5 x 22 Premium SQC for Excel User Manual Last Sheet Tag 1 as LastofTag1 Last Sheet Tag 2 as LastofTag2 Last Sheet Tag 3 as LastofTag3 Last Sheet Tag 4 as LastofTag4 Last Sheet Tag 5 as LastofTag5 Last Sheet Status as LastofStatus Last Sheet Comment as LastofComment Last Sheet UCL as LastofUCL Last Sheet LCL as LastofLCL FROM c My Documents db3 Sheet Sheet Step 10 Click on the following dialog Microsoft Query Note After this dialog you should not have any errors However if you get one check your SQL commands Step 11 From File Menu choose Close and Return Data to Excel Step 12 Enter Cell C8 as the data entry Returning External Data to Microsoft Excel Premium SQC Using External Database 23 How to Start the Online Program Once the external database is connected to ONLINE worksheet please do the following steps Step 1 Make sure you clicked the a Local time check box b Refresh External data c Entered the sample time of 0 0 1 lt i Book2 1 2 3 4 C External trigger
22. for a continuous polybutadiene polymerization train In Proceedings of the 2nd IFAC Symposium on Dynamics and Control of Chemical Reactors Distillation Columns and Batch Processes Pergamon Press 1989 5 2 Premium SQC for Excel User Manual Online SQC for Excel 6 I Online SQC Using External Database 19 Introduction 5 P Before You Begin 5 Microsofta Excel Version 5 PCA Partial Component Analysis 39 N Q Nested Charts 25 Quick Tour 10 O R Online Parameters 9 References 52 Nested SQC Charts 5 3
23. hange this setting by choosing Setup from SQC menu The new data is copied from the ONLINE worksheet and is placed at the end of the history buffer until the buffer size reaches the Nh If the number of records in the history buffer exceed the Nh the program automatically scrolls the buffer and deletes the first record or the oldest record Record 1 Record 2 Record 3 Record 2 Record 3 Record 4 Record 3 Record 4 Record 5 Record 4 Record 5 Record 6 Record 1000 Record 1001 Record 1002 Record 1001 Record 1002 Record 1003 Record 999 Record 1000 Premium SQC Using External Database 7 3 Calculation Buffer The SQC for Excel has an internal calculation buffer Nc This is the number of RECORDS or number of points in the SQC chart You set this number by selecting the initial SCQ data For example the Quick Tour Example had 15 sub groups or 15 records The Online SQC for Excel program will connect the CALCULATION worksheet data buffer to the newest data in the history buffer of the DATA worksheet Ts 1 0 Min 10 19 18 17 11 11 12 14 12 12 14 12 13 12 1111 10 45 13 12 11 12 14 13 12 11 13 1211 12 ONLINE worksheet DATA Worksheet 11111214 12 12 14 12 10 46 14 14 10 12 13 12 11 11 14131211 13 12 11 12 14 14 10 12 ONLINE worksheet DATA Worksheet S Premium SQC for Excel User Manual 01 10 31 11 11 12 14 02 10 32 12 12 14 12 03 10 33 13 12 11 11 14 10 44 14 13 12 11 15 10 45 13 12 11
24. lculates a new set of k variables called Scored Factors based on the original data e Loading Factors Data 44 Premium SQC for Excel User Manual There are many different algorithms to calculate the Factor Loading matrix The algorithm used here to calculate the Factor Loading matrix uses the Eigen vector e and Eigen value matrix E of the correlation matrix R calculated by the Jaccobian decomposition method In other words by calculating the Eigen vector of the correlation matrix one will evaluate the importance or the weighting of each variable For example assume an Eigen vector calculated and sorted for a data set with 5 measurements is Value 1 Value 2 Value 3 Value 4 Value 5 Difference 1 077 1 582 0115 0 085 The amount of information on each Eigen vector element can be evaluated by calculation of the Percentage of Information or Proportion Proportion e L e fori 1 5 which is Value 1 Value 2 Value 3 Value 4 Value 5 Difference 1 077 1 582 0115 0085 This means the first measurement can represent about 57 5 of the information and the combination of the first and second measurement will represent 93 4 or 57 5 35 9 of the data In the above example the order of significance relative weighting of the Eigen Vector and the data order is the same the first Eigen value is the largest one and so on However one needs to sort the Eigen vector in descending order o
25. le Description Value Start and Stop These buttons start and stop the Do not use now Button Premium SQC for Excel program External The content of the cell Select Internal trigger and B3 is changed externally enter 00 01 00 for 1 Internal The program works at minute in cell B4 frequency entered in cell B4 Data The data to be used by Enter Now in cell B8 Premium SQC for Excel Output The data to be sent to the Enter the UCL and LCL External source from the calculation worksheet B13 gt Calculation C12 B14 gt Calculation C11 Reports and Saving This option allows the user to Select both options print a report and save the data to a disk 6 Save the Spreadsheet as ONLINE XLS f Setting the external data generator Do the following steps to start the SQC_DDE 1 Double click on SFX_DDE EXE file in the SQC folder a SFX_DDE ox This program will produce random numbers in given range Spre adsheet name work book must be saved before running this program Workbook name Dpline xls Worksheet Name Worksheet name Online Cell number R27 fg fe Cell reference No of Col 5 Min and Max j3 E Send each miisec f60000 eae Start sending data Min and Max Number of columns 2 Set the parameters as shown above Frequency 3 Check mark the Start Sending data 1 6 Premium SQC for Excel User Manual 4 Product Baten fragt Tag2 Tag3 Tag4 Tag5 Status Comment 9 T 1
26. ling variability within batch 3 Batch to batch variability Each of the three sources of variability adds variation or error to the results The sample pulled from the batch and tested yields to E X Xba Average Where E is the error X is the measurement and Xovar Average is the population average The overall error has three components corresponding to three sources of variations E E Batch E Sample E Measurement Where E Batch is the error caused by batch to batch variability E sample is the error caused by batch to sampling variability and E Measurement is the error caused by batch to measurement variability 26 Premium SQC for Excel User Manual By definition these errors have zero averages and can be assumed to be represented as normal distributions with fixed variances The standard deviation O calculated from the data represents an estimate of the combination of these error components 2 2 2 2 O O patch O Sample O Measurement where o is the total estimated variable O Batch 2 is the batch to batch estimated variance Ji 3 O sample is the sample estimated variance Os O Measurement 1S the measurement variance Nested SQC Charts 27 Calculation The following variances are calculated Measurement Variance z Da The estimated variance of measurement O Measurement 1S calculated as the average of all measurement variances Measurement J Variance X 1
27. nalysis and will readily acquire a feel for the tool The example involves analyzing data from a manufacturing process Five observations a day have been made over a fifteen day period and tabulated in the following table Day Nol No2 No3 No4 No5 Ps ss 2s so fso foi Ps isa i722 9 12s o fao fisz fia fiso fiso The task is to develop online SQC charts for this data It assumes that the external program will generate 5 data points every minute Files Required There is one file required to run this example 1 Process_Data worksheet in the Cases xls file 1 0 Premium SQC for Excel User Manual Flow of Calculations There are several steps in transforming a set of raw data points from an Excel worksheet into a set of charts or reports These steps include a Opening the initial data file b Installing the SQC for Excel program see installation chapter c Loading the data to a working SQC spreadsheet d Analyzing the data e Adding Premium SQC for Excel parameters f Setting the external data generator g Starting the Premium SQC for Excel h Checking the results Once the data has been selected and the load data option has been selected flow of calculations will automatically proceed to the plotting phase The report option needs to be specifically selected to generate printed output Once a set of calculations has been completed the user is able to restart the sequence at any point a Opening
28. on the Browse button and look at Program Files SQC for Excel folder 1 2 Premium SQC for Excel User Manual c Loading the data to a working SQC spreadsheet Only data that has been selected on the raw data worksheet will be analyzed To select data on the raw data worksheet use the mouse or keypad to highlight the area of cells which represents the data of interest In the following example the whole data set representing fifteen days of observations is selected Select data C11 to G25 1 From Window menu choose file Cases xls 2 Select the Process_Data worksheet 3 Select the first data point i e click on cell C11 4 Hold the mouse button down and drag to the last data point i e cell G25 or click on the Select Data Button Operator Log of Process Readings Select Data Shift Readings 10 00 14 00 22 00 1 2 3 4 5 4 6 3 Fi 8 G G io IN Rw Bio Premium SQC Using External Database 1 3 e Analyzing the data Follow the instruction in the Quick Tour Chapter in the SQC for Excel User manual X Bar 14 13 5 13 amp 12 5 xX 42 11 5 L 11 H H H H H H N oO to o N D E N oO Tt wo Sample Number LCL 12 089 Ave 12 939 UCL 13 789 Range Chart 4 3 UCL o 22 oO oc Ave x HR 1 L A Ny Se a Y 0 t t t t t t t t t t t t t LCL N M lt iro o N D o N oO Tt wo Sample Number LCL 0 000 Ave 1 473 UCL 3 115
29. program runs on an internal timer set by the user At a fixed sampling time Ts The program runs to update the history buffer and all appropriate calculations and reports The program assumes that at each sample time Ts the data fields are updated by an auxiliary program The data fields can be set to collect data directly with programs such as aGlance In this case the user connects the data fields to the auxiliary program manually The Data can be collected automatically with an External Data Gathering Program see the last section of this chapter 6 Premium SQC for Excel User Manual 2 History Buffer When the online option is activated at each sample time of Ts the program will copy the data fields from the ONLINE worksheet to the history buffer in the DATA worksheet Ts 1 0 Min 10 30 10 19 18 17 10 31 111112 14 10 32 12 12 14 12 10 33 13 12 11 11 10 45 13 12 11 12 10 44 14 13 12 11 10 45 13 12 11 12 ONLINE worksheet DATA Worksheet For example at 10 45 a new sample arrives and the program copies it to the history buffer Ts 1 0 Min 10 30 10 19 18 17 10 31 11 11 12 14 10 32 12 12 14 12 10 33 13 12 11 11 10 46 14 14 10 12 10 44 14 13 12 11 10 45 13 12 11 12 10 46 14 14 10 12 ONLINE worksheet DATA Worksheet At 10 46 the program executes again and copies the 10 46 data to the history buffer The maximum number of records in the history buffer Nh is set to 1000 records Then the user can c
30. rnal Database Introduction The Online functionality of SQC for Excel can be used with a variety of external databases The supported databases are listed below Choose Data Source 2 x Databases Queries OK lt New Data Source gt dBASE Files not sharable Excel Files not sharable FoxPro Files not sharable Cancel Browse Options adui Paradox Files not sharable Text Files not sharable Once you have your data updated in one of the above databases you can connect it to the SQC for Excel for online observation The following example provides the information and step by step instructions on how to setup the connections between SQC for Excel and Microsoft Access 97 You can use this example to set connections with other databases Premium SQC Using External Database 1 9 How to set the Access Database The SQC for Excel data input should be EXACTLY the way the data is arranged in the DATA worksheet for your SQC charts The data worksheet for the Online Quick tour is constructed as Label Date Product Batch Tag5 Status Comment Therefore you need to create an Access database with exactly the same fields and connect your Access database to the data gathering program amp Sheet Table olx FieldName DataType Description DI AutoNumber Label Number _ Date Date Time Product Text Batch Number Tagi Number
31. rs routinely collect thousands and thousands of data from multitude of plant sensors every second This data overload will cause the operators to ignore the majority of the received information and concentrated on few measurements However statistical method such as PCA are capable of compressing the information down to a manageable size by retaining most of the information Data processing and scaling The multivariable data gathered either online or from historical data bases to represent a process usually have different mean and standard deviations This will yield some inconsistency in statistical evaluation of the data The implementation of PCA is achieved by Data processing and scaling Calculation of Similarity matrix Calculation of Eigen vector and Eigen matrix Sorting of Eigen vector Loading Factors Score Factors Oe RS which are described in the following sections Example Assume a simple database of 5 variables m 5 and 12 observations n 12 The objective is to condense the data to a single variable k 3 with same number of observations n 12 The variables are 1 Population 2 School 3 Employment 4 Professional Services 5 House with the following observations 40 Premium SQC for Excel User Manual Pop School Emp Prof Ser House tooo tO OF 10000 8200 8 600 12000 00 S 8300 14000 9000 GH 8300 8 12000 Data processing and scaling The multivariable data gathered
32. ta SQC Chart B TO 11 12 Sample gt lpi pca Setup Temp data 1s gt Nested SQC Charts 5 1 References The following is a list of references that provide more information on concepts and terminology used in this paper i 2 A Hoskuldsson PLS regression methods Journal of Chemometrics 2 211 22S 1988 Arthur Jutan J F MacGregor and J D Wright Multivariate computer control of a butane hydrogenolysis reactor part ii Data collection parameter estimation and stochastic disturbance identification AIChE Journal 23 5 742 750 September 1977 J C Liao Fermentation data analysis and state estimation in the presence of incomplete mass balance Biotechnology and Bioengineering 33 613 622 1989 John F MacGregor Thomas E Marlin James Kresta and Bert Skagerberg Multivariate statistical methods in process analysis and control In CPC IV 1991 Rolf Manne Analysis of two partial least squares algorithms for multivariate calibration Chemometrics and Intelligent Laboratory Systems 2 187 197 1987 G Musumarra G Scarlata G Romano and S Clementi Identification of drugs by principal component analysis of rf data obtained by TLC in different effluent systems Journal of Analytical Toxicology 7 286 292 1983 J J Roffel J F MacGregor and T W Hoffman The design and implementation of a multivariable controller
33. td Ave 0 I sd eL baaa N m lt wo o N o N sp to Batch Number LCL 0 000 Ave 8 117 UCL 26 517 Nested SQC Charts 3 3 How to Analyze Nested Control Charts 1 Establish control limits for the nested control charts 2 Use the first S chart to monitor within the sample variability Out of control situations have to be interpreted as uniformity problems Note do not analyze X Bar charts 3 Use the nested S chart to monitor the posted standard deviation Var b When out of control first check the S chart If this shows no out of control then there is a between sample problem 4 Finally use individual Nested Control charts to monitor the Var b as well as the trend of the run averages In the case of out of control check within the sample and between the sample charts for out of control IF the latter is OK then a batch problem exists The material in this chapter is based on the following references e Rene Penning De Vnes Control Charts in IC Manufacturing e W H McNeese and R A Klein Statistical Methods for the Process Industries ASQC Quality Press 1991 34 Premium SQC for Excel User Manual How to Setup Nested Charts The nested charts are calculated for you automatically with the Premium version of SQC for Excel Please do the following steps 1 Prepare Original Data The data should be prepared as Batch Sample and Measurement For example in
34. the initial data file The SQC for Excel can be loaded into the Microsoft Excel using the following procedure 1 Start Microsoft Excel 2 From File menu choose Open and select Cases xls file in the Program Files gt SQC for Excel 97 folder FY Exploring Program Files oO x File Edit View Tools Help amp Program Files x i sale a x All Folders Contents of Program Files H E Program Files Projects C Paint Shop Pro File Folder 3 30 97 aA Qgate Plus File Folder 5 23 96 9 Raplayer SQC for Excel 97 File Folder 9 2 97 9 7 Recycled E The Microsoft Network File Folder 8 30 97 Lol 9 Rfire File Folder 5 6 97 6 x b 1 object s selected Vi 3 Click OK Premium SQC Using External Database 1 1 b Installing the SQC for Excel 1 Check to see if you have SQC on the Excel menu bar if not please do the following a From Tools menu choose Add in Add Ins RAkS Add Ins available Euro Currency Tools T Internet Assistant VBA T Lookup Wizard 7 ODBC Add in T Rea For Excel 2000 Browse I Report Manager IV Sfx_Main IV Small Business Financial Manager 7 Solver Add in x Affinity Diagram For Excel 2000 b Make sure that the SFX_Main add in is checked c Click OK 1 Note If SFX_Main SQC for Excel is not shown in the add in list please follow the directions given in the installation chapter or add it in by clicking
35. y iofjx Eile Edit Yiew Format Table Criteria Records Window Help ceme seu eE E e e eE pele w Query 1 from MS Access 97 Database not sharable joj x LastOflD LastOfDate LastOfTag1 LastOfTag2 LastOfTag3 1899 12 30 10 00 00 11 0 12 0 13 0 Step 8 Write the following SQL commands in a word processor or Excel and copy them to the local buffer SELECT DISTINCTROW Last Sheet1 ID as LastofID Last Sheet1 Label as LastofLabel Last Sheet1 Date as LastofDate Last Sheet1 Product as LastofProduct Last Sheet1 Batch as LastofBatch Last Sheet1 Tag 1 as LastofTag1 Last Sheet1 Tag 2 as LastofTag2 Last Sheet1 Tag 3 as LastofTag3 Last Sheet1 Tag 4 as LastofTag4 Last Sheet1 Tag 5 as LastofTag5 Last Sheet1 Status as LastofStatus Last Sheet1 Comment as LastofComment Last Sheet1 UCL as LastofUCL Last Sheet1 LCL as LastofLCL FROM c My Documents db3 Sheet1 Sheet Note that all of the fields are shown here The Tag 1 is placed in a single apostrophe to preserve the space The address of the database should be the address of your database Step 9 Click on the SQL button on the QUERY tool bar or choose SQL from View menu Paste the above commands instead of the one that exists there The above SQL will update the LAST record of the database for SQC for Excel access Click the OK button SQL 2 x SAL statement SELECT DISTINCTROW Last Sheet ID as Lasto

Premium SQC for Excel

Contents

Download Pdf Manuals

Related Search

Related Contents