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

shaowu459 · 发表于 2011-1-14 17:16

参考一下，这个思路写的公式比较长……呵呵，见笑
更新一下：=INDIRECT(INDEX(TEXT(MID(SMALL(IF($B$3:$L$16<>"",COLUMN($B:$L)+ROW($3:$16)*1000),ROW(A1)),{1,3,1},CHOOSE({1,2,3},1+(SMALL(IF($B$3:$L$16<>"",COLUMN($B:$L)+ROW($3:$16)*1000),ROW(A1))>10^4),3,5)),{"R0C1","R2C0","R0C000"}),COLUMN(A1)),)

[ 本帖最后由 shaowu459 于 2011-1-14 17:57 编辑 ]

shaowu459 · 发表于 2011-1-14 18:08

=INDIRECT(INDEX(TEXT(MID(TEXT(SMALL(IF($B$3:$L$16<>"",COLUMN($B:$L)+ROW($3:$16)*1000),ROW(A1)),"00000"),{1,3,1},{2,3,5}),{"R0C1","R2C0","R0C000"}),COLUMN(A1)),)

最后更新一次了。

7786910 · 发表于 2011-1-14 22:07

好想法，好主意，好行动！

7786910 · 发表于 2011-1-14 22:14

感谢老师，向您学习。

7786910 · 发表于 2011-1-14 22:15

虽然用不到，但收藏了学习

7786910 · 发表于 2011-1-14 22:18

虽然用不到，但收藏了学习

huerhy · 发表于 2011-1-14 22:33

原帖由 lhx120824 于 2011-1-9 16:53 发表
列串联：
多单元格数组公式：

=MID(SMALL(IF(B2:E16="",4^8,COLUMN(B2:E16)*10^4+ROW(B2:E16)*10^2+B2:E16),ROW(1:200)),4,4)
效果见下图：
855978

感谢楼主付出的努力！一定要向您学习，把繁琐的工作简单化！
感谢你给我问题的答复，那个关于列串联的问题，我把公式挪过来后，没有实现预期的效果。我把要做的成绩表发上来，请您帮忙做一下！
具体见附件！

[ 本帖最后由 huerhy 于 2011-1-14 22:35 编辑 ]

lhx120824 · 发表于 2011-1-15 06:48

原帖由 huerhy 于 2011-1-14 22:33 发表

感谢楼主付出的努力！一定要向您学习，把繁琐的工作简单化！
感谢你给我问题的答复，那个关于列串联的问题，我把公式挪过来后，没有实现预期的效果。我把要做的成绩表发上来，请您帮忙做一下！
具体见附件！

需要修改引用范围和截取方法以及空值的处理方法。你的这个问题就是我上几楼的转换表中的教师列的转换问题。不过具体到数值这个特例就简单多了。保密号可直接与所在的行列信息一起处理。
=RIGHT(SMALL(IF(B2:W70="","",COLUMN(B2:W70)*10^4+ROW(B2:W70)*10^2+B2:W70),ROW(1:2000)),2)
公式的意思就是：
COLUMN(B2:W70)*10^4+ROW(B2:W70)*10^2+B2:W70 是把B2:W70所在的位置列乘以10^4，行乘以10^2，然后相加，这个加权的和的后两位是0，再把保密号本身加上正好不进位。
加权值的具体用法要看具体问题的行列数和单元格值的最大值的情况，三者所在的数位不能重合。上述公式中的加权和是列占5、6位，行占3、4，保密号占后两位。然后从小到大进行排序，先第一列，然后第二列，……每列之内再按行排序。这样站位就确定了，空值显示成错误值，排在最后。最后截取后两位即可实现列串联的目的。
如果保密号是3位数，那么行加权至少是10^3, 如果行数再超过2位数，那么列加权不能少于10的（保密号位数+行数的位数）次方。
这样说吧：行的加权是10^（保密号的位数），列的加权是10^（保密号位数+行数的位数)，RIGHT（，保密号的位数）。

物理登分表.rar (22.46 KB, 下载次数: 105)

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

lhx120824 · 发表于 2011-1-15 07:20

楼上的公式：
=RIGHT(SMALL(IF(B2:W70="","",COLUMN(B2:W70)*10^4+ROW(B2:W70)*10^2+B2:W70),ROW(1:2000)),2)
为多单元格数组公式，公式中的
B2:W70 是数据区域，包括空值的情况，最好用名称进行定义，区域变化后公式不用修改。
ROW(1:2000）中的2000不能小于区域中单元格的个数，取多了可以用屏蔽错误值的方法解决，取少了返回值不全。
RIGHT( ，2）其中的2由你所想后取的字符个数确定。
再说一下区域的选取，比如这个公式可选取 Y2:Y1500，不用鼠标选，因为太多。鼠标激活左上方的名称框，输入Y2:Y1500，回车即可实现。
然后鼠标激活编辑栏，输入公式后，按数组公式需要的三键结束。

[ 本帖最后由 lhx120824 于 2011-1-15 07:23 编辑 ]

huerhy · 发表于 2011-1-15 11:10

衷心谢谢楼主的指导和帮助！
因为是很菜的菜鸟，只能说一些想要的结果，但对公式一点都不懂。可能是我数学没学好，总是弄不明白公式的结构和含义，十分惭愧！
越来越觉得EXCEl的强大，简直有点儿望而却步了，要从那里学起，才能得到楼主之万一啊？
再次感谢您！

		自动登录	找回密码
密码			免费注册

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