excel 換算得分的50種方法公式
發(fā)布時間：2012-03-28 17:23:19 作者：佚名 
我要評論
我要評論excel 換算得分的50種方法公式
| 序號 | 作者 | 字符數(shù) | 公式 | |||||||||
| 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) | |||||||||
相關(guān)文章
陽歷/農(nóng)歷轉(zhuǎn)換對照表怎么做? Excel制作日歷表/萬年歷的最終版
想要制作陽歷/農(nóng)歷轉(zhuǎn)換對照表，，通過這個對照表，你只需知道陽歷日期，就可以快速準(zhǔn)確的找到對應(yīng)的農(nóng)歷日期，下面我們就來看看詳細(xì)制作方法2025-04-10- 工作上需要制作一個日歷表/萬年歷，該怎么制作呢？我們需要使用三個excel函數(shù)來實現(xiàn)，詳細(xì)請看下文介紹2025-04-10
這個函數(shù)公式簡直太強了! excel按權(quán)重求和計算的技巧
Excel中有一個功能非常強大的函數(shù)，它就是SUMPRODUCT函數(shù)，既能求和、計數(shù)、權(quán)重計算，還能排名等，今天我們就來看看Excel根據(jù)權(quán)重求和的技巧2025-04-10tocol函數(shù)哪個版本能用? excel中tocol函數(shù)的兩種高級用法
TOCOL函數(shù)公式非常強大，值得一學(xué)，從字面意義可以理解出來，它是將數(shù)據(jù)轉(zhuǎn)換成一列的，今天分享TOCOL函數(shù)的2種高級用法2025-04-10Excel隱藏行和折疊行有什么不同? 隱藏行和折疊使用方法和區(qū)別介紹
excel表格進(jìn)行操作的時候，發(fā)現(xiàn)有隱藏和和折疊行，看上去效果一樣，都隱藏了，但是兩者是有很大區(qū)別的，詳細(xì)請看下文介紹2025-03-21Excel工齡計算不再愁! Excel隱藏函數(shù)DATEDIF輕松計算精確到天級的工齡
在日常工作中，我們經(jīng)常需要計算員工的工齡，以便進(jìn)行人事管理、薪資調(diào)整等工作，本文將詳細(xì)介紹如何在Excel表格中計算工齡，并提供多種實用方法供您選擇2025-03-13快速提高工作效率! Excel高手的8個技巧勸你學(xué)學(xué)
Excel數(shù)據(jù)處理遇到的問題，99%都是由于奇葩的數(shù)據(jù)格式造成的，今天跟大家整理了幾個Excel表格構(gòu)建的規(guī)則,先不要問為什么，照著做就對了2025-03-06Excel透視表1月到12月不能排序怎么辦? 數(shù)據(jù)透視表日期無法排序解決方法
如圖對數(shù)據(jù)透視表日期進(jìn)行升序排序后，仍不是我們想要的結(jié)果，有可能月份是文本，不能排列，下面我們就來看看詳細(xì)解決辦法2025-03-06財務(wù)審計必會! 2分鐘搞定用excel做賬齡劃分的技巧
首先咱們得明確一下賬齡的概念，簡單來說，賬齡就是某個賬戶的余額從產(chǎn)生到現(xiàn)在的時間長度，比如，一年以內(nèi)的賬齡就是那些在過去一年內(nèi)產(chǎn)生的債權(quán)或債務(wù)，下面我們就來看看2025-03-06
實現(xiàn)辦公自動化! DeepSeek搭配Excel制作自定義按鈕的技巧
DeepSeek生成想要的代碼很方便，如果將生成的代碼放到excel中作為vba代碼使用，豈不是能實現(xiàn)辦公自動化？下面我們就來看看實現(xiàn)方法2025-03-05
