Excel函数与公式一本通电子书

内容提要

前言

本书内容概要

学习本书的方法

（1）找到学习的“快速通道”

（2）多看不如多实践

（3）扩展思路，寻找最佳方案

（4）不断学习并养成良好的习惯

本书的读者对象

第1章 Excel函数与公式学前准备

1.1 为什么要学习Excel函数与公式

1.1.1使用公式实现精准计算

1.1.2计算结果联动轻松自动更新

1.1.3直观展示数据分析结果

1.2 Excel公式其实并不难

1.2.1Excel公式和人们常说的公式有何区别

1.2.2公式的组成

1.2.3Excel公式中运算符的类型

（1）算术运算符

（2）比较运算符

（3）文本运算符

（4）引用运算符

1.2.4公式不能运算的原因及解决方法

1.3 函数在公式中的重要性

1.3.1Excel函数的类型及查看方式

（1）在“公式”选项卡中查看函数

（2）在“插入函数”对话框中查看函数

1.3.2函数的组成结构是怎样的

1.3.3最快捷的计算方式

1.4 无论何时都要使用规范的数据源

1.4.1凌乱的数据源对公式计算有什么危害

1.4.2规范的数据源要具备的10个条件

拓展练习 用公式排查重复的兑奖记录

知识总结

第2章 公式与函数入门课

2.1 公式的输入与编辑

2.1.1输入公式计算商品折扣率

（1）在公式中手动输入单元格地址

（2）在公式中自动引用单元格地址

2.1.2重新编辑折扣率公式

（1）双击启动编辑

（2）快捷键启动编辑

（3）编辑栏启动编辑

2.1.3填充公式计算所有商品折扣率

2.2 插入函数的多种方法

2.2.1统计销售员全年销售金额

2.2.2统计各季度销售金额

2.2.3统计1季度指定店铺的销售金额

2.3 根据折扣计算商品实际销售价格

2.3.1相对引用对计算结果的影响

2.3.2绝对引用对计算结果的影响

2.3.3混合引用对计算结果的影响

拓展练习 计算员工工资

知识总结

第3章 逻辑函数断是非

3.1 Excel逻辑值的类型及作用

3.1.1用逻辑值显示比较运算结果

3.1.2信息函数的逻辑判断结果

3.1.3判断实际支出与预算金额是否匹配

3.2 IF函数自动做出准确判断

3.2.1自动判断成绩是否合格

3.2.2根据考生成绩自动评定等级

3.2.3将成绩以优秀、良好、及格、不及格的形式表示

3.3 IF与其他函数的嵌套应用

3.3.1自动判断员工平均工资是否高于部门平均工资

3.3.2直接对带单位的数值进行计算

3.3.3屏蔽公式返回的错误值

3.4 多条件判断的三个好搭档

3.4.1了解函数

3.4.2判断公司新员工各项考核是否已全部通过

3.4.3判断员工是否具备内部竞聘资格

3.4.4判断员工是否具有住房补贴资格

3.4.5判断银行卡号位数是否准确

3.4.6根据性别和年龄判断是否退休

3.4.7判断产品最终检验结果是否合格

拓展练习 自动完成员工业绩考评及奖金计算

知识总结

第4章 自动完成汇总与统计

4.1 不同条件下的求和计算

4.1.1计算所有销售员的销售总额｜SUM

4.1.2每月销售额记录在不同工作表中时怎样求和｜SUM

4.1.3统计指定员工的销售总额｜SUMIF

4.1.4只对销售额大于1000的值进行求和｜SUMIF

4.1.5仅对“智能”产品的销售额进行求和｜SUMIF

4.1.6计算满足指定条件的男性销售员的销售金额｜SUMIFS

4.2 统计员工考勤情况

4.2.1统计参与考勤的人数｜COUNTA

4.2.2统计员工出勤、请假和休息天数｜COUNT，COUNTBLANK

4.2.3统计不同事由的请假天数｜COUNTIF

4.2.4单元格真空还是假空COUNTIF函数自有定夺｜COUNTIF

4.2.5统计指定月份满勤的人数｜COUNTIFS

4.3 按条件统计平均值

4.3.1计算平均奖金金额｜AVERAGEA，AVERAGE

4.3.2去除0值计算平均奖金金额｜AVERAGE

4.3.3计算指定部门员工的平均工资｜AVERAGEIF

4.3.4计算人事部30岁以下员工平均工资｜AVERAGEIFS

4.3.5计算30～40岁之间员工的平均基本工资｜AVERAGEIFS

4.3.6统计基本工资低于平均值的人数｜COUNTIF，AVERAGE

4.4 迅速统计最大值或最小值

4.4.1提取报价单中的最高和最低金额｜MAX

4.4.2统计旅行团中女性的最大年龄｜MAX

4.4.3根据出库记录统计单日最高出库数量｜MAX，SUMIF

4.4.4统计投票选举中最高得票数和最低得票数

4.4.5统计去除最高分和最低分的平均分成绩

4.4.6根据服务态度好评率计算奖金

4.5 数据排名讲章法

4.5.1为比赛成绩排名｜RANK

4.5.2计算运动员在三个小组中的排名｜RANK

4.5.3对比赛成绩进行中式排名

拓展练习 奶茶店销售数据统计分析

知识总结

第5章 处理数值取舍和排列问题

5.1 对数值进行四舍五入处理

5.1.1将金额四舍五入到角｜ROUND

5.1.2将金额四舍五入到百位｜ROUND

5.2 对收支金额进行取整处理

5.2.1将预算金额保留到整数｜TRUNC

5.2.2对促销商品价格进行向上取整｜ROUNDUP

5.2.3对商品实收总价向下取舍到角｜ROUNDDOWN

5.3 除法运算取零取整你说了算

5.3.1计算平均每日出勤人数｜QUOTIENT

5.3.2不用四则运算也能统计余额｜MOD

5.3.3根据身份证号码判断性别｜IF，MOD，MID

5.4 随机函数在工作中的广泛用途

5.4.1随机安排考生座位｜RANK

5.4.2为志愿者随机分配工作内容｜CHOOSE，RANDBETWEEN

5.4.3制作简易随机抽奖器｜INDEX，RANDBETWEEN

5.4.4自动生成随机密码｜RANDBETWEEN

拓展练习 随机安排不重复值班人员

知识总结

第6章 轻松实现数据查询

6.1 使用VLOOKUP函数根据条件查找数据

6.1.1根据商品名称查询商品价格

6.1.2根据身高自动查询服装尺码

6.1.3根据工号自动查询员工信息

6.1.4查询不同店铺同类产品的销量

6.1.5根据考核分数自动评定等级

6.2 HLOOKUP换个方向也能查找需要的数据

6.2.1查找指定车间某日的生产量

6.2.2查询产品不同时期的价格

6.3 MATCH函数根据数据位置进行查找

6.3.1查找指定演员的出场次序

6.3.2查找列表中是否包含指定的产品

6.3.3排查是否存在重复报销的项目

6.4 CHOOSE函数根据给定索引值查找数据

6.4.1随机安排家务

6.4.2跳过周六和周日安排值日人员

6.4.3根据员工编号自动录入所属部门

6.4.4根据产品不良数量自动判断处理方式

6.4.5根据书名和图书类别查询指定图书的价格

6.4.6计算工程完工日期是所在月份的上旬、中旬还是下旬

6.5 INDEX函数提取指定行列交叉处的值

6.5.1了解两种参数的设置方法

6.5.2根据收发地自动查询首重价格

6.5.3根据月份自动查询销售金额

6.5.4隔行提取客户名称

6.5.5根据姓名逆向查询节目名称

6.6 OFFSET函数根据指定偏移量查找数据

6.6.1OFFSET函数的详细应用分析

6.6.2查询指定日期之前的出库总量

6.6.3根据准考证号查询考生成绩

6.6.4在合并单元格中填充序列

6.6.5将合并区域中的值拆分到多个单元格中

6.6.6根据工资表制作工资条

拓展练习 制作可自动显示新增数据的动态图表

知识总结

第7章 用函数分析日期和时间函数

7.1 根据日期推算其他相关数据

7.1.1计算距离活动结束还剩多少天｜NOW

7.1.2设置合同状态提醒｜TODAY

7.1.3统计临期合同的数量｜TODAY

7.1.4日期处理利器DATE函数应用分析｜DATE

7.1.5根据年份和月份计算第一天和最后一天｜DATE

7.2 从日期中提取年、月、日信息

7.2.1根据员工入职日期计算工龄｜YEAR

7.2.2计算2021年之前的总成交额｜SUM，IF，YEAR

7.2.3统计本月过生日的员工人数｜MONTH

7.2.4根据入职日期计算新员工转正日期｜DAY

7.2.5计算分期付款的最后一个还款日｜EDATE

7.3 有效利用时间信息完成数据分析和计算

7.3.1计算员工工时工资｜HOUR

7.3.2根据任务完成时间计算积分｜MINUTE

7.3.3计算提前完成任务的人数｜SUM，HOUR

7.3.4计算借车时长｜HOUR，MINUTE，SECOND

7.4 星期函数的应用

7.4.1计算指定日期是星期几｜WEEKDAY

7.4.2判断演出时间是星期几｜TEXT，WEEKDAY

7.4.3判断演出时间是周末还是工作日｜IF，WEEKDAY

7.4.4统计所有星期一至星期五的消费金额｜SUM，WEEKDAY

7.4.5计算今年秋季开学是第几周｜WEEKNUM

7.4.6计算项目历时多少周完成｜WEEKNUM

7.4.7计算项目的实际工作天数｜NETWORKDAYS

7.4.8单休制度如何统计工作天数

7.4.9统计每个工作阶段休息的天数｜NETWORKDAYS.INTL

拓展练习 根据出生日期计算其他相关信息

知识总结

第8章 文本处理的关键函数

8.1 文本长度应该如何计算

8.1.1按照书名的长度进行排序｜LEN

8.1.2判断电话号码是座机号还是手机号｜LEN，IF

8.1.3计算字符串中包含的数字个数｜LEN，LENB

8.2 从指定位置提取字符串

8.2.1从客户地址中提取省份信息｜LEFT

8.2.2省份长度不同时如何自动提取｜LEFT，FIND

8.2.3从身份证号码中提取出生日期｜MID

8.2.4根据打卡数据判断是否迟到｜MID，IF

8.2.5提取手机四位数尾号｜RIGHT

8.2.6根据打卡数据判断员工所属部门｜RIGHT，VLOOKUP

8.3 文本的查找和替换

8.3.1FIND函数判断字符位置｜FIND

8.3.2从混合数据中提取姓名｜LEFT，FIND

8.3.3提取括号中的内容｜IFERROR，MID，FIND

8.3.4FIND函数的孪生兄弟SEARCH｜SEARCH

8.3.5按需替换指定位置的字符｜REPLACE

8.3.6批量更改课程编号｜IF，MID，REPLACE

8.3.7直接指定要替换的内容｜SUBSTITUTE

8.3.8隐藏身份证号码中的部分数字｜SUBSTITUTE，MID

8.3.9统计每日加工工序｜SUBSTITUTE

8.3.10对带单位的金额进行求和｜SUMPRODUCT

8.4 字符转换函数的应用

8.4.1将产品的品类转换成大写｜UPPER

8.4.2将大写单位转换成小写｜LOWER

8.4.3将中英文互译诗句转换成第一个单词首字母大写｜LOWER，CONCATENATE

8.4.4将英文名的每个单词全部转换成首字母大写｜PROPER

8.4.5将文本型数字转换成真正的数字｜VALUE

8.5 最重要的格式转换函数

8.5.1搞清楚常用代码的含义｜TEXT

8.5.2自动显示英文状态下的当前日期和星期几｜TEXT，TODAY

8.5.3计算加班时长｜IF，TEXT

8.5.4直观显示活动预算和实际支出的差额｜TEXT

8.5.5分段显示电话号码｜TEXT

8.5.6计算施工期间有多少个项目是在星期一完工的｜SUM，N，TEXT

8.5.7将财务报表中的数据以小数点对齐｜TEXT

拓展练习 对合并的课程信息进行分列整理

知识总结

第9章 理清资产折旧及投资理财难题

9.1 计算固定资产折旧值

9.1.1使用固定余额递减法计算资产折旧值｜DB

9.1.2使用双倍余额递减法计算资产折旧值｜DDB

9.1.3使用双倍余额递减法计算任何期间的资产折旧值｜VDB

9.1.4使用年限总和折旧法计算折旧值｜SYD

9.2 用函数计算存款与贷款利息

9.2.1根据投资金额、投资年数及目标收益计算年增长率｜RATE

9.2.2根据银行初期存款额、定期存款额、存款年限计算利息｜RATE

9.2.3根据保险金额、年限及每年返还金额计算收益率｜RATE

9.2.4根据利息和存款金额计算存款额达到目标数需要多少个月｜NPER

9.2.5计算等额等息贷款的还款年限｜NPER

9.2.6根据年利率、每次存款金额及存款年限计算本金与利息总和｜FV

9.2.7根据年利率、每月扣除金额及年限计算住房公积金｜FV

9.2.8根据月支付金额、回报率及支付年限计算投资未来现值｜PV

9.2.9计算多个项目在相同利益条件下投资更少的项目｜PV

9.2.10根据贷款、利率和贷款年限计算需要偿还的本金｜CUMPRINC

9.2.11根据贷款、年利息和贷款年限计算指定时间内的利息｜CUMIPMT

拓展练习 根据固定资产清单计算累计折旧金额

知识总结

第10章 公式在条件格式与数据验证中的应用

10.1 设置条件格式突出重要数据

10.1.1当录入重复的产品名称时自动突出显示

10.1.2突出显示每月最高销量

10.1.3将抽检不合格的产品整行突出显示

10.2 通过数据验证限制数据录入

10.2.1禁止修改报价单中的任何内容

10.2.2禁止在姓名中输入空格

10.2.3限制只允许输入18位身份证号码

拓展练习 制作课程选择多级列表

知识总结

附录

附录Ⅰ Excel常用快捷键汇总

（1）功能键

（2）Shift组合功能键

（3）Ctrl组合功能键

附录Ⅱ Excel常用函数一览

（1）数学与三角函数

（2）日期与时间函数

（3）查找与引用函数

（4）文本函数

（5）财务函数

（6）逻辑函数

（7）统计函数

（8）信息函数

（9）工程函数

（10）数据库函数