Excel函數公式綜合應用實踐案例視頻精講

目錄

第1章.創建高效Excel函數公式必備技能..............01

1.1　公式的本質，是如何進行計算的邏輯思維..................................................02

1.1.1　仔細閱讀表格，尋找解決思路....................................................................02

1.1.2　梳理邏輯思路，繪制邏輯流程圖................................................................04

1.2　必備重要技能，高效輸入嵌套函數公式......................................................06

1.2.1.同一個函數嵌套：函數參數對話框+名稱框方法..................................07

1.2.2　不同函數嵌套：函數參數對話框+名稱框方法.......................................08

1.2.3　復雜的嵌套公式：分解綜合法.....................................................................10

1.3　使用名稱創建高效、靈活的數據分析公式..................................................13

1.3.1　使用名稱簡化公式..........................................................................................13

1.3.2　名稱應用案例：使用名稱製作動態圖表....................................................16

1.4　使用數組公式解決復雜計算問題.................................................................17

1.4.1　數組基本知識..................................................................................................18

1.4.2.數組公式基本知識..........................................................................................19

1.4.3　數組公式應用案例：計算前N大數據之和...............................................19

第2章.數據邏輯判斷處理案例精講........................23

2.1　條件表達式應用技能與案例.........................................................................24

2.1.1　使用條件表達式的註意事項........................................................................24

2.1.2.條件表達式的書寫規則.................................................................................24

2.1.3　條件表達式應用案例.....................................................................................25

2.2　信息函數及其應用案例................................................................................27

2.2.1　判斷單元格狀態..............................................................................................27

2.2.2　判斷數據類型..................................................................................................28

2.2.3　判斷是否為錯誤值..........................................................................................30

2.2.4　判斷數字的奇偶..............................................................................................31

2.2.5　獲取工作表信息..............................................................................................33

2.2.6　獲取工作簿信息..............................................................................................33

2.2.7　構建自動化的累計數計算公式....................................................................34

2.3　IF函數基本邏輯與應用................................................................................35

2.3.1　IF函數基本原理與邏輯.................................................................................36

2.3.2　IF函數基本應用案例.....................................................................................36

2.4　IF函數嵌套應用...........................................................................................38

2.4.1　繪制邏輯思路圖..............................................................................................38

2.4.2　IF函數嵌套應用：單流程..............................................................................39

2.4.3　IF函數嵌套應用：多流程..............................................................................40

2.5　多條件組合判斷...........................................................................................42

2.5.1　AND函數應用：多個與條件組合................................................................42

2.5.2　OR函數應用：多個或條件組合...................................................................43

2.5.3　AND函數和OR函數聯合應用....................................................................44

2.6　IF函數高級應用...........................................................................................46

2.6.1　使用條件數組進行判斷.................................................................................46

2.6.2　使用常量數組進行判斷：VLOOKUP函數反向查找................................47

2.6.3　使用常量數組進行判斷：多個或條件求和................................................48

2.7　其他邏輯判斷函數.......................................................................................49

2.7.1　IFS函數及其應用............................................................................................50

2.7.2　IFERROR函數應用：處理錯誤值.................................................................51

第3章.文本數據處理案例精講..............................53

3.1　清理數據中的垃圾.......................................................................................54

3.1.1　TRIM函數：清理文本中的空格...................................................................54

3.1.2　CLEAN函數：清除非打印字符....................................................................54

3.2　統計字符的長度...........................................................................................56

3.2.1　LEN函數和LENB函數的基本應用............................................................56

3.2.2　綜合應用案例：拆分列..................................................................................57

3.2.3　綜合應用案例：設置數據驗證，只能輸入漢字名稱..............................58

3.2.4　綜合應用案例：設置數據驗證，只能輸入固定位數的不重復數據....59

3.3　截取字符......................................................................................................60

3.3.1　LEFT函數：從左側截取字符........................................................................60

3.3.2　RIGHT函數：從右側截取字符.....................................................................61

3.3.3　MID函數：從指定位置截取字符.................................................................62

3.3.4　截取字符綜合應用案例：從身份證號碼提取信息...................................62

3.4　連接字符串..................................................................................................63

3.4.1　用連接運算符&連接字符串........................................................................64

3.4.2　使用CONCAT函數或CONCATENATE函數連接字符串.......................64

3.4.3　使用TEXTJOIN函數連接字符串.................................................................64

3.4.4　綜合應用案例：設計摘要說明.....................................................................65

3.5　替換字符......................................................................................................67

3.5.1　SUBSTITUTE函數及其應用.........................................................................67

3.5.2　REPLACE函數及其應用................................................................................68

3.5.3　綜合應用案例：快速統計匯總.....................................................................68

3.6　查找字符......................................................................................................69

3.6.1　FIND函數及其應用........................................................................................69

3.6.2　SEARCH函數及其應用..................................................................................70

3.6.3　綜合應用案例：復雜的數據分列.................................................................71

3.7　轉換字符......................................................................................................72

3.7.1　為什麽要使用TEXT函數轉換字符.............................................................72

3.7.2　TEXT函數及其應用........................................................................................72

3.7.3　綜合應用案例：直接使用原始數據進行匯總計算...................................75

3.8　其他實用的文本函數及引用.........................................................................76

3.8.1　CHAR函數及其應用.......................................................................................76

3.8.2.綜合應用案例：在圖表中顯示更多信息....................................................77

第4章.日期和時間數據處理案例精講....................80

4.1　處理文本格式的日期....................................................................................81

4.1.1　使用SUBSTITUTE函數處理非法日期......................................................81

4.1.2　使用TEXT函數處理非法日期.....................................................................81

4.1.3　使用DATEVALUE函數處理文本型日期....................................................81

4.2　日期組合與還原...........................................................................................82

4.2.1　使用DATE函數組合年月日數字.................................................................82

4.2.2　使用DATE函數或DATEVALUE函數和其他函數生成具體日期..........83

4.3　從日期中提取重要信息................................................................................84

4.3.1　從日期中提取年月日數字.............................................................................84

4.3.2　從日期中提取年份、月份和日名稱...........................................................85

4.3.3　從日期中提取季度名稱.................................................................................87

4.3.4　從日期中提取星期幾及星期名稱................................................................87

4.3.5　從日期中提取周次名稱.................................................................................91

4.4　獲取當前日期和時間....................................................................................93

4.4.1　TODAY函數及其應用....................................................................................93

4.4.2　NOW函數及其應用........................................................................................94

4.4.3　NOW函數與TODAY函數的區別................................................................95

4.5　計算將來日期和過去日期............................................................................96

4.5.1　EDATE函數：計算將來或過去的具體日期...............................................96

4.5.2　EOMONTH函數：計算將來或過去的月底日期.......................................97

4.5.3　綜合應用案例：計算合同到期日.................................................................97

4.5.4　綜合應用案例：計算付款截止日.................................................................99

4.6　計算兩個日期之間的期限..........................................................................100

4.6.1.DATEDIF函數及其應用..............................................................................100

4.6.2　YEARFRAC函數及其應用...........................................................................102

4.6.3　綜合應用案例：直接用身份證號碼計算年齡........................................103

4.7　計算工作日................................................................................................103

4.7.1　計算一段時間後的工作日是哪天.............................................................104

4.7.2　計算兩個日期之間的工作日天數.............................................................105

4.8　計算時間....................................................................................................106

4.8.1　時間計算規則及註意事項..........................................................................106

4.8.2　時間計算經典案例：指紋打卡數據處理.................................................107

4.8.3　時間計算經典案例：機器工時計算..........................................................108

第5章.數據統計與匯總案例精講.........................110

5.1　數據計數統計分析.....................................................................................111

5.1.1　COUNTA函數：統計不為空的單元格個數............................................111

5.1.2　COUNTIF函數：統計滿足一個指定條件的單元格個數......................112

5.1.3　COUNTIFS函數：統計滿足多個指定條件的單元格個數...................113

5.1.4　綜合應用案例：統計數據出現次數..........................................................114

5.1.5　綜合應用案例：員工屬性分析報告..........................................................117

5.1.6　綜合應用案例：刪除兩個表都存在的數據.............................................120

5.1.7　構建條件值數組進行或條件的計數........................................................121

5.1.8　使用COUNTIF函數和COUNTIFS函數的註意事項............................123

5.2　數據求和統計分析.....................................................................................124

5.2.1　SUMIF函數基本原理與基本應用.............................................................124

5.2.2.SUMIF函數經典案例：對小計行求和.....................................................125

5.2.3　SUMIF函數經典案例：隔列求和..............................................................126

5.2.4　SUMIF函數經典案例：關鍵詞匹配求和.................................................127

5.2.5　使用SUMIF函數實現多個或條件值的求和：精確值匹配條件.........127

5.2.6　使用SUMIF函數實現多個或條件值的求和：關鍵詞匹配條件.........128

5.2.7　使用SUMIF函數計算指定季度的合計數..............................................129

5.2.8　使用SUMIF函數做單條件數據查找.......................................................130

5.2.9　SUMIFS函數基本原理與基本應用..........................................................131

5.2.10　SUMIFS函數經典案例：比較值條件求和.............................................132

5.2.11　SUMIFS函數經典案例：關鍵詞匹配條件求和....................................133

5.2.12　使用SUMIFS函數實現多個或條件值的求和：精確值匹配條件.....134

5.2.13　使用SUMIFS函數實現多個或條件值的求和：關鍵詞匹配條件.....136

5.2.14　使用SUMIFS函數做多條件數據查找...................................................137

5.2.15　使用SUMIF函數和SUMIFS函數做關鍵詞匹配條件的數據查找...138

5.2.16　使用SUMIF函數和SUMIFS函數的註意事項.....................................139

5.3　數組求和函數SUMPRODUCT及其應用...................................................139

5.3.1　SUMPRODUCT函數基本原理與基本應用.............................................140

5.3.2　SUMPRODUCT函數統計公式值不為空的單元格個數.......................142

5.3.3　SUMPRODUCT函數用於單條件計數.....................................................143

5.3.4　SUMPRODUCT函數用於多條件計數.....................................................144

5.3.5　SUMPRODUCT函數用於多條件計數：關鍵詞匹配............................145

5.3.6　SUMPRODUCT函數用於單條件求和.....................................................146

5.3.7　SUMPRODUCT函數用於多條件求和.....................................................146

5.3.8　SUMPRODUCT函數用於條件求和：關鍵詞匹配.................................147

5.3.9　SUMPRODUCT函數用於復雜條件的數據查找....................................148

5.3.10　SUMPRODUCT函數綜合應用案例........................................................149

5.4　數據最大值、最小值和平均值計算...........................................................152

5.4.1　計算數據最大值（無條件）........................................................................152

5.4.2　計算數據最大值（指定條件）...................................................................153

5.4.3　計算數據最小值（無條件）........................................................................154

5.4.4　計算數據最小值（指定條件）...................................................................155

5.4.5　計算數據平均值（無條件）........................................................................156

5.4.6　計算數據平均值（指定條件）...................................................................157

5.4.7　指定幾個或條件下的最大值、最小值和平均值...................................159

5.4.8　計算數據中位數...........................................................................................160

5.4.9　計算數據四分位值.......................................................................................161

5.5　數字編碼超過15位情況下的計算問題.....................................................163

5.5.1　一個案例揭示的問題..................................................................................163

5.5.2　解決方法........................................................................................................163

第6章.數據查找與引用案例精講.........................165

6.1　VLOOKUP函數及其案例...........................................................................166

6.1.1　基本原理與使用方法..................................................................................166

6.1.2　使用關鍵詞條件查找數據..........................................................................168

6.1.3　從不確定的列中查找數據：使用MATCH函數.....................................169

6.1.4　利用VLOOKUP函數模糊查找..................................................................170

6.1.5　利用VLOOKUP函數做反向查找..............................................................172

6.1.6　利用VLOOKUP函數一次查找並返回多個值.........................................173

6.1.7　利用VLOOKUP函數製作動態分析圖表.................................................174

6.1.8　VLOOKUP函數的註意事項........................................................................175

6.2　LOOKUP函數及其案例.............................................................................176

6.2.1　基本原理與應用方法..................................................................................177

6.2.2　綜合應用案例：獲取最後一個不為空的單元格數據...........................178

6.2.3　綜合應用案例：獲取滿足多個條件下最後一個不為空的數據..........179

6.2.4　綜合應用案例：替代嵌套IF函數和VLOOKUP函數做模糊判斷.....180

6.3　XLOOKUP函數及其案例...........................................................................182

6.3.1　基本原理與使用方法..................................................................................182

6.3.2　單列匹配條件，返回一個結果.................................................................183

6.3.3　單行匹配條件，返回一個結果.................................................................184

6.3.4　單列匹配條件，返回多個結果.................................................................186

6.3.5　單行匹配條件，返回多個結果.................................................................186

6.3.6　關鍵詞條件匹配查找..................................................................................187

6.3.7　模糊條件匹配查找.......................................................................................188

6.3.8　自動處理錯誤值............................................................................................189

6.4　MATCH函數及其案例................................................................................191

6.4.1　MATCH函數：關鍵詞匹配定位................................................................191

6.4.2　MATCH函數：模糊匹配定位.....................................................................191

6.4.3　MATCH函數與其他函數聯合使用...........................................................192

6.5　INDEX函數及其案例.................................................................................194

6.5.1　基本原理與使用方法..................................................................................194

6.5.2　綜合應用案例：單列或單行查找數據.....................................................195

6.5.3　綜合應用案例：多行多列區域查找數據（簡單情況）.........................196

6.5.4　綜合應用案例：多行多列區域查找數據（復雜情況）.........................196

6.5.5　綜合應用案例：製作動態圖表（表單控件）..........................................199

6.6　OFFSET函數及其案例...............................................................................201

6.6.1　基本原理與使用方法..................................................................................201

6.6.2　基本應用案例：引用某列動態區域，製作隨數據增減自動調整的 圖表.................................................................................................................203

6.6.3　基本應用案例：引用某行的動態區域，計算累計數...........................206

6.6.4　綜合應用案例：分析指定起止月份之間的預算執行情況...................207

6.6.5　小技巧：如何判斷OFFSET函數結果是否正確.....................................208

6.7　INDIRECT函數及其案例............................................................................209

6.7.1　基本原理與使用方法..................................................................................209

6.7.2　綜合應用案例：快速從各個工作表查詢匯總數據................................211

6.7.3　綜合應用案例：各月費用跟蹤分析..........................................................212

6.7.4　INDIRECT函數應用的註意事項...............................................................216

6.7.5　小技巧：如何判斷INDIRECT函數結果是否正確.................................217

第7章.數據排名分析案例精講............................218

7.1　LARGE函數和SMALL函數及其案例........................................................219

7.1.1　基本原理與使用方法..................................................................................219

7.1.2　數據基本排序及名稱匹配..........................................................................220

7.1.3　相同數據排序及名稱匹配問題.................................................................221

7.1.4　綜合應用案例：建立自動化排名分析模板.............................................223

7.1.5　LARGE函數和SMALL函數的註意事項..................................................227

7.2　SORT函數及其案例..................................................................................228

7.2.1　基本原理與使用方法..................................................................................228

7.2.2　使用數組進行多列多方式排序.................................................................229

7.2.3　綜合應用案例：建立自動化排名分析模型（簡單情況）.....................230

7.2.4　綜合應用案例：建立自動化排名分析模型（復雜情況）.....................231

7.3　SORTBY函數及其案例..............................................................................233

7.3.1　基本原理與使用方法..................................................................................233

7.3.2　任意指定條件下的動態排序（按列排序）..............................................234

7.3.3　任意指定條件下的動態排序（按行排序）..............................................236

7.3.4　綜合應用案例：按照自定義序列排序.....................................................237

7.4　排位分析及其案例.....................................................................................239

7.4.1　基本原理與使用方法..................................................................................239

7.4.2　綜合應用案例：業務員排名及額外獎金計算........................................241

第8章.數據篩選分析案例精講............................242

8.1　FILTER函數及其案例.................................................................................243

8.1.1　基本原理與使用方法..................................................................................243

8.1.2　單條件篩選....................................................................................................245

8.1.3　多個與條件篩選...........................................................................................246

8.1.4　多個或條件篩選...........................................................................................247

8.1.5　多個與條件和或條件組合下的篩選........................................................248

8.1.6　計算條件下的篩選.......................................................................................249

8.1.7　關鍵詞匹配條件下的篩選..........................................................................251

8.2　FILTER函數綜合應用案例.........................................................................253

8.2.1　建立任意指定單列條件的動態篩選表....................................................253

8.2.2　建立多條件動態篩選表..............................................................................254

8.2.3　篩選與排序聯合使用：基本應用..............................................................256

8.2.4　篩選與排序聯合使用：綜合應用..............................................................258

8.2.5　建立多條件動態篩選與排序模型.............................................................260

8.2.6　剔除零值的動態分析..................................................................................261

8.2.7　對比篩選兩個表格.......................................................................................262

8.2.8　嵌套篩選數據...............................................................................................264

8.3　使用其他查找函數快速製作明細表...........................................................265

8.3.1　利用其他函數做滾動查找：基本原理和方法........................................265

8.3.2　利用其他函數做多條件滾動查找.............................................................268

第9章.數據其他處理案例精講............................270

9.1　數字舍入計算函數及應用..........................................................................271

9.1.1　使用ROUND函數將數字常規四捨五入................................................271

9.1.2　使用RUNDUP函數和ROUNDDOWN函數將數字向上/向下舍入...273

9.2　數據分組分析函數及應用..........................................................................275

9.2.1　使用FREQUENCY函數統計數據頻數分佈............................................275

9.2.2　使用COUNTIFS函數統計數據頻數分佈...............................................277

9.3　數據預測分析函數及應用..........................................................................278

9.3.1　建立一元線性預測模型..............................................................................278

9.3.2　建立多元線性預測模型..............................................................................280

9.3.3　建立指數預測模型.......................................................................................281

9.4　其他數據處理函數及應用..........................................................................283

9.4.1　繪制折線圖必需的NA函數......................................................................283

9.4.2　對公式空值做算式計算必需的N函數...................................................285

9.4.3　將全角字符轉換為半角字符必需的ASC函數......................................286

第10章.函數公式綜合應用案例精講....................287

10.1　滾動匯總每天各個門店的銷售日報.........................................................288

10.1.1　門店每天銷售額滾動匯總......................................................................288

10.1.2　門店每天銷售毛利滾動匯總..................................................................289

10.2　跟蹤分析各個門店的每天銷售數據.........................................................290

10.2.1　各個門店的銷售額跟蹤分析..................................................................290

10.2.2　各個門店的銷售毛利跟蹤分析.............................................................292

10.3　門店銷售排名分析...................................................................................293

10.3.1　各個門店的累計銷售額排名分析.........................................................293

10.3.2　各個門店的累計銷售毛利排名分析.....................................................294

10.3.3　各個門店的毛利率排名分析..................................................................295