常用函数使用及重难点总结Word文档格式.docx

常用函数使用及重难点总结Word文档格式.docx

《常用函数使用及重难点总结Word文档格式.docx》由会员分享，可在线阅读，更多相关《常用函数使用及重难点总结Word文档格式.docx（10页珍藏版）》请在冰豆网上搜索。

,"

FALSE”）

3. 

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

60~80分数段：

=COUNTIF（D2:

D39,”<

80”）-COUNTIF（D2:

60”）

80~100分数段：

=COUNTIF（Sheet1!

D2:

D39,"

<

100"

）-COUNTIF（Sheet1!

80"

）

4. 

将Sheet1复制到Sheet3中，并对Sheet3进行高级筛选，要求：

筛选条件：

“语文”〉=75，“数学”>

=75，“英语”>

=75，“总分”>

=250;

将结果保存在Sheet3中。

在空白区域创建筛选条件，注意筛选条件如“语文“”订数“与原统计表格式保持一致。

数据—筛选—高级筛选，”列表区域“与”条件区域“的选择

二、书籍订购表：

1、 

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

统计出版社名称为“高等教育出版社”的书的种类数；

统计订购数量大于110且小于850的书的种类数。

a小题使用COUNTIF函数，Range“D3:

D52”,Criteria参数为”高等教育出版社”

b小题=COUNTIF（G3:

G52,”<

850”）-COUNTIF（G3:

=110”）

2、 

使用函数计算，每个用户所订购图书所需支付的金额总数，将结果保存在Sheet1中

的相应位置。

使用SUMIF函数（数学与三角函数），Range[微软用户1]填A3：

A52，Criteria参数为”-c1”,Sum_range参数[微软用户2]为I3:

I52，以此类推

3、 

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

说明：

闰年定义：

年数能被4整除而不能被100整除，或者能被400整除的年份。

使用逻辑函数IF，输入公式

=IF（OR（AND（MOD[微软用户3]（A2,4）=0，MOD（A2，100）<

>

0）,MOD（A2,400）=0），”闰年”，”平年”）

三、灯泡生产表：

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

计算：

商标为上海，瓦数小于100的白炽灯的平均单价

产品为白炽灯，其瓦数大于等于80且小于等于100的数量。

=DAVERAGE（A1:

H17,E1[微软用户4],J2:

L3）

=DCOUNT（A1:

H17,,[微软用户5]J7:

L8）

某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。

对于调查对象，只能回答Y（吸烟）或者N（不吸烟）。

根据调查情况，制作出Sheet3。

请使用函数，统计符合以下条件的数值。

a. 

统计未登记的部门个数；

b. 

统计在登记的部门中，吸烟的部门个数。

=COUNTBLANK[微软用户6]（B2:

E11）

=COUNTIF（B2:

E11,“Y”）

使用函数，对Sheet3中的B21单元格中的内容进行判断，判断其是否问文本，如果是，结果为“TRUE”；

如果不是，结果为“FALSE”，并将结果保存在Sheet3中的B22单元格中。

=ISTEXT[微软用户7]（B21）

四、电话号码分析表

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

要求：

计算用户的年龄，并将其计算结果填充到“年龄”列当中

=YEAR（TODAY（）[微软用户8]）-YEAR（C2）

2、使用REPLACE函数，对Sheet1中用户的电话号码进行升级。

对“原电话号码”列中的电话号码进行升级。

升级方法是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列

的相应单元格中。

使用文本函数REPLACE：

Old_text:

G2;

Start_num:

5;

Num_char:

0[微软用户9]；

New_text:

“8”

使用逻辑函数，判断Sheet1中的“大于等于40岁的男性”，将结果保存在Sheet1中的“是否〉=40男性”。

=IF（AND（B3="

男"

D3>

=40）,"

是"

否"

对Sheet1中的数据，根据以下条件，利用函数进行统计：

统计性别为“男”的用户人数，将结果填入Sheet2的B1单元格中；

统计年龄为“〉40岁”的用户人数，将结果填入Sheet2的B2单元格中。

COUNTIF（Sheet1!

B2:

B37,“男”）COUNTIF（Sheet1!

D37,”>

40”）

五、房产销售表

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

解法有二：

在Sheet2需填充的单元格中分别输入函数，如在人员甲的销售总额函数为：

=SUMIF（Sheet1!

K3:

K26,”=人员甲”,Sheet1!

I3:

I26），以此类推。

在Sheet2B2单元格中输入公式：

$K$3:

$K$26,Sheet2!

A2,Sheet1!

$I$3:

$I$26），拖拉B2的填充柄。

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

RANK函数（统计函数，Rank（number,ref,order）,number为需要找到排位的数字；

RANK（B2,$B$2:

$B$[微软用户10]6,0）注意ref为绝对引用

六、服装采购表

使用VLOOKUP函数，对Sheet1中的商品单价进行自动填充。

根据“价格表”中的商品单价，利用VLOOKUP函数，将其单价自动填充到采购表中的“单价”列中。

=VLOOKUP（A11,$F$2:

$G$4,2,FALSE）[微软用户11]

使用逻辑函数，对Sheet1中的商品折扣率进行自动填充。

根据“折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购表中的“折扣”列中。

=IF（B23<

100,"

0%"

IF（B23<

200,"

6%"

300,"

8%"

10%"

）））

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

采购总量：

=SUMIF（$A$11:

$A$43,I12,$B$11:

$B$43[微软用户12]）然后拖动J12的填充柄

采购总金额：

$A$43,I12,$F$11:

$F$43）

七、公务员考试成绩表

使用IF函数，对Sheet1中的“学位”列进行自动填充。

填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）：

博士研究生——博士

硕士研究生——硕士

本科——学士

其他——无

=IF（G7="

博士研究生"

博士"

IF（G7="

硕士研究生"

硕士"

本科"

学士"

无"

在Sheet2中，添加一列，将其命名为“排名”。

使用RANK函数，根据“总成绩”对所有考生排名。

=RANK（M18,$M$3:

$M$18,0）

八、停车收费表

使用HLOOKUP函数，对Sheet1中的停车单价进行自动填充。

根据Sheet1中的“停车价目表”价格，利用HLOOKUP函数对“停车情况记录表”中的“单价”列根据不同的车型进行自动填充。

=HLOOKUP（B9,$A$2:

$C$3[微软用户13],2,FALSE）

使用函数公式，计算停车费用，要求：

根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。

注意：

停车按小时收费，对于不满一个小时的按照一个小时收费；

对于超过整点小时数十五分钟的多积累一个小时。

（例如1小时23分，将以2小时计费）

=IF（HOUR（F9）=0,1,IF（MINUTE（F9）>

=15,HOUR（F9）+1,HOUR（F9）））*C9

使用统计函数，对Sheet1中的“停车情况记录表”根据下列条件进行统计，要求：

统计停车费用大于等于40元的停车记录条数COUNTIF

统计最高的停车费用=MAX（G9:

G39）

九、温度分析表

1.使用数组公式，对Sheet1中的相差温度值（杭州相对于上海的温差）进行填充。

=B2:

B16-C2:

C16

2.将Sheet1复制到Sheet2中，在Sheet2中，重新编辑数组公式，

将Sheet2中的“相差的温度值”中的数值取其绝对值（均为正数）

=ABS[微软用户14]（B2:

C16）

十、员工情况统计表

使用REPLACE函数，对Sheet1中的员工代码进行升级，要求：

升级方法：

在PA后面加上0；

将升级后的员工代码结果填入表中的“升级员工代码”列中。

=REPLACE（B2:

B65,3,0,0）

2.使用时间函数，对Sheet1员工的“年龄”和“工龄”进行计算，并将结果填入到表中

“年龄”列和“工龄”列中。

年龄计算：

=YEAR（TODAY（）[微软用户15]）-YEAR（E2）

工龄计算：

如果工龄要写成“**年**个月”这种形式，则

=CONCATENATE（DATEDIF（G8,TODAY（）,"

y"

）,"

年"

DATEDIF（G8,TODAY（）,"

ym"

个月"

或=CONCATENATE（YEAR（TODAY（））-YEAR（G2）,"

MONTH（TODAY（））-MONTH（G2）,"

考虑到下题“统计工龄大于等于10的人数，结果填入N5单元格中”，故工龄计算应为=（YEAR（TODAY（））-YEAR（G2）），直接求出年份，且无格式。

补充函数：

1.使用IF函数，根据Sheet1中的“图书订购信息