速查速用·Excel公式與函數(shù)應(yīng)用技巧

第1章 公式編輯與數(shù)據(jù)引用技巧 1
1.1 公式的輸入與編輯 2
技巧1 查看長公式中的某一步計(jì)算結(jié)果 2
技巧2 快速選中公式中引用的單元格 3
技巧3 隱藏公式 3
技巧4 大范圍復(fù)制公式 5
技巧5 將公式運(yùn)算結(jié)果轉(zhuǎn)換為
數(shù)值 6
技巧6 查看工作表中的所有
公式 6
技巧7 公式記憶式鍵入 8
技巧8 審核公式 9
技巧9 為公式定義名稱 9
1.2 單元格的引用 10
技巧10 數(shù)據(jù)源的相對(duì)引用 11
技巧11 數(shù)據(jù)源的絕對(duì)引用 11
技巧12 通過混合引用創(chuàng)建乘
法表 13
技巧13 引用多張工作表中特定數(shù)據(jù)源進(jìn)行計(jì)算 13
技巧14 在相對(duì)引用和絕對(duì)引用之間進(jìn)行切換 16
技巧15 在不同工作表中引用區(qū)域名稱進(jìn)行計(jì)算 16
第2章 邏輯信息函數(shù)實(shí)戰(zhàn)
應(yīng)用技巧 18
2.1 邏輯運(yùn)算函數(shù) 19
技巧16 判斷員工的銷售額是否達(dá)標(biāo) 19
技巧17 根據(jù)編碼返回部門
名稱 20
技巧18 使用AND函數(shù)考評(píng)成績(jī)是否合格 20
技巧19 使用OR函數(shù)判斷員工考核是否達(dá)標(biāo) 21
技巧20 使用NOT函數(shù)進(jìn)行
篩選 21
技巧21 使用數(shù)據(jù)組公式比較兩組龐大數(shù)據(jù) 23
2.2 邏輯判斷函數(shù) 24
技巧22 使用IF函數(shù)判斷給定值是否在指定區(qū)間 24
技巧23 將工作表中的“0”
替換成“_” 26
技巧24 快速識(shí)別產(chǎn)品類別 27
技巧25 解決計(jì)算結(jié)果為“0”、錯(cuò)誤值的問題 28
第3章 日期與時(shí)間函數(shù)實(shí)戰(zhàn)
應(yīng)用技巧 30
3.1 日期函數(shù) 31
技巧26 使用DATEVALUE函數(shù)計(jì)算請(qǐng)假天數(shù) 31
技巧27 返回當(dāng)前日期與
星期數(shù) 32
技巧28 計(jì)算倒計(jì)時(shí)天數(shù) 32
技巧29 根據(jù)出生日期快速
計(jì)算年齡 34
技巧30 將非日期數(shù)據(jù)轉(zhuǎn)換為標(biāo)準(zhǔn)的日期 35
技巧31 判斷一個(gè)月的最大
天數(shù) 35
技巧32 在報(bào)表中自動(dòng)填寫當(dāng)前
月份 36
技巧33 已知一年中的第幾天，計(jì)算其準(zhǔn)確日期 36
技巧34 快速自動(dòng)追加
工齡工資 37
技巧35 批量計(jì)算員工工齡 39
技巧36 使用DATEDIF函數(shù)計(jì)算總借款天數(shù) 40
技巧37 使用DAYS360函數(shù)計(jì)算總借款天數(shù) 40
技巧38 計(jì)算還款剩余天數(shù) 42
技巧39 計(jì)算應(yīng)收賬款的
賬齡 43
技巧40 快速查看某一日期對(duì)應(yīng)
的星期數(shù) 43
技巧41 以中文星期數(shù)形式查看
日期對(duì)應(yīng)的星期數(shù) 44
技巧42 查看指定年份
各月天數(shù) 45
技巧43 計(jì)算指定日期到月底的
天數(shù) 45
技巧44 計(jì)算兩個(gè)日期之間的實(shí)
際工作日 46
技巧45 計(jì)算年假占全年工作日
的百分比 47
3.2 時(shí)間函數(shù) 47
技巧46 計(jì)算員工外出使用的
時(shí)間 48
技巧47 計(jì)算員工演講所用的
時(shí)間 50
第4章 數(shù)學(xué)計(jì)算函數(shù)實(shí)戰(zhàn)
應(yīng)用技巧 53
4.1 常規(guī)計(jì)算 54
技巧48 使用SUM函數(shù)
求和 54
技巧49 已知銷售量和銷售單價(jià)
計(jì)算其總銷售額 54
技巧50 在不連續(xù)的單元格中
求和 55
技巧51 統(tǒng)計(jì)某人的總
銷售金額 56
技巧52 統(tǒng)計(jì)兩位或多位人員的
總銷售金額 57
技巧53 將數(shù)據(jù)按照月份進(jìn)行
匯總 58
技巧54 統(tǒng)計(jì)不同時(shí)間段不同類
別產(chǎn)品的銷售金額 59
技巧55 使用SUMIF函數(shù)按條
件對(duì)數(shù)字進(jìn)行求和 59
技巧56 統(tǒng)計(jì)各部門數(shù)據(jù)
總數(shù) 60
技巧57 在SUMIF函數(shù)中使用通配符 61
技巧58 統(tǒng)計(jì)各個(gè)時(shí)段的銷售
總金額 61
技巧59 統(tǒng)計(jì)多種類別產(chǎn)品的
總銷售金額 62
技巧60 使用AVERAGE函數(shù)求
平均值 63
技巧61 使用SUMIFS函數(shù)實(shí)現(xiàn)
多條件統(tǒng)計(jì) 64
技巧62 統(tǒng)計(jì)某日期區(qū)間的銷售
金額 64
4.2 舍入計(jì)算 65
技巧63 使用INT函數(shù)對(duì)平均銷量取整 65
技巧64 使用ROUND函數(shù)按位數(shù)進(jìn)行四舍五入 66
技巧65 使用MOD函數(shù)
取余數(shù) 66
技巧66 計(jì)算加班時(shí)長 67
技巧67 使用FLOOR函數(shù)制定
產(chǎn)品價(jià)格 68
技巧68 使用CEILING函數(shù)計(jì)算
通話費(fèi)用 69
4.3 階乘與隨機(jī)數(shù) 69
技巧69 確定一組對(duì)象所有可能
的組合數(shù)目 70
技巧70 使用RAND函數(shù)隨機(jī)創(chuàng)
建彩票號(hào)碼 70
技巧71 使用PRODUCT函數(shù)計(jì)
算多個(gè)數(shù)字的乘積 71
技巧72 使用SUMPRODUCT函
數(shù)統(tǒng)計(jì)多種產(chǎn)品的總銷
售金額 71
技巧73 統(tǒng)計(jì)非工作日銷售
金額 71
第5章 文本信息函數(shù)實(shí)戰(zhàn)
應(yīng)用技巧 73
5.1 獲取特定文本信息 74
技巧74 使用CONCATENATE函數(shù)自動(dòng)提取序號(hào) 74
技巧75 從E-mail地址中提
取賬號(hào) 74
技巧76 使用RIGHT函數(shù)判斷性別 75
技巧77 使用REPLACE函數(shù)為電話號(hào)碼升級(jí) 76
技巧78 從身份證號(hào)碼中提取
出生日期 76
技巧79 從一段信息中分離三
段信息 77
技巧80 從單元格中提取有用的
數(shù)據(jù)并合并起來 78
技巧81 使用SUBSTITUTE函數(shù)
縮寫名稱 79
技巧82 去掉文本中的所有
空格 80
5.2 獲取其他文本信息 80
技巧83 使用LEN函數(shù)檢查身份
證位數(shù)是否正確 80
技巧84 計(jì)算各項(xiàng)課程的實(shí)際參
加人數(shù) 81
技巧85 檢查具有固定長度的字符串位數(shù)是否正確 81
技巧86 使用EXACT函數(shù)比
較兩個(gè)字符串是否
相同 82
技巧87 利用REPT函數(shù)一次性輸
入多個(gè)相同符號(hào) 83
技巧88 用“一”表示員工銷售
業(yè)績(jī) 83
技巧89 解決因四舍五入而造成
的顯示誤差問題 84
第6章 統(tǒng)計(jì)分析函數(shù)實(shí)戰(zhàn)
應(yīng)用技巧 85
6.1 平均值函數(shù) 86
技巧90 使用AVERAGEA函數(shù)求不連續(xù)的平均值 86
技巧91 求平均值時(shí)忽略計(jì)算區(qū)域中的0值 86
技巧92 求包含文本值的
平均值 87
技巧93 按指定條件求
平均值 88
技巧94 統(tǒng)計(jì)指定月份的平均銷售額 88
技巧95 在AVERAGEIF函數(shù)中使用通配符 89
技巧96 在AVERAGEIFS函數(shù)中使用通配符 90
技巧97 使用AVERAGEIFS
函數(shù)忽略計(jì)算中的
0值 91
技巧98 計(jì)算出滿足多重條件的數(shù)據(jù)的平均值 91
技巧99 使用GEOMEAN函數(shù)計(jì)算幾何平均值 91
技巧100 數(shù)據(jù)集的內(nèi)部
平均值 92
6.2 數(shù)理統(tǒng)計(jì)函數(shù) 92
技巧101 使用MODE函數(shù)
統(tǒng)計(jì)哪種商品最受
歡迎 92
技巧102 使用CRITBINOM
函數(shù)檢測(cè)產(chǎn)品的合
格數(shù) 93
技巧103 使用GROWTH函
數(shù)預(yù)測(cè)全年的銷售
金額 94
6.3 條目統(tǒng)計(jì)函數(shù) 94
技巧104 統(tǒng)計(jì)銷售記錄
條數(shù) 94
技巧105 統(tǒng)計(jì)包含文本值的單
元格數(shù) 95
技巧106 使用COUNT函數(shù)按條
件統(tǒng)計(jì) 96
技巧107 統(tǒng)計(jì)出各類產(chǎn)品的銷
售記錄條數(shù) 96
技巧108 在COUNTIF函數(shù)中使
用通配符 97
技巧109 統(tǒng)計(jì)出大于指定數(shù)值的記錄條數(shù) 98
技巧110 統(tǒng)計(jì)出大于某個(gè)單
元格中數(shù)值的記錄
條數(shù) 98
技巧111 統(tǒng)計(jì)出大于平均值的
記錄條數(shù) 99
技巧112 統(tǒng)計(jì)出指定區(qū)域中
滿足多個(gè)條件的記
錄數(shù)目 99
技巧113 統(tǒng)計(jì)空白單元格的
數(shù)目 100
6.4 最大值與最小值函數(shù) 100
技巧114 按指定條件返回最
高分 100
技巧115 返回?cái)?shù)據(jù)表中前三名
分?jǐn)?shù) 101
技巧116 返回?cái)?shù)據(jù)表中后三名
分?jǐn)?shù) 101
技巧117 使用MAX函數(shù)統(tǒng)計(jì)最
高分?jǐn)?shù) 102
技巧118 使用MIN函數(shù)統(tǒng)計(jì)最
低分?jǐn)?shù) 103
技巧119 求最小值時(shí)忽略
0值 103
技巧120 按條件求取
最大值 104
技巧121 計(jì)算學(xué)生考試成績(jī)
中最高分?jǐn)?shù)（包含
文本） 105
技巧122 計(jì)算學(xué)生考試成績(jī)
中最低分?jǐn)?shù)（包含
文本） 106
第7章 財(cái)務(wù)函數(shù)實(shí)戰(zhàn)應(yīng)用技巧 107
7.1 投資計(jì)算類函數(shù) 108
技巧123 使用PV函數(shù)計(jì)算某項(xiàng)
投資的現(xiàn)值 108
技巧124 使用PMT函數(shù)計(jì)算貸
款每期付款金額 109
技巧125 計(jì)算貸款指定期間的
本金償還金額 109
技巧126 計(jì)算貸款在兩個(gè)期
間累積償還的本金
數(shù)額 110
技巧127 使用FV函數(shù)計(jì)算某項(xiàng)
投資的未來值 110
技巧128 使用NPER函數(shù)計(jì)
算出貸款的清還
年數(shù) 111
技巧129 使用NPV函數(shù)計(jì)算某
項(xiàng)投資的凈現(xiàn)值 111
技巧130 計(jì)算出一組不定期盈
利額的凈現(xiàn)值 112
技巧131 計(jì)算某項(xiàng)投資的投資
期數(shù) 112
技巧132 計(jì)算某項(xiàng)投資在可變
利率下的未來值 113
7.2 償還率計(jì)算函數(shù) 113
技巧133 使用RATE函數(shù)計(jì)算某項(xiàng)借款的收益率 114
技巧134 使用IRR函數(shù)計(jì)算
某項(xiàng)投資的內(nèi)部收
益率 114
技巧135 計(jì)算某項(xiàng)投資的修正內(nèi)部收益率 114
7.3 折舊值計(jì)算函數(shù) 115
技巧136 使用SLN函數(shù)計(jì)算固
定資產(chǎn)的折舊值 115
技巧137 使用DB函數(shù)計(jì)算每年
的折舊值 116
技巧138 使用DB函數(shù)計(jì)算每月
的折舊值 116
技巧139 使用SYD函數(shù)計(jì)算
固定資產(chǎn)每年折
舊額 117
技巧140 使用DDB函數(shù)計(jì)
算固定資產(chǎn)每年折
舊額 118
技巧141 采用余額遞減法計(jì)算
累積折舊額 119
技巧142 使用直線法計(jì)算累計(jì)
折舊額 120
7.4 證券計(jì)算函數(shù) 121
技巧143 使用YIELD函數(shù)
計(jì)算有價(jià)證券的收
益率 121
技巧144 計(jì)算證券成交日和
到期日之間的付息
次數(shù) 121
第8章 查找和引用函數(shù)實(shí)戰(zhàn)
應(yīng)用技巧 123
8.1 普通查詢 124
技巧145 使用LOOKUP函
數(shù)查找信息（向
量型） 124
技巧146 使用LOOKUP函
數(shù)查找信息（數(shù)
組型） 125
技巧147 使用VLOOKUP函數(shù)進(jìn)行查詢 125
技巧148 使用CHOOSE函數(shù)判
斷成績(jī)是否合格 127
技巧149 求取一組數(shù)據(jù)的反轉(zhuǎn)
數(shù)據(jù) 127
8.2 引用查詢 128
技巧150 使用INDEX函數(shù)按指
定條件查找 128
技巧151 使用MATCH函數(shù)
返回指定元素所在
位置 128
技巧152 配合使用INDEX
與MATCH函數(shù)
查詢 129
技巧153 使用INDEX與MATCH
函數(shù)反向查詢 130
技巧154 使用INDEX與MATCH函數(shù)雙條件查詢 130
技巧155 使用TRANSPOSE函
數(shù)實(shí)現(xiàn)行列轉(zhuǎn)置 131
技巧156 使用OFFSET函數(shù)實(shí)
現(xiàn)動(dòng)態(tài)查詢 132
8.3 建立有規(guī)律的三級(jí)序列
編號(hào) 133
技巧157 使用ROW函數(shù)建立三
級(jí)序列編號(hào) 133
技巧158 使用COLUMN函數(shù)建
立三級(jí)序列編號(hào) 133
第9章 數(shù)據(jù)庫函數(shù)實(shí)戰(zhàn)
應(yīng)用技巧 135
9.1 DSUM函數(shù) 136
技巧159 統(tǒng)計(jì)銷售員一個(gè)月對(duì)應(yīng)的銷售數(shù)量 136
技巧160 使用DSUM函數(shù)
統(tǒng)計(jì)去除指定條件
的數(shù)據(jù) 136
技巧161 在DSUM函數(shù)中使用
通配符 137
技巧162 使用DSUM函數(shù)進(jìn)行
雙條件計(jì)算 138
技巧163 DSUM函數(shù)與SUMIF
函數(shù)的區(qū)別 138
9.2 DAVERAGE函數(shù) 139
技巧164 統(tǒng)計(jì)某個(gè)職業(yè)的
平均工資 139
技巧165 在DAVERAGE函數(shù)參
數(shù)中使用通配符 139
技巧166 使用DAVERAGE函數(shù)
進(jìn)行計(jì)算后查詢 140
9.3 DCOUNT函數(shù) 141
技巧167 使用DCOUNT函
數(shù)統(tǒng)計(jì)滿足條件的
數(shù)據(jù) 141
技巧168 使用DCOUNT函數(shù)忽
略0值統(tǒng)計(jì)數(shù)據(jù) 142
技巧169 使用DCOUNT函數(shù)實(shí)
現(xiàn)雙條件統(tǒng)計(jì) 142
9.4 其他函數(shù) 143
技巧170 統(tǒng)計(jì)基本工資小于800元的員工人數(shù) 143
技巧171 統(tǒng)計(jì)指定銷售日期
和產(chǎn)品名稱對(duì)應(yīng)的
銷量 143
第10章 函數(shù)返回錯(cuò)誤值的
解決辦法 145
技巧172 “#####”錯(cuò)誤值的解決辦法 146
技巧173 “#N/A”錯(cuò)誤值的解決辦法 146
技巧174 “#DIV/0!”錯(cuò)誤值的解決辦法 147
技巧175 “#NAME?”錯(cuò)誤值的解決辦法 148
技巧176 “#VALUE!”錯(cuò)誤值的解決辦法 149
技巧177 “#NUM!”錯(cuò)誤值的解決辦法 149
技巧178 “#REF!”錯(cuò)誤值的解決辦法 150
技巧179 “#NULL”錯(cuò)誤值的解決辦法 150