可以写VBA代码解决。
代码如下:
Sub test()
'-------------------声明变量-------------------
Dim wb As Workbook, Sh As Worksheet, mRow As Integer, mCol As Integer
Dim mPath As String, Fx As String, i As Integer, mAry
On Error Resume Next '容错
'-------------------工作环境初始化-------------------
t = Timer
If Workbooks.Count > 1 Then MsgBox "关闭其他工作簿后,再运行本程序!": Exit Sub
'-------------------获取源文件路径-------------------
MsgBox "在接下来的对话框中,选择源数据所在的文件夹。" & Chr(10), vbOKOnly, "提示"
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count = 0 Then MsgBox "没有选择任何文件夹!": Exit Sub
mPath = .SelectedItems(1)
End With
'-------------------枚举文件夹中的Excel文件,并进行相应处理-------------------
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Fx = Dir(mPath & "\*.xls*")
Do While Fx <> ""
If Fx <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(mPath & "\" & Fx, , False)
Set Sh = wb.Worksheets("每木调查")
If Err.Number > 0 Then Err.Clear: GoTo NA
Sh.Copy after:=wb.Worksheets(wb.Worksheets.Count)
Set Sh = ActiveSheet
Sh.Name = "新表"
With Sh
mRow = .Cells(Rows.Count, 2).End(3).Row
For i = mRow To 2 Step -1
mCol = .Cells(i, Columns.Count).End(xlToLeft).Column
If mCol = 4 Then
mAry = .Cells(i, 4).Value
.Rows(i + 1).Insert shift:=xlDown
.Cells(i + 1, 3) = mAry
ElseIf mCol >= 4 Then
mAry = .Range(.Cells(i, 4), .Cells(i, mCol))
.Rows(i + 1 & ":" & i + UBound(mAry, 2)).Insert shift:=xlDown
.Cells(i + 1, 3).Resize(UBound(mAry, 2), 1) = Application.Transpose(mAry)
End If
Next i
.Columns("D:AZ").Delete
End With
End If
NA:
wb.Close True
Fx = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
MsgBox "处理结束!共耗时" & Timer - t & "秒。"
End Sub