Excel 高效辦公 數據處理與分析（第3版）

神龙工作室团队拥有多位国家重点院校教授、行业应用专业人士/设计师、Office/Windows/PS/CAD等方面的高手。其出版的《高效办公》系列中《新手学电脑》、《新手学上网》、《新手学Photoshop图像处理》、《外行学电脑从入门到精通》被中国书刊发行业协会评为“全行业优秀畅销品种”（科技类）

目 錄

第 1 章 數據的輸入

1.1 Excel可以用來做什麽 2

1.1.1 Excel到底能做什麽 2

1. 製作表單 2

2. 完成復雜的運算 2

3. 建立圖表 2

4. 數據管理 2

5. 決策指示 3

1.1.2 3種不同用途的表——數據表、統計報表、表單 3

1.2 基本數據的輸入 4

1.2.1 文本型數據的輸入 4

1.2.2 數值型數據的輸入 5

1.2.3 貨幣型數據的輸入 6

1.2.4 會計專用型數據的輸入 7

1.2.5 日期型數據的輸入 7

1.2.6 使用自定義快速輸入編號 9

1.2.7 自定義數據的格式 9

1. 文本和空格 10

2. 小數點、空格、顏色和條件 11

3. 貨幣、百分比和科學計數 12

4. 百分比 13

5. 日期和時間 13

1.2.8 使用自定義格式隱藏零值 15

1.3 有規律數據的填充 16

1.3.1 填充數字序列 16

1.3.2 填充文本序列 18

1.4 相同數據的填充 18

1.5 利用數據驗證，高效輸入數據 19

1.5.1 通過下拉列表填充 20

1.5.2 限定文本長度 21

 

第 2 章 數據的格式化

2.1 不規範表格及數據的整理技巧 24

2.1.1 表格結構的規範與整理 24

2.1.2 快速刪除空白行、空白列 26

1. 手動刪除 26

2. 通過篩選刪除 26

3. 使用函數刪除 27

4. 使用定位功能刪除 29

2.1.3 快速刪除重復值、重復記錄 30

1. 刪除重復項 31

2. 高級篩選法 31

2.1.4 不規範日期的整理技巧 32

1. 查找替換 32

2. 分列功能 33

2.1.5 取消合並單元格的整理技巧 34

2.2 套用Excel表格格式 36

2.2.1 套用系統自帶格式 36

2.2.2 自定義樣式 37

2.3 套用單元格樣式 39

2.3.1 套用系統自帶樣式 39

2.3.2 自定義樣式 40

2.4 條件格式的設置 43

2.4.1 突出顯示重點單元格 44

2.4.2 輔助識別數據大小 44

2.4.3 輔助用戶查看數據走向 45

 

第 3 章 排序、篩選與分類匯總

3.1 排序 50

3.1.1 單個關鍵字的排序 50

3.1.2 多個關鍵字的排序 51

3.1.3 自定義排序 52

3.2 篩選 53

3.2.1 自動篩選 53

1. 單一條件的篩選 53

2. 快速篩選前N項 54

3. 按字體顏色、單元格顏色篩選 55

4. 多條件的篩選 56

3.2.2 高級篩選 57

1. 同時滿足多個條件的篩選 57

2. 滿足其中一個條件的篩選 58

3.3 分類匯總 59

3.3.1 簡單分類匯總 59

3.3.2 高級分類匯總 61

3.3.3 嵌套分類匯總 61

1. 刪除分類匯總 62

2. 分類匯總 62

 

第 4 章 公式與函數的應用

4.1 認識公式與函數 67

4.1.1 初識公式 67

1. 單元格引用 68

2. 運算符 69

4.1.2 初識函數 69

1. 函數的基本構成 70

2. 函數的種類 70

4.2 文本函數 70

4.2.1 LEFT函數——從左側截取字符 70

4.2.2 RIGHT函數——從右側截取字符 72

4.2.3 MID函數——截取中間字符 73

4.2.4 FIND函數——查找字符位置 74

4.2.5 LEN函數——計算字符串長度 76

4.2.6 TEXT函數——字符串“整容”器 78

4.3 數學與三角函數 80

4.3.1 SUM函數——輕松求和 80

4.3.2 SUMIF函數——單一條件求和 81

4.3.3 SUMIFS函數——多條件求和 82

4.3.4 SUMPRODUCT——乘積求和 83

1. 一個參數 83

2. 兩個參數 84

3. 多個參數 84

4. 按條件求和 85

4.3.5 SUBTOTAL函數——輕松分類匯總 86

4.3.6 MOD函數——求餘計算器 90

4.3.7 INT函數——整數切割器 92

4.4 邏輯函數 94

4.4.1 IF函數——專精條件選擇 94

1. Excel 中的邏輯關系 94

2. 用於條件判斷的IF函數 94

4.4.2 OR函數——只要有一個值為真，結果就為真 95

4.4.3 AND函數——只要有一個值為假，結果就為假 97

4.4.4 IFS函數（2019）——多條件判斷很簡單 100

4.5 統計函數 103

4.5.1 COUNTA函數——非空格計算器 103

4.5.2 COUNT函數——數字計算器 104

4.5.3 MAX函數——極大值搜索器 105

4.5.4 MIN函數——極小值搜索器 106

4.5.5 AVERAGE函數——算數平均值計算器 107

4.5.6 COUNTIF函數——單條件自定義計算器 108

4.5.7 COUNTIFS函數——多條件自定義計算器 109

4.5.8 RANK.EQ函數——排序好幫手 111

4.6 查找與引用函數 112

4.6.1 VLOOKUP函數——縱向查找利器 112

4.6.2 HLOOKUP函數——橫向查找利器 115

4.6.3 MATCH函數——確定數據位置 117

4.6.4 LOOKUP函數——查找神器 119

1. LOOKUP函數進行縱向查找 120

2. LOOKUP函數進行橫向查找 122

3. LOOKUP函數進行條件判斷 122

4. LOOKUP函數進行逆向查詢 124

4.7 日期和時間函數 127

4.7.1 YEAR、MONTH、DAY函數——年、月、日函數 127

4.7.2 DATE函數——生成日期 129

4.7.3 DATEDIF函數——計算兩日期之差 132

4.7.4 EDATE函數——計算相差幾個月的日期 133

4.7.5 EMONTH函數——計算相差幾個月的月末日期 134

4.7.6 TODAY函數——計算當前日期 134

4.8 財務函數 136

4.8.1 SLN函數——平均折舊 136

4.8.2 DDB函數——指定期間折舊 137

4.8.3 SYD函數——年數總計折舊 139

4.8.4 PMT函數——等額還款 140

4.8.5 PPMT函數——等額還款本金 141

4.8.6 IPMT函數——等額還款利息 142

4.9 定義名稱 144

4.9.1 認識Excel中的名稱 144

4.9.2 定義名稱 145

1. 為數據區域定義名稱 145

2. 為數據常量定義名稱 146

3. 為公式定義名稱 147

4.9.3 編輯和刪除名稱 148

1. 編輯名稱 149

2. 刪除名稱 149

4.9.4 在公式中使用名稱 150

4.10 數組與數組公式 151

4.10.1 什麽是數組 151

4.10.2 什麽是數組公式 152

4.10.3 數組公式的應用 152

4.11 函數輸入、編輯中的技巧 153

4.11.1 根據需求搜索函數 153

4.11.2 函數參數不用背的秘密 154

4.11.3 函數較長記不住怎麽辦 154

4.11.4 事半功倍——快速輸入函數 155

4.11.5 通過單擊選擇的方式快速引用其他工作表數據 155

 

第 5 章 可視化分析工具——圖表

5.1 圖表的類型及應用原則 158

5.1.1 柱形圖及其適用場合 158

5.1.2 折線圖及其適用場合 159

5.1.3 餅圖及其適用場合 159

5.1.4 條形圖及其適用場合 160

5.1.5 面積圖及其適用場合 160

5.1.6 XY散點圖及其適用場合 161

5.1.7 股價圖及其適用場合 161

5.1.8 雷達圖及其適用場合 162

5.1.9 樹狀圖及其適用場合 162

5.1.10 旭日圖及其適用場合 162

5.1.11 直方圖及其適用場合 163

5.1.12 瀑布圖及其適用場合 163

5.1.13 漏鬥圖及其適用場合 164

5.2 圖表的創建 164

5.2.1 利用固定數據區域創建圖表 164

1. 連續區域的所有數據 165

2. 選定的部分數據 165

5.2.2 利用固定常量創建圖表 166

5.2.3 利用定義名稱創建圖表 168

5.3 圖表的編輯 170

5.3.1 更改圖表類型 171

1. 更改整個圖表的圖表類型 171

2. 更改某個數據系列的圖表類型 172

5.3.2 編輯數據系列 173

1. 減少數據系列 173

2. 增加數據系列 173

5.3.3 編輯圖表標題 175

5.3.4 編輯圖例 176

5.3.5 添加坐標軸標題 177

5.3.6 添加數據標簽 178

5.3.7 添加趨勢線 179

5.4 格式化圖表 180

5.4.1 保證圖表的完整性 180

5.4.2 格式化圖表區 181

5.4.3 格式化繪圖區 183

5.4.4 格式化圖表標題、坐標軸標題、圖例 183

5.4.5 格式化坐標軸 186

5.4.6 格式化數據系列 187

5.4.7 格式化數據標簽 189

5.5 製作高級圖表 190

5.5.1 用箭頭替代數據條 190

5.5.2 製作金字塔分佈圖 191

5.5.3 將精美小圖應用於圖表 195

5.5.4 雙軸復合圖表 198

5.6 製作動態圖表 200

5.6.1 利用查找函數創建動態圖表 200

5.6.2 利用INDIRECT函數創建動態圖表 203

1. INDIRECT函數基本解析 203

2. ADDRESS函數基本解析 204

3. ROW函數基本解析 204

4. CELL函數基本解析 204

5. 創建動態圖表 207

 

第 6 章 數據透視表與數據透視圖

6.1 創建數據透視表 210

6.1.1 認識數據和數據透視表 210

6.1.2 字段與值 210

6.1.3 創建初始數據透視表 211

6.2 佈局數據透視表 213

6.2.1 數據透視表字段窗格 213

6.2.2 數據透視表的佈局 214

1. 數據結構的佈局 214

2. 顯示方式的佈局 214

6.3 格式化數據透視表 215

6.4 數據透視表的排序與篩選 216

6.4.1 數據透視表的排序 216

6.4.2 數據透視表的篩選 217

6.5 生成數據透視圖 219

6.6 數據透視表（圖）的高級應用 220

6.6.1 使用切片器篩選 220

1. 插入並使用切片器 220

2. 設置切片器樣式 222

3. 一個切片器控制多個數據透視表（圖） 224

4. 刪除切片器 225

6.6.2 使用日程表篩選 225

6.7 數據透視表的計算 227

6.7.1 添加計算字段 228

6.7.2 添加計算項 229

 

第 7 章 數據分析工具的使用

7.1 安裝分析工具庫 233

7.2 模擬運算表 233

7.2.1 單變量模擬運算表 234

7.2.2 雙變量模擬運算表 235

7.3 單變量求解 236

7.4 規劃求解 237

7.4.1 線性規劃概述 237

7.4.2 加載規劃求解 238

7.4.3 實例：建立規劃求解模型——人員最優配置方案 238

7.5 分析工具庫 242

7.5.1 方差分析 242

7.5.2 相關系數 245

7.5.3 協方差 247

7.5.4 指數平滑 248

7.5.5 移動平均 251

 

第 8 章 生產決策數據的處理

8.1 生產成本分析 254

8.1.1 知識點 254

1. 公式的應用 254

2. 盈虧平衡點 254

3. IF函數的應用 254

4. 單變量求解的應用 254

8.1.2 成本分析的流程 254

8.1.3 實例：設備生產能力優化決策分析 255

8.1.4 實例：量本利分析 257

1. 降低單位變動成本 257

2. 降低固定成本 258

8.2 產品最優組合決策分析 259

8.2.1 知識點：規劃求解 259

8.2.2 決策分析的步驟 259

8.2.3 實例：利潤最大化 259

8.2.4 實例：成本最小化 263

 

第 9 章 銷售數據的處理

9.1 公司銷售發展趨勢分析 269

9.1.1 知識點 269

1. 趨勢分析 269

2. 趨勢分析的影響因素 269

3. 同比和環比 270

4. 發展速度 270

5. 平均發展速度 270

6. POWER函數 271

7. 增長速度 271

9.1.2 實例：計算銷售額的同比增速 271

9.1.3 實例：計算銷售額的環比發展速度 272

9.1.4 實例：計算銷售額的發展速度 274

9.1.5 實例：計算銷售額的平均發展速度 276

9.1.6 實例：計算銷售額的增長速度 276

9.1.7 實例：製作分析圖表 277

1. 同比分析圖表 277

2. 環比分析圖表 280

3. 銷售走勢圖表 282

9.2 公司銷售相關分析 286

9.2.1 知識點 286

1. 什麽是相關分析 286

2. 相關分析的分類 287

3. 相關表和相關圖 288

4. 相關系數 290

5. CORREL函數 290

9.2.2 實例：計算直通車與銷售額的相關程度 291

1. CORREL函數法 291

2. 數據分析工具庫 292

9.2.3 實例 直通車與銷售額相關性分析——散點圖 293

9.3 公司結構分析 296

9.3.1 知識點 296

1. 什麽是結構分析 296

2. 結構相對指標 297

3. 餅圖 297

9.3.2 實例：公司份額結構分析 297

9.3.3 實例：分析各類商品的貢獻率 301

9.4 門店的客流量分析 303

9.4.1 知識點 303

1. 描述分析 303

2. 刪除重復值 303

3. 數據透視表 303

4. 折線圖 303

9.4.2 實例：確定客流高峰期的時間段 303

1. 刪除重復訂單編號 304

2. 匯總各時間段的訂單量 305

3. 折線圖——清晰展示客流量 307

 

第 10 章 人力資源數據的處理

10.1 在職人員結構分析 310

10.1.1 知識點 310

1. 自動篩選 310

2. COUNTIFS函數 310

3. 數據透視表 310

10.1.2 實例：統計在職人員結構信息 310

1. 篩選法 311

2. 函數法 315

3. 數據透視表法 320

10.1.3 實例：分析在職人員結構 326

1. 簡單圖表 326

2. 多級聯動圖表 328

10.2 分析人員流動情況 331

10.2.1 知識點 331

1. SUMPRODUCT函數 331

2. YEAR函數 332

3. MONTH函數 332

10.2.2 實例：統計分析各部門的人員流動情況 332

1. 計算入職人數 332

2. 計算離職人數 333

3. 分析人員流動 335

 

第 11 章 財務數據的處理

11.1 財務比率分析 339

11.1.1 知識點 339

1. 變現能力比率 339

2. 資產管理比率 339

3. 負債比率 341

4. 盈利能力比率 342

11.1.2 實例：計算變現能力比率 343

11.1.3 實例：計算資產管理比率指標 343

11.1.4 實例 計算負債比率指標 346

11.1.5 實例：計算盈利能力比率指標 348

11.2 財務比較分析 349

11.3 財務趨勢分析 354

11.4 杜邦分析 355

11.4.1 知識點 355

1. 資產凈利率 355

2. 權益乘數 356

3. 權益凈利率 356

11.4.2 實例：計算各財務比率 356