excel 换算得分的50种方法公式
网友投稿
·
2024-03-07 19:03
·
云表格 ·
阅读 362
excel 换算得分的50种方法公式
序号
作者
字符数
公式
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)
excel 换算得分的50种方法公式
序号
作者
字符数
公式
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)