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） ABCDE1姓名编号奖金基本工资合计2张一3#N/A100#N/A3李二211001302304王五101101302405大侠151201502706小虾201001602607老板2560250310- 然后，使用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_true,value_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” 这个值添入：“VLOOKUP(B2,H:I,2) ”，即值公式的值等于FALSE时。 - OK单元格“C2”最终的公式得到了，如下： - “=IF(ISERROR(VLOOKUP(B2,H:I,2,FALSE),0,VLOOKUP(B2,H:I,2)” 最后使用“自动填充”功能，向下拖动即可得到相应的数值，见表（3） - 表（3） 姓名编号正确奖金错误奖金基本工资错误合计正确合计张一30#N/A100#N/A100李二210100130230130王五10110110130240240大侠15120120150270270小虾20100100160260260老板256060250310310- 通过这个公式我们能够认识到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”，