Excel經(jīng)典教程（公式與函數(shù)）

第 1章 將區(qū)域使用到極致　1
1．1 高級(jí)區(qū)域選擇技巧　1
1．1．1 鼠標(biāo)區(qū)域選擇竅門　1
1．1．2 鍵盤區(qū)域選擇竅門　2
1．1．3 三維區(qū)域選擇　2
1．1．4 利用【定位】對(duì)話框選擇區(qū)域　3
1．1．5 利用【定位條件】對(duì)話框選擇區(qū)域　4
1．2 在區(qū)域中快速選擇單元格　8
1．3 區(qū)域的填充　9
1．4 使用填充柄　9
1．4．1 使用自動(dòng)填充來創(chuàng)建序列文本和序列數(shù)值　9
1．4．2 創(chuàng)建自己的自動(dòng)填充表　11
1．4．3 填充區(qū)域　12
1．5 創(chuàng)建序列　12
1．6 高級(jí)區(qū)域復(fù)制技巧　13
1．6．1 復(fù)制選定單元格的屬性　14
1．6．2 用運(yùn)算方法合并源單元格與目標(biāo)單元格　15
1．6．3 行與列的轉(zhuǎn)置　15
1．7 清除區(qū)域內(nèi)容　16
1．8 在區(qū)域中應(yīng)用條件格式　16
1．8．1 創(chuàng)建突出顯示單元格規(guī)則　17
1．8．2 創(chuàng)建項(xiàng)目選取規(guī)則　18
1．8．3 添加數(shù)據(jù)條　20
1．8．4 添加色階　22
1．8．5 添加圖標(biāo)集　24
第 2章 使用區(qū)域名稱　26
2．1 定義區(qū)域名稱　26
2．1．1 使用【名稱框】　27
2．1．2 使用【新建名稱】對(duì)話框　28
2．1．3 改變范圍來定義表級(jí)名稱　29
2．1．4 使用工作表文本框定義名稱　30
2．1．5 命名常量　32
2．2 使用區(qū)域名稱　33
2．2．1 應(yīng)用區(qū)域名稱　33
2．2．2 使用自動(dòng)填充　35
2．2．3 區(qū)域名稱導(dǎo)航　36
2．2．4 在工作表中粘貼區(qū)域名稱列表　36
2．2．5 展開【名稱管理器】對(duì)話框　36
2．2．6 篩選名稱　37
2．2．7 編輯區(qū)域名稱的坐標(biāo)　37
2．2．8 自動(dòng)調(diào)整區(qū)域名稱坐標(biāo)　37
2．2．9 改變區(qū)域名稱　39
2．2．10 刪除區(qū)域名稱　39
2．2．11 區(qū)域名稱的交集操作　39
第3章 建立基本公式　41
3．1 了解公式的基礎(chǔ)知識(shí)　41
3．1．1 Excel公式中的一些數(shù)量限制　41
3．1．2 輸入和編輯公式　42
3．1．3 使用運(yùn)算公式　43
3．1．4 使用比較公式　43
3．1．5 使用文本公式　44
3．1．6 使用引用函數(shù)　44
3．2 了解運(yùn)算符的優(yōu)先級(jí)　45
3．2．1 優(yōu)先順序　45
3．2．2 管理優(yōu)先順序　46
3．3 管理工作表運(yùn)算　47
3．4 復(fù)制和移動(dòng)公式　49
3．4．1 了解相對(duì)引用格式　50
3．4．2 了解絕對(duì)引用格式　51
3．4．3 復(fù)制公式但不調(diào)整相對(duì)引用　52
3．5 顯示工作表公式　52
3．6 轉(zhuǎn)換公式為數(shù)據(jù)　53
3．7 在公式中使用區(qū)域名稱　53
3．7．1 將名稱粘貼到公式中　54
3．7．2 在公式中應(yīng)用名稱　54
3．7．3 命名公式　57
3．8 在公式中使用鏈接　57
3．8．1 了解外部引用　58
3．8．2 更新鏈接　59
3．8．3 改變鏈接源　60
3．9 格式化數(shù)字、日期和時(shí)間　60
3．9．1 數(shù)字顯示格式　60
3．9．2 日期和時(shí)間顯示格式　67
3．9．3 刪除自定義格式　70
第4章 創(chuàng)建高級(jí)公式　71
4．1 使用數(shù)組　71
使用數(shù)組公式　71
4．2 理解數(shù)組公式　72
數(shù)組公式在多重區(qū)域中的操作　73
4．3 使用數(shù)組常量　74
函數(shù)：使用或返回?cái)?shù)組　74
4．4 使用迭代計(jì)算和循環(huán)引用　76
4．5 合并多表數(shù)據(jù)　78
4．5．1 按位置進(jìn)行合并計(jì)算　78
4．5．2 按分類進(jìn)行合并計(jì)算　81
4．6 在單元格中應(yīng)用數(shù)據(jù)有效性規(guī)則　83
4．7 在工作表中使用對(duì)話框控件　85
4．7．1 顯示開發(fā)工具標(biāo)簽　85
4．7．2 使用表單控件　86
4．7．3 給工作表添加控件　86
4．7．4 鏈接控件與單元格數(shù)值　87
4．7．5 了解工作表控件　87
第5章 解決公式中的問題　92
5．1 了解Excel中的錯(cuò)誤值　92
5．1．1 #DIV/0!　92
5．1．2 #N/A　93
5．1．3 #NAME？　93
5．1．4 #NULL!　95
5．1．5 #NUM!　95
5．1．6 #REF!　95
5．1．7 #VALUE!　96
5．2 解決公式中的其他問題　96
5．2．1 括號(hào)的缺失或不匹配　96
5．2．2 錯(cuò)誤的公式結(jié)果　97
5．2．3 解決循環(huán)引用　98
5．3 使用IFERROR( )函數(shù)來解決公式中的問題　99
5．4 使用公式錯(cuò)誤檢查功能　99
設(shè)置錯(cuò)誤檢查選項(xiàng)　101
5．5 審核工作表　103
5．5．1 理解審核　103
5．5．2 追蹤引用單元格　104
5．5．3 追蹤從屬單元格　104
5．5．4 追蹤錯(cuò)誤　105
5．5．5 移去追蹤箭頭　105
5．5．6 公式求值　105
第6章 理解函數(shù)　107
6．1 關(guān)于Excel的函數(shù)　107
6．2 函數(shù)的構(gòu)成　108
6．3 在公式中輸入函數(shù)　110
6．4 使用函數(shù)插入功能　111
6．5 加載分析工具庫　113
第7章 使用文本函數(shù)　114
7．1 Excel的文本函數(shù)　114
7．2 使用字符和代碼　115
7．2．1 CHAR( )函數(shù)　115
7．2．2 CODE( )函數(shù)　118
7．3 轉(zhuǎn)換文本　119
7．3．1 LOWER( )函數(shù)　119
7．3．2 UPPER( )函數(shù)　119
7．3．3 PROPER( )函數(shù)　120
7．4 格式化文本　120
7．4．1 DOLLAR( )函數(shù)　120
7．4．2 FIXED( )函數(shù)　121
7．4．3 TEXT( )函數(shù)　121
7．4．4 顯示工作簿最近更新　122
7．5 從字符串中刪掉不需要的字符　122
7．5．1 TRIM( )函數(shù)　122
7．5．2 CLEAN( )函數(shù)　123
7．5．3 REPT( )函數(shù)：重復(fù)輸入字符　124
7．5．4 REPT( )函數(shù)：填充單元格　124
7．5．5 REPT( )函數(shù)：繪制文本圖表　124
7．6 提取子串　126
7．6．1 LEFT( )函數(shù)　126
7．6．2 RIGHT( )函數(shù)　126
7．6．3 MID( )函數(shù)　126
7．6．4 將文本更改為句首字母大寫　127
7．6．5 日期轉(zhuǎn)換公式　127
7．7 查找子串　128
7．7．1 FIND( )函數(shù)和SEARCH( )函數(shù)　128
7．7．2 案例分析：生成賬號(hào)1　128
7．7．3 提取名或姓　129
7．7．4 提取名、姓及中間名縮寫　130
7．7．5 確定列標(biāo)簽字母　131
7．8 用一個(gè)子串來代替另一個(gè)　132
7．8．1 REPLACE( )函數(shù)　132
7．8．2 SUBSTITUTE( )函數(shù)　132
7．8．3 從字符串中刪除字符　133
7．8．4 從字符串中刪除兩個(gè)不同的字符　133
7．8．5 案例分析：生成賬號(hào)2　134
7．8．6 刪除換行符　134
第8章 使用邏輯函數(shù)和信息函數(shù)　135
8．1 在邏輯函數(shù)中添加智能　135
8．1．1 使用IF( )函數(shù)　136
8．1．2 完成復(fù)合邏輯測(cè)試　138
8．1．3 AND( )函數(shù)　140
8．1．4 OR( )函數(shù)　141
8．1．5 將邏輯函數(shù)與數(shù)組混合使用　143
8．2 案例分析：建立應(yīng)收賬款賬齡表　148
8．2．1 計(jì)算更精確的到期日　149
8．2．2 將逾期發(fā)票按時(shí)間分類　150
8．3 通過信息函數(shù)來找到數(shù)據(jù)　151
8．3．1 CELL( )函數(shù)　152
8．3．2 ERROR．TYPE( )函數(shù)　154
8．3．3 INFO( )函數(shù)　156
8．3．4 IS函數(shù)　157
第9章 使用查找函數(shù)　159
9．1 了解查找表　160
9．2 CHOOSE( )函數(shù)　160
9．2．1 定義一周中某天的名稱　161
9．2．2 定義財(cái)務(wù)年度月份　161
9．2．3 核算加權(quán)問卷　162
9．2．4 合并CHOOSE( )函數(shù)和工作表選項(xiàng)按鈕　163
9．2．5 在表格中查找值　163
9．3 VLOOKUP( )函數(shù)　164
9．4 HLOOKUP( )函數(shù)　164
9．5 使用區(qū)域查找返回客戶折扣率　165
9．6 使用區(qū)域查找返回稅率　166
9．7 查找精確匹配　167
9．7．1 查找客戶賬號(hào)　167
9．7．2 精確匹配查找和單元格內(nèi)組合框的結(jié)合使用　168
9．8 高級(jí)查找操作　169
9．8．1 MATCH( )和INDEX( )函數(shù)　169
9．8．2 使用列表框查找值　170
9．8．3 使用任意列作為查找列　171
9．8．4 創(chuàng)建行-列查找　172
9．8．5 創(chuàng)建多列查找　173
第 10章 使用日期和時(shí)間函數(shù)　174
10．1 Excel是如何處理日期和時(shí)間的　174
10．1．1 輸入日期和時(shí)間　175
10．1．2 Excel和兩位數(shù)的年份　176
10．2 使用Excel的日期函數(shù)　176
10．2．1 返回一個(gè)完整的日期　177
10．2．2 返回日期的組成部分　179
10．2．3 計(jì)算兩個(gè)日期之間的天數(shù)差　186
10．3 使用Excel的時(shí)間函數(shù)　189
10．3．1 返回一個(gè)時(shí)間　190
10．3．2 返回部分時(shí)間　191
10．3．3 計(jì)算兩個(gè)時(shí)間之間的時(shí)間差　192
10．4 案例分析：建立員工考勤表　193
10．4．1 輸入考勤表數(shù)據(jù)　194
10．4．2 計(jì)算每日工作時(shí)間　194
10．4．3 計(jì)算每周工作時(shí)間　195
10．4．4 計(jì)算周薪　196
第 11章 使用數(shù)學(xué)函數(shù)　197
11．1 了解Excel的四舍五入函數(shù)　199
11．1．1 ROUND( )函數(shù)　200
11．1．2 MROUND( )函數(shù)　200
11．1．3 ROUNDDOWN( )和ROUNDUP( )函數(shù)　201
11．1．4 CEILING( )和FLOOR( )函數(shù)　202
11．1．5 EVEN( )和ODD( )函數(shù)　203
11．1．6 INT( )和TRUNC( )函數(shù)　203
11．1．7 使用四舍五入來避免誤差　204
11．1．8 設(shè)置價(jià)格點(diǎn)　204
11．2 案例分析：四舍五入計(jì)費(fèi)時(shí)間　205
11．3 數(shù)值求和　205
SUM( )函數(shù)　206
11．4 MOD( )函數(shù)　207
11．5 生成隨機(jī)數(shù)　210
11．5．1 RAND( )函數(shù)　210
11．5．2 RANDBETWEEN( )函數(shù)　213
第 12章 使用統(tǒng)計(jì)函數(shù)　214
12．1 了解描述統(tǒng)計(jì)學(xué)　216
12．2 使用COUNT( )函數(shù)計(jì)數(shù)　216
12．3 計(jì)算平均值　217
12．3．1 AVERAGE( )函數(shù)　217
12．3．2 MEDIAN( )函數(shù)　217
12．3．3 MODE( )函數(shù)　218
12．3．4 計(jì)算加權(quán)平均值　218
12．4 計(jì)算極值　219
12．4．1 MAX( )和MIN( )函數(shù)　220
12．4．2 LARGE( )和SMALL( )函數(shù)　220
12．4．3 對(duì)排在前k位的值執(zhí)行計(jì)算　221
12．4．4 對(duì)排在后k位的值執(zhí)行計(jì)算　222
12．5 計(jì)算差異　222
12．5．1 計(jì)算全距　222
12．5．2 計(jì)算方差　222
12．5．3 計(jì)算標(biāo)準(zhǔn)差　223
12．6 使用頻數(shù)分布　224
12．6．1 FREQUENCY( )函數(shù)　225
12．6．2 了解正態(tài)分布和NORMDIST( ) 函數(shù)　226
12．6．3 曲線形狀1：SKEW( )函數(shù)　228
12．6．4 曲線形狀2：KURT( )函數(shù)　229
12．7 使用分析工具庫中的數(shù)據(jù)分析 工具　230
12．7．1 使用描述統(tǒng)計(jì)工具　232
12．7．2 使用相關(guān)系數(shù)工具　234
12．7．3 使用直方圖工具　236
12．7．4 使用隨機(jī)數(shù)發(fā)生器工具　237
12．7．5 使用排位與百分比排位工具　240
第 13章 使用表分析數(shù)據(jù)　242
13．1 將區(qū)域轉(zhuǎn)換為表　243
13．2 表的基本操作　244
13．3 給表格內(nèi)數(shù)據(jù)排序　246
13．3．1 執(zhí)行更復(fù)雜的排序　246
13．3．2 按照自然順序給表格排序　247
13．3．3 給域內(nèi)的一部分排序　249
13．3．4 排序時(shí)忽略冠詞　250
13．4 篩選表格數(shù)據(jù)　250
13．4．1 利用【篩選】功能來篩選 數(shù)據(jù)　251
13．4．2 使用復(fù)雜條件篩選表格　254
13．4．3 輸入估算條件　257
13．4．4 復(fù)制篩選數(shù)據(jù)到不同的區(qū)域　258
13．5 在公式中引用表　259
13．5．1 使用表說明符　260
13．5．2 輸入表公式　261
13．6 Excel的表函數(shù)　263
13．6．1 關(guān)于表函數(shù)　263
13．6．2 不需要條件區(qū)域的表函數(shù)　263
13．6．3 接受多重條件的表函數(shù)　265
13．6．4 需要條件區(qū)域的表函數(shù)　268
13．6．5 案例分析：在“缺陷”數(shù)據(jù)庫中 應(yīng)用統(tǒng)計(jì)表函數(shù)　271
第 14章 使用數(shù)據(jù)透視表分析 數(shù)據(jù)　273
14．1 什么是數(shù)據(jù)透視表　273
數(shù)據(jù)透視表是如何工作的　274
14．2 建立數(shù)據(jù)透視表　276
14．2．1 由表或區(qū)域建立數(shù)據(jù)透視表　276
14．2．2 從外部數(shù)據(jù)中建立數(shù)據(jù) 透視表　279
14．2．3 使用并自定義數(shù)據(jù)透視表　280
14．3 使用數(shù)據(jù)透視表的分類匯總 功能　281
14．4 更改數(shù)據(jù)字段的匯總計(jì)算　282
14．4．1 使用差異匯總計(jì)算　283
14．4．2 切換：差異計(jì)算　285
14．4．3 使用百分比匯總計(jì)算　285
14．4．4 按某一字段匯總　287
14．4．5 使用指數(shù)匯總計(jì)算　289
14．5 創(chuàng)建自定義數(shù)據(jù)透視表 計(jì)算　290
14．5．1 創(chuàng)建計(jì)算字段　291
14．5．2 創(chuàng)建計(jì)算項(xiàng)　293
14．6 案例分析：使用計(jì)算項(xiàng)制定 預(yù)算　294
14．7 在工作表公式中使用數(shù)據(jù) 透視表結(jié)果　296
第 15章 使用Excel的商業(yè)模型工具　298
15．1 使用模擬分析　298
15．2 使用單變量求解　304
15．3 使用方案　311
第 16章 使用回歸分析追蹤趨勢(shì)并 作出預(yù)測(cè)　318
16．1 選擇回歸分析方法　318
16．2 在線性數(shù)據(jù)中使用簡單回歸　319
16．3 案例分析：季節(jié)性銷售模型的 趨勢(shì)分析和預(yù)測(cè)　331
16．4 在非線性數(shù)據(jù)中使用簡單 回歸　338
第 17章 使用規(guī)劃求解解決復(fù)雜 問題　353
17．1 關(guān)于規(guī)劃求解的一些背景　353
17．2 加載規(guī)劃求解　354
17．3 使用規(guī)劃求解　355
17．4 添加約束　357
17．5 將解保存為方案　359
17．6 設(shè)置規(guī)劃求解的其他選項(xiàng)　360
17．7 理解規(guī)劃求解的信息　364
17．8 案例分析：解決運(yùn)輸問題　365
17．9 顯示規(guī)劃求解報(bào)告　368
第 18章 建立貸款公式　372
18．1 了解貨幣的時(shí)間價(jià)值　372
18．2 計(jì)算貸款償還　373
18．3 制定貸款分期償還計(jì)劃表　378
18．4 計(jì)算貸款期限　381
18．5 計(jì)算貸款所需的利率　383
18．6 計(jì)算可借金額　384
18．7 案例分析：抵押貸款　384
第 19章 建立投資公式　389
19．1 使用利率　389
19．2 計(jì)算將來值　391
19．3 達(dá)到投資目標(biāo)　393
19．4 案例分析：建立投資計(jì)劃表　397
第 20章 建立貼現(xiàn)公式　400
20．1 計(jì)算現(xiàn)值　400
20．2 貼現(xiàn)現(xiàn)金流　404
20．3 計(jì)算投資回收期　409
20．4 案例分析：出版一本書　414