浙江省计算机二级高级办公软件PPT+EXCEL大全

发布时间 : 星期五 文章浙江省计算机二级高级办公软件PPT+EXCEL大全更新完毕开始阅读

5. 对Sheet2中的“采购表”进行高级筛选。

a. 筛选条件为:“采购数量”>150,“折扣”>0; 采购数量 >150 折扣 >0 b. 将筛选结果保存在Sheet2中。

6. 根据Sheet1中的采购表,新建一个数据透视图Chart1,要求: a. 该图形显示每个采购时间点所采购的所有项目数量汇总情况; b. x坐标设置为“采购时间”; c. 求和项为采购数量;

d. 将对应的数据透视表保存在Sheet3中。

①选择所创建的数据透视表的数据源类型(会自动出默认)。

选:数据透视图(及数据透视表)(R) 下一步

重要: 对该题来说:出图后在Sheet3中把“项目”拖到“汇总”

13

(三)、书籍出版(闰年new)(数组公式if).xls

1. 使用数组公式,计算Sheet1中的订购金额,将结果保存到表中的 “金额”列当中。 ={=H2:H51*G2:G51}

2. 使用统计函数,对Sheet1中结果按以下条件进行统计,并将结果保存在Sheet1中的相应位置,要求:

a. 统计出版社名称为“高等教育出版社”的书的种类数; =COUNTIF(D2:D51,\高等教育出版社\

b. 统计订购数量大于110且小于850的书的种类数。 =COUNTIF(G2:G51,\

3. 使用函数计算,每个用户所订购图书所需支付的金额总数,将结果保存在Sheet1中的相应位置。 SUMIF(A2:A51,K8,I2:I51) 等等,等等

4. 使用函数,判断Sheet2中的年份是否为闰年,如果是,结果保存“闰年”,如果不是,则结果保存“平年”,并将结果保存在“是否为闰年”列中。

说明:闰年定义:能被400整除的年份,或者年数能被4整除而不能被100整除。 取除法后得余数的函数:MOD(被除数,除数) 方法:

IF( , \闰年\平年\OR( MOD(x,400)=0, ) AND( MOD(x,4)=0 ,MOD(x,100)<>0 )

成为:=IF(OR(MOD(A2,400)=0,AND(MOD(A2,4)=0,MOD(A2,100)<>0)),\闰年\平年\5. 将Sheet1复制到Sheet3中,对Sheet3进行高级筛选,要求:

a. 筛选条件为“订数>=500,且金额总数<=30000”;

订数 >=500 金额 <=30000 b. 将结果保存在Sheet3中。

6. 根据Sheet1中的结果,在Sheet4中新建一张数据透视表,要求: a. 显示每个客户在每个出版社所订的教材数目; b. 行区域设置为:“出版社”; c. 列区域设置为:“客户”;

d. 计数项为订数。

(四)、电话升级(时间函数).xls

1. 使用时间函数,对Sheet1中用户的年龄进行计算。

要求:计算用户的年龄,并将其计算结果填充到“年龄”列当中。 (1)取年份函数:YEAR(date),取出date的4位年份整数。 (2)取当前日期函数:NOW()

=YEAR(NOW())-YEAR(C2)

2. 使用REPLACE函数,对Sheet1中用户的电话号码进行升级。 要求:对“原电话号码”列中的电话号码进行升级。

升级方法是在区号(0571)后面加上“8”,

并将其计算结果保存在“升级电话号码”列的相应单元格中。 (1)字符串替换函数:REPLACE

功能:将某几位的文字以新的字符串替换。其替换功能与SUBSTITUTE函数大致类似 格式:REPLACE(old_text,start_num,num_chars,new_text)

14

old_text:老的文本数据

start_num:从第几个字符位置开始替换

num_chars:共替换多少字符(1个汉字算2个) new_text:用来替换的新字符串 (2)连字符运算符:&

(3)RIGHT(text,num_chars)根据所指定的字符数num_chars返回文本字符串text中右面最后一个或多个字符。

=REPLACE(F2,1,4,\3. 使用逻辑函数,判断Sheet1中的“大于等于40岁的男性”,将结果保存在Sheet1中的“是否>=40男性”。

IF嵌套:IF(判别条件,条件成立(真)时取值,条件不成立(假)时取值)

或这里 又来个IF(条件,(真)取值,(假)取值 )

=IF(D2>=40,IF(B2=\男\

4. 对Sheet1中的数据,根据以下条件,利用函数进行统计:

a. 统计性别为“男”的用户人数,将结果填入Sheet2的B1单元格中; =COUNTIF(Sheet1!B2:B37,\男\

b. 统计年龄为“>40”岁的用户人数,将结果填入Sheet2的B2单元格中。=COUNTIF(Sheet1!D2:D37,\

5. 将Sheet1复制到Sheet3,并对Sheet3进行高级筛选。 a. 筛选条件为:“性别”-女、“所在区域”-西湖区;

性 别 女 所在区域 西湖区 b. 将筛选结果保存在Sheet3中。

6. 根据Sheet1的结果,创建一数据透视图Chart1,要求:

a. 显示每个区域所拥有的用户数量;汇总 b. x坐标设置为“所在区域”; c. 计数项为“所在区域”;

d. 将对应的数据透视表保存在Sheet4中。

(五)、灯泡采购(数组公式if).xls

1. 使用数组公式,计算Sheet1中的每种产品的价值,将结果保存 到表中的“价值”列中。 计算价值的计算方法为:“单价*每盒数量*采购盒数”。 {=E2:E17*F2:F17*G2:G17}

2. 在Sheet2中,利用数据库函数及已设置的条件区域,计算以下 情况的结果,并将结果保存相应的单元格中。

数据库函数格式为:函数名称(database,field,criteria)

database(数据库):包含有每列项目标题的长方形单元格区域,一般即整个工作表。 field(字段):指定数据库函数所作用的数据列名。如E1(单价) criteria(条件区域):一组包含给定条件的单元格区域。 如在sheet2表中自己先构建条件区间,如; J K 1 条件区域1:

L 瓦数 <100 2 3 商标 上海 产品 白炽灯 15

a. 计算:商标为上海,瓦数小于100的白炽灯的平均单价; DAVERAGE(A1:G17,E1,J2:L3)

b. 计算:产品为白炽灯,其瓦数大于等于80且小于等于100的数量(采购合数)。

DSUM(A1:H17,G1,J7:L8)

3. 某公司对各个部门员工吸烟情况进行统计,作为人力资源搭配的一个数据依据。对于调查对象,只能回答Y(吸烟)或者N(不吸烟)。根据调查情况,制做出Sheet3。请使用函数,统计符合以下条件的数值。

a. 统计未登记的部门个数;

计算空白单元格数目函数:COUNTBLANK(range) 功能:计算某个单元格区域中空白单元格的数目。 COUNTBLANK(B2:E11)

b. 统计在登记的部门中,吸烟的部门个数。 COUNTIF(B2:E11,\

4. 使用函数,对Sheet3中的B21单元格中的内容进行判断,判断其是否问(?为)文本,如果是,结果为TRUE;如果不是,结果为FALSE,并将结果保存在Sheet3中的B22单元格当中。 函数ISTEXT(value) 功能:判定value是否为文本 ISTEXT(B21)

5. 将Sheet1复制到Sheet4中,对Sheet4进行高级筛选,要求:

a. 筛选条件:“产品为白炽灯,商标为上海”,并将结果保存;

产品 白炽灯 商标 上海 b. 将结果保存在Sheet4中。

6. 根据Sheet1的结果,在Sheet5中创建一张数据透视表,要求: a. 显示不同商标的不同产品的采购数量; b. 行区域设置为“产品”; c. 列区域设置为“商标”; d. 计数项为“采购盒数”。

(六)、房产销售(数组公式).xls

1. 利用公式,计算Sheet1中的房价总额。

房价总额的计算公式为:“面积*单价” F3*G3 2. 使用数组公式,计算Sheet1中的契税总额。 契税总额的计算公式为:“契税*房价总额”

{=H3:H26*I3:I26}

3. 使用函数,根据Sheet1中的结果,统计每个销售人员的销售总额,将结果保存在Sheet2中的相应的单元格中。

SUMIF(Sheet1!K3:K26,A2,Sheet1!I3:I26) 等等,等等!

4. 使用RANK函数,根据Sheet2的结果,对每个销售人员的销售情况 进行排序,并将结果保存在“排名”列当中。

RANK(B2,$B$2:$B$6) 等等!

5. 将Sheet1复制到Sheet3中,并对Sheet3进行高级筛选,要求:

a. 筛选条件为:“户型”为两室一厅,“房价总额”>1000000; 户型 两室一厅 房价总额 >1000000 b. 将结果保存在Sheet3中。 6. 根据Sheet1的结果,创建一张数据透视图Chart1,要求;

16

联系合同范文客服:xxxxx#qq.com(#替换为@)