作者：雏志资讯,龙建祥,张铁军
出版社：人民邮电出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
随身查：Excel函数与公式综合应用技巧试读：
版权信息书名：随身查：Excel函数与公式综合应用技巧作者：雏志资讯,龙建祥,张铁军排版：上官雅弘出版社：人民邮电出版社出版时间：2014-09-01ISBN：9787115218629本书由人民邮电出版社授权北京当当科文电子商务有限公司制作与发行。—·版权所有 侵权必究·—前 言Excel提供了360多个函数，这些函数在数据统计、数据处理、数据分析过程中有着举足轻重的地位。通过函数，可以对不同数据进行计算、逻辑赋值、分类、查询、分析……正是因为这些函数，Excel的功能才变得如此强大，深受各行各业的用户喜爱。
本书以短小精悍的技巧来分解Excel中各类常用函数的应用操作，做到每个函数的应用都能模拟实际的使用环境。这一特点可以让读者在首次接触该函数时就能体会其重大作用，联想到自己工作中哪些地方可以用这些函数来处理。因此可以为读者的学习带来极大的帮助。
另外，本书是随身查形式的口袋本，技巧实属精选，以实用为主，既不累赘，也不忽略重点，是目前生活节奏快、工作繁忙的办公一族学习的首选。第1章公式编辑与数据源引用技巧1.1 公式的输入与编辑例1 只查看长公式中某一步的计算结果
在一个复杂的公式中，若要调试其中某部分的运算公式，可以按下面的方法实现只查看该部分的运算结果。
❶ 选中含有公式的单元格，在编辑栏中选中需要查看其结果的部分公式，如图1-1所示。图1-1
➋ 按键盘上的“F9”功能键，即可计算出选中部分对应的结果，如图1-2所示。图1-2提示被选中的部分必须是一个完整的、可以得出运算结果的公式。否则不能得到正确的结果，同时还会显示错误提示信息。
➌ 查看后按“Esc”功能键，即可还原。例2 一次性选中公式中引用的单元格
如果想查看某一单元格中的公式是引用了哪些单元格进行计算的，可以按如下方法快速选中。
❶ 例如选中F3单元格（F3单元格中的公式为：=SUMIF(B2:B8，E3，C2:C8)）。
❷ 在英文输入状态下，按键盘上的“Ctrl＋[”组合键，即可快速选取公式中引用的所有单元格，如图1-3所示。图1-3例3 隐藏公式
如果要将工作表所有公式都隐藏起来，可以按如下方法操作。
❶ 在工作表中，单击编辑界面的行列交叉处，选中整张工作表所有单元格，单击鼠标右键，选择“设置单元格格式”命令，打开“单元格格式”对话框。
❷ 选择“保护”选项卡，撤选“锁定”复选框，选中“隐藏”复选框，如图1-4所示。
❸ 单击“确定”按钮回到工作表中，在菜单中依次单击“工具”→“保护”→“保护工作表”，打开“保护工作表”对话框，设置保护密码，如图1-5所示。图1-4图1-5
❹ 单击“确定”按钮，提示再次输入密码。
❺ 设置完成后，选中输入了公式的单元格，可以看到，无论是在单元格中还是在编辑栏中，都看不到公式了，如图1-6所示。图1-6例4 大范围复制公式
当某一单元格中设置了公式后，如果该行或该列其他单元格需要使用同一类型公式，常用的方法是选中该单元格，将光标定位到单元格右下角，出现黑色十字形时按住鼠标左键进行拖动复制。但如果表格数据非常多（如有2000行），采用此方法会有些不便，此时可按如下技巧操作。
❶ 在地址栏中填入同列最后的单元格地址（本例为方便学习只选择到G16单元格），如图1-7所示，然后按“Shift＋Enter”组合键即可选中G2单元格到G16单元格地址之间的区域，如图1-8所示。图1-7图1-8
❷ 将光标定位到公式编辑栏中，按“Ctrl＋Enter”组合键，即可一次性完成对选中单元格的公式复制，如图1-9所示。图1-9例5 将公式运算结果转换为数值
在完成公式计算后，为了方便数据的引用，可以将包含公式的单元格中的数据转换为数值形式。
❶ 选中包含公式的单元格，按“Ctrl＋C”组合键执行复制，然后再按“Ctrl＋V”组合键执行粘贴。
❷ 此时会在选中单元格的右下位置出现一个“选择粘贴”按钮，单击该按钮打开下拉菜单，选择“只有值”选项（如图1-10所示），即可实现将原本包含公式的单元格数据转换为数值（可以选中单元格在编辑栏中查看）。图1-101.2 公式中数据源的引用例6 数据源的相对引用
公式的使用就是对数据源的引用，默认使用相对引用方式。采用这种方式引用的数据源，当将公式复制到其他位置时，公式中的单元格地址会随着改变。下面举出一个实例说明需要使用数据源相对引用方式的情况。
如图1-11所示，选中H3单元格，在公式编辑栏中可以看到该单元格的公式。图1-11
❶ 选中H3单元格，将光标定位到该单元格右下角，当出现黑色十字形时按住鼠标左键向下拖动即可快速复制公式。
❷ 复制得到的公式，数据源自动更改，H4单元格的公式为：=IF（F4=0，0，（G4-F4）/F4），如图1-12所示；H5单元格的公式为：=IF（F5=0，0，（G5-F5）/F5），如图1-13所示。图1-12图1-13例7 数据源的绝对引用
所谓数据源的绝对引用，是指把公式复制或引入到新位置，公式中的固定单元格地址保持不变。要对数据源采用绝对引用方式，需要使用“$”符号来标注。下面用一个实例说明需要使用数据源绝对引用方式的情况。
❶ 如图1-14所示，选中C2单元格，在公式编辑栏中可以看到该单元格的公式（既使用了相对引用的单元格，也使用了绝对引用的单元格）。图1-14
❷ 选中C2单元格，将光标定位到该单元格右下角，当出现黑色十字形时按住鼠标左键向下拖动即可快速复制公式。
❸ 复制得到的公式，相对引用的数据源自动更改，绝对引用的数据源不做任何更改。如图1-15所示，选中C4单元格，可以与C2进行公式的对比。图1-15提示在通常情况下，绝对数据源都是配合相对数据源一起应用到公式中的。单纯使用绝对数据源，在进行公式复制时，得到的结果都是一样的，因此不具有任何意义。例8 引用当前工作表之外的单元格
在进行公式运算时，很多时候需要使用其他工作表的数据源来参与计算。这时，需要按如下格式来引用：工作表名!数据源地址。比如要统计销售额时，将各个季度的销售额统计在不同的工作表中，现在要统计出各个季度的总销售额，则需要引用多张工作表中的数据。
❶ 选中要显示统计值的单元格，首先输入等号及函数等，如此处输入：=SUM（，如图1-16所示。图1-16
❷ 用鼠标在“一分部销售”工作表标签上单击，切换到“一分部销售”工作表中，选中参与计算的单元格，注意看引用单元格的前面都添加了工作表名称标识，如图1-17所示。图1-17
❸ 接着再输入其他运算符，选择需要引用的单元格区域等，完成后按回车键得到计算结果，如图1-18所示。图1-18提示如果公式使用熟练了，要引用其他工作表中的单元格时，也可以在公式编辑栏中直接输入公式，不过也要使用“工作表名!数据源地址”这种格式。例9 在公式中引用多个工作表中的同一单元格
在公式中可以引用多个工作表中的同一单元格进行计算。
❶ 选中要显示统计值的单元格，首先输入前半部分公式：=SUM（，如图1-19所示。图1-19
❷ 在“一分部销售”工作表标签上单击鼠标，然后按住“Shift”键，在“三分部销售”工作表标签上单击鼠标，即选中了所有要参加计算的工作表为“一分部销售：三分部销售”（3张工作表）。
❸ 用鼠标选中相同数据源所在的单元格，此例为“D13”，接着再完成公式的输入，按回车键得到计算结果，如图1-20所示。图1-20例10 引用多个工作簿中的数据源来进行计算
有时为了实现一些复杂的运算或是对数据进行比较，还需要引用其他工作簿中的数据进行计算才能达到求解目的。多工作簿数据源引用的格式为：[工作簿名称]工作表名!数据源地址。比如本例中要比较下半年销售额与上半年的销售额，上半年销售额与下半年销售额分别保存在两个工作簿中，此时可以按如下方法设置公式。
❶ 首先打开“销售统计（上半年）”工作簿，在“总销售情况”工作表的C3单元格中显示了上半年的销售金额总值，如图1-21所示。图1-21
❷ 在当前工作簿中选中要显示求解值的单元格，输入公式的前半部分，如图1-22所示。图1-22
❸ 接着切换到“销售统计（上半年）”工作簿，选择参与运算的数据源所在工作表（即“总销售情况”），然后再选择参与运算的单元格或单元格区域，如图1-23所示（从公式编辑栏中可以看到完整的公式）。图1-23例11 在相对引用和绝对引用之间进行切换
使用键盘上的“F4”功能键可以快速地在相对引用和绝对引用之间进行切换。下面以“=SUM（B2:D2）”为例，依次按“F4”键，得到结果如下。
❶ 在包含公式的单元格上双击，选中公式全部内容，按下“F4”键，该公式内容变为“=SUM（$B$2:$D$2）”，表示对行列单元格均进行绝对引用。
❷ 第二次按下“F4”键，公式内容又变为“=SUM（B$2:D$2）”，表示对行绝对引用，列仍采用相对引用。
❸ 第三次按下“F4”键，公式则变为“=SUM（$B2:$D2）”，表示对列绝对引用，行仍采用相对引用。
❹ 第四次按下“F4”键时，公式变回到初始状态：=SUM（B2:D2）。继续按“F4”键，将再次进行循环。第2章逻辑函数范例应用技巧例12 快速判断给定值是否在指定区间
IF函数是根据指定的条件来判断其“真”（TRUE）、“假”（FALSE），从而返回相应的内容。在本例数据表的B列（上限）与C列（下限）中显示了一个数据区间。通过IF函数可以判断D列的值是否在B列与C列的数据之间。
❶ 选中E2单元格，在编辑栏中输入公式：=IF（D2＜B2，IF（D2＞C2，"在"，"不在"），"不在"）。
按回车键，即可判断D2单元格的值是否在C2与B2之间，并返回相应值。
❷ 选中E2单元格，向下复制公式，可一次性判断出D列的数值是否在B列与C列之间，如图2-1所示。图2-1例13 根据代码返回部门名称
数据表A列中显示为员工编码，其中第一个字母代表其所在部门（Y代表研发部，X代表销售部，S代表生产部），现在可以结合LEFT函数来根据编码中的第一个字母自动返回其所属部门。
❶ 选中C2单元格，在编辑栏中输入公式：=IF（LEFT（A2）="Y"，"研发部"，IF（LEFT（A2）="X"，"销售部"，IF（LEFT（A2）="S"，"生产部"，""））），按回车键，即可根据A2单元格中内容返回所属部门。
❷ 选中C2单元格，向下拖动进行公式复制，可实现快速返回所属部门，如图2-2所示。图2-2例14 考评成绩是否合格
AND函数一般用来检验一组数据是否都满足条件。因此本例实现利用AND函数配合IF函数进行成绩评定，即各项成绩都达标时显示“合格”，否则显示为“不合格”。
❶ 选中E2单元格，在编辑栏中输入公式：=IF（AND（B2>60，C2>60，D2>60），"合格"，"不合格"），按回车键，即可判断B2、C2、D2单元格中的值是否都达标，如果都达标，利用IF函数显示“合格”；如果有一项未达标，利用IF函数显示“不合格”。
❷ 选中E2单元格，向下拖动进行公式填充，可实现快速判断其他人员考评结果，如图2-3所示。图2-3例15 对员工的考核成绩进行综合评定
OR函数一般用来检验一组数据是否都不满足条件，只要有一个数据满足条件，结果为“真”。如本例中使用OR函数来判断一组考评数据中是否有一个大于“80”，如果有，该员工就具备参与培训的资格，否则取消资格。
❶ 选中E2单元格，在编辑栏中输入公式：=IF（OR（B2>80，C2>80，D2>80），"参与培训"，"取消资格"），按回车键，即可判断B2、C2、D2单元格中的值是否有一个大于80。如果有，利用IF函数显示“参与培训”；如果没有，利用IF函数显示“取消资格”。
❷ 选中E2单元格，向下拖动进行公式填充，可实现快速判断其他人员考评结果，如图2-4所示。图2-4例16 快速识别产品类别
本例中要判断采购的产品是否为电脑设备，如果是，分类为“电脑设备”；反之，分类为“其他办公用品”，此时可以将IF函数与OR函数配合使用。
❶ 选中C2单元格，在编辑栏中输入公式：=IF（OR（B2="硬盘"，B2="内存"，B2="主板"），"电脑设备"，"其他办公用品"），按回车键即可判断采购产品的类别，如果满足OR函数中指定的任意一个名称就返回“电脑设备”；否则显示为“其他办公用品”。
❷ 选中C2单元格，向下拖动进行公式填充，可实现快速判断其他采购产品的产品类别，如图2-5所示。图2-5例17 根据产品的名称与颜色进行一次性调价
下面的表格中想在D列中返回满足以下条件的结果：
·如果产品类别为“洗衣机”，且颜色为“白色”，其调整后价格为原来的单价加50元。
·如果产品类别为“洗衣机”，且颜色为“彩色”，其调整后价格为原来的单价加200元。
·其他产品类别价格不变。
❶ 选中D2单元格，输入公式：=IF（NOT（LEFT（A2，3）="洗衣机"），"原价"，IF（AND（LEFT（A2，3）="洗衣机"，NOT（B2="白色"）），C2+200，C2+50））。
试读结束[说明：试读内容隐藏了图片]