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

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