wps表格if.docx
- 文档编号:11537344
- 上传时间:2023-03-19
- 格式:DOCX
- 页数:4
- 大小:18.70KB
wps表格if.docx
《wps表格if.docx》由会员分享,可在线阅读,更多相关《wps表格if.docx(4页珍藏版)》请在冰豆网上搜索。
wps表格if
竭诚为您提供优质文档/双击可除
wps,表格,if
篇一:
巧用wps表格数组公式分析班级学生成绩
学校对于某年级各教学班的考试成绩分析,一般要统计每个班的参加考试人数、及格率、平均分(或每班前若干名学生的平均分)、学生个体在本教学班中的名次等,如果仅用wps表格的内置函数计算,则需要一个班一个班的分块后计算,非常麻烦。
笔者通过巧妙组合wps表格的内置函数编写出数组公式,简捷方便地完成了以上各种特征量数计算(如图1所示)。
图1班级学生成绩统计
1、每个班参加考试人数
假设某年级有4个班,学生总数不大于260名,班号在a列(用01、02、03表示各班号),从261行开始为汇总行。
要统计的是语文学科(c列)的各班参加考试人数(每个班有得分的学生数,即非空格数),则在要计算各班参加考试人数的纵向连续的第一个单元格c262中输入公式:
=sum((a$2:
a$260=0))
按ctrl+shift+enter组合键确认输入后,向下拖动填充到全部班级单元格c262:
c265,即可得到各班的该学科参加考试人数。
上述公式为数组公式,在编辑栏输入这种公式后,必须按ctrl+shift+enter组合键确认,确认后在公式两边自动加上花括号{}(如图2)。
图2学科实考统计
三联推荐:
点击免费下载最新版wps办公软件》》查看更多电脑教程
公式解释:
将班号列所在区域a$2:
a$260为某班(0)两个条件做乘法运算。
当两个条件都成立时返回1(tRue*tRue=1),否则返回0(tRue*False=0、False*tRue=0、False*False=0)。
逻辑判断的结果组成0、1数组,通过函数sum()将所有的0、1值求和,得出该学科参加考试人数。
2、每个班所有考生的总平均分
假设e列为总分列,在要计算各班总平均分的纵向连续的第一个单元格e262中输入数组公式:
=aVeRage(iF(a$2:
a$260=0再通过Row(indiRect(1:
=60))/sum((a$2:
a$260=0))
按ctrl+shift+enter组合键确认后向下拖动填充到全部班级单元格g262:
g265,得到各班该学科的及格率(如图5)。
图5学科及格率
假如语文(c列)、数学(d列)中的学科及格分数分别为60分、72分,则计算每个班这两个学科都及格的人数的数组公式为:
=sum((a$2:
a$260=0=60)*(d$2:
d$260;=72))
5、每个学生在本班的名次
一个学生在全年级的名次,可用函数Rank()求出,而该学生在本班内的名次,却没有现成的内置函数可用,我们仍然可以巧用数组公式来解决。
在名次列的第一个学生成绩行所在单元格F2中输入数组公式:
=sum((a$2:
a$260=a2)*(e$2:
e$260;e2),1)
按ctrl+shift+enter组合键确认后向下拖动到最后一行,得到每个学生在本班的名次(如图6)。
图6班内排名次
公式解释:
此公式是通过求本班大于该学生总分的人数与1的和,得到该学生在本教学班的名次。
如下的两个数组公式与上述公式等价(见g列和h列的名次1和名次2):
=sum(((a$2:
a$260=a2)*e$2:
e$260;e2)*1,1)
=sum(iF((a$2:
a$260=a2)*e$2:
e$260;e2,1))+1
由上面几个例子可见,数组公式可以解决许多有条件的统计问题。
为了方便,我们可以根据上面讲到的各数组公式,设置成考试成绩统计模板应用于每次考试,起到一劳永逸的作用。
篇二:
excel常用命令文档
lookup的用法
精确查找:
1、以一份学生成绩表为例,要查询的是得分76的学生的姓名
2、在F2单元格输入=lookup(e2,b2:
b8,c2:
c8)
3、回车以后得到结果如图所示,学生姓名是李四,查看左侧表格对应无误
4、该lookup函数括号内的含义是,其中e2就是我们查询的得分条件,b2:
b8是得分项所在的列区间,也叫条件区域,那么c2:
c8就是我们要查找的对应区域。
非精确查找:
1、还是这张表格,我们要查询的得分是99,从左侧表格可以看出,没有得分是99的学生,那么会查找到什么样的结果呢?
2、同样在F2单元格输入函数=lookup(e2,b2:
b8,c2:
c8)
3、看到返回的查找值是孙八
4、结果是怎么来的呢?
因为lookup函数对于非精确查找时,查找到的结果是小于我们的查找值,并且是最接近于它的对应结果,因为孙八的成绩是98,我们要查找的条件是99分,所以该函数就返回了小于99分,同时有最接近99分的成绩对应的学生姓名。
注意:
lookup函数的使用要求查询条件按照升序排列,所以该函数之前需要对表格进行排序处理。
查询的条件可以高于查询条件列的最大值,但是不能低于查询条件列的最小值。
篇三:
excel表格中根据身份证号码自动填出生日期、计算年龄
excel表格中根据身份证号码自动填出生日期、计算年龄
18位身份证号码转换成出生日期的函数公式:
如果e2中是身份证,在F2中求出出生日期,F2=date(midb(e2,7,4),midb(e2,11,2),midb(e2,13,2))自动录入男女:
=iF(mod((iF(len(e2)=18,mid(e2,17,1),mid(e2,15,1))),2)=0,"女","男")
15/18位都可以的公式:
转换出生日期:
=iF(len(e2)=18,text(mid(e2,7,8),"#-00-00"),"19"&text(mid(e2,7,6),"#-00-00"))
自动录入男女:
=iF(e2="","",iF(mod(Right(leFt(e2,17),1),2)=0,"女","男"))计算年龄(新旧身份
证号都可以):
=iF(and(e2=""),"",iF(midb(e2,7,2)="19",107-midb(e2,9,2),107-midb(e2,7,2)))
wps表格提取身份证详细信息
前些天领导要求统计所有员工的性别、出生日期、年龄等信息,并且要得很急。
而我们单位员工人数众多,短时间内统计相关信息并且输入计算机几乎是不太可能的。
幸好在以前的一份金山表格中我们曾经统计有所有员工的身份证号码,而身份证中正有我们所需要的性别、出生日期、年龄等信息的。
所以,干脆,还是直接在金山表格中从身份证号码提取相关的信息吧。
身份证号放在a2单元格以下的区域。
我们需要从身份证号码中提取性别、出生日期、年龄等相关信息。
由于现在使用的身份证有15位和18位两种。
所以,在提取相关信息时,首先应该判断身份证号码的数字个数,然后再区别不同情况进行相关处理。
一、身份证号的位数判断
在b2单元格输入如下公式“=len($a2)”,回车后即可得到a2单元格身份证号码的数字位数,如图1所示。
len($a2)公式的含义是求出a2单元格字符串中字符的个数。
由于当初身份证输入时就是以文本形式输入的,所以用此函数正可以很方便地求到身份证号码的位数。
二、判断男女性别
第一步:
提取性别识别数字。
如果是18位身份证,那么倒数第二位就是性别判断的识别数字,奇数为男,偶数则为女。
而如果是15位身份证,那么倒数第一位则是性别识别数字,同样奇数为男,偶数为女。
所以首先应该提取这两种身份证的性别识别数字。
在c2单元格输入公式“=iF(b2=18,mid(a2,17,1),Right(a2,1))”,回车后,立刻可以得到相应的识别数字了。
如图2所示。
图2wps中键入函数
函数mid(a2,17,1)的作用是从a2单元格字符串的第17个字开始截取1个字符,这正好是倒数第二个字符;函数Right(a2,1)的作用则是从a2单元格字符串的右侧开始截取1个字符,也就是倒数第一个字符。
至于iF函数,则进行相应的判断。
公式“=iF(b2=18,mid(a2,17,1),Right(a2,1))”的作用是“如果b2单元格等于18,那么从a2单元格字符串的第17位开始截取1个字符,否则从a2单元格字符串的右侧截取1个字符”。
第二步:
根据识别数字判断男女性别,在d2单元格输入公式“=iF(iseVen($c2)=tRue,"女","男")”,回车后就可以得到的结果了。
如图3所示。
图3wps中键入函数判断男、女性别
此处函数iseVen($c2)的作用是判断c2单元格的数字是否为偶数,如果是,则返回结果tRue,否则返回结果False。
因此,公式“=iF(iseVen($c2)=tRue,"女","男")”的含义就是如果c2单元格数字为偶数,那么在当前单元格显示“女”,否则显示
三、出生日期提取
第一步:
提取数字型出生日期。
在e2单元格输入公式“=iF($b2=18,mid($a2,7,8),"19"&mid($a2,7,6))”并回车,如图4所示。
这种形式的出生日期我们并不常用,此处提取出来只是为了我们下一步计算年龄时要用,也是一个辅助列。
"19"&mid($a2,7,6)的意思是把字符“19”和mid($a2,7,6)截取到的6个字符合并在一起成为一个新的字符串。
其它的,就不用再解释了吧?
图4wps中键入函数判断出生日期
第二步:
提取带连接符的出生日期。
在F2单元格输入公式“=iF($b2=18,mid($a2,7,4)&"-"&mid($a2,11,2)&"-"&mid($a2,13,2),"19"&mid($a2,7,2)&"-"&mid($a2,9,2)&"-"&mid($a2,11,2))”并回车,如图5所示,可以得到我们常用的如“1977-08-11”的格式。
此处用到的函数在前面已经有过解释,所以不再罗嗦了。
图5wps中键入函数判断特殊日期
四、公式计算年龄
在g2单元格输入公式“=yeaR(now())-leFt(e2,4)”并回车,年龄计算就这么简单,用当前的年份减去出生的年份就行了。
如图6所示。
函数yeaR(now())可以返回当前的年数“20xx”,而leFt(e2,4)则从e2单元格的左侧截取4个字符,也就是出生年份。
图6wps中用公式计算年龄
最后,选中b2:
g2单元格,向下拖动填充句柄至最后一行,那么我们所需要的所有数据就有了,再选中b列、c列、e列,并点击右键,在弹出的右键菜单中点击“隐藏”命令,将这些辅助列隐藏起来,就可以得到一份完整的表格了。
很简单吧?
利用函数功能,只需要设置一下相应的参数,把一个个隐藏在身份证号码里的信息提取出来,完成上面所有操作用时也不到半小时,将所得到的表格美化一下,就可以向领导交差了。
一、分析身份证号码
其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是15位还是18位的身份证号码,其中都保存了相关的个人信息。
15位身份证号码:
第7、8位为出生年份(两位数),第9、10位为出生月份,第11、12位代表出生日期,第15位代表性别,奇数为男,偶数为女。
18位身份证号码:
第7、8、9、10位为出生年份(四位数),第11、第12位为出生月份,第13、14位代表出生日期,第17位代表性别,奇数为男,偶数为女。
例如,某员工的身份证号码(15位)是320521720807024,那么表示1972年8月7日出生,性别为女。
如果能想办法从这些身份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错,核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率。
二、提取个人信息
这里,我们需要使用iF、len、mod、
mid、date等函数从身份证号码中提取个人信息。
如图1所示,其中员工的身份证号码信息已输入完毕(c列),出生年月信息填写在d列,性别信息填写在b列。
1.提取出生年月信息
由于上交报表时只需要填写出生年月,不需要填写出生日期,因此这里我们只需要关心身份证号码的相应部位即可,即显示为“7208”这样的信息。
在d2单元格中输入公式“=iF(len(c2)=15,mid(c2,7,4),mid(c2,9,4))”,其中:
len(c2)=15:
检查c2单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位。
mid(c2,7,4):
从c2单元格中字符串的第7位开始提取四位数字,本例中表示提取15位身份证号码的第7、8、9、10位数字。
mid(c2,9,4):
从c2单元格中字符串的第9位开始提取四位数字,本例中表示提取18位身份证号码的第9、10、11、12位数字。
iF(len(c2)=15,mid(c2,7,4),mid(c2,9,4)):
iF是一个逻辑判断函数,表示如果c2单元格是15位,则提取第7位开始的四位数字,如果不是15位则提取自第9位开始的四位数字。
如果需要显示为“70年12月”这样的格式,请使用date格式,并在“单元格格式→日期”中进行设置。
2.提取性别信息
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- wps 表格 if