大家看一下这个公式错哪了

ppla666

=SUM(--(MATCH(IF($A$2:$A$500=$A$7,$B$2:$B$500),IF($A$2:$A$500=$A$7,$B$2:$B$500),0)=ROW($B$2:$B$500)-1))这个公式是求第一问设计的，算出的结果为86，比实际多了1，不知错在哪了。
用f9分步运算，match函数运算结果第一个值应为false但实际显示却是true，不知为什么。
谢谢，大侠

jacky1998

1. 
   
2. =COUNT(0/FREQUENCY((OFFSET(A1,,,COUNTA(A:A))=A7)*COUNTIF(OFFSET(B1,,,COUNTA(A:A)),">="&OFFSET(B1,,,COUNTA(A:A))),ROW(1:999)-1))-1
   
3. =COUNT(0/FREQUENCY((OFFSET(A1,,,COUNTA(A:A))=A7)*MMULT(--(OFFSET(C1,,,COUNTA(A:A))={"花生米","红豆沙","绿豆沙"}),{1;1;1})*COUNTIF(OFFSET(B1,,,COUNTA(A:A)),">="&OFFSET(B1,,,COUNTA(A:A))),ROW(1:999)-1))-1

复制代码

=COUNT(0/FREQUENCY(MATCH(""&B1:B999,""&B1:B999,)*(A1:A999=A7),ROW(1:1000)-1))-1
公式都是卖任一种豆沙为计数，后面大师有说，公式结果是错的。
再另外看看。
将OFFSET(A1,,,COUNTA(A:A))等定义为名称。
最后问题2，结果为
=COUNT(0/(FREQUENCY(MATCH(""&B1:B999,""&B1:B999,)*(A1:A999=A7)*(C1:C999="花生米"),ROW(1:1000)-1)*FREQUENCY(MATCH(""&B1:B999,""&B1:B999,)*(A1:A999=A7)*(C1:C999="红豆沙"),ROW(1:1000)-1)*FREQUENCY(MATCH(""&B1:B999,""&B1:B999,)*(A1:A999=A7)*(C1:C999="绿豆沙"),ROW(1:1000)-1)))-1
=COUNT(0/(FREQUENCY(IF(FREQUENCY(MATCH(""&B1:B999,""&B1:B999,)*(A1:A999=A7)+MMULT(--(C1:C999={"花生米","绿豆沙","红豆沙"}),10^(-{1;2;3})),ROW(1:1000)-1+{0.1,0.01,0.001}),SMALL((ROW(1:1000)-1)*{1,1,1},ROW(1:3000))),ROW(1:1000)-1)=3))-1
只有3家。商家为
=OFFSET($B$1,SMALL(IF(FREQUENCY(MATCH(""&B1:B999,""&B1:B999,)*(A1:A999=A7)*(C1:C999="花生米"),ROW(1:1000)-1)*FREQUENCY(MATCH(""&B1:B999,""&B1:B999,)*(A1:A999=A7)*(C1:C999="红豆沙"),ROW(1:1000)-1)*FREQUENCY(MATCH(""&B1:B999,""&B1:B999,)*(A1:A999=A7)*(C1:C999="绿豆沙"),ROW(1:1000)-1),ROW(1:1001)-1),ROW(2:100))-1,)

Bodhidharma

sum(--(match(data,data)=row(...)))這個公式，是計算data有幾個不重複的值
在你的公式中，data為IF($A$2:$A$500=$A$7,$B$2:$B$500)，當A欄不等於A7時，會回傳false
但是這個false也是一個「不重複的值」，因此這種用法計算的結果會多1
但是直接減1的話，若A2:A500都等於A7時會出錯(因為沒有「false」這個元素)
因此比較靠譜的作法是再加個判斷把false的部分處理掉：

1. =SUM(($A$2:$A$500=$A$7)*(MATCH(IF($A$2:$A$500=$A$7,$B$2:$B$500),IF($A$2:$A$500=$A$7,$B$2:$B$500),0)=ROW($B$2:$B$500)-1))

复制代码

快樂學習

供参考

1. =COUNT(0/(MATCH(201402&B2:B999,A2:A999&B2:B999,)=ROW(2:999)-1))
   
2. =COUNT(0/(MATCH(201402&B2:B999,A2:A999&B2:B999,)=MMULT(N(C2:C999={"绿豆沙","花生米","红豆沙"}),{1;1;1})*(ROW(2:999)-1)))

复制代码

jacky1998

快樂學習 发表于 2014-7-9 17:47
供参考

公式2，想一下，结果不对，因为，思路不对呀，
........=MULT(N(C2:C999={"绿豆沙","花生米","红豆沙"}),{1;1;1})*(ROW(2:999)-1)))
这段与前面，不能指定不重得计数。因为前面是MATCH呀。因为MATCH只对最先匹配的行计数，而豆沙等则可能是以后出现的行计数的。所以这个思路下的结果，很可能是错误的。
如果是错误的，那么计数会比实际的少。

wangjguo44

难道是我理解错了？
楼主第二个问题要求：用公式统计201402同时售卖“花生米、红豆沙、绿豆沙”的客户数量，可楼上算出的都有80家之多，而我分析只有三家！
1、既然是统计201402，那么首先排除不是201402的；
2、既然是同时售卖“花生米、红豆沙、绿豆沙”的客户，那么201402只出现2次及以下的客户也被排除掉；
3、留下的应该只有出现3次以上的；
4、但是有的虽然达3次以上，如COMO，三次售卖的是{红豆沙、花生米、杨枝甘露}，客户田野，出现五次，售卖的都是花生米……，这些都应被排除掉，余下只有三家：Mini Work Shop、麦考利、致盛才是真正同时售卖“花生米、红豆沙、绿豆沙”。见附件（为观察方便起见，我排了序，并进行了筛选。）
难道我理解真的错了？？？

jacky1998

wangjguo44 发表于 2014-7-9 19:12
难道是我理解错了？
楼主第二个问题要求：用公式统计201402同时售卖“花生米、红豆沙、绿豆沙”的客户数量 ...

大师公析的正是，我新作了个公式，结果是6家。不过，公式看来，还是错的。

wangjguo44

Bodhidharma 发表于 2014-7-9 17:14
sum(--(match(data,data)=row(...)))這個公式，是計算data有幾個不重複的值
在你的公式中，data為IF($A$2: ...

您好，请看看我在6楼的疑惑

wangjguo44

快樂學習 发表于 2014-7-9 17:47
供参考

您好，请看看我在6楼的疑惑

jacky1998

wangjguo44 发表于 2014-7-9 19:30
您好，请看看我在6楼的疑惑

=COUNT(0/(FREQUENCY(MATCH(""&B1:B999,""&B1:B999,)*(A1:A999=A7)*(C1:C999="花生米"),ROW(1:1000)-1)*FREQUENCY(MATCH(""&B1:B999,""&B1:B999,)*(A1:A999=A7)*(C1:C999="红豆沙"),ROW(1:1000)-1)*FREQUENCY(MATCH(""&B1:B999,""&B1:B999,)*(A1:A999=A7)*(C1:C999="绿豆沙"),ROW(1:1000)-1)))-1