Excel函數(shù)與公式速查大全

第1章　探索Excel公式與函數(shù)的秘密 001
1.1　初來乍到先做自我介紹 001
1.1.1　學(xué)習(xí)Excel公式到底難不難 001
1.1.2　什么是Excel公式 002
1.1.3　Excel公式具有哪些特點 002
1.1.4　組成Excel公式的主要零部件 004
1.1.5　公式的運算原理是什么 004
1.2　理解Excel專業(yè)術(shù)語 006
1.2.1　Excel常用術(shù)語詳解 006
1.2.2　詳述行、列以及單元格的關(guān)系 008
1.2.3　如何定義名稱 010
1.3　怎樣輸入公式更快速 011
1.3.1　如何引用單元格或單元格區(qū)域 011
1.3.2　如何修改公式 013
1.3.3　如何復(fù)制和填充公式 014
1.4　單元格的三種引用形式 016
1.4.1　相對引用的特點 016
1.4.2　絕對引用的特點 017
1.4.3　混合引用的特點 018
1.4.4　快速切換引用方式 019
1.5　數(shù)組公式是什么 020
1.5.1　數(shù)組的類型 020
1.5.2　數(shù)組公式的表現(xiàn)形式 021
1.5.3　數(shù)組公式的運算原理 021
1.5.4　數(shù)組公式的輸入方法及注意事項 022
1.6　函數(shù)應(yīng)用必不可少 023
1.6.1　函數(shù)與公式的關(guān)系 023
1.6.2　Excel函數(shù)有哪些類型 023
1.6.3　函數(shù)的組成結(jié)構(gòu) 024
1.6.4　最常用的函數(shù)公式 025
1.6.5　熟悉函數(shù)類型及作用 026
1.6.6　快速輸入函數(shù) 027
1.6.7　嵌套函數(shù)的應(yīng)用 032

第2章　數(shù)學(xué)和三角函數(shù)的應(yīng)用 034
數(shù)學(xué)和三角函數(shù)速查表 034
函數(shù)1　SUM——計算指定單元格區(qū)域中所有數(shù)字之和 037
函數(shù)練兵1：使用自動求和匯總銷售數(shù)量 037
函數(shù)練兵2：匯總銷售金額 038
函數(shù)練兵3：求3D合計值 039
函數(shù)2　SUMIF——對指定區(qū)域中符合某個特定條件的值求和 041
函數(shù)練兵1：計算不同類型消費所支出的金額 041
函數(shù)練兵2：設(shè)置比較條件計算超過100元的消費總額 043
函數(shù)練兵3：使用通配符設(shè)置求和條件 043
函數(shù)組合應(yīng)用：SUMIF+IF——根據(jù)總分判斷是否被錄取 044
函數(shù)3　SUMIFS——對區(qū)域內(nèi)滿足多個條件的值進行求和 045
函數(shù)練兵1：多個車間同時生產(chǎn)時計算指定車間指定產(chǎn)品的產(chǎn)量 046
函數(shù)練兵2：統(tǒng)計指定日期之前“花生”類產(chǎn)品的生產(chǎn)數(shù)量 047
函數(shù)4　PRODUCT——計算所有參數(shù)的乘積 048
函數(shù)練兵：計算茶葉銷售額 048
函數(shù)5　SUMPRODUCT——將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和 050
函數(shù)練兵：計算所有種類茶葉的合計銷售金額 050
函數(shù)6　SUMSQ——求參數(shù)的平方和 051
函數(shù)練兵：計算溫度測試結(jié)果的偏差平方和 052
函數(shù)組合應(yīng)用：SUMSQ+SQRT——求二次方、三次方坐標(biāo)的最大向量 052
函數(shù)7　SUMX2PY2——計算兩數(shù)組中對應(yīng)數(shù)值的平方和之和 053
函數(shù)練兵1：計算兩數(shù)組中對應(yīng)數(shù)值的平方和之和 053
函數(shù)練兵2：SUMSQ+SUM函數(shù)計算平方和之和 054
函數(shù)8　SUMX2MY2——計算兩數(shù)組中對應(yīng)數(shù)值的平方差之和 054
函數(shù)練兵1：求兩數(shù)組元素的平方差之和 055
函數(shù)練兵2：POWER+SUM分步求平方差之和 055
函數(shù)9　SUMXMY2——求兩數(shù)組中對應(yīng)數(shù)值差的平方和 056
函數(shù)練兵1：求兩數(shù)組中對應(yīng)數(shù)值差的平方和 056
函數(shù)練兵2：分步計算兩數(shù)組數(shù)值差的平方和 057
函數(shù)10　SUBTOTAL——計算數(shù)據(jù)列表或數(shù)據(jù)庫中的分類匯總 057
函數(shù)練兵1：計算網(wǎng)絡(luò)投票的票數(shù) 058
函數(shù)練兵2：計算參與比賽的男性或女性人數(shù) 060
函數(shù)練兵3：去除小計求平均票數(shù) 061
函數(shù)11　QUOTIENT——計算兩數(shù)相除商的整數(shù)部分 062
函數(shù)練兵：求在預(yù)算內(nèi)能購買的商品數(shù)量 062
函數(shù)12　MOD——計算兩數(shù)相除的余數(shù) 063
函數(shù)練兵：計算采購商品后的余額 063
函數(shù)13　ABS——求數(shù)值的絕對值 064
函數(shù)練兵1：計算數(shù)值的絕對值 064
函數(shù)練兵2：計算兩組數(shù)字的差值 065
函數(shù)組合應(yīng)用：ABS+IF——對比兩次考試的進步情況 065
函數(shù)14　SIGN——求數(shù)值的正負號 066
函數(shù)練兵：判斷業(yè)績是否達標(biāo) 066
函數(shù)組合應(yīng)用：SIGN+IF——用文字形式直觀展示業(yè)績完成情況 067
函數(shù)15　GCD——求最大公約數(shù) 067
函數(shù)練兵：求最大公約數(shù) 067
函數(shù)16　LCM——求最小公倍數(shù) 068
函數(shù)練兵：求最小公倍數(shù) 069
函數(shù)17　RAND——求大于等于0及小于1的均勻分布隨機數(shù) 069
函數(shù)練兵1：隨機安排值班人員 069
函數(shù)練兵2：批量生成指定范圍內(nèi)的隨機數(shù) 071
函數(shù)練兵3：求圓周率π的近似值 072
函數(shù)組合應(yīng)用：RAND+RANK——隨機安排考生座位號 073
函數(shù)18　RANDBETWEEN——自動生成一個介于指定數(shù)字之間的隨機整數(shù) 074
函數(shù)練兵：隨機生成6位數(shù)密碼 074
函數(shù)組合應(yīng)用：VLOOKUP+IFERROR+RANDBETWEEN——根據(jù)隨機中獎號碼同步顯示中獎?wù)咝彰?076
函數(shù)19　INT——將數(shù)值向下取整為最接近的整數(shù) 077
函數(shù)練兵1：舍去金額中的零錢部分 077
函數(shù)練兵2：計算固定金額在購買不同價格的商品時最多能買多少個 077
函數(shù)組合應(yīng)用：INT+SUM——舍去合計金額的小數(shù)部分 078
函數(shù)20　TRUNC——截去數(shù)字的小數(shù)部分，保留整數(shù) 078
函數(shù)練兵1：用TRUNC函數(shù)舍去數(shù)值中的小數(shù) 079
函數(shù)練兵2：舍去百位之后的數(shù)值，并以萬元為單位顯示 079
函數(shù)21　ROUND——按指定位數(shù)對數(shù)值四舍五入 080
函數(shù)練兵1：計算銷售金額，并對結(jié)果值四舍五入保留一位小數(shù) 081
函數(shù)練兵2：四舍五入不足1元的折扣金額 081
函數(shù)22　ROUNDUP——按指定的位數(shù)向上舍入數(shù)字 082
函數(shù)練兵：計算快遞計費重量 083
函數(shù)組合應(yīng)用：ROUNDUP+SUMIF——計算促銷商品的總金額并對結(jié)果向上舍入保留1位小數(shù) 083
函數(shù)23　ROUNDDOWN——按照指定的位數(shù)向下舍入數(shù)值 085
函數(shù)練兵：對實際測量體重進行取整 085
函數(shù)24　CEILING——將參數(shù)向上舍入為最接近的基數(shù)的倍數(shù) 086
函數(shù)練兵：計算不同數(shù)值在相同基數(shù)下的舍入結(jié)果 087
函數(shù)25　FLOOR——將參數(shù)向下舍入到最接近的基數(shù)的倍數(shù) 087
函數(shù)練兵：計算員工銷售提成 088
函數(shù)26　MROUND——按照指定基數(shù)的倍數(shù)對參數(shù)四舍五入 088
函數(shù)練兵：計算供銷雙方貨物訂單平衡值 089
函數(shù)27　EVEN——將數(shù)值向上舍入到最接近的偶數(shù) 089
函數(shù)練兵1：將數(shù)值向上舍入到最接近的偶數(shù) 090
函數(shù)練兵2：求每組所差人數(shù) 091
函數(shù)28　ODD——將數(shù)值向上舍入到最接近的奇數(shù) 091
函數(shù)練兵：將數(shù)值向上舍入到最接近的奇數(shù)值 092
函數(shù)29　PI——計算圓周率π的值 092
函數(shù)練兵：求圓周率的近似值 093
函數(shù)30　SQRT——求數(shù)值的平方根 093
函數(shù)練兵：求正數(shù)的平方根 094
函數(shù)31　SQRTPI——求某數(shù)與π的乘積的平方根 095
函數(shù)練兵：求圓周率π的倍數(shù)的平方根 095
函數(shù)組合應(yīng)用：SQRT+SUMSQ——求連接原點和坐標(biāo)指向的向量大小 095
函數(shù)32　COMBIN——求一組數(shù)所有可能的組合數(shù)目 096
函數(shù)練兵1：計算從10個數(shù)字中任選6個數(shù)有多少種排法 096
函數(shù)練兵2：根據(jù)條件計算組合數(shù) 096
函數(shù)33　ROMAN——將阿拉伯?dāng)?shù)字轉(zhuǎn)換為羅馬數(shù)字 097
函數(shù)練兵：將指定阿拉伯?dāng)?shù)字轉(zhuǎn)換成羅馬數(shù)字 098
函數(shù)34　ARABIC——將羅馬數(shù)字轉(zhuǎn)換成阿拉伯?dāng)?shù)字 099
函數(shù)練兵：將指定羅馬數(shù)字轉(zhuǎn)換成阿拉伯?dāng)?shù)字 099
函數(shù)35　SERIESSUM——求基于公式的冪級數(shù)的和 100
函數(shù)36　RADIANS——將角度轉(zhuǎn)換為弧度 100
函數(shù)37　DEGREES——將弧度轉(zhuǎn)換為角度 100
函數(shù)38　SIN——求給定角度的正弦值 101
函數(shù)39　COS——求給定角度的余弦值 101
函數(shù)40　TAN——求給定角度的正切值 101
函數(shù)41　ASIN——求指定數(shù)值的反正弦值 101
函數(shù)42　ACOS——求指定數(shù)值的反余弦值 101
函數(shù)43　ATAN——求指定數(shù)值的反正切值 101
函數(shù)44　ATAN2——根據(jù)給定的X軸及Y軸坐標(biāo)值，求反正切值 101
函數(shù)45　SINH——求任意實數(shù)的雙曲正弦值 101
函數(shù)46　COSH——求任意實數(shù)的雙曲余弦值 101
函數(shù)47　TANH——求任意實數(shù)的雙曲正切值 101
函數(shù)48　ASINH——求數(shù)值的反雙曲正弦值 101
函數(shù)49　ACOSH——求數(shù)值的反雙曲余弦值 101
函數(shù)50　ATANH——求數(shù)值的反雙曲正切值 102
函數(shù)51　POWER——求數(shù)值的乘冪 102
函數(shù)52　FACT——計算數(shù)值的階乘 102
函數(shù)53　EXP——計算e為底數(shù)指定指數(shù)的冪值 102
函數(shù)54　LOG——求指定的底數(shù)的對數(shù)值 102
函數(shù)55　LN——求自然底數(shù)的對數(shù)值 102
函數(shù)56　LOG10——計算以10為底數(shù)的對數(shù)值 102
函數(shù)57　MULTINOMIAL——求參數(shù)和的階乘與各參數(shù)階乘乘積的比值 102
函數(shù)58　MDETERM——求數(shù)組的矩陣行列式的值 102
函數(shù)59　MINVERSE——求數(shù)組矩陣的逆矩陣 102
函數(shù)60　MMULT——求兩個數(shù)組的矩陣乘積 102
函數(shù)61　BASE——將數(shù)字轉(zhuǎn)換為具備給定基數(shù)的文本表示 102

第3章　統(tǒng)計函數(shù)的應(yīng)用 103
統(tǒng)計函數(shù)速查表 103
函數(shù)1　AVERAGE——計算數(shù)字的平均值 107
函數(shù)練兵1：使用自動求平均值功能計算平均基本工資 107
函數(shù)練兵2：計算車間平均產(chǎn)量 108
函數(shù)2　AVERAGEA——計算參數(shù)列表中非空單元格中數(shù)值的平均值 110
函數(shù)練兵：計算車間全年平均產(chǎn)量 110
函數(shù)3　AVERAGEIF——求滿足條件的所有單元格中數(shù)值的平均值 111
函數(shù)練兵1：計算指定部門員工的平均工資 112
函數(shù)練兵2：計算實發(fā)工資低于5000元的平均工資 112
函數(shù)練兵3：使用通配符設(shè)置條件求平均值 113
函數(shù)4　AVERAGEIFS——求滿足多重條件的所有單元格中數(shù)值的平均值 113
函數(shù)練兵：計算人事部女性的平均年齡 114
函數(shù)組合應(yīng)用：AVERAGEIFS+LARGE——求銷售額前10名中智能產(chǎn)品的平均銷售額 115
函數(shù)5　TRIMMEAN——求數(shù)據(jù)集的內(nèi)部平均值 116
函數(shù)練兵：去除測試分?jǐn)?shù)指定比例的極值后，求平均值 116
函數(shù)6　GEOMEAN——求正數(shù)數(shù)組或數(shù)據(jù)區(qū)域的幾何平均值 118
函數(shù)練兵：去除測試分?jǐn)?shù)指定比例的極值后，求平均值 118
函數(shù)7　MEDIAN——求給定數(shù)值集合的中值 120
函數(shù)練兵：計算各城市上半年降雨量的中值 121
函數(shù)8　AVEDEV——計算一組數(shù)據(jù)與其均值的絕對偏差的平均值 122
函數(shù)練兵：根據(jù)抽樣檢查結(jié)果計算樣品平均偏差 122
函數(shù)組合應(yīng)用：AVERAGE+ABS——求抽檢產(chǎn)品蛋白質(zhì)含量平均偏差 122
函數(shù)9　DEVSQ——求數(shù)據(jù)點與各自樣本平均值偏差的平方和 124
函數(shù)練兵：根據(jù)抽樣檢查結(jié)果計算樣品的偏差平方和 124
函數(shù)10　MODE——求數(shù)值數(shù)據(jù)的眾數(shù) 124
函數(shù)練兵：求生產(chǎn)不良數(shù)記錄的眾數(shù) 125
函數(shù)11　HARMEAN——求數(shù)據(jù)集合的調(diào)和平均值 126
函數(shù)練兵：求接力賽跑平均速度 127
函數(shù)12　COUNT——計算區(qū)域中包含數(shù)字的單元格個數(shù) 128
函數(shù)練兵：計算實際參加考試的人數(shù) 128
函數(shù)13　COUNTA——計算指定單元格區(qū)域中非空單元格的數(shù)目 129