首先要说明的是,本次作品,没有任何的VBA后台编程技术,全是运用Excel工作表函数+名称管理的形式智能完成的哦,嘿嘿! 一、原始简单数据的准备 我以简略的学生成绩名次数据准备如下图所示。 图1 原始学生名次数据准备 二、利用名称管理器进行名称定义 在Excel中利用名称,可以简化很多复杂的冗长公式,看起来也很简洁。主要涉及三个名称定义“名字”、“查找的名次”、“成绩排名变化情况”,其定义分别如下(定义名称前,必须首先选中“图1”的单元格“F1”,然后打开如下“图2”的名称管理器,点击“新建”准备开始创建名称)。 图2 打开名称管理其界面 (一) “名字”名称的定义。如下图所示 图3 “名字”名称内容设定 必要的解释: REPT(' ',IF(LEN(Sheet1!A1)=4,2,IF(LEN(Sheet1!A1)=3,4,IF(LEN(Sheet1!A1)=2,6)))),该REPT函数是根据实际学生名字Sheet1!A1的长度判断应该重复产生多少给空格的意思,以方便显示名次变化情况对齐效果。形如下图效果 图4 用REPT函数产生名词变化对其效果 (二) “查找的名次”名称的定义。如下图所示 图5 “查找的名次”名称内容设定 必要的解释:VLOOKUP(Sheet1!A1,Sheet1!$C$1:$D$47,2,FALSE)代表根据学生名字精准查找该学生在新的成绩中的新名次情况 (三) “成绩排名变化情况”名称的定义。如下图所示 图6 “成绩排名变化情况”名称内容设定 必要的解释:IF(查找的名次>Sheet1!B1,'同学下降了【' & 查找的名次-Sheet1!B1 & '】名',IF(查找的名次=Sheet1!B1,'同学没有名次变化','同学上升了【' & Sheet1!B1-查找的名次 & '】名'))代表通过IF函数根据学生原始名次和新的名次精准比对得出上升、下降或者未发生异动的情况。 三、统计上升、下降、名次未发生异动的情况 (一)名次上升情况的统计。如下图 图7 上升名次的情况统计 (二)名次下降情况的统计。如下图 图8 下降名次的情况统计 (三)名次未发生异动情况的统计。如下图 图9 未发生异动名次的情况统计 (四)必要的通配符说明 说明:COUNTIF(F1:F47,'*上升了*')中的条件'*上升了*'的'*'表示任意字符的通配意思,'*上升了*'表示包含'上升了'的意思 四、自动填充测试名次变化情况 (一)在F1单元格输入“=名字 & 成绩排名变化情况”。如下图所示 图10 “F1”单元格输入初始公式 (二)然后用自动填充的方式,可以看到如下的效果展示。如下图所示 图11 名次情况变化展示 (三)随后,我们可以看到名次情况统计信息。如下图所示 图12 名次情况统计信息 好了,我们的分享就到这里,希望能够给予给为工作上有所帮助,此案例不仅对教学,也可以运用于非教学工作如员工的考评情况的变化测试。 最后,还是非常感谢各位的长期关注(头条号:跟我学Office高级办公)、推广和点评哦,谢谢! |
|
来自: 杞乡青年 > 《教育教学电子表格》