EXCEL中比较常用的的函数Word格式.docx

EXCEL中比较常用的的函数Word格式.docx

《EXCEL中比较常用的的函数Word格式.docx》由会员分享，可在线阅读，更多相关《EXCEL中比较常用的的函数Word格式.docx（23页珍藏版）》请在冰豆网上搜索。

时,计算结果是两个日期间隔的天数即公式为=DATEDIF（"

）、

YD"

时,计算结果是忽略年数差两个日期间隔的天数，

即公式为=DATEDIF（"

）

MD"

时,计算结果是两个日期间隔的天数.忽略年数和月份之差。

YM"

时,计算结果是不计年份的间隔月份数.

将小写金额转换为大写金额

=IF（INT（D11）=0,"

"

TEXT（INT（D11）,"

[DBNum2]"

）&

IF（OR（INT（D11*10）=0,INT（D11）=D11）,"

IF（INT（D11*10）=INT（D11）*10,"

零"

RIGHT（TEXT（INT（D11*10）,"

）,1）&

））&

IF（INT（D11*100）=INT（D11*10）*10,"

整"

RIGHT（TEXT（ROUND（D11,2）,"

分"

函数SUBTOTAL

返回列表或数据库中的分类汇总。

通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。

一旦创建了分类汇总，就可以通过编辑SUBTOTAL函数对该列表进行修改。

语法

SUBTOTAL（function_num,ref1,ref2,...）[=SUBTOTAL（分类汇总中使用的函数代码，数据区域）]

Function_num 

为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。

Function_num

（包含隐藏值）

（忽略隐藏值）

函数

例子

1

101

AVERAGE――平均数

2

102

COUNT―――包含数据单元格的个数

3

103

COUNTA-----非空格的个数

序号=SUBTOTAL（103,$B$3:

B3）

4

104

MAX――――最大值

5

105

MIN――――最小值

6

106

PRODUCT――乘积

7

107

STDEV

8

108

STDEVP

9

109

SUM――――合计数

10

110

VAR

11

111

VARP

Ref1,ref2, 

为要进行分类汇总计算的1到29个区域或引用。

说明

∙如果在ref1,ref2,…中有其他的分类汇总（嵌套分类汇总），将忽略这些嵌套分类汇总，以避免重复计算。

∙当function_num为从1到11的常数时，SUBTOTAL函数将包括通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。

当您要分类汇总列表中的隐藏和非隐藏值时，请使用这些常数。

当function_num为从101到111的常数时，SUBTOTAL函数将忽略通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。

当您只分类汇总列表中的非隐藏数字时，使用这些常数。

∙SUBTOTAL函数忽略任何不包括在筛选结果中的行，不论使用什么function_num值。

∙SUBTOTAL函数适用于数据列或垂直区域。

不适用于数据行或水平区域。

例如，当function_num大于或等于101时需要分类汇总某个水平区域时，例如SUBTOTAL（109,B2:

G2），则隐藏某一列不影响分类汇总。

但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。

∙如果所指定的某一引用为三维引用，函数SUBTOTAL将返回错误值#VALUE!

。

SUBTOTAL（分类汇总中使用的函数代码，数据区域）

举例

数据表

A列B列C列D列

姓名语文数学英语

张三959488

……

共56条记录，

求语文的平均分，则公式

=SUBTOTAL（1，B2：

B57）

注意：

参数“1”表示分类汇总使用的平均值函数AVERAGE（参见函数帮助）。

公式等价于公式=AVERAGE（B2：

B57）。

求语文的最高分，则公式

=SUBTOTAL（4，B2：

参数“4”表示分类汇总使用的最大值函数MAX（参见函数帮助）

公式等价于公式=MAX（B2：

B57）

设置有效性条件验证

　　假设G列为员工“身份证号”字段，G2单元格为第一个员工的身份证号码所在的单元格。

在未输入之前，我们可先设置该列的有效性条件来确保该列数据的惟一性。

　　选中G2单元格，单击“数据”菜单中的“有效性”命令，弹出“数据有效性”对话框，选择“设置”选项卡，在“允许”下拉列表中选择“自定义”，在“公式”框内输入“=COUNTIF（G:

G,G2）=1”（公式内所有的字符使用半角英文，不包括双引号，如图1所示）。

设置出错警告提示信息

设置出错警告提示信息的目的在于提醒用户正确输入数据。

具体步骤是：

单击“数据有效性”对话框中的“出错警告”选项卡，在“标题”框内输入“数据输入错误”，在“错误信息”框内输入“你刚才输入的数据已经存在，请检查数据的惟一性！

”。

设置完之后，单击“确定”按钮（如图2所示）。

　　至此，已经设置了G2单元格的有效性条件验证和出错提示信息。

为了将这个设置应用到整个G列（除了字段名称所在的单元格即G1单元格），可用填充柄工具向下拖动将公式复制到G列其他的单元格。

　　输入身份证信息

　　以上设置完成之后我们就可以向G列中输入员工的身份证号了。

每输入一个员工的身份证号，Excel会自动对该数据进行有效性验证，如果该数据已经存在，系统将弹出出错警告提示框，如图3所示。

　　上述功能只能验证数据的惟一性，若数据位数输入错误，系统则检测不出这一错误。

若在输入时需要同时验证数据的位数，还是以身份证号为例，可将图一中的公式改为“=AND（COUNTIF（G:

G,G2）=1,OR（LEN（G2）=15,LEN（G2）=18））”，图二中的错误信息改为“请检查数据的惟一性或输入数据位数错！

设置完后重新复制G2单元格的公式至G列其他的单元格。

该公式的含义是：

在G列输入的数据必须是惟一的且数据位数必须是15位或18位。

　　最后还需要提醒大家，由于G列输入的是身份证号，位数超过了11位数据，所以最好在输入数据之间，选将G列全部选定，设置“单元格格式”中的“数字分类”格式为“文本”格式，这样才能保证身份证号以正确形式输入。

CONCATENATE

　　主要功能：

将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中。

　　使用格式：

CONCATENATE（Text1，Text……）

　　参数说明：

Text1、Text2……为需要连接的字符文本或引用的单元格。

　　应用举例：

在C14单元格中输入公式：

=CONCATENATE（A14,"

@"

B14,"

.com"

），确认后，即可将A14单元格中字符、@、B14单元格中的字符和.com连接成一个整体，显示在C14单元格中。

　　特别提醒：

如果参数不是引用的单元格，且为文本格式的，请给参数加上英文状态下的双引号，如果将上述公式改为：

=A14&

&

B14&

，也能达到相同的目的。

将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中也可用以下方式实现：

＝Text1&

Text1&

Text……，Text表示单元格或文本或数值

COUNTIF

统计某个单元格区域中符合指定条件的单元格数目。

COUNTIF（Range,Criteria）

Range代表要统计的单元格区域；

Criteria表示指定的条件表达式。

在C17单元格中输入公式：

=COUNTIF（B1:

B13,"

>

=80"

），确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目。

允许引用的单元格区域中有空白单元格出现。

VLOOKUP

VLOOKUP函数

在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。

这里所说的“数组”，可以理解为表格中的一个区域。

数组的列序号：

数组的“首列”，就是这个区域的第一纵列，此列右边依次为第2列、3列……。

假定某数组区域为B2:

E10，那么，B2:

B10为第1列、C2:

C10为第2列……。

语法：

VLOOKUP（查找值，区域，列序号，逻辑值）

“查找值”：

为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。

“区域”：

数组所在的区域，如“B2:

E10”，也可以使用对区域或区域名称的引用，例如数据库或数据清单。

“列序号”：

即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；

若列序号小于1，函数VLOOKUP返回错误值#VALUE!

；

如果大于区域的列数，函数VLOOKUP返回错误值#REF!

“逻辑值”：

为TRUE或FALSE。

它指明函数VLOOKUP返回时是精确匹配还是近似匹配。

如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；

如果“逻辑值”为FALSE，函数VLOOKUP将返回精确匹配值。

如果找不到，则返回错误值#N/A。

如果“查找值”为文本时，“逻辑值”一般应为FALSE。

另外：

·

如果“查找值”小于“区域”第一列中的最小数值，函数VLOOKUP返回错误值#N/A。

如果函数VLOOKUP找不到“查找值”且“逻辑值”为FALSE，函数VLOOKUP返回错误值#N/A。

下面举例说明VLOOKUP函数的使用方法。

假设在Sheet1中存放小麦、水稻、玉米、花生等若干农产品的销售单价：

A 

B

1 

农产品名称 

单价

2 

小麦 

0.56

3 

水稻 

0.48

4 

玉米 

0.39

5 

花生 

0.51

…………………………………

100 

大豆 

0.45

Sheet2为销售清单，每次填写的清单内容不尽相同：

要求在Sheet2中输入农产品名称、数量后，根据Sheet1的数据，自动生成单价和销售额。

设下表为Sheet2：

B 

C 

D 

数量 

单价 

金额 

1000 

0.48 

480