跟著視頻學Excel數據處理：函式篇

《跟著視頻學Excel數據處理：函式篇》由電子工業出版社於2021年1月出版，作者是曹明武（佛山小老鼠）。

本書內含260個視頻，輕鬆學習數據處理基礎；248個函式套用案例，涉及銷售、財務、人力資源等行業；40個Excel技巧，提高數據處理效率。

基本介紹

• 書名：跟著視頻學Excel數據處理：函式篇
• 作者：曹明武（佛山小老鼠）
• 出版社：電子工業出版社
• 出版時間：2021年1月
• 頁數：236 頁
• 定價：59.90 元
• 開本：16 開
• ISBN：9787121399145

內容簡介

《跟著視頻學Excel數據處理：函式篇》主要介紹Excel函式知識。主要內容包括函式基礎知識、函式使用小技巧、文本函式、查找引用函式、邏輯函式、日期和時間函式、其他函式，以及函式初級綜合案例、函式中級進階案例及函式高級案例。其中共涉及140個函式初級案例、79個函式中級案例、28個函式高級案例和40個常用技巧，讓讀者循序漸進學習Excel函式，並將其套用到數據處理中。

本書適合在校大學生和職場辦公人員、會計、統計、數據分析員、文員。

圖書目錄

第1章 函式基礎知識：Excel數據處理利器 1

1.1 函式基礎 1

1.2 函式小技巧 2

第2章 基礎函式：打好基礎，輕鬆實現數據處理 5

2.1 文本函式 5

2.1.1 RIGHT：從右側提取字元函式 5

案例01 使用RIGHT函式提取單元格中的字元 5

2.1.2 LEFT：從中間提取字元函式 6

案例02 使用LEFT函式提取單元格中的字元 6

2.1.3 MID：從中間提取字元函式 7

案例03 使用MID函式提取單元格中的字元 7

2.1.4 LEN：計算文本長度函式 7

案例04 使用LEN函式統計單元格中有多少個字元 7

2.1.5 LENB：文本長度計算函式（區分單雙位元組） 8

案例05 使用LENB函式統計單元格中共有幾個字元 8

案例06 使用LEFT函式提取單元格中左邊的漢字 9

案例07 使用RIGHT函式提取單元格中右邊的數字 9

2.1.6 MIDB：從指定位置提取字元函式（區分單雙位元組） 10

案例08 使用MIDB函式從單元格中提取指定的字元 10

2.1.7 SEARCH：不區分大小寫、支持通配符的查找函式 10

案例09 使用SEARCH函式查找指定字元的位置 11

2.1.8 SEARCHB：查找指定字元的位置函式（區分單雙位元組） 11

案例10 使用SEARCHB函式提取漢字中間的數字 11

2.1.9 FIND：區分大小寫、不支持通配符的查找函式 12

案例11 使用FIND函式從字元串中查找某個字元串所在的位置 12

2.1.10 ASC：將全形雙位元組轉換為半角單位元組函式 13

案例12 使用LENB函式把逗號轉為單位元組 13

2.1.11 WIDECHAR：把單位元組轉換為雙位元組函式 13

案例13 使用WIDECHAR函式把單位元組字元轉換成雙位元組字元 14

2.1.12 CHAR：將數字轉換為字元函式 14

案例14 使用CHAR函式自動填充26個大寫字母 14

2.1.13 CODE：將字元轉換為數字函式 15

案例15 使用CODE函式求字母A的ASCII碼 15

2.1.14 UPPER：將小寫字母轉換為大寫字母函式 15

案例16 使用UPPER函式把字母由小寫轉換為大寫 15

2.1.15 REPLACE：查找和替換函式 16

案例17 使用REPLACE函式把銀行卡號每隔4位加一個空格 16

2.1.16 TEXT：格式轉換函式 17

案例18 用TEXT函式將0值禁止且保留1位小數。 17

案例19 用TEXT函式計算2008-8-8是星期幾 17

案例20 使用TEXT函式根據分數判斷成績等級 18

案例21 使用TEXT函式將大於100的數值顯示為100，將小於

或等於100的數值顯示為其本身 19

2.1.17 T：檢測給定值是否為文本函式 19

案例22 使用T函式實現只保留文本 20

2.1.18 TRIM：清除空格字元專用函式 20

案例23 使用TRIM函式清除單元格兩端的空格 20

2.1.19 SUBSTITUTE：按值替換函式 21

案例24 使用SUBSTITUTE函式統計單元格中有多少個“c” 21

2.2 查找引用函式 21

2.2.1 LOOKUP：查找函式 21

案例25 使用LOOKUP函式查找A列中最後一個數值 22

案例26 使用LOOKUP函式查找A列中的最後一個文本 22

案例27 使用LOOKUP函式查找A列中的最後一個值 23

案例28 使用LOOKUP函式根據分數判斷成績等級 24

2.2.2 MATCH：查找址函式 24

案例29 使用MATCH函式查找某一個值的位置 25

案例30 使用MATCH函式根據日期返回對應的季度 25

案例31 使用MATCH函式根據數值返回對應的位置 26

案例32 使用MATCH函式查找最後一個銷售數量出現的位置 26

2.2.3 VLOOKUP：垂直查找函式 27

案例33 使用VLOOKUP函式根據姓名查找對應的銷量 27

案例34 使用VLOOKUP函式根據學生分數判斷成績等級 28

案例35 使用VLOOKUP函式查找和數據源列中的欄位順序

不一樣的信息 28

2.2.4 HLOOKUP：水平查找函式 29

案例36 使用HLOOKUP函式根據月份查找銷量 29

2.2.5 INDEX：引用函式① 30

案例37 使用INDEX函式引用單元格區域中的數值 30

案例38 使用INDEX函式實現反向查找 31

案例39 使用INDEX函式動態查詢每門科目的總分數 31

2.2.6 OFFSET：引用函式② 32

案例40 使用OFFSET函式向下引用某個單元格中的內容 32

案例41 使用OFFSET函式向上引用某個單元格中的內容 33

案例42 使用SUM函式動態求每列的數量之和 33

2.2.7 INDIRECT：引用函式③ 34

案例43 使用INDIRECT函式引用單元格中的值 34

案例44 使用INDIRECT函式根據工號查找姓名 35

2.2.8 CHOOSE：引用函式④ 35

案例45 使用CHOOSE函式引用單元格中的值 36

案例46 更改列數據的位置 36

案例47 使用VLOOKUP函式輕鬆實現反向查找 36

2.2.9 ROW：返回行號函式 37

案例48 使用ROW函式輸入26個英文字母 37

案例49 使用SUM和ROW函式求從1加到100的結果 38

2.2.10 COLUMN：返回列函式 38

案例50 使用COLUMN函式計算數值 39

2.2.11 ADDRESS：單元格地址函式 39

案例51 使用ADDRESS函式根據列號返回對應的字母 39

2.2.12 TRANSPOSE：轉置函式 40

案例52 使用TRANSPOSE函式把單元格區域中的內容橫向顯示 40

2.2.13 HYPERLINK：超連結函式 41

案例53 使用HYPERLINK函式為單元格中的值設定超連結 41

案例54 使用HYPERLINK函式實現單元格之間的跳轉 41

2.3 邏輯函式 42

2.3.1 IF：條件判斷函式 42

案例55 使用IF函式判斷學生成績：小於60分為不及格，

否則為及格 42

案例56 使用IF函式判斷成績 43

2.3.2 TRUE：邏輯真函式 43

案例57 求TRUE加TRUE等於幾 43

2.3.3 FALSE：邏輯假函式 44

案例58 求TRUE+FALSE等於幾？ 44

案例59 使用IF函式把0值禁止 44

2.3.4 AND：檢查所有參數是否為TRUE函式 45

案例60 使用AND函式判斷學生成績：如果三科成績都大於或

等於60就返回“通過” 45

2.3.5 OR：檢查所有參數是否為FALSE函式 45

案例61 使用OR函式判斷學生成績 46

2.3.6 NOT：相反函式 46

案例62 使用NOT 函式判斷參數的邏輯值 46

2.3.7 IFERROR：螢幕錯誤值函式 47

案例63 使用IFERROR函式禁止公式中的錯誤值 47

2.4 日期和時間函式 47

2.4.1 YEAR：年函式 47

案例64 使用YEAR函式提取日期中的年份 48

2.4.2 MONTH：月函式 48

案例65 使用MONTH函式提取日期中的月份 48

2.4.3 DAY：日函式 49

案例66 使用DAY函式提取日期中的日 49

2.4.4 DATE：日期函式 49

案例67 使用DATE函式根據年、月、日返回“年-月-日”

格式的日期 49

2.4.5 EOMONTH：月末函式 50

案例68 使用EOMONTH函式統計每個月有多少天 50

2.4.6 HOUR：時函式 51

案例69 使用HOUR函式提取時間中的小時數 51

2.4.7 MINUTE：分函式 51

案例70 使用MINUTE函式提取時間中的分鐘數 51

2.4.8 SECOND：秒函式 52

案例71 使用SECOND函式提取時間中的秒數 52

2.4.9 NOW：系統時間函式 52

案例72 使用NOW函式返回當前日期和時間 52

2.4.10 TODAY：系統日期函式 53

案例73 使用TODAY函式返回當前日期 53

2.4.11 WEEKDAY：計算星期幾函式 53

案例74 使用WEEKDAY函式高亮顯示周六和周日 54

2.4.12 DATEDIF：日期處理函式 55

案例75 使用DATEDIF函式根據出生日期計算年紀 56

2.5 其他函式 56

2.5.1 AVERAGE：求平均值函式 56

案例76 使用AVERAGE函式求單元格區域的平均值 57

2.5.2 AVERAGEIF：單條件求平均值函式 57

案例77 使用AVERAGEIF函式求單元格區域中大於或等於某個

值的值的平均值 57

2.5.3 AVERAGEIFS：多條件求平均值函式 58

案例78 求大於300且小於800的值的平均值 58

2.5.4 COUNT：計算數字個數函式 59

案例79 使用COUNT函式統計單元格區域中的數據有多少個

為數值型 59

2.5.5 COUNTA：非空計數函式 59

案例80 統計單元格區域中非空單元格的個數 60

2.5.6 COUNTIF：單條件計數函式 60

案例81 使用COUNTIF函式統計字元 60

2.5.7 COUNTIFS：多條件計數函式 61

案例82 使用COUNTIFS函式統計業務員是“曹麗”且銷量

大於500的記錄個數 61

2.5.8 SUM：求和函式 62

案例83 使用SUM函式求多個工作表單元格中的值的和，

但不包括當前的工作表 62

2.5.9 SUMPRODUCT：計算乘積之和函式 62

案例84 使用SUMPRODUCT函式求產品名稱是A且型號是

大號的產品數量 63

2.5.10 PRODUCT：計算所有參數的乘積函式 63

案例85 計算體積 63

2.5.11 SUMIF：單條件求和函式 64

案例86 使用SUMIF函式匯總數據 64

2.5.12 SUMIFS：多條件求和函式 64

案例87 使用SUMIFS函式多條件求產品數量 65

2.5.13 MIN：最小值函式 65

案例88 使用MIN函式判斷單元格中的數值 65

2.5.14 MAX：最大值函式 66

案例89 使用MAX函式判斷單元格中的數值 66

2.5.15 SMALL：返回第k個最小值函式 67

案例90 使用SMALL函式升序排序單元格中的數值 67

2.5.16 LARGE：返回第k個最大值函式 67

案例91 使用LARGE函式降序排序單元格區域中的數值 67

2.5.17 SUBTOTAL：分類匯總函式 68

案例92 使用SUBTOTAL函式給隱藏的行自動編號 68

2.5.18 ROUND：四捨五入函式 69

案例93 使用ROUND函式將單元格區域中的數值保留兩位小數 69

2.5.19 ROUNDDOWN：向下捨入函式 69

案例94 使用ROUNDDOWN函式保留一位小數，不進行四舍

五入，全部捨棄 69

2.5.20 ROUNDUP：向上捨入函式 70

案例95 使用ROUNDUP函式保留一位小數，不進行四捨五入，

全部進入 70

2.5.21 CEILING：按倍數向上進位函式 70

案例96 使用CEILING函式進行數值捨入：十分位不足0.5

就按0.5算，大於或等於0.5就向上進1 71

2.5.22 FLOOR：按倍數向下捨入函式 71

案例97 使用FLOOR函式進行數值捨入，十分位不足0.5就

按向下捨去，大於或等於0.5就按0.5處理 71

2.5.23 INT：取整函式 72

案例98 使用INT函式把日期提取出來 72

2.5.24 MOD：取余函式 73

案例99 使用MOD函式求餘數 73

2.5.25 REPT：重複函式 73

案例100 使用REPT函式製作符號編號 73

2.5.26 N：將非數值型數值轉換為數值型數值函式 74

案例101 使用N函式將數值進行轉換 74

2.5.27 ABS：取絕對值函式 75

案例102 使用ABS函式求絕對值 75

2.5.28 CELL：獲取單元格信息的函式 75

案例103 使用CELL函式獲取工作簿路徑及工作表信息 76

2.5.29 ISNUMBER：檢測是否為數值型數字函式 76

案例104 使用ISNUMBER函式判斷數值型數值 76

2.5.30 ISTEXT：檢測一個值是否為文本函式 77

案例105 使用ISTEXT函式判斷文本型數值 77

2.5.31 PHONETIC：另類文本字元連線函式 77

案例106 使用PHONETIC函式連線文本 77

2.5.32 RAND：取隨機小數函式 78

案例107 使用RAND函式生成隨機數 78

2.5.33 RANDBETWEEN：取隨機整數函式 78

案例108 使用RANDBETWEEN函式生成指定大小的隨機整數 79

2.5.34 MODE：取眾數函式 79

案例109 使用MODE函式判斷出現次數最多的數值 79

2.6 初級函式綜合案例 80

案例110 使用TEXT函式把秒數轉換為分鐘數 80

案例111 為什麼使用SUMIF函式的求和結果是0 81

案例112 對比兩張表中的數據 83

案例113 判斷奇偶行的兩種方法 85

案例114 使用SUMIF函式遇到通配符時如何解決 86

案例115 提取單元格中靠左側的漢字 87

案例116 從漢字中提取數字的最簡單的方法 88

案例117 求A1:C1的和（簡單的數值相加為什麼會報錯） 88

案例118 使用LOOKUP函式實現反向查找 89

案例119 比IF函式還經典的判斷用法 90

案例120 引用每個表的C列中的最後一個值 92

案例121 將單元格中的內容進行分列 93

案例122 求18:00—23:00 有幾個小時 94

案例123 為什麼使用SUM函式無法求和 95

案例124 提取小括弧里的數據 95

案例125 每隔4行提取數據組成新的一列 97

案例126 最簡單的分類匯總方法 98

案例127 判斷漢字和字母 99

案例128 用VLOOKUP函式實現多表查找 100

案例129 將一列數據快速轉換為兩列數據 101

案例130 根據產品名返回最後一次進價 101

案例131 提取最後一個月的數據 102

案例132 為什麼使用VLOOKUP函式得不到正確的結果 103

案例133 為什麼使用 SUMPRODUCT函式得不到正確的結果 104

案例134 如何實現“六舍七入” 105

案例135 動態獲取當前工作表名稱 107

案例136 Excel中的兩個通配符的用法 108

案例137 ROW函式與ROWS函式的區別 108

案例138 如何把“2017-10-20”轉換為“20171020” 111

案例139 為什麼公式=IF(2,3,4)返回3 112

案例140 隱藏0值 112

案例141 計算表達式 113

案例142 如何把“2017.8.30”轉換成“2017年8月30日” 114

第3章 中級函式：實現批量數據處理 115

3.1 數組 115

3.2 中級數組函式經典案例 118

案例143 用數組求1～100的和 118

案例144 用數組求文本中的數字之和 119

案例145 使用MID函式求單元格中的數字之和 119

案例146 使用LEN函式統計單元格區域中有多少個字母A 119

案例147 使用RIGHT函式提取單元格中右邊的數字 120

案例148 使用SUMIF函式求張三和李四的銷量之和 120

案例149 使用VLOOKUP函式求每個員工上半年和下半年的

銷量之和 121

案例150 使用COUNTIF函式統計字元共出現多少次 121

案例151 使用MATCH函式統計不重複值的個數 122

案例152 使用FIND函式查找最後一個“/”的位置 123

案例153 使用FIND函式查找單元格中第一個數字出現的位置 123

案例154 使用數組根據日期返回對應的季度 124

案例155 使用數組隔行求和 124

案例156 使用數組引用每一行單元格中的最後一個數據 125

案例157 使用數組引用每一行單元格中的第一個數據 125

案例158 使用數組統計超過15位數字的個數 126

案例159 使用MID函式提取單元格中最後一個逗號後面的數據 126

案例160 使用MID函式從中英文中提取數字 127

案例161 使用IF+VLOOKUP函式實現反向查找 127

案例162 使用CHOOSE函式實現反向查找 128

案例163 使用COUNTIF函式統計大於100且小於200的

數字個數 128

案例164 雙條件查找的7種方法 129

案例165 使用INDEX函式實現一對多查詢並且縱向顯示結果 132

案例166 使用INDEX函式實現一對多查詢並且橫向顯示結果 133

案例167 實現一對多查詢並且將結果用頓號分隔 133

案例168 LOOKUP+FIND函式的經典組合套用 134

案例169 單列去重 135

案例170 多列去重 135

案例171 中國式排名 136

案例172 美國式排名 137

案例173 多工作表匯總 137

案例174 目錄製作 138

案例175 VLOOKUP函式的第1參數數組用法 139

案例176 計算體積 139

案例177 把9*20*30中的數字分別提取到3個單元格中 140

案例178 將多列轉為一列 140

案例179 將一列轉為多列 141

案例180 用全稱匹配簡稱 141

案例181 用簡稱匹配全稱 142

案例182 使用LOOKUP函式實現多條件查找 142

案例183 對合併單元格按條件求和 143

案例184 查找最後一次出現的位置對應的值 144

案例185 雙條件計數 144

案例186 如何生成序列 145

案例187 引用合併單元格中的數據 145

案例188 從漢字中提取數字 146

案例189 將通話記錄里的分和秒相加 146

案例190 使用COUNTIF函式統計不連續列中的字元個數 147

案例191 使用COUNTIF函式統計不重複值的個數 147

案例192 使用COUNT函式統計不重複值的個數且

排除空單元格 148

3.3 中級函式 149

案例193 根據身份證號提取戶籍所在地址 149

案例194 根據身份證號提取出生日期 149

案例195 根據身份證號提取性別 150

案例196 根據身份證號計算年齡 150

案例197 根據名稱顯示照片 151

案例198 使用VLOOKUP函式製作工資條 152

案例199 將周末高亮顯示 152

案例200 使用定義名稱功能+INDIRECT函式實現二級下拉選單 154

案例201 使用數組公式實現二級下拉選單 155

案例202 將TEXT函式當IF函式用 155

案例203 為銀行卡號每隔4位加空格 156

案例204 動態求每周的銷量 156

案例205 設定七天內生日提醒 157

案例206 使序號隨著篩選而自動編號 158

案例207 給合併單元格編號 158

案例208 不顯示錯誤值的3種方法 159

案例209 TEXT函式十!的用法 159

案例210 計算經過多少個工作日完成任務 160

案例211 向下和向右填充公式生成26個字母 161

案例212 提取括弧里的數據 161

案例213 計算一個日期為當月的第幾周 162

案例214 隔列求和的3種方法 163

案例215 取得單元格的列號 164

案例216 篩選在19:00—23:00範圍內的時間 164

案例217 判斷某月有多少天 165

案例218 獲取當前工作表的名稱 165

案例219 輸出4位數，不足4位在左邊加0 166

案例220 限制單元格中只能輸入15位或者18位字元 167

第4章 高級函式：Excel函式高級驗法 168

4.1 MMULT：矩陣乘積函式 168

案例221 使用MMULT函式求各科成績總和 169

案例222 通過MMULT函式求每一個人的總分 169

案例223 使用MMULT函式單條件求和 170

案例224 使用MMULT函式實現多行多列查找 171

案例225 找出每個銷售員銷量最大的4個數值 171

案例226 按數量生成姓名 172

4.2 FREQUENCY：頻率函式 173

案例227 使用FREQUENCY函式統計分數出現的頻率 173

案例228 使用FREQUENCY函式統計不重複值的個數 174

案例229 使用FREQUENCY函式實現去重 174

案例230 合併單元格條件求和 175

4.3 降維函式 176

案例231 使用N函式降維求奇數行的和 176

案例232 使用T函式降維動態求和 177

案例233 使用SUMIF函式進行降維匯總多個工作表 178

案例234 使用SUBTOTAL函式降維實現隔列求和 178

案例235 為何MATCH函式會報錯？ 179

4.4 加權函式 180

案例236 提取多段數字 180

案例237 動態引用每一列單元格中的最後一個值並求和 180

4.5 高級函式徑變案例 181

案例238 把月份數轉為“#年#月”的格式 181

案例239 提取單元格中的數字再相乘 182

案例240 使用SUBSTITUTE函式根據身份證號計算年齡

是幾歲幾個月 183

案例241 VLOOKUP函式的第1參數為數組的用法 184

案例242 使用VLOOKUP函式實現一對多查詢 184

案例243 使用MATCH和MID函式找到單元格中第一個

出現的數字 185

案例244 如何給LOOKUP函式構建參數 186

案例245 把小括弧里的數字相加 186

案例246 提取多段數字並放在多個單元格中 187

案例247 將文字和數字分開 188

案例248 提取多段數字且求和 189

第5章 Excel中常用技巧：提高 數據處理效率 191

5.1 第1個技巧：批量填充 191

5.2 第2個技巧：批量填充上一個單元格中的內容 191

5.3 第3個技巧：把不規範的日期轉為規範的日期 192

5.4 第4個技巧：自動為單元格添加框線 193

5.5 第5個技巧：使單元格中的內容自動適合列寬 194

5.6 第6個技巧：批量快速定義單元格區域名稱 195

5.7 第7個技巧：Tab鍵的妙用 195

5.8 第8個技巧：設定文檔自動保存時間 196

5.9 第9個技巧：從身份證號碼中提取出生日期 196

5.10 第10個技巧：製作斜線表頭 197

5.11 第11個技巧：計算文本表達式 198

5.12 第12個技巧：凍結單元格 199

5.13 第13個技巧：標示單元格中的重複值 200

5.14 第14個技巧：給工作簿加密 201

5.15 第15個技巧：使用快捷鍵Ctrl+\快速對比兩列數據 202

5.16 第16個技巧：使用快捷鍵Alt+=一鍵求和 202

5.17 第17個技巧：快速合併單元格中的內容 203

5.18 第18個技巧：隔列複製數據 203

5.19 第19個技巧：輸入當前的日期和時間 204

5.20 第20個技巧：數值和日期之間的轉換 204

5.21 第21個技巧：妙用快捷鍵F4隔行插入空行 205

5.22 第22個技巧：使用快捷鍵F4切換單元格引用方式 205

5.23 第23個技巧：輸入1顯示“男”；輸入2顯示“女” 206

5.25 第24個技巧：顯示和隱藏Excel的功能區 206

5.26 第25個技巧：跨列居中優於合併單元格 207

5.27 第26個技巧：如何輸入以0開頭的數字 208

5.28 第27個技巧：通過自定義單元格格式快速錄入數據 208

5.29 第28個技巧：快速跳轉到數據列的最後一個單元格 209

5.30 第29個技巧：讓每一頁工作表列印出來都有標題行 209

5.31 第30個技巧：把“*”替換成“×” 210

5.32 第31個技巧：使用快捷鍵Alt+↓快速彈出下拉選單 211

5.33 第32個技巧：按所選內容進行篩選 211

5.34 第33個技巧：使用快捷鍵Ctrl+D複製上一個單元格中的內容 212

5.35 第34個技巧：在多個工作表中批量輸入 212

5.36 第35個技巧：設定單元格區域保護 213

5.37 第36個技巧：設定數值以萬為單位 214

5.38 第37個技巧：如何讓複製的表格列寬不變 215

5.39 第38個技巧：快速打開“選擇性貼上”對話框 216

5.40 第39個技巧：快速添加框線 216

5.41 第40個技巧：快速刪除框線 217

作者簡介

曹明武，網名佛山小老鼠，中國知名ExcelHome論壇的版主，精通Excel技巧，函式，透視表，Vba編程，Excel完美論壇的站長，Excel大型外掛程式完美工具箱的作者，編寫120個Excel常用函式瀏覽70多萬次，下載30多萬次。