Excel函数应用.docx

Excel函数应用.docx

《Excel函数应用.docx》由会员分享，可在线阅读，更多相关《Excel函数应用.docx（10页珍藏版）》请在冰豆网上搜索。

Excel函数应用

目录

第1章　Excel函数基础　1

1.1　Excel2003的基础知识　1

1.1.1　工作簿、工作表与单元格　1

1.1.2　函数与参数　3

1.2　单元格的引用　3

1.2.1　A1和R1C1引用样式　3

1.2.2　绝对引用、相对引用和混合引用　5

1.2.3　三维引用　8

1.3　使用公式　10

1.3.1　公式的基本元素　10

1.3.2　常见运算符　10

1.3.3　公式的使用　12

1.3.4　为公式命名　14

1.4　使用函数　16

1.5　函数的分类　19

第2章　数学与三角函数　21

1．返回数字的绝对值：

ABS　21

2．返回数字的反余弦值：

ACOS　21

3．返回数字的反双曲余弦值：

ACOSH　22

4．返回数字的反正弦值：

ASIN　22

5．返回数字的反双曲正弦值：

ASINH　23

6．返回数字的反正切值：

ATAN　24

7．返回给定坐标值的反正切值：

ATAN2　24

8．返回数字的反双曲正切值：

ATANH　25

9．按条件向上舍入：

CEILING　26

10．计算组合数：

COMBIN　27

11．计算余弦值：

COS　28

12．计算数字的双曲余弦值：

COSH　28

13．将弧度转换为度：

DEGREES　29

14．将数字舍入为偶数：

EVEN　29

15．返回e的n次幂：

EXP　30

16．计算数字的阶乘：

FACT　31

17．计算数字的双倍阶乘：

FACTDOUBLE　31

18．按条件向下舍入：

FLOOR　32

19．返回最大公约数：

GCD　33

20．将数字向下舍入取整：

INT　34

21．返回最小公倍数：

LCM　34

22．返回数字的自然对数：

LN　35

23．返回指定底数的对数：

LOG　35

24．返回以10为底的对数：

LOG10　36

25．返回数组的矩阵行列式值：

MDETERM　36

26．返回数组矩阵的逆矩阵：

MINVERSE　37

27．返回矩阵的乘积：

MMULT　39

28．求两数的余数：

MOD　39

29．返回按指定基数舍入数值：

MROUND　40

30．返回和的阶乘与阶乘乘积的比值：

MULTINOMIAL　41

31．返回舍入奇数：

ODD　42

32．返回数学常量：

PI　43

33．返回数字的乘幂：

POWER　43

34．返回乘积值：

PRODUCT　44

35．返回商的整数部分：

QUOTIENT　45

36．返回弧度值：

RADIANS　45

37．返回随机数：

RAND　46

38．返回指定两数之间的随机数：

RANDBETWEEN　46

39．转换成文本形式罗马数字：

ROMAN　47

40．返回按指定位数四舍五入的数字：

ROUND　48

41．向下舍入数字：

ROUNDDOWN　48

42．向上舍入数字：

ROUNDUP　49

43．返回幂级数之和：

SERIESSUM　50

44．返回数字的符号：

SIGN　50

45．返回正弦值：

SIN　51

46．返回双曲正弦值：

SINH　52

47．计算平方根：

SQRT　52

48．返回与PI乘积的平方根：

SQRTPI　53

49．返回分类汇总计算：

SUBTOTAL　53

50．对数字求和：

SUM　55

51．按条件求和：

SUMIF　56

52．返回对应元素的乘积之和：

SUMPRODUCT　57

53．求平方和：

SUMSQ　58

54．求平方差之和：

SUMX2MY2　58

55．求平方和之和：

SUMX2PY2　59

56．求对应数值差的平方和：

SUMXMY2　60

57．计算正切值：

TAN　61

58．求双曲正切：

TANH　62

59．取整函数：

TRUNC　62

第3章　日期与时间函数　64

1．返回代表特定日期的序列号：

DATE　64

2．返回两个日期之间的差值：

DATEDIF　65

3．返回文本日期的系列数：

DATEVALUE　67

4．返回以系列数表示的日期的天数：

DAY　68

5．按每年360天返回两个日期相差的天数：

DAYS360　68

6．返回某个日期之前或之后指定月份数的日期系列：

EDATE　69

7．返回某个日期之前或之后指定月份最后一天的序列号：

EOMONTH　70

8．返回时间值的小时数：

HOUR　71

9．返回时间值的分钟数：

MINUTE　72

10．返回以序列号表示的日期中的月份：

MONTH　72

11．返回两个日期之间完整的工作日数：

NETWORKDAYS　73

12．返回当前日期和时间对应的序列号：

NOW　74

13．返回时间值的秒数：

SECOND　75

14．返回特定时间的小数值：

TIME　75

15．返回文本字符串代表的时间值：

TIMEVALUE　76

16．返回当前日期的序列号：

TODAY　77

17．返回某个日期的星期数：

WEEKDAY　77

18．返回某个日期在一年中是第几周：

WEEKNUM　78

19．返回起始日期之前或之后相隔指定工作日的日期值：

WORKDAY　79

20．返回日期对应的年份：

YEAR　80

21．返回两个日期之间的天数占全年总天数的百分比：

YEARFRAC　81

第4章　逻辑函数　83

1．返回逻辑值交集的计算结果：

AND　83

2．返回逻辑值FALSE：

FALSE　83

3．返回逻辑判断结果：

IF　84

4．返回数字的相反值：

NOT　85

5．返回逻辑值并集的计算结果：

OR　86

6．返回逻辑值TRUE：

TRUE　87

第5章　查找与引用函数　88

1．查找与引用函数：

ADDRESS　88

2．返回引用中包含的区域个数：

AREAS　89

3．选出参数列表中的数值：

CHOOSE　90

4．返回给定引用的列标：

COLUMN　90

5．返回数组或者引用的列数：

COLUMNS　91

6．在首行查找指定的数值：

HLOOKUP　92

7．创建快捷方式（跳转）：

HYPERLINK　93

8．返回指定内容：

INDEX　95

9．返回由文本字符串指定的引用：

INDIRECT　98

10．查找数据：

LOOKUP　99

11．在数组中查找值的位置：

MATCH　101

12．返回引用的偏移：

OFFSET　102

13．返回引用的行号：

ROW　103

14．返回引用或者数组的行数：

ROWS　104

15．返回实时数据：

RTD　105

16．返回转置单元格区域：

TRANSPOSE　106

17．垂直查找：

VLOOKUP　106

第6章　文本函数　109

1．将全角字符转换为半角字符：

ASC　109

2．转换为泰语文本：

BAHTTEXT　109

3．返回数字代码的字符：

CHAR　110

4．删除不能打印的字符：

CLEAN　111

5．返回字符的数字代码：

CODE　111

6．合并字符串：

CONCATENATE　112

7．转换为货币文本：

DOLLAR　113

8．转换为货币文本：

RMB　114

9．比较两个字符串是否相同：

EXACT　114

10．查找文本字符串：

FIND　115

11．查找文本字符串：

FINDB　116

12．按小数位取整并返回文本格式：

FIXED　116

13．按指定字符数返回左边字符串：

LEFT　117

14．按指定字节数返回左边字符串：

LEFTB　118

15．返回字符串的字符数：

LEN　119

16．返回字符串的字节数：

LENB　119

17．将文本转换为小写：

LOWER　120

18．返回特定字符：

MID　120

19．返回特定字符：

MIDB　121

20．提取文本中的拼音字符：

PHONETIC　122

21．将部分字符转换为大写：

PROPER　123

22．替换文本：

REPLACE　123

23．替换文本：

REPLACEB　124

24．重复显示文本：

REPT　125

25．返回右边字符串：

RIGHT　125

26．返回右边字符串：

RIGHTB　126

27．查找字符串：

SEARCH　127

28．查找字符串：

SEARCHB　128

29．替换指定文本：

SUBSTITUTE　129

30．返回文本：

T　129

31．将数值转换为文本：

TEXT　130

32．清除文本中的空格：

TRIM　131

33．将文本转换为大写：

UPPER　132

34．将文本转换为数字：

VALUE　132

35．将半角转换为全角：

WIDECHAR　133

第7章　信息函数　135

1．返回单元格信息：

CELL　135

2．空白单元格数：

COUNTBLANK　138

3．判断错误类型：

ERROR.TYPE　138

4．当前操作环境的信息：

INFO　139

5．判断是否为错误值：

ISBLANK　140

6．判断是否为错误值：

ISERR　141

7．判断是否为任意错误值：

ISERROR　142

8．判断是否为逻辑值：

ISLOGICAL　142

9．判断是否为错误值#N/A：

ISNA　143

10．判断是否为非文本：

ISNONTEXT　143

11．判断是否为数字：

ISNUMBER　144

12．判断是否为引用：

ISREF　144

13．判断是否为逻辑值：

ISTEXT　145

14．判断参数是否为偶数：

ISEVEN　146

15．判断参数是否为奇数：

ISODD　146

16．转换为数值：

N　147

17．返回错误值＃N/A：

NA　147

18．返回数值的类型：

TYPE　148

第8章　数据库函数　150

1．条目的平均值：

DAVERAGE　150

2．符合条件的数字单元格数：

DCOUNT　151

3．符合条件的非空单元格数：

DCOUNTA　152

4．符合条件的单个值：

DGET　153

5．符合条件的最大值：

DMAX　154

6．符合条件的最小值：

DMIN　155

7．符合条件的数值的乘积：

DPRODUCT　156

8．作为样本计算标准偏差：

DSTDEV　157

9．作为样本计算标准偏差：

DSTDEVP　158

10．符合条件的数值之和：

DSUM　158

11．作为样本计算方差：

DVAR　159

12．作为样本总体计算方差：

DVARP　160

13．检索汇总数据：

GETPIVOTDATA　161

第9章　工程函数　164

1．修正Bessel函数值：

BESSELI　164

2．返回Bessel函数值：

BESSELJ　165

3．返回Bessel函数值：

BESSELK　166

4．返回Bessel函数值：

BESSELY　166

5．二进制转换为十进制：

BIN2DEC　167

6．二进制转换为十六进制：

BIN2HEX　168

7．二进制转换为八进制：

BIN2OCT　169

8．转换为复数：

COMPLEX　170

9．将数字转换到不同的度量系统：

CONVERT　171

10．十进制转换为二进制：

DEC2BIN　174

11．十进制转换为十六进制：

DEC2HEX　175

12．十进制转换为八进制：

DEC2OCT　176

13．测试两个数值是否相等：

DELTA　177

14．求误差函数在上下限之间的积分：

ERF　178

15．求ERF函数的余误差函数：

ERFC　178

16．判断某值是否大于等于一个特定值：

GESTEP　179

17．十六进制转换为二进制：

HEX2BIN　180

18．十六进制转换为十进制：

HEX2DEC　181

19．十六进制转换为八进制：

HEX2OCT　182

20．求复数的绝对值：

IMABS　183

21．返回复数的虚系数：

IMAGINARY　184

22．返回以弧度表示的角：

IMARGUMENT　184

23．返回共轭复数：

IMCONJUGATE　185

24．求复数的余弦：

IMCOS　186

25．求两个复数的商：

IMDIV　186

26．求复数的指数：

IMEXP　187

27．求复数的自然对数：

IMLN　187

28．求复数的常用对数：

IMLOG10　188

29．求复数以2为底的对数：

IMLOG2　189

30．求复数的n次幂：

IMPOWER　190

31．求复数的乘积：

IMPRODUCT　191

32．返回复数的实系数：

IMREAL　192

33．求复数的正弦值：

IMSIN　192

34．求复数的平方根：

IMSQRT　193

35．求两个复数的差：

IMSUB　194

36．求两个复数的和：

IMSUM　194

37．八进制转换为二进制：

OCT2BIN　195

38．八进制转换为十进制：

OCT2DEC　196

39．八进制转换为十六进制：

OCT2HEX　197

第10章　统计函数　199

1．计算绝对偏差绝对值：

AVEDEV　199

2．计算算术平均值：

AVERAGE　199

3．计算非空单元格的平均值：

AVERAGEA　200

4．返回Beta累积分布函数：

BETADIST　201

5．返回Beta累积分布函数的反函数：

BETAINV　202

6．返回一元二项式分布的概率值：

BINOMDIST　203

7．返回r2分布的单尾概率：

CHIDIST　204

8．返回r2分布单尾概率的反函数值：

CHIINV　205

9．返回独立性检验值：

CHITEST　206

10．返回平均值的置信区间：

CONFIDENCE　208

11．返回两组数值的相关系数：

CORREL　208

12．计算数值单元格的个数：

COUNT　210

13．计算非空值的单元格的个数：

COUNTA　211

14．计算空白单元格的个数：

COUNTBLANK　211

15．计算满足给定条件的单元格的个数：

COUNTIF　212

16．协方差：

COVAR　213

17．返回累积二项式分布大于等于临界值的最小值：

CRITBINOM　214

18．求平均值偏差的平方和：

DEVSQ　215

19．求指数分布：

EXPONDIST　216

20．求F概率分布：

FDIST　217

21．求F概率分布的反函数值：

FINV　218

22．返回点x的Fisher变换：

FISHER　219

23．返回Fisher变换的反函数值：

FISHERINV　220

24．根据已有的数值返回未来值：

FORECAST　220

25．返回数据的频率分布：

FREQUENCY　221

26．返回F检验的结果：

FTEST　222

27．计算伽玛分布：

GAMMADIST　224

28．返回伽玛分布的反函数：

GAMMAINV　225

29．计算伽玛函数的自然对数：

GAMMALN　225

30．计算几何平均值：

GEOMEAN　226

31．预测指数增长值：

GROWTH　227

32．计算数据集合的调和平均值：

HARMEAN　228

33．返回超几何分布：

HYPGEOMDIST　229

34．求截距：

INTERCEPT　230

35．返回数据集的峰值：

KURT　232

36．返回数据集中第k个最大值：

LARGE　233

37．对已知数据进行最佳直线拟合：

LINEST　233

38．计算最佳指数回归拟合曲线：

LOGEST　235

39．返回x的对数累积分布函数的反函数：

LOGINV　237

40．返回x的对数累积分布函数：

LOGNORMDIST　238

41．返回一组值中的最大值：

MAX　239

42．返回参数列表中的最大值：

MAXA　240

43．返回给定数值集合的中值：

MEDIAN　241

44．返回一组值中的最小值：

MIN　241

45．返回参数列表中的最小值：

MINA　242

46．返回出现频率最多的数值：

MODE　243

47．返回负二项式分布：

NEGBINOMDIST　244

48．返回正态分布函数：

NORMDIST　245

49．返回正态累计分布函数的反函数：

NORMINV　246

50．返回标准正态分布的累积函数：

NORMSDIST　247

51．返回标准正态分布累积函数的反函数：

NORMSINV　248

52．返回Pearson乘积矩相关系数：

PEARSON　249

53．返回数值的第K个百分点的值：

PERCENTILE　250

54．返回特定数值的百分比排位：

PERCENTRANK　251

55．返回对象集合中选取若干个对象的排列数：

PERMUT　252

56．返回泊松分布：

POISSON　253

57．返回特定数值落在某区间内的概率：

PROB　254

58．返回数据集的四分位数：

QUARTILE　255

59．返回数字在列表中的排位：

RANK　256

60．返回Pearson乘积矩相关系数的平方：

RSQ　258

61．返回分布的偏斜度：

SKEW　258

62．返回拟合的线性回归直线的斜率：

SLOPE　260

63．返回数据集中第K个最小值：

SMALL　261

64．返回正态分布的数值：

STANDARDIZE　262

65．返回样本的标准偏差：

STDEV　263

66．返回给定样本的标准偏差：

STDEVA　264

67．返回样本总体的标准偏差：

STDEVP　265

68．返回给定样本总体的标准偏差：

STDEVPA　266

69．返回标准偏差：

STEYX　267

70．返回学生T分布的概率：

TDIST　268

71．返回学生T分布的T值：

TINV　270

72．返回线性回归拟合线的Y值：

TREND　271

73．返回数据集的内部平均值：

TRIMMEAN　272

74．返回学生T检验的概率值：

TTEST　273

75．返回样本的方差：

VAR　275

76．返回给定样本的方差：

VARA　276

77．返回样本总体的方差：

VARP　277

78．返回给定样本总体的方差：

VARPA　278

79．返回韦伯分布：

WEIBULL　279

80．返回Z检验的单尾概率值：

ZTEST　280

第11章　财务函数　282

1．返回有价证券的应计利息：

ACCRINT　282

2．返回一次性付息证券的应计利息：

ACCRINTM　283

3．返回每个折旧期间的折旧值：

AMORDEGRC　285

4．按线性折旧法计算折旧值：

AMORLINC　286

5．返回截止到成交日的天数：

COUPDAYBS　288

6．返回成交日所在的付息期的天数：

COUPDAYS　289

7．返回成交日到下一个付息日之间的天数：

COUPDAYSNC　289

8．返回成交日之后下一个付息日的数字：

COUPNCD　290

9．返回成交日和到期日之间的付息次数：

COUPNUM　291

10．返回成交日之前的上一个付息日：

COUPPCD　292

11．返回某个期间内累计要偿还的利息数额：

CUMIPMT　293

12．返回某个期间内累计要偿还的本金数额：

CUMPRINC　294

13．返回用固定余额递减法计算的资产的折旧值：

DB　295

14．返回用双倍余额递减法计算的折旧值：

DDB　296

15．返回有价证券的贴现率：

DISC　298

16．将按分数表示的价格转换为小数：

DOLLARDE　299

17．将按小数表示的价格转换为分数：

DOLLARFR　299

18．返回有价证券的修正期限：

DURATION　300

19．计算有效的年利率：

EFFECT　301

20．返回投资的未来值：

FV　302

21．返回本金的未来值：

FVSCHEDULE　304

22．返回一次性付息证券的利率：

INTRATE　305

23．返回利息偿还额：

IPMT　306

24．返回一组现金流的内部收益率：

IRR　307

25．返回特定投资期内要支付的利息：

ISPMT　309

26．返回有价证券的Macauley修正期限：

MDURATION　309

27．返回修正内部收益率：

MIRR　310

28．返回名义年利率：

NOMINAL　311

29．返回投资的总期数：

NPER　312

30．返回投资的净现值：

NPV　314

31．返回有价证券的价格：

ODDFPRICE　315

32．返回有价证券的收益率：

ODDFYIELD　316

33．返回末期付息日不固定的有价证券的价格：

ODDLPRICE　317

34．返回末期付息日不固定的有价证券的收益率：

ODDLYIELD　319

35．返回贷款的每期付款额：

PMT　320

36．返回某一个给定期间内的本金偿还额：

PPMT　321

37．返回定期付息的有价证券的价格：

PRICE　322

38．返回折价发行的有价证券的价格：

PRICEDISC　323

39．返回到期付息的有价证券的价格：

PRICEMAT　324

40．返回投资的现值：

PV　325

41．返回年金的各期利率：

RATE　327

42．返回到期收回的总金额：

RECEIVED　327

43．返回资产的线性折旧值：

SLN　329

44．返回按年限总和折旧法计算的折旧值：

SYD　330

45．返回国库券的等效收益率：

TBILLEQ　330

46．返回国库券的价格：

TBILLPRICE　331

47．返回国库券的收益率：

TBILLYIELD　332

48．返回指定期间的资产折旧值：

VDB　333

49．返回一组现金流的内部收益率：

XIRR　335

50．返回现金流的净现值：

XNPV　336

51．返回有价证券的收益率：

YIELD　338

52．返回折价发行的有价证券的年收益率：

YIELDDISC　339

53．返回到期付息的有价证券的年收益率：

YIELDMAT　340

附录　函数应用常见技巧　342

1．如何进行单元格的引用？

　342

2．如何输入18位身份证号码？

　342

3．如何自动计算应收账款滞纳金？

　343

4．如何在Excel中统计某个数的位置（在哪一行）？

　343

5．如何在工作表之间查找相同的内容并将其复制到新的工作表中？

　343

6．如何统计偶数单元格中的数据之和？

　344

7．如何将数字直接转换为日期？

　344

8．如何进行列转换？

　344

9．如何快速地计算年龄？

　345

10．如何对列进行跳跃求和？

　347

11．如何对公式进行排列，使其清晰易懂？

　347

12．如何进行大小写转换？

　347

13．如何筛选出特定的行？

　348

14．如何以数组的形式输出结果？

　348

15．如何加载【分析工具库】？

　348

16．如何将某一个函数作为另一个函数的参数来使用？

　348

17．如何快速地选取工作表中所有包含公式的单元格？

　349

18．如何简化求平均值的公式？

　350

19．如何利用函数实现公式无法实现的计算？

　351

20．如何将整个行或整个列作为参数？

　352

21．如何把文字值作为参数？

　352

22．如何把表达式作为参数？

　353

23．如何将数组作为参数？

　353

24．如何提高编辑任务的速度？

　353

25．如何利用函数实现判断功能？

　354

26．如何自动地输入函数？

　355

EXCEL混合引用介绍一下

在公式和函数中，经常要引用某一单元格或单元格区域中的数据，单元格的引用是使用工作表中的列标和行号，即单元格的地址。

单元格的引用分3种：

相对引用、绝对引用和混合引用。

要知道混合引用，首先要了解相对引用和绝对引用。

1、相对引用

相对引用表示某一单元格相对于当前单元格的相对位置。

例如，在E9单元格中输入公式"＝E4＋E5＋E6＋E7＋E8"后，自动填充F9将得到"＝F4＋F5＋F6＋F7＋F8"的值。

在对E9和F9单元格填充公式时，公式中引用的单元格地址发生了变化。

2、相对引用

绝对引用表示某一单元格在工作表中的绝对位置。

绝对引用要在行号和列标前加一个＄

符