☰

Excel 公式助手 - 查找缺失的数字

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 公式助手 - 查找缺失的数字

我从数据库导出了一个包含Roles 和Account IDs 的电子表格。每个帐户 ID 有 4-5 个角色名称。我需要一个公式来显示没有 角色的任何帐户RBD。有人能帮忙吗？

例子：

Account ID   Account Name       Team        Role
1            123 AG SERVICE     National    MANG
1            123 SERVICE        National    CAP
1            123 AG SERVICE     National    RGL
1            123 AG SERVICE     National    CS
1            123 AG SERVICE     National    XSM
1            123 AG SERVICE     National    RBD
1            123 AG SERVICE     National    Q4
2            NORTHEAST INC      National    MANG
2            NORTHEAST INC      National    CAP
2            NORTHEAST INC      National    RGL
2            NORTHEAST INC      National    CS
2            NORTHEAST INC      National    XSM

我希望查询显示2为不具有角色的帐号RBD。

任何帮助都将不胜感激！

答案1

SumProduct是你的朋友。SumProduct允许您对一系列数据逐行进行多项测试。

在单独的选项卡上，在 A 列中列出您的不同帐户 ID。在 B 列中，我们将获得每个 AccountID 的所有行数，而不管 countif 公式如何……=countif(Sheet1!A:A, Sheet2!A1)假设 Sheet1 包含您的数据，而我们正在处理 Sheet2。

在 C 列中，我们将部署 sumproduct。我们将要返回每个 accountID 中 Role 不等于 RBD 的行数。这将看起来像=SUMPRODUCT((A1=Sheet1!$A$1:$A$13)*(Sheet1!$D$1:$D$13<>"RBD")*1)。它所做的是测试范围内每行括号中的每个条件的真/假。然后它将两个条件都为真的所有行加起来。这里的两个条件是 Sheet1 中的 A 列具有我们感兴趣的 AccountID，并且 Sheet1 中的 D 列不包含 RBD。

在第二个选项卡的 D 列中，从 B 列中减去 C 列：=B1-C1。如果不是 0，则 accountID 缺少 RBD。

=if(countif(Sheet1!A:A, Sheet2!A1)-SUMPRODUCT((A1=Sheet1!$A$1:$A$13)*(Sheet1!$D$1:$D$13<>"RBD")*1) = 0, "", "Missing RBD!")如果您追求简洁的话，可以将所有这些都写成一个公式，而不是将其分散到 B、C 和 D 列中。

最终，您可能还是最好使用 SQL 在数据库上执行此操作。每当您使用 sumproduct（或需要它）时，最好使用 SQL，因为它更像是数据库类型的函数。例如

SELECT distinct_account_ids.Account_ID FROM (SELECT DISTINCT ACCOUNT_ID FROM <table>) as distinct_account_ids LEFT OUTER JOIN (SELECT Account_ID FROM <table> WHERE ROLES = 'RBD' GROUP BY Account_ID ) as ids_with_rbd ON distinct_account_ids.Account_id = ids_with_rbd.Account_id WHERE ids_with_rbd.Account_id is null;

相关内容