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

第1章創(chuàng)建高效Excel函數(shù)公式技能01
11　公式的本質(zhì)，是如何進行計算的邏輯思維02
111　仔細閱讀表格，尋找解決思路02
112　梳理邏輯思路，繪制邏輯流程圖04
12　重要技能，高效輸入嵌套函數(shù)公式06
121同一個函數(shù)嵌套：函數(shù)參數(shù)對話框 名稱框方法07
122　不同函數(shù)嵌套：函數(shù)參數(shù)對話框 名稱框方法08
123　復雜的嵌套公式：分解綜合法10
13　使用名稱創(chuàng)建高效、靈活的數(shù)據(jù)分析公式13
131　使用名稱簡化公式13
132　名稱應用案例：使用名稱制作動態(tài)圖表16
14　使用數(shù)組公式解決復雜計算問題17
141　數(shù)組基本知識18
142數(shù)組公式基本知識19
143　數(shù)組公式應用案例：計算前N大數(shù)據(jù)之和19
第2章數(shù)據(jù)邏輯判斷處理案例精講23
21　條件表達式應用技能與案例24
211　使用條件表達式的注意事項24
212條件表達式的書寫規(guī)則24
213　條件表達式應用案例25
22　信息函數(shù)及其應用案例27
221　判斷單元格狀態(tài)27
222　判斷數(shù)據(jù)類型28
223　判斷是否為錯誤值30
224　判斷數(shù)字的奇偶31
225　獲取工作表信息33
226　獲取工作簿信息33
227　構(gòu)建自動化的累計數(shù)計算公式34
23　IF函數(shù)基本邏輯與應用35
231　IF函數(shù)基本原理與邏輯36
232　IF函數(shù)基本應用案例36
24　IF函數(shù)嵌套應用38
241　繪制邏輯思路圖38
242　IF函數(shù)嵌套應用：單流程39
243　IF函數(shù)嵌套應用：多流程40
25　多條件組合判斷42
251　AND函數(shù)應用：多個與條件組合42
252　OR函數(shù)應用：多個或條件組合43
253　AND函數(shù)和OR函數(shù)聯(lián)合應用44
26　IF函數(shù)高級應用46
261　使用條件數(shù)組進行判斷46
262　使用常量數(shù)組進行判斷：VLOOKUP函數(shù)反向查找47
263　使用常量數(shù)組進行判斷：多個或條件求和48
27　其他邏輯判斷函數(shù)49
271　IFS函數(shù)及其應用50
272　IFERROR函數(shù)應用：處理錯誤值51
第3章文本數(shù)據(jù)處理案例精講53
31　清理數(shù)據(jù)中的垃圾54
311　TRIM函數(shù)：清理文本中的空格54
312　CLEAN函數(shù)：清除非打印字符54
32　統(tǒng)計字符的長度56
321　LEN函數(shù)和LENB函數(shù)的基本應用56
322　綜合應用案例：拆分列57
323　綜合應用案例：設(shè)置數(shù)據(jù)驗證，只能輸入漢字名稱58
324　綜合應用案例：設(shè)置數(shù)據(jù)驗證，只能輸入固定位數(shù)的不重復數(shù)據(jù)59
33　截取字符60
331　LEFT函數(shù)：從左側(cè)截取字符60
332　RIGHT函數(shù)：從右側(cè)截取字符61
333　MID函數(shù)：從指定位置截取字符62
334　截取字符綜合應用案例：從身份證號碼提取信息62
34　連接字符串63
341　用連接運算符&連接字符串64
342　使用CONCAT函數(shù)或CONCATENATE函數(shù)連接字符串64
343　使用TEXTJOIN函數(shù)連接字符串64
344　綜合應用案例：設(shè)計摘要說明65
35　替換字符67
351　SUBSTITUTE函數(shù)及其應用67
352　REPLACE函數(shù)及其應用68
353　綜合應用案例：快速統(tǒng)計匯總68
36　查找字符69
361　FIND函數(shù)及其應用69
362　SEARCH函數(shù)及其應用70
363　綜合應用案例：復雜的數(shù)據(jù)分列71
37　轉(zhuǎn)換字符72
371　為什么要使用TEXT函數(shù)轉(zhuǎn)換字符72
372　TEXT函數(shù)及其應用72
373　綜合應用案例：直接使用原始數(shù)據(jù)進行匯總計算75
38　其他實用的文本函數(shù)及引用76
381　CHAR函數(shù)及其應用76
382綜合應用案例：在圖表中顯示更多信息77
第4章日期和時間數(shù)據(jù)處理案例精講80
41　處理文本格式的日期81
411　使用SUBSTITUTE函數(shù)處理非法日期81
412　使用TEXT函數(shù)處理非法日期81
413　使用DATEVALUE函數(shù)處理文本型日期81
42　日期組合與還原82
421　使用DATE函數(shù)組合年月日數(shù)字82
422　使用DATE函數(shù)或DATEVALUE函數(shù)和其他函數(shù)生成具體日期83
43　從日期中提取重要信息84
431　從日期中提取年月日數(shù)字84
432　從日期中提取年份、月份和日名稱85
433　從日期中提取季度名稱87
434　從日期中提取星期幾及星期名稱87
435　從日期中提取周次名稱91
44　獲取當前日期和時間93
441　TODAY函數(shù)及其應用93
442　NOW函數(shù)及其應用94
443　NOW函數(shù)與TODAY函數(shù)的區(qū)別95
45　計算將來日期和過去日期96
451　EDATE函數(shù)：計算將來或過去的具體日期96
452　EOMONTH函數(shù)：計算將來或過去的月底日期97
453　綜合應用案例：計算合同到期日97
454　綜合應用案例：計算付款截止日99
46　計算兩個日期之間的期限100
461DATEDIF函數(shù)及其應用100
462　YEARFRAC函數(shù)及其應用102
463　綜合應用案例：直接用身份證號碼計算年齡103
47　計算工作日103
471　計算一段時間后的工作日是哪天104
472　計算兩個日期之間的工作日天數(shù)105
48　計算時間106
481　時間計算規(guī)則及注意事項106
482　時間計算經(jīng)典案例：指紋打卡數(shù)據(jù)處理107
483　時間計算經(jīng)典案例：機器工時計算108
第5章數(shù)據(jù)統(tǒng)計與匯總案例精講110
51　數(shù)據(jù)計數(shù)統(tǒng)計分析111
511　COUNTA函數(shù)：統(tǒng)計不為空的單元格個數(shù)111
512　COUNTIF函數(shù)：統(tǒng)計滿足一個指定條件的單元格個數(shù)112
513　COUNTIFS函數(shù)：統(tǒng)計滿足多個指定條件的單元格個數(shù)113
514　綜合應用案例：統(tǒng)計數(shù)據(jù)出現(xiàn)次數(shù)114
515　綜合應用案例：員工屬性分析報告117
516　綜合應用案例：刪除兩個表都存在的數(shù)據(jù)120
517　構(gòu)建條件值數(shù)組進行或條件的計數(shù)121
518　使用COUNTIF函數(shù)和COUNTIFS函數(shù)的注意事項123
52　數(shù)據(jù)求和統(tǒng)計分析124
521　SUMIF函數(shù)基本原理與基本應用124
522SUMIF函數(shù)經(jīng)典案例：對小計行求和125
523　SUMIF函數(shù)經(jīng)典案例：隔列求和126
524　SUMIF函數(shù)經(jīng)典案例：關(guān)鍵詞匹配求和127
525　使用SUMIF函數(shù)實現(xiàn)多個或條件值的求和：精確值匹配條件127
526　使用SUMIF函數(shù)實現(xiàn)多個或條件值的求和：關(guān)鍵詞匹配條件128
527　使用SUMIF函數(shù)計算指定季度的合計數(shù)129
528　使用SUMIF函數(shù)做單條件數(shù)據(jù)查找130
529　SUMIFS函數(shù)基本原理與基本應用131
5210　SUMIFS函數(shù)經(jīng)典案例：比較值條件求和132
5211　SUMIFS函數(shù)經(jīng)典案例：關(guān)鍵詞匹配條件求和133
5212　使用SUMIFS函數(shù)實現(xiàn)多個或條件值的求和：精確值匹配條件134
5213　使用SUMIFS函數(shù)實現(xiàn)多個或條件值的求和：關(guān)鍵詞匹配條件136
5214　使用SUMIFS函數(shù)做多條件數(shù)據(jù)查找137
5215　使用SUMIF函數(shù)和SUMIFS函數(shù)做關(guān)鍵詞匹配條件的數(shù)據(jù)查找138
5216　使用SUMIF函數(shù)和SUMIFS函數(shù)的注意事項139
53　數(shù)組求和函數(shù)SUMPRODUCT及其應用139
531　SUMPRODUCT函數(shù)基本原理與基本應用140
532　SUMPRODUCT函數(shù)統(tǒng)計公式值不為空的單元格個數(shù)142
533　SUMPRODUCT函數(shù)用于單條件計數(shù)143
534　SUMPRODUCT函數(shù)用于多條件計數(shù)144
535　SUMPRODUCT函數(shù)用于多條件計數(shù)：關(guān)鍵詞匹配145
536　SUMPRODUCT函數(shù)用于單條件求和146
537　SUMPRODUCT函數(shù)用于多條件求和146
538　SUMPRODUCT函數(shù)用于條件求和：關(guān)鍵詞匹配147
539　SUMPRODUCT函數(shù)用于復雜條件的數(shù)據(jù)查找148
5310　SUMPRODUCT函數(shù)綜合應用案例149
54　數(shù)據(jù)最大值、最小值和平均值計算152
541　計算數(shù)據(jù)最大值（無條件）152
542　計算數(shù)據(jù)最大值（指定條件）153
543　計算數(shù)據(jù)最小值（無條件）154
544　計算數(shù)據(jù)最小值（指定條件）155
545　計算數(shù)據(jù)平均值（無條件）156
546　計算數(shù)據(jù)平均值（指定條件）157
547　指定幾個或條件下的最大值、最小值和平均值159
548　計算數(shù)據(jù)中位數(shù)160
549　計算數(shù)據(jù)四分位值161
55　數(shù)字編碼超過15位情況下的計算問題163
551　一個案例揭示的問題163
552　解決方法163
第6章數(shù)據(jù)查找與引用案例精講165
61　VLOOKUP函數(shù)及其案例166
611　基本原理與使用方法166
612　使用關(guān)鍵詞條件查找數(shù)據(jù)168
613　從不確定的列中查找數(shù)據(jù)：使用MATCH函數(shù)169
614　利用VLOOKUP函數(shù)模糊查找170
615　利用VLOOKUP函數(shù)做反向查找172
616　利用VLOOKUP函數(shù)一次查找并返回多個值173
617　利用VLOOKUP函數(shù)制作動態(tài)分析圖表174
618　VLOOKUP函數(shù)的注意事項175
62　LOOKUP函數(shù)及其案例176
621　基本原理與應用方法177
622　綜合應用案例：獲取最后一個不為空的單元格數(shù)據(jù)178
623　綜合應用案例：獲取滿足多個條件下最后一個不為空的數(shù)據(jù)179
624　綜合應用案例：替代嵌套IF函數(shù)和VLOOKUP函數(shù)做模糊判斷180
63　XLOOKUP函數(shù)及其案例182
631　基本原理與使用方法182
632　單列匹配條件，返回一個結(jié)果183
633　單行匹配條件，返回一個結(jié)果184
634　單列匹配條件，返回多個結(jié)果186
635　單行匹配條件，返回多個結(jié)果186
636　關(guān)鍵詞條件匹配查找187
637　模糊條件匹配查找188
638　自動處理錯誤值189
64　MATCH函數(shù)及其案例191
641　MATCH函數(shù)：關(guān)鍵詞匹配定位191
642　MATCH函數(shù)：模糊匹配定位191
643　MATCH函數(shù)與其他函數(shù)聯(lián)合使用192
65　INDEX函數(shù)及其案例194
651　基本原理與使用方法194
652　綜合應用案例：單列或單行查找數(shù)據(jù)195
653　綜合應用案例：多行多列區(qū)域查找數(shù)據(jù)（簡單情況）196
654　綜合應用案例：多行多列區(qū)域查找數(shù)據(jù)（復雜情況）196
655　綜合應用案例：制作動態(tài)圖表（表單控件）199
66　OFFSET函數(shù)及其案例201
661　基本原理與使用方法201
662　基本應用案例：引用某列動態(tài)區(qū)域，制作隨數(shù)據(jù)增減自動調(diào)整的
圖表203
663　基本應用案例：引用某行的動態(tài)區(qū)域，計算累計數(shù)206
664　綜合應用案例：分析指定起止月份之間的預算執(zhí)行情況207
665　小技巧：如何判斷OFFSET函數(shù)結(jié)果是否正確208
67　INDIRECT函數(shù)及其案例209
671　基本原理與使用方法209
672　綜合應用案例：快速從各個工作表查詢匯總數(shù)據(jù)211
673　綜合應用案例：各月費用跟蹤分析212
674　INDIRECT函數(shù)應用的注意事項216
675　小技巧：如何判斷INDIRECT函數(shù)結(jié)果是否正確217
第7章數(shù)據(jù)排名分析案例精講218
71　LARGE函數(shù)和SMALL函數(shù)及其案例219
711　基本原理與使用方法219
712　數(shù)據(jù)基本排序及名稱匹配220
713　相同數(shù)據(jù)排序及名稱匹配問題221
714　綜合應用案例：建立自動化排名分析模板223
715　LARGE函數(shù)和SMALL函數(shù)的注意事項227
72　SORT函數(shù)及其案例228
721　基本原理與使用方法228
722　使用數(shù)組進行多列多方式排序229
723　綜合應用案例：建立自動化排名分析模型（簡單情況）230
724　綜合應用案例：建立自動化排名分析模型（復雜情況）231
73　SORTBY函數(shù)及其案例233
731　基本原理與使用方法233
732　任意指定條件下的動態(tài)排序（按列排序）234
733　任意指定條件下的動態(tài)排序（按行排序）236
734　綜合應用案例：按照自定義序列排序237
74　排位分析及其案例239
741　基本原理與使用方法239
742　綜合應用案例：業(yè)務(wù)員排名及額外獎金計算241
第8章數(shù)據(jù)篩選分析案例精講242
81　FILTER函數(shù)及其案例243
811　基本原理與使用方法243
812　單條件篩選245
813　多個與條件篩選246
814　多個或條件篩選247
815　多個與條件和或條件組合下的篩選248
816　計算條件下的篩選249
817　關(guān)鍵詞匹配條件下的篩選251
82　FILTER函數(shù)綜合應用案例253
821　建立任意指定單列條件的動態(tài)篩選表253
822　建立多條件動態(tài)篩選表254
823　篩選與排序聯(lián)合使用：基本應用256
824　篩選與排序聯(lián)合使用：綜合應用258
825　建立多條件動態(tài)篩選與排序模型260
826　剔除零值的動態(tài)分析261
827　對比篩選兩個表格262
828　嵌套篩選數(shù)據(jù)264
83　使用其他查找函數(shù)快速制作明細表265
831　利用其他函數(shù)做滾動查找：基本原理和方法265
832　利用其他函數(shù)做多條件滾動查找268
第9章數(shù)據(jù)其他處理案例精講270
91　數(shù)字舍入計算函數(shù)及應用271
911　使用ROUND函數(shù)將數(shù)字常規(guī)四舍五入271
912　使用RUNDUP函數(shù)和ROUNDDOWN函數(shù)將數(shù)字向上/向下舍入273
92　數(shù)據(jù)分組分析函數(shù)及應用275
921　使用FREQUENCY函數(shù)統(tǒng)計數(shù)據(jù)頻數(shù)分布275
922　使用COUNTIFS函數(shù)統(tǒng)計數(shù)據(jù)頻數(shù)分布277
93　數(shù)據(jù)預測分析函數(shù)及應用278
931　建立一元線性預測模型278
932　建立多元線性預測模型280
933　建立指數(shù)預測模型281
94　其他數(shù)據(jù)處理函數(shù)及應用283
941　繪制折線圖必需的NA函數(shù)283
942　對公式空值做算式計算必需的N函數(shù)285
943　將全角字符轉(zhuǎn)換為半角字符必需的ASC函數(shù)286
第10章函數(shù)公式綜合應用案例精講287
101　滾動匯總每天各個門店的銷售日報288
1011　門店每天銷售額滾動匯總288
1012　門店每天銷售毛利滾動匯總289
102　跟蹤分析各個門店的每天銷售數(shù)據(jù)290
1021　各個門店的銷售額跟蹤分析290
1022　各個門店的銷售毛利跟蹤分析292
103　門店銷售排名分析293
1031　各個門店的累計銷售額排名分析293
1032　各個門店的累計銷售毛利排名分析294
1033　各個門店的毛利率排名分析295