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