☰

不要惧怕强力查询！

tag-icon • tag-icon microsoft-excel pivot-table
不要惧怕强力查询！

我正在使用 Excel 数据透视表创建要与他人共享的报告。我没有任何 VBA 或 PowerQuery 或 PowerBI 或编码经验，但对数据透视表很在行。

我的数据透视表的源数据来自这样的 Excel 文件（出于隐私考虑，使用虚假示例）：

项目 ＃ 任务 执行任务的人 此人是内部员工吗？
1 喷漆车 约翰 是的
1 质量控制 克里斯 是的
2 喷漆车 马特 不
2 质量控制 克里斯 是的
3 喷漆车 威尔逊 不
3 平衡轮胎 杰夫 不

我希望能够回答以下问题：

对于由非内部员工完成“喷漆汽车”任务的项目：

  1. 这些项目中有多少也履行了“质量控制”的任务？
  • 根据上表，答案应为“1 个项目”（项目 #2）
  1. 这些项目中有多少没有完成“质量控制”任务？
  • 根据上表，答案应为“1 个项目”（项目 #3）
  1. 列出符合上述问题 2 标准的项目编号。
  • 根据上表，答案应为“项目 #3”

重点是找出哪些项目的“油漆工作”任务是由外部完成的（由承包商而不是内部员工），但却没有进行任何质量控制，因为这代表着企业的质量风险——我们不确定我们的外部承包商有多好。

使用上面的表格，是否有任何方法可以通过数据透视表轻松回答这些问题？显然我的数据量更大，所以我想要一个数据透视表方法。

我曾尝试摆弄数据透视表中的东西，但 Excel 似乎被搞乱了，因为项目编号在数据的同一行中从来没有“喷漆汽车”和“质量控制”的任务——当我尝试这样做时，“项目编号计数”总是显示“0”。

答案1

确实是一个非常有趣的问题，但我担心您的数据结构会让数据透视表方法变得相当困难。另一种方法是使用公式化方法，这样您就可以相应地回答所有问题。

假设您的数据存储在范围内A1:D7，您可以将以下LET()语句放在工作表的某个位置：

=LET(
data, $A$2:$D$7,
proj,
UNIQUE(INDEX(data,,1)),
task1,
IFERROR(INDEX(data, MATCH(proj&"Paint car", INDEX(data,,1)&INDEX(data,,2), 0), 2), ""),
internal1,
IFERROR(INDEX(data, MATCH(proj&"Paint Car", INDEX(data,,1)&INDEX(data,,2), 0), 4), ""),
task2,
IFERROR(INDEX(data, MATCH(proj&"Quality control", INDEX(data,,1)&INDEX(data,,2), 0), 2), ""),
internal2,
IFERROR(INDEX(data, MATCH(proj&"Quality control", INDEX(data,,1)&INDEX(data,,2), 0), 4), ""),
criteria,
IF(internal1="yes", "internal",
IF((internal1="no")*(internal2="yes"), "checked", "to be checked")),
HSTACK(proj, task1, internal1, task2, internal2, criteria)
)

基本上，您要指定数据集，然后是项目编号。然后，您要提取“喷漆汽车”任务以及它是否在内部执行；“质量控制”的想法相同。根据这些数据，您可以根据IF()检查油漆是否“内部”并将其标记为“内部”的语句定义回答问题的标准。如果油漆不是内部的并且已检查，则将其标记为“已检查”，否则需要“待检查”。

理论上，您也可以将所有公式应用到各个列中并将其向下拖动，但该LET()语句允许您一次性指定所有内容并返回输出数组。这意味着您只需在一个单元格中设置公式，这样就非常易于维护。此外，如果需要，还可以使其在活动方面更具动态性。如果您只想返回项目编号和条件，您只需调整数组即可HSTACK()。

最终输出如下所示（手动添加标题）：

输出

因此，回答你的问题：

  1. 项目 #2 已选中
  2. 项目 #3 需要检查
  3. 这种方法会自动给出项目列表，只要你筛选出需要检查的项目即可

答案2

不要惧怕强力查询！

当我第一次开始使用 Power Query 时，我很生气，想起我曾经多次需要它，却没想到它会如此容易使用——

您需要做的大部分事情只需点击一下即可完成。保存原始备份并记住：您所做的一切都是可以恢复的。

相关内容