excel取后面4位不同的数
excel\u53d6\u540e\u97624\u4f4d\u4e0d\u540c\u7684\u6570
=SUM(LEFT(RIGHT(0&SUBSTITUTE(A2,".",),SMALL(IF(MATCH(LEFT(RIGHT(SUBSTITUTE(A2,".",),ROW($1:$20))),LEFT(RIGHT(SUBSTITUTE(A2,".",),ROW($1:$20))),)=ROW($1:$20),ROW($1:$20),99),{1;2;3;4})))*10^{3;2;1;0})
excel\u4e2d5\u4f4d\u6570\u5b57\u53ea\u53d6\u524d\u97624\u4f4d\u6570\u5b57\uff0c\u53ef\u4f7f\u7528LEFT\u51fd\u6570\u5b9e\u73b0\u3002
\u65b9\u6cd5\u6b65\u9aa4\u5982\u4e0b\uff1a
1\u3001\u6253\u5f00\u9700\u8981\u64cd\u4f5c\u7684EXCEL\u8868\u683c\uff0c\u5728\u7a7a\u767d\u5355\u5143\u683c\u4e2d\u8f93\u5165\u51fd\u6570=LEFT(\uff0c\u7136\u540e\u70b9\u51fb\u9009\u4e2d\u9700\u8981\u63d0\u53d6\u524d4\u4f4d\u6570\u5b57\u7684\u76f8\u5173\u5355\u5143\u683c\u3002
2\u3001\u8f93\u5165\u9017\u53f7\uff0c\u7136\u540e\u7ee7\u7eed\u8f93\u51654)\uff0c\u5e76\u56de\u8f66\u5b8c\u6210\u516c\u5f0f=LEFT(A1,4)\u7684\u7f16\u8f91\u8f93\u5165\u5373\u53ef\u3002\u30104\u4ee3\u8868\u63d0\u53d6\u5de6\u8fb9\u524d4\u4f4d\u6570\u5b57\u3011
3\u3001\u8fd4\u56deEXCEL\u8868\u683c\uff0c\u53d1\u73b0\u6210\u529f\u5728EXCEL\u4e2d\u63d0\u53d65\u4f4d\u6570\u5b57\u7684\u524d4\u4f4d\u6570\u5b57\u3002
=SUM(LEFT(RIGHT(0&SUBSTITUTE(A2,".",),SMALL(IF(MATCH(LEFT(RIGHT(SUBSTITUTE(A2,".",),ROW($1:$20))),LEFT(RIGHT(SUBSTITUTE(A2,".",),ROW($1:$20))),)=ROW($1:$20),ROW($1:$20),99),{1;2;3;4})))*10^{3;2;1;0})
数组公式，注意三键结束
看看效果如图
欢迎测试其他数据
1.为了公式简单些，先用I列作辅助列，在I2输入公式：
=SUM(MID(SUBSTITUTE(J2,".",),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(J2,".",)))),1)*10^(ROW(INDIRECT("1:"&LEN(SUBSTITUTE(J2,".",))))-1))
数组公式，按Ctrl+Shift+Enter（三键同时按）结束公式输入，再向下填充。
2.在H2输入公式：
=LEFT(SUM((0&MID(I2,SMALL(FIND(ROW($1:$10)-1,I2&1/17),ROW($1:$10)),1))*10^(10-ROW($1:$10)))&"000",4)
数组公式，按Ctrl+Shift+Enter（三键同时按）结束公式输入，再向下填充。
3.选中H列再右击→选择性粘贴→数值→确定，最后删除I列即为所需要的。
用VBA写的Function 肯定能实现你的要求，
alt+f11 打开vba 编译器，插入模块，复制黏贴下面的代码。
然后回到excel 输入公式 "ldiff4()" 下拉即可
Function LDiff4(a As String) As String
Dim i As Long
Dim k As Long
Dim v(0 To 9)
a = StrReverse(Replace(a, ".", ""))
For i = 1 To Len(a)
If IsEmpty(v(Mid(a, i, 1))) Then
v(Mid(a, i, 1)) = 1
k = k + 1
End If
If k = 4 Then Exit For
Next i
For i = 0 To 9
If v(i) = 1 Then
LDiff4 = LDiff4 & i
End If
Next i
If Len(LDiff4) < 4 Then
Do
LDiff4 = LDiff4 & 0
Loop Until Len(LDiff4) = 4
End If
End Function
K2输入公式
=LEFT(SUBSTITUTE(SUMPRODUCT(IF(ISERROR(FIND(ROW($A$1:$A$9),J2)),0,ROW($A$1:$A$9)*10^ROW($A$1:$A$9))),"0","")&"0000",4)
按ctrl+shift+回车，然后可以拖动公式。
I2输入=RIGHT(TEXT(SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW($A$1:$A$9),J2)),ROW($A$1:$A$9)*10^ROW($A$1:$A$9),"")),0,""),"0000"),4)
shift+ctrl+enter三键结束输入，下拉填充！
绛旓細杩欎釜闂姣旇緝瀹规槗瑙ｅ喅锛岀敤涓涓嚱鏁板氨鍙互瑙ｅ喅闂锛宺ight鍑芥暟灏辨槸浠庡彸杈瑰紑濮嬫埅鍙栨寚瀹浣嶆暟鐨鏁版嵁锛屽叿浣撳叕寮忎负锛=RIGHT(A2,4)锛屾晥鏋滃鍥撅紝濡傛湁闇瑕佸彲浠ョ户缁氦娴侊紒
绛旓細鍙互浣跨敤RIGHT鎴朢IGHTB鍑芥暟鎻愬彇鍚庨潰鍑浣嶇殑鍐呭銆俁IGHT鎴朢IGHTB鐢ㄩ旓細RIGHT鏍规嵁鎵鎸囧畾鐨勫瓧绗︽暟杩斿洖鏂囨湰涓蹭腑鏈鍚庝竴涓垨澶氫釜瀛楃銆俁IGHT鎴朢IGHTB鐢ㄩ旓細RIGHT鏍规嵁鎵鎸囧畾鐨勫瓧绗︽暟杩斿洖鏂囨湰涓蹭腑鏈鍚庝竴涓垨澶氫釜瀛楃銆俁IGHTB鏍规嵁鎵鎸囧畾鐨勫瓧鑺傛暟杩斿洖鏂囨湰涓蹭腑鏈鍚庝竴涓垨澶氫釜瀛楃銆傝娉曪細RIGHT(text锛宯um_chars)锛孯IGHT...
绛旓細Excel涓鎴彇澶氫綅鏁板瓧鐨勫墠鍑犱綅锛屾垨鑰呮槸鍚鍑犱綅锛屽彲閫氳繃浣跨敤MID鍑芥暟瀹炵幇銆傛柟娉曟楠ゅ涓嬶細1銆佹墦寮闇瑕佹搷浣滅殑EXCEL琛ㄦ牸锛屽湪绌虹櫧鍗曞厓鏍艰緭鍏=MID锛堬紝鐒跺悗鐐瑰嚮閫夋嫨鏁版嵁鏉ユ簮鍗曞厓鏍硷紝濡侫1銆2銆佺户缁緭鍏ラ渶瑕佸紑濮嬫埅鍙栫殑璧峰浣嶏紝鍜屾埅鍙栫殑浣嶆暟锛岀劧鍚庡洖杞﹀嵆鍙紝濡1锛4琛ㄧず浠庣涓浣嶅紑濮嬫埅鍙4浣嶆暟瀛銆3銆佽繑鍥濿XCEL琛ㄦ牸锛...
绛旓細鍋囪a鍒楁槸鍙风爜锛宐鍒楁槸鍚4浣 c鍒楁槸鍓7浣 鍦╞1鍐呰緭鍏=right(a1,4)c1鍐呰緭鍏=left(a1,7)閫変腑b1锛宑1 涓鍧椾笅鎷夊嵆鍙
绛旓細涓句緥璇存槑銆備緥濡傛湁琛ㄦ牸濡傚浘锛氳姹傜瓫閫夋暟鎹」浜屾湯4浣涓衡0000鈥濄傜涓姝ワ細鍦–1杈撳叆瀛楃鈥滆緟鍔╁垪鈥濄傜浜屾锛氬湪C2杈撳叆鍏紡锛=RIGHT(B2,4)銆傜粨鏋滃鍥撅細绗笁姝ワ細灏咰2鐨勫叕寮忎笅鎷夎嚦c7锛岀粨鏋滃鍥撅細绗洓姝ワ細閫夋嫨A1锛岀偣鍑绘帓搴忓拰绛涢夊浘鏍-绛涢夈傚鍥撅細绗簲姝ワ細閫夋嫨绛涢夋潯浠躲傚鍥撅細绛涢夌粨鏋滃鍥撅細...
绛旓細1銆佺敤榧犳爣鐐瑰嚮韬唤璇佸彿鐮佸悗闈㈢殑鏌愪釜琛ㄦ牸妗嗭紝濡傚浘銆2銆佸湪琛ㄦ牸妗嗛噷杈撳叆=right( ) 锛屽鍥俱3銆佸皢榧犳爣绉诲姩鍒版嫭鍙烽噷鐐瑰嚮涓涓嬶紝鎷彿棰滆壊鍙樻垚绱壊锛屽鍥俱4銆佺偣鍑昏韩浠借瘉鍙风爜鎵鍦ㄧ殑浣嶇疆C3锛屽鍥俱5銆佽緭鍏ョ殑=right( )閲岃嚜鍔ㄨ緭鍏3锛岃繖鏃舵垜浠湪C3鍚庤緭鍏モ滐紝4鈥濓紝浠ｈ〃鍙栬韩浠借瘉鍙风爜鍚4浣锛屽鏋滃彇5浣嶏紝灏辫緭鍏...
绛旓細鐢≧IGHT鍑芥暟鍙互鍋氬埌锛孯IGHT鍑芥暟鐨勮娉曞涓嬶細RIGHT(text,num_chars)杩欓噷鐨則ext鏄渶瑕佹彁鍙栨枃瀛楃殑鍘熸枃鏈 num_chars鍒欐槸甯屾湜鎻愬彇鏈鍙宠竟鐨勫灏戜釜瀛 渚嬪A1涓湁鏂囨湰鈥濡備綍鎻愬彇鍚庨潰鐨鍑犱釜瀛椻濓紝閭ｄ箞鍦˙1涓緭鍏ュ叕寮忊=RIGHT(A1,6)鈥濆洖杞﹀悗B1涓細鏄剧ずA1鐨勬渶鍚6涓瓧锛堝嵆鏂囨湰鈥滃悗闈㈢殑鍑犱釜瀛椻濓級...
绛旓細涓銆佸涓嬪浘锛岃鍦˙鍒涓彁鍙鍑哄墠涓鍗曞厓鏍鍚鍏浣嶆暟瀛锛岃繖鏃剁偣鍑籅2鍗曞厓鏍硷紝鐐瑰嚮鈥滃嚱鏁扳濄備簩銆佸湪鍏ㄩ儴鍑芥暟涓壘鍒扳淩IGHT鍑芥暟鈥濓紝閫変腑璇ュ嚱鏁板悗鐐瑰嚮鈥滅‘瀹氣濄備笁銆佺涓涓弬鏁颁腑杈撳叆鍓嶄竴涓崟鍏冩牸鈥淎2鈥濓紝绗簩涓嚱鏁拌緭鍏ラ渶瑕鎻愬彇鐨瀛楃鏁伴噺锛岀偣鍑烩滅‘瀹氣濄鍥銆佹渶鍚庝笅鎷夊～鍏呭嵆鍙湪B鍒楁彁鍙朅鍒楀悗鍏綅鏁板瓧浜嗐
绛旓細B1杈撳叆涓嬮潰鍏紡锛岀劧鍚庝笅鎷夊嵆鍙 =MID(A1,FIND("Color:",A1)+6,4)
绛旓細1銆佹墦寮闇瑕佺紪杈戠殑Excel琛ㄦ牸銆2銆佸闇鎻愬彇A鏍忔暟瀛楀乏杈瑰墠涓や綅锛屽湪B1鏍忚緭鍏=left锛圓1,2锛夛紝鈥渓eft鈥濊〃绀洪渶瑕佷粠宸﹁竟鎻愬彇鏁板瓧锛屸淎1鈥濊〃绀轰綘瑕佹彁鍙栫殑鏁板瓧鏍忥紝鈥2鈥濊〃绀鸿鎻愬彇鐨勬暟瀛椾綅鏁锛屼箣鍚庢寜Enter鍗冲彲銆3銆佺紪杈戞垚鍔鍚闇瑕佹彁鍙朅鏍忕殑鏁板瓧锛屽彧闇鐐瑰嚮B1鏍硷紝鍙充笅瑙掍細鍑虹幇涓涓疄蹇冪殑榛戠偣锛屽乏閿偣鍑婚粦鐐瑰悜涓...
