2.2Excel数据分析实验.docVIP

Excel数据分析实验(2)　公式和函数实验 2.1实验目的 (1)掌握在工作表中应用公式的方法。 (2)掌握在工作表中应用函数的方法。 2.2实验内容 实验案例1　在工作表 1 中完成下列操作 ①在区域A2∶A21中用函数输入[40,100]的随机整数； ②在区域B2∶B21中用函数计算出与A2∶A21对应随机整数的平方根值； ③在区域C2∶C21中用函数计算出以A2∶A21对应随机整数为直径的圆面积； ④在区域D2∶D21中用函数计算出与A2∶A21对应随机整数的奇偶性。对应的整数为奇数时输出文本“奇”，为偶数时输出文本“偶”。 图2-1　工作表 1 操作步骤： ① 在单元格A2中输入公式 ＝40＋INT(RAND()*61)，然后拖动单元格A2的填充柄将公式复制到A3∶A21； ② 在单元格B2中输入公式 ＝SQRT(A2)，然后双击单元格B2的填充柄将公式复制到B3∶B21； ③ 在单元格C2中输入公式 ＝PI()*(A2/2)^2，然后双击单元格C2的填充柄将公式复制到C3∶C21； ④ 在单元格D2中输入公式 =IF(MOD(A2,2)=0,偶,奇)，然后双击单元格D2的填充柄将公式复制到D3∶D21。 案例2　在工作表 2 中完成下列操作 在区域G2∶G27中用函数计算每个职工的工龄工资，工龄在5年以下的(含5年)，每年的工龄工资为5元，5年以上的每年工龄工资为10元； 图2-2　工作表 2 操作步骤： 在单元格G2中输入公式 =IF(F2=5,F2*5,F2*10)，然后双击单元格G2的填充柄将公式复制到G2∶G27。 案例3　在工作表 3 中完成下列操作 区域A1∶D12为计算机水平考试获奖学生清单。设奖励规则为： ①若笔试成绩和机试成绩均不低于80分，则奖励等级“优秀”； ②若笔试成绩或机试成绩低于80分，但二者平均分不低于80分，则奖励等级为“良好”； ③若平均分不足80分，则奖励等级为“及格”。 根据学生的考试成绩在区域D3∶D12中用公式计算出相应的奖励等级。 图2-3　工作表 3 操作步骤： 在单元格D3中输入公式 ＝IF(AND(B3＝80，C3＝80)，″优秀″，IF(AND(OR(B380，C380)，AVERAGE(B3∶C3)＝80)，″良好″，″及格″))，然后双击单元格D3的填充柄将公式复制到D4∶D12。 或在单元格D3中输入公式 ＝IF(AND(B3＝80，C3＝80)，″优秀″，IF(AVERAGE(B3∶C3)80，″及格″，″良好″))，然后双击单元格D3的填充柄将公式复制到D4∶D12。 案例4　在工作表 4 中完成下列操作 区域A1∶B11给出了某次考试的分数。设计算成绩的规则为：若分数不小于全体学生的平均分数，则成绩为“合格”，否则为“不合格”。根据学生的成绩在区域C2∶C11中用公式计算出相应的成绩； 图2-4　工作表 4 操作步骤： 在单元格C2中输入公式 ＝IF(B2＝AVERAGE($B$2∶$B$11)，″合格″，″不合格″)，然后双击单元格C2的填充柄将公式复制到C3∶C11。 案例5　在工作表 5 中完成下列操作 在区域F2∶F28中用函数计算每个学生的平均分；在区域G2∶G28中用函数计算每个学生的总分；在区域H2∶H28中用IF函数计算每个学生的综合分，综合分的评分标准为：文科学生的语文成绩占综合分的70%、数学成绩占30%，理科学生的语文成绩占30%、数学成绩占70%。 图2-5　工作表 5 操作步骤： ①在单元格F2中输入公式 ＝AVERAGE(D2∶E2)，然后双击单元格F2的填充柄将公式复制到F3∶F28； ②在单元格G2中输入公式 ＝SUM(D2∶E2)，然后双击单元格G2的填充柄将公式复制到G3∶G28； ③在单元格H2中输入公式 =IF(C2=$J$3,SUM(D2*$K$3,E2*$L$3),SUM(D2*$K$4,E2*$L$4))，然后双击单元格H2的填充柄将公式复制到H3∶H28。 案例6　在工作表 6 中完成下列操作 在区域B5∶D5中用函数计算出相应科目最高分同学的姓名。要求区域B5∶D5的姓名会随区域A1∶D4中的数据一同变化。 图2-6　工作表 6 操作步骤： 在单元格B5中输入公式 ＝IF(B2＝MAX(B2∶B4)，$A2，IF(B3＝MAX(B2∶B4)，$A3，$A4))，然后将公式复制到C5∶D5。 案例7　在工作表 7 中完成下列操作 在单元格G3中输入公式计算税金：税金交纳规则为将年工资总额分段计算，15000元及以下部分扣税金0%,15001～25000元部分扣税金5%,25001元及以上部分扣税金10%，然后复制到G4∶G17，“税金”的数值取小数点后2位。 图2-7　工作表 7 操