《Excel函式與公式套用技巧》全書共12章，第1章講解了公式編輯與數據源引用必備技巧，後面依次以函式功能劃分章節，按函式實用程度，主次分明。第12章還列出了Excel函式與公式使用中的常見問題集，為讀者提供查閱參考。
基本介紹
- 書名：Excel函式與公式套用技巧
- 作者：賽貝爾資訊
- ISBN：9787302353492
- 定價：29.80元
- 出版社：清華大學出版社
- 出版時間：2014.07.01
圖書信息,內容簡介,前言,目錄,
圖書信息
內容簡介
《Excel函式與公式套用技巧》全書共12章，第1章講解了公式編輯與數據源引用必備技巧，後面依次以函式功能劃分章節，按函式實用程度，主次分明。第12章還列出了Excel函式與公式使用中的常見問題集，為讀者提供查閱參考。
本書適合辦公人員、文秘人員作為隨身速查手冊。
前言
一、這是一本什麼樣的書？
1．著重於解決日常疑難問題和提高工作效率：與市場上很多同類圖書不同，本書不是全面系統講述工具使用，而是點對點地快速解決日常辦公、電腦使用中的疑難和技巧，著重幫助提高工作效率的。
2．注意解決一類問題，讓讀者觸類旁通：日常工作問題可能很多，各有不同，事事列舉問題既非常繁雜也無必要，本書在選擇問題時注意選擇一類問題，給出思路、方法和套用擴展，方便讀者觸類旁通。
3．套用技巧詳盡、豐富：本書選擇了幾百個套用技巧，足夠滿足日常辦公、電腦維護方面的工作套用。
4．圖解方式，一目了然：讀圖時代，大家都需要緩解壓力，本書圖解的方式可以讓讀者學起來毫不費力。
二、這本書是寫給誰看？
1．想成為職場"白骨精"的小A：高效、幹練，企事業單位的主力骨幹，白領中的精英，高效辦公是必需的！
2．想乾點"更重要"的事的小B：日常辦公耗費了不少時間，其實掌握點技巧，可節省2/3的時間！去乾點個人發展的事更重要啊。
3．想獲得領導認可的文秘小C：把工作及時、高效、保質保量做好，讓領導滿意，最好掌握點辦公絕活。
4．想早早下班逗兒子的小D：人生苦短，莫使金樽空對月，享受生活是小D的人生追求，一天的事情半天搞定，滿足小D早早回家陪兒子的願望。
5．不善於求人的小E：事事求人，給人的感覺好像很謙虛，但有時候也可能顯得自己很笨，所以小E這類人，還是自己多學兩招。
目錄
第1章 公式編輯與數據源引用必備技巧 1
1.1 公式的輸入與編輯 1
技巧1 查看長公式中某一步的計算結果 1
技巧2 查看公式引用的所有單元格 2
技巧3 將公式中的部分公式轉換為數值 2
技巧4 將公式運算結果轉換為數值 2
技巧5 快速複製公式的方法 3
技巧6 大範圍複製公式 3
技巧7 跳過非空單元格批量建立公式 4
技巧8 函式參數的省略與簡寫 6
技巧9 快速查找和學習某函式用法 6
技巧10 用函式螢幕提示工具輔助建立公式 8
技巧11 保留沒有輸入完整的公式 9
技巧12 隱藏公式實現保護 10
1.2 公式中數據源的引用 11
技巧13 混合引用並向右複製公式 11
技巧14 混合引用並向下複製公式 12
技巧15 引用當前工作表之外的單元格 13
技巧16 引用多工作表的同一單元格計算 15
技巧17 引用其他工作簿中的數據源 16
技巧18 快速切換單元格引用類型 17
技巧19 引用定義的名稱創建公式 18
技巧20 將公式定義為名稱 18
技巧21 讓定義的名稱只套用於當前工作表 19
技巧22 查看當前工作簿中定義的所有名稱 20
技巧23 修改定義的名稱 21
技巧24 刪除不再使用的名稱 21
1.3 公式檢測與審核 22
技巧25 查看當前工作表中的所有公式 22
技巧26 追蹤引用單元格 23
技巧27 追蹤從屬單元格 23
技巧28 公式查錯與監視 24
技巧29 通過“公式求值”功能逐步分解公式 25
第2章 邏輯函式範例 27
1.AND函式（檢驗一組數據是否都滿足條件） 27
技巧30 考評學生的各門課程是否全部及格 27
技巧31 一次性判斷一組數據是否都滿足條件 28
2.NOT函式（對所給參數求反） 29
技巧32 篩選掉小於某一年齡的應聘人員 29
3.OR函式（檢驗一組數據是否有一個滿足條件） 30
技巧33 檢驗員工是否通過考評 30
4.IF函式（根據條件判斷真假） 31
技巧34 利用IF函式進行金額校驗 32
技巧35 根據不同返利比計算返利金額 32
技巧36 根據業務處理量判斷員工業務水平 33
技巧37 庫存量過小時提示補貨 35
技巧38 分性別判斷成績是否合格 37
技巧39 根據消費卡類別與消費額派發贈品 38
技巧40 根據職工性別和職務判斷退休年齡 39
技巧41 根據年齡與學歷對應聘人員二次篩選 41
技巧42 根據商品的名稱與顏色進行一次性調價 42
技巧43 根據3項業務的完成率計算綜合完成率 43
技巧44 比較兩個採購部門的採購價格 45
技巧45 計算個人所得稅 46
5.IFERROR函式（根據條件判斷真假） 49
技巧46 當被除數為空值（或0值）時返回“計算數據源有錯誤”文字 49
第3章 文本函式範例 50
3.1 返回文本內容 50
1.CONCATENATE函式（合併多個字元） 50
技巧47 合併商品貨號碼數及顏色 50
技巧48 提取任意有用數據併合並 51
2.LEFT函式（從最左側開始提取指定數目的字元） 52
技巧49 從E—mail地址中提取賬號 52
技巧50 從商品全稱中提取產地信息 53
3.LEFTB函式（按位元組數從最左側提取指定數目的字元） 54
技巧51 根據產品編號提取類別 54
4.RIGHT函式（提取從最右側開始的指定數目的字元） 55
技巧52 將電話號碼的區號與號碼分離開 56
技巧53 從右側提取字元並自動轉換為數值 57
技巧54 發票金額的分列填寫 58
5.RIGHTB函式（按位元組數從最右側提取指定數目的字元） 61
技巧55 提取產品的規格數據 61
6.MID函式（從指定位置開始提取字元） 62
技巧56 從規格數據中提取部分數據 63
技巧57 提取產品的貨號 63
7.MIDB函式（按位元組數從指定位置開始提取字元） 64
技巧58 從房號數據中提取單元號 65
8.LEN函式（返回字元串的字元數） 66
技巧59 當輸入的身份證位數錯誤時給出提示 66
技巧60 從身份證號碼中提取出生年份 67
技巧61 從身份證號碼中提取性別 68
技巧62 配合多函式從身份證號碼中提取完整的出生年月日 69
9.REPT函式（重複文本） 71
技巧63 輸入身份證號碼填寫框 71
技巧64 根據銷售額用“★”評定等級 72
10.TRIM函式（清除空格） 73
技巧65 刪除文本單詞中多餘的空格 73
3.2 查找與替換文本 73
11.EXACT函式（比較兩個字元串是否相同） 73
技巧66 比較兩個店鋪的平均售價是否相同 74
12.FIND函式（查找指定字元並返回其位置） 75
技巧67 查找品名所在位置並提取 75
技巧68 問卷調查時實現自動統計答案 76
13.REPLACE函式（替換字元中的部分字元） 78
技巧69 禁止中獎手機號碼的後幾位數 78
14.SEARCH函式（查找字元並返回其起始位置） 79
技巧70 從項目編碼中提取契約號 79
15.SUBSTITUTE函式（用新字元替換舊字元） 81
技巧71 去掉報表中多餘的空格 81
技巧72 查找特定文本且將第一次出現的刪除，其他保留 82
技巧73 嵌套使用SUBSTITUTE函式返回公司名稱簡稱 82
技巧74 根據報名學員統計人數 84
3.3 轉換文本格式 85
16.FIXED函式（按指定的小數位數進行取整） 85
技巧75 解決因四捨五入而造成的顯示誤差問題 85
17.WIDECHAR函式（半角字母轉換為全形字母） 86
技巧76 將半角英文字母轉換為全形英文字母 86
18.LOWER函式（大寫字母轉換為小寫字母） 87
技巧77 將大寫字母轉換為小寫字母 87
19.PROPER函式（將首字元轉換為大寫） 88
技巧78 一次性將每個單詞的首字母轉換為大寫 88
20.RMB函式（四捨五入並轉換為貨幣格式） 88
技巧79 將銷售額轉換為人民幣格式 89
21.TEXT函式（將數值轉換為指定格式的文本） 89
技巧80 合併編碼時不忽略前面的090
技巧81 讓合併的日期顯示正確格式 91
技巧82 讓計算得到金額顯示為“餘款：15，850.00”形式 92
22.VALUE函式（將文本型數字轉換成數值型數字） 92
技巧83 解決總金額無法計算的問題 93
第4章 日期與時間函式範例 94
4.1 日期函式 94
1.NOW函式（返回當前日期時間） 94
技巧84 計算員工在職天數 94
2.TODAY函式（返回當前日期） 95
技巧85 判斷應收賬款是否到期 95
3.DATE函式（返回指定日期的序列號） 96
技巧86 建立倒計時顯示牌 96
4.DAY函式（返回某日期的天數） 97
技巧87 顯示出全年中各月的天數 97
技巧88 計算本月上旬的銷售總額 98
5.YEAR函式（返回某日期中的年份） 99
技巧89 計算出員工年齡 99
技巧90 計算出員工工齡 100
6.DATEDIF函式（計算兩個日期之間的年、月和天數） 101
技巧91 計算總借款天數 102
技巧92 計算借款至今日的時長 103
技巧93 根據員工工齡自動追加工齡工資 104
7.DAYS360函式（計算兩日期間相差的天數） 105
技巧94 計算還款剩餘天數 105
技巧95 計算固定資產已使用時間 106
技巧96 利用DAYS360函式判斷借款是否逾期 107
8.WEEKDAY函式（返回某日期為星期幾） 108
技巧97 返回值班日期對應的星期數 108
技巧98 判斷值班日期是平時加班還是雙休日加班 109
9.MONTH函式（返回某日期中的月份） 110
技巧99 自動填寫銷售報表中的月份 111
技巧100 計算本月賬款金額總計 111
10.EOMONTH函式（返回某個月份最後一天的序列號） 112
技巧101 根據活動開始日期計算各月活動天數 112
技巧102 根據當前月份自動顯示本月所有日期序列 113
技巧103 自動返回各日期對應的星期數 115
11.WORKDAY函式（計算工作日） 116
技巧104 根據休假天數自動顯示出休假結束日期 117
12.NETWORKDAYS函式（去除指定日期之後的工作日數） 117
技巧105 計算國慶節到元旦節之間的工作日 118
技巧106 計算年假占全年工作日的百分比 118
13.WEEKNUM函式（返回指定日期是第幾周） 119
技巧107 快速得知2013年中各節日在第幾周 120
14.YEARFRAC函式（計算兩日期間天數占全年天數的百分比） 120
技巧108 計算各地點工作時間點全年天數百分比 121
4.2 時間函式 121
15.HOUR函式（返回某時間中的小時數） 121
技巧109 計算來訪簽到時間的區間 122
16.MINUTE函式（返回某時間中的分鐘數） 122
技巧110 計算停車時間 123
17.SECOND函式（返回某時間中的秒數） 123
技巧111 計算通話秒數 124
技巧112 顯示高於或低於標準時間的值 124
18.DATEVALUE函式（將文本日期轉換為可識別的日期序列號） 126
技巧113 計算借款天數 126
19.TIMEVALUE函式（將時間轉換為對應的小數值） 127
技巧114 將指定時間轉換為時間小數值 127
第5章 數學函式範例 128
5.1 常規數學計算函式 128
1.SUM函式（求和） 128
技巧115 根據各月份預算費用一步計算總預算費用 128
技巧116 根據銷售數量與單價計算總銷售額 129
技巧117 按經辦人計算銷售金額 129
技巧118 統計銷售部女員工人數 130
技巧119 只計算某兩個產品的合計金額 131
技巧120 按月匯總出庫數量 132
技巧121 統計大於12個月的賬款 133
2.SUMIF函式（按條件求和） 135
技巧122 統計各部門工資總額 135
技巧123 分別統計前半個月與後半個月的銷售額 136
技巧124 用通配符對某一類數據求和 137
技巧125 計算銷售金額前3名合計值 138
技巧126 解決計算結果為0的問題 139
3.SUMIFS函式（按多條件求和） 140
技巧127 統計總銷售額時滿足指定類別指定時間 140
技巧128 多條件統計某一類數據總和 141
技巧129 按不同性質統計應收款 142
4.SUMPRODUCT函式（求多組數的乘積之和） 144
技巧130 統計出指定班級分數大於指定值的人數 145
技巧131 統計出指定部門獲取獎金的人數（去除空值） 145
技巧132 統計出某兩種商品的銷售金額 146
技巧133 統計出指定部門、指定職務的員工人數 147
技巧134 分奇偶行統計數據 147
技巧135 分單位統計賬齡 148
技巧136 統計某一時間段出現的次數 151
技巧137 統計指定日期區間中指定性別的人數 152
技巧138 統計非工作日銷售金額 153
5.ABS函式（求絕對值） 154
技巧139 比較今年銷售額與去年銷售額 154
技巧140 計算支出金額總計值 155
6.MOD函式（求兩個數相除後的餘數） 156
技巧141 按奇數月與偶數月統計銷量 156
技巧142 計算每位員工的加班時長 158
7.SUBTOTAL函式（返回分類匯總的結果） 159
技巧143 在工資統計表中得出多種匯總方式的結果 159
8.SQRT函式（求算術平方根） 161
技巧144 計算指定數值對應的算術平方根 162
9.GCD函式（求最大公約數） 162
技巧145 返回兩個或多個整數的最大公約數 162
10.LCM函式（求最低公倍數） 163
技巧146 計算兩個或多個整數的最低公倍數 163
11.POWER函式（計算方根） 164
技巧147 根據指定的底數和指數計算出方根值 164
5.2 捨入計算函式 165
12.INT函式（向下取整） 165
技巧148 計算平均銷售數量時取整數 165
13.TRUNC函式（截去數據小數部分） 166
技巧149 計算銷售金額時取整或保留指定位數小數 166
14.CEILING函式（捨入計算） 167
技巧150 以6秒（不足6秒按6秒計算）為單位計算通話費用 167
15.ROUND函式（四捨五入） 169
技巧151 以1個百分點為單位計算獎金或扣款 169
16.ROUNDUP函式（向上捨入） 171
技巧152 使用ROUNDUP函式計算物品的快遞費用 171
技巧153 計算上網費用 172
17.ROUNDDOWN函式（向下捨入） 173
技巧154 根據給定時間界定整點範圍 174
18.FLOOR函式（去尾捨入） 175
技巧155 計件工資中的獎金計算 175
19.MROUND函式（按指定倍數捨入） 177
技巧156 計算商品運送車次 177
20.QUOTIENT函式（返回數字相除後的整數） 178
技巧157 計算參加某活動的每組人數 178
5.3 階乘、隨機數計算函式 179
21.FACT函式（求指定正數值的階乘） 179
技巧158 求指定正數值的階乘值 179
22.MULTINOMIAL函式（計算指定數值階乘與各數值階乘乘積的比值） 179
技巧159 計算指定數值和的階乘與各數值階乘乘積的比值 180
23.RAND函式（返回一個大於等於0小於1的隨機數） 180
技巧160 隨機獲取選手編號 181
技巧161 自動生成彩票7位開獎號碼 181
24.RANDBETWEEN函式（返回兩個數值間的隨機數） 182
技巧162 自動隨機生成三位數編碼 182
第6章 統計函式範例 183
6.1 平均值計算函式 183
1.AVERAGE函式（求平均值） 183
技巧163 在成績表中忽略0值求平均分 183
技巧164 統計各班級平均分 184
技巧165 計算一車間女職工平均工資 185
技巧166 提取部分數據作為關鍵字以統計學生平均成績 186
2.AVERAGEA函式（求包括文本和邏輯值的平均值） 187
技巧167 計算平均分時將“缺考”的也計算在內 187
技巧168 統計各月份的平均銷售額（計算區域含文本值） 188
3.AVERAGEIF函式（按條件求平均值） 188
技巧169 計算月平均出庫數量 189
技巧170 求二次平均值 190
技巧171 排除新店計算平均利潤 190
4.AVERAGEIFS函式（按多條件求平均值） 191
技巧172 求介於某一區間內的平均值 191
技巧173 統計指定店面所有男裝品牌的平均利潤 192
技巧174 忽略0值求指定班級的平均分 193
5.GEOMEAN函式（求幾何平均值） 193
技巧175 比較兩種產品的銷售利潤的穩定性 194
6.TRIMMEAN函式（去頭尾後求平均值） 195
技巧176 通過10位評審打分計算選手的最後得分 195
6.2 數目統計函式 196
7.COUNT函式（統計數目） 196
技巧177 統計出故障的機器台數 196
技巧178 統計學生成績及格率 196
技巧179 統計其中一科得滿分的人數 198
8.COUNTA函式（求包括文本和邏輯值的數目） 198
技巧180 統計參與某項課程的總人數 199
9.COUNTIF函式（按條件統計數目） 199
技巧181 統計工資大於3000元的人數 200
技巧182 統計出某兩門課程的報名人數 200
技巧183 統計出成績大於平均分數的學生人數 201
技巧184 統計是三好學生且參加數學競賽的人數 201
技巧185 統計連續3次考試都進入前10名的人數 202
10.COUNTIFS函式（按多條件統計數目） 203
技巧186 統計各店面男裝的銷售記錄條數（雙條件） 203
技巧187 統計指定時間指定類別商品的銷售記錄數 204
技巧188 統計指定類型指定影院指定時間的影片放映數量 205
技巧189 統計指定產品每日的銷售記錄數 206
11.COUNTBLANK函式（統計空單元格的數目） 207
技巧190 統計缺考人數 207
6.3 最大值與最小值統計函式 208
12.MAX函式（求最大值） 208
技巧191 返回上半個月單筆最高銷售金額 208
技巧192 返回企業女性員工的最大年齡 209
技巧193 計算單日銷售金額並返回最大值 210
13.MIN函式（求最小值） 211
技巧194 忽略0值求最低分數 211
技巧195 返回200米跑中用時最短的次數編號 212
14.MAXA函式（包含文本與邏輯值時求最大值） 212
技巧196 返回成績表中的最高分數（包含文本） 213
15.MINA函式（包含文本與邏輯值時求最小值） 213
技巧197 返回最低利潤額（包含文本） 213
16.LARGE函式（返回某一數據集中的某個最大值） 214
技巧198 返回排名前3位的銷售金額 215
技巧199 計算成績表中前5名的平均值 215
技巧200 分別統計各班級第一名成績 216
17.SMALL函式（返回某一數據集中的某個最小值） 217
技巧201 統計成績表中後5名的平均分 217
技巧202 統計成績表中後5名的平均分（忽略0值） 217
18.MODE函式 218
技巧203 返回被投訴次數最多的客服的編號 219
6.4 排位統計函式 219
19.RANK.EQ函式（返回數字的排位） 219
技巧204 為學生考試成績排名次 220
技巧205 對不連續單元格排名次 220
20.RANK.AVG函式（排位有相同名次時返回平均排位） 221
技巧206 用RANK.AVG函式對銷售額排名 221
21.PERCENTILE.INC 函式（返回數值指定百分點的值） 222
技巧207 返回數值區域的K百分比數值點 222
22.PERCENTRANK.INC函式（返回數字的百分比排位） 224
技巧208 將各月銷售利潤按百分比排位 224
23.MEDIAN函式（返回數據集的中位數） 225
技巧209 統計全年各月利潤值中的中位數 225
24.QUARTILE.INC函式（返回數據集的四分位數） 226
技巧210 從全年各月銷售利潤數據集中求四分位數 226
第7章 財務函式範例 228
7.1 投資計算函式 228
1.PMT函式（計算貸款每期付款額） 228
技巧211 計算貸款的每年償還額 228
技巧212 計算貸款的每月償還額 229
2.IPMT函式（計算每期償還額中的利息額） 229
技巧213 計算貸款每年償還金額中有多少是利息 230
技巧214 計算貸款每月償還金額中有多少是利息 231
3.PPMT函式（計算本金） 231
技巧215 計算貸款每年償還金額中有多少是本金 232
技巧216 計算貸款每月償還金額中有多少是本金 232
4.ISPMT函式（等額本金還款方式下的利息計算） 233
技巧217 在等額本金還款方式下計算某貸款的利息 233
5.FV函式（計算投資未來值） 234
技巧218 計算分期存款的未來值 235
技巧219 計算購買某項保險的未來值 235
6.FVSCHEDULE函式（投資在變動或可調利率下的未來值） 236
技巧220 計算某項整存整取存款的未來值 236
7.PV函式（計算投資現值） 237
技巧221 判斷購買某項保險是否合算 237
8.NPV函式（計算投資淨現值） 238
技巧222 計算某投資的淨現值 238
9.XNPV函式（計算一組不定期現金流的淨現值） 240
技巧223 計算出一組不定期盈利額的淨現值 240
10.NPER函式（計算投資期數） 240
技巧224 計算出貸款的清還年數 241
技巧225 計算出某項投資的投資期數 241
7.2 償還率計算函式 242
11.IRR函式（計算一組現金流的內部收益率） 242
技巧226 計算某項投資的內部收益率 242
12.XIRR函式（計算一組不定期現金流的內部收益率） 243
技巧227 計算出一組不定期盈利額的內部收益率 243
13.MIRR函式（計算修正內部收益率） 244
技巧228 計算某項投資的修正內部收益率 244
14.RATE函式（計算年金每個期間的利率） 245
技巧229 計算購買某項保險的收益率 246
7.3 資產折舊計算函式 246
15.SLN函式（直線法） 246
技巧230 用直線法計算出固定資產的每年折舊額 247
技巧231 用直線法計算出固定資產的每月折舊額 247
16.SYD函式（年數總和法） 248
技巧232 用年數總和法計算出固定資產的每年折舊額 248
17.DB函式（固定餘額遞減法） 249
技巧233 用固定餘額遞減法計算出固定資產的每年折舊額 249
18.DDB函式（雙倍餘額遞減法） 250
技巧234 用雙倍餘額遞減法計算出固定資產的每年折舊額 250
19.VDB函式（計算部分期間內的折舊值） 251
技巧235 計算出固定資產部分期間的設備折舊值 252
20.AMORDEGRC函式（指定會計期間的折舊值） 253
技巧236 計算指定會計期間的折舊值 254
第8章 查找和引用函式範例 255
8.1 查找數據函式 255
1.CHOOSE函式（返回指定的值） 255
技巧237 在成績表中查詢任意學生任意科目的成績 255
技巧238 根據產品不合格率決定產品處理辦法 257
2.LOOKUP函式（向量型）（按條件查找並返回值） 258
技巧239 根據產品編號查詢庫存數量（向量型語法） 259
技巧240 返回銷售筆數最多的員工的姓名 259
3.LOOKUP函式（數組型）（按條件查找並返回值） 260
技巧241 根據產品編號查詢庫存數量（數組型語法） 260
4.HLOOKUP函式（橫向查找） 261
技巧242 根據不同的返利率計算各筆訂單的返利金額 261
技巧243 快速查詢任意科目的成績序列 263
5.VLOOKUP函式（縱向查找） 265
技巧244 在銷售表中自動返回產品單價 265
技巧245 將多張工作表中的數據合併到一張工作表中 267
技巧246 根據多條件計算員工年終獎 269
技巧247 使用VLOOKUP函式進行反向查詢 270
6.MATCH函式（查找並返回找到值所在位置） 271
技巧248 返回成績最高的學生的姓名 272
技巧249 查找遲到次數最多的學生 273
7.INDEX函式（返回指定位置的值） 274
技巧250 按姓名查詢學生的各科目成績 274
技巧251 查找指定月份指定專櫃的銷售金額 277
技巧252 查詢最高總金額所對應的店鋪（反向查詢） 278
技巧253 羅列出指定店面的所有銷售記錄 279
8.2 引用數據函式 282
8.ADDRESS函式（建立文本類型單元格的地址） 282
技巧254 查找最大銷售額所在位置 283
9.COLUMN函式（返回引用的列號） 284
技巧255 在一行中快速輸入月份 284
技巧256 實現隔列求總銷售金額 285
10.COLUMNS函式（返回引用的列數） 286
技巧257 返回參與考試的科目數量 286
11.ROW函式（返回引用的行號） 287
技巧258 讓數據自動隔4行（自定義）加1（自定義） 287
技巧259 提取季度合計值計算全年銷售額 288
技巧260 根據借款期限返回相應的年數序列 289
12.ROWS函式（返回引用的行數） 290
技巧261 統計銷售記錄條數 291
技巧262 判斷值班人員是否重複 291
13.INDIRECT函式（返回指定的引用） 292
技巧263 解決合併單元格引用數據列出現跳躍的問題 293
技巧264 按指定的範圍計算平均值 294
14.OFFSET函式（根據指定偏移量得到新引用） 296
技巧265 實現數據的動態查詢 297
技巧266 對每日出庫量累計求和 298
技巧267 指定時間段的進出庫合計統計 299
15.TRANSPOSE函式（行列轉置） 301
技巧268 將表格中的行列標識項相互轉置 301
第9章 信息函式範例 303
9.1 使用IS函式進行判斷 303
1.ISBLANK函式（判斷值是否為空值） 303
技巧269 標註出缺考學生 303
2.ISTEXT函式（判斷數據是否為文本） 304
技巧270 快速統計缺考人數 304
3.ISLOGICAL函式（判斷數據是否為邏輯值） 304
技巧271 檢驗數據是否為邏輯值 305
4.ISNUMBER函式（判斷數據是否為數字） 305
技巧272 統計實考人數 305
技巧273 統計銷售量總計值 306
5.ISNA函式（判斷數據是否為錯誤值“#N/A”） 307
技巧274 檢驗數據是否為“#N/A”錯誤值 307
6.ISERR函式（判斷數據是否為“#N/A”之外任意錯誤值） 307
技巧275 檢驗數據是否為“#N/A”之外的任何錯誤值 308
7.ISODD函式（判斷數據是否為奇數） 308
技巧276 根據身份證號碼判斷其性別 308
8.ISEVEN函式（判斷數據是否為偶數） 309
技巧277 根據身份證號碼判斷其性別 309
9.2 返回相關值 310
9.CELL函式（返回單元格、位置等） 310
技巧278 判斷測試結果是否達標 311
10.ERROR.TYPE函式（返回錯誤對應的編號） 311
技巧279 根據錯誤代碼顯示錯誤原因 312
11.N函式（返迴轉換為數字後的值） 313
技巧280 用訂單生成日期的序列號與當前行號生成定單的編號 313
12.TYPE函式（返回數值類型） 314
技巧281 返回數值對應的類型數字 314
第10章 數學庫函式範例 315
10.1 常規統計 315
1.DSUM函式（從資料庫中按給定條件求和） 315
技巧282 計算指定經辦人的訂單總金額 315
技巧283 計算上半月中指定名稱產品的總銷售額（滿足雙條件） 316
技巧284 計算總工資時去除某一個（或多個）部門 316
技巧285 使用通配符實現利潤求和統計 317
技巧286 解決模糊匹配造成統計錯誤問題 318
2.DAVERAGE函式（從資料庫中按給定條件求平均值） 319
技巧287 計算指定班級平均分 320
技巧288 計算指定車間指定性別員工的平均工資（雙條件） 320
技巧289 實現對各科目平均成績查詢 321
3.DCOUNT函式（從資料庫中按給定條件統計記錄條數） 322
技巧290 統計成績小於60分的人數（忽略0值） 322
技巧291 統計出指定性別工資大於2500元的人數 323
技巧292 統計記錄條數時使用通配符 324
4.DCOUNTA函式（從資料庫中按給定條件統計非空單元格數目） 324
技巧293 統計業務水平為“好”的人數 325
技巧294 統計出指定性別測試合格的人數 325
技巧295 按條件統計來訪總人數（使用通配符） 326
5.DMAX函式（從資料庫中按給定條件求最大值） 326
技巧296 返回指定車間指定性別員工的最高工資 327
技巧297 實現查詢各科目成績中的最高分 327
6.DMIN函式（從資料庫中按給定條件求最小值） 328
技巧298 返回指定班級的最低分 329
技巧299 實現查詢各科目成績中的最低分 329
7.DGET函式（從資料庫中提取符合條件的單個值） 330
技巧300 在列表或資料庫中按條件查詢 331
8.DPRODUCT函式（從資料庫中返回滿足指定條件的數值的乘積） 332
技巧301 判斷指定類別與品牌的商品是否被維修過 332
10.2 方差、標準差計算 333
9.DSTDEV函式（按條件計算樣本標準偏差） 333
技巧302 計算女性員工工齡的樣本標準偏差 334
10.DSTDEVP函式（按條件計算總體標準偏差） 334
技巧303 計算女性員工工齡的總體標準偏差 335
11.DVAR函式（按條件計算樣本總體方差） 335
技巧304 計算女性員工工齡的樣本總體方差 336
12.DVARP函式（按條件計算總體方差） 336
技巧305 計算女性員工工齡的總體方差 337
第11章 用公式設定單元格格式及限制數據輸入 338
11.1 函式在條件格式中的套用 338
技巧306 工作表自動隔行填色 338
技巧307 突出顯示前3名 339
技巧308 自動標識周末日期 341
技巧309 標識出不同的採購價格 342
技巧310 為不包含某一特定字元的單元格設定格式 343
技巧311 標識出重複值班的員工 344
技巧312 將成績高於平均值的標註為“優” 345
技巧313 突出顯示“缺考”或未填寫數據的單元格 346
11.2 函式在數據有效性中的套用 347
技巧314 避免輸入重複的產品編碼 347
技巧315 提示出貨數量大於進貨數量 348
技巧316 避免輸入文本值 349
技巧317 設定D列中只能輸入女性員工姓名 350
技巧318 禁止錄入不完整的產品規格 351
技巧319 設定單元格輸入必須包含指定內容 352
技巧320 限制數據輸入的長度 353
第12章 Excel函式與公式使用常見問題集 355
技巧321 想複製公式卻找不到填充柄了 355
技巧322 為什麼在計算結果的單元格中始終顯示公式 355
技巧323 如何取消Excel表格中的計算公式 356
技巧324 解決公式中計算結果無法自動更新的問題 357
技巧325 如何定位和取消循環引用 357
技巧326 完成合併計算後，原表數據更新後的結果不能同步更新 358
技巧327 計算工齡時，計算結果顯示“1900—1—6”形式 358
技巧328 解決條件為文本計算結果為0的問題 359
技巧329 解決浮點運算造成ROUND函式計算不準確的問題 359
技巧330 表格中有很多計算公式導致運行速度慢，有辦法解決嗎 360
技巧331 新輸入的行中不能自動填充上一行的公式 361
技巧332 合併單元格中公式的複製 362
技巧333 公式返回“#DIV>0！”錯誤值（“0”值或空白單元格被作為了除數） 363
技巧334 公式返回“#N>A”錯誤值1（公式中省略了函式中必不可少的參數） 364
技巧335 公式返回“#N>A”錯誤值2（公式中引用的數據源不正確或不能使用） 365
技巧336 公式返回“#NAME？”錯誤值1（輸入的函式和名稱拼寫錯誤） 365
技巧337 公式返回“#NAME？”錯誤值2（公式中使用文本作為參數時未加雙引號） 366
技巧338 公式返回“#NAME？”錯誤值3（在公式中使用了未定義的名稱） 367
技巧339 公式返回“#NAME？”錯誤值4（引用其他工作表時工作表名稱包含空格） 367
技巧340 公式返回“#NUM！”錯誤值（引用了無效的參數） 368
技巧341 公式返回“#VALUE！”錯誤值1（公式中將文本類型的數據參與了數值運算）368
技巧342 公式返回“#VALUE！”錯誤值2（公式中函式使用的參數與語法不一致）369
技巧343 公式返回“#VALUE！”錯誤值3（數組運算未按“Shift+Ctrl+Enter”鍵結束）369
技巧344 公式返回“#REF！”錯誤值（公式計算中引用了無效的單元格） 370
1.1 公式的輸入與編輯 1
技巧1 查看長公式中某一步的計算結果 1
技巧2 查看公式引用的所有單元格 2
技巧3 將公式中的部分公式轉換為數值 2
技巧4 將公式運算結果轉換為數值 2
技巧5 快速複製公式的方法 3
技巧6 大範圍複製公式 3
技巧7 跳過非空單元格批量建立公式 4
技巧8 函式參數的省略與簡寫 6
技巧9 快速查找和學習某函式用法 6
技巧10 用函式螢幕提示工具輔助建立公式 8
技巧11 保留沒有輸入完整的公式 9
技巧12 隱藏公式實現保護 10
1.2 公式中數據源的引用 11
技巧13 混合引用並向右複製公式 11
技巧14 混合引用並向下複製公式 12
技巧15 引用當前工作表之外的單元格 13
技巧16 引用多工作表的同一單元格計算 15
技巧17 引用其他工作簿中的數據源 16
技巧18 快速切換單元格引用類型 17
技巧19 引用定義的名稱創建公式 18
技巧20 將公式定義為名稱 18
技巧21 讓定義的名稱只套用於當前工作表 19
技巧22 查看當前工作簿中定義的所有名稱 20
技巧23 修改定義的名稱 21
技巧24 刪除不再使用的名稱 21
1.3 公式檢測與審核 22
技巧25 查看當前工作表中的所有公式 22
技巧26 追蹤引用單元格 23
技巧27 追蹤從屬單元格 23
技巧28 公式查錯與監視 24
技巧29 通過“公式求值”功能逐步分解公式 25
第2章 邏輯函式範例 27
1.AND函式（檢驗一組數據是否都滿足條件） 27
技巧30 考評學生的各門課程是否全部及格 27
技巧31 一次性判斷一組數據是否都滿足條件 28
2.NOT函式（對所給參數求反） 29
技巧32 篩選掉小於某一年齡的應聘人員 29
3.OR函式（檢驗一組數據是否有一個滿足條件） 30
技巧33 檢驗員工是否通過考評 30
4.IF函式（根據條件判斷真假） 31
技巧34 利用IF函式進行金額校驗 32
技巧35 根據不同返利比計算返利金額 32
技巧36 根據業務處理量判斷員工業務水平 33
技巧37 庫存量過小時提示補貨 35
技巧38 分性別判斷成績是否合格 37
技巧39 根據消費卡類別與消費額派發贈品 38
技巧40 根據職工性別和職務判斷退休年齡 39
技巧41 根據年齡與學歷對應聘人員二次篩選 41
技巧42 根據商品的名稱與顏色進行一次性調價 42
技巧43 根據3項業務的完成率計算綜合完成率 43
技巧44 比較兩個採購部門的採購價格 45
技巧45 計算個人所得稅 46
5.IFERROR函式（根據條件判斷真假） 49
技巧46 當被除數為空值（或0值）時返回“計算數據源有錯誤”文字 49
第3章 文本函式範例 50
3.1 返回文本內容 50
1.CONCATENATE函式（合併多個字元） 50
技巧47 合併商品貨號碼數及顏色 50
技巧48 提取任意有用數據併合並 51
2.LEFT函式（從最左側開始提取指定數目的字元） 52
技巧49 從E—mail地址中提取賬號 52
技巧50 從商品全稱中提取產地信息 53
3.LEFTB函式（按位元組數從最左側提取指定數目的字元） 54
技巧51 根據產品編號提取類別 54
4.RIGHT函式（提取從最右側開始的指定數目的字元） 55
技巧52 將電話號碼的區號與號碼分離開 56
技巧53 從右側提取字元並自動轉換為數值 57
技巧54 發票金額的分列填寫 58
5.RIGHTB函式（按位元組數從最右側提取指定數目的字元） 61
技巧55 提取產品的規格數據 61
6.MID函式（從指定位置開始提取字元） 62
技巧56 從規格數據中提取部分數據 63
技巧57 提取產品的貨號 63
7.MIDB函式（按位元組數從指定位置開始提取字元） 64
技巧58 從房號數據中提取單元號 65
8.LEN函式（返回字元串的字元數） 66
技巧59 當輸入的身份證位數錯誤時給出提示 66
技巧60 從身份證號碼中提取出生年份 67
技巧61 從身份證號碼中提取性別 68
技巧62 配合多函式從身份證號碼中提取完整的出生年月日 69
9.REPT函式（重複文本） 71
技巧63 輸入身份證號碼填寫框 71
技巧64 根據銷售額用“★”評定等級 72
10.TRIM函式（清除空格） 73
技巧65 刪除文本單詞中多餘的空格 73
3.2 查找與替換文本 73
11.EXACT函式（比較兩個字元串是否相同） 73
技巧66 比較兩個店鋪的平均售價是否相同 74
12.FIND函式（查找指定字元並返回其位置） 75
技巧67 查找品名所在位置並提取 75
技巧68 問卷調查時實現自動統計答案 76
13.REPLACE函式（替換字元中的部分字元） 78
技巧69 禁止中獎手機號碼的後幾位數 78
14.SEARCH函式（查找字元並返回其起始位置） 79
技巧70 從項目編碼中提取契約號 79
15.SUBSTITUTE函式（用新字元替換舊字元） 81
技巧71 去掉報表中多餘的空格 81
技巧72 查找特定文本且將第一次出現的刪除，其他保留 82
技巧73 嵌套使用SUBSTITUTE函式返回公司名稱簡稱 82
技巧74 根據報名學員統計人數 84
3.3 轉換文本格式 85
16.FIXED函式（按指定的小數位數進行取整） 85
技巧75 解決因四捨五入而造成的顯示誤差問題 85
17.WIDECHAR函式（半角字母轉換為全形字母） 86
技巧76 將半角英文字母轉換為全形英文字母 86
18.LOWER函式（大寫字母轉換為小寫字母） 87
技巧77 將大寫字母轉換為小寫字母 87
19.PROPER函式（將首字元轉換為大寫） 88
技巧78 一次性將每個單詞的首字母轉換為大寫 88
20.RMB函式（四捨五入並轉換為貨幣格式） 88
技巧79 將銷售額轉換為人民幣格式 89
21.TEXT函式（將數值轉換為指定格式的文本） 89
技巧80 合併編碼時不忽略前面的090
技巧81 讓合併的日期顯示正確格式 91
技巧82 讓計算得到金額顯示為“餘款：15，850.00”形式 92
22.VALUE函式（將文本型數字轉換成數值型數字） 92
技巧83 解決總金額無法計算的問題 93
第4章 日期與時間函式範例 94
4.1 日期函式 94
1.NOW函式（返回當前日期時間） 94
技巧84 計算員工在職天數 94
2.TODAY函式（返回當前日期） 95
技巧85 判斷應收賬款是否到期 95
3.DATE函式（返回指定日期的序列號） 96
技巧86 建立倒計時顯示牌 96
4.DAY函式（返回某日期的天數） 97
技巧87 顯示出全年中各月的天數 97
技巧88 計算本月上旬的銷售總額 98
5.YEAR函式（返回某日期中的年份） 99
技巧89 計算出員工年齡 99
技巧90 計算出員工工齡 100
6.DATEDIF函式（計算兩個日期之間的年、月和天數） 101
技巧91 計算總借款天數 102
技巧92 計算借款至今日的時長 103
技巧93 根據員工工齡自動追加工齡工資 104
7.DAYS360函式（計算兩日期間相差的天數） 105
技巧94 計算還款剩餘天數 105
技巧95 計算固定資產已使用時間 106
技巧96 利用DAYS360函式判斷借款是否逾期 107
8.WEEKDAY函式（返回某日期為星期幾） 108
技巧97 返回值班日期對應的星期數 108
技巧98 判斷值班日期是平時加班還是雙休日加班 109
9.MONTH函式（返回某日期中的月份） 110
技巧99 自動填寫銷售報表中的月份 111
技巧100 計算本月賬款金額總計 111
10.EOMONTH函式（返回某個月份最後一天的序列號） 112
技巧101 根據活動開始日期計算各月活動天數 112
技巧102 根據當前月份自動顯示本月所有日期序列 113
技巧103 自動返回各日期對應的星期數 115
11.WORKDAY函式（計算工作日） 116
技巧104 根據休假天數自動顯示出休假結束日期 117
12.NETWORKDAYS函式（去除指定日期之後的工作日數） 117
技巧105 計算國慶節到元旦節之間的工作日 118
技巧106 計算年假占全年工作日的百分比 118
13.WEEKNUM函式（返回指定日期是第幾周） 119
技巧107 快速得知2013年中各節日在第幾周 120
14.YEARFRAC函式（計算兩日期間天數占全年天數的百分比） 120
技巧108 計算各地點工作時間點全年天數百分比 121
4.2 時間函式 121
15.HOUR函式（返回某時間中的小時數） 121
技巧109 計算來訪簽到時間的區間 122
16.MINUTE函式（返回某時間中的分鐘數） 122
技巧110 計算停車時間 123
17.SECOND函式（返回某時間中的秒數） 123
技巧111 計算通話秒數 124
技巧112 顯示高於或低於標準時間的值 124
18.DATEVALUE函式（將文本日期轉換為可識別的日期序列號） 126
技巧113 計算借款天數 126
19.TIMEVALUE函式（將時間轉換為對應的小數值） 127
技巧114 將指定時間轉換為時間小數值 127
第5章 數學函式範例 128
5.1 常規數學計算函式 128
1.SUM函式（求和） 128
技巧115 根據各月份預算費用一步計算總預算費用 128
技巧116 根據銷售數量與單價計算總銷售額 129
技巧117 按經辦人計算銷售金額 129
技巧118 統計銷售部女員工人數 130
技巧119 只計算某兩個產品的合計金額 131
技巧120 按月匯總出庫數量 132
技巧121 統計大於12個月的賬款 133
2.SUMIF函式（按條件求和） 135
技巧122 統計各部門工資總額 135
技巧123 分別統計前半個月與後半個月的銷售額 136
技巧124 用通配符對某一類數據求和 137
技巧125 計算銷售金額前3名合計值 138
技巧126 解決計算結果為0的問題 139
3.SUMIFS函式（按多條件求和） 140
技巧127 統計總銷售額時滿足指定類別指定時間 140
技巧128 多條件統計某一類數據總和 141
技巧129 按不同性質統計應收款 142
4.SUMPRODUCT函式（求多組數的乘積之和） 144
技巧130 統計出指定班級分數大於指定值的人數 145
技巧131 統計出指定部門獲取獎金的人數（去除空值） 145
技巧132 統計出某兩種商品的銷售金額 146
技巧133 統計出指定部門、指定職務的員工人數 147
技巧134 分奇偶行統計數據 147
技巧135 分單位統計賬齡 148
技巧136 統計某一時間段出現的次數 151
技巧137 統計指定日期區間中指定性別的人數 152
技巧138 統計非工作日銷售金額 153
5.ABS函式（求絕對值） 154
技巧139 比較今年銷售額與去年銷售額 154
技巧140 計算支出金額總計值 155
6.MOD函式（求兩個數相除後的餘數） 156
技巧141 按奇數月與偶數月統計銷量 156
技巧142 計算每位員工的加班時長 158
7.SUBTOTAL函式（返回分類匯總的結果） 159
技巧143 在工資統計表中得出多種匯總方式的結果 159
8.SQRT函式（求算術平方根） 161
技巧144 計算指定數值對應的算術平方根 162
9.GCD函式（求最大公約數） 162
技巧145 返回兩個或多個整數的最大公約數 162
10.LCM函式（求最低公倍數） 163
技巧146 計算兩個或多個整數的最低公倍數 163
11.POWER函式（計算方根） 164
技巧147 根據指定的底數和指數計算出方根值 164
5.2 捨入計算函式 165
12.INT函式（向下取整） 165
技巧148 計算平均銷售數量時取整數 165
13.TRUNC函式（截去數據小數部分） 166
技巧149 計算銷售金額時取整或保留指定位數小數 166
14.CEILING函式（捨入計算） 167
技巧150 以6秒（不足6秒按6秒計算）為單位計算通話費用 167
15.ROUND函式（四捨五入） 169
技巧151 以1個百分點為單位計算獎金或扣款 169
16.ROUNDUP函式（向上捨入） 171
技巧152 使用ROUNDUP函式計算物品的快遞費用 171
技巧153 計算上網費用 172
17.ROUNDDOWN函式（向下捨入） 173
技巧154 根據給定時間界定整點範圍 174
18.FLOOR函式（去尾捨入） 175
技巧155 計件工資中的獎金計算 175
19.MROUND函式（按指定倍數捨入） 177
技巧156 計算商品運送車次 177
20.QUOTIENT函式（返回數字相除後的整數） 178
技巧157 計算參加某活動的每組人數 178
5.3 階乘、隨機數計算函式 179
21.FACT函式（求指定正數值的階乘） 179
技巧158 求指定正數值的階乘值 179
22.MULTINOMIAL函式（計算指定數值階乘與各數值階乘乘積的比值） 179
技巧159 計算指定數值和的階乘與各數值階乘乘積的比值 180
23.RAND函式（返回一個大於等於0小於1的隨機數） 180
技巧160 隨機獲取選手編號 181
技巧161 自動生成彩票7位開獎號碼 181
24.RANDBETWEEN函式（返回兩個數值間的隨機數） 182
技巧162 自動隨機生成三位數編碼 182
第6章 統計函式範例 183
6.1 平均值計算函式 183
1.AVERAGE函式（求平均值） 183
技巧163 在成績表中忽略0值求平均分 183
技巧164 統計各班級平均分 184
技巧165 計算一車間女職工平均工資 185
技巧166 提取部分數據作為關鍵字以統計學生平均成績 186
2.AVERAGEA函式（求包括文本和邏輯值的平均值） 187
技巧167 計算平均分時將“缺考”的也計算在內 187
技巧168 統計各月份的平均銷售額（計算區域含文本值） 188
3.AVERAGEIF函式（按條件求平均值） 188
技巧169 計算月平均出庫數量 189
技巧170 求二次平均值 190
技巧171 排除新店計算平均利潤 190
4.AVERAGEIFS函式（按多條件求平均值） 191
技巧172 求介於某一區間內的平均值 191
技巧173 統計指定店面所有男裝品牌的平均利潤 192
技巧174 忽略0值求指定班級的平均分 193
5.GEOMEAN函式（求幾何平均值） 193
技巧175 比較兩種產品的銷售利潤的穩定性 194
6.TRIMMEAN函式（去頭尾後求平均值） 195
技巧176 通過10位評審打分計算選手的最後得分 195
6.2 數目統計函式 196
7.COUNT函式（統計數目） 196
技巧177 統計出故障的機器台數 196
技巧178 統計學生成績及格率 196
技巧179 統計其中一科得滿分的人數 198
8.COUNTA函式（求包括文本和邏輯值的數目） 198
技巧180 統計參與某項課程的總人數 199
9.COUNTIF函式（按條件統計數目） 199
技巧181 統計工資大於3000元的人數 200
技巧182 統計出某兩門課程的報名人數 200
技巧183 統計出成績大於平均分數的學生人數 201
技巧184 統計是三好學生且參加數學競賽的人數 201
技巧185 統計連續3次考試都進入前10名的人數 202
10.COUNTIFS函式（按多條件統計數目） 203
技巧186 統計各店面男裝的銷售記錄條數（雙條件） 203
技巧187 統計指定時間指定類別商品的銷售記錄數 204
技巧188 統計指定類型指定影院指定時間的影片放映數量 205
技巧189 統計指定產品每日的銷售記錄數 206
11.COUNTBLANK函式（統計空單元格的數目） 207
技巧190 統計缺考人數 207
6.3 最大值與最小值統計函式 208
12.MAX函式（求最大值） 208
技巧191 返回上半個月單筆最高銷售金額 208
技巧192 返回企業女性員工的最大年齡 209
技巧193 計算單日銷售金額並返回最大值 210
13.MIN函式（求最小值） 211
技巧194 忽略0值求最低分數 211
技巧195 返回200米跑中用時最短的次數編號 212
14.MAXA函式（包含文本與邏輯值時求最大值） 212
技巧196 返回成績表中的最高分數（包含文本） 213
15.MINA函式（包含文本與邏輯值時求最小值） 213
技巧197 返回最低利潤額（包含文本） 213
16.LARGE函式（返回某一數據集中的某個最大值） 214
技巧198 返回排名前3位的銷售金額 215
技巧199 計算成績表中前5名的平均值 215
技巧200 分別統計各班級第一名成績 216
17.SMALL函式（返回某一數據集中的某個最小值） 217
技巧201 統計成績表中後5名的平均分 217
技巧202 統計成績表中後5名的平均分（忽略0值） 217
18.MODE函式 218
技巧203 返回被投訴次數最多的客服的編號 219
6.4 排位統計函式 219
19.RANK.EQ函式（返回數字的排位） 219
技巧204 為學生考試成績排名次 220
技巧205 對不連續單元格排名次 220
20.RANK.AVG函式（排位有相同名次時返回平均排位） 221
技巧206 用RANK.AVG函式對銷售額排名 221
21.PERCENTILE.INC 函式（返回數值指定百分點的值） 222
技巧207 返回數值區域的K百分比數值點 222
22.PERCENTRANK.INC函式（返回數字的百分比排位） 224
技巧208 將各月銷售利潤按百分比排位 224
23.MEDIAN函式（返回數據集的中位數） 225
技巧209 統計全年各月利潤值中的中位數 225
24.QUARTILE.INC函式（返回數據集的四分位數） 226
技巧210 從全年各月銷售利潤數據集中求四分位數 226
第7章 財務函式範例 228
7.1 投資計算函式 228
1.PMT函式（計算貸款每期付款額） 228
技巧211 計算貸款的每年償還額 228
技巧212 計算貸款的每月償還額 229
2.IPMT函式（計算每期償還額中的利息額） 229
技巧213 計算貸款每年償還金額中有多少是利息 230
技巧214 計算貸款每月償還金額中有多少是利息 231
3.PPMT函式（計算本金） 231
技巧215 計算貸款每年償還金額中有多少是本金 232
技巧216 計算貸款每月償還金額中有多少是本金 232
4.ISPMT函式（等額本金還款方式下的利息計算） 233
技巧217 在等額本金還款方式下計算某貸款的利息 233
5.FV函式（計算投資未來值） 234
技巧218 計算分期存款的未來值 235
技巧219 計算購買某項保險的未來值 235
6.FVSCHEDULE函式（投資在變動或可調利率下的未來值） 236
技巧220 計算某項整存整取存款的未來值 236
7.PV函式（計算投資現值） 237
技巧221 判斷購買某項保險是否合算 237
8.NPV函式（計算投資淨現值） 238
技巧222 計算某投資的淨現值 238
9.XNPV函式（計算一組不定期現金流的淨現值） 240
技巧223 計算出一組不定期盈利額的淨現值 240
10.NPER函式（計算投資期數） 240
技巧224 計算出貸款的清還年數 241
技巧225 計算出某項投資的投資期數 241
7.2 償還率計算函式 242
11.IRR函式（計算一組現金流的內部收益率） 242
技巧226 計算某項投資的內部收益率 242
12.XIRR函式（計算一組不定期現金流的內部收益率） 243
技巧227 計算出一組不定期盈利額的內部收益率 243
13.MIRR函式（計算修正內部收益率） 244
技巧228 計算某項投資的修正內部收益率 244
14.RATE函式（計算年金每個期間的利率） 245
技巧229 計算購買某項保險的收益率 246
7.3 資產折舊計算函式 246
15.SLN函式（直線法） 246
技巧230 用直線法計算出固定資產的每年折舊額 247
技巧231 用直線法計算出固定資產的每月折舊額 247
16.SYD函式（年數總和法） 248
技巧232 用年數總和法計算出固定資產的每年折舊額 248
17.DB函式（固定餘額遞減法） 249
技巧233 用固定餘額遞減法計算出固定資產的每年折舊額 249
18.DDB函式（雙倍餘額遞減法） 250
技巧234 用雙倍餘額遞減法計算出固定資產的每年折舊額 250
19.VDB函式（計算部分期間內的折舊值） 251
技巧235 計算出固定資產部分期間的設備折舊值 252
20.AMORDEGRC函式（指定會計期間的折舊值） 253
技巧236 計算指定會計期間的折舊值 254
第8章 查找和引用函式範例 255
8.1 查找數據函式 255
1.CHOOSE函式（返回指定的值） 255
技巧237 在成績表中查詢任意學生任意科目的成績 255
技巧238 根據產品不合格率決定產品處理辦法 257
2.LOOKUP函式（向量型）（按條件查找並返回值） 258
技巧239 根據產品編號查詢庫存數量（向量型語法） 259
技巧240 返回銷售筆數最多的員工的姓名 259
3.LOOKUP函式（數組型）（按條件查找並返回值） 260
技巧241 根據產品編號查詢庫存數量（數組型語法） 260
4.HLOOKUP函式（橫向查找） 261
技巧242 根據不同的返利率計算各筆訂單的返利金額 261
技巧243 快速查詢任意科目的成績序列 263
5.VLOOKUP函式（縱向查找） 265
技巧244 在銷售表中自動返回產品單價 265
技巧245 將多張工作表中的數據合併到一張工作表中 267
技巧246 根據多條件計算員工年終獎 269
技巧247 使用VLOOKUP函式進行反向查詢 270
6.MATCH函式（查找並返回找到值所在位置） 271
技巧248 返回成績最高的學生的姓名 272
技巧249 查找遲到次數最多的學生 273
7.INDEX函式（返回指定位置的值） 274
技巧250 按姓名查詢學生的各科目成績 274
技巧251 查找指定月份指定專櫃的銷售金額 277
技巧252 查詢最高總金額所對應的店鋪（反向查詢） 278
技巧253 羅列出指定店面的所有銷售記錄 279
8.2 引用數據函式 282
8.ADDRESS函式（建立文本類型單元格的地址） 282
技巧254 查找最大銷售額所在位置 283
9.COLUMN函式（返回引用的列號） 284
技巧255 在一行中快速輸入月份 284
技巧256 實現隔列求總銷售金額 285
10.COLUMNS函式（返回引用的列數） 286
技巧257 返回參與考試的科目數量 286
11.ROW函式（返回引用的行號） 287
技巧258 讓數據自動隔4行（自定義）加1（自定義） 287
技巧259 提取季度合計值計算全年銷售額 288
技巧260 根據借款期限返回相應的年數序列 289
12.ROWS函式（返回引用的行數） 290
技巧261 統計銷售記錄條數 291
技巧262 判斷值班人員是否重複 291
13.INDIRECT函式（返回指定的引用） 292
技巧263 解決合併單元格引用數據列出現跳躍的問題 293
技巧264 按指定的範圍計算平均值 294
14.OFFSET函式（根據指定偏移量得到新引用） 296
技巧265 實現數據的動態查詢 297
技巧266 對每日出庫量累計求和 298
技巧267 指定時間段的進出庫合計統計 299
15.TRANSPOSE函式（行列轉置） 301
技巧268 將表格中的行列標識項相互轉置 301
第9章 信息函式範例 303
9.1 使用IS函式進行判斷 303
1.ISBLANK函式（判斷值是否為空值） 303
技巧269 標註出缺考學生 303
2.ISTEXT函式（判斷數據是否為文本） 304
技巧270 快速統計缺考人數 304
3.ISLOGICAL函式（判斷數據是否為邏輯值） 304
技巧271 檢驗數據是否為邏輯值 305
4.ISNUMBER函式（判斷數據是否為數字） 305
技巧272 統計實考人數 305
技巧273 統計銷售量總計值 306
5.ISNA函式（判斷數據是否為錯誤值“#N/A”） 307
技巧274 檢驗數據是否為“#N/A”錯誤值 307
6.ISERR函式（判斷數據是否為“#N/A”之外任意錯誤值） 307
技巧275 檢驗數據是否為“#N/A”之外的任何錯誤值 308
7.ISODD函式（判斷數據是否為奇數） 308
技巧276 根據身份證號碼判斷其性別 308
8.ISEVEN函式（判斷數據是否為偶數） 309
技巧277 根據身份證號碼判斷其性別 309
9.2 返回相關值 310
9.CELL函式（返回單元格、位置等） 310
技巧278 判斷測試結果是否達標 311
10.ERROR.TYPE函式（返回錯誤對應的編號） 311
技巧279 根據錯誤代碼顯示錯誤原因 312
11.N函式（返迴轉換為數字後的值） 313
技巧280 用訂單生成日期的序列號與當前行號生成定單的編號 313
12.TYPE函式（返回數值類型） 314
技巧281 返回數值對應的類型數字 314
第10章 數學庫函式範例 315
10.1 常規統計 315
1.DSUM函式（從資料庫中按給定條件求和） 315
技巧282 計算指定經辦人的訂單總金額 315
技巧283 計算上半月中指定名稱產品的總銷售額（滿足雙條件） 316
技巧284 計算總工資時去除某一個（或多個）部門 316
技巧285 使用通配符實現利潤求和統計 317
技巧286 解決模糊匹配造成統計錯誤問題 318
2.DAVERAGE函式（從資料庫中按給定條件求平均值） 319
技巧287 計算指定班級平均分 320
技巧288 計算指定車間指定性別員工的平均工資（雙條件） 320
技巧289 實現對各科目平均成績查詢 321
3.DCOUNT函式（從資料庫中按給定條件統計記錄條數） 322
技巧290 統計成績小於60分的人數（忽略0值） 322
技巧291 統計出指定性別工資大於2500元的人數 323
技巧292 統計記錄條數時使用通配符 324
4.DCOUNTA函式（從資料庫中按給定條件統計非空單元格數目） 324
技巧293 統計業務水平為“好”的人數 325
技巧294 統計出指定性別測試合格的人數 325
技巧295 按條件統計來訪總人數（使用通配符） 326
5.DMAX函式（從資料庫中按給定條件求最大值） 326
技巧296 返回指定車間指定性別員工的最高工資 327
技巧297 實現查詢各科目成績中的最高分 327
6.DMIN函式（從資料庫中按給定條件求最小值） 328
技巧298 返回指定班級的最低分 329
技巧299 實現查詢各科目成績中的最低分 329
7.DGET函式（從資料庫中提取符合條件的單個值） 330
技巧300 在列表或資料庫中按條件查詢 331
8.DPRODUCT函式（從資料庫中返回滿足指定條件的數值的乘積） 332
技巧301 判斷指定類別與品牌的商品是否被維修過 332
10.2 方差、標準差計算 333
9.DSTDEV函式（按條件計算樣本標準偏差） 333
技巧302 計算女性員工工齡的樣本標準偏差 334
10.DSTDEVP函式（按條件計算總體標準偏差） 334
技巧303 計算女性員工工齡的總體標準偏差 335
11.DVAR函式（按條件計算樣本總體方差） 335
技巧304 計算女性員工工齡的樣本總體方差 336
12.DVARP函式（按條件計算總體方差） 336
技巧305 計算女性員工工齡的總體方差 337
第11章 用公式設定單元格格式及限制數據輸入 338
11.1 函式在條件格式中的套用 338
技巧306 工作表自動隔行填色 338
技巧307 突出顯示前3名 339
技巧308 自動標識周末日期 341
技巧309 標識出不同的採購價格 342
技巧310 為不包含某一特定字元的單元格設定格式 343
技巧311 標識出重複值班的員工 344
技巧312 將成績高於平均值的標註為“優” 345
技巧313 突出顯示“缺考”或未填寫數據的單元格 346
11.2 函式在數據有效性中的套用 347
技巧314 避免輸入重複的產品編碼 347
技巧315 提示出貨數量大於進貨數量 348
技巧316 避免輸入文本值 349
技巧317 設定D列中只能輸入女性員工姓名 350
技巧318 禁止錄入不完整的產品規格 351
技巧319 設定單元格輸入必須包含指定內容 352
技巧320 限制數據輸入的長度 353
第12章 Excel函式與公式使用常見問題集 355
技巧321 想複製公式卻找不到填充柄了 355
技巧322 為什麼在計算結果的單元格中始終顯示公式 355
技巧323 如何取消Excel表格中的計算公式 356
技巧324 解決公式中計算結果無法自動更新的問題 357
技巧325 如何定位和取消循環引用 357
技巧326 完成合併計算後，原表數據更新後的結果不能同步更新 358
技巧327 計算工齡時，計算結果顯示“1900—1—6”形式 358
技巧328 解決條件為文本計算結果為0的問題 359
技巧329 解決浮點運算造成ROUND函式計算不準確的問題 359
技巧330 表格中有很多計算公式導致運行速度慢，有辦法解決嗎 360
技巧331 新輸入的行中不能自動填充上一行的公式 361
技巧332 合併單元格中公式的複製 362
技巧333 公式返回“#DIV>0！”錯誤值（“0”值或空白單元格被作為了除數） 363
技巧334 公式返回“#N>A”錯誤值1（公式中省略了函式中必不可少的參數） 364
技巧335 公式返回“#N>A”錯誤值2（公式中引用的數據源不正確或不能使用） 365
技巧336 公式返回“#NAME？”錯誤值1（輸入的函式和名稱拼寫錯誤） 365
技巧337 公式返回“#NAME？”錯誤值2（公式中使用文本作為參數時未加雙引號） 366
技巧338 公式返回“#NAME？”錯誤值3（在公式中使用了未定義的名稱） 367
技巧339 公式返回“#NAME？”錯誤值4（引用其他工作表時工作表名稱包含空格） 367
技巧340 公式返回“#NUM！”錯誤值（引用了無效的參數） 368
技巧341 公式返回“#VALUE！”錯誤值1（公式中將文本類型的數據參與了數值運算）368
技巧342 公式返回“#VALUE！”錯誤值2（公式中函式使用的參數與語法不一致）369
技巧343 公式返回“#VALUE！”錯誤值3（數組運算未按“Shift+Ctrl+Enter”鍵結束）369
技巧344 公式返回“#REF！”錯誤值（公式計算中引用了無效的單元格） 370
