Excel表格必学的常用函数

Excel表格必学的常用函数

countif( )

1、条件计数，统计区域内符合条件的个数，不过好像只能做到单个条件计数。

如统计区域a1:b5内单元格内容为“是”的个数，

=countif(a1:b5,”是”)。

多条件计数见sumproduct( )。

2、与sumproduct( )结合，用于统计区域内不同个数总数。见sumproduct( )。

find( )、search( )、len( )、left( )、mid( )、right( )字符查找函数，字符个数统计及字符左中右函数

1、find( )，字符查找函数，返回值是查找的字符在查找串中的位置。

如在7854912645中查找5，=find(5,7854912645)返回值是3；

也可以引用单元格进行查找，如5在a1，7854912645在a2，=find(a1,a2)，返回3。

查找的字符也可以是字符串，返回值是与查找字符串相匹配的第一个字符在被查找串中的位置，如在7854912645中查找45，=find(45,7854912645)，返回值是9。

2、search( )，字符查找函数，返回值是查找的字符在查找串中的位置，与find( )不同的

是，search( )可以自定义查找的起始位置。

如在7854912645中查找5，

=search(5,7854912645)或=search(5,7854912645,1)，默认从第一个字符开始查找，返回3；

=search(5,7854912645,3)，从第三个字符开始查找，返回10；

也可以引用单元格进行查找。

3、len( )，统计字符串的长度，也可以引用单元格。

如：=len(7854912645)，返回10。

4、left( )，返回字符串中从左边起的某几个字符，如果要求长度超过字符串总长度，则

返回全部，可以引用单元格。

如：=left(7854912645,3)，返回785；

5、mid( )，返回字符串中从左边某位置开始的某几个字符，如果要求长度超过字符串总

长度，则返回全部，可以引用单元格。

如：=mid(7854912645,2,3)，返回854；

6、right ( )，返回字符串中从右边起的某几个字符，如果要求长度超过字符串总长度，

则返回全部，可以引用单元格。

如：=right(7854912645,3)，返回645；

7、可以其中几个函数一起应用。

如：返回左边开始的第一个“5”到第二个“4”之间的字符，

=mid(7854912645,(find(5,7854912645)+1),search(4,7854912645,(find(4,7854912645)+1 ))- find(5,7854912645)-1)，返回49126。

hlookup( )、vlookup( ) 查找和引用函数

1、完整的公式是：vlookup(lookup_value, table_array, col_index_num, [range_lookup])。

lookup_value，是要在区域的第一列中搜索的值。lookup_value可以是值或引用。

table_array，包含数据的区域。可以使用对区域（例如，A2:D8）或区域名称的引用。

col_index_num，table_array参数中必须返回的匹配值的列号。col_index_num参数为

1时，返回table_array第1列中的值；col_index_num为2时，返回table_array第2列中的值，依此类推。如果col_index_num 参数小于1，则vlookup 返回错误值；大于table_array的列数，则vlookup返回错误值。

range_lookup，一个逻辑值，指定希望vlookup查找精确匹配值还是近似匹配值：如果range_lookup为true或被省略，则返回精确匹配值或近似匹配值。如果找不到精确匹配值，则返回小于lookup_value的最大值，另如果range_lookup为true或被省略，则必须按升序排列table_array第1列中的值，否则vlookup可能无法返回正确的值；

如果range_lookup为false或0，则不需要对table_array第1列中的值进行排序。如果range_lookup参数为false或0，vlookup将只查找精确匹配值。如果table_array 的第一列中有两个或更多值与lookup_value匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值。

2、vlookup函数是按垂直方向(列方向)进行数值查找，返回对应量。

例如：sheet1是一个数据源的综合表，sheet2是一个引用使用表。

sheet1

A B C

1 北京010 ...

2 广东020 ...

3 海南030 ...

要在sheet2中引用“北京”的某个数据值，可以在sheet2的某一单元格输入：

=vlookup("北京",sheet1!a:c,2,0)，返回010。

公式中含义：“北京”，是要查找比对的值，也可以引用单元格；“sheet1!a:c”是查找的范围；“2”是查找到匹配数值后要求返回的顺数列的对应值；“0”，是匹配要求为精确匹配。返回的值是，在查找范围内按垂直方向对“北京”进行查找，找到第一个匹配值停止，返回对应的第“2”列的值。

3、hlookup( )与vlookup( )查找方向相反，为按水平方向(行方向)进行查找，用法一致。

if( )，判断函数

1、用作对别的单元格进行运算判断，根据判断结果显示返回值，公式不能引用到自身

单元格。

公式原型：=if(判断条件，符合条件返回的值，不符合条件返回的值)。判断条件只是针对单一单元格，不能对区域进行判断。公式中可以重复套用if( )函数，最多能镶嵌7层。套用镶嵌，可以做多条件判断。

例：如a1=1时，若b1=1，则c1=1；若b1<>1，则c1=2；如a1<>1，若b1=2，则c1=3；

若b1<>2，则c1=4；

在c1输入：=if(a1=1,if(b1=1,1,2),if(b1=2,3,4))。这属于对串联条件进行判断，就是条件是一级一级往下的。

可以利用and( )函数或or( )函数对并联条件进行判断。

例：如a1=1，b1=1，则c1=1，否则c1=2。=if(and(a1=1,b1=1),1,2)。

例：如a1=1或者b1=1，则c1=1，否则c1=2。=if(or(a1=1,b1=1),1,2)。

所有逻辑函数都可以作为if( )函数的条件进行判断应用。

2、在excel单元格的条件格式中，if( )函数除可以对别的单元格进行判断外，也可以对

自身单元格进行条件判断。公式可以利用格式刷复制到别的单元格。

条件格式的设定。

选中单元格，点击菜单栏：格式-条件格式，打开对话框，如图：

条件选择“公式”，然后在公示栏输入判断条件。例：如a1内的值>100，则改变单元格a1的格式(如改变字体、背景)。则输入：=if(a1>100,true,false)，然后选择条件成立的格式。

确定即可完成，一个单元格的条件格式最多能设置三个。如果输入的判断条件有交集，则以条件的先后顺序有优先权。比如的判断条件设了三个，而单元格同时满足了条件1和条件2，则会采用条件1。

mod( )

1、mod( )函数的原意是，返回商的余数。公式原型为：=mod(被除数,除数)。可以是对

数值或者单元格进行引用。

如：=mod(25,3)，返回的是25/3的余数1。

2、用于判断整数是奇数还是偶数。

如：单元格a1内为一自然数，判断奇偶，在b1显示。在b1输入：

=if(mod(a1,2)=0,”偶数”,if(mod(a1,2)=1,”奇数”,”非整数”))

因为0是个存在争议的自然数，也可以避开0来判断：

=if(and(a1<>0,mod(a1,2)=0),”偶数”,if(and(a1<>0,mod(a1,2)=1),”奇数”,if(a1<>0,”非整数”,0)))

3、与sumproduct( )结合，用于统计区域范围内的奇偶数个数。

如，统计区域a1:a20内的奇偶数个数：

奇数：=sumproduct(mod(a1:a20,2))

偶数：=sumproduct((mod(a1:a20,2)=0)*(a1:a20<>0))

rank( )

函数返回指定值在范围内的排序名次，指定值必须包含在范围内。公式原型：=rank(指

定的数值或引用单元格，排名范围，排名方式)。排名方式为0，则从大到小进行排名；

排名方式为非0，则从小到大进行排名。

如：=rank(a1,a1:a4,0)，返回值是a1的值在区域a1~a4中从大到小的名次。

round( )，取舍函数

1、round( )取舍函数，直接用作四舍五入。对公式计算结果或者非自身单元格的值进行

取舍，如果要对自身单元格进行取舍，自身单元格必须是公式。

用法：=round(数值或公式，精度)。精度使用整数表示，正整数表示取小数点右边的位数，0表示取舍到整数，负数表示取小数点左边的位数。

如：=round(a1,-3)，返回a1单元格数值的精确到“千位”。

2、roundup( )，进一法取舍，对取舍位数后非0值进行进1的取舍。

3、rounddown( )，去一法取舍，对取舍位数后值直接去除的取舍。与取整函数trunc( )

的用法相近，rounddown( )函数必须输入取舍精度，trunc( )可以不输入取舍精度，默认取整数。

sum( )

求和函数，用于对某几个单元格或某区域单元格进行求和，可以对非连续单元格或非规则区域进行求和。

用法：=sum(数值1或单元格1或区域1,数值2或单元格2或区域2,……)

sumif( )，条件求和

1、单个条件求和，简单举例如下：

如上表格，需要计算进货苹果总数，输入函数式：

=sumif(a1:a8,"苹果",d1:d8)，返回a1:a8范围符合条件是“苹果”的单元格对应区域d1:d10内的数值和。

2、多个条件求和，返回符合多个条件对应的数值和。

如同上表格，需要计算从山东进货苹果的总数，输入函数式：

=sum(if(a1:a8="苹果",if(b1:b8="山东",d1:dD8)))

多个条件可以使用if继续往下加进去，但是公式只能镶嵌7层。公式输完成后，须同时按ctrl+shift+enter才能使公式生效。

多条件求和也可以使用sumproduct( )，同时sumproduct( )函数不受镶嵌层的影响，可以并联多个条件，见后sumproduct( )函数。

Sumproduct( )

1、和

与sum()函数几乎等同的应用方式。但是只能应用在同一列或同一行连续格的数值求和，或者一片连续的矩形区域求和，不能应用与跳格的数值求和。

如：A1~A5的数值和=sumproduct(a1:a5)

A1~M1的数值和=sumproduct(a1:m1)

A1~B10的数值和=sumproduct(a1:b10)

2、乘积和

两个或以上对应的数列乘积和。

如=sumproduct(a1:a5,b1:b5,c1:c5) 结果为a1*b1*c1+a2*b2*c2+a3*b3*c3+…+a5*b5*c5

3、多条件计数

统计符合多条件的个数。

如：如要统计符合A1~A100中内容为“是”，B1~B100中内容为“否”……的个数=sumproduct((a1:a100=”是”)*(b1:b100=”否”)*…)

4、多条件求和

统计符合多条件的对应数值和

如：如要统计符合A1~A100中内容为“是”，B1~B100中内容为“否”…… C1~C100对应数值的和

=sumproduct((a1:a100=”是”)*(b1:b100=”否”),(c1:c100))

5、用于统计区域中不同的个数总数，区域中不能存在空单元格

如统计区域a1：b100内存在的不同个数总数

=sumproduct(1/countif(a1:b100,a1:b100)

trunc( )、取整函数

函数原型：=trunk(取整数值，精度)，精度可以缺省，缺省时默认为取整数。有精度要求时，与rounddown( )函数的用法以及返回值均一致。