Excel函數(shù)與公式實(shí)戰(zhàn)秘技250招

目錄
第1 章　公式與函數(shù)基礎(chǔ) ......1

招式001 公式中的文本連接運(yùn)算符的使用 ........2
招式002　編輯與修改公式 .........3
招式003　快速復(fù)制公式的多種方法 ...................5
招式004　查看工作表中的所有公式 ...................6
招式005　函數(shù)也來玩“搜索” .7
招式006　快速獲取函數(shù)的幫助信息 ...................8
招式007　Excel 數(shù)據(jù)源的相對(duì)引用 ....................9
招式008　Excel 數(shù)據(jù)源的絕對(duì)引用 ....................9
招式009　在相對(duì)引用和絕對(duì)引用之間切換 ......10
招式010　引用當(dāng)前工作表之外的單元格 ..........12
招式011　跨工作簿引用數(shù)據(jù) ...13
招式012　引用多個(gè)工作表中的同一單元格
運(yùn)算 ........................15
招式013　公式中函數(shù)的記憶式鍵入 .................16
招式014　自動(dòng)求和功能的使用 ........................17
招式015　快速選中公式中引用的單元格 ..........18
招式016　將公式運(yùn)算結(jié)果轉(zhuǎn)換為數(shù)值 ..............19
招式017　函數(shù)參數(shù)的修改 .......20
招式018　函數(shù)的嵌套方法 .......21
招式019　如何在單元格中自動(dòng)填入當(dāng)前日期 ...22
招式020　隱藏保護(hù)工作表中的公式 .................22
招式021　正確區(qū)分空文本與空單元格 ..............24
招式022　為何數(shù)據(jù)無法進(jìn)行計(jì)算 .....................25
招式023　按F9 鍵查看公式運(yùn)算結(jié)果 ...............26
招式024　跳過非空單元格批量建立公式 ..........27
招式025　公式的監(jiān)視 ..............28
招式026　公式錯(cuò)誤檢查 ..........30
招式027　逐步分解公式 ..........31
招式028　普通公式與數(shù)組公式 ........................32
第2 章　名稱的定義與使用 ...33
招式029　定義名稱的規(guī)則 .......34
招式030　使用名稱的好處 .......34
招式031　使用“定義名稱”功能按鈕 ..............36
招式032　使用名稱框創(chuàng)建名稱 ........................36
招式033　根據(jù)所選內(nèi)容批量創(chuàng)建名稱 ..............37
招式034　名稱應(yīng)用于公式計(jì)算 ........................38
招式035　定義動(dòng)態(tài)引用的名稱 ........................39
招式036　使用名稱管理器篩選名稱 .................41
招式037　修改與編輯名稱 .......42
招式038　導(dǎo)出名稱列表 ..........43
招式039　刪除名稱 .................44
招式040　為Excel 公式定義名稱 .....................45
招式041　讓定義的名稱只應(yīng)用于當(dāng)前工作表 ...47

第3 章　文本函數(shù) .................49
招式042　CONCATENATE——快速自動(dòng)生成訂單編號(hào) .................50
招式043　CONCATENATE——合并面試人員的總分?jǐn)?shù)與錄取情況 .................50
招式044　FIND——搜索指定字符在字符串中的位置 .....................51
招式045　LEN——從員工身份證號(hào)碼中提取出生年份 .................52
招式046　LEN——從身份證號(hào)碼中提取性別 ...53
招式047　MID——從身份證號(hào)碼中提取完整的出生日期 .................54
招式048　LEFT——根據(jù)編碼返回部門名稱 .....55
招式049　LEFT——從完整地址中提取省份名 .....................56
招式050　LEFT——根據(jù)商品名稱實(shí)現(xiàn)一次性調(diào)價(jià) .............56
招式051　LEN——驗(yàn)證身份證號(hào)碼的位數(shù) .......57
招式052　TEXT——讓計(jì)算的金額顯示為“余款:12，250.00”的形式 ...........59
招式053　LEFT——從E-mail 地址中提取賬號(hào) ........................59
招式054　REPT——根據(jù)銷售額用“★”評(píng)定等級(jí) .................60
招式055　RIGHT——將電話號(hào)碼的區(qū)號(hào)與號(hào)碼隔開 .................61
招式056　EXACT——比較兩個(gè)門店的平均售價(jià)是否相同 ..........62
招式057　PROPER——一次性將每個(gè)單詞的首字母轉(zhuǎn)換為大寫 ...63
招式058　REPLACE——屏蔽手機(jī)號(hào)碼的后幾位數(shù) .................64
招式059　LEFT——快速生成對(duì)客戶的稱呼 .....65
招式060　ASC——將全角( 雙字節(jié)) 字符更改為半角( 單字節(jié)) 字符 ..............66
招式061　WIDECHAR——將半角( 單字節(jié))字符轉(zhuǎn)換為全角( 雙字節(jié)) 字符 .......66
招式062　BAHTTEXT——將數(shù)字轉(zhuǎn)換為β( 銖) 貨幣格式文本.......................67
招式063　DOLLAR——將數(shù)字轉(zhuǎn)換為帶美元符號(hào)$ 的文本 ..........68
招式064　RMB——將數(shù)字轉(zhuǎn)換為帶人民幣符號(hào)¥ 的文本 ..........69
招式065　CHAR——返回?cái)?shù)字對(duì)應(yīng)的字符代碼 .................70
招式066　CODE——返回字符代碼對(duì)應(yīng)的數(shù)字 ........................70
招式067　UPPER——將文本轉(zhuǎn)換為大寫字母 .................71
招式068　LOWER——將文本轉(zhuǎn)換為小寫字母 .................72
招式069　VALUE——將文本型數(shù)字轉(zhuǎn)換為可計(jì)算的數(shù)值數(shù)據(jù) ...73
招式070　TEXT——將“20111001”格式轉(zhuǎn)換為“2011-10-01”格式 .............74
招式071　TEXT——數(shù)據(jù)合并時(shí)避免0被省略 .....................75
招式072　SEARCH——從項(xiàng)目編碼中提取合同號(hào) .............76
招式073　TRIM——刪除文本中多余的空格 .....77
招式074　SUBSTITUTE——替換文本字符串中的特定文本 ........................78
招式075　SUBSTITUTE——刪除文本中的所有空格 .................79
招式076　CLEAN——刪除文本中所有無法打印的字符 .............80

第4 章　日期與時(shí)間函數(shù) .......81
招式077　DAYS360——計(jì)算總借款的天數(shù) .....82
招式078　DATEDIF——計(jì)算兩個(gè)日期相差的年月日數(shù) .................83
招式079　DATEDIF——為員工自動(dòng)追加工齡工資 .............84
招式080　DATEVALUE——計(jì)算借款的還款天數(shù) .................85
招式081　EDATE——計(jì)算食品的過期日期......86
招式082　DATE——確定明年父親節(jié)的日期 ....86
招式083　DAY——指定日期的上一月份的天數(shù) ........................87
招式084　DATEDIF——計(jì)算2010 年到2014 年共有多少天 .88
招式085　EOMONTH——返回員工結(jié)算工資的日期 .............89
招式086　HOUR——計(jì)算出員工缺勤的小時(shí)數(shù) .....................91
招式087　MINUTE——計(jì)算停車時(shí)間 ..............92
招式088　MONTH——自動(dòng)填寫報(bào)表中的月份 ........................93
招式089　NETWORKDAYS——計(jì)算年假占全年工作日的百分比 ....................94
招式090　NOW——返回當(dāng)前系統(tǒng)日期和時(shí)間 .............96
招式091　SECOND——計(jì)算通話秒數(shù) .............97
招式092　TIMEVALUE——將文本格式的時(shí)間轉(zhuǎn)換為序列數(shù) ...98
招式093　TIME——安排會(huì)議時(shí)間 ...................98
招式094　TODAY——返回系統(tǒng)當(dāng)前日期 .........99
招式095　TODAY——計(jì)算勞動(dòng)節(jié)倒計(jì)時(shí)的天數(shù) ......................100
招式096　WEEKDAY——自動(dòng)返回各日期所對(duì)應(yīng)的星期數(shù) ....101
招式097　WEEKDAY——匯總12 月份星期六的收入金額 .102
招式098　WEEKNUM——返回指定日期在一年中是第幾周 ....104
招式099　WORKDAY——根據(jù)休假天數(shù)返回休假結(jié)束日期 ........104
招式100　YEARFRAC——計(jì)算連續(xù)日期之間天數(shù)占全年天數(shù)的百分比 ...............105
招式101　YEAR——計(jì)算今天到年末時(shí)還有多少天 ...............106

第5 章　邏輯函數(shù) ...............109
招式102　AND——判斷員工的銷售額是否達(dá)標(biāo) ............... 110
招式103　FALSE——返回邏輯值FALSE ...... 111
招式104　IFERROR——根據(jù)公式結(jié)果返回不同內(nèi)容 ...................... 111
招式105　IF——根據(jù)銷售額計(jì)算提成額 ........ 112
招式106　IF——評(píng)定員工的考核等級(jí) ............ 113
招式107　IF——給持普通積分卡和VIP 卡的
客戶派發(fā)贈(zèng)品 ........ 114
招式108　IF——計(jì)算個(gè)人所得稅 ................... 115
招式109　NOT——篩選掉年齡在25歲以下的應(yīng)聘人員 ............... 117

第6 章　數(shù)學(xué)和三角函數(shù) .....123
招式110　OR——對(duì)員工的考核成績(jī)進(jìn)行綜合評(píng)定 ............... 118
招式111　OR——根據(jù)年齡判斷職工是否退休 ............... 118
招式112　OR——判斷未來年份是閏年還是平年 ............... 119
招式113　TRUE——判斷員工的身份證號(hào)碼位數(shù)是否正確 ........121
招式114　ABS——快速計(jì)算相應(yīng)數(shù)字的絕對(duì)值 ...................124
招式115　ABS——比較學(xué)生上月和本月的成績(jī)進(jìn)退情況 ........124
招式116　ACOS——求指定角度的反余弦值 ...............125
招式117　ASIN——求指定角度的反正弦值....126
招式118　ATAN——求指定角度的反正切值 ...126
招式119　CEILING——以5 秒( 不足5 秒按5 秒計(jì)算) 為單位計(jì)算通話費(fèi)用 ......................127
招式120　COS——求指定角度的余弦值 .......128
招式121　DEGREES——將弧度轉(zhuǎn)換為角度 ......................129
招式122　EXP——計(jì)算e 的n 次方 ...............129
招式123　FLOOR——對(duì)數(shù)據(jù)進(jìn)行向下舍取求值 ...............130
招式124　INT——對(duì)平均銷量取整 .................131
招式125　LN——計(jì)算某個(gè)數(shù)值的自然對(duì)數(shù) ...132
招式126　LOG10——計(jì)算指定正數(shù)值以10 為底數(shù)的對(duì)數(shù)值 ....................133
招式127　LOG——計(jì)算指定正數(shù)值和底數(shù)的對(duì)數(shù)值 ...................134
招式128　MOD——計(jì)算通話剩余秒數(shù) ..........134
招式129　MOD——按奇偶行匯總數(shù)據(jù) ..........135
招式130　PI——將指定角度轉(zhuǎn)換為弧度值 .....136
招式131　POWER——計(jì)算數(shù)值的乘冪 .........137
招式132　RADIANS——將角度轉(zhuǎn)換為弧度 ...137
招式133　RANDBETWEEN——隨機(jī)生成兩個(gè)指定數(shù)之間的整數(shù) ..................138
招式134　RAND——快速生成隨機(jī)數(shù)字 .........139
招式135　RAND——自動(dòng)生成彩票7 位開獎(jiǎng)號(hào)碼 ...............140
招式136　ROUNDDOWN——根據(jù)小數(shù)位數(shù)進(jìn)行向下舍入計(jì)算 .140
招式137　ROUNDUP——按小數(shù)位數(shù)進(jìn)行向上舍入計(jì)算 ........141
招式138　ROUNDOWN——界定時(shí)間的整點(diǎn)范圍 ...............142
招式139　ROUNDUP——計(jì)算物品的快遞費(fèi)用 ...............143
招式140　ROUND——對(duì)數(shù)據(jù)進(jìn)行四舍五入 ...............144
招式141　SIN——求指定角度的正弦值 .........145
招式142　SQRT——計(jì)算數(shù)值對(duì)應(yīng)的正平方根 ...............146
招式143　SUBTOTAL——在工資統(tǒng)計(jì)表中得出匯總方式的結(jié)果 ......................147
招式144　SUM——快速計(jì)算總銷售金額 .......149
招式145　SUM——統(tǒng)計(jì)營(yíng)銷部男員工人數(shù) ....149
招式146　SUMIF——統(tǒng)計(jì)各部門工資總額 ....150
招式147　SUMIF——統(tǒng)計(jì)10 月份前半個(gè)月與后半個(gè)月的銷售額 .152
招式148　SUMIF——計(jì)算同一類產(chǎn)品的總銷售額 ...............153
招式149　SUMIFS——按多個(gè)條件對(duì)指定
單元格求和 ...........154
招式150　SUMPRODUCT——統(tǒng)計(jì)出指定部門、指定職務(wù)的員工數(shù) .156
招式151　SUMSQ——計(jì)算所有參數(shù)的平方和 ...................157
招式152　TRUNC——計(jì)算銷售金額時(shí)取整或保留指定位數(shù)小數(shù) .158

第7 章　查找與引用函數(shù) .....161
招式153　HLOOKUP——根據(jù)不同的返利率計(jì)算各筆訂單的返利金額 ...............162
招式154　HLOOKUP——計(jì)算每位員工的銷售提成率 ...........164
招式155　LOOKUP——返回銷售筆數(shù)最多的員工姓名 ...............165
招式156　VLOOKUP——根據(jù)多條件計(jì)算員工年終獎(jiǎng) ...........167
招式157　VLOOKUP——查詢指定員工的銷售數(shù)據(jù) ...............168
招式158　INDEX——查詢總金額最高所對(duì)應(yīng)的門店 ........169
招式159　INDEX——查詢最高總分對(duì)應(yīng)的學(xué)號(hào) ......................171
招式160　CHOOSE——判斷員工考試成績(jī)是否合格 ...............172
招式161　MATCH——返回銷售額最高的員工姓名 ...............174
招式162　COLUMNS——返回?cái)?shù)據(jù)區(qū)域包含的列數(shù) ...........175
招式163　ADDRESS——返回指定行號(hào)和列號(hào)所對(duì)應(yīng)的單元格地址 ......................176
招式164　COLUMN——返回?cái)?shù)據(jù)區(qū)域首列的列號(hào) ......................177
招式165　ROW——填充一年中所有月的月份名 ...................178
招式166　ROWS——判斷值班人員是否重復(fù) ...............180
招式167　OFFSET——實(shí)現(xiàn)數(shù)據(jù)的動(dòng)態(tài)查詢 ......................181
招式168　HYPERLINK——為指定內(nèi)容創(chuàng)建超鏈接 ...................183
招式169　TRANSPOSE——數(shù)據(jù)區(qū)域的行列轉(zhuǎn)置 ......................184

第8 章　統(tǒng)計(jì)函數(shù) ...............185
招式170　AVERAGE——計(jì)算某件商品的平均售價(jià) ...............186
招式171　AVERAGEIFS——忽略0 值求指定部門的平均銷售額 .187
招式172　AVERAGEA——統(tǒng)計(jì)各月份的平均銷售額( 計(jì)算區(qū)域含文本值) .............188
招式173　AVERAGEIF——計(jì)算滿足條件的數(shù)據(jù)的平均值 ........189
招式174　MAX——計(jì)算單日銷售金額并返回最大值 ...................191
招式175　MAXA——返回?cái)?shù)據(jù)區(qū)域中非空值的最大值 ....192
招式176　MIN——返回上半個(gè)月單筆最低銷售金額 ...............192
招式177　MINA——返回?cái)?shù)據(jù)區(qū)域中非空值的最小值 ...................194
招式178　RANK.EQ——對(duì)員工銷售業(yè)績(jī)排名次 ...................194
招式179　COUNTIF——統(tǒng)計(jì)員工試用期到期的人數(shù) ...........195
招式180　COUNTIF——統(tǒng)計(jì)銷售額大于1000 的員工人數(shù) ...................196
招式181　COUNTIF——統(tǒng)計(jì)重復(fù)的員工人數(shù) ...............197
招式182　COUNTIFS——計(jì)算滿足多個(gè)條件的單元格數(shù) ....198
招式183　COUNT——統(tǒng)計(jì)有銷售記錄的條數(shù) ......................200
招式184　COUNTA——統(tǒng)計(jì)包含文本值的單元格數(shù) ...............202
招式185　COUNTBLANK——統(tǒng)計(jì)空白單元格的個(gè)數(shù) ........203
招式186　LARGE——統(tǒng)計(jì)數(shù)據(jù)表中前3 名的平均值 ...................204
招式187　SMALL——統(tǒng)計(jì)數(shù)據(jù)表中后3 名的平均值 ...................206
招式188　RANK.AVG——解決出現(xiàn)相同名次時(shí)默認(rèn)的排名問題 ..................207

第9 章　信息函數(shù) ...............209

招式189　CELL——獲取當(dāng)前工作簿和工作表的名稱 ........210
招式190　ISTEXT——檢驗(yàn)所給的數(shù)據(jù)是否為文本 ........... 211
招式191　ISBLANK——檢驗(yàn)指定值是否為空 ...............212
招式192　ISNONTEXT——檢驗(yàn)數(shù)據(jù)是否為非文本 ...................214
招式193　ISLOGICAL——判斷指定數(shù)據(jù)是否為邏輯值 ........214
招式194　ISNUMBER——判斷所給數(shù)據(jù)是否為數(shù)字 ...........215
招式195　ISNA——判斷值是否為“#N\A”錯(cuò)誤值 ...................217
招式196　ISERR——統(tǒng)計(jì)公司各部門的人數(shù) ......................217
招式197　ISODD——判斷數(shù)字是否為奇數(shù) ....218
招式198　ISEVEN——判斷數(shù)字是否為偶數(shù) ......................220
招式199　ISEVEN——根據(jù)身份證號(hào)判斷其性別 ......................220
招式200　ERROR.TYPE——返回錯(cuò)誤值所對(duì)應(yīng)錯(cuò)誤類型的數(shù)字 ..................221
招式201　N——建立產(chǎn)品的訂單編碼 .............222
招式202　TYPE——返回?cái)?shù)值對(duì)應(yīng)的表示數(shù)據(jù)類型的數(shù)字 ....223

第10 章　財(cái)務(wù)函數(shù) .............225
招式203　FV——計(jì)算分期存款的未來值 .......226
招式204　FV——計(jì)算住房公積金的未來值 ...226
招式205　RATE——計(jì)算購買某項(xiàng)保險(xiǎn)的收益率 ...................227
招式206　PV——判斷某筆貸款是否合算 .......228
招式207　PMT——計(jì)算貸款的每年付款額 ....229
招式208　PPMT——計(jì)算貸款在給定期間內(nèi)需償還的本金 ....229
招式209　IPMT——計(jì)算貸款在給定期間內(nèi)需支付的利息 ...........230
招式210　ISPMT——計(jì)算普通( 無擔(dān)保) 貸款需償還的利息 ........232
招式211　FVSCHEDULE——計(jì)算某項(xiàng)借款的未來回收金額 ........232
招式212　NPV——計(jì)算某投資的凈現(xiàn)值 ........233
招式213　XNPV——計(jì)算出一組不定期盈利額的凈現(xiàn)值 ....234
招式214　NPER——計(jì)算出某項(xiàng)投資的期數(shù) ......................235
招式215　IRR——計(jì)算某項(xiàng)投資的內(nèi)部收益率 ...................236
招式216　XIRR——計(jì)算一組不定期發(fā)生現(xiàn)金流的內(nèi)部收益率 ......................236
招式217　MIRR——計(jì)算正負(fù)現(xiàn)金流在不同利率下支付的內(nèi)部收益率 ...............237
招式218　SLN——用直線法計(jì)算出固定資產(chǎn)的每年折舊額 ...........238
招式219　SYD——用年數(shù)總和法計(jì)算出固定資產(chǎn)的每年折舊額 .239
招式220　DB——用固定余額遞減法計(jì)算出固定資產(chǎn)的每年折舊額 ..................240
招式221　DDB——用雙倍余額遞減法計(jì)算出固定資產(chǎn)的每年折舊額 ..................241
招式222　VDB——計(jì)算出固定資產(chǎn)部分期間的設(shè)備折舊值 .242
招式223　AMORDEGRC——計(jì)算每個(gè)結(jié)算期間的折舊值 ........243
招式224　DOLLARDE——將按分?jǐn)?shù)表示的價(jià)格轉(zhuǎn)換為按小數(shù)表示的價(jià)格 ........244
招式225　DOLLARFR——將按小數(shù)表示的價(jià)格轉(zhuǎn)換為按分?jǐn)?shù)表示的價(jià)格 ........245

第11 章　其他函數(shù) .............247
招式226　DELTA——測(cè)試兩個(gè)數(shù)值是否相等 ......................248
招式227　GESTEP——測(cè)試某值是否大于閾值 ......................248
招式228　BIN2OCT——將任意二進(jìn)制編碼轉(zhuǎn)換為八進(jìn)制編碼 .249
招式229　BIN2DEC——將任意二進(jìn)制編碼轉(zhuǎn)換為十進(jìn)制編碼 .250
招式230　OCT2BIN——將任意八進(jìn)制編碼轉(zhuǎn)換為二進(jìn)制編碼 .251
招式231　DEC2BIN——將任意十進(jìn)制編碼轉(zhuǎn)換為二進(jìn)制編碼 .252
招式232　BIN2HEX——將任意二進(jìn)制編碼轉(zhuǎn)換為十六進(jìn)制編碼 ......................252
招式233　ERF——返回誤差 .253
招式234　DPRODUCT——判斷指定產(chǎn)品是否被維修過 ........254
招式235　DSUM——返回列表或數(shù)據(jù)庫中滿足指定條件的記錄字段( 列) 中的數(shù)字之和 ......................255
招式236　DAVERAGE——計(jì)算指定車間指定性別員工的平均工資 ...............257
招式237　DCOUNT——統(tǒng)計(jì)出指定性別工資大于2500 元的人數(shù) .......................258

第12 章　公式與函數(shù)常見問題 ....................259
招式238　VLOOKUP 函數(shù)查找時(shí)出現(xiàn)錯(cuò)誤值的幾個(gè)原因 ...............260
招式239　完成合并計(jì)算后，原表數(shù)據(jù)更新后的結(jié)果不能同步更新 ...........261
招式240　新輸入的行中不能自動(dòng)填充上一行的公式 ........262
招式241　在做日常報(bào)表時(shí)，怎樣才能讓月累計(jì)數(shù)自動(dòng)加上 .......263
招式242　完成計(jì)算后，結(jié)果單元格中為什么始終顯示公式 ........264
招式243　“#####”錯(cuò)誤及解決方法 ............264
招式244　“#DIV/0!”錯(cuò)誤及解決方法 ..........265
招式245　“#N/A”錯(cuò)誤及解決方法 ..............265
招式246　“#NAME?”錯(cuò)誤及解決方法 ........266
招式247　“#NULL!”錯(cuò)誤及解決方法 ..........268
招式248　“#NUM!”錯(cuò)誤及解決方法 ...........268
招式249　“#REF!”錯(cuò)誤及解決方法 ............269
招式250　“#VALUE!”錯(cuò)誤及解決方法 ........269