Excel公式與函數(shù)大辭典

第1章　公式與函數(shù)基礎　1
1．1　了解公式　1
1．1．1　公式的組成　1
1．1．2　運算符及其優(yōu)先級　1
1．1．3　普通公式與數(shù)組公式　3
1．1．4　單個單元格公式與多個單元格公式　3
1．2　輸入與編輯公式　3
1．2．1　A1引用與R1C1引用　4
1．2．2　輸入公式　5
1．2．3　修改公式　6
1．2．4　移動和復制公式　6
1．2．5　改變公式的計算方式　8
1．3　在公式中使用函數(shù)　9
1．3．1　為什么使用函數(shù)　9
1．3．2　函數(shù)的類型　9
1．3．3　函數(shù)的參數(shù)　11
1．3．4　在公式中輸入函數(shù)　11
1．4　在公式中使用名稱　13
1．4．1　名稱的作用范圍　14
1．4．2　命名區(qū)域　14
1．4．3　命名公式　16
1．4．4　命名常量　16
1．4．5　將名稱應用到公式中　16
1．5　使用數(shù)組公式　18
1．5．1　單個單元格數(shù)組公式與多個單元格數(shù)組公式　18
1．5．2　數(shù)組的維數(shù)　19
1．5．3　輸入數(shù)組公式　20
1．5．4　修改數(shù)組公式　21
1．5．5　擴展或縮小多單元格數(shù)組公式　21
1．5．6　選擇數(shù)組公式所在區(qū)域　22
1．5．7　使用常量數(shù)組　22
1．6　使用外部公式　23
1．6．1　創(chuàng)建引用其他工作表中數(shù)據(jù)的公式　24
1．6．2　創(chuàng)建引用其他工作簿中數(shù)據(jù)的公式　24
1．6．3　更新外部公式　25
1．7　審核公式　25
1．7．1　使用公式錯誤檢查器　26
1．7．2　定位特定類型的數(shù)據(jù)　26
1．7．3　跟蹤單元格關系　27
1．7．4　監(jiān)視單元格內(nèi)容　28
1．7．5　使用公式求值器　29
1．8　處理公式中的錯誤　30
1．8．1　括號不匹配　30
1．8．2　以#號填充單元格　30
1．8．3　空白但非空的單元格　30
1．8．4　顯示值與實際值　31
1．8．5　返回錯誤值　31
1．8．6　循環(huán)引用　34
1．9　公式使用技巧　34
1．9．1　在多個單元格中輸入同一個公式　35
1．9．2　顯示公式而不是值　35
1．9．3　查看公式的中間結果　35
1．9．4　將公式轉換為值　36
1．9．5　復制公式但不使用相對引用　36
1．9．6　隱藏公式　37
1．9．7　禁止任何人修改公式　38
第2章　數(shù)學和三角函數(shù)　39
2．1　常規(guī)計算　42
2．1．1　SIGN——返回數(shù)字的符號　42
案例01　檢測商品銷量是否達標　43
2．1．2　ABS——計算數(shù)字的絕對值　43
案例02　計算兩地溫差　44
2．1．3　SUM——計算數(shù)字之和　44
案例03　匯總兩種商品的銷售額　44
案例04　匯總大于15000的銷售總額　45
案例05　統(tǒng)計銷售部女員工人數(shù)　45
2．1．4　PRODUCT——計算數(shù)字之積　46
案例06　計算每種商品打折后的價格(PRODUCT+ROUND)　46
2．1．5　SQRT——計算正平方根　47
案例07　計算正方形邊長　47
2．1．6　MOD——返回商的余數(shù)　47
案例08　判斷閏年(MOD+IF+OR+AND)　48
案例09　分別匯總奇數(shù)月和偶數(shù)月的銷量(MOD+SUM+IF+ROW)　48
2．1．7　QUOTIENT——返回商的整數(shù)部分　49
案例10　統(tǒng)計在預算內(nèi)可購買的辦公用品數(shù)量　50
2．1．8　GCD——計算最大公約數(shù)　50
案例11　計算最大公約數(shù)　50
2．1．9　LCM——計算最小公倍數(shù)　51
案例12　計算最小公倍數(shù)　51
2．1．10　SUMIF——按給定條件對指定單元格求和　52
案例13　計算某部門員工年薪總和　52
案例14　計算前兩名和后兩名員工的銷售額總和(SUMIF+LARGE+SMALL)　53
2．1．11　SUMIFS——按多個條件對指定單元格求和　53
案例15　匯總指定銷售額范圍內(nèi)的銷售總額　54
2．1．12　SUMPRODUCT——計算數(shù)組元素的乘積之和　54
案例16　計算商品打折后的總價格(SUMPRODUCT+ROUND)　55
案例17　統(tǒng)計銷售部女員工人數(shù)　55
2．1．13　SUMSQ——計算參數(shù)的平方和　56
案例18　計算直角三角形斜邊的長度(SUMSQ+SQRT)　56
2．1．14　SUMXMY2——計算數(shù)組對應值之差的平方和　56
案例19　計算數(shù)組對應值之差的平方和　57
2．1．15　SUMX2MY2——計算數(shù)組對應值的平方差之和　57
案例20　計算數(shù)組對應值的平方差之和　58
2．1．16　SUMX2PY2——計算數(shù)組對應值的平方和之和　58
案例21　計算數(shù)組對應值的平方和之和　58
2．1．17　SERIESSUM——計算基于公式的冪級數(shù)之和　59
案例22　計算自然對數(shù)的底數(shù)e的近似值　59
2．2　舍入計算　60
2．2．1　INT——返回永遠小于原數(shù)字的最接近的整數(shù)　60
案例23　匯總整數(shù)金額(INT+SUM)　60
2．2．2　TRUNC——返回數(shù)字的整數(shù)部分　61
案例24　匯總金額只保留一位小數(shù)(TRUNC+SUM)　61
2．2．3　ROUND——按指定位數(shù)對數(shù)字進行四舍五入　62
案例25　將金額取整舍入到百位(ROUND+SUM)　62
2．2．4　ROUNDDOWN——以絕對值減小的方向按指定位數(shù)舍入數(shù)字　63
案例26　匯總金額，忽略分位(ROUNDDOWN+SUM)　64
2．2．5　ROUNDUP——以絕對值增大的方向按指定位數(shù)舍入數(shù)字　64
案例27　計算上網(wǎng)費用(ROUNDUP+HOUR+MINUTE)　65
2．2．6　MROUND——舍入到指定倍數(shù)的數(shù)字　66
案例28　計算商品運送車次　67
2．2．7　CEILING——以絕對值增大的方向按指定倍數(shù)舍入　67
案例29　計算上網(wǎng)費用(CEILING+HOUR+MINUTE)　68
2．2．8　CEILING．PRECISE——以算數(shù)值增大的方向按指定倍數(shù)舍入　68
2．2．9　CEILING．MATH——以絕對值或算數(shù)值增大的方向按指定倍數(shù)舍入　69
2．2．10　FLOOR——以絕對值減小的方向按指定倍數(shù)舍入　70
案例30　計算員工提成獎金　71
2．2．11　FLOOR．PRECISE——以算數(shù)值減小的方向按指定倍數(shù)舍入　72
2．2．12　FLOOR．MATH——以絕對值或算數(shù)值減小的方向按指定倍數(shù)舍入　72
2．2．13　EVEN——以絕對值增大的方向舍入到最接近偶數(shù)　74
案例31　隨機抽獎(男員工)(EVEN+INDEX+RANDBETWEEN)　74
2．2．14　ODD——以絕對值增大的方向舍入到最接近奇數(shù)　75
案例32　隨機抽獎(女員工)(ODD+INDEX+RANDBETWEEN)　76
2．3　指數(shù)與對數(shù)計算　76
2．3．1　POWER——計算數(shù)字的乘冪　76
案例33　計算區(qū)域中各數(shù)字的倒數(shù)之和(POWER+SUM)　77
2．3．2　EXP——計算e的n次方　77
案例34　計算自然對數(shù)的底數(shù)e的n次冪　78
2．3．3　LN——計算自然對數(shù)　78
案例35　計算數(shù)值的自然對數(shù)　78
2．3．4　LOG——計算以指定數(shù)字為底的對數(shù)　78
案例36　計算以指定數(shù)字為底的對數(shù)　79
2．3．5　LOG10——計算以10為底的對數(shù)　79
案例37　計算以10為底的對數(shù)　79
2．4　階乘、矩陣與隨機數(shù)　80
2．4．1　COMBIN——計算給定數(shù)目對象的組合數(shù)　80
案例38　計算公司知識競賽對局次數(shù)　80
2．4．2　COMBINA——計算給定數(shù)目對象具有重復項的組合數(shù)　81
2．4．3　FACT——計算數(shù)字的階乘　81
案例39　計算10到20之間的數(shù)字階乘　82
2．4．4　FACTDOUBLE——計算數(shù)字的雙倍階乘　82
案例40　計算1到36之間偶數(shù)的乘積　82
2．4．5　MULTINOMIAL——計算多個數(shù)字和的階乘與各數(shù)字階乘乘積的比值　83
案例41　計算人員分組問題　83
2．4．6　MDETERM——計算數(shù)組的矩陣行列式的值　83
案例42　求解二元聯(lián)立方程組　84
2．4．7　MINVERSE——計算數(shù)組的逆矩陣　84
案例43　求解多元聯(lián)立方程組(MINVERSE+MMULT)　85
2．4．8　MMULT——計算兩個數(shù)組的矩陣乘積　85
案例44　計算商品在不同單價下的銷售額　85
案例45　計算某商品的最大銷量(MMULT+MAX+N+TRANSPOSE)　86
2．4．9　MUNIT——返回指定維度的單位矩陣　86
2．4．10　RAND——返回0到1之間的一個隨機數(shù)　87
案例46　隨機顯示A到Z之間的大寫字母(RAND+CHAR+INT)　87
2．4．11　RANDBETWEEN——返回某個范圍內(nèi)的隨機數(shù)　88
案例47　生成1到50之間的隨機偶數(shù)　88
2．5　三角函數(shù)計算　89
2．5．1　DEGREES——將弧度轉換為角度　89
案例48　根據(jù)弧長和半徑計算角度(DEGREES+ROUND)　89
2．5．2　RADIANS——將角度轉換為弧度　89
案例49　根據(jù)角度和半徑計算弧長(RADIANS+ROUND)　90
2．5．3　SIN——計算給定角度的正弦值　90
案例50　計算給定角度的正弦值(SIN+ROUND)　90
2．5．4　ASIN——計算數(shù)字的反正弦值　91
案例51　計算數(shù)字的反正弦值(ASIN+ROUND)　91
2．5．5　SINH——計算數(shù)字的雙曲正弦值　91
案例52　計算數(shù)字的雙曲正弦值(SINH+ROUND)　92
2．5．6　ASINH——計算數(shù)字的反雙曲正弦值　92
案例53　計算數(shù)字的反雙曲正弦值(ASINH+ROUND)　92
2．5．7　COS——計算給定角度的余弦值　93
案例54　計算給定角度的余弦值(COS+ROUND)　93
2．5．8　ACOS——計算數(shù)字的反余弦值　93
案例55　計算數(shù)字的反余弦值(ACOS+ROUND)　94
2．5．9　COSH——計算數(shù)字的雙曲余弦值　94
案例56　計算數(shù)字的雙曲余弦值(COSH+ROUND)　94
2．5．10　ACOSH——計算數(shù)字的反雙曲余弦值　95
案例57　計算數(shù)字的反雙曲余弦值(ACOSH+ROUND)　95
2．5．11　TAN——計算給定角度的正切值　95
案例58　計算給定角度的正切值(TAN+ROUND)　96
2．5．12　ATAN——計算數(shù)字的反正切值　96
案例59　計算數(shù)字的反正切值(ATAN+ROUND)　96
2．5．13　TANH——計算數(shù)字的雙曲正切值　97
案例60　計算數(shù)字的雙曲正切值(TANH+ROUND)　97
2．5．14　ATANH——計算數(shù)字的反雙曲正切值　97
案例61　計算數(shù)字的反雙曲正切值(ATANH+ROUND)　98
2．5．15　ATAN2——計算給定坐標的反正切值　98
案例62　計算給定坐標的反正切值　99
2．5．16　ACOT——計算數(shù)字的反余切值　99
案例63　計算數(shù)字的反余切值(ACOT+ROUND)　99
2．5．17　ACOTH——計算數(shù)字的反雙曲余切值　100
案例64　計算數(shù)字的反雙曲余切值(ACOTH+ROUND)　100
2．5．18　COT——計算給定角度的余切值　100
案例65　計算給定角度的余切值　101
2．5．19　COTH——計算數(shù)字的雙曲余切值　101
案例66　計算數(shù)字的雙曲余切值(COTH+ROUND)　102
2．5．20　SEC——計算給定角度的正割值　102
案例67　計算給定角度的正割值(SEC+ROUND)　102
2．5．21　SECH——計算給定角度的雙曲正割值　103
案例68　計算給定角度的雙曲正割值(SECH+ROUND)　103
2．5．22　CSC——計算給定角度的余割值　103
案例69　計算給定角度的余割值(CSC+ROUND)　104
2．5．23　CSCH——計算給定角度的雙曲余割值　104
案例70　計算給定角度的雙曲余割值(CSCH+ROUND)　105
2．6　其他計算　105
2．6．1　PI——返回pi的值　105
案例71　計算圓周長(PI+ROUND)　105
2．6．2　SQRTPI——計算某數(shù)與pi的乘積的平方根　105
案例72　計算圓周率倍數(shù)的平方根　106
2．6．3　SUBTOTAL——返回指定區(qū)域的分類匯總結果　106
案例73　匯總某部門員工工資情況(一)　108
案例74　設置不間斷序號　108
2．6．4　AGGREGATE——返回指定區(qū)域的分類匯總結果　108
案例75　匯總某部門員工工資情況(二)　110
案例76　統(tǒng)計某部門年薪排在第2位的員工姓名(AGGREGATE+INDEX+MATCH)　111
2．6．5　ROMAN——將阿拉伯數(shù)字轉為文本型羅馬數(shù)字　112
案例77　將人員編號轉換為羅馬數(shù)字格式(ROMAN+CHOOSE)　113
2．6．6　ARABIC——將羅馬數(shù)字轉換為阿拉伯數(shù)字　113
案例78　將人員編號轉換為阿拉伯數(shù)字格式(ARABIC+CHOOSE)　114
2．6．7　BASE——將一個數(shù)轉換為給定基數(shù)的文本格式　114
案例79　以給定基數(shù)轉換數(shù)字　115
2．6．8　DECIMAL——將給定基數(shù)的文本轉換為十進制數(shù)　115
案例80　將給定基數(shù)的文本轉換為十進制數(shù)　116
第3章　日期和時間函數(shù)　117
3．1　了解Excel日期系統(tǒng)　118
3．1．1　Excel提供的兩種日期系統(tǒng)　118
3．1．2　了解日期和時間序列號　120
3．1．3　輸入與設置日期和時間　120
3．1．4　兩位數(shù)年份問題　121
3．2　返回當前的日期、時間和指定的日期、時間　122
3．2．1　NOW——返回當前日期和時間　122
案例81　統(tǒng)計員工在職時間(NOW+ROUND+IF)　122
案例82　元旦倒計時(NOW+TEXT)　123
3．2．2　TODAY——返回當前日期　124
案例83　統(tǒng)計試用期到期的人數(shù)(TODAY+COUNTIF)　124
3．2．3　DATE——返回指定日期的序列號　125
案例84　計算2009年星期六的個數(shù)(DATE+SUM+N+TEXT+ROW+INDIRECT)　125
案例85　計算本月的天數(shù)(DATE+TEXT+YEAR+MONTH+TODAY)　126
3．2．4　TIME——返回指定時間的序列號　126
案例86　安排會議時間(TIME+TEXT+NOW)　127
3．3　返回日期和時間的某個部分　127
3．3．1　YEAR——返回年份　127
案例87　計算2000年以后的員工平均工資(YEAR+ROUND+AVERAGE+IF)　128
3．3．2　MONTH——返回月份　128
案例88　計算本月需要結算的金額(MONTH+TODAY+SUM+IF)　129
案例89　判斷閏年(MONTH+IF+DATE+YEAR)　129
3．3．3　DAY——返回日期中具體的某一天　130
案例90　計算本月下旬需要結算的金額(DAY+SUM+IF)　130
3．3．4　WEEKDAY——返回當前日期是星期幾　131
案例91　統(tǒng)計周五的商品銷量(WEEKDAY+SUM+IF)　132
3．3．5　HOUR——返回小時數(shù)　132
案例92　計算用餐時間　133
3．3．6　MINUTE——返回分鐘數(shù)　133
案例93　計算用餐的精確時間(HOUR+MINUTE)　133
3．3．7　SECOND——返回秒數(shù)　134
案例94　計算廣告時長　134
3．4　文本與日期、時間格式間的轉換　135
3．4．1　DATEVALUE——將文本格式的日期轉換為序列號　135
案例95　計算兩個月之間相差的天數(shù)　135
3．4．2　TIMEVALUE——將文本格式的時間轉換為序列號　135
案例96　計算加班費用(TIMEVALUE+SUBSTITUTE+ROUND)　136
3．5　其他日期函數(shù)　136
3．5．1　DATEDIF——計算開始和結束日期之間的時間間隔　136
案例97　統(tǒng)計辦公用品使用情況(DATEDIF+TODAY)　137
3．5．2　DAYS360——以360天為準計算兩個日期間天數(shù)　138
案例98　計算還款天數(shù)(一)　138
3．5．3　DAYS——計算兩個日期之間的天數(shù)　139
案例99　計算還款天數(shù)(二)　139
3．5．4　EDATE——計算從指定日期向前或向后幾個月的日期　139
案例100　計算還款日期(EDATE+TEXT+LEFT+LEN)　140
3．5．5　DATESTRING——將指定日期的序列號轉換為文本日期　141
案例101　計算還款日期(DATESTRING+EDATE+LEFT+LEN)　141
3．5．6　EOMONTH——計算從指定日期向前或向后幾個月后的那個月最后一天的日期　142
案例102　統(tǒng)計員工的工資結算日期(EOMONTH+TEXT)　142
3．5．7　NETWORKDAYS——計算日期間的所有工作日數(shù)　143
案例103　計算工程施工的天數(shù)(一)　143
3．5．8　NETWORKDAYS．INTL——計算日期間的所有工作日數(shù)，使用參數(shù)指明周末的日期和天數(shù)　144
案例104　計算工程施工的天數(shù)(二)　145
3．5．9　WEEKNUM——返回日期在一年中是第幾周　146
案例105　計算本月包含的周數(shù)(WEEKNUM+EOMONTH+NOW)　147
3．5．10　ISOWEEKNUM——返回日期在一年中的ISO周數(shù)　147
3．5．11　WORKDAY——計算與指定日期相隔數(shù)個工作日的日期　148
案例106　安排施工時間(一)(WORKDAY+TEXT+LEFT+LEN)　148
3．5．12　WORKDAY．INTL——計算與指定日期相隔數(shù)個工作日的日期，使用參數(shù)指明周末的日期和天數(shù)　149
案例107　安排施工時間(二)(WORKDAY．INTL+TEXT+LEFT+LEN)　151
3．5．13　YEARFRAC——計算從開始日期到結束日期所經(jīng)歷的天數(shù)占全年天數(shù)的百分比　151
案例108　計算全年盈利額(YEARFRAC+ROUND)　152
第4章　邏輯函數(shù)　153
4．1　邏輯值函數(shù)　153
4．1．1　TRUE——返回邏輯值TRUE　153
案例109　判斷兩列數(shù)據(jù)是否相同　154
4．1．2　FALSE——返回邏輯值FALSE　154
案例110　計算兩列中相同數(shù)據(jù)的個數(shù)　154
4．2　條件判斷函數(shù)　155
4．2．1　NOT——對邏輯值求反　155
案例111　計算區(qū)域內(nèi)數(shù)字的個數(shù)(NOT+SUM+ISERROR)　155
4．2．2　AND——判斷多個條件是否同時成立　156
案例112　判斷面試人員是否被錄取　156
案例113　判斷是否為員工發(fā)放獎金　157
4．2．3　OR——判斷多個條件中是否至少有一個條件成立　157
案例114　判斷身份證號碼的長度是否正確(OR+LEN)　158
4．2．4　XOR——判斷多個條件中是否有一個條件成立　158
4．2．5　IF——根據(jù)條件判斷而返回不同結果　159
案例115　評定員工業(yè)績　159
案例116　計算需要發(fā)放獎金的總額(IF+SUM)　160
4．2．6　IFNA——判斷公式是否出現(xiàn)“#N/A”錯誤　160
案例117　根據(jù)姓名查找員工所在部門并排錯(IFNA+VLOOKUP)　161
4．2．7　IFERROR——根據(jù)公式結果返回不同內(nèi)容　161
案例118　根據(jù)編號查找員工信息并排錯(IFERROR+VLOOKUP)　162
第5章　文本函數(shù)　163
5．1　返回字符或字符編碼　165
5．1．1　CHAR——返回與ANSI字符編碼對應的字符　165
案例119　在區(qū)域中自動生成大寫字母列表(CHAR+ROW)　165
5．1．2　UNICHAR——返回與Unicode字符編碼對應的字符　166
5．1．3　CODE——返回與字符對應的ANSI字符編碼　166
案例120　判斷單元格第一個字符是否為字母(CODE+IF+OR+AND)　166
案例121　按升序排列順序錯亂的字母列表(CODE+CHAR+SMALL+ROW)　167
5．1．4　UNICODE——返回與字符對應的Unicode字符編碼　167
5．2　返回文本內(nèi)容　168
5．2．1　LEFT——從文本左側起提取指定個數(shù)的字符　168
案例122　提取地址中的省市名稱(LEFT+FIND)　168
案例123　統(tǒng)計各地區(qū)參會人數(shù)(LEFT+SUM)　169
5．2．2　LEFTB——從文本左側起提取指定字節(jié)數(shù)字符　170
5．2．3　LEN——計算文本中的字符個數(shù)　170
案例124　計算單元格中的數(shù)字個數(shù)(LEN+LENB)　170
案例125　根據(jù)身份證號判斷性別(LEN+IF+MOD+RIGHT+MID)　171
5．2．4　LENB——計算文本中代表字符的字節(jié)數(shù)　172
5．2．5　MID——從文本指定位置起提取指定個數(shù)的字符　172
案例126　提取公司獲獎人員姓名(MID+FIND+LEN)　172
案例127　從身份證號中提取生日(MID+TEXT+IF+LEN)　173
5．2．6　MIDB——從文本指定位置起提取指定字節(jié)數(shù)的字符　173
5．2．7　RIGHT——從文本右側起提取指定個數(shù)的字符　174
案例128　動態(tài)提取公司名稱列表(RIGHT+LEN+FIND)　174
5．2．8　RIGHTB——從文本右側起提取指定字節(jié)數(shù)字符　175
5．2．9　REPT——生成重復的字符　175
案例129　自動為數(shù)字添加星號(REPT+LEN)　176
案例130　制作簡易的銷售圖表　177
5．3　合并文本　177
5．3．1　CONCATENATE——將多個文本合并到一處　177
案例131　評定員工考核成績(CONCATENATE+IF+SUM)　177
5．4　轉換文本格式　178
5．4．1　ASC——將全角字符轉換為半角字符　178
案例132　計算全、半角混合文本中的字母個數(shù)(ASC+LEN+LENB)　178
5．4．2　WIDECHAR——將半角字符轉換為全角字符　179
案例133　計算全、半角混合文本中的漢字個數(shù)(WIDECHAR+LEN+LENB+ASC)　179
5．4．3　PHONETIC——返回文本中的拼音字符　180
5．4．4　BAHTTEXT——將數(shù)字轉換為泰語文本　180
5．4．5　DOLLAR——將數(shù)字轉換為帶美元符號＄的文本　180
案例134　出口商品價格轉換　180
5．4．6　RMB——將數(shù)字轉換為帶人民幣符號￥的文本　181
案例135　將數(shù)字轉換為帶人民幣符號￥的文本　181
5．4．7　NUMBERSTRING——將數(shù)值轉換為大寫漢字　182
案例136　將銷售額轉換為中文大寫漢字　182
5．4．8　NUMBERVALUE——以與區(qū)域設置關的方式將文本轉換為數(shù)字　183
5．4．9　T——將參數(shù)轉換為文本　183
案例137　為公式添加文字說明(RMB+T+N)　184
5．4．10　LOWER——將文本轉換為小寫　184
5．4．11　UPPER——將文本轉換為大寫　184
案例138　將文本轉換為句首字母大寫其他小寫(UPPER+LEFT+LOWER+RIGHT+LEN)　185
5．4．12　PROPER——將文本中每個單詞的首字母轉換為大寫　185
案例139　整理人名數(shù)據(jù)　186
5．4．13　VALUE—將文本格式的數(shù)字轉換為普通數(shù)字　186
案例140　對文本格式的銷售數(shù)據(jù)求和(VALUE+SUM)　186
5．4．14　TEXT——多樣化格式設置函數(shù)　187
案例141　將金額顯示為特定的單位(TEXT+SUM)　187
案例142　將普通數(shù)字轉換為電話格式　187
案例143　自動生成12個月的英文月份名稱(TEXT+ROW)　188
5．4．15　FIXED——將數(shù)字按指定的小數(shù)位數(shù)取整　188
案例144　格式化數(shù)字格式　189
5．5　查找與替換文本　189
5．5．1　EXACT——比較兩個文本是否相同　189
案例145　核對數(shù)據(jù)錄入是否正確(EXACT+IF)　189
5．5．2　FIND——以字符為單位并區(qū)分大小寫地查找指定字符的位置　190
案例146　檢查聯(lián)系地址是否詳細(FIND+ISERROR+IF)　190
案例147　提取公司名稱(FIND+MID)　191
5．5．3　FINDB——以字節(jié)為單位并區(qū)分大小寫地查找指定字符的位置　191
5．5．4　REPLACE——以字符為單位根據(jù)指定位置進行替換　192
案例148　電話號碼位數(shù)升級　192
案例149　提取地區(qū)公司名稱(REPLACE+LEFT+FIND)　193
5．5．5　REPLACEB——以字節(jié)為單位根據(jù)指定位置進行替換　193
5．5．6　SEARCH——以字符為單位不區(qū)分大小寫地查找指定字符的位置　194
案例150　將銷售圖書分類上架(SEARCH+IF+COUNT)　194
5．5．7　SEARCHB——以字節(jié)為單位不區(qū)分大小寫地查找指定字符的位置　195
5．5．8　SUBSTITUTE——以指定文本進行替換　196
案例151　將日期轉換為標準格式　196
案例152　格式化公司名稱(SUBSTITUTE+REPLACE)　197
5．6　刪除文本中的字符　197
5．6．1　CLEAN——刪除法打印的字符　197
案例153　對表格內(nèi)容排版　197
5．6．2　TRIM——刪除多余的空格　198
案例154　整理不規(guī)則數(shù)據(jù)(TRIM+CLEAN)　198
第6章　查找和引用函數(shù)　199
6．1　查找表中數(shù)據(jù)　200
6．1．1　CHOOSE——根據(jù)序號從列表中選擇對應的內(nèi)容　200
案例155　評定員工業(yè)績(CHOOSE+IF)　201
案例156　制作購物打折收費系統(tǒng)　202
案例157　重組銷售數(shù)據(jù)　204
案例158　在多組銷售數(shù)據(jù)中查找銷量(CHOOSE+VLOOKUP+MATCH)　205
6．1．2　LOOKUP——僅在單行單列中查找(向量形式)　206
案例159　根據(jù)姓名查找員工編號　207
案例160　使用LOOKUP函數(shù)進行多條件判斷　207
案例161　提取文本中的金額(LOOKUP+LEFT+ROW+INDIRECT+LEN)　208
6．1．3　LOOKUP——僅在單行單列中查找(數(shù)組形式)　208
案例162　查找員工信息　209
6．1．4　HLOOKUP——在區(qū)域或數(shù)組的行中查找數(shù)據(jù)　209
案例163　查找商品在某季度的銷量(HLOOKUP+MATCH)　211
6．1．5　VLOOKUP——在區(qū)域或數(shù)組的列中查找數(shù)據(jù)　211
案例164　根據(jù)商品名稱查找銷量　212
案例165　根據(jù)銷量對員工進行評定　213
案例166　從多個表中計算員工的年終獎(VLOOKUP+IF)　213
案例167　逆向查找(VLOOKUP+IF)　214
6．1．6　MATCH——返回指定內(nèi)容所在的位置　215
案例168　不區(qū)分大小寫查找數(shù)據(jù)(MATCH+INDEX)　216
案例169　區(qū)分大小寫查找數(shù)據(jù)(MATCH+INDEX+EXACT)　216
案例170　雙重定位查找數(shù)據(jù)(MATCH+INDEX)　217
案例171　提取某商品最后一次進貨日期(MATCH+TEXT+INDIRECT)　218
案例172　從多列數(shù)據(jù)中查找員工工資(MATCH+INDEX)　218
6．1．7　INDEX——返回指定位置中的內(nèi)容(數(shù)組形式)　219
案例173　查找左側列中的數(shù)據(jù)(INDEX+MATCH)　220
6．1．8　INDEX——返回指定位置中的內(nèi)容(引用形式)　220
案例174　提取某區(qū)域的銷售數(shù)據(jù)(INDEX+MATCH)　221
6．2　引用表中數(shù)據(jù)　221
6．2．1　ADDRESS——返回與指定行號和列號對應的單元格地址　221
案例175　確定最大銷售額的位置(ADDRESS+MAX+IF+ROW)　222
案例176　返回當前單元格的列字母(ADDRESS+COLUMN+SUBSTITUTE)　223
案例177　跨工作表返回匯總金額(ADDRESS+INDIRECT)　223
6．2．2　AREAS——返回引用中包含的區(qū)域數(shù)量　225
案例178　統(tǒng)計分公司數(shù)量　225
6．2．3　COLUMN——返回單元格或單元格區(qū)域首列的列號　225
案例179　在一行中快速輸入月份(COLUMN+TEXT)　226
案例180　匯總多個列中的銷量(COLUMN+SUM+IF+MOD)　226
6．2．4　COLUMNS——返回數(shù)據(jù)區(qū)域包含的列數(shù)　227
案例181　計算需要扣款的項目數(shù)量　227
6．2．5　ROW——返回單元格或單元格區(qū)域首行的行號　227
案例182　在一列中快速輸入月份(ROW+TEXT)　228
案例183　提取最后一次銷量大于800的日期(ROW+TEXT+INDEX+MAX)　228
案例184　計算員工的最高工資(ROW+MAX+MMULT)　229
6．2．6　ROWS——返回數(shù)據(jù)區(qū)域包含的行數(shù)　230
案例185　計算需要開支的員工人數(shù)　230
案例186　計算銷售數(shù)據(jù)中的報價數(shù)量(ROWS+COLUMNS)　231
案例187　檢驗日期是否重復(ROWS+IF+MATCH)　231
6．2．7　OFFSET——根據(jù)給定的偏移量返回新的引用區(qū)域　232
案例188　使用OFFSET函數(shù)查詢員工信息(OFFSET+MATCH)　233
案例189　對每日銷量累積求和(OFFSET+SUM+ROW)　234
案例190　匯總最近5天的銷量(OFFSET+SUBTOTAL+INDIRECT+MAX+ROW)　234
案例191　制作工資條(OFFSET+IF+MOD+ROW+ROUND+COLUMN)　235
6．2．8　TRANSPOSE——轉置數(shù)據(jù)區(qū)域的行列位置　237
案例192　轉換銷售數(shù)據(jù)區(qū)域　237
6．2．9　INDIRECT——返回由文本值指定的引用　238
案例193　統(tǒng)計銷量小于600的員工數(shù)(INDIRECT+SUM+COUNTIF)　239
案例194　提取多個工作表中的最大值(INDIRECT+SUBTOTAL)　239
6．2．10　FORMULATEXT——返回給定引用公式的文本形式　240
案例195　提取單元格中的公式(一)　241
6．2．11　GETPIVOTDATA——返回數(shù)據(jù)透視表中的數(shù)據(jù)　241
案例196　使用函數(shù)從數(shù)據(jù)透視表中提取數(shù)據(jù)　242
6．2．12　HYPERLINK——為指定內(nèi)容創(chuàng)建超鏈接　243
案例197　在銷售數(shù)據(jù)表中指定公司郵件地址　243
案例198　實現(xiàn)自動跳轉到數(shù)據(jù)結尾的功能(HYPERLINK+OFFSET+COUNTA)　244
6．2．13　RTD——返回支持COM自動化程序的實時數(shù)據(jù)　245
案例199　使用COM加載宏快速顯示時間　246
第7章　信息函數(shù)　247
7．1　返回信息　248
7．1．1　CELL——返回單元格格式、位置或內(nèi)容的信息　248
案例200　獲取當前工作簿的完整路徑　250
7．1．2　INFO——返回當前操作環(huán)境的相關信息　250
案例201　獲取當前操作環(huán)境　251
7．1．3　SHEET——返回引用工作表的工作表編號　252
7．1．4　SHEETS——返回引用中的工作表總數(shù)　252
案例202　統(tǒng)計當前工作簿包含的工作表總數(shù)(一)　253
7．2　返回各類型的值　253
7．2．1　ERROR．TYPE——返回對應于錯誤類型的數(shù)字　253
案例203　根據(jù)錯誤代碼顯示錯誤原因(ERROR．TYPE+CHOOSE)　254
7．2．2　N——返回轉換為數(shù)字的值　254
案例204　生成指定員工的年薪列表(N+OFFSET)　255
7．2．3　NA——返回錯誤值“#N/A”　255
案例205　統(tǒng)計貨商品的數(shù)量　255
7．2．4　TYPE——返回表示數(shù)據(jù)類型的數(shù)字　256
案例206　簡易的輸入驗證工具(TYPE+LOOKUP)　256
7．3　使用IS函數(shù)進行各種判斷　257
7．3．1　ISBLANK——判斷單元格是否為空　257
案例207　統(tǒng)計員工缺勤人數(shù)(ISBLANK+SUM)　257
7．3．2　ISLOGICAL——判斷值是否為邏輯值　258
7．3．3　ISNUMBER——判斷值是否為數(shù)字　258
案例208　統(tǒng)計指定商品的銷量總和(ISNUMBER+SUM+FIND)　258
7．3．4　ISTEXT——判斷值是否為文本　259
案例209　判斷員工是否已簽到(ISTEXT+IF)　259
7．3．5　ISNONTEXT——判斷值是否為非文本　259
案例210　判斷員工是否已簽到(ISNONTEXT+IF)　259
7．3．6　ISFORMULA——判斷單元格是否包含公式　260
案例211　判斷單元格中的內(nèi)容是否包含公式　260
7．3．7　ISEVEN——判斷數(shù)字是否為偶數(shù)　261
案例212　統(tǒng)計公司女員工人數(shù)(ISEVEN+SUM+MID)　261
7．3．8　ISODD——判斷數(shù)字是否為奇數(shù)　261
案例213　統(tǒng)計公司男員工人數(shù)(ISODD+SUM+MID)　262
7．3．9　ISNA——判斷值是否為錯誤值“#N/A”　262
案例214　查找員工信息(ISNA+IF+VLOOKUP)　263
7．3．10　ISREF——判斷值是否為單元格引用　263
7．3．11　ISERR——判斷值是否為除“#N/A”以外的其他錯誤值　264
7．3．12　ISERROR——判斷值是否為錯誤值　264
案例215　統(tǒng)計員工年薪總和(ISERROR+SUM+IF)　264
第8章　統(tǒng)計函數(shù)　265
8．1　統(tǒng)計數(shù)量與頻率　269
8．1．1　COUNT——計算參數(shù)中包含數(shù)字的個數(shù)　269
案例216　統(tǒng)計員工人數(shù)　270
案例217　統(tǒng)計不重復員工人數(shù)(COUNT+MATCH+ROW)　271
8．1．2　COUNTA——計算參數(shù)中包含非空值的個數(shù)　271
案例218　統(tǒng)計遲到人數(shù)　272
案例219　創(chuàng)建動態(tài)區(qū)域名稱(COUNTA+OFFSET)　272
8．1．3　COUNTBLANK——計算區(qū)域中空白單元格的個數(shù)　273
案例220　統(tǒng)計會議未到場人數(shù)　273
8．1．4　COUNTIF——計算滿足給定條件的單元格的個數(shù)　274
案例221　統(tǒng)計銷量大于800的員工人數(shù)　274
案例222　計算兩列數(shù)據(jù)中相同數(shù)據(jù)的個數(shù)(COUNTIF+SUM)　274
案例223　統(tǒng)計不重復員工人數(shù)(COUNTIF+SUM)　275
案例224　提取不重復員工姓名(COUNTIF+LOOKUP+NOT)　275
8．1．5　COUNTIFS——計算滿足多個給定條件的單元格的個數(shù)　276
案例225　統(tǒng)計銷量在600到1000之間的男員工人數(shù)　276
8．1．6　FREQUENCY——以垂直數(shù)組形式返回數(shù)據(jù)的頻率分布　277
案例226　統(tǒng)計不同銷量區(qū)間的員工人數(shù)　278
8．2　統(tǒng)計均值和眾數(shù)　278
8．2．1　AVEDEV——計算一組數(shù)據(jù)與其平均值的絕對偏差的平均值　278
案例227　計算零件質量系數(shù)的平均偏差　279
8．2．2　AVERAGE——計算參數(shù)的平均值　279
案例228　計算某商品的平均價格　279
案例229　計算男員工的平均銷量(一)(AVERAGE+ROUND+IF)　280
8．2．3　AVERAGEA——計算參數(shù)中非空值的平均值　280
案例230　計算員工的平均銷量(含未統(tǒng)計者)(AVERAGEA+ROUND)　281
8．2．4　AVERAGEIF——計算滿足給定條件的所有單元格的平均值　281
案例231　計算男員工的平均銷量(二)(AVERAGEIF+ROUND)　282
8．2．5　AVERAGEIFS——計算滿足多個給定條件的所有單元格的平均值　283
案例232　計算銷量大于600的男員工的平均銷量(AVERAGEIFS+ROUND)　284
8．2．6　GEOMEAN——計算幾何平均值　284
案例233　計算銷售業(yè)績的平均增長率　285
8．2．7　HARMEAN——計算調(diào)和平均值　285
案例234　計算從第一天開始到第三天每天的平均產(chǎn)量　286
8．2．8　TRIMMEAN——計算內(nèi)部平均值　286
案例235　計算選手最后得分(TRIMMEAN+ROUND)　286
8．2．9　MEDIAN——返回中值　287
案例236　計算銷量的中間值　288
案例237　計算銷量的中間值所在的日期(MEDIAN+INDEX+MATCH)　288
8．2．10　MODE．SNGL——返回數(shù)據(jù)中出現(xiàn)次數(shù)最多的值　289
案例238　統(tǒng)計投票次數(shù)最多的選手(一)　289
案例239　提取出現(xiàn)次數(shù)最多的數(shù)字(MODE．SNGL+MID+ROW+INDIRECT+LEN)　289
8．2．11　MODE．MULT——返回數(shù)據(jù)中出現(xiàn)頻率最高或重復出現(xiàn)的數(shù)值的垂直數(shù)組　290
案例240　統(tǒng)計投票次數(shù)最多的選手(二)　291
8．3　統(tǒng)計極值與排位　291
8．3．1　MAX——返回一組數(shù)字中的最大值　291
案例241　統(tǒng)計男員工中完成的最大銷量　292
案例242　計算單日最高銷量(MAX+SUMIF)　292
案例243　提取最后一次出貨的日期(MAX+TEXT+INDEX+IF+ROW)　292
8．3．2　MAXA——返回一組非空值中的最大值　293
8．3．3　MIN——返回一組數(shù)字中的最小值　294
案例244　顯示一個不超過限定日期的動態(tài)時間(MIN+TODAY)　294
案例245　提取完成最小銷量的員工姓名(MIN+INDEX+MATCH)　294
8．3．4　MINA——返回一組非空值中的最小值　295
8．3．5　LARGE——返回數(shù)據(jù)集中第k個最大值　296
案例246　計算銷量前3名的銷量總和(LARGE+SUM)　296
8．3．6　SMALL—返回數(shù)據(jù)集中第k個最小值　296
案例247　計算銷量后3名的銷量總和(SMALL+SUM)　297
8．3．7　RANK．EQ——返回一個數(shù)字在一組數(shù)字中的排位　297
案例248　對員工銷量降序排名(一)　298
案例249　計算兩列數(shù)據(jù)中相同數(shù)據(jù)的個數(shù)(RANK．EQ+COUNT)　298
8．3．8　RANK．AVG——返回一個數(shù)字在一組數(shù)字中的排位　299
案例250　對員工銷量降序排名(二)　299
8．3．9　PERCENTRANK．INC——返回數(shù)據(jù)集中值的百分比排位　300
案例251　計算某個銷售員在所有銷售員中的銷量百分比排位　300
8．3．10　PERCENTRANK．EXC——返回數(shù)據(jù)集中值的百分比排位　301
8．3．11　QUARTILE．INC——返回數(shù)據(jù)集的四分位數(shù)　301
案例252　根據(jù)員工銷量計算四分位數(shù)　302
8．3．12　QUARTILE．EXC——返回數(shù)據(jù)集的四分位數(shù)　302
8．3．13　PERCENTILE．INC——返回區(qū)域中數(shù)值的第k個百分點的值　303
案例253　根據(jù)員工銷量計算指定百分點的值　304
8．3．14　PERCENTILE．EXC——返回區(qū)域中數(shù)值的第k個百分點的值　304
8．3．15　PERMUT——返回給定數(shù)目對象的排列數(shù)　305
案例254　計算中獎率　305
8．3．16　PERMUTATIONA——返回給定數(shù)目對象(含重復)的排列數(shù)　306
8．4　統(tǒng)計數(shù)據(jù)的散布度　306
8．4．1　DEVSQ——計算偏差的平方和　306
案例255　計算零件質量系數(shù)的偏差平方和　307
8．4．2　STDEV．S——估算基于樣本的標準偏差，忽略文本和邏輯值　307
案例256　計算員工工齡樣本的標準偏差　307
8．4．3　STDEVA——估算基于樣本的標準偏差，包括文本和邏輯值　308
案例257　計算員工工齡樣本的標準偏差(含未統(tǒng)計者)　308
8．4．4　STDEV．P——估算基于整個樣本總體的標準偏差，忽略文本和邏輯值　309
案例258　計算員工工齡樣本總體的標準偏差　310
8．4．5　STDEVPA——估算基于整個樣本總體的標準偏差，包括文本和邏輯值　310
案例259　計算員工工齡樣本總體的標準偏差(含未統(tǒng)計者)　311
8．4．6　VAR．S——計算基于給定樣本的方差，忽略文本和邏輯值　311
案例260　計算員工工齡樣本的方差　312
8．4．7　VARA——計算基于給定樣本的方差，包括文本和邏輯值　312
案例261　計算員工工齡樣本的方差(含未統(tǒng)計者)　312
8．4．8　VAR．P——計算基于整個樣本總體的方差，忽略文本和邏輯值　313
案例262　計算員工工齡樣本總體的方差　313
8．4．9　VARPA——計算基于整個樣本總體的方差，包括文本和邏輯值　314
案例263　計算員工工齡樣本總體的方差(含未統(tǒng)計者)　314
8．4．10　KURT——返回數(shù)據(jù)集的峰值　315
案例264　計算商品在一段時期內(nèi)價格的峰值　315
8．4．11　SKEW——返回分布的不對稱度　316
案例265　計算商品在一段時期內(nèi)價格的不對稱度　316
8．4．12　SKEW．P——返回某一分布相對其平均值的不對稱度　316
8．5　統(tǒng)計概率分布　317
8．5．1　BINOM．DIST——返回一元二項式分布的概率　317
案例266　計算沒有不合格產(chǎn)品的概率　318
8．5．2　BINOM．INV——返回使累積二項式分布小于或等于臨界值的最小值　318
案例267　計算允許不合格產(chǎn)品的數(shù)量　319
8．5．3　BINOM．DIST．RANGE——返回二項式分布試驗結果的概率　319
8．5．4　NEGBINOM．DIST——返回負二項式分布的概率　320
案例268　計算談判成功的概率　321
8．5．5　PROB——返回區(qū)域中的數(shù)值落在指定區(qū)間內(nèi)的概率　321
案例269　計算中獎概率　322
8．5．6　GAUSS——返回比標準正態(tài)累積分布函數(shù)小0．5的值　322
案例270　計算比標準正態(tài)累積分布函數(shù)小0．5的值　322
8．5．7　PHI——返回標準正態(tài)分布的密度函數(shù)值　323
案例271　計算標準正態(tài)分布的密度函數(shù)值　323
8．5．8　NORM．DIST——返回正態(tài)累積分布函數(shù)　323
案例272　計算概率密度函數(shù)的值　324
8．5．9　NORM．INV——返回標準正態(tài)累積分布的反函數(shù)值　324
案例273　計算累積分布函數(shù)的反函數(shù)的值　325
8．5．10　NORM．S．DIST——返回標準正態(tài)累積分布函數(shù)　325
案例274　制作正態(tài)分布表　326
8．5．11　NORM．S．INV——返回標準正態(tài)累積分布函數(shù)的反函數(shù)值　326
案例275　計算標準正態(tài)分布函數(shù)的反函數(shù)　327
8．5．12　STANDARDIZE——返回正態(tài)化數(shù)值　327
案例276　計算正態(tài)化數(shù)值　327
8．5．13　LOGNORM．DIST——返回對數(shù)累積分布函數(shù)　329
案例277　計算對數(shù)累積分布函數(shù)的值　329
8．5．14　LOGNORM．INV——返回對數(shù)累積分布函數(shù)的反函數(shù)　330
案例278　計算對數(shù)累積分布函數(shù)的反函數(shù)的值　330
8．5．15　HYPGEOM．DIST——返回超幾何分布　331
案例279　計算沒有不合格產(chǎn)品的概率　332
8．5．16　POISSON．DIST——返回泊松分布　332
案例280　計算產(chǎn)品不發(fā)生故障的概率　333
8．5．17　EXPON．DIST——返回指數(shù)分布　333
案例281　計算在經(jīng)過指定期限后，產(chǎn)品在兩家公司發(fā)生故障的概率　333
8．5．18　WEIBULL．DIST——返回韋伯分布　334
案例282　計算產(chǎn)品的不同故障發(fā)生的概率　335
8．5．19　GAMMA——返回伽瑪函數(shù)值　335
案例283　計算伽瑪函數(shù)值　336
8．5．20　GAMMA．DIST——返回伽瑪分布函數(shù)　336
案例284　計算伽瑪分布函數(shù)的值　337
8．5．21　GAMMA．INV——返回伽瑪累積分布函數(shù)的反函數(shù)值　337
案例285　計算伽瑪分布函數(shù)的反函數(shù)的值　338
8．5．22　GAMMALN——返回伽瑪函數(shù)的自然對數(shù)　338
案例286　計算伽瑪函數(shù)的自然對數(shù)值　338
8．5．23　GAMMALN．PRECISE——返回伽瑪函數(shù)的自然對數(shù)　339
案例287　計算伽瑪函數(shù)的自然對數(shù)值　339
8．5．24　BETA．DIST——返回β累積分布函數(shù)　339
案例288　計算β累積分布函數(shù)的值　340
8．5．25　BETA．INV——返回指定β累積分布函數(shù)的反函數(shù)值　340
案例289　計算β累積分布函數(shù)的反函數(shù)的值　341
8．5．26　CONFIDENCE．NORM——返回總體平均值的置信區(qū)間　341
案例290　計算平均視力的95％置信區(qū)間　342
8．5．27　CONFIDENCE．T——返回總體平均值的置信區(qū)間　343
8．6　檢驗數(shù)據(jù)的傾向性　343
8．6．1　CHISQ．DIST．RT——返回x2分布的右尾概率　343
案例291　計算x2分布的單尾概率　344
8．6．2　CHISQ．DIST——返回x2分布　344
8．6．3　CHISQ．INV．RT——返回x2分布的右尾概率的反函數(shù)值　345
案例292　計算x2分布的單尾概率的反函數(shù)　345
8．6．4　CHISQ．INV——返回x2分布的左尾概率的反函數(shù)值　346
8．6．5　CHISQ．TEST——返回獨立性檢驗值　346
案例293　檢驗電腦用戶與患頸椎病的關系　347
8．6．6　F．DIST．RT——返回F概率分布　347
案例294　計算F分布的概率　348
8．6．7　F．DIST——返回F概率分布　348
8．6．8　F．INV．RT——返回F概率分布的反函數(shù)值　349
案例295　計算F累積分布概率的反函數(shù)　349
8．6．9　F．INV——返回F概率分布的反函數(shù)值　349
8．6．10　T．DIST．2T——返回t分布的雙尾概率　350
案例296　計算t分布的概率　351
8．6．11　T．DIST．RT——返回t分布的右尾概率　351
8．6．12　T．DIST——返回t分布　351
8．6．13　T．INV．2T——返回t分布的雙尾反函數(shù)值　352
案例297　計算t分布概率的反函數(shù)　353
8．6．14　T．INV——返回t分布的左尾反函數(shù)值　353
8．6．15　F．TEST——返回F檢驗的結果　353
案例298　檢驗空調(diào)和冰箱耗電量的方差　354
8．6．16　T．TEST——返回與t檢驗相關的概率　354
案例299　檢驗空調(diào)和冰箱耗電量的平均值　355
8．6．17　Z．TEST——返回z檢驗的單尾概率　356
案例300　檢驗2009年度與5年前商品銷量的平均記錄　357
8．7　統(tǒng)計協(xié)方差、相關系數(shù)與回歸　357
8．7．1　COVARIANCE．P——計算總體協(xié)方差，即成對偏差乘積的平均值　357
案例301　計算以年齡和視力為樣本數(shù)據(jù)的協(xié)方差　358
8．7．2　COVARIANCE．S——計算樣本協(xié)方差，即成對偏差乘積的平均值　358
案例302　計算以年齡和視力為樣本數(shù)據(jù)的協(xié)方差　359
8．7．3　CORREL——返回兩個數(shù)據(jù)集之間的相關系數(shù)　359
案例303　計算年齡和視力的相關系數(shù)　359
8．7．4　FISHER——返回Fisher變換值　360
案例304　計算Fisher變換的相關系數(shù)(FISHER+CORREL)　360
8．7．5　FISHERINV——返回Fisher變換的反函數(shù)值　360
案例305　計算Fisher變換的反函數(shù)　361
8．7．6　PEARSON——返回皮爾生乘積矩相關系數(shù)　361
案例306　計算年齡和視力的皮爾生乘積矩相關系數(shù)　362
8．7．7　RSQ——返回皮爾生乘積矩相關系數(shù)的平方　362
案例307　計算年齡和視力的皮爾生乘積矩相關系數(shù)的平方　363
8．7．8　FORECAST——根據(jù)現(xiàn)有的數(shù)據(jù)計算或預測未來值　363
案例308　預測特定年齡的視力　364
8．7．9　GROWTH——根據(jù)現(xiàn)有的數(shù)據(jù)計算或預測指數(shù)增長值　364
案例309　預測未來月份的銷量　365
8．7．10　TREND——計算一條線性回歸線的值　365
案例310　預測回歸線上的視力　366
8．7．11　LINEST——返回線性回歸線的參數(shù)　366
案例311　根據(jù)年齡和視力求線性回歸線　367
8．7．12　LOGEST——返回指數(shù)回歸線的參數(shù)　368
案例312　根據(jù)時間和銷量求指數(shù)回歸線　369
8．7．13　SLOPE——返回線性回歸線的斜率　369
案例313　從年齡和視力中求回歸線的斜率　370
8．7．14　INTERCEPT——返回線性回歸線的截距　370
案例314　從年齡和視力中求回歸線的截距　371
8．7．15　STEYX——返回通過線性回歸法預測每個x的y值時所產(chǎn)生的標準誤差　371
案例315　從年齡和視力中求回歸線的標準誤差　372
第9章　財務函數(shù)　373
9．1　了解貨幣的時間價值　375
9．2　計算本金和利息　376
9．2．1　PMT——計算貸款的每期付款額　376
案例316　計算貸款的每期付款額　377
9．2．2　IPMT——計算貸款在給定期間內(nèi)支付的利息　377
案例317　計算貸款在給定期間內(nèi)支付的利息　378
9．2．3　PPMT——計算貸款在給定期間內(nèi)償還的本金　378
案例318　計算貸款在給定期間內(nèi)償還的本金　379
9．2．4　ISPMT——計算特定投資期內(nèi)支付的利息　379
案例319　計算特定投資期內(nèi)支付的利息　380
9．2．5　CUMIPMT——計算兩個付款期之間累積支付的利息　380
案例320　計算兩個付款期之間累積支付的利息　381
9．2．6　CUMPRINC——計算兩個付款期之間累積支付的本金　381
案例321　計算兩個付款期之間累積支付的本金　382
9．2．7　EFFECT——將名義年利率轉換為實際年利率　383
案例322　將名義年利率轉換為實際年利率　383
9．2．8　NOMINAL——將實際年利率轉換為名義年利率　383
案例323　將實際年利率轉換為名義年利率　384
9．2．9　RATE——計算年金的各期利率　384
案例324　計算年金的各期利率　385
9．3　計算投資預算　385
9．3．1　FV——計算一筆投資的未來值　385
案例325　計算一筆投資的未來值　386
9．3．2　FVSCHEDULE——使用一系列復利率計算初始本金的未來值　386
案例326　使用一系列復利率計算初始本金的未來值　387
9．3．3　NPER——計算投資的期數(shù)　387
案例327　計算投資的期數(shù)(NPER+ROUNDUP)　387
9．3．4　PDURATION——計算投資到達指定值所需的期數(shù)　388
案例328　計算投資到達指定值所需的期數(shù)(PDURATION+ROUNDUP)　388
9．3．5　PV——計算投資的現(xiàn)值　389
案例329　計算投資的現(xiàn)值　389
9．3．6　NPV——基于一系列定期的現(xiàn)金流和貼現(xiàn)率計算投資的凈現(xiàn)值　389
案例330　計算投資的凈現(xiàn)值　390
9．3．7　XNPV——計算一組未必定期發(fā)生的現(xiàn)金流的凈現(xiàn)值　390
案例331　計算未必定期發(fā)生的投資的凈現(xiàn)值　391
9．3．8　RRI——計算某項投資增長的等效利率　391
案例332　計算某項投資增長的等效利率　392
9．4　計算收益率　392
9．4．1　IRR——計算一系列現(xiàn)金流的內(nèi)部收益率　392
案例333　計算一系列現(xiàn)金流的內(nèi)部收益率　392
9．4．2　MIRR——計算正負現(xiàn)金流在不同利率下支付的內(nèi)部收益率　393
案例334　計算在不同利率下支付的修正內(nèi)部收益率　393
9．4．3　XIRR——計算一組未必定期發(fā)生的現(xiàn)金流的內(nèi)部收益率　394
案例335　計算未必定期發(fā)生的現(xiàn)金流的內(nèi)部收益率　394
9．5　計算折舊值　395
9．5．1　AMORDEGRC——根據(jù)資產(chǎn)的耐用年限，計算每個結算期間的折舊值　395
案例336　計算每個結算期間的余額遞減折舊值　396
9．5．2　AMORLINC——計算每個結算期間的折舊值　396
案例337　計算每個結算期間的余額遞減折舊值　397
9．5．3　DB——使用固定余額遞減法，計算一筆資產(chǎn)在給定期間內(nèi)的折舊值　398
案例338　使用固定余額遞減法計算折舊值(DB+ROW)　398
9．5．4　DDB——使用雙倍余額遞減法或其他指定方法，計算一筆資產(chǎn)在給定期間內(nèi)的折舊值　398
案例339　使用雙倍余額遞減法計算折舊值(DDB+ROW)　399
9．5．5　VDB——使用余額遞減法，計算一筆資產(chǎn)在給定期間或部分期間內(nèi)的折舊值　399
案例340　使用余額遞減法計算折舊值　400
9．5．6　SYD——計算某項資產(chǎn)按年限總和折舊法計算的指定期間的折舊值　400
案例341　按年限總和折舊法計算折舊值(SYD+ROW)　400
9．5．7　SLN——計算某項資產(chǎn)在一個期間內(nèi)的線性折舊值　401
案例342　計算期間內(nèi)的線性折舊值　401
9．6　計算證券與國庫券　401
9．6．1　ACCRINT——計算定期支付利息的有價證券的應計利息　401
案例343　計算定期支付利息的有價證券的應計利息　403
9．6．2　ACCRINTM——計算在到期日支付利息的有價證券的應計利息　403
案例344　計算在到期日支付利息的有價證券的應計利息　404
9．6．3　COUPDAYBS——計算當前付息期內(nèi)截止到成交日的天數(shù)　404
案例345　計算當前付息期內(nèi)截止到成交日的天數(shù)　405
9．6．4　COUPDAYS——計算成交日所在的付息期的天數(shù)　405
案例346　計算成交日所在的付息期的天數(shù)　406
9．6．5　COUPDAYSNC——計算從成交日到下一個付息日之間的天數(shù)　407
案例347　計算從成交日到下一個付息日之間的天數(shù)　408
9．6．6　COUPNCD——計算成交日之后的下一個付息日　408
案例348　計算成交日之后的下一個付息日　409
9．6．7　COUPNUM——計算成交日和到期日之間的應付利息次數(shù)　409
案例349　計算成交日和到期日之間的應付利息次數(shù)　410
9．6．8　COUPPCD——計算成交日之前的上一付息日　411
案例350　計算成交日之前的上一付息日　412
9．6．9　DISC——計算有價證券的貼現(xiàn)率　412
案例351　計算有價證券的貼現(xiàn)率　413
9．6．10　DURATION——計算定期支付利息的有價證券的修正期限　413
案例352　計算定期支付利息的有價證券的修正期限　414
9．6．11　PRICE——計算定期付息的面值￥100的有價證券的價格　415
案例353　計算定期付息的面值￥100的有價證券的價格　416
9．6．12　PRICEDISC——計算折價發(fā)行的面值￥100的有價證券的價格　416
案例354　計算折價發(fā)行的面值￥100的有價證券的價格　417
9．6．13　PRICEMAT——計算到期付息的面值￥100的有價證券的價格　418
案例355　計算到期付息的面值￥100的有價證券的價格　419
9．6．14　ODDFPRICE——計算首期付息日不固定的面值￥100的有價證券價格　419
案例356　計算首期付息日不固定的面值￥100的有價證券價格　420
9．6．15　ODDFYIELD——計算首期付息日不固定的有價證券的收益率　420
案例357　計算首期付息日不固定的有價證券的收益率　421
9．6．16　ODDLPRICE——計算末期付息日不固定的面值￥100的有價證券價格　422
案例358　計算末期付息日不固定的面值￥100的有價證券價格　423
9．6．17　ODDLYIELD——計算末期付息日不固定的有價證券的收益率　423
案例359　計算末期付息日不固定的有價證券的收益率　424
9．6．18　MDURATION——計算假設面值為￥100的有價證券的Macauley修正期限　425
案例360　計算假設面值為￥100的有價證券的Macauley修正期限　426
9．6．19　INTRATE——計算一次性付息證券的利率　426
案例361　計算一次性付息證券的利率　427
9．6．20　RECEIVED——計算一次性付息的有價證券到期收回的金額　427
案例362　計算一次性付息的有價證券到期收回的金額　428
9．6．21　TBILLEQ——計算國庫券的等價債券收益　429
案例363　計算國庫券的等價債券收益　429
9．6．22　TBILLPRICE——計算面值￥100的國庫券的價格　429
案例364　計算面值￥100的國庫券的價格　430
9．6．23　TBILLYIELD——計算國庫券的收益率　430
案例365　計算國庫券的收益率　431
9．6．24　YIELD——計算定期支付利息的有價證券的收益率　431
案例366　計算定期支付利息的有價證券的收益率　432
9．6．25　YIELDDISC——計算折價發(fā)行的有價證券的年收益率　433
案例367　計算折價發(fā)行的有價證券的年收益率　434
9．6．26　YIELDMAT——計算到期付息的有價證券的年收益率　434
案例368　計算到期付息的有價證券的年收益率　435
9．7　轉換美元價格的格式　435
9．7．1　DOLLARDE——將以分數(shù)表示的美元價格轉換為以小數(shù)表示的美元價格　435
案例369　將小數(shù)格式的美元轉換為分數(shù)格式的美元　436
9．7．2　DOLLARFR——將以小數(shù)表示的美元價格轉換為以分數(shù)表示的美元價格　436
案例370　將分數(shù)格式的美元轉換為小數(shù)格式的美元　436
第10章　數(shù)據(jù)庫函數(shù)　437
10．1　計算數(shù)據(jù)庫中的數(shù)據(jù)　438
10．1．1　DPRODUCT——計算滿足條件的數(shù)字的乘積　438
案例371　統(tǒng)計商品的維修記錄　438
10．1．2　DCOUNTA——計算滿足條件的數(shù)字的總和　439
案例372　計算符合特定條件的員工工資總和　439
10．2　對數(shù)據(jù)庫中的數(shù)據(jù)進行常規(guī)統(tǒng)計　440
10．2．1　DAVERAGE——計算滿足條件的數(shù)值的平均值　440
案例373　計算符合特定條件的員工的平均月薪　441
10．2．2　DCOUNT——計算滿足條件的包含數(shù)字的單元格的個數(shù)　441
案例374　計算公司各職位的員工人數(shù)　442
10．2．3　DCOUNTA——計算滿足條件的非空單元格的個數(shù)　443
案例375　計算上班遲到的女員工人數(shù)　443
10．2．4　DGET——返回符合條件的單個值　444
案例376　提取指定商品的價格　445
10．2．5　DMAX——返回滿足條件的列表中的最大值　445
案例377　提取指定商品的最大銷量　446
10．2．6　DMIN——返回滿足條件的列表中的最小值　446
案例378　提取指定商品的最小銷量　447
10．3　對數(shù)據(jù)庫中的數(shù)據(jù)進行散布度統(tǒng)計　447
10．3．1　DSTDEV——返回滿足條件的數(shù)字作為一個樣本估算出的樣本標準偏差　447
案例379　計算男員工工齡的樣本標準偏差　448
10．3．2　DSTDEVP——返回滿足條件的數(shù)字作為樣本總體計算出的總體標準偏差　449
案例380　計算男員工工齡的總體標準偏差　449
10．3．3　DVAR——返回滿足條件的數(shù)字作為一個樣本估算出的樣本總體方差　450
案例381　計算男員工工齡的樣本總體方差　451
10．3．4　DVARP——返回滿足條件的數(shù)字作為樣本總體計算出的總體方差　451
案例382　計算男員工工齡的總體方差　452
第11章　Web函數(shù)與宏表函數(shù)　453
11．1　Web函數(shù)　453
11．1．1　ENCODEURL——將文本轉換為URL編碼　453
案例383　對包含中文字符的網(wǎng)址進行編碼　454
11．1．2　WEBSERVICE——從Web服務中獲取網(wǎng)絡數(shù)據(jù)　454
11．1．3　FILTERXML——在XML結構化內(nèi)容中獲取指定路徑下的信息　455
11．2　宏表函數(shù)　455
11．2．1　GET．WORKBOOK——返回工作簿的相關信息　455
案例384　統(tǒng)計當前工作簿包含的工作表總數(shù)(二)(GET．WORKBOOK+T+NOW)　456
11．2．2　GET．CELL——返回單元格的相關信息　457
案例385　提取單元格中的公式(二)　458
11．2．3　EVALUATE——計算包含文本的表達式的值　459
案例386　計算單元格中的文本公式　459
案例387　計算不規(guī)則格式的文本公式(EVALUATE+SUBSTITUTE+ISTEXT)　460
第12章　在條件格式中使用公式　461
12．1　條件格式簡介　461
12．2　在條件格式中使用公式　462
12．2．1　創(chuàng)建基于公式的條件格式的原則　462
12．2．2　條件格式公式示例　464
案例388　標記重復值　464
案例389　標記最大值　464
案例390　隱藏錯誤值　465
案例391　設置隔行底紋　465
案例392　設置固定不變的隔行底紋　466
案例393　設置生日到期提醒　466
第13章　在數(shù)據(jù)有效性中使用公式　467
13．1　數(shù)據(jù)有效性簡介　467
13．2　在數(shù)據(jù)有效性中使用公式　469
13．2．1　創(chuàng)建基于公式的數(shù)據(jù)有效性的原則　469
13．2．2　數(shù)據(jù)有效性公式示例　469
案例394　僅限輸入某種類型的數(shù)據(jù)　469
案例395　禁止輸入重復值　470
案例396　將輸入值限制在總數(shù)內(nèi)　471
案例397　快速切換數(shù)據(jù)有效性來源　471
案例398　創(chuàng)建可自動擴展的動態(tài)列表　473
案例399　創(chuàng)建二級下拉列表　474
第14章　在圖表中使用公式　475
14．1　圖表簡介　475
14．1．1　圖表類型　475
14．1．2　圖表元素　480
14．1．3　創(chuàng)建圖表　481
14．1．4　圖表在工作簿中的位置　483
14．2　使用公式與控件創(chuàng)建動態(tài)圖表　484
14．2．1　在圖表中使用公式　484
14．2．2　在圖表中使用控件　485
14．2．3　了解數(shù)據(jù)系列的SERIES公式　486
14．2．4　動態(tài)圖表示例　487
案例400　制作可自動擴展的圖表　487
案例401　制作顯示最近幾天銷售額的圖表　489
案例402　制作可指定起點和終點的圖表　490
案例403　使用下拉列表控制餅圖的顯示　492
案例404　使用滾動條控制圖表的顯示　494
案例405　使用復選項控制圖表中顯示的數(shù)據(jù)系列　496
附錄1　Excel快捷鍵　500
附錄2　Excel函數(shù)速查表　505
附錄3　Excel 2003到Excel2013命令參考　525