Excel函數與公式一本通：場景化應用·案例式學習

第1章Excel函數與公式學前準備
1.1 為什麼要學習Excel函數與公式2
1.1.1 使用公式實現精準計算2
1.1.2 計算結果聯動輕鬆自動更新2
1.1.3 直觀展示數據分析結果3
1.2 Excel公式其實並不難4
1.2.1 Excel公式和人們常說的公式有何區別4
1.2.2 公式的組成5
1.2.3 Excel公式中運算符的類型6
1.2.4 公式不能運算的原因及解決方法8
1.3 函數在公式中的重要性11
1.3.1 Excel函數的類型及查看方式11
1.3.2 函數的組成結構是怎樣的13
1.3.3 最快捷的計算方式14
1.4 無論何時都要使用規範的數據源16
1.4.1 凌亂的數據源對公式計算有什麼危害16
1.4.2 規範的數據源要具備的10個條件18
拓展練習用公式排查重複的兌獎記錄18
知識總結20

第2章公式與函數入門課
2.1 公式的輸入與編輯22
2.1.1 輸入公式計算商品折扣率22
2.1.2 重新編輯折扣率公式23
2.1.3 填充公式計算所有商品折扣率24
2.2 插入函數的多種方法26
2.2.1 統計銷售員全年銷售金額26
2.2.2 統計各季度銷售金額27
2.2.3 統計1季度指定店舖的銷售金額29
2.3 根據折扣計算商品實際銷售價格30
2.3.1 相對引用對計算結果的影響30
2.3.2 絕對引用對計算結果的影響31
2.3.3 混合引用對計算結果的影響32
拓展練習計算員工工資33
知識總結36

第3章邏輯函數斷是非
3.1 Excel邏輯值的類型及作用38
3.1.1 用邏輯值顯示比較運算結果38
3.1.2 信息函數的邏輯判斷結果38
3.1.3 判斷實際支出與預算金額是否匹配40
3.2 IF函數自動做出準確判斷40
3.2.1 自動判斷成績是否合格41
3.2.2 根據考生成績自動評定等級41
3.2.3 將成績以優秀、良好、及格、不及格的形式表示42
3.3 IF與其他函數的嵌套應用43
3.3.1 自動判斷員工平均工資是否高於部門平均工資43
3.3.2 直接對帶單位的數值進行計算44
3.3.3 屏蔽公式返回的錯誤值45
3.4 多條件判斷的三個好搭檔47
3.4.1 了解函數47
3.4.2 判斷公司新員工各項考核是否已全部通過48
3.4.3 判斷員工是否具備內部競聘資格49
3.4.4 判斷員工是否具有住房補貼資格50
3.4.5 判斷銀行卡號位數是否準確51
3.4.6 根據性別和年齡判斷是否退休52
3.4.7 判斷產品最終檢驗結果是否合格53
拓展練習自動完成員工業績考評及獎金計算54
知識總結57

第4章自動完成匯總與統計
4.1 不同條件下的求和計算59
4.1.1 計算所有銷售員的銷售總額| SUM 59
4.1.2 每月銷售額記錄在不同工作表中時怎樣求和| SUM 60
4.1.3 統計指定員工的銷售總額| SUMIF 62
4.1.4 只對銷售額大於1000的值進行求和| SUMIF 63
4.1.5 僅對“智能”產品的銷售額進行求和| SUMIF64
4.1.6 計算滿足指定條件的男性銷售員的銷售金額| SUMIFS 65
4.2 統計員工考勤情況68
4.2.1 統計參與考勤的人數| COUNTA 69
4.2.2 統計員工出勤、請假和休息天數| COUNT，COUNTBLANK 69
4.2.3 統計不同事由的請假天數| COUNTIF 71
4.2.4 單元格真空還是假空COUNTIF函數自有定奪| COUNTIF 72
4.2.5 統計指定月份滿勤的人數|COUNTIFS 73
4.3 按條件統計平均值74
4.3.1 計算平均獎金金額| AVERAGEA，AVERAGE75
4.3.2 去除0值計算平均獎金金額| AVERAGE 75
4.3.3 計算指定部門員工的平均工資| AVERAGEIF78
4.3.4 計算人事部30歲以下員工平均工資| AVERAGEIFS 79
4.3.5 計算30～40歲之間員工的平均基本工資| AVERAGEIFS 80
4.3.6 統計基本工資低於平均值的人數| COUNTIF，AVERAGE 80
4.4 迅速統計最大值或最小值81
4.4.1 提取報價單中的最高和最低金額| MAX 82
4.4.2 統計旅行團中女性的最大年齡| MAX 82
4.4.3 根據出庫記錄統計單日最高出庫數量| MAX，SUMIF 83
4.4.4 統計投票選舉中最高得票數和最低得票數84
4.4.5 統計去除最高分和最低分的平均分成績85
4.4.6 根據服務態度好評率計算獎金86
4.5 數據排名講章法87
4.5.1 為比賽成績排名| RANK 87
4.5.2 計算運動員在三個小組中的排名| RANK 88
4.5.3 對比賽成績進行中式排名88
拓展練習奶茶店銷售數據統計分析90
知識總結93

第5章處理數值取捨和排列問題
5.1 對數值進行四捨五入處理95
5.1.1 將金額四捨五入到角| ROUND 95
5.1.2 將金額四捨五入到百位| ROUND 96
5.2 對收支金額進行取整處理96
5.2.1 將預算金額保留到整數| TRUNC 97
5.2.2 對促銷商品價格進行向上取整| ROUNDUP 97
5.2.3 對商品實收總價向下取捨到角| ROUNDDOWN98
5.3 除法運算取零取整你說了算99
5.3.1 計算平均每日出勤人數| QUOTIENT 100
5.3.2 不用四則運算也能統計餘額| MOD 101
5.3.3 根據身份證號碼判斷性別| IF，MOD，MID 101
5.4 隨機函數在工作中的廣泛用途102
5.4.1 隨機安排考生座位| RANK 103
5.4.2 為志願者隨機分配工作內容| CHOOSE，RANDBETWEEN 104
5.4.3 製作簡易隨機抽獎器| INDEX，RANDBETWEEN 105
5.4.4 自動生成隨機密碼| RANDBETWEEN 106
拓展練習隨機安排不重複值班人員108
知識總結110

第6章輕鬆實現數據查詢
6.1 使用VLOOKUP函數根據條件查找數據112
6.1.1 根據商品名稱查詢商品價格112
6.1.2 根據身高自動查詢服裝尺碼113
6.1.3 根據工號自動查詢員工信息114
6.1.4 查詢不同店鋪同類產品的銷量116
6.1.5 根據考核分數自動評定等級117
6.2 HLOOKUP換個方向也能查找需要的數據118
6.2.1 查找指定車間某日的生產量119
6.2.2 查詢產品不同時期的價格120
6.3 MATCH函數根據數據位置進行查找121
6.3.1 查找指定演員的出場次序121
6.3.2 查找列表中是否包含指定的產品122
6.3.3 排查是否存在重複報銷的項目123
6.4 CHOOSE函數根據給定索引值查找數據124
6.4.1 隨機安排家務125
6.4.2 跳過週六和周日安排值日人員126
6.4.3 根據員工編號自動錄入所屬部門126
6.4.4 根據產品不良數量自動判斷處理方式127
6.4.5 根據書名和圖書類別查詢指定圖書的價格128
6.4.6 計算工程完工日期是所在月份的上旬、中旬還是下旬129
6.5 INDEX函數提取指定行列交叉處的值130
6.5.1 了解兩種參數的設置方法130
6.5.2 根據收發地自動查詢首重價格132
6.5.3 根據月份自動查詢銷售金額133
6.5.4 隔行提取客戶名稱133
6.5.5 根據姓名逆向查詢節目名稱134
6.6 OFFSET函數根據指定偏移量查找數據135
6.6.1 OFFSET函數的詳細應用分析135
6.6.2 查詢指定日期之前的出庫總量136
6.6.3 根據准考證號查詢考生成績137
6.6.4 在合併單元格中填充序列138
6.6.5 將合併區域中的值拆分到多個單元格中139
6.6.6 根據工資表製作工資條141
拓展練習製作可自動顯示新增數據的動態圖表143
知識總結146

第7章用函數分析日期和時間函數
7.1 根據日期推算其他相關數據148
7.1.1 計算距離活動結束還剩多少天| NOW 148
7.1.2 設置合同狀態提醒| TODAY 149
7.1.3 統計臨期合同的數量| TODAY 149
7.1.4 日期處理利器DATE函數應用分析| DATE 150
7.1.5 根據年份和月份計算第一天和最後一天| DATE152
7.2 從日期中提取年、月、日信息153
7.2.1 根據員工入職日期計算工齡| YEAR 154
7.2.2 計算2021年之前的總成交額| SUM，IF，YEAR154
7.2.3 統計本月過生日的員工人數| MONTH 155
7.2.4 根據入職日期計算新員工轉正日期| DAY 156
7.2.5 計算分期付款的最後一個還款日| EDATE 157
7.3 有效利用時間信息完成數據分析和計算158
7.3.1 計算員工工時工資| HOUR 159
7.3.2 根據任務完成時間計算積分| MINUTE 160
7.3.3 計算提前完成任務的人數| SUM，HOUR 162
7.3.4 計算借車時長| HOUR，MINUTE，SECOND162
7.4 星期函數的應用163
7.4.1 計算指定日期是星期幾| WEEKDAY 163
7.4.2 判斷演出時間是星期幾| TEXT，WEEKDAY164
7.4.3 判斷演出時間是周末還是工作日| IF，WEEKDAY164
7.4.4 統計所有星期一至星期五的消費金額| SUM，WEEKDAY 165
7.4.5 計算今年秋季開學是第幾週| WEEKNUM 166
7.4.6 計算項目歷時多少週完成| WEEKNUM 167
7.4.7 計算項目的實際工作天數| NETWORKDAYS 167
7.4.8 單休制度如何統計工作天數168
7.4.9 統計每個工作階段休息的天數| NETWORKDAYS.INTL 168
拓展練習根據出生日期計算其他相關信息169
知識總結171

第8章文本處理的關鍵函數
8.1 文本長度應該如何計算173
8.1.1 按照書名的長度進行排序| LEN 174
8.1.2 判斷電話號碼是座機號還是手機號| LEN，IF174
8.1.3 計算字符串中包含的數字個數| LEN，LENB175
8.2 從指定位置提取字符串175
8.2.1 從客戶地址中提取省份信息| LEFT 176
8.2.2 省份長度不同時如何自動提取| LEFT，FIND176
8.2.3 從身份證號碼中提取出生日期| MID 177
8.2.4 根據打卡數據判斷是否遲到| MID，IF 178
8.2.5 提取手機四位數尾號| RIGHT 179
8.2.6 根據打卡數據判斷員工所屬部門| RIGHT，VLOOKUP 179
8.3 文本的查找和替換180
8.3.1 FIND函數判斷字符位置| FIND 180
8.3.2 從混合數據中提取姓名| LEFT，FIND 181
8.3.3 提取括號中的內容| IFERROR，MID，FIND182
8.3.4 FIND函數的孿生兄弟SEARCH | SEARCH 183
8.3.5 按需替換指定位置的字符| REPLACE 184
8.3.6 批量更改課程編號| IF，MID，REPLACE 184
8.3.7 直接指定要替換的內容| SUBSTITUTE 185
8.3.8 隱藏身份證號碼中的部分數字| SUBSTITUTE，MID 186
8.3.9 統計每日加工工序| SUBSTITUTE 187
8.3.10 對帶單位的金額進行求和| SUMPRODUCT 187
8.4 字符轉換函數的應用188
8.4.1 將產品的品類轉換成大寫| UPPER 188
8.4.2 將大寫單位轉換成小寫| LOWER 189
8.4.3 將中英文互譯詩句轉換成第一個單詞首字母大寫| LOWER，CONCATENATE 189
8.4.4 將英文名的每個單詞全部轉換成首字母大寫| PROPER 190
8.4.5 將文本型數字轉換成真正的數字| VALUE 190
8.5 最重要的格式轉換函數192
8.5.1 搞清楚常用代碼的含義| TEXT 192
8.5.2 自動顯示英文狀態下的當前日期和星期幾| TEXT，TODAY 194
8.5.3 計算加班時長| IF，TEXT 195
8.5.4 直觀顯示活動預算和實際支出的差額| TEXT195
8.5.5 分段顯示電話號碼| TEXT 196
8.5.6 計算施工期間有多少個項目是在星期一完工的| SUM，N，TEXT 196
8.5.7 將財務報表中的數據以小數點對齊| TEXT 197
拓展練習對合併的課程信息進行分列整理198
知識總結200

第9章理清資產折舊及投資理財難題
9.1 計算固定資產折舊值202
9.1.1 使用固定餘額遞減法計算資產折舊值| DB 202
9.1.2 使用雙倍餘額遞減法計算資產折舊值| DDB203
9.1.3 使用雙倍餘額遞減法計算任何期間的資產折舊值| VDB 204
9.1.4 使用年限總和折舊法計算折舊值| SYD 205
9.2 用函數計算存款與貸款利息205
9.2.1 根據投資金額、投資年數及目標收益計算年增長率| RATE 206
9.2.2 根據銀行初期存款額、定期存款額、存款年限計算利息| RATE 207
9.2.3 根據保險金額、年限及每年返還金額計算收益率| RATE 207
9.2.4 根據利息和存款金額計算存款額達到目標數需要多少個月| NPER 207
9.2.5 計算等額等息貸款的還款年限| NPER 208
9.2.6 根據年利率、每次存款金額及存款年限計算本金與利息總和| FV 209
9.2.7 根據年利率、每月扣除金額及年限計算住房公積金| FV 210
9.2.8 根據月支付金額、回報率及支付年限計算投資未來現值| PV 210
9.2.9 計算多個項目在相同利益條件下投資更少的項目| PV 211
9.2.10 根據貸款、利率和貸款年限計算需要償還的本金| CUMPRINC 212
9.2.11 根據貸款、年利息和貸款年限計算指定時間內的利息| CUMIPMT 213
拓展練習根據固定資產清單計算累計折舊金額214
知識總結215

第10章公式在條件格式與數據驗證中的應用
10.1 設置條件格式突出重要數據217
10.1.1 當錄入重複的產品名稱時自動突出顯示217
10.1.2 突出顯示每月最高銷量218
10.1.3 將抽檢不合格的產品整行突出顯示220
10.2 通過數據驗證限制數據錄入221
10.2.1 禁止修改報價單中的任何內容221
10.2.2 禁止在姓名中輸入空格222
10.2.3 限制只允許輸入18位身份證號碼223
拓展練習製作課程選擇多級列表225
知識總結230
附錄
附錄Ⅰ Excel常用快捷鍵匯總232
附錄Ⅱ Excel常用函數一覽234