印刷费公式wps,表格,if

2020年10月2日发(作者：萧正楠)


竭诚为您提供优质文档双击可除

wps,表格,if


篇一：巧用wps表格数组公式分析班级学生成绩
学校对于某 年级各教学班的考试成绩分析，一般要统计
每个班的参加考试人数、及格率、平均分(或每班前若干名< br>学生的平均分)、学生个体在本教学班中的名次等，如果仅
用wps表格的内置函数计算，则需要 一个班一个班的分块后
计算，非常麻烦。笔者通过巧妙组合wps表格的内置函数编
写出数组公 式，简捷方便地完成了以上各种特征量数计算
(如图1所示)。
图1班级学生成绩统计
1、每个班参加考试人数
假设某年级有4个班，学生总数不大于260名，班号在< br>a列(用01、02、03表示各班号)，从261行开始为汇总行。
要统计的是语文学科(c列 )的各班参加考试人数(每个班有
得分的学生数，即非空格数)，则在要计算各班参加考试人
数 的纵向连续的第一个单元格c262中输入公式：
=sum((a$$2:a$$260=0))
按ctrl+shift+enter组合键确认输入后，向下拖动填


第 1 页 共 11 页


充到全部班级单元格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学科及格率


第 2 页 共 11 页


假如语文(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
由上 面几个例子可见，数组公式可以解决许多有条件的
统计问题。为了方便，我们可以根据上面讲到的各数组 公式，
设置成考试成绩统计模板应用于每次考试，起到一劳永逸的


第 3 页 共 11 页


作用。
篇二：excel常用命令文档
lookup的用法
精确查找：
1、以一份学生成绩表为例，要查询的是得分76的学生
的姓名
2、在F2单元格输入=lookup(e2,b2:b8,c2:c8)
3、回车以后得到结果如图所示，学生姓名是李四，查
看左侧表格对应无误
4、该loo kup函数括号内的含义是，其中e2就是我们
查询的得分条件，b2：b8是得分项所在的列区间，也 叫条件
区域，那么c2:c8就是我们要查找的对应区域。
非精确查找：
1、还是这张表格，我们要查询的得分是99，从左侧表
格可以看出，没有得分是99的学生，那么会查 找到什么样
的结果呢？
2、同样在F2单元格输入函数=lookup(e2,b2:b8,c2:c8)
3、看到返回的查找值是孙八
4、结果是怎么来的呢？因为lookup函数对于非精确查
找时，查找到的结果是小于我们的查找值，并且是最接近于
它的对应结果，因为孙八的成绩是98，我 们要查找的条件是
99分，所以该函数就返回了小于99分，同时有最接近99分


第 4 页 共 11 页


的成绩对应的学生姓名。
注意：
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,女男
1518位都可以的公式：转换出生日期：

=iF(len(e2)=18,text(mid(e2,7,8),
t(mid(e2,7,6),
自动录入男女：
=iF(e2=女


第 5 页 共 11 页


男计算年龄（新旧身份
证号都可以）：

=iF(and(e2=
9,2),107-midb(e2,7,2)))
wps表格提取身份证详细信息
前些天领导要求统计所有员工的性别、出生日期、年龄
等 信息，并且要得很急。而我们单位员工人数众多，短时间
内统计相关信息并且输入计算机几乎是不太可能 的。幸好在
以前的一份金山表格中我们曾经统计有所有员工的身份证
号码，而身份证中正有我们 所需要的性别、出生日期、年龄
等信息的。所以，干脆，还是直接在金山表格中从身份证号
码提 取相关的信息吧。
身份证号放在a2单元格以下的区域。我们需要从身份
证号码中提取性 别、出生日期、年龄等相关信息。由于现在
使用的身份证有15位和18位两种。所以，在提取相关信息
时，首先应该判断身份证号码的数字个数，然后再区别不同
情况进行相关处理。
一、身份证号的位数判断
在b2单元格输入如下公式“=len($$a2)”，回车后即可
得到a2单元格身份证号码的数字位数，如图1所示。len($$a2)
公式的含义是求出a2单元格 字符串中字符的个数。由于当


第 6 页 共 11 页


初身份证输入时就是以文本形式输入的，所以用此函数正可
以很方便地求到身份证号码的位数。
二、判断男女性别
第一步：提取性别识别数字。如果是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(b 2=18,mid(a2,17,1),Right(a2,1))”
的作用是“如果b2单元格等于1 8，那么从a2单元格字符串
的第17位开始截取1个字符，否则从a2单元格字符串的右
侧截 取1个字符”。
第二步：根据识别数字判断男女性别，在d2单元格输
入公式“=iF( iseVen($$c2)=tRue,女男”，回车后就可
以得到的结果了。如图3所示。


第 7 页 共 11 页


图3wps中键入函数判断男、女性别
此处函数iseVen($$c2)的作用是判断c2单元格 的数字是
否为偶数，如果是，则返回结果tRue，否则返回结果False。
因此，公式“= iF(iseVen($$c2)=tRue,女男”的含义
就是如果c2单元格数字为偶数，那么在当前 单元格显示“女”，
否则显示
三、出生日期提取
第一步：提取数字型出生 日期。在e2单元格输入公式
“=iF($$b2=18,mid($$a2,7,8),”并回车，
如图4所示。这种形式的出生日期我们并不常用，此处提取
出来只是为了我们下一步计算年龄时要用， 也是一个辅助列。
的意思是把字符“19”和mid($$a2,7,6)
截取到的6个字符合并 在一起成为一个新的字符串。其它的，
就不用再解释了吧？
图4wps中键入函数判断出生日期
第二步：提取带连接符的出生日期。在F2单元格输入
公式
“=iF($$b2=18,mid($$a2,7,4)&
($$a2,13,2),
id($$a2,11,2))”并回车，如图5所示，可以得到我们常用
的如“1977-08-11 ”的格式。此处用到的函数在前面已经有
过解释，所以不再罗嗦了。


第 8 页 共 11 页


图5wps中键入函数判断特殊日期
四、公式计算年龄
在g2单元格输入公式“=yeaR(now())-leFt(e2,4)” 并
回车，年龄计算就这么简单，用当前的年份减去出生的年份
就行了。如图6所示。函数yea R(now())可以返回当前的年
数“20xx”，而leFt(e2,4)则从e2单元格的左侧截 取4个字
符，也就是出生年份。
图6wps中用公式计算年龄
最后，选中 b2:g2单元格，向下拖动填充句柄至最后一
行，那么我们所需要的所有数据就有了，再选中b列、c 列、
e列，并点击右键，在弹出的右键菜单中点击“隐藏”命令，
将这些辅助列隐藏起来，就可 以得到一份完整的表格了。
很简单吧？利用函数功能，只需要设置一下相应的参数，
把一 个个隐藏在身份证号码里的信息提取出来，完成上面所
有操作用时也不到半小时，将所得到的表格美化一 下，就可
以向领导交差了。
一、分析身份证号码
其实，身份证号码与一个 人的性别、出生年月、籍贯等
信息是紧密相连的，无论是15位还是18位的身份证号码，
其中 都保存了相关的个人信息。
15位身份证号码：第7、8位为出生年份(两位数)，第
9 、10位为出生月份，第11、12位代表出生日期，第15位


第 9 页 共 11 页


代表性别，奇数为男，偶数为女。
18位身份证号码 ：第7、8、9、10位为出生年份(四位
数)，第11、第12位为出生月份，第13、14位代表出 生日
期，第17位代表性别，奇数为男，偶数为女。
例如，某员工的身份证号码(15位 )是324，
那么表示1972年8月7日出生，性别为女。如果能想办法
从这些身份证号码中 将上述个人信息提取出来，不仅快速简
便，而且不容易出错，核对时也只需要对身份证号码进行检
查，肯定可以大大提高工作效率。
二、提取个人信息
这里，我们需要使用iF、len、mod、
mid、date等函数从身份证号码中提取个人信 息。如图
1所示，其中员工的身份证号码信息已输入完毕(c列)，出
生年月信息填写在d列， 性别信息填写在b列。
1.提取出生年月信息
由于上交报表时只需要填写出生年月 ，不需要填写出生
日期，因此这里我们只需要关心身份证号码的相应部位即可，
即显示为“72 08”这样的信息。在d2单元格中输入公式
“=iF(len(c2)=15,mid(c2,7,4 ),mid(c2,9,4))”，其中：
len(c2)=15：检查c2单元格中字符串的字 符数目，本
例的含义是检查身份证号码的长度是否是15位。
mid(c2,7,4)：从c2单元格中字符串的第7位开始提取


第 10 页 共 11 页


四位数字，本例中表示提取15位身份证号码的第7、8、9、
10位数字。
m id(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.提取性别信息




第 11 页 共 11 页