excel函数应用实例详解 表格函数的使用方法及实例

很多朋友对于excel函数应用实例详解和表格函数的使用方法及实例不太懂，今天就由小编来为大家分享，希望可以帮助到大家，下面一起来看看吧！

EXCEL函数应用实例详解的目录

第1章 Excel公式基础 1

1-1 Excel中的公式应用 1

1-1-1在工作表中输入公式 2

1-1-2在工作表中编辑公式 3

1-1-3在公式中使用运算符 3

1-1-4在公式中使用Excel内置函数 6

1-1-5计算Excel公式 7

1-1-6把公式转换为数值 7

1-1-7在单元格中显示公式 8

1-2在公式中使用单元格和区域引用 9

1-2-1绝对引用、相对引用与混合引用 9

1-2-2改变引用类型 12

1-2-3引用当前工作表外的单元格 12

1-2-4引用多个工作表上的同一单元格 13

1-3处理常见的公式错误 14

1-4处理循环引用 15

1-5公式运算时的改动 16

1-6在公式中使用名称 16

1-6-1对单元格和区域定义名称 17

1-6-2在公式中使用区域名 18

1-6-3显示区域名称列表 19

1-6-4更改已有区城名称 20

1-6-5使用常量名称 20

1-6-6为公式定义名称 21

第2章工作表函数概述 23

2-1函数基础 23

2-1-1函数的结构 23

2-1-2函数参数类型 24

2-2在公式中输入函数 25

2-3函数的种类 27

2-3-1数据库函数 27

2-3-2日期与时间函数 27

2-3-3工程函数 27

2-3-4财务函数 27

2-3-5信息函数 27

2-3-6逻辑函数 28

2-3-7查询和引用函数 28

2-3-8数学和三角函数 28

2-3-9统计函数 28

2-3-10文本函数 28

2-3-11用户自定义函数 28

2-4函数中的逻辑判断 29

2-4-1 AND、OR、NOT函数 29

2-4-2 TRUE、FALSE函数 31

2-4-3 IF函数 31

第3章使用函数和公式操作文本 37

3-1确定单元格是否含有文本 39

3-1-1使用ISTEXT函数检验单元格内容 39

3-1-2使用TYPE函数决定数据类型 39

3-2使用字符代码工作 40

3-2-1用CODE函数转换字符为数字代码 40

3-2-2用CHAR函数根据ASCII的值返回所代表的字符 40

3-3确定两个字符串是否相同 42

3-3-1用运算符号判断字符串是否相同 42

3-3-2用EXACT函数进行字符串的比较 42

3-4连接两个或多个单元格的内容 43

3-4-1用运算符连接字符串 43

3-4-2用CONCATENATE函数连接两个或多个字符串 43

3-5文本大小写转换 44

3-6文本与数值的互相转换 45

3-6-1用TEXT函数将数值转换为格式化的文本 45

3-6-2用VALUE函数将文本转换为数值 46

3-7删除多余空格和非打印字符 50

3-7-1用TRIM函数去除字符串的空白 50

3-7-2用CLEAN函数去除字符串中不能打印的字符 50

3-8取出字符串中的部分字符 51

3-8-1用LEN函数统计字符串中字符数 51

3-8-2用LEFT函数从字符串中第一个字符开始截取部分字符 52

3-8-3用MID函数从字符串中的不固定位置开始截取部分字符串 52

3-8-4用RIGHT函数从字符串的最后字符开始截取 53

3-9字符串的查找与替换 53

3-9-1用FIND函数在字符串中查找字符 54

3-9-2用SEARCH函数在字符串中查找字符 54

3-9-3用SUBSTITUTE函数在字符串中查找和替换 55

3-9-4用REPLACE函数在字符串中查找与替换字符 59

3-10其他文本函数 60

3-10-1用REPT函数重复一个字符或字符串 60

3-10-2用DOLLAR与RMB函数依照货币格式将数字转换成文本 62

第4章处理日期和时间 63

4-1 Excel如何处理日期和时间 63

4-1-1了解日期和时间的序列号 63

4-1-2输入日期和时间 64

4-1-3格式化日期和时间 64

4-1-4在两个日期之间计算天数 65

4-1-5日期的函数 65

4-2显示与计算日期 66

4-2-1用TODAY函数显示当前系统日期信息 66

4-2-2用DATE函数显示指定数值的日期 67

4-2-3用DATEVALUE函数将日期转换为序列值 68

4-2-4用DAY函数得到日期的天数 69

4-2-5用MONTH函数得到日期的月份 70

4-2-6用YEAR函数得到日期的年份 71

4-2-7用DATEDIF函数计算日期 72

4-2-8用WEEKDAY函数确定星期几 74

4-3分析工具库中的日期函数 83

4-3-1用EOMONTH函数获得月份最后一天的日期 83

4-3-2用NETWORKDAYS函数获得一段时期之间的工作日 84

4-3-3用WORKDAY函数获得相隔指定工作日后的日期 85

4-4显示与计算时间 86

4-4-1用NOW函数显示时间 86

4-4-2用TIME函数显示指定数值的时间 87

4-4-3用TIMEVALUE函数将时间转换为序列值 88

第5章计数与求和技术 95

5-1与求和有关的函数应用 95

5-1-1使用SUM函数进行基本数值求和 95

5-1-2使用SUMIF函数进行高级条件求和 98

5-1-3用SUBTOTAL函数进行分类汇总 101

5-2与计数有关的函数应用 103

5-2-1用COUNT函数进行基本计数 103

5-2-2用COUNTA函数进行忽略空值的计数 103

5-2-3用COUNTIF函数进行指定条件的计数 104

第6章查询与引用函数 113

6-1引用的基础知识 113

6-2引用函数的使用 114

6-2-1用COLUMNS函数得到列数 114

6-2-2用ROWS函数得到行数 114

6-2-3用MATCH函数得到给定值在列表中的位置 114

6-2-4用INDIRECT函数得到字符串指定的引用 115

6-2-5用OFFSET函数根据偏移量得到新的引用 117

6-3获得单元格的地址 119

6-3-1用COLUMN函数获得单元格列标号 119

6-3-2用ROW函数获得单元格行标号 119

6-3-3用ADDRESS函数确定值的地址 120

6-4理解查找公式 122

6-5创建查找公式 123

6-5-1用VLOOKUP函数在列中查找值 123

6-5-2用HLOOKUP函数在行中查找值 127

6-5-3用LOOKUP函数在行或列中查找值 130

6-5-4用INDEX从单元格引用中选取某个值 134

6-5-5用CHOOSE函数从数字的列表中查找一个值 138

第7章数学函数与统计函数 141

7-1常用数学函数 141

7-1-1用RAND函数生成随机数 141

7-1-2用INT函数把数字转换为整数 142

7-1-3常用的四舍五入函数 143

7-1-4用ABS函数获取绝对值 147

7-1-5用MOD函数返回两数相除后的余数 147

7-2用于求平均值的统计函数 148

7-2-1用AVERAGE函数求算术平均值 148

7-2-2用TRIMMEAN函数求数据集的内部平均值 148

7-3查找一组数据中满足不同要求的数值 149

7-3-1求一组数据的最大值MAX与最小值MIN 149

7-3-2求一组数据第K个最大值LARGE与第k个最小值SMALL 150

7-3-3用MEDIAN求一组数据的中位数 154

7-3-4用MODE求一组数据出现频率最多的数 154

7-4建立频率分布 155

7-4-1用FREQUENCY函数建立频率分布 155

7-4-2使用公式建立频率分布 157

7-5用来排位的函数 158

7-5-1用RANK函数获得数值在一组数据中的排位 158

7-5-2用PERCENTRANK函数获得数值在一组数据的百分比排位 158

7-6其他计算方法 159

7-6-1用CONVERT函数进行度量衡转换 159

7-6-2在不同的数字系统间进行数值转换 160

第8章财务函数 163

8-1货币时间价值的计算 164

8-1-1求某项投资的未来值FV 164

8-1-2贷款分期偿还额PMT 165

8-1-3求某项投资的现值PV 169

8-1-4投资期数的计算NPER 170

8-2本金和利息的计算 171

8-2-1 PPMT与IPMT 171

8-2-2 CUMIPMT与CUMPRINC 172

8-3固定资产折旧计算 173

8-3-1直线法折旧函数SLN 174

8-3-2固定余额递减法函数DB 175

8-3-3双倍余额递减法函数DDB 177

8-3-4计算指定的期间内的折旧值VDB 177

8-3-5年数总和法函数SYD 178

8-4投资决策中有关指标的计算 179

8-4-1求投资的净现值NPV 179

8-4-2返回内部收益率的函数IRR 180

8-4-3计算某项投资的实际赢利率RATE 181

8-5债券及其他金融函数 182

8-5-1求定期付息有价证券的应计利息的函数ACCRINT 183

8-5-2计算一次性付息有价证券的应计利息函数ACCRINTM 184

8-5-3求有价证券的贴现率DISC 185

第9章信息函数 187

9-1用CELL函数获得单元格格式、位置或内容等信息 187

9-2用TYPE函数返回数值的类型 190

9-3用INFO函数获得当前操作环境的信息 191

9-4用COUNTBLANK计算区域内空白单元格的个数 191

9-5用ERROR.TYPE函数返回对应于错误类型的数字 192

9-6 IS类函数检验数值或引用类型 193

第10章数据库函数 197

10-1数据库函数基础 197

10-1-1函数的共同特点 197

10-1-2数据库函数列表 197

10-1-3数据库函数的参数含义 198

10-1-4条件定义 198

10-2数据库函数应用 200

10-2-1查找符合条件的值DGET 200

10-2-2计算满足条件的数值和DSUM 201

10-2-3计算满足条件的数值平均值DAVERAGE 203

10-2-4查找满足条件的最大值DMAX 204

10-2-5查找满足条件的最小值DMIN 205

10-2-6高级筛选技术 205

第11章数组与数组公式 211

11-1了解数组公式 211

11-1-1多单元格数组公式 211

11-1-2单元格数组公式 212

11-1-3数组常量 213

11-2了解数组的维数 214

11-2-1创建—维水平数组 214

11-2-2刨建一维纵向数组 214

11-2-3创建二维数组 214

11-3命名数组常量 215

11-4使用数组公式 216

11-4-1输入数组公式 216

11-4-2选择数组公式区域 216

11-4-3编辑数组公式 216

11-4-4用TRANSPOSE函数将工作表中的水平数据表变为垂直数据 217

11-5创建多单元格数组公式 219

11-6创建单一单元格数组公式 223

11-6-1使用数组公式求和与计数 224

11-6-2在查找与引用中使用数组公式 232

11-7用Row()函数在数组公式中生成连续整数 239

11-8数组公式中的逻辑运算 244

第12章创建自定义函数 253

12-1 VBA基础知识 253

12-1-1 VBE编辑器 253

12-1-2代码窗口的操作 255

12-1-3变量及变量的数据类型 256

12-1-4声明变量 257

12-1-5常量 258

12-1-6 VBA的流程控制与循环 259

12-2一个简单的VBA自定义函数范例 260

12-2-1创建一个简单的自定义函数 260

12-2-2在工作表中使用函数 261

12-2-3分析自定义函数 261

12-3创建函数程序 262

12-3-1声明函数 262

12-4执行函数程序 263

12-4-1从其他函数中调用自定义函数 263

12-4-2在工作表公式中使用自定义函数 263

12-5学习函数程序的参数 264

12-5-1不使用参数的函数 264

12-5-2有—个参数的函数 264

12-5-3使用两个参数的函数 266

12-5-4使用区域参数的函数 267

12-6调试自定义函数 268

12-7插入自定义函数 269

附录 Excel的内部函数 271

1数学和三角函数 271

2文本函数 275

3日期与时间函数 279

4信息函数 281

5逻辑函数 283

6查找和引用函数 283

7统计函数 286

8财务函数 295

9数据库函数 304

10工程函数 305

Excel 制作要领 解释部分函数指令的用法接实际应用 送好评

1、AND函数：

所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。

语法

AND(logical1,logical2,...)

Logical1, logical2,...是 1到 255个待检测的条件，它们可以为 TRUE

或 FALSE。

注解

参数必须是逻辑值 TRUE或 FALSE,或者包含逻辑值的数组（数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）或引用。

如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。

如果指定的单元格区域内包括非逻辑值，则 AND将返回错误值#VALUE!。

示例 1

如果将示例复制到一个空白工作表中，可能会更易于理解。

公式说明（结果）

=AND(TRUE, TRUE)

所有参数的逻辑值为真(TRUE)

=AND(TRUE, FALSE)

一个参数的逻辑值为假(FALSE)

=AND(2+2=4, 2+3=5)

所有参数的计算结果为真

(TRUE)

示例 2

如果将示例复制到一个空白工作表中，可能会更易于理解。

=AND(1<A2, A2<100)

因为 50介于 1到 100之间(TRUE)

=IF(AND(1<A3, A3<100), A3,

"数值超出范围")

如果上面的第二个数字介于 1到 100

之间，则显示该数字，否则显示信息（数值超出范围）

=IF(AND(1<A2, A2<100), A2,

"数值超出范围")

如果上面的第一个数字介于 1和 100之间，则显示该数字，否则显示一条消息

(50)

2、FALSE函数：

返回逻辑值 FALSE。

语法

FALSE()

说明

也可以直接在工作表或公式中输入文字 FALSE，Microsoft Excel会自动将它解释成逻辑值 FALSE

3、IFERROR函数：

如果公式计算出错误则返回您指定的值；否则返回公式结果。使用 IFERROR函数来捕获和处理公式（公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号(=)

开始。）中的错误。

语法

IFERROR(value,value_if_error)

Value是需要检查是否存在错误的参数。

Value_if_error

是公式计算出错误时要返回的值。计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!。

注解

如果 value或 value_if_error是空单元格，则 IFERROR将其视为空字符串值("")。

如果 value是数组公式，则 IFERROR为 value中指定区域的每个单元格返回一个结果数组。请参阅下面的第二个示例。

示例：使用常规公式捕获除法错误

如果将示例复制到空白工作表中，可能会更易于理解。

A B

配额销售量

210 35

55 0

23

公式说明（结果）

=IFERROR(A2/B2,"计算中有错误")

检查第一个参数中公式的错误（210除以 35），未找到错误，返回公式结果

(6)。

=IFERROR(A3/B3,"计算中有错误")

检查第一个参数中公式的错误（55除以 0），找到被零除错误，返回

value_if_error（计算中有错误）。

=IFERROR(A4/B4,"计算中有错误")

检查第一个参数中公式的错误（""除以 23），未找到错误，返回公式结果

(0)。

4、IFNA函数：

如果公式返回错误值#N/A，则结果返回您指定的值；否则返回公式的结果。

语法

IFNA(value, value_if_na)

IFNA函数语法具有下列参数。

Value必需。用于检查错误值#N/A的参数。

Value_if_na必需。公式计算结果为错误值#N/A时要返回的值。

说明

如果 Value或 Value_if_na是空单元格，则 IFNA将其视为空字符串值("")。

如果 Value是数组公式，则 IFNA为 Value中指定区域的每个单元格以数组形式返回结果。

示例：

公式：

=IFNA(VLOOKUP("Seattle",$A$5:$B$10,0),"Not found")

说明：

IFNA检验 VLOOKUP函数的结果。因为在查找区域中找不到西雅图，VLOOKUP将返回错误值#N/A。 IFNA在单元格中返回字符串“未找到”，而不是标准#N/A错误值。

5、NOT函数：

对参数值求反。当要确保一个值不等于某一特定值时，可以使用 NOT函数。

语法

NOT(logical)

Logical为一个可以计算出 TRUE或 FALSE的逻辑值或逻辑表达式。

说明

如果逻辑值为 FALSE，函数 NOT返回 TRUE；如果逻辑值为 TRUE，函数 NOT返回 FALSE。

示例：

公式说明（结果）

=NOT(FALSE)

对 FALSE求反(TRUE)

=NOT(1+1=2)对计算结果为 TRUE的公式求反

(FALSE)

6、OR函数：

在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；任何一个参数的逻辑值为 FALSE，即返回 FALSE。

语法

OR(logical1,logical2,...)

Logical1,logical2,...是 1到 255个需要进行测试的条件，测试结果可以为

TRUE或 FALSE。

注解

参数必须能计算为逻辑值，如 TRUE或 FALSE，或者为包含逻辑值的数组（数组：用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）或引用。

如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。

如果指定的区域中不包含逻辑值，函数 OR返回错误值#VALUE!。

可以使用 OR数组公式来检验数组中是否包含特定的数值。若要输入数组公式，请按 Ctrl+Shift+Enter。

示例：

公式说明（结果）

=OR(TRUE)参数为 TRUE(TRUE)

=OR(1+1=1,2+2=5)所有参数的逻辑值为 FALSE(FALSE)

=OR(TRUE,FALSE,TRUE)至少一个参数为 TRUE(TRUE)

7、XOR函数：

返回所有参数的逻辑异或。

语法

XOR(logical1, [logical2],...)

XOR函数语法具有下列参数。

Logical1、logical2等 Logical 1是必需的，后续逻辑值是可选的。您要检验的 1至 254个条件，可为 TRUE或 FALSE，且可为逻辑值、数组或引用。

注解

参数必须计算为逻辑值，如 TRUE或 FALSE，或者为包含逻辑值的数组或引用。

如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。

如果指定的区域中不包含逻辑值，则 XOR返回错误值#VALUE!。

您可以使用 XOR数组公式检查数组中是否出现某个值。若要输入数组公式，请按 Ctrl+Shift+Enter。

当 TRUE输入的数字为奇数时，XOR的结果为 TRUE；当 TRUE输入的数字为偶数时，XOR的结果为 FALSE。

示例：

公式：

=XOR(3>0,2<9)

说明：

由于其中一个测试计算为 TRUE，则返回 TRUE。

结果：

TRUE

公式：

=XOR(3>12,4>6)

说明：

由于所有测试结果计算为 False，则返回 FALSE。必须至少其中一个测试结果计算为 True，才能返回 TRUE。

结果：

FALSE

很高兴为你解答，满意请采纳！！

excel常见函数及应用

Excel最常用的八个函数

1、sum求和函数

用法=sum(求和区域)，比如sum(A1:B2,D1:E2)，就是求A1:B2和D1:E2两个区域内所有数值的和；

2、left从左边取指定长度字符串函数

用法=left(字符串或所在单元格，长度），比如left(A1,1)，就是从A1的左边取一位文本，例如A1中是“ABC”，则此公式结果为A。

3、text文本格式函数

用法=text(字符串或单元格，文本格式)，比如text(A1,"000")，就是让A1内的内容变成”000“这种格式，如A1中为1，则变为001。

4、vlookup查找并返回指定值函数

用法=vlookup（查找值，查找区域，返回查找区域列数，匹配方式），比如Vlookup（A1,C:D，2,0），就是从C列中查找与A1万全相同的数值，并返回对应的D列的值。

5、if条件函数

用法=if(条件，条件成立返回值，条件不成立返回值），列入if(A1>B1,1,0)，就是如果A1的值大于B1的值，则结果为1，否则结果为0。

6、Sumif和Countif函数

用途：按条件求和，按条件计数，很多复杂的数据核对也需要用到这2个函数。

用法：

=Sumif(判断区域,条件，求和区域)

=Counif(判断区域,条件）

7、Round函数、INT函数

用途：数值四舍五入和取整函数

用法：

四舍五入=Round(数值,保留的小数位数）

取整=INT(数值)

8、Datedif函数

用途：日期的间隔计算。

用法：

=Datedif(开始日期,结束日期."y")间隔的年数

=Datedif(开始日期,结束日期."M")间隔的月份

=Datedif(开始日期,结束日期."D")间隔的天数

Excel常用快捷键

关闭工作簿 Ctrl+W

打开工作簿 Ctrl+O

转至“主页”选项卡 Alt+H

保存工作簿 Ctrl+S

复制 Ctrl+C

粘贴 Ctrl+V

撤消 Ctrl+Z

删除单元格内容 Delete键

选择填充颜色 Alt+H, H

剪切 Ctrl+X

转至“插入”选项卡 Alt+N

加粗 Ctrl+B

居中对齐单元格内容 Alt+ H、A、C

转至“页面布局”选项卡 Alt+P

转至“数据”选项卡 Alt+A

转至“视图”选项卡 Alt+W

打开上下文菜单 Shift+F10或上下文键

添加边框 Alt+H, B

删除列 Alt+ H、D、C

转至“公式”选项卡 Alt+M

隐藏选定的行 Ctrl+9

隐藏选定的列 Ctrl+0

文章到此结束，如果本次分享的excel函数应用实例详解和表格函数的使用方法及实例的问题解决了您的问题，那么我们由衷的感到高兴！