浅谈运用VBA对EXCEL表的二次开发.docVIP

浅谈运用 VBA 对 EXCEL 表的二次开发 核工业第二研究设计院 康力 Microsoft Office 作为办公软件已被各行业普遍使用。 其中， Microsoft Excel 由于易用性、熟悉的使用界面和与 windows 系列的良好兼容性，使其大量用于数据的收集、计算和统计。 我作为一 名工程经济人员，在工作中经常使用 Excel 软件记录和处理许多数据 。由 于 Excel 电子 表格在 行业 N 内 的广泛 使用 ，使 得 Excel 表中数据量巨大，同时不同制表者表格形式也彼此有异，这些数据、表格的处理将耗费处理者大量的时间与精力，使用某种编程语言对工作表格进行处理就显得必要和迫切。 由于工作表的中间和结果表格的形式多样多变，大量输入数据的不确定性，使用 foxpro 或其它语言编写制定软件的成本过大，灵活性差，使用也不如在 Excel 表内 直接处理方便，所以使用 Excel 电子表格内嵌 的 VBA 是一个不错的选择 。使 用 Excel 软件 内置 的 VB 编程，形成宏命令对大量的表格和数据进行处理，显现出优势和必要性 。 使用 VB对电子表数据进行编程处理，所有的操作过程都在 EXCEL软件内部完成，避免了文件的数据转换，造成的 EXCEL表数据格式丢失，程序兼容性差问题及一定范围内的不可预见的问题。 以某项工程文件设计为例，我和同事在设计过程中产生大量 Excel 表格 ，在汇总阶段需要把这些表格中某一列的 数值按 某种规则汇总求和，并根据结果生成最终数据表。在此过程中，相同数据项的规范与合并和根据中间数据计算出最终数据是主要的环节。 在编制“合并”的宏命令程序之前，我们分析一下数据结构： 每个需处理的表都有许多数据列，其中一些是关键列，每列的表头相当数据库中的键值，各列的每个单元格组成一行，相当于一个记录，我们需要把键值相同的行合并对某个键值的数值求和。在对这些键值排序和分析后，通常会得到一个树形结构，比如说一个工程，它由许多系统组成，每个系统包含很多项目，每个项目下需要不同材料，每种材料下分为不同规格型号，不同系统的各种材料都有数量，在每个系统中有相当多名称、材料、规格尺寸相同，但数量不同的行。我们可以根据同类项合并的要求选择键值，然后通过键值进行搜索合并，整个搜索过程形成树状结构的穷尽搜索，如下图所示 EXCEL 1 2 1 2 1 1 2 在分析好 数据结构，确定主键值后使用 Excel 自带的排序功能， 按关键字的优先顺序能对主键值的所在数据列进行排序，减少归类合并的计算量。如在排序中，我选择“系统”、“项目名称”二个键值对工作行用进行排序。 关键一步是合并同类项。在程序中，首先考虑有两个指针：一个指向每个需要合并表的第一行，称为原指针；另一个指针指向需比较的合并行，称为比较指针。以上为例，如果两个指针所指向的项目行在系统号、项目名称、材料、公称直径等需要比较的键值相同，则原指针所指项目行的数量列的数值等于原值加上比较值，同时删除比较行，比较指针指向下一行。当比较指针在同一系统运行一遍后，确认系统内无相同项，原指针移向下一行，比较指针指向原指针下一行，继续在同一系统内顺序移动进行比较。原指针比较完一个系统，进入下一个系统，继续进行，直至原指针指向结束符。 在项目行合并时计算机的算法选择时，考虑目前计算机速度提高很快，故数据结构采用普通气泡算法进行计算。主要代码如下： i = 6 ‘开始行 c = 0 ‘合并行个数 e = 0 ‘比较指针 g = True Do i = i + 1 e = i Do e = e + 1 g = (Cells(i, 4) = Cells(e, 4)) And (Cells(i, 7)) And (Cells(i, 10) = Cells(e, 10)) And (Cells(i, 14) = Cells(e, 14)) If g Then Cells(i, 16) = Cells(i, 16) + Cells(e, 16) Cells(e, 16).Select  7) = Cells(e, e = e  –  1; c = c + 1 End If Loop While Cells(i, 4) = Cells(e, 4) Loop While Cells(i, 4) 123 其中 Cell(I,X) 中的 4、 7、 10 、 14 为需要比较的键值所在的列号，16 为数量这个键值所在列号。“123”为在表中设置的结束符。在计算过程中，应先把工作表单元格中的所做的公式数值化。 将以上代码调试成功，即可完成对工作表的归类合并操作。在实际使用中即可用作对手工操作生成的文件进行校验，也可用生成新文件。用 计算机进行分类汇总速度快，而且差错率小。例 如