|
小结： 本期竞赛题，有两大难点，一是筛选不重复值，二是文本合并，对于第一个问题已有很多比较成熟的解法，在这里就不重点分析了；二是文本合并，这是本题的难点。
不用VBA，不借助辅助列实现文本合并连接是一件比较困难的事，困难在于可用于文本合并连接的方法非常少，只有concatenate函数和文本运算符&，这两种方法的问题最大问题是无法动态地控制需要合并连接的项数。本期出题也是抛砖引玉，期待在这一问题上有所突破。可喜的是已有willin2000、laoyebin、封州大少、dengyf、fjlhgs等五位朋友给出了新的思路，就是利用TEXT(数值，rept())函数组合动态构造并控制文本取值输出项，所以多加了一分，但未能突破7项的限制，所以只得2分。
这一函数组合在论坛上早出现过，但未被用于文本合并连接上，也未深入研究过。借助本期竞赛总结，对这一问题进行一些介绍。
要运用好text(数值，rept())这个函数组合，首选要了解text的用法，
TEXT(value,format_text)，Value为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用，Format_text为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。基本思路就利用文本形式的数字格式构建所需要的取数函数，并由rept函数输出重复次数，最后根据数值用text予以赋值。实现这一功能大体可用三种方法，为了便于使用，可先将其定义为名称，如link1,如需要增加分隔符，可再定义k=";"。
1、TEXT(数值，REPT("\b00&k\,",SUM(n(line>0))))），实用于对单元格取值；
2、TEXT(数值，REPT("i!n!d!ex(!aa,00)&k,",SUM(n(line)>0))))，实用于内存数组取值；
3、TEXT(数值,TEXT("&\b00&k",REPT("@",SUM(n(line)>0)))，两次text嵌套，这一种方法以是方法1的变形，列出来是为了让大家多一点了解text丰富的变化。
利用TEXT(数值，rept())函数组合生成需要重复项后，又可用两种方法构建最终公式：
1、=SUBSTITUTE(EVALUATE(link1),";","",SUM(N(line>0)))，直接用文本运算符＆连接。
2、=SUBSTITUTE(EVALUATE("concatenate("&link1&")"),";","",SUM(N(line>0)))，用运concatenate函数连接。 因为concatenate函数有30个参数的限制，所以建议使用&连接，一可以避参数数量限制，二可以使公式简洁些。最后用宏表函数EVALUATE生成最后的公式，得到结果。
这里要特别说明的两个限制的难点：一是text函数的第一个参数有15位限制，二是EVALUATE函数参数总长不能超过256个字符(包括用函数生成的结果)，且参数值不能为空。
为了突破这些限制，可借鉴sum((),(),())结构突破sum函数30个参数限制的思路，对数值进行分组，因为要至少要考虑10项以上的输出需求，就需要两位数数字格式结构，要满足小于15位限制的大偶数为14，因此一次分组最大项数只能是7项，经过多次分组可以突破7项的限制，目前已完成了99项文本合并连接。
当然实际运用中，一般没必要用到100以上或更多项，在20以内基本可以满足需要，因此本例中只给出了最大20项的结果，需要了解99项的合并连接的朋友可以去看看：http://club.excelhome.net/viewthread.php?tid=262026&px=0
因为时间问题，有些公式未进行深入推敲，可能未实现最优化，请大家多参与研究，提出更好的思路，优化公式，谢谢大家。
| 
楼主: jssy
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2007-8-26 22:58
楼主
