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

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

2. 使用RANK函数,对Sheet1中的每个同学排名情况进行统计,并将排名结果保存到表中的“排名”列当中。

函数RANK的用法:RANK(排名次的数据依据单元,排名的范围——绝对引用,0(或缺省)/非0) 说明:0(或缺省)以所在单元的名次是降序的位数排名,否则升序。

=RANK(F2,$F$2:$F$39,0)

3. 使用逻辑函数判断Sheet1中每个同学的每门功课是否均高于平均分,如果是,保存结果为TRUE,否则,保存结果为FALSE

将结果保存在表中的“三科成绩是否均超过平均”列当中。 (1)Excel中有关逻辑函数

1. AND(X条件 , Y条件, Z条件,??)都成立(TRUE),则最终成立。 2. OR (X条件, Y条件, Z条件,??)有一成立,则最终成立。 3. NOT(条件) 反之!

(2)IF函数:

IF(判别条件,条件成立(真)时取值,条件不成立(假)时取值) 例:=IF(C5(成绩)<60,“不及格”,“及格”) (3)AVERAGE平均值函数 AVERAGE(被求平均值的区域)

=IF(AND(C2>AVERAGE($C$2:$C$39),D2>AVERAGE($D$2:$D$39),E2>AVERAGE($E$2:$E$39)),TRUE,FALSE)

4. 根据Sheet1中的结果,使用统计函数,统计“数学”考试成绩各个 分数段的同学人数,将统计结果保存到Sheet2中的相应位置。

条件统计个数的函数COUNTIF

COUNTIF(被统计个数的区域之绝对引用,条件)

数学分数位于0到20分的人数:=COUNTIF(Sheet1!$D$2:$D$39,\数学分数位于20到40分的人数:

=COUNTIF(Sheet1!$D$2:$D$39,\等等

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

a. 筛选条件:“语文”>=75,“数学”>=75,“英语”>=75,“总分”>=250; b. 将结果保存在Sheet3中。 (1)将Sheet1复制到Sheet3中

(2)在Sheet3的空白处,自己先建如下条件

语文 >=75

数学 >=75 英语 >=75 总分 >=250

9

(3)选中Sheet3中的表格全部。 (4)点[数据]→[筛选] →[高级筛选] (5)如下图:

(6)选中条件区域,成为如下图:确定!

注:(a)无需考虑是否删除或移动筛选条件;

(b)复制数据表后,粘贴时,数据表必须顶格放置。

6. 根据Sheet1中的结果,在Sheet4中创建一张数据透视表,要求: a. 显示是否三科均超过平均分的学生人数;

b. 行区域设置为:“三科成绩是否均超过平均”; c. 计数项为三科成绩是否均超过平均。

数据透视表: 用“数据透视表和数据透视图向导”

选中Sheet1的整个表:[数据]→[数据透视表和数据透视图P..] ①选择所创建的数据透视表的数据源类型(会自动出默认)。下一步

10

②选择数据源的区域,包括那张表sheet?(没有汇总过的)。下一步

③“布局”设计将要生成的数据透视表的版式和选项。

④在以下的图中,按题目要求进行拖放(搞错可拖回去)

11

(二)、服装采购(VLOOKUP函数).xls

1. 使用VLOOKUP函数,对Sheet1中的商品单价进行自动填充。 要求:根据“价格表”中的商品单价,利用VLOOKUP函数,

将其单价自动填充到采购表中的“单价”列中。

(1)VLOOKUP叫引用函数;一个数组或表格②table_array的最左列中含有特定值的字段,到另一数据表格、数组或数据库①look_value去查找,找到同值时,把②的第col_index_num列的对应值填到①的某一指定单元格中。

VLOOKUP(lookup_value,talbe_array,col_index_num,range_lookup) look_value:被查找的列的值, 如A11,A12,??会相对下去 table_array:引用的数据表格、数组或数据库,如$F$2:$G$4 绝对引用。

col_index_num:一个数字,代表要返回的值位于table_array中的第几列。

rang_lookup:一个逻辑值,如果其值为TRUE或被省略,则符合多少算多少;如果该值为FALSE时,函数只会查找完全符合的数值,如果找不到,则返回错误值“#N/A”。 =VLOOKUP(A11,$F$2:$G$5,2,FALSE)

11 12 A 项目 衣服 裤子 鞋子 衣服 裤子 鞋子 衣服 裤子 ?? B Look表 C D 单价 table表 第2列 ?? ?? 1 2 3 4 5 F G 价格表 项目 衣服 裤子 鞋子 单价 120 80 150 ?? ?? ?? 2. 使用逻辑函数,对Sheet1中的商品折扣率进行自动填充。 要求:根据“折扣表”中的商品折扣率,利用相应的函数,将其折扣率自动填充到采购表中的“折扣“列中。

=IF(B11>=$A$6,$B$6,IF(B11>=$A$5,$B$5,IF(B11>=$A$4,$B$4,$B$3)))

3. 利用公式,计算Sheet1中的“合计金额”。

要求:根据“采购数量”,“单价”和“折扣”,计算采购的“合计金额”。 计算公式:单价*采购数量*(1-折扣) =D11*B11*(1-E11)

4. 使用SUMIF函数,统计各种商品的采购总量和采购总金额,将结果保存在Sheet1中的“统计表”当中。

(1)判断条件求和函数SUMIF

SUMIF(被判断的区域,判断条件,要求和的列)

=SUMIF(A$11:A$43,I12,B$11:B$43) =SUMIF(各种商品,衣服,采购数量)等等,等等! =SUMIF(A$11:A$43,I12,F$11:F$43) =SUMIF(各种商品,衣服,合) 等等,等等!

统计表 统计类别 总采购量 总采购金额 衣服 2800 305424

12