高效隨身查——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