当前位置: > 热闻

办公小技巧:智能感知数据改动并添加标记

时间:2022-04-21 13:14:59 热闻 我要投稿

我们经常会在Excel表格中进行数据的核对修改,如果要在第一时间知道哪些数据被修改过,通过对修改过的数据做出标记是快速识别这类数据的好方法。利用Excel的有关组件,我们就可以实现被修改数据的标记功能。下面,我们以在Excel 2019中操作为例来说明。

1. 用好突出显示修订 修改数据轻松标记

对于数据修改,Excel自带突出显示修订功能。不过Excel 2019默认没有显示修订菜单,需要点击“文件→选项→自定义功能区→不在功能区中的命令”,在右侧定位到“审阅”,点击“新建组”,接着定位到“新建组”,然后将左侧的“追踪更改(旧版)”菜单添加到该组中(图1)。

返回Excel窗口,点击“审阅→追踪更改(旧版)→突出显示修订”,勾选“编辑时跟踪修订信息,同时共享工作簿”,时间选择全部,位置选择B2:B7的数据(图2)。

这样,当我们在原始工作簿中对数据进行修改时,在修改的单元格中就会添加三角形标记,鼠标移到修改位置可以看到修改者和原始数据(图3)。

小提示:如果使用Word 2019,默认就有修订菜单,若是觉得上述添加菜单操作麻烦,也可以直接将原始工作表粘贴到Word中,接着点击“审阅→修订”,这样在Word中修改的数据会在修改行前添加红色修订标记,点击可以看到修订前后的数据对比(图4)。

2. 设置条件格式 修改更醒目

突出显示修订需要设置工作簿为共享模式,并且对每个修改区域手动选择才能显示修订。借助条件格式设置则可以解决突出显示修订的不足,不仅可以使用醒目的颜色标注修改数据(不是像突出显示修订那样使用三角形标记),而且可以使用单独列显示原始数据(无需手动选择),当然也无需设置工作簿为共享模式。

在原工作簿中新建工作表,然后将数据都复制一份到新表中,点击“开始→条件格式→新建规则”(注意:如果原来的表格数据设置了“突出显示修订”,那么需要取消这个设置才能设置条件格式,否则条件格式菜单不可用)。在打开的窗口中选择“使用公式确定要设置格式的单元格”,在公式栏输入“=B2<>Sheet2!B2”(即两个表格的B2值不相同),点击“格式”,设置符合条件时单元格背景颜色填充为红色显示(图5)。

点击“条件格式→编辑规则”,选中上述新建的规则,在“应用于”选择B2:B7数据区域。如果有多个区块数据,按住Ctrl键进行多选即可。这样上述区域的数据发生变化时,就会应用设置的条件格式(图6)。

为了便于比对修改前后的数据,可以在C1中输入“修改提示”,在C2中输入公式“=IF(B2<>Sheet2!B2,"数据已改,原始数据是"&Sheet2!B2,"")”,表示当表1的B2数据修改后,在C2显示“数据已改,原始数据是表2复制的数据”,将公式下拉填充。接着右击Sheet2选择“隐藏”,这样当我们在原始工作表修改数据后,由于修改后的数据和Sheet2的不同,修改的数据单元格就会自动填充为红色,同时在C列则会提示数据已经修改,并会自动显示原始数据值(图7)。

3. 简单高效 使用VBA设置修改为数据上色

如果需要标注的文档很多,或者工作簿包含多个工作表,那么每个表都要重复设置上述条件,操作效率不高。这时,我们还可以借助VBA代码实现数据变动全局自动上色。

选中上述原始工作表,右击选择“查看代码”,在打开的窗口中输入下列代码(图8):

Dim x

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value <> x Then Target.Interior.Color = vbRed

End Sub

代码解释:这里先使用DIM语句设定一个变量,然后使用代码对变量进行校验,当活动单元格输入新值时就自动填充为红色(代码vbRed,可以自行更改为其他颜色)。

返回Excel窗口,只要我们对数据进行了更改,那么更改数据(当然也包括文字等任何修改)的单元格就会变为红色,默认是对整张工作表生效,也可以设置代码对整个工作簿生效,这样工作簿中任何数据的变动都会自动标红(图9)。