excel - Excel公式在行簇内计数（由相同的值定义）和相邻列的值对于给定的簇是相同的

Question

我有一个包含大型数据集的电子表格，需要一个公式来实现以下目标（见附图）：

目标是让 Excel 执行以下操作：

1- 根据 col 中的相同值识别和聚类行。A（在这种情况下，我们应该有集群“Alpha、Bravo、Charlie”）

2- 在每个集群中，如果 col 中的所有值都返回一个新计数（例如，称为“个人计数”）。集群的 B 是“个人”

3- 如果任何行在 col 中包含值“实体”​​。B 对于给定的集群，返回一个新的计数（例如，称为“实体计数”）。

基于上面的例子，输出应该是：

个体计数 = 1（仅 Alpha 仅包含与其行关联的“个体”）实体计数 = 2（Bravo 和查理都至少包含与其行关联的“实体”）

标签： excelexcel-formula

Answer 1

下面的公式确实很长，需要很长时间才能得出。要考虑的变量太多了。我尝试了频率函数，但无法让它与文本单元格一起工作，包括其他函数的变体。公式可以放在单元格 C1 中并根据需要填写：

=IF(IF(AND(A1<>A2,A1="Bravo"),SUM(IF(A$1:A$10&B$1:B$10="BravoIndividual",1,""),0))/SUM( IF(A$1:A$10&B$1:B$10="BravoIndividual",1,""),0)+IF(AND(A1<>A2,A1="Bravo"),SUM(IF(A$1:A $10&B$1:B$10="BravoEntity",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="BravoEntity",1,""),0)+IF (AND(A1<>A2,A1="Alpha"),SUM(IF(A$1:A$10&B$1:B$10="AlphaIndividual",1,""),0))/SUM(IF(A$1 :A$10&B$1:B$10="AlphaIndividual",1,""),0)+IFERROR(IF(AND(A1<>A2,A1="Alpha"),SUM(IF(A$1:A$10&B $1:B$10="AlphaEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="AlphaEntity",1,"")) ,"")+IF(AND(A1<>A2,A1="查理"),SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1, ""),0)+IFERROR(IF(AND(A1<>A2,A1="查理"),SUM(IF(A$1:A$10&B$1:B$10="CharlieEntity",1,""), 0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="CharlieEntity",1,"")),"")<1,"",IF(AND(A1 <>A2,A1="Bravo"),SUM(IF(A$1:A$10&B$1:B$10="BravoIndividual",1,""),0))/SUM(IF(A$1:A$10&B $1:B$10="BravoIndividual",1,""),0)+IF(AND(A1<>A2,A1="Bravo"),SUM(IF(A$1:A$10&B$1:B$10=" BravoEntity",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="BravoEntity",1,""),0)+IF(AND(A1<>A2, A1="Alpha"),SUM(IF(A$1:A$10&B$1:B$10="AlphaIndividual",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="AlphaIndividual",1,""),0)+IFERROR(IF(AND (A1<>A2,A1="Alpha"),SUM(IF(A$1:A$10&B$1:B$10="AlphaEntity",1,""),0)),"")*IFERROR(SUM( IF(A$1:A$10&B$1:B$10="AlphaEntity",1,"")),"")+IF(AND(A1<>A2,A1="查理"),SUM(IF(A$1 :A$10&B$1:B$10="CharlieIndividual",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0) +IFERROR(IF(AND(A1<>A2,A1="查理"),SUM(IF(A$1:A$10&B$1:B$10="CharlieEntity",1,""),0)),"" )*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="CharlieEntity",1,"")),""))A$10&B$1:B$10="AlphaIndividual",1,""),0)+IFERROR(IF(AND(A1<>A2,A1="Alpha"),SUM(IF(A$1:A$10&B$1 :B$10="AlphaEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="AlphaEntity",1,"")), "")+IF(AND(A1<>A2,A1="查理"),SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0))/SUM (IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0)+IFERROR(IF(AND(A1<>A2,A1="Charlie"),SUM(IF(A $1:A$10&B$1:B$10="CharlieEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="CharlieEntity",1 ,"")),""))A$10&B$1:B$10="AlphaIndividual",1,""),0)+IFERROR(IF(AND(A1<>A2,A1="Alpha"),SUM(IF(A$1:A$10&B$1 :B$10="AlphaEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="AlphaEntity",1,"")), "")+IF(AND(A1<>A2,A1="查理"),SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0))/SUM (IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0)+IFERROR(IF(AND(A1<>A2,A1="Charlie"),SUM(IF(A $1:A$10&B$1:B$10="CharlieEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="CharlieEntity",1 ,"")),""))A$10&B$1:B$10="AlphaEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="AlphaEntity",1," ")),"")+IF(AND(A1<>A2,A1="查理"),SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0 ))/SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0)+IFERROR(IF(AND(A1<>A2,A1="Charlie"),SUM (IF(A$1:A$10&B$1:B$10="CharlieEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="查理实体",1,"")),""))A$10&B$1:B$10="AlphaEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="AlphaEntity",1," ")),"")+IF(AND(A1<>A2,A1="查理"),SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0 ))/SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0)+IFERROR(IF(AND(A1<>A2,A1="Charlie"),SUM (IF(A$1:A$10&B$1:B$10="CharlieEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1:A$10&B$1:B$10="查理实体",1,"")),""))CharlieIndividual",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0)+IFERROR(IF(AND(A1<> A2,A1="查理"),SUM(IF(A$1:A$10&B$1:B$10="CharlieEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1 :A$10&B$1:B$10="CharlieEntity",1,"")),""))CharlieIndividual",1,""),0))/SUM(IF(A$1:A$10&B$1:B$10="CharlieIndividual",1,""),0)+IFERROR(IF(AND(A1<> A2,A1="查理"),SUM(IF(A$1:A$10&B$1:B$10="CharlieEntity",1,""),0)),"")*IFERROR(SUM(IF(A$1 :A$10&B$1:B$10="CharlieEntity",1,"")),""))