內容簡介
本書通過250個實戰秘技，講解Excel函數與公式實戰技巧。
全書共12章，分別介紹公式與函數基礎、名稱的定義與使用、文本函數、日期與時間函數、邏輯函數、
數學和三角函數、查找與引用函數、統計函數、信息函數、財務函數、其他函數以及公式與函數常見問題等內
容。
本書結構合理，圖文并茂，既適合各行業工作人員使用，也適合經常從事數據統計與分析的管理人員使
用，同時還適合學習Excel公式與函數的初學者使用。
前言
在日常辦公中處處可見Excel 的身影，如數據記錄、數據計算、數據統計、數據分析、項
目匯報等，Excel 已經成為我們工作中的一把利器。現在很多公司、企業在招聘時，都要求應聘
的人員會使用Excel，這使得我們必須學會與掌握Excel。而掌握Excel 最為重要的就是要學會
公式與函數的使用，它是所有數據計算、數據統計、數據分析、數據展示等的核心。
本書的特色包含以下4 點。
快速索引，簡單便捷：本書考慮到讀者實際遇到問題時的查找習慣，標題即點明要點，從而在目
錄中即可快速檢索出自己需要的技巧。
傳授秘技，招招實用：本書總結了250 個使用Excel 函數與公式時常見的難題，并對Excel 的每
一步操作都進行詳細講解，從而使讀者輕松掌握實用的操作秘技。
知識拓展，學以致用：本書中的每個技巧都含有知識拓展內容，對每個技巧的知識點都進行了延伸，
能夠讓讀者學以致用，對日常的工作、學習有所幫助。
圖文并茂，視頻教學：本書采用一步一圖形的方式，使技巧的講解形象而生動。另外，本書配備
了所有技巧的教學視頻，使讀者學習Excel 圖表與數據分析更加輕松。
本書內容分為12 章，具體內容介紹如下。
第1 章 公式與函數基礎：介紹公式與函數的基礎操作，如文本連接運算符的使用、公式的多種
復制方法、函數的記憶式鍵入、數據源的引用等內容。
第2章 名稱的定義與使用：介紹名稱的作用、名稱的定義、名稱的管理以及名稱的使用等內容。
第3 章 文本函數：介紹Excel 最為常用的文本函數，如CONCATENATE 函數、FIND 函數、LEN
函數、LEFT 函數等。
第4 章 日期與時間函數：介紹Excel 最為常用的日期與時間函數，如DAYS360 函數、DATEDIF
函數、DATE 函數、DAY 函數、MINUTE 函數、MONTH 函數、NOW 函數等。
第5章 邏輯函數：介紹Excel最為常用的邏輯函數，如AND函數、FALSE函數、IF函數、OR函數等。
第6 章 數學和三角函數：介紹Excel 最為常用的數學與三角函數，如ABS 函數、ACOS 函數、
ASIN 函數、LN 函數、MOD 函數等。
第7章 查找與引用函數：介紹Excel最為常用的查找與引用函數，如HLOOKUP函數、LOOKUP
函數、VLOOKUP 函數、INDEX 函數、MATCH 函數等。
第8 章 統計函數：介紹Excel 最為常用的統計函數，如AVERAGE 函數、AVERAGEA 函數、
AVERAGEIF 函數、MAX 函數、MIN 函數、COUNTIF 函數等。
第9章 信息函數：介紹Excel最為常用的信息函數，如CELL函數、ISTEXT函數、ISBLANK函數、
ISNONTEXT 函數等。
第10章 財務函數：介紹Excel最為常用的賬務函數，如FV函數、RATE函數、PV函數、PMT函數、
IRR 函數等。
第11 章 其他函數：介紹Excel 以上8 類常用函數以外的其他函數，如DELTA函數、GESTEP 函
數、ERF 函數、DSUM 函數、DAVERAGE 函數、DCOUNT 函數等。
第12 章 公式與函數常見問題：介紹Excel 函數與公式在使用中出現的一些問題，如出現錯誤值
的原因、數據源的更新、“#####”錯誤值的解決方法、“#DIV/0!” 錯誤值的解決方法、“#N/A”
錯誤值的解決方法、“#NAME?” 錯誤值的解決方法、“#NULL!” 錯誤值的解決方法、“#NUM!”
錯誤值的解決方法、“#REF!” 錯誤值的解決方法等內容。
作者團隊及服務
本書由李杏林編著，其他參與編寫的人員還有張小雪、羅超、李雨旦、孫志丹、何輝、彭蔓、
梅文、毛瓊健、胡丹、何榮、張靜玲、舒琳博等。
由于作者水平有限，書中錯誤、疏漏之處在所難免。在感謝您選擇本書的同時，也希望您
能夠把對本書的意見和建議告訴我們。
編 者
目錄
第1 章 公式與函數基礎 ......1
招式001 公式中的文本連接運算符的使用 ........2
招式002 編輯與修改公式 .........3
招式003 快速復制公式的多種方法 ...................5
招式004 查看工作表中的所有公式 ...................6
招式005 函數也來玩“搜索” .7
招式006 快速獲取函數的幫助信息 ...................8
招式007 Excel 數據源的相對引用 ....................9
招式008 Excel 數據源的絕對引用 ....................9
招式009 在相對引用和絕對引用之間切換 ......10
招式010 引用當前工作表之外的單元格 ..........12
招式011 跨工作簿引用數據 ...13
招式012 引用多個工作表中的同一單元格
運算 ........................15
招式013 公式中函數的記憶式鍵入 .................16
招式014 自動求和功能的使用 ........................17
招式015 快速選中公式中引用的單元格 ..........18
招式016 將公式運算結果轉換為數值 ..............19
招式017 函數參數的修改 .......20
招式018 函數的嵌套方法 .......21
招式019 如何在單元格中自動填入當前日期 ...22
招式020 隱藏保護工作表中的公式 .................22
招式021 正確區分空文本與空單元格 ..............24
招式022 為何數據無法進行計算 .....................25
招式023 按F9 鍵查看公式運算結果 ...............26
招式024 跳過非空單元格批量建立公式 ..........27
招式025 公式的監視 ..............28
招式026 公式錯誤檢查 ..........30
招式027 逐步分解公式 ..........31
招式028 普通公式與數組公式 ........................32
第2 章 名稱的定義與使用 ...33
招式029 定義名稱的規則 .......34
招式030 使用名稱的好處 .......34
招式031 使用“定義名稱”功能按鈕 ..............36
招式032 使用名稱框創建名稱 ........................36
招式033 根據所選內容批量創建名稱 ..............37
招式034 名稱應用于公式計算 ........................38
招式035 定義動態引用的名稱 ........................39
招式036 使用名稱管理器篩選名稱 .................41
招式037 修改與編輯名稱 .......42
招式038 導出名稱列表 ..........43
招式039 刪除名稱 .................44
招式040 為Excel 公式定義名稱 .....................45
招式041 讓定義的名稱只應用于當前工作表 ...47
第3 章 文本函數 .................49
招式042 CONCATENATE——快速自動生成訂單編號 .................50
招式043 CONCATENATE——合并面試人員的總分數與錄取情況 .................50
招式044 FIND——搜索指定字符在字符串中的位置 .....................51
招式045 LEN——從員工身份證號碼中提取出生年份 .................52
招式046 LEN——從身份證號碼中提取性別 ...53
招式047 MID——從身份證號碼中提取完整的出生日期 .................54
招式048 LEFT——根據編碼返回部門名稱 .....55
招式049 LEFT——從完整地址中提取省份名 .....................56
招式050 LEFT——根據商品名稱實現一次性調價 .............56
招式051 LEN——驗證身份證號碼的位數 .......57
招式052 TEXT——讓計算的金額顯示為“余款:12，250.00”的形式 ...........59
招式053 LEFT——從E-mail 地址中提取賬號 ........................59
招式054 REPT——根據銷售額用“★”評定等級 .................60
招式055 RIGHT——將電話號碼的區號與號碼隔開 .................61
招式056 EXACT——比較兩個門店的平均售價是否相同 ..........62
招式057 PROPER——一次性將每個單詞的首字母轉換為大寫 ...63
招式058 REPLACE——屏蔽手機號碼的后幾位數 .................64
招式059 LEFT——快速生成對客戶的稱呼 .....65
招式060 ASC——將全角( 雙字節) 字符更改為半角( 單字節) 字符 ..............66
招式061 WIDECHAR——將半角( 單字節)字符轉換為全角( 雙字節) 字符 .......66
招式062 BAHTTEXT——將數字轉換為β( 銖) 貨幣格式文本.......................67
招式063 DOLLAR——將數字轉換為帶美元符號$ 的文本 ..........68
招式064 RMB——將數字轉換為帶人民幣符號¥ 的文本 ..........69
招式065 CHAR——返回數字對應的字符代碼 .................70
招式066 CODE——返回字符代碼對應的數字 ........................70
招式067 UPPER——將文本轉換為大寫字母 .................71
招式068 LOWER——將文本轉換為小寫字母 .................72
招式069 VALUE——將文本型數字轉換為可計算的數值數據 ...73
招式070 TEXT——將“20111001”格式轉換為“2011-10-01”格式 .............74
招式071 TEXT——數據合并時避免0被省略 .....................75
招式072 SEARCH——從項目編碼中提取合同號 .............76
招式073 TRIM——刪除文本中多余的空格 .....77
招式074 SUBSTITUTE——替換文本字符串中的特定文本 ........................78
招式075 SUBSTITUTE——刪除文本中的所有空格 .................79
招式076 CLEAN——刪除文本中所有無法打印的字符 .............80
第4 章 日期與時間函數 .......81
招式077 DAYS360——計算總借款的天數 .....82
招式078 DATEDIF——計算兩個日期相差的年月日數 .................83
招式079 DATEDIF——為員工自動追加工齡工資 .............84
招式080 DATEVALUE——計算借款的還款天數 .................85
招式081 EDATE——計算食品的過期日期......86
招式082 DATE——確定明年父親節的日期 ....86
招式083 DAY——指定日期的上一月份的天數 ........................87
招式084 DATEDIF——計算2010 年到2014 年共有多少天 .88
招式085 EOMONTH——返回員工結算工資的日期 .............89
招式086 HOUR——計算出員工缺勤的小時數 .....................91
招式087 MINUTE——計算停車時間 ..............92
招式088 MONTH——自動填寫報表中的月份 ........................93
招式089 NETWORKDAYS——計算年假占全年工作日的百分比 ....................94
招式090 NOW——返回當前系統日期和時間 .............96
招式091 SECOND——計算通話秒數 .............97
招式092 TIMEVALUE——將文本格式的時間轉換為序列數 ...98
招式093 TIME——安排會議時間 ...................98
招式094 TODAY——返回系統當前日期 .........99
招式095 TODAY——計算勞動節倒計時的天數 ......................100
招式096 WEEKDAY——自動返回各日期所對應的星期數 ....101
招式097 WEEKDAY——匯總12 月份星期六的收入金額 .102
招式098 WEEKNUM——返回指定日期在一年中是第幾周 ....104
招式099 WORKDAY——根據休假天數返回休假結束日期 ........104
招式100 YEARFRAC——計算連續日期之間天數占全年天數的百分比 ...............105
招式101 YEAR——計算今天到年末時還有多少天 ...............106
第5 章 邏輯函數 ...............109
招式102 AND——判斷員工的銷售額是否達標 ............... 110
招式103 FALSE——返回邏輯值FALSE ...... 111
招式104 IFERROR——根據公式結果返回不同內容 ...................... 111
招式105 IF——根據銷售額計算提成額 ........ 112
招式106 IF——評定員工的考核等級 ............ 113
招式107 IF——給持普通積分卡和VIP 卡的
客戶派發贈品 ........ 114
招式108 IF——計算個人所得稅 ................... 115
招式109 NOT——篩選掉年齡在25歲以下的應聘人員 ............... 117
第6 章 數學和三角函數 .....123
招式110 OR——對員工的考核成績進行綜合評定 ............... 118
招式111 OR——根據年齡判斷職工是否退休 ............... 118
招式112 OR——判斷未來年份是閏年還是平年 ............... 119
招式113 TRUE——判斷員工的身份證號碼位數是否正確 ........121
招式114 ABS——快速計算相應數字的絕對值 ...................124
招式115 ABS——比較學生上月和本月的成績進退情況 ........124
招式116 ACOS——求指定角度的反余弦值 ...............125
招式117 ASIN——求指定角度的反正弦值....126
招式118 ATAN——求指定角度的反正切值 ...126
招式119 CEILING——以5 秒( 不足5 秒按5 秒計算) 為單位計算通話費用 ......................127
招式120 COS——求指定角度的余弦值 .......128
招式121 DEGREES——將弧度轉換為角度 ......................129
招式122 EXP——計算e 的n 次方 ...............129
招式123 FLOOR——對數據進行向下舍取求值 ...............130
招式124 INT——對平均銷量取整 .................131
招式125 LN——計算某個數值的自然對數 ...132
招式126 LOG10——計算指定正數值以10 為底數的對數值 ....................133
招式127 LOG——計算指定正數值和底數的對數值 ...................134
招式128 MOD——計算通話剩余秒數 ..........134
招式129 MOD——按奇偶行匯總數據 ..........135
招式130 PI——將指定角度轉換為弧度值 .....136
招式131 POWER——計算數值的乘冪 .........137
招式132 RADIANS——將角度轉換為弧度 ...137
招式133 RANDBETWEEN——隨機生成兩個指定數之間的整數 ..................138
招式134 RAND——快速生成隨機數字 .........139
招式135 RAND——自動生成彩票7 位開獎號碼 ...............140
招式136 ROUNDDOWN——根據小數位數進行向下舍入計算 .140
招式137 ROUNDUP——按小數位數進行向上舍入計算 ........141
招式138 ROUNDOWN——界定時間的整點范圍 ...............142
招式139 ROUNDUP——計算物品的快遞費用 ...............143
招式140 ROUND——對數據進行四舍五入 ...............144
招式141 SIN——求指定角度的正弦值 .........145
招式142 SQRT——計算數值對應的正平方根 ...............146
招式143 SUBTOTAL——在工資統計表中得出匯總方式的結果 ......................147
招式144 SUM——快速計算總銷售金額 .......149
招式145 SUM——統計營銷部男員工人數 ....149
招式146 SUMIF——統計各部門工資總額 ....150
招式147 SUMIF——統計10 月份前半個月與后半個月的銷售額 .152
招式148 SUMIF——計算同一類產品的總銷售額 ...............153
招式149 SUMIFS——按多個條件對指定
單元格求和 ...........154
招式150 SUMPRODUCT——統計出指定部門、指定職務的員工數 .156
招式151 SUMSQ——計算所有參數的平方和 ...................157
招式152 TRUNC——計算銷售金額時取整或保留指定位數小數 .158
第7 章 查找與引用函數 .....161
招式153 HLOOKUP——根據不同的返利率計算各筆訂單的返利金額 ...............162
招式154 HLOOKUP——計算每位員工的銷售提成率 ...........164
招式155 LOOKUP——返回銷售筆數最多的員工姓名 ...............165
招式156 VLOOKUP——根據多條件計算員工年終獎 ...........167
招式157 VLOOKUP——查詢指定員工的銷售數據 ...............168
招式158 INDEX——查詢總金額最高所對應的門店 ........169
招式159 INDEX——查詢最高總分對應的學號 ......................171
招式160 CHOOSE——判斷員工考試成績是否合格 ...............172
招式161 MATCH——返回銷售額最高的員工姓名 ...............174
招式162 COLUMNS——返回數據區域包含的列數 ...........175
招式163 ADDRESS——返回指定行號和列號所對應的單元格地址 ......................176
招式164 COLUMN——返回數據區域首列的列號 ......................177
招式165 ROW——填充一年中所有月的月份名 ...................178
招式166 ROWS——判斷值班人員是否重復 ...............180
招式167 OFFSET——實現數據的動態查詢 ......................181
招式168 HYPERLINK——為指定內容創建超鏈接 ...................183
招式169 TRANSPOSE——數據區域的行列轉置 ......................184
第8 章 統計函數 ...............185
招式170 AVERAGE——計算某件商品的平均售價 ...............186
招式171 AVERAGEIFS——忽略0 值求指定部門的平均銷售額 .187
招式172 AVERAGEA——統計各月份的平均銷售額( 計算區域含文本值) .............188
招式173 AVERAGEIF——計算滿足條件的數據的平均值 ........189
招式174 MAX——計算單日銷售金額并返回最大值 ...................191
招式175 MAXA——返回數據區域中非空值的最大值 ....192
招式176 MIN——返回上半個月單筆最低銷售金額 ...............192
招式177 MINA——返回數據區域中非空值的最小值 ...................194
招式178 RANK.EQ——對員工銷售業績排名次 ...................194
招式179 COUNTIF——統計員工試用期到期的人數 ...........195
招式180 COUNTIF——統計銷售額大于1000 的員工人數 ...................196
招式181 COUNTIF——統計重復的員工人數 ...............197
招式182 COUNTIFS——計算滿足多個條件的單元格數 ....198
招式183 COUNT——統計有銷售記錄的條數 ......................200
招式184 COUNTA——統計包含文本值的單元格數 ...............202
招式185 COUNTBLANK——統計空白單元格的個數 ........203
招式186 LARGE——統計數據表中前3 名的平均值 ...................204
招式187 SMALL——統計數據表中后3 名的平均值 ...................206
招式188 RANK.AVG——解決出現相同名次時默認的排名問題 ..................207
第9 章 信息函數 ...............209
招式189 CELL——獲取當前工作簿和工作表的名稱 ........210
招式190 ISTEXT——檢驗所給的數據是否為文本 ........... 211
招式191 ISBLANK——檢驗指定值是否為空 ...............212
招式192 ISNONTEXT——檢驗數據是否為非文本 ...................214
招式193 ISLOGICAL——判斷指定數據是否為邏輯值 ........214
招式194 ISNUMBER——判斷所給數據是否為數字 ...........215
招式195 ISNA——判斷值是否為“#N\A”錯誤值 ...................217
招式196 ISERR——統計公司各部門的人數 ......................217
招式197 ISODD——判斷數字是否為奇數 ....218
招式198 ISEVEN——判斷數字是否為偶數 ......................220
招式199 ISEVEN——根據身份證號判斷其性別 ......................220
招式200 ERROR.TYPE——返回錯誤值所對應錯誤類型的數字 ..................221
招式201 N——建立產品的訂單編碼 .............222
招式202 TYPE——返回數值對應的表示數據類型的數字 ....223
第10 章 財務函數 .............225
招式203 FV——計算分期存款的未來值 .......226
招式204 FV——計算住房公積金的未來值 ...226
招式205 RATE——計算購買某項保險的收益率 ...................227
招式206 PV——判斷某筆貸款是否合算 .......228
招式207 PMT——計算貸款的每年付款額 ....229
招式208 PPMT——計算貸款在給定期間內需償還的本金 ....229
招式209 IPMT——計算貸款在給定期間內需支付的利息 ...........230
招式210 ISPMT——計算普通( 無擔保) 貸款需償還的利息 ........232
招式211 FVSCHEDULE——計算某項借款的未來回收金額 ........232
招式212 NPV——計算某投資的凈現值 ........233
招式213 XNPV——計算出一組不定期盈利額的凈現值 ....234
招式214 NPER——計算出某項投資的期數 ......................235
招式215 IRR——計算某項投資的內部收益率 ...................236
招式216 XIRR——計算一組不定期發生現金流的內部收益率 ......................236
招式217 MIRR——計算正負現金流在不同利率下支付的內部收益率 ...............237
招式218 SLN——用直線法計算出固定資產的每年折舊額 ...........238
招式219 SYD——用年數總和法計算出固定資產的每年折舊額 .239
招式220 DB——用固定余額遞減法計算出固定資產的每年折舊額 ..................240
招式221 DDB——用雙倍余額遞減法計算出固定資產的每年折舊額 ..................241
招式222 VDB——計算出固定資產部分期間的設備折舊值 .242
招式223 AMORDEGRC——計算每個結算期間的折舊值 ........243
招式224 DOLLARDE——將按分數表示的價格轉換為按小數表示的價格 ........244
招式225 DOLLARFR——將按小數表示的價格轉換為按分數表示的價格 ........245
第11 章 其他函數 .............247
招式226 DELTA——測試兩個數值是否相等 ......................248
招式227 GESTEP——測試某值是否大于閾值 ......................248
招式228 BIN2OCT——將任意二進制編碼轉換為八進制編碼 .249
招式229 BIN2DEC——將任意二進制編碼轉換為十進制編碼 .250
招式230 OCT2BIN——將任意八進制編碼轉換為二進制編碼 .251
招式231 DEC2BIN——將任意十進制編碼轉換為二進制編碼 .252
招式232 BIN2HEX——將任意二進制編碼轉換為十六進制編碼 ......................252
招式233 ERF——返回誤差 .253
招式234 DPRODUCT——判斷指定產品是否被維修過 ........254
招式235 DSUM——返回列表或數據庫中滿足指定條件的記錄字段( 列) 中的數字之和 ......................255
招式236 DAVERAGE——計算指定車間指定性別員工的平均工資 ...............257
招式237 DCOUNT——統計出指定性別工資大于2500 元的人數 .......................258
第12 章 公式與函數常見問題 ....................259
招式238 VLOOKUP 函數查找時出現錯誤值的幾個原因 ...............260
招式239 完成合并計算后，原表數據更新后的結果不能同步更新 ...........261
招式240 新輸入的行中不能自動填充上一行的公式 ........262
招式241 在做日常報表時，怎樣才能讓月累計數自動加上 .......263
招式242 完成計算后，結果單元格中為什么始終顯示公式 ........264
招式243 “#####”錯誤及解決方法 ............264
招式244 “#DIV/0!”錯誤及解決方法 ..........265
招式245 “#N/A”錯誤及解決方法 ..............265
招式246 “#NAME?”錯誤及解決方法 ........266
招式247 “#NULL!”錯誤及解決方法 ..........268
招式248 “#NUM!”錯誤及解決方法 ...........268
招式249 “#REF!”錯誤及解決方法 ............269
招式250 “#VALUE!”錯誤及解決方法 ........269
