EXCEL的If和IsError函数来消除VLOOKUP函数的错误值.doc

EXCEL的If和IsError函数来消除VLOOKUP函数的错误值》
  发表日期：2005年11月3日   出处：转载      已经有134位读者读过此文

巧用EXCEL的If和IsError函数来消除VLOOKUP函数的错误值

---- 大家都知道EXCEL是微软公司的OFFICE产品，EXCEL一经推出就得到很多经常处理大量数据的人士的喜爱，主要是因为它的快捷和自动计算的功能，特别是他提供了大量的函数，让我们能够十分方便的使用！
---- 例如：VLOOKUP函数就是一个十分好的应用函数，它主要是用来计算如奖金分配等工作的，为我们减少了很多的麻烦和一些不必要的错误，只要您的条件值是正确的，他保证能够让您得到准确无误的值，今后只要您的条件值有所改动，VLOOKUP函数马上就会更新您的所有值。好了，言归正传！
---- VLOOKUP函数
---- 语法
---- VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
---- Lookup_value 为需要在数据表第一列中查找的数值。
---- Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。
---- Col_index_num 为 table_array 中待返回的匹配值的列序号。
---- Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值。
---- 首先，我们看看下面的这个表（表1）
---- 这是一个编号和奖金分配的表，本例中奖金是随着编号的固定数值的不同而改变，而且任何不在此编号内的数据都将视为不合格产品，不能给奖金！如20和25这两个值，奖金分别为100和60，如果编号是21、22、23、24那么就不能得到奖金！
---- 表（1）
编号 奖金
5 50
10 110
15 120
20 100
25 60

---- 第一步我做了一个VLOOKUP函数，让奖金与编号挂钩，首先，看看我们的工资表是如何使用VLOOKUP函数的，见表（2）这是一个EXCEL数据表，它VLOOKUP需要一个主表 [表（2）] 和一个条件表 [表（1）] ，将他们放在一张表内即可，例如SHEET1内的不同列中即可，我将主表放在A1:E7中，将条件表[表（1）]放在H和I列内，一切准备就绪后，我们就可以将VLOOKUP函数放在相应的单元格中了，即C列中从C2到C7，首先，选择单元格C2，然后我们点击工具条中的按钮，在“查找与引用”里找到 “VLOOKUP”函数，点击确定即可，进入对话框后在：
---- lookup_value内输入：B2
---- table_array内输入：H:I
---- col_index_num内输入：2
---- range_lookup内输入：暂时不输入（空值）即近似匹配值，将在以下详细介绍。
---- 确定后，单元格C2得到的公式为：“=VLOOKUP(B2,H:I,2)”，直接在单元格中输入也是可以的！
---- 表（2）
 A B C D  E
1 姓名 编号 奖金 基本工资 合计
2 张一 3 #N/A 100  #N/A
3 李二 21 100 130  230
4 王五 10 110 130  240
5 大侠 15 120 150  270
6 小虾 20 100 160  260
7 老板 25 60 250  310
---- 然后，使用EXCEL的“自动填充”功能来填入下面5个数据，填充的结果如 [表（2）]，只要你改变“条件表” [表（1）]的值，[表（2）]数值将马上进行改变。这样就实现了表格的自动化，但是有一点你可以看到这个表格有两个很大的缺陷，首先就是它出现了错误值 #N/A，这个错误值代表的意思是：“除以了0”；其次 “=VLOOKUP(B2,H:I,2)”这个公式是一个近似匹配值，即20和25之间的任意值奖金都为100，如本例的单元格B3它的值为：21，就得到奖金100（参看[表1）]）。而本例的要求是：不在编号内的数据，都将视为不合格产品，且不能给奖金！即C3的值必需为“0”，不应该是“100”，否则将导致合计数据为230而不是130元，产生错误！怎样才能改正这两个错误的发生呢？
---- 这就是我要做的第二步，选用另两个函数
---- ISERROR和IF函数，ISERROR函数是一个测试错误的函数，它的语法是：
---- ISERROR 值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。如果您的测试值为错误的时候，当前得到的值为“TRUE”，否则将为“FALSE”。
---- 举例：如果有一个单元格“B9”是一个公式为：“＝2/0”回车后，它将成为一个错误值即“#DIV/0!”，用以告诉我们任何值不可以除零！在单元格“A9”内输入公式“=ISERROR(B9)”回车后“A9”的值为：“TRUE”，表示测试结果是“真”，如果再次改变“B9”的公式为：“＝2/2”回车后给公式变为“1”，我们会发现同时“A9”的值也发生了变化，变为：“FALSE”。
---- 在本例中公式“VLOOKUP(B2,H:I,2)”相当于上例中的“B9”单元格，现在我们看看如下两个公式：
---- ①“=ISERROR(VLOOKUP(B2,H:I,2))” ← 近似匹配值
---- ②“=ISERROR(VLOOKUP(B2,H:I,2,FALSE))”← 精确匹配值
---- 上述两个公式，得到的值是不同的，即①得到的两个值（20和25）之间的值如21得到的是FLASE，这就与我们的特定值 [表（1）]规定的“任何不在此编号内的数据都将视为不合格产品，不能给奖金！”产生了冲突，所以只能强制让公式得TRUE，即只能用②这个公式，让VLOOKUP函数精确匹配。这样C2和C3的值都为“TRUE”我们的目的就达到了！
---- 最后一步就是使用IF函数，
---- 它显然是一个条件函数，语法
---- IF(logical_test,value_if_truevalue_if_false)
---- Logical_test 计算结果为 TRUE 或 FALSE 的任何数值或表达式。
---- Value_if_true Logical_test 为 TRUE 时函数的返回值。
---- Value_if_false Logical_test 为 FALSE 时函数的返回值。
---- “Logical_test”的值就是在第二步中，说的②精确匹配公式 “
---- Value_if_true”这个值添入：“ "0" ”，即值公式②的值等于TRUE时。
---- “Value_if_false” 这个值添入：“VLOOKUPB2,H:I,2) ”，即值公式①的值等于FALSE时。
---- OK单元格“C2”最终的公式得到了，如下：
---- “=IF(ISERROR(VLOOKUP(B2,H:I,2,FALSE)),"0",VLOOKUP(B2,H:I,2))” 最后使用“自动填充”功能，向下拖动即可得到相应的数值，见[表（3）]
---- 表（3）
姓名 编号 正确奖金 错误奖金 基本工资 错误合计 正确合计
张一 3 0 #N/A 100 #N/A 100
李二 21 0 100 130 230 130
王五 10 110 110 130 240 240
大侠 15 120 120 150 270 270
小虾 20 100 100 160 260 260
老板 25 60 60 250 310 310
---- 通过这个公式我们能够认识到EXCEL的强大数据处理能力，并由此让您对EXCEL的函数有进一步的了解，在实际工作中充分利用它的内置函数方便自己的工作！

8、防止误改有新招

　　在包含多个工作表的工作薄中，为了防止误修改，我们常常采取将行(列)隐藏或者设置编辑区域的方法，但是如果要防止整个工作表的误修改怎么办呢?单击“格式→工作表→隐藏”，将当前的工作表隐藏，这样操作者连表格都看不到，误操作就无从谈起了。要重新显示该表格，只须单击“格式→工作表→取消隐藏”。要注意的是：如果设置了工作表保护，则不能进行隐藏操作。

　　9、快速隐藏

　　在打印工作表时，我们有时需要把某些行或者列隐藏起来，可是用菜单命令或调整行号(列标)分界线的方法比较麻烦，这时笔者介绍一个简单方法：在英文状态下，按“Ctrl+9”或“Ctrl+0”组合键，就可以快速隐藏光标所在的行或列。

对单元格的保护分为写保护和读保护两类，所谓写保护就是对单元格中输入信息加以限制，读保护是对单元格中已经存有信息的浏览和查看加以限制。

对单元格的写保护有两种方法：

A、对单元格的输入信息进行有效性检测。首先选定要进行有效性检测的单元格或单元格集合，然后从数据菜单中选择“有效数据”选项，通过设定有效条件、显示信息和错误警告，控制输入单元格的信息要符合给定的条件。

B、设定单元格的锁定属性，以保护存入单元格的内容不能被改写。可分为以下步骤：

（1）选定需要锁定的单元格或单元格集合；

（2）从格式菜单中选择“单元格”选项；

（3）在设置单元格格式的弹出菜单中选择“保护”标签，在弹出的窗口中，选中“锁定”；

（4）从工具菜单中选择“保护”选项，设置保护密码，即完成了对单元格的锁定设置。

对单元格的读保护有三种方法：

A、通过对单元格颜色的设置进行读保护。例如：将选定单元格或单元格集合的背景颜色与字体颜色同时设为白色，这样，从表面看起来单元格中好像是没有输入任何内容，用户无法直接读出单元格中所存储的信息。

B、用其他画面覆盖在需要保护的单元格之上，遮住单元格的本来面目，以达到读保护目的。例如：使用绘图工具，画一不透明矩形覆盖在单元格之上，从格式菜单中选定矩形的“锁定”选项，然后保护工作表，以保证矩形不能被随意移动。这样，用户所看到的只是矩形，而看不到单元格中所存储的内容。

C、通过设置单元格的行高和列宽，隐藏选定的单元格，然后保护工作表，使用户不能直接访问被隐藏的单元格，从而起到读保护的作用。

1.在Word中点击“插入”→“特殊符号”→数学符号”，然后选择“×”即可输入乘号。

2.顺次点击“视图”→“工具栏”→“符号栏”，然后选择“×”即可。

3.打开任意一种输入法，右键点击“软键盘”图标，选择“数字符号”，选择“×”即可。

4.在智能ABC输入法下，按“V+数字键1”，在出现的选择框中选择“×”即可。

5.在拼音加加输入法下，输入“ch”，再按数字键“2”或左边的Shift键即可选中“×”。事实上，其他输入法下也有“×”的输入方法，大家可以自行摸索。

6.在Word中依次点击“插入”→“符号”，在“字体（F）”下拉列表中选择“Symbol”，然后在第六行第九列选择“×”即可插入乘号。

如果要输入“÷”，按前五种方法输入即可。事实上，输入乘号和除号的方法还有很多，大家可以自己摸索，找到了不要忘记告诉我们呀。