昵称：平常心
头衔：会员
积分：22分
注册：2010-4-30
状态：离线
|
|
Excel"数据有效性"和"条件格式"高级应用
编辑: 天马行空 发表日期: 2009-03-01 09:50
数据录入是一项既枯燥又烦琐，且容易出错的事情。不过如果我们能够合理的利用Excel中的“数据有效性”和“条件格式”这两项功能，那么我们的数据录入工作的出错机率将会大大的降低。但是朋友们知不知道如果把Excel函数和“数据有效性”、“条件格式”结合起来能够达到更好的效果吗？那么我们且来看看。
一、检查空单元格
大量数据录入完毕之后肯定先要检查一下有没有遗漏的单元格没有输入数据。如果用肉眼去看的话，太没效率了。试试“Isblank”函数吧。拖动鼠标，将所有数据区域全部选中，接着点击菜单栏中的“格式”---“条件格式”在弹出的条件格式对话框中的第一个下拉列表框中选择“公式”在后面的输入框中输入 “=isblank(a1)”
接着点击“格式”按钮。在“单元格格式”对话框中切换到“图案”选项。任意选择一种填充颜色。单击两次“确定”返回主界面。这时我们可以看到数据区域中的所有空白单元格全部都会以我们刚刚所预设的颜色显示。
颜色显示。见图2
这里需要注意一点。我们一定要设置单元格的填充颜色，而不是设置字体格式。因为我们需要空白单元格高亮显示，如果我们设置的是字体格式而不是单元格的填充颜色的话，此时因为空白单元格中没有任何数据，所以该格式起不到任何效果。
二、防止数据重复输入。
很多时候我们在工作中录入的数据是不允许重复的。例如本例中的“学号”或是员工资料中的“身份证号”“毕业证号”等等。我们同样可以利用函数来防止我们重复输入。
拖动鼠标选中我们不允许重复的数据区域。本例中为“B2：B13”接着点击菜单栏中的“数据”---“有效性”在“允许”下拉列表框中选择“自定义”在公式一栏中输入“=COUNTIF($B$2:$B$13,B2)=1”
接着切换到“出错警告”标签。在错误信息一栏中输入“您输入了重复数据!”。
最后点击“确定”返回主界面。
现在我们在B列中输入学生的学号时，如果输入了相同的学号，则系统会弹出如下对话框阻止您。
三、检查字数。
有时候我们输入数据的时候，该字段的字数全都是一样的，这样我也可以用函数来检查。例如我们输入员工的身份证号码。这些字段的字数全是18位。这时我们有必要有函数来检查一下我们输入的字数是否足够。我们假设本例中的“学号”字段全部都是五位数。拖动鼠标选中“B2：B13”接着点击菜单栏中的“数据”---“有效性”。在“允许”下拉列表框中选择“自定义”在公式一栏中输入“=len(b2)=5”。接着切换到“出错警告”标签。在错误信息一栏中输入“您输入的字数有误!”。接着点击“确定”返回。现在如果我们在“学号”这一列输入的字数不够五位数，那么系统同样会弹出对话框阻止。
四、查找前几名的数据。
大家都知道用“Max”或“Min”函数来返回数据的最大值或最小值。但是如果我们需要系统返回前几名或是后几名的数据呢？例如：我需要总分前三名的同学的姓名以红色显示。不知道大家还有没有办法？
来试试“large”函数吧。
拖动鼠标选中所有学生姓名也就是"A2:A13"区域。接着点击菜单栏中的“格式”---“条件格式”在第一个下拉列表框中选择“公式”。在第二个输入框中输入公式“=I2>LARGE($I$2:$I$13,4)”再点击“格式”按钮。在“单元格格式”对话框中设置好字体格式。单击两次确定返回主界面。现在我们可以看到前三名的同学姓名已经以我们设置的格式显示在单元格中了。
|
|
|
