以計數百分比對 Excel 資料透視表進行排序

Question

我的來源資料顯示了以下格式的時間表批准（大約 850 名員工和 200 名經理）：

  Employee Name  Manager Name  TS Approved? 
  Employee 1     Manager 1     No
  Employee 2     Manager 2     Yes
  Employee 3     Manager 3     Yes
  Employee 4     Manager 1     No
  Employee 5     Manager 3     No

我製作了一個資料透視表，如下所示（未批准的百分比只是我在資料透視表旁邊的公式）：

                 Count TS Approved? 
  Manager Name   No    Yes   Total  % Unapproved
  Manager 1      11          11     100%
  Manager 2      6     10    16     38%
  Manager 3      7     18    25     28%
  Manager 4      5     8     13     38%
  Manager 5      5     4     9      56%
  Manager 6            3     3      0%
  Manager 7      5           5      100%

我需要排序以按計數獲得前 5 個最差批准者 - 但只有 5 個。

• 如果我在“否”列上使用資料透視表“前 10 個”，它將顯示 6 個值，因為它不區分三個 5
• 我嘗試添加百分比，這樣我就可以按% 對最大-最小排序，然後按計數對最大-最小排序，然後手動取前5 個- 因為5/5 (100%) 未批准比5/8 (38% ) 更糟 -但不知道如何按 % 排序。
• 如果我將其作為公式新增至資料透視表之外（如上所示），Excel 將不允許我根據這些資料對資料透視表進行排序。 “您無法移動資料透視表報表的一部分...”
• 如果我添加資料以在表中顯示為“父行總計的百分比”，它仍然只按計數排序

誰能想到我怎麼能讓它做我想做的事，即？

                 Count TS Approved? 
  Manager Name   No    Yes   Total  % Unapproved
  Manager 1      11          11     100%
  Manager 3      7     18    25     28%
  Manager 2      6     10    16     38%
  Manager 7      5           5      100%
  Manager 5      5     4     9      56%
  Manager 4      5     8     13     38%
  Manager 6            3     3      0%

注意：我可以使用 countif 而不是資料透視表輕鬆完成此操作，但如果可能的話，理想情況下希望使用資料透視表格式。

謝謝你！

路易絲

Answer 1

有趣的挑戰。其中一些問題包括：

• 現場計算沒有足夠的靈活性來獲得您所需的
• 雖然您可以將數字顯示為總數的百分比，並且看起來您可以對其進行排序 - 它實際上是根據基礎數字進行排序。

我有一個使用表和資料透視表的解決方案。可能有更簡單的解決方案可用。步驟是（在 Excel 2016 中完成）：

1. 在原始數據中選擇。選擇“插入”功能區並點擊“表格”
2. 在新表中，插入 %NotApproved 的計算
3. 選擇“表格工具”“設計”功能區，然後按一下“使用資料透視表進行匯總”
4. 建立一個簡單的資料透視表，其中「經理名稱」作為行，「%NotApproved」作為值。
5. 以 %NotApproved 降序對經理姓名進行排序

這是一個例子。以下是 30 行「原始資料」的片段，類似於您問題中的描述...

選擇“插入”功能區並點擊“表格”...

您可以獲得更好格式的數據。選擇 D1，在最後一個列標題旁邊並輸入“%No” - 這將在表中建立一個帶有新標題的新列。在儲存格 D2 中，輸入下列公式...

=IF([@[TS Approved?]]="No",1,0)/COUNTIF([Manager Name],"="&[@[Manager Name]])*100

當您按下回車鍵時，它會自動填入表格中。這個公式的作用是：

1. IF([@[TS Approved?]]="No",1,0)如果核准的時間表為“否”，則取得值 1。
2. COUNTIF([Manager Name],"="&[@[Manager Name]])決定該行中的經理在表格中出現的次數。
3. 1 除以 2 乘以 100 的結果

桌子現在看起來像這樣....

選擇「表格工具」「設計」功能區，然後按一下「使用資料透視表匯總」。建立資料透視表，如下所示...

...然後對其進行排序...

....為了得到這個...

儘管設定步驟似乎很多，但維護表非常容易，並且這會自動保持資料透視表的維護。

Answer 2

這可能很舊，但我想我找到了解決這個問題的方法。

1. 第一步只是透過傳統的右鍵單擊然後“將值顯示為”然後“行總計的%”來簡單地顯示未批准的百分比
2. 然後點擊“行標籤”附近的下拉按鈕
3. 按“核准的 TS 數量”選擇“降序”
4. 選擇“更多排序選項”，然後按一下對話方塊中的“更多選項”
5. 取消勾選「每次更新報告時自動排序」方塊（這是關鍵步驟）
6. 選擇「所選列中的值」作為「否」列的第一個儲存格
7. 按一下“確定”
8. 若要自動重新更新，請重複步驟 2、4 和 5，但這次選取「每次更新報告時自動排序」。

Answer 3

不知道為什麼，但今天早上早餐時我意識到兩件事...

1. 使用表固然很好，但也許只會增加問題的複雜性。
2. 儘管您將未批准百分比計算為經理負責的工時單的百分比，但您可能希望將其計算為所有未批准工時單的百分比。

所以我想我應該發布一個替代答案。

除了原始數據之外，%No在下面放置一個標題和此計算（並填寫）。

=IF(C2="No",1,0)/COUNTIF($C$2:$C$31,"="&C2)*100

如果此時間表未獲批准，則該公式將計算它佔所有未批准時間表的百分比。

您的原始數據現在看起來像這樣...

建立資料透視表並按%No 排序。

如果您仍希望未批准百分比為經理負責的時間表的百分比，請在 D 列中使用此等式。

=IF(C2="No",1,0)/COUNTIF($B$2:$B$31,"="&B2)*100