excel中如何实现在sheet1中的1-100行随机抽取10行放在sheet2中1-10行中？ 怎么实现excel中sheet1中的数据变化，sheet2中...
office excel\u4e2d\uff0c\u5982\u4f55\u5b9e\u73b0\u5728sheet1\u4e2d\u968f\u673a\u62bd\u53d610\u4e2a\u9898\u76ee\u653e\u5728sheet2\u4e2d\u76841-10\u884c\uff1f\u5982\u679c\u7528\u516c\u5f0f\u505a\u968f\u673a\u6570\uff0c\u4f1a\u6709\u91cd\u590d\u7684\u6570\u636e\u51fa\u73b0\uff0c\u6240\u4ee5\u6700\u597d\u7528VBA\u6765\u505a\uff0c\u53ef\u4ee5\u5728\u4ee3\u7801\u4e2d\u76f4\u63a5\u5224\u65ad\u4e0d\u91cd\u590d\u7684\u968f\u673a\u6570\uff0c\u4ee3\u7801\u5982\u4e0b\uff1a
Sub sj()
For i = 1 To 10
10: k = Int(Rnd() * 30) + 1 '\u8303\u56f4\u4ece1\u523030
If i > 1 Then
For j = 1 To i
'\u5224\u65ad\u662f\u5426\u91cd\u590d
If Sheets("sheet2").Cells(j, 1) = k Then GoTo 10:
Next
End If
Sheets("sheet2").Cells(i, 1) = k
Sheets("sheet2").Cells(i, 2) = Sheets("sheet1").Cells(k, 2)
Next
End Sub
\u697c\u4e3b\u524d\u9762A-C\u5217\u5e94\u8be5\u8fd8\u6709\u4e00\u4e2a\u5bf9\u6bcf\u7ec4\u7684\u540d\u79f0\u5427\u3002
\u6709\u4e24\u79cd\u7b80\u5355\u7684\u529e\u6cd5\uff1a
1. \u76f4\u63a5ctrl+\u9f20\u6807\u5de6\u952e\uff0c\u7136\u540e\u53f3\u62c9\uff0c\u590d\u5236sheet1,\u7136\u540e\u9009\u4e2dF-V\u5217\uff0c\u53f3\u952e\u5220\u9664\u6574\u5217\uff0c\u7136\u540e\u5728\u590d\u5236\u51fa \u7684 \u5de5\u4f5c\u8868E\u5217\u4e2d\u7b2c\u4e94\u884c\u5355\u5143\u683c\uff08E5\uff09\u4e2d\u8f93\u5165\u516c\u5f0f=sheet1!w5(\u6216\u8005\u76f4\u63a5\u8f93\u5165\u201c=\u201d\u7136\u540e\u70b9\u51fbsheet1\u7684W5\u5355\u5143\u683c)\uff0c\u7136\u540e\u5411\u4e0b\u62c9\uff0c\u590d\u5236\u5355\u5143\u683c\uff1b
\u4f18\u70b9\uff1a\u64cd\u4f5c\u7b80\u5355
\u7f3a\u70b9\uff1asheet1\u4e2d\u6bcf\u7ec4\u6392\u5217\u987a\u5e8f\u6539\u53d8\u7684\u8bdd\u5c31\u4e0d\u80fd\u6b63\u786e\u5bf9\u5e94
2 \u6bd4\u5982\u7b2c\u4e00\u7ec4\u5728\u7b2c\u4e94\u884c\uff0cC\u5217,\u4eba\u657050.
\u76f4\u63a5\u5c06sheet1\u4e2dA-D\u5217\u590d\u5236\u5230sheet2\u4e2d\uff0c\u7136\u540e\u5728sheet2\u4e2d\u8f93\u5165\uff1a
=vlookup(C5,Sheet1!$C$5:$W$390,21,0),\u5176\u4e2dC5\u4ee3\u8868\u7b2c\u4e00\u7ec4\u7ec4\u540d\u6240\u5728\u7684\u5355\u5143\u683c\uff0c\u5982\u679c\u7b2c\u4e00\u7ec4\u7ec4\u540d\u5728B5\u5355\u5143\u683c\u5219\u516c\u5f0f\u4e2d$C$5\u8981\u6539\u4e3a$B$5\uff0c21\u8981\u6539\u4e3a22\uff0c\u7b2c\u4e00\u7ec4\u7ec4\u540d\u6240\u5728\u5355\u5143\u683c\u4e3aA5\u5219$C$5\u8981\u6539\u4e3a$A$5,21\u8981\u6539\u4e3a23
\u4f18\u70b9\uff1a\u4ee5\u540e\u65e0\u8bba\u7ec4\u540d\u987a\u5e8f\u5982\u4f55\u6539\u53d8\uff0csheet2\u4e2d\u5206\u503c\u90fd\u662f\u6b63\u786e\u7684\uff0c\u65e0\u9700\u91cd\u65b0\u8c03\u6574\uff0c\u4e00\u52b3\u6c38\u9038
\u7f3a\u70b9\uff1a\u64cd\u4f5c\u7a0d\u5fae\u590d\u6742
Sheet1
如图数据表共4列：序号、数据1、数据2、数据3
增加两个辅助列
E2=RAND()
F2=RANK(E2,$E$2:$E$101)
两公式向下复制到101行。
Sheet2
A2=INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$F$1:$F$101,0))向右复制到D列向下复制到第11行。按F9可以刷新数据。
设需要复制的列为a到c列。
在sheet1的d1输入：
=RAND()
然后选中d1下拉到d100单元格。
在sheet2的a1输入：
=INDEX(sheet1!A$1:A$100,MATCH(SMALL(sheet1!$D$1:$D$100,ROW()),sheet1!$D$1:$D$100,0))
选中sheet2的a1横拉至c1，再选中a1到c1下拉至第10行就可以了。
该函数为更具sheet1的d列的随机数进行随机取值。需要试取值变化时，经需求选中sheet2的任意空单元格，然后按“delete”键就可以了。
在sheet2的A1输入公式：
=OFFSET(Sheet1!A$1,INT(RAND()*100),)
然后，向右复制（复制或“拖”的列数与sheet1中内容的列数相同）
选中所有内容，再向下复制到第10行
sheet2!A1
=OFFSET(Sheet1!$A$1,INT(RAND()*100),)
下拉到A10
分太少了，懒得编，100起
绛旓細鍑芥暟鍏紡=sum(sheet1:sheet4!b3)銆備互EXCEL2011鎿嶄綔涓轰緥锛1銆佹墦寮EXCEL琛ㄦ牸锛宻heet1鍒皊heet4鐨勬眰鍜屽埌鎬昏〃涓2銆佺偣鍑讳笅鏂圭殑姹囨昏〃锛岄紶鏍囬変腑姹囨昏〃涓渶瑕佹眹鎬荤殑鍖哄煙锛屽鍥炬墍绀猴紝浠嶣3鍒癊12鎵鍦ㄧ殑鍖哄煙銆3銆侀変腑B3鍗曞厓鏍硷紝杈撳叆鍑芥暟鍏紡=sum(sheet1:sheet4!b3)锛屽洖杞﹂敭纭畾銆4銆乻heet1锛歴heet4琛ㄧず璺ㄥ伐浣...
绛旓細鏂规硶/姝ラ 1 灏嗗涓狤XCEL鏂囦欢涓殑宸ヤ綔琛紝姹囨诲埌涓涓EXCEL涓銆2 涓句緥涓锛氬EXCEL涓浉閭荤殑澶氫釜宸ヤ綔琛ㄥ悓鏃惰繘琛屼互涓嬫搷浣滐紝濡傗滆缃崟鍏冩牸鏍煎紡鈥濄傛寜浣廠hift閿紝鐐瑰嚮榧犳爣宸﹂敭杩涜鎿嶄綔锛屽湪宸ヤ綔绨垮簳閮ㄩ夋嫨鐩搁偦鐨勫伐浣滆〃锛岃繘琛屽涓伐浣滆〃鐨勨滆缃崟鍏冩牸鏍煎紡鈥濈殑鎿嶄綔銆3 涓句緥浜岋細瀵笶XCEL涓笉鐩搁偦鐨勫涓伐浣滆〃鍚屾椂...
绛旓細鍙彲浠ュ悓姝ユ洿鏂般備綘鐨澶囦唤瑕佹眰涓嶅ソ鍔烇紝鍋囪鏇存柊涓娆★紝澶囦唤鏁版嵁鍜屾洿鏂颁竴涓暟鎹紝灏变細鍗2涓牸锛屽啀鏇存柊涓娆★紝灏辫鍗3涓牸銆傚鏋滀竴涓暣琛ㄤ腑鏈夊嚑涓洿鏂版暟鎹紝鏀惧湪鍝噷鍛锛熷缓璁細涓涓〃瀹屾垚鍚庯紝澶嶅埗--绮樿创鍒版煇鏍糞HEET涓紝浣滀负澶囦唤銆傛暟鎹洿鏂板悗锛屽啀澶嶅埗--绮樿创鍒板彟涓SHEET鍋氬浠姐傚悓姝ュ緢绠鍗曪細鍦⊿HEET2 A1...
绛旓細鐢ㄤ唬鐮佹潵瀹炵幇锛屽湪鎸夐挳鐨浠ｇ爜涓緭鍏ュ涓嬩唬鐮侊細sheet1!i4=vlookup(e4,sheeet2!a:c,3,0) 'sheet1鐨処4鏍兼樉绀簊heet2閲瀵瑰簲璇ヨ揣鍙风殑搴撳瓨缁撲綑銆俰=1 do while a(i)="" '鍦╯heet3涓搴斿啓鍏heet1涓殑鏁版嵁 sheet3!a(i)=sheet1!e4 sheet3!b(i)=sheet1!f4 sheet3!c(i)=sheet1!g4 ...
绛旓細1銆佺偣鍑诲乏涓嬭Sheet2宸ヤ綔琛ㄦ爣绛撅紝鎸変笅Shift閿鐨鎯呭喌涓嬶紝鎸変笅鏈鍚庝竴涓嬪伐浣滆〃鐨勬爣绛撅紝灏嗛変腑鐨勫伐浣滆〃澶勪簬缁勫悎鐘舵侊紱2銆佸湪F3鍗曞厓鏍艰緭鍏ヤ互涓嬫暟缁勫叕寮忥紝鎸塁trl+Shift+Enter缁勫悎閿粨鏉 =INDEX(Sheet1!A:A,SMALL(IF(Sheet2!B$2:B$15<>"-",ROW($2:$15),4^8),MID(CELL("filename"),FIND("Sheet...
绛旓細寰楀厛鏈変釜鍩虹琛細鍚嶇О銆佸瀷鍙枫佷环鏍硷紝鏈鍓嶉潰鍔1鍒楋紝A2鏍煎叕寮忎负锛欱2&C2锛屾妸鍚嶇О鍜屽瀷鍙疯繛鎺ヨ捣鏉ヤ綔涓1涓垪锛孉鍒楀叏閮ㄥ姝ゃ傚湪浣鐨鐩爣琛ㄦ牸涓紝涔熷姞涓垪D鎶婂悕绉板瀷鍙疯繛璧锋潵锛岀劧鍚嶤鍒楀叕寮忕敤vlookup鏉瀹炵幇灏卞彲浠ヤ簡銆
绛旓細excel鐨鐩稿叧鐭ヨ瘑 2013-04-26 excel绠楀伐璧 15 2013-07-02 excel sheet绛涢 8 2011-03-28 excel琛ㄦ牸鎿嶄綔鎶宸 748 2010-04-23 excel2003鏁欑▼鍏ㄩ泦 314 2013-12-03 鐢╡xcel鍋氬伐璧勮〃鍚 6 鏇村鍏充簬excel鐨勭煡璇 > 姝ｅ湪姹傚姪 鎹竴鎹 鍥炵瓟闂,璧㈡柊鎵嬬ぜ鍖 鑻︾瓑1鍒嗛挓: 鑸掕偆浣冲绁涚棙鏈夋病鏈変粈涔堢敤 鍥炵瓟 ...
绛旓細闂涓滃崐甯﹀鑷姩璁＄畻鍏紡鈥濇病鐪嬫槑鐧斤紝sheet搴忓彿閫掑锛屾寜濡備笅鎿嶄綔鍗冲彲銆傦紙鐣岄潰鏄2016鐗鐨锛屽叾浠栫増鏈殑浣嶇疆鍙兘涓嶅悓锛1銆侀殢渚挎壘涓涓猻heet锛屽缓绔嬩竴涓綘闇瑕佺殑搴忓垪 2銆佸杩欎釜搴忓垪寤虹珛閫忚琛紝灏嗗簭鍙峰瓧娈垫斁鍒扮瓫閫夊櫒涓3銆侀変腑搴忓彿锛屽湪鈥滃垎鏋愨濋夐」鍗★紝鈥滈夐」鈥濃斺斾笅鎷夎彍鍗曪紝鐐瑰嚮鈥滄樉绀烘姤琛ㄧ瓫閫夐」鈥濆苟...
绛旓細绠鍗曚妇涓緥瀛愶紝濡傚浘锛氭妸涓婂浘涓璼heet1涓璄鍒楁诲垎鑷姩鐢熸垚鍒皊heet3涓瑽鍒椼1銆鍦╯heet3 B2鍗曞厓鏍艰緭鍏モ滐紳sheet1!e2鈥2銆佸洖杞﹀悗灏辫兘鏄剧ずsheet1涓璭2鍗曞厓鏍煎唴瀹广3銆佹妸b2涓嬫嫋灏卞緱鍒版暣鍒鐨鎬诲垎浜嗐
绛旓細鍋囪浣sheeet1涓暟鎹粨鏋勬槸锛歛bc涓夊垪鍒嗗埆鏄鍚嶏紝璇枃鎴愮哗鍜屾暟瀛︽垚缁 閭ｄ箞sheet2琛鐨a1杈撳叆 =vlookup("寮犱笁",sheet1!A:B,2,0)鍗冲彲
