根据要求计算球队积分

楼主蒸蒸日上	如题：求球队积分！如图：要求：用函数完成，不用VBA，不用辅助列，不用自定义名称！谢谢大家参与，欢迎大家积极回答，有奖问答，答案正确者5个分值好评，最佳评一个满分6分。另送蒸蒸日上的祝福一份，还有笑脸，鲜花！蒸蒸.rar
2楼一嘟噜钥匙	=SUM((LEFT($B$3:$B$8,2)=F3)TEXT($C$3:$C$8-$D$3:$D$8,"\3;\0;\1")+(RIGHT($B$3:$B$8,2)=F3)TEXT($D$3:$D$8-$C$3:$C$8,"\3;\0;\1"))
3楼 ragtag	=SUM(IF(IF({1,0},LEFT(B$3:B$8,2),RIGHT(B$3:B$8,2))=F3,IF(C$3:C$8>D$3:D$8,{3,0},IF(C$3:C$8=D$3:D$8,{1,1},{0,3})),0)) =SUM((F3=IF({1,0},LEFT(B$3:B$8,2),RIGHT(B$3:B$8,2)))*IF(C$3:C$8>D$3:D$8,{3,0},IF(C$3:C$8=D$3:D$8,{1,1},{0,3})))
4楼 wjc2090742	数组公式： =SUM((MID(B$3:B$8,{1,4},2)=F3)TEXT(MMULT(C$3:D$8,{1,-1;-1,1}),"3;!0;1")) =SUM((MID(B$3:B$8,{1,4},2)=F3)TEXT({1,-1}*(C$3:C$8-D$3:D$8),"3;!0;1")) 蒸蒸.rar
5楼蜜语	看我能回复不
6楼天南地北	公式老长了，都不好意思回帖 =SUM((IF(FIND(F3,B$3:B$8&F3)=1,C$3:C$8,IF(FIND(F3,B$3:B$8&F3)=4,D$3:D$8))>IF(FIND(F3,B$3:B$8&F3)=1,D$3:D$8,IF(FIND(F3,B$3:B$8&F3)=4,C$3:C$8)))3)+SUM(ISNUMBER(FIND(F3,B$3:B$8))(C$3:C$8=D$3:D$8)) 优化后的为 =SUM((IF(FIND(F3,B$3:B$8&F3)=1,C$3:C$8,IF(FIND(F3,B$3:B$8&F3)=4,D$3:D$8,))>IF(FIND(F3,B$3:B$8&F3)=1,D$3:D$8,C$3:C$8))3)+SUM(ISNUMBER(FIND(F3,B$3:B$8))(C$3:C$8=D$3:D$8)) 在更新一个答案（数组公式） =SUM(((MID(B$3:B$8,{1,4},2)=F3)C$3:D$82>C$3:C$8+D$3:D$8)3)+SUM(((MID(B$3:B$8,{1,4},2)=F3)C$3:D$82=C$3:C$8+D$3:D$8)1)
7楼 wp8680	=SUM((LEFT(B$3:B$8,2)=F3)((C$3:C$8>D$3:D$8)3+(C$3:C$8=D$3:D$8)1),(RIGHT(B$3:B$8,2)=F3)((C$3:C$8<D$3:D$8)3+(C$3:C$8=D$3:D$8)1)) 公式就是长了点，三键回车后下拉。 =SUM((LEFT(B$3:B$8,2)=F3)((C$3:C$8>D$3:D$8)3+(C$3:C$8=D$3:D$8)),(RIGHT(B$3:B$8,2)=F3)((C$3:C$8<D$3:D$8)3+(C$3:C$8=D$3:D$8)))
8楼 kakaco	=SUMPRODUCT((LEFT(B$3:B$8,2)=F3)((C$3:C$8>D$3:D$8)3+(C$3:C$8=D$3:D$8)1)+(RIGHT(B$3:B$8,2)=F3)((C$3:C$8<D$3:D$8)3+(C$3:C$8=D$3:D$8)1)) 偷一个。。 =SUM((LEFT(B$3:B$8,2)=F3)TEXT(C$3:C$8-D$3:D$8,"3;\0;1")+(RIGHT(B$3:B$8,2)=F3)TEXT(D$3:D$8-C$3:C$8,"3;\0;1")) 数组公式。
9楼 wp8680	=SUM((LEFT(B$3:B$8,2)=F3)TEXT(C$3:C$8-D$3:D$8,"3;!0;1"),(RIGHT(B$3:B$8,2)=F3)TEXT(C$3:C$8-D$3:D$8,"!0;3;1")) 三键回车后下拉降至110字了。
10楼篮板球	上交。我写得好长。蒸蒸.rar
11楼 soup5257	=SUM(IF(LEFT($B$3:$B$8,2)=F3,IF($C$3:$C$8=$D$3:$D$8,1,IF($C$3:$C$8>$D$3:$D$8,3,0)),IF(ISNUMBER(FIND(F3,$B$3:$B$8)),IF($C$3:$C$8=$D$3:$D$8,1,IF($C$3:$C$8<$D$3:$D$8,3,0))))) CTRL SHIFT 回车，我知道自己的很长，所以希望能给个简单的点，
12楼 wcymiss	=SUM((MID(B$3:B$8,{1,4},2)=F3)TEXT((C$3:C$8-D$3:D$8){1,-1},"3;!0;1"))
13楼 gouweicao78	71字 =SUM((MID(B$3:B$8,{1,4},2)=F4)TEXT((C$3:C$8-D$3:D$8){1,-1},"3;\0;1"))
14楼涅磐86970	=SUM((LEFT(B$3:B$8,2)=F3)TEXT(C$3:C$8-D$3:D$8,"3;!0;1")+(RIGHT(B$3:B$8,2)=F3)TEXT(D$3:D$8-C$3:C$8,"3;!0;1")) 感觉太长了- -\|
15楼 JLxiangwei	=SUM(IF(ISERR(FIND(F3,B$3:B$8)),,--TEXT((2-FIND(F3,B$3:B$8))*(C$3:C$8-D$3:D$8),"3;!0;1")))
16楼 ilyte	G3数组公式： =SUM((LEFT(B$3:B$8,2)=F3)TEXT(C$3:C$8-D$3:D$8,"3;!0;1")+(RIGHT(B$3:B$8,2)=F3)TEXT(D$3:D$8-C$3:C$8,"3;!0;1")) =SUM(IF(ISNUMBER(FIND(F3,B$3:B$8)),--TEXT(IF(FIND(F3,B$3:B$8)=1,1,-1)(C$3:C$8-D$3:D$8),"3;!0;1")),0) =SUM(IF(ISNUMBER(FIND(F3,B$3:B$8)),--TEXT((FIND(F3,B$3:B$8)-2)(D$3:D$8-C$3:C$8),"3;!0;1"))) 87字符： =SUM(IF(FIND(F3,B$3:B$8&F3)<6,--TEXT((D$3:D$8-C$3:C$8)*(FIND(F3,B$3:B$8)-3),"3;!0;1"))) 前面的不删了，留下解题过程球队积分.zip
17楼 xgg2001	73的 =SUM(INT(3^SIGN(MMULT(((MID(B$3:B$8,{1,4},2)=F3)2-1)2^C$3:D$8,{1;1})))) 69的 =SUM((MID(B$3:B$8,{1,4},2)=F3)INT(3^SIGN((C$3:C$8-D$3:D$8){1,-1})))
18楼大表弟	=MMULT(TRANSPOSE(--(ISNUMBER(FIND(F3,$B$3:$B$8,1)))),--TEXT(CHOOSE(--(FIND(F3,$B$3:$B$8&I3,1)=1)+1,$D$3:$D$8-$C$3:$C$8+1,$C$3:$C$8-$D$3:$D$8+1),"[>1]3;[=1]1;!0")) MM函数用的少。。有点长了
19楼小虎	小虎.rar
20楼 piny	方法一 111字元 =SUM((MID(B$3:B$8,1,2)=F3)TEXT(C$3:C$8-D$3:D$8,"3;!0;1")+(MID(B$3:B$8,4,2)=F3)TEXT(D$3:D$8-C$3:C$8,"3;!0;1")) 方法二 102字元 =SUM(--CHOOSE(FIND(F3,B$3:B$8&F3),TEXT(C$3:C$8-D$3:D$8,"3;!0;1"),,,TEXT(D$3:D$8-C$3:C$8,"3;!0;1"),,0)) 方法三 94字元 =SUM(--TEXT((C$3:C$8-D$3:D$8)(5-2FIND(F3,B$3:B$8&F3))/3-9(FIND(F3,B$3:B$8&F3)=6),"3;!0;1")) 方法四 92字元 =SUM(--TEXT((C$3:C$8-D$3:D$8)(5-2FIND(F3,B$3:B$8&F3))-9(FIND(F3,B$3:B$8&F3)=6),"3;!0;1")) 方法五 90字元 =SUM(--TEXT((C$3:C$8-D$3:D$8)(2-FIND(F3,B$3:B$8&F3))-9(FIND(F3,B$3:B$8&F3)=6),"3;!0;1")) 方法六 87字元 =SUM(TEXT((C$3:C$8-D$3:D$8)(2-FIND(F3,B$3:B$8&F3)),"3;!0;1")(FIND(F3,B$3:B$8&F3)<>6)) 皆數組下拉
21楼 lrlxxqxa	=SUM((LEFT(B$3:B$8,2)=F3)(C$3:C$8>D$3:D$8)3+(((RIGHT(B$3:B$8,2)=F3)+(LEFT(B$3:B$8,2)=F3)))*(C$3:C$8=D$3:D$8)) 蒸蒸lr.rar
22楼 bluexuemei	=SUM(--(0&MID(TEXT(C$3:C$8-D$3:D$8,"3--!0;!0--3;1--1"),FIND(F3,B$3:B$8&F3),1)))
23楼蒸蒸日上	精彩，大家辛苦了，分值没有啦，明天在来评最佳！
24楼篮板球	感谢评分，看到各位各种函数答案，获益匪浅。
25楼 bluexuemei	吴姐好思路,学习!
26楼 bluexuemei	强大的思路,牛B!公式一中2^C$3:D$8可以改为C$3:D$8，再简化2个字符。
27楼 xgg2001	不行的啊。两个false对应0:0会出错，必须要保证有进球，呵呵，要不然我何必多这两个兄台的思路也很不错，也可以精简一点，拙见，见笑了 =SUM(--(0&MID(TEXT(C$3:C$8-D$3:D$8,"3;!0--3;1--1"),FIND(F3,B$3:B$8&F3),1))) 或者 =SUM(--(0&MID(TEXT(C$3:C$8-D$3:D$8,"3;!03;11"),FIND(F3,B$3:B$8&F3)^0.7,1)))
28楼 bluexuemei	谢谢兄台指点,兄台技术精湛,以后要多向你请教才是!
29楼蒸蒸日上	大表弟，在修改一下，期待你的MMULT解法！
30楼天南地北	没有使用TEXT公式长了将近50个字符 =SUM(--TEXT((MID(B$3:B$8,{1,4},2)=F3)C$3:D$82-C$3:C$8-D$3:D$8,"3;!0;1"))
31楼 xgg2001	有0:0的比分出现时，结果出错
32楼天南地北	知道了，谢谢提醒！
33楼 JLxiangwei	开贴有段时间了，集合众人思路，公式最终简化到66字符 =SUM((MID(B$3:B8,{1,4},2)=F3)INT(3^SIGN((C$3:C8-D$3:D8){1,-1})))
34楼蜜语	路过
35楼 pcwmmn	很不错的啊