別怕，Excel 函數其實很簡單（第2版）

網名ggsmart，人稱葉楓，Excel Home雲課堂人氣講師，擁有豐富的教育培訓經驗，擅長Excel數據處理與分析，在使用Excel 函數方面擁有豐富的學習心得和實踐經驗。其發表於Excel Home的技術帖成為大量Excel 函數初學者的啟蒙“教材”。

 

周慶麟：網名Kevin，連續22年當選微軟全球最有價值專家（MVP），Excel Home創始人、站長，策劃和編寫了百餘本Office技術圖書和教材，同時也是一名優秀的技術顧問和培訓講師，有著豐富的授課經驗。

第 1 章 函數與公式多能乾？我們一起來看看

 

1.1 你以為我很厲害？不，我其實也這樣窘過 2

1.1.1 大家習慣了低效的計算方法 2

1.1.2 那時候，我以為這種查詢數據的方法很高明 4

1.2 認清自己後，我踏上了“小白的逆襲之路” 5

1.2.1 你是否也覺得學得越多，懂得越少 5

1.2.2 為什麽要學習 Excel 函數與公式 9

 

第 2 章 要學函數與公式，這些概念得掌握

 

2.1 公式並不神秘，它只是一個能自動計算的表達式 13

2.2 熟悉這些內容，編寫公式才能得心應手 14

2.2.1 一個完整的公式，可能會包含這些信息 14

2.2.2 數據也有自己的“小團夥” 15

2.2.3 數據常量別寫錯了 19

2.2.4 讓數據參與計算，單元格地址應該這樣寫 20

2.2.5 別小看單元格地址中的“＄”，它的用處可大了 23

2.2.6 案例：若一列數據不便打印，可用公式將它轉換為多列 26

2.2.7 運算符就是計算或分析數據需要使用的符號 31

2.2.8 函數就是已經封裝好、能直接使用的公式 34

2.2.9 簡單地看看，Excel 中都有哪些函數 36

2.3 另類的 Excel 公式——名稱 37

2.3.1 認識 Excel 中的名稱 37

2.3.2 怎樣定義一個名稱 38

2.3.3 編輯或刪除已定義的名稱 41

2.3.4 為公式定義名稱（命名公式） 41

2.3.5 在公式中使用名稱 42

 

第 3 章 判斷選擇不簡單，用對函數並不難

 

3.1 “是”或“不是”，應該這樣表示 45

3.1.1 邏輯值，是 Excel 對某個事件判斷的結果 45

3.1.2 比較運算返回的結果是邏輯值 45

3.2 IF 函數，解決選擇問題經常用到它 46

3.2.1 選擇問題，就是從多種方案中選擇一種 46

3.2.2 案例：用 IF 函數判斷考核分是否合格 47

3.2.3 案例：復制公式，判斷多個考核分是否合格 48

3.2.4 案例：多個等級中，怎樣確定每個考核分對應的等級 50

3.2.5 案例：用 IFS 函數給考核分評定等級 53

3.3 多條件判斷，應該使用這樣的表達方式 55

3.3.1 Excel 並不認識我們熟悉的不等式 55

3.3.2 用 AND 函數判斷數據是否同時滿足多個條件 57

3.3.3 用 OR 函數判斷數據是否滿足多個條件中的一個 58

3.3.4 用 NOT 函數求與參數相反的邏輯值 59

 

第 4 章 這些函數若掌握，數據計算變簡單

 

4.1 SUM 函數是常用的求和函數 61

4.1.1 SUM 函數是代替運算符“+”的最佳選擇 61

4.1.2 對數據進行求和，SUM 函數還具有這些特點 61

4.1.3 案例：用 SUM 函數求每個月的累計已繳稅額 64

4.1.4 案例：用 SUM 函數求多張工作表中交通補貼的總和 66

4.1.5 案例：快速在多個區域輸入求和公式 69

4.1.6 案例：求和的捷徑，快速匯總各類數據 70

4.2 認識 COUNT 家族，求單元格個數的“專業戶” 72

4.2.1 用 COUNTBLANK 函數統計空單元格的個數 72

4.2.2 用 COUNTA 函數統計非空單元格的個數 73

4.2.3 用 COUNT 函數統計數值單元格的個數 74

4.2.4 案例：統計應考人數和實考人數 74

4.3 用函數求指定數據的平均值、最大值、最小值 75

4.3.1 案例：用 AVERAGE 函數求一組數據的平均值 75

4.3.2 案例：用 MIN（MAX）函數求一組數據的最小值（最大值） 77

4.4 對數值進行取捨，可以使用取捨函數 77

4.4.1 案例：用 ROUND 函數將數據四捨五入且保留兩位小數 77

4.4.2 案例：用 ROUNDUP 函數強制對數值進行向上取捨 78

4.4.3 案例：用 ROUNDDOWN 函數強制對數值進行向下取捨 79

4.4.4 案例：用 INT 函數獲得小於或等於指定數值的最大整數 79

4.5 匯總篩選或隱藏狀態下的數據 80

4.5.1 SUBTOTAL 函數能完成哪些計算 80

4.5.2 SUBTOTAL 函數的計算規則 80

4.5.3 案例：用 SUBTOTAL 函數生成序號 86

 

第 5 章 文本處理雖復雜，能用函數應付它

 

5.1 將多個文本合並為一個 92

5.1.1 案例：用 CONCATENATE 函數將多個文本合並為一個 92

5.1.2 案例：使用文本連接符 & 將多個文本合並為一個 94

5.1.3 案例：用 CONCAT 函數輕松合並區域中的多個文本 95

5.1.4 案例：用 CONCAT 函數合並符合條件的部分數據 96

5.1.5 案例：合並數據需帶分隔符，用 TEXTJOIN 函數更方便 97

5.2 計算一個文本包含幾個字符 98

5.2.1 字符和字節——文本長度的兩個單位 98

5.2.2 案例：用 LEN 函數求文本包含的字符個數 99

5.2.3 案例：使用 LENB 函數求文本包含的字節個數 100

5.2.4 案例：判斷手機號是否正確 100

5.3 查找指定字符在文本中的起始位置 101

5.3.1 起始位置，就是字符在文本中從左往右數第 一次出現的位置 101

5.3.2 精確查找字符的起始位置，可以使用 FIND 函數 103

5.3.3 設置 FIND 函數開始查找的起始位置 105

5.3.4 案例：判斷居住地址是否屬於某個小區 106

5.3.5 用 SEARCH 函數按模糊條件查找字符的起始位置 108

5.3.6 用 SEARCH 函數查找字符位置時不區分大小寫 109

5.3.7 在 SEARCH 函數中使用通配符，設置模糊的查找內容 110

5.3.8 查找字符 “*”和“ ”時，公式應這樣寫 112

5.3.9 用 FINDB 和 SEARCHB 函數按字節查找字符的起始位置 112

5.4 截取字符串中的部分信息 113

5.4.1 用 LEFT 函數截取字符串左邊的部分字符 113

5.4.2 用 RIGHT 函數截取字符串右邊的部分字符 114

5.4.3 用 MID 函數截取字符串中任意位置的部分字符 114

5.4.4 用 LEFTB、RIGHTB 和 MIDB 函數按字節截取字符 115

5.4.5 案例：從身份證號中獲取性別信息 116

5.4.6 案例：截取數據中不含擴展名的文件名稱 118

5.4.7 案例：分離數據中的中文姓名和電子郵箱地址 120

5.4.8 案例：將金額數字按數碼拆分到多列中 123

5.5 將文本中的部分字符替換為新字符 125

5.5.1 用 SUBSTITUTE 函數替換指定的字符 125

5.5.2 案例：統計保存在同一單元格中的各組人數 129

5.5.3 案例：加密隱私數據（如電話號碼中的部分信息） 130

5.5.4 用 REPLACE 函數替換指定位置的字符 134

5.5.5 案例：批量將手機號等長數字信息分段顯示 134

5.5.6 案例：從身份證號中獲取出生日期信息 135

5.6 比較兩個文本數據是否相同 136

5.6.1 判斷兩個數據是否相同，常用比較運算符“=” 136

5.6.2 需要區分大小寫時，可以用 EXACT 函數 137

5.7 文本類型的數字怎樣轉換為能求和的數值 138

5.7.1 函數就像噴漆罐，只能返回某個類型的數據 138

5.7.2 不是所有數字都能正常參與算術運算 138

5.7.3 數字是文本還是數值，可以用這些方法辨別 139

5.7.4 將文本型數字轉換為數值，可以使用這些方法 142

 

第 6 章 查詢數據常遇到，公式套路要記牢

 

6.1 使用 VLOOKUP 函數查詢符合條件的數據 149

6.1.1 查詢數據，就是從另一張表中找到需要的信息 149

6.1.2 找準信息，是熟練用函數解決查詢問題的前提 150

6.1.3 用 VLOOKUP 函數在表中查詢符合條件的數據 151

6.1.4 案例：返回同一查詢結果的多列數據 153

6.1.5 案例：補全表中缺失的信息 156

6.1.6 案例：為考核分評定等級 158

6.1.7 案例：查找商品名稱包含部分字符的商品信息 161

6.1.8 案例：查詢符合條件的多條記錄 162

6.1.9 案例：查詢表中滿足多個條件的數據 164

6.2 使用 VLOOKUP 函數可能發生的查詢錯誤 167

6.2.1 為什麽 VLOOKUP 函數會返回錯誤值 #N/A 168

6.2.2 隱藏 VLOOKUP 函數返回的錯誤值 #N/A 168

6.2.3 公式與數據都正常，為什麽 VLOOKUP 函數仍然返回 #N/A 169

6.2.4 VLOOKUP 函數返回錯誤值 #REF!，原因其實很簡單 173

6.2.5 若函數返回的結果異常，看看這個參數是否正確 174

6.3 用 HLOOKUP 函數按橫向查詢表中數據 176

6.4 用 MATCH 函數確定指定數據的位置 177

6.4.1 MATCH 函數就是數據的定位追蹤器 177

6.4.2 用好 MATCH 函數，參數應該這樣設置 179

6.4.3 案例：快速找出兩表中相同或不同的數據 180

6.4.4 案例：獲取某個區域中不重復的數據信息 182

6.4.5 案例：確定 VLOOKUP 函數返回數據的列信息 184

 

6.5 如果知道數據的位置，可以用 INDEX 函數獲取它 186

6.5.1 用 INDEX 函數獲取表中指定行、列的數據 186

6.5.2 INDEX 函數的 3 個參數代表這 3 個關鍵信息 187

6.6 INDEX 和MATCH 函數，一對查詢數據的“黃金搭檔” 188

6.6.1 將 INDEX 和 MATCH 函數搭配使用，數據查詢會變得很簡單 188

6.6.2 案例：用 INDEX 和 MATCH 函數解決逆向查詢問題 189

6.6.3 案例：根據訂單號中的部分信息查詢商品名稱 190

6.6.4 案例：查詢適用稅率並計算應繳稅金額 191

6.6.5 案例：動態交叉查詢指定人員及學科的成績 196

6.7 功能強大的新查詢函數 XLOOKUP 197

6.8 返回多條查詢結果的 FILTER 函數 198

 

第 7 章 公式計算不正確，查找錯誤有妙招

 

7.1 EXCEL 中的公式可能會出現哪些錯誤 201

7.1.1 公式出錯，就是公式沒有返回期望的結果 201

7.1.2 有些錯誤，Excel 能自動發現並進行提示 201

7.1.3 每種錯誤值，都包含了公式出錯的原因 203

7.1.4 邏輯有誤，公式就不能返回正確的結果 203

7.2 公式返回的錯誤值代表什麽意思 204

7.2.1 返回 #####，可能是因為單元格格式設置錯誤 204

7.2.2 返回 #DIV/O!，可能是因為有公式將 0 設置為除數 205

7.2.3 返回 #VALUE!，可能是因為使用了錯誤類型的數據 206

7.2.4 返回 #NAME ，可能是因為函數或參數名稱輸入錯誤 206

7.2.5 返回 #REF!，可能是公式中使用了無效的單元格引用 208

7.2.6 返回 #N/A，可能是查詢函數沒有找到匹配的數據 210

7.2.7 返回 #NUM!，可能是給函數設置了無效的數值參數 211

7.2.8 返回 #NULL!，可能是引用運算符沒有可返回區域 212

7.2.9 不用牢記每個出錯原因，錯誤檢查器能給出提示 213

7.3 公式沒有返回正確結果應該怎麽辦 215

7.3.1 檢查函數的參數是否設置正確 215

7.3.2 檢查單元格的引用樣式是否正確 217

7.3.3 檢查公式的運算順序是否正確 218

7.3.4 檢查是否使用正確的方法輸入數組公式 219

7.3.5 註意辨別公式中使用的名稱 220

7.4 解讀公式，這些工具必須瞭解 223

7.4.1 公式越長，閱讀和理解它的難度也就越大 223

7.4.2 借助【編輯欄】瞭解公式的結構 224

7.4.3 借助【函數參數】對話框拆解公式 226

7.4.4 借助 鍵查看某部分公式的計算結果 227

7.4.5 借助【公式求值】命令觀察公式的計算過程 228