Excel數據處理和數據分析的核心工具之一，是函數公式。
說起函數公式，很多人覺得用起來太難了，嵌套太難了，單個函數會用，復雜點的表格就不會用了。
之所以覺得函數公式很難，是因為很多人只是機械套用，沒有去理解函數公式中內在的邏輯思維和解決問題的邏輯思路。
在使用函數創建計算公式之前，首先要從函數的邏輯原理去了解及認識函數。
任何一個函數，都有自己內在的邏輯，其背后的源代碼也是相應的邏輯計算。因此，要學好、用好函數，首先要從函數的邏輯原理上去認識函數、了解函數、運用函數。
例如，IF是什么？用一句通俗的白話來說就是：如果怎么怎么，那么就怎么怎么，否則就怎么怎么。如果你好好學習，就能快速進步，否則就蝸牛踏步。
說得簡單點，IF函數就是指定一個條件，判斷數據是否滿足這個條件，如果滿足了這個條件，就給一個結果A，如果不滿足這個條件，就給另一個結果B。
SUM函數是求和，是無條件求和，不管是什么數字，全部加總起來。但是，如何給它介紹一個對象IF呢？SUM IF=SUMIF，就構成了單條件求和，也就是，只有這個條件滿足了才去求和，否則一邊待著去。
如果給SUM多介紹幾個對象IFS呢？SUM IFS=SUMIFS，就構成了多條件求和，也就是必須同時滿足這幾個條件才求和，只要一個條件不滿足，就不去理會它。
但是，SUMIF函數也好，SUMIFS函數也好，能不能用數組判斷，用數組求和呢？肯定不行的啊，因為在函數的條件判斷參數的名稱與實際求和參數的名稱里，都有一個關鍵詞Range，那么Range是什么意思呢？是單元格區域的意思。例如，我們可以使用這樣的公式=SUMIF(A2:A6,"彩電",B2:B6)，但不能做出這樣的公式=SUMIF(LEFT(A2:A6,2),"彩電",B2:B6)，因為這個公式里，第1個參數不是引用的單元格區域，而是一個用LEFT函數得到的數組LEFT(A2:A6,2)。
如果給SUM介紹一個對象PRODUCT呢？什么是PRODUCT？就是乘積啊，就是幾個數組的乘積，因此，SUM PRODUCT=SUMPRODUCT，就是把幾組數的乘積加起來的意思。仔細看看這個函數的參數，不叫Range，而是叫Array，Array就是數組，數組可以是工作表上的實際區域，也可以是自己設計的數組，因此，SUMPRODUCT更加通用。
再比如，VLOOKUP函數的 V 代表什么？HLOOKUP函數的 H 代表什么？它們各自適用于什么樣的表格？這點不清楚，就會天下大亂了。還有，VLOOKUP函數也
好，HLOOKUP函數也好，LOOKUP函數也好，從名字上看，就是找的意思。那
么，根據什么條件找，從哪里找？怎么找？這不就是它們的基本邏輯嗎？再仔細看看它們的參數名稱，不是Range，而是Array。這就有點意思了，可以從實際數據區域里找，也可以構建數組，從數組里找，例如，我們可以使用LOOKUP函數查找某列最后一行的數據，查找最右邊一列的數據，這是很簡單的，你只要構建一個條件Array就可以了。
例如，直接使用鼠標來引用單元格的公式=單元格，這叫作直接引用。那么，能不能間接引用單元格呢？首先要弄明白，什么叫間接引用？肯定是通過一個中介來引用啊！那么，中介在哪里？怎么尋找中介？這就是間接引用，使用INDIRECT函數，想辦法成立一個中介，讓中介去幫我們干活。
再比如，MATCH是干什么的？單詞的直譯就是匹配，那么，匹配什么？為什么要匹配？其實，稱呼定位更恰當一些，把指定的數據，在指定的數組中，定位出它藏在哪里，也就是位置。再仔細看，這個函數的第二個參數是不是Array？既可以是工作表的某列或某行，也可以是一個數組。
很多函數，單純從名字上就可以了解一個大概，再仔細觀察每個參數的名稱及定義，可以說就不會有學不會函數基本用法的。這里只能先說到是基本用法，而不是靈活應用。
在實際數據處理和數據分析中，我們必須結合具體表格來做公式，才有意義。如果要徹底學會函數，能夠靈活運用函數設計公式，就肯定是離不開具體表格了，因為公式只有存在于表格中才能存活，才有生命力。
不同的應用場景、不同結構的表格、不同的思路、不同人的喜好，使用的函數是不一樣的，因此做出的公式也是五花八門的。例如，對于圖1所示的表格，至少有以下幾種解決公式，所使用的函數各不相同：
圖1 查找指定地區指定產品的數據
公式1，從左往右查找，使用VLOOKUP函數（使用MATCH函數列方向定位）：
=VLOOKUP(J3,B4:F9,MATCH(J4,B3:F3,0),0)
公式2，從上往下查找，使用HLOOKUP函數（使用MATCH函數行方向定位）：
=HLOOKUP(J4,C3:F9,MATCH(J3,B3:B9,0),0)
公式3：通過兩個坐標查找，聯合使用INDEX函數和MATCH函數：
=INDEX(C4:F9,MATCH(J3,B4:B9,0),MATCH(J4,C3:F3,0))
公式4，通過偏移行和偏移列查找，聯合使用OFFSET函數和MATCH函數：
=OFFSET(B3,MATCH(J3,B4:B9,0),MATCH(J4,C3:F3,0))
公式5，通過間接查找，聯合使用INDIRECT函數和MATCH函數：
=INDIRECT("R"&MATCH(J3,B:B,0)&"C"&MATCH(J4,3:3,0),0)
公式6，通過求和函數SUM查找，條件滿足就是自己本身，構建數組公式：
=SUM((B4:B9=J3)*(C3:F3=J4)*C4:F9)
公式7，通過求和函數SUMPRODUCT查找，條件滿足就是自己本身，構建普通公式：
=SUMPRODUCT((B4:B9=J3)*(C3:F3=J4)*C4:F9)
公式8，通過左右偏移求和查找，聯合使用SUMIF函數、OFFSET函數和MATCH函數：
=SUMIF(B4:B9,J3,OFFSET(B4,,MATCH(J4,C3:F3,0),6,1))
公式9，上下偏移求和查找，聯合使用SUMIF函數、OFFSET函數和MATCH函數：
=SUMIF(C3:F3,J4,OFFSET(C3,MATCH(J3,B4:B9,0),,1,4))
公式10：先從列上匹配地區，得到指定地區下的各個產品的數據，再從行上匹配產品，獲取指定產品數據，使用嵌套XLOOKUP函數構建公式：
=XLOOKUP(J5,C3:F3,XLOOKUP(J4,B4:B9,C4:F9))
公式11：先從行上匹配產品，得到指定產品下的各個地區的數據，再從列上匹配地區，獲取指定地區數據，使用嵌套XLOOKUP函數構建公式：
=XLOOKUP(J4,B4:B9,XLOOKUP(J5,C3:F3,C4:F9))
可見，即使是同一個表格，由于思路的不同，解決問題的切入點不同，使用的函數是不一樣的，因此做出的公式也是不一樣的，公式有簡單，也有復雜，有高效，也有低效。
下面是另外一個很簡單的例子，一個學生問了這樣一個問題：如何從B列中，將費用名稱和項目名稱提取出來，分兩列保存？如圖2所示。
圖2 要求從B列提取費用名稱和項目名稱
我們的任務是：從B列中，將費用名稱和項目名稱分別提取出來，那么就要分析B列中費用名稱和項目名稱的特征是什么了。
仔細觀察B列數據特征，凡是帶著費字的，就是費用名稱，否則就是項目名稱。
那么，如何判斷某個單元格有費字呢？我們知道有一個函數就可以解決這樣的問題：FIND函數。
FIND函數就是從一個字符串中，查找指定字符出現的位置，如果有指定的字符，函數的結果就是一個表示出現位置的序號。例如，下面公式的結果就是3，因為在字符串保險費用中，字符費出現在第3個位置上：
=FIND("費","保險費用")
這樣，不管指定字符出現在什么位置，只要存在，結果就是一個數字，那么就可以使用ISNUMBER函數來判斷FIND函數結果是否為數字，如果是數字，就表示是費用名稱。
提取費用名稱還有一個問題，如果B列含有費字，就是費用名稱，那么不含有費字呢？如何在該行單元格輸入費用名稱？我們已經在上一行單元格判斷并提取出了費用名稱，那么下一行單元格填充為上一行單元格已經提取出的費用名稱，就可以了。
因此，單元格F2提取費用名稱的公式就可以做出來了，如下所示：
=IF(ISNUMBER(FIND("費",B2)),B2,F1)
提取項目名稱的公式，是判斷B列單元格是否沒有費字。那么，何謂沒有？使用FIND函數查找指定字符，如果存在就是一個數字，如果不存在，就是一個錯誤值，所以只要判斷是不是錯誤值就可以了，此時，使用ISERROR函數來判斷FIND函數的結果是否為錯誤值。因此，單元格G2提取項目名稱的公式如下：
=IF(ISERROR(FIND("費",B2)),B2,"")
這兩列的公式都并不復雜，邏輯也是很簡單的，就是考察你是不是徹底了解表格結構、數據特征，找出了解決問題的邏輯思路。
我不止一次給學生們說過，學習Excel函數公式的核心是學習邏輯思路，而不是機械套用，正如上面介紹的幾個公式，就是不同邏輯思路的體現。你能看出這些公式的邏輯思路有什么不同嗎？
邏輯思路，永遠是函數公式的核心。
本書不僅介紹了常規Excel版本所共有的函數，也詳細介紹了Excel 365及Excel 2019以后版本所特有的功能更加強大的函數，使你能掌握更多的實用工具。更重要的是，訓練自己的數據處理和數據分析的邏輯思維，提升解決問題的能力。
學習需要持之以恒，也需要不斷總結和提升。
祝愿各位使用Excel的朋友，工作愉快，學習快樂，每天都有進步。
掃描下方二維碼，獲取案例素材及原圖
Excel函數公式綜合應用實踐案例視頻精講
Excel函數公式綜合應用實踐案例視頻精講
第1章創建高效Excel函數公式技能01
11 公式的本質，是如何進行計算的邏輯思維02
111 仔細閱讀表格，尋找解決思路02
112 梳理邏輯思路，繪制邏輯流程圖04
12 重要技能，高效輸入嵌套函數公式06
121同一個函數嵌套：函數參數對話框 名稱框方法07
122 不同函數嵌套：函數參數對話框 名稱框方法08
123 復雜的嵌套公式：分解綜合法10
13 使用名稱創建高效、靈活的數據分析公式13
131 使用名稱簡化公式13
132 名稱應用案例：使用名稱制作動態圖表16
14 使用數組公式解決復雜計算問題17
141 數組基本知識18
142數組公式基本知識19
143 數組公式應用案例：計算前N大數據之和19
第2章數據邏輯判斷處理案例精講23
21 條件表達式應用技能與案例24
211 使用條件表達式的注意事項24
212條件表達式的書寫規則24
213 條件表達式應用案例25
22 信息函數及其應用案例27
221 判斷單元格狀態27
222 判斷數據類型28
223 判斷是否為錯誤值30
224 判斷數字的奇偶31
225 獲取工作表信息33
226 獲取工作簿信息33
227 構建自動化的累計數計算公式34
23 IF函數基本邏輯與應用35
231 IF函數基本原理與邏輯36
232 IF函數基本應用案例36
24 IF函數嵌套應用38
241 繪制邏輯思路圖38
242 IF函數嵌套應用：單流程39
243 IF函數嵌套應用：多流程40
25 多條件組合判斷42
251 AND函數應用：多個與條件組合42
252 OR函數應用：多個或條件組合43
253 AND函數和OR函數聯合應用44
26 IF函數高級應用46
261 使用條件數組進行判斷46
262 使用常量數組進行判斷：VLOOKUP函數反向查找47
263 使用常量數組進行判斷：多個或條件求和48
27 其他邏輯判斷函數49
271 IFS函數及其應用50
272 IFERROR函數應用：處理錯誤值51
第3章文本數據處理案例精講53
31 清理數據中的垃圾54
311 TRIM函數：清理文本中的空格54
312 CLEAN函數：清除非打印字符54
32 統計字符的長度56
321 LEN函數和LENB函數的基本應用56
322 綜合應用案例：拆分列57
323 綜合應用案例：設置數據驗證，只能輸入漢字名稱58
324 綜合應用案例：設置數據驗證，只能輸入固定位數的不重復數據59
33 截取字符60
331 LEFT函數：從左側截取字符60
332 RIGHT函數：從右側截取字符61
333 MID函數：從指定位置截取字符62
334 截取字符綜合應用案例：從身份證號碼提取信息62
34 連接字符串63
341 用連接運算符&連接字符串64
342 使用CONCAT函數或CONCATENATE函數連接字符串64
343 使用TEXTJOIN函數連接字符串64
344 綜合應用案例：設計摘要說明65
35 替換字符67
351 SUBSTITUTE函數及其應用67
352 REPLACE函數及其應用68
353 綜合應用案例：快速統計匯總68
36 查找字符69
361 FIND函數及其應用69
362 SEARCH函數及其應用70
363 綜合應用案例：復雜的數據分列71
37 轉換字符72
371 為什么要使用TEXT函數轉換字符72
372 TEXT函數及其應用72
373 綜合應用案例：直接使用原始數據進行匯總計算75
38 其他實用的文本函數及引用76
381 CHAR函數及其應用76
382綜合應用案例：在圖表中顯示更多信息77
第4章日期和時間數據處理案例精講80
41 處理文本格式的日期81
411 使用SUBSTITUTE函數處理非法日期81
412 使用TEXT函數處理非法日期81
413 使用DATEVALUE函數處理文本型日期81
42 日期組合與還原82
421 使用DATE函數組合年月日數字82
422 使用DATE函數或DATEVALUE函數和其他函數生成具體日期83
43 從日期中提取重要信息84
431 從日期中提取年月日數字84
432 從日期中提取年份、月份和日名稱85
433 從日期中提取季度名稱87
434 從日期中提取星期幾及星期名稱87
435 從日期中提取周次名稱91
44 獲取當前日期和時間93
441 TODAY函數及其應用93
442 NOW函數及其應用94
443 NOW函數與TODAY函數的區別95
45 計算將來日期和過去日期96
451 EDATE函數：計算將來或過去的具體日期96
452 EOMONTH函數：計算將來或過去的月底日期97
453 綜合應用案例：計算合同到期日97
454 綜合應用案例：計算付款截止日99
46 計算兩個日期之間的期限100
461DATEDIF函數及其應用100
462 YEARFRAC函數及其應用102
463 綜合應用案例：直接用身份證號碼計算年齡103
47 計算工作日103
471 計算一段時間后的工作日是哪天104
472 計算兩個日期之間的工作日天數105
48 計算時間106
481 時間計算規則及注意事項106
482 時間計算經典案例：指紋打卡數據處理107
483 時間計算經典案例：機器工時計算108
第5章數據統計與匯總案例精講110
51 數據計數統計分析111
511 COUNTA函數：統計不為空的單元格個數111
512 COUNTIF函數：統計滿足一個指定條件的單元格個數112
513 COUNTIFS函數：統計滿足多個指定條件的單元格個數113
514 綜合應用案例：統計數據出現次數114
515 綜合應用案例：員工屬性分析報告117
516 綜合應用案例：刪除兩個表都存在的數據120
517 構建條件值數組進行或條件的計數121
518 使用COUNTIF函數和COUNTIFS函數的注意事項123
52 數據求和統計分析124
521 SUMIF函數基本原理與基本應用124
522SUMIF函數經典案例：對小計行求和125
523 SUMIF函數經典案例：隔列求和126
524 SUMIF函數經典案例：關鍵詞匹配求和127
525 使用SUMIF函數實現多個或條件值的求和：精確值匹配條件127
526 使用SUMIF函數實現多個或條件值的求和：關鍵詞匹配條件128
527 使用SUMIF函數計算指定季度的合計數129
528 使用SUMIF函數做單條件數據查找130
529 SUMIFS函數基本原理與基本應用131
5210 SUMIFS函數經典案例：比較值條件求和132
5211 SUMIFS函數經典案例：關鍵詞匹配條件求和133
5212 使用SUMIFS函數實現多個或條件值的求和：精確值匹配條件134
5213 使用SUMIFS函數實現多個或條件值的求和：關鍵詞匹配條件136
5214 使用SUMIFS函數做多條件數據查找137
5215 使用SUMIF函數和SUMIFS函數做關鍵詞匹配條件的數據查找138
5216 使用SUMIF函數和SUMIFS函數的注意事項139
53 數組求和函數SUMPRODUCT及其應用139
531 SUMPRODUCT函數基本原理與基本應用140
532 SUMPRODUCT函數統計公式值不為空的單元格個數142
533 SUMPRODUCT函數用于單條件計數143
534 SUMPRODUCT函數用于多條件計數144
535 SUMPRODUCT函數用于多條件計數：關鍵詞匹配145
536 SUMPRODUCT函數用于單條件求和146
537 SUMPRODUCT函數用于多條件求和146
538 SUMPRODUCT函數用于條件求和：關鍵詞匹配147
539 SUMPRODUCT函數用于復雜條件的數據查找148
5310 SUMPRODUCT函數綜合應用案例149
54 數據最大值、最小值和平均值計算152
541 計算數據最大值（無條件）152
542 計算數據最大值（指定條件）153
543 計算數據最小值（無條件）154
544 計算數據最小值（指定條件）155
545 計算數據平均值（無條件）156
546 計算數據平均值（指定條件）157
547 指定幾個或條件下的最大值、最小值和平均值159
548 計算數據中位數160
549 計算數據四分位值161
55 數字編碼超過15位情況下的計算問題163
551 一個案例揭示的問題163
552 解決方法163
第6章數據查找與引用案例精講165
61 VLOOKUP函數及其案例166
611 基本原理與使用方法166
612 使用關鍵詞條件查找數據168
613 從不確定的列中查找數據：使用MATCH函數169
614 利用VLOOKUP函數模糊查找170
615 利用VLOOKUP函數做反向查找172
616 利用VLOOKUP函數一次查找并返回多個值173
617 利用VLOOKUP函數制作動態分析圖表174
618 VLOOKUP函數的注意事項175
62 LOOKUP函數及其案例176
621 基本原理與應用方法177
622 綜合應用案例：獲取最后一個不為空的單元格數據178
623 綜合應用案例：獲取滿足多個條件下最后一個不為空的數據179
624 綜合應用案例：替代嵌套IF函數和VLOOKUP函數做模糊判斷180
63 XLOOKUP函數及其案例182
631 基本原理與使用方法182
632 單列匹配條件，返回一個結果183
633 單行匹配條件，返回一個結果184
634 單列匹配條件，返回多個結果186
635 單行匹配條件，返回多個結果186
636 關鍵詞條件匹配查找187
637 模糊條件匹配查找188
638 自動處理錯誤值189
64 MATCH函數及其案例191
641 MATCH函數：關鍵詞匹配定位191
642 MATCH函數：模糊匹配定位191
643 MATCH函數與其他函數聯合使用192
65 INDEX函數及其案例194
651 基本原理與使用方法194
652 綜合應用案例：單列或單行查找數據195
653 綜合應用案例：多行多列區域查找數據（簡單情況）196
654 綜合應用案例：多行多列區域查找數據（復雜情況）196
655 綜合應用案例：制作動態圖表（表單控件）199
66 OFFSET函數及其案例201
661 基本原理與使用方法201
662 基本應用案例：引用某列動態區域，制作隨數據增減自動調整的
圖表203
663 基本應用案例：引用某行的動態區域，計算累計數206
664 綜合應用案例：分析指定起止月份之間的預算執行情況207
665 小技巧：如何判斷OFFSET函數結果是否正確208
67 INDIRECT函數及其案例209
671 基本原理與使用方法209
672 綜合應用案例：快速從各個工作表查詢匯總數據211
673 綜合應用案例：各月費用跟蹤分析212
674 INDIRECT函數應用的注意事項216
675 小技巧：如何判斷INDIRECT函數結果是否正確217
第7章數據排名分析案例精講218
71 LARGE函數和SMALL函數及其案例219
711 基本原理與使用方法219
712 數據基本排序及名稱匹配220
713 相同數據排序及名稱匹配問題221
714 綜合應用案例：建立自動化排名分析模板223
715 LARGE函數和SMALL函數的注意事項227
72 SORT函數及其案例228
721 基本原理與使用方法228
722 使用數組進行多列多方式排序229
723 綜合應用案例：建立自動化排名分析模型（簡單情況）230
724 綜合應用案例：建立自動化排名分析模型（復雜情況）231
73 SORTBY函數及其案例233
731 基本原理與使用方法233
732 任意指定條件下的動態排序（按列排序）234
733 任意指定條件下的動態排序（按行排序）236
734 綜合應用案例：按照自定義序列排序237
74 排位分析及其案例239
741 基本原理與使用方法239
742 綜合應用案例：業務員排名及額外獎金計算241
第8章數據篩選分析案例精講242
81 FILTER函數及其案例243
811 基本原理與使用方法243
812 單條件篩選245
813 多個與條件篩選246
814 多個或條件篩選247
815 多個與條件和或條件組合下的篩選248
816 計算條件下的篩選249
817 關鍵詞匹配條件下的篩選251
82 FILTER函數綜合應用案例253
821 建立任意指定單列條件的動態篩選表253
822 建立多條件動態篩選表254
823 篩選與排序聯合使用：基本應用256
824 篩選與排序聯合使用：綜合應用258
825 建立多條件動態篩選與排序模型260
826 剔除零值的動態分析261
827 對比篩選兩個表格262
828 嵌套篩選數據264
83 使用其他查找函數快速制作明細表265
831 利用其他函數做滾動查找：基本原理和方法265
832 利用其他函數做多條件滾動查找268
第9章數據其他處理案例精講270
91 數字舍入計算函數及應用271
911 使用ROUND函數將數字常規四舍五入271
912 使用RUNDUP函數和ROUNDDOWN函數將數字向上/向下舍入273
92 數據分組分析函數及應用275
921 使用FREQUENCY函數統計數據頻數分布275
922 使用COUNTIFS函數統計數據頻數分布277
93 數據預測分析函數及應用278
931 建立一元線性預測模型278
932 建立多元線性預測模型280
933 建立指數預測模型281
94 其他數據處理函數及應用283
941 繪制折線圖必需的NA函數283
942 對公式空值做算式計算必需的N函數285
943 將全角字符轉換為半角字符必需的ASC函數286
第10章函數公式綜合應用案例精講287
101 滾動匯總每天各個門店的銷售日報288
1011 門店每天銷售額滾動匯總288
1012 門店每天銷售毛利滾動匯總289
102 跟蹤分析各個門店的每天銷售數據290
1021 各個門店的銷售額跟蹤分析290
1022 各個門店的銷售毛利跟蹤分析292
103 門店銷售排名分析293
1031 各個門店的累計銷售額排名分析293
1032 各個門店的累計銷售毛利排名分析294
1033 各個門店的毛利率排名分析295
新書資訊