1. 需求

2. 实现流程

2.1 流程图

流程如上，因测试得到多个数据表格，先将表格数据合并，并以文件名作为每个数据的代号。然后更换坐标轴，通过对文件名数据的处理，得到最终曲线的数据。

2.2 运行方法

First: 打开下载的Excel文件，点击 视图—>宏—>查看宏

Second: 弹出如下界面后，点击 编辑，弹出代码对话框，在红色框内输入对应的数据文件夹


**Third:**在宏的界面，依次点击 “整理所有数据放在总表内” / “切换坐标轴” / “对数据进行计算”，然后点击执行即可

2.3 完整代码

Sub 整理所有数据放在总表内()Application.DisplayAlerts = False' 关闭信息提示弹窗Dim MyFile As StringDim erow As LongDim Filepath As Stringerow = 1Filepath = "E:\课题进度\待完成工作\现阶段工作\隔膜分析\2. 测试数据\2022-10-12-圆柱型电池测试\2. 物理性能\拉伸\2023-10-13-MD-TD对比\" '设置需要遍历的文件夹路径MyFile = Dir(Filepath)Do While Len(MyFile) > 0If MyFile = "Master.xlsm" Then GoTo NextFile '跳过当前工作表Workbooks.Open (Filepath & MyFile)'提取数据LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).RowRange("B2:C" & LastRow).Copy'粘贴到总表Sheet1.Cells(1, 2 * erow - 1) = MyFile '在第一列写入文件名Sheet1.Cells(2, 2 * erow - 1).PasteSpecialWorkbooks(MyFile).CloseSaveChanges = Falseerow = erow + 1
NextFile:MyFile = Dir()LoopEnd SubSub 切换坐标轴()For i = 1 To 30Sheets("Sheet1").SelectRange(Columns(2 * i - 1), Columns(2 * i - 1)).SelectSelection.CopySheets("Sheet2").SelectCells(1, 2 * i).SelectActiveSheet.PasteSheets("Sheet1").SelectRange(Columns(2 * i), Columns(2 * i)).SelectSelection.CopySheets("Sheet2").SelectCells(1, 2 * i - 1).SelectActiveSheet.PasteNextEnd SubSub 对数据进行计算()Dim sh2Data As Variant, sh3Data As VariantDim result(100000, 60) As DoubleDim i As Long, j As Long, k As Long'读取Sheet2数据到数组sh2Data = Sheets("Sheet2").UsedRange'读取Sheet3数据到数组sh3Data = Sheets("Sheet3").UsedRange'根据Sheet3矩阵进行运算For i = 1 To 25For j = 4 To Sheets("Sheet2").Cells(Rows.Count, i * 2).End(xlUp).Rowresult(j, 2 * i - 1) = sh2Data(j, 2 * i - 1) / sh3Data(i, 2)result(j, 2 * i) = sh2Data(j, 2 * i - 1) / sh3Data(i, 3)Next jNext i'将结果写入Sheet4Sheets("Sheet4").Range("A1").Resize(UBound(result, 1), UBound(result, 2)).Value = resultFor i = 1 To 25Sheets("Sheet4").Cells(1, 2 * i) = sh2Data(1, 2 * i)Sheets("Sheet4").Cells(2, 2 * i) = "strain"Sheets("Sheet4").Cells(2, 2 * i + 1) = "stress"Sheets("Sheet4").Cells(3, 2 * i + 1) = "MPa"NextEnd Sub