最近工作遇到这样一个问题:有200+个仪表参数信息文件,要将所有仪表重新编订位号,并将参数分类汇总到下图所示的“样表”中,如果一个个手动输入,不仅耗时低效还不能保证数据准确率。
任意打开几个参数文件,发现参数表都是固定格式。以TI 563303为例,汇总表中“介质名称”对应参数表中I9单元格“GW”,“相态”对应I10单元格“Liquid”,“参数”中“最大值”、“正常值”、“最小值”分别对应单元格J22 J21 J20的内容“75” “71” “70”,介质“操作压力”“操作温度”“动力粘度”分别对应单元格J25 J21 J30的内容,“管道规格”、“材质”、“设计压力”、“设计温度”、“保温厚度”对应单元格D12 D10 J18 J15 D14中的内容。
既然有规律可循,就可以简化操作,提高效率。那么,是否可以通过仪表位号定位到目标文件,提取目标单元格中的数据呢?
1、导出仪表位号
现有参数表文件名即为仪表位号,所有参数表放于F盘的“仪表参数”文件夹中,导出所有文件名即可。
电脑任务栏“开始”菜单中打开“运行”,输入“cmd”后点击“确定”,跳出黑色对话框,输入指令:dir f:\仪表参数/b>f:\仪表参数.xls,敲击“Enter”,在F盘会出现一个“仪表参数.xls”的excel文件。
因位号要重新给定,所以将生成文件中的仪表位号(即原位号)暂时拷贝到样表“备注”栏AD做参考用。
2、多个excel文件合并为一个excel文件
只有被引用的文件或工作簿在打开状态下,excel公式对其引用才有效。所以,将所有的参数文件合并为同一个excel文件下的多个工作表,对简化操作十分必要。
在excel操作页面,点击“开发工具”栏的“VB编辑器”,或“alt+F11”,打开宏。
在“视图”命令栏选择“代码窗口”,输入以下代码。
——————————分割线———————————
Sub sheets2one()
'定义对话框变量
Dim cc As FileDialog
Set cc = Application.FileDialog(msoFileDialogFilePicker)
Dim newwork As Workbook
Set newwork = Workbooks.Add
With cc
If .Show = -1 Then
Dim vrtSelectedItem As Variant
Dim i As Integer
i = 1
For Each vrtSelectedItem In .SelectedItems
Dim tempwb As Workbook
Set tempwb = Workbooks.Open(vrtSelectedItem)
tempwb.Worksheets(1).Copy Before:=newwork.Worksheets(i)
newwork.Worksheets(i).Name = VBA.Replace(tempwb.Name, ".xls", "")
tempwb.Close SaveChanges:=False
i = i + 1
Next vrtSelectedItem
End If
End With
Set cc = Nothing
End Sub
——————————分割线———————————
输入完毕,运行宏,弹出对话框,选择需要合并的excel文件,点击“打开”。
将自动弹出合并好的excel文件,另存命名为“仪表参数汇总.xls”。为方便操作,将前述样表也复制到这个文件中,置于所有工作表之前。
3、读取目标数据
INDIRECT是一个引用函数,一般有两种用法:
=INDIRECT("A1")——加引号,目标单元格中内容作为文本引用;
=INDIRECT(A1)——不加引号,目标单元格中内容作为地址引用。
在本例中,选择第一种用法,直接引用目标单元格中的文本作为结果。
以样表单元格H10为例,输入=INDIRECT("'"&$AD10&"'!$I$9")
"&$AD10&" - 取单元格AD10的值 TI 563303(文本格式);
'"&$AD10&"'!$I$9 - 为名为TI 563303的工作表中I9单元格;
INDIRECT("'"&$AD10&"'!$I$9") - 即为对I9单元格内容的文本引用;
得到结果“GW”。
以此类推,
单元格I10=INDIRECT("'"&$AD10&"'!$I$10")
单元格K10=INDIRECT("'"&$AD12&"'!$J$22")
等等。
第10行公式编辑完毕后,整体下拉,即得到所有仪表参数的汇总数据。
Leave a Reply