作者：罗刚君
出版社：电子工业出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
Excel 2013函数案例自学宝典(实战版)试读：
前言
■ 本书编写背景
Excel 2013的功能主要体现在将数据用表格式存储，并对数据进行运算、分析、预测，以及用直观的图表呈现数据等方面，其中数据运算和利用图表分析数据均占据相当重要的地位。本书针对函数运算和图表设计技巧提供了大量案例，并详尽、深入地分析操作思路与理论基础。
本书对Excel的常用函数进行了案例演示、语法解释，以及罗列同一案例的多种解法。全书讲述了135个常用内置函数（包含4个宏表函数）的应用，共469个案例，案例涉及财务、人事、业务、销售、仓储、教育等多个行业的应用。对于部分案例，提供了多种经典的解法，以拓展函数爱好者的思路。
■ 本书结构
本书根据函数的分类及用途分10章进行讲解。前8章演示八大类共130个常用函数的应用案例，第9章演示4个宏表函数的语法与应用案例，第10章讲解利用Excel 2013的新增函数WEBSERVICE执行中英文翻译的应用思路。
此外，为了扩展本书的知识面，拓展读者视野，本书的随书案例文件中将额外赠送6章PDF内容，其中前3章为函数相关内容，包含“函数在数据有效性与条件格式中的运用”、“公式的迭代计算”和“自定义函数”一共35个案例，后3章为图表案例教材，包含“利用图表分析数据”、“图表的高级应用”和“利用控件强化图表”一共47个案例。
■ 本书编写思路
本书主要通过案例展示函数的语法、参数以及提供不同行业中日常问题的解题思路。为了使每个案例的讲解更易于理解，本书对每个函数案例主要从以下4个方面进行演示。
1.案例演示（1）本书对Excel 2013集成的部分常用函数进行案例演示，以日常工作中使用率较高的函数通过案例展示其功能、语法及参数变化。（2）由于函数的重要性和工作中的使用率不同，书中对于每个函数的案例数量也是不同的。例如，ABS函数（绝对值函数）在工作中的应用率不如SUM函数（汇总函数），因此关于ABS函数的案例少于SUM函数的例案数量。（3）为了使本书适合不同读者群，以及让读者更好地理解每个案例的思路，本书对每个函数先以一个简单的案例讲解函数的基本功能与语法，然后再以进阶案例演示函数的高级应用。高级应用包括复杂的参数调用、多函数嵌套及数组公式。（4）每个案例尽量采用较具代表性的数据，有助于读者理解公式及体现案例的实用性。（5）部分案例的公式并非最佳方案，仅在于介绍一种解题思路，或者演示当前函数在此类问题中的运用。通常在“使用注意”中会对公式进行评价，对其他更好的公式也会一并罗列出来。
2.公式说明
每个案例对公式的设计思路或者某些代码、参数都提供解释。
对于某些难解的数组公式，将分步解释参数在不同步骤中值的变化，使读者可以更清晰地了解数组参数在公式中的作用。
3.使用注意
使用注意包括如下几项：（1）当前案例中函数的功能、语法、参数含义。（2）函数的适用范围。（3）当案例的条件变化时，如何修改公式。（4）当前案例中涉及的函数与其他类似函数的功能比较。（5）当前案例中公式的优缺点阐述。（6）对当前案例提供其他解法。
4.思考
提供与当前案例相关的题目供读者练习，或者当前函数的其他方面应用题供读者练习。同时附上解题的思路提示。
■ 本书学习指南（1）本书是基于Excel 2013进行写作的，但与Excel 2010的函数并没有多大区别，因此如果读者在使用Excel 2010也不会影响阅读。（2）本书不提供光盘，但是会提供全书案例文件供读者使用。同时还提供40个函数动画教材和50个图表动画教材，与案例文件保存在同一压缩档案中，读者可在如下网址中下载：http：//www.broadview.com.cn/24926也可以到售后服务群中下载。（3）本书各章节的内容连贯性不大，读者可以从任意章节开始阅读、学习，但是按顺序学习会有更好的效果，因为作者在写作时将简单的函数放在更靠前的位置。（4）读者在学习上遇到困难时应与作者联系，加入售后服务群参与讨论会有更好的学习效果。
■ 本书售后服务
本书提供售后服务QQ群，群号为145529755，阅读本书时遇有疑难可以加入本群向作者提问，或者与其他读者一并讨论函数与图表的更多技巧。
如果读者同时购买了《Excel 2010 VBA编程与实践》、《Excel函数、图表与透视表从入门到精通（修订版）》、《来吧，带你玩转Excel VBA》、《Excel VBA程序开发自学宝典（第3版）》、《Excel 2013函数案例自学宝典（实战版）》这些书中的任何三本，可以向作者申请价值60元的《Excel百宝箱可撤销版》插件，免费赠送。作者QQ号：670218239，E-mail地址：888＠excelbbx.net。
■ 感谢
参与本书编写的人员有：龚丹、吴炎翠、王丽云、郑英杰、周红霞、郭丽亚、龚太平、胡青春、葛度金、王益明、陈卫、秦伟、熊忠芳、张定文，在此一并表示感谢！罗刚君2014年12月1日
第1章 数学与三角函数
本章要点：
·
求和问题
·
求积问题
·
排列组合问题
·
余数问题
·
进制转换问题
·
绝对值问题
·
随机数问题
·
乘方与开方问题
· 数据舍与入问题
· 分类汇总问题
涉及函数：
SUM、SUMIF、SUMIFS、SUMPRODUCT、SUMSQ、PRODUCT、MMULT、COMBIN、PERMUT、MOD、BASE、ABS、RAND、RANDBETWEEN、POWER、SQRT、ODD、EVEN、TRUNC、INT、CEILING、FLOOR、ROUND、ROUNDDOWN、ROUNDUP、SUBTOTAL
案例细分：
· 汇总三个组别的产量
· 求所有工作表相同区域数据之和
· 求前三名产量之和
· 按条件汇总进库数量
· 使用通配符对车间人员的工资求和
· 按指定范围统计工资总和
· 汇总鼠标所在列中大于600的数据
· 对一车间的男性职工的工资求和
· 根据三边长计算三角形的面积
· 制作中文九九表
· 计算中奖率
· 对奇数行数据求和
· 设计工资条
· 根据身份证号码计算身份证拥有者的性别求和问题案例1 汇总三个组别的产量（SUM）
源文件：案例文件\第1章\001.xlsx
打开本书配套资源中的数据文件，在单元格H2中输入如下公式：
按【Enter】键后在H2单元格将显示统计结果，如图1.1所示。图1.1 对三个区域求和
公式说明：
SUM函数可对若干个区域中的数值或者逻辑值、表达式进行求和，它有1～255个参数。在本例中，SUM函数用于对B2：B7、D2：D7、F2：F7三个参数代表的三个区域求和。
鉴于本案例的特殊性，公式也可以改为“=SUM（B2：F7）”，函数会忽略区域中的文本。
使用注意：（1）从Excel 2007开始，SUM函数有1～255个参数。参数可以是单元格引用，也可以是表达式、数组、数值、字符串等。以下公式中SUM函数的参数就包含了单元格引用、表达式、数组和数值。（2）如果参数是文本型数字，SUM函数可以直接对它求和；如果参数是单元格地址，该单元格中有文本型数字（单元格的数字前有半角单引号“'”）则不会参与求和。例如：
=SUM（10，″10″）——结果为20
=SUM（10，A1）——当A1的值是文本“′10”时，公式结果为10（3）如果一定要对单元格中的文本型数字求和，应将文本型数字转换成数值。转换方法很多——使用Value、“--”、“*1”和“/1”等都可以，例如：
=SUM（10，VALUE（A1））——当A1的值是文本“′10”时，公式结果为20
=SUM（10，--（A1））——当A1的值是文本“′10”时，公式结果为20
案例链接：
思考：如果三个组的数据分别在三个工作表中，如何对数据求和?
提示：在每个区域地址之前添加工作表名及“！”，如“Sheet2！B2：B7”。案例2 求所有工作表相同区域数据之和（SUM）
源文件：案例文件\第1章\002.xlsx
假设生产车间有5个组，每组8人，在工作簿中将5个组别的生产数据分别存放于5个工作表中，现要求对所有组别的生产数据求和。
打开本书配套资源中的数据文件，在单元格D2中输入如下公式：
按下【Enter】键后，在D2单元格将显示5个组别的产量和，如图1.2所示。图1.2 所有工作表相同区域的数据之和
公式说明：
对多个工作表的相同区域求和不需要输入每个工作表的待求和区域地址，只需在输入“=SUM（”之后单击第一个待求和的工作表名称，然后按住【Shift】键单击最后一个待求和的工作表名称，最后选择待求和的区域即可。当单击【Enter】键后，Excel会自动产生第一个工作表名称和最后一个工作表名称，中间用冒号连接，后面自动添加叹号“！”与单元格地址。以此方式设置参数可以汇总任意一个相邻工作表的相同区域。
使用注意：（1）只有待求和的工作表相邻时才可使用本例的办法。（2）当函数的参数是单元格引用时，如果单元格处于公式所在工作表，那么只需要书写单元格地址即可；如果单元格与公式不在同一个工作表中，那么必须在单元格地址前添加工作表名称和“！”。避免忘记书写工作表名称的办法是通过选择单元格的方式产生单元格地址，而非手工输入字符。（3）不能使用包含公式所在单元格的区域作为SUM函数的参数，否则会产生循环引用，公式无法正确计算结果。例如，公式在D2单元格，那么使用B2：E2区域作参数则无法对该区域求和。
案例链接：
思考：求前两个工作表B2：B9以及后两个工作表B2：B9之和。
提示：可以用两组工作表的区域引用作为SUM函数的参数来完成求和，也可以用两个SUM函数对两组工作表的区域引用分别求和，然后相加。案例3 求当前表以外的所有工作表相同区域的总和（SUM）
源文件：案例文件\第1章\003.xlsx
在工作簿中有若干个工作表存放了员工每月的生产数据，每个工作表以月份命名，在最右方有一个总表，要求在总表中汇总每个员工的所有月份的产量总和。
打开本书配套资源中的数据文件，在单元格B2中输入如下公式：
按下【Enter】键后，公式会自动变成“=SUM（一月：五月！B2）”，同时计算出每一个员工的总产量。
将鼠标移到B2单元格的右下角，当鼠标指针变成黑色十字形状时，单击并向下拖动到B9即可将B2单元格的公式复制到B2：B9区域中，从而快速计算出每一位职工的产量之和。图1.3所示为对当前表以外的所有工作表求和，图1.4所示为下拉填充柄从而复制公式。图1.3 对当前表以外的所有工作表求和图1.4 下拉填充柄从而复制公式
当公式从B2向下填充到B9后，B2：B9将分别显示每一位职工的产量之和。不过尽管B3：B9区域的公式是通过B2复制而来，但是这8个单元格的公式并不相同。例如，B2单元格的公式是“=SUM（一月：五月！B2）”，而B9单元格的公式则是“=SUM（一月：五月！B9）”，效果如图1.5所示。图1.5 将B2的公式复制到B9后得到的公式
公式说明：（1）公式中两个单引号中间加“*”表示引用当前工作表以外的所有工作表。不过在新建工作表后，公式并不会自动更新引用范围，不会将新表追加进去。（2）使用填充柄可以将一个单元格的公式快速复制到相邻的其他单元格中。图1.4中黑色十字形的鼠标指针即为单元格的填充柄，它只在鼠标指向单元格右下角时出现。（3）本例公式中的单元格地址B2属于相对引用，如果采用绝对引用则填充公式后无法让公式分别计算出每位职工的产量。（4）与填充公式相关的一个知识点是引用。通过单元格地址引用单元格的值称为单元格引用，例如A1、C10、$F$5：$G$10和C$8。单元格引用包含相对引用、绝对引用和混合引用，A1和C10属于相对引用，$F$5：$G$10属于绝对引用，C$8则属于混合引用。（5）相对引用即基于包含公式的单元格与被引用的单元格之间的相对位置的单元格地址。如果复制公式，相对引用将自动调整单元格地址。相对引用采用A1样式。
绝对引用即公式中单元格的精确地址，与包含公式的单元格的位置无关。绝对引用采用的形式为$A$1，即在相对引用的单元格地址中添加“$”符号，表示将行与列锁定。
换一种说法，相对引用是基于参照坐标的相对位置，当参照点发生变化时，相对引用的单元格地址会相应地变化；绝对引用则相反，它没有参照点，所以复制公式时绝对引用总是保持不变。其中“$”符号表示将对象固定，包括行与列。
图1.6中B1单元格的公式采用相对引用方式引用A1单元格的值，当把B1复制到B2、B3、B4、B5时，公式会相应变化，B2不再引用A1的值，而是引用A2的值。B3、B4、B5的引用也以同样原理产生位移。图1.7的公式采用了绝对引用，所以B1单元格的公式复制到B3、B4、B5、B6后仍然只引用A1单元格的值。图1.6 相对引用图1.7 绝对引用（6）混合引用即一半相对引用、一半绝对引用。“$A1”表示相对行绝对列，因此属于混合引用，在公式中使用它引用数值且向右、向下填充后将得到图1.8所示效果。图中E1的公式是“=$A1”，公式向右填充后仍然只能引用A1单元格的值，而向下填充时公式会自动变化，从而引用A2、A3的值。“A$1”也属于混合引用，当包含此引用的公式向下填充时公式不会变化，向右填充时才会变化。图1.9正是“A$1”形式的引用效果。图1.8 相对行绝对列的引用效果图1.9 绝对行相对列的引用效果
使用注意：（1）公式中的“′*′”只能手动输入，无法通过鼠标选择工作表的方式产生该符号。（2）跨表引用区域时，区域地址和工作表名称不宜手工输入，通过选择区域自动产生区域地址的办法能提高速度和准确性。案例4 求前三名产量之和（SUM）
源文件：案例文件\第1章\004.xlsx
打开本书配套资源中的数据文件，在单元格D2中输入如下公式：
按下【Enter】键后，公式返回前三名产量之和，结果如图1.10所示。图1.10 求前三名产量之和
公式说明：
获取前N大值需要使用LARGE函数。LARGE函数表示从一组数据中获取第N个最大值，本例中要求统计前三大数据之和，因此采用常量数组“{1，2，3}”作为LARGE函数的参数分别提取第一大值、第二大值和第三大值，最后利用SUM函数将它们汇总。
本例是两个函数嵌套的应用案例。由于Excel没有提供单个函数来汇总前N大值，因此必须两者套用。Excel的函数都只能执行单一运算，复杂运算需要函数嵌套。
使用注意：（1）LARGE函数的第一参数表示数据源，第二参数表示要取第几个最大值。例如，公式“=LARGE（A1：A10，2）”表示计算A1：A10区域的第二大值。在本书的第4章会有大量的关于LARGE函数的应用案例和使用技巧介绍。（2）公式“LARGE（B2：B9，2）”表示计算B2：B9区域的第二大值，而公式“LARGE（B2：B9，{1，2，3}）”则表示同时计算B2：B9区域的第一、第二和第三大值。在它前面使用SUM函数可以将这3个数据汇总，从而得到区域中前三大值之和。（3）“{1，2，3}”是一个数组，表示同时包含1、2、3的数据组合。数组在函数公式中的地位很高，通过数组能实现更强大的数据运算，不过数组也更难以理解。在本章的末尾会提供数组的相关知识作为补充。（4）编写多个函数嵌套的公式时，初学者应该先写里面一层的函数，然后再写外面的函数，同时要注意将括号配对。例如，本例的公式可以先写“LARGE（B2：B10，{1，2，3}）”部分，然后再写外面的SUM函数和括号，当对函数能熟练应用后再一次性书写公式。
案例链接：
思考：求A2：C20区域的前十大值之和。
提示：根据实际情况修改区域地址和LARGE函数第二参数即可。案例5 按条件汇总进库数量（SUMIF）
源文件：案例文件\第1章\005.xlsx
B列用于标示进库和出库，C列则存放对应的进库数据和出库数据，现要求只汇总进库数据。
打开本书配套资源中的数据文件，在单元格E2中输入如下公式：
按下【Enter】键后，将显示当日数据中进库数量之和，结果如图1.11所示。图1.11 显示当日数据中进库数量之和
公式说明：
SUMIF属于条件求和函数，它有3个参数，第一参数为求和的条件区域；第二参数表示条件，可以使用“＞”、“＜”、“=”等比较运算符来限制条件范围；第三参数是可选参数，表示实际参与求和的区域。本例公式表示当第一参数代表的区域B2：B10等于条件“进库”时，那么对第三参数C2：C10中对应的单元格求和。本例中B2、B3、B5和B8的值是“进库”，因此实际求和的单元格是C2、C3、C5和C8。
使用注意：（1）SUMIF函数用于条件求和，可以理解为SUM+IF两个函数的集合，对于符合单条件求和的情况常使用SUMIF函数，而条件较多时则使用SUM+IF两个函数嵌套来完成。（2）SUMIF也有它自身的局限性，它的第一参数和第三参数只能是区域引用，不能是数组。例如，“{1，2，3}”、“ROW（20：30）”和“A1：A10+1”都不能作为SUMIF的第一和第三参数，否则无法执行求和。（3）当SUMIF的第一参数既是条件区域也是求和区域时，那么第三参数允许忽略，下一个案例将会演示此类应用。（4）SUMIF的第二参数允许使用“＞”、“＜”、“=”、“＜=”和“＞=”等比较运算符，当表示等于某个条件时，允许忽略等号。因此本例的公式也可以简化为：
案例链接：
思考：计算当日12：00之后的送货数量总和。
提示：将公式中代表条件区域和条件的第一、第二参数修改为A2：A10及“＞12：00”。案例6 对大于1000的数据求和（SUMIF）
源文件：案例文件\第1章\006.xlsx
对仓库进货数据中单次进货超过1 000者求和。
打开本书配套资源中的数据文件，在单元格D2中输入如下公式：
按下【Enter】键后，公式将返回当日数据中超过1 000的数量之和，结果如图1.12所示。图1.12 计算仓库进库数量之和
公式说明：
SUMIF函数的第三参数是可选参数，如果忽略则对第一参数所代表的区域求和，本例中条件区域和求和区域都是B2：B8，因此忽略了第三参数。
如果添加第三参数也可以得到正确结果，公式如下：
使用注意：（1）SUMIF函数省略第三参数的用法只针对第一参数既是条件区域又是求和区域的情况，而且这个区域必须是数值，否则没有意义，文本的合计永远是0。（2）如果需要对区域中不等于1 000的数据求和，则公式可以写为：（3）如果需要对区域中等于1 000的数据进行求和，则公式可以写为：
当条件是数值时，SUMIF函数的第二参数有3种表示方法。假设条件是1 000，那么第二参数可以写为“″=1000″”、“″1000″”或者“1000”，三种写法的计算结果一致。但是条件是文本时则只有两种写法，不能忽略等号的同时又忽略引号。（4）当求和区域中存在文本时（本例的求和区域是B2：B8），SUMIF函数会自动略过文本。假设在B2单元格输入文本“函数”，“函数”二字是大于数值1 000的，但是它并不会参与求和，因此公式的结果不会产生变化。
案例链接：
思考：计算图1.12中小于等于1 000的进库数据之和。
提示：参考本例的公式修改一下逻辑运算符即可。案例7 使用通配符对车间人员的工资求和（SUMIF）
源文件：案例文件\第1章\007.xlsx
公司包含人事部、采购部、一车间、二车间、三车间、业务部和印刷分部7个部门，现要求汇总车间的职工工资。
打开本书配套资源中的数据文件，在单元格E2中输入如下公式：
按下【Enter】键后，将返回所有车间人员的工资之和，结果如图1.13所示。图1.13 对所有车间人员的工资求和
公式说明：
本公式中SUMIF函数的第一参数是条件区域，第二参数“?车间”表示条件是以“车间”二字结尾，同时其长度为3。第三参数是简写，虽然只有“C2”，而实际计算时是用C2：C10区域参与求和。整个公式的含义是如果A2：A10区域中任意单元格包含3个字而且以“车间”二字结尾，那么对该单元格在C列中对应的单元格求和。
本例最终的求和单元格是C2、C4、C6、C8：C10。
使用注意：（1）SUMIF函数的第二参数支持通配符“*”和“?”。其中“?”表示任意单个字符，“*”表示任意长度的字符。在本例中比较特殊，使用“?”和“*”都能得到同样结果。（2）如果单元格中本身就有“?”符号，那么可以使用第三个通配符“～”。例如：
=SUMIF（A2：A10，″～?″，C2）——此处的“?”不再具备通配符的功能，仅当作标点符号“?”处理。（3）如果统计工资表中的“高周波车间”和“印刷车间”等不同长度的部门名称，本公式需要修改为“=SUMIF（A2：A10，″*车间″，C2）”。（4）通配符包括“*”、“?”和“～”三个符号，它们只能在半角状态下输入。
案例链接：
思考：计算本例中非车间部门的工资。
提示：可以采取两个思路，其一是全部求和，然后减去车间的工资总数，其二是利用“＜＞?车间”作为SUMIF函数的求和条件。案例8 按指定范围统计工资总和（SUMIF）
源文件：案例文件\第1章\008.xlsx
A列是姓名、B列是工资，要求统计3 000～3 500之间的工资总和（不含3 000）。
打开本书配套资源中的数据文件，在单元格D2中输入如下公式：
按下【Enter】键后，将返回3 000～3 500之间的工资总和，结果如图1.14所示。图1.14 对3 000～3 500之间的工资求和
公式说明：
SUMIF函数只能在求和时设置单个条件，从而得到单个统计结果。由于本例的需求是“3 000～3 500之间”，属于双条件求和，因此采用“″＜=″&{3000，3500}”作为参数，从而生成两个结果，即小于等于3 000的工资之和以及小于等于3500的工资之和。
由于目标是对3 000到3 500之间的值求和，相当于小于等于3500的工资合计减去小于等于3 000的工资合计，因此本例在SUMIF函数之后使用“*{-1，1}”，从而将小于等于3 000的工资之和转换成负数（乘以负1的结果），最后使用SUM函数将SUMIF的两个计算结果求和，从而得到最终的计3 000到3 500之间的工资总和（不含3 000）。
使用注意：（1）SUMIF函数只能处理单条件求和，如果要求同时满足多个条件才参与求和，那么应使用SUMIFS函数，在后面的几页中会有相关的案例演示；如果要求满足多条件之一就参与求和则应将SUM和SUMIF两个函数搭配使用。（2）本例中SUMIF的第二参数使用“″＜=″&{3000，3500}”相当于将公式变成了“=SUMIF（B2：B10，″＜=3000″）”和“=SUMIF（B2：B10，″＜=3500″）”，从而得到两个结果，而一个单元格中只能显示一个结果，因此需要在外面添加SUM函数将它们求和。（3）本例的公式也可以修改为：
案例链接：
思考：计算图1.14中小于3 000以及大于3 500的数据之和。
提示：使用两次SUMIF函数分别计算两个范围的值，然后两者相加即可。案例9 计算前三名和后三名的数据之和（SUMIF）
源文件：案例文件\第1章\009.xlsx
打开本书配套资源中的数据文件，在单元格D2中输入如下公式：
按下【Enter】键后，将返回前三名和后三名的数据之和，结果如图1.15所示。图1.15 前三名和后三名的数据之和
公式说明：
本例中用两个SUMIF函数分别计算三个最大值之和与三个最小值之和，然后相加。其中LARGE用于计算第三大值，SMALL用于计算第三小值。在本书的第4章会有更多、更详细的关于LARGE和SMALL函数的应用案例。
试读结束[说明：试读内容隐藏了图片]