Excel 函數公式綜合應用實踐案例視頻精講
於峰 韓小良
買這商品的人也買了...
相關主題
商品描述
"函數公式是 Excel 數據處理和數據分析的核心工具之一，而邏輯思路是函數公式的核心。本書共 10 章，詳細介紹在數據處理和數據分析中常用的 Excel 函數及其實際用法，以及這些函數的各種變形的靈活應用。通過這些案例，讀者學到的不僅是相應的知識和技能，更是使 用函數公式解決問題的基本邏輯原理和邏輯思維，以及解決問題的能力。《Excel 函數公式綜合應用實踐案例視頻精講》所有案例都配 有相應的學習素材和學習視頻，使用手機掃描對應內容的二維碼即可觀看。 《Excel 函數公式綜合應用實踐案例視頻精講》採用由淺入深的寫作手法，從邏輯思維到技能技巧均運用在實際案例中，適合各個水平的 Excel 用戶。既可作為初學者的入門指南，又可作為中、高級用戶的參考手冊，還可用於培訓機構的教學用書。 本書提供了相應的 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
