excel在线求助，急！哪位好心人，帮我看看这个在excel中怎么编写公式，先谢谢了！ 麻烦好心人给我按图上要求做一个EXCEL公式，谢谢
excel \u5728\u7ebf\u6c42\u52a9\uff0c\u54ea\u4f4d\u597d\u5fc3\u4eba\u5e2e\u6211\u770b\u4e0b\u8fd9\u4e2a\u5e94\u8be5\u600e\u4e48\u7f16\u5199\u516c\u5f0f\uff0c\u8c22\u8c22\u4e86\uff01\u5047\u5982\u6570\u636e\u5728A1
=MOD(10-RIGHT(SUMPRODUCT(--MID(RIGHT(SUMPRODUCT(MID(A1,(ROW($1:$7)-1)*2+1,1)*10^(7-ROW($1:$7)))*2,7),ROW($1:$7),1))+SUMPRODUCT(--MID(A1,ROW($1:$7)*2,1))),10)
\u5b8c\u5168\u6309\u4f60\u63cf\u8ff0\u7684\u6b65\u9aa4\u505a\u7684\u516c\u5f0f\uff0c\u672a\u4f5c\u4f18\u5316\u3002
\u540c\u4e00\u4e2a\u95ee\u9898\u600e\u4e48\u95ee\u4e86\u4e24\u6b21\u3002\uff08\u516c\u5f0f\u53ef\u4ee5\u641e\u5b9a\uff0c\u671b\u91c7\u7eb3\uff09
\u8f93\u5165\u4e0b\u9762\u516c\u5f0f\uff0c\u4e0d\u8981\u76f4\u63a5\u6309\u56de\u8f66\uff0c\u6309Ctrl + Shift + Enter\uff0c\u518d\u5c06\u516c\u5f0f\u5411\u53f3\u62c9\uff0c\u5411\u4e0b\u62c9\u5373\u53ef\u3002
=SUMPRODUCT(($F$1:$O$1)*(COUNTIF($F2:$O11,$F$1:$O$1)*10-$F$1:$O$1=LARGE(COUNTIF($F2:$O11,$F$1:$O$1)*10-$F$1:$O$1,COLUMN(A1))))
\u5982\u4e0b\u56fe\u3002\uff08\u8fd9\u624d\u662f\u6b63\u786e\u7ed3\u679c\uff0c\u4f60\u624b\u52a8\u8f93\u5165\u7684\u4e0d\u5bf9\uff09
Option Explicit
Function myVerify(ref As Range) As Integer
Dim r As String, temp As String
Dim i As Integer, s As Long, temp2 As Integer '第一步乘积会超出整型范围，故定义为long
r = Trim(ref.Text)
If Len(r) <> 14 Then
myVerify = 0
MsgBox (ref.Address & "中有效位数不为14位")
Return
End If
temp = ""
For i = 1 To 13 Step 2 'temp为1至13位字符
temp = temp & Mid(r, i, 1)
Next i
temp2 = 0
For i = 2 To 14 Step 2 'temp2为2至14位合计
temp2 = temp2 + Val(Mid(r, i, 1))
Next i
s = Val(temp) * 2
temp = Right(String(0, "0") & s, 7) '补0取后七位,第一步完成
For i = 1 To 7 '第二、三步,结果存于temp2
temp2 = temp2 + Val(Mid(temp, i, 1))
Next i
Dim v As Integer '最后校验位
v = 0
If Val(Right(temp2, 1)) > 0 Then v = 10 - Val(Right(temp2, 1))
myVerify = v
End Function
有挑战， 本小仙给你搞定,先占楼
=LOOKUP(--RIGHT(SUM(--MID(RIGHT(2*(LEFT(A1,1)&MID(A1,3,1)&MID(A1,5,1)&MID(A1,7,1)&MID(A1,9,1)&MID(A1,11,1)&MID(A1,13,1)),7),{1;2;3;4;5;6;7},1))+SUM(--MID(A1,{2;4;6;8;10;12;14},1)),1),{0,1,2,3,4,5,6,7,8,9},{0,9,8,7,6,5,4,3,2,1})
我晕， 我觉得自己定义个 自定义函数好些， 这个太长了
A1：88210123456789
校验码：
=RIGHT(10-RIGHT(SUM(--MID(RIGHT(TEXT(SUM(MID(A1,{1,3,5,7,9,11,13},1)*10^{6,5,4,3,2,1,0})*2,"0000000"),7),{1,2,3,4,5,6,7},1))+SUM(--MID(A1,{2,4,6,8,10,12,14},1))))
=RIGHT(10-RIGHT(SUM(--MID(TEXT(RIGHT(SUM(MID(A1,ROW($1:$7)*2-1,1)*10^(7-ROW($1:$7)))*2,7),REPT(0,7)),ROW($1:$7),1),--MID(A1,ROW($1:$7)*2,1))))
值得学习
绛旓細alt+f11杩涘畯缂栬緫鍣紝sheet1涓婂彸閿彃鍏ユā鍧楋紝璐村叆浠ヤ笅浠ｇ爜锛屽叧闂畯缂栬緫鍣ㄥ悗锛屽湪浣犻渶瑕佹牎楠屼綅鐨勫湴鏂癸紙姣斿鍗″彿鍦ˋ1锛夛紝閭ｅ氨杈撳叆=myVerify(a1)Option Explicit Function myVerify(ref As Range) As Integer Dim r As String, temp As String Dim i As Integer, s As Long, temp2 As Integer '绗...
绛旓細=MOD(10-RIGHT(SUMPRODUCT(--MID(RIGHT(SUMPRODUCT(MID(A1,(ROW($1:$7)-1)*2+1,1)*10^(7-ROW($1:$7)))*2,7),ROW($1:$7),1))+SUMPRODUCT(--MID(A1,ROW($1:$7)*2,1))),10)瀹屽叏鎸変綘鎻忚堪鐨勬楠ゅ仛鐨勫叕寮忥紝鏈綔浼樺寲銆
绛旓細涓銆佹妸W鍒楅噸鏂拌瀹氫竴娆′负甯歌鎴栨棩鏈熸牸寮忥紝鍐嶅崟鍏冩牸涓噸鏂拌緭鍏ヤ竴娆℃棩鏈熴備簩銆佺敤浠ヤ笅鍏紡浠ｆ浛锛=LOOKUP(1,0/('301'!W2:W999<>""),'301'!W2:W999)
绛旓細1:鏁版嵁鏈夋晥鎬 2:INDEX鍑芥暟
绛旓細1 棣栧厛锛岃冨彿 濮撳悕 瀛﹀彿 鍙敤楂樼骇绛涢夊緱鍒板敮涓鍊硷紝杩斿洖鍦ㄦ柊琛ㄧ殑ABC鍒 2 鐒跺悗鍦―2灏卞彲浠ュ啓鍏紡浜 =LOOKUP(,0/(($A2=琛ㄤ竴!$A$1:$A$6666)*(D$1=琛ㄤ竴!$D$1:$D$6666)),琛ㄤ竴!$E$1:$E$6666)鏈鍚庡彸鎷夛紝涓嬫媺鍗冲彲
绛旓細浣跨敤鏉′欢鏍煎紡鍗冲彲瑙ｅ喅锛涙潯浠舵牸寮忎腑鍏紡濡備笅鍥炬墍绀猴細
绛旓細A4锛孊4锛孋4锛孌4锛孍4 A5锛孊5锛孋5锛孌5锛孍5 A6锛孊6锛孋6锛孌6锛孍6 锛堝涓琛屽ソ璇存槑锛夎浆鎹负锛欰1 A2 A3 ...E6鍚 鍙互鎻掑叆涓涓柊琛紝鍦ˋ1鏍间腑鎻掑叆杩欎釜鍏紡杞崲锛=OFFSET(Sheet1!$A$1,MOD((ROW()-1),6),INT((ROW()-1)/6))鍏朵腑鍏紡涓殑Sheet1涓哄師琛ㄧ殑鍚嶇О锛屽叕寮忕殑6涓哄師鏁版嵁鏈6琛5...
绛旓細浣犵殑鎻忚堪璺熶綘鐨勮〃鏍兼湁鍒嗘锛氱湅B7銆 B6鏄0锛孊7鏄礋鏁帮紝涓嶇畻姝ｈ礋鏁拌浆鎹紝浣嗘槸浣犺繕鏄紩鐢ㄤ簡-2.濡傛灉浣犳兂瑕佽繖绉嶇粨鏋滅殑璇濓紝鍦ㄩ斾腑B4鍗曞厓鏍煎唴杈撳叆浠ヤ笅鍏紡锛=IF(OR(AND(A3<>0,A3*A4<0),A3+A4=A4),A4,0) 涓嬫媺澶嶅埗 濡傛灉鎸夌収鏂囧瓧鎻忚堪鐨勮姹傦紝鍦˙4鍗曞厓鏍煎唴杈撳叆锛=IF(AND(A3<>0,A3*A4<0),A4,0...
绛旓細=IF(SHEET1!$G1>HEET1!$F1,SHEETA1,"')
绛旓細寰楀埌姹傚拰鏄500 鐒跺悗鍦‥22杈撳叆涓涓1锛岀劧鍚庯紝寮濮--濉厖--搴忓垪锛屽簭鍒椾骇鐢熷湪鍒楋紝缁堟鍊500锛岀‘瀹 鐒跺悗锛屽彸閿瓻鍒椾换鎰忎竴鏁板瓧锛屾帓搴--鍗囧簭锛岀粨鏋滃涓 鍦ˋ鍒楀垱寤轰竴涓狝鍒癦鐨勫尯鍩燂紝鐒跺悗澶嶅埗锛屽啀閫変腑A涓嬩笅闈㈢殑绌虹櫧锛岀矘璐 鐒跺悗灏辨槸濉厖B鍒桟鍒楃殑鏁版嵁 浠鍒椾负渚嬶紝閫変腑B鍒楋紝CTRL+G锛屽畾浣嶆潯浠讹紝绌哄硷紝纭畾...
