水冰阁

第11套

知识点

格式设置

页面设置： 打印区域在同一页

If函数的嵌套使用

分类汇总

 =IFERROR(K3*IF(K3>80000,45%,IF(K3>55000,35%,IF(K3>35000,30%,IF(K3>9000,25%,IF(K3>4500,20%,IF(K3>1500,10%,3%))))))-IF(K3>80000,13505,IF(K3>55000,5505,IF(K3>35000,2755,IF(K3>9000,1005,IF(K3>4500,555,IF(K3>1500,105,0)))))),"")

 全月应纳税所得额         税率         速算扣除数（元）

不超过1500元       3%    0

超过1500元至4500元 10% 105

超过4500元至9000元 20% 555

超过9000元至35000元        25% 1005

超过35000元至55000元     30% 2755

超过55000元至80000元     35% 5505

超过80000元                            45% 13505

 =IF(K3>80000,K3*45%-13505,IF(K3>55000,K3*35%-5505,IF(K3>35000,K3*30%-2755,IF(K3>9000,K3*25%-1005,IF(K3>4500,K3*20%-555,IF(K3>1500,K3*10%-105,K3*3%))))))

1、  选中合并单元格，修改字体，字号

2、  填充单元格：在序号的前两行先输入1 2，再填充，选中单元格，设置格式为数值，0位小数，

3、  选中“基础工资”及后的列，设置数据格式为“会计专用”，2位小数，不带货币符号。

4、  选中所有单元格，居中对齐，在菜单“页面布局”中，更改纸张大小为A4，纸张方向为“横向”，在宽度、高度中都设置为1页

5、  打开文件“工资薪金所得税率”，参照内容进行计算：=IF(K3>80000,K3*45%-13505,IF(K3>55000,K3*35%-5505,IF(K3>35000,K3*30%-2755,IF(K3>9000,K3*25%-1005,IF(K3>4500,K3*20%-555,IF(K3>1500,K3*10%-105,K3*3%))))))

6、  公式计算：等号开始，输入公式所需的数据。实发工资=应付工资合计-扣除社保-应交个人所得税

7、  右键单击工作表名，选择“移动或复制工作表”，建立副本，放在sheet2前面，并重命名为“分类汇总”

8、  对分类汇总工作表进行排序，按“部门”，然后再选择“分类汇总”按钮，设置分类为“部门”，方式为“求和”，求和项为应付工资合计，实发工资，最后选中每组数据不分页。

EXCEL第12套

知识点

1、查找函数： LOOKUP函数： LOOKUP(填充表里所对应的参照值，｛参照值所在数组｝，｛所需值所在数组｝)。数组元素之间用逗号隔开

第四个参数输入0或false，表示要进行精确查找（1或true表模糊查找）

取中间子串字符函数：mid(完整字符串，第几个开始，总共取几个)

详细制作步骤：

1、  打开“素材”，选择“文件”菜单-另存为，命名“年级期末成绩分析”

2、  在M、N、O列第二行分别输入：总分、平均分、年级排名。选中A1：O1，合并后居中，更改字体字号、选中数据区，选“开始”-“样式”-套用表格样式-“表样式中等深浅 15”，对齐设为水平居中，垂直居中。再设D到N列的数据区为数值1位小数，O列为数值0位小数。

3、  利用SUM函数计算总分，Average函数计算平均分，Rank函数计算排名。选中各科成绩区，使用样式中的条件格式，小于60的用黄色填充红色文本。

4、  使用LOOKUP函数进行班级的计算。第一个参数使用MID函数。第二个参数使用数组｛“01”， “02”， “03”， “04”｝，第三个参数也使用数组{"法律一班","法律二班","法律三班","法律四班"}。因为使用了组合函数，所以不能按回车确认，而要按CTRL+ALT+ENTER组合键进行确认。

5、  选择插入-数据透视表，选择新工作表。依次选择字段，将求和项的值字段设置全部改成平均值。选中数据透视表的数据区，套用表格样式“表样式中等深浅 15”，再设对齐方式为水平居中，垂直居中，数据类型为数值1位小数。

6、  选中班级及各科平均成绩，插入=图表-簇状柱形图，创建图表，将图表移至A10:H30的区域。

EXCEL第13套

知识点：

条件格式设置

排序：按关键字、按关键字颜色排序

函数：VLOOKUP()   SUMIFS()

迷你图

数据透视表：日期筛选，按季度、年统计

1、  使用VLOOKUP函数，根据图书名称填充图书编号

2、  先按订单编号升序排序，再设条件格式：先选中所有订单编号，再点条件格式---突出规则----重复值----紫色字体。最后再进行一次排序，排序依据按字体着颜色，次序选紫色

3、  使用SUMIFS函数分别统计2013年1-12月的每本书的销量。

如2月的：=SUMIFS(表1[销量（本）],表1[图书名称],[@图书名称],表1[日期],">=2013-2-1",表1[日期],"<2013-3-1")。通过复制此公式分别再计算其他月份的销量。各月份的日期范围不一样，要进行修改。

在最后一行输入汇总，再使用SUM函数统计各月的销量量。

4、  在最后一列插入-----迷你图----拆线图，选中高点、低点，数据区为1-12月的统计数据。

5、  对工作表“销售订单”进行插入---数据透视表----新工作表，将前两行删除，设行为书店名称，列为日期，汇总项为“销量”。在列标签中进行其他字段值筛选----日期-----介于-----2012-1-1到2012-12-31，再进行选项菜单，找到“将字段分组”。将工作表重命名为“2012年书店销量”

第14套

知识点：

导入数据：来自文本

工作表：重命名，标签颜色，新建，移动

?函数：VLOOKUP() , IF() , MID() , MOD() ,CONCATENATE()  &, RANK()  ，INT（）, TODAY()  公式计算

格式设置：行高列宽，格式刷

条件格式：第一名，前十名

页面布局：纸张，打印页在一页内?

取余函数：MOD(被除数，除数)  如判奇偶：MOD(数字,2)=1是奇数，=0是偶数。

?日期函数：TODAY(),获取今天的日期

?取整函数：INT（）：将一个数值的整数部分取出来。??

1、  打开学生成绩，在新建工作表，拖放至最左侧，重命名为“初三学生档案”，将标签颜色设置为“紫色”

2、  点“数据”菜单，获取外部数据—自文本，在弹出的对话框中，找到考生文件夹下的学生档案文本文件，文件的原始格式选择“Windows ANSI”，设身份证为文本格式，完成导入。

在第二列前插入一列，选中第一列，点菜单“数据”中的“分列”，固定宽度，用鼠标点拉设好分隔位置，进行分列。分完后将姓名剪切到第二列表头中。再选中所有的数据，复制到新建的工作表中，粘贴时选择“值”的粘贴方式。工作表命名为“档案”。

3、  性别列使用IF(),MID(),MOD()组合，从身份证号得出性别。

=IF(MOD(MID(C2,17,1),2)=1,"男","女")

出生年月用MID()函数得到：

=MID(C2,7,4)&"年"&MID(C2,11,2)&"月"&MID(C2,13,2)&"日"

年龄用int()和today()函数得到：

=INT((TODAY()-E2)/365)

调整行高列宽，居中对齐

4、  在姓名列使用VLOOKUP函数根据学生档案，将姓名进行填充，注意区域使用绝对引用。

=VLOOKUP(A2,档案!A1:B56,2，0)

在学期成绩列按照公式进行计算：等号开始，=平时*0.3+期中*0.3+期末*0.4

在班级名次列，使用排名函RANK()和字符连接函数CONCATENATE()实现，=CONCATENATE("第",RANK(F2,$F$2:$F$45,0),"名")

或者使用&符实现："第" &RANK(F2,$F$2:$F$45,0)&"名"

使用If（）函数嵌套实现期末总评：

=IF(F2>=102,"优秀",IF(F2>=84,"良好",IF(F2>=72,"及格","不合格")))

5、  选中语文的数据区，使用格式刷，按住CTRL键将其它各科的工作表选中，进行格式刷。再在选中状态下，进行行高设置22，列宽设为14。复制公式，再按住CTRL键选中其他各科工作表，粘贴公式，将其他工作表的对应单元格的姓名、学期成绩、班级名次、期末总评都进行填充。注意修改：其他各科的期末总评与语文数学的的条件值不同。

6、  使用VLOOKUP函数，将其他各科成绩填充到期末总成绩工作表中。注意，使用复制公式的方式，只需修改各工作表的名称即可。再使用求各函数SUM()计算总成绩，使用平均值函数AVERAGE()计算各科的平列值

使用排名函数RANK()计算总成绩排名。将所有的成绩选中，设置单元格格式为数值，两位小数

7、  选中语文列的成绩，使用“条件格式”-项目选取规则-值最大的10项-最大的1项，设置自定义：红色加粗字体，再双击格式刷，去分别刷其他各科的成绩数据。同样的方式设置总分前10的自定义格式为浅蓝色填充底纹。

8、页面布局：设纸张方向为横向，调整适当大小为宽度1页，高度1页

第15套

小赵是一名参加工作不久的大学生。他习惯使用Excel表格来记录每月的个人开支情况，在2013年底，小赵将每个月各类支出的明细数据录入了文件名为"开支明细表.xlsx"的Excel工作簿文档中。

    请你根据下列要求帮助小赵对明细表进行整理和分析：

    1. 在工作表"小赵的美好生活"的第一行添加表标题"小赵2013年开支明细表"，并通过合并单元格，放于整个表的上端、居中。

    在第一行输入标题“小赵2013年开支明细表”，合并后居中。

2. 将工作表应用一种主题，并增大字号，适当加大行高列宽，设置居中对齐方式，除表标题"小赵2013年开支明细表"外为工作表分别增加恰当的边框和底纹以使工作表更加美观。

页面布局—主题—选择一个主题。字号设大一点，行高、列宽设大点，对齐方式设为居中，单元格格式---边框、底纹。

    3. 将每月各类支出及总支出对应的单元格数据类型都设为"货币"类型，无小数、有人民币货币符号。

选种各月支出数据，设置单元格格式---货币---小数位0位。

    4. 通过函数计算每个月的总支出、各个类别月均支出、每月平均总支出；并按每个月总支出升序对工作表进行排序。

    5. 利用"条件格式"功能：将月单项开支金额中大于1000元的数据所在单元格以不同的字体颜色与填充颜色突出显示；将月总支出额中大于月均总支出110%的数据所在单元格以另一种颜色显示，所用颜色深浅以不遮挡数据为宜。

    6. 在"年月"与"服装服饰"列之间插入新列"季度"，数据根据月份由函数生成，例如：1至3月对应"1季度"、4至6月对应"2季度"……

用if函数或者Lookup函数进行计算

=IF(MONTH(A3)<4,"1季度",IF(MONTH(A3)<7,"2季度",IF(MONTH(A3)<10,"3季度","4季度")))

=LOOKUP(MONTH(A3),{1,4,7,10;"1","2","3","4"})&"季度"

    7. 复制工作表"小赵的美好生活"，将副本放置到原表右侧；改变该副本表标签的颜色，并重命名为"按季度汇总"；删除"月均开销"对应行。

    8. 通过分类汇总功能，按季度升序求出每个季度各类开支的月均支出金额。

    9. 在"按季度汇总"工作表后面新建名为"折线图"的工作表，在该工作表中以分类汇总结果为基础，创建一个带数据标记的折线图，水平轴标签为各类开支，对各类开支的季度平均支出进行比较，给每类开支的最高季度月均支出值添加数据标签。

EXCEL第16套

  1. 将"素材.xlsx"文件另存为"停车场收费政策调整情况分析.xlsx"，所有的操作基于此新保存好的文件。

    2. 在"停车收费记录"表中，涉及金额的单元格格式均设置为保留2位的数值类型。依据"收费标准"表，利用公式将收费标准对应的金额填入"停车收费记录"表中的"收费标准"列；利用出场日期、时间与进场日期、时间的关系，计算"停放时间"列，单元格格式为时间类型的"XX时XX分"。

选中收费标准、收费金额、拟收费金额、差值的数据区，设置数值类型保留2位小数。

使用Vlookup函数计算"收费标准"

收费标准=VLOOKUP(C2,收费标准!A$3:B$5,2,FALSE)

停放时间=IF(I2>G2,I2-G2,I2+24-G2)，设置时间格式为"XX时XX分"。

（注意：I2+24-G2，表示进入第二天，需要加上24小时，否则时间为负数）

3. 依据停放时间和收费标准，计算当前收费金额并填入"收费金额"列；计算拟采用的收费政策的预计收费金额并填入"拟收费金额"列；计算拟调整后的收费与当前收费之间的差值并填入"差值"列。

CEILING(数值，1)：表示对数值进行向上取整。INT(数值)：仅保留整数部分，即向下取整

收费金额=Celling((HOUR(J2)*60+MINUTE(J2))/15,1)*E2

(=CEILING((HOUR([@停放时间])*60+MINUTE([@停放时间]))/15,1)*[@收费标准])

拟收费金额=INT((HOUR(J2)*60+MINUTE(J2))/15)*E2

差值=K2-L2

    4. 将"停车收费记录"表中的内容套用表格格式"表样式中等深浅12"，并添加汇总行，最后三列"收费金额"、"拟收费金额"和"差值"汇总值均为求和。

在数据表下方最后一行输入“汇总”，并用SUM()对"收费金额"、"拟收费金额"和"差值"进行求和运算

    5. 在"收费金额"列中，将单次停车收费达到100元的单元格突出显示为黄底红字的货币类型。

选中"收费金额"列的数据，设置条件格式-à=100---黄底红字的货币类型

6. 新建名为"数据透视分析"的表，在该表中创建3个数据透视表，起始位置分别为A3、A11、A19单元格。第一个透视表的行标签为"车型"，列标签为"进场日期"，求和项为"收费金额"，可以提供当前的每天收费情况；第二个透视表的行标签为"车型"，列标签为"进场日期"，求和项为"拟收费金额"，可以提供调整收费政策后的每天收费情况；第三个透视表行标签为"车型"，列标签为"进场日期"，求和项为"差值"，可以提供收费政策调整后每天的收费变化情况。

先新建一个工作表，重命名为"数据透视分析"。再对"停车收费记录"表的数据创建三个数据透视分析表，

插入---数据透视分析表---现有工作表---起始位置为A3，行为"车型"，列为"进场日期"，求和项为"收费金额"。

插入---数据透视分析表---现有工作表---起始位置为A11，行为"车型"，列为"进场日期"，求和项为"拟收费金额"。

插入---数据透视分析表---现有工作表---起始位置为A19，行为"车型"，列为"进场日期"，求和项为"差值"。

EXCEL第17套

1. 打开"Excel_素材.xlsx"文件，将其另存为"Excel.xlsx"，之后所有的操作均在"Excel.xlsx"文件中进行。

2. 在"订单明细"工作表中，删除订单编号重复的记录(保留第一次出现的那条记录），但须保持原订单明细的记录顺序。

数据---删除重复项

 3. 在"订单明细"工作表的"单价"列中，利用VLOOKUP公式计算并填写相对应图书的单价金额。图书名称与图书单价的对应关系可参考工作表"图书定价"。

单价=VLOOKUP([@图书名称],表2,2,FALSE)

     4. 如果每订单的图书销量超过40本（含40本），则按照图书单价的9.3折进行销售；否则按照图书单价的原价进行销售。按照此规则，计算并填写"订单明细"工作表中每笔订单的"销售额小计"，保留2位小数。要求该工作表中的金额以显示精度参与后续的统计计算。

销售额小计=IF(F3>=40,E3*0.93*F3,E3*F3)

精度设置：文件---选项---高级---将精度设为所显示的精度

 5. 根据"订单明细"工作表的"发货地址"列信息，并参考"城市对照"工作表中省市与销售区域的对应关系，计算并填写"订单明细"工作表中每笔订单的"所属区域"。

所属区域=VLOOKUP(MID([@发货地址],1,3),表3,2,FALSE)

     6. 根据"订单明细"工作表中的销售记录， 分别创建名为"北区"、"南区"、"西区"和"东区"的工作表，这4个工作表中分别统计本销售区域各类图书的累计销售金额，统计格式请参考"Excel_素材.xlsx"文件中的"统计样例"工作表。将这4个工作表中的金额设置为带千分位的、保留两位小数的数值格式。

依据"订单明细"工作表中的销售记录，插入数据透视表，在新工作表，报表筛选字段为"所属区域"，行为“图书名称”，数据为“销售额小计”。更改名称“行标签”为“图书名称”，销售额，所属地区选择为“北区”。重复复制工作表“北区”，依次改名为南区、西区、东区，筛选字段也依次选择为南区、西区、东区。

     7. 在"统计报告"工作表中，分别根据"统计项目"列的描述，计算并填写所对应的"统计数据"单元格中的信息。

2013年所有图书订单的销售额

=SUMIFS(订单明细!I3:I636,订单明细!B3:B636,">="&DATE(2013,1,1),订单明细!B3:B636,"<="&DATE(2013,12,31))

《MS Office高级应用》图书在2012年的总销售额

=SUMIFS(订单明细!I3:I636,订单明细!B3:B636,">="&DATE(2012,1,1),订单明细!B3:B636,"<="&DATE(2012,12,31),订单明细!D3:D636,"《MS Office高级应用》")

隆华书店在2013年第3季度（7月1日~9月30日）的总销售额

=SUMIFS(订单明细!I3:I636,订单明细!B3:B636,">="&DATE(2013,7,1),订单明细!B3:B636,"<="&DATE(2013,9,30),订单明细!C3:C636,"隆华书店")

隆华书店在2012年的每月平均销售额（保留2位小数）

=SUMIFS(订单明细!I3:I636,订单明细!B3:B636,">="&DATE(2012,1,1),订单明细!B3:B636,"<="&DATE(2012,12,31),订单明细!C3:C636,"隆华书店")/12

2013年隆华书店销售额占公司全年销售总额的百分比（保留2位小数）

=SUMIFS(订单明细!I3:I636,订单明细!B3:B636,">="&DATE(2013,1,1),订单明细!B3:B636,"<="&DATE(2013,12,31),订单明细!C3:C636,"隆华书店")/SUMIFS(订单明细!I3:I636,订单明细!B3:B636,">="&DATE(2013,1,1),订单明细!B3:B636,"<="&DATE(2013,12,31))

 EXCEL第18套

   1. 将"素材.xlsx"另存为"成绩分析.xlsx"的文件，所有的操作基于此新保存好的文件。

    2. 在"法一"、"法二"、"法三"、"法四"工作表中表格内容的右侧，分别按序插入"总分"、"平均分"、"班内排名"列；并在这四个工作表表格内容的最下面增加"平均分"行。所有列的对齐方式设为居中，其中"班内排名"列数值格式为整数，其他成绩统计列的数值均保留1位小数。

选中"法一"再按SHIFT键选中"法四"工作表，四个工作表同时操作以下步骤

在表格内容的右侧列分别输入"总分"、"平均分"、"班内排名"，在数据最下方一行输入"平均分"。

选中整个数据区，设置对齐方式设为居中，选中"班内排名"列数据，设数值格式0位小数，其他成绩统计列的设数值1位小数

3. 为"法一"、"法二"、"法三"、"法四"工作表内容套用"表样式中等深浅 15"的表格格式，并设置表包含标题。

选中数据内容，套用表格样式"表样式中等深浅 15"

    4. 在"法一"、"法二"、"法三"、"法四"工作表中，利用公式分别计算"总分"、"平均分"、"班内排名"列的值和最后一行"平均分"的值。对学生成绩不及格（小于60）的单元格突出显示为"橙色（标准色）填充色，红色（标准色）文本"格式。

总分=SUM(表1[@[英语]:[立法法]])

平均分=AVERAGE(C3:K3)

班内排名=RANK(M3,$M$3:$M$27,0)

最后一行"平均分" =AVERAGE(C3:C27)，再向右填充

条件格式：选中9 门课的成绩，设置小于60的单元格突出显示为"橙色（标准色）填充色，红色（标准色）文本"格式。

    5. 在"总体情况表"工作表中，更改工作表标签为红色，并将工作表内容套用"表样式中等深浅 15"的表格格式，设置表包含标题；将所有列的对齐方式设为居中；并设置"排名"列数值格式为整数，其他成绩列的数值格式保留1位小数。

选中"总体情况表"工作表中，右键---工作表标签颜色---红色，选中数据区，套用"表样式中等深浅 15"的表格格式，对齐方式设为居中；并设置"排名"列数值格式为整数，其他成绩列的数值格式保留1位小数。

    6. 在"总体情况表"工作表B3:J6单元格区域内，计算填充各班级每门课程的平均成绩；并计算"总分"、"平均分"、"总平均分"、"排名"所对应单元格的值。

使用平均函数AVERAGE(法一!D3:D27)，计算法一的英语平均成绩，再向右填充，得到其它各科成绩。同样的方法计算其它班级的各科平均成绩。

总分=SUM(B3:J3)，得用SUM函数对左侧各科求各得到。

平均分= AVERAGE(表7[@[英语]:[立法法]])

排名=RANK(L3,$L$3:$L$6,0)

    7. 依据各课程的班级平均分，在"总体情况表"工作表A9:M30区域内插入二维的簇状柱形图，水平簇标签为各班级名称，图例项为各课程名称。

选中班级和名科成绩的数据区，插入---图表---簇状柱形图，将图放在A9:M30区域

    8. 将该文件中所有工作表的第一行根据表格内容合并为一个单元格，并改变默认的字体、字号，使其成为当前工作表的标题。

选中第一行的A1：M1，合并后居中，合并单元格，更改字体、字号。

    9. 保存"成绩分析.xlsx"文件。

EXCEL第19套

1. 打开考生文件夹下的工作簿"Excel素材.xlsx"，将其另存为"一二季度销售统计表.xlsx"，后续操作均基于此文件。

    2. 参照"产品基本信息表"所列，运用公式或函数分别在工作表"一季度销售情况表"、"二季度销售情况表"中，填入各型号产品对应的单价，并计算各月销售额填入F列中。 其中单价和销售额均为数值、保留两位小数、使用千位分隔符。（注意：不得改变这两个工作表中的数据顺序）

选中两工作表"一季度销售情况表"、"二季度销售情况表"，使用VLOOKUP函数计算单价

单价=VLOOKUP(B2,产品基本信息表!$B$2:$C$21,2,0)，设置格式为数值、保留两位小数、使用千位分隔符。

销售额=D2*E2  设置格式为数值、保留两位小数、使用千位分隔符。

    3. 在"产品销售汇总表"中，分别计算各型号产品的一、二季度销量、销售额及合计数，填入相应列中。所有销售额均设为数值型、小数位数0，使用千位分隔符，右对齐。

一季度销量=SUMIFS(一季度销售情况表!$D$2:$D$44,一季度销售情况表!$B$2:$B$44,B2)

一季度销量额=SUMIFS(一季度销售情况表!$F$2:$F$44,一季度销售情况表!$B$2:$B$44,B2) ，设置格式数值型、小数位数0，千位分隔符，右对齐。

二季度销量=SUMIFS('二季度销售情况表 '!$D$2:$D$43,'二季度销售情况表 '!$B$2:$B$43,B2)

二季度销量额=SUMIFS('二季度销售情况表 '!$F$2:$F$43,'二季度销售情况表 '!$B$2:$B$43,B2)   设置格式数值型、小数位数0，千位分隔符，右对齐。

一二季度销量总量=SUM(C2,E2)   或者=C2+E2

一二季度销售总额=SUM(D2,F2)   或者=D2+F2   设置格式数值型、小数位数0，千位分隔符，右对齐。

    4. 在"产品销售汇总表"中，在不改变原有数据顺序的情况下，按一二季度销售总额从高到低给出销售额排名，填入I列相应单元格中。将排名前3位和后3位的产品名次分别用标准红色和标准绿色标出。

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

设置条件格式：前3位即 <4 , 字体 标准红色 （或者“项目选取规则”-值最小的 3）

              后3位即 >17, 字体 标准绿色 （或者“项目选取规则”-值最大的 3）

    5. 为"产品销售汇总表"的数据区域A1:I21套用一个表格格式，包含表标题，并取消列标题行的筛选标记。

选中"产品销售汇总表"的数据区A1:I21套用表格样式，点击数据—筛选，即取消筛选标记

    6. 根据"产品销售汇总表"中的数据，在一个名为"透视分析"的新工作表中创建数据透视表，统计每个产品类别的一、二季度销售及总销售额，透视表自A3单元格开始、并按一二季度销售总额从高到低进行排序。结果参见文件"透视表样例.png"。

选中"产品销售汇总表"的数据区，插入---数据透视表---新工作表（重命名“透视分析”）--行为“产品类别代码”，数值为：一季度销售额、二季度销售额、一二季度销售总额。

单击“一二季度销售总额”的数据，选择数据---排序---降序

7. 将"透视分析"工作表标签颜色设为标准紫色，并移动到"产品销售汇总表"的右侧。

右键工作表名字，设置工作表标签颜色设为标准紫色，拖动“透视分析”工作表到到"产品销售汇总表"的右侧。

EXCEL第20套

1. 将"素材.xlsx"另存为"滨海市2015年春高二物理统考情况分析.xlsx"文件。

2. 利用"成绩单"、"小分统计"和"分值表"工作表中的数据，完成"按班级汇总"和"按学校汇总"工作表中相应空白列的数值计算。具体提示如下：

    (1)"考试学生数"列必须利用公式计算，"平均分"列由"成绩单"工作表数据计算得出；

方法一：公式计算（下列所有组合函数，均以Ctrl+Shift+Enter确认结束）

考试学生数=COUNTIFS(成绩单!$A$2:$A$950,A2,成绩单!$B$2:$B$950,B2)

最高分=MAX(IF((成绩单!$A$2:$A$950=A2)*(成绩单!$B$2:$B$950=B2),(成绩单!$D$2:$D$950)))

最低分=MIN(IF((成绩单!$A$2:$A$950=A2)*(成绩单!$B$2:$B$950=B2),(成绩单!$D$2:$D$950)))

平均分=AVERAGE(IF((成绩单!$A$2:$A$950=A2)*(成绩单!$B$2:$B$950=B2),(成绩单!$D$2:$D$950)))

方法二：数据透视表+复制

将工作表“成绩单”复制到最后，创建数据透视表，行为“班级”，列为“学校”，数据为“物理”

考试学生数：将数据透视表的数值的值字段设置为“计数”，然后复制人数到对应单元格

最高分：将数据透视表的数值的值字段设置为“最大值”，然后复制分数到对应单元格

最低分：将数据透视表的数值的值字段设置为“最小值”，然后复制分数到对应单元格

平均分：将数据透视表的数值的值字段设置为“平均值”，然后复制分数到对应单元格

    (2)"分值表"工作表中给出了本次考试各题的类型及分值。（备注：本次考试一共50道小题，其中【1】至【40】为客观题，【41】至【50】为主观题）；

    (3)"小分统计"工作表中包含了各班级每一道小题的平均得分，通过其可计算出各班级的"客观题平均分"和"主观题平均分"。（备注：由于系统生成每题平均得分时已经进行了四舍五入操作，因此通过其计算"客观题平均分"和"主观题平均分"之和时，可能与根据"成绩单"工作表的计算结果存在一定误差）；

客观题平均分=SUM(小分统计!C2:AP2)

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

    (4)利用公式计算"按学校汇总"工作表中的"客观题平均分"和"主观题平均分"，计算方法为：每个学校的所有班级相应平均分乘以对应班级人数，相加后再除以该校的总考生数；

考试学生数=SUMIFS(按班级汇总!$C$2:$C$33,按班级汇总!$A$2:$A$33,A2)

最高分=MAX(IF((按班级汇总!$A$2:$A$33=A2),(按班级汇总!$D$2:$D$33)))

最低分=MIN(IF((按班级汇总!$A$2:$A$33=A2),(按班级汇总!$E$2:$E$33)))

平均分=AVERAGE(IF((按班级汇总!$A$2:$A$33=A2),(按班级汇总!$F$2:$F$33)))

客观题平均分==SUM((按班级汇总!$A$2:$A$33=A2)*(按班级汇总!$C$2:$C$33)*(按班级汇总!$G$2:$G$33))/B2

主观题平均分==SUM((按班级汇总!$A$2:$A$33=A2)*(按班级汇总!$C$2:$C$33)*(按班级汇总!$H$2:$H$33))/B2

同样可用数据透视表做，然后复制数据。行为学校，数据依次为考试学生数（求和），最高分（最大值），最低分（最小值），平均分（平均值）

    (5)计算"按学校汇总"工作表中的每题得分率，即：每个学校所有学生在该题上的得分之和除以该校总考生数，再除以该题的分值。

【1】得分率=SUM((按班级汇总!$A$2:$A$33=$A$2)*(小分统计!C$2:C$33)*(按班级汇总!$C$2:$C$33))/($B$2*分值表!B$3)

将【1】得分率算出后向下填充，依次改变公式中的学校和人数单元格。如滨海市第二中学的【1】得分率，将上面公式中的$A$2改成$A$3，$B$3改成$B$3。

（注意点：小分统计!C$2:C$33和分值表!B$3，使用相对引用，行固定，列随着填充相对引用，可以用填充方式实现后面各题的得分率）

然后将四个中学的【1】得分率选中，向右填充，计算各学校【2】-【50】得分率

    (6)所有工作表中"考试学生数"、"最高分"、"最低分"显示为整数；各类平均分显示为数值格式，并保留2位小数；各题得分率显示为百分比数据格式，并保留2位小数。

选中"按学校汇总"工作表中"考试学生数"、"最高分"、"最低分"，设置单元格格式为数值，0位小数。平均分保留2位小数，各题得分率显示为百分比数据格式，保留2位小数。

3. 新建"按学校汇总2"工作表，将"按学校汇总"工作表中所有单元格数值转置复制到新工作表中。

新建工作，重命名为"按学校汇总2"，，将"按学校汇总"工作表中数据选中（可用Ctrl+A全选），复制，在新工作表右键---粘贴---选择性粘贴，在弹出的对话框中选中“值和数字格式”和“转置”。

 4. 将"按学校汇总2"工作表中的内容套用表格样式为"表样式中等深浅12"；将得分率低于80%的单元格标记为"浅红填充色深红色文本"格式，将介间的单元格标记为"黄填充色深黄色文本"格式。

选中"按学校汇总2"工作表中的内容，套用表格样式为"表样式中等深浅12"，再选中得分率的数据区，选择条件格式---突出单元格规则---小于---80%---"浅红填充色深红色文本"，选择条件格式---突出单元格规则---介于---80%和90% ---黄填充色深黄色文本