Excel高效辦公：函數(shù)與圖表（修訂版）

第1章　Excel函數(shù)的基礎知識
1.1　公式概述　2
1.1.1　公式的組成　2
1.1.2　公式的輸入　3
1.2　引用　3
1.2.1　引用樣式　3
1.2.2　相對引用和絕對引用　5
1.3　函數(shù)的基礎應用　6
1.3.1　使用名稱　6
1.3.2　Excel函數(shù)簡介　8
1.3.3　函數(shù)的使用　9
1. 函數(shù)的輸入　9
2. 修改函數(shù)　11
1.3.4　公式中的錯誤值　11
1.3.5　函數(shù)的分類　13第2章　日期和時間函數(shù)的典型應用
2.1　Excel中日期和時間的有關介紹　16
2.1.1　Excel的日期和時間處理　16
2.1.2　日期系統(tǒng)　17
2.1.3　兩位數(shù)年份問題及解決方法　17
2.2　用來計算日期的函數(shù)　18
2.2.1　使用DATE及DATEVALUE函數(shù)計算日期編號　18
2.2.2　使用NOW及TODAY函數(shù)顯示當前日期　21
2.2.3　使用YEAR函數(shù)轉換日期的年份　22
2.2.4　使用MONTH函數(shù)轉換日期的月份　22
2.2.5　使用DAY函數(shù)轉換日期的天數(shù)　23
2.2.6　使用DAYS360函數(shù)計算日期相差天數(shù)　23
2.2.7　使用WEEKDAY函數(shù)確定星期幾　24
2.3　用來計算時間的函數(shù)　24
2.3.1　使用TIME及TIMEVALUE函數(shù)計算時間　25
2.3.2　使用HOUR函數(shù)轉換小時數(shù)　26
2.3.3　使用MINUTE函數(shù)轉換分鐘數(shù)　26
2.3.4　使用SECOND函數(shù)轉換秒數(shù)　26
2.4　綜合實例　28
2.4.1　有關節(jié)假日的計算　28
2.4.2　有關火車站寄存包裹收費的計算　30
實例拓展　33第3章　數(shù)學和三角函數(shù)的典型應用
3.1　常用的數(shù)學函數(shù)　36
3.1.1　使用ABS函數(shù)計算絕對值　36
3.1.2　使用SUM函數(shù)求和　37
3.1.3　使用SUMIF函數(shù)按條件求和　37
3.1.4　使用EXP和POWER函數(shù)求冪　38
3.1.5　使用FACT函數(shù)計算階乘　39
3.1.6　使用LN、LOG、LOG10函數(shù)計算對數(shù)　39
3.1.7　使用PRODUCT函數(shù)計算多個數(shù)字的乘積　40
3.1.8　使用COMBIN函數(shù)計算組合數(shù)　40
3.1.9　使用ODD或EVEN可將數(shù)值舍入 為奇數(shù)或偶數(shù)　41
3.1.10　使用INT函數(shù)向下取整　42
3.1.11　使用MOD函數(shù)取相除兩數(shù)的 余數(shù)　43
3.1.12　使用RAND函數(shù)得到隨機數(shù)　44
3.1.13　使用ROUNDDOWN、ROUNDUP 函數(shù)按位數(shù)向下、上舍入　45
3.2　基本的三角函數(shù)　45
3.2.1　使用DEGREES和RADIANS函數(shù)轉換度和弧度　45
3.2.2　正三角函數(shù)SIN、CON、TAN　46
3.2.3　反三角函數(shù)ASIN、ACOS、ATAN　47
3.3　數(shù)組函數(shù)　47
3.3.1　使用SUMPRODUCT函數(shù)計算數(shù)組乘積的和　48
3.3.2　使用SUMX2MY2函數(shù)計算平方差的和　48
3.3.3　使用SUMX2PY2函數(shù)計算平方和的和　49
3.3.4　使用SUMXMY2函數(shù)計算差的平方和　49
3.3.5　使用MDETERM函數(shù)計算行列式的值　50
3.4　綜合實例　50
3.4.1　根據(jù)產(chǎn)品銷售情況動態(tài)統(tǒng)計庫存以及獎金提成　50
3.4.2　運用數(shù)學函數(shù)計算工資及所需票面金額　52
實例拓展　54第4章　查詢和引用函數(shù)的典型應用
4.1　常用的查詢和引用函數(shù)　58
4.1.1　使用LOOKUP函數(shù)查找數(shù)據(jù)　58
4.1.2　使用HLOOKUP函數(shù)實現(xiàn)水平查找　60
4.1.3　使用VLOOKUP函數(shù)實現(xiàn)豎直查找　62
4.1.4　使用MATCH函數(shù)實現(xiàn)在數(shù)組中查找值　64
4.1.5　使用CHOOSE函數(shù)實現(xiàn)從列值中查找值　65
4.1.6　使用INDEX函數(shù)得到指定的內(nèi)容　66
4.2　綜合實例　68
4.2.1　在員工季度考核中使用查詢函數(shù)　69
4.2.2　在員工的銷售評定中使用查詢函數(shù)　70
實例拓展　73第5章　統(tǒng)計函數(shù)的典型應用
5.1　一般的統(tǒng)計函數(shù)　80
5.1.1　使用AVERAGE和AVERAGEA函數(shù)求平均值　80
5.1.2　使用COUNT和COUNTA函數(shù)完成統(tǒng)計單元格個數(shù)　81
5.1.3　使用COUNTIF函數(shù)實現(xiàn)按條件統(tǒng)計　83
5.1.4　使用COUNTBLANK函數(shù)統(tǒng)計空白單元格的個數(shù)　84
5.1.5　使用FREQUENCY函數(shù)統(tǒng)計頻率分布　85
5.1.6　使用MODE函數(shù)返回出現(xiàn)頻率最多的數(shù)值　85
5.1.7　使用MEDIAN函數(shù)返回中值　86
5.1.8　使用LARGE和SMALL函數(shù)按條件返回最值　87
5.1.9　使用MAX和MAXA函數(shù)返回最大值　89
5.1.10　使用MIN和MINA函數(shù)返回最小值　90
5.1.11　使用RANK函數(shù)返回數(shù)字的排位　93
5.1.12　使用GROWTH函數(shù)預測指數(shù) 增長值　94
5.2　數(shù)理統(tǒng)計函數(shù)　95
5.2.1　使用VAR和VARA函數(shù)計算樣本方差　95
5.2.2　使用STDEV和STDEVA函數(shù)計算樣本的標準偏差　96
5.2.3　使用NORMSDIST函數(shù)返回標準正態(tài)分布　96
5.2.4　使用FDIST函數(shù)得到F概率分布　97
5.2.5　使用CORREL函數(shù)得到相關系數(shù)　98
5.3　綜合實例　99
5.3.1　統(tǒng)計分析員工在職培訓成績表　99
5.3.2　根據(jù)裁判打分計算選手最后得分　104
實例拓展　106第6章　財務函數(shù)的典型應用
6.1　有關本金和利息計算的函數(shù)　110
6.1.1　使用PMT函數(shù)計算貸款的每期償還額　110
6.1.2　使用PPMT和IPMT函數(shù)計算還款中的本金和利息　112
6.1.3　使用CUMPRINC和CUMIPMT函數(shù)計算階段本金和利息　115
6.2　有關投資計算的函數(shù)　116
6.2.1　使用PV函數(shù)計算投資的現(xiàn)值　117
6.2.2　使用NPV函數(shù)計算非固定回報投資的凈現(xiàn)值　117
6.2.3　使用FV函數(shù)計算某筆投資的未來值　119
6.3　有關報酬率計算的函數(shù)　120
6.3.1　使用IRR函數(shù)計算現(xiàn)金流的內(nèi)部收益率　121
6.3.2　使用MIRR函數(shù)計算現(xiàn)金流的修正內(nèi)部收益率　122
6.4　有關折舊值計算的函數(shù)　123
6.4.1　SLN函數(shù)與直線折舊法　123
6.4.2　DB函數(shù)與固定余額遞減折舊法　124
6.4.3　VDB函數(shù)與可變余額遞減折舊法　125
6.4.4　DDB函數(shù)與雙倍余額遞減折舊法　127
6.4.5　SYD函數(shù)與年限總和折舊法　128
6.4.6　使用Excel函數(shù)計算的幾種折舊法的比較　129
6.5　有關證券計算的函數(shù)　131
6.5.1　使用INTRATE函數(shù)計算一次性付息證券的利率　131
6.5.2　使用PRICE函數(shù)計算定期付息有價證券的價格　132
6.5.3　使用ACCRINT函數(shù)計算有價證券的應計利息　133
6.6　綜合實例　134
6.6.1　在貸款經(jīng)營表中運用財務函數(shù)　134
6.6.2　在住房貸款中運用財務函數(shù)　138
實例拓展　140第7章　邏輯函數(shù)的典型應用
7.1　使用IF函數(shù)按條件返回值　144
7.2　使用FALSE和TRUE函數(shù)得到邏輯值　145
7.3　使用AND函數(shù)計算邏輯值的交集　145
7.4　使用OR函數(shù)計算邏輯值的并集　147
7.5　使用NOT函數(shù)對邏輯值求反　149
7.6　綜合實例——運用邏輯函數(shù)判斷閏年　149第8章　文本函數(shù)的典型應用
8.1　使用CHAR函數(shù)將代碼轉換為對應的字符　152
8.2　使用CODE函數(shù)將字符轉換為對應的代碼　152
8.3　使用FIND和FINDB函數(shù)查找文本字符串　153
8.4　使用SEARCH和SEARCHB函數(shù)查找文本字符串　154
8.5　使用EXACT函數(shù)比較字符串是否完全相同　154
8.6　使用CONCATENATE函數(shù)實現(xiàn)合并字符串　155
8.7　使用LEFT和LEFTB函數(shù)返回左邊字符　155
8.8　使用RIGHT和RIGHTB函數(shù)返回右邊字符　157
8.9　使用MID和MIDB函數(shù)查找特定的字符　158
8.10　使用LEN和LENB函數(shù)計算字符 串長度　159
8.11　使用UPPER、LOWER函數(shù)將文本 轉換成大、小寫　161
8.12　使用TEXT函數(shù)將數(shù)值轉換為文本　162
8.13　使用VALUE函數(shù)將文本轉換為數(shù)值　162
8.14　使用REPLACE和REPLACEB函數(shù) 替換文本　163
8.15　使用SUBSTITUTE函數(shù)替換文本　164
8.16　使用REPT函數(shù)復制文本　165
8.17　使用TRIM函數(shù)清除文本中的空格　166
8.18　使用CLEAN函數(shù)刪除不能打印的 字符　167
8.19　綜合實例——運用文本函數(shù)處理人員信息　167
實例拓展　170第9章　數(shù)據(jù)庫函數(shù)的典型應用
9.1　使用DAVERAGE函數(shù)計算條目的平均值　174
9.2　使用DPRODUCT函數(shù)計算指定數(shù)值的乘積　175
9.3　使用DSUM函數(shù)計算指定數(shù)字之和　176
9.4　使用DCOUNT函數(shù)計算含有數(shù)字的單元格個數(shù)　177
9.5　使用DGET函數(shù)提取指定條件的值　178
9.6　使用DMAX、DMIN函數(shù)得到符合條件的最值　180
9.7　使用DVAR、DVARP函數(shù)計算方差　182
9.8　使用DSTDEV、DSTDEVP函數(shù)計算標準偏差　183
實例拓展　185第10章　信息函數(shù)的典型應用
10.1　IS類信息函數(shù)　190
10.1.1　使用ISBLANK函數(shù)判斷單元格是否為空白　190
10.1.2　使用ISERR、ISERROR函數(shù)判斷參數(shù)是否為錯誤值　191
10.1.3　使用ISNA函數(shù)判斷錯誤值是否為#N/A　192
10.1.4　使用ISLOGICAL函數(shù)判斷參數(shù)是否為邏輯值　193
10.1.5　使用ISNONTEXT函數(shù)判斷參數(shù)是否為非字符串　194
10.1.6　使用ISTEXT函數(shù)判斷參數(shù)是否為文本　194
10.1.7　使用ISNUMBER函數(shù)判斷參數(shù)是否為數(shù)字　195
10.1.8　使用ISREF函數(shù)判斷參數(shù)是否為引用　195
10.2　其他信息函數(shù)　195
10.2.1　使用CELL函數(shù)得到單元格信息　196
10.2.2　使用ERROR.TYPE函數(shù)判斷參數(shù)的錯誤類型　197
10.2.3　使用TYPE函數(shù)得到數(shù)值的類型　198
10.2.4　使用N函數(shù)返回轉換為數(shù)字的值　199
10.2.5　使用INFO函數(shù)得到當前操作環(huán)境的信息　199第11章　工程函數(shù)的典型應用
11.1　貝塞爾(BESSEL)函數(shù)　202
11.1.1　使用BESSELI函數(shù)計算修正的貝塞爾函數(shù)值In(x)　202
11.1.2　使用BESSELK函數(shù)計算修正的貝塞爾函數(shù)值Kn(x)　202
11.1.3　使用BESSELJ函數(shù)計算貝塞爾函數(shù)值Jn(x)　203
11.1.4　使用BESSELY函數(shù)計算貝塞爾函數(shù)值Yn(x)　203
11.2　各種進制之間的轉換　204
11.2.1　使用DEC2BIN函數(shù)將十進制轉換成二進制　204
11.2.2　使用BIN2DEC函數(shù)將二進制轉換成十進制　205
11.2.3　使用OCT2BIN函數(shù)將八進制轉換成二進制　205
11.2.4　使用OCT2DEC函數(shù)將八進制轉換成十進制　206
11.2.5　使用HEX2BIN函數(shù)將十六進制轉換成二進制　206
11.2.6　使用HEX2DEC函數(shù)將十六進制轉換成十進制　207
11.3　與復數(shù)有關的函數(shù)　208
11.3.1　使用IMABS函數(shù)求復數(shù)的?！?08
11.3.2　使用IMREAL和IMAGINARY函數(shù)求實、虛系數(shù)　208
11.3.3　使用IMCONJUGATE函數(shù)求共軛復數(shù)　209
11.3.4　使用IMSUM、IMSUB函數(shù)求復數(shù)的和與差　209
11.4　使用DELTA函數(shù)檢測兩個值是否相等　209
11.5　使用CONVERT函數(shù)進行度量衡轉換　210
11.6　使用ERF、ERFC函數(shù)計算誤差　211第12章　圖表的基礎知識
12.1　圖表的組成　216
12.2　圖表的創(chuàng)建　217
12.3　圖表的類型　222
12.3.1　柱形圖　222
12.3.2　條形圖　223
12.3.3　折線圖　223
12.3.4　餅圖　224
12.3.5　XY散點圖　225
12.3.6　圓環(huán)圖　226
12.3.7　自定義圖表　226第13章　圖表的編輯
13.1　調(diào)整圖表位置和大小　228
13.2　設置圖表區(qū)字體　228
13.3　修改圖表的類型　229
13.4　為圖表添加或者刪除數(shù)據(jù)系列　230
13.5　為圖表添加趨勢線　232
13.6　圖表坐標軸的屬性設置　234
13.7　數(shù)據(jù)系列格式的設置　235
13.8　修飾圖表標題、繪圖區(qū)以及圖表區(qū)　237
13.9　顯示或刪除圖例　238第14章　柱形圖和條形圖的典型應用
14.1　柱形圖　240
14.1.1　繪制企業(yè)貸款還息圖表　240
14.1.2　繪制銷量圖表　244
14.1.3　繪制任務進程圖表　248
14.1.4　繪制員工業(yè)績考核圖表　252
14.2　條形圖　255
14.2.1　比較產(chǎn)品的接受群體　255
14.2.2　繪制項目進度圖表　260
14.2.3　繪制每天新用戶數(shù)量圖表　264
實例拓展　267第15章　折線圖和XY散點圖的典型應用
15.1　折線圖　270
15.1.1　繪制一年的日銷售額趨勢圖表　270
15.1.2　根據(jù)量本利分析繪制產(chǎn)品的盈虧圖表　273
15.1.3　繪制四分位點圖表　277
15.2　XY散點圖　281
15.2.1　繪制函數(shù)圖像　282
15.2.2　繪制廣告與銷量的關系圖表　284
15.2.3　繪制薪水分布圖表　287
15.2.4　繪制數(shù)據(jù)變化的階梯圖表　290
實例拓展　294第16章　餅圖和圓環(huán)圖的典型應用
16.1　餅圖　298
16.1.1　繪制年度支出比例圖表　298
16.1.2　繪制半圓形進程圖表　301
16.2　圓環(huán)圖　304
16.2.1　繪制公司支出比例圖表　304
16.2.2　利用儀表式的圖表反映儀器工作狀態(tài)　307
實例拓展　311第17章　其他圖表的典型應用
17.1　股價圖　314
17.2　面積圖　318
17.3　雷達圖　322第18章　數(shù)據(jù)透視表和數(shù)據(jù)透視圖
18.1　數(shù)據(jù)透視表簡介　326
18.2　數(shù)據(jù)透視表的創(chuàng)建　326
18.3　數(shù)據(jù)透視表的規(guī)劃管理　330
18.3.1　使用【數(shù)據(jù)透視表字段列表】規(guī)劃數(shù)據(jù)透視表　330
18.3.2　使用【數(shù)據(jù)透視表】工具欄管理數(shù)據(jù)透視表　331
18.4　數(shù)據(jù)透視表應用實例　332
18.5　數(shù)據(jù)透視圖的創(chuàng)建　339
18.6　數(shù)據(jù)透視圖的特點　341第19章　函數(shù)和圖表的綜合應用
19.1　火車站寄存包裹費用的計算及分布圖　344
19.2　員工業(yè)務能力的查詢及等級表　347
19.3　銷量提成的計算及相關圖表　353附錄　圖表技巧高級技法　361
附錄1　使用圖片圖表繪制銷售量　361
附錄2　繪制有數(shù)據(jù)間隔的圖表　364
附錄2.1　手動隱藏數(shù)據(jù)　364
附錄2.2　使用自動篩選繪制　366
附錄3　創(chuàng)建下拉菜單式圖表　368
附錄4　繪制有滾動條的圖表　372
附錄5　在圖表中顯示系列的最大值和最小值　376