|
|
我把我公式的思路说一下，以便楼主和家里xdjm理解：=NDIRECT("班级课表!"&TEXT(MAX((T(OFFSET(班级课表!$B$3,1+(ROW($A$1:$A$12)-1)*15,(COLUMN($A$1:$B$1)-1)*7))=教师任课一览表!$BR$3)*((3+(ROW($A$1:$A$12)-1)*15)*1000+(2+(COLUMN($A$1:$B$1)-1)*7))),"R0C000"),)数组公式按三键
1、楼主24层IF嵌套公式的logical-test后半部是固定单元格：教师任课一览表!$BR$3，而前半部是班级课表!B4、I4、B19、I19、B34、I34……B154、I154、B169、I169，纵向每隔15行，横向是隔开7列，很有规律。
2、为此，就用OFFSET构成一个二维“数组”（诸元素非数字）：T(OFFSET(班级课表!$B$3,1+(ROW($A$1:$A$12)-1)*15,(COLUMN($A$1:$B$1)-1)*7))，其中1+(ROW($A$1:$A$12)-1)*15为{1;16;31;46;61;76;91;106;121;136;151;166}，(COLUMN($A$1:$B$1)-1)*7为{0,7}，这两组数分别组成以B3为参照点的OFFSET行偏移与列偏移，也即是上面的所列出的不连续单元格。
3、IF函数的value-if-true结果单元格是上述单元格的的上一行，即B3、I3、B18、I18、B33、I33、……，为了最后能通过INDIRECT用R1C1引用方式返回结果，公式构造另一个二维数组：相关行号乘1000加上列标序号：(3+(ROW($A$1:$A$12)-1)*15)*1000+(2+(COLUMN($A$1:$B$1)-1)*7)({3002,3009;18002,18009;33002,33009;48002,48009;63002,63009;78002,78009;93002,93009;108002,108009;123002,123009;138002,138009;153002,153009;168002,168009})（不熟悉R1C1引用的朋友可以从这个数组看到：千位数以上的3、18、33……正是所需单元格行号，而后三位2、9恰是B列、I列的列标序号）
4、这样一来，24层嵌套就变成：MAX((T(OFFSET(班级课表!$B$3,1+(ROW($A$1:$A$12)-1)*15,(COLUMN($A$1:$B$1)-1)*7))=教师任课一览表!$BR$3)*((3+(ROW($A$1:$A$12)-1)*15)*1000+(2+(COLUMN($A$1:$B$1)-1)*7)))，前半部粉色部分与绿色部分相等判断结果是一个true与诸多false组成的数组，与后半部紫色部分相乘，得到唯一一个符合相等条件的四（五、六）位数及其他全是0的数组，通过MAX就把这个数提出来。
5、最后，INDIRECT("班级课表!"&TEXT(MAX(……),"R0C000"),)，把结果单元格里内容返回出来。
[ 本帖最后由 wangjguo44 于 2011-1-7 11:55 编辑 ] |
|
楼主: xiangjm123
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2011-1-7 11:41
楼主
学习了
发表于 2011-1-7 22:46