令人叫绝的EXCEL函数功能（V2006.5.15整理版）.xls

目 录 序序号号函函数数名名称称分分类类 1DCOUNT 2DGET 3自定义格式 4拆分姓名 5公式中的括号 6百分率 7文件名函数 8年龄计算 9突破SUM函数参数的限制 10ABS 11ACCRINT 12ACOS 13ACOSH 14ADDRESS 15AMORDEGRC 16ACCRINTM 17AMORLINC 18AND 19CELL 20CEILING 21COUNTA 22Excel链接 23HLOOKUP 24CHOOSE 25AVERAGE 26BIN2DEC 27CHAR 28CLEAN 29CODE 30COMBIN 31CONCATENATE 32CONVERT 33COUNT 34COUNTBLANK 35COUNTIF 36VLOOKUP 37VLOOKUP 38DATEDIF 39DATUE 40DAVERAGE 41DAY 42DAYS360 43DMAX 44DMIN 45DOLLAR 或 RMB 46DSUM 47OFFSET 48高级筛选示例1 49高级筛选示例2 50高级筛选示例3 51高级筛选示例4 52高级筛选示例5 53高级筛选示例6 54- 55- 56Vlookup示例 57IndexMatch示例 58CountCells 59 自制推算性格程序vlookup 模糊查找的力量） 60VLOOKUP示例2 61VLOOKUP示例3 62- 63- 64- 65- 66- 67- 68- 69- 70- 71- 72- 73- 74- 75- 76- 77- 78- 79- 80- 81- 82- 83- 84- 85- 86- 87- 88- 89- 90- 91- 92- 93- 94- 95- 96- 97- 98- 99- 100- 备备注注 绝对值函数运用 文本函数的组合运用 运算次序 CELL“filename“、宏表函数get.cell32或get.cell66 DATEDIF 函数 这个函数是用来计算一个数的绝对值，与正负数符号没有关系。 返回定期付息有价证券的应计利息。 返回数字的反余弦值。反余弦值是角度，它的余弦值为数字。返回的角度值以弧度 表示，范围是 0 到 pi。 返回 number 参数的反双曲余弦值。参数必须大于或等于 1。反双曲余弦值的双曲 余弦即为该函数的参数， 按照给定的行号和列标，建立文本类型的单元格地址。 返回每个结算期间的折旧值。该函数主要为法国会计系统提供。如果某项资产是在 该结算期的中期购入的，则按直线折旧法计算。该函数与函数 AMORLINC 相似，不 同之处在于该函数中用于计算的折旧系数取决于资产的寿命。 返回到期一次性付息有价证券的应计利息。 返回每个结算期间的折旧值，该函数为法国会计系统提供。如果某项资产是在结算 期间的中期购入的，则按 这个函数用于检验两个或更多（最多30个）条件，看它们是否都为TRUE. 返回某一引用区域的左上角单元格的格式、位置或内容等信息。 这个函数可以根据用户要求把一个数字向上舍入。 统计列表中含数字或文本的单元格个数 在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列 处的数值。当比较值位于数据表首列时，可以使用函数 VLOOKUP 代替函数 HLOOKUP 。在 VLOOKUP 中的 V 代表垂直。 可以使用 index_num 返回数值参数列表中的数值。使用函数 CHOOSE 可以基于索 引号返回多达 这个函数能够计算一组数字的平均数 这个函数将二进制数转换为十进制数。负数用二进制数补码表示 返回对应于数字代码的字符。函数 CHAR 可将其他类型计算机文件中的代码转换为 字符。 删除文本中不能打印的字符。 返回文本字符串中第一个字符的数字代码。返回的代码对应于计算机当前使用的字 符集。 计算从给定数目的对象集合中提取若干对象的组合数。利用函数 COMBIN 可以确定 一组对象所有可能的组合数。 将几个文本字符串合并为一个文本字符串。 将数字从一个度量系统转换到另一个度量系统中 返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计 算单元格区域或数字数组 该函数用于统计一个区域中空白单元格的个数 计算区域中满足给定条件的单元格的个数 这个函数在表格左侧的行标题中查找指定的内容 目 录 这个函数在表格左侧的行标题中查找指定的内容 这个函数可以计算两个日期间的间隔 返回 date_text 所表示的日期的序列号。函数 DATUE 的主要功能是将以文 本表示的 这个函数示例列出了产品的平均价格. 这个函数可以从一个完整的日期中提取出日 按照一年 360 天的算法（每个月以 30 天计，一年共计 12 个月），返回两日期 间相差的天数，这在一些会计计算 返回数据清单或数据库的列中满足指定条件的最大数值。 返回数据清单或数据库的列中满足指定条件的最小数字。 该函数依照货币格式将小数四舍五入到指定的位数并转换成文本。使用的格式为 ,0.00_;,0.00。 返回数据清单或数据库的列中满足指定条件的数字之和。 DCOUNT 产产品品瓦瓦数数寿寿命命 小小时时商商标标单单价价每每盒盒数数量量采采购购盒盒数数价价值值 白炽灯2003000上海4.504354.00 氖管1002000上海2.0015260.00 日光灯600.00 其它108000北京0.80256120.00 白炽灯801000上海0.2040324.00 日光灯100未知上海1.2510450.00 日光灯2003000上海2.501500.00 其它25未知北京0.5010315.00 白炽灯2003000北京5.003230.00 氖管1002000北京1.80205180.00 白炽灯100未知北京0.2510512.50 白炽灯10800上海0.2025210.00 白炽灯601000北京0.152500.00 白炽灯801000北京0.2030212.00 白炽灯1002000上海0.8010540.00 白炽灯401000上海0.10205 10.00 统计某一有使用寿命小时的产品的商标数 商标这两个单元格是数据范围. 商标名称 北京 有寿命小时的“北京”商标数是 5 DCOUNTB3I19,D3,E23E24 DCOUNTDCOUNT功功能能 返回数据库或数据清单的列中满足指定条件并且包含数字的单元格个数。 DCOUNTDCOUNT语语法法 DCOUNTdatabase,field,criteria Database 构成数据清单或数据库的单元格区域。数据库是包含一组相关数据的数据清单，其中包含 相关信息的行为记录，而包含数据的列为字段。数据清单的第一行包含着每一列的标志项。 Field 指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。Field 可以是文本， 即两端带引号的标志项，如示例2中的“使用年数”或“产量”；此外，Field 也可以是代表数据清单中数 据列位置的数字1 表示第一列，2 表示第二列，等等。 Criteria 为一组包含指定条件的单元格区域。可以为参数 criteria 指定任意区域，只要它至少包含一 个列标志和列标志下方用于设定条件的单元格。 参数 field 为可选项，如果省略，函数 DCOUNT 返回数据库中满足条件 criteria 的所有记录数。 DCOUNTDCOUNT格格式式 没有专门的格式 DCOUNTDCOUNT示示例例1 1 指指定定产产品品已已知知采采购购盒盒数数的的总总数数。。 产品采购盒数 白炽灯5 采购盒数为5的产品白炽灯的数量是3 DCOUNTB3I19,H3,E50F51 这是相同的计算，但是用采购盒数代替了单元格地址 3 DCOUNTB3I19,“采购盒数“,E50F51 产产品品白白炽炽灯灯具具体体指指定定瓦瓦数数为为100100的的总总数数是是 产品瓦数 白炽灯100 总数是 2 DCOUNTB3I19,“采购盒数“,E61F62 产产品品白白炽炽灯灯瓦瓦数数在在808080101000的数据行。 类型销售人员销售额 农产品程香宙1000 某某一一列列或或另另一一列列上上具具有有单单个个条条件件 若要找到满足一列条件或另一列条件的数据，请在条件区域的不同行中输入条件。例如，下面的条件区域 将显示所有在类型列中包含农产品、在销售人员列中包含程龙或销售额1000的行。 类型销售人员销售额 农产品 程龙 1000 两两列列上上具具有有两两组组条条件件之之一一 若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件。例如， 下面的条件区域将显示所有在销售人员列中包含程香宙且销售额3000的行，同时也显示 程龙销售商的销售额1500的行。 销售人员销售额 程香宙3000 程龙1500 一一列列有有两两组组以以上上条条件件 若要找到满足两组以上条件的行，请用相同的列标包括多列。例如，下面条件区域显示5000和 100],000;;; 102 [100],000;;; -54 [100],000;;; 程香宙 显显示示为为 1,500 元人民币 答复 1500 总额是1,500美元 123,457 12,345,679 1 123.00 仅显示正数 0.00 -0- 12 102 852377 659-6295 852377/659-6295 421-89-7322 社会福利号 421-89-7322 六月-95 六月 1, 1995 星期四 六月 1, 1995 星期四 Its 星期四 1 100.0 45 程香宙 234 2004 注册 号码 文本引述 123 正数 负数 零 文本 099 102 拆拆分分姓姓名名 当你需要拆分某个含有文本格式内容的单元格内文字时要用到这个公式 下面几个例子可以是用来提取姓名中一部分的通常方法。 需要指出的是这些文法一般只适应于老外的英文姓名名在前，姓在后，对于中文姓名，由于 书写习惯是姓在前且文字中间没有空格，所以不太适用。 这个公式是使用文本函数达到目的的 其技巧就在于姓名中间有空格分开 提提取取名名 姓名名 Alan JonesAlan LEFTC14,FIND“ “,C14,1 Bob SmithBob LEFTC15,FIND“ “,C15,1 Carol WilliamsCarol LEFTC16,FIND“ “,C16,1 提提取取姓姓 姓名姓 Alan JonesJones RIGHTC22,LENC22-FIND“ “,C22 Bob SmithSmith RIGHTC23,LENC23-FIND“ “,C23 Carol WilliamsWilliams RIGHTC24,LENC24-FIND“ “,C24 当当有有中中间间名名字字出出现现时时提提取取姓姓 这个公式不能用于姓名有两个以上单词的情况 如果有中间名，公式的套用将出现不正确的结果 解决这个问题的办法是用一个较长的公式 姓名姓 Alan David JonesJones Bob John SmithSmith Carol Susan Williams Williams RIGHTC37,LENC37-FIND““,SUBSTITUTEC37,“ “,““,LENC37-LENSUBSTITUTEC37,“ “,““ 提提取取中中间间名名 姓名中间名 Alan David JonesDavid Bob John SmithJohn Carol Susan Williams Susan LEFTRIGHTC45,LENC45-FIND“ “,C45,1,FIND“ “,RIGHTC45,LENC45-FIND“ “,C45,1,1 公公式式中中的的括括号号 有时你在公式中可能使用到括号才能保证计算按你需要的顺序进行 在进行加、减、乘、除混合运算时需要用括号分开 数学上的 * * 和/ / 比 和 - 优先。* 和 / 的运算将在 和 - 之前进行 . 例 1 答案不正确 10 20 2 50 C12C13*C14 你可能是想计算10 20 得到 30 然后30 * 2 得到 60 但是，因为*在Excel中首先计算 结果是 20 * 2 得到 40 然后10 40 得到错误的结果50 例 2 正确答案. 10 20 2 60 C27C28*C29 在1020两边放上括号后 Excel 首先运行它并 得到结果 30 然后用 30 乘以 2 得到 60 这都是你想要的结果。 百百分分率率 excelhome chengxiang Excel并没有提供专门用于计算百分率的函数 但我们完全可以利用在学校学习的数学知识分类掌握下面这些技巧 已已知知百百分分率率求求一一个个数数 起始数120 百分率25 得数30 D8*D9 示示例例 1 一个公司将要给职员加薪 财务部门需要计算职员应该增加多少 职员工资级别不同增加的比例不同 级别增幅 A10 B15 C20 姓名级别原工资增加的工资 程香宙A10,0001,000 E23*LOOKUPD23,C18C20,D18D20 刘冰B20,0003,000 E24*LOOKUPD24,C18C20,D18D20 程龙C30,0006,000 E25*LOOKUPD25,C18C20,D18D20 程香峰B25,0003,750 E26*LOOKUPD26,C18C20,D18D20 程坤C32,0006,400 E27*LOOKUPD27,C18C20,D18D20 张凤玲A12,0001,200 E28*LOOKUPD28,C18C20,D18D20 已已知知百百分分率率求求一一个个增增加加了了的的数数 起始数120 百分率25 得数150 D33*D34D33 示示例例 2 一个公司将要给职员加薪 财务部门需要计算职员工资增加后是多少 职员工资级别不同增加的比例不同 级别增幅 A10 B15 C20 姓名级别原工资增加后工资 程香宙A10,00011,000 E48*LOOKUPD48,C18C20,D18D20E48 刘冰B20,00023,000 E49*LOOKUPD49,C18C20,D18D20E49 程龙C30,00036,000 E50*LOOKUPD50,C18C20,D18D20E50 程香峰B25,00028,750 E51*LOOKUPD51,C18C20,D18D20E51 程坤C32,00038,400 E52*LOOKUPD52,C18C20,D18D20E52 张凤玲A12,00013,200 E53*LOOKUPD53,C18C20,D18D20E53 计计算算百百分分率率 数字A120 数字B60 减幅50 D59/D58 你需要通过工具栏的按钮将结果格式化成形式。 示示例例 3 一个经理需要提交下年度财务预算 经理需要明确指出四个区域的需求 经理知道上一年每个区域的实际费用 依据上年消费，经理希望预算出下年度各个区域的消费情况。 上上一一年年数数字字 区域Q1Q2Q3Q4 东9,0002,0009,0007,000 南7,0004,0009,0005,000 西2,0008,0007,0003,000 北8,0009,0006,0005,000合计 合计26,00023,00031,00020,000100,000 去去年年四四个个区区域域占占总总消消费费百百分分率率 区域Q1Q2Q3Q4 东9297 G74/H78 南7495 G75/H78 西2873 G76/H78 北8965 G77/H78 合计26233120 G78/H78 下下年年度度预预算算150,000 判判断断下下年年度度预预算算分分配配 区域Q1Q2Q3Q4 东13,5003,00013,50010,500 G82*E88 南10,5006,00013,5007,500 G83*E88 西3,00012,00010,5004,500 G84*E88 北12,00013,5009,0007,500合计 合计39,00034,50046,50030,000150,000 已已知知现现有有数数和和增增加加的的百百分分率率求求起起始始数数 现有数150 增加了25 起始数120 D100/100D101 示示例例 4 一旅游者在某处旅游支出了一笔引用并一张含消费税的收据 他需要知道实际开支和税收开支明细 然而收据上只显示开支总数 他通过下表把开支明细计算了出来 税率17.50 物品总数实际开支税收 汽油1091 D113-D113/100D110 旅馆23520035 汽油11810018 D115/100D110 文文件件名名函函数数 当你需要在工作表的一个单元格中插入当前工作簿名称时 可以使用这个函数。 这时使用 CELL 函数, 显示如下. L\cl 培训\学习总结\令人叫绝的EXCEL函数功能\[令人叫绝的EXCEL函数功能（V2006.5.15整理版）.xls]目录 CELL“filename“ 问题是它带有驱动器盘符和文件的全部路径 你需要使用文本函数挑选出工作表或工作簿名称 选选择择路路径径 L\cl 培训\学习总结\令人叫绝的EXCEL函数功能\ MIDCELL“filename“,1,FIND“[“,CELL“filename“-1 选选择择工工作作簿簿名名称称 令人叫绝的EXCEL函数功能（V2006.5.15整理版）.xls MIDCELL“filename“,FIND“[“,CELL“filename“1,FIND“]“,CELL“filename“-FIND“[“,CELL“filename“-1 选选择择工工作作表表名名称称 目录 MIDCELL“filename“,FIND“]“,CELL“filename“1,255 宏宏表表函函数数 1、get.cell66 定义一个名称，简写为mc,引用位置写入“get.cell66“然后在任意单元格输入mc,即可得到当 前工作簿名称。 令人叫绝的EXCEL函数功能（V2006.5.13-JSSY汇总版）.xls mc 2、get.cell62[或get.cell32] 定义一个名称，简写为mcb,引用位置写入“get.cell62或get.cell32,然后在任意单元格 输入mcb,即可得到当前工作簿及工作表名称。 [令人叫绝的EXCEL函数功能（V2006.5.13-JSSY汇总版）.xls]目录 mcb [令人叫绝的EXCEL函数功能（V2006.5.13-JSSY汇总版）.xls]目录 mcb2 年年龄龄计计算算 你可以根据一个人的生日计算他的年龄 这个计算要使用 DATEDIF 函数. DATEDIF不能在 Excel 5, 7 or 97中使用, 但是在excel 2000中可以. 不要奇怪微软公司没有告诉我们 出生日期 1-1月-60 生活的年46 DATEDIFC8,TODAY,“y“ 和月4 DATEDIFC8,TODAY,“ym“ 和日 14 DATEDIFC8,TODAY,“md“ 你可以把它们连接起来使用 年龄是 46 年, 4 月和 14 天 “年龄是 “,0“C0“ ,0_;[Red],0“C0-“ ,0.00_;,0.00“C2“ ,0.00_;[Red],0.00“C2-“ 0“P0“ 0.00“P2“ 0.00E00“S2“ / 或 /“G“ yy-m-d 或 yy-m-d hmm 或 dd-mm-yy“D4“ d-mmm-yy 或 dd-mmm-yy“D1“ d-mmm 或 dd-mmm“D2“ mmm-yy“D3“ dd-mm“D5“ hmm AM/PM“D7“ hmmss AM/PM“D6“ hmm“D9“ hmmss“D8“ 如果 CELL 公式中的 info_type 参数为“at”，而且以后又用自定义格式设置了单元格， 则必须重新计算工作表，以更新 CELL 公式。 示示例例1 1 下面的例子是使用 CELL 函数和其它函数结合提取文件名。 当前的文件名是 令人叫绝的EXCEL 函数功能（ V2006.5.15整理 版）.xls MIDCELL“filename“,FIND“[“,CELL“filename“1,FIND“]“,CELL“filename“-FIND“[“,CELL“filename“-1 说说明明 函数 CELL 用于与其他电子表格程序兼容。 示示例例2 2 数数据据 37685 TOTAL 公公式式说说明明（（结结果果）） 20 单元格 A20 的行号 20 CELL“row“,A20 G 第一个字符串的格式代码（D2,请参见 上面的信息） CELL“at“, B112 G 单元格 A3 的内容 TOTAL CELL“at“, B113 Info_type 为一个文本值，指定所需要的单元格信息的类型。下面列出 info_type 的可能值及相应的结果。 Reference 表示要获取其有关信息的单元格。如果忽略，则在 info_type 中所指定的信息将返回给最后更改的单元格。 下表描述 info_type 为“at”，以及引用为用内置数字格式设置的单元格时，函数 CELL 返回的文本值。 CEILING 数字向上舍入 2.13 CEILINGC4,1 1.52 CEILINGC5,1 1.92 CEILINGC6,1 2030 CEILINGC7,30 2530 CEILINGC8,30 4060 CEILINGC9,30 功功能能 这个函数可以根据用户要求把一个数字向上舍入。 将参数 Number 向上舍入（沿绝对值增大的方向）为最接近的 significance 的倍数。 例如，如果您不愿意使用像“分”这样的零钱，而所要购买的商品价格为 4.42， 可以用公式 CEILING4.42,0.1 将价格向上舍入为以“角”表示。 语语法法 CEILINGValueToRound,MultipleToRoundUpTo ValueToRound可以是单元格地址，也可以是个算式。 格格式式 没有固定的格式 示示例例1 下面的表格可用于度假公寓出租租金计算 它的核算单位是以一周计算 计算顾客付款时要用到 CEILING 要求天数 需要付款的天 数 函数将付款天数全部转化成了7 的倍数。 客户 137 CEILINGD28,7 客户 247 CEILINGD29,7 客户 31014 CEILINGD30,7 示示例例2 下面表格适用于一个建筑商要把建筑材料运输到工地 建筑商需要卡车去运输这些建筑材料 每种材料需要一定的卡车容量 表 1 根据卡车载货量计算出运输材料的最小卡车数量。 这个结果不是整数而建筑商有无法分割卡车。 表格 1 项目运输数量卡车载重量需要的卡车数 砖10003003.33 D45/E45 木头50006008.33 D46/E46 水泥20003505.71 D47/E47 表格2显示了如何使用 CEILING 函数计算出需要卡车的实际数量。 表格 2 项目运输数量卡车载重量需要的卡车数 砖10003004 CEILINGD54/E54,1 木头50006009 CEILINGD55/E55,1 水泥20003506 CEILINGD56/E56,1 示示例例 3 下面的表格适用于商店老板计算商品销售价格表 商店老板购买商品需要包装 包装费用要分担到单位商品 商店老板问题希望最后结果带有9分 表1显示如何计算单位商品分摊的实际费用 表 1 品名包装规格包装费平均分摊 插头11201.81818 D69/C69 插座718.252.60714 D70/C70 接头528.105.62000 D71/C71 电路板16281.75000 D72/C72 表2 显示了如何使用 CEILING 函数将单位费用向上舍入到最后99分 Table 2 品名包装规格包装费平均分摊向上舍入后 插头11201.818181.99 插座718.252.607142.99 接头528.105.620005.99 电路板16281.750001.99 INTE83CEILINGMODE83,1,0.99 说说明明 INTE83计算整数部分 MODE83,1计算小数部分 CEILINGMODE83,0.99 向上舍入小数部分到0.99 1、如果参数为非数值型，CEILING 返回错误值 VALUE。 2、无论数字符号如何，都按远离 0 的方向向上舍入。 3、如果数字已经为 Significance 的倍数，则不进行舍入。 4、如果 Number 和 Significance 符号不同，CEILING 返回错误值 NUM。 示示例例4 4 公式说明（结果） 3 将 2.5 向上舍 入到最接近的 1 的倍数 3 CEILING2.5, 1 -4 将 -2.5 向上舍 入到最接近的 -2 的倍数 -4 CEILING-2.5, -2 NUM 返回错误值， 因为 -2.5 和 2 的符号不同 NUM CEILING-2.5, 2 1.5 将 1.5 向上舍 入到最接近的 0.1 的倍数 1.5 CEILING1.5, 0.1 0.24 将 0.234 向上 舍入到最接近 的 0.01 的倍数 0.24 CEILING0.234, 0.01 CEILINGD54/E54,1 CEILINGD55/E55,1 CEILINGD56/E56,1 INTE83CEILINGMODE83,1,0.99 COUNTA 数据统计 1020303 COUNTAC4E4 100303 COUNTAC5E5 10-20303 COUNTAC6E6 101-1月-88303 COUNTAC7E7 102130303 COUNTAC8E8 100.856588699303 COUNTAC9E9 10302 COUNTAC10E10 10Hello303 COUNTAC11E11 10DIV/0303 COUNTAC12E12 功功能能 统计列表中含数字或文本的单元格个数 它忽略空值 语语法法 COUNTAValue1, value2, ... Value1, value2, ... 为所要计算的值，参数个数为 1 到 30 个。在这种情况下，参数值可以是任何类型， 它们可以包括空字符 ““，但不包括空白单元格。如果参数是数组或单元格引用，则数组或引用中的空 白单元格将被忽略。如果不需要统计逻辑值、文字或错误值，请使用函数 COUNT。 格格式式 没有专用的格式 示示例例1 以下是一个学校统计keep track of学生的考试情况的表格 及格学生评定等