注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

蓝天堂

Venchia's Blog

 
 
 

日志

 
 

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

2006-09-17 15:01:54|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

出处:eNet硅谷动力

?

说起是否会使用Excel,估计只要用过电脑的人都说自己会用。真的是这样吗?就笔者的多年用户技术支援的经验,很多用户所谓的“会用”,真的只是仅仅局限于“会用”而己。输入一些内容,加上一些表格格线,点一下存盘,瞧,文件做好了。

如果只是这样,微软公司就不用煞费苦心,开发什么新版本了。早期的Excel 5.0就己经绰绰有余了。

其实,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。

应用示例:

让你从菜鸟成为玩转Excel的高手 - venchia - 蓝天堂

上图中,为方便比较,我将原始数据区域放在了同一工作表中(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”即可。

让你从菜鸟成为玩转Excel的高手 - venchia - 蓝天堂

通过上图可以看出,能找到的,系统己自动填入了找到的值,如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 返回相应语句执行后的返回值。

应用示例:

让你从菜鸟成为玩转Excel的高手 - venchia - 蓝天堂

第三招:投石问路(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 为需要进行检验的数值。分别为:空白(空白单元格)、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。

让你从菜鸟成为玩转Excel的高手 - venchia - 蓝天堂

应用示例:

让你从菜鸟成为玩转Excel的高手 - venchia - 蓝天堂

详细解释

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

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

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

?

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

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

让你从菜鸟成为玩转Excel的高手 - venchia - 蓝天堂

对于找不到的项目,系统显示#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的高手 - venchia - 蓝天堂

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

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


  评论这张
 
阅读(225)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016