Excel函数实例在间隔数据中排定名次

【 tulaoshi.com - excel 】

Excel函数实例,在间隔数据中排定名次

　　用Excel表格做了学生考试成绩汇总表，格式如图1所示。需要根据AA列中相应科目的成绩排出名次，并将结果放到AB列相应的单元格中。

　　图1

　　排定名次要使用RANK函数，其语法是RANK(number,ref,order)其中，参数number是需要找到排位的数字;而参数ref则是数字列表的引用;第三个参数如果省略则是按降序排列，这正是我们需要的。比如公式=RANK(A3,A2:A6)的意思就是要得到A3单元格数据在A2:A6单元格数据中的排名。

　　显然，在本例中要使用RANK函数，但有一个问题是必须要解决的。假设我们要针对语文学科的总分来排名次。学生们的语文总成绩分布在AA4、AA12、AA20等单元格中，所处单元格区域并不连续，每8行有一个成绩。怎样才能将它们选中，并作为排名的区域呢?

　　这个问题也曾经困扰了我很长时间，最后发现利用求余数函数可以使问题得到顺利解决。不过，有些准备工作是要做的。

　　还是以语文成绩的排名为例。

　　先将AA列的数据复制到别的位置，等准备工作完成后再粘贴回来。

　　先选中AA4单元格，输入公式=1/(MOD(ROW(),8)-4)，回车后即可发现出现#DIV/0!的错误提示。拖动该单元格填充句柄向下至AA379，则会从AA4单元格开始，每8行出现相同的错误提示，其它各行均为数字。

　　点击功能区开始选项卡编辑功能组查找和选择按钮，在弹出的菜单中点击定位条件命令，打开定位条件对话框。选中公式单选项，并只保留选中随后出现的错误复选项，如图2所示。确定后就可以发现，凡是出现错误的提示的单元格就处于被选中状态了。

　　图2

　　现在点击功能区公式选项卡定义的名称功能组定义名称按钮，在打开的新建名称对话框的名称输入框中输入ymzf。确定关闭对话框。

　　以后只要我们在名称框中输入ymzf，回车，就可以再次选中AA列中全部语文学科对应的单元格了，如图3所示。

　　图3

　　按照上面的方法，只要能让错误提示分别出现在相应学科所在行，那么就可以利用定位条件来选中它们。因为语文学科所处的单元格所在行除以8的余数为4，所以我们采用公式=1/(MOD(ROW(),8)-4)制造了除数为0的错误提示。那么数学、英语等其它学科则可以分别根据其行数除以8的余数不同，重复上面的操作过程，只是将公式分母中-4分别变成-5、-6、-7、-0、1、2、3就可以了。将所到的各学科区域分别以sxzf、yyzf等名称命名。

　　但准备工作仅仅做这些还是不够的。因为我们排出的名次应该放在AB列而不是AA列。所以我们还要在再用上面的方法在AB列中选中各学科对应的区域，并分别以ymmc、sxmc、yymc等名称命名，以便将来在这些区域中输入不同的公式。

　　至此，我们的准备工作才算是完成了。现在我们可以将临时放到别处的总分粘贴回AA列单元格中了，再剩下的事儿就是用RANK函数排名的问题了。咱还是先根据语文成绩排名吧。

　　先在名称栏输入ywmc，回车，将AB列语文学科所对应的单元格全部选中，此时AB372单元格会(图老师整理)处于被激活状态。我们只要在编辑栏输入公式=RANK(AA372,ywzf)，并按下Ctrl+Enter就可以在全部选中的单元格中输入公式并得到名次结果了。最后的结果如图1所示。

　　其它学科的名次排定依此法办理。够简单吧?

　　至此，我们针对各学科的排名工作就算是大功告成了。

        注：更多精彩教程请关注图老师办 公软件教程栏目，图老师电脑办公群:189034526欢迎你的加入

教你使用Excel做数据分析之回归分析方法

　　在数据分析中，对于成对成组数据的拟合是经常遇到的，涉及到的任务有线性描述，趋势预测和残差分析等等。很多专业读者遇见此类问题时往往寻求专业软件，比如在化工中经常用到的Origin和数学中常见的MATLAB等等。它们虽很专业，但其实使用Excel就完全够用了。我们已经知道在Excel自带的数据库中已有线性拟合工具，但是它还稍显单薄，今天我们来尝试使用较为专业的拟合工具来对此类数据进行处理。

　　注：本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择工具-加载宏，在安装光盘支持下加载分析数据库。加载成功后，可以在工具下拉菜单中看到数据分析选项

　　实例 某溶液浓度正比对应于色谱仪器中的峰面积，现欲建立不同浓度下对应峰面积的标准曲线以供测试未知样品的实际浓度。已知8组对应数据，建立标准曲线，并且对此曲线进行评价，给出残差等分析数据。

　　这是一个很典型的线性拟合问题，手工计算就是采用最小二乘法求出拟合直线的待定参数，同时可以得出R的值，也就是相关系数的大小。在Excel中，可以采用先绘图再添加趋势线的方法完成前两步的要求。

　　选择成对的数据列，将它们使用X、Y散点图制成散点图。

　　在数据点上单击右键，选择添加趋势线-线性，并在选项标签中要求给出公式和相关系数等，可以得到拟合的直线。

　　由图中可知，拟合的直线是y=15620x+6606.1，R2的值为0.9994。

　　因为R2 0.99，所以这是一个线性特征非常明显的实验模型，即说明拟合直线能够以大于99.99%地解释、涵盖了实测数据，具有很好的一般性，可以作为标准工作曲线用于其他未知浓度溶液的测量。

　　为了进一步使用更多的指标来描述这一个模型，我们使用数据分析中的回归工具来详细分析这组数据。

　　在选项卡中显然详细多了，注意选择X、Y对应的数据列。常数为零就是指明该模型是严格的正比例模型，本例确实是这样，因为在浓度为零时相应峰面积肯定为零。先前得出的回归方程虽然拟合程度相当高，但是在x=0时，仍然有对应的数值，这显然是一个可笑的结论。所以我们选择常数为零。

　　回归工具为我们提供了三张图，分别是残差图、线性拟合图和正态概率图。重点来看残差图和线性拟合图。

　　在线性拟合图中可以看到，不但有根据要求生成的数据点，而且还有经过拟和处理的预测数据点，拟合直线的参数会在数据表格中详细显示。本实例旨在提供更多信息以起到抛砖引玉的作用，由于涉及到过多的专业术语，请各位读者根据实际，在具体使用中另行参考各项参数，此不再对更多细节作进一步解释。

　　残差图是有关于世纪之与预测值之间差距的图表，如果残差图中的散点在中州上下两侧零乱分布，那么拟合直线就是合理的，否则就需要重新处理。

　　更多的信息在生成的表格中，详细的参数项目完全可以满足回归分析的各项要求。下图提供的是拟合直线的得回归分析中方差、标准差等各项信息。

Excel技巧：快速录数据,让单位自动补全

　　用Excel做表格，经常遇到需要在某一列使用同一单位的情况。如果先在第一个单元格输入对应单位的名称，然后使用拖拽的方法使其它行复制这一单位，达到的效果不尽人意，要么需要再改(图老师整理)单位前的数值，要么单位与数值分在两列。

　　我们通过预先设置某一列的格式可以让这些单位自动补全。

　　这里以将某一列的单位设置为厘米(CM)，最终的效果是在该列任一单元格中输入数字后，按下回车键或将光标移至其它单元格，在数字后会自动补齐CM。

　　第一步：运行Excel，单击选中需要设置格式的列。右键单击选中的列，在弹出的快捷菜单中选择设置单元格格式(如图1)。

　　第二步：在打开的单元格格式对话框中，在数字标签中，选中自定义项，在右侧类型(T):下方的文本框中输入0.00"CM"(如图2,不含外侧引号)，最后单击确定按钮。

　　经过这样设置之后，在该列单元格中输入数据时，不用再考虑单位问题了，直接输入数字即可。

　　小提示：其中0.00表示该列单元格保留两位小数，小数点后面几个0就代表几位小数，具体多少可根据需要自行设置。如果是其它单位符号，只要更改双引号中的CM为相应的字符即可。

        注：更多精彩教程请关注图老师办 公软件教程栏目，图老师电脑办公群:189034526欢迎你的加入

在Excel中实现多条件求和的三种方法

　　在平时的工作中经常会遇到多条件求和的问题。如图1所示各产品的销售业绩工作表，我们希望分别求出东北区和华北区两部门各类产品的销售业绩，或者在同一部门中的不同组也要求出各产品的销售业绩。在Excel中，我们可以有三种方法实现这些要求。

　　　　一、分类汇总法

　　首先选中A1:E7全部单元格，点击菜单命令数据→排序，打开排序对话框。设置主要关键字和次要关键字分别为部门、组别，如图2所示。确定后可将表格按部门及组别进行排序。

　　然后将鼠标定位于数据区任一位置，点击菜单命令数据→分类汇总，打开分类汇总对话框。在分类字段下拉列表中选择部门，汇总方式下拉列表中选择求和，然后在选定汇总项的下拉列表中选中A产品、B产品、C产品复选项，并选中下方的汇总结果显示在数据下方复选项，如图3所示。确定后，可以看到，东北区和华北区的三种产品的销售业绩均列在了各区数据的下方。

　　再点击菜单命令数据→分类汇总，在打开的分类汇总对话框中，设置分类字段为组别，其它设置仍如图3所示。注意一定不能勾选替换当前分类汇总复选项。确定后，就可以在区汇总的结果下方得到按组别汇总的结果了。如图4所示。

　　　二、输入公式法

　　上面的方法固然简单，但需要事先排序，如果因为某种原因不能进行排序的操作的话，那么我们还可以利用Excel函数和公式直接进行多条件求和。

　　比如我们要对东北区A产品的销售业绩求和。那么可以点击C8单元格，输入如下公式:=SUMIF($A$2:$A$7,"=东北区",C$2:C$7)。回车后，即可得到汇总数据。

　　选中C8单元格后，拖动其填充句柄向右复制公式至E8单元格，可以直接得到B产品和C产品的汇总数据。

　　而如果把上面公式中的东北区替换为华北区，那么就可以得到华北区各汇总数据了。

　　如果要统计东北区中辽宁的A产品业绩汇总，那么可以在C10单元格中输入如下公式:=SUM(IF($A$2:$A$7="东北区",IF($B$2:$B$7="辽宁",Sheet1!C$2:C$7)))。然后按下Ctrl+Shift+Enter键，则可看到公式最外层加了一对大括号(不可手工输入此括号)，同时，我们所需要的东北区辽宁组的A产品业绩和也在当前单元格得到了，如图5所示。

　　拖动C10单元格的填充句柄向右复制公式至E10单元格，可以得到其它产品的业绩和。

　　把公式中的东北区、辽宁换成其它部门或组别，就可以得到相应的业绩和了。

　　　　三、分析工具法

　　在EXCEL中还可以使用多条件求和向导来方便地完成此项任务。不过，默认情况下EXCEL并没有安装此项功能。我们得点击菜单命令工具→加载宏，在打开的对话框中选择多条件求和向导复选项，如图6所示。准备好Office 2003的安装光盘，按提示进行操作，很快就可以安装完成。完成后在工具菜单中会新增添向导→条件求和命令。

　　先选取原始表格中A1:E7全部单元格，点击向导→条件求和命令，会弹出条件求和的向导对话框，在第一步中已经会自动添加了需要求和计算的区域，如图7所示。

　　点击下一步，在此步骤中添加求和的条件和求和的对象。如图8所示。在求和列下拉列表中选择要求和的数据所在列，而在 条件列中指定要求和数据应满足的条件。设置好后，点击添加条件将其添加到条件列表中。条件列可多次设置，以满足多条件求和。

　　点击下一步后设置好结果的显示方式，然后在第四步中按提示指定存放结果的单元格位置，点击完成就可以得到结果了。

　　如果要对多列数据按同一条件进行求和，那么需要重复上述操作。