深入淺出Excel

目　錄

前言
　　你的大腦開始關(guān)注Excel。我們從這里開始學(xué)習(xí)，但此時(shí)你的大腦可能正在扯你的后腿。你的大腦可能會(huì)想“最好把大腦留給更為重要的問題，比如，應(yīng)該避開哪些野生動(dòng)物，裸體單板滑雪是否是個(gè)很瘋狂的舉動(dòng)？”所以，如何才能讓你的大腦覺得，你以后要靠電子表格來謀生呢？
　誰適合閱讀本書？　xxvi
　我們知道你在想什么　xxvii
　元認(rèn)知：關(guān)于思考的思考　xxix
　這樣做就能讓你的大腦動(dòng)起來　xxxi
　自述文件　xxxii
　技術(shù)評(píng)審團(tuán)隊(duì)　xxxiv
　致謝　xxxv

1. 公式初探　Excel真正的力量
　　我們都使用Excel來制作電子表格。
　　在制作電子表格方面，Excel貢獻(xiàn)很大。但真正的Excel高手是那些已經(jīng)熟練掌握了公式技巧的人。熟練操作數(shù)據(jù)是指能執(zhí)行你所需要的計(jì)算以及使用做這些計(jì)算的公式，并將你的數(shù)據(jù)變得有用和清晰。如果你掌握了這些公式技巧，那么你真的可以讓數(shù)據(jù)為你唱歌。
　你能快樂地度過假期的最后一晚嗎？　2
　這是你們的預(yù)算和實(shí)際花銷　3
　用Excel來做記錄是非常棒的　4
　使用公式來處理你的數(shù)據(jù)　5
　即使你的數(shù)據(jù)有所變化，“引用”也可以使你的公式正常工作　11
　仔細(xì)檢查你的公式　14
　使用單元格區(qū)域引用一列單元格　15
　使用SUM將單元格區(qū)域內(nèi)的值相加　15
　當(dāng)你復(fù)制/粘貼一個(gè)公式時(shí)，引用也會(huì)改變　21
　Excel公式讓你更深入地掌控你的數(shù)據(jù)　26
　每個(gè)人都還剩下充足的現(xiàn)金，可以在紐約享受一頓美味的晚餐了！　27

2. 視覺設(shè)計(jì)　美化電子表格
　　大部分人通常使用Excel來進(jìn)行頁面布局。
　　很多熟悉Excel的編程高手都驚訝于人們“只”使用該軟件的網(wǎng)格來顯示信息。但Excel，特別是最新的版本，其作為頁面布局工具非常得心應(yīng)手。使用一些重要的，并且不太明顯的Excel工具來做一些復(fù)雜的視圖是非常方便的。
　CRMFreak需要向分析師提交財(cái)務(wù)報(bào)表　30
　美元符號(hào)是單元格格式的一部分　35
　如何為你的數(shù)據(jù)選擇格式　36
　老板批準(zhǔn)了！　39
　設(shè)計(jì)原則：簡(jiǎn)潔　40
　設(shè)計(jì)高手們的爭(zhēng)論……　41
　使用字體來標(biāo)明什么是最重要的　42
　單元格樣式使得重復(fù)的要素保持一致的格式　46
　選擇要使用的單元格樣式后，繼續(xù)使用主題來改變電子表格外觀　47
　他很滿意，但還有一些其他問題……　50
　為類似的一些組使用接近和對(duì)齊　53
　你的表格引起了轟動(dòng)！　57

3. 引用　指引正確的方向
　　一個(gè)公式應(yīng)該和它的引用一樣準(zhǔn)確。
　　無論你的公式多么有創(chuàng)意多么精彩，假如它沒有指向正確的數(shù)據(jù)，那它將沒有任何意義。為短的單個(gè)公式寫引用是很容易的，但一旦這些公式變長并且需要被復(fù)制，引用出錯(cuò)的概率將會(huì)大大增加。在本章中，你將學(xué)習(xí)絕對(duì)引用和相對(duì)引用，以及Excel先進(jìn)的結(jié)構(gòu)化引用特性，以確保無論你的引用多大多復(fù)雜，你的公式都可以保持緊湊和準(zhǔn)確。
　MIN返回一系列數(shù)字中最小的數(shù)字　64
　運(yùn)行你的公式并使用你的鼠標(biāo)讓Excel來自動(dòng)填充這些列　67
　Excel使用一個(gè)更復(fù)雜的引用計(jì)算出了正確的答案　68
　使用絕對(duì)引用來避免復(fù)制/粘貼時(shí)的變化　73
　絕對(duì)引用給了你很多選擇　75
　命名區(qū)域簡(jiǎn)化了你的公式　76
　Excel的表格(Table)功能讓你的引用變得快速簡(jiǎn)單　82
　結(jié)構(gòu)化引用是絕對(duì)引用的一種不同形式　83
　你對(duì)利潤率的預(yù)測(cè)被證明是準(zhǔn)確的　88

4. 不同準(zhǔn)度查看數(shù)據(jù)　排序、縮放和篩選
　　你的數(shù)據(jù)看上去很有誘惑力。
　　只有你懂得數(shù)據(jù)的含義，你才能發(fā)現(xiàn)它們的魅力。在這章中，你將會(huì)忘記格式和函數(shù)，而只是關(guān)注你將如何改變對(duì)數(shù)據(jù)的看法。當(dāng)你處理你的數(shù)據(jù)時(shí)，尋找需要解決的問題，排序、放大和篩選工具將為你提供許多驚喜的功能來幫助你了解數(shù)據(jù)的實(shí)際含義。
　競(jìng)選顧問需要幫助——整理他們的籌款數(shù)據(jù)庫　90
　尋找大筆金額捐款人的姓名　91
　排序可以改變數(shù)據(jù)的順序　92
　排序?yàn)槟阏故玖瞬煌嵌鹊拇髷?shù)據(jù)集　95
　利用放大功能可以看到更多的數(shù)據(jù)　103
　你的客戶對(duì)此印象深刻！　106
　篩選功能可以隱藏那些你不想看到的數(shù)據(jù)　107
　使用篩選下拉框來選擇如何篩選你的數(shù)據(jù)　108
　競(jìng)選團(tuán)隊(duì)提出了一個(gè)意料之外的要求……　109
　競(jìng)選團(tuán)隊(duì)對(duì)你的工作非常滿意　112
　不斷有捐款資金注入　115

5. 數(shù)據(jù)類型　讓Excel正確地表示數(shù)值
　　Excel并不是總能表現(xiàn)出它所想的。
　　有時(shí)，Excel向你展現(xiàn)了一個(gè)數(shù)字，而實(shí)際上Excel卻認(rèn)為其是文本?；蛘咚蚰阏宫F(xiàn)了一些文本，而實(shí)際上它卻視其為數(shù)字。Excel甚至?xí)蚰阏故疽恍┘炔皇菙?shù)字也不是文本的數(shù)據(jù)！本章你將學(xué)習(xí)到，無論數(shù)據(jù)看起來是什么格式，你都必須按照Excel的方式來看數(shù)據(jù)。這方面的知識(shí)不僅能幫助你更好地掌控你的數(shù)據(jù)（少發(fā)生一點(diǎn)“#$%!是怎么了？”的情況），而且可以幫助你了解公式真正的含義。
　你的醫(yī)生朋友在最后一刻毀掉了他的數(shù)據(jù)　118
　不知為何，你的平均值公式的分母為0　121
　Excel中的數(shù)據(jù)既可以是數(shù)字也可以是文本　122
　醫(yī)生以前也遇到過類似的問題　125
　你需要一個(gè)Excel將你的文本認(rèn)為是數(shù)字的函數(shù)　126
　研究生也會(huì)做一些統(tǒng)計(jì)……但是出現(xiàn)了一個(gè)問題　132　
　錯(cuò)誤是一種特殊的數(shù)據(jù)類型　135
　現(xiàn)在你的名字被當(dāng)成科學(xué)家的名字印在了書上　140

6. 日期和時(shí)間　保持準(zhǔn)時(shí)
　　Excel里的日期和時(shí)間很難表示。
　　除非你了解Excel的內(nèi)部是如何表示它們的。我們都會(huì)涉及到這些類型數(shù)字的計(jì)算，這一章會(huì)教你如何計(jì)算兩個(gè)日期間的天、月、年，甚至秒的方法。簡(jiǎn)單來說，日期和時(shí)間的確是一種特別的數(shù)據(jù)類型和格式。一旦你掌握了基本理論，你就可以使用Excel完美地來做時(shí)間調(diào)度了。
　你有時(shí)間來參加訓(xùn)練嗎？為了參加馬薩諸塞州的馬拉松比賽　142
　VALUE()返回了一個(gè)數(shù)字，該數(shù)字是日期，卻保存為文本格式　146
　Excel將日期認(rèn)成了整數(shù)　147
　將這兩個(gè)日期相減，得出的是這兩個(gè)日期間相隔的天數(shù)　148
　當(dāng)日期相減時(shí)，注意觀察你的格式　152
　看上去你在第一個(gè)一萬公里長跑比賽前沒有足夠的時(shí)間完成訓(xùn)練　153
　教練有一個(gè)更好的辦法　154
　DATEDIF()用來計(jì)算兩個(gè)日期間的間隔時(shí)間　156
　教練很高興你能加入她的課程　161
　Excel將時(shí)間用0至1之間的十進(jìn)制小數(shù)來表示　162
　教練為你制定了一個(gè)Excel挑戰(zhàn)課程　165
　你已經(jīng)具有馬薩諸塞州馬拉松賽的資格了　167

7. 發(fā)掘函數(shù)　獨(dú)立探索Excel的特性
　　Excel有很多函數(shù)，絕對(duì)超出了你的需要。
　　經(jīng)過多年的發(fā)展和版本升級(jí)，Excel已經(jīng)積累了大量函數(shù)，對(duì)于使用這些函數(shù)的人來說，這些函數(shù)是不可或缺的。Excel是否有足夠多的函數(shù)絕對(duì)不是問題，問題是你不知道那些對(duì)你非常有用的函數(shù)。我們所說的是哪些函數(shù)呢？當(dāng)然只有你自己知道。下面，你將可以學(xué)到一些如何尋找所需公式的技巧，以幫助你高效地工作。
　你應(yīng)該租一個(gè)額外的停車位嗎？　170
　你需要一個(gè)計(jì)劃來了解更多函數(shù)　173
　Excel幫助頁面中有很多提示和技巧　174
　這是會(huì)展中心下個(gè)月售票的數(shù)據(jù)庫。　178
　函數(shù)參考解析　183
　Dataville會(huì)展中心的COO出現(xiàn)了……　185
　函數(shù)是按照數(shù)據(jù)類型和規(guī)程組織的　186
　你的電子表格顯示了每個(gè)日期的總售票數(shù)量　192
　免費(fèi)的包廂票！　195

8. 公式審核　讓公式運(yùn)行變得直觀
　　Excel的公式有時(shí)候非常復(fù)雜。
　　一針見血，不是嗎？如果你所要做的只是簡(jiǎn)單的計(jì)算，那么一張紙、一支筆和一個(gè)計(jì)算器就足夠了。然而，這些復(fù)雜的公式有時(shí)候會(huì)很麻煩——特別是其他人編寫的公式，如果你不了解編寫者的想法就無法理解這些公式。本章中，你將學(xué)習(xí)使用Excel中一個(gè)簡(jiǎn)單但是很有用的圖形化的功能，叫做公式審核，它能生動(dòng)地展示你電子表格模型中的數(shù)據(jù)流。
　你應(yīng)該買房還是租房呢？　198
　用凈現(xiàn)值將未來的成本折算成今天的價(jià)值　202
　經(jīng)紀(jì)人給你做了一個(gè)電子表格　205
　Excel中的模型可以很復(fù)雜　206
　公式審核顯示了公式中參數(shù)的位置　208
　Excel的貸款函數(shù)采用相同的基本元素　212
　經(jīng)紀(jì)人電子表格中的PMT公式可以計(jì)算出你每月的付款金額　213
　公式必須正確，而且假設(shè)必須合理　218
　經(jīng)紀(jì)人在衡量……　222
　房產(chǎn)絕對(duì)是個(gè)好投資！　225

9. 圖表　將數(shù)據(jù)做成圖表
　　誰想一直盯著枯燥的數(shù)字？
　　通常，一張整潔的圖表是更吸引人的數(shù)據(jù)展示方法。有時(shí)候，你的數(shù)據(jù)太多了，以至于沒有圖表根本無法將它們?nèi)靠辞宄xcel有龐大的圖表工具，只要你知道鼠標(biāo)該往哪兒點(diǎn)，它就能釋放魔力，生動(dòng)而流暢地用圖表來展示你的數(shù)據(jù)吧。
　Head First投資公司需要其投資報(bào)告的圖表　228
　使用插入(Insert)選項(xiàng)卡創(chuàng)建圖表　231
　使用設(shè)計(jì)(Design)和布局(Layout)選項(xiàng)卡來重做你的圖表　232
　你的餅圖沒得到公司圖形藝術(shù)家的認(rèn)可　236
　你的時(shí)間不多了……　247
　你的報(bào)表非常成功……　249

10. 假設(shè)分析　其他可能性
　　事情可能會(huì)向不同的方向發(fā)展。
　　有很多定量因素可能會(huì)影響你的業(yè)務(wù)發(fā)展、經(jīng)濟(jì)周轉(zhuǎn)、日程安排等。Excel可以出色地幫助你將設(shè)想變成模型，并評(píng)估這些因素的變化將如何影響你最關(guān)心的變量。本章你將學(xué)習(xí)3 個(gè)主要的功能——方案、單變量求解和規(guī)劃求解，它們的目的是讓你對(duì)“假設(shè)”問題的分析易如反掌。
　你的朋友貝蒂是否應(yīng)該打廣告呢？　252
　貝蒂對(duì)不同的廣告配置已經(jīng)做了最好和最壞的預(yù)測(cè)　255
　方案幫助你了解同一模型中不同輸入數(shù)據(jù)所得的結(jié)果　258
　方案顯示變化的元素在不同配置情況下的結(jié)果　259
　貝蒂想知道自己的盈虧平衡點(diǎn)在哪里　261
　通過嘗試不同的候選值，使用單變量求解出所需的數(shù)值　262
　貝蒂想讓你的模型更精細(xì)　266
　規(guī)劃求解可以處理更加復(fù)雜的優(yōu)化問題　267
　對(duì)你的規(guī)劃求解模型進(jìn)行一次檢查　272
　使用規(guī)劃求解計(jì)算你的預(yù)測(cè)　276
　貝蒂的最佳方案通過了……　277

11. 文本函數(shù)　文字也可以作為數(shù)據(jù)
　　Excel熱愛數(shù)字，但它同時(shí)也能處理你的文本。
　　Excel有很多種函數(shù)幫助你處理文本數(shù)據(jù)。這些函數(shù)可以用于很多種場(chǎng)合，但是有一種場(chǎng)合卻不適用，那就是人們不得不處理亂糟糟的數(shù)據(jù)時(shí)。很多情況下，你接收到的數(shù)據(jù)并不是你想要的格式——例如，來自陌生數(shù)據(jù)庫的數(shù)據(jù)。文本函數(shù)擅長從雜亂無章的數(shù)據(jù)中提取要點(diǎn)，這樣你便可加以分析使用。接下來你會(huì)得到更多信息……
　你的客戶數(shù)據(jù)庫崩潰了！　280
　這是數(shù)據(jù)　281
　分列(Text to Columns)采用分隔符拆分?jǐn)?shù)據(jù)　282
　分列并非所向無敵　285
　Excel有一整套函數(shù)用于處理文本　286
　LEFT和RIGHT是提取文本的基本函數(shù)　289
　你需要改變公式第二個(gè)參數(shù)的值　291
　客戶數(shù)據(jù)缺失，業(yè)務(wù)開始受到影響　293
　電子表格變得越來越龐大！　297
　FIND返回一個(gè)數(shù)字，該數(shù)字確定文本的位置　298
　分列只看公式，不看結(jié)果　302
　選擇性粘貼(Paste Special)為你提供多種粘貼選擇　302
　看起來時(shí)間不多了……　305
　數(shù)據(jù)危機(jī)解除！　308

12. 數(shù)據(jù)透視表　強(qiáng)大的分組能力
　　數(shù)據(jù)透視表是Excel最為強(qiáng)大的功能之一。
　　數(shù)據(jù)透視表是什么？我們?yōu)槭裁匆P(guān)注它？對(duì)Excel新手而言，數(shù)據(jù)透視表也可能是Excel中最讓人頭疼的功能之一。但是數(shù)據(jù)透視表的目的非常簡(jiǎn)單：將數(shù)據(jù)快速分組，這樣你就能對(duì)其進(jìn)行分析了。正如下文中你即將了解到的，利用數(shù)據(jù)透視表對(duì)數(shù)據(jù)進(jìn)行分組和匯總要比單純使用公式進(jìn)行分組快多了。當(dāng)你閱讀完本章，你就能以前所未有的速度對(duì)數(shù)據(jù)進(jìn)行切分了。
　Head First 汽車周刊(HFAW)需要對(duì)年度汽車資訊進(jìn)行分析　310
　你不得不進(jìn)行大量的重復(fù)操作　313
　數(shù)據(jù)透視表無疑是匯總數(shù)據(jù)最強(qiáng)大的工具　314
　構(gòu)建數(shù)據(jù)透視表結(jié)構(gòu)其實(shí)就是預(yù)覽你的字段的走向　316
　使用數(shù)據(jù)透視表匯總數(shù)據(jù)要比公式更快　320
　你的編輯真是太有才了！　322
　你就要完成雜志的數(shù)據(jù)表格了　326
　你的數(shù)據(jù)透視表非常受歡迎！　330

13. 布爾表達(dá)式　TRUE和FALSE
　　Excel中有一種看起來似乎極其簡(jiǎn)單的數(shù)據(jù)類型。
　　它們就是布爾表達(dá)式，即簡(jiǎn)單的TRUE和FALSE 。你可能覺得它們太簡(jiǎn)單太原始了，在復(fù)雜數(shù)據(jù)分析中沒什么用，但事實(shí)卻并非如此。在這一章中，你將把布爾表達(dá)式插入到邏輯公式中來完成各種任務(wù)，從整理數(shù)據(jù)到生成全新的數(shù)據(jù)點(diǎn)。
　在Dataville湖捕魚的漁夫都遵守規(guī)定嗎？　332
　你手上有每條船的允許捕撈量　333
　布爾表達(dá)式返回TRUE或FALSE的結(jié)果　334
　IF根據(jù)布爾表達(dá)式的條件給出結(jié)果　334
　你的IF公式需要結(jié)合整個(gè)命名系統(tǒng)　336
　統(tǒng)計(jì)一下每種類型的船各有多少只　343
　COUNTIFS和COUNTIF差不多，但是前者要強(qiáng)大得多　346
　遇到復(fù)雜情況的時(shí)候，將公式分別應(yīng)用到各列　350
　魚類申張了正義！　356

14. 數(shù)據(jù)分割　切分
　　創(chuàng)造性地運(yùn)用你的工具。
　　在前面的13章中，你已經(jīng)掌握了許多Excel的知識(shí)，現(xiàn)在你已經(jīng)知道(或者知道如何找到)可以解決數(shù)據(jù)問題的大多數(shù)工具。但是，如果你的問題不適合使用這些工具呢？如果你所需的數(shù)據(jù)并不在一個(gè)地方，或者你的數(shù)據(jù)被分成了不同類別，且不適用你的分析目標(biāo)？在最后一章，你將使用查找函數(shù)和一些已知的數(shù)據(jù)分割工具，創(chuàng)造性地使用Excel的工具。
　你現(xiàn)在為一個(gè)監(jiān)察機(jī)構(gòu)工作，他們需要記錄預(yù)算情況　358
　這是他們想要的圖表　359
　這是按郡劃分的聯(lián)邦政府支出數(shù)據(jù)　360
　有時(shí)候你得到的數(shù)據(jù)不夠用　363
　區(qū)域的問題更加難以解決　365
　這是一個(gè)查找關(guān)鍵字　366
　VLOOKUP 將交叉引用兩個(gè)數(shù)據(jù)源　367
　創(chuàng)建數(shù)據(jù)分段，將正確的數(shù)據(jù)加入你的分析　374
　地緣政治協(xié)會(huì)想要再做一點(diǎn)細(xì)微的調(diào)整　376
　你讓地緣政治協(xié)會(huì)能夠追蹤到錢的流向……　380
　離開小鎮(zhèn)……　381
　和你在Dataville的時(shí)光太美好了！　381

附錄i. 其他　最熱門的十大功能(我們沒有涵蓋到)
　　你已經(jīng)學(xué)了很多內(nèi)容了。
　　但是Excel是一款復(fù)雜的程序，還有許多內(nèi)容沒學(xué)呢！在附錄里，我們將討論本書由于篇幅限制而沒有涵蓋的10項(xiàng)內(nèi)容，但是這10項(xiàng)內(nèi)容應(yīng)該是你接下來學(xué)習(xí)的重點(diǎn)。
　1. 數(shù)據(jù)分析　384
　2. 格式刷(Format Painter)　385
　3. 數(shù)據(jù)分析工具庫　386
　4. 數(shù)組公式　386
　5. 圖形和SmartArt　387
　6. 控制再計(jì)算和優(yōu)化性能　388
　7. 連接到網(wǎng)絡(luò)　389
　8. 使用外部數(shù)據(jù)源　389
　9. 協(xié)作　　390
　10. 應(yīng)用程序可視化基礎(chǔ)(VBA)　390

附錄ii. 在Excel中安裝規(guī)劃求解　規(guī)劃求解
　　默認(rèn)狀態(tài)下，有些好用的功能并沒有直接安裝在Excel中。
　　沒錯(cuò)，為了運(yùn)行第10章中的優(yōu)化程序，你需要激活規(guī)劃求解(Solver)，它是Excel 默認(rèn)自帶的加載項(xiàng)，但你必須先激活它。
　在Excel中安裝規(guī)劃求解　392