Excel函数应用集.xls

1、V_06.1 2 作者：沈万波 工作：江苏.昆山 Email:技术交流！ 使用说明函数基础常用函数函数精华应用实例 函 数功 能 描 述 IF真假值判断，根据逻辑计算的真假值，返回不同结果; SUM数字求和； OFFSET以指定的引用为参照系，通过给定偏移量得到新的引用; MAXAMIN返回参数列表中的最大值 2SUM数字求和； 3OFFSET以指定的引用为参照系，通过给定偏移量得到新的引用; 4MAXAMIN返回参数列表中的最大值 5突破IF7次限制阿泉的三种突破IF7次限制方法； 6筛选后自动编号筛选后自动按1、2、编号； 7vlookup高级应用VLOOKUP高级应用； 8中国式的排名一

2、个排名函数； 9DCOUNT高级应用查找、计算的综合运用； 10文件名的函数一个很有趣的函数； 11突破函数参数限制突破SUM函数30参数的限制； 12提取函数新异用法中英文姓名拆分； 13三维引用自已看吧，我还是有些不明白； 14COUNTIF高级应用COUNTIF函数高级应用示例； 15FREQUENCY函数FREQUENCY函数的高级应用； 16取交叉值函数函数嵌套应用精华； 17精典的函数解析SUM、SUMIF、COUNTIF函数的使用； 18WLOOKUP应用高级查询函数WLOOKUP； 19规划求解规划求解得到最省料的方法？ 20数组特殊用途数组的一些另类用法； 应用实例 1高级筛

3、选应用发挥至Excel极点的筛选应用； 2加班申请单加班单自动生成打印，全部自动完成； 3价格走势图动态统计图； 4多维引用及其应用黄朝阳先生的详细解析； 5汉字转换拼音李勇(网名CpaLi3023)先生用VBA编写的实用函数； 6财务金额大小写金额大小写相互转换； 7数据库函数数据库函数应用两例； 8高级查询应用用多种方法实现查询功能,几例查询实例； 9随机与循环引用随机与循环引用； 10模拟运算表模拟运算表； 11文字与数字分离文字与数字分离函数用法； 12模糊查找示例满足指定条件模糊查找不重复值； 13身份证问题身份证相关； 14条件格式有趣的条件格式； 15数值积分数值积分的例子； 1

4、6数组公式骗你爱上数组公式； 17 18常见问题常见问题-解决； 19 20 Excel 应应用用实实例例主主页页 名名称称：加班申请单 作作者者：一位日本人制作 功功能能：按要求确定人员加班，自动生成打印，全部自动完成！ 名名称称：价格走势图 作作者者：来自Excel Home 技术论坛一位网友制作 功功能能：两个很值得参考的统计分析图，以动态的形势展示数据！ 名名称称：多维引用及其应用 作作者者：黄朝阳 功功能能：浅谈在引用函数中使用数组参数产生的多维引用及其应用 动态图表 加班申请单 产品价格走势图 多维引用及其应用 变动图表 名名称称：高级查询应用 作作者者：Apolloh(网名) 功

5、功能能：用多种方法实现查询功能 例例1 1 运用辅助列序列实现查询-普通公式 例例2 2 运用辅助列序列实现查询-有易失函数 例例3 3 运用辅助列序列实现查询-有易失函数.更少辅助列 例例4 4 模糊查询 运用辅助列序列实 现查询-普通公式 运用辅助列序列实 现查询-有易失函数.xls 运用辅助列序列实 现查询-有易失函数.更少辅助列 模糊查询 变动图表 函函数数综综合合应应用用 主主页页 1、自自动动记记录录输输入入的的时时间间 输入记录时间 =IF(C6=“,“,IF(D6“,D6,NOW() 12005年12月29日 10:57:19 AM 自定义格式:yyyy“年“m“月“d“日“

6、h:mm:ss AM/PM 22005-12-29- 10:57:40 自定义格式:yyyy“-“m“-“d“-“ h:mm:ss AA05/12/29 10:57 AM 自定义格式:yy“/“m“/“d“ h:mm AM/PM A12月29日 10:57 AM 自定义格式:m“月“d“日“ h:mm AM/PM 112月29日 10:59 输机 自定义格式:m“月“d“日“ h:mm “输机“ 2、多多单单元元格格合合并并 TEXT一二差异 销售人员姓名加一个“的“ 销售人员姓名 =TEXT(C4,“的“) 1.一个以它为基础确定偏移量的固定单元格地址. 2. 从固定单元格向上或向下查找多少

7、行. 3. 从固定单元格向左或向右查找多少列. 总计一月二月三月四月五月 1010400500600700 这个示例将作为固作为固定起始点并且没有行列偏移， =“这个示例将“40,50,60,70,80,2,1) 由于为了解决IF函数7层嵌套问题，CHENJUN版主提供一经典解法还原HLOOKUP函数的原型来求解。 解法思路与方法2一样，但对于使用常量数组法什么时候用呢？ 我们有时可能不希望单独建立辅助列（或辅助表），那么我们就可以通过定义常量数组的名称来实现，而且这样做的好处是当使用该名称的工作表复制到其他工作薄中时， 名称也会被自动复制（由于是常量数组，也不会涉及到外部引用）。 T类：J类

8、： 04005 150105 1.160126 2.170217 3.180318 100801008 4、巧用CHOOSE函数解法：=CHOOSE(MIN(4,ROUNDUP(D53/10,0)+1,5,5,6,7,8) 首先大家需要了解CHOOSE函数的用法，第1个参数需要注意：为10,20,30)*1) 该解法对于区间结果是等差数列递增的非常有用，公式也比较简洁。 思路是：通过区间逐项进行比较，满足条件后求和汇总进行求解。 T类：J类： 04005 150105 1.160126 2.170217 3.180318 100801008 6、条件统计法：=SMALL(B$80:B$84,C

9、OUNTIF(A$80:A$84,“55“) 示示例例2(2(函函数数与与公公式式宝宝典典中中的的示示例例) ) 数据统计 10030.333333333 =1/C86:C97 10030.333333333 =1/C86:C98 10030.333333333 20020.5 20020.5 30011 40020.5 40020.5 50040.25 50040.25 50040.25 50040.25 数据中不重复项目5 =SUM(D86:D97) 单个公式5 =SUM(1/COUNTIF(data3,data3) 示示例例3(3(函函数数与与公公式式宝宝典典中中的的示示例例) ) 资料

10、统计项目统计结果备注 aaAlpha2全部单元格(不区分大小写) =COUNTIF(data4,text) Alpha1全部单元格 (区分大小写) =SUM(IF(EXACT(data4,text),1) AAA 3 包含的单元格(不区分大小写 ) =COUNTIF(data4,“*“20;30;40，是一列四行（纵向）、包含四个元素的一维数组常量 10,20,30;40,50,60，是两行三列、包含六个元素的二维数组常量；二维数组常量需要行列等长 如果你觉得输数组常量麻烦（要区分【,】、【;】，文本要加【“】）， 可以先将数据输入单元格区域，然后在其它单元格输入【=】，用鼠标选定该区域后按F

11、9，显示的就是数组常量，然后复制到公式中 其实将以前使用的vlookup公式中第三个参数（区域不要太大）选定按F9，再回车，可以看到公式正常运作；此时已使用了数组常量 以下举例中的公式并不是数组公式，输入时不用Ctrl+Shift+Enter一齐按 搬一个由chenjun版主提供的计算个人所得税的例子： 应应税税所所得得税税率率速速算算扣扣除除数数 税税款款 650020375925 再搬一个前面的例子 汉汉字字字字符符拼拼音音首首字字母母 海H是否被公式中的数组常量吓一跳？不是一个个输的，按F9得到的； 汉汉字字字字符符拼拼音音首首字字母母 螺丝扣LSK对于此类固定的数组常量可以将其定义一个

12、名称，公式就简洁多了，还不占用单元格 公式中的【拼音】就是一个定义的名称，可以自【插入】【名称】【定义】中查看 从从右右向向左左查查询询 返返回回 我们知道，vlookup查找的列必须位于查找区域的最左列，有时会需要按右面的查找左边的值 以【表一】为例，要按【姓名】查询【工号】很容易；但反过来按【工号】查找【姓名】直接使用vlookup就不行了 此时可以使用的公式很多，如lookup、index+match、offset+match、indirect+match等 但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值，介绍这个用法的目的主要是学会将两个一维数组合并为一个二维数

13、组 工工号号姓姓名名 KT002李四光这个公式先由【IF(1,0,$C$9:$C$12,$B$9:$B$12)】计算出内存数组： “KT001“,“张三丰“;“KT002“,“李四光“;“KT003“,“王麻子“;“KT004“,“赵六儿“ 可以看到，在该内存数组中，【工号】位于【姓名】的左侧，Vlookup在该内存数组中查找 注意1,0是一行二列（横向）常量数组，后面的两个区域是多行一列（纵向），即两个数组的方向不同，这样才会生成两列多行数组 程程香香宙宙的的示示例例： 列1列2列3列4列5列6 元月1020304050 二月8090100110120 三月9769455177 输入要查找的

14、月份: 三月 需要挑选出的列: 4 结果是 : 45 =VLOOKUP(G11,C6:H8,G12,FALSE) 功功能能 这个函数在表格左侧的行标题中查找指定的内容 当找到时，它再挑选出该行对应的指定列的单元格内容。 语语法法 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。 Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或数据清单。 如果 range_lo

15、okup 为 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 第一

16、列中 的数值；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，函数 VL

17、OOKUP 将返回精确匹配值。如果找不到，则返回错误值 #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。 格格式式 没有专门的格式 示示例例 1 1 下面的下示例是以指定的名字和月份为基础查

18、找一个数值. =VLOOKUP()是用于沿第一列向下查找指定的名字. 难点是如何向右查找指定的月份. 解决这个难题的方法是使用=MATCH()函数. 函数=MATCH()通过使用找到名字的列表查找对应月份.并推算该月份在列表中的位置. 不巧的是，因为月份列表的搜索范围与查找数值的范围不等宽. 函数=MATCH()函数返回的数字比我们需要的数字少1, 因此在公式中用了+1进行调整. 函数=VLOOKUP()现在使用函数 =MATCH()得到的调整的数字n,在对应名字所在行向右查找到该行第n列对应单元格的输入内容 . 函数=VLOOKUP()中最后使用了 FALSE，因此左侧标题行不用排序。 元月

19、二月三月 程香宙108097 刘冰209069 程龙3010045 程坤4011051 chengxiang5012077 输入要查找的名称: 程龙 输入要查找的月份 : 三月 结果是: 45 =VLOOKUP(F60,C54:F58,MATCH(F61,D53:F53,0)+1,FALSE) 3=MATCH(F61,D53:F53,0) 示示例例 2 2 这个示例使用函数=VLOOKUP() 查找不同小车生产厂商不同配件的价值。 函数=VLOOKUP()向下扫描F列的标题行并查找对应的位于C列的配件名称. 找到配件后，函数VLOOKUP根据函数MATCH找到的位置查找到对应配件的价格。 公式

20、中使用了绝对引用，为的是确保公式复制移动时函数 =HLOOKUP() 和=MATCH()引用的范围不发生变化。 厂商配件价值查找表格 日本丰田火花塞50日本丰田福特 奔驰变速箱600变速箱500450 福特引擎1,200引擎10001200 奔驰方向盘275方向盘250350 福特火花塞70火花塞5070 福特刹车片290刹车片 300290 日本丰田变速箱500 福特引擎1,200 =VLOOKUP(C80,F74:I78,MATCH(B80,G73:I73,0)+1,FALSE) 示示例例 3 3 下面的示例是一个建材经销商提供的不同采购数量的折扣率 价格表中显示了砖,木材和玻璃的单价.

21、折扣表提供了不同产品不同采购数量的折扣率. 采购表是采购预算. 所有的预算结果显示在采购表中. 产品名称列表在C列. 单价是从价格表中获得的. FALSE选项表示产品名称在价格表中没有排序整理. 使用FALSE强迫搜索精确匹配. 如果没有找到，则函数显示错误. 折扣是从折扣表中获得的 如果采购数量与折扣表中某个值匹配，函数 =VLOOKUP将在折扣表中查找正确的匹配折扣. TRUE选项表示采购数量在折扣表中经过了升序排列整理. 使用TRUE允许模糊匹配.如果采购数量在折扣表中没有找到匹配的值，则它下面较小的值将被使用. 比如采购数量为125将向下与100匹配,并且使用100对应列的折扣率. 折

22、扣表 价格表砖砖木木材材 砖砖210%0% 木木材材11006%3% 玻玻璃璃33008%5% 采购表 项目采购数量单价折扣合计 砖12526%235 木材20013%194 玻璃150312%396 砖22526%423 木材5010%50 玻璃500315%1,275 公式为: 单价 E118: =VLOOKUP(C118,C106:D108,2,FALSE) 折扣 F118: =VLOOKUP(D118,F106:I108,MATCH(C118,G105:I105,0)+1,TRUE) 合计 G118: =(D118*E118)-(D118*E118*F118) 示示例例4 该示例使用

23、1 个大气压的空气值。 密度粘度温度 0.4573.55500 0.5253.25400 0.6162.93300 0.6752.75250 0.7462.57200 0.8352.38150 0.9462.17100 1.091.9550 1.291.710 公式说明（结果） 2.17 在 A 列中查找 1，并从相同 行的 B 列中返回值 (2.17) =VLOOKUP(1,B128:D136,2) 100 在 A 列中查找 1，并从相同 行的 C 列中返回值 (100) =VLOOKUP(1,B128:D136,3,TRUE) #N/A 在 A 列中查找 0.746。因为 A 列中没有精确

24、地匹配，所 以返回了一个错误值 (#N/A) =VLOOKUP(0.7,B128:D136,3,FALSE) #N/A 在 A 列中查找 0.1。因为 0.1 小于 A 列的最小值，所 以返回了一个错误值 (#N/A) =VLOOKUP(0.1,B128:D136,2,TRUE) 1.71 在 A 列中查找 2，并从相同 行的 B 列中返回值 (1.71) =VLOOKUP(2,B128:D136,2,TRUE) 返返回回 B50第四个字符码 32 可以为数值、引用或文本字符串。需要注意的是类型必须与table_array第一列的类型一致。 查找文本时，文本不区分大小写；可以使用通配符“*”、

25、“?”。 可以使用对区域或区域名称的引用、常数数组、计算后的内存数组。 该参数的第一列必须包含查找的内容，其它列包含需返回的内容；返回内容的列序号由下个参数指定。 如为1时，返回table_array第一列中的数值；为2，返回table_array第二列中的数值，以此类推。 如果col_index_num小于1，函数 VLOOKUP 返回错误值值 #VALUE!； 如果col_index_num大于table_array的列数，函数 VLOOKUP 返回错误值 #REF!。 如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；

26、近似匹配查询一般用于数值的查询，table_array的第一列必须按升序排列；否则不能返回正确的结果。 如果range_value为FALSE（或0），函数VLOOKUP将返回精确匹配值。 此时，table_array不必进行排序。如果找不到，则返回错误值#N/A；可isna检测错误后使用if判断去除错误信息。 在同一单元格按不同字段查询 返回多个符合条件的查询结果 有两个并列条件（不同字段）的查询 使用数组常量的查询 从右向左查询 处理查找错误 #VALUE!和 #REF!是由于col_index_num设置不当造成的，虽然可以使用ISERROR剔除，但建议不要这么做，因为这类错误需要纠正。

27、 有时col_index_num参数使用column()函数生成，当设置不当造成的错误用ISERROR剔除时，会与#N/A错误混淆，不利于公式查错。 如果vlookup的参数产生错误，vlooukp也返回相同错误，那不在本讨论范围内。 当确定引用正确、不应出现#N/A（即应该查找到值）时，检查是否存在空格或其它字符、格式是否一致 对于空格或其他不可见字符，可以使用len函数测试文本长度来确定，或使用code测试其ASCII码 该错误由于查询字符“张三丰 ”后含空格，可能看不出，但在精确匹配查找时造成不完全匹配 张三丰 李四光 王麻子 张三丰赵六儿 李四光KT001 王麻子KT002 赵六儿KT

28、003 KT001KT004 KT002 KT003 对于lookup_value中的空格，将其中的空格替换掉，或使用TRIM去除空格后查找 该错误是由于查询表表四中“李四军”后含空格，在精确匹配查找时不完全匹配 这个公式中的Table_array已不是直接引用单元格区域，而是引用对$B$87:$C$88运算trim后的内存数组 因此，这是一个数组公式，需要按Ctrl+Shift+Enter输入 如果数据区比较大，数组公式会很慢；不如直接使用替换将数据区的空格替换后使用普通公式。 该错误由于查询字符“李四军”后含不可见字符（非空格），使用trim、clean、替换空格都不能去除。 由网页复制、

29、其他程序转出的表格，经常会出现这类字符，CODE可以测试其并非空格 对于这类不可见字符，可以先复制该字符，然后替换该字符为空白 可以使用鼠标选定几个单元格，看状态栏的合计。有，则为数值，无，则为文本。 这两个错误都是由于格式不一致造成，一个是按数值查文本，一个是按文本查数值 对于此类错误，可以修改查找值、数据区的格式，使之统一；也可以在公式中处理 这个公式将数值100用20;30;40，是一列四行（纵向）、包含四个元素的一维数组常量 10,20,30;40,50,60，是两行三列、包含六个元素的二维数组常量；二维数组常量需要行列等长 可以先将数据输入单元格区域，然后在其它单元格输入【=】，用鼠

30、标选定该区域后按F9，显示的就是数组常量，然后复制到公式中 其实将以前使用的vlookup公式中第三个参数（区域不要太大）选定按F9，再回车，可以看到公式正常运作；此时已使用了数组常量 是否被公式中的数组常量吓一跳？不是一个个输的，按F9得到的； 对于此类固定的数组常量可以将其定义一个名称，公式就简洁多了，还不占用单元格 公式中的【拼音】就是一个定义的名称，可以自【插入】【名称】【定义】中查看 我们知道，vlookup查找的列必须位于查找区域的最左列，有时会需要按右面的查找左边的值 以【表一】为例，要按【姓名】查询【工号】很容易；但反过来按【工号】查找【姓名】直接使用vlookup就不行了 此

31、时可以使用的公式很多，如lookup、index+match、offset+match、indirect+match等 但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值，介绍这个用法的目的主要是学会将两个一维数组合并为一个二维数组 这个公式先由【IF(1,0,$C$9:$C$12,$B$9:$B$12)】计算出内存数组： “KT001“,“张三丰“;“KT002“,“李四光“;“KT003“,“王麻子“;“KT004“,“赵六儿“ 可以看到，在该内存数组中，【工号】位于【姓名】的左侧，Vlookup在该内存数组中查找 注意1,0是一行二列（横向）常量数组，后面的两个区域是

32、多行一列（纵向），即两个数组的方向不同，这样才会生成两列多行数组 =VLOOKUP(G11,C6:H8,G12,FALSE) Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。 Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或数据清单。 如果 range_lookup 为 TRUE，则 table_array 的第一列中的数值必须按升序排列：、-2、-1、0、1、2、-Z、FALSE 、TRUE；否则，函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 F

33、ALSE，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 返回时是

34、精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配 值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。 如果函数 VLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE，则使用小于等于 lookup_value 的最大值。 如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE，函数 VLOOKUP 返回错误值 #N/A。 函数=VLOOK

35、UP()现在使用函数 =MATCH()得到的调整的数字n,在对应名字所在行向右查找到该行第n列对应单元格的输入内容 . 奔驰 600 800 275 45 310 玻玻璃璃 0% 12% 15% =VLOOKUP(F60,C54:F58,MATCH(F61,D53:F53,0)+1,FALSE) 公式中使用了绝对引用，为的是确保公式复制移动时函数 =HLOOKUP() 和=MATCH()引用的范围不发生变化。 折扣表 高高级级筛筛选选示示例例 主主页页 条件区域日日期期日日期期顾顾客客产产品品 数据区域=1/11/02 筛选高级筛选. 3. 选择复制到其它位置 4. 点击数据区域 5.选择Sh

36、eet 1并选中数据区域 . 6. 点击条件区域框 . 7. 选择条件区域 8. 点击复制到框. 9. 在sheet2工作表中选择任一单元格作为输出数据的起始位置 . 10. 点击确定 高级筛选示例 自动筛选 总总计计 =100 CONVERT主主页页 需要转换的数量原单位转换后的单位 转换后的数量 1incm2.54 =CONVERT(C4,D4,E4) 1ftm0.3048 =CONVERT(C5,D5,E5) 1ydm0.9144 =CONVERT(C6,D6,E6) 1yrday365.25 =CONVERT(C8,D8,E8) 1dayhr24 =CONVERT(C9,D9,E9)

37、1.5hrmn90 =CONVERT(C10,D10,E10) 0.5mnsec30 =CONVERT(C11,D11,E11) 功功能能 将数字从一个度量系统转换到另一个度量系统中 如果该函数不可用，并返回错误值 #NAME?，请安装并加载“分析工具库” 函数 CONVERT 可以将一个以“英里”为单位的距离表转换成一个以“公里”为单位的距离表。 语语法法 CONVERT(number,from_unit,to_unit) Number 以 from_units 为单位的需要进行转换的数值,From_unit 数值 number 的单位。 To_unit 为结果的单位。函数 CONVERT

38、接受下面的文本值（引号中）作为 from_unit 和 to_unit。 格格式式 没有特殊的格式 示示例例1 下面的例子是进出口公司将货物重量由 以前老的英制单位转换成现行的制单位。 磅盎司公斤 重量532.353010419 =CONVERT(D28,“lbm“,“kg“)+CONVERT(E28,“ozm“,“kg“) 英尺英寸米 高1263.81 长832.5146 宽521.5748 =CONVERT(D34,“ft“,“m“)+CONVERT(E34,“in“,“m“) 缩缩写写 下面的缩写都是合理的。 重重量量和和质质量量 From_unit 或 to_unit Distance

39、 克g米m 公斤kg法定英里mi 斯勒格sg海里Nmi 磅（常衡制）lbm英寸in U（原子质量单位）u英尺ft 盎司（常衡制）ozm码yd 埃ang 日日期期和和时时间间 皮卡（1/72 英 寸） Pica 年yr 日day压压强强 小时hr帕斯卡Pa 分钟mn大气压atm 秒sec毫米汞柱mmHg 温温度度液液体体度度量量 摄氏度C茶匙tsp 华氏度F汤匙tbs 开尔文度K液量盎司oz 杯cup 力力U.S. 品脱pt 牛顿N夸脱qt 达因dyn加仑gal 磅力lbf升l 能能量量乘乘幂幂 焦耳J马力HP 尔格e瓦特W 热力学卡c IT 卡cal磁磁 电子伏eV特斯拉T 马力-小时HPh高

40、斯ga 瓦特-小时Wh 英尺磅flb BTUBTU 下列缩写的单位前缀可以加在任何的公制单位 from_unit 或 to_unit 之前。 前缀乘子缩写前缀乘子缩写 exa1.00E+18Edeci1.00E-01 d peta1.00E+15Pcenti1.00E-02 c tera1.00E+12Tmilli1.00E-03 m giga1.00E+09Gmicro1.00E-06 u mega1.00E+06Mnano1.00E-09 n kilo1.00E+03kpico1.00E-12 p hecto1.00E+02hfemto1.00E-15 f dekao1.00E+01eat

41、to1.00E-18 a 说说明明 如果输入数据的拼写有误，函数 CONVERT 返回错误值 #VALUE!。 如果单位不存在，函数 CONVERT 返回错误值 #N/A。 如果单位不支持缩写的单位前缀，函数 CONVERT 返回错误值 #N/A。 如果单位在不同的组中，函数 CONVERT 返回错误值 #N/A。 单位名称和前缀要区分大小写。 示示例例2 公公式式说说明明（结结果果） 0.45359237将 1 磅转换为千克 (0.453592) =CONVERT(1, “lbm“, “kg“) 20将 68 华氏度转换为摄氏度 (20) =CONVERT(68, “F“, “C“) #N/

42、A由于数据类型不同，因此返回错误值 (#N/A) =CONVERT(2.5, “ft“, “sec“) 9.290304将 100 平方英尺转换为平方米 (9.290304)。 =CONVERT(CONVERT(100,“ft“,“m“),“ft“,“m“) =CONVERT(CONVERT(100,“ft“,“m“),“ft“,“m“) COMBIN主主页页 总字符数组合的字符数可组合数 426 =COMBIN(C4,D4) 434 =COMBIN(C5,D5) 262325 =COMBIN(C6,D6) 功功能能 计算从给定数目的对象集合中提取若干对象的组合数。利用函数 COMBIN 可以

43、确定一组对象所有可能的组合数。 不论其内部顺序，对象组合是对象整体的任意集合或子集, 如 AB 和 BA是同一组合. 语语法法 COMBIN(number,number_chosen) Number 为对象的总数量。Number chosen 为每一组合中对象的数量 格格式式 没有一定格式 示示例例 1 1 本示例用于计算在A、B、C、D四个字符中每次两个可能组成的组合数 总字符数组合的字符数可组合数 426 =COMBIN(C25,D25) 检验!四个字母: ABCD 第1对AB 第2对AC 第3对AD 第4对BC 第5对BD 第6对CD 示示例例 2 2 装饰需要不同的颜色方案 现有五种颜

44、色，每个方案只需要三种颜色 可以组成多少方案？ 可用颜色每个组合需要颜色 决计组合方法 5310 =COMBIN(C41,D41) 颜色 红 绿 蓝 黄 黑 方案 1方案 2方案 3方案 4方案 5 红红红红红 绿绿绿蓝蓝 蓝黄黑黄黑 方案 6方案 7方案 8方案 9 绿绿绿蓝 蓝蓝黄黄 黄黑黑黑 说说明明 数字参数截尾取整。 如果参数为非数值型，则函数 COMBIN 返回错误值 #VALUE!。 如果 number 100#,000; 99099 100#,000; 102102 100#,000; -54 100#,000; 程香宙 DOLLAR 或或 RMB 原始数据转换成文本后 10$

45、10.00 =DOLLAR(C4) 10$10 =DOLLAR(C5,0) 10￥10.0 =RMB(C6,1) 10￥10.00 =RMB(C7,2) 10.25￥10.25 =RMB(C8) 10.25$10 =DOLLAR(C9,0) 10.25$10.3 =DOLLAR(C10,1) 10.25￥10.25 =RMB(C11,2) 功功能能 该函数依照货币格式将小数四舍五入到指定的位数并转换成文本。使用的格式为 ($#,#0.00_);($#,#0.00)。 本函数可将数字转换为文本格式，并应用货币符号。函数的名称及其应用的货币符号取决于您的语言设置。 语语法法 DOLLARDOLLA

46、R 或或 RMBRMB(number,decimals) Number 为数字、包含数字的单元格引用，或是计算结果为数字的公式。 Decimals 为十进制数的小数位数。如果 Decimals 为负数，则参数 number 从小数点往左按相应位数取整。 如果省略 Decimals，则假设其值为 2。 说说明明 使用“格格式式”菜单中的“单单元元格格”命令来设置包含数字的单元格的格式与使用 DOLLAR 函数直接设置数字的格式 之间的区别在于：DOLLAR 函数将结果转换为文本，而使用“单元格”命令设置格式的数字仍为数字。但可以 继续在公式中使用由 DOLLAR 函数设置了格式的数字，因为 Mi

47、crosoft Excel 在计算公式时会将以文本值输 入的数字转换为数字。 示示例例 数据 1234.567 -1234.567 -0.123 99.888 公式说明（结果） $1,234.57以货币格式及小数点右边 2 位数字的形式显示第一个数（$1,234.57 或 ￥1,234.57） ￥1,200以货币格式及小数点左边 2 位数字的形式显示第一个数（$1,200 或 ￥1,200） ($1,200)以货币格式及小数点左边 2 位数字的形式显示第二个数（($1,200) 或 (￥1,200)） ($0.1230)以货币格式及小数点右边 4 位数字的形式显示第三个数（($0.1230)

48、或 (￥0.1230)） ￥99.89以货币格式及小数点右边 2 位数字的形式显示第四个数（$99.89 或 ￥99.89） Reference 表示要获取其有关信息的单元格。如果忽略，则在 info_type 中所指定的信息将返回给最后更改的单元格。 ACCRINT 主主页页 功功能能 返回定期付息有价证券的应计利息。 如果该函数不可用，并返回错误值 #NAME?，请安装并加载“分析工具库”加载宏。 语语法法 ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis) 要点 应使用 DATE 函数来输入日期，或者将日期作

49、为其他公式或函数的结果输入。例如， 使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本的形式输入，则会出现问题。 Issue 为有价证券的发行日。 First_interest 是证券的起息日。 Settlement 是证券的成交日。即在发行日之后，证券卖给购买者的日期。 Rate 为有价证券的年息票利率。 Par 为有价证券的票面价值，如果省略 par，函数 ACCRINT 视 par 为 $1000。 Frequency 为年付息次数，如果按年支付，frequency = 1；按半年期支付，frequency = 2；按季支付，frequency = 4。 Basis 日计数基准类型。 Basis 日计数基准 0 或省略 US (NASD) 30/360 1 实际天数/实际天数 2 实际天数/360 3 实际天数/365 4 欧洲 30/360 说说明明 Microsoft Excel 可将日期存储为可用于计算的序列数。默认情况下，1900 年 1 月 1 日