Excel報表一勞永逸：數據+函數+表格（簡體書）

Excel 是職場辦公人員的必備工具，然而大多數用戶只會進行較基礎的操作，在製作報表時常常採用手工操作等低效的方法，加班加點、費時費力。究其原因，一是不理解 Excel 的規則和理念，二是缺乏相應的技巧。 本書開篇即闡明製作報表的正確步驟。接下來按此步驟介紹大量的技巧：在採集數據階段，如何設計規範的數據表格；在加工整合階段，如何進行數據表格格式轉換及查漏補缺；在分析呈現階段，如何應用常用的統計函數及數據透視表，以及使用 SQL 技巧。在本書的最後，系統總結了“一勞永逸”報表的製作理念和具體方法。 本書旨在幫助廣大用戶提高報表製作效率，將原本幾天的工作減少為一天甚至半天，將原本每個月都要做報表變為只需做一次即可，將用戶從報表困境中解脫出來！

目錄

第一部分 必備知識

第1章萬丈高樓平地起 2
1.1 工作表與工作簿·3
1.2 單元格·3
1.3 區域4
1.4 活動單元格4
1.5 選項卡與功能區·5
1.6 名稱5
1.6.1 名稱的概念及作用5
1.6.2 名稱的快速創建 6
1.6.3 根據所選內容批量創建名稱 6
1.6.4 名稱修改與刪除 7
1.7 函數7
1.7.1 函數的概念與構成7
1.7.2 如何有效地學習函數9
1.8 單元格引用方式及其區別10
1.8.1 公式計算結果為何出錯10
1.8.2 如何讓行號列標填充時不變化10
1.8.3 相對引用、絕對引用與混合引用11
1.8.4 公式中4種引用方式的選用與快速變換11
1.9 快速選取單元格的方法·12
1.9.1 選取連續單元格12
1.9.2 選取不連續單元格·13
1.9.3 使用名稱框選取連續及不連續單元格·13
1.9.4 使用定位選取特定單元格·13
1.9.5 使用查找選取特定單元格·15
1.10 ※當前區域16
1.11 ※R1C1引用樣式17
1.12 ※關於名稱的幾點補充17
1.12.1 名稱的作用·17
1.12.2 使用功能區命令定義名稱18
1.12.3 名稱的兩種作用範圍19
1.12.4 給名稱起名字注意事項19
1.12.5 查看已定義的名稱·20
1.12.6 名稱管理器高級應用技巧20
1.12.7 名稱相同時的處理規則21
1.12.8 名稱定義中的相對引用與絕對引用·22
1.12.9 將名稱應用到公式中22

第二部分 採集數據
第2章設計Excel數據采集模板 26
2.1 搞懂Excel中的數據類型27
2.2 揭開日期類型的真面目·27
2.3 各種類型的數據如何識別 28
2.4 利用數據驗證限定數據符合要求 29
2.4.1 限定輸入數值型字段 ·30
2.4.2 限定輸入文本型字段 ·31
2.4.3 限定日期與時間型字段 31
2.4.4 製作下拉框進行選擇 ·32
2.4.5 ※限定不能重複輸入 ·33
2.5 為模板添加保護措施以防修改 ·33
2.5.1 保護工作表 ·34
2.5.2 為不同單元格區域設定不同的修改密碼 35
2.5.3 為工作簿加把鎖 35
2.6 ※使用IS函數判斷數據類型 ·36
第3章 數據輸入技巧 37

3.1 身份證號碼的輸入方法 ·38
3.2 數值輸入技巧 ·38
3.2.1 輸入前導0 38
3.2.2 快速輸入數值末尾的多個0 39
3.2.3 自動插入小數點 39
3.3 分數的輸入方法 40
3.4 日期與時間輸入方法 40
3.4.1 當前日期輸入 41
3.4.2 ※日期的簡化輸入法 ·41
3.4.3 ※幾十年後兩位年份怎麼辦 ·42
3.4.4 時間與當前時間輸入 ·42
3.4.5 日期與時間組合輸入 ·43
3.4.6 ※使用下拉框輸入日期與時間 43
3.5 在一個單元格內輸入多行內容 ·44
3.6 使用自動下拉框輸入已有數據 ·44
3.7 填充序列與自定義序列 ·45
3.7.1 生成1，2，3，4，…自增序號 ·45
3.7.2 生成1，3，5，7，…等差數列 ·46
3.7.3 生成按天遞增或僅工作日的日期序列 ·46
3.7.4 使用填充序列對話框 ·47
3.7.5 Excel中的內置文本序列 ·47
3.7.6 添加自定義序列 48
3.8 一鍵批量輸入多個單元格 48
3.9 工作組模式對多張工作表批量輸入 49
3.10 更改回車鍵移動方向 ·50

第4章 獲取外部數據 51
4.1 導入文本文件 ·52
4.1.1 按分隔符號導入 52
4.1.2 按固定寬度導入 53
4.2 導入網頁數據 ·54

第三部分 加工整合
第5章 合理規範的表格更利於分析 56
5.1 Excel其實只有兩種表 ·57
5.2 畫蛇添足的多行標題 58
5.3 多此一舉的小計及匯總行 59
5.4 不要有合並單元格 59
5.5 該分成多列的沒拆分 61
5.5.1 使用分列功能拆分為多列 ·61
5.5.2 文本截取函數LEFT/RIGHT/MID 62
5.6 試圖用顏色區分不同類型 63
5.6.1 ※根據顏色自動填充文本 ·63
5.7 錯誤的數值格式 64
5.7.1 利用“轉換為數字”命令 ·64
5.7.2 使用選擇性粘貼 64
5.8 非法或不完整的日期格式 65
5.8.1 修正“20070820”8位年月日格式 ·65
5.8.2 使用DATE函數修正8位年月日格式 66
5.8.3 修正“2007.08.20”帶小圓點格式 ·67
5.8.4 ※修正“2007.8.20”月日長度不固定的格式 ·67
5.8.5 只有月日的日期處理方法 ·69
5.8.6 “XXXX年XX月XX日”格式的處理方法 ·69
5.8.7 將標準日期轉換成其他格式 ·69
5.9 數據庫不要使用二維表 ·70
5.9.1 多重合並數據透視表轉換二維表 70
5.9.2 ※使用INDEX/ROW/INT/MOD函數轉換二維表 ·71
5.9.3 ※逆透視法轉換二維表 73
5.10 其他數據及表格結構轉換 73
5.10.1 將兩列合併成一列 ·74
5.10.2 將多列合併成一列 ·74
5.10.3 一列裡都添加上同樣的文字 ·75
5.10.4 刪除重複數據保留唯一值 75
5.10.5 轉置功能將表格橫向縱向相互轉換 ·76
5.10.6 ※更複雜的橫向或縱向表格數據處理 76
5.11 神奇的快速填充 77
5.12 表格設計實例 ·78
5.12.1 資產折舊表 ·78
5.12.2 產品銷量表 ·80
5.12.3 客戶維修記錄表 80
5.12.4 木業公司工資表 81
5.12.5 小結：何時要用“數據庫”表 82

第四部分 分析呈現
第6章 使用函數統計報表數據 84
6.1 多條件記數函數COUNTIFS 85
6.1.1 COUNTIFS使用場景與語法解讀 ·85
6.1.2 COUNTIFS單條件計數 ·85
6.1.3 COUNTIFS多條件計數 ·86
6.2 多條件求和函數SUMIFS ·86
6.3 條件判斷函數IF 87
6.3.1 IF使用場景與語法解讀 ·87
6.3.2 兩種情況的判斷 88
6.3.3 三種情況的判斷 88
6.4 查值函數VLOOKUP ·89
6.4.1 VLOOKUP使用場景與語法解讀 89
6.4.2 參數“查找區域”的潛規則 ·89
6.4.3 第4個參數：精確和近似查找 ·90
6.4.4 何時用精確，何時用近似 ·91
6.5 函數綜合應用實例 92
6.6 公式錯誤處理 ·94
6.6.1 公式報錯原因及處理對策 ·94
6.6.2 屏蔽公式錯誤 95

第7章 強大的報表工具――數據透視表 96
7.1 創建透視表 98
7.2 三種數據類型在數據透視表中的作用 99
7.3 更改值匯總依據 100
7.4 設置數字格式 101
7.5 設置值顯示方式 101
7.6 對行/列標簽字段組合 105
7.6.1 對數值字段分區間匯總 105
7.6.2 對文本字段分組 ·106
7.6.3 對日期字段按月/季度/年度匯總 107
7.7 調整字段順序 107
7.7.1 在區域中調整字段順序 108
7.7.2 在透視表中調整值字段順序 ·108
7.7.3 在透視表中調整標簽字段順序 108
7.8 插入計算字段 109
7.9 在透視表中排序 110
7.9.1 各種數據類型排序規則 110
7.9.2 按一列值字段排序111
7.9.3 按多列值字段排序111
7.9.4 對標簽字段自定義排序 112
7.10 在透視表中篩選 113
7.10.1 按標簽字段篩選 114
7.10.2 按值字段篩選 115
7.10.3 直接在透視表中篩選 116
7.11 更改報表佈局 117
7.12 添加行/列小計與總計 118
7.13 更改數據源引用位置 ·119
7.14 從數據透視表到數據透視圖 120
7.15 數據透視表更多應用技巧 120
7.15.1 在現有工作表中創建透視表 120
7.15.2 移動透視表位置 121
7.15.3 透視表中雙擊查看數據明細 121
7.15.4 不讓透視表顯示錯誤值和調整列寬 ·121
7.15.5 更改字段與區域排列方式 ·122
7.15.6 延遲佈局更新 122

第8章 表間黏合劑SQL 123
8.1 什麼是SQL 124
8.2 在哪裡使用SQL 125
8.2.1 使用現有連接 125
8.2.2 使用Microsoft Query 126
8.3 SQL訪問的數據對象 128
8.3.1 系統表 128
8.3.2 命名區域（名稱）128
8.4 常用SQL查詢語句 129
8.4.1 按條件查詢 ·129
8.4.2 查詢其他工作簿數據 130
8.4.3 多表合併查詢 131
8.4.4 多表關聯查詢 131
8.5 SQL小結 133

第9章 報表格式與查看 ·134
9.1 複製並粘貼格式和列寬 135
9.2 運用單元格樣式統一格式 135
9.3 套用表格格式及自定義表樣式 136
9.4 快速更改數據透視表樣式 136
9.5 設置工作表背景 137
9.6 為報表添加條件格式 137
9.6.1 不同數據突出顯示為不同顏色 138
9.6.2 選取數據TOP10%或最大/最小N項 139
9.6.3 用數據條長短直觀表示數據大小 ·139
9.6.4 色階與圖標集 140
9.6.5 設置合同到期前30天變色提醒 140
9.6.6 條件格式優先級與清除 142
9.7 小而美的迷你圖 142
9.7.1 什麼是迷你圖 142
9.7.2 創建迷你圖 ·142
9.8 窗口界面自定義 143
9.8.1 自定義快速訪問工具欄 143
9.8.2 創建個性化選項卡144
9.8.3 快速隱藏或顯示功能區 144
9.8.4 不顯示網格線、標題和編輯欄 145
9.8.5 不顯示多餘的行和列 145
9.8.6 隱藏工作表標簽與滾動條 ·146
9.9 報表查看技巧 146
9.9.1 凍結窗格固定住標題行 146
9.9.2 快速調整工作表顯示比例 ·147
9.9.3 窗口拆分與重排 ·147
9.9.4 報表導航系統 148

第五部分 一勞永逸
第10章 一勞永逸的報表製作秘訣 150
10.1 在函數報表裡多維查看 152
10.1.1 按產品查看報表 152
10.1.2 日期函數EOMONTH 153
10.1.3 按年份及月份查看報表 ·153
10.2 函數報表自動適應變化 154
10.2.1 自動包含新增的行記錄 ·155
10.2.2 生成精准的年份列表 155
10.2.3 動態區域引用函數OFFSET ·156
10.2.4 製作動態精准年份下拉框 ·157
10.3 數組公式入門 158
10.3.1 數組公式的概念與輸入方法 158
10.3.2 數組公式初體驗 159
10.3.3 數組公式的計算過程 159
10.3.4 數組公式讓VLOOKUP逆向和多列查找 161
10.3.5 數組公式求績效總分 161
10.3.6 位置檢索函數MATCH 162
10.3.7 ※自動提取某列內容的不重複列表 ·163
10.3.8 ※製作動態精准產品下拉框 165
10.3.9 ※生成動態精准銷售員名單 165
10.4 表格應用技巧 167
10.4.1 表格最有用的幾個功能 ·167
10.4.2 如何創建表格並命名 170
10.4.3 使用表格公式自動適應變化 170
10.4.4 ※表格中的行列操作 172
10.4.5 ※調整表格大小 173
10.4.6 ※讓表格不自動擴展和填充公式 ·173
10.4.7 ※將表格轉換為單元格區域 174
10.5 切片器對透視表報表多維查看 174
10.6 透視表報表如何適應變化 175
10.6.1 定義名稱實現動態數據源 ·176
10.6.2 表格為透視表提供動態數據源 176
10.6.3 刷新數據透視表 176
10.6.4 超強組合“表格+數據透視表+切片器” 177
10.7 報表自動化中的實用技巧 178
10.7.1 查找數據時如何適應列順序的調整 ·178
10.7.2 間接引用INDIRECT函數 ·179
10.7.3 製作二級動態關聯下拉框 ·180
10.7.4 獲取當前工作表與工作簿名 181
10.7.5 生成所有工作表列表 182

第11章 讓圖表動起來 183
11.1 動態圖表原理及製作思路 185
11.2 利用數據驗證+VLOOKUP函數製作動態圖表 185
11.3 顯示表單控件 186
11.4 利用下拉框控件+INDEX函數製作動態圖表 187
11.5 圖表自定義動態標題 189
11.6 使用名稱製作動態圖表 189
11.7 使用更多表單控件 190
11.7.1 複選框控件 191
11.7.2 數值調節按鈕控件 ·191
11.7.3 列表框控件 192
11.7.4 單選按鈕控件 192
11.7.5 滾動條控件 193
11.8 按部門查看最近N個月銷量趨勢圖 193
11.9 交互式數據透視圖 197

第12章 終極核武器VBA開發 198
12.1 宏與VBA開發 200
12.1.1 什麼是“宏” 200
12.1.2 宏不是Excel特有 200
12.1.3 VBA開發環境 201
12.1.4 如何運行宏 202
12.1.5 為工程設置密碼保護 203
12.2 VBA基礎語法 203
12.2.1 數據類型 ·204
12.2.2 運算符·204
12.2.3 變量與常量 204
12.2.4 自定義函數並在工作表中調用 205
12.2.5 為自定義函數定義參數 ·205
12.3 VBA流程控制 206
12.3.1 分支語句 ·206
12.3.2 循環語句 ·207
12.3.3 錯誤處理語句 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 使用“錄制宏”快速學習 214
12.6 VBA開發實例 215
12.6.1 刷新數據透視表 215
12.6.2 隱藏分組框控件的邊框線 ·215
12.6.3 去除所有工作表中的公式 ·216
12.6.4 批量創建工作表 216
12.6.5 合併多表數據並生成圖表 ·217
12.6.6 桌牌批量打印工具 ·217
12.6.7 工資表自動生成系統 218
12.7 VBA小結 218
後記 ·221