Excel 重复名次也可以查姓名成绩

　　当老师的，对分析学生成绩大概有瘾。这不，本来我们已经把学生各学科的成绩、总分、名次都排出来了，并按照总分进行了升序排序，但现在又有任课老师过来要求希望能够把自己学科的前10名的学生姓名及成绩找出来。按理说，这个要求并不是很困难，但是麻烦就在于学生各科名次有可能相同，这样的话，前10名的学生其实不一定是10个人，有可能更多。每个学科都要这么做的话，工作量也不小，所以，还是得靠函数和公式来帮忙。

图1 原始成绩表

　　原始的成绩表如图1所示。姓名位于C2:C92单元格，语文成绩位于D2:D92单元格区域。我们就以查找语文学科的前10名成绩及学生姓名为例。为方便比较结果，图1中我们已经将数据按语文成绩降序进行了排序，实际操作中是不需要事先排序的。

　　一、名次表的建立

　　前面我们说过，我们不太容易确定排在前10名的学生共有多少，所以，我们需要使用公式将它们找出来。当然，最好顺便将名次表填写出来。完成结果如图2所示。

图2 成绩排序

　　将鼠标定位于X3单元格，然后在编辑栏输入公式“=TEXT(SUMPRODUCT(($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92)),"第G/通用格式名")”，回车后就可以得到“第1名”的结果。选定X3单元格，向下拖动其填充句柄至出现“第11名”为止。

　　这里用到了几个函数，感觉上比较复杂。其实思路是这样的：“ROW(1:1)”的结果是“1”，而“LARGE($D$2:$D$92,1)”的结果是在指定的单元格区域中最大的一个数；那么公式中“($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))”可以理解为拿D2:D92单元格区域中的数据与该区域中最大值比较，大于或等于该值及小于该值的则会分别以“TRUE”、“FALSE”的结果保存在一个数组中。

　　公式中“COUNTIF($D$2:$D$92,$D$2:$D$92))”部分则会统计D2:D92单元格区域中每一个数值出现的次数，也分别保存到一个数组中。所以，我们所用公式中“SUMPRODUCT(($D$2:$D$92>=LARGE($D$2:$D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92))”在执行时会得到一个类似于“SUMPRODUCT({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;…}/{1;1;2;2;1;2;2;1;2;2;2;2;1;…})”的结果。两个数组中的对应的数据分别相除，再将所有的商相加，正是分数所对应的名次。这种方法即使名次是并列的，也不会影响显示效果。

　　至于最外层的TEXT函数，则是将得到的结果转换为按指定数字格式表示的文本。也就是本来内层公式运算的结果是数字“1”，现在我们将它显示为“第1名”。

　　二、分数的查找

　　将鼠标定位于Y3单元格，在编辑栏中输入如下公式“=INDEX($D$2:$D$92,MATCH(LARGE($D$2:$D$92+1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92+1/ROW($D$2:$D$92),0))”，然后按下“Ctrl+Shift+Enter”快捷键，完成数组公式的输入。这一步很关键的，否则不会出现正确的结果。

　　向下拖动Y3单元格的填充句柄向下至最后一个单元格完成公式的复制。

　　我们还是简单解释一下公式的思路。

　　由于D2:D92区域中有很多数据是重复的，这给我们造成了困难。所以，我们要想办法使每一数据都变成唯一。公式中“$D$2:$D$92+1/ROW($D$2:$D$92)”就是给D2:D92区域中每一个数据都加了该数据对应行数的倒数。由于每一数据对应的行数是不一样的，这样，就会使每一数据都变成了唯一的值，并保存到了一个数组中。

　　公式中的“LARGE($D$2:$D$92+1/ROW($D$2:$D$92),ROW(1:1))”还是返回了上面所得数组中的最大值。本例中的结果是“｛96.5｝”。

　　公式中“MATCH(LARGE($D$2:$D$92+1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92+1/ROW($D$2:$D$92),0)”返回的是刚刚得到的最大值在数组中的位置。本例中的结果是“｛1｝”。

　　这样，其实Excel最后执行的查询就是“INDEX($D$2:$D$92,1)”了，自然可以返回在$D$2:$D$92区域中的第一个值了。

　　三、姓名的查找

　　将鼠标定位于Z3单元格，在编辑栏中输入公式“=INDEX($C$2:$C$92,MATCH(LARGE($D$2:$D$92+1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92+1/ROW($D$2:$D$92),0))”，同样按下“Ctrl+Shift+Enter”快捷键完成数组公式的输入。

　　向下拖动Z3单元格的填充句柄向下至最后一个单元格完成公式的复制。最后的效果如图3所示。

图3 完成公式的复制

　　其实您肯定已经明白了，姓名的查找与前面分数的查找是一样的。公式本身也没有什么大的变化。所以，明白了前面的方法，要查找别的什么东西也就方便了。

　　其它的学科可以照此办理。只要注意变换一下公式中的单元格区域就可以了，我这里就不罗嗦了。

Excel用SUMIF函数实现按指定条件求平均值

　　作者： 王道才 原创

　　Excel 2003中的条件求和SUMIF函数非常实用，例如在年级段总成绩表中计算某科教师所教的所有班级成绩的平均分(如5到8班化学老师的平均分)，就可以利用如下方法实现：

　　在准备放该化学教师所教所有班级平均分的单元格中输入=SUMIF(K2:K132,">4",G2:G132)/COUNTIF(K2:K132,">4")回车即可，这里边用到了SUMIF和COUNTIF两个函数。(如图1)

图1

　　SUMIF函数是按给定条件对指定单元格进行求和的函数。其语法格式是：SUMIF(range,criteria,sum_range)，range是要根据条件进行计算的单元格区域，每个区域中的单元格都必须是数字和名称、数组和包含数字的引用，空值和文本值将被忽略。criteria 是指对range指定的区域实行什么条件，其形式可以为数字、表达式或文本。如条件可以表示为 32、"32"、">32" 或 "apples";sum_range是要进行相加的实际单元格，如果省略Sum_range，则当区域中的单元格符合条件时，它们既按条件计算，也执行相加。

　　注意：Sum_range 与Range的大小和形状可以不同，相加的实际单元格从sum_range 中左上角的单元格作为起始单元格，然后包括与range大小和形状相对应的单元格。公式中range是指“K2:K132”，也就是“班级”这列所有单元格;criteria是指“">4"”，意思是指班级数大于4的5、6、7、8班;而sum_range是指“化学”这列成绩，意思是对符合“班级”条件的化学分数求和。而整个SUMIF函数公式是计算所有班级为5、6、7和8班的同学的化学成绩的总和。

　　COUNTIF函数是统计指定区域中满足给定条件的单元格个数的函数。其语法格式是：COUNTIF(range,criteria)，range是一个或多个要计数的单元格，其中包括数字或名称、数组或包含数字的引用，空值和文本值将被忽略。criteria 是指按什么条件进行统计，其形式可以为数字、表达式、单元格引用或文本。

　　公式中range是指“K2:K132”，也就“班级”这列所有单元格;criteria是指“">4"”，意思也指班级数大于4的5、6、7、8班。整个COUNTIF函数公式是统计班级这列中班级数为5、6、7和8班的同学的总人数。而整个公式就是等于所有班级为5、6、7和8班的同学的化学成绩总和除以班级数为5、6、7和8班的同学的总人数。

　　如果每科教师所教的班级一样，还可以在公式中加入$，以便进行拖动填充($为绝对应用，拖动公式时所引应用的单元格不发生变化)，如：=SUMIF($K$2:$K$132,">4",G2:G132)/COUNTIF($K$2:$K$132,">4")。(如图2)

图2

善用Word文档版本功能 跟踪文档修改历史

　　作者： 编辑整理

　　如果希望得记录对Office文档的更改，可以在同一个文档中保存多个文档版本。由于 Microsoft Word 仅保存版本间的差别而不是每个版本的完整副本，该方法也可节省磁盘空间。保存文档的几个版本后，可以退回审阅、打开、打印和删除早期的版本。

　　保存文档的一个版本

　　·手动 如果在任何时刻您希望保存文档当前状态的一个“快照”，可以保存文档的一个版本。例如，可以在送交审阅时保存一个版本，合并审阅修订后再保存另外一个版本。

　　·自动 在每次关闭文档时，可以使 Word 自动保存文档的一个版本。如果您需要记录文档修订的作者和时间(例如对于法律文档)，该功能非常有用。

　　将版本保存为独立的文件

　　在一个文件中保存了同一文档的多个版本后，在以下两种情况下，可以将特定的版本保存为独立的文件：

　　·当送交审阅的文档中包含多个版本，但只需发送最新的或特定的版本时。该方法可以防止审阅者打开文档的早期版本。

　　·如果要将文档的某个早期版本与当前版本进行比较。可以使用“工具”菜单上的“比较并合并文档”命令比较不同的文档。

　　注释 保存多个版本不同于保存文档的备份副本，保存备份副本设计用于确认不发生数据丢失或无意中的更改。启用了“保存备份”功能后，每次保存文档时新的备份都会取代原有的备份。

　　防止文档的版本信息丢失

　　将文档另存为网页时，虽然 Microsoft Word 保持了大量原有信息，但是不保存文档的版本信息。若要保存版本信息，请在另存为网页前将文件的一个副本保存为文档。

妙用三键 密藏文档

在多人共用的电脑中，为了防止别人看到自己的秘密，每个人对各自存放的文档都采取了一定的保护措施，要么给文件或文件夹设置隐藏属性，要么对文档进行密码保护，但这些都是俗招！笔者最近另辟蹊径，发现了一个保护Word文档的新招，只要同时按下三个键即可将文档内容隐藏，别人打开后里面空空如也。

第一步：启动Word，执行"工具→宏→录制新宏......"命令，打开"录制宏"窗口，在"宏名"栏中为所录制的新宏取名为Hidden，在"将宏保存在"下拉框中选择"所有文档(Normal.dot)"选项。在"将宏指定到"工具框中，单击"键盘"按钮，打开"自定义键盘"窗口，光标定位在"请按新快捷键"文本框中，此时进行键盘操作，按下Alt+Ctrl+H组合键，这个快捷键组合就会出现在"请按新快捷键"文本框中，单击窗口"指定"按钮，将快捷键指定给Hidden宏，最后单击"关闭"按钮，进入宏录制状态。　

第二步：执行"编辑→全选"菜单命令，选定所有文本。再执行"格式→字体"菜单命令，打开"字体"设置对话框，在"字体"选项卡中，选中"效果"单选框中的"隐藏文字"选项，单击"确定"按钮。返回后再单击工具栏上的"保存"按钮。单击"停止录制"，结束Hidden宏的录制。

第三步：录制Show宏，同样选择"录制新宏"选项，将宏取名为Show，在"将宏保存在"下拉框中选择"所有文档(NormaJ.dot)"选项，将Show宏的快捷键指定为Alt+Ctrl+S(操作方法与给Hidden宏指定快捷键的方法相同)。

第四步：进入宏录制状态后，执行"编辑→全选"菜单命令，再执行"格式→字体"菜单命令。如果"字体"选项卡中"隐藏文字"的单选框中有"√"，则单击将"√"去掉，使该选项不被选中，然后单击"确定"按钮。单击"停止录制"，结束Show宏的录制。

第五步：执行"工具→宏→宏......"菜单命令，在"宏"窗口中找到Hidden宏和Show宏，分别选中Hidden宏或Show宏，接着单击"宏"窗口右边的"编辑"按钮，此时Word 2000中的Visual Basic编辑器被打开，Hidden宏和Show宏的代码出现在编辑器的代码窗口中。对Hidden宏和Show宏代码进行修改，将字体、字号等属性删除，只保留Hidden(隐藏)属性，修改完成后，单击编辑器菜单栏上的"文件→保存Normal"命令，最终得到Hidden宏和Show宏的代码。　

下面试一下吧，打开一个已编辑好的Word，按下Alt+Ctrl+H组合键，运行Hidden宏，此时文档隐藏内容后自动存盘，最后关闭文档。当别人再次打开该文档时，惊！文档是空的，什么内容也看不到，真是一头雾水！要想显示文档内容，只需按下Alt+Ctrl+S组合键即可，此秘密自己知道就行了，千万别泄露哦！使用快捷键来运行Hidden宏和Show宏，不仅操作简便，而且提高了此法的保密性，隐藏、显示随心所欲。此法对含有表格、图片、文本框、超级链接等多种属性格式的文档均有效。

上一篇: DM硬盘分区工具使用说明
下一篇: “云计算”时代
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags: 电脑 Excel
相关日志: