我有一个表,它是这样的:
Customer ID | Review Flag | Date Received
London Official 27-May-14
London Official 29-May-14
Reginald Official 29-May-14
Reginald Official 29-May-14
Townie Official 05-Jun-14
FHAR_L Unofficial 05-Jun-14
Reginald Official 10-Jun-14
Akmed Official 10-Jun-14
PALNEES Unofficial 11-Jun-14
Akmed Official 11-Jun-14
Woody Official 11-Jun-14
Woody Official 11-Jun-14
Sarah Official 11-Jun-14
我想统计一下过去两周内独特的官方病例的数量。例如,这个特定表的结果将是5例,而不是7例。
我目前的实现是构造一个数据透视表,将客户ID作为行和值,然后在值列上运行计数函数作为输出。这样做的问题是用户需要每天都去更改数据透视表上的过滤器,这可能会让人很困惑,因为我们不是每天都遇到这样的情况。例如,由于有些月份有31天,出现的情况是,如果我们在31日没有收到病例,那么当我们在下个月的13日收到病例时,我们不应该计算30日收到的病例。所以理想情况下,我希望这个解决方案是基于公式的。
我读了很多关于在excel中计算唯一值的内容,但它们似乎都令人困惑和复杂。此外,它们似乎基于使其与COUNTIFS不兼容的条件,这是我在尝试考虑"审查标志=官方"one_answers"日期范围= cell1 <"时应该使用的功能。X <= cell2';
当然,我可以在VBA中做到这一点,但我想知道是否有一个基于公式的解决方案。
我用SUM(1/COUNTIFS(...))
方法计算了列表中唯一项的个数。
=SUM(IF(("Official"=R18:R1000)*(D18:D1000>TODAY()-14),1/COUNTIFS(R18:R100,"Official",C18:C1000,C18:C1000,D18:D1000,">"&TODAY()-14)),0)
其中列名R = review flag, D = date received, C = customer ID,数据集从第18行开始。
我认为这是如何工作的:我们有一个左边的数组(IF
数组)和右边的数组(COUNTIFS
)数组。COUNTIFS
数组查找每个绑定发生的次数,即在数组行[10]中,它将询问"'Woody, Official, 11- june -14'一起出现了多少次?"左边的数组查看哪些行符合我们的目标条件,每行返回真或假。然后,SUM
函数将右侧数组中的所有行相加,其中左侧数组表示该特定行是TRUE
,但正如我们之前对1/
所做的那样,我们将使每个绑定之和仅为1,从而模拟唯一的。
重要的事情要记住:
使用数组公式时,请确保所有数组的大小相同,否则会出现错误