ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 10147|回复: 28

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 2007-1-14 23:15 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:INDIRECT

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编辑过]

bliFI5ZW.rar

3.28 KB, 下载次数: 160

[分享及讨论]

BGPr3vDw.rar

3.34 KB, 下载次数: 140

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

评分

参与人数 1鲜花 +5 收起 理由
lgcmeli + 5

查看全部评分

TA的精华主题

TA的得分主题

发表于 2007-1-15 15:17 | 显示全部楼层

常规解法:

=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编辑过]

TA的精华主题

TA的得分主题

发表于 2007-1-15 15:47 | 显示全部楼层

哈哈,新发现,直接用(称为公式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编辑过]

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-1-15 15:51 | 显示全部楼层
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编辑过]

TA的精华主题

TA的得分主题

发表于 2007-1-15 15:57 | 显示全部楼层

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

=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编辑过]

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-1-15 15:58 | 显示全部楼层
按草兄在5楼的说法,是否能说明按F9后看到的是二维(三维)的一维显示?晕了。

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-1-15 16:18 | 显示全部楼层
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编辑过]

TA的精华主题

TA的得分主题

发表于 2007-1-15 16:29 | 显示全部楼层

我这里用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返回的是伪内存数组,需要用定义名称结合多单元格数组公式来实现。

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-1-15 16:36 | 显示全部楼层

我的出错,用的是2003版

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


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

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

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

TA的精华主题

TA的得分主题

发表于 2007-1-15 16:43 | 显示全部楼层
QUOTE:
以下是引用guangyp在2007-1-15 16:36:20的发言:

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

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


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

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

[分享及讨论]多列转单列新公式
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-7-17 08:44 , Processed in 0.175527 second(s), 19 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表