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

25

2017
03

二级MSOffice高级应用——Excel解析1-5套

EXCEL第1 套

字符连接函数：CONCATENATE(“第一串字符”，”第二串符”)，相当于& 符号。如CONCATENATE(“ABCD”，”123”)= “ABCD”&”123”=”ABCD123”

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

如MID(“ABCDEF”，3，3)=”CDE”.

详细步骤

1、选中学号列的数据，右键-设置单元格格式-数据-文本。选中所有的成绩列的数据，设为数值，两位小数。选中所有数据列，设置行高15，列宽9，字体黑体，12号，居中对齐，加边框和填充底纹。

2、选中语文、数学、英语三列的数据，设条件格式，大于等于110（>=110）的用浅色填充，再选中其他四科的数据，设条件格式，大于95的用蓝色文本。

3、 Sum函数求和，计算总分，average函数求平均成绩。

4、使用CONCATENATE函数进行字符连接，第一个参数使用MID函数，取出班级数，第二个参数输入“班”，如下图所示

5、右键工作表-复制或移动工作表，勾选建立副本，对工作表进行复制，并重命名为分类汇总，设工作表标签颜色为红色。

6、对数据按班级进行排序，排序后进行分类汇总。分类为班级，汇总方式为平均值，汇总项为各科成绩，勾选每组数据分页。

7、选中分类的结果，插入图表-簇状柱形图，并将图表剪切到新工作表中，将新工作表重命名为“柱状分析图”

EXCEL第2套

多条件求和函数：SUMIFS函数

SUMIFS(求和的区域，条件1的区域，条件1的值，条件2的区域，条件2的值)

日期函数：date(年，月，日)

1、选中数据区，套用格式样式，再选中单价和小计列，设置单元格格式，数字设为会计专用

2、利用VLOOKUP函数完成图书名称的填充

3、利用VLOOKUP函数完成图书名称的填充

4、单击小计列，利用公式计算：=F3*G3，小计=单价*数量

5、在“订单明细”工作表中，使用SUM函数计算小计列的总销售金额。

6、使用SUMIFS函数计算2012年的销售额：=SUMIFS(订单明细表!H3:H636,订单明细表!B3:B636,">="&DATE(2012,1,1),订单明细表!B3:B636,"<="&DATE(2012,12,31),订单明细表!E3:E636,"《MS Office高级应用》")

,">="&DATE(2012,1,1),：>=使用双引号，是因为条件值都默认为字符型，&是连接符，用来连接字符的符号。Date(年，月，日)日期函数，得到一个年月日的格式，也是字符型，故不需再使用双引号

7、使用SUMIFS函数计算隆华书店2011年第3季度的销售额：=SUMIFS(表_5[小计],表_5[日期],">="&DATE(2011,7,1),表_5[日期],"<="&DATE(2011,9,30),表_5[书店名称],"隆华书店")

8、使用SUMIFS函数计算隆华书店2011年年度的销售额，再修改公式除12得到每月平均销售额，设置数值保留两2小数。

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

第2套

多条件求和函数：SUMIFS函数

SUMIFS(求和的区域，条件1的区域，条件1的值，条件2的区域，条件2的值)

日期函数：date(年，月，日)

9、选中数据区，套用格式样式，再选中单价和小计列，设置单元格格式，数字设为会计专用

10、利用VLOOKUP函数完成图书名称的填充

11、利用VLOOKUP函数完成图书名称的填充

12、单击小计列，利用公式计算：=F3*G3，小计=单价*数量

13、在“订单明细”工作表中，使用SUM函数计算小计列的总销售金额。

14、 使用SUMIFS函数计算2012年的销售额：=SUMIFS(订单明细表!H3:H636,订单明细表!B3:B636,">="&DATE(2012,1,1),订单明细表!B3:B636,"<="&DATE(2012,12,31),订单明细表!E3:E636,"《MS Office高级应用》")

15、 使用SUMIFS函数计算隆华书店2011年第3季度的销售额：=SUMIFS(表_5[小计],表_5[日期],">="&DATE(2011,7,1),表_5[日期],"<="&DATE(2011,9,30),表_5[书店名称],"隆华书店")

16、使用SUMIFS函数计算隆华书店2011年年度的销售额，再修改公式除12得到每月平均销售额，设置数值保留两2小数。

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

第3套

知识点

数据透视表

图表

1、打开文件，将sheet1重命名为“销售情况”，sheet2右键重命名为“平均单价”

2、右键单击A列，插入列，在A3输入“序号”，在A4输入“‘001”，并进行填充。

3、合并A1：F2，修改字体字号和颜色。选中数据区，修改行高列宽。对齐方式为水平居中。右键设置边框和底纹。选中销售额列，设置数值2位小数。

4、选中B3：C7，在名称框输入“商品均价”。使用VLOOKUP函数进行计算。再修改公式，在函数后输入*E4

5、选中数据区，插入-数据透视表，新的工作表，分别设置字段，行为店铺，列为季度，商品名称为报表筛选，销售额求和。并对数据透视表套用表格样式。将筛选字段选择为“笔记本”。重命名工作表为“数据透视分析”

6、选中数据透视表的A4：E8，插入-图表-簇状柱形图，移动图表位置在透视表下方。选择商品名称中的笔记本。

EXCEL第4套

知识点

1、多表综合运算：在计算一个表中的某列数据时，需要引用另一张表的数据。

2、纵向查找函数：VLOOKUP函数：是按列查找，最终返回该列所需查询列序所对应的值；功能：对照参照值，从参照表中，将所需的数据找出来，

VLOOKUP（填充表里所对应的参照列，参照表的数据区，所需数据在所选参照区的第几列，false）。第四个参数输入0或false，表示要进行精确查找（1或true表模糊查找）

详细制作步骤：

1、在工作表“一季度销售情况表”中，单击D2单元格，选择函数vlookup，在弹出的框中，设置如下图：第一个参数选择表“一季度销售情况表”的产品型号列的B2单元格，第二个参数选择表“产品基本信息表“的B1：C21区域，第三个参数输入所需值在所选参照区的第几列，即数字2，第四个参数输入0或false。确定后得出结果，再选中计算出来的数据，右键-单元格格式，选数值，小数位数为0。双击填充柄，实现整列数据的填充。