Excel表格必学的常用函数

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

2、645 中查找 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,78549

3、12645)或=search(5,7854912645,1)，默认从第一个字符开始查找，返回 3；=search(5,7854912645,3)，从第三个字符开始查找，返回10;也可以引用单元格进行查找。3、len( ) ，统计字符串的长度，也可以引用单元格。女口： =len(7854912645)，返回 10。4、left( ) ，返回字符串中从左边起的某几个字符，如果要求长度超过字符串总长度，那么 返回全部，可以引用单元格。如： =left(7854912645,3) ，返回 785；5、 mid( ) ，返回字符串中从左边某位置开始的某几个字符，如果要求长度超过字符串总 长度，那么返回全

4、部，可以引用单元格。如： =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(

5、) 、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 为

6、 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 列中

7、的值，否那么 vlookup 可能无法返回正确的值； 如果 range_lookup 为 false 或 0，那么不需要对 table_array 第 1 列中的值进行排序。如 果 range_lookup 参数为 false 或 0， vlookup 将只查找精确匹配值。如果 table_array 的 第一列中有两个或更多值与 lookup_value 匹配，那么使用第一个找到的值。如果找不到 精确匹配值，那么返回错误值。2、 vlookup 函数是按垂直方向 (列方向 )进行数值查找，返回对应量。例如：sheetl是一个数据源的综合表，sheet2是一个引用使用表。sheet1ABC1北

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

9、单元格进行运算判断，根据判断结果显示返回值，公式不能引用到自身 单元格。公式原型： =if( 判断条件，符合条件返回的值，不符合条件返回的值 ) 。判断条件只 是针对单一单元格，不能对区域进行判断。公式中可以重复套用if( )函数，最多能镶嵌 7 层。套用镶嵌，可以做多条件判断。例：女口 a仁1 时，假设 b1=1，贝U c1=1;假设 b11，贝U c1=2;女口 a11，假设 b1=2， 那么c仁3;假设b12，那么c1=4 ;在 c1 输入： =if(a1=1,if(b1=1,1,2),if(b1=2,3,4) 。这属于对串联条件进行判断，就是条 件是一级一级往下的。可以利用 and(

10、)函数或 or( )函数对并联条件进行判断。例：女口 a1=1, b仁1，贝U c1=1，否那么 c仁2。=if(and(a1=1,b仁1),1,2)。 例：女口 a仁1 或者 b1=1，贝U c1=1，否那么 c1=2。=if(or(a仁1,b1=1),1,2)。 所有逻辑函数都可以作为 if( ) 函数的条件进行判断应用。2、 在excel单元格的条件格式中，if()函数除可以对别的单元格进行判断外，也可以对 自身单元格进行条件判断。公式可以利用格式刷复制到别的单元格。条件格式的设定。选中单元格，点击菜单栏：格式 -条件格式，翻开对话框，如图：条件选择“公式，然后在公示栏输入判断条件。例：

11、如 al内的值100,那么改变单 元格al的格式(如改变字体、背景)。那么输入：=if(a1100,true,false)，然后选择条件成 立的格式。确定即可完成，一个单元格的条件格式最多能设置三个。如果输入的判断条件有交 集，那么以条件的先后顺序有优先权。比方的判断条件设了三个，而单元格同时满足了 条件1和条件2，那么会采用条件1。mod()1、 mod()函数的原意是，返回商的余数。公式原型为：=mod(被除数，除数)。可以是对 数值或者单元格进行引用。女口： =mod(25,3)，返回的是 25/3的余数 1。2、用于判断整数是奇数还是偶数。女口：单元格a1内为一自然数，判断奇偶，在 b

12、1显示。在b1输入：=if(mod(a1,2)=0,偶数 ,if(mod(a1,2)=1,奇数,非整数)因为0是个存在争议的自然数，也可以避开0来判断：=if(and(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()函数返回指定值在范围内的排序

13、名次，指定值必须包含在范围内。公式原型:定的数值或引用单元格，排名范围，排名方式)。排名方式为0,那么从大到小进行排名；排名方式为非0，那么从小到大进行排名。女口： =rank(a1,a1:a4,0)，返回值是 al的值在区域 a1a4中从大到小的名次。round()，取舍函数1、round()取舍函数，直接用作四舍五入。对公式计算结果或者非自身单元格的值进行 取舍，如果要对自身单元格进行取舍，自身单元格必须是公式。用法：=round(数值或公式，精度)。精度使用整数表示，正整数表示取小数点右边的 位数，0表示取舍到整数，负数表示取小数点左边的位数。女口： =round(a1,-3)，返回al

14、单元格数值的精确到千位。2、 roundup()，进一法取舍，对取舍位数后非0值进行进1的取舍。3、 rounddown()，去一法取舍，对取舍位数后值直接去除的取舍。与取整函数trunc()的用法相近，rounddown()函数必须输入取舍精度，trunc()可以不输入取舍精度，默认取整数。sum()求和函数，用于对某几个单元格或某区域单元格进行求和，可以对非连续单元格或非规那么区域进行求和。用法：=sum(数值1或单元格1或区域1,数值2或单元格2或区域2,) sumif()，条件求和1、单个条件求和，简单举例如下:X/4, VrA1CDE品种产地批友敷星2苹果120003山东215004

15、苹旱山西2苹杲河北19000苹呆山忘33QQV橙子山东1他8如上表格，需要计算进货苹果总数，输入函数式：=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

16、()函数不受镶嵌层的影响，可以并联多个条件，见后 sumproduct()函数。Sumproduct( )1、和与sum()函数几乎等同的应用方式。但是只能应用在同一列或同一行连续格的数值求和，或者一片连续的矩形区域求和，不能应用与跳格的数值求和。如： A1A5 的数值和 =sumproduct(a1: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、多条件求和统计符合多条件的对应数值和女口：如要统计符合 A1A1