Excel零基礎高效辦公與數據處理實踐案例視頻精講

 

目錄

第1章.Excel工作表操作技能與技巧...............................01

1.1　選擇工作表..................................................................................................02

1.1.1　選擇一個工作表的常規方法........................................................................02

1.1.2　選擇多個工作表的常規方法........................................................................02

1.1.3　使用“激活”對話框快速選擇某個工作表..............................................02

1.1.4　使用名稱框快速選擇某個工作表...............................................................03

1.1.5　使用“定位”對話框快速選擇某個工作表..............................................03

1.1.6　快速選擇定義有某個名稱的工作表...........................................................03

1.1.7　快速選擇有某個特殊數據的工作表...........................................................04

1.1.8　快速切換工作表.............................................................................................04

1.2　隱藏和保護工作表.......................................................................................05

1.2.1　隱藏/顯示工作表的普通方法....................................................................05

1.2.2　隱藏/顯示工作表的特殊方法....................................................................06

1.2.3　防止所有工作表被刪除、移動、復制和重命名.....................................07

1.2.4　保護工作表全部數據不被改動...................................................................08

1.3　工作表的其他操作.......................................................................................09

1.3.1　插入新工作表..................................................................................................09

1.3.2　移動工作表......................................................................................................10

1.3.3　復制工作表......................................................................................................10

1.3.4　將工作表復制為新工作簿............................................................................10

1.3.5　重命名工作表..................................................................................................11

1.3.6　刪除工作表......................................................................................................11

1.3.7　設置工作表標簽顏色.....................................................................................11

1.4　工作表界面顯示設置....................................................................................12

1.4.1　顯示/不顯示網格線.....................................................................................12

1.4.2　設置網格線顏色.............................................................................................13

1.4.3　顯示/不顯示分頁預覽.................................................................................14

1.4.4　顯示/不顯示表格的零值............................................................................15

1.4.5　拆分窗格..........................................................................................................16

1.4.6　凍結窗格..........................................................................................................17

1.4.7　並排顯示工作表.............................................................................................17

1.5　設置Excel選項............................................................................................19

1.5.1　設置新工作簿默認的工作表數...................................................................20

1.5.2　設置新工作簿默認的字體和字號...............................................................20

1.5.3　設置自動重算/手動重算............................................................................21

1.6　自定義快速訪問工具欄和功能區.................................................................22

1.6.1　自定義快速訪問工具欄................................................................................22

1.6.2　自定義功能區..................................................................................................23

 

第2章.Excel單元格操作技能與技巧...............................25

2.1　快速選擇單元格...........................................................................................26

2.1.1　選擇單元格的常規方法................................................................................26

2.1.2　使用快捷鍵快速選擇較大的單元格區域..................................................26

2.1.3　使用名稱框快速選擇單元格區域...............................................................27

2.1.4　使用“定位”對話框快速選擇單元格......................................................27

2.1.5　使用“定位條件”對話框快速選擇特殊單元格.....................................28

2.1.6　快速選擇有特定數據的單元格...................................................................29

2.1.7　快速選擇有指定格式的單元格...................................................................30

2.1.8　快速選取所有的合並單元格........................................................................31

2.1.9　快速選擇數據區域的第一個單元格和最後一個單元格........................31

2.1.10　同時選擇幾個工作表的相同單元格區域..................................................31

2.2　操作單元格實用技能與技巧.........................................................................32

2.2.1　復制單元格的常見操作................................................................................32

2.2.2　將單元格復制為圖片.....................................................................................34

2.2.3　移動單元格......................................................................................................35

2.2.4　插入單元格......................................................................................................35

2.2.5　插入復制的單元格.........................................................................................35

2.2.6　一次插入多個空行.........................................................................................35

2.2.7　每隔幾行插入幾個空行................................................................................36

2.2.8　刪除單元格......................................................................................................36

2.2.9　刪除數據區域中所有的空行和空列...........................................................37

2.2.10　取消合並單元格...........................................................................................37

2.2.11　跨列居中顯示數據.......................................................................................38

2.3　單元格清除操作技能與技巧.........................................................................39

2.3.1　清除單元格內容.............................................................................................39

2.3.2　清除單元格批註.............................................................................................39

2.3.3　清除單元格的普通格式................................................................................39

2.3.4　清除單元格的自定義數字格式...................................................................40

2.3.5　清除單元格的條件格式................................................................................40

2.3.6　清除單元格的數據驗證................................................................................40

2.3.7　清除單元格的普通公式................................................................................41

2.3.8　清除單元格的數組公式................................................................................42

 

第3章.設置單元格格式技能與技巧..................................43

3.1　設置常規的固定格式....................................................................................44

3.1.1　合理設置邊框..................................................................................................44

3.1.2　合理設置對齊..................................................................................................45

3.1.3　合理設置數字格式.........................................................................................45

3.1.4　合理設置字體和字號.....................................................................................46

3.1.5　合理設置行高和列寬.....................................................................................46

3.1.6　合理設置合計數格式.....................................................................................47

3.2　套用表格格式和單元格樣式.........................................................................47

3.2.1　套用表格格式..................................................................................................48

3.2.2　套用單元格樣式.............................................................................................50

3.3　設置自定義數字格式....................................................................................51

3.3.1　瞭解自定義數字格式的代碼結構...............................................................52

3.3.2　將差異數分別設置為不同格式...................................................................52

3.3.3　根據數字大小分別設置不同格式...............................................................53

3.3.4　縮小位數顯示數字.........................................................................................54

3.3.5　日期格式的特殊設置.....................................................................................55

3.3.6　時間格式的特殊設置.....................................................................................56

3.4　設置條件格式...............................................................................................57

3.4.1　標識大於/小於指定值的單元格...............................................................58

3.4.2　標識含有特殊字符的單元格........................................................................58

3.4.3　標識重復數據單元格.....................................................................................59

3.4.4　標識最前/最後數據單元格........................................................................60

3.4.5　使用數據條定性標識數據大小...................................................................60

3.4.6　使用色階標識數據分佈................................................................................61

3.4.7　使用圖標集標識數據.....................................................................................63

3.4.8　創建新建規則的條件格式............................................................................64

3.4.9　條件格式的典型應用：建立提醒模型.......................................................66

3.4.10　編輯、刪除條件格式....................................................................................69

3.4.11　使用公式來設置條件格式的重要註意事項...........................................70

3.4.12　條件格式綜合應用案例..............................................................................70

 

第4章.數據排序實用技能與技巧.......................................73

4.1　數據排序規則及註意事項............................................................................74

4.1.1　基本數據排序規則.........................................................................................74

4.1.2　月份名稱的排序問題.....................................................................................75

4.1.3　編碼類數據的排序問題................................................................................75

4.1.4　對某列排序的問題.........................................................................................76

4.1.5　純文本和文本型數字混合排序的問題......................................................77

4.1.6　有合並單元格的排序問題............................................................................78

4.1.7　有計算公式的排序問題................................................................................78

4.2　數據排序的基本方法....................................................................................79

4.2.1　使用命令按鈕快速排序................................................................................79

4.2.2　使用對話框進行排序.....................................................................................80

4.3　不同類型標題的數據排序............................................................................83

4.3.1　單行標題的數據排序.....................................................................................83

4.3.2　多行標題（有合並單元格）的數據排序..................................................83

4.4　多條件排序..................................................................................................84

4.4.1　多條件排序的基本方法................................................................................85

4.4.2　數值與格式組合的多條件排序...................................................................86

4.5　自定義序列排序...........................................................................................88

4.5.1　創建自定義序列.............................................................................................88

4.5.2　單獨使用自定義序列進行數據排序...........................................................90

4.5.3　常規排序與自定義序列排序組合使用......................................................92

4.6　按照關鍵詞匹配排序....................................................................................95

4.6.1　不區分關鍵詞出場次序的關鍵詞匹配排序..............................................95

4.6.2　區分關鍵詞出場次序的關鍵詞匹配排序..................................................96

4.6.3　對所有關鍵詞匹配排序................................................................................97

4.7　編輯、刪除排序條件....................................................................................98

4.7.1　編輯已有的排序條件.....................................................................................98

4.7.2　刪除排序條件..................................................................................................99

 

第5章.數據篩選實用技能與技巧.....................................100

5.1　自動篩選及註意事項..................................................................................101

5.1.1　建立自動篩選...............................................................................................101

5.1.2　多行標題的自動篩選..................................................................................102

5.1.3　數據區域有空行時的自動篩選................................................................102

5.1.4　為什麽數據區域右側沒數據的列也出現了篩選標記.........................103

5.1.5　當工作表有大量數據和計算公式時篩選數據......................................104

5.1.6　取消自動篩選...............................................................................................104

5.2　三類數據的篩選操作..................................................................................104

5.2.1　日期數據篩選...............................................................................................104

5.2.2　文本數據篩選...............................................................................................106

5.2.3　數字篩選.......................................................................................................107

5.3　按顏色篩選................................................................................................108

5.3.1　按單元格顏色篩選......................................................................................108

5.3.2　按字體顏色篩選..........................................................................................109

5.3.3　單元格顏色和字體顏色都存在情況下的篩選......................................109

5.4　自定義篩選................................................................................................110

5.4.1　自定義篩選的運用......................................................................................110

5.4.2　在同一列中做多個項目篩選.....................................................................111

5.4.3　在不同列中做多次篩選.............................................................................111

5.4.4　清除某個字段的篩選..................................................................................112

5.5　高級篩選....................................................................................................112

5.5.1　高級篩選：設計篩選條件區域.................................................................112

5.5.2　高級篩選：基本操作方法..........................................................................114

5.5.3　高級篩選：將篩選結果顯示到其他位置................................................116

5.5.4　清除高級篩選...............................................................................................116

5.6　篩選的其他操作.........................................................................................117

5.6.1　復制篩選結果...............................................................................................117

5.6.2　清除所有篩選...............................................................................................117

5.6.3　編輯篩選出來的數據和公式.....................................................................118

 

第6章.數據拆分列實用技能與技巧................................120

6.1　使用Excel快速填充工具拆分列................................................................121

6.1.1　明顯規律情況下的快速填充.....................................................................121

6.1.2　存在特殊數據情況下的快速填充............................................................122

6.2　使用Excel分列工具拆分列.......................................................................123

6.2.1　根據單個分隔符進行分列.........................................................................124

6.2.2　根據多個分隔符進行分列.........................................................................126

6.2.3　根據固定寬度進行分列.............................................................................128

6.3　使用Excel函數拆分列...............................................................................130

6.3.1　使用文本函數進行分列的基本方法........................................................130

6.3.2　拆分全角字符和半角字符.........................................................................131

6.4　使用Power Query工具拆分列.................................................................133

6.4.1　將一列拆分成多列保存（拆分列工具）.................................................133

6.4.2　將一列拆分成多列保存（自定義列工具）.............................................137

6.4.3　將一列拆分成多行保存.............................................................................139

 

第7章.分類匯總與分級顯示實用技能與技巧...............143

7.1　創建分類匯總.............................................................................................144

7.1.1　創建單級分類匯總......................................................................................144

7.1.2　創建多級分類匯總......................................................................................146

7.1.3　刪除分類匯總...............................................................................................147

7.2　創建分級顯示.............................................................................................148

7.2.1　自動建立分級顯示......................................................................................148

7.2.2　手動建立分級顯示......................................................................................149

7.3　分類匯總和分級顯示的其他操作...............................................................151

7.3.1　復制分類匯總和分級顯示數據................................................................151

7.3.2　取消分類匯總和分級顯示.........................................................................152

 

第8章.合並計算工具實用技能與技巧............................153

8.1　合並計算工具的基本使用方法...................................................................154

8.1.1　僅獲取合計數的合並計算.........................................................................154

8.1.2　與每個表格鏈接的合並計算.....................................................................155

8.2　復雜情況下的合並計算..............................................................................159

8.2.1　復雜結構表格合並......................................................................................159

8.2.2　跨工作簿的合並計算..................................................................................160

 

第9章.表格數據整理加工技能與技巧............................163

9.1　數據填充....................................................................................................164

9.1.1　將空單元格填充零......................................................................................164

9.1.2　將空單元格填充為上一行數據................................................................166

9.1.3　將空單元格填充為下一行數據................................................................167

9.1.4　將空單元格填充為左一列數據................................................................168

9.1.5　將空單元格填充為右一列數據................................................................169

9.1.6　將某行數據往下復制填充N行...............................................................170

9.1.7　將某列數據往右復制填充N列...............................................................170

9.2　數據去重....................................................................................................170

9.2.1　從某列中獲取不重復數據列表................................................................171

9.2.2　刪除表單中的重復數據.............................................................................171

9.3　數字格式轉換.............................................................................................172

9.3.1　將文本型數字轉換為數值型數字............................................................172

9.3.2　將數值型數字轉換為文本型數字............................................................173

9.4　特殊字符處理.............................................................................................175

9.4.1　清除字符前後的空格..................................................................................175

9.4.2　清除字符中的星號......................................................................................175

9.4.3　清除字符中的特殊字符.............................................................................176

 

第10章.數據驗證實用技能與技巧...................................178

10.1　數據驗證的設置方法與註意事項.............................................................179

10.1.1　數據驗證的設置方法...............................................................................179

10.1.2　復制粘貼會破壞設置的數據驗證..........................................................181

10.1.3　清除數據驗證............................................................................................181

10.2　設置數據驗證來控制輸入日期.................................................................182

10.2.1　只允許輸入指定時間段的日期..............................................................182

10.2.2　不允許輸入指定時間段的日期..............................................................182

10.2.3　只允許輸入指定日期之前或之後的日期.............................................183

10.2.4　只允許輸入當天日期...............................................................................184

10.3　設置數據驗證來控制輸入數字.................................................................184

10.3.1　只允許輸入整數........................................................................................185

10.3.2　只允許輸入小數........................................................................................185

10.4　設置數據驗證來控制輸入文本.................................................................186

10.4.1　只能輸入固定長度的文本.......................................................................186

10.4.2　只能輸入中文名稱，並且不允許有空格.............................................187

10.4.3　只能輸入英文名稱....................................................................................188

10.5　使用序列規則在單元格設置下拉列表......................................................188

10.5.1　序列項目固定情況下的下拉列表..........................................................189

10.5.2　序列項目不固定情況下的下拉列表.....................................................190

10.6　設置自定義規則的數據驗證....................................................................190

10.6.1　只能輸入18位不重復身份證號碼.......................................................191

10.6.2　只能輸入位數不定的文本型數字編碼.................................................192

 

第11章.Excel常用函數公式與實際應用........................194

11.1　公式基本知識和操作技能與技巧.............................................................195

11.1.1　公式運算規則及運算符...........................................................................195

11.1.2　單元格引用.................................................................................................195

11.1.3　在公式中輸入固定值...............................................................................196

11.1.4　復制公式的基本方法...............................................................................197

11.1.5　復制公式的快捷方法...............................................................................197

11.1.6　移動公式的基本方法...............................................................................198

11.1.7　移動公式的快捷方法...............................................................................198

11.1.8　復制公式本身字符串或公式的一部分字符串....................................198

11.1.9　用F9鍵查看公式的計算結果................................................................198

11.1.10　將公式轉換為值......................................................................................199

11.1.11　顯示公式計算結果和顯示公式表達式...............................................199

11.1.12　將公式分行輸入，以便使公式更加容易理解和查看.....................199

11.1.13　在公式表達式中插入空格，編輯起來更加方便.............................200

11.1.14　保護公式不被修改或查看....................................................................200

11.1.15　公式和函數中的字母不區分大小寫...................................................202

11.1.16　公式和函數中的標點符號都必須是半角字符..................................202

11.2　函數基本知識和操作技能與技巧.............................................................203

11.2.1　函數的基本語法........................................................................................203

11.2.2　函數參數的類型........................................................................................204

11.2.3　養成輸入函數的好習慣...........................................................................205

11.2.4　在單元格快速輸入函數的技巧..............................................................205

11.3　常用邏輯判斷函數及其應用....................................................................206

11.3.1　IF函數基本應用........................................................................................206

11.3.2　IF函數嵌套應用........................................................................................207

11.3.3　IFS函數基本應用.....................................................................................210

11.3.4　IFERROR函數基本應用...........................................................................210

11.4　常用分類匯總函數及其應用....................................................................211

11.4.1　COUNTA函數統計不為空的單元格個數............................................211

11.4.2　COUNTIF函數統計滿足一個條件的單元格個數..............................212

11.4.3　COUNTIFS函數統計滿足多個條件的單元格個數............................213

11.4.4　SUMIF函數對滿足一個條件的單元格求和........................................213

11.4.5　SUMIFS函數對滿足多個條件的單元格求和......................................215

11.5　常用查找引用函數及其應用....................................................................216

11.5.1　VLOOKUP函數從列結構表中查找數據..............................................216

11.5.2　HLOOKUP函數從行結構表中查找數據..............................................220

11.5.3　XLOOKUP函數靈活查找數據................................................................221

11.5.4　MATCH函數從一列或一行中定位........................................................223

11.5.5　INDEX函數根據行坐標和列坐標查找數據.........................................224

11.6　常用日期處理函數及其應用....................................................................226

11.6.1　日期數據特徵與註意事項.......................................................................226

11.6.2　TODAY函數獲取當天日期......................................................................228

11.6.3　EDATE函數計算到期日..........................................................................229

11.6.4　EOMONTH函數計算月底日期..............................................................230

11.6.5　DATEDIF函數計算兩個日期的期限.....................................................231

11.6.6　WEEKDAY函數判斷星期幾....................................................................232

11.6.7　DATE函數將年月日三個數字組合為日期..........................................233

11.7　常用文本處理函數及其應用....................................................................235

11.7.1　LEN函數獲取文本長度...........................................................................235

11.7.2　LEFT函數從文本字符串左側截取字符...............................................235

11.7.3　RIGHT函數從文本字符串右側截取字符.............................................236

11.7.4　MID函數從文本字符串指定位置截取字符.........................................237

11.7.5　SUBSTITUTE函數替換指定字符..........................................................237

11.7.6　CONCAT函數連接字符串.......................................................................238

11.7.7　TEXTJOIN函數連接字符串.....................................................................240

11.7.8　TEXT函數將數值轉換為指定格式的文本字符串..............................240

 

第12章.Excel數據透視分析技能與技巧........................243

12.1　製作數據透視表的基本方法....................................................................244

12.1.1　以一個一維表格製作數據透視表..........................................................244

12.1.2　以多個一維表格製作數據透視表..........................................................246

12.1.3　以一個二維表格製作數據透視表..........................................................250

12.1.4　以多個二維表格製作數據透視表..........................................................252

12.2　數據透視表的格式化和美化....................................................................256

12.2.1　設置報表佈局............................................................................................257

12.2.2　重復項目標簽（填充項目空單元格）...................................................258

12.2.3　顯示/不顯示分類匯總...........................................................................259

12.2.4　顯示/不顯示行總計和列總計..............................................................259

12.2.5　合並居中標簽單元格...............................................................................260

12.2.6　設置報表樣式............................................................................................261

12.2.7　修改字段名稱............................................................................................261

12.2.8　調整字段項目的次序...............................................................................262

12.2.9　設置數字格式............................................................................................262

12.3　利用數據透視表分析數據........................................................................263

12.3.1　排名分析.....................................................................................................263

12.3.2　篩選排名前（後）N的商品或客戶......................................................264

12.3.3　設置值字段匯總依據...............................................................................266

12.3.4　設置值字段顯示方式...............................................................................268

12.3.5　對字段的項目進行分組：組合日期......................................................269

12.3.6　對字段的項目進行分組：組合數字......................................................270

12.3.7　對字段的項目進行分組：組合文本......................................................271

12.3.8　插入計算字段，生成新的分析指標.....................................................273

12.3.9　插入計算項，生成新的分析項目..........................................................275

12.3.10　插入切片器，快速靈活篩選數據.......................................................277

12.3.11　快速製作某個項目的明細表................................................................280

12.3.12　快速製作所有項目的明細表................................................................280

12.4　使用數據透視圖可視化數據透視表.........................................................282

12.4.1　創建數據透視圖的基本方法..................................................................282

12.4.2　不顯示數據透視圖上的字段按鈕..........................................................283

12.4.3　數據透視圖的基本格式化.......................................................................284