Excel表格必学的常用函数

Excel表格必学的常用函数countif()1、条件计数，统计区域内符合条件的个数，不过好像只能做到单个条件计数。如统计区域al:b5内单元格内容为“是”的个数，=countif(al:b5,"是”)。多条件计数见sumproduct()。2、与sumproduct()结合，用于统计区域内不同个数总数。见sumproduct()。find()、search()、len()、left()、mid()、right()字符查找函数，字符个数统计及字符左中右函数1、find()，字符查找函数，返回值是查找的字符在查找串中的位置。如在7854912645中查找5，=find(5,7854912645)返回值是3；也可以引用单元格进行查找，如5在al，7854912645在a2，=find(a1,a2)，返回3。查找的字符也可以是字符串，返回值是与查找字符串相匹配的第一个字符在被查找串中的位置，如在7854912645中查找45，=find(45,7854912645)，返回值是9。2、search()，字符查找函数，返回值是查找的字符在查找串中的位置，与find()不同的是，search()可以自定义查找的起始位置。如在7854912645中查找5，=search(5,7854912645)或=$6&『比(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」ookup,—个逻辑值，指定希望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是一个引用使用表。sheet1ABC1北京010...2广东020...3海南030...要在sheet2中引用“北京”的某个数据值，可以在sheet2的某一单元格输入：=vlookup("北京",sheet1!a:c,2,0)，返回010。公式中含义：“北京”，是要查找比对的值，也可以引用单元格；“sheetl!a:c”是查找的范围；“2”是查找到匹配数值后要求返回的顺数列的对应值；“0”，是匹配要求为精确匹配。返回的值是，在查找范围内按垂直方向对“北京”进行查找，找到第一个匹配值停止，返回对应的第“2”列的值。3、hlookup()与vlookup()查找方向相反，为按水平方向(行方向)进行查找，用法一致。if()，判断函数1、用作对别的单元格进行运算判断，根据判断结果显示返回值，公式不能引用到自身单元格。公式原型：=if(判断条件，符合条件返回的值，不符合条件返回的值)。判断条件只是针对单一单元格，不能对区域进行判断。公式中可以重复套用if()函数，最多能镶嵌7层。套用镶嵌，可以做多条件判断。例：如a1=1时，若b1=1，则c1=1；若b1v>1,则c1=2；如a1v>1,若b1=2,则c1=3；若b1<>2，贝9c1=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()函数除可以对别的单元格进行判断外，也可以对自身单元格进行条件判断。公式可以利用格式刷复制到别的单元格。条件格式的设定。选中单元格，点击菜单栏：格式-条件格式，打开对话框，如图：彌格式冈条件选择“公式”然后在公示栏输入判断条件。例：如al内的值>100，则改变单元格al的格式(如改变字体、背景)。则输入：=if(a1>100,true,false),然后选择条件成立的格式。x.蛊件1-U皆式Vtrow,fd.s.4)JSJ.r03I一「一确定即可完成，一个单元格的条件格式最多能设置三个。如果输入的判断条件有交集，则以条件的先后顺序有优先权。比如的判断条件设了三个，而单元格同时满足了条件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(a1v>0,mod(a1,2)=0),”偶数”,if(and(a1v>0,mod(a1,2)=1),”奇数”,if(a1v>0,”非整数”,0)))3、与sumproduct()结合，用于统计区域范围内的奇偶数个数。如,统计区域a1:a20内的奇偶数个数：奇数：=sumproduct(mod(a1:a20,2))偶数：=sumproduct((mod(a1:a20,2)=0)*(a1:a20v>0))rank()函数返回指定值在范围内的排序名次，指定值必须包含在范围内。公式原型:=rank(指定的数值或引用单元格，排名范围，排名方式)。排名方式为0,则从大到小进行排名；排名方式为非0,则从小到大进行排名。如：=rank(al,al:a4,0),返回值是al的值在区域a1~a4中从大到小的名次。round()，取舍函数1、round()取舍函数，直接用作四舍五入。对公式计算结果或者非自身单元格的值进行取舍，如果要对自身单元格进行取舍，自身单元格必须是公式。用法：=round(数值或公式，精度)。精度使用整数表示，正整数表示取小数点右边的位数，0表示取舍到整数，负数表示取小数点左边的位数。如:=round(a1,-3),返回al单元格数值的精确到“千位”2、roundup()，进一法取舍，对取舍位数后非0值进行进1的取舍。3、rounddown()，去一法取舍，对取舍位数后值直接去除的取舍。与取整函数trunc()的用法相近，rounddown()函数必须输入取舍精度，trunc()可以不输入取舍精度，默认取整数。sum()求和函数，用于对某几个单元格或某区域单元格进行求和，可以对非连续单元格或非规则区域进行求和。用法：=sum(数值1或单元格1或区域1,数值2或单元格2或区域2,)sumif()，条件求和1、单个条件求和，简单举例如下：J--'J-ABCDE-品种产地批次数2苹果山东120003苹果山东215004苹果山不213005苹果河北19000苹果山忘33DO橙子山忘15DO8y=10如上表格，需要计算进货苹果总数，输入函数式：=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(al:a5)Al〜Ml的数值和=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*c53、多条件计数统计符合多条件的个数。如:如要统计符合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()函数的用法以及返回值均一致。