Excel自定义公式

Excel自定义公式李晓平数据计算（1）自定义公式方式（2）函数方式什么是公式Excel中的公式是一种对工作表中的数值进行计算的等式，它可以帮助用户快速的完成各种复杂的运算。公式以“=”开始，其后是公式的表达式，如“=A1+A2”例如:公式”=(A2+67)/SUM(B2:F2)”单元格引用=(A2+67)/SUM(B2:F2)除法运算符函数加法运算符常量单元格区域引用公式通常由运算符和参与运算的操作数组成。运算符算术运算符含义示例+（加号）加法3+3–（减号）减法负数3-1-1*（星号）乘法3*3/（正斜杠）除法3/3%（百分号）百分比20%^（脱字号）乘方3^2比较运算符含义示例=（等号）等于A1=B1（大于号）大于A1B1（小于号）小于A1B1=（大于等于号）大于等于A1=B1=（小于等于号）小于等于A1=B1（不等号）不等于A1B1文本运算符含义示例&（与号）将两个文本值连接或串起来产生一个连续的文本值(North&wind)引用运算符含义示例:（冒号）区域运算符，生成对两个引用之间的所有单元格的引用，包括这两个引用B5:B15,（逗号）联合运算符，将多个引用合并为一个引用SUM(B5:B15,D5:D15)（空格）交叉运算符，生成对两个引用共同的单元格的引用B7:D7C6:C8EXCEL函数的结构•Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。Excel函数结构:也有一些函数是没有参数的,如ROW()左右括号成对出现单一结构嵌套结构参数与参数之间使用半角逗号进行分隔函数参数常用符号或表示方法函数公式中的文本必须用半角引号，如：〝东南汽车〞;而非直接输入东南汽车或“东南汽车”连接符:&如：〝东南〞&〝汽车〞的值为东南汽车空值/空格的表示法:空值：〝〞空格：〝〞相关数学符号:（不等于）;=（大于等于）;=（小于等于）单元格引用表示法:A2;$A$2;$A2;A$2;A2:B7；1:1；1:5；F:F；A：NIF函数的简单案例一性别为男者,称谓为XX先生;性别为女者,称谓为XX女士姓名性别称谓公式张三男张三先生=IF(B2=男,A2&先生,A2&女士)李四女李四女士Sumif:条件求和函数•用途：根据指定条件对若干单元格、区域或引用求和。•语法：SUMIF(条件区域，条件，需求和的区域)•参数：条件是由数字、逻辑表达式等组成的判定条件。Sumif案例单位姓名工资人资组李宁5000总务组陈忠和12000装配组张湘祥8200人资组郭晶晶4000人资组张怡宁3000总务组石智勇5000焊装组田亮7200请统计人资组同仁的工资总额:Sumif(A:A,〝人资组〞,C:C)Countif：条件计数函数。•用途：计算区域中满足给定条件的单元格的个数。•语法：COUNTIF(统计区域，条件)•参数：“统计区域”为需要计算其中满足条件的单元格数目的单元格区域。“条件”为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。Countif案例以上述样表为据,完成以下案例:例1：依“师员”字段，统计师员级的人数countif(C:C,〝师〞)；countif(C:C,〝员〞)例2:统计全公司年资大於等於3年的人数及大于等于3年小于5年的人数countif(I:I,〞=3〞)；countif(I:I,〞=3〞)-countif(I:I,〞=5〞)例3:检测到职编号是否有重复值countif(A:A,A2)ABCDEFGHI到职编号单位代码师员单位名称职务代码职务名称到职日期性别工作年限061964B316师服务保证课108课长1996-6-3男12.2104489B322员零件开发课231零件供应仓管员2000-9-20男7.9130230d222师开发四课117资深工程师2003-4-15男5.3150192D32C员乘用车初检线212车检技术员2005-4-8男3.4160350C131师涂装课121助理工程师2006-7-3男2.1170183C432员熔接二课226焊接技术员2008-7-1男0.1061971B322师零件开发课115高级工程师1996-6-3女12.2Counta•用途：返回参数组中非空值的数目。利用函数COUNTA可以计算数组或单元格区域中数据项的个数。•语法：COUNTA(单元格区域1,单元格区域2……)说明：参数的个数为1～30个。Counta的思考案例例1：如果A1=6.28、A2=3.74，其余单元格为空，则公式“=COUNTA(A1:A7)”的计算结果等于?。例2：统计C列的非空白单元格的表示方法为：？;若整张EXCEL表的每个单元格都有数据,前述函数公式的结果为?MOD：取余•用途：返回两数相除的余数，其结果的正负号与除数相同。•语法：MOD(被除数，除数)•参数：除数不能为零。Mod函数的案例实例1：公式“=MOD(14，4)”返回?；“=MOD(-5，-2)”返回？。实例2:如何利用MOD函数，将整张EXCEL表的偶数行都标上底色Vlookup•返回表格或数组当前行中指定列处的数值。•语法：VLOOKUP(索引值，数据区域，列序号，查找方式)•参数：索引值为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。数据区域为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。列序号指数据区域中待返回的匹配值的列序号。它等于1时，返回数据区域第一列中的数值；它等于2时，返回数据区域第二列中的数值，以此类推。查找方式为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为1或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于索引值的最大数值；如果为0，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。字符函数Left/Right：截取字符串函数•用途：根据指定的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。•语法：LEFT(文本,返回的字符数)Right(文本,返回的字符数)•参数：返回的字符数必须大于或等于0。•实例：如果A1=电脑爱好者，则LEFT(A1，2)返回?;Right(A1,3)返回?Left与right的综合运用如何利用Left及Right从身份证号中提取出生年份。身份证号出生年份3526241982082263191982310104196501204855196535010219531228054119533501021964073104371964350103195105110036195135010219440318031419443501021968080105741968350102195410110597195435010219531220045X19533501021977050745291977350102197405253613197435010319680403024019683501021966071803661966提示：假如A2存放身份证号，提取年份的做法：=RIGHT(LEFT(A2,10),4)Mid：截取字符串函数•用途：MID返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。•语法：MID(文本串,开始位置,返回字符的个数)•参数：开始位置是文本中要提取的第一个字符的位置，文本中第一个字符的开始位置为1，以此类推。•实例1：如果a1=东南(福建)汽车工业有限公司，则公式“=MID(A1，4，2)”返回“福建”。•案例:利用MID从身份证号中提取出生年份。GoReplace：替换指定位置处的任意文本•用途：REPLACE使用其他文本串并根据所指定的字符数替换另一文本串中的部分文本。•语法：REPLACE(需替换其部分字符的文本，被替换字符的起始位置，替换的字符个数，用于替换到原文本中去的字符)。•思考：EXCEL本身就有替换功能，为什么要用replace函数？----解决替换字符多变的问题Replace的案例案例一：请分别说出下列两个函数公式的返回值案例二：如何用replace取出身份证号中的出生年月LEN：计算字符长度。•用途：LEN返回文本串的字符数。•语法：LEN(text)。•参数：Text待要查找其长度的文本。•注意：此函数用于双字节字符，且空格也将作为字符进行统计。•实例：如果A1=电脑爱好者，则公式“=LEN(A1)”返回？If、Len及mid函数综合运用AB1CM1002CM0010022CM1071CM0010713CM1103CM0011034CM778-BCM00778-B5CM1020CM0010206CM00848CM008487CM00969-ACM00969-A8CM924CM009249CM1100CM001100如果A列里CM后没有“00”的话，就把“00“加到CM后,但是如果CM后有”00“话，就不加了（产生的效果如B列所示），怎么做？公式提示：=IF(MID(A1,3,2)00,CM00&MID(A1,3,LEN(A1)-2),A1)SUBSTITUTE：替换指定文本•用途：在文字串中用新文本替代旧文本。如果需要在一个文字串中替换指定的文本，可以使用函数SUBSTITUTE；如果需要在某一文字串中替换指定位置处的任意文本，就应当使用函数REPLACE。•语法：SUBSTITUTE(需要替换其中字符的文本，需被替换的旧文本，用於替换的文本，[第几次出现的旧文本])。•参数：[第几次出现的旧文本]为一数值，用来指定以新的文本替换第几次出现的旧文本；如果指定了该参数，则只有满足要求的旧文本被替换；否则将用新文本替换所有出现的旧文本。SUBSTITUTE、MID、LEN的综合运用案例将左边的物料代码中无序空格,处理为右边的统一格式：物料代码效果CM1002CM1002CM1071CM1071CM1103CM1103CM778-BCM778-BCM1020CM1020CM00848CM00848CM00969-ACM00969-ACM924CM924CM1100CM1100ROUND：对数值进取四舍五入。•用途：按指定位数四舍五入某个数字。•语法：ROUND(需四舍五入的数字，保留的小数位)•注意：如果保留的小数位大于0，则四舍五入到指定的小数位；如果等于0，则四舍五入到最接近的整数；如果小于0，则在小数点左侧按指定位数四舍五入。•实例：如果A1=65.25，则公式“=ROUND(A1，1)”返回65.3；=ROUND(82.149，2)返回82.15；=ROUND(21.5，-1)返回20；Round(A1,0)返回65。INT:无条件取整•用途：将任意实数向下取整为最接近的整数。•语法：INT(实数)•参数：Number为需要处理的任意一个实数。•实例：如果A1=16.24、A2=-28.389，则公式“=INT(A1)”返回16，=INT(A2)返回-29。YEAR\MONTH\DAY用途：返回某日期的年份/月份/日期。语法：YEAR/MONTH/DAY(日期值)实例：假设A2单元格存放的日期为2008-8-20公式“=YEAR(A2)返回2008”公式“=MONTH(A2)返回8”公式“=DAY(A2)返回20”常规函数SumAverageMaxMinCountCountaIf