Excel函數(shù)與公式一本通

第1章 Excel函數(shù)與公式學(xué)前準(zhǔn)備
1.1 為什么要學(xué)習(xí)Excel函數(shù)與公式 2
1.1.1 使用公式實現(xiàn)精準(zhǔn)計算 2
1.1.2 計算結(jié)果聯(lián)動輕松自動更新 2
1.1.3 直觀展示數(shù)據(jù)分析結(jié)果 3
1.2 Excel公式其實并不難 4
1.2.1 Excel公式和人們常說的公式有何區(qū)別 4
1.2.2 公式的組成 5
1.2.3 Excel公式中運(yùn)算符的類型 6
1.2.4 公式不能運(yùn)算的原因及解決方法 8
1.3 函數(shù)在公式中的重要性 11
1.3.1 Excel函數(shù)的類型及查看方式 11
1.3.2 函數(shù)的組成結(jié)構(gòu)是怎樣的 13
1.3.3 最快捷的計算方式 14
1.4 無論何時都要使用規(guī)范的數(shù)據(jù)源 16
1.4.1 凌亂的數(shù)據(jù)源對公式計算有什么危害 16
1.4.2 規(guī)范的數(shù)據(jù)源要具備的10個條件 18
拓展練習(xí) 用公式排查重復(fù)的兌獎記錄 18
知識總結(jié) 20

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

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

第4章 自動完成匯總與統(tǒng)計
4.1 不同條件下的求和計算 59
4.1.1 計算所有銷售員的銷售總額 | SUM 59
4.1.2 每月銷售額記錄在不同工作表中時怎樣求和| SUM 60
4.1.3 統(tǒng)計指定員工的銷售總額 | SUMIF 62
4.1.4 只對銷售額大于1000的值進(jìn)行求和 | SUMIF 63
4.1.5 僅對“智能”產(chǎn)品的銷售額進(jìn)行求和 | SUMIF64
4.1.6 計算滿足指定條件的男性銷售員的銷售金額| SUMIFS 65
4.2 統(tǒng)計員工考勤情況 68
4.2.1 統(tǒng)計參與考勤的人數(shù) | COUNTA 69
4.2.2 統(tǒng)計員工出勤、請假和休息天數(shù) | COUNT，COUNTBLANK 69
4.2.3 統(tǒng)計不同事由的請假天數(shù) | COUNTIF 71
4.2.4 單元格真空還是假空COUNTIF函數(shù)自有定奪 | COUNTIF 72
4.2.5 統(tǒng)計指定月份滿勤的人數(shù) |COUNTIFS 73
4.3 按條件統(tǒng)計平均值 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 統(tǒng)計基本工資低于平均值的人數(shù) | COUNTIF，AVERAGE 80
4.4 迅速統(tǒng)計最大值或最小值 81
4.4.1 提取報價單中的最高和最低金額 | MAX 82
4.4.2 統(tǒng)計旅行團(tuán)中女性的最大年齡 | MAX 82
4.4.3 根據(jù)出庫記錄統(tǒng)計單日最高出庫數(shù)量 | MAX，SUMIF 83
4.4.4 統(tǒng)計投票選舉中最高得票數(shù)和最低得票數(shù) 84
4.4.5 統(tǒng)計去除最高分和最低分的平均分成績 85
4.4.6 根據(jù)服務(wù)態(tài)度好評率計算獎金 86
4.5 數(shù)據(jù)排名講章法 87
4.5.1 為比賽成績排名 | RANK 87
4.5.2 計算運(yùn)動員在三個小組中的排名 | RANK 88
4.5.3 對比賽成績進(jìn)行中式排名 88
拓展練習(xí) 奶茶店銷售數(shù)據(jù)統(tǒng)計分析 90
知識總結(jié) 93

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

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

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

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

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

第10章 公式在條件格式與數(shù)據(jù)驗證中的應(yīng)用
10.1 設(shè)置條件格式突出重要數(shù)據(jù) 217
10.1.1 當(dāng)錄入重復(fù)的產(chǎn)品名稱時自動突出顯示 217
10.1.2 突出顯示每月最高銷量 218
10.1.3 將抽檢不合格的產(chǎn)品整行突出顯示 220
10.2 通過數(shù)據(jù)驗證限制數(shù)據(jù)錄入 221
10.2.1 禁止修改報價單中的任何內(nèi)容 221
10.2.2 禁止在姓名中輸入空格 222
10.2.3 限制只允許輸入18位身份證號碼 223
拓展練習(xí) 制作課程選擇多級列表 225
知識總結(jié) 230

附錄
附錄Ⅰ Excel常用快捷鍵匯總 232
附錄Ⅱ Excel常用函數(shù)一覽 234