【Excel VBA 编程】批量处理表格中文本格式数据为数值型数据，并根据环比数据自动添加批注

引言

问题的分析与设计

文本格式数据转化为数值型数据

环比数据计算及显示

自动添加及删除批注

代码入口设计

代码实现

单元格格式设置、环比计算及增加批注代码实现

批量删除批注

运行结果

回顾总结

引言

在 Excel 中，经常会看到文本类型的数据，该数据看起来像是数字（如下表格B和C列数据），Excel 默认会将这些文本数字显示为常规格式，并在单元格的左上角显示一个绿色的三角形状的图标，用以提示用户这是一个文本格式的数字。这个图标通常出现在“公式错误检查”功能中，来提醒用户可能发生的潜在问题。

很多时候我们并不需要这些绿色的三角形状的图标，而且在使用比较复杂Excel公式时也容易出差错。想要手工去除的话，只需要用鼠标选择B列或C列的某个单元格，就会出现一个感叹号，提醒我们“此单元格中的数字为文本格式，或者其前面有撇号。”，然后点击这个图标，选择“转换为数字”，该单元格的数字即可正常显示

如果按照单元格一个个去除，势必带来很大的工作量，所以本期的第一个需求就是批量去掉这些绿色三角图标，另外两个需求就是自动计算环比增长及根据环比数据情况增加批注

工作中你是不是也遇到过类似的问题呢？一起来看看是如何实现的吧

问题的分析与设计

文本格式数据转化为数值型数据

要解决或去掉这个绿色的三角图标，最直接和最常用的方法通常是改变单元格的格式为数值或常规，这样既解决了数据看起来像是数字的问题，也去除了绿色三角图标

如果我们想要设置单元格的格式为数值，可以使用NumberFormat属性。但需要注意这种方法主要用于设置显示格式，而非改变单元格的实际值类型，所以还需要单独将数据类型做一个转换，表格中B列和C列数据都是整数，因此可以使用int函数进行转化

这类函数还有Val 函数、CDbl 函数、CInt 函数或者 CLng 函数等，都可以帮助我们将字符串转换为数值类型，根据需要选择使用即可

环比数据计算及显示

根据环比计算公式获取：（本期-上期）/上期

需要注意的是，直接套用公式求出的数据精度很高，一般我们只需要保留2~4位小时即可满足需求了，因此需要用到Round函数通过四舍五入设置小数点位数

计算出来的数据如果想按照成百分比格式显示，还需要再次使用NumberFormat属性，设置Cells对象格式为百分比“0.00%”，如，将D2单元格设置成百分比格式显示，并且保留两位小数，代码如下：

Worksheets(“Sheet1”).Cells(2, 4).NumberFormat = “0.00%”

自动添加及删除批注

既可以是Range对象也可以是Cells对象，使用它们的.AddComment和.Comment.Delete方法即可实现添加或是删除批注的操作，如下两种方式均是向D2单元格添加一条批注信息：

Worksheets(“Sheet1”).Cells(2, 4).AddComment “批注内容”

Worksheets(“Sheet1”).Range(“D2”).AddComment “批注内容”

代码入口设计

考虑使用方便，还是选取在Sheet页面插入命令按钮(Active x控件)

第一步：Excel文件首先要另存为.xlsm格式文件，第一次打开.xlsm文件会有安全警告，选择启用即可

第二步：顺次选择工具栏 –开发工具–插入–命令按钮（Active X控件），在表单中的适当位置拖动鼠标即可插入。如果该步骤有问题，可以查看我前期写的公众号文章，里面有详细的步骤：如何快速开始编程还不用搭建环境，那一定就是它了……

第三步：双击添加的命令按钮，快速打开VBA代码并且默认会创建类似如下的两行代码，然后我们只要在中间添加代码就可以通过命令按钮调用了

Private Sub CommandButton1_Click()

End Sub

Sheet页面插入命令按钮后显示

代码实现

单元格格式设置、环比计算及增加批注代码实现

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim iRow As Integer
    Dim iCol As Integer
    Dim i As Integer
    
    iRow = 2
    iCol = 2
    i = 1
    Set ws = Worksheets("Sheet1")
    
    Do While ws.Cells(iRow, 1) <> ""
        Do While ws.Cells(iRow, iCol).Value <> ""
            ws.Cells(iRow, iCol).Value = Int(ws.Cells(iRow, iCol).Value) '去掉绿色三角，其实还是文本类型
            ws.Cells(iRow, iCol).NumberFormat = "0" '单元格格式设置，设置为不带小数的整数
            iCol = iCol + 1
        Loop
        '计算环比
        ws.Cells(iRow, 4).Value = Round((ws.Cells(iRow, 3).Value - ws.Cells(iRow, 2).Value) / ws.Cells(iRow, 2).Value, 4)
        ws.Cells(iRow, 4).NumberFormat = "0.00%"  ' 设置为带两位小数的数值
        '环比负增长，增加批注
        If ws.Cells(iRow, 4).Value < 0 Then
            ws.Cells(iRow, 4).AddComment "这是第" & i & "批注"
            i = i + 1
            With ws.Cells(iRow, 4).Comment
                 .Visible = msoTrue ' 使批注可见
                 .Shape.Fill.Visible = msoTrue ' 显示批注的填充色（如果设置了的话）
                 .Shape.Fill.ForeColor.RGB = RGB(255, 124, 128) ' 粉色' 设置批注颜色
                 .Shape.Fill.Transparency = 0 ' 设置填充色的透明度为0（不透明），全透明1
             End With
        End If
        iRow = iRow + 1
        iCol = 2
    Loop
    
    MsgBox "完成"
    
End Sub

批量删除批注

Private Sub CommandButton2_Click()
    Dim ws As Worksheet
    Dim lastRow As Integer
    Dim cell As Range

    Set ws = Worksheets("Sheet1")
    
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row ' 修改为你的列号，例如"D"表示第四列
    For Each cell In ws.Range("D2:D" & lastRow) ' 修改为你的列号和最后一行号，例如"D2:D" & lastRow 表示第四列除去第一行后的所有行
        If Not cell.Comment Is Nothing Then cell.Comment.Delete
    Next cell
    MsgBox "D列中的所有批注已删除"
End Sub