Excel報(bào)表一勞永逸（數(shù)據(jù)+函數(shù)+表格）

第一部分 必備知識
第1章萬丈高樓平地起 2
1.1 工作表與工作簿····3
1.2 單元格·3
1.3 區(qū)域4
1.4 活動(dòng)單元格··4
1.5 選項(xiàng)卡與功能區(qū)····5
1.6 名稱5
1.6.1 名稱的概念及作用· 5
1.6.2 名稱的快速創(chuàng)建 6
1.6.3 根據(jù)所選內(nèi)容批量創(chuàng)建名稱··· 6
1.6.4 名稱修改與刪除 7
1.7 函數(shù)7
1.7.1 函數(shù)的概念與構(gòu)成· 7
1.7.2 如何有效地學(xué)習(xí)函數(shù)· 9
1.8 單元格引用方式及其區(qū)別10
1.8.1 公式計(jì)算結(jié)果為何出錯(cuò)10
1.8.2 如何讓行號列標(biāo)填充時(shí)不變化··10
1.8.3 相對引用、絕對引用與混合引用···11
1.8.4 公式中4種引用方式的選用與快速變換··11
1.9 快速選取單元格的方法····12
1.9.1 選取連續(xù)單元格···12
1.9.2 選取不連續(xù)單元格····13
1.9.3 使用名稱框選取連續(xù)及不連續(xù)單元格····13
1.9.4 使用定位選取特定單元格·13
1.9.5 使用查找選取特定單元格·15
1.10 ※當(dāng)前區(qū)域···16
1.11 ※R1C1引用樣式··17
1.12 ※關(guān)于名稱的幾點(diǎn)補(bǔ)充··17
1.12.1 名稱的作用····17
1.12.2 使用功能區(qū)命令定義名稱···18
1.12.3 名稱的兩種作用范圍··19
1.12.4 給名稱起名字注意事項(xiàng)···19
1.12.5 查看已定義的名稱·20
1.12.6 名稱管理器高級應(yīng)用技巧···20
1.12.7 名稱相同時(shí)的處理規(guī)則···21
1.12.8 名稱定義中的相對引用與絕對引用·22
1.12.9 將名稱應(yīng)用到公式中··22
第二部分 采集數(shù)據(jù)
第2章設(shè)計(jì)Excel數(shù)據(jù)采集模板·· 26
2.1 搞懂Excel中的數(shù)據(jù)類型··27
2.2 揭開日期類型的真面目····27
2.3 各種類型的數(shù)據(jù)如何識別 28
2.4 利用數(shù)據(jù)驗(yàn)證限定數(shù)據(jù)符合要求 ··29
2.4.1 限定輸入數(shù)值型字段 ····30
2.4.2 限定輸入文本型字段 ····31
2.4.3 限定日期與時(shí)間型字段 31
2.4.4 制作下拉框進(jìn)行選擇 ····32
2.4.5 ※限定不能重復(fù)輸入 ····33
2.5 為模板添加保護(hù)措施以防修改 ·33
2.5.1 保護(hù)工作表 ·34
2.5.2 為不同單元格區(qū)域設(shè)定不同的修改密碼 35
2.5.3 為工作簿加把鎖 ···35
2.6 ※使用IS函數(shù)判斷數(shù)據(jù)類型 ·36
第3章 數(shù)據(jù)輸入技巧 ··37
3.1 身份證號碼的輸入方法 ····38
3.2 數(shù)值輸入技巧 ·38
3.2.1 輸入前導(dǎo)0 ···38
3.2.2 快速輸入數(shù)值末尾的多個(gè)0 ···39
3.2.3 自動(dòng)插入小數(shù)點(diǎn) ···39
3.3 分?jǐn)?shù)的輸入方法 ··40
3.4 日期與時(shí)間輸入方法 ···40
3.4.1 當(dāng)前日期輸入 ··41
3.4.2 ※日期的簡化輸入法 ····41
3.4.3 ※幾十年后兩位年份怎么辦 ·42
3.4.4 時(shí)間與當(dāng)前時(shí)間輸入 ····42
3.4.5 日期與時(shí)間組合輸入 ····43
3.4.6 ※使用下拉框輸入日期與時(shí)間 ··43
3.5 在一個(gè)單元格內(nèi)輸入多行內(nèi)容 ·44
3.6 使用自動(dòng)下拉框輸入已有數(shù)據(jù) ·44
3.7 填充序列與自定義序列 ····45
3.7.1 生成1，2，3，4，…自增序號 ·45
3.7.2 生成1，3，5，7，…等差數(shù)列 ·46
3.7.3 生成按天遞增或僅工作日的日期序列 ····46
3.7.4 使用填充序列對話框 ····47
3.7.5 Excel中的內(nèi)置文本序列 ····47
3.7.6 添加自定義序列 ···48
3.8 一鍵批量輸入多個(gè)單元格 48
3.9 工作組模式對多張工作表批量輸入 ···49
3.10 更改回車鍵移動(dòng)方向 ·50
第4章 獲取外部數(shù)據(jù) ··51
4.1 導(dǎo)入文本文件 ·52
4.1.1 按分隔符號導(dǎo)入 ···52
4.1.2 按固定寬度導(dǎo)入 ···53
4.2 導(dǎo)入網(wǎng)頁數(shù)據(jù) ·54
第三部分 加工整合
第5章 合理規(guī)范的表格更利于分析 56
5.1 Excel其實(shí)只有兩種表 ·57
5.2 畫蛇添足的多行標(biāo)題 ···58
5.3 多此一舉的小計(jì)及匯總行 59
5.4 不要有合并單元格 ··59
5.5 該分成多列的沒拆分 ···61
5.5.1 使用分列功能拆分為多列 ·61
5.5.2 文本截取函數(shù)LEFT/RIGHT/MID 62
5.6 試圖用顏色區(qū)分不同類型 63
5.6.1 ※根據(jù)顏色自動(dòng)填充文本 ·63
5.7 錯(cuò)誤的數(shù)值格式 ··64
5.7.1 利用“轉(zhuǎn)換為數(shù)字”命令 ·64
5.7.2 使用選擇性粘貼 ···64
5.8 非法或不完整的日期格式 65
5.8.1 修正“20070820”8位年月日格式 ····65
5.8.2 使用DATE函數(shù)修正8位年月日格式 ···66
5.8.3 修正“2007.08.20”帶小圓點(diǎn)格式 ····67
5.8.4 ※修正“2007.8.20”月日長度不固定的格式 ····67
5.8.5 只有月日的日期處理方法 ·69
5.8.6 “XXXX年XX月XX日”格式的處理方法 ·69
5.8.7 將標(biāo)準(zhǔn)日期轉(zhuǎn)換成其他格式 ·69
5.9 數(shù)據(jù)庫不要使用二維表 ····70
5.9.1 多重合并數(shù)據(jù)透視表轉(zhuǎn)換二維表 ···70
5.9.2 ※使用INDEX/ROW/INT/MOD函數(shù)轉(zhuǎn)換二維表 ····71
5.9.3 ※逆透視法轉(zhuǎn)換二維表 73
5.10 其他數(shù)據(jù)及表格結(jié)構(gòu)轉(zhuǎn)換 ··73
5.10.1 將兩列合并成一列 ·74
5.10.2 將多列合并成一列 ·74
5.10.3 一列里都添加上同樣的文字 ····75
5.10.4 刪除重復(fù)數(shù)據(jù)保留唯一值 ···75
5.10.5 轉(zhuǎn)置功能將表格橫向縱向相互轉(zhuǎn)換 ·76
5.10.6 ※更復(fù)雜的橫向或縱向表格數(shù)據(jù)處理 ··76
5.11 神奇的快速填充 77
5.12 表格設(shè)計(jì)實(shí)例 ····78
5.12.1 資產(chǎn)折舊表 ····78
5.12.2 產(chǎn)品銷量表 ····80
5.12.3 客戶維修記錄表 80
5.12.4 木業(yè)公司工資表 81
5.12.5 小結(jié)：何時(shí)要用“數(shù)據(jù)庫”表 82
第四部分 分析呈現(xiàn)
第6章 使用函數(shù)統(tǒng)計(jì)報(bào)表數(shù)據(jù) ···· 84
6.1 多條件記數(shù)函數(shù)COUNTIFS 85
6.1.1 COUNTIFS使用場景與語法解讀 ····85
6.1.2 COUNTIFS單條件計(jì)數(shù) ·85
6.1.3 COUNTIFS多條件計(jì)數(shù) ·86
6.2 多條件求和函數(shù)SUMIFS ·86
6.3 條件判斷函數(shù)IF ··87
6.3.1 IF使用場景與語法解讀 ·87
6.3.2 兩種情況的判斷 ···88
6.3.3 三種情況的判斷 ···88
6.4 查值函數(shù)VLOOKUP ····89
6.4.1 VLOOKUP使用場景與語法解讀 89
6.4.2 參數(shù)“查找區(qū)域”的潛規(guī)則 ·89
6.4.3 第4個(gè)參數(shù)：精確和近似查找 ····90
6.4.4 何時(shí)用精確，何時(shí)用近似 ·91
6.5 函數(shù)綜合應(yīng)用實(shí)例 ··92
6.6 公式錯(cuò)誤處理 ·94
6.6.1 公式報(bào)錯(cuò)原因及處理對策 ·94
6.6.2 屏蔽公式錯(cuò)誤 ··95
第7章 強(qiáng)大的報(bào)表工具——數(shù)據(jù)透視表 ···96
7.1 創(chuàng)建透視表 98
7.2 三種數(shù)據(jù)類型在數(shù)據(jù)透視表中的作用 ···99
7.3 更改值匯總依據(jù) ···· 100
7.4 設(shè)置數(shù)字格式 ··· 101
7.5 設(shè)置值顯示方式 ···· 101
7.6 對行/列標(biāo)簽字段組合 ···· 105
7.6.1 對數(shù)值字段分區(qū)間匯總 ···105
7.6.2 對文本字段分組 ·106
7.6.3 對日期字段按月/季度/年度匯總 ···107
7.7 調(diào)整字段順序 ··· 107
7.7.1 在區(qū)域中調(diào)整字段順序 ···108
7.7.2 在透視表中調(diào)整值字段順序 ····108
7.7.3 在透視表中調(diào)整標(biāo)簽字段順序 108
7.8 插入計(jì)算字段 ··· 109
7.9 在透視表中排序 110
7.9.1 各種數(shù)據(jù)類型排序規(guī)則 ···110
7.9.2 按一列值字段排序··111
7.9.3 按多列值字段排序··111
7.9.4 對標(biāo)簽字段自定義排序 ···112
7.10 在透視表中篩選 ··113
7.10.1 按標(biāo)簽字段篩選 ···114
7.10.2 按值字段篩選 ···115
7.10.3 直接在透視表中篩選 116
7.11 更改報(bào)表布局 ··117
7.12 添加行/列小計(jì)與總計(jì) ···118
7.13 更改數(shù)據(jù)源引用位置 ····119
7.14 從數(shù)據(jù)透視表到數(shù)據(jù)透視圖 120
7.15 數(shù)據(jù)透視表更多應(yīng)用技巧 ···· 120
7.15.1 在現(xiàn)有工作表中創(chuàng)建透視表 ··120
7.15.2 移動(dòng)透視表位置 ···121
7.15.3 透視表中雙擊查看數(shù)據(jù)明細(xì) ··121
7.15.4 不讓透視表顯示錯(cuò)誤值和調(diào)整列寬 ····121
7.15.5 更改字段與區(qū)域排列方式 ·122
7.15.6 延遲布局更新 ···122
第8章 表間黏合劑SQL 123
8.1 什么是SQL ·· 124
8.2 在哪里使用SQL ···· 125
8.2.1 使用現(xiàn)有連接 125
8.2.2 使用Microsoft Query ··126
8.3 SQL訪問的數(shù)據(jù)對象 128
8.3.1 系統(tǒng)表 ···128
8.3.2 命名區(qū)域（名稱）··128
8.4 常用SQL查詢語句 ···· 129
8.4.1 按條件查詢 ····129
8.4.2 查詢其他工作簿數(shù)據(jù) ··130
8.4.3 多表合并查詢 131
8.4.4 多表關(guān)聯(lián)查詢 131
8.5 SQL小結(jié)·· 133
第9章 報(bào)表格式與查看 ·134
9.1 復(fù)制并粘貼格式和列寬 · 135
9.2 運(yùn)用單元格樣式統(tǒng)一格式 ·· 135
9.3 套用表格格式及自定義表樣式 ··· 136
9.4 快速更改數(shù)據(jù)透視表樣式 ·· 136
9.5 設(shè)置工作表背景 ···· 137
9.6 為報(bào)表添加條件格式 137
9.6.1 不同數(shù)據(jù)突出顯示為不同顏色 138
9.6.2 選取數(shù)據(jù)TOP10%或最大/最小N項(xiàng) ··139
9.6.3 用數(shù)據(jù)條長短直觀表示數(shù)據(jù)大小 ·139
9.6.4 色階與圖標(biāo)集 140
9.6.5 設(shè)置合同到期前30天變色提醒 140
9.6.6 條件格式優(yōu)先級與清除 ···142
9.7 小而美的迷你圖 ···· 142
9.7.1 什么是迷你圖 142
9.7.2 創(chuàng)建迷你圖 ····142
9.8 窗口界面自定義 ···· 143
9.8.1 自定義快速訪問工具欄 ···143
9.8.2 創(chuàng)建個(gè)性化選項(xiàng)卡··144
9.8.3 快速隱藏或顯示功能區(qū) ···144
9.8.4 不顯示網(wǎng)格線、標(biāo)題和編輯欄 145
9.8.5 不顯示多余的行和列 ··145
9.8.6 隱藏工作表標(biāo)簽與滾動(dòng)條 ····146
9.9 報(bào)表查看技巧 ··· 146
9.9.1 凍結(jié)窗格固定住標(biāo)題行 ···146
9.9.2 快速調(diào)整工作表顯示比例 ····147
9.9.3 窗口拆分與重排 ·147
9.9.4 報(bào)表導(dǎo)航系統(tǒng) 148
第五部分 一勞永逸
第10章 一勞永逸的報(bào)表制作秘訣 150
10.1 在函數(shù)報(bào)表里多維查看 ···· 152
10.1.1 按產(chǎn)品查看報(bào)表 ···152
10.1.2 日期函數(shù)EOMONTH 153
10.1.3 按年份及月份查看報(bào)表 ·153
10.2 函數(shù)報(bào)表自動(dòng)適應(yīng)變化 ···· 154
10.2.1 自動(dòng)包含新增的行記錄 ·155
10.2.2 生成精準(zhǔn)的年份列表 155
10.2.3 動(dòng)態(tài)區(qū)域引用函數(shù)OFFSET ····156
10.2.4 制作動(dòng)態(tài)精準(zhǔn)年份下拉框 ·157
10.3 數(shù)組公式入門 · 158
10.3.1 數(shù)組公式的概念與輸入方法 ··158
10.3.2 數(shù)組公式初體驗(yàn) ···159
10.3.3 數(shù)組公式的計(jì)算過程 159
10.3.4 數(shù)組公式讓VLOOKUP逆向和多列查找 161
10.3.5 數(shù)組公式求績效總分 161
10.3.6 位置檢索函數(shù)MATCH ···162
10.3.7 ※自動(dòng)提取某列內(nèi)容的不重復(fù)列表 ····163
10.3.8 ※制作動(dòng)態(tài)精準(zhǔn)產(chǎn)品下拉框 ··165
10.3.9 ※生成動(dòng)態(tài)精準(zhǔn)銷售員名單 ··165
10.4 表格應(yīng)用技巧 · 167
10.4.1 表格最有用的幾個(gè)功能 ·167
10.4.2 如何創(chuàng)建表格并命名 170
10.4.3 使用表格公式自動(dòng)適應(yīng)變化 ··170
10.4.4 ※表格中的行列操作 172
10.4.5 ※調(diào)整表格大小 ···173
10.4.6 ※讓表格不自動(dòng)擴(kuò)展和填充公式 ····173
10.4.7 ※將表格轉(zhuǎn)換為單元格區(qū)域 ··174
10.5 切片器對透視表報(bào)表多維查看 · 174
10.6 透視表報(bào)表如何適應(yīng)變化 ···· 175
10.6.1 定義名稱實(shí)現(xiàn)動(dòng)態(tài)數(shù)據(jù)源 ·176
10.6.2 表格為透視表提供動(dòng)態(tài)數(shù)據(jù)源 ···176
10.6.3 刷新數(shù)據(jù)透視表 ···176
10.6.4 超強(qiáng)組合“表格+數(shù)據(jù)透視表+切片器” 177
10.7 報(bào)表自動(dòng)化中的實(shí)用技巧 ···· 178
10.7.1 查找數(shù)據(jù)時(shí)如何適應(yīng)列順序的調(diào)整 ····178
10.7.2 間接引用INDIRECT函數(shù) ····179
10.7.3 制作二級動(dòng)態(tài)關(guān)聯(lián)下拉框 ·180
10.7.4 獲取當(dāng)前工作表與工作簿名 ··181
10.7.5 生成所有工作表列表 182
第11章 讓圖表動(dòng)起來 ···183
11.1 動(dòng)態(tài)圖表原理及制作思路 185
11.2 利用數(shù)據(jù)驗(yàn)證+VLOOKUP函數(shù)制作動(dòng)態(tài)圖表 185
11.3 顯示表單控件 · 186
11.4 利用下拉框控件+INDEX函數(shù)制作動(dòng)態(tài)圖表 187
11.5 圖表自定義動(dòng)態(tài)標(biāo)題 ··· 189
11.6 使用名稱制作動(dòng)態(tài)圖表 ···· 189
11.7 使用更多表單控件 ·· 190
11.7.1 復(fù)選框控件 ··191
11.7.2 數(shù)值調(diào)節(jié)按鈕控件 ····191
11.7.3 列表框控件 ··192
11.7.4 單選按鈕控件 ···192
11.7.5 滾動(dòng)條控件 ··193
11.8 按部門查看最近N個(gè)月銷量趨勢圖 ···· 193
11.9 交互式數(shù)據(jù)透視圖 ·· 197
第12章 終極核武器VBA開發(fā) ··198
12.1 宏與VBA開發(fā) · 200
12.1.1 什么是“宏” ···200
12.1.2 宏不是Excel特有 ··200
12.1.3 VBA開發(fā)環(huán)境 ···201
12.1.4 如何運(yùn)行宏 ··202
12.1.5 為工程設(shè)置密碼保護(hù) 203
12.2 VBA基礎(chǔ)語法 · 203
12.2.1 數(shù)據(jù)類型 ·204
12.2.2 運(yùn)算符·204
12.2.3 變量與常量 ··204
12.2.4 自定義函數(shù)并在工作表中調(diào)用 ···205
12.2.5 為自定義函數(shù)定義參數(shù) ·205
12.3 VBA流程控制 · 206
12.3.1 分支語句 ·206
12.3.2 循環(huán)語句 ·207
12.3.3 錯(cuò)誤處理語句 ···208
12.4 Excel VBA常用對象 ···· 209
12.4.1 對象、屬性和方法 ····209
12.4.2 Application對象210
12.4.3 Workbooks集合對象 211
12.4.4 Workbook對象 ·211
12.4.5 Worksheets集合對象 ····212
12.4.6 Worksheet對象 ····212
12.4.7 Range對象 ···213
12.5 使用“錄制宏”快速學(xué)習(xí) ···· 214
12.6 VBA開發(fā)實(shí)例 · 215
12.6.1 刷新數(shù)據(jù)透視表 ···215
12.6.2 隱藏分組框控件的邊框線 ·215
12.6.3 去除所有工作表中的公式 ·216
12.6.4 批量創(chuàng)建工作表 ···216
12.6.5 合并多表數(shù)據(jù)并生成圖表 ·217
12.6.6 桌牌批量打印工具 ····217
12.6.7 工資表自動(dòng)生成系統(tǒng) 218
12.7 VBA小結(jié) ···· 218
后記 ·221