实训 6 公式和函数实训6.1 实训目的(1)掌握在工作表中应用公式的方法。(2)掌握在工作表中应用函数的方法。6.2实训内容案例 1 在工作表 1 中完成下列操作在区域A2A21 中用函数输入 40,100的随机整数；在区域B2B21 中用函数计算出与A2A21 对应随机整数的平方根值；在区域C2C21 中用函数计算出以A2A21 对应随机整数为直径的圆面积；在区域D2D21 中用函数计算出与A2A21 对应随机整数的奇偶性。对应的整数为奇数时输出文本“奇”，为偶数时输出文本“偶”。图 6-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 元；名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 10 页 -图 6-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%。图 6-5工作表5 操作步骤：在单元格F2 中输入公式AVERAGE(D2E2)，然后双击单元格F2 的填充柄将公式复制到F3F28；名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 10 页 -在单元格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的填充柄将公式复制到H3 H28。案例 6在工作表6 中完成下列操作在区域 B5D5 中用函数计算出相应科目最高分同学的姓名。要求区域B5D5 的姓名会随区域A1D4 中的数据一同变化。图 6-6工作表6 操作步骤：在单元格 B5中输入公式IF(B2MAX(B2B4)，$A2，IF(B3MAX(B2B4)，$A3，$A4)，然后将公式复制到C5D5。案例 7 在工作表 7 中完成下列操作在单元格G3 中输入公式计算税金：税金交纳规则为将年工资总额分段计算，15000 元及以下部分扣税金0%,15001 25000 元部分扣税金5%,25001 元及以上部分扣税金10%，然后复制到G4G17，“税金”的数值取小数点后2 位。图 6-7工作表7 操作步骤：在单元格F3 中输入公式=IF(E3=15000,0,IF(E325，肥胖，IF(F419，瘦削 ，正常)，然后将公式复制到F5F9。选中区域D4D9和区域 E4E9，将数据格式设置为数值取小数点后2 位。案例 10 在工作表 10 中完成下列操作区域 A1C28 给出了一个救灾捐款清单，根据此清单中的内容在区域F2 G7 的各单元中计算出相应的内容；名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 10 页 -图 6-10工作表10 操作步骤：在单元格F3 中输入公式COUNTIF(B$3 B$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 中用公式计算出相应的新号码；图 6-11工作表11 名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 10 页 -操作步骤：在单元格C3 中输入公式IF(LEFT(B3,1)2，2&B3，IF(OR(LEFT(B3,1)1，LEFT(B3,1)9)，B3，8&B3)，然后将公式复制到C4C14。案例 12 在工作表 12 中完成下列操作根据 C2C28 数据清单中所列的年龄，在区域D2D28 中计算出每个人的年龄排名，排名规则为：年龄最长者排名为1，然后随年龄递减而排名依次加1，若年龄相同则排名也相同，但该排名之后的名次仍应为比其名次靠前的总人数加1。图 6-12工作表12 操作步骤：在单元格 D2中输入公式RANK(C2，C$2C$28,0)，然后将公式复制到D3D28。案例 13 在工作表 13 中用数据库统计函数完成下列操作在 H1 单元内计算出所有上半年出生人员得分的总和；(DSUM)在 H2 单元内计算出所有50 年代出生人员得分的平均分；(DAVERAGE)在 H3 单元内计算出所有少数民族的人员总数；(DCOUNT A 或 DCOUNT)在 H4 单元内计算出所有汉族医生的最高得分；(DMAX)在 H5 单元内计算出所有大学本科人员的最低得分。(DMIN)操作步骤：A.在区域 G7G13 中建立比较条件区域。B.在区域 1718 中建立计算条件区域。身份证号码?01?02?03?04?05?06?名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 10 页 -或tj=MID(A3,9,2)=06在单元格 H1 中输入公式DSUM(A2E28，E2，G7 G13)；图 6-13工作表13 A.在区域 G15G16 中建立比较条件区域B.在区域I15 I16 中建立计算条件区域。身份证号码?5?或tj=MID(A3,7,1)=5在单元格 H2 中输入公式DAVERAGE(A2E28，E2，G15G16)；在区域 G18G19 中建立比较条件区域：民族汉在单元格H3 中输入公式DCOUNTA(A2E28，B2，G18G19)；在区域 G21H22 中建立比较条件区域民族职业汉医生在单元格H4 中输入公式DMAX(A2E28，E2，G21H22)；在区域 G24G25 中建立比较条件区域：名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 10 页 -文化程度大学本科在单元格H5中输入公式DMIN(A2E28，E2，G24G25)。6.3 设计性实训1.在工作表 14 中完成下列操作：在 E3 单元格填入公式，计算出累积金牌数，然后复制到E4E18 区域；在 F3 单元格填入公式，计算出累积奖牌数，然后复制到F4F18 区域。图 6-14工作表 14 2.在工作表 15 中完成下列操作根据区域A1B28 中的数据清单，在区域F3F8 中使用频率分布函数计算出各分数段的人数。名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 10 页 -图 6-15工作表15 名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 10 页 -