计算机二级MS OFFICE Excel函数公式 常用函数： SUM、AVERAGE、SUMIF（条件求和函数）、SUMIFS(多条件求和函数)、INT(向下取整函数)、TRUNC（只取整函数）、ROUND(四舍五入函数)、VLOOKUP(垂直查询函数)、TODAY（）(当前日期函数)、AVERAGEIF(条件平均值函数)、AVERAGEIFS(多条件平均值函数)、COUNT/COUNTA(计数函数)、COUNTIF(条件计数函数)、COUNTIFS(多条件计数函数)、MAX(最大值函数)、MIN(最小值函数)、RANK.EQ(排位函数)、CONCATENATE（&）(文本合并函数)、MID（截取字符函数）、LEFT(左侧截取字符串函数)、RIGHT(右侧截取字符串函数) 其他重要函数（对实际应用有帮助的函数）： AND（所有参数计算结果都为T时，返回T，只要有一个计算结果为F，即返回F） OR（在参数组中任何一个参数逻辑值为T，即返回T，当所有参数逻辑值均为F，才返回F） TEXT (根据指定的数字格式将数字转换为文本) DATE（返回表示特定日期的连续序列号） DAYS360 (按照每月30天，一年360天的算法，返回两日期间相差的天数) MONTH（返回日期中的月份值，介于1到12之间的整数） WEEKDAY (返回某日期为星期几，其值为1到7之间的整数) CHOOSE (根据给定的索引值，从参数串中选择相应的值或操作) ROW （返回指定单元格引用的行号） COLUMN（返回指定单元格引用的列号） MOD（返回两数相除的余数） ISODD (如果参数为奇数返回T，否则返回F)
EXCEL 2010 常用函数应用 1. VLOOKUP 函数的使用方法 (这个一定要会) VLOOKUP 是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。它的基本语法为： 方法一：VLOOKUP （查找目标，查找范围，返回值的列数，精确 OR 模糊查找) 下面以一个实例来介绍一下这四个参数的使用 例 1：如下图所示，要求根据表二中的姓名，查找姓名所对应的年龄 公式：B13 =VLOOKUP(A13,$B$2:$D$8,3,0) 方法二： 返回值的列数 精确 OR 模糊查找
2.符合多个条件的数据求和——SUMIFS 可以添加更多条件 3.MID函数 （1）在C2单元格中输入公式“=MID(A2,4,1)&"班"”， 说明：MID函数是截取字符串函数，主要功能是从一个文本字符串的指定位置开始，截取指定数目的字符。本题中“=MID(A2,4,1)&"班"”是指从A2单元格的字符串中的第4位开始，截取1个字符，“&”为连接运算符，可以将两个文本字符串连接在一起，在本题中将截取出的字符和“班”字连接。 （2）=IF(MID(B3,3,2)="01","法律一班",IF(MID(B3,3,2)="02","法律二班 ",IF(MID(B3,3,2)="03","法律三班","法律四班")))” 4.IF函数见教材实例 C列“等级”的评定由IF函数来求得，C2=IF(B2>=90,"优秀",IF(B2>=80,"良好",IF(B2>=70,"中等",IF(B2>=60,"及格","补考")))) 5.名次排位函数——RANK 函数 rank 函数最常用的是求某一个数值在某一区域内的排名。 rank 函数语法形式：=RANK(D2,$D$2:$D$21,0) 6.根据日期判断是否周末（2013年1月20日星期日） =IF(WEEKDAY(A3,2)>5,"是","否")
注意：除了中文，其它都用英文格式。例如符号。 1.相对引用公式中的相对单元格引用（如 A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制或填充公式，引用会自动调整。默认情况下，新公式使用相对引用。例如，如果将单元格 B2 中的相对引用复制或填充到单元格 B3，将自动从 =A1 调整到 =A2。 绝对引用公式中的绝对单元格引用（如 $A$1）总是在特定位置引用单元格。如果公式所在单元格的位置改变，绝对引用将保持不变。如果多行或多列地复制或填充公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，因此您可能需要将它们转换为绝对引用。例如，如果将单元格 B2 中的绝对引用复制或填充到单元格 B3，则该绝对引用在两个单元格中一样，都是 =$A$1 混合引用混合引用具有绝对列和相对行或绝对行和相对列。绝对引用列采用 $A1、$B1 等形式。绝对引用行采用 A$1、B$1 等形式。如果公式所在单元格的位置改变，则相对引用将改变，而绝对引用将不变。如果多行或多列地复制或填充公式，相对引用将自动调整，而绝对引用将不作调整。例如，如果将一个混合引用从 A2 复制到 B3，它将从 =A$1 调整到 =B$1 。 注：更改引用方式的快捷键是：F4，选中公式中要改变引用的地址，按F4即可。2. 人民币(Chinese Yuan)代码CNY，是ISO分配给中国的币种表示符号。目前人民币(RenMinBi Yuan)简写为RMB￥，其简写用的是人民币汉语拼音开头字母组合，标准货币符号为CNY。 在国际贸易中是表示人民币元的唯一规范符号。现在已取代了 RMB￥的记法，统一用于外汇结算和国内结算。但用于国内结算时在不发生混淆的情况下RMB ￥仍然可以用。 3.记住函数：VLOOKUP纵向查找函数（查找引用）；SUMIFS多条件求和函数；rank,排位函数；.LOOKUP返回 向量或数组中的数值函数。int为取整函数；countifs：统计单元格个数函数；IFERROR，判断函数；if，判断函数；AVERAGE，平均函数； A.纵向查找函数（查找引用）： VLOOKUP(D3（第一列那个位置）,编号对 照!$A$3:$C$19（引用另个工作表）,2（与第一列的位置关系，也是要填空地,这里是E）,FALSE（为大致匹配，而ture为精确匹配）)： VLOOKUP(D3,编号对照!$A$3:$C$19(即表二）,2,FALSE)：
计算机二级excel中常考公式及讲解 一、常用函数 1.绝对值函数：=ABS(number) 注：当其引用“C1”时，左上角有个绿色角标。 2.最大值函数：=MAX(number1,number2,…) 最小值函数：=MIN(number1,number2,…) 注：无论是直接选择一个区域还是单个选择哪几个数字，都可以直接显示出最大值或者最小值。 3.四舍五入函数：=ROUND(number,Num_digits) 注：◆num_digits 表示保留的小数位数，按此位数对number 参数进行四舍五入。 number可是输入数字，也能 输入单元格（图中红色为输入分数得出的）
4.取整函数：=TRUNC(number,[ Num_digits]) 向下取整函数：=INT(number) 注： ◆其中TRUNC中还能选择日期，选择后默认把常规变为自定义（如表格中的红框框，其中43118为常规状态下的”=TRUNC(A2,1)”,而框框中黑色字体的“2018年1月18日”为输入公式后的自定义下的导出值） 其中”=TRUNC( number,[ Num_digits])”中的 [ Num_digits]默认为0，当只想取整数部分时，可入图片黄色区域，不输入“,0”。 ?在使用函数INT时，如果遇到负数，将会如图所示，同样的，INT也能直接引用数字。
二、求和函数 1.求和函数：=SUM(number1,number2,…) 2.条件求和函数：=SUMIF(range, criteria, [sum_range]) 多条件求和函数： =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 注：range（取值范围）criteria（条件） ◆使用条件求和函数SUMIF第三个参数的时，默认[sum_range]区域的左上角第一个就为前面一个取值范围的左上角，并且默认扩展其区域（如图上，我们在F1:G3中输入任何数字都会被加入到A7的数值中，即完整的应该为 =SUMIF(A1:E3,">3",C1:G3)）。 使用多条件求和函数SUMIFS时,维度必须相同。
二级MS Office高级应用-Excel公式应用 1、mid(text,start_num,num_chars) 功能：作用是从一个字符串中截取出指定数量的字符 参数说明： text：文本所在的单元格 start_num: 从第几个字符开始截取 num_chars: 截取字符的个数 举例：
2、sum(number1,number2,number3,….) 功能：计算单元格区域中所有数值的和 参数：number1数字所在单元格，number2数字2所在单元格，连续的单元格可以A2：A10这样表示，或A2:G2 举例：
3、average(number1,number2,number3,…) 功能：返回其参数的算术平均值 参数：number1数字所在单元格，number2数字2所在单元格，连续的单元格可以A2：A10这样表示，或A2:G2 举例：
4、if(logical_test,value_of_true,value_of_false) 功能：判断是否满足某个条件，如果满足返回一个值，如果不满足返回另一个值。 参数： Logical_test：逻辑表达式 Value_of_true：如果条件为真，显示这个值 Value_of_false：如果条件为假，显示这个值 举例：
5、int(number) 功能：取整函数，向下取整，即只取整数部分，不四舍五入。 参数：number可以是带小数的。 举例： 6、round(number,num_digits) 功能：按指定的位数对数值进行四舍五入 参数： Number：需要四舍五入的数值 Num_digits：保留的小数位数，如为0，则对数值进行取整（四舍五入），注意与int函数的区别。 举例：
2016年计算机二级Excel函数公式
第1套 (2)图书名称=VLOOKUP(D3,编号对照!$A$3:$C$19,2,FALSE) (5)所有订单的总销售额=SUM(订单明细表!H3:H636) (6)《MSOffice高级应用》图书在2012年的总销售额 降序→=SUMIF(订单明细表!E3:E262,"=《MS Office高级应用》",订单明细表!H3:H262) (7)隆华书店在2011年第3季度（7月1日~9月30日）的总销售额 降序→=SUMIF(订单明细表!C350:C461,"=隆华书店",订单明细表!H350:H461) 第2套 (4)班级=MID(A2,4,1)&"班" 第3套 (3)销售额排名=RANK(D2,$D$2:$D$21,0)RANK函数排序区域绝对引用。 RANK 函数语法具有下列参数： Number 必需。要找到其排位的数字。 Ref 必需。数字列表的数组，对数字列表的引用。 Ref 中的非数字值会被忽略。Order 可选。一个指定数字排位方式的数字。
如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。 如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。 第4套 合并计算 在【数据】选项卡的【数据工具】组中单击"合并计算"按钮，弹出"合并计算"对话框，设置"函数"为"求和"，在"引用位置"文本框中键入第一个区域"第五次普查数据!$A$1:$C$34"，单击"添加"按钮，键入第二个区域"第六次普查数据!$A$1:$C$34"，单击"添加"按钮，在"标签位置"下勾选"首行"复选框和"最左列"复选框，然后单击"确定"按钮。 第5套 您需要四条信息才能构建VLOOKUP语法： 1.要查找的值，也被称为查阅值。 2.查阅值所在的区域。请记住，查阅值应该始终位于所在区域的第一列，这样 VLOOKUP 才能正常工作。例如，如果查阅值位于单元格C2 内，那么您的区域应该以 C 开头。 3.区域中包含返回值的列号。例如，如果指定 B2：D11 作为区域，那么应该将 B 算作第一列，C 作为第二列，以此类推。 4.（可选）如果需要返回值的近似匹配，可以指定 TRUE；如果需要返回值的精确匹配，则指定 FALSE。如果没有指定任何内容，默认值将始终为 TRUE 或近似匹配。 WEEKDAY(serial_number,[return_type]) WEEKDAY 函数语法具有下列参数：
名师总结精品知识点 1、求和函数：SUM =SUM ( A1:A5 , 5 ) 等于60 2、条件求和函数：SUMIF = SUMIF ( A2 : A6 , “ 01 ” , F2 : F6 ) 3、求平均值函数：AVERAGE =AVERAGE(A1:A5, 5) 等于10 4、最大（小）值函数：MAX（MIN）= MAX(A1:A5) 等于27 5、统计数值型数据个数函数：COUNT = COUNT (A1:A5) 等于3 6、条件计数函数：COUNT IF =COUNTIF (A3:A6, "??p* ") 等于3 7、统计非空白单元格个数函数：COUNTA 8、查找函数：VLOOKUP =VLOOKUP (1,A2:C10,3,TRUE) 9、排位函数：RANK ( 排序数, 范围, 排位方式)=RANK( A3 , A2:A6 , 1 ) 10、当前日期函数：TODAY（）功能：返回计算机系统的当前日期。 11、求出给定“日期”的年份。：YEAR=YEAR("2006/04/24") 等于2006 12、当前日期和时间函数：NOW（）功能：返回计算机系统的当前日期和当前时间。 13、IF（条件X，值1，值2）=IF（D3>=80 ，“优良”,IF(D3>=60,“中”，“不及格”) 14、逻辑“与”函数：AND 条件：80 <= C2 < 90, 则表示成AND（C2 >= 80 ，C2 < 90） 15、逻辑“或”函数：OR 成绩大于等于80 或者成绩小于90,OR（C2 >= 80 ，C2 < 90 ） 16、左边截取函数：LEFT（X，n）=LEFT (“陈列室”, 1 ) 结果为“陈”。 17、右边截取函数：RIGHT（X ，n） 18、求余数函数: MOD(X1,X2)，返回X1/X2的余数,结果的正负号与除数(X2)相同 19、四舍五入函数: ROUND( X , n ) 20、中间截取函数：MID（X ，m ，n）= MID ( "Fluid Flow" , 1 , 5 ) 等于"Fluid" 21、求字串长度函数：LEN（X）= LEN ( “abcdefg”) 结果为7 。 其他考试公式总结： 1、=SUMPRODUCT(1*(E3:E30=”《认命》”),H3:H30) =SUMPRODUCT(1*(C350:C461=”隆化书店”),H350:H461),季度需要先排序 =SUMPRODUCT(1*(D3:D17="销售"),M3:M17) 2、RANK(D2,$D$2:$D$21,0)，0是降序。="第"&RANK(F2,$F$2:$F$45)&"名" ，第几名。 3、LOOKUP(MID(A2,3,2),{"01","02","03"},{"1班","2班","3班"})"，从第三个字符起，取两个。 IF(MID(A3,4,2)="01","1班",IF(MID(A3,4,2)="02","2班","3班")) 4、VLOOKUP(A3,学号对照!$A$3:$B$20,2,FALSE) 5、=MID(F3,7,4)&"年"&MID(F3,11,2)&"月"&MID(F3,13,2)&"日" 6、INT((TODAY()-I3)/365) 7、"=AVERAGEIF(员工档案!H3:H37,"本科"，员工档案!K3:K37)" 8、=ROUND(IF(K3<=1500,K3*3/100,IF(K3<=4500,K3*10/100-105,IF(K3<=9000,K3*20/100- 555,IF(K3<=35000,K3*25%-1005,IF(K3<=5500,K3*30%-2755,IF(K3<=80000,K3*35%-55 05,IF(K3>80000,K3*45%-13505))))))),2) 。应交个人所得税。 “=IF(F2>=102,"优秀",良好",IF(F2>=72,"及格",IF(F2>72,"及格","不及格"))))” 9、SUBTOTAL(109,B4:B11)”. 109=sum, 对AL31-AL34求和. 10、=IF(MOD(MID(C2,17,1),2)=1,"男","女")”输入男女生性别。 11、TEXT(MID(C2,7,8),"0-00-00")”出生年月日。 12、=DATEDIF(--TEXT(MID(C2,7,8),"0-00-00"),TODAY(),"y")，年龄计算。
小李今年毕业：(图书名称)=V L O O K U P(D3,编号对照!$A$2:$C$19,2,F A L S E) (订单明细工作表)=VLOOKUP(D3,编号对照!$A$2:$C$19,3,FALSE) SUMIFS=(求和区域,条件区域1,条件1,?[条件区域2,条件2],?...) 小蒋是一位中学教师：(班级)=MID(A2,4,1)&"班" 某公司拟对其产品：(销售额排名)=RANK(D2,$D$2:$D$21,0) 财务部助理小王:(是否加班)=IF(WEEKDAY(A3,2)>5,"是","否") (地区)=LEFT(C3,3) (费用类别)?=VLOOKUP(E3,费用类别!$A$2:$B$12,2,FALSE) （二季度北京差旅费）=SUMIFS(费用报销管理!G3:G401,费用报销管 理!A3:A401,">=2013-04-01",费用报销管理!A3:A401,"<=2013-06-30",费用报销管 理!D3:D401,"北京市") （钱顺卓报销火车票）=SUMIFS(费用报销管理!G3:G401,费用报销管理!B3:B401,"钱顺卓",费用报销管理!F3:F401,"火车票") （飞机票所占比例）=SUMIF(费用报销管理!F3:F401,"飞机票",费用报销管 理!G3:G401)/SUM(费用报销管理!G3:G401) （周末补助总额）=SUMIFS(费用报销管理!G3:G401,费用报销管理!H3:H401,"是",费用报 销管理!F3:F401,"通讯补助") 文涵是大地公司的:(销售额)=VLOOKUP（D4，商品均价，2，0）*E4 小李是东方公司的会计:(应交个人所得 税)=?IF(K3<=1500,K3*3/100,IF(K3<=4500,K3*10/100-105,IF(K3<=9000,K3*20/100-555 ,IF(K3<=35000,K3*25%-1005,IF(K3<=55000,K3*30%-2755,IF(K3<=80000,K3*35%-5505,I F(K3>80000,K3*45%-13505))))))) 小李是北京某政法学院:(年级排名)=RANK(M3,M$3:M$102,?0) (班级)=IF(MID(B3,3,2)="01","法律一班",IF(MID(B3,3,2)="02","法律二班 ",IF(MID(B3,3,2)="03 销售部助理小王:(图书编号)=VLOOKUP(D3,图书编目表!$A$2:$B$9,2,FALSE) 期末考试结束了:(性别)=IF(MOD(MID(C2,17,1),2)=1,?"男","女") (出生日期)=MID(C2,7,4)&"年"&MID(C2,11,2)&"月"&MID(C2,13,2)&"日" (年龄)=INT((TODAY()-E2)/365) “=IF(F2>=90,"优秀",IF(F2>=75,"良好",IF(F2>=60,"及格",IF(F2>60,"及格","不及格")))”，“=VLOOKUP(A3,初三学生档案!$A$2:$B$56,2,0) “=VLOOKUP(A3,数学!$A$2:$F$45,6,0)”， “=VLOOKUP(A3,语文!$A$2:$F$45,6,0)”， 小李是某政法学院：4.（排名）=RANK(L3,$K$3:$K$6,0)（总平均）=AVERAGE(C3:C27) 6.=RANK(K3,$K$3:$K$6,0)（总平均）=AVERAGE(B3:B6) 销售部门主管大华：2.（一季度销售情况表）=VLOOKUP(B2,产品基本信息表!$B$1： $C$21,2,0)3.（C2填）=SUMIF(一季度销售情况表!$B$2：$B$44,B2,一季度销售情况表!$D$2：$D44)
精品文档 1、求和函数：SUM =SUM ( A1:A5 , 5 ) 等于60 2、条件求和函数：SUMIF = SUMIF ( A2 : A6 , “ 01 ” , F2 : F6 ) 3、求平均值函数：AVERAGE =AVERAGE(A1:A5, 5) 等于10 4、最大（小）值函数：MAX（MIN）= MAX(A1:A5) 等于27 5、统计数值型数据个数函数：COUNT = COUNT (A1:A5) 等于3 6、条件计数函数：COUNT IF =COUNTIF (A3:A6, "??p* ") 等于3 7、统计非空白单元格个数函数：COUNTA 8、查找函数：VLOOKUP =VLOOKUP (1,A2:C10,3,TRUE) 9、排位函数：RANK ( 排序数, 范围, 排位方式)=RANK( A3 , A2:A6 , 1 ) 10、当前日期函数：TODAY（）功能：返回计算机系统的当前日期。 11、求出给定“日期”的年份。：YEAR=YEAR("2006/04/24") 等于2006 12、当前日期和时间函数：NOW（）功能：返回计算机系统的当前日期和当前时间。 13、IF（条件X，值1，值2）=IF（D3>=80 ，“优良”,IF(D3>=60,“中”，“不及格”) 14、逻辑“与”函数：AND 条件：80 <= C2 < 90, 则表示成AND（C2 >= 80 ，C2 < 90） 15、逻辑“或”函数：OR 成绩大于等于80 或者成绩小于90,OR（C2 >= 80 ，C2 < 90 ） 16、左边截取函数：LEFT（X，n）=LEFT (“陈列室”, 1 ) 结果为“陈”。 17、右边截取函数：RIGHT（X ，n） 18、求余数函数: MOD(X1,X2)，返回X1/X2的余数,结果的正负号与除数(X2)相同 19、四舍五入函数: ROUND( X , n ) 20、中间截取函数：MID（X ，m ，n）= MID ( "Fluid Flow" , 1 , 5 ) 等于"Fluid" 21、求字串长度函数：LEN（X）= LEN ( “abcdefg”) 结果为7 。 其他考试公式总结： 1、=SUMPRODUCT(1*(E3:E30=”《认命》”),H3:H30) =SUMPRODUCT(1*(C350:C461=”隆化书店”),H350:H461),季度需要先排序 =SUMPRODUCT(1*(D3:D17="销售"),M3:M17) 2、RANK(D2,$D$2:$D$21,0)，0是降序。="第"&RANK(F2,$F$2:$F$45)&"名" ，第几名。 3、LOOKUP(MID(A2,3,2),{"01","02","03"},{"1班","2班","3班"})"，从第三个字符起，取两个。 IF(MID(A3,4,2)="01","1班",IF(MID(A3,4,2)="02","2班","3班")) 4、VLOOKUP(A3,学号对照!$A$3:$B$20,2,FALSE) 5、=MID(F3,7,4)&"年"&MID(F3,11,2)&"月"&MID(F3,13,2)&"日" 6、INT((TODAY()-I3)/365) 7、"=AVERAGEIF(员工档案!H3:H37,"本科"，员工档案!K3:K37)" 8、=ROUND(IF(K3<=1500,K3*3/100,IF(K3<=4500,K3*10/100-105,IF(K3<=9000,K3*20/100- 555,IF(K3<=35000,K3*25%-1005,IF(K3<=5500,K3*30%-2755,IF(K3<=80000,K3*35%-55 05,IF(K3>80000,K3*45%-13505))))))),2) 。应交个人所得税。 “=IF(F2>=102,"优秀",良好",IF(F2>=72,"及格",IF(F2>72,"及格","不及格"))))” 9、SUBTOTAL(109,B4:B11)”. 109=sum, 对AL31-AL34求和. 10、=IF(MOD(MID(C2,17,1),2)=1,"男","女")”输入男女生性别。 11、TEXT(MID(C2,7,8),"0-00-00")”出生年月日。 12、=DATEDIF(--TEXT(MID(C2,7,8),"0-00-00"),TODAY(),"y")，年龄计算。 .
全国计算机等级考试-二级MS-Office高级应用Excel函数总结
VLOOKUP函数 语法规则 VLOOKUP(lookup_value,table_array,col_ 参数说明 Lookup_value为需要在数据表第一列中进行查找的数值。Lookup_value 可以为数值、引用或文本字符串。 Table_array为需要在其中查找数据的数据表。使用对区域或区域名称的引用。 col_index_num为table_array 中查找数据的数据列序号。col_index_num 为 1 时，返回table_array 第一列的数值， col_index_num 为2 时，返回table_array 第二列的数值，以此类推。如果 col_index_num 小于1，函数VLOOKUP 返回错误值#VALUE!；如果col_index_num 大
于table_array 的列数，函数VLOOKUP 返回错误值#REF!。 Range_lookup为一逻辑值，指明函数VLOOKUP 查找时是精确匹配，还是近似匹配。如果为false或0 ，则返回精确匹配，如果找不到，则返回错误值#N/A。如果range_lookup 为TRUE或1，函数VLOOKUP 将查找近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value 的最大数值。如果range_lookup 省略，则默认为近似匹配。 例如： 【第1套】 =VLOOKUP(D3,编号对照!$A$3:$C$19,2,FALSE) 【第5套】 =VLOOKUP(E3,费用类别!$A$3:$B$12,2,FALSE) 【第9套】 =VLOOKUP(D3,图书编目表!$A$2:$B$9,2,FALSE) 【第10套】 =VLOOKUP(A2,初三学生档案!$A$2:$B$56,2,0) SUMPRODUCT函数 说明: 数组参数必须具有相同的维数，否则，函数SUMPRODUCT 将返回错误值#VALUE!。函数SUMPRODUCT 将非数值型的数组元素作为0 处理。
(新整理)计算机二级excel中常考公式及讲 解 EXCEL2010常用函数应用 1.单条件求和——SUMIF 函数 如要统计C列中的数据, 要求统计条件是B列中数据为”条件一”。并将结果放在C6单元格中，我们只要在C6单元格中输入公式“=SUMIF（B2：B5，” 条件一”，C2：C5）”即完成这一统计。 2.符合多个条件的数据求和——SUMIFS 函数 统计5班中语文名次、数学名次均位于前20 名同学的总分之和。在相应单元格内输入公式 “=SUMIFS(M2:M80,A2:A80,”5”,D2:D80,”如图所示。 ⑴功能按指定的位数对数值进行舍入。
⑵格式ROUNDDOWN(数值或数值单元格，指定的位数) ⑶示例12.351325.52512.35325.522.舍去小数位保留整数——舍去十分位及以后的小数位保留整数123251032003.整数保留到十位——整数部分舍去个位上大于0的数字=ROUNDDOWN(A1,-1)=ROUNDDOWN(A2,-1)=ROUNDDOWN(A2, -2)1.保留2位小数——舍去千分位以后的小数位保留到百分位=ROUNDDOWN(A1,2)=ROUNDDOWN(A2,2)=ROUNDDOWN(A1,0) =ROUNDDOWN(A2,0) 7.1LOOkUP函数使用方法LOOKUP(MID(A2,3,2),{“01”,”02 WECHAT_EMPTY_TITLEhttps://https://www.doczj.com/doc/0017040941.html,/ba5fe3dfdd5ca2edc 83e9bd55-2.html “,”03”},{“1班”,”2班”,”3班”})意思为如果A2单元格从第三个字符起取两个字符与数组中{“01”,”02”,”03”}中第某项相同,则返回另一个数组{“1班”,”2班”,”3班”}中的第某项内容假设A2单元格的内容为220345 则MID(A2,3,2) 返回“03”而”03”是{“01”,”02”,”03”}中的第三项,所以公式返回{“1班”,”2班”,”3班”}中的第三项即”3班”这个公式可以简写成LOOKUP(MID(A2,3,2),{1,2,3},{“1班”,”2班”,”3班”})或=MID(A2,3,2)&”班” 1. 以一份学生成绩表为例，要查询的是得分76的学生的姓名
小李今年毕业：（图书名称）=VLOOKUP（D3, 编号对照 !$A$2:$C$19,2,FALSE）（订单明细工作表）=VL00KUP（D3编号对照！$A$2:$C$19,3,FALSE） SUMIFS二求和区域，条件区域1,条件1,?[条件区域2,条件2],?…） 小蒋是一位中学教师：（班级）=MID（A2,4,1） &"班" 某公司拟对其产品：（销售额排名）=RANK（D2,$D$2:$D$21,0） 财务部助理小王 :（是否加班）=IF（WEEKDAY（A3,2）>5," 是"," 否"） （地区）=LEFT（C3,3） （费用类别）？=VLOOKUP（E3费用类别!$A$2:$B$12,2,FALSE） （二季度北京差旅费）二SUMIFS费用报销管理！G3:G401,费用报销管 理!A3:A401,">=2013-04-01", 费用报销管理!A3:A401,"<=2013-06-30", 费用报销管 理!D3:D401,"北京市"） （钱顺卓报销火车票）二SUMIFS费用报销管理!G3:G401,费用报销管理！B3:B401," 钱顺卓", 费用报销管理 !F3:F401," 火车票"） （飞机票所占比例）二SUMIF债用报销管理!F3:F401,"飞机票",费用报销管 理!G3:G401）/SUM（费用报销管理!G3:G401） （周末补助总额）二SUMIFS费用报销管理!G3:G401,费用报销管理！H3:H401,"是", 费用报 销管理 !F3:F401," 通讯补助 "） 文涵是大地公司的:（销售额）=VLOOKU（D4,商品均价，2，0）*E4 小李是东方公司的会计 :（应交个人所得 税)=?IF(K3<=1500,K3*3/100,IF(K3v=4500,K3*10/100-105,IF(K3v=9000,K3*20/10 0-555,IF(K3<=35000,K3*25%-1005,IF(K3<=55000,K3*30%-2755,IF(K3<=80000,K3* 35%-5505,IF(K3>80000,K3*45%-13505)))))))
（小李，计算机图书销售） 产品季度销售 财务部助理小王，差旅报销 小李东方会计，2014年3月
小林北京师范大学，2012年 小李公司出纳，手工记账，九月份
20套： 步骤3：将【图书名称】拖拽至【行标签】，将【所属区域】拖拽至【列标签】，将【销售额小计】拖拽至【数值】。展开列标签，取消勾选“北区”外其他3个区， 步骤4：切换至【数据透视表工具】-【设计】选项卡，在【布局】选项组中，单击【总计】按钮，在弹出的下拉列表中单击【仅对列启用】；单击【报表布局】按钮，在弹出的下拉列表中选择【以大纲形式显示 16套：销售部助理小王，2012年和2013年图书产品 3.【解题步骤】 步骤1：在“销售订单”工作表中选中“书店名称”单元格，右键单击在快捷菜单中选择“插入”级联菜单中的“在左侧插入表列”命令，插入一列单元格，然后C3单元格中输入“=MONTH($B$3:$B$678)” “=SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,A4,销售订单!$C$3:$C$678,1)” SUMIFS(销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,A4,销售订单!$C$3:$C$678,2)” 步骤4：在A12单元格中输入“每月图书总销量”字样，然后选中B12单元输入“=SUBTOTAL(109,B4:B11)” 5.【解题步骤】 小赵参加工作不久，个人开支2013年底 销售部小王，2012和2013公司产品
步骤1：根据题意要求切换至“销售订单”工作表中，单击【开始】选项卡下【表格】组中的“数据透视表”下拉按钮，在弹出的下拉列表中选择“数据透视表”，在弹出的“创建数据透视表”对话框中将“表/区域”设置为表1，选择“新工作表”，单击“确定”按钮。 步骤2：单击【选项】选项卡下【操作】组中的“移动数据透视表”按钮，在打开的“移动数据透视表”对话框中选中“现有工作表”，将“位置”设置为“Sheet1!A1”，单击“确定”按钮。 “=SUM(B3:BK3)”，步骤3：使用同样方法在C8，D8，E8单元格中分别输入以下公式“=SUM(BL3:DY3)”、“=SUM(DZ3:GL3)”，=SUM(QM3:SL3)。 17套：期末考试，初三（14） “=IF(MOD(MID(C2,17,1),2)=1,"男","女")” “=--TEXT(MID(C2,7,8),"0-00-00")”，然后选择E2：E56单元格，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”选项。切换至“数字”选项卡，将“分类”设置为“日期”， “=DATEDIF(--TEXT(MID(C2,7,8),"0-00-00"),TODAY(),"y")” “=SUM(C2*30%)+(D2*30%)+(E2*40%)” “="第"&RANK(F2,$F$2:$F$45)&"名"” “=IF(F2>=102,"优秀",IF(F2>=84,"良好",IF(F2>=72,"及格",IF(F2>72,"及格","不及格"))))”
计算机等级考试二级常用excel函数大全 1、DAVERAGE 用途：返回数据库或数据清单中满足指定条件的列中数值的平均值。 语法：DAVERAGE(database，field，criteria) 参数：Database 构成列表或数据库的单元格区域;Field指定函数所使用的数据(所用项目);Criteria 为一组包含给定条件的单元格区域。 2、DCOUNT 用途：返回数据库或数据清单的指定字段中，满足给定条件并且包含数字的单元格数目。 语法：DCOUNT(database，field，criteria) 参数：Database 构成列表或数据库的单元格区域;Field 指定函数所使用的数据列;Criteria为一组包含给定条件的单元格区域。 3、DMAX（DMIN ） 用途：返回数据清单或数据库的指定列中，满足给定条件单元格中的最大数值。 语法：DMAX(database，field，criteria) 参数：Database 构成列表或数据库的单元格区域;Field 指定函数所使用的数据列;Criteria为一组包含给定条件的单元格区域。 4、DPRODUCT 用途：返回数据清单或数据库的指定列中，满足给定条件单元格中数值乘积。 语法：DPRODUCT(database，field，criteria) 参数：Database 构成列表或数据库的单元格区域;Field 指定函数所使用的数据列;Criteria为一组包含给定条件的单元格区域。 5、DSUM 用途：返回数据清单或数据库的指定列中，满足给定条件单元格中的数字之和。 语法：DSUM(database，field，criteria) 参数：Database 构成列表或数据库的单元格区域;Field 指定函数所使用的数据列;Criteria为一组包含给定条件的单元格区域。 6、YEAR
计算机二级excel中常考公式及讲解EXCEL2010常用函数应用 1.单条件求和——SUMIF 函数 如要统计C列中的数据, 要求统计条件是B列中数据为"条件一"。并将结果放在C6单元格中，我们只要在C6单元格中输入公式“=SUMIF （B2：B5，" 条件一"，C2：C5）”即完成这一统计。 2.符合多个条件的数据求和——SUMIFS 函数 统计5班中语文名次、数学名次均位于前20 名同学的总分之和。在相应单元格内输入公式 “=SUMIFS(M2:M80,A2:A80,"5",D2:D80,"<=20",F2:F80,"<=20")”，按下回车键，就一切OK。 如图所示。
⑴功能按指定的位数对数值进行舍入。 ⑵格式ROUNDDOWN(数值或数值单元格，指定的位数) ⑶示例12.351325.52512.35325.522.舍去小数位保留整数——舍去十分位及以后的小数位保留整数123251032003.整数保留到十位——整数部分舍去个位上大于0的数字 =ROUNDDOWN(A1,-1)=ROUNDDOWN(A2,-1)=ROUNDDOWN(A2,-2)1.保留2位小数——舍去千分位以后的小数位保留到百分位 =ROUNDDOWN(A1,2)=ROUNDDOWN(A2,2)=ROUNDDOWN(A1,0) =ROUNDDOWN(A2,0) 7.1LOOkUP函数使用方法
LOOKUP(MID(A2,3,2),{"01","02 WECHAT_EMPTY_TITLEhttps://https://www.doczj.com/doc/0017040941.html,/ba5fe3dfdd5ca2edc8 3e9bd55-2.html ","03"},{"1班","2班","3班"})意思为如果A2单元格从第三个字符起取两个字符与数组中{"01","02","03"}中第某项相同,则返回另一个数组{"1班","2班","3班"}中的第某项内容假设A2单元格的内容为220345 则MID(A2,3,2) 返回"03"而"03"是{"01","02","03"}中的第三项,所以公式返回{"1班","2班","3班"}中的第三项即"3班"这个公式可以简写成LOOKUP(MID(A2,3,2),{1,2,3},{"1班","2班","3班"})或 =MID(A2,3,2)&"班" 1. 以一份学生成绩表为例，要查询的是得分76的学生的姓名
Excel常考公式函数 公式书写注意事项 1.单元格格式一定不能是文本 2. 格式必须正确（等号开头，函数名正确，参数齐全，标点是英文标点） 五大常用计算函数 sum（）求和、average（）求平均、count（）求个数，max（）求最大，min（）求最小需要注意的是：count（）只能求数值的个数（不求文本），counta（）求非空单元格的个数 IF 条件判断函数 if（逻辑判断条件，条件成立运行的语句，条件不成立运行的语句）简单来说就是如果…那么…否则 需要注意的是：if 函数多层嵌套，if 函数与其他函数的联合使用。 RANK 排名函数 rank（排名对象，排名的数据区域，次序） 次序可以省略不写，默认为降序 需要注意的是：排名的数据区域需要绝对引用，按 F4 键可以快速绝对引用，如果不能请按 Fn+F4.
countif（）与 countifs（）单/多条件求个数函数 countif（条件区域，条件），countifs（条件区域 1，条件 1，条件区域 2，条件 2，……）需要注意的是：条件可以手动输入，也可以鼠标选择。 sumif（）与 sumifs（）单/多条件求和函数 sumif（条件区域，条件，计算求和区域） sumifs（计算求和区域，条件区域 1，条件 1，条件区域 2，条件 2，……） 需要注意的是：1.为了避免出现错误，尽量都用 sumifs 函数。 2.条件书写时要注意格式，譬如没有 6 月 31 日 3.条件区域大小和计算求和区域大小（行数）要一致 Sumproduct 乘积求和函数 Sumproduct ((条件 1）*（条件 2），计算求和区域）与 sumifs 用法相似，都可以实现 多条件求和，但一般情况下建议大家使用 sumifs 函数 需要注意的是：写条件时一定不要忘记书写括号。 vlookup（）跨表纵向查询填充函数 vlookup（查询依据，查询的数据区域，结果所在的列数，精确还是近似） 需要注意的是：1.查询的数据区域如果没有定义名称一定要进行绝对引用
二级MS Office高级应用-Excel公式应用1、mid(text,start_num,num_chars) 功能：作用是从一个字符串中截取出指定数量的字符 参数说明： text：文本所在的单元格 start_num: 从第几个字符开始截取 num_chars: 截取字符的个数 举例： 2、sum(number1,number2,number3,….) 功能：计算单元格区域中所有数值的和 参数：number1数字所在单元格，number2数字2所在单元格，连续的单元格可以A2：A10这样表示，或A2:G2 举例： 3、average(number1,number2,number3,…) 功能：返回其参数的算术平均值 参数：number1数字所在单元格，number2数字2所在单元格，连续的单元格可以A2：A10这样表示，或A2:G2 举例：
4、if(logical_test,value_of_true,value_of_false) 功能：判断是否满足某个条件，如果满足返回一个值，如果不满足返回另一个值。 参数： Logical_test：逻辑表达式 Value_of_true：如果条件为真，显示这个值 Value_of_false：如果条件为假，显示这个值 举例： 5、int(number) 功能：取整函数，向下取整，即只取整数部分，不四舍五入。 参数：number可以是带小数的。 举例： 6、round(number,num_digits) 功能：按指定的位数对数值进行四舍五入 参数： Number：需要四舍五入的数值 Num_digits：保留的小数位数，如为0，则对数值进行取整（四舍五入），注意与int函数的区别。 举例： 7、roundup(number,num_digits) 功能：向上舍入数字 参数：