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

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