《物流数据分析》课件-任务2.3 熟练编辑公式

任务1.5熟练编辑公式【任务目的】1.掌握复杂公式的编辑技巧；2.掌握运算符的使用；3.掌握绝对引用、相对引用和混合引用的概念和用法。【工作任务】用编辑公式的方法快速设计一个如图所示的九九乘法表。【知识链接】1.Excel中的运算符类型运算符作用举例（假设A1=2,A3=4）备注算符运算符+加号=A1+A3结果为：6-减号=A1-A3结果为：-2*乘号=A1*A3结果为：8/除号=A1/A3结果为：0.5^乘幂=A1^A3结果为：16文本运算符&将两个字符串连接为一个新字符串=“AB”&“CD”结果为：ABCD类型运算符作用举例（假设A1=2,A3=4）备注比较运算符=等于=A1=A3结果为：False<>

不等于=A1<>A3结果为：True>

大于=A1>A3结果为：False<

小于=A1<A3结果为：True>=大于等于=A1>=A3结果为：False<=小于等于=A1<=A3结果为：True类型运算符作用举例（假设A1=2,A3=4）备注引用运算符:区域运算符=SUM(B3:C7)冒号两端两个单元格形成的连续矩形区域求和,联合运算符=SUM(B3,C7)逗号前后两个单元格求和(空格)交叉运算符=SUM(A2:B6B2:C6)空格前后两个单元格区域共同的单元格求和2.单元格地址的引用方式Excel公式中单元格地址的引用包括相对引用、绝对引用和混合引用三种，三种方式切换的快捷键为F4。相对引用：形如B2，在引用公式的时候，单元格的行号和列标都会相应发生改变。绝对引用：形如$B$2，在引用公式的时候，单元格的行号和列标都固定不变。混合引用：形如$B1、B$1，在引用公式时，只有行号或列标的一个参数发生改变。【实施步骤】1.设计九九乘法表框架2.输入第一个表达式3.使用混合引用4.引用公式5.去除重复的一半1.设计九九乘法表框架在Excel中编辑如图所示的表格。2.输入第一个表达式单击C3单元格，输入公式“=B3&"×"&C2&"="&B3*C2”，回车后即可产生表达式“1×1=1”。思考与点拨：公式“=B3&"×"&C2&"="&B3*C2”该如何理解？B3为第一个乘数；"×"为表示乘法运算的字符串，会原样展现到公式运行结果中，并未实际进行乘法运算；C2为第二个乘数；B3*C2表示两个乘数相乘；“&”为连接运算符，其作用是多个字符串连接为一个字符串；建议分段输入本公式并回车查看效果有助于理解。3.使用混合引用分别向下和向右拖动C3单元格右下角的填充句柄，发现公式不能直接正确引用到其它单元格，还需要修改公式。双击C3单元格，对公式进行修改，因为向右拖动填充句柄时第一个乘数始终应该引用B列的数字，而向下拖动填充句柄时第二个乘数始终应该引用第2行的数字，所以需要将第B列和第2行锁定，添加绝对引用符号“$”，不让其发生变化，将公式修改为“=$B3&"×"&C$2&"="&$B3*C$2”，再次回车确认。4.引用公式拖动C3单元格右下角的填充句柄至K3，产生第一行表达式，选中C3：K3单元格区域，拖动单元格区域的填充句柄至K11，产生所有表达式，如图所示。5.去除重复的一半双击C3单元格，在原有公式基础上加入IF函数进行判断，将其公式修改为“IF($B3<C$2,"",$B3&"×"&C$2&"="&$B3*C$2)”，回车确认。拖动C3单元格右下角的填充句柄至K3，产生第一行表达式，选中C3：K3单元格区域，拖动单元格区域的填充句柄至K11，产生所有表达式。此时可以发现，原来的重复表达式被去除，效果如图所示。思考与点拨：公式“=IF($B3<C$2,"",$B3&"×"&C$2&"="&$B3*C$2)”公式如何理解？B3表示第一个乘数，C2表示第二个乘数，此公式首先判断第一个乘数是否小于第二个乘数，如果成立则返回第二个参数（“”表示空值），否则返回第三个参数所示的算式。【任务小结】手动编辑公式能够灵活完成各种计算要求，使用公式就必须熟练掌握运算符的使用，同时要注意多个运算符同时出现时的运算优先级问题，可以通过括号的使用来改变运算优先级。公式引用时需要注意Excel中的三种引用方式，只有正确使用引用方式才能快速实现公式的引用和计算。对于较为复杂的公式，不要一次性写完，最好是分段书写，每一段执行无误后再输入下一段，这样方便公式调试。【实践训练一】请在Excel中制作如图所示的表格。已知向银行存入本金100,现要求通过编辑公式快速计算出在不同年存款利率情况下，每年可以从银行取出的金额，按复利计算。复利终值的计算公式为其中：F为复利终值，P为本金，i为年利率，n为年数。【实践训练二】请制作如图所示表格，并通过使用函数和编辑公式计算出每个员工的出生日期、年龄和性别。其中出生日期要求年月日之间用“/”分隔，性别用“男”或“女”表示，年龄计算到当前日期的周岁。思考与点拨：1.我国身份证号码的编码结构是怎样的？我国公民的18位身份证号码中，1~6位表示籍贯、7~14位表示出生年月日，年用四位数表示，月和日都用两位数字表示，15~17位是顺序码，其中第17位奇数表示男性，偶数表示女性，第18位是校验位。2.身份证号码分析的思路是怎样的？综合使用连接运算符“&”、文本函数MID、求余函数MOD、条件判断函数IF、日期差值函数DATEDIF、当前日期函数TODAY等可完成身份证号码分析。3.本题目的实际意义在哪里？身份