如何使用Excel2007製作柏拉圖(Pareto chart)

FacebookTwitterEvernotePinterestPlurkDiggGoogle+EmailSina Weibo推到書籤/社群網站

如何使用Excel2007製作柏拉圖(Pareto chart)

柏拉圖(Pareto Chart)是品管工具裡不可或缺的工具之一,它利用80/20的趨勢原則幫助工程師們歸納出較重大的要因,然後讓工程師可以只針對少數的及個要因,集中火力並對症下藥,以收事半功倍之效。但好像沒有幾個人可以使用Excel畫出正確的柏拉圖。

如果你還不瞭解何謂柏拉圖(Pareto Chart),就參考這裡吧:
柏拉圖分析 (Pareto Chart)介紹

本文就暫時跳開所有的工程問題,單純的只討論如何利用Excel2007來製作出完整的柏拉圖(Pareto chart),我知所以強調完整,是因為很多人做出來的柏拉圖都有點似是而非,比較一下上面兩張柏拉圖的畫法,右邊的圖只要稍有Excel經驗的人,應該很簡單就可以畫出來,左圖才是比較正去的柏拉圖的畫法,要畫出這樣的圖可得有點技巧



心動了嗎?現在就來看看如何利用Excel2007畫出這樣的柏拉圖效果,不過得先說聲對不起,因為我只有英文版的Excel2007,所以解說中的指令也都是英文,可能得麻煩自己對照一下中文了。

Step 1. 輸入數據並將數據由大到小排列。
1. 如下圖輸入A1~D1標題及A3~B6的「現象」及「數量」。
2. 將D2~D6的各式設為「百分比」。
3. 在C3的地方輸入公式【=B3】,在C4的地方輸入公式【=C3+B4】,其餘的C5~C6用複製貼上就可以,或者用拖拉的方式複製也可以。
4. 在D2的地方輸入【0%】,這是一定要的,因為柏拉圖都是從0%開始的。
5. 在D3的地方輸入【=C3/$C$6】,其他的D4~D6用複製貼上就可以,或者用拖拉的方式複製也可以。小撇步:當你要輸入【$C$6】時,其實可以把滑鼠點到C6的欄位再按<F4>鍵,就會自動切換成絕對位址了。
6. 請檢查D6的地方,也就是百分比的累加最終一定要是100%。
柏拉圖製作02

Step 2. 建立柱狀圖
點選水平功能表的「Insert(插入)」,然後用滑鼠把A3~B6圈選起來,然後再按功能表的「Column」,出現快顯表之後選澤第一個圖形「Clustered Column」。
柏拉圖製作03

Step 3. 調整柱狀圖的間距為零
在柱狀圖上點選滑鼠右鍵,出現快顯功能表,選擇「Format Data Series…」,出現功能表之後,在「Series Options」的功能表下,將「Gap Width(間距)」往左邊拉到底,或是輸入「0」後按關閉,這樣就可以調成沒有間距了

柏拉圖製作04

Step 4. 為每個柱狀圖標注不同的顏色
為了美觀,可以個別設定每個柱狀圖的顏色。先單擊任何一個想變更顏色的柱狀圖,在Excel的右上方會出現「Chart Tools」功能表,選取「Format」,再下拉「Colored Style」樣式就可以選擇想要的式樣了。建議不要選到有白框的樣式,作成柏拉圖後感覺會怪怪的。
柏拉圖製作05
柏拉圖製作06

Step 5. 添加百分比折線圖
在圖表的上面點擊滑鼠右鍵,選擇「Select Data…」。柏拉圖製作07

Step 6. 開啟對話框之後,在左邊【Legend Entries(Series)】的地方點擊「Add」,新增一組資料。
柏拉圖製作08

Step 7. 出現【Edit Series】的對話框,把滑鼠游標點在【Series values:】的方框內,記得把原來上面的資料刪除,然後用滑鼠拉選新加入的資料項D2~D6,範例中因為放在「Sheet2」工作頁,所以其內容為【=Sheet2!$D$2:$D$6】。建議不要用輸入的方式作業,因為容易輸入錯誤。完成後點擊【OK】就可以看到原來沒有間距的柱狀圖又多出了一道空隙了,這是因為多出了一組資料的關係,但這組資料的數值因為太小(百分比造成),所以在圖面上比較難看到。
柏拉圖製作09

Step 8. 點選第二組資料,如果不好選的話就先點選第一組資料的柱狀圖,然後再按鍵盤的上、下按鍵來作選擇。一般的操作方式通常為選擇第一組柱狀圖,然後按向上按鍵,應該就可以選到了第二組資料了。注意工作表上的區域是否有跟著變換到第二組資料。在第二組資料的上面點擊滑鼠右鍵,在快顯功能表中選擇「Change Series Chart Type…」。如果可以直接選到第二組資料,也可以直接在其上面點按滑鼠右鍵就可以選擇了。
柏拉圖製作10

Step 9. 在【Change Chart Type】的對話框中,點選「Line」,再選擇第一組折線圖示,按「OK」。
柏拉圖製作11

Step 10. 柱狀圖回到原來沒有間距的模樣了,一樣選擇第二組資料的圖線,在其上面點擊滑鼠右鍵,在快顯功能表中選擇「Format Data Series…」。
柏拉圖製作12

Step 11. 在「Series Options」功能表下切換選擇到「Secondary Axis(次座標軸)」,然後按「OK」。現在應該就可以看到初步的柏拉圖雛形了。
柏拉圖製作13

Step 12. 設定Y軸座標刻度
用滑鼠右鍵點擊Y軸主座標刻度,點選快顯功能表中的「Format Chart Area…」。

柏拉圖製作14

Step 13. 將【Maximum:】改選為【Fixed】,並在其後面的空格中輸入【242】,這個值就是表格中第一組資料累加後的總值;建議在【Minimum:】的後面也輸入【0】,以確保其最小值從0開始。
柏拉圖製作15

Step 14. 設定Y軸座標刻度
依照Step12~13的方法,點擊次座標軸的刻度,一樣點選顯功能表中的「Format Chart Area…」。在【Maximum:】的後面輸入【1】就可以了,因為【1】就
表示100%。這樣整個比例看起來就正常多了。
柏拉圖製作16

Step 15. 接下來是重點要設定彎折線與柱狀突的零點重疊。
點擊柏拉圖的圖形啟動圖表功能表,選擇「Layout」功能表,然後選擇「Axes」下拉功能表,點選「Secondary Horizontal Axis」,再點選「Show Left to Right Axis」。
柏拉圖製作17

Step 16. 出現X軸的次作座標,在它的上面點選滑鼠右鍵,從快顯功能表上點選「Format Axis」。
柏拉圖製作18

Step 17. 在【Format Axis】對話框中作如下的設定:
1. Major tick mark type: 變更到「None」。這樣可以隱藏次X軸的座標。
2. Axis label:變更到「None」。這樣可以隱藏次X軸的座標的標籤。
3. Position: 變更到「On tick marks」。這樣就可以將折線圖的原點移到左下角的零點位置並與柱狀圖重疊。這裡就是重點了。
柏拉圖製作19

Step 18. 顯示百分比及不良數值的標籤。
基本上柏拉圖已經完成了,接下來的動作應該只是美化。
用滑鼠右鍵點擊折線圖,在快顯功能表上點選「Add Data Labels」,就會出現各個這點的百分比數值標籤。
可以再用右鍵點擊柱狀圖,一樣在快顯功能表上點選「Add Data Labels」,就會出現柱狀圖的數值。
柏拉圖製作20

Step 19. 調整柱狀圖的標籤位置。
用滑鼠右擊柱狀圖的標籤,在快顯功能表上選擇「Format Data Labels」。
柏拉圖製作21

Step 20. 建議在【Label Position】的地方選擇「Inside End」,讓標籤出現在柱狀圖內部頂端的位置。其實這些標籤也可以手動用滑鼠個別拖拉移動其位置。
柏拉圖製作22

Step 21.最後的柏拉圖顯示如下,大家可以再自行作一些微調,有些個別標籤也可以考慮刪除,如0%及100%。
柏拉圖製作23

完成了囉!真的寫到頭昏腦脹的,重看了一次發現錯別字特別多,這已經是訂正過的版本了,如果還是有不清楚的地方,留言討論囉!


延伸閱讀:
如何使用Excel2007建立常態分布曲線圖表
何謂製程能力?
製程能力改善步驟流程圖
製程能力解析(Process Capacity Analysis)

關於統計製程SPC:
製程能力介紹─製程能力的三種表示法
製程能力介紹 ─ Cp之製程能力解釋
製程能力介紹 ─ Ck之製程能力解釋
製程能力介紹 ─ Cpk之製程能力解釋

製程能力介紹 ─ 製程能力的評估與改善對策




首次留言要通過審核之後才會出現在版面上,請大家不要重覆留言。也歡迎你訂閱本部落格的最新文章,當有新文章時會主動以電子郵件通知你。

Comments

我也看得頭昏眼花的.但是如果學起來就是自己的.感謝狂人大哥分享

真是受益良多,感謝你

狂人高手大哥谢谢!你有没有中文的我看不懂英文的

實在很對不起,我沒有中文版。

很棒~多虧有狂人大哥的整理,謝謝

candy;
可惜我只有英文的版本。將就一下吧!

谢谢您的分享,之前想看,一看是英文版的就没看了。这次认真的看完了,步骤非常的详细,看完后感觉并不难。您是台北的吗?看您用的繁体字。

hmily,
包涵一下,我的手上就只有英文版的Excel,所以當然是以它作範例了。
文章中的繁體字與用語應該都與你平常所接觸的不同,所以應該不難猜到我的所在地,但台灣可不只有台北啊!

用excel很有用,谢谢分享哈, 有个疑问,这样画出来的pareto,其实柱状和曲线都表示一个内容,只将柱状的数据用曲线百分比表示出来,怎样显示出来80/20rule?我也在学习中,个人认为主坐标X轴表示问题类型,Y轴表示问题出现的频率,次坐标是否可以用X轴表示问题原因Y轴表示原因频率?这样就可以表示较少的原因引起较多的问题,谢谢哈

Grace;
柏拉圖的次座標軸就是用百分比來表示問題的不良率(發生的頻率),
因為博拉圖是把要因由左到右,依大小排列,
所以只要對應到80%以上的部良率就可以貴納出80/20的問題點了。
通常都是前三個要因就可以形成80%以上的不良率喔!

Thank you for your interpretation on pareto chart making in EXCEL.And I got another trouble which requires your favour to help to solve it. WHen I access a website,http://www1.asknlearn.com/rosythweb/social/presentations/Wits/QC_Convention.html, I find the a pareto chart made of cylinders. THe legend presents each cylinder.

I have no idea how the cylinders and the legend emerge, although I tried many time but can not find the format.

THank you in advance

Albert;
I’m sorry that I can’t make this one.
I believe this cylinder pareto make by chart but the line was made by manual. Also the 2nd Y- axial scale make by manual since the percentage scale is uneven.

熊sir:
小小提醒筆誤部分,文中的step 1中項3應為:在C4的地方輸入公式[=C3+B4]
才符合data相加之結果
辛苦了~您的文章令許多人受益良多
Thanks!!

louis;
謝謝你的反饋。

謝謝!!我跟著您的步驟做,有做成功哦!!

Ariel;
恭喜你!
老實說第一次做有點複雜,第二次就熟悉了。

請教Step 8. 點選第二組資料是如何進行看不懂.3Q

wamsloy;
先點選第一組資料的「柱狀圖」,在按往下鍵,就可以選到第二組資料了。

謝謝版大詳細說明;
英文版好像也沒差,因為都在相對位置上(我用的是2010版),再次謝謝版大圖文並茂,太贊了!!

請問一下:Step 7.中 出現【Edit Series】的對話框,把滑鼠游標點在【Series values:】,圖中數列值後面=0.00%;我的數列值後面=1,填入【=Sheet2!$D$2:$D$6]後顯示{參照無效},該如何解決?

hn8697;
建議你先依照文章中的說明使用一個新的EXCEL檔案,並依照文章說明一步一步作業,沒有畫面很難判斷你的問題點。
一般的{參照無效}應該是儲存格的內容不對,很有可能就是前面的Sheet名稱或其他手動輸入錯誤造成。

請教您~我將第二組資料改為折線圖後,第一組的直條圖就不見了耶…?

桂;
這是因為你的百分比超過100%,多了兩位數,把第二組資料的百分比條正常就可以了。

發表迴響

(必)

(必)