Excel函數(shù)活用范例大辭典

函數(shù)分類目錄
Chapter 01 邏輯函數(shù)應用實例 1
◎真假值判斷函數(shù) 2
案例001 判斷員工性別信息是否正確【TRUE()、FALSE()】 2
案例002 判斷員工是否全勤【AND()】 3
案例003 判斷員工是否可以直接晉升【AND()】 5
案例004 判斷員工身份證號碼位數(shù)是否正確【AND()】 6
案例005 根據(jù)年齡判斷員工是否退休【OR()】 8
案例006 判斷員工是否符合晉升條件【AND()】 9
案例007 判斷員工是否升職【NOT()】 11
案例008 統(tǒng)計月利潤中文本型數(shù)字的個數(shù)【NOT()】 12
案例009 判斷商品的盈利情況是否良好【NOT()】 13
案例010 根據(jù)員工工資計算員工的個人所得稅【NOT()】 15
◎條件判斷函數(shù) 17
案例011 根據(jù)員工的各項考核成績判斷員工是否合格【IF()】 17
案例012 判斷員工是否需要補考【IF()】 18
案例013 判斷員工是否缺考【IF()】 20
案例014 查詢員工是否享受夜班補貼【IF()】 21
案例015 計算員工12月工資【IF()】 23
案例016 匯總商品的銷售額【IF()】 24
案例017 為辦公物資采購表建立歸類標簽【IF()】 27
案例018 在有學生全部缺考的情況下計算學生的平均成績【IF()】 28

Chapter 02 統(tǒng)計函數(shù)應用實例 30
◎計數(shù)函數(shù) 31
案例019 統(tǒng)計2月26日加班人次【COUNT()】 31
案例020 統(tǒng)計女裝訂單數(shù)大于500的商品數(shù)量【COUNT()】 32
案例021 統(tǒng)計月銷售量1 000元以下、利潤2 000元以上商品數(shù)【COUNT()】 34
案例022 統(tǒng)計特色商品數(shù)量【COUNT()】 35
案例023 統(tǒng)計銷售部總人數(shù)【COUNTA()】 36
案例024 自動生成員工編號【COUNTA()】 38
案例025 統(tǒng)計學生缺考總次數(shù)【COUNTBLANK()】 39
案例026 統(tǒng)計購買各種首飾的會員人數(shù)【COUNTIF()】 40
案例027 計算20～35歲的員工比例【COUNTIF()】 42
案例028 統(tǒng)計與公司兩項業(yè)務都有往來的客戶數(shù)【COUNTIF()】 44
案例029 統(tǒng)計語、數(shù)、外大于90分且總分大于480分的人數(shù)【COUNTIFS()】 45
案例030 統(tǒng)計業(yè)績200 000元以上的大專本科學歷男性業(yè)務員【COUNTIFS()】 46
案例031 統(tǒng)計各級業(yè)務員業(yè)績異常的人數(shù)【COUNTIFS()】 48
◎平均值函數(shù) 50
案例032 計算各季度的平均生產(chǎn)成本【AVERAGE()】 50
案例033 計算90分以上的平均成績【AVERAGE()】 51
案例034 計算學生含缺考科目的平均成績【AVERAGEA()】 52
案例035 計算4月份交貨產(chǎn)品的平均訂單金額【AVERAGEIF()】 54
案例036 計算會員在金飾上的平均消費【AVERAGEIF()】 55
案例037 計算金融系講師平均授課課時【AVERAGEIFS()】 57
案例038 計算滿足特定條件的教師的科研能力平均分【AVERAGEIFS()】 58
案例039 去除最值求全班跳遠平均成績【AVERAGEIFS()】 60
案例040 去掉一個最高分和最低分求參賽選手平均得分【TRIMMEAN()】 62
◎最值函數(shù) 63
案例041 突出顯示銷量最高的數(shù)據(jù)【MAX()】 63
案例042 求月銷售量低于平均銷量的最大銷量的商品名稱【MAX()】 66
案例043 分別求1～4月排前3的銷售量【LARGE()】 67
案例044 制作產(chǎn)品一季度銷量排行榜【LARGE()】 69
案例045 求最后購買某種首飾的會員【LARGE()】 71
案例046 計算工齡最小的女員工的工齡【MIN()】 72
案例047 計算材料的3個最少消耗量【SMALL()】 73
案例048 提取每月銷量倒數(shù)第二的商品【SMALL()】 75
◎中值函數(shù) 76
案例049 計算3月份產(chǎn)品銷量中值【MEDIAN()】 76
案例050 查找銷售量處于中間位置的部門【MEDIAN()】 78
◎排名函數(shù) 76
案例051 對員工上半年銷售業(yè)績進行排名【RANK()】 80
案例052 查詢指定學生的成績排名【RANK.AVG()】 82
案例053 求同齡異性員工的人數(shù)【RANK()】 86
◎頻率分布函數(shù) 88
案例054 求跳遠測試中出現(xiàn)次數(shù)最多的成績【MODE()】 88
案例055 查詢材料采購最頻繁的車間【MODE.MULT()】 91
案例056 統(tǒng)計各個利潤區(qū)間的商品數(shù)目【FREQUENCY()】 92
案例057 分別統(tǒng)計未完成任務和超額完成任務的人數(shù)【FREQUENCY()】 94
案例058 分別統(tǒng)計本科以下及本科以上學歷員工數(shù)【FREQUENCY()】 96
案例059 統(tǒng)計各學歷員工數(shù)【FREQUENCY()】 97
案例060 求員工銷售業(yè)績的中國式排名【FREQUENCY()】 99
◎概率分布函數(shù) 102
案例061優(yōu)選機器維修人員招聘方案【BINOM.DIST()】 102
案例062 提供可供選擇的機床維修人員配置方案【BINOM.DIST()】 104

Chapter 03 數(shù)學函數(shù)應用實例 110
◎求和函數(shù) 111
案例063 計算所有生產(chǎn)線的總產(chǎn)量【SUM()】 111
案例064 計算簽單總額在300 000元以上的總金額【SUM()】 113
案例065 計算銷售部當月的實發(fā)工資總和【SUM()】 115
案例066 計算銷售量排前三的商品總銷量【SUM()】 117
案例067 高斯求和【SUM()】 119
案例068 匯總女裝訂單表中已收到的定金總額【SUMIF()】 120
案例069 匯總跳遠成績在1.9米以上的學生人數(shù)【SUMIF()】 122
案例070 計算工資在8 000元以上的員工工資總和【SUMIF()】 125
案例071 計算提成在1 500到2 000的員工工資總和【SUMIF()】 126
案例072 統(tǒng)計會員在項鏈上的總消費【SUMIF()】 129
案例073 統(tǒng)計會員在項鏈和手鐲上的總消費【SUMIF()】 130
案例074 匯總選中單元格所在行100元以上補貼總額【SUMIF()】 132
案例075 計算售價10元以下、銷量200以上的商品總利潤【SUMIFS()】 134
案例076 統(tǒng)計銷量300以上的竹鹽系列產(chǎn)品總利潤【SUMIFS()】 135
案例077 計算滿足一定條件的一、二級教師工作量之差【SUMIFS()】 137
案例078 計算低值易耗品采購總金額【SUMPRODUCT()】 138
案例079 計算男性員工人數(shù)【SUMPRODUCT()】 140
案例080 統(tǒng)計購買鉑金戒指的男性會員人數(shù)【SUMPRODUCT()】 142
案例081 匯總男性會員在首飾上的總消費【SUMPRODUCT()】 143
案例082 匯總教學能力85分以上的一級教師總工作量【SUMPRODUCT()】 144
◎隨機數(shù)函數(shù) 146
案例083 隨機生成抽檢產(chǎn)品的序號【RAND()】 146
案例084 制作隨機順序的面試次序表【RAND()】 149
案例085 將三個部門的員工隨機分組【RAND()】 153
案例086 隨機抽取5名員工的編號【RANDBETWEEN()】 157
案例087 隨機抽取一名員工工資數(shù)據(jù)【RANDBETWEEN()】 160
◎取舍函數(shù) 163
案例088 將跳遠平均成績保留兩位小數(shù)【ROUND()】 163
案例089 以“萬元”為單位計算當月的產(chǎn)品銷售總利潤【ROUND()】 165
案例090 匯總數(shù)量計量單位不同的金額【ROUND()】 166
案例091 將員工實發(fā)工資保留“角”，忽略“分”【ROUNDDOWN()】 167
案例092 計算順利完成業(yè)務需要的人數(shù)【ROUNDUP()】 169
案例093 計算發(fā)放工資所需各種面額鈔票的數(shù)量【INT()】 170
案例094 給通訊錄中的數(shù)據(jù)編號【INT()】 173
案例095 計算員工年限工資【CEILING()】 174
案例096 計算可以組建的業(yè)務小組的個數(shù)【FLOOR()】 177
案例097 計算員工的提成工資【FLOOR()】 179
案例098 制作商品簡易標簽【TRUNC()】 180
◎求積函數(shù) 183
案例099 計算車間每小時生產(chǎn)產(chǎn)值【PRODUCT()】 183
案例100 跨表計算總產(chǎn)值【PRODUCT()】 184
案例101 計算超市打折活動的總營業(yè)額【MMULT()】 186
案例102 制作中文九九乘法表【MMULT()】 187
◎商與余數(shù)函數(shù) 189
案例103 根據(jù)出生年月推算員工生肖【MOD()】 189
案例104為員工檔案奇偶行設置不同的格式【MOD()】 191
案例105 按梯度計算員工提成工資【QUOTIENT()】 193
◎排列組合函數(shù) 195
案例106 預計象棋比賽的總場次【COMBIN()】 195
案例107 計算兩種彩票的中頭獎概率【COMBIN()】 197
◎分類匯總函數(shù) 195
案例108 求所有商品的平均銷量【SUBTOTAL()】 198
案例109 計算隱藏某些商品時的平均利潤【SUBTOTAL()】 200

Chapter 04 日期和時間函數(shù)應用實例 202
◎日期函數(shù) 203
案例110 計算員工工齡【YEAR()】 203
案例111 計算2010年之前空調的平均售價【YEAR()】 206
案例112 計算本月交貨的產(chǎn)品數(shù)量【MONTH()】 207
案例113 計算工程的起止年月【MONTH()】 210
案例114 計算員工的轉正時間【MONTH()】 213
案例115 計算產(chǎn)品的交貨時間處于哪一旬【DAY()】 215
案例116 制作2012年法定假日倒計時【TODAY()】 217
案例117 判斷借款是否逾期【DAYS360()】 219
案例118 將簡易輸入的日期轉換為標準日期【DATE()】 221
案例119 計算付款日期【DATE()】 223
案例120 計算當月天數(shù)【DATE()】 225
案例121 顯示本次打開檔案的時間【NOW()】 226
案例122 判斷機器是否需要檢修【NOW()】 228
案例123 自動生成離職員工工資結算日期【EOMONTH()】 230
案例124 計算支付貨款日期【EDATE()】 232
案例125 計算員工離職之前在公司工作的月數(shù)【DATEDIF()】 235
案例126 根據(jù)入職日期計算員工帶薪年假的天數(shù)【DATEDIF()】 236
◎工作日函數(shù) 238
案例127計算工作的完成時間【W(wǎng)ORKDAY()】 238
案例128 計算本月的工作日數(shù)目【NETWORKDAYS()】 241
◎星期函數(shù) 242
案例129 計算法定假日的星期【W(wǎng)EEKDAY()】 242
案例230 計算付款時間【W(wǎng)EEKDAY()】 246
案例231 計算某員工每日的工資【W(wǎng)EEKDAY()】 248
案例132 按周匯總產(chǎn)品出入庫數(shù)量【W(wǎng)EEKNUM()】 250
案例133 自動生成考勤周報表的標題【W(wǎng)EEKNUM()】 252
案例134 羅列第45周的日期【W(wǎng)EEKNUM()】 254
◎時間函數(shù) 242
案例135 計算臨時加班的員工的工資【TIMEVALUE()】 256
案例136 計算員工今日的計時工資【HOUR()】 259
案例137 計算員工的平均速度【MINUTE()】 261
案例138 根據(jù)完成任務時間計算獎懲金額【MINUTE()】 264
案例139 根據(jù)通信時間計算本月總話費【SECOND()】 267

Chapter 05 文本函數(shù)應用實例 270
◎字符串轉換函數(shù) 271
案例140 換行顯示庫存量及是否需要進貨【CHAR()】 271
案例141 將文本數(shù)字轉換為數(shù)值并計算利潤【VALUE()】 273
案例142 從身份證號碼中提取員工的性別信息【TEXT()】 275
案例143 從身份證號碼中提取員工的生日信息【TEXT()】 277
案例144 快速恢復以科學計數(shù)法顯示的商品條形碼【TEXT()】 279
案例145 將數(shù)字金額轉換為人民幣大寫【TEXT()】 281
案例146 將日期轉換為中文大寫【TEXT()】 284
案例147將員工提成工資轉換為會計格式【TEXT()】 285
案例148 根據(jù)工作年限計算員工年終獎【TEXT()】 287
案例149 快速將數(shù)字轉換為電話格式【TEXT()】 289
案例150 將全角字符轉換為半角字符【ASC()】 291
案例151 計算混合字符串之中字母的個數(shù)【ASC()】 293
案例152 將半角字符轉換為全角字符【W(wǎng)IDECHAR()】 294
案例152 將半角字符轉換為全角字符【W(wǎng)IDECHAR()】 294
案例153 計算混合字符串之中漢字的個數(shù)【W(wǎng)IDECHAR()】 295
案例154 將單詞的首字母轉換為大寫【PROPER()】 296
案例155 將所有單詞轉換為小寫形式【LOWER()】 298
案例156 將英文句子的首字母轉換為大寫其余小寫的形式【LOWER()】 299
案例157 將所有單詞轉換為大寫【UPPER()】 301
案例158 統(tǒng)計軟件名稱中英文字母的個數(shù)【UPPER()】 302
案例159 將區(qū)域中的文本串聯(lián)【T()】 304
案例160 為公式添加說明【T()】 306
案例161 用簡易圖表表示銷量情況【REPT()】 307
◎文本合并比較函數(shù) 309
案例162 在數(shù)值結果左側添加“￥”符號【CONCATENATE()】 309
案例163 在身份證號碼中提取員工的出生年月信息【CONCATENATE()】 310
案例164 提取月利潤最高的前三種產(chǎn)品的名稱【CONCATENATE()】 311
案例165 判斷實習員工是否可以繼續(xù)考察【EXACT()】 313
◎查找與替換函數(shù) 315
案例166 根據(jù)產(chǎn)品規(guī)格計算產(chǎn)品的體積【FIND()】 315
案例167 通訊錄單列轉3列【FIND()】 317
案例168 將15位身份證號碼升級為18位【REPLACE()】 319
案例169 升級產(chǎn)品型號【REPLACE()】 322
案例170 將專業(yè)年級班級名稱分開【SEARCH()】 323
案例171 從軟件名稱中提取軟件的版本號【SEARCH()】 324
案例172 統(tǒng)計各部門的員工人數(shù)【SEARCH()】 326
案例173 提取姓名和省名【SUBSTITUTE()】 328
◎獲取字符串函數(shù) 330
案例174 從地址中提取省名【LEFT()】 330
案例175 通過身份證號碼計算年齡【LEFT()】 331
案例176 將金額分散填充【LEFT()】 333
案例177 從開支明細中提取金額【MID()】 335
案例178 從卡機數(shù)據(jù)中判斷員工所屬分公司及部門【RIGHT()】 337
案例179 從卡機數(shù)據(jù)中提取時間并判斷是否遲到【RIGHT()】 339
案例180 根據(jù)身份證號碼統(tǒng)計男性員工人數(shù)【RIGHT()】 340
案例181 在不規(guī)范格式的日期數(shù)據(jù)中提取日期【RIGHT()】 342

Chapter 06 財務函數(shù)應用實例 344
◎投資預算函數(shù) 345
案例182 整存整取【FV()】 345
案例183 零存整取【FV()】 346
案例184 計算各項投資的金額【PV()】 347
案例185 年金現(xiàn)值的計算【PV()】 348
案例186 不同計息期現(xiàn)值的計算【PV()】 350
案例187 設備更新投資分析【PV()】 352
案例188 選用甲設備的最低使用年限【NPER()】 355
案例189 求銀行存款年利率【RATE()】 356
◎本金和利息函數(shù) 357
案例190 償債基金的計算【PMT()】 357
案例191 固定期限的銀行貸款分析【PMT()】 359
案例192 求等額分期付款每期的本、息及本息和【PPMT()】 361
案例193 求等額本金付款每期的本、息及本息和【ISPMT()】 363
案例194 選擇最佳還貸方案【ISPMT()】 366
◎折舊函數(shù) 370
案例195 使用直線折舊法計算固定資產(chǎn)折舊【SLN()】 370
案例196 使用固定余額遞減法計算資產(chǎn)折舊【DB()】 374
案例197 使用雙倍余額遞減法計算資產(chǎn)折舊【DDB()】 377
案例198 使用年數(shù)總和法計算資產(chǎn)折舊【SYD()】 381
案例199 使用可變余額遞減法計算資產(chǎn)折舊【VDB()】 384

Chapter 07 查找和引用函數(shù)應用實例 387
◎查找函數(shù) 388
案例200 查詢指定員工缺勤次數(shù)【VLOOKUP()】 388
案例201 根據(jù)考核成績評定員工考核等級【VLOOKUP()】 389
案例202 根據(jù)員工姓名查找員工編號【VLOOKUP()】 391
案例203 查詢指定廠家提供的某種產(chǎn)品的價格【VLOOKUP()】 393
案例204 查詢商品最新單價【VLOOKUP()】 394
案例205 根據(jù)員工編號或者姓名查找提成工資【VLOOKUP()】 396
案例206 根據(jù)員工姓名和編號查找員工信息【VLOOKUP()】 399
案例207 根據(jù)職務和工齡計算年限工資【VLOOKUP()】 401
案例208 根據(jù)產(chǎn)品名稱的拼音生成產(chǎn)品編號【VLOOKUP()】 403
案例209 根據(jù)本月收入查詢應采用稅率和速算扣除數(shù)【HLOOKUP()】 407
案例210 查找某銷售員某月的銷售業(yè)績【HLOOKUP()】 408
案例211 制作員工工牌【HLOOKUP()】 410
案例212 查詢最后入職的員工【MATCH()】 412
案例213 查詢某員工是否為本公司人員【MATCH()】 413
案例214 計算員工的工作項數(shù)【MATCH()】 415
案例215 制作周營業(yè)額排行榜【MATCH()】 417
案例216 根據(jù)員工考核成績進行排名【MATCH()】 421
案例217 羅列各省參展名單【MATCH()】 423
案例218 羅列本周多次遲到的員工【MATCH()】 426
案例219 根據(jù)庫存編號查詢庫存信息【LOOKUP()】 428
案例220 查詢員工應得福利費【LOOKUP()】 431
案例221 計算員工測評總分【LOOKUP()】 433
案例222 查詢六月值夜班最多的員工【LOOKUP()】 435
案例223 羅列每個名次上的員工姓名【LOOKUP()】 436
案例224 根據(jù)達標率計算不同工種員工的獎金【LOOKUP()】 439
案例225 根據(jù)關鍵字查找所有符合的商品【LOOKUP()】 441
案例226 根據(jù)員工檔案詳表制作簡表【CHOOSE()】 446
案例227 提取格式混亂的日期中的月份【CHOOSE()】 448
案例228 計算業(yè)務員的業(yè)績獎金【CHOOSE()】 450
◎引用函數(shù) 454
案例229 為員工檔案填充序號【ROW()】 454
案例230 求員工第幾次的測試成績最好【ROW()】 456
案例231 將中文日期轉換為標準格式日期【ROW()】 458
案例232 每10種商品匯總一次利潤【ROW()】 461
案例233 為上半年生產(chǎn)統(tǒng)計表中填充周次【CLOUMN()】 463
案例234 統(tǒng)計員工的缺勤情況【CLOUMN()】 464
案例235 拆分合并單元格【INDIRECT()】 467
案例236 統(tǒng)計某公司某周的工作事項【INDIRECT()】 469
案例237 多區(qū)域統(tǒng)計業(yè)績50萬元以上的銷售員人數(shù)【INDIRECT()】 471
案例238 使求3個車間的最高平均產(chǎn)量【INDIRECT()】 473
案例239 求最大簽單業(yè)績所在單元格【ADDRESS()】 474
案例240 求指定車間的最大產(chǎn)量【ADDRESS()】 475
案例241 匯總簽單業(yè)績【OFFSET()】 477
案例242 按品種編號【OFFSET()】 479
案例243 根據(jù)單價表和銷量表匯總銷售額【OFFSET()】 481
案例244 查詢員工福利相關信息【INDEX()】 483
案例245 計算最低購入價格【INDEX()】 485
案例246 查詢員工檔案中的最后一個記錄【INDEX()】 486
案例247 交換表格行列【TRANSPOSE()】 489
◎超鏈接函數(shù) 490
案例248 快速返回工作表首行【HYPERLINK()】 490
案例249 快速跳轉至員添加信息處【HYPERLINK()】 492
案例250 在工作表中直接使用百度搜索【HYPERLINK()】 494

Chapter 08 信息函數(shù)應用實例 496
◎IS()類函數(shù) 497
案例251 查詢指定員工最大缺勤次數(shù)【ISNA()】 497
案例252 轉換面試結果表達方式【ISTEXT()】 499
案例253 標記考核成績達到A級的員工【ISNUMBER()】 503
案例254 按編號匯總庫存量【ISNUMBER()】 505
案例255 統(tǒng)計男性員工人數(shù)【ISEVEN()】 506
案例256 統(tǒng)計一季度出入庫總量【ISODD()】 508
案例257 求員工服務態(tài)度平均分【ISBLANK()】 510
案例258 統(tǒng)計字符串中不重復的字母個數(shù)【ISERROR()】 512
案例259 羅列參與兩個項目和一個項目的員工【ISERROR()】 514
案例260 統(tǒng)計生產(chǎn)人員人數(shù)【ISERR()】 517
◎信息類函數(shù) 520
案例261 顯示當前工作表路徑【CELL()】 520
案例262 計算員工累計積分【N()】 522
案例263 從明細表中提取數(shù)據(jù)【N()】 523
案例264 從未知格式日期中提取月份【TYPE()】 527

Chapter 09 數(shù)據(jù)庫和三角函數(shù)應用實例 530
◎數(shù)據(jù)庫函數(shù) 531
案例265 統(tǒng)計各車間平均生產(chǎn)成本【DAVERAGE()】 531
案例266 統(tǒng)計竹鹽產(chǎn)品平均售價【DAVERAGE()】 533
案例267 統(tǒng)計一、二車間第二季度生產(chǎn)總成本【DSUM()】 535
案例268 求滿足條件的商品的總利潤【DSUM()】 537
案例269 求2班的最高平均分【DMAX()】 539
案例270 求平均工齡以下員工的最高工資【DMAX()】 541
案例271 判斷可否選派二級教師【DMIN()】 545
案例272 查詢符合條件的員工姓名【DGET()】 547
案例273 統(tǒng)計所有考核均合格的員工數(shù)【DCOUNTA()】 548
◎三角函數(shù) 551
案例274 計算定制鍋爐的直徑【PI()】 551
案例275 計算扇形材料的弧長【RADIANS()】 552
案例276 繪制簡單三角函數(shù)圖像【SIN()】 554
案例277 繪制復雜三角函數(shù)圖像【COS()】 556
案例278 判斷三角形材料是否滿足條件【ACOS()】 558

Chapter 10 函數(shù)與其他Excel功能結合應用 560
◎條件格式中函數(shù)的應用 561
案例279 使工作表自動隱藏錯誤值 561
案例280 標記銷量最大的10種商品名稱 562
案例281 在高于平均成績的姓名后添加標識 565
案例282 3天內(nèi)行程提示 568
案例283 根據(jù)關鍵字標識商品信息 570
案例284 多條件控制工資的顯示 571
◎數(shù)據(jù)有效性中函數(shù)的應用 576
案例285 禁止輸入重復的員工編號 576
案例286 多條件限制員工編號輸入 578
案例287 利用數(shù)據(jù)有效性生成二級列表 582
案例288 四級下拉列表的制作 586
案例289 將已輸入的數(shù)據(jù)作為數(shù)據(jù)源 593
◎圖表中函數(shù)的應用 591
案例290 制作一季度產(chǎn)品的動態(tài)銷量圖 596
案例291 制作區(qū)域銷量的動態(tài)子母圖 598
案例292 制作銷量的動態(tài)分類統(tǒng)計圖 604
案例293 制作最近一周的銷量圖 606
案例294 制作收支瀑布圖 609
案例295 制作可比較任意產(chǎn)品產(chǎn)量的柱形圖 612
案例296 制作細分柱形圖 616
案例297制作雙色柱形圖 618
案例298 制作動態(tài)復合餅圖 621
◎模擬分析中函數(shù)的應用 561
案例299 求可變利率下每月的還貸額 626
案例300 求不同利率和還款期限下的月還款額 628
案例301 貸款方案分析 630
案例302 應聘員工工資要求分析 633
◎規(guī)劃求解中函數(shù)的應用 561
案例303 確定產(chǎn)品的最佳運輸方案 635
案例304 確定最佳生產(chǎn)方案 638
◎迭代計算中函數(shù)的應用 561
案例305自動記錄填寫產(chǎn)品入庫記錄的時間 642
案例306羅列鋁材下料方案 645

附錄A 函數(shù)語法格式速查 649
1． 邏輯函數(shù) 649
2． 統(tǒng)計函數(shù) 649
3． 數(shù)學和三角函數(shù) 654
4． 日期和時間函數(shù) 656
5． 文本函數(shù) 656
6． 財務函數(shù) 658
7． 查找和引用函數(shù) 661
8． 信息函數(shù) 662
9． 數(shù)據(jù)庫函數(shù) 663


行業(yè)應用目錄
人事
案例001 判斷員工性別信息是否正確【TRUE()、FALSE()】 2
案例002 判斷員工是否全勤【AND()】 3
案例003 判斷員工是否可以直接晉升【AND()】 5
案例004 判斷員工身份證號碼位數(shù)是否正確【AND()】 6
案例005 根據(jù)年齡判斷員工是否退休【OR()】 8
案例006 判斷員工是否符合晉升條件【AND()】 9
案例007 判斷員工是否升職【NOT()】 11
案例011 根據(jù)員工的各項考核成績判斷員工是否合格【IF()】 17
案例012 判斷員工是否需要補考【IF()】 18
案例013 判斷員工是否缺考【IF()】 20
案例014 查詢員工是否享受夜班補貼【IF()】 21
案例019 統(tǒng)計2月26日加班人次【COUNT()】 31
案例020 統(tǒng)計女裝訂單數(shù)大于500的商品數(shù)量【COUNT() 32
案例024 自動生成員工編號【COUNTA()】 38
案例027 計算20～35歲的員工比例【COUNTIF()】 42
案例028 統(tǒng)計與公司兩項業(yè)務都有往來的客戶數(shù)【COUNTIF()】 44
案例030 統(tǒng)計業(yè)績200 000元以上的大專本科學歷男性業(yè)務員【COUNTIFS()】 46
案例040 去掉一個最高分和最低分求參賽選手平均得分【TRIMMEAN()】 62
案例046 計算工齡最小的女員工的工齡【MIN()】 72
案例053 求同齡異性員工的人數(shù)【RANK()】 86
案例058 分別統(tǒng)計本科以下及本科以上學歷員工數(shù)【FREQUENCY()】 96
案例059 統(tǒng)計各學歷員工數(shù)【FREQUENCY()】 97
案例061優(yōu)選機器維修人員招聘方案【BINOM.DIST()】 102
案例077 計算滿足一定條件的一、二級教師工作量之差【SUMIFS()】 137
案例079 計算男性員工人數(shù)【SUMPRODUCT()】 140
案例084 制作隨機順序的面試次序表【RAND()】 149
案例085 將三個部門的員工隨機分組【RAND()】 153
案例086 隨機抽取5名員工的編號【RANDBETWEEN()】 157
案例092 計算順利完成業(yè)務需要的人數(shù)【ROUNDUP()】 169
案例094 給通訊錄中的數(shù)據(jù)編號【INT()】 173
案例096 計算可以組建的業(yè)務小組的個數(shù)【FLOOR()】 177
案例097 計算員工的提成工資【FLOOR()】 179
案例103 根據(jù)出生年月推算員工生肖【MOD()】 189
案例104為員工檔案奇偶行設置不同的格式【MOD()】 191
案例106 預計象棋比賽的總場次【COMBIN()】 195
案例110 計算員工工齡【YEAR()】 203
案例114 計算員工的轉正時間【MONTH()】 213
案例116 制作2012年法定假日倒計時【TODAY()】 217
案例118 將簡易輸入的日期轉換為標準日期【DATE()】 221
案例125 計算員工離職之前在公司工作的月數(shù)【DATEDIF()】 235
案例126 根據(jù)入職日期計算員工帶薪年假的天數(shù)【DATEDIF()】 236
案例128 計算本月的工作日數(shù)目【NETWORKDAYS()】 241
案例129 計算法定假日的星期【W(wǎng)EEKDAY()】 242
案例133 自動生成考勤周報表的標題【W(wǎng)EEKNUM()】 252
案例134 羅列第45周的日期【W(wǎng)EEKNUM()】 254
案例142 從身份證號碼中提取員工的性別信息【TEXT()】 275
案例143 從身份證號碼中提取員工的生日信息【TEXT()】 277
案例149 快速將數(shù)字轉換為電話格式【TEXT()】 289
案例163 在身份證號碼中提取員工的出生年月信息【CONCATENATE()】 310
案例165 判斷實習員工是否可以繼續(xù)考察【EXACT()】 313
案例167 通訊錄單列轉3列【FIND()】 317
案例168 將15位身份證號碼升級為18位【REPLACE()】 319
案例169 升級產(chǎn)品型號【REPLACE()】 322
案例172 統(tǒng)計各部門的員工人數(shù)【SEARCH()】 326
案例173 提取姓名和省名【SUBSTITUTE()】 328
案例174 從地址中提取省名【LEFT()】 330
案例175 通過身份證號碼計算年齡【LEFT()】 331
案例152 將半角字符轉換為全角字符【W(wǎng)IDECHAR()】 294
案例179 從卡機數(shù)據(jù)中提取時間并判斷是否遲到【RIGHT()】 339
案例180 根據(jù)身份證號碼統(tǒng)計男性員工人數(shù)【RIGHT()】 340
案例181 在不規(guī)范格式的日期數(shù)據(jù)中提取日期【RIGHT()】 342
案例200 查詢指定員工缺勤次數(shù)【VLOOKUP()】 388
案例201 根據(jù)考核成績評定員工考核等級【VLOOKUP()】 389
案例202 根據(jù)員工姓名查找員工編號【VLOOKUP()】 391
案例206 根據(jù)員工姓名和編號查找員工信息【VLOOKUP()】 399
案例207 根據(jù)職務和工齡計算年限工資【VLOOKUP()】 401
案例211 制作員工工牌【HLOOKUP()】 410
案例212 查詢最后入職的員工【MATCH()】 412
案例213 查詢某員工是否為本公司人員【MATCH()】 413
案例214 計算員工的工作項數(shù)【MATCH()】 415
案例216 根據(jù)員工考核成績進行排名【MATCH()】 421
案例217 羅列各省參展名單【MATCH()】 423
案例218 羅列本周多次遲到的員工【MATCH()】 426
案例221 計算員工測評總分【LOOKUP()】 433
案例222 查詢六月值夜班最多的員工【LOOKUP()】 435
案例223 羅列每個名次上的員工姓名【LOOKUP()】 436
案例226 根據(jù)員工檔案詳表制作簡表【CHOOSE()】 446
案例227 提取格式混亂的日期中的月份【CHOOSE()】 448
案例229 為員工檔案填充序號【ROW()】 454
案例230 求員工第幾次的測試成績最好【ROW()】 456
案例234 統(tǒng)計員工的缺勤情況【CLOUMN()】 464
案例236 統(tǒng)計某公司某周的工作事項【INDIRECT()】 469
案例246 查詢員工檔案中的最后一個記錄【INDEX()】 486
案例247 交換表格行列【TRANSPOSE()】 489
案例248 快速返回工作表首行【HYPERLINK()】 490
案例249 快速跳轉至員添加信息處【HYPERLINK()】 492
案例250 在工作表中直接使用百度搜索【HYPERLINK()】 494
案例251 查詢指定員工最大缺勤次數(shù)【ISNA()】 497
案例252 轉換面試結果表達方式【ISTEXT()】 499
案例253 標記考核成績達到A級的員工【ISNUMBER()】 503
案例255 統(tǒng)計男性員工人數(shù)【ISEVEN()】 506
案例257 求員工服務態(tài)度平均分【ISBLANK()】 510
案例260 統(tǒng)計生產(chǎn)人員人數(shù)【ISERR()】 517
案例261 顯示當前工作表路徑【CELL()】 520
案例262 計算員工累計積分【N()】 522
案例272 查詢符合條件的員工姓名【DGET()】 547
案例273 統(tǒng)計所有考核均合格的員工數(shù)【DCOUNTA()】 548
案例282 3天內(nèi)行程提示 568
案例285 禁止輸入重復的員工編號 576
案例286 多條件限制員工編號輸入 578
案例287 利用數(shù)據(jù)有效性生成二級列表 582
案例289 將已輸入的數(shù)據(jù)作為數(shù)據(jù)源 593

財務
案例015 計算員工12月工資【IF()】 23
案例016 匯總商品的銷售額【IF()】 24
案例017 為辦公物資采購表建立歸類標簽【IF()】 27
案例065 計算銷售部當月的實發(fā)工資總和【SUM()】 115
案例066 計算銷售量排前三的商品總銷量【SUM()】 117
案例070 計算工資在8 000元以上的員工工資總和【SUMIF()】 125
案例071 計算提成在1 500到2 000的員工工資總和【SUMIF()】 126
案例074 匯總選中單元格所在行100元以上補貼總額【SUMIF()】 132
案例075 計算售價10元以下、銷量200以上的商品總利潤【SUMIFS()】 134
案例076 統(tǒng)計銷量300以上的竹鹽系列產(chǎn)品總利潤【SUMIFS()】 135
案例078 計算低值易耗品采購總金額【SUMPRODUCT()】 138
案例087 隨機抽取一名員工工資數(shù)據(jù)【RANDBETWEEN()】 160
案例089 以“萬元”為單位計算當月的產(chǎn)品銷售總利潤【ROUND()】 165
案例090 匯總數(shù)量計量單位不同的金額【ROUND()】 166
案例091 將員工實發(fā)工資保留“角”，忽略“分”【ROUNDDOWN()】 167
案例093 計算發(fā)放工資所需各種面額鈔票的數(shù)量【INT()】 170
案例095 計算員工年限工資【CEILING()】 174
案例100 跨表計算總產(chǎn)值【PRODUCT()】 184
案例101 計算超市打折活動的總營業(yè)額【MMULT()】 186
案例105 按梯度計算員工提成工資【QUOTIENT()】 193
案例107 計算兩種彩票的中頭獎概率【COMBIN()】 197
案例108 求所有商品的平均銷量【SUBTOTAL()】 198
案例109 計算隱藏某些商品時的平均利潤【SUBTOTAL()】 200
案例117 判斷借款是否逾期【DAYS360()】 219
案例123 自動生成離職員工工資結算日期【EOMONTH()】 230
案例124 計算支付貨款日期【EDATE()】 232
案例230 計算付款時間【W(wǎng)EEKDAY()】 246
案例231 計算某員工每日的工資【W(wǎng)EEKDAY()】 248
案例135 計算臨時加班的員工的工資【TIMEVALUE()】 256
案例136 計算員工今日的計時工資【HOUR()】 259
案例138 根據(jù)完成任務時間計算獎懲金額【MINUTE()】 264
案例145 將數(shù)字金額轉換為人民幣大寫【TEXT()】 281
案例147將員工提成工資轉換為會計格式【TEXT()】 285
案例148 根據(jù)工作年限計算員工年終獎【TEXT()】 287
案例162 在數(shù)值結果左側添加“￥”符號【CONCATENATE()】 309
案例176 將金額分散填充【LEFT()】 333
案例177 從開支明細中提取金額【MID()】 335
案例182 整存整取【FV()】 345
案例183 零存整取【FV()】 346
案例184 計算各項投資的金額【PV()】 347
案例185 年金現(xiàn)值的計算【PV()】 348
案例186 不同計息期現(xiàn)值的計算【PV()】 350
案例187 設備更新投資分析【PV()】 352
案例188 選用甲設備的最低使用年限【NPER()】 355
案例189 求銀行存款年利率【RATE()】 356
案例190 償債基金的計算【PMT()】 357
案例191 固定期限的銀行貸款分析【PMT()】 359
案例192 求等額分期付款每期的本、息及本息和【PPMT()】 361
案例193 求等額本金付款每期的本、息及本息和【ISPMT()】 363
案例194 選擇最佳還貸方案【ISPMT()】 366
案例195 使用直線折舊法計算固定資產(chǎn)折舊【SLN()】 370
案例196 使用固定余額遞減法計算資產(chǎn)折舊【DB()】 374
案例197 使用雙倍余額遞減法計算資產(chǎn)折舊【DDB()】 377
案例198 使用年數(shù)總和法計算資產(chǎn)折舊【SYD()】 381
案例199 使用可變余額遞減法計算資產(chǎn)折舊【VDB()】 384
案例205 根據(jù)員工編號或者姓名查找提成工資【VLOOKUP()】 396
案例209 根據(jù)本月收入查詢應采用稅率和速算扣除數(shù)【HLOOKUP()】 407
案例220 查詢員工應得福利費【LOOKUP()】 431
案例224 根據(jù)達標率計算不同工種員工的獎金【LOOKUP()】 439
案例228 計算業(yè)務員的業(yè)績獎金【CHOOSE()】 450
案例231 將中文日期轉換為標準格式日期【ROW()】 458
案例232 每10種商品匯總一次利潤【ROW()】 461
案例237 多區(qū)域統(tǒng)計業(yè)績50萬元以上的銷售員人數(shù)【INDIRECT()】 471
案例244 查詢員工福利相關信息【INDEX()】 483
案例263 從明細表中提取數(shù)據(jù)【N()】 523
案例270 求平均工齡以下員工的最高工資【DMAX()】 541
案例279 使工作表自動隱藏錯誤值 561
案例284 多條件控制工資的顯示 571
案例294 制作收支瀑布圖 609
案例299求可變利率下每月的還貸額 626
案例300 求不同利率和還款期限下的月還款額 628
案例301 貸款方案分析 630
案例302 應聘員工工資要求分析 633

銷售
案例008 統(tǒng)計月利潤中文本型數(shù)字的個數(shù)【NOT()】 12
案例009 判斷商品的盈利情況是否良好【NOT()】 13
案例010 根據(jù)員工工資計算員工的個人所得稅【NOT()】 15
案例021 統(tǒng)計月銷售量1 000元以下、利潤2 000元以上商品數(shù)【COUNT()】 34
案例022 統(tǒng)計特色商品數(shù)量【COUNT()】 35
案例023 統(tǒng)計銷售部總人數(shù)【COUNTA()】 36
案例026 統(tǒng)計購買各種首飾的會員人數(shù)【COUNTIF()】 40
案例031 統(tǒng)計各級業(yè)務員業(yè)績異常的人數(shù)【COUNTIFS()】 48
案例035 計算4月份交貨產(chǎn)品的平均訂單金額【AVERAGEIF()】 54
案例036 計算會員在金飾上的平均消費【AVERAGEIF()】 55
案例041 突出顯示銷量最高的數(shù)據(jù)【MAX()】 63
案例042 求月銷售量低于平均銷量的最大銷量的商品名稱【MAX()】 66
案例043 分別求1～4月排前3的銷售量【LARGE()】 67
案例044 制作產(chǎn)品一季度銷量排行榜【LARGE()】 69
案例045 求最后購買某種首飾的會員【LARGE()】 71
案例048 提取每月銷量倒數(shù)第二的商品【SMALL()】 75
案例049 計算3月份產(chǎn)品銷量中值【MEDIAN()】 76
案例050查找銷售量處于中間位置的部門【MEDIAN()】 78
案例051 對員工上半年銷售業(yè)績進行排名【RANK()】 80
案例056 統(tǒng)計各個利潤區(qū)間的商品數(shù)目【FREQUENCY()】 92
案例057 分別統(tǒng)計未完成任務和超額完成任務的人數(shù)【FREQUENCY()】 94
案例060 求員工銷售業(yè)績的中國式排名【FREQUENCY()】 99
案例064 計算簽單總額在300 000元以上的總金額【SUM()】 113
案例068 匯總女裝訂單表中已收到的定金總額【SUMIF()】 120
案例072 統(tǒng)計會員在項鏈上的總消費【SUMIF()】 129
案例073 統(tǒng)計會員在項鏈和手鐲上的總消費【SUMIF()】 130
案例080 統(tǒng)計購買鉑金戒指的男性會員人數(shù)【SUMPRODUCT()】 142
案例081 匯總男性會員在首飾上的總消費【SUMPRODUCT()】 143
案例098 制作商品簡易標簽【TRUNC()】 180
案例111 計算2010年之前空調的平均售價【YEAR()】 206
案例112 計算本月交貨的產(chǎn)品數(shù)量【MONTH()】 207
案例115 計算產(chǎn)品的交貨時間處于哪一旬【DAY()】 215
案例119 計算付款日期【DATE()】 223
案例120 計算當月天數(shù)【DATE()】 225
案例121 顯示本次打開檔案的時間【NOW()】 226
案例132 按周匯總產(chǎn)品出入庫數(shù)量【W(wǎng)EEKNUM()】 250
案例139 根據(jù)通信時間計算本月總話費【SECOND()】 267
案例140 換行顯示庫存量及是否需要進貨【CHAR()】 271
案例141 將文本數(shù)字轉換為數(shù)值并計算利潤【VALUE()】 273
案例144 快速恢復以科學計數(shù)法顯示的商品條形碼【TEXT()】 279
案例146 將日期轉換為中文大寫【TEXT()】 284
案例150 將全角字符轉換為半角字符【ASC()】 291
案例151 計算混合字符串之中字母的個數(shù)【ASC()】 293
案例152 將半角字符轉換為全角字符【W(wǎng)IDECHAR()】 294
案例153 計算混合字符串之中漢字的個數(shù)【W(wǎng)IDECHAR()】 295
案例160 為公式添加說明【T()】 306
案例161 用簡易圖表表示銷量情況【REPT()】 307
案例164 提取月利潤最高的前三種產(chǎn)品的名稱【CONCATENATE()】 311
案例203 查詢指定廠家提供的某種產(chǎn)品的價格【VLOOKUP()】 393
案例204 查詢商品最新單價【VLOOKUP()】 394
案例208 根據(jù)產(chǎn)品名稱的拼音生成產(chǎn)品編號【VLOOKUP()】 403
案例210 查找某銷售員某月的銷售業(yè)績【HLOOKUP()】 408
案例215 制作周營業(yè)額排行榜【MATCH()】 417
案例219 根據(jù)庫存編號查詢庫存信息【LOOKUP()】 428
案例225 根據(jù)關鍵字查找所有符合的商品【LOOKUP()】 441
案例235 拆分合并單元格【INDIRECT()】 467
案例239 求最大簽單業(yè)績所在單元格【ADDRESS()】 474
案例240 求指定車間的最大產(chǎn)量【ADDRESS()】 475
案例241 匯總簽單業(yè)績【OFFSET()】 477
案例242 按品種編號【OFFSET()】 479
案例243 根據(jù)單價表和銷量表匯總銷售額【OFFSET()】 481
案例245 計算最低購入價格【INDEX()】 485
案例254 按編號匯總庫存量【ISNUMBER()】 505
案例256 統(tǒng)計一季度出入庫總量【ISODD()】 508
案例264 從未知格式日期中提取月份【TYPE()】 527
案例266 統(tǒng)計竹鹽產(chǎn)品平均售價【DAVERAGE()】 533
案例268 求滿足條件的商品的總利潤【DSUM()】 537
案例280 標記銷量最大的10種商品名稱 562
案例283 根據(jù)關鍵字標識商品信息 570
案例290 制作一季度產(chǎn)品的動態(tài)銷量圖 596
案例291 制作區(qū)域銷量的動態(tài)子母圖 598
案例292 制作銷量的動態(tài)分類統(tǒng)計圖 604
案例293 制作最近一周的銷量圖 606
案例296 制作細分柱形圖 616
案例297制作雙色柱形圖 618
案例298 制作動態(tài)復合餅圖 621
案例303 確定產(chǎn)品的最佳運輸方案 635

教學
案例018 在有學生全部缺考的情況下計算學生的平均成績【IF()】 28
案例025 統(tǒng)計學生缺考總次數(shù)【COUNTBLANK()】 39
案例029 統(tǒng)計語、數(shù)、外大于90分且總分大于480分的人數(shù)【COUNTIFS()】 45
案例033 計算90分以上的平均成績【AVERAGE()】 51
案例034 計算學生含缺考科目的平均成績【AVERAGEA()】 52
案例037 計算金融系講師平均授課課時【AVERAGEIFS()】 57
案例038 計算滿足特定條件的教師的科研能力平均分【AVERAGEIFS()】 58
案例039 去除最值求全班跳遠平均成績【AVERAGEIFS()】 60
案例052 查詢指定學生的成績排名【RANK.AVG()】 82
案例054 求跳遠測試中出現(xiàn)次數(shù)最多的成績【MODE()】 88
案例067 高斯求和【SUM()】 119
案例069 匯總跳遠成績在1.9米以上的學生人數(shù)【SUMIF()】 122
案例082 匯總教學能力85分以上的一級教師總工作量【SUMPRODUCT()】 144
案例088 將跳遠平均成績保留兩位小數(shù)【ROUND()】 163
案例170 將專業(yè)年級班級名稱分開【SEARCH()】 323
案例269 求2班的最高平均分【DMAX()】 539
案例271 判斷可否選派二級教師【DMIN()】 545
案例276 繪制簡單三角函數(shù)圖像【SIN()】 554
案例277 繪制復雜三角函數(shù)圖像【COS()】 556
案例281 在高于平均成績的姓名后添加標識 565

業(yè)務
案例113 計算工程的起止年月【MONTH()】 210
案例127計算工作的完成時間【W(wǎng)ORKDAY()】 238
案例154 將單詞的首字母轉換為大寫【PROPER()】 296
案例155 將所有單詞轉換為小寫形式【LOWER()】 298
案例156 將英文句子的首字母轉換為大寫其余小寫的形式【LOWER()】 299
案例157 將所有單詞轉換為大寫【UPPER()】 301
案例158 統(tǒng)計軟件名稱中英文字母的個數(shù)【UPPER()】 302
案例159 將區(qū)域中的文本串聯(lián)【T()】 304
案例258 統(tǒng)計字符串中不重復的字母個數(shù)【ISERROR()】 512
案例259 羅列參與兩個項目和一個項目的員工【ISERROR()】 514
案例288 四級下拉列表的制作 586

生產(chǎn)
案例032 計算各季度的平均生產(chǎn)成本【AVERAGE()】 50
案例047 計算材料的3個最少消耗量【SMALL()】 73
案例055 查詢材料采購最頻繁的車間【MODE.MULT()】 91
案例062 提供可供選擇的機床維修人員配置方案【BINOM.DIST()】 104
案例063 計算所有生產(chǎn)線的總產(chǎn)量【SUM()】 111
案例083 隨機生成抽檢產(chǎn)品的序號【RAND()】 146
案例099 計算車間每小時生產(chǎn)產(chǎn)值【PRODUCT()】 183
案例102 制作中文九九乘法表【MMULT()】 187
案例122 判斷機器是否需要檢修【NOW()】 228
案例137 計算員工的平均速度【MINUTE()】 261
案例166 根據(jù)產(chǎn)品規(guī)格計算產(chǎn)品的體積【FIND()】 315
案例171 從軟件名稱中提取軟件的版本號【SEARCH()】 324
案例233 為上半年生產(chǎn)統(tǒng)計表中填充周次【CLOUMN()】 463
案例237 使求3個車間的最高平均產(chǎn)量【INDIRECT()】 473
案例265 統(tǒng)計各車間平均生產(chǎn)成本【DAVERAGE()】 531
案例267 統(tǒng)計一、二車間第二季度生產(chǎn)總成本【DSUM()】 535
案例274 計算定制鍋爐的直徑【PI()】 551
案例275 計算扇形材料的弧長【RADIANS()】 552
案例278 判斷三角形材料是否滿足條件【ACOS()】 558
案例295 制作可比較任意產(chǎn)品產(chǎn)量的柱形圖 612
案例304 確定最佳生產(chǎn)方案 638
案例305自動記錄填寫產(chǎn)品入庫記錄的時間 642
案例306羅列鋁材下料方案 645