[分享及讨论]多列转单列新公式

guangyp

data为多列数据,用下面的公式可以得到单列结果。

http://club.excelhome.net/dispbbs.asp?boardid=3&replyid=383520&id=165518&page=1&skin=0&Star=2

=========================================================================

经与gouweicao78版主讨论后，最后具有处理错误值能力的单元格数组公式为：

=INDIRECT(TEXT(SMALL(IF(data<>"",ROW(data)*1000+COLUMN(data),65536255),ROW(1:1)),"r0c000"),0)&""

结果为内存数组的公式还有待讨论。

[此贴子已经被作者于2007-4-13 13:25:25编辑过]

gouweicao78

常规解法：

=INDEX(data,SMALL(IF(data<>"",ROW(data)*1000+COLUMN(data)),ROW(1:1))/1000,RIGHT(SMALL(IF(data<>"",ROW(data)*1000+COLUMN(data)),ROW(1:1)),3))

根据Indirect得到的新常规解法：

=INDIRECT(TEXT(SMALL(IF(data<>"",ROW(data)*1000+COLUMN(data)),ROW(1:1)),"r00000c000"),0)

数组公式

暂时未细看guangyp兄的问题是怎么引起的。

另：对1楼"r"&+Replace的方法进行改进，直接用Text即可实现：

=INDIRECT(TEXT(SMALL(IF(COUNTIF(OFFSET(A1:D1,,,ROW(data)),data),ROW(data)*1000+COLUMN(data)),ROW(INDIRECT("1:"&COUNTA(data)))),"R00000C000"),)

[此贴子已经被作者于2007-1-15 15:52:54编辑过]

gouweicao78

哈哈，新发现，直接用（称为公式3）：

=INDIRECT(TEXT(SMALL(IF(data<>"",ROW(data)*1000+COLUMN(data)),ROW(INDIRECT("1:"&COUNTA(data)))),"r00000c000"),)，按F9可得到内存数组，但是在单元格却得到#Value!

如果在单元格中对公式3套上Index（）：

=INDEX(INDIRECT(TEXT(SMALL(IF(data<>"",ROW(data)*1000+COLUMN(data)),ROW(INDIRECT("1:"&COUNTA(data)))),"r00000c000"),),1)——注意，把后面的1改为row(1:1)或者rows($1:1)都可以，然后用多单元格数组公式可以得到正确结果，换作别的数字则不行。

定义名称y=公式3，就可以用=y的多单元格数组公式得到。

这与楼主所说的奇怪现象是一致的——r和c两个变量组成的二维？

按理——Small出来后得到的就是一维数组。

[此贴子已经被作者于2007-1-15 15:49:33编辑过]

guangyp

QUOTE:

以下是引用gouweicao78在2007-1-15 15:17:50的发言：

=INDIRECT(TEXT(SMALL(IF(data<>"",ROW(data)*1000+COLUMN(data)),ROW(INDIRECT("1:"&COUNTA(data)))),"r00000c000"),)，按F9可得到内存数组，但是在单元格却得到#Value!

定义名称y=上面这个公式，就可以用了。

呵呵，草兄的改进确是不错！

“在单元格却得到#Value!”偶认为是隐性交叉产生的问题，至于结果是否是内存数组还有待讨论吧，偶总觉得不像。

[此贴子已经被作者于2007-1-15 15:51:49编辑过]

gouweicao78

根据本帖又得到新的常规解法：

=INDIRECT(TEXT(SMALL(IF(data<>"",ROW(data)*1000+COLUMN(data)),ROW(1:1)),"r00000c000"),0)

这个公式不会出现上述问题，因此反推：

问题出在——Small(……,Row)的Row上面，即Indirect的第1参数是否多元素数组的问题：

用Row(1:1)——得到的是单元数数组，用Rows($1:1)得到的是单值——但这两个都不重要。

用Row(Indirect("1:"&Counta(Data)))——得到的是多元素数组。

即Indirect对多元素数组进行引用时，会产生上述现象。

QUOTE:

再用简单的例子来证实：

=INDIRECT({"A1";"B3"})——按下F9可得到1列2行答案，一维；

=INDIRECT({"A1","B1";"B3","C1"})——按下F9可得到2行2列的答案，二维。

但这2个公式用多单元格数组公式都得到#VALUE!错误。

通过=ROWS(INDIRECT({"A1","B1";"B3","C1"}))——得到1——因此证实其结果不是内存数组。

定义名称XX=INDIRECT({"A1","B1";"B3","C1"})

用多单元格数组公式{=XX}可以得到正确答案，用=Rows(XX)——得到1，还一样不是内存数组。

[此贴子已经被作者于2007-1-15 16:12:08编辑过]

guangyp

按草兄在5楼的说法，是否能说明按F9后看到的是二维（三维）的一维显示？晕了。

guangyp

QUOTE:

以下是引用gouweicao78在2007-1-15 15:57:48的发言：

根据本帖又得到新的常规解法：

=INDIRECT(TEXT(SMALL(IF(data<>"",ROW(data)*1000+COLUMN(data)),ROW(1:1)),"r00000c000"),0)

这个公式不会出现上述问题，因此反推：

问题出在——Small(……,Row)的Row上面，即Indirect的第1参数是否多元素数组的问题：

用Row(1:1)——得到的是单元数数组，用Rows($1:1)得到的是单值——但这两个都不重要。

用Row(Indirect("1:"&Counta(Data)))——得到的是多元素数组。

即Indirect对多元素数组进行引用时，会产生上述现象。

QUOTE:

再用简单的例子来证实：

=INDIRECT({"A1";"B3"})——按下F9可得到1列2行答案，一维；

=INDIRECT({"A1","B1";"B3","C1"})——按下F9可得到2行2列的答案，二维。

但这2个公式用多单元格数组公式都得到#VALUE!错误。

通过=ROWS(INDIRECT({"A1","B1";"B3","C1"}))——得到1——因此证实其结果不是内存数组。

定义名称XX=INDIRECT({"A1","B1";"B3","C1"})

用多单元格数组公式{=XX}可以得到正确答案，用=Rows(XX)——得到1，还一样不是内存数组。

看了草兄的回复，问题的原因是找到了，这是否说明不可把公式直接改造成多单元格数组公式？目的是加快计算速度。

另外还有 &"" 的问题原因又是什么呢？

[此贴子已经被作者于2007-1-15 16:19:36编辑过]

gouweicao78

我这里用1楼附件的

x=INDIRECT("r"&REPLACE(TEXT(SMALL(IF(data<>"",ROW(data)*1000+COLUMN(data)),ROW(INDIRECT("1:"&COUNTA(data)))),"00000000"),6,,"c"),)

=x和=x&""——多单元格数组公式均能得到正确答案，因此不需要&""。且，放在任意位置均可以，无隐含交叉问题。

问题同5楼，即Indirect第1参数为多元素数组时，Indirect返回的是伪内存数组，需要用定义名称结合多单元格数组公式来实现。

guangyp

另外，&"" 问题主要指的是我在juyouhh兄《[分享]多列提取不重复元素新解》这一贴里未出错，而在本贴中却出错，大家可以验证看看。

[此贴子已经被作者于2007-1-15 16:38:52编辑过]

gouweicao78

QUOTE:

以下是引用guangyp在2007-1-15 16:36:20的发言：

另外，&"" 问题主要指的是我在juyouhh兄《[分享]多列提取不重复元素新解》这一贴里未出错，而在本贴中却出错，大家可以验证看看。

我的确是验证了的呀，这就奇怪了：Winxp sp2+Excel2003

[此贴子已经被作者于2007-1-15 16:46:50编辑过]