《excel函数技巧使用》由会员分享，可在线阅读，更多相关《excel函数技巧使用（6页珍藏版）》请在金锄头文库上搜索。
1、说起是否会使用Excel ，估计只要用过电脑的人都说自己会用。真的是这样吗？就笔者的多年用户技术支援的经验，很多用户所谓的“ 会用 ” ，真的只是仅仅局限于“ 会用 ” 而己。输入一些内容，加上一些表格格线，点一下存盘，瞧，文件做好了。如果只是这样，微软公司就不用煞费苦心，开发什么新版本了。早期的Excel 5.0 就己经绰绰有余了。其实， Excel 是一个功能非常强大的数据处理系统，决不只是一个拿来画个表格的表格绘制工具。如果真正掌握了Excel 的所有功能，你会发现，很多重复的，烦闷的数据处理过程，会变得非常美妙，你只需点点或拖拖鼠标， 然后靠在椅背上啜口咖啡，本来会忙得你晕天黑地的任务
2、己让系统帮你处理好了。这时，看着仍忙得一塌湖涂的同事，你只有一个感觉：酷要真正发挥Excel 的威力，你必须掌握Excel 的灵魂 函数。不会使用Excel 的函数，你就不是一个真正的会用 Excel 的用户。正是一个个功能各异的函数，组成的不同的公式，才让Excel 这位大侠，有了绝世武功。 每一个函数， 便是他的一招绝学， 但只单单学会他的招术，也还是不够， 只有把所有招术练熟了，并综合运用，融会贯通，做到招中有招，才能真正掌握他的绝世武功，让Excel 自动帮我们完成无法完成的任务。下面，我们便来开始学心Excel 大侠的绝学。我会先一招一招地介绍，几招之后，再演示怎么综合应用所学的几招
3、，创建新招。在实际对敌中，我们要知道，招是死的，人是活的，招由心生，绵绵不绝。第一招：大海捞针（Vlookup 函数）招如其名。此招用来在一个茫茫的数据源中，自动让电脑找出你要的某个数据的相关资料并填在指定的地方。也是就是，可以让电脑在一个表格或指定的一个区域中查找某一指定的值，并由此返回该值相对应当前行中指定列处的数值。此招还有相应的变化，分别为 lookup和 Hlookup 两式。 当查找的数据是水平排列时，可以使用函数HLOOKUP 代替函数VLOOKUP 。但用到的情况比较少，这里不做介绍。例如：你有一个工作表，上面近万项货品名称，每项货品的代码，价格，购买日期等。如你要做另一份报表
4、，且其中部分货品在这份表中己有数据，则可以利用此招，只输入货品的名称或者代码，其余的让电脑自动查找并返回相应的价格，购买日期等。使用语法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、 、 -
5、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 第二列中的数值，以此类推。
6、如果 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_val
7、ue ，且 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_arra
8、y前面需加上工作表的名称，写法为“ 表名 ! ”区域范围， 如“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 ” 参数系统，查找区域内容未进行排序，需使用精确查找，找不到就算了，不返回近似匹配值。特别要注意的是，通常我们都
9、是使用鼠标拖动的方法来填充公式，而拖动时，Excel 对公式中区域的引用，处理方法是不一样的。如果是相对参照， 即栏名列号前没有“$”符号，则 Excel 会对该区域作相对位移，如上栏是 E2:B5, 拖到下栏后，即会自动成为E3:B6 ，这种处理方法在很多公式中是必要的，但在这个公式中却是致命的，因为它更改了查找的原始数据的区域，导致实际上包含有的数据，因己不在查找的区域中而漏网。这也是很多用户在实际应用中犯的错误，引致查找结果不真实。要解决这个问题，我们可以利用Excel 对区域引用的第二种方法：绝对参照。即在栏名列号前加上“$”，这样，系统就不会作相对的位移，无论怎样拖，区域范围都不变。
10、（在很多情况下，我们会使用“ 名称 ” 来代替直接的区域指定方式，使用更为方便。这一内容将在其他章节中介绍）相对参照与绝对参照的写法，可以让电脑作自动转换。方法是，先将当前单元格定位在要修改的单元格上，然后在资料编辑列，用鼠标涂黑（英文的说法叫Highlight ）要转换的部分，再按“F4”即可。见下图：通过上图可以看出，能找到的，系统己自动填入了找到的值，如Apple & cherry ，对于找不到的（Plum & Pear ），则显示 #N/A。第二招：左右逢源（If 函数）此招用来对某一条件执行的真假值进行判断，根据逻辑计算的真假值，返回不同结果。如果结果为真，则返回一个真，如果为假，则
11、返回另一值，可谓左右逢源。使用语法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
12、 为 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
13、为 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
14、 类函数，可以检验数值的类型并根据参数取值返回TRUE 或FALSE 。例如，如果数值为对空白单元格的引用，函数ISBLANK 返回逻辑值TRUE ，否则返回FALSE 。使用语法ISBLANK(value) ISERR(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) Value 为需要进行检验的数值。分别为：空白（空白单元格）、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。应用示例：详细解释公式 “=I
15、SBLANK(A1) ” ，表示对A1 单元格是否为空进行判断。如是是空的，则返回“True ”值，如果不为空，则返回“False) 的值。上面示例图中，B1 及 B2 单元格中的函数分别对A1 及 A2 单元格进行是否为空白的判断。结果显示一个为真，一个为假。OK ，我们己经学了三招，现在我们要将这三招组合起来，自创一招新招。第四招：瞒天过海（自创组合招数）我们来看学第一招时用的例子，对于找不到的项目，系统显示#N/A ，但这样的报告交给上司，未免太难看了些。用什么方法，可以让其不显示出错误值呢？对了，先来一招投石问路，对系统返回的值做一个判断，看看系统到底找到没有。再来一招左右逢源，对于找
16、到的就显示原值，找不到的，就干脆让它显示空白（当然，也可让设置其他的值如 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，报表因此漂亮多了。因为是公式，只要写好第一个单元格的公式，其余的一拖就好了。好了，新创的这招叫什么呢？就叫瞒天过海吧！
