有一种相当常见的伪 COUNTUNIQUE 方法,基于和积 https://support.office.com/en-us/article/SUMPRODUCT-function-57a7bfa7-f74d-4ead-8c93-57f759c8f616 and COUNTIF https://support.office.com/en-US/article/COUNTIF-function-E0DE10C6-F885-4E71-ABB4-1F464816DF34函数看起来像这样。
=SUMPRODUCT(1/COUNTIF(A2:A19, A2:A19&""))
但这确实弥补了条件。要添加条件,您需要更改为COUNTIFS 函数 https://support.office.com/en-us/article/COUNTIFS-function-53C4DC8E-0E5B-4E32-93DF-9CA5E7DA89ED。此外,除法运算的分子和分母都必须根据条件和条件的倒数进行调整,以便返回真实的计数而不会出现错误#DIV/0!
.
=SUMPRODUCT(((C$2:C$19>=F2)*(C$2:C$19<=G2)*($B$2:$B$19=E2))/
(COUNTIFS(A$2:A$19, A$2:A$19&"", C$2:C$19, ">="&F2, C$2:C$19, "<="&G2, $B$2:$B$19, E2)+
(C$2:C$19<F2)+(C$2:C$19>G2)+($B$2:$B$19<>E2)))
现在该公式很难维护,因为行数不断添加和删除。要创建动态范围,我们可以INDEX https://support.office.com/en-us/article/index-function-0ee99cef-a811-4762-8cfb-a222dd31368a每个完整的列和用途MATCH https://support.office.com/en-us/article/match-function-0600e189-9f3c-4e4f-98c1-943a0eb427ca找到最后一个日期作为每列的终止符。
=SUMPRODUCT(((C$2:INDEX(C:C, MATCH(1E+99,C:C ))>=F2)*(C$2:INDEX(C:C, MATCH(1E+99,C:C ))<=G2)*($B$2:INDEX(B:B, MATCH(1E+99,C:C ))=E2))/
(COUNTIFS(A$2:INDEX(A:A, MATCH(1E+99,C:C )), A$2:INDEX(A:A, MATCH(1E+99,C:C ))&"", C$2:INDEX(C:C, MATCH(1E+99,C:C )), ">="&F2, C$2:INDEX(C:C, MATCH(1E+99,C:C )), "<="&G2, B$2:INDEX(B:B, MATCH(1E+99,C:C )), E2)+
(C$2:INDEX(C:C, MATCH(1E+99,C:C ))<F2)+(C$2:INDEX(C:C, MATCH(1E+99,C:C ))>G2)+($B$2:INDEX(B:B, MATCH(1E+99,C:C ))<>E2)))
是的,这看起来很混乱,但它动态地添加和减去 SUMPRODUCT 函数处理的行,因此计算效率非常高。
¹ For an explanation on how this works see Count Unique with SUMPRODUCT() Breakdown http://answers.microsoft.com/en-us/office/wiki/office_2003-excel/count-unique-with-sumproduct-breakdown/1bc18979-4172-4537-993e-3897f74d6a5b.