Excel 2019公式與函數(shù)應(yīng)用大全（視頻教學(xué)版）

前言

第1章 認(rèn)識Excel公式

1.1 認(rèn)識公式2

1.1.1 公式的組成2

實戰(zhàn)實例：根據(jù)面試和筆試成績計算總成績2

1.1.2 公式中運算符的使用3

實戰(zhàn)實例1：計算總銷售額（算術(shù)運算符）3

實戰(zhàn)實例2：比較各店面的銷售額（比較運算符）4

實戰(zhàn)實例3：統(tǒng)計各季度分店的總銷售額（引用運算符）4

1.1.3 公式輸入與修改5

實戰(zhàn)實例1：統(tǒng)計各產(chǎn)品的折扣后金額5

實戰(zhàn)實例2：重新計算每月總銷售額6

1.1.4 公式的復(fù)制7

實戰(zhàn)實例1：統(tǒng)計學(xué)生總成績7

實戰(zhàn)實例2：統(tǒng)計各店鋪兼職人員工資9

1.2 認(rèn)識函數(shù)10

1.2.1 函數(shù)的巨大作用10

實戰(zhàn)實例1：計算全年總支出11

實戰(zhàn)實例2：篩選應(yīng)聘者的年齡11

1.2.2 參數(shù)設(shè)置要符合規(guī)則12

實戰(zhàn)實例1：判斷面試成績是否合格12

實戰(zhàn)實例2：統(tǒng)計指定產(chǎn)品的總銷量13

1.2.3 初學(xué)者如何學(xué)習(xí)函數(shù)14

實戰(zhàn)實例1：打開“插入函數(shù)”對話框逐步了解參數(shù)14

實戰(zhàn)實例2：使用幫助功能學(xué)習(xí)函數(shù)15

1.2.4 正確輸入函數(shù)的方法16

實戰(zhàn)實例1：使用編輯欄中的“插入函數(shù)”按鈕輸入公式16

實戰(zhàn)實例2：使用“公式”選項卡中的“插入函數(shù)”按鈕輸入公式17

1.2.5 修改與保存函數(shù)公式19

實戰(zhàn)實例1：修改函數(shù)參數(shù)19

實戰(zhàn)實例2：保存未輸入完的公式20

1.3 嵌套函數(shù)21

實戰(zhàn)實例1：兩項測試數(shù)據(jù)都達(dá)標(biāo)時才予以驗收合格21

實戰(zhàn)實例2：根據(jù)職位調(diào)整基本工資23

1.4 了解數(shù)組公式24

1.4.1 普通公式與數(shù)組公式的區(qū)別25

實戰(zhàn)實例：計算商品折后總金額25

1.4.2 多個單元格數(shù)組公式26

實戰(zhàn)實例：統(tǒng)計前三名分?jǐn)?shù)26

1.4.3 單個單元格數(shù)組公式27

實戰(zhàn)實例：計算所有商品的總銷售額27

1.4.4 修改或刪除數(shù)組公式28

實戰(zhàn)實例：修改或刪除數(shù)組公式28

1.5 公式的保護(hù)與設(shè)置28

實戰(zhàn)實例1：設(shè)置公式保護(hù)29

實戰(zhàn)實例2：設(shè)置公式隱藏31

第2章 數(shù)據(jù)源的引用方式

2.1 不同的單元格引用方式34

2.1.1 相對引用34

實戰(zhàn)實例：相對引用34

2.1.2 絕對引用36

實戰(zhàn)實例：絕對引用36

2.1.3 混合引用38

實戰(zhàn)實例：混合引用38

2.1.4 引用當(dāng)前工作表之外的單元格40

實戰(zhàn)實例：引用當(dāng)前工作表之外的單元格40

2.1.5 引用多個工作表中的同一單元格41

實戰(zhàn)實例：引用多個工作表中的同一單元格41

2.1.6 引用其他工作簿中的單元格43

實戰(zhàn)實例：引用其他工作簿中的單元格43

2.2 了解名稱定義功能45

2.2.1 定義名稱的作用45

實戰(zhàn)實例1：定義名稱簡化公式45

實戰(zhàn)實例2：快速輸入序列46

實戰(zhàn)實例3：快速跳轉(zhuǎn)至指定工作表46

2.2.2 定義名稱的方法47

實戰(zhàn)實例1：使用“定義名稱”功能48

實戰(zhàn)實例2：在名稱框中直接定義名稱50

2.2.3 公式中應(yīng)用名稱50

實戰(zhàn)實例：公式中應(yīng)用名稱51

2.2.4 定義公式為名稱52

實戰(zhàn)實例：定義公式為名稱52

2.2.5 創(chuàng)建動態(tài)名稱54

實戰(zhàn)實例：創(chuàng)建動態(tài)名稱54

2.2.6 管理定義的名稱57

實戰(zhàn)實例1：重新修改名稱的引用位置57

實戰(zhàn)實例2：刪除不再使用的名稱58

第3章 常見錯誤公式修正及錯誤值分析

3.1 公式檢測與審核60

3.1.1 利用“公式求值”按鈕解析公式60

實戰(zhàn)實例：利用“公式求值”按鈕解析公式60

3.1.2 使用【F9】鍵快速查看公式指定部分計算結(jié)果62

實戰(zhàn)實例：使用【F9】鍵快速查看公式指定部分計算結(jié)果62

3.1.3 查看從屬與引用單元格63

實戰(zhàn)實例1：查看從屬引用單元格—使用“追蹤從屬單元格”63

實戰(zhàn)實例2：查看從屬引用單元格—使用“追蹤引用單元格”64

3.1.4 使用“錯誤檢查”功能65

實戰(zhàn)實例：使用“錯誤檢查”功能65

3.1.5 利用“監(jiān)視窗口”監(jiān)視數(shù)據(jù)66

實戰(zhàn)實例：利用“監(jiān)視窗口”監(jiān)視數(shù)據(jù)66

3.1.6 利用“顯示公式”功能查看公式68

實戰(zhàn)實例：利用“顯示公式”功能查看公式68

3.2 常見錯誤公式的修正69

3.2.1 修正文本數(shù)據(jù)參與計算的問題70

實戰(zhàn)實例1：數(shù)據(jù)中帶有中文單位70

實戰(zhàn)實例2：單元格格式是文本格式70

3.2.2 修正日期計算時差值總為日期問題71

實戰(zhàn)實例：修正日期計算時差值總為日期問題71

3.2.3 空白單元格不為空問題72

實戰(zhàn)實例1：公式返回的空值再參與計算時造成出錯72

實戰(zhàn)實例2：單元格中有英文單引號造成出錯74

實戰(zhàn)實例3：自定義單元格格式為隱藏格式造成出錯75

3.2.4 實際的值與顯示的值不同77

實戰(zhàn)實例：實際的值與顯示的值不同77

3.2.5 修正循環(huán)引用不能計算的公式78

實戰(zhàn)實例：修正循環(huán)引用不能計算的公式79

3.3 分析與解決公式返回的錯誤值80

3.3.1 分析與解決 “####”錯誤值80

實戰(zhàn)實例：分析與解決 “####”錯誤值80

3.3.2 分析與解決“#DIV/0!”錯誤值81

實戰(zhàn)實例：分析與解決“#DIV/0!”錯誤值81

3.3.3 分析與解決“#N/A” 錯誤值82

實戰(zhàn)實例1：數(shù)據(jù)源引用錯誤82

實戰(zhàn)實例2：行數(shù)與列數(shù)引用不一致83

3.3.4 分析與解決“#NAME?”錯誤值84

實戰(zhàn)實例1：公式中的文本要添加引號84

實戰(zhàn)實例2：公式中使用沒有定義的名稱85

實戰(zhàn)實例3：引用單元格區(qū)域時缺少冒號87

3.3.5 分析與解決“#NUM!”錯誤值88

實戰(zhàn)實例：分析與解決“#NUM!”錯誤值88

3.3.6 分析與解決“#VALUE!” 錯誤值89

實戰(zhàn)實例：分析與解決“#VALUE!”錯誤值89

3.3.7 分析與解決“#REF!” 錯誤值90

實戰(zhàn)實例：分析與解決“#REF!”錯誤值90

3.3.8 分析與解決“#NULL!” 錯誤值91

實戰(zhàn)實例：分析與解決“#NULL!”錯誤值91

第4章 邏輯函數(shù)

4.1 邏輯判斷函數(shù)94

4.1.1 AND：判斷指定的多個條件是否全部成立94

實戰(zhàn)實例1：判斷學(xué)生各科成績是否都合格94

實戰(zhàn)實例2：判斷是否通過職稱評定95

4.1.2 OR：判斷指定的多個條件是否有一個成立96

實戰(zhàn)實例1：判斷員工考核是否合格96

實戰(zhàn)實例2：判斷是否通過職稱評定98

4.1.3 NOT：判斷指定的條件不成立98

實戰(zhàn)實例1：判斷員工是否符合發(fā)放工齡工資的要求99

實戰(zhàn)實例2：篩選出高中學(xué)歷的應(yīng)聘人員100

4.2 根據(jù)邏輯判斷結(jié)果返回值101

4.2.1 IF：根據(jù)邏輯測試值返回指定值101

實戰(zhàn)實例1：判斷學(xué)生成績是否優(yōu)秀101

實戰(zhàn)實例2：分區(qū)間判斷成績并返回不同結(jié)果103

實戰(zhàn)實例3：判斷能夠獲得公司年終福利的員工104

實戰(zhàn)實例4：判斷是否通過職稱評定105

實戰(zhàn)實例5：根據(jù)雙條件篩選出符合發(fā)放贈品條件的消費者106

實戰(zhàn)實例6：根據(jù)員工的職位和工齡調(diào)整工資107

實戰(zhàn)實例7：只為滿足條件的商品提價109

4.2.2 IFS：多條件判斷110

實戰(zhàn)實例：計算銷售員提成金額110

4.2.3 SWITCH：返回匹配的結(jié)果112

實戰(zhàn)實例1：根據(jù)代碼返回到具體的星期112

實戰(zhàn)實例2：分區(qū)間判斷成績并返回不同結(jié)果113

4.2.4 IFERROR：根據(jù)錯誤值返回指定值114

實戰(zhàn)實例：解決被除數(shù)為空值（或0值）時返回錯誤值問題114

第5章 日期與時間函數(shù)

5.1 構(gòu)建與提取日期、時間118

5.1.1 TODAY：返回當(dāng)前日期118

實戰(zhàn)實例1：統(tǒng)計書稿還有多少天截稿118

實戰(zhàn)實例2：判斷借書證是否到期119

5.1.2 DATE：構(gòu)建標(biāo)準(zhǔn)日期120

實戰(zhàn)實例1：將不規(guī)范日期轉(zhuǎn)換為標(biāo)準(zhǔn)日期120

實戰(zhàn)實例2：合并年月日并得到完整日期122

5.1.3 TIME：構(gòu)建標(biāo)準(zhǔn)時間122

實戰(zhàn)實例：計算促銷商品的結(jié)束時間123

5.1.4 YEAR：返回某日期中的年份值124

實戰(zhàn)實例：計算員工年齡124

5.1.5 MONTH：返回某日期中的月份值125

實戰(zhàn)實例1：判斷員工是否是本月過生日125

實戰(zhàn)實例2：統(tǒng)計指定月份的總?cè)霂炝?26

5.1.6 DAY：返回以序列號表示的某日期中的天數(shù)128

實戰(zhàn)實例：按本月缺勤天數(shù)計算缺勤扣款金額128

5.1.7 EOMONTH：返回某個月份最后一天的序列號129

實戰(zhàn)實例1：計算實習(xí)生轉(zhuǎn)正日期130

實戰(zhàn)實例2：根據(jù)促銷開始日期計算促銷天數(shù)130

5.1.8 WEEKDAY：返回日期對應(yīng)的星期數(shù)132

實戰(zhàn)實例1：判斷加班日期是星期幾132

實戰(zhàn)實例2：判斷加班日期是工作日還是雙休日133

5.1.9 HOUR：返回時間值的小時數(shù)134

實戰(zhàn)實例：確定客戶來訪時間區(qū)間134

5.1.10 MINUTE：返回時間值的分鐘數(shù)135

實戰(zhàn)實例1：計算商品的促銷用時（分鐘數(shù)）136

實戰(zhàn)實例2：計算停車分鐘數(shù)137

5.1.11 SECOND：返回時間值的秒數(shù)138

實戰(zhàn)實例：計算比賽總秒數(shù)138

5.2 期間差140

5.2.1 DAYS360：返回兩日期間相差的天數(shù)（按照一年360天的算法）140

實戰(zhàn)實例1： 計算賬齡140

實戰(zhàn)實例2： 計算合同到期天數(shù)141

5.2.2 NETWORKDAYS：計算某時段中的工作日天數(shù)142

實戰(zhàn)實例：計算臨時工的實際工作天數(shù)142

5.2.3 NETWORKDAYS.INTL函數(shù)143

實戰(zhàn)實例：計算臨時工的實際工作天數(shù)（指定只有星期日為休息日）144

5.2.4 WORKDAY：根據(jù)起始日期計算出指定工作日之后的日期145

實戰(zhàn)實例：計算工程的竣工日期145

5.2.5 WORKDAY.INTL函數(shù)146

實戰(zhàn)實例：根據(jù)項目各流程所需工作日計算項目結(jié)束日期147

5.2.6 ISOWEEKNUM：返回日期的ISO周數(shù)147

實戰(zhàn)實例：判斷項目開工日期位于第幾周148

5.2.7 EDATE：計算出間隔指定月份數(shù)后的日期148

實戰(zhàn)實例1：計算食品過期日期149

實戰(zhàn)實例2：計算員工退休日期149

5.3 文本日期與文本時間的轉(zhuǎn)換150

5.3.1 DATEVALUE：將文本日期轉(zhuǎn)換為可計算的日期序列號150

實戰(zhàn)實例：計算商品促銷天數(shù)151

5.3.2 TIMEVALUE：將文本時間轉(zhuǎn)換為可計算的小數(shù)值152

實戰(zhàn)實例：根據(jù)下班打卡時間計算加班時長152

第6章 數(shù)學(xué)函數(shù)

6.1 數(shù)據(jù)計算函數(shù)155

6.1.1 SUM：求和155

實戰(zhàn)實例1：統(tǒng)計總營業(yè)額155
......