高效隨身查：Excel 2021必學的函數(shù)與公式應用技巧（視頻教學版）

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