高效隨身查——Excel 2021必學的函數與公式應用技巧（視頻教學版）
賽貝爾資訊
商品描述
目錄大綱
目 錄
contents
第 1 章 函數是什麽？公式要怎麽用？ 1
1.1 公式的輸入與編輯 1
技巧 1 快速求和（平均值） 1
技巧 2 公式函數不分家 . 2
技巧 3 啟用“插入函數”對話框編輯函數 . 3
技巧 4 手寫編輯公式 . 6
技巧 5 公式的修改 . 8
技巧 6 不記得函數全稱也能正確輸入函數 . 9
技巧 7 快速查找和學習某函數用法 9
技巧 8 使用批量計算 11
技巧 9 大範圍公式復制的方法 11
技巧 10 跳過非空單元格批量建立公式 12
技巧 11 普通公式與數組公式 . 14
技巧 12 為什麽數字與“空”單元格相加出錯 . 15
技巧 13 為什麽明明顯示的是數字而計算結果卻為 0 . 16
技巧 14 暫時保留沒有輸入完整的公式 17
技巧 15 將公式運算結果轉換為數值 18
技巧 16 保護公式不被修改 19
1.2 公式中數據源的引用 20
技巧 17 相對引用數據源計算 20
技巧 18 絕對引用數據源計算 21
技巧 19 引用當前工作表之外的單元格 22
技巧 20 引用多工作表的同一單元格計算 24
技巧 21 引用其他工作簿中的數據源 . 25
技巧 22 用“F4”鍵快速改變數據源引用類型 27
技巧 23 為什麽要定義名稱？ 27
技巧 24 快速定義名稱的方法有哪些？ 28
技巧 25 引用定義的名稱創建公式 . 29
技巧 26 將公式定義為名稱 30
技巧 27 將表格創建為動態名稱實現數據計算即時更新 . 31
技巧 28 重新修改名稱的引用位置 . 33
1.3 學會分解、理解分式 34
技巧 29 查看長公式中某一步的計算結果 . 34
技巧 30 追蹤公式引用的單元格 . 35
技巧 31 通過“公式求值”功能逐步分解公式 . 35
技巧 32 在單元格中顯示所有公式 . 37
第 2 章 邏輯函數範例 .39
1．AND 函數（檢驗一組數據是否都滿足條件） 39
技巧 1 考評學生的各門課程是否全部及格 . 39
技巧 2 一次性判斷一組數據是否都滿足條件 . 40
2．NOT 函數（對所給參數求反） 41
技巧 3 篩選掉小於某一年齡的應聘人員 41
3．OR 函數（檢驗一組數據是否有一個滿足條件） .42
技巧 4 檢驗員工是否通過考評 . 42
4．IF 函數（根據條件判斷真假） 43
技巧 5 利用 IF 函數進行金額校驗 . 43
技巧 6 根據不同返利比計算返利金額 44
技巧 7 根據業務處理量判斷員工業務水平 . 45
技巧 8 分性別判斷成績是否合格 . 46
技巧 9 根據消費卡類別與消費額派發贈品 . 48
技巧 10 根據職工性別和職務判斷退休年齡 . 49
技巧 11 根據年齡與學歷對應聘人員二次篩選 50
技巧 12 根據商品的名稱與顏色進行一次性調價 51
技巧 13 根據 3 項業務的完成率計算綜合完成率 . 52
技巧 14 比較兩個採購部門的採購價格 54
5．IFS 函數（多層條件判斷） .55
技巧 15 比較 IF 與 IFS . 55
技巧 16 分男女性別判斷跑步成績是否合格 . 56
技巧 17 實現智能調薪 . 57
技巧 18 計算個人所得稅 58
6．SWITCH 函數（根據表達式的返回值匹配結果） .62
技巧 19 只安排周一至周三值班 . 62
技巧 20 提取紙張大小的規格分類 63
7．IFERROR 函數（根據條件判斷真假） .64
技巧 21 當被除數為空值（或 0 值）時返回“計算數據源有錯誤”文字 . 65
第 3 章 文本函數範例 .66
3.1 提取文本 66
1．LEFT 函數（從最左側開始提取指定數目的字符） .66
技巧 1 提取分部名稱 . 66
技巧 2 從特產名稱中提取產地信息 67
2．LEFTB 函數（按字節數從最左側提取指定數目的字符） 68
技巧 3 根據產品編號提取類別 . 68
3．RIGHT 函數（提取從最右側開始的指定數目的字符） 69
技巧 4 從右側提取字符並自動轉換為數值 . 69
技巧 5 只為滿足條件的產品提價 . 70
技巧 6 發票金額的分列填寫 72
4．RIGHTB 函數（按字節數從最右側提取指定數目的字符） 75
技巧 7 提取產品的規格數據 75
5．MID 函數（從指定位置開始提取字符） .76
技巧 8 從規格數據中提取部分數據 . 76
技巧 9 從身份證號碼中提取出生年份 77
技巧 10 從身份證號碼中提取性別 . 78
6．MIDB 函數（按字節數從指定位置開始提取字符） 79
技巧 11 從房號數據中提取單元號 80
7．CONCATENATE 函數（合並多個字符） 81
技巧 12 合並商品貨號、碼數及顏色 . 81
技巧 13 合並面試人員的總分數與錄取情況 82
3.2 查找與替換文本 83
8．FIND 函數（查找指定字符並返回其位置） .83
技巧 14 查找品名所在位置並提取 . 83
技巧 15 提取產品的貨號 . 85
技巧 16 問捲調查時實現自動統計答案 86
9．REPLACE 函數（替換字符中的部分字符） .87
技巧 17 屏蔽中獎手機號碼的後幾位數 88
10．SEARCH 函數（查找字符並返回其起始位置） .89
技巧 18 從產品的名稱中提取重量數據 89
11．SUBSTITUTE 函數（用新字符替換舊字符） .90
技巧 19 查找特定文本且將第一次出現的刪除，其他保留 . 90
技巧 20 根據報名學員統計人數 92
3.3 轉換文本格式 93
12．FIXED 函數（按指定的小數位數進行取整） .93
技巧 21 解決因四捨五入而造成的顯示誤差問題 93
13．WIDECHAR 函數（半角字母轉換為全角字母） .94
技巧 22 將半角英文字母轉換為全角英文字母 . 94
14．UPPER( 將文本轉換為大寫形式 ) .95
技巧 23 將小寫英文文本一次性轉換為大寫 95
15．LOWER 函數（大寫字母轉換為小寫字母） 95
技巧 24 將大寫字母轉換為小寫字母 96
16．PROPER 函數（將首字符轉換為大寫） .96
技巧 25 一次性將每個單詞的首字母轉換為大寫 96
17．RMB 函數（四捨五入數值，並添加千分位符號和 ￥ 符號） 97
技巧 26 將銷售額一次性轉換為人民幣格式 97
18．DOLLAR 函數（四捨五入數值，並添加千分位符號和 $ 符號） 98
技巧 27 將銷售金額一次性轉換為美元貨幣格式 98
19．TEXT 函數（將數值轉換為指定格式的文本） 99
技巧 28 讓計算得到金額顯示為“餘款：15,850.00”形式 . 99
技巧 29 返回值班日期對應在的星期數 100
技巧 30 按上下班時間計算加班時長並顯示為“* 時 * 分”形式 101
技巧 31 解決日期計算返回日期序列號問題 . 102
技巧 32 讓數據統一顯示固定的位數 103
技巧 33 讓合並的日期顯示正確格式 104
20．VALUE 函數（將文本型數字轉換成數值型數字） 105
技巧 34 解決總金額無法計算的問題 105
3.4 其他文本函數 106
21．LEN 函數（返回字符串的字符數） 106
技巧 35 將電話號碼的區號與號碼分離開 106
22．REPT 函數（重復文本） .107
技巧 36 輸入身份證號碼填寫框 . 108
技巧 37 根據銷售額用“★”評定等級 108
23．TRIM 函數（清除空格） .109
技巧 38 刪除文本單詞中多餘的空格 109
24．EXACT 函數（比較兩個字符串是否相同） .110
技巧 39 比較兩個店鋪的平均售價是否相同 110
第 4 章 日期與時間函數範例 . 112
4.1 返回日期 112
1．NOW 函數（返回當前日期時間） .112
技巧 1 為打印報表添加打印時間 .112
2．TODAY 函數（返回當前日期） 113
技巧 2 計算員工在職天數 .113
技巧 3 判斷借出圖書是否到期 114
技巧 4 判斷應收賬款是否到期 115
3．DATE 函數（返回指定日期的序列號） 116
技巧 5 建立倒計時顯示牌 .116
技巧 6 計算臨時工的工作天數 116
4．DAY 函數（返回某日期的天數） 118
技巧 7 計算本月上旬的出庫數量 .118
技巧 8 按本月缺勤天數計算缺勤扣款 119
技巧 9 顯示出全年中各月的天數 . 120
5．YEAR 函數（返回某日期中的年份） 121
技巧 10 計算出員工年齡 122
技巧 11 計算出員工工齡 123
6．WEEKDAY 函數（返回某日期為星期幾） .124
技巧 12 返回值班日期對應的星期數 124
技巧 13 判斷值班日期是平時加班還是雙休日加班 125
技巧 14 計算平常日與周末日的加班工資 126
7．MONTH 函數（返回某日期中的月份） 127
技巧 15 自動填寫銷售報表中的月份 127
技巧 16 計算本月賬款金額總計 . 128
8．EOMONTH 函數（返回某個月份最後一天的序列號） .129
技巧 17 根據活動開始日期計算各月活動天數 . 129
技巧 18 計算優惠券有效期的截止日期 130
技巧 19 在考勤表中根據當前月份自動建立日期序列 131
技巧 20 在考勤表中根據各日期自動返回對應的星期數 . 133
9．WEEKNUM 函數（返回指定日期是第幾周） 134
技巧 21 計算借書歷經周數 134
4.2 日期計算 135
10．DATEDIF 函數（計算兩個日期之間的年、月和天數） 135
技巧 22 計算總借款天數 136
技巧 23 根據員工工齡自動追加工齡工資 137
11．DAYS360 函數（計算兩日期間相差的天數） 138
技巧 24 計算還款剩餘天數 139
技巧 25 計算固定資產已使用月份 . 140
技巧 26 利用 DAYS360 函數判斷借款是否逾期 141
12．YEARFRAC 函數（計算兩日期間天數占全年天數的百分比） .142
技巧 27 計算年假占全年天數的百分比 142
13．WORKDAY 函數（獲取間隔若乾工作日後的日期） 143
技巧 28 根據休假天數計算休假結束日期 144
14．WORKDAY.INTL 函數 .144
技巧 29 根據休假天數計算休假結束日期（指定一天為法定假日） . 145
15．NETWORKDAYS 函數（計算兩個日期間的工作日） 146
技巧 30 計算兩個日期間的工作日 . 146
16．NETWORKDAYS.INTL 函數 .147
技巧 31 計算兩個日期間的工作日（指定只有周一為休息日） 149
17．EDATE 函數（計算與指定日期相隔指定月份數的日期） 149
技巧 32 根據賬齡計算應收賬款的到期日期 . 150
4.3 時 間 函 數 .151
18．HOUR 函數（返回某時間中的小時數） .151
技巧 33 計算登錄訪問的時間的區間 151
19．MINUTE 函數（返回某時間中的分鐘數） .152
技巧 34 計算停車時間 . 152
20．SECOND 函數（返回某時間中的秒數） .153
技巧 35 電腦器運行秒數 153
技巧 36 顯示高於或低於標準時間的值 . 154
4.4 日期與時間轉換 .156
21．DATEVALUE 函數（將文本日期轉換為可識別的日期序列號） .156
技巧 37 計算到某一指定日期截止的總天數 . 156
22．TIMEVALUE 函數（將時間轉換為對應的小數值） 157
技巧 38 根據下班打卡時間計算加班時間 158
第 5 章 數學函數範例 .160
5.1 求和及按條件求和函數 .160
1．SUM 函數（求和） .160
技巧 1 一次性對多列數據求和 . 160
技巧 2 根據銷售數量與單價計算總銷售額 161
技巧 3 只統計某兩個店鋪的合計金額 162
技巧 4 分奇偶行統計數據 163
2．SUMIF 函數（按條件求和） 164
技巧 5 按經辦人計算銷售金額 165
技巧 6 統計各部門工資總額 166
技巧 7 分別統計前半個月與後半個月的銷售額 . 166
技巧 8 用通配符對某一類數據求和 168
技巧 9 計算銷售金額前 3 名合計值 . 169
3．SUMIFS 函數（按多條件求和） 170
技巧 10 統計指定倉庫指定商品的出庫總數量 . 170
技巧 11 按月匯總出庫數量 172
技巧 12 多條件統計某一類數據總和 173
技巧 13 按不同性質統計應收款 . 174
4．SUMPRODUCT 函數（求多組數的乘積之和） .176
技巧 14 統計銷售部女員工人數 . 178
技巧 15 按月匯總出庫數量 179
技巧 16 統計出指定班級分數大於指定值的人數 180
技巧 17 統計出指定部門獲取獎金的人數（去除空值） . 181
技巧 18 統計出指定部門、指定職務的員工人數 . 182
技巧 19 統計非工作日銷售金額 183
技巧 20 分別統計 12 個月內賬款與超過 12 個月的賬款合計 . 184
技巧 21 統計某一時間段出現的次數 186
技巧 22 從學生檔案表中統計指定日期區間中指定性別的人數 187
5.2 數據舍入函數 188
5．INT 函數（向下取整） 188
技巧 23 計算平均銷售數量時取整數 188
6．ROUND 函數（四捨五入） 189
技巧 24 以 1 個百分點為單位計算獎金或扣款 . 190
7．ROUNDUP 函數（向上舍入） .191
技巧 25 以 1 個百分點為單位計算獎金（向上舍入） 191
技巧 26 使用 ROUNDUP 函數計算物品的快遞費用 192
8．ROUNDDOWN 函數（向下舍入） 193
技巧 27 以 1 個百分點為單位計算獎金（向下舍入） 193
技巧 28 根據給定時間界定整點範圍 194
9．TRUNC 函數（不考慮四捨五入截去數據小數部分） .195
技巧 29 計算銷售金額時取整或保留指定位數小數 195
10．CEILING 函數（舍入計算） .196
技巧 30 以 6 秒（不足 6 秒按 6 秒計算）為單位計算通話費用 . 197
11．FLOOR 函數（去尾舍入） .198
技巧 31 計件工資中的獎金計算 . 198
12．MROUND 函數（按指定倍數舍入） .200
技巧 32 計算商品運送車次 200
13．QUOTIENT 函數（返回商品的整數部分） 201
技巧 33 計算參加某活動的每組人數 . 202
5.3 階乘、隨機數計算函數 .202
14．FACT 函數（求指定正數值的階乘） .202
技巧 34 求指定正數值的階乘值 . 203
15．MULTINOMIAL 函數（計算指定數值階乘與各數值階乘乘積的比值） 203
技巧 35 計算指定數值和的階乘與各數值階乘乘積的比值 . 203
16．RAND 函數（返回一個大於或等於 0 且小於 1 的隨機數） .204
技巧 36 隨機獲取選手編號 205
技巧 37 自動生成彩票 7 位開獎號碼 . 205
17．RANDBETWEEN 函數（返回兩個數值間的隨機數） 206
技巧 38 自動隨機生成 3 位數編號 . 206
5.4 其他數據學運算函數 .207
18．ABS 函數（求絕對值） .207
技巧 39 比較今年銷售額與去年銷售額 . 207
技巧 40 計算支出金額總計值 208
19．MOD 函數（求兩個數相除後的餘數） .209
技巧 41 按奇數月與偶數月統計銷量 209
技巧 42 計算每位員工的加班時長 211
20．SQRT 函數（求算術平方根） 212
技巧 43 計算指定數值對應的算術平方根 212
21．GCD 函數（求最大公約數） 212
技巧 44 返回兩個或多個整數的最大公約數 . 213
22．LCM 函數（求最小公倍數） .213
技巧 45 計算兩個或多個整數的最小公倍數 . 214
23．POWER 函數（計算方根） 214
技巧 46 根據指定的底數和指數計算出方根值 . 215
第 6 章 統計函數範例 .216
6.1 平均值計算函數 .216
1．AVERAGE 函數（求平均值） 216
技巧 1 快速自動求平均值 216
技巧 2 在成績表中忽略 0 值求平均分 217
2．AVERAGEA 函數（求包括文本和邏輯值的平均值） .219
技巧 3 計算平均分時值將文本項也計算在內 . 219
技巧 4 統計各月份的平均銷售額（計算區域含文本值） . 220
3．AVERAGEIF 函數（按條件求平均值） 220
技巧 5 統計各班級平均分 221
技巧 6 計算月平均出庫數量 222
技巧 7 通配符模糊匹配求平均值 . 223
技巧 8 排除新店計算平均利潤 . 224
4．AVERAGEIFS 函數（按多條件求平均值） 225
技巧 9 計算一車間女職工平均工資 225
技巧 10 求介於某一區間內的平均值 226
技巧 11 統計指定店面所有男裝品牌的平均利潤 . 226
技巧 12 忽略 0 值求指定班級的平均分 228
5．GEOMEAN 函數（求幾何平均值） .228
技巧 13 比較兩種產品的銷售利潤的穩定性 . 229
6．TRIMMEAN 函數（去頭尾後求平均值） .230
技巧 14 通過 10 位評委打分計算選手的最後得分 . 230
6.2 條目數統計函數 .231
7．COUNT 函數（統計數目） .231
技巧 15 根據簽到表統計到會人數 . 231
技巧 16 統計各個部門獲取交通補貼的人數 . 232
技巧 17 統計其中一科得滿分的人數 233
8．COUNTA 函數（求包括文本和邏輯值的數目） 234
技巧 18 統計課程的總報名人數 . 234
技巧 19 統計非正常出勤的人數 . 235
9．COUNTIF 函數（按條件統計數目） 236
技巧 20 統計某課程的報名人數 . 236
技巧 21 統計工資大於或等於 5000 元的人數 236
技巧 22 按學歷統計人數 237
技巧 23 在成績表中分別統計及格人數與不及格人數 238
技巧 24 統計出成績大於平均分數的學生人數 . 239
技巧 25 統計同時在兩列數據中都出現的條目數 240
技巧 26 統計連續 3 次考試都進入前 10 名的人數 . 241
10．COUNTIFS 函數（按多條件統計數目） .242
技巧 27 統計指定部門銷量達標人數 242
技巧 28 統計各店面男裝的銷售記錄條數（雙條件） 243
技巧 29 統計指定商品每日的銷售記錄數 244
11．COUNTBLANK 函數（統計空單元格的數目） .245
技巧 30 統計缺考人數 245
6.3 最大值與最小值統計函數 246
12．MAX 函數與 MIN 函數 .246
技巧 31 快速返回數據區域中的最大值 246
技巧 32 計算單日銷售金額並返回最大值 247
12．MAXIFS（按條件求最大值） .248
技巧 33 返回企業女性員工的最大年齡 249
技巧 34 返回上半月單筆最高銷售金額 249
技巧 35 分別統計各班級第一名成績 250
12．MINIFS（按條件求最小值） .251
技巧 36 返回指定產品的最低報價 . 251
14．MAXA 函數與 MINA 函數（包含文本與邏輯值時求最大值） 252
技巧 37 返回最低利潤額（包含文本） 252
16．LARGE 函數（返回某一數據集中的某個最大值） .253
技巧 38 返回排名前 3 位的銷售金額 253
技巧 39 計算成績表中前 5 名的平均值 . 254
17．SMALL 函數（返回某一數據集中的某個最小值） .255
技巧 40 統計成績表中後 5 名的平均分 255
技巧 41 統計成績表中後 5 名的平均分（忽略 0 值） 256
18．MODE.MULT 函數 256
技巧 42 統計生產量抽樣數據中的眾數 257
6.4 排位統計函數 .258
19．RANK.EQ 函數（返回數字的排位） .258
技巧 43 為學生考試成績排名次 . 258
技巧 44 對不連續單元格排名次 259
20．RANK.AVG 函數（排位有相同名次時返回平均排位） 260
技巧 45 用 RANK.AVG 函數對銷售額排名 260
21．PERCENTRANK.INC 函數（返回數字的百分比排位） 261
技巧 46 將各月銷售利潤按百分比排位 . 261
22．MEDIAN 函數（返回數據集的中位數） .262
技巧 47 統計全年各月利潤值中的中位數 262
23．QUARTILE.INC 函數（返回數據集的四分位數） .263
技巧 48 統計一組身高數據的四分位數 264
24．PERCENTILE.INC 函數（返回一組數據的百分位數） .265
技巧 49 統計一組身高數據的 K 個百分點的值 . 265
6.5 方差、協方差與偏差 .267
25．VAR.S（計算基於樣本的方差） 267
技巧 50 估算產品質量的方差 268
26．VAR.P（計算基於樣本總體的方差） 268
技巧 51 以樣本值估算總體的方差 . 269
27．STDEV.S（計算基於樣本估算標準偏差） 269
技巧 52 估算入伍軍人身高的標準偏差 270
28．STDEV.P（計算樣本總體的標準偏差） 271
技巧 53 以樣本值估算總體的標準偏差 272
29．COVARIANCE.S（返回樣本協方差） .272
技巧 54 計算甲狀腺與碘食用量的協方差 273
30．COVARIANCE.P（返回總體協方差） .273
技巧 55 以樣本值估算總體的協方差 274
31．DEVSQ（返回平均值偏差的平方和） 274
技巧 56 計算零件質量系數的偏差平方和 275
32．AVEDEV 函數（計算數值的平均絕對偏差） .275
技巧 57 計算一種產品重量的平均絕對偏差 . 276
第 7 章 財務函數範例 .277
7.1 投資計算函數 277
1．PMT 函數（計算貸款每期付款額） .277
技巧 1 計算貸款的每年償還額 . 277
技巧 2 計算貸款的每月償還金額 278
2．IPMT 函數（計算每期償還額中的利息額） .278
技巧 3 計算貸款每年償還金額中的利息金額 279
技巧 4 計算貸款每月償還金額中的利息金額 279
3．PPMT 函數（計算本金） .280
技巧 5 計算貸款每年償還金額中的本金金額 . 281
技巧 6 計算貸款每月償還金額中的本金金額 . 281
4．ISPMT 函數（等額本金還款方式下的利息計算） .282
技巧 7 在等額本金還款方式下計算某貸款的利息 282
5．FV 函數（計算投資未來值） .283
技巧 8 計算分期存款的未來值 . 284
技巧 9 計算購買某項保險的未來值 284
6．FVSCHEDULE 函數（投資在變動或可調利率下的未來值） .285
技巧 10 計算某項整存整取存款的未來值 . 285
7．PV 函數（計算投資現值） 285
技巧 11 判斷購買某項保險是否合算 286
8．NPV 函數（計算投資凈現值） 286
技巧 12 計算某投資的凈現值 287
9．XNPV 函數（計算一組不定期現金流的凈現值） 288
技巧 13 計算出一組不定期盈利額的凈現值 . 289
10．NPER 函數（計算投資期數） .289
技巧 14 計算出貸款的清還年數 . 289
技巧 15 計算出某項投資的投資期數 290
7.2 償還率計算函數 .290
11．IRR 函數（計算一組現金流的內部收益率） 290
技巧 16 計算某項投資的內部收益率 291
12．XIRR 函數（計算一組不定期現金流的內部收益率） .291
技巧 17 計算出一組不定期盈利額的內部收益率 292
13．MIRR 函數（計算修正內部收益率） 292
技巧 18 計算某項投資的修正內部收益率 292
14．RATE 函數（計算年金每個期間的利率） .293
技巧 19 計算購買某項保險的收益率 . 294
7.3 資產折舊計算函數 294
15．SLN 函數（直線法） .294
技巧 20 用直線法計算出固定資產的每年折舊額 294
技巧 21 用直線法計算出固定資產的每月折舊額 295
16．SYD 函數（年數總和法） 296
技巧 22 用年數總和法計算出固定資產的每年折舊額 296
17．DB 函數（固定餘額遞減法） 297
技巧 23 用固定餘額遞減法計算出固定資產的每年折舊額 . 297
18．DDB 函數（雙倍餘額遞減法） 298
技巧 24 用雙倍餘額遞減法計算出固定資產的每年折舊額 . 298
19．VDB 函數（計算部分期間內的折舊值） 299
技巧 25 計算出固定資產部分期間的設備折舊值 299
20．AMORDEGRC 函數（指定會計期間的折舊值） .301
技巧 26 計算指定會計期間的折舊值 . 301
第 8 章 查找和引用函數範例 .302
8.1 查找函數 .302
1．CHOOSE 函數（從參數列表中選擇並返回一個值） .302
技巧 1 配合 IF 函數找出短跑成績的前三名 . 302
技巧 2 根據產品不合格率決定產品處理辦法 303
2．LOOKUP 函數（向量型）（按條件查找並返回值） 304
技巧 3 根據產品編碼查詢庫存數量（向量型語法） 305
3．LOOKUP 函數（數組型）（按條件查找並返回值） 306
技巧 4 根據產品編碼查詢庫存數量（數組型語法） 307
技巧 5 按姓名查詢學生的各科目成績 308
4．HLOOKUP 函數（橫向查找） 310
技巧 6 根據不同的返利率計算各筆訂單的返利金額 310
技巧 7 快速查詢任意科目的成績序列 .311
5．VLOOKUP 函數（縱向查找） 313
技巧 8 產品備案表中查詢各產品單價 314
技巧 9 將多張工作表中的數據合並到一張工作表中 315
技巧 10 根據多條件計算員工年終獎 317
技巧 11 使用 VLOOKUP 函數進行反向查詢 318
技巧 12 查找並返回符合條件的多條記錄 319
6．MATCH 函數 +INDEX 函數（MATCH 查找並返回找到值所在位置，
INDEX 函數返回指定位置的值） 323
技巧 13 瞭解 MATCH 函數與 INDEX 函數 . 323
技巧 14 查找任意指定銷售員的銷售總金額（單條件查找） 325
技巧 15 查找指定月份指定專櫃的銷售金額（雙條件查找） 326
技巧 16 返回成績最高的學生的姓名 327
技巧 17 返回多次短跑中用時最短的編號 328
技巧 18 查找遲到次數最多的員工 . 329
8.2 引用函數 330
7．ADDRESS 函數（建立文本類型單元格的地址） 330
技巧 19 查找最大銷售額所在位置 330
8．COLUMN 函數（返回引用的列號） .331
技巧 20 在一行中快速輸入月份 . 332
技巧 21 實現隔列求總銷售金額 . 332
9．COLUMNS 函數（返回引用的列數） .334
技巧 22 返回參與考試的科目數量 334
10．ROW 函數（返回引用的行號） .334
技巧 23 讓數據自動隔 4 行（自定義）加 1（自定義） . 335
技巧 24 提取季度合計值計算全年銷售額 . 336
技巧 25 根據借款期限返回相應的年數序列 337
11．ROWS 函數（返回引用的行數） 338
技巧 26 統計銷售記錄條數 338
技巧 27 判斷值班人員是否重復 . 339
12．INDIRECT 函數（返回指定的引用） 340
技巧 28 解決合並單元格引用數據列出現跳躍的問題 340
技巧 29 按指定的範圍計算平均值 . 342
技巧 30 INDIRECT 解決跨工作表查詢時名稱匹配問題 . 344
13．OFFSET 函數（根據指定偏移量得到新引用） 345
技巧 31 實現數據的動態查詢 346
技巧 32 對每日出庫量累計求和 . 347
技巧 33 OFFSET 常用於創建動態圖表數據源 . 348
第 9 章 信息函數範例 .351
9.1 使用 IS 函數進行判斷 351
1．ISBLANK 函數（判斷值是否為空值） .351
技巧 1 標註出缺考學生 351
2．ISTEXT 函數（判斷數據是否為文本） .352
技巧 2 快速統計缺考人數 352
3．ISLOGICAL 函數（判斷數據是否為邏輯值） .353
技巧 3 檢驗數據是否為邏輯值 . 353
4．ISNUMBER 函數（判斷數據是否為數字） 353
技巧 4 當出現無法計算時檢測數據是否是數值數據 354
5．ISNA 函數（判斷數據是否為錯誤值“#N/A”） 354
技巧 5 查詢編號錯誤時顯示“無此編號” 355
6．ISERR 函數（檢測給定值是否為“#N/A”以外的錯誤值） 356
技巧 6 檢驗數據是否為錯誤值 #N/A 356
7．ISERROR（檢測給定值是否為任意錯誤值） .357
技巧 7 忽略錯誤值進行求和運算 357
8．ISODD 函數（判斷數據是否為奇數） 358
技巧 8 根據身份證號碼判斷其性別 358
9．ISEVEN 函數（判斷數據是否為偶數） 359
技巧 9 根據工號返回性別信息 359
9.2 獲取相關信息函數 .360
10．CELL 函數（返回單元格、位置等） .360
技巧 10 獲得正在選取的單元格地址 361
技巧 11 獲得當前文件的路徑、路徑和工作表名 362
技巧 12 分辨日期和數字 362
技巧 13 判斷測試結果是否達標 . 364
11．ERROR.TYPE 函數（返回錯誤對應的編號） 364
技巧 14 根據錯誤代碼顯示錯誤原因 365
12．TYPE 函數（返回數值類型） 366
技巧 15 測試數據是否是數值型 . 366
13．N 函數（返回轉換為數值後的值） 367
技巧 16 用訂單生成日期的序列號與當前行號生成定單的編號 367
第 10 章 數據庫函數範例 369
10.1 常 規 統 計 .369
1．DSUM 函數（從數據庫中按給定條件求和） .369
技巧 1 計算指定經辦人的訂單總金額 369
技巧 2 計算上半月中指定名稱產品的總銷售額（滿足雙條件）. 370
技巧 3 計算總工資時去除某一個（或多個）部門 . 371
技巧 4 使用通配符實現利潤求和統計 372
技巧 5 解決模糊匹配造成統計錯誤問題 372
2．DAVERAGE 函數（從數據庫中按給定條件求平均值） .374
技巧 6 計算指定班級平均分 374
技巧 7 計算指定車間指定性別員工的平均工資（雙條件） 375
技巧 8 實現對各科目平均成績查詢 . 376
3．DCOUNT 函數（從數據庫中按給定條件統計記錄條數） 377
技巧 9 計算指定車間、指定性別員工的人數 . 377
技巧 10 統計出指定班級分數大於指定值的人數 378
技巧 11 統計出指定性別測試合格的人數 . 379
技巧 12 統計記錄條數時使用通配符 380
4．DCOUNTA 函數（從數據庫中按給定條件統計非空單元格數目） .381
技巧 13 統計業務水平為“好”的人數 . 381
技巧 14 統計出指定性別測試合格的人數 . 382
技巧 15 按條件統計來訪總人數（使用通配符） 382
5．DMAX 函數（從數據庫中按給定條件求最大值） 383
技巧 16 返回指定車間指定性別員工的最高工資 384
技巧 17 實現查詢各科目成績中的最高分 384
6．DMIN 函數（從數據庫中按給定條件求最小值） 386
技巧 18 返回指定班級的最低分 386
技巧 19 實現查詢各科目成績中的最低分 387
7．DGET 函數（從數據庫中提取符合條件的單個值） 388
技巧 20 在列表或數據庫中按條件查詢 . 388
8．DPRODUCT 函數（從數據庫中返回滿足指定條件的數值的乘積） .389
技巧 21 判斷指定類別與品牌的商品是否被維修過 390
10.2 方差、標準差計算 .391
9．DVAR 函數（按條件通過樣本估算總體方差） 391
技巧 22 計算指定機器生產零件直徑的總體方差 392
10．DVARP 函數（按條件計算樣本的方差）392
技巧 23 計算指定機器生產零件直徑的樣本總體方差 393
11．DSTDEV 函數（按條件通過樣本估算總體標準偏差） .394
技巧 24 計算不同性別身高數據的總體標準偏差 394
12．DSTDEVP 函數（按條件計算樣本的標準偏差） .395
技巧 25 計算不同性別身高數據的樣本總體標準偏差 396
第 11 章 用公式設置單元格格式及限制數據輸入 397
11.1 函數在條件格式中的應用 397
技巧 1 次日值班人員自動提醒 . 397
技巧 2 自動標識周末日期 399
技巧 3 比較兩個單元格採購價格是否相同 400
技巧 4 將成績大於指定分數的標註為“優” . 401
技巧 5 突出顯示“缺考”或未填寫數據的單元格 403
技巧 6 滿足條件的整行突出顯示 1 404
技巧 7 滿足條件的整行突出顯示 2 405
技巧 8 突出顯示每行的最高與最低分 . 406
技巧 9 加班時長最長的員工特殊顯示 408
11.2 函數在數據有效性中的應用 410
技巧 10 避免輸入重復值 410
技巧 11 禁止出庫數量大於庫存數 .411
技巧 12 禁止輸入文本值 412
技巧 13 限制輸入的數據必須小於兩位 413
技巧 14 禁止輸入空格 414
技巧 15 禁止錄入不完整的產品規格 415
技巧 16 設置單元格輸入必須包含指定內容 . 416
技巧 17 限制數據輸入的長度 417
附錄 Excel 函數與公式問題集 418
1. 公式運算問題 418
問題 1 想復制公式卻找不到填充柄了 418
問題 2 利用公式計算時不顯示計算結果只顯示公式 419
問題 3 兩個日期相減時不能得到差值天數，卻返回一個日期值 420
問題 4 更改了數據源的值，公式的計算結果並不自動更新是什麽原因 421
問題 5 公式引用單元格明明顯示的是數據，計算結果卻為 0 421
問題 6 數字與“空”單元格，結果卻報錯 . 422
問題 7 新輸入的行中不能自動填充上一行的公式 423
問題 8 LOOKUP 查找總是找不到正確結果 . 424
問題 9 VLOOKUP 查找時，查找內容與查找區域首列內容不精確匹配， 有辦法實現查找嗎？ . 425
問題 10 在設置按條件求和（按條件計數等）函數的 criteria（用於條件判斷的） 參數時，如何處理條件判斷問題 . 426
問題 11 解決浮點運算造成 ROUND 函數計算不準確的問題 . 427
2．公式返回錯誤值問題 428
問題 12 公式返回“#DIV>0!”錯誤值 （“0”值或空白單元格被作為了除數） . 428
問題 13 公式返回“#N>A”錯誤值 （公式中引用的數據源不正確或不能使用） . 429
問題 14 公式返回“#NAME?”錯誤值 1 （輸入的函數和名稱拼寫錯誤） . 430
問題 15 公式返回“#NAME?”錯誤值 2 （公式中使用文本作為參數時未加雙引號） 430
問題 16 公式返回“#NAME?”錯誤值 3 （在公式中使用了未定義的名稱） . 431
問題 17 公式返回“#NAME?”錯誤值 4 （引用其他工作表時工作表名稱包含空格） 432
問題 18 公式返回“#NUM!”錯誤值（引用了無效的參數） . 433
問題 19 公式返回“#VALUE!”錯誤值 1 （公式中將文本類型的數據參與了數值運算） 433
問題 20 公式返回“#VALUE!”錯誤值 2 （公式中函數使用的參數與語法不一致） 434
問題 21 公式返回“#VALUE!”錯誤值 3 （數組運算未按 Shift+Ctrl+Enter 組合鍵結束） 435
問題 22 公式返回“#REF!”錯誤值 （公式計算中引用了無效的單元格） 435
3．Excel 應用中的其他常見問題 .436
問題 23 滑動鼠標中鍵向下查看數據時，工作表中的內容卻隨之進行縮放 436
問題 24 要輸入一串產品代碼，按下 Enter 鍵後顯示為科學計數方式的數字 . 437
問題 25 填充序號時不能自動遞增 . 438
問題 26 填充時間時為何不能按分鐘數（秒數）遞增 439
問題 27 向單元格中輸入數據總是彈出對話框 . 440
問題 28 對於文本型數字，為它應用了“數值”格式後怎麽還是沒有變成數值 （無法計算） 441
問題 29 在 Excel 中編輯時按 Enter 鍵無法換行 441
問題 30 在 Excel 中查找時總是無法精確找到 . 442
問題 31 添加了“自動篩選”後，日期值卻不能自動分組篩選 443
