22 Excel数据分析实验(2).doc

Excel数据分析实验(2)公式和函数实验2.1实验目的(1)掌握在工作表中应用公式的方法。 (2)掌握在工作表中应用函数的方法。2.2实验内容 实验案例1在工作表 1 中完成下列操作在区域A2A21中用函数输入40,100的随机整数；在区域B2B21中用函数计算出与A2A21对应随机整数的平方根值；在区域C2C21中用函数计算出以A2A21对应随机整数为直径的圆面积；在区域D2D21中用函数计算出与A2A21对应随机整数的奇偶性。对应的整数为奇数时输出文本“奇”，为偶数时输出文本“偶”。图2-1工作表 1操作步骤： 在单元格A2中输入公式 40INT(RAND()*61)，然后拖动单元格A2的填充柄将公式复制到A3A21； 在单元格B2中输入公式 SQRT(A2)，然后双击单元格B2的填充柄将公式复制到B3B21； 在单元格C2中输入公式 PI()*(A2/2)2，然后双击单元格C2的填充柄将公式复制到C3C21； 在单元格D2中输入公式 =IF(MOD(A2,2)=0,偶,奇)，然后双击单元格D2的填充柄将公式复制到D3D21。案例2在工作表 2 中完成下列操作在区域G2G27中用函数计算每个职工的工龄工资，工龄在5年以下的(含5年)，每年的工龄工资为5元，5年以上的每年工龄工资为10元；图2-2工作表 2操作步骤： 在单元格G2中输入公式 =IF(F280，C380)，优秀，IF(AND(OR(B380，C380)，良好，及格)，然后双击单元格D3的填充柄将公式复制到D4D12。或在单元格D3中输入公式IF(AND(B380，C380)，优秀，IF(AVERAGE(B3C3)AVERAGE($B$2$B$11)，合格，不合格)，然后双击单元格C2的填充柄将公式复制到C3C11。案例5在工作表 5 中完成下列操作在区域F2F28中用函数计算每个学生的平均分；在区域G2G28中用函数计算每个学生的总分；在区域H2H28中用IF函数计算每个学生的综合分，综合分的评分标准为：文科学生的语文成绩占综合分的70%、数学成绩占30%，理科学生的语文成绩占30%、数学成绩占70%。图2-5工作表 5操作步骤：在单元格F2中输入公式 AVERAGE(D2E2)，然后双击单元格F2的填充柄将公式复制到F3F28；在单元格G2中输入公式 SUM(D2E2)，然后双击单元格G2的填充柄将公式复制到G3G28；在单元格H2中输入公式 =IF(C2=$J$3,SUM(D2*$K$3,E2*$L$3),SUM(D2*$K$4,E2*$L$4)，然后双击单元格H2的填充柄将公式复制到H3H28。案例6在工作表 6 中完成下列操作在区域B5D5中用函数计算出相应科目最高分同学的姓名。要求区域B5D5的姓名会随区域A1D4中的数据一同变化。图2-6工作表 6操作步骤： 在单元格B5中输入公式 IF(B2MAX(B2B4)，$A2，IF(B3MAX(B2B4)，$A3，$A4)，然后将公式复制到C5D5。案例7在工作表 7 中完成下列操作在单元格G3中输入公式计算税金：税金交纳规则为将年工资总额分段计算，15000元及以下部分扣税金0%,1500125000元部分扣税金5%,25001元及以上部分扣税金10%，然后复制到G4G17，“税金”的数值取小数点后2位。图2-7工作表 7操作步骤：在单元格F3中输入公式 =IF(E3=15000, 0,IF(E325，肥胖，IF(F419，瘦削，正常)，然后将公式复制到F5F9。选中区域D4D9和区域E4E9，将数据格式设置为数值取小数点后2位。案例10在工作表 10 中完成下列操作区域A1C28给出了一个救灾捐款清单，根据此清单中的内容在区域F2G7的各单元中计算出相应的内容；图2-10工作表 10操作步骤： 在单元格F3中输入公式 COUNTIF(B$3B$28，E3)，然后将公式复制到F4F7；在单元格G3中输入公式 SUMIF(B$3B$28，E3，C$3C$28)，然后将公式复制到G4G7。案例11在工作表 11 中完成下列操作东莞市电话号码由七位升八位的规则为：若原号码首位为2，则新号码在原号码前加2；若原号码首位为1或9，则原号码不变； 若原号码首位既非2非1又非9，则新号码在原号码前加8。根据区域B3B12中的原号码在区域C3C12中用公式计算出相应的新号码；图2-11工作表 11操作步骤： 在单元格C3中输入公式 IF(LEFT(B3,1)2，2&B3，IF(OR(LEFT(B3,1)1，LEFT(B3,1)9)，B3，8&B3)，然后将公式复制到C4C14。2.3实验练习（设计性实验）注意* 1、3题可以调取自己的数据；2要上网查询准确数字1.在工作表 12 中完成下列操作根据C2C28数据清单中所列的年龄，在区域D2D28中计算出每个人的年龄排名，排名规则为：年龄最长者排名为1，然后随年龄递减而排名依次加1，若年龄相同则排名也相同。图2-12工作表 122.在工作表 13 中完成下列操作：在E3单元格填入公式，计算出累积金牌数，然后复制到E4E18区域；在F3单元格填入公式，计算出累积奖牌数，然后复制到F4F18区域。图2-13工作表 13