Excel函數(shù)活用范例大辭典（全新版 含盤）

函數(shù)分類目錄
Chapter 01 公式與函數(shù)必會知識和操作1
◎認識公式、函數(shù)及運算符2
No001 認識公式與函數(shù)的作用與聯(lián)系2
No002 運算符的類型及其用途2
No003 計算中的運算順序4

◎靈活處理數(shù)據(jù)計算中的各種引用5
No004 利用快捷鍵調整單元格的引用方式5
No005 處理跨表引用6
No006 處理跨工作簿引用8

◎數(shù)據(jù)計算中的互換準則與數(shù)據(jù)區(qū)分9
No007 用數(shù)值指代邏輯值的規(guī)則9
No008 用數(shù)學運算替代邏輯函數(shù)11
No009 區(qū)分空單元格與空文本13
No010 讓空白單元格不顯示為0 13

◎函數(shù)屏幕提示的妙用 15
No011 提示函數(shù)的語法結構15
No012 查看函數(shù)參數(shù)的意義17
No013 快速選定指定參數(shù)17
No014 快速獲取函數(shù)的幫助信息19

◎處理數(shù)據(jù)計算的方法 20
No015 計算公式結果的幾種方法20
No016 使用【F9】鍵查看公式結果21
No017 批量復制公式——按【Ctrl+Enter】組合鍵21
No018 批量復制公式——按【Ctrl+D】組合鍵23
No019 批量復制公式——拖動控制柄24
No020 批量復制公式——雙擊控制柄25
No021 批量復制公式——使用粘貼選項功能26

Chapter 02 數(shù)學函數(shù)應用實例 29
◎求和函數(shù) 30
案例001計算所有生產(chǎn)線的總產(chǎn)量【SUM()】30
案例002 計算簽單總額在300 000元以上的總金額【SUM()】32
案例003 計算銷售部當月的實發(fā)工資總和【SUM()】34
案例004 計算銷售量排前三的商品總銷量【SUM()】36
案例005 高斯求和【SUM()】38
案例006 匯總女裝訂單表中已收到的定金總額【SUMIF()】39
案例007 匯總跳遠成績在19米以上的學生人數(shù)【SUMIF()】41
案例008 計算工資在8 000元以上的員工工資總和【SUMIF()】44
案例009 計算提成在1 500~2 000元的員工工資總和【SUMIF()】45
案例010 統(tǒng)計會員在項鏈上的總消費【SUMIF()】48
案例011 統(tǒng)計會員在項鏈和手鐲上的總消費【SUMIF()】49
案例012 匯總選中單元格所在行100元以上補貼總額【SUMIF()】51
案例013 計算前三名和后三名的數(shù)據(jù)之和【SUMIF()】53
案例014 匯總鼠標所在列中大于7 500的生產(chǎn)量【SUMIF()】54
案例015 計算售價10元以下、銷量200以上的商品總利潤【SUMIFS()】56
案例016 統(tǒng)計銷量300以上的竹鹽系列產(chǎn)品總利潤【SUMIFS()】57
案例017 計算滿足一定條件的一、二級教師工作量之差【SUMIFS()】58
案例018 匯總銷售部和市場部遲到的人數(shù)【SUMIFS()】60
案例019 計算低值易耗品采購總金額【SUMPRODUCT()】61
案例020 計算男性員工人數(shù)【SUMPRODUCT()】63
案例021 統(tǒng)計購買鉑金戒指的男性會員人數(shù)【SUMPRODUCT()】64
案例022 匯總男性會員在首飾上的總消費【SUMPRODUCT()】66
案例023 匯總教學能力85分以上的一級教師總工作量【SUMPRODUCT()】67
案例024 匯總所有車間員工的實際開支費用【SUMPRODUCT()】69

◎隨機數(shù)函數(shù) 70
案例025 隨機生成抽檢產(chǎn)品的序號【RAND()】70
案例026制作隨機順序的面試次序表【RAND()】73
案例027 將三個部門的員工隨機分組【RAND()】77
案例028 隨機抽取5名員工的編號【RANDBETWEEN()】81
案例029 隨機抽取一名員工工資數(shù)據(jù)【RANDBETWEEN()】84

◎取舍函數(shù) 87
案例030 將跳遠平均成績保留兩位小數(shù)【ROUND()】87
案例031 以“萬元”為單位計算當月的產(chǎn)品銷售總利潤【ROUND()】89
案例032 匯總數(shù)量計量單位不同的金額【ROUND()】90
案例033 將員工實發(fā)工資保留“角”，忽略“分” 【ROUNDDOWN()】91
案例034 計算順利完成業(yè)務需要的人數(shù)【ROUNDUP()】93
案例035 計算發(fā)放工資所需各種面額鈔票的數(shù)量【INT()】94
案例036 給通訊錄中的數(shù)據(jù)編號【INT()】96
案例037 計算員工年限工資【CEILING()】98
案例038 計算可以組建的業(yè)務小組的個數(shù)【FLOOR()】101
案例039 計算員工的提成工資【FLOOR()】103
案例040 制作商品簡易標簽【TRUNC()】104

◎求積函數(shù) 106
案例041 計算車間每小時生產(chǎn)產(chǎn)值【PRODUCT()】106
案例042 跨表計算總產(chǎn)值【PRODUCT()】108
案例043 根據(jù)三邊求普通三角形的面積【PRODUCT()】109
案例044 計算超市打折活動的總營業(yè)額【MMULT()】110
案例045 制作中文九九乘法表【MMULT()】111
案例046 求不同單價下的利潤【MMULT()】113
案例047 計算C產(chǎn)品的最大入庫量【MMULT()】115
案例048 計算產(chǎn)品的累計入庫數(shù)【MMULT()】116

◎商與余數(shù)函數(shù) 117
案例049 根據(jù)出生年月推算員工生肖【MOD()】117
案例050 根據(jù)出生年月判斷性別【MOD()】119
案例051 生成循環(huán)序列【MOD()】120
案例052 為員工檔案奇偶行設置不同的格式【MOD()】121
案例053 制作工資條【MOD()】123
案例054 按梯度計算員工提成工資【QUOTIENT()】125

◎排列組合函數(shù) 127
案例055 預計象棋比賽的總場次【COMBIN()】127
案例056 計算兩種彩票的中頭獎概率【COMBIN()】128
案例057 計算中獎率【PERMUT()】129

◎分類匯總函數(shù) 130
案例058 求所有商品的平均銷量【SUBTOTAL()】130
案例059 計算隱藏某些商品時的平均利潤【SUBTOTAL()】132

Chapter 03 統(tǒng)計函數(shù)應用實例 134
◎計數(shù)函數(shù) 135
案例060 統(tǒng)計2月26日加班人次【COUNT()】135
案例061 統(tǒng)計女裝訂單數(shù)大于500的商品數(shù)量【COUNT()】136
案例062 統(tǒng)計月銷售量1 000以下、利潤2 000元以上商品數(shù)【COUNT()】138
案例063 統(tǒng)計特色商品數(shù)量【COUNT()】139
案例064 統(tǒng)計各部門職工的學歷情況【COUNT()】140
案例065 統(tǒng)計銷售部總人數(shù)【COUNTA()】142
案例066 自動生成員工編號【COUNTA()】143
案例067 統(tǒng)計學生缺考總次數(shù)【COUNTBLANK()】144
案例068 統(tǒng)計有效的調查問卷數(shù)目【COUNTBLANK()】145
案例069 統(tǒng)計購買各種首飾的會員人數(shù)【COUNTIF()】146
案例070 計算20～35歲的員工比例【COUNTIF()】148
案例071 統(tǒng)計與公司兩項業(yè)務都有往來的客戶數(shù)【COUNTIF()】150
案例072 統(tǒng)計語、數(shù)、外大于90分且總分大于480分的人數(shù)【COUNTIFS()】151
案例073 統(tǒng)計業(yè)績200 000元以上的大專或本科學歷男性業(yè)務員【COUNTIFS()】152
案例074 統(tǒng)計各級業(yè)務員業(yè)績異常的人數(shù)【COUNTIFS()】154

◎平均值函數(shù) 156
案例075 計算各季度的平均生產(chǎn)成本【AVERAGE()】156
案例076 計算90分以上的平均成績【AVERAGE()】157
案例077 計算學生含缺考科目的平均成績【AVERAGE()】158
案例078 統(tǒng)計銷量前N名的平均銷量【AVERAGE()】160
案例079 計算操作員的平均產(chǎn)量【AVERAGE()】161
案例080 計算4月份交貨產(chǎn)品的平均訂單金額【AVERAGEIF()】162
案例081 計算會員在金飾上的平均消費【AVERAGEIF()】163
案例082 計算金融系講師平均授課課時【AVERAGEIFS()】164
案例083 計算滿足特定條件的教師的科研能力平均分【AVERAGEIFS()】165
案例084 去除最值求全班跳遠平均成績【AVERAGEIFS()】167
案例085 去掉一個最高分和最低分求參賽選手平均得分【TRIMMEAN()】169

◎最值函數(shù) 171
案例086 突出顯示銷量最高的數(shù)據(jù)【MAX()】171
案例087 求月銷售量低于平均銷量的最大銷量的商品名稱【MAX()】173
案例088 分別求1～4月排前3的銷售量【LARGE()】175
案例089 制作產(chǎn)品一季度銷量排行榜【LARGE()】176
案例090 求最后購買某種首飾的會員【LARGE()】178
案例091 計算工齡最小的女員工的工齡【MIN()】179
案例092 計算材料的3個最少消耗量【SMALL()】180
案例093 提取每月銷量倒數(shù)第二的商品【SMALL()】182

◎中值函數(shù) 183
案例094 計算3月份產(chǎn)品銷量中值【MEDIAN()】183
案例095 設定社保繳費基數(shù)上下限【MEDIAN()】185
案例096 查找銷售量處于中間位置的部門【MEDIAN()】186

◎排名函數(shù) 188
案例097 對員工上半年銷售業(yè)績進行排名【RANK()】188
案例098 查詢指定學生的成績排名【RANKAVG()】190
案例099 求同齡異性員工的人數(shù)【RANK()】194

◎頻率分布函數(shù) 196
案例100 求跳遠測試中出現(xiàn)次數(shù)最多的成績【MODE()】196
案例101 查詢材料采購最頻繁的車間【MODEMULT()】198
案例102 統(tǒng)計各個利潤區(qū)間的商品數(shù)目【FREQUENCY()】200
案例103 分別統(tǒng)計未完成任務和超額完成任務的人數(shù)【FREQUENCY()】202
案例104分別統(tǒng)計本科以下及本科以上學歷的員工人數(shù)【FREQUENCY()】204
案例105 統(tǒng)計各學歷員工數(shù)【FREQUENCY()】205
案例106 求員工銷售業(yè)績的中國式排名【FREQUENCY()】207

◎概率分布函數(shù) 210
案例107 優(yōu)選機器維修人員招聘方案【BINOMDIST()】210
案例108 提供可供選擇的機床維修人員配置方案【BINOMDIST()】212

Chapter 04 日期和時間函數(shù)應用實例 217
◎日期函數(shù) 218
案例109 計算員工工齡【YEAR()】218
案例110 計算2010年之前空調的平均售價【YEAR()】221
案例111 計算本月交貨的產(chǎn)品數(shù)量【MONTH()】222
案例112 計算工程的起止年月【MONTH()】224
案例113 計算員工的轉正時間【MONTH()】228
案例114 計算產(chǎn)品的交貨時間處于哪一旬【DAY()】230
案例115 制作2012年法定假日倒計時【TODAY()】232
案例116 判斷借款是否逾期【DAYS360()】234
案例117 將簡易輸入的日期轉換為標準日期【DATE()】236
案例118 計算付款日期【DATE()】238
案例119 計算當月天數(shù)【DATE()】240
案例120 顯示本次打開檔案的時間【NOW()】241
案例121 判斷機器是否需要檢修【NOW()】243
案例122 自動生成離職員工工資結算日期【EOMONTH()】245
案例123 計算支付貨款日期【EDATE()】247
案例124 計算員工離職之前在公司工作的月數(shù)【DATEDIF()】250
案例125 根據(jù)入職日期計算員工帶薪年假的天數(shù)【DATEDIF()】251

◎工作日函數(shù) 253
案例126 計算員工的工齡（精確到月份）【DATEDIF()】253
案例127 計算工作的完成時間【W(wǎng)ORKDAY()】255
案例128 計算本月的工作日數(shù)目【NETWORKDAYS()】257

◎星期函數(shù) 258
案例129 計算法定假日的星期【W(wǎng)EEKDAY()】258
案例130 計算付款時間【W(wǎng)EEKDAY()】262
案例131 計算某員工每日的工資【W(wǎng)EEKDAY()】264
案例132 按周匯總產(chǎn)品出入庫數(shù)量【W(wǎng)EEKNUM()】266
案例133 自動生成考勤周報表的標題【W(wǎng)EEKNUM()】268
案例134 羅列第45周的日期【W(wǎng)EEKNUM()】270

◎時間函數(shù) 272
案例135 計算臨時加班的員工的工資【TIMEVALUE()】272
案例136 計算員工今日的計時工資【HOUR()】274
案例137 計算員工的平均速度【MINUTE()】277
案例138 根據(jù)完成任務時間計算獎懲金額【MINUTE()】280
案例139 根據(jù)通信時間計算本月總話費【SECOND()】282
案例140 在時間日期數(shù)據(jù)中提取時間或日期【TRUNC】285

Chapter 05 文本函數(shù)應用實例 287
◎字符串轉換函數(shù) 288
案例141 換行顯示庫存量及是否需要進貨【CHAR()】288
案例142 將文本數(shù)字轉換為數(shù)值并計算利潤【VALUE()】290
案例143 從身份證號碼中提取員工的性別信息【TEXT()】292
案例144 從身份證號碼中提取員工的生日信息【TEXT()】294
案例145 快速恢復以科學計數(shù)法顯示的商品條形碼【TEXT()】296
案例146將數(shù)字金額轉換為人民幣大寫【TEXT()】298
案例147將日期轉換為中文大寫【TEXT()】301
案例148將員工提成工資轉換為會計格式【TEXT()】302
案例149根據(jù)工作年限計算員工年終獎【TEXT()】304
案例150快速將數(shù)字轉換為電話號碼格式【TEXT()】306
案例151將成本保留兩位小數(shù)后再求和【TEXT()】308
案例152將應收款顯示為“萬元”并添加￥符號【TEXT()】309
案例153將全角字符轉換為半角字符【ASC()】310
案例154計算混合字符串之中字母的個數(shù)【ASC()】312
案例155將半角字符轉換為全角字符【W(wǎng)IDECHAR()】313
案例156計算混合字符串之中漢字的個數(shù)【W(wǎng)IDECHAR()】314
案例157將單詞的首字母轉換為大寫【PROPER()】315
案例158將所有單詞轉換為小寫形式【LOWER()】317
案例159將英文句子的首字母轉換為大寫其余小寫的形式【LOWER()】318
案例160將所有單詞轉換為大寫【UPPER()】320
案例161統(tǒng)計軟件名稱中英文字母的個數(shù)【UPPER()】321
案例162將區(qū)域中的文本串聯(lián)【T()】323
案例163為公式添加說明【T()】324
案例164用簡易圖表表示銷量情況【REPT()】326

◎文本合并比較函數(shù) 327
案例165 在數(shù)值結果左側添加“￥”符號【CONCATENATE()】327
案例166 在身份證號碼中提取員工的出生年月信息【CONCATENATE()】329
案例167 提取月利潤最高的前三種產(chǎn)品的名稱【CONCATENATE()】330
案例168 判斷實習員工是否可以繼續(xù)考察【EXACT()】331
◎查找與替換函數(shù) 334
案例169根據(jù)產(chǎn)品規(guī)格計算產(chǎn)品的體積【FIND()】334
案例170通訊錄單列轉3列【FIND()】336
案例171將15位身份證號碼升級為18位【REPLACE()】338
案例172升級產(chǎn)品型號【REPLACE()】340
案例173將專業(yè)/年級/班級/名稱分開【REPLACE()】341
案例174從軟件名稱中提取軟件的版本號【SEARCH()】343
案例175統(tǒng)計各部門的員工人數(shù)【SEARCH()】345
案例176提取姓名和省名【SUBSTITUTE()】347

◎獲取字符串函數(shù) 348
案例177從地址中提取省名【LEFT()】348
案例178通過身份證號碼計算年齡【LEFT()】350
案例179將金額分散填充【LEFT()】351
案例180從開支明細中提取金額【MID()】354
案例181從卡機數(shù)據(jù)中判斷員工所屬分公司及部門【RIGHT()】355
案例182從卡機數(shù)據(jù)中提取時間并判斷是否遲到【RIGHT()】357
案例183根據(jù)身份證號碼統(tǒng)計男性員工人數(shù)【RIGHT()】358
案例184在不規(guī)范格式的日期數(shù)據(jù)中提取日期【RIGHT()】360

◎除去空格 362
案例185 將英文句子規(guī)范化【TRIM()】362
案例186分別提取省/市/縣名稱【TRIM()】363

Chapter 06 邏輯函數(shù)應用實例 365
◎真假值判斷函數(shù) 366
案例187判斷員工性別信息是否正確【TRUE()、FALSE()】366
案例188判斷員工是否全勤【AND()】367
案例189判斷員工是否可以直接晉升【AND()】369
案例190判斷員工身份證號碼位數(shù)是否正確【AND()】370
案例191根據(jù)年齡判斷員工是否退休【OR()】371
案例192判斷員工是否符合晉升條件【AND()】373
案例193判斷員工是否升職【NOT()】375
案例194統(tǒng)計月利潤中文本型數(shù)字的個數(shù)【NOT()】376
案例195判斷商品的盈利情況是否良好【NOT()】377
案例196根據(jù)員工工資計算員工的個人所得稅【NOT()】379

◎條件判斷函數(shù) 381
案例197 根據(jù)員工的各項考核成績判斷員工是否合格【IF()】381
案例198 判斷員工是否需要補考【IF()】382
案例199 判斷員工是否缺考【IF()】383
案例200 查詢員工是否享受夜班補貼【IF()】385
案例201 計算員工12月工資【IF()】387
案例202 匯總商品的銷售額【IF()】388
案例203 為辦公物資采購表建立歸類標簽【IF()】391
案例204 在有學生全部缺考的情況下計算學生的平均成績【IF()】392

Chapter 07 查找和引用函數(shù)應用實例 394
◎查找函數(shù) 395
案例205查詢指定員工缺勤次數(shù)【VLOOKUP()】395
案例206 根據(jù)考核成績評定員工考核等級【VLOOKUP()】396
案例207 根據(jù)員工姓名查找員工編號【VLOOKUP()】398
案例208 查詢指定廠家提供的某種產(chǎn)品的價格【VLOOKUP()】400
案例209 查詢商品最新單價【VLOOKUP()】401
案例210 根據(jù)員工編號或者姓名查找提成工資【VLOOKUP()】403
案例211 根據(jù)員工姓名和編號查找員工信息【VLOOKUP()】406
案例212 根據(jù)職務和工齡計算年限工資【VLOOKUP()】408
案例213 根據(jù)產(chǎn)品名稱的拼音生成產(chǎn)品編號【VLOOKUP()】410
案例214 根據(jù)本月收入查詢應采用稅率和速算扣除數(shù)【HLOOKUP()】413
案例215 查找某銷售員某月的銷售業(yè)績【HLOOKUP()】415
案例216 制作員工工牌【HLOOKUP()】416
案例217 查詢最后入職的員工【MATCH()】418
案例218 查詢某員工是否為本公司人員【MATCH()】419
案例219 計算員工的工作項數(shù)【MATCH()】421
案例220 制作周營業(yè)額排行榜【MATCH()】423
案例221 根據(jù)員工考核成績進行排名【MATCH()】427
案例222 羅列各省參展名單【MATCH()】429
案例223 羅列本周多次遲到的員工【MATCH()】432
案例224 根據(jù)庫存編號查詢庫存信息【LOOKUP()】433
案例225 查詢員工應得福利費【LOOKUP()】437
案例226 計算員工測評總分【LOOKUP()】439
案例227 查詢六月值夜班最多的員工【LOOKUP()】441
案例228 羅列每個名次上的員工姓名【LOOKUP()】442
案例229 根據(jù)達標率計算不同工種員工的獎金【LOOKUP()】445
案例230 根據(jù)關鍵字查找所有符合的商品【LOOKUP()】447
案例231 根據(jù)員工檔案詳表制作簡表【CHOOSE()】452
案例232 提取格式混亂的日期中的月份【CHOOSE()】454
案例233 計算業(yè)務員的業(yè)績獎金【CHOOSE()】456
案例234 評定面試結果【CHOOSE()】460

◎引用函數(shù) 461
案例235 為員工檔案填充序號【ROW()】461
案例236 求員工第幾次的測試成績最好【ROW()】463
案例237 將中文日期轉換為標準格式日期【ROW()】465
案例238 每10種商品匯總一次利潤【ROW()】467
案例239 為上半年生產(chǎn)統(tǒng)計表中填充周次【CLOUMN()】470
案例240 統(tǒng)計員工的缺勤情況【COLUMN()】471
案例241 拆分合并單元格【INDIRECT()】473
案例242 統(tǒng)計某公司某周的工作事項【INDIRECT()】476
案例243 多區(qū)域統(tǒng)計業(yè)績50萬元以上的銷售員人數(shù)【INDIRECT()】477
案例244 求3個車間的最高平均產(chǎn)量【INDIRECT()】479
案例245 返回指定時間段的銷售額【INDIRECT()】480
案例246 求最大簽單業(yè)績所在單元格【ADDRESS()】482
案例247 求指定車間的最大產(chǎn)量【ADDRESS()】483
案例248 匯總簽單業(yè)績【OFFSET()】484
案例249 按品種編號【OFFSET()】486
案例250 根據(jù)單價表和銷量表匯總銷售額【OFFSET()】488
案例251 計算最近N天的銷售額【OFFSET()】490
案例252 自動生成工資條【OFFSET()】491
案例253 查詢員工福利相關信息【INDEX()】493
案例254 計算最低購入價格【INDEX()】495
案例255 查詢員工檔案中的最后一個記錄【INDEX()】496
案例256 提取不重復的姓名【INDEX()】499
案例257 交換表格行列【TRANSPOSE()】500

◎超鏈接函數(shù) 501
案例258 快速返回工作表首行【HYPERLINK()】501
案例259 快速跳轉至添加員工信息處【HYPERLINK()】503
案例260 在工作表中直接使用百度搜索【HYPERLINK()】505

Chapter 08 財務函數(shù)應用實例 507
◎投資預算函數(shù) 508
案例261 整存整取【FV()】508
案例262 零存整取【FV()】509
案例263 計算各項投資的金額【PV()】510
案例264 年金現(xiàn)值的計算【PV()】511
案例265 不同計息期現(xiàn)值的計算【PV()】513
案例266 設備更新投資分析【PV()】515
案例267 選用甲設備的最低使用年限【NPER()】518
案例268 求銀行存款年利率【RATE()】519

◎本金和利息函數(shù) 520
案例269 償債基金的計算【PMT()】520
案例270 固定期限的銀行貸款分析【PMT()】522
案例271 求等額分期付款每期的本、息及本息和【PPMT()】524
案例272 求等額本金付款每期的本、息及本息和【PPMT()】526
案例273 選擇最佳還貸方案【PPMT()】529

◎折舊函數(shù) 533
案例274 使用直線折舊法計算固定資產(chǎn)折舊【SLN()】533
案例275 使用固定余額遞減法計算資產(chǎn)折舊【DB()】537
案例276 使用雙倍余額遞減法計算資產(chǎn)折舊【DDB()】540
案例277 使用年數(shù)總和法計算資產(chǎn)折舊【SYD()】544
案例278 使用可變余額遞減法計算資產(chǎn)折舊【VDB()】547

Chapter 09 信息函數(shù)應用實例 550
◎IS()類函數(shù) 551
案例279 查詢指定員工最大缺勤次數(shù)【ISNA()】551
案例280 轉換面試結果表達方式【ISTEXT()】553
案例281 標記考核成績達到A級的員工【ISNUMBER()】557
案例282 按編號匯總庫存量【ISNUMBER()】559
案例283 統(tǒng)計男性員工人數(shù)【ISEVEN()】560
案例284 統(tǒng)計一季度出入庫總量【ISODD()】562
案例285 求員工服務態(tài)度平均分【ISBLANK()】564
案例286 統(tǒng)計字符串中不重復的字母個數(shù)【ISERROR()】566
案例287 羅列參與兩個項目和一個項目的員工【ISERROR()】568
案例288 統(tǒng)計生產(chǎn)人員人數(shù)【ISERR()】571

◎信息類函數(shù) 574
案例289 顯示當前工作表路徑【CELL()】574
案例290 計算員工累計積分【N()】576
案例291 從明細表中提取數(shù)據(jù)【N()】577
案例292 從未知格式日期中提取月份【TYPE()】581

Chapter 10 數(shù)據(jù)庫和三角函數(shù)應用實例 584
◎數(shù)據(jù)庫函數(shù) 585
案例293 統(tǒng)計各車間平均生產(chǎn)成本【DAVERAGE()】585
案例294 統(tǒng)計竹鹽產(chǎn)品平均售價【DAVERAGE()】587
案例295 統(tǒng)計一、二車間第二季度生產(chǎn)總成本【DSUM()】589
案例296 求滿足條件的商品的總利潤【DSUM()】591
案例297 求2班的最高平均分【DMAX()】593
案例298 求平均工齡以下員工的最高工資【DMAX()】595
案例299 判斷可否選派二級教師【DMIN()】599
案例300 查詢符合條件的員工姓名【DGET()】601
案例301 統(tǒng)計所有考核均合格的員工數(shù)【DCOUNTA()】602

◎三角函數(shù) 605
案例302 計算定制鍋爐的直徑【PI()】605
案例303 計算扇形材料的弧長【RADIANS()】606
案例304 繪制簡單三角函數(shù)圖像【SIN()】608
案例305 繪制復雜三角函數(shù)圖像【COS()】610
案例306 判斷三角形材料是否滿足條件【ACOS()】612

Chapter 11 函數(shù)與其他Excel功能結合應用 614
◎條件格式中函數(shù)的應用 615
案例307 使工作表能自動隱藏錯誤值615
案例308 標記銷量最大的10種商品名稱616
案例309 標示第一季度銷售額均在2 000元以上的員工619
案例310 在高于平均成績的姓名后添加標識621
案例311 3天內(nèi)行程提示624
案例312 根據(jù)關鍵字標識商品信息626
案例313 多條件控制工資的顯示627
案例314 按模糊條件標示符合條件的記錄632

◎數(shù)據(jù)有效性中函數(shù)的應用633
案例315 禁止輸入重復的員工編號633
案例316 多條件限制員工編號輸入636
案例317 利用數(shù)據(jù)有效性生成二級列表639
案例318 四級下拉列表的制作644
案例319 將已輸入的數(shù)據(jù)作為數(shù)據(jù)源650

◎圖表中函數(shù)的應用653
案例320 制作一季度產(chǎn)品的動態(tài)銷量圖653
案例321 制作區(qū)域銷量的動態(tài)子母圖656
案例322 制作銷量的動態(tài)分類統(tǒng)計圖661
案例323 制作最近一周的銷量圖663
案例324 制作收支瀑布圖666
案例325 制作可比較任意產(chǎn)品產(chǎn)量的柱形圖669
案例326 制作細分柱形圖673
案例327 制作雙色柱形圖675
案例328 制作動態(tài)復合餅圖678
案例329 突出顯示圖表中的極值683
案例330 高亮顯示光標選定數(shù)據(jù)685

◎數(shù)據(jù)透視表中函數(shù)的應用688
案例331 獲取數(shù)據(jù)透視表關鍵字688
案例332 計算分類百分比689
案例333 為排名進行星級評定690

◎模擬分析中函數(shù)的應用692
案例334 求可變利率下每月的還貸額692
案例335 求不同利率和還款期限下的月還款額694
案例336 貸款方案分析696
案例337 應聘員工工資要求分析699

◎規(guī)劃求解中函數(shù)的應用701
案例338 確定產(chǎn)品的最佳運輸方案701
案例339 確定最佳生產(chǎn)方案704

◎迭代計算中函數(shù)的應用708
案例340 自動記錄填寫產(chǎn)品入庫記錄的時間708
案例341 羅列鋁材下料方案711

Chapter 12 Excel 2013特有函數(shù)介紹及其應用實例 715
No001 返回數(shù)字的反余切值【ACOT()】716
No002 返回數(shù)字的反雙曲余切值【ACOTH()】716
No003 返回角度的正割值【SEC()】717
No004 返回角度的雙曲正割值【SECH()】717
No005 返回角度的余切值【COT()】717
No006 返回雙曲角度的雙曲余切值【COTH()】718
No007 返回角度的余割值【CSC()】718
No008 返回角度的雙曲余割值【CSCH()】719
No009 將羅馬數(shù)字轉換為阿拉伯數(shù)字【ARABIC()】719
No010 將數(shù)字轉換為具備給定基數(shù)的文本表示【BASE()】720
No011 將數(shù)字的文本表現(xiàn)形式轉換為十進制數(shù)【DECIMAL()】720
No012 向上舍入數(shù)字到指定基數(shù)的倍數(shù)【CEILINGMATH()】721
No013 向下舍入數(shù)字到指定基數(shù)的倍數(shù)【FLOORMATH()】722
No014 向上舍入數(shù)字到指定有效位的倍數(shù)【ISOCEILING()】722
No015 返回給定數(shù)目的項的組合數(shù)【COMBINA()】723
No016 返回指定維度的單位矩陣【MUNIT()】723
No017 使用二項式分布返回試驗結果的概率【BINOMDISTRANGE()】724
No018 返回伽瑪函數(shù)值【GAMMA()】724
No019 返回比指定標準正態(tài)累積分布函數(shù)值小05的值【GAUSS()】725
No020 返回對象總數(shù)中給定數(shù)據(jù)的排列數(shù)【PERMUTATIONA()】725
No021 返回標準正態(tài)分布的密度函數(shù)值【PHI()】726
No022 返回數(shù)據(jù)集分布的不對稱度【SKEWP()】726
No023 返回兩個日期之間的天數(shù)【DAYS()】727
No024 返回給定日期在全年中所處的周數(shù)【ISOWEEKNUM()】727
No025 將數(shù)字轉化為文本格式并添加$符號【DOLLAR()】728
No026 以與區(qū)域設置無關的方式將文本轉換為數(shù)字【NUMBERVALUE()】728
No027 將給定數(shù)值轉化為對應的Unicode字符【UNICHAR()】729
No028 返回給定文本第一個字符對應的Unicode數(shù)字【UNICODE()】729
No029 判斷公式結果是否為錯誤值#N/A 【IFNA()】730
No030 返回所有參數(shù)的邏輯異或【XOR()】730
No031 以字符串的形式返回公式【FORMULATEXT()】731
No032 返回投資到達指定值所需的期數(shù)【PDURATION()】731
No033 返回投資增長的等效利率【RRI()】732
No034 檢查單元格引用是否包含公式732
No035 返回引用工作表的工作表編號【ISFORMULA()】733
No036 返回引用中的工作表總數(shù)【SHEETS ()】733
No037 返回兩個數(shù)的按位“與”結果【BITAND()】733
No038 返回兩個數(shù)的按位“或”結果【BITOR()】734
No039 返回兩個數(shù)值的按位“異或”結果【BITXOR()】734
No040 返回向左移動指定位數(shù)后的數(shù)值【BITLSHIFT()】735
No041 返回向右移動指定位數(shù)后的數(shù)值【BITRSHIFT()】735
No042 返回復數(shù)的余切值【IMCOT()】736
No043 返回復數(shù)的余割值【IMCSC()】736
No044 返回復數(shù)的正割值【IMSEC()】736
No045 返回復數(shù)的正切值【IMTAN()】737
No046 返回復數(shù)的雙曲正弦值【IMSINH()】737
No047 返回復數(shù)的雙曲余弦值【IMCOSH()】737
No048 返回復數(shù)的雙曲正割值【IMSECH()】738
No049 返回復數(shù)的雙曲余割值【IMCSCH()】738
No050 返回復數(shù)的平方根【IMSQRT()】738
No051 返回URL編碼的字符串【ENCODEURL()】739
No052 返回XML內(nèi)容中的特定數(shù)據(jù)【FILTERXML()】739
No053 返回網(wǎng)絡上的Web服務數(shù)據(jù)【W(wǎng)EBSERVICE()】740

案例001 計算任務的持續(xù)時間【DAYS()】740
案例002 計算法定節(jié)日是全年的第幾周【ISOWEEKNUM()】742
案例003 處理員工姓名查找不到的情況【IFNA()】743

附錄A 函數(shù)語法格式速查 745
1．邏輯函數(shù)745
2．統(tǒng)計函數(shù)745
3．數(shù)學和三角函數(shù)750
4．日期和時間函數(shù)752
5．文本函數(shù)752
6．財務函數(shù)754
7．查找和引用函數(shù)757
8．信息函數(shù)758
9．數(shù)據(jù)庫函數(shù)759

行業(yè)應用目錄
案例001計算所有生產(chǎn)線的總產(chǎn)量【SUM()】30
案例017 計算滿足一定條件的一、二級教師工作量之差【SUMIFS()】58
案例018 匯總銷售部和市場部遲到的人數(shù)【SUMIFS()】60
案例020 計算男性員工人數(shù)【SUMPRODUCT()】63
案例026 隨機生成抽檢產(chǎn)品的序號【RAND()】73
案例027 將三個部門的員工隨機分組【RAND()】77
案例028 隨機抽取5名員工的編號【RANDBETWEEN()】81
案例034 計算順利完成業(yè)務需要的人數(shù)【ROUNDUP()】93
案例036 給通訊錄中的數(shù)據(jù)編號【INT()】96
案例038 計算可以組建的業(yè)務小組的個數(shù)【FLOOR()】101
案例039 計算員工的提成工資【FLOOR()】103
案例049 根據(jù)出生年月推算員工生肖【MOD()】117
案例050 根據(jù)出生年月判斷性別【MOD()】119
案例051 生成循環(huán)序列【MOD()】120
案例052 為員工檔案奇偶行設置不同的格式【MOD()】121
案例055 預計象棋比賽的總場次【COMBIN()】127
案例060 統(tǒng)計2月26日加班人次【COUNT()】135
案例061 統(tǒng)計女裝訂單數(shù)大于500的商品數(shù)量【COUNT()】136
案例062 統(tǒng)計月銷售量1 000以下、利潤2 000元以上商品數(shù)【COUNT()】138
案例064 統(tǒng)計各部門職工的學歷情況【COUNT()】140
案例065 統(tǒng)計銷售部總人數(shù)【COUNTA()】142
案例066 自動生成員工編號【COUNTA()】143
案例070 計算20～35歲的員工比例【COUNTIF()】148

人 事
案例071 統(tǒng)計與公司兩項業(yè)務都有往來的客戶數(shù)【COUNTIF()】150
案例073 統(tǒng)計業(yè)績200 000元以上的大專本科學歷男性業(yè)務員【COUNTIFS()】152
案例085 去掉一個最高分和最低分求參賽選手平均得分【TRIMMEAN()】169
案例086 突出顯示銷量最高的數(shù)據(jù)【MAX()】171
案例091 計算工齡最小的女員工的工齡【MIN()】179
案例095 設定社保繳費基數(shù)上下限【MEDIAN()】185
案例099 求同齡異性員工的人數(shù)【RANK()】194
案例104分別統(tǒng)計本科以下及本科以上學歷的員工人數(shù)【FREQUENCY()】204
案例105 統(tǒng)計各學歷員工數(shù)【FREQUENCY()】205
案例107 優(yōu)選機器維修人員招聘方案【BINOMDIST()】210
案例109 計算員工工齡【YEAR()】218
案例113 計算員工的轉正時間【MONTH()】228
案例115 制作2012年法定假日倒計時【TODAY()】232
案例117 將簡易輸入的日期轉換為標準日期【DATE()】236
案例122 自動生成離職員工工資結算日期【EOMONTH()】245
案例124 計算員工離職之前在公司工作的月數(shù)【DATEDIF()】250
案例125 根據(jù)入職日期計算員工帶薪年假的天數(shù)【DATEDIF()】251
案例126 計算員工的工齡（精確到月份）【DATEDIF()】253
案例128 計算本月的工作日數(shù)目【NETWORKDAYS()】257
案例129 計算法定假日的星期【W(wǎng)EEKDAY()】258
案例133 自動生成考勤周報表的標題【W(wǎng)EEKNUM()】268
案例134 羅列第45周的日期【W(wǎng)EEKNUM()】270
案例140 在時間日期數(shù)據(jù)中提取時間或日期【TRUNC】285
案例143 從身份證號碼中提取員工的性別信息【TEXT()】292
案例144 從身份證號碼中提取員工的生日信息【TEXT()】294
案例150快速將數(shù)字轉換為電話號碼格式【TEXT()】306
案例166 在身份證號碼中提取員工的出生年月信息【CONCATENATE()】329
案例168 判斷實習員工是否可以繼續(xù)考察【EXACT()】331
案例170通訊錄單列轉3列【FIND()】336
案例171將15位身份證號碼升級為18位【REPLACE()】338
案例175統(tǒng)計各部門的員工人數(shù)【SEARCH()】345
案例176提取姓名和省名【SUBSTITUTE()】347
案例177從地址中提取省名【LEFT()】348
案例178通過身份證號碼計算年齡【LEFT()】350
案例181從卡機數(shù)據(jù)中判斷員工所屬分公司及部門【RIGHT()】355
案例182從卡機數(shù)據(jù)中提取時間并判斷是否遲到【RIGHT()】357
案例183根據(jù)身份證號碼統(tǒng)計男性員工人數(shù)【RIGHT()】358
案例184在不規(guī)范格式的日期數(shù)據(jù)中提取日期【RIGHT()】360
案例186分別提取省/市/縣名稱【TRIM()】363
案例187判斷員工性別信息是否正確【TRUE()、FALSE()】366
案例188判斷員工是否全勤【AND()】367
案例189判斷員工是否可以直接晉升【AND()】369
案例190判斷員工身份證號碼位數(shù)是否正確【AND()】370
案例191根據(jù)年齡判斷員工是否退休【OR()】371
案例192判斷員工是否符合晉升條件【AND()】373
案例193判斷員工是否升職【NOT()】375
案例197 根據(jù)員工的各項考核成績判斷員工是否合格【IF()】381
案例198 判斷員工是否需要補考【IF()】382
案例199 判斷員工是否缺考【IF()】383
案例200 查詢員工是否享受夜班補貼【IF()】385
案例205查詢指定員工缺勤次數(shù)【VLOOKUP()】395
案例206 根據(jù)考核成績評定員工考核等級【VLOOKUP()】396
案例207 根據(jù)員工姓名查找員工編號【VLOOKUP()】398
案例211 根據(jù)員工姓名和編號查找員工信息【VLOOKUP()】406
案例212 根據(jù)職務和工齡計算年限工資【VLOOKUP()】408
案例216 制作員工工牌【HLOOKUP()】416
案例217 查詢最后入職的員工【MATCH()】418
案例218 查詢某員工是否為本公司人員【MATCH()】419
案例219 計算員工的工作項數(shù)【MATCH()】421
案例221 根據(jù)員工考核成績進行排名【MATCH()】427
案例222 羅列各省參展名單【MATCH()】429
案例223 羅列本周多次遲到的員工【MATCH()】432
案例226 計算員工測評總分【LOOKUP()】439
案例227 查詢六月值夜班最多的員工【LOOKUP()】441
案例228 羅列每個名次上的員工姓名【LOOKUP()】442
案例231 根據(jù)員工檔案詳表制作簡表【CHOOSE()】452
案例232 提取格式混亂的日期中的月份【CHOOSE()】454
案例234 評定面試結果【CHOOSE()】460
案例235 為員工檔案填充序號【ROW()】461
案例236 求員工第幾次的測試成績最好【ROW()】463
案例240 統(tǒng)計員工的缺勤情況【CLOUMN()】471
案例242 統(tǒng)計某公司某周的工作事項【INDIRECT()】476
案例255 查詢員工檔案中的最后一個記錄【INDEX()】496
案例256 提取不重復的姓名【INDEX()】499
案例257 交換表格行列【TRANSPOSE()】500
案例258 快速返回工作表首行【HYPERLINK()】501
案例259 快速跳轉至員添加信息處【HYPERLINK()】503
案例260 在工作表中直接使用百度搜索【HYPERLINK()】505
案例279 查詢指定員工最大缺勤次數(shù)【ISNA()】551
案例280 轉換面試結果表達方式【ISTEXT()】553
案例281 標記考核成績達到A級的員工【ISNUMBER()】557
案例283 統(tǒng)計男性員工人數(shù)【ISEVEN()】560
案例288 統(tǒng)計生產(chǎn)人員人數(shù)【ISERR()】571
案例289 顯示當前工作表路徑【CELL()】574
案例290 計算員工累計積分【N()】576
案例300 查詢符合條件的員工姓名【DGET()】601
案例301 統(tǒng)計所有考核均合格的員工數(shù)【DCOUNTA()】602
案例315 禁止輸入重復的員工編號633
案例316 多條件限制員工編號輸入636
案例317 利用數(shù)據(jù)有效性生成二級列表639
案例319 將已輸入的數(shù)據(jù)作為數(shù)據(jù)源650
案例003 計算銷售部當月的實發(fā)工資總和【SUM()】34
案例004 計算銷售量排前三的商品總銷量【SUM()】36
案例008 計算工資在8 000元以上的員工工資總和【SUMIF()】44
案例009 計算提成在1 500~2 000元的員工工資總和【SUMIF()】45
案例010 統(tǒng)計會員在項鏈上的總消費【SUMIF()】48
案例012 匯總選中單元格所在行100元以上補貼總額【SUMIF()】51
案例013 計算前三名和后三名的數(shù)據(jù)之和【SUMIF()】53
案例014 匯總鼠標所在列中大于7500的生產(chǎn)量【SUMIF()】54
案例015 計算售價10元以下、銷量200以上的商品總利潤【SUMIFS()】56
案例016 統(tǒng)計銷量300以上的竹鹽系列產(chǎn)品總利潤【SUMIFS()】57
案例019 計算低值易耗品采購總金額【SUMPRODUCT()】61

財 務
案例029 隨機抽取一名員工工資數(shù)據(jù)【RANDBETWEEN()】84
案例031 以“萬元”為單位計算當月的產(chǎn)品銷售總利潤【ROUND()】89
案例032 匯總數(shù)量計量單位不同的金額【ROUND()】90
案例033 將員工實發(fā)工資保留“角”，忽略“分” 【ROUNDDOWN()】91
案例035 計算發(fā)放工資所需各種面額鈔票的數(shù)量【INT()】94
案例037 計算員工年限工資【CEILING()】98
案例042 跨表計算總產(chǎn)值【PRODUCT()】108
案例044 計算超市打折活動的總營業(yè)額【MMULT()】110
案例046 求不同單價下的利潤【MMULT()】113
案例053 制作工資條【MOD()】123
案例054 按梯度計算員工提成工資【QUOTIENT()】125
案例058 求所有商品的平均銷量【SUBTOTAL()】130
案例059 計算隱藏某些商品時的平均利潤【SUBTOTAL()】132
案例116 判斷借款是否逾期【DAYS360()】234
案例123 計算支付貨款日期【EDATE()】247
案例130 計算付款時間【W(wǎng)EEKDAY()】262
案例131 計算某員工每日的工資【W(wǎng)EEKDAY()】264
案例135 計算臨時加班的員工的工資【TIMEVALUE()】272
案例136 計算員工今日的計時工資【HOUR()】274
案例138 根據(jù)完成任務時間計算獎懲金額【MINUTE()】280
案例146將數(shù)字金額轉換為人民幣大寫【TEXT()】298
案例148將員工提成工資轉換為會計格式【TEXT()】302
案例149根據(jù)工作年限計算員工年終獎【TEXT()】304
案例151將成本保留兩位小數(shù)后再求和【TEXT()】308
案例152將應收款顯示為“萬元”并添加￥符號【TEXT()】309
案例165 在數(shù)值結果左側添加“￥”符號【CONCATENATE()】327
案例179將金額分散填充【LEFT()】351
案例180從開支明細中提取金額【MID()】354
案例196根據(jù)員工工資計算員工的個人所得稅【NOT()】379
案例201 計算員工12月工資【IF()】387
案例202 匯總商品的銷售額【IF()】388
案例203 為辦公物資采購表建立歸類標簽【IF()】391
案例210 根據(jù)員工編號或者姓名查找提成工資【VLOOKUP()】403
案例214 根據(jù)本月收入查詢應采用稅率和速算扣除數(shù)【HLOOKUP()】413
案例225 查詢員工應得福利費【LOOKUP()】437
案例229 根據(jù)達標率計算不同工種員工的獎金【LOOKUP()】445
案例233 計算業(yè)務員的業(yè)績獎金【CHOOSE()】456
案例237 將中文日期轉換為標準格式日期【ROW()】465
案例238 每10種商品匯總一次利潤【ROW()】467
案例243 多區(qū)域統(tǒng)計業(yè)績50萬元以上的銷售員人數(shù)【INDIRECT()】477
案例253 查詢員工福利相關信息【INDEX()】493
案例261 整存整取【FV()】508
案例262 零存整取【FV()】509
案例263 計算各項投資的金額【PV()】510
案例264 年金現(xiàn)值的計算【PV()】511
案例265 不同計息期現(xiàn)值的計算【PV()】513
案例266 設備更新投資分析【PV()】515
案例267 選用甲設備的最低使用年限【NPER()】518
案例268 求銀行存款年利率【RATE()】519
案例269 償債基金的計算【PMT()】520
案例270 固定期限的銀行貸款分析【PMT()】522
案例271 求等額分期付款每期的本、息及本息和【PPMT()】524
案例272 求等額本金付款每期的本、息及本息和【PPMT()】526
案例273 選擇最佳還貸方案【PPMT()】529
案例274 使用直線折舊法計算固定資產(chǎn)折舊【SLN()】533
案例275 使用固定余額遞減法計算資產(chǎn)折舊【DB()】537
案例276 使用雙倍余額遞減法計算資產(chǎn)折舊【DDB()】540
案例277 使用年數(shù)總和法計算資產(chǎn)折舊【SYD()】544
案例278 使用可變余額遞減法計算資產(chǎn)折舊【VDB()】547
案例291 從明細表中提取數(shù)據(jù)【N()】577
案例298 求平均工齡以下員工的最高工資【DMAX()】595
案例307 使工作表能自動隱藏錯誤值615
案例313 多條件控制工資的顯示627
案例314 按模糊條件標示符合條件的記錄632
案例324 制作收支瀑布圖666
案例331 獲取數(shù)據(jù)透視表關鍵字688
案例334 求可變利率下每月的還貸額692
案例335 求不同利率和還款期限下的月還款額694
案例336 貸款方案分析696
案例337 應聘員工工資要求分析699
案例002 計算簽單總額在300 000元以上的總金額【SUM()】32
案例006 匯總女裝訂單表中已收到的定金總額【SUMIF()】39
案例011 統(tǒng)計會員在項鏈和手鐲上的總消費【SUMIF()】49
案例021 統(tǒng)計購買鉑金戒指的男性會員人數(shù)【SUMPRODUCT()】64
案例022 匯總男性會員在首飾上的總消費【SUMPRODUCT()】66
案例040 制作商品簡易標簽【TRUNC()】104
案例063 統(tǒng)計特色商品數(shù)量【COUNT()】139

銷 售
案例069 統(tǒng)計購買各種首飾的會員人數(shù)【COUNTIF()】146
案例074 統(tǒng)計各級業(yè)務員業(yè)績異常的人數(shù)【COUNTIFS()】154
案例078 統(tǒng)計銷量前N名的平均銷量【AVERAGE()】160
案例079 計算操作員的平均產(chǎn)量【AVERAGE()】161
案例080 計算4月份交貨產(chǎn)品的平均訂單金額【AVERAGEIF()】162
案例081 計算會員在金飾上的平均消費【AVERAGEIF()】163
案例087 求月銷售量低于平均銷量的最大銷量的商品名稱【MAX()】173
案例088 分別求1～4月排前3的銷售量【LARGE()】175
案例089 制作產(chǎn)品一季度銷量排行榜【LARGE()】176
案例090 求最后購買某種首飾的會員【LARGE()】178
案例094 計算3月份產(chǎn)品銷量中值【MEDIAN()】183
案例096 查找銷售量處于中間位置的部門【MEDIAN()】186
案例097 對員工上半年銷售業(yè)績進行排名【RANK()】188
案例102 統(tǒng)計各個利潤區(qū)間的商品數(shù)目【FREQUENCY()】200
案例103 分別統(tǒng)計未完成任務和超額完成任務的人數(shù)【FREQUENCY()】202
案例106 求員工銷售業(yè)績的中國式排名【FREQUENCY()】207
案例110 計算2010年之前空調的平均售價【YEAR()】221
案例111 計算本月交貨的產(chǎn)品數(shù)量【MONTH()】222
案例114 計算產(chǎn)品的交貨時間處于哪一旬【DAY()】229
案例118 計算付款日期【DATE()】238
案例119 計算當月天數(shù)【DATE()】240
案例120 顯示本次打開檔案的時間【NOW()】241
案例132 按周匯總產(chǎn)品出入庫數(shù)量【W(wǎng)EEKNUM()】266
案例139 根據(jù)通信時間計算本月總話費【SECOND()】282
案例141 換行顯示庫存量及是否需要進貨【CHAR()】288
案例142 將文本數(shù)字轉換為數(shù)值并計算利潤【VALUE()】290
案例145 快速恢復以科學計數(shù)法顯示的商品條形碼【TEXT()】296
案例147將日期轉換為中文大寫【TEXT()】301
案例153將全角字符轉換為半角字符【ASC()】310
案例154計算混合字符串之中字母的個數(shù)【ASC()】312
案例155將半角字符轉換為全角字符【W(wǎng)IDECHAR()】313
案例156計算混合字符串之中漢字的個數(shù)【W(wǎng)IDECHAR()】314
案例163為公式添加說明【T()】324
案例164用簡易圖表表示銷量情況【REPT()】326
案例167 提取月利潤最高的前三種產(chǎn)品的名稱【CONCATENATE()】330
案例194統(tǒng)計月利潤中文本型數(shù)字的個數(shù)【NOT()】376
案例195判斷商品的盈利情況是否良好【NOT()】377
案例208 查詢指定廠家提供的某種產(chǎn)品的價格【VLOOKUP()】400
案例209 查詢商品最新單價【VLOOKUP()】401
案例215 查找某銷售員某月的銷售業(yè)績【HLOOKUP()】415
案例220 制作周營業(yè)額排行榜【MATCH()】423
案例224 根據(jù)庫存編號查詢庫存信息【LOOKUP()】433
案例230 根據(jù)關鍵字查找所有符合的商品【LOOKUP()】447
案例241 拆分合并單元格【INDIRECT()】473
案例245 返回指定時間段的銷售額【INDIRECT()】480
案例246 求最大簽單業(yè)績所在單元格【ADDRESS()】482
案例247 求指定車間的最大產(chǎn)量【ADDRESS()】483
案例248 匯總簽單業(yè)績【OFFSET()】484
案例249 按品種編號【OFFSET()】486
案例250 根據(jù)單價表和銷量表匯總銷售額【OFFSET()】488
案例251 計算最近N天的銷售額【OFFSET()】490
案例252 自動生成工資條【OFFSET()】491
案例254 計算最低購入價格【INDEX()】495
案例282 按編號匯總庫存量【ISNUMBER()】559
案例284 統(tǒng)計一季度出入庫總量【ISODD()】562
案例292 從未知格式日期中提取月份【TYPE()】581
案例294 統(tǒng)計竹鹽產(chǎn)品平均售價【DAVERAGE()】587
案例296 求滿足條件的商品的總利潤【DSUM()】591
案例308 標記銷量最大的10種商品名稱616
案例309 標示第一季度銷售額均在2000以上的員工619
案例312 根據(jù)關鍵字標識商品信息626
案例320 制作一季度產(chǎn)品的動態(tài)銷量圖653
案例321 制作區(qū)域銷量的動態(tài)子母圖656
案例322 制作銷量的動態(tài)分類統(tǒng)計圖661
案例323 制作最近一周的銷量圖663
案例326 制作細分柱形圖673
案例327 制作雙色柱形圖675
案例328 制作動態(tài)復合餅圖678
案例329 突出顯示圖表中的極值683
案例330 高亮顯示光標選定數(shù)據(jù)685
案例338 確定產(chǎn)品的最佳運輸方案701
案例005 高斯求和【SUM()】38
案例007 匯總跳遠成績在19米以上的學生人數(shù)【SUMIF()】41
案例023 匯總教學能力85分以上的一級教師總工作量【SUMPRODUCT()】67
案例030 將跳遠平均成績保留兩位小數(shù)【ROUND()】87
案例045 制作中文九九乘法表【MMULT()】111
案例067 統(tǒng)計學生缺考總次數(shù)【COUNTBLANK()】144

教 學
案例068 統(tǒng)計有效的調查問卷數(shù)目【COUNTBLANK()】145
案例072 統(tǒng)計語、數(shù)、外大于90分且總分大于480分的人數(shù)【COUNTIFS()】151
案例076 計算90分以上的平均成績【AVERAGE()】157
案例077 計算學生含缺考科目的平均成績【AVERAGE()】158
案例082 計算金融系講師平均授課課時【AVERAGEIFS()】164
案例083 計算滿足特定條件的教師的科研能力平均分【AVERAGEIFS()】165
案例084 去除最值求全班跳遠平均成績【AVERAGEIFS()】167
案例098 查詢指定學生的成績排名【RANKAVG()】190
案例100 求跳遠測試中出現(xiàn)次數(shù)最多的成績【MODE()】196
案例173將專業(yè)/年級/班級/名稱分開【REPLACE()】341
案例204 在有學生全部缺考的情況下計算學生的平均成績【IF()】392
案例297 求2班的最高平均分【DMAX()】593
案例299 判斷可否選派二級教師【DMIN()】599
案例304 繪制簡單三角函數(shù)圖像【SIN()】608
案例305 繪制復雜三角函數(shù)圖像【COS()】610
案例310 在高于平均成績的姓名后添加標識621
案例311 3天內(nèi)行程提示624
案例024 匯總所有車間員工的是實際開支費用【SUMPRODUCT()】69
案例025 隨機生成抽檢產(chǎn)品的序號【RAND()】70
案例040 制作商品簡易標簽【TRUNC()】104
案例041 計算車間每小時生產(chǎn)產(chǎn)值【PRODUCT()】106
案例047 計算C產(chǎn)品的最大入庫量【MMULT()】115
案例048 計算產(chǎn)品的累計入庫數(shù)【MMULT()】116
案例075 計算各季度的平均生產(chǎn)成本【AVERAGE()】156

生 產(chǎn)
案例092 計算材料的3個最少消耗量【SMALL()】180
案例093 提取每月銷量倒數(shù)第二的商品【SMALL()】182
案例101 查詢材料采購最頻繁的車間【MODEMULT()】198
案例108 提供可供選擇的機床維修人員配置方案【BINOMDIST()】212
案例121 判斷機器是否需要檢修【NOW()】243
案例137 計算員工的平均速度【MINUTE()】277
案例169根據(jù)產(chǎn)品規(guī)格計算產(chǎn)品的體積【FIND()】334
案例172升級產(chǎn)品型號【REPLACE()】340
案例239 為上半年生產(chǎn)統(tǒng)計表中填充周次【CLOUMN()】470
案例244 求3個車間的最高平均產(chǎn)量【INDIRECT()】479
案例293 統(tǒng)計各車間平均生產(chǎn)成本【DAVERAGE()】585
案例295 統(tǒng)計一、二車間第二季度生產(chǎn)總成本【DSUM()】589
案例302 計算定制鍋爐的直徑【PI()】605
案例303 計算扇形材料的弧長【RADIANS()】606
案例306 判斷三角形材料是否滿足條件【ACOS()】612
案例325 制作可比較任意產(chǎn)品產(chǎn)量的柱形圖669
案例332 計算分類百分比689
案例333 為排名進行星級評定690
案例339 確定最佳生產(chǎn)方案704
案例340 自動記錄填寫產(chǎn)品入庫記錄的時間708
案例043 根據(jù)三邊求普通三角形的面積【PRODUCT()】109
案例112 計算工程的起止年月【MONTH()】224
案例124 計算員工離職之前在公司工作的月數(shù)【DATEDIF()】250
案例157將單詞的首字母轉換為大寫【PROPER()】315

業(yè) 務
工 程
案例158將所有單詞轉換為小寫形式【LOWER()】317
案例159將英文句子的首字母轉換為大寫其余小寫的形式【LOWER()】318
案例160將所有單詞轉換為大寫【UPPER()】320
案例161統(tǒng)計軟件名稱中英文字母的個數(shù)【UPPER()】321
案例285 求員工服務態(tài)度平均分【ISBLANK()】564
案例287 羅列參與兩個項目和一個項目的員工【ISERROR()】568
案例318 四級下拉列表的制作644
案例056 計算兩種彩票的中頭獎概率【COMBIN()】128
案例057 計算中獎率【PERMUT()】129
案例162將區(qū)域中的文本串聯(lián)【T()】323
案例174從軟件名稱中提取軟件的版本號【SEARCH()】343
案例185 將英文句子規(guī)范化【TRIM()】362
案例286 統(tǒng)計字符串中不重復的字母個數(shù)【ISERROR()】566
雜 項