教師必須知道的80個Excel函式

《教師必須知道的80個Excel函式》是2010年10月電子工業出版社出版的圖書，作者是起點文化。

基本介紹

• 書名：教師必須知道的80個Excel函式
• 作者：起點文化
• ISBN：978-7-121-11814-2
• 頁數：352頁
• 定價：39.80元（含光碟1張）
• 出版社：電子工業出版社
• 出版時間：2010年10月
• 開本：32開

內 容 簡 介

本書全面、細緻地介紹了Excel在教師工作中所必須掌握的函式知識。通過對函式的格式、參數、套用舉例、注意點說明幾個方面的講解，使讀者能夠對每一個函式的套用有基本的了解；在基本掌握函式後，通過案例、案例中的公式說明以及案例提示再次強調函式的具體套用問題，且每個案例都與實際工作息息相關，從而使讀者能夠真正的理解和完全的掌握函式，在工作中充分發揮函式的功能，提高工作效率。

本書按照函式的功能，將函式分為數學和三角函式、日期函式、邏輯函式、信息函式、查找和引用函式、文本函式、統計函式、以及其他函式等等進行介紹和講解。所講解的函式中，除了Excel 2007、Excel 2010新增的幾個函式SUMIFS、COUNTIFS、AVERAGEIF、AVERAGEIFS、ISODD、ISEVEN、IFRROR外，其他函式適用於Excel的各個版本，因此，對於本書的通用性，讀者大可放心。

前 言

許多讀者一直希望掌握很多常用的Excel函式，對Excel能夠了解的更深，以便在工作中派上用場，提高工作效率，而且便於日後的數據維護。本書正是為了滿足這樣的讀者而設計編寫的。

本書用案例形式的編排加上清晰的步驟講述，使讀者可以很輕鬆查詢、學習和掌握所需的函式使用。全書涵蓋了教師工作中常用的80個Excel函式，並採用小開本的編排方式，方便攜帶速查。

本書突出特色

1．含金量高

本書在第1章介紹了Excel中的基礎知識，相信讀者在學習完第1章後，對基本操作應該不會有太大問題。因此本書中的每個案例儘量省去繁瑣無用的分步操作，將節省的篇幅用於講解更多的函式具體套用、技巧以及擴展函式和案例，從質量上提高本書的含金量。

2．目的性強

書中對每個函式都加以了“套用舉例”，從套用舉例部分的圖例中就可達到對函式功能一目了然的目的。

3．案例精而多

書中編寫了大量的、有針對性的案例，能夠適合各個行業的實際套用，使讀者在實際工作中得心應手。此外，本書的配套光碟中包括了全書的所有函式套用舉例和案例，讀者在工作中可作為模板直接運用，從而大大的提高了工作效率，降低了工作強度。

4．學習功能強

本書除了對函式的參數、套用、注意點、案例、公式說明和案例提示進行了詳細的講解外，同時為了加深對函式的學習和理解增添了“擴展函式”體例，且在擴展函式同樣做出了細緻的講解，以達到讀者能夠真正掌握函式的套用的目的。

5．適用面廣、兼容性好

本書中所套用到的Excel函式，除了Excel 2007、Excel 2010的新增函式SUMIFS、COUNTIFS、AVERAGEIF、AVERAGEIFS、ISODD、ISEVEN外，其他函式完全能夠適用於Excel的早期版本，如Excel2003、Excel 2000等。

6．便於實操，舉一反三

全書採用理論與實踐相結合的方式，讀者可以根據書中的案例舉一反三，而且本書配套光碟中附帶了全書所有案例素材的結果檔案，可以直接將本書中的案例套用到實際工作中，從而更加有效地增強了學習的效果和實際套用的能力。

本書適合讀者

本書特別適用於對Excel辦公軟體已經有了一定操作經驗的普通用戶，也適用於初學者，更是廣大教師工作者提高辦公技能的必備手冊。

本書光碟說明

本書光碟內包括了所有案例的源檔案，源檔案保存格式為兼容格式。不論您的Excel是2010、2007，還是2003或2000，都可以打開檔案進行操作和實踐。

但由於Excel 2003與Excel 2007的某些規範、限制不同，因此本書部分案例為“.xlsx”格式，其具體的規範限制對比表如下：

功 能 Excel 2007最大限制 Excel 2003最大限制

工作表大小 1 048 576行乘以16 384列 65 536行乘以256列

分頁符個數 水平方向和垂直方向各1 026個 水平方向1 000個；垂直方向1 000個

工作簿中的顏色數 1 600萬種顏色（32位，具有到24位色譜的完整通道） 56

唯一單元格格式個數/單元格樣式個數 64 000 4 000

填充樣式個數 32 受可用記憶體限制

工作簿中的數字格式數 200和250之間，取決於所安裝的Excel的語言版本 受可用記憶體限制

排序引用的個數 單個排序中為64。如果使用連續排序，則沒有限制 單個排序中為3，如果使用連續排序則沒有限制

撤消層次個數 100 16

過濾器下拉列表個數 10 000 1 000

公式內容的長度 8 192個字元 1 024個字元

公式的內部長度 16 384個位元組 1 024個字元

疊代次數 32 767 32 767

函式的參數個數 255 30

函式的嵌套層數 64 7

可用工作表函式的個數 341 329

同時本書光碟還附贈了Office 2007操作技巧300招的電子檔案以及Excel 2007基礎操作的視頻檔案。

致謝

劉璐、王雅茹、張宏展等老師參與了本書的編寫工作，在此感謝。

編 者

2010年8月

目 錄

第1章 Excel函式知識

1.1 Excel常見名詞 2

1.2 單元格引用 3

1.3 公式基礎 9

1.4 函式基礎 12

1.5 錯誤分析與檢查 22

1.6 Excel數據安全 24

第2章 數學和三角函式

2.1 SUM 返回某一單元格區域中所有數字之和 30

2.2 SUMIFS 對區域中滿足多個條件的單元格求和 32

2.3 SUMPRODUCT 返回數組間對應的元素乘積之和 36

2.4 MMULT 返回兩個數組的矩陣乘積 38

2.5 RAND 返回一個隨機值 44

2.6 EVEN 返回沿絕對值增大方向取整後最接近的偶數 47

2.7 INT 返回參數的整數部分 49

2.8 CEILING 返回沿絕對值增大方向的捨入值 51

2.9 SUBTOTAL 返回列表或資料庫中的分類匯總 53

2.10 PRODUCT 返回所有參數乘積值 58

2.11 MDETERM 返回一個數組的矩陣行列式的值 60

2.12 MINVERSE 返回數組中存儲的矩陣的逆矩陣 62

2.13 FLOOR 將number向下捨入（向零的方向）到最接近的singnificance的倍數 64

2.14 MOD 返回兩數相除的餘數 66

第3章 邏輯函式

3.1 AND 判定指定的多個條件是否全部成立 70

3.2 NOT 對其參數的邏輯求反 73

3.3 IF 根據指定的條件返回不同的結果 76

3.4 OR 判定指定的任一條件是為真，即返回真 81

第4章 文本函式

4.1 CONCATENATE 84

4.2 FIND 查找字元串字元起始位置（區分大小寫） 87

4.3 SEARCH 查找字元串字元起始位置（不區分大小寫） 90

4.4 SUBSTITUTE 在文本字元串中用新文本替換舊文本 93

4.5 LEFT 返回文本值中最左邊的字元 96

4.6 MID 從文本字元串中的指定位置起返回特定個數的字元 98

4.7 RIGHT 返回文本值中最右邊的字元 101

4.8 FINDB 查找字元串位元組起始位置 104

4.9 SEARCHB 查找字元串位元組起始位置（不區分大小寫） 106

4.10 REPLACEB 替換字元串中的部分字元

（根據位元組數） 109

4.11 REPLACE 替換字元串中的部分字元（根據字元數） 111

4.12 TEXT 設定數字格式並將其轉換為文本 113

4.13 MIDB 從文本字元串中的指定位置起返回特定個數的字元 117

4.14 LEFTB 根據所指定的位元組數返回文本字元串中最左邊的字元 119

第5章 統計函式

5.1 AVERAGE 返回參數的平均值 122

5.2 AVERAGEA 計算參數列表中數值的平均值 127

5.3 AVERAGEIFS 返回滿足多重條件的所有單元格的平均值 131

5.4 COUNT 返回參數列表中的數字個數 134

5.5 COUNTA 返回參數列表中非空值的單元格個數 138

5.6 COUNTIF 計算區域中滿足給定條件的單元格的個數 142

5.7 COUNTIFS 計算區域內符合多個條件的單元格的數量 153

5.8 MAX 返回一組值中的最大值 157

5.9 MAXA 返回參數列表中的最大值 162

5.10 MEDIAN 返回給定數值的中值 165

5.11 MIN 返回參數中最小值 167

5.12 SMALL 求一組數值中第k個最小值 171

5.13 LARGE 求一組數值中第k個最大值 178

5.14 RANK 返回一組數字的排列順序 182

5.15 FREQUENCY 返回一個垂直數組 186

5.16 AVEDEV 返回一組數據與其平均值的絕對偏差的平均值 198

5.17 GEOMEAN 返回正數數組或區域的幾何平均值 200

5.18 COUNTBLANK 返回參數列表中非空值的單元格個數 202

5.19 PERCENTILE 返回區域中數值的第K個百分點的值 204

5.20 PERCENTRANK 返回特定數值在一個數據集中的百分比排位 206

5.21 QUARTILE 返回數據集的四分位數 208

第6章 日期和時間函式

6.1 YEAR 返回某日期對應的年份 216

6.2 TODAY 返回當前日期 218

6.3 NOW 返回當前的日期和時間 220

6.4 MONTH 返回某日期對應的月份 222

6.5 DATE 返回特定日期的年、月、日 224

6.6 TIME 返回某一特定時間的小數值 227

第7章 查找和引用函式

7.1 ROW 返回引用的行號 230

7.2 ROWS 返回數組或引用的行數 237

7.3 COLUMN 返回引用的列標 239

7.4 COLUMNS 返回數組或引用的列數 242

7.5 INDIRECT 返回由文本字元串指定的引用 244

7.6 ADDRESS 按照指定的行號和列標，返回單元格引用地址 251

7.7 OFFSET 以指定引用為參照系，通過給定偏移量得到新的引用 254

7.8 INDEX 返回指定單元格或單元格數組的值（數組形式） 272

7.9 LOOKUP 從單列或單行區域返回值（向量形式） 279

7.10 VLOOKUP 在數據表的首列查找指定的值，

並返回數據表當前行中指定列的值 284

7.11 HLOOKUP 在數據表的首行查找指定的數值，並在數據表中指定行的同一列中返回一個數值 291

7.12 MATCH 返回指定方式下與指定數值匹配的元素位置 296

7.13 CHOOSE 返回指定數值參數列表中的數值 301

7.14 HYPERLINK 創建一個捷徑，打開存儲在網路伺服器、Intranet或Internet中的檔案 304

7.15 AREAS 返回引用中包含的區域個數 307

第8章 信息函式

8.1 ISEVEN 偶數判斷 310

8.2 ISTEXT 文本判斷 314

8.3 N 返迴轉換為數字後的值 317

8.4 NAMES 返回指定工作簿中被定義的指定名字 322

8.5 GET.OBJECT 返回可用於處理該對象的其他宏公式方面的信息 324

8.6 EVALUATE 對以文字表示的一個公式或表達式求值，並返回結果 329

第2章 數學和三角函式

案例1 求圖書訂購價格總和（SUM） 30

案例2 只匯總60到80分的成績（SUMIFS） 33

案例3 匯總三年級二班人員遲到次數（SUMIFS） 34

案例4 匯總一班人員獲獎次數（SUMPRODUCT） 36

案例5 求第一名人員最多有幾次（MMULT） 38

案例6 在不同班級有同名前提下計算學生人數（MMULT） 39

案例7 計算前進中學參賽人數（MMULT） 40

案例8 計算成績在60到80分之間合計數與個數（MMULT） 42

案例9 將20個學生的考位隨機排列（RAND） 44

案例10 將三個學校植樹人員隨機分組（RAND） 46

案例11 統計參考人數（EVEN） 47

案例12 成績表的格式轉換（INT） 49

案例13 成績表的格式轉換（CEILING） 51

案例14 按需求對成績進行分類匯總（SUBTOTAL） 54

案例15 不間斷的序號（SUBTOTAL） 55

案例16 僅對篩選出的人員排名次（SUBTOTAL） 56

案例17 計算每種教材打折後的價格（PRODUCT） 58

案例18 解二元聯立方程組（MDETERM） 60

案例19 解多元聯立方程組（MINVERSE） 62

案例20 FLOOR函式處理正負數混合區域（FLOOR） 64

案例21 將數值逐位相加成一位數（MOD） 66

第3章 邏輯函式

案例22 判斷學生是否符合獎學金髮放條件（AND） 70

案例23 所有裁判都給“通過”就進入決賽（AND） 71

案例24 沒有任何裁判給“不通過”就進行決賽（NOT） 73

案例25 計算成績區域數字個數（NOT） 74

案例26 評定學生成績是否及格（IF） 76

案例27 根據學生成績自動產生評語（IF） 77

案例28 將成績從大到小排列（IF） 78

案例29 排除空值（IF） 79

案例30 根據年齡判斷教師職工是否退休（OR） 81

第4章 文本函式

案例31 計算平均成績及評判是否及格（CONCATENATE） 84

案例32 提取前三名人員姓名（CONCATENATE） 85

案例33 提取學校與醫院地址（FIND） 88

案例34 將年級或者專業與班級名稱分開（SEARCH） 91

案例35 提取最後一次短跑成績（SUBSTITUTE） 94

案例36 計算國小參賽者人數（LEFT） 96

案例37 提取成績並計算平均（MID） 99

案例38 提取參賽選手姓名（MID） 100

案例39 對所有人員按平均分排序（RIGHT） 101

案例40 提取學生英文名字（FINDB） 105

案例41 提取各軟體的版本號（SEARCHB） 107

案例42 將15位身份證號碼升級為18位（REPLACEB） 110

案例43 求最大時間（REPLACE） 112

案例44 根據身份證號碼計算學生出生年月日（TEXT） 115

案例45 提取各年級獲獎人員姓名（MIDB） 118

案例46 提取學生所在省市名稱（LEFTB） 120

第5章 統計函式

案例47 計算平均成績（忽略缺考人員）（AVERAGE） 122

案例48 計算90分以上的平均成績（AVERAGE） 123

案例49 計算當前表以外的所有工作表平均值

（AVERAGE） 125

案例50 計算平均成績，成績空白也計算（AVERAGEA） 127

案例51 計算二年級所有人員的平均獲獎率

（AVERAGEA） 128

案例52 統計前三名人員的平均成績（AVERAGEA） 130

案例53 去掉首尾求平均分（AVERAGEIFS） 132

案例54 計算及格率（COUNT） 134

案例55 統計各分數段人數（COUNT） 135

案例56 統計有多少個選手（COUNT） 136

案例57 統計出勤異常人數（COUNTA） 138

案例58 判斷是否有人缺考（COUNTA） 139

案例59 根據畢業學校統計中學學歷人數（COUNTIF） 142

案例60 計算兩列數據相同個數（COUNTIF） 143

案例61 統計連續三次進入前十名的人數（COUNTIF） 145

案例62 統計淘汰者人數（COUNTIF） 146

案例63 統計區域中不重複數據的個數（COUNTIF） 147

案例64 統計冠軍榜前三名（COUNTIF） 148

案例65 統計真空、假空單元格個數（COUNTIF） 150

案例66 對名冊表進行混合編號（COUNTIF） 151

案例67 統計大於80分的三好學生個數（COUNTIFS） 154

案例68 統計二班和三班數學競賽獲獎人數（COUNTIFS） 155

案例69 根據身高計算各班淘汰人數（COUNTIFS） 156

案例70 查找第一名學生姓名（MAX） 157

案例71 計算衛冕失敗最多的次數（MAX） 158

案例72 低於平均成績中的最優成績（MAX） 159

案例73 計算語文成績大於90分者的最高總成績（MAXA） 162

案例74 計算數學成績等於100分的男生最高總成績（MAXA） 164

案例75 計算中間成績（MEDIAN） 165

案例76 確定最佳成績（MIN） 167

案例77 將科目與成績分開（MIN） 168

案例78 計算五個班的第一名人員的最低成績（MIN） 170

案例79 第三個最小的成績（SMALL） 171

案例80 計算最後三名成績的平均值（SMALL） 172

案例81 將成績按升序排列（SMALL） 173

案例82 羅列三個班第一名成績（SMALL） 174

案例83 羅列三個工作表B列最後三名成績（SMALL） 175

案例84 第3個最小成績到第6個最小成績之間的人數（SMALL） 176

案例85 計算與第3個最大值並列的個數（LARGE） 178

案例86 按成績列出學生排行榜（LARGE） 179

案例87 最後一次獲得第一名是第幾屆（LARGE） 180

案例88 對學生成績排名（RANK） 182

案例89 計算兩列數值相同個數（RANK） 183

案例90 查詢某人成績在三個班中的排名（RANK） 185

案例91 分別統計每個分數段的人員個數（FREQUENCY） 187

案例92 蟬聯冠軍最多的次數（FREQUENCY） 188

案例93 計算最多經過幾次測試才成功（FREQUENCY） 189

案例94 計算三個不連續區間的頻率分布（FREQUENCY） 190

案例95 計算國小加國中人數及中專加大學人數（FREQUENCY） 191

案例96 計算文本的頻率分布（FREQUENCY） 192

案例97 奪冠排行榜（FREQUENCY） 193

案例98 誰蟬聯冠軍次數最多（FREQUENCY） 194

案例99 中國式排名（FREQUENCY） 195

案例100 誰獲得第二名（FREQUENCY） 196

案例101 計算零件質量係數的平均偏差（AVEDEV） 198

案例102 計算利潤的平均增長率（GEOMEAN） 200

案例103 統計未參加測試的人數（COUNTBLANK） 202

案例104 根據習題完成量計算指定百分點的值（PERCENTILE） 204

案例105 計算某個學生所完成的習題量在所有學生中的

百分比排位（PERCENTRANK） 206

案例106 根據學生習題完成情況計算四分位數（QUARTILE） 209

第6章 日期和時間函式

案例107 計算2000年前電腦培訓平均收費（YEAR） 216

案例108 本月有幾個科目需要結束課時（TODAY） 218

案例109 確定是否已到丟棄時間（NOW） 220

案例110 計算8月份筆筒和毛筆的進貨數量（MONTH） 222

案例111 確定今年母親節的日期（DATE） 225

案例112 安排會議時間（TIME） 227

第7章 查找和引用函式

案例113 最後一次不及格是哪次測試（ROW） 231

案例114 計算第11名到第30名學員的平均成績（ROW） 232

案例115 計算成績排名，不能產生並列名次（ROW） 233

案例116 對班級和成績升序排列（ROW） 234

案例117 羅列第一名學生姓名（ROW） 235

案例118 檢測每個志願是否與之前的重複（ROWS） 237

案例119 班級成績查詢（COLUMN） 239

案例120 羅列每日缺席名單（COLUMN） 240

案例121 提取引用區域右下角的數據（COLUMNS） 242

案例122 整理成績單（INDIRECT） 245

案例123 合併三個工作表的數據（INDIRECT） 246

案例124 多區域計數（INDIRECT） 248

案例125 計算五個工作表最大平均值（INDIRECT） 249

案例126 根據下拉列表羅列班級成績第一名姓名（ADDRESS） 252

案例127 查詢成績（OFFSET） 255

案例128 計算10屆運動會中有幾次破紀錄（OFFSET） 256

案例129 計算至少兩科不及格的學生人數（OFFSET） 257

案例130 列出成績最好的科目（OFFSET） 258

案例131 計算及格率不超過50%的科目數（OFFSET） 259

案例132 計算語文、英語、化學、政治哪科總分最高（OFFSET） 260

案例133 羅列及格率最高的學生姓名（OFFSET） 261

案例134 排列植樹最多的班級（OFFSET） 262

案例135 多表成績查詢（OFFSET） 263

案例136 計算每個學生總分是否高於本班平均成績（OFFSET） 265

案例137 計算每個學生進入前三名的科目數（OFFSET） 266

案例138 計算高於單科平均值的科目總數（OFFSET） 267

案例139 羅列平均成績倒數三名的班級（OFFSET） 268

案例140 重組培訓科目表（OFFSET） 269

案例141 分別計算每個班第一名的成績和姓名（OFFSET） 270

案例142 計算60分到95分之間的人員個數（INDEX） 273

案例143 按身高對學生排列座次表（INDEX） 274

案例144 重組教師授課表（INDEX） 275

案例145 提取三個不規則區域的交集（INDEX） 277

案例146 計算某班六年中誰獲第一名次數最多（LOOKUP） 280

案例147 羅列每個名次的所有姓名（LOOKUP） 281

案例148 填補空白區（LOOKUP） 282

案例149 將得分轉換成等級（VLOOKUP） 285

案例150 查找雙列信息（VLOOKUP） 286

案例151 按學歷對姓名排序（VLOOKUP） 288

案例152 多工作表查找最大值（VLOOKUP） 289

案例153 在同一行查找數據（HLOOKUP） 292

案例154 製作准考證（HLOOKUP） 293

案例155 計算補課科目總數（MATCH） 297

案例156 按班級插入分隔行（MATCH） 298

案例157 統計一、二班舉重參賽人員數（MATCH） 299

案例158 讓VLOOKUP函式在多區域查找（CHOOSE） 302

案例159 選擇二年級曠課人員名單（HYPERLINK） 305

案例160 統計分公司數量（AREAS） 307

第8章 信息函式

案例161 計算期末平均成績（ISEVEN） 310

案例162 提取期末成績明細（ISEVEN） 311

案例163 提取每年級第一名名單（ISTEXT） 314

案例164 統計各班所有科目成績大於60分者人數（N） 317

案例165 將數據間隔著色（N） 319

案例166 羅列工作簿中所有名稱（NAMES） 322

案例167 提取單元格中的批註（GET.OBJECT） 327

案例168 按分隔設定取數並求平均（EVALUATE） 329