領悟專家技巧，釋放Excel潛能 如果只是使用Excel來創建簡單的表格和圖表，現在是時候深挖這個軟件的真正功能了：公式。本書是一本理想的指南，介紹了公式的方方面面，向讀者展示了Excel的強大功能。 利用本書的提示和技巧，讀者的工作流程將變得更快、更輕松、更精確。本書介紹的專家技巧是在別的地方找不到的。無論是初學者還是高級用戶，這本書都是一本重要參考，可帶領讀者深入Excel，*程度地利用Excel 2016版本的功能。
本書能夠為你提供幫助。我們將帶你進入Excel公式的世界，詳細介紹Excel的各種內置函數。每章都以前一章為基礎，帶領你從學習基本的數學函數，一直到能夠實際創建自定義的函數。
公式是Excel真正的動力引擎，這一點無可爭議。通過運用Excel中的各種函數，公式讓Excel分析人員能夠創建聚合報表、復雜的計算引擎、巧妙的儀表板模型等。實際上，隨著對Excel函數和公式熟悉程度的增加，Excel分析人員的效率會越來越高。但是，增加對Excel函數和公式的熟悉程度是需要時間的。Excel中包含超過400個函數，可能需要用幾個月甚至幾年的時間不斷學習，才能知道哪些函數最適合特定任務，哪些函數可以與其他函數結合使用。本書能夠為你提供幫助。我們將帶你進入Excel公式的世界，詳細介紹Excel的各種內置函數。每章都以前一章為基礎，帶領你從學習基本的數學函數，一直到能夠實際創建自定義的函數。你會發現，運用Excel函數不僅能夠提高效率，還能夠完成一些你原本不知道能用Excel處理的任務。本書讀者對象書中涉及的層面較深，適合有一定Excel基礎的用戶使用。如果讀者沒有任何使用Excel的經驗，那么本書可能不是很適合你，除非你是一個可以在極短的時間內學會一款新軟件的天才。如果希望掌握本書的大部分內容，讀者必須對Excel的使用具有一定的背景。本書假定讀者已掌握以下內容：● 創建工作簿、插入工作表、保存文件和其他基本操作● 在各個工作簿之間瀏覽● 會使用Excel功能區和對話框● 會使用Windows基本功能，例如文件管理、復制和粘貼操作等使用本書的條件本書是為Excel 2016編寫的，但其中大部分內容也適用于Excel 2007-Excel 2013。如果讀者使用的是Excel 2016之前的版本，會發現無法使用一些函數(例如，預測函數是在Excel 2016中新增的)。每章中會指出那些無法在其他版本中使用的函數。如果讀者使用的是Excel 2007之前的版本，那么需要放下這本書，選擇本書的早期版本了。Excel 2007是一個分水嶺，之前和之后版本的差別非常大。如果用Excel 2007之前的版本對著本書學習，會被本書的內容弄糊涂。要下載本書的示例，需要能夠訪問Internet。稍后的本書示例文件下載部分將更詳細地說明這一點。注意 本書的示例是在Windows平臺上創建的。對于Mac用戶，我們不能保證本書的示例都能用于Mac電腦上的Excel。雖然Excel的跨平臺兼容性很好，但還沒有做到完全兼容。
對于硬件來說，當然是運行速度越快越好，同時，系統的內存越大，使用起來就越方便。本書約定下面，我們將花點時間來介紹本書采用的排版上的約定。鍵盤約定公式是由鍵盤輸入的。另外，菜單和對話框也可以用鍵盤操作，特別是如果手正好在這些操作鍵上時就顯得更為方便。公式書中的公式通常用等寬字體以單獨一行顯示。例如，下面就顯示了一個公式：=VLOOKUP(StockNumber,PriceList,2,False)Excel支持特殊的公式形式，即數組公式。輸入數組公式時，需要按下Ctrl Shift Enter組合鍵(不能只按Enter鍵)。Excel會自動為數組公式添加括號，表示這是一個數組公式。本書中在列出數組公式時，會使用括號來清楚地顯示這實際上是一個數組公式，如下所示：{=SUM(LEN(A1:A10))}注意 不必專門輸入數組公式的括號，Excel會自動添加。VBA代碼本書還包含許多VBA代碼示例。這些代碼都使用等寬字體，且每行代碼占據單獨一行。為了便于代碼的閱讀，通常會使用Tab來縮排代碼。雖然縮進不是必需的，但這有助于將一組語句對齊。如果實際中的一行代碼在本書中用一行放不下，本書就會使用標準的VBA續行符：一個空格后跟下劃線，這表示該行代碼將延續到下一行。例如，下面兩行代碼構成一條VBA語句：If Right(cell.Value, 1) = "!" Then cell.Value _ =Left(cell.Value, Len(cell.Value) 1)在實際使用中，可以用這種方式分兩行輸入代碼，也可以不使用下劃線直接將代碼寫在同一行中。快捷鍵快捷鍵要用到一些功能鍵，例如Alt、Home、PgDn和Ctrl。本書會用加號連接兩個鍵來表示這兩個按鍵需要同時按下，例如，Ctrl G表明要同時按下Ctrl和G鍵來打開定位對話框。函數、過程和命名范圍Excel的工作表函數均使用大寫字母來表示，例如使用SUM函數累加A列中的值。而宏和過程名會使用一般的表示方法，例如執行InsertTotals過程，書中使用大小寫混用的方式，以方便閱讀。命名范圍使用斜體表示，如選擇InputArea范圍。除了引號內的文本，一般情況下Excel不區分大小寫。換言之，下面兩個公式的結果相同：=SUM(A1:A50)=sum(a1:a50)不過Excel會自動把第二個公式中的字符轉換為大寫。鼠標約定關于鼠標，本書中使用的都是標準術語：指向、單擊、右擊、拖動等。從字面就知道是如何操作的。圖標含義在本書中，下面這些圖標用來強調某些重要的內容。注意 注意圖標用來表示這些內容很重要，這些內容會對手頭的任務，或對后續內容的理解產生至關重要的影響提示 提示圖標用來表示某些高效的操作方式或一些隱晦的技術。掌握這些提示會令人刮目相看。在線資源 這個圖標表示示例文件位于本書的網站中(參見稍后的本書示例文件下載)。警告 警告圖標用于警示那些不注意就會導致問題的操作。交叉參考 交叉參考圖標用于告訴讀者其他章節對這個主題也有所介紹。本書的組織結構本書分為7大部分，每部分關注Excel函數的一個關鍵方面。第Ⅰ部分：基礎知識這部分介紹基本的操作和功能(第1章～第3章)。第1章簡要介紹Excel的功能，主要是為使用過其他電子表格產品的Excel新用戶準備的。第2章介紹Excel公式的基礎知識，如果讀者想對本書有一個基本了解，那么本章是必讀的。第3章介紹名稱，如果以為名稱就是單元格和范圍，就過于片面了。第Ⅱ部分：在公式中使用函數這部分包含第4章～第10章。第4章介紹在公式中使用工作表函數的基礎知識，后面的章節還會更詳細地介紹這些知識；第5章介紹文本操作；第6章介紹日期和時間；第7章探討各種計數方法；第8章討論幾種不同形式的查找公式；第9章闡述表格和工作表數據庫的內容；第10章涵蓋一些雜項計算方法，例如單位轉換和舍入等。第Ⅲ部分：財務公式這部分含有3章(第11章～第13章)，探討創建財務公式的問題。這部分介紹許多能滿足實際需要的有用的公式。第Ⅳ部分：數組公式這部分含有第14章和第15章。大多數Excel用戶對數組公式知之甚少，但數組公式是非常有用的。因此，這里單獨用了一個部分來介紹此功能強大的特性。第Ⅴ部分：其他公式這部分包括第16章～第22章。這一部分涵蓋了許多主題，其中也介紹了一部分從表面上看與公式沒有關聯但卻很重要的內容。第16章介紹許多有關數據清潔的有用信息；第17章介紹使用圖表時公式的重要性；第18章介紹與數據透視表相關的公式；第19章包含一些非常有趣且有用的公式，這些公式可與Excel的條件格式特性聯合使用；第20章介紹數據驗證這個特性；第21章介紹大公式，所謂大公式，就是很大的公式，這個公式代替了多個中間公式。如果公式不正確，該怎么辦？這時可以閱讀第22章介紹的調試技術。第Ⅵ部分：開發自定義工作表函數這部分包括第23章～第26章，主要介紹VBA，以及創建自定義工作表函數的相關知識。第23章介紹VBA和VB編輯器；第24章介紹一些編程概念；第25章介紹自定義工作表函數相關的背景知識；第26章提供很多自定義工作表函數的示例，讀者可以照原樣使用，也可以根據自己的需要做些改動。第Ⅶ部分：附錄本書包含兩個附錄。附錄A是一份Excel工作表函數速查表，附錄B是一些自定義數字格式的技巧。本書示例文件下載本書含有許多例子，可在下面的這個鏈接中下載含有這些例子的工作簿：www.wiley.com/go/excel2016formulas擴展名為*.xlsm的文件包含VBA宏。為了使用其中的宏，打開文件時必須啟用宏(也可以將文件放到受信任位置)。
Michael Alexander擁有MCAD(微軟認證應用開發專家)認證資格，有超過15年的Microsoft Office解決方案咨詢與開發經驗。Michael還是一名Microsoft MVP，他運營著一個免費課程網站www.datapigtechnologies.com。 Dick Kusleika已連續12年榮膺Microsoft MVP稱號，擁有逾20年的Microsoft Office使用經驗，為客戶開發基于Access和Excel的解決方案。Dick撰寫了一個有關Excel的熱門博客www.dailydoseofexcel.com。
第Ⅰ部分 基礎知識
第1章 Excel用戶界面概述 3
1.1
Excel的工作方式 3
1.1.1
工作表 4
1.1.2
圖表工作表 5
1.1.3
宏表和對話表 5
1.2
Excel用戶界面 5
1.2.1
功能區 5
1.2.2
后臺視圖 7
1.2.3
快捷菜單和浮動工具欄 7
1.2.4
對話框 7
1.2.5
自定義用戶界面 7
1.2.6
任務窗格 8
1.2.7
自定義屏幕顯示 8
1.2.8
數字格式化 8
1.2.9
樣式格式化 9
1.3 保護選項 9
1.3.1 保護整個工作簿的訪問 9
1.3.2
限制對特定工作表范圍的
訪問 12
1.3.3
保護工作簿結構 15
第2章 公式基本內容 17
2.1 輸入和編輯公式 17
2.1.1
公式元素 17
2.1.2
輸入公式 18
2.1.3
粘貼名稱 19
2.1.4
空格和空行 20
2.1.5
公式的限制 20
2.1.6
公式示例 20
2.1.7
編輯公式 21
2.2 在公式中使用運算符 22
2.2.1
引用運算符 23
2.2.2
運算符的優先級 24
2.3 計算公式 26
2.4 單元格和范圍引用 27
2.4.1
創建絕對引用或混合引用 27
2.4.2
引用其他工作表或工作簿 29
2.5 復制或移動公式 30
2.6 準確地復制公式 31
2.7 把公式轉換成值 32
2.8 隱藏公式 34
2.9 公式中的錯誤 35
2.10
處理循環引用 35
2.11
單變量求解 37
2.11.1
單變量求解示例 37
2.11.2
更多有關單變量求解的內容 38
第3章 使用名稱 39
3.1 名稱的定義 39
3.2 名稱的作用域 40
3.2.1
引用名稱 41
3.2.2
引用其他工作簿的名稱 41
3.2.3
名稱沖突 42
3.3 名稱管理器 42
3.3.1
創建名稱 43
3.3.2
編輯名稱 43
3.3.3
刪除名稱 43
3.4 創建單元格和范圍名稱的
快捷方法 44
3.4.1
新建名稱對話框 44
3.4.2
使用名稱框創建名稱 45
3.4.3
從單元格中的文本創建名稱 46
3.4.4
命名整個行和列 46
3.4.5
Excel創建的名稱 47
3.5 創建多表名稱 48
3.6 使用范圍和單元格名稱 49
3.6.1
創建一個名稱列表 49
3.6.2
在公式中使用名稱 50
3.6.3
對名稱使用的交叉運算符 51
3.6.4
使用帶名稱的范圍運算符 52
3.6.5
引用多單元格命名范圍中的
單個單元格 53
3.6.6
在現有公式中使用名稱 53
3.6.7
創建公式時自動應用名稱 54
3.6.8 取消名稱的應用 54
3.6.9
有錯誤的名稱 55
3.6.10
查看命名范圍 55
3.6.11
在圖表中使用名稱 55
3.7
Excel維護單元格和
范圍名稱的方式 56
3.7.1
插入行或列 56
3.7.2
刪除行或列 56
3.7.3
剪切和粘貼 56
3.8 名稱中的潛在問題 56
3.8.1
復制工作表時的名稱問題 57
3.8.2
刪除工作表時的名稱問題 57
3.9 理解名稱的奧秘 58
3.9.1
命名常量 59
3.9.2
命名文本常量 59
3.9.3
在命名公式中使用
工作表函數 60
3.9.4
在命名公式中使用單元格和
范圍引用 61
3.9.5
使用包含相對引用的
命名公式 62
3.10
使用名稱的高級技術 64
3.10.1
使用INDIRECT函數
處理命名范圍 64
3.10.2
在命名公式中使用數組 66
3.10.3
創建一個動態的命名公式 67
3.10.4
在命名公式中使用XLM宏 68
第Ⅱ部分 在公式中使用函數
第4章 工作表函數入門 73
4.1 什么是函數 73
4.1.1
簡化公式 73
4.1.2
執行其他方法無法實現的計算 74
4.1.3
提高編輯任務的速度 74
4.1.4
實現判斷功能 74
4.1.5
其他函數 75
4.2 函數參數類型 75
4.2.1
將名稱用作參數 76
4.2.2
把整行或整列作為參數 76
4.2.3
把字面值作為參數 77
4.2.4
把表達式作為參數 77
4.2.5
把其他函數作為參數 77
4.2.6
把數組作為參數 78
4.3 在公式中輸入函數的方法
78
4.3.1
手工輸入函數 78
4.3.2
使用函數庫命令 79
4.3.3
使用插入函數對話框
輸入函數 80
4.3.4
輸入函數的其他技巧 82
第5章 處理文本 85
5.1 文本的概念 85
5.1.1
一個單元格可以容納的
字符數 85
5.1.2
把數字作為文本 85
5.2 文本函數 87
5.2.1
確定單元格中是否包含文本 87
5.2.2
使用字符代碼 88
5.2.3
確定兩個字符串是否相同 90
5.2.4
連接兩個或多個單元格 90
5.2.5
把格式化的值顯示成文本 91
5.2.6
把格式化的貨幣值顯示為
文本 93
5.2.7
刪除額外的空格和非打印字符 93
5.2.8
計算字符串中的字符個數 93
5.2.9
重復字符或字符串 94
5.2.10
創建文本直方圖 94
5.2.11
填充數字 95
5.2.12
改變文本的大小寫 96
5.2.13
從字符串中提取字符 97
5.2.14
用其他文本替換文本 97
5.2.15
在字符串中查找和搜索 98
5.2.16
在字符串中進行查找和替換 99
5.3 高級文本公式 99
5.3.1
計算單元格中指定字符的
個數 99
5.3.2
計算單元格中指定子串的
個數 100
5.3.3
刪除尾部的減號 100
5.3.4
把數字表示成序數詞 100
5.3.5
為列數確定列字母 101
5.3.6
從具體路徑中提取文件名 101
5.3.7
提取字符串的第一個詞 102
5.3.8
提取字符串的最后一個單詞 102
5.3.9
提取字符串中除第一個詞
以外的所有詞 102
5.3.10
提取名字的名、中間
名和姓 103
5.3.11
刪除姓名中的稱謂 104
5.3.12
計算單元格中詞的數量 105
第6章 處理日期和時間 107
6.1
Excel如何處理日期和時間 107
6.1.1
了解日期序列號 107
6.1.2
輸入日期 108
6.1.3
理解時間序列號 110
6.1.4
輸入時間 111
6.1.5
日期和時間的格式化 112
6.1.6
有關日期的問題 114
6.2 日期函數 115
6.2.1
顯示當前日期 116
6.2.2
使用函數顯示任意日期 117
6.2.3
生成一系列日期 117
6.2.4
把非日期字符串轉換為日期 119
6.2.5
計算兩個日期之間的天數 119
6.2.6
計算兩日期之間的工作日數 120
6.2.7
計算指定工作日天數的日期 121
6.2.8
計算兩日期之間的年數 121
6.2.9
計算人的年齡 122
6.2.10
確定在某年中的天數 123
6.2.11
確定某天是星期幾 123
6.2.12
確定在一年中的周數 124
6.2.13
確定最近星期日的日期 124
6.2.14
確定某個日期后面的第一個
星期的某一天的日期 124
6.2.15
確定某月中第n個星期的
某一天的日期 125
6.2.16
計算星期日期的出現次數 125
6.2.17
把日期表示為序數 126
6.2.18
計算節假日期 127
6.2.19
確定某月的最后一天 129
6.2.20
確定某年是否是閏年 129
6.2.21
確定某個日期的季度 130
6.2.22
把年份轉換成羅馬數字 130
6.3 時間函數 130
6.3.1
顯示當前時間 131
6.3.2
使用函數顯示任何時間 131
6.3.3
計算兩個時間的差 132
6.3.4
合計超過24小時的時間 133
6.3.5
轉換軍事時間 135
6.3.6
把帶小數點的小時、分鐘
或秒轉換成時間 136
6.3.7
在時間中添加小時數、
分鐘數或秒數 136
6.3.8
時區之間的轉換 137
6.3.9
時間值的舍入 138
6.3.10
計算期間值 138
第7章 計數與求和 141
7.1 工作表單元格的計數與求和
141
7.2 其他計數方法 143
7.3 基本計數公式 143
7.3.1
統計單元格的總數 144
7.3.2
統計空單元格的個數 144
7.3.3
非空單元格計數 145
7.3.4
數字單元格計數 145
7.3.5
文本單元格計數 145
7.3.6
非文本單元格計數 145
7.3.7
邏輯值計數 145
7.3.8
某個范圍中的錯誤值計數 145
7.4 高級計數公式 146
7.4.1
使用COUNTIF函數統計
單元格數量 146
7.4.2
統計滿足多個條件的
單元格數量 147
7.4.3
出現頻率最高的項的計數 150
7.4.4
確定特定文本的出現頻率 151
7.4.5
統計唯一值的數目 153
7.4.6
創建頻率分布 154
7.5 求和公式 160
7.5.1
對范圍內的所有單元格求和 160
7.5.2
對包含錯誤的范圍求和 161
7.5.3
計算累計和 161
7.5.4
求n個最大值的和 162
7.6 使用單個條件求和 163
7.6.1
只對負值求和 164
7.6.2
根據范圍Difference中的
值求和 165
7.6.3
基于文本的比較結果求和 165
7.6.4
基于日期的比較結果求和 165
7.7 使用多重條件求和 165
7.7.1
使用AND條件 166
7.7.2
使用OR條件 167
7.7.3
使用AND和OR條件 167
第8章 使用查找函數 169
8.1 什么是查找公式 169
8.2 與查找相關的函數 170
8.3 基本查找函數 171
8.3.1
VLOOKUP函數 171
8.3.2
HLOOKUP函數 173
8.3.3
LOOKUP函數 173
8.3.4
組合使用MATCH和
INDEX函數 175
8.4 專業查找公式 176
8.4.1
精確查找 177
8.4.2
查找值的左側 178
8.4.3
進行區分大小寫的查找 179
8.4.4
在多個查找表中進行選擇 179
8.4.5
確定考試分數的等級 180
8.4.6
計算等級平均分(GPA) 181
8.4.7
進行雙向查找 182
8.4.8
進行雙列查找 183
8.4.9
確定范圍中值的地址 184
8.4.10
使用最接近匹配查找
一個值 184
8.4.11
使用線性插值法查找
一個值 185
第9章 表格和列表 189
9.1 表格和術語 189
9.1.1
列表示例 190
9.1.2
表格示例 190
9.2 處理表格 191
9.2.1
創建表格 192
9.2.2
改變表格的外觀 193
9.2.3
在表格中導航和選擇 193
9.2.4
添加新行或新列 194
9.2.5
刪除行或列 194
9.2.6
移動表格 195
9.2.7
從表格中刪除重復的行 195
9.2.8
表格的排序和篩選 196
9.2.9
處理匯總行 201
9.2.10
在表格中使用公式 202
9.2.11
引用表格中的數據 204
9.2.12
將表格轉換為列表 208
9.3 使用高級篩選功能 208
9.3.1
設置條件范圍 209
9.3.2
應用高級篩選 209
9.3.3
清除高級篩選 211
9.4 指定高級篩選條件 211
9.4.1
指定單一條件 211
9.4.2
指定多重條件 213
9.4.3
指定計算條件 215
9.5 使用數據庫函數 216
9.6 插入分類匯總 217
第10章 其他計算 221
10.1
單位轉換 221
10.2
舍入數字 224
10.2.1
基本舍入公式 225
10.2.2
舍入到最近倍數 226
10.2.3
舍入貨幣值 226
10.2.4
處理以分數表示的
美元值 227
10.2.5
使用INT和TRUNC
函數 228
10.2.6
舍入為一個偶數或奇數 228
10.2.7
舍入為n位有效數字 229
10.3
解直角三角形 229
10.4
面積、表面積、周長和
體積的計算 231
10.4.1
計算正方形的面積和周長 231
10.4.2
計算矩形的面積和周長 232
10.4.3
計算圓的面積和周長 232
10.4.4
計算梯形的面積 232
10.4.5
計算三角形的面積 232
10.4.6
計算球體的表面積和體積 233
10.4.7
計算立方體的表面積和
體積 233
10.4.8
計算長方體的表面積和
體積 233
10.4.9
計算圓錐體的表面積和
體積 233
10.4.10
計算圓柱體的體積 234
10.4.11
計算棱椎的體積 234
10.5
解聯立方程組 234
10.6
處理正態分布 235
第Ⅲ部分 財務公式
第11章 借貸和投資公式 241
11.1
貨幣時值 241
11.2
貸款計算 242
11.2.1
用于計算貸款信息的
工作表函數 242
11.2.2
貸款計算示例 245
11.2.3
信用卡還款 246
11.2.4
創建貸款分期償還時間表 248
11.2.5
計算不定期還款的貸款 249
11.3
投資計算 251
11.3.1
單筆存款的未來值 251
11.3.2
一系列收益的現值 256
11.3.3
一系列存款的未來值 256
第12章 貼現和折舊計算公式 259
12.1
使用NPV函數 259
12.1.1
NPV的定義 260
12.1.2
NPV函數示例 261
12.2
使用IRR函數 265
12.2.1
回報率 266
12.2.2
幾何增長率 267
12.2.3
檢查結果 267
12.3
不定期的現金流 268
12.3.1
凈現值 269
12.3.2
內部回報率 270
12.4
折舊計算 270
第13章 財務計劃 275
13.1
創建財務計劃 275
13.2
創建分期償還計劃 275
13.2.1
簡單的分期償還計劃 276
13.2.2
動態的分期償還計劃 278
13.2.3
信用卡計算 280
13.3
使用數據表匯總貸款項 282
13.3.1
創建單變量數據表 282
13.3.2
創建雙變量數據表 283
13.4
財務決算和比率 285
13.4.1
基本財務決算 285
13.4.2
比率分析 288
13.5
創建指數 290
第Ⅳ部分 數組公式
第14章 數組 295
14.1
數組公式 295
14.1.1
多單元格數組公式 296
14.1.2
單個單元格數組公式 297
14.1.3
創建數組常量 298
14.1.4
數組常量元素 299
14.2
理解數組的維數 299
14.2.1
一維橫向數組 299
14.2.2
一維縱向數組 300
14.2.3
二維數組 300
14.3
命名數組常量 301
14.4
使用數組公式 303
14.4.1
輸入數組公式 303
14.4.2
選擇數組公式范圍 303
14.4.3
編輯數組公式 303
14.4.4
擴展或壓縮多單元格
數組公式 305
14.5
使用多單元格數組公式 305
14.5.1
從一個范圍的值中
創建數組 305
14.5.2
從一個范圍的值中創建
數組常量 306
14.5.3
對數組執行運算 306
14.5.4
對數組使用函數 307
14.5.5
數組轉置 308
14.5.6
生成一個連續整數的數組 309
14.6
使用單個單元格數組公式 310
14.6.1
范圍中的字符計數 310
14.6.2
范圍中最小的三個
數值求和 311
14.6.3
范圍中文本單元格的計數 312
14.6.4
省略中間公式 313
14.6.5
使用數組代替范圍引用 315
第15章 使用數組公式的技巧 317
15.1
使用單個單元格數組公式 317
15.1.1
對包含錯誤的范圍
進行求和 317
15.1.2
范圍中錯誤值的計數 318
15.1.3
范圍中n個最大值的求和 319
15.1.4
計算非零數的平均值 319
15.1.5
確定范圍中是否包含
特定值 320
15.1.6
兩個范圍中不同單元格的
計數 321
15.1.7
返回范圍中最大值的位置 323
15.1.8
查找范圍中某個值第n次
出現的行 323
15.1.9
返回范圍中的最長文本 323
15.1.10
確定一個范圍是否包含
有效值 324
15.1.11
整數數字求和 325
15.1.12
舍入值求和 326
15.1.13
對范圍中相隔n個數的
數值求和 327
15.1.14
從字符串中刪除非
數字字符 328
15.1.15
確定范圍中最接近的
數值 329
15.1.16
返回一列中的最后
一個數值 330
15.1.17
返回一行中的最后
一個數值 330
15.2
使用多單元格數組公式 331
15.2.1
只返回范圍中的正數 331
15.2.2
返回范圍中的非空單元格 332
15.2.3
反轉范圍中單元格的順序 333
15.2.4
對一個范圍的值動態排序 334
15.2.5
返回范圍中唯一元素的
列表 334
15.2.6
在范圍中顯示日歷 335
第Ⅴ部分 其他公式
第16章 導入和清洗數據 341
16.1
關于數據 341
16.2
導入數據 341
16.2.1
從文件導入 342
16.2.2
將文本文件導入到
指定范圍 344
16.2.3
復制并粘貼數據 345
16.3
數據清洗技術 346
16.3.1
刪除重復行 346
16.3.2
標識重復行 347
16.3.3
拆分文本 348
16.3.4
改變文本大小寫 353
16.3.5
刪除多余的空格 354
16.3.6
刪除亂字符 354
16.3.7
轉換值 355
16.3.8
對值進行分類 355
16.3.9
連接列 357
16.3.10
重新安排列 357
16.3.11
隨機化行 358
16.3.12
在列表中匹配文本 358
16.3.13
將縱向數據轉換為
橫向數據 359
16.3.14
填充導入報告中的空隙 361
16.3.15
拼寫檢查 362
16.3.16
替換或刪除單元格中的
文本 362
16.3.17
向單元格添加文本 364
16.3.18
修復尾部的負號 364
16.4
數據清洗檢查表 365
16.5
導出數據 365
16.5.1
導出到文本文件 366
16.5.2
導出到其他文件格式 366
第17章 圖表技術 369
17.1
理解SERIES公式 369
17.1.1
在SERIES公式中
使用名稱 371
17.1.2
斷開圖表系列與其數據
范圍的鏈接 371
17.2
創建與單元格的鏈接 372
17.2.1
添加圖表標題鏈接 372
17.2.2
添加坐標軸標題鏈接 373
17.2.3
添加文本鏈接 373
17.2.4
為圖表添加鏈接的圖片 374
17.3
圖表示例 374
17.3.1
單數據點圖表 374
17.3.2
在柱形圖表中顯示帶
條件的顏色 376
17.3.3
創建比較直方圖 378
17.3.4
創建甘特圖 379
17.3.5
創建箱式圖 381
17.3.6
繪出每隔n個點的數據 383
17.3.7
在圖表中標識最大值和
最小值 384
17.4
創建時間軸 385
17.4.1
繪制數學函數 386
17.4.2
繪制圓 390
17.4.3
創建鐘表圖 393
17.4.4
創建卓越的設計 394
17.5
使用趨勢線 396
17.5.1
線性趨勢線 396
17.5.2
非線性趨勢線 402
17.5.3
趨勢線方程總結 403
17.6
創建交互式圖表 404
17.6.1
從下拉列表中選擇系列 404
17.6.2
繪制最后n個數據點 404
17.6.3
選擇起始日期和點的數量 405
17.6.4
顯示人口數據 406
17.6.5
顯示天氣數據 407
第18章 數據透視表 411
18.1
數據透視表簡介 411
18.2
數據透視表示例 412
18.3
適合數據透視表的數據 414
18.4
自動創建數據透視表 416
18.5
手工創建數據透視表 417
18.5.1
指定數據 417
18.5.2
指定數據透視表的位置 418
18.5.3
數據透視表的布局 420
18.5.4
格式化數據透視表 421
18.5.5
修改數據透視表 423
18.6
更多的數據透視表示例 425
18.6.1
問題1：每個支行每天的
新存款金額有多少？ 425
18.6.2
問題2：一星期中哪天的
存款最多？ 425
18.6.3
問題3：按賬戶類型分，每
個支行開了多少賬戶？ 426
18.6.4
問題4：不同賬戶類型的
金額分布情況如何？ 426
18.6.5
問題5：柜員新開的哪類
賬戶最多？ 427
18.6.6
問題6：中心支行與其他兩
個支行相比，情況怎樣？ 428
18.6.7
問題7：哪個支行為新客戶
開的支票賬戶最多？ 429
18.7
組合數據透視表中的項 429
18.7.1
手工組合示例 430
18.7.2
查看組合的數據 431
18.7.3
自動組合示例 432
18.8
創建頻率分布 436
18.9
創建計算字段或計算項 437
18.9.1
創建計算字段 439
18.9.2
插入計算項 441
18.10
使用切片器篩選數據
透視表 443
18.11
使用日程表篩選數據
透視表 444
18.12
引用數據透視表中的
單元格 446
18.13
另一個數據透視表示例 447
18.14
使用數據模型 450
18.15
創建數據透視圖 453
18.15.1
數據透視圖示例 453
18.15.2
關于數據透視圖的
更多內容 455
第19章 條件格式 457
19.1
條件格式簡介 457
19.2
指定條件格式 459
19.2.1
可以應用的格式類型 459
19.2.2
定義自己的規則 460
19.3
使用圖形的條件格式 461
19.3.1
使用數據條 461
19.3.2
使用色階 462
19.3.3
使用圖標集 465
19.4
創建基于公式的規則 468
19.4.1
理解相對和絕對引用 469
19.4.2
條件格式公式示例 470
19.5
使用條件格式 476
19.5.1
管理規則 477
19.5.2
復制包含條件格式的
單元格 477
19.5.3
刪除條件格式 477
19.5.4
定位包含條件格式的
單元格 478
第20章 數據驗證 479
20.1
數據驗證簡介 479
20.2
指定驗證條件 480
20.3
可應用的驗證條件類型 481
20.4
創建下拉列表 483
20.5
將公式用于數據驗證規則 483
20.6
理解單元格引用 484
20.7
數據驗證公式示例 485
20.7.1
只接受文本 485
20.7.2
接受比前一個單元格大的
數值 486
20.7.3
只接受不重復的輸入項 486
20.7.4
接受以特定字符開頭的
文本 486
20.7.5
只接受是星期幾的日期 487
20.7.6
只接受不超過總和的值 487
20.7.7
創建有依賴關系的列表 487
20.7.8
使用結構化的表引用 489
第21章 創建大公式 491
21.1
什么是大公式 491
21.2
創建大公式：一個簡單的
示例 492
21.3
大公式示例 494
21.3.1
使用大公式刪除中間名 494
21.3.2
使用大公式返回字符串中最
后一個空格字符的位置 498
21.3.3
使用大公式判斷信用卡
號碼的有效性 501
21.3.4
使用中間命名公式 505
21.3.5
生成隨機姓名 506
21.4
大公式的優缺點 507
第22章 調試公式的工具和方法
509
22.1
什么是公式調試 509
22.2
公式的問題及其解決辦法 510
22.2.1
括號不匹配 510
22.2.2
用#填充的單元格 511
22.2.3
空白單元格并非空白 511
22.2.4
額外的空格字符 512
22.2.5
返回錯誤的公式 512
22.2.6
絕對/相對引用問題 517
22.2.7
運算符優先級問題 517
22.2.8
公式不能計算 518
22.2.9
實際數值與顯示的數值 519
22.2.10
浮點數錯誤
520
22.2.11
幻影鏈接錯誤 520
22.2.12
邏輯值錯誤 521
22.2.13
循環引用錯誤 522
22.3
Excel的審核工具 522
22.3.1
識別特定類型的單元格 522
22.3.2
查看公式 523
22.3.3
跟蹤單元格關系 524
22.3.4
跟蹤錯誤值 526
22.3.5
糾正循環引用錯誤 526
22.3.6
使用后臺錯誤檢查特性 526
22.3.7
使用Excel的公式求值器 527
第Ⅵ部分 開發自定義工作表函數
第23章 VBA簡介 531
23.1
宏的基本概念 531
23.1.1
激活開發工具選項卡 531
23.1.2
錄制宏 532
23.1.3
理解啟用宏的擴展名 534
23.1.4
Excel中宏的安全性 534
23.1.5
受信任位置 535
23.1.6
在個人宏工作簿中保存宏 535
23.1.7
將宏指定給按鈕和其他
窗體控件 536
23.1.8
將宏放到快速訪問工具欄 537
23.2
使用Visual Basic編輯器 538
23.2.1
了解VBE的組件 538
23.2.2
使用工程窗口 539
23.2.3
使用代碼窗口 541
23.2.4
自定義VBA環境 543
第24章 VBA編程概念 549
24.1
Excel對象模型簡述 549
24.1.1
理解對象 550
24.1.2
理解集合 550
24.1.3
理解屬性 551
24.1.4
理解方法 551
24.1.5
變量概述 552
24.1.6
錯誤處理 554
24.1.7
在代碼中使用注釋 556
24.2
一個函數過程示例 557
24.3
使用VBA的內置函數 559
24.4
控制執行 560
24.4.1
If-Then結構 560
24.4.2
Select Case結構 562
24.4.3
循環指令塊 563
24.5
使用范圍 567
24.5.1
For Each-Next結構 567
24.5.2
引用范圍 568
24.5.3
一些有用的范圍屬性 570
24.5.4
Set關鍵字 573
24.5.5
lntersect函數 573
24.5.6
Union函數 574
24.5.7
UsedRange屬性 574
第25章 函數過程基礎 577
25.1
為什么要創建自定義函數 577
25.2
VBA函數的示例 578
25.3
關于函數過程 579
25.3.1
聲明函數 579
25.3.2
選擇函數名 580
25.3.3
在公式中使用函數 581
25.3.4
使用函數參數 582
25.4
使用插入函數對話框 582
25.4.1
添加函數說明 583
25.4.2
指定函數類別 584
25.4.3
添加參數說明 586
25.5
測試和調試函數 586
25.5.1
使用VBA的MsgBox語句 587
25.5.2
在代碼中使用Debug.Print
語句 588
25.5.3
從子過程中調用函數 589
25.5.4
在函數中設置斷點 591
24.6
為函數創建加載項 592
第26章 VBA自定義函數示例 595
26.1
簡單函數 595
26.1.1
確定單元格是否隱藏 596
26.1.2
返回工作表名稱 596
26.1.3
返回工作簿名稱 597
26.1.4
返回應用程序名稱 597
26.1.5
返回Excel的版本號 597
26.1.6
返回單元格的格式信息 598
26.2
確定單元格的數據類型 599
26.3
多功能函數 600
26.4
生成隨機數 603
26.4.1
生成不變的隨機數 603
26.4.2
隨機選擇單元格 604
26.5
計算銷售傭金 605
26.5.1
用于簡單傭金結構的函數 606
26.5.2
用于復雜傭金結構的函數 607
26.6
文本處理函數 608
26.6.1
反轉字符串 608
26.6.2
攪亂文本 608
26.6.3
返回首字母縮寫詞
609
26.6.4
檢查文本是否與模式匹配 609
26.6.5
檢查單元格中是否包含
某個特定單詞 610
26.6.6
檢查單元格是否包含文本 611
26.6.7
從字符串中提取第n個
元素 612
26.6.8
拼寫一個數字 613
26.7
計數函數 613
26.7.1
計算與模式匹配的
單元格數 614
26.7.2
計算工作簿中的工作表
個數 614
26.7.3
計算范圍中的單詞數 614
26.8
日期函數 615
26.8.1
計算下一個星期一的日期 615
26.8.2
計算下一個星期幾的日期 616
26.8.3
指出日期是月份中的
第幾個星期 616
26.8.4
處理1900年以前的日期 617
26.9
返回列或行中的最后一個
非空單元格 618
26.9.1
LASTINCOLUMN函數 618
26.9.2
LASTINROW函數 619
26.10
多表函數 619
26.10.1
返回所有工作表中的
最大值 619
26.10.2
SHEETOFFSET函數 620
26.11
高級函數技術 621
26.11.1
返回錯誤值 621
26.11.2
從函數中返回一個數組 623
26.11.3
返回一個沒有重復隨機
整數的數組 624
26.11.4
隨機排列一個范圍 626
26.11.5
使用可選參數 628
26.11.6
使用不確定數量的參數 629
第Ⅶ部分 附錄
附錄A Excel函數參考 637
附錄B 使用自定義數字格式
653
新書資訊