Note: The other languages of the website are Google-translated. Back to English

在Excel中創建Pareto圖表

在Excel中,一個pareto圖表由一個柱形圖和一個折線圖組成,其中柱形條表示降序的頻率值,而該線則表示第二個軸上的累積總數。 它用於分析產品缺陷的重要區域,並確定可以提高公司價值的改進措施。 若要創建一個pareto圖表以在Excel工作表中顯示最常見的失敗原因和產品缺陷,本文將為您提供幫助。


在Excel 2016及更高版本中創建一個簡單的Pareto圖表

如果您具有Excel 2016及更高版本,則有一個內置功能-Pareto圖表,它可以幫助您快速輕鬆地插入pareto圖表。 請執行以下操作:

1。 選擇要基於其創建圖表的數據范圍。

2。 然後,點擊 插入 > 插入統計圖 > 帕累托,請參見屏幕截圖:

3。 然後,一次創建了一個pareto圖表,如下圖所示:

4。 然後,您可以編輯圖表標題並根據需要添加數據標籤,請參見屏幕截圖:


在Excel 2013和更早版本中創建簡單的pareto圖表

如果您使用的是Excel 2013和更早版本,則應一一應用以下步驟:

首先,準備用於創建pareto圖表的數據:

1。 您應該按降序對數據進行排序,請選擇單元格B2,然後單擊 數據 > 將Z排序到A,請參見屏幕截圖:

2。 然後數據范圍已按降序排序,請參見屏幕截圖:

3。 然後,通過鍵入此公式來計算累積計數 = B2 在這種情況下進入單元格C2,然後按 Enter 鍵。 看截圖:

4。 然後輸入此公式 = C2 + B3 進入單元格C3,並將填充手柄向下拖動到單元格,請參見屏幕截圖:

5。 獲取累計數量後,請繼續計算累計百分比,輸入以下公式: = C2 / $ C $ 11 進入單元格D2,並將填充手柄向下拖動到所需的單元格,請參見屏幕截圖:

6。 然後,您應該將十進制值轉換為百分比值,選擇公式單元格,然後單擊 首頁 > 百分比樣式,請參見屏幕截圖:

二,根據數據創建圖表

7。 準備好數據後,請按以下順序在A列,B列和D列中選擇數據 按Ctrl 鍵,請參見屏幕截圖:

8。 然後,單擊 插入 > 插入柱形圖或條形圖 > 聚集列,請參見屏幕截圖:

9。 您將獲得如下圖所示的圖表:

10。 然後,右鍵單擊一個紅色條(累積百分比),然後選擇 更改系列圖類型 從上下文菜單中,查看屏幕截圖:

11。 在 更改圖表類型 對話框中的 所有圖表 標籤,點擊 優惠組合 左窗格中的選項 選擇數據系列的圖表類型和軸 列錶框,單擊“累積”字段中的下拉列表,然後選擇 符合標記圖 類型,然後選中復選框,請參見屏幕截圖:

備註:如果您具有Excel 2010或2007,則首先應將圖表類型更改為折線圖,然後右鍵單擊該折線圖並選擇 格式化數據系列,在中 格式化數據系列 對話框,單擊 系列選項 並檢查 副軸 在右側部分。

12。 然後,單擊 OK 按鈕,您將獲得如下圖所示的圖表:

13。 然後,右鍵單擊百分比軸,然後選擇 格式軸 上下文菜單中的選項,請參見屏幕截圖:

14。 在 格式軸 窗格中的 軸選項 標籤,在 最大值,將數字設置為 1.0 進入文本框,並且已經成功創建了pareto圖表,請參見屏幕截圖:

15。 最後,您可以更改圖表標題並根據需要添加數據標籤。


在Excel中創建動態Pareto圖表

在本節中,我將討論如何使用滾動條創建動態pareto圖表。 當您通過單擊或移動滾動條來更改目標值時,圖表將自動突出顯示問題欄,如下面的演示所示。

要創建此類型的pareto圖表,請按以下步驟操作:

1。 首先,計算C列中的累計百分比,請將此公式應用於單元格C2中:

=SUM($B$2:B2)/SUM($B$2:$B$11)

2。 然後將填充手柄向下拖動到單元格,並將十進制數字格式設置為百分比值,請參見屏幕截圖:

3。 接下來,您需要三個幫助器單元,分別是“目標”,“累積值”,“滾動條鏈接值”,以進行一些計算,如下圖所示:

4。 創建三個單元格後,然後單擊 開發人員 > 插入 > 滾動條(窗體控件),然後繪製一個滾動條,如下圖所示:

5。 然後,右鍵單擊滾動條,然後選擇 格式控制 從上下文菜單中,彈出 格式化對象 對話框中的 控制 標籤,設置相對值並指定鏈接的單元格,如下圖所示:

6。 然後,單擊 OK 按鈕關閉對話框。 現在,請分別在單元格B14和B15中鍵入以下兩個公式,然後將它們格式化為百分比值:

B14: =B16/100
B15: =IFERROR(INDEX($C$2:$C$11,IFERROR(MATCH($B$14,$C$2:$C$11,1),0)+1),1)

7。 現在,您應該在原始數據旁邊創建兩個幫助器列,並在單元格D2和E2中輸入以下兩個公式:

D2: =IF($B$15>=C2,B2,NA())
E2: =IF($B$15<C2,B2,NA())

8。 然後將公式複製到其他單元格,如下面的屏幕截圖所示:

9。 創建數據後,請按住A鍵,然後在C列,D列,E列中選擇數據。 按Ctrl 鍵,然後單擊 插入 > 插入柱形圖或條形圖 > 聚集列,並插入一個圖表,如下圖所示:

10。 然後,選擇圖表,然後單擊 設計 > 更改圖表類型更改圖表類型 對話框中的 所有圖表 標籤,點擊 優惠組合 在左窗格中,然後單擊 自定義組合圖,然後在 選擇數據系列的圖表類型和軸 部分,請為每個數據系列指定以下操作:

  • 累計%:折線圖類型,然後檢查 副軸 複選框;
  • 高亮條:聚集柱形圖類型;
  • 剩餘酒吧:聚集柱形圖類型。

11。 然後,點擊 OK 按鈕關閉對話框,您將獲得如下圖所示的圖表:

12。 然後,右鍵單擊百分比軸,然後選擇 格式軸 選項,在展開後 格式軸 窗格中的 軸選項 標籤,在 最大值,將數字設置為 1.0 進入文本框,請參見屏幕截圖:

13。 然後,右鍵單擊圖表中的任何條形系列,然後選擇 格式化數據系列 格式化數據系列 窗格中的 系列選項 標籤,輸入 100% OFF系列重疊 文本框,請參見屏幕截圖:

14。 最後,您可以將滾動條移動到圖表下方,然後輸入以下公式: =“目標”&TEXT(B14,“ 0%”) 放入滾動條下方的單元格中以獲取目標百分比值,請參見屏幕截圖:

15。 現在,已經創建了動態pareto圖表,您可以根據需要更改突出顯示的條的填充顏色,並且在使用滾動條更改目標值時,將自動更新pareto圖表,如下圖所示:


下載帕累托圖樣本文件


視頻:在Excel中創建帕累托圖


最佳辦公效率工具

Kutools for Excel-幫助您從人群中脫穎而出

您想快速,完美地完成日常工作嗎? Kutools for Excel具有300個強大的高級功能(合併工作簿,按顏色求和,拆分單元格內容,轉換日期等),並為您節省80%的時間。

  • 專為1500個工作方案而設計,可幫助您解決80%的Excel問題。
  • 每天減少數千次鍵盤和鼠標的點擊,減輕您疲倦的眼睛和手的疲勞。
  • 在3分鐘內成為Excel專家。 不再需要記住任何痛苦的公式和VBA代碼。
  • 30天無限制免費試用。 60天退款保證。 免費升級和支持2年。
Excel功能區(已安裝Kutools for Excel)

Office選項卡-在Microsoft Office(包括Excel)中啟用選項卡式閱讀和編輯

  • 一秒鐘即可在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標單擊,告別鼠標手。
  • 查看和編輯多個文檔時,可將您的工作效率提高50%。
  • 像Chrome,Firefox和新的Internet Explorer一樣,為Office(包括Excel)帶來高效選項卡。
Excel的屏幕截圖(已安裝Office選項卡)
按評論排序
留言 (0)
還沒有評分。 成為第一位評論!
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0  字符
推薦地點