CN101266597A - Method for generating quality management common tool drawing at Excel - Google Patents
Method for generating quality management common tool drawing at Excel Download PDFInfo
- Publication number
- CN101266597A CN101266597A CNA2008100547677A CN200810054767A CN101266597A CN 101266597 A CN101266597 A CN 101266597A CN A2008100547677 A CNA2008100547677 A CN A2008100547677A CN 200810054767 A CN200810054767 A CN 200810054767A CN 101266597 A CN101266597 A CN 101266597A
- Authority
- CN
- China
- Prior art keywords
- excel
- quality management
- common tool
- quality
- data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Landscapes
- Management, Administration, Business Operations System, And Electronic Commerce (AREA)
Abstract
A method for generating a quality management common tool graph in the Excel, the method includes the following steps: a, an input area and a center calculation area are designed in an EXCEL electronic table, b, data collected in quality management is exported into the input area, c, formulas is designed and edited according to requirement by using an EXCEL built-in function in the center calculation area, then center calculations of sum, merger, average are operated, eigenvalue of quality statistic is calculated, d, the quality management common tool graph in an EXCEL condition is formed by using a graph guide function of the EXCEL. The invention has advantages of well-directed, wide popularity, high speed, the eigenvalue of quality statistic and the graph is simultaneously changed when input data is changed, a template is designed according to requirement at anytime, and efficiency of analyzing various graphs is greatly progressed.
Description
Technical field:
The invention belongs to a kind of drafting method, be specifically related to a kind of method that in Excel, generates quality management common tool figure.
Background technology:
At present, understand by online enquiries and bookstore, also find no the people in Excel systematically research how to generate quality management common tool figure, in the quality management data analysis, as with traditional manual plotting, speed is slow, difficulty is big; If adopt special-purpose statistical software, price is expensive, popularizes not too easily.
Summary of the invention
The present invention be exactly to solve utilize traditional-handwork generate quality management common tool figure difficulty big, utilize the special software mapping to have the expensive problem popularized of being difficult for of price again.
A kind of method that in Excel, generates quality management common tool figure, the step that this method comprises has:
A, in the EXCEL electrical form, design input field, intermediate operations district, output area,
B, with the data of collecting in the quality management, be input to the input field,
C, utilize the built-in function among the EXCEL, in the intermediate operations district, design as required, edit formula, sue for peace, merger, ask intermediate operations such as average, the calculated mass statistical characteristics,
D, utilize among the EXCEL mapping of ChartWizard function, form the various quality management common tool figure under the EXCEL environment.
A kind of method that generates quality management common tool figure in Excel is with strong points, popularize easily, speed is fast, as long as understand the EXCEL simple operations, through simple training, just can design masterplate as required at any time, greatly improved the efficient that generates all kinds of analyzed patterns, and in use deepened understanding the intension of instrument own, can be with the study mathematical statistics by uninteresting lively, the image, simple and clear that becomes, have very strong illustrative and interest.When the data of input changed, statistic of attribute eigenwert and figure were just simultaneously along with changing.
Description of drawings
Fig. 1 is the case effect figure that utilizes the quality management Pareto diagram commonly used that the present invention generates
Fig. 2 is that the case effect figure that process capability calculates simple and easy masterplate is used in the quality management that utilizes the present invention to generate always
Fig. 3 utilizes the quality management scatter diagram commonly used that the present invention generates and the case effect figure of regression straight line
Embodiment:
The quality management common tool figure that now can generate has: Pareto diagram, process capability calculate simple and easy masterplate, scatter diagram and regression straight line, histogram, control chart, point diagram, changeable spirogram, case line chart, questionnaire, Gantt chart, detectability index measurement and analyze figures.The present invention is described in further detail with embodiment with reference to the accompanying drawings below.
1. Pareto diagram
In quality management, Pareto diagram is with the most use, most popular figure.
Pareto diagram is Pareto diagram again.
Be to influence the employed figure of quality main cause for seeking.
It has used the principle of " crucial minority, less important majority ".In quality management, which quality problems is a subject matter, the main cause that produces quality problems where, this is started with wherefrom again after finding problem, Pareto diagram is exactly a kind of effective ways that address the above problem.
The figure that on behalf of 80%, 90%, 100% straight line, Pareto diagram form by an ordinate, horizontal ordinate, several column diagram that is arranged in order by the height order, cumulative percentage curve and three.
As in Fig. 1, have by problem statistical arrangement figure, arrange three figures of column diagram by the statistical arrangement figure of unit and two dimensions.
It is achieved in that
1.1 design table (schedule) such as table one:
In Table 1, cell A2:H7 is the input field;
(A2:H2 is for doing the intermediate computations district of " by problem statistical arrangement figure " A8:H12), (A13:F18) is for doing the intermediate computations district by " the statistical arrangement figure of unit ";
Make " two dimension arrangement column diagram " with the data in (A2:E6), do not get the total data among the cell B3:H7, purpose is to influence the quality main cause in order to seek.
Secondly, in the intermediate computations district, designed formula and edited relevant EXCEL built-in function as required.
In input field input data or after changing data, total column or row to data unit of pressing or problem, add up to calculating respectively, but data not necessarily are descending sort, at this moment, be that main object carries out descending sort with the data of the row or column that adds up to, at this moment, data in cell B3:H7 will be two-way synthetic ascending order to the upper left side from the lower right and arrange.We take the method for record macro, write down " grand " program of implementing the descending operation, and specified record grand with text box " descending sort ", after, when the data of importing change, as long as click text box " descending sort ", data will be as above situation ordering (as table one), and figure is also along with variation.
Table one
That records is grand:
Design formula:
Formula among the cell I3:I8
Formula among the cell B8:H12:
| 8 | Add up to | ＝SUM(B3:B7) | ＝SUM(C3:C7) | ＝SUM(D3:D7) | ＝SUM(E3:E7) | ＝SUM(F3:F7) | ＝SUM(G3:G7) | ＝SUM(H3:H7) |
| 9 | Cumulative number | ＝B8 | ＝B9+C8 | ＝C9+D8 | ＝D9+E8 | ＝E9+F8 | ＝F9+G8 | ＝G9+H8 |
| 10 | 0.8 | ＝$H$9*0.8 | ＝$H$9*0.8 | ＝$H$9*0.8 | ＝$H$9*0.8 | ＝$H$9*0.8 | ＝$H$9*0.8 | ＝$H$9*0.8 |
| 11 | 0.9 | ＝$H$9*0.9 | ＝$H$9*0.9 | ＝$H$9*0.9 | ＝$H$9*0.9 | ＝$H$9*0.9 | ＝$H$9*0.9 | ＝$H$9*0.9 |
| 12 | 1 | ＝$H$9*1 | ＝$H$9*1 | ＝$H$9*1 | ＝$H$9*1 | ＝$H$9*1 | ＝$H$9*1 | ＝$H$9*1 |
Formula among the cell A14:F18:
Mapping
Choose (A2:E6),, take column diagram, bunch shape column diagram, series to be created in the method for row, do " two dimension arrangement column diagram " with the ChartWizard function among the EXCEL.
Choose (A2:H2, A8:H12), with the ChartWizard function among the EXCEL, the method for taking column diagram, bunch shape column diagram, series generation to be expert at, work " by problem statistical arrangement figure ".In the mapping, to the broken line of expression mass curve, subtype is " XY scatter diagram, a broken line scatter diagram ", and to representing 80%, 90%, 100% 3 straight line, subtype is " XY scatter diagram, the level and smooth scatter diagram in countless strong points ".
Choose (A13:F18), with the ChartWizard function among the EXCEL, the method for taking column diagram, bunch shape column diagram, series to be created in row is made " the statistical arrangement figure of the unit of pressing ".
2. process capability calculates simple and easy masterplate
The ability that the measurement operation can stably produce specification product is called process capability.
In other words, process capability is exactly within a certain period of time, the equipment that this operation is used, frock, material, method of operating and check utensil etc. under rated condition, the machining precision that when production is in steady state (SS), is had, promptly operation is in the actual working ability under the steady state (SS).
Say that in a certain sense process capability also can be understood as process quality.
From quantitative angle, process capability be exactly operation itself the intrinsic a kind of characteristic that can measure.
Process quality then refers to the fluctuating range (degree of scatter) of the quality characteristic value of operation institute converted products.For the process capability of variable characteristic project, be: B=6 σ with quantificational expression.
Can draw from the mathematical statistics theory, under the normal distribution situation, be 99.7% at the ratio of 6 σ scopes.If B (=6 σ) is big more, the realistic accuracy of expression operation is poor more, and process capability is more little; If B (=6 σ) is more little, the realistic accuracy of expression operation is high more, and process capability is big more.
The process quality analysis will be analyzed the influence factor that causes the process quality fluctuation exactly, and then takes appropriate measures, and eliminates abnormal factors, makes to produce to recover normal, is in steady state (SS), i.e. slave mode.
The factor that influences process quality has people, machine, material, method, ring, five big factors.
The notion of process capability index
To some manufacturing procedure, particularly take the operation of factor as the leading factor with machine, the quantificational expression of its process quality generally can be used " process capability index " C
PExpress.So-called process capability index is exactly the margin tolerance " T " of product and the ratio of process capability " B ", and its expression process capability is to the assurance degree of product design quality requirements, to represent:
C
P＝T/6σ
According to C
PThe size of value, but quantitative estimation goes out the percent defective and the σ level of this process.
We are in the same place histogram, normal distribution and process capability index calculation combination, have made " process capability calculates simple and easy masterplate ", for process control provides a simple and practical instrument.
Main method for making:
2.1 design table (schedule) such as table three are established input field, output area and intermediate computations district in table.
In the input field, the measurement data of 50 part sizes of input, formula and EXCEL built-in function by the output area is provided with calculate: maximal value, minimum value, extreme difference, bias, mean value, standard deviation, super poor probability, the probability of super allowance below nominal size, process capability index C automatically
P, C
PK
The centre calculate the district be for the mapping carried out intermediate computations (intermediate computations district last column be 52 the row, this get 18 the row).
Table two
2.2 design formula and insertion function
Formula in the output area is:
The intermediate computations district is:
2.3 mapping
Choose (P9:Q52),, take column diagram, bunch shape column diagram, series to be created in the method mapping of row with the ChartWizard function among the EXCEL.At this moment, only get the data of P, two row of Q and the data of input X-axis, and taked the method for adding, the data of adding R, S two row.The pairing figure of data to the R row will make normal distribution, so subtype is " XY scatter diagram, the level and smooth scatter diagram in countless strong points ".After making, again figure is carried out suitable form.
3. scatter diagram and regression straight line
Scatter diagram also is correlogram.It is the figure of two variable change relations of expression.
Scatter diagram is made up of an ordinate, a horizontal ordinate and a lot of idea of scattering.
As figure below is to show the scatter diagram that concerns between the acidity X of certain brewery's intermediate product wine unstrained spirits and these two variablees of wine degree Y.
In rectangular coordinate system, one group of coordinate (X, Y) is arranged just, corresponding point, the set of these points is exactly a scatter diagram.
Idea distribution situation from the scatter diagram can go out between two variablees whether correlationship is arranged by observation analysis, and the level of intimate of relation how.
Scatter diagram is the set of point, if can represent that quantitatively two relations between the variable just more are of practical significance with the formula of mathematics.
Two variablees exist significant correlationship, and for variable X, though be not that a definite Y value is corresponding with it, Y value is always in a scope, and is and big more the closer to the possibility of mean value appearance.Usually represent this estimated value with y=ax+b.The straight line of equation representative is called the regression straight line of y to x, and it is and the immediate straight line of whole ideas.
Describe the mathematics formula of regression straight line and recall the formula of returning.
After regression equation has been arranged, in industrial analysis, correlationship can be described quantitatively.As long as X is controlled on the certain level, just can obtain corresponding Y value.Otherwise,, also can obtain the X value if Y is controlled on the certain level.
According to the related coefficient determining method:
When the absolute value of related coefficient is lower correlation less than 0.3 the time;
Absolute value was that moderate is relevant between 0.3～0.7 o'clock;
Absolute value was a height correlation between 0.7～0.8 o'clock;
Absolute value is unusual height correlation when above 0.8.
We are as long as input or change the coordinate figure of X, Y in the input field, and so, the value of Y, Y1, Y2 is just along with change, the just judgement that can produce new equation, related coefficient automatically and draw degree of correlation, and, import known X value, just can dope the value of Y.
The figure of being done such as Fig. 3
Main method for making:
3.1 design table (schedule) such as table three are established input field, output area and intermediate computations district in table.
In the input field, input will be carried out the X of statistical study, the value of Y;
In the intermediate computations district, the span of establishing X is X ± 0.2.
In the output area, obtain equation, related coefficient, intercept, slope respectively; Known X, prediction Y; Judge degree of correlation according to related coefficient.
Table three
3.2 formula and function
In intermediate computations district and output area, all be to realize by the formula of design and the function of insertion.
The formula in intermediate computations district is:
The formula of output area:
The formula of judging degree of correlation according to related coefficient is:
=IF (N4＜=0.3, " lower correlation ", IF (N4＜=0.7, " moderate is relevant ", IF (N4＜=0.8, " height correlation ", IF (N4＜=1, " very height correlation "))))
Other formula are:
3.3 mapping
Data correspondence among the B5:C34 be scatter diagram, the data correspondence in the intermediate computations district be three straight lines.The practice is chosen B5:F34,, takes " XY scatter diagram, scatter diagram, more paired numerical value " mapping with the ChartWizard function among the EXCEL.The figure of making here all is a scatter diagram, to the scatter diagram of the data correspondence in the centre calculating district, is that " XY scatter diagram; the level and smooth scatter diagram in countless strong points " makes amendment with subtype again, become three straight lines, and further figure is carried out form, just become the figure of Fig. 3.
Claims (1)
1, a kind of method that in Excel, generates quality management common tool figure, the step that this method comprises has:
A, in the EXCEL electrical form, design input field, intermediate operations district, output area;
B, with the data of collecting in the quality management, be input to the input field;
C, utilize the built-in function among the EXCEL, in the intermediate operations district, design as required, edit formula, sue for peace, merger, ask intermediate operations such as average, calculated mass statistical characteristics;
D, utilize among the EXCEL mapping of ChartWizard function, form the various quality management common tool figure under the EXCEL environment.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CNA2008100547677A CN101266597A (en) | 2008-04-09 | 2008-04-09 | Method for generating quality management common tool drawing at Excel |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CNA2008100547677A CN101266597A (en) | 2008-04-09 | 2008-04-09 | Method for generating quality management common tool drawing at Excel |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN101266597A true CN101266597A (en) | 2008-09-17 |
Family
ID=39989020
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CNA2008100547677A Pending CN101266597A (en) | 2008-04-09 | 2008-04-09 | Method for generating quality management common tool drawing at Excel |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN101266597A (en) |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101556579B (en) * | 2009-05-08 | 2012-10-10 | 用友软件股份有限公司 | Formula processing system used for dynamic report areas and method thereof |
| CN103034652A (en) * | 2011-09-28 | 2013-04-10 | 海洋王照明科技股份有限公司 | Method and system for analyzing and processing data |
| CN105138500A (en) * | 2015-08-10 | 2015-12-09 | 闻泰通讯股份有限公司 | Method for importing access point name (APN) by EXCEL splicing grammar |
| CN105571553A (en) * | 2013-07-01 | 2016-05-11 | 蔡洪祥 | Cable external diameter auxiliary detection method |
| CN105912817A (en) * | 2016-05-06 | 2016-08-31 | 姜凌宇 | Excel software based method for judging concrete strength guarantee rate |
| CN109507751A (en) * | 2017-09-15 | 2019-03-22 | 中国石油化工股份有限公司 | The consistency quantitative evaluation method and system of seismic sensor |
| CN113836882A (en) * | 2021-11-24 | 2021-12-24 | 深圳小库科技有限公司 | Method and device for generating economic index statistical table, computing equipment and storage medium |
-
2008
- 2008-04-09 CN CNA2008100547677A patent/CN101266597A/en active Pending
Cited By (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101556579B (en) * | 2009-05-08 | 2012-10-10 | 用友软件股份有限公司 | Formula processing system used for dynamic report areas and method thereof |
| CN103034652A (en) * | 2011-09-28 | 2013-04-10 | 海洋王照明科技股份有限公司 | Method and system for analyzing and processing data |
| CN105571553A (en) * | 2013-07-01 | 2016-05-11 | 蔡洪祥 | Cable external diameter auxiliary detection method |
| CN105571553B (en) * | 2013-07-01 | 2018-10-16 | 中策永通电缆有限公司 | A kind of auxiliary test method for cable outer diameter |
| CN105138500A (en) * | 2015-08-10 | 2015-12-09 | 闻泰通讯股份有限公司 | Method for importing access point name (APN) by EXCEL splicing grammar |
| CN105912817A (en) * | 2016-05-06 | 2016-08-31 | 姜凌宇 | Excel software based method for judging concrete strength guarantee rate |
| CN109507751A (en) * | 2017-09-15 | 2019-03-22 | 中国石油化工股份有限公司 | The consistency quantitative evaluation method and system of seismic sensor |
| CN113836882A (en) * | 2021-11-24 | 2021-12-24 | 深圳小库科技有限公司 | Method and device for generating economic index statistical table, computing equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN101266597A (en) | Method for generating quality management common tool drawing at Excel | |
| Laner et al. | A novel approach to characterize data uncertainty in material flow analysis and its application to plastics flows in Austria | |
| Caballero et al. | A Data Quality in Use Model for Big Data: (Position Paper) | |
| Wang et al. | Spatial differences and multi-mechanism of carbon footprint based on GWR model in provincial China | |
| CN104866572B (en) | A kind of network short text clustering method | |
| CN102214261B (en) | Method for automatic check of process data of printed circuit board | |
| CN107133436A (en) | A kind of multiple sample model training method and device | |
| CN104504583B (en) | The evaluation method of grader | |
| EP4080429A1 (en) | Technology readiness level determination method and system based on science and technology big data | |
| Feng et al. | A novel energy evaluation approach of machining processes based on data analysis | |
| Aydın et al. | A methodology for cutting force prediction in side milling | |
| CN114548552A (en) | Carbon emission prediction method and device, and electronic device | |
| Zou et al. | Efficiency of Two‐Stage Technological Innovation in High Patent‐Intensive Industries That Considers Time Lag: Research Based on the SBM‐NDEA Model | |
| CN116542430B (en) | Multi-dimensional water carbon emission intelligent analysis method and system | |
| Wu et al. | Optimization and improvement based on K-Means Cluster algorithm | |
| CN113837912B (en) | Analysis method for influence factors of carbon emission in construction industry | |
| CN110514335B (en) | Method for determining energy efficiency ratio of numerical control machine tool | |
| CN112950279A (en) | Accurate marketing strategy model construction method and device based on machine learning | |
| Subramani | Process control in the presence of linear trend | |
| Jia et al. | Use and future development of optical measurement technology in the study of wood surface roughness CiteSpace-based scientometric analysis (2003 through 2022) | |
| CN114036822B (en) | Quick thermal model construction method based on neural network | |
| Xiong et al. | Prediction of cement specific surface area based on XGBoost | |
| Ballard et al. | Applied General Equilibrium Analysis: Birth, Growth, and Maturity | |
| Sun et al. | Analysis and inference of stream of dimensional errors in multistage machining process based on an improved semiparametric model | |
| CN114926024A (en) | Online calibration method and system for coal gasification device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C12 | Rejection of a patent application after its publication | ||
| RJ01 | Rejection of invention patent application after publication |
Open date: 20080917 |