搜索
大学IT课程Excel函数实验一、实验要求打开“学生奖学金信息表.xlsx”，“学生基本信息”工作表内容如图1所示，“学院信息”工作表内容如图2所示。图1“学生基本信息”工作表内容图2“学院信息”工作表内容在上述数据的基础上，实现结果如图3所示。图3实现结果说明：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，如图4所示。图4设置行高更改标题字号为18并加粗显示，设置后的标题效果如图4-15所示。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=一等,I3=优秀),5000,IF(H3=一等,4000,3000))，计算出当前单元格的奖学金，然后拖动填充柄至单元格J33，或双击填充柄，计算出所有人的奖学金信息。7.计算获得奖学金的男、女生人数在单元格B35、B36中分别输入公式：=COUNTIF(D3:D33,男)和=COUNTIF(D3:D33,女)，计算男、女生人数。8.计算女生获得奖学金总额在单元格D35中输入公式：=SUMIF(D3:D33,女,J3:J33)，计算女生获得奖学金总额。9.计算获得5000元奖学金的人数在单元格D36中输入公式：=COUNTIF(J3:J33,5000)，计算获得5000元奖学金的人数。10．为奇数行和偶数行设置不同填充颜色选中单元格区域A2：J33，然后选择“开始”选项卡中“样式”命令组中的“条件格式”中的下拉箭头，选择“新建规则”，在“新建格式规则”对话框中选择“使用公式确定要设置格式的单元格”，然后设置公式：=MOD(ROW(),1)=0，如图5所示，单击“格式”按钮，在图6所示的图中选择填充颜色，按确定返回，这样就设置好了奇数行的填充颜色。图5“新建格式规则”对话框图6“设置单元格格式”对话框重复刚才步骤，将公式修改为：=MOD(ROW(),2)=0，设置偶数行的填充颜色。11.为表格添加表格线选中单元格区域A2：J33，然后选择“开始”选项卡中“字体”命令组中的“所有框线”命令，如图7所示。计算完毕及设置效果如图3所示。