计算机二级excel真题题库重点难点解题步骤

根据题库列出excel每套题的难点及重点解题步骤，帮助你前期高质量刷题及后期高速复习，一次通过计算机二级考试！！

excel真题

37、朱明慧银行流水账

导入外部文件，删除与外部数据的连接：“数据”-“获取外部数据”-“自文本”-“数据”-“连接”-“连接”-删除链接

转化日期格式【通过分列转换格式】-选中日期列-“分列”-“日期”-“设置数据格式”-自定义-yyyy年mm月dd日

求账户余额【需先对收入和支出列的表格格式进行调整】：用“分列”转化或者在公式中输入N函数 =N(D4)+N(B5)-N(C5)- =D4+B5-C5

标题跨列居中：选中标题-对齐方式-跨列居中

隐藏行列：选择列-ctrl+shift+→选中所有列，单击右键-隐藏

锁定前3行：选中第4行-视图-冻结窗格

求2016年第3季度的销售收入：=SUMIFS(表1[收入],表1[收支项目],D2,表1[交易日期],">=2016-7-1",表1[交易日期],"<=2016-9-30")

求毛利率【错误值需显示为空，因此使用iferror函数】：=IFERROR(F3/D3,"")

制作气泡图：只选中年度总计的数据，插入气泡图

数据透视表：设计-布局-报表布局-以表格显示；选中单元格-单击右键-数据透视表选项-勾选“合并且居中排列带标签的单元格”；“设计”-“布局”-“在组的底部显示所有分类

数据透视表中的空格填入0：任意选中单元格-单击右键-“数据透视表选项”-在“对于空单元格，显示”输入0

36、东方公司员工工资表

35、财务小朱银行流水

34、许晓璐自行车贸易公司

修改日期中不规范格式，设置单元格格式：将“.”替换为“-” -设置单元格格式-日期--英语（美国）-类型选择 March 14,2012

添加代码：【vlookup函数的查询列应该在第一列】：修改地区和代码位置，复制粘贴重新建立数据区域，将地区移到代码前-在客户编号前插入新一列，标题处输入客户编号-=VLOOKUP(E2,地区代码!$A$7:$B$10,2,FALSE)&D2-复制粘贴值【粘贴值后再删除原编号列就不会影响公式】-删除原来的客户编号列

求产品价格：=VLOOKUP(G2,产品信息!$B$2:$C$26,2,FALSE)

修改设置数据有效性的错误：选中E2单元格-“数据”-“数据验证”-“序列”-来源输入“=INDIRECT(D2)”勾选“对有同样设置的所有单元格应用这些更改”-

冻结首行：“视图”-“冻结窗格”-“冻结首行”

竖排文字：选中单元格-对齐方式-设置单元格格式-对齐-方向

计算各类别商品的总金额：=SUMIFS(销售资料!$J$2:$J$117,销售资料!$D$2:$D$117,$B3,销售资料!$F$2:$F$117,C$2)

设置数据有效性：“数据”-“数据验证”-“序列”-“来源”为类别的数据区域

在B8单元格输入日用品-选中数据区域-“公式”-“定义的名称”-“根据所选内容创建”-勾选“首行”-选中区域内的任意单元格-“定义名称”-在名称处输入各类别销售汇总-“引用位置”输入=indirect(销售汇总!$B$8)

插入图表-选择数据-图例项，添加-系列值为 =销售汇总!各类别销售汇总；水平轴标签，编辑，轴标签区域为 =销售汇总!$B$3:$B$6

条件格式-新建规则-使用公式-=$B$8=C$2-格式

为形状添加超链接：选中形状-链接-修改单元格引用为A2

33、老钱购销数据

32、小许中国主要城市空气质量

删除超链接：选中数据区域-单击右键-删除超链接

设置单元格格式：“设置单元格格式”-“自定义”-yyyy"年"m"月"d"日"-修改成 yy"年"m"月"d"日"

求空气质量指数级别：=IF(B2<=50,"一级",IF(B2<=100,"二级",IF(B2<=150,"三级",IF(B2<=200,"四级",IF(B2<=300,"五级","六级")))))

求空气质量指数类别：=VLOOKUP(I2,空气质量指数说明!$B$2:$C$7,2,0)

北京工作表中比较每天PM数值，较大的设置格式：“条件格式”-“新建规则”-“使用公式确定要设置格式的单元格”-=C2=max($C2:$D2)【C和D中之中较大的应用格式】-设置格式

上海工作表中PM2.5、10都大于100的整行设置格式：“条件格式”-“新建规则”-“使用公式确定要设置格式的单元格”-=and($C2>100,$D2>100)-设置格式

广州工作表中，周末且AQI指数大于全年平均的设置格式：=and(weekday($A2,2)>5,$B2>average($B$2:$B$367))

取消保护：“审阅”-“保护工作簿”

“数据透视表和数据透视图向导”-勾选“多重合并计算数据区域”-勾选“数据透视表和数据透视图”-勾选“自定义页字段”-选择各个工作表前两列，“添加”-勾选页字段数目中的“1”-在字段1 输入工作表名称；选中日期列的任意单元格-单击右键-创建组-月【让该列按月汇总】；选中AQI指数-单击右键-值汇总方式-平均值

当标签的值大于100时，字体颜色可变换：添加数据标签-设置数据系列格式-“数字”-“格式代码”-[>100][红色]0;0-“添加”

隐藏按钮：“分析”-“字段按钮”-“隐藏”

图表标题随B1变化而变化：选中图表标题-在上面的编辑栏中输入 = 并选中B1单元格

标题所在行在打印时会重复出现：“页面设置”-“顶端标题行”-选中工作表标题行

添加页脚：“页面设置”-“页眉/页脚”-“自定义页脚”

31、小刘各省市计票数据

求各省市选票数、抽样数：添加辅助列，用vlookup根据代码查询出地区-sum求和-vlookup

条件格式

候选人各省选票数：插入新工作表-数据-合并计算—引用三个阶段结果-勾选首行、最左列-第二行插入省份行（转置粘贴）-match、index

排名：rank

锁定

得票率：vlookup

得票率最高：Max、match、index

条件格式：新建规则-公式-rank

簇状图表

30、小金公司社保费用计算（救命好难，咱先跳过好吧）

提取身份证每一位数字：=MID($C3,COLUMN(D2)-3,1)

计算校验码：=VLOOKUP(MOD(SUMPRODUCT(D3:T3*校对参数!$E$5:$U$5),11),校对参数!$B$5:$C$15,2,0)

校验结果：=IF(U3=TEXT(V3,0),"正确","错误")

条件格式：条件格式-新建规则-使用公式-=$W3="错误"

根据员工编号填入身份证：=MID(VLOOKUP(员工档案!A3,身份证校对!$B$3:$V$122,2,FALSE),1,17)& VLOOKUP(员工档案!A3,身份证校对!$B$3:$V$122,21,FALSE)

计算年龄：=INT(YEARFRAC(E3,"2016-12-31",3))

计算工龄

数据透视表

29、林明德网站注册会员

注意另存为的文件格式，不是excel

取消隔行底纹：取消“镶边行”

M替换为男，F替换为女：替换时勾选“单元格匹配”

生日：“设置单元格格式”-“自定义”-“yy"年"m"月"d"日"”

求年龄：=DATEDIF([@生日],"2017-1-1","y")

求年龄段：自行建立数据区域，复制粘贴年龄段，在旁边列写出每个人年龄段的最小年龄-=VLOOKUP([@年龄],按年龄和性别!$F$2:$G$12,2,1)

求年消费金额：取消顾客编号的“合并后居中”--选中空值并填充-=SUMIFS('2016年消费'!$C$2:$C$364,'2016年消费'!$A$2:$A$364,[@顾客编号])

数据验证/数据有效性：序列-男,女-出错警告：仅可输入中文

录制“最小年龄”列的宏：“视图”或“审阅”-“宏”-“录制宏”-宏名为最小年龄-快捷键为 shift+U-“条件格式”-“最后10项”-“确定”-左下角停止宏录制-选中“最小年龄”列-按ctrl+shift+U

筛选出年消费金额最小的15位顾客，年龄后10保持深红色文本：“条件格式”-“新建规则”=rank($F2,$F$2:$F$101,1)<15-“条件格式”-“管理规则”-后10个-确定

求男顾客人数：=COUNTIFS(客户资料[性别],"男",客户资料[年龄段],A2)

打印时第一行会出现在每一页的顶部：页面设置右下角箭头工作表-“顶端标题行”选中第一行

插入可以自动更新的页眉页脚：“自定义页眉”-“插入表格标题”-“自定义页脚”-插入页码

28、小何本月销售数据

为数据区域定义名称：选中整个数据区域-单击右键-定义名称

将4周数据合并到月销工作表中：数据-合并计算-逐个选中4周工作表内的数据 “添加”-勾选首行、最左列-

月销量为0的菜品行删除：“替换”-查找内容为0-选项-勾选“单元格匹配”-查找和选择-定位条件-空值-删除-整行

锁定首行首列：选中第2行第2列的单元格-视图-冻结窗格-冻结拆分窗格

导入文本文件

求类别：=VLOOKUP([@名称],品种目录!$B$2:$D$919,3,0)

27、阿文食品贸易公司销售数据

将等于0的单元格设置格式为-：“条件格式”-“等于”-“自定义格式”-“数字”-“自定义”-输入“-”

清除不可见字符和空格：=TRIM(CLEAN(B2))

求发货城市：=VLOOKUP(B2,客户信息!$A$2:$F$92,5,FALSE)

求发货地区：=VLOOKUP(B2,客户信息!$A$2:$F$92,6,FALSE)

求订单金额（注意这里不要用vlookup）：=SUMIFS(订单明细!$H$2:$H$907,订单明细!$A$2:$A$907,A2)

日期间隔大于10设置格式：条件格式-新建规则-使用公式确定要设置格式的单元格-=$D2-$C2>10

销售额：=SUMIFS(订单明细!$H$2:$H$907,订单明细!$D$2:$D$907,产品类别分析!A2)

取消饼图之间的间隙-设置数据系列格式-边框-无线条

将饼图分为4块：设置数据系列格式-第二绘图中的值-4

将两个行标签分为两列显示-设计-报表布局-以表格形式显示

选中任意单元格-单击右键-数据透视表选项-勾选“合并且居中排列带标签的单元格”

设计-分类汇总-不显示分类汇总

取消＋号按钮-分析-显示-+/-按钮

求客户等级（先计算销售额，在计算等级）：=VLOOKUP(SUMIFS(订单信息!$G$2:$G$342,订单信息!$B$2:$B$342,客户信息!A2),客户等级!$A$2:$B$11,2,1)

添加文档属性-文件-信息-高级属性-自定义

26、陈颖主要城市降水量分析

删除名称列里的拼音：复制粘贴到新建word中用任意字母替换为空 删去拼音 再复制粘贴回excel

城市名称后添加市：在城市列后新建一列，=城市名称单元格&"市"

选中数据区域-“设置单元格格式”-“自定义”-“类型”输入“[<15]干旱”-“条件格式”-“突出显示单元格”-“小于”

应用数据条件格式：“条件格式”-“数据条”-“其他规则”-勾选“仅显示数据条”

降水量排名：=RANK([@合计降水量],[合计降水量],0)

“插入”-“迷你柱形图”-“设计”-“标记颜色”-“高点”

在R3、S2建立数据透视性，查询降水量：R3-“数据”-“数据验证”-“允许”填入“序列”-=INDEX(B2:M33,MATCH(R3,表1[城市（毫米）],0),MATCH(S2,表1[[#标题],[1月]:[12月]],0))

文件-选项-所有命令-数据透视表和数据透视图向导-新建组-添加-数据透视表和数据透视图向导-多重合并计算数据类型-自定义页字段-选择数据区域-确定；调整标签位置-拖拽月份顺序 全年占比列 值显示方式为总计的百分比；分析-数据透视表-选项-显示报表筛选页

页面设置-1页宽1页高

文件-信息-属性-高级属性-自定义-名称为类别、取值输入水资源-添加

25、李晓玲采购成本

强制换行：alt+回车

通过公式计算经济订货批量的值（SQRT开根号函数）：=SQRT((2*$C$2*$C$3)/$C$4)

模拟运算表：先在C7空白单元格引入C5的经济订货批量数据 =C5-选中数据区域-“数据”-“模拟分析”-“模拟运算表”-选中相应的行和列的单元格-“确定”

选中C2-C4单元格-“数据”-“模拟分析”-“方案管理器”-“添加”

定义表格名称：选中单元格在左上角更改

生成方案摘要：选中单元格-“数据”-“模拟分析”-“方案管理器”-“摘要”

24、小马公务员考试成绩数据整理

取消网格线：“页面布局”-“工作表选项”-取消勾选网格线“查看”

性别列中的空白单元格输入男：可以用“查找和选择”的定位条件-空值 或者 “替换”的空替换男

修改并应用标题1样式：“开始”-“单元格样式”-“标题1”-“修改”-“格式”-修改文字样式-“对齐”-“跨列居中”

求部门代码、总成绩：=MID(B4,1,3)；=IF(MID(B4,4,1)="1",J4*0.5+K4*0.5,J4*0.6+K4*0.4)

隐藏批注：选中单元格-“审阅”-“隐藏批注”

设置数据有效性：选中准考证号-“文本长度 等于 12”-“出错警告”处输入“出错范围请重新输入！”-“文本长度 等于 12”-“出错警告”处输入“出错范围请重新输入！”；选中“面试分数”-“整数 介于 0 100”-“出错警告”处输入“出错范围请重新输入！”

标出错误数据：“开始”-“条件格式”-“新建规则”-“使用公式确定要设置格式的单元格”-“=len($B4)<>12”-设置格式

取消筛选：“设计”-“表格样式”-取消勾选“筛选按钮”-“开始”-“排序和筛选”-“筛选”没底色时即设置成功

锁定1-3行：选中第4行-“视图”-“冻结窗格”

分别以数据区域的首行作为各列的名称：“选中单元格”-“公式”-“根据所选内容创建”-“首行”

填写部门代码和报考部门：先复制粘贴部门代码-数据-删除重复值-排序；报考部门：=VLOOKUP(B5,名单!$F$4:$G$1777,2,FALSE)

求各部门女性人数：=COUNTIFS(性别,"女",报考部门,统计分析!C5)

笔试最低分数：=MIN(IF(部门代码=统计分析!B5,笔试分数))再按ctrl+shift+回车【数组函数需要按组合键：ctrl+shift+回车】

设置条件格式，标出非空单元格和偶数行：“条件格式”-“新建规则”-“使用公式确定要设置格式的单元格”-=and(A1<>"",mod(row(),2)=0)

图表标题随表格同步变化：选中图表标题的文本框-在“编辑栏”输入=-选中表格中标题的单元格

移动图表：选中图表-单击右键-“移动图表”

23、小刘整理有关减免税政策

取消网格线：“视图”-取消勾选“网格线”

自动换行：“开始”-“对齐方式”-“自动换行”

收入种类：=VLOOKUP(MID([@减免性质代码],1,2),代码!$B$5:$C$22,2,FALSE)；减免政策大类：=VLOOKUP(MID([@减免性质代码],3,2),代码!$E$5:$F$15,2,FALSE)；减免政策小类：=VLOOKUP(MID([@减免性质代码],5,2),代码!$H$5:$I$49,2,FALSE)

没有年份显示为空：“查找和选择”-“定位条件”-“公式”-勾选“错误”

添加批注并隐藏：将批注文本复制粘贴到新建的text文件中，再回到excel中-“审阅”-“新建批注”-粘贴批注-单击右键-“设置批注格式”

保持1-3行，A:E列总是可见（冻结窗口）：“视图”-“冻结窗口”-“冻结拆分窗格”

建立数据透视表，以类别为表名（显示报表筛选页）：插入数据透视表-更改列标签的年份-“文件”-“选项”-“高级”-“自定义序列”-输入序列-“添加”；“数据透视表工具”-“分析”-“数据透视表”-“选项”-“显示报表筛选页”

22、晓雨企业员工office应用能力考核

取消镶边行后转化为区域：选中单元格区域-应用表格样式-“表格工具”-“表格样式选项”-取消“镶边行”-“工具”-“转换为区域”

删除姓名列中所有汉语拼音字母，只保留汉字（复制到word中使用替换功能快速删除所有拼音字母）：复制粘贴姓名列至新建的word中-“任意字母”替换为空-输入一个空格 替换为空-复制粘贴回excel-粘贴时使用“匹配目标格式粘贴”

以001、002格式编号：选中编号列-设置单元格格式-自定义-000

空格输入0：“查找和选择”-“定位条件”-“空值”-在编辑栏输入0-按住ctrl+回车

填写考核成绩等级：=IF(OR(G3<60,H3<60,I3<60,J3<60,K3<60),"不合格",IF(L3<75,"及格",IF(L3<85,"良","优")))

自定义序列排序，部门相同则按成绩排：“排序和筛选”-“次序”选择“自定义序列”-在“输入序列”处输入“研发部 物流部 采购部 行政部 生产部 市场部”-“添加”-“添加条件”

创建数据透视表：到“分数段统计”B2单元格插入数据透视表-“选择一个表或区域”中选择“成绩单”内的数据区域-在“值”移入两个“姓名”-选择行标签的某一单元格-单击右键-“组合”-“起始于”填入60，“终止于”填入100-选择姓名列内的某一单元格-“值显示的方式”-“总计的百分比”-“设置单元格格式”-“百分比”-1位

创建透视数据图：“数据透视表工具”-“分析”-“数据透视图”-“设计”-“更改图表类型”-“组合图”-“带数据标记的折线图”-勾选“次坐标轴”

插入散点图：选中数据区域-插入散点图-插入坐标轴标题-设置横坐标轴格式-设置纵坐标轴格式-删除网格线

添加公式、添加趋势图：设置数据线格式-趋势线选项勾选线性-勾选“显示公式”

设置打印区域，设置标题行在打印时可以重复出现在每页顶端：选中成绩单的所有单元格-“页面布局”-“打印区域”-“设置打印区域”-“打印标题”“顶端标题行”选择第二行

“页面设置”右下角箭头-“页眉页脚”-“自定义页眉页脚”-在自定义页脚的“第页，共页”插入页码-“文件”-“打印”-预览结果，有遗漏的再重新添加

21、销售经理鹏程公司销售情况

所有空白单元格填充数字0：“开始”-“查找和选择”-“定位条件”-“空值”-在上方“编辑栏”输入0，再按ctrl+回车 或用“替换”-“查找内容”为空，“替换为”输入0

更改日期类型：“设置单元格格式”-“自定义”-类型由“yyyy/m/d”改为“yyyy/mm/dd”

求商品单价（输入公式后显示为公式 本身，需要将数据格式改为常规）：=VLOOKUP([@咨询商品编码],商品单价!$A$3:$B$7,2,FALSE)

求销售经理成交额按月统计表（有日期、经理限制两个限制）：=SUMIFS(表1[成交金额],表1[销售经理],月统计表!$A3,表1[咨询日期],">=2014-1-1",表1[咨询日期],"<=2014-1-31")

插入堆积柱形图，切换行和列

1、小李隆华书店

设置表格外观格式：选中第二行标题行（不要选中第一行合并的单元格），按住ctrl+shift+向下键（连续向下选择）-“开始”-“套用表格格式”

将单元格调整为“会计专用”（人民币）数字格式：选中单价、小计列，右键设置单元格格式-会计专用-CNY

使用vlookup自动填充图书名称和单价的自动填充：=VLOOKUP([@图书编号],表2,2,FALSE) - =VLOOKUP([@图书编号],表2[#全部],3,FALSE)

所有订单的总销售金额：=SUM(表3[小计]) - 撤销计算列

《MS Office高级应用》图书在2012年的总销售额：=SUMIFS(表3[小计],表3[图书名称],订单明细!E7,表3[日期],">=2012-1-1",表3[日期],"<=2012-12-31")

隆华书店在2011年第3季度（7月1日~9月30日）的总销售额：=SUMIFS(表3[小计],表3[书店名称],订单明细!C12,表3[日期],">=2011-7-1",表3[日期],"<=2011-9-30")

隆华书店在2011年的每月平均销售额（保留2位小数）【先求出该年的总销售额再除以12个月】：=SUMIFS(表3[小计],表3[书店名称],订单明细!C12,表3[日期],">=2011-1-1",表3[日期],"<=2011-12-31")/12

2、小蒋初一学生成绩单

学号列单元格格式为文本、成绩列为数值：选中学号列，设置单元格格式为文本；选中成绩列，设置单元格格式为数值，保留两位小数

利用条件格式设置语数英不低于110以颜色填充：选中列-“开始”-“条件格式”-“突出显示单元格规则”-“其他规则”-“大于或等于”-“110”-在“格式”设置填充颜色

其他四科高于95分的以一种字体颜色标出：选中列-“开始”-“条件格式”-“大于”-“95”-“红色文本”

求平均分：=SUM(D2:J2)

求总分：=AVERAGE(D2:J2)

通过函数提取学号填写班级：=MID(A2,4,1)&"班"

复制工作表：按住ctrl移动

通过分类汇总求出各个班的平均成绩（按照分类字段排序），将每组结果分类显示：“数据”-“分级显示”-“分类汇总”-“分类字段”为班级、汇总方式为平均值、选定汇总项为语数英政史地-勾选“每组数据分页”

表格最左边，选择“2”使表格显示两个级别-“插入”-“簇状柱形图”-“切换行列”-“移动图表”-“新工作表：柱状分析图”

4、全国人口普查

浏览网页，表格导入到工作表中：“数据”-“现有连接”-“浏览更多”-考生文件夹内的文件-选择表格-“导入”

人口列数字格式为千位分隔符的整数：选中表格-单价右键-设置单元格格式-数值-小数位数为0，勾选“使用千位分隔符

“数据”-“合并计算”-“引用位置”选择第五、六次普查数据的所有单元格-添加-勾选“首行”、“最左列”

将excel的“统计数据”插入另一excel“比较数据”的右侧“来到“统计数据”的excel-单击右键左下角工作表名称-移动或复制-选择文档-移动到最后

统计2000年人口最多和最少的地区：复制“比较数据”的表格，命名为辅助表，在辅助表中的人口数按降序排序，在比较数据中输入辅助表中人口最多和最少的省份-删除辅助表

数据透视表要求筛选出：“插入”-“数据透视表”-移动字段到相应的“行”和“值”处-“行标签”-“值筛选”-“大于”-5000-选中单元格-单击右键-设置单元格格式

5、小王2013年公司差旅报销情况

先删除最后一行

设置日期显示格式：“设置单元格格式”-“自定义”-将“yyyy”年“月”d“日”；@”改成“yyyy”年“月”d“日” aaaa”（去掉@ 加上一个空格和aaaa）

如果为周六或周日，则在是否加班列显示是，否则显示否：=IF(WEEKDAY(A3,2)>5,"是","否")

填写活动地点所在的省份或直辖市：=LEFT(C3,3)

根据费用类别编号生成费用类别：=VLOOKUP(E3,表4[#全部],2,FALSE)

（条件求和）：先删除最后一行

2013年第二季度发生在北京市的差旅费用金额总计为：=SUMIFS(费用报销管理!G2:G401,费用报销管理!A2:A401,">=2013-4-1",费用报销管理!A2:A401,"<=2013-6-30",费用报销管理!D2:D401,费用报销管理!D322)

2013年钱顺卓报销的火车票总计金额为：=SUMIFS(费用报销管理!G2:G401,费用报销管理!B2:B401,费用报销管理!B398,费用报销管理!F2:F401,费用报销管理!F387)【=sumifs（差旅费用金额,报销人列,钱顺卓,费用类别,火车票）】

2013年差旅费用金额中，飞机票占所有报销费用的比例为（保留2位小数）：=SUMIFS(费用报销管理!G3:G401,费用报销管理!F3:F401,费用报销管理!F3)/SUM(费用报销管理!G3:G401)

2013年发生在周末（星期六和星期日）中的通讯补助总金额为：=SUMIFS(费用报销管理!G3:G401,费用报销管理!H3:H401,费用报销管理!H3,费用报销管理!F3:F401,费用报销管理!F391)

6、文涵公司销售情况

以001、002、003...的方式向下填充到该列到最后一个数据行：选中该列，“设置单元格格式”-文本

将区域B3:C7定义名称为“商品均价”：选中区域，单击右键“定义名称”

求销售额：=E4*VLOOKUP(销售情况!D4,商品均价,2,FALSE)

数据透视表

创建簇状柱形图，仅对各门店四个季度笔记本的销售额进行比较：选中行标签和季度数据（不要选中总计的数据），插入簇状柱形图，选择“笔记本”-“确定”

7、小李编制2014年3月员工工资表

整个工作表需调整在1个打印页内：“页面布局”-“调整为合适大小”-“宽度”和“高度”都调整为1页

用if求个人所得税：先将税率表复制粘贴至excel中，=IF(K3<=1500,K3*0.03-0,IF(K3<=4500,K3*0.01-105,IF(K3<=9000,K3*0.2-555,IF(K3<=3500,K3*0.25-1005,IF(K3<=55000,K3*0.3-2755,IF(K3<=80000,K3*0.35-5505,K3*0.45-13505))))))

分类汇总（先排序）：先将部门排序-“数据”-“分级显示”-“分类汇总”-“分类字段”选择部门，勾选“应付工资合计”、“实发工资”，不勾选“每组数据分页”

8、小李2012级法律专业学生期末成绩分析

排名：=RANK([@总分],[总分],)

对不及格的单元格套用格式：“条件格式”-“突出显示单元格规则”-“小于”-“60”-“设置为” “自定义格式”-填充为黄色，字体为红色

利用学号填写班级：=LOOKUP(MID([@学号],3,2),{"01","02","03","04"},{"法律一班","法律二班","法律三班","法律四班"})

在数据透视表中统计各科平均值：单击右键-值汇总依据-平均值

9、小王图书产品销售情况

根据图书名称填写图书编号：=VLOOKUP([@图书名称],表3[#全部],2,FALSE)

将重复的订单编号数值标记为紫色：条件格式-突出显示单元格规则-重复值

将紫色字体排列在列表区域的顶端：“排序和筛选”-“自定义排序”-主要关键词 订单编号；排序依据 字体颜色；次要关键词 订单编号；排序依据 单元格值

1月：=SUMIFS(表1[销量（本）],表1[图书名称],[@图书名称],表1[日期],">=2013-1-1",表1[日期],"<2013-2-1") ；2月：=SUMIFS(表1[销量（本）],表1[图书名称],[@图书名称],表1[日期],">=2013-2-1",表1[日期],"<2013-3-1")； ... 12月：=SUMIFS(表1[销量（本）],表1[图书名称],[@图书名称],表1[日期],">=2013-12-1",表1[日期],"<=2013-12-31") 或者用数据透视表来做：图书名称设置为行标签，年、日期设置为列标签，销量设置为值（将图书名称设置为自定义序列：选中图书名称列-文件-选项-高级-编辑自定义列表-导入）

“设计”-“汇总行”-点击汇总行空格-“求和”

在N4：N11中插入迷你折线图：选中表格区域-“插入”-“迷你图”-“折线图”-“数据范围”为前面各月的数据-“位置范围”为N4：N11-在“显示”中勾选“高点”、“低点”

以A1单元格为数据透视表的起点：点击要A1单元格-“分析”-“操作”-“移动数据透视表”

将列标签的年标签和季度标签去掉-选中单元格-单击右键-创建组/组合-起始于 填2012/1/1，终止于 填 2012/12/31，步长为 季度-“列标签”-取消勾选“>2012/12/31”

10、王老师成绩通知单

将以制表符分隔的文本导入工作表：“数据”-“自文本”-“文件原始格式”选中“简体中文”-“下一步”-勾选“制表符”-“下一步”-选中身份证号码列-勾选“文本”-“完成”

将学号和姓名分成两列显示：先在需要分割的列后插入一个空白列-在学号和姓名中间插入几个空格-“数据”-“分列”-勾选“固定宽度”-在“数据预览”中分割-“完成”

选择数据区域-“插入”-“表格”-勾选“表包含标题”

性别（根据身份证第17位来判断，奇数为男，偶数为女）：=IF(ISODD(MID([@身份证号码],17,1)),"男","女")

出生日期（根据身份证第7位）：=TEXT(MID([@身份证号码],7,8),"0000年00月00日")

年龄【=datedif(起始日期,终止日期,日期单位)】：=DATEDIF(E5,TODAY(),"y")

选中语文工作表，按住shift再点历史，同时选中所有科目的工作表-根据学号在每科工作表输入姓名 =VLOOKUP(A2,档案[[学号]:[姓名]],2,FALSE)

班级排名（需要绝对引用）：="第"&RANK(F2,F1:$F$45)&"名"或用text（）,"第0名"

期末总评：=IF(F2>=102,"优秀",IF(F2>=84,"良好",IF(F2>=72,"及格","不合格")))

调整表格行高和列宽：“开始”-“单元格”-“格式”-“行高”、“列宽”

总分排名（需要绝对引用，按f4）：=RANK(J3,$J$3:$J$46)

各科第一名用红色加粗标出：“插入”-“样式”-“条件格式”-“最前/最后规则”-“1”-“自定义”

总分前10用蓝色填充：“插入”-“样式”-“条件格式”-“最前/最后规则”-“10”-“自定义”

缩减打印输出使得所有列只占一个页面宽：“页面布局”-“页面设置”-“调整为” “1页宽”

11、小赵2013年开支明细表

为工作表应用主题：“套用表格格式”-应用一种样式-“转换为区域”

将月总单项支出大于月均总支出110%的突出显示：“条件格式”-“新建规则”-“使用公式确定要设置格式的单元格-公式：=$M3>$M$15*1.1 或者直接用“大于”来做

插入季度列，数据根据月份填充（先求出月份，再用月份除以3，向上取整，保留0位小数）：=ROUNDUP(MONTH(A3)/3,0)&"季度"

光标放在季度单元格-升序-“数据”-“分类汇总”-“分类字段”为“季度”-“汇总方式”为“平均值”-勾选“选定汇总项”的内容

单击工作表左上的2，使显示级别为2-选中数据区域-插入带数据标记的折线图-切换行列-移动图表-双击折线最高点-右侧+号添加数据标签

12、小罗停车场收费标准调整

填写收费标准（收费标准表格绝对引用）：=VLOOKUP(C2,收费标准!$A$2:$B$5,2,FALSE)

停放时间=出场日期-进场日期+出场时间-进场时间：=H2-F2+I2-G2 - “设置单元格格式”-“时间”-类型选择“时间-xx时xx分”-“设置单元格格式”-“自定义”-在 h"时"mm"分";@ 的h前加一个h 变成 hh"时"mm"分";@

收费金额，不足15min按15min收费：=ROUNDUP(J2*24*60/15,0)*E2 【先求出停放天数J2*24*60-再求出这些天数里有多少个15min-再向上取整、保留零位小数-再乘收费标准】

拟收费金额，不足15min不收费： =ROUNDDOWN(J2*24*60/15,0)*E2

求差值，即调整后的减去当前收费，即拟收费减去收费

添加汇总行：“设计”=勾选“汇总行”

单次停车收费达到100元的单元格突出显示：“条件格式”-“突出显示单元格规则”-“其他规则”-“大于或等于”-100-设置填充效果和字体-“数字”-“货币”

创建3个数据透视表：先创建一个，再复制粘贴两个数据透视表，再改变“值”的内容即可

13、小王公司产品销售情况

删除订单编号重复的记录：“数据”-“数据工具”-“删除重复值”-勾选“订单编号”

求书的单价：=VLOOKUP([@图书名称],表2[#全部],2,FALSE)

销售额小计，销量超过40本按93折销售，否则按原价：=IF([@销量（本）]>=40,[@单价]*0.93*[@销量（本）],[@单价]*[@销量（本）])

根据发货地址填写所属区域：先取出省市，再用vlookup查询：=VLOOKUP(LEFT([@发货地址],3),表3[#全部],2,FALSE) 或=vlookup(mid(发货地址,1,3),城市对照,2,false)

根据销售记录创建东西南北区4个工作表，分别统计各类图书的累计销售金额【数据透视表筛选项】：“所属区域”放在“筛选”、“图书名称”放在“行”、“销售额小计”放在“值”-修改行标签和求和项-“分析”-“数据透视表”-“选项”-“显示报表符选项”-“确定”-删除原表

2013年所有图书订单的销售额：=SUMIFS(表1[销售额小计],表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")

《MS Office高级应用》图书在2012年的总销售额 ：=SUMIFS(表1[销售额小计],表1[图书名称],订单明细!D26,表1[日期],">=2012-1-1",表1[日期],"<=2012-12-31")

隆华书店在2013年第3季度（7月1日~9月30日）的总销售额 ：=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],">=2013-7-1",表1[日期],"<=2013-9-30")

隆华书店在2012年的每月平均销售额（保留2位小数） ：=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],">=2012-1-1",表1[日期],"<=2012-12-31")/12

2013年隆华书店销售额占公司全年销售总额的百分比（保留2位小数） ：=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")/B3

15、大华前两季度销售情况

求单价：=VLOOKUP(B2,产品基本信息表!B:C,2,FALSE)

求一季度销量：=SUMIFS(一季度销售情况表!D:D,一季度销售情况表!B:B,产品销售汇总表!B2)

求一季度销售额：=SUMIFS(一季度销售情况表!F:F,一季度销售情况表!B:B,B2)

求二季度销量：=SUMIFS('二季度销售情况表 '!D:D,'二季度销售情况表 '!B:B,产品销售汇总表!B2)

求二季度销售额：=SUMIFS('二季度销售情况表 '!F:F,'二季度销售情况表 '!B:B,产品销售汇总表!B2)

求一二季度销售总量：=C2+E2

求一二季度销售总额：=D2+F2

求总销售额排名：=RANK(H2,$H$2:$H$21)

将后3名标出：“条件格式”-“最前最后规则”-“最后10项”-“自定义格式”-3

套用表格格式，取消筛选：“套用表格格式”-“数据”-“筛选”

16、小罗滨海市物理统考

求各班考试学生数：=COUNTIFS(成绩单!$A$2:$A$950,按班级汇总!A2,成绩单!$B$2:$B$950,按班级汇总!B2)

求各学校考试学生数：=COUNTIFS(成绩单!$A$2:$A$950,按学校汇总!A2)

求各班平均分：=AVERAGEIFS(成绩单!D2:D950,成绩单!A2:A950,A2,成绩单!B2:B950,B2)

求各校平均分：=AVERAGEIFS(成绩单!$D$2:$D$950,成绩单!$A$2:$A$950,A2)

求各班客观题平均分（将一个班客观题加起来即本班客观题平均分，用sum函数）：=SUM(小分统计!C2:AP2)

求各班主观题平均分：=SUM(小分统计!AQ2:AZ2)

求各学校客观题平均分（先各班平均分乘人数，再除以该校总人数）【不能直接按+号拉，要一个个学校输入公式】：=SUMPRODUCT(按班级汇总!C2:C9,按班级汇总!G2:G9)/按学校汇总!B2

求各校每题的得分率（用该校该题的的分值乘该校相应各班人数，除以该校考试人数，除以该题的分值。其中相应各班人数和考试人数需要绝对引用。例如：一中在第一题的得分数乘一中各班人数，除以一中考试人数，除以第一题的分值）

所有单元格转置复制到新工作表中：复制单元格-单击右键“选择性粘贴”-“选择性粘贴”-勾选“数值”、“转置”

17、小王公司上半年产品销售情况的统计分析

求各月销售达标率（以1月为例）：=COUNTIFS(表1[一月份],">60000")/COUNT(表1[一月份])

求各月销售第一、二、三名业绩（以1月为例）：=LARGE(表1[一月份],1)；=LARGE(表1[一月份],2)；=LARGE(表1[一月份],3)

插入数据透视表：把“销售团队”移至“行”；把“姓名”、“个人销售总计”移至“值”

插入图表：插入“堆积柱状图”-选中计划销售额的柱-设置-更改图表类型-勾选“次坐标轴”-在“格式”中设置无填充色，形状轮廓为红色，粗细加大-单击右键“设置数据系列格式”-缩小“间隙宽度”

18、李东阳家用电器企业销售情况分析

导入数据：“数据”-“获取外部数据”-“自文本”-下一步-确定

001格式：001-“设置单元格格式”-“自定义”-“000”

不显示工作表网格线：“页面布局”-“工作表选项”-取消勾选网格线“查看”

使内容显示在单元格区域正中间（跨列居中）：“开始”-“对齐方式”右下角箭头-“对齐”-“水平对齐”-“跨列居中”

根据类型求价格：=VLOOKUP(C4,价格表!$B$2:$C$5,2,FALSE)

求金额（数量乘价格乘折扣，折扣需根据类别寻找）【先转置在vlookup中嵌套多个if函数】：运用vlookup函数时注意查询数据必须位于列，需要先对折扣表的数据进行转置，复制转置粘贴-=D4*E4*(1-VLOOKUP(C4,折扣表!$B$8:$F$11,IF(销售记录!D4<1000,2,IF(销售记录!D4<1500,3,IF(销售记录!D4<2000,4,5))),FALSE))

“条件格式”-“新建规则”-“使用公式确定要设置格式的单元格”-=WEEKDAY($B4,2)>5

数据透视表：单击右键-“组合”-“月、季度”

数据透视图：“数据透视表工具”-“分析”-“数据透视图”

趋势线：选中b线-“设计”-添加图表元素-趋势线-其他选项趋势线选项-勾选“显示公司”、“显示R平方值”

右下角字段按钮：“数据透视图工具”-“字段按钮”-“显示展开折叠整个字段按钮”

高级筛选【条件区域（需要自己输入）+筛选结果】：“数据”-“排序和筛选”-“高级”-“列表区域”为销售记录、“条件区域”为刚刚输入的区域，“复制到”为A6单元格

19、员工基础档案

导入文本文件：“数据”-“获取外部数据”-“自文本”-“分隔符”为逗号-“身份证号”列数据格式为文本

分列：在工号和姓名之间输入空格-“分列”-勾选“固定宽度”

选中数据区域-“插入”-“表格”

性别（提取倒数第二位mid、判断奇偶mod、根据奇偶判断性别if）：=IF(MOD(MID([@身份证号],17,1),2)=0,"女","男")

出生年月日（提取7-14位mid-格式改为“xxxx年xx月xx日”）：=TEXT(MID([@身份证号],7,8),"0000年00月00日")

年龄（只有题目要求一年按360天、每月按30天计算才使用day360）：=ROUNDDOWN(DAYS360([@出生日期],"2015-9-30")/360,0)

月工龄工资（工龄>=30,工龄*50；工龄<30,工龄*20;工龄<1,无工龄；其他工龄，工龄*30）：=IF([@工龄]>=30,[@工龄]*50,IF([@工龄]<10,[@工龄]*20,IF([@工龄]<1,0,[@工龄]*30)))

求姓名：=VLOOKUP(A4,档案[#全部],2,FALSE)；求部门=VLOOKUP(A4,档案[#全部],3,FALSE)；求月基本工资：=VLOOKUP(A4,档案[#全部],14,FALSE)

奖金个税：=VLOOKUP(A4,年终奖金!$A$3:$H$71,7,FALSE)；应发年终奖金：=VLOOKUP(A4,年终奖金!$A$3:$H$71,5,FALSE)

工资条（按员工编号升序排序；工资条是按空行→标题行→金额进行排序）【自行排序大小：空行<标题行<员工行】：先复制粘贴员工列-再复制粘贴68个标题行-排序时“填充”-“序列”-“序列”产生在“列”-“等差序列”-员工行的“步长值”为1-“终止值”为68-员工行的“步长值”为0.8-“终止值”为67.8-员工行的“步长值”为0.5-“终止值”为67.5

设置空行-“开始”-“编辑”-“查找和选择”-“定位条件”-“空值”-“边框”-“无框线”

设置内外框线：先设置无框线-再设置上下框线

20、小任外汇报告完成情况的统计分析

“数据”-“数据验证”（即数据有效性）-“序列”-在“来源”中输入五人的姓名-

标记重复文号：“条件格式”-“突出显示单元格格式”-“重复值”

报告奖金：=IF(D3<=1000,100,IF(D3<=2800,D3*0.08,D3*0.1))+IF(I3="完成",30,0)

完成情况：=IF(AND(F3="是",G3="是",H3="是"),"完成","未完成")

最后两列数据不被修改：先选中所有单元格-单击右键-“设置单元格格式”-“保护”-取消勾选“锁定”-再选中最后两列单元格-单击右键-“设置单元格格式”-“保护”-勾选“锁定”-“审阅”-“保护”-“保护工作表”-直接按确定（因为密码为空，所以不用输入）

三维饼图：先插入三维饼图-再选择数据源-选择修改报告数的0次到5次的合计行及标题行-“设置数据标签格式”-“类别” 百分比、“位数” 2