EXCEL常用计算公式大全

1 1 1 EXCEL常用计算公式大 全 日常办公中少不了 EXCEL 表格的运用 方便 快速 高效 EXCEL 表格是基于计算公式而运行的 只有掌握了基本公式 才能完成各项运算 经过长时间收集 整理了一些常用工具 基本能够满足日常运用 贵州龙山源酒业有限公司 贵州贞丰 联系人 石启洪 qq 478094808 1 1 3 4 EXCEL 常用计算公式大全 5 6 文 石启洪 整理 7 8 整理说明 9 EXCEL 是日常办公中少不了的运算工具 方便 快速 高效 10 由于 EXCEL 表格是基于计算公式而运行的 只有掌握了基本公式 才能完成各项运算 经过长 11 时间收集 整理了一些常用工具 基本能够满足日常运用 12 公式由 包括的为数组公式 在复制粘贴到单元后先去掉 然后按住 Shift 键 Ctrl 键再按 13 Enter 键 自动生成数组公式 14 15 16 17 18 19 20 21 22 23 24 25 26 27 2 2 28 29 30 31 32 33 34 目 录 35 常用求和运算 3 36 1 单组数据加减乘除运算 3 37 2 多组数据加减乘除运算 3 38 3 其它应用函数代表 5 39 和字篇 5 40 1 求和 5 41 2 与和 5 42 产字篇 7 43 1 生产 7 44 2 产量 7 45 3 产品 8 46 四 产值 10 47 5 产生 10 48 大小于篇 10 49 一 大于 11 50 2 小于 12 51 数据篇 12 52 工资篇 14 53 价格篇 15 54 计算篇 16 3 3 55 库房篇 27 56 一 进库 28 57 二 出库 28 58 三 库存 28 59 成字篇 28 60 一 成绩 28 61 二 与成 31 62 金字篇 33 63 一 金额 33 64 二 奖金 35 65 时间篇 35 66 1 时间 35 67 2 年 37 68 3 月 39 69 4 日 41 70 销售篇 44 71 72 73 EXCEL常用计算公式大全 74 75 常用求和运算 76 一 单组数据加减乘除运算 77 1 单组数据求加和公式 A1 B1 78 举例 单元格 A1 B1 区域依次输入了数据 10 和 5 计算 在 C1 中输入 A1 B1 后点击键盘 Enter 确 79 定 键后 该单元格就自动显示 10 与 5 的和 15 4 4 80 2 单组数据求减差公式 A1 B1 81 举例 在 C1 中输入 A1 B1 即求 10 与 5 的差值 5 电脑操作方法同上 82 3 单组数据求乘法公式 A1 B1 83 举例 在 C1 中输入 A1 B1 即求 10 与 5 的积值 50 电脑操作方法同上 84 4 单组数据求乘法公式 A1 B1 85 举例 在 C1 中输入 A1 B1 即求 10 与 5 的商值 2 电脑操作方法同上 86 5 其它应用 87 在 D1 中输入 A1 3 即求 5 的立方 三次方 88 在 E1 中输入 B1 1 3 即求 10 的立方根 89 90 小结 91 在单元格输入的含等号的运算式 Excel 中称之为公式 都是数学里面的基本运算 只不过在计算机上有的 92 运算符号发生了改变 与 同 与 同 与 乘方 相同 开方作为乘方的逆运 93 算 把乘方中和指数使用成分数就成了数的开方运算 94 这些符号是按住电脑键盘 Shift 键同时按住键盘第二排相对应的数字符号即可显示 95 如果同一列的其它单元格都需利用刚才的公式计算 只需要先用鼠标左键点击一下刚才已做好公式的单元 96 格 将鼠标移至该单元格的右下角 带出现十字符号提示时 开始按住鼠标左键不动一直沿着该单元格依次往 97 下拉到你需要的某行同一列的单元格下即可 即可完成公司自动复制 自动计算 98 99 二 多组数据加减乘除运算 100 1 多组数据求加和公式 常用 101 举例说明 SUM A1 A10 表示同一列纵向从 A1 到 A10 的所有数据相加 5 5 102 SUM A1 J1 表示不同列横向从 A1 到 J1 的所有第一行数据相加 103 2 多组数据求乘积公式 较常用 104 举例说明 PRODUCT A1 J1 表示不同列从 A1 到 J1 的所有第一行数据相乘 105 PRODUCT A1 A10 表示同列从 A1 到 A10 的所有的该列数据相乘 106 3 多组数据求相减公式 很少用 107 举例说明 A1 SUM A2 A10 表示同一列纵向从 A1 到 A10 的所有该列数据相减 108 A1 SUM B1 J1 表示不同列横向从 A1 到 J1 的所有第一行数据相减 109 4 多组数据求除商公式 极少用 110 举例说明 A1 PRODUCT B1 J1 表示不同列从 A1 到 J1 的所有第一行数据相除 111 A1 PRODUCT A2 A10 表示同列从 A1 到 A10 的所有的该列数据相除 112 三 其它应用函数代表 113 1 平均函数 AVERAGE 114 2 最大值函数 MAX 115 3 最小值函数 MIN 116 4 统计函数 COUNTIF B5 60 117 说明 统计分数大于 60 分的人数 注意 条件要加双引号 在英文状态下输入 118 和字篇 119 一 求和 120 1 对三组生产数据求和 SUM B2 B7 D2 D7 F2 F7 121 2 对生产表中大于 100 的产量进行求和 SUM B2 B11 100 B2 B11 122 3 对生产表大于 110 或者小于 100 的数据求和 SUM B2 B11110 B2 B11 6 6 123 4 对一车间男性职工的工资求和 SUM B2 B10 一车间 C2 C10 男 D2 D10 124 5 对姓赵的女职工工资求和 SUM LEFT A2 A10 赵 C2 C10 女 D2 D10 125 6 LEFT left 解释 就是最左边的 最开始的 如 石启洪 最左边就是 石 对应 RIGHT right 最右 126 边的 最后的 127 7 对 1400 到 1600 之间的工资求和 SUM SUMIF B2 B10 60 B2 B1160 B2 B11 80 ROW 2 11 0 132 11 既求积也求和 IF D2 PRODUCT C2 D2 SUM OFFSET E2 3 3 133 12 按文字描述求和 SUM ISNUMBER FIND A 2 A 8 D2 B 2 B 8 134 13 求积 求和两相宜 SUM IF C2 INDIRECT E 支出 154 SUM IF SUBSTITUTE IF B2 B13 B2 B13 0 负 1LARGE B2 C11 11 B2 C11 166 22 计算 8 月份笔筒和毛笔的进货数量 SUM IF MONTH A2 A11 8 IF B1 H1 笔筒 B1 H1 毛笔 B2 H11 167 168 23 统计家具类和文具类产品在 1 月份的出库次数 SUM B2 B11 文具类 家具类 169 IF C2 C11 0 MONTH C2 C11 1 170 24 计算员工工作天数和月数 DATEDIF B2 C2 M 171 25 对班级和成绩升序排列 1 MID SMALL 1 A2 A12名 OFFSET A 1 MOD MAX SUBTOTAL 9 OFFSET B 1 ROW 2 31 1 1 COLUMNS C I 177 1000 ROW 2 31 B 2 B 31 K2 1000 1 178 28 根据评委评分和权重分配统计最后得分 SUM B2 F8 A2 A8 B10 TRANSPOSE I2 I6 179 29 区分工种和达标率计算奖金 LOOKUP C2 100 1 LEFT 达标与奖金标准 B 1 K 1 FIND 达标与奖金标准 180 B 1 K 1 1 OFFSET 达标与奖金标准 B 1 MATCH B2 达标与奖金标准 A 2 A 4 0 10 8 8 181 30 提取每日累计出库数和每日库存数 日期 INDEX A A ROW A1 2 累计出库数 182 SUM ISODD ROW INDIRECT 2 每日库存数 183 SUM SUMIF OFFSET B 1 1 ROW A1 2 进库 出库 C 2 1 1 184 31 计算生产部人数和非生产部人数 生产部人数 SUM NOT ISERR FIND 车间 A2 A11 B2 C11 非生产 185 部人数 SUM ISERR FIND 车间 A2 A11 B2 C11 186 32 根据利息和存款数计算存款达到 1 万元需要几个月 NPER A2 0 B2 C2 12 187 33 根据投资金额 时间和目标收益计算增长率 RATE B2 0 A2 C2 188 34 根据贷款 利率和时间计算某段时间的利息 CUMIPMT B2 12 C2 12 A2 1 24 0 189 35 根据贷款 利率和时间计算需偿还的本金 CUMPRINC B2 12 C2 12 A2 1 24 0 190 36 以年限总和折旧法计算折旧值 SYD A 2 B 2 C 2 ROW A1 191 192 产字篇 193 一 生产 194 1 对三组生产数据求和 SUM B2 B7 D2 D7 F2 F7 195 2 对生产表中大于 100 的产量进行求和 SUM B2 B11 100 B2 B11 196 3 对生产表大于 110 或者小于 100 的数据求和 SUM B2 B11110 B2 B11 197 4 计算每小时生产产值 PRODUCT C2 E2 198 5 生产 A 产品且无异常的机台平均产量 AVERAGEIFS C2 C11 B2 B11 A D2 D11 199 6 根据员工生产产品的废品率记分 MAX MIN 6 B2 100 5 10 0 200 7 哪种产品生产次数最多 TEXT MODE B2 B9 1 00 201 8 本月需要完成几批货物生产 SUM N B2 B11 TEXT TODAY MMMM 202 9 计算生产车间异常机台个数 COUNT C2 C11 203 10 计算生产速度是否达标 YEARFRAC C2 D2 250 215 6 统计产量达标率 TEXT COUNTIF B2 B11 800 COUNT B2 B11 0 00 216 7 计算大于等于前 10 个最大产量之和 SUMPRODUCT B2 C11 LARGE B2 C11 11 B2 C11 217 8 按周汇总产量 SUM WEEKDAY B1 2 WEEKDAY B1 AF1 2 COLUMN B1 AF1 1 1 COLUMN A1 1 218 7 B2 AF2 219 9 计算所有人的一周产量并排名 INDEX 1 1 RIGHT LARGE SUBTOTAL 9 OFFSET A2 A8 COLUMN B J 1 220 10 COLUMN B J 1 COLUMN A1 1 221 10 根据下拉列表引用不同工作表的产量 INDIRECT ADDRESS 11 2 1 1 D1 222 11 计算连续三天之总产量大于等于 25 万元的次 223 13 计算产量最高的季度 TEXT MATCH MAX SUBTOTAL 9 OFFSET A1 0 3 6 9 1 3 224 SUBTOTAL 9 OFFSET A1 0 3 6 9 1 3 0 DBNum1 0 季度 225 14 罗列 12 月中产量倒数第一名次数最多者名单 INDEX B B SMALL IF COUNTIF B 2 B 13 B 2 B 13 226 MAX COUNTIF B 2 B 13 B 2 B 13 MATCH B 2 B 13 B 2 B 13 0 ROW 2 13 10 10 227 1 ROW 2 13 1048576 ROW A1 1 1 1 268 21 罗列导致产品不良的主因 269 IFERROR T INDEX A A SMALL IF B 2 B 11 LARGE IF FREQUENCY B 2 B 11 B 2 B 11 B 2 B 11 R 270 OW A1 ROW 2 11 COLUMN A1 271 22 计算两个产品不同时期的单价 HLOOKUP MONTH A2 IF B2 塑胶机 0 3 8 25 19 18 272 0 5 10 12 5 10 11 2 12 12 273 23 产生混合编号 TEXT COUNTIF C 1 C1 DBNum2 274 24 计算产品体积 IF ISERROR FIND B2 B2 3 PRODUCT 1 TRIM MID SUBSTITUTE B2 REPT 100 275 1 100 200 100 276 25 按月份统计每个产品的机器返修数量 SUMPRODUCT ISNUMBER FIND F 2 A 2 A 11 277 TEXT B 2 B 11 YM TEXT E3 YM C 2 C 11 278 26 从产品规格中提取直径 长 宽 长 直径 LOOKUP 9 9E 307 RIGHT IF ISNUMBER FIND A2 279 REPLACE A2 FIND A2 100 A2 ROW 1 100 宽 IF ISNUMBER FIND A2 RIGHT A2 LEN A2 280 FIND A2 0 281 27 根据产品规格计算体积 体积 EVALUATE SUBSTITUTE SUBSTITUTE SUBSTITUTE Sheet1 XFD4 L W 282 H 283 禁止录入不完整的产品规格 数据有效性设置 自定义 ISNUMBER SEARCH 长 宽 高 B2 284 285 四 产值 286 1 计算每小时生产产值 PRODUCT C2 E2 287 2 每隔 4 行合计产值 IF MOD ROW 5 1 SUM OFFSET F2 4 4 D2 E2 288 289 五 产生 290 1 产生 100 到 200 之间带小数的随机数 RAND 200 100 100 291 2 产生 ll 到 20 之间的不重复随机整数 RANK A2 A11 A2 A11 10 292 3 产生 50 到 100 之间的随机整数 RANDBETWEEN 50 100 293 4 产生 1 到 100 之问的奇数随机数 INDEX IF MOD ROW 1 100 2 ROW 1 100 ROW 1 100 1 294 RANDBETWEEN 1 100 295 5 产生 1 到 10 之间随机不重复数 LARGE IF COUNTIF A 1 A1 ROW 1 10 0 ROW 1 10 13 13 296 RANDBETWEEN 1 12 ROW 297 6 根据学生成绩自动产生评语 IF AVERAGE B2 D2 60 不及格 IF AVERAGE B2 D2 90 良好 298 IF AVERAGE B2 D2 100 优秀 满分 299 7 产生大 小写字母 A 到 Z 的序列 大写字母 CHAR ROW A65 小写字母 CHAR ROW A65 32 300 8 产生大写字母 A 到 ZZ 的字母序列 IF ROW 26 CHAR MOD ROW 1 26 65 302 9 产生三个字母组成的随机字符串 CHAR RANDBETWEEN 65 90 小于 文本 325 5 计算每个车间大于 250 的平均产量 AVERAGEIF B2 C11 250 326 6 统计大于 80 分的三好学生个数 COUNTIFS B2 B11 三好学生 C2 C11 80 327 7 计算语文成绩大于 90 分者的最高总成绩 DMAX A1 E11 5 G1 G2 328 8 计算大于等于前 10 个最大产量之和 SUMPRODUCT B2 C11 LARGE B2 C11 11 B2 C11 329 9 计算第一次收入金额大于 30 元时的金额是多少 INDEX B B MIN IF A2 A11 A2 B2 B11 30 ROW 2 11 330 10 记录最后一次销量大于 3000 的地址 ADDRESS MOD MAX IF ISNUMBER B2 D7 B2 D7 0 3000 ROW B2 D7 331 IF ISNUMBER B2 D7 B2 D7 0 3000 COLUMN B2 D7 1000 1000 INT MAX IF ISNUMBER B2 D7 B2 D7 0 332 3000 ROW B2 D7 IF ISNUMBER B2 D7 B2 D7 0 3000 COLUMN B2 D7 1000 1000 333 11 计第奎续三天之总产量大于等于 25 万元的次数 SUMPRODUCT N SUBTOTAL 9 OFFSET B 1 ROW 1 10 1 3 334 25 335 12 统计各班所有科目成绩大于 60 分者人数 MMULT N TRANSPOSE A2 A21 H3 H6 336 N COUNTIF OFFSET C2 F2 ROW 2 21 2 60 4 337 13 对生产表大于 110 或者小于 100 的数据求和 SUM B2 B11110 B2 B11 338 339 3 小于 15 15 340 1 判断单元格的数据类型 TEXT A2 大于 小于 文本 341 2 对生产表大于 110 或者小于 100 的数据求和 SUM B2 B11110 B2 B11 342 343 数据篇 344 1 对三组生产数据求和 SUM B2 B7 D2 D7 F2 F7 345 2 对生产表大于 110 或者小于 100 的数据求和 SUM B2 B11110 B2 B11 346 37 求所有工作表相同区域数据求和 SUM A 组 E 组 B2 B9 347 3 求前三名和后三名的数据求和 SUMIF B2 B10 小于 文本 16 16 361 16 将三列数据交换位置 TEXT 1 1 0 C1 C5 0 B2 H2 A2 G2 2 FALSE 381 33 不区分大小写判断两列相同数据个数 COUNT MATCH A2 A11 B2 B11 0 382 34 提取 A 列最后一个数据 INDIRECT A sheets GET WORKBOOK 1 392 WorkBook GET CELL 66 IFERROR REPLACE INDEX sheets SMALL IF TRANSPOSE sheets AcSht ROW INDIRECT 393 1 女 SUM 461 ISEVEN MID B2 B10 15 3 462 10 设置 D 列只能录入男职工的姓名 数据有效性设置 自定义 VLOOKUP D2 A B 2 0 男 463 464 计算篇 465 1 计算仓库进库数量之和 SUMIF B2 B10 进库 C2 C10 466 2 计算仓库大额进库数量之和 SUMIF B2 B8 1000 467 3 计算车间男性与女性人员的差 SUM SUMIFS C2 C11 B2 B11 女 男 A2 A11 车间 1 1 468 4 计算参保人数 SUMPRODUCT C2 C11 是 1 469 5 计算 A1 A10 区域正数的平方和 SUMSQ IF A1 A10 0 A1 A10 470 6 计算 1 到 10 的自然数的积 FACT 10 471 7 计算 50 到 60 之间的整数相乘的结果 FACT 60 FACT 49 21 21 472 8 计算 1 到 15 之间奇数相乘的结果 FACTDOUBLE 15 473 9 计算每小时生产产值 PRODUCT C2 E2 474 10 计算车间盈亏 SUM MMULT B3 E5 0 B3 E5 1 1 1 1 MMULT B3 E5 TRANSPOSE ROW 2 11 B2 B11 478 14 计算每日库存数 MMULT N ROW 2 11 TRANSPOSE ROW 2 11 B2 B11 C2 C11 479 15 计算 A 产品每日库存数 MMULT N ROW 2 17 TRANSPOSE ROW 2 17 B2 B17 A C2 C17 D2 D17 480 16 在不同班级有同名前提下计算学生人数 481 SUM 1 MMULT N A2 A17 0 0 00 537 61 计算字母 A 的首次出现位置 忽略大小写 TEXT SEARCH a A2第 24 24 538 1 1 0 0 0 5 0 0 540 63 计算星期日完工的工程个数 COUNT TEXT B2 B10 C2 C10 1 AAA 日 0 541 64 计算本月星期日的个数 SUM N TEXT TODAY TEXT TODAY d 542 ROW INDIRECT 1 罗列 654 约数 IFERROR SMALL IF MOD A 2 ROW INDIRECT 1 1 1 1 1 1000 ROW 2 11 683 ROW 1 10 1000 1 684 166 计算五个地区销售利润 TRANSPOSE MMULT 1 1 1 1 1 1 1 1 1 1 SUBTOTAL 6 OFFSET INDIRECT 华东 685 区 华南区 华北区 华中区 西南区 名 OFFSET A 1 MOD MAX SUBTOTAL 9 OFFSET B 1 ROW 2 31 1 1 COLUMNS C I 691 1000 ROW 2 31 B 2 B 31 K2 1000 1 692 169 计算哪一个月完成目标 OFFSET A1 LOOKUP 1 SUBTOTAL 9 OFFSET B1 1 0 ROW 2 12 1 200 693 ROW 2 12 694 170 计算 10 个月中的销售利润并排名 OFFSET A1 MOD LARGE INT MMULT SUBTOTAL 6 OFFSET INDIRECT 华 695 东区 华南区 华北区 华中区 西南区 1 1 1 1 1000 ROW 2 11 696 ROW 1 10 1000 1 697 171 计算犯规低于 3 次的人数 SUM N MMULT B2 B21 TRANSPOSE B2 B21 ROW 2 21 0 1 2 1 2 698 172 计算 60 分到 95 分之间的人员个数 INDEX FREQUENCY B2 B11 60 95 2 31 31 699 173 计算两个产品不同时期的单价 HLOOKUP MONTH A2 IF B2 塑胶机 0 3 8 25 19 18 700 0 5 10 12 5 10 11 2 701 174 根据进厂日期计算有薪假天数 HLOOKUP DATEDIF B2 TODAY y 0 1 3 5 7 10 0 2 3 5 7 10 2 702 175 计算补课科目总数 COUNT 0 MATCH B2 B8 B2 B8 0 ROW 2 8 1 703 176 计算某班六年中谁获第一名次数最多 MAX COUNTIF B2 B7 B2 B7 704 177 计算各厂商参赛人数 IFERROR LOOKUP SMALL IF A 2 A 21 ROW 2 21 ROW A1 ROW 2 21 705 A 2 A 21 1 706 178 计算购货金额 LOOKUP 9E 307 MID A2 MATCH 0 0 MID A2 ROW 1 1000 1 0 ROW 1 15 707 LOOKUP 9E 307 LEFT REPLACE A2 1 FIND A2 ROW 1 1000 708 179 从销售记录中提取销量与单价并计算金额 LOOKUP 10 16 RIGHT REPLACE A2 FIND 公斤 A2 100 709 ROW 1 100 LOOKUP 10 16 RIGHT REPLACE A2 FIND 元 A2 100 ROW 1 100 710 180 计算每个职工的得分 LOOKUP FIND B2 A A A B B B C C C D 11 711 ROW 1 10 712 181 根据产量计算员工产量得分 LOOKUP B2 3 0 5 ROW 1 11 1 713 182 区分工种和达标率计算奖金 LOOKUP C2 100 1 LEFT 达标与奖金标准 B 1 K 1 FIND 达标与奖金标准 714 B 1 K 1 1 OFFSET 达标与奖金标准 B 1 MATCH B2 达标与奖金标准 A 2 A 4 0 10 715 183 计算今天是本月的上旬 中旬还是下旬 CHOOSE MIN CEILING DAY TODAY 10 1 3 上旬 中旬 716 下旬 717 184 计算期末平均成绩 AVERAGE IF ISEVEN COLUMN B I 1 B3 I3 718 185 计算生产部人数和非生产部人数 生产部人数 SUM NOT ISERR FIND 车间 A2 A11 B2 C11 非生产 719 部人数 SUM ISERR FIND 车间 A2 A11 B2 C11 720 186 计算产品体积 IF ISERROR FIND B2 B2 3 PRODUCT 1 TRIM MID SUBSTITUTE B2 REPT 100 721 1 100 200 100 722 187 数字 字母与汉字个数计算 数字个数 SUM ERROR TYPE INDIRECT XFD 前 737 EVALUATE 累计出库数 763 SUM ISODD ROW INDIRECT 2 每日库存数 764 SUM SUMIF OFFSET B 1 1 ROW A1 2 进库 出库 C 2 1 1 765 766 3 库存 767 1 按编码计算库存总数 SUM ISNUMBER FIND 名次 814 RANK I2 B2 B11 D2 D11 F2 F11 0 815 32 计算第 11 名到第 30 名学员的平均成绩 AVERAGE IF RANK B2 B101 B2 B101 TRANSPOSE ROW 11 30 816 B2 B101 817 33 计算成绩排名 不能产生并列名次 SUMPRODUCT A 2 A 15 A2 C 2 C 15 1 ROW C 2 C 15 818 C2 1 ROW 2 2 1 819 34 对班级和成绩升序排列 1 MID SMALL 1 A2 A12 二班 三班 830 1 ROWS 2 11 831 41 计算每个学生总分是否高于本班平均成绩 SUM C2 E2 AVERAGE IF A2 A 2 A 11 832 SUBTOTAL 9 OFFSET B 1 ROW 2 11 1 1 COLUMNS C E 833 42 罗列平均成绩倒数三名的班级 OFFSET A1 MATCH SMALL SUBTOTAL 1 OFFSET A1 ROW 2 9 834 1 1 1 COLUMNS B F 1000 ROW 2 9 ROW 1 3 SUBTOTAL 1 OFFSET A1 ROW 2 9 835 1 1 1 COLUMNS B F 1000 ROW 2 9 836 43 分别计算每个班第一名的成绩和姓名 名次 MAX SUBTOTAL 9 OFFSET B 1 ROW 2 31 1 1 COLUMNS C I 837 B 2 B 31 K2 名 OFFSET A 1 MOD MAX SUBTOTAL 9 OFFSET B 1 ROW 2 31 1 1 COLUMNS C I 838 1000 ROW 2 31 B 2 B 31 K2 1000 1 839 44 计算期末平均成绩 AVERAGE IF ISEVEN COLUMN B I 1 B3 I3 840 45 提取期末成绩明细 INDEX 成绩表 1 1 SMALL IF ISEVEN COLUMN B I ROW 1 COLUMN B I 841 COLUMN A1 842 46 罗列某运动员九次参赛成绩 INDEX 1 1 MAX ISTEXT B2 E2 COLUMN B E 843 47 统计各班所有科目成绩大于 60 分者人数 MMULT N TRANSPOSE A2 A21 H3 H6 844 N COUNTIF OFFSET C2 F2 ROW 2 21 2 60 4 845 48 提取第一次参赛取得最佳成绩者姓名与成绩 参赛者 INDEX A A MOD MAX IF NOT ISBLANK C2 C11 846 MATCH A2 A11 A A 0 ROW 2 11 C2 C11 100 ROW 2 11 100 成绩 MAX IF NOT ISBLANK C2 C11 847 MATCH A2 A11 A A 0 ROW 2 11 C2 C11 848 49 将单元格的公式转换成数值 计算 EVALUATE Sheet1 A3 849 50 按分隔符取数并求平均 成绩 EVALUATE SUBSTITUTE 851 51 将成绩高于平均值的姓名标示 优等 条件格式 B2 AVERAGE B 2 F 10 MOD COLUMN 2 852 853 二 与成 854 52 汇总姓赵 刘 李的业务员提成金额 SUM SUMIF A2 A10 赵 刘 李 0 0 00 874 71 将数字转化成电话格式 TEXT A2 0000 0000 0000 875 72 将分数转换成小数 LEFT A2 FIND A2 1 RIGHT A2 LEN A2 FIND A2 2 876 73 统计未检验完成的产品数 COUNTBLANK B2 B11 877 74 计算最多经过几次测试才成功 MAX FREQUENCY ROW 2 11 B2 B11 成功 ROW 2 11 878 75 本月需要完成几批货物生产 SUM N B2 B11 TEXT TODAY MMMM 879 76 将身份证号码转换成出生日期序列 DATE MID B2 7 2 LEN B2 18 2 MID B2 9 LEN B2 18 2 2 880 MID B2 11 LEN B2 18 2 2 881 77 生成工资结算日期 TEXT EOMONTH B2 0 1 e 年 M 月 D 日 882 78 将三列课程转换成单列且忽略空值 INDIRECT TEXT SMALL IF B 2 D 7 ROW 2 7 883 1000 1 1048576001 ROW A1 r c000 60 C 80 B 90 A 2 38 38 887 81 将中文大写编号转换成阿位伯数字小写 TEXT LOOKUP 1 0 B2 TEXT ROW 1 1000 DBNum2 000 888 ROW 1 1000 000 889 82 将字母转换成评分 AVERAGE LOOKUP B2 I2 A B C D E 10 9 5 8 7 5 890 83 将字母转换成评分并对选手排名 LOOKUP MOD LARGE MMULT LOOKUP B 2 I 7 A B C D E 891 10 9 5 8 7 5 ROW 1 8 0 10000 ROW 2 7 ROW A1 10 ROW 2 7 A 2 A 7 892 84 将按日期排列的销售表转换成按品名排列 IFERROR VLOOKUP A2 IF MATCH ROW 1 15 IF ISTEXT 日期 893 A 1 A 15 MATCH 日期 A 1 A 15 日期 A 1 A 15 0 MATCH B 1 日期 A 1 A 15 0 日期 B 1 C 15 894 2 0 895 896 金字篇 897 一 金额 898 1 汇总姓赵 刘 李的业务员提成金额 SUM SUMIF A2 A10 赵 刘 李 见角进元 CEILING TRUNC A2 1 1 906 8 分别统计收支金额并忽略小数 收入合计 SUMPRODUCT INT B2 B8 支出合计 SUMPRODUCT TRUNC C2 C8 907 9 统计交易损失金额 SUMPRODUCT B2 B11 CEILING B2 B11 0 1 908 10 统计业务员提成金额 不足 20000 元忽略 FLOOR B2 20000 20000 500 909 11 将统计金额保留到分位 ROUND SUMPRODUCT B2 B10 C2 C10 2 910 12 将统计金额转换成以万元为单位 ROUND SUMPRODUCT B2 B10 C2 C10 911 13 对单价计量单位不同的品名汇总金额 SUM ROUND B2 B10 C2 C10 IF D2 D10 G 1000 1 D2 D10 G 2 912 913 14 将金额保留 角 位 忽略 分 位 SUM ROUNDDOWN B2 B10 C2 C10 1 914 15 判断学生是否符合奖学金发放条件 AND B2 90 C2 汉族 915 16 混合单价求金额合计 SUM ROUND B2 B10 C2 C10 IF D2 D10 K 1000 1 2 39 39 916 17 将数字金额显示为人民币大写 IF MOD B2 1 0 TEXT INT B2 dbnum2 G 通用格式元整 负 dbnum2 G 通 917 用格式元整 零元整 IF B2 0 负 918 零角 IF ABS B2 0 零 919 零分 920 18 将汇总金额保留一位小数并显示千分位分隔符 FIXED SUM FIXED B2 B11 C2 C11 1 1 FALSE 921 19 计算订单金额并以 百万 为单位显示 FIXED SUMPRODUCT B2 B10 C2 C10 6 1000000 922 20 计算购物金额中小数位数最多是几 MAX LEN B2 B10 C2 C10 LEN INT B2 B10 C2 C10 1 923 21 将金额分散填充 LEFT RIGHT 2 3 4 5 6 7 8 9 B21 1 1 1 A2 A11 A 组 945 B 组 C 组 946 36 引用销售金额高于 200 次数最多者 INDEX A A RIGHT MAX MMULT B2 H9 200 1 TRANSPOSE COLUMN B H 947 0 10 ROW 2 9 948 37 计算购货金额 LOOKUP 9E 307 MID A2 MATCH 0 0 MID A2 ROW 1 1000 1 0 ROW 1 15 949 LOOKUP 9E 307 LEFT REPLACE A2 1 FIND A2 ROW 1 1000 950 38 从销售记录中提取销量与单价并计算金额 LOOKUP 10 16 RIGHT REPLACE A2 FIND 公斤 A2 100 951 ROW 1 100 LOOKUP 10 16 RIGHT REPLACE A2 FIND 元 A2 100 ROW 1 100 952 39 根据投资金额 时间和目标收益计算增长率 RATE B2 0 A2 C2 953 40 根据贷款 利率和时间计算需偿还的本金 CUMPRINC B2 12 C2 12 A2 1 24 0 954 41 以固定余额递减法计算资产折旧值 DB A 2 B 2 C 2 ROW A1 12 955 42 使用双倍余额递减法计算任何期间的资产折旧值 VDB A 2 B 2 C 2 12 7 12 2 956 957 2 奖金 958 1 根据业绩计算业务员奖金 CHOOSE MIN QUOTIENT B2 10000 1 6 0 3 5 7 9 11 B2 959 2 根据业绩计算需要发放多少奖金 SUM IF B2 B11 80000 1000 500 960 3 根据达标率计算员工奖金 MAX B2 0 0 8 0 9 1 1 05 200 250 300 450 550 961 4 根据工程的难度系数计算奖金 MIN A2 1 A2 1 3 0 3 500 962 5 根据完工时间计算奖金 IF HOUR B2 18 ROUNDUP HOUR B2 18 00 60 MINUTE B2 30 0 963 3 ROUNDDOWN HOUR 18 00 B2 60 60 MINUTE B2 30 0 3 964 6 按金 银 铜牌排名次 MATCH B2 B11 C2 C11 D2 D11 LARGE B2 B11 C2 C11 D2 D11 ROW 2 11 1 965 0 966 7 区分工种和达标率计算奖金 LOOKUP C2 100 1 LEFT 达标与奖金标准 B 1 K 1 FIND 达标与奖金标准 967 B 1 K 1 1 OFFSET 达标与奖金标准 B 1 MATCH B2 达标与奖金标准 A 2 A 4 0 10 968 8 计算周末奖金补贴 SUMPRODUCT N WEEKDAY ROW INDIRECT B2 1 1 0 0 0 5 0 0 1014 9 将年级或者专业与班级名称分开 REPLACE A2 MAX IFERROR SEARCH CHAR ROW 65 90 A 1015 10 通过身份证号码计算年龄 TEXT TODAY YYYY IF LEN B2 18 19 1 1 1 1 1000 ROW 2 11 1116 ROW 1 10 1000 1 1117 34 计算哪一个月完成目标 OFFSET A1 LOOKUP 1 SUBTOTAL 9 OFFSET B1 1 0 ROW 2 12 1 200 1118 ROW 2 12 1119 35 有几次连续三个月的平均值低于整体平均值 SUM N SUBTOTAL 9 OFFSET B4 ROW 2 11 2 3 2 1120 36 计算 10 个月中的销售利润并排名 OFFSET A1 MOD LARGE INT MMULT SUBTOTAL 6 OFFSET INDIRECT 华东 1121 区 华南区 华北区 华中区 西南区 1 1 1 1 1000 ROW 2 11 1122 ROW 1 10 1000 1 46 46 1123 37 罗列 12 月中产量倒数第一名次数最多者名单 INDEX B B SMALL IF COUNTIF B 2 B 13 B 2 B 13 1124 MAX COUNTIF B 2 B 13 B 2 B 13 MATCH B 2 B 13 B 2 B 13 0 ROW 2 13 1125 1 ROW 2 13 1048576 ROW A1 日期有误 1153 12 在 A1 A7 区域产生星期一到星期日的英文全称 TEXT ROW 1 7 1 DDDD 1154 13 将日期规范化再求差 SUBSTITUTE C2 SUBSTITUTE B2 1155 14 从格式不规范的日期中取出日 TRIM RIGHT SUBSTITUTE A2 2 3 47 47 1156 15 计算单日最高销售金额 MAX SUMIF A2 A11 A2 A11 C2 C11 1157 16 显示动态日期 但不能超过 9 月 30 日 MIN 2008 9 30 TODAY 1158 17 记录当前日期与时间 TEXT NOW m 月 d 日 h m s 1159 18 计算 2008 年有多少个星期日 SUM N TEXT DATE 2008 1 ROW INDIRECT 1 0 2 3 5 7 10 2 1197 42 罗列 2008 年每月第一个及最后一个星期日 MIN IF WEEKDAY DATE 2008 ROW A1 1198 ROW INDIRECT 1 累计出库数 1203 SUM ISODD ROW INDIRECT 2 每日库存数 1204 SUM SUMIF OFFSET B 1 1 ROW A1 2 进库 出库 C 2 1 1 1205 45 将按日期排列的销售表转换成按品名排列 IFERROR VLOOKUP A2 IF MATCH ROW 1 15 IF ISTEXT 日期 1206 A 1 A 15 MATCH 日期 A 1 A 15 日期 A 1 A 15 0 MATCH B 1 日期 A 1 A 15 0 日期 B 1 C 15 1207 2 0 1208 46 累计每日得分 N C1 0 5 N C1 IF B2 0 B2 0 1 1209 47 累计每日库存数 N G1 SUM OFFSET C 1 ROW A1 2 1 2 SUM OFFSET D 1 ROW A1 2 1 2 1210 48 强制录入规范化的日期 数据有效性设置 自定义 LEN A2 8 TEXT A2 00 00 1211 1212 销售篇 1213 1 计算单日最高销售金额 MAX SUMIF A2 A11 A2 A11 C2 C11 1214 2 查看产品曾经销售的所有价位 IF ROW A1 SUM 1 COUNTIF B 2 C 11 B 2 C 11 1215 SMALL B 2 C 11 1 COUNTIF B 2 C 11 200 1 TRANSPOSE COLUMN B H 0 1229 10 ROW 2 9 1230 9 从销售记录中提取销量与单价并计算金额 LOOKUP 10 16 RIGHT REPLACE A2 FIND 公斤 A2 100 1231 ROW 1 100 LOOKUP 10 16 RIGHT REPLACE A2 FIND 元 A2 100 ROW 1 100 1232 10 将按日期排列的销售表转换成按品名排列 IFERROR VLOOKUP A2 IF MATCH ROW 1 15 IF ISTEXT 日期 1233 A 1 A 15 MATCH 日期 A 1 A 15 日期 A 1 A 15 0 MATCH B 1 日期 A 1 A 15 0 日期 B 1 C 15 1234 2 0