Excel函数应用顶级技巧(不适合初学者)@旭川.xls

偶然间在一处僻静之 地发现此表 后惊许 久 技至如此 后何 求 初学者慎读 循 序渐进才是正道 较 适合中级进阶研究 风度 V 06 1 4 作者 沈万波 工作 江苏 昆山 Email 技术交流 使用说明函数基础常用函数函数精华应用实例 函 数功 能 描 述 高级筛选应用发挥至Excel极点的筛选应用 加班申请单加班单自动生成打印 全部自动完成 价格走势图动态统计图 多维引用及其应用黄朝阳先生的详细解析 汉字转换拼音李勇 网名CpaLi3023 先生用VBA编写的实用函数 财务金额大小写金额大小写相互转换 数据库函数数据库函数应用两例 高级查询应用用多种方法实现查询功能 几例查询实例 随机与循环引用随机与循环引用 模拟运算表模拟运算表 文字与数字分离文字与数字分离函数用法 模糊查找示例满足指定条件模糊查找不重复值 身份证问题身份证相关 条件格式有趣的条件格式 数值积分数值积分的例子 数组公式骗你爱上数组公式 常见问题常见问题 解决 函数基础 1Excel基础知识 1Excel的 论种种限制 2Excel基础知识 2EXCEL的六大 条件 功能 3Excel基础知识 3定义名称的妙处 4Excel基础知识 4微软的Excel函数使用帮助 5度量衡单位知识国际标准的度量衡单位 6关于逻辑实际中逻辑的应用 7电脑字符代码1 255数字各自对应转换的符号 8单元格格式单元格的所有格式 9括号的使用使用括号将改变Excel的计算顺序 10GET CELL参数GET CELL参数 类型值 大全 11 12下面是常用函数 13百分率 Excel并没有提供专门用于计算百分率的函数 14关于计息的函数计算利息 15字符计算计算中英文字符的函数 16超级链接本合集用得最多的一个函数 17日期函数汇集与日期相关的函数 18查找类函数按设定值查找 返回符合要求的数据 19 20 常用函数 1IF真假值判断 根据逻辑计算的真假值 返回不同结果 2SUM数字求和 3OFFSET以指定的引用为参照系 通过给定偏移量得到新的引用 4MAXA MIN返回参数列表中的最大值 5突破IF7次限制阿泉的三种突破IF7次限制方法 6筛选后自动编号筛选后自动按1 2 编号 7vlookup高级应用VLOOKUP高级应用 8中国式的排名一个排名函数 9DCOUNT高级应用查找 计算的综合运用 10文件名的函数一个很有趣的函数 11突破函数参数限制突破SUM函数30参数的限制 12提取函数新异用法中英文姓名拆分 13三维引用自已看吧 我还是有些不明白 14COUNTIF高级应用COUNTIF函数高级应用示例 15FREQUENCY函数FREQUENCY函数的高级应用 16取交叉值函数函数嵌套应用精华 17精典的函数解析SUM SUMIF COUNTIF函数的使用 18WLOOKUP应用高级查询函数WLOOKUP 19规划求解规划求解得到最省料的方法 20数组特殊用途数组的一些另类用法 应用实例 1高级筛选应用发挥至Excel极点的筛选应用 2加班申请单加班单自动生成打印 全部自动完成 3价格走势图动态统计图 4多维引用及其应用黄朝阳先生的详细解析 5汉字转换拼音李勇 网名CpaLi3023 先生用VBA编写的实用函数 6财务金额大小写金额大小写相互转换 7数据库函数数据库函数应用两例 8高级查询应用用多种方法实现查询功能 几例查询实例 9随机与循环引用随机与循环引用 10模拟运算表模拟运算表 11文字与数字分离文字与数字分离函数用法 12模糊查找示例满足指定条件模糊查找不重复值 13身份证问题身份证相关 14条件格式有趣的条件格式 15数值积分数值积分的例子 16数组公式骗你爱上数组公式 17 18常见问题常见问题 解决 19 20 Excel 应应用用实实例例主主页页 名名称称 加班申请单 作作者者 一位日本人制作 功功能能 按要求确定人员加班 自动生成打印 全部自动完成 名名称称 价格走势图 作作者者 来自Excel Home 技术论坛一位网友制作 功功能能 两个很值得参考的统计分析图 以动态的形势展示数据 名名称称 多维引用及其应用 作作者者 黄朝阳 功功能能 浅谈在引用函数中使用数组参数产生的多维引用及其应用 动态图表 加班申请单 产品价格走势图 多维引用及其应用 变动图表 名名称称 高级查询应用 作作者者 Apolloh 网名 功功能能 用多种方法实现查询功能 例例1 1 运用辅助列序列实现查询 普通公式 例例2 2 运用辅助列序列实现查询 有易失函数 例例3 3 运用辅助列序列实现查询 有易失函数 更少辅助列 例例4 4 模糊查询 运用辅助列序列实 现查询 普通公式 运用辅助列序列实 现查询 有易失函数 xls 运用辅助列序列实 现查询 有易失函数 更少辅助列 模糊查询 函函数数综综合合应应用用 主主页页 1 自自动动记记录录输输入入的的时时间间 输入记录时间 IF C6 IF D6 D6 NOW 自定义格式 yyyy 年 m 月 d 日 h mm ss AM PM 自定义格式 yyyy m d h mm ss 自定义格式 yy m d h mm AM PM 自定义格式 m 月 d 日 h mm AM PM 自定义格式 m 月 d 日 h mm 输机 2 多多单单元元格格合合并并 TEXT一二差异 销售人员姓名加一个 的 销售人员姓名 TEXT C4 的 1 一个以它为基础确定偏移量的固定单元格地址 2 从固定单元格向上或向下查找多少行 3 从固定单元格向左或向右查找多少列 总计一月二月三月四月五月 1010400500600700 这个示例将 40 50 60 70 80 2 1 由于为了解决IF函数7层嵌套问题 CHENJUN版主提供一经典解法 还原HLOOKUP函数的原型来求解 解法思路与方法2一样 但对于使用常量数组法什么时候用呢 我们有时可能不希望单独建立辅助列 或辅助表 那么我们就可以通过定义常量数组的名称来实现 而且这样做的好处是当使用该名称的工作表复制到其他工作薄中时 名称也会被自动复制 由于是常量数组 也不会涉及到外部引用 T类 J类 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 COUNTIF A 80 A 84 逻辑值为TRUE的单元格数量 COUNTIF DATA TRUE 示示例例1 数据数据 苹果32 柑桔54 桃75 苹果86 公式说明 结果 2 计算第一列中 苹果所在单元 格的个数 2 COUNTIF C41 C44 苹果 2 计算第二列中 值大于 55 的 单元格个数 2 COUNTIF D41 D44 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 函函数数与与公公式式宝宝典典中中的的示示例例 资料统计项目统计结果备注 aaAlpha2全部单元格 不区分大小写 COUNTIF data4 text Alpha1全部单元格 区分大小写 SUM IF EXACT data4 text 1 AAA 3 包含的单元格 不区分大小写 COUNTIF data4 20 30 40 是一列四行 纵向 包含四个元素的一维数组常量 10 20 30 40 50 60 是两行三列 包含六个元素的二维数组常量 二维数组常量需要行列等长 如果你觉得输数组常量麻烦 要区分 文本要加 可以先将数据输入单元格区域 然后在其它单元格输入 用鼠标选定该区域后按F9 显示的就是数组常量 然后复制到公式中 其实将以前使用的vlookup公式中第三个参数 区域不要太大 选定按F9 再回车 可以看到公式正常运作 此时已使用了数组常量 以下举例中的公式并不是数组公式 输入时不用Ctrl Shift Enter一齐按 搬一个由chenjun版主提供的计算个人所得税的例子 应应税税所所得得税税率率速速算算扣扣除除数数 税税款款 650020375925 再搬一个前面的例子 汉汉字字字字符符拼拼音音首首字字母母 海H是否被公式中的数组常量吓一跳 不是一个个输的 按F9得到的 汉汉字字字字符符拼拼音音首首字字母母 螺丝扣LSK对于此类固定的数组常量可以将其定义一个名称 公式就简洁多了 还不占用单元格 公式中的 拼音 就是一个定义的名称 可以自 插入 名称 定义 中查看 从从右右向向左左查查询询 返返回回 我们知道 vlookup查找的列必须位于查找区域的最左列 有时会需要按右面的查找左边的值 以 表一 为例 要按 姓名 查询 工号 很容易 但反过来按 工号 查找 姓名 直接使用vlookup就不行了 此时可以使用的公式很多 如lookup index match offset match indirect match等 但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值 介绍这个用法的目的主要是学会将两个一维数组合并为一个二维数组 工工号号姓姓名名 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 输入要查找的月份 三月 需要挑选出的列 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 lookup 为 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 第一列中 的数值 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 函数 VLOOKUP 将返回精确匹配值 如果找不到 则返回错误值 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 下面的下示例是以指定的名字和月份为基础查找一个数值 VLOOKUP 是用于沿第一列向下查找指定的名字 难点是如何向右查找指定的月份 解决这个难题的方法是使用 MATCH 函数 函数 MATCH 通过使用找到名字的列表查找对应月份 并推算该月份在列表中的位置 不巧的是 因为月份列表的搜索范围与查找数值的范围不等宽 函数 MATCH 函数返回的数字比我们需要的数字少1 因此在公式中用了 1进行调整 函数 VLOOKUP 现在使用函数 MATCH 得到的调整的数字n 在对应名字所在行向右查找到该行第n列对应单元格的输入内容 函数 VLOOKUP 中最后使用了 FALSE 因此左侧标题行不用排序 元月二月三月 程香宙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找到的位置查找到对应配件的价格 公式中使用了绝对引用 为的是确保公式复制移动时函数 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 下面的示例是一个建材经销商提供的不同采购数量的折扣率 价格表中显示了砖 木材和玻璃的单价 折扣表提供了不同产品不同采购数量的折扣率 采购表是采购预算 所有的预算结果显示在采购表中 产品名称列表在C列 单价是从价格表中获得的 FALSE选项表示产品名称在价格表中没有排序整理 使用FALSE强迫搜索精确匹配 如果没有找到 则函数显示错误 折扣是从折扣表中获得的 如果采购数量与折扣表中某个值匹配 函数 VLOOKUP将在折扣表中查找正确的匹配折扣 TRUE选项表示采购数量在折扣表中经过了升序排列整理 使用TRUE允许模糊匹配 如果采购数量在折扣表中没有找到匹配的值 则它下面较小的值将被使用 比如采购数量为125将向下与100匹配 并且使用100对应列的折扣率 折扣表 价格表砖砖木木材材 砖砖 210 0 木木材材 11006 3 玻玻璃璃 33008 5 采购表 项目采购数量单价折扣合计 砖125 26 235 木材200 13 194 玻璃150 312 396 砖225 26 423 木材50 10 50 玻璃500 315 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 该示例使用 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 列中没有精确地匹配 所 以返回了一个错误值 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第一列的类型一致 查找文本时 文本不区分大小写 可以使用通配符 可以使用对区域或区域名称的引用 常数数组 计算后的内存数组 该参数的第一列必须包含查找的内容 其它列包含需返回的内容 返回内容的列序号由下个参数指定 如为1时 返回table array第一列中的数值 为2 返回table array第二列中的数值 以此类推 如果col index num小于1 函数 VLOOKUP 返回错误值值 VALUE 如果col index num大于table array的列数 函数 VLOOKUP 返回错误值 REF 如果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 近似匹配查询一般用于数值的查询 table array的第一列必须按升序排列 否则不能返回正确的结果 如果range value为FALSE 或0 函数VLOOKUP将返回精确匹配值 此时 table array不必进行排序 如果找不到 则返回错误值 N A 可isna检测错误后使用if判断去除错误信息 在同一单元格按不同字段查询 返回多个符合条件的查询结果 有两个并列条件 不同字段 的查询 使用数组常量的查询 从右向左查询 处理查找错误 VALUE 和 REF 是由于col index num设置不当造成的 虽然可以使用ISERROR剔除 但建议不要这么做 因为这类错误需要纠正 有时col index num参数使用column 函数生成 当设置不当造成的错误用ISERROR剔除时 会与 N A错误混淆 不利于公式查错 如果vlookup的参数产生错误 vlooukp也返回相同错误 那不在本讨论范围内 当确定引用正确 不应出现 N A 即应该查找到值 时 检查是否存在空格或其它字符 格式是否一致 对于空格或其他不可见字符 可以使用len函数测试文本长度来确定 或使用code测试其ASCII码 该错误由于查询字符 张三丰 后含空格 可能看不出 但在精确匹配查找时造成不完全匹配 张三丰 李四光 王麻子 张三丰赵六儿 李四光KT001 王麻子KT002 赵六儿KT003 KT001KT004 KT002 KT003 对于lookup value中的空格 将其中的空格替换掉 或使用TRIM去除空格后查找 该错误是由于查询表表四中 李四军 后含空格 在精确匹配查找时不完全匹配 这个公式中的Table array已不是直接引用单元格区域 而是引用对 B 87 C 88运算trim后的内存数组 因此 这是一个数组公式 需要按Ctrl Shift Enter输入 如果数据区比较大 数组公式会很慢 不如直接使用替换将数据区的空格替换后使用普通公式 该错误由于查询字符 李四军 后含不可见字符 非空格 使用trim clean 替换空格都不能去除 由网页复制 其他程序转出的表格 经常会出现这类字符 CODE可以测试其并非空格 对于这类不可见字符 可以先复制该字符 然后替换该字符为空白 可以使用鼠标选定几个单元格 看状态栏的合计 有 则为数值 无 则为文本 这两个错误都是由于格式不一致造成 一个是按数值查文本 一个是按文本查数值 对于此类错误 可以修改查找值 数据区的格式 使之统一 也可以在公式中处理 这个公式将数值100用20 30 40 是一列四行 纵向 包含四个元素的一维数组常量 10 20 30 40 50 60 是两行三列 包含六个元素的二维数组常量 二维数组常量需要行列等长 可以先将数据输入单元格区域 然后在其它单元格输入 用鼠标选定该区域后按F9 显示的就是数组常量 然后复制到公式中 其实将以前使用的vlookup公式中第三个参数 区域不要太大 选定按F9 再回车 可以看到公式正常运作 此时已使用了数组常量 是否被公式中的数组常量吓一跳 不是一个个输的 按F9得到的 对于此类固定的数组常量可以将其定义一个名称 公式就简洁多了 还不占用单元格 公式中的 拼音 就是一个定义的名称 可以自 插入 名称 定义 中查看 我们知道 vlookup查找的列必须位于查找区域的最左列 有时会需要按右面的查找左边的值 以 表一 为例 要按 姓名 查询 工号 很容易 但反过来按 工号 查找 姓名 直接使用vlookup就不行了 此时可以使用的公式很多 如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 是一行二列 横向 常量数组 后面的两个区域是多行一列 纵向 即两个数组的方向不同 这样才会生成两列多行数组 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 为 FALSE 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 返回时是精确匹配还是近似匹配 如果为 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 函数 VLOOKUP 现在使用函数 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 选择Sheet 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 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 接受下面的文本值 引号中 作为 from unit 和 to unit 格格式式 没有特殊的格式 示示例例1 下面的例子是进出口公司将货物重量由 以前老的英制单位转换成现行的制单位 磅盎司公斤 重量532 353010094 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 克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高斯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 01eatto1 00E 18 a 说说明明 如果输入数据的拼写有误 函数 CONVERT 返回错误值 VALUE 如果单位不存在 函数 CONVERT 返回错误值 N A 如果单位不支持缩写的单位前缀 函数 CONVERT 返回错误值 N A 如果单位在不同的组中 函数 CONVERT 返回错误值 N A 单位名称和前缀要区分大小写 示示例例2 公公式式说说明明 结结果果 0 45359231将 1 磅转换为千克 0 453592 CONVERT 1 lbm kg 20将 68 华氏度转换为摄氏度 20 CONVERT 68 F C N 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 可以确定一组对象所有可能的组合数 不论其内部顺序 对象组合是对象整体的任意集合或子集 如 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 装饰需要不同的颜色方案 现有五种颜色 每个方案只需要三种颜色 可以组成多少方案 可用颜色每个组合需要颜色 决计组合方法 5310 COMBIN C41 D41 颜色 红 绿 蓝 黄 黑 方案 1方案 2方案 3方案 4方案 5 红红红红红 绿绿绿蓝蓝 蓝黄黑黄黑 方案 6方案 7方案 8方案 9 绿绿绿蓝 蓝蓝黄黄 黄黑黑黑 说说明明 数字参数截尾取整 如果参数为非数值型 则函数 COMBIN 返回错误值 VALUE 如果 number 0 number chosen 0 或 number number chosen COMBIN 返回错误值 NUM 不论其内部顺序 对象组合是对象整体的任意集合或子集 组合与排列不同 排列数与对象内部顺序有关 示示例例3 公公式式说说明明 结结果果 28从八个候选人中提取两个候选人的组合数 28 COMBIN 8 2 CHAR主主页页 ANSI数字 代表字符 65A CHAR G4 66B CHAR G5 169 CHAR G6 功功能能 返回对应于数字代码的字符 函数 CHAR 可将其他类型计算机文件中的代码转换为字符 语语法法 CHAR number Number 是用于转换的字符代码 介于 1 到 255 之间 使用的是当前计算机字符集中的字符 格格式式 运算结果是一个没有固定格式的符号 示示例例 下表列出了1 255数字各自转换成的符号 注意 大多数计算机可能不能正常显示某些符号 它们的形状很小 1 26 51 3 76 L 101 e 126 151 176 201 226 2 27 52 4 77 M 102 f 127 152 177 202 227 3 28 53 5 78 N 103 g 128 153 178 203 228 4 29 54 6 79 O 104 h 129 154 179 204 229 5 30 55 7 80 P 105 i 130 155 180 205 230 6 31 56 8 81 Q 106 j 131 156 181 206 231 7 32 57 9 82 R 107 k 132 157 182 207 232 8 33 58 83 S 108 l 133 158 183 208 233 9 34 59 84 T 109 m 134 159 184 209 234 10 35 60 87 W 112 p 137 162 187 212 237 13 38 0 C0 0 Red 0 C0 0 00 0 00 C2 0 00 Red 0 00 C2 0 P0 0 00 P2 0 00E 00 S2 或 G yy m d 或 yy m d h mm 或 dd mm yy D4 d mmm yy 或 dd mmm yy D1 d mmm 或 dd mmm D2 mmm yy D3 dd mm D5 h mm AM PM D7 h mm ss AM PM D6 h mm D9 h mm ss D8 如果 CELL 公式中的 info type 参数为 format 而且以后又用自定义格式设置了单元格 则必须重新计算工作表 以更新 CELL 公式 示示例例1 1 下面的例子是使用 CELL 函数和其它函数结合提取文件名 当前的文件名是 Excel函数应用顶级技巧 不适合初学者 风度 xls MID CELL filename FIND CELL filename 1 FIND CELL filename FIND CELL filename 1 说说明明 函数 CELL 用于与其他电子表格程序兼容 示示例例2 2 数数据据 37685 TOTAL 公公式式说说明明 结结果果 REF 单元格 A20 的行号 20 CELL row A20 G 第一个字符串的格式代码 D2 请参见上面的信 息 CELL format B112 G 单元格 A3 的内容 TOTAL CELL format B113 自自定定义义格格式式单单元元格格输输入入显显示示为为 使用文本 0 元人民币 15001 500 元人民币 答复 通用格式 1500答复 1500 总额是 0 美元 1500总额是1 500美元 分隔长数字 0 123456789123 457 0 1234567891212 345 679 0 12341 数据确认 0 00 仅显示正数 123123 00 0 00 仅显示正数 123仅显示正数 0 00 仅显示正数 00 00 0用破折号 0 0 0 0 0 0 0 0 12 212 0 0 0 102 102 电话号码 8 52378E 12 852377 659 6295 8 52378E 12852377 659 6295 福利号 421897322421 89 7322 社会福利号 421897322社会福利号 421 89 7322 日期格式 mmmm yy 1995 6 1六月 95 mmmm d yyyy 1995 6 1六月 1 1995 dddd 1995 6 1星期四 mmmm d yyyy dddd 1995 6 1六月 1 1995 星期四 It s dddd 1995 6 1It s 星期四 不同的颜色和格式 红色 1 0 G 通用格式11 红色 1 1 G 通用格式 1100 0 红色 1 2 G 通用格式4545 G 通用格式 G 通用格式 G 通用格式 红色 G 通用格式程香宙程香宙 G 通用格式 G 通用格式 G 通用格式 红色 G 通用格式234234 专用符号 G 通用格式 2004 2004 G 通用格式 G 通用格式 G 通用格式 G 通用格式 注册注册 G 通用格式 G 通用格式 G 通用格式 G 通用格式 号码号码 G 通用格式 G 通用格式 G 通用格式 G 通用格式 文本引述文本引述 G 通用格式 G 通用格式 G 通用格式 G 通用格式 123123 描述 正数 负数 零 文本 32正数 正数 负数 零 文本 32负数 正数 负数 零 文本 0零 正数 负数 零 文本 程香宙文本 隐藏 1234 145 条件语句 100 000 99099 100 000 102102 100 000 54 100 000 程香宙 DOLLAR 或或 RMB 原始数据转换成文本后 10 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 本函数可将数字转换为文本格式 并应用货币符号 函数的名称及其应用的货币符号取决于您的语言设置 语语法法 DOLLARDOLLAR 或或 RMBRMB number decimals Number 为数字 包含数字的单元格引用 或是计算结果为数字的公式 Decimals 为十进制数的小数位数 如果 Decimals 为负数 则参数 number 从小数点往左按相应位数取整 如果省略 Decimals 则假设其值为 2 说说明明 使用 格格式式 菜单中的 单单元元格格 命令来设置包含数字的单元格的格式与使用 DOLLAR 函数直接设置数字的格式 之间的区别在于 DOLLAR 函数将结果转换为文本 而使用 单元格 命令设置格式的数字仍为数字 但可以 继续在公式中使用由 DOLLAR 函数设置了格式的数字 因为 Microsoft 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 或 0 1230 99 89以货币格式及小数点右边 2 位数字的形式显示第四个数 99 89 或 99 89 Reference 表示要获取其有关信息的单元格 如果忽略 则在 info type 中所指定的信息将返回给最后更改的单元格 ACCRINT 主主页页 功功能能 返回定期付息有价证券的应计利息 如果该函数不可用 并返回错误值 NAME 请安装并加载 分析工具库 加载宏 语语法法 ACCRINT issue first interest settlement rate par frequency basis 要点 应使用 DATE 函数来输入日期 或者将日期作为其他公式或函数的结果输入 例如 使用函数 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 日的序列号是 1 而 2008 年 1 月 1 日的序列号是 39448 这是因为它距 1900 年 1 月 1 日有 39448 天 Microsoft Excel for the Macintosh 使用另外一个默认日期系统 Issue first interest settlement frequency 和 basis 将被截尾取整 如果 issue first interest 或 settlement 不是合法日期 函数 ACCRINT 返回错误值 VALUE 如果利率为 0 或票面价值为 0 函数 ACCRINT 返回错误值 NUM 如果 frequency 不是数字 1 2 或 4 函数 ACCRINT 返回错误值 NUM 如果 basis 4 函数 ACCRINT 返回错误值 NUM 如果 issue settlement 函数 ACCRINT 返回错误值 NUM 示示例例 数据说明 39508发行日 39691起息日 39569成交日 0 1息票利率 1000票面价值 2按半年期支付 请参见上面的信息 0以 30 360 为日计数基准 请参见上面的信息 公式说明 结果 16 66666667 满足上述条件的应付利息 16 66666667 ACCRINT C38 C39 C40 C41 C42 C43 C44 15 55555556 满足上述条件的应付利息 除发行日为 2008 年 3 月 5 日之外 ACCRINT DATE 2008 3 5 C39 C40 C41 C42 C43 C44 ACCRINTM 功功能能 返回到期一次性付息有价证券的应计利息 如果该函数不可用 并返回错误值 NAME 请安装并加载 分析工具库