Excel97数据的保护和跟踪监测
作者：张仁庆 魏文芳
单位：张仁庆(郧阳医学院计算机中心 十堰442000)；魏文芳(郧阳医学院计算机中心 十堰442000)
关键词：非法数据；数据监控；IF函数；拒绝非法数据；宏指令
数理医药学杂志000447
摘 要 探讨了Excel97数据的保护与跟踪监测，为减少和发现输入、计算过程中的错误数据提供了有效方法。
中图分类号： TP 309.2 文献标识码： A
文章编号：1004-4337(2000)04-0356-02
Excel97具有强大的数据管理能力，它不仅可以能对大数据量的表格进行输入、编辑、访问、复制等操作，还可以创建和组织各种数据表格和各类图表，使得制作出的报表图文并茂，信息表达清晰明了；还可以把工作表的数据作为一个数据库来管理，并提供排序、分类汇总、数据筛选等功能，是我们处理数据、管理数据的好帮手。然而，工作表中的数据的安全问题是我们用户最关心的问题，工作表中的数据越多，就越有可能出现问题，若出现问题，我们就要投入大量的精力来校对、改错。怎样才能把数据输入时的出错率降低到最小呢?怎样才能避免和发现误输和后来的误改呢?
, http://www.100md.com
通常工作表内的每列或某些范围内的数据有其共同的特征，这就为保护数据提供了条件，虽然Excel97系统提供了一些数据的保护方法，但我们还可以利用IF函数、通过宏指令等方法，对工作表中的数据进行监控。下面几种方法，供同行参考。
1 利用“有效数据命令”将非法数据拒之门外
Excel97“数据”菜单的“有效数据”命令，可以很方便地完成有效数据的设置。以表1为例，此工作表是统计学生成绩的一个列表，若要防止非法数据的输入，可以对各门成绩进行有效数据的设置。先选择工作表范围C2：E6，再启动“数据”菜单的“有效数据”命令。弹出有效数据对话框后，在“设置”标签中的有效条件“许可：”项中设为整数，“数据(D：)”项设为介于，“最小值”设为0，“最大值”设为100。将“错误警告”标签中的“图案样式(Y：)”设为“中止”，确定即可。此时你在C2：E6区域内输入的数据，被限定在0到100之内，超出这个范围的数据或非数值型数据，视为非法数据，系统会提出错误信息，要求用户重新输入数据。
, 百拇医药
2 利用IF函数计算的设置拒绝非法数据
拒绝非法数据的另一种方法是利用IF函数，虽然较上一种方法麻烦，但可以自由地选择范围。如表1，当C2单元格的值小于100或大于0，并且当D2单元格的值小于100或大于0，并且当E2单元格的值小于100或大于0时，F2单元格为C2、D2和E2的和，否则D2单元格中显示“错误”。操作如下：
① 选中F2，输入公式“=IF(AND(AND (AND(C2<=100,C2>=0), AND(D2<=100,D2>=0)),AND(E2<=100,E2>=2)),C2+D2+E2,"错误")”
② 将光标置于F2下角，当光标成十字型时，按下鼠标左键，向下拖至需要计算的最后一个单元格，此时F列就计算出结果。
表1 学生成绩工作表
, 百拇医药
A
B
C
D
E
F
G
1
系别
姓名
高数
英语
革命史
总分
, 百拇医药
2
机械
张峰
87
98
87
3
机械
陈燕
99
87
76
4
计算机
, http://www.100md.com
王亮
87
87
98
5
计算机
李凌
98
98
99
6
计算机
李威
67
, http://www.100md.com
56
67
3 实时验证数据是否更改
在学生成绩统计中，最担心的是学生成绩的无故更改。为了避免学生成绩无故更改的发生，我们可以对必要的数据实时验证。如表1，若不小心更改F列中数据(总分)，则在G列中就会自动出现“×”，为了醒目，把“×”设为红色。具体作法为：
① 选择G2，输入公式“=IF(E2=C2+D2+E2,"","×")”；
② 选择G2，复制，再选择G3：G6，粘贴。
③ 选择G3：G6，启动“格式”菜单中的“条件格式”命令。设置条件1，单元格值等于“×”，设置格式中字体为红色，确定。
4 数据的跟踪检测
, 百拇医药
通过编制宏指令可以对一定范围内数据的进行跟踪检测。当某个单元格中的数据发生改变时，其中的数据就会显示出醒目的红色字体，能够帮助你快速地发现错误。宏虽然长一点，但效果不错。
Option Explicit
Public aa(200,200) As Variant 数组元素个数可据记录的多少来定
Public bb,cc As Variant
Sub 宏 1( )
Dim i,j As Integer
Dim r, c As Integer
bb=ActiveCell. Cells.Row
, http://www.100md.com
cc=ActiveCell.Cells.Column
r=Selection.Rows.Count
c=Selection.Columns.Count
For i=1 To r
For j=1 To c
aa(i,j)=Selection.Cells(i,j)
Next
Next
End Sub
Sub 宏 2( )
, 百拇医药
Dim i,j As Integer
Dim r,c As Integer
Ifbb<>ActiveCell. Cells.Row Or cc<>ActiveCell.Cells.Column Then
MsgBox "可能范围选择错误或数据未被记录!"
Exit Sub
End If
r=Selection.Rows.Count
c=Selection.Columns.Count
For i=1 To r
, http://www.100md.com
For j=1 To c
If aa(i,j)<>Selection.Cells(i,j)Then
Selection.Cells(i,j).Font.ColorIndex=3 设字体颜色为红色
End If
Next
Next
End Sub
宏1是对选定范围内数据的暂时保存(只要不退出Excel)，设执行宏1的快捷键为Ctrl+A。宏2是对选定范围内数据和暂时保存数的比较，若数据不同(数据已更改)，则红色显示，从而起到数据的跟踪检测。设执行宏2的快捷键为Ctrl+B。在执行宏1和宏2时，选择范围最好一样(当然，执行宏2时选取的范围也可以小于执行宏1选取的范围，但范围选择的起点应该一样)。
, 百拇医药
设置宏的快捷键的方法为：
① 从“工具”菜单中选“宏”，在“宏”子菜单下选“宏”命令。
② 在对话框中选宏1，再单击“选项”。
③ 出现“宏选项”对话框时，在快捷键区域的输入框中填入a。确定。最后关闭宏对话框(宏2快捷键的建立方法与宏1相同)。
5 利用Excel的数据“隐藏功能保护数据”
对于一张大数据量的工作列表，为了数据输入的明了和数据检测的方便或者是数据的安全，我们还可以利用Excel的“格式”菜单下“行”子菜单下的“隐藏”命令，将列表中的部分记录隐藏。这种功能，常常不被一些用户所看中，其实这种功能很有用，特别是在列表中的记录很多的情况下。我们在输入记录的时候，可以先把以前输入的且无错的记录暂隐藏起来。采用这种方法，在输入数据时，因记录相对少，则对数据的监控和修改，就简便多了。
6 设密码保护工作表和工作簿
对于工作表的保护(防止其他人员修改)，可以利用“工具”菜单下的“保护”子菜单下的“保护工作表”命令，来设置自己的口令(密码)。
若要保护工作簿，选择“文件”菜单下的“另存为”命令，单击“选项”按钮。在文件共享权限设置项中，设置自己的口令。
收稿日期：1999-10-14, 百拇医药
单位：张仁庆(郧阳医学院计算机中心 十堰442000)；魏文芳(郧阳医学院计算机中心 十堰442000)
关键词：非法数据；数据监控；IF函数；拒绝非法数据；宏指令
数理医药学杂志000447
摘 要 探讨了Excel97数据的保护与跟踪监测，为减少和发现输入、计算过程中的错误数据提供了有效方法。
中图分类号： TP 309.2 文献标识码： A
文章编号：1004-4337(2000)04-0356-02
Excel97具有强大的数据管理能力，它不仅可以能对大数据量的表格进行输入、编辑、访问、复制等操作，还可以创建和组织各种数据表格和各类图表，使得制作出的报表图文并茂，信息表达清晰明了；还可以把工作表的数据作为一个数据库来管理，并提供排序、分类汇总、数据筛选等功能，是我们处理数据、管理数据的好帮手。然而，工作表中的数据的安全问题是我们用户最关心的问题，工作表中的数据越多，就越有可能出现问题，若出现问题，我们就要投入大量的精力来校对、改错。怎样才能把数据输入时的出错率降低到最小呢?怎样才能避免和发现误输和后来的误改呢?
, http://www.100md.com
通常工作表内的每列或某些范围内的数据有其共同的特征，这就为保护数据提供了条件，虽然Excel97系统提供了一些数据的保护方法，但我们还可以利用IF函数、通过宏指令等方法，对工作表中的数据进行监控。下面几种方法，供同行参考。
1 利用“有效数据命令”将非法数据拒之门外
Excel97“数据”菜单的“有效数据”命令，可以很方便地完成有效数据的设置。以表1为例，此工作表是统计学生成绩的一个列表，若要防止非法数据的输入，可以对各门成绩进行有效数据的设置。先选择工作表范围C2：E6，再启动“数据”菜单的“有效数据”命令。弹出有效数据对话框后，在“设置”标签中的有效条件“许可：”项中设为整数，“数据(D：)”项设为介于，“最小值”设为0，“最大值”设为100。将“错误警告”标签中的“图案样式(Y：)”设为“中止”，确定即可。此时你在C2：E6区域内输入的数据，被限定在0到100之内，超出这个范围的数据或非数值型数据，视为非法数据，系统会提出错误信息，要求用户重新输入数据。
, 百拇医药
2 利用IF函数计算的设置拒绝非法数据
拒绝非法数据的另一种方法是利用IF函数，虽然较上一种方法麻烦，但可以自由地选择范围。如表1，当C2单元格的值小于100或大于0，并且当D2单元格的值小于100或大于0，并且当E2单元格的值小于100或大于0时，F2单元格为C2、D2和E2的和，否则D2单元格中显示“错误”。操作如下：
① 选中F2，输入公式“=IF(AND(AND (AND(C2<=100,C2>=0), AND(D2<=100,D2>=0)),AND(E2<=100,E2>=2)),C2+D2+E2,"错误")”
② 将光标置于F2下角，当光标成十字型时，按下鼠标左键，向下拖至需要计算的最后一个单元格，此时F列就计算出结果。
表1 学生成绩工作表
, 百拇医药
A
B
C
D
E
F
G
1
系别
姓名
高数
英语
革命史
总分
, 百拇医药
2
机械
张峰
87
98
87
3
机械
陈燕
99
87
76
4
计算机
, http://www.100md.com
王亮
87
87
98
5
计算机
李凌
98
98
99
6
计算机
李威
67
, http://www.100md.com
56
67
3 实时验证数据是否更改
在学生成绩统计中，最担心的是学生成绩的无故更改。为了避免学生成绩无故更改的发生，我们可以对必要的数据实时验证。如表1，若不小心更改F列中数据(总分)，则在G列中就会自动出现“×”，为了醒目，把“×”设为红色。具体作法为：
① 选择G2，输入公式“=IF(E2=C2+D2+E2,"","×")”；
② 选择G2，复制，再选择G3：G6，粘贴。
③ 选择G3：G6，启动“格式”菜单中的“条件格式”命令。设置条件1，单元格值等于“×”，设置格式中字体为红色，确定。
4 数据的跟踪检测
, 百拇医药
通过编制宏指令可以对一定范围内数据的进行跟踪检测。当某个单元格中的数据发生改变时，其中的数据就会显示出醒目的红色字体，能够帮助你快速地发现错误。宏虽然长一点，但效果不错。
Option Explicit
Public aa(200,200) As Variant 数组元素个数可据记录的多少来定
Public bb,cc As Variant
Sub 宏 1( )
Dim i,j As Integer
Dim r, c As Integer
bb=ActiveCell. Cells.Row
, http://www.100md.com
cc=ActiveCell.Cells.Column
r=Selection.Rows.Count
c=Selection.Columns.Count
For i=1 To r
For j=1 To c
aa(i,j)=Selection.Cells(i,j)
Next
Next
End Sub
Sub 宏 2( )
, 百拇医药
Dim i,j As Integer
Dim r,c As Integer
Ifbb<>ActiveCell. Cells.Row Or cc<>ActiveCell.Cells.Column Then
MsgBox "可能范围选择错误或数据未被记录!"
Exit Sub
End If
r=Selection.Rows.Count
c=Selection.Columns.Count
For i=1 To r
, http://www.100md.com
For j=1 To c
If aa(i,j)<>Selection.Cells(i,j)Then
Selection.Cells(i,j).Font.ColorIndex=3 设字体颜色为红色
End If
Next
Next
End Sub
宏1是对选定范围内数据的暂时保存(只要不退出Excel)，设执行宏1的快捷键为Ctrl+A。宏2是对选定范围内数据和暂时保存数的比较，若数据不同(数据已更改)，则红色显示，从而起到数据的跟踪检测。设执行宏2的快捷键为Ctrl+B。在执行宏1和宏2时，选择范围最好一样(当然，执行宏2时选取的范围也可以小于执行宏1选取的范围，但范围选择的起点应该一样)。
, 百拇医药
设置宏的快捷键的方法为：
① 从“工具”菜单中选“宏”，在“宏”子菜单下选“宏”命令。
② 在对话框中选宏1，再单击“选项”。
③ 出现“宏选项”对话框时，在快捷键区域的输入框中填入a。确定。最后关闭宏对话框(宏2快捷键的建立方法与宏1相同)。
5 利用Excel的数据“隐藏功能保护数据”
对于一张大数据量的工作列表，为了数据输入的明了和数据检测的方便或者是数据的安全，我们还可以利用Excel的“格式”菜单下“行”子菜单下的“隐藏”命令，将列表中的部分记录隐藏。这种功能，常常不被一些用户所看中，其实这种功能很有用，特别是在列表中的记录很多的情况下。我们在输入记录的时候，可以先把以前输入的且无错的记录暂隐藏起来。采用这种方法，在输入数据时，因记录相对少，则对数据的监控和修改，就简便多了。
6 设密码保护工作表和工作簿
对于工作表的保护(防止其他人员修改)，可以利用“工具”菜单下的“保护”子菜单下的“保护工作表”命令，来设置自己的口令(密码)。
若要保护工作簿，选择“文件”菜单下的“另存为”命令，单击“选项”按钮。在文件共享权限设置项中，设置自己的口令。
收稿日期：1999-10-14, 百拇医药