水冰阁-二级MSOffice高级应用—

25

2017
03

二级MSOffice高级应用——Excel解析6-10套

第6套

知识点：

1、工作表：重命名，

2、计算：函数：VLOOKUP，公式

3、格式：单元格，行高列宽，套用样式

详细步骤

1、右键sheet1重命名，“7月份计算机图书销售情况”，sheet2重命名“8月份计算机图书销售情况”/

2、单击B3单元格，插入函数VLOOKUP，参数设置如下。第一个参数选图书编号列的A3，第二个参数选工作表“图书销售汇总统计”的数据区，使用绝对引用，第三个参数输入所需数据在所选区域内列数2，第四个参数为0或false。确定后双击填充。

3、同2一样的方法计算单价列。

4、利用自定义公式计算销售额：销售额=单价*销售量

5、选中工作表“图书销售汇总统计”的单价与总销售额两例，右键选“单元格格式”，选数值型，两位小。从开始-格式中设置行高列宽。改字体为黑体，字号为12号，居中对齐。再选择数据区，开始-格式-样式-套用表格样式

6、单击单元格D3，插入SUM函数，第一个参数为7月的销售额，第二个参数为8月的销售额，确定后再填充。

第7套

知识点

1、行高列宽，单元格设置

2、 VLOOKUP函数，RANK函数

3、公式计算

4、分类汇总

5、图表

详细步骤

1、选中“SUV销售统计”工作表的数据区，设置字体为楷体，字号为12号，行高列宽16，居中对齐。右键设置单元格格式，设内外边框，选一种填充色。

2、单击E3单元格，插入函数，VLOOKUP，第一个参数选A3单元格，第二个参数选“汽车报价”工作表中数据区，并将区域设为绝对引用。第三个参数输入3，第四个参数输入0或false。如下图。再双击填充柄进行自动填充。将所有单价选中，右键，设置单元格格式，选数值型，保留两位小数。

3、单击F3单元格，输入=号，再点D3，输入*号，再点E3，实现销售额=11月销量*单价的公式计算，确定后设置数值数，保留2位小数。双击填充

4、单击G3，插入函数RANK，第一个参数选F3，第二个参数选销售额的数据区，使用绝对引用，确定。再双击填充。

5、右键选中工作表“SUV销售统计”，选择“移动和复制”，勾选“建立副本”，在汽车报价表前，再重命名为“分类汇总”

6、选对数据按品牌进行排序，再选择“数据”-“分类汇总”，分类字段选“品牌”，汇总方式选“平均值”，选定汇总项选“销售额”，勾选“每组数据分页”

7、选择分类汇总的等级2，选择“所属品牌”和“销售额”两列的数据，从“插入”-柱形图-簇状柱形图，建立图表。将图表选中，剪切，再在“汽车报价”表后的“sheet3”中粘贴出来。对“sheet3”改名为“品牌车销售额图表分析图”

第8套

知识点

1、工作表操作：重命名，标签颜色，复制

2、函数：SUM,RANK

3、分类汇总

4、图表

详细步骤

1、右键“sheet1”选择重命名，

2、选中A1：K1，点合并后居中，设为黑体，16号，改成浅绿色。再选中数据区，从“开始“-“格式”中去设置行高列宽。右键数据区，点单元格格式，设内外边框，选一种填充色。

3、点单元格J3，插入函数sum，对左边数据进行求和。再双击填充。点单元K3，插入函数RANK，第一个参数为J3，第二个参数为总销售额列的数据，使用绝对引用。

4、右键工作表选择“移动或复制”，勾选“建立副本”，在sheet2之前，确定后重命名为“分类汇总”。

5、对数据区按部门进行排序，再点数据-分类汇总

6、选中分类汇总的2级汇总结果，插入图表-簇状圆柱图，剪切图表粘贴到sheet3,重命名为“图表分析”

第九套

匹配函数MATCH()：MATCH(指定的需进行匹配的值，匹配的区域，匹配方式)，结果得到匹配值在这个区域是第几个元素的数字

匹配方式：1（或省略）、0、-1。

1（或省略）：查找小于或等于指定内容的最大值，且指定区域必须按升序排列

0：进行精确匹配

-1：查找大于或等于指定内容的最小值，且指定区域必须按降序排列

函数INDEX：INDEX(需要引用的区域，引用的值在第几行，引用的值所在列数)，结果得到区域中指定行对应单元格里的内容（或指定列的内容）

★★★★注意：组合函数确认时不能按回车，而要按组合键：ALT+CTRL+ENTER

=INDEX(比较数据!A2:A34,MATCH(MIN(IF((比较数据!A2:A34="中国人民解放军现役军人")+(比较数据!A2:A34="难以确定常住地"),FALSE,比较数据!B2:B34)),比较数据!B2:B34,0))

????1、在考生文件夹中新建EXCEL文件，命名为“全国人口普查数据分析”，sheet1重命名为“第五次普查数据”，sheet2重命名为“第六次普查数据”

???2、点”数据”-“现有连接”，打开如下对话框，选“浏览更多”，打开网页“第五次全国人口普查公报”，找到表，点表前面的箭头，然后单击“导入”。同样的方法做第六次的???????

???3、分别对两个工作表进行表格样式套用，设置人口列数据为“数值”-千分位，小数位为0

?4、将第五次普查数据复制到sheet3(不带格式粘贴，即粘贴值)，再输入两个标题，在对应列下，使用VLOOKUPB函数，实现2010年人口数和2010年比重的填充。????

5、在数据右边增加两列“人口增长数”和“比重变化”，手动公式计算：人口增长数=2010年人口数-2000年人口数，比重变化=2010年比重-2000年比重

6、打开“统计指标”右键工作表名-移动或复制-建立副本-复制到“全国人口普查数据分析”工作表的最后

7、计算“统计数据”：

2000年的总人数=SUM(比较数据!B2:B34)

2010年的总人数=SUM(比较数据!D2:D34)

总增长人数：?=SUM(比较数据!F2:F34)?

2000年人口最多的地区：=INDEX(比较数据!A2:A34,MATCH(MAX(比较数据!B2:B34),比较数据!B2:B34,0))

2010年人口最多的地区：=INDEX(比较数据!A2:A34,MATCH(MAX(比较数据!D2:D34),比较数据!D2:D34,0))

2000年人口最少的地区：=INDEX(比较数据!A2:A34,MATCH(MIN(IF((比较数据!A2:A34="中国人民解放军现役军人")+(比较数据!A2:A34="难以确定常住地"),FALSE,比较数据!B2:B34)),比较数据!B2:B34,))

2010年人口最少的地区：=INDEX(比较数据!A2:A34,MATCH(MIN(IF((比较数据!A2:A34="中国人民解放军现役军人")+(比较数据!A2:A34="难以确定常住地"),FALSE,比较数据!D2:D34)),比较数据!D2:D34,))

人口增长最多的地区：=INDEX(比较数据!A2:A34,MATCH(MAX(比较数据!F2:F34),比较数据!F2:F34,))

人口增长最少的地区：=INDEX(比较数据!A1:A34,MATCH(MIN(IF((比较数据!A1:A34="中国人民解放军现役军人")+(比较数据!A1:A34="难以确定常住地"),FALSE,比较数据!F1:F34)),比较数据!F1:F34,))

人口增长为负的地区数：=COUNTIFS(比较数据!A2:A34,"<>中国人民解放军现役军人",比较数据!A2:A34,"<>难以确定常住地",比较数据!F2:F34,"<0")

8、点击“比较数据”表，“插入“－“数据透视表”－“新工作表”，创建数据透视表，命名为“透视分析”，行设为“地区”，数值为“2010年人口数、2010年比重、人口增长数”，然后再点“行标签右边的”小按钮，选择 “值筛选”中设置2010年人口数>5000。再选其他排序选项中的“降序”，按人口数降序排序

注意：透视分析表一定要放在所有工作表的最后

第2，12，9，4，10 ，11，14套

第10套

知识点

1、 日期后加星期几：

格式：yyyy”年”m”月”d”日” aaaa aaaa表示中文的星期几，aaa是不星期两个字，如“日”，dddd表示英语如SUNDY，ddd表“SUN”

2、判断星期几的函数：Weekday(日期数字，数字表示方式)

数字表示方式 1：星期日为1，星期一为2，星期六为7

2：星期一为2，星期六为6，星期日为7

3：星期一为0，星期六为5，星期日为6

3、取左函数Left()：left（完整字符，取几个），如left(“ABCDEF”,3)=”ABC”

4、按行查找VLOOKUP（）函数

5、多条件求和SUMIFS（）函数

详细步骤

1、选中日期列的数据，右键－设置单元格格式，自定义，格式为:yyyy”年”m”月”d”日” aaaa，可以将日期后面带上星期几

2、If函数和weekday函数结合实现周末的判断，=IF(WEEKDAY(A3,2)>5,"是","否")，插入函数if，在第一个参数中输入weekday(A3,2)>5，即用weekday()函数判断选定日期的值是不是大于5，参数2表示的用数字1表示星期一，周末星期六为6、星期日为7，均大于5

3、使用取左子串函数left()实现省市的选择，=LEFT(C3,3)，第二个参数3表示从选定字符是取前面3个字

4、使用vlookup函数实现费用类别的填充，第一个参数为费用类别编号，第二个参数为费用类别的数据区，第三个参数为费用类别所在的列数，第四个参数为FALSE进行精确比较

5、使用SUMIFS()函数计算2013年第二季度发生在北京市的差旅费用金额总计，第一个参数为所有的差旅费用，假设第一个条件是北京市，则第二个参数为条件1所在区域，即北京市所在那列的数据，第三个参数为第一个条件的值，即北京市，假设第二个条件是第二季度的开始，第二季度是从2013年4月1日到2013年6月30号，所以第四个参数为条件2所在区域，即所有的日期，第五个参数为条件值，即 “>=”&date(2013,4,1) ，第六个参数为所有的日期，第七个参数为条件件值，即 “<=”&date(2013,6,30)