秒懂Excel函數(shù)應(yīng)用技巧

第1章　與Excel公式相約
1.1　為何要學(xué)Excel公式    2
1.1.1　提高數(shù)據(jù)統(tǒng)計效率    2
1.1.2　簡化數(shù)據(jù)分析流程    3
1.1.3　加快數(shù)據(jù)處理速度    4
1.2　Excel公式快速入門    5
1.2.1　了解Excel公式的基本形態(tài)    5
1.2.2　提高公式輸入速度的關(guān)鍵    7
1.2.3　在公式中插入函數(shù)的方法    8
1.2.4　按具體情況選擇填充方式    11
1.2.5　單元格引用方式不能忽視    13
1.2.6　省心的自動計算    15
1.2.7　公式的高階玩法——數(shù)組公式    16
1.3　初學(xué)者學(xué)函數(shù)的正確方法    18
1.3.1　認(rèn)識函數(shù)類型    18
1.3.2　快速了解不熟悉的函數(shù)    19
1.3.3　搞懂每個參數(shù)的含義    20
1.3.4　適時查看幫助信息    21
1.3.5　解鎖嵌套函數(shù)的規(guī)則    22
1.4　名稱讓公式更容易理解    23
1.4.1　為區(qū)域定義名稱    23
1.4.2　常用公式的名稱定義    24
1.4.3　名稱的應(yīng)用與管理    25
1.5　解決初學(xué)者常見問題    26
1.5.1　為什么公式不自動計算    27
1.5.2　為什么公式返回了錯誤值    29
1.5.3　單元格中為什么出現(xiàn)綠色小三角    30
1.5.4　公式求和結(jié)果為什么是0    31
1.5.5　如何排查公式錯誤    32
1.5.6　無法退出公式編輯狀態(tài)怎么辦    33
初試鋒芒    34
第2章　典型的10種函數(shù)必修課
2.1　SUM函數(shù)為求和而生    36
2.1.1　SUM函數(shù)的基礎(chǔ)用法    36
2.1.2　參數(shù)的設(shè)置原則    37
2.2　IF函數(shù)幫助選擇正確結(jié)果    38
2.2.1　先聽懂函數(shù)的語言    38
2.2.2　有判斷的地方要想到IF    39
2.2.3　循環(huán)嵌套進(jìn)行多次判斷    40
2.3　數(shù)據(jù)查詢的明星函數(shù)    41
2.3.1　單向查詢用LOOKUP函數(shù)    41
2.3.2　關(guān)鍵詞查詢用VLOOKUP函數(shù)    43
2.3.3　位置查詢用MATCH函數(shù)    45
2.3.4　列表索引用CHOOSE函數(shù)    47
2.3.5　行列定位坐標(biāo)用INDEX函數(shù)    48
2.4　精確完成日期計算    49
2.4.1　DATE函數(shù)告知具體日期    50
2.4.2　日期間隔天數(shù)DAYS函數(shù)準(zhǔn)知道    51
2.5　先確定字符位置再執(zhí)行后續(xù)操作    52
2.5.1　FIND函數(shù)確定字符起始位置    52
2.5.2　嵌套函數(shù)提取字符    53
初試鋒芒    54
第3章　大數(shù)據(jù)時代的統(tǒng)計分析
3.1　完成平均值計算    56
3.1.1　計算各科平均成績——AVERAGE函數(shù)    56
3.1.2　計算包含缺考同學(xué)在內(nèi)的平均成績——AVERAGEA函數(shù)    57
3.1.3　計算指定班級和指定科目的平均成績——AVERAGEIF函數(shù)    58
3.1.4　計算指定班級所有及格分?jǐn)?shù)的平均分——AVERAGEIFS函數(shù)    59
3.2　按條件統(tǒng)計單元格數(shù)量    60
3.2.1　統(tǒng)計生產(chǎn)線實(shí)際開工天數(shù)——COUNT函數(shù)    60
3.2.2　統(tǒng)計通過面試的人數(shù)——COUNTA函數(shù)    61
3.2.3　統(tǒng)計員工正常休息的天數(shù)——COUNTBLANK函數(shù)    62
3.2.4　統(tǒng)計銷售額超過6萬元的人數(shù)——COUNTIF函數(shù)    63
3.2.5　統(tǒng)計指定門店業(yè)績達(dá)標(biāo)的人數(shù)——COUNTIFS函數(shù)    64
3.3　統(tǒng)計最大值和最小值    65
3.3.1　統(tǒng)計會員單筆消費(fèi)最高記錄——MAX函數(shù)    66
3.3.2　從負(fù)數(shù)和文本值中統(tǒng)計最高增長率——MAXA函數(shù)    67
3.3.3　從所有人員中提取最小年齡——MIN函數(shù)    68
3.3.4　提取商品最低銷售數(shù)量——MINA函數(shù)    69
3.4　對數(shù)據(jù)進(jìn)行排位統(tǒng)計    70
3.4.1　對比賽成績進(jìn)行排名——RANK函數(shù)    70
3.4.2　提取指定名次的銷售業(yè)績——LARGE函數(shù)    71
3.4.3　淘汰評分最低的3名成員——SMALL函數(shù)    72
3.4.4　計算一周血糖值的中值——MEDIAN函數(shù)    73
初試鋒芒    75
第4章　一絲不茍的數(shù)學(xué)運(yùn)算
4.1　四則運(yùn)算函數(shù)    78
4.1.1　統(tǒng)計商品銷售金額——SUM函數(shù)    78
4.1.2　統(tǒng)計指定商品的銷售總額——SUMIF函數(shù)    79
4.1.3　根據(jù)多個條件計算指定產(chǎn)品的出庫數(shù)量——SUMIFS函數(shù)    80
4.1.4　求在預(yù)算內(nèi)能購買的商品數(shù)量——QUOTIENT函數(shù)    81
4.1.5　計算兩數(shù)相除的余數(shù)——MOD函數(shù)    82
4.1.6　計算商品折扣價——PRODUCT函數(shù)    84
4.1.7　根據(jù)單價和數(shù)量直接計算總銷售額——SUMPRODUCT函數(shù)    85
4.1.8　計算同期營業(yè)額的相差值——ABS函數(shù)    86
4.2　舍入函數(shù)    87
4.2.1　舍去金額中的零錢部分——INT函數(shù)    87
4.2.2　將平均單價四舍五入保留1位小數(shù)——ROUND函數(shù)    88
4.2.3　將投資收益取整——TRUNC函數(shù)    89
4.2.4　計算車輛租賃計費(fèi)時長——ROUNDUP函數(shù)    91
4.2.5　對食品成分含量進(jìn)行舍入處理——ROUNDDOWN函數(shù)    92
4.2.6　計算業(yè)績提成金額——FLOOR函數(shù)    93
4.2.7　計算包裝是否達(dá)到最大容量——EVEN函數(shù)    94
4.2.8　將發(fā)貨數(shù)量補(bǔ)充為奇數(shù)——ODD函數(shù)    95
4.3　隨機(jī)數(shù)函數(shù)    96
4.3.1　隨機(jī)安排選手入場順序——RAND函數(shù)    96
4.3.2　隨機(jī)生成6位數(shù)密碼——RANDBETWEEN函數(shù)    97
初試鋒芒    99
第5章　明辨是非的邏輯函數(shù)
5.1　邏輯判斷函數(shù)    102
5.1.1　判斷員工是否具備晉升資格——AND函數(shù)    102
5.1.2　判斷考生是否有一項(xiàng)成績考了滿分——OR函數(shù)    103
5.1.3　判斷面試者性別是否符合要求——NOT函數(shù)    104
5.2　直觀呈現(xiàn)邏輯判斷結(jié)果    105
5.2.1　根據(jù)綜合考評分?jǐn)?shù)自動輸入評語——IF函數(shù)    105
5.2.2　判斷綜合評分是否低于平均值——IF函數(shù)    106
5.2.3　判斷食品添加劑含量是否超標(biāo)——IF函數(shù)    107
5.2.4　根據(jù)性別和年齡判斷是否為退休人員——IF函數(shù)    108
5.2.5　屏蔽公式產(chǎn)生的錯誤值——IFERROR函數(shù)    109
初試鋒芒    111
第6章　火眼金睛檢索數(shù)據(jù)
6.1　查找指定的數(shù)據(jù)    114
6.1.1　根據(jù)商品名稱查詢商品庫存——VLOOKUP函數(shù)    114
6.1.2　抽取隨機(jī)獎品——CHOOSE函數(shù)    115
6.1.3　根據(jù)書名查找在貨架上的位置——LOOKUP函數(shù)    116
6.1.4　查詢指定員工的實(shí)際工作天數(shù)——HLOOKUP函數(shù)    118
6.1.5　查詢10天內(nèi)第幾天的產(chǎn)量最高——MATCH函數(shù)    120
6.1.6　自動提取員工各項(xiàng)考勤數(shù)據(jù)——INDEX函數(shù)    122
6.2　引用單元格    124
6.2.1　根據(jù)給定參數(shù)返回單元格地址——ADDRESS函數(shù)    124
6.2.2　統(tǒng)計引用的數(shù)組個數(shù)——AREAS函數(shù)    126
6.2.3　解決VLOOKUP函數(shù)無法完成的查詢?nèi)蝿?wù)——OFFSET函數(shù)    127
6.2.4　跨表合并員工各月工資——INDIRECT函數(shù)    131
6.2.5　自動輸入不會打亂順序的序號——ROW函數(shù)    133
6.2.6　統(tǒng)計生產(chǎn)步驟——ROWS函數(shù)    134
6.2.7　自動生成工資條——COLUMN函數(shù)    135
6.2.8　統(tǒng)計工資構(gòu)成項(xiàng)的數(shù)量——COLUMNS函數(shù)    137
初試鋒芒    138
第7章　專注時效的日期與時間函數(shù)
7.1　返回當(dāng)前日期和時間    140
7.1.1　計算從當(dāng)前日期到項(xiàng)目結(jié)束剩余天數(shù)——TODAY函數(shù)    140
7.1.2　計算秒殺活動距離結(jié)束的天數(shù)——NOW函數(shù)    141
7.2　根據(jù)給定的參數(shù)計算日期和時間    142
7.2.1　根據(jù)給定年份和月份返回第一天和最后一天日期——DATE函數(shù)    142
7.2.2　提取直播結(jié)束的具體時間——TIME函數(shù)    143
7.2.3　提取出生年份——YEAR函數(shù)    144
7.2.4　提取員工出生月份——MONTH函數(shù)    146
7.2.5　根據(jù)發(fā)貨日期計算結(jié)款日期——DAY函數(shù)    146
7.2.6　計算最遲還款日期——EOMONTH函數(shù)    148
7.2.7　設(shè)置近期還款提醒——EDATE函數(shù)    149
7.2.8　計算指定日期是星期幾——WEEKDAY函數(shù)    150
7.2.9　計算指定日期是當(dāng)年的第幾周——WEEKNUM函數(shù)    153
7.2.10　從時間值中提取小時數(shù)——HOUR函數(shù)    154
7.2.11　計算計時工資——MINUTE函數(shù)    155
7.2.12　計算游船租賃總時長——SECOND函數(shù)    156
7.3　計算期間差    157
7.3.1　計算項(xiàng)目歷時天數(shù)——DAYS360函數(shù)    157
7.3.2　根據(jù)出生日期計算實(shí)際年齡——DATEDIF函數(shù)    158
7.3.3　計算產(chǎn)品的實(shí)際加工天數(shù)——NETWORKDAYS函數(shù)    159
7.3.4　根據(jù)起始日期返回指定工作日之后的日期——WORKDAY函數(shù)    160
初試鋒芒    162
第8章　精打細(xì)算的財務(wù)函數(shù)
8.1　投資計算函數(shù)    164
8.1.1　計算銀行存款的本金和利息總額——FV函數(shù)    164
8.1.2　計算投資的期值——FVSCHEDULE函數(shù)    164
8.1.3　計算貸款每年應(yīng)償還的利息——IPMT函數(shù)    166
8.1.4　計算等額本金還款時需要支付的利息——ISPMT函數(shù)    167
8.1.5　計算指定期限內(nèi)達(dá)到目標(biāo)存款額時每月存款額——PMT函數(shù)    168
8.1.6　計算一筆投資5年后的凈現(xiàn)值——NPV函數(shù)    169
8.1.7　根據(jù)已知條件計算貸款總額——PV函數(shù)    170
8.1.8　求存款額達(dá)到20萬需要幾年——NPER函數(shù)    171
8.2　折舊計算函數(shù)    172
8.2.1　計算資產(chǎn)折舊值——DB函數(shù)    173
8.2.2　使用雙倍余額遞減法計算資產(chǎn)折舊值——DDB函數(shù)    174
8.2.3　計算指定時間段的資產(chǎn)的折舊值——VDB函數(shù)    175
8.2.4　按年或月計算商務(wù)車的線性折舊值——SLN函數(shù)    176
8.2.5　計算設(shè)備在指定年限時的折舊值——SYD函數(shù)    177
8.3　償還率計算函數(shù)    178
8.3.1　計算投資火鍋店的收益率——IRR函數(shù)    178
8.3.2　計算投資的修正收益率——MIRR函數(shù)    179
8.3.3　計算分期投資的年利率——RATE函數(shù)    180
8.3.4　計算不定期投資和收入的收益率——XIRR函數(shù)    180
初試鋒芒    182
第9章　玩轉(zhuǎn)數(shù)值格式的文本函數(shù)
9.1　查找字符位置與提取文本    184
9.1.1　提取指定字符的位置——FIND函數(shù)    184
9.1.2　按字節(jié)數(shù)量提取目標(biāo)字符的位置——FINDB函數(shù)    185
9.1.3　計算指定文本串的長度——LEN函數(shù)    186
9.1.4　忽略大小寫查找指定字符的位置——SEARCH函數(shù)    187
9.1.5　從地址中提取城市信息——LEFT函數(shù)    188
9.1.6　根據(jù)字節(jié)提取產(chǎn)品規(guī)格——LEFTB函數(shù)    189
9.1.7　從最后一個字向前提取字符——RIGHT函數(shù)    190
9.1.8　利用字節(jié)數(shù)量提取業(yè)主的樓號——RIGHTB函數(shù)    192
9.1.9　從字符串的指定位置提取指定數(shù)量的字符——MID函數(shù)    192
9.2　文本新舊替換    194
9.2.1　替換字符串中指定位置的內(nèi)容——REPLACE函數(shù)    194
9.2.2　替換字符串中的指定內(nèi)容——SUBSTITUTE函數(shù)    196
9.3　文本格式的轉(zhuǎn)換    197
9.3.1　將全角字符轉(zhuǎn)換為半角字符——ASC函數(shù)    197
9.3.2　將數(shù)字轉(zhuǎn)換為帶$符號的貨幣格式——DOLLAR函數(shù)    198
9.3.3　將數(shù)字轉(zhuǎn)換為帶￥符號的貨幣格式——RMB函數(shù)    198
9.3.4　將文本型數(shù)字轉(zhuǎn)換成數(shù)值型數(shù)字——VALUE函數(shù)    200
9.3.5　按照代碼將數(shù)字轉(zhuǎn)換成指定格式——TEXT函數(shù)    201
9.3.6　判斷銷售盈虧情況——TEXT函數(shù)    204
9.3.7　自動返回銷售業(yè)績考核等級——TEXT函數(shù)    206
9.3.8　為數(shù)字添加千位分隔符并四舍五入到指定位數(shù)——FIXED函數(shù)    207
9.3.9　識別文本字符串——T函數(shù)    208
9.3.10　將所有字母轉(zhuǎn)換為大寫——UPPER函數(shù)    209
9.3.11　將品牌名稱的所有英文字母轉(zhuǎn)換為小寫——LOWER函數(shù)    209
9.3.12　將英文商品名稱的每個單詞轉(zhuǎn)換為首字母大寫——PROPER函數(shù)    210
9.4　數(shù)據(jù)的整理    211
9.4.1　合并品牌和型號獲取手機(jī)基本信息——CONCATENATE函數(shù)    211
9.4.2　刪除多余空格只保留一個作為分隔——TRIM函數(shù)    212
9.4.3　清除產(chǎn)品規(guī)格參數(shù)中的換行符——CLEAN函數(shù)    213
9.4.4　比較應(yīng)付款和實(shí)付款是否存在差異——EXACT函數(shù)    214
9.4.5　用心形圖標(biāo)直觀展示商品好評率——REPT函數(shù)    215
初試鋒芒    217
第10章　Excel中的信息提取專家
10.1　提取文件或操作環(huán)境信息    220
10.1.1　提取當(dāng)前工作表和工作簿信息——CELL函數(shù)    220
10.1.2　提取操作系統(tǒng)版本——INFO函數(shù)    221
10.2　檢查數(shù)值屬性    223
10.2.1　判斷當(dāng)日銷售的各種產(chǎn)品是否有銷量——ISNONTEXT函數(shù)    224
10.2.2　判斷產(chǎn)品檢測結(jié)果是否合格——ISTEXT函數(shù)    225
10.2.3　提取有效的考試成績——ISNUMBER函數(shù)    226
10.2.4　根據(jù)尾號判斷車輛是否限行——ISEVEN函數(shù)    227
10.2.5　根據(jù)身份證號碼判斷性別——ISODD函數(shù)    228
10.2.6　判斷數(shù)據(jù)的類型——TYPE函數(shù)    229
10.2.7　判斷哪些員工無銷量——ISBLANK函數(shù)    230
10.2.8　判斷微量元素的計算結(jié)果是否為錯誤值——ISERROR函數(shù)    231
10.2.9　判斷查詢的商品是否在商品列表中——ISERR/ISNA函數(shù)    232
10.2.10　判斷員工是否有開單獎金——ISLOGICAL函數(shù)    233
初試鋒芒    234
第11章　Excel數(shù)據(jù)分析的秘密
11.1　函數(shù)在條件格式中的應(yīng)用    236
11.1.1　內(nèi)置條件格式的應(yīng)用    236
11.1.2　從第2處開始突出顯示重復(fù)姓名    237
11.1.3　突出顯示每門學(xué)科的最高成績    239
11.1.4　將合計銷量最小的商品整行突出顯示    240
11.1.5　將30天內(nèi)到期的合同整行突出顯示    241
11.1.6　將大于等于90分的成績標(biāo)識成“優(yōu)秀”    242
11.2　函數(shù)在數(shù)據(jù)驗(yàn)證中的應(yīng)用    243
11.2.1　數(shù)據(jù)驗(yàn)證的基本應(yīng)用方法    243
11.2.2　禁止輸入重復(fù)的工號    244
11.2.3　禁止在單元格中輸入空格    245
11.2.4　禁止向指定區(qū)域內(nèi)輸入任何內(nèi)容    246
初試鋒芒    247