计算机二级MSOfficeEcel函数公式大集合Word格式文档下载.docx

1、=2011-7-1=2011-9-30（8）=SUMIFS（订单明细表!=2011-1-1=2011-12-31）/12注意：用SUNPRODUCT函数之前先排序，尤其是涉及时间的时候。第2套（4）班级 =LOOKUP（MID（A2,3,2）,”01”,”02”,”03”,”1班”,”2班”,”3班”）第3套（1）一季度销售额（元）、二季度销售额（元） =产品基本信息表!C2*C2（2）一二季度销售总量、一二季度销售总额=一季度销售情况表!C2+二季度销售情况表!C2=一季度销售情况表!D2+D2（3）销售额排名 =RANK（D2,$D$2:$D$21,0）RANK函数排序区域绝对引用。第4套

2、合并计算双击工作表sheet3的表名，在编辑状态下输入比较数据。在该工作表的A1中输入地区，在【数据】选项卡的【数据工具】组中单击合并计算按钮，弹出对话框，设置函数为求和，在引用位置文本框中键入第一个区域第五次普查数据!$A$1:$C$34，单击添加按钮，键入第二个区域第六次普查数据!按钮，在标签位置下勾选首行复选框和最左列复选框，然后单击确定按钮。数据透视表筛选单击行标签右侧的标签筛选按钮，在弹出的下拉列表中选择值筛选，打开级联菜单，选择大于，弹出值筛选（地区）对话框，在第一个文本框中选择求和项：2010年人口数（万人），第二个文本框选择，在第三个文本框中输入5000第5套在日期后添加星期在

3、费用报销管理工作表中，选中日期数据列，单击鼠标右键，在弹出的快捷菜单中选择设置单元格格式命令，弹出对话框。切换至数字选项卡，在分类列表框中选择自定义命令，在右侧的示例组中类型列表框中输入yyyy年m月d日aaaa设置完毕后单击按钮即可。（2）是否加班 =IF（WEEKDAY（A3,2）5,”是”,”否”）（3）地区 =LEFT（C3,3）（5）2013年第二季度发生在北京市的差旅费用金额总计为=SUMPRODUCT（1*（费用报销管理!D74:D340=”北京市”）,费用报销管理!G74:G340）=SUMIFS（费用报销管理!G3:G401,费用报销管理!D3:D401,费用报销管理!D11

4、,费用报销管理!A3:A401,=2013-4-1,费用报销管理!=2013-6-30（6）2013年钱顺卓报销的火车票总计金额为B401=”钱顺卓”）,1*（费用报销管理!F3:F401=”火车票”），费用报销管理!G401）F401, 费用报销管理!F10,费用报销管理!B401,费用报销管理!B378）（7）2013年差旅费用金额中，飞机票占所有报销费用的比例为（保留2位小数）F401=”飞机票”）,费用报销管理!G401）/SUM（费用报销管理!F401,费用报销管理!F3）/SUM（费用报销管理!（8）2013年发生在周末（星期六和星期日）中的通讯补助总金额为=SUMPRODUCT（

5、费用报销管理!H401=”是”）*（费用报销管理!F401=”通讯补助”）,费用报销管理！H401,费用报销管理!H3,费用报销管理!F391）第6套定义名称在“平均单价”工作表中选中B3：C7区域，单击鼠标右键，在弹出的下拉列表中选择定义名称命令，打开新建名称名称中输入商品均价后单击“确定”按钮即可。销售额: =VLOOKUP（D4,商品均价,2,0）*E4第7套（5）应交个人所得税=ROUND（IF（K3=1500,K3*,IF（K3=4500,K3*,IF（K3=9000,K3*,IF（K3=35000,K3*,IF（K3=55000,K3*,IF（K380000,K3*）,2）（8）管

6、理部门应付工资合计 =SUMPRODUCT（1*（D3:D17=”行政”）,I3:I17）管理部门实发工资 =SUMPRODUCT（1*（D3:D17=”行政”）,M3:M17）第8套平均成绩和总分 =SUM（D3:L3）=AVERAGE（D3:L3）年级排名 =RANK（M3,M$3:M$102,0）学号的第三位为专业代码、第四位代表班级序号 =”法律”&TEXT（MID（B3,3,2）,”DBNum1”）&”班”=LOOKUP（MID（B3,3,2）,01,020304,法律一班法律二班法律三班法律四班）插入图表到指定单元格按住AIT键，带鼠标指针出现斜向上箭头是开始拖动第9套插入一列“插

7、入”在C3中输入=IF（YEAR（销售订单!$B3）=2013,MONTH（销售订单!$B3）,0）Office商务办公好帮手1月（销售）=SUMIFS（销售订单!$H$3:$H$678,销售订单!$E$3:$E$678,2013年图书销售分析$A4,销售订单!$C$3:$C$678,1）Office商务办公好帮手2月（销售）$C$678,2）Office商务办公好帮手3月（销售）$C$678,3）每月图书总销量: =SUBTOTAL（109,B4:B11）添加汇总行第10套分列选中B列单元格，单击鼠标右键，在弹出的快捷菜单中选择“插入”选项。然后选中A1单元格，将光标置于“学号”和“名字”之

8、间，按3次空格键，然后选中A列单元格，单击【数据工具】组中的“分列”按钮，在弹出的对话框中选择“固定宽度”单选按钮，单击“下一步”按钮，然后建立分列线。单击“下一步”按钮，保持默认设置，单击“完成”按钮。（3）性别 =IF（MOD（MID（C2,17,1）,2）=1,”男”,”女”）出生日期 =-TEXT（MID（C2,7,8）,”0-00-00”）=CONCATENATE（MID（C8,7,4）,MID（C8,11,2）,月,MID（C8,13,2）,年龄: =DATEDIF（-TEXT（MID（C2,7,8）,”0-00-00”）,TODAY（）,”y”）=INT（TODAY（）-E2）/

9、365）（4）语文姓名=VLOOKUP（A2,初三学生档案!$B$56,2,FALSE），其余方法一样按照三个比例计算学期成绩 =SUM（C2*30%）+（D2*30%）+（E2*40%）按成绩由高到低的顺序排名，并按“第n名”的形式填入“班级名次”列中=第&RANK（F2,$F$2:$F$45）&名“期末总评”数学，语文：=IF（F2=102,优秀,IF（F2=84,良好=72,及格72,不及格）英语及其他:=IF（F3=90,IF（F3=75,=60,）姓名: =VLOOKUP（A3,初三学生档案!$A$2:$B$56,2,FALSE）语文成绩: =VLOOKUP（A3,语文!A2:F45

10、,6,FALSE）第11套（3）职称: =VLOOKUP（E12,教师基本信息!$D$3:$E$22,2,FALSE）学时数: =SUMIF（授课信息表!$D$72,E3,授课信息表!$F$3:$F$72）第12套（3） =IF（H2=0,”平”,IF（H20,”借”,”贷”）第13套（3）销量: =VLOOKUP（A4,销量信息,3,FALSE）第14套条件格式选中F2：G14，单击【开始】选项卡下【样式】组中的条件格式按钮，选择突出显示单元格规则中的其他规则新建格式规则对话框，在编辑规则说明选项下设置单元格值小于80。然后单击格式填充选项卡下选择红色分类汇总选中C15，单击【数据】选项卡下

11、【分级显示】组中的分类汇总对话框，单击分类字段组中的下拉按钮选择班级汇总方式平均值选定汇总项组中勾选高等代数、数学分析大学英语大学物理VB程序设计C语言程序设计Matlab复选框，并勾选每组数据分页复选框。其他分类汇总方法一致第15套（4）班级: =IF（MID（A3,4,2）=”01”,”1班”,IF（MID（A3,4,2）=”02”,”2班”,”3班”）（5）姓名: =VLOOKUP（A3,学号对照!$B$20,2,FALSE）第16套（2）出生日期: =MID（F3,7,4）&”年”&MID（F3,11,2）&”月”&MID（F3,13,2）&”日”（3）工龄: =INT（TODAY（）

12、-I3）/365）（4）工龄工资: =AVERAGEIF（员工档案!H37,”本科”,员工档案!K3:K37）（5）所有人的基础工资: =SUM（员工档案!M3:M37） （6）项目经理的基本工资总额: =员工档案!K3+员工档案!K7（7）本科生平均基本工资:第17套根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对博达书店一月份的销售额小计进行比较。选中博达书店销售额小计，单击【开始】选项卡下【图表】组中的柱形图簇状柱形图命令。步骤2：数据透视图中单击书店名称右侧下三角按钮，在下拉列表中只选择博达书店第18套选择【B3:L14】单元格，切换至【开始】选项卡，单击【样式】选项组

13、下的【条件格式】下拉按钮，在下拉列表中选择【突出显示单元格规则】-【大于】，在“为大于以下值的单元格设置格式”文本框中输入“1000”，使用默认设置“浅红填充色深红色文本”，单击“确定”按钮。选择【M3:M14】单元格，切换至【开始】选项卡，单击【样式】选项组下的【条件格式】下拉按钮，在弹出的下拉列表中选择【突出显示单元格规则】-【大于】，在“为大于以下值的单元格设置格式”文本框中输入“=$M$15*110%”，设置颜色为“黄填充色深黄色文本”，单击“确定”按钮。季度: =”第”&INT（1+（MONTH（A3）-1）/3）&”季度”第19套停放时间: =DATEDIF（F2,H2,”YD”）

14、*24+（I2-G2）收费金额: =E2*ROUNDUP（HOUR（J2）*60+MINUTE（J2）/15）,0）拟收费金额: =E2*ROUNDDOWN（HOUR（J2）*60+MINUTE（J2）/15）,0）差值： =K2-L2第20套单价: =VLOOKUP（图书名称,表2,2,0）销售额小计注：销售量超过40本（含），按折销售=IF（销量（本）=40,单价*销量（本）*,单价*销量（本）所属区域: =VLOOKUP（MID（发货地址,1,3）,表3,2,0）2013年所有图书订单的销售额=SUMIFS（表1销售额小计,表1日期,”=2013-1-1”,表1日期,”=2012-1-1”,表1日期,”=2013-7-1”,表1日期,”=2013-12-31”）设置数字格式为百分比，保留两位小数