Ⅰ 鎬庢牱鎵嶈兘灝嗗氫釜Excel琛ㄦ牸鐨勫唴瀹瑰悎騫跺埌涓涓琛ㄦ牸閲岄潰錛
鏈夊氱嶆柟娉曞彲浠ュ皢澶氫釜Excel琛ㄦ牸鐨勫唴瀹瑰悎騫跺埌涓涓琛ㄦ牸閲岄潰錛屼互涓嬫槸鍏朵腑涓ょ嶅父瑙佺殑鏂規硶錛
鏂規硶涓錛氫嬌鐢‥xcel鑷甯︾殑鍚堝苟宸ュ叿
1. 鎵撳紑涓涓鏂扮殑Excel鏂囨。錛岀偣鍑燴滄暟鎹鈥濋夐」鍗★紝鍦ㄢ滄潵鑷鍏朵粬鏉ユ簮鈥濅竴鏍忎腑閫夋嫨鈥滀粠鏂囨湰/CSV鈥濋夐」錛
2. 閫夋嫨瑕佸悎騫剁殑絎涓涓狤xcel琛ㄦ牸錛岀偣鍑燴滄墦寮鈥濓紱
3. 鍦ㄢ滄枃鏈瀵煎叆鍚戝尖濅腑錛岄夋嫨鈥滃垎闅旂︹濋夐」錛屽苟鍕鵑夆滃埗琛ㄧ︹濆拰鈥滈楀彿鈥濓紱
4. 鐐瑰嚮鈥滀笅涓姝モ濓紝鍦ㄢ滃垪鏁版嵁鏍煎紡鈥濅腑閫夋嫨鈥滄枃鏈鈥濓紱
5. 鐐瑰嚮鈥滃畬鎴愨濓紝灝嗘暟鎹瀵煎叆鍒版柊鐨凟xcel鏂囨。涓錛
6. 閲嶅嶄互涓婃ラわ紝灝嗗叾浠朎xcel琛ㄦ牸鐨勬暟鎹鍒嗗埆瀵煎叆鍒版柊鐨凟xcel鏂囨。涓錛
7. 鏈鍚庯紝灝嗘墍鏈夋暟鎹澶嶅埗鍒板悓涓涓宸ヤ綔琛ㄤ腑鍗沖彲銆
鏂規硶浜岋細浣跨敤VBA瀹
1. 鎵撳紑涓涓鏂扮殑Excel鏂囨。錛
2. 鐐瑰嚮鈥滃紑鍙戝伐鍏封濋夐」鍗★紝閫夋嫨鈥淰isual Basic鈥濓紱
3. 鍦╒isual Basic緙栬緫鍣ㄤ腑錛岀偣鍑燴滄彃鍏モ濋夐」鍗★紝閫夋嫨鈥滄ā鍧椻濓紱
4. 灝嗕互涓嬩唬鐮佸嶅埗鍒版ā鍧椾腑錛
Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range
Application.ScreenUpdating = False
Set SummarySheet = ThisWorkbook.Worksheets.Add
FolderPath = InputBox("璇瘋緭鍏ヨ佸悎騫剁殑鏂囦歡澶硅礬寰勶細")
If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath + "\"
FileName = Dir(FolderPath & "*.xls*")
Do While FileName <> ""
Set WorkBk = Workbooks.Open(FolderPath & FileName)
Set SourceRange = WorkBk.Worksheets(1).UsedRange
If NRow = 0 Then
Set DestRange = SummarySheet.Range("A1")
SourceRange.Copy DestRange
Else
Set DestRange = SummarySheet.Range("A" & NRow + 1)
SourceRange.Copy DestRange
End If
NRow = SummarySheet.UsedRange.Rows.Count
WorkBk.Close False
FileName = Dir()
Loop
SummarySheet.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
5. 鐐瑰嚮鈥滆繍琛屸濇寜閽錛岃緭鍏ヨ佸悎騫剁殑Excel琛ㄦ牸鎵鍦ㄧ殑鏂囦歡澶硅礬寰勶紱
6. 紼嬪簭灝嗚嚜鍔ㄩ亶鍘嗚ユ枃浠跺す涓嬬殑鎵鏈塃xcel琛ㄦ牸錛屽皢瀹冧滑鐨勬暟鎹鍚堝苟鍒版柊鐨凟xcel鏂囨。涓錛
7. 鏈鍚庯紝淇濆瓨鏂扮殑Excel鏂囨。鍗沖彲銆
浠ヤ笂涓ょ嶆柟娉曢兘鑳藉熷皢澶氫釜Excel琛ㄦ牸鐨勫唴瀹瑰悎騫跺埌涓涓琛ㄦ牸閲岄潰錛屼嬌鐢╒BA瀹忕殑鏂規硶鐩稿規潵璇存洿鍔犺嚜鍔ㄥ寲鍜屽揩鎹鳳紝浣嗛渶瑕佷竴瀹氱殑緙栫▼鐭ヨ瘑銆
Ⅱ 濡備綍鎶婂氬紶Excel琛ㄦ牸鐨勫唴瀹瑰悎騫舵垚涓涓狤xcel琛ㄦ牸錛
瑕佸皢澶氫釜Excel琛ㄦ牸涓鐨勬暟鎹奼囬泦鍒頒竴涓琛ㄦ牸涓錛屼綘鍙浠ヤ嬌鐢ㄤ互涓嬫柟娉曪細
1. 澶嶅埗綺樿創錛氳繖鏄鏈綆鍗曠殑鏂規硶銆傛墦寮涓涓鐩鏍嘐xcel宸ヤ綔綈匡紝鐒跺悗閫愪釜鎵撳紑鍏朵粬瑕佹眹闆嗘暟鎹鐨凟xcel鏂囦歡銆傚湪婧愬伐浣滅翱涓閫夋嫨鏁版嵁鑼冨洿錛堜緥濡傦紝閫変腑鏁翠釜宸ヤ綔琛ㄦ垨鐗瑰畾鏁版嵁鍖哄煙錛夛紝鐒跺悗澶嶅埗錛圕trl + C錛夛紝鍒囨崲鍒扮洰鏍囧伐浣滅翱錛岄夋嫨瑕佺矘璐寸殑浣嶇疆錛岀劧鍚庣矘璐達紙Ctrl + V錛夈傞噸澶嶈繖涓姝ラわ紝灝嗘墍鏈夋簮鏁版嵁閫愪釜綺樿創鍒扮洰鏍囧伐浣滅翱涓銆
2. 鏁版嵁閫忚嗚〃錛氫嬌鐢ㄦ暟鎹閫忚嗚〃鍙浠ヨ交鏉懼湴奼囨誨氫釜宸ヤ綔綈誇腑鐨勬暟鎹銆傚壋寤轟竴涓鏂扮殑Excel宸ヤ綔綈匡紝鐒跺悗閫夋嫨鎻掑叆鏁版嵁閫忚嗚〃錛屽皢鍚勪釜宸ヤ綔綈跨殑鏁版嵁鑼冨洿浣滀負鏁版嵁婧愩傞殢鍚庯紝鍙浠ヤ嬌鐢ㄦ暟鎹閫忚嗚〃鍔熻兘鏉ユ眹鎬誨拰鍒嗘瀽鎵鏈夋暟鎹銆
3. Power Query錛圙et & Transform鏁版嵁錛夛細鍦‥xcel 2010鍙婃洿楂樼増鏈涓錛孭ower Query錛圙et & Transform鏁版嵁錛夋槸涓涓寮哄ぇ鐨勫伐鍏鳳紝鍙鐢ㄤ簬瀵煎叆銆佽漿鎹㈠拰鍚堝苟澶氫釜Excel琛ㄦ牸涓鐨勬暟鎹銆備綘鍙浠ュ湪Excel涓鎵懼埌瀹冿紝瀵艱埅鍒扳滄暟鎹鈥濋夐」鍗★紝鐒跺悗閫夋嫨鈥滆幏鍙栨暟鎹鈥濇垨鈥滀粠琛ㄦ牸涓鍚堝苟鏌ヨ⑩濄
4. 浣跨敤VBA瀹忥細濡傛灉闇瑕佽嚜鍔ㄥ寲榪欎釜榪囩▼錛屽彲浠ヤ嬌鐢╒BA瀹忋傜紪鍐欎竴涓猇BA瀹忥紝瀹冧細鎵撳紑姣忎釜婧怑xcel鏂囦歡錛屽皢鍏舵暟鎹澶嶅埗鍒扮洰鏍囧伐浣滅翱涓鐨勫悎閫備綅緗銆傝繖涓瀹忓彲浠ラ氳繃涓嬈℃墽琛屾潵澶勭悊澶氫釜宸ヤ綔綈誇腑鐨勬暟鎹銆
榪欎簺鏂規硶鍙浠ユ牴鎹浣犵殑鍏蜂綋闇奼傚拰鏁版嵁鐨勫嶆潅紼嬪害鏉ラ夋嫨銆傚逛簬綆鍗曠殑浠誨姟錛屽嶅埗綺樿創鍙鑳借凍澶燂紝浣嗗逛簬澶嶆潅鐨勬暟鎹澶勭悊鍜屽氫釜宸ヤ綔綈跨殑澶ч噺鏁版嵁錛屼嬌鐢ㄦ暟鎹閫忚嗚〃銆丳ower Query鎴朧BA瀹忓彲鑳芥洿涓烘湁鏁堛