Excel的常用函数类型和使用方法在Excel2010中, Excel 提供的常用函数类型,和使用直接输入法和“插入函数”按钮等输入函数的方法这些内容,如果我们想做出高技术含量的表格都是要学习的,下面就为大家简单的接扫下Excel的常用函数类型和使用方法。
1、函数的分类
Excel 提供了大量的函数,表1列出了常用的函数类型和使用范例。
表1
使用函数时,应首先确认已在单元格中输入了“=”号,即已进入公式编辑状态。接下来可输入函数名称,再紧跟着一对括号,括号内为一个或多个参数,参数之间要用逗号来分隔。用户可以在单元格中手工输入函数,也可以使用函数向导输入函数。
手工输入函数
手工输入一般用于参数比较单一、简单的函数,即用户能记住函数的名称、参数等,此时可直接在单元格中输入函数。
步骤1 打开一个学生成绩表,单击要输入函数的单元格,然后输入等号、函数名、左括号、具体参数(此处为单元格区域引用)和右括号,如图1左图所示。
图1
步骤2 单击编辑栏中的“输入”按钮或按【Enter】键,得到计算结果,如图1右图所示。
使用函数向导输入函数
如果不能确定函数的拼写或参数,可以使用函数向导输入函数。
步骤1 单击要输入函数的单元格,然后单击编辑栏中的“插入函数”按钮,如图2所示,打开“插入函数”对话框,在“或选择类别”下拉列表中选择“常用函数”类,然后在“选择函数”列表中选择“AVERAGE”函数,如图3所示。
左图2和右图3
步骤2 单击“确定”按钮,打开“函数参数”对话框,单击Number1 编辑框右侧的压缩对话框按钮,如图4左图所示。
图4
步骤3 在工作表中选择要求平均分的单元格区域C2:C11,如图4右图所示,然后单击展开对话框按钮返回“函数参数”对话框。
步骤4 单击“函数参数”对话框中的“确定”按钮得到结果,如图5右图所示。再向右拖动C13 单元格的填充柄至E13 单元格,利用复制函数功能计算出其他科的平均分。
图5
此外,也可选中Excel单元格后在“公式”选项卡的“函数库”组中单击某个类型的函数,在展开的列表中选择具体的函数,然后在打开的“函数参数”对话框输入或选择函数参数后确定即可,根据上面Excel的常用函数类型和使用方法,我们已经简单的了解和学会了简单的使用Excel函数了。
目录 一、IF函数——————————————————————————————————2 二、ASC函数—————————————————————————————————4 三、SEARCH函数——————————————————————————————4 四、CONCATENATE函数———————————————————————————4 五、EXACT函数———————————————————————————————5 六、find函数—————————————————————————————————5 七、PROPER函数——————————————————————————————7 八、LEFT函数————————————————————————————————7 九、LOWER函数———————————————————————————————7 十、MID函数————————————————————————————————8 十一、REPT函数———————————————————————————————8 十二、Replace函数——————————————————————————————9 十三、Right函数———————————————————————————————10 十四、UPPER函数——————————————————————————————10 十五、SUBSTITUTE函数———————————————————————————10 十六、VALUE函数——————————————————————————————12 十七、WIDECHAR函数———————————————————————————12 十八、AND函数———————————————————————————————12 十九、NOT函数———————————————————————————————13 二十、OR函数————————————————————————————————13 二十一、COUNT函数—————————————————————————————14 二十二、MAX函数——————————————————————————————15 二十三、MIN函数——————————————————————————————15 二十四、SUMIF函数—————————————————————————————16 二十五、OFFSET函数————————————————————————————17 二十六、ROW函数——————————————————————————————20 二十七、INDEX 函数————————————————————————————21 二十八、LARGE函数—————————————————————————————22 二十九、ADDRESS函数————————————————————————————23 三十、Choose函数——————————————————————————————24 三十一、HLOOKUP函数———————————————————————————24 三十二、VLOOKUP函数———————————————————————————26 三十三、LOOKUP函数————————————————————————————29 三十四、MATCH函数————————————————————————————29 三十五、HYPERLINK函数——————————————————————————30 三十六、ROUND函数————————————————————————————31 三十七、TREND函数—————————————————————————————32
excel常用函数公式介绍 excel常用函数公式介绍1:MODE函数应用 1MODE函数是比较简单也是使用最为普遍的函数,它是众数值,可以求出在异地区域或者范围内出现频率最多的某个数值。 2例如求整个班级的普遍身高,这时候我们就可以运用到了MODE 函数了 3先打开插入函数的选项,之后可以直接搜索MODE函数,找到求众数的函数公式 4之后打开MODE函数后就会出现一个函数的窗口了,我们将所要求的范围输入进Number1选项里面,或者是直接圈选区域 5之后只要按确定就可以得出普遍身高这一个众数值了 excel常用函数公式介绍2:IF函数应用 1IF函数常用于对一些数据的进行划分比较,例如对一个班级身高进行评测 2这里假设我们要对身高的标准要求是在170,对于170以及170之上的在备注标明为合格,其他的一律为不合格。这时候我们就要用到IF函数这样可以快捷标注好备注内容。先将光标点击在第一个备注栏下方 3之后还是一样打开函数参数,在里面直接搜索IF函数后打开 4打开IF函数后,我们先将条件填写在第一个填写栏中, D3>=170,之后在下面的当条件满足时为合格,不满足是则为不合格 5接着点击确定就可以得到备注了,这里因为身高不到170,所以备注里就是不合格的选项 6接着我们只要将第一栏的函数直接复制到以下所以的选项栏中就可以了
excel常用函数公式介绍3:RANK函数应用 2这里我们就用RANK函数来排列以下一个班级的身高状况 3老规矩先是要将光标放于排名栏下面第一个选项中,之后我们打开函数参数 4找到RANK函数后,我们因为选项的数字在D3单元格所以我们就填写D3就可了,之后在范围栏中选定好,这里要注意的是必须加上$不然之后复制函数后结果会出错 5之后直接点击确定就可以了,这时候就会生成排名了。之后我们还是一样直接复制函数黏贴到下方选项栏就可以了。
Excel表格中的一些基本函数使用方法 一、输入三个“=”,回车,得到一条双直线; 二、输入三个“~”,回车,得到一条波浪线; 三、输入三个“*”或“-”或“#”,回车,惊喜多多; 在单元格内输入=now()显示日期 在单元格内输入=CHOOSE(WEEKDAY(I3,2),"星期一","星期二","星期三","星期四","星期五","星期六","星期日") 显示星期几 Excel常用函数大全 1、ABS函数 函数名称:ABS 主要功能:求出相应数字的绝对值。 使用格式:ABS(number) 参数说明:number代表需要求绝对值的数值或引用的单元格。 应用举例:如果在B2单元格中输入公式:=ABS(A2),则在A2单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数(如100)。 特别提醒:如果number参数不是数值,而是一些字符(如A等),则B2中返回错误值“#VALUE!”。 2、AND函数 函数名称:AND 主要功能:返回逻辑值:如果所有参数值均为逻辑“真(TRUE)”,则返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。
使用格式:AND(logical1,logical2, ...) 参数说明:Logical1,Logical2,Logical3……:表示待测试的条件值或表达式,最多这30个。 应用举例:在C5单元格输入公式:=AND(A5>=60,B5>=60),确认。如果C5中返回TRUE,说明A5和B5中的数值均大于等于60,如果返回FALSE,说明A5和B5中的数值至少有一个小于60。 特别提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!”或“#NAME”。 3、AVERAGE函数 函数名称:AVERAGE 主要功能:求出所有参数的算术平均值。 使用格式:AVERAGE(number1,number2,……) 参数说明:number1,number2,……:需要求平均值的数值或引用单元格(区域),参数不超过30个。 应用举例:在B8单元格中输入公式: =AVERAGE(B7:D7,F7:H7,7,8),确认后,即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。 特别提醒:如果引用区域中包含“0”值单元格,则计算在内;如果引用区域中包含空白或字符单元格,则不计算在内。 4、COLUMN 函数 函数名称:COLUMN 主要功能:显示所引用单元格的列标号值。
个常用的E x c e l函数公 式 Document serial number【UU89WT-UU98YT-UU8CB-UUUT-UUT108】
15个常用的Excel函数公式,拿来即用 1、查找重复内容 =IF(COUNTIF(A:A,A2)>1,"重复","") 2、重复内容首次出现时不提示 =IF(COUNTIF(A$2:A2,A2)>1,"重复","") 3、重复内容首次出现时提示重复 =IF(COUNTIF(A2:A99,A2)>1,"重复","") 4、根据出生年月计算年龄
=DATEDIF(A2,TODAY(),"y") 5、根据身份证号码提取出生年月 =--TEXT(MID(A2,7,8),"0-00-00") 6、根据身份证号码提取性别 =IF(MOD(MID(A2,15,3),2),"男","女") 7、几个常用的汇总公式 A列求和:=SUM(A:A) A列最小值:=MIN(A:A) A列最大值:=MAX (A:A) A列平均值:=AVERAGE(A:A)
A列数值个数:=COUNT(A:A) 8、成绩排名 =(A2,A$2:A$7) 9、中国式排名(相同成绩不占用名次) =SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1 10、90分以上的人数 =COUNTIF(B1:B7,">90")
11、各分数段的人数 同时选中E2:E5,输入以下公式,按Shift+Ctrl+Enter =FREQUENCY(B2:B7,{70;80;90}) 12、按条件统计平均值 =AVERAGEIF(B2:B7,"男",C2:C7) 13、多条件统计平均值 =AVERAGEIFS(D2:D7,C2:C7,"男",B2:B7,"销售")
电子表格常用函数公式 1、自动排序函数: =RANK(第1数坐标,$第1数纵坐标$横坐标:$最后数纵坐标$横坐标,升降序号1降0升) 例如:=RANK(X3,$X$3:$X$155,0) 说明:从X3 到X 155自动排序 2、多位数中间取部分连续数值: =MID(该多位数所在位置坐标,所取多位数的第一个数字的排列位数,所取数值的总个数) 例如:612730************在B4坐标位置,取中间出生年月日,共8位数 =MID(B4,7,8) =19820711 说明:B4指该数据的位置坐标,7指从第7位开始取值,8指一共取8个数字 3、若在所取的数值中间添加其他字样, 例如:612730************在B4坐标位置,取中间出生年、月、日,要求****年**月**日格式 =MID(B4,7,4)&〝年〞&MID(B4,11,2) &〝月〞& MID(B4,13,2) &〝月〞&
=1982年07月11日 说明:B4指该数据的位置坐标,7、11指开始取值的第一位数排序号,4、2指所取数值个数,引号必须是英文引号。 4、批量打印奖状。 第一步建立奖状模板:首先利用Word制作一个奖状模板并保存为“奖状.doc”,将其中班级、姓名、获奖类别先空出,确保打印输出后的格式与奖状纸相符(如图1所示)。 第二步用Excel建立获奖数据库:在Excel表格中输入获奖人以及获几等奖等相关信息并保存为“奖状数据.xls”,格式如图2所示。 第三步关联数据库与奖状:打开“奖状.doc”,依次选择视图→工具栏→邮件合并,在新出现的工具栏中选择“打开数据源”,并选择“奖状数据.xls”,打开后选择相应的工作簿,默认为sheet1,并按确定。将鼠标定位到需要插入班级的地方,单击“插入域”,在弹出的对话框中选择“班级”,并按“插入”。同样的方法完成姓名、项目、等第的插入。 第四步预览并打印:选择“查看合并数据”,然后用前后箭头就可以浏览合并数据后的效果,选择“合并到新文档”可以生成一个包含所有奖状的Word文档,这时就可以批量打印了。
excel常用函数及使用方法 一、数字处理 (一)取绝对值:=ABS(数字) (二)数字取整:=INT(数字) (三)数字四舍五入:=ROUND(数字,小数位数) 二、判断公式 (一)把公式返回的错误值显示为空: 1、公式:C2=IFERROR(A2/B2,"") 2、说明:如果是错误值则显示为空,否则正常显示。 (二)IF的多条件判断 1、公式:C2=IF(AND(A2<500,B2="未到期"),"补款","") 2、说明:两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 (一)统计两表重复 1、公式:B2=COUNTIF(Sheet15!A:A,A2) 2、说明:如果返回值大于0说明在另一个表中存在,0则不存在。 (二)统计年龄在30~40之间的员工个数 公式=FREQUENCY(D2:D8,{40,29} (三)统计不重复的总人数 1、公式:C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 2、说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。
(四)按多条件统计平均值 =AVERAGEIFS(D:D,B:B,"财务",C:C,"大专") (五)中国式排名公式 =SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9))) 四、求和公式 (一)隔列求和 1、公式:H3=SUMIF($A$2:$G$2,H$2,A3:G3) 或=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3) 2、说明:如果标题行没有规则用第2个公式 (二)单条件求和 1、公式:F2=SUMIF(A:A,E2,C:C) 2、说明:SUMIF函数的基本用法 (三)单条件模糊求和 说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。 (四)多条求模糊求和 1、公式:=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 2、说明:在sumifs中可以使用通配符* (五)多表相同位置求和 1、公式:=SUM(Sheet1:Sheet19!B2) 2、说明:在表中间删除或添加表后,公式结果会自动更新。
电子表格常用函数公式 1.去掉最高最低分函数公式: =SUM(所求单元格…注:可选中拖动?)—MAX(所选单元格…注:可选中拖动?)—MIN(所求单元格…注:可选中拖动?) (说明:“SUM”是求和函数,“MAX”表示最大值,“MIN”表示最小值。)2.去掉多个最高分和多个最低分函数公式: =SUM(所求单元格)—large(所求单元格,1)—large(所求单元格,2) —large(所求单元格,3)—small(所求单元格,1) —small(所求单元格,2) —small(所求单元格,3) (说明:数字123分别表示第一大第二大第三大和第一小第二小第三小,依次类推) 3.计数函数公式: count 4.求及格人数函数公式:(”>=60”用英文输入法) =countif(所求单元格,”>=60”) 5.求不及格人数函数公式:(”<60”用英文输入法) =countif(所求单元格,”<60”) 6.求分数段函数公式:(“所求单元格”后的内容用英文输入法) 90以上:=countif(所求单元格,”>=90”) 80——89:=countif(所求单元格,”>=80”)—countif(所求单元格,”<=90”) 70——79:=countif(所求单元格,”>=70”)—countif(所求单元
格,”<=80”) 60——69:=countif(所求单元格,”>=60”)—countif(所求单元格,”<=70”) 50——59:=countif(所求单元格,”>=50”)—countif(所求单元格,”<=60”) 49分以下: =countif(所求单元格,”<=49”) 7.判断函数公式: =if(B2,>=60,”及格”,”不及格”) (说明:“B2”是要判断的目标值,即单元格) 8.数据采集函数公式: =vlookup(A2,成绩统计表,2,FALSE) (说明:“成绩统计表”选中原表拖动,“2”表示采集的列数) 公式是单个或多个函数的结合运用。 AND “与”运算,返回逻辑值,仅当有参数的结果均为逻辑“真(TRUE)”时返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。条件判断 AVERAGE 求出所有参数的算术平均值。数据计算 COLUMN 显示所引用单元格的列标号值。显示位置 CONCATENATE 将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。字符合并 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。条件统计 DATE 给出指定数值的日期。显示日期
15个常用的Excel函数公式,拿来即用 1、查找重复内容 =IF(COUNTIF(A:A,A2)>1,"重复","") 2、重复内容首次出现时不提示 =IF(COUNTIF(A$2:A2,A2)>1,"重复","") 3、重复内容首次出现时提示重复 =IF(COUNTIF(A2:A99,A2)>1,"重复","")
4、根据出生年月计算年龄 =DATEDIF(A2,TODAY(),"y") 5、根据身份证号码提取出生年月 =--TEXT(MID(A2,7,8),"0-00-00") 6、根据身份证号码提取性别 =IF(MOD(MID(A2,15,3),2),"男","女") 7、几个常用的汇总公式 A列求和:=SUM(A:A)
A列最小值:=MIN(A:A) A列最大值:=MAX (A:A) A列平均值:=AVERAGE(A:A) A列数值个数:=COUNT(A:A) 8、成绩排名 =RANK.EQ(A2,A$2:A$7) 9、中国式排名(相同成绩不占用名次) =SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1 10、90分以上的人数
=COUNTIF(B1:B7,">90") 11、各分数段的人数 同时选中E2:E5,输入以下公式,按Shift+Ctrl+Enter =FREQUENCY(B2:B7,{70;80;90}) 12、按条件统计平均值 =AVERAGEIF(B2:B7,"男",C2:C7) 13、多条件统计平均值 =AVERAGEIFS(D2:D7,C2:C7,"男",B2:B7,"销售")
1、ABS函数 函数名称:ABS 主要功能:求出相应数字的绝对值。 使用格式:ABS(number) 参数说明:number代表需要求绝对值的数值或引用的单元格。 应用举例:如果在B2单元格中输入公式:=ABS(A2),则在A2单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数(如100)。 特别提醒:如果number参数不是数值,而是一些字符(如A等),则B2中返回错误值“#VALUE!”。 2、AND函数 函数名称:AND 主要功能:返回逻辑值:如果所有参数值均为逻辑“真(TRUE)”,则返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。 使用格式:AND(logical1,logical2, ...) 参数说明:Logical1,Logical2,Logical3……:表示待测试的条件值或表达式,最多这30个。 应用举例:在C5单元格输入公式:=AND(A5>=60,B5>=60),确认。如果C5中返回TRUE,说明A5和B5中的数值均大于等于60,如果返回FALSE,说明A5和B5中的数值至少有一个小于60。 特别提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!”或“#NAME”。 3、AVERAGE函数 函数名称:AVERAGE 主要功能:求出所有参数的算术平均值。 使用格式:AVERAGE(number1,number2,……) 参数说明:number1,number2,……:需要求平均值的数值或引用单元格(区域),参数不超过30个。
应用举例:在B8单元格中输入公式:=AVERAGE(B7:D7,F7:H7,7,8),确认后,即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。 特别提醒:如果引用区域中包含“0”值单元格,则计算在内;如果引用区域中包含空白或字符单元格,则不计算在内。 4、COLUMN 函数 函数名称:COLUMN 主要功能:显示所引用单元格的列标号值。 使用格式:COLUMN(reference) 参数说明:reference为引用的单元格。 应用举例:在C11单元格中输入公式:=COLUMN(B11),确认后显示为2(即B列)。 特别提醒:如果在B11单元格中输入公式:=COLUMN(),也显示出2;与之相对应的还有一个返回行标号值的函数——ROW(reference)。 5、CONCATENATE函数 函数名称:CONCATENATE 主要功能:将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。 使用格式:CONCATENATE(Text1,Text……) 参数说明:Text1、Text2……为需要连接的字符文本或引用的单元格。 应用举例:在C14单元格中输入公式:=CONCATENATE(A14,"@",B14,".com"),确认后,即可将A14单元格中字符、@、B14单元格中的字符和.com连接成一个整体,显示在C14单元格中。 特别提醒:如果参数不是引用的单元格,且为文本格式的,请给参数加上英文状态下的双引号,如果将上述公式改为:=A14&"@"&B14&".com",也能达到相同的目的。 6、COUNTIF函数 函数名称:COUNTIF 主要功能:统计某个单元格区域中符合指定条件的单元格数目。 使用格式:COUNTIF(Range,Criteria) 参数说明:Range代表要统计的单元格区域;Criteria表示指定的条件表达式。
Excel常用函数公式大全 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式: =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式: =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和:=SUM(K2:K56) ——对K2到K56这一区域进行求和; 2、平均数:=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数; 3、排名:=RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名; 4、等级:=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评:=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩; 6、最高分:=MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分; 7、最低分:=MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分; 8、分数段人数统计: (1)=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数;假设把结果存放于K57单元格; (2)=COUNTIF(K2:K56,">=95")-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格; (3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格; (4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ——求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格;
excel博大精深,其使用中有许多细节的地方需要注意。 vlookup函数的使用,其语法我就不解释了,百度很多,其实我自己也没看懂语法的解释,下面就按照我自己的理解来说说怎么用的。首先,这个函数是将一个表中的数据导入另一个表中,其中这两个表有一列数据是相同项,但是排列顺序不同。举例说明; 表1 表2 将表1中的face量一列导入表2中,但两表中的名称一列的排列顺序是不同的。此时需要使用vlookup函数。 下面介绍vlookup的使用方法。
将鼠标放到表2中的D2单元格上,点击fx,会出现一个对话框,里面有vlookup函数。若在常用函数里面没有,下拉找“查找与引用”,里面有此函数。点确定。表示此函数是在表2中的D2单元格中应用。 此时出现对话框: 在第个格里输入B2,直接用鼠标在表2中点击B2单元格即可。表示需要在查找的对象是表2中的B2单元格中的内容。
然后是第二个格,点表1,用鼠标选择整个表的所有数据。表示要在表1中的B1—C14区域查找表2中的B2单元格中的内容。
第三个格里输入在表2中要导入的列数在表1中的列数的数字。在此例中为C列,其列数数字为2.表示将表1中(B1—C14)区域中查找到的单元格里的内容相对应的列(第2列)中的单元格中的内容(face量列中的数据)导入表2中相应的单元格(D2)。 最后一个格中输入“0”。表示查找不到就出现#N/A。点确定,即出现相应数据,然后下拉复制格式。
当下拉出现这种情况的时候: 其实是其查找区域在下拉过程中随着行的改变而改变了。需要对查找区域做一下固定。其方法为,在选择区域后,在区域前面加“$”号($B$1:$C$14)。
常用的excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式:C2 =IFERROR(A2/B2,"") 说明:如果是错误值则显示为空,否则正常显示。
2、IF多条件判断返回值 公式:C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明:两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明:如果返回值大于0说明在另一个表中存在,0则不存在。
2、统计不重复的总人数 公式:C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。 四、求和公式
1、隔列求和 公式:H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明:如果标题行没有规则用第2个公式 2、单条件求和 公式:F2 =SUMIF(A:A,E2,C:C) 说明:SUMIF函数的基本用法
3、单条件模糊求和 公式:详见下图 说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。
4、多条件模糊求和 公式:C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明:在sumifs中可以使用通配符* 5、多表相同位置求和 公式:b2 =SUM(Sheet1:Sheet19!B2) 说明:在表中间删除或添加表后,公式结果会自动更新。 6、按日期和产品求和
EXCEL中常用函数及使用方法 Excel函数一共有11类:数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。 1.数据库函数 当需要分析数据清单中的数值是否符合特定条件时,可以使用数据库工作表函数。例如,在一个包含销售信息的数据清单中,可以计算出所有销售数值大于1,000 且小于2,500 的行或记录的总数。Microsoft Excel 共有12 个工作表函数用于对存储在数据清单或数据库中的数据进行分析,这些函数的统一名称为Dfunctions,也称为D 函数,每个函数均有三个相同的参数:database、field 和criteria。这些参数指向数据库函数所使用的工作表区域。其中参数database 为工作表上包含数据清单的区域。参数field 为需要汇总的列的标志。参数criteria 为工作表上包含指定条件的区域。 2.日期与时间函数 通过日期与时间函数,可以在公式中分析和处理日期值和时间值。 3.工程函数 工程工作表函数用于工程分析。这类函数中的大多数可分为三种类型:对复数进行处理的函数、在不同的数字系统(如十进制系统、十六进制系统、八进制系统和二进制系统)间进行数值转换的函数、在不同的度量系统中进行数值转换的函数。 4.财务函数 财务函数可以进行一般的财务计算,如确定贷款的支付额、投资的未来值或净现值,以及债券或息票的价值。财务函数中常见的参数: 未来值(fv)--在所有付款发生后的投资或贷款的价值。 期间数(nper)--投资的总支付期间数。 付款(pmt)--对于一项投资或贷款的定期支付数额。 现值(pv)--在投资期初的投资或贷款的价值。例如,贷款的现值为所借入的本金数额。 利率(rate)--投资或贷款的利率或贴现率。 类型(type)--付款期间内进行支付的间隔,如在月初或月末。 5.信息函数 可以使用信息工作表函数确定存储在单元格中的数据的类型。信息函数包含一组称为IS 的工作表函数,在单元格满足条件时返回TRUE。例如,如果单元格包含一个偶数值,ISEVEN 工作表函数返回TRUE。如果需要确定某个单元格区域中是否存在空白单元格,可以使用COUNTBLANK 工作表函数对单元格区域中的空白单元格进行计数,或者使用ISBLANK 工作表函数确定区域中的某个单元格是否为空。 6.逻辑函数 使用逻辑函数可以进行真假值判断,或者进行复合检验。例如,可以使用IF 函数确定条件为真还是假,并由此返回不同的数值。
工作中最常用的excel函数公式大全 一、数字处理 1、取绝对值=ABS(数字) 2、取整=INT(数字) 3、四舍五入=ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式:C2=IFERROR(A2/B2,"") 说明:如果是错误值则显示为空,否则正常显示。 2、IF多条件判断返回值公式: C2=IF(AND(A2<500,B2="未到期"),"补款","") 说明:两个条件同时成立用AND,任一个成立用OR函数。
1、统计两个表格重复的内容 公式:B2=COUNTIF(Sheet15!A:A,A2) 说明:如果返回值大于0说明在另一个表中存在,0则不存在。 2、统计不重复的总人数 公式:C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。
1、隔列求和 公式:H3=SUMIF($A$2:$G$2,H$2,A3:G3) 或=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3) 说明:如果标题行没有规则用第2个公式 2、单条件求和 公式:F2=SUMIF(A:A,E2,C:C) 说明:SUMIF函数的基本用法
3、单条件模糊求和 公式:详见下图 说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。 4、多条件模糊求和 公式:C11=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明:在sumifs中可以使用通配符*
EXCEL常用函数大全(做表不求人!) 2013-12-03 00:00 我们在使用Excel制作表格整理数据的时候,常常要用到它的函数功能来自动统计处理表格中的数据。这里整理了Excel中使用频率最高的函数的功能、使用方法,以及这些函数在实际应用中的实例剖析,并配有详细的介绍。 1、ABS函数 函数名称:ABS 主要功能:求出相应数字的绝对值。 使用格式:ABS(number) 参数说明:number代表需要求绝对值的数值或引用的单元格。 应用举例:如果在B2单元格中输入公式:=ABS(A2),则在A2单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数(如100)。 特别提醒:如果number参数不是数值,而是一些字符(如A等),则B2中返回错误值“#VALUE!”。
2、AND函数 函数名称:AND 主要功能:返回逻辑值:如果所有参数值均为逻辑“真(TRUE)”,则返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。 使用格式:AND(logical1,logical2, ...) 参数说明:Logical1,Logical2,Logical3……:表示待测试的条件值或表达式,最多这30个。 应用举例:在C5单元格输入公式:=AND(A5>=60,B5>=60),确认。如果C5中返回TRUE,说明A5和B5中的数值均大于等于60,如果返回FALSE,说明A5和B5中的数值至少有一个小于60。 国美提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!”或“#NAME”。 3、AVERAGE函数 函数名称:AVERAGE 主要功能:求出所有参数的算术平均值。
EXCEL常用函数公式大全及举例 一、相关概念 (一)函数语法 由函数名+括号+参数组成 例:求和函数:SUM(A1,B2,…) 。参数与参数之间用逗号“,”隔开(二)运算符 1. 公式运算符:加(+)、减(-)、乘(*)、除(/)、百分号(%)、乘幂(^) 2. 比较运算符:大与(>)、小于(<)、等于(=)、小于等于(<=)、大于等于(>=)、不等于(<>) 3. 引用运算符:区域运算符(:)、联合运算符(,) (三)单元格的相对引用与绝对引用 例: A1 $A1 锁定第A列 A$1 锁定第1行 $A$1 锁定第A列与第1行 二、常用函数 (一)数学函数 1. 求和 =SUM(数值1,数值2,……) 2. 条件求和 =SUMIF(查找的范围,条件(即对象),要求和的范围) 例:(1)=SUMIF(A1:A4,”>=200”,B1:B4) 函数意思:对第A1栏至A4栏中,大于等于200的数值对应的第B1列至B4列中数值求和 (2)=SUMIF(A1:A4,”<300”,C1:C4)
函数意思:对第A1栏至A4栏中,小于300的数值对应的第C1栏至C4栏中数值求和 3. 求个数 =COUNT(数值1,数值2,……) 例:(1) =COUNT(A1:A4) 函数意思:第A1栏至A4栏求个数(2) =COUNT(A1:C4) 函数意思:第A1栏至C4栏求个数 4. 条件求个数 =COUNTIF(范围,条件) 例:(1) =COUNTIF(A1:A4,”<>200”) 函数意思:第A1栏至A4栏中不等于200的栏求个数 (2)=COUNTIF(A1:C4,”>=1000”) 函数意思:第A1栏至C4栏中大于等1000的栏求个数 5. 求算术平均数 =AVERAGE(数值1,数值2,……) 例:(1) =AVERAGE(A1,B2) (2) =AVERAGE(A1:A4) 6. 四舍五入函数 =ROUND(数值,保留的小数位数) 7. 排位函数 =RANK(数值,范围,序别) 1-升序 0-降序 例:(1) =RANK(A1,A1:A4,1) 函数意思:第A1栏在A1栏至A4栏中按升序排序,返回排名值。 (2) =RANK(A1,A1:A4,0) 函数意思:第A1栏在A1栏至A4栏中按降序排序,返回排名值。 8. 乘积函数 =PRODUCT(数值1,数值2,……) 9. 取绝对值 =ABS(数字) 10. 取整 =INT(数字) (二)逻辑函数
计算机等级考试 =公式名称(参数1,参数2,。。。。。) =sum(计算范围) =average(计算范围) =sumifs(求和范围,条件范围1,符合条件1,条件范围2,符合条件2,。。。。。。) =vlookup(翻译对象,到哪里翻译,显示哪一种,精确匹配) =rank(对谁排名,在哪个范围里排名) =max(范围) =min(范围) =index(列范围,数字) =match(查询对象,范围,0) =mid(要截取的对象,从第几个开始,截取几个) =int(数字) =weekday(日期,2) =if(谁符合什么条件,符合条件显示的内容,不符合条件显示的内容) =if(谁符合什么条件,符合条件显示的内容,if(谁符合什么条件,符合条件显示的内容,不符合条件显示的内容)) EXCEL的常用计算公式大全 一、单组数据加减乘除运算: ①单组数据求加和公式:=(A1+B1) 举例:单元格A1:B1区域依次输入了数据10和5,计算:在C1中输入=A1+B1 后点击键盘“Enter(确定)”键后,该单元格就自动显示10与5的和15。 ②单组数据求减差公式:=(A1-B1) 举例:在C1中输入=A1-B1即求10与5的差值5,电脑操作方法同上; ③单组数据求乘法公式:=(A1*B1) 举例:在C1中输入=A1*B1即求10与5的积值50,电脑操作方法同上; ④单组数据求乘法公式:=(A1/B1) 举例:在C1中输入=A1/B1即求10与5的商值2,电脑操作方法同上; ⑤其它应用: 在D1中输入=A1^3即求5的立方(三次方); 在E1中输入=B1^(1/3)即求10的立方根 小结:在单元格输入的含等号的运算式,Excel中称之为公式,都是数学里面的基本 运算,只不过在计算机上有的运算符号发生了改变——“×”与“*”同、“÷”与 “/”同、“^”与“乘方”相同,开方作为乘方的逆运算,把乘方中和指数使用成分数 就成了数的开方运算。这些符号是按住电脑键盘“Shift”键同时按住键盘第二排 相对应的数字符号即可显示。如果同一列的其它单元格都需利用刚才的公式计算,只 需要先用鼠标左键点击一下刚才已做好公式的单元格,将鼠标移至该单元格的右下 角,带出现十字符号提示时,开始按住鼠标左键不动一直沿着该单元格依次往下拉到 你需要的某行同一列的单元格下即可,即可完成公司自动复制,自动计算。
1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式: =TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3、从输入的18位身份证号的出生年月计算公式: =CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式: =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和:=SUM(K2:K56) ——对K2到K56这一区域进行求和; 2、平均数:=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数; 3、排名:=RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名; 4、等级:=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 5、学期总评:=K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩; 6、最高分:=MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分;
7、最低分:=MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分; 8、分数段人数统计: (1)=COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数;假设把结果存放于K57单元格; (2)=COUNTIF(K2:K56,">=95")-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格; (3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格; (4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ——求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格; (5)=COUNTIF(K2:K56,">=70")-SUM(K57:K60) ——求K2到K56区域70~84.5分的人数;假设把结果存放于K61单元格; (6)=COUNTIF(K2:K56,">=60")-SUM(K57:K61) ——求K2到K56区域60~69.5分的人数;假设把结果存放于K62单元格; (7)=COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数;假设把结果存放于K63单元格;
各函数使用方法大全 Excel函数使用方法 1、ABS函数 主要功能:求出相应数字的绝对值。 使用格式:ABS(number) 参数说明:number代表需要求绝对值的数值或引用的单元格。 应用举例:如果在B2单元格中输入公式:=ABS(A2),则在A2单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数(如100)。 特别提醒:如果number参数不是数值,而是一些字符(如A等),则B2中返回错误值“#VALUE!”。 2、AND函数 主要功能:返回逻辑值:如果所有参数值均为逻辑“真(TRUE)”,则返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。 使用格式:AND(logical1,logical2, ...) 参数说明:Logical1,Logical2,Logical3……:表示待测试的条件值或表达式,最多这30个。 应用举例:在C5单元格输入公式:=AND(A5>=60,B5>=60),确认。如果C5中返回TRUE,说明A5和B5中的数值均大于等于60,如果返回FALSE,说明A5和B5中的数值至少有一个小于60。 特别提醒:如果指定的逻辑条件参数中包含非逻辑值时,则函数返回错误值“#VALUE!”或“#NAME”。 3、AVERAGE函数 主要功能:求出所有参数的算术平均值。 使用格式:AVERAGE(number1,number2,……) 参数说明:number1,number2,……:需要求平均值的数值或引用单元格(区域),参数不超过30个。 应用举例:在B8单元格中输入公式:=AVERAGE(B7:D7,F7:H7,7,8),确认后,即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均值。 特别提醒:如果引用区域中包含“0”值单元格,则计算在内;如果引用区域中包含空白或字符单元格,则不计算在内。 4、COLUMN 函数 主要功能:显示所引用单元格的列标号值。 使用格式:COLUMN(reference) 参数说明:reference为引用的单元格。