excel怎么从0000-9999 10000个数字中提取包含2,3,4,5的数值,如1002,0234,这些都算在其中. EXCEL 从0000-9999中找出由0、1、2、3、4 ...
EXCEL\u8868\u4e2d0000-9999\u4e00\u4e07\u4e2a\u6570\u5b57\u4e2d\uff0c\u5982\u4f55\u7528\u516c\u5f0f\u63d0\u53d6\u51fa\u5305\u542b\u4e09\u4e2a\u76f8\u540c\u5b57\u7b26\u7684\u6570\u5b57\u4e0b\u5217\u7a0b\u5e8f\u53ef\u4ee5\u627e\u51fa\u6240\u6709000\uff0c111\uff0c222\uff0c333\uff0c444\uff0c555\uff0c......999\u7684\u7b26\u5408\u9898\u76ee\u8981\u6c42\u7684\u6570\uff1a
Sub SpecilNum()
'\u8bbeA\u5217\u4e3a\u6e90\u5217\uff0cB\u5217\u4e3a\u76ee\u6807\u6570\u636e\u5217\uff0c\u8bbeB\u5217\u4e3a\u6587\u672c
Sheets("Sheet1").Select
lastrow = [A65536].End(xlUp).Row '\u4f5c\u7528\u662f\u5bfb\u627e\u5230\u672c\u8868\u683c\u7684\u6700\u540e\u4e00\u884c
For I = 1 To lastrow
MyCell = CStr(Right(Cells(I, 1), 3))
Select Case MyCell
Case "000"
Cells(I, 2) = MyCell
Case "111"
Cells(I, 2) = MyCell
Case "222"
Cells(I, 2) = MyCell
Case "333"
Cells(I, 2) = MyCell
Case "444"
Cells(I, 2) = MyCell
Case "555"
Cells(I, 2) = MyCell
Case "666"
Cells(I, 2) = MyCell
Case "777"
Cells(I, 2) = MyCell
Case "888"
Cells(I, 2) = MyCell
Case "999"
Cells(I, 2) = MyCell
End Select
Next I
End Sub
\u5168\u90e8\u7531\u8fd95\u4e2a\u6570\u5b57\u7ec4\u6210\u76844\u4f4d\u6570\u5b57\uff1f
\u4f60\u5c31\u6392\u5217\u7ec4\u5408\u4e00\u4e0b\u5427
Sub 全排列排除()
For i = 0 To 9999 Step 1 '10000个循环
If InStr(i, 2) > 0 Or InStr(i, 3) > 0 Or _
InStr(i, 4) > 0 Or InStr(i, 5) > 0 Then '包含指定则执行
S = S & " " & Format(i, "0000") '叠加到变量
End If
Next i
S = LTrim(S) '删除左边空格
t = Split(S, " ") '按空格分解成一维数组
t = Application.WorksheetFunction.Transpose(t) '转置
P = UBound(t) '下标(排列数)
Range("A1:A" & P).NumberFormatLocal = "@" '设文本格式
Range("A1:A" & P) = t '结果赋值到单元格
End Sub
假定A列0-9999
思路:1.利用条件格式,包含。(可以看,但是判断语句不可用。。)
2.笨办法。查找全部 - Ctr+A结果 - 改变格式,如此四次;B列B1=cell("format",A1)
格式没改变的返回结果是G,改变了的是别的(具体可F1帮助 cell)。到此已找出答案。
绛旓細y 鎴 yy 浠ヤ袱浣嶆暟鏉ヨ〃绀哄勾 (00 鈥 99)銆倅yy鎴杫yyy 浠ュ洓浣嶆暟鏉ヨ〃绀哄勾 (0000 鈥 9999)銆侶 浠ユ病鏈夊墠瀵奸浂鐨勬暟瀛楁潵鏄剧ず灏忔椂 (0 鈥 23)銆侶h 浠ユ湁鍓嶅闆剁殑鏁板瓧鏉ユ樉绀哄皬鏃 (00鈥 23)銆侻 浠ユ病鏈夊墠瀵奸浂鐨勬暟瀛楁潵鏄剧ず鍒 (0 鈥 59) 闇璺熷湪 h 鎴 hh 涔嬪悗銆侻m 浠ユ湁鍓嶅闆剁殑鏁板瓧鏉ユ樉绀哄垎 ...
绛旓細绗竴鏉 鍙互鐢ㄦ潯浠舵牸寮 瀵 浠嬩簬100鍒10 鐨 璁剧疆涓烘暟瀛 閫夋嫨灏忔暟鐐瑰悗1浣 浠嬩簬10鍒1 閫夋嫨灏忔暟鐐瑰悗2浣 鍏朵粬涓嶈
绛旓細鍋囪鍘熸暟鎹湪A鍒楋紝B1杈撳叆鍏紡 =TEXT(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"0000")B1鍏紡鍚戜笅澶嶅埗 杩欐椂锛屾寜B鍒楄繘琛屾帓搴忓嵆鍙
绛旓細濡傛灉鏁版嵁鍦ˋ1鍗曞厓鏍硷紝閭d箞鍦˙1鍗曞厓鏍艰緭鍏ヤ互涓嬪叕寮忥紝鐒跺悗鍚戜笅濉厖鍏紡 =MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)濡傛灉闇瑕佷繚鐣欏墠鍚庣殑涓や釜鍒嗛殧绗︼紝閭d箞鍙互杈撳叆 =MID(A1,FIND("/",A1),FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)+1)...
绛旓細璇曡瘯锛氬彸閿 銆 閫夋嫨鍗曞厓鏍煎睘鎬 銆 鏀规垚榛樿鐨勩愭爣鍑嗐戝晩锛岋紝锛 浣犳槸鎯冲紕鎴0000杩欎釜鏍峰瓙鍚楋紝锛岋紝杈撳叆1 -> 鏄剧ず00001 杈撳叆99 -> 00099锛燂紵杈撳叆 123 -> 00123 濡傛灉鎴戠殑鐞嗚В姝g‘鐨勮瘽锛岀敤杩欎釜鏂规硶锛氬彸閿 銆 閫夋嫨鍗曞厓鏍煎睘鎬 銆 鏀规垚鑷畾涔夛紝杈撳叆00000锛岀‘瀹氥傚鏋滀綘瑕佺殑鏄笅闈㈢殑鏁堟灉锛岃緭鍏99 -> ...
绛旓細yy锛氫袱浣嶆暟鐨勫勾浠(00-99)濡傦細Format("2010-5-19:8:5", "yy")=10 yyy锛氫笂闈㈢殑 yy 涓 y 缁撳悎鍦ㄤ竴璧 濡傦細Format(2010-5-1 9:8:5","yyy")=10121 Format("2010-5-19:8:5", "yy骞寸y澶")=10骞寸121澶 yyyy锛氬洓浣嶆暟鐨勫勾浠(0100-9999)濡傦細Format("2010-5-19:8:5", "yyyy"...
绛旓細=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鏌ユ壘濡備綍鎴彇鐗瑰畾涓や釜瀛楃涓棿鐨勬暟瀛楃殑瑙e喅鏂规硶濡備笅锛1銆佹墦寮瑕佸鐞嗙殑excel鏂囨。锛2銆佸亣璁捐澶勭悊鐨勬暟鎹瓨鍦╝鍒椾腑锛3銆佸湪B1鍗曞厓鏍间腑鍐欏涓嬪叕寮忥細=TEXT(-LOOKUP(1,-MID(A1,ROW($1:$99),4)),"0000")锛4銆佹嫋鍔╞1鍗曞厓鏍兼帶鍒剁偣鍚戜笅濉厖 杩欐牱锛孊鍒楃悊鍒扮殑鏁版嵁灏辨槸浣犺鐨勭粨鏋溿
绛旓細=TEXT(LOOKUP(9^99,--LEFT(A1,ROW($1:$99))),"0000!/00!/00 00!:00!:00")