秒懂Excel函數應用技巧

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