Excel表格必学的常用函数.doc

《Excel表格必学的常用函数.doc》由会员分享，可在线阅读，更多相关《Excel表格必学的常用函数.doc（5页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel表格必学的常用函数countif( ) 1、条件计数，统计区域内符合条件的个数，不过好像只能做到单个条件计数。如统计区域a1:b5内单元格内容为“是”的个数，=countif(a1:b5,”是”)。多条件计数见sumproduct( )。2、与sumproduct( )结合，用于统计区域内不同个数总数。见sumproduct( )。find( )、search( )、len( )、left( )、mid( )、right( )字符查找函数，字符个数统计及字符左中右函数1、find( )，字符查找函数，返回值是查找的字符在查找串中的位置。如在中查找5，=find(5,)返回值是3；也可

2、以引用单元格进行查找，如5在a1，在a2，=find(a1,a2)，返回3。查找的字符也可以是字符串，返回值是与查找字符串相匹配的第一个字符在被查找串中的位置，如在中查找45，=find(45,)，返回值是9。2、search( )，字符查找函数，返回值是查找的字符在查找串中的位置，与find( )不同的是，search( )可以自定义查找的起始位置。如在中查找5，=search(5,)或=search(5,1)，默认从第一个字符开始查找，返回3；=search(5,3)，从第三个字符开始查找，返回10；也可以引用单元格进行查找。3、len( )，统计字符串的长度，也可以引用单元格。如：=le

3、n()，返回10。4、left( )，返回字符串中从左边起的某几个字符，如果要求长度超过字符串总长度，则返回全部，可以引用单元格。如：=left(,3)，返回785；5、mid( )，返回字符串中从左边某位置开始的某几个字符，如果要求长度超过字符串总长度，则返回全部，可以引用单元格。如：=mid(,2,3)，返回854；6、right ( )，返回字符串中从右边起的某几个字符，如果要求长度超过字符串总长度，则返回全部，可以引用单元格。如：=right(,3)，返回645；7、可以其中几个函数一起应用。如：返回左边开始的第一个“5”到第二个“4”之间的字符，=mid(,(find(5,)+1),

4、search(4,(find(4,)+1)- find(5,)-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时，返

5、回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列中的值，否

6、则vlookup可能无法返回正确的值；如果range_lookup为false或0，则不需要对table_array第1列中的值进行排序。如果range_lookup参数为false或0，vlookup将只查找精确匹配值。如果 table_array 的第一列中有两个或更多值与lookup_value匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值。2、vlookup函数是按垂直方向(列方向)进行数值查找，返回对应量。例如：sheet1是一个数据源的综合表，sheet2是一个引用使用表。sheet1 A B C1 北京 010 .2 广东 020 .3 海南 030 .要在she

7、et2中引用“北京”的某个数据值，可以在sheet2的某一单元格输入：=vlookup(北京,sheet1!a:c,2,0)，返回010。公式中含义：“北京”，是要查找比对的值，也可以引用单元格；“sheet1!a:c”是查找的范围；“2”是查找到匹配数值后要求返回的顺数列的对应值；“0”，是匹配要求为精确匹配。返回的值是，在查找范围内按垂直方向对“北京”进行查找，找到第一个匹配值停止，返回对应的第“2”列的值。3、hlookup( )与vlookup( )查找方向相反，为按水平方向(行方向)进行查找，用法一致。if( )，判断函数1、用作对别的单元格进行运算判断，根据判断结果显示返回值，公式

8、不能引用到自身单元格。公式原型：=if(判断条件，符合条件返回的值，不符合条件返回的值)。判断条件只是针对单一单元格，不能对区域进行判断。公式中可以重复套用if( )函数，最多能镶嵌7层。套用镶嵌，可以做多条件判断。例：如a1=1时，若b1=1，则c1=1；若b11，则c1=2；如a11，若b1=2，则c1=3；若b12，则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(a

9、1=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(a1100,true,false)，然后选择条件成立的格式。确定

10、即可完成，一个单元格的条件格式最多能设置三个。如果输入的判断条件有交集，则以条件的先后顺序有优先权。比如的判断条件设了三个，而单元格同时满足了条件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(a

11、nd(a10,mod(a1,2)=0),”偶数”,if(and(a10,mod(a1,2)=1),”奇数”,if(a10,”非整数”,0)3、与sumproduct( )结合，用于统计区域范围内的奇偶数个数。如，统计区域a1:a20内的奇偶数个数：奇数：=sumproduct(mod(a1:a20,2)偶数：=sumproduct(mod(a1:a20,2)=0)*(a1:a200)rank( )函数返回指定值在范围内的排序名次，指定值必须包含在范围内。公式原型：=rank(指定的数值或引用单元格，排名范围，排名方式)。排名方式为0，则从大到小进行排名；排名方式为非0，则从小到大进行排名。如：

12、=rank(a1,a1:a4,0)，返回值是a1的值在区域a1a4中从大到小的名次。round( )，取舍函数1、round( )取舍函数，直接用作四舍五入。对公式计算结果或者非自身单元格的值进行取舍，如果要对自身单元格进行取舍，自身单元格必须是公式。用法：=round(数值或公式，精度)。精度使用整数表示，正整数表示取小数点右边的位数，0表示取舍到整数，负数表示取小数点左边的位数。如：=round(a1,-3)，返回a1单元格数值的精确到“千位”。2、roundup( )，进一法取舍，对取舍位数后非0值进行进1的取舍。3、rounddown( )，去一法取舍，对取舍位数后值直接去除的取舍。与

13、取整函数trunc( )的用法相近，rounddown( )函数必须输入取舍精度，trunc( )可以不输入取舍精度，默认取整数。sum( )求和函数，用于对某几个单元格或某区域单元格进行求和，可以对非连续单元格或非规则区域进行求和。用法：=sum(数值1或单元格1或区域1,数值2或单元格2或区域2,)sumif( )，条件求和1、单个条件求和，简单举例如下：如上表格，需要计算进货苹果总数，输入函数式：=sumif(a1:a8,苹果,d1:d8)，返回a1:a8范围符合条件是“苹果”的单元格对应区域d1:d10内的数值和。2、多个条件求和，返回符合多个条件对应的数值和。如同上表格，需要计算从山

14、东进货苹果的总数，输入函数式：=sum(if(a1:a8=苹果,if(b1:b8=山东,d1:dD8)多个条件可以使用if继续往下加进去，但是公式只能镶嵌7层。公式输完成后，须同时按 ctrl+shift+enter才能使公式生效。多条件求和也可以使用sumproduct( )，同时sumproduct( )函数不受镶嵌层的影响，可以并联多个条件，见后sumproduct( )函数。Sumproduct( )1、和与sum()函数几乎等同的应用方式。但是只能应用在同一列或同一行连续格的数值求和，或者一片连续的矩形区域求和，不能应用与跳格的数值求和。如：A1A5的数值和=sumproduct(a

15、1:a5) A1M1的数值和=sumproduct(a1:m1) A1B10的数值和=sumproduct(a1:b10)2、乘积和两个或以上对应的数列乘积和。如=sumproduct(a1:a5,b1:b5,c1:c5) 结果为 a1*b1*c1+a2*b2*c2+a3*b3*c3+a5*b5*c53、多条件计数统计符合多条件的个数。如：如要统计符合 A1A100中内容为“是”，B1B100中内容为“否”的个数=sumproduct(a1:a100=”是”)*(b1:b100=”否”)*)4、多条件求和统计符合多条件的对应数值和如：如要统计符合 A1A100中内容为“是”，B1B100中内容为“否” C1C100对应数值的和=sumproduct(a1:a100=”是”)*(b1:b100=”否”),(c1:c100)5、用于统计区域中不同的个数总数，区域中不能存在空单元格如统计区域 a1：b100内存在的不同个数总数=sumproduct(1/countif(a1:b100,a1:b100)trunc( )、取整函数函数原型：=trunk(取整数值，精度)，精度可以缺省，缺省时默认为取整数。有精度要求时，与rounddown( )函数的用法以及返回值均一致。