☰

按计数百分比对 Excel 数据透视表进行排序

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 pivot-table sorting
按计数百分比对 Excel 数据透视表进行排序

我有源数据显示时间表批准情况如下（针对大约 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%

注意：我可以使用 countifs 而不是数据透视表轻松地完成此操作，但如果可能的话，理想情况下希望使用数据透视表格式。

谢谢你！

路易丝

答案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 的结果

该表现在看起来像这样...

在此处输入图片描述

选择“表格工具”“设计”功能区，然后单击“使用数据透视表汇总”。构建数据透视表，使其看起来像这样...

在此处输入图片描述

...并对其进行排序...

在此处输入图片描述

... 得到这个...

在此处输入图片描述

虽然设置起来似乎有很多步骤，但维护表格却非常容易，并且可以自动保持数据透视表的维护。

答案2

这可能有点老了，但我认为我找到了解决这个问题的方法。

  1. 第一步是简单地显示未批准的百分比，方法是单击鼠标右键，然后选择“将值显示为”，然后选择“占行总数的百分比”
  2. 然后点击“行标签”附近的下拉按钮
  3. 按“TS 批准数量”选择“降序”
  4. 选择“更多排序选项”，然后单击对话框中的“更多选项”
  5. 取消选中“每次更新报告时自动排序”框（这是关键的一步）
  6. 选择“选定列中的值”作为“否”列的第一个单元格
  7. 单击“确定”
  8. 要重新自动更新，请重复步骤 2、4 和 5，但这次选中“每次更新报告时自动排序”。

答案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

相关内容