要求:从下面的身份证号提取性别。 D2单元格输入公式为: =IF(MOD(MID(C2,17,1),2),'男','女') 注:18位身份证中第17位表示性别,如果是奇数,表示男性,如果为偶数,表示女性。MOD函数是取余函数,与2相除来取余判断奇偶数。 03 合并单元格求和 选中D2:D13单元格区域,然后在公式编辑栏里输入公式: =SUM(C2:C13)-SUM(D3:D14) 然后按<Ctrl+Enter>完成,如下图所示: 注:一定要注意第二个SUM函数的区域范围要错位,不然就报错。 04 上下限范围取值 对下面的系数,取下限为0,取上限为2.(如果低于0则为0,如果高于2则取2,0-2之间则取原数)。D2单元格输入公式为: =MIN(MAX(D2,0),2) 按Enter键完成后向下填充。 注:MAX表示取最大值,可用来设定下限;MIN表示取最小值,用来设定上限。 05 计算工龄 计算下表中的员工的工龄。在E2单元格中输入公式: =YEARFRAC(E2,TODAY(),1) 按Enter键完成后向下填充。 注:YEARFRAC函数是指返回 开始日期与结束日期之间的天数占全年天数的百分比。TODAY函数是返回系统当前的日期。 06 忽略指定日期后计算两上日期之间的工作日 计算下面两个日期之间共有多少个工作日。在D2单元格中输入公式: =NETWORKDAYS(B2,C2,G1:G3) 按Enter键完成后向下填充。 注:NETWORKDAYS是计算两个日期之间的工作日天数的函数。 07 修剪平均值 去掉一个最大值,去掉一个最小值然后求平均值。在H2单元格中输入公式: =TRIMMEAN(C2:G2,2/5) 按Enter键完成后向下填充。 注意:简单地可以说成,“掐头去尾”然后求平均值,即通常说的去掉一个最大值,去掉一个最小值后求平均值。先从一组数据中去掉头部与尾部(最大值与最小值)除去一定的百分比的数据点,然后再求平均值。此函数将除去的数据点数量向下舍入为最接近的2的倍数。 语法:=TRIMMEAN(不让平均值区域,掐头去尾的个数/总个数) 08 多工作表求和 下表中是4个月的业绩统计,每个工作表的里面的张成的位置都是一样的,求张成的1-4月的提成统计。在F5单元格中输入公式: =SUM('1月:4月'!C2) 按Enter键完成填充。如下图所示: 09 多工作表求和 下表中是某次活动的一个购买清单,每个金额后面跟了一个元,要求求和。 在D5单元格中输入公式: =SUMPRODUCT(--SUBSTITUTE(D2:D4,'元','')) 按Enter键完成。 注:“--”是减负的意思,强制地把文本型的数字转换成数值型的。然后使用SUMPRODUCT来求和。 10 判断退休年龄 男性退休年龄为60,女性退休年龄为55,如果为总经理或者副总经理的则可以推迟5年退休。在F2单元格中输入公式: =IF(D2='男',60,55)+IF(OR(E2='总经理',E2='副总经理'),5,0) 确定向下填充。 说明:OR函数表示两个条件一个成立时那么就返回指定的结果。 |
|