Excel 2010函式與公式綜合套用技巧（雙色精華版）

內容簡介

本書精選了日常辦公中常用的Excel函式進行講解，全書共11章，辣拳腳蘭前兩章為公式與函式基礎知識介紹，包括公式創建、複製、錯誤值分析與處理等方面整體組的技巧，為後面的學習做鋪墊；第3章～第 11章介紹了邏輯函式、數學與三角函式、時間與日期函式、文本函式、查找和引用函式、信息函式、統計函式、財務函式、資料庫函式等9類共540個函式的使用芝笑促項技巧，每個函式都輔以實例進行講解，力求向讀者展示Excel函式在數據計算、處理和分析方面的強大功能。

本書內容實用、結構清晰、表述簡練，採用雙色印刷，以圖文並茂的形式來進行講解。本書非常適合經常使用Excel進行數據計算、處理與分析的辦公人員閱讀，也可作為行政管理人員、數據分析人員、財務人員、統計人員和行銷人員案頭函式速查參考書。

圖書目錄

1.1　公式操作基礎 1

技巧1修改公式的幾種方法 1

技巧2快速複製公式的4種方法 2

技巧3大範圍複製公式 3

技巧4在多工作表的同一單充墓詢元格建立相同

公式 4

技巧5跳過非空單元格批量建立公式 4

技巧6保留沒有輸入完整的公式 5

技巧7合併單元格中公式的複製 6

技巧8複製公式但不使用相對引用 6

技巧9查看長公式中某一步的計算結果 7

技巧10在新輸入的行中自動填充公式 7

技巧11快速在多個合計行中填充求和公式 8

技巧12批量將文本型數字轉換成數值類型 8

技巧13讓大寫數字金額可用於計算 9

技巧14將公式結果轉換為數值 10

技巧15將公式轉換為文本 10

技巧16為循灑厚什麼更改數據後公式計算結果不

自動更新 11

技巧17為什麼顯示公式而不顯示計算結果 11

技巧18為什麼數字與“空”單元格相加

出錯 12

技巧19為什麼有時候匯總金額會比實際差

1分錢 12

技巧20隱藏公式 13

技巧21禁止任何人修改公式 15

1.2　函式操作基礎 16

技巧22快速輸入函式的3種方法 16

技巧23快速查找和學習某函式用法 18

技巧24函式參數的修改 18

技巧25分步理解函式的計算過程 19

技巧26函式出錯後的修改 20

技巧27嵌套函式 20

1.3　運算符優先權與通配符的使用 21

技巧28使用算術運算符 21

技巧29使用比較運算符 21

技巧30使用引用運算符 21

技巧31使用文本運算符 22

技巧32運算符的優先權順序 22

技巧33改變運算符的優先權方法 22

技巧34公式中使用通配符*、？ 23

技巧35公式中巧用通配符~ 23

1.4　數組公式 24

技巧36使用數組公式 24

技巧37數組的維數 25

技巧38普通公式與數組公式 25

技巧39單個單元格數組公式與多單元格數

組公式 26

技巧40修改多單元格數組公式 27

技巧41複製多單元格數組公式 27

技巧42常量數組在公式中的套用 27

技巧43多項計算與數組公式的區別 28

技巧44擴展或縮小多單元格數組公式 29

技巧45準確定位數組公式所在區域 29

1.5　公式計算錯誤值分析與處理 30

技巧46通過“監視視窗”來監視數據 30

技巧47注意括弧不匹配 30

技巧48注意循環引用 31

技巧49用“Excel幫助”獲取錯誤值的處理

方法 31

技巧50“#DIV/0!”錯誤值分析與處理 32

技巧51“#N/A”錯誤值分析與處理 32

技巧52“#NAME?”錯誤值分析與處理 32

技巧53“#NUM!”錯誤值分析與處理 33

技巧54“#VALUE!”錯誤值分析與

處理 33

技巧55“#REF!”錯誤值分嘗慨估析與處理 34

技巧56“#NULL!”錯誤值分析與

處理 34

2.1　數據源的引用 35

技巧1使用A1格式引用數據源 35

技巧2使用R1C1格式引用數據源 36

技巧3輸入引用地址 36

技巧4用滑鼠選擇引用地址 36

技巧5相對引用 37

技巧6絕對引用 37

技巧7混合引用 38

技巧8用“F4”章設鍵改變數據源引用類型 39

技巧9引用其他工作表中的數據 39

技巧10引用其他工作簿中的數據 40

技巧11引用多工作表中的同一單元格 40

技巧12更新外部公式 41

2.2　名稱定義與使用 42

技巧13為什麼要定義名稱 42

技巧14快速定義名稱 42

技巧15定義工作簿級與工作表級名稱 43

技巧16將常量定義為名稱並套用於

公式 44

技巧17將單元格區域定義為名稱並套用於

公式 44

技巧18將公式定義為名稱 45

技巧19名稱定義中“相對引用”與“絕對引

用”的區別 46

技巧20創建動態引用名稱 47

技巧21將整表創建為動態名稱並

使用 48

技巧22重新編輯定義的名稱 49

技巧23快速查看所有定義的名稱 50

技巧24快速刪除不用的名稱 50

AND（判斷多個條件是否同時成立） 51

OR（判斷多個條件是否到少有一個條件

成立） 53

NOT（對邏輯值求反） 54

IF（根據條件判斷返回指定的值） 55

IFERROR（根據公式返回的錯誤結果返回

指定的值） 62

TURE（返回邏輯值TRUE） 62

FALSE（返回邏輯值FALSE） 62

4.1　常規數學計算 63

ABS函式（求絕對值） 63

SUM（對給定區域的數據求和） 64

SUMIF（按照指定條件求和） 66

SUMIFS（對滿足多重條件的單元格求和） 71

SUMPRODUCT（將數組間對應的元素相乘，並返回乘積之和） 73

SUMTOTAL（返回列表或資料庫中的分類

匯總） 77

SUMSQ（計算所有參數的平方和） 79

SUMXMY2（求兩個數組中對應數值之差的

平方和） 79

SUMX2MY2（求兩個數組中對應數值的平方

差之和） 79

PRODUCT（求指定的多個數值的乘積） 80

MOD（求兩個數值相除後的餘數） 80

MROUND（求按指定基數捨入後的數值） 81

GCD（求兩個或多個整數的最大公約數） 82

LCM（計算兩個或多個整數的**小公倍數） 82

SQRT（求數據的算術平方根） 83

SQRTPI（計算某數與π的乘積的平方根） 83

4.2　數據的捨入 83

INT（將數字向下捨入到**接近的整數） 83

CEILING（向上捨入為**接近指定數值的

倍數） 84

CEILING.PRECISE（向上捨入數字） 84

FLOOR（向下捨入到**接近指定數值的

倍數） 85

FLOOR.PRECISE（向下捨入數字） 86

ROUND（對數據進行四捨五入） 86

ROUNDUP（遠離零值向上捨入計算） 88

ROUNDDOUWN（靠近零值向下捨入數值） 89

EVEN（將數字向上捨入到**接近的偶數） 90

ODD（將數字向上捨入到**接近的奇數） 90

QUOTIENT（返回商的整數部分） 91

TRUNC（將數字的小數部分截去返回整數） 91

4.3　階乘、隨機與矩陣計算 92

FACT（計算指定正數值的階乘） 92

FACTDOUBLE（返回數字的雙倍階乘） 92

MULTINOMIAL（返回參數和的階乘與各參

數階乘乘積的比值） 93

RAND（返回大於等於0小於1的隨機數） 93

RANDBETWEEN（返回指定數值之間的隨

機數） 93

MDETERM（返回一個數組的矩陣行列式

的值） 94

MINVERSE（返回數組矩陣的逆矩陣） 94

MMULT（返回兩個數組的矩陣乘積） 95

4.4　指數、對數與冪計算 95

EXP（返回e為底數指定指數的冪值） 95

LN（返回一個數的自然對數） 96

LOG（計算數值的對數值） 96

LOG10（計算以10為底數的對數值） 96

SERIESSUM(返回基於數字的冪級數之和） 97

POWER（返回給定數字的乘冪） 97

4.5　三角函式計算 98

SIN（返回某一角度的正弦值） 98

COS（返回某一角度的餘弦值） 98

TAN（返回某一角度的正切值） 99

SINH（返回給定數字的雙曲正弦值） 99

ASINH（返回給定數字的反雙曲正弦值） 99

ACOS（返回給定數字的反餘弦值） 100

ATAN（返回給定數字的反正切值） 100

ATAN2（返回直角坐標系中給定X及Y的反正

切值） 101

SIGH（返回數字的符號） 101

COSH（返回任意實數的雙曲餘弦值） 101

TANH（返回任意實數的雙曲正切值） 102

ACOSH（返回指定數值的反雙曲餘弦值） 102

ATANH（返回指定數值的反雙曲正切值） 103

PI（返回圓周率π） 103

RADIANS（將角度轉換為弧度） 103

DEGREES（將弧度轉換為角度） 104

4.6　其他函式 98

ROMAN（將任意阿拉伯數字轉換為羅馬

數字） 104

5.1返回當前日期和時間 105

NOW（返回當前日期和時間） 105

TODAY（返回當前日期） 106

5.2日期和時間處理 107

DATE（返回日期的序列號） 107

YEAR（返回指定日期對應的年份） 109

MONTH（返回指定日期中的月份） 110

DAY（返回指定日期中的天數） 111

WEEKDAY（返回指定日期對應的星期數） 113

EDATE（計算出與指定日期相隔指定

月份數的日期） 114

EOMONTH（根據給定日期返回此月最後

一天的日期） 114

WORKDAY（根據給定日期返回與此日期

間隔指定工作日的日期） 115

WEEKNUM（返回給定日期是一年中的第

幾周） 116

HOUR（返回時間值的小時數） 116

MINUTE（返回時間值的分鐘數） 118

SECOND（返回時間值的秒數） 119

TIME（返回某一特定時間的小數值） 120

5.3日期差值計算 121

DATEDIF（用指定的單位計算起始日和

結束日之間的天數） 121

DAYS360（按照一天360天的算法計算

兩日期間相差的天數） 123

NETWORKDAYS（計算某時段的工作日

天數） 123

YEARFRAC（返回兩個日期之間的天數

占全年天數的比例） 124

5.4文本與日期、時間格式轉換 125

DATEVALUE（將文本日期轉換為可識別

的日期序列號） 125

TIMEVALUE（將時間轉換為對應的小

數值） 126

6.1查找字元串並返回所在位置 127

LEFT（按指定字元數從**左側提取字

符串） 127

LEFTB（按指定位元組數從**左側提取字

符串） 130

MID（從任意位置提取指定字元數的字

符串） 130

MIDB（從任意位置提取指定位元組數的

字元） 131

FIND（查找指定字元在一個字元串中的

位置） 131

FINDB（查找指定字元在一個字元串中

的位置“按位元組算”） 133

RIGHT（按指定字元數從**右側提取字

符串） 133

RIGHTB（按指定位元組數從**右側提取字

符串） 134

SEARCH（查找字元串字元起始位置） 134

SEARCHB（查找字元串字元起始位置

“按位元組算”） 135

6.2文本格式的轉換 136

TEXT（設定數字格式，並將其轉換為文本） 136

DOLLAR（四捨五入數值，並添加千分位

符號和$符號） 138

RMB（四捨五入數值，並添加千分位

符號和￥符號） 138

FIXED（將數字按指定的位數取整並以

文本形式返回） 139

UPPER（將文本轉換為大寫形式） 139

LOWER（將文本轉換為小寫形式） 140

PROPER（將文本字元串的首字母轉換成

大寫） 140

CODE（返回文本字元串中第一個字元的

數字代碼） 141

CHAR（返回對應於數字代碼的字元） 141

VALUE（將文本字轉換成數值） 141

T（將給定內容轉換為文本） 142

6.3文本的其他操作 142

CONCATENATE（合併兩個或多個文本字

符串） 142

LEN（返回文本字元串的字元數量） 144

LENB（返回文本字元串的位元組數量） 146

REPLACE（用指定的字元和字元數替換

文本字元串中的部分文本） 146

REPLACEB（用指定的字元和位元組數替換

文本字元串中的部分文本） 148

SUBSTITUTE（替換舊文本） 148

EXACT（比較兩個文本字元串是否完全

相同） 151

REPT（按照給定的次數重複顯示文本） 151

TRIM（刪除文本中的多餘空格） 152

CLEAN（刪除文本中不能列印的字元） 152

7.1查找數據 154

CHOOSE（從給定參數列表中選擇值） 154

HLOOKUP（查找數組的首行，並返回

指定單元格的值） 156

LOOKUP（從單行或單列區域返回值-

向量型） 157

LOOKUP（從單行或單列區域返回值-

數組型） 160

VLOOKUP（在給定單元格區域首列查找

並返回該區域上同行的上值） 161

MATCH（返回指定值在指定區域中的位置） 164

INDEX（使用索引從引用或數組中選擇值） 165

GETPIVOTDATA（返回存儲在數據透視表

中的數據） 169

7.2引用數據 171

AREAS（返回引用中涉及的區域個數） 171

ADDRESS（建立文本類型的單元格

地址） 171

COLUMN（返回引用的列號） 172

COLUMNS（返回引用中包含的列數） 173

ROW（返回引用的行號） 174

ROWS（返回引用中包含的行數） 176

INDIRECT（返回由文本字元串指定的引用） 176

OFFSET（按指定引用與偏移量返回新的

引用） 177

TRANSPOSE（返迴轉置單元格區域） 179

HYPERLINK（創建一個可以打開存在於

網路伺服器中的檔案的捷徑） 179

8.1返回信息與類型值 181

CELL（返回有關單元格格式、位置或內容的

信息） 181

INFO（返回當前操作環境的信息） 182

TYPE（返回單元格內的數值類型） 183

ERROR.TYPE（返回與錯誤值對應的數字） 184

N（將參數轉換為數值） 184

NA（返回錯誤值 #N/A） 185

8.2使用IS函式進行判斷 186

ISBLANK（如果值為空，則返回TRUE） 186

ISNUMBER（如果值為數字，則返回

TRUE） 188

ISTEXT（如果值為文本，則返回TRUE） 190

ISEVEN（如果數字為偶數，則返回TRUE） 191

ISODD（如果數字為奇數，則返回TRUE） 192

ISERROR（如果值為任何錯誤值，則返回

TRUE） 193

ISERR（判斷值是否為除#N/A之外的其他

任意錯誤值） 194

ISLOGICAL（檢測一個值是否為邏輯值） 196

ISNA（檢測一個值是否為#N/A錯誤值） 196

ISNONTEXT（檢測一個值是否不是文本） 197

ISREF（檢測一個值是否為引用） 198

9.1平均值計算 199

AVERAGE（計算算術平均值） 199

AVERAGEA（返回平均值） 202

AVERAGEIF（返回滿足條件的平均值） 202

AVERAGEIFS（返回滿足多重條件的平

均值） 204

GEOMEAN（返回幾何平均值） 206

HARMEAN（返回數據集的調和平均值） 206

TRIMMEAN（去除數據集頭尾求平均值） 207

9.2數目統計函式 207

COUNT（統計單元格區域中數字的個數） 207

COUNTA（返回單元格區域中任意值的

個數） 210

COUNTIF（統計滿足給定條件的單元格

個數） 211

COUNTIFS（統計滿足多條件的單元格

個數） 212

COUNTBLANK（統計空白單元格的個數） 214

9.3最大值與最小值函式 214

MIN（返回數據集的最小值） 214

MAX（返回數據集的最大值） 216

MAXA（參數含文本與邏輯值時返回**

大值） 218

MINA（參數含文本與邏輯值時返回最小值） 219

LARGE（返回數據集中某個最大值） 219

SMALL（返回數據集的某個最小值） 220

9.4排位統計函式 221

MEDIAN（返回中位數） 221

PERCENTILE.INC（返回第k個百分點值） 222

PERCENTRANK.INC（返回百分比排位） 222

QUARTILE.INC（返回四分位數） 223

PERMUT（返回排列數） 224

RANK.EQ（返回一個數字在數字列表中的

排位） 224

RANK.AVG（返回一個數字在數字列表中的

排位） 225

PERCENTRANK.EXC（返回數值在一個

數據集中的百分比） 226

9.5方差、協方差與偏差 226

COVARIANCE.P（返回總體協方差） 226

COVARIANCE.S（返回樣本協方差） 227

DEVSQ（返回平均值偏差的平方和） 227

STDEV.S（計算基於樣本估算標準偏差） 228

STDEVA（計算基於給定樣本的標準偏差） 228

STDEVPA（計算樣本總體的標準偏差） 229

VARA（估算給定樣本的方差） 229

VARPA（計算樣本總體的方差） 229

VAR.S（估算基於樣本的方差） 230

VAR.P（計算基於整個樣本總體的方差） 230

9.6數據檢驗 231

CHISQ.TEST（返回獨立性檢驗值） 231

F.TEST（返回F檢驗的結果） 231

FREQUENCY（計算數值在某個區域內的

出現頻率） 231

9.7回歸分析 233

FORECAST（根據已有的數值計算或預測

未來值） 233

GROWTH（對給定的數據預測指數增長值） 234

LINEST（返回描述直線擬合的數組） 234

INTERCEPT（計算直線與y軸的截距） 235

LOGEST（回歸擬合曲線返回該曲線的數值） 235

SLOPE（求擬合的線性回歸直線的斜率） 236

STEYX（返回預測值時產生的標準誤差） 236

TREND（返回一條線性回歸擬合線的值） 237

9.8相關係數 237

CORREL（返回兩個不同事物之間的相

關係數） 237

PEARSON（返回Pearson乘積矩相關係數） 238

RSQ（返回皮爾生乘積矩相關係數的平方） 238

9.9機率分布 239

BETA.DIST（返回beta分布） 239

BINOM.INV（返回二項式分布機率） 239

BETA.INV（返回Beta累積機率密度函式的反

函式） 240

BINOM.DIST（返回一元二項式分布的機率） 240

CHISQ.DIST.RT（返回X2分布的單尾機率） 240

CHISQ.INV（返回X2分布的左尾機率的

反函式） 241

CHISQ.INV.RT（返回X2分布的右尾機率的

反函式） 241

EXPON.DIST（返回指數分布） 241

F.DIST（返回F機率分布函式值） 242

F.DIST.RT（返回F機率分布） 242

F.INV（返回F機率分布函式的反函式） 243

F.INV.RT（返回F機率分布的反函式值） 243

GAMMA.DIST（返回伽馬分布函式的

函式值） 243

GAMMA.INV（返回伽馬累積分布函式的

反函式值） 244

GAMMALN（返回伽馬函式的自然對數） 244

HYPGEOM.DIST（返回超幾何分布） 245

KURT（返回數據集的峰值） 245

LOGNORM.INV（返回x的對數累積分布

函式的反函式） 245

LOGNORM.DIST（返回x的對數累積分布

函式） 246

MODE.MULT（返回數據區域中出現頻率最高的數值數組） 246

MODE.SNGL（返回數組中出現頻率**多的

數值） 247

NEGBINOM.DIST（返回負二項式分布） 247

NORM.S.DIST（返回標準常態分配的累積

函式） 248

NORM.INV（返回正態累積分布的

反函式） 248

NORM.DIST（返回指定平均值和標準偏差的

常態分配函式） 248

NORM.S.INV（返回標準正態累積分布函式的

反函式） 249

PROB（返回數值落在指定區間內的機率） 249

POISSON.DIST（返回泊松分布） 250

SKEW（返回分布的偏斜度） 250

STDEV.P（返回整個樣本總體計算標準

偏差） 251

T.DIST（返回學生的左尾t分布） 251

T.DIST.2T（返回學生的雙尾t分布） 252

T.DIST.RT（返回學生的右尾t分布） 252

T.INV.2T（返回學生t分布的雙尾反函式） 252

T.INV（返回學生t分布的左尾反函式） 253

T.TEST（返回與學生t檢驗相關的機率） 253

WEIBULL.DIST（返回韋伯分布） 253

Z.TEST（返回z檢驗的單尾P值） 254

9.10其他統計函式 255

CONFIDENCE.NORM（使用常態分配返回

總體平均值的置信區間） 255

CONFIDENCE.T（使用學生的t分布返回

總體平均值的置信區間） 255

10.1投資計算函式 257

PMT（計算貸款的每期付款額） 257

PPMT（返回給定期間內的本金額） 258

IPMT（返回給定期限內的利息額） 259

ISPMT（計算投資期內的利息額） 260

FV（返回某項投資的未來值） 260

FVSCHEDULE（計算某項投資在變動或

可調利率下的未來值） 261

PV（返回投資的現值） 262

NPV（返回一項投資的淨現值） 262

XNPV（返回一組不定期現金流的淨現值） 263

NPER（返回某項投資的總期數） 263

10.2　償還率計算函式 264

IRR（計算內部收益率） 264

MIRR（計算修正內部收益率） 264

XIRR（計算內部收益率） 265

RATE（返回年金的各期利率） 265

10.3　資產折舊計算函式 266

AMORDEGRC（計算會計期間的折舊值） 266

AMORLINC（返回每個會計期間的折舊值） 267

DB（固定餘額遞減法計算折舊值） 267

DDB（雙倍餘額遞減法計算折舊值） 268

VDB（雙倍餘額遞減法計算指定期間

折舊值） 269

SLN（返回線性折舊值） 270

SYD（年限總和法計算折舊值） 271

10.4　轉換美元價格的格式 272

DOLLARDE（將價格轉換為小數表示） 272

DOLLARFR（將價格轉換為分數表示） 272

10.5　證券與國券 273

ACCRINT（返回定期付息有價證券的利息） 273

ACCRINTM（返回到期一次性付息有價

證券的利息） 273

CUMPRINC（返回貸款在給定的兩個期間

累計償還的本金數額） 274

CUMIPMT（返回貸款在給定的兩個期間

累計償還的利息數額） 274

COUPDAYBS（返回付息期到成交日的

天數） 275

COUPDAYS（返回成交日所在付息期天數） 275

COUPDAYSNC（返回從成交日到下一付息日

之間的天數） 276

COUPNCD（返回一個表示在成交日之後

下一個付息日） 276

COUPNUM（返回成交日和到期日之間的

利息應付次數） 277

COUPPCD（返回成交日之前的上一付息日

日期） 277

DISC（返回貼現率） 278

DURATION（返回有價證券的修正期限） 278

EFFECT（計算實際年利率） 279

NOMINAL（返回名義年利率） 279

INTRATE（返回一次性付息證券的利率） 279

MDURATION（返回有價證券的Macauley修正

期限） 280

ODDFPRICE（返回首期付息日不固定的

有價證券的價格） 280

ODDFYIELD（返回首期付息日不固定的

有價證券的收益率） 281

ODDLPRICE（返回末期付息日不固定的

有價證券的價格） 282

ODDLYIELD（返回末期付息日不固定的

有價證券的收益率） 282

PRICE（返回定期付息有價證券的價格） 283

PRICEDISC（返回折價發行有價證券的

價格） 283

PRICEMAT（返回到期付息有價證券的

價格） 284

RECEIVED（返回有價證券到期收回的

金額） 284

TBILLEQ（返回國庫券的等效收益率） 285

TBILLYIELD（返回國庫券的收益率） 285

TBILLPRICE（返回面值$100的國庫券的

價格） 286

YIELD（返回定期付息有價證券的收益率） 286

YIELDDISC（返回折價發行的有價證券的

年收益率） 287

YIELDMAT（返回到期付息的有價證券的

年收益率） 287

11.1常規數據統計 288

DSUM（返回滿足條件的數字之和） 288

DAVERAGE（對滿足條件的數據求平均值） 291

DPRODUCT（對滿足條件數值求乘積） 293

DGET（提取符合指定條件的單個值） 293

DMIN（返回滿足條件的最小值） 294

DMAX（返回滿足條件的最大值） 296

DCOUNT（統計滿足條件的單元格個數） 297

DCOUNTA（統計非空單元格的個數） 299

11.2　散布度統計 301

DSTDEV（返回滿足條件的數字作為一個

樣本估算出的總體標準偏差） 301

DSTDEVP（返回滿足條件的數字作為樣本

總體計算出的總體標準偏差） 301

DVAR（返回滿足條件的數字作為一個

樣本估算出的總體方差） 302

DVARP（返回滿足條件的數字作為樣本

總體計算出的樣本總體方差） 303

Excel 2010函式與公式綜合套用技巧（雙色精華版）

基本介紹

內容簡介

圖書目錄

相關詞條

熱門詞條