归档时间:五月, 2012

用Excel中的宏减少重复劳动

五 21 2012 由 创建在标签 office

客户发过来的测试报告都是用Excel表格制成的,但是他们的格式总是不停地变,而且不能方便我们监控问题的改动。最好的办法就是自己制定一个表格,按照自己的想法把它组合起来,将格式设成我们想要的格式,然后再重客户的Excel表中将数据考到表格中来。返回拷贝是不是太麻烦了?还是用Excel为我们提供的工具——宏。

关于宏及VBA,我以前写过一篇简单的描述。现在就重点说说如何实现吧。我使用的是Office2010,2007应该和10的界面差不多,03的就自己去找一下,但是名字都是通用的,百度或者Google一下应该就知道了。

格式控制

主要是根据我们现在对问题的控制,按照固定的格式显示以示区分,如添加单元格背景色或者改变字体。主要通过数据有效性和条件格式来实现这样的格式控制。

条件格式

根据这个描述就应该猜出是满足一定条件后显示出一定的格式,我们要区分问题的不同状态,就使用它了。这个地方条件可以使用Excel的函数。

比如我们就只使用两个状态——已处理与未处理,已处理的将文字格式设置成倾斜+删除线+背景灰色;未处理的问题用普通字体+红色背景。

开始->条件格式->管理规则,然后新建规则,上面可以选择规则的类型,下面选择符合规则的格式,中间填写你的规则。

但是规则如何判断呢?我们的输入可以任由用户输入,而程序又不能理解人类的语言,如何保证用户的输入都是满足我们的条件呢?就看下面的数据有效性。

数据有效性

数据有效性就是用来控制单元格的格式,比如某个单元格我只让他输入某些固定的字段,可以用一个下拉框供选择;总之就是让单元格的数据与你设置的保持一致,可以设置不一致就不让输入,从而方便条件格式用字段来当作条件判断。

数据->数据有效性,设置页签中有有效性条件的选择,整数、小数、序列、日期、长度和自定义,基本满足我们的日常需求,实在不行就自己定义一个(我没有尝试过),重点说一下序列,序列就是提前定义好单元格中输入的数据,在来源中设置有效序列,用英文的逗号“,”作为分隔符,有选项可以设置是否提供下拉,按照需求设置。

设置好了数据有效性后,只有输入符合条件才能够输入,这样就可以解决条件格式中的条件了,后面可帮助前面的人解决条件不一致的问题,将问题扼杀在萌芽之中。

多表单问题

有时我们拷贝数据时并不是第一个表单(sheet),有时甚至可能是多个表单,需要给提供一个选择的空间。每次手动输入用户会很烦躁,而且表单的位置之间看是看不出来的,也就是说直接用index方式索引表单其实是不准确的,需要根据表单的表名去索引,而表名有时会很长或复杂,手动输入的方法都不是很友好。

最友好的办法还是通过UI显示出来,获取到要导入的Excel表中所有的表单,将他们的表名提供给用户选择,然后按照用户的意愿来操作。显示表名时也会有些问题,由于不知道表单的个数,需要动态显示的表名,需要计算好每一个表名的索引,由于没有设置好,这个地方在VBA实现的时候出了很大的Bug,有表单与选择不一致、翻页错误等一系列的问题,这个地方还是看细心,虽然调试也能调出来,但是会耗费一些时间的。

下面是关于多表单的一些代码:

Public Sub load_UserForm()

    lcounttmp = oIBLBook.Worksheets.Count

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox1.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = 1

        setVerNoForm.CheckBox1.Value = False
    Else
        'setVerNoForm.CheckBox1.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox2.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox2.Visible = True
        setVerNoForm.CheckBox2.Value = False
    Else
        setVerNoForm.CheckBox2.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox3.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox3.Visible = True
        setVerNoForm.CheckBox3.Value = False
    Else
        setVerNoForm.CheckBox3.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox4.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox4.Visible = True
        setVerNoForm.CheckBox4.Value = False
    Else
        setVerNoForm.CheckBox4.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox5.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox5.Visible = True
        setVerNoForm.CheckBox5.Value = False
    Else
        setVerNoForm.CheckBox5.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox6.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox6.Visible = True
        setVerNoForm.CheckBox6.Value = False
    Else
        setVerNoForm.CheckBox6.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox7.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox7.Visible = True
        setVerNoForm.CheckBox7.Value = False
    Else
        setVerNoForm.CheckBox7.Visible = False
    End If

    If oIBLDisIndex <= lcounttmp Then
        setVerNoForm.CheckBox8.Caption = oIBLBook.Worksheets(oIBLDisIndex).Name
        oIBLDisIndex = oIBLDisIndex + 1
        lFormDisNum = lFormDisNum + 1
        setVerNoForm.CheckBox8.Visible = True
        setVerNoForm.CheckBox8.Value = False
    Else
        setVerNoForm.CheckBox8.Visible = False
    End If

End Sub

'UI上上一页和下一页按钮事件处理函数
Private Sub NextButton_Click()
    If oIBLDisIndex = oIBLBook.Worksheets.Count + 1 Then
        Exit Sub
    Else
        Call load_UserForm
    End If
End Sub

Private Sub PrevButton_Click()

    'Get the sheet count
    If oIBLDisIndex = 9 Then
        Exit Sub
    Else
        oIBLDisIndex = oIBLDisIndex - 8 - lFormDisNum
    End If

    Call load_UserForm
End Sub

 

无规则合并单元格的区分

通过单元格的MergeArea.Rows.Count属性来判断一个单元格是否被合并,因为单元格的合并是我们不能控制的,而且这种情况可以用两重循环来对表格中每一行数据及部分合并的情况。

'lFromIndex  开始的行索引
'lfromend    结束的行索引号
     For ltmpindex = lFromIndex To lfromend Step 1

        '填充第一行序列号
        If 2 = lToIndex Then
            oToSheet.Cells(2, 1).Value = 1
        Else
            oToSheet.Cells(lToIndex, 1).Value = oToSheet.Cells(lToIndex - 1, 1).Value + 1
        End If

        '判断是否有单元格被合并
        lmergedcnt = oFromSheet.Cells(ltmpindex, 1).MergeArea.Rows.Count
        If lmergedcnt > 1 Then
            '有单元格合并,需要单独处理合并的每个单元格
            oToSheet.Cells(lToIndex, 2).Value = oFromSheet.Cells(ltmpindex, 2).Value
            oToSheet.Cells(lToIndex, 4).Value = "Test"
            oToSheet.Cells(lToIndex, 5).Value = oFromSheet.Cells(ltmpindex, 4).Value

            '处理合并单元格中的详细描述
            Dim lMergeTmp As Long
            Dim sDesInfo As String
            sDesInfo = ""
            For lMergeTmp = 0 To lmergedcnt - 1 Step 1
                lDesIndex = oFromSheet.Cells(ltmpindex + lMergeTmp, 3).MergeArea.Rows.Count
                sDesInfo = sDesInfo & oFromSheet.Cells(ltmpindex + lMergeTmp, 3).Value & Chr(10)

                lMergeTmp = lMergeTmp + lDesIndex - 1
            Next lMergeTmp
            ltmpindex = ltmpindex + lmergedcnt - 1
            'MsgBox sDesInfo
            oToSheet.Cells(lToIndex, 3).Value = sDesInfo
        Else
            '直接依次填充相应的数据即可~~
            oToSheet.Cells(lToIndex, 2).Value = oFromSheet.Cells(ltmpindex, 2).Value
            oToSheet.Cells(lToIndex, 3).Value = oFromSheet.Cells(ltmpindex, 3).Value
            oToSheet.Cells(lToIndex, 4).Value = "Test"
            oToSheet.Cells(lToIndex, 5).Value = oFromSheet.Cells(ltmpindex, 4).Value
        End If
        oToSheet.Cells(lToIndex, 10).Value = oFromSheet.Cells(ltmpindex, 7).Value

        '移动到下一行
        lToIndex = lToIndex + 1
    Next ltmpindex

 结束语

Excel中的宏可以让我们减少很多重复的工作量,只要能够发现其中的需求,就可以用宏来解决问题。而且Excel还可以和Word结合起来,可以批量导入Word中的数据,但是Word也需要定制,可以使用Word的表单工具,我有时间再写一篇关于Word和Excel联合工作。

无评论