《物流數(shù)據(jù)分析》課件-任務(wù)2.3 熟練編輯公式

任務(wù)1.5熟練編輯公式【任務(wù)目的】1.掌握復(fù)雜公式的編輯技巧；2.掌握運(yùn)算符的使用；3.掌握絕對引用、相對引用和混合引用的概念和用法。【工作任務(wù)】用編輯公式的方法快速設(shè)計(jì)一個(gè)如圖所示的九九乘法表。【知識鏈接】1.Excel中的運(yùn)算符類型運(yùn)算符作用舉例（假設(shè)A1=2,A3=4）備注算符運(yùn)算符+加號=A1+A3結(jié)果為：6-減號=A1-A3結(jié)果為：-2*乘號=A1*A3結(jié)果為：8/除號=A1/A3結(jié)果為：0.5^乘冪=A1^A3結(jié)果為：16文本運(yùn)算符&將兩個(gè)字符串連接為一個(gè)新字符串=“AB”&“CD”結(jié)果為：ABCD類型運(yùn)算符作用舉例（假設(shè)A1=2,A3=4）備注比較運(yùn)算符=等于=A1=A3結(jié)果為：False<>

不等于=A1<>A3結(jié)果為：True>

大于=A1>A3結(jié)果為：False<

小于=A1<A3結(jié)果為：True>=大于等于=A1>=A3結(jié)果為：False<=小于等于=A1<=A3結(jié)果為：True類型運(yùn)算符作用舉例（假設(shè)A1=2,A3=4）備注引用運(yùn)算符:區(qū)域運(yùn)算符=SUM(B3:C7)冒號兩端兩個(gè)單元格形成的連續(xù)矩形區(qū)域求和,聯(lián)合運(yùn)算符=SUM(B3,C7)逗號前后兩個(gè)單元格求和(空格)交叉運(yùn)算符=SUM(A2:B6B2:C6)空格前后兩個(gè)單元格區(qū)域共同的單元格求和2.單元格地址的引用方式Excel公式中單元格地址的引用包括相對引用、絕對引用和混合引用三種，三種方式切換的快捷鍵為F4。相對引用：形如B2，在引用公式的時(shí)候，單元格的行號和列標(biāo)都會相應(yīng)發(fā)生改變。絕對引用：形如$B$2，在引用公式的時(shí)候，單元格的行號和列標(biāo)都固定不變?；旌弦茫盒稳?B1、B$1，在引用公式時(shí)，只有行號或列標(biāo)的一個(gè)參數(shù)發(fā)生改變?！緦?shí)施步驟】1.設(shè)計(jì)九九乘法表框架2.輸入第一個(gè)表達(dá)式3.使用混合引用4.引用公式5.去除重復(fù)的一半1.設(shè)計(jì)九九乘法表框架在Excel中編輯如圖所示的表格。2.輸入第一個(gè)表達(dá)式單擊C3單元格，輸入公式“=B3&"×"&C2&"="&B3*C2”，回車后即可產(chǎn)生表達(dá)式“1×1=1”。思考與點(diǎn)撥：公式“=B3&"×"&C2&"="&B3*C2”該如何理解？B3為第一個(gè)乘數(shù)；"×"為表示乘法運(yùn)算的字符串，會原樣展現(xiàn)到公式運(yùn)行結(jié)果中，并未實(shí)際進(jìn)行乘法運(yùn)算；C2為第二個(gè)乘數(shù)；B3*C2表示兩個(gè)乘數(shù)相乘；“&”為連接運(yùn)算符，其作用是多個(gè)字符串連接為一個(gè)字符串；建議分段輸入本公式并回車查看效果有助于理解。3.使用混合引用分別向下和向右拖動C3單元格右下角的填充句柄，發(fā)現(xiàn)公式不能直接正確引用到其它單元格，還需要修改公式。雙擊C3單元格，對公式進(jìn)行修改，因?yàn)橄蛴彝蟿犹畛渚浔鷷r(shí)第一個(gè)乘數(shù)始終應(yīng)該引用B列的數(shù)字，而向下拖動填充句柄時(shí)第二個(gè)乘數(shù)始終應(yīng)該引用第2行的數(shù)字，所以需要將第B列和第2行鎖定，添加絕對引用符號“$”，不讓其發(fā)生變化，將公式修改為“=$B3&"×"&C$2&"="&$B3*C$2”，再次回車確認(rèn)。4.引用公式拖動C3單元格右下角的填充句柄至K3，產(chǎn)生第一行表達(dá)式，選中C3：K3單元格區(qū)域，拖動單元格區(qū)域的填充句柄至K11，產(chǎn)生所有表達(dá)式，如圖所示。5.去除重復(fù)的一半雙擊C3單元格，在原有公式基礎(chǔ)上加入IF函數(shù)進(jìn)行判斷，將其公式修改為“IF($B3<C$2,"",$B3&"×"&C$2&"="&$B3*C$2)”，回車確認(rèn)。拖動C3單元格右下角的填充句柄至K3，產(chǎn)生第一行表達(dá)式，選中C3：K3單元格區(qū)域，拖動單元格區(qū)域的填充句柄至K11，產(chǎn)生所有表達(dá)式。此時(shí)可以發(fā)現(xiàn)，原來的重復(fù)表達(dá)式被去除，效果如圖所示。思考與點(diǎn)撥：公式“=IF($B3<C$2,"",$B3&"×"&C$2&"="&$B3*C$2)”公式如何理解？B3表示第一個(gè)乘數(shù)，C2表示第二個(gè)乘數(shù)，此公式首先判斷第一個(gè)乘數(shù)是否小于第二個(gè)乘數(shù)，如果成立則返回第二個(gè)參數(shù)（“”表示空值），否則返回第三個(gè)參數(shù)所示的算式?！救蝿?wù)小結(jié)】手動編輯公式能夠靈活完成各種計(jì)算要求，使用公式就必須熟練掌握運(yùn)算符的使用，同時(shí)要注意多個(gè)運(yùn)算符同時(shí)出現(xiàn)時(shí)的運(yùn)算優(yōu)先級問題，可以通過括號的使用來改變運(yùn)算優(yōu)先級。公式引用時(shí)需要注意Excel中的三種引用方式，只有正確使用引用方式才能快速實(shí)現(xiàn)公式的引用和計(jì)算。對于較為復(fù)雜的公式，不要一次性寫完，最好是分段書寫，每一段執(zhí)行無誤后再輸入下一段，這樣方便公式調(diào)試。【實(shí)踐訓(xùn)練一】請?jiān)贓xcel中制作如圖所示的表格。已知向銀行存入本金100,現(xiàn)要求通過編輯公式快速計(jì)算出在不同年存款利率情況下，每年可以從銀行取出的金額，按復(fù)利計(jì)算。復(fù)利終值的計(jì)算公式為其中：F為復(fù)利終值，P為本金，i為年利率，n為年數(shù)?！緦?shí)踐訓(xùn)練二】請制作如圖所示表格，并通過使用函數(shù)和編輯公式計(jì)算出每個(gè)員工的出生日期、年齡和性別。其中出生日期要求年月日之間用“/”分隔，性別用“男”或“女”表示，年齡計(jì)算到當(dāng)前日期的周歲。思考與點(diǎn)撥：1.我國身份證號碼的編碼結(jié)構(gòu)是怎樣的？我國公民的18位身份證號碼中，1~6位表示籍貫、7~14位表示出生年月日，年用四位數(shù)表示，月和日都用兩位數(shù)字表示，15~17位是順序碼，其中第17位奇數(shù)表示男性，偶數(shù)表示女性，第18位是校驗(yàn)位。2.身份證號碼分析的思路是怎樣的？綜合使用連接運(yùn)算符“&”、文本函數(shù)MID、求余函數(shù)MOD、條件判斷函數(shù)IF、日期差值函數(shù)DATEDIF、當(dāng)前日期函數(shù)TODAY等可完成身份證號碼分析。3.本題目的實(shí)際意義在哪里？身份