EXCEL内置函数用法Word文档格式.docx

EXCEL内置函数用法Word文档格式.docx

《EXCEL内置函数用法Word文档格式.docx》由会员分享，可在线阅读，更多相关《EXCEL内置函数用法Word文档格式.docx（30页珍藏版）》请在冰点文库上搜索。

语法形式：

函数名（）

2）取得日期/时间的部分字段值

如果需要单独的年份、月份、日期或小时的数据，可以使用HOUR，DAY，MONTH，YEAR函数直接从日期/时间中取出需要的数据。

例如，现有如图1-8所示的单元格，需要返回2002-3-2122:

10的年份、月份、日期及小时数，可以分别采用相应函数实现。

返回年则在B3单元格中输入“=year（a1）”，如图1-9所示。

图1-8处理时间单元格

图1-9年函数用法

可以看出B1的值为2002，同理在月、日、小时相对应的B4、B5、B6中输入“=MONTH（A1）”，“=DAY（A1）”，“=HOUR（A1）”，结果如图1-10所示。

图1-10最终结果

可以试着在A1单元格中输入“=Now（）”，再查看相应各项的变化。

日期与时间函数有很多，在实际操作中也应用得较多，通过下面的例子，学习日期与时间函数——DATEDIF函数。

DATEDIF:

计算两个日期之间的天数、月数或年数

DATEDIF（start_date,end_date,unit）

start_date参数：

开始时间

end_date参数：

结束时间

unit参数：

两个时间差的比较单位，可以为y,m,d,分别对应年、月、天

例如公司职员工资和工龄关系很大，所以有必要把各员工的工龄统计清楚。

第一员工很多，如果一个一个地计算很麻烦，而且容易出错；

第二则工龄是变化的，如果每年每个月都这样去计算，那工作量太大。

而使用DATEDIF，就可以轻易解决。

首先要知道员工进入公司的时间，一般公司都会有这样的数据，如图1-11所示。

图1-11人员工龄统计表

选中C2单元格,输入“=DATEDIF（B2，NOW（），”y”）”，如图1-12所示,结果如图1-13所示。

图1-12使用DATEDIF函数

图1-13得出工龄

接下来采用填充操作得出所有员工的工龄。

首先选中整个工龄单元格，如图1-14所示。

图1-14选中剩下工龄单元格区域

选择“编辑/填充/向下填充”命令，如图1-15所示。

图1-15填充菜单

最后结果如图1-16所示。

图1-16得出所有人的工龄

也可以选中填好公式的单元格，将鼠标移至单元格右下方，鼠标变为十字，点中，然后下拉，即可使公式复制到下面的单元格。

1.3.2文本函数

所谓文本函数，就是可以在公式中处理文字串的函数。

例如，可以改变大小写或确定文字串的长度；

可以替换某些字符或者去除某些字符等。

1大小写转换

_LOWER：

将一个文字串中的所有大写字母转换为小写字母

_UPPER：

将文本转换为大写形式

_PROPER：

将文字串的首字母及任何非字母字符之后的首字母转换为大写。

将其余的字母转换为小写语法形式：

函数名（文本字符串）。

示例说明：

已有字符串为hOwArEYOu!

可以看到由于输入的不规范，这句话大小写混乱，如图1-17所示。

通过以上3个函数可以转换文本显示样式，使文本变得规范。

分别在B3,B4,B5单元格里输入“=LOWER（"

hOwArEYOu!

"

）”,“=UPPER（"

）”,“=PROPER（"

）”，得到的结果如图1-18所示。

图1-17待转换的文本图1-18转换后的结果

2.取出字符串中的部分字符

可以使用Mid,Left,Right等函数从长字符串内获取一部分字符。

LEFT函数用来取出字符串中从左数几个数字的字符串。

语法格式：

LEFT（text,num_chars）

text参数：

包含要提取字符的文本串

num_chars参数：

指定要由LEFT所提取的字符数

Mid,Right函数使用方法与Left函数相同，应用实例如下。

LEFT（"

Iloveyou"

1）=I

MID（"

3,4）=love

RIGHT（"

3）=you

3.去除字符串的空白

在字符串形态中，空白也是一个有效字符，但是如果字符串中出现空白字符时，容易在判断或对比数据时发生错误，在Excel中可以使用Trim函数清除字符串中的空白。

TRIM（text）

需要清除其中空格的文本

例如，从字符串"

中清除空格的函数写法为TRIM（"

）=Iloveyou。

注意：

Trim函数只能清除单元格开头和结尾的空格，不会清除单词之间的单个空格，如果连这部分空格都需要清除，可以使用替换功能

4.字符串的比较

在数据表中经常会比较不同的字符串，此时可以使用Exact函数来比较两个字符串是否相同。

该函数测试两个字符串是否完全相同，如果完全相同，返回TRUE；

否则，返回FALSE。

函数Exact能区分大小写，但忽略格式上的差异。

利用Exact函数可以测试输入文档内的文字。

Exact（text1,text2）

text1参数：

待比较的第1个字符串

text2参数：

待比较的第2个字符串。

如图所示。

图1-19Excat函数使用实例

1.3.3数学和三角函数

通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或进行复杂计算。

1.与求和有关的函数的应用

SUM函数是Excel中使用最多的函数，利用它进行求和运算可以忽略存有文本、空格等数据的单元格，语法简单、使用方便。

相信这也是大家最先学会使用的Excel函数之一。

但是实际上，Excel所提供的求和函数不仅仅只有SUM一种，还包括SUBTOTAL，SUMIF，SUMPRODUCT，SUMSQ，SUMX2MY2，SUMX2PY2，SUMXMY2几种函数。

（1）SUM

想求不在一行或一列中的数据和，如图1-20所示绿底的数据之和，可在B10中输入“=SUM（）”后将光标点在括号内，然后按住Ctrl键的同时单击绿底的数据，在B10中会出现公式“=SUM（A3,A4,A5,B6,B7,C1,C2）”，按Enter键后会得出结果142。

也可在B10中输入公式“=SUM（A3:

A5,B6:

B7,C1:

C2）”，得出同样的结果。

图1-20求绿底数据之和

SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。

换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel就会提示参数太多。

对需要参与求和的某个常数，可用“=SUM（单元格区域，常数）”的形式直接引用，一般不必绝对引用存放该常数的单元格。

（2）SUMIF

SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，假设欲统计各电压等级变电站总容量，由于受录入及其他排版因素的影响，不是所有的110kV变电站都在一起，所以用SUM（）函数的话，需要一个一个选择单元格，比较复杂。

而用SUMIF（）就方便多了。

在单元格中输入“=SUMIF（B:

B,"

110"

C:

C）”，如图1-21所示。

图1-21输入SUMIF函数参数

其中B:

B为提供逻辑判断依据的单元格区域，“110”为判断条件，即只统计B:

B区域中职称为“110”的单元格，C:

C为实际求和的单元格区域。

结果如下图：

图1-22求和结果

2.与函数图像有关的函数应用

EXCEL可以根据现状数据完成图像的制作。

以正弦函数和余弦函数为例说明函数图像的描绘方法。

步骤1录入数据

如图1-23所示，首先在表中录入数据，自B1~N1的单元格以30度递增的方式录入从0~360的数字，共13个数字。

图1-23原始数据

步骤2求函数值

在第2行和第3行分别输入SIN和COS函数，这里需要注意的是：

由于SIN等三角函数在Excel的定义是弧度值，因此必须先将角度值转为弧度值。

具体公式写法为B2：

=SIN（B1*PI（）/180）,B3:

=COS（B1*PI（）/180），得到如图所示的工作表。

图1-24函数坐标点

步骤3选择“图像”类型

首先选中制作函数图像所需要的表中数据，利用Excel工具栏上的图表向导按钮（也可利用“插入/图表”命令），在“图表类型”中选择“XY散点图”，再在右侧的“子图表类型”中选择“无数据点平滑线散点图”，如图所示。

图1-25“图表类型”对话框

单击“下一步”按钮，出现“图表数据源”对话框，不进行任何操作，直接单击“下一步”按钮，如图所示。

图1-26“图表选项”对话框

步骤4图表选项操作

图表选项操作是制作函数曲线图的重要步骤，在“图表选项”窗口中，依次进行操作的项目如下。

标题：

为图表取标题，本例中命名为“正弦和余弦函数图像”；

为横轴和纵轴取标题。

坐标轴：

可以不做任何操作。

网格线：

可以做出类似坐标纸上网格效果，也可以取消网格线。

图例：

本例选择图例放在图像右边，可随具体情况选择。

数据标志：

本例未将数据标志在图像上，主要原因是影响美观。

步骤5完成图像

操作结束后单击“完成”按钮，一幅图像就插入Excel的工作区了，如图所示。

图1-27函数图像

步骤6编辑图像

图像生成后，字体、图像大小、位置都不一定合适。

可选择相应的选项进行修改。

所有这些操作都可以先用鼠标选中相关部分，再单击右键弹出快捷菜单，通过快捷菜单中的有关项目进行操作。

至此，一幅正弦和余弦函数图像制作完成。

用同样的方法，还可以制作二次曲线、对数图像等。

3.常见数学函数使用技巧——四舍五入

在实际工作的数学运算中，特别是财务计算中常常会遇到四舍五入的问题。

虽然，Excel的单元格格式中允许定义小数位数，但是在实际操作中，我们发现，其实数字本身并没有真正四舍五入，只是显示结果似乎四舍五入了。

如果采用这种四舍五入的方法，在财务运算中常常会出现几分钱的误差，而这是财务运算不允许的。

其实，Excel已经提供这方面的函数了，这就是ROUND函数，它可以返回某个数字按指定位数舍入后的数字。

ROUND（number,num_digits）

number参数：

将要进行四舍五入的数字

num_digits参数：

希望得到的数字的小数点后的位数

单元格A2中为初始数据0.124556，将要对它进行四舍五入。

在单元格B2中输入“=ROUND（A2,2）”，小数点后保留两位有效数字。

在单元格C2中输入“=ROUND（B2,3）”，则小数点保留3位有效数字。

对数字进行四舍五入，还可以使用INT（取整函数），但由于这个函数的定义是返回实数舍入后的整数值，因此，用其进行四舍五入还是需要一些技巧的，也就是要加上0.5，才能达到取整的目的。

仍然以上图为例，如果采用INT函数，则B2公式应写为“=INT（A2*100+0.5）/100”。

1.3.4逻辑函数

用来判断真假值或者进行复合检验的Excel函数，我们称为逻辑函数。

在Excel中提供了6种逻辑函数，即AND，OR，NOT，FALSE，IF，TRUE函数。

1.AND、OR、NOT函数

这3个函数都用来返回参数逻辑值。

（1）AND函数

所有参数的逻辑值为真时返回TRUE；

只要一个参数的逻辑值为假即返回FALSE。

即当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。

AND（logical1,logical2,...）

logical1,logical,...参数：

表示待检测的1~30个条件值，各条件值可能为TRUE，可能为FALSE。

参数必须是逻辑值，或者包含逻辑值的数组或引用。

下面以一个实例进行说明。

步骤1在A1单元格中输入数字40，在A2中输入公式“=AND（A1>

30,A1<

60）”。

由于A1大于30、小于60。

所以两个条件值（logical）均为真，则返回结果为TRUE。

步骤2如果B1,C1,D1单元格中的值为TRUE,TRUE,FALSE，显然3个参数并不都为真，所以在A3单元格中的公式“=AND（B1,C1,D1）”为FALSE，如图所示。

图1-28AND函数使用两例

（2）OR函数

OR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE。

它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。

而OR函数仅需其中任何一个为真即可为真。

例如上面的实例2，如果在B3单元格中的公式写为=OR（B1:

C1）则结果为TRUE。

（3）NOT函数

NOT函数用于对参数值求反。

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

简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE。

例如“NOT（3+10），3+10”等于13，结果为TRUE，但由于是NOT函数，因此返回函数结果与之相反，为FALSE。

2.TRUE，FALSE函数

TRUE，FALSE函数用来返回参数的逻辑值，由于可以直接在单元格或公式中键入值TRUE或者FALSE。

因此这两个函数通常可以不使用。

3.IF函数

（1）IF函数说明

IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此IF函数也称为条件函数。

它的应用很广泛，可以使用函数IF对数值和公式进行条件检测。

IF（logical_test,value_if_true,value_if_false）

logical_test参数：

表示计算结果为TRUE或FALSE的任意值或表达式

value_if_true参数：

logical_test为TRUE时返回的值，Value_if_true也可以是其他公式

value_if_false参数：

logical_test为FALSE时返回的值，Value_if_false也可以是其他公式

如果第1个参数logical_test返回的结果为真的话，则执行第2个参数

Value_if_true的结果，否则执行第3个参数Value_if_false的结果。

IF函数可以嵌套7层，用Value_if_false及Value_if_true参数可以构造复杂的检测条件。

（4）根据条件计算值

了解了IF函数的使用方法后，我们再来看看与之类似的Excel提供的可根据某一条件来分析数据的其他函数。

例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。

如果要根据单元格区域中的某一文本串或数字求和，则可使用SUMIF工作表函数。

COUNTIF可以用来计算给定区域内满足特定条件的单元格的数目。

COUNTIF（range,criteria）

range参数：

需要计算其中满足条件的单元格数目的单元格区域

criteria参数：

确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

1.3.5查询和引用函数

当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。

1.引用的作用

在Excel中引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。

通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。

还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其他应用程序中的数据。

2.引用的含义

关于引用需要了解如下几种情况的含义。

外部引用：

不同工作簿中的单元格的引用称为外部引用

远程引用：

引用其他程序中的数据称为远程引用

相对引用：

在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置

绝对引用：

如果在复制公式时不希望Excel调整引用，那么请使用绝对引用。

即加入美元符号，如$C$1。

3.引用的表示方法

关于引用有两种表示的方法，即A1和R1C1引用样式。

（1）引用样式一（默认）——A1

A1的引用样式是Excel的默认引用类型。

这种类型引用字母标志列（从A~IV，共256列）和数字标志行（从1~65536）。

这些字母和数字被称为行和列标题。

如果要引用单元格，按顺序输入列字母和行数字。

例如，C25引用了列C和行25交叉处的单元格。

如果要引用单元格区域，需输入区域左上角单元格的引用、冒号和区域右下角单元格的引用，如A20:

C35。

（2）引用样式二——R1C1

在R1C1引用样式中，Excel使用R加行数字和C加列数字来指示单元格的位置。

例如，单元格绝对引用R1C1与A1引用样式中的绝对引用$A$1等价。

如果活动单元格是A1，则单元格相对引用R[1]C[1]将引用下面一行和右边一列的单元格，或是B2。

在了解了引用的概念后，我们来看看Excel提供的查询与引用函数。

查询与引用函数可以用来在数据清单或表格中查找特定数值，或查找某一单元格的引用。

Excel中一共提供了ADDRESS，AREAS，CHOOSE，COLUMN，COLUMNS，HLOOKUP，HYPERLINK，INDEX，INDIRECT，LOOKUP，MATCH，OFFSET，ROW，ROWS，TRANSPOSE，VLOOKUP等16个查询与引用函数。

1.ADDRESS函数

ADDRESS用于按照给定的行号和列标建立文本类型的单元格地址。

ADDRESS（row_num,column_num,abs_num,a1,sheet_text）

row_num参数：

指在单元格引用中使用的行号

column_num参数：

指在单元格引用中使用的列标

abs_num参数：

指明返回的引用类型，1代表绝对引用，2代表绝对行号，相对列标，3代表相对行号，绝对列标，4为相对引用

a1参数：

用以指明A1或R1C1引用样式的逻辑值。

如果A1为TRUE或省略，函数ADDRESS返回A1样式的引用；

如果A1为FALSE，函数ADDRESS返回R1C1样式的引用。

sheet_text参数：

指明作为外部引用的工作表的名称，如果省略sheet_text，则不使用任何工作表名，简单地说，即ADDRESS（行号，列标，引用类型，引用样式，工作表名称）。

2.INDEX函数

INDEX用于返回表格或区域中的数值或对数值的引用。

INDEX（）有两种形式：

数组和引用。

数组形式通常返回数值或数值数组；

引用形式通常返回引用。

（1）INDEX（array,row_num,column_num）

返回数组中指定单元格或单元格数组的数值。

array参数：

单元格区域或数组常数

数组中某行的行序号，函数从该行返回数值

数组中某列的列序号，函数从该列返回数值。

需注意的是row_num和column_num必须指向array中的某一单元格，否则，函数INDEX返回错误值#REF!

。

这个函数也是工作中经常使用到的。

（2）INDEX（reference,row_num,column_num,area_num）

返回引用中指定单元格或单元格区域的引用。

reference参数：

对一个或多个单元格区域的引用

引用中某行的行序号，函数从该行返回一个引用

引用中某列的列序号，函数从该列返回一个引用

需注意的是Row_num,column_num和area_num必须指向reference中的单元格；

否则，函数INDEX返回错误值#REF!

如果省略row_num和column_num，函数INDEX返回由area_num所指定的区域。

3.OFFSET

OFFSET函数用于以指定的引用为参照系，通过给定偏移量得到新的引用。

返回的引用可以是一个单元格或者单元格区域，并可以指定返回的行数或者列数。

OFFSET（reference,rows,cols,height,width）

作为偏移量参照系的引用区域（reference必须为对单元格或相连单元格区域的引用，否则，OFFSET函数返回错误值＃VALUE!

）

rows参数：

相对于偏移量参照系的左上角单元格向上（向下）偏移的行数（例如rows使用2作为参数，表示目标引用区域的左上角单元格比reference低两行），行数可为正数（代表在起始引用单元格的下方）或者负数（代表在起始引用单元格的上方）或者0（代表起始引用单元格）

cols参数：

表示相对于偏移量参照系的左上角单元格向左（向右）偏移的列数（例如cols使用4作为参数，表示目标引用区域的左上角单元格比reference右移4列），列数可为正数（代表在起始引用单元格的右边）或者负数（代表在起始引用单元格的左边）如果行数或者列数偏移量超出工作表边缘，OFFSET函数将返回错误值＃REF！

height参数：

表示高度，即所要返回的引用区域的行数（height必须为正数）

width参数：

表示宽度，即所要返回的引用区域的列数（width必须为正数）。

如果