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

第1章 Excel函數(shù)與公式學(xué)前準(zhǔn)備
1.1 為什么要學(xué)習(xí)Excel函數(shù)與公式 2
1.1.1 使用公式實(shí)現(xiàn)精準(zhǔn)計(jì)算 2
1.1.2 計(jì)算結(jié)果聯(lián)動(dòng)輕松自動(dòng)更新 2
1.1.3 直觀展示數(shù)據(jù)分析結(jié)果 3
1.2 Excel公式其實(shí)并不難 4
1.2.1 Excel公式和人們常說(shuō)的公式有何區(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 最快捷的計(jì)算方式 14
1.4 無(wú)論何時(shí)都要使用規(guī)范的數(shù)據(jù)源 16
1.4.1 凌亂的數(shù)據(jù)源對(duì)公式計(jì)算有什么危害 16
1.4.2 規(guī)范的數(shù)據(jù)源要具備的10個(gè)條件 18
拓展練習(xí) 用公式排查重復(fù)的兌獎(jiǎng)記錄 18
知識(shí)總結(jié) 20

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

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

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

第5章 處理數(shù)值取舍和排列問(wèn)題
5.1 對(duì)數(shù)值進(jìn)行四舍五入處理 95
5.1.1 將金額四舍五入到角 | ROUND 95
5.1.2 將金額四舍五入到百位 | ROUND 96
5.2 對(duì)收支金額進(jìn)行取整處理 96
5.2.1 將預(yù)算金額保留到整數(shù) | TRUNC 97
5.2.2 對(duì)促銷商品價(jià)格進(jìn)行向上取整 | ROUNDUP 97
5.2.3 對(duì)商品實(shí)收總價(jià)向下取舍到角 | ROUNDDOWN98
5.3 除法運(yùn)算取零取整你說(shuō)了算 99
5.3.1 計(jì)算平均每日出勤人數(shù) | QUOTIENT 100
5.3.2 不用四則運(yùn)算也能統(tǒng)計(jì)余額 | MOD 101
5.3.3 根據(jù)身份證號(hào)碼判斷性別 | 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 制作簡(jiǎn)易隨機(jī)抽獎(jiǎng)器 | INDEX，RANDBETWEEN 105
5.4.4 自動(dòng)生成隨機(jī)密碼 | RANDBETWEEN 106
拓展練習(xí) 隨機(jī)安排不重復(fù)值班人員 108
知識(shí)總結(jié) 110

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

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

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

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

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

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