您好，有个关于excel的问题想请教你一下 您好，有个excel筛选的问题向您请教一下
\u5927\u795e\u5173\u4e8eexcel\u7684\u51e0\u4e2a\u95ee\u9898\u60f3\u8bf7\u6559\u4f60\uff0c\u53ef\u4ee5\u7ed9\u4e2a\u8054\u7cfb\u65b9\u5f0f\u4e48\uff1f\u8bf7\u3010\u767e\u5ea6\u4e91\u76d8\u3011\uff08\u4e00\u4e2a\u4f7f\u7528\u767e\u5ea6\u5e10\u53f7\u767b\u5f55\u7684\u53ef\u4ee5\u5bf9\u8bdd\u548c\u4f20\u6587\u4ef6\u7684\u5e94\u7528\u8f6f\u4ef6\uff09\u8054\u7cfb\uff0c\u5982\u4e0b\u56fe\uff1a
MOD(ROW(6:6)-5,10)=1
这个用自定义函数会方便些。
自定义函数代码：
Function Fill(ByVal rng As Range)
Dim str$, st1$, st2$, st3$, FinX%, i%, L%, k%, j%, ar()
str = rng
If str = "" Then Fill = "": Exit Function
L = Len(rng)
FinX = InStr(str, "-")
Do Until IsNumeric(Mid(str, FinX - 1 - k, 1)) = False
st1 = st1 & Mid(str, FinX - 1 - k, 1)
k = k + 1
Loop
st3 = Left(str, FinX - 1 - k)
k = 0
Do Until IsNumeric(Mid(str, L - k, 1)) = False
st2 = Mid(str, L - k, 1) & st2
k = k + 1
Loop
For i = st1 To st2
ReDim Preserve ar(j)
ar(j) = st3 & i
j = j + 1
Next
Fill = Join(ar(), ",")
End Function
B1=RIGHT(RIGHT(A1,LEN(A1)-FIND("-",A1)))-RIGHT(LEFT(A1,FIND("-",A1)-1))向下复制
C1=LEFT(A1,FIND("-",A1)-1)向下复制
D1=IF(COLUMN(A1)>$B1,"",","&LEFT($C1,LEN($C1)-1)&COLUMN(B1))向下向右复制
I1=C1&D1&E1&F1&G1&H1向下复制
绛旓細闂鍏細wps 琛ㄦ牸鎵撳嵃鍑烘潵鎬庝箞娌℃湁鏍煎瓙 閭ｆ槸鍥犱负娌℃湁璁剧疆琛ㄦ牸鐨勮竟妗嗭紝閫夋嫨鎵闇瑕佺殑閮ㄥ垎鍚庡彸鍑婚夎缃崟鍏冩牸锛屽湪杈规閫夐」鍗′腑璁剧疆銆傞棶棰樹竷锛氭庝箞鍦EXCEL涓婂仛琛ㄦ牸 涓轰粈涔堟墦鍗板嚭鏉ユ病鏈夎〃鏍煎晩 璁茬殑璇︾粏鐐 閫変腑浣犻渶瑕佸姞琛ㄦ牸绾跨殑鍖哄煙锛屼竴鑸湪鑿滃崟鏍忓彸涓婃湁涓涓儚灏忓鐢熲滅敯瀛楁湰鈥濈敯瀛楁寜閽紝鐐瑰嚮涓涓嬪彸闈㈢殑...
绛旓細閿佸畾鐘舵侊細妫鏌ュ伐浣滆〃鐨勪繚鎶ょ姸鎬併傚鏋滄暣涓伐浣滆〃琚繚鎶わ紝浣犲皢鏃犳硶鍦ㄤ换浣曞崟鍏冩牸涓緭鍏ユ暟鎹備綘鍙互閫氳繃"瀹￠槄"閫夐」鍗′腑鐨"淇濇姢宸ヤ綔琛"鏉ヨВ闄ゅ伐浣滆〃鐨勪繚鎶ゃ傞棶棰樺伐浣滅翱鎴栧簲鐢ㄧ▼搴忥細鏈夋椂锛孍xcel鍙兘浼氬嚭鐜伴敊璇垨闂锛瀵艰嚧鏃犳硶杈撳叆鏁版嵁銆傚皾璇曞叧闂苟閲嶆柊鎵撳紑Excel宸ヤ綔绨匡紝鎴栬呴噸鏂板惎鍔‥xcel搴旂敤绋嬪簭锛岀湅鐪嬫槸鍚﹁В鍐...
绛旓細杩欎釜鐢ㄨ嚜瀹氫箟鍑芥暟浼氭柟渚夸簺銆傝嚜瀹氫箟鍑芥暟浠ｇ爜锛欶unction Fill(ByVal rng As Range) Dim str$, st1$, st2$, st3$, FinX%, i%, L%, k%, j%, ar() str = rng If str = "" Then Fill = "": Exit Function L = Len(rng) FinX = InStr(str, "-") Do Until ...
绛旓細G11=SUMPRODUCT(($B$2:$B$7=$F11)*($C$2:$C$7=G$10)*($D$2:$D$7))鍚戝彸鍚戜笅鎷
绛旓細鎮ㄥソ锛杩欎釜鏈夊彲鑳藉氨鏄箣鍓嶆枃浠舵病鏈変繚瀛樺ソ鎴栬呮枃浠跺彂鐢熶簡鎹熷潖锛屽鑷存棤娉曟仮澶嶃傝繖涓椂鍊欓渶瑕侀噸鏂拌繘琛屽仛涓涓〃鏍笺
绛旓細=if(max(a1:c1)>10,100)+SUM(IF(D1:J1>=95,1000,IF(D1:J1>=90,500,IF(D1:J1>=85,100,0)))鐒跺悗鎸夆渃trl鈥+鈥渟hift鈥+鈥滃洖杞︹濅笁涓敭缁撴潫杈撳叆銆傛敞鎰忥紝杩欎釜鏄暟缁勫叕寮忚緭鍏ュ悗锛屽崈涓囦笉瑕佺洿鎺ユ寜鈥滃洖杞︹濄備笉鐒跺叕寮忔棤鏁堛=== 浣犳渶鍚庣殑绗3閮ㄥ垎鐨勬弿杩版病鐪嬫噦銆傚鏋滄槸瑕佸湪鏈缁堢殑鎬诲垎涓婂姞涓...
绛旓細宸ュ叿锛EXCEL2003 1銆佹墦寮鐢佃剳妗岄潰锛岀劧鍚庣偣鍑诲彧璇荤殑鍦Excel鏂囦欢锛屽鍥炬墍绀猴紝鍙抽敭鐐瑰嚮鏂囦欢銆2銆佺劧鍚庡湪鍑烘潵鐨勫璇濇鐨勯夐」閲岄潰锛屼笅婊戝埌搴曢儴锛岀偣鍑诲睘鎬э紝濡傚浘鎵绀恒3銆佺劧鍚庯紝鍦ㄥ睘鐨勯〉闈紝鐪嬩竴涓嬫渶涓嬮潰鏈変竴涓彧璇荤殑閫夐」鏄嬀閫変笂鐨勩4銆佹渶鍚庯紝鎶婂墠闈㈢殑鍙鍕鹃夊幓鎺夊嵆鍙紝灏卞彲浠ヨВ鍐充笉鑳借闂鐨勯棶棰銆
绛旓細鍒楄閿佸畾鐨.浣犲張妯潃鎷栬〃绀鸿埅涓嶅彉,鎵浠ュ氨涓嶅彉鍟.浣犲彲浠ュ湪鍏紡閲岄潰寮曠敤column鏉ヨ揪鍒扮洰鐨 A1=Sheet1!b3+Sheet1!C3鏀规垚 A1=INDIRECT(CONCATENATE("Sheet1!b",COLUMN()+2))+INDIRECT(CONCATENATE("Sheet1!C",COLUMN()+2))鍥犱负鏄湪A鍗曞厓鏍兼墍浠olumn()=1 鍔犱笂2灏辩瓑浜3浜. 褰撲綘鎷栧埌Bcolumn...
绛旓細浣犻兘宸茬粡濂椾笂浜 灏辫鍚嶄綘宸茬粡鎺掑ソ鐗堜簡鈥︹︾焊寮犲綋鐒舵槸瑕佷笌浣犵殑 濂楁墦 绾稿紶澶у皬涓鑷村柦鈥︹︿綘鐨勫鎵撶焊 鏄粈涔堟牱瀛愮殑鍟婏紵 濡傛灉浣犱笉鐭ラ亾杩欎釜濂楁墦绾告槸澶氬ぇ鐨 閭ｄ箞浣犲氨鐢ㄥ昂瀛愭祴閲忎竴涓嬶紝鐒跺悗 鑷畾涔夌焊寮犵殑澶у皬鈥︹
绛旓細1銆侀鍏堟墦寮Excel锛杩涘叆缂栬緫鐣岄潰锛岀偣鍑绘渶鍙充笂鏂圭殑鑿滃崟鎸夐挳銆2銆佺偣鍑婚夐」涓淓xcel閫夐」鈥濇寜閽3銆佺劧鍚庡湪鎵撳紑鐨勭獥鍙ｄ腑锛岀偣鍑诲乏渚р滆祫婧愨濄4銆佷箣鍚庡湪閲岄潰鐐瑰嚮鈥滆繍琛孧icrosoft office璇婃柇鈥濄傚璇婃柇鏂囦欢杩涜淇锛屼竴鑸彲浠ヨВ鍐崇▼搴忛敊璇犳垚鏂囦欢鏍煎紡涓㈠け闂銆5銆佸鏋滄槸鍥犱负鍏煎鎬у拰鐗堟湰闂瀵艰嚧鐨勬枃浠舵牸寮忎涪澶憋紝...
