Excel办公软件从菜鸟到高手实训教程.docx

Excel办公软件从菜鸟到高手实训教程.docx

《Excel办公软件从菜鸟到高手实训教程.docx》由会员分享，可在线阅读，更多相关《Excel办公软件从菜鸟到高手实训教程.docx（13页珍藏版）》请在冰豆网上搜索。

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_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:

$H$5,2,FALSE））,"",VLOOKUP（D3,$G$2:

$H$5,2,FALSE））

　　下图显示了这种情况。

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

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

　　好了，新创的这招叫什么呢？

就叫瞒天过海吧！

第五招：

留头去尾（Left函数）

　　此招用来对原始数据进行截取。

截取的方式是从第一个字符开始，截取用户指定长度的内容。

　　例如：

在一个工作表中，某一列的资料是地址，录有省、市、街道等。

如果你想插多一列，加入省份的资料，以便进行省份筛选，则可用该函数自动进行截取，而无需人工输入。

使用语法

　　LEFT（text,num_chars）

　　Text是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。

　　Num_chars指定要由LEFT所提取的字符数。

　　Num_chars必须大于或等于0。

　　如果num_char