職場(chǎng)力！寫(xiě)給經(jīng)理人的超強(qiáng)Excel函數(shù)效率提升術(shù)（含盤(pán)）

函數(shù)篇CHAPTER 01 函數(shù)的基本知識(shí) 1
01　求和按鈕達(dá)人 ····································································2
STEP01　快速輸入求和的技巧 ··················································2
STEP02　計(jì)算平均值、計(jì)數(shù)也是如法炮制 ································4
STEP03　聰明的按鈕 ·····························································6
02　所謂的函數(shù)是何方神圣 ······················································9
STEP01　按鈕到底做了些什么 ···············································9
STEP02　什么是函數(shù) ·······························································10
STEP03　什么是參數(shù) ·······························································10
STEP04　什么是引用 ······························································· 11
03　函數(shù)的輸入方法·······························································13
STEP01　只用鍵盤(pán)輸入 ···························································13
STEP02　用“鍵盤(pán)+ 鼠標(biāo)”輸入 ············································15
STEP03　用“插入函數(shù)”對(duì)話框輸入······································17
04　使用拖動(dòng)就能更改引用區(qū)域 ·············································20
STEP01　引用區(qū)域的確認(rèn)························································20
STEP02　引用區(qū)域的更改························································21
05　函數(shù)功能大提升·······························································24
06　練習(xí)題 ·············································································26
問(wèn)題01　快速將指定區(qū)域的合計(jì)值顯示在不相鄰的單元格中 ···26
問(wèn)題02　將引用區(qū)域更改為其他工作表 ···································27
問(wèn)題03　難題！幾乎無(wú)限大的加法工具 ···································27
函數(shù)篇CHAPTER 02　謎一樣的$ 29
01　復(fù)制函數(shù) ·········································································30
STEP01　復(fù)制公式 ··································································30
STEP02　引用區(qū)域被任意調(diào)整而感到困惑的范例····················31
STEP03　什么是相對(duì)引用························································32
STEP04　粉墨登場(chǎng)——**引用中的“$” ····························33
STEP05　各種**引用 ···························································34
02　排出次序的RANK 函數(shù) ···················································40
STEP01　輸入RANK 函數(shù)·······················································40
STEP02　設(shè)置為**引用························································42
STEP03　從分?jǐn)?shù)低的人開(kāi)始排名次 ·········································44
03　zui方便的查詢函數(shù)——VLOOKUP ··································46
STEP01　VLOOKUP 的基礎(chǔ)知識(shí) ············································46
STEP02　VLOOKUP 參數(shù)詳解 ················································48
STEP03　再度登場(chǎng)的**引用 ················································49

04　無(wú)論是行列轉(zhuǎn)置還是跳過(guò)一格都能隨意引用的OFFSET
函數(shù) ················································································53
STEP01　不可思議！縱向數(shù)據(jù)轉(zhuǎn)換成橫向數(shù)據(jù) ·······················53
STEP02　對(duì)角線上的引用························································57
STEP03　不管跳一格還是跳兩格都可使用OFFSET 函數(shù) ·······60
05　函數(shù)大提升 ······································································61
06　練習(xí)題 ·············································································62
問(wèn)題01　復(fù)雜的行加列**引用 ··············································62
問(wèn)題02　求出累加值 ································································62
問(wèn)題03　使用一張對(duì)照表來(lái)連續(xù)查找多列數(shù)據(jù) ························63
函數(shù)篇CHAPTER 03　靈活運(yùn)用文本 65
01　連接字符串 ······································································66
STEP01　利用“&”連接符來(lái)連接字符串································66
STEP02　連接〒、郵政編碼、地址，并以空格分隔 ················67
STEP03　能記住CONCATENATE 的拼法嗎 ···························68
02　轉(zhuǎn)換文本的類(lèi)型·······························································70
STEP01　切換大寫(xiě)、小寫(xiě)························································70
STEP02　切換全角、半角························································73
STEP03　顯示拼音標(biāo)注 ···························································76
03　截取字符串的一部分 ·······················································78
STEP01　從左邊截取 ·······························································78
STEP02　從右邊截取 ·······························································80
STEP03　從任一位置截取想要的部分······································82
04　查看文本的長(zhǎng)度、位置 ····················································85
STEP01　查看指定字符的位置 ················································85
STEP02　只截取位于“@”前面的字符串 ······························87
STEP03　查看文本長(zhǎng)度（一） ················································90
STEP04　查看文本長(zhǎng)度（二） ················································91
05　有趣又實(shí)用的文本函數(shù) ····················································94
STEP01　重復(fù)文本 ··································································94
STEP02　替換文本中的一部分 ················································97
STEP03　以自己想要的格式來(lái)顯示字符串 ······························99
06　函數(shù)大提升 ····································································103
07　練習(xí)題 ···········································································105
問(wèn)題01　顯示中文的拼音 ·······················································105
問(wèn)題02　在空格處換行 ··························································105
問(wèn)題03　將“對(duì)不起”改為m(__)m、將“哇~”改為(^o^) ···106

函數(shù)篇CHAPTER 04　徹底學(xué)會(huì)日期和時(shí)間的操作 107
01　Excel 中日期和時(shí)間的處理 ············································108
STEP01　日期和時(shí)間的輸入方式 ···········································108
STEP02　什么是序列值 ························································· 110
STEP03　日期的計(jì)算 ····························································· 112
STEP04　NOW 函數(shù) ······························································ 112
02　處理日期和時(shí)間····························································· 114
STEP01　更改數(shù)字顯示格式來(lái)顯示日期和時(shí)間 ····················· 114
STEP02　通過(guò)TEXT 函數(shù)來(lái)更改數(shù)字格式 ···························· 115
STEP03　取出年、月、日的元素 ··········································· 116
STEP04　取出時(shí)、分、秒的元素 ··········································· 118
03　操作序列值 ····································································120
STEP01　計(jì)算序列值的差······················································120
STEP02　從年月日來(lái)求出序列值 ···········································122
STEP03　從出生年月日來(lái)算出星座 ·······································124
04　麻煩的星期顯示·····························································127
STEP01　使用TEXT 函數(shù)來(lái)顯示星期 ···································127
STEP02　出生在星期幾 ·························································129
STEP03　使用CHOOSE 函數(shù)來(lái)顯示星期 ·····························132
05　函數(shù)大提升 ····································································134
06　練習(xí)題 ···········································································135
問(wèn)題01　古風(fēng)風(fēng)格的中文數(shù)字時(shí)鐘 ········································135
問(wèn)題02　按時(shí)間來(lái)變換顯示信息 ············································135
問(wèn)題03　萬(wàn)年歷當(dāng)月的行程預(yù)訂表 ········································136
函數(shù)篇CHAPTER 05　向IF 函數(shù)挑戰(zhàn) 137
01　根據(jù)年齡段來(lái)處理的IF 函數(shù) ·········································138
STEP01　IF 函數(shù) ···································································138
STEP02　如果為60 以上就顯示“good” ·····························139
STEP03　清除不必要的顯示 ··················································141
STEP04　將IF 函數(shù)設(shè)置成嵌套結(jié)構(gòu) ······································142
02　可統(tǒng)計(jì)個(gè)數(shù)的COUNTIF 函數(shù) ·······································145
STEP01　查找符合條件的數(shù)據(jù)個(gè)數(shù) ·······································145
STEP02　將字符串設(shè)置成條件 ··············································147
STEP03　使用通配符 ·····························································148
03　只求指定部分的和 ·························································150
STEP01　只對(duì)10 萬(wàn)以下的數(shù)據(jù)求和 ·····································150

STEP02　用SUMIF 函數(shù)對(duì)其他范圍求和 ······························152
STEP03　可更改的查找條件 ··················································153
04　函數(shù)大提升 ····································································155
05　練習(xí)題 ···········································································156
問(wèn)題01　根據(jù)男女分別計(jì)算出英語(yǔ)、數(shù)學(xué)、語(yǔ)文的合計(jì)值 ····156
問(wèn)題02　心電感應(yīng)函數(shù) ··························································156
問(wèn)題03　全自動(dòng)！超完美！萬(wàn)年歷 ··········································158
問(wèn)題04　全自動(dòng)！超完美！實(shí)用！可作為普通萬(wàn)年歷來(lái)使用 ····158
函數(shù)篇CHAPTER 06　簡(jiǎn)單學(xué)數(shù)學(xué) 159
01　思考“四舍五入” ·························································160
STEP01　不合理的顯示（一） ··············································160
STEP02　不合理的顯示（二） ··············································161
STEP03　四舍五入 ································································161
02　隨機(jī)數(shù)和模擬 ································································164
STEP01　隨機(jī)數(shù) ····································································164
STEP02　無(wú)條件舍去 ·····························································168
STEP03　擲骰子模擬 ·····························································169
03　π、弧度、三角函數(shù) ·····················································171
STEP01　π ···········································································171
STEP02　弧度與度 ································································172
STEP03　三角函數(shù) ································································174
04　函數(shù)大提升 ····································································176
05　練習(xí)題 ···········································································177
問(wèn)題01　猜拳模擬 ··································································177
問(wèn)題02　錢(qián)幣的各種面額計(jì)算 ················································177
問(wèn)題03　描繪山形 ··································································178
函數(shù)篇CHAPTER 07　函數(shù)活用特輯 179
01　引用其他工作表·····························································180
STEP01　工作表間的引用······················································180
STEP02　立體引用的技巧······················································181
02　公式也可變換成值 ·························································184
STEP01　公式與值 ································································184
STEP02　將公式轉(zhuǎn)換成值······················································185
03　根據(jù)喜好為引用的單元格區(qū)域命名 ································187
STEP01　為引用的單元格區(qū)域命名（一） ····························187

STEP02　利用名稱來(lái)進(jìn)行引用 ··············································190
STEP03　為引用的單元格區(qū)域命名（二） ····························192
04　函數(shù)大提升 ····································································193
05　練習(xí)題 ···········································································194
問(wèn)題01　只以函數(shù)來(lái)顯示星期二上的第5 節(jié)課 ······················194
問(wèn)題02　瞬間輸入“刪除完畢” ············································195
問(wèn)題03　將企業(yè)中文名稱全部改為英文名稱 ··························196
宏 篇CHAPTER 08　宏入門(mén) 197
01　宏的錄制、宏入門(mén) ·························································198
STEP01　什么是宏 ································································198
STEP02　錄制宏的操作步驟 ··················································199
STEP03　宏的執(zhí)行 ································································205
STEP04　宏的保存位置 ·························································207
STEP05　使用宏的準(zhǔn)備工作 ··················································208
STEP06　將1 000 個(gè)數(shù)據(jù)分開(kāi)分布——相對(duì)引用的
宏錄制 ····································································213
02　雖為附加功能卻很厲害的VBE ······································217
STEP01　認(rèn)識(shí)VBE ·······························································217
STEP02　制作宏 ····································································219
STEP03　執(zhí)行宏、錯(cuò)誤提示 ··················································220
STEP04　什么是程序 ·····························································222
STEP05　工作表函數(shù)與VBA 函數(shù) ·········································222
03　制作超酷宏的InputBox ·················································224
STEP01　用InputBox 來(lái)輸入文本 ·········································224
STEP02　姓名與“先生/ 小姐”連接 ····································225
STEP03　代入到變量 ·····························································226
STEP04　以InputBox 來(lái)顯示標(biāo)準(zhǔn)體重 ··································227
STEP05　健康宏 ····································································228
04　按快捷鍵或單擊圖形打開(kāi)宏 ···········································229
STEP01　指定到快捷鍵 ·························································229
STEP02　將宏指定到圖形中 ··················································231
05　練習(xí)題 ···········································································232
問(wèn)題01　立即復(fù)制上方的單元格 ············································232
問(wèn)題02　輸入姓名與金額就會(huì)顯示計(jì)算結(jié)果 ··························233
問(wèn)題03　按快捷鍵就會(huì)將表格中的當(dāng)前記錄整理?yè)Q行后并
打印出來(lái) ·································································234
目錄.indd 5 2016-1-12 16:17:36
CONTENTS
6
宏 篇CHAPTER09　輸入到單元格中 235
01　單純輸入單元格·····························································236
STEP01　在單元格中輸入數(shù)據(jù) ··············································236
STEP02　Range——**的數(shù)據(jù)輸入法 ································237
STEP03　組合Range 與函數(shù) ················································238
STEP04　Range 小技巧 ························································240
02　以x 和y 來(lái)表示單元格位置 ···········································242
STEP01　使用Cells 輸入數(shù)據(jù) ···············································242
STEP02　以For~Next 來(lái)循環(huán)處理 ········································243
STEP03　連續(xù)輸入的技巧（一） ···········································244
STEP04　連續(xù)輸入的技巧（二） ···········································245
03　宏的大提升 ····································································249
04　練習(xí)題 ···········································································250
問(wèn)題01　以2、4、8、16…的方法來(lái)輸入2 的累乘 ···············250
問(wèn)題02　連接所有詞語(yǔ)并顯示在對(duì)話框中 ······························250
問(wèn)題03　制作九九乘法表 ·······················································251
宏 篇CHAPTER 10　了解對(duì)象、屬性和方法 253
01　什么是對(duì)象、屬性 ·························································254
STEP01　什么是對(duì)象 ·····························································254
STEP02　什么是集合 ·····························································255
STEP03　什么是屬性 ·····························································256
STEP04　顯示屬性 ································································258
02　字體大小和顏色·····························································259
STEP01　字體大小 ································································259
STEP02　連續(xù)改變屬性——文字變大/ 變小··························260
STEP03　改變文字的顏色······················································262
STEP04　更改單元格的顏色 ··················································263
STEP05　制作多彩工作表（一） ···········································264
03　方法閃亮登場(chǎng) ································································266
STEP01　什么是方法 ·····························································266
STEP02　執(zhí)行刪除操作 ·························································269
STEP03　什么是方法的參數(shù) ··················································271
STEP04　讓電腦開(kāi)口說(shuō)話······················································272
04　宏的大提升 ····································································276
05　練習(xí)題 ···········································································277
問(wèn)題01　以用戶所選的顏色來(lái)填充單元格 ······························277
目錄.indd 6 2016-1-12 16:17:36
7
CONTENTS
問(wèn)題02　以語(yǔ)音指引來(lái)輸入數(shù)據(jù) ············································278
問(wèn)題03　以動(dòng)畫(huà)的方式隨機(jī)將“好開(kāi)心啊～”變大變小 ········279
宏 篇CHAPTER 11　如果是○○就進(jìn)行×× 281
01　如果是○○就進(jìn)行××——If 篇 ···································282
STEP01　什么是If ～ Then ～ Else ·······································282
STEP02　以單元格的值來(lái)改變處理 ·······································284
STEP03　只輸入空白單元格 ··················································286
02　如果是○○就進(jìn)行××——Select ～ Case 篇 ·············288
STEP01　什么是Select ～ Case ···········································288
STEP02　改變條件的設(shè)置方法 ··············································290
03　宏的大提升 ····································································293
04　練習(xí)題 ···········································································295
問(wèn)題01　如果文字不是白色就變成白色，如果是白色就
　　　　 變成黑色 ··································································295
問(wèn)題02　隨意星座占卜 ··························································295
問(wèn)題03　復(fù)制數(shù)據(jù)到空白單元格中 ········································296
宏 篇CHAPTER 12　可循環(huán)處理的宏 297
01　決定循環(huán)處理的次數(shù) ·····················································298
STEP01　For ～ Next 的詳細(xì)介紹 ··········································298
STEP02　制作多彩工作表（二） ···········································299
02　循環(huán)到符合條件為止 ·····················································301
STEP01　什么是Do ～ Loop Until ·········································301
STEP02　Do Until ～ Loop ···················································303
STEP03　Do While ～ Loop ···················································304
03　For Each ～ Next ··························································307
STEP01　什么是For Each ～ Next ········································307
STEP02　針對(duì)所有的工作表來(lái)執(zhí)行 ·······································307
STEP03　制作多彩工作表（三） ···········································310
STEP04　將所有單元格的值變成原來(lái)的100 倍····················· 311
04　宏的大提升 ····································································313
05　練習(xí)題 ···········································································316
問(wèn)題01　輸入隨機(jī)數(shù) ······························································316
問(wèn)題02　將“旋轉(zhuǎn)~”文字在一定范圍內(nèi)旋轉(zhuǎn)任意角度 ·········316
問(wèn)題03　輸入考生zui在意的偏差值 ········································317
問(wèn)題04　輸入所有的工作表名稱 ············································318
目錄.indd 7 2016-1-12 16:17:36
CONTENTS
8
宏 篇CHAPTER13　不可思議的宏 319
01　什么是事件 ····································································320
STEP01　事件概述 ································································320
STEP02　只要打開(kāi)工作簿就會(huì)執(zhí)行的宏 ································321
STEP03　事件宏的寫(xiě)法 ·························································323
STEP04　在打印時(shí)執(zhí)行的宏 ··················································323
02　通過(guò)工作表操作來(lái)執(zhí)行的宏 ···········································325
STEP01　只要將工作表設(shè)置為當(dāng)前就會(huì)開(kāi)始執(zhí)行的宏 ··········325
STEP02　針對(duì)工作表的事件 ··················································326
STEP03　沒(méi)有密碼就無(wú)法進(jìn)入的工作表 ································327
STEP04　只要更改數(shù)據(jù)就會(huì)執(zhí)行的宏····································329
STEP05　只要雙擊就會(huì)輸入數(shù)據(jù)并填充黃色 ·························331
03　宏的大提升 ····································································333
04　練習(xí)題 ···········································································334
問(wèn)題01　只要更改數(shù)據(jù)就會(huì)將A1 單元格作為當(dāng)前單元格 ·····334
問(wèn)題02　雙擊就會(huì)以原來(lái)的2 倍大小顯示“好痛哦!”文字 ···334
問(wèn)題03　只要單擊就能看見(jiàn) ···················································335
APPENDIX 附錄 337
01　各類(lèi)型函數(shù)速查表 ·························································338
02　練習(xí)題參考答案·····························································345
03　利用Excel 也能完成文件操作 ·······································357
STEP01　快速輸入求和的技巧 ··············································357
STEP02　保存、關(guān)閉工作簿 ··················································357
STEP03　復(fù)制文件、移動(dòng)、更改名稱····································358
STEP04　刪除文件 ································································359
STEP05　查找文件 ································································359
STEP06　操作文件夾 ·····························································359
STEP07　操作文本文件 ·························································360
04　將宏指定到按鈕中 ·························································361
目錄.indd 8 2016-1-12 16:17:36