Data analysis and improvement examples using the seven QC tools in Excel

Introduction to the Seven QC Tools

💡 こうした調達・受発注の属人化、newji なら「ひとつの画面」で解決。見積依頼から発注・進捗・承認までAIが下支えします。

14日間無料で試す →

The seven Quality Control (QC) tools, a set of fundamental techniques introduced by Kaoru Ishikawa, are essential for quality management and process improvement.
These tools help identify, analyze, and interpret data, enhancing the decision-making process in businesses.
The advantage of using these tools with Excel is the ability to manage and visualize large data sets efficiently.
This article explores the seven QC tools and provides examples of how to implement them using Excel.

Pareto Chart in Excel

A Pareto chart is used to identify and prioritize problems by highlighting the most significant factors.
It combines a bar graph with a line graph where individual values are represented in descending order by bars, and the cumulative total is represented by the line.

Creating a Pareto Chart

To create a Pareto chart in Excel, first, list your data with categories and their respective frequencies.
Select the data and navigate to the “Insert” tab.
Choose “Insert Column or Bar Chart” and select “Clustered Column.”
After inserting the column chart, add a cumulative percentage line by selecting “Change Chart Type” and choosing “Combo.”
Set the category frequencies to a “Clustered Column” and the cumulative total to a “Line.”

Fishbone Diagram in Excel

A Fishbone diagram, or Ishikawa diagram, is used to identify potential causes of a problem.
It helps in brainstorming and categorizing the root causes.

Creating a Fishbone Diagram

Although Excel does not have a built-in feature for Fishbone diagrams, you can create one manually using the “Shapes” tool.
Insert a horizontal arrow to represent the main problem.
Then, add diagonal lines branching off the main arrow to list the potential causes.
Label the major branches to represent different categories like people, equipment, materials, etc.

Check Sheets in Excel

Check sheets are used to collect data in a structured and consistent manner.
They help gather data easily and are useful during data collection phases.

Designing a Check Sheet

To design a check sheet in Excel, create a simple table with columns for the date, event, and tally marks.
Use additional columns if needed depending on the specifics of the data collection requirements.
Use Excel’s data validation feature to limit the data inputs to specific entries, ensuring accuracy and consistency.

Control Charts in Excel

Control charts help monitor processes over time to detect unusual variations.
They plot data points along a timeline and help in distinguishing between natural variations and significant changes.

How to Make a Control Chart

In Excel, organize your data with a timeline and the values you wish to track.
Insert a “Line Chart” to plot these values.
Add horizontal lines for the mean, upper control limit (UCL), and lower control limit (LCL).
To do this, calculate the mean and standard deviation of your data.
Set the UCL and LCL by adding or subtracting three times the standard deviation from the mean, respectively.

Histogram in Excel

A histogram represents the frequency distribution of numerical data.
It is useful for understanding the distribution and identifying variations.

Creating a Histogram

To create a histogram in Excel, first compile the data into classes or bins.
Use the “Data Analysis” tool in Excel’s “Data” tab and select “Histogram.”
Choose the data range and bin range, then Excel will generate the histogram.

Scatter Diagram in Excel

A scatter diagram illustrates the relationship between two variables.
It helps to identify correlations and patterns.

Steps to Create a Scatter Diagram

In Excel, input your two sets of data into adjacent columns.
Highlight these columns and go to the “Insert” tab.
Select “Insert Scatter (X, Y) or Bubble Chart” and choose “Scatter.”
This will plot your data points to illustrate any potential relationships.

Cause-and-Effect Diagram in Excel

A cause-and-effect diagram helps to solve problems by identifying possible causes.
It visually maps out contributing factors to the problem.

Drawing a Cause-and-Effect Diagram

Since Excel doesn’t offer a direct tool, you can use shapes to manually create one.
Begin with a horizontal line (the spine) where you write the main issue.
Using arrows, branch out to show major categories of potential causes.
For each branch, list the individual factors that may contribute to each category.

Using Excel Add-Ins for QC Tools

To enhance the capabilities of Excel in implementing QC tools, several add-ins are available.
These add-ins extend Excel’s functionality by providing ready-made templates and specialized functions for quality control tasks.

Recommended Excel Add-Ins

Three popular Excel add-ins are “QI Macros,” “XLSTAT,” and “SigmaXL.”
These tools provide additional analytics features that cater to quality control requirements.
They include pre-built templates for creating control charts, Pareto diagrams, and more, simplifying the process for users.

Conclusion

Utilizing Excel for implementing the seven QC tools is efficient and effective in managing quality control tasks.
Excel’s versatile features, combined with its widespread availability, make it an ideal platform for performing quality analysis.
Learning to efficiently apply these tools will lead businesses to streamlined operations, better quality management, and improved service delivery.
By integrating QC tools with Excel’s powerful data processing capabilities, organizations can drive performance improvements and boost overall productivity.

WHITE PAPER

この記事の理解を深める
無料ホワイトペーパーをプレゼント

製造業の現場で使える実務資料（PDF）を無料でお届けします。"こんな資料が届きます" ↓ 下のボタンからどうぞ。