求助：多条件不重复个数的计算，

wangjguo44

lijinmiles1974 发表于 2014-2-16 22:00
大师帮忙解释一下这个公式可否？

1. =SUMPRODUCT((表源!$C$2:$C$61=$A3)*(表源!$D$2:$D$61=B$2)*(表源!$B$2:$B$61>0)*(表源!$H$2:$H$61=2)*(MATCH(IF((表源!$B$2:$B$61>0)*(表源!$H$2:$H$61=2),表源!$A$2:$A$61),IF((表源!$B$2:$B$61>0)*(表源!$H$2:$H$61=2),表源!$A$2:$A$61),)=ROW(表源!$1:$60)))

复制代码

1、应该说，公式的前几项(表源!$C$2:$C$61=$A3)*(表源!$D$2:$D$61=B$2)*(表源!$B$2:$B$61>0)*(表源!$H$2:$H$61=2)理解没多大问题，分别是渠道、地区、规模和“周”的条件，相乘后得到的是符合这三条件的为1，不合的为0；

2、关键是MATCH(IF((表源!$B$2:$B$61>0)*(表源!$H$2:$H$61=2),表源!$A$2:$A$61),IF((表源!$B$2:$B$61>0)*(表源!$H$2:$H$61=2),表源!$A$2:$A$61),)=ROW(表源!$1:$60)——
    a、因为你要的是不重复：“比如武汉市建行，只有一个网点7单，应该为1”，这个不重复，不是简单地只对A列网点去重复（按照去重原则，首次出现的留下来，再次出现的舍弃，如网点“中国建设银行湖北省三峡分行宜昌王家河支行”，若简单地去重复，在第一周出现了，保留，而到下面再出现，就不计了，这样第2周即使有这个网点也被排除在外，统计就不正确了，又如“农业银行湖北省武汉豹海分理处”，假定它在第1周没出现，而是第2周才首次出现，但首次出现时规模为0，这样当第2次出现，即使规模不为0，也已经被排除了，统计也就不正确了），而是要同时把规模及周的因素考虑进来（渠道和地区的信息已经包含在网点中），所以有IF((表源!$B$2:$B$61>0)*(表源!$H$2:$H$61=2),表源!$A$2:$A$61)，把去重复的数据（网点）限制在符合规模和周数条件的范围，在这个范围里去重复。
    b、函数MATCH()可以返回单列（或单行）区域（或数组）中元素首次出现的位置序号，因此可以用MATCH()求得的位置号是否等于该元素实际所处位置号来判断是否是首次出现（相等是，不等非，所以就有MATCH(IF((表源!$B$2:$B$61>0)*(表源!$H$2:$H$61=2),表源!$A$2:$A$61),IF((表源!$B$2:$B$61>0)*(表源!$H$2:$H$61=2),表源!$A$2:$A$61),)=ROW(表源!$1:$60)的判断；

3、把第1点和第2点相乘：(表源!$C$2:$C$61=$A3)*(表源!$D$2:$D$61=B$2)*(表源!$B$2:$B$61>0)*(表源!$H$2:$H$61=2)*(MATCH(IF((表源!$B$2:$B$61>0)*(表源!$H$2:$H$61=2),表源!$A$2:$A$61),IF((表源!$B$2:$B$61>0)*(表源!$H$2:$H$61=2),表源!$A$2:$A$61),)=ROW(表源!$1:$60))就得到这样一个数组，符合条件的为1，不合的为0，再用SUMPRODUCT()求和，最终得到符合条件的个数。

Johnny_Lyc

学习了，自己也试着写写看

lijinmiles1974

wangjguo44 发表于 2014-2-17 09:25
1、应该说，公式的前几项(表源!$C$2:$C$61=$A3)*(表源!$D$2:$D$61=B$2)*(表源!$B$2:$B$61>0)*(表源!$H$ ...

高深！慢慢消化，不懂再请教大师！

lijinmiles1974

wangjguo44 发表于 2014-2-17 09:25
1、应该说，公式的前几项(表源!$C$2:$C$61=$A3)*(表源!$D$2:$D$61=B$2)*(表源!$B$2:$B$61>0)*(表源!$H$ ...

2.a模模糊糊知道一点，2.b完全搞不懂了，sorry，对match用法查了几个地方，还是不懂，为什么有两个IF一样的啊，原谅我的无知，感觉自己像个文盲

lijinmiles1974

wangjguo44 发表于 2014-2-17 09:25
1、应该说，公式的前几项(表源!$C$2:$C$61=$A3)*(表源!$D$2:$D$61=B$2)*(表源!$B$2:$B$61>0)*(表源!$H$ ...

若是每天在表源里面添加数据，那个,=ROW(表源!$1:$60)该作如何自动改变，难道要手动加到最新行？

wangjguo44

lijinmiles1974 发表于 2014-2-20 21:39
2.a模模糊糊知道一点，2.b完全搞不懂了，sorry，对match用法查了几个地方，还是不懂，为什么有两个IF一样 ...

1、先看看该函数的帮助：MATCH  
返回在指定方式下与指定数值匹配的数组 中元素的相应位置。
语法
MATCH(lookup_value,lookup_array,match_type)
Lookup_value     为需要在数据表中查找的数值。
Lookup_array     可能包含所要查找的数值的连续单元格区域。Lookup_array 应为数组或数组引用。
Match_type     为数字 -1、0 或 1。Match-type 指明 Microsoft Excel 如何在 lookup_array 中查找 lookup_value。
    如果 match_type 为 1，函数 MATCH 查找小于或等于 lookup_value 的最大数值。Lookup_array 必须按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。
    如果 match_type 为 0，函数 MATCH 查找等于 lookup_value 的第一个数值。Lookup_array 可以按任何顺序排列。
    如果 match_type 为 -1，函数 MATCH 查找大于或等于 lookup_value 的最小数值。Lookup_array 必须按降序排列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...，等等。
    如果省略 match_type，则假设为 1。
说明
    函数 MATCH 返回 lookup_array 中目标值的位置，而不是数值本身。例如，MATCH("b",{"a","b","c"},0) 返回 2，即“b”在数组 {"a","b","c"} 中的相应位置。
    查找文本值时，函数 MATCH 不区分大小写字母。
    如果函数 MATCH 查找不成功，则返回错误值 #N/A。
    如果 match_type 为 0 且 lookup_value 为文本，lookup_value 可以包含通配符、星号 (*) 和问号 (?)。星号可以匹配任何字符序列；问号可以匹配单个字符。

2、关于公式中两个if——当lookup_value和lookup_array是同一数组，且match_type是0时，它返回的结果是lookup_value的每一个元素在本数组中首次出现的位置。（请体会一下=MATCH({2;2;1;3;1;3;3;2;4},{2;2;1;3;1;3;3;2;4},)的结果是{1;1;3;4;3;4;4;1;9}——数组中所有2首次出现的是第1个位置、所有1首次出现的是第3个位置、所有3首次出现的是第4个位置，所有4（只有一个）首次出现的是第9个位置），再用=MATCH({2;2;1;3;1;3;3;2;4},{2;2;1;3;1;3;3;2;4},)=ROW(A1:A9)的判断，就成了{1;1;3;4;3;4;4;1;9}={1;2;3;4;5;6;7;8;9}，其结果是{true;false;true;true;false;false;false;false;true}，只有首次出现的是true，对应位置的数据是2、1、3、4，达到了去重复的目的 ）


3、关于每天在表源里面添加数据，那个,=ROW(表源!$1:$60)该作如何自动改变——先说说后半段=ROW(表源!$1:$60)，从上面两点都可以得出，match()=row()的结构中，match中的区域可以在任何位置，而row的数值一定是从1开始（match中第一个元素首次出现的位置总是在第一个位置）而它的高度是与数组同一个高度：有9个元素，就是row($1:$9),有60个元素就是row($1:$60)，有1600个元素，就是row($1:$1600)，这两个数字不匹配，查找就不成功，出错；
其次，如果每天在表源里面添加数据，那么要自动改变的不光是ROW(表源!$1:$60)，而是前面的数组也要变，否则不匹配，必错。而要全部自动改变，公式就要用更多的函数了（indirect、或offset再加counta等等）

lijinmiles1974

wangjguo44 发表于 2014-2-21 08:43
1、先看看该函数的帮助：MATCH  
返回在指定方式下与指定数值匹配的数组 中元素的相应位置。
语法

终于明白了！谢谢大师！

lijinmiles1974

wangjguo44 发表于 2014-2-21 08:43
1、先看看该函数的帮助：MATCH  
返回在指定方式下与指定数值匹配的数组 中元素的相应位置。
语法

关于公式中两个if——当lookup_value和lookup_array是同一数组，且match_type是0时，它返回的结果是lookup_value的每一个元素在本数组中首次出现的位置。（请体会一下=MATCH({2;2;1;3;1;3;3;2;4},{2;2;1;3;1;3;3;2;4},)的结果是{1;1;3;4;3;4;4;1;9}——数组中所有2首次出现的是第1个位置、所有1首次出现的是第3个位置、所有3首次出现的是第4个位置，所有4（只有一个）首次出现的是第9个位置），再用=MATCH({2;2;1;3;1;3;3;2;4},{2;2;1;3;1;3;3;2;4},)=ROW(A1:A9)的判断，就成了{1;1;3;4;3;4;4;1;9}={1;2;3;4;5;6;7;8;9}，其结果是{true;false;true;true;false;false;false;false;true}，只有首次出现的是true，对应位置的数据是2、1、3、4，达到了去重复的目的 ）
这个例子简单明了，超赞！是否可以理解为：第一个IF数组中每一个数据在第二个IF数组中首次出现的位置？
为什么不能将前面公式设置成整咧？
=SUMPRODUCT((表源!$C:$C=$A3)*(表源!$D:$D=B$2)*(表源!$B:$B>0)*(表源!$H:$H=2)*(MATCH(IF((表源!$B:$B>0)*(表源!$H:$H=2),表源!$A:$A),IF((表源!$B:$B>0)*(表源!$H:$H=2),表源!$A:$A),)=ROW(表源!$1:$60)))
要是王老师有空帮我研究一下每天往下面加数据的时候如何自动更新的公式？
不吝赐教，万谢！

复道加餐

找多条件不重复计数，终于找到能现用的。