excel单元格一个条件匹配多个结果VBA

摘要：为了批量提取管道力学计算软件PipeStress输出的结果文件中的数据，需要使用计算机从结果文件中提取支架的反力及反力方向矢量等数据。基于VBA简单易学易用的特点，提出使用Excel电子表格读取结果文件，并使用VBA语言编程进行数据处理的方法。经过实际应用表明，该工具能满足实际需求，大大提高了工作效率，节省了大量的人力和时间，使用、维护简便，可靠性高。

关键词：批量；数据提取；Excel；VBA

某電站采用管道系统有限元分析软件PipeStress对电站的管道系统进行应力分析，软件输出的结果文件（*.ppo）包含了各工况组合下的支架（或阻尼器等）的支撑载荷、接管载荷、支架（和自由端、穿墙点等）处的节点位移。在实际的管道力学分析项目中，会根据管道系统结构的复杂程度将管道分成若干个计算单元。对于复杂的计算单元有多达40-50个支架、10个以上位移输出节点。配管专业需要从结果文件中提取各个支架的最大支撑载荷以及穿墙点等处的最大位移来分析管道布置的合理性，并根据分析的结果调整管道结构。对于一个大型的电站项目，计算的管道单元往往多达几百甚至上千个，涉及的支架达1万多个，从这么多的计算单元中找出各个支架的反力和位移需要耗费大量人力和时间，而且由于人为因素的影响还会存在一定的错误率，后期再人工校对数据又要耗费大量的时间，极大地影响工程进度。

VBA（ Visual Basic for Application） 是微软在 Office中内置的宏编程语言。它语法形式上与VB相同，易学易用，功能却非常强大，可以操控各种 Office对象，为用户文档增添强大的功能。例如 Excel VBA可控制各单元格的内容、格式，VBA 还具有各种图形用户界面控件，可开发出各种 Windows 图形界面的应用程序，可以使用程序批量处理大量文档数据，减轻手工文档处理的烦琐劳动。

限于篇幅，本文主要介绍支撑载荷提取工具的设计。为了使工具能够实现“一键操作”，支撑载荷数据提取工具主要实现以下功能：1）批量读取多个计算单元的结果文件；2）提取每个计算单元中的各个支架在指定组合工况局部坐标系下的最大反力以及对应的反力矢量方向；3）列出支架所在的计算单元号、版本号以及对应的节点号、支架功能。工具实现的功能流程图如下图1.

2 工具实现的关键技术

工具设计最关键的是支架名的统一，由于在使用PipeStress软件时支架的命名因人而异，如果不统一命名规则，就无法实现通过识别支架名读取支架对应的反力和力的方向矢量，因此在设计工具前先确定好支架及其他必要输出点对应的节点命名方式，如标准支架名为3BFX10ST0071-GL，“-”前为支架名，其后为支架功能。确定好命名规则后，就可以进行接下来的程序开发了。本工具的开发主要包括以下3点关键技术。

2.1 批量读取文件名

一个计算单元对应一个结果文件（*.ppo，下用PPO代替），如果有多个结果文件，就需要依次读取各个文件，作者通过识别文件名来实现依次读取，在此之前需要将所有要读取的PPO文件放入同一个路径下的某个文件夹中，然后程序将该路径下的所有的PPO文件名依次写入工作表，另外结果文件的命名方式采用的是计算单元号+版本号，这便为后续识别支架所在的单元号及版本号提供了思路，具体代码如下：

2.2 读取原始结果文件

识别完需要读取的PPO文件名后，就从第一个PPO文件开始依次读取PPO中的原始数据，由于是在Excel中完成读取过程，因此首先要将原始数据写入Excel中，具体实现方法如下：

2.3 提取支架相关数据

结果文件中支架及其反力的分布存在一定的规律，每页支架反力抬头有提示文字Mark，支架所在行带有特征记号Sign，在支架所在行提供了支架名、支架功能、节点号以及力的方向矢量，在接下来的若干行中提供了指定组合工况下支架的反力，根据这个规律，可以先根据Mark通过IF函数确定开始查找位置并记录行数row1，然后从row1+1行开始到其后的若干行通过FIND函数查找Sign直至找到支架所在行，从该行中提取支架名称、支架功能和力的方向矢量，用同样的方法提取支架反力，代码如下：

利用VBA的图形控件设置一个启动按钮，如下图2所示，图中1即为控件，“读取力学报告”控件，单击该按钮即可实现一键解决批量读取结果文件、提取支架信息、自动输出读取结果等功能。读取的结果文件名依次写入区域2所在列，单元格3的值代表所有需要读取的结果文件数。

基于Excel VBA的数据读取工具已经开发完成并获得了实际应用，取得了非常好的效果，大大提高了工作效率，节省了人力和时间。利用Excel应用程序中的VBA编程功能非常好地解决了批量数据提取的问题，为此类问题的解决提供了思路和方法，也可以为类似大数据处理提供参考。通过实际使用效果可以发现，利用数组可以大大提高读写速度，对于大批量的数据处理具有非常明显的优势。

[5] 王志军.运用Excel公式进行数据处理技巧八则[J].电脑知识与技术， 2015（10）：44-46.

如果把VBA比作一门刀法，那么经过前面内容的操练，大家已经掌握了很多实用的招式。

如果我们在刀法招式的基础之上，再掌握更多的“磨刀”心法，那么我们的刀用起来才会又好又快。

所以今天主要和大家分享——VBA“磨刀”心法之程序调试，这也是《Excel VBA：办公自动化》系列教程的第7节。

所谓的程序调试就是：在某个计算机程序正式投入使用之前，利用一些工具或方法找出程序的漏洞或错误，从而保证该程序能够正常运行不出错。

这样说，好像仍然不是很明白。我们举个例子，帮助大家更好消化。

案例：根据左侧「C列」富豪榜的身价数据，利用右侧「G列」的判断标准，将判断后的「富豪尊称」填入「D列」

在正式分享上述案例之前，先插播一条关于美化的信息。

有朋友私信问我：猴子，我的那个「宏按钮」实在是太丑了，我看你设置的就挺好看，应该如何设置呢？

我觉得这位同学很有眼光，既然你已经发现了我的审美，那么也不隐藏了，都分享给你们了。

通常情况下，我们都是按照「开发工具」-「插入」-「表单控件」-「按钮（窗体控件）」这样的路径来绘制我们的「宏按钮」

这样绘制的「宏按钮」，简直是巨丑无比，哪能入我猴子的法眼？

一对比上图，很明显左侧的宏按钮颜值更高。

就像同样多金的2个男人，要是我，肯定选择更帅的那个，不是吗？

那么，左侧颜值更高的宏按钮如何设置呢？

经过我多年不眠不休的潜心研究，终于，研制出提升宏按钮颜值的方案：

首先，我们点选Excel选项卡中的「插入」-「形状」-「矩形」-「圆角矩形」

然后，直接拖动绘制就好，绘制完成后，Excel会自动多一个「格式」的选项卡。这个选项卡有很多可以自定义这个圆角矩形的操作，比如：颜色、阴影、大小等，大家根据自己的喜好修改就行。

大家可能还有个疑问，宏按钮确实好看了，可是怎么让它关联宏呢？

纳尼？这能难倒我万能的猴子，又经过数十年的研究，天台式微笑又一次上扬在我的嘴角：

其实，和插入VBA那个巨丑无比的「按钮（窗体控件）」关联宏的操作一样。

我们「鼠标右击」-「指定宏」，然后从「指定宏」列表选择自己需要关联的宏即可。

「宏美化」探讨完毕，我们继续回到上面的案例。

2.VBA程序如何调试？

我们根据上述案例的特点，并结合之前和的2个知识点，首先将这个案例的代码写完（大家肯定都会写，我就直接展示了）。

这什么鬼，VBA你这又来侮辱我的智商吗？

不过，俗话说：调试用的好，快乐没烦恼。

因此，我们要用VBA的调试功能，解除VBA侮辱我们智商的烦恼。

（2）鼠标点选「断点」位置

比如，我们在「For i = 3 To 12」处设置「断点」，那么我只需要将鼠标挪至每行代码前的「灰色框体」上，然后轻轻点击「鼠标左键」，该位置会出现一个，并且该句代码会被批色，表明「断点」设置成功。

所谓「断点」就是当VBA遇到手动设置的「断点」，运行中的程序将会自动停止，并「批黄断点处代码」，等待下一步执行指令。

套用到这个案例就是，我点击程序自动运行后，当程序执行到「For i = 3 To 12」这句话时，就会自动停止，且批黄该句代码，等待我的下一条指令。

（3）「单步调试」查找错误出处

此时为了找到程序程序出错的位置和原因，我们需要使用「单步调试」，也就是一句句执行，查看每行代码执行情况，更加精准的排查错误。

在VBA编辑器中，依次点选「调试」-「逐语句」，当然最高效的方法是使用快捷键「F8」

（4）鼠标悬停变量处，自动显示当前变量的值

在代码过程中，我们将鼠标悬停在变量上，VBA编辑器将自动提示当前变量的取值，比如：图中的鼠标悬停在「变量i」上，下方会自动显示当前「i = 3」

有些小伙伴，可能说，猴子这个悬停显示变量值的功能确实很贴心，你讲解的这个案例，只有一个变量，悬停很easy。如果我的代码中有很多个变量，我要查看多个变量的值，那岂不是...想想都头皮发麻呢。

一听就知道，心中有这样疑问的同学，绝对是“懒中王者”，妥妥的未来科技界领头羊。

所以，接下来，猴子就再给你介绍个“懒人”VBA调试法宝。

（4）「添加监控」让VBA调试更贴心

我们在VBA选项卡依次点选「调试」-「添加监控」

在弹出的「添加监控」的表达式填写需要监视的内容即可，然后点击右侧「确定」按钮，比如：本案例中需要监控「变量i」的值变化，在「表达式」文本框填入「i」，然后点击「确定」

然后，我们就可以看到在VBA编辑前底部会生成一个名为「监视窗口」的窗体，上面显示了监控的相关内容。比如监控的是什么，当前监控内容的值；监控内容的类型，相关内容大家在实操后，自行探索查看即可

（5）综合利用上述调试功能，快速查找代码出错原因

当我们将上述代码一行行执行，在「i = 9」发现，根据分类方法「C9 = 934」属于二级分类，「单元格D9」显示的应该是「富豪」，而不是现在错误的「超级富豪」

然后，我们就聚焦在这行代码上，检查问题。果然是将单元格的判断值条件写错了，才导致了这次的翻车事件~

然后，修改代码后，发现代码执行结果正常。

以上就是，我们利用VBA代码调试的功能，实现了定位错误代码、找出原因错误原因、修改错误代码，最后成功执行获得正确结果的全部流程。

通过这个案例，介绍了VBA三大调试法宝。

在需要停止的位置手动设置「断点」，程序运行此处批色显示且暂停执行，等待下一步指令。

单步调试实现程序逐句执行，快速定位错误代码位置，便于及时排查错误。

实时监控需要关注的变量或内容，精准定位错误原因。

同时，还与大家分享了，通过「插入形状」美化宏按钮颜值的小技巧，让你的代码实用的同时，也增加了爆表的颜值。

毕竟，多金又帅的小哥哥谁不喜欢呢？