EXCEL最佳实用教程.xls

常用函数 SUM SUMIFS COUNT COUNTIFS AVERAGE AVERAGEIFS FIND MID MATCH RANK VLOOKUP HLOOKUP TEXT 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 65316515303 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 个 在这种情况下 参数值可以是任何类型 ADDRESS 列号 2 行号 3 工作表名 Hello B 3 ADDRESS F4 F3 1 TRUE B 3 ADDRESS F4 F3 2 TRUE B3 ADDRESS F4 F3 3 TRUE B3 ADDRESS F4 F3 4 TRUE R3C2 ADDRESS F4 F3 1 FALSE R3C 2 ADDRESS F4 F3 2 FALSE R 3 C2 ADDRESS F4 F3 3 FALSE R 3 C 2 ADDRESS F4 F3 4 FALSE Hello B 3 ADDRESS F4 F3 1 TRUE F5 Hello B 3 ADDRESS F4 F3 2 TRUE F5 Hello B3 ADDRESS F4 F3 3 TRUE F5 Hello B3 ADDRESS F4 F3 4 TRUE F5 功功能能 按照给定的行号和列标 建立文本类型的单元格地址 这个函数可用于当前工作表的宏 语语法法 ADDRESS row num column num abs num a1 sheet text Row num 在单元格引用中使用的行号 1 16384 Column num 在单元格引用中使用的列标 A IV 或1 256 Abs num 指定返回的引用类型 1 2 3 4 1或省略 绝对引用 2绝对行号 相对列标 3绝对列标 相对行号 4相对引用 A1 用以指定 A1 或 R1C1 引用样式的逻辑值 如果 A1 为 TRUE 或省略 函数 ADDRESS 返回 A1 样式的引用 如果 A1 为 FALSE 函数 ADDRESS 返回 R1C1 样式的引用 Sheet text 为一文本 指定作为外部引用的工作表的名称 如果省略 sheet text 则不使用任何工作表名 示示例例 公式说明 结果 C 2 绝对引用 C 2 ADDRESS 2 3 C 2 绝对行号 相对 列标 C 2 ADDRESS 2 3 2 R2C 3 在 R1C1 引用样 式中的绝对行号 相对列标 R2C 3 ADDRESS 2 3 2 FALSE Book1 Sh eet1 R2C3 对其他工作簿或 工作表的绝对引 用 Book1 Sheet1 R2C3 ADDRESS 2 3 1 FALSE Book1 Sheet1 EXCEL SHEET R2 C3 对其他工作表的 绝对引用 EXCEL SHEET R2C3 ADDRESS 2 3 1 FALSE EXCEL SHEET AND 测试项目结果 500800TRUE AND C4 100 D4 100 50025FALSE AND C5 100 D5 100 25500FALSE AND C6 100 D6 100 12TRUE AND D7 1 D7 AVERAGE C 29 C 38 D38 AVERAGE D 29 D 38 E38 AVERAGE E 29 E 38 平均分475460 例例 2 公公式式说说明明 结结果果 TRUE所有参数的逻辑值为真 TRUE NAME FALSE一个参数的逻辑值为假 FALSE NAME TRUE所有参数的计算结果为真 TRUE NAME 例例 3 数数据据 50 104 公公式式说说明明 结结果果 TRUE 因为 50 介于 1 到 100 之间 TRUE NAME 104 如果上面的第二个数字介于 1 到 100 之间 则显示该数字 否则显示信息 数值超出范 围 NAME 数值超出范 围 如果上面的第一个数字介于 1 到 100 之间 则显示该数字 否则显示信息 50 NAME 说说明明 参数必须是逻辑值 TRUE 或 FALSE 或者包含逻辑值的数组或引用 如果数组或引用参数中包含文本或空白单元格 则这些值将被忽略 如果指定的单元格区域内包括非逻辑值 则 AND 将返回错误值 VALUE Logical1 logical2 表示待检测的 1 到 30 个条件值 各条件值可为 TRUE 或 FALSE AND C38 AVERAGE C 29 C 38 D38 AVERAGE D 29 D 38 E38 AVERAGE E 29 E 38 AVERAGE by chengxiangzhou 星期一 星期二 星期三 星期四星期五 星期六 星期日 平均数 Temp3031322926282729 AVERAGE D4 J4 Rain00046312 AVERAGE D5 J5 星期一 星期二 星期三 星期四星期五 星期六 星期日 平均数 Temp30322926282728 6667 AVERAGE D8 J8 Rain0046312 33333 AVERAGE D9 J9 星期一 星期二 星期三 星期四星期五 星期六 星期日 平均数 Temp30No322926282728 6667 AVERAGE D12 J12 Rain0Reading046312 33333 AVERAGE D13 J13 功功能能 这个函数能够计算一组数字的平均数 如果某个单元格是空的或包含文本 它将不用于计算平均数 如果单元格数值为0 将将参于计算平均数 语语法法 AVERAGE Range1 Range2 Range3 through to Range30 格格式式 不需要具体的格式 说说明明 参数可以是数字 或者是包含数字的名称 数组或引用 如果数组或引用参数包含文本 逻辑值或空白单元格 则这些值将被忽略 但包含零值的单元格将计算在内 星期一 星期二 星期三 星期四星期五 星期六 星期日 平均数 Temp30No322926282724 5714 SUM D31 J31 COUNTA D31 J31 Rain0Reading046312 SUM D32 J32 COUNTA D32 J32 星期一 星期二 星期三 星期四星期五 星期六 星期日 平均数 Temp30322926282728 6667 SUM D35 J35 COUNTA D35 J35 Rain0046312 33333 SUM D36 J36 COUNTA D36 J36 提提示示 当对单元格中的数值求平均值时 应牢记空白单元格与含零值单元格的区别 尤其在 选项 对话框中的 视图 选项卡上已经清除了 零值 复选框的条件下 空白单元格不计算在内 但计算零值 若要查看 选项 对话框 单击 工具 菜单中的 选项 示示例例 数据 10 7 9 27 2 公式说明 结果 11 上面数 字的平 均值 11 AVERAGE B46 B50 10 上面数 字与 5 的平均 值 10 AVERAGE B46 B50 5 AVERAGE D4 J4 AVERAGE D5 J5 AVERAGE D8 J8 AVERAGE D9 J9 AVERAGE D12 J12 AVERAGE D13 J13 如果数组或引用参数包含文本 逻辑值或空白单元格 则这些值将被忽略 但包含零值的单元格将计算在内 SUM D31 J31 COUNTA D31 J31 SUM D32 J32 COUNTA D32 J32 SUM D35 J35 COUNTA D35 J35 SUM D36 J36 COUNTA D36 J36 空白单元格不计算在内 但计算零值 若要查看 选项 对话框 单击 工具 菜单中的 选项 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 CHOOSE bbb 索引数据结果 1Alan CHOOSE C4 Alan Bob Carol 3Carol CHOOSE C5 Alan Bob Carol 2Bob CHOOSE C6 Alan Bob Carol 318 CHOOSE C7 10 15 18 110 CHOOSE C8 10 15 18 215 CHOOSE C9 10 15 18 功功能能 可以使用 index num 返回数值参数列表中的数值 使用函数 CHOOSE 可以基于索引号返回多达 29 个基于 index number 待选数值中的任一数值 例如 如果数值 1 到 7 表示一个星期的 7 天 当用 1 到 7 之间的数字作 index num 时 函数 CHOOSE 返回其中的某一天 语语法法 CHOOSE UserValue Item1 Item2 Item3 through to Item29 详见下面 格格式式 没有固定的必须格式 示示例例1 下列表格可用于计算运动员比赛成绩及所得奖牌 时间为运动员起跑时间 使用 RANK 函数计算出完成比赛后的运动员排名 使用 CHOOSE 分配奖牌 使用 IF 排名小于等于3的 仅依此将引起错误 因为只有通过 CHOOSE 都能仅选择前三名 名字时间排名奖牌 Alan1 302Silver IF D30 3 CHOOSE D30 Gold Silver Bronze unplaced Bob1 154unplaced IF D31 3 CHOOSE D31 Gold Silver Bronze unplaced Carol2 451Gold IF D32 3 CHOOSE D32 Gold Silver Bronze unplaced David1 055unplaced IF D33 3 CHOOSE D33 Gold Silver Bronze unplaced Eric1 203Bronze IF D34 3 CHOOSE D34 Gold Silver Bronze unplaced RANK C34 C30 C34 语语法法 CHOOSE index num value1 value2 Index num 用以指明待选参数序号的参数值 Index num 必须为 1 到 29 之间的数字 或者是包含数字 1 到 29 的公式或单元格引用 如果 index num 为 1 函数 CHOOSE 返回 value1 如果为 2 函数 CHOOSE 返回 value2 以此 类推 如果 index num 小于 1 或大于列表中最后一个值的序号 函数 CHOOSE 返回错误值 VALUE 如果 index num 为小数 则在使用前将被截尾取整 Value1 value2 为 1 到 29 个数值参数 函数 CHOOSE 基于 index num 从中选择一个数值或 执行相应的操作 参数可以为数字 单元格引用 已定义的名称 公式 函数或文本 说说明明 如果 index num 为一个数组 则在函数 CHOOSE 计算时 每一个值都将计算 函数 CHOOSE 的数值参数不仅可以为单个数值 也可以为区域引用 例如 下面的公式 SUM CHOOSE 2 A1 A10 B1 B10 C1 C10 相当于 SUM B1 B10 然后基于区域 B1 B10 中的数值返回值 函数 CHOOSE 先被计算 返回引用 B1 B10 然后函数 SUM 用 B1 B10 进行求和计算 即函数 CHOOSE 的结果是函数 SUM 的参数 示示例例 2 数数据据数数据据 1stNails 2ndScrews 3rdNuts 完成Bolts 公公式式 说说明明 结结果果 2nd 第二个参数 A3 的值 2nd CHOOSE 2 B58 B59 B60 B61 Bolts 第四个参数 B5 的值 Bolts CHOOSE 4 C58 C59 C60 C61 示示例例 3 数数据据 23 45 12 10 公式 说说明明 结结果果 80 单元格区域 A2 A4 中 所有数值的 和 80 SUM B67 CHOOSE 2 B68 B69 B70 如果 index num 为 1 函数 CHOOSE 返回 value1 如果为 2 函数 CHOOSE 返回 value2 以此 类推 如果 index num 小于 1 或大于列表中最后一个值的序号 函数 CHOOSE 返回错误值 VALUE Value1 value2 为 1 到 29 个数值参数 函数 CHOOSE 基于 index num 从中选择一个数值或 COUNT 用于统计的项目统计 1020303 COUNT C4 E4 100303 COUNT C5 E5 10 20303 COUNT C6 E6 101 1月 88303 COUNT C7 E7 1021 30303 COUNT C8 E8 100 843955869303 COUNT C9 E9 10302 COUNT C10 E10 10Hello302 COUNT C11 E11 10 DIV 0 302 COUNT C12 E12 功功能能 返回包含数字以及包含参数列表中的数字的单元格的个数 利用函数 COUNT 可以计算单元格区域或数字数组 中数字字段的输入项个数 它忽略空格 文本和错误 语语法法 COUNT value1 value2 alue1 value2 为包含或引用各种类型数据的参数 1 到 30个 但只有数字类型的数据才被计算 格格式式 没有固定的格式 示示例例1 下面的表格适用于建筑物品供应商不同产品在每个月的销量预测销售收入 项目JanFebMar 砖 1 000 木材 5 000 玻璃 2 000 1 000 钢材 1 000 水泥320 COUNT D29 D32 说说明明 函数 COUNT 在计数时 将把数字 日期 或以文本代表的数字计算在内 但是错误值或其他无法转换成数字的文字将被忽略 如果参数是一个数组或引用 那么只统计数组或引用中的数字 数组或引用中的空白单元格 逻辑值 文字或错误值都将被忽略 如果要统计逻辑值 文字或错误值 请使用函数 COUNTA 示示例例2 2 数据 销售 39790 19 22 24 TRUE DIV 0 公式说明 结果 3 计算上列数据中包含数字的单 元格的个数 3 COUNT C44 C49 2 计算上列数据的最后 4 行中包 含数字的单元格的个数 2 COUNT C46 C49 4 计算上列数据中包含数字的单 元格以及包含数值 2 的单元格的 个数 4 COUNT C44 C49 2 alue1 value2 为包含或引用各种类型数据的参数 1 到 30个 但只有数字类型的数据才被计算 函数 COUNT 在计数时 将把数字 日期 或以文本代表的数字计算在内 但是错误值或其他无法转换成数字的文字将被忽略 如果参数是一个数组或引用 那么只统计数组或引用中的数字 数组或引用中的空白单元格 逻辑值 文字或错误值都将被忽略 如果要统计逻辑值 文字或错误值 请使用函数 COUNTA 如果参数是一个数组或引用 那么只统计数组或引用中的数字 数组或引用中的空白单元格 逻辑值 文字或错误值都将被忽略 如果要统计逻辑值 文字或错误值 请使用函数 COUNTA 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 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 2 这个示例使用函数 HLOOKUP 查找不同小车生产厂商不同配件的价值 函数 HLOOKUP 在B列不同厂商标题 找到厂商后 函数 HLOOKUP 又使用 MATCH 函数查找下一列C内配件列表中指定的行 公式中使用了绝对引用 为的是确保公式复制移动时函数 HLOOKUP 和 MATCH 引用的范围不发生变化 厂商配件价值 日本丰田火花塞 50日本丰田福特奔驰 奔驰变速箱 600变速箱500450600 福特引擎 1 200引擎10001200800 奔驰方向盘 275方向盘250350275 福特火花塞 70火花塞507045 福特刹车片 290刹车片 300290310 日本丰田变速箱 500 福特引擎 1 200 HLOOKUP B71 G 64 I 69 MATCH C71 F 65 F 69 0 1 FALSE 示示例例 3 3 下面的示例是一个建材经销商提供的不同采购数量的折扣率 价格表中显示了砖 木材和玻璃的单价 折扣表提供了不同产品不同采购数量的折扣率 采购表是采购预算 所有的预算结果显示在采购表中 产品名称列表在C列 单价是从价格表中获得的 FALSE选项表示产品名称在价格表中没有排序整理 使用FALSE强迫搜索精确匹配 如果没有找到 则函数显示错误 HLOOKUP C127 E111 G112 2 FALSE 折扣是从折扣表中获得的 如果采购数量与折扣表中某个值不匹配 函数 HLOOKUP将向下查找正确的匹配折扣 TRUE选项表示采购数量在折扣表中经过了升序排列整理 使用TRUE允许模糊匹配 如果采购数量在折扣表中没有找到匹配的值 则它下面较小的值将被使用 比如采购数量为125将向下与100匹配 并且使用100对应列的折扣率 NAME 价格表 砖砖木木材材玻玻璃璃 2 1 3 折扣表 1100300 砖砖0 6 8 木木材材0 3 5 玻玻璃璃0 12 15 采购表 项目采购数量单价折扣合计 砖125 26 235 木材200 13 194 玻璃150 312 396 砖225 26 423 木材50 10 50 玻璃500 315 1 275 单价 HLOOKUP C114 E 98 G 99 2 FALSE 折扣 HLOOKUP D114 E 102 G 105 MATCH C114 D 103 D 105 0 1 TRUE 示示例例4 密度粘度温度 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 100 在 A 列中查找 1 并从相同行的 C 列 中返回值 100 N A 在 A 列中查找 0 746 因为 A 列 中没有精确地匹配 所以返回了一个 错误值 N A N A 在 A 列中查找 0 1 因为 0 1 小 于 A 列的最小值 所以返回了一个错 误值 N A 1 71 在 A 列中查找 2 并从相同行的 B 列 中返回值 1 71 说说明明 如果函数 VLOOKUP 找不到 lookup value 且 range lookup 为 TRUE 则使用小于等于 lookup value 的最大值 如果 lookup value 小于 table array 第一列中的最小数值 函数 VLOOKUP 返回错误值 N A 如果函数 VLOOKUP 找不到 lookup value 且 range lookup 为 FALSE 函数 VLOOKUP 返 回错误值 N A HLOOKUP F10 D3 F10 F11 FALSE 如果 lookup value 小于 table array 第一列中的最小数值 函数 VLOOKUP 返回错误值 N A COUNTBLANK 试验列空白格数 12 COUNTBLANK C4 C11 程香宙 3 0 1 1月 98 5 功功能能 该函数用于统计一个区域中空白单元格的个数 语语法法 COUNTBLANK Range Range 为需要计算其中空白单元格个数的区域 格格式式 没有专门的格式 示示例例1 以下是一张某公司调查表 用来统计抽烟情况 调查对象是多个工厂的不同部门 回答问题只能用Y或N 将结果填入下面表格 用 COUNTBLANK 可以统计出没有登记的数目 部门1部门2部门3部门4 工厂1YN 工厂2YYN 工厂3 工厂4NNN 工厂5YY 工厂6YYYN 工厂7NY 工厂8NNYY 工厂9Y 工厂10YNY 没登记的16 COUNTBLANK C32 F41 回答Yes的14 COUNTIF C32 F41 Y 回答No的10 COUNTIF C32 F41 N 示示例例2 数据数据 6 27 434 公式 说明 结果 4 计算上述区 域中空白单 元格的个数 公式返回 空文本 4 COUNTBLANK C54 D57 说说明明 即使单元格中含有返回值为空文本 的公式 该单元格也会计算在内 但包含零值的单元格不计算在内 VLOOKUP 列数并非必需的 它只是便于在不便中说明 列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变速箱500450600 福特引擎 1 200引擎10001200800 奔驰方向盘 275方向盘250350275 福特火花塞 70火花塞507045 福特刹车片 290刹车片 300290310 日本丰田变速箱 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 0 木木材材 11006 3 12 玻玻璃璃 33008 5 15 采购表 项目采购数量单价折扣合计 砖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