教育应用贴（分班、统计、课表等）1142楼添加运动会成绩统计表20121215

lhx120824

INDEX 函数   帮助文件中的解释是：
本文介绍 Microsoft Excel 中 INDEX 函数 （函数:函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。）的公式语法和用法。

说明
返回表格或区域中的值或值的引用。函数 INDEX 有两种形式：数组 （数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）形式和引用形式。

如果需要 则参阅
返回指定单元格或单元格数组的值 数组形式
返回指定单元格的引用 引用形式
数组形式
说明
返回表格或数组 （数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）中的元素值，此元素由行号和列号的索引值给定。

当函数 INDEX 的第一个参数为数组常量时，使用数组形式。

语法
INDEX(array, row_num, [column_num])INDEX 函数语法具有下列参数 （参数:为操作、事件、方法、属性、函数或过程提供信息的值。）：

Array    必需。单元格区域或数组常量。
如果数组只包含一行或一列，则相对应的参数 row_num 或 column_num 为可选参数。
如果数组有多行和多列，但只使用 row_num 或 column_num，函数 INDEX 返回数组中的整行或整列，且返回值也为数组。
Row_num    必需。选择数组中的某行，函数从该行返回数值。如果省略 row_num，则必须有 column_num。
Column_num    可选。选择数组中的某列，函数从该列返回数值。如果省略 column_num，则必须有 row_num。
说明
如果同时使用参数 row_num 和 column_num，函数 INDEX 返回 row_num 和 column_num 交叉处的单元格中的值。
如果将 row_num 或 column_num 设置为 0（零），函数 INDEX 则分别返回整个列或行的数组数值。若要使用以数组形式返回的值，请将 INDEX 函数以数组公式 （数组公式:数组公式对一组或多组值执行多重计算，并返回一个或多个结果。数组公式括于大括号 ({ }) 中。按 Ctrl+Shift+Enter 可以输入数组公式。）形式输入，对于行以水平单元格区域的形式输入，对于列以垂直单元格区域的形式输入。若要输入数组公式，请按 Ctrl+Shift+Enter。
注释   在 Excel Web App 中，不能创建数组公式。

Row_num 和 column_num 必须指向数组中的一个单元格；否则，函数 INDEX 返回错误值 #REF!。

lhx120824

要学习函数 INDEX 的使用方法请参看帖子：
史上最牛查找函数组合 Index + Match 一看就会! [老搭档 新感觉]  
http://club.excelhome.net/viewth ... 26amp%3Btypeid%3D39

和
【INDEX函数动态图解】
[url=http://club.excelhome.net/viewth ... 9&page=4#pid3491418]http://club.excelhome.net/viewth ... 9&page=4#pid3491418[/url]

MATCH函数动态图解
http://club.excelhome.net/viewth ... 9&page=7#pid3493838



[ 本帖最后由 lhx120824 于 2011-1-13 10:40 编辑 ]

lhx120824

为了学习方便，在此借用一下【查找函数动态图解】
中的操作动画：



[ 本帖最后由 lhx120824 于 2011-1-13 10:43 编辑 ]

lhx120824

版主xiangzi728  
在10楼的
OFFSET函数 动态图解
OFFSET函数返回的是区域引用，不能作为普通公式单独使用，初学者理解比较困难，下面是OFFSET函数引用区域的动态图解，能直观地了解参照地址与各参数及新的引用地址之间的关系。


要学习其他的函数的动态图解，请参阅原帖子
http://club.excelhome.net/viewth ... 26amp%3Btypeid%3D41

[ 本帖最后由 lhx120824 于 2011-1-13 10:50 编辑 ]

lhx120824

如何完成下面的表格：
可用多种方法完成，其中一个就是用一个多单元格数组公式。
稍后提供。


[ 本帖最后由 lhx120824 于 2011-1-13 16:06 编辑 ]

lhx120824

再回到前面楼层提到的如下图效果的公式：

piny 提供的公式：
=IF(MOD(COLUMN(A:T),5),ROW(1:17)+INT(COLUMN(A:O)/5)*17,"")

粉红色部分是我原先设想用的mmult函数得到的，绕了一个弯，多此一举了。
=MMULT(ROW(1:17)+17*(COLUMN($A:$I)-1),1*(ROW($1:$9)=INT(TRANSPOSE((ROW($1:$45)-1)/5)+1)))


[ 本帖最后由 lhx120824 于 2011-1-13 18:21 编辑 ]

lhx120824

把下图中的A:D 区域变换到右边的分红色区域：
你能用一个公式实现吗？

成绩单.rar (14.21 KB, 下载次数: 202)

2011-1-14 03:55 上传

点击文件名下载附件

如果定义名称
座号=ROW($1:$17)+INT((COLUMN($A:$O)-1)/5)*17
方法一：就是上图中编辑栏中的公式。2007中的公式：
方法二：
=IF(MOD(COLUMN(A:O),5),INDEX($A:$D,ROW($1:$17)+INT((COLUMN($A:$O)-1)/5)*17+1,MOD(COLUMN(A:O)-1,5)+1),"")
用名称 =IF(MOD(COLUMN(A:O),5),INDEX($A:$D,座号+1,MOD(COLUMN(A:O)-1,5)+1),"")
方法三：2007中的公式
=IFERROR(INDEX($A:$D,ROW($1:$17)+INT((COLUMN($A:$O)-1)/5)*17+1,MOD(COLUMN(A:O)-1,5)+1),"")
用名称  =IFERROR(INDEX($A:$D,座号+1,MOD(COLUMN(A:O)-1,5)+1),"")

以上我用的都是多单元格数组公式。
当然还有其他的方法，比如用  =OFFSET( )  等等，等待其他方法。

上楼粉红色（579楼）部分就可用本楼的定义得到：
=座号     
多单元格数组公式

[ 本帖最后由 lhx120824 于 2011-1-14 04:14 编辑 ]

piny

原帖由 lhx120824 于 2011-1-13 16:54 发表
再回到前面楼层提到的如下图效果的公式：

ping 提供的公式：
..

發現太多人叫錯 現在每看到叫錯一次 piny會很認真的提醒 ^^

lhx120824

原帖由 piny 于 2011-1-13 17:08 发表


發現太多人叫錯 現在每看到叫錯一次 piny會很認真的提醒 ^^

对不起了，是
piny
怪我马虎，优点失礼。向你道歉。

weisiyu520

说明：1、首先，一个试场54人（这个数根据需要可以随时变化，是个变量）；
2、其次，由参考人数确定试场个数；
3、试场顺序是：1 — 求出的试场个数（取整）；
4、根据年级总分排名，第一试场就取年级总分排名1—54名作为第1试场；第二试场就取年级总分排名55—108；每个试场座位号只能是从1-54，依次类推。
5、学生编号格式为：例如下面所示：2010254  2010301
编排结果已给出：见此表！
我已经用辅助列得出结果，但觉得效率不够高，想让大家一起思考看能否得出更简洁的方法来！谢谢！

[ 本帖最后由 weisiyu520 于 2011-1-13 19:33 编辑 ]