作者：曾贤志
出版社：电子工业出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
加薪不加班：174个Excel函数案例闯职场试读：
前言
工作表函数是Excel中最为重要的功能之一。除可以在工作表的单元格中使用外，还可以将函数与条件格式、高级筛选、数据验证、数据透视表等功能相结合。
本书从零开始讲解函数，循序渐进地讲解函数的高级应用。本书最大的特点是一边讲解函数知识点，一边介绍如何在实际中应用知识点，并且对每个实例的解法进行细致解析，让读者对函数的应用更加熟练。
本书内容
本书一共分为12章。
第1章讲解Excel函数的编写技巧、公式运算符、单元格引用、常用函数等相关基础知识。
第2章讲解数组的基础知识并介绍对应的实例应用。
第3~8章讲解一些常用的函数，具体包括逻辑函数与信息函数；条件求和类、条件计数类、条件求平均类函数，以及求极大值、极小值类函数；日期、星期与工作日、时间函数；取整、求余、舍入、随机等数学函数；提取、查找、替换、重复、清除、格式转换、字母转换等文本函数；查找引用函数。
第9章讲解一些不常用但又非常有用的高级函数。
第10章讲解Excel 2016之后新增加的多条件判断、多条件求最大值、多条件求最小值以及合并函数的应用。
第11章讲解函数的三维引用技术的应用。
第12章讲解函数与Excel其他功能的结合使用。
本书读者对象
本书适合有一定Excel基础的读者，或者有对数据进行整理、汇总、分析等处理需求的读者。同样适合从事财务、统计、仓库管理、数据分析、电子商务等职业的读者。
本书配套资源
本书中的实例均有示例文件，请到以下网址下载：
http://www.broadview.com.cn/35695
致谢
完成本书整整花了两年时间，这期间得到很多人的帮助和支持。在此，感谢王静老师及出版社的小伙伴们。最后要特别感谢我的妻子曾瑜对我工作的支持，也感谢孩子们理解我在写作期间没有更多的时间关心和陪伴他们成长。作者
读者服务
轻松注册成为博文视点社区用户（www.broadview.com.cn），扫码直达本书页面。
● 提交勘误：您对书中内容的修改意见可在【提交勘误】处提交，若被采纳，将获赠博文视点社区积分（在您购买电子书时，积分可用来抵扣相应金额）。
● 与读者交流：在页面下方【读者评论】处留下您的疑问或观点，与其他读者一同学习交流。页面入口：http：//www.broadview.com.cn/35695
第1章 菜鸟必学——函数与公式基础
Excel工作表函数是专门用于分析、处理数据的一种参数化的交互式功能。在学习函数之前，我们要了解一些与函数相关的重要基础知识，如函数录入技巧、函数参数编辑技巧、公式填充技巧、公式中的运算符、单元格引用等。在本书后面的内容中，会逐步讲解这些知识，这样读者学习函数才更得心应手。
1.1 公式与函数的关系
Excel公式是以等号开头，可以计算出结果的一串表达式。公式中可以包含运算符、单元格引用、函数、逻辑值、常量等各种元素，只要符合Excel公式的编写规则即可。公式中可以没有函数，但函数一定是在公式中编写的。让Excel公式“扬名立万”的是函数，甚至我们通常都说“函数公式”一词。
1.2 公式的编写与填充技巧
公式编写：将鼠标光标定位到单元格后直接输入等号即可开始编写公式，也可以在编辑栏中编写公式。公式编写完成后，按Enter键来结束编写。
公式填充：实际上指的是公式复制，当在某个单元格（通常在表格的第1个单元格）中编写好公式后，将鼠标光标放到公式单元格的右下角，如图1-1所示，当鼠标光标变成黑色十字形状时，按住鼠标左键向下拖曳或者双击填充柄，即可完成公式的填充复制，结果如图1-2所示。图1-1图1-2
1.3 函数的组成
函数的结构是：函数名（参数1,参数2,……）。需要注意的是，函数名不区分大小写，函数中参数与参数之间的分隔符为逗号（半角逗号），编辑函数中的参数值时，一定要按照参数要求来填写。否则，将会使整条公式返回的值是错误值。参照图1-3中的函数使用方法。图1-3
1.4 函数录入技巧
由于函数一般是由英文单词构成的，在录入函数时有些单词太长或记不住，可以去【公式】选项卡→【函数库】组中选择。如果公式中使用的函数比较少，这种方法还是比较好的。如果公式中有比较多的函数，且函数之间又有多层嵌套，用选择函数的方式来编辑公式会非常烦琐。所以笔者建议读者最好手动输入函数，刚开始可能不太习惯，时间长了，你就会觉得这种方法能与思维合二为一，想哪儿写哪儿。
那怎样更快地手动输入函数呢?在输入函数时，Excel有一种智能感知功能。如果需要输入SUMIF函数，则只需要在单元格中输入前几个字母，Excel就会自动列出以所输字母开头的一系列函数，如图1-4所示，用鼠标单击或者使用键盘中的上下方向键使光标位移到要使用的函数上（同时可以看到该函数的相关说明），然后按Tab键就可以补全这个函数。或者直接用鼠标光标指向要选择的函数，双击完成补全，效果如图1-5所示。填写好函数的相关参数之后，按Enter键，函数会自动补全右括号。图1-4图1-5
1.5 函数编辑技巧
在输入函数后，需要迅速进入函数的编辑状态。可以使用对话框和公式栏两种编辑模式。
对话框编辑模式：在图1-5的模式下，单击编辑栏的图标，或者按Ctrl+A组合键，两种方法均可进入到对话框编辑模式，如图1-6所示。在这种模式下进行编辑的优点是：在图1-6中1位置可看到该函数的说明，在2位置可看到函数中每个参数的说明。图1-6中的SUMIF函数有3个参数，光标分别定位到3个参数框，就会看到2位置的说明是不一样的。对于函数初学者来说，这种模式非常方便。图1-6
公式栏编辑模式：在补全函数后，按下Ctrl+Shift+A组合键，就将会把函数的相关参数及右括号全部补全。图1-7中框线框住的部分是函数的参数，如果参数名以加粗显示，则表示该参数处于编辑状态。编辑好后，单击提示框中下一个参数名，则下一个参数名又显示为加粗，以此类推。这样方便用户观察函数正处于哪个参数的编辑状态下，在多个函数嵌套使用时，避免因忘记处于哪个参数的编辑状态而产生错误。图1-7
1.6 常用函数的应用
Excel工作表函数有多种类别，但在函数分类中却单独分了一类为常用函数。图1-8中用框线标记出来的求和、平均值、计数、最大值、最小值就是工作中最常用的函数。接下来，就学学这些使用频率比较高的函数。图1-8
● 求和（SUM）：计算单元格区域中所有数值的和。
● 平均值（AVERAGE）：返回参数的平均值（算术平均值）。
● 计数（COUNT）：计算包含数字的单元格以及参数列表中数字的个数。
● 最大值（MAX）：返回一组值中的最大值。
● 最小值（MIN）：返回一组值中的最小值。
实例1 求每个销售员在第1季度的总业绩\平均业绩\最高业绩\最低业绩\月份数
问题描述：图1-9是2017年××公司第1季度各个销售员的销售业绩，但情况比较复杂，在业绩表中有休假、空单元格（无业绩）情况。如果要求汇总每个销售员这 3 个月的总业绩、平均业绩等情况，该如何处理呢？图1-9
解决思路：其实解决这些特殊问题非常简单，即直接无视，应用相关的汇总函数计算即可。遇到文本、空值这些情况时自然会忽略不计，但前提是我们必须知道这些函数有这些特性。真是虚惊一场，接下来直接操作吧!
操作步骤：如图1-10所示，以第1条记录为基准写入对应的公式，其中的“C3:E3”是指“íí036兰爱媛”1月到3月的业绩区域。图1-10
步骤1：在F3、G3、H3、I3、J3单元格中输入对应的公式。
步骤2：选择F3:J3单元格区域，将鼠标光标指到这个区域右下角的填充柄，向下拖曳即可完成公式填充。
以上5个函数是较为常用的，关于这些函数的相关特性，我们将在后面的章节通过结合其他函数展示。
1.7 Excel公式运算符
在Excel中编写公式时，经常会使用运算符。运算符分为算术运算符、比较运算符、连接运算符、引用运算符。接下来一一详解这些运算符在公式中的使用方法。1.7.1 算术运算符
图1-11列出了常见的算术运算符，并介绍了简单的应用实例。如果文本型数值和逻辑值遇到这些算术运算符，那么它们将会变成数字型数值。后面章节将有非常多这种应用。图1-11
实例2 使用SUM函数计算每个销售员的业绩在总业绩中的占比
问题描述：图1-12中列出了2017年1月份各个销售员的销售业绩，现在需要统计一下每个销售员的业绩在他们总业绩中的占比是多少。图1-12
解决思路：首先要知道计算占比的公式是：个人占比=个人业绩/总业绩。个人业绩已经知道了，那总业绩如何获取呢?自然是将每个人的业绩加起来，就得到总业绩。那用什么函数来实现呢?当然是SUM函数。
操作步骤：如图1-13所示，以C3单元格为基准，首先在该单元格中输入公式：=B3/SUM($B$3:$B$6)，然后向下填充公式完成计算，下面分析一下公式的运算过程。图1-13
步骤1：公式=B3/SUM（$B$3:$B$6）中的SUM函数引用的“$B$3:$B$6”中使用了美元符号（$）来固定这个区域，目的是为了后续向下填充公式时，求和的区域不变。后面的章节中会详细介绍这些技术细节。
步骤2：B3单元格中的78为罗娟的个人业绩，函数SUM($B$3:$B$6)的求和结果283为4个人的总业绩，直接相除=78/283，即得到罗娟的业绩在总业绩中的占比。向下填充公式即可得到其他销售员的占比情况。
步骤3：如果发现步骤2计算出来的值是小数，而不是百分比，则在“设置单元格格式”对话框中将数字格式设置为“百分比”格式。1.7.2 比较运算符
比较运算符是用于比较大小的符号，如图1-14所示，我们印象中通常只能比较数字的大小。但在Excel公式中，文本也可以进行比较，按从小到大的顺序排列为：数字、字母、汉字。
比较运算的结果一定是个逻辑值（TRUE和FALSE），如果比较结果成立，则返回值为TRUE，不成立则为FALSE。TRUE相当于1，FALSE相当于0。有时在编写公式时，需要将TRUE和FALSE转为对应的数字。如何转换呢?只需要将逻辑值做不改变原值的算术运算即可。图1-15中列出了常见的将逻辑值转为1和0的方法。图1-14图1-15
通过图1-15的转换方法也证明了TRUE相当于1，FALSE相当于0。当然，逻辑值也不是一定只能转换为对应的1和0，也可以将逻辑值和其他数字进行算术运算，关键看公式需求。在运算过程中始终把TRUE和FALSE看作1和0就对了。
除此之外，公式中的逻辑值之间也经常做算术运算，特别是相乘和相加的运算。图1-16所示为逻辑值之间的相乘、相加运算的结果，从中可以发现以下规律。
● 相乘运算：必须所有逻辑值为TRUE，结果才能为1，否则为0。相当于逻辑“与”运算，只有全部为真时才为真。
● 相加运算：必须所有逻辑值为 FALSE，结果才能为 0，否则为非 0。相当于逻辑或运算，只有全部为假时才为假。图1-16
实例3 两科成绩同时大于或等于90分的学生奖励100元
问题描述：图1-17显示的是一张学生成绩表，包含科目有语文和数学。现在需要判断，若某个学生两个科目的分数都同时大于或等于90分，则发放奖金100元，否则奖金为0。图1-17
解决思路：先分别用比较运算符判断“语文”和“数学”是否大于或等于90，再对判断出来的两个逻辑值进行相乘运算，结果为1则证明两个条件同时成立，结果为0则不同时成立。然后将得到的这个数字乘以100，同时成立则是1í100，结果为100，不同时成立则是0í100，结果为0。
操作步骤：如图1-18所示，以D3单元格为基准，输入公式=(B3>=90)*(C3>=90)*100，向下填充公式完成计算，下面分析一下公式的运算过程。图1-18
步骤1：先判断语文（B3>=90），结果为TRUE，再判断数学（C3>=90），结果为TRUE，然后将两个分数判断出来的逻辑值相乘，TRUE*TRUE，结果为1。
步骤2：将步骤1得到的值1乘以100，得到100，便计算出了第1条记录的奖金为100元。后面的记录也按此思路进行计算。1.7.3 连接运算符
Excel公式中的&是连接运算符，连接运算符可以直接连接数字、文本、单元格。不同的是数字与单元格的连接可以直接连接，而在连接文本时，被连接的文本必须加双引号。无论是怎样的连接方式，最后返回的数据类型都是文本。很多初学者搞不清楚数字、单元格、文本之间到底该怎么连接?下面用图1-19所示的一张表罗列出来。图1-19
实例4 上半年和下半年业绩均为优的员工奖金为100 000元，其他奖金为80000元
问题描述：图1-20的表格中是2016年上半年和下半年每个员工的业绩等级情况。现在需要判断，如果某员工上半年和下半年的等级都同时等于“优”，那么该员工的奖金为100000元，否则为80000元。图1-20
解决思路：本实例和实例3的要求很相似，也可以用实例3的解决思路来完成，但本实例的目的是应用连接运算符解题。在判断上、下半年是否等于“优”时，他们的判断条件是相同的，所以可以将两个条件合并到一起进行判断，而不用分别判断。然后根据判断后返回的逻辑值再计算出对应的奖金。
操作步骤：如图1-21所示，以D3单元格为基准，输入公式=((B3&C3="优优")*2+8)/1%%，向下填充公式，完成计算。接下来分析一下运算过程。图1-21
步骤1：先合并B3&C3的值，再与条件〝优优〝做等于比较。公式写法为：（B3&C3=〝优优〝）。
步骤2：如果（B3&C3=〝优优〝）比较结果为TRUE，则TRUE*2+8，等于10；如果比较结果为FALSE，则FALSE*2+8，等于8。
步骤3：将步骤2计算的结果除以1%%，相当于除以0.0001，本质就是乘以10000。最终公式是：（（B3&C3=〝优优〝）*2+8）/1%%。1.7.4 引用运算符
在Excel工作表中写公式时，引用单元格的操作非常频繁，引用单个单元格的表示方法读者应该比较熟悉，但引用各种单元格区域的表示方法却不一定了解。引用单元格区域通常分为区域引用、联合引用、交叉引用。
● 区域引用：单元格区域用冒号（：）连接两个对角单元格地址，图1-22所示区域表示成A1：C5、C5：A1、C1：A5、A5：C1均可。
● 联合引用：将两个及以上单元格区域同时引用时，可以使用逗号（，）连接，图1-23所示的区域表示为A1：B3，B5：D8。
● 交叉引用：引用两个及以上单元格区域的重叠区域时，可以使用空格连接，图1-24所示A1：C5 B3：D6区域，最终引用区域是B3：C5。图1-22图1-23图1-241.7.5 运算符优先顺序
学习完常用的运算符之后，还要熟悉各种运算符之间的先后运算顺序，否则编写公式时很容易出错，表1-1列出了各种运算符之间的优先运算顺序。表1-1
注意 要想提高运算符的优先级，则可以使用小括号（），公式中的括号可以使用多组括号嵌套。运算的优先级顺序则是由内向外的，也就是内层括号的优先级高于外层括号。
1.8 单元格引用
什么是单元格引用?比如，就是在B1单元格中的公式引用了A1单元格，那么B1与A1就有产生了对应关系。可产生相对引用、绝对引用、混合引用3种对应关系。单元格引用在Excel公式的编写中是非常重要的知识点，下面讲解这3种引用关系。1.8.1 相对引用
相对引用，就是保持引用单元格和被引用单元格的相对位置始终不变。相对引用也是默认的引用关系。如图1-25所示，在C2单元格中将B2单元格的工资，再加200。C2单元格在向下填充时，公式中B2单元格的行号也会跟着相对变化，如图1-26所示。如果单元格横向填充，则相对变化的是列号。总结成一句话：将有公式的单元格向上或向下填充时，行号变化，向左或向右填充时列号变化，但引用单元格和被引用单元格的位置关系始终不变，这就是相对引用。图1-25图1-26
试读结束[说明：试读内容隐藏了图片]