25
2017
03

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

6

知识点:

1、工作表:重命名,

2、计算:函数:VLOOKUP,公式

3、格式:单元格,行高列宽,套用样式

详细步骤

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

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

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,第四个参数输入0false。如下图。再双击填充柄进行自动填充。将所有单价选中,右键,设置单元格格式,选数值型,保留两位小数。

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、  选中A1K1,点合并后居中,设为黑体,16号,改成浅绿色。再选中数据区,从“开始“-“格式”中去设置行高列宽。右键数据区,点单元格格式,设内外边框,选一种填充色。

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

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

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

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

第九套

 

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

匹配方式:1(或省略)、0-1

1(或省略):查找小于或等于指定内容的最大值,且指定区域必须按升序排列

0:进行精确匹配

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

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

★★★★注意:组合函数确认时不能按回车,而要按组合键: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再选其他排序选项中的“降序”按人口数降序排序

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



2129410 1114套

10

知识点

1、             日期后加星期几:

格式:yyyy””m””d”” aaaa  aaaa表示中文的星期几,aaa是不星期两个字,如“日”,dddd表示英语如SUNDYddd表“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,可以将日期后面带上星期几

2If函数和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所在区域,即北京市所在那列的数据,第三个参数为第一个条件的值,即北京市,假设第二个条件是第二季度的开始,第二季度是从201341日到2013630号,所以第四个参数为条件2所在区域,即所有的日期,第五个参数为条件值,即 “>=”&date(2013,4,1) ,第六个参数为所有的日期,第七个参数为条件件值,即 “<=”&date(2013,6,30)

=SUMIFS(费用报销管理!G3:G401,费用报销管理!A3:A401,">="&DATE(2013,4,1),费用报销管理!A3:A401,"<="&DATE(2013,6,30),费用报销管理!D3:D401,费用报销管理!D11)

6、同样使用sumifs()函数计算2013年钱顺卓报销的火车票总计金额:

=SUMIFS(费用报销管理!G3:G401,费用报销管理!B3:B401,费用报销管理!B7)

7、先用sumifs()函数计算飞机票占所有报销费用,再手动输入除法符号,再次插入函数sum(),求各所有费用

2013年差旅费用金额中,飞机票占所有报销费用的比例为(保留2位小数)

=SUMIFS(费用报销管理!G3:G401,费用报销管理!F3:F401,费用报销管理!F3)/SUM(费用报销管理!G3:G401)

8、用sumifs()函数计算2013年发生在周末(星期六和星期日)中的通讯补助总金额:

=SUMIFS(费用报销管理!G3:G401,费用报销管理!H3:H401,费用报销管理!H3,费用报销管理!F3:F401,费用报销管理!F397)

« 上一篇下一篇 »

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。