换算得分的50种方法

wshcw

这个贴子是OFFICEFNAS里的精华贴,原贴37种方法,这几天我又对它作了更新,公式增加到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)

 

欢迎到我空间 [ 本帖最后由 wshcw 于 2010-3-22 00:37 编辑 ]

yongbin

樓主經常有大作,支持一下!!!

yfan17

真是条条大道通罗马，拓宽了思维方法。谢谢整理，收藏了。

一剑惊心

谢谢楼主的整理,下载收藏一下

fy5s

xuexi,zhijing

jackeroo

楼主技术水平是不错的，不过我不是这个版的版主，嘿嘿。
支持~~

[ 本帖最后由 jackeroo 于 2009-5-15 18:46 编辑 ]

wangtx218

好友！我学习了！

nmll316

支持你！！！！！！！！！！

mxqchina

好作品，已经加入精华。

wanwan_03

学习了啊～