应用EXCEL实施质量统计控制 英文版_36页.pdf

STATISTICALQUALITYCONTROLUSING EXCELSecond EditionAlso available from ASQ Quality Press:The Desk Reference of Statistical Quality MethodsMark L.CrossleyImproving Performance through Statistical ThinkingASQ Statistics DivisionThe Handbook of Applied Acceptance Sampling:Plans,Procedures,and PrinciplesKenneth S.StephensGlossary and Tables for Statistical Quality Control,Third EditionASQ Statistics DivisionInnovative Control Charting:Practical SPC Solutions for Todays Manufacturing EnvironmentStephen A.Wise and Douglas C.FairSPC Essentials and Productivity Improvement:A Manufacturing ApproachWilliam A.Levinson and Frank TumbeltyThe Uncertainty of Measurements:Physical and Chemical Metrology and AnalysisS.K.KimothyZero Acceptance Number Sampling Plans,Fourth EditionNicholas L.SquegliaStatistical Process Control Methods for Long and Short Runs,Second EditionGary K.GriffithTo request a complimentary catalog of ASQ Quality Press publications,call 800-248-1946,or visit our Web site at qualitypress.asq.org.STATISTICALQUALITYCONTROLUSING EXCELSecond EditionSteven M.ZimmermanMarjorie L.IcenogleASQ Quality PressMilwaukee,WisconsinStastical Quality Control Using ExcelSteven M.Zimmerman and Marjorie L.IcenogleLibrary of Congress Cataloging-in-Publication DataZimmerman,Steven M.Statistical quality control using Excel/Steven M.Zimmerman Marjorie L.Icenogle.2nd ed.p.cm.Includes bibliographical references and index.ISBN 0-87389-566-5(Soft Cover,Auto Bind:alk.paper)1.Quality controlData processing.2.Microsoft Excel(Computerfile)I.Icenogle,Marjorie.II.Title.TS156.Z54 2003658.562dc212002014684 2003 by ASQAll rights reserved.No part of this book may be reproduced in any form or by anymeans,electronic,mechanical,photocopying,recording,or otherwise,without the priorwritten permission of the publisher.10 9 8 7 6 5 4 3 2 1ISBN 0-87389-566-5Publisher:William A.TonyAcquisitions Editor:Annemieke KoudstaalProject Editor:Paul OMaraProduction Administrator:Gretchen TrautmanSpecial Marketing Representative:David LuthASQ Mission:The American Society for Quality advances individual,organizational,andcommunity excellence worldwide through learning,quality improvement,andknowledge exchange.Attention Bookstores,Wholesalers,Schools,and Corporations:ASQ Quality Press books,videotapes,audiotapes,and software are available at quantity discounts with bulkpurchases for business,educational,or instructional use.For information,please contact ASQ Quality Press at 800-248-1946,or write to ASQQuality Press,P.O.Box 3005,Milwaukee,WI 53201-3005.To place orders or to request a free copy of the ASQ Quality Press Publications Catalog,including ASQ membership information,call 800-248-1946.Visit our Web site atwww.asq.org or qualitypress.asq.org.Printed in the United States of AmericaPrinted on acid-free paperQuality PressCall toll free 800-248-1946Fax [phone]www.asq.orgqualitypress.asq.orgstandardsgroup.asq.orgE-mail:authorsasq.org600 N.Plankinton AvenueMilwaukee,Wisconsin 53203vTo my grandchildren Samantha,Arthur,Richard,and Elizabeth S.M.Z.To my nephew Brian,and nieces Talia and Kristin M.L.I.ContentsPreface .xvWhy Spreadsheets?.xvSelecting a Spreadsheet Program .xviOrganization .xviiFeatures .xviiiAcknowledgments.xviiiPart IStartingChapter OneIntroduction .3Introduction .3What Is Statistical Quality Control?.4What Is Statistical Process Control?.4What Is Acceptance Sampling?.6Why Use Spreadsheets?.6How to Use This Text .7Organization and Features .9Summary .9Chapter TwoSQC and Spreadsheet Programs.11Introduction .11History of Quality Control .12Structure of SQC Problems .12Statistical Process Control .12Acceptance Sampling .13Using Spreadsheet(Worksheet)Templates As Software .16Editing the Templates .16Locking Worksheet Cells for Other Users.16Excel Capabilities .18Summary .19Part IIFundamentals of Excel xpChapter ThreeMicrosoft Excel Basics.23What Are Worksheet Programs?.24viTable of ContentsviiWarning .26Operating in the Microsoft Windows xp Environment .26Using the Mouse .26Understanding the Excel Operating Environment.26Why Use Microsoft Excel?.26Starting Excel .27Closing Excel .27Shutting Down Your Computer.29The Excel Window .29Understanding the Dialog Boxes in Excel xp .31Opening,Creating,Saving,and Closing Workbooks .34Opening a Copy of a Template .36Opening a Workbook.36Reopening a Recently Used Workbook .37Creating a New Workbook .38Saving a Workbook.39Closing a Workbook .41Entering Text,Numbers,and Formulas in Excel .41Moving to a Cell .41Entering Text and Numbers .42Entering Formulas .43Understanding Absolute and Relative Addresses .44Using Excel Functions .45Manipulating Worksheet Contents.46Copying Cells .46Cutting and Pasting .47Formatting Text and Numbers .48Retaining Leading Zeros.48Adjusting Column Width or Row Height .49Inserting and Deleting Columns and Rows .50Controlling Your View .50Using Help .51Summary .52Chapter FourLearning More about Excel .53Introduction .54Checking Your Spelling .54Sorting Data .55Creating Charts and Graphs .58Selecting the Graphing Range.58Step 1 of 4 Choosing a Chart Type .59Step 2 of 4 Identifying Chart Source Data .59Step 3 of 4 Selecting Chart Options .61Step 4 of 4 Choosing the Chart Location .61Formatting a Chart.64Formatting the Chart Title and Axis Titles.65Formatting the Axis Scale .66Formatting the Legend.67viiiTable of ContentsFormatting the Appearance of Data Points and Lines .67Creating Text Boxes .69Printing Worksheets and Graphs.70Page Setup Options.70Manually-Set Page Breaks .74Print Preview .74Printing Worksheets .74Customizing with Add-Ins .76Importing Data from Other Sources .76Importing Microsoft Access Data Files into Excel .79Pasting Graphics,Text,and Tables among Programs .80Hypertext Capabilities.80Summary .80Chapter FiveLinking Excel and PowerPoint for Outstanding Presentations.83Introduction to Microsoft PowerPoint.84Selecting the Design Template .85Copying Graphics into a Presentation.88Linking and Embedding Objects into a Presentation .89Linking or Embedding an Excel Graph into a Presentation .89Reconnecting or Breaking a Link .90Linking or Embedding a Complete Excel Workbook into a Presentation .93Internet GraphicsCopyright Issues .94Hyperlinks in Presentations .96Animating Presentations .97Preset Animation Schemes .97Custom Animation .98Summary .102Exercises.102Part IIIFundamentals of Statistical Quality ControlChapter SixStatistical Fundamentals Using Excel .105Statistical Fundamentals .106Central Tendency .107Arithmetic Mean .107Median.107Mode .107Variation .108Range.109Variance .109Standard Deviation.110Shape of the Frequency Distribution .110Probability Density Function(pdf).112Cumulative Distribution Function(cdf).113Using Excel to Understand Distributions .113Standardized Normal Distribution .115Table of ContentsixCreating the Standardized Normal Worksheet .115Creating the Probability Density Function Graph .117Creating the Cumulative Distribution Function Graph .117Binomial Distribution .120Creating the Binomial Worksheet.121Creating the Binomial pdf and cdf Graphs .123Using Excel Functions.125Summary .126Chapter SevenTalking Statistical Quality Control .127Introduction .127Specifications .128Inspection.128Acceptance Sampling 128Statistical Process Control.130Summary .133Chapter EightDistributions .135Introduction .136Probability .136Uniform Distribution .137Central Limit Theorem .139Normal Distribution.142Entering Worksheet Formulas.144Exponential Distribution .147Poisson Distribution .149Summary .150Chapter NineSelected Functions and Data Analysis Tools .153Introduction .154Excel xp Functions.154Chi-square Function .154Correlation Function(CORREL).156COUNTBLANK Function .158TREND Function and Trendlines .159Data Analysis Tools .162Rank and Percentile .163Regression Analysis .164Single-Factor ANOVA .167Summary .168Part IVStatistical Process ControlChapter Ten X and Range Control Charts .171What Is Statistical Process Control?.172Why Use X and Range Control Charts?.173Creating X and Range Worksheets .174 X and Range Chart Formulas .176Entering Worksheet Text.178Numbering Rows and Columns .178Entering Observation Data .180Entering Formulas .182Graphing the X Control Chart .184Graphing the Range Control Chart .189Formatting Text .189Tools and Spelling .192Template .192Expanding the X and Range Control Charts .193Copying Sheet 1 to Sheet 2 .193Recording Base Period Control Chart Parameters .194Copying Parameter Values into Columns .195Calculating the Subgroup Averages and Ranges .196Editing the Control Graphs .196Creating a Comparison Graph .196Creating Worksheet 3 .196Graphing the X and Range Control Charts for Worksheet 3 .199Using X and Range Control Charts .201No Change in Variation,Two-Sigma Increase in the Mean .201No Change in the Mean,One-Sigma Increase in the Variation .202Real Life.204Summary .206Exercises.206Chapter Eleven X and Sigma Control Charts .209Why Use X and Sigma Control Charts?.210Creating X and Sigma Control Worksheets.211 X and Sigma Chart Formulas .212Entering Worksheet Text.214Generating Sample Data.215Entering Formulas .216Creating the X Control Chart.218Creating the Sigma Control Chart .221Support Capabilities.221Formatting Numbers.221Formatting Text .222Template .222Using X and Sigma Control Charts .223Expanding the Worksheet for Additional Observations.224Changing the Average and Standard Deviation of a Range of Subgroups.224No Change in the Mean,Two-Sigma Increase in the Variation .226Real Life.226Summary .227Exercises.228Chapter Twelvep and np Control Charts.229Why Use p and np Control Charts?.230Creating p and np Worksheets .231xTable of Contentsp and np Chart Formulas .231Entering Worksheet Text.232Generating Sample Data.235Entering Worksheet Formulas.236Creating the p Control Chart .238Template .239Worksheet Design Improvements .240Revising the Worksheet for the np Control Chart .242Changing Formulas for np Chart Generation .243Changing Column Labels for the np Control Chart .244Creating the np Control Chart .244p Control Charts Based on Variable Lot Size .246Revising the Worksheet .246Revising Formulas for Chart Generation .247Generating the p Control Chart with Variable Lot Size .249Using p Control Charts .249Revising the Worksheet .250Real Life.252Summary .253Exercises.253Chapter Thirteenc and u Control Charts.255Why Use c and u Control Charts?.256Poisson DistributionRandom Numbers .257c Chart Formulas.258Creating the c Chart Worksheet .258Entering Worksheet Text.258Generating Sample Data.260Entering the Poisson Formulas .263Range Names and Macros .265Entering Worksheet Formulas.267Creating the c Control Chart .268Changes in (Lambda).269u Chart Formulas .270Creating the u Chart Worksheet .271Entering Worksheet Text.272Entering Sample Data .273Entering Worksheet Formulas.274Real Life.276Summary .276Exercises.277Chapter FourteenProcess Capability .279Capability of a Process .280Testing for the Normal Distribution .281Creating a Process Capability Worksheet .282Entering Worksheet Text.282Creating Observations Using the Normal Random Generator .283Defining the Z Scale .286Table of ContentsxiGraphing Data for a Visual Test .289Cumulative Graph .290Expected,Observed,and Specification Limits .290Kolmogorov-Smirnov(KS)Test .291Kolmogorov-Smirnov(KS)Graph .296Issues in KS Testing .297Indexes and Ratios.297Real Life.300Summary .302Exercises.302Chapter FifteenPareto and Fishbone Diagrams.303Why Use Pareto Analysis?.304Creating a Worksheet for a Pareto Diagram .304Entering Worksheet Text.305Entering Worksheet Formulas.305Creating the Pareto Diagram .306Revising the Pareto Worksheet to Include Costs .307Creating the Pareto Cost Diagram .310Why Use a Fishbone Diagram?.310Creating a Fishbone Worksheet .312Entering Worksheet Text.312Entering Worksheet Art .312Summary .312Exercises.314Chapter SixteenCumulative Sum(CuSum)Charts.315What Is a Cumulative Sum(CuSum)Chart?.316Why Use the CuSum Chart?.318Disadvantages of the CuSum Chart .319Creating the CuSum Worksheet .319Entering Worksheet Text.319Entering Worksheet Values .320Entering Worksheet Formulas.321Creating the CuSum Graph.323Summary .324Real Life.324Exercises.324Chapter SeventeenIndividual,Moving Average,and Moving Range Control Charts .329Introduction .330Why Use Individual Control Charts?.331Assumptions of Normality .332Creating and Calculating Individual Process Control Worksheets.333Entering Worksheet Text.333Entering Observation Data .333Entering Worksheet Formulas.335Graphing the Individual Control Chart .335xiiTable of ContentsRevising the Worksheet for the Moving Range Control Chart .336Graphing the Moving Range Control Chart .337Revising the Worksheet for the Moving Average Control Chart .338Graphing the Moving Average Control Chart .338Using Individual,Moving Range,and Moving Average Control Charts .339Real Life.340Summary .343Exercises.343Chapter EighteenExponential Weighted Moving Average Control Charts .345Why Use EWMA Charts?.346Creating the Smoothed Limits EWMA Worksheet.349Entering Worksheet Text.349Entering Observation Values .349Entering Worksheet Formulas.352Creating the Smoothed Limits EWMA Control Chart.353Revising the Worksheet for the Wandering Limits EWMA Control Chart .354Changing the Worksheet Text.354Changing the Worksheet Formulas.354Creating the Wandering Limits EWMA Control Chart .355Real Life.356Summary .358Exercises.359Part VAcceptance SamplingChapter NineteenBinomal and Hypergeometric Distributions .363Introduction .364Binomial Distribution .365Hypergeometric Distribution.366Creating and Calculating the Binomial and Hypergeometric Worksheet .366Entering Worksheet Text.366Generating Observation Data .367Self-Checking .368Graphing the Binomial and Hypergeometric Distributions.369Summary .370Exercises.371Chapter TwentyOperating Characteristic and Power Curves(Measuring Average Incoming Quality)373The Operating Characteristic Curve and Power Curve .374Creating an Operating Characteristic Worksheet .377Entering Worksheet Text.377Entering Worksheet Formulas.378Adding the Power Curve Observations to the Worksheet.379Graphing the Operating Characteristic Curve .380Graphing the Power Curve .380Hypergeometric Operating Characteristic Curves.380Graphing the Hypergeometric Operating Characteristic and Power Curves.382Table of ContentsxiiiDemings All-or-None Inspection Rule .383Real Life.385Summary .386Exercises.387Chapter Twenty-OneAverage Outgoing Quality .389Outgoing Quality .390Creating the Average Outgoing Quality Worksheet .391Entering Worksheet Text.391Formatting Column Headings .392Calculating the Average Outgoing Quality Limit.392Creating the Average Outgoing Quality Graph .393Real Life.395Summary .395Exercises.396Chapter Twenty-TwoComparing Control Chart Performance .397Introduction .398Creating an Operating Characteristics Curves Workbook .400Creating the Data Generation Worksheet .401Creating the X Worksheet .402Graphing the X Control Chart .405Creating the CuSum Worksheet .405Graphing the CuSum Control Chart .407Creating the Simulation Worksheet .409Creating the Comp