Excel函数日常办公应用

1含义表达式2含义表达式sum函数设定条件表达式案例输入公式时或输入完毕后必须按【Ctrl+Shift+Enter】组合键结束，否则无法得到正确结果Excel常用函数整理if函数logical_test:参数可使用任何比较运算符（一个标记或符号，指定表达式内执行的计算的类型。有数学、比较、逻辑和引用运算符等）表达如：1.G10表格内=if（A3=花岗岩踢脚,F10,0),表达为指定区域数值等于指定数值或是指定表格值（含文本），则在编辑区域显示需要值；2.G10表格内=if(sum(A10:A32)0,sum(A10:A32),)，表达为指定区域不等于零时(表达式为：sum(A10:A32)0)，编辑表格区域显示求和值，否则不显示以空白表示(假设E5表格内为空白，使C7=E5时，表达式可以写成C7E5,即可以表达出汇总空白区域的数值和，或是用另一种表达方式C7=,按下ctrl+shift+enter结束.接下来举例解释：【SUM(IF($P$6:$P699M714,$O$6:$O699,0))=SUM(IF($P$6:$P699=,$O$6:$O699,0))+ctrl+shift+enter结束,此编辑表格内容表达为P6：P699区域内对应的各excel表格，内容为空格的对应的O6:O669数值进行求和)；3.G10表格内=if(f1160,(if(and(f1190),优秀，合格),不合格))，用于表达学生成绩等（其中：and(f1190)可以直接写成f1190）4.G10表格内=if(f1150000,10%,15%)*f11；用于表达销售人员奖金计算；5.G10表格内==IF(A289,A,(IF(A279,B,(IF(A269,C,(IF(A259,D,F))))如果参数为错误值或为不能转换成数字的文本，将会导致错误；表达如：sum（1，A10：A20,26，F15：F35)，1.如数值求和：SUM(3,2)等于5；2.又如文本3、数值2、文本true，则求和SUM(3,2,TRUE)等于6，因为文本值被转换成数字，而逻辑值TRUE被转换成数字1。又如换一种表达方法，如果A1=3（文本），而B1=TRUE，则：SUM(A1,B1,2)=2，因为对非数值型的值的引用不能被转换成数值，所以表达为：sum(A1,B1,2)=6。1、数组求和：=SUM((G12:G21100)*G12:G21)[公式说明]：本公式为数组公式，可以对G12:G21区域中大于100的数据进行求和，而排除小于等于100的数据。【使用注意】：1、公式中G12:G21100部分表示求和条件，后跟实际求和区域G12:G21。如果有多个条件，可一并罗列出来。例如求大于100且小于115的数据之和，公式如下:=SUM(G12:G21100)*(G12:G21115)*G12:G21)。2、此数组公式只适用于单个区域求和，如果有多个区域，只能用多个SUM求和，然后相加。例如对G12:G21和H12:H21区域中大于100的数汇总，公式如下:=SUM(SUM((G12:G21100)*G12:G21),SUM((H12:H21100)*H12:H21))。2、多条件数组求和1：=SUM(((G23:G32100)+(G23:G32110))*G23:G32)[公式说明]：对于两个范围求和，即满足两个条件中任意一个条件都进行求和，这和同时满足两个条件才求和的公式思路不同。案例1的使用注意中已提到，多个条件同时满足才求和需要用*连接所有条件，表示同时满足多条件，而本案例中对多个条件用+来连接，表示对于多条例中满足任意一个条件就可以进行求和。【使用注意】：1、多条件满足任意条件即求和，在设置公式时对于所有条件需要用+连接，如本例中(G23:G32100)+(G23:G32110),然后对条件用括号括起来再与实际求和区相乘。如果缺少条件的外括号将得到错误结果。2、如果不只两个条件，也用同样方式将所有条件罗列出来并相加。例如求区域中小于100或者等于120的数据和，那么数组公式如下:=SUM(((G23:G32100)+(G23:G32=110)+(G23:G32=120))*G23:G32)3、多条件求和2：=SUM((H34:H43=一车间)*(I34:I43=男)*J34:J43)[公式说明]：本公式以H、I两列数据作为限制条件，对J列数据汇总。“(H34:H43=一车间)*(I34:I43=男)”表示同时满足两个条件，如果还有更多条件，也可以同时罗列出来，利用“*”符号连接，然后再与求和区相乘，得到最后的汇总结果。【使用注意】：1、前三个案例是以求和数据自身为条件对目标区域进行求和，本案例以不同的区域作为限制，条件区有两个，对第三区域进行求和。在设置公式时，三个区域的单元格个数必须一致。例如以下公式将产生错误结果:=SUM((H34:H43=一车间)*(I34:I44=男)*J34:J43)、=SUM((H34:H43=一车间)*(I35:I44=男)*J34:J43)。2、多条件求和时，条件录用区域与求和区域可以在不同列，三个区域只需要宽度、高度一致即可。它们所在的列可以不同，起止行也可以不同，甚至三个区域可以分别位于不同的工作表中，仍然可以得到需要的结果。例如条件区在Sheet2工作表，求和区在当前工作表，公式可以修改为:=SUM((Sheet2!H34:H43=一车间)*(Sheet2!I34:I43=男)*J34:J43)。3、本公式可以用另一种简化写法，仍然可以返回正确结果:=SUM((H34:H43&I34:I43=一车间男)*J34:J43)执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。IF(logical_test,value_if_ture,value_if_false)(其中logical：翻译“合乎逻辑的”)表示计算结果为TRUE或FALSE的任意值或表达式如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本将被忽略；直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算；常用表达返回某一单元格区域中数字、逻辑值及数字的文本表达式之和SUM(number1,number2,...)常用表达3含义表达式4含义表达式ExpressionNumdecimalplaces5含义表达式含义6含义表达式sum函数sumif函数round函数设定条件表达式案例输入公式时或输入完毕后必须按【Ctrl+Shift+Enter】组合键结束，否则无法得到正确结果必选项。数值或是公式表达式被四舍五入；count函数countifaverage函数根据指定条件对若干单元格求和。自定义项，以数字表示。数字表明小数点右边有多少位进行四舍五入。如果小数位数是负数,则round()返回的结果在小数点左端包含指定个零.如果省略，则Round函数返回整数。SUMIF(range,criteria,sum_range)表达式应用案例sum_range（求和范围）：需要求和的实际单元格1.SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。如：在工资表中需要分别计算各个科室的工资发放情况，要计算销售部2001年5月加班费情况。则在F15种输入公式为=SUMIF($C10:$C36,销售部,$F10:$F36)，其中$C10:$C36为提供逻辑判断依据的单元格区域，销售部为判断条件即只统计$C10:$C36区域中部门为销售部的单元格，$F10:$F36为实际求和的单元格区域。2.sum(if($P$6:$P99=B70,$N$6:$N99,0)),表达式意思为：p6:p99范围内各表格数值等于b70表格中内容的，所有相对应的n6:n99表格内所有的数值进行求和；criteria（标准）：为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本range(范围)：为用于条件判断的单元格区域4、用SUM函数计数：=SUM((H94:H102=男)*(I94:I102=汉))【公式说明】：SUM函数虽然是求和函数，在很多时候可以用来计数。本例中利用H94:H101=男这一表达式返回一串包含TRUE和FALSE的数组，最后用输入公式时或输入完毕后必须按【Ctrl+Shift+Enter】组合键结束，得到正确结果，否则无法得到正确结果。【使用注意】：1、表达式H94:H101=男得到的是包含逻辑值的数组。SUM函数可以对逻辑参数进行求和，但是包含于数组中的逻辑值值却会被忽略。例如以下公式:=SUM(TRUE,1)—结果为2，=SUM({TRUE,1})结果为1。但是通过*1将逻辑值转换成数值后就可以直接求和了。例如:=SUM({TRUE,1}*1)—结果为2。COUNT(value1,value2,...)返回按指定位数进行四舍五入的数值。Round(expression,numdecimalplaces)表达式应用案例1.在Excel办公软件中计算参数列表中的数字项的个数。2.在数据库（sqlserver或者access）中可以用来统计符合条件的数据条数。2.用来统计满足某个条件的单元格的个数。统计A1:A10单元格区域内数值大于90的单元格的个数就输入如下公式：=countif（A1:A10,＞90）一共2个参数：第一个参数:对象的范围；第二个参数:if的判定条件；说明：函数COUNT在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去；但是错误值或其他无法转化成数字的文字则被忽略；1.如:=COUNTIF(A10:A36,男)计算A列中内容是男的个数;返回参数平均值（算术平均）。AVERAGE(Number1,Number2……)AVERAGE(A1:A5)等于SUM(A1:A5)/COUNT(A1:A5)等于117含义表达式textstart_numnum_chars说明8含义表达式textnum_chars说明9含义表达式textnum_chars说明10含义表达式11含义表达式find_textWithin_textfind函数mid函数left函数right函数int函数要提取字符的字符串表达式，从中返回字符。如果text包含Null，则返回Null；text中被提取的字符部分的开始位置（被提取字符的第一个位置）。如果start超过了text中字符的数目，Mid将返回零长度字符串()。要返回的字符数。如果省略或num_chars超过文本的字符数（包括start处的字符），将返回字符串中从start_num到字符串结束的所有字符。例：M=4100　　A1=Mid(M,1,1)A1=4　　A2=Mid(M,2,2)A2=10例：E6=D20,则20=mid(E6,find(D,E6)+1,len(E6));解释：len(E6)=3,超出了计取的数位，默认计算计取到末位，或是为了保证能计取到末尾还有多余2位数时的值，所以此种办法也是防止丢值出现错误；要提取字符的字符串表达式，从中返回字符。如果string包含Null，将返回Null。字符串函数，从字符串中返回指定数目的字符。MID(text,start_num,num_chars)得到字符串左部指定个数的字符left(text,num_chars)要判断text中字符的数目，可使用Len函数。用来对原始数据中某个字符串进行定位，以确定其位置。Find函数进行定位时，总是从指定位置开始，返回找到的(即：定位的字一个字符)第一个匹配字符串的位置，而不管其后是否还有相匹配的字符串。函数执行成功时返回text字符串左边n个字符，发生错误时返回空字符串（）。如果任何参数的值为NULL(空的（）)，Left()函数返回NULL。如果n的值大于text字符串的长度，那么Left()函数返回整个text字符串，但并不