回来主页
当时方位: manbetx万博官网 > > Excel2007教程 >

EXCEL的If和IsError函数来消除VLOOKUP函数的过错值

时刻:2012-02-22 14:31来历:manbetx万博官网网 www.fwnyp.com修正:麦田守望者

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,如表(1)

编号
奖金
5
50
10
110
15
120
20
100
25
60
表(1)

假如编号是21、22、23、24那么就不能得到奖金!  

  榜首步我做了一个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)",直接在单元格中输入也是能够的!  
    

 
EXCEL的If和IsError函数来消除VLOOKUP函数的过错值

   然后,运用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
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
表(3)


  经过这个公式咱们能够认识到EXCEL的强壮数据处理才能,并由此让您对EXCEL的函数有进一步的了解,在实践作业中充分运用它的内置函数便利自己的作业!

------分隔线----------------------------
标签(Tag):excel excel2007 excel2010 excel2003 excel技巧 excel实例教程 excel2010技巧
------分隔线----------------------------
引荐内容
猜你感兴趣