序号
|
作者
|
字符数
|
公式
|
01
|
wshcw
|
51
|
=LOOKUP(LEFT(A3),{"A","B","C","D";7,4,1,0})+LEN(A3)
|
|
|
02
|
wshcw
|
55
|
=VLOOKUP(LEFT(A3),{"A",7;"B",4;"C",1;"D",0},2,)+LEN(A3)
|
|
03
|
wshcw
|
46
|
=MAX(1,SUMIF(C$3:C$12,A3&"*",B$3)/3+LEN(A3)-2)
|
|
|
04
|
wshcw
|
40
|
=SEARCH(LEFT(A3),"DC00B00A",1)+LEN(A3)-1
|
|
|
|
05
|
wshcw
|
56
|
=INDEX(B$3:B$12,SEARCH(LEFT(A3),"00A00B00CD"))+LEN(A3)-1
|
|
06
|
wshcw
|
52
|
=INDEX(B$3:B$12,MATCH(LEFT(A3),C$3:C$12,))+LEN(A3)-1
|
|
07
|
wshcw
|
37
|
=10-MATCH(LEFT(A3),C$3:C$12,)+LEN(A3)
|
|
|
|
|
08
|
wshcw
|
30
|
=MAX(202-3*CODE(A3)+LEN(A3),1)
|
|
|
|
|
|
09
|
wshcw
|
34
|
=AVERAGE(IF(C$3:C$12=A3,B$3:B$12))
|
|
|
|
|
10
|
wshcw
|
63
|
=VLOOKUP(LEFT(A3),CHOOSE({1,2},C$3:C$12,B$3:B$12),2,)+LEN(A3)-1
|
11
|
wshcw
|
59
|
=VLOOKUP(LEFT(A3),IF({1,0},C$3:C$12,B$3:B$12),2,)+LEN(A3)-1
|
|
12
|
wshcw
|
45
|
=LOOKUP(A3,LEFT(C$3:C$12),B$3:B$12)+LEN(A3)-1
|
|
|
13
|
wshcw
|
54
|
=INDEX(B$3:B$12,FIND(LEFT(A3),"12A34B56CD"))+LEN(A3)-1
|
|
14
|
wshcw
|
48
|
=INDEX(B$3:B$12,MATCH(A3,C$3:C$12,))-(LEN(A3)=2)
|
|
|
15
|
wshcw
|
35
|
=11-MATCH(A3,C$3:C$12,)-(LEN(A3)=2)
|
|
|
|
|
16
|
wshcw
|
51
|
=LOOKUP(1,0/(LEFT(A3)=C$3:C$12),B$3:B$12)+LEN(A3)-1
|
|
|
17
|
wshcw
|
54
|
=MATCH(A3&"*",MID("DC00B00A",ROW($1:$8),1),)+LEN(A3)-1
|
|
18
|
wshcw
|
48
|
=MAX(MATCH(A3,{"D","C","B","A"},)*3-5+LEN(A3),1)
|
|
|
19
|
wshcw
|
41
|
=MAX(FIND(LEFT(A3),"DCBA")*3-5+LEN(A3),1)
|
|
|
|
20
|
wshcw
|
42
|
=MOD(-MATCH(A3,C$3:C$12,),10)+(LEN(A3)<>2)
|
|
|
|
21
|
compass
|
53
|
=LOOKUP(LEFT(A3),{"A","B","C","D"},{7,4,1,0})+LEN(A3)
|
|
|
22
|
compass
|
36
|
=FIND(LEFT(A3),"DC34B67A")+LEN(A3)-1
|
|
|
|
|
23
|
zz5151353
|
41
|
=LOOKUP(2,1/(FIND(A3,C$3:C$12)),B$3:B$12)
|
|
|
|
24
|
lzw590
|
35
|
=SUMPRODUCT((C$3:C$12=A3)*B$3:B$12)
|
|
|
|
|
25
|
mxqchina
|
44
|
=SUMIF(C$3:C$12,SUBSTITUTE(A3,"*","~*"),B$3)
|
|
|
|
26
|
bosco_yip
|
37
|
=LOOKUP(,-FIND(A3,C$3:C$12),B$3:B$12)
|
|
|
|
|
27
|
bosco_yip
|
32
|
=MID(7410,CODE(A3)-64,1)+LEN(A3)
|
|
|
|
|
28
|
bosco_yip
|
28
|
=SUM((C$3:C$12=A3)*B$3:B$12)
|
|
|
|
|
|
29
|
bosco_yip
|
39
|
=OFFSET(B$2,MATCH(1,--(C$3:C$12=A3),),)
|
|
|
|
|
30
|
bosco_yip
|
42
|
=INDEX(B$3:B$12,MATCH(1,--(C$3:C$12=A3),))
|
|
|
|
31
|
bosco_yip
|
66
|
=HLOOKUP(LEFT(A3),TRANSPOSE({"A",7;"B",4;"C",1;"D",0}),2,)+LEN(A3)
|
32
|
bosco_yip
|
54
|
=CHOOSE(MATCH(2,1/(C$3:C$12=A3)),10,9,8,7,6,5,4,3,2,1)
|
|
|
33
|
bosco_yip
|
44
|
=INDIRECT("B"&(MATCH(1,--(C$3:C$12=A3),)+2))
|
|
|
|
34
|
bosco_yip
|
28
|
=MAX((C$3:C$12=A3)*B$3:B$12)
|
|
|
|
|
|
35
|
bosco_yip
|
29
|
=MAXA((C$3:C$12=A3)*B$3:B$12)
|
|
|
|
|
|
36
|
bosco_yip
|
30
|
=-MIN(-(C$3:C$12=A3)*B$3:B$12)
|
|
|
|
|
|
37
|
bosco_yip
|
31
|
=-MINA(-(C$3:C$12=A3)*B$3:B$12)
|
|
|
|
|
|
38
|
bosco_yip
|
32
|
=LARGE((C$3:C$12=A3)*B$3:B$12,1)
|
|
|
|
|
39
|
bosco_yip
|
34
|
=SMALL(IF(C$3:C$12=A3,B$3:B$12),1)
|
|
|
|
|
40
|
bosco_yip
|
36
|
=PRODUCT(IF((C$3:C$12=A3),B$3:B$12))
|
|
|
|
|
41
|
bosco_yip
|
41
|
=MMULT(--TRANSPOSE(C$3:C$12=A3),B$3:B$12)
|
|
|
|
42
|
bosco_yip
|
64
|
=INDIRECT(ADDRESS(MATCH(SUBSTITUTE(A3,"*","~*"),C$3:C$12,)+2,2))
|
43
|
bosco_yip
|
63
|
=INDIRECT("R"&MATCH(SUBSTITUTE(A3,"*","~*"),C$3:C$12,)+2&"C2",)
|
44
|
bosco_yip
|
57
|
=INDEX(B$3:B$12,MATCH(SUBSTITUTE(A3,"*","~*"),C$3:C$12,))
|
|
45
|
bosco_yip
|
65
|
=OFFSET(A:A,,1,) INDEX(B:B,MATCH(1,INDEX(--(A3=C$3:C$12),,),)+2,)
|
46
|
x.f.zhao
|
37
|
=SUMIF(C$3:C$12,LEFT(A3),B$4)+LEN(A3)
|
|
|
|
|
47
|
wxyqxxz2007
|
36
|
=CHOOSE(69-CODE(A3),0,1,4,7)+LEN(A3)
|
|
|
|
|
48
|
qinqh_yl
|
31
|
=ABS((67-CODE(A3))*3+1+LEN(A3))
|
|
|
|
|
|
49
|
qinqh_yl
|
45
|
=11-MATCH(SUBSTITUTE(A3,"*","~*"),C$3:C$12,0)
|
|
|
|
50
|
qinqh_yl
|
65
|
=VLOOKUP(SUBSTITUTE(A3,"*","~*"),IF({1,0},C$3:C$12,B$3:B$12),2,0)
|