我的最终目标是确定与特定名称关联的唯一值的数量。A 列是数据透视表中的第一列，其中包含名称列表（某些名称列出多次）。B 列是与名称关联的值的列表。C 列是 B 列左侧的 2 个数字。D 列是名称列表（与 A 中相同），但每个名称仅列出一次。在 EI 列中，希望通过引用 D 列中的唯一名称来了解 C 列中有多少个唯一值与 A 列中的每个唯一名称相关联。
我可以使用公式的静态范围来做到这一点
=SUMPRODUCT(1/COUNTIF(C7:C12,C7:C12))
我希望我的范围是动态的，以便当它引用 D 列中的名称时，它会在 A 列中为相同的名称创建值范围，这样它就知道在 C 列中的哪个范围内计算唯一值的数量。
我还可以使用 MATCH 公式来获取第一个值的位置：
="C"&MATCH(D5,$A$1:$A$3879,0)
并使用 MAXIF 公式来获取最后一个值的位置：
="C"&MAX(INDEX(ROW($1:$3879)*($A$1:$A$3879=D5), , ))
将它们放在一起，我就能得到我想要的动态范围。在这个例子中，它是 C7:C12。
我的问题是将这个动态范围插入到我的原始公式中。我希望我可以将它们插入到我的原始公式中，它会将其读取为一个范围。
=SUMPRODUCT(1/COUNTIF("C"&MATCH(D5,$A$1:$A$3879,0)&":"&"C"&MAX(INDEX(ROW($1:$3879)*($A$1:$A$3879=D5), , )),"C"&MATCH(D5,$A$1:$A$3879,0)&":"&"C"&MAX(INDEX(ROW($1:$3879)*($A$1:$A$3879=D5), , ))))
这不管用。我也试过使用文本公式和地址公式来帮助创建范围。但都没有用。
有没有办法让 SUMPRODUCT 公式将我的 match 函数和 maxif 函数读取为一个范围？或者还有其他方法可以做到这一点？
答案1
使用索引：
=INDEX(C:C,MATCH(D5,$A$1:$A$3879,0))
对于全系列，您可以使用：
INDEX(C:C,MATCH(D5,$A$1:$A$3879,0)):INDEX(C:C,MAX(INDEX(ROW($1:$3879)*($A$1:$A$3879=D5), , )))
因此你的 SUMPRODUCT 将是：
=SUMPRODUCT(1/COUNTIF(INDEX(C:C,MATCH(D5,$A$1:$A$3879,0)):INDEX(C:C,MAX(INDEX(ROW($1:$3879)*($A$1:$A$3879=D5), , ))),INDEX(C:C,MATCH(D5,$A$1:$A$3879,0)):INDEX(C:C,MAX(INDEX(ROW($1:$3879)*($A$1:$A$3879=D5), , )))))
但最终你的原始公式可以简化：
=SUMPRODUCT(($A$1:$A$3879=D5)/COUNTIFs($C$1:$C$3879,$C$1:$C$3879,$A$1:$A$3879,"<>"&D5))
或者使用 Excel 365 Excel
=ROWS(UNIQUE(TOCOL(FILTER($C$1:$C$3879,$A$1:$A$3879=D5)),3))