Excel实验2

实验2 Excel公式和函数

一、实验目的

1． 掌握

（1） 公式和运算符 （2） 常用函数

二、实验要求

1． 学会创建公式 2． 学会使用函数

三、实验内容

（一）、基础实验

打开素材文件1.xlsx文件，下面所有的操作都在此工作簿内完成。

操作一、选择Sheet1工作表，对“1949年～1990年中国人口增长统计表（万人）”，按下面的要求完成。

1. 用公式和函数计算。

（1） 在F3:F10区域，计算“自然增长率”，自然增长率等于出生率－死亡率。 （2） 在G3:G10区域，使用IF函数计算“增长率高低”，条件是如果自然增长率大于

等于20%，显示为“高”，否则显示为“低”。

（3） 在C12单元格，使用MAX函数计算每年总人口的最大值。 （4） 在D12单元格，使用AVERAGE函数计算平均出生率。 （5） 在E12单元格，使用MIN函数计算最小死亡率。 （6） 在F12单元格，使用COUNT函数统计记录个数。

（7） 在G12单元格，使用COUNTIF函数统计出生率大于35%的记录个数。 2. 注意：公式填充的过程中选择“不带格式填充”。 操作二、选定Sheet2工作表，按下面的要求完成。 1. 用公式和函数计算。

（1） 在F4:F7单元格内，计算每个部门的总人数。 （2） 在G4:G7单元格内，用公式计算每个部门的人数在这家公司总人数中所占比例，

并设置为百分比格式，保留小数点后2位。（注意使用相对引用和绝对引用）

操作三、选定Sheet3工作表，按下面的要求完成。 1. 选择A1单元格，输入身份证号。（学生自己的身份证号）

第1页共3页

2. 用函数按要求一步一步完成。

（1） 在B1单元格，使用LEN函数判断身份证号的位数。 （2） 在C1单元格，使用MID取出身份证号的第17位数字。

（3） 在D1单元格，使用MOD函数判断C1单元格值除以2的余数。

（4） 在E1单元格，使用IF函数输出性别，要求为：如果D1单元格的值为0，输出

“女”，否则为“男”。

（5） 在F1单元格，试着用嵌套函数直接对A1单元格进行操作，输出性别。 （二）、综合实验

打开素材文件2.xlsx文件，下面所有的操作都在此工作簿内完成。 1. 选择工作表“学生成绩单”，对“学生成绩单”表格进行快速格式化。

（1） 表格标题：应用样式 （2） 边框：自定义外粗里细 （3） 各列标题加粗 （4） 所有内容居中

2. 用公式和函数完成“学生成绩单”表格。

（1） 计算“代码”，代码为学号的第3、4位。 （2） 计算“代码”代表的“学院名称”，使用VLOOKUP函数，查找“代码对应表”，

输出代码对应的学院名称。（选做题，如果无法用VLOOKUP函数完成，请自行填写学院名称）

（3） 计算每个人的“平均成绩”并进行四舍五入，保留整数。（注意：使用嵌套函数） （4） 计算每个人的“总分”。 （5） 计算“成绩等级”。若平均成绩在85分以上(包括85），则等级为“优秀”；成绩

在60分-84分之间，成绩为“及格”；成绩在60分以下，则为“不及格”。

3. 选定工作表“统计表”，对“学生成绩单”表格进行统计。

（1） 在“成绩统计表”表格的相应区域内，用函数求出各科目的最高分和最低分。 （2） 在“频率分布表1”表格内，用COUNTIF函数统计各科目60分以下、60-84.99

分、85分以上人数。

（3） 在“性别统计表”的表格内计算不同性别的人数。

（4） 在“频率分布表2”表格内，用FREQUENCY函数统计各科目60分以下、

60-84.99分、85分以上人数。（选做题）

（三）、拓展实验

打开3.xlsx文件，下面的操作都在此工作簿内完成。 操作一、选定Sheet1工作表，按下面的要求完成。（利用Excel表的计算列和结构化引用功能）

1. 选定A3:G7单元格，单击“插入”选项卡|“表格”组|“表格”按钮，在“创建表”对话

框中选择“表包含标题”选项。 2. 用公式和函数计算。

（1） 在F4:F7单元格内，计算每个部门的总人数。

第2页共3页

（2） 在G4:G7单元格内，用公式计算每个部门的人数在这家公司总人数中所占比例，

并设置为百分比格式，保留小数点后2位。

3. 注意观察Excel表的公式中单元格引用方式和计算列功能。 4. 将Excel表转化为普通区域，观察公式。

第3页共3页

本文来源：https://www.bwwdw.com/article/ytxa.html