多工作簿里、多表格、各相同类型表格的相同位置如何快速合并计算成一个合并表数据？
若是想要全自动的核算汇总的，则需要找人写代码编程操作，但这样的操作也是有很多的限制的，比如上报表格的结构格式、文件名称、磁盘上保存的位置等都有严格规范要求的。若是用所见即所得的直白操作，则可以设计一个套表，每个子公式的上报的表可以存放在N个工作表、也可以存放在同一个工作表，然后再用函数公式按要求设计好汇总的数据表格，今后每次只要把子公式的表格复制粘贴过来，即可自动显示汇总表格了…………
用vba操作，加在宏按钮上，一键搞定。
关键得要用上你这些表格，光看你的字描述，很难实现。
以下是我平常用的代码，你可以参照试用。
Sub 合并工作表()
Dim MyPath, MyName, AWbName
Dim Wb As Workbook, WbN As String
Dim G As Long
Dim Num As Long
Dim BOX As String
Dim ShRcou As Long, ShCol As Integer
Dim S As Long, D As Long, F As Long, H As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
MyPath = ActiveWorkbook.Path
MyName = Dir(MyPath & "\" & "*.xls")
AWbName = ActiveWorkbook.Name
Num = 0
On Error Resume Next
Sheets(1).Range("A2:P15000").Select
Selection.Clear
Do While MyName <> ""
If MyName <> AWbName Then
Set Wb = Workbooks.Open(MyPath & "\" & MyName)
Num = Num + 1
With Workbooks(1).ActiveSheet
If Num = 1 Then
S = .Range("A65536").End(xlUp).Row + 3
Wb.Sheets(1).UsedRange.Copy .Cells(.Range("A65536").End(xlUp).Row + 1, 1)
F = .Range("A65536").End(xlUp).Row
For G = S To F
ShRcou = Wb.Sheets(1).UsedRange.Rows.Count
.Cells(G, 16) = MyName
Next
Else
D = .Range("A65536").End(xlUp).Row + 1
Wb.Sheets(1).UsedRange.Offset(2, 0).Copy .Cells(.Range("A65536").End(xlUp).Row + 1, 1)
H = .Range("A65536").End(xlUp).Row
For G = D To H
ShRcou = Wb.Sheets(1).UsedRange.Rows.Count
.Cells(G, 16) = MyName
Next
End If
WbN = WbN & Chr(13) & Wb.Name
Wb.Close False
End With
End If
MyName = Dir
Loop
Range("B2:K2").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.UnMerge
Range("A2:J2").Select
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("I:I").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A1").Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
绛旓細浣犳槸瑕佸悎骞跺涓伐浣滅翱涓殑宸ヤ綔琛鏁版嵁锛熻繕鏄涓伐浣滆〃涓暟鎹紵濡傛灉鏄涓伐浣滅翱鐨勮瘽锛屽厛鎶婂伐浣滆〃浣跨敤绉诲姩澶嶅埗鍔熻兘锛屾妸浠栦滑闆嗕腑鍒颁竴涓宸ヤ綔绨块噷锛鐒跺悗鍐嶉夋嫨澶嶅埗鍒颁竴涓伐浣滆〃閲岋紒褰撶劧濡傛灉浣犱細vba鐨勮瘽锛屽彲浠ヤ娇鐢╲ba鏉ュ疄鐜扮Щ鍔ㄥ鍒讹紒
绛旓細濡傛灉鏈夋槑纭殑瑙勫垯锛屽彲浠ラ氳繃缂栧啓 浠 鐮 鐩存帴鎻愬彇鏁版嵁姹囨伙紝涓嶅繀鎵嬪伐鎵撳紑姣忎釜琛ㄣ
绛旓細5銆佽繖鏍峰氨鍙互蹇熷皢褰撳墠宸ヤ綔绨跨殑鎵鏈夊伐浣滆〃鍚堝苟鍒板彟澶栦竴涓伐浣滅翱浜嗐6銆佽繖鏍峰仛鐨勮瘽锛屽綋鍓嶅伐浣滅翱涓嶄細鍙戠敓鍙樺寲锛屽鍒剁Щ鍔ㄧ殑宸ヤ綔绨匡紝浼氬悎骞朵袱涓伐浣滆〃鐨勬墍鏈夊唴瀹广7銆侀渶瑕佹敞鎰忥細濡傛灉涓や釜宸ヤ綔绨匡紝鏈鐩稿悓鍚嶅瓧鐨勫伐浣滆〃鐨勮瘽锛岀郴缁熻嚜鍔ㄧ敤锛2锛夋潵鍛藉悕锛涘鍥撅細鏈緥涓洜涓烘病鏈夐噸鍚嶇殑宸ヤ綔琛紝鎵浠ュ悎骞跺悗鐨勫伐浣滅翱...
绛旓細澶氫釜宸ヤ綔绨夸腑澶寮犲伐浣滆〃鐨勫悎骞 杩涜鎿嶄綔銆傛暟鎹儏鍐碉細銆愬涓伐浣滅翱涓寮犲伐浣滆〃鐨勫悎骞躲戞枃浠跺す涓紝鏈2涓伐浣滅翱锛屽垎鍒槸娓犻亾1鍜屾笭閬2锛岀劧鍚庢瘡涓伐浣滅翱涓湁4寮宸ヤ綔琛紝姣忓紶宸ヤ綔琛ㄦ湁10鏉℃暟鎹傛楠わ細1锛夊彲浠ユ柊寤轰竴涓枃浠跺す锛屽皢銆愬涓伐浣滅翱涓寮犲伐浣滆〃鐨勫悎骞躲戞枃浠跺す鏀捐繘鍘伙紝鐒跺悗鍐嶉噷闈㈠啀鏂板缓涓涓伐浣...
绛旓細1.棣栧厛锛屽厛鏉ョ湅涓涓婭NDIRECT鍑芥暟鐨勭敤娉曘侷NDIRECT鍑芥暟鐨勪綔鐢ㄦ槸锛氬皢琛ㄧず鍗曞厓鏍煎紩鐢ㄧ殑鏂囨湰杞崲涓哄疄闄呭崟鍏冩牸鐨勫紩鐢ㄣ傚叿浣撶ず渚嬭涓嬪浘璇存槑锛2.涓婇潰鐨勪緥瀛愭槸杩斿洖鍚宸ヤ綔琛ㄤ腑鐨勫唴瀹癸紝INDIRECT鍑芥暟瀵逛笉鍚屽伐浣滅翱銆佷笉鍚屽伐浣滆〃鐨勬枃鏈湴鍧涔熸槸鍚屾牱閫傜敤鐨勶紝濡備笅鍥剧ず渚嬶細3.娉ㄦ剰锛氬閮ㄥ垎宸ヤ綔琛ㄥ悕绉板紩鐢ㄦ椂闇瑕佸湪宸ヤ綔琛ㄥ悕绉...
绛旓細4銆佺偣鍑籩xcel琛ㄦ牸涓暟鎹>鍚堝苟琛ㄦ牸鈥>澶氫釜宸ヤ綔琛鍚堝苟鎴愪竴涓伐浣滆〃銆5銆佺偣鍑绘坊鍔犳枃浠讹紝鎶婇渶瑕佸悎骞剁殑excel鏁版嵁琛ㄦ坊鍔犲埌鏂囦欢涓6銆佸畬鎴愪笂涓姝ュ悗锛屽彇娑堝嬀閫夋柊寤虹殑excel琛紝骞跺湪宸︿笅鏂圭殑閫夐」涓偣鍑伙紝閫夋嫨琛ㄦ牸鏍囬鐨勮鏁颁负1锛岀劧鍚庣偣鍑诲紑濮嬪悎骞躲7銆佸畬鎴愬悗锛屽湪鏂扮殑excel琛ㄤ腑锛浼氬嚭鐜颁袱涓宸ヤ綔绨锛屽叾涓竴涓...
绛旓細Excel涓鏋滃綋鍓宸ヤ綔绨夸腑澶氫釜宸ヤ綔琛ㄤ腑鐩稿悓浣嶇疆閮芥湁鏁版嵁锛屽鏋滃杩欎簺鏁版嵁杩涜姹傚拰鐨勮瘽锛屼笉闇瑕佷竴涓竴涓崟鍏冩牸鐨勭浉鍔狅紝鏈夌畝鍗曠殑鍔炴硶銆備粖澶╁氨鏉ヤ粙缁岴xcel涓澶氫釜宸ヤ綔琛ㄤ腑鍚屼竴浣嶇疆鏁版嵁杩涜姹傚拰鐨勬柟娉曘傚叿浣撴楠ゅ涓嬶細1銆佹垜浠鎶婁笅鍒梥heet1鍒皊heet4鐨勬暟鎹眰鍜屾眹鎬诲埌姹囨琛ㄤ腑锛姹囨荤殑鍓嶆彁鏉′欢鏄紝sheet1鍒皊heet...
绛旓細=INDIRECT("Sheet"&ROW(A1)&"!D5")锛屾眰琛屾爣鍏紡锛歊OW(A1)锛屾眰鍒楁爣鍏紡锛欳OLUMN(A1)銆澶氬伐浣滆〃鍚浣嶇疆鍗曞厓鏍兼眰鍜屻3銆丒XCEL涓鏄撹蹇界暐鍔熻兘(18):澶氬伐浣滆〃鍚屼綅缃崟鍏冩牸姹傚拰銆傚浜庝竴涓伐浣滅翱鍐呭涓鐩稿悓浣嶇疆鐨勫崟浣嶆牸姹傚拰锛屾渶甯歌鐨勬柟娉曟槸锛=sheet1!A1+sheet2!A1+sheet3!A1+sheet4!A1+sheet5!A1...
绛旓細7.鐐瑰嚮鈥滈椤碘宸ヤ綔琛ㄤ腑鎻掑叆鐨勬寜閽紝鏍规嵁鎻愮ず锛屾祻瑙堝埌鍘熷鏁版嵁宸ヤ綔绨裤8.涓嬩竴姝ワ紝鐢ㄩ紶鏍囬夋嫨瑕佸悎骞剁殑鏁版嵁鑼冨洿銆傛敞鎰忥細姣忎釜宸ヤ綔琛ㄦ暟鎹彲鑳戒笉涓鏍凤紝姣斿璇存湁鐨勬槸10琛屾暟鎹紝鏈夌殑鏄30琛屾暟鎹傚湪杩欓噷鍙互鐢ㄩ紶鏍囬夋嫨浠绘剰宸ヤ綔琛ㄧ殑涓涓緝澶ц寖鍥达紝姣斿璇碅1:D100锛屼繚璇佹瘮鏈澶氳鏁扮殑宸ヤ綔琛ㄦ暟鎹繕澶氬氨鍙互锛屼竴浼...
绛旓細2銆佸湪涓嬩竴涓崟鍏冩牸锛圕3锛夛紝杈撳叆鍑芥暟鍏紡锛=IF(A3=A2,C2&"/"&B3,B3)鍗筹細濡傛灉A鍒楃殑濮撳悕涓庝笂涓涓崟鍏冩牸鐩稿悓锛杩斿洖缁撴灉涓婁竴涓崟鍏冩牸鐨勫唴瀹瑰姞B鍒楀搴旂殑鏁版嵁锛屽苟涓庝箣浠ユ枩鏉犻殧寮锛屽惁鍒欙紝绛変簬B鍒楀搴旂殑鏁版嵁銆3銆佸悜涓嬪～鍏呭叕寮忓悗锛屾垜浠彲浠ョ湅鍒帮紝鍚勫鍚嶄腑鐨勬渶鍚庝竴涓鍚嶅搴斿彸渚э紙C鍒楋級鐨勬暟鎹紝灏辨槸...
