Excel函数的使用基础

1、 excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 自定义公式的作成自定义公式的作成 excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 自定义公式的作成自定义公式的作成现在我们要根据收入来计算个人收入调节税，按照规定每月收入减去现在我们要根据收入来计算个人收入调节税，按照规定每月收入减去800800元元基础后，除去养老保险金、失业保险金、医疗保险金、住房公积金、工会基础后，除去养老保险金、失业保险金、医疗保险金、住房公积金、工会费，对剩下的余额征收个税。余额在费，对剩下的余额征收个税。余额在500500元内的征收余额的元内的征收余额的5%5%，余额为，余额为

2、50020005002000元的征收余额的元的征收余额的10%10%，余额为，余额为2000500020005000元的征收余额的元的征收余额的15%15%征征收个税的最高税率为收个税的最高税率为45%45%（余额在（余额在1010万元以上的）。如果用万元以上的）。如果用excelexcel的函数，的函数，则要进行多层则要进行多层ifif嵌套，稍有不注意，可能造成计算上差错，我们建立自己嵌套，稍有不注意，可能造成计算上差错，我们建立自己的函数来解决这个问题。的函数来解决这个问题。 转转excel函数之旅函数之旅 自定义公式的作成自定义公式的作成首先进入首先进入excelexcel，在，在 工具

3、工具宏宏visul basicvisul basic编辑器编辑器 （也可按组合（也可按组合键键alt+f11alt+f11），在），在“visul basicvisul basic编辑器编辑器”中选择中选择 插入插入添加模块添加模块 ，在代码窗口输入下列函数：在代码窗口输入下列函数：function tax(income as single) as single select case income case 0 to 800c excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 自定义公式的作成自定义公式的作成 tax = 0 case 800.01 to 1300 tax

4、= (income - 800) * 0.05 case 1300.01 to 2800 tax = (income - 1300) * 0.1 + 25 case 2800.01 to 5800 tax = (income - 2800) * 0.15 + 175 case 5800.01 to 20800 c excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 自定义公式的作成自定义公式的作成 tax = (income - 5800) * 0.2 + 625 case 20800.01 to 40800 tax = (income - 20800) * 0.25 + 36

5、25 case 40800.01 to 60800 tax = (income - 40800) * 0.3 + 8625 case 60800.01 to 80800 tax = (income - 60800) * 0.35 + 14625 case 80800.01 to 100800 c excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 自定义公式的作成自定义公式的作成 tax = (income - 80800) * 0.4 + 21625 case is = 100800 tax = (income - 100800) * 0.45 + 29625 case is

6、 0 msgbox “你的工资你的工资 ” & income & “ 输入有误输入有误” end select end functionc excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 自定义公式的作成自定义公式的作成continueexcelexcel中函数都有一个说明，帮助使用，我们也要给这个函数添加一个中函数都有一个说明，帮助使用，我们也要给这个函数添加一个说明。在工具栏中选择说明。在工具栏中选择“对象浏览器对象浏览器”( (如图如图1)1)，选择我们所做，选择我们所做taxtax模块，模块，在其在其 右键右键属性属性 中添加关于对这个函数的描述，这个描述将出现在中

7、添加关于对这个函数的描述，这个描述将出现在excelexcel中关于函数的说明中，如果你要对软件保密的话，在中关于函数的说明中，如果你要对软件保密的话，在“模块模块”上上按右键，按右键，vbaprojectvbaproject属性属性保护中设置密码保护中设置密码 ，这样别人就看不到你，这样别人就看不到你的源程序了。的源程序了。 图图1 1 添加自制函数说明添加自制函数说明 excel函数 作成：知秋导购返利网wme euc教育资料 excel函数 作成：裘进,郁林转转excel函数之旅函数之旅 自定义公式的作成自定义公式的作成 end这时，退出，回到这时，退出，回到excelexcel界面，将

8、这个文件另存为：类型为界面，将这个文件另存为：类型为“microsoft microsoft excel excel 加载宏加载宏”，在，在excel 2000excel 2000中，它会自动更改保存位置为中，它会自动更改保存位置为c:windowsapplication datamicrosoftaddins(c:windowsapplication datamicrosoftaddins(系统装在系统装在c:windows)c:windows)，当然，你也可以把这个文件，当然，你也可以把这个文件tax.xlatax.xla，直接复制到，直接复制到officelibraryofficelib

9、rary（officeoffice的安装路径下），而在的安装路径下），而在excel 97excel 97中只能放在后中只能放在后一个位置。使用函数很简单，点击一个位置。使用函数很简单，点击 工具工具加载宏加载宏 ，在你创建的，在你创建的taxtax前打个勾，在单元格直接输入前打个勾，在单元格直接输入“=tax()”=tax()”，是不是像，是不是像microsoft officemicrosoft office提供的函数一样（如图提供的函数一样（如图2 2），）， 图图2 2 使用自制函数使用自制函数 转转excel函数之旅函数之旅 查阅或引用函数查阅或引用函数的使用的使用 excel函数

10、作成：知秋导购返利网转转excel函数之旅函数之旅 函数函数lookup 的使用的使用函数 lookup 有两种语法形式：向量和数组。向量:向量为只包含一行或一列的区域。函数 lookup 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。如果需要指定包含待查找数值的区域，则可以使用函数 lookup 的这种形式。函数 lookup 的另一种形式为自动在第一列或第一行中查找数值。lookup_value 为函数 lookup 在第一个向量中所要查找的数值。lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。lookup_

11、vector 为只包含一行或一列的区域。lookup_vector 的数值可以为文本、数字或逻辑值。 c 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 中的最小值 c excel函数 作成：

12、知秋导购返利网wme euc教育资料 excel函数 作成：裘进,郁林转转excel函数之旅函数之旅 函数函数lookup 的使用的使用数组:函数 lookup 的数组形式是在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。如果需要查找的数值在数组的第一行或第一列，就可以使用函数 lookup 的这种形式。当需要指定列或行的位置时，可以使用函数 lookup 的其他形式。lookuplookup(lookup_valuelookup_value,arrayarray)lookup_value 为函数 lookup 在数组中所要查找的数值。lookup_valu

13、e 可以为数字、文本、逻辑值或包含数值的名称或引用。 array 为包含文本、数字或逻辑值的单元格区域，它的值用于与 lookup_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 和函数

14、 vlookup 非常相似。不同之处在于函数 hlookup 在第一行查找 lookup_value，函数 vlookup 在第一列查找，而函数 lookup 则按照数组的维数查找。 c excel函数 作成：知秋导购返利网wme euc教育资料 excel函数 作成：裘进,郁林转转excel函数之旅函数之旅 函数函数vlookup 的使用的使用在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数 vlookup 代替函数 hlookup。vlookup(lookup_value,table_array,col_index_n

15、um,range_lookup)lookup_value 为需要在数据表第一列中查找的数值。lookup_value 可以为数值、引用或文字串。table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或数据清单。 col_index_num 为 table_array 中待返回的匹配值的列序号。range_lookup 为一逻辑值，指明函数 vlookup 返回时是精确匹配还是近似匹配。如果为 true 或省略，则返回近似匹配值，如果 range_value 为 false，函数 vlookup 将返回精确匹配值。如果找不到，则返回错误值 #n/a。 c

16、ontinuewme euc教育资料 excel函数 作成：裘进,郁林转转excel函数之旅函数之旅 函数函数vlookup 的使用的使用说明说明 如果函数 vlookup 找不到 lookup_value，且 range_lookup 为 true，则使用小于等于 lookup_value 的最大值。如果 lookup_value 小于 table_array 第一列中的最小数值，函数 vlookup 返回错误值 #n/a。如果函数 vlookup 找不到 lookup_value 且 range_lookup 为 false，函数 vlookup 返回错误值 #n/a。 continue转

17、转excel函数之旅函数之旅 函数函数vlookup 的使用的使用示例示例:在上述工作表中，区域 a4:c12 的名称为 range：vlookup(2,range,2,false) 等于 #n/avlookup(.746,range,3,false) 等于 200vlookup(0.1,range,2,true) 等于 #n/avlookup(2,range,2,true) 等于 1.71 c excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 函数函数hlookup 的使用的使用hlookup(lookup_value,table_array,row_index_num,r

18、ange_lookup).lookup_value 为需要在数据表第一行中进行查找的数值。.lookup_value 可以为数值、引用或文字串。.table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。 1.table_array 的第一行的数值可以为文本、数字或逻辑值。 2.如果 range_lookup 为 true，则 table_array 的第一行的数值必须按 升序排列：.-2、-1、0、1、2、a-z、false、true；否则，函数 hlookup 将不能给出正确的数值。如果 range_lookup 为 false，则 table_array 不必

19、进行排序。 3.不区分文本的大小写。 c excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 函数函数hlookup 的使用的使用.row_index_num 为 table_array 中待返回的匹配值的行序号。 row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推。如果 row_index_num 小于 1，函数 hlookup 返回错误值 #value!；如果 row_index_num 大于 table-array 的行数，函数 hlookup 返

20、回错误值 #ref!。.range_lookup 为一逻辑值，指明函数 hlookup 查找时是精确匹配，还是近似匹配。如果为 true 或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。如果 range_value 为 false，函数 hlookup 将查找精确匹配值，如果找不到，则返回错误值 #n/a!。 c excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 函数函数hlookup 的使用的使用示例示例假设有一张关于汽车零配件库存清单的工作表：a1:a4 的内容为 axles、4、5、6。b1:b4 的内容为 b

21、earings、4、7、8。c1:c4 的内容为 bolts、9、10、11。hlookup(axles, a1:c4,2,true) 等于 4hlookup(bearings,a1:c4,3,false) 等于 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 c excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 函数函数index的使用的使用index 函数有两种语法形式

22、：数组和引用。数组形式通常返回数值或数值数组，引用形式通常返回引用。当函数 index 的第一个参数为数组常数时，使用数组形式。数组形式index(array,row_num,column_num)array 为单元格区域或数组常数。有关数组常数中的数值的详细信息，请单击 。row_num 数组中某行的行序号，函数从该行返回数值。如果省略 row_num，则必须有 column_num。column_num 数组中某列的列序号，函数从该列返回数值。如果省略 column_num，则必须有 row_num。 c excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 函数函数inde

23、x的使用的使用说明说明row_num 和 column_num 必须指向 array 中的某一单元格，否则，函数 index 返回错误值 #ref!。示例示例index(1,2;3,4,2,2) 等于 4如果作为数组公式输入，则：index(1,2;3,4,0,2) 等于 2;4如果单元格 b5:b6 分别包含文本“苹果”和“香蕉”，而单元格 c5:c6 分别包含文本“柠檬”和“鸭梨”，则：index(b5:c6,2,2) 等于“鸭梨”index(b5:c6,2,1) 等于“香蕉” c excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 函数函数index的使用的使用引用形式i

24、ndex(reference,row_num,column_num,area_num) .reference 对一个或多个单元格区域的引用。 .row_num 引用中某行的行序号，函数从该行返回一个引用。 .column_num 引用中某列的列序号，函数从该列返回一个引用。 .area_num 选择引用中的一个区域，并返回该区域中 row_num 和 column_num 的交叉区域。选中或输入的第一个区域序号为 1，第二个为 2，以此类推。如果省略 area_num，函数 index 使用区域 1。 如果将 row_num 或 column_num 设置为 0，函数 index 分别返回对整个列或行的引用。 c excel函数 作成：知秋导购返利网转转excel函数之旅函数之旅 函数函数index的使用的使用示例示例在下面的工作表中，区域 a2:c6 的名称为 fruit，而区域 a1:c11 的名称为 stock。index(fruit,2,3) 等于引用 c3， 内容为 38index(a1:c6,a8:c11),2,2,2) 等于引用 b9，内容为 $3.55sum(i