不良率分析の基本：まずは「何がどれだけ悪いのか」を見える化する
Excelで不良率を分析するとき、最初にやるべきことは「なんとなく不良が多い気がする」という感覚を、数字で見える状態に変えることです。現場や業務では、「最近ミスが増えている」「A商品の返品が多い」「入力エラーが目立つ」といった声が出ることがあります。しかし、そのままでは原因も優先順位も判断できません。
不良率分析の基本は、まず「何が」「どれだけ」「どのくらいの割合で」悪いのかを整理することです。たとえば製造業であれば、検査数に対して不良品が何件あったのか。事務作業であれば、処理件数に対して入力ミスや差し戻しが何件あったのか。IT系の業務なら、問い合わせ件数に対して設定ミスや対応漏れが何件あったのか、といった形です。
ここで重要なのが、単純な「不良件数」だけで判断しないことです。たとえばA商品で不良が10件、B商品で不良が5件あった場合、一見するとA商品のほうが問題に見えます。しかし、A商品を1,000個作っていて、B商品を50個しか作っていないなら話は変わります。不良率で見ると、A商品は1％、B商品は10％になります。つまり、本当に注意すべきなのはB商品の可能性が高いのです。
不良率は、基本的に次の式で計算できます。
不良率（％）＝ 不良数 ÷ 全体数 × 100
この考え方を使うことで、件数の大小に惑わされず、公平に状態を比較できます。部署別、商品別、担当者別、日付別、工程別など、さまざまな切り口で不良率を見ることで、「どこに問題が集中しているのか」が見えてきます。
Excelで分析を始める前には、まず元データを整理しておくことも大切です。最低限、日付・商品名・担当者・工程・結果・不良内容などの項目を表形式で用意しておきましょう。1行に1件のデータを入力する形にしておくと、後から集計やグラフ化がしやすくなります。
不良率分析は、難しい統計知識がなくても始められます。大切なのは、最初から完璧な分析を目指すことではなく、まず現状を数字で把握することです。「どの不良が多いのか」「どの条件で発生しやすいのか」をExcelで見える化できれば、改善すべきポイントは自然と絞り込まれていきます。
Excelで不良率を計算する方法：COUNTIF・ピボットテーブルを活用しよう
元データを整理できたら、次はExcelで実際に不良率を計算していきます。ポイントは、「全体数」と「不良数」を正しく数えることです。この2つが分かれば、不良率は簡単に算出できます。
たとえば、検査結果が入力された表があり、A列に「商品名」、B列に「検査結果」が入っているとします。B列には「良品」または「不良」と入力されているケースです。この場合、全体数は次のように計算できます。
=COUNTA(B:B)-1
見出し行を除いて、検査結果が入力されている件数を数えるイメージです。一方、不良数はCOUNTIF関数を使って数えます。
=COUNTIF(B:B,”不良”)
COUNTIFは、「指定した条件に合うデータが何件あるか」を数える関数です。不良率を出したい場合は、不良数を全体数で割り、パーセント表示にします。
=COUNTIF(B:B,”不良”)/(COUNTA(B:B)-1)
セルの表示形式を「パーセンテージ」にすれば、不良率として見やすく表示できます。小数点以下の桁数も調整しておくと、報告資料にも使いやすくなります。
さらに、商品別や担当者別に不良率を見たい場合は、COUNTIFよりもCOUNTIFS関数が便利です。COUNTIFSは複数条件で件数を数えられます。たとえば、A列が商品名、B列が検査結果で、「商品Aの不良数」を数えたい場合は次のように入力します。
=COUNTIFS(A:A,”商品A”,B:B,”不良”)
商品Aの全体数は、次のように数えられます。
=COUNTIF(A:A,”商品A”)
つまり、商品Aの不良率は次の式で計算できます。
=COUNTIFS(A:A,”商品A”,B:B,”不良”)/COUNTIF(A:A,”商品A”)
ただし、商品や担当者の種類が多い場合、手作業で式を作るのは手間がかかります。そこで活用したいのがピボットテーブルです。
ピボットテーブルを使えば、商品別・担当者別・工程別などの集計をドラッグ操作だけで作成できます。元データを選択し、Excelの「挿入」タブから「ピボットテーブル」を選びます。その後、行エリアに「商品名」、値エリアに「検査結果」や「件数」を入れることで、商品ごとの検査件数を確認できます。
不良数を集計したい場合は、「検査結果」を列エリアに入れると、「良品」「不良」ごとの件数を分けて表示できます。これにより、各商品の全体数と不良数を一覧で確認できるようになります。
ピボットテーブル上で不良率まで出したい場合は、集計結果の横に計算列を追加し、不良数 ÷ 総件数で算出すると分かりやすいです。ピボットテーブルを更新すれば、元データを追加した後も最新の状態で分析できます。
COUNTIF・COUNTIFSは細かい条件指定に向いており、ピボットテーブルは全体を素早く集計するのに向いています。まずはピボットテーブルで大きな傾向をつかみ、気になる部分を関数で深掘りする、という使い分けがおすすめです。
グラフで傾向をつかむ：不良が増えるタイミング・条件を見つける
不良率を計算できたら、次はグラフにして傾向を確認しましょう。数字の表だけを見ていると気づきにくい変化も、グラフにすると「この時期だけ急に悪化している」「特定の商品だけ不良率が高い」といったポイントが見えやすくなります。
まずおすすめなのが、日付別の不良率を折れ線グラフにする方法です。横軸に日付、縦軸に不良率を設定すると、不良が増えたタイミングを直感的に把握できます。たとえば、月初だけ不良率が高い、特定の週から急に悪化している、繁忙期にミスが増えている、といった傾向が見つかることがあります。
Excelでは、日付別に集計した表を選択し、「挿入」タブから「折れ線グラフ」を選ぶだけで作成できます。グラフを作ったら、縦軸をパーセント表示にし、グラフタイトルを「日別不良率の推移」など分かりやすい名前にしておきましょう。報告資料に貼り付ける場合も、一目で内容が伝わります。
次に確認したいのが、商品別・工程別・担当者別の不良率です。この場合は、棒グラフが向いています。横軸に商品名や工程名、縦軸に不良率を置くことで、どの項目が高いのかを比較しやすくなります。単純な件数ではなく不良率で比較することで、数量の多い少ないに左右されず、問題の大きさを判断できます。
たとえば、商品Aは不良件数が多くても不良率は低く、商品Cは件数は少ないのに不良率が高い、というケースがあります。このような場合、棒グラフにすると「本当に注意すべき対象」が分かりやすくなります。
さらに深掘りしたい場合は、条件を組み合わせたグラフも有効です。たとえば、「商品別×月別」「工程別×担当者別」のように分けて見ると、不良が発生しやすい条件が見えてきます。Excelのピボットグラフを使えば、ピボットテーブルで集計した結果をそのままグラフ化できるため、条件を切り替えながら確認できます。
ピボットグラフを作成するには、ピボットテーブルを選択した状態で「挿入」タブから「ピボットグラフ」を選びます。行に「日付」、列に「商品名」、値に「不良率」や「不良数」を設定すれば、商品ごとの推移を比較できます。必要に応じてスライサーを追加すると、担当者や工程をクリックで絞り込めるため、分析がかなり楽になります。
グラフを見るときは、単に「高い・低い」だけでなく、変化が起きた前後に何があったかを考えることが大切です。新しい担当者が入った、作業手順が変わった、設備を変更した、注文数が増えた、チェック体制が変わったなど、業務上の出来事と照らし合わせることで原因の仮説を立てやすくなります。
また、一時的な増加に振り回されないために、移動平均線を使うのもおすすめです。日々の不良率はばらつきが出やすいため、7日平均や4週平均で見ると、長期的に悪化しているのか、一時的なブレなのかを判断しやすくなります。
グラフ化の目的は、きれいな資料を作ることではありません。不良が増えるタイミングや条件を見つけ、改善の手がかりを得ることです。折れ線グラフで時系列の変化を見て、棒グラフで対象ごとの差を比較し、ピボットグラフで条件を切り替えながら確認する。この流れを押さえるだけでも、不良率分析の精度は大きく上がります。
改善ポイントの探し方：パレート図で“優先すべき原因”を絞り込む
グラフで不良の傾向が見えてきたら、次に考えるべきは「どこから改善すれば効果が大きいのか」です。不良原因が複数ある場合、すべてを同時に改善しようとすると、時間も手間もかかります。そこで役立つのがパレート図です。
パレート図とは、不良原因を件数の多い順に並べた棒グラフと、累積比率を表す折れ線グラフを組み合わせたものです。よく「全体の問題の大部分は、一部の原因から発生している」と言われます。つまり、発生件数の多い原因から優先的に対策すれば、少ない労力で大きな改善効果を狙えるという考え方です。
たとえば、不良原因として「入力ミス」「確認漏れ」「設定ミス」「連絡不足」「システム不具合」があったとします。このとき、単に原因を一覧で見るだけでは、どれを優先すべきか判断しにくいです。しかし、パレート図にすると「入力ミス」と「確認漏れ」だけで全体の70％を占めている、といった状況が一目で分かります。
Excelでパレート図を作る場合は、まず不良原因ごとの件数を集計します。ピボットテーブルを使い、行に「不良原因」、値に「件数」を入れると簡単です。その後、件数の多い順に並べ替えます。
次に、集計表に構成比と累積比率の列を追加します。構成比は、各原因の件数が全体に占める割合です。
構成比 ＝ 各原因の件数 ÷ 不良件数の合計
累積比率は、上から順番に構成比を足していったものです。たとえば、構成比が入っている列がC列なら、1行目はそのままC2、2行目以降は「前の累積比率＋今回の構成比」で計算します。
=D2+C3
表ができたら、「不良原因」「件数」「累積比率」を選択し、Excelの「挿入」から複合グラフを選びます。件数は集合縦棒、累積比率は折れ線に設定し、累積比率は第2軸にすると見やすくなります。これで、パレート図の完成です。
見るときのポイントは、累積比率が70〜80％に到達するまでの原因に注目することです。そこに入っている原因が、優先して改善すべき候補になります。逆に、発生件数が少ない原因に最初から時間をかけすぎると、全体の不良率はあまり下がらない可能性があります。
ただし、件数だけで判断しないことも大切です。発生件数は少なくても、顧客クレームにつながる、損失金額が大きい、安全性に関わるといった不良は、優先度を上げる必要があります。パレート図はあくまで改善の優先順位を考えるための材料として使いましょう。
不良率分析では、原因を細かく見すぎる前に、まずパレート図で大きな山を見つけることが重要です。「どの原因をつぶせば、全体の不良が大きく減るのか」をExcelで見える化できれば、改善活動のムダを減らし、効率よく成果につなげられます。
分析結果を改善につなげる：再発防止と効果確認までExcelで管理する
不良率分析で原因や優先順位が見えても、そこで終わってしまうと意味がありません。大切なのは、分析結果をもとに具体的な改善アクションへ落とし込み、効果が出ているかを確認することです。Excelは、分析だけでなく改善活動の管理にも活用できます。
まずは、パレート図などで特定した優先課題に対して、改善策を一覧化しましょう。おすすめは、Excelで次のような管理表を作ることです。
- 不良原因
- 改善内容
- 担当者
- 期限
- 実施状況
- 対策前の不良率
- 対策後の不良率
- 効果判定
たとえば「確認漏れ」が多い場合、改善内容には「チェックリストを作成する」「ダブルチェックのタイミングを変更する」「入力後に自動チェックする関数を追加する」などを記載します。ポイントは、誰が・いつまでに・何をするのかを明確にすることです。「気をつける」だけでは再発防止になりにくいため、作業手順や仕組みを変える対策にするのが理想です。
Excelでは、実施状況の列に「未着手」「対応中」「完了」などを入力し、条件付き書式で色分けすると管理しやすくなります。たとえば、未着手は赤、対応中は黄色、完了は緑にしておけば、進捗が一目で分かります。期限が過ぎているものを自動で赤く表示する設定にしておくと、対応漏れも防ぎやすくなります。
改善策を実施したら、必ず対策前後の不良率を比較しましょう。たとえば、対策前の不良率が5.0％、対策後が2.8％であれば、一定の効果があったと判断できます。一方で、ほとんど変化がない場合は、原因の見立てが違っていたり、対策が現場に定着していなかったりする可能性があります。
効果確認には、折れ線グラフを使うと便利です。対策を実施した日をグラフ上にメモしておくと、その前後で不良率がどう変化したかを確認できます。単発で下がっただけなのか、継続して改善しているのかを見ることが重要です。
また、再発防止のためには、改善後も定期的にデータを更新する仕組みを作りましょう。週次や月次で不良率を確認し、基準値を超えたら原因を再確認するルールにしておくと、問題が大きくなる前に気づけます。
不良率分析は、数字を出して終わりではなく、改善して、確認して、必要なら再対策するところまでがセットです。Excelで改善内容と結果を管理すれば、上司への報告もしやすくなり、自分の仕事の成果も説明しやすくなります。分析を「資料作成」で終わらせず、実際の業務改善につなげていきましょう。
コメント