Excel函数与公式标准教程(实战微课版)/清华电脑学堂 聂静 著 专业科技 文轩网

目 录 第1章 Excel公式与函数基础知识 1.1 认识Excel公式 2 1.1.1 Excel公式的基础形式 2 1.1.2 公式的组成 2 1.1.3 公式中的运算符 3 1.1.4 输入公式 5 1.1.5 修改公式 6 1.2 单元格的引用 7 1.2.1 相对引用 7 1.2.2 绝对引用 7 1.2.3 混合引用 8 动手练 使用公式计算销售净利润 8 8 1.3 复制和填充公式 9 1.3.1 复制公式 9 1.3.2 填充公式 10 动手练 批量计算所有商品的销售额 11 1.4 名称的使用 12 1.4.1 定义名称 12 1.4.2 使用名称 13 1.4.3 删除名称 14 动手练 制作疫情发展动态图表 15 1.5 函数的类型和输入方法 17 1.5.1 函数的类型 17 1.5.2 函数的输入技巧 18 1.5.3 什么是数组 20 动手练 启用“公式记忆式键入”功能 22 案例实战：计算车间生产合计 23 新手答疑 24 第2章 公式的检查和错误值处理 2.1 公式审核 26 2.1.1 自动检查错误公式 26 2.1.2 手动检查错误公式 27 2.1.3 查看公式求值步骤 27 2.1.4 显示或隐藏公式 28 2.1.5 追踪引用或从属单元格 30 动手练 解决公式不能自动计算的问题 31 2.2 公式返回的错误值 33 2.2.1 分析错误值产生的原因 33 2.2.2 屏蔽错误值 35 动手练 设置不打印错误值 36 案例实战：检查销售明细表中的错误公式并修改 38 新手答疑 40 第3章 统计函数的应用 3.1 使用函数完成平均值计算 42 3.1.1 计算所有考生平均成绩 42 3.1.2 计算全班学生平均成绩 43 3.1.3 计算所有男生/女生的平均成绩 44 3.1.4 计算90分以上男生/女生平均成绩 44 3.1.5 计算过去一年产量的平均增长率 45 3.1.6 计算车辆从始发点到终点的平均速度 46 动手练 统计员工平均工资 47 3.2 按条件统计单元格数量 47 3.2.1 统计参加考试人数 47 3.2.2 统计活动报名人数 48 3.2.3 统计缺席活动的人数 48 3.2.4 统计销售额超过5万元的人数 49 3.2.5 统计指定商品销售额超过5万元的人数 50 动手练 统计员工考勤数据 50 3.3 统计优选值和最小值 53 3.3.1 统计单笔消费优选金额 53 .3.3 统计大力度优惠的商品销量 54 3.3.4 统计运动员最差成绩 55 动手练 统计景点不同时段优选和大力度优惠人流量 55 3.4 对数据进行排位统计 56 3.4.1 对员工销售业绩进行排位 56 3.4.2 提取业绩很好的3位员工的销售额 57 3.4.3 提取业绩最差的3位员工的销售额 58 3.4.4 计算短跑中间成绩 58 动手练 对面试应聘成绩进行排名 59 3.5 计算概率分布 60 3.5.1 计算试验成功的概率 60 3.5.2 计算各概率下应包含合格产品个数 61 3.5.3 计算累积负二项式分布值 62 3.5.4 计算F概率分布 63 3.5.5 计算商品在一段时期内价格的峰值 63 3.5.6 计算数值在指定区间内的概率 64 动手练 计算各地区平均气温的峰值 65 3.6 使用函数进行数据预测 66 3.6.1 根据以往数据预测双十一成交额 66 3.6.2 预测未来利润 67 3.6.3 预测未来两个月的产品销售额 67 3.6.4 预测9月份的产品销售量 68 案例实战：统计出入库数据 69 新手答疑 72 第4章 查找与引用函数的应用 4.1 查找指定的数据 74 4.1.1 根据商品名称查询商品价格 74 4.1.2 根据所选价格查找相关售房信息 75 4.1.3 查找工号对应的职务 76 4.1.4 根据书名查找书的定价 77 4.1.5 根据客户名称查找订单信息 77 4.1.6 查找销售员对应的提成金额 78 4.1.7 检索书号所在的位置 79 4.1.8 查找指定月份指定员工的销售额 81 4.1.9 从所有销售记录中找出指定商品的销售记录 81 动手练 从销售订单表中查找需要的信息 82 4.2 引用单元格 83 4.2.1 查找优选订单数量所在单元格 84 动手练 通过下拉列表查询不同工作表中的营业额 85 4.2.2 统计分公司的数量 87 4.2.3 查询员工工资 87 4.2.4 合并多张工作表中的数据 88 4.2.5 快速为表格填充序号 89 4.2.6 计算项目的数量 90 4.2.7 根据列标提取对应的列数 90 4.2.8 计算项目需要开支的数量 91 案例实战：查询客户基本信息 92 新手答疑 94 第5章 逻辑函数的应用 5.1 真假判断函数 96 5.1.1 判断两个单元格中的内容是否相同 96 动手练 提取手机近期新报价 96 5.1.2 判断员工是否符合晋升条件 97 5.1.3 判断学生是否符合贫困生 98 5.1.4 判断游戏玩家是否未成年 99 根据年龄判断员工是否退休 99 5.2 IF条件判断函数 100 5.2.1 判断实际产量是否达标 100 5.2.2 判断员工培训成绩是否及格 101 5.2.3 根据笔试成绩和面试成绩判断是否有证书 102 5.2.4 根据报销类型输入报销费用 102 5.2.5 判断参赛选手是否被淘汰 103 5.2.6 根据业绩计算奖金总额 103 5.2.7 计算合计金额时忽略错误值 104 5.2.8 根据考核总分判断考核结果 105 5.2.9 根据公司代码返回公司名称 106 动手练 根据工龄计算提成金额 107 案例实战：根据出生日期判断员工生肖和星座 109 新手答疑 110 第6章 数学与三角函数的应用 6.1 常用的求和函数 112 6.1.1 统计所有商品销售总额 112 6.1.2 统计指定商品的销售总额 112 动手练 对超过1000的销量进行汇总 113 6.1.3 统计姓“李”的员工的销售额 114 动手练 统计手机的销售数量 115 6.1.4 统计3月份台式电脑的入库数量 115 动手练 统计销售员销售指定商品的金额 116 6.1.5 计算购买商品的合计金额 117 动手练 统计销售部女员工人数 118 6.1.6 计算所有数值的平方和 119 6.2 其他数学函数的应用 120 6.2.1 计算自然数的阶乘 120 6.2.2 计算产品的总产值 120 6.2.3 计算产品不同售价时的利润 121 6.2.4 计算两数相除的余数 122 动手练 从身份证号码中提取性别 123 6.2.5 计算两个或两个以上整数的优选公约数 123 6.2.6 计算两个或两个以上整数的最小公倍数 124 6.3 四舍五入函数的应用 125 6.3.1 计算合计支出时忽略小数 125 6.3.2 将收入金额保留到分位 126 6.3.3 将收入金额向上舍入到角位 126 6.3.4 将支出金额向下舍入到整数 127 6.3.5 将销售金额的小数部分去掉 128 动手练 根据员工入职日期计算年假天数 128 6.3.6 统计销售员提成金额，不足10000元忽略 129 6.3.7 活动分组时保证每组人数为偶数 130 6.3.8 随机从奇数行中抽取值班人员 131 统计指定单元格区域中偶数的个数 132 6.4 随机函数的应用 133 6.4.1 产生1～10之间的不重复随机整数 133 6.4.2 生成-10～10之间的随机整数 134 动手练 随机抽取中奖人员 134 案例实战：制作工资条 136 新手答疑 138 第7章 日期与时间函数的应用 7.1 日期函数 140 7.1.1 快速录入当前日期和时间 140 动手练 计算距离项目完工还有多少天 140 7.1.2 快速录入当前日期 141 动手练 统计即将到期的合同数量 142 7.1.3 计算6个月后的日期 142 7.1.4 提取日期中的年份 143 动手练 计算公司成立多少周年 144 7.1.5 提取日期中的月份 144 动手练 计算5月份的销售额 145 7.1.6 提取日期中的某一天 146 动手练 计算今年平均每月天数 146 7.1.7 计算指定日期之前/之后两个月的最后一天 147 7.1.8 计算机器工作天数和月数 148 7.1.9 计算工程历时天数 149 7.1.10 计算请假天数占全年天数百分比 150 7.1.11 根据借款日期和借款期限计算最后还款日期 150 动手练 设置合同到期提醒 151 7.2 时间函数 152 7.2.1 返回某一特定时间 152 7.2.2 计算迟到时长 153 7.2.3 计算加班时间 153 7.2.4 从时间值中提取秒数 154 计算共享单车骑行时间 154 7.3 星期函数 155 7.3.1 计算指定日期是星期几 155 动手练 计算今天是星期几 156 7.3.2 计算指定日期是当年的第几周 157 动手练 计算本学期的周数 158 7.4 工作日的计算 158 7.4.1 统计实际工作日 158 动手练 根据辞职报告批准日期计算离职日期 159 7.4.2 计算兼职天数 160 7.4.3 计算项目完成日期 160 7.4.4 计算两个日期值之间的工作天数 162 7.5 将文本日期与文本时间转换成标准格式 162 7.5.1 解决日期无法计算的问题 162 7.5.2 解决时间无法计算的问题 163 案例实战：计算员工相关信息 164 新手答疑 166 第8章 文本函数的应用 8.1 计算字符长度 168 8.1.1 根据歌曲名称长短排序 168 8.1.2 计算字符串一共有多少字节 168 动手练 计算文本字符串中包含多少个数字 169 8.2 提取字符 170 8.2.1 从产品简介中提取产品名称 170 8.2.2 从摘要中提取订单号 171 8.2.3 从学号中提取班级 171 动手练 从身份证号码中提取出生日期 172 8.3 转换文本格式 172 8.3.1 将单元格中所有字母转换为大写 172 8.3.2 将所有字母转换为小写 173 8.3.3 将所有英文句子转换成首字母大写 173 8.3.4 将全角字符转换为半角字符 174 8.3.5 将半角字符转换为全角字符 174 8.3.6 将文本型数字转换成真正的数字 175 8.3.7 判断给定的值是否是文本 176 8.3.8 将手机号码分段显示 176 8.3.9 为销售业绩自动划分等级 178 8.3.10 判断公司盈亏情况 178 8.3.11 转换日期的显示方式 178 8.3.12 为金额添加单位“元” 179 8.3.13 将数字代码转换成文本 179 8.3.14 为金额添加$符号 180 8.3.15 为金额添加千位分隔符和￥符号 180 8.3.16 将数字显示为千分位格式并转换为文本 181 将数字金额显示为中文大写金额 182 8.4 查找与替换文本 183 8.4.1 提取空格之前的字符 183 8.4.2 查找（??学历）的字符位置 184 动手练 提取括号中的内容 185 8.4.3 在字符串的指定位置插入固定内容 185 8.4.4? 删除字符串中多余的文本 186 对不规范的日期进行计算 187 8.5 合并文本 187 8.5.1? 将多个字符串合并成一个字符串 187 8.5.2? 将销售人员名单按销售部门进行显示 188 动手练 统计员工销量并判断是否达标 189 8.6 删除文本 190 8.6.1 将多个字符串合并成一个字符串 190 8.6.2 删除不能打印的字符 190 动手练 显示12位及以上的数值 191 案例实战：隐藏手机号码中间4位 192 新手答疑 194 第9章 财务函数的应用 9.1 计算固定资产折旧费 196 9.1.1 用余额递减法计算固定资产的年度折旧值 196 9.1.2 用双倍余额递减法计算资产折旧值 197 9.1.3 使用年限总和折旧法计算折旧值 198 9.1.4 计算折旧期限为8年的固定资产的折旧值 199 9.1.5 使用双倍余额递减法计算任何期间的资产折旧值 199 动手练 计算前200天的折旧值 200 9.2 计算存款和利息 201 9.2.1 计算某项投资的未来值 201 动手练 计算银行存款总额 202 9.2.2 计算贷款的现值 203 9.2.3 计算贷款需多少年才能还清 204 9.2.4 计算贷款的年利率 205 9.2.5 计算给定时间段内累计偿还的利息 206 9.2.6 计算贷款在第一个月偿还的本金 207 9.3 计算内部收益率 208 9.3.1 计算投资的内部收益率 208 9.3.2 计算基金定投的年化收益率 209 9.3.3 计算四年后投资的修正收益率 210 动手练 计算实际年利率 211 案例实战：计提固定资产折旧额 212 新手答疑 214 第10章 信息函数的应用 10.1? 数据类型信息的获得 216 10.1.1 提取当前工作表的名称 216 10.1.2 判断错误类型 217 10.1.3 按类别单独设置序号 218 动手练判断成绩等级 219 10.2? 使用IS函数进行各种判断 220 10.2.1 统计缺勤人数 220 10.2.2 统计缺考人数 220 10.2.3 判断数值是否为偶数 221 10.2.4 判断员工性别 222 10.2.5 屏蔽单元格中数值的错误 222 10.2.6 计算项目的完成率 223 计算男、 女员工人数 224 案例实战： 提取某运动员比赛成绩 225 新手答疑 226 第11章 函数在条件格式和数据验证中的应用 11.1? 函数在条件格式中的应用 228 11.1.1 标记重复数据 228 11.1.2 突出显示每个商品优选销售量 229 11.1.3 突出显示不达标业绩 230 11.1.4 突出显示周末日期 231 动手练 将低于60分的考核成绩标为“不合格” 233 11.2? 使用函数设置数据验证 234 11.2.1 禁止输入重复值 234 11.2.2 禁止在单元格中输入空格 235 11.2.3 禁止修改指定区域数据 236 动手练 保证输入的银行卡号位数正确 237 案例实战： 制作多级下拉菜单 238 新手答疑 240