《物流數據分析》課件-任務2.3 熟練編輯公式

任務1.5熟練編輯公式【任務目的】1.掌握復雜公式的編輯技巧；2.掌握運算符的使用；3.掌握絕對引用、相對引用和混合引用的概念和用法?！竟ぷ魅蝿铡坑镁庉嫻降姆椒焖僭O計一個如圖所示的九九乘法表?！局R鏈接】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）備注引用運算符:區(qū)域運算符=SUM(B3:C7)冒號兩端兩個單元格形成的連續(xù)矩形區(qū)域求和,聯合運算符=SUM(B3,C7)逗號前后兩個單元格求和(空格)交叉運算符=SUM(A2:B6B2:C6)空格前后兩個單元格區(qū)域共同的單元格求和2.單元格地址的引用方式Excel公式中單元格地址的引用包括相對引用、絕對引用和混合引用三種，三種方式切換的快捷鍵為F4。相對引用：形如B2，在引用公式的時候，單元格的行號和列標都會相應發(fā)生改變。絕對引用：形如$B$2，在引用公式的時候，單元格的行號和列標都固定不變?；旌弦茫盒稳?B1、B$1，在引用公式時，只有行號或列標的一個參數發(fā)生改變?！緦嵤┎襟E】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單元格右下角的填充句柄，發(fā)現公式不能直接正確引用到其它單元格，還需要修改公式。雙擊C3單元格，對公式進行修改，因為向右拖動填充句柄時第一個乘數始終應該引用B列的數字，而向下拖動填充句柄時第二個乘數始終應該引用第2行的數字，所以需要將第B列和第2行鎖定，添加絕對引用符號“$”，不讓其發(fā)生變化，將公式修改為“=$B3&"×"&C$2&"="&$B3*C$2”，再次回車確認。4.引用公式拖動C3單元格右下角的填充句柄至K3，產生第一行表達式，選中C3：K3單元格區(qū)域，拖動單元格區(qū)域的填充句柄至K11，產生所有表達式，如圖所示。5.去除重復的一半雙擊C3單元格，在原有公式基礎上加入IF函數進行判斷，將其公式修改為“IF($B3<C$2,"",$B3&"×"&C$2&"="&$B3*C$2)”，回車確認。拖動C3單元格右下角的填充句柄至K3，產生第一行表達式，選中C3：K3單元格區(qū)域，拖動單元格區(qū)域的填充句柄至K11，產生所有表達式。此時可以發(fā)現，原來的重復表達式被去除，效果如圖所示。思考與點撥：公式“=IF($B3<C$2,"",$B3&"×"&C$2&"="&$B3*C$2)”公式如何理解？B3表示第一個乘數，C2表示第二個乘數，此公式首先判斷第一個乘數是否小于第二個乘數，如果成立則返回第二個參數（“”表示空值），否則返回第三個參數所示的算式?！救蝿招〗Y】手動編輯公式能夠靈活完成各種計算要求，使用公式就必須熟練掌握運算符的使用，同時要注意多個運算符同時出現時的運算優(yōu)先級問題，可以通過括號的使用來改變運算優(yōu)先級。公式引用時需要注意Excel中的三種引用方式，只有正確使用引用方式才能快速實現公式的引用和計算。對于較為復雜的公式，不要一次性寫完，最好是分段書寫，每一段執(zhí)行無誤后再輸入下一段，這樣方便公式調試?！緦嵺`訓練一】請在Excel中制作如圖所示的表格。已知向銀行存入本金100,現要求通過編輯公式快速計算出在不同年存款利率情況下，每年可以從銀行取出的金額，按復利計算。復利終值的計算公式為其中：F為復利終值，P為本金，i為年利率，n為年數?！緦嵺`訓練二】請制作如圖所示表格，并通過使用函數和編輯公式計算出每個員工的出生日期、年齡和性別。其中出生日期要求年月日之間用“/”分隔，性別用“男”或“女”表示，年齡計算到當前日期的周歲。思考與點撥：1.我國身份證號碼的編碼結構是怎樣的？我國公民的18位身份證號碼中，1~6位表示籍貫、7~14位表示出生年月日，年用四位數表示，月和日都用兩位數字表示，15~17位是順序碼，其中第17位奇數表示男性，偶數表示女性，第18位是校驗位。2.身份證號碼分析的思路是怎樣的？綜合使用連接運算符“&”、文本函數MID、求余函數MOD、條件判斷函數IF、日期差值函數DATEDIF、當前日期函數TODAY等可完成身份證號碼分析。3.本題目的實際意義在哪里？身份