Excel 2013表格、公式、函數(shù)與圖表應(yīng)用傻瓜書

01Excel 2013輕松入門
1.1 Excel 2013的工作界面··························2
1.2 Excel 2013工作簿的基礎(chǔ)操作···············3
1.2.1 新建工作簿··············································3
1.2.2 保存工作簿··············································4
1.2.3 打開(kāi)工作簿··············································5
1.2.4 關(guān)閉工作簿··············································6
1.2.5 隱藏工作簿··············································6
1.3 工作簿的多窗口顯示······························7
1.3.1 創(chuàng)建新窗口··············································7
1.3.2 切換窗口··················································8
1.3.3 全部重排··················································9
1.3.4 視圖縮放················································10
1.4 Excel 2013工作環(huán)境的設(shè)置················· 13
1.4.1 顯示與隱藏網(wǎng)格線································13
1.4.2 顯示與隱藏標(biāo)尺····································14
1.4.3 設(shè)置自動(dòng)保存時(shí)間間隔························14
1.4.4 設(shè)置文檔的默認(rèn)保存路徑····················15
1.4.5 設(shè)置文檔的默認(rèn)保存類型····················16
1.4.6 隱藏與顯示功能區(qū)································16
1.5 共享工作簿··········································· 17
1.5.1 創(chuàng)建共享工作簿····································17
1.5.2 修訂共享工作簿····································18
1.5.3 接受/拒絕修訂工作簿···························19

02輸入與編輯Excel表格數(shù)據(jù)
2.1 Excel單元格數(shù)據(jù)編輯·························· 22
2.1.1 選擇單元格············································22
2.1.2 插入單元格············································23
2.1.3 刪除單元格············································24
2.1.4 清除單元格···········································25
2.1.5 命名單元格···········································26
2.1.6 移動(dòng)單元格數(shù)據(jù)···································27
2.1.7 復(fù)制單元格數(shù)據(jù)···································28
2.1.8 合并多個(gè)單元格···································29
2.1.9 取消單元格合并···································30
2.2 手動(dòng)輸入數(shù)據(jù)·······································30
2.2.1 輸入文本型數(shù)據(jù)···································30
2.2.2 將數(shù)字轉(zhuǎn)化為文本·······························31
2.2.3 輸入日期或時(shí)間···································31
2.2.4 輸入貨幣型數(shù)據(jù)···································32
2.2.5 快速填充相同數(shù)據(jù)·······························33
2.3 填充數(shù)據(jù)···············································34
2.3.1 設(shè)置記憶式輸入···································34
2.3.2 使用填充柄自動(dòng)填充數(shù)據(jù)···················34
2.3.3 使用“填充”命令填充數(shù)據(jù)···············35
2.3.4 文本序列填充·······································35
2.3.5 自定義填充序列···································36
2.4 編輯數(shù)據(jù)···············································37
2.4.1 修改數(shù)據(jù)···············································37
2.4.2 選擇性粘貼···········································38
2.4.3 撤銷和恢復(fù)數(shù)據(jù)···································39
2.4.4 查找和替換數(shù)據(jù)···································39

03工作表格式設(shè)置與美化
3.1 設(shè)置單元格格式····································42
3.1.1 設(shè)置字體格式·······································42
3.1.2 設(shè)置數(shù)字格式·······································43
3.1.3 使用格式刷···········································43
3.2 設(shè)置對(duì)齊方式·······································44
3.2.1 設(shè)置水平和垂直對(duì)齊方式···················44
3.2.2 設(shè)置旋轉(zhuǎn)文本·······································46
3.3 格式化單元格······································· 47
3.3.1 調(diào)整行高和列寬··································· 47
3.3.2 設(shè)置單元格邊框··································· 49
3.3.3 設(shè)置單元格背景色和圖案··················· 50
3.3.4 設(shè)置工作表的背景圖··························· 51
3.4 文字拼音·············································· 52
3.4.1 添加拼音··············································· 52
3.4.2 顯示和隱藏拼音··································· 53
3.4.3 編輯拼音字段······································· 53
3.4.4 設(shè)置拼音格式······································· 54
3.5 套用表格樣式······································· 55
3.5.1 應(yīng)用表樣式··········································· 55
3.5.2 自定義表格樣式··································· 56
3.6 使用單元格樣式··································· 58
3.6.1 應(yīng)用單元格樣式··································· 58
3.6.2 創(chuàng)建新樣式··········································· 58
3.6.3 修改樣式··············································· 59
3.6.4 刪除樣式··············································· 60
3.7 使用條件格式······································· 61
3.7.1 設(shè)置三色條件格式······························· 61
3.7.2 使用數(shù)據(jù)條格式··································· 62
3.7.3 更改條件格式······································· 63
3.7.4 刪除條件格式······································· 64

04 使用公式快速計(jì)算
4.1 關(guān)于公式·············································· 66
4.1.1 公式的結(jié)構(gòu)··········································· 66
4.1.2 運(yùn)算符··················································· 66
4.1.3 運(yùn)算符優(yōu)先級(jí)······································· 67
4.2 公式的基本操作··································· 68
4.2.1 在單元格中輸入公式··························· 68
4.2.2 在編輯欄中輸入公式··························· 69
4.2.3 結(jié)合鍵盤和鼠標(biāo)輸入公式··················· 70
4.2.4 在公式中使用括號(hào)······························· 71
4.3 編輯公式·············································· 72
4.3.1 顯示公式··············································· 72
4.3.2 修改公式··············································· 72
4.3.3 復(fù)制公式··············································· 73
4.4 審核公式··············································74
4.4.1 檢查錯(cuò)誤··············································· 74
4.4.2 追蹤引用單元格··································· 75
4.4.3 追蹤從屬單元格··································· 75
4.4.4 移去箭頭··············································· 76
4.5 單元格的引用·······································77
4.5.1 相對(duì)引用··············································· 77
4.5.2 絕對(duì)引用··············································· 78
4.5.3 混合引用··············································· 79

05 使用函數(shù)高效辦公
5.1 函數(shù)簡(jiǎn)介··············································82
5.1.1 認(rèn)識(shí)函數(shù)··············································· 82
5.1.2 函數(shù)分類··············································· 82
5.2 函數(shù)的輸入和編輯································83
5.2.1 輸入函數(shù)··············································· 83
5.2.2 復(fù)制函數(shù)··············································· 84
5.3 統(tǒng)計(jì)函數(shù)··············································85
5.3.1 AVERAGE 函數(shù)··································· 85
5.3.2 MAX 函數(shù)············································ 86
5.3.3 COUNTA函數(shù)····································· 88
5.3.4 COUNT函數(shù)········································ 89
5.4 文本函數(shù)··············································90
5.4.1 EXACT函數(shù)········································ 90
5.4.2 LOWER 與UPPER 函數(shù)····················· 92
5.4.3 CONCATENATE 函數(shù)························· 95
5.4.4 REPT 函數(shù)············································ 97
5.4.5 SUBSTITUTE函數(shù)······························ 99
5.5 數(shù)學(xué)與三角函數(shù)··································101
5.5.1 ABS 函數(shù)············································ 101
5.5.2 INT 函數(shù)············································· 102
5.5.3 ODD 與EVEN函數(shù)·························· 104
5.5.4 SUM 函數(shù)··········································· 106
5.5.5 DEGREES函數(shù)·································· 109
5.6 邏輯函數(shù)············································ 110
5.6.1 AND 函數(shù)············································110
5.6.2 OR 函數(shù)···············································111
5.6.3 NOT 函數(shù)·············································112
5.6.4 IF 函數(shù)·················································113
5.7 財(cái)務(wù)函數(shù)············································ 114
5.7.1 折舊計(jì)算函數(shù)······································114
5.7.2 本利計(jì)算函數(shù)······································115
5.7.3 報(bào)酬計(jì)算函數(shù)······································117
06 創(chuàng)建與編輯圖表
6.1 認(rèn)識(shí)圖表············································ 120
6.1.1 圖表結(jié)構(gòu)··············································120
6.1.2 圖表類型··············································121
6.2 創(chuàng)建圖表············································ 124
6.2.1 創(chuàng)建基本圖表······································124
6.2.2 使用快捷鍵創(chuàng)建圖表··························125
6.2.3 創(chuàng)建組合圖表······································125
6.2.4 移動(dòng)圖表的位置··································127
6.2.5 轉(zhuǎn)變圖表類型······································128
6.2.6 創(chuàng)建迷你圖··········································128
6.3 編輯圖表數(shù)據(jù)····································· 130
6.3.1 更改系列值··········································131
6.3.2 刪除系列··············································131
6.3.3 添加系列··············································132
6.3.4 重新選擇圖表數(shù)據(jù)區(qū)域······················134
6.3.5 處理隱藏的單元格······························135
6.3.6 改變數(shù)據(jù)系列的產(chǎn)生方式··················135
6.4 更改圖表布局····································· 136
6.4.1 添加圖表元素······································136
6.4.2 添加數(shù)據(jù)標(biāo)簽······································137
6.4.3 添加網(wǎng)格線··········································138
6.4.4 添加坐標(biāo)軸標(biāo)題··································139
6.4.5 將標(biāo)題鏈接到單元格··························139
6.4.6 更改圖例的位置··································141
6.4.7 應(yīng)用快速布局樣式······························141
6.5 應(yīng)用趨勢(shì)線和誤差線··························142
6.5.1 添加趨勢(shì)線·········································142
6.5.2 添加誤差線·········································143
6.6 美化圖表·············································144
6.6.1 更改圖表顏色·····································144
6.6.2 更改圖表樣式·····································145
6.6.3 通過(guò)格式窗格設(shè)置格式·····················145
6.6.4 使用“格式”選項(xiàng)卡設(shè)置圖表格式·············································147
6.6.5 插入形狀·············································149
6.6.6 插入圖片·············································150
6.6.7 設(shè)置圖片填充·····································151

07 Excel數(shù)據(jù)管理與分析
7.1 數(shù)據(jù)的排序·········································154
7.1.1 排序規(guī)則·············································154
7.1.2 簡(jiǎn)單排序·············································155
7.1.3 多關(guān)鍵字排序·····································155
7.1.4 更改排序依據(jù)·····································156
7.1.5 自定義序列排序·································157
7.2 數(shù)據(jù)的篩選·········································158
7.2.1 按文本值篩選·····································158
7.2.2 按數(shù)字篩選·········································160
7.2.3 高級(jí)篩選·············································161
7.3 數(shù)據(jù)的分類匯總··································162
7.3.1 認(rèn)識(shí)分類匯總·····································162
7.3.2 簡(jiǎn)單分類匯總·····································163
7.3.3 刪除分類匯總·····································165
7.3.4 嵌套分類匯總·····································165
7.4 數(shù)據(jù)的合并計(jì)算··································167
7.4.1 按位置合并計(jì)算·································167
7.4.2 按分類合并計(jì)算·································168
7.5 數(shù)據(jù)的有效性·····································170
7.5.1 設(shè)置數(shù)據(jù)有效性·································170
7.5.2 圈釋錯(cuò)誤信息·····································172

08 數(shù)據(jù)透視表與數(shù)據(jù)透視圖
8.1 創(chuàng)建數(shù)據(jù)透視表································· 174
8.1.1 在新工作表中創(chuàng)建數(shù)據(jù)透視表········· 174
8.1.2 在當(dāng)前工作表中創(chuàng)建數(shù)據(jù)透視表····· 175
8.2 移動(dòng)與刪除數(shù)據(jù)透視表······················ 176
8.2.1 移動(dòng)透視表········································· 176
8.2.2 刪除數(shù)據(jù)透視表································· 177
8.3 更改行標(biāo)簽和列標(biāo)簽·························· 177
8.3.1 更改行標(biāo)簽層次結(jié)構(gòu)························· 178
8.3.2 添加列標(biāo)簽········································· 178
8.4 篩選數(shù)據(jù)透視表數(shù)據(jù)·························· 179
8.4.1 在透視表中篩選數(shù)據(jù)························· 179
8.4.2 在“數(shù)據(jù)透視表字段”窗格中篩選數(shù)據(jù)············································· 179
8.5 更改數(shù)據(jù)透視表的布局······················ 180
8.5.1 顯示/隱藏分類匯總···························· 180
8.5.2 設(shè)置報(bào)表布局····································· 181
8.5.3 更改數(shù)據(jù)透視表樣式························· 182
8.6 數(shù)據(jù)透視表排序································· 182
8.6.1 簡(jiǎn)單排序············································· 183
8.6.2 自定義排序條件································· 183
8.7 字段設(shè)置············································ 185
8.7.1 更改字段名稱····································· 185
8.7.2 更改值的匯總方式····························· 186
8.7.3 以總計(jì)百分比顯示數(shù)據(jù)····················· 187
8.7.4 以百分比顯示數(shù)據(jù)····························· 187
8.8 使用切片器········································ 188
8.8.1 創(chuàng)建切片器········································· 188
8.8.2 篩選數(shù)據(jù)············································· 189
8.8.3 刪除切片器········································· 190
8.8.4 更改切片器樣式································· 190
8.9 使用篩選器········································ 192
8.9.1 添加篩選器········································· 192
8.9.2 顯示報(bào)表篩選頁(yè)································· 193
8.10 使用條件格式···································194
8.10.1 突出顯示單元格規(guī)則······················· 194
8.10.2 使用項(xiàng)目選取規(guī)則··························· 195
8.11 數(shù)據(jù)透視表的其他操作·····················195
8.11.1 顯示詳細(xì)信息··································· 196
8.11.2 復(fù)制數(shù)據(jù)··········································· 196
8.12 使用數(shù)據(jù)透視圖································197
8.12.1 使用現(xiàn)有數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視圖······································· 197
8.12.2 創(chuàng)建全新數(shù)據(jù)透視圖······················· 198
8.12.3 使用數(shù)據(jù)透視圖篩選數(shù)據(jù)··············· 199
8.12.4 轉(zhuǎn)換為標(biāo)準(zhǔn)圖表······························· 200

09 宏與VBA 應(yīng)用
9.1 宏與安全設(shè)置···································· 202
9.1.1 認(rèn)識(shí)宏················································· 202
9.1.2 更改宏的安全設(shè)置····························· 202
9.1.3 打開(kāi)包含宏的工作簿························· 203
9.2 宏的錄制與執(zhí)行································· 203
9.2.1 顯示“開(kāi)發(fā)工具”選項(xiàng)卡················· 204
9.2.2 創(chuàng)建宏················································· 204
9.2.3 使用“宏”對(duì)話框執(zhí)行宏················· 205
9.2.4 使用快捷鍵執(zhí)行宏····························· 206
9.2.5 指定宏················································· 206
9.3 宏的其他操作···································· 207
9.3.1 保存宏文檔········································· 207
9.3.2 刪除宏················································· 208
9.3.3 更改執(zhí)行宏的快捷鍵························· 209
9.3.4 編輯宏················································· 209
9.3.5 加載宏················································· 209
9.4 認(rèn)識(shí)VBA··········································· 210
9.4.1 VBA 簡(jiǎn)介··········································· 210
9.4.2 VBA 與VB 的關(guān)系·····························211
9.5 熟悉VBA 開(kāi)發(fā)環(huán)境····························211
9.5.1 用戶界面組成······································211
9.5.2 工程資源管理器窗口··························212
9.5.3 代碼窗口··············································212
9.5.4 監(jiān)視窗口··············································213
9.5.5 屬性窗口··············································213
9.5.6 立即窗口··············································214
9.5.7 對(duì)象瀏覽器窗口··································214
9.6 VBA 語(yǔ)法基礎(chǔ)····································215
9.6.1 數(shù)據(jù)類型··············································215
9.6.2 常量與變量··········································217
9.6.3 VBA 運(yùn)算符與表達(dá)式························219
9.6.4 程序結(jié)構(gòu)··············································222
9.6.5 數(shù)組······················································227
9.6.6 SUB 過(guò)程·············································228
9.6.7 Function 函數(shù)·······································230
9.6.8 使用按鈕控件······································233
9.6.9 其他控件屬性······································234

10 創(chuàng)建鏈接與引用
10.1 插入超鏈接·······································238
10.1.1 創(chuàng)建指向現(xiàn)有文件的超鏈接············238
10.1.2 創(chuàng)建指向特定網(wǎng)頁(yè)的超鏈接············239
10.1.3 創(chuàng)建指向工作表的超鏈接················240
10.1.4 創(chuàng)建指向特定單元格的超鏈接········240
10.1.5 選中超鏈接單元格····························242
10.1.6 刪除超鏈接········································242
10.2 數(shù)據(jù)引用··········································243
10.2.1 引用本工作表內(nèi)的單元格················243
10.2.2 引用其他工作表的單元格················243
10.2.3 引用其他工作簿的單元格················244
10.2.4 粘貼引用鏈接····································245
10.3 嵌入與鏈接文件·······························246
10.3.1 嵌入Word 文檔·································246
10.3.2 嵌入PowerPoint演示文稿···············247
10.3.3 鏈接Excel 工作簿·····························247

11 工作表打印與輸出
11.1 設(shè)置頁(yè)面版式··································· 250
11.1.1 設(shè)置頁(yè)邊距········································250
11.1.2 設(shè)置紙張大小····································251
11.1.3 設(shè)置打印區(qū)域····································252
11.1.4 設(shè)置打印順序····································253
11.2 設(shè)置頁(yè)眉和頁(yè)腳······························· 253
11.2.1 添加預(yù)定義頁(yè)眉和頁(yè)腳···················253
11.2.2 自定義頁(yè)眉和頁(yè)腳····························254
11.3 使用分頁(yè)符······································ 256
11.3.1 插入分頁(yè)符········································256
11.3.2 調(diào)整分頁(yè)符········································257
11.3.3 刪除分頁(yè)符········································258
11.4 打印工作表······································ 259
11.4.1 快速打印工作表································259
11.4.2 設(shè)置打印參數(shù)····································260
11.5 添加工作表水印效果························ 260
11.5.1 制作圖片水印····································260
11.5.2 制作文字水印····································262

12 Excel數(shù)據(jù)安全與保護(hù)
12.1 使用隱藏的方式保護(hù)數(shù)據(jù)················ 266
12.1.1 隱藏行或列·······································266
12.1.2 顯示隱藏的行或列···························266
12.1.3 隱藏工作表·······································267
12.1.4 取消隱藏工作表·······························267
12.1.5 隱藏工作簿·······································267
12.1.6 顯示隱藏的工作簿···························268
12.2 使用密碼保護(hù)數(shù)據(jù)··························· 269
12.2.1 保護(hù)特定單元格·······························269
12.2.2 限定工作表可用的操作···················271
12.2.3 設(shè)置工作簿打開(kāi)密碼·······················272
12.2.4 保護(hù)工作簿結(jié)構(gòu)·······························273