Excel 2007函數(shù)與公式實戰(zhàn)技巧精粹

緒論  最佳Excel學習方法 1
1  成為Excel高手的捷徑 1
1.1  積極的心態(tài) 1
1.2  正確的學習方法 2
1.3  善用資源，學以致用 4
1.4  多閱讀，多實踐 5
2  通過互聯(lián)網(wǎng)獲取學習資源和解題方法 5
2.1  搜索引擎的使用 6
2.2  搜索業(yè)內(nèi)網(wǎng)站的內(nèi)容 7
2.3  在新聞組或BBS中學習 7
2.4  在視頻網(wǎng)站中學習 10
2.5  利用微博和微信學習 10
3  函數(shù)與公式學習指南 11
3.1  打好函數(shù)基礎 11
3.2  由淺入深，由簡至繁學好函數(shù)公式 11
3.3  學好函數(shù)公式的基本方法 11
3.4  厚積薄發(fā)，脫胎換骨 12
3.5  學以致用，自由馳騁 12 第一篇  函數(shù)導讀 13 第1章  函數(shù)與公式基礎介紹 14
技巧1  什么是公式 14
技巧2  快速掌握運算符 14
2.1  運算符的類型及用途 14
2.2  公式的運算順序 15
技巧3  透視“單元格引用” 16
3.1  A1引用 16
3.2  R1C1引用 17
3.3  引用運算符 17
3.4  相對引用 18
3.5  絕對引用 19
技巧4  靈活處理跨工作表、工作簿引用 20
4.1  跨表引用 20
4.2  跨工作簿引用 21
技巧5  公式的快速批量復制 21
技巧6  公式的自動擴展 22
技巧7  公式中的結構化引用 23
7.1  直觀的結構化引用 23
7.2  自動擴展 25
技巧8  慧眼識函數(shù) 26
技巧9  函數(shù)的類型 27
技巧10  Excel 2007函數(shù)的新特性 29
技巧11  函數(shù)參數(shù)的省略與簡寫 29
技巧12  使用通配符進行模糊匹配 30
技巧13  通配符在多表引用中的妙用 32
技巧14  認識公式中的數(shù)據(jù) 33
14.1  數(shù)據(jù)類型 33
14.2  數(shù)據(jù)排序規(guī)則 33
14.3  常量數(shù)據(jù) 33
技巧15  文本型數(shù)字轉(zhuǎn)換為數(shù)值 34
15.1  利用查錯工具批量轉(zhuǎn)換 34
15.2  文本型數(shù)字轉(zhuǎn)數(shù)值的公式方法 35
技巧16  邏輯值與數(shù)值的互換 36
16.1  邏輯值與數(shù)值互換準則 36
16.2  用數(shù)學運算替代邏輯函數(shù) 37
技巧17  正確區(qū)分空文本與空單元格 38
17.1  空單元格與空文本的差異 38
17.2  防止空單元格影響統(tǒng)計結果 39
17.3  讓空單元格不顯示為0 39
技巧18  計算規(guī)范與限制 40
18.1  數(shù)字計算精度限制 40
18.2  函數(shù)嵌套層數(shù)限制 41
18.3  函數(shù)參數(shù)個數(shù)的限制 41
技巧19  以顯示的精度進行計算 42
技巧20  易失性函數(shù) 43
20.1  什么是易失性 43
20.2  具有易失性表現(xiàn)的函數(shù) 43
20.3  易失性的利用與規(guī)避 44
技巧21  自動重算和手動重算 44
技巧22  循環(huán)引用和迭代計算 45
22.1  For…Next循環(huán) 46
22.2  Do…While循環(huán) 48
22.3  記錄操作時間 48
22.4  已知和值求組合 49
技巧23  自動完成公式 50
技巧24  妙用函數(shù)提示 51
24.1  提示函數(shù)語法 51
24.2  參數(shù)含義提示 52
24.3  快速選定相應參數(shù) 52
24.4  獲取函數(shù)幫助信息 52
技巧25  公式的查錯 53
25.1  公式常見錯誤類型 53
25.2  錯誤自動檢查 54
技巧26  公式審核和監(jiān)控 55
26.1  錯誤檢查 55
26.2  審核和監(jiān)控 55
技巧27  分步查看公式結果 56
27.1  使用公式審核工具分步求值 57
27.2  用F9鍵查看公式運算結果 58
技巧28  如何閱讀和理解復雜公式 58 第2章  數(shù)組公式入門 62
技巧29  神奇的數(shù)組 62
29.1  數(shù)組的概念及分類 62
29.2  數(shù)組的維度和尺寸 63
技巧30  多項計算和數(shù)組公式 63
30.1  多項計算 63
30.2  數(shù)組公式 64
技巧31  多單元格數(shù)組公式 65
技巧32  數(shù)組的直接運算 66
32.1  數(shù)組與單值直接運算 66
32.2  同方向一維數(shù)組之間的直接運算 66
32.3  不同方向一維數(shù)組之間的直接運算 67
32.4  一維數(shù)組與二維數(shù)組之間的直接運算 67
32.5  二維數(shù)組之間的直接運算 68
技巧33  數(shù)組公式中的邏輯運算 69 第3章  靈活使用名稱 71
技巧34  名稱的奧秘 71
技巧35  多種方法定義名稱 73
35.1  使用“定義名稱”功能 73
35.2  使用名稱框創(chuàng)建 74
35.3  根據(jù)所選內(nèi)容批量創(chuàng)建 75
技巧36  名稱的規(guī)范與限制 76
技巧37  通過名稱定位區(qū)域 77
技巧38  以圖形方式查看名稱 77
技巧39  查看和修改名稱 78
39.1  導出名稱列表 78
39.2  編輯修改名稱 78
39.3  刪除名稱 79
技巧40  認識名稱的作用范圍 80
技巧41  根據(jù)單元格顏色進行統(tǒng)計 81
技巧42  固定引用區(qū)域的名稱 82
技巧43  使用名稱動態(tài)引用圖片 83
技巧44  在其他功能中使用名稱 85
44.1  在條件格式和有效性中使用他表數(shù)據(jù) 85
44.2  在圖表中使用名稱 86
技巧45  創(chuàng)建動態(tài)引用名稱 87
技巧46  自動更新的打印區(qū)域 88 第二篇  函數(shù)常用技巧 91 第4章  邏輯與信息函數(shù) 92
技巧47  邏輯函數(shù)的判斷功能 92
技巧48  邏輯值在條件匯總中的妙用 93
48.1  條件計數(shù) 93
48.2  條件匯總 94
48.3  多條件邏輯 94
技巧49  快速判決數(shù)據(jù)的簡單特性 95
技巧50  從身份證號碼中提取性別 96
技巧51  數(shù)據(jù)的模糊匯總 97
技巧52  獲取當前操作環(huán)境信息 97
技巧53  捕捉當前活動單元格信息 98
技巧54  提取工作表和工作簿的名稱 100
54.1  提取工作表名稱 100
54.2  提取工作簿名稱 101
技巧55  當公式報錯時返回空白 101
55.1  舊版美化公式 102
55.2  新版簡化公式 102
技巧56  N函數(shù)在函數(shù)公式中的應用 103
56.1  將邏輯值轉(zhuǎn)化為數(shù)值 103
56.2  生成內(nèi)存數(shù)組 104
技巧57  強制INDEX函數(shù)返回內(nèi)存數(shù)組 105 第5章  文本處理函數(shù) 107
技巧58  意義非凡的文本連接符& 107
技巧59  處理文本的基本手段 108
59.1  文本截取 108
59.2  查找定位 109
59.3  文本替換 110
59.4  文本比較 112
59.5  文本重復 112
技巧60  處理字符的基本技巧 112
技巧61  生成字母序列ABC…… 115
61.1  CHAR+CODE方式 115
61.2  列標提取方式 116
技巧62  生成可換行的文本 116
技巧63  規(guī)整不規(guī)范的日期數(shù)據(jù) 118
63.1  MID函數(shù)文本截取法 119
63.2  TEXT函數(shù)方式 119
技巧64  巧用身份證填寫個人信息 120
64.1  提取性別 121
64.2  出生日期 121
64.3  年齡 121
技巧65  財務數(shù)字金額拆分實例 122
技巧66  分離中文、數(shù)字和字母 123
66.1  分離中文與數(shù)字 124
66.2  字母與中文 124
66.3  提取數(shù)字 125
技巧67  統(tǒng)計不重復數(shù)字個數(shù) 126
技巧68  根據(jù)固定間隔符號截取字符串 127
68.1  FIND+SUBSTITUTE 127
68.2  展寬隔離符號 127
技巧69  根據(jù)間隔符統(tǒng)計項目個數(shù) 128
技巧70  格式化設備編號 129
技巧71  數(shù)字格式化為中文數(shù)字 130
71.1  數(shù)字整體轉(zhuǎn)換 130
71.2  逐字符轉(zhuǎn)換 131
71.3  日期格式 131
技巧72  轉(zhuǎn)換中文大寫金額 132
技巧73  以小時累計時間 132
技巧74  合并單元格區(qū)域中的文本 133 第6章  數(shù)學計算函數(shù) 135
技巧75  基本的數(shù)學計算函數(shù) 135
75.1  求和與求積 135
75.2  乘冪與對數(shù) 136
75.3  組合數(shù)與階乘 137
75.4  最小公倍數(shù)和最大公約數(shù) 137
75.5  進制轉(zhuǎn)換函數(shù) 138
75.6  絕對值和?值 138
75.7  弧度轉(zhuǎn)換為度數(shù) 139
75.8  符號函數(shù) 139
技巧76  出神入化的舍入函數(shù) 139
76.1  基本取整函數(shù) 139
76.2  指定數(shù)字位舍入 140
76.3  特定基數(shù)取整 141
76.4  舍入至奇數(shù)和偶數(shù) 141
技巧77  特定條件的舍入設計 142
77.1  設計一舍二入方式 142
77.2  0.2單位和0.5單位舍入 143
技巧78  四舍六入五單雙 143
技巧79  指定有效數(shù)字 144
技巧80  駕馭求模函數(shù) 145
80.1  初始求模函數(shù) 145
80.2  判斷數(shù)字奇偶性 146
80.3  生成循環(huán)序列 147
技巧81  正整數(shù)逐位累加至1位數(shù)字 148
81.1  速算法 148
81.2  速算擴展 149
技巧82  數(shù)字校驗碼的應用 149
技巧83  隨機產(chǎn)生數(shù)字與字母 150
83.1  初識隨機函數(shù) 150
83.2  隨機產(chǎn)生數(shù)字與字母 150
技巧84  生成不重復隨機序列 151
84.1  輔助列方式 152
84.2  直接方式 152 第7章  日期與時間函數(shù) 153
技巧85  認識日期與時間數(shù)據(jù) 153
85.1  日期與時間數(shù)據(jù)的本質(zhì) 153
85.2  日期與時間數(shù)據(jù)的識別 153
85.3  輸入日期時間的注意事項 154
技巧86  基本的日期函數(shù) 154
86.1  日期生成函數(shù) 154
86.2  年、月、日函數(shù) 155
86.3  當前日期 155
86.4  星期相關函數(shù) 155
86.5  工作日相關函數(shù) 156
技巧87  基本的時間函數(shù) 157
87.1  時間生成函數(shù) 157
87.2  時、分、秒 158
87.3  當前時間 158
技巧88  處理1900年之前的日期 158
技巧89  判斷閏年和平年 159
技巧90  2014年巴西世界杯倒計時 160
技巧91  快速確定員工的退休日期 160
技巧92  判斷一個日期所在的季度 161
技巧93  推算所在季度的季末日期 162
技巧94  計算日期所在季度天數(shù) 163
技巧95  計算指定日期鄰近月份的天數(shù) 163
技巧96  動態(tài)生日提醒 164
技巧97  計算公司員工的工齡 165
97.1  以年為單位 165
97.2  精確到月份 166
技巧98  身份證中提取周歲信息 167
技巧99  計算食品的保質(zhì)期 169
技巧100  目標日期是所在年第幾天 169
100.1  點對點相減 169
100.2  日期值序列 170
技巧101  計算兩個日期相差的年月日數(shù) 170
技巧102  下一個星期日和最近的星期日 171
102.1  日期值序列思路 171
102.2  星期幾的歸零思路 172
技巧103  指定日期范圍內(nèi)星期幾出現(xiàn)的次數(shù) 172
技巧104  推算指定月份第幾個星期幾的日期 173
技巧105  日期A從日期B算起是第幾周 173
技巧106  計算指定月份第幾周的天數(shù) 174
技巧107  計算兩個日期之間的凈工作日 175
技巧108  根據(jù)停車時間計算費用 177
108.1  計費時間 177
108.2  每小時費用 178
108.3  總費用 178
技巧109  將時間轉(zhuǎn)換成標準格式 178
技巧110  三班倒工作時長計算 179
技巧111  在時間日期數(shù)據(jù)中提取時間或日期 180
111.1  提取日期數(shù)據(jù) 180
111.2  提取時間數(shù)據(jù) 180
技巧112  生成指定范圍的隨機時間 181
112.1  以分為單位隨機變化 181
112.2  以秒為單位隨機變化 181 第8章  查找與引用函數(shù) 182
技巧113  動態(tài)按行或列生成序列號 182
技巧114  認識INDIRECT函數(shù) 183
技巧115  建立恒定的引用區(qū)域 183
技巧116  輕松調(diào)用名稱和“表”數(shù)據(jù) 184
技巧117  應用INDIRECT函數(shù)組合查詢 185
技巧118  實現(xiàn)數(shù)據(jù)區(qū)域的行列轉(zhuǎn)換 186
技巧119  引用區(qū)域的位置變換 187
技巧120  批量生成工資條 187
技巧121  現(xiàn)解OFFSET函數(shù) 188
121.1  圖解OFFSET參數(shù)含義 188
121.2  參數(shù)為數(shù)組產(chǎn)生多維引用 189
技巧122  應用OFFSET函數(shù)分析銷售業(yè)績 190
技巧123  按自定義順序排序 191
技巧124  根據(jù)多個條件查詢數(shù)據(jù) 192
技巧125  查找不重復值 193
技巧126  為圖書目錄自動編號 193
技巧127  根據(jù)首行或首列查找記錄 194
技巧128  為股票名稱編制拼音簡碼 196
技巧129  模糊查找符合條件的記錄 198
技巧130  模糊轉(zhuǎn)換數(shù)據(jù) 198
技巧131  應用LOOKUP函數(shù)實現(xiàn)單條件多值求和 199
技巧132  多區(qū)間判斷數(shù)值等級 200
技巧133  亂序查找最后一個滿足條件的記錄 201
技巧134  填補合并單元格的空缺 202
技巧135  多條件逆序篩選 202
技巧136  多條件順序篩選 203
技巧137  根據(jù)查找結果建立超鏈接 204 第9章  統(tǒng)計分析函數(shù) 206
技巧138  統(tǒng)計總分前N名的平均成績 206
技巧139  AVERAGEIF和AVERAGEIFS函數(shù)求解條件均值 207
技巧140  返回數(shù)據(jù)表中的最小正數(shù) 208
技巧141  利用極限函數(shù)設定銷售限價 209
技巧142  實現(xiàn)多表統(tǒng)計人員工資 209
技巧143  校驗編碼中數(shù)字位數(shù)的有效性 211
技巧144  認識COUNTA與COUNTBLANK函數(shù) 212
技巧145  COUNTIF函數(shù)條件判斷介紹 213
技巧146  無序情況下的分類匯總 215
技巧147  查找不重復值 216
技巧148  單字段多條件計數(shù) 217
技巧149  多字段多條件計數(shù) 217
技巧150  應用SUMPRODUCT函數(shù)多條件計數(shù) 218
技巧151  應用SUMPRODUCT函數(shù)多條件求和 218
技巧152  應用SUMPRODUCT函數(shù)排序 219
技巧153  計算絕對值之和 220
技巧154  二維區(qū)域條件求和 220
技巧155  計算綜合得分 222
技巧156  多字段多條件求和 223
技巧157  多表數(shù)據(jù)條件求和 223
技巧158  FREQUENCY函數(shù)技巧二則 224
技巧159  按成績實現(xiàn)班級和年級排名 226
技巧160  計算百分位排名 227
技巧161  剔除極值算平均得分 228
技巧162  篩選和隱藏狀態(tài)下的統(tǒng)計 229
技巧163  生成連續(xù)的序號 230
技巧164  眾數(shù)的妙用 231 第10章  數(shù)據(jù)庫函數(shù) 233
技巧165  數(shù)據(jù)庫函數(shù)的基本技巧 233
技巧166  利用通配符查詢產(chǎn)品信息 235
技巧167  理解條件區(qū)域使用相對引用產(chǎn)生的偏移 236
技巧168  統(tǒng)計指定條件下公司部門的人員數(shù) 238
技巧169  統(tǒng)計企業(yè)各部門的人員工資 238
技巧170  返回車間生產(chǎn)產(chǎn)品的最低成本 239
技巧171  計算商場指定商品的平均銷售價格 240
技巧172  提取符合條件的信息 240 第11章  財務金融函數(shù) 242
技巧173  普通復利終值與現(xiàn)值計算 242
技巧174  年金終值與現(xiàn)值計算 243
技巧175  固定利率下混合現(xiàn)金流的終值計算 244
技巧176  變動利率下混合現(xiàn)金流的終值計算 244
技巧177  固定資產(chǎn)投資的動態(tài)回收期計算 245
技巧178  現(xiàn)金流不定期條件下的決策分析 246
技巧179  名義利率與實際利率 248
技巧180  債券發(fā)行價格的計算 250
技巧181  每年付息債券的持有收益率計算 251
技巧182  折舊計算函數(shù) 251
第12章  工程函數(shù) 256
技巧183  貝塞爾（Bessel）函數(shù) 256
技巧184  度量衡轉(zhuǎn)換函數(shù) 257
技巧185  數(shù)字進制轉(zhuǎn)換函數(shù) 258
技巧186  用于篩選數(shù)據(jù)的函數(shù) 259
技巧187  誤差函數(shù)相關計算的函數(shù) 262
技巧188  復數(shù)計算的函數(shù) 263 第13章  宏表函數(shù) 264
技巧189  認識宏表及宏表函數(shù) 264
技巧190  取指定路徑下的所有文件名 269
技巧191  判斷是否打開了指定的工作簿 270
技巧192  取得當前工作簿中所有工作表名稱 271
技巧193  取得當前行所在的打印頁碼 271
技巧194  取得單元格屬性值 272
技巧195  計算文本表達式中的結果 274
技巧196  將文本字符串變成常量數(shù)組 276 第14章  循環(huán)引用 277
技巧197  認識循環(huán)引用 277
技巧198  記錄單元格操作時間 278
技巧199  循環(huán)引用計算的一般技巧 279
技巧200  利用循環(huán)引用實現(xiàn)字符串連接 280 第三篇  函數(shù)綜合應用技巧 281 第15章  數(shù)字及文本處理技術 282
技巧201  提取文本中的數(shù)值 282
技巧202  提取文本中所有的數(shù)字 284
技巧203  數(shù)值格式化修約 286
技巧204  插值計算 286
技巧205  提取帶有固定分割符文本中的一項內(nèi)容 287
技巧206  提取含有某些特性的文本 288 第16章  條件篩選技術 291
技巧207  一列數(shù)據(jù)提取不重復數(shù)據(jù) 291
技巧208  二維表提取不重復值 292
技巧209  帶條件的提取不重復值 293
技巧210  統(tǒng)計唯一值的數(shù)量 296
技巧211  根據(jù)鐵路里程表篩選里程 297
技巧212  用先進先出法計算出貨價值 297 第17章  數(shù)據(jù)排名與排序技術 299
技巧213  對數(shù)據(jù)進行中式排名 299
技巧214  按排名占比確定等級 301
技巧215  對相同成績平均排名名次 301
技巧216  對數(shù)值大小進行排序 302
技巧217  對文本進行排序 303
技巧218  按自定義順序排序 305
技巧219  按數(shù)據(jù)出現(xiàn)次數(shù)排序 305 第18章  數(shù)據(jù)重構技巧 307
技巧220  數(shù)字的按位與或非 307
技巧221  利用MMULT函數(shù)實現(xiàn)數(shù)據(jù)累加 308
技巧222  幾個典型構造矩陣的應用 308
技巧223  計算各類貨品最大入貨量中的最小值 311
技巧224  填充合并單元格 312 第19章  數(shù)據(jù)表處理技巧 313
技巧225  對數(shù)據(jù)總表的模糊查詢 313
技巧226  按日期和品牌進行匯總 316
技巧227  按客戶名對送貨清單進行分組 318
技巧228  將分表數(shù)據(jù)合并到總表 320
技巧229  將總表數(shù)據(jù)拆分到分表 321 第20章  處理日期數(shù)據(jù) 323
技巧230  轉(zhuǎn)換中文日期為規(guī)范日期 323
技巧231  將標準日期轉(zhuǎn)為英文日期 324
技巧232  推算母親節(jié)和父親節(jié) 324
技巧233  制作簡易萬年歷 325
技巧234  陽歷與農(nóng)歷之間的轉(zhuǎn)換 327
技巧235  推測某年度二十四節(jié)氣的具體日期 330
技巧236  推算陽歷日期所屬生肖年 331
技巧237  年月日時的天干地支 333 第21章  多維引用技術 338
技巧238  計算應收款賬齡 338
技巧239  統(tǒng)計營銷任務完成的員工人數(shù) 339
技巧240  多表多條件匯總技術 341
技巧241  創(chuàng)建動態(tài)適應篩選條件的統(tǒng)計公式 342
技巧242  對數(shù)據(jù)列進行分區(qū)域統(tǒng)計 343 第四篇  其他功能中的函數(shù)應用 345 第22章  條件格式中使用函數(shù) 346 第23章  數(shù)據(jù)有效性中使用函數(shù) 367 第24章  數(shù)據(jù)透視表函數(shù)綜合應用 382 第25章  圖形制作中的函數(shù)應用 406
第五篇  實例作證 415 第26章  經(jīng)典實例 416
技巧279  先進先出計算進貨庫存和銷售成本 416
技巧280  汽車里程修理費補貼計算 421
技巧281  動態(tài)有效性點菜單 423
技巧282  速算個人所得稅 425
技巧283  提示豐富的萬年歷 426
技巧284  加油站投資項目動態(tài)盈虧平衡分析 433
技巧285  按照BOM表自動生成材料用量計劃表 439
技巧286  動態(tài)統(tǒng)計截至當月的計劃完成率 441
技巧287  從多張工作表中按需查找引用數(shù)據(jù) 442
技巧288  根據(jù)房間人數(shù)智能分攤水電費