你就是Excel高手Word文件下载.docx
《你就是Excel高手Word文件下载.docx》由会员分享,可在线阅读,更多相关《你就是Excel高手Word文件下载.docx(7页珍藏版)》请在冰点文库上搜索。
正是一个个功能各异的函数,组成的不同的公式,才让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!
$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_truelogical_test为TRUE时返回的值。
例如,如果本参数为文本字符串“预算内”而且logical_test参数值为TRUE,则IF函数将显示文本“预算内”。
如果logical_test为TRUE而value_if_true为空,则本参数返回0(零)。
如果要显示TRUE,则请为本参数使用逻辑值TRUE。
Value_if_true也可以是其他公式。
Value_if_falselogical_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:
VLOOKUP(D3,$G$2:
下图显示了这种情况。
红框中用的就是组合的公式,而其中的Plum&
Pear没有再显示难看的#N/A,报表因此漂亮多了。
因为是公式,只要写好第一个单元格的公式,其余的一拖就好了。
好了,新创的这招叫什么呢?
就叫瞒天过海吧!