Ⅰ 镐庢牱镓嶈兘灏嗗氢釜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瀹忓彲鑳芥洿涓烘湁鏁堛