Excel常用函数经典教程(珍藏版).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 39DATEVALUE 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 cell 32 或get cell 66 DATEDIF 函数 这个函数是用来计算一个数的绝对值 与正负数符号没有关系 返回定期付息有价证券的应计利息 返回数字的反余弦值 反余弦值是角度 它的余弦值为数字 返回的角度值以弧度 表示 范围是 0 到 pi 返回 number 参数的反双曲余弦值 参数必须大于或等于 1 反双曲余弦值的双曲 余弦即为该函数的参数 按照给定的行号和列标 建立文本类型的单元格地址 返回每个结算期间的折旧值 该函数主要为法国会计系统提供 如果某项资产是在 该结算期的中期购入的 则按直线折旧法计算 该函数与函数 AMORLINC 相似 不 同之处在于该函数中用于计算的折旧系数取决于资产的寿命 返回到期一次性付息有价证券的应计利息 返回每个结算期间的折旧值 该函数为法国会计系统提供 如果某项资产是在结算 期间的中期购入的 则按 这个函数用于检验两个或更多 最多30个 条件 看它们是否都为TRUE 返回某一引用区域的左上角单元格的格式 位置或内容等信息 这个函数可以根据用户要求把一个数字向上舍入 统计列表中含数字或文本的单元格个数 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列 处的数值 当比较值位于数据表首列时 可以使用函数 VLOOKUP 代替函数 HLOOKUP 在 VLOOKUP 中的 V 代表垂直 可以使用 index num 返回数值参数列表中的数值 使用函数 CHOOSE 可以基于索 引号返回多达 这个函数能够计算一组数字的平均数 这个函数将二进制数转换为十进制数 负数用二进制数补码表示 返回对应于数字代码的字符 函数 CHAR 可将其他类型计算机文件中的代码转换为 字符 删除文本中不能打印的字符 返回文本字符串中第一个字符的数字代码 返回的代码对应于计算机当前使用的字 符集 计算从给定数目的对象集合中提取若干对象的组合数 利用函数 COMBIN 可以确定 一组对象所有可能的组合数 将几个文本字符串合并为一个文本字符串 将数字从一个度量系统转换到另一个度量系统中 返回包含数字以及包含参数列表中的数字的单元格的个数 利用函数 COUNT 可以计 算单元格区域或数字数组 该函数用于统计一个区域中空白单元格的个数 计算区域中满足给定条件的单元格的个数 这个函数在表格左侧的行标题中查找指定的内容 目 录 这个函数在表格左侧的行标题中查找指定的内容 这个函数可以计算两个日期间的间隔 返回 date text 所表示的日期的序列号 函数 DATEVALUE 的主要功能是将以文 本表示的 这个函数示例列出了产品的平均价格 这个函数可以从一个完整的日期中提取出日 按照一年 360 天的算法 每个月以 30 天计 一年共计 12 个月 返回两日期 间相差的天数 这在一些会计计算 返回数据清单或数据库的列中满足指定条件的最大数值 返回数据清单或数据库的列中满足指定条件的最小数字 该函数依照货币格式将小数四舍五入到指定的位数并转换成文本 使用的格式为 0 00 0 00 返回数据清单或数据库的列中满足指定条件的数字之和 DCOUNT 产产品品瓦瓦数数寿寿命命 小小时时 商商标标单单价价每每盒盒数数量量采采购购盒盒数数价价值值 白炽灯2003000上海 4 5043 54 00 氖管1002000上海 2 00152 60 00 日光灯60 0 00 其它108000北京 0 80256 120 00 白炽灯801000上海 0 20403 24 00 日光灯100未知上海 1 25104 50 00 日光灯2003000上海 2 50150 0 00 其它25未知北京 0 50103 15 00 白炽灯2003000北京 5 0032 30 00 氖管1002000北京 1 80205 180 00 白炽灯100未知北京 0 25105 12 50 白炽灯10800上海 0 20252 10 00 白炽灯601000北京 0 15250 0 00 白炽灯801000北京 0 20302 12 00 白炽灯1002000上海 0 80105 40 00 白炽灯401000上海 0 10205 10 00 统计某一有使用寿命 小时 的产品的商标数 商标这两个单元格是数据范围 商标名称 北京 有寿命 小时 的 北京 商标数是 5 DCOUNT B3 I19 D3 E23 E24 DCOUNTDCOUNT功功能能 返回数据库或数据清单的列中满足指定条件并且包含数字的单元格个数 DCOUNTDCOUNT语语法法 DCOUNT database field criteria Database 构成数据清单或数据库的单元格区域 数据库是包含一组相关数据的数据清单 其中包含 相关信息的行为记录 而包含数据的列为字段 数据清单的第一行包含着每一列的标志项 Field 指定函数所使用的数据列 数据清单中的数据列必须在第一行具有标志项 Field 可以是文本 即两端带引号的标志项 如示例2中的 使用年数 或 产量 此外 Field 也可以是代表数据清单中数 据列位置的数字 1 表示第一列 2 表示第二列 等等 Criteria 为一组包含指定条件的单元格区域 可以为参数 criteria 指定任意区域 只要它至少包含一 个列标志和列标志下方用于设定条件的单元格 参数 field 为可选项 如果省略 函数 DCOUNT 返回数据库中满足条件 criteria 的所有记录数 DCOUNTDCOUNT格格式式 没有专门的格式 DCOUNTDCOUNT示示例例1 1 指指定定产产品品已已知知采采购购盒盒数数的的总总数数 产品采购盒数 白炽灯5 采购盒数为5的产品白炽灯的数量是 3 DCOUNT B3 I19 H3 E50 F51 这是相同的计算 但是用采购盒数代替了单元格地址 3 DCOUNT B3 I19 采购盒数 E50 F51 产产品品白白炽炽灯灯具具体体指指定定瓦瓦数数为为100100的的总总数数是是 产品瓦数 白炽灯100 总数是 2 DCOUNT B3 I19 采购盒数 E61 F62 产产品品白白炽炽灯灯瓦瓦数数在在 80 80 80101000的数据行 类型销售人员销售额 农产品程香宙 1000 某某一一列列或或另另一一列列上上具具有有单单个个条条件件 若要找到满足一列条件或另一列条件的数据 请在条件区域的不同行中输入条件 例如 下面的条件区域 将显示所有在类型列中包含农产品 在销售人员列中包含程龙或销售额 1000的行 类型销售人员销售额 农产品 程龙 1000 两两列列上上具具有有两两组组条条件件之之一一 若要找到满足两组条件 每一组条件都包含针对多列的条件 之一的数据行 请在各行中键入条件 例如 下面的条件区域将显示所有在销售人员列中包含程香宙且销售额 3000的行 同时也显示 程龙销售商的销售额 1500的行 销售人员销售额 程香宙 3000 程龙 1500 一一列列有有两两组组以以上上条条件件 若要找到满足两组以上条件的行 请用相同的列标包括多列 例如 下面条件区域显示 5000和 8000之间以及5000 8000 AVERAGE d 81 D 84 注注意意 用作条件的公式必须使用相对引用 相对单元格引用 在公式中 基于包含公式的单元格与被引用的单元 格之间的相对位置的单元格地址 如果复制公式 相对引用将自动调整 相对引用采用 A1 样式 来引 用列标志 例如 销售 或者引用第一个记录的对应字段 公式中的所有其他引用都必须是绝对引 用 绝对单元格引用 公式中单元格的精确地址 与包含公式的单元格的位置无关 绝对引用采用的形式 为 A 1 并且公式必须计算出结果 TRUE 或 FALSE 您可以在公式中使用列标志来代替相对的单元格引用或区域名称 当 Microsoft Excel 在包含条件的单元 格中显示错误值 NAME 或 VALUE 时 您可以忽略这些错误 因为它们不影响列表的筛选 Microsoft Excel 在计算数据时不区分大小写 DGET 这是一个数据库区域 产产品品瓦瓦数数寿寿命命 小小时时 商商标标单单价价每每盒盒数数量量采采购购盒盒数数价价值值 白炽灯2003000上海 4 5043 54 00 氖管1002000上海 2 00152 60 00 日光灯60 0 00 其它108000北京 0 80256 120 00 白炽灯801000上海 0 20403 24 00 日光灯100未知上海 1 25104 50 00 日光灯2003000上海 2 50151 37 50 其它25未知北京 0 50103 15 00 白炽灯2003000北京 5 0032 30 00 氖管1002000北京 1 80205 180 00 白炽灯100未知北京 0 25105 12 50 白炽灯10800上海 0 20252 10 00 白炽灯601000北京 0 15251 3 75 白炽灯801000北京 0 20302 12 00 白炽灯1002000上海 0 80105 40 00 白炽灯401000上海 0 10205 10 00 指定的符合条件的产品共采购多少 产产品品瓦瓦数数寿寿命命 小小时时 商商标标 白炽灯100上海 数量是 5 DGET B3 I19 H3 C23 F24 功功能能 从数据清单或数据库的列中提取符合指定条件的单个值 如果没有满足条件的记录 则函数 DGET 将返回错误值 VALUE 如果有多个记录满足条件 则函数 DGET 将返回错误值 NUM 语语法法 DGET database field criteria Database 构成数据清单或数据库的单元格区域 数据库是包含一组相关数据的数据清单 其中包含 相关信息的行为记录 而包含数据的列为字段 数据清单的第一行包含着每一列的标志项 Field 指定函数所使用的数据列 数据清单中的数据列必须在第一行具有标志项 Field 可以是文本 即两端带引号的标志项 此外 Field 也可以是代表数据清单中数据列位置的数字 1 表示第一列 2 表示第二列 等等 Criteria 为一组包含给定条件的单元格区域 可以为参数 criteria 指定任意区域 只要它至少包含一个 列标志和列标志下方用于设定条件的单元格 格格式式 没有专门的格式 示示例例 1 这个示例从正确的记录中提取信息 采购了多少盒指定的产品 产产品品瓦瓦数数寿寿命命 小小时时 商商标标 白炽灯100上海 采购数量是 5 DGET B3 I19 H3 C51 F52 示示例例 2 这个示例从两个并联的记录中提取信息 因此显示为 NUM错误 采购了多少盒指定的产品 产产品品瓦瓦数数寿寿命命 小小时时 商商标标 白炽灯100 采购盒数是 NUM DGET B3 I19 H3 C63 F64 示示例例 3 这个示例从不存在的记录中提取信息 因此显示为 VALUE错误 采购了多少盒指定的产品 产产品品瓦瓦数数寿寿命命 小小时时 商商标标 白炽灯9999 采购盒数是 VALUE DGET B3 I19 H3 C64 F65 示示例例 4 这个示例使用了 IF 函数使之在出现错误时得到提示信息 采购了多少盒指定的产品 产产品品瓦瓦数数寿寿命命 小小时时 商商标标 白炽灯1000上海 采购盒数是 VALUE DGET B3 I19 H3 C85 F86 没有这个产品 IF ISERR F88 CHOOSE ERROR TYPE F88 3 没有这个产品 找到多个产品 找到一个产品 自自定定义义格格式式单单元元格格输输入入 使使用用文文本本 0 元人民币 1500 答复 通用格式 1500 总额是 0 美元 1500 分分隔隔长长数数字字 0 123456789 0 12345678912 0 1234 数数据据确确认认 0 00 仅显示正数 123 0 00 仅显示正数 123 0 00 仅显示正数 0 0 0用用破破折折号号 0 0 0 0 0 0 0 12 2 0 0 0 102 电电话话号号码码 8 52378E 12 8 52378E 12 福福利利号号 421897322 社会福利号 421897322 日日期期格格式式 mmmm yy 1995 6 1 mmmm d yyyy 1995 6 1 dddd 1995 6 1 mmmm d yyyy dddd 1995 6 1 It s dddd 1995 6 1 不不同同的的颜颜色色和和 格格式式 红色 1 0 G 通用格式 1 红色 1 1 G 通用格式 1 红色 1 2 G 通用格式 45 G 通用格式 G 通用格式 G 通用格式 红色 G 通用格式 程香宙 G 通用格式 G 通用格式 G 通用格式 红色 G 通用格式 234 专专用用符符号号 G 通用格式 2004 G 通用格式 G 通用格式 G 通用格式 G 通用格式 注册 G 通用格式 G 通用格式 G 通用格式 G 通用格式 号码 G 通用格式 G 通用格式 G 通用格式 G 通用格式 文本引述 G 通用格式 G 通用格式 G 通用格式 G 通用格式 123 描描述述 正数 负数 零 文本 32 正数 负数 零 文本 32 正数 负数 零 文本 0 正数 负数 零 文本 程香宙 隐隐藏藏 1234 145 条条件件语语句句 100 000 99 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 星期四 It s 星期四 1 100 0 45 程香宙 234 2004 注册 号码 文本引述 123 正数 负数 零 文本 099 102 拆拆分分姓姓名名 当你需要拆分某个含有文本格式内容的单元格内文字时要用到这个公式 下面几个例子可以是用来提取姓名中一部分的通常方法 需要指出的是 这些文法一般只适应于老外的英文姓名 名在前 姓在后 对于中文姓名 由于 书写习惯是姓在前且文字中间没有空格 所以不太适用 这个公式是使用文本函数达到目的的 其技巧就在于姓名中间有空格分开 提提取取名名 姓名名 Alan JonesAlan LEFT C14 FIND C14 1 Bob SmithBob LEFT C15 FIND C15 1 Carol WilliamsCarol LEFT C16 FIND C16 1 提提取取姓姓 姓名姓 Alan JonesJones RIGHT C22 LEN C22 FIND C22 Bob SmithSmith RIGHT C23 LEN C23 FIND C23 Carol WilliamsWilliams RIGHT C24 LEN C24 FIND C24 当当有有中中间间名名字字出出现现时时提提取取姓姓 这个公式不能用于姓名有两个以上单词的情况 如果有中间名 公式的套用将出现不正确的结果 解决这个问题的办法是用一个较长的公式 姓名姓 Alan David JonesJones Bob John SmithSmith Carol Susan Williams Williams RIGHT C37 LEN C37 FIND SUBSTITUTE C37 LEN C37 LEN SUBSTITUTE C37 提提取取中中间间名名 姓名中间名 Alan David JonesDavid Bob John SmithJohn Carol Susan Williams Susan LEFT RIGHT C45 LEN C45 FIND C45 1 FIND RIGHT C45 LEN C45 FIND C45 1 1 公公式式中中的的括括号号 有时你在公式中可能使用到括号才能保证计算按你需要的顺序进行 在进行加 减 乘 除混合运算时需要用括号分开 数学上的 和 比 和 优先 和 的运算将在 和 之前进行 例 1 答案不正确 10 20 2 50 C12 C13 C14 你可能是想计算10 20 得到 30 然后30 2 得到 60 但是 因为 在Excel中首先计算 结果是 20 2 得到 40 然后10 40 得到错误的结果50 例 2 正确答案 10 20 2 60 C27 C28 C29 在 10 20 两边放上括号后 Excel 首先运行它并 得到结果 30 然后用 30 乘以 2 得到 60 这都是你想要的结果 百百分分率率 excelhome chengxiang Excel并没有提供专门用于计算百分率的函数 但我们完全可以利用在学校学习的数学知识分类掌握下面这些技巧 已已知知百百分分率率求求一一个个数数 起始数120 百分率25 得数30 D8 D9 示示例例 1 一个公司将要给职员加薪 财务部门需要计算职员应该增加多少 职员工资级别不同增加的比例不同 级别增幅 A10 B15 C20 姓名级别原工资增加的工资 程香宙A 10 000 1 000 E23 LOOKUP D23 C 18 C 20 D 18 D 20 刘冰B 20 000 3 000 E24 LOOKUP D24 C 18 C 20 D 18 D 20 程龙C 30 000 6 000 E25 LOOKUP D25 C 18 C 20 D 18 D 20 程香峰B 25 000 3 750 E26 LOOKUP D26 C 18 C 20 D 18 D 20 程坤C 32 000 6 400 E27 LOOKUP D27 C 18 C 20 D 18 D 20 张凤玲A 12 000 1 200 E28 LOOKUP D28 C 18 C 20 D 18 D 20 已已知知百百分分率率求求一一个个增增加加了了的的数数 起始数120 百分率25 得数150 D33 D34 D33 示示例例 2 一个公司将要给职员加薪 财务部门需要计算职员工资增加后是多少 职员工资级别不同增加的比例不同 级别增幅 A10 B15 C20 姓名级别原工资增加后工资 程香宙A10 00011 000 E48 LOOKUP D48 C 18 C 20 D 18 D 20 E48 刘冰B20 00023 000 E49 LOOKUP D49 C 18 C 20 D 18 D 20 E49 程龙C30 00036 000 E50 LOOKUP D50 C 18 C 20 D 18 D 20 E50 程香峰B25 00028 750 E51 LOOKUP D51 C 18 C 20 D 18 D 20 E51 程坤C32 00038 400 E52 LOOKUP D52 C 18 C 20 D 18 D 20 E52 张凤玲A12 00013 200 E53 LOOKUP D53 C 18 C 20 D 18 D 20 E53 计计算算百百分分率率 数字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 东9 2 9 7 G74 H 78 南7 4 9 5 G75 H 78 西2 8 7 3 G76 H 78 北8 9 6 5 G77 H 78 合计26 23 31 20 G78 H 78 下下年年度度预预算算150 000 判判断断下下年年度度预预算算分分配配 区域Q1Q2Q3Q4 东13 5003 00013 50010 500 G82 E 88 南10 5006 00013 5007 500 G83 E 88 西3 00012 00010 5004 500 G84 E 88 北12 00013 5009 0007 500合计 合计39 00034 50046 50030 000150 000 已已知知现现有有数数和和增增加加的的百百分分率率求求起起始始数数 现有数150 增加了 25 起始数120 D100 100 D101 示示例例 4 一旅游者在某处旅游支出了一笔引用并一张含消费税的收据 他需要知道实际开支和税收开支明细 然而收据上只显示开支总数 他通过下表把开支明细计算了出来 税率17 50 物品总数实际开支税收 汽油 10 9 1 D113 D113 100 D 110 旅馆 235 200 35 汽油 118 100 18 D115 100 D 110 文文件件名名函函数数 当你需要在工作表的一个单元格中插入当前工作簿名称时 可以使用这个函数 这时使用 CELL 函数 显示如下 D Personal Study MS技巧 令人叫绝的EXCEL函数功能 V2006 5 15整理版 xls 目录 CELL filename 问题是它带有驱动器盘符和文件的全部路径 你需要使用文本函数挑选出工作表或工作簿名称 选选择择路路径径 D Personal Study MS技巧 MID CELL filename 1 FIND CELL filename 1 选选择择工工作作簿簿名名称称 令人叫绝的EXCEL函数功能 V2006 5 15整理版 xls MID CELL filename FIND CELL filename 1 FIND CELL filename FIND CELL filename 1 选选择择工工作作表表名名称称 目录 MID CELL filename FIND CELL filename 1 255 宏宏表表函函数数 1 get cell 66 定义一个名称 简写为 mc 引用位置写入 get cell 66 然后在任意单元格输入 mc 即可得到当 前工作簿名称 令人叫绝的EXCEL函数功能 V2006 5 13 JSSY汇总版 xls mc 2 get cell 62 或get cell 32 定义一个名称 简写为 mcb 引用位置写入 get cell 62 或 get cell 32 然后在任意单元格 输入 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 生活的年 51 DATEDIF C8 TODAY y 和月 0 DATEDIF C8 TODAY ym 和日 6 DATEDIF C8 TODAY md 你可以把它们连接起来使用 年龄是 51 年 0 月和 6 天 年龄是 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 函数功能 V2006 5 15整理 版 xls MID CELL 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 format B112 G 单元格 A3 的内容 TOTAL CELL format B113 Info type 为一个文本值 指定所需要的单元格信息的类型 下面列出 info type 的可能值及相应的结果 Reference 表示要获取其有关信息的单元格 如果忽略 则在 info type 中所指定的信息将返回给最后更改的单元格 下表描述 info type 为 format 以及引用为用内置数字格式设置的单元格时 函数 CELL 返回的文本值 CEILING 数字向上舍入 2 13 CEILING C4 1 1 52 CEILING C5 1 1 92 CEILING C6 1 2030 CEILING C7 30 2530 CEILING C8 30 4060 CEILING C9 30 功功能能 这个函数可以根据用户要求把一个数字向上舍入 将参数 Number 向上舍入 沿绝对值增大的方向 为最接近的 significance 的倍数 例如 如果您不愿意使用像 分 这样的零钱 而所要购买的商品价格为 4 42 可以用公式 CEILING 4 42 0 1 将价格向上舍入为以 角 表示 语语法法 CEILING ValueToRound MultipleToRoundUpTo ValueToRound可以是单元格地址 也可以是个算式 格格式式 没有固定的格式 示示例例1 下面的表格可用于度假公寓出租租金计算 它的核算单位是以一周计算 计算顾客付款时要用到 CEILING 要求天数 需要付款的天 数 函数将付款天数全部转化成了7 的倍数 客户 137 CEILING D28 7 客户 247 CEILING D29 7 客户 31014 CEILING D30 7 示示例例2 下面表格适用于一个建筑商要把建筑材料运输到工地 建筑商需要卡车去运输这些建筑材料 每种材料需要一定的卡车容量 表 1 根据卡车载货量计算出运输材料的最小卡车数量 这个结果不是整数而建筑商有无法分割卡车 表格 1 项目运输数量卡车载重量需要的卡车数 砖10003003 33 D45 E45 木头50006008 33 D46 E46 水泥20003505 71 D47 E47 表格2显示了如何使用 CEILING 函数计算出需要卡车的实际数量 表格 2 项目运输数量卡车载重量需要的卡车数 砖10003004 CEILING D54 E54 1 木头50006009 CEILING D55 E55 1 水泥20003506 CEILING D56 E56 1 示示例例 3 下面的表格适用于商店老板计算商品销售价格表 商店老板购买商品需要包装 包装费用要分担到单位商品 商店老板问题希望最后结果带有9分 表1显示如何计算单位商品分摊的实际费用 表 1 品名包装规格包装费平均分摊 插头11 201 81818 D69 C69 插座7 18 252 60714 D70 C70 接头5 28 105 62000 D71 C71 电路板16 281 75000 D72 C72 表2 显示了如何使用 CEILING 函数将单位费用向上舍入到最后99分 Table 2 品名包装规格包装费平均分摊向上舍入后 插头11 201 818181 99 插座7 18 252 607142 99 接头5 28 105 620005 99 电路板16 281 750001 99 INT E83 CEILING MOD E83 1 0 99 说说明明 INT E83 计算整数部分 MOD E83 1 计算小数部分 CEILING MOD E83 0 99 向上舍入小数部分到0 99 1 如果参数为非数值型 CEILING 返回错误值 VALUE 2 无论数字符号如何 都按远离 0 的方向向上舍入 3 如果数字已经为 Significance 的倍数 则不进行舍入 4 如果 Number 和 Significance 符号不同 CEILING 返回错误值 NUM 示示例例4 4 公式说明 结果 3 将 2 5 向上舍 入到最接近的 1 的倍数 3 CEILING 2 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 CEILING 1 5 0 1 0 24 将 0 234 向上 舍入到最接近 的 0 01 的倍数 0 24 CEILING 0 234 0 01 CEILING D54 E54 1 CEILING D55 E55 1 CEILING D56 E56 1 INT E83 CEILING MOD E83 1 0 99 COUNTA 数据统计 1020303 COUNTA C4 E4 100303 COUNTA C5 E5 10 20303 COUNTA C6 E6 101 1月 88303 COUNTA C7 E7 1021 30303 COUNTA C8 E8 100 184568363303 COUNTA C9 E9 10302 COUNTA C10 E10 10Hello303 COUNTA C11 E11 10 DIV 0 303 COUNTA C12 E12 功功能能 统计列表中含数字或文本的单元格个数 它忽略空值 语语法法 COUNTA Value1 value2 Value1 value2 为所要计算的值 参数个数为 1 到 30 个 在这种情况下 参数值可以是任何类型 它们可以包括空字符 但不包括空白单元格 如果参数是数组或单元格引用 则数组或引用中的空 白单元格将被忽略 如果不需要统计逻辑值 文字或错误值 请使用函数 COUNT 格格式式 没有专用的格式 示示例例1 以下是一个学校统计 keep track of 学生的考试情况的表格 及格学生评定等级以1 2 3表示 不及格的记做 不及格 学校想要知道多少学生参加了考试 每科考试参加了多少人 COUNTA 函数可以实现这一目的 因为它就是用来专门统计表格中数字和文本的个数 数学英语艺术历史 每个学生参 加的科目数 程香宙不及格12 刘冰2133 程龙1113 程坤不及格不及格2 程香峰132不及格4 COUNTA D39 G39 每个科目参加了多了学生 数学英语艺术历史 4352 COUNTA D35 D39 示示例例2 2 数数据据 销售 39790 19 22 24 TRUE DIV 0 公公式式说说明明 结结果果 6 计算上列数据中 非空单元格的个 数 6 COUNTA C49 C55 4 计算上列数据的 最后四行中非空 单元格的个数 4 COUNTA C51 C55 7 计算上列数据中 非空单元格以及 包含数值 2 的单 元格个数 7 COUNTA C49 C55 2 7 计算上列数据中 非空单元格以及 值 Two 的个数 7 COUNTA C49 C55 Two Value1 value2 为所要计算的值 参数个数为 1 到 30 个 在这种情况下 参数值可以是任何类型 Excel链链接接 Excel信信息息 The links below are to sites of general interest Andy Pope s Excel Information Excel 下载和图表示例 Ask Mr Excel Excel 问题和解决办法 ASSUME 统计 Barrie Davidson s Excel Page VBA公式示例 Bella Online Excel问题和链接 Bob Houghton 信息和下载 Contextures Excel问题和技巧 CSA NET 帮助和下载 DataPig 下载和手册 Epsen Gaarder Haug 选项定价计算表 Daniel Lamarche Excel 2000 没有困扰 Excel Advisor 问题及解答 Excel By Example 屏幕录象 Excel Experts Club Yahoo的高水平用户俱乐部 Excel Help Page John F Lacher s site Excel Logic Page By Aaron Blood Excel Maniacs Excel 问题讨论 ExcelTip Excel问题和策略 ExWorks Excel问题和下载 Financial Modeling 财政金额模块 The Fortress 有趣的Excel文件 Fred Cumming s Excel Page Excel文件下载 GantTool 创建图表 免费下载 Google Excel Search Add In From Ron de Bruin Help Talk Online 讨论Excel和其它Office 产品 John s Finance Page 许多文件下载 Laurent Longre 免费 Excel 加载宏下载 统计和一般 Laroux Virus Remover For Excel 97 Windows or Mac Linda s Computer Stop 娱乐Excel Magic Box Web Tutor for Microsoft Excel 在线Excel指导 M 科学和工程 MeadInKent Help with Excel functions graphs and spreadsheet checking Mike s Excel and Maths Page 下载 MM s Little Spreadsheet Helpers 数学和物理 My Excel Pages By David McRitchie National Marine Mammal Laboratory Excel 几何函数 VBA NEKO to Excel VBA游戏 Neville Hunt 统计表格 OneOnOne Quick and Dirty task guides OzGrid Business Applications Excel tips tricks downloads newsletter Peter Noneley Excel Function Dictionary Download a workbook that contains more than 150 examples of Excel s functions Resources For MS Excel Excel product features support Knowledge Base article links Richard Knights Computer modeling examples using Excel Sam Raheb s Microsoft Excel Web Site Lots of useful downloads Simtools and Formlist add ins Freeware statistical and auditing tools Spreadsheet Autopublisher An experimental service for teachers and others who would like to publish their spreadsheet models on the Web but do not have their own Web sites Spreadsheets in Education Erich Neuwirth s site which includes mathematical scientific and statistical applications Tattsoft Spreadsheet Productions Files to download TOPS Free Excel downloads time series analysis financial market tools numerical computation Virtual Help Desk Resources and Help for Excel WinSite Excel文件下载 HLOOKUP 一月二月三月行1 行数字并非必需的 108097行2它们只是便于在示例中使用 209069行3 3010045行4 4011051行5 5012077行6 寻找月份 三月 需要挑出的行 4 结果是 45 HLOOKUP F10 D3 F10 F11 FALSE 功功能能 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 当比较值位于数 据表首列时 可以使用函数 VLOOKUP 代替函数 HLOOKUP 在 VLOOKUP 中的 V 代表垂直 语语法法 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 格格式式 没有专门的格式 示示例例 1 下面示例以月份和姓名为基础查找值 使用函数 HLOOKUP 查找月份 当向下查找相邻的姓名时问题出现了 这个问题可以使用函数 MATCH 解决 函数 MATCH 彻底搜索姓名列表并找到我们需要的姓名在列表中的位置 然而 因为姓名列表并不像LOOKUP范围那样广泛 函数 MATCH 等到的结果与我们实际需要的结果少1 因此要额外添加数字1补充 在函数 HLOOKUP 中使用函数 MATCH 得到的数字在月份列向下找到单元格条目 函数 HLOOKUP 在结尾处使用FALSE为的是月份列不用按升序排列 一月二月三月 程香宙108097 刘冰209069 程龙3010045 程坤4011051 张三5012077 查找的月份 二月 查找的姓名 程龙 结果是 100 HLOOKUP F51 D44 F51 MATCH F52 C45 C49 0 1 FALSE 示示例例 2 这个示例使用函数 HLOOKUP 查找不同小车生产厂商不同配件的价值 函数 HLOOKUP 在B列不同厂商标题 找到厂商后 函数 HLOOKUP 又使用 MATCH 函数查找下一列C内配件列表中指定的行 公式中使用了绝对引用 为的是确保公式复制移动时函数 HLOOKUP 和 MATCH 引用的范围不发生变化 厂商配件价值 日本丰