Excel報表一勞永逸（數據+函式+表格）

《Excel報表一勞永逸（數據+函式+表格）》是2019年清華大學出版社出版的圖書，作者是王德寶。

基本信息

Excel報表一勞永逸（數據+函式+表格）

作者：王德寶
　　定價：49元
印次：1-1
ISBN：9787302512417
出版日期：2019.03.01
印刷日期：2019.01.16

內容簡介

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輸入前導038

3.2.2快速輸入數值末尾的多個039

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.5Excel中的內置文本序列·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.1Excel其實只有兩種表·57

5.2畫蛇添足的多行標題58

5.3多此一舉的小計及匯總行59

5.4不要有合併單元格59

5.5該分成多列的沒拆分61

5.5.1使用分列功能拆分為多列·61

5.5.2文本截取函式LEFT/RIGHT/MID62

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多條件記數函式COUNTIFS85

6.1.1COUNTIFS使用場景與語法解讀·85

6.1.2COUNTIFS單條件計數·85

6.1.3COUNTIFS多條件計數·86

6.2多條件求和函式SUMIFS·86

6.3條件判斷函式IF87

6.3.1IF使用場景與語法解讀·87

6.3.2兩種情況的判斷88

6.3.3三種情況的判斷88

6.4查值函式VLOOKUP·89

6.4.1VLOOKUP使用場景與語法解讀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章表間黏合劑SQL123

8.1什麼是SQL124

8.2在哪裡使用SQL125

8.2.1使用現有連線125

8.2.2使用MicrosoftQuery126

8.3SQL訪問的數據對象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.5SQL小結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日期函式EOMONTH153

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位置檢索函式MATCH162

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.3VBA開發環境201

12.1.4如何運行宏202

12.1.5為工程設定密碼保護203

12.2VBA基礎語法203

12.2.1數據類型·204

12.2.2運算符·204

12.2.3變數與常量204

12.2.4自定義函式並在工作表中調用205

12.2.5為自定義函式定義參數·205

12.3VBA流程控制206

12.3.1分支語句·206

12.3.2循環語句·207

12.3.3錯誤處理語句208

12.4ExcelVBA常用對象209

12.4.1對象、屬性和方法·209

12.4.2Application對象210

12.4.3Workbooks集合對象211

12.4.4Workbook對象·211

12.4.5Worksheets集合對象·212

12.4.6Worksheet對象·212

12.4.7Range對象213

12.5使用“錄製宏”快速學習214

12.6VBA開發實例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.7VBA小結218

後記·221