大学IT第三次上机实验

大学IT第三次上机实验

本次上机实验需要两周时间，完成后每个同学将自己的文档存放在以“专业+班级+姓名”的文件夹内，并按班级汇总，压缩成一个名称为“专业+班级.RAR”的文件，并提交给任课教师。

实验一

请参照《大学IT实验教程》3.3.6和3.3.7两节的内容，使用Excel2010软件制作如下表格内容，最后设计的表格内容和形式务必与提供的样表一致。

家庭收支流水账序号发生日期收支摘要上月余额0102030405060708091011121314152013年2月1日2013年2月2日2013年2月3日2013年2月4日2013年2月5日2013年2月6日2013年2月7日2013年2月8日2013年2月9日工资奖金房租日常生活费学习用品工资奖金日常生活费服装鞋帽日常生活费日常生活费￥5,400.00￥46.00￥230.00￥144.00￥230.00￥300.00￥8,320.00收入金额支出金额余额￥2,320.00￥10,640.00￥4,300.00￥150.00￥23.00￥6,340.00￥6,190.00￥6,167.00￥11,567.00￥11,521.00￥11,291.00￥11,147.00￥10,917.00￥10,617.00￥10,117.00￥10,067.00￥10,024.00￥9,924.00￥9,854.00 2013年2月10日水电气2013年2月11日服装鞋帽2013年2月12日学习用品2013年2月13日日常生活费2013年2月14日日常生活费2013年2月15日日常生活费￥500.00￥50.00￥43.00￥100.00￥70.001. 表格标题“家庭收支流水账”必须居于表格的中间，且底色为浅黄色，文字颜色为深蓝色，字体为黑体，字号为14磅。

2. 设置表格的列标题行底色为标准浅蓝色、文字颜色为白色，且居中对齐。 3. 表格中的“序号”列内容设置为文字型，“发生日期”为日期型，“收支摘要”为文字型，其余各列均为货币型，且小数位数保留两位。

4. 表格中的货币型数据右对齐，其余各列数据均居中对齐。

5. 设置表格的第2行至第18行的行高为20，调整列宽能够适应单元格的内容，并为该区域添加边框线（其中外框为双框线，内框为单细线）。

6. 使用“数据有效性”，设置“收支摘要”列的内容只能输入“工资奖金、房租、水电气、日常生活费、学习用品、服装鞋帽”这些项目内容，不允许输入其他的项目内容。

7. 序号01至15的各行中，“余额”列使用公式计算，即：本期余额=上期

余额+收入-支出。

8. 使用条件格式，把流水账中支出金额超过300元的用红色、斜体、加删除线的格式标出。

9. 使用“页面设置”，将表格的全部内容设定为打印区域，第1行至第3行为打印的标题行，并设置页边距为水平居中对齐。

10. 将表格所在的工作表名称改为“二月”，并删除其他工作表，保存当前工作薄名称为“家庭流水账.xlsx”。

实验二

请参照《大学IT实验教程》3.3.6和3.3.7两节的内容，使用Excel2010软件完成如下操作（提供的素材样表内容如下图，左图为学生基本信息表，右图为学院信息表）。 学号200601130007200609220150200705010109200707010406200709030235200710030111200711010131200713000136200715010509200802230102200803610126200804230101200805610124200805610130200806410103200806410104200806410105200807140101200807140102200808140103200809410101200809410102200901150104200901150106200902400101200902400102200903610102200904210104200912610105201010220111201014220110男生人数女生人数姓名聂晓玉洪文娟徐月婷杜希萍于斌赵敏安研研赵芹孙营营刘杰李峰杜俊儒刘红李小凤倪晓荣徐林辉姚亮叶向东刘杰王玉艳孙广芳郭泽良王卓群张宏丽刘李敏鄢凤英李衍超刘恩来孙小韵赵聪慧姜宁宁身份证号******198801052535******198906105739******198803264528******198812060624******198710111948******198806031164******198704070865******198906200224******198612074912******199002104022******198910302068******198805072545******198911122817******198908026928******199001280758******198803040714******199001140826******198809242925******198911293368******199005125922******198811071014******198907100970******198907103330******199008230023******199010030020******199002251022******198902163858******199105040323******19920202142X******199005310529******198812286022女生获得奖学金总额获得5000元奖学金的人数性别学院年龄年级奖学金等级二等一等二等一等二等二等一等二等二等二等二等一等二等二等二等二等二等二等二等二等二等一等二等一等二等一等二等二等二等一等一等论文奖学金优秀优秀优秀良好良好良好良好良好良好优秀良好优秀优秀优秀良好优秀优秀优秀良好良好优秀优秀优秀优秀优秀优秀良好良好良好良好良好学院代号010203040506070809101112131415学院名称文学院政治与国际关系学院历史与社会发展学院教育学院外国语学院音乐学院美术学院数学科学学院物理与电子科学学院化学化工与材料科学学院信息科学与工程学院传媒学院生命科学学院人口．资源与环境学院体育学院打开素材“学生奖学金信息表.xlsx”，有如下说明： 1) 学号的前四位表示年级，第 5、 6 位表示学院代号，如： 200601130007， 2006 表示年级， 01 表示文学院；身价证号的第 17 位表示性别，若为奇数表示“男”，若为偶数表示“女”，第 7、 8、 9、 10 位表示出身年份。

2) 奖学金等级分为两类：一等和二等，论文等级也分为两类：优秀和良好。发放标准如下：奖学金等级为一等且论文为优秀的，奖学金额 5000 元，奖学金等级为一等且论文为良好的，奖学金额 4000 元，其他的为 3000 元。

实验任务如下： 1. 为表格增加标题

1) 在行号为 1 的行号上右击，在快捷菜单中选择“插入”，插入一个空行，然后在 A1单元格中输入“虚拟学校学生奖学金发放表”。

2) 选中单元格区域 A1：J1，单击“开始”选项卡中“对齐方式”组中的按钮，合并单元格区域 A1：J1，并使标题居中显示。

3) 在行号为 1 的行号上右击，在快捷菜单中选择“行高”，在出现的对话框中输入行高20，更改标题字号为 18 并加粗显示。 2. 填充“性别”列

在 D3 单元格中输入公式： =IF(MOD(MID(C3,17,1),2)=0,\女\男\并回车，计算出当前单元格的性别，然后拖动填充柄至单元格 D33，或双击填充柄，计算出所有人的性别。

3. 填充“学院”列

在 E3 单元格中输入公式： =VLOOKUP(MID(A3,5,2),学院信息!$A$2:$B$16,2)，计算出当前单元格的学院，然后拖动填充柄至单元格 E33，或双击填充柄，计算出所有人的学院信息。

4. 填充“年龄”列

在 F3 单元格中输入公式： =YEAR(NOW())-(MID(C3,7,4))，计算出当前单元格的年龄，然后拖动填充柄至单元格 F33，或双击填充柄，计算出所有人的年龄信息。

5. 填充“年级”列

在 G3 单元格中输入公式： =LEFT(A3,4)，计算出当前单元格的年级，然后拖动填充柄至单元格 G33，或双击填充柄，计算出所有人的年级信息。

6. 填充“奖学金”列

根据前面的奖学金发放标准，在 J3 单元格中输入公式： =IF(AND(H3=\一等\优秀\一等\，计算出当前单元格的奖学金，然后拖动填充柄至单元格J33，或双击填充柄，计算出所有人的奖学金信息。

7. 计算获得奖学金的男、女生人数

在单元格 B35、 B36 中分别输入公式： =COUNTIF(D3:D33,\男\和=COUNTIF(D3:D33,\女\，计算男、女生人数。

8. 计算女生获得奖学金总额

在单元格 D35 中输入公式： =SUMIF(D3:D33,\女\，计算女生获得奖学金总额。

9. 计算获得 5000 元奖学金的人数

在单元格 D36 中输入公式： =COUNTIF(J3:J33,5000)，计算获得 5000 元奖学金的人数。

10. 为奇数行和偶数行设置不同填充颜色

选中单元格区域 A2： J33，然后选择“开始”选项卡中“样式”命令组中的“条件格式”中的下拉箭头，选择“新建规则”，在“新建格式规则”对话框中选择“使用公式确定要设置格式的单元格”，然后设置公式： =MOD(ROW(),1)=0，单击“格式”按钮，在打开的对话框中选择填充颜色，按确定返回，这样就设置好了奇数行的填充颜色。

11. 为表格添加表格线

选中单元格区域 A2： J33，然后选择“开始”选项卡中“字体”命令组中的“所有框线”命令。计算完毕及设置效果下图所示。

12. 另存工作薄文件为“奖学金计算.XLSX”。