怎样在excel工作表中快速建立多个工作簿 怎样快速汇总多个excel工作簿
\u5982\u4f55\u5728\u4e00\u4e2aexcel\u8868\u683c\u5de5\u4f5c\u7c3f\u4e2d\u4e00\u6b21\u6027\u521b\u5efa\u591a\u4e2a\u5de5\u4f5c\u8868\u8bbe\u7f6e\u5de5\u4f5c\u8868\u8868\u540d\uff1a
\u521b\u5efa\u900f\u89c6\u8868\uff1a
\u751f\u6210\u5de5\u4f5c\u8868\uff1a
4. \u6548\u679c\u56fe\uff1a
\u5c06\u9700\u8981\u5408\u5e76\u7684excel\u5de5\u4f5c\u7c3f\u6587\u4ef6\u653e\u7f6e\u5728\u4e00\u4e2a\u6587\u4ef6\u5939\u4e2d\u3002
\u6bcf\u4e2a\u6587\u4ef6\u5939\u4e2d\u7684\u5de5\u4f5c\u7c3f\u90fd\u8f93\u5165\u4e86\u7b80\u5355\u7684\u5185\u5bb9\u3002
\u5728\u8be5\u6587\u4ef6\u5939\u4e2d\uff0c\u65b0\u5efa\u7acb\u4e00\u4e2a\u65b0\u7684excel\u5de5\u4f5c\u7c3f\u6587\u4ef6\u3002\u91cd\u547d\u540d\u4e3a8.
\u6253\u5f00\u65b0\u5efa\u7acb\u7684excel\u5de5\u4f5c\u7c3f\u6587\u4ef68.\u6309Alt+F11\u6216\u8005\u5c06\u9f20\u6807\u79fb\u52a8\u5230\u4e0b\u65b9\u5de5\u4f5c\u8868\u540d\u79f0sheet1\u4e0a\u53f3\u952e\uff0c\u9009\u62e9\u67e5\u770b\u4ee3\u7801\u3002
\u5728\u5f39\u51fa\u7684\u4ee3\u7801\u7f16\u8f91\u7a97\u53e3\u4e2d\uff0c\u8f93\u5165\u4ee3\u7801\u3002\u6ce8\u610f\uff1a\u901a\u8fc7\u5feb\u6377\u952eAlt+F11\u6253\u5f00\u7684\u7a97\u53e3\u5982\u4e0b\u6ca1\u6709\u76f4\u63a5\u590d\u5236\u4ee3\u7801\u7684\u9762\u677f\uff0c\u9700\u8981\u70b9\u51fb\u5de6\u4e0a\u65b9\u7684\u5de5\u7a0b-VBA project\u6a21\u5757\uff0c\u53cc\u51fbsheet1\uff0c\u5373\u53ef\u6253\u5f00\u4ee3\u7801\u7a97\u53e3\u3002\u800c\u6b65\u9aa43\u4e2d\u7684\u7b2c\u4e8c\u79cd\u65b9\u6cd5\u53ef\u4ee5\u76f4\u63a5\u6253\u5f00\u4ee3\u7801\u8f93\u5165\u7a97\u53e3\u3002
\u5728\u4ee3\u7801\u7a97\u53e3\u4e2d\uff0c\u9ecf\u8d34\u4e0b\u5217\u4ee3\u7801\uff1a
Sub CombineFiles()
Dimpath As String
DimFileName As String
DimLastCell As Range
DimWkb As Workbook
DimWS As Worksheet
DimThisWB As String
Dim MyDir AsString
MyDir =ThisWorkbook.path & "\"
'ChDriveLeft(MyDir, 1) 'find all the excel files
'ChDir MyDir
'Match =Dir$("")
ThisWB =ThisWorkbook.Name
Application.EnableEvents = False
Application.ScreenUpdating = False
path =MyDir
FileName =Dir(path & "\*.xls", vbNormal)
Do UntilFileName = ""
If FileName ThisWB Then
Set Wkb = Workbooks.Open(FileName:=path & "\"& FileName)
For Each WS In Wkb.Worksheets
Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address = Range("$A$1").AddressThen
Else
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
Set Wkb =Nothing
Set LastCell= Nothing
End Sub
\u70b9\u51fb\u83dc\u5355\u680f\u8fd0\u884c-\u8fd0\u884c\u5b50\u8fc7\u7a0b-\u7528\u6237\u7a97\u4f53\u3002\u5173\u95ed\u4ee3\u7801\u8f93\u5165\u7a97\u53e3\u3002\u6253\u5f00excel\u5de5\u4f5c\u7c3f8.\u53ef\u4ee5\u770b\u5230\u4e0b\u65b9\u5df2\u7ecf\u591a\u4e86\u5f88\u591a\u65b0\u7684\u5de5\u4f5c\u8868\u2014\u2014\u6b64\u65f6\uff0c\u6211\u4eec\u5df2\u7ecf\u5c06\u4e4b\u524d\u7684\u5de5\u4f5c\u7c3f\u4e2d\u7684\u5de5\u4f5c\u8868\u90fd\u590d\u5236\u5230\u4e86\u8fd9\u4e00\u65b0\u5efa\u7684\u5de5\u4f5c\u7c3f\u4e2d\u3002
鼠标放在sheet条上——右键——选定全部工作表——移动或复制工作表——在建立副本上打钩
表数会级数增长
按SHIFT+F11 按30次
或者 工具-选项-常规
新工作簿里的工作表数 改为 30
这样 新建一个工作簿里面就自动有30个工作表
只有一个一个建立工作薄，针对您的问题暂时无法解决
2007也一样，自己累点吧
绛旓細1. 澶嶅埗绮樿创锛氬鏋滄偍宸茬粡鏈変竴涓〃鏍锛屽彲浠ョ洿鎺ュ鍒舵暣涓〃鏍硷紝鐒跺悗绮樿创鍒版柊鐨勪綅缃傞夋嫨鍘熻〃鏍煎苟鎸変笅Ctrl+C锛岀劧鍚庡湪鏂颁綅缃寜涓婥trl+V锛屽嵆鍙敓鎴愪竴涓浉鍚岀殑琛ㄦ牸銆2. 妯℃澘锛欵xcel鎻愪緵浜嗚澶氬唴缃ā鏉匡紝杩欎簺妯℃澘鍖呭惈浜嗗悇绉嶄笉鍚岀被鍨嬬殑琛ㄦ牸銆傛偍鍙互閫氳繃鎵撳紑Excel骞跺湪鈥滄枃浠垛濊彍鍗曚腑閫夋嫨鈥滄柊寤衡濇潵璁块棶杩欎簺妯℃澘锛...
绛旓細蹇鎵归噺鍒涘缓Excel宸ヤ綔琛锛屾暀浣1鍒嗛挓杞绘澗瀹屾垚鍦‥xcel涓壒閲忓垱寤哄伐浣滆〃鏄竴椤圭箒閲嶇殑浠诲姟锛屼絾鍏跺疄閫氳繃鏁版嵁閫忚琛ㄥ彲浠ヨ交鏉惧疄鐜般傞鍏堬紝鎵撳紑Excel 2016锛屾柊寤轰竴涓伐浣滆〃锛岀劧鍚庡湪浠绘剰鍗曞厓鏍艰緭鍏ュ伐浣滆〃璇存槑锛屾寜鐓ф墍闇椤哄簭杈撳叆宸ヤ綔琛ㄥ悕绉帮紝閫変腑鎵鏈夊唴瀹广傛帴涓嬫潵锛岃繘鍏ユ彃鍏ラ夐」鍗★紝鐐瑰嚮鏁版嵁閫忚琛ㄦ寜閽紝閫夋嫨浣犵殑鏁版嵁鍖哄煙...
绛旓細棣栧厛锛屾墦寮Excel骞跺垱寤轰竴涓柊鐨勫伐浣滅翱銆傜偣鍑烩滄枃浠垛濊彍鍗曪紝閫夋嫨鈥滄柊寤衡濓紝鐒跺悗鍦ㄦā鏉垮垪琛ㄤ腑閫夋嫨鈥滅┖鐧藉伐浣滅翱鈥銆傝繖灏嗕负浣犳彁渚涗竴涓┖鐧界殑Excel宸ヤ綔琛紝浣犲彲浠ュ紑濮嬪湪鍏朵腑杈撳叆鎴愮哗鏁版嵁銆傛帴涓嬫潵锛屽湪宸ヤ綔琛ㄤ腑杈撳叆鎴愮哗鏁版嵁銆傚湪绗竴琛岋紝浣犲彲浠ヨ緭鍏ュ鐢熺殑濮撳悕鎴栧叾浠栨爣璇嗕俊鎭傜劧鍚庯紝鍦ㄤ笅涓琛屽紑濮嬭緭鍏ユ瘡涓鐢熺殑鎴愮哗銆
绛旓細灏"2016骞存湀鎶ヨ〃"鎷栧埌绛涢夋涓紝鐒跺悗閫変腑C1鍗曞厓鏍硷紝鍒囨崲鍒版暟鎹忚琛ㄥ伐鍏风殑鍒嗘瀽閫夐」鍗★紝閫夋嫨"鏄剧ず鎶ヨ〃绛涢夐〉"锛蹇瀹屾垚宸ヤ綔琛鍛藉悕鐨勮缃傚湪鎵鏈夊伐浣滆〃鏍囩涓婂彸閿夋嫨"閫夊畾鍏ㄩ儴宸ヤ綔琛"锛屾帴鐫鍏ㄩ夊唴瀹癸紝浣跨敤"娓呴櫎"鍔熻兘锛屽皢閫忚琛ㄦ竻闄ゃ傝繖鏍凤紝浣犱究鎴愬姛鎵归噺鍒涘缓浜嗗懡鍚嶅噯纭笖鏃犻澶栧唴瀹圭殑宸ヤ綔琛紝鏁翠釜杩囩▼鍙...
绛旓細棣栧厛锛屼綘闇瑕佹槑纭綘鎯宠鎻掑叆鍗曞厓鏍肩殑浣嶇疆锛屽彲鑳芥槸鍦ㄧ幇鏈夎鐨勫紑澶淬佺粨灏撅紝鎴栬呭湪鍒椾腑鎻掑叆鏂扮殑鍒銆傚叾娆★紝纭畾瑕佹彃鍏ョ殑鍗曞厓鏍兼暟閲忋備緥濡傦紝濡傛灉浣犻渶瑕佹彃鍏ュ洓涓崟鍏冩牸锛屼綘闇瑕侀鍏堝喅瀹氬畠浠殑椤哄簭鍜屼綅缃傜劧鍚庯紝鍙抽敭鐐瑰嚮浣犳兂鎻掑叆鐨勪綅缃紝杩欏皢鎵撳紑涓涓笂涓嬫枃鑿滃崟銆傚湪鑿滃崟涓紝閫夋嫨鈥滄彃鍏モ濋夐」銆傝繖灏嗗厑璁镐綘鍦ㄦ寚瀹...
绛旓細1. **鎵撳紑 Excel 鏂囦欢锛** 鎵撳紑鎮ㄨ鍦ㄥ叾涓彃鍏ユ柊琛ㄦ牸鐨 Excel 鏂囦欢銆2. **閫夋嫨宸ヤ綔琛锛** 鍦ㄥ簳閮ㄦ爣绛炬爮涓紝閫夋嫨鎮ㄥ笇鏈涘湪鍏跺悗鎻掑叆鏂拌〃鏍肩殑宸ヤ綔琛ㄣ3. **鎻掑叆鏂板伐浣滆〃锛** 鍦 Excel 鑿滃崟鏍忔垨宸ュ叿鏍忎腑锛屾偍鍙互鎵惧埌 "鎻掑叆" 鎴栫被浼肩殑閫夐」銆傜偣鍑昏閫夐」锛岀劧鍚庨夋嫨 "宸ヤ綔琛"銆傝繖灏嗗湪閫夊畾鐨勫伐浣滆〃...
绛旓細excel鎵归噺寤虹珛鍓湰鐨勬柟娉 Excel涓鍙互閫氳繃澶嶅埗宸ヤ綔琛鐨勬柟寮忥紝浣垮凡缁忚缃ソ鏍煎紡鐨琛ㄦ牸锛屽缓绔嬪涓壇鏈 鏂规硶濡備笅锛1.宸叉湁璁剧疆濂芥牸寮忕殑宸ヤ綔琛細2.鍙抽敭鐐瑰嚮宸ヤ綔琛ㄦ爣绛撅紝閫夋嫨鈥滅Щ鍔ㄥ拰澶嶅埗鈥濓細3.鍦ㄤ笅闈㈠璇濇涓嬀閫夆滃缓绔嬪壇鏈濓紝鐐瑰嚮纭畾锛4.杩欐牱灏卞缓绔嬩簡涓涓壇鏈紝閲嶅鎿嶄綔鍙缓绔嬪涓壇鏈細
绛旓細1銆侀鍏堟墦寮鏌愪釜EXCEL宸ヤ綔钖勶紝鐐瑰嚮寮濮嬮夐」鍗♀斺斿崟鍏冩牸缁勨斺旀彃鍏ユ寜閿紝鍦ㄦ彃鍏ユ寜閿殑涓嬫媺閫夐」涓夋嫨鎻掑叆宸ヤ綔琛銆2銆佸彲浠ョ湅鍒板湪鍒氭墠鐨勬椿鍔ㄥ伐浣滆〃宸﹁竟锛屽鍔犱簡涓涓柊宸ヤ綔琛╯heet5銆3銆佷笂杩板姛鑳戒篃鍙互閫氳繃鐐瑰嚮鍙抽敭閫夋嫨鈥滄彃鍏モ濇潵瀹炵幇銆傚湪鏌愪釜宸ヤ綔琛ㄦ爣绛惧sheet3涓婄偣鍑诲彸閿紝閫夋嫨鎻掑叆銆4銆佸湪璺冲嚭鐨勫璇濇閲...
绛旓細1銆佸缓绔嬩竴涓宸ヤ綔琛妯℃澘锛屽皢瑕佸仛鐨勬槸浠ユ湰琛ㄤ负鍩虹锛屽缓绔嬩竴涓笌鍏剁浉鍚岀殑宸ヤ綔琛ㄣ2銆佸彸閿崟鍑绘ā鏉垮伐浣滆〃鐨勮〃鍚嶏紝閫夋嫨鈥滅Щ鍔ㄦ垨澶嶅埗宸ヤ綔琛ㄢ3銆佲滃皢閫夊畾宸ヤ綔琛ㄧЩ鑷宸ヤ綔绨鈥濅笅鐨勪笅鎷夋锛氶夋嫨鍏朵粬鎵撳紑鐨勫伐浣滅翱锛屾垨寤虹珛鏂扮殑宸ヤ綔绨匡紝濡傛灉涓や釜宸ヤ綔琛ㄥ悓鍦ㄥ綋鍓嶅伐浣滅翱锛屽垯涓嶅繀淇敼銆4銆佲滀笅鍒楅夋嫨宸ヤ綔琛ㄤ箣鍓嶁濅笅...
绛旓細1銆佺偣鍑诲伐浣滅翱涓殑涓宸ヤ綔琛紝鍚屾椂鎸変笅閿洏涓婄殑Shift鍜孎11閿紝鍙互閫熷害鍦ㄥ伐浣滅翱涓鎻掑叆涓涓伐浣滆〃銆2銆佺偣鍑绘彃鍏ュ伐浣滆〃鍥炬爣锛屽湪宸ヤ綔绨夸腑涓涓伐浣滆〃鐨勫彸渚т細鏈変竴涓彃鍏ュ伐浣滆〃鐨勫浘鏍囷紝鐐瑰嚮鍥炬爣鍗冲彲閫熷害鎻掑叆涓涓┖鐧藉伐浣滆〃銆3銆佸湪寮濮嬭彍鍗曚笅鐐瑰嚮鎻掑叆瀹炵幇锛屽湪寮濮嬭彍鍗曚笅鐨勫崟鍏冩牸閫夐」鍖哄煙锛岀偣寮鎻掑叆涓嬫媺绠ご銆傚湪...
