数据分析技术第3章.pptx

《数据分析技术第3章.pptx》由会员分享，可在线阅读，更多相关《数据分析技术第3章.pptx（99页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第三章 Excel数据分析常用函数北京西普阳光教育科技股份有限公司 2018年12月目 录 Contents01公式与函数的基础02统计计算类函数03字符提取类函数04逻辑运算类函数0506关联匹配类函数 时间序列函数公式与函数的基础3.13.1公式与函数的基础公式与函数作为Excel的重要组成部分，有着很强的计算功能，为用户分析与处理工作表中的数据提供了很大方便。公式是在工作表中对数据进行计算的式子，它可以对工作表数值进行加、减、乘、除等运算。对于一些特殊运算，无法直接利用公式来实现，可以使用Excel内置的函数来求解。01020304运算符+（加）、-（减）、*（乘）、/（除）、%（百分比

2、）、（指数）算术运算符=、=、=比较运算符&（连接）字符运算符：（冒号）、，（逗号）、空格引用运算符3.1.1 Excel公式要创建一个公式，首先需要选定一个单元格，输入一个等于号“=”，然后在其后输入公式的内容，按Enter键就可以按公式计算得出结果1.1.单元格引用单元格引用单元格引用就是标识工作表上的单元格或单元格区域，指明公式中所使用的数据的位置。在Excel中，可以引用同一工作表不同部分的数据，同一工作簿不同工作表的数据，甚至不同工作簿的单元格数据:（冒号）区域运算符,（逗号）联合运算符空格交叉运算符u3个引用运算符u单元格或单元格区域引用一般式工作表名！单元格引用或 工作簿名工作表

3、名！单元格引用2.2.地址引用地址引用若在一个公式中用到一个或多个单元格地址，则认为该公式引用了单元格地址相对地址随公式复制的单元格位置变化而变化的单元格地址绝对地址当复制单元格的公式到目标单元格时，其地址不能改变混合地址行号或列号前面带有“$”2.2.地址引用地址引用例：作为销售部门的统计员，小马每个月都要统计出产品销售的情况。小马制作销售报表时，需要计算销售额和利润。利用单元格的引用功能，小马每次都能很快地制作出报表。具体步骤如下。在工作表中输入基本数据，如图 3-1所示。图 31输入基本数据2.2.地址引用地址引用在“Exce项”对话框中，单击左侧列表中的“高级”选项，在“此工作表的显示

4、选项”栏中，选中“在单元格中显示公式而非其计算结果”复选框，可以使单元格显示公式，而不是计算的结果，如图 3-2所示。图 32 选中“在单元格中显示公式而非其计算结果”2.2.地址引用地址引用在D3单元格中输入公式“=B3*C3”，拖动D3单元格的填充柄至D7单元格，填充公式。公式中的单价单元格、销售数量单元格的地址随着销售额单元格位置的改变而改变。在E3单元格中输入公式“=D3*$B$1”，拖动E3单元格的填充柄至E7单元格，填充公式。公式中的销售额单元格的地址随着利润额单元格的位置改变而改变，而利润率单元格的地址不变，如图 3-3所示。图 3-3 小马制作的报表3.1.2 3.1.2 名称

5、的定义与运用名称的定义与运用为单元格指定一个名称，是实现绝对引用的方法之一。使用名称可以使公式更加容易理解和维护，可为单元格或单元区域、函数、常量、表格等定义名称。1名称的语法规则创建和编辑名称时需要注意以下语法规则：（1）有效字符：名称的第一字符必须是字母、下划线或反斜杠（）。名称中的其余字符可以是字母、数字、句点和下划线。（2）名称不能与单元格引用地址相同，如Z$100或R1C1。（3）不能使用空格：在名称中不允许使用空格，请使用下划线（_）和句点（.）作为单词分隔符。（4）名称长度限制：名称最多可以包含255个字符。（5）不区分大小写：名称可以包含大写字母和小写字母，但Excel在名称中

6、不区分大写字母和小写字母。（6）唯一性：名称在其适用范围之内必须具备唯一性，不可重复。3.1.2 3.1.2 名称的定义与运用名称的定义与运用2名称的适用范围名称的适用范围是指能够识别名称的位置。如果定义了一个名称（如Budget_FY08）且其适应范围为Sheet1，则该名称只能在Sheet1中被识别，如要在其他同一工作簿的工作表中使用该名称，必须加上工作表名称，如Sheet1!Budget_FY08。如果定义了一个名称（如Sales_01）且适用范围是工作簿（即该Excel文件），则该名称对于该工作秒表中的所有工作表都是可识别的，但对于其他工作簿是不可识别的。3.1.2 3.1.2 名称的

7、定义与运用名称的定义与运用3为单元格或单元格区域定义名称（1）快速定义名称选择要命名的单元格或单元格区域单击“编辑栏”最左边的“名称”框 在“名称”框中键入引用选定内容时要使用的名称。按Enter键确认。3.1.2 3.1.2 名称的定义与运用名称的定义与运用3为单元格或单元格区域定义名称（2）将现有行和列标签转换为名称选择要命名的区域，包括行或列标签。在“公式”选项卡的“定义的名称”组中，单击“根据所选内容创建”按钮。在弹出的“以选定区域创建名称”对话框中，通过选中“首行”、“左列”、“末行”、“右列”复选框来指定包含标签的位置，如图 3-4所示单击“确定”按钮，完成名称的创建。通过该方式创

8、建的名称仅引用相应标题下包含值的单元格，并且不包含现有行和列标题。图 34根据所选内容创建区域名称3.1.2 3.1.2 名称的定义与运用名称的定义与运用3为单元格或单元格区域定义名称（3）使用“新名称”对话框定义名称在“公式”选项卡的“定义的名称”组中，单击“定义名称”。在“新建名称”对话框的“名称”框中，输入要用于引用的名称。指定名称的适用范围：在“适用范围”下拉列表框中选择“工作簿”或工作簿中工作表的名称根据需要，在备注栏中输入对该名称的说明性批注，最多255个字符。在“引用位置”框中，执行下列操作之一：a)要引用一个单元格，则鼠标单击“引用位置”框，然后在工作表中重新选择需要引用的单元

9、格或单元格区域。b)要引用一个常量，则输入（等号），然后输入常量值。c)要引用公式，则输入（等号），然后输入公式。单击“确定”按钮，完成命名并返回工作表。3.1.2 3.1.2 名称的定义与运用名称的定义与运用使用“名称管理器”可以处理工作簿中所有已定义的名称和表名称。鼠标单击“公式”选项卡的“定义的名称”组中的“名称管理器”按钮，打开“名称管理器”对话框，如图 3-6所示。图 36“名称管理器”管理名称4使用“名称管理器”管理名称（1）更改名称如果更改某个已定义名称或表名称，则工作簿中该名称的所有实例也会随之更改。打开“名称管理器”，在该对话框中单击要更改的名称，然后单击“编辑”按钮，打开“

10、编辑名称”对话框，在该对话框中按照需要修改名称、引用位置、备注说明等，但适用范围不能更改，更改完成后单击“确定”按钮即可。（2）删除名称在“名称管理器”对话框中，选择要删除的名称，也可按住Shift并单击鼠标选择连续的几个名称或按住Ctrl并单击鼠标选择不连续的多个名称，单击“删除”按钮或按Delete键，再单击“确定”，确认删除。3.1.2 3.1.2 名称的定义与运用名称的定义与运用名称可直接用来快速选定已命名的区域，也可在公式中引用名称以实现精确引用。（1）通过“名称框”引用单击“名称框”右侧的黑色箭头，打开“名称”下拉列表，在其中显示所有已被命名的单元格名称，但不包括常量和公式的名称。

11、单击选择某一名称，该名称所引用的单元格或单元区域将公被选中，如果是在输入公式过程中，则该名称会出现在公式中。（2）在公式中引用单击要输入公式的单元格。在“公式”选项卡的“定义的名称”组中，单击“用于公式”按钮，打开名称下拉列表。3.1.2 3.1.2 名称的定义与运用名称的定义与运用5引用名称3.1.3 Excel3.1.3 Excel中的函数中的函数1行列数据自动求和在Excel中经常进行的工作是合计行和列中的数据，Excel为用户提供了一条很方便的途径，即利用“自动求和”按钮。利用自动求和按钮求和的方法是：选定求和区域并在下方或右方留有一空行或空列，然后在“开始”选项卡的“编辑”组单击“自

12、动求和”按钮，在下拉菜单中选择“求和”命令，便会在空行或空列上求出对应列或行的合计值，最后按Enter键。在Excel中函数是预定义的内置公式，它使用被称为参数的特定数值，按照语法所列的特定顺序进行计算。Excel提供了大量的函数，可以实现数值统计、逻辑判断、财务计算、工程分析、数值计算等功能。3.1.3 Excel3.1.3 Excel中的函数中的函数2粘贴函数首先选定要生成函数的单元格，然后单击编辑栏左侧的“插入函数”按钮，打开“插入函数”对话框，如图 3-7所示。选择函数（如COUNT）后，单击“确定”按钮，打开“函数参数”对话框，如图 3-8所示，在Number1、Number2文本框

13、中输入单元格区域或单击“拾取”按钮 选择单元格区域（再次单击“拾取”按钮返回“函数参数”对话框），最后单击“确定”按钮即可。图 3-7“插入函数”对话框图 3-8“函数参数”对话框3.1.4 3.1.4 公式与函数运用中的常见问题公式与函数运用中的常见问题1常见错误信息与处理方法错误错误常见原因常见原因处理方法处理方法#DIV/0!在公式中有除数为零，或者有除数为空白的单元格(Excel把空白单元格也当作0)。把除数改为非零的数值，或者用IF函数进行控制。#N/A在公式使用查找功能的函数(VLOOKUP、HLOOKUP、LOOKUP等)时，找不到匹配的值。检查被查找的值，使之的确存在于查找的数

14、据表中的第一列。#NAME?在公式中使用了Excel无法识别的文本，例如函数的名称拼写错误，使用了没有被定义的区域或单元格名称，引用文本时没有加引号等。根据具体的公式，逐步分析出现该错误的可能，并加以改正。#NUM!当公式需要数字型参数时，却给了它一个非数字型参数；给了公式一个无效的参数；公式返回的值太大或者太小。根据公式的具体情况，逐一分析可能的原因并修正。#VALUE文本类型的数据参与了数值运算，函数参数的数值类型不正确；函数的参数本应该是单一值，却提供了一个区域作为参数；输入一个数组公式时，忘记按CtrlShiftEnter键。更正相关的数据类型或参数类型；提供正确的参数；输入数组公式时

15、，记得使用CtrlShiftEnter键确定。#REF!公式中使用了无效的单元格引用。通常如下这些操作会导致公式引用无效的单元格：删除了被公式引用的单元格；把公式复制到含有引用自身的单元格中。避免导致引用无效的操作，如果已经出现错误，先撤销，然后用正确的方法操作。#NULL!使用了不正确的区域运算符或引用的单元格区域的交集为空。改正区域运算符使之正确；更改引用使之相交。3.1.4 3.1.4 公式与函数运用中的常见问题公式与函数运用中的常见问题2追踪单元格（1）追踪引用单元格选择包含需要找到其引用单元格的公式的单元格，鼠标单击“公式”选项卡的“公式审核”组中的如图3-9所示，蓝色箭头显示无错误

16、的单元格，红色箭头显示导致错误的单元格。如果所选单元格引用了另一个工作表或工作簿上的单元格，则会显示一个从工作表图标指向所选单元格的黑色箭头。图 3-9 追踪引用单元格3.1.4 3.1.4 公式与函数运用中的常见问题公式与函数运用中的常见问题2追踪单元格（1）追踪引用单元格若要移去引用单元格追踪箭头，则单击如图3-10所示的“删除箭头”按钮旁边的箭头，在弹出的下拉列表中执行“删除引用单元格追踪箭头”命令。图 3-10“删除箭头”下拉列表（2）追踪从属单元格选择要对其标识从属单元格的单元格，鼠标单击“公式”选项卡的“公式审核”组中的，可追踪显示引用了该单元格的单元格。同样，蓝色箭头显示无错误的

17、单元格，红色单元格显示导致错误的单元格。统计计算类函数3.23.2.1 统计类函数平均值函数1算术平均值：返回参数的平均值AVERAGE(number1,number2,.)2几何平均值：返回正数数组或数值区域的几何平均数GEOMEAN(number1,number2,.)3调和平均值：返回一组正数的调和平均数HARMEAN(number1,number2,.)1平均值函数平均值是表示一组数据集中趋势的量数，是反映数据集中趋势的一项指标3.2.1 统计类函数1平均值函数【例3-1】某企业2018年上半年每个月的成本如图3-11所示，现要求计算出该企业2018年上半年的平均值、几何平均值和调和平

18、均值，操作步骤如下。图 3-11 某企业2018年上半年每个月成本明细3.2.1 统计类函数1平均值函数光标定位于D4单元格，鼠标单击“插入函数”按钮，弹出如图3-12所示的“插入函数”对话框，选择类别“统计”“GEOMEAN”函数，单击“确定”按钮，弹出如图3-13所示的“函数参数”对话框。光标定位于number1右侧的输入框中，按住鼠标左键选取EXCEL表中的B2:B7区域，再单击“确定”按钮，即计算得出“上半年成本几何平均值”。同理，分别将光标定位于D2单元格和D6单元格计算“上半年成本平均值”和“上半年成本调和平均值”图 3-12 “插入函数”对话框图 3-13 “函数参数”对话框3.

19、2.1 统计类函数2计数函数在数据分析过程中，经常需要统计选定区域内数值型单元格的数目、空白单元格的数目、非空单元格的数目以及满足某条件的单元格的数目03020104COUNT功能：返回包含数字以及包含参数列表中的数字的单元格的个数COUNT(value1,value2,.)COUNTA功能：计算范围中不为空的单元格的个数COUNTA(value1,value2,.)COUNTBLANK功能:用于计算单元格区域中的空单元格的个数COUNTBLANK（range）COUNTIF功能：计算区域中满足给定条件的单元格的个数COUNTIF(range,criteria)3.2.1 统计类函数2计数函数

20、【例3-2】请根据如图3-14所示的学生成绩明细，统计学生人数、语文科目参加考试人数及数学科目参加考试人数。操作步骤如下。图 3-14 学生成绩明细数据表3.2.1 统计类函数2计数函数光标定位于F4单元格，鼠标单击“插入函数”按钮，弹出如图315所示的“插入函数”对话框，选择类别“统计”“COUNTA”函数，单击“确定”按钮，弹出如图316所示的“函数参数”对话框。光标定位于value1右侧的输入框中，鼠标选取A2:A11单元格区域，单击“确定”按钮即可。光标定位于F5单元格，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“统计”“COUNT”函数，单击“确定”按钮，弹出“函数参

21、数”对话框，光标定位于value1右侧的输入框中，鼠标选取B2:B11单元格区域，单击“确定”按钮即可。图 3-15 “插入函数”对话框图 3-16 “函数参数”对话框3.2.1 统计类函数2计数函数【例3-3】如图3-17所示是会员数据，一般数据库中的数据均有一个主键，即不允许重复的键，计算主键的重复次数如果大于1即为重复。操作步骤如下。图 3-17 会员数据明细3.2.1 统计类函数2计数函数在A列与B列中插入一列，名称为“重复次数”。将光标定位于B2单元格，鼠标单击“插入函数”按钮，弹出如图3-18所示的“插入函数”对话框，选择类别“统计”“COUNTIF”函数，单击“确定”按钮，弹出如

22、图3-19示的“函数参数”对话框。光标定位于range右侧的输入框中，鼠标选取A2:A11单元格区域，由于此区域在公式复制过程中不变，故行号后添加$符号，将相对地址改为混合地址。将光标定位于Criteria右侧的输入框中，选取A2单元格，单击“确定”按钮，B2单元格中的数值显示3。单击B2单元格，将鼠标移动到B2单元格右下角，此时光标变为一个+，双击+，后续的单元格自动按此公式进行计算。图 3-18 “插入函数”对话框图 3-19 “函数参数”对话框3.2.1 统计类函数2计数函数【例3-4】如图3-20所示是某单位2018年1月的销售明细数据，现要求统计教学部男员工的销售记录个数。操作步骤如

23、下。图 3-20 某单位2018年1月销售明细数据3.2.1 统计类函数2计数函数光标定位于工作表的空白单元格，鼠标单击“插入函数”按钮，弹出如图3-21所示的“插入函数”对话框，选择类别“统计”“COUNTIFS”函数，单击“确定”按钮，弹出如图3-22所示的“函数参数”对话框。光标定位于criteria_range1右侧的输入框中，鼠标选取第一个条件区域C3:C14，光标定位于criteria1右侧的输入框中，输入第一个条件“教学部”，光标定位于criteria_range2右侧的输入框中，鼠标选取第二个条件区域B3:B14，光标定位于criteria2，输入第二个条件“男”，单击“确定”

24、按钮即可。图 3-21 “插入函数”对话框图 3-22 “函数参数”对话框3.2.1 统计类函数3其他统计类函数其他统计函数0102030405最大值函数最小值函数Frequency函数Rank函数MEDIAN函数MAX(number1,number2,.)功能：返回一组值中的最大值MIN(number1,number2,.)功能：返回一组值中的最小值MEDIAN(number1,number2,.)功能：返回给定数值集合的中值.中值是在一组数据中居于中间的数,即在这组数据中，有一半的数据比它大,有一半的数据比它小RANK(number,ref,order)功能：返回一个数字在数字列表中的排位

25、。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）Frequency(data_array,bins_array)功能：以一列垂直数组返回一组数据的频率分布3.2.1 统计类函数3其他统计类函数【例3-5】计算如图3-17所示会员明细数据表中，购买金额的最大值与最小值。操作步骤如下。图 3-17 会员数据明细3.2.1 统计类函数3其他统计类函数鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“统计”“MAX”函数，单击“确定”按钮，弹出“函数参数”对话框。光标定位于number1右侧的输入框中，鼠标选取G2:G11单元格区域，单击“确定”按钮

26、即可求出购买金额的最大值。【例3-6】计算如图 3-17所示会员明细数据表中，购买金额的中位数。操作步骤如下。鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“统计”“MEDIAN”函数，单击“确定”按钮，弹出“函数参数”对话框。光标定位于number1右侧的输入框中，鼠标选取G2:G11单元格区域，单击“确定”按钮即可求出购买金额的中位数。3.2.1 统计类函数3其他统计类函数【例3-7】计算如图3-23所示学生成绩明细表中每位同学成绩在年级中的排名。操作步骤如下。图 3-23 学生成绩明细表3.2.1 统计类函数3其他统计类函数光标定位于工作表的E2单元格，鼠标单击“插入函数”按

27、钮，弹出“插入函数”对话框，选择类别“统计”“RANK”函数，单击“确定”按钮，弹出图324“函数参数”对话框。光标定位于number右侧的输入框中，鼠标选取B2单元格。光标定位于Ref右侧的输入框中，按住Ctrl的同时，鼠标选取B2:B11单元格区域与D2:D11单元格区域，为保证复制公式时，此区域地址不变化，需要使用绝对地址，因此在行列号前加上$，并在区域外添加英文状态下的括号。光标定位于Order右侧的输入框中，输入0或省略，默认情况下为降序。鼠标单击“确定”按钮即可。图 3-24 RANK“函数参数”对话框3.2.1 统计类函数3其他统计类函数【例3-8】请分别统计图3-23所示学生成

28、绩明细表中学生成绩的分段人数，结果如图3-26所示。操作步骤如下。图 3-23 学生成绩明细表图 3-26 分段统计结果3.2.1 统计类函数3其他统计类函数鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“统计”“FREQUENCY”函数，单击“确定”按钮，弹出如图3-25所示“函数参数”对话框。光标定位于Data_array右侧的输入框中，按住Ctrl键的同时，鼠标选取B2:B11单元格区域和D2:D11单元格区域。为保证复制公式时，此区域地址不变化，需要使用绝对地址，因此在行列号前加上$，并在区域外添加英文状态下的括号。光标定位于Bins_array右侧的输入框中，鼠标选取C1

29、4:C17单元格区域，接收区间的结束单元格C17在复制公式时不能变化，需要使用混合地址，因此在C17行号前加上$。鼠标单击“确定”按钮即可。图 3-25 Frequency“函数参数”对话框3.2.2 数学计算类函数1求和函数在数据分析过程中，有时需要对数值型数据进行求和或根据指定条件求和等03020104SUMSUM(number1,number2,.)功能：返回某一单元格区域中所有数字之和。参数说明：Number1,number2,.为 1 到 30 个需要求和的参数,可以是一个具体的数值、一个单元格或单元格区域SUMIFSSUMIFS(sum_range,criteria_range1,

30、criteria1,criteria_range2,criteria2,.)功能：用于计算其满足多个条件的全部参数的总量。SUMIFSUMIF(range,criteria,sum_range)功能：对范围中符合指定条件的数值求和。SUMPRODUCTSUMPRODUCT(array1,array2,array3,.)功能：在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和3.2.2 数学计算类函数1求和函数【例3-9】要求计算如图3-27所示的信息表中指定部门的总成绩。操作步骤如下。图 3-27 成绩信息表3.2.1 统计类函数1求和函数光标定位于工作表的B14单元格，鼠标单击“插入

31、函数”按钮，弹出如图3-28所示的“插入函数”对话框，选择类别“数学与三角函数”“SUMIF”函数，单击“确定”按钮，弹出如图3-29所示的“函数参数”对话框。光标定位于range右侧的输入框中，鼠标选取条件区域A2:A10，为确保向下复制公式时，条件区域永远定位于A2:A10，因此需要将该地址改为混合地址A$2:A$10，光标定位于criteria右侧的输入框中，选取条件单元格A14，光标定位于Sum_range右侧的输入框中，鼠标选取求各区域B3:B14，同样，需要将该区域地址修改为混合地址B$3:B$14，单击“确定”按钮即可。图 3-28 “插入函数”对话框图 3-29 SUMIF“函

32、数参数”对话框3.2.2 数学计算类函数1求和函数【例3-10】要求计算如图3-30所示的信息表中各部门男女员工的销售总额。操作步骤如下。图 3-30 各部门员工销售明细表3.2.1 统计类函数1求和函数光标定位于工作表的D18单元格，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“数学与三角函数”“SUMIFS”函数，单击“确定”按钮，弹出“函数参数”对话框。光标定位于Sum_range右侧的输入框中，鼠标选取计算区域D3:D14，为确保向下复制公式时，条件区域永远定位于D3:D14，因此需要将该地址改为绝对地址$D$3:$D$14。光标定位于criteria_Range1右侧的

33、输入框中，鼠标选取计算区域C3:C14，为确保向下复制公式时，条件区域永远定位于C3:C14，因此需要将该地址改为绝对地址$C$3:$C$14，光标定位于criteria1右侧的输入框中，鼠标选取条件单元格B18。光标定位于criteria_Range2右侧的输入框中，鼠标选取计算区域B3:B14，为确保向下复制公式时，条件区域永远定位于B3:B14，因此需要将该地址改为绝对地址$B$3:$B$14，光标定位于criteria2右侧的输入框中，鼠标选取条件单元格C18，单击“确定”按钮即可。3.2.2 数学计算类函数1求和函数【例3-11】要求使用SUMPRODUCT计算如图330所示的信息表

34、中各部门男女员工的人数及销售总额。操作步骤如下。图 3-30 各部门员工销售明细表3.2.1 统计类函数1求和函数光标定位于工作表的D18单元格，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“数学与三角函数”“SUMPRODUCT”函数，单击“确定”按钮，弹出如图3-32所示的“函数参数”对话框。光标定位于array1右侧的输入框中，输入公式($C$3:$C$14=D18)*($B$3:$B$14=C18)光标定位于array2右侧的输入框中，鼠标选取计算区域$D$3:$D$14，单击“确定”按钮即可计算各部门男女员工的销售总额计算各部门男女员工的人数，只需要在array1右侧的

35、输入框中输入公式($C$3:$C$14=D18)*($B$3:$B$14=C18)，单击“确定”按钮即可。图 3-32 SUMPRODUCT“函数参数”对话框3.2.2 数学计算类函数2其他数学函数（1）INT(number)功能：将number数值向下舍入为最接近的整数【例3-12】计算职工工资发放备钞张数，如图3-33所示。操作步骤如下。图 3-33 职工工资备钞张数3.2.1 统计类函数2其他数学函数光标定位于B2单元格中，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“数学与三角函数”“INT”函数，单击“确定”按钮，弹出如图3-34所示的“函数参数”对话框。光标定位于nu

36、mber1右侧的输入框中，输入公式A2/B$1，单击“确定”按钮即可计算出100元的备钞张数。计算50元的备钞张数，操作与之累同，公式改为(A2-B2*B$1)/C$1即可。图 3-34 INT“函数参数”对话框3.2.2 数学计算类函数2其他数学函数（2）MOD函数MOD(number,divisor)功能：返回两数相除的余数，结果的正负号与除数相同【例3-13】为如图3-30所示的员工销售明细表的奇偶行设置不同的底纹颜色。操作步骤如下。图 3-30 各部门员工销售明细表3.2.1 统计类函数2其他数学函数选取A3:D14单元格区域，鼠标单击“条件格式”“新建规则”，弹出如图3-35所示的“

37、新建格式规则”对话框。选择规则类型“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”下面的输入框中输入公式“=mod(row($A3),2)”，单击“格式”设置底纹颜色，单击“确定”按钮，则为行号为奇数行的单元格设置了相应的底纹。图 3-35 “新建格式规则”对话框文本类函数3.33.3.1 字符串截取类函数字符串截取0102030405LEN函数LENB函数MID函数RIGHT函数LEFT函数LEN(text)功能：返回文本字符串中的字符数LEFT(text,num_chars)功能：LEFT 基于所指定的字符数返回文本字符串中的第一个或前几个字符RIGHT(text,num

38、_chars)功能：根据所指定的字符数返回文本字符串中最后一个或多个字符）MID(text,start_num,num_chars)功能：返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定LENB(text)功能：返回文本字符串中用于代表字符的字节数3.3.1 字符串截取类函数【例3-14】从如图3-36所示信息表的A列相应单元格中分别截取数字和单位。操作步骤如下。图 3-36 信息表3.3.1 字符串截取类函数1截取数字（1）光标定位于B2单元格，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“文本”“LEFT”函数，单击“确定”按钮，弹出如图3-37所示的“函数参

39、数”对话框。（2）光标定位于Text右侧的输入框中，鼠标选取A2单元格。（3）光标定位于Num_chars右侧的输入框中，输入公式LEN(A2)*2-LENB(A2)，单击“确定”按钮即可。图 3-37 LEFT“函数参数”对话框3.3.1 字符串截取类函数2截取单位（1）光标定位于B2单元格，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“文本”“RIGHT”函数，单击“确定”按钮，弹出如图3-38所示的“函数参数”对话框。（2）光标定位于Text右侧的输入框中，鼠标选取A2单元格。（3）光标定位于Num_chars右侧的输入框中，输入公式LENB(A2)-LEN(A2)，单击“

40、确定”按钮即可。图 3-38 RIGHT“函数参数”对话框3.3.1 字符串截取类函数【例3-15】从如图3-39所示员工信息表的身份证号中提取员工的生日，以“*年*月*日”的形式存放。操作步骤如下。图 3-39 员工信息表3.3.1 字符串截取类函数1光标定位于C2单元格，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“文本”“MID”函数，单击“确定”按钮，弹出如图3-40所示的“函数参数”对话框。2光标定位于Text右侧的输入框中，鼠标选取B2单元格。3光标定位于Start_num右侧的输入框中，输入身份证号中出生日期年份起始位置7。4光标定位于Num_chars右侧的输入框

41、中，输入年份的长度4，单击“确定”按钮，即可截取身份证号中出生日期的年份。5将截取的年份、月份、日用字符串连接符“&”连接，即完整的公式为：=mid(b2,7,4)&“年”&mid(b2,11,2)&“月”&mid(b2,13,2)&“日”。图 3-40 MID“函数参数”对话框3.3.2字符串查找替换函数字符串查找替换1FIND(find_text,within_text,start_num)功能：FIND 用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并从 within_text 的首字符开始返回 find_text 的起始位置编号。2REPLACE

42、(old_text,start_num,num_chars,new_text)功能：使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。3SUBSTITUTE(text,old_text,new_text,instance_num)功能：在文本字符串中用 new_text 替代 old_text。如果需要在某一文本字符串中替换指定的文本3.3.2字符串查找替换函数【例3-16】请从如图3-41所示的员工联系信息表的员工联系地址信息列中截取“姓名”、“邮政编码”和“联系地址”。操作步骤如下。图 3-41 员工联系信息3.3.1 字符串截取类函数（1）光标定位于B2单元格，鼠标单击

43、“插入函数”按钮，弹出“插入函数”对话框，选择类别“文本”“FIND”函数，单击“确定”按钮，弹出如图3-42所示的“函数参数”对话框。（2）光标定位于find_text右侧的输入框中，输入“|”。由于姓名、邮政编码与联系地址之间均用“|”分隔，因此需要查找“|”在“员工联系地址信息”中的位置。（3）光标定位于Start_num右侧的输入框中，输入“1”，若省略，则默认为“1”，单击“确定”按钮，即可求出“|”在字符串中的位置。（4）截取姓名，只需用公式“=LEFT(A2，FIND(“|”,A2,1)-1)”即可。（5）截取邮政编码，只需用公式“=MID(A2,FIND(|,A2,1)+1,6

44、)”即可。（6）截取联系地址，只需用公式“=RIGHT(A2,LEN(A2)-LEN(B2)-LEN(C2)-2)”即可图 3-42 FIND“函数参数”对话框3.3.2字符串查找替换函数3.3.2字符串查找替换函数【例3-17】请隐藏如图3-39所示员工信息表中身份证号中的出生日期，用“*”代替。操作步骤如下。图 3-39 员工信息表3.3.1 字符串截取类函数（1）鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“文本”“REPLACE”函数，单击“确定”按钮，弹出如图3-43所示的“函数参数”对话框。（2）光标定位于Old_text右侧的输入框中，鼠标选取B2单元格。（3）光标

45、定位于Start_num右侧的输入框中，输入7，确定开始替换字符串的位置。（4）光标定位于Num_chars右侧的输入框中，输入8，确定被替换字符串的个数。（5）光标定位于New_text右侧的输入框中，输入“*”，表示身份证号中8位表示出生日期的字符将用“*”代替，单击“确定”按钮即可。图 3-43 REPLACE“函数参数”对话框3.3.2字符串查找替换函数3.3.2字符串查找替换函数【例3-18】请使用SUBSTITUTE函数隐藏如图339所示员工信息表中身份证号中的出生日期，用“*”代替。操作步骤如下。图 3-39 员工信息表3.3.1 字符串截取类函数（1）鼠标单击“插入函数”按钮，

46、弹出“插入函数”对话框，选择类别“文本”“REPLACE”函数，单击“确定”按钮，弹出如图3-44所示的“函数参数”对话框。（2）光标定位于Text右侧的输入框中，鼠标选取B2单元格。（3）光标定位于Old_text右侧的输入框中，输入mid(B2,7,8)，确定需要被替换的字符串。（4）光标定位于New_text右侧的输入框中，输入“*”，表示身份证号中8位表示出生日期的字符将用“*”代替。（5）光标定位于Instance_num右侧的输入框中，输入1或省略，单击“确定”按钮即可。图 3-44 SUBSTITUTE“函数参数”对话框3.3.2字符串查找替换函数3.3.3文本转换类函数1TEX

47、T函数函数TEXT(value,format_text)功能：根据指定的数值格式将数字转换为文本。【例3-19】根据如图345所示信息表中的收入和支出数据设置盈亏平衡判断。收入大于支出设置为盈利，收入小于支出设置为亏本，收入等于支出设置为平衡，结果如图3-45所示。操作步骤如下。图 3-45 收入支出信息表3.3.1 字符串截取类函数（1）光标定位于C2单元格，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“文本”“TEXT”函数，单击“确定”按钮，弹出如图3-46所示的“函数参数”对话框。（2）光标定位于value右侧的输入框，输入A2-B2，确定盈亏的数值。（3）光标定位于Fo

48、rmat_text右侧的输入框中，输入“盈利0.00万；亏损0.00万；平衡；”，单击“确定”按钮，即可完成“收益情况”判断。图 3-46 TEXT“函数参数”对话框3.3.3文本转换类函数逻辑运算类函数3.43.4.1IF类函数IF类函数1IF(logical_test,value_if_true,value_if_false)功能：执行真假值判断，根据逻辑计算的真假值，返回不同结果2IFNA(value,value_if_na)功能：如果表达式解析为#N/A，则返回value_if_na指定的值，否则返回表达式的结果3IFERROR(value,value_if_error)功能：如果表达

49、式是一个错误，则返回value_if_error的值，否则返回表达式自身的值3.4.1IF类函数【例3-20】如图3-47所示为不同产品的去年和今年的销售量数据，现要求计算各个产品的同比增长率。若无“上年销售量”，则是今年新增产品，若无“本年销售量”，则是今年停产产品。操作步骤如下。图 3-47 产品去年和今年销售量数据表3.3.1 字符串截取类函数（1）光标定位于工作表的D2单元格，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“逻辑”“IF”函数，单击“确定”按钮，弹出如图3-48所示的“函数参数”对话框。（2）光标定位于“logical_test”右侧的输入框，输入公式B2”

50、。（3）光标定位于“Value_if_true”右侧的输入框，输入IF的嵌套公式IF(C2”,(C2-B2)/B2,”已经停产”)。（4）光标定位于“Value_if_false”右侧的输入框，输入“新增产品”，鼠标单击“确定”按钮即可。图 3-48 IF“函数参数”对话框3.4.1IF类函数3.3.1 字符串截取类函数3.4.2IS类函数3检查一个值是否为错误ISERROR(value)4检查一个值是否是逻辑值ISLOGICAL(value)1检查是否引用了空单元格ISBLANK(value)6检查一个值是否不是文本ISNONTEXT(value)2检查一个值是否为#N/A以外的错误ISER