Excel 2013從新手到高手：公式、函數(shù)、圖表與數(shù)據(jù)分析

第1部分　基本操作
第1章　數(shù)據(jù)的編輯處理　1
1．1 創(chuàng)建“來賓簽到表”　2
1．1．1 工作簿的基本操作　2
1． 新建和保存工作簿　2
2． 保護(hù)工作簿結(jié)構(gòu)　3
3． 加密保護(hù)工作簿　4
4． 共享工作簿　6
1．1．2 工作表的基本操作　6
1． 添加和刪除工作表　6
2． 在同一工作簿中移動(dòng)或復(fù)制工作表　7
3． 在不同工作簿中移動(dòng)或復(fù)制工作表　8
4． 重命名工作表　8
5． 設(shè)置工作表標(biāo)簽顏色　9
6． 隱藏與顯示工作表　9
1．1．3 單元格的基本操作　10
1． 插入與刪除單元格　10
2． 合并和拆分單元格　12
3． 調(diào)整單元格的行高和列寬　12
1．2 創(chuàng)建“來訪登記表”　13
1．2．1 數(shù)據(jù)的輸入　13
1． 輸入一般數(shù)據(jù)　13
2． 輸入特殊字符　14
3． 輸入規(guī)定數(shù)據(jù)　15
1．2．2 數(shù)據(jù)的填充　17
1． 自動(dòng)填充　17
2． 序列填充　17
3． 右鍵填充　18
1．2．3 數(shù)據(jù)的編輯　19
1． 通過單元格修改數(shù)據(jù)　19
2． 通過記錄單修改數(shù)據(jù)　19
3． 查找和替換數(shù)據(jù)　20
4． 刪除數(shù)據(jù)　21
1．3 美化“值班記錄表”　21
1．3．1 美化數(shù)據(jù)　21
1． 套用內(nèi)置樣式　22
2． 設(shè)置表格主題　22
3． 應(yīng)用單元格樣式　23
4． 添加邊框　24
5． 填充單元格　25
6． 設(shè)置表格背景　25
1．3．2 設(shè)置數(shù)據(jù)格式　26
1． 設(shè)置字體格式　26
2． 設(shè)置對(duì)齊方式　27
3． 設(shè)置數(shù)字類型　29
新手加油站——數(shù)據(jù)的編輯處理技巧　29
高手競(jìng)技場(chǎng)——數(shù)據(jù)的編輯處理練習(xí)　34
第2章　數(shù)據(jù)的顯示與輸出　35
2．1 編輯“銷售統(tǒng)計(jì)表”　36
2．1．1 突出顯示數(shù)據(jù)　36
1． 顯示數(shù)據(jù)條　36
2． 顯示色階　36
3． 顯示圖標(biāo)集　37
2．1．2 按規(guī)定顯示數(shù)據(jù)　39
1． 按項(xiàng)目選擇規(guī)則顯示數(shù)據(jù)　39
2． 顯示規(guī)定的單元格數(shù)據(jù)　39
3． 自定義規(guī)則顯示數(shù)據(jù)　40
2．2 打印“入職登記表”　42
2．2．1 打印數(shù)據(jù)　42
1． 設(shè)置頁面布局　42
2． 設(shè)置頁邊距　43
3． 設(shè)置頁眉和頁腳　43
4． 設(shè)置表格打印區(qū)域　45
2．2．2 導(dǎo)出數(shù)據(jù)　46
1． 將表格導(dǎo)出為PDF格式　46
2． 將工作簿導(dǎo)出為模板　47
3． 發(fā)送工作簿　48
新手加油站 ——數(shù)據(jù)的顯示與輸出技巧　49
高手競(jìng)技場(chǎng)——數(shù)據(jù)的顯示與輸出練習(xí)　51
第2部分　公式與函數(shù)
第3章　公式和函數(shù)的基本操作　53
3．1 計(jì)算“工資表”　54
3．1．1 公式的使用　54
1． 輸入公式　54
2． 復(fù)制填充公式　54
3． 修改公式　55
4． 顯示公式　56
3．1．2 單元格的引用　56
1． 在公式中引用單元格來計(jì)算數(shù)據(jù)　56
2． 相對(duì)引用單元格　57
3． 絕對(duì)引用單元格　58
4． 引用不同工作表中的單元格　59
5． 引用不同工作簿中的單元格　60
6． 引用已定義名稱的單元格　61
3．1．3 調(diào)試公式　63
1． 檢查公式　63
2． 審核公式　64
3． 實(shí)時(shí)監(jiān)控公式　65
3．2 計(jì)算“績(jī)效考核表”　66
3．2．1 函數(shù)的使用　66
1． 輸入函數(shù)　67
2． 復(fù)制函數(shù)　68
3． 應(yīng)用嵌套函數(shù)　68
4． 定義與使用名稱　69
3．2．2 常用函數(shù)的應(yīng)用　72
1． 求和函數(shù)SUM　72
2． 求平均值函數(shù)AVERAGE　73
3． 最大值函數(shù)MAX和最小值函數(shù)MIN　75
新手加油站——公式和函數(shù)的基本操作技巧　77
高手競(jìng)技場(chǎng)——公式和函數(shù)的基本操作練習(xí)　79
第4章　邏輯和文本函數(shù)的應(yīng)用　81
4．1 計(jì)算“測(cè)試成績(jī)表”　82
4．1．1 邏輯運(yùn)算函數(shù)　82
1． AND函數(shù)　82
2． OR函數(shù)　83
3． NOT函數(shù)　84
4．1．2 邏輯判斷函數(shù)　85
4．2 編輯“客戶信息表”　86
4．2．1 獲取特定文本信息　86
1． CONCATENATE函數(shù)　87
2． LEN函數(shù)　87
3． RIGHT函數(shù)　89
4． MID函數(shù)　90
5． VALUE函數(shù)　91
4．2．2 獲取其他文本信息　92
1． EXACT函數(shù)　92
2． REPT函數(shù)　93
3． FIND函數(shù)　94
新手加油站——邏輯和文本函數(shù)的應(yīng)用技巧　96
高手競(jìng)技場(chǎng)——邏輯和文本函數(shù)的應(yīng)用練習(xí)　98
第5章　日期和時(shí)間函數(shù)的應(yīng)用　99
5．1 計(jì)算“停車收費(fèi)記錄表”　100
5．1．1 提取日期和時(shí)間函數(shù)　100
1． TODAY 函數(shù)　100
2． NOW函數(shù)　101
5．1．2 提取特定時(shí)間函數(shù)　102
1． HOUR 函數(shù)　102
2． MINUTE函數(shù)　103
3． SECOND 函數(shù)　104
4． DAY 函數(shù)　105
5． YEAR 函數(shù)　106
6． MONTH 函數(shù)　108
7． WEEKDAY 函數(shù)　109
5．2 編輯“產(chǎn)品生產(chǎn)時(shí)間表”　110
5．2．1 提取不同月份的日期　110
5．2．2 計(jì)算實(shí)際天數(shù)　112
1． WORKDAY 函數(shù)　112
2． NETWORKDAYS函數(shù)　113
3． DAYS360 函數(shù)　114
新手加油站——日期和時(shí)間函數(shù)的應(yīng)用技巧　115
高手競(jìng)技場(chǎng)——日期和時(shí)間函數(shù)的應(yīng)用練習(xí)　116
第6章　統(tǒng)計(jì)和數(shù)學(xué)函數(shù)的應(yīng)用　117
6．1 統(tǒng)計(jì)“員工測(cè)試成績(jī)表”　118
6．1．1 條目統(tǒng)計(jì)函數(shù)　118
1． COUNTA 函數(shù)　118
2． COUNTBLANK函數(shù)　119
3． COUNTIF 函數(shù)　119
4． COUNTIFS 函數(shù)　120
6．1．2 排位函數(shù)　122
1． RANK．AVG 函數(shù)　122
2． RANK．EQ函數(shù)　123
6．1．3 平均函數(shù)　125
1． AVERAGEA 函數(shù)　125
2． AVERAGEIF函數(shù)　126
6．2 統(tǒng)計(jì)“產(chǎn)品銷售情況”　127
6．2．1 常規(guī)計(jì)算　127
1． PRODUCT 函數(shù)　127
2． MOD函數(shù)　128
3． ABS 函數(shù)　129
4． SUMIF函數(shù)　131
6．2．2 舍入計(jì)算　132
1． TRUNC 函數(shù)　132
2． ROUND函數(shù)　133
新手加油站——統(tǒng)計(jì)和數(shù)學(xué)函數(shù)的應(yīng)用技巧　134
高手競(jìng)技場(chǎng)——統(tǒng)計(jì)和數(shù)學(xué)函數(shù)的應(yīng)用練習(xí)　136
第7章　查找和引用函數(shù)的應(yīng)用　137
7．1 查找“商品采購和銷售一覽表”　138
7．1．1 普通查找　138
1． LOOKUP函數(shù)　138
2． VLOOKUP函數(shù)　139
3． HLOOKUP 函數(shù)　141
7．1．2 引用查找　142
1． MATCH 函數(shù)　142
2． INDEX函數(shù)　143
7．2 查詢“客服管理表”　144
7．2．1 綜合查詢　144
1． 反向查找　145
2． 多條件查找　146
7．2．2 高級(jí)引用　147
1． OFFSET函數(shù)　147
2． TRANSPOSE函數(shù)　148
新手加油站——查找和引用函數(shù)的應(yīng)用技巧　150
高手競(jìng)技場(chǎng)——查找和引用函數(shù)的應(yīng)用練習(xí)　151
第8章　財(cái)務(wù)函數(shù)的應(yīng)用　153
8．1 計(jì)算“固定資產(chǎn)統(tǒng)計(jì)表”　154
8．1．1 余額折舊函數(shù)　154
1． DB 函數(shù)　154
2． DDB函數(shù)　155
3． VDB 函數(shù)　156
8．1．2 年限折舊函數(shù)　157
1． SLN函數(shù)　158
2． SYD函數(shù)　158
8．2 計(jì)算“投資計(jì)劃表”　160
8．2．1 計(jì)算支付額函數(shù)　160
1． PMT 函數(shù)　160
2． PPMT函數(shù)　162
3． IPMT 函數(shù)　163
4． ISPMT函數(shù)　164
8．2．2 投資預(yù)算函數(shù)　165
1． FV 函數(shù)　165
2． NPER函數(shù)　167
3． NPV 函數(shù)　168
4． PV 函數(shù)　169
8．2．3 償還率函數(shù)　170
1． IRR 函數(shù)　170
2． RATE函數(shù)　171
8．3 計(jì)算“證券和債券投資表”　172
8．3．1 與證券相關(guān)的函數(shù)　172
1． ACCRINT 函數(shù)　172
2． COUPDAYS函數(shù)　174
8．3．2 與債券相關(guān)的函數(shù)　175
1． DISC 函數(shù)　175
2． INTRATE函數(shù)　176
3． RECEIVED 函數(shù)　176
4． YIELDMAT 函數(shù)　177
新手加油站——財(cái)務(wù)函數(shù)的應(yīng)用技巧　178
高手競(jìng)技場(chǎng)——財(cái)務(wù)函數(shù)的應(yīng)用練習(xí)　181
第3部分　圖表與數(shù)據(jù)分析
第9章　圖表的應(yīng)用　183
9．1 制作“銷售分析”圖表　184
9．1．1 創(chuàng)建圖表　184
1． 插入圖表　184
2． 調(diào)整圖表的位置和大小　185
3． 重新選擇圖表數(shù)據(jù)源　185
4． 交換圖表的行和列　187
5． 更改圖表類型　187
9．1．2 編輯并美化圖表　188
1． 添加坐標(biāo)軸標(biāo)題　188
2． 添加數(shù)據(jù)標(biāo)簽　189
3． 調(diào)整圖例位置　189
4． 添加并設(shè)置趨勢(shì)線　190
5． 添加并設(shè)置誤差線　191
6． 設(shè)置圖表區(qū)樣式　192
7． 設(shè)置繪圖區(qū)樣式　192
8． 設(shè)置數(shù)據(jù)系列顏色　193
9． 應(yīng)用圖表樣式　193
9．2 制作“產(chǎn)品構(gòu)成”圖表　194
9．2．1 創(chuàng)建復(fù)合餅圖　194
9．2．2 創(chuàng)建雙層餅圖　197
9．2．3 創(chuàng)建雷達(dá)圖　200
新手加油站——圖表的應(yīng)用技巧　203
高手競(jìng)技場(chǎng)——圖表的應(yīng)用練習(xí)　206
第10章　數(shù)據(jù)分析與匯總　207
10．1 編輯“庫存盤點(diǎn)表”　208
10．1．1 編輯數(shù)據(jù)記錄　208
1． 新建記錄　208
2． 編輯記錄　209
3． 刪除記錄　209
10．1．2 瀏覽數(shù)據(jù)記錄　210
1． 瀏覽記錄　210
2． 查找記錄　211
10．2 編輯“文書檔案管理表”　212
10．2．1 數(shù)據(jù)排序　212
1． 簡(jiǎn)單排序　213
2． 單關(guān)鍵字排序　213
3． 多關(guān)鍵字排序　215
4． 按行排序　216
5． 自定義排序　217
10．2．2 數(shù)據(jù)篩選　218
1． 指定目標(biāo)篩選　218
2． 自動(dòng)篩選　219
3． 自定義篩選　221
4． 高級(jí)篩選　221
10．3 編輯“肉制品銷量表”　223
10．3．1 數(shù)據(jù)合并計(jì)算　223
1． 按位置合并計(jì)算　223
2． 按類合并計(jì)算　224
10．3．2 數(shù)據(jù)分類匯總　225
1． 創(chuàng)建分類匯總　225
2． 多重分類匯總　226
3． 控制分類匯總的顯示數(shù)據(jù)　227
新手加油站——數(shù)據(jù)分析與匯總技巧　228
高手競(jìng)技場(chǎng)——數(shù)據(jù)分析與匯總練習(xí)　231
第11章　數(shù)據(jù)透視表和數(shù)據(jù)透視圖的應(yīng)用　233
11．1 編輯“固定資產(chǎn)統(tǒng)計(jì)表”　234
11．1．1 創(chuàng)建數(shù)據(jù)透視表　234
1． 插入數(shù)據(jù)透視表　234
2． 添加字段　235
11．1．2 設(shè)置數(shù)據(jù)透視表　236
1． 設(shè)置值字段數(shù)據(jù)格式　236
2． 更改字段　237
3． 設(shè)置值字段的匯總方式　238
11．1．3 使用數(shù)據(jù)透視表　239
1． 顯示與隱藏明細(xì)數(shù)據(jù)　239
2． 排序數(shù)據(jù)透視表　241
3． 篩選數(shù)據(jù)透視表　242
4． 刷新數(shù)據(jù)透視表　244
5． 清除或刪除數(shù)據(jù)透視表　245
11．1．4 美化數(shù)據(jù)透視表　246
1． 應(yīng)用并設(shè)置樣式　246
2． 手動(dòng)美化數(shù)據(jù)透視表　247
11．1．5 使用切片器　248
1． 插入切片器　248
2． 更改切片器樣式　249
3． 設(shè)置切片器　249
4． 刪除切片器　250
11．2 編輯“產(chǎn)品銷售統(tǒng)計(jì)表”　251
11．2．1 創(chuàng)建數(shù)據(jù)透視圖　251
1． 插入數(shù)據(jù)透視圖　251
2． 移動(dòng)數(shù)據(jù)透視圖　252
11．2．2 使用數(shù)據(jù)透視圖　253
1． 篩選圖表并添加趨勢(shì)線　253
2． 設(shè)置并美化數(shù)據(jù)透視圖　256
新手加油站——數(shù)據(jù)透視圖表的應(yīng)用技巧　257
高手競(jìng)技場(chǎng)——數(shù)據(jù)透視圖表的應(yīng)用練習(xí)　259
第12章　其他數(shù)據(jù)分析方法　261
12．1 編輯“投資計(jì)劃表”　262
12．1．1 方案管理器的使用　262
1． 輸入并計(jì)算基本方案　262
2． 使用方案管理器創(chuàng)建方案　263
3． 輸出并查看方案摘要　265
12．1．2 模擬運(yùn)算表的使用　266
1． 單變量模擬運(yùn)算　266
2． 雙變量模擬運(yùn)算　267
12．2 編輯“進(jìn)銷分析表”　268
12．2．1 規(guī)劃求解　268
1． 加載規(guī)劃求解分析工具　268
2． 建立模型　269
3． 規(guī)劃求解計(jì)算　271
12．2．2 回歸分析　272
新手加油站——其他數(shù)據(jù)分析方法技巧　273
高手競(jìng)技場(chǎng)——其他數(shù)據(jù)分析方法練習(xí)　276