超强办公室EXLE函数应用实例.xls

PMT函数使用 贷款分析 贷款额 元 200000 偿还期 年 15 年利率4 80 还款方式月末 每月偿还额 1 560 83 总计偿还额 280949 1966 总计利息 80949 19664 相对引用与绝对引用的区别 数学语文英语相对引用合计 1505050150 2606060180 3707070210 混合引用 1234 11234 22468 336912 4481216 55101520 66121824 77142128 88162432 99182736 RANK函数 用于排名 姓名总分排名姓名总分 张三5363 张三560 李四5204 李四521 王五5801 王五589 朱六5762 朱六576 赵七4995 赵七499 用IF函数返回条件值 姓名语文数学英语总分 张鹏102112130344 张峰11696106318 贵惠峰95103104302 AND函数 进行交集运算 语文数学英语总分是否合格 102112130344TRUE 11696106318FALSE 95103104302FALSE 用ISBLANK函数判断是否为空白单元格 语文数学英语总分平均分 102112130344114 6666667 96106202 缺考 95103104302100 6666667 用ISERR函数判断参数是否错误 客户号数量价格已收款应收款 10132455612000061720 1025432 待定80000159008 10356784850000222544 制制作作员员工工工工资资表表 个人所得税计算方法有无违纪现象 低于22天算违纪 应纳税所得额应缴个人所得税23 无 3001518 有 10007515 有 300032522 无 600082525 无 使用文本函数处理文本字符串 处理文本 CONCATENATE 张小莉江西省南昌市青山湖区江西省南昌市青山湖区 王晓明湖南省长沙市园中区湖南省长沙市园中区 孙小芬浙江省杭州市青州区浙江省杭州市青州区 使用EXACT函数比较字符串 区分大小写 但忽略格式上的差异 第1次第2次校验校验 公式 A79 B79 江西省江西省TRUE是TRUE 湖南省湖 南省FALSE否FALSE 浙江省 浙江省FALSE否FALSE 用REPT函数复制文本 姓名积分条形图复制文本 张鹏345 345345345 张峰456 456456456 贵惠峰235 235235235 FIND函数查找文本 单词奖金自创 LEFT500 好人好 DCET100 人好假 END1000 你真好假 YEAR500 好不好好 用SEARCH函数查找文本 abcde3 bcdef2 gfedbc6 REPLACE函数替换文本 姓名电话号码替换后的电话号码 张鹏3695580 7895580 张峰3695536 7895536 贵惠峰3695533 7895533 用SUBSTITUTE函数替换文本 姓名单位 职务替换后 张鹏武汉工业大学教授武汉理工大学教授 张峰武汉工业大学讲师武汉理工大学讲师 贵惠峰武汉工业大学助教武汉理工大学助教 用LEFT函数返回左边字符 姓名单位 职务X老师 张鹏武汉工业大学教授张老师 张峰武汉工业大学讲师张老师 贵惠峰武汉工业大学助教贵老师 用RIGHT函数返回右边字符 医疗证号户主姓名姓名个人号与户主关系 4212001刘大军刘大军00本人 4201001黄晓兰01配偶 4263002赵晓年陈春华00本人 4278002赵英02子女 用MID函数返回指定位置的字符 姓名身份证号性别 赵 李 王 用LEN函数返回字符串长度 名称星级等级 钓鱼台宾馆 5 颖都宾馆 3 江西宾馆 4 用LOWER UPPER函数将字母转换为小写 密码输入 转为小写输入 转为大写 CDXDSxvacdxdsxvaCDXDSXVA XICHcnaACxichcnaacXICHCNAAC xjdaJAJFxjdajajfXJDAJAJF 用VALUE TEXT函数将文本 数值互转 文本转为数值转为文本 5022250222 50222AB 8222334482223344 82223344AB 144223144223 144223AB 用CODE函数返回字符代码 返回文本字符串中第一个字符的数字代码 用TRIM函数清除多余的空格 姓名姓名比较结果清除后 张 霞李连杰不同张 霞 李克强李小明相同李克强 钱文辉马珊不同钱文辉 自动生成用户称谓 从身份证号码中提取出生月份 姓名身份证号码出生年月性别称谓 张天君42112719720407043519720407 男张先生 王红42112719850805105219850805 男王先生 刘春晖42112719780425152819780425 女刘小姐 王芸42112719501120304319501120 女王小姐 绝对引用合计 150 150 150 56789 56789 1012141618 1518212427 2024283236 2530354045 3036424854 3542495663 4048566472 4554637281 排名 3 4 1 2 5 是否合格等级TRUE 是AFALSE 是B 否C NOT函数进行逻辑值求反OR函数求并集 是否合格是否合格 TRUETRUE FALSETRUE FALSEFALSE 相对引用与绝对引用的区别 混合引用 用IF函数返回条件值 处理后的应收额 61720 请输入单价 222544 完全相同返回TRUE 不完全相同 返回FALSE 用ISERR函数判断参数是否错误 使用EXACT函数比较字符串 区分大小写 但忽略格式上的差异 地址合成称谓 广州市白云区广州市白云区张先生 南昌市青山湖区南昌市青山湖区王先生 峡江县水边镇峡江县水边镇刘小姐 赣州市章贡区赣州市章贡区王小姐 11234 11234 22468 336912 4481216 55101520 66121824 77142128 88162432 99182736 用DATE函数计算货物存放的天数 货物清单 A20125202012 B20121302012 C20123222012 用TODAY函数显示当前日期 商品名称生产日期保质期是否过期 A2012 1 25180 未过期 B2011 11 1180 过期 C2012 4 6360 未过期 用YEAR函数返回日期的年份 姓名获奖时间获奖年份获奖月份获奖 日 张丽2006 3 2200632 廖仲凯2011 4 152011415 李泽钜2012 5 192012519 用WEEKDAY函数显示日期的星期数 显示某日期为星期几 2012 6 240日期星期几 2012 6 214 建军节2012 7 10 2012 6 192 显示与转换时间 用TIME函数返回时间 返回0 0 9999999之间的数值 代表从0点到23 59 59PM之间的时间 HOUR为0 32767之间 任何大于23的数值除以24 其余数将视为小时 TIME 27 0 0 TIME 3 0 0 或者3 00 PM 时分秒对应值 北京奥运会20888 08 PM 用TIMEVALUE返回时间 返回文本字符所代表的时间的小数值 0 0 9999999之间 20 08 080 838981481 NOW函数返回当前日期及时间 2012 6 25 22 3636 用HOUR函数返回小时数 返回小时 分钟数 车次类型始发站始发时间终点站 K917快速空调吉安2012 6 26 北京 用SUMIF函数按条件求和 姓名部门奖金 张丽生产计划部1200 王进军人力资源部1000 张铁林人力资源部900 赵玉英生产计划部800 刘劲人力资源部680 李大明生产计划部780 常胜军人力资源部680 财务部1000 和生产计划部2780 人力资源部3260 用PRODUCT函数求乘积 将所有以参数形式给出的数字相乘 返回乘积值 名称长宽高体积 甲20304024000 乙1112131716 丙30303027000 27000 用MOD函数返回两数的余数 被除数除数余数 23103 6390 12872 用POWER函数求幂 底数指数乘幂 28256 44256 3481 用FACT计算阶乘 参数为非负数 不为正数则截尾取整 424 5 NUM 120 6720 用RAND返回随机数 RAND是返回大于等于0且小于1的均匀分布随机数 53699777 72629261 87655660 55519953 78786990 71567488 50638697 54699392 75988754 92935492 63826163 85686664 74977869 98745355 71895552 1008377100 用FLOOR函数按条件向下舍入 INT函数向下取整 姓名销售额 1 18 未取整奖金 舍入为5的倍数向下取整 INT 张鹏78904931 0672930931 王进军987201164 89611601164 张华良67843800 5474800800 用ODD函数将数值舍入为奇数 数舍入为奇数舍入为偶数 67 8456968 56 356 57 58 34 563536 56 75758 45 864746 使用函数对数据进行统计与管理 姓名语文数学英语总分 张丽105101113319 王娟98105109312 刘华113101105319 赵平89109100298 最大值 MAX113109113319 最小值 MIN89101100298 返回中值 MEDIAN101 5103107315 5 用LARGE函数按条件返回最大值 姓名语文数学英语 张丽105101113 王娟98105109 刘华113101105 赵平89109100 最大值 LARGE113 第1大值 105 第2大值 最小值 SMALL第1小值100 第2小值105 用FREQUENCY函数统计分布频率 分数排序 RANK分割点人数 884804 7871006 10631206 758 6310MODE 频率最高的 123288 1451 719 884 884 用DAVERAGE函数返回条目的平均值 树种高度使用年限产量利润 苹果树182014105 樱桃树12121096 苹果树131410105 梨树98876 5 苹果树89645 树种高度使用年限产量利润 苹果树 10 12 用DGET函数返回符合指定条件的值 树种高度使用年限产量利润 苹果树182014105 樱桃树12121096 苹果树141510105 梨树98876 5 苹果树89645 找出使用年数为15年的产量 树种高度使用年限产量利润 苹果树15 10 用DMAX函数返回符合条件的最大值 树种高度使用年限产量利润 12 12 14 用DMIN函数返回符合条件的最小值 树种高度使用年限产量利润 15 14 14 24 查询和引用数据 用CHOOSE函数在列表中选值 姓名比赛得分名次奖牌 张帆1456 无 刘一统1327 无 李丽1238 无 张希1841 金牌 赵玉英1545 无 陈彬1654 无 肖克捷1822 银牌 孙兵1723 铜牌 用LOOKUP函数查找数据 未按升序排列不能提供正确的值 第三名得分172 姓名刘一统 按升序排列 姓名比赛得分 李丽123 刘一统132 张帆145 赵玉英154 陈彬165 孙兵172 肖克捷182 张希184 第三名得分172 姓名孙兵 用HLOOKUP函数进行水平查找 奖学金发放标准 总分400以下400 500500 600600以上 参照分0400500600 比例0 50 100 200 姓名总分比例奖学金 李丽5531553 刘一统4710 5235 5 张帆36400 赵玉英69921398 陈彬4670 5233 5 孙兵5671567 肖克捷69321386 张希5431543 用VLOOKUP函数竖直查找 奖学金发放标准 总分参照分比例姓名总分 400以下00 李丽553 400 50040050 刘一统471 500 600500100 张帆364 600以上600200 赵玉英699 陈彬467 孙兵567 肖克捷693 张希543 5678 5678 10121416 15182124 20242832 25303540 30364248 35424956 40485664 45546372 61122 22829 723123 到达时间天数小时分钟 用DATE函数计算货物存放的天数 货物清单 用TIME函数返回时间 返回0 0 9999999之间的数值 代表从0点到23 59 59PM之间的时间 HOUR为0 32767之间 任何大于23的数值除以24 其余数将视为小时 TIME 27 0 0 TIME 3 0 0 或者3 00 PM 用TIMEVALUE返回时间 返回文本字符所代表的时间的小数值 0 0 9999999之间 用HOUR函数返回小时数 返回小时 分钟数 2012