内容简介
本书是“新起点电脑教程”系列丛书的一个分册,以通俗易懂的语言、精挑细选的实用技巧和翔实生动的操作案例,全面介绍了Excel 2013公式、函数、图表与数据分析的基础知识及应用案例,主要内容包括Excel快速入门与应用、Excel公式与函数基础知识、公式审核与错误处理、文本与逻辑函数、日期与时间函数、数学与三角函数、财务函数、统计函数、查找与引用函数、数据库函数、图表入门与应用、数据处理与分析,以及使用数据透视表和数据透视图等方面的知识、技巧及应用案例。
本书配套一张多媒体全景教学光盘,收录了书中全部知识点的视频教学课程,同时还赠送了4套相关视频教学课程,以及多本电子图书,可以帮助读者循序渐进地学习、掌握和提高。
本书可作为有一定Excel基础操作知识的读者学习公式、函数、图表与数据分析的参考用书,也可以作为函数速查工具手册,又可以作为丰富的函数应用案例宝典,适合广大电脑爱好者及各行各业人员作为学习Excel的自学手册使用,同时还可以作为初、中级电脑培训班的电脑课堂教材。
前 言
Excel 2013是Office 2013中最重要的家族成员之一,它比以往的老版本功能更强大、更具人性化、设计更专业、使用更方便,被广泛地应用于数据管理、财务统计、金融等领域。为了帮助初学者快速学习与应用Excel 2013软件,以便在日常的学习和工作中学以致用,我们编写了本书。
本书根据初学者的学习习惯,采用由浅入深的方式讲解,通过大量的实际案例,全面介绍了Excel 2013的公式、函数、图表和数据分析的功能、应用,以及一些实用的操作技巧,读者还可通过随书赠送的多媒体教学视频进行学习。全书结构清晰,内容丰富,共分为13章,主要包括以下5个部分。
1. Excel 2013快速入门与应用
本书第1章,介绍了Excel 2013快速入门与应用的相关知识,包括Excel的启动与退出、工作簿的基本操作、工作表的基本操作、单元格的基本操作和格式化工作表方面的内容。
2. Excel公式与函数基础知识
本书第2章,介绍了Excel公式与函数基础知识,包括认识公式与函数、单元格引用、公式中的运算符及其优先级、输入与编辑公式、函数的结构和种类、输入函数的方法,以及定义和使用名称等方面的相关知识及操作方法。
3. 公式审核与错误处理
本书第3章,介绍了公式审核与错误处理的方法,包括审核公式、公式返回错误及解决方法和处理公式中常见错误的操作方法。
4. Excel函数的应用举例
本书第4~10章,介绍了Excel各类函数的应用举例,包括文本与逻辑函数、日期与时间函数、数学与三角函数、财务函数、统计函数、查找与引用函数和数据库函数的相关知识及应用举例。
5. 图表与数据分析
本书第11~13章,介绍了图表与数据分析的相关知识,包括图表入门与应用、数据处理与分析,以及使用数据透视表和数据透视图等方面的知识、技巧及应用案例。
本书由文杰书院编著,参与本书编写工作的有李军、袁帅、文雪、肖微微、李强、高桂华、蔺丹、张艳玲、李统财、安国英、贾亚军、蔺影、李伟、冯臣、宋艳辉等。
我们真切希望读者在阅读本书之后,可以开阔视野、增长实践操作技能,并从中学习和总结操作的经验和规律,灵活运用。鉴于编者水平有限,书中纰漏和考虑不周之处在所难免,热忱欢迎读者予以批评、指正,以便我们日后能为您编写出更好的图书。
编 者
目 录
第1章 Excel快速入门与应用 11.1 Excel的启动与退出 21.1.1 启动Excel 21.1.2 熟悉Excel 2013工作界面 31.1.3 退出Excel 71.2 工作簿的基本操作 81.2.1 创建新工作簿 81.2.2 开始输入数据 101.2.3 保存和关闭工作簿 121.2.4 打开保存的工作簿 151.2.5 保护工作簿 151.3 工作表的基本操作 161.3.1 选取工作表 171.3.2 重命名工作表 191.3.3 添加与删除工作表 201.3.4 复制与移动工作表 211.3.5 保护工作表 231.4 单元格的基本操作 241.4.1 选取单元格 251.4.2 插入单元格 251.4.3 删除单元格 291.4.4 复制与移动单元格 331.5 格式化工作表 341.5.1 调整表格的行高与列宽 341.5.2 设置字体格式 361.5.3 设置对齐方式 371.5.4 添加表格边框 381.5.5 自动套用表格格式 391.6 实践案例与上机指导 401.6.1 隐藏与显示工作表 401.6.2 合并单元格 421.6.3 更改工作表标签颜色 431.6.4 设置背景 441.7 思考与练习 45第2章 Excel公式与函数基础知识 472.1 认识公式与函数 482.1.1 什么是公式 482.1.2 什么是函数 482.2 单元格引用 492.2.1 相对引用 492.2.2 绝对引用 502.2.3 混合引用 512.2.4 改变引用类型 532.3 公式中的运算符及其优先级 542.3.1 算术运算符 542.3.2 比较运算符 552.3.3 引用运算符 552.3.4 文本运算符 552.3.5 运算符的优先级顺序 552.4 输入与编辑公式 562.4.1 输入公式 562.4.2 修改公式 582.4.3 复制与移动公式 592.4.4 隐藏公式 612.4.5 删除公式 632.5 函数的结构和种类 652.5.1 函数的结构 652.5.2 函数的种类 652.6 输入函数的方法 672.6.1 直接输入函数 672.6.2 通过【插入函数】对话框输入函数 682.7 定义和使用名称 702.7.1 定义名称 702.7.2 根据所选内容一次性创建多个名称 732.7.3 让定义的名称只应用于当前工作表 742.8 实践案例与上机指导 752.8.1 将公式定义为名称 752.8.2 使用【函数库】组中的功能按钮插入函数 772.8.3 将公式结果转换为数值 782.9 思考与练习 80第3章 公式审核与错误处理 833.1 审核公式 843.1.1 使用公式错误检查功能 843.1.2 添加追踪箭头追踪引用和从属单元格 853.1.3 监视单元格内容 873.1.4 定位特定类型的数据 883.1.5 使用公式求值功能 893.2 公式返回错误及解决方法 913.2.1 "#DIV/0!"错误及解决方法 913.2.2 "#N/A"错误及解决方法 923.2.3 "#NAME?"错误及解决方法 923.2.4 "#NULL!"错误及解决方法 933.2.5 "#NUM!"错误及解决方法 943.2.6 "#REF!"错误及解决方法 943.2.7 "#VALUE!"错误及解决方法 953.2.8 "#####"错误及解决方法 953.3 处理公式中常见的错误 963.3.1 括号不匹配 963.3.2 循环引用 973.3.3 空白但非空的单元格 983.3.4 显示值与实际值 983.4 实践案例与上机指导 993.4.1 在工作表中显示公式而非计算结果 993.4.2 在多个单元格中输入同一个公式 1003.4.3 查看长公式中的某一步计算结果 1013.4.4 自动求和 1023.5 思考与练习 102第4章 文本与逻辑函数 1054.1 文本函数 1064.1.1 什么是文本函数 1064.1.2 认识文本数据 1064.1.3 区分文本与数值 1064.1.4 文本函数介绍 1074.2 文本函数应用举例 1084.2.1 使用ASC函数将全角字符转换为半角字符 1084.2.2 使用CONCATENATE函数自动提取序号 1084.2.3 使用CLEAN函数清理非打印字符 1094.2.4 使用CHAR函数返回对应于数字代码的字符 1104.2.5 使用DOLLAR函数转换货币格式 1114.2.6 使用EXACT函数比较产品编号是否相同 1124.2.7 使用LEN函数检查身份证位数是否正确 1124.2.8 使用LOWER函数将文本转换为小写 1134.2.9 使用REPLACE函数为电话号码升级 1144.2.10 使用PROPER函数将每个单词的首字母转换为大写 1154.3 逻辑函数 1154.3.1 什么是逻辑函数 1154.3.2 逻辑函数介绍 1164.4 逻辑函数应用举例 1164.4.1 使用AND函数检测产品是否合格 1164.4.2 使用FALSE函数判断两列数据是否相等 1174.4.3 使用IF函数标注不及格考生 1184.4.4 使用IFERROR函数检查数据的正确性 1194.4.5 使用NOT函数进行筛选 1204.4.6 使用OR函数判断员工考核是否达标 1204.4.7 使用TRUE函数判断两列数据是否相同 1214.5 实践案例与上机指导 1224.5.1 使用IF和NOT函数选择面试人员 1224.5.2 使用IF和OR函数对产品进行分类 1224.5.3 根据年龄判断职工是否退休 1234.5.4 比对文本 1244.6 思考与练习 124第5章 日期与时间函数 1275.1 日期函数 1285.1.1 Excel提供的两种日期系统 1285.1.2 日期序列号和时间序列号 1285.1.3 常用的日期函数 1295.2 日期函数应用举例 1295.2.1 使用DATE函数计算已知第几天对应的准确日期 1295.2.2 使用DATEVALUE函数计算完成计划内容所需天数 1305.2.3 使用DAYS360函数计算两个日期间的天数 1315.2.4 使用DAY函数计算指定日期对应的天数 1325.2.5 使用EOMONTH函数计算指定日期到月底的天数 1335.2.6 使用MONTH函数计算指定日期的月份 1355.2.7 使用NOW函数得到当前的日期和时间 1355.2.8 使用TODAY函数推算春节倒计时 1365.2.9 使用WEEKDAY函数返回指定日期的星期值 1365.2.10 使用WORKDAY函数计算项目完成日期 1385.3 时间函数 1385.3.1 时间的加减运算 1385.3.2 时间的取舍运算 1395.3.3 常用的时间函数 1395.4 时间函数应用举例 1395.4.1 使用HOUR函数计算电影播放时长 1395.4.2 使用MINUTE函数精确到分钟计算工作时间 1405.4.3 使用SECOND函数计算选手之间相差的秒数 1415.4.4 使用TIME函数计算比赛到达终点的时间 1425.4.5 使用TIMEVALUE函数计算口语测试时间 1435.5 实践案例与上机指导 1445.5.1 计算员工转正时间 1445.5.2 计算员工的应付工资 1455.5.3 计算应付账款的还款天数 1465.5.4 根据出生日期快速计算年龄 1465.6 思考与练习 147第6章 数学与三角函数 1496.1 常用的数学与三角函数 1506.2 常规计算 1526.2.1 使用ABS函数计算两地的温差 1526.2.2 使用MOD函数计算库存结余 1526.2.3 使用SUM函数计算学生总分成绩 1536.2.4 使用SUMIF函数统计指定商品的销售数量 1546.2.5 使用SUMIFS函数统计某日期区间的销售金额 1556.3 舍入计算 1556.3.1 使用CEILING函数计算通话费用 1566.3.2 使用FLOOR函数计算员工的提成奖金 1566.3.3 使用INT函数对平均销量取整 1576.3.4 使用ROUND函数将数字按指定位数舍入 1586.3.5 使用ROUNDUP函数计算人均销售额 1586.4 阶乘与随机数 1596.4.1 使用COMBIN函数确定所有可能的组合数目 1596.4.2 使用FACT函数计算数字的阶乘 1606.4.3 使用MULTINOMIAL函数解决分组问题 1616.4.4 使用RAND函数随机创建彩票号码 1616.4.5 使用SUMPRODUCT函数计算参保人数 1626.5 指数与对数 1636.5.1 使用EXP函数返回e的n次方 1636.5.2 使用LN函数计算均衡修正项 1636.5.3 使用LOG函数计算无噪信道传输能力 1646.5.4 使用LOG10函数计算分贝数 1656.5.5 使用POWER函数计算数字的乘幂 1656.6 三角函数计算 1666.6.1 使用ACOS函数计算反余弦值 1666.6.2 使用ATAN2函数计算射击目标的方位角 1676.6.3 使用ATANH函数计算反双曲正切值 1686.6.4 使用ASIN函数计算数字的反正弦值 1686.6.5 使用COS函数计算直角三角形中锐角的邻边长度 1696.6.6 使用DEGREES函数计算扇形运动场角度 1706.6.7 使用RADIANS函数计算弧长 1706.6.8 使用SIN函数计算指定角度的正弦值 1716.6.9 使用TAN函数计算给定角度的正切值 1726.6.10 使用TANH函数计算双曲正切值 1726.7 其他数学与三角函数 1736.7.1 使用PI函数计算圆周长 1736.7.2 使用ROMAN函数将阿拉伯数字转为罗马数字 1746.7.3 使用SUBTOTAL函数汇总员工工资情况 1756.8 实践案例与上机指导 1766.8.1 使用MROUND函数计算车次 1776.8.2 计算四项比赛对局总次数 1776.8.3 随机抽取中奖号码 1786.9 思考与练习 178第7章 财务函数 1817.1 常用的财务函数 1827.2 折旧值计算函数 1837.2.1 使用AMORDEGRC函数计算第一时期的折旧值 1847.2.2 使用AMORLINC函数计算第一时期的折旧值 1857.2.3 使用DB函数计算每年的折旧值 1867.2.4 使用SLN函数计算线性折旧值 1877.2.5 使用SYD函数按年限总和折旧法计算折旧值 1887.2.6 使用VDB函数按余额递减法计算房屋折旧值 1887.3 投资计算函数 1897.3.1 使用FV函数计算存款加利息数 1907.3.2 使用NPER函数计算存款达到1万元需要几个月 1907.3.3 使用NPV函数计算某项投资的净现值 1917.3.4 使用PV函数计算贷款买车的贷款额 1927.3.5 使用XNPV函数计算未必定期发生的投资净现值 1937.4 本金与利息函数 1947.4.1 使用EFFECT函数将名义年利率转换为实际年利率 1947.4.2 使用IPMT函数计算在给定期间内的支付利息 1947.4.3 使用NOMINAL函数计算某债券的名义年利率 1957.4.4 使用PMT函数计算贷款的每月分期付款额 1967.4.5 使用RATE函数计算贷款年利率 1977.5 收益率函数 1987.5.1 使用IRR函数计算某项投资的内部收益率 1987.5.2 使用MIRR函数计算某项投资的修正内部收益率 1997.5.3 使用XIRR函数计算未必定期发生的现金流的内部收益率 1997.6 债券与证券函数 2007.6.1 使用ACCRINT函数计算定期付息债券应计利息 2007.6.2 使用COUPDAYBS函数计算当前付息期内截止到结算日的天数 2027.6.3 使用DISC函数计算有价证券的贴现率 2027.6.4 使用INTRATE函数计算一次性付息证券的利率 2037.6.5 使用YIELD函数计算有价证券的收益率 2047.7 实践案例与上机指导 2057.7.1 计算汽车的折旧值 2057.7.2 计算贷款每期返还的本金金额 2067.7.3 计算投资期内所支付的利息 2067.8 思考与练习 207第8章 统计函数 2098.1 常用的统计函数 2108.2 平均值函数 2118.2.1 使用AVERAGE函数计算人均销售额 2118.2.2 使用AVERAGEA函数求包含文本值的平均值 2118.2.3 使用AVERAGEIF函数求每季度平均支出金额 2128.2.4 使用AVERAGEIFS函数计算满足多重条件的数据的平均值 2138.2.5 使用GEOMEAN函数计算销售量的几何平均值 2148.2.6 使用TRIMMEAN函数进行评分统计 2158.2.7 使用MEDIAN函数计算中间成绩 2158.3 数理统计函数 2168.3.1 使用FORECAST函数预测未来指定日期的天气 2168.3.2 使用FREQUENCY函数统计每个分数段的人员个数 2178.3.3 使用GROWTH函数预测下一年的销量 2188.3.4 使用MODE.SNGL函数统计配套生产最佳产量值 2198.4 条目统计函数 2208.4.1 使用COUNT函数统计生产车间异常机台个数 2208.4.2 使用COUNTA函数统计出勤异常人数 2218.4.3 使用COUNTBLANK函数统计未检验完成的产品数 2228.4.4 使用COUNTIF函数统计不及格的学生人数 2238.4.5 使用COUNTIFS函数统计A班成绩优秀的学生数 2238.5 最大值与最小值函数 2248.5.1 使用LARGE函数提取销售季军的销售额 2248.5.2 使用MAX函数统计销售额中的最大值 2258.5.3 使用MAXA函数计算已上报销售额中的最大值 2268.5.4 使用MIN函数统计销售额中的最小值 2278.5.5 使用SMALL函数提取最后一名的销售额 2278.6 实践案例与上机指导 2288.6.1 根据总成绩对考生进行排名 2288.6.2 计算单日最高销售金额 2298.6.3 计算平均成绩(忽略缺考人员) 2308.7 思考与练习 231第9章 查找与引用函数 2339.1 查找与引用函数概述 2349.2 普通查询 2349.2.1 使用CHOOSE函数标注热销产品 2349.2.2 使用HLOOKUP函数查找某业务员在某季度的销量 2359.2.3 使用LOOKUP函数查找信息(向量型) 2379.2.4 使用LOOKUP函数查找信息(数组型) 2389.2.5 使用VLOOKUP函数对岗位考核成绩进行评定 2399.3 引用查询 2409.3.1 使用INDEX函数快速提取员工编号 2409.3.2 使用MATCH函数不区分大小写提取成绩 2419.3.3 使用OFFSET函数根据指定姓名和科目查询成绩 2429.3.4 使用TRANSPOSE函数转换数据区域 2439.4 引用表中数据 2449.4.1 使用ADDRESS函数定位年会抽奖号码位置 2449.4.2 使用AREAS函数统计选手组别数量 2469.4.3 使用COLUMNS函数统计公司的部门数量 2469.4.4 使用HYPERLINK函数添加客户的电子邮件地址 2479.4.5 使用ROW函数快速输入12个月份 2489.5 实践案例与上机指导 2499.5.1 进、出库合计查询 2499.5.2 根据产量计算员工产量得分 2509.5.3 通过差旅费报销明细统计出差人数 2519.6 思考与练习 251第10章 数据库函数 25310.1 数据库函数概述 25410.2 计算数据库中的数据 25410.2.1 使用DPRODUCT函数统计手机的返修记录 25410.2.2 使用DSUM函数统计符合条件的销售额总和 25510.3 数据库常规统计 25610.3.1 使用DAVERAGE函数统计符合条件的销售额平均值 25610.3.2 使用DCOUNT函数统计销售精英人数 25710.3.3 使用DCOUNTA函数计算公司使用手机的人数 25810.3.4 使用DGET函数提取指定条件的销售额 25910.3.5 使用DMAX函数提取销售员的最高销售额 26010.4 数据库散布度统计 26010.4.1 使用DSTDEV函数计算员工的年龄标准差 26110.4.2 使用DSTDEVP函数计算员工的总体年龄标准差 26110.4.3 使用DVAR函数计算男员工销售量的方差 26210.4.4 使用DVARP函数计算男员工销售量的总体方差 26310.5 实践案例与上机指导 26410.5.1 统计文具类和厨具类产品的最低单价 26410.5.2 计算公司员工销售额 26510.5.3 计算语文成绩大于90分者的最高总成绩 26610.6 思考与练习 266第11章 图表入门与应用 26911.1 认识图表 27011.1.1 图表的类型 27011.1.2 图表的组成 27311.2 创建图表的方法 27411.2.1 通过对话框创建图表 27411.2.2 使用功能区创建图表 27511.2.3 使用快捷键创建图表 27611.3 设置图表 27711.3.1 更改图表类型 27811.3.2 更改数据源 27911.3.3 设计图表布局 28011.3.4 设计图表样式 28111.4 创建各种类型的图表 28111.4.1 使用折线图显示产品销量 28111.4.2 使用饼图显示人口比例 28211.4.3 使用柱形图显示学生成绩差距 28211.4.4 使用XY散点图显示人口分布 28311.5 美化图表 28411.5.1 设置图表标题 28411.5.2 设置图表背景 28411.5.3 设置图例 28611.5.4 设置数据标签 28711.5.5 设置坐标轴标题 28711.5.6 设置网格线 28811.6 实践案例与上机指导 28911.6.1 为图表添加趋势线 28911.6.2 添加数据系列 29011.6.3 插入迷你图 29311.7 思考与练习 295第12章 数据处理与分析 29712.1 数据的筛选 29812.1.1 自动筛选 29812.1.2 高级筛选 29912.2 数据的排序 30212.2.1 单条件排序 30212.2.2 多条件排序 30312.2.3 按行排序 30412.2.4 按笔画排序 30512.3 数据的分类汇总 30612.3.1 简单分类汇总 30612.3.2 删除分类汇总 30812.4 合并计算 30912.4.1 按位置合并计算 30912.4.2 按类别合并计算 31112.5 分级显示数据 31312.5.1 新建分级显示 31312.5.2 隐藏与显示明细数据 31412.6 实践案例与上机指导 31512.6.1 使用通配符进行模糊筛选 31512.6.2 按颜色排序 31612.6.3 取消和替换当前的分类汇总 31712.6.4 删除重复数据 31812.7 思考与练习 320第13章 使用数据透视表和数据透视图 32113.1 认识数据透视表与数据透视图 32213.1.1 认识数据透视表 32213.1.2 认识数据透视图 32213.2 创建与编辑数据透视表 32313.2.1 创建数据透视表 32313.2.2 设置数据透视表字段 32413.2.3 删除数据透视表 32513.3 操作数据透视表中的数据 32613.3.1 刷新数据透视表 32613.3.2 数据透视表的排序 32613.3.3 筛选数据透视表中的数据 32713.4 美化数据透视表 32813.4.1 更改数据透视表布局 32813.4.2 应用数据透视表样式 32913.5 创建与操作数据透视图 33013.5.1 使用数据区域创建数据透视图 33013.5.2 更改数据透视图类型 33213.5.3 筛选数据 33313.5.4 分析数据透视图 33313.6 实践案例与上机指导 33513.6.1 使用数据透视表创建数据透视图 33513.6.2 设置数据透视表的显示方式 33613.6.3 对数据透视表中的项目进行组合 33713.6.4 清除数据透视图 33813.7 思考与练习 339思考与练习答案 340