让你从菜鸟成为玩转Excel的高手

时间:2009-05-07 00:28:00  来源:第二电脑网  作者:第二电脑网

  第二电脑网导读:晕天黑地的任务己让系统帮你处理好 了。这时,看着仍忙得一塌湖涂的同事,你只有一个感觉:酷……   要真正发挥Excel的威力,你必须掌握Excel的灵魂―函数。不会使用Excel的函数,你就不是一个真正的会用Excel的用户。正是一 个个功能各异的函数,组成的不同的公式,才让Excel这位大侠,有了绝世武功。每一个函数,便是他的一招绝学,但只单单学会他的招术,也还...
  正文:

  其实,Excel是一个功能非常强大的数据处理系统,决不只是一个拿来画个表格的表格绘制工具。如果真正掌握了Excel的所有功能,你会发 现,很多重复的,烦闷的数据处理过程,会变得非常美妙,你只需点点或拖拖鼠标,然后靠在椅背上啜口咖啡,本来会忙得你晕天黑地的任务己让系统帮你处理好 了。这时,看着仍忙得一塌湖涂的同事,你只有一个感觉:酷…… 

  要真正发挥Excel的威力,你必须掌握Excel的灵魂―函数。不会使用Excel的函数,你就不是一个真正的会用Excel的用户。正是一 个个功能各异的函数,组成的不同的公式,才让Excel这位大侠,有了绝世武功。每一个函数,便是他的一招绝学,但只单单学会他的招术,也还是不够,只有 把所有招术练熟了,并综合运用,融会贯通,做到招中有招,才能真正掌握他的绝世武功,让Excel自动帮我们完成无法完成的任务。

  下面,我们便来开始学心Excel大侠的绝学。我会先一招一招地介绍,几招之后,再演示怎么综合应用所学的几招,创建新招。在实际对敌中,我们要知道,招是死的,人是活的,招由心生,绵绵不绝。

  第一招:大海捞针(Vlookup函数)

  招如其名。此招用来在一个茫茫的数据源中,自动让电脑找出你要的某个数据的相关资料并填在指定的地方。也是就是,可以让电脑在一个表格或指定的 一个区域中查找某一指定的值,并由此返回该值相对应当前行中指定列处的数值。此招还有相应的变化,分别为lookup和Hlookup两式。当查找的数据 是水平排列时,可以使用函数 HLOOKUP 代替函数 VLOOKUP。但用到的情况比较少,这里不做介绍。

  例如:你有一个工作表,上面近万项货品名称,每项货品的代码,价格,购买日期等。如你要做另一份报表,且其中部分货品在这份表中己有数据,则可以利用此招,只输入货品的名称或者代码,其余的让电脑自动查找并返回相应的价格,购买日期等。

  使用语法

  VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

  Lookup_value 为需要查找的值。Lookup_value 可以为数值、引用或文本字符串。

  Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用,例如数据库或列表。

  如果 range_lookup 为 TRUE,则 table_array 的第一列中的数值必须按升序排列:…、-2、-1、0、1、2、…、-Z、FALSE、TRUE;否则,函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE,table_array 不必进行排序。

  通过在“数据”菜单中的“排序”中选择“升序”,可将数值按升序排列。

  Table_array 的第一列中的数值可以为文本、数字或逻辑值。

文本不区分大小写。

  Col_index_num 为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时,返回 table_array 第一列中的数值;col_index_num 为 2,返回 table_array 第二列中的数值,以此类推。如果 col_index_num 小于 1,函数 VLOOKUP 返回错误值值 #VALUE!;如果 col_index_num 大于 table_array 的列数,函数 VLOOKUP 返回错误值 #REF!。

  Range_lookup 为一逻辑值,指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于 lookup_value 的最大数值;如果 range_value 为 FALSE,函数 VLOOKUP 将返回精确匹配值。如果找不到,则返回错误值 #N/A。

  说明

  如果函数 VLOOKUP 找不到 lookup_value,且 range_lookup 为 TRUE,则使用小于等于 lookup_value 的最大值。

  如果 lookup_value 小于 table_array 第一列中的最小数值,函数 VLOOKUP 返回错误值 #N/A。

  如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE,函数 VLOOKUP 返回错误值 #N/A。

  应用示例:

 

  上图中,为方便比较,我将原始数据区域放在了同一工作表中(E1:F5),实际使用时,原始数据可以在不同的工作表,甚至不同的工作簿(即不同 的Excel文件)。当被查找的内容与原始内容在不同的工作表,table_array前面需加上工作表的名称,写法为 “表名! ”区域范围,如“Sheet2!$A$1:$B$12”,而若在不同的工作簿,则还得加上文件名,如“[文件名]sheet1!$A$1:$B$12”。

  详细解释

  公式“=Vlookup(A2,$E$2:$F$5,2,FALSE)”中A2表示要查找的值为A2单元格的内容,即“Apple”, “$E$2:$F$5”告诉电脑,应该去$E$2:$F$5这个数据区域中查找,“2”表示找到后,应传回该区域第二列的值,即数量列,最后 “FALSE”参数系统,查找区域内容未进行排序,需使用精确查找,找不到就算了,不返回近似匹配值。

  特别要注意的是,通常我们都是使用鼠标拖动的方法来填充公式,而拖动时,Excel对公式中区域的引用,处理方法是不一样的。如果是相对参照, 即栏名列号前没有“$”符号,则Excel会对该区域作相对位移,如上栏是E2:B5,拖到下栏后,即会自动成为E3:B6,这种处理方法在很多公式中是 必要的,但在这个公式中却是致命的,因为它更改了查找的原始数据的区域,导致实际上包含有的数据,因己不在查找的区域中而漏网。这也是很多用户在实际应用 中犯的错误,引致查找结果不真实。要解决这个问题,我们可以利用Excel对区域引用的第二种方法:绝对参照。即在栏名列号前加上“$”,这样,系统就不 会作相对的位移,无论怎样拖,区域范围都不变。(在很多情况下,我们会使用“名称”来代替直接的区域指定方式,使用更为方便。这一内容将在其他章节中介 绍)

  相对参照与绝对参照的写法,可以让电脑作自动转换。方法是,先将当前单元格定位在要修改的单元格上,然后在资料编辑列,用鼠标涂黑(英文的说法叫Highlight)要转换的部分,再按“F4”即可。见下图:

  通过上图可以看出,能找到的,系统己自动填入了找到的值,如Apple & cherry,对于找不到的(Plum & Pear),则显示#N/A。

第二招:左右逢源(If函数)

  此招用来对某一条件执行的真假值进行判断,根据逻辑计算的真假值,返回不同结果。如果结果为真,则返回一个真,如果为假,则返回另一值,可谓左右逢源。

  使用语法

  IF(logical_test,value_if_true,value_if_false)

  Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。例如,A1>=60 就是一个逻辑表达式,如果单元格 A1 中的值大于或等于 60,表达式即为 TRUE,否则为 FALSE。本参数可使用任何比较运算符。

  Value_if_true logical_test 为 TRUE 时返回的值。例如,如果本参数为文本字符串“预算内”而且 logical_test 参数值为 TRUE,则 IF 函数将显示文本“预算内”。如果 logical_test 为 TRUE 而 value_if_true 为空,则本参数返回 0(零)。如果要显示 TRUE,则请为本参数使用逻辑值 TRUE。Value_if_true 也可以是其他公式。

  Value_if_false logical_test 为 FALSE 时返回的值。例如,如果本参数为文本字符串“超出预算”而且 logical_test 参数值为 FALSE,则 IF 函数将显示文本“超出预算”。如果 logical_test 为 FALSE 且忽略了 Value_if_false(即 value_if_true 后没有逗号),则会返回逻辑值 FALSE。如果 logical_test 为 FALSE 且 Value_if_false 为空(即 value_if_true 后有逗号,并紧跟着右括号),则本参数返回 0(零)。Value_if_false 也可以是其他公式。

  说明

  函数 IF 最多可以嵌套七层,用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。

  在计算参数 value_if_true 和 value_if_false 后,函数 IF 返回相应语句执行后的返回值。

  应用示例:

  第三招:投石问路(IS函数)

  此招用来对某个单元格的当前值的类型进行判断,以便知道其类型后,再采取下一部行动,因此称为投石问路。

  IS函数共有九个工作表函数。概括为 IS 类函数,可以检验数值的类型并根据参数取值返回 TRUE 或 FALSE。例如,如果数值为对空白单元格的引用,函数 ISBLANK 返回逻辑值 TRUE,否则返回 FALSE。

  使用语法

  ISBLANK(value)

  ISERR(value)

  ISERROR(value)

  ISLOGICAL(value)

  ISNA(value)

  ISNONTEXT(value)

  ISNUMBER(value)

  ISREF(value)

  ISTEXT(value)

  Value 为需要进行检验的数值。分别为:空白(空白单元格)、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。

  应用示例:



详细解释

  公式“=ISBLANK(A1)”,表示对A1单元格是否为空进行判断。如是是空的,则返回“True”值,如果不为空,则返回 “False)的值。

  上面示例图中,B1及B2单元格中的函数分别对A1及A2单元格进行是否为空白的判断。结果显示一个为真,一个为假。

  OK,我们己经学了三招,现在我们要将这三招组合起来,自创一招新招。

  第四招:瞒天过海(自创组合招数)

  我们来看学第一招时用的例子,

  对于找不到的项目,系统显示#N/A,但这样的报告交给上司,未免太难看了些。用什么方法,可以让其不显示出错误值呢?对了,先来一招投石问 路,对系统返回的值做一个判断,看看系统到底找到没有。再来一招左右逢源,对于找到的就显示原值,找不到的,就干脆让它显示空白(当然,也可让设置其他的 值如No等),岂不妙哉?

  因此,对于原单一公式:=VLOOKUP(A2,$E$2:$F$5,2,FALSE),可以结合IF和IS函数来使用。大家刚才看到,对于投 石问路,共有九种变化,其中第三式(ISERROR)或第五式(ISNA)均适合这种情况,可以使用。因此,组合后的公式就变成:

  =IF(ISNA(VLOOKUP(D2,$G$2:$H$5,2,FALSE)),"",VLOOKUP(D2,$G$2:$H$5,2,FALSE))

  或

  =IF(ISERROR(VLOOKUP(D3,$G$2:$H$5,2,FALSE)),"",VLOOKUP(D3,$G$2:$H$5,2,FALSE))

  下图显示了这种情况。红框中用的就是组合的公式,而其中的Plum & Pear没有再显示难看的#N/A,报表因此漂亮多了。

  因为是公式,只要写好第一个单元格的公式,其余的一拖就好了。

  好了,新创的这招叫什么呢?就叫瞒天过海吧!


"让你从菜鸟成为玩转Excel的高手"由第二电脑网原创提供,转载请注明:http://www.002pc.com/pcedu/Office/Excel/2008-12-14/7410.html


关键字:菜鸟 Excel

关于《让你从菜鸟成为玩转Excel的高手》文章的评论

站内搜索: 高级搜索

热门搜索: Windows style 系统 tr IP QQ CPU 安装 function 注册 if td