目 錄

第1章 構建基礎數據 / 001

1.1 輸入新數據 / 002

1.1.1 Excel中的數據類型 / 002

1.1.2 輸入數據的3種模式 / 004

1.1.3 快速輸入由純數字組成的員工編號 / 005

1.1.4 快速輸入由字母和數字組成的員工編號 / 006

1.1.5 輸入18位身份證號 / 006

1.1.6 輸入分數 / 007

1.1.7 輸入指數 / 007

1.1.8 自動輸入小數點 / 008

1.1.9 輸入日期和時間 / 009

1.1.10 快速輸入一繫列連續的日期 / 010

1.1.11 以指定的時間間隔輸入一繫列日期 / 010

1.1.12 按學歷從高到低輸入學歷的名稱 / 011

1.1.13 換行輸入數據 / 013

1.1.14 同時在格中輸入數據 / 014

1.1.15 使用記憶式鍵入和下拉列表選擇功能提高輸入效率 / 014

1.2 導入現有數據 / 015

1.2.1 導入文本文件中的數據 / 016

1.2.2 導入Access數據庫中的數據 / 019

1.2.3 使用復制和粘貼功能導入數據 / 020

1.3 限制數據的輸入和編輯 / 023

1.3.1 輸入性別時隻允許輸入“男”或“女” / 023

1.3.2 禁止輸入重復的員工編號 / 025

1.3.3 禁止修改員工信息 / 026

第2章 整理數據 / 028

2.1 快速選擇所需的數據 / 029

2.1.1 選擇一個或格 / 029

2.1.2 選擇整行或整列 / 029

2.1.3 選擇格 / 030

2.1.4 使用名稱框格 / 030

2.1.5 選擇符合特定條格 / 030

2.1.6 選擇多個工作表格 / 032

2.2 規範化數據 / 032

2.2.1 使用分列功能快速拆分員工姓名和部門名稱 / 033

2.2.2 使用替換功能批量修改錯誤的產品編號 / 034

2.2.3 修復使用小數點分隔的日期 / 035

2.2.4 轉換數據類型 / 036

2.3 設置數據的顯示方式 / 038

2.3.1 設置員工信息表的標題的文字格式 / 038

2.3.2 居中排列銷售明細表中的所有數據 / 040

2.3.3 將“工資”設置為貨幣格式 / 042

2.3.4 以“萬”為單位顯示銷售額 / 042

2.3.5 隱藏銷售明細表中值為零的銷量數據 / 045

2.3.6 使用樣式快速為數據設置多種格式 / 046

第3章 數據計算基礎——公式和函數快速入門 / 049

3.1 輸入和編輯公式與函數 / 050

3.1.1 公式的組成 / 050

3.1.2 輸入和修改公式 / 051

3.1.3 移動和復制公式 / 052

3.1.4 改變公式的計算方式 / 054

3.1.5 函數的類型 / 055

3.1.6 在公式中輸入函數及其參數 / 057

3.1.7 在公式中引用其他工作表或工作簿中的數據 / 060

3.1.8 創建數組公式 / 062

3.2 創建和使用名稱 / 064

3.2.1 使用名稱框創建名稱 / 064

3.2.2 使用“新建名稱“對話框創建名稱 / 064

3.2.3 根據所選內容創建名稱 / 065

3.2.4 為常量和公式創建名稱 / 066

3.2.5 在公式中使用名稱 / 066

3.3 處理公式中的錯誤 / 067

3.3.1 使用公式錯誤檢查器 / 068

3.3.2 使用公式求值器 / 069

3.3.3 格之間的關繫 / 070

3.3.4 快速刪除所有錯誤值 / 072

第4章 處理文本信息——文本函數 / 073

4.1 查找和提取文本 / 074

4.1.1 FIND函數和SEARCH函數 / 074

4.1.2 LEFT函數、RIGHT函數和MID函數 / 074

4.1.3 LEN函數和LENB函數 / 075

4.1.4 計算文本包含的漢字個數和數字個數 / 076

4.1.5 從混合文本中提取公司名稱 / 077

4.1.6 從身份證號碼中提取出生日期和性別 / 077

4.1.7 統計特定字符的個數 / 078

4.2 轉換和設置文本格式 / 079

4.2.1 LOWER函數和UPPER函數 / 079

4.2.2 REPLACE函數和SUBSTITUTE函數 / 079

4.2.3 TEXT函數 / 080

4.2.4 將英文轉換為句首字母大寫、其他字母小寫 / 081

4.2.5 自定義公司名稱的顯示方式 / 081

4.2.6 將普通數字格式設置為電話號碼格式 / 082

4.2.7 評定員工的考核成績 / 082

第5章 使用公式對數據進行求和與統計——數學

函數和統計函數 / 083

5.1 求極值 / 084

5.1.1 MAX函數和MIN函數 / 084

5.1.2 LARGE函數和SMALL函數 / 084

5.2 數據求和 / 085

5.2.1 SUM函數 / 085

5.2.2 SUMIF函數和SUMIFS函數 / 086

5.2.3 SUMPRODUCT函數 / 088

5.2.4 SUBTOTAL函數 / 089

5.2.5 對月銷量進行累計求和 / 090

5.2.6 彙總特定部門中的所有員工的年薪總和 / 091

5.2.7 計算銷售額位於前兩名和後兩名的銷售額總和 / 091

5.2.8 計算指定銷售額範圍內的銷售額總和 / 091

5.2.9 計算商品打折後的總價格 / 092

5.3 統計數量 / 092

5.3.1 COUNT函數和COUNTA函數 / 092

5.3.2 COUNTIF函數和COUNTIFS函數 / 094

5.3.3 統計姓名不重復的員工人數 / 095

5.3.4 統計遲到人數 / 095

5.3.5 彙總單日累計銷量總和 / 096

5.3.6 計算銷量前5名的銷量總和 / 096

第6章 使用公式處理日期和時間——日期和時間

函數 / 097

6.1 日期計算 / 098

6.1.1 TODAY函數和NOW函數 / 098

6.1.2 DATE函數 / 098

6.1.3 YEAR函數、MONTH函數和DAY函數 / 099

6.1.4 WEEKDAY函數 / 100

6.1.5 EDATE函數和EOMONTH函數 / 100

6.1.6 DATEDIF函數 / 101

6.1.7 提取日期和時間 / 102

6.1.8 計算當月的總天數 / 103

6.1.9 判斷閏年 / 103

6.1.10 計算母親節和父親節的日期 / 103

6.1.11 計算工資結算日期 / 104

6.1.12 計算員工工齡 / 104

6.1.13 計算還款日期 / 105

6.2 時間計算 / 105

6.2.1 TIME函數 / 105

6.2.2 HOUR函數、MINUTE函數和SECOND函數 / 106

6.2.3 計算加班費 / 106

6.2.4 安排會議時間 / 107

6.2.5 計算顧客的用餐時長 / 107

第7章 數據的基本分析——排序、篩選和分類彙總 / 108

7.1 排序數據 / 109

7.1.1 排序規則 / 109

7.1.2 按工資高低對員工進行降序排列 / 109

7.1.3 按日期和銷量對商品進行排序 / 110

7.1.4 按學歷高低對員工進行降序排列 / 111

7.2 篩選數據 / 113

7.2.1 進入和退出篩選模式 / 113

7.2.2 篩選出學歷為“大專”的員工信息 / 114

7.2.3 篩選出除博士和碩士外的其他學歷的員工信息 / 114

7.2.4 篩選出銷量大於60的商品銷售記錄 / 116

7.2.5 篩選出5月16日和5月18日的商品銷售記錄 / 116

7.2.6 篩選出碩士學歷或工資大於3的員工信息 / 118

7.2.7 利用篩選刪除重復數據 / 119

7.3 分類彙總數據 / 120

7.3.1 按日統計各個商品的銷量 / 120

7.3.2 按日分類統計各個商品的銷量 / 121

7.3.3 分級查看彙總數據 / 123

7.3.4 刪除分類彙總 / 123

第8章 多角度數據分析——數據透視表 / 124

8.1 數據透視表的結構和術語 / 125

8.1.1 數據透視表的結構 / 125

8.1.2 數據透視表常用術語 / 126

8.2 創建和編輯數據透視表 / 127

8.2.1 創建銷售分析數據透視表 / 127

8.2.2 通過布局字段創建商品在各個地區銷量彙總的報表 / 128

8.2.3 改變數據透視表的整體布局 / 130

8.2.4 將帶有“求和項”的銷量字段重命名為“銷量” / 131

8.2.5 刷新數據透視表 / 132

8.2.6 更改數據源 / 133

8.2.7 移動、復制和刪除數據透視表 / 133

8.3 設置數據透視表數據的顯示方式 / 135

8.3.1 顯示或隱藏銷售分析報表中的明細數據 / 135

8.3.2 按地區對商品銷量進行彙總 / 136

8.3.3 使用多種方式彙總銷售數據 / 137

8.3.4 設格和錯誤值的顯示方式 / 138

8.4 分析和計算數據透視表中的數據 / 139

8.4.1 排序和篩選數據 / 140

8.4.2 按月統計商品銷量 / 142

8.4.3 按地理區域統計商品銷量 / 143

8.4.4 分析不同地區各類商品的銷量占比情況 / 144

8.4.5 創建顯示商品單價的計算字段 / 146

8.4.6 創建計算兩個地區銷量差異的計算項 / 148

第9章 更專業的數據分析——模擬運算表、

單變量求解、規劃求解和分析工具庫 / 150

9.1 創建模擬運算表 / 151

9.1.1 使用普通公式計算不同貸款期數對應的每月還款額 / 151

9.1.2 使用模擬運算表計算不同貸款期數對應的每月還款額 / 152

9.1.3 計算不同利率和貸款期數對應的每月還款額 / 153

9.1.4 使用“方案”功能分析多組不同條件下的數據 / 154

9.2 使用單變量求解進行逆向的模擬分析 / 156

9.3 使用規劃求解 / 157

9.3.1 加載規劃求解 / 157

9.3.2 生產收益化問題 / 159

9.4 使用分析工具庫 / 161

9.4.1 使用直方圖分析員工年齡分布情況 / 161

9.4.2 使用描述統計分析商品銷售情況 / 163

第10章 數據展示——圖表、迷你圖和條件格式 / 165

10.1 圖表基礎 / 166

10.1.1 圖表類型和結構 / 166

10.1.2 嵌入式圖表和圖表工作表 / 168

10.1.3 創建北京和上海地區的銷量對比圖表 / 169

10.1.4 更改圖表類型 / 171

10.1.5 設置圖表的整體布局和配色 / 172

10.1.6 設素的格式 / 175

10.1.7 向銷量分析圖表中添加銷售數據 / 176

10.1.8 創建圖表模板 / 178

10.1.9 刪除圖表 / 179

10.2 圖表實戰 / 179

10.2.1 制作可以動態捕獲數據源的圖表 / 179

10.2.2 制作用於追蹤任務進度的甘特圖 / 182

10.2.3 制作各部門不同學歷人數對比的條形圖 / 184

10.2.4 靈活控制餅圖中顯示特定月份的商品銷量 / 186

10.2.5 使用滾動條控制柱形圖的顯示 / 188

10.3 使用迷你圖和條件格式 / 190

10.3.1 迷你圖類型 / 190

10.3.2 創建商品銷量分析迷你圖 / 191

10.3.3 設置迷你圖格式 / 192

10.3.4 編輯和刪除迷你圖 / 193

10.3.5 突出顯示前3名銷量 / 194

10.3.6 突出顯示姓名相同的員工 / 195

本書的寫作目的是幫助讀者快速掌握Excel 中用於數據處理和分析的各種功能和技術，使讀者輕松完成在Excel 中處理和分析數據的相關任務，解決工作中遇到的問題。本書主要有以下3 個特點。

 全書各章包含大量案例，讀者可以邊學邊練。大多數3級標題都以案例名稱命名，既便於讀者從目錄中快速找到相應的案例，又能讓讀者了解每個知識點、每項技術的實際應用場景和方法，將技術與應用融為一體、無縫對接。

案例的選擇以貼近工作、易上手、易操作為標準，便於讀者理解和動手練習。

每學完一個案例，都能有立竿見影的效果，使讀者對後續的學習更有信心。

在每個關鍵操作對應的圖中使用框線進行醒目標注，讀者可以快速找到操作的關鍵點，讀圖時間。

本書以Excel
2016 為主要操作環境，內容也適用於Excel 2019 及Excel
2016之前的Excel 版本，如果讀者正在使用Excel
2007/2010/2013/2019 中的任意一個版本，則界面環境與Excel 2016 差別很小。

本書共有10
章，以數據處理和分析的整體流程（構建基礎數據→整理數據→分析數據→展示數據）來設計全書的結構及每章內容，本書各章的內容簡介如表所示。

本書適合以下讀者閱讀：

◆ 以Excel 為主要工具進行數據處理和分析的各行業人員。

◆ 需要在Excel 中制作各類表格和圖表的用戶。

◆ 希望掌握Excel 函數、公式、圖表、數據透視表和高級分析工具的用戶。

◆ 對使用Excel 處理和分析數據感興趣的用戶。

本書附贈以下配套資源：

◆ 本書所有案例的源文件。

◆ 本書重點案例的多媒體視頻教程。

如果在使用本書的過程中遇到問題，或者對本書的編寫有一些意見或建議，可以通過以下方式與作者聯繫：

◆ 郵箱：songxiangbook@163.com。

◆ 微博：@ 宋翔book，微博網址http://weibo.com/songxiangbook。

◆ 作者QQ：188171768，加QQ 時請注明“輕松學”以驗證身份。

◆ 讀者QQ 群：150469075，加群時請注明“輕松學”以驗證身份。