加薪不加班：174個Excel函式案例闖職場

《加薪不加班：174個Excel函式案例闖職場》是2019年7月電子工業出版社出版的圖書，作者是曾賢志。

基本介紹

• 書名：加薪不加班：174個Excel函式案例闖職場
• 作者：曾賢志
• ISBN：9787121356957
• 頁數：288頁
• 定價：59.9元
• 出版社：電子工業出版社
• 出版時間：2019年7月
• 開本：16開

內容簡介

本書主要介紹在進行數據處理時需要掌拘糊握的Excel函式，主要內容包括Excel函式入門、處理數據相關的Excel函式、數據匯總函式、時間處理函式、數學函式、字元處理函式、查找與引用函式、高級函式學習套用、定義名稱與宏表函式，並在每個函式後面配以實用案例，讓讀者循序漸進學習Excel函式，並將其套用到數據處理中。

目錄

第1章 菜鳥必學——函式與公式基礎1

1.1 公式與函式的關係1

1.2 公式的編寫與填充技巧2

1.3 函式的組成2

1.4 函式錄入技巧2

1.5 函式編輯技巧3

1.6 常用函式的套用4

1.7 Excel公式運算符5

1.7.1 算術運算符6

1.7.2 比較運算符7

1.7.3 連線運算符9

1.7.4 引用運算符10

1.7.5 運算符優先順序10

1.8 單元格引用11

1.8.1 相對引用11

1.8.2 絕對引用11

1.8.3 混合引用12

1.9 查看公式運算過程13

1.9.1 使洪牛禁用【公式求值】命令13

1.9.2 使用功能鍵F914

1.10 常用函式進階套用15

1.10.1 SUM函式15

1.10.2 AVERAGE函式16

1.10.3 COUNT函式18

1.10.4 MAX函式19

1.10.5 MIN函式20

1.10.6 綜合套用21

第2章 成為高手的必經之路——數組才葛跨技術23

2.1 數組的定義23

2.2 數組維度24

2.3 數組與單元格24

2.3.1 數組與單元格區域的對應關係24

2.3.2 將數組寫入單元格區域25

2.4 數組駝灶殃的分類25

2.5 數組運算的規律與套用26

2.5.1 一維數組與單值的運算26

2.5.2 一維數組與一維數組的運算（方向相同）27

2.5.3 一維數組與一維數組的運算（方向不同）30

2.5.4 一維數組與二維數組的運算31

2.5.5 二維數組與單值的運算32

2.5.6 二維數組與二維數組的運算34

2.6 運算符“*”“+”與數組結合的套用37

2.6.1 數組間的“乘”運算37

2.6.2 數組間的“加”運算39

2.7 數組構造42

2.7.1 用ROW函式構造垂直序列數組42

2.7.2 用COLUMN函式構造水平序列數組44

2.7.3 獲取行、列數：ROWS函式與COLUMNS函式45

第3章 讓電腦幫助人腦——Excel對數據的判斷處理46

3.1 邏輯函式46

3.1.1 IF函式46

3.1.2 IF函式的嵌套套用48

3.1.3 IF函式的引用特性49

3.1.4 AND函式50

3.1.5 OR函式51

3.1.6 NOT函式53

3.2 Excel公式的錯誤值53

3.2.1 常見錯誤值53

3.2.2 識別錯誤值（ISERROR，ISERR，ISNA）54

3.2.3 處理錯誤值函式（IFERROR，IFNA）55

3.2.4 構造利用錯誤值55

3.3 信估夜笑息函式58

3.3.1 IS類信息函式58

3.3.2 N函式59

3.3.3 T函式60

3.3.4 N/T+IF函式60

第4章 數據匯總知多少——常用匯總統計函式套用62

4.1 求和類函式62

4.1.1 計算乘積之和函式：SUMPRODUCT62

4.1.2 單條件求和函式：SUMIF64

4.1.3 多條件求和函式：SUMIFS69

4.2 計數類函式73

4.2.1 計算數字個數函式：COUNT73

4.2.2 非空計數函式：COUNTA74

4.2.3 單條件計數函式：COUNTIF76

4.2.4 多條件計數函式：COUNTIFS78

4.3 平均類函式79

4.3.1 條件求平均函式：AVERAGEIF79

4.3.2 多條件求平均函式：AVERAGEIFS81

4.3.3 修剪平均函式：TRIMMEAN82

4.4 求最大值、最小值的函式84

4.4.1 條件求最大值、最小值84

4.4.2 極大值、極小值函式：LARGE與SMALL85

第5章 時間都去哪兒了——日期時間函式的套用88

5.1 日期函式89

5.1.1 年月日函式：YEAR、碑拳己潤MONTH、DAY89

5.1.2 日期函式：DATE91

5.1.3 系統日期、時間函式：TODAY、NOW92

5.1.4 月份函式：EDATE94

5.1.5 月末函式：EOMONTH95

5.1.6 日期處理函式：DATEDIF96

5.2 星期與工作日函式100

5.2.1 計算酷刪獄騙星期幾函式：WEEKDAY100

5.2.2 計算第幾周函式：WEEKNUM101

5.2.3 計算工作日函式：WORKDAY103

5.2.4 自定義工作日與休息日函式：WORKDAY.INTL105

5.2.5 計算工作日天數函式：NETWORKDAYS108

5.2.6 自定義工作日天數函式：NETWORKDAYS.INTL109

5.3 時間函式113

5.3.1 時分秒函式：HOUR、MINUTE、SECOND113

5.3.2 時間函式：TIME114

第6章 我行我“數”——數學函式117

6.1 取整函式117

6.1.1 向下取整函式：INT117

6.1.2 截尾取數函式：TRUNC119

6.2 求余函式：MOD120

6.3 捨入函式123

6.3.1 四捨五入函式：ROUND123

6.3.2 向下捨入函式：ROUNDDOWN125

6.3.3 向上捨入函式：ROUNDUP126

6.3.4 按倍數向下舍位函式：FLOOR128

6.3.5 按倍數向上進位函式：CEILING129

6.4 隨機函式131

6.4.1 隨機小數函式：RAND131

6.4.2 隨機整數函式：RANDBETWEEN132

第7章 字字珠璣——文本處理函式134

7.1 文本長度計算函式：LEN和LENB134

7.2 文本提取函式137

7.2.1 從左提取字元函式：LEFT、LEFTB137

7.2.2 從右提取字元函式：RIGHT、RIGHTB139

7.2.3 從指定位置提取字元函式：MID、MIDB141

7.3 文本查找函式144

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

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

7.4 文本替換函式150

7.4.1 按址替換函式：REPLACE、REPLACEB150

7.4.2 按值替換函式：SUBSTITUTE152

7.5 字元與數字代碼函式155

7.5.1 將字元轉換為數字代碼的函式：CODE155

7.5.2 將數字代碼轉換為字元的函式：CHAR155

7.6 重複函式：REPT157

7.7 不可見字元清除函式159

7.7.1 清除不可見字元函式：CLEAN160

7.7.2 清除空格字元專用函式：TRIM160

7.8 文本字元連線函式162

7.8.1 文本字元連線函式：CONCATENATE162

7.8.2 另類文本字元連線函式：PHONETIC162

7.9 格式轉換函式：TEXT163

7.10 字母轉換函式167

7.10.1 大寫字母函式：UPPER167

7.10.2 小寫字母函式：LOWER167

7.10.3 將單詞首字母轉換為大寫函式：PROPER168

第8章 數據追捕高手——查找和引用函式170

8.1 查找址函式：MATCH170

8.2 查找值函式175

8.2.1 垂直查找函式：VLOOKUP175

8.2.2 水平查找函式：HLOOKUP179

8.2.3 查找函式：LOOKUP183

8.3 引用函式189

8.3.1 引用函式①：INDEX189

8.3.2 引用函式②：CHOOSE195

8.3.3 引用函式③：INDIRECT198

8.3.4 引用函式④：OFFSET203

8.4 其他函式206

8.4.1 轉置函式：TRANSPOSE207

8.4.2 單元格地址函式：ADDRESS208

8.4.3 超連結函式：HYPERLINK209

第9章 讓函式水平更上一層樓——高級函式213

9.1 獲取單元格信息的函式：CELL213

9.2 矩陣乘積函式：MMULT216

9.3 分類匯總函式：SUBTOTAL220

9.4 頻率函式：FREQUENCY223

第10章 為函式世界添磚加瓦——Excel 2019新增函式227

10.1 條件函式227

10.1.1 多條件判斷函式：IFS227

10.1.2 多條件求最大值函式：MAXIFS228

10.1.3 多條件求最小值函式：MINIFS230

10.2 合併函式231

10.2.1 無分隔設定合併函式：CONCAT231

10.2.2 有分隔設定合併函式：TEXTJOIN234

第11章 函式中的隱形絕招——三維引用技術238

11.1 什麼是三維引用238

11.2 三維引用函式239

11.2.1 三維引用函式：INDIRECT239

11.2.2 三維引用函式：OFFSET241

11.3 支持三維引用的函式244

第12章 啥事兒都能幫上忙——函式與Excel其他功能結合245

12.1 函式與Excel其他功能結合套用245

12.1.1 函式與條件格式245

12.1.2 函式與數據透視表256

12.1.3 函式與數據驗證（數據有效性）257

12.1.4 函式與高級篩選260

12.2 Excel函式與名稱結合套用262

12.2.1 用名稱簡化公式262

12.2.2 名稱與圖片連結263

12.2.3 名稱與合併計算264

12.2.4 名稱與圖表266

12.2.5 名稱與表單控制項268

實例

第1章 菜鳥必學——函式與公式基礎

實例1 求每個銷售員在第1季度的總業績平均業績最高業績最低業績月份數4

實例2 使用SUM函式計算每個銷售員的業績在總業績中的占比6

實例3 兩科成績同時大於或等於90分的學生獎勵100元8

實例4 上半年和下半年業績均為優的員工獎金為10 000元，其他獎金為8000元9

實例5 九九乘法表的製作12

實例6 求家庭收支明細表中的累計餘額15

實例7 計算每位客服人員的獎金是多少17

實例8 計算員工一周的出勤率18

實例9 計算員工上班遲到的分鐘數19

實例10 計算兩科成績同時大於或等於90分的學生的平均分20

實例11 計算每個參賽選手的最後得分21

第2章 成為高手的必經之路——數組技術

實例12 計算採購離合器產品的總金額27

實例13 根據各離合器產品的單價和數量求總金額28

實例14 計算2015—2017年業績等級為全優的人數28

實例15 利用數組製作九九乘法表30

實例16 計算各科成績低於平均分的學生人數32

實例17 計算業績大於或等於450萬元的銷售員的補助總金額33

實例18 統計兩年各項產品的銷售情況同時合格的人數34

實例19 綜合套用：根據各農產品不同等級對應的單價和數量計算出金額36

實例20 求連續3個月銷售電腦數量大於或等於50台的人數37

實例21 求部門是銷售部且工資大於或等於10 000元的員工的總工資38

實例22 求銷售電腦數量至少有1個月大於或等於80台的達標人數40

實例23 求部門是銷售部或者工資大於或等於10 000元的員工的總工資41

實例24 求每個員工上半年獲得最高工資的月份43

實例25 求各班學生的最高分44

第3章 讓電腦幫助人腦——Excel對數據的判斷處理

實例26 根據價格表計算出採購清單的採購金額47

實例27 判斷兩科成績同時大於或等於90分的學生為優秀（IF函式）49

實例28 根據選擇的起始月份和終止月份匯總相應的銷售數量49

實例29 判斷兩科成績同時大於或等於90分的學生為優秀（AND函式）51

實例30 判斷三科成績中只要有一科成績大於或等於90分的學生為合格52

實例31 計算採購清單中的商品金額54

實例32 統計因故沒有考試和考試不及格的人數56

實例33 求考試及格的人數57

實例34 計算1月到4月業績大於或等於50萬元的平均業績58

實例35 計算成績大於或等於80分以上的學生的平均分60

第4章 數據匯總知多少——常用匯總統計函式套用

實例36 計算各產品的總金額63

實例37 求各種花卉的銷售總數量64

實例38 求花卉的銷售數量大於或等於700件的總數量（SUMIF函式忽略第3參數的

套用）65

實例39 求競賽中國中和高中最高分的總和分別是多少（SUMIF函式第2參數通配符的

套用）66

實例40 求某競賽中國中和高中最高分的總和分別是多少（數據結構有變化）68

實例41 求二級蘋果的總數量（SUMIFS函式的普通套用）70

實例42 求各等級、各品名的水果的總數量71

實例43 統計廣東科技公司的總銷售額（萬元）（通配符套用）72

實例44 統計出上、下半年均有銷售的蔬菜品種數73

實例45 統計每個人的達標率75

實例46 統計成績為85~95分的學生人數76

實例47 統計出每個人抽獎抽到的獎品種類是多少77

實例48 統計各店男、女服裝的銷售數量分別是多少78

實例49 統計出各店的平均銷售額80

實例50 統計每個參賽選手的最後得分81

實例51 統計每個參賽選手的最後得分（棄權不計）83

實例52 求各班的最高分和最低分84

實例53 統計每個參賽選手的最後得分（升級版）86

第5章 時間都去哪兒了——日期時間函式的套用

實例54 根據入職日期計算出員工工齡，入職時是上半年還是下半年，是平年還是閏年89

實例55 計算指定年份的天數92

實例56 計算每個人的年齡93

實例57 計算挖掘機設備的租用到期時間94

實例58 計算出指定日期所在月份的天數95

實例59 判斷員工是否已過試用期（試用期為3個月）97

實例60 計算員工工齡98

實例61 根據出生日期對未來近10天內即將過生日的員工做提醒99

實例62 根據培訓日期計算出總的培訓費用100

實例63 計算一個月內所有日期屬於當月的第幾周102

實例64 根據工作日推算證件辦理完成的最後日期103

實例65 計算證件的領取日期104

實例66 根據辦理各事項所需工作日推算完成日期106

實例67 根據超市收貨日期計算不同類別供貨商的結款日期107

實例68 計算指定月份的員工出勤天數108

實例69 計算2017年指定月份的工作日天數（不含節假日）110

實例70 計算2017年全年每月的工作日天數111

實例71 根據上課時長計算出各講師的課酬113

實例72 根據開始時間和通話時間長度（分鐘）計算結束時間114

案例73 根據月份計算對應的季度118

第6章 我行我“數”——數學函式

實例74 計算某公司2017年每月業績占所在季度的百分比118

實例75 截尾保留金額小數點後兩位，然後求和119

實例76 製作循環序列數121

實例77 根據上下班時間計算工作時間122

實例78 將工資金額四捨五入到小數點後1位123

實例79 提取金額中十位、個位部分的值125

實例80 根據各講師的講課時間計算培訓費用126

實例81 根據分數判斷其屬於哪一個等級128

實例82 根據課程時長計算費用129

實例83 根據月份計算對應的季度130

實例84 生成8:00—12:00之間的隨機時間（RAND函式）131

實例85 生成8:00—12:00之間的隨機時間（RANDBETWEEN函式）133

第7章 字字珠璣——文本處理函式

實例86 計算字元串中單、雙位元組各自的字元長度135

實例87 根據月份計算對應的季度136

實例88 提取字元串中的不定長姓名137

實例89 所有成績大於或等於80分的學生為通過，否則為空138

實例90 提取字元串中的數字140

實例91 根據身份證號判斷性別140

實例92 根據分數判斷對應的等級142

實例93 計算各職稱名單對應的人數143

實例94 提取職員信息中的部門信息145

實例95 提取編號中的部分數據（尺碼提取）146

實例96 根據信息判斷學生屬於哪個階段147

實例97 提取文本字元串中的英文名149

實例98 隱藏地址中的部分信息150

實例99 給品名的英文單詞加括弧151

實例100 提取路徑下的檔案名稱152

實例101 提取字元串中多個數量的最大值154

實例102 判斷分數，及格打“√”，不及格打“×”156

實例103 將字元組別重新排序156

實例104 根據分數在單元格中製作條形圖158

實例105 根據項目的開始日期和結束日期製作進度圖158

實例106 清除文本字元串中的換行符160

實例107 以分隔設定提取多條不同長度的數據161

實例108 快速連線單元格區域數據，使其成為文本字元串162

實例109 根據身份證號碼提取性別164

實例110 使用TEXT函式判斷分數的等級165

實例111 給QQ號碼的每個數字之間加分隔設定166

實例112 判斷單詞首字母是否大寫167

實例113 將英文語句的首字母設為大寫，其他為小寫168

實例114 將中英文對譯中的英文部分轉換成句首字母大寫169

第8章 數據追捕高手——查找和引用函式

實例115 根據業績查詢對應等級171

實例116 根據業績等級返回對應獎金172

實例117 計算每個人當前業績距上一個等級還差多少萬元173

實例118 計算不重複的部門數量174

實例119 根據蔬選單價表計算蔬菜的金額176

實例120 根據提成百分比和業績計算提成金額177

實例121 多條件查詢177

實例122 查找指定學生的各科成績179

實例123 根據購買課程類別和購買金額計算折扣後金額180

實例124 計算指定科目所有分數的平均分182

實例125 根據分數判斷等級（LOOKUP向量垂直查詢）183

實例126 根據分數判斷等級（LOOKUP向量水平查詢）184

實例127 根據書籍編號查詢書籍名187

實例128 提取字元串中最後的數字部分188

實例129 根據分數判斷對應等級191

實例130 製作二維查詢系統192

實例131 製作模糊查詢系統193

實例132 根據課程編號返回課程類別195

實例133 製作工資條196

實例134 重組查詢區域後的VLOOKUP查詢197

實例135 將單列數據轉為多行多列數據199

實例136 將多行多列數據轉換為單列數據200

實例137 將二維數據錶轉換為一維數據表201

實例138 統計每個人各年的總業績204

實例139 統計各個產品最後兩天的總銷量205

實例140 整理轉置數據207

實例141 製作員工請假銷假監控系統210

第9章 讓函式水平更上一層樓——高級函式

實例142 以活動單元格為條件製作動態查詢系統214

實例143 統計成績表的最高總分及其對應的姓名217

實例144 統計出任意一科及以上成績大於或等於90分的人數219

實例145 自動編寫序號220

實例146 比賽評分計算表222

實例147 統計差、中、良、優四個等級各自的人數223

實例148 提取相同日期中的第一條記錄224

實例149 計算球賽的最多連勝次數225

第10章 為函式世界添磚加瓦——Excel 2019新增函式

實例150 根據成績判斷對應的等級228

實例151 多條件求最高銷售金額229

實例152 多條件求最低價格230

實例153 合併各月份的業績數據232

實例154 提取各姓名對應的分數233

實例155 根據部門提取合併對應的人員名單234

實例156 將文本字元串中的數字進行排序235

第11章 函式中的隱形絕招——三維引用技術

實例157 統計各科成績總分的最高分239

實例158 統計各月銷量占總銷量的百分比240

實例159 將訂單按數量進行拆分241

實例160 統計各個日期最高平均數量及對應城市243

第12章 啥事兒都能幫上忙——函式與Excel其他功能結合

實例161 標記出第4~6名的分數246

實例162 隔日標記日期記錄247

實例163 用顏色動態標記活動單元格所在行的數據248

實例164 用數據條標記未來3天即將到期的記錄251

實例165 動態標記日曆253

實例166 在數據透視表中根據匯總金額計算提成金額256

實例167 利用數據驗證製作二級下拉選單257

實例168 利用數據驗證驗證身份證號259

實例169 篩選包含“變速箱”且全年總銷量大於或等於350件的商品260

實例170 統計最高總分及對應姓名262

實例171 動態引用查詢系統的圖片263

實例172 動態獲取合併計算的數據源264

實例173 製作動態圖表266

實例174 使用表單控制項製作動態圖表268,第1章 菜鳥必學——函式與公式基礎1

1.1 公式與函式的關係1

1.2 公式的編寫與填充技巧2

1.3 函式的組成2

1.4 函式錄入技巧2

1.5 函式編輯技巧3

1.6 常用函式的套用4

1.7 Excel公式運算符5

1.7.1 算術運算符6

1.7.2 比較運算符7

1.7.3 連線運算符9

1.7.4 引用運算符10

1.7.5 運算符優先順序10

1.8 單元格引用11

1.8.1 相對引用11

1.8.2 絕對引用11

1.8.3 混合引用12

1.9 查看公式運算過程13

1.9.1 使用【公式求值】命令13

1.9.2 使用功能鍵F914

1.10 常用函式進階套用15

1.10.1 SUM函式15

1.10.2 AVERAGE函式16

1.10.3 COUNT函式18

1.10.4 MAX函式19

1.10.5 MIN函式20

1.10.6 綜合套用21

第2章 成為高手的必經之路——數組技術23

2.1 數組的定義23

2.2 數組維度24

2.3 數組與單元格24

2.3.1 數組與單元格區域的對應關係24

2.3.2 將數組寫入單元格區域25

2.4 數組的分類25

2.5 數組運算的規律與套用26

2.5.1 一維數組與單值的運算26

2.5.2 一維數組與一維數組的運算（方向相同）27

2.5.3 一維數組與一維數組的運算（方向不同）30

2.5.4 一維數組與二維數組的運算31

2.5.5 二維數組與單值的運算32

2.5.6 二維數組與二維數組的運算34

2.6 運算符“*”“+”與數組結合的套用37

2.6.1 數組間的“乘”運算37

2.6.2 數組間的“加”運算39

2.7 數組構造42

2.7.1 用ROW函式構造垂直序列數組42

2.7.2 用COLUMN函式構造水平序列數組44

2.7.3 獲取行、列數：ROWS函式與COLUMNS函式45

第3章 讓電腦幫助人腦——Excel對數據的判斷處理46

3.1 邏輯函式46

3.1.1 IF函式46

3.1.2 IF函式的嵌套套用48

3.1.3 IF函式的引用特性49

3.1.4 AND函式50

3.1.5 OR函式51

3.1.6 NOT函式53

3.2 Excel公式的錯誤值53

3.2.1 常見錯誤值53

3.2.2 識別錯誤值（ISERROR，ISERR，ISNA）54

3.2.3 處理錯誤值函式（IFERROR，IFNA）55

3.2.4 構造利用錯誤值55

3.3 信息函式58

3.3.1 IS類信息函式58

3.3.2 N函式59

3.3.3 T函式60

3.3.4 N/T+IF函式60

第4章 數據匯總知多少——常用匯總統計函式套用62

4.1 求和類函式62

4.1.1 計算乘積之和函式：SUMPRODUCT62

4.1.2 單條件求和函式：SUMIF64

4.1.3 多條件求和函式：SUMIFS69

4.2 計數類函式73

4.2.1 計算數字個數函式：COUNT73

4.2.2 非空計數函式：COUNTA74

4.2.3 單條件計數函式：COUNTIF76

4.2.4 多條件計數函式：COUNTIFS78

4.3 平均類函式79

4.3.1 條件求平均函式：AVERAGEIF79

4.3.2 多條件求平均函式：AVERAGEIFS81

4.3.3 修剪平均函式：TRIMMEAN82

4.4 求最大值、最小值的函式84

4.4.1 條件求最大值、最小值84

4.4.2 極大值、極小值函式：LARGE與SMALL85

第5章 時間都去哪兒了——日期時間函式的套用88

5.1 日期函式89

5.1.1 年月日函式：YEAR、MONTH、DAY89

5.1.2 日期函式：DATE91

5.1.3 系統日期、時間函式：TODAY、NOW92

5.1.4 月份函式：EDATE94

5.1.5 月末函式：EOMONTH95

5.1.6 日期處理函式：DATEDIF96

5.2 星期與工作日函式100

5.2.1 計算星期幾函式：WEEKDAY100

5.2.2 計算第幾周函式：WEEKNUM101

5.2.3 計算工作日函式：WORKDAY103

5.2.4 自定義工作日與休息日函式：WORKDAY.INTL105

5.2.5 計算工作日天數函式：NETWORKDAYS108

5.2.6 自定義工作日天數函式：NETWORKDAYS.INTL109

5.3 時間函式113

5.3.1 時分秒函式：HOUR、MINUTE、SECOND113

5.3.2 時間函式：TIME114

第6章 我行我“數”——數學函式117

6.1 取整函式117

6.1.1 向下取整函式：INT117

6.1.2 截尾取數函式：TRUNC119

6.2 求余函式：MOD120

6.3 捨入函式123

6.3.1 四捨五入函式：ROUND123

6.3.2 向下捨入函式：ROUNDDOWN125

6.3.3 向上捨入函式：ROUNDUP126

6.3.4 按倍數向下舍位函式：FLOOR128

6.3.5 按倍數向上進位函式：CEILING129

6.4 隨機函式131

6.4.1 隨機小數函式：RAND131

6.4.2 隨機整數函式：RANDBETWEEN132

第7章 字字珠璣——文本處理函式134

7.1 文本長度計算函式：LEN和LENB134

7.2 文本提取函式137

7.2.1 從左提取字元函式：LEFT、LEFTB137

7.2.2 從右提取字元函式：RIGHT、RIGHTB139

7.2.3 從指定位置提取字元函式：MID、MIDB141

7.3 文本查找函式144

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

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

7.4 文本替換函式150

7.4.1 按址替換函式：REPLACE、REPLACEB150

7.4.2 按值替換函式：SUBSTITUTE152

7.5 字元與數字代碼函式155

7.5.1 將字元轉換為數字代碼的函式：CODE155

7.5.2 將數字代碼轉換為字元的函式：CHAR155

7.6 重複函式：REPT157

7.7 不可見字元清除函式159

7.7.1 清除不可見字元函式：CLEAN160

7.7.2 清除空格字元專用函式：TRIM160

7.8 文本字元連線函式162

7.8.1 文本字元連線函式：CONCATENATE162

7.8.2 另類文本字元連線函式：PHONETIC162

7.9 格式轉換函式：TEXT163

7.10 字母轉換函式167

7.10.1 大寫字母函式：UPPER167

7.10.2 小寫字母函式：LOWER167

7.10.3 將單詞首字母轉換為大寫函式：PROPER168

第8章 數據追捕高手——查找和引用函式170

8.1 查找址函式：MATCH170

8.2 查找值函式175

8.2.1 垂直查找函式：VLOOKUP175

8.2.2 水平查找函式：HLOOKUP179

8.2.3 查找函式：LOOKUP183

8.3 引用函式189

8.3.1 引用函式①：INDEX189

8.3.2 引用函式②：CHOOSE195

8.3.3 引用函式③：INDIRECT198

8.3.4 引用函式④：OFFSET203

8.4 其他函式206

8.4.1 轉置函式：TRANSPOSE207

8.4.2 單元格地址函式：ADDRESS208

8.4.3 超連結函式：HYPERLINK209

第9章 讓函式水平更上一層樓——高級函式213

9.1 獲取單元格信息的函式：CELL213

9.2 矩陣乘積函式：MMULT216

9.3 分類匯總函式：SUBTOTAL220

9.4 頻率函式：FREQUENCY223

第10章 為函式世界添磚加瓦——Excel 2019新增函式227

10.1 條件函式227

10.1.1 多條件判斷函式：IFS227

10.1.2 多條件求最大值函式：MAXIFS228

10.1.3 多條件求最小值函式：MINIFS230

10.2 合併函式231

10.2.1 無分隔設定合併函式：CONCAT231

10.2.2 有分隔設定合併函式：TEXTJOIN234

第11章 函式中的隱形絕招——三維引用技術238

11.1 什麼是三維引用238

11.2 三維引用函式239

11.2.1 三維引用函式：INDIRECT239

11.2.2 三維引用函式：OFFSET241

11.3 支持三維引用的函式244

第12章 啥事兒都能幫上忙——函式與Excel其他功能結合245

12.1 函式與Excel其他功能結合套用245

12.1.1 函式與條件格式245

12.1.2 函式與數據透視表256

12.1.3 函式與數據驗證（數據有效性）257

12.1.4 函式與高級篩選260

12.2 Excel函式與名稱結合套用262

12.2.1 用名稱簡化公式262

12.2.2 名稱與圖片連結263

12.2.3 名稱與合併計算264

12.2.4 名稱與圖表266

12.2.5 名稱與表單控制項268

實例

第1章 菜鳥必學——函式與公式基礎

實例1 求每個銷售員在第1季度的總業績平均業績最高業績最低業績月份數4

實例2 使用SUM函式計算每個銷售員的業績在總業績中的占比6

實例3 兩科成績同時大於或等於90分的學生獎勵100元8

實例4 上半年和下半年業績均為優的員工獎金為10 000元，其他獎金為8000元9

實例5 九九乘法表的製作12

實例6 求家庭收支明細表中的累計餘額15

實例7 計算每位客服人員的獎金是多少17

實例8 計算員工一周的出勤率18

實例9 計算員工上班遲到的分鐘數19

實例10 計算兩科成績同時大於或等於90分的學生的平均分20

實例11 計算每個參賽選手的最後得分21

第2章 成為高手的必經之路——數組技術

實例12 計算採購離合器產品的總金額27

實例13 根據各離合器產品的單價和數量求總金額28

實例14 計算2015—2017年業績等級為全優的人數28

實例15 利用數組製作九九乘法表30

實例16 計算各科成績低於平均分的學生人數32

實例17 計算業績大於或等於450萬元的銷售員的補助總金額33

實例18 統計兩年各項產品的銷售情況同時合格的人數34

實例19 綜合套用：根據各農產品不同等級對應的單價和數量計算出金額36

實例20 求連續3個月銷售電腦數量大於或等於50台的人數37

實例21 求部門是銷售部且工資大於或等於10 000元的員工的總工資38

實例22 求銷售電腦數量至少有1個月大於或等於80台的達標人數40

實例23 求部門是銷售部或者工資大於或等於10 000元的員工的總工資41

實例24 求每個員工上半年獲得最高工資的月份43

實例25 求各班學生的最高分44

第3章 讓電腦幫助人腦——Excel對數據的判斷處理

實例26 根據價格表計算出採購清單的採購金額47

實例27 判斷兩科成績同時大於或等於90分的學生為優秀（IF函式）49

實例28 根據選擇的起始月份和終止月份匯總相應的銷售數量49

實例29 判斷兩科成績同時大於或等於90分的學生為優秀（AND函式）51

實例30 判斷三科成績中只要有一科成績大於或等於90分的學生為合格52

實例31 計算採購清單中的商品金額54

實例32 統計因故沒有考試和考試不及格的人數56

實例33 求考試及格的人數57

實例34 計算1月到4月業績大於或等於50萬元的平均業績58

實例35 計算成績大於或等於80分以上的學生的平均分60

第4章 數據匯總知多少——常用匯總統計函式套用

實例36 計算各產品的總金額63

實例37 求各種花卉的銷售總數量64

實例38 求花卉的銷售數量大於或等於700件的總數量（SUMIF函式忽略第3參數的

套用）65

實例39 求競賽中國中和高中最高分的總和分別是多少（SUMIF函式第2參數通配符的

套用）66

實例40 求某競賽中國中和高中最高分的總和分別是多少（數據結構有變化）68

實例41 求二級蘋果的總數量（SUMIFS函式的普通套用）70

實例42 求各等級、各品名的水果的總數量71

實例43 統計廣東科技公司的總銷售額（萬元）（通配符套用）72

實例44 統計出上、下半年均有銷售的蔬菜品種數73

實例45 統計每個人的達標率75

實例46 統計成績為85~95分的學生人數76

實例47 統計出每個人抽獎抽到的獎品種類是多少77

實例48 統計各店男、女服裝的銷售數量分別是多少78

實例49 統計出各店的平均銷售額80

實例50 統計每個參賽選手的最後得分81

實例51 統計每個參賽選手的最後得分（棄權不計）83

實例52 求各班的最高分和最低分84

實例53 統計每個參賽選手的最後得分（升級版）86

第5章 時間都去哪兒了——日期時間函式的套用

實例54 根據入職日期計算出員工工齡，入職時是上半年還是下半年，是平年還是閏年89

實例55 計算指定年份的天數92

實例56 計算每個人的年齡93

實例57 計算挖掘機設備的租用到期時間94

實例58 計算出指定日期所在月份的天數95

實例59 判斷員工是否已過試用期（試用期為3個月）97

實例60 計算員工工齡98

實例61 根據出生日期對未來近10天內即將過生日的員工做提醒99

實例62 根據培訓日期計算出總的培訓費用100

實例63 計算一個月內所有日期屬於當月的第幾周102

實例64 根據工作日推算證件辦理完成的最後日期103

實例65 計算證件的領取日期104

實例66 根據辦理各事項所需工作日推算完成日期106

實例67 根據超市收貨日期計算不同類別供貨商的結款日期107

實例68 計算指定月份的員工出勤天數108

實例69 計算2017年指定月份的工作日天數（不含節假日）110

實例70 計算2017年全年每月的工作日天數111

實例71 根據上課時長計算出各講師的課酬113

實例72 根據開始時間和通話時間長度（分鐘）計算結束時間114

案例73 根據月份計算對應的季度118

第6章 我行我“數”——數學函式

實例74 計算某公司2017年每月業績占所在季度的百分比118

實例75 截尾保留金額小數點後兩位，然後求和119

實例76 製作循環序列數121

實例77 根據上下班時間計算工作時間122

實例78 將工資金額四捨五入到小數點後1位123

實例79 提取金額中十位、個位部分的值125

實例80 根據各講師的講課時間計算培訓費用126

實例81 根據分數判斷其屬於哪一個等級128

實例82 根據課程時長計算費用129

實例83 根據月份計算對應的季度130

實例84 生成8:00—12:00之間的隨機時間（RAND函式）131

實例85 生成8:00—12:00之間的隨機時間（RANDBETWEEN函式）133

第7章 字字珠璣——文本處理函式

實例86 計算字元串中單、雙位元組各自的字元長度135

實例87 根據月份計算對應的季度136

實例88 提取字元串中的不定長姓名137

實例89 所有成績大於或等於80分的學生為通過，否則為空138

實例90 提取字元串中的數字140

實例91 根據身份證號判斷性別140

實例92 根據分數判斷對應的等級142

實例93 計算各職稱名單對應的人數143

實例94 提取職員信息中的部門信息145

實例95 提取編號中的部分數據（尺碼提取）146

實例96 根據信息判斷學生屬於哪個階段147

實例97 提取文本字元串中的英文名149

實例98 隱藏地址中的部分信息150

實例99 給品名的英文單詞加括弧151

實例100 提取路徑下的檔案名稱152

實例101 提取字元串中多個數量的最大值154

實例102 判斷分數，及格打“√”，不及格打“×”156

實例103 將字元組別重新排序156

實例104 根據分數在單元格中製作條形圖158

實例105 根據項目的開始日期和結束日期製作進度圖158

實例106 清除文本字元串中的換行符160

實例107 以分隔設定提取多條不同長度的數據161

實例108 快速連線單元格區域數據，使其成為文本字元串162

實例109 根據身份證號碼提取性別164

實例110 使用TEXT函式判斷分數的等級165

實例111 給QQ號碼的每個數字之間加分隔設定166

實例112 判斷單詞首字母是否大寫167

實例113 將英文語句的首字母設為大寫，其他為小寫168

實例114 將中英文對譯中的英文部分轉換成句首字母大寫169

第8章 數據追捕高手——查找和引用函式

實例115 根據業績查詢對應等級171

實例116 根據業績等級返回對應獎金172

實例117 計算每個人當前業績距上一個等級還差多少萬元173

實例118 計算不重複的部門數量174

實例119 根據蔬選單價表計算蔬菜的金額176

實例120 根據提成百分比和業績計算提成金額177

實例121 多條件查詢177

實例122 查找指定學生的各科成績179

實例123 根據購買課程類別和購買金額計算折扣後金額180

實例124 計算指定科目所有分數的平均分182

實例125 根據分數判斷等級（LOOKUP向量垂直查詢）183

實例126 根據分數判斷等級（LOOKUP向量水平查詢）184

實例127 根據書籍編號查詢書籍名187

實例128 提取字元串中最後的數字部分188

實例129 根據分數判斷對應等級191

實例130 製作二維查詢系統192

實例131 製作模糊查詢系統193

實例132 根據課程編號返回課程類別195

實例133 製作工資條196

實例134 重組查詢區域後的VLOOKUP查詢197

實例135 將單列數據轉為多行多列數據199

實例136 將多行多列數據轉換為單列數據200

實例137 將二維數據錶轉換為一維數據表201

實例138 統計每個人各年的總業績204

實例139 統計各個產品最後兩天的總銷量205

實例140 整理轉置數據207

實例141 製作員工請假銷假監控系統210

第9章 讓函式水平更上一層樓——高級函式

實例142 以活動單元格為條件製作動態查詢系統214

實例143 統計成績表的最高總分及其對應的姓名217

實例144 統計出任意一科及以上成績大於或等於90分的人數219

實例145 自動編寫序號220

實例146 比賽評分計算表222

實例147 統計差、中、良、優四個等級各自的人數223

實例148 提取相同日期中的第一條記錄224

實例149 計算球賽的最多連勝次數225

第10章 為函式世界添磚加瓦——Excel 2019新增函式

實例150 根據成績判斷對應的等級228

實例151 多條件求最高銷售金額229

實例152 多條件求最低價格230

實例153 合併各月份的業績數據232

實例154 提取各姓名對應的分數233

實例155 根據部門提取合併對應的人員名單234

實例156 將文本字元串中的數字進行排序235

第11章 函式中的隱形絕招——三維引用技術

實例157 統計各科成績總分的最高分239

實例158 統計各月銷量占總銷量的百分比240

實例159 將訂單按數量進行拆分241

實例160 統計各個日期最高平均數量及對應城市243

第12章 啥事兒都能幫上忙——函式與Excel其他功能結合

實例161 標記出第4~6名的分數246

實例162 隔日標記日期記錄247

實例163 用顏色動態標記活動單元格所在行的數據248

實例164 用數據條標記未來3天即將到期的記錄251

實例165 動態標記日曆253

實例166 在數據透視表中根據匯總金額計算提成金額256

實例167 利用數據驗證製作二級下拉選單257

實例168 利用數據驗證驗證身份證號259

實例169 篩選包含“變速箱”且全年總銷量大於或等於350件的商品260

實例170 統計最高總分及對應姓名262

實例171 動態引用查詢系統的圖片263

實例172 動態獲取合併計算的數據源264

實例173 製作動態圖表266

實例174 使用表單控制項製作動態圖表268

3.1.5 OR函式51

3.1.6 NOT函式53

3.2 Excel公式的錯誤值53

3.2.1 常見錯誤值53

3.2.2 識別錯誤值（ISERROR，ISERR，ISNA）54

3.2.3 處理錯誤值函式（IFERROR，IFNA）55

3.2.4 構造利用錯誤值55

3.3 信息函式58

3.3.1 IS類信息函式58

3.3.2 N函式59

3.3.3 T函式60

3.3.4 N/T+IF函式60

第4章 數據匯總知多少——常用匯總統計函式套用62

4.1 求和類函式62

4.1.1 計算乘積之和函式：SUMPRODUCT62

4.1.2 單條件求和函式：SUMIF64

4.1.3 多條件求和函式：SUMIFS69

4.2 計數類函式73

4.2.1 計算數字個數函式：COUNT73

4.2.2 非空計數函式：COUNTA74

4.2.3 單條件計數函式：COUNTIF76

4.2.4 多條件計數函式：COUNTIFS78

4.3 平均類函式79

4.3.1 條件求平均函式：AVERAGEIF79

4.3.2 多條件求平均函式：AVERAGEIFS81

4.3.3 修剪平均函式：TRIMMEAN82

4.4 求最大值、最小值的函式84

4.4.1 條件求最大值、最小值84

4.4.2 極大值、極小值函式：LARGE與SMALL85

第5章 時間都去哪兒了——日期時間函式的套用88

5.1 日期函式89

5.1.1 年月日函式：YEAR、MONTH、DAY89

5.1.2 日期函式：DATE91

5.1.3 系統日期、時間函式：TODAY、NOW92

5.1.4 月份函式：EDATE94

5.1.5 月末函式：EOMONTH95

5.1.6 日期處理函式：DATEDIF96

5.2 星期與工作日函式100

5.2.1 計算星期幾函式：WEEKDAY100

5.2.2 計算第幾周函式：WEEKNUM101

5.2.3 計算工作日函式：WORKDAY103

5.2.4 自定義工作日與休息日函式：WORKDAY.INTL105

5.2.5 計算工作日天數函式：NETWORKDAYS108

5.2.6 自定義工作日天數函式：NETWORKDAYS.INTL109

5.3 時間函式113

5.3.1 時分秒函式：HOUR、MINUTE、SECOND113

5.3.2 時間函式：TIME114

第6章 我行我“數”——數學函式117

6.1 取整函式117

6.1.1 向下取整函式：INT117

6.1.2 截尾取數函式：TRUNC119

6.2 求余函式：MOD120

6.3 捨入函式123

6.3.1 四捨五入函式：ROUND123

6.3.2 向下捨入函式：ROUNDDOWN125

6.3.3 向上捨入函式：ROUNDUP126

6.3.4 按倍數向下舍位函式：FLOOR128

6.3.5 按倍數向上進位函式：CEILING129

6.4 隨機函式131

6.4.1 隨機小數函式：RAND131

6.4.2 隨機整數函式：RANDBETWEEN132

第7章 字字珠璣——文本處理函式134

7.1 文本長度計算函式：LEN和LENB134

7.2 文本提取函式137

7.2.1 從左提取字元函式：LEFT、LEFTB137

7.2.2 從右提取字元函式：RIGHT、RIGHTB139

7.2.3 從指定位置提取字元函式：MID、MIDB141

7.3 文本查找函式144

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

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

7.4 文本替換函式150

7.4.1 按址替換函式：REPLACE、REPLACEB150

7.4.2 按值替換函式：SUBSTITUTE152

7.5 字元與數字代碼函式155

7.5.1 將字元轉換為數字代碼的函式：CODE155

7.5.2 將數字代碼轉換為字元的函式：CHAR155

7.6 重複函式：REPT157

7.7 不可見字元清除函式159

7.7.1 清除不可見字元函式：CLEAN160

7.7.2 清除空格字元專用函式：TRIM160

7.8 文本字元連線函式162

7.8.1 文本字元連線函式：CONCATENATE162

7.8.2 另類文本字元連線函式：PHONETIC162

7.9 格式轉換函式：TEXT163

7.10 字母轉換函式167

7.10.1 大寫字母函式：UPPER167

7.10.2 小寫字母函式：LOWER167

7.10.3 將單詞首字母轉換為大寫函式：PROPER168

第8章 數據追捕高手——查找和引用函式170

8.1 查找址函式：MATCH170

8.2 查找值函式175

8.2.1 垂直查找函式：VLOOKUP175

8.2.2 水平查找函式：HLOOKUP179

8.2.3 查找函式：LOOKUP183

8.3 引用函式189

8.3.1 引用函式①：INDEX189

8.3.2 引用函式②：CHOOSE195

8.3.3 引用函式③：INDIRECT198

8.3.4 引用函式④：OFFSET203

8.4 其他函式206

8.4.1 轉置函式：TRANSPOSE207

8.4.2 單元格地址函式：ADDRESS208

8.4.3 超連結函式：HYPERLINK209

第9章 讓函式水平更上一層樓——高級函式213

9.1 獲取單元格信息的函式：CELL213

9.2 矩陣乘積函式：MMULT216

9.3 分類匯總函式：SUBTOTAL220

9.4 頻率函式：FREQUENCY223

第10章 為函式世界添磚加瓦——Excel 2019新增函式227

10.1 條件函式227

10.1.1 多條件判斷函式：IFS227

10.1.2 多條件求最大值函式：MAXIFS228

10.1.3 多條件求最小值函式：MINIFS230

10.2 合併函式231

10.2.1 無分隔設定合併函式：CONCAT231

10.2.2 有分隔設定合併函式：TEXTJOIN234

第11章 函式中的隱形絕招——三維引用技術238

11.1 什麼是三維引用238

11.2 三維引用函式239

11.2.1 三維引用函式：INDIRECT239

11.2.2 三維引用函式：OFFSET241

11.3 支持三維引用的函式244

第12章 啥事兒都能幫上忙——函式與Excel其他功能結合245

12.1 函式與Excel其他功能結合套用245

12.1.1 函式與條件格式245

12.1.2 函式與數據透視表256

12.1.3 函式與數據驗證（數據有效性）257

12.1.4 函式與高級篩選260

12.2 Excel函式與名稱結合套用262

12.2.1 用名稱簡化公式262

12.2.2 名稱與圖片連結263

12.2.3 名稱與合併計算264

12.2.4 名稱與圖表266

12.2.5 名稱與表單控制項268

實例

第1章 菜鳥必學——函式與公式基礎

實例1 求每個銷售員在第1季度的總業績平均業績最高業績最低業績月份數4

實例2 使用SUM函式計算每個銷售員的業績在總業績中的占比6

實例3 兩科成績同時大於或等於90分的學生獎勵100元8

實例4 上半年和下半年業績均為優的員工獎金為10 000元，其他獎金為8000元9

實例5 九九乘法表的製作12

實例6 求家庭收支明細表中的累計餘額15

實例7 計算每位客服人員的獎金是多少17

實例8 計算員工一周的出勤率18

實例9 計算員工上班遲到的分鐘數19

實例10 計算兩科成績同時大於或等於90分的學生的平均分20

實例11 計算每個參賽選手的最後得分21

第2章 成為高手的必經之路——數組技術

實例12 計算採購離合器產品的總金額27

實例13 根據各離合器產品的單價和數量求總金額28

實例14 計算2015—2017年業績等級為全優的人數28

實例15 利用數組製作九九乘法表30

實例16 計算各科成績低於平均分的學生人數32

實例17 計算業績大於或等於450萬元的銷售員的補助總金額33

實例18 統計兩年各項產品的銷售情況同時合格的人數34

實例19 綜合套用：根據各農產品不同等級對應的單價和數量計算出金額36

實例20 求連續3個月銷售電腦數量大於或等於50台的人數37

實例21 求部門是銷售部且工資大於或等於10 000元的員工的總工資38

實例22 求銷售電腦數量至少有1個月大於或等於80台的達標人數40

實例23 求部門是銷售部或者工資大於或等於10 000元的員工的總工資41

實例24 求每個員工上半年獲得最高工資的月份43

實例25 求各班學生的最高分44

第3章 讓電腦幫助人腦——Excel對數據的判斷處理

實例26 根據價格表計算出採購清單的採購金額47

實例27 判斷兩科成績同時大於或等於90分的學生為優秀（IF函式）49

實例28 根據選擇的起始月份和終止月份匯總相應的銷售數量49

實例29 判斷兩科成績同時大於或等於90分的學生為優秀（AND函式）51

實例30 判斷三科成績中只要有一科成績大於或等於90分的學生為合格52

實例31 計算採購清單中的商品金額54

實例32 統計因故沒有考試和考試不及格的人數56

實例33 求考試及格的人數57

實例34 計算1月到4月業績大於或等於50萬元的平均業績58

實例35 計算成績大於或等於80分以上的學生的平均分60

第4章 數據匯總知多少——常用匯總統計函式套用

實例36 計算各產品的總金額63

實例37 求各種花卉的銷售總數量64

實例38 求花卉的銷售數量大於或等於700件的總數量（SUMIF函式忽略第3參數的

套用）65

實例39 求競賽中國中和高中最高分的總和分別是多少（SUMIF函式第2參數通配符的

套用）66

實例40 求某競賽中國中和高中最高分的總和分別是多少（數據結構有變化）68

實例41 求二級蘋果的總數量（SUMIFS函式的普通套用）70

實例42 求各等級、各品名的水果的總數量71

實例43 統計廣東科技公司的總銷售額（萬元）（通配符套用）72

實例44 統計出上、下半年均有銷售的蔬菜品種數73

實例45 統計每個人的達標率75

實例46 統計成績為85~95分的學生人數76

實例47 統計出每個人抽獎抽到的獎品種類是多少77

實例48 統計各店男、女服裝的銷售數量分別是多少78

實例49 統計出各店的平均銷售額80

實例50 統計每個參賽選手的最後得分81

實例51 統計每個參賽選手的最後得分（棄權不計）83

實例52 求各班的最高分和最低分84

實例53 統計每個參賽選手的最後得分（升級版）86

第5章 時間都去哪兒了——日期時間函式的套用

實例54 根據入職日期計算出員工工齡，入職時是上半年還是下半年，是平年還是閏年89

實例55 計算指定年份的天數92

實例56 計算每個人的年齡93

實例57 計算挖掘機設備的租用到期時間94

實例58 計算出指定日期所在月份的天數95

實例59 判斷員工是否已過試用期（試用期為3個月）97

實例60 計算員工工齡98

實例61 根據出生日期對未來近10天內即將過生日的員工做提醒99

實例62 根據培訓日期計算出總的培訓費用100

實例63 計算一個月內所有日期屬於當月的第幾周102

實例64 根據工作日推算證件辦理完成的最後日期103

實例65 計算證件的領取日期104

實例66 根據辦理各事項所需工作日推算完成日期106

實例67 根據超市收貨日期計算不同類別供貨商的結款日期107

實例68 計算指定月份的員工出勤天數108

實例69 計算2017年指定月份的工作日天數（不含節假日）110

實例70 計算2017年全年每月的工作日天數111

實例71 根據上課時長計算出各講師的課酬113

實例72 根據開始時間和通話時間長度（分鐘）計算結束時間114

案例73 根據月份計算對應的季度118

第6章 我行我“數”——數學函式

實例74 計算某公司2017年每月業績占所在季度的百分比118

實例75 截尾保留金額小數點後兩位，然後求和119

實例76 製作循環序列數121

實例77 根據上下班時間計算工作時間122

實例78 將工資金額四捨五入到小數點後1位123

實例79 提取金額中十位、個位部分的值125

實例80 根據各講師的講課時間計算培訓費用126

實例81 根據分數判斷其屬於哪一個等級128

實例82 根據課程時長計算費用129

實例83 根據月份計算對應的季度130

實例84 生成8:00—12:00之間的隨機時間（RAND函式）131

實例85 生成8:00—12:00之間的隨機時間（RANDBETWEEN函式）133

第7章 字字珠璣——文本處理函式

實例86 計算字元串中單、雙位元組各自的字元長度135

實例87 根據月份計算對應的季度136

實例88 提取字元串中的不定長姓名137

實例89 所有成績大於或等於80分的學生為通過，否則為空138

實例90 提取字元串中的數字140

實例91 根據身份證號判斷性別140

實例92 根據分數判斷對應的等級142

實例93 計算各職稱名單對應的人數143

實例94 提取職員信息中的部門信息145

實例95 提取編號中的部分數據（尺碼提取）146

實例96 根據信息判斷學生屬於哪個階段147

實例97 提取文本字元串中的英文名149

實例98 隱藏地址中的部分信息150

實例99 給品名的英文單詞加括弧151

實例100 提取路徑下的檔案名稱152

實例101 提取字元串中多個數量的最大值154

實例102 判斷分數，及格打“√”，不及格打“×”156

實例103 將字元組別重新排序156

實例104 根據分數在單元格中製作條形圖158

實例105 根據項目的開始日期和結束日期製作進度圖158

實例106 清除文本字元串中的換行符160

實例107 以分隔設定提取多條不同長度的數據161

實例108 快速連線單元格區域數據，使其成為文本字元串162

實例109 根據身份證號碼提取性別164

實例110 使用TEXT函式判斷分數的等級165

實例111 給QQ號碼的每個數字之間加分隔設定166

實例112 判斷單詞首字母是否大寫167

實例113 將英文語句的首字母設為大寫，其他為小寫168

實例114 將中英文對譯中的英文部分轉換成句首字母大寫169

第8章 數據追捕高手——查找和引用函式

實例115 根據業績查詢對應等級171

實例116 根據業績等級返回對應獎金172

實例117 計算每個人當前業績距上一個等級還差多少萬元173

實例118 計算不重複的部門數量174

實例119 根據蔬選單價表計算蔬菜的金額176

實例120 根據提成百分比和業績計算提成金額177

實例121 多條件查詢177

實例122 查找指定學生的各科成績179

實例123 根據購買課程類別和購買金額計算折扣後金額180

實例124 計算指定科目所有分數的平均分182

實例125 根據分數判斷等級（LOOKUP向量垂直查詢）183

實例126 根據分數判斷等級（LOOKUP向量水平查詢）184

實例127 根據書籍編號查詢書籍名187

實例128 提取字元串中最後的數字部分188

實例129 根據分數判斷對應等級191

實例130 製作二維查詢系統192

實例131 製作模糊查詢系統193

實例132 根據課程編號返回課程類別195

實例133 製作工資條196

實例134 重組查詢區域後的VLOOKUP查詢197

實例135 將單列數據轉為多行多列數據199

實例136 將多行多列數據轉換為單列數據200

實例137 將二維數據錶轉換為一維數據表201

實例138 統計每個人各年的總業績204

實例139 統計各個產品最後兩天的總銷量205

實例140 整理轉置數據207

實例141 製作員工請假銷假監控系統210

第9章 讓函式水平更上一層樓——高級函式

實例142 以活動單元格為條件製作動態查詢系統214

實例143 統計成績表的最高總分及其對應的姓名217

實例144 統計出任意一科及以上成績大於或等於90分的人數219

實例145 自動編寫序號220

實例146 比賽評分計算表222

實例147 統計差、中、良、優四個等級各自的人數223

實例148 提取相同日期中的第一條記錄224

實例149 計算球賽的最多連勝次數225

第10章 為函式世界添磚加瓦——Excel 2019新增函式

實例150 根據成績判斷對應的等級228

實例151 多條件求最高銷售金額229

實例152 多條件求最低價格230

實例153 合併各月份的業績數據232

實例154 提取各姓名對應的分數233

實例155 根據部門提取合併對應的人員名單234

實例156 將文本字元串中的數字進行排序235

第11章 函式中的隱形絕招——三維引用技術

實例157 統計各科成績總分的最高分239

實例158 統計各月銷量占總銷量的百分比240

實例159 將訂單按數量進行拆分241

實例160 統計各個日期最高平均數量及對應城市243

第12章 啥事兒都能幫上忙——函式與Excel其他功能結合

實例161 標記出第4~6名的分數246

實例162 隔日標記日期記錄247

實例163 用顏色動態標記活動單元格所在行的數據248

實例164 用數據條標記未來3天即將到期的記錄251

實例165 動態標記日曆253

實例166 在數據透視表中根據匯總金額計算提成金額256

實例167 利用數據驗證製作二級下拉選單257

實例168 利用數據驗證驗證身份證號259

實例169 篩選包含“變速箱”且全年總銷量大於或等於350件的商品260

實例170 統計最高總分及對應姓名262

實例171 動態引用查詢系統的圖片263

實例172 動態獲取合併計算的數據源264

實例173 製作動態圖表266

實例174 使用表單控制項製作動態圖表268,第1章 菜鳥必學——函式與公式基礎1

1.1 公式與函式的關係1

1.2 公式的編寫與填充技巧2

1.3 函式的組成2

1.4 函式錄入技巧2

1.5 函式編輯技巧3

1.6 常用函式的套用4

1.7 Excel公式運算符5

1.7.1 算術運算符6

1.7.2 比較運算符7

1.7.3 連線運算符9

1.7.4 引用運算符10

1.7.5 運算符優先順序10

1.8 單元格引用11

1.8.1 相對引用11

1.8.2 絕對引用11

1.8.3 混合引用12

1.9 查看公式運算過程13

1.9.1 使用【公式求值】命令13

1.9.2 使用功能鍵F914

1.10 常用函式進階套用15

1.10.1 SUM函式15

1.10.2 AVERAGE函式16

1.10.3 COUNT函式18

1.10.4 MAX函式19

1.10.5 MIN函式20

1.10.6 綜合套用21

第2章 成為高手的必經之路——數組技術23

2.1 數組的定義23

2.2 數組維度24

2.3 數組與單元格24

2.3.1 數組與單元格區域的對應關係24

2.3.2 將數組寫入單元格區域25

2.4 數組的分類25

2.5 數組運算的規律與套用26

2.5.1 一維數組與單值的運算26

2.5.2 一維數組與一維數組的運算（方向相同）27

2.5.3 一維數組與一維數組的運算（方向不同）30

2.5.4 一維數組與二維數組的運算31

2.5.5 二維數組與單值的運算32

2.5.6 二維數組與二維數組的運算34

2.6 運算符“*”“+”與數組結合的套用37

2.6.1 數組間的“乘”運算37

2.6.2 數組間的“加”運算39

2.7 數組構造42

2.7.1 用ROW函式構造垂直序列數組42

2.7.2 用COLUMN函式構造水平序列數組44

2.7.3 獲取行、列數：ROWS函式與COLUMNS函式45

第3章 讓電腦幫助人腦——Excel對數據的判斷處理46

3.1 邏輯函式46

3.1.1 IF函式46

3.1.2 IF函式的嵌套套用48

3.1.3 IF函式的引用特性49

3.1.4 AND函式50

3.1.5 OR函式51

3.1.6 NOT函式53

3.2 Excel公式的錯誤值53

3.2.1 常見錯誤值53

3.2.2 識別錯誤值（ISERROR，ISERR，ISNA）54

3.2.3 處理錯誤值函式（IFERROR，IFNA）55

3.2.4 構造利用錯誤值55

3.3 信息函式58

3.3.1 IS類信息函式58

3.3.2 N函式59

3.3.3 T函式60

3.3.4 N/T+IF函式60

第4章 數據匯總知多少——常用匯總統計函式套用62

4.1 求和類函式62

4.1.1 計算乘積之和函式：SUMPRODUCT62

4.1.2 單條件求和函式：SUMIF64

4.1.3 多條件求和函式：SUMIFS69

4.2 計數類函式73

4.2.1 計算數字個數函式：COUNT73

4.2.2 非空計數函式：COUNTA74

4.2.3 單條件計數函式：COUNTIF76

4.2.4 多條件計數函式：COUNTIFS78

4.3 平均類函式79

4.3.1 條件求平均函式：AVERAGEIF79

4.3.2 多條件求平均函式：AVERAGEIFS81

4.3.3 修剪平均函式：TRIMMEAN82

4.4 求最大值、最小值的函式84

4.4.1 條件求最大值、最小值84

4.4.2 極大值、極小值函式：LARGE與SMALL85

第5章 時間都去哪兒了——日期時間函式的套用88

5.1 日期函式89

5.1.1 年月日函式：YEAR、MONTH、DAY89

5.1.2 日期函式：DATE91

5.1.3 系統日期、時間函式：TODAY、NOW92

5.1.4 月份函式：EDATE94

5.1.5 月末函式：EOMONTH95

5.1.6 日期處理函式：DATEDIF96

5.2 星期與工作日函式100

5.2.1 計算星期幾函式：WEEKDAY100

5.2.2 計算第幾周函式：WEEKNUM101

5.2.3 計算工作日函式：WORKDAY103

5.2.4 自定義工作日與休息日函式：WORKDAY.INTL105

5.2.5 計算工作日天數函式：NETWORKDAYS108

5.2.6 自定義工作日天數函式：NETWORKDAYS.INTL109

5.3 時間函式113

5.3.1 時分秒函式：HOUR、MINUTE、SECOND113

5.3.2 時間函式：TIME114

第6章 我行我“數”——數學函式117

6.1 取整函式117

6.1.1 向下取整函式：INT117

6.1.2 截尾取數函式：TRUNC119

6.2 求余函式：MOD120

6.3 捨入函式123

6.3.1 四捨五入函式：ROUND123

6.3.2 向下捨入函式：ROUNDDOWN125

6.3.3 向上捨入函式：ROUNDUP126

6.3.4 按倍數向下舍位函式：FLOOR128

6.3.5 按倍數向上進位函式：CEILING129

6.4 隨機函式131

6.4.1 隨機小數函式：RAND131

6.4.2 隨機整數函式：RANDBETWEEN132

第7章 字字珠璣——文本處理函式134

7.1 文本長度計算函式：LEN和LENB134

7.2 文本提取函式137

7.2.1 從左提取字元函式：LEFT、LEFTB137

7.2.2 從右提取字元函式：RIGHT、RIGHTB139

7.2.3 從指定位置提取字元函式：MID、MIDB141

7.3 文本查找函式144

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

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

7.4 文本替換函式150

7.4.1 按址替換函式：REPLACE、REPLACEB150

7.4.2 按值替換函式：SUBSTITUTE152

7.5 字元與數字代碼函式155

7.5.1 將字元轉換為數字代碼的函式：CODE155

7.5.2 將數字代碼轉換為字元的函式：CHAR155

7.6 重複函式：REPT157

7.7 不可見字元清除函式159

7.7.1 清除不可見字元函式：CLEAN160

7.7.2 清除空格字元專用函式：TRIM160

7.8 文本字元連線函式162

7.8.1 文本字元連線函式：CONCATENATE162

7.8.2 另類文本字元連線函式：PHONETIC162

7.9 格式轉換函式：TEXT163

7.10 字母轉換函式167

7.10.1 大寫字母函式：UPPER167

7.10.2 小寫字母函式：LOWER167

7.10.3 將單詞首字母轉換為大寫函式：PROPER168

第8章 數據追捕高手——查找和引用函式170

8.1 查找址函式：MATCH170

8.2 查找值函式175

8.2.1 垂直查找函式：VLOOKUP175

8.2.2 水平查找函式：HLOOKUP179

8.2.3 查找函式：LOOKUP183

8.3 引用函式189

8.3.1 引用函式①：INDEX189

8.3.2 引用函式②：CHOOSE195

8.3.3 引用函式③：INDIRECT198

8.3.4 引用函式④：OFFSET203

8.4 其他函式206

8.4.1 轉置函式：TRANSPOSE207

8.4.2 單元格地址函式：ADDRESS208

8.4.3 超連結函式：HYPERLINK209

第9章 讓函式水平更上一層樓——高級函式213

9.1 獲取單元格信息的函式：CELL213

9.2 矩陣乘積函式：MMULT216

9.3 分類匯總函式：SUBTOTAL220

9.4 頻率函式：FREQUENCY223

第10章 為函式世界添磚加瓦——Excel 2019新增函式227

10.1 條件函式227

10.1.1 多條件判斷函式：IFS227

10.1.2 多條件求最大值函式：MAXIFS228

10.1.3 多條件求最小值函式：MINIFS230

10.2 合併函式231

10.2.1 無分隔設定合併函式：CONCAT231

10.2.2 有分隔設定合併函式：TEXTJOIN234

第11章 函式中的隱形絕招——三維引用技術238

11.1 什麼是三維引用238

11.2 三維引用函式239

11.2.1 三維引用函式：INDIRECT239

11.2.2 三維引用函式：OFFSET241

11.3 支持三維引用的函式244

第12章 啥事兒都能幫上忙——函式與Excel其他功能結合245

12.1 函式與Excel其他功能結合套用245

12.1.1 函式與條件格式245

12.1.2 函式與數據透視表256

12.1.3 函式與數據驗證（數據有效性）257

12.1.4 函式與高級篩選260

12.2 Excel函式與名稱結合套用262

12.2.1 用名稱簡化公式262

12.2.2 名稱與圖片連結263

12.2.3 名稱與合併計算264

12.2.4 名稱與圖表266

12.2.5 名稱與表單控制項268

實例

第1章 菜鳥必學——函式與公式基礎

實例1 求每個銷售員在第1季度的總業績平均業績最高業績最低業績月份數4

實例2 使用SUM函式計算每個銷售員的業績在總業績中的占比6

實例3 兩科成績同時大於或等於90分的學生獎勵100元8

實例4 上半年和下半年業績均為優的員工獎金為10 000元，其他獎金為8000元9

實例5 九九乘法表的製作12

實例6 求家庭收支明細表中的累計餘額15

實例7 計算每位客服人員的獎金是多少17

實例8 計算員工一周的出勤率18

實例9 計算員工上班遲到的分鐘數19

實例10 計算兩科成績同時大於或等於90分的學生的平均分20

實例11 計算每個參賽選手的最後得分21

第2章 成為高手的必經之路——數組技術

實例12 計算採購離合器產品的總金額27

實例13 根據各離合器產品的單價和數量求總金額28

實例14 計算2015—2017年業績等級為全優的人數28

實例15 利用數組製作九九乘法表30

實例16 計算各科成績低於平均分的學生人數32

實例17 計算業績大於或等於450萬元的銷售員的補助總金額33

實例18 統計兩年各項產品的銷售情況同時合格的人數34

實例19 綜合套用：根據各農產品不同等級對應的單價和數量計算出金額36

實例20 求連續3個月銷售電腦數量大於或等於50台的人數37

實例21 求部門是銷售部且工資大於或等於10 000元的員工的總工資38

實例22 求銷售電腦數量至少有1個月大於或等於80台的達標人數40

實例23 求部門是銷售部或者工資大於或等於10 000元的員工的總工資41

實例24 求每個員工上半年獲得最高工資的月份43

實例25 求各班學生的最高分44

第3章 讓電腦幫助人腦——Excel對數據的判斷處理

實例26 根據價格表計算出採購清單的採購金額47

實例27 判斷兩科成績同時大於或等於90分的學生為優秀（IF函式）49

實例28 根據選擇的起始月份和終止月份匯總相應的銷售數量49

實例29 判斷兩科成績同時大於或等於90分的學生為優秀（AND函式）51

實例30 判斷三科成績中只要有一科成績大於或等於90分的學生為合格52

實例31 計算採購清單中的商品金額54

實例32 統計因故沒有考試和考試不及格的人數56

實例33 求考試及格的人數57

實例34 計算1月到4月業績大於或等於50萬元的平均業績58

實例35 計算成績大於或等於80分以上的學生的平均分60

第4章 數據匯總知多少——常用匯總統計函式套用

實例36 計算各產品的總金額63

實例37 求各種花卉的銷售總數量64

實例38 求花卉的銷售數量大於或等於700件的總數量（SUMIF函式忽略第3參數的

套用）65

實例39 求競賽中國中和高中最高分的總和分別是多少（SUMIF函式第2參數通配符的

套用）66

實例40 求某競賽中國中和高中最高分的總和分別是多少（數據結構有變化）68

實例41 求二級蘋果的總數量（SUMIFS函式的普通套用）70

實例42 求各等級、各品名的水果的總數量71

實例43 統計廣東科技公司的總銷售額（萬元）（通配符套用）72

實例44 統計出上、下半年均有銷售的蔬菜品種數73

實例45 統計每個人的達標率75

實例46 統計成績為85~95分的學生人數76

實例47 統計出每個人抽獎抽到的獎品種類是多少77

實例48 統計各店男、女服裝的銷售數量分別是多少78

實例49 統計出各店的平均銷售額80

實例50 統計每個參賽選手的最後得分81

實例51 統計每個參賽選手的最後得分（棄權不計）83

實例52 求各班的最高分和最低分84

實例53 統計每個參賽選手的最後得分（升級版）86

第5章 時間都去哪兒了——日期時間函式的套用

實例54 根據入職日期計算出員工工齡，入職時是上半年還是下半年，是平年還是閏年89

實例55 計算指定年份的天數92

實例56 計算每個人的年齡93

實例57 計算挖掘機設備的租用到期時間94

實例58 計算出指定日期所在月份的天數95

實例59 判斷員工是否已過試用期（試用期為3個月）97

實例60 計算員工工齡98

實例61 根據出生日期對未來近10天內即將過生日的員工做提醒99

實例62 根據培訓日期計算出總的培訓費用100

實例63 計算一個月內所有日期屬於當月的第幾周102

實例64 根據工作日推算證件辦理完成的最後日期103

實例65 計算證件的領取日期104

實例66 根據辦理各事項所需工作日推算完成日期106

實例67 根據超市收貨日期計算不同類別供貨商的結款日期107

實例68 計算指定月份的員工出勤天數108

實例69 計算2017年指定月份的工作日天數（不含節假日）110

實例70 計算2017年全年每月的工作日天數111

實例71 根據上課時長計算出各講師的課酬113

實例72 根據開始時間和通話時間長度（分鐘）計算結束時間114

案例73 根據月份計算對應的季度118

第6章 我行我“數”——數學函式

實例74 計算某公司2017年每月業績占所在季度的百分比118

實例75 截尾保留金額小數點後兩位，然後求和119

實例76 製作循環序列數121

實例77 根據上下班時間計算工作時間122

實例78 將工資金額四捨五入到小數點後1位123

實例79 提取金額中十位、個位部分的值125

實例80 根據各講師的講課時間計算培訓費用126

實例81 根據分數判斷其屬於哪一個等級128

實例82 根據課程時長計算費用129

實例83 根據月份計算對應的季度130

實例84 生成8:00—12:00之間的隨機時間（RAND函式）131

實例85 生成8:00—12:00之間的隨機時間（RANDBETWEEN函式）133

第7章 字字珠璣——文本處理函式

實例86 計算字元串中單、雙位元組各自的字元長度135

實例87 根據月份計算對應的季度136

實例88 提取字元串中的不定長姓名137

實例89 所有成績大於或等於80分的學生為通過，否則為空138

實例90 提取字元串中的數字140

實例91 根據身份證號判斷性別140

實例92 根據分數判斷對應的等級142

實例93 計算各職稱名單對應的人數143

實例94 提取職員信息中的部門信息145

實例95 提取編號中的部分數據（尺碼提取）146

實例96 根據信息判斷學生屬於哪個階段147

實例97 提取文本字元串中的英文名149

實例98 隱藏地址中的部分信息150

實例99 給品名的英文單詞加括弧151

實例100 提取路徑下的檔案名稱152

實例101 提取字元串中多個數量的最大值154

實例102 判斷分數，及格打“√”，不及格打“×”156

實例103 將字元組別重新排序156

實例104 根據分數在單元格中製作條形圖158

實例105 根據項目的開始日期和結束日期製作進度圖158

實例106 清除文本字元串中的換行符160

實例107 以分隔設定提取多條不同長度的數據161

實例108 快速連線單元格區域數據，使其成為文本字元串162

實例109 根據身份證號碼提取性別164

實例110 使用TEXT函式判斷分數的等級165

實例111 給QQ號碼的每個數字之間加分隔設定166

實例112 判斷單詞首字母是否大寫167

實例113 將英文語句的首字母設為大寫，其他為小寫168

實例114 將中英文對譯中的英文部分轉換成句首字母大寫169

第8章 數據追捕高手——查找和引用函式

實例115 根據業績查詢對應等級171

實例116 根據業績等級返回對應獎金172

實例117 計算每個人當前業績距上一個等級還差多少萬元173

實例118 計算不重複的部門數量174

實例119 根據蔬選單價表計算蔬菜的金額176

實例120 根據提成百分比和業績計算提成金額177

實例121 多條件查詢177

實例122 查找指定學生的各科成績179

實例123 根據購買課程類別和購買金額計算折扣後金額180

實例124 計算指定科目所有分數的平均分182

實例125 根據分數判斷等級（LOOKUP向量垂直查詢）183

實例126 根據分數判斷等級（LOOKUP向量水平查詢）184

實例127 根據書籍編號查詢書籍名187

實例128 提取字元串中最後的數字部分188

實例129 根據分數判斷對應等級191

實例130 製作二維查詢系統192

實例131 製作模糊查詢系統193

實例132 根據課程編號返回課程類別195

實例133 製作工資條196

實例134 重組查詢區域後的VLOOKUP查詢197

實例135 將單列數據轉為多行多列數據199

實例136 將多行多列數據轉換為單列數據200

實例137 將二維數據錶轉換為一維數據表201

實例138 統計每個人各年的總業績204

實例139 統計各個產品最後兩天的總銷量205

實例140 整理轉置數據207

實例141 製作員工請假銷假監控系統210

第9章 讓函式水平更上一層樓——高級函式

實例142 以活動單元格為條件製作動態查詢系統214

實例143 統計成績表的最高總分及其對應的姓名217

實例144 統計出任意一科及以上成績大於或等於90分的人數219

實例145 自動編寫序號220

實例146 比賽評分計算表222

實例147 統計差、中、良、優四個等級各自的人數223

實例148 提取相同日期中的第一條記錄224

實例149 計算球賽的最多連勝次數225

第10章 為函式世界添磚加瓦——Excel 2019新增函式

實例150 根據成績判斷對應的等級228

實例151 多條件求最高銷售金額229

實例152 多條件求最低價格230

實例153 合併各月份的業績數據232

實例154 提取各姓名對應的分數233

實例155 根據部門提取合併對應的人員名單234

實例156 將文本字元串中的數字進行排序235

第11章 函式中的隱形絕招——三維引用技術

實例157 統計各科成績總分的最高分239

實例158 統計各月銷量占總銷量的百分比240

實例159 將訂單按數量進行拆分241

實例160 統計各個日期最高平均數量及對應城市243

第12章 啥事兒都能幫上忙——函式與Excel其他功能結合

實例161 標記出第4~6名的分數246

實例162 隔日標記日期記錄247

實例163 用顏色動態標記活動單元格所在行的數據248

實例164 用數據條標記未來3天即將到期的記錄251

實例165 動態標記日曆253

實例166 在數據透視表中根據匯總金額計算提成金額256

實例167 利用數據驗證製作二級下拉選單257

實例168 利用數據驗證驗證身份證號259

實例169 篩選包含“變速箱”且全年總銷量大於或等於350件的商品260

實例170 統計最高總分及對應姓名262

實例171 動態引用查詢系統的圖片263

實例172 動態獲取合併計算的數據源264

實例173 製作動態圖表266

實例174 使用表單控制項製作動態圖表268