《高效隨身查——Excel函式與公式套用技巧（2016版）》是2018年清華大學出版社出版的圖書，作者是賽貝爾資訊。
基本介紹
- 書名：高效隨身查——Excel函式與公式套用技巧（2016版）
- 作者：賽貝爾資訊
- ISBN：9787302467700
- 定價：49.80元
- 出版社：清華大學出版社
- 出版時間：2018.01.01
- 印刷日期：2017.11.30
內容簡介,目錄,
內容簡介
《高效隨身查——Excel函式與公式套用技巧（2016版）》共12 章，分別講解函式是什麼、公式要怎么用、邏輯函式範例、文本函式範例、日期與時間函式範例、數學函式範例、統計函式範例、財務函式範例、查找和引用函式範例、信息函式範例、資料庫函式範例、公式設定單元格，以及Excel 函式與公式使用常見問題集等內容。
目錄
第1章函式與公式1
1.1公式的輸入與編輯1
技巧1不會函式也能快速求和（或蘭厚跨平均值）1
技巧2公式函式不分家2
技巧3啟用“插入函式”對話框編輯函式3
技巧4手寫編輯公式7
技巧5公式的修改7
技巧6不記得函式全稱也能正確輸入函式8
技巧7快速查找和學習某函式用法9
技巧8使用批量計算11
技巧9大範圍公式複製的方法11
技巧10跳過非空單元格批量建立公式12
技巧11數組公式14
技巧12為什麼數字與“空”單元格相加出錯15
技巧13為什厚罪么明明顯示的是數據，計算結果卻為016
技巧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重新修改名稱的引用位置32
1.3學會分解、理解公式33
技巧29查看長公式中某一步的計算結果33
技巧30追蹤公式引用的單元格34
技巧31通過“公式求值”功能逐步分解公式35
技巧32在單元格中顯示所有公式36
第2章邏輯函式範例38
1.AND函式（檢驗一組數據是否都滿足條件）38
技巧33考評學生的各門課程是否全部及格38
技巧34一次性判斷一組數據是否都滿足條件39
2.NOT函式（對所給參數求反）40
技巧35篩選掉小於某一年齡的應聘人員40
3.OR函式（檢驗一組數據是否有一個滿足條件）41
技巧36檢驗員工是否通過考評41
4.IF函式（根據條件判斷真假）42
技巧37利用IF函式進行金額校驗43
技巧38根據不同返利比計算返利金額43
技巧39根據業務處理量判斷員工業務水平44
技巧40分性別判斷成績是否合格45
技巧41根據消費卡類別與消費額派發贈品47
技巧42根據職工性別和職務判斷退休年齡48
技巧43根據年齡與學歷對應聘人員二次篩選49
技巧44根剃求洪據商品的名稱與顏色進行一次性調價50
技巧45根據3項業務的完成率計算綜合完成率52
技巧46比較兩個採購部門的採購價格53
技巧47計算個人所得稅54
5.IFERROR函式（根據條件判斷真假）56
技巧48當被除數為空值（或0值）時返回“計算數據源有錯誤”文字56
第3章文本函式範例58
3.1提取文本58
1.LEFT函式（從最左側開始提取指定數目的字元）58
技巧49從E-mail地址中提取定棕剃邀賬號58
技巧50從商品全稱中提取產地信息59
2.LEFTB函式（按位元組數從最左側提取指定數目的字元）60
技巧51根據產品編號提取類別60
3.RIGHT函式（從最右側開始提取指定數目的字元）61
技巧52將電話號碼的區號與號碼分離62
技巧53從右側提取字元並自動轉換為數值寒蒸烏63
技巧54發票金額的分列填寫64
4.RIGHTB函式（按位元組數從最右側提取指定數目的字元）67
技巧55提取產品的規格數據67
5.MID函式（從指定位置開始提取字元）68
技巧56從規格數據中提取部分數據69
技巧57提取產品的貨號69
6.MIDB函式（按位元組數從指定位置開始提取字元）70
技巧58從房號數據中提取單元號71
7.CONCATENATE函式（合併多個字元）72
技巧59合併商品貨號碼數及顏色72
技巧60提取任意有用數據併合並73
3.2查找與替換文本74
8.FIND函式（查找指定字元並返回其位置）74
技巧61查找品名所在位置並提取74
技巧62問卷調查時實現自動統計答案75
9.REPLACE函式（替換字元中的部分字元）77
技巧63禁止中獎手機號碼的後幾位數77
10.SEARCH函式（查找字元並返回其起始位置）78
技巧64從項目編碼中提取契約號79
11.SUBSTITUTE函式（用新字元替換舊字元）80
技巧65去掉報表中多餘的空格80
技巧66查找特定文本且將第一次出現的刪除，其他保留81
技巧67嵌套使用SUBSTITUTE函式返回公司名稱簡稱81
技巧68根據報名學員統計人數83
3.3轉換文本格式84
12.FIXED函式（按指定的小數位數進行取整）84
技巧69消除因四捨五入而造成的顯示誤差84
13.WIDECHAR函式（半角字母轉換為全形字母）85
技巧70將半角英文字母轉換為全形英文字母85
14.UPPER(將文本轉換為大寫形式)85
技巧71將小寫英文文本一次性變更為大寫86
15.LOWER函式（大寫字母轉換為小寫字母）86
技巧72將大寫字母轉換為小寫字母86
16.PROPER函式（將首字元轉換為大寫）87
技巧73一次性將每個單詞的首字母轉換為大寫87
17.RMB函式（四捨五入數值，並添加千分位符號和￥符號）88
技巧74將銷售額一次性轉換為人民幣格式88
18.DOLLAR函式（四捨五入數值，並添加千分位符號和$符號）89
技巧75將銷售金額一次性轉換為美元貨幣格式89
19.TEXT函式（將數值轉換為指定格式的文本）90
技巧76讓計算得到金額顯示為特定形式90
技巧77返回值班日期對應在的星期數91
技巧78按上下班時間計算加班時長並顯示為“*時*分”形式91
技巧79解決日期計算返回日期序列號問題93
技巧80讓數據統一顯示固定的位數94
技巧81讓合併的日期顯示正確格式95
20.VALUE函式（將文本型數字轉換成數值型數字）96
技巧82解決總金額無法計算的問題96
3.4其他文本函式97
21.LEN函式（返回字元串的字元數）97
技巧83從身份證號碼中提取出生年份97
技巧84從身份證號碼中提取性別98
技巧85配合多函式從身份證號碼中提取完整的出生年月日99
22.REPT函式（重複文本）101
技巧86輸入身份證號碼填寫框101
技巧87根據銷售額用“★”評定等級101
23.TRIM函式（清除空格）102
技巧88刪除文本單詞中多餘的空格103
24.EXACT函式（比較兩個字元串是否相同）103
技巧89比較兩個店鋪的平均售價是否相同103
第4章日期與時間函式範例105
4.1返回日期105
1.NOW函式（返回當前日期時間）105
技巧90計算員工在職天數105
2.TODAY函式（返回當前日期）106
技巧91判斷應收賬款是否到期106
3.DATE函式（返回指定日期的序列號）107
技巧92建立倒計時顯示牌107
4.DAY函式（返回某日期的天數）108
技巧93顯示出全年中各月的天數108
技巧94計算本月上旬的銷售總額109
5.YEAR函式（返回某日期中的年份）110
技巧95計算出員工年齡110
技巧96計算出員工工齡111
6.WEEKDAY函式（返回某日期為星期幾）113
技巧97返回日期對應的星期數113
技巧98判斷值班日期是平時加班還是雙休日加班114
7.MONTH函式（返回某日期中的月份）115
技巧99自動填寫銷售報表中的月份115
技巧100計算本月賬款金額總計116
8.EOMONTH函式（返回某個月份最後一天的序列號）117
技巧101根據活動開始日期計算各月活動天數117
技巧102根據當前月份自動顯示本月所有日期序列118
技巧103自動返回各日期對應的星期數120
9.WEEKNUM函式（返回指定日期是第幾周）121
技巧104快速得知2016年中各節日在第幾周121
4.2日期計算122
10.DATEDIF函式（計算兩個日期之間的年、月和天數）122
技巧105計算總借款天數123
技巧106計算借款至今日的時長123
技巧107根據員工工齡自動追加工齡工資124
11.DAYS360函式（計算兩日期間相差的天數）125
技巧108計算還款剩餘天數125
技巧109計算固定資產已使用月份126
技巧110利用DAYS360函式判斷借款是否逾期127
12.YEARFRAC函式（計算兩日期間天數占全年天數的百分比）128
技巧111計算各地點工作時間占全年天數百分比129
13.WORKDAY函式（計算工作日）129
技巧112根據休假天數自動顯示出休假結束日期130
14.NETWORKDAYS函式（去除指定日期之後的工作日數）130
技巧113計算元旦到春節之間的工作日131
技巧114計算年假占全年工作日的百分比131
15.EDATE函式（計算與指定日期相隔指定月份數的日期）133
技巧115計算應收賬款的到期日期133
4.3時間函式134
16.HOUR函式（返回某時間中的小時數）134
技巧116計算來訪簽到時間的區間134
17.MINUTE函式（返回某時間中的分鐘數）135
技巧117計算停車時間135
18.SECOND函式（返回某時間中的秒數）136
技巧118計算通話秒數136
技巧119顯示高於或低於標準時間的值137
4.4日期與時間轉換138
19.DATEVALUE函式（將文本日期轉換為可識別的日期序列號）138
技巧120計算借款天數139
20.TIMEVALUE函式（將時間轉換為對應的小數值）140
技巧121將指定時間轉換為時間小數值140
第5章數學函式範例141
5.1常規數學計算函式141
1.SUM函式（求和）141
技巧122一次性對多列數據求和141
技巧123根據銷售數量與單價計算總銷售額142
技巧124隻計算某兩個產品的合計金額143
技巧125分奇偶行統計數據144
2.SUMIF函式（按條件求和）145
技巧126按經辦人計算銷售金額145
技巧127統計各部門工資總額146
技巧128分別統計前半個月與後半個月的銷售額147
技巧129用通配符對某一類數據求和148
技巧130計算銷售金額前3名合計值149
3.SUMIFS函式（按多條件求和）150
技巧131滿足指定類別指定時間統計總銷售額150
技巧132多條件統計某一類數據總和152
技巧133按不同性質統計應收款153
4.SUMPRODUCT函式（求多組數的乘積之和）155
技巧134統計銷售部女員工人數155
技巧135統計出指定班級中分數大於指定值的人數156
技巧136統計出指定部門獲取獎金的人數（去除空值）157
技巧137統計出指定部門、指定職務的員工人數158
技巧138統計非工作日銷售金額159
技巧139按月匯總出庫數量160
技巧140統計大於12個月的賬款161
技巧141統計出某兩種商品的銷售金額162
技巧142統計某一時間段出現的次數163
技巧143統計指定日期區間中指定性別的人數164
技巧144分單位統計賬齡165
5.ABS函式（求絕對值）168
技巧145比較今年銷售額與去年銷售額168
技巧146計算支出金額總計值169
6.MOD函式（求兩個數相除後的餘數）169
技巧147按奇數月與偶數月統計銷量170
技巧148計算每位員工的加班時長171
7.SQRT函式（求算術平方根）172
技巧149計算指定數值對應的算術平方根173
8.GCD函式（求最大公約數）173
技巧150返回兩個或多個整數的最大公約數173
9.LCM函式（求最低公倍數）174
技巧151計算兩個或多個整數的最低公倍數174
10.POWER函式（計算方根）175
技巧152根據指定的底數和指數計算出方根值175
5.2捨入計算函式176
11.INT函式（向下取整）176
技巧153計算平均銷售數量時取整數176
12.TRUNC函式（截去數據小數部分）177
技巧154計算銷售金額時取整或保留指定位數小數177
13.CEILING函式（捨入計算）179
技巧155以6秒（不足6秒按6秒計算）為單位計算通話費用179
14.ROUND函式（四捨五入）180
技巧156以一個百分點為單位計算獎金或扣款181
15.ROUNDUP函式（向上捨入）182
技巧157使用ROUNDUP函式計算物品的快遞費用182
技巧158計算上網費用183
16.ROUNDDOWN函式（向下捨入）184
技巧159根據給定時間界定整點範圍185
17.FLOOR函式（去尾捨入）186
技巧160計件工資中的獎金計算186
18.MROUND函式（按指定倍數捨入）187
技巧161計算商品運送車次187
19.QUOTIENT函式（返回數值相除後的整數）189
技巧162計算參加某活動的每組人數189
5.3階乘、隨機數計算函式189
20.FACT函式（求指定正數值的階乘）189
技巧163求指定正數值的階乘值190
21.MULTINOMIAL函式（計算指定數值階乘與各數值階乘乘積的比值）190
技巧164計算指定數值和的階乘與各數值階乘乘積的比值190
22.RAND函式（返回一個大於等於0小於1的隨機數）191
技巧165隨機獲取選手編號192
技巧166自動生成彩票7位開獎號碼192
23.RANDBETWEEN函式（返回兩個數值間的隨機數）193
技巧167自動隨機生成3位數編碼193
第6章統計函式範例194
6.1平均值計算函式194
1.AVERAGE函式（求平均值）194
技巧168快速自動求平均值194
技巧169在成績表中忽略0值求平均分195
技巧170計算指定學校學生的平均成績196
2.AVERAGEA函式（求包括文本和邏輯值的平均值）197
技巧171計算平均分時將“缺考”的也計算在內197
技巧172統計各月份的平均銷售額（計算區域含文本值）198
3.AVERAGEIF函式（按條件求平均值）199
技巧173統計各班級平均分199
技巧174計算月平均出庫數量200
技巧175排除新店計算平均利潤201
4.AVERAGEIFS函式（按多條件求平均值）201
技巧176計算一車間女職工平均工資202
技巧177求介於某一區間內的平均值203
技巧178統計指定店面所有男裝品牌的平均利潤203
技巧179忽略0值求指定班級的平均分204
5.GEOMEAN函式（求幾何平均值）205
技巧180比較兩種產品的銷售利潤的穩定性205
6.TRIMMEAN函式（去頭尾後求平均值）206
技巧181通過10位評審打分計算選手的最後得分206
6.2數目統計函式207
7.COUNT函式（統計數目）207
技巧182統計出故障的機器台數207
技巧183統計各個部門獲取交通補助的人數208
技巧184統計出指定學歷員工人數209
技巧185統計其中一科得滿分的人數210
8.COUNTA函式（求包括文本和邏輯值的數目）211
技巧186統計參與某項課程的總人數211
技巧187統計非正常出勤的人數211
9.COUNTIF函式（按條件統計數目）212
技巧188統計工資大於3000元的人數212
技巧189統計某課程的報名人數213
技巧190在成績表中分別統計及格人數與不及格人數213
技巧191統計出成績大於平均分數的學生人數215
技巧192統計是三好學生且參加數學競賽的人數215
技巧193統計連續3次考試都進入前10名的人數216
10.COUNTIFS函式（按多條件統計數目）217
技巧194統計各店面男裝的銷售記錄條數（雙條件）217
技巧195統計指定時間指定類別商品的銷售記錄數218
技巧196統計指定產品每日的銷售記錄數219
11.COUNTBLANK函式（統計空單元格的數目）220
技巧197統計缺考人數221
6.3最大值與最小值統計函式221
12.MAX函式（求最大值）221
技巧198快速返回數據區域中的最大值221
技巧199返回企業女性員工的最大年齡222
技巧200返回上半個月單筆最高銷售金額223
技巧201計算單日銷售金額並返回最大值224
13.MIN函式（求最小值）225
技巧202返回最低銷售金額225
技巧203忽略0值求最低分數226
技巧204返回多次短跑中用時最短的編號227
14.MAXA函式（包含文本與邏輯值時求最大值）228
技巧205返回成績表中的最高分數（包含文本）228
15.MINA函式（包含文本與邏輯值時求最小值）229
技巧206返回最低利潤額（包含文本）229
16.LARGE函式（返回某一數據集中的某個最大值）230
技巧207返回排名前3位的銷售金額230
技巧208計算成績表中前5名的平均值231
技巧209分別統計各班級第一名成績231
17.SMALL函式（返回某一數據集中的某個最小值）232
技巧210統計成績表中後5名的平均分232
技巧211統計成績表中後5名的平均分（忽略0值）233
18.MODE函式234
技巧212返回被投訴次數最多的客服的編號234
6.4排位統計函式235
19.RANK.EQ函式（返回數字的排位）235
技巧213為學生考試成績排名次235
技巧214對不連續單元格排名次236
20.RANK.AVG函式（排位有相同名次時返回平均排位）236
技巧215用RANK.AVG函式對銷售額排名237
21.PERCENTILE.INC函式（返回數值指定百分點的值）237
技巧216返回數值區域的k指定百分比對應的數值238
22.PERCENTRANK.INC函式（返回數字的百分比排位）239
技巧217將各月銷售利潤按百分比排位239
23.MEDIAN函式（返回數據集的中位數）240
技巧218統計全年各月利潤值中的中位數241
24.QUARTILE.INC函式（返回數據集的四分位數）241
技巧219從全年各月銷售利潤數據集中求四分位數242
第7章財務函式範例244
7.1投資計算函式244
1.PMT函式（計算貸款每期付款額）244
技巧220計算貸款的每年償還額244
技巧221計算貸款的每月償還額245
2.IPMT函式（計算每期償還額中的利息額）245
技巧222計算貸款每年償還金額中有多少是利息245
技巧223計算貸款每月償還金額中有多少是利息246
3.PPMT函式（計算本金）247
技巧224計算貸款每年償還金額中有多少是本金247
技巧225計算貸款每月償還金額中有多少是本金248
4.ISPMT函式（等額本金還款方式下的利息計算）249
技巧226在等額本金還款方式下計算某貸款的利息249
5.FV函式（計算投資未來值）250
技巧227計算分期存款的未來值250
技巧228計算購買某項保險的未來值251
6.FVSCHEDULE函式（投資在變動或可調利率下的未來值）251
技巧229計算某項整存整取存款的未來值251
7.PV函式（計算投資現值）252
技巧230判斷購買某項保險是否合算252
8.NPV函式（計算投資淨現值）253
技巧231計算某投資的淨現值253
9.XNPV函式（計算一組不定期現金流的淨現值）255
技巧232計算出一組不定期盈利額的淨現值255
10.NPER函式（計算投資期數）255
技巧233計算出貸款的清還年數256
技巧234計算出某項投資的投資期數256
7.2償還率計算函式257
11.IRR函式（計算一組現金流的內部收益率）257
技巧235計算某項投資的內部收益率257
12.XIRR函式（計算一組不定期現金流的內部收益率）258
技巧236計算出一組不定期盈利額的內部收益率258
13.MIRR函式（計算修正內部收益率）259
技巧237計算某項投資的修正內部收益率259
14.RATE函式（計算年金每個期間的利率）260
技巧238計算購買某項保險的收益率260
7.3資產折舊計算函式261
15.SLN函式（直線法）261
技巧239用直線法計算出固定資產的每年折舊額261
技巧240用直線法計算出固定資產的每月折舊額261
技巧241用年數總和法計算出固定資產的每年折舊額262
17.DB函式（固定餘額遞減法）263
技巧242用固定餘額遞減法計算出固定資產的每年折舊額263
技巧243用雙倍餘額遞減法計算出固定資產的每年折舊額264
19.VDB函式（計算部分期間內的折舊值）265
技巧244計算出固定資產部分期間的設備折舊值266
20.AMORDEGRC函式（指定會計期間的折舊值）267
技巧245計算指定會計期間的折舊值268
第8章查找和引用函式範例269
8.1查找函式269
1.CHOOSE函式（從參數列表中選擇並返回一個值）269
技巧246配合IF函式找出短跑成績的前三名269
技巧247根據產品不合格率決定產品處理辦法270
2.LOOKUP函式（向量型）（按條件查找並返回值）271
技巧248根據產品編號查詢庫存數量（向量型語法）272
3.LOOKUP函式（數組型）（按條件查找並返回值）273
技巧249根據產品編號查詢庫存數量（數組型語法）274
技巧250按姓名查詢學生的各科目成績275
4.HLOOKUP函式（橫向查找）277
技巧251根據不同的返利率計算各筆訂單的返利金額278
技巧252快速查詢任意科目的成績序列279
5.VLOOKUP函式（縱向查找）281
技巧253在銷售表中自動返回產品單價282
技巧254將多張工作表中的數據合併到一張工作表中283
技巧255根據多條件派發贈品285
技巧256使用VLOOKUP函式進行反向查詢286
6.MATCH函式+INDEX函式（MATCH查找並返回找到值所在位置，INDEX函式返回指定位置的值）288
技巧258查找任意指定銷售員的銷售總金額290
技巧259查找指定月份指定專櫃的銷售金額291
技巧260返回成績最高的學生的姓名293
技巧261查找遲到次數最多的學生294
技巧262查詢最高總金額所對應的店鋪295
8.2引用函式296
7.ADDRESS函式（建立文本類型單元格的地址）296
技巧263查找最大銷售額所在位置296
8.COLUMN函式（返回引用的列標）297
技巧264在一行中快速輸入月份298
技巧265實現隔列求總銷售金額298
9.COLUMNS函式（返回引用的列數）299
技巧266返回參與考試的科目數量300
10.ROW函式（返回引用的行號）300
技巧267讓數據自動隔4行（自定義）加1（自定義）300
技巧268提取季度合計值計算全年銷售額301
技巧269根據借款期限返回相應的年數序列302
11.ROWS函式（返回引用的行數）304
技巧270統計銷售記錄條數304
技巧271判斷值班人員是否重複304
12.INDIRECT函式（返回指定的引用）305
技巧272解決合併單元格引用數據列出現跳躍的問題306
技巧273按指定的範圍計算平均值307
技巧274INDIRECT解決跨工作表查詢時名稱匹配問題309
13.OFFSET函式（根據指定偏移量得到新引用）310
技巧275實現數據的動態查詢311
技巧276對每日出庫量累計求和312
技巧277OFFSET函式常用於創建動態圖表數據源313
第9章信息函式範例316
9.1使用IS函式進行判斷316
1.ISBLANK函式（判斷值是否為空值）316
技巧278標註出缺考學生316
2.ISTEXT函式（判斷數據是否為文本）317
技巧279快速統計缺考人數317
3.ISLOGICAL函式（判斷數據是否為邏輯值）318
技巧280檢驗數據是否為邏輯值318
4.ISNUMBER函式（判斷數據是否為數字）318
技巧281統計實考人數319
技巧282統計銷售量總計值319
5.ISNA函式（判斷數據是否為錯誤值“N/A”）320
技巧283檢驗數據是否為“N/A”錯誤值320
6.ISERR函式（判斷數據是否為“N/A”之外的任意錯誤值）321
技巧284檢驗數據是否為“N/A”之外的任意錯誤值321
7.ISODD函式（判斷數據是否為奇數）321
技巧285根據身份證號碼判斷其性別1322
8.ISEVEN函式（判斷數據是否為偶數）322
技巧286根據身份證號碼判斷其性別2323
9.2返回相關值323
9.CELL函式（返回單元格、位置等）323
技巧287判斷測試結果是否達標324
10.ERROR.TYPE函式（返回錯誤對應的編號）325
技巧288根據錯誤代碼顯示錯誤原因326
11.N函式（返迴轉換為數值後的值）326
技巧289用訂單生成日期的序列號與當前行號生成訂單的編號327
12.TYPE函式（返回數值類型）327
技巧290返回數值對應的類型數字328
技巧39根據業務處理量判斷員工業務水平44
技巧40分性別判斷成績是否合格45
技巧41根據消費卡類別與消費額派發贈品47
技巧42根據職工性別和職務判斷退休年齡48
技巧43根據年齡與學歷對應聘人員二次篩選49
技巧44根據商品的名稱與顏色進行一次性調價50
技巧45根據3項業務的完成率計算綜合完成率52
技巧46比較兩個採購部門的採購價格53
技巧47計算個人所得稅54
5.IFERROR函式（根據條件判斷真假）56
技巧48當被除數為空值（或0值）時返回“計算數據源有錯誤”文字56
第3章文本函式範例58
3.1提取文本58
1.LEFT函式（從最左側開始提取指定數目的字元）58
技巧49從E-mail地址中提取賬號58
技巧50從商品全稱中提取產地信息59
2.LEFTB函式（按位元組數從最左側提取指定數目的字元）60
技巧51根據產品編號提取類別60
3.RIGHT函式（從最右側開始提取指定數目的字元）61
技巧52將電話號碼的區號與號碼分離62
技巧53從右側提取字元並自動轉換為數值63
技巧54發票金額的分列填寫64
4.RIGHTB函式（按位元組數從最右側提取指定數目的字元）67
技巧55提取產品的規格數據67
5.MID函式（從指定位置開始提取字元）68
技巧56從規格數據中提取部分數據69
技巧57提取產品的貨號69
6.MIDB函式（按位元組數從指定位置開始提取字元）70
技巧58從房號數據中提取單元號71
7.CONCATENATE函式（合併多個字元）72
技巧59合併商品貨號碼數及顏色72
技巧60提取任意有用數據併合並73
3.2查找與替換文本74
8.FIND函式（查找指定字元並返回其位置）74
技巧61查找品名所在位置並提取74
技巧62問卷調查時實現自動統計答案75
9.REPLACE函式（替換字元中的部分字元）77
技巧63禁止中獎手機號碼的後幾位數77
10.SEARCH函式（查找字元並返回其起始位置）78
技巧64從項目編碼中提取契約號79
11.SUBSTITUTE函式（用新字元替換舊字元）80
技巧65去掉報表中多餘的空格80
技巧66查找特定文本且將第一次出現的刪除，其他保留81
技巧67嵌套使用SUBSTITUTE函式返回公司名稱簡稱81
技巧68根據報名學員統計人數83
3.3轉換文本格式84
12.FIXED函式（按指定的小數位數進行取整）84
技巧69消除因四捨五入而造成的顯示誤差84
13.WIDECHAR函式（半角字母轉換為全形字母）85
技巧70將半角英文字母轉換為全形英文字母85
14.UPPER(將文本轉換為大寫形式)85
技巧71將小寫英文文本一次性變更為大寫86
15.LOWER函式（大寫字母轉換為小寫字母）86
技巧72將大寫字母轉換為小寫字母86
16.PROPER函式（將首字元轉換為大寫）87
技巧73一次性將每個單詞的首字母轉換為大寫87
17.RMB函式（四捨五入數值，並添加千分位符號和￥符號）88
技巧74將銷售額一次性轉換為人民幣格式88
18.DOLLAR函式（四捨五入數值，並添加千分位符號和$符號）89
技巧75將銷售金額一次性轉換為美元貨幣格式89
19.TEXT函式（將數值轉換為指定格式的文本）90
技巧76讓計算得到金額顯示為特定形式90
技巧77返回值班日期對應在的星期數91
技巧78按上下班時間計算加班時長並顯示為“*時*分”形式91
技巧79解決日期計算返回日期序列號問題93
技巧80讓數據統一顯示固定的位數94
技巧81讓合併的日期顯示正確格式95
20.VALUE函式（將文本型數字轉換成數值型數字）96
技巧82解決總金額無法計算的問題96
3.4其他文本函式97
21.LEN函式（返回字元串的字元數）97
技巧83從身份證號碼中提取出生年份97
技巧84從身份證號碼中提取性別98
技巧85配合多函式從身份證號碼中提取完整的出生年月日99
22.REPT函式（重複文本）101
技巧86輸入身份證號碼填寫框101
技巧87根據銷售額用“★”評定等級101
23.TRIM函式（清除空格）102
技巧88刪除文本單詞中多餘的空格103
24.EXACT函式（比較兩個字元串是否相同）103
技巧89比較兩個店鋪的平均售價是否相同103
第4章日期與時間函式範例105
4.1返回日期105
1.NOW函式（返回當前日期時間）105
技巧90計算員工在職天數105
2.TODAY函式（返回當前日期）106
技巧91判斷應收賬款是否到期106
3.DATE函式（返回指定日期的序列號）107
技巧92建立倒計時顯示牌107
4.DAY函式（返回某日期的天數）108
技巧93顯示出全年中各月的天數108
技巧94計算本月上旬的銷售總額109
5.YEAR函式（返回某日期中的年份）110
技巧95計算出員工年齡110
技巧96計算出員工工齡111
6.WEEKDAY函式（返回某日期為星期幾）113
技巧97返回日期對應的星期數113
技巧98判斷值班日期是平時加班還是雙休日加班114
7.MONTH函式（返回某日期中的月份）115
技巧99自動填寫銷售報表中的月份115
技巧100計算本月賬款金額總計116
8.EOMONTH函式（返回某個月份最後一天的序列號）117
技巧101根據活動開始日期計算各月活動天數117
技巧102根據當前月份自動顯示本月所有日期序列118
技巧103自動返回各日期對應的星期數120
9.WEEKNUM函式（返回指定日期是第幾周）121
技巧104快速得知2016年中各節日在第幾周121
4.2日期計算122
10.DATEDIF函式（計算兩個日期之間的年、月和天數）122
技巧105計算總借款天數123
技巧106計算借款至今日的時長123
技巧107根據員工工齡自動追加工齡工資124
11.DAYS360函式（計算兩日期間相差的天數）125
技巧108計算還款剩餘天數125
技巧109計算固定資產已使用月份126
技巧110利用DAYS360函式判斷借款是否逾期127
12.YEARFRAC函式（計算兩日期間天數占全年天數的百分比）128
技巧111計算各地點工作時間占全年天數百分比129
13.WORKDAY函式（計算工作日）129
技巧112根據休假天數自動顯示出休假結束日期130
14.NETWORKDAYS函式（去除指定日期之後的工作日數）130
技巧113計算元旦到春節之間的工作日131
技巧114計算年假占全年工作日的百分比131
15.EDATE函式（計算與指定日期相隔指定月份數的日期）133
技巧115計算應收賬款的到期日期133
4.3時間函式134
16.HOUR函式（返回某時間中的小時數）134
技巧116計算來訪簽到時間的區間134
17.MINUTE函式（返回某時間中的分鐘數）135
技巧117計算停車時間135
18.SECOND函式（返回某時間中的秒數）136
技巧118計算通話秒數136
技巧119顯示高於或低於標準時間的值137
4.4日期與時間轉換138
19.DATEVALUE函式（將文本日期轉換為可識別的日期序列號）138
技巧120計算借款天數139
20.TIMEVALUE函式（將時間轉換為對應的小數值）140
技巧121將指定時間轉換為時間小數值140
第5章數學函式範例141
5.1常規數學計算函式141
1.SUM函式（求和）141
技巧122一次性對多列數據求和141
技巧123根據銷售數量與單價計算總銷售額142
技巧124隻計算某兩個產品的合計金額143
技巧125分奇偶行統計數據144
2.SUMIF函式（按條件求和）145
技巧126按經辦人計算銷售金額145
技巧127統計各部門工資總額146
技巧128分別統計前半個月與後半個月的銷售額147
技巧129用通配符對某一類數據求和148
技巧130計算銷售金額前3名合計值149
3.SUMIFS函式（按多條件求和）150
技巧131滿足指定類別指定時間統計總銷售額150
技巧132多條件統計某一類數據總和152
技巧133按不同性質統計應收款153
4.SUMPRODUCT函式（求多組數的乘積之和）155
技巧134統計銷售部女員工人數155
技巧135統計出指定班級中分數大於指定值的人數156
技巧136統計出指定部門獲取獎金的人數（去除空值）157
技巧137統計出指定部門、指定職務的員工人數158
技巧138統計非工作日銷售金額159
技巧139按月匯總出庫數量160
技巧140統計大於12個月的賬款161
技巧141統計出某兩種商品的銷售金額162
技巧142統計某一時間段出現的次數163
技巧143統計指定日期區間中指定性別的人數164
技巧144分單位統計賬齡165
5.ABS函式（求絕對值）168
技巧145比較今年銷售額與去年銷售額168
技巧146計算支出金額總計值169
6.MOD函式（求兩個數相除後的餘數）169
技巧147按奇數月與偶數月統計銷量170
技巧148計算每位員工的加班時長171
7.SQRT函式（求算術平方根）172
技巧149計算指定數值對應的算術平方根173
8.GCD函式（求最大公約數）173
技巧150返回兩個或多個整數的最大公約數173
9.LCM函式（求最低公倍數）174
技巧151計算兩個或多個整數的最低公倍數174
10.POWER函式（計算方根）175
技巧152根據指定的底數和指數計算出方根值175
5.2捨入計算函式176
11.INT函式（向下取整）176
技巧153計算平均銷售數量時取整數176
12.TRUNC函式（截去數據小數部分）177
技巧154計算銷售金額時取整或保留指定位數小數177
13.CEILING函式（捨入計算）179
技巧155以6秒（不足6秒按6秒計算）為單位計算通話費用179
14.ROUND函式（四捨五入）180
技巧156以一個百分點為單位計算獎金或扣款181
15.ROUNDUP函式（向上捨入）182
技巧157使用ROUNDUP函式計算物品的快遞費用182
技巧158計算上網費用183
16.ROUNDDOWN函式（向下捨入）184
技巧159根據給定時間界定整點範圍185
17.FLOOR函式（去尾捨入）186
技巧160計件工資中的獎金計算186
18.MROUND函式（按指定倍數捨入）187
技巧161計算商品運送車次187
19.QUOTIENT函式（返回數值相除後的整數）189
技巧162計算參加某活動的每組人數189
5.3階乘、隨機數計算函式189
20.FACT函式（求指定正數值的階乘）189
技巧163求指定正數值的階乘值190
21.MULTINOMIAL函式（計算指定數值階乘與各數值階乘乘積的比值）190
技巧164計算指定數值和的階乘與各數值階乘乘積的比值190
22.RAND函式（返回一個大於等於0小於1的隨機數）191
技巧165隨機獲取選手編號192
技巧166自動生成彩票7位開獎號碼192
23.RANDBETWEEN函式（返回兩個數值間的隨機數）193
技巧167自動隨機生成3位數編碼193
第6章統計函式範例194
6.1平均值計算函式194
1.AVERAGE函式（求平均值）194
技巧168快速自動求平均值194
技巧169在成績表中忽略0值求平均分195
技巧170計算指定學校學生的平均成績196
2.AVERAGEA函式（求包括文本和邏輯值的平均值）197
技巧171計算平均分時將“缺考”的也計算在內197
技巧172統計各月份的平均銷售額（計算區域含文本值）198
3.AVERAGEIF函式（按條件求平均值）199
技巧173統計各班級平均分199
技巧174計算月平均出庫數量200
技巧175排除新店計算平均利潤201
4.AVERAGEIFS函式（按多條件求平均值）201
技巧176計算一車間女職工平均工資202
技巧177求介於某一區間內的平均值203
技巧178統計指定店面所有男裝品牌的平均利潤203
技巧179忽略0值求指定班級的平均分204
5.GEOMEAN函式（求幾何平均值）205
技巧180比較兩種產品的銷售利潤的穩定性205
6.TRIMMEAN函式（去頭尾後求平均值）206
技巧181通過10位評審打分計算選手的最後得分206
6.2數目統計函式207
7.COUNT函式（統計數目）207
技巧182統計出故障的機器台數207
技巧183統計各個部門獲取交通補助的人數208
技巧184統計出指定學歷員工人數209
技巧185統計其中一科得滿分的人數210
8.COUNTA函式（求包括文本和邏輯值的數目）211
技巧186統計參與某項課程的總人數211
技巧187統計非正常出勤的人數211
9.COUNTIF函式（按條件統計數目）212
技巧188統計工資大於3000元的人數212
技巧189統計某課程的報名人數213
技巧190在成績表中分別統計及格人數與不及格人數213
技巧191統計出成績大於平均分數的學生人數215
技巧192統計是三好學生且參加數學競賽的人數215
技巧193統計連續3次考試都進入前10名的人數216
10.COUNTIFS函式（按多條件統計數目）217
技巧194統計各店面男裝的銷售記錄條數（雙條件）217
技巧195統計指定時間指定類別商品的銷售記錄數218
技巧196統計指定產品每日的銷售記錄數219
11.COUNTBLANK函式（統計空單元格的數目）220
技巧197統計缺考人數221
6.3最大值與最小值統計函式221
12.MAX函式（求最大值）221
技巧198快速返回數據區域中的最大值221
技巧199返回企業女性員工的最大年齡222
技巧200返回上半個月單筆最高銷售金額223
技巧201計算單日銷售金額並返回最大值224
13.MIN函式（求最小值）225
技巧202返回最低銷售金額225
技巧203忽略0值求最低分數226
技巧204返回多次短跑中用時最短的編號227
14.MAXA函式（包含文本與邏輯值時求最大值）228
技巧205返回成績表中的最高分數（包含文本）228
15.MINA函式（包含文本與邏輯值時求最小值）229
技巧206返回最低利潤額（包含文本）229
16.LARGE函式（返回某一數據集中的某個最大值）230
技巧207返回排名前3位的銷售金額230
技巧208計算成績表中前5名的平均值231
技巧209分別統計各班級第一名成績231
17.SMALL函式（返回某一數據集中的某個最小值）232
技巧210統計成績表中後5名的平均分232
技巧211統計成績表中後5名的平均分（忽略0值）233
18.MODE函式234
技巧212返回被投訴次數最多的客服的編號234
6.4排位統計函式235
19.RANK.EQ函式（返回數字的排位）235
技巧213為學生考試成績排名次235
技巧214對不連續單元格排名次236
20.RANK.AVG函式（排位有相同名次時返回平均排位）236
技巧215用RANK.AVG函式對銷售額排名237
21.PERCENTILE.INC函式（返回數值指定百分點的值）237
技巧216返回數值區域的k指定百分比對應的數值238
22.PERCENTRANK.INC函式（返回數字的百分比排位）239
技巧217將各月銷售利潤按百分比排位239
23.MEDIAN函式（返回數據集的中位數）240
技巧218統計全年各月利潤值中的中位數241
24.QUARTILE.INC函式（返回數據集的四分位數）241
技巧219從全年各月銷售利潤數據集中求四分位數242
第7章財務函式範例244
7.1投資計算函式244
1.PMT函式（計算貸款每期付款額）244
技巧220計算貸款的每年償還額244
技巧221計算貸款的每月償還額245
2.IPMT函式（計算每期償還額中的利息額）245
技巧222計算貸款每年償還金額中有多少是利息245
技巧223計算貸款每月償還金額中有多少是利息246
3.PPMT函式（計算本金）247
技巧224計算貸款每年償還金額中有多少是本金247
技巧225計算貸款每月償還金額中有多少是本金248
4.ISPMT函式（等額本金還款方式下的利息計算）249
技巧226在等額本金還款方式下計算某貸款的利息249
5.FV函式（計算投資未來值）250
技巧227計算分期存款的未來值250
技巧228計算購買某項保險的未來值251
6.FVSCHEDULE函式（投資在變動或可調利率下的未來值）251
技巧229計算某項整存整取存款的未來值251
7.PV函式（計算投資現值）252
技巧230判斷購買某項保險是否合算252
8.NPV函式（計算投資淨現值）253
技巧231計算某投資的淨現值253
9.XNPV函式（計算一組不定期現金流的淨現值）255
技巧232計算出一組不定期盈利額的淨現值255
10.NPER函式（計算投資期數）255
技巧233計算出貸款的清還年數256
技巧234計算出某項投資的投資期數256
7.2償還率計算函式257
11.IRR函式（計算一組現金流的內部收益率）257
技巧235計算某項投資的內部收益率257
12.XIRR函式（計算一組不定期現金流的內部收益率）258
技巧236計算出一組不定期盈利額的內部收益率258
13.MIRR函式（計算修正內部收益率）259
技巧237計算某項投資的修正內部收益率259
14.RATE函式（計算年金每個期間的利率）260
技巧238計算購買某項保險的收益率260
7.3資產折舊計算函式261
15.SLN函式（直線法）261
技巧239用直線法計算出固定資產的每年折舊額261
技巧240用直線法計算出固定資產的每月折舊額261
技巧241用年數總和法計算出固定資產的每年折舊額262
17.DB函式（固定餘額遞減法）263
技巧242用固定餘額遞減法計算出固定資產的每年折舊額263
技巧243用雙倍餘額遞減法計算出固定資產的每年折舊額264
19.VDB函式（計算部分期間內的折舊值）265
技巧244計算出固定資產部分期間的設備折舊值266
20.AMORDEGRC函式（指定會計期間的折舊值）267
技巧245計算指定會計期間的折舊值268
第8章查找和引用函式範例269
8.1查找函式269
1.CHOOSE函式（從參數列表中選擇並返回一個值）269
技巧246配合IF函式找出短跑成績的前三名269
技巧247根據產品不合格率決定產品處理辦法270
2.LOOKUP函式（向量型）（按條件查找並返回值）271
技巧248根據產品編號查詢庫存數量（向量型語法）272
3.LOOKUP函式（數組型）（按條件查找並返回值）273
技巧249根據產品編號查詢庫存數量（數組型語法）274
技巧250按姓名查詢學生的各科目成績275
4.HLOOKUP函式（橫向查找）277
技巧251根據不同的返利率計算各筆訂單的返利金額278
技巧252快速查詢任意科目的成績序列279
5.VLOOKUP函式（縱向查找）281
技巧253在銷售表中自動返回產品單價282
技巧254將多張工作表中的數據合併到一張工作表中283
技巧255根據多條件派發贈品285
技巧256使用VLOOKUP函式進行反向查詢286
6.MATCH函式+INDEX函式（MATCH查找並返回找到值所在位置，INDEX函式返回指定位置的值）288
技巧258查找任意指定銷售員的銷售總金額290
技巧259查找指定月份指定專櫃的銷售金額291
技巧260返回成績最高的學生的姓名293
技巧261查找遲到次數最多的學生294
技巧262查詢最高總金額所對應的店鋪295
8.2引用函式296
7.ADDRESS函式（建立文本類型單元格的地址）296
技巧263查找最大銷售額所在位置296
8.COLUMN函式（返回引用的列標）297
技巧264在一行中快速輸入月份298
技巧265實現隔列求總銷售金額298
9.COLUMNS函式（返回引用的列數）299
技巧266返回參與考試的科目數量300
10.ROW函式（返回引用的行號）300
技巧267讓數據自動隔4行（自定義）加1（自定義）300
技巧268提取季度合計值計算全年銷售額301
技巧269根據借款期限返回相應的年數序列302
11.ROWS函式（返回引用的行數）304
技巧270統計銷售記錄條數304
技巧271判斷值班人員是否重複304
12.INDIRECT函式（返回指定的引用）305
技巧272解決合併單元格引用數據列出現跳躍的問題306
技巧273按指定的範圍計算平均值307
技巧274INDIRECT解決跨工作表查詢時名稱匹配問題309
13.OFFSET函式（根據指定偏移量得到新引用）310
技巧275實現數據的動態查詢311
技巧276對每日出庫量累計求和312
技巧277OFFSET函式常用於創建動態圖表數據源313
第9章信息函式範例316
9.1使用IS函式進行判斷316
1.ISBLANK函式（判斷值是否為空值）316
技巧278標註出缺考學生316
2.ISTEXT函式（判斷數據是否為文本）317
技巧279快速統計缺考人數317
3.ISLOGICAL函式（判斷數據是否為邏輯值）318
技巧280檢驗數據是否為邏輯值318
4.ISNUMBER函式（判斷數據是否為數字）318
技巧281統計實考人數319
技巧282統計銷售量總計值319
5.ISNA函式（判斷數據是否為錯誤值“N/A”）320
技巧283檢驗數據是否為“N/A”錯誤值320
6.ISERR函式（判斷數據是否為“N/A”之外的任意錯誤值）321
技巧284檢驗數據是否為“N/A”之外的任意錯誤值321
7.ISODD函式（判斷數據是否為奇數）321
技巧285根據身份證號碼判斷其性別1322
8.ISEVEN函式（判斷數據是否為偶數）322
技巧286根據身份證號碼判斷其性別2323
9.2返回相關值323
9.CELL函式（返回單元格、位置等）323
技巧287判斷測試結果是否達標324
10.ERROR.TYPE函式（返回錯誤對應的編號）325
技巧288根據錯誤代碼顯示錯誤原因326
11.N函式（返迴轉換為數值後的值）326
技巧289用訂單生成日期的序列號與當前行號生成訂單的編號327
12.TYPE函式（返回數值類型）327
技巧290返回數值對應的類型數字328
