☰

如何在目标表中统一这两个公式？

tag-icon • tag-icon microsoft-excel worksheet-function
如何在目标表中统一这两个公式？

我有一个包含数十列和数万行的源表。我有一个包含六行的目标表。我想计算一个项目中有多少人并且只有该项目。由于不相关（且必要）的原因，每个项目中的人员（个人 ID）都会被多次列出。

目前在源表中，我有：

=SUM(--(FREQUENCY(IF((([Personal ID]=[@[Personal ID]])*([@[project name]]<>[project name])),[Personal ID]),[Personal ID])>0))

这就创造了任何其他项目字段，如果他们只参与此项目，则为 0；如果他们也参与其他项目，则为 1。

目标表中的公式为：

=SUM(--(FREQUENCY(IF((SourceTable[Any other project]=0)*([@[project name]]=SourceTable[project name]),SourceTable[Personal ID]),SourceTable[Personal ID])>0))

问题在于源表有数万行；第一个公式很慢，并且不必要地计算了数千次 - 每个个人 ID 只需计算一次。每次我对表进行不同的排序时，它也会重新计算。我该如何将其折叠到目标表的公式中？

我考虑在目标表中放置一个辅助列，并使用 textjoin 为我提供每个项目的所有个人 ID 列表，这些 ID 在每个行的一个单元格中。但是，ID 重复的次数太多，以至于有些项目会抛出 #value!，因为结果超出了 Excel 在一个单元格内的字符限制。

答案1

我想出了一个方法来做我需要的事情：

=SUM(--(FREQUENCY(sourcetable[personal id],sourcetable[personal ID])>0))-SUM(--(FREQUENCY(IF(([@[project name]]<>SourceTable[project name]),SourceTable[Personal ID]),SourceTable[Personal ID])>0))

这将获取所有个人 ID，并减去目标表中除此行之外的其他项目上工作的所有人员。

答案2

要计算单元格区域（在下面的示例中名为“数据”）中唯一值或不同值的数量，您可以使用基于 COUNTIF 和 SUMPRODUCT 函数的公式。

公式的工作原理 从内向外工作，COUNTIF 查看数据区域并计算每个值在数据中出现的次数。结果是一个数字数组，可能如下所示：{3;3;3;2;2;3;3;3;2;2}。

COUNTIF 完成后，结果将用作以 1 为分子的除数。在数据中出现一次的值在数组中显示为 1，但出现多次的值将显示为与倍数相对应的分数值。（即，在数据中出现 5 次的值将在数组中生成 5 个项目，值为 1/5 = .2）。

最后，SUMPRODUCT 函数将数组中的所有值相加并返回结果。

处理空白单元格如果数据可能包含空白单元格，则需要按如下方式调整公式：

=SUMPRODUCT(1/COUNTIF(data,data&"")) data&"" 表达式可防止当 data 中有空白单元格时，COUNTIF 创建的数组中出现零。它通过确保空单元格的标准为 "" 而不是零来实现这一点。这很重要，因为除数中的零将引发 #DIV/0 错误。因此，当有空白单元格时，此版本的公式不会引发错误，但它会在计数中包含空白单元格。如果要排除计数中的空白单元格，请使用：

=SUMPRODUCT((data<>"")/COUNTIF(data,data&"")) 性能低下？这是一个很酷很优雅的公式，但它的计算速度比使用 FREQUENCY 来计算唯一值的公式要慢得多。对于较大的数据集，您可能需要切换到基于 FREQUENCY 函数的公式。这是一个用于数值的公式，另一个用于文本值。

相关内容