Excel常见函数的使用方法

2012-01-10 14:05:58来源:作者:

Excel是办公室自动化中非常重要的一款数据处理软件,在Excel中函数用来分析和处理数据,如排序、求和、计数、提取数据等等。

Excel是办公室自动化中非常重要的一款数据处理软件,在Excel中函数用来分析和处理数据,如排序、求和、计数、提取数据等等。

Excel函数一共有11类,分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。

Excel中函数其实是一些预定义的公式,函数右边括号中的部分称为参数,假如一个函数可以使用多个参数,那么参数与参数之间使用半角逗号进行分隔。

=IF(AVERAGE(F2:F5)>60,SUM(G2:G5)>0,0)

参数包括:常量(直接键入的数字和文本)、逻辑值(TRUE或FALSE)、错误值(#VALUE!或#NAME)、单元格地址、单元格区域引用(A1:B2)、数组(常量数组{56,58,59;70,80,90}、区域数组「B1:B3,A1:A3」)、表达式(1+2、1+2=3)和函数(=SUM((1+2=3),(1+2)))。在嵌套中,一个函数可以另个函数的参数。

①单元格地址命名(别名):在名称框直接输入;插入→名称→定义(Excel2003)
快捷键F3→将定义的名称粘贴到公式中。
快捷键Ctrl+F3→定义名称对话框。

②表达式分为算术表达式(数学公式)和逻辑表达式。逻辑表达式的结果只有两个:TRUE(真)和FALSE(假),一共六种关系运算符和三种逻辑运算符。
=(等于)、<(小于)、<=(小于等于)、>(大于)、>=(大于等于)、<>(不等于)
OR(或)、AND(与)、NOT(非)

③在Excel单元格里输入「false」或「true」,会自动转化为大写为FALSE(假)和TRUE(真),在四则运算中,TRUE可以当1计算,FALSE可以当0计算。

④数组(Array,繁体版称作阵列)是由数据元素组成的集合,数据无素可以是数值、文本、日期、逻辑、错误值等,数据元素以行和列的形式组织起来,构成一个数据矩阵。在Microsoft_Excel有两类数组:区域数组和常量数组。
常量数组用{}将构成数组的常量括起来,行中的元素用逗号,分隔,行之间用分号;分隔。区域数组实际上是单元格区域,数据存储在单元格中,公式必须引用单元格才能调用数据。
一个数组其实就是一组同类型的数据,可以当做一个整体来处理,而无须对每个单元格一一应用公式。数组公式可以可以对一批单元格应用一个公式,产生单个结果,也可以同时分列显示多个结果。

Excel常用函数

1)AND函数(与)
AND(logical1,logical2,...)
如果所有参数值均为逻辑「真(TRUE)」,则返回逻辑「真(TRUE)」,反之返回逻辑「假(FALSE)」。
如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值「#VALUE!」或「#NAME」。

2)OR函数(或)
OR(logical1,logical2,...)
任何一个参数逻辑值为TRUE,即返回TRUE;
AND只要有一个参数是FALSE则结果是FALSE,OR函数只要有一个参数是TRUE则结果就是TRUE。

3)NOT函数(非)
用于对参数值求反。NOT函数只有一个参数,可以是一个值,也可以是一个表达式。
NOT(TRUE)=FALSE;NOT(FALSE)=TRUE

4)IF函数
IF(logical_test,value_if_true,value_if_false)
IF函数就是判断logical_test是否为TRUE或者FALSE(逻辑表达式logical_test成立或者计算结果非0则为TRUE),如果是TRUE则执行参数2,如果FALSE则执行参数3。

5)COUNT计数
COUNT(value1,value2,...)
返回包含数字以及包含参数列表中的数字的单元格的个数(数字、日期、或以文本代表的数字都计算在内,'+数字不计算在内)。
参数value为包含或引用各种类型数据的参数。COUNT函数最多可以有30个参数。数字、逻辑值、文本数字、日期值、空单元格、单元格和区域的引用地址、名称都可以作为参数。
=COUNT("B1","D1","123","hello")=0,这里没有一个数字,B1和D1因为加了引号,不是单元格,变成字符了。

COUNTif函数是计算区域中满足给定条件的单元格的个数。
语法格式:COUNTIF(range,criteria)
=COUNTIF(A2:A5,"apples")计算A2-A5列中苹果所在单元格的个数。
=COUNTIF(B2:B5,">55")计算B2-B5列中值大于55的单元格个数。

6)SUM求和,是Excel中使用最多的函数(西格玛∑)。
SUM(number1,number2,...) 
number参数包括数字(1、2、3)、逻辑值(1+2=3)、也可以是表达式(1+2)、单元格区域、数组、公式。
①=SUM((1+2=3),(1+2)),结果为4。(1+2=3)表达式的结果为真,文本值被转换成数字,而逻辑值「TRUE」被转换成数字1;1+2表达式的结果3会被计算。
②SUM不计算引用单元格中的表达式、不计算非数字字符。若A1为「1+2」、B1为「1+2=3」、C1为「A」,D1=SUM(A1,B1,C1),结果为0。
③SUM函数中的参数不能超过30个。
④SUM(A1:B2)=A1+A2+B1+B2,参数可以单元格区域。
⑤选中一行或一列后,点击西格玛∑按钮或快捷键(Alt+=)

SUM函数也可以用来求差
SUM(A1,-B1)=A1-B1

SUMIF函数根据指定条件对若干单元格求和。
语法格式:SUMIF(range,criteria,sum_range)(sum_range)
如果参数sum_range与range是一个区域,就可省略,则条件区域就是实际求和区域。

7)求积函数(Product产品)
=PRODUCT(A1:B2,100)=A1*A2*B1*B2*100

PRODUCT函数也可以用来求商
=PRODUCT(A1,B1^(-1))=A1/B1

8)乘积求和函数
将数组间对应的元素相乘,并返回乘积之和。
=SUMPRODUCT(A1:A3,B1:B3,C1:C3);
=SUMPRODUCT({2;5;9;7;1},{3;5;7;2;6},{4;6;8;7;2})(数组公式);
=A1*B1*C1+A2*B2*C2+A3*B3*C3;
=SUMPRODUCT(ROW(1:100))=SUMPRODUCT(ROW(1:100),1)(只有一组就是就是直接求和)

9)求余函数(两数相除求余)
=MOD(100,33)
=1

10)取整函数(两数相除取整)
=QUOTIENT(100,33)(Excel2003没有这个函数)
=INT(100/33)(INT函数
=3

11)AVERAGE函数:求出所有参数的算术平均值
AVERAGE(number1,number2,……)
在B8单元格中输入公式:=AVERAGE(B7:D7,F7:H7,7,8),确认后,即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。

12)时间函数
①=today(),返回当前时间(快捷键Ctrl+;→输入当前时间)
②=now(),返回当前日期和时间(快捷键Ctrl+:→输入当前日期和时间)

13)文本提取函数
①RIGHT(string,N)从一个文本字符串的最后一个字符开始,截取指定数目的字符。
Right()函数返回NULL;
string是包含要提取字符的文本字符串,也可以是单元格引用;
如果缺省N,则默认值日为1;
如果N的值大于string字符串的长度,返回整个string字符串。

②LEFT(string,N)从一个文本字符串的第一个字符开始,截取指定数目的字符。

③MID(string,start_num,num_chars)从string的start_num,截取num_chars个字符。
MID函数只能从左向右提取的。

14)查询与引用函数
LOOKUP与VLOOKUP和HLOOKUP属于一类函数,是最常用的“查找和引用”的函数,VLOOKUP是按列查找,HLOOKUP是按行查找。
①Lookup
②VLOOKUP(查询值,数据查询范围,返回值的行序号,逻辑值TRUE或FALSE)
③HLOOKUP(查询值,数据查询范围,返回值的行序号,逻辑值TRUE或FALSE)

④COLUMN列函数-返回引用的行号
=COLUMN(A10),返回A10所在的列号;
=COLUMN(),返回函数COLUMN所在单元格的列号。

⑤ROW行函数-返回引用的行号
=ROW(1:1)=1,返回第一行的行号;
=ROW(A1:A10)={1,2,3,4..10},选择对应的单元格区域如B2:B11,按F2输入函数公式,再按Ctrl+Shift+Enter,返回A1-A10的列行号;
=ROW(A:A)={1,2,3,...65536},选择对应的单元格区域如B列,按F2输入函数公式,再按Ctrl+Shift+Enter,返回A列行号。

函数提示工具

①如图所示红色区域即为函数提示工具。

②函数提示设置
Excel2003→工具栏→选项→常规→勾选「函数工具提示」复选框。
Excel2007→Office按钮→Excel选型→高级→显示-显示函数屏幕提示

③fx按钮-插入函数对话框
工具栏→自定义→命令→插入→插入函数→左键拖动到工具栏的空白处。
Shift+F3插入函数对话框

关键词:Excel

赞助商链接: