Excel函数的使用基础.ppt

《Excel函数的使用基础.ppt》由会员分享，可在线阅读，更多相关《Excel函数的使用基础.ppt（26页珍藏版）》请在三一办公上搜索。

1、 Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,自定义公式的作成,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,自定义公式的作成,现在我们要根据收入来计算个人收入调节税，按照规定每月收入减去800元基础后，除去养老保险金、失业保险金、医疗保险金、住房公积金、工会费，对剩下的余额征收个税。余额在500元内的征收余额的5%，余额为5002000元的征收余额的10%，余额为20005000元的征收余额的15%征收个税的最高税率为45%（余额在10万元以上的）。如果用Excel的函数，则要进行多层if嵌套，稍有不注意，可能造成计算上差错，我们建立自己的函数来解决这个

2、问题。,转Excel函数之旅,玩,自定义公式的作成,首先进入Excel，在工具宏Visul Basic编辑器（也可按组合键Alt+F11），在“Visul Basic编辑器”中选择插入添加模块，在代码窗口输入下列函数：Function tax(income As Single)As Single Select Case income Case 0 To 800,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,自定义公式的作成,tax=0 Case 800.01 To 1300 tax=(income-800)*0.05 Case 1300.01 To 2800

3、 tax=(income-1300)*0.1+25 Case 2800.01 To 5800 tax=(income-2800)*0.15+175 Case 5800.01 To 20800,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,自定义公式的作成,tax=(income-5800)*0.2+625 Case 20800.01 To 40800 tax=(income-20800)*0.25+3625 Case 40800.01 To 60800 tax=(income-40800)*0.3+8625 Case 60800.01 To 80800 ta

4、x=(income-60800)*0.35+14625 Case 80800.01 To 100800,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,自定义公式的作成,tax=(income-80800)*0.4+21625 Case Is=100800 tax=(income-100800)*0.45+29625 Case Is 0 MsgBox“你的工资”&income&“输入有误”End Select End Function,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,自定义公式的作成,Continue,Exce

5、l中函数都有一个说明，帮助使用，我们也要给这个函数添加一个说明。在工具栏中选择“对象浏览器”(如图1)，选择我们所做Tax模块，在其右键属性中添加关于对这个函数的描述，这个描述将出现在Excel中关于函数的说明中，如果你要对软件保密的话，在“模块”上按右键，VBAproject属性保护中设置密码，这样别人就看不到你的源程序了。图1 添加自制函数说明,Excel函数 作成：知秋导购返利网,WME EUC教育资料 Excel函数 作成：裘进,郁林,转Excel函数之旅,玩,自定义公式的作成,End,这时，退出，回到Excel界面，将这个文件另存为：类型为“Microsoft Excel 加载宏”，

6、在Excel 2000中，它会自动更改保存位置为c:windowsapplication datamicrosoftaddins(系统装在c:windows)，当然，你也可以把这个文件tax.xla，直接复制到officelibrary（office的安装路径下），而在Excel 97中只能放在后一个位置。使用函数很简单，点击工具加载宏，在你创建的Tax前打个勾，在单元格直接输入“=tax()”，是不是像Microsoft office提供的函数一样（如图2），图2 使用自制函数,转Excel函数之旅,玩,查阅或引用函数的使用,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,函

7、数LOOKUP 的使用,函数 LOOKUP 有两种语法形式：向量和数组。向量:向量为只包含一行或一列的区域。函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。如果需要指定包含待查找数值的区域，则可以使用函数 LOOKUP 的这种形式。函数 LOOKUP 的另一种形式为自动在第一列或第一行中查找数值。Lookup_value 为函数 LOOKUP 在第一个向量中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。Lookup_vector 为只包含一行或一列的区域。Lookup_vect

8、or 的数值可以为文本、数字或逻辑值。,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,函数LOOKUP 的使用,示例 在上述工作表中：LOOKUP(4.91,A2:A7,B2:B7)等于“橙LOOKUP(5.00,A2:A7,B2:B7)等于“橙LOOKUP(7.66,A2:A7,B2:B7)等于“紫LOOKUP(7.66E-14,A2:A7,B2:B7)等于#N/A，因为 7.66E-14 小于 lookup_vector 即 A2:A7 中的最小值,Continue,Excel函数 作成：知秋导购返利网,WME EUC教育资料 Excel函数 作成：裘进

9、,郁林,转Excel函数之旅,玩,函数LOOKUP 的使用,数组:函数 LOOKUP 的数组形式是在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。如果需要查找的数值在数组的第一行或第一列，就可以使用函数 LOOKUP 的这种形式。当需要指定列或行的位置时，可以使用函数 LOOKUP 的其他形式。LOOKUP(lookup_value,array)Lookup_value 为函数 LOOKUP 在数组中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。Array 为包含文本、数字或逻辑值的单元格区域，它的值用于与 loo

10、kup_value 进行比较。,Continue,转Excel函数之旅,玩,函数LOOKUP 的使用,要点 这些数值必须按升序排列：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。示例LOOKUP(C,a,b,c,d;1,2,3,4)等于 3LOOKUP(“bump”,“a”,1;“b”,2;“c”,3)等于 2函数 LOOKUP 的数组形式与函数 HLOOKUP 和函数 VLOOKUP 非常相似。不同之处在于函数 HLOOKUP 在第一行查找 lookup_value，函数 VLOOKUP 在第一列查找，而函数 L

11、OOKUP 则按照数组的维数查找。,Continue,Excel函数 作成：知秋导购返利网,WME EUC教育资料 Excel函数 作成：裘进,郁林,转Excel函数之旅,玩,函数VLOOKUP 的使用,在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数 VLOOKUP 代替函数 HLOOKUP。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value 为需要在数据表第一列中查找的数值。Lookup_value 可以为数值、引用或文字串。

12、Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或数据清单。Col_index_num 为 table_array 中待返回的匹配值的列序号。Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值#N/A。,Continue,WME EUC教育资料 Excel函数 作成：裘进,郁林,转Excel函数之旅,玩,函数VLOOKUP 的使用,说明 如果函数 VL

13、OOKUP 找不到 lookup_value，且 range_lookup 为 TRUE，则使用小于等于 lookup_value 的最大值。如果 lookup_value 小于 table_array 第一列中的最小数值，函数 VLOOKUP 返回错误值#N/A。如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE，函数 VLOOKUP 返回错误值#N/A。,Continue,转Excel函数之旅,玩,函数VLOOKUP 的使用,示例:在上述工作表中，区域 A4:C12 的名称为 Range：VLOOKUP(2,Range,2,FALSE)

14、等于#N/AVLOOKUP(.746,Range,3,FALSE)等于 200VLOOKUP(0.1,Range,2,TRUE)等于#N/AVLOOKUP(2,Range,2,TRUE)等于 1.71,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,函数HLOOKUP 的使用,HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).Lookup_value 为需要在数据表第一行中进行查找的数值。.Lookup_value 可以为数值、引用或文字串。.Table_array 为需要在其中查找数据的

15、数据表。可以使用对区域或区域名称的引用。1.Table_array 的第一行的数值可以为文本、数字或逻辑值。2.如果 range_lookup 为 TRUE，则 table_array 的第一行的数值必须按 升序排列：.-2、-1、0、1、2、A-Z、FALSE、TRUE；否则，函数 HLOOKUP 将不能给出正确的数值。如果 range_lookup 为 FALSE，则 table_array 不必进行排序。3.不区分文本的大小写。,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,函数HLOOKUP 的使用,.Row_index_num 为 table_ar

16、ray 中待返回的匹配值的行序号。Row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推。如果 row_index_num 小于 1，函数 HLOOKUP 返回错误值#VALUE!；如果 row_index_num 大于 table-array 的行数，函数 HLOOKUP 返回错误值#REF!。.Range_lookup 为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回

17、小于 lookup_value 的最大数值。如果 range_value 为 FALSE，函数 HLOOKUP 将查找精确匹配值，如果找不到，则返回错误值#N/A!。,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,函数HLOOKUP 的使用,示例假设有一张关于汽车零配件库存清单的工作表：A1:A4 的内容为 Axles、4、5、6。B1:B4 的内容为 Bearings、4、7、8。C1:C4 的内容为 Bolts、9、10、11。HLOOKUP(Axles,A1:C4,2,TRUE)等于 4HLOOKUP(Bearings,A1:C4,3,FALSE)等于

18、 7HLOOKUP(Bearings,A1:C4,3,TRUE)等于 7HLOOKUP(Bolts,A1:C4,4,)等于 11Table_array 也可以为数组常量：HLOOKUP(3,1,2,3;a,b,c;d,e,f,2,TRUE)等于 c,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,函数INDEX的使用,INDEX 函数有两种语法形式：数组和引用。数组形式通常返回数值或数值数组，引用形式通常返回引用。当函数 INDEX 的第一个参数为数组常数时，使用数组形式。数组形式INDEX(array,row_num,column_num)Array 为单元

19、格区域或数组常数。有关数组常数中的数值的详细信息，请单击。Row_num 数组中某行的行序号，函数从该行返回数值。如果省略 row_num，则必须有 column_num。Column_num 数组中某列的列序号，函数从该列返回数值。如果省略 column_num，则必须有 row_num。,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,函数INDEX的使用,说明Row_num 和 column_num 必须指向 array 中的某一单元格，否则，函数 INDEX 返回错误值#REF!。示例INDEX(1,2;3,4,2,2)等于 4如果作为数组公式输入，则

20、：INDEX(1,2;3,4,0,2)等于 2;4如果单元格 B5:B6 分别包含文本“苹果”和“香蕉”，而单元格 C5:C6 分别包含文本“柠檬”和“鸭梨”，则：INDEX(B5:C6,2,2)等于“鸭梨”INDEX(B5:C6,2,1)等于“香蕉”,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,函数INDEX的使用,引用形式INDEX(reference,row_num,column_num,area_num).Reference 对一个或多个单元格区域的引用。.Row_num 引用中某行的行序号，函数从该行返回一个引用。.Column_num 引用中某

21、列的列序号，函数从该列返回一个引用。.Area_num 选择引用中的一个区域，并返回该区域中 row_num 和 column_num 的交叉区域。选中或输入的第一个区域序号为 1，第二个为 2，以此类推。如果省略 area_num，函数 INDEX 使用区域 1。如果将 row_num 或 column_num 设置为 0，函数 INDEX 分别返回对整个列或行的引用。,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,函数INDEX的使用,示例在下面的工作表中，区域 A2:C6 的名称为 Fruit，而区域 A1:C11 的名称为 Stock。INDEX(F

22、ruit,2,3)等于引用 C3，内容为 38INDEX(A1:C6,A8:C11),2,2,2)等于引用 B9，内容为$3.55SUM(INDEX(Stock,0,3,1)等于 SUM(C1:C11)等于 216SUM(B2:INDEX(Fruit,5,2)等于 SUM(B2:B6)等于 2.42,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,统计函数的使用,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,函数MODE的使用,返回在某一数组或数据区域中出现频率最多的数值。MODE(number1,number2,.)Number1,numb

23、er2,.是用于众数计算的 1 到 30 个参数，也可以使用单一数组（即对数组区域的引用）来代替由逗号分隔的参数。说明 参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或引用中包括文字串、逻辑值或空白单元格，这些值将被忽略；但包括数值零的单元格计算在内。如果数据集合中不含有重复的数据，则 MODE 数返回错误值 N/A。示例MODE(5.6,4,4,3,2,4)等于 4,Continue,Excel函数 作成：知秋导购返利网,转Excel函数之旅,玩,函数STDEV的使用,估算样本的标准偏差。标准偏差反映相对于平均值（mean）的离散程度。STDEV(number1,number2,.)Number1,number2,.为对应于总体样本的 1 到 30 个参数。也可以不使用这种用逗号分隔参数的形式，而用单一数组，即对数组单元格的引用。示例假设在生产过程中，10 件工具是由同样的机器生产的，将其作为随机样本，测试抗拉强度。样本值（1345、1301、1368、1322、1310、1370、1318、1350、1303、1299）分别存放于 A2:E3 单元格里。函数 STDEV 估算所有工具抗拉强度的标准偏差。STDEV(A2:E3)等于 27.46,End,End,Excel函数 作成：知秋导购返利网,