加薪不加班：174個Excel函數(shù)案例闖職場

第1章 菜鳥必學――函數(shù)與公式基礎1
1．1 公式與函數(shù)的關系1
1．2 公式的編寫與填充技巧2
1．3 函數(shù)的組成2
1．4 函數(shù)錄入技巧2
1．5 函數(shù)編輯技巧3
1．6 常用函數(shù)的應用4
1．7 Excel公式運算符5
1．7．1 算術運算符6
1．7．2 比較運算符7
1．7．3 連接運算符9
1．7．4 引用運算符10
1．7．5 運算符優(yōu)先順序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 常用函數(shù)進階應用15
1．10．1 SUM函數(shù)15
1．10．2 AVERAGE函數(shù)16
1．10．3 COUNT函數(shù)18
1．10．4 MAX函數(shù)19
1．10．5 MIN函數(shù)20
1．10．6 綜合應用21
第2章 成為高手的必經(jīng)之路――數(shù)組技術23
2．1 數(shù)組的定義23
2．2 數(shù)組維度24
2．3 數(shù)組與單元格24
2．3．1 數(shù)組與單元格區(qū)域的對應關系24
2．3．2 將數(shù)組寫入單元格區(qū)域25
2．4 數(shù)組的分類25
2．5 數(shù)組運算的規(guī)律與應用26
2．5．1 一維數(shù)組與單值的運算26
2．5．2 一維數(shù)組與一維數(shù)組的運算（方向相同）27
2．5．3 一維數(shù)組與一維數(shù)組的運算（方向不同）30
2．5．4 一維數(shù)組與二維數(shù)組的運算31
2．5．5 二維數(shù)組與單值的運算32
2．5．6 二維數(shù)組與二維數(shù)組的運算34
2．6 運算符“*”“+”與數(shù)組結合的應用37
2．6．1 數(shù)組間的“乘”運算37
2．6．2 數(shù)組間的“加”運算39
2．7 數(shù)組構造42
2．7．1 用ROW函數(shù)構造垂直序列數(shù)組42
2．7．2 用COLUMN函數(shù)構造水平序列數(shù)組44
2．7．3 獲取行、列數(shù)：ROWS函數(shù)與COLUMNS函數(shù)45
第3章 讓電腦幫助人腦――Excel對數(shù)據(jù)的判斷處理46
3．1 邏輯函數(shù)46
3．1．1 IF函數(shù)46
3．1．2 IF函數(shù)的嵌套應用48
3．1．3 IF函數(shù)的引用特性49
3．1．4 AND函數(shù)50
3．1．5 OR函數(shù)51
3．1．6 NOT函數(shù)53
3．2 Excel公式的錯誤值53
3．2．1 常見錯誤值53
3．2．2 識別錯誤值（ISERROR，ISERR，ISNA）54
3．2．3 處理錯誤值函數(shù)（IFERROR，IFNA）55
3．2．4 構造利用錯誤值55
3．3 信息函數(shù)58
3．3．1 IS類信息函數(shù)58
3．3．2 N函數(shù)59
3．3．3 T函數(shù)60
3．3．4 N/T+IF函數(shù)60
第4章 數(shù)據(jù)匯總知多少――常用匯總統(tǒng)計函數(shù)應用62
4．1 求和類函數(shù)62
4．1．1 計算乘積之和函數(shù)：SUMPRODUCT62
4．1．2 單條件求和函數(shù)：SUMIF64
4．1．3 多條件求和函數(shù)：SUMIFS69
4．2 計數(shù)類函數(shù)73
4．2．1 計算數(shù)字個數(shù)函數(shù)：COUNT73
4．2．2 非空計數(shù)函數(shù)：COUNTA74
4．2．3 單條件計數(shù)函數(shù)：COUNTIF76
4．2．4 多條件計數(shù)函數(shù)：COUNTIFS78
4．3 平均類函數(shù)79
4．3．1 條件求平均函數(shù)：AVERAGEIF79
4．3．2 多條件求平均函數(shù)：AVERAGEIFS81
4．3．3 修剪平均函數(shù)：TRIMMEAN82
4．4 求最大值、最小值的函數(shù)84
4．4．1 條件求最大值、最小值84
4．4．2 極大值、極小值函數(shù)：LARGE與SMALL85
第5章 時間都去哪兒了――日期時間函數(shù)的應用88
5．1 日期函數(shù)89
5．1．1 年月日函數(shù)：YEAR、MONTH、DAY89
5．1．2 日期函數(shù)：DATE91
5．1．3 系統(tǒng)日期、時間函數(shù)：TODAY、NOW92
5．1．4 月份函數(shù)：EDATE94
5．1．5 月末函數(shù)：EOMONTH95
5．1．6 日期處理函數(shù)：DATEDIF96
5．2 星期與工作日函數(shù)100
5．2．1 計算星期幾函數(shù)：WEEKDAY100
5．2．2 計算第幾周函數(shù)：WEEKNUM101
5．2．3 計算工作日函數(shù)：WORKDAY103
5．2．4 自定義工作日與休息日函數(shù)：WORKDAY．INTL105
5．2．5 計算工作日天數(shù)函數(shù)：NETWORKDAYS108
5．2．6 自定義工作日天數(shù)函數(shù)：NETWORKDAYS．INTL109
5．3 時間函數(shù)113
5．3．1 時分秒函數(shù)：HOUR、MINUTE、SECOND113
5．3．2 時間函數(shù)：TIME114
第6章 我行我“數(shù)”――數(shù)學函數(shù)117
6．1 取整函數(shù)117
6．1．1 向下取整函數(shù)：INT117
6．1．2 截尾取數(shù)函數(shù)：TRUNC119
6．2 求余函數(shù)：MOD120
6．3 舍入函數(shù)123
6．3．1 四舍五入函數(shù)：ROUND123
6．3．2 向下舍入函數(shù)：ROUNDDOWN125
6．3．3 向上舍入函數(shù)：ROUNDUP126
6．3．4 按倍數(shù)向下舍位函數(shù)：FLOOR128
6．3．5 按倍數(shù)向上進位函數(shù)：CEILING129
6．4 隨機函數(shù)131
6．4．1 隨機小數(shù)函數(shù)：RAND131
6．4．2 隨機整數(shù)函數(shù)：RANDBETWEEN132
第7章 字字珠璣――文本處理函數(shù)134
7．1 文本長度計算函數(shù)：LEN和LENB134
7．2 文本提取函數(shù)137
7．2．1 從左提取字符函數(shù)：LEFT、LEFTB137
7．2．2 從右提取字符函數(shù)：RIGHT、RIGHTB139
7．2．3 從指定位置提取字符函數(shù)：MID、MIDB141
7．3 文本查找函數(shù)144
7．3．1 區(qū)分大小寫、不支持通配符的查找函數(shù)：FIND、FINDB145
7．3．2 不區(qū)分大小寫、支持通配符的查找函數(shù)：SEARCH、SEARCHB147
7．4 文本替換函數(shù)150
7．4．1 按址替換函數(shù)：REPLACE、REPLACEB150
7．4．2 按值替換函數(shù)：SUBSTITUTE152
7．5 字符與數(shù)字代碼函數(shù)155
7．5．1 將字符轉換為數(shù)字代碼的函數(shù)：CODE155
7．5．2 將數(shù)字代碼轉換為字符的函數(shù)：CHAR155
7．6 重復函數(shù)：REPT157
7．7 不可見字符清除函數(shù)159
7．7．1 清除不可見字符函數(shù)：CLEAN160
7．7．2 清除空格字符專用函數(shù)：TRIM160
7．8 文本字符連接函數(shù)162
7．8．1 文本字符連接函數(shù)：CONCATENATE162
7．8．2 另類文本字符連接函數(shù)：PHONETIC162
7．9 格式轉換函數(shù)：TEXT163
7．10 字母轉換函數(shù)167
7．10．1 大寫字母函數(shù)：UPPER167
7．10．2 小寫字母函數(shù)：LOWER167
7．10．3 將單詞首字母轉換為大寫函數(shù)：PROPER168
第8章 數(shù)據(jù)追捕高手――查找和引用函數(shù)170
8．1 查找址函數(shù)：MATCH170
8．2 查找值函數(shù)175
8．2．1 垂直查找函數(shù)：VLOOKUP175
8．2．2 水平查找函數(shù)：HLOOKUP179
8．2．3 查找函數(shù)：LOOKUP183
8．3 引用函數(shù)189
8．3．1 引用函數(shù)①：INDEX189
8．3．2 引用函數(shù)②：CHOOSE195
8．3．3 引用函數(shù)③：INDIRECT198
8．3．4 引用函數(shù)④：OFFSET203
8．4 其他函數(shù)206
8．4．1 轉置函數(shù)：TRANSPOSE207
8．4．2 單元格地址函數(shù)：ADDRESS208
8．4．3 超鏈接函數(shù)：HYPERLINK209
第9章 讓函數(shù)水平更上一層樓――高級函數(shù)213
9．1 獲取單元格信息的函數(shù)：CELL213
9．2 矩陣乘積函數(shù)：MMULT216
9．3 分類匯總函數(shù)：SUBTOTAL220
9．4 頻率函數(shù)：FREQUENCY223
第10章 為函數(shù)世界添磚加瓦――Excel 2019新增函數(shù)227
10．1 條件函數(shù)227
10．1．1 多條件判斷函數(shù)：IFS227
10．1．2 多條件求最大值函數(shù)：MAXIFS228
10．1．3 多條件求最小值函數(shù)：MINIFS230
10．2 合并函數(shù)231
10．2．1 無分隔符合并函數(shù)：CONCAT231
10．2．2 有分隔符合并函數(shù)：TEXTJOIN234
第11章 函數(shù)中的隱形絕招――三維引用技術238
11．1 什么是三維引用238
11．2 三維引用函數(shù)239
11．2．1 三維引用函數(shù)：INDIRECT239
11．2．2 三維引用函數(shù)：OFFSET241
11．3 支持三維引用的函數(shù)244
第12章 啥事兒都能幫上忙――函數(shù)與Excel其他功能結合245
12．1 函數(shù)與Excel其他功能結合應用245
12．1．1 函數(shù)與條件格式245
12．1．2 函數(shù)與數(shù)據(jù)透視表256
12．1．3 函數(shù)與數(shù)據(jù)驗證（數(shù)據(jù)有效性）257
12．1．4 函數(shù)與高級篩選260
12．2 Excel函數(shù)與名稱結合應用262
12．2．1 用名稱簡化公式262
12．2．2 名稱與圖片鏈接263
12．2．3 名稱與合并計算264
12．2．4 名稱與圖表266
12．2．5 名稱與表單控件268
實例目錄

第1章 菜鳥必學――函數(shù)與公式基礎
實例1 求每個銷售員在第1季度的總業(yè)績\\平均業(yè)績\\最高業(yè)績\\最低業(yè)績\\月份數(shù)4
實例2 使用SUM函數(shù)計算每個銷售員的業(yè)績在總業(yè)績中的占比6
實例3 兩科成績同時大于或等于90分的學生獎勵100元8
實例4 上半年和下半年業(yè)績均為優(yōu)的員工獎金為10 000元，其他獎金為8000元9
實例5 九九乘法表的制作12
實例6 求家庭收支明細表中的累計余額15
實例7 計算每位客服人員的獎金是多少17
實例8 計算員工一周的出勤率18
實例9 計算員工上班遲到的分鐘數(shù)19
實例10 計算兩科成績同時大于或等于90分的學生的平均分20
實例11 計算每個參賽選手的最后得分21
第2章 成為高手的必經(jīng)之路――數(shù)組技術
實例12 計算采購離合器產(chǎn)品的總金額27
實例13 根據(jù)各離合器產(chǎn)品的單價和數(shù)量求總金額28
實例14 計算2015―2017年業(yè)績等級為全優(yōu)的人數(shù)28
實例15 利用數(shù)組制作九九乘法表30
實例16 計算各科成績低于平均分的學生人數(shù)32
實例17 計算業(yè)績大于或等于450萬元的銷售員的補助總金額33
實例18 統(tǒng)計兩年各項產(chǎn)品的銷售情況同時合格的人數(shù)34
實例19 綜合應用：根據(jù)各農產(chǎn)品不同等級對應的單價和數(shù)量計算出金額36
實例20 求連續(xù)3個月銷售電腦數(shù)量大于或等于50臺的人數(shù)37
實例21 求部門是銷售部且工資大于或等于10 000元的員工的總工資38
實例22 求銷售電腦數(shù)量至少有1個月大于或等于80臺的達標人數(shù)40
實例23 求部門是銷售部或者工資大于或等于10 000元的員工的總工資41
實例24 求每個員工上半年獲得最高工資的月份43
實例25 求各班學生的最高分44
第3章 讓電腦幫助人腦――Excel對數(shù)據(jù)的判斷處理
實例26 根據(jù)價格表計算出采購清單的采購金額47
實例27 判斷兩科成績同時大于或等于90分的學生為優(yōu)秀（IF函數(shù)）49
實例28 根據(jù)選擇的起始月份和終止月份匯總相應的銷售數(shù)量49
實例29 判斷兩科成績同時大于或等于90分的學生為優(yōu)秀（AND函數(shù)）51
實例30 判斷三科成績中只要有一科成績大于或等于90分的學生為合格52
實例31 計算采購清單中的商品金額54
實例32 統(tǒng)計因故沒有考試和考試不及格的人數(shù)56
實例33 求考試及格的人數(shù)57
實例34 計算1月到4月業(yè)績大于或等于50萬元的平均業(yè)績58
實例35 計算成績大于或等于80分以上的學生的平均分60
第4章 數(shù)據(jù)匯總知多少――常用匯總統(tǒng)計函數(shù)應用
實例36 計算各產(chǎn)品的總金額63
實例37 求各種花卉的銷售總數(shù)量64
實例38 求花卉的銷售數(shù)量大于或等于700件的總數(shù)量（SUMIF函數(shù)忽略第3參數(shù)的
應用）65
實例39 求競賽中初中和高中最高分的總和分別是多少（SUMIF函數(shù)第2參數(shù)通配符的
應用）66
實例40 求某競賽中初中和高中最高分的總和分別是多少（數(shù)據(jù)結構有變化）68
實例41 求二級蘋果的總數(shù)量（SUMIFS函數(shù)的普通應用）70
實例42 求各等級、各品名的水果的總數(shù)量71
實例43 統(tǒng)計廣東科技公司的總銷售額（萬元）（通配符應用）72
實例44 統(tǒng)計出上、下半年均有銷售的蔬菜品種數(shù)73
實例45 統(tǒng)計每個人的達標率75
實例46 統(tǒng)計成績?yōu)?5~95分的學生人數(shù)76
實例47 統(tǒng)計出每個人抽獎抽到的獎品種類是多少77
實例48 統(tǒng)計各店男、女服裝的銷售數(shù)量分別是多少78
實例49 統(tǒng)計出各店的平均銷售額80
實例50 統(tǒng)計每個參賽選手的最后得分81
實例51 統(tǒng)計每個參賽選手的最后得分（棄權不計）83
實例52 求各班的最高分和最低分84
實例53 統(tǒng)計每個參賽選手的最后得分（升級版）86
第5章 時間都去哪兒了――日期時間函數(shù)的應用
實例54 根據(jù)入職日期計算出員工工齡，入職時是上半年還是下半年，是平年還是閏年89
實例55 計算指定年份的天數(shù)92
實例56 計算每個人的年齡93
實例57 計算挖掘機設備的租用到期時間94
實例58 計算出指定日期所在月份的天數(shù)95
實例59 判斷員工是否已過試用期（試用期為3個月）97
實例60 計算員工工齡98
實例61 根據(jù)出生日期對未來近10天內即將過生日的員工做提醒99
實例62 根據(jù)培訓日期計算出總的培訓費用100
實例63 計算一個月內所有日期屬于當月的第幾周102
實例64 根據(jù)工作日推算證件辦理完成的最后日期103
實例65 計算證件的領取日期104
實例66 根據(jù)辦理各事項所需工作日推算完成日期106
實例67 根據(jù)超市收貨日期計算不同類別供貨商的結款日期107
實例68 計算指定月份的員工出勤天數(shù)108
實例69 計算2017年指定月份的工作日天數(shù)（不含節(jié)假日）110
實例70 計算2017年全年每月的工作日天數(shù)111
實例71 根據(jù)上課時長計算出各講師的課酬113
實例72 根據(jù)開始時間和通話時間長度（分鐘）計算結束時間114
案例73 根據(jù)月份計算對應的季度118
第6章 我行我“數(shù)”――數(shù)學函數(shù)
實例74 計算某公司2017年每月業(yè)績占所在季度的百分比118
實例75 截尾保留金額小數(shù)點后兩位，然后求和119
實例76 制作循環(huán)序列數(shù)121
實例77 根據(jù)上下班時間計算工作時間122
實例78 將工資金額四舍五入到小數(shù)點后1位123
實例79 提取金額中十位、個位部分的值125
實例80 根據(jù)各講師的講課時間計算培訓費用126
實例81 根據(jù)分數(shù)判斷其屬于哪一個等級128
實例82 根據(jù)課程時長計算費用129
實例83 根據(jù)月份計算對應的季度130
實例84 生成8：00―12：00之間的隨機時間（RAND函數(shù)）131
實例85 生成8：00―12：00之間的隨機時間（RANDBETWEEN函數(shù)）133
第7章 字字珠璣――文本處理函數(shù)
實例86 計算字符串中單、雙字節(jié)各自的字符長度135
實例87 根據(jù)月份計算對應的季度136
實例88 提取字符串中的不定長姓名137
實例89 所有成績大于或等于80分的學生為通過，否則為空138
實例90 提取字符串中的數(shù)字140
實例91 根據(jù)身份證號判斷性別140
實例92 根據(jù)分數(shù)判斷對應的等級142
實例93 計算各職稱名單對應的人數(shù)143
實例94 提取職員信息中的部門信息145
實例95 提取編號中的部分數(shù)據(jù)（尺碼提?。?46
實例96 根據(jù)信息判斷學生屬于哪個階段147
實例97 提取文本字符串中的英文名149
實例98 隱藏地址中的部分信息150
實例99 給品名的英文單詞加括號151
實例100 提取路徑下的文件名152
實例101 提取字符串中多個數(shù)量的最大值154
實例102 判斷分數(shù)，及格打“√”，不及格打“×”156
實例103 將字符組別重新排序156
實例104 根據(jù)分數(shù)在單元格中制作條形圖158
實例105 根據(jù)項目的開始日期和結束日期制作進度圖158
實例106 清除文本字符串中的換行符160
實例107 以分隔符提取多條不同長度的數(shù)據(jù)161
實例108 快速連接單元格區(qū)域數(shù)據(jù)，使其成為文本字符串162
實例109 根據(jù)身份證號碼提取性別164
實例110 使用TEXT函數(shù)判斷分數(shù)的等級165
實例111 給QQ號碼的每個數(shù)字之間加分隔符166
實例112 判斷單詞首字母是否大寫167
實例113 將英文語句的首字母設為大寫，其他為小寫168
實例114 將中英文對譯中的英文部分轉換成句首字母大寫169
第8章 數(shù)據(jù)追捕高手――查找和引用函數(shù)
實例115 根據(jù)業(yè)績查詢對應等級171
實例116 根據(jù)業(yè)績等級返回對應獎金172
實例117 計算每個人當前業(yè)績距上一個等級還差多少萬元173
實例118 計算不重復的部門數(shù)量174
實例119 根據(jù)蔬菜單價表計算蔬菜的金額176
實例120 根據(jù)提成百分比和業(yè)績計算提成金額177
實例121 多條件查詢177
實例122 查找指定學生的各科成績179
實例123 根據(jù)購買課程類別和購買金額計算折扣后金額180
實例124 計算指定科目所有分數(shù)的平均分182
實例125 根據(jù)分數(shù)判斷等級（LOOKUP向量垂直查詢）183
實例126 根據(jù)分數(shù)判斷等級（LOOKUP向量水平查詢）184
實例127 根據(jù)書籍編號查詢書籍名187
實例128 提取字符串中最后的數(shù)字部分188
實例129 根據(jù)分數(shù)判斷對應等級191
實例130 制作二維查詢系統(tǒng)192
實例131 制作模糊查詢系統(tǒng)193
實例132 根據(jù)課程編號返回課程類別195
實例133 制作工資條196
實例134 重組查詢區(qū)域后的VLOOKUP查詢197
實例135 將單列數(shù)據(jù)轉為多行多列數(shù)據(jù)199
實例136 將多行多列數(shù)據(jù)轉換為單列數(shù)據(jù)200
實例137 將二維數(shù)據(jù)表轉換為一維數(shù)據(jù)表201
實例138 統(tǒng)計每個人各年的總業(yè)績204
實例139 統(tǒng)計各個產(chǎn)品最后兩天的總銷量205
實例140 整理轉置數(shù)據(jù)207
實例141 制作員工請假銷假監(jiān)控系統(tǒng)210
第9章 讓函數(shù)水平更上一層樓――高級函數(shù)
實例142 以活動單元格為條件制作動態(tài)查詢系統(tǒng)214
實例143 統(tǒng)計成績表的最高總分及其對應的姓名217
實例144 統(tǒng)計出任意一科及以上成績大于或等于90分的人數(shù)219
實例145 自動編寫序號220
實例146 比賽評分計算表222
實例147 統(tǒng)計差、中、良、優(yōu)四個等級各自的人數(shù)223
實例148 提取相同日期中的第一條記錄224
實例149 計算球賽的最多連勝次數(shù)225
第10章 為函數(shù)世界添磚加瓦――Excel 2019新增函數(shù)
實例150 根據(jù)成績判斷對應的等級228
實例151 多條件求最高銷售金額229
實例152 多條件求最低價格230
實例153 合并各月份的業(yè)績數(shù)據(jù)232
實例154 提取各姓名對應的分數(shù)233
實例155 根據(jù)部門提取合并對應的人員名單234
實例156 將文本字符串中的數(shù)字進行排序235
第11章 函數(shù)中的隱形絕招――三維引用技術
實例157 統(tǒng)計各科成績總分的最高分239
實例158 統(tǒng)計各月銷量占總銷量的百分比240
實例159 將訂單按數(shù)量進行拆分241
實例160 統(tǒng)計各個日期最高平均數(shù)量及對應城市243
第12章 啥事兒都能幫上忙――函數(shù)與Excel其他功能結合
實例161 標記出第4~6名的分數(shù)246
實例162 隔日標記日期記錄247
實例163 用顏色動態(tài)標記活動單元格所在行的數(shù)據(jù)248
實例164 用數(shù)據(jù)條標記未來3天即將到期的記錄251
實例165 動態(tài)標記日歷253
實例166 在數(shù)據(jù)透視表中根據(jù)匯總金額計算提成金額256
實例167 利用數(shù)據(jù)驗證制作二級下拉菜單257
實例168 利用數(shù)據(jù)驗證驗證身份證號259
實例169 篩選包含“變速箱”且全年總銷量大于或等于350件的商品260
實例170 統(tǒng)計最高總分及對應姓名262
實例171 動態(tài)引用查詢系統(tǒng)的圖片263
實例172 動態(tài)獲取合并計算的數(shù)據(jù)源264
實例173 制作動態(tài)圖表266
實例174 使用表單控件制作動態(tài)圖表268